Item Category API

SET serveroutput on size 100000;
declare
l_status  VARCHAR2(10);
l_err_code  NUMBER;
l_msg_count  NUMBER;
l_msg_data  VARCHAR2(1500);
l_cnt       number := 0;

 g_user_id number := 9577 ;
 g_resp_id number := 20634 ;
 g_appl_id number := 401;
 cursor cur_items is select msib.inventory_item_id, msib.segment1
  from mtl_system_items_b msib
 where msib.organization_id = 101
   and msib.segment1 in
       ('0025000', '0025300', '0025500', '0025600', '0025700', '0025800',
        '0025900', '0026100', '0026200', '0026300', '0026500', '0026600',
        '0026700', '0026800', '0026900', '0027000', '0027100', '0027300',
        '0027400', '0027600', '0027800', '0027900', '0028100', '0028200',
        '5298177400', '5298178400', '5298178500', '5298192400', '5298212400',
        '5298212500', '5298220400');
BEGIN
Fnd_Global.apps_initialize(g_user_id, g_resp_id, g_appl_id);

FOR c1 IN cur_items LOOP
/*  Inv_Item_Category_Pub.Update_Category_Assignment(1.0
  ,'T' --p_init_msg_list
  ,'F' --p_commit
  ,4164 --p_category_id
  , 4123--p_old_category_id
  ,-1 --p_category_set_id
  ,360871 -- p_inventory_item_id :0300000200
  ,101 --p_organization_id
  ,l_status, l_err_code, l_msg_count, l_msg_data);*/
/*  Inv_Item_Category_Pub.Create_Category_Assignment(p_api_version => 1.0
                                                  ,p_init_msg_list => 'T'
                                                  ,p_commit => 'F'
                                                  ,x_return_stauts => l_status
                                                  ,x_errorcode => l_err_code
                                                  ,x_msg_count => l_msg_count
                                                  ,x_msg_data => l_msg_data
                                                  ,p_category_id => 33172
                                                  ,p_category_set_id => 1100000121
                                                  ,p_inventory_item_id => c1.inventory_item_id
                                                  ,p_organization_id => 101
                                                  );*/
  Inv_Item_Category_Pub.Create_Category_Assignment(1.0
                                                  ,'T'
                                                  ,'F'
                                                  ,l_status
                                                  ,l_err_code
                                                  ,l_msg_count
                                                  ,l_msg_data
                                                  ,33172
                                                  ,1100000121
                                                  ,c1.inventory_item_id
                                                  ,101
                                                  );                                                 
 
  IF l_status != 'S' THEN
    l_msg_data := replace(l_msg_data,chr(0),':');
    DBMS_OUTPUT.PUT_LINE(c1.segment1||'-'||'Retured Status:'||l_status||' , Err code:'||l_err_code||',Msg count:' || l_msg_count || ' ,Error msg:'||l_msg_data);
     FOR i IN 1..l_msg_count LOOP
       DBMS_OUTPUT.PUT_LINE( 'error line ' || i || ':' || FND_MSG_PUB.Get(p_encoded=>'F')  );      
     END LOOP;
     rollback;
  ELSE
    DBMS_OUTPUT.PUT_LINE(c1.segment1||'-'||'Success!');
    commit; 
 
  Update mtl_system_items_b
 set last_updated_by = 8777, last_update_date=sysdate
 where inventory_item_id = c1.inventory_item_id and organization_id = 101;
 l_cnt := l_cnt + 1;
 
  END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Count of items updated '||l_cnt);
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/785120/viewspace-364583/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/785120/viewspace-364583/