Oracle EBS INV 更新状态

使用API改变现有物料状态，改成如下：On-Hand, Subinventory, Locator, Lot & Serial。参数使用如下：H, O, S, Z, L。对应如下：
'H' - Onhand
'O' - Lot  
'S' – Serial
'Z' - Subinventory (Zone)
'L' - Locator
具体代码为：
CREATE OR REPLACE
PROCEDURE XX_UPDATE_MTL_STS AS 

        -- Common Declarations
        l_api_version		   NUMBER      := 1.0; 
        l_init_msg_list		 VARCHAR2(2) := FND_API.G_TRUE; 
        l_commit           VARCHAR2(2) := FND_API.G_FALSE; 
        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_object_type   VARCHAR2 (20) ; 
        l_status_rec    INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type ;      

BEGIN

    -- Initialize variables
    l_object_type  := 'H';   -- 'O' = Lot , 'S' = Serial, 'Z' = Subinventory, 'L' = Locator, 'H' = Onhand

    l_status_rec.organization_id       := 209;
    l_status_rec.inventory_item_id     := 516963;
    l_status_rec.lot_number            := 'EXPLOT200';
    l_status_rec.zone_code             := 'RIP';
    l_status_rec.locator_id            := NULL;
    l_status_rec.status_id             := 1;    -- select status_id, status_code from mtl_material_statuses_vl;
    l_status_rec.update_reason_id      := 305;  --'Reviewed';  -- select reason_id, reason_name from mtl_transaction_reasons where reason_type_display = 'Update Status';
    l_status_rec.update_method         := 2;
/*    
    l_status_rec.serial_number         := fnd_api.g_miss_char;
    l_status_rec.to_serial_number      := fnd_api.g_miss_char;
    l_status_rec.lpn_id                := fnd_api.g_miss_num;
    l_status_rec.initial_status_flag   := fnd_api.g_miss_char;
    l_status_rec.from_mobile_apps_flag := fnd_api.g_miss_char;
    l_status_rec.grade_code            := fnd_api.g_miss_char;
    l_status_rec.primary_onhand        := fnd_api.g_miss_num;
    l_status_rec.secondary_onhand      := fnd_api.g_miss_num;
    l_status_rec.group_id              := fnd_api.g_miss_num; 
    l_status_rec.pending_status        := fnd_api.g_miss_num;  
*/
    -- 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 );
     
    -- call API to update material status
    DBMS_OUTPUT.PUT_LINE('=======================================================');
    DBMS_OUTPUT.PUT_LINE('Calling INV_MATERIAL_STATUS_PUB.Update_Status');        
   
    INV_MATERIAL_STATUS_PUB.update_status
        (  p_api_version_number   =>    l_api_version 
         , p_init_msg_lst         =>    l_init_msg_list       
         , p_commit               =>    l_commit             
         , x_return_status        =>    x_return_status      
         , x_msg_count            =>    x_msg_count          
         , x_msg_data             =>    x_msg_data           
         , p_object_type          =>    l_object_type        
         , p_status_rec           =>    l_status_rec         
         );
     
     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;
     
    DBMS_OUTPUT.PUT_LINE('=======================================================');
   
END XX_UPDATE_MTL_STS;