PURCHASE REQUISITION WITHOUT PO

SELECT prh.segment1 "PR NUM",
TRUNC (prh.creation_date) "CREATED ON",
TRUNC (prl.creation_date) "Line Creation Date",
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
TRUNC (prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
FROM po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
( SELECT DISTINCT agent_id, agent_name FROM apps.po_agents_v) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND ppf1.person_id = prh.preparer_id
AND prh.creation_date BETWEEN ppf1.effective_start_date
AND ppf1.effective_end_date
AND ppf2.agent_id(+) = msi.buyer_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.destination_organization_id
AND pll.line_location_id(+) = prl.line_location_id
AND pll.po_header_id = ph.po_header_id(+)
AND pll.po_line_id = pl.po_line_id(+)
AND prh.authorization_status = 'APPROVED'
AND pll.line_location_id IS NULL
AND prl.closed_code IS NULL
AND NVL (prl.cancel_flag, 'N') <> 'Y'

ORDER BY 1, 2, 4

PO details query in oracle apps R12


SELECT HOU.SHORT_CODE OU,
             PHA.CREATION_DATE PO_CREATION_DATE,
             PHA.APPROVED_DATE PO_APPROVED_DATE,
             NVL(PRH.INTERFACE_SOURCE_CODE, PRH.APPS_SOURCE_CODE)                           
             REQUISITION_SOURCE,
             PHA.TYPE_LOOKUP_CODE PO_TYPE,
             APS.VENDOR_NAME SUPPLIER_NAME,
             APS.SEGMENT1 SUPPLIER_NUMBER,
             REPLACE(PHA.COMMENTS, CHR(10), '  ') PO_HEADER_DESCRIPTION,
             NVL(PRH.SEGMENT1, pda.req_header_reference_num) PR_NUMBER,
             PHA.SEGMENT1 PO_NUMBER,
             PLA.LINE_NUM PO_LINE_NUMBER,
             (SELECT SEGMENT1
                FROM apps.MTL_SYSTEM_ITEMS_B
               WHERE INVENTORY_ITEM_ID = PLA.ITEM_ID
                 AND ORGANIZATION_ID = PLLA.SHIP_TO_ORGANIZATION_ID) ITEM,
             pda.destination_type_code DESTINATION_TYPE,
             REPLACE(REPLACE(PLA.ITEM_DESCRIPTION, CHR(13), '  '),
                     CHR(10),
                     '  ') PO_LINE_DESCRIPTION,
             TO_CHAR(NVL(PLA.UNIT_PRICE * PLA.QUANTITY, '0.00'),
                     '999,999,999,999.99') PO_LINE_AMOUNT,
             PO_INQ_SV.GET_PO_TOTAL(PHA.TYPE_LOOKUP_CODE,
                                    PHA.PO_HEADER_ID,
                                    NULL) PO_TOTAL_AMOUNT,
             PHA.CURRENCY_CODE PO_CURRENCY,
             PLA.PURCHASE_BASIS PO_LINE_TYPE,
             MCK.CONCATENATED_SEGMENTS HERMES_CATEGORY,
             PLLA.PROMISED_DATE PROMISED_DATE,
             PLLA.NEED_BY_DATE NEED_BY_DATE --LPAD(TO_CHAR(PLLA.NEED_BY_DATE,'MM/DD/YYYY '),20) "NEED_BY_DATE",
             PPX1.FULL_NAME REQUESTER,
             PPX2.FULL_NAME PREPARER,
             PPF2.AGENT_NAME BUYER,
             HRL.LOCATION_CODE SHIP_TO_LOC,
             GCCK.CONCATENATED_SEGMENTS GL_ACCOUNT,
             PLLA.SHIPMENT_NUM SHIPMENT_NUMBER,
             PLA.UNIT_PRICE UNIT_PRICE,
             (PRL.unit_price * PRL.quantity)Req_line_amt,
             PLLA.QUANTITY QTY_ORDERED,
             PLLA.QUANTITY_BILLED QTY_BILLED,
             PLLA.QUANTITY_RECEIVED QTY_RECD,
             (PLLA.QUANTITY_BILLED * PLA.UNIT_PRICE) AMOUNT_BILLED,
             (PLLA.QUANTITY_RECEIVED * PLA.UNIT_PRICE) AMOUNT_RECD,
             RCD.RCV_CREATION_DATE RECEIVING_CREATION_DATE,
             PA.segment1 PROJECT,
             pa.project_type PROJECT_TYPE,
             AT.NAME PAYMENT_TERMS,
             DECODE(PLLA.INSPECTION_REQUIRED_FLAG ||
                    PLLA.RECEIPT_REQUIRED_FLAG,
                    'NN',
                    '2 WAY',
                    'NY',
                    '3 WAY',
                    'YY',
                    '4 WAY',
                    'Not Specified') MATCHING_TYPE,
             PDA.ACCRUE_ON_RECEIPT_FLAG ACCRUE_AT_RECEIPT,
             PHA.AUTHORIZATION_STATUS PO_STATUS,
             PLLA.CLOSED_CODE SHIPMENT_STATUS,
             (SELECT ORGANIZATION_CODE
                FROM apps.MTL_PARAMETERS
               WHERE ORGANIZATION_ID = PLLA.SHIP_TO_ORGANIZATION_ID) SHIP_TO_ORG,
             PRH.description Description_Headers,
             PRL.LINE_NUM Line#,
             TO_CHAR(TRUNC(PRL.CREATION_DATE), 'DD-MON-YYYY') Creation_date_of_Req_line,
             (SELECT distinct papf.full_name
                FROM apps.po.po_action_history pah,
                     applsys.fnd_user     fu,
                     hr.per_all_people_f  papf
               WHERE object_id = prh.requisition_header_id
                 AND pah.employee_id = fu.employee_id
                 AND fu.employee_id = papf.person_id
                 AND SYSDATE BETWEEN papf.effective_start_date AND
                     papf.effective_end_date
                 AND pah.object_type_code = 'REQUISITION'
                    --  AND pah.action_code = 'APPROVE'
                 AND pah.sequence_num =
                     (SELECT MAX(sequence_num)
                        FROM po.po_action_history pah1
                       WHERE pah1.object_id = pah.object_id
                         AND pah1.object_type_code = 'REQUISITION'
                      --  AND pah1.action_code = 'APPROVE'
                      )) Performed_By,
             PRH.CREATION_DATE PR_CREATION_DATE,
             (SELECT distinct pah.ACTION_DATE
                FROM apps.po.po_action_history pah,
                     applsys.fnd_user     fu,
                     hr.per_all_people_f  papf
               WHERE object_id = prh.requisition_header_id
                 AND pah.employee_id = fu.employee_id
                 AND fu.employee_id = papf.person_id
                 AND SYSDATE BETWEEN papf.effective_start_date AND
                     papf.effective_end_date
                 AND pah.object_type_code = 'REQUISITION'
                    --  AND pah.action_code = 'APPROVE'
                 AND pah.sequence_num =
                     (SELECT MAX(sequence_num)
                        FROM po.po_action_history pah1
                       WHERE pah1.object_id = pah.object_id
                         AND pah1.object_type_code = 'REQUISITION'
                      --  AND pah1.action_code = 'APPROVE'
                      )) Approval_Date,
             prl.line_num Line,
             (SELECT distinct PRIMARY_UNIT_OF_MEASURE
                FROM apps.mtl_system_items_b
               where INVENTORY_ITEM_ID(+) = PLA.item_id) UOM,
             (SELECT distinct DESCRIPTION
                FROM apps.hr_locations
               where location_id = pha.BILL_TO_LOCATION_ID) BILL_TO,
             (SELECT displayed_field
                FROM APPS.po_lookup_codes fob
               WHERE fob.lookup_type = 'FREIGHT TERMS'
                 AND fob.lookup_code = pha.freight_terms_lookup_code) FOB_Terms,
             PDA.ATTRIBUTE2 SIngle_Bid,
             PDA.ATTRIBUTE9 Emergency_PO,
             PDA.ATTRIBUTE5 After_The_fact,
             PDA.ATTRIBUTE6 Expedited_PO,
             PDA.ATTRIBUTE7 O2_Cleaning,
             PDA.ATTRIBUTE10 Critical_Item,
             PDA.ATTRIBUTE3 OEM_SOurced,
             PLA.CANCEL_DATE Cancelled_DATE,
             PRH.description Line_Description,
             PRL.CURRENCY_CODE PR_CURRENCY,
             PDA.DESTINATION_ORGANIZATION_ID deliver_to_organization,
             PRL.CURRENCY_UNIT_PRICE PR_Unit_Price,
             PHA.PO_HEADER_ID PO_HEADER,
             PRL.CURRENCY_UNIT_PRICE Currancy_Price,
              por_view_reqs_pkg.get_note_to_agent
                                     (prh.requisition_header_id)
                                                                Note_to_Buyer
        FROM APPS.PO_HEADERS_ALL PHA,
             APPS.PO_LINES_ALL PLA,
             APPS.PO_LINE_LOCATIONS_ALL PLLA,
             APPS.HR_LOCATIONS_ALL HRL,
             APPS.PO_DISTRIBUTIONS_ALL PDA,
             (SELECT DISTINCT AGENT_ID, AGENT_NAME from APPS.PO_AGENTS_V) PPF2,
             APPS.AP_SUPPLIERS APS,
             APPS.MTL_CATEGORIES_KFV MCK,
             APPS.PO_REQUISITION_HEADERS_ALL PRH,
             APPS.PO_REQUISITION_LINES_ALL PRL,
             APPS.PO_REQ_DISTRIBUTIONS_ALL PRD,
             APPS.HR_OPERATING_UNITS HOU,
             APPS.PER_PEOPLE_X PPX1,
             APPS.PER_PEOPLE_X PPX2,
             APPS.GL_CODE_COMBINATIONS_KFV GCCK,
             APPS.PO_LINE_TYPES PLT,
             APPS.GL_LEDGERS GL,
             APPS.PA_PROJECTS_ALL PA,
             APPS.AP_TERMS AT,
             (SELECT MAX(RSL.CREATION_DATE) RCV_CREATION_DATE,
                     pla.po_line_id
                FROM APPS.RCV_SHIPMENT_LINES RSL,
                     APPS.PO_HEADERS_ALL     PHA,
                     APPS.PO_LINES_ALL       PLA
               WHERE 1 = 1
                 AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
                 AND RSL.PO_line_ID(+) = PlA.PO_line_ID
               GROUP BY pla.po_line_id) RCD
       WHERE 1 = 1
         AND RCD.PO_LINE_ID(+) = PLA.PO_LINE_ID
         AND PHA.TERMS_ID = AT.TERM_ID
         AND PHA.VENDOR_ID = APS.VENDOR_ID
         AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
         AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
         AND PLLA.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID
         AND nvl(PLLA.CANCEL_FLAG, 'N') = 'N'
         AND PHA.AGENT_ID = PPF2.AGENT_ID(+)
         AND PLA.CATEGORY_ID = MCK.CATEGORY_ID
         AND PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
         AND PLA.PO_LINE_ID = PDA.PO_LINE_ID
         AND PA.PROJECT_ID(+) = PDA.PROJECT_ID
         AND PRH.REQUISITION_HEADER_ID(+) = PRL.REQUISITION_HEADER_ID
         AND PRL.REQUISITION_LINE_ID(+) = PRD.REQUISITION_LINE_ID
         AND PDA.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID(+)
         AND PHA.ORG_ID = HOU.ORGANIZATION_ID
         AND PDA.DELIVER_TO_PERSON_ID = PPX1.PERSON_ID(+)
         AND PRH.PREPARER_ID = PPX2.PERSON_ID(+)
         AND PDA.CODE_COMBINATION_ID = GCCK.CODE_COMBINATION_ID(+)
         AND PLT.LINE_TYPE_ID = PLA.LINE_TYPE_ID(+)
        -- AND PHA.AUTHORIZATION_STATUS = 'APPROVED'
        AND PRH.AUTHORIZATION_STATUS= 'APPROVED'
         AND HOU.SET_OF_BOOKS_ID = GL.LEDGER_ID
         AND GL.LEDGER_ID = NVL(P_SET_OF_BOOK_ID, GL.LEDGER_ID)
         AND HOU.ORGANIZATION_ID =
             NVL(P_ORGANIZATION_ID, HOU.ORGANIZATION_ID)
         AND PHA.AGENT_ID = NVL(P_BUYER_ID, PHA.AGENT_ID)
         AND HRL.LOCATION_ID = NVL(P_LOCATION_ID, HRL.LOCATION_ID)
         AND ((P_REQUESTOR_ID is null and 1 = 1) OR
             (P_REQUESTOR_ID is not null and
             PPX1.PERSON_ID = P_REQUESTOR_ID))
         AND TRUNC(PHA.APPROVED_DATE) BETWEEN
             NVL(TRUNC(fnd_date.canonical_to_date(PO_APPR_DATE_FROM)),
                 TRUNC(PHA.APPROVED_DATE)) AND
             NVL(TRUNC(fnd_date.canonical_to_date(PO_APPR_DATE_TO)),
                 TRUNC(PHA.APPROVED_DATE))
       ORDER BY PHA.SEGMENT1,
                PLA.LINE_NUM,
                PLLA.QUANTITY,
                GCCK.CODE_COMBINATION_ID;