Oracle Order Management: Drop Ship Tables and SQL Query

Below are the basic tables used during Drop Ship flow

apps.oe_order_headers_all 

apps.oe_order_lines_all

apps.oe_drop_ship_sources

apps.po_requisitions_interface_all

apps.po_interface_errors

apps.po_requisition_headers_all

apps.po_headers_all

apps.po_lines_all

apps.po_line_locations_all

apps.po_distibutions_all

apps.po_releases_all

Below is the SQL Statement to find out the Drop Ship SO and corresponding Requistiona and PO details along with their status

selectooh.order_number

       ,ool.ordered_item

       ,ool.ordered_quantity

       ,ooh.flow_status_code header_status                   

       ,ool.flow_status_code line_status

       ,prha.segment1 requisition

       ,poh.segment1 po_number

       ,poh.closed_code po_status

       ,pll.quantity

       ,pll.quantity_received

       ,pll.closed_code po_shipment_status

fromapps.oe_order_headers_all                   ooh

    ,apps.oe_order_lines_all                     ool

    ,apps.oe_drop_ship_sources                   odss

    ,apps.po_requisition_headers_all             prha

    ,apps.po_headers_all                         poh

    ,apps.po_lines_all                           pol

    ,apps.po_line_locations_all                  pll

              

whereool.header_id = ooh.header_id

and   odss.header_id = ooh.header_id

and   odss.line_id = ool.line_id

and   prha.requisition_header_id = odss.requisition_header_id

and   poh.po_header_id = odss.po_header_id

and   pol.po_line_id = odss.po_line_id

and   pol.po_header_id = poh.po_header_id

and   pll.po_line_id = pol.po_line_id

and   ooh.order_number = ‘66411’;

Oracle Order Management: Drop Ship Flow

Setting up the Item first

Master Level:

Inventory:

Inventory Item, Stockable, Transactable

Purchasing:

Purchased, Purchasable, List Price

Order Management:

Customer Ordered, Customer Order Enabled, Shippable, OE Transactable

Default SO Source Type should be External

Internal Ordered, Internal Order Enabled (These 2 if the drop ship will happen from internal organization)

Invoicing

Organization Level Setup:

Inventory

Costing

Receiving

Now create the Sales Order

Make sure source type is External

Now Book the order

Now the line is in Booked

Workflow is deferred for Purchase Release

Run the workflow Background

After the program completes, re query the SO

Now the time, you can see the record both in Drop Ship table and Po Req Interface table

select * from apps.oe_order_headers_all whereorder_number = ‘66411’;

select * from apps.OE_DROP_SHIP_SOURCES whereheader_id = 190500;

select * from apps.po_requisitions_interface_all where creation_date >= sysdate–1/24;

Now run the Requisition Import Program

If this program not able to create the requisition, then please check the error if any in below table

select * from apps. PO_INTERFACE_ERRORS where transaction_id =

Once the program, completes, lets check the Req details in Sales order line, additional Information , Drop Ship tab

PO Creation

Now take this Requisition and Auto Create PO from this using the Purchasing responsibility

Click Automatic

Select the supplier and Site and click Create

This will create the PO

Now check the details and Submit for Approval

Wait till you get the confirmation from supplier for the shipments either by ASN or any EDI communication.

Now you can see this PO details in SO Line, Drop Ship tab

PO Receiving

Once confirmed, then go ahead and do the receipt

Once received, check the transaction completion.

Once the receiving transaction completed, go and check the SO line status

Now run the workflow background program, this will close the line

This will interface the record to Receivable

Now run the Autoinvoice Master program to create the Receivable Invoice

Oracle Order Management: Drop Ship flow

1. Create an Item, should be Customer ordered, customer orderable, purchased, purchase order enabled
2. Give the buyer details
3. OM: Population of Buyer Code for Drop Ship should be Order Creator
4. Now add the buyer Employee to the user who will crete the PO
5. Create a sales order, line source type should be external(this can be setiup at item level too)
6. Once you book the order, workflow will be deferred.
7. Run the Workflow background, this will change the line status to Awaiting Receipt
8. Run the PO Requistion Import program from the PO responsibility with parameter Source as ORDER ENTRY and Initiate Approval after ReqImport as Yes.
9. Go to the sales order and in additoional information, drop ship tab, you can see the PO/PO req details
10.Go to Purchasing –> Autocreate, Goahead and create the PO Document and Approve the same.
11.Now go and receive the PO, this will automatically trigger the Sales order issue transaction for the drop ship SO

Order to Cash Lifecycle: Drop Shipment

Drop Shipments

Drop Shipment allows a supplier to fulfill a sales order where the seller is not required to handle stock or deliver the items. This also helps in meeting urgent customer requirements.

Drop shipment occurs when a customer needs an item urgently and which is generally not stocked by the seller or customer requires large quantity which is not there in the seller’s warehouse. If it is more economical to ship the goods directly from the supplier to customer, then also Drop Shipment process used.

There are 3 types of Drop Shipments:

Full Drop Shipment:

Seller send the purchase orders to the supplier for the items that customer has ordered.

Normal Shipment and Partial Drop Shipment:

If the seller has only part of the quantity order, the seller supplies the available quantity to the customer and creates a purchase order for the rest of the quantity, which supplier can send to the customer.

Normal Shipment and Full Drop Shipment:

Seller ships some goods from the inventory to the customer. The rest of the goods are shipped from the supplier after creating a purchase order.

Steps:

·         Drop shipments are created as Sales Order but it can be identified when the source type is External.

·         The Purchase Release concurrent program or workflow in Order Management creates records in the Requisition import tables in the purchasing module. This creates requisitions and once these are approved, it generates the purchase order to supplier where the source type is Supplier.

·         This Purchase Order sent to the supplier who will arrange the delivery of the item to the customer via an Invoice, EDI document or an ASN (Advanced Shipment Notice). Then Seller will generate the invoice to customer.

·         In addition to this, when the seller receives the confirmation of the drop shipment or receives the invoice from the supplier, it creates a receipt. It creates the inbound and outbound transactions for the accounting purpose.

Order to Cash Lifecycle: Drop Shipment

Drop Shipments

Drop Shipment allows a supplier to fulfill a sales order where the seller is not required to handle stock or deliver the items. This also helps in meeting urgent customer requirements.

Drop shipment occurs when a customer needs an item urgently and which is generally not stocked by the seller or customer requires large quantity which is not there in the seller’s warehouse. If it is more economical to ship the goods directly from the supplier to customer, then also Drop Shipment process used.

There are 3 types of Drop Shipments:

Full Drop Shipment:

Seller send the purchase orders to the supplier for the items that customer has ordered.

Normal Shipment and Partial Drop Shipment:

If the seller has only part of the quantity order, the seller supplies the available quantity to the customer and creates a purchase order for the rest of the quantity, which supplier can send to the customer.

Normal Shipment and Full Drop Shipment:

Seller ships some goods from the inventory to the customer. The rest of the goods are shipped from the supplier after creating a purchase order.

Steps:

·         Drop shipments are created as Sales Order but it can be identified when the source type is External.

·         The Purchase Release concurrent program or workflow in Order Management creates records in the Requisition import tables in the purchasing module. This creates requisitions and once these are approved, it generates the purchase order to supplier where the source type is Supplier.

·         This Purchase Order sent to the supplier who will arrange the delivery of the item to the customer via an Invoice, EDI document or an ASN (Advanced Shipment Notice). Then Seller will generate the invoice to customer.

·         In addition to this, when the seller receives the confirmation of the drop shipment or receives the invoice from the supplier, it creates a receipt. It creates the inbound and outbound transactions for the accounting purpose.