DECLARE
/** create io bom **/
CURSOR cur
IS
SELECT *
FROM R12_IMPORT_ITEM_201106 where ORGANIZATION_ID=164 ;--and COST_CATEGORY='FG' and rownum <=100 ;
cursor getbomhead(pORGANIZATION_ID in number ,pINVENTORY_ITEM_ID in number )
is select * from BOM_BILL_OF_MATERIALS_V@prod
where ORGANIZATION_ID=pORGANIZATION_ID and ASSEMBLY_ITEM_ID=pINVENTORY_ITEM_ID;
cursor getbomcomp(pBILL_SEQUENCE_ID in number )
is select * from GB_BOM_INVENTORY_COMPONENTS_V@prod where BILL_SEQUENCE_ID=pBILL_SEQUENCE_ID
and DISABLE_DATE is null;
--BOM_BILL_OF_MATERIALS_V
--BOM_INVENTORY_COMPONENTS_V
rec_imp_bomh R12_BOM_HEADER_IMPORT%rowtype;
rec_imp_comp R12_BOM_COMPONENT_IMPORT%rowtype;
rec_imp_bomh_init R12_BOM_HEADER_IMPORT%rowtype;
rec_imp_comp_init R12_BOM_COMPONENT_IMPORT%rowtype;
finished number:=0;
finished_comp number:=0;
completed number:=0;
existed pls_integer:=0 ;
PROCEDURE set_r12_assembly_item ( vrec_imp_bomh in out R12_BOM_HEADER_IMPORT%rowtype ) is
begin
SELECT INVENTORY_ITEM_ID
INTO vrec_imp_bomh.R12_ASSEMBLY_ITEM_ID
FROM MTL_SYSTEM_ITEMS@e4900prod
WHERE ORGANIZATION_ID = vrec_imp_bomh.R12_ORGANIZATION_ID
AND segment1 = vrec_imp_bomh.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
finished :=0;
completed :=0;
dbms_output.put_line(vrec_imp_bomh.segment1 || ' ^無R12組裝料號 ^' || to_char(vrec_imp_bomh.R12_ORGANIZATION_ID));
end;
PROCEDURE set_r12_comp_item ( vrec_imp_comp in out R12_BOM_COMPONENT_IMPORT%rowtype ) is
begin
finished_comp:=0;
SELECT INVENTORY_ITEM_ID
INTO vrec_imp_comp.R12_COMPONENT_ITEM_ID
FROM MTL_SYSTEM_ITEMS@e4900prod
WHERE ORGANIZATION_ID = rec_imp_bomh.R12_ORGANIZATION_ID
AND segment1 = vrec_imp_comp.COMPONENT_SEGMENT1;
finished_comp :=1;
/*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
if vrec_imp_comp.COMPONENT_QUANTITY<>0 then
finished_comp:=0;
finished :=0;
dbms_output.put_line(vrec_imp_comp.COMPONENT_SEGMENT1 || '^' || to_char(vrec_imp_comp.COMPONENT_ITEM_ID) || ' ^無R12元件料號^' || to_char(rec_imp_bomh.R12_ORGANIZATION_ID)|| '^'||rec_imp_bomh.segment1 );
completed :=0;
else
finished_comp:=0;
finished :=1;
dbms_output.put_line(vrec_imp_comp.COMPONENT_SEGMENT1 || '^' || to_char(vrec_imp_comp.COMPONENT_ITEM_ID) || ' ^無R12元件料號但數量=0,不進行轉入^' || to_char(rec_imp_bomh.R12_ORGANIZATION_ID)|| '^'||rec_imp_bomh.segment1 );
end if;
end;
PROCEDURE set_r12_comp_subinv ( vrec_imp_comp in out R12_BOM_COMPONENT_IMPORT%rowtype ) is
begin
if vrec_imp_comp.SUPPLY_SUBINVENTORY is not null then
SELECT SECONDARY_INVENTORY_NAME,INVENTORY_LOCATION_ID
INTO vrec_imp_comp.R12_SUPPLY_SUBINVENTORY,
vrec_imp_comp.R12_SUPPLY_LOCATOR_ID
FROM GB_SUBINVENTORY_MAPPING@e4900prod
WHERE ORGANIZATION_ID = rec_imp_bomh.R12_ORGANIZATION_ID
AND R11_SECONDARY_INVENTORY_NAME = vrec_imp_comp.SUPPLY_SUBINVENTORY
AND nvl(R11_INVENTORY_LOCATION_ID,0)=nvl(vrec_imp_comp.SUPPLY_LOCATOR_ID,0);
end if;
exception
when no_data_found then
finished :=0;
completed :=0;
dbms_output.put_line(vrec_imp_comp.COMPONENT_SEGMENT1 || ' ^無supply R12倉別-儲位 ^' || vrec_imp_comp.SUPPLY_SUBINVENTORY || '^' || to_char(vrec_imp_comp.SUPPLY_LOCATOR_ID));
end;
PROCEDURE set_r11_item_rev ( vrec_imp_bomh in out R12_BOM_HEADER_IMPORT%rowtype ) is
begin
SELECT CURRENT_REVISION,EFFECTIVITY_DATE
INTO vrec_imp_bomh.CURRENT_REVISION,vrec_imp_bomh.EFFECTIVITY_DATE
FROM BOM_ITEM_CURRENT_REV_VIEW@prod
WHERE ORGANIZATION_ID = vrec_imp_bomh.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = vrec_imp_bomh.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;
completed :=0;
dbms_output.put_line(vrec_imp_bomh.segment1 || ' ^無版本號 ^' || to_char(vrec_imp_bomh.ASSEMBLY_ITEM_ID));
end;
PROCEDURE set_r11_comp_op_seq ( vrec_imp_comp in out R12_BOM_COMPONENT_IMPORT%rowtype ) is
begin
SELECT OPERATION_SEQUENCE_ID,OPERATION_DESCRIPTION
INTO vrec_imp_comp.OPERATION_SEQ_ID,
vrec_imp_comp.OPERATION_SEQ_DESC
FROM R12_ROUTING_IMPORT a,R12_OPERATION_SEQ_IMPORT b
WHERE a.R12_ORGANIZATION_ID = rec_imp_bomh.R12_ORGANIZATION_ID
and a.ASSEMBLY_ITEM_ID=rec_imp_bomh.ASSEMBLY_ITEM_ID
AND a.ROUTING_SEQUENCE_ID = b.ROUTING_SEQUENCE_ID
and b.OPERATION_SEQ_NUM=vrec_imp_comp.OPERATION_SEQ_NUM;
exception
when no_data_found then
begin
SELECT min(OPERATION_SEQUENCE_ID),min(OPERATION_SEQ_NUM)
INTO vrec_imp_comp.OPERATION_SEQ_ID, vrec_imp_comp.OPERATION_SEQ_NUM
FROM R12_ROUTING_IMPORT a,R12_OPERATION_SEQ_IMPORT b
WHERE a.R12_ORGANIZATION_ID = rec_imp_bomh.R12_ORGANIZATION_ID
and a.ASSEMBLY_ITEM_ID=rec_imp_bomh.ASSEMBLY_ITEM_ID
AND a.ROUTING_SEQUENCE_ID = b.ROUTING_SEQUENCE_ID;
if (vrec_imp_comp.OPERATION_SEQ_ID is null) or (vrec_imp_comp.OPERATION_SEQ_NUM is null ) then
--finished :=0;
finished_comp:=0;
dbms_output.put_line(vrec_imp_comp.COMPONENT_SEGMENT1 || ' ^找不到原件對應製程序號^' || vrec_imp_comp.OPERATION_SEQ_NUM || ' ^' || rec_imp_bomh.segment1|| ' ^' || to_char( rec_imp_bomh.R12_ORGANIZATION_ID) );
end if;
end;
end;
PROCEDURE set_bom_comp ( vrec_imp_bomh in out R12_BOM_HEADER_IMPORT%rowtype ) is
begin
-- rec_imp_comp R12_BOM_COMPONENT_IMPORT%rowtype;
for rec in getbomcomp(vrec_imp_bomh.BILL_SEQUENCE_ID) loop
rec_imp_comp := rec_imp_comp_init; --initial record
rec_imp_comp.OPERATION_SEQ_NUM:=rec.OPERATION_SEQ_NUM;
rec_imp_comp.COMPONENT_ITEM_ID:=rec.COMPONENT_ITEM_ID;
rec_imp_comp.ITEM_NUM:=rec.ITEM_NUM;
rec_imp_comp.COMPONENT_QUANTITY:=rec.COMPONENT_QUANTITY;
rec_imp_comp.COMPONENT_YIELD_FACTOR:=rec.COMPONENT_YIELD_FACTOR;
rec_imp_comp.COMPONENT_REMARKS:=rec.COMPONENT_REMARKS;
rec_imp_comp.EFFECTIVITY_DATE:=rec.EFFECTIVITY_DATE;
rec_imp_comp.CHANGE_NOTICE:=rec.CHANGE_NOTICE;
rec_imp_comp.IMPLEMENTATION_DATE:=rec.IMPLEMENTATION_DATE;
rec_imp_comp.IMPL_CB:=rec.IMPL_CB;
rec_imp_comp.DISABLE_DATE:=rec.DISABLE_DATE;
rec_imp_comp.PLANNING_FACTOR:=rec.PLANNING_FACTOR;
rec_imp_comp.QUANTITY_RELATED:=rec.QUANTITY_RELATED;
rec_imp_comp.SO_BASIS:=rec.SO_BASIS;
rec_imp_comp.OPTIONAL:=rec.OPTIONAL;
rec_imp_comp.MUTUALLY_EXCLUSIVE_OPTIONS:=rec.MUTUALLY_EXCLUSIVE_OPTIONS;
rec_imp_comp.INCLUDE_IN_COST_ROLLUP:=rec.INCLUDE_IN_COST_ROLLUP;
rec_imp_comp.CHECK_ATP:=rec.CHECK_ATP;
rec_imp_comp.SHIPPING_ALLOWED:=rec.SHIPPING_ALLOWED;
rec_imp_comp.REQUIRED_TO_SHIP:=rec.REQUIRED_TO_SHIP;
rec_imp_comp.REQUIRED_FOR_REVENUE:=rec.REQUIRED_FOR_REVENUE;
rec_imp_comp.INCLUDE_ON_SHIP_DOCS:=rec.INCLUDE_ON_SHIP_DOCS;
rec_imp_comp.INCLUDE_ON_BILL_DOCS:=rec.INCLUDE_ON_BILL_DOCS;
rec_imp_comp.LOW_QUANTITY:=rec.LOW_QUANTITY;
rec_imp_comp.HIGH_QUANTITY:=rec.HIGH_QUANTITY;
rec_imp_comp.ACD_TYPE:=rec.ACD_TYPE;
rec_imp_comp.OLD_COMPONENT_SEQUENCE_ID:=rec.OLD_COMPONENT_SEQUENCE_ID;
rec_imp_comp.COMPONENT_SEQUENCE_ID:=rec.COMPONENT_SEQUENCE_ID;
rec_imp_comp.BILL_SEQUENCE_ID:=rec.BILL_SEQUENCE_ID;
rec_imp_comp.WIP_SUPPLY_TYPE:=rec.WIP_SUPPLY_TYPE;
rec_imp_comp.SUPPLY_TYPE:=rec.SUPPLY_TYPE;
rec_imp_comp.PICK_COMPONENTS:=rec.PICK_COMPONENTS;
rec_imp_comp.SUPPLY_SUBINVENTORY:=rec.SUPPLY_SUBINVENTORY;
rec_imp_comp.SUPPLY_LOCATOR_ID:=rec.SUPPLY_LOCATOR_ID;
rec_imp_comp.OPERATION_LEAD_TIME_PERCENT:=rec.OPERATION_LEAD_TIME_PERCENT;
rec_imp_comp.REVISED_ITEM_SEQUENCE_ID:=rec.REVISED_ITEM_SEQUENCE_ID;
rec_imp_comp.COST_FACTOR:=rec.COST_FACTOR;
rec_imp_comp.BOM_ITEM_TYPE:=rec.BOM_ITEM_TYPE;
rec_imp_comp.PRIMARY_UOM_CODE:=rec.PRIMARY_UOM_CODE;
rec_imp_comp.DESCRIPTION:=rec.DESCRIPTION;
rec_imp_comp.ENG_ITEM_FLAG:=rec.ENG_ITEM_FLAG;
rec_imp_comp.INVENTORY_ITEM_STATUS_CODE:=rec.INVENTORY_ITEM_STATUS_CODE;
rec_imp_comp.SHIPPABLE_ITEM_FLAG:=rec.SHIPPABLE_ITEM_FLAG;
rec_imp_comp.ITEM_LOCATOR_CONTROL:=rec.ITEM_LOCATOR_CONTROL;
rec_imp_comp.RESTRICT_LOCATORS_FLAG:=rec.RESTRICT_LOCATORS_FLAG;
rec_imp_comp.RESTRICT_SUBINVENTORIES_FLAG:=rec.RESTRICT_SUBINVENTORIES_FLAG;
rec_imp_comp.ITEM_TYPE:=rec.ITEM_TYPE;
rec_imp_comp.REPLENISH_TO_ORDER_FLAG:=rec.REPLENISH_TO_ORDER_FLAG;
rec_imp_comp.ATP_COMPONENTS_FLAG:=rec.ATP_COMPONENTS_FLAG;
rec_imp_comp.PICK_COMPONENTS_FLAG:=rec.PICK_COMPONENTS_FLAG;
rec_imp_comp.INVENTORY_ASSET_FLAG:=rec.INVENTORY_ASSET_FLAG;
rec_imp_comp.ATP_FLAG:=rec.ATP_FLAG;
rec_imp_comp.FORECAST_CONTROL:=rec.FORECAST_CONTROL;
rec_imp_comp.COMPONENT_SEGMENT1:=rec.SEGMENT1;
set_r12_comp_item(rec_imp_comp);
set_r12_comp_subinv(rec_imp_comp);
set_r11_comp_op_seq(rec_imp_comp);
if finished_comp =1 then
insert into R12_BOM_COMPONENT_IMPORT values rec_imp_comp;
end if;
-- commit;
end loop;
end;
BEGIN
--execute immediate 'truncate table R12_BOM_HEADER_IMPORT';
--execute immediate 'truncate table R12_BOM_COMPONENT_IMPORT';
FOR rec IN cur
LOOP
for rec_bom in getbomhead(rec.R11_ORGANIZATION_ID ,rec.INVENTORY_ITEM_ID ) loop
begin
select 1 into existed
from R12_BOM_HEADER_IMPORT
where ORGANIZATION_ID = rec_bom.ORGANIZATION_ID
and nvl(ALTERNATE_BOM_DESIGNATOR,'x')=nvl(rec_bom.ALTERNATE_BOM_DESIGNATOR,'x')
and ASSEMBLY_ITEM_ID=rec_bom.ASSEMBLY_ITEM_ID
and completed=1 ;
exception
when no_data_found then
existed:=0;
end ;
if existed=0 then
rec_imp_bomh:=rec_imp_bomh_init;
finished:=1;
completed :=1;
/* set r12 io*/
if rec.R11_ORGANIZATION_ID =4 then --GB1
rec_imp_bomh.R12_ORGANIZATION_ID:=104;
elsif rec.R11_ORGANIZATION_ID =76 then --GB2
rec_imp_bomh.R12_ORGANIZATION_ID:=164;
elsif rec.R11_ORGANIZATION_ID =1216 then --GB3
rec_imp_bomh.R12_ORGANIZATION_ID:=184;
elsif rec.R11_ORGANIZATION_ID =118 then --DD1
rec_imp_bomh.R12_ORGANIZATION_ID:=121;
end if;
rec_imp_bomh.ASSEMBLY_ITEM_ID:=rec_bom.ASSEMBLY_ITEM_ID;
rec_imp_bomh.UOM:=rec_bom.UOM;
rec_imp_bomh.PICK_COMPONENTS_FLAG:=rec_bom.PICK_COMPONENTS_FLAG;
rec_imp_bomh.REPLENISH_TO_ORDER_FLAG:=rec_bom.REPLENISH_TO_ORDER_FLAG;
rec_imp_bomh.BOM_ITEM_TYPE:=rec_bom.BOM_ITEM_TYPE;
rec_imp_bomh.BASE_ITEM_ID:=rec_bom.BASE_ITEM_ID;
rec_imp_bomh.DESCRIPTION:=rec_bom.DESCRIPTION;
rec_imp_bomh.ITEM_CATALOG_GROUP_ID:=rec_bom.ITEM_CATALOG_GROUP_ID;
rec_imp_bomh.ATP_COMPONENTS_FLAG:=rec_bom.ATP_COMPONENTS_FLAG;
rec_imp_bomh.WIP_SUPPLY_TYPE:=rec_bom.WIP_SUPPLY_TYPE;
rec_imp_bomh.ENG_ITEM_FLAG:=rec_bom.ENG_ITEM_FLAG;
rec_imp_bomh.ORGANIZATION_ID:=rec_bom.ORGANIZATION_ID;
rec_imp_bomh.ALTERNATE_BOM_DESIGNATOR:=rec_bom.ALTERNATE_BOM_DESIGNATOR;
rec_imp_bomh.COMMON_ASSEMBLY_ITEM_ID:=rec_bom.COMMON_ASSEMBLY_ITEM_ID;
rec_imp_bomh.SPECIFIC_ASSEMBLY_COMMENT:=rec_bom.SPECIFIC_ASSEMBLY_COMMENT;
rec_imp_bomh.PENDING_FROM_ECN:=rec_bom.PENDING_FROM_ECN;
rec_imp_bomh.ASSEMBLY_TYPE:=rec_bom.ASSEMBLY_TYPE;
rec_imp_bomh.COMMON_BILL_SEQUENCE_ID:=rec_bom.COMMON_BILL_SEQUENCE_ID;
rec_imp_bomh.BILL_SEQUENCE_ID:=rec_bom.BILL_SEQUENCE_ID;
rec_imp_bomh.COMMON_ORGANIZATION_ID:=rec_bom.COMMON_ORGANIZATION_ID;
rec_imp_bomh.NEXT_EXPLODE_DATE:=rec_bom.NEXT_EXPLODE_DATE;
rec_imp_bomh.ITEM_TYPE:=rec_bom.ITEM_TYPE;
rec_imp_bomh.BOM_ENABLED_FLAG:=rec_bom.BOM_ENABLED_FLAG;
-- dbms_output.put_line(rec.SEGMENT1);
rec_imp_bomh.SEGMENT1:=rec.SEGMENT1;
set_r12_assembly_item(rec_imp_bomh);
set_r11_item_rev(rec_imp_bomh);
set_bom_comp(rec_imp_bomh);
rec_imp_bomh.completed:=completed;
insert into R12_BOM_HEADER_IMPORT values rec_imp_bomh;
if finished=1 then
commit;
else
rollback;
end if;
end if;
end loop;
END LOOP;
END;
留言列表
Oracle (6)
