order management tables and its details (O2C Cycle )

This is available in metalink and many internet sites. Since I felt it handy reference, I have reproduced the contents.

Order Management Tables.
Entered
oe_order_headers_all 1 record created in header tableoe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked
oe_order_headers_all Booked_flag=Y Order booked.wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactionsmtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.wsh_new_deliveries if Auto-Create is Yes then data populated.wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from stagedmtl_material_transactions On the ship confirm form, check Ship all boxwsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N
Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program forra_customer_trx_lines_all specific batch transaction tables get populated
Price Detailsqp_list_headers_b To Get Item Price Details.
qp_list_lines

Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.

Customer Information
hz_parties Get Customer information include name,contacts,Address and Phonehz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers

Document Sequence
fnd_document_sequences Document Sequence Numbersfnd_doc_sequence_categories
fnd_doc_sequence_assignments

Default rules for Price List
oe_def_attr_def_rules Price List Default Rulesoe_def_attr_condns
ak_object_attributes

End User Details
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)oe_sales_credits

Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions

Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.

Cancel Ordersoe_order_lines_all Cancel Order Details.

Order Management, Receivables and General Ledger Cross Flow

Order Management to Receivables Flow

If you are using Oracle Order Entry, no accounting information is available until you run AutoInvoice. Order Management Transfers all the transactions to Oracle Receivables using the Receivables Interface. When you run AutoInvoice that creates the actual transactions and uses AutoAccounting to derive the segment values for the GL Accounts.
Whether you are manually entering your receipts or processing them through AutoLockbox, the accounting information is automatically determined by Oracle Receivables when you create and apply the receipts (not when it is still a “QuickCash” batch). The values used are based on the setup values for the bank where the receipts were deposited and the invoices they are paying.

General Ledger Interface

GL Interface Transfers accounting information from Oracle Receivables to Oracle General Ledger. If you have properly set up Oracle Receivables, you should never have to create manual journal entries in your General Ledger and the two systems should always be in sync.
When you invoke the General Ledger Interface process, you initiate multiple programs that:

• Finds all of the records for the period you specified that have not yet been passed to the General Ledger;
• Determines if the debits equal the credits;
• Passes the data to GL for editing; and
• Marks the records as having been passed (so they will not pass twice).
• If you have specified that you want the Journal Import to also run, this process verifies that the individual segments and combinations of segments are valid. Only when the Journal Import completes successfully are the Journals available for posting.

Always run the General Ledger Interface using the starting date of the period through the last day of the period. This is applicable no matter when you are running the process or if you know you will never have activity for that date, since sometimes the system uses dates other than the dates you expect.
Depending on which patches you have applied, you may or may not see the Unposted Items Report. If this report does run, always check each page to ensure that you have no items that could not be passed to the General Ledger. If anything besides headings appears, work with your IT department to resolve (since this is usually caused by a bug).
Verify that the amounts in the General Ledger Interface Report are reasonable and that the debits equal the credits.
Verify that the Journal Import has a status of “SUCCESS.” If not, you had a problem that will need to be resolved or none of the items in the batch will be available for posting. Generally you have a problem if an account was valid when the activity was created, as you know, you cannot save with invalid values but, someone has since disabled either a segment or the combination. An example of this is your Accounts Receivable account that may have been valid when the invoice was originally created but it is not longer valid, and a receipt was just applied against it. When you apply a receipt to an invoice it always causes an offsetting entry against the original Accounts Receivable account.
Should this occur, I suggest that you:

• Re-enable the segment or combination;
• Re-run the Journal Import (in GL — be sure to include the applicable id);
• Create a manual journal entry (also in GL) to move the activity from the bad account to the proper account (this is my one exception to never creating manual journal entries); and
• Re-disable the segment or combination.

By making the corrections in this way you are able to keep your GL in sync with your AR activity and you have an audit trial of what you did to make the correction. You have the option to correct in the Import Corrections form (in GL), but you lose the audit trail of what you did and why. I also recommend noting what you did and why and storing the notes in a handy binder so you will be prepared when the auditors ask why you did what you did.

Journal Entries Reports

The Journal Entries reports are the best way to verify the actual accounting for Oracle Receivables’ activities and the only way to view the accounting for the foreign currency gains and losses. There are actually four reports that give you varying levels of details regarding the journal entries you will be creating or have already created. These reports may be run at anytime before or after you run the General Ledger Interface. Your options are: Detail by Account (very large), Summary by Account, Detail by Category (also large) and Summary by Category.
I always recommend that you at least run the Summary by Category and review to insure that there are no invalid or illogical accounts, prior to running the General Ledger Interface. If you find funny accounts, you can correct or create offsetting entries prior to posting. Run the Detail by Category (just for that category and account) to see which specific activities used the funny account. Correct the activity if possible. If not possible (i.e., adjustment), create an offsetting entry using the proper account.
If you run this report for Unposted Items only, you must leave the Posted Date range blank or nothing will appear on the report

Order To Cash Techinical Overview

Enter order à Book Order à Pick Release à Pick Slip à Confirm Shipment àGenerate Invoice à Customer follow up à Receipt à [Final Reconciliation]

Steps in Cycle:

1. Order Entry

This is first stage, when the order is entered in the system; it creates a record in order headers and Order Lines table.

• Enter header details: Once you enter details on the order header and save it or move it to lines, record goes to one table OE_ORDER_HEADERS_ALL

FLOW_STATUS_CODE = ENTERED,

BOOKED_FLAG = N),

Primary key=HEADER_ID

No record exists in any other table for this order till now.

• Enter Line details for this order: Enter different item numbers, quantity and other details in line tab. When the record gets saved, it goes to one table. Order header details will be linked with line details by order HEADER_ID.

OE_ORDER_LINES_ALL

FLOW_STATUS_CODE = ENTERED,

BOOKED_FLAG = N,

OPEN_FLAG = Y

Primary key= LINE_ID

2. Order Booking

This is next stage, when Order is booked then the Flow status changed from Entered to Booked.At this stage, these below table get affected.

• OE_ORDER_HEADERS_ALL (FLOW_STATUS_CODE as BOOKED, BOOKED_FLAG updated to Y)
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE as AWAITING_SHIPPING, BOOKED_FLAGupdated Y)
• WSH_DELIVERY_DETAILS (DELIVERY_DETAIL_ID is assigned here, RELEASED_STATUS ‘R’ ready to release, LINE_ID comes as SOURCE_LINE_ID)
• WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ASSIGNMENT_ID is assigned for DELIVERY_DETAIL_ID present in WSH_DELIVERY_DETAILS, DELIVERY_ID remains blank till this stage)

In shipping transaction form order status remains "Ready to Release".

At the same time, Demand interface program runs in background And insert into inventory tables MTL_DEMAND, here LINE_ID come as a reference in DEMAND_SOURCE_LINE

3. Reservation

This step is required for doing reservations SCHEDULE ORDER PROGRAM runs in the background and quantities are reserved. Once these programs get successfully get completed.

• MTL_DEMAND andMTL_RESERVATIONS table get updated. LINE_ID gets updated in DEMAND_SOURCE_LINE_ID in both the tables.

4. Pick Release

Pick Release is the process of putting reservation on on-hand quantity available in the inventory and pick them for particular sales order.

Pick release can be done from 'Release Sales Order' form or 'Pick release SRS' program can be scheduled in background. In both of these cases all lines of the order gets pick released depending on the Picking rule used. If specific line/s needs to be pick release it can be done from 'Shipping Transaction form. For this case Pick Release is done from 'Release Sales Order' form with Pick Confirm=NO.
Once pick release is done these are the tables get affected:

· If step 3 is not done then MTL_RESERVATIONS gets updated now.

· WSH_NEW_DELIVERIES (one record gets inserted with SOURCE_HEADER_ID= order header ID, STATUS_CODE=OP =>open)

· WSH_DELIVERY_ASSIGNMENTS (DELIVERY_ID gets assigned which comes from WSH_NEW_DELIVERIES)

· WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘S’ ‘submitted for release’)

· MTL_TXN_REQUEST_HEADERS

· MTL_TXN_REQUEST_LINES (LINE_ID goes as TXN_SOURCE_LINE_ID)

· (move order tables. Here request is generated to move item from Source (RM or FG) sub-inventory to staging sub-inventory)

· MTL_MATERIAL_TRANSACTIONS_TEMP (link to above tables through MOVE_ORDER_HEADER_ID/LINE_ID, this table holds the record temporally)

· MTL_SERIAL_NUMBERS_TEMP (if item is serial controlled at receipt then record goes in this table)

· MTL_SERIAL_NUMBERS (enter value in GROUP_MARK_ID )

*In shipping transaction form order status remains "Released to Warehouse" and all the material still remains in source sub-inventory. We need to do Move Order Transaction for this order. Till this no material transaction has been posted to MTL_MATERIAL_TRANSACTIONS

5. Pick Confirm/ Move Order Transaction

Items are transferred from source sub-inventory to staging Sub-inventory. Here material transaction occurs.

Order line status becomes 'Picked' on Sales Order and 'Staged/Pick Confirmed' on Shipping Transaction Form.

• MTL_MATERIAL_TRANSACTIONS_TEMP (Record gets deleted from here and gets posted to MTL_MATERIAL_TRANSACTIONS)
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘PICKED’ )
• MTL_MATERIAL_TRANSACTIONS (LINE_ID goes as TXN_SOURCE_LINE_ID)
• MTL_TRANSACTION_ACCOUNTS
• WSH_DELIVERY_DETAILS (RELEASED_STATUS becomes ‘Y’ => ‘Released’ )
• WSH_DELIVERY_ASSIGNMENTS
• MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')
• MTL_SERIAL_NUMBERS (record gets inserted after putting details for the item which are serial controlled at 'Sales order issue')

* This step can be eliminated if we set Pick Confirm=YES at the time of Pick Release

6. Ship Confirm

Here is the ship confirm interface program runs in background. Data removed from

• WSH_NEW_DELIVERIES. The items on the delivery shipped to customer at this stage
• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’)
• WSH_DELIVERY_DETAILS (RELEASED_STATUS ‘C’ ‘Shipped’, SERIAL_NUMBER if quantity is ONE)
• WSH_SERIAL_NUMBERS (records gets inserted with the DELIVERY_DETAIL_ID reference, only in case of shipped quantity is two or more)
• MTL_TRANSACTION_INTERFACE
• MTL_MATERIAL_TRANSACTIONS (linked through Transaction source header id)
• MTL_TRANSACTION_ACCOUNTS
• Data deleted from MTL_DEMAND, MTL_RESERVATIONS
• Item deducted from MTL_ONHAND_QUANTITIES
• MTL_SERIAL_NUMBERS_TEMP (records gets deleted from this table)
• MTL_SERIAL_NUMBERS (Serial number stauts gets updated CURRENT_STATUS=4 , 'Issued out of store')

7. Enter Invoice

After shipping the order, order lines are eligible to transfer to RA_INTERFACE_LINES_ALL. Workflow background engine picks those records and post it to RA_INTERFACE_LINES_ALL. This is also called Receivable interface, this mean information moved to accounting area for invoicing details. Invoicing workflow activity transfers shipped item information to Oracle Receivables. At the same time records also goes in the table RA_INTERFACE_SALESCREDITS_ALL which hold details of sales credit for the particular order

• RA_INTERFACE_LINES_ALL (interface table into which the data is transferred from order management) Then Autoinvoice program imports data from this table which get affected into this stage are receivables base table. At the same time records goes in
• RA_CUSTOMER_TRX_ALL (CUST_TRX_ID is primary key to link it to TRX_LINES table and TRX_NUMBER is the invoice number)
• RA_CUSTOMER_TRX_LINES_ALL (LINE_ATTRIBUTE_1 and LINE_ATTRIBUTE_6 are linked to order number and LINE_ID of the orders)

8. Complete Line

In this stage order line level table get updated with Flow status and open flag.

• OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘shipped’, OPEN_FLAG “N”)

9. Close Order

This is last step of Order Processing. In this stage only OE_ORDER_LINES_ALL table get updated. These are the table get affected in this step.

OE_ORDER_LINES_ALL (FLOW_STATUS_CODE ‘closed’, OPEN_FLAG “N”)

Internal orders

Internal orders normally start with creation of requisitions in Oracle Purchasing (which is created as Internal Sales Orders) .Once the requisitions created from the MRP and Inventory modules, the Requisition Import program must be run in order to move records from the requisition interface tables to the historical PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL.

Now next is to create internal requisitions from Purchasing module.When each requisition is approved, data will be inserted into the MTL_SUPPLY table. When the items on the requisition are stocked in inventory, the supply data can be viewed from the inventory form called Item Supply/Demand.

Once the Internal Requisition is approved, a user should run Create Internal Sales Orders (from a Purchasing responsibility) to load the internal sales orders interface tables called in Order Entry.

After the Creation of Internal Sales Orders has completed, a Import Orders concurrent program need to run from an Order Management. at this stage, Oracle order Workflow will process the internal sales order using the workflow definition listed for the Transaction Type definition in Order Management. When the workflow completes the Shipping Network is checked to see if the Transfer Type between the source and destination organization is In transit or Direct.

You can use Oracle Order Entry/Shipping to define order cycles, approvals and holds.Internal Requisitions use the Account Generator, which automatically builds account distributions. You can specify only one accounting distribution per inventory sourced line.

This internal requisition process provides the features needed to define your inter- organization shipping network. For transfers between two organizations, you can specify whether to use in transit or direct shipments. You can also require internal requisitions for transfers between specific organization.

ORDER MANAGEMENT BASICS

1) What are the Base Tables and Interface Tables for Order Management?Interface Tables : OE_HEADERS_IFACE_ALL, OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL, OE_ACTIONS_IFACE_ALL
OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)
Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information
OE_ORDER_LINES_ALL: Items Information
OE_PRICE_ADJUSTMENTS: Discounts Information
OE_SALES_CREDITS: Sales Representative Credits.
Shipping Tables :WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS, WSH_DELIVERIES.

2) What is Order Import and What are the Setup's involved in Order Import?
A) Order Import is an open interface that consists of open interface tables and a set of API’s. It imports New, updated, or changed sales orders from other applications such as Legacy systems. Order Import features include validations, Defaulting, Processing Constraints checks, Applying and releasing of order holds, scheduling of shipments, then ultimately inserting, updating or deleting orders from the OM base tables. Order management checks all the data during the import process to ensure its validity with OM. Valid Transactions are then converted into orders with lines, reservations ,price adjustments, and sales credits in the OM base tables.
B) Setups:
· Setup every aspect of order management that we want to use with imported orders, including customers, pricing, items, and bills.
· Define and enable the order import sources using the order import source window.

3) Explain the Order Cycle?i) Enter the Sales Order
ii) Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
iii) Release sales order(Pickslip Report is generated and Deliveries are created)
(Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.
iv) Transaction Move Order (creates reservations determines the source and transfers the inventory into the staging areas)
v) Launch Pick Release (
vi) Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))

4) Explain the Order to Cash Flow?
I. Enter the Sales Order
II. Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
III. Release sales order(Pickslip Report is generated and Deliveries are created)
(Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.
IV. Transaction Move Order (Selects the serial number of the product which has to be moved/ shipped)
V. Launch Pick Release
VI. Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))
VII. AutoInvoice (Creation of Invoice in Accounts Receivable Module)
VIII. Autolockbox ( Appling Receipts to Invoices In AR)
IX. Transfer to General Ledger ( Populates GL interface tables)
X. Journal Import ( Populates GL base tables)
XI. Posting ( Account Balances Updated).

5. What are the Process Constraints?A. Process Constraints prevent users from adding updating, deleting, splitting lines and canceling order or return information beyond certain points in the order cycle. Oracle has provided certain process constraints which prevent data integrity violations.
Process constraints are defined for entities and attributes. Entities include regions on the sales order window such as order, line, order price adjustments, line price adjustments, order sales credits and line sales credits. Attributes include individual fields (of a particular entity) such as warehouse, shit to location, or agreement.

6. What are Validation Templates?
A) Validation Templates are used to define the validation conditions in process constraints. A validation template names a conditions and defines the semantic of how to validate that condition. These are used in processing constraints framework to specify the constraining conditions for a given constraint. These conditions are based on
Where the entity is in its work flow.
The state of attributes on an entity.
Any other validation condition that cannot be modeled using the above condition.

7. What are different types of Holds?· GSA(General Services Administration) Violation Hold(Ensures that specific customers always get better pricing for example Govt. Customers)
· Credit Checking Hold( Used for credit checking feature Ex: Credit Limit)
· Configurator Validation Hold ( Cause: If we invalidate a configuration after booking)

8. What is Document Sequence?
A) Document sequence is defined to automatically generate numbers for your orders or returns as you enter them. Single / multiple document sequences can be defined for different order types.
Document sequences can be defined as three types Automatic (Does not ensure that the numbers are contiguous), Gapless (Ensures that the numbering is contiguous), Manual Numbering. Order Management validates that the number specified is unique for order type.

9. What are Defaulting Rules?A) A defaulting rule is a value that OM automatically places in an order field of the sales order window. Defaulting rules reduce the amount of information one must enter. A defaulting rule is a collection of defaulting sources for objects and their attributes.
It involves the following steps
· Defaulting Conditions - Conditions for Defaulting
· Sequence – Priority for search
· Source – Entity ,Attribute, Value
· Defaulting source/Value

10. When an order cannot be cancelled?
A) An order cannot be cancelled if,
· It has been closed
· It has already been cancelled
· A work order is open for an ATO line
· Any part of the line has been shipped or invoiced
· Any return line has been returned or credited.

11. When an order cannot be deleted?A) you cannot delete an order line until there is a need for recording reason.

12. What is order type?
A) An order type is the classification of order. It controls the order work flow activity, order number sequence, credit check point and transaction type. Order Type is associated to a work flow process which drives the processing of the order.

13. What are primary and secondary price lists?A) Every order is associated to a price list as each item on the order ought to have a price. A price list is contains basic list information and one or more pricing lines, pricing attributes, qualifiers, and secondary price lists. The price list that is primarily associated to an order is termed as Primary price list.
The pricing engine uses a Secondary Price list if it cannot determine the price of the item ordered in the Primary price list.

14. What is pick slip? Types?
A) It is an internal shipping document that pickers use to locate items to ship for an order.
· Standard Pick Slip – Each order will have its own pick slip with in each picking batch.
· Consolidated Pickslip – Pick slip will have all the orders released in the each picking batch.

15. What is packing slip?A) It is an external shipping document that accompanies the shipment itemizing the contents of the shipment.

16. What are picking rules?
A) Picking rules define the sources and prioritization of sub inventories, lots, revisions and locators when the item is pick released by order management. They are user defined set of rules to define the priorities order management must use when picking items from finished goods inventory to ship to a customer.

17. Where do you find the order status column?A) In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.

18. When the order import program is run it validates and the errors occurred can be seen in?A) Responsibility: Order Management Super User
Navigation: Order, Returns > Import Orders > Corrections

Ship To address and Bill to Address Qry

SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'SHIP_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.ship_to_org_id

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT hp.party_name
FROM hz_parties hp
,hz_party_sites site
,hz_locations loc
,hz_party_site_uses uses
,fnd_territories_vl terr
,hz_cust_site_uses hcsua
,hz_cust_acct_sites hcasa
WHERE site.party_id = hp.party_id
AND site.location_id = loc.location_id
AND site.party_site_id = uses.party_site_id(+)
AND loc.country = terr.territory_code
AND hp.status = 'A'
AND site.status = 'A'
AND hcsua.status = 'A'
AND uses.site_use_type = 'BILL_TO'
AND NVL (uses.status, 'A') = 'A'
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hcasa.party_site_id = site.party_site_id
AND hcsua.site_use_code = uses.site_use_type
AND hcsua.site_use_id = ol.invoice_to_org_id

‘Drop Shipment’, ‘BackOrders’ and ‘Back to Back Order’

Back Orders

·         The Oracle "term" backorder is a "status" on the order line or delivery line indicating that you have tried to release an order for picking in your warehouse, but that the pick release was UNSUCCESSFUL because there was no available inventory.(Backorder can be partial or complete). The Oracle term backorder does NOT mean that you have open purchase orders for the out-of-stock item from your vendors.

·         The term backorder is also used in business a little differently than in Oracle. The term "An item is on backorder" usually means that the item is not in stock, but the shipping company has already placed purchase orders from their suppliers to restock the item.

·         Back Order is when you do not fulfill the Sales Order, or if the inventory is out of stock for delivery to customer.

Back to Back Orders (B2B)

In Drop-ship items are directly shipped to customer from the supplier and only logical receiving is performed in Oracle. In B2B orders items are physically received to Oracle from supplier and later they are shipped to customers.

Ex: When an order for Laptop is placed, you cannot send laptop and charger differently to the customer. If the company is not interested in maintaining the inventory of chargers, B2B is perfect solution as laptop charger order will go out when ever an order is created for laptop.And the charger is received to oracle and can be shipped with the Laptop.

Flow status code of the order line --FSC
Item reservation type ….IRT

1. Enter sales order …source code Internal
2. Book the order, at this time FSC – Supply Eligible
3. Perform progress order … and FSC –PO Req. Requested & IRT inventory
4. Req. Import –FSC –PO Req. Created & IRT external requisition
5. Auto Create PO –FSC – PO created & IRT PO order
6. Perform receiving transaction--- FSC Awaiting shipping& IRT Inventory
After this complete the order as normal sales order.

Important Notes:
Items used in Back to back order should be ATO enabled, Build in WIP flag checked and in general planning set the Buy flag.
In B2B order at some point we will physically receive goods before shipping them out, where as in Drop ship goods are directly shipped to Customer
Drop ship order may connect to more than one PO but B2B is connected to single PO.

SetupDocument Link:

http://functionalguy.blogspot.com/2009/02/back-to-back-order-cycle.html

Drop Shipment

It means you can receive orders for items that you do not stock or for which you lack sufficient inventory, and have a supplier provide the items directly to your customer
.

• Drop shipment on the other hand is a method of order fulfillment where the organization taking the order does NOT maintain their own inventory for the drop-shipped product, but fulfill their orders through 3rd party vendors who directly ship to the end customer ordering the product.

For example,

• A orders item x from B
• B orders item x from C
• C ships Item x to A.
• B bills A for the order, C bills B for the order

Drop Shipment - Process Steps
• Create a Sales Order with line where the line source is External
• Book and Schedule the Sales Order this will populate data in OE_DROP_SHIP_SOURCES
• The Purchase Release program this will populate data into PO_REQUISITIONS_INTERFACE_ALL
• Run Requisition Import Process PO_REQUISITION_HEADERS_ALL/ LINES/DISTRIBUTION
• Now the line status will be in Awaiting Receipt
• Login to the Receiving Organization (Purchasing) who has been setup as an Approver
• Create Purchase Order from the Requisition , populates data into PO tables
• Approve the PO
• Receive the full quantity
• Run Auto Invoice
• Verify Invoice in Sales Order

Things not to forget in a Drop Shipment

• Release 11i/12 does not support Drop Shipment across operating units.
• Blanket PO's will not used with Drop Shipment, the reason the PO must be created when OM notifies PO that a Drop Ship order has been created.
• You can't cancel Drop Shipments once Oracle Purchasing obtains the receipt.
• Standard Items can be used for Drop Shipment.

TABLES Involved
• PO_LINE_LOCATIONS_ALL
o This table stores document shipment schedules for purchase orders, purchase agreements, quotations, and RFQs.
o This table stores purchase document lines for purchase orders, purchase agreements, quotations, and RFQs.
• PO_HEADERS_ALL/ PO_LINES_ALL/ PO_DISTRIBUTIONS_ALL
• PO_REQUISITION_HEADERS_ALL/ PO_REQUISITION_LINES_ALL/ PO_REQ_DISTRIBUTIONS_ALL
• PO_REQUISITIONS_INTERFACE_ALL
o This is the Requisition Import interface table.
• OE_ORDER_LINES_ALL/OE_ORDER_LINES_ALL/OE_DROP_SHIP_SOURCES
o This table stores relationships between order lines in OE_ORDER_LINES_ALL table and associated oracle purchasing requisitions in PO_REQUISITIONS_ALL and Oracle purchasing purchase orders in PO_LINES_ALL.
• RCV_SHIPMENT_LINES
o This table stores receiving shipment line information

Drop Shipment - Setup You need to make sure these are attribute setup Correctly:

• Item Attributes

• Purchased : Enabled
• Purchasable : Enabled
• Transactable : Enabled
• Stockable : Optional
• Reservable : Optional
• Inventory Item : Optional
• Customer Ordered : Enabled
• Customer Order Enabled : Enabled
• Internal Ordered : Disabled
• OE Transactable : Enabled
• Shippable : Optional

Difference between “Internal” and “External” Drop-Ship

In Oracle Context External Drop-Shipping means your Oracle Order Management uses purchase orders to outside suppliers that are automatically generated from sales orders for goods supplied directly from the supplier. The “external ” supplier ships the goods directly to the 3rd Party customer and confirms the shipment through the use of an Advanced Shipment Notice(ASN). Note:Oracle uses this ASN to record a receiving transaction into inventory followed by an immediate logical shipping transaction. From these transactions, conveyance of title takes place and the customer can be invoiced and the supplier’s invoice can be processed. where as “Internal” context Drop-Shipping functions in a similar fashion. The key difference is that no inventory transactions take place on the books of the selling operating unit; transfer of ownership of the goods from shipper to seller to customer with the only physical movement of the goods being out of the shipping organization.