DECLARE
CURSOR C3 IS
SELECT TRIM(A ) item_number, TRIM(D) g01
FROM TMP_CCC
where TRIM(d) is not null;
v_process_id number;
v_inventory_item_id number;
v_item_insert number:=0 ;
v_category_id number;
v_po_category_set_id number;
v_om_category_set_id number;
v_item_count number:=0 ;
v_count1 number:=0 ;
v_request_id number;
v_organization_id number;
xx_ls_phase varchar2(240 );
xx_ls_status varchar2(240 );
xx_ls_dummy1 varchar2(240 );
xx_ls_dummy2 varchar2(240 );
xx_ls_dummy3 varchar2(240 );
xx_lb_return boolean;
v_buyer_id number;
v_planner_code number;
v_template_id number;
v_template_name varchar2(30 );
x_item_id number;
dummy number;
x_category_id number;
v_uom varchar2(10 );
v_weight_uom varchar2(10 );
x_errbuf VARCHAR2(200 );
x_retcode VARCHAR2(200 );
V_CATEGORY_SET_ID_O number;
V_CATEGORY_ID_O number;
V_CATEGORY_CONCAT_SEGS VARCHAR2(163 );
V_CATEGORY_SET_ID number;
v_exist VARCHAR2(1 ):= 'N';
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO (84); --Organization_ID
--DBMS_APPLICATION_INFO.SET_CLIENT_INFO(FND_PROFILE.VALUE('LANG'));
select Mtl_system_items_intf_sets_s.nextval
into v_process_id
from dual;
v_exist := 'N';
for r in c3 loop
v_exist := 'Y';
begin
select a.CATEGORY_ID
,b. category_set_id
into v_category_id
,v_po_category_set_id
from mtl_categories_b_kfv a
,MTL_CATEGORY_SETS_V b
WHERE a.structure_id = b .structure_id
and b.CATEGORY_SET_NAME = 'Group01' --只更新指定的Category Name
and a.CONCATENATED_SEGMENTS = r .g01;
exception
when others then
v_category_id := '';
v_po_category_set_id := '';
end;
select inventory_item_id into v_inventory_item_id from mtl_system_items_b where segment1=r.item_number
and organization_id=84 ;
if v_category_id is not null then
BEGIN
SELECT CATEGORY_SET_ID, CATEGORY_ID, CATEGORY_CONCAT_SEGS
INTO V_CATEGORY_SET_ID_O, V_CATEGORY_ID_O, V_CATEGORY_CONCAT_SEGS
FROM MTL_ITEM_CATEGORIES_V A,
MTL_SYSTEM_ITEMS_B B
WHERE A.Organization_id = B .Organization_id
AND A.Organization_id = 84
AND A.INVENTORY_ITEM_ID = B .INVENTORY_ITEM_ID
AND A.STRUCTURE_ID = 50272
AND B.SEGMENT1 = trim(upper( r.item_number ));
EXCEPTION
WHEN OTHERS THEN
V_CATEGORY_SET_ID_O := '';
V_CATEGORY_ID_O := '';
V_CATEGORY_CONCAT_SEGS := '';
END;
BEGIN
IF V_CATEGORY_CONCAT_SEGS IS NOT NULL AND V_CATEGORY_CONCAT_SEGS ! = r.FUHSING_STOCKER THEN
Insert into MTL_Item_Categories_Interface (
inventory_item_id,
Organization_id,
Transaction_type,
process_flag,
Item_number,
Set_process_id,
category_set_id,
category_id,
OLD_CATEGORY_ID)
Values(
v_inventory_item_id,
84,
'UPDATE',
1,
trim(upper(r. item_number)),
v_process_id,
V_CATEGORY_SET_ID_O,
v_category_id, -- NEW_CATEGORY_ID
V_CATEGORY_ID_O -- OLD_CATEGORY_ID
);
END IF;
IF V_CATEGORY_CONCAT_SEGS IS NULL THEN
Insert into MTL_Item_Categories_Interface (
inventory_item_id,
Organization_id,
last_update_date,
last_updated_by,
creation_date,
----------------------5
created_by,
last_update_login,
category_set_id,
category_id,
Transaction_type,
----------------------10
process_flag,
Item_number,
Set_process_id
)Values(
v_inventory_item_id,
84,
sysdate,
fnd_profile.VALUE('USER_ID' ),--fnd.user,
sysdate,
----------------------5
fnd_profile.VALUE('USER_ID' ),--fnd.user,
fnd_profile.VALUE('USER_ID' ),--fnd.user,
v_po_category_set_id,
v_category_id,
'CREATE',
----------------------10
1,
trim(upper(r. item_number)),
v_process_id
);
END IF;
END;
end if;
commit;
end loop;
END;
/
執行完上述的PL/SQL之後，可以到ERP來執行。
1、找出set_process_id
select set_process_id from mtl_item_categories_interface ;
2、執行ERP，填入Set_Process_id
