O2C Tables in Oracle Apps

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 

SELECT *

  FROM oe_order_headers_all
 WHERE order_number = 66161;

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

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

SELECT *
  FROM wsh_delivery_assignments
 WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161));

SELECT *
  FROM wsh_new_deliveries
 WHERE delivery_id IN (
          SELECT delivery_id
            FROM wsh_delivery_assignments
           WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161)));

SELECT *
  FROM ra_customer_trx_all
 WHERE (interface_header_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162));

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE (interface_line_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162));

SELECT *
  FROM ra_customer_trx_lines_all
 WHERE (interface_line_attribute3) IN (
          SELECT TO_CHAR (delivery_id)
            FROM wsh_delivery_assignments
           WHERE delivery_detail_id IN (
                         SELECT delivery_detail_id
                           FROM wsh_delivery_details
                          WHERE source_header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66161)))

SELECT *
  FROM ar_cash_receipts_all
 WHERE cash_receipt_id IN (
          SELECT cash_receipt_id
            FROM ar_payment_schedules_all
           WHERE customer_trx_id IN (
                    SELECT DISTINCT customer_trx_id
                               FROM ra_customer_trx_all
                              WHERE (interface_header_attribute1 =
                                        (SELECT TO_CHAR (order_number)
                                           FROM oe_order_headers_all
                                          WHERE header_id =
                                                   (SELECT header_id
                                                      FROM oe_order_headers_all
                                                     WHERE order_number =
                                                                         66162))
                                    )));

SELECT *
  FROM ar_receivable_applications_all
 WHERE customer_trx_id =
          (SELECT customer_trx_id
             FROM ra_customer_trx_all
            WHERE (interface_header_attribute1) =
                             (SELECT TO_CHAR (order_number)
                                FROM oe_order_headers_all
                               WHERE header_id = (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162)));

SELECT *
  FROM ar_cash_receipts_all
 WHERE cash_receipt_id =
          (SELECT cash_receipt_id
             FROM ar_receivable_applications_all
            WHERE customer_trx_id =
                     (SELECT customer_trx_id
                        FROM ra_customer_trx_all
                       WHERE (interface_header_attribute1) =
                                (SELECT TO_CHAR (order_number)
                                   FROM oe_order_headers_all
                                  WHERE header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162))));

SELECT *
  FROM gl_code_combinations
 WHERE code_combination_id =
          (SELECT code_combination_id
             FROM ar_receivable_applications_all
            WHERE customer_trx_id =
                     (SELECT customer_trx_id
                        FROM ra_customer_trx_all
                       WHERE (interface_header_attribute1) =
                                (SELECT TO_CHAR (order_number)
                                   FROM oe_order_headers_all
                                  WHERE header_id =
                                                 (SELECT header_id
                                                    FROM oe_order_headers_all
                                                   WHERE order_number = 66162))));