Creating a Credit memo against an Invoice through API.

CREATE OR REPLACE PACKAGE APPS.CREDIT_INVOICE_PKG AS
PROCEDURE CREATE_CM (
ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2);
END CREDIT_INVOICE_PKG;
/




CREATE OR REPLACE PACKAGE BODY APPS.CREDIT_INVOICE_PKG AS
PROCEDURE CREATE_CM(
ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2
)
IS
--V_TRX_DATE DATE := '31-DEC-2012';
--======================================================================================
--  Program Name:  CREDIT_INVOICE_PKG
--  Developed By:  Adithya.V
--  Description :  This Package will be used to apply the negative credit amounts to invoices.
-- Created 02-Jan-2013
-- Last updated 10-Jan-2013
--======================================================================================
CURSOR C_TRX
IS
SELECT TRX.TRX_NUMBER, TRX.CUSTOMER_TRX_ID CUSTOMER_TRX_ID,
NVL (SUM (TL.EXTENDED_AMOUNT), 0) LINE_AMT,TRX.TRX_DATE,
(SELECT NVL (SUM (L.EXTENDED_AMOUNT), 0)
FROM RA_CUSTOMER_TRX_LINES_ALL L
WHERE L.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND L.LINE_TYPE = 'TAX') TAX_AMT,
TYP.NAME TRX_TYPE_NAME
FROM RA_CUSTOMER_TRX_ALL TRX,
RA_CUSTOMER_TRX_LINES_ALL TL,
RA_CUST_TRX_TYPES_ALL TYP
WHERE 1 = 1
AND TRX.CUSTOMER_TRX_ID = TL.CUSTOMER_TRX_ID
AND TYP.CUST_TRX_TYPE_ID = TRX.CUST_TRX_TYPE_ID
AND TRX.CUSTOMER_TRX_ID IN (SELECT CUSTOMER_TRX_ID  FROM ZN_AR_INVOICE_STG_2 WHERE INV_TYPE<>'DM')
AND TYP.TYPE <> 'CM'
AND TL.LINE_TYPE = 'LINE'
AND TRX.ORG_ID = 630
AND NVL (TRX.ATTRIBUTE7, 'N') != 'Y'
GROUP BY TRX.TRX_NUMBER, TRX.CUSTOMER_TRX_ID ,
TYP.NAME,TRX.TRX_DATE;


V_MSG_COUNT NUMBER;
V_MSG_DATA VARCHAR2 (2000);
V_RETURN_STATUS VARCHAR2 (1);
V_REQUEST_ID NUMBER;
V_CM_REASON VARCHAR2(100) := 'A/R ERROR';
V_DUMMY VARCHAR2(240);
V_CM_TRX_NUM RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE;
V_CM_TRX_ID RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE;
V_TRX_SOURCE VARCHAR2(100);
V_ORG_NAME VARCHAR2(100);


BEGIN
--DBMS_SUPPORT.START_TRACE;

FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'FIRST==> ' || V_RETURN_STATUS);

 BEGIN
SELECT NAME INTO V_ORG_NAME
FROM HR_OPERATING_UNITS
WHERE ORGANIZATION_ID = 630;
 END;


IF UPPER(V_ORG_NAME) LIKE UPPER('Airtel Zambia OU')
THEN
V_TRX_SOURCE := 'AZM DATA CONVERSION';
END IF;


FND_GLOBAL.APPS_INITIALIZE
(USER_ID => FND_PROFILE.VALUE('USER_ID'),
RESP_ID => FND_PROFILE.VALUE('RESP_ID'),
RESP_APPL_ID => FND_PROFILE.VALUE('RESP_APPL_ID'),
SECURITY_GROUP_ID => 0);
MO_GLOBAL.SET_POLICY_CONTEXT ('S', 630);
ARP_GLOBAL.INIT_GLOBAL;


FOR C_TRX_ROW IN C_TRX
LOOP

BEGIN
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Before Procedure ==> ' || C_TRX_ROW.CUSTOMER_TRX_ID);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Before Procedure ==> ' || C_TRX_ROW.LINE_AMT);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Before Procedure ==> ' || C_TRX_ROW.TAX_AMT);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Before Procedure ==> ' || V_CM_REASON);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Before Procedure ==> ' || V_TRX_SOURCE);

AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_TRUE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_CUSTOMER_TRX_ID => C_TRX_ROW.CUSTOMER_TRX_ID,
P_LINE_CREDIT_FLAG => 'N',
P_LINE_AMOUNT => -C_TRX_ROW.LINE_AMT,
P_TAX_AMOUNT => -C_TRX_ROW.TAX_AMT,
P_FREIGHT_AMOUNT=> 0,
P_CM_REASON_CODE => 'A/R ERROR',
--P_COMMENTS => 'N'
P_SKIP_WORKFLOW_FLAG => 'Y',
--P_CREDIT_METHOD_INSTALLMENTS => NULL,
--P_CREDIT_METHOD_RULES => NULL,
P_BATCH_SOURCE_NAME => 'AZM DATA CONVERSION-2',
P_ORG_ID => 630,
P_DISPUTE_DATE => '31-Dec-12',
-- P_TRX_NUMBER => 'CM-'||C_TRX_ROW.TRX_NUMBER,
X_REQUEST_ID => V_REQUEST_ID,
X_RETURN_STATUS => V_RETURN_STATUS,
X_MSG_COUNT => V_MSG_COUNT,
X_MSG_DATA => V_MSG_DATA
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'After Procedure ==> ' || V_RETURN_STATUS);


FND_MSG_PUB.COUNT_AND_GET (P_ENCODED => FND_API.G_FALSE,
P_COUNT => V_MSG_COUNT,
P_DATA => V_MSG_DATA
);
DBMS_OUTPUT.PUT_LINE ('Return Status ==> ' || V_RETURN_STATUS);
DBMS_OUTPUT.PUT_LINE ('Credit Memo request_id ==> ' || V_REQUEST_ID);
DBMS_OUTPUT.PUT_LINE ('V_msg_count ==> ' || V_MSG_COUNT);
-- FND_MESSAGE.RETRIEVE (V_DUMMY);
DBMS_OUTPUT.PUT_LINE('Error in Create Credit Transaction:'
|| SUBSTR (SQLERRM, 1, 80)||' -- '||V_DUMMY
);

COMMIT;

--FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Return Status ==> ' || V_RETURN_STATUS);
-- FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Credit Memo request_id ==> ' || V_REQUEST_ID);
-- FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'V_msg_count ==> ' || V_MSG_COUNT);



IF V_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS
THEN
IF V_REQUEST_ID IS NOT NULL
THEN --SHOW CM TRX NUMBER --
BEGIN
SAVEPOINT NO_DATA;


SELECT TRX.TRX_NUMBER, TRX.CUSTOMER_TRX_ID
INTO V_CM_TRX_NUM, V_CM_TRX_ID
FROM RA_CM_REQUESTS R, RA_CUSTOMER_TRX_ALL TRX
WHERE R.REQUEST_ID = V_REQUEST_ID
AND R.CM_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND R.CUSTOMER_TRX_ID = C_TRX_ROW.CUSTOMER_TRX_ID;

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, V_RETURN_STATUS|| ' - AR invoice: '
|| C_TRX_ROW.TRX_NUMBER
|| ' | Generated Credit Transaction Number: '
|| V_CM_TRX_NUM||' | Success: '||V_MSG_DATA);



DBMS_OUTPUT.PUT_LINE ( 'AR invoice: '
|| C_TRX_ROW.TRX_NUMBER
|| ' | Generated Credit Transaction Number: '
|| V_CM_TRX_NUM||' | Success: '||V_MSG_DATA);

---REMARKED THE AR INVOICE WAS GENERATED CM
UPDATE RA_CUSTOMER_TRX_ALL TRX
SET TRX.ATTRIBUTE7 = 'Y',
TRX.ATTRIBUTE8 = V_CM_TRX_ID,
TRX.ATTRIBUTE9 = V_CM_TRX_NUM,
TRX.ATTRIBUTE10 = V_REQUEST_ID,
TRX.INTERFACE_HEADER_ATTRIBUTE12 = NULL
WHERE 1 = 1
AND TRX.ORG_ID = 630
AND TRX.CUSTOMER_TRX_ID = C_TRX_ROW.CUSTOMER_TRX_ID;


--UPDATE RA_CUSTOMER_TRX_ALL TRX
--SET TRX.TRX_DATE = V_TRX_DATE
--WHERE 1 = 1
--AND TRX.ORG_ID = 630
--AND TRX.CUSTOMER_TRX_ID = V_CM_TRX_ID;


UPDATE RA_CUSTOMER_TRX_LINES_ALL RCTL2
SET RCTL2.INTERFACE_LINE_ATTRIBUTE12 = NULL
WHERE RCTL2.CUSTOMER_TRX_ID = C_TRX_ROW.CUSTOMER_TRX_ID
AND RCTL2.ORG_ID = 630
AND RCTL2.LINE_TYPE = 'LINE'
AND RCTL2.INTERFACE_LINE_ATTRIBUTE12 IS NOT NULL;
--

COMMIT;
EXCEPTION WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'AR invoice: '
|| C_TRX_ROW.TRX_NUMBER
|| ' | Generated Credit Transaction Number not found. '
);
DBMS_OUTPUT.PUT_LINE ( 'AR invoice: '
|| C_TRX_ROW.TRX_NUMBER
||'  Generated Credit Transaction Number not found. '
);
ROLLBACK TO NO_DATA;
---REMARKED THE AR INVOICE WAS GENERATED CM
END;
END IF;
END IF;



IF V_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR
OR V_RETURN_STATUS = FND_API.G_RET_STS_ERROR
THEN
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Invoice Number: '

|| C_TRX_ROW.TRX_NUMBER

||' | Error:' || V_MSG_DATA );
DBMS_OUTPUT.PUT_LINE ('Error:' || V_MSG_DATA
||' | For Invoice Number: '||C_TRX_ROW.TRX_NUMBER);
END IF;




IF V_MSG_COUNT > 1
THEN
V_MSG_COUNT := 0;

LOOP
V_MSG_DATA :=
FND_MSG_PUB.GET (FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);

IF V_MSG_DATA IS NULL
THEN
EXIT;
END IF;

FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'msg_data:' || V_MSG_DATA);
DBMS_OUTPUT.PUT_LINE ( 'msg_data:' || V_MSG_DATA);
END LOOP;
END IF;



EXCEPTION
WHEN OTHERS
THEN


FND_MESSAGE.RETRIEVE (V_DUMMY);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'Error in Create Credit Transaction:'
|| SUBSTR (SQLERRM, 1, 80)||' -- '||V_DUMMY
);
DBMS_OUTPUT.PUT_LINE('Error in Create Credit Transaction:'
|| SUBSTR (SQLERRM, 1, 80)||' -- '||V_DUMMY
);
END;
--END IF;
--------------------------------------------------------------------------------
END LOOP;

END CREATE_CM;

END CREDIT_INVOICE_PKG;
/