SELECT
PRH.SEGMENT1 REQ_NO,PRL.QUANTITY REQ_QTY,PRL.UNIT_PRICE REQ_PRICE,PRH.AUTHORIZATION_STATUS REQ_STATUS
,PHA.ORG_ID,PHA.SEGMENT1 PO_NUMBER,PLA.QUANTITY,PLA.UNIT_PRICE,PHA.AUTHORIZATION_STATUS PO_STATUS
,(SELECT APT.NAME FROM AP_TERMS APT WHERE APT.TERM_ID =PHA.TERMS_ID) PAYMENT_TERMS
,(SELECT HOU.NAME FROM HR_ORGANIZATION_UNITS HOU WHERE HOU.ORGANIZATION_ID=RT.ORGANIZATION_ID) ORGZ_NAME
,RSH.SHIPMENT_NUM,RT.SUBINVENTORY,RT.PRIMARY_QUANTITY RCV_QTY,RT.TRANSACTION_TYPE
,AIA.INVOICE_NUM, AIA.VENDOR_ID
,AIP.PAYMENT_NUM,ACA.CHECK_NUMBER
FROM
PO_REQUISITION_HEADERS_ALL PRH
,PO_REQUISITION_LINES_ALL PRL
,PO_REQ_DISTRIBUTIONS_ALL PRD
,PO_DISTRIBUTIONS_ALL PDA
,PO_HEADERS_ALL PHA
,PO_LINES_ALL PLA
,RCV_TRANSACTIONS RT
,RCV_SHIPMENT_HEADERS RSH
,RCV_SHIPMENT_LINES RSL
,AP_INVOICES_ALL AIA
,AP_INVOICE_LINES_ALL AIL
,AP_INVOICE_DISTRIBUTIONS_ALL AID
,AP_INVOICE_PAYMENTS_ALL AIP
,AP_PAYMENT_SCHEDULES_ALL APS
,AP_CHECKS_ALL ACA
WHERE
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 PDA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PDA.PO_DISTRIBUTION_ID= RT.PO_DISTRIBUTION_ID
AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND AIA.PO_HEADER_ID(+)=PHA.PO_HEADER_ID --AIA.QUICK_PO_HEADER_ID
--AND AIL.PO_LINE_ID(+)=PLA.PO_LINE_ID
AND AIL.INVOICE_ID = AIA.INVOICE_ID
AND AIL.RCV_TRANSACTION_ID(+)=RT.TRANSACTION_ID
AND AID.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND AID.INVOICE_ID=AIP.INVOICE_ID(+)
AND AIP.INVOICE_ID=APS.INVOICE_ID(+)
AND AIP.INVOICE_PAYMENT_ID =ACA.PAYMENT_ID(+)
Order By PO_NUMBER
AND AIL.RCV_TRANSACTION_ID(+)=RT.TRANSACTION_ID
AND AID.PO_DISTRIBUTION_ID=PDA.PO_DISTRIBUTION_ID
AND AID.INVOICE_ID=AIP.INVOICE_ID(+)
AND AIP.INVOICE_ID=APS.INVOICE_ID(+)
AND AIP.INVOICE_PAYMENT_ID =ACA.PAYMENT_ID(+)
Order By PO_NUMBER