1 Package Body AR_CM_APP_VAL_PVT AS
2 /* $Header: ARXCMAVB.pls 120.5.12010000.2 2008/11/11 13:28:35 npanchak ship $ */
3 --Validation procedures are contained in this package
4
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
9 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
10 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
11
12 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
13
14 PROCEDURE Validate_apply_gl_date(p_apply_gl_date IN DATE,
15 p_trx_gl_date IN DATE,
16 p_return_status OUT NOCOPY VARCHAR2
17 ) IS
18 l_bool BOOLEAN;
19
20 BEGIN
21 IF PG_DEBUG in ('Y', 'C') THEN
22 arp_util.debug('Validate_apply_gl_date ()+');
23 END IF;
24 p_return_status := FND_API.G_RET_STS_SUCCESS;
25
26 IF p_apply_gl_date IS NOT NULL THEN
27
28 -- Check that the application GL Date is not before the invoice GL Date.
29 IF p_apply_gl_date < p_trx_gl_date THEN
30 FND_MESSAGE.SET_NAME('AR','AR_VAL_GL_INV_GL');
31 FND_MSG_PUB.Add;
32 p_return_status := FND_API.G_RET_STS_ERROR;
33 END IF;
34
35 -- Check that the Application GL Date is in an open or future GL period.
36 IF ( NOT arp_util.is_gl_date_valid( p_apply_gl_date )) THEN
37 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
38 FND_MESSAGE.set_token( 'GL_DATE', TO_CHAR( p_apply_gl_date ));
39 FND_MSG_PUB.Add;
40 p_return_status := FND_API.G_RET_STS_ERROR;
41 END IF;
42
43 END IF;
44 IF PG_DEBUG in ('Y', 'C') THEN
45 arp_util.debug('Validate_apply_gl_date ()-');
46 END IF;
47
48 END Validate_apply_gl_date;
49
50 PROCEDURE Validate_apply_date(p_apply_date IN DATE,
51 p_trx_date IN DATE,
52 p_return_status OUT NOCOPY VARCHAR2
53 ) IS
54
55 BEGIN
56 IF PG_DEBUG in ('Y', 'C') THEN
57 arp_util.debug('Validate_apply_date ()+');
58 END IF;
59 p_return_status := FND_API.G_RET_STS_SUCCESS;
60
61 IF p_apply_date IS NOT NULL THEN
62
63 -- check that the apply date is not before the invoice date.
64 IF p_apply_date < p_trx_date THEN
65 FND_MESSAGE.SET_NAME('AR','AR_APPLY_BEFORE_TRANSACTION');
66 FND_MSG_PUB.Add;
67 p_return_status := FND_API.G_RET_STS_ERROR;
68 END IF;
69
70 END IF;
71
72 IF PG_DEBUG in ('Y', 'C') THEN
73 arp_util.debug('Validate_apply_date ()-');
74 END IF;
75 END Validate_apply_date;
76
77 PROCEDURE Validate_amount_applied_from(
78 p_receivable_application_id IN NUMBER,
79 p_cm_unapp_amount IN NUMBER,
80 p_return_status OUT NOCOPY VARCHAR2
81 ) IS
82 l_amount_applied NUMBER;
83 l_amount_applied_from NUMBER;
84 l_remaining_unapp_cm_amt NUMBER;
85 BEGIN
86 IF PG_DEBUG in ('Y', 'C') THEN
87 arp_util.debug('Validate_amount_applied_from ()+');
88 END IF;
89 p_return_status := FND_API.G_RET_STS_SUCCESS;
90
91 SELECT amount_applied,
92 amount_applied_from INTO l_amount_applied,l_amount_applied_from
93 FROM ar_receivable_applications
94 WHERE receivable_application_id = p_receivable_application_id;
95
96 l_remaining_unapp_cm_amt := p_cm_unapp_amount + nvl(l_amount_applied_from, l_amount_applied);
97
98 IF l_remaining_unapp_cm_amt < 0 THEN
99 IF PG_DEBUG in ('Y', 'C') THEN
100 arp_util.debug('Validate_amount_applied_from: ' || 'l_remaining_unapp_cm_amt :'||to_char(l_remaining_unapp_cm_amt));
101 END IF;
102 p_return_status := FND_API.G_RET_STS_ERROR;
103 FND_MESSAGE.SET_NAME('AR','AR_CKAP_OVERAPP');
104 FND_MSG_PUB.Add;
105 END IF;
106 IF PG_DEBUG in ('Y', 'C') THEN
107 arp_util.debug('Validate_amount_applied_from ()-');
108 END IF;
109 END Validate_amount_applied_from;
110
111
112 PROCEDURE Validate_Rev_gl_date(p_reversal_gl_date IN DATE,
113 p_apply_gl_date IN DATE,
114 p_cm_gl_date IN DATE,
115 p_return_status OUT NOCOPY VARCHAR2
116 ) IS
117
118 BEGIN
119 IF PG_DEBUG in ('Y', 'C') THEN
120 arp_util.debug('Validate_Rev_gl_date ()+');
121 END IF;
122 p_return_status := FND_API.G_RET_STS_SUCCESS;
123 IF p_reversal_gl_date IS NOT NULL THEN
124
125 IF p_reversal_gl_date < NVL(p_apply_gl_date,p_reversal_gl_date) THEN
126 FND_MESSAGE.SET_NAME('AR','AR_RW_BEFORE_APP_GL_DATE');
127 FND_MESSAGE.SET_TOKEN('GL_DATE', p_apply_gl_date);
128 FND_MSG_PUB.Add;
129 p_return_status := FND_API.G_RET_STS_ERROR;
130 END IF;
131 IF p_reversal_gl_date < nvl(p_cm_gl_date,p_reversal_gl_date) THEN
132 FND_MESSAGE.SET_NAME('AR','AR_REF_BEFORE_CM_GL_DATE');
133 FND_MESSAGE.SET_TOKEN('GL_DATE', p_cm_gl_date);
134 FND_MSG_PUB.Add;
135 p_return_status := FND_API.G_RET_STS_ERROR;
136 END IF;
137
138 IF ( NOT arp_util.is_gl_date_valid(p_reversal_gl_date)) THEN
139 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
140 FND_MESSAGE.set_token( 'GL_DATE', TO_CHAR( p_reversal_gl_date ));
141 FND_MSG_PUB.Add;
142 p_return_status := FND_API.G_RET_STS_ERROR;
143 END IF;
144
145 ELSE
146 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REV_GL_DATE_NULL');
147 FND_MSG_PUB.Add;
148 p_return_status := FND_API.G_RET_STS_ERROR;
149 IF PG_DEBUG in ('Y', 'C') THEN
150 arp_util.debug('Validate_Rev_gl_date: ' || 'The Reversal gl date is null ');
151 END IF;
152 END IF;
153
154 IF PG_DEBUG in ('Y', 'C') THEN
155 arp_util.debug('Validate_Rev_gl_date ()-');
156 END IF;
157 EXCEPTION
158 WHEN others THEN
159 IF PG_DEBUG in ('Y', 'C') THEN
160 arp_util.debug('EXCEPTION: Validate_rev_gl_date() ');
161 END IF;
162 raise;
163 END Validate_Rev_gl_date;
164
165 PROCEDURE Validate_receivable_appln_id(
166 p_receivable_application_id IN NUMBER,
167 p_application_type IN VARCHAR2,
168 p_return_status OUT NOCOPY VARCHAR2) IS
169 l_valid NUMBER;
170 BEGIN
171 p_return_status := FND_API.G_RET_STS_SUCCESS;
172 IF PG_DEBUG in ('Y', 'C') THEN
173 arp_util.debug('Validate_receivable_appln_id ()+');
174 END IF;
175 IF p_receivable_application_id IS NOT NULL
176 THEN
177 SELECT count(*)
178 INTO l_valid
179 FROM AR_RECEIVABLE_APPLICATIONS ra
180 WHERE ra.receivable_application_id = p_receivable_application_id
181 and ra.display = 'Y'
182 and ra.status = p_application_type
183 and ra.application_type = 'CM';
184
185 IF l_valid = 0 THEN
186 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_INVALID');
187 FND_MSG_PUB.Add;
188 p_return_status := FND_API.G_RET_STS_ERROR;
189 END IF;
190
191 ELSIF p_receivable_application_id IS NULL THEN
192 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_NULL');
193 FND_MSG_PUB.Add;
194 p_return_status := FND_API.G_RET_STS_ERROR;
195
196 END IF;
197 IF PG_DEBUG in ('Y', 'C') THEN
198 arp_util.debug('Validate_receivable_appln_id ()-');
199 END IF;
200 EXCEPTION
201 WHEN others THEN
202 IF PG_DEBUG in ('Y', 'C') THEN
203 arp_util.debug('EXCEPTION: Validate_receivable_appln_id()');
204 END IF;
205 raise;
206 END Validate_receivable_appln_id;
207
208 PROCEDURE validate_activity(p_receivables_trx_id IN NUMBER,
209 p_customer_trx_id IN NUMBER,
210 p_applied_ps_id IN NUMBER,
211 p_amount_applied IN NUMBER,
212 p_currency_code IN VARCHAR2,
213 p_chk_approval_limit_flag IN VARCHAR2,
214 p_return_status IN OUT NOCOPY VARCHAR2
215 ) IS
216 l_activity_type VARCHAR2(30);
217 l_amount_from NUMBER;
218 l_amount_to NUMBER;
219 l_user_id NUMBER;
220 l_existing_cmref_amount NUMBER;
221 l_tot_cmref_amt NUMBER;
222
223 cursor activity_type is
224 select type
225 from ar_receivables_trx rt
226 where receivables_trx_id = p_receivables_trx_id;
227
228 BEGIN
229
230 IF PG_DEBUG in ('Y', 'C') THEN
231 arp_util.debug('validate_activity (+)');
232 END IF;
233
234 OPEN activity_type;
235 FETCH activity_type INTO l_activity_type;
236 IF activity_type%NOTFOUND THEN
237 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_TRX_ID_INVALID');
238 FND_MSG_PUB.Add;
239 p_return_status := FND_API.G_RET_STS_ERROR;
240 END IF;
241 CLOSE activity_type;
242
243 IF l_activity_type IS NOT NULL THEN
244 --Validate applied ps_id
245 IF p_applied_ps_id = -8 THEN
246 --6865230
247 IF l_activity_type <> 'CM_REFUND' THEN
248 FND_MESSAGE.SET_NAME('AR','AR_RAPI_ACTIVITY_X_INVALID');
249 FND_MSG_PUB.Add;
250 p_return_status := FND_API.G_RET_STS_ERROR;
251 END IF;
252
253 l_user_id := to_number(fnd_profile.value('USER_ID'));
254
255 --get the existing refund amount on the CM.
256
257 BEGIN
258 SELECT sum(amount_applied)
259 INTO l_existing_cmref_amount
260 FROM ar_receivable_applications
261 WHERE applied_payment_schedule_id = -8
262 AND status = 'ACTIVITY'
263 AND NVL(confirmed_flag,'Y') = 'Y'
264 AND customer_trx_id = p_customer_trx_id;
265
266 l_tot_cmref_amt := NVL(l_existing_cmref_amount,0) + NVL(p_amount_applied,0);
267
268 EXCEPTION
269 WHEN no_data_found THEN
270 l_tot_cmref_amt := p_amount_applied;
271 END;
272
273 IF NVL(p_chk_approval_limit_flag,'Y') <> 'N' THEN
274 BEGIN
275 SELECT NVL(amount_from,0),
276 NVL(amount_to,0)
277 INTO l_amount_from,
278 l_amount_to
279 FROM ar_approval_user_limits
280 where currency_code = p_currency_code
281 and user_id = l_user_id
282 and document_type ='CMREF';
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN
285 l_amount_from := NVL(l_tot_cmref_amt,0);
286 l_amount_to := NVL(l_tot_cmref_amt,0);
287 END;
288
289 IF (NVL(l_tot_cmref_amt,0) > l_amount_to) OR
290 (NVL(l_tot_cmref_amt,l_amount_from) < l_amount_from)
291 THEN
292 fnd_message.set_name ('AR','AR_REF_USR_LMT_OUT_OF_RANGE');
293 fnd_message.set_token('FROM_AMOUNT', to_char(l_amount_from), FALSE);
294 fnd_message.set_token('TO_AMOUNT', to_char(l_amount_to), FALSE);
295 FND_MSG_PUB.Add;
296 p_return_status := FND_API.G_RET_STS_ERROR;
297 END IF;
298
299 END IF;
300
301 ELSE
302 --the applied payment schedule id is invalid
303 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APP_PS_ID_INVALID');
304 FND_MSG_PUB.Add;
305 p_return_status := FND_API.G_RET_STS_ERROR;
306 END IF; --additional control structures to be added for new activity types.
307 END IF;
308 IF PG_DEBUG in ('Y', 'C') THEN
309 arp_util.debug('validate_activity (-)');
310 END IF;
311 END validate_activity;
312
313 PROCEDURE validate_activity_app( p_receivables_trx_id IN NUMBER,
314 p_applied_ps_id IN NUMBER,
315 p_customer_trx_id IN NUMBER,
316 p_cm_gl_date IN DATE,
317 p_cm_unapp_amount IN NUMBER,
318 p_trx_date IN DATE,
319 p_amount_applied IN NUMBER,
320 p_apply_gl_date IN DATE,
321 p_apply_date IN DATE,
322 p_cm_currency_code IN VARCHAR2,
323 p_return_status OUT NOCOPY VARCHAR2,
324 p_chk_approval_limit_flag IN VARCHAR2,
325 p_called_from IN VARCHAR2
326 ) IS
327 l_valid VARCHAR2(1) DEFAULT 'N';
328 l_cm_return_status VARCHAR2(1);
329 l_act_return_status VARCHAR2(1);
330 l_amt_return_status VARCHAR2(1);
331 l_gl_date_return_status VARCHAR2(1);
332 l_apply_date_return_status VARCHAR2(1);
333 BEGIN
334 IF PG_DEBUG in ('Y', 'C') THEN
335 arp_util.debug('validate_activity_app ()+');
336 END IF;
337 p_return_status := FND_API.G_RET_STS_SUCCESS;
338
339 l_act_return_status := FND_API.G_RET_STS_SUCCESS;
340 l_amt_return_status := FND_API.G_RET_STS_SUCCESS;
341 l_cm_return_status := FND_API.G_RET_STS_SUCCESS;
342 l_gl_date_return_status := FND_API.G_RET_STS_SUCCESS;
343 l_apply_date_return_status := FND_API.G_RET_STS_SUCCESS;
344
345 validate_activity(
346 p_receivables_trx_id,
347 p_customer_trx_id,
348 p_applied_ps_id,
349 p_amount_applied,
350 p_cm_currency_code,
351 p_chk_approval_limit_flag,
352 l_act_return_status
353 );
354 -- if this routine is called for cmrefund,this routine will check whether
355 -- the credit memo is suitable for refund
356
357 validate_credit_memo(FND_API.G_FALSE,
358 p_customer_trx_id,
359 l_cm_return_status);
360
361 validate_apply_date (p_apply_date,
362 p_trx_date,
363 l_apply_date_return_status
364 );
365
366 -- validate amount applied
367 IF p_amount_applied IS NULL THEN
368 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APPLIED_AMT_NULL');
369 FND_MSG_PUB.Add;
370 l_amt_return_status := FND_API.G_RET_STS_ERROR;
371
372 ELSIF (p_amount_applied < 0 AND NVL(p_applied_ps_id,-8) = -8) THEN
373 FND_MESSAGE.SET_NAME('AR','AR_REF_CM_APP_NEG');
374 FND_MSG_PUB.Add;
375 l_amt_return_status := FND_API.G_RET_STS_ERROR;
376 -- Bug 2897244 - amount not checked if called from form
377 ELSIF ((nvl(p_cm_unapp_amount,0)- p_amount_applied) < 0 AND
378 NVL(p_called_from,'RAPI') <> 'ARXRWAPP') THEN
379 FND_MESSAGE.SET_NAME('AR','AR_CKAP_OVERAPP');
380 FND_MSG_PUB.Add;
381 l_amt_return_status := FND_API.G_RET_STS_ERROR;
382 END IF;
383
384 validate_apply_gl_date(
385 p_apply_gl_date,
386 p_cm_gl_date,
387 l_gl_date_return_status
388 );
389
390 IF l_cm_return_status <> FND_API.G_RET_STS_SUCCESS OR
391 l_amt_return_status <> FND_API.G_RET_STS_SUCCESS OR
395 THEN
392 l_gl_date_return_status <> FND_API.G_RET_STS_SUCCESS OR
393 l_apply_date_return_status <> FND_API.G_RET_STS_SUCCESS OR
394 l_act_return_status <> FND_API.G_RET_STS_SUCCESS
396 p_return_status := FND_API.G_RET_STS_ERROR;
397 END IF;
398
399 IF PG_DEBUG in ('Y', 'C') THEN
400 arp_util.debug('validate_activity_app ()-');
401 END IF;
402 EXCEPTION
403 WHEN others THEN
404 IF PG_DEBUG in ('Y', 'C') THEN
405 arp_util.debug('EXCEPTION: validate_activity_app()');
406 END IF;
407 raise;
408 END validate_activity_app;
409
410
411 PROCEDURE validate_unapp_activity(
412 p_trx_gl_date IN DATE,
413 p_receivable_application_id IN NUMBER,
414 p_reversal_gl_date IN DATE,
415 p_apply_gl_date IN DATE,
416 p_cm_unapp_amt IN NUMBER,
417 p_return_status OUT NOCOPY VARCHAR2
418 ) IS
419 l_amt_app_from_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
420 BEGIN
421 p_return_status := FND_API.G_RET_STS_SUCCESS;
422 IF PG_DEBUG in ('Y', 'C') THEN
423 arp_util.debug('validate_unapp_activity ()+');
424 END IF;
425
426 Validate_rev_gl_date( p_reversal_gl_date ,
427 p_apply_gl_date ,
428 p_trx_gl_date,
429 p_return_status
430 );
431
432 IF p_receivable_application_id IS NOT NULL
433 AND p_cm_unapp_amt IS NOT NULL THEN
434 Validate_amount_applied_from( p_receivable_application_id,
435 p_cm_unapp_amt,
436 l_amt_app_from_return_status);
437 END IF;
438 IF l_amt_app_from_return_status <> FND_API.G_RET_STS_SUCCESS OR
439 p_return_status <> FND_API.G_RET_STS_SUCCESS THEN
440 p_return_status := FND_API.G_RET_STS_ERROR;
441 END IF;
442
443 IF PG_DEBUG in ('Y', 'C') THEN
444 arp_util.debug('validate_unapp_activity: ' || 'p_return_status :'||p_return_status);
445 arp_util.debug('validate_unapp_activity ()-');
446 END IF;
447 END validate_unapp_activity;
448
449
450 PROCEDURE validate_credit_memo (
451 p_init_msg_list IN VARCHAR2,
452 p_customer_trx_id IN NUMBER,
453 p_return_status OUT NOCOPY VARCHAR2) IS
454
455 CURSOR c_cm IS
456 SELECT ct.receipt_method_id,
457 ct.customer_bank_account_id,
458 ct.previous_customer_trx_id,
459 ct.complete_flag,
460 rc.remit_flag,
461 rma.remittance_ccid
462 FROM ra_customer_trx ct,
463 ar_receipt_methods rm,
464 ar_receipt_classes rc,
465 ar_receipt_method_accounts rma
466 WHERE ct.customer_trx_id = p_customer_trx_id
467 AND ct.receipt_method_id = rm.receipt_method_id(+)
468 AND rm.receipt_class_id = rc.receipt_class_id(+)
469 AND rm.receipt_method_id = rma.receipt_method_id(+)
470 AND ROWNUM = 1;
471
472 CURSOR c_ps IS
473 SELECT SUM(amount_due_original), SUM(amount_due_remaining)
474 FROM ar_payment_schedules
475 WHERE customer_trx_id = p_customer_trx_id;
476
477 l_receipt_method_id NUMBER;
478 l_customer_bank_account_id NUMBER;
479 l_previous_customer_trx_id NUMBER;
480 l_complete_flag ra_customer_trx.complete_flag%TYPE;
481 l_remit_flag ar_receipt_classes.remit_flag%TYPE;
482 l_remittance_ccid NUMBER;
483 l_amount_due_original NUMBER;
484 l_amount_due_remaining NUMBER;
485 BEGIN
486 IF PG_DEBUG in ('Y', 'C') THEN
487 arp_util.debug('validate_credit_memo (+)');
488 END IF;
489 /*--------------------------------------------------------------+
490 | Initialize message list if p_init_msg_list is set to TRUE |
491 +--------------------------------------------------------------*/
492
493 IF FND_API.to_Boolean( p_init_msg_list )
494 THEN
495 FND_MSG_PUB.initialize;
496 END IF;
497 /*-----------------------------------------+
498 | Initialize return status to SUCCESS |
499 +-----------------------------------------*/
500
501 p_return_status := FND_API.G_RET_STS_SUCCESS;
502
503 OPEN c_cm;
504 FETCH c_cm INTO l_receipt_method_id,
505 l_customer_bank_account_id,
506 l_previous_customer_trx_id,
507 l_complete_flag,
508 l_remit_flag,
509 l_remittance_ccid;
510 CLOSE c_cm;
511 IF l_previous_customer_trx_id IS NOT NULL THEN
512 FND_MESSAGE.SET_NAME('AR','AR_REF_NOT_OACM');
513 FND_MSG_PUB.Add;
514 p_return_status := FND_API.G_RET_STS_ERROR;
515 END IF;
516 IF NVL(l_complete_flag,'N') <> 'Y' THEN
517 FND_MESSAGE.SET_NAME('AR','AR_REF_CM_INCOMPLETE');
518 FND_MSG_PUB.Add;
519 p_return_status := FND_API.G_RET_STS_ERROR;
520 ELSE
521 OPEN c_ps;
522 FETCH c_ps INTO l_amount_due_original, l_amount_due_remaining;
523 CLOSE c_ps;
524 /* Bug 4203308 - checks the original amount for positive CM condition
528 FND_MSG_PUB.Add;
525 instead of amount due which may be positive due to overapplication */
526 IF l_amount_due_original > 0 THEN
527 FND_MESSAGE.SET_NAME('AR','AR_REF_CM_POSITIVE');
529 p_return_status := FND_API.G_RET_STS_ERROR;
530 END IF;
531 IF l_amount_due_remaining >= 0 THEN
532 FND_MESSAGE.SET_NAME('AR','AR_REF_MORE_THAN_CM_AMT');
533 FND_MSG_PUB.Add;
534 p_return_status := FND_API.G_RET_STS_ERROR;
535 END IF;
536 END IF;
537 IF PG_DEBUG in ('Y', 'C') THEN
538 arp_util.debug('validate_credit_memo (-)');
539 END IF;
540 EXCEPTION
541 WHEN OTHERS THEN
542 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_INVALID');
543 FND_MSG_PUB.Add;
544 p_return_status := FND_API.G_RET_STS_ERROR;
545
546 END validate_credit_memo;
547
548 END AR_CM_APP_VAL_PVT;
[Home] [Help]