|
在所有的導入中﹐item的導入是比較麻煩的﹐因為在導入后﹐還要手動更新它的catalog ,catalog
的更新比較復雜,下面是item check的語句
rem PL/SQL Developer Test Script
set feedback off
set autoprint off
rem Execute PL/SQL Block
/*******************************************\
Created by :Jerry
Created at :2005-07-26
\*******************************************/
declare
cursor cu_item is
------------- 潰脤笭葩腔訧蹋 -----------------------
select 'Item' err_type
, item_no err_item
, count(*) err_number
, 'Exists move than 1 record(s)' err_msg
from aic_import_item_temp
group by item_no
having count(*)>1
;
----------- 潰脤炵苀笢羶衄膘蕾腔UOM--------------------------
cursor cu_uom is
select 'UOM' err_type
, primary_uom_code err_item
, count(*) err_number
, 'This UOM is not created!' err_msg
from aic_import_item_temp aic_table
where not exists
( select 1
from Mtl_Units_Of_Measure_Tl muom
where muom.uom_code =aic_table.primary_uom_code
)
group by primary_uom_code
;
--------------- 潰脤炵苀笢羶衄膘蕾腔catalog_group ------
cursor cu_catalog_group is
select 'Catalog Group' err_type
, item_catalog_group_name err_item
, count(*) err_number
, 'This Catalog Group is not created !' err_msg
from aic_import_item_temp aic_table
where item_catalog_group_id is null
and item_catalog_group_name is not null
group by item_catalog_group_name
;
--------------- 潰脤炵苀笢羶衄膘蕾腔template name ------
cursor cu_item_template is
select
'Item Template' Err_type
, aic_table.template_name err_item
, count(*) err_number
, 'This item template is not created!' err_msg
from aic_import_item_temp aic_table
where template_id is null
and template_name is not null
group by aic_table.template_name
;
/*-----category_group Check*/
cursor cu_category_group is
select 'Catogory Group' err_type
, err_item err_item
, count(*) err_number
, 'Not exists this catogory group !' err_msg
from (
select category1_set_name err_item
from aic_import_item_temp aic_table
where CATEGORY1_SET_ID is null
and CATEGORY1_SET_name is not null
union all
select category2_set_name err_item
from aic_import_item_temp aic_table
where CATEGORY2_SET_ID is null
and CATEGORY2_SET_name is not null
union all
select category3_set_name err_item
from aic_import_item_temp aic_table
where CATEGORY3_SET_ID is null
and CATEGORY3_SET_name is not null
union all
select category4_set_name err_item
from aic_import_item_temp aic_table
where CATEGORY4_SET_ID is null
and CATEGORY4_SET_name is not null
union all
select category5_set_name err_item
from aic_import_item_temp aic_table
where CATEGORY5_SET_ID is null
and CATEGORY5_SET_name is not null
) catogory_list
group by err_item
;
--------------- 潰脤炵苀笢羶衄膘蕾腔 categroy segment --------------------
cursor cu_category_seg is
select 'Category segment' err_type
, err_item
, count(*) err_number
, 'Error category segment values !' err_msg
from ( select category1_set_name||': '||category1_Seg1||'.'||category1_Seg2||'.'||category1_Seg3 err_item
from aic_import_item_temp aic_table
where category1_id is null
and category1_set_name is not null
union
select
category2_set_name||': '||category2_Seg1||'.'||category2_Seg2 err_item
from aic_import_item_temp aic_table
where category2_id is null
and category2_set_name is not null
union
select category3_set_name||': '||category3_Seg1||'.'||category3_Seg2 err_item
from aic_import_item_temp aic_table
where category3_id is null
and category3_set_name is not null
union
select category4_set_name||': '||category4_Seg1||'.'||category4_Seg2 err_item
from aic_import_item_temp aic_table
where category4_id is null
and category4_set_name is not null
union
select category5_set_name||': '||category5_Seg1||'.'||category5_Seg2 err_item
from aic_import_item_temp aic_table
where category5_id is null
and category5_set_name is not null
) category_seg
group by err_item
;
------------- Check buyer code -------
cursor cu_buyer is
select 'Buyer' err_type
, buyer_code err_item
, count(*) err_number
, 'Error buyer' err_msg
from aic_import_item_temp aic_table
where aic_table.buyer_id is null
and aic_table.buyer_code is not null
group by buyer_code
;
------------- Check planner code -------
cursor cu_planner is
select 'Planner' err_type
, planner_code err_item
, count(*) err_number
, 'Error planner' err_msg
from aic_import_item_temp aic_table
where aic_table.planner_id is null
and aic_table.planner_code is not null
group by planner_code
;
-------------- check sales_account -----------
cursor cu_Sale_acc is
select 'Sale_acc' err_type
, sale_acc err_item
, count(*) err_number
, 'Error Sale_acc' err_msg
from aic_import_item_temp aic_table
where aic_table.Sale_acc_id is null
and aic_table.sale_acc is not null
group by sale_acc
;
------------------- show error message
cursor cu_err is
select distinct rpad(err_type,20,' ')||rpad(err_item,60,' ')||err_msg err_msg
from aic_import_item_error;
begin
---------- replace cagalog_group_name -----------
update aic_import_item_temp
set item_catalog_group_name = replace(item_catalog_group_name,' ','') ;
----------------- update Primary UOM ----
update aic_import_item_temp aic_table
set primary_UOM_code =UPPER(replace(aic_table.primary_uom_code,' ',''));
--------------- update CATALOG_GROUP_ID -----------
UPDATE aic_import_ITEM_TEMP TEMP_TABLE
SET Item_Catalog_group_id =
(
select mic.item_catalog_group_id
from MTL_ITEM_CATALOG_GROUPS mic
where mic.segment1=TEMP_TABLE.Item_Catalog_group_NAME
and nvl(mic.inactive_date,sysdate+1)>sysdate
);
--------------- update template ------------
update aic_import_item_temp aic_table
set template_id =(
select template_id
from mtl_item_templates
where upper(template_name) = upper(aic_table.template_name)
)
where aic_table.template_name is not null;
--------------- update CATEGORY_SET_ID and STRUCTURE ID ---------------------
UPDATE aic_import_ITEM_TEMP TEMP_table
SET (CATEGORY1_SET_ID,STRUCTURE1_ID) =
(
SELECT distinct MCST.CATEGORY_SET_ID,MCSB.STRUCTURE_ID
FROM MTL_CATEGORY_SETS_TL MCST,
MTL_CATEGORY_SETS_B MCSB
WHERE MCST.CATEGORY_SET_NAME = temp_table.category1_set_name
AND MCST.CATEGORY_SET_ID = MCSB.CATEGORY_SET_ID
)
;
UPDATE aic_import_ITEM_TEMP TEMP_table
SET (CATEGORY2_SET_ID,STRUCTURE2_ID) =
(
SELECT distinct MCST.CATEGORY_SET_ID,MCSB.STRUCTURE_ID
FROM MTL_CATEGORY_SETS_TL MCST,
MTL_CATEGORY_SETS_B MCSB
WHERE MCST.CATEGORY_SET_NAME = temp_table.category2_set_name
AND MCST.CATEGORY_SET_ID = MCSB.CATEGORY_SET_ID
)
;
UPDATE aic_import_ITEM_TEMP TEMP_table
SET (CATEGORY3_SET_ID,STRUCTURE3_ID) =
(
SELECT distinct MCST.CATEGORY_SET_ID,MCSB.STRUCTURE_ID
FROM MTL_CATEGORY_SETS_TL MCST,
MTL_CATEGORY_SETS_B MCSB
WHERE MCST.CATEGORY_SET_NAME = temp_table.category3_set_name
AND MCST.CATEGORY_SET_ID = MCSB.CATEGORY_SET_ID
)
;
UPDATE aic_import_ITEM_TEMP TEMP_table
SET (CATEGORY4_SET_ID,STRUCTURE4_ID) =
(
SELECT distinct MCST.CATEGORY_SET_ID,MCSB.STRUCTURE_ID
FROM MTL_CATEGORY_SETS_TL MCST,
MTL_CATEGORY_SETS_B MCSB
WHERE MCST.CATEGORY_SET_NAME = temp_table.category4_set_name
AND MCST.CATEGORY_SET_ID = MCSB.CATEGORY_SET_ID
)
;
UPDATE aic_import_ITEM_TEMP TEMP_table
SET (CATEGORY5_SET_ID,STRUCTURE5_ID) =
(
SELECT distinct MCST.CATEGORY_SET_ID,MCSB.STRUCTURE_ID
FROM MTL_CATEGORY_SETS_TL MCST,
MTL_CATEGORY_SETS_B MCSB
WHERE MCST.CATEGORY_SET_NAME = temp_table.category5_set_name
AND MCST.CATEGORY_SET_ID = MCSB.CATEGORY_SET_ID
)
;
------------------- update category id -----
update aic_import_item_temp aic_table
set category1_id =
( select category_id
from mtl_categories_b mc
where aic_table.category1_seg1||'.'||aic_table.category1_seg2||'.'||aic_table.category1_seg3=mc.segment1||'.'||mc.segment2||'.'||mc.segment3
and aic_table.structure1_id =mc.structure_id
)
;
update aic_import_item_temp aic_table
set category2_id =
( select category_id
from mtl_categories_b mc
where aic_table.category2_seg1||'.'||aic_table.category2_seg2||aic_table.category2_seg3=mc.segment1||'.'||mc.segment2||mc.segment3
and aic_table.structure2_id =mc.structure_id
)
;
update aic_import_item_temp aic_table
set category3_id =
( select category_id
from mtl_categories_b mc
where aic_table.category3_seg1||'.'||aic_table.category3_seg2=mc.segment1||'.'||mc.segment2
and aic_table.structure3_id =mc.structure_id
)
;
update aic_import_item_temp aic_table
set category4_id =
( select category_id
from mtl_categories_b mc
where aic_table.category4_seg1||'.'||aic_table.category4_seg2=mc.segment1||'.'||mc.segment2
and aic_table.structure4_id =mc.structure_id
)
;
update aic_import_item_temp aic_table
set category5_id =
( select category_id
from mtl_categories_b mc
where aic_table.category5_seg1||'.'||aic_table.category5_seg2=mc.segment1||'.'||mc.segment2
and aic_table.structure5_id =mc.structure_id
);
--------- update buyer ------------
update aic_import_item_temp aic_table
set buyer_id =
(
select agent_id
from PO_AGENTS_v pa
where pa.agent_name = buyer_code
);
--------- update planner ------------
update aic_import_item_temp aic_table
set planner_id =
( select 1
from mtl_planners mp
, aic_import_organization a
where mp.planner_code= aic_table.planner_code
and mp.organization_id=a.organization_id
and a.organization_code='TJ1'
);
update aic_import_item_temp aic_table
set Sale_acc_id =
(
select gcc.code_combination_id
from gl_code_combinations gcc
where gcc.segment1 ||'.'||segment2 ||'.'||segment3 ||'.'||segment4||'.'||segment5 ||'.'||segment6 =aic_table.sale_acc
);
commit;
----------- check data ---------------
delete from aic_import_item_error;
for lr in cu_item loop
insert into aic_import_item_error (err_type, err_item,err_number, err_msg)
values (lr.err_type,lr.err_item,lr.err_number,lr.err_msg);
end loop;
for lr in cu_uom loop
insert into aic_import_item_error (err_type, err_item,err_number, err_msg)
values (lr.err_type,lr.err_item,lr.err_number,lr.err_msg);
end loop;
for lr in cu_catalog_group loop
insert into aic_import_item_error (err_type, err_item,err_number, err_msg)
values (lr.err_type,lr.err_item,lr.err_number,lr.err_msg);
end loop;
for lr in cu_item_template loop
insert into aic_import_item_error (err_type, err_item,err_number, err_msg)
values (lr.err_type,lr.err_item,lr.err_number,lr.err_msg);
end loop;
for lr in cu_category_group loop
insert into aic_import_item_error (err_type, err_item,err_number, err_msg)
values (lr.err_type,lr.err_item,lr.err_number,lr.err_msg);
end loop;
for lr in cu_category_seg loop
insert into aic_import_item_error (err_type, err_item,err_number, err_msg)
values (lr.err_type,lr.err_item,lr.err_number,lr.err_msg);
end loop;
for lr in cu_buyer loop
insert into aic_import_item_error (err_type, err_item,err_number, err_msg)
values (lr.err_type,lr.err_item,lr.err_number,lr.err_msg);
end loop;
for lr in cu_planner loop
insert into aic_import_item_error (err_type, err_item,err_number, err_msg)
values (lr.err_type,lr.err_item,lr.err_number,lr.err_msg);
end loop;
for lr in cu_planner loop
insert into aic_import_item_error (err_type, err_item,err_number, err_msg)
values (lr.err_type,lr.err_item,lr.err_number,lr.err_msg);
end loop;
commit;
---------------------- show the error message ---------------------------------
dbms_output.put_line('Now list the error message.......');
dbms_output.put_line(rpad('err_type',20,' ')||rpad('err_item',60,' ')||'err_msg');
dbms_output.put_line(rpad('=',20,'=')||rpad('=',60,'=')||rpad('=',50,'='));
for lr in cu_err loop
dbms_output.put_line( lr.err_msg);
end loop;
dbms_output.put_line('Error message is list OK.');
end;
/
|
