Oracle Inventory Process Move Order API

DECLARE

   -- Common Declarations

   l_api_version      NUMBER := 1.0;

   l_init_msg_list    VARCHAR2 (2) := FND_API.G_TRUE;

   l_return_values    VARCHAR2 (2) := FND_API.G_FALSE;

   l_commit           VARCHAR2 (2) := FND_API.G_FALSE;

   x_return_status    VARCHAR2 (2);

   x_msg_count        NUMBER := 0;

   x_msg_data         VARCHAR2 (255);

   -- WHO columns

   l_user_id          NUMBER := -1;

   l_resp_id          NUMBER := -1;

   l_application_id   NUMBER := -1;

   l_row_cnt          NUMBER := 1;

   l_user_name        VARCHAR2 (30) := 'MFG';

   l_resp_name        VARCHAR2 (50) := 'Manufacturing and Distribution Manager';

   -- API specific declarations

   l_header_id        NUMBER := 0;

   l_trohdr_rec       INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;

   l_trohdr_val_rec   INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;

   l_trolin_tbl       INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;

   l_trolin_val_tbl   INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE;

   x_trolin_tbl       INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;

   x_trolin_val_tbl   INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE;

   x_trohdr_rec       INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;

   x_trohdr_val_rec   INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;

BEGIN

   -- Get the user_id

   SELECT user_id

     INTO l_user_id

     FROM fnd_user

    WHERE user_name = l_user_name;

   -- Get the application_id and responsibility_id

   SELECT application_id, responsibility_id

     INTO l_application_id, l_resp_id

     FROM fnd_responsibility_vl

    WHERE responsibility_name = l_resp_name;

   FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_application_id);

   DBMS_OUTPUT.put_line ( 'Initialized applications context: ' || l_user_id || ' ' || l_resp_id || ' ' || l_application_id);

   -- Initialize the move order header

   l_trohdr_rec.date_required := SYSDATE + 2;

   l_trohdr_rec.organization_id := 207;

   l_trohdr_rec.from_subinventory_code := 'Stores';

   l_trohdr_rec.to_subinventory_code := 'FGI';

   l_trohdr_rec.status_date := SYSDATE;

   l_trohdr_rec.request_number := 'TEST_TRO2';

   l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED; -- preApproved

   l_trohdr_rec.transaction_type_id :=

   INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR; -- INV_GLOBALS.G_TYPE_TRANSFER_ORDER_STGXFR;

   l_trohdr_rec.move_order_type := INV_GLOBALS.G_MOVE_ORDER_REQUISITION; -- G_MOVE_ORDER_PICK_WAVE;

   l_trohdr_rec.db_flag := FND_API.G_TRUE;

   l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;

   -- Who columns

   l_trohdr_rec.created_by := l_user_id;

   l_trohdr_rec.creation_date := SYSDATE;

   l_trohdr_rec.last_updated_by := l_user_id;

   l_trohdr_rec.last_update_date := SYSDATE;

   -- create  line  for the  header created above

   l_trolin_tbl (l_row_cnt).date_required := SYSDATE;

   l_trolin_tbl (l_row_cnt).organization_id := 207;

   l_trolin_tbl (l_row_cnt).inventory_item_id := 513963;

   l_trolin_tbl (l_row_cnt).from_subinventory_code := 'Stores';

   l_trolin_tbl (l_row_cnt).to_subinventory_code := 'FGI';

   l_trolin_tbl (l_row_cnt).quantity := 2;

   l_trolin_tbl (l_row_cnt).status_date := SYSDATE;

   l_trolin_tbl (l_row_cnt).uom_code := 'Ea';

   l_trolin_tbl (l_row_cnt).line_number := l_row_cnt;

   l_trolin_tbl (l_row_cnt).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;

   l_trolin_tbl (l_row_cnt).db_flag := FND_API.G_TRUE;

   l_trolin_tbl (l_row_cnt).operation := INV_GLOBALS.G_OPR_CREATE;

   -- Who columns

   l_trolin_tbl (l_row_cnt).created_by := l_user_id;

   l_trolin_tbl (l_row_cnt).creation_date := SYSDATE;

   l_trolin_tbl (l_row_cnt).last_updated_by := l_user_id;

   l_trolin_tbl (l_row_cnt).last_update_date := SYSDATE;

   l_trolin_tbl (l_row_cnt).last_update_login := FND_GLOBAL.login_id;

   -- call API to create move order header

   DBMS_OUTPUT.PUT_LINE ('=======================================================');

   DBMS_OUTPUT.PUT_LINE ('Calling INV_MOVE_ORDER_PUB.Process_Move_Order API');

   INV_MOVE_ORDER_PUB.Process_Move_Order (

      P_API_VERSION_NUMBER   => l_api_version,

      P_INIT_MSG_LIST        => l_init_msg_list,

      P_RETURN_VALUES        => l_return_values,

      P_COMMIT               => l_commit,

      X_RETURN_STATUS        => x_return_status,

      X_MSG_COUNT            => x_msg_count,

      X_MSG_DATA             => x_msg_data,

      P_TROHDR_REC           => l_trohdr_rec,

      P_TROHDR_VAL_REC       => l_trohdr_val_rec,

      P_TROLIN_TBL           => l_trolin_tbl,

      P_TROLIN_VAL_TBL       => l_trolin_val_tbl,

      X_TROHDR_REC           => x_trohdr_rec,

      X_TROHDR_VAL_REC       => x_trohdr_val_rec,

      X_TROLIN_TBL           => x_trolin_tbl,

      X_TROLIN_VAL_TBL       => x_trolin_val_tbl);

   DBMS_OUTPUT.PUT_LINE ( '=======================================================');

   DBMS_OUTPUT.PUT_LINE ('Return Status: ' || x_return_status);

   IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)

   THEN

      DBMS_OUTPUT.PUT_LINE ('Error Message :' || x_msg_data);

   END IF;

   IF (x_return_status = FND_API.G_RET_STS_SUCCESS)

   THEN

      DBMS_OUTPUT.PUT_LINE ( 'Move Order Created Successfully for ' || x_trolin_tbl (l_row_cnt).header_id);

   END IF;

   DBMS_OUTPUT.PUT_LINE ('=======================================================');

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE ('Exception Occured :');

      DBMS_OUTPUT.PUT_LINE (SQLCODE || ':' || SQLERRM);

      DBMS_OUTPUT.PUT_LINE ( '=======================================================');

END;