1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Unable to update 'inventory_item_status_code' of mtl_system_items_b table

Discussion in 'Oracle SCM & Manufacturing' started by ssankars, Jul 11, 2012.

  1. ssankars

    ssankars Premium Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    Hyderabad
    Hi

    I have a requirement like i have to update the value of 'inventory_item_status_code' column in mtl_system_items_b table using API in all organization_id.
    So i am using inv_item_api.update_item_row API for updation of item row. I am able to update the column for all organization but i am not able to update for the master org row.
    So please help me out for this problem.

    Awaiting for UR Response.

    Advance Thanks

    Sidharth
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Sidharth,

    Can you post some more details like what error you are getting while doing that and what code you have used to do that. By that we can analyze the issue.
     
  3. ssankars

    ssankars Premium Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    Hyderabad
    BElow is my code which I am using for updation of inventory_item_status_code for an item in all organization.
    but this code is not working properly.
    can you suggest what will be the alternative solution for this..

    Thanks
    Below is my code:

    CREATE OR REPLACE PROCEDURE xxinv_upd_status_prc (
    i_inventory_item_id IN NUMBER, --6039162
    i_organization_id IN NUMBER --5621
    )
    IS
    CURSOR item_org_cur
    IS
    SELECT organization_id
    FROM mtl_system_items_b
    WHERE inventory_item_id = i_inventory_item_id;

    v_item_rec apps.inv_item_api.item_rec_type;
    v_return_status VARCHAR2 (20);
    v_flag NUMBER := 0;
    BEGIN
    FOR item_org_rec IN item_org_cur
    LOOP
    BEGIN
    SELECT 1
    INTO v_flag
    FROM apps.mtl_system_items_b msib,
    apps.cst_item_cost_details cicd
    WHERE msib.inventory_item_id = cicd.inventory_item_id
    AND msib.organization_id = item_org_rec.organization_id
    AND msib.planning_make_buy_code = 2 -- Condition for Checking Buy ITEM
    AND cicd.item_cost IN (0, NULL) -- Condition for Checking Item Cost with 0
    AND msib.inventory_item_id = i_inventory_item_id;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ( 'EXCEPTION : '
    || SQLERRM);
    v_flag := 0;
    END;

    IF v_flag = 1 THEN
    DBMS_OUTPUT.put_line ('v_flag = 1');

    FOR v_item_rec IN inv_item_api.item_csr (i_inventory_item_id, item_org_rec.organization_id)
    LOOP
    DBMS_OUTPUT.put_line ('FOR v_item_rec IN INV_ITEM_API.Item_csr');

    BEGIN
    v_item_rec.inventory_item_status_code := 'Phase-In';
    inv_item_api.update_item_row (p_item_rec => v_item_rec,
    p_update_item_tl => TRUE,
    p_lang_flag => 'B',
    x_return_status => v_return_status
    );

    IF v_return_status = fnd_api.g_ret_sts_success THEN
    DBMS_OUTPUT.put_line ('Updation Success ful');
    ELSE
    DBMS_OUTPUT.put_line ('Error in updation of Mtl System');
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ( 'EXCEPTION : '
    || SQLERRM);
    v_flag := 0;
    END;
    END LOOP;
    ELSE
    DBMS_OUTPUT.put_line ('inventory_item_status_code updation is not required ');
    END IF;
    END LOOP;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ( 'EXCEPTION : '
    || SQLERRM);
    END xxinv_upd_status_prc;
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi sidharth,

    That works fine for me after making few changes. I have master organization as 204 which is for 204.

    Your part of procedure:
    Code (Text):
    SELECT 1
    INTO v_flag
    FROM apps.mtl_system_items_b msib,
    apps.cst_item_cost_details cicd
    WHERE msib.inventory_item_id = cicd.inventory_item_id
    AND msib.organization_id = item_org_rec.organization_id
    AND msib.planning_make_buy_code = 2 -- Condition for Checking Buy ITEM
    AND cicd.item_cost IN (0, NULL) -- Condition for Checking Item Cost with 0
    AND msib.inventory_item_id = i_inventory_item_id;
    In this we are not able to get multiple records into v_flag. This query is getting error as below:

    Why because you have given "cicd.item_cost in (0,NULL)" and in this you have passed two values. So it does not fetch two records at a time into v_flag from that query. You need to modify this according to your requirement. If you want only 0 then pass it as 0 and then give a try.

    Its working fine for me after changing that query as below:

    Code (Text):
    SELECT 1
    INTO v_flag
    FROM apps.mtl_system_items_b msib,
    apps.cst_item_cost_details cicd
    WHERE msib.inventory_item_id = cicd.inventory_item_id
    AND msib.organization_id = item_org_rec.organization_id
    AND msib.planning_make_buy_code = 2 -- Condition for Checking Buy ITEM
    AND cicd.item_cost IN (100) -- Condition for Checking Item Cost with 0
    AND msib.inventory_item_id = i_inventory_item_id
    and cicd.ORGANIZATION_ID  = item_org_rec.organization_id;
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Sidharth,

    Have you solved your issue or still facing any problems??
     
  6. P D Sharma

    P D Sharma Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    You can try this update statement

    update mtl_system_items_b
    set inventory_item_status_code='Inactive or Inactive' ( Select one as per your requirement)
    where segment1='Item_code'
    ---and organization_id=( Give Organization_id if you want to update in specific unit)

    Note - Kindly try it on test Instance fist and if your purpose is resolved then kindly send a mail to me at pd.sharma@sandhar.in)
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Sharma,

    You are not supposed to do such update statements on them and its not safe to do such things. Rather than updating directly with update statement we can use API's and below OP is successfully working after doing one simple modification as I said in my earlier post.