Delete Reservations based on Item , Organization Code and Sales Order number

CREATE OR REPLACE PROCEDURE xx_relieve_reservation (
   p_segment1 IN VARCHAR2,
   p_organization_code IN VARCHAR2,
   p_order_number IN VARCHAR2
)
AS
   -- Common Declarations
   l_api_version                 NUMBER                                          := 1.0;
   l_init_msg_list               VARCHAR2 (2)                                    := fnd_api.g_true;
   x_return_status               VARCHAR2 (2);
   x_msg_count                   NUMBER                                          := 0;
   x_msg_data                    VARCHAR2 (255);
   x_error_code                  NUMBER                                          := 0;
   -- 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)                                   := 'SMOHIDEENPITCHAI';
   l_resp_name                   VARCHAR2 (30)                                   := 'INVENTORY';
   -- API specific declarations
   l_rsv_rec                     inv_reservation_global.mtl_reservation_rec_type;
   l_serial_number               inv_reservation_global.serial_number_tbl_type;
   l_primary_relieved_qty        NUMBER                                          := 0;
   l_validation_flag             VARCHAR2 (2)                                    := fnd_api.g_true;
   x_mtl_reservation_tbl         inv_reservation_global.mtl_reservation_tbl_type;
   x_mtl_reservation_tbl_count   NUMBER                                          := 0;
   x_primary_relieved_qty        NUMBER                                          := 0;
   x_primary_remain_qty          NUMBER                                          := 0;

   -- Get the reservation to be relieved
   CURSOR c_item_reservations
   IS
      SELECT msi.organization_id,
             msi.inventory_item_id,
             msi.segment1,
             msi.primary_uom_code,
             res.reservation_id
        FROM mtl_system_items_b msi,
             mtl_parameters mp,
             mtl_reservations res,
             mtl_sales_orders mso
       WHERE msi.segment1 = p_segment1   -- Item Name ---
         AND msi.organization_id = mp.organization_id
         AND mp.organization_code = p_organization_code  -- Organization Short code --
         AND res.organization_id = msi.organization_id
         AND res.inventory_item_id = msi.inventory_item_id
         AND res.demand_source_header_id = mso.sales_order_id
         AND mso.segment1 = p_order_number;  -- Sales Order Number --
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);

   -- Get the first row
   FOR ir IN c_item_reservations
   LOOP
      l_rsv_rec.organization_id := ir.organization_id;
      l_rsv_rec.inventory_item_id := ir.inventory_item_id;
      l_rsv_rec.reservation_id := ir.reservation_id;
      DBMS_OUTPUT.put_line (   'c_item_reservations'
                            || ':  '
                            || ir.reservation_id);
      --EXIT;

      -- Get all reservations that exist for this item
      -- call API to get all the reservations for this item
      DBMS_OUTPUT.put_line ('=======================================================');
      DBMS_OUTPUT.put_line ('Calling INV_RESERVATION_PUB.Query_Reservation');
      inv_reservation_pub.query_reservation (p_api_version_number             => l_api_version,
                                             p_init_msg_lst                   => l_init_msg_list,
                                             x_return_status                  => x_return_status,
                                             x_msg_count                      => x_msg_count,
                                             x_msg_data                       => x_msg_data,
                                             p_query_input                    => l_rsv_rec,
                                             p_lock_records                   => fnd_api.g_false,
                                             p_sort_by_req_date               => inv_reservation_global.g_query_no_sort,
                                             p_cancel_order_mode              => inv_reservation_global.g_cancel_order_no,
                                             x_mtl_reservation_tbl            => x_mtl_reservation_tbl,
                                             x_mtl_reservation_tbl_count      => x_mtl_reservation_tbl_count,
                                             x_error_code                     => x_error_code
                                            );
      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
         FOR i IN 1 .. x_mtl_reservation_tbl_count
         LOOP
            DBMS_OUTPUT.put_line ('=======================================================');
            DBMS_OUTPUT.put_line (   'reservation_id              : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).reservation_id));
            DBMS_OUTPUT.put_line (   'requirement_date            : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).requirement_date, 'YYYY/MM/DD'));
            DBMS_OUTPUT.put_line (   'organization_id             : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).organization_id));
            DBMS_OUTPUT.put_line (   'inventory_item_id           : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).inventory_item_id));
            DBMS_OUTPUT.put_line (   'demand_source_type_id       : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).demand_source_type_id));
            DBMS_OUTPUT.put_line (   'demand_source_name          : '
                                  || x_mtl_reservation_tbl (i).demand_source_name);
            DBMS_OUTPUT.put_line (   'demand_source_header_id     : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).demand_source_header_id));
            DBMS_OUTPUT.put_line (   'demand_source_line_id       : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).demand_source_line_id));
            DBMS_OUTPUT.put_line (   'demand_source_line_detail   : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).demand_source_line_detail));
            DBMS_OUTPUT.put_line (   'primary_uom_code            : '
                                  || x_mtl_reservation_tbl (i).primary_uom_code);
            DBMS_OUTPUT.put_line (   'reservation_uom_code        : '
                                  || x_mtl_reservation_tbl (i).reservation_uom_code);
            DBMS_OUTPUT.put_line (   'reservation_quantity        : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).reservation_quantity));
            DBMS_OUTPUT.put_line (   'primary_reservation_quantity: '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).primary_reservation_quantity));
            DBMS_OUTPUT.put_line (   'detailed_quantity           : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).detailed_quantity));
            DBMS_OUTPUT.put_line (   'supply_source_type_id       : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).supply_source_type_id));
            DBMS_OUTPUT.put_line (   'supply_source_header_id     : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).supply_source_header_id));
            DBMS_OUTPUT.put_line (   'supply_source_line_id       : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).supply_source_line_id));
            DBMS_OUTPUT.put_line (   'supply_source_name          : '
                                  || (x_mtl_reservation_tbl (i).supply_source_name));
            DBMS_OUTPUT.put_line (   'supply_source_line_detail   : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).supply_source_line_detail));
            DBMS_OUTPUT.put_line (   'subinventory_code           : '
                                  || x_mtl_reservation_tbl (i).subinventory_code);
            DBMS_OUTPUT.put_line (   'ship_ready_flag             : '
                                  || TO_CHAR (x_mtl_reservation_tbl (i).ship_ready_flag));
            DBMS_OUTPUT.put_line (   'staged_flag                 : '
                                  || x_mtl_reservation_tbl (i).staged_flag);
            DBMS_OUTPUT.put_line ('=======================================================');
         END LOOP;
      END IF;

      -- call API to relieve all the queried reservations for this item
      DBMS_OUTPUT.put_line ('=======================================================');
      DBMS_OUTPUT.put_line ('Calling INV_RESERVATION_PUB.Relieve_Reservation');

      FOR i IN 1 .. x_mtl_reservation_tbl_count
      LOOP
         l_primary_relieved_qty := x_mtl_reservation_tbl (i).reservation_quantity;
                                                                                  --- Passing whole quantity to relieve
         -- Call the API to relieve reservations for the provided serial numbers
         inv_reservation_pub.relieve_reservation
                                               (p_api_version_number             => l_api_version,
                                                p_init_msg_lst                   => l_init_msg_list,
                                                x_return_status                  => x_return_status,
                                                x_msg_count                      => x_msg_count,
                                                x_msg_data                       => x_msg_data,
                                                p_rsv_rec                        => x_mtl_reservation_tbl (i),
                                                p_primary_relieved_quantity      => l_primary_relieved_qty,
                                                p_relieve_all                    => fnd_api.g_true
                                                                                 ---- True for Quantity Relieves to Zero
                                                                                                  ,
                                                p_original_serial_number         => l_serial_number,
                                                p_validation_flag                => l_validation_flag,
                                                x_primary_relieved_quantity      => x_primary_relieved_qty,
                                                x_primary_remain_quantity        => x_primary_remain_qty
                                               );
         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 ('Item reserved quantity relieve details');
            DBMS_OUTPUT.put_line (   'Reservation ID:'
                                  || x_mtl_reservation_tbl (i).reservation_id);
            DBMS_OUTPUT.put_line (   'Item ID:'
                                  || x_mtl_reservation_tbl (i).inventory_item_id);
            DBMS_OUTPUT.put_line (   'Organization ID:'
                                  || x_mtl_reservation_tbl (i).organization_id);
            DBMS_OUTPUT.put_line (   'Primary Relieved Quantity: '
                                  || x_primary_relieved_qty);
            DBMS_OUTPUT.put_line (   'Primary Remain Quantity: '
                                  || x_primary_remain_qty);
         END IF;
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception Occured :');
      DBMS_OUTPUT.put_line (   SQLCODE
                            || ':'
                            || SQLERRM);
      DBMS_OUTPUT.put_line ('=======================================================');
END;

----------------------------------------------------------
BEGIN
   xx_relieve_reservation ('EAE05209965',
                           'UKI',
                           '70033015'
                          );                                -- Item , Organization Code, Sales Order number respectively
END;

============================================================