Oracle EBS - R12 - Inventory - Oracle API to Update Lot Origin Date of Item Lot Numbers

 -- R12 - INV - Sample Script to Update Lot Number Lot Origin Date usinginv_lot_api_pub.update_inv_lot

DECLARE

    l_object_id      NUMBER;

    l_return_status  VARCHAR2(1);

    l_msg_count      NUMBER;

    l_msg_data       VARCHAR2(4000);

    l_expire_date    DATE;

    x_lot_rec        mtl_lot_numbers%rowtype;

    l_lot_rec        mtl_lot_numbers%rowtype;

    l_source         NUMBER;

    l_api_version    NUMBER := 1.0;

    l_init_msg_list  VARCHAR2(1000) := fnd_api.g_false;

    l_commit         VARCHAR2(1000) := fnd_api.g_false;

    CURSOR c_item_info IS

    SELECT

        inventory_item_id,

        organization_id,

        lot_number,

        creation_date,

        origination_date,

        c_attribute1

    FROM

        mtl_lot_numbers

    WHERE

            1 = 1

        AND trunc(origination_date) BETWEEN trunc(TO_DATE('03/03/24', 'MM/DD/YY')) AND trunc(TO_DATE('03/05/24', 'MM/DD/YY'))

        AND trunc(creation_date) != trunc(origination_date)

        AND organization_id IN ( 85, 97 )

--AND TRUNC(CREATION_DATE) >= SYSDATE-150

--and C_ATTRIBUTE1='WMOTCWRJAB'

--and c_attribute1 is not null

        AND lot_number = '1710010' --'1542009' --'1423007'

        ;

BEGIN

-- initialization required for R12

    mo_global.set_policy_context('S', 102);

    mo_global.init('INV');

-- Initialization for Organization_id

    inv_globals.set_org_id(102);

-- initialize environment

    fnd_global.apps_initialize(user_id => 11445,-----------------------------------> pass in user_id User Id=11445 ,

                              resp_id => 20634,

                              resp_appl_id => 401);

    

    FOR i IN c_item_info LOOP

    

    l_lot_rec.inventory_item_id:=i.inventory_item_id;

    l_lot_rec.organization_id:=i.organization_id;

    l_lot_rec.lot_number:=i.lot_number;

    l_lot_rec.origination_date:=i.creation_date;

    

        dbms_output.put_line('Calling inv_lot_api_pub.update_inv_lot API to Update Lot Numbers');

        dbms_output.put_line('*********************************************');

        inv_lot_api_pub.update_inv_lot(x_return_status => l_return_status, x_msg_count => l_msg_count,

                                      x_msg_data => l_msg_data,

                                      x_lot_rec => x_lot_rec,

                                      p_lot_rec => l_lot_rec,

                                      p_source => l_source,

                                      p_api_version => 1.0,

                                      p_init_msg_list => l_init_msg_list,

                                      p_commit => l_commit);

        dbms_output.put_line('The Status Returned by the API is => ' || l_return_status);

        IF l_return_status = fnd_api.g_ret_sts_success THEN

            COMMIT;

        ELSE

            ROLLBACK;

        END IF;

        dbms_output.put_line('x_msg_count :' || l_msg_count);

        dbms_output.put_line('x_msg_data :' || l_msg_data);

    END LOOP;

END;