Thursday, December 24, 2015

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;

/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect