批量更改Item的属性

用户需求：需要更改1200个Item的Lead_time_lot_size的值。

1. 用dataload是一种办法，但是比较费时间。

2. 后面用临时表+Update直接改mtl_system_items_b表。

 

DECLARE

   --共1143行

    CURSOR cur_upd_data IS

        SELECT segment1,

                 LEAD_TIME_LOT_SIZE

          FROM inv.ascp_inv_item_temp;

BEGIN

   

    FOR rec_upd_data IN cur_upd_data

    LOOP

        UPDATE apps.mtl_system_items_b t

            SET lead_time_lot_size = rec_upd_data.LEAD_TIME_LOT_SIZE

         WHERE t.organization_id = &org_id

            AND T.ITEM_TYPE = 'FG'

            AND t.inventory_item_status_code = 'Active'

            AND t.segment1 = rec_upd_data.segment1;

    END LOOP;

   

   COMMIT;

END;

 

3. 当然也可以用临时表+标准的API修改ITEM相关属性。

DECLARE
    l_item_rec      inv_item_grp.item_rec_type;
    l_error_tbl     inv_item_grp.error_tbl_type;
    x_item_rec      inv_item_grp.item_rec_type;
    l_return_status VARCHAR2(40);
BEGIN
    inv_globals.set_org_id(&org_id);
    fnd_global.apps_initialize(12843, 50749, 401);--userid,resposi_id,app_id
    l_item_rec.inventory_item_id   := 423007;
    l_item_rec.organization_id     := &org_id; --You master organization
    l_item_rec.lead_time_lot_size := 100000;
    inv_item_grp.update_item(p_commit           => fnd_api.g_false,
                                     p_lock_rows        => fnd_api.g_true,
                                     p_validation_level => fnd_api.g_valid_level_full,
                                     p_item_rec         => l_item_rec,
                                     x_item_rec         => x_item_rec,
                                     x_return_status    => l_return_status,
                                     x_error_tbl        => l_error_tbl);
    DBMS_OUTPUT.put_line(l_return_status);

    IF l_error_tbl.COUNT() > 0 THEN
        FOR i IN 1 .. l_error_tbl.COUNT
        LOOP
            DBMS_OUTPUT.put_line(l_error_tbl.MESSAGE_TEXT);
            DBMS_OUTPUT.put_line(l_error_tbl.message_name);
        END LOOP;
    END IF;
   COMMIT;
END;