INV API: Allocate Inventory Move Order inv_ppengine_pvt.create_suggestions

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

   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;
BEGIN
   l_line_tbl (1).line_id := 3929705;
   allocate_move_order (l_line_tbl, x_return_status, x_msg_data, x_msg_count);
   COMMIT;
END;