Query to extract Purchase Order Details

SELECT POH.SEGMENT1 "Po Number"
     , POH.TYPE_LOOKUP_CODE "Po  Type"
     , POH.AUTHORIZATION_STATUS "Po  Status"
     , POV.VENDOR_NAME "Supplier Name"
     , POVS.VENDOR_SITE_CODE "Location"
     , HRLS.LOCATION_CODE "Ship To"
     , HRLB.LOCATION_CODE "Bill To"
     , POL.LINE_NUM "Line Number"
     , MSIB.SEGMENT1 "Item"
     , POL.UNIT_PRICEm "Unit Price"
     , POL.QUANTITY "Quantity"
     , POD.AMOUNT_BILLED "Amount"
     , POD.DESTINATION_SUBINVENTORY
     , PPF.FULL_NAME "Buyer Name"
     , POH.CLOSED_CODE
FROM   PO_HEADERS_ALL POH
     , PO_LINES_ALL POL
     , MTL_SYSTEM_ITEMS_B MSIB
     , PO_LINE_LOCATIONS_ALL POLL
     , PO_DISTRIBUTIONS_ALL POD
     , PO_VENDORS POV
     , PO_VENDOR_SITES_ALL POVS
     , HR_LOCATIONS_ALL HRLS
     , HR_LOCATIONS_ALL HRLB
     , PER_ALL_PEOPLE_F PPF
     , PO_LINE_TYPES POLT
WHERE  POLT.LINE_TYPE_ID = POL.LINE_TYPE_ID
AND    POVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND    POV.VENDOR_ID = POH.VENDOR_ID
AND    POL.ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND    MSIB.ORGANIZATION_ID = 204
AND    POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND    POL.PO_LINE_ID = POD.PO_LINE_ID
AND    POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND    POH.SHIP_TO_LOCATION_ID = HRLS.LOCATION_ID
AND    POH.BILL_TO_LOCATION_ID = HRLB.LOCATION_ID
AND    POH.AGENT_ID = PPF.PERSON_ID
AND    POH.SEGMENT1 = NVL (:PO_NUMBER, POH.SEGMENT1);    -- Enter Purchase Order Number

Query to extract PO Requisitions Details

SELECT HOU.NAME "Operating unit"
     , PRH.SEGMENT1 "Requisition Number"
     , PRH.DESCRIPTION "Description"
     , PRH.TYPE_LOOKUP_CODE "Requisition Type"
     , PRH.AUTHORIZATION_STATUS "Requisition Status"
     , PPF.FULL_NAME "Preparer"
     , MSI2.SEGMENT1 "Item Number"
     , PRL.LINE_NUM "Requisition Line Num"
     , PRL.ITEM_DESCRIPTION "Requisition Line Desc"
     , PRL.UNIT_MEAS_LOOKUP_CODE "UOM"
     , PRL.QUANTITY
     , PRL.UNIT_PRICE "Unit cost"
     , MIC.SEGMENT1 || '.' || MIC.SEGMENT2 || '.' || MIC.SEGMENT3 || '.' || MIC.SEGMENT4 CATEGORY
     , HOUT.NAME ORGANIZATION
     , HLA.LOCATION_CODE LOCATION
     , PRL.DESTINATION_SUBINVENTORY DEPARTMENT
     , PPF2.FULL_NAME REQUISITER
FROM   APPS.PO_REQUISITION_HEADERS_ALL PRH
     , APPS.PO_REQUISITION_LINES_ALL PRL
     , APPS.PER_ALL_PEOPLE_F PPF
     , APPS.PER_ALL_PEOPLE_F PPF2
     , APPS.MTL_SYSTEM_ITEMS_B MSI2
     , APPS.HR_LOCATIONS_ALL HLA
     , APPS.HR_ALL_ORGANIZATION_UNITS_TL HOUT
     , APPS.MTL_CATEGORIES MIC
     , APPS.HR_OPERATING_UNITS HOU
WHERE  1 = 1
AND    HOU.ORGANIZATION_ID = PRH.ORG_ID
AND    HOUT.ORGANIZATION_ID(+) = PRL.DESTINATION_ORGANIZATION_ID
AND    HOUT.LANGUAGE(+) = USERENV ('LANG')
AND    PPF2.PERSON_ID(+) = PRL.TO_PERSON_ID
AND    TRUNC (SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE(+) AND PPF2.EFFECTIVE_END_DATE(+)
AND    HLA.LOCATION_ID(+) = PRL.DELIVER_TO_LOCATION_ID
AND    MIC.CATEGORY_ID(+) = PRL.CATEGORY_ID
AND    MSI2.INVENTORY_ITEM_ID = PRL.ITEM_ID
AND    MSI2.INVENTORY_ITEM_ID(+) = PRL.ITEM_ID
AND    MSI2.ORGANIZATION_ID(+) = PRL.DESTINATION_ORGANIZATION_ID
AND    NVL (PRH.CANCEL_FLAG, 'N') <> 'Y'
AND    NVL (PRL.CANCEL_FLAG, 'N') <> 'Y'
AND    PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND    PPF.PERSON_ID = PRH.PREPARER_ID
AND    TRUNC (SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND    PRH.ORG_ID = 204
AND    PRH.SEGMENT1 = NVL (:REQ_NUMBER   ,PRH.SEGMENT1)                                                         -- ENTER REQUISITION NUMBER