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
需要了解更多数据库技术:导出大数据方法。批量导BOM,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/796397.html