Creating a Hold on Sales Order Header

DECLARE

   l_header_rec                   oe_order_pub.header_rec_type;

   l_line_tbl                     oe_order_pub.line_tbl_type;

   l_action_request_tbl           oe_order_pub.request_tbl_type;

   l_header_adj_tbl               oe_order_pub.header_adj_tbl_type;

   l_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

   l_header_scr_tbl               oe_order_pub.header_scredit_tbl_type;

   l_line_scredit_tbl             oe_order_pub.line_scredit_tbl_type;

   l_request_rec                  oe_order_pub.request_rec_type;

   l_return_status                VARCHAR2 (1000);

   l_msg_count                    NUMBER;

   l_msg_data                     VARCHAR2 (1000);

   p_api_version_number           NUMBER := 1.0;

   p_init_msg_list                VARCHAR2 (10) := fnd_api.g_true;

   p_return_values                VARCHAR2 (10) := fnd_api.g_false;

   p_action_commit                VARCHAR2 (10) := fnd_api.g_false;

   x_return_status                VARCHAR2 (1);

   x_msg_count                    NUMBER;

   x_msg_data                     VARCHAR2 (100);

   p_header_rec                   oe_order_pub.header_rec_type

                                     := oe_order_pub.g_miss_header_rec;

   p_old_header_rec               oe_order_pub.header_rec_type

                                     := oe_order_pub.g_miss_header_rec;

   p_header_val_rec               oe_order_pub.header_val_rec_type

                                     := oe_order_pub.g_miss_header_val_rec;

   p_old_header_val_rec           oe_order_pub.header_val_rec_type

                                     := oe_order_pub.g_miss_header_val_rec;

   p_header_adj_tbl               oe_order_pub.header_adj_tbl_type

                                     := oe_order_pub.g_miss_header_adj_tbl;

   p_old_header_adj_tbl           oe_order_pub.header_adj_tbl_type

                                     := oe_order_pub.g_miss_header_adj_tbl;

   p_header_adj_val_tbl           oe_order_pub.header_adj_val_tbl_type

                                     := oe_order_pub.g_miss_header_adj_val_tbl;

   p_old_header_adj_val_tbl       oe_order_pub.header_adj_val_tbl_type

                                     := oe_order_pub.g_miss_header_adj_val_tbl;

   p_header_price_att_tbl         oe_order_pub.header_price_att_tbl_type

                                     := oe_order_pub.g_miss_header_price_att_tbl;

   p_old_header_price_att_tbl     oe_order_pub.header_price_att_tbl_type

      := oe_order_pub.g_miss_header_price_att_tbl;

   p_header_adj_att_tbl           oe_order_pub.header_adj_att_tbl_type

                                     := oe_order_pub.g_miss_header_adj_att_tbl;

   p_old_header_adj_att_tbl       oe_order_pub.header_adj_att_tbl_type

                                     := oe_order_pub.g_miss_header_adj_att_tbl;

   p_header_adj_assoc_tbl         oe_order_pub.header_adj_assoc_tbl_type

      := oe_order_pub.g_miss_header_adj_assoc_tbl;

   p_old_header_adj_assoc_tbl     oe_order_pub.header_adj_assoc_tbl_type

      := oe_order_pub.g_miss_header_adj_assoc_tbl;

   p_header_scredit_tbl           oe_order_pub.header_scredit_tbl_type

                                     := oe_order_pub.g_miss_header_scredit_tbl;

   p_old_header_scredit_tbl       oe_order_pub.header_scredit_tbl_type

                                     := oe_order_pub.g_miss_header_scredit_tbl;

   p_header_scredit_val_tbl       oe_order_pub.header_scredit_val_tbl_type

      := oe_order_pub.g_miss_header_scredit_val_tbl;

   p_old_header_scredit_val_tbl   oe_order_pub.header_scredit_val_tbl_type

      := oe_order_pub.g_miss_header_scredit_val_tbl;

   p_line_tbl                     oe_order_pub.line_tbl_type

                                     := oe_order_pub.g_miss_line_tbl;

   p_old_line_tbl                 oe_order_pub.line_tbl_type

                                     := oe_order_pub.g_miss_line_tbl;

   p_line_val_tbl                 oe_order_pub.line_val_tbl_type

                                     := oe_order_pub.g_miss_line_val_tbl;

   p_old_line_val_tbl             oe_order_pub.line_val_tbl_type

                                     := oe_order_pub.g_miss_line_val_tbl;

   p_line_adj_tbl                 oe_order_pub.line_adj_tbl_type

                                     := oe_order_pub.g_miss_line_adj_tbl;

   p_old_line_adj_tbl             oe_order_pub.line_adj_tbl_type

                                     := oe_order_pub.g_miss_line_adj_tbl;

   p_line_adj_val_tbl             oe_order_pub.line_adj_val_tbl_type

                                     := oe_order_pub.g_miss_line_adj_val_tbl;

   p_old_line_adj_val_tbl         oe_order_pub.line_adj_val_tbl_type

                                     := oe_order_pub.g_miss_line_adj_val_tbl;

   p_line_price_att_tbl           oe_order_pub.line_price_att_tbl_type

      := oe_order_pub.g_miss_line_price_att_tbl;

   p_old_line_price_att_tbl       oe_order_pub.line_price_att_tbl_type

      := oe_order_pub.g_miss_line_price_att_tbl;

   p_line_adj_att_tbl             oe_order_pub.line_adj_att_tbl_type

                                     := oe_order_pub.g_miss_line_adj_att_tbl;

   p_old_line_adj_att_tbl         oe_order_pub.line_adj_att_tbl_type

                                     := oe_order_pub.g_miss_line_adj_att_tbl;

   p_line_adj_assoc_tbl           oe_order_pub.line_adj_assoc_tbl_type

      := oe_order_pub.g_miss_line_adj_assoc_tbl;

   p_old_line_adj_assoc_tbl       oe_order_pub.line_adj_assoc_tbl_type

      := oe_order_pub.g_miss_line_adj_assoc_tbl;

   p_line_scredit_tbl             oe_order_pub.line_scredit_tbl_type

                                     := oe_order_pub.g_miss_line_scredit_tbl;

   p_old_line_scredit_tbl         oe_order_pub.line_scredit_tbl_type

                                     := oe_order_pub.g_miss_line_scredit_tbl;

   p_line_scredit_val_tbl         oe_order_pub.line_scredit_val_tbl_type

      := oe_order_pub.g_miss_line_scredit_val_tbl;

   p_old_line_scredit_val_tbl     oe_order_pub.line_scredit_val_tbl_type

      := oe_order_pub.g_miss_line_scredit_val_tbl;

   p_lot_serial_tbl               oe_order_pub.lot_serial_tbl_type

                                     := oe_order_pub.g_miss_lot_serial_tbl;

   p_old_lot_serial_tbl           oe_order_pub.lot_serial_tbl_type

                                     := oe_order_pub.g_miss_lot_serial_tbl;

   p_lot_serial_val_tbl           oe_order_pub.lot_serial_val_tbl_type

      := oe_order_pub.g_miss_lot_serial_val_tbl;

   p_old_lot_serial_val_tbl       oe_order_pub.lot_serial_val_tbl_type

      := oe_order_pub.g_miss_lot_serial_val_tbl;

   p_action_request_tbl           oe_order_pub.request_tbl_type

                                     := oe_order_pub.g_miss_request_tbl;

   x_header_rec                   oe_order_pub.header_rec_type;

   x_header_val_rec               oe_order_pub.header_val_rec_type;

   x_header_adj_tbl               oe_order_pub.header_adj_tbl_type;

   x_header_adj_val_tbl           oe_order_pub.header_adj_val_tbl_type;

   x_header_price_att_tbl         oe_order_pub.header_price_att_tbl_type;

   x_header_adj_att_tbl           oe_order_pub.header_adj_att_tbl_type;

   x_header_adj_assoc_tbl         oe_order_pub.header_adj_assoc_tbl_type;

   x_header_scredit_tbl           oe_order_pub.header_scredit_tbl_type;

   x_header_scredit_val_tbl       oe_order_pub.header_scredit_val_tbl_type;

   x_line_val_tbl                 oe_order_pub.line_val_tbl_type;

   x_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

   x_line_adj_val_tbl             oe_order_pub.line_adj_val_tbl_type;

   x_line_price_att_tbl           oe_order_pub.line_price_att_tbl_type;

   x_line_adj_att_tbl             oe_order_pub.line_adj_att_tbl_type;

   x_line_adj_assoc_tbl           oe_order_pub.line_adj_assoc_tbl_type;

   x_line_scredit_tbl             oe_order_pub.line_scredit_tbl_type;

   x_line_scredit_val_tbl         oe_order_pub.line_scredit_val_tbl_type;

   x_lot_serial_tbl               oe_order_pub.lot_serial_tbl_type;

   x_lot_serial_val_tbl           oe_order_pub.lot_serial_val_tbl_type;

   x_action_request_tbl           oe_order_pub.request_tbl_type;

   x_debug_file                   VARCHAR2 (100);

   l_msg_index_out                NUMBER (10);

   l_org_id                       VARCHAR2 (1000);

BEGIN

   DBMS_OUTPUT.ENABLE (1000000);

   fnd_global.Apps_initialize (1318, 21623, 660);

   -- pass in user_id, responsibility_id, and application_id

   mo_global.Init ('ONT');                                 -- Required for R12

   mo_global.set_org_context (204, NULL, 'ONT');

   fnd_global.set_nls_context ('AMERICAN');

   MO_GLOBAL.SET_POLICY_CONTEXT ('S', 204);                -- Required for R12

   oe_msg_pub.initialize;

   oe_debug_pub.initialize;

   /*

    x_debug_file := oe_debug_pub.Set_debug_mode('FILE');

    User to Enable Debug Option

   oe_debug_pub.Setdebuglevel(5); -- Use 5 for the most debuging output

   oe_debug_pub.debug_on;

   dbms_output.Put_line('START OF NEW DEBUG');

   */

   l_header_rec := oe_order_pub.g_miss_header_rec;

   --This is to apply hold an order header

   l_request_rec.entity_id := 238413;

   l_request_rec.entity_code := oe_globals.g_entity_header;

   l_request_rec.request_type := oe_globals.g_apply_hold;

   -- hold_id must be passed

   l_request_rec.param1 := 1000;

   -- indicator that it is an order hold

   l_request_rec.param2 := 'O';

   -- Header ID of the order

   l_request_rec.param3 := 238413;

   l_request_rec.param4 := 'Hold';

   L_action_request_tbl (1) := l_request_rec;

   DBMS_OUTPUT.Put_line ('entity_id: ' || l_request_rec.entity_id);

   DBMS_OUTPUT.Put_line ('param3: ' || l_request_rec.param3);

   -- CALL TO PROCESS ORDER

   oe_order_pub.Process_order (

      p_org_id                   => 204,

      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,

      x_return_status            => l_return_status,

      x_msg_count                => l_msg_count,

      x_msg_data                 => l_msg_data,

      p_header_rec               => l_header_rec-- , p_line_tbl          => l_line_tbl

      ,

      p_action_request_tbl       => l_action_request_tbl--OUT PARAMETERS

      ,

      x_header_rec               => x_header_rec,

      x_header_val_rec           => x_header_val_rec,

      x_header_adj_tbl           => x_header_adj_tbl,

      x_header_adj_val_tbl       => x_header_adj_val_tbl,

      x_header_price_att_tbl     => x_header_price_att_tbl,

      x_header_adj_att_tbl       => x_header_adj_att_tbl,

      x_header_adj_assoc_tbl     => x_header_adj_assoc_tbl,

      x_header_scredit_tbl       => x_header_scredit_tbl,

      x_header_scredit_val_tbl   => x_header_scredit_val_tbl,

      x_line_tbl                 => l_line_tbl,

      x_line_val_tbl             => x_line_val_tbl,

      x_line_adj_tbl             => x_line_adj_tbl,

      x_line_adj_val_tbl         => x_line_adj_val_tbl,

      x_line_price_att_tbl       => x_line_price_att_tbl,

      x_line_adj_att_tbl         => x_line_adj_att_tbl,

      x_line_adj_assoc_tbl       => x_line_adj_assoc_tbl,

      x_line_scredit_tbl         => x_line_scredit_tbl,

      x_line_scredit_val_tbl     => x_line_scredit_val_tbl,

      x_lot_serial_tbl           => x_lot_serial_tbl,

      x_lot_serial_val_tbl       => x_lot_serial_val_tbl,

      x_action_request_tbl       => x_action_request_tbl);

   --dbms_output.Put_line('OM Debug file: ' ||oe_debug_pub.g_dir ||'/' ||oe_debug_pub.g_file);

   -- Retrieve messages

   FOR i IN 1 .. l_msg_count

   LOOP

      oe_msg_pub.Get (p_msg_index       => i,

                      p_encoded         => fnd_api.g_false,

                      p_data            => l_msg_data,

                      p_msg_index_out   => l_msg_index_out);

      DBMS_OUTPUT.Put_line ('message is: ' || l_msg_data);

      DBMS_OUTPUT.Put_line ('message index is: ' || l_msg_index_out);

      DBMS_OUTPUT.Put_line ('l_return_status is: ' || l_return_status);

   END LOOP;

   DBMS_OUTPUT.Put_line ('message is: ' || l_msg_data);

   DBMS_OUTPUT.Put_line ('message index is: ' || l_msg_index_out);

   DBMS_OUTPUT.Put_line ('l_return_status is: ' || l_return_status);

   -- Check the return status

   IF l_return_status = fnd_api.g_ret_sts_success

   THEN

      DBMS_OUTPUT.Put_line ('Process Order Success');

   ELSE

      DBMS_OUTPUT.Put_line ('Failed');

   END IF;

END;

/

COMMIT;


For Releasing Hold :

   l_request_rec.request_type := OE_GLOBALS.G_RELEASE_HOLD;

   l_request_rec.param4 := 'OM_APPROVE';

   l_request_rec.param5 := 'Release Hold Through API';