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;