DECLARE
--MTL_PENDING_ITEM_STATUS
-- select INVENTORY_ITEM_ID from R12_IMPORT_ITEM_201106@wsdb group by INVENTORY_ITEM_ID having count(ORGANIZATION_ID) >1
CURSOR cur
IS
SELECT b.ORGANIZATION_ID, b.INVENTORY_ITEM_ID, b.segment1,
a.INV_CATEGORY_ID ,a.INV_CATEGORY,
a.COST_CATEGORY_ID ,a.COST_CATEGORY,
a.PO_CATEGORY_ID,a.PO_CATEGORY,
a.MFG_CATEGORY_ID,a.MFG_CATEGORY,
a.SALES_CATEGORY_ID,a.SALES_CATEGORY
FROM R12_IMPORT_ITEM_201106@wsdb a, mtl_system_items b
WHERE a.ORGANIZATION_ID = b.ORGANIZATION_ID AND a.SEGMENT1 = b.SEGMENT1
-- and a.INVENTORY_ITEM_ID=374978 --
-- AND ROWNUM <= 10
order by b.INVENTORY_ITEM_ID,decode( a.r11_organization_id ,76,1,4,2,1216,3,118,4,3,5 );
--MTL_INTERFACE_ERRORS
c_inv_category_set_id NUMBER := 1100000043;
c_po_category_set_id NUMBER := 1100000044;
c_sale_category_set_id NUMBER := 1100000045;
c_cst_category_set_id NUMBER := 1100000041;
c_mfg_category_set_id NUMBER := 1100000042;
rec_imp MTL_ITEM_CATEGORIES_INTERFACE%rowtype;
rec_empty MTL_ITEM_CATEGORIES_INTERFACE%rowtype;
l_transaction_type VARCHAR2 (20);
vPROCESS_FLAG NUMBER := 1;
vTRANSACTION_TYPE VARCHAR2 (10) := NULL;
vSET_PROCESS_ID NUMBER := 1694;
vmas_org NUMBER := 103;
vCREATED_BY NUMBER := 1157;
l_organization_id NUMBER;
l_old_category_id NUMBER;
curr_item_id NUMBER;
pre_item_id NUMBER;
skip_mas boolean;
i NUMBER:=0;
p_requestid NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (user_id => 1157,
resp_id => 50659,
resp_appl_id => 401);
pre_item_id :=0;
FOR rec IN cur
LOOP
i:=i+1;
curr_item_id :=rec.INVENTORY_ITEM_ID ;
if curr_item_id=pre_item_id then
skip_mas :=true;
dbms_output.put_line('same');
else
skip_mas :=false;
end if;
pre_item_id :=curr_item_id;
rec_imp := rec_empty;
BEGIN
l_organization_id :=vmas_org;--rec.ORGANIZATION_ID;
rec_imp.CREATED_BY := vCREATED_BY;
rec_imp.SET_PROCESS_ID := vSET_PROCESS_ID;
rec_imp.PROCESS_FLAG:=vPROCESS_FLAG;
rec_imp.last_update_login:= vCREATED_BY;
rec_imp.last_update_date:=sysdate;
rec_imp.creation_date:=sysdate;
rec_imp.inventory_item_id := rec.inventory_item_id;
rec_imp.organization_id := l_organization_id;
/* inv category*/
IF (rec.INV_CATEGORY_ID IS NOT NULL) and not skip_mas
THEN
rec_imp.category_set_id := c_inv_category_set_id;
BEGIN
begin
SELECT 'UPDATE',category_id
INTO l_transaction_type,l_old_category_id
FROM mtl_item_categories mic
WHERE inventory_item_id = rec.inventory_item_id
AND ORGANIZATION_ID = vmas_org
AND CATEGORY_SET_ID = rec_imp.category_set_id;
rec_imp.OLD_CATEGORY_ID:=l_old_category_id;
exception
when no_data_found then
l_transaction_type := 'CREATE';
end;
rec_imp.TRANSACTION_TYPE :=l_transaction_type;
rec_imp.category_id:=rec.INV_CATEGORY_ID;
IF rec.INV_CATEGORY_ID <> nvl(l_old_category_id,-1)
THEN
insert into MTL_ITEM_CATEGORIES_INTERFACE values rec_imp;
commit;
END IF;
END;
END IF;
/* po category*/
IF (rec.PO_CATEGORY_ID IS NOT NULL) and not skip_mas
THEN
rec_imp.category_set_id :=null;
rec_imp.TRANSACTION_TYPE :=null;
rec_imp.category_id:=null;
rec_imp.OLD_CATEGORY_ID:=null;
rec_imp.category_set_id := c_po_category_set_id;
BEGIN
begin
SELECT 'UPDATE',category_id
INTO l_transaction_type,l_old_category_id
FROM mtl_item_categories mic
WHERE inventory_item_id = rec.inventory_item_id
AND ORGANIZATION_ID = vmas_org
AND CATEGORY_SET_ID = rec_imp.category_set_id;
rec_imp.OLD_CATEGORY_ID:=l_old_category_id;
exception
when no_data_found then
l_transaction_type := 'CREATE';
end;
rec_imp.TRANSACTION_TYPE :=l_transaction_type;
rec_imp.category_id:=rec.PO_CATEGORY_ID;
IF rec.PO_CATEGORY_ID <> nvl(l_old_category_id,-1)
THEN
insert into MTL_ITEM_CATEGORIES_INTERFACE values rec_imp;
commit;
END IF;
END;
END IF;
/* sales category*/
IF (rec.SALES_CATEGORY_ID IS NOT NULL) and not skip_mas
THEN
rec_imp.category_set_id :=null;
rec_imp.TRANSACTION_TYPE :=null;
rec_imp.category_id:=null;
rec_imp.OLD_CATEGORY_ID:=null;
rec_imp.category_set_id := c_sale_category_set_id;
BEGIN
begin
SELECT 'UPDATE',category_id
INTO l_transaction_type,l_old_category_id
FROM mtl_item_categories mic
WHERE inventory_item_id = rec.inventory_item_id
AND ORGANIZATION_ID = vmas_org
AND CATEGORY_SET_ID = rec_imp.category_set_id;
rec_imp.OLD_CATEGORY_ID:=l_old_category_id;
exception
when no_data_found then
l_transaction_type := 'CREATE';
end;
rec_imp.TRANSACTION_TYPE :=l_transaction_type;
rec_imp.category_id:=rec.SALES_CATEGORY_ID;
IF rec.SALES_CATEGORY_ID <> nvl(l_old_category_id,-1)
THEN
insert into MTL_ITEM_CATEGORIES_INTERFACE values rec_imp;
commit;
END IF;
END;
END IF;
/* cost category*/
IF rec.COST_CATEGORY_ID IS NOT NULL and not skip_mas
THEN
rec_imp.category_set_id :=null;
rec_imp.TRANSACTION_TYPE :=null;
rec_imp.category_id:=null;
rec_imp.OLD_CATEGORY_ID:=null;
rec_imp.category_set_id := c_cst_category_set_id;
BEGIN
begin
SELECT 'UPDATE',category_id
INTO l_transaction_type,l_old_category_id
FROM mtl_item_categories mic
WHERE inventory_item_id = rec.inventory_item_id
AND ORGANIZATION_ID = rec_imp.organization_id
AND CATEGORY_SET_ID = rec_imp.category_set_id;
rec_imp.OLD_CATEGORY_ID:=l_old_category_id;
exception
when no_data_found then
l_transaction_type := 'CREATE';
end;
rec_imp.TRANSACTION_TYPE :=l_transaction_type;
rec_imp.category_id:=rec.COST_CATEGORY_ID;
IF rec.COST_CATEGORY_ID <> nvl(l_old_category_id,-1)
THEN
insert into MTL_ITEM_CATEGORIES_INTERFACE values rec_imp;
commit;
END IF;
END;
END IF;
END;
if i >= 2000 then
vSET_PROCESS_ID := vSET_PROCESS_ID+1;
i:=0;
p_requestid :=
fnd_request.submit_request
(application => 'INV',
-- application short name
program => 'INV_ITEM_CAT_ASSIGN_OI',
-- program short name
argument1 => vSET_PROCESS_ID,
argument2 => 1,
argument3 => 1);
COMMIT;
end if;
END LOOP;
p_requestid :=
fnd_request.submit_request
(application => 'INV',
-- application short name
program => 'INV_ITEM_CAT_ASSIGN_OI',
-- program short name
argument1 =>vSET_PROCESS_ID,
argument2 => 1,
argument3 => 1);
commit;
END;
留言列表
Oracle (6)
