P2P & O2C Technical Flow

                                 P2P Backend FLOW

Indent

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1=5671

SELECT * FROM PO_REQUISITION_LINES_ALL

WHERE REQUISITION_HEADER_ID = (SELECT REQUISITION_HEADER_ID FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='5671')

PO Related

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1=4500

SELECT * FROM PO_LINES_ALL

WHERE PO_HEADER_ID= (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1=4500)

SELECT * FROM PO_LINE_LOCATIONS_ALL

WHERE PO_HEADER_ID=(SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1=4500)

SELECT * FROM PO_DISTRIBUTIONS_ALL

WHERE PO_HEADER_ID=(SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1=4500).

Receipt Information

 SELECT * FROM RCV_SHIPMENT_HEADERS    WHERE RECEIPT_NUM =9621

SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID IN (SELECT SHIPMENT_HEADER_ID FROM RCV_SHIPMENT_HEADERS  

        WHERE RECEIPT_NUM =9621 AND SHIPMENT_HEADER_ID=69428)

Select * from RCV_TRANSACTIONS

          Where PO_HEADER_ID IN (SELECT PO_HEADER_ID   FROM PO_HEADERS_ALL

                                   WHERE SEGMENT1=4500) 

SELECT * FROM mtl_material_transactions

WHERE RCV_TRANSACTION_ID IN (select TRANSACTION_ID

    From RCV_TRANSACTIONS  Where PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM

                       PO_HEADERS_ALL WHERE SEGMENT1=4500))

        

            AP Invoice related

SELECT * FROM AP_INVOICES_ALL  WHERE INVOICE_NUM=4579

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID IN (SELECT INVOICE_ID

                       FROM AP_INVOICES_ALL WHERE INVOICE_NUM='4579')

Payment Related

 SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM AP_INVOICES_ALL WHERE INVOICE_NUM='4579')

SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM AP_INVOICES_ALL

        WHERE INVOICE_NUM='4579')

SELECT * FROM AP_PAYMENT_DISTRIBUTIONS_ALL WHERE INVOICE_PAYMENT_ID IN (SELECT INVOICE_PAYMENT_ID  FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM AP_INVOICES_ALL  WHERE INVOICE_NUM='4579'))

SELECT * FROM AP_CHECKS_ALL   WHERE CHECK_ID=28698  

à Corresponding vendor_id and bank_account is related to that particular vendor

Bank Reconciliation in Cash Management

SELECT STATEMENT_HEADER_ID FROM CE_STATEMENT_HEADERS_ALL

WHERE STATEMENT_HEADER_ID =75826

SELECT * FROM CE_STATEMENT_LINES WHERE STATEMENT_HEADER_ID IN (SELECT STATEMENT_HEADER_ID FROM CE_STATEMENT_HEADERS_ALL

WHERE STATEMENT_HEADER_ID =75826)

GL Related

Ø  After these Invoice’s are transferred to GL by running

  Payables Transfer to General Ledger  PROGRAM

The tables affected are

    GL_JE_BATCHES

    GL_JE_HEADERS

    GL_JE_LINES    

                                           O2C FLOW

Order Related

SELECT * FROM oe_order_headers_all WHERE order_number = 57803

SELECT * FROM oe_order_lines_all WHERE header_id = (SELECT header_id FROM oe_order_headers_all WHERE order_number = 57803);

Once order is booked and pick release is next step

 SELECT * FROM wsh_delivery_details WHERE source_header_id = (SELECT header_id FROM oe_order_headers_all WHERE order_number = 57803)

Once order is pick confirm

SELECT delivery_id FROM wsh_delivery_assignments WHERE delivery_detail_id =

(SELECT delivery_detail_id FROM wsh_delivery_details WHERE source_header_id = (SELECT header_id FROM oe_order_headers_all WHERE order_number = 57803))

SELECT * FROM wsh_new_deliveries WHERE delivery_id = 211904

SELECT * FROM mtl_material_transactions WHERE transaction_id IN (11262482, 11262483)

    Once Move order is transacted

SELECT header_id FROM mtl_txn_request_headers WHERE request_number = '187252'

       SELECT * FROM mtl_txn_request_lines WHERE header_id = 209289

Once order is ship confirmed

Interface trip stop program automatically launches and order line status gets interfaced, and delivery id status is closed.

SELECT * FROM wsh_delivery_legs WHERE delivery_id = 211904

SELECT * FROM wsh_trip_stops WHERE stop_id IN (216963, 216964)

SELECT * FROM wsh_trips WHERE trip_id = 179789

Inventory Interface- SRS updates inventory with the ship confirmation information.

Inventory balances are decremented and reservations relieved. This program always spawns the Order Management Interface - SRS program. It is very important in the process flow that the Inventory Interface complete before the Order Management Interface to ensure the integrity of the reservation information.

Inventory Interface picks records from MTI (MTL_TRANSACTIONS_INTERFACE) and inserts them into MMTT (MTL_MATERIAL_TRANSACTIONS_TEMP). The Inventory Manager then processes rows and inserts them in to MTL_MATERIAL_TRANSACTIONS.

Order Management  Interface runs to update Order Management with the ship confirmation information. Order line shipped quantities will be updated.

At the end of ship confirm, invoice lines are created in Ra_interface_lines_all table.

Once Ship Confirm is done, Workflow background process runs, and inserts data in below table

Auto Invoice Master program runs to generate entry in AR tables

SELECT * FROM ra_interface_lines_all where interface_line_attribute1 = <order_number>

AR Invoices Related Tables

SELECT * FROM Ra_customer_trx_lines_all where interface_line_attribute1 = <order_number>

SELECT * FROM Ra_customer_trx_all where trx_header_id = <trx_header_id> (trx_header_id fetched from Ra_customer_trx_lines_all)

AR Receipt Table

Select * from ar_cash_receipts_all

Select * from ar_receivable_applications

Transfer TO GL

Run General Ledger Transfer Program, to transfer Accounting info in to GL.

Select * from GL_INTERFACE

Journal Import

Run Journal Import program, to import GL interface data into GL Tables.

GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES :- While importing GL , data in these tables will be in UN Posted Form.

Post IN GL

Once GL Batch is posted, Data will be finally available in

Select * from GL_BALANCES

Order to Cash Cycle - Tables get Affected @ Each Step

1) Order Entry
------------------------------------
oe_order_headers_all.flow_status_code = 'ENTERED'
oe_order_headers_all.booked_flag = 'N'

oe_order_lines_all.flow_status_code = 'ENTERED'
oe_order_lines_all.booked_flag = 'N'
oe_order_lines_all.open_flag = 'Y'

2) Order Booking
------------------------------------
oe_order_headers_all.flow_status_code = 'BOOKED'
oe_order_headers_all.booked_flag = 'Y'

oe_order_lines_all.flow_status_code = 'AWAITING SHIPPING'
oe_order_lines_all.booked_flag = 'Y'

wsh_delivery_details.released_status = 'R' (ready to release)

wsh_delivery_assignments.delivery_id = BLANK

3) Reservation
------------------------------------
mtl_demand

mtl_reservations

4) Pick Release
------------------------------------
wsh_new_deliveries.status_code = 'OP'

wsh_delivery_assignments.delivery_id gets assigned

wsh_delivery_details.released_status = 'S' (submitted for release)

mtl_txn_request_headers

mtl_txn_request_lines

mtl_material_transactions_temp

5) Pick Confirm
------------------------------------
oe_order_lines_all.flow_status_code = 'PICKED'

mtl_material_transactions

wsh_delivery_details.released_status = 'Y' (Released)

mtl_onhand_quantities

6) Ship Confirm
------------------------------------
Data will be removed from wsh_new_deliveries

oe_order_lines_all.flow_status_code = 'SHIPPED'

wsh_delivery_details.released_status = 'C' (Shipped)

wsh_serial_numbers

data will be deleted from mtl_demand and mtl_reservations

item qty gets deducted from mtl_onhand_quantities

7) Enter Invoice
------------------------------------
After shipping the order, workflow backgroud engine picks up records and post it to

ra_interface_lines_all

Auto invoice program picks up records from interface table and insert them into

ra_customer_trx_all (trx_number is invoice number)

ra_customer_trx_lines_all (line_attribute_1 = Order number and line_attribute_6 = Order line id)

8) Complete Line
------------------------------------
oe_order_lines_all.flow_status_code = 'SHIPPED'
oe_order_lines_all.open_flag = 'N'

9) Close Order
------------------------------------
oe_order_headers_all.flow_status_code = 'CLOSED'

oe_order_lines_all.flow_status_code = 'CLOSED'
oe_order_lines_all.open_flag = 'N'

10) Create Receipt
------------------------------------
AR_CASH_RECEIPTS_ALL

11) Transfer to General Ledger
------------------------------------
GL_INTERFACE

12) Journal Import
------------------------------------
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES

13) Posting
------------------------------------
GL_BALANCES