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

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

CREATE OR REPLACE
PROCEDURE XXTA_RelieveReservation(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) := 'XXXUSER_NAME';
        l_resp_name  VARCHAR2(30) := 'XXXXesponsibility_key';  
       
        -- 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
        AND msi.organization_id = mp.organization_id
        AND mp.organization_code = p_organization_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 ;
       
       
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
XXTA_RelieveReservation('B6-M','TB1','413098');-- Item , Organization Code, Sales Order number respectively
END;
/
SELECT msi.organization_id, msi.inventory_item_id, msi.segment1, msi.primary_uom_code,res.reservation_id,mso.segment1
        FROM mtl_system_items_b msi, mtl_parameters mp, mtl_reservations res, mtl_Sales_orders mso
        WHERE msi.segment1 = 'B6-M'
        AND msi.organization_id = mp.organization_id
        AND mp.organization_code = 'TB1'
        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              ='413098' ;
/