Procure to Pay Technical Cycle

P2P Technical Flow (Procure to Pay Cycle with Important Columns and Joins)

SELECT
PRHA.SEGMENT1 REQ_NUMBER,
PRHA.AUTHORIZATION_STATUS,
PRHA.TYPE_LOOKUP_CODE,
PRLA.ITEM_DESCRIPTION,
PRLA.UNIT_PRICE,
PRLA.QUANTITY,
PRLA.ITEM_ID,
PRLA.QUANTITY_DELIVERED,
PRLA.QUANTITY_CANCELLED,
PRLA.ORG_ID,
MSIB.INVENTORY_ITEM_ID,
MSIB.ORGANIZATION_ID,
MSIB.SEGMENT1 "ITEM",
PRDA.DISTRIBUTION_ID,
PDA.PO_DISTRIBUTION_ID,
PDA.REQ_DISTRIBUTION_ID,
PLA.PO_LINE_ID,
PHA.PO_HEADER_ID,
PHA.SHIP_TO_LOCATION_ID,
PHA.AUTHORIZATION_STATUS "PO STATUS",
ASS.VENDOR_NAME,
ASSA.ADDRESS_LINE1||','||ASSA.CITY||','||ASSA.STATE||','||ASSA.ZIP||','||ASSA.COUNTRY,
RT.TRANSACTION_ID,
RT.TRANSACTION_TYPE,
RT.UNIT_OF_MEASURE,
RSL.QUANTITY_SHIPPED,
RSL.QUANTITY_RECEIVED,
RSH.RECEIPT_NUM,
AILA.INVOICE_ID,
AILA.LINE_SOURCE,
AILA.LINE_TYPE_LOOKUP_CODE,
AILA.AMOUNT,
AIA.INVOICE_NUM,
AIA.INVOICE_AMOUNT,
AIA.AMOUNT_PAID,
AIA.SOURCE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
ACA.BANK_ACCOUNT_NAME,
ACA.CHECK_NUMBER,
ACA.CHECK_ID,
ACA.STATUS_LOOKUP_CODE,
ACA.CLEARED_AMOUNT,
XAH.DESCRIPTION "INVOICE DESCRIPTION",
GJB.NAME "BATCH NAME",
GJB.STATUS,
GJB.DESCRIPTION "BATCH DESCRIPTION",
GJH.NAME "JOURNAL NAME",
GJH.DESCRIPTION "JOURNAL DESCRIPTION"
from
PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
MTL_SYSTEM_ITEMS_B MSIB,
PO_LINE_LOCATIONS_ALL PLLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_DISTRIBUTIONS_ALL PDA,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
AP_SUPPLIERS ASS,
AP_SUPPLIER_SITES_ALL ASSA,
--AP_SUPPLIER_CONTACTS ASCS
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
AP_INVOICE_LINES_ALL AILA,
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_CHECKS_ALL ACA,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XE,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_DISTRIBUTION_LINKS XDL,
GL_IMPORT_REFERENCES GIR,
GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL
where
PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND
PRLA.ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND
PLLA.SHIP_TO_ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND
PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND
PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND
PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND
PLLA.PO_LINE_ID=PLA.PO_LINE_ID
AND
PLA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND
PHA.VENDOR_ID=ASS.VENDOR_ID
AND
ASS.VENDOR_ID=ASSA.VENDOR_ID
--AND
--ASSA.VENDOR_SITE_ID=ASCS.VENDOR_SITE_ID
AND
PDA.PO_DISTRIBUTION_ID=RT.PO_DISTRIBUTION_ID
AND
RT.TRANSACTION_TYPE='RECEIVE'
AND
RT.SHIPMENT_LINE_ID=RSL.SHIPMENT_LINE_ID
AND
RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
AND
PDA.PO_DISTRIBUTION_ID=AILA.PO_DISTRIBUTION_ID
AND
AILA.INVOICE_ID=AIA.INVOICE_ID
AND
AIA.INVOICE_ID=AIDA.INVOICE_iD
AND
AIDA.INVOICE_ID=AIPA.INVOICE_ID
AND
AIPA.CHECK_ID=ACA.CHECK_ID
AND
XTE.TRANSACTION_NUMBER=AIA.INVOICE_NUM
AND
XTE.ENTITY_ID=XE.ENTITY_ID
AND
XE.EVENT_ID=XAH.EVENT_ID
AND
XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND
XAL.AE_LINE_NUM=AIDA.INVOICE_LINE_NUMBER
AND
XE.EVENT_ID=XDL.EVENT_ID
AND
XAH.AE_HEADER_ID=XDL.AE_HEADER_ID
AND
XAL.AE_LINE_NUM=XDL.AE_LINE_NUM
AND
GIR.REFERENCE_5=XTE.ENTITY_ID
AND
GIR.REFERENCE_6=TO_CHAR(XE.EVENT_ID)
AND
GIR.REFERENCE_7=TO_CHAR(XAH.AE_HEADER_ID)
AND
GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND
GIR.JE_BATCH_ID=GJB.JE_BATCH_ID
AND
GJB.JE_BATCH_ID=GJH.JE_BATCH_ID
AND
GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND
GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND
GJL.JE_LINE_NUM=GIR.JE_LINE_NUM
AND
PRHA.SEGMENT1=2259;