Oracle Apps R12 Transact Move Order in Inventory API

--Transact Move order from here

PROCEDURE xxcona_alloc_move_order (
      p_move_order_line_id   IN       NUMBER,
      x_return_status        OUT      VARCHAR2,
      x_detailed_qty         OUT      NUMBER
   )
   IS

-- WHO columns
   l_user_id          NUMBER        := -1;
   l_resp_id          NUMBER        := -1;
   l_application_id   NUMBER        := -1;
   l_user_name        VARCHAR2 (30) := 'XX_AFZAL';
   l_resp_name        VARCHAR2 (30) := 'xx Inventory';
      -- 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)                    := 'CONACENT';
      --l_resp_name              VARCHAR2 (30)                   := 'INVENTORY';
       -- API specific declarations
      l_trohdr_rec             inv_move_order_pub.trohdr_rec_type;
      l_trohdr_val_rec         inv_move_order_pub.trohdr_val_rec_type;
      x_trohdr_rec             inv_move_order_pub.trohdr_rec_type;
      x_trohdr_val_rec         inv_move_order_pub.trohdr_val_rec_type;
      l_validation_flag        VARCHAR2 (2)
                                       := inv_move_order_pub.g_validation_yes;
      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;
      l_line_id                mtl_txn_request_lines.line_id%TYPE
                                                      := p_move_order_line_id;
      l_move_order_type        mtl_txn_request_headers.move_order_type%TYPE
                                                                         := 1;
      --x_detailed_qty           NUMBER                                    := 0;
      x_number_of_rows         NUMBER                                    := 0;
      x_revision               VARCHAR2 (3);
      x_locator_id             NUMBER                                    := 0;
      x_transfer_to_location   NUMBER                                    := 0;
      x_lot_number             VARCHAR2 (30);
      x_expiration_date        DATE;
      x_transaction_temp_id    NUMBER                                    := 0;
   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
       WHERE responsibility_key = l_resp_name;

      fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);
      -- MFG / Mfg Mgr / INV
      DBMS_OUTPUT.put_line (   'Initialized applications context: '
                            || l_user_id
                            || ' '
                            || l_resp_id
                            || ' '
                            || l_application_id
                           );*/
      -- Allocate each line of the Move Order
      inv_replenish_detail_pub.line_details_pub
                           (p_line_id                    => l_line_id,
                            x_number_of_rows             => x_number_of_rows,
                            x_detailed_qty               => x_detailed_qty,
                            x_return_status              => x_return_status,
                            x_msg_count                  => x_msg_count,
                            x_msg_data                   => x_msg_data,
                            x_revision                   => x_revision,
                            x_locator_id                 => x_locator_id,
                            x_transfer_to_location       => x_transfer_to_location,
                            x_lot_number                 => x_lot_number,
                            x_expiration_date            => x_expiration_date,
                            x_transaction_temp_id        => x_transaction_temp_id,
                            p_transaction_header_id      => NULL,
                            p_transaction_mode           => NULL,
                            p_move_order_type            => l_move_order_type,
                            p_serial_flag                => fnd_api.g_false,
                            p_plan_tasks                 => FALSE
                                                                 --FND_API.G_FALSE
      ,
                            p_auto_pick_confirm          => FALSE
                                                                 --FND_API.G_FALSE
      ,
                            p_commit                     => TRUE
                           --FND_API.G_FALSE
                           );
      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
         COMMIT;
         DBMS_OUTPUT.put_line ('Detailed Qty: ' || x_detailed_qty);
         DBMS_OUTPUT.put_line ('Number of rows: ' || x_number_of_rows);
         DBMS_OUTPUT.put_line ('Trx temp ID: ' || x_transaction_temp_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 xxcona_alloc_move_order;

   PROCEDURE xxcona_process_move_order_line (
      p_move_order_line_id      IN       NUMBER,
      p_new_quantity_detailed   IN       NUMBER,
      x_return_status           OUT      VARCHAR2
   )
   IS
      -- Main parameters -- Move order to update, and new quantity
      l_new_quantity_detailed   NUMBER             := p_new_quantity_detailed;
      l_mo_line_id              NUMBER                := p_move_order_line_id;
      -- User Information
      --l_user_id                 NUMBER                             := -1;
      --l_user_name               VARCHAR2 (20)                   := 'CONACENT';
      --l_resp_id                 NUMBER                             := -1;
      --l_resp_name               VARCHAR2 (40)                  := 'INVENTORY';
      --l_application_id          NUMBER                             := -1;
      l_rowcnt                  NUMBER                             := 1;
      -- Errors
      --x_return_status           VARCHAR2 (10);
      x_msg_count               NUMBER;
      x_msg_data                VARCHAR2 (255);
      x_message_list            error_handler.error_tbl_type;
      -- Move order variables
      l_trolin_tbl              inv_move_order_pub.trolin_tbl_type;
      l_trolin_old_tbl          inv_move_order_pub.trolin_tbl_type;
      x_trolin_tbl              inv_move_order_pub.trolin_tbl_type;
      l_mo_line_rec             inv_move_order_pub.trolin_rec_type;
   BEGIN
  
      l_mo_line_rec := inv_trolin_util.query_row (p_line_id => l_mo_line_id);
      l_trolin_tbl (1) := l_mo_line_rec;
      l_trolin_old_tbl (1) := l_mo_line_rec;
      l_trolin_tbl (1).operation := 'UPDATE';    -- INV_GLOBALS.G_OPR_UPDATE;
      l_trolin_tbl (1).quantity_detailed := l_new_quantity_detailed;
      inv_move_order_pub.process_move_order_line
                                       (p_api_version_number      => 1.0,
                                        p_init_msg_list           => fnd_api.g_true,
                                        p_return_values           => fnd_api.g_false
                                                                                    -- Convert ids to values
      ,
                                        p_commit                  => fnd_api.g_false
                                                                                    --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_trolin_tbl,
                                        p_trolin_old_tbl          => l_trolin_old_tbl,
                                        x_trolin_tbl              => x_trolin_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 ('x_msg_data :' || x_msg_data);
         DBMS_OUTPUT.put_line ('Error Messages :');
         error_handler.get_message_list (x_message_list => x_message_list);

         FOR i IN 1 .. x_message_list.COUNT
         LOOP
            DBMS_OUTPUT.put_line (x_message_list (i).MESSAGE_TEXT);
         END LOOP;
      ELSE
         COMMIT;
      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 xxcona_process_move_order_line;

   PROCEDURE xxcona_transact_mo_line (
      p_header_id   IN   NUMBER,
      p_line_id     IN   NUMBER
   )
   IS
      -- Common Declarations
      l_api_version        NUMBER                                  := 1.0;
      l_init_msg_list      VARCHAR2 (2)                     := fnd_api.g_true;
      l_commit             VARCHAR2 (2)                    := fnd_api.g_false;
      x_return_status      VARCHAR2 (2);
      x_msg_count          NUMBER                                  := 0;
      x_msg_data           VARCHAR2 (255);
      -- API specific declarations
      l_move_order_type    NUMBER                                  := 1;
      l_transaction_mode   NUMBER                                  := 1;
      l_trolin_tbl         inv_move_order_pub.trolin_tbl_type;
      l_mold_tbl           inv_mo_line_detail_util.g_mmtt_tbl_type;
      x_mmtt_tbl           inv_mo_line_detail_util.g_mmtt_tbl_type;
      x_trolin_tbl         inv_move_order_pub.trolin_tbl_type;
      l_transaction_date   DATE                                    := SYSDATE;
      -- 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)                        := 'CONACENT';
   --l_resp_name          VARCHAR2 (30)                       := 'INVENTORY';
   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
       WHERE responsibility_key = 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
                           );*/
      l_trolin_tbl (1).line_id := p_line_id;
      l_trolin_tbl (1).header_id := p_header_id;                     -- Added
      -- call API to create move order header
      DBMS_OUTPUT.put_line
                   ('=======================================================');
      DBMS_OUTPUT.put_line
                   ('Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API');
      inv_pick_wave_pick_confirm_pub.pick_confirm
                                   (p_api_version_number      => l_api_version,
                                    p_init_msg_list           => l_init_msg_list,
                                    p_commit                  => l_commit,
                                    x_return_status           => x_return_status,
                                    x_msg_count               => x_msg_count,
                                    x_msg_data                => x_msg_data,
                                    p_move_order_type         => l_move_order_type,
                                    p_transaction_mode        => l_transaction_mode,
                                    p_trolin_tbl              => l_trolin_tbl,
                                    p_mold_tbl                => l_mold_tbl,
                                    x_mmtt_tbl                => x_mmtt_tbl,
                                    x_trolin_tbl              => x_trolin_tbl,
                                    p_transaction_date        => l_transaction_date
                                   );
      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);
      ELSE
         COMMIT;
      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 xxcona_transact_mo_line;

   PROCEDURE xxcona_main (
      p_move_order_no     IN   VARCHAR2,
      p_organization_id   IN   NUMBER
   )
   IS
      x_return_status   VARCHAR2 (2);
      x_detailed_qty    NUMBER;

      CURSOR move_order_cur
      IS
         SELECT mtrh.request_number, mtrh.organization_id, mtrh.header_id,
                mtrl.line_id, mtrh.move_order_type, mtrl.quantity,
                mtrl.quantity_delivered,
                (mtrl.quantity - mtrl.quantity_delivered) unallocated_qty
           FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
          WHERE mtrh.organization_id = mtrl.organization_id
            AND mtrh.header_id = mtrl.header_id
            AND mtrh.request_number = p_move_order_no
            AND mtrh.organization_id = p_organization_id;
   BEGIN
      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
          WHERE responsibility_key = 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
                              );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               ('Exception Occured : Not able to Initialize applications context'
               );
            DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
            DBMS_OUTPUT.put_line
                    ('=======================================================');
      END;

      BEGIN
         FOR cur_rec IN move_order_cur
         LOOP
            xxcona_alloc_move_order (cur_rec.line_id,
                                     x_return_status,
                                     x_detailed_qty
                                    );

            IF     x_return_status = fnd_api.g_ret_sts_success
               AND x_detailed_qty > 0
            THEN
               x_return_status := NULL;                      --- Reset Status
               xxcona_process_move_order_line (cur_rec.line_id,
                                               x_detailed_qty,
                                               x_return_status
                                              );

               IF x_return_status = fnd_api.g_ret_sts_success
               THEN
                  xxcona_transact_mo_line (cur_rec.header_id,
                                           cur_rec.line_id);
               END IF;
            ELSE
               DBMS_OUTPUT.put_line
                  ('Exception Occured : Unable to allocate requested quantity.'
                  );
            END IF;
         END LOOP;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line
               ('Exception Occured : Unable to process the request --- Transact Move Orders'
               );
            DBMS_OUTPUT.put_line (SQLCODE || ':' || SQLERRM);
            DBMS_OUTPUT.put_line
                    ('=======================================================');
      END;
   END xxcona_main;
--Transact move order ends here

Execute:

BEGIN
   xxcona_main (p_move_order_no        => '112',
                                            p_organization_id      => 1733
                                           );
END;

commit