Procure to Pay (P2P) Technical Flow

 P2P cycle flows through the following stages

 

·       Creating Requisition

·       Requisition Approval

·       Creating Purchase Order

·       Purchase Order Approval

·       Creating Invoice and Validating

·       Creation of Payment

·       Transfer and Import Journal to General Ledger

 

Tables Involved at each step:

Requisition:

POR_REQUISITION_HEADERS_ALL

POR_REQUISITION_LINES_ALL

POR_REQ_DISTRIBUTIONS_ALL

 

Approval:

PO_ACTION_HISTORY

 

Purchase Order:

PO_HEADERS_ALL

PO_HEADERS_ARCHIVE_ALL

PO_HEADERS_DRAFT_ALL

 

PO_LINES_ALL

PO_LINES_ARCHIVE_ALL

PO_LINES_DRAFT_ALL

PO_LINE_LOCATIONS_ALL

 

PO_DISTRIBUTIONS_ALL

PO_DISTRIBUTIONS_ARCHIVE_ALL

PO_DISTRIBUTIONS_DRAFT_ALL

 

Invoice:

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

 

AP_INVOICE_PAYMENTS_ALL

AP_INV_APRVL_HIST_ALL

 

Payment:

AP_CHECKS_ALL

AP_HOLDS_ALL

AP_PAYMENT_SCHEDULES_ALL

AP_PAYMENT_HISTORY_ALL

 

General Ledger Transfer:

GL_JE_HEADERS

GL_JE_LINES

GL_IMPORT_REFERENCES

 

XLA_AE_HEADERS

XLA_AE_LINES

XLA_DISTRIBUTION_LINKS

 

Some other useful tables

PO_VERSIONS

POZ_SUPPLIERS

PO_VENDORS

PO_VENDOR_SITES_ALL

PO_VENDOR_CONTACTS_ALL

PO_AGENTS_V

PO_LOOKUP_CODES

AP_LOOKUP_CODES

AP_HISTORY_INVOICES_ALL

AP_HISTORY_CHECKS_ALL

 

P2P Flow chart:

 

 

P2P Query:

 SELECT PRH.REQUISITION_NUMBER,

            PRL.LINE_NUMBER REQ_LINE_NUM,

            PAH.LAST_UPDATED_BY APPROVER,

            PHA.SEGMENT1 PO_NUMBER,

            PLA.LINE_NUM PO_LINE_NUM,

            AIA.INVOICE_NUM,

            AIA.INVOICE_AMOUNT,

            NVL((

                                    SELECT SUM(AMOUNT)

                                    FROM AP_INVOICE_LINES_ALL

                                    WHERE INVOICE_ID = AIA.INVOICE_ID

                                                AND LINE_TYPE_LOOKUP_CODE = 'TAX'

                                    ), 0) TAX,

            ACA.CHECK_NUMBER,

            AIPA.AMOUNT AMOUNT_PAID,

            (

                        SELECT APPROVER_ID

                        FROM (

                                    SELECT AIAH.APPROVER_ID

                                    FROM AP_INV_APRVL_HIST_ALL AIAH

                                    WHERE AIPA.INVOICE_ID = AIAH.INVOICE_ID

                                                AND AIAH.RESPONSE LIKE '%APPROVED'

                                    ORDER BY LAST_UPDATE_DATE

                                    )

                        WHERE ROWNUM = 1

                        ) INVOICE_APPROVER,

            XDL.UNROUNDED_ACCOUNTED_CR,

            XDL.UNROUNDED_ACCOUNTED_DR,

            XAL.AE_LINE_NUM,

            GJH.NAME JOURNAL_NAME,

            GJL.JE_LINE_NUM

FROM POR_REQUISITION_HEADERS_ALL PRH,

            POR_REQUISITION_LINES_ALL PRL,

            PO_ACTION_HISTORY PAH,

            POR_REQ_DISTRIBUTIONS_ALL PRD,

            PO_DISTRIBUTIONS_ALL PDA,

            PO_LINES_ALL PLA,

            PO_HEADERS_ALL PHA,

            AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

            AP_INVOICES_ALL AIA,

            AP_INVOICE_PAYMENTS_ALL AIPA,

            AP_CHECKS_ALL ACA,

            -- AP_INV_APRVL_HIST_ALL AIAH,

            XLA_DISTRIBUTION_LINKS XDL,

            XLA_AE_HEADERS XAH,

            XLA_AE_LINES XAL,

            GL_JE_LINES GJL,

            GL_IMPORT_REFERENCES GIR,

            GL_JE_HEADERS GJH

WHERE 1 = 1

            AND AIA.INVOICE_NUM = :P_INVOICE_NUM

            AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

            AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID

            AND PAH.OBJECT_ID = PRH.REQUISITION_HEADER_ID

            AND PAH.SEQUENCE_NUM = (

                        SELECT MAX(SEQUENCE_NUM)

                        FROM PO_ACTION_HISTORY PAH1

                        WHERE PAH1.OBJECT_ID = PAH.OBJECT_ID

                                    AND PAH1.OBJECT_TYPE_CODE = 'REQ'

                                    AND PAH1.ACTION_CODE = 'APPROVE'

                        )

            AND PRD.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID

            AND PLA.PO_LINE_ID = PDA.PO_LINE_ID

            AND PLA.PO_HEADER_ID = PDA.PO_HEADER_ID

            AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID

            AND PDA.PO_DISTRIBUTION_ID = AIDA.PO_DISTRIBUTION_ID

            AND AIDA.INVOICE_ID = AIA.INVOICE_ID

            AND AIDA.OBJECT_VERSION_NUMBER = (

                        SELECT MAX(OBJECT_VERSION_NUMBER)

                        FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA1

                        WHERE AIDA1.INVOICE_ID = AIDA.INVOICE_ID

                        )

            AND AIPA.CHECK_ID = ACA.CHECK_ID

            AND AIPA.INVOICE_ID = AIA.INVOICE_ID

            AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID

            AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

            AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

            AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

            AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

            AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

            AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

            AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

            AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID