Procure to Pay Cycle (P2P) Query


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