--Update 客戶剩餘的信用額度
CREATE OR REPLACE PACKAGE XXX_CMS_OM_INT_PKG AS
PROCEDURE EXPOSURE_TMEP(P_ORG_ID IN NUMBER
,P_USER_ID IN NUMBER
,P_OK_FLG OUT VARCHAR2);
PROCEDURE NEW_EXPOSURE_IMPORT(ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2);
PROCEDURE UPD_EXPOSURE_TEMP(P_CUST_NUM IN VARCHAR2
,P_TYPE IN VARCHAR2
,P_ERROR_MSG IN VARCHAR2);
END XXX_CMS_OM_INT_PKG;
/
CREATE OR REPLACE PACKAGE BODY XXX_CMS_OM_INT_PKG AS
PROCEDURE EXPOSURE_TMEP(P_ORG_ID IN NUMBER
,P_USER_ID IN NUMBER
,P_OK_FLG OUT VARCHAR2) IS
CUSTUSESITEID NUMBER;
L_CHK_DATA_ERROR EXCEPTION;
--
LS_CUST_NAME VARCHAR2(240);
LS_NO_DATA_FOUND VARCHAR2(100);
L_EXPOSURE_IFC ONT.OE_EXPOSURE_INTERFACE%ROWTYPE;
L_EXPOSURE_BAK OE_EXPOSURE_INTERFACE_BACKUP%ROWTYPE;
LN_OK_CNT NUMBER := 0;
LS_ERROR_MSG VARCHAR2(1000);
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG
,'Import to OE_EXPOSURE_INTERFACE Start Time:' ||
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
P_OK_FLG := 'Y';
FOR L_TEMP_REC IN (SELECT *
FROM XXX.CMS_OE_EXPOSURE_INTERFACE
WHERE NVL(EXPOSURE_INTERFACE_FLAG, 'N') = 'N') LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
BEGIN
SELECT HP.PARTY_NAME
INTO LS_CUST_NAME
FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP
WHERE HCA.PARTY_ID = HP.PARTY_ID
AND HCA.STATUS = 'A'
AND HCA.ACCOUNT_NUMBER = L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LS_NO_DATA_FOUND := 'Cust Number: ' ||
L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER ||
' , not exists in ERP';
RAISE L_CHK_DATA_ERROR;
END;
FND_FILE.PUT_LINE(FND_FILE.LOG
,'Customer Number: ' ||
L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Customer Name: ' || LS_CUST_NAME);
BEGIN
BEGIN
SELECT SITE_USE.LOCATION
INTO CUSTUSESITEID -- find the customer use site id information
FROM HZ_CUST_ACCOUNTS CUST
,HZ_CUST_ACCT_SITES_ALL CUST_SITE
,HZ_CUST_SITE_USES_ALL SITE_USE
WHERE CUST.ACCOUNT_NUMBER = L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER
AND CUST.CUST_ACCOUNT_ID = CUST_SITE.CUST_ACCOUNT_ID
AND CUST_SITE.CUST_ACCT_SITE_ID = SITE_USE.CUST_ACCT_SITE_ID
AND SITE_USE.PRIMARY_FLAG = 'Y'
AND SITE_USE.STATUS = 'A'
AND SITE_USE.SITE_USE_CODE = 'BILL_TO'
AND SITE_USE.ORG_ID = P_ORG_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LS_NO_DATA_FOUND := 'Bill to Address not exists in ERP';
RAISE L_CHK_DATA_ERROR;
END;
--Insert Values to OE_EXPOSURE_INTERFACE
L_EXPOSURE_IFC.EXPOSURE_SOURCE_CODE := L_TEMP_REC.EXPOSURE_SOURCE_CODE;
L_EXPOSURE_IFC.OPERATION_CODE := L_TEMP_REC.OPERATION_CODE;
L_EXPOSURE_IFC.EXPOSURE_AMOUNT := L_TEMP_REC.EXPOSURE_AMOUNT;
L_EXPOSURE_IFC.CURRENCY_CODE := L_TEMP_REC.CURRENCY_CODE;
L_EXPOSURE_IFC.BILL_TO_SITE_USE_ID := CUSTUSESITEID;
L_EXPOSURE_IFC.ORG_ID := P_ORG_ID;
L_EXPOSURE_IFC.CREATED_BY := P_USER_ID;
L_EXPOSURE_IFC.LAST_UPDATED_BY := P_USER_ID;
L_EXPOSURE_IFC.CREATION_DATE := SYSDATE;
L_EXPOSURE_IFC.LAST_UPDATE_DATE := SYSDATE;
INSERT INTO ONT.OE_EXPOSURE_INTERFACE VALUES L_EXPOSURE_IFC;
COMMIT;
LN_OK_CNT := LN_OK_CNT + 1;
UPD_EXPOSURE_TEMP(P_CUST_NUM => L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER
,P_TYPE => 'S'
,P_ERROR_MSG => NULL);
EXCEPTION
WHEN L_CHK_DATA_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG
,'Error Message: ' || LS_NO_DATA_FOUND);
WHEN OTHERS THEN
LS_ERROR_MSG := TO_CHAR(SQLCODE) || SQLERRM;
ROLLBACK;
UPD_EXPOSURE_TEMP(P_CUST_NUM => L_TEMP_REC.BILL_TO_CUSTOMER_NUMBER
,P_TYPE => 'F'
,P_ERROR_MSG => LS_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG
,'Insert Data has errors: ' || LS_ERROR_MSG);
END;
IF LN_OK_CNT = 0 THEN
P_OK_FLG := 'N';
END IF;
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG
,'Import to OE_EXPOSURE_INTERFACE END Time:' ||
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
END EXPOSURE_TMEP;
PROCEDURE NEW_EXPOSURE_IMPORT(ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2) IS
LN_ORG_ID NUMBER := FND_PROFILE.VALUE('ORG_ID');
LN_USER_ID NUMBER := FND_PROFILE.VALUE('USER_ID');
LS_OK_FLG VARCHAR2(1);
LN_REQUEST_ID NUMBER;
LS_PHASE VARCHAR2(50);
LS_STATUS VARCHAR2(50);
LS_DEV_PHASE VARCHAR2(50);
LS_DEV_STATUS VARCHAR2(50);
LS_MSG VARCHAR2(100);
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG
,'Start Time:' ||
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(LN_ORG_ID);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org ID:' || TO_CHAR(LN_ORG_ID));
FND_GLOBAL.APPS_INITIALIZE(USER_ID => LN_USER_ID
,RESP_ID => FND_PROFILE.VALUE('RESP_ID')
,RESP_APPL_ID => FND_PROFILE.VALUE('RESP_APPL_ID'));
--Improt Temp Table
--IMPORT_EXPOSURE_PKG(X_RETCODE => LS_RETCODE, X_ERRBUF => LS_ERRBUF);
EXPOSURE_TMEP(P_ORG_ID => LN_ORG_ID
,P_USER_ID => LN_USER_ID
,P_OK_FLG => LS_OK_FLG);
IF LS_OK_FLG = 'Y' THEN
--Submit Request to Run OE Exposure Import
LN_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'ONT'
,PROGRAM => 'OEXCEIMP'
,DESCRIPTION => NULL
,START_TIME => NULL
,SUB_REQUEST => FALSE
,ARGUMENT1 => LN_ORG_ID
,ARGUMENT2 => 'COMMISSION'
,ARGUMENT3 => NULL
,ARGUMENT4 => 'N'
,ARGUMENT5 => CHR(0));
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG
,'Request ID:' || TO_CHAR(LN_REQUEST_ID));
IF LN_REQUEST_ID > 0 THEN
IF FND_CONCURRENT.WAIT_FOR_REQUEST(LN_REQUEST_ID
,10
,0
,LS_PHASE
,LS_STATUS
,LS_DEV_PHASE
,LS_DEV_STATUS
,LS_MSG) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG
,'OE Exposure Import IS successful');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG, 'OE Exposure Import IS faild');
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Run Request has errors');
END IF;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG
,'End Time:' ||
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
END NEW_EXPOSURE_IMPORT;
PROCEDURE UPD_EXPOSURE_TEMP(P_CUST_NUM IN VARCHAR2
,P_TYPE IN VARCHAR2
,P_ERROR_MSG IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF P_TYPE = 'S' THEN
UPDATE XXX.CMS_OE_EXPOSURE_INTERFACE XT
SET XT.EXPOSURE_INTERFACE_FLAG = 'Y'
,XT.CHECK_FLAG = 'Y'
,XT.IMPORT_FLAG = 'Y'
,XT.SUCCESS_DATE = SYSDATE
WHERE XT.BILL_TO_CUSTOMER_NUMBER = P_CUST_NUM
AND NVL(XT.EXPOSURE_INTERFACE_FLAG, 'N') = 'N';
ELSE
UPDATE XXX.CMS_OE_EXPOSURE_INTERFACE XT
SET XT.EXPOSURE_INTERFACE_FLAG = 'N'
,XT.CHECK_FLAG = 'N'
,XT.IMPORT_FLAG = 'N'
,XT.ERR_MSG = P_ERROR_MSG
,XT.SUCCESS_DATE = NULL
WHERE XT.BILL_TO_CUSTOMER_NUMBER = P_CUST_NUM
AND NVL(XT.EXPOSURE_INTERFACE_FLAG, 'N') = 'N';
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG
,'Update XXX.CMS_OE_EXPOSURE_INTERFACE has errors');
END UPD_EXPOSURE_TEMP;
END XXX_CMS_OM_INT_PKG;
/
Oracle (6)