EBS oracle 批量导入更新MOQ（最小拆分量、采购提前期、最小订购量、最小包装量）

excel的列：组织id,供应商编号，供应商地点，料号，最小拆分量、采购提前期、最小订购量、最小包装量

 

--采购导入更新moq四个值,若有为空的那列，会保留原来的值，不会去更新那列的值
procedure update_po_moq as
cursor po_moq_cur is
select id,
trim(org_id) org_id,
trim(vendor_no) vendor_no,
trim(vendor_site) vendor_site,
trim(segment1) segment1,
trim(min_split_qty) min_split_qty,
trim(processing_lead_time) processing_lead_time,
trim(min_order_qty) min_order_qty,
trim(fixed_order_quantity) fixed_order_quantity
from cux.update_po_moq_temp1; --cux.excel_bom_lmh;
e_check exception;
v_err_message varchar2(2000);
l_vendor_id number :=0;
l_vendor_site number :=0;
l_inventory_item_id number := 0;
-- l_organization_id number := 0;
l_count number := 0;
begin
begin
--清空导入错误表
delete from cux_import_data_error;
for po_moq_rec in po_moq_cur loop
begin
select count(*) into l_count
from meg_cux_org_v
where operating_unit = po_moq_rec.org_id;
exception
when no_data_found then
v_err_message := '业务实体【' || po_moq_rec.org_id || '】不存在-';
raise e_check;
end;
begin
select p.vendor_id into l_vendor_id
from po_vendors p
where segment1 = po_moq_rec.vendor_no;
exception
when no_data_found then
v_err_message := '供应商编号【' || po_moq_rec.vendor_no || '】不存在-';
raise e_check;
end;
begin
select t.vendor_site_id into l_vendor_site
from ap_supplier_sites_all t
where vendor_site_code = po_moq_rec.vendor_site
and t.vendor_id=l_vendor_id
and org_id =po_moq_rec.org_id;
exception
when no_data_found then
v_err_message := '供应商简称/地点【' || po_moq_rec.vendor_site || '】不存在-';
raise e_check;
end ;
begin
select msi.inventory_item_id
into l_inventory_item_id
from mtl_system_items_b msi
where msi.segment1 = po_moq_rec.segment1
and msi.organization_id = 140;
exception
when no_data_found then
v_err_message := '料号【' || po_moq_rec.segment1 || '】不存在-';
raise e_check;
end;
begin
--为空的列，不更新，保留原来的值
update po_asl_attributes paa
set paa.attribute1=nvl(po_moq_rec.min_split_qty,paa.attribute1),
paa.processing_lead_time=nvl(po_moq_rec.processing_lead_time,paa.processing_lead_time),
paa.min_order_qty=nvl(po_moq_rec.min_order_qty,paa.min_order_qty),
paa.fixed_lot_multiple=nvl(po_moq_rec.fixed_order_quantity,paa.fixed_lot_multiple)
where vendor_id= l_vendor_id
and vendor_site_id =l_vendor_site
and item_id =l_inventory_item_id;
exception
when e_check then
insert into cux_import_data_error
(id, column_values, err_message)
values
(po_moq_rec.id,
'【' || po_moq_rec.vendor_no || '】-【' ||
po_moq_rec.segment1 || '】',
v_err_message);
end;
end loop;
end;
commit;
end update_po_moq;