API to Cancel Orders Lines 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_line_tbl_in                  oe_order_pub.line_tbl_type;   

   l_action_request_tbl_in        oe_order_pub.request_tbl_type;

   l_header_rec_out               oe_order_pub.header_rec_type; 

   l_line_tbl_out                 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_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;

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

   l_num_api_version              NUMBER := 1.0; 

   CURSOR c_so_details

   IS

      SELECT oh.order_number, ol.*

        FROM oe_order_lines_all ol, oe_order_headers_all oh

       WHERE     oh.header_id = ol.header_id

             AND oh.org_id = ol.org_id

             AND NVL (ol.cancelled_flag, 'N') = 'N'

             AND oh.order_number = '2171163'          -- Enter the Order Number

             AND ol.line_number = 2                   -- Enter the Line Number

             AND ol.shipment_number = 1               -- Enter the Shipment Number

             AND ol.flow_status_code = 'AWAITING_SHIPPING';

BEGIN

   SELECT user_id

     INTO l_user_id

     FROM fnd_user

    WHERE user_name = 'MYRANJIT';

   SELECT responsibility_id, application_id

     INTO l_resp_id, l_appl_id

     FROM fnd_responsibility_vl

    WHERE responsibility_name = 'Order Management Super User';

   fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

   FOR iso_rec IN c_so_details

   LOOP

      l_line_tbl_in (1) := oe_order_pub.g_miss_line_rec;

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

      l_line_tbl_in (1).ordered_quantity := 0;

      l_line_tbl_in (1).change_reason := 'Admin Error';

      l_line_tbl_in (1).change_comments := 'CANCEL ORDER';

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

      oe_msg_pub.delete_msg;

      mo_global.init ('ONT');

      mo_global.set_policy_context ('S', iso_rec.org_id);

      oe_order_pub.process_order (

         p_api_version_number       => l_num_api_version,

         p_org_id                   => mo_global.get_current_org_id,

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

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

         x_line_val_tbl             => l_line_val_tbl_out,

         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;

      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.ENABLE (10000);

      DBMS_OUTPUT.put_line (

            'Sales Order => '

         || iso_rec.order_number

         || ' - Line Number => '

         || iso_rec.line_number

         || ' - Shipment Number => '

         || iso_rec.shipment_number

         || ' Having Line ID=> '

         || iso_rec.line_id

         || ' Cancelled Successfully');

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

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

   END LOOP;

END;