Oracle API Script for Pick Release in OM

The shipping public API "WSH_DELIVERIES_PUB. Delivery_Action”, enables pick release of the sales order line.  The relevant pick release parameters are retrieved from the Shipping and Organization Parameter setup.

Specific Parameters:

• p_action_code                            = > PICK_RELEASE
• p_delivery_id/p_delivery_name => Id/name of delivery

Refer to the attached PICK_RELEASE.sql sample script which will pick releases lines for a given delivery.

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);

   x_trip_id                 VARCHAR2(30);

   x_trip_name               VARCHAR2(30);

  

   -- outparameters  

   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 WSH_DELIVERIES_PUB.delivery_action  

   p_action_code := 'PICK-RELEASE';  -- Releases Lines related to a delivery

   p_delivery_id := 1997596;         -- delivery ID that action is performed on

  

   -- 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,

      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 delivery '||p_delivery_id || ' is successfully pick released');

      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;

/