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

Oracle Shipping pick release status

Below post will give you the details about the pick release status

Instance: 11i or R12

Table: WSH_DELIVERY_DETAILS

Column: RELEASED_STATUS

Possible Values:

B: Backordered- Line failed to be allocated in Inventory
C: Shipped -Line has been shipped
D: Cancelled -Line is Cancelled
N: Not Ready for Release -Line is not ready to be released
R: Ready to Release: Line is ready to be released
S: Released to Warehouse: Line has been released to Inventory for processing
X: Not Applicable- Line is not applicable for Pick Release
Y: Staged- Line has been picked and staged by Inventory

Delivery line statuses in detail

 Not Applicable (Code X)  

The delivery line can be invoiced but non-shippable, for example, a service line or a warranty line.

Not Ready for Release (Code N)

 The delivery line is not eligible for pick release. This happens when the order line is manually imported into Oracle Shipping Execution using the Import Delivery Line concurrent process or the corresponding order line has not reached the Awaiting Shipping workflow activity.

Ready for Release (Code R)

  The delivery line is eligible for pick release.  Occurs when the order line has reached the Awaiting Shipping workflow activity (it is booked, scheduled, and in Oracle Shipping Execution).

Submitted to Warehouse (Code S)

Pick release has processed the delivery line and has:

1.       Created move order headers and lines.

2.       Found available quantity and created inventory allocations.

3.       Not pick confirmed. If you are using auto-pick confirm, it changes release status to Staged. If you are not using auto-pick confirm and want to progress the delivery lines, navigate to Oracle Inventory Move Order Transaction window and perform manual pick confirm.

Staged (Code Y)

 The delivery line is pick confirmed; inventory is transferred from storage sub-inventory to staging sub-inventory.  It remains staged until ship confirm.

 Backordered (Code B)

Some of the circumstances that can causes this status are listed below

Ø  Pick release has processed the delivery line and cannot find the entire quantity.  This typically occurs when the Oracle Inventory indicates that there is not enough material (either because there is not enough material or because the inventory balance is incorrect).  

Ø  At ship confirm, you: Enter Shipped Quantity that is less than Original Requested Quantity Backorder the entire delivery quantity transfer a reservation to cycle count.

Ø  This typically occurs when the material that you want to ship:

1.       Has become unavailable, for example, damaged, between picking and shipping.

2.       Is available and you backorder material for specific business reasons. For example, all available material has been allocated to a specific customer when you find out additional supply for other orders will be delayed.

  Shipped (Code C)

  The delivery line’s delivery is ship confirmed and posted as in-transit, OM Interface and Inventory Interface have processed, and the trip is closed.

  Cancelled (Code D)

  The order line that the delivery line supports is cancelled.

Sales Order Lines Information In Oracle Apps R12

 

select * from apps.oe_order_lines_all

where header_id=oe_order_headers_all.header_id

Transactional currency code

select ota.price_list_id, qhb.currency_code

from ont.oe_transaction_types_all ota, qp.qp_list_headers_b qhb

where ota.transaction_type_id = oe_order_headers_all.order_type_id

and ota.price_list_id = qhb.list_header_id(+)

and NVL(qhb.list_type_code, ‘PRL’) = ‘PRL’

and qhb.currency_code =oe_order_headers_all.transactional_curr_code

Item info

select * from apps.mtl_system_items_b

where segment1 like oe_order_lines_all.ordered_item

and organization_id=oe_order_lines_all.ship_from_org_id

UOM

select uom_code from inv.mtl_units_of_measure_tl

where upper(uom_code)= upper(oe_order_lines_all.order_quantity_uom)

and language= ‘US’ and nvl(disable_date, (sysdate + 1)) > sysdate

Item type code validation

select lookup_code from apps.oe_lookups

where upper(lookup_type) = ‘ITEM_TYPE’

and enabled_flag = ‘Y’

and upper(lookup_code)= oe_order_lines_all.item_type_code

On hand quantities

select * from apps.mtl_onhand_quantities

where inventory_item_id=oe_order_lines_all.inventory_item_id

and organization_id=oe_order_lines_all.ship_from_org_id

Shipping

select * from wsh_delivery_details

where source_header_id=oe_order_headers_all.header_id

select * from wsh_delivery_assignments

where delivery_detail_id=wsh_delivery_details.delivery_detail_id

select * from wsh_new_deliveries

where delivery_id=wsh_delivery_assignments.delivery_id

select * from wsh_delivery_legs

where delivery_id=wsh_new_deliveries.delivery_id

select * from wsh_trip_stops wts

where stop_id=wsh_delivery_legs.pick_up_stop_id

select * from wsh_trips wt

where trip_id=wsh_trip_stops.trip_id

select * from org_organization_definitions

where organization_id = wsh_new_deliveries.organization_id

Material transactions

select * from mtl_material_transactions

where inventory_item_id=oe_order_lines_all.inventory_item_id

and organization_id=oe_order_lines_all.ship_from_org_id

select * from mtl_transaction_types

where transaction_type_id = mmt.transaction_type_id

select * from apps.mtl_txn_source_types

where transaction_source_type_id= mmt.transaction_source_type_id

mmt = mtl_material_transactions

Please share your comments

Sales Order Header Information in Oracle Apps

Select * from oe_order_headers_all ooh

where order_number= :p_order_number

Operating unit info

select * from hr_operating_units

where organization_id=oe_order_headers_all.org_id

Order type info

select * from apps.oe_transaction_types_tl

where transaction_type_id=oe_order_headers_all.order_type_id

Price list info

select * from apps.qp_list_headers_tl

where list_header_id=oe_order_headers_all.price_list_id

select * from apps.qp_list_lines

where list_header_id=oe_order_headers_all.price_list_id

Find customer info

select * from hz_cust_accounts hca, hz_parties hp, oe_order_headers_all

where  hp.party_id=hca.party_id

and hca.cust_account_id=oe_order_headers_all.sold_to_org_id

Find Ship to location info

select * from hz_cust_site_uses_all

where site_use_id=oe_order_headers_all.ship_to_org_id

select * from apps.hz_cust_acct_sites_all

where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id

select * from hz_party_sites

where party_site_id=hz_cust_acct_sites_all.party_site_id

Find Bill to location

select * from hz_cust_site_uses_all

where site_use_id=oe_order_headers_all.invoice_to_org_id

select * from hz_cust_acct_sites_all

where cust_acct_site_id=hz_cust_site_uses_all.cust_acct_site_id

select * from hz_party_sites

where party_site_id=hz_cust_acct_sites_all.party_site_id

actual address

select * from hz_locations

where location_id=hz_party_sites.location_id

Sales rep id

select name from apps.ra_salesreps_all salerep  where

salesrep_id = oe_order_headers_all.salesrep_id  and rownum =1

Payment terms

select name from apps.ra_terms_tl

where term_id =oe_order_headers_all.payment_term_id

and language = ‘US’

Order source

select name from apps.oe_order_sources

where order_source_id= oe_order_headers_all.order_source_id

and enabled_flag= ‘Y’

Order Source Reference

select orig_sys_document_ref from oe_order_headers_all ooh

where order_number=’&oracle order number’

FOB Point Code

select lookup_code from ar_lookups

where lookup_type = ‘FOB’ and enabled_flag = ‘Y’

and upper(meaning) = upper(oe_order_headers_all.fob_point_code)

Freight terms

select lookup_code from apps.oe_lookups

where upper (lookup_type) = ‘FREIGHT_TERMS’  and enabled_flag = ‘Y’

and upper (lookup_code) = upper (oe_order_headers_all.freight_terms_code)

For sales channel code validation

select lookup_code from apps.oe_lookups

where lookup_type = ‘SALES_CHANNEL’ and enabled_flag = ‘Y’

upper(lookup_code) = upper(oe_order_headers_all.sales_channel_code)

Ship method

select ship_method_code from wsh.wsh_carrier_services

where ship_method_code = oe_order_headers_all.shipping_method_code

Warehouse Info

select * from org_organization_definitions

where organization_id = oe_order_headers_all.ship_from_org_id

Please Share Your Comments