CREATE OR REPLACE PACKAGE MEW_SO_SALESREPS_IMPORT_PKG IS
--通过ADI 更新
PROCEDURE MAIN(P_BATCH_ID NUMBER
,P_OU_NAME VARCHAR2
,P_ORDER_NUMBER VARCHAR2
,P_SALESREP_NUMBER VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL);
--通过此过程更新销售员
PROCEDURE UPDATE_SO_SALESREP_PRC(P_BATCH_ID NUMBER
,P_ORG_ID VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY MEW_SO_SALESREPS_IMPORT_PKG IS
PROCEDURE MAIN(P_BATCH_ID NUMBER
,P_OU_NAME VARCHAR2
,P_ORDER_NUMBER VARCHAR2
,P_SALESREP_NUMBER VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL --Line Num
,P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE3 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL) IS
V_OE_HEADERS_IFACE_REC OE_HEADERS_IFACE_ALL%ROWTYPE;
V_OE_LINES_IFACE_REC OE_LINES_IFACE_ALL%ROWTYPE;
V_ORG_ID NUMBER;
V_SALESREP_ID NUMBER;
V_CURSALESREP_ID NUMBER;
V_HEADER_ID NUMBER;
CNT NUMBER;
BEGIN
BEGIN
SELECT ORGANIZATION_ID
INTO V_ORG_ID
FROM HR_OPERATING_UNITS
WHERE NAME = P_OU_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
APP_EXCEPTION.RAISE_EXCEPTION('OU NAME不存在');
END;
BEGIN
SELECT SALESREP_ID
INTO V_SALESREP_ID
FROM JTF_RS_SALESREPS JRS
WHERE JRS.SALESREP_NUMBER = P_SALESREP_NUMBER
AND JRS.ORG_ID = V_ORG_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
APP_EXCEPTION.RAISE_EXCEPTION('销售员不存在');
WHEN TOO_MANY_ROWS THEN
APP_EXCEPTION.RAISE_EXCEPTION('销售员编号在系统中存在重复');
END;
BEGIN
SELECT OOH.ORIG_SYS_DOCUMENT_REF
,OOH.HEADER_ID
,OOH.ORDER_SOURCE_ID
,OOH.ORG_ID
INTO V_OE_HEADERS_IFACE_REC.ORIG_SYS_DOCUMENT_REF
,V_HEADER_ID
,V_OE_HEADERS_IFACE_REC.ORDER_SOURCE_ID
,V_OE_HEADERS_IFACE_REC.ORG_ID
FROM OE_ORDER_HEADERS_ALL OOH
WHERE OOH.ORDER_NUMBER = P_ORDER_NUMBER
AND OOH.ORG_ID = V_ORG_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
APP_EXCEPTION.RAISE_EXCEPTION('订单编号不存在');
WHEN TOO_MANY_ROWS THEN
APP_EXCEPTION.RAISE_EXCEPTION('订单编号在系统中存在重复');
END;
BEGIN
SELECT NVL(OOL.SALESREP_ID
,-2)
,OOL.ORG_ID
,OOL.ORDER_SOURCE_ID
,OOL.ORIG_SYS_DOCUMENT_REF
,OOL.ORIG_SYS_LINE_REF
,OOL.ORIG_SYS_SHIPMENT_REF
,OOL.LINE_ID
INTO V_CURSALESREP_ID
,V_OE_LINES_IFACE_REC.ORG_ID
,V_OE_LINES_IFACE_REC.ORDER_SOURCE_ID
,V_OE_LINES_IFACE_REC.ORIG_SYS_DOCUMENT_REF
,V_OE_LINES_IFACE_REC.ORIG_SYS_LINE_REF
,V_OE_LINES_IFACE_REC.ORIG_SYS_SHIPMENT_REF
,V_OE_LINES_IFACE_REC.LINE_ID
FROM OE_ORDER_LINES_ALL OOL
WHERE OOL.HEADER_ID = V_HEADER_ID
AND OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER = P_ATTRIBUTE1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
APP_EXCEPTION.RAISE_EXCEPTION('销售订单行不存');
END;
IF V_CURSALESREP_ID = -2
THEN
APP_EXCEPTION.RAISE_EXCEPTION('订单行销售员信息未维护');
END IF;
IF V_CURSALESREP_ID <> 100004726 -- ='Hanate (PEWC)'
THEN
APP_EXCEPTION.RAISE_EXCEPTION('行销售员不等于Hanate (PEWC)');
END IF;
IF V_CURSALESREP_ID = V_SALESREP_ID
THEN
APP_EXCEPTION.RAISE_EXCEPTION('当前销售员与系统中一致!');
END IF;
SELECT COUNT(*)
INTO CNT
FROM OE_HEADERS_IFACE_ALL OHIA
WHERE OHIA.BATCH_ID = P_BATCH_ID
AND OHIA.HEADER_ID = V_HEADER_ID;
IF NVL(CNT
,0) = 0
THEN
--OE_HEADERS_IFACE
V_OE_HEADERS_IFACE_REC.OPERATION_CODE := 'UPDATE';
V_OE_HEADERS_IFACE_REC.CREATION_DATE := SYSDATE;
V_OE_HEADERS_IFACE_REC.CREATED_BY := FND_GLOBAL.USER_ID;
V_OE_HEADERS_IFACE_REC.LAST_UPDATE_DATE := SYSDATE;
V_OE_HEADERS_IFACE_REC.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
V_OE_HEADERS_IFACE_REC.HEADER_ID := V_HEADER_ID;
V_OE_HEADERS_IFACE_REC.BATCH_ID := P_BATCH_ID;
INSERT INTO OE_HEADERS_IFACE_ALL VALUES V_OE_HEADERS_IFACE_REC;
END IF;
--OE_LINES_IFACE
V_OE_LINES_IFACE_REC.OPERATION_CODE := 'UPDATE';
V_OE_LINES_IFACE_REC.CREATION_DATE := SYSDATE;
V_OE_LINES_IFACE_REC.CREATED_BY := FND_GLOBAL.USER_ID;
V_OE_LINES_IFACE_REC.LAST_UPDATE_DATE := SYSDATE;
V_OE_LINES_IFACE_REC.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
V_OE_LINES_IFACE_REC.SALESREP_ID := V_SALESREP_ID; --100006424 =Jo Wan (PEWC)
INSERT INTO OE_LINES_IFACE_ALL VALUES V_OE_LINES_IFACE_REC;
END;
--通过此过程更新销售员
PROCEDURE UPDATE_SO_SALESREP_PRC(P_BATCH_ID NUMBER
,P_ORG_ID VARCHAR2) IS
V_OE_HEADERS_IFACE_REC OE_HEADERS_IFACE_ALL%ROWTYPE;
V_OE_LINES_IFACE_REC OE_LINES_IFACE_ALL%ROWTYPE;
V_ORG_ID NUMBER;
V_SALESREP_ID NUMBER;
V_CURSALESREP_ID NUMBER;
V_HEADER_ID NUMBER;
CNT NUMBER;
CURSOR C1 IS
SELECT *
FROM MEWBACKUP.MEW_SO_SALESREPS_TEMP T
WHERE T.BATCH_ID = P_BATCH_ID
AND T.ERROR_CODE IS NULL
AND t.order_number = '40106708';
V_IMPORT_FLAG VARCHAR2(1) := 'Y';
BEGIN
/* BEGIN
SELECT ORGANIZATION_ID
INTO V_ORG_ID
FROM HR_OPERATING_UNITS
WHERE NAME = P_OU_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
APP_EXCEPTION.RAISE_EXCEPTION('OU NAME不存在');
END;*/
FOR R1 IN C1
LOOP
V_IMPORT_FLAG := 'Y';
BEGIN
SELECT SALESREP_ID
INTO V_SALESREP_ID
FROM JTF_RS_SALESREPS JRS
WHERE JRS.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND JRS.ORG_ID = P_ORG_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
SET T.ERROR_CODE = 'E'
,T.ERROR_MESSAGE = '销售员不存在'
WHERE T.BATCH_ID = P_BATCH_ID
AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND T.LINE_NUMBER = R1.LINE_NUMBER
AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
V_IMPORT_FLAG := 'N';
WHEN TOO_MANY_ROWS THEN
UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
SET T.ERROR_CODE = 'E'
,T.ERROR_MESSAGE = '销售员编号在系统中存在重复'
WHERE T.BATCH_ID = P_BATCH_ID
AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND T.LINE_NUMBER = R1.LINE_NUMBER
AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
V_IMPORT_FLAG := 'N';
END;
BEGIN
SELECT OOH.ORIG_SYS_DOCUMENT_REF
,OOH.HEADER_ID
,OOH.ORDER_SOURCE_ID
,OOH.ORG_ID
INTO V_OE_HEADERS_IFACE_REC.ORIG_SYS_DOCUMENT_REF
,V_HEADER_ID
,V_OE_HEADERS_IFACE_REC.ORDER_SOURCE_ID
,V_OE_HEADERS_IFACE_REC.ORG_ID
FROM OE_ORDER_HEADERS_ALL OOH
WHERE OOH.ORDER_NUMBER = R1.ORDER_NUMBER
AND OOH.ORG_ID = P_ORG_ID
AND OOH.FLOW_STATUS_CODE NOT IN
('CANCELLED'
,'CLOSED');
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
SET T.ERROR_CODE = 'E'
,T.ERROR_MESSAGE = '订单编号不存在或已取消 关闭'
WHERE T.BATCH_ID = P_BATCH_ID
AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND T.LINE_NUMBER = R1.LINE_NUMBER
AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
V_IMPORT_FLAG := 'N';
WHEN TOO_MANY_ROWS THEN
UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
SET T.ERROR_CODE = 'E'
,T.ERROR_MESSAGE = '订单编号在系统中存在重复'
WHERE T.BATCH_ID = P_BATCH_ID
AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND T.LINE_NUMBER = R1.LINE_NUMBER
AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
V_IMPORT_FLAG := 'N';
END;
BEGIN
SELECT NVL(OOL.SALESREP_ID
,-2)
,OOL.ORG_ID
,OOL.ORDER_SOURCE_ID
,OOL.ORIG_SYS_DOCUMENT_REF
,OOL.ORIG_SYS_LINE_REF
,OOL.ORIG_SYS_SHIPMENT_REF
,OOL.LINE_ID
INTO V_CURSALESREP_ID
,V_OE_LINES_IFACE_REC.ORG_ID
,V_OE_LINES_IFACE_REC.ORDER_SOURCE_ID
,V_OE_LINES_IFACE_REC.ORIG_SYS_DOCUMENT_REF
,V_OE_LINES_IFACE_REC.ORIG_SYS_LINE_REF
,V_OE_LINES_IFACE_REC.ORIG_SYS_SHIPMENT_REF
,V_OE_LINES_IFACE_REC.LINE_ID
FROM OE_ORDER_LINES_ALL OOL
WHERE OOL.HEADER_ID = V_HEADER_ID
AND OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER =
R1.LINE_NUMBER
AND OOL.FLOW_STATUS_CODE NOT IN
('CANCELLED'
,'CLOSED');
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
SET T.ERROR_CODE = 'E'
,T.ERROR_MESSAGE = '销售订单行不存在或已取消或关闭'
WHERE T.BATCH_ID = P_BATCH_ID
AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND T.LINE_NUMBER = R1.LINE_NUMBER
AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
V_IMPORT_FLAG := 'N';
END;
/* IF V_CURSALESREP_ID = -2
THEN
UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
SET T.ERROR_CODE = 'E'
,T.ERROR_MESSAGE = '订单行销售员信息未维护'
WHERE T.BATCH_ID = P_BATCH_ID
AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND T.LINE_NUMBER = R1.LINE_NUMBER
AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
V_IMPORT_FLAG := 'N';
END IF;
IF V_CURSALESREP_ID <> 100004726 -- ='Hanate (PEWC)'
THEN
UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
SET T.ERROR_CODE = 'E'
,T.ERROR_MESSAGE = '行销售员不等于Hanate (PEWC)'
WHERE T.BATCH_ID = P_BATCH_ID
AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND T.LINE_NUMBER = R1.LINE_NUMBER
AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
V_IMPORT_FLAG := 'N';
END IF;
IF V_CURSALESREP_ID = V_SALESREP_ID
THEN
UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
SET T.ERROR_CODE = 'E'
,T.ERROR_MESSAGE = '当前销售员与系统中一致'
WHERE T.BATCH_ID = P_BATCH_ID
AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND T.LINE_NUMBER = R1.LINE_NUMBER
AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
\* RAISE_APPLICATION_ERROR(-20001
,V_SALESREP_ID || '-----' ||
V_CURSALESREP_ID || '-----' ||
R1.Order_Number || '----' ||
R1.LINE_NUMBER);*\
V_IMPORT_FLAG := 'N';
END IF;*/
IF V_IMPORT_FLAG = 'Y'
THEN
UPDATE MEWBACKUP.MEW_SO_SALESREPS_TEMP T
SET T.ERROR_CODE = 'S'
,T.ERROR_MESSAGE = '成功导入接口表'
WHERE T.BATCH_ID = P_BATCH_ID
AND T.SALESREP_NUMBER = R1.SALESREP_NUMBER
AND T.LINE_NUMBER = R1.LINE_NUMBER
AND T.ORDER_NUMBER = R1.ORDER_NUMBER;
SELECT COUNT(*)
INTO CNT
FROM OE_HEADERS_IFACE_ALL OHIA
WHERE OHIA.BATCH_ID = P_BATCH_ID
AND OHIA.HEADER_ID = V_HEADER_ID;
IF NVL(CNT
,0) = 0
THEN
--OE_HEADERS_IFACE
V_OE_HEADERS_IFACE_REC.OPERATION_CODE := 'UPDATE';
V_OE_HEADERS_IFACE_REC.CREATION_DATE := SYSDATE;
V_OE_HEADERS_IFACE_REC.CREATED_BY := FND_GLOBAL.USER_ID;
V_OE_HEADERS_IFACE_REC.LAST_UPDATE_DATE := SYSDATE;
V_OE_HEADERS_IFACE_REC.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
V_OE_HEADERS_IFACE_REC.HEADER_ID := V_HEADER_ID;
V_OE_HEADERS_IFACE_REC.BATCH_ID := P_BATCH_ID;
-- V_OE_HEADERS_IFACE_REC.SALESREP_ID := V_SALESREP_ID;
INSERT INTO OE_HEADERS_IFACE_ALL VALUES V_OE_HEADERS_IFACE_REC;
END IF;
--OE_LINES_IFACE
V_OE_LINES_IFACE_REC.OPERATION_CODE := 'UPDATE';
V_OE_LINES_IFACE_REC.CREATION_DATE := SYSDATE;
V_OE_LINES_IFACE_REC.CREATED_BY := FND_GLOBAL.USER_ID;
V_OE_LINES_IFACE_REC.LAST_UPDATE_DATE := SYSDATE;
V_OE_LINES_IFACE_REC.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
V_OE_LINES_IFACE_REC.SALESREP_ID := V_SALESREP_ID; --100006424 =Jo Wan (PEWC)
INSERT INTO OE_LINES_IFACE_ALL VALUES V_OE_LINES_IFACE_REC;
END IF;
END LOOP;
COMMIT;
END;
END;
/