oracle最小采购量,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;