导出大数据方法。批量导BOM

 

create table cux_import_data_e45 as

select * from cux_import_data_l11

where 1>2;

 

create table cux.lmh_segment1_e41 as

select * from cux.lmh_segment1_l11

where 1>2;

 

cux.lmh_segment1_e41

 

create table cux_import_data_e41 as

select * from cux_import_data_l11

where 1>2

 

 

delete from cux_import_data_e41;

插入表中

insert into cux_import_data_e41(a,id)

select bbo.bill_sequence_id,rownum

from bom_bill_of_materials bbo,

mtl_system_items_b msi

where bbo.organization_id = msi.organization_id

and bbo.assembly_item_id = msi.inventory_item_id

and msi.organization_id = 140

and msi.inventory_item_status_code='active'

and bbo.bill_sequence_id=664146

;

 

select *

--delete

from cux_import_data_e41;

 

分组

update cux_import_data_e41 set b = round(id/100);

 

/* select * from cux.lmh_segment1_l11*/

 

 

bom_bill_of_materials_200113;

 

create table bom_bill_of_materials_20200401 as

select bbo.* from bom_bill_of_materials bbo,

mtl_system_items_b msi

where bbo.organization_id = msi.organization_id

and bbo.assembly_item_id = msi.inventory_item_id

and msi.organization_id = 140

and msi.inventory_item_status_code='active';

-- and bbo.bill_sequence_id=664146

 

select * from cux.lmh_segment1_e41;

delete from cux.lmh_segment1_e41;

 

declare

cursor bom_cur is

select b from cux_import_data_e41

group by b ;

 

 

begin

for l_bom in bom_cur loop

insert into cux.lmh_segment1_e41

select

to_char(msi.segment1) segment1,

to_char(msii.segment1) segment2,

bic.component_quantity,

cux_html_bom_report.getsubstr(bic.component_sequence_id,140) sub_item,

cux_html_bom_report.getdescstr2(bic.component_sequence_id) descstr,

cux_html_bom_report.getrevisionstr(bbo.assembly_item_id,140) revision,

bbo.attribute10

from bom_bill_of_materials bbo,--20200401 bbo,

bom_inventory_components bic,

mtl_system_items_b msi,

mtl_system_items_b msii

where bbo.bill_sequence_id = bic.bill_sequence_id

and bbo.organization_id = msi.organization_id

and bbo.assembly_item_id = msi.inventory_item_id

and bic.component_item_id = msii.inventory_item_id

and bbo.organization_id = 140

and msii.organization_id = 140

and msi.organization_id = 140

and msii.inventory_item_status_code='active'

and nvl(bic.disable_date,sysdate+1)>sysdate

and bbo.bill_sequence_id--=664146

in (select a from cux_import_data_e41 where b = l_bom.b);

end loop;

commit;

end;

 

 

select segment1 ,segment2,component_quantity,sub_item , to_char(descstr ) a,revision ,attribute10 from cux.lmh_segment1_e41