[Home] [Help]
PACKAGE BODY: APPS.ARI_SERVICE_CHARGE_PKG
Source
1 PACKAGE BODY ARI_SERVICE_CHARGE_PKG AS
2 /* $Header: ARISCRGB.pls 120.2 2005/10/31 04:14:38 vgundlap noship $ */
3
4 /*=======================================================================+
5 | Global Constants
6 +=======================================================================*/
7
8 G_PKG_NAME CONSTANT VARCHAR2(40) := 'ARI_SERVICE_CHARGE_PKG';
9 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
10
11 CURSOR C_INVOICE_SET IS
12 SELECT PAYMENT_SCHEDULE_ID,
13 PAYMENT_AMT,
14 CUSTOMER_ID,
15 ACCOUNT_NUMBER,
16 CUSTOMER_TRX_ID,
17 CURRENCY_CODE,
18 STATUS
19 FROM AR_IREC_PAYMENT_LIST_GT;
20
21 /*========================================================================
22 | PROCEDURE Adjust Invoice
23 |
24 | DESCRIPTION iReceivables adjust invoice
25 |
26 |
27 | PARAMETERS adjustment_rec_type IN adjustment_rec_type
28 |
29 *=======================================================================*/
30 FUNCTION ADJUST_INVOICE (P_ADJUSTMENT_REC_TYPE IN ARI_SERVICE_CHARGE_PKG.ADJUSTMENT_REC_TYPE)
31 RETURN VARCHAR2
32
33 IS
34
35 RETURN_VALUE VARCHAR2(01) ;
36 L_API_VERSION NUMBER ;
37 L_RETURN_STATUS VARCHAR2(1);
38 L_MSG_COUNT NUMBER;
39 L_MSG_DATA VARCHAR2(2000);
40 L_MSG_INDEX NUMBER;
41 L_MESG VARCHAR2(2000);
42 L_NEW_ADJUST_NUMBER VARCHAR2(20);
43 L_NEW_ADJUST_ID NUMBER(15);
44 L_ADJ_REC AR_ADJUSTMENTS%ROWTYPE;
45 L_PROCEDURE_NAME VARCHAR2(30) ;
46 l_debug_info VARCHAR2(200);
47
48 BEGIN
49
50 --Assign default values
51 RETURN_VALUE := FND_API.G_RET_STS_SUCCESS;
52 L_API_VERSION := 1.0;
53 L_RETURN_STATUS := NULL;
54 L_MSG_COUNT := 0;
55 L_MSG_DATA := NULL;
56 L_MSG_INDEX := 0;
57 L_MESG := NULL;
58 L_NEW_ADJUST_NUMBER := NULL;
59 L_NEW_ADJUST_ID := 0;
60 L_PROCEDURE_NAME:= '.ADJUST_INVOICE';
61
62 L_ADJ_REC.TYPE := 'CHARGES';
63 L_ADJ_REC.PAYMENT_SCHEDULE_ID := P_ADJUSTMENT_REC_TYPE.PAYMENT_SCHEDULE_ID;
64 L_ADJ_REC.RECEIVABLES_TRX_ID := P_ADJUSTMENT_REC_TYPE.RECEIVABLES_TRX_ID;
65 L_ADJ_REC.AMOUNT := P_ADJUSTMENT_REC_TYPE.AMOUNT;
66 -- KRM - Look into this logic
67 -- Since we are not doing a line adjustment, we may not
68 -- need this at all.
69 -- If the TYPE = Invoice then this value is not required
70 L_ADJ_REC.APPLY_DATE := P_ADJUSTMENT_REC_TYPE.APPLY_DATE;
71 L_ADJ_REC.GL_DATE := P_ADJUSTMENT_REC_TYPE.GL_DATE;
72 L_ADJ_REC.CREATED_FROM := P_ADJUSTMENT_REC_TYPE.CREATED_FROM;
73
74 --------------------------------------------------------------------
75 l_debug_info := 'Adjusting Invoice, calling CREATE_ADJUSTMENT';
76 --------------------------------------------------------------------
77
78 -- Bug 3892588 - Modified call to ensure the adjusted amount is not validated against the user's approval limit
79 AR_ADJUST_PUB.CREATE_ADJUSTMENT(P_API_NAME => 'AR_ADJUST_PUB'
80 ,P_API_VERSION => L_API_VERSION
81 ,P_INIT_MSG_LIST => FND_API.G_TRUE
82 ,P_MSG_COUNT => L_MSG_COUNT
83 ,P_MSG_DATA => L_MSG_DATA
84 ,P_RETURN_STATUS => L_RETURN_STATUS
85 ,P_ADJ_REC => L_ADJ_REC
86 ,P_NEW_ADJUST_NUMBER => L_NEW_ADJUST_NUMBER
87 ,P_NEW_ADJUST_ID => L_NEW_ADJUST_ID
88 ,P_CHK_APPROVAL_LIMITS => 'F');
89
90
91 IF L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
92 RETURN_VALUE := FND_API.G_RET_STS_ERROR;
93 --Bug 4146107 - Errors during payment not bubbled up
94 IF L_MSG_DATA IS NOT NULL THEN
95 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, L_PROCEDURE_NAME, L_MSG_DATA);
96 END IF;
97 IF (PG_DEBUG = 'Y') THEN
98 arp_standard.debug('Result from api: Adj Nbr = ' || L_NEW_ADJUST_NUMBER);
99 arp_standard.debug('L_RETURN_STATUS=>'||L_RETURN_STATUS);
100 arp_standard.debug('L_MSG_COUNT=>'||to_char(L_MSG_COUNT));
101 END IF;
102 END IF; -- END RETURN NOT SUCCESS
103
104 RETURN(RETURN_VALUE);
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 IF (PG_DEBUG = 'Y') THEN
109 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
110 arp_standard.debug('ERROR =>'|| SQLERRM);
111 END IF;
112 --Bug 3630101
113 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
114 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
115 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
116 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
117 FND_MSG_PUB.ADD;
118
119 RETURN_VALUE := FND_API.G_RET_STS_UNEXP_ERROR;
120 RETURN(RETURN_VALUE);
121 END ADJUST_INVOICE;
122
123
124 /* =======================================================================
125 | PROCEDURE Compute Service Charge
126 |
127 | DESCRIPTION iReceivables PROCEDURE TO RETURN THE SERVICE CHARGE -
128 |
129 |
130 |
131 * ======================================================================*/
132 PROCEDURE COMPUTE_SERVICE_CHARGE(P_INVOICE_SET IN OUT NOCOPY ARI_SERVICE_CHARGE_PKG.INVOICE_LIST_TABTYPE)
133 IS
134
135 L_PROCEDURE_NAME VARCHAR2(30);
136 L_COUNT NUMBER ;
137 L_TOTAL_COUNT NUMBER;
138 L_SERVICE_CHARGE_AMOUNT NUMBER;
139 L_TOTAL_SERVICE_CHARGE NUMBER;
140 l_debug_info VARCHAR2(200);
141 BEGIN
142
143 --Assign default values
144 L_PROCEDURE_NAME := '.COMPUTE_SERVICE_CHARGE';
145 L_SERVICE_CHARGE_AMOUNT := 0;
146 L_TOTAL_SERVICE_CHARGE := 0;
147
148 /*
149 This procedure can be customized by the customer.
150 Service charge can be added by the amount of the invoice or by the percentage
151 of the invoice amount or the flat amount or the layered rate.
152 --
153 1) CALCULATE THE ONE(FLAT CHARGE) CHARGE FOR THE SET OF INVOICES - IF WE DO
154 THAT FOR WHICH INVOICE WE UPDATE THE CHARGE IN THE GLOBAL TABLE AND ALSO
155 FOR WHICH INVOICE WE ADD THE AMOUNT.
156 2) IF THE SERVICE CHARGE IS CALCULATED FOR EVERY INVOICE,UPDATE the GLOBAL
157 TABLE WITH THE SERVICE CHARGE
158 */
159 --------------------------------------------------------------------
160 l_debug_info := 'Calculating service charge';
161 --------------------------------------------------------------------
162
163 -- Here is an example of a pro-rated service charge;
164 -- Flat rate service charge:
165 L_TOTAL_SERVICE_CHARGE := P_INVOICE_SET.COUNT * 2;
166
167 -- Prorate the service charge for the invoices
168 L_TOTAL_COUNT := P_INVOICE_SET.COUNT;
169 L_SERVICE_CHARGE_AMOUNT := L_TOTAL_SERVICE_CHARGE/L_TOTAL_COUNT;
170
171 -- Since we do not know how the table may have been indexed
172 -- we shall play it safe by using NEXT to traverse the table
173 L_COUNT := P_INVOICE_SET.FIRST;
174
175 -- CUSTOMIZED % BASED CODE
176 L_TOTAL_SERVICE_CHARGE := 0;
177
178 WHILE L_COUNT IS NOT NULL
179 LOOP
180 L_SERVICE_CHARGE_AMOUNT := P_INVOICE_SET(L_COUNT).PAYMENT_AMOUNT * .05/100;
181
182 P_INVOICE_SET(L_COUNT).SERVICE_CHARGE := L_SERVICE_CHARGE_AMOUNT;
183
184 L_TOTAL_SERVICE_CHARGE := L_TOTAL_SERVICE_CHARGE + L_SERVICE_CHARGE_AMOUNT;
185
186 L_COUNT := P_INVOICE_SET.NEXT(L_COUNT);
187 END LOOP;
188
189 EXCEPTION
190 WHEN OTHERS THEN
191 IF (PG_DEBUG = 'Y') THEN
192 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
193 arp_standard.debug('ERROR =>'|| SQLERRM);
194 END IF;
195 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
196 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
197 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
198 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
199 FND_MSG_PUB.ADD;
200
201 END COMPUTE_SERVICE_CHARGE;
202
203
204 /* =======================================================================
205 | PROCEDURE Apply Service Charge
206 |
207 | DESCRIPTION iReceivables PROCEDURE TO APPLY THE SERVICE CHARGE -
208 |
209 |
210 | PARAMETERS
211 |
212 * ======================================================================*/
213 FUNCTION APPLY_CHARGE(P_INVOICE_SET IN ARI_SERVICE_CHARGE_PKG.INVOICE_LIST_TABTYPE)
214 RETURN VARCHAR2
215 IS
216
217 L_RETURN_VALUE VARCHAR2(1) ;
218 L_PROCEDURE_NAME VARCHAR2(30);
219
220 L_ADJ_REC ARI_SERVICE_CHARGE_PKG.ADJUSTMENT_REC_TYPE;
221 L_COUNT NUMBER;
222 L_TOTAL_INVOICES NUMBER;
223 l_debug_info VARCHAR2(200);
224 L_CUSTOMER_ID NUMBER(15);
225 L_CUSTOMER_SITE_USE_ID NUMBER(15);
226
227 BEGIN
228 --Assign default values
229 L_RETURN_VALUE := NULL;
230 L_PROCEDURE_NAME := '.APPLY_CHARGE';
231
232 --fnd_log_repository.init;
233 -- Since we do not know how the table may have been indexed
234 -- we shall play it safe by using NEXT to traverse the table
235 L_COUNT := P_INVOICE_SET.FIRST;
236
237 --------------------------------------------------------------------
238 l_debug_info := 'Applying service charge';
239 --------------------------------------------------------------------
240 --Bug 3886652 - Customer and Customer Site added as params to ARI_CONFIG.get_service_charge_activity_id
241 WHILE L_COUNT IS NOT NULL
242 LOOP
243 -- Call the AR_ADJUST api to adjust the invoice.
244 L_ADJ_REC.PAYMENT_SCHEDULE_ID := P_INVOICE_SET(L_COUNT).PAYMENT_SCHEDULE_ID;
245 L_CUSTOMER_ID := P_INVOICE_SET(L_COUNT).CUSTOMER_ID;
246 L_CUSTOMER_SITE_USE_ID := P_INVOICE_SET(L_COUNT).CUSTOMER_SITE_USE_ID;
247 L_ADJ_REC.RECEIVABLES_TRX_ID := ARI_UTILITIES.get_service_charge_activity_id(L_CUSTOMER_ID, L_CUSTOMER_SITE_USE_ID);
248 L_ADJ_REC.AMOUNT := P_INVOICE_SET(L_COUNT).SERVICE_CHARGE;
249 L_ADJ_REC.APPLY_DATE := sysdate;
250 L_ADJ_REC.GL_DATE := sysdate;
251 L_ADJ_REC.CREATED_FROM := 'ARI_ADJ_INVOICE';
252
253 -- Call the adjustment API Wrapper
254 L_RETURN_VALUE :=ARI_SERVICE_CHARGE_PKG.ADJUST_INVOICE(L_ADJ_REC);
255
256 IF L_RETURN_VALUE <> FND_API.G_RET_STS_SUCCESS THEN
257 L_RETURN_VALUE := FND_API.G_RET_STS_ERROR;
258 IF (PG_DEBUG = 'Y') THEN
259 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
260 arp_standard.debug('ERROR =>'|| SQLERRM);
261 END IF;
262 EXIT;
263 END IF;
264
265 L_COUNT := P_INVOICE_SET.NEXT(L_COUNT);
266
267 END LOOP;
268
269 RETURN L_RETURN_VALUE;
270 EXCEPTION
271 WHEN OTHERS THEN
272 IF (SQLCODE <> -20001) THEN
273 IF (PG_DEBUG = 'Y') THEN
274 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
275 arp_standard.debug('ERROR =>'|| SQLERRM);
276 END IF;
277 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
278 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
279 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
280 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
281 FND_MSG_PUB.ADD;
282 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
283 END IF;
284
285 END APPLY_CHARGE;
286
287
288 END ARI_SERVICE_CHARGE_PKG;