Oracle EBS (ERP)

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

SELECT
     AC.CUSTOMER_NAME
    ,OOH.ORDER_NUMBER
    ,OOH.ORDERED_DATE
    ,(Select ORGANIZATION_CODE From mtl_parameters where ORGANIZATION_ID=OOH.SHIP_FROM_ORG_ID) ORG_CODE
    ,OT.NAME ORDER_TYPE
    ,OOL.ORDERED_ITEM
    ,OOL.ORDERED_QUANTITY
    ,OOL.SHIPPED_QUANTITY
    ,OOL.UNIT_SELLING_PRICE UNIT_PRICE
    ,WND.NAME DELIVERY_NO
    ,(SELECT RAT.NAME FROM RA_TERMS RAT WHERE RAT.TERM_ID=RCT.TERM_ID) TERM_NAME
    ,RCT.TRX_NUMBER
    ,RCT.TRX_DATE
    ,ARPS.AMOUNT_LINE_ITEMS_ORIGINAL TOTAL_INVOICE_AMOUNT
    ,ARPS.TAX_ORIGINAL      ORIGINAL_TAX_AMT
    ,ARPS.TAX_REMAINING     REMAINING_TAX_AMT
    ,ARPS.AMOUNT_DUE_REMAINING DUE_AMOUNT
FROM
    OE_ORDER_HEADERS_ALL OOH
,OE_ORDER_LINES_ALL OOL
,OE_TRANSACTION_TYPES_TL OT
,AR_CUSTOMERS AC
,WSH_DELIVERY_DETAILS WDD
,WSH_NEW_DELIVERIES WND
,WSH_DELIVERY_ASSIGNMENTS WDA
,RA_CUSTOMER_TRX_ALL RCT
--,RA_CUSTOMER_TRX_LINES_ALL RCTL
,AR_PAYMENT_SCHEDULES_ALL ARPS
WHERE
OOH.HEADER_ID=OOL.HEADER_ID
AND OOH.ORDER_TYPE_ID=OT.TRANSACTION_TYPE_ID
AND AC.CUSTOMER_ID=OOH.SOLD_TO_ORG_ID
--AND WDD.SOURCE_HEADER_ID=OOH.HEADER_ID
AND WDD.SOURCE_LINE_ID(+)=OOL.LINE_ID
AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID(+)
AND WDA.DELIVERY_ID=WND.DELIVERY_ID(+)
--AND WND.CUSTOMER_ID=AC.CUSTOMER_ID
--AND OOH.ORG_ID=RCT.ORG_ID
--AND OOH.ORDER_NUMBER=RCT.INTERFACE_HEADER_ATTRIBUTE1(+)
AND WDA.DELIVERY_ID=RCT.INTERFACE_HEADER_ATTRIBUTE3(+)
AND ARPS.CUSTOMER_TRX_ID(+)=RCT.CUSTOMER_TRX_ID
--AND RCT.ORG_ID=RCTL.ORG_ID
--AND RCT.CUSTOMER_TRX_ID=RCTL.CUSTOMER_TRX_ID(+)
--AND RCTL.INTERFACE_LINE_ATTRIBUTE6(+)=TO_CHAR(OOL.LINE_ID)
--AND RCTL.interface_line_context = 'ORDER ENTRY'
--AND RCTL.LINE_TYPE='LINE'
--AND OOH.ORDER_NUMBER=RCTL.INTERFACE_LINE_ATTRIBUTE1
Order By ORDER_NUMBER

Procure to Pay Cycle (P2P) Query

Order to Cash cycle (O2C) Query