Script to update sales order schedule ship date in oracle apps

Script to update sales order schedule ship date in oracle apps

In this post, we will be discuss about the Script to update sales order schedule ship date in oracle apps. This api script will help to update the sales order schedule ship data. We can mass change the schedule ship date for the sales orders in oracle apps. This script is the tested script and we need to have the sales order number and line number for which we want to update the schedule ship date in oracle apps. Here below is the complete Script to update sales order schedule ship date in oracle apps.

Details PLSQL Script to update sales order schedule ship date in oracle apps

DECLARE

   l_user_id                      NUMBER;

   l_resp_id                      NUMBER;

   l_appl_id                      NUMBER;

   l_header_rec_in                oe_order_pub.header_rec_type;     

   l_action_request_tbl_in        oe_order_pub.request_tbl_type;

   l_header_rec_out               oe_order_pub.header_rec_type;         

   p_lin_rec_tbl                 oe_order_pub.line_tbl_type;

   l_header_val_rec_out           oe_order_pub.header_val_rec_type;

   l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;

   l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;

   l_oe_lin_rec                  oe_order_pub.line_tbl_type;   

   l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;

   l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;

   l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;

   l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;

   l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;

   p_line_val_rec             oe_order_pub.line_val_tbl_type;

   l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;

   l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;

   l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;

   l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;

   l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;

   l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;

   l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;

   l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;

  l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;

   l_action_request_tbl_out       oe_order_pub.request_tbl_type;

   l_chr_program_unit_name        VARCHAR2 (100);                     

   l_chr_ret_status               VARCHAR2 (1000)                     := NULL;

   l_msg_count                    NUMBER                                 := 0;

   l_msg_data                     VARCHAR2 (2000);

                                                

   CURSOR c_so_details

   IS 

    SELECT oh.header_id, ol.line_id,sysdate+3 update_ssd, msi.segment1,

       ol.line_number || '.' || ol.shipment_number LINE,ol.inventory_item_id,ol.line_type_id

  FROM oe_order_headers_all oh,

       oe_order_lines_all ol,

       mtl_system_items msi

WHERE oh.header_id = ol.header_id

   AND ol.inventory_item_id = msi.inventory_item_id

   AND msi.organization_id = ol.ship_from_org_id

   AND OH.ORG_ID=2332

   AND ol.line_number || '.' || ol.shipment_number='11.2'

   AND OH.ORDER_NUMBER='443999';

 

BEGIN

   SELECT user_id

     INTO l_user_id

     FROM fnd_user

    WHERE user_name = 'NIKITA_V';

   SELECT responsibility_id, application_id

     INTO l_resp_id, l_appl_id

     FROM APPS.fnd_responsibility_TL

    WHERE responsibility_name LIKE '%EMR Manufacturing%Distribution Manager USD 1162%'

    AND LANGUAGE='US';

   fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

   FOR iso_rec IN c_so_details

   LOOP

      l_oe_lin_rec (1) := oe_order_pub.g_miss_line_rec;

      l_oe_lin_rec (1).line_id := iso_rec.line_id;

       l_oe_lin_rec (1).line_type_id := iso_rec.line_type_id;

       l_oe_lin_rec (1).schedule_ship_date :=iso_rec.update_ssd;

      l_oe_lin_rec (1).operation := oe_globals.g_opr_update;

      oe_msg_pub.delete_msg;

     

     oe_debug_pub.setdebuglevel (5);

    

      oe_order_pub.process_order

                   (p_api_version_number          => 1.0,                 

                  --  p_init_msg_list               => fnd_api.g_false,

                  --  p_return_values               => fnd_api.g_false,

                    p_action_commit               => fnd_api.g_false,

                    p_line_tbl                    => l_oe_lin_rec,

                    x_header_rec                  => l_header_rec_out,

                    x_header_val_rec              => l_header_val_rec_out,

                    x_header_adj_tbl              => l_header_adj_tbl_out,

                    x_header_adj_val_tbl          => l_header_adj_val_tbl_out,

                    x_header_price_att_tbl        => l_header_price_att_tbl_out,

                    x_header_adj_att_tbl          => l_header_adj_att_tbl_out,

                    x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out,

                    x_header_scredit_tbl          => l_header_scredit_tbl_out,

                    x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out,

                    x_line_tbl                    => p_lin_rec_tbl,

                    x_line_val_tbl                => p_line_val_rec,

                    x_line_adj_tbl                => l_line_adj_tbl_out,

                    x_line_adj_val_tbl            => l_line_adj_val_tbl_out,

                    x_line_price_att_tbl          => l_line_price_att_tbl_out,

                    x_line_adj_att_tbl            => l_line_adj_att_tbl_out,

                    x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out,

                    x_line_scredit_tbl            => l_line_scredit_tbl_out,

                    x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out,

                    x_lot_serial_tbl              => l_lot_serial_tbl_out,

                    x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out,

                    x_action_request_tbl          => l_action_request_tbl_out,

                    x_return_status               => l_chr_ret_status,

                    x_msg_count                   => l_msg_count,

                    x_msg_data                    => l_msg_data

                   );

      l_msg_data := NULL;

     

       DBMS_OUTPUT.put_line ( 'STATUS => '|| l_chr_ret_status);

      IF l_chr_ret_status <> 'S'

      THEN

         FOR iindx IN 1 .. l_msg_count

         LOOP

            l_msg_data := l_msg_data || '  ' || oe_msg_pub.get (iindx);

         END LOOP;

      END IF;

      DBMS_OUTPUT.put_line ( 'Line Number => '|| iso_rec.line);

   

      DBMS_OUTPUT.put_line ('Return Status: ' || l_chr_ret_status);

      DBMS_OUTPUT.put_line ('Error Message: ' || l_msg_data);

   END LOOP;

COMMIT;

END;