Skip to main content

Oracle

Oracle Drop Ship Automation – A Technical Perspective #C17LV

The purpose of this blog post is to share how to setup/customize the Oracle Drop Ship process to automatically create Purchase Orders originating from the Drop Ship Sales order lines.

Standard Process:

Oracle’s out-of-the-box functionality involves creation of a sales order in Oracle Order Management with sales order lines set to source type as External. Once an order is booked in the system, it is purchase released by the sales person or order entry user. This initiates a background workflow that interfaces the demand into a purchase requisition in Oracle Purchasing. Once the requisition is imported manually, it is ready for creation of a Purchase Order using Auto Create functionality, which is a manual process since the buyer has to determine the sourcing on the Purchase Order creation process. After the appropriate sourcing information about suppliers and supplier sites is provided, the Purchase Order is created and approved and later received when the delivery is completed by the supplier directly to the customer location.

While the above process works great on a typical Oracle implementation, our client could not handle the volume of the requisitions and Purchase Orders manually in a reasonable amount of time. An automated solution was needed to bridge the gap between the Oracle Order Management representative and the buyer especially for the suppliers that were known to the sales person upfront.

Custom Process:

  • Setup and configure Supplier/Supplier Site:
     Setup a Descriptive Flexfield (DFF) on Supplier to enable/disable a supplier for Auto Drop ship process.
     Identify the participating suppliers and set Auto Drop Ship flag DFF to Y.
     Set the vendor site notification method to EMAIL in order to facilitate automatic run of PO output for communication.
  • Setup and configure Sales Order Line DFF and Value Sets:
     Setup value set driven off of vendor setups above.
     Utilize the value set(s) created above in the creation of Sales Order Line DFF.
  • Setup and configure Drop Ship profile options
  • Setup and define profile options:
    Default Buyer: The buyer associated with the Automatic Purchase Order creation
    Emergency PO Number: This profile determines if the emergency PO number functionality should be used to create the Purchase Order with predefined                  numbering scheme. If buyer needs to review the PO via a manual process at some point, this can be disabled
  1. Simple use of triggers: The automated solution mainly relies on triggers on the table OE_DROP_SHIP_SOURCES table which is populated when a drop ship sales order line created. The trigger gets the required values entered on the sales order line (Q1) and updates the PO_REQUISITIONS_INTERFACE table with order line DFF data (Q2) when requisitions are created after the Purchase Release process, which ties the two together.

Q1:

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

select
max(substr(ool.attribute16,1,instr(ool.attribute16,’~’,1)-1)),
max(substr(ool.attribute16,instr(ool.attribute16,’~’,1)+1)),
max(to_char(ooh.order_number)),
max(ool.attribute17),
max(wcs.attribute1) ship_method_suffix,
max(msi.list_price_per_unit)
into
g_suggested_vendor_name,
g_suggested_vendor_Site_code,
g_po_number,
l_unit_price_char,
g_ship_method_suffix,
l_item_list_price
from
oe_order_lines_all ool,
oe_order_headers_all ooh,
wsh_carrier_ship_methods wcs,
mtl_system_items_b msi
where
ooh.header_id = ool.header_id
and ooh.header_id = p_header_id
and ool.shipping_method_code= wcs.ship_method_code (+)
and ool.ship_from_org_id = wcs.organization_id(+)
and ool.inventory_item_id = msi.inventory_item_id (+)
and ool.ship_from_org_id = msi.organization_id (+)
and ool.line_id = p_line_id
and exists (select
‘Requisition interface’
from
po_requisitions_interface_all
where
interface_source_line_id = p_drop_ship_source_id)

Q2:

update
po_requisitions_interface_all
set
unit_price = nvl(g_unit_price,unit_price),
suggested_buyer_id = nvl(g_suggested_buyer_id,suggested_buyer_id),
autosource_flag = nvl(g_autosource_flag,autosource_flag),
suggested_vendor_name = nvl(g_suggested_vendor_name,suggested_vendor_name),
suggested_vendor_site = nvl(g_suggested_vendor_site_code,suggested_vendor_site),
emergency_po_num = decode(g_em_po_num_enabled_flag,’Y’,
nvl(g_po_number,emergency_po_num),
emergency_po_num),
req_number_segment1 = decode(g_em_po_num_enabled_flag,’Y’,
nvl(g_po_number,req_number_segment1),
req_number_segment1)
where
interface_source_line_id = p_drop_ship_source_id
and interface_source_code = c_interface_source_code
and process_flag is null;

2. Leverage Standard Oracle Flow: Standard Oracle Purchasing comes with an emergency PO functionality which automatically kicks off PO Create and Approval workflow without manual intervention. The pre-requisite for triggering this is emergency_po_num column in the po_requisitions_interface table.

Learn More at Collaborate

This is a great extension with minimum customization to achieve the automation of the drop ship process in Oracle E-Business Suite. This solution resulted in tremendous time saving for our customer and streamlined their supply chain. This solution has scope for further enhancements using the functionality as a foundation to tailor the needs of any future customer.

If you’re headed to Collaborate 17 in Las Vegas, April 2-6, I invite you to stop by my presentation Accelerating the Drop Ship Sales Flow Saved the Day on Wednesday, April 5th from 11:00am – 12:00pm.  While you are there, please also check out Perficient’s other must-attend sessions at the show!

Thoughts on “Oracle Drop Ship Automation – A Technical Perspective #C17LV”

  1. Siddarth Mandela

    Hello Amit,

    Good work done. I am just curious to know about the custom process you have detailed above. We have a similar process and we use ASL’s in Purchasing module and this is where you can also setup to Autocreate PO or manual release. I am wondering why wouldn’t take the standard path for your solution.
    Will be good to learn.

    Thanks

    Siddarth M

  2. Amit Pandya Post author

    The solution we implemented did not have structured ASLs for the client. They were more interested in determining the sourcing needs by the salesrep taking the order. ASLS would be more appropriate when you know how and when you would source from rather than adhoc determination for my client needs.
    We can look more into standard ASLs to get that information from OM to avoid trigger solution.

  3. Excellent write-up.We have a scenario where the PO Auto Approval is done using the Custom Drop Ship Process but the PO XML is not getting generated as the Generation Message in Outbound shows as Pending.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Amit Pandya

Amit has over 20 years experience in design and implementation of  solutions for Oracle’s Cloud and EBusiness Suite products, across various medium to large scale industries such as Manufacturing, Energy, Retail, Government, Telecommunications, Food and Beverages, Aviation, Shipping, Mining, Publication, Media, Agriculture and Trading. His application knowledge spans across multiple Oracle ERP areas like Financials, Procurement, Supply chain, Manufacturing, Projects, in addition to Technical tools and platforms like Oracle Forms, Oracle Reports, Workflow, BI Publisher, OTBI, Linux, Oracle SOAP and REST APIs using Python and Cloud Integration Tools like OCI.

More from this Author

Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram