P2P Cycle in Oracle Apps with Tables

   P2PCycle:-

  

  TABLES:-

REQUISITION:-

                        Navigation:- Purchasing, Vision Operation(USA) => Requisitions => Requisitions

1.       PO_REQUISITION_HEADERS_ALL

2.       PO_REQUISITION_LINES_ALL

3.       PO_REQ_DISTRIBUTIONS_ALL

     SELECT *

     FROM PO_REQUISITION_HEADERS_ALL prha,

          PO_REQUISITION_LINES_ALL prla,

          PO_REQ_DISTRIBUTIONS_ALL prda

     WHERE prha.REQUISITION_HEADER_ID = prla.REQUISITION_HEADER_ID   

     AND prla.REQUISITION_LINE_ID = prda.REQUISITION_LINE_ID

     AND SEGMENT1 = '14531' ;   -- Requisition Number

RFQ:-

       Navigation:- Purchasing, Vision Operation(USA) => RFQ’s and Quotations => RFQ’s

1.       PO_HEADERS_ALL

2.       PO_LINES_ALL

3.       PO_LINE_LOCATIONS_ALL

4.       PO_REF_VENDORS

SELECT *

FROM PO_HEADERS_ALL pha,

     PO_LINES_ALL pla,

     PO_LINE_LOCATIONS_ALL plla

WHERE pha.PO_HEADER_ID  = pla.PO_HEADER_ID

AND pha.ORG_ID  = pla.ORG_ID

AND pla.PO_HEADER_ID  = plla.PO_HEADER_ID

AND pla.PO_LINE_ID  = plla.PO_LINE_ID

AND pLa.ORG_ID  = plla.ORG_ID

AND pha.SEGMENT1 = ‘329’  -- RFQ Number

AND pha.TYPE_LOOKUP_CODE = 'RFQ';

 RFQ Supplier Information:-  “PO_RFQ_VENDORS”

                  where po_header_id(PO_RFQ_VENDORS) link to po_header_id(PO_HEADERS_ALL).

      SELECT *

      FROM PO_RFQ_VENDORS

           WHERE PO_HEADER_ID = 114255;

QUOTATION:-

                         Navigation:- Purchasing, Vision Operation(USA) => RFQ’s and Quotations => Quotation

1.       PO_HEADERS_ALL

2.       PO_LINES_ALL

3.       PO_LINE_LOCATIONS_ALL

SELECT *

FROM PO_HEADERS_ALL pha,

     PO_LINES_ALL pla,

     PO_LINE_LOCATIONS_ALL plla

WHERE pha.PO_HEADER_ID  = pla.PO_HEADER_ID

AND pha.ORG_ID  = pla.ORG_ID

AND pla.PO_HEADER_ID  = plla.PO_HEADER_ID

AND pla.PO_LINE_ID  = plla.PO_LINE_ID

AND pLa.ORG_ID  = plla.ORG_ID

AND pha.SEGMENT1 = ‘531’   -- Quotation Number

AND pha.TYPE_LOOKUP_CODE = 'QUOTATION'; 

Note:- When you Approved Quotation and analysis it then data insert into

 “PO_QUOTATION_APPROVALS_ALL” table, where line_location_id link to line_location_id from PO_LINE_LOCATIONS_ALL table.

PURCHASE ORDER:-

        Navigation:- Purchasing, Vision Operation(USA) => Purchase Orders => Purchase Orders

1.       PO_HEADERS_ALL

2.       PO_LINES_ALL

3.       PO_LINE_LOCATIONS_ALL

4.       PO_DISTRIBUTIONS_ALL

SELECT *

          FROM PO_HEADERS_ALL pha,

               PO_LINES_ALL pla,

               PO_LINE_LOCATIONS_ALL plla,

               PO_DISTRIBUTIONS_ALL PDA

          WHERE pha.PO_HEADER_ID  = pla.PO_HEADER_ID

          AND pha.ORG_ID  = pla.ORG_ID

          AND pla.PO_HEADER_ID  = plla.PO_HEADER_ID

          AND pla.PO_LINE_ID  = plla.PO_LINE_ID

          AND pLa.ORG_ID  = plla.ORG_ID

          AND pha.PO_HEADER_ID  = pda.PO_HEADER_ID

          AND pla.PO_LINE_ID  = pda.PO_LINE_ID

          AND plla.LINE_LOCATION_ID  = pda.LINE_LOCATION_ID

          AND pla.ORG_ID  = pda.ORG_ID

         AND pha.segment1  = '7566';   -- PO Number

Receipt:-

                Navigation:- Purchasing, Vision Operation(USA) => Purchase Orders => Purchase Orders

1.       RCV_TRANSACTIONS

2.       RCV_SHIPMENT_HEADERS

3.       RCV_SHIPMENT_LINES

           SELECT *

           FROM RCV_TRANSACTIONS rt,

                RCV_SHIPMENT_HEADERS rsh,

                RCV_SHIPMENT_LINES rsl

           WHERE rt.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID

           AND rt.SHIPMENT_LINE_ID = rsl.SHIPMENT_LINE_ID

          AND rsh.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID

          AND rt.PO_HEADER_ID = pha.PO_HEADER_ID

          AND pha.segment1= '7566';   -- PO Number

AP INVOCE:-

            Navigation:- Payables, Vision Operatoon(USA) => Invoices => Entry => Invoices

1.       AP_INVOICES_ALL

2.       AP_INVOICE_LINES_ALL

3.       AP_INVOICE_DISTRIBUTIONS_ALL

       

        SELECT *

  FROM AP_INVOICES_ALL aia,

     AP_INVOICE_LINES_ALL aila,

     AP_INVOICE_DISTRIBUTIONS_ALL aida

  WHERE aia.INVOICE_ID = aila.INVOICE_ID

  AND aia.INVOICE_ID = aida.INVOICE_ID

  AND aila.LINE_NUMBER = aida.INVOICE_LINE_NUMBER

  AND aia.INVOICE_NUM = '19879-781';   -- Invoice Number

AP PAYMENT:-

            Navigation:- Payables, Vision Operatoon(USA) => Payments => Entry => Payments

1.        AP_CHECKS_ALL

2.       AP_PAYMENT_SCHEDULES

3.       AP_PAYMENT_HIST_DISTS

GL TABLES:-

1.       XLA_DISTRIBUTION_LINKS

2.       XLA_AE_HEADERS

3.       XLA_AE_LINES

4.       XLA_EVENTS

5.       XLA_TRANSACTION_ENTITIES

6.       GL_IMPORT_REFERENCES

7.       GL_JE_HEADERS

8.       GL_JE_LINES

9.       GL_JE_BATCHES

10.   GL_BALANCES       

SELECT XAL.*

       FROM XLA_DISTRIBUTION_LINKS xdl,

            XLA_AE_LINES xal,

            XLA_AE_LINES xah,

            XLA_EVENTS xe

       WHERE xdl.AE_HEADER_ID = xah.AE_HEADER_ID

       AND xdl.AE_HEADER_ID = xaL.AE_HEADER_ID

       AND xdl.AE_LINE_NUM = xal.AE_LINE_NUM

       AND xdl.EVENT_ID = xe.EVENT_ID;

       SELECT *

       FROM GL_IMPORT_REFERENCES gir,

            GL_JE_HEADERS gjh,

            GL_JE_LINES gjl,

            GL_JE_BATCHES gjb

        WHERE gir.JE_HEADER_ID = gjh.JE_HEADER_ID

        AND gir.JE_HEADER_ID = gjl.JE_HEADER_ID

        AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM

        AND gir.JE_BATCH_ID = gjb.JE_BATCH_ID;

Link between XLA and GL tables:-

       SELECT *

       FROM XLA_DISTRIBUTION_LINKS xdl,

            XLA_AE_LINES xal,

            XLA_AE_LINES xah,

            XLA_EVENTS xe,

            GL_IMPORT_REFERENCES gir,

            GL_JE_HEADERS gjh,

            GL_JE_LINES gjl,

            GL_JE_BATCHES gjb

       WHERE xdl.AE_HEADER_ID = xah.AE_HEADER_ID

       AND xdl.AE_HEADER_ID = xaL.AE_HEADER_ID

       AND xdl.AE_LINE_NUM = xal.AE_LINE_NUM

       AND xdl.EVENT_ID = xe.EVENT_ID

       AND xal.GL_SL_LINK_ID = gir.GL_SL_LINK_ID

       AND xal.GL_SL_LINK_TABLE = gir.GL_SL_LINK_TABLE

       AND gir.JE_HEADER_ID = gjh.JE_HEADER_ID

       AND gir.JE_HEADER_ID = gjl.JE_HEADER_ID

       AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM

       AND gir.JE_BATCH_ID = gjb.JE_BATCH_ID;