INV API: Create Inventory Move Order Line inv_move_order_pub.create_move_order_lines

CREATE OR REPLACE PROCEDURE APPS.ets_create_move_order_ln (
   p_org_id                   IN       NUMBER,
   user_name                  IN       VARCHAR2,
   resp_name                  IN       VARCHAR2,
   resp_appl_id               IN       NUMBER,

   p_move_order_number        IN       VARCHAR2,
   p_date_required            IN       DATE,
   p_status_date              IN       DATE,
   p_item_code                IN       VARCHAR2,
   p_line_number              IN       NUMBER,
   p_transaction_type_name    IN       VARCHAR2,
   p_organization_code        IN       VARCHAR2,
   p_quantity                 IN       NUMBER,
   p_from_subinventory_code   IN       VARCHAR2,
   p_to_subinventory_code     IN       VARCHAR2,
   p_uom_code                 IN       VARCHAR2,
   p_return_status            OUT      VARCHAR2
)
AS
   l_line_tbl              inv_move_order_pub.trolin_tbl_type
                                      := inv_move_order_pub.g_miss_trolin_tbl;
   x_return_status         VARCHAR2 (1);
   x_msg_count             NUMBER;
   x_msg_data              VARCHAR2 (4000);
   x_line_tbl              inv_move_order_pub.trolin_tbl_type;
   l_line_val_tbl          inv_move_order_pub.trolin_val_tbl_type;
   x_line_val_tbl          inv_move_order_pub.trolin_val_tbl_type;
   v_msg_index_out         NUMBER;
   l_rsr_type              inv_reservation_global.mtl_reservation_tbl_type;
   p_validation_flag       VARCHAR2 (1);
   v_header_id             NUMBER;
   v_transaction_type_id   NUMBER;
   v_organization_id       NUMBER;
   v_inventory_item_id     NUMBER;
   v_user_id               NUMBER;
   v_resp_id               NUMBER;
BEGIN
   l_line_tbl.DELETE;
   x_line_tbl.DELETE;
-------------------------------------------------
   BEGIN
      SELECT usr.user_id
        INTO v_user_id
        FROM fnd_user usr
       WHERE user_name = usr.user_name;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_user_id := NULL;
   END;
   BEGIN
      SELECT resp.responsibility_id
        INTO v_resp_id
        FROM fnd_responsibility_vl resp
       WHERE resp_name = resp.responsibility_name;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_resp_id := NULL;
   END;
   mo_global.set_policy_context ('S', p_org_id);
   inv_globals.set_org_id (p_org_id);
   fnd_global.apps_initialize (v_user_id, v_resp_id, resp_appl_id);
-------------------------------------------------------------
   BEGIN
      SELECT hd.header_id
        INTO v_header_id
        FROM mtl_txn_request_headers hd
       WHERE hd.request_number = p_move_order_number;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_header_id := NULL;
   END;
   BEGIN
      SELECT trx.transaction_type_id
        INTO v_transaction_type_id
        FROM mtl_transaction_types trx
       WHERE trx.transaction_type_name = p_transaction_type_name;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_transaction_type_id := 64;
   END;
   BEGIN
      SELECT org.organization_id
        INTO v_organization_id
        FROM inv_organization_info_v org
       WHERE org.organization_code = p_organization_code;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_organization_id := 103;
   END;
   BEGIN
      SELECT DISTINCT item.inventory_item_id
                 INTO v_inventory_item_id
                 FROM mtl_system_items_b item
                WHERE item.segment1 = p_item_code;
   EXCEPTION
      WHEN OTHERS
      THEN
         v_inventory_item_id := NULL;
   END;
   l_line_tbl (1).header_id := v_header_id;
   l_line_tbl (1).date_required := p_date_required;
   l_line_tbl (1).inventory_item_id := v_inventory_item_id;
   l_line_tbl (1).line_id := fnd_api.g_miss_num;
   l_line_tbl (1).line_number := p_line_number;
   l_line_tbl (1).line_status := inv_globals.g_to_status_incomplete;
   l_line_tbl (1).REFERENCE := TO_CHAR (TRIM (SYSDATE));
   l_line_tbl (1).transaction_type_id := v_transaction_type_id;
   l_line_tbl (1).organization_id := v_organization_id;
   l_line_tbl (1).quantity := p_quantity;
   l_line_tbl (1).status_date := p_status_date;
   l_line_tbl (1).uom_code := p_uom_code;
   l_line_tbl (1).db_flag := fnd_api.g_true;
   l_line_tbl (1).operation := inv_globals.g_opr_create;
   l_line_tbl (1).from_subinventory_code := p_from_subinventory_code;
   l_line_tbl (1).to_subinventory_code := p_to_subinventory_code;
   inv_move_order_pub.create_move_order_lines
                                       (p_api_version_number      => 1.0,
                                        p_init_msg_list           => fnd_api.g_false,
                                        p_return_values           => fnd_api.g_false,
                                        p_commit                  => fnd_api.g_false,
                                        x_return_status           => x_return_status,
                                        x_msg_count               => x_msg_count,
                                        x_msg_data                => x_msg_data,
                                        p_trolin_tbl              => l_line_tbl,
                                        p_trolin_val_tbl          => l_line_val_tbl,
                                        x_trolin_tbl              => x_line_tbl,
                                        x_trolin_val_tbl          => x_line_val_tbl,
                                        p_validation_flag         => p_validation_flag
                                       );
   DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status);
   DBMS_OUTPUT.put_line ('Message Count is :' || x_msg_count);
   DBMS_OUTPUT.put_line ('Number of Lines Created are :' || x_line_tbl.COUNT);
   IF x_return_status = 'S'
   THEN
      COMMIT;
      p_return_status := x_return_status;
   ELSE
      ROLLBACK;
      p_return_status := x_return_status;
   END IF;
   IF x_msg_count > 0
   THEN
      FOR v_index IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get (p_msg_index          => v_index,
                          p_encoded            => 'F',
                          p_data               => x_msg_data,
                          p_msg_index_out      => v_msg_index_out
                         );
         x_msg_data := SUBSTR (x_msg_data, 1, 200);
         DBMS_OUTPUT.put_line (x_msg_data);
         DBMS_OUTPUT.put_line
               ('============================================================');
      END LOOP;
   END IF;
END;
/