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;   


arrow
arrow
    全站熱搜

    Nolem 發表在 痞客邦 留言(0) 人氣()