Item Category API

最新推荐文章于 2025-03-23 10:30:59 发布

转载最新推荐文章于 2025-03-23 10:30:59 发布 · 226 阅读

本文详细阐述了如何使用PL/SQL脚本批量更新特定库存项的类别，并通过调用自定义过程完成此操作，同时提供了错误处理机制，确保更新过程的准确性。

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;