Effected Tables in Order Management in Different Stages

Entered
oe_order_headers_all	1 record created in header table
oe_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
PickReleased
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
FullTransaction
mtl_material_transactions	No records in mtl_material_transactions
mtl_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
PickConfirmed
wsh_delivery_details	Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock
ShipConfirmed
wsh_delivery_details	Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions	On the ship confirm form, check Ship all box
wsh_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 for
ra_customer_trx_lines_all	specific batch transaction tables get populated

Oracle API Script for Ship Confirm Process in Order Management

What happened during Ship Confirm:

******************************************
The results of the picking process are recorded against a Delivery.

• Ship Confirm can only be performed on Deliveries with Delivery Lines that have been Pick Confirmed.
• Ship confirming a delivery records the results of the picking process. These results could be shipped, backordered, staged or cycle count or a combination of all 4.
• The workflow activity will be completed when the quantity picked is recorded as “Shipped”.
• If the ordered item is set up with the item attribute “Shippable” checked, Ship Confirming is a prerequisite for Invoicing.

Shipped Quantities:
************************
• Once the delivery is closed the Order Line is updated with the shipped quantities and the status of the line is changed to “Shipped”. This enables the order line to proceed to its next workflow activity.
• The Ship Confirm transaction initiates the Inventory Interface to generate the “Issue of Stores” transactions which will decrement inventory and remove the material reservation. Then the OM interfaces is initiated to update the Sales Order Line with Shipped quantities, freight charges, etc.
• The Cost of goods sold account number that is passed to inventory is workflow generated. In Inventory it creates a Material Distribution record that is ultimately passed to the General Ledger.
• If Ship confirm is partial the remaining quantity can be either staged or backordered.

Backordered Quantities:
****************************
• Backordered quantities are left in the Staging Subinventory. They are not automatically returned to their source location.
• A new pick release will be required before they can be ship confirmed.
• The backordered quantity is removed from the delivery being Ship Confirmed.and the reservation is removed making the quantity available to ATP.
• The Sales Order line splits into shipment schedules. One schedule will have the quantity that was shipped and a status of “Shipped”. The other schedule will have the quantity that was backordered and a status of “Awaiting Shipment”.

Staged Quantities:
**********************
• Staged quantities are left in the Staging subinventory and can be Ship Confirmed at a later time. The staged quantity is removed from the delivery being confirmed and optionally linked to a new delivery number.
• The Sales Order line splits into shipment schedules. One schedule will have the quantity that was shipped and a status of “Shipped”. The other schedule will have the quantity that remained Staged with a status of “Picked”.

Different Delivery Line Statuses:
*************************************
select * from wsh_lookups
where lookup_type = 'PICK_STATUS'

- Not Applicable (Code X)
The delivery line is invoiceable 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. Occurs when the order line is manually imported into Oracle Shipping Execution using the Import Delivery Line concurrent process. It 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: Created move order headers and lines. Found available quantity and created inventory allocations. 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 subinventory to staging subinventory. It remains staged until ship confirm.

- Backordered (Code B)
Any of the following circumstances occurs: Pick release has processed the delivery line and cannot find the entire quantity. This typically occurs when the Oracle Inventory inventory balance 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: Has become unavailable, for example, damaged, between picking and shipping. 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. For information on the backorder processing in pick release and ship confirm,

- Shipped (Code C)
The delivery line’s delivery is ship confirmed and posted as intransit, 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.
=================================
WSH_DELIVERIES_PUB.Delivery_Action:
=================================
We can call “WSH_DELIVERIES_PUB.Delivery_Action” API in order to ship confirm the delivery Programatically.

As a final step, call “WSH_DELIVERIES_PUB.Delivery_Action” API in order to ship confirm the delivery.

Specific Parameters:

p_action_code(Required) => action to be performed on Delivery

p_delivery_id (Required)  => delivery id on which the action is performed

p_trip_name    =>  Trip identifier for assignment of trip to delivery

p_asg_pickup_loc_code => Stop location code for pickup assignment

p_asg_pickup_dep_date => Stop location departure date for pickup assignment

p_asg_dropoff_loc_code=> Stop location code for dropoff assignment

p_asg_dropoff_dep_date=> Stop location departure date for dropoff assignment

p_sc_action_flag             => Ship Confirm option - S, B, T, A, C. Used p_sc_intransit_flag         => Ship Confirm set in-transit flag.

p_sc_close_trip_flag      => Ship Confirm close trip flag.

p_sc_create_bol_flag     => Ship Confirm create Bill of Lading flag

p_sc_stage_del_flag       => Ship Confirm create delivery for stage quantity flag

p_sc_trip_ship_method  => Ship Confirm trip ship method.

p_wv_override_flag       => Override flag for weight volume calculations.

x_trip_name                   => Name of autocreated trip.

DECLARE

   --Standard Parameters.

   p_api_version                NUMBER;

   p_init_msg_list              VARCHAR2(30);

   p_commit                     VARCHAR2(30);

   --Parameters for WSH_DELIVERIES_PUB.Delivery_Action.  

   p_action_code                VARCHAR2(15);

   p_delivery_id                NUMBER;

   p_delivery_name              VARCHAR2(30);

   p_asg_trip_id                NUMBER;

   p_asg_trip_name              VARCHAR2(30);

   p_asg_pickup_stop_id         NUMBER;

   p_asg_pickup_loc_id          NUMBER;

   p_asg_pickup_loc_code        VARCHAR2(30);

   p_asg_pickup_arr_date        DATE;

   p_asg_pickup_dep_date        DATE;

   p_asg_dropoff_stop_id        NUMBER;

   p_asg_dropoff_loc_id         NUMBER;

   p_asg_dropoff_loc_code       VARCHAR2(30);

   p_asg_dropoff_arr_date       DATE;

   p_asg_dropoff_dep_date       DATE;

   p_sc_action_flag             VARCHAR2(10);

   p_sc_close_trip_flag         VARCHAR2(10);

   p_sc_create_bol_flag         VARCHAR2(10);

   p_sc_stage_del_flag          VARCHAR2(10);

   p_sc_trip_ship_method        VARCHAR2(30);

   p_sc_actual_dep_date         VARCHAR2(30);

   p_sc_report_set_id           NUMBER;

   p_sc_report_set_name         VARCHAR2(60);

   p_wv_override_flag           VARCHAR2(10);

   p_sc_defer_interface_flag    VARCHAR2(1);

   x_trip_id                    VARCHAR2(30);

   x_trip_name                  VARCHAR2(30);

   --out parameters  

   x_return_status              VARCHAR2(10);

   x_msg_count                  NUMBER;

   x_msg_data                   VARCHAR2(2000);

   x_msg_details                VARCHAR2(3000);

   x_msg_summary                VARCHAR2(3000);

  

   -- Handle exceptions  

   fail_api                     EXCEPTION;

BEGIN

  

   -- Initialize return status  

   x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;

  

   -- Call this procedure to initialize applications parameters    

   FND_GLOBAL.APPS_INITIALIZE(

      user_id      => 1318

   ,  resp_id      => 21623

   ,  resp_appl_id => 660);

  

   -- Values for Ship Confirming the delivery  

   p_action_code                 := 'CONFIRM'; -- The action code for ship confirm

   p_delivery_id                 := 2023619;   -- The delivery that needs to be confirmed

   p_sc_action_flag              := 'S';       -- Ship entered quantity.

   p_sc_close_trip_flag          := 'Y';       -- Close the trip after ship confirm

   p_sc_trip_ship_method         := 'DHL';     -- The ship method code

   p_sc_defer_interface_flag     := 'N';

  

   -- Call to WSH_DELIVERIES_PUB.Delivery_Action.

   WSH_DELIVERIES_PUB.Delivery_Action(

      p_api_version_number         => 1.0,

      p_init_msg_list              => p_init_msg_list,

      x_return_status              => x_return_status,

      x_msg_count                  => x_msg_count,

      x_msg_data                   => x_msg_data,

      p_action_code                => p_action_code,

      p_delivery_id                => p_delivery_id,

      p_delivery_name              => p_delivery_name,

      p_asg_trip_id                => p_asg_trip_id,

      p_asg_trip_name              => p_asg_trip_name,

      p_asg_pickup_stop_id         => p_asg_pickup_stop_id,

      p_asg_pickup_loc_id          => p_asg_pickup_loc_id,

      p_asg_pickup_loc_code        => p_asg_pickup_loc_code,

      p_asg_pickup_arr_date        => p_asg_pickup_arr_date,

      p_asg_pickup_dep_date        => p_asg_pickup_dep_date,

      p_asg_dropoff_stop_id        => p_asg_dropoff_stop_id,

      p_asg_dropoff_loc_id         => p_asg_dropoff_loc_id,

      p_asg_dropoff_loc_code       => p_asg_dropoff_loc_code,

      p_asg_dropoff_arr_date       => p_asg_dropoff_arr_date,

      p_asg_dropoff_dep_date       => p_asg_dropoff_dep_date,

      p_sc_action_flag             => p_sc_action_flag,

      p_sc_close_trip_flag         => p_sc_close_trip_flag,

      p_sc_create_bol_flag         => p_sc_create_bol_flag,

      p_sc_stage_del_flag          => p_sc_stage_del_flag,

      p_sc_trip_ship_method        => p_sc_trip_ship_method,

      p_sc_actual_dep_date         => p_sc_actual_dep_date,

      p_sc_report_set_id           => p_sc_report_set_id,

      p_sc_report_set_name         => p_sc_report_set_name,

      p_wv_override_flag           => p_wv_override_flag,

      p_sc_defer_interface_flag    => p_sc_defer_interface_flag  ,         

      x_trip_id                    => x_trip_id,

      x_trip_name                  => x_trip_name);

     

   if (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) then

      raise fail_api;

   else

      dbms_output.put_line('The confirm action on the delivery '||p_delivery_id||' is successful');

   end if;

EXCEPTION

   when fail_api then

      WSH_UTIL_CORE.get_messages('Y', x_msg_summary, x_msg_details,x_msg_count);

      if x_msg_count > 1 then

         x_msg_data := x_msg_summary || x_msg_details;

         Dbms_output.put_line('Message Data : '||x_msg_data);

      else

         x_msg_data := x_msg_summary;

         Dbms_output.put_line('Message Data : '||x_msg_data);

      end if;  

END;

/