Oracle SCM: Important Tables of P2P Cycle

Here all important tables of Oracle SCM module for P2P (Procure to Pay) Cycle.

Process flow on very high level.

Create ITEM ->  Create PR - > Create Suppliers -> Create PO -> Create Receipts -> Create INVOICE -> Payments -> Create Accounting -> Post to GL.

ITEM/INVENTORY

---------------------------------------------------------------------------------------------------------

Once we create an Item in Oracle, below are the tables/view where data will be populated.

SELECT * FROM MTL_SYSTEM_ITEM_B WHERE SEGMENT1=’ITEM1’;

SELECT * FROM MTL_SYSTEM_ITEM WHERE SEGMENT1=’ITEM1’;

SELECT * FROM MTL_ONHAND_QUANTITIES_DETAILS --- To get the ON HAND quantity of an Item.

PURCHASE REQUISITION

---------------------------------------------------------------------------------------------------------

Once we create a new REQUISITION , below tables will be populated.

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1=’PR Number’

SELECT * FROM PO_ REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=1234

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=3214

SELECT * FROM GL_CODE_COMBINATION WHERE CODE_COMBINATION_ID=12313

SUPPLIERS

---------------------------------------------------------------------------------------------------------

Once we create an Suppliers in Oracle, below are the tables/view where data will be populated.

SELECT * FROM AP_SUPPLIERS WHERE SEGMENT1=’Suppliers1’

SELECT * FROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_ID=1212

SELECT * FROM AP_SUPPLIER_CONTACTS

Below tables also have the same information , these are views in R12 but base table in below versions.

SEELCT * FROM PO_VENDORS WHERE SEGMENT1=’Suppliers1’

SELECT * FROM PO_VENDOR_SITES_ALL WHERE VENDOR_ID=1212

SELECT * FROM PO_VENDOR_CONTACTS

Below tables are TCA(Trading Community Architecture) tables.

SELECT * FROM HZ_PARTIES WHERE PARTY_NAME=’Supplier Name’

SELECT * FROM HZ_PARTY_SITES WHERE PARTY_ID=1223

PURCHASE ORDER

---------------------------------------------------------------------------------------------------------

Once we create a new PO , below tables will be populated.

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1=’PO Number’;

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID=1212;

SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=2123;

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_LINE_LOCATION_ID=343;

RECEIPTS

---------------------------------------------------------------------------------------------------------

SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM=’121’

SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPENT_HEADER_ID=’233’; ---- To get the how much quantity has been received.

SELECT * FROM RCV_TRANSACTIONS WHERE SHIPENT_HEADER_ID=’233’ --- To get the received and delivered quantity.

INVOICE

---------------------------------------------------------------------------------------------------------

SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM=’Invoice Num’;

SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=1212;

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=1212; ---- ACCOUNTING_EVET_ID will be populated once Invoice is validated.

PAYMENTS

---------------------------------------------------------------------------------------------------------

SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOIVE_ID=1233;

SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOIVE_ID=1233;

SELECT * FROM AP_CHECKS_ALL;

ACCOUNTING

---------------------------------------------------------------------------------------------------------

SELECT * FROM XLA_EVENTS WHERE EVENT_ID=1212;  -- All accounting related events (LIKE VALIDATION) will be stored in this table.

Once we run an accounting event, oracle runs multiple programs behind the screen.

1.       Create Accounting : This program will transfer the data from Sub-ledger to GL interface tables.

2.       Journal Import: This one will move the data from GL interface table to GL  base tables.

SELECT * FROM XLA_AE_HEADERS WHERE EVENT_ID=1223;

SELECT * FROM XLA_AE_LINES WHERE AE_HEADER_ID=121;

SELECT * FROM GL_INTERFACE WHERE REFERENECE26=’Event Id’;  ----This is GL interface table. Once Journal Import program runs data will move from this table to GL base table. You won’t find any record after that.

GL LEDGERS

---------------------------------------------------------------------------------------------------------

SELECT * FROM GL_JE_BATCHES WHERE NAME =’Batch Name’;  --- We will get this Batch Name from Journal Import program output.

SELECT * FROM GL_JE_HEADERS  WHERE JE_BATCH_ID=123;

SELECT * FROM GL_JE_LINES  WHERE JE_HEADER_ID=434;

GL POSTING

---------------------------------------------------------------------------------------------------------

SELECT * FROM GL_BALANCES;

Enjoy & Stay Well !! :)