Friday, April 24, 2015

P2P(Procure to Pay) Cycle Complete Query


SELECT PRHA.SEGMENT1 REQNO,
PRHA.TYPE_LOOKUP_CODE REQTYPE,
PHA.SEGMENT1 PONO,
PHA.TYPE_LOOKUP_CODE POTYPE,
APS.VENDOR_NAME SUPPLIERNAME,
ASSA.VENDOR_SITE_CODE SUPPLIERSITE,
RSH.SHIPMENT_NUM RECEIPTNO,
AIA.INVOICE_ID INVID,
AIA.INVOICE_NUM INVOICENO,
AIA.INVOICE_AMOUNT INVAMOUNT,
AIA.INVOICE_TYPE_LOOKUP_CODE INVTYPE,
AIA.INVOICE_CURRENCY_CODE INVCURRENCY,
AIPA.INVOICE_PAYMENT_ID PAYMENTID,
AIPA.AMOUNT PAYMENTAMOUNT,
ACA.CHECK_ID CHECKID,
ACA.BANK_ACCOUNT_NAME BANKNAME,
ACA.BANK_ACCOUNT_NUM BANKNO,
ACA.BANK_ACCOUNT_TYPE BANKTYPE,
GJH.JE_HEADER_ID BATCHHEADERID,
GJH.JE_SOURCE BATCHSOURCE,
GJB.JE_BATCH_ID BATCHID,
GJB.NAME BATCHNAME
FROM PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL ASSA,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_JE_BATCHES GJB
WHERE PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND APS.VENDOR_ID = PHA.VENDOR_ID
AND ASSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND APS.VENDOR_ID = ASSA.VENDOR_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND APS.VENDOR_ID = AIA.VENDOR_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND TO_CHAR (AIDA.INVOICE_ID) = GJL.REFERENCE_2
AND TO_CHAR (AIDA.DISTRIBUTION_LINE_NUMBER) = GJL.REFERENCE_3
AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
AND PRHA.SEGMENT1 = '100'