BOM模組的介面表使用總結

sudn發表於2012-01-14

利用BOM介面完成BOM表、Routing的新增還是比較順利的,對於刪除物料、修改物料、刪除資源、新增資源、修改資源這樣的操作,按常規思維利用介面去做還是比較糾結的,每次都要花時間從頭測過。現將相關資料整理總結一下(不一定非得用遊標,主要為增強可讀性):

1. 通過介面批量插入BOM物料

declare     
  x_operation_seq_num number;       
  cursor xxx_ass  is
      select msi.inventory_item_id,
             msi.segment1,
             bbom.BILL_SEQUENCE_ID BILL_SEQUENCE_ID1,
             bc.component_quantity,
             msi1.segment1 com_segment1
       from  bom_bill_of_materials        bbom,
             bom_bill_of_materials        bbom1, --tJ2
             bom_components_b             bc,
             bom.bom_operational_routings bor,
             bom.bom_operation_sequences  bos,
             bom.bom_standard_operations  bso,
             mtl_system_items_b           msi, --成品料號
             mtl_system_items_b           msi1 --採購料號            
       where bbom.common_bill_sequence_id=bc.bill_sequence_id
       and   bbom1.ASSEMBLY_ITEM_ID=bbom.ASSEMBLY_ITEM_ID
       and   bbom1.ORGANIZATION_ID=117 
       and   to_char(bc.disable_date,'yyyy/mm/dd')='2012/01/11'
       and   bc.implementation_date is not null
       and   bbom.ORGANIZATION_ID=116--&ORGANIZATION_ID --組織
       and   bbom.ASSEMBLY_ITEM_ID=bor.assembly_item_id
       and   bbom.ORGANIZATION_ID=bor.organization_id
       and   bor.routing_sequence_id=bos.routing_sequence_id
       and   bos.operation_seq_num=bc.operation_seq_num
       and   bbom.ORGANIZATION_ID=bso.organization_id
       and   bos.standard_operation_id=bso.standard_operation_id
       and   bos.department_id=bso.department_id
       and   bos.disable_date is null
       and   bso.operation_code='A700'              --表示 鑽孔 
       and   bc.component_item_id=msi1.inventory_item_id
       and   msi1.organization_id=115
       and   bbom.ASSEMBLY_ITEM_ID=msi.inventory_item_id
       and   bbom.ORGANIZATION_ID=msi.organization_id
       and   msi.inventory_item_status_code='Active'
      -- and   msi.segment1='A681AC6037B11'        
       ;   
      
  begin 
 
    for p_ass in xxx_ass loop                 
            
             if substr(p_ass.segment1,7,1)<='2' then 
                x_operation_seq_num  := 20;
             else
                x_operation_seq_num  := 70;
             end if;
         
             INSERT into bom_bill_of_mtls_interface
                        (assembly_item_id,
                         organization_id,
                         bill_sequence_id,
                         process_flag,
                         organization_code,
                         item_number,
                         assembly_type,
                         last_updated_by,
                         created_by,
                         transaction_type
                         )
                   VALUES
                         (
                         p_ass.inventory_item_id,
                         117,
                         p_ass.BILL_SEQUENCE_ID1, --bill_sequence_iD
                         1,
                         'TJ2',
                         p_ass.segment1,
                         1,                      
                         -1,                      --更新時用系統使用者
                         -1,
                         'UPDATE'
                         );  
                        
             INSERT into bom_inventory_comps_interface
                        (component_item_number,
                         last_updated_by,
                         created_by,
                         operation_seq_num,
                         effectivity_date,
                         supply_subinventory,
                         location_name,
                         transaction_type,
                         process_flag,
                         component_quantity,
                         assembly_item_number,
                         organization_code,
                         wip_supply_type
                        )
                 VALUES(
                        p_ass.com_segment1,
                        -1,
                        -1,
                        x_operation_seq_num,
                        sysdate,
                        '11911',
                        'A502.000.000',--正式環境是D702.000.000
                        'CREATE',
                        1,
                        p_ass.component_quantity,
                        p_ass.segment1,
                        'TJ2',
                        3
                        );
     commit;
    end loop;
    
  exception when others then
      rollback;
        raise_application_error (-20001,
                                  'Disable item is failure!'
                                 );
  end;

2.通過介面,批量插入工序


DECLARE

 l_iface_rec        bom.bom_op_routings_interface%ROWTYPE;
 l_iface_seq_rec    bom.bom_op_sequences_interface%ROWTYPE;
 l_iface_res_rec    bom.bom_op_resources_interface%ROWTYPE;
 l_cur_mfg_org_id   NUMBER := 117; --Current Inv Organization
 l_user_id          NUMBER := 1584; --User ID, Sysadmin here,TJ_MIS08
 l_transaction_type varchar2(20);
 
 cursor  xxx_r is
  select distinct
         bor.organization_id,
         bor.assembly_item_id,
         msi.segment1,
         bor.routing_sequence_id,
         msi.unit_volume
    from BOM_OPERATIONAL_ROUTINGS bor,
         BOM_OPERATION_SEQUENCES  bos,
         mtl_system_items_b       msi
   where bor.routing_sequence_id=bos.routing_sequence_id
     and bor.organization_id=117
     and bos.standard_operation_id in(9,4)
     and length(msi.segment1)=13
     and bor.assembly_item_id=msi.inventory_item_id
     and msi.organization_id=115
     --and msi.segment1='A681AC6037B11'
     and not exists
         ( select 1
             from BOM_OPERATION_SEQUENCES  bos1
            where bos.routing_sequence_id=bos1.routing_sequence_id
              and bos1.standard_operation_id=10
          )
     ;   
 
BEGIN

 for i in xxx_r loop
 
  l_iface_rec.last_update_date  := SYSDATE;
  l_iface_rec.last_updated_by   := l_user_id;
  l_iface_rec.creation_date     := SYSDATE;
  l_iface_rec.created_by        := l_user_id;
  l_iface_rec.last_update_login := -1;
  l_iface_seq_rec.last_update_date  := SYSDATE;
  l_iface_seq_rec.last_updated_by   := l_user_id;
  l_iface_seq_rec.creation_date     := SYSDATE;
  l_iface_seq_rec.created_by        := l_user_id;
  l_iface_seq_rec.last_update_login := -1;
 
  l_iface_res_rec.last_update_date  := SYSDATE;
  l_iface_res_rec.last_updated_by   := l_user_id;
  l_iface_res_rec.creation_date     := SYSDATE;
  l_iface_res_rec.created_by        := l_user_id;
  l_iface_res_rec.last_update_login := -1;
 
  l_iface_rec.process_flag                 := 1;
  l_iface_rec.transaction_type             := 'UPDATE';
  l_iface_rec.organization_id              := l_cur_mfg_org_id;
  l_iface_rec.assembly_item_id             := i.assembly_item_id;
  l_iface_rec.assembly_item_number         := i.segment1;
 
  INSERT INTO bom.bom_op_routings_interface VALUES l_iface_rec;
 
  l_iface_seq_rec.process_flag                 := 1;
  l_iface_seq_rec.transaction_type             := 'CREATE';
  l_iface_seq_rec.organization_id              := l_iface_rec.organization_id;
  l_iface_seq_rec.assembly_item_number         := l_iface_rec.assembly_item_number;
  l_iface_seq_rec.alternate_routing_designator := l_iface_rec.alternate_routing_designator;
 
  if substr(i.segment1,7,1)<='2' then 
     l_iface_seq_rec.operation_seq_num        := 20;
  else
     l_iface_seq_rec.operation_seq_num        := 70;
  end if;
   
  l_iface_seq_rec.department_code              := 'A70'; 
  l_iface_seq_rec.effectivity_date             := SYSDATE;
  l_iface_seq_rec.reference_flag               :=2;
  l_iface_seq_rec.standard_operation_id        :=10;
 
  INSERT INTO bom.bom_op_sequences_interface VALUES l_iface_seq_rec;
 
  l_iface_res_rec.process_flag                 := 1;  
  l_iface_res_rec.organization_id              := l_iface_seq_rec.organization_id;  
  l_iface_res_rec.assembly_item_number         := l_iface_seq_rec.assembly_item_number;
  l_iface_res_rec.alternate_routing_designator := l_iface_seq_rec.alternate_routing_designator;
  l_iface_res_rec.operation_seq_num            := l_iface_seq_rec.operation_seq_num;
  l_iface_res_rec.effectivity_date             := l_iface_seq_rec.effectivity_date;  
  l_iface_res_rec.transaction_type             := 'UPDATE'; 
  
  l_iface_res_rec.resource_seq_num             := 1;  
  l_iface_res_rec.resource_code                := 'A70E001';
  l_iface_res_rec.usage_rate_or_amount         := i.unit_volume;
  INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
 
  l_iface_res_rec.resource_seq_num             := 2;  
  l_iface_res_rec.resource_code                := 'A70M001';
  l_iface_res_rec.usage_rate_or_amount         := i.unit_volume;
  INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
 
  l_iface_res_rec.resource_seq_num             := 3;  
  l_iface_res_rec.resource_code                := 'A70P001';
  l_iface_res_rec.usage_rate_or_amount         := i.unit_volume;
  INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
 
  l_iface_res_rec.resource_seq_num             := 4;  
  l_iface_res_rec.resource_code                := 'AN0T001';
  l_iface_res_rec.usage_rate_or_amount         := round(i.unit_volume*0.47361,6);
  INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
 
end loop;  

commit;

END;

3. 單獨通過製程資源介面表,插入資源


DECLARE
 
  l_iface_res_rec  bom.bom_op_resources_interface%ROWTYPE;
  l_cur_mfg_org_id   NUMBER := 116; --Current Inv Organization
  l_user_id          NUMBER := 1584; --User ID, Sysadmin here,TJ_MIS08
 
  cursor xxx is
   select distinct     
         bor.routing_sequence_id,     
         bos.operation_sequence_id,
         bos.operation_seq_num,
         bore.resource_seq_num,
         SPNLtoPCS.element_value,
         msi.unit_volume
    from BOM_OPERATIONAL_ROUTINGS bor,
         BOM_OPERATION_SEQUENCES  bos,
         bom_operation_resources  bore,
         mtl_system_items_b       msi,
         (select md.inventory_item_id,md.element_value
            from apps.MTL_DESCR_ELEMENT_VALUES md
           where md.element_sequence=140)SPNLtoPCS --出貨片排版        
   where bor.routing_sequence_id=bos.routing_sequence_id
     and bor.organization_id=116
     and bos.standard_operation_id=45
     and bos.operation_sequence_id=bore.operation_sequence_id
     and bore.resource_id =63
     and bor.assembly_item_id=SPNLtoPCS.inventory_item_id(+)
     and bor.assembly_item_id=msi.inventory_item_id
     and msi.organization_id=115
     --and msi.segment1='C226AA4049B11'
     ;
  
BEGIN
 
 for i in xxx loop
 
  l_iface_res_rec.last_update_date  := SYSDATE;
  l_iface_res_rec.last_updated_by   := l_user_id;
  l_iface_res_rec.creation_date     := SYSDATE;
  l_iface_res_rec.created_by        := l_user_id;
  l_iface_res_rec.last_update_login := -1; 
 
  l_iface_res_rec.process_flag                 := 1;
  l_iface_res_rec.routing_sequence_id         := i.routing_sequence_id;
  l_iface_res_rec.operation_sequence_id        :=i.operation_sequence_id;
  l_iface_res_rec.transaction_type             :='CREATE';
  l_iface_res_rec.effectivity_date             :=to_date('12-JUL-2006 10:14:03',
                                                          'DD-MON-YYYY HH24:MI:SS');
  
  l_iface_res_rec.resource_seq_num             := 5;
  l_iface_res_rec.resource_id                  :=202;
  l_iface_res_rec.usage_rate_or_amount         :=i.element_value;
   
  INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
                                                        
  l_iface_res_rec.resource_seq_num             := 6;
  l_iface_res_rec.resource_id                  :=7008;
  l_iface_res_rec.usage_rate_or_amount         :=i.unit_volume;
   
  INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;
 
 end loop ;
 
 commit;
 
END;

4.通過介面批量刪除特定站別的所有BOM物料

declare   
 
      cursor xxx_comp is      
        
      select msi.segment1,
             bbom.ASSEMBLY_ITEM_ID,
             bbom.BILL_SEQUENCE_ID,
             bc.operation_seq_num,
             bc.item_num,
             bc.component_item_id,
             bc.component_sequence_id,
             msi1.segment1 com_segment1
       from  bom_bill_of_materials        bbom,
             bom_components_b             bc,
             bom.bom_operational_routings bor,
             bom.bom_operation_sequences  bos,
             bom.bom_standard_operations  bso,
             mtl_system_items_b           msi, --成品料號
             mtl_system_items_b           msi1 --採購料號            
       where bbom.common_bill_sequence_id=bc.bill_sequence_id
       --and   bc.disable_date is null
       and   bc.implementation_date is not null
       and   bbom.ORGANIZATION_ID=116--&ORGANIZATION_ID --組織
       and   bbom.ASSEMBLY_ITEM_ID=bor.assembly_item_id
       and   bbom.ORGANIZATION_ID=bor.organization_id
       and   bor.routing_sequence_id=bos.routing_sequence_id
       and   bos.operation_seq_num=bc.operation_seq_num
       and   bbom.ORGANIZATION_ID=bso.organization_id
       and   bos.standard_operation_id=bso.standard_operation_id
       and   bos.department_id=bso.department_id
       and   bos.disable_date is null
       and   bso.operation_code='A700'              --D700表示 OSP
       and   bc.component_item_id=msi1.inventory_item_id
       and   msi1.organization_id=115
       and   bbom.ASSEMBLY_ITEM_ID=msi.inventory_item_id
       and   bbom.ORGANIZATION_ID=msi.organization_id
       and   msi.inventory_item_status_code='Active'
       and   msi.segment1='A681AC8033C10'
       ;     
     p_assem  mtl_system_items_b.segment1 %type;
    
  begin   
          p_assem:='AAAA';  --初值
            
          for p_comp in xxx_comp
          loop   
             if p_assem<>p_comp.segment1  then 
            
                p_assem:=p_comp.segment1;     
                     
             INSERT into bom_bill_of_mtls_interface
                        (assembly_item_id,
                         organization_id,
                         bill_sequence_id,
                         process_flag,
                         organization_code,
                         item_number,
                         assembly_type,
                         last_updated_by,
                         created_by,
                         transaction_type
                         )
                   VALUES
                         (
                         p_comp.ASSEMBLY_ITEM_ID,
                         116,
                         p_comp.BILL_SEQUENCE_ID, --bill_sequence_iD
                         1,
                         'TJ1',
                         p_comp.segment1,
                         1,                      
                         -1, --更新時用系統使用者
                         -1,
                         'UPDATE'
                         );
               end if;

            INSERT into bom_inventory_comps_interface
                        (
                         component_item_id,
                         assembly_item_id,
                         bill_sequence_id,
                         COMPONENT_SEQUENCE_ID,             
                         last_updated_by,
                         created_by,             
                         operation_seq_num,             
                         --effectivity_date,             
                         transaction_type,             
                         process_flag,             
                         disable_date,       
                         organization_code,
                         delete_group_name
                        )
                  VALUES
                       (
                        p_comp.component_item_id,
                        p_comp.ASSEMBLY_ITEM_ID,
                        p_comp.BILL_SEQUENCE_ID, --bill_sequence_iD           
                        p_comp.component_sequence_id, --COMPONENT_SEQUENCE_ID           
                        -1,
                        -1 ,           
                        p_comp.operation_seq_num,           
                        --TRUNC(SYSDATE),           
                        'DELETE',
                        1,           
                        TRUNC(SYSDATE),           
                        'TJ1',
                        'C1213'
                       );
             commit;
             end loop;     
  exception when others then
      rollback;
        raise_application_error (-20001,
                                  'Disable item is failure!'
                                 );
  end;

5. 單獨通過製程資源介面表,刪除資源


DECLARE
 
  l_iface_res_rec  bom.bom_op_resources_interface%ROWTYPE;
  l_cur_mfg_org_id   NUMBER := 116; --Current Inv Organization
  l_user_id          NUMBER := 1584; --User ID, Sysadmin here,TJ_MIS08
 
cursor xxx is
  select distinct     
         bor.routing_sequence_id,     
         bos.operation_sequence_id,
         bos.operation_seq_num,
         bore.resource_seq_num
    from BOM_OPERATIONAL_ROUTINGS bor,
         BOM_OPERATION_SEQUENCES  bos,
         bom_operation_resources  bore
   where bor.routing_sequence_id=bos.routing_sequence_id
     and bor.organization_id=116
     and bos.standard_operation_id=45
     and bos.operation_sequence_id=bore.operation_sequence_id
     and bore.resource_id <>63
    ;
 
 
BEGIN

 for i in xxx loop

  l_iface_res_rec.last_update_date  := SYSDATE;
  l_iface_res_rec.last_updated_by   := l_user_id;
  l_iface_res_rec.creation_date     := SYSDATE;
  l_iface_res_rec.created_by        := l_user_id;
  l_iface_res_rec.last_update_login := -1; 
  l_iface_res_rec.process_flag                 := 1;  
  l_iface_res_rec.transaction_type             :='DELETE';
  l_iface_res_rec.operation_sequence_id        := i.operation_sequence_id;
  l_iface_res_rec.resource_seq_num             := i.resource_seq_num;
  l_iface_res_rec.routing_sequence_id          := i.routing_sequence_id;  
  l_iface_res_rec.operation_seq_num            := i.operation_seq_num;
  l_iface_res_rec.effectivity_date             :=to_date('12-JUL-2006 10:14:03',
                                                          'DD-MON-YYYY HH24:MI:SS');
                                                                                                                   
  INSERT INTO bom.bom_op_resources_interface VALUES l_iface_res_rec;

 end loop;
 
END;

6. 刪除特定製程


DECLARE

 l_iface_rec        bom.bom_op_routings_interface%ROWTYPE;
 l_iface_seq_rec    bom.bom_op_sequences_interface%ROWTYPE;
 l_iface_res_rec    bom.bom_op_resources_interface%ROWTYPE;
 l_cur_mfg_org_id   NUMBER := 116;  --Current Inv Organization
 l_user_id          NUMBER := 1584; --User ID, Sysadmin here,TJ_MIS08
 l_transaction_type varchar2(20);
 
 cursor xxx is
  select distinct
         bor.organization_id,
         bor.assembly_item_id,
         msi.segment1,
         bor.routing_sequence_id,
         msi.unit_volume,
         bos.operation_seq_num,
         bos.department_id,
         bos.operation_sequence_id
    from BOM_OPERATIONAL_ROUTINGS bor,
         BOM_OPERATION_SEQUENCES  bos,
         mtl_system_items_b       msi
   where bor.routing_sequence_id=bos.routing_sequence_id
     and bor.organization_id=116
     and bos.standard_operation_id=45
     and length(msi.segment1)=13
     and bor.assembly_item_id=msi.inventory_item_id
     and msi.organization_id=115
     and msi.segment1='A681AC8033C10'
    ;
    
BEGIN

 for i in xxx loop
 
  l_iface_rec.last_update_date      := SYSDATE;
  l_iface_rec.last_updated_by       := l_user_id;
  l_iface_rec.creation_date         := SYSDATE;
  l_iface_rec.created_by            := l_user_id;
  l_iface_rec.last_update_login     := -1;
  l_iface_seq_rec.last_update_date  := SYSDATE;
  l_iface_seq_rec.last_updated_by   := l_user_id;
  l_iface_seq_rec.creation_date     := SYSDATE;
  l_iface_seq_rec.created_by        := l_user_id;
  l_iface_seq_rec.last_update_login := -1;
 
  l_iface_res_rec.last_update_date  := SYSDATE;
  l_iface_res_rec.last_updated_by   := l_user_id;
  l_iface_res_rec.creation_date     := SYSDATE;
  l_iface_res_rec.created_by        := l_user_id;
  l_iface_res_rec.last_update_login := -1;
 
  l_iface_rec.process_flag                 := 1;
  l_iface_rec.transaction_type             := 'UPDATE';
  l_iface_rec.organization_id              := l_cur_mfg_org_id;
  l_iface_rec.assembly_item_number         := i.segment1;
 
  INSERT INTO bom.bom_op_routings_interface VALUES l_iface_rec;
 
  l_iface_seq_rec.process_flag                 := 1;
  l_iface_seq_rec.delete_group_name            := 'yang1213';
  l_iface_seq_rec.transaction_type             := 'DELETE';
  l_iface_seq_rec.operation_sequence_id        := i.operation_sequence_id;

 
  INSERT INTO bom.bom_op_sequences_interface VALUES l_iface_seq_rec;
 
 end loop;
 commit;
 
END;


刪除製程、工序或BOM是需要指定刪除群組。有事還會經常出現“You cannot update or delete the operation resources when there is no operation sequence num 70”異常。此時,建議不要通過組裝料號或id進行刪除,直接用 routing id刪除。

附上從Metalink網站上下載的技術文件,很有用的。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/16860121/viewspace-714924/,如需轉載,請註明出處,否則將追究法律責任。

相關文章