当前位置： 移动技术网 > IT编程>数据库>Oracle > 导出大数据方法。批量导BOM

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

2020年04月01日  | 移动技术网IT编程  | 我要评论
 
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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网