CREATE OR REPLACE PACKAGE APPS.XX_RECEIPT_CREATE_PKG AS
PROCEDURE XX_RECEIPT_CREATE_PROC(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
END XX_RECEIPT_CREATE_PKG;
/
CREATE OR REPLACE PACKAGE BODY APPS.XX_RECEIPT_CREATE_PKG AS
PROCEDURE XX_RECEIPT_CREATE_PROC(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER) IS
P_API_VERSION NUMBER;
P_INIT_MSG_LIST VARCHAR2(240);
P_COMMIT VARCHAR2(240);
P_VALIDATION_LEVEL NUMBER;
P_DOC_SEQUENCE_VALUE NUMBER;
P_COMMENTS VARCHAR2(240);
P_ISSUER_NAME VARCHAR2(240);
P_ISSUE_DATE DATE;
P_ISSUER_BANK_BRANCH_ID NUMBER;
P_TRX_NUMBER NUMBER;
P_RECEIPT_COMMENTS VARCHAR2(200);
P_CUSTOMER_TRX_ID RA_CUSTOMER_TRX.CUSTOMER_TRX_ID%TYPE;
P_DEPOSIT_DATE DATE;
X_RETURN_STATUS VARCHAR2(1);
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(240);
P_CR_ID AR_CASH_RECEIPTS.CASH_RECEIPT_ID%TYPE;
P_GLOBAL_ATTRIBUTE_REC AR_RECEIPT_API_PUB.GLOBAL_ATTRIBUTE_REC_TYPE;
P_ATTRIBUTE_REC AR_RECEIPT_API_PUB.ATTRIBUTE_REC_TYPE;
P_CUSTOMER_NUMBER VARCHAR2(240);
P_CUSTOMER_SITE_USE_ID NUMBER;
P_CUSTOMER_NAME VARCHAR2(240);
P_CUSTOMER_ID NUMBER;
P_CUSTOMER_RECEIPT_REFERENCE VARCHAR2(40);
P_RECEIPT_DATE DATE;
P_AMOUNT NUMBER;
P_RECEIPT_METHOD_NAME VARCHAR2(250);
P_RECEIPT_NUMBER VARCHAR2(240);
P_RECEIPT_METHOD_ID NUMBER;
P_CURRENCY_CODE VARCHAR2(10);
P_GL_DATE DATE ;--:='28-FEB-2010';
P_ORG_ID NUMBER;
P_EXCHANGE_RATE NUMBER;
QB_TRANSACTION_NUM NUMBER;
L_CUSTOMER_TRX_ID NUMBER;
L_ERROR_FLAG VARCHAR2(1):='N';
L_ERROR_MESG VARCHAR2(2000);
L_CONCAT_ERROR_MESG VARCHAR2(4000);
L_REC_COUNT NUMBER :=0;
L_REC_CNT_SUCCESS NUMBER:=0;
L_REC_CNT_FAILED NUMBER:=0;
L_RECEIPT_METHOD_NAME VARCHAR2(100);
-- VARIABLES TO CAPTURE VALIDATIONS
L_CUST_ID NUMBER;
L_CUST_SITE NUMBER;
L_CURRENCY_CODE VARCHAR2(3);
L_PERIOD_STATUS VARCHAR2(10);
L_RECEIPT_METHOD_ID NUMBER;
L_METHOD_CODE VARCHAR2(60);
L_PERIOD VARCHAR2(10);
L_CUST_ACCT_SITE_ID NUMBER;
L_TRX_NUMBER RA_CUSTOMER_TRX.TRX_NUMBER%TYPE;
CURSOR C1 IS
SELECT * FROM XX_AR_UNAPP_RECEIPTS
where STATUS_FLAG='N';
--AND SNO between 11 and 100;
BEGIN
FOR C_REC IN C1 LOOP
L_ERROR_FLAG :='N';
L_ERROR_MESG := NULL;
L_CONCAT_ERROR_MESG := NULL;
L_CUST_ID := NULL;
L_CUST_SITE := NULL;
L_CURRENCY_CODE := NULL;
L_PERIOD_STATUS := NULL;
L_RECEIPT_METHOD_ID := NULL;
L_METHOD_CODE := NULL;
L_REC_COUNT:=L_REC_COUNT+1;
FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Receipt Amount is : '||C_REC.RECEIPT_AMT);
-- ========================================================
-- CUSTOMER VALIDATION STARTS
-- =======================================================
FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Customer Validation Starts ');
/*
IF C_REC.CUSTOMER_NUMBER IS NOT NULL THEN
BEGIN
SELECT HCA.CUST_ACCOUNT_ID
INTO L_CUST_ID
FROM HZ_CUST_ACCOUNTS_ALL HCA
WHERE HCA.ACCOUNT_NUMBER= (select CUSTOMER_NUMBER from ar_customers where UPPER(CUSTOMER_NAME)=UPPER(C_REC.CUSTOMER_NAME)
and CUSTOMER_NUMBER=c_rec.CUSTOMER_NUMBER)
--HCA.ACCOUNT_NUMBER=C_REC.CUSTOMER_NUMBER
AND HCA.STATUS='A' AND ROWNUM=1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_ERROR_MESG:='Customer Neither DEFINED Nor ACTIVE in ORACLE EBS';
L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG||L_ERROR_MESG;
FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
L_ERROR_FLAG:='Y';
WHEN OTHERS
THEN
L_ERROR_MESG:= SUBSTR(SQLERRM,1,255);
FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG||'~'||L_ERROR_MESG;
L_ERROR_FLAG:='Y';
END;
END IF;
FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Customer Validation Ends '||'Customer ID Is '||L_CUST_ID||' For Customer Num '||C_REC.CUSTOMER_NAME); */
-- ========================================================
-- CUSTOMER VALIDATION ENDS
-- ========================================================
-- ========================================================
-- CUSTOMER SITE VALIDATION STARTS
-- =======================================================
FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Customer Site Validation Starts ');
/*
IF C_REC.CUSTOMER_NUMBER IS NOT NULL THEN
BEGIN
SELECT HCSUA.SITE_USE_ID
INTO L_CUST_SITE
FROM HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_CUST_SITE_USES_ALL HCSUA
WHERE CUST_ACCOUNT_ID=(SELECT CUST_ACCOUNT_ID FROM HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP
WHERE HCA.ACCOUNT_NUMBER= (select CUSTOMER_NUMBER from ar_customers where UPPER(CUSTOMER_NAME)=UPPER(C_REC.CUSTOMER_NAME)
and CUSTOMER_NUMBER=c_rec.CUSTOMER_NUMBER)
AND HP.PARTY_ID=HCA.PARTY_ID)
AND HCASA.CUST_ACCT_SITE_ID=HCSUA.CUST_ACCT_SITE_ID
AND HCSUA.ORG_ID=<ORG_ID>
AND SITE_USE_CODE='BILL_TO' AND HCSUA.STATUS='A' AND ROWNUM=1;
/* SELECT OITO.SITE_USE_ID
INTO L_CUST_SITE
FROM OE_INVOICE_TO_ORGS_V OITO
WHERE 1=1 --AND OITO.SITE_USE_ID=C_REC.BILL_TO_LOCATION
AND OITO.CUSTOMER_ID=L_CUST_ID AND ORG_ID=103
AND OITO.STATUS='A'
AND OITO.ADDRESS_STATUS='A'AND ROWNUM=1;
/*
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_ERROR_MESG:='Bill To Location Neither DEFINED Nor ACTIVE in ORACLE EBS';
L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG ||L_ERROR_MESG;
FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
L_ERROR_FLAG:='Y';
WHEN OTHERS
THEN
L_ERROR_MESG:= SUBSTR(SQLERRM,1,255);
FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG||'~'||L_ERROR_MESG;
L_ERROR_FLAG:='Y';
END;
END IF;
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Bill-To Location Validation Ends -- '||' '||'Bill to Cust Is '||L_CUST_SITE);
*/
-- ====================================================
-- CUSTOMER SITE VALIDATION ENDS
-- ====================================================
-- =====================================================
-- CURRENCY VALIDATION STARTS --
-- =====================================================
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Currency Validation - Starts ');
BEGIN
SELECT CURRENCY_CODE
INTO L_CURRENCY_CODE
FROM FND_CURRENCIES
WHERE UPPER(CURRENCY_CODE)=UPPER(TRIM(C_REC.CURRENCY))
AND ENABLED_FLAG='Y';
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Currency is '||L_CURRENCY_CODE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_ERROR_MESG:='Currency not Defined in EBS';
L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG ||L_ERROR_MESG;
FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
L_ERROR_FLAG:='Y';
WHEN OTHERS
THEN
L_ERROR_MESG:= SUBSTR(SQLERRM,1,255);
FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG||'~'||L_ERROR_MESG;
L_ERROR_FLAG:='Y';
END;
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Currency Validation - Ends ');
-- =====================================================
-- CURRENCY VALIDATION ENDS --
-- =====================================================
-- ====================================================
-- PERIOD VALIDATION STARTS
-- ====================================================
FND_FILE.PUT_LINE ( FND_FILE.LOG,'PERIOD VALIDATION STARTS ');
-- BEGIN
--
-- BEGIN
-- L_PERIOD:=TO_CHAR(C_REC.RECEIPT_DATE,'MON');
-- END;
--
-- --FND_FILE.PUT_LINE ( FND_FILE.LOG,'PERIOD IS '||L_PERIOD);
--
-- END;
-- SELECT CLOSING_STATUS
-- INTO L_PERIOD_STATUS
-- FROM GL_PERIOD_STATUSES GPS
-- WHERE GPS.APPLICATION_ID=222 -- RECEIVABLES APPLICATION ID
-- AND UPPER(SUBSTR(GPS.PERIOD_NAME,1,3))=TO_CHAR(C_REC.RECEIPT_DATE,'MON')
-- AND GPS.CLOSING_STATUS='O' AND ROWNUM=1;
-- --AND TRUNC((C_REC.RECEIPT_DATE)) BETWEEN TRUNC(GPS.START_DATE) AND TRUNC(GPS.END_DATE)
-- EXCEPTION
-- WHEN NO_DATA_FOUND
-- THEN
-- L_ERROR_MESG:='GIVEN RECEIPT DATE IS NOT IN OPEN PERIOD.. PLEASE OPEN THE PERIODS IN RECEIVABLES';
-- L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG ||L_ERROR_MESG;
-- FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
-- L_ERROR_FLAG:='Y';
--
-- WHEN OTHERS
-- THEN
-- L_ERROR_MESG:= SUBSTR(SQLERRM,1,255);
-- FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
-- L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG||'~'||L_ERROR_MESG;
-- L_ERROR_FLAG:='Y';
--
-- END;
--FND_FILE.PUT_LINE ( FND_FILE.LOG,'PERIOD VALIDATION - ENDS ' ||'STATUS IS '||L_PERIOD_STATUS||' OF PERIOD '||L_PERIOD);
-- ====================================================
-- PERIOD VALIDATION ENDS
-- ====================================================
-- ====================================================
-- FETCHIING RECEIPT METHOD ID STARTS
-- ====================================================
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Fetching Receipt Method ID Starts ');
BEGIN
SELECT ARM.RECEIPT_METHOD_ID,NAME
INTO L_RECEIPT_METHOD_ID ,L_METHOD_CODE
FROM AR_RECEIPT_METHODS ARM
WHERE trim(ARM.NAME)=trim(C_REC.RECEIPT_METHOD)
AND ROWNUM=1;
-- AND ARM.END_DATE IS NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_ERROR_MESG:='RECEIPT METHOD IS NOT DEFINED IN EBS';
L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG ||L_ERROR_MESG;
FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
L_ERROR_FLAG:='Y';
WHEN OTHERS
THEN
L_ERROR_MESG:= SUBSTR(SQLERRM,1,255);
FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG||'~'||L_ERROR_MESG;
L_ERROR_FLAG:='Y';
END;
FND_FILE.PUT_LINE ( FND_FILE.LOG,'RECEIPT Method Validation - Ends '||'METHOD ID IS '||L_RECEIPT_METHOD_ID||' For Method '||L_METHOD_CODE);
-- ====================================================
-- FETCHIING RECEIPT METHOD ID ENDS
-- ====================================================
--***********************************************************
-- INVOICE VALIDATION
--**********************************************************
--BEGIN
-- SELECT DISTINCT CUSTOMER_TRX_ID,TRX_NUMBER
-- INTO L_CUSTOMER_TRX_ID,L_TRX_NUMBER
--FROM XX_AR_RECEIPTS_DF A ,OE_ORDER_HEADERS_ALL B,RA_CUSTOMER_TRX_ALL C
--WHERE TRIM(A.INVOICE_NUMBER)=TRIM(B.ATTRIBUTE2)
--AND TO_CHAR(B.ORDER_NUMBER)=C.CT_REFERENCE;
--
--FND_FILE.PUT_LINE ( FND_FILE.LOG,'INVOICE NUMBER IS:'||L_TRX_NUMBER||' AND TRX ID:'||L_CUSTOMER_TRX_ID);
-- EXCEPTION
-- WHEN NO_DATA_FOUND
-- THEN
-- L_ERROR_MESG:='INVOICE IS NOT DEFINED';
-- L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG ||L_ERROR_MESG;
-- FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
-- L_ERROR_FLAG:='Y';
--
-- WHEN OTHERS
-- THEN
-- L_ERROR_MESG:= SUBSTR(SQLERRM,1,255);
-- FND_FILE.PUT_LINE ( FND_FILE.LOG,L_ERROR_MESG);
-- L_CONCAT_ERROR_MESG:=L_CONCAT_ERROR_MESG||'~'||L_ERROR_MESG;
-- L_ERROR_FLAG:='Y';
--
-- END;
FND_FILE.PUT_LINE ( FND_FILE.LOG,'ASSIGNING VALUES TO API VARIABLES ');
BEGIN
IF L_ERROR_FLAG ='N' THEN
P_ORG_ID :=<ORG_ID>;
P_CUSTOMER_ID := L_CUST_ID;
P_CUSTOMER_NAME :=C_REC.CUSTOMER_NAME;
P_CUSTOMER_NUMBER :=C_REC.CUSTOMER_NUMBER;
P_RECEIPT_METHOD_ID := L_RECEIPT_METHOD_ID;
P_RECEIPT_NUMBER :=C_REC.RECEIPT_NUMBER;
P_RECEIPT_METHOD_NAME :=L_METHOD_CODE;
P_RECEIPT_DATE := C_REC.RECEIPT_DATE;
P_CURRENCY_CODE := L_CURRENCY_CODE;
P_EXCHANGE_RATE :=C_REC.REBASSED_EXCHANGE_RATE;
P_GL_DATE := C_REC.GL_DATE;
P_CUSTOMER_SITE_USE_ID := L_CUST_SITE;
P_AMOUNT :=C_REC.REBASSED_RECEIPT_AMT;
FND_FILE.PUT_LINE ( FND_FILE.LOG,'STARTING API ');
AR_RECEIPT_API_PUB.CREATE_CASH (
P_API_VERSION => 1.0
,P_INIT_MSG_LIST => FND_API.G_TRUE
,P_COMMIT => FND_API.G_TRUE
,P_ORG_ID => P_ORG_ID
--,P_CUSTOMER_NAME => P_CUSTOMER_NAME
--,P_CUSTOMER_NUMBER => P_CUSTOMER_NUMBER
,P_RECEIPT_NUMBER => P_RECEIPT_NUMBER
,P_RECEIPT_METHOD_NAME => L_RECEIPT_METHOD_NAME
,P_RECEIPT_DATE => P_RECEIPT_DATE
,P_CURRENCY_CODE => P_CURRENCY_CODE
,P_GL_DATE => P_GL_DATE
,P_AMOUNT => P_AMOUNT
,P_CUSTOMER_ID => P_CUSTOMER_ID
,P_CUSTOMER_SITE_USE_ID => P_CUSTOMER_SITE_USE_ID
,P_RECEIPT_METHOD_ID => L_RECEIPT_METHOD_ID
--,P_RECEIPT_METHOD_NAME => P_RECEIPT_METHOD_NAME
--,P_EXCHANGE_RATE => P_EXCHANGE_RATE
--,P_EXCHANGE_RATE_TYPE => 'User'
--,P_EXCHANGE_RATE => P_EXCHANGE_RATE
--,P_EXCHANGE_RATE_DATE => '31-DEC-2012'
,P_COMMENTS => P_COMMENTS
,P_DOC_SEQUENCE_VALUE => NULL
--,P_CUSTOMER_RECEIPT_REFERENCE => P_CUSTOMER_RECEIPT_REFERENCE
,X_RETURN_STATUS => X_RETURN_STATUS
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA
,P_CR_ID => P_CR_ID
);
FND_FILE.PUT_LINE ( FND_FILE.LOG,'API ENDS ');
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Message'||X_MSG_DATA||'Msg Count'||X_MSG_COUNT||'Return Status'||X_RETURN_STATUS);
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Return Status = '|| SUBSTR (X_RETURN_STATUS,1,255));
IF (X_RETURN_STATUS = 'S') THEN
COMMIT;
L_REC_CNT_SUCCESS:=L_REC_CNT_SUCCESS+1;
FND_FILE.PUT_LINE ( FND_FILE.LOG,'API SUCCESSFULLY COMPLETED ');
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Return Status = '|| SUBSTR (X_RETURN_STATUS,1,255));
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Message Data = '|| X_MSG_DATA);
FND_FILE.PUT_LINE ( FND_FILE.LOG,'CASH RECEIPT ID IS '||P_CR_ID);
FND_FILE.PUT_LINE ( FND_FILE.LOG,'========================================================');
UPDATE XX_AR_UNAPP_RECEIPTS
SET STATUS_FLAG='P',CREATE_ERROR_MESG = X_MSG_DATA,RECEIPT_CREATED='Y'
WHERE RECEIPT_NUMBER=C_REC.RECEIPT_NUMBER
-- AND CUSTOMER_NUMBER=C_REC.CUSTOMER_NUMBER
-- AND INVOICE_NUMBER=C_REC.INVOICE_NUMBER
and sno=c_rec.sno;
commit;
ELSE
ROLLBACK;
L_REC_CNT_FAILED:=L_REC_CNT_FAILED+1;
FND_FILE.PUT_LINE ( FND_FILE.LOG,'API FAILED ');
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Return Status = '|| SUBSTR (X_RETURN_STATUS,1,255));
FND_FILE.PUT_LINE ( FND_FILE.LOG,'Message Data = '|| X_MSG_DATA);
-- UPDATING STAGING TABLES
UPDATE XX_AR_UNAPP_RECEIPTS
SET STATUS_FLAG='E',CREATE_ERROR_MESG = X_MSG_DATA
WHERE RECEIPT_NUMBER=C_REC.RECEIPT_NUMBER
-- AND CUSTOMER_NUMBER=C_REC.CUSTOMER_NUMBER
-- AND INVOICE_NUMBER=C_REC.INVOICE_NUMBER
and sno=c_rec.sno;
commit;
IF X_MSG_COUNT >=0 THEN
FOR I IN 1..10 LOOP
FND_FILE.PUT_LINE ( FND_FILE.LOG,SUBSTR (FND_MSG_PUB.GET(P_ENCODED => FND_API.G_FALSE ), 1, 255));
END LOOP;
END IF;
END IF;
ELSE
FND_FILE.PUT_LINE ( FND_FILE.LOG,'ERROR FLAG IS '||L_ERROR_FLAG ||' '|| L_CONCAT_ERROR_MESG);
UPDATE XX_AR_UNAPP_RECEIPTS
SET STATUS_FLAG='E',
CREATE_ERROR_MESG =L_CONCAT_ERROR_MESG,
RECEIPT_CREATED='N'
WHERE RECEIPT_NUMBER=C_REC.RECEIPT_NUMBER
-- AND CUSTOMER_NUMBER=C_REC.CUSTOMER_NUMBER
--AND INVOICE_NUMBER=C_REC.INVOICE_NUMBER
and sno=c_rec.sno;
commit;
END IF;
END;
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.LOG,' No of Records Read '||L_REC_COUNT);
FND_FILE.PUT_LINE(FND_FILE.LOG,' No of Records Successfully Completed '||L_REC_CNT_SUCCESS);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'No of Records Errored Out '||L_REC_CNT_FAILED);
END XX_RECEIPT_CREATE_PROC;
END XX_RECEIPT_CREATE_PKG;
/
No comments:
Post a Comment