DECLARE
/** create io org routing **/
CURSOR cur
IS
SELECT *
FROM R12_IMPORT_ITEM_201106 where ORGANIZATION_ID=164 ;
-- and COST_CATEGORY='FG'
-- and rownum <=10;
--where segment1 in ('C50050F0770331G','C60127F0211951G');
/*這是我整理好要轉進去的item */
-- WHERE ROWNUM <= 5000;
-- update R12_IMPORT_ITEM_201106
-- set WIP_SUPPLY_SUBINVENTORY='G製成品'
-- where OLD_WIP_SUPPLY_SUBINVENTORY='9GBT製成品'
--
vPROCESS_FLAG NUMBER := 1;
vTRANSACTION_TYPE VARCHAR2 (10) := NULL;
vSET_PROCESS_ID NUMBER := 1694;
vmas_org NUMBER := 103;
vCREATED_BY NUMBER := 1157;
i PLS_INTEGER := 0;
cnt number:=40000; /*這是我要產生分批轉入的批次process no 累加*/
finished number:=0;
existed pls_integer:=0;
cursor getrtg(pORGANIZATION_ID in number ,pINVENTORY_ITEM_ID in number )
is select * from BOM_OPERATIONAL_ROUTINGS_V@prod
where ORGANIZATION_ID=pORGANIZATION_ID and ASSEMBLY_ITEM_ID=pINVENTORY_ITEM_ID;
cursor getop(pROUTING_SEQUENCE_ID in number )
is select * from BOM_OPERATION_SEQUENCES_V@prod where ROUTING_SEQUENCE_ID=pROUTING_SEQUENCE_ID
and DISABLE_DATE is null;
cursor getres(pOPERATION_SEQUENCE_ID in number )
is select * from BOM_OPERATION_RESOURCES_V@prod where OPERATION_SEQUENCE_ID=pOPERATION_SEQUENCE_ID;
--BOM_OPERATION_SEQUENCES_V
--BOM_OPERATION_RESOURCES_V
rec_imp_rtg R12_ROUTING_IMPORT%rowtype;
rec_imp_op R12_OPERATION_SEQ_IMPORT%rowtype;
rec_imp_res R12_OPERATION_RES_IMPORT%rowtype;
rec_imp_rtg_init R12_ROUTING_IMPORT%rowtype;
rec_imp_op_init R12_OPERATION_SEQ_IMPORT%rowtype;
rec_imp_res_init R12_OPERATION_RES_IMPORT%rowtype;
PROCEDURE set_r12_item ( vrec_imp_rtg in out R12_ROUTING_IMPORT%rowtype ) is
begin
SELECT INVENTORY_ITEM_ID
INTO vrec_imp_rtg.R12_ASSEMBLY_ITEM_ID
FROM MTL_SYSTEM_ITEMS@e4900prod
WHERE ORGANIZATION_ID = vrec_imp_rtg.R12_ORGANIZATION_ID
AND segment1 = vrec_imp_rtg.segment1;
/*exception
when others then
dbms_output.put_line( vrec_ar_tmp.INV#|| ' , ' || vrec_ar_tmp.SALES_TYPE || ' , ' || vrec_ar_tmp.CLASS || ' , ' || to_char(vrec_ar_tmp.ORG_ID) || ':' || vrec_ar_tmp.ar_type_GL_ID_REV );
*/
exception
when no_data_found then
dbms_output.put_line(rec_imp_rtg.segment1 || ' ^無R12料號 ^' || vrec_imp_rtg.segment1|| ' ^' || to_char(vrec_imp_rtg.R12_ORGANIZATION_ID));
end;
PROCEDURE set_r12_rtg_subinv ( vrec_imp_rtg in out R12_ROUTING_IMPORT%rowtype ) is
begin
if vrec_imp_rtg.COMPLETION_SUBINVENTORY is not null then
SELECT SECONDARY_INVENTORY_NAME,INVENTORY_LOCATION_ID
INTO vrec_imp_rtg.R12_COMPLETION_SUBINVENTORY,
vrec_imp_rtg.R12_COMPLETION_LOCATOR_ID
FROM GB_SUBINVENTORY_MAPPING@e4900prod
WHERE ORGANIZATION_ID = vrec_imp_rtg.R12_ORGANIZATION_ID
AND R11_SECONDARY_INVENTORY_NAME = vrec_imp_rtg.COMPLETION_SUBINVENTORY
AND nvl(R11_INVENTORY_LOCATION_ID,0)=nvl(vrec_imp_rtg.COMPLETION_LOCATOR_ID,0);
end if;
exception
when no_data_found then
finished :=0;
dbms_output.put_line(rec_imp_rtg.segment1 || ' ^無R12倉別-儲位 ^' || vrec_imp_rtg.COMPLETION_SUBINVENTORY || '^' || to_char(vrec_imp_rtg.COMPLETION_LOCATOR_ID));
end;
PROCEDURE set_r11_item_rev ( vrec_imp_rtg in out R12_ROUTING_IMPORT%rowtype ) is
begin
SELECT CURRENT_REVISION,EFFECTIVITY_DATE
INTO vrec_imp_rtg.CURRENT_REVISION,vrec_imp_rtg.EFFECTIVITY_DATE
FROM BOM_RTG_CURRENT_REV_VIEW@prod
WHERE ORGANIZATION_ID = vrec_imp_rtg.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = vrec_imp_rtg.ASSEMBLY_ITEM_ID;
/*exception
when others then
dbms_output.put_line( vrec_ar_tmp.INV#|| ' , ' || vrec_ar_tmp.SALES_TYPE || ' , ' || vrec_ar_tmp.CLASS || ' , ' || to_char(vrec_ar_tmp.ORG_ID) || ':' || vrec_ar_tmp.ar_type_GL_ID_REV );
*/
exception
when no_data_found then
finished :=0;
dbms_output.put_line(rec_imp_rtg.segment1 || ' ^無版本號 ^' || to_char(vrec_imp_rtg.ASSEMBLY_ITEM_ID));
end;
PROCEDURE set_r12_rtg_op ( vrec_imp_op in out R12_OPERATION_SEQ_IMPORT%rowtype ) is
begin
if vrec_imp_op.STANDARD_OPERATION_ID is not null then
--dbms_output.put_line(vrec_imp_op.STANDARD_OPERATION_CODE );
--dbms_output.put_line('r12 OPERATION_SEQ :' || vrec_imp_op.STANDARD_OPERATION_CODE || ' ,' || to_char(rec_imp_rtg.R12_ORGANIZATION_ID));
SELECT R12_OPERATION_ID,R12_OPERATION,R12_DEPARTMENT_ID,R12_DEPARTMENT_CODE
INTO vrec_imp_op.R12_STANDARD_OPERATION_ID,
vrec_imp_op.R12_STANDARD_OPERATION_CODE,
vrec_imp_op.R12_DEPARTMENT_ID,
vrec_imp_op.R12_DEPARTMENT_CODE
FROM GB_OPERATIONS_MAPPING@e4900prod
WHERE ORGANIZATION_ID = rec_imp_rtg.R12_ORGANIZATION_ID
AND R11_OPERATION_ID = vrec_imp_op.STANDARD_OPERATION_ID;
else
SELECT DEPARTMENT_ID,R12_DEPT
INTO vrec_imp_op.R12_DEPARTMENT_ID,
vrec_imp_op.R12_DEPARTMENT_CODE
FROM GB_DEPARTMENT_MAPPING@e4900prod
WHERE ORGANIZATION_ID = rec_imp_rtg.R12_ORGANIZATION_ID
AND R11_DEPARTMENT_ID = vrec_imp_op.DEPARTMENT_ID;
end if;
exception
when others then
finished :=0;
if vrec_imp_op.STANDARD_OPERATION_ID is not null then
dbms_output.put_line(rec_imp_rtg.segment1 || ' ^找不到R12 製程^' || to_char(vrec_imp_op.STANDARD_OPERATION_ID) || ' ^' || to_char(rec_imp_rtg.R12_ORGANIZATION_ID));
else
dbms_output.put_line(rec_imp_rtg.segment1 || ' ^找不到R12 部門^' || to_char(vrec_imp_op.DEPARTMENT_ID) || ' ^' || to_char(rec_imp_rtg.R12_ORGANIZATION_ID));
end if;
end;
PROCEDURE set_r12_rtg_op_res ( vrec_imp_res in out R12_OPERATION_RES_IMPORT%rowtype ) is
begin
--dbms_output.put_line(vrec_imp_op.STANDARD_OPERATION_CODE );
--dbms_output.put_line('r12 OPERATION_SEQ :' || vrec_imp_op.STANDARD_OPERATION_CODE || ' ,' || to_char(rec_imp_rtg.R12_ORGANIZATION_ID));
SELECT R12_RESOURCE_ID,R12_RESOURCE,R12_DEPARTMENT_ID,R12_DEPARTMENT_CODE
INTO vrec_imp_res.R12_RESOURCE_ID,
vrec_imp_res.R12_RESOURCE_CODE,
vrec_imp_res.R12_DEPARTMENT_ID,
vrec_imp_res.R12_DEPARTMENT_CODE
FROM GB_RESOURCE_MAPPING@e4900prod
WHERE ORGANIZATION_ID = rec_imp_rtg.R12_ORGANIZATION_ID
AND R11_RESOURCE_ID = vrec_imp_res.RESOURCE_ID;
exception
when others then
--finished :=0;
dbms_output.put_line(rec_imp_rtg.segment1 || ' ^無法對應 R12 reource,開單須手動調整^'||rec_imp_res.RESOURCE_CODE || '^' || rec_imp_op.STANDARD_OPERATION_CODE || ' ^' || to_char(rec_imp_res.RESOURCE_ID));
end;
PROCEDURE set_rtg_op_res ( vrec_imp_op in out R12_OPERATION_SEQ_IMPORT%rowtype ) is
begin
-- rec_imp_res R12_OPERATION_RES_IMPORT%rowtype;
for rec in getres(vrec_imp_op.OPERATION_SEQUENCE_ID) loop
rec_imp_res := rec_imp_res_init; --initial record
rec_imp_res.OPERATION_SEQUENCE_ID:=rec.OPERATION_SEQUENCE_ID;
rec_imp_res.RESOURCE_SEQ_NUM:=rec.RESOURCE_SEQ_NUM;
rec_imp_res.RESOURCE_CODE:=rec.RESOURCE_CODE;
rec_imp_res.UOM:=rec.UOM;
rec_imp_res.RESOURCE_ID:=rec.RESOURCE_ID;
rec_imp_res.STANDARD_RATE_FLAG:=rec.STANDARD_RATE_FLAG;
rec_imp_res.ASSIGNED_UNITS:=rec.ASSIGNED_UNITS;
rec_imp_res.USAGE_RATE_OR_AMOUNT:=rec.USAGE_RATE_OR_AMOUNT;
rec_imp_res.USAGE_RATE_OR_AMOUNT_INVERSE:=rec.USAGE_RATE_OR_AMOUNT_INVERSE;
rec_imp_res.BASIS_TYPE:=rec.BASIS_TYPE;
rec_imp_res.SCHEDULE_FLAG:=rec.SCHEDULE_FLAG;
rec_imp_res.AVAILABLE_24_HOURS_FLAG:=rec.AVAILABLE_24_HOURS_FLAG;
rec_imp_res.RESOURCE_OFFSET_PERCENT:=rec.RESOURCE_OFFSET_PERCENT;
rec_imp_res.AUTOCHARGE_TYPE:=rec.AUTOCHARGE_TYPE;
rec_imp_res.R11_DEPARTMENT_ID:=rec_imp_op.DEPARTMENT_ID;
rec_imp_res.R11_DEPARTMENT_CODE :=rec_imp_op.DEPARTMENT_CODE;
rec_imp_res.ROUTING_SEQUENCE_ID :=rec_imp_rtg.ROUTING_SEQUENCE_ID;
rec_imp_res.OPERATION_SEQ_NUM :=rec_imp_op.OPERATION_SEQ_NUM;
rec_imp_res.STANDARD_OPERATION_CODE :=rec_imp_op.STANDARD_OPERATION_CODE;
set_r12_rtg_op_res(rec_imp_res);
if rec_imp_res.R12_RESOURCE_ID is not null then
insert into R12_OPERATION_RES_IMPORT values rec_imp_res;
else
dbms_output.put_line(rec_imp_rtg.segment1 || ' ^無法對應 R12 reource,開單須手動調整^'||rec_imp_res.RESOURCE_CODE || '^' || rec_imp_op.STANDARD_OPERATION_CODE || ' ^' || to_char(rec_imp_res.RESOURCE_ID));
end if;
-- commit;
end loop;
end;
PROCEDURE set_rtg_op ( vrec_imp_rtg in out R12_ROUTING_IMPORT%rowtype ) is
begin
-- rec_imp_op R12_OPERATION_SEQ_IMPORT%rowtype;
for rec in getop(vrec_imp_rtg.ROUTING_SEQUENCE_ID) loop
rec_imp_op := rec_imp_op_init; --initial record
rec_imp_op.OPERATION_SEQUENCE_ID:=rec.OPERATION_SEQUENCE_ID;
rec_imp_op.ROUTING_SEQUENCE_ID:=rec.ROUTING_SEQUENCE_ID;
rec_imp_op.OPERATION_SEQ_NUM:=rec.OPERATION_SEQ_NUM;
rec_imp_op.STANDARD_OPERATION_CODE:=rec.STANDARD_OPERATION_CODE;
rec_imp_op.STANDARD_OPERATION_ID:=rec.STANDARD_OPERATION_ID;
rec_imp_op.DEPARTMENT_CODE:=rec.DEPARTMENT_CODE;
rec_imp_op.DEPARTMENT_ID:=rec.DEPARTMENT_ID;
rec_imp_op.LOCATION_ID:=rec.LOCATION_ID;
rec_imp_op.OPERATION_LEAD_TIME_PERCENT:=rec.OPERATION_LEAD_TIME_PERCENT;
rec_imp_op.MINIMUM_TRANSFER_QUANTITY:=rec.MINIMUM_TRANSFER_QUANTITY;
rec_imp_op.COUNT_POINT_TYPE:=rec.COUNT_POINT_TYPE;
rec_imp_op.OPERATION_DESCRIPTION:=rec.OPERATION_DESCRIPTION;
rec_imp_op.EFFECTIVITY_DATE:=rec.EFFECTIVITY_DATE;
rec_imp_op.DISABLE_DATE:=rec.DISABLE_DATE;
rec_imp_op.BACKFLUSH_FLAG:=rec.BACKFLUSH_FLAG;
rec_imp_op.OPTION_DEPENDENT_FLAG:=rec.OPTION_DEPENDENT_FLAG;
rec_imp_op.COUNT_POINT_FLAG:=rec.COUNT_POINT_FLAG;
rec_imp_op.AUTOCHARGE_FLAG:=rec.AUTOCHARGE_FLAG;
rec_imp_op.OPERATION_TYPE:=rec.OPERATION_TYPE;
rec_imp_op.REFERENCE_FLAG:=rec.REFERENCE_FLAG;
--dbms_output.put_line(rec_imp_op.STANDARD_OPERATION_CODE );
set_r12_rtg_op(rec_imp_op);
set_rtg_op_res(rec_imp_op);
insert into R12_OPERATION_SEQ_IMPORT values rec_imp_op;
-- commit;
end loop;
exception
when no_data_found then
dbms_output.put_line('no rtg op :' || to_char(vrec_imp_rtg.ASSEMBLY_ITEM_ID));
end;
BEGIN
FOR rec IN cur
LOOP
for rec_rtg in getrtg(rec.R11_ORGANIZATION_ID,rec.INVENTORY_ITEM_ID) loop
begin
select 1 into existed
from R12_ROUTING_IMPORT
where ORGANIZATION_ID = rec_rtg.ORGANIZATION_ID
and nvl(ALTERNATE_ROUTING_DESIGNATOR,'x')=nvl(rec_rtg.ALTERNATE_ROUTING_DESIGNATOR,'x')
and ASSEMBLY_ITEM_ID=rec_rtg.ASSEMBLY_ITEM_ID
and completed=1 ;
exception
when no_data_found then
existed:=0;
end;
if existed=0 then
rec_imp_rtg :=rec_imp_rtg_init;
/* set r12 io*/
if rec.R11_ORGANIZATION_ID =4 then --GB1
rec_imp_rtg.R12_ORGANIZATION_ID:=104;
elsif rec.R11_ORGANIZATION_ID =76 then --GB2
rec_imp_rtg.R12_ORGANIZATION_ID:=164;
elsif rec.R11_ORGANIZATION_ID =1216 then --GB3
rec_imp_rtg.R12_ORGANIZATION_ID:=184;
elsif rec.R11_ORGANIZATION_ID =118 then --DD1
rec_imp_rtg.R12_ORGANIZATION_ID:=121;
end if;
finished :=1;
rec_imp_rtg.ROUTING_SEQUENCE_ID:=rec_rtg.ROUTING_SEQUENCE_ID;
rec_imp_rtg.ASSEMBLY_ITEM_ID:=rec_rtg.ASSEMBLY_ITEM_ID;
rec_imp_rtg.ORGANIZATION_ID:=rec_rtg.ORGANIZATION_ID;
rec_imp_rtg.ALTERNATE_ROUTING_DESIGNATOR:=rec_rtg.ALTERNATE_ROUTING_DESIGNATOR;
rec_imp_rtg.ROUTING_TYPE:=rec_rtg.ROUTING_TYPE;
rec_imp_rtg.COMMON_ASSEMBLY_ITEM_ID:=rec_rtg.COMMON_ASSEMBLY_ITEM_ID;
rec_imp_rtg.COMMON_ROUTING_SEQUENCE_ID:=rec_rtg.COMMON_ROUTING_SEQUENCE_ID;
rec_imp_rtg.ROUTING_COMMENT:=rec_rtg.ROUTING_COMMENT;
rec_imp_rtg.COMPLETION_SUBINVENTORY:=rec_rtg.COMPLETION_SUBINVENTORY;
rec_imp_rtg.SUB_LOCATOR_CONTROL:=rec_rtg.SUB_LOCATOR_CONTROL;
rec_imp_rtg.COMPLETION_LOCATOR_ID:=rec_rtg.COMPLETION_LOCATOR_ID;
rec_imp_rtg.CFM_ROUTING_FLAG:=rec_rtg.CFM_ROUTING_FLAG;
rec_imp_rtg.MIXED_MODEL_MAP_FLAG:=rec_rtg.MIXED_MODEL_MAP_FLAG;
rec_imp_rtg.PRIORITY:=rec_rtg.PRIORITY;
rec_imp_rtg.CTP_FLAG:=rec_rtg.CTP_FLAG;
rec_imp_rtg.TOTAL_PRODUCT_CYCLE_TIME:=rec_rtg.TOTAL_PRODUCT_CYCLE_TIME;
rec_imp_rtg.segment1 := rec.segment1;
set_r12_item(rec_imp_rtg);
set_r12_rtg_subinv(rec_imp_rtg);
set_r11_item_rev(rec_imp_rtg);
set_rtg_op(rec_imp_rtg);
rec_imp_rtg.completed := finished;
insert into R12_ROUTING_IMPORT values rec_imp_rtg;
if finished=1 then
commit;
else
rollback;
end if;
-- commit;
-- exit;
end if;
end loop;
end loop;
end;
留言列表
Oracle (6)
