4 /*=======================================================================+
1 PACKAGE BODY ARI_SERVICE_CHARGE_PKG AS
2 /* $Header: ARISCRGB.pls 120.5.12020000.2 2012/07/22 13:27:20 rsinthre ship $ */
3
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 |
32
29 *=======================================================================*/
30 FUNCTION ADJUST_INVOICE (P_ADJUSTMENT_REC_TYPE IN ARI_SERVICE_CHARGE_PKG.ADJUSTMENT_REC_TYPE)
31 RETURN VARCHAR2
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 P_PAYMENT_TYPE IN varchar2 DEFAULT NULL, P_LOOKUP_CODE IN varchar2 DEFAULT NULL)
134 IS
135
136 L_PROCEDURE_NAME VARCHAR2(30);
137 L_COUNT NUMBER ;
138 L_TOTAL_COUNT NUMBER;
139 L_SERVICE_CHARGE_AMOUNT NUMBER;
140 L_TOTAL_SERVICE_CHARGE NUMBER;
141 l_debug_info VARCHAR2(200);
142 L_SERVICE_CHRG_PRCNT NUMBER;
143 L_LOOKUP_TYPE VARCHAR2(200);
144 BEGIN
145
146 --Assign default values
147 L_PROCEDURE_NAME := '.COMPUTE_SERVICE_CHARGE';
148 L_SERVICE_CHARGE_AMOUNT := 0;
149 L_TOTAL_SERVICE_CHARGE := 0;
150
151 /*
152 This procedure can be customized by the customer.
153 Service charge can be added by the amount of the invoice or by the percentage
154 of the invoice amount or the flat amount or the layered rate.
155 --
156 1) CALCULATE THE ONE(FLAT CHARGE) CHARGE FOR THE SET OF INVOICES - IF WE DO
157 THAT FOR WHICH INVOICE WE UPDATE THE CHARGE IN THE GLOBAL TABLE AND ALSO
158 FOR WHICH INVOICE WE ADD THE AMOUNT.
159 2) IF THE SERVICE CHARGE IS CALCULATED FOR EVERY INVOICE,UPDATE the GLOBAL
160 TABLE WITH THE SERVICE CHARGE
161 */
165
162 --------------------------------------------------------------------
163 l_debug_info := 'Calculating service charge';
164 --------------------------------------------------------------------
166 -- Here is an example of a pro-rated service charge;
167 -- Flat rate service charge:
168 L_TOTAL_SERVICE_CHARGE := P_INVOICE_SET.COUNT * 2;
169
170 -- Prorate the service charge for the invoices
171 L_TOTAL_COUNT := P_INVOICE_SET.COUNT;
172 L_SERVICE_CHARGE_AMOUNT := L_TOTAL_SERVICE_CHARGE/L_TOTAL_COUNT;
173
174 -- Since we do not know how the table may have been indexed
175 -- we shall play it safe by using NEXT to traverse the table
176 L_COUNT := P_INVOICE_SET.FIRST;
177
178 -- CUSTOMIZED % BASED CODE
179 L_TOTAL_SERVICE_CHARGE := 0;
180
181 if P_PAYMENT_TYPE = 'CREDIT_CARD' then
182 L_SERVICE_CHRG_PRCNT := 0;
183 L_LOOKUP_TYPE := 'AR_CREDIT_CARD_SURCHARGE';
184 else
185 L_SERVICE_CHRG_PRCNT := 0.05;
186 L_LOOKUP_TYPE := null;
187 end if;
188
189 -- fnd_lookup_values, ar_lookups
190 IF P_LOOKUP_CODE IS NOT NULL THEN
191 BEGIN
192 SELECT ATTRIBUTE1 INTO L_SERVICE_CHRG_PRCNT from ar_lookups where lookup_type = upper(L_LOOKUP_TYPE) AND lookup_code = upper(P_LOOKUP_CODE)
193 AND ENABLED_FLAG='Y' and sysdate between start_date_active and nvl(end_date_active,sysdate);
194 EXCEPTION
195 WHEN OTHERS THEN
196 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
197 arp_standard.debug('Could not find Service Charge Lookup Value in ' || G_PKG_NAME || l_procedure_name);
198 arp_standard.debug('Service Charge lookup_type : ' || P_PAYMENT_TYPE);
199 arp_standard.debug('Service Charge lookup_code : ' || p_lookup_code);
200 END;
201 END IF;
202
203 --if no value is defined for credit card type in lookup then setting default values 0.05
204 IF L_SERVICE_CHRG_PRCNT is null or L_SERVICE_CHRG_PRCNT ='' then
205 L_SERVICE_CHRG_PRCNT := 0.05;
206 END IF;
207
208 WHILE L_COUNT IS NOT NULL
209 LOOP
210 L_SERVICE_CHARGE_AMOUNT := P_INVOICE_SET(L_COUNT).PAYMENT_AMOUNT * L_SERVICE_CHRG_PRCNT/100;
211
212 P_INVOICE_SET(L_COUNT).SERVICE_CHARGE := L_SERVICE_CHARGE_AMOUNT;
213
214 L_TOTAL_SERVICE_CHARGE := L_TOTAL_SERVICE_CHARGE + L_SERVICE_CHARGE_AMOUNT;
215
216 L_COUNT := P_INVOICE_SET.NEXT(L_COUNT);
217 END LOOP;
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 IF (PG_DEBUG = 'Y') THEN
222 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
223 arp_standard.debug('ERROR =>'|| SQLERRM);
224 END IF;
225 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
226 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
227 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
228 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
229 FND_MSG_PUB.ADD;
230
231 END COMPUTE_SERVICE_CHARGE;
232
233
234 /* =======================================================================
235 | PROCEDURE Apply Service Charge
236 |
237 | DESCRIPTION iReceivables PROCEDURE TO APPLY THE SERVICE CHARGE -
238 |
239 |
240 | PARAMETERS
241 |
242 * ======================================================================*/
243 FUNCTION APPLY_CHARGE(P_INVOICE_SET IN ARI_SERVICE_CHARGE_PKG.INVOICE_LIST_TABTYPE)
244 RETURN VARCHAR2
245 IS
246
247 L_RETURN_VALUE VARCHAR2(1) ;
248 L_PROCEDURE_NAME VARCHAR2(30);
249
250 L_ADJ_REC ARI_SERVICE_CHARGE_PKG.ADJUSTMENT_REC_TYPE;
251 L_COUNT NUMBER;
252 L_TOTAL_INVOICES NUMBER;
253 l_debug_info VARCHAR2(200);
254 L_CUSTOMER_ID NUMBER(15);
255 L_CUSTOMER_SITE_USE_ID NUMBER(15);
256
257 BEGIN
258 --Assign default values
259 L_RETURN_VALUE := NULL;
260 L_PROCEDURE_NAME := '.APPLY_CHARGE';
261
262 --fnd_log_repository.init;
263 -- Since we do not know how the table may have been indexed
264 -- we shall play it safe by using NEXT to traverse the table
265 L_COUNT := P_INVOICE_SET.FIRST;
266
267 --------------------------------------------------------------------
268 l_debug_info := 'Applying service charge';
269 --------------------------------------------------------------------
270 --Bug 3886652 - Customer and Customer Site added as params to ARI_CONFIG.get_service_charge_activity_id
271 WHILE L_COUNT IS NOT NULL
272 LOOP
273 -- Call the AR_ADJUST api to adjust the invoice.
274 L_ADJ_REC.PAYMENT_SCHEDULE_ID := P_INVOICE_SET(L_COUNT).PAYMENT_SCHEDULE_ID;
275 L_CUSTOMER_ID := P_INVOICE_SET(L_COUNT).CUSTOMER_ID;
276 L_CUSTOMER_SITE_USE_ID := P_INVOICE_SET(L_COUNT).CUSTOMER_SITE_USE_ID;
277 L_ADJ_REC.RECEIVABLES_TRX_ID := ARI_UTILITIES.get_service_charge_activity_id(L_CUSTOMER_ID, L_CUSTOMER_SITE_USE_ID);
278 L_ADJ_REC.AMOUNT := P_INVOICE_SET(L_COUNT).SERVICE_CHARGE;
279 L_ADJ_REC.APPLY_DATE := P_INVOICE_SET(L_COUNT).APPLY_DATE;
280 L_ADJ_REC.GL_DATE := P_INVOICE_SET(L_COUNT).GL_DATE;
281 L_ADJ_REC.CREATED_FROM := 'ARI_ADJ_INVOICE';
282
283 -- Call the adjustment API Wrapper
284 L_RETURN_VALUE :=ARI_SERVICE_CHARGE_PKG.ADJUST_INVOICE(L_ADJ_REC);
285
286 IF L_RETURN_VALUE <> FND_API.G_RET_STS_SUCCESS THEN
287 L_RETURN_VALUE := FND_API.G_RET_STS_ERROR;
288 IF (PG_DEBUG = 'Y') THEN
289 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
290 arp_standard.debug('ERROR =>'|| SQLERRM);
291 END IF;
292 EXIT;
293 END IF;
294
295 L_COUNT := P_INVOICE_SET.NEXT(L_COUNT);
296
297 END LOOP;
298
299 RETURN L_RETURN_VALUE;
300 EXCEPTION
301 WHEN OTHERS THEN
302 IF (SQLCODE <> -20001) THEN
303 IF (PG_DEBUG = 'Y') THEN
304 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
305 arp_standard.debug('ERROR =>'|| SQLERRM);
306 END IF;
307 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
308 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
309 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
310 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
311 FND_MSG_PUB.ADD;
312 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
313 END IF;
314
315 END APPLY_CHARGE;
316
317
318 END ARI_SERVICE_CHARGE_PKG;
[Home] [Help]