Showing posts with label joins. Show all posts
Showing posts with label joins. Show all posts

Monday, November 2, 2020

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