使用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;