Oracle EBS INV更新保留

CREATE or REPPLACE PROCEDURE UpdateReservation
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);
    
        -- 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) := 'MFG';
        l_resp_name    VARCHAR2(50) := 'Manufacturing and Distribution Manager';    
        
        -- API specific declarations
        l_rsv_rec                   INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
        l_new_rsv_rec               INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
        l_serial_number             INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
        l_new_serial_number         INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;        
        l_validation_flag           VARCHAR2(2) := FND_API.G_TRUE;  

        -- Load required serial numbers that are reserved
        CURSOR c_serials IS
        SELECT msn.inventory_item_id , msn.serial_number 
        FROM mtl_system_items_b msi, mtl_serial_numbers msn, mtl_parameters mp
        WHERE msi.organization_id = mp.organization_id 
        AND msi.organization_id = msn.current_organization_id
        AND msi.inventory_item_id = msn.inventory_item_id
        AND msn.group_mark_id IS NOT NULL
        AND msi.serial_number_control_code not in (1, 6)  -- item is not serial controlled / controlled at sales order issue 
        AND msi.segment1 = 'SU_TEST_STS3'
        AND mp.organization_code = 'M1'
        AND msn.serial_number BETWEEN '' AND ''
        ORDER BY msn.serial_number DESC;
        
        -- Load reservation for this item
        CURSOR c_item_reservations IS
        SELECT msi.organization_id, msi.inventory_item_id, res.reservation_id, res.reservation_quantity, res.demand_source_name
        FROM mtl_system_items_b msi, mtl_parameters mp, mtl_reservations res
        WHERE msi.segment1 = 'SU_TEST_STS3'
        AND mp.organization_code = 'M1'
        AND msi.organization_id = mp.organization_id 
        AND res.organization_id = msi.organization_id
        AND res.inventory_item_id = msi.inventory_item_id;
        
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_vl
  WHERE responsibility_name = 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.reservation_id       := ir.reservation_id;
            l_rsv_rec.demand_source_name   := ir.demand_source_name;
            l_rsv_rec.reservation_quantity := ir.reservation_quantity; 
  
            -- Update Demand Source Name, reservation qty for reservations that exist for this item
            l_new_rsv_rec.reservation_id       := ir.reservation_id;
            l_new_rsv_rec.demand_source_name   := ir.demand_source_name; --||'_0723';
            l_new_rsv_rec.reservation_quantity := 10;   -- new reservation quantity 
  
            BEGIN
                  -- Initialize Serials to be updated / reserved
                  FOR ser IN c_serials LOOP
                      l_serial_number(l_row_cnt).inventory_item_id := ser.inventory_item_id;
                      l_serial_number(l_row_cnt).serial_number     := ser.serial_number;
                  
                      l_new_serial_number(l_row_cnt).inventory_item_id := ser.inventory_item_id;
                      l_new_serial_number(l_row_cnt).serial_number     := ser.serial_number + 10;
                      
                      l_row_cnt := l_row_cnt + 1;
                  END LOOP;
             EXCEPTION
              WHEN NO_DATA_FOUND THEN
                dbms_output.put_line('Item not serial controlled / serials not provided');
             END; 
          
            -- call API to update all the reservations for this item
            DBMS_OUTPUT.PUT_LINE('=======================================================');
            DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Update_Reservation');         
            INV_RESERVATION_PUB.UPDATE_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_ORIGINAL_RSV_REC       =>   l_rsv_rec
                  , P_TO_RSV_REC       =>   l_new_rsv_rec
                  , P_ORIGINAL_SERIAL_NUMBER =>   l_serial_number
                  , P_TO_SERIAL_NUMBER       =>   l_new_serial_number
                  , P_VALIDATION_FLAG       =>   l_validation_flag 
                  , P_CHECK_AVAILABILITY     =>   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
              DBMS_OUTPUT.PUT_LINE('Reservation ID           :'||l_new_rsv_rec.reservation_id);
              DBMS_OUTPUT.PUT_LINE('Demand Source Name (old) :'||l_rsv_rec.demand_source_name);
              DBMS_OUTPUT.PUT_LINE('Demand Source Name (new) :'||l_new_rsv_rec.demand_source_name);            
              DBMS_OUTPUT.PUT_LINE('Reservation Qty  (old)   :'||l_rsv_rec.reservation_quantity);
              DBMS_OUTPUT.PUT_LINE('Reservation Qty  (new)   :'||l_new_rsv_rec.reservation_quantity);            
          END IF;
           
          DBMS_OUTPUT.PUT_LINE('=======================================================');
         
      END LOOP;
     
EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
          DBMS_OUTPUT.PUT_LINE('=======================================================');
 	END UpdateReservation;