Oracle R12 Move Order using APIs

DECLARE
   l_header_rec      inv_move_order_pub.trohdr_rec_type;
   l_line_tbl        inv_move_order_pub.trolin_tbl_type;
   x_return_status   VARCHAR2 (1);
   x_msg_data        VARCHAR2 (4000);
   x_msg_count       NUMBER;

   PROCEDURE create_move_order (
      x_hdr_rec         OUT   inv_move_order_pub.trohdr_rec_type
    , x_line_tbl        OUT   inv_move_order_pub.trolin_tbl_type
    , x_return_status   OUT   VARCHAR2
    , x_msg_data        OUT   VARCHAR2
    , x_msg_count       OUT   NUMBER
   )
   IS
      l_hdr_rec         inv_move_order_pub.trohdr_rec_type              := inv_move_order_pub.g_miss_trohdr_rec;
      l_line_tbl        inv_move_order_pub.trolin_tbl_type              := inv_move_order_pub.g_miss_trolin_tbl;
      x_hdr_val_rec     inv_move_order_pub.trohdr_val_rec_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;
   BEGIN
      l_line_tbl.DELETE;
      x_line_tbl.DELETE;
      l_hdr_rec.date_required := SYSDATE;
      l_hdr_rec.header_status := inv_globals.g_to_status_preapproved;
      l_hdr_rec.organization_id := 207;
      l_hdr_rec.status_date := SYSDATE;
      l_hdr_rec.transaction_type_id := inv_globals.g_type_transfer_order_issue;
      l_hdr_rec.move_order_type := inv_globals.g_move_order_requisition;
      l_hdr_rec.db_flag := fnd_api.g_true;
      l_hdr_rec.operation := inv_globals.g_opr_create;
      l_hdr_rec.description := ‘Test Move Order’;
      l_hdr_rec.to_account_id := 12831;
      l_hdr_rec.from_subinventory_code := ‘FGI’;
      l_line_tbl (1).date_required := SYSDATE;
      l_line_tbl (1).inventory_item_id := 167742;
      l_line_tbl (1).line_id := fnd_api.g_miss_num;
      l_line_tbl (1).line_number := 1;
      l_line_tbl (1).line_status := inv_globals.g_to_status_preapproved;
      l_line_tbl (1).transaction_type_id := inv_globals.g_type_transfer_order_issue;
      l_line_tbl (1).organization_id := 207;
      l_line_tbl (1).quantity := 1;
      l_line_tbl (1).status_date := SYSDATE;
      l_line_tbl (1).uom_code := ‘Ea’;
      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 := ‘FGI’;
      l_line_tbl (1).to_account_id := 12831;
      l_line_tbl (1).lot_number := ‘A01’;                                                         –If the item is lot controlled
      l_line_tbl (1).serial_number_start := ‘A01-0039’;                                 –If the item is serial number controlled
      l_line_tbl (1).serial_number_end := ‘A01-0039’;                                   –If the item is serial number controlled
      inv_move_order_pub.process_move_order (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_trohdr_rec              => l_hdr_rec
                                           , p_trolin_tbl              => l_line_tbl
                                           , x_trohdr_rec              => x_hdr_rec
                                           , x_trohdr_val_rec          => x_hdr_val_rec
                                           , x_trolin_tbl              => x_line_tbl
                                           , x_trolin_val_tbl          => x_line_val_tbl
                                            );
      DBMS_OUTPUT.put_line (‘Return Status is :’ || x_return_status);
      DBMS_OUTPUT.put_line (‘Message Count is :’ || x_msg_count);
      DBMS_OUTPUT.put_line (‘Move Order Number is :’ || x_hdr_rec.request_number);
      DBMS_OUTPUT.put_line (‘Move Order ID is :’ || x_hdr_rec.header_id);
      DBMS_OUTPUT.put_line (‘Number of Lines Created are :’ || x_line_tbl.COUNT);

      IF x_return_status = ‘S’
      THEN
         COMMIT;
      ELSE
         ROLLBACK;
      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;

   PROCEDURE allocate_move_order (
      p_line_tbl        IN       inv_move_order_pub.trolin_tbl_type
    , x_return_status   OUT      VARCHAR2
    , x_msg_data        OUT      VARCHAR2
    , x_msg_count       OUT      NUMBER
   )
   IS
      x_line_tbl        inv_move_order_pub.trolin_tbl_type;
      l_trolin_tbl      inv_move_order_pub.trolin_tbl_type;
      l_mold_tbl        inv_mo_line_detail_util.g_mmtt_tbl_type;
      l_qty_detailed    NUMBER;
      l_qty_delivered   NUMBER;
      l_return_status   VARCHAR2 (1);
      v_msg_index_out   NUMBER;
      l_rsr_type        inv_reservation_global.mtl_reservation_tbl_type;
      i                 INTEGER;
      l_trolin_rec      inv_move_order_pub.trolin_rec_type;
   BEGIN
      x_line_tbl := p_line_tbl;

      IF x_line_tbl.COUNT > 0
      THEN
         FOR j IN x_line_tbl.FIRST .. x_line_tbl.LAST
         LOOP
            DBMS_OUTPUT.put_line (x_line_tbl (j).line_id);

            BEGIN
               inv_ppengine_pvt.create_suggestions (p_api_version              => 1.0
                                                  , p_init_msg_list            => fnd_api.g_false
                                                  , p_commit                   => fnd_api.g_false
                                                  , p_validation_level         => fnd_api.g_valid_level_none
                                                  , x_return_status            => x_return_status
                                                  , x_msg_count                => x_msg_count
                                                  , x_msg_data                 => x_msg_data
                                                  , p_transaction_temp_id      => x_line_tbl (j).line_id
                                                  , p_reservations             => l_rsr_type
                                                  , p_suggest_serial           => fnd_api.g_true
                                                  , p_plan_tasks               => FALSE
                                                  , p_quick_pick_flag          => ‘N’
                                                  , p_organization_id          => 207
                                                   );
               DBMS_OUTPUT.put_line (‘Return Status is :’ || x_return_status);
               DBMS_OUTPUT.put_line (‘Message Count is :’ || x_msg_count);

               IF x_return_status = ‘S’
               THEN
                  BEGIN
                     l_trolin_tbl := x_line_tbl;

                     IF (l_trolin_tbl.COUNT <> 0)
                     THEN
                        i := l_trolin_tbl.FIRST;

                        WHILE i IS NOT NULL
                        LOOP
                           IF (    l_trolin_tbl (i).return_status <> fnd_api.g_ret_sts_unexp_error
                               AND l_trolin_tbl (i).return_status <> fnd_api.g_ret_sts_error
                              )
                           THEN
                              l_trolin_rec := inv_trolin_util.query_row (l_trolin_tbl (i).line_id);
                              l_trolin_tbl (i) := l_trolin_rec;
                              l_qty_detailed := l_trolin_tbl (i).quantity_detailed;
                              l_qty_delivered := NVL (l_trolin_tbl (i).quantity_delivered, 0);

                              IF NVL (l_qty_detailed, 0) = 0
                              THEN
                                 l_mold_tbl := inv_mo_line_detail_util.query_rows (p_line_id => l_trolin_tbl (i).line_id);

                                 FOR j IN 1 .. l_mold_tbl.COUNT
                                 LOOP
                                    l_mold_tbl (j).transaction_status := 3;
                                    l_mold_tbl (j).transaction_mode := 1;
                                    l_mold_tbl (j).source_line_id := l_trolin_tbl (i).line_id;
                                    inv_mo_line_detail_util.update_row (l_return_status, l_mold_tbl (j));
                                 END LOOP;

                                 SELECT transaction_header_id, transaction_quantity
                                   INTO l_trolin_tbl (i).transaction_header_id, l_trolin_tbl (i).quantity_detailed
                                   FROM mtl_material_transactions_temp
                                  WHERE move_order_line_id = l_trolin_tbl (i).line_id;

                                 l_trolin_tbl (i).last_update_date := SYSDATE;
                                 l_trolin_tbl (i).last_update_login := fnd_global.login_id;

                                 IF l_trolin_tbl (i).last_update_login = -1
                                 THEN
                                    l_trolin_tbl (i).last_update_login := fnd_global.conc_login_id;
                                 END IF;

                                 l_trolin_tbl (i).last_updated_by := fnd_global.user_id;
                                 l_trolin_tbl (i).program_id := fnd_global.conc_program_id;
                                 l_trolin_tbl (i).program_update_date := SYSDATE;
                                 l_trolin_tbl (i).request_id := fnd_global.conc_request_id;
                                 l_trolin_tbl (i).program_application_id := fnd_global.prog_appl_id;
                                 inv_trolin_util.update_row (l_trolin_tbl (i));
                              END IF;
                           END IF;

                           i := l_trolin_tbl.NEXT (i);
                        END LOOP;
                     END IF;
                  END;
               ELSE
                  ROLLBACK;
               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;
         END LOOP;
      END IF;
   END;

   PROCEDURE transact_move_order (p_move_order_id IN NUMBER, x_return_status OUT VARCHAR2)
   IS
      l_header_id        NUMBER;
      l_program          VARCHAR2 (100);
      l_func             VARCHAR2 (100);
      l_args             VARCHAR2 (100);
      p_timeout          NUMBER;
      l_old_tm_success   BOOLEAN;
      l_rc_field         NUMBER;

      CURSOR c1 (p_header_id IN NUMBER)
      IS
         SELECT transaction_header_id
           FROM mtl_material_transactions_temp
          WHERE transaction_source_id = p_header_id;
   BEGIN
      FOR i IN c1 (p_move_order_id)
      LOOP
         l_program := ‘INXTPU’;
         l_func := l_program;
         l_args := l_program || ‘ ‘ || ‘TRANS_HEADER_ID=’ || TO_CHAR (i.transaction_header_id);
         p_timeout := 500;
         COMMIT;
         l_old_tm_success :=
            inv_pick_wave_pick_confirm_pub.inv_tm_launch (program      => l_program
                                                        , args         => l_args
                                                        , TIMEOUT      => p_timeout
                                                        , rtval        => l_rc_field
                                                         );

         IF l_old_tm_success
         THEN
            x_return_status := ‘S’;
            DBMS_OUTPUT.put_line (‘Result is :’ || ‘Success’);
         ELSE
            x_return_status := ‘E’;
            DBMS_OUTPUT.put_line (‘Result is :’ || ‘Failed’);
         END IF;

         IF x_return_status = ‘S’
         THEN
            COMMIT;
         ELSE
            ROLLBACK;
         END IF;
      END LOOP;
   END;
BEGIN
   mo_global.set_policy_context (‘S’, 204);
   inv_globals.set_org_id (207);
   fnd_global.apps_initialize (1005902, 50583, 401);
   DBMS_OUTPUT.put_line (‘Creating Move Order’);
   create_move_order (l_header_rec, l_line_tbl, x_return_status, x_msg_data, x_msg_count);

   IF x_return_status = ‘S’
   THEN
      COMMIT;
      DBMS_OUTPUT.put_line (‘Move Order Created’);

      IF l_line_tbl.COUNT > 0
      THEN
         x_return_status := NULL;
         x_msg_data := NULL;
         x_msg_count := NULL;
         DBMS_OUTPUT.put_line (‘Allocating Move Order’);
         allocate_move_order (l_line_tbl, x_return_status, x_msg_data, x_msg_count);

         IF x_return_status = ‘S’
         THEN
            COMMIT;
            DBMS_OUTPUT.put_line (‘Move Order Allocated’);
            x_return_status := NULL;
            DBMS_OUTPUT.put_line (‘Transacting Move Order’);
            DBMS_OUTPUT.put_line (‘l_header_rec.header_id :’ || l_header_rec.header_id);
            transact_move_order (l_header_rec.header_id, x_return_status);

            IF x_return_status = ‘S’
            THEN
               COMMIT;
               DBMS_OUTPUT.put_line (‘Move Order Transacted’);
            END IF;
         END IF;
      END IF;
   END IF;
END;