1 PACKAGE BODY AR_CM_VAL_PVT AS
2 /* $Header: ARXVCMEB.pls 120.2.12020000.4 2013/03/06 06:03:02 kgnanase ship $ */
3
4 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
5 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
6 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
7
8 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
9 PG_PROFILE_APPLN_GL_DATE_DEF varchar2(30) := FND_PROFILE.value('AR_APPLICATION_GL_DATE_DEFAULT');
10
11 -- PRIVATE PROCEDURES/FUNCTIONS
12
13 PROCEDURE default_customer_trx_id(
14 p_customer_trx_id IN OUT NOCOPY NUMBER,
15 p_trx_number IN VARCHAR,
16 p_return_status OUT NOCOPY VARCHAR2
17 ) IS
18 BEGIN
19 IF PG_DEBUG in ('Y', 'C') THEN
20 arp_util.debug('Default_customer_trx_id ()+');
21 arp_util.debug(' Trx Number is '|| p_trx_number);
22 END IF;
23 p_return_status := FND_API.G_RET_STS_SUCCESS;
24 IF p_customer_trx_id IS NULL THEN
25 IF p_trx_number IS NOT NULL THEN
26 BEGIN
27 SELECT customer_trx_id
28 INTO p_customer_trx_id
29 FROM ra_customer_trx
30 WHERE trx_number = p_trx_number;
31 EXCEPTION
32 WHEN no_data_found THEN
33 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_NUM_INVALID');
34 FND_MSG_PUB.Add;
35 p_return_status := FND_API.G_RET_STS_ERROR ;
36 END;
37 END IF;
38
39 ELSE -- p_customer_trx_id IS NOT NULL
40
41 IF p_trx_number IS NOT NULL
42 THEN
43 --give a warning message to indicate that the trx number
44 --entered by the user has been ignored.
45 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
46 THEN
47 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_NUM_IGN');
48 FND_MSG_PUB.Add;
49 END IF;
50 END IF;
51 END IF;
52 IF PG_DEBUG in ('Y', 'C') THEN
53 arp_util.debug('Default_customer_trx_id ()-');
54 END IF;
55 EXCEPTION
56 WHEN others THEN
57 IF PG_DEBUG in ('Y', 'C') THEN
58 arp_util.debug('EXCEPTION: default_customer_trx_id()', G_MSG_UERROR);
59 END IF;
60 END default_customer_trx_id;
61
62 PROCEDURE default_customer_trx_line_id(
63 p_inv_customer_trx_id IN OUT NOCOPY NUMBER,
64 p_inv_customer_trx_line_id IN OUT NOCOPY NUMBER,
65 p_inv_line_number IN NUMBER,
66 p_return_status OUT NOCOPY VARCHAR2
67 ) IS
68 BEGIN
69 IF PG_DEBUG in ('Y', 'C') THEN
70 arp_util.debug('Default_customer_trx_line_id ()+');
71 END IF;
72 p_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 IF p_inv_customer_trx_line_id IS NOT NULL AND
75 p_inv_line_number IS NOT NULL
76 THEN
77 --give a warning message to indicate that the line number
78 --entered by the user has been ignored.
79 IF FND_MSG_PUB.Check_Msg_Level(G_MSG_SUCCESS)
80 THEN
81 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_LINE_NUM_IGN');
82 FND_MSG_PUB.Add;
83 END IF;
84 END IF;
85
86 IF p_inv_customer_trx_id IS NOT NULL THEN
87 IF p_inv_customer_trx_line_id IS NULL THEN
88 IF p_inv_line_number IS NOT NULL THEN
89 BEGIN
90 SELECT customer_trx_line_id
91 INTO p_inv_customer_trx_line_id
92 FROM ra_customer_trx_lines
93 WHERE customer_trx_id = p_inv_customer_trx_id
94 AND line_number = p_inv_line_number
95 AND line_type = 'LINE';
96 EXCEPTION
97 WHEN no_data_found THEN
98 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_LINE_NO_INVALID');
99 FND_MSG_PUB.Add;
100 p_return_status := FND_API.G_RET_STS_ERROR ;
101 END;
102 END IF;
103 END IF;
104 ELSE
105 IF p_inv_customer_trx_line_id IS NOT NULL THEN
106 BEGIN
107 SELECT customer_trx_id
108 INTO p_inv_customer_trx_id
109 FROM ra_customer_trx_lines
110 WHERE customer_trx_line_id = p_inv_customer_trx_line_id
111 AND line_type = 'LINE';
112 EXCEPTION
113 WHEN no_data_found THEN
114 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_LINE_ID_INVALID');
115 FND_MSG_PUB.Add;
116 p_return_status := FND_API.G_RET_STS_ERROR ;
117 END;
118 END IF;
119 END IF;
120 IF PG_DEBUG in ('Y', 'C') THEN
121 arp_util.debug('Default_customer_trx_line_id ()-');
122 END IF;
123 EXCEPTION
124 WHEN others THEN
125 IF PG_DEBUG in ('Y', 'C') THEN
126 arp_util.debug('EXCEPTION: Default_customer_trx_line_id()', G_MSG_UERROR);
127 END IF;
128 END default_customer_trx_line_id;
129
130
131 PROCEDURE default_cm_info(
132 p_cm_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
133 p_cm_gl_date OUT NOCOPY DATE,
134 p_cm_amount_rem OUT NOCOPY ar_payment_schedules.amount_due_remaining%TYPE,
135 p_cm_trx_date OUT NOCOPY DATE,
136 p_cm_ps_id OUT NOCOPY ar_payment_schedules.payment_schedule_id%TYPE ,
137 p_cm_currency_code OUT NOCOPY fnd_currencies.currency_code%TYPE,
138 p_cm_customer_id OUT NOCOPY ra_customer_trx.paying_customer_id%TYPE,
139 p_return_status OUT NOCOPY VARCHAR2
140 ) IS
141
142 BEGIN
143
144 p_return_status := FND_API.G_RET_STS_SUCCESS;
145
146 IF PG_DEBUG in ('Y', 'C') THEN
147 arp_util.debug('Default_CM_Info ()+');
148 END IF;
149
150 IF p_cm_customer_trx_id IS NOT NULL THEN
151 BEGIN
152
153 SELECT ps.payment_schedule_id,
154 cm.trx_date,
155 ps.gl_date,
156 ps.amount_due_remaining,
157 NVL(cm.paying_customer_id, cm.bill_to_customer_id),
158 cm.invoice_currency_code
159 INTO p_cm_ps_id,
160 p_cm_trx_date,
161 p_cm_gl_date,
162 p_cm_amount_rem,
163 p_cm_customer_id,
164 p_cm_currency_code
165 FROM ra_customer_trx cm,
166 ar_payment_schedules ps
167 WHERE ps.customer_trx_id = cm.customer_trx_id
168 AND cm.customer_trx_id = p_cm_customer_trx_id;
169
170 EXCEPTION
171 WHEN NO_DATA_FOUND THEN
172 FND_MESSAGE.SET_NAME( 'AR','AR_CMAPI_CM_TRX_ID_INVALID');
173 FND_MSG_PUB.ADD;
174 p_return_status := FND_API.G_RET_STS_ERROR;
175 WHEN OTHERS THEN
176 IF PG_DEBUG in ('Y', 'C') THEN
177 arp_util.debug('EXCEPTION: Default_CM_Info()');
178 END IF;
179 END;
180
181 ELSE
182 FND_MESSAGE.SET_NAME( 'AR','AR_CMAPI_CM_TRX_ID_NULL');
183 FND_MSG_PUB.ADD;
184 p_return_status := FND_API.G_RET_STS_ERROR ;
185 END IF;
186
187
188 IF PG_DEBUG in ('Y', 'C') THEN
189 arp_util.debug('Default_CM_Info ()-');
190 END IF;
191
192 END default_cm_info;
193
194
195
196 PROCEDURE default_trx_info(
197 p_inv_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
198 p_inv_customer_trx_line_id IN NUMBER,
199 p_show_closed_invoices IN VARCHAR2,
200 p_cm_gl_date IN DATE,
201 p_cm_customer_id IN ra_customer_trx.paying_customer_id%TYPE,
202 p_cm_currency_code IN fnd_currencies.currency_code%TYPE,
203 p_cm_ps_id IN NUMBER,
204 p_cm_trx_date IN DATE,
205 p_inv_customer_id OUT NOCOPY NUMBER, --customer on transaction
206 p_inv_cust_trx_type_id OUT NOCOPY ra_customer_trx.cust_trx_type_id%TYPE ,
207 p_inv_due_date OUT NOCOPY DATE,
208 p_inv_trx_date OUT NOCOPY DATE,
209 p_inv_gl_date OUT NOCOPY DATE,
210 p_allow_overappln_flag OUT NOCOPY VARCHAR2,
211 p_natural_appln_only_flag OUT NOCOPY VARCHAR2,
212 p_creation_sign OUT NOCOPY VARCHAR2,
213 p_applied_payment_schedule_id IN OUT NOCOPY NUMBER,
214 p_app_gl_date OUT NOCOPY DATE, --this is the application gl_date
215 p_installment IN OUT NOCOPY NUMBER,
216 p_inv_amount_rem OUT NOCOPY NUMBER,
217 p_inv_currency_code OUT NOCOPY VARCHAR,
218 p_return_status OUT NOCOPY VARCHAR2
219 )
220
221 IS
222
223 l_applied_payment_schedule_id NUMBER;
224
225 BEGIN
226
227 IF PG_DEBUG in ('Y', 'C') THEN
228 arp_util.debug('Default_Trx_Info ()+');
229 END IF;
230 p_return_status := FND_API.G_RET_STS_SUCCESS;
231
232 IF p_inv_customer_trx_id IS NOT NULL AND
233 p_installment IS NOT NULL THEN
234 IF arp_global.sysparam.pay_unrelated_invoices_flag = 'Y' THEN
235 SELECT
236 ot.customer_id ,
237 ot.cust_trx_type_id ,
238 ot.trx_due_date ,
239 ot.trx_date,
240 ot.trx_gl_date ,
241 ot.allow_overapplication_flag ,
242 ot.natural_application_only_flag ,
243 ot.creation_sign ,
244 ot.payment_schedule_id ,
245 greatest(p_cm_gl_date,ot.trx_gl_date,
246 decode(pg_profile_appln_gl_date_def,
247 'INV_REC_SYS_DT', sysdate, 'INV_REC_DT', ot.trx_gl_date,
248 ot.trx_gl_date)) gl_date,
249 ot.balance_due_functional,
250 ot.invoice_currency_code
251 INTO
252 p_inv_customer_id ,
253 p_inv_cust_trx_type_id ,
254 p_inv_due_date ,
255 p_inv_trx_date,
256 p_inv_gl_date ,
257 p_allow_overappln_flag ,
258 p_natural_appln_only_flag ,
259 p_creation_sign ,
260 l_applied_payment_schedule_id ,
261 p_app_gl_date, --this is the application gl_date
262 p_inv_amount_rem,
263 p_inv_currency_code
264 FROM
265 ar_open_trx_v ot
266 WHERE
267 ot.customer_trx_id = p_inv_customer_trx_id and
268 ot.invoice_currency_code = p_cm_currency_code and
269 ot.status=decode(p_show_closed_invoices,'Y',ot.status,'OP') and
270 ot.terms_sequence_number = p_installment;
271
272 ELSE
273 --This is the case where pay_unrelated_invoices_flag is 'N'
274 SELECT
275 ot.customer_id ,
276 ot.cust_trx_type_id ,
277 ot.trx_due_date ,
278 ot.trx_date,
279 ot.trx_gl_date ,
280 ot.allow_overapplication_flag ,
281 ot.natural_application_only_flag ,
282 ot.creation_sign ,
283 ot.payment_schedule_id ,
284 greatest(p_cm_gl_date,ot.trx_gl_date,
285 decode(pg_profile_appln_gl_date_def,
286 'INV_REC_SYS_DT', sysdate, 'INV_REC_DT', ot.trx_gl_date,
287 ot.trx_gl_date)) gl_date,
288 ot.balance_due_functional,
289 ot.invoice_currency_code
290 INTO
291 p_inv_customer_id ,
292 p_inv_cust_trx_type_id ,
293 p_inv_due_date ,
294 p_inv_trx_date,
295 p_inv_gl_date ,
296 p_allow_overappln_flag,
297 p_natural_appln_only_flag,
298 p_creation_sign,
299 l_applied_payment_schedule_id,
300 p_app_gl_date, --this is the defaulted application gl_date
301 p_inv_amount_rem,
302 p_inv_currency_code
303 FROM
304 ar_open_trx_v ot
305 WHERE
306 ot.customer_trx_id = p_inv_customer_trx_id and
307 ot.invoice_currency_code = p_cm_currency_code and
308 ot.status=decode(p_show_closed_invoices,'Y',ot.status,'OP') and
309 ot.terms_sequence_number = p_installment and
310 ot.customer_id IN (
311 SELECT rcr.related_cust_account_id
312 FROM hz_cust_acct_relate rcr
313 WHERE rcr.status='A' and
314 rcr.cust_account_id= p_cm_customer_id
315 and rcr.bill_to_flag = 'Y'
316 UNION
317 SELECT p_cm_customer_id
318 FROM dual
319 UNION
320 SELECT rel.related_cust_account_id
321 FROM ar_paying_relationships_v rel,
322 hz_cust_accounts acc
323 WHERE rel.party_id = acc.party_id
324 AND acc.cust_account_id = p_cm_customer_id
325 AND p_cm_trx_date BETWEEN effective_start_date
326 AND effective_end_date
327 );
328
329 END IF;
330
331
332
333 --If the defaulted payment_schedule_id does not match the
334 --applied_ps_id entered by the user, then raise error.
335 IF p_applied_payment_schedule_id IS NOT NULL THEN
336 IF l_applied_payment_schedule_id <>
337 p_applied_payment_schedule_id THEN
338 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_PS_ID_X_INVALID');
339 FND_MSG_PUB.Add;
340 p_return_status := FND_API.G_RET_STS_ERROR ;
341 END IF;
342 ELSE
343 p_applied_payment_schedule_id := l_applied_payment_schedule_id;
344 END IF;
345
346
347 ELSE --case when p_customer_trx_id is null
348 --no further validation done in the validation routines for customer_trx_id
349 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_NULL');
350 FND_MSG_PUB.Add;
351 p_return_status := FND_API.G_RET_STS_ERROR ;
352
353 END IF;
354
355 IF PG_DEBUG in ('Y', 'C') THEN
356 arp_util.debug('Default_Trx_Info: ' || 'p_applied_payment_schedule_id : '||to_char(p_applied_payment_schedule_id));
357 arp_util.debug('Default_Trx_Info: ' || 'p_allow_overappln_flag : '||p_allow_overappln_flag);
358 arp_util.debug('Default_Trx_Info: ' || 'p_natural_appln_only_flag : '||p_natural_appln_only_flag);
359 arp_util.debug('Default_Trx_Info: ' || 'p_creation_sign : '||p_creation_sign);
360 arp_util.debug('Default_Trx_Info ()-');
361 END IF;
362 EXCEPTION
363 WHEN no_data_found THEN
364 IF PG_DEBUG in ('Y', 'C') THEN
365 arp_util.debug('Default_Trx_Info : No data found ');
366 END IF;
367 WHEN others THEN
368 IF PG_DEBUG in ('Y', 'C') THEN
369 arp_util.debug('EXCEPTION: Default_Trx_Info()');
370 END IF;
371 raise;
372
373 END default_trx_info;
374
375 PROCEDURE default_amt_applied(
376 p_inv_currency_code IN fnd_currencies.currency_code%TYPE,
377 p_cm_currency_code IN fnd_currencies.currency_code%TYPE,
378 p_cm_amount_rem IN ar_payment_schedules.amount_due_remaining%TYPE,
379 p_inv_amount_rem IN ar_payment_schedules.amount_due_Remaining%TYPE,
380 p_amount_applied IN OUT NOCOPY NUMBER,
381 p_return_status OUT NOCOPY VARCHAR2
382 )
383
384 IS
385
386 BEGIN
387
388 IF PG_DEBUG in ('Y', 'C') THEN
389 arp_util.debug('Default_amt_applied ()+');
390 END IF;
391
392 p_return_status := FND_API.G_RET_STS_SUCCESS;
393
394 IF p_amount_applied IS NULL THEN
395 IF PG_DEBUG in ('Y', 'C') THEN
396 arp_util.debug('Default_amt_applied: ' || 'p_amount_applied is NULL ');
397 arp_util.debug('Invoice currency code: ' || p_inv_currency_code);
398 arp_util.debug('CM currency code: ' || p_cm_currency_code);
399 END IF;
400
401 IF p_inv_currency_code = p_cm_currency_code -- Same currency case
402 THEN
403
404 IF (sign(p_inv_amount_rem) <> sign(p_cm_amount_rem) ) THEN
405 IF abs(p_inv_amount_rem) > abs(p_cm_amount_rem) THEN
406 p_amount_applied := abs(p_cm_amount_rem)*sign(p_inv_amount_rem);
407 ELSE
408 p_amount_applied := p_inv_amount_rem;
409 END IF;
410 END IF;
411 p_amount_applied := arp_util.CurrRound(p_amount_applied,
412 p_inv_currency_code);
413 END IF;
414 IF PG_DEBUG in ('Y', 'C') THEN
415 arp_util.debug('Default_amt_applied: ' || p_amount_applied );
416 END IF;
417 END IF;
418
419 IF PG_DEBUG in ('Y', 'C') THEN
420 arp_util.debug('Default_amt_applied ()-');
421 END IF;
422
423 EXCEPTION
424 WHEN others THEN
425 IF PG_DEBUG in ('Y', 'C') THEN
426 arp_util.debug('Default_amt_applied: ' || 'EXCEPTION: Default_amt_applied()');
427 END IF;
428 raise;
429
430
431 END default_amt_applied;
432
433
434 PROCEDURE validate_apply_date(
435 p_apply_date IN DATE,
436 p_inv_trx_date IN DATE,
437 p_cm_trx_date IN DATE,
438 p_return_status OUT NOCOPY VARCHAR2
439 )
440 IS
441
442 BEGIN
443
444 IF PG_DEBUG in ('Y', 'C') THEN
445 arp_util.debug('Validate_apply_date ()+');
446 END IF;
447 p_return_status := FND_API.G_RET_STS_SUCCESS;
448
449 IF p_apply_date IS NOT NULL THEN
450
451 -- check that the apply date is not before the invoice date.
452 IF p_apply_date < p_inv_trx_date THEN
453 FND_MESSAGE.SET_NAME('AR','AR_APPLY_BEFORE_TRANSACTION');
454 FND_MSG_PUB.Add;
455 p_return_status := FND_API.G_RET_STS_ERROR;
456
457 -- check that the application date is not before the CM trx date.
458 END IF;
459
460 IF p_apply_date < p_cm_trx_date THEN
461 FND_MESSAGE.SET_NAME('AR','AR_APPLY_BEFORE_CM');
462 FND_MSG_PUB.Add;
463 p_return_status := FND_API.G_RET_STS_ERROR;
464 END IF;
465
466 END IF;
467
468 IF PG_DEBUG in ('Y', 'C') THEN
469 arp_util.debug('Validate_apply_date ()-');
470 END IF;
471
472
473 END validate_apply_date;
474
475 PROCEDURE validate_apply_gl_date(p_apply_gl_date IN DATE,
476 p_inv_gl_date IN DATE,
477 p_cm_gl_date IN DATE,
478 p_return_status OUT NOCOPY VARCHAR2
479 )
480
481 IS
482
483 BEGIN
484
485 IF PG_DEBUG in ('Y', 'C') THEN
486 arp_util.debug('Validate_apply_gl_date ()+');
487 END IF;
488
489 p_return_status := FND_API.G_RET_STS_SUCCESS;
490
491 IF p_apply_gl_date IS NOT NULL THEN
492
493 -- Check that the application GL Date is not before the invoice GL Date.
494 IF p_apply_gl_date < p_inv_gl_date THEN
495 FND_MESSAGE.SET_NAME('AR','AR_VAL_GL_INV_GL');
496 FND_MSG_PUB.Add;
497 p_return_status := FND_API.G_RET_STS_ERROR;
498
499 -- Check that the application GL Date is not before the CM GL Date.
500 END IF;
501 IF p_apply_gl_date < p_cm_gl_date THEN
502 FND_MESSAGE.SET_NAME('AR','AR_RW_GL_DATE_BEFORE_CM_GL');
503 FND_MSG_PUB.Add;
504 p_return_status := FND_API.G_RET_STS_ERROR;
505 END IF;
506
507
508 -- Check that the Application GL Date is in an open or future GL period.
509 IF ( NOT arp_util.is_gl_date_valid( p_apply_gl_date )) THEN
510 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
511 --Int'l Calendar Project
512 FND_MESSAGE.set_token( 'GL_DATE', fnd_date.date_to_chardate(p_apply_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
513 FND_MSG_PUB.Add;
514 p_return_status := FND_API.G_RET_STS_ERROR;
515 END IF;
516
517
518 END IF;
519
520 IF PG_DEBUG in ('Y', 'C') THEN
521 arp_util.debug('Validate_apply_gl_date ()-');
522 END IF;
523
524
525 END validate_apply_gl_date;
526
527 PROCEDURE validate_amount_applied(
528 p_amount_applied IN NUMBER,
529 p_applied_payment_schedule_id IN NUMBER,
530 p_customer_trx_line_id IN NUMBER,
531 p_inv_line_amount IN NUMBER,
532 p_creation_sign IN VARCHAR2 ,
533 p_allow_overappln_flag IN VARCHAR2,
534 p_natural_appln_only_flag IN VARCHAR2,
535 p_amount_due_remaining IN NUMBER,
536 p_return_status OUT NOCOPY VARCHAR2
537 )
538 IS
539
540 BEGIN
541
542 IF PG_DEBUG in ('Y', 'C') THEN
543 arp_util.debug('Validate_amount_applied ()+');
544 END IF;
545 p_return_status := FND_API.G_RET_STS_SUCCESS;
546
547
548 IF p_amount_applied IS NULL THEN
549 p_return_status := FND_API.G_RET_STS_ERROR;
550 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APPLIED_AMT_NULL');
551 FND_MSG_PUB.Add;
552 return;
553 END IF;
554
555
556 -- Should we check for natural application here???
557
558 IF PG_DEBUG in ('Y', 'C') THEN
559 arp_util.debug('Validate_amount_applied ()-');
560 END IF;
561
562 END validate_amount_applied;
563
564
565
566 FUNCTION Get_trx_ps_id(p_inv_customer_trx_id IN OUT NOCOPY NUMBER,
567 p_installment IN NUMBER,
568 p_return_status OUT NOCOPY VARCHAR2
569 ) RETURN NUMBER IS
570 l_inv_ps_id NUMBER;
571
572 BEGIN
573 IF PG_DEBUG in ('Y', 'C') THEN
574 arp_util.debug('Get_trx_ps_id ()+');
575 END IF;
576 p_return_status := FND_API.G_RET_STS_SUCCESS;
577
578 IF p_installment IS NOT NULL THEN
579 BEGIN
580 SELECT ps.payment_schedule_id
581 INTO l_inv_ps_id
582 FROM ra_customer_trx ct,
583 ar_payment_schedules ps
584 WHERE ct.customer_trx_id = p_inv_customer_trx_id
585 AND ct.customer_trx_id = ps.customer_trx_id
586 --AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
587 AND ps.terms_sequence_number = p_installment
588 ;
589 EXCEPTION
590 WHEN no_data_found THEN
591 IF ar_cm_api_pub.Original_cm_unapp_info.inv_customer_trx_id IS NOT NULL THEN
592 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_ID_INST_INVALID');
593 FND_MSG_PUB.Add;
594 p_return_status := FND_API.G_RET_STS_ERROR;
595 ELSIF ar_cm_api_pub.Original_cm_unapp_info.inv_trx_number IS NOT NULL THEN
596 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_NUM_INST_INVALID');
597 FND_MSG_PUB.Add;
598 p_return_status := FND_API.G_RET_STS_ERROR;
599 END IF;
600
601 END;
602 ELSE
603 --if the user has not entered the installment then if the transaction
604 --has only one installment, get the ps_id for that installment
605 BEGIN
606 SELECT ps.payment_schedule_id
607 INTO l_inv_ps_id
608 FROM ra_customer_trx ct,
609 ar_payment_schedules ps
610 WHERE ct.customer_trx_id = p_inv_customer_trx_id
611 AND ct.customer_trx_id = ps.customer_trx_id
612 --AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
613 ;
614 EXCEPTION
615 WHEN no_data_found THEN
616 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_INVALID');
617 FND_MSG_PUB.Add;
618 p_return_status := FND_API.G_RET_STS_ERROR;
619 WHEN too_many_rows THEN
620 FND_MESSAGE.SET_NAME('AR','AR_RAPI_INSTALL_NULL');
621 FND_MSG_PUB.Add;
622 p_return_status := FND_API.G_RET_STS_ERROR;
623 END;
624
625
626 END IF;
627
628 RETURN(l_inv_ps_id);
629
630 IF PG_DEBUG in ('Y', 'C') THEN
631 arp_util.debug('Get_trx_ps_id ()-');
632 END IF;
633
634 EXCEPTION
635 WHEN others THEN
636 IF PG_DEBUG in ('Y', 'C') THEN
637 arp_util.debug('EXCEPTION: Get_trx_ps_id()');
638 END IF;
639 raise;
640 END Get_trx_ps_id;
641
642 PROCEDURE get_ra_info(
643 p_ra_id IN NUMBER,
644 p_ra_app_ps_id OUT NOCOPY NUMBER,
645 p_inv_customer_trx_id OUT NOCOPY NUMBER,
646 p_apply_gl_date OUT NOCOPY DATE,
647 p_return_status OUT NOCOPY VARCHAR2
648 ) IS
649 CURSOR rec_apppln IS
650 SELECT ra.applied_customer_trx_id, ra.applied_payment_schedule_id, ra.gl_date
651 FROM ar_receivable_applications ra,
652 ar_payment_schedules ps
653 WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
654 AND ra.receivable_application_id = p_ra_id
655 AND ra.display = 'Y'
656 AND ra.status = 'APP'
657 AND ps.reserved_value IS NULL
658 AND ps.reserved_type IS NULL;
659
660 BEGIN
661
662 IF PG_DEBUG in ('Y', 'C') THEN
663 arp_util.debug('get_ra_info ()+');
664 END IF;
665
666 p_return_status := FND_API.G_RET_STS_SUCCESS;
667
668 OPEN rec_apppln;
669 FETCH rec_apppln INTO p_inv_customer_trx_id, p_ra_app_ps_id,p_apply_gl_date;
670 IF rec_apppln%NOTFOUND THEN
671 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_INVALID');
672 FND_MSG_PUB.Add;
673 p_return_status := FND_API.G_RET_STS_ERROR;
674 END IF;
675 CLOSE rec_apppln;
676
677
678 IF PG_DEBUG in ('Y', 'C') THEN
679 arp_util.debug('Get_ra_info ()-');
680 arp_util.debug('Applied PS ID: '|| p_ra_app_ps_id ||
681 'Applied Trx ID: '|| p_inv_customer_trx_id ||
682 'Apply GL date: '|| p_apply_gl_date);
683 END IF;
684
685
686
687 END get_ra_info;
688
689 PROCEDURE default_ra_id(
690 p_cm_customer_trx_id IN NUMBER,
691 p_applied_payment_schedule_id IN NUMBER,
692 p_apply_gl_date OUT NOCOPY DATE,
693 p_receivable_application_id OUT NOCOPY NUMBER,
694 p_return_status OUT NOCOPY VARCHAR2
695 ) IS
696
697 BEGIN
698 IF PG_DEBUG in ('Y', 'C') THEN
699 arp_util.debug('Default_ra_id ()+');
700 END IF;
701 p_return_status := FND_API.G_RET_STS_SUCCESS;
702 IF p_cm_customer_trx_id IS NOT NULL AND
703 p_applied_payment_schedule_id IS NOT NULL
704 THEN
705 SELECT receivable_application_id, gl_date
706 INTO p_receivable_application_id, p_apply_gl_date
707 FROM ar_receivable_applications ra
708 WHERE ra.customer_trx_id = p_cm_customer_trx_id
709 AND ra.applied_payment_schedule_id = p_applied_payment_schedule_id
710 AND ra.display = 'Y'
711 AND ra.status = 'APP'
712 AND ra.application_type = 'CM';
713
714 END IF;
715 IF PG_DEBUG in ('Y', 'C') THEN
716 arp_util.debug('Default_ra_id ()+');
717 END IF;
718 EXCEPTION
719 WHEN no_data_found THEN
720 FND_MESSAGE.SET_NAME('AR','AR_CMAPI_CM_NOT_APP_TO_INV');
721 FND_MSG_PUB.Add;
722 p_return_status := FND_API.G_RET_STS_ERROR;
723 raise;
724 WHEN others THEN
725 IF PG_DEBUG in ('Y', 'C') THEN
726 arp_util.debug('Default_ra_id: ' || 'EXCEPTION: Get_ra_id()');
727 END IF;
728 raise;
729 END default_ra_id;
730
731 PROCEDURE default_reversal_gl_date(
732 p_receivable_application_id IN NUMBER,
733 p_reversal_gl_date IN OUT NOCOPY DATE,
734 p_apply_gl_date IN OUT NOCOPY DATE,
735 p_cm_customer_trx_id IN OUT NOCOPY NUMBER
736 ) IS
737 l_apply_gl_date DATE;
738 l_default_gl_date DATE;
739 l_defaulting_rule_used VARCHAR2(100);
740 l_error_message VARCHAR2(240);
741 BEGIN
742 IF PG_DEBUG in ('Y', 'C') THEN
743 arp_util.debug('Default_reversal_gl_date ()+');
744 END IF;
745
746 l_apply_gl_date := p_apply_gl_date;
747
748 IF p_receivable_application_id IS NOT NULL THEN
749 IF p_apply_gl_date IS NULL THEN
750 --get the gl_date for the application
751 BEGIN
752 SELECT gl_date, customer_trx_id
753 INTO l_apply_gl_date, p_cm_customer_trx_id
754 FROM ar_receivable_applications
755 WHERE receivable_application_id =
756 p_receivable_application_id;
757 EXCEPTION
758 WHEN OTHERS THEN
759 NULL;
760 END;
761 END IF;
762
763 IF p_reversal_gl_date is null THEN
764 IF (arp_util.validate_and_default_gl_date(
765 nvl(l_apply_gl_date,trunc(sysdate)),
766 NULL,
767 l_apply_gl_date,
768 NULL,
769 NULL,
770 NULL,
771 NULL,
772 NULL,
773 'N',
774 NULL,
775 arp_global.set_of_books_id,
776 222,
777 l_default_gl_date,
778 l_defaulting_rule_used,
779 l_error_message) = TRUE) THEN
780
781 p_reversal_gl_date := l_default_gl_date;
782 ELSE
783 --we were not able to default the gl_date put the message
784 --here on the stack, but the return status will be set
785 --to FND_API.G_RET_STS_ERROR in the validation phase.
786 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
787 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', l_error_message);
788 FND_MSG_PUB.Add;
789 END IF;
790 END IF;
791 END IF;
792 IF PG_DEBUG in ('Y', 'C') THEN
793 arp_util.debug('Default_reversal_gl_date ()-');
794 END IF;
795 EXCEPTION
796 When others THEN
797 IF PG_DEBUG in ('Y', 'C') THEN
798 arp_util.debug('EXCEPTION: Default_reversal_gl_date()');
799 END IF;
800 raise;
801 END Default_reversal_gl_date;
802
803
804 PROCEDURE Validate_ra_id(
805 p_receivable_application_id IN NUMBER,
806 p_application_type IN VARCHAR2,
807 p_return_status OUT NOCOPY VARCHAR2) IS
808 l_valid NUMBER;
809 BEGIN
810 p_return_status := FND_API.G_RET_STS_SUCCESS;
811 IF PG_DEBUG in ('Y', 'C') THEN
812 arp_util.debug('Validate_ra_id ()+');
813 END IF;
814 --validate the receivable application id only if it was passed in
815 --directly as a parameter. No need to validate if it was derived.
816 IF p_receivable_application_id IS NOT NULL AND
817 ar_cm_api_pub.original_cm_unapp_info.receivable_application_id IS NOT NULL
818 THEN
819 SELECT count(*)
820 INTO l_valid
821 FROM AR_RECEIVABLE_APPLICATIONS ra
822 WHERE ra.receivable_application_id = p_receivable_application_id
823 and ra.display = 'Y'
824 and ra.status = p_application_type
825 and ra.application_type = 'CM';
826
827 IF l_valid = 0 THEN
828 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_INVALID');
829 FND_MSG_PUB.Add;
830 p_return_status := FND_API.G_RET_STS_ERROR;
831 END IF;
832
833 ELSIF p_receivable_application_id IS NULL THEN
834 IF ar_cm_api_pub.original_cm_unapp_info.cm_trx_number IS NULL AND
835 ar_cm_api_pub.original_cm_unapp_info.cm_customer_trx_id IS NULL AND
836 ar_cm_api_pub.original_cm_unapp_info.applied_ps_id IS NULL AND
837 ar_cm_api_pub.original_cm_unapp_info.inv_customer_trx_id IS NULL AND
838 ar_cm_api_pub.original_cm_unapp_info.inv_trx_number IS NULL
839 THEN
840 --receivable application id is null
841 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_NULL');
842 FND_MSG_PUB.Add;
843 p_return_status := FND_API.G_RET_STS_ERROR;
844 ELSIF ar_cm_api_pub.original_cm_unapp_info.inv_trx_number IS NULL AND
845 ar_cm_api_pub.original_cm_unapp_info.inv_customer_trx_id IS NULL AND
846 ar_cm_api_pub.original_cm_unapp_info.applied_ps_id IS NULL AND
847 (ar_cm_api_pub.original_cm_unapp_info.cm_customer_trx_id IS NOT NULL OR
848 ar_cm_api_pub.original_cm_unapp_info.cm_trx_number IS NOT NULL)
849 THEN
850 --the transaction was not specified
851 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_NULL');
852 FND_MSG_PUB.Add;
853 p_return_status := FND_API.G_RET_STS_ERROR;
854 ELSIF (ar_cm_api_pub.original_cm_unapp_info.inv_trx_number IS NOT NULL OR
855 ar_cm_api_pub.original_cm_unapp_info.inv_customer_trx_id IS NOT NULL OR
856 ar_cm_api_pub.original_cm_unapp_info.applied_ps_id IS NOT NULL) AND
857 ar_cm_api_pub.original_cm_unapp_info.cm_customer_trx_id IS NULL AND
858 ar_cm_api_pub.original_cm_unapp_info.cm_trx_number IS NULL
859 THEN
860 --the credit memo was not specified
861 FND_MESSAGE.SET_NAME('AR','AR_CMAPI_CM_TRX_ID_NULL');
862 FND_MSG_PUB.Add;
863 p_return_status := FND_API.G_RET_STS_ERROR;
864 END IF;
865
866 END IF;
867 IF PG_DEBUG in ('Y', 'C') THEN
868 arp_util.debug('Validate_receivable_appln_id ()-');
869 END IF;
870 EXCEPTION
871 WHEN others THEN
872 IF PG_DEBUG in ('Y', 'C') THEN
873 arp_util.debug('EXCEPTION: Validate_ra_id(-)');
874 END IF;
875 raise;
876
877 END Validate_ra_id;
878
879
880 PROCEDURE Validate_Rev_gl_date(p_reversal_gl_date IN DATE,
881 p_apply_gl_date IN DATE,
882 p_cm_gl_date IN DATE,
883 p_return_status OUT NOCOPY VARCHAR2
884 ) IS
885
886 BEGIN
887 IF PG_DEBUG in ('Y', 'C') THEN
888 arp_util.debug('Validate_Rev_gl_date ()+');
889 END IF;
890 p_return_status := FND_API.G_RET_STS_SUCCESS;
891 IF p_reversal_gl_date IS NOT NULL THEN
892
893 IF p_reversal_gl_date < NVL(p_apply_gl_date,p_reversal_gl_date) THEN
894 FND_MESSAGE.SET_NAME('AR','AR_RW_BEFORE_APP_GL_DATE');
895 --Int'l Calendar Project
896 FND_MESSAGE.SET_TOKEN('GL_DATE', fnd_date.date_to_chardate(p_apply_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
897 FND_MSG_PUB.Add;
898 p_return_status := FND_API.G_RET_STS_ERROR;
899 END IF;
900 IF p_reversal_gl_date < nvl(p_cm_gl_date,p_reversal_gl_date) THEN
901 FND_MESSAGE.SET_NAME('AR','AR_RW_BEFORE_CM_GL_DATE');
902 --Int'l Calendar Project
903 FND_MESSAGE.SET_TOKEN('GL_DATE', fnd_date.date_to_chardate(p_cm_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
904 FND_MSG_PUB.Add;
905 p_return_status := FND_API.G_RET_STS_ERROR;
906 END IF;
907
908 IF ( NOT arp_util.is_gl_date_valid(p_reversal_gl_date)) THEN
909 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
910 --Int'l Calendar Project
911 FND_MESSAGE.set_token( 'GL_DATE', fnd_date.date_to_chardate(p_reversal_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt));
912 FND_MSG_PUB.Add;
913 p_return_status := FND_API.G_RET_STS_ERROR;
914 END IF;
915
916 ELSE
917 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REV_GL_DATE_NULL');
918 FND_MSG_PUB.Add;
919 p_return_status := FND_API.G_RET_STS_ERROR;
920 IF PG_DEBUG in ('Y', 'C') THEN
921 arp_util.debug('Validate_Rev_gl_date: ' || 'The Reversal gl date is null ');
922 END IF;
923 END IF;
924
925 IF PG_DEBUG in ('Y', 'C') THEN
926 arp_util.debug('Validate_Rev_gl_date ()+');
927 END IF;
928 EXCEPTION
929 WHEN others THEN
930 IF PG_DEBUG in ('Y', 'C') THEN
931 arp_util.debug('EXCEPTION: Validate_rev_gl_date() ');
932 END IF;
933 raise;
934 END Validate_Rev_gl_date;
935
936 -- PUBLIC PROCEDURES/FUNCTIONS
937
938 PROCEDURE default_app_ids(
939 p_cm_customer_trx_id IN OUT NOCOPY NUMBER,
940 p_cm_trx_number IN VARCHAR2,
941 p_inv_customer_trx_id IN OUT NOCOPY NUMBER,
942 p_inv_trx_number IN VARCHAR2,
943 p_inv_customer_trx_line_id IN OUT NOCOPY NUMBER,
944 p_inv_line_number IN NUMBER,
945 p_installment IN OUT NOCOPY NUMBER,
946 p_applied_payment_schedule_id IN NUMBER,
947 p_return_status OUT NOCOPY VARCHAR2 )
948
949 IS
950
951
952 CURSOR c_pay_sched IS
953 SELECT customer_trx_id, terms_sequence_number
954 FROM ar_payment_schedules
955 WHERE payment_schedule_id = p_applied_payment_schedule_id and
956 payment_schedule_id >0 and
957 class in ('INV','DM'); -- Should we include DM, DEP, GUAR, CB?
958
959 CURSOR c_trx_type(c_customer_trx_id number ) IS
960 SELECT tt.type
961 FROM ra_cust_trx_types tt, ra_customer_trx ct
962 WHERE ct.customer_trx_id = c_customer_trx_id
963 and tt.cust_trx_type_id=ct.cust_trx_type_id;
964
965 l_class varchar2(20);
966
967 l_inv_customer_trx_id NUMBER;
968 l_installment NUMBER;
969 p_inv_return_status_lines VARCHAR2(100);
970
971 BEGIN
972
973 IF PG_DEBUG in ('Y', 'C') THEN
974 arp_util.debug('default_app_ids(+)');
975 END IF;
976
977 p_return_status := FND_API.G_RET_STS_SUCCESS;
978
979 -- Step 1: Get CM customer_trx_id and validate.
980 IF p_cm_trx_number IS NOT NULL THEN
981 default_customer_trx_id(p_cm_customer_trx_id ,
982 p_cm_trx_number ,
983 p_return_status);
984 END IF;
985
986 OPEN c_trx_type(p_cm_customer_trx_id);
987 FETCH c_trx_type into l_class;
988 CLOSE c_trx_type;
989
990 IF (l_class <> 'CM') then
991 FND_MESSAGE.SET_NAME('AR','AR_CMAPI_CM_TRX_ID_INVALID');
992 FND_MSG_PUB.Add;
993 p_return_status := FND_API.G_RET_STS_ERROR ;
994 END IF;
995
996 -- Step 2: Get DM/INV customer_trx_id and validate
997 IF p_inv_trx_number IS NOT NULL THEN
998 default_customer_trx_id(p_inv_customer_trx_id ,
999 p_inv_trx_number ,
1000 p_return_status);
1001 END IF;
1002
1003 OPEN c_trx_type(p_inv_customer_trx_id);
1004 FETCH c_trx_type into l_class;
1005 CLOSE c_trx_type;
1006
1007 If (l_class not in ( 'INV','DM') ) then
1008 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_INVALID');
1009 FND_MSG_PUB.Add;
1010 p_return_status := FND_API.G_RET_STS_ERROR ;
1011 end if;
1012
1013 -- Step 3: Get a valid value for DM customer trx line id
1014 default_customer_trx_line_id(p_inv_customer_trx_id,
1015 p_inv_customer_trx_line_id,
1016 p_inv_line_number,
1017 p_inv_return_status_lines);
1018
1019 -- Step 4: Get inv_customer_trx_id from applied_payment_schedule_id and
1020 -- installment
1021
1022 IF p_applied_payment_schedule_id IS NOT NULL THEN
1023 OPEN c_pay_sched;
1024 FETCH c_pay_sched
1025 INTO l_inv_customer_trx_id,
1026 l_installment;
1027 IF c_pay_sched%NOTFOUND THEN
1028 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APP_PS_ID_INVALID');
1029 FND_MSG_PUB.Add;
1030 p_return_status := FND_API.G_RET_STS_ERROR ;
1031 END IF;
1032 CLOSE c_pay_sched;
1033
1034 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
1035 IF nvl(p_inv_customer_trx_id,nvl(l_inv_customer_trx_id,-99)) = nvl(l_inv_customer_trx_id,-99) THEN
1036 p_inv_customer_trx_id := l_inv_customer_trx_id;
1037 END IF;
1038 IF nvl(p_installment,nvl(l_installment,-99)) = nvl(l_installment,-99) THEN
1039 p_installment := l_installment;
1040 END IF;
1041 END IF;
1042 ELSE -- p_payment_schedule_id is null
1043 --default the installment from the customer_trx_id if not entered
1044 IF p_inv_customer_trx_id IS NOT NULL THEN
1045 IF p_installment IS NULL THEN
1046 BEGIN
1047 SELECT terms_sequence_number
1048 INTO p_installment
1049 FROM ar_payment_schedules
1050 WHERE customer_trx_id = p_inv_customer_trx_id;
1051 EXCEPTION
1052 WHEN no_data_found THEN
1053 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_INVALID');
1054 FND_MSG_PUB.Add;
1055 p_return_status := FND_API.G_RET_STS_ERROR;
1056 WHEN too_many_rows THEN
1057 FND_MESSAGE.SET_NAME('AR','AR_RAPI_INSTALL_NULL');
1058 FND_MSG_PUB.Add;
1059 p_return_status := FND_API.G_RET_STS_ERROR;
1060 END;
1061 END IF;
1062 END IF;
1063 END IF;
1064
1065
1066 IF (p_inv_return_status_lines = FND_API.G_RET_STS_ERROR OR
1067 p_return_status = FND_API.G_RET_STS_ERROR) THEN
1068 p_return_status := FND_API.G_RET_STS_ERROR;
1069 END IF;
1070
1071 IF PG_DEBUG in ('Y', 'C') THEN
1072 arp_util.debug('Default_app_ids: ' || 'Defaulted Value for the application ids');
1073 arp_util.debug('Default_app_ids: ' || 'p_cm_customer_trx_id :'||to_char(p_cm_customer_trx_id));
1074 arp_util.debug('Default_app_ids: ' || 'p_inv_customer_trx_id :'||to_char(p_inv_customer_trx_id));
1075 arp_util.debug('Default_appln_ids: ' || 'p_installment :'||to_char(p_installment));
1076 arp_util.debug('Default_app_ids: ' || 'p_applied_payment_schedule_id :'||to_char(p_applied_payment_schedule_id));
1077 arp_util.debug('Default_app_ids ()- ');
1078 END IF;
1079 EXCEPTION
1080 WHEN others THEN
1081 IF PG_DEBUG in ('Y', 'C') THEN
1082 arp_util.debug('EXCEPTION: Default_appln_ids()');
1083 END IF;
1084 RAISE;
1085
1086 END Default_app_ids;
1087
1088
1089 PROCEDURE default_app_info(
1090 p_cm_customer_trx_id IN NUMBER,
1091 p_inv_customer_trx_id IN NUMBER,
1092 p_inv_customer_trx_line_id IN NUMBER,
1093 p_show_closed_invoices IN VARCHAR2,
1094 p_installment IN OUT NOCOPY NUMBER,
1095 p_apply_date IN OUT NOCOPY DATE,
1096 p_apply_gl_date IN OUT NOCOPY DATE,
1097 p_amount_applied IN OUT NOCOPY NUMBER,
1098 p_applied_payment_schedule_id IN OUT NOCOPY NUMBER,
1099 p_cm_gl_date OUT NOCOPY DATE,
1100 p_cm_trx_date OUT NOCOPY DATE,
1101 p_cm_amount_rem OUT NOCOPY NUMBER,
1102 p_cm_currency_code OUT NOCOPY VARCHAR2,
1103 p_inv_due_date OUT NOCOPY DATE,
1104 p_inv_currency_code OUT NOCOPY VARCHAR2,
1105 p_inv_amount_rem OUT NOCOPY NUMBER,
1106 p_inv_trx_date OUT NOCOPY DATE,
1107 p_inv_gl_date OUT NOCOPY DATE,
1108 p_allow_overappln_flag OUT NOCOPY VARCHAR2,
1109 p_natural_appln_only_flag OUT NOCOPY VARCHAR2,
1110 p_creation_sign OUT NOCOPY VARCHAR2,
1111 p_cm_payment_schedule_id OUT NOCOPY NUMBER,
1112 p_inv_line_amount OUT NOCOPY NUMBER,
1113 p_return_status OUT NOCOPY VARCHAR2
1114 )
1115 IS
1116
1117 l_cm_gl_date DATE;
1118 l_cm_amount_rem NUMBER;
1119 l_cm_trx_date DATE;
1120 l_cm_ps_id NUMBER;
1121 l_cm_currency_code fnd_currencies.currency_code%TYPE;
1122 l_cm_customer_id NUMBER;
1123 l_cm_info_return_status VARCHAR2(1);
1124
1125 l_inv_customer_id NUMBER; --customer on transaction
1126 l_inv_cust_trx_type_id NUMBER;
1127 l_inv_due_date DATE;
1128 l_inv_trx_date DATE;
1129 l_inv_gl_date DATE;
1130 l_allow_overappln_flag VARCHAR2(1);
1131 l_natural_appln_only_flag VARCHAR2(1);
1132 l_creation_sign VARCHAR2(1);
1133 --l_applied_payment_schedule_id NUMBER;
1134 l_app_gl_date DATE;
1135 l_inv_amount_rem NUMBER;
1136 l_trx_info_return_status VARCHAR2(1);
1137 l_inv_line_amount NUMBER;
1138
1139 l_inv_currency_code fnd_currencies.currency_code%TYPE;
1140 l_def_amt_return_status VARCHAR2(1);
1141
1142 l_return BOOLEAN;
1143 l_default_gl_date DATE;
1144 l_defaulting_rule_used VARCHAR2(100);
1145 l_error_message VARCHAR2(200);
1146
1147 BEGIN
1148
1149 IF PG_DEBUG in ('Y', 'C') THEN
1150 arp_util.debug('Default_app_info ()+');
1151 END IF;
1152
1153 p_return_status := FND_API.G_RET_STS_SUCCESS;
1154
1155 -- Step 1: Default CM Info:
1156 default_cm_info(
1157 p_cm_customer_trx_id,
1158 l_cm_gl_date,
1159 l_cm_amount_rem,
1160 l_cm_trx_date,
1161 l_cm_ps_id,
1162 l_cm_currency_code,
1163 l_cm_customer_id,
1164 l_cm_info_return_status );
1165
1166 IF PG_DEBUG in ('Y', 'C') THEN
1167 arp_util.debug('Default_app_info: ' || 'Default_CM_Info return status = '||l_cm_info_return_status);
1168 END IF;
1169
1170 -- Step 2: Default DM info
1171
1172 IF l_cm_info_return_status = FND_API.G_RET_STS_SUCCESS THEN
1173 Default_Trx_Info(
1174 p_inv_customer_trx_id ,
1175 p_inv_customer_trx_line_id ,
1176 p_show_closed_invoices ,
1177 l_cm_gl_date ,
1178 l_cm_customer_id ,
1179 l_cm_currency_code ,
1180 l_cm_ps_id ,
1181 l_cm_trx_date ,
1182 --- Out variables
1183 l_inv_customer_id , --customer on transaction
1184 l_inv_cust_trx_type_id ,
1185 l_inv_due_date ,
1186 l_inv_trx_date ,
1187 l_inv_gl_date ,
1188 l_allow_overappln_flag ,
1189 l_natural_appln_only_flag ,
1190 l_creation_sign ,
1191 p_applied_payment_schedule_id ,
1192 l_app_gl_date , --this is the application gl_date
1193 p_installment ,
1194 l_inv_amount_rem ,
1195 l_inv_currency_code ,
1196 l_trx_info_return_status
1197 );
1198
1199
1200 END IF;
1201
1202 IF PG_DEBUG in ('Y', 'C') THEN
1203 arp_util.debug('Default_app_info: ' || 'Default trx info return status = '||l_trx_info_return_status);
1204 arp_util.debug('Applied PS ID : ' || p_applied_payment_schedule_id);
1205 END IF;
1206
1207
1208 -- Step 3: Default apply date
1209
1210 IF p_apply_date IS NULL THEN
1211 p_apply_date := GREATEST(sysdate,
1212 GREATEST(NVL(l_cm_trx_date,sysdate),
1213 NVL(l_inv_trx_date,sysdate)));
1214 END IF;
1215
1216
1217 -- Step 4: Default GL Date
1218
1219 IF p_apply_gl_date IS NULL THEN
1220 l_return :=
1221 arp_util.validate_and_default_gl_date(
1222 gl_date => l_app_gl_date,
1223 trx_date => null,
1224 validation_date1 => null,
1225 validation_date2 => null,
1226 validation_date3 => null,
1227 default_date1 => l_app_gl_date,
1228 default_date2 => null,
1229 default_date3 => null,
1230 p_allow_not_open_flag => 'N',
1231 p_invoicing_rule_id => null,
1232 p_set_of_books_id => arp_global.set_of_books_id,
1233 p_application_id => 222,
1234 default_gl_date => l_default_gl_date ,
1235 defaulting_rule_used => l_defaulting_rule_used,
1236 error_message => l_error_message);
1237
1238 IF l_return = TRUE THEN
1239 p_apply_gl_date := l_default_gl_date;
1240 END IF;
1241
1242 END IF;
1243
1244
1245 -- Step 5: Default amount applied
1246 default_amt_applied(
1247 l_inv_currency_code ,
1248 l_cm_currency_code ,
1249 l_cm_amount_rem ,
1250 l_inv_amount_rem ,
1251 p_amount_applied ,
1252 l_def_amt_return_status
1253 );
1254
1255
1256 p_cm_gl_date := l_cm_gl_date;
1257 p_cm_trx_date := l_cm_trx_date;
1258 p_cm_amount_rem := l_cm_amount_rem;
1259 p_cm_currency_code := l_cm_currency_code;
1260 p_inv_due_date := l_inv_due_date;
1261 p_inv_currency_code := l_inv_currency_code;
1262 p_inv_amount_rem := l_inv_amount_rem;
1263 p_inv_trx_date := l_inv_trx_date;
1264 p_inv_gl_date := l_inv_gl_date;
1265 p_allow_overappln_flag := l_allow_overappln_flag;
1266 p_natural_appln_only_flag := l_natural_appln_only_flag;
1267 p_creation_sign := l_creation_sign;
1268 p_cm_payment_schedule_id := l_cm_ps_id;
1269 p_inv_line_amount := l_inv_line_amount;
1270
1271
1272
1273 IF PG_DEBUG in ('Y', 'C') THEN
1274 arp_util.debug('Default_appl_info: ' || 'Default amount return status :'||l_def_amt_return_status );
1275 END IF;
1276
1277
1278 END default_app_info;
1279
1280
1281 PROCEDURE validate_app_info(
1282 p_apply_date IN DATE,
1283 p_cm_trx_date IN DATE,
1284 p_inv_trx_date IN DATE,
1285 p_apply_gl_date IN DATE,
1286 p_cm_gl_date IN DATE,
1287 p_inv_gl_date IN DATE,
1288 p_amount_applied IN NUMBER,
1289 p_applied_payment_schedule_id IN NUMBER,
1290 p_customer_trx_line_id IN NUMBER,
1291 p_inv_line_amount IN NUMBER,
1292 p_creation_sign IN VARCHAR2,
1293 p_allow_overappln_flag IN VARCHAR2,
1294 p_natural_appln_only_flag IN VARCHAR2,
1295 p_cm_amount_rem IN NUMBER,
1296 p_inv_amount_rem IN NUMBER,
1297 p_cm_currency_code IN VARCHAR2,
1298 p_inv_currency_code IN VARCHAR2,
1299 p_return_status OUT NOCOPY VARCHAR2
1300 ) IS
1301
1302 l_gl_date_return_status VARCHAR2(1);
1303 l_amt_applied_return_status VARCHAR2(1);
1304 l_apply_date_return_status VARCHAR2(1);
1305
1306
1307 BEGIN
1308
1309 -- Validations of cm_customer_trx_id, inv_customer_trx_id,
1310 -- applied_payment_schedule_id are done in defaulting routines
1311
1312 IF PG_DEBUG in ('Y', 'C') THEN
1313 arp_util.debug('Validate_App_info ()+');
1314 END IF;
1315
1316 p_return_status := FND_API.G_RET_STS_SUCCESS;
1317
1318 validate_apply_date(p_apply_date,
1319 p_inv_trx_date,
1320 p_cm_trx_date,
1321 l_apply_date_return_status
1322 );
1323
1324 validate_apply_gl_date(p_apply_gl_date ,
1325 p_inv_gl_date ,
1326 p_cm_gl_date ,
1327 l_gl_date_return_status
1328 );
1329
1330 IF PG_DEBUG in ('Y', 'C') THEN
1331 arp_util.debug('Validate_Application_info: ' || 'Apply gl_date return status :'||l_gl_date_return_status);
1332 END IF;
1333 validate_amount_applied(
1334 p_amount_applied ,
1335 p_applied_payment_schedule_id ,
1336 p_customer_trx_line_id ,
1337 p_inv_line_amount ,
1338 p_creation_sign ,
1339 p_allow_overappln_flag ,
1340 p_natural_appln_only_flag ,
1341 p_inv_amount_rem ,
1342 l_amt_applied_return_status
1343 );
1344 IF l_gl_date_return_status <> FND_API.G_RET_STS_SUCCESS OR
1345 l_amt_applied_return_status <> FND_API.G_RET_STS_SUCCESS OR
1346 l_apply_date_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1347 p_return_status := FND_API.G_RET_STS_ERROR;
1348 END IF;
1349
1350 IF PG_DEBUG in ('Y', 'C') THEN
1351 arp_util.debug('Validate_App_info ()-');
1352 END IF;
1353 EXCEPTION
1354 WHEN others THEN
1355 IF PG_DEBUG in ('Y', 'C') THEN
1356 arp_util.debug('EXCEPTION: Validate_App_Info() ');
1357 END IF;
1358 raise;
1359
1360
1361 END validate_app_info;
1362
1363 PROCEDURE Default_unapp_ids(
1364 p_cm_trx_number IN VARCHAR2,
1365 p_cm_customer_trx_id IN OUT NOCOPY NUMBER,
1366 p_inv_trx_number IN VARCHAR2,
1367 p_inv_customer_trx_id IN OUT NOCOPY NUMBER,
1368 p_receivable_application_id IN OUT NOCOPY NUMBER,
1369 p_installment IN NUMBER,
1370 p_applied_payment_schedule_id IN OUT NOCOPY NUMBER,
1371 p_apply_gl_date OUT NOCOPY DATE,
1372 p_return_status OUT NOCOPY VARCHAR2
1373 ) IS
1374
1375 CURSOR c_pay_sched IS
1376 SELECT customer_trx_id, terms_sequence_number
1377 FROM ar_payment_schedules
1378 WHERE payment_schedule_id = p_applied_payment_schedule_id and
1379 payment_schedule_id >0 and
1380 class in ('INV','DM'); -- Should we include BR, DEP, GUAR, CB?
1381
1382
1383 l_cm_cust_trx_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1384 l_inv_cust_trx_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1385 l_applied_ps_id_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1386 l_inv_customer_trx_id NUMBER;
1387 l_applied_payment_schedule_id NUMBER;
1388 l_ra_app_ps_id NUMBER;
1389 l_receivable_application_id NUMBER;
1390 l_installment NUMBER(15);
1391 l_ra_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1392
1393 BEGIN
1394
1395
1396 IF PG_DEBUG in ('Y', 'C') THEN
1397 arp_util.debug('Default_unapp_ids ()+');
1398 END IF;
1399 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1400
1401 --Step 1: Get a valid value for the CM customer_trx_id
1402 IF p_cm_trx_number IS NOT NULL THEN
1403 Default_customer_trx_id(p_cm_customer_trx_id ,
1404 p_cm_trx_number ,
1405 l_cm_cust_trx_return_status);
1406 END IF;
1407
1408 -- Step 2: Get a valid value for DM customer_trx_id
1409 IF p_inv_trx_number IS NOT NULL THEN
1410 default_customer_trx_id(p_inv_customer_trx_id ,
1411 p_inv_trx_number ,
1412 l_inv_cust_trx_return_status);
1413 END IF;
1414
1415
1416 -- Step 3: Get payment schedule info
1417 --if error is raised in deriving the customer_trx_id from the trx_number,
1418 --do not process the applied_payment_schedule_id any further.
1419 IF l_cm_cust_trx_return_status= FND_API.G_RET_STS_SUCCESS AND
1420 l_inv_cust_trx_return_status= FND_API.G_RET_STS_SUCCESS THEN
1421 IF p_applied_payment_schedule_id IS NOT NULL THEN
1422 OPEN c_pay_sched;
1423 FETCH c_pay_sched
1424 INTO l_inv_customer_trx_id,
1425 l_installment;
1426 IF c_pay_sched%NOTFOUND THEN
1427 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APP_PS_ID_INVALID');
1428 FND_MSG_PUB.Add;
1429 p_return_status := FND_API.G_RET_STS_ERROR ;
1430 END IF;
1431 CLOSE c_pay_sched;
1432
1433 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
1434 IF (nvl(p_inv_customer_trx_id,l_inv_customer_trx_id) <> l_inv_customer_trx_id OR
1435 nvl(p_installment,l_installment) <> l_installment) THEN
1436 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_PS_ID_X_INVALID');
1437 FND_MSG_PUB.Add;
1438 p_return_status := FND_API.G_RET_STS_ERROR;
1439 ELSE
1440 p_inv_customer_trx_id := l_inv_customer_trx_id;
1441 END IF;
1442
1443 END IF;
1444 ELSE -- p_applied_ayment_schedule_id is null
1445 IF p_inv_customer_trx_id IS NOT NULL THEN
1446 l_applied_payment_schedule_id :=
1447 Get_trx_ps_id(p_inv_customer_trx_id,
1448 p_installment,
1449 l_applied_ps_id_return_status);
1450 p_applied_payment_schedule_id
1451 :=l_applied_payment_schedule_id;
1452 END IF;
1453 END IF;
1454
1455 END IF;
1456
1457 -- Step 4: get related info for receivable_application_id
1458
1459 IF p_receivable_application_id IS NOT NULL THEN
1460 get_ra_info(p_receivable_application_id,
1461 l_ra_app_ps_id,
1462 l_inv_customer_trx_id,
1463 p_apply_gl_date,
1464 l_ra_return_status);
1465
1466 IF nvl( l_ra_app_ps_id,-99) <> nvl(p_applied_payment_schedule_id,
1467 nvl( l_ra_app_ps_id,-99))
1468 THEN
1469 IF ar_cm_api_pub.original_cm_unapp_info.inv_customer_trx_id IS NOT NULL OR
1470 ar_cm_api_pub.original_cm_unapp_info.inv_trx_number IS NOT NULL THEN
1471 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_RA_ID_X_INVALID');
1472 FND_MSG_PUB.Add;
1473 p_return_status := FND_API.G_RET_STS_ERROR;
1474 ELSIF ar_cm_api_pub.original_cm_unapp_info.applied_ps_id IS NOT NULL THEN
1475 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APP_PS_RA_ID_X_INVALID');
1476 FND_MSG_PUB.Add;
1477 p_return_status := FND_API.G_RET_STS_ERROR;
1478 END IF;
1479 ELSE
1480 p_applied_payment_schedule_id := l_ra_app_ps_id;
1481 END IF;
1482
1483 IF nvl(l_inv_customer_trx_id,-99) <> nvl(p_inv_customer_trx_id,nvl(l_inv_customer_trx_id,-99)) THEN
1484 --Invalid receivable application identifier for the entered
1485 -- invoice customer trx id
1486 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCPT_RA_ID_X_INVALID');
1487 FND_MSG_PUB.Add;
1488 p_return_status := FND_API.G_RET_STS_ERROR;
1489 ELSE
1490 p_inv_customer_trx_id := l_inv_customer_trx_id;
1491 END IF;
1492 ELSE --the user has not passed in the receivable application id
1493 --
1494 -- derive receivable_application_id
1495 --
1496 --If app_ps_id and the cash_receipt_id are not null then
1497 --get the default receivable_application_id which will be
1498 --used for defaulting or cross-validation
1499 IF p_cm_customer_trx_id IS NOT NULL AND
1500 p_applied_payment_schedule_id IS NOT NULL
1501 THEN
1502 --derive the receivable application id using the CM customer trx id
1503 --and the applied payment schedule id
1504 default_ra_id(
1505 p_cm_customer_trx_id,
1506 p_applied_payment_schedule_id,
1507 p_apply_gl_date,
1508 l_receivable_application_id,
1509 l_ra_return_status);
1510 p_receivable_application_id := l_receivable_application_id;
1511 END IF;
1512
1513 END IF;
1514
1515
1516 END default_unapp_ids;
1517
1518
1519 PROCEDURE Default_unapp_info(
1520 p_receivable_application_id IN NUMBER,
1521 p_apply_gl_date IN DATE,
1522 p_cm_customer_trx_id IN NUMBER,
1523 p_reversal_gl_date IN OUT NOCOPY DATE,
1524 p_cm_gl_date OUT NOCOPY DATE
1525 ) IS
1526 l_cm_customer_trx_id NUMBER(15);
1527 l_apply_gl_date DATE;
1528 BEGIN
1529
1530 IF PG_DEBUG in ('Y', 'C') THEN
1531 arp_util.debug('Default_unapp_info ()+');
1532 END IF;
1533
1534 l_apply_gl_date := p_apply_gl_date;
1535 l_cm_customer_trx_id := p_cm_customer_trx_id;
1536
1537 default_reversal_gl_date(p_receivable_application_id,
1538 p_reversal_gl_date,
1539 l_apply_gl_date,
1540 l_cm_customer_trx_id);
1541
1542 --default the cm gl date which is to be used later
1543 --in the validation of the reversal gl date.
1544 IF p_cm_gl_date IS NULL AND
1545 l_cm_customer_trx_id IS NOT NULL
1546 THEN
1547 BEGIN
1548 SELECT gl_date
1549 INTO p_cm_gl_date
1550 FROM ar_payment_schedules
1551 WHERE customer_trx_id = l_cm_customer_trx_id;
1552 EXCEPTION
1553 WHEN no_data_found THEN
1554 null;
1555 IF PG_DEBUG in ('Y', 'C') THEN
1556 arp_util.debug('Default_unapp_info: ' || 'Could not get the cm_gl_date. ');
1557 END IF;
1558 END;
1559 END IF;
1560 IF PG_DEBUG in ('Y', 'C') THEN
1561 arp_util.debug('Default_unapp_info ()-');
1562 END IF;
1563
1564 END default_unapp_info;
1565
1566 PROCEDURE Validate_unapp_info(
1567 p_cm_gl_date IN DATE,
1568 p_receivable_application_id IN NUMBER,
1569 p_reversal_gl_date IN DATE,
1570 p_apply_gl_date IN DATE,
1571 p_return_status OUT NOCOPY VARCHAR2
1572 ) IS
1573 l_rec_app_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1574 l_rev_gl_date_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1575
1576 BEGIN
1577
1578 p_return_status := FND_API.G_RET_STS_SUCCESS;
1579 IF PG_DEBUG in ('Y', 'C') THEN
1580 arp_util.debug('Validate_unapp_info ()+');
1581 END IF;
1582
1583 --In case the user has entered the receivable application id
1584 -- as well as the receipt and transaction info, then the cross validation
1585 --is done at the defaulting phase itself so no need to do that here.
1586 Validate_ra_id(
1587 p_receivable_application_id,
1588 'APP',
1589 l_rec_app_return_status);
1590
1591 Validate_rev_gl_date( p_reversal_gl_date ,
1592 p_apply_gl_date ,
1593 p_cm_gl_date,
1594 l_rev_gl_date_return_status
1595 );
1596 END validate_unapp_info;
1597
1598 END AR_CM_VAL_PVT;
[Home] [Help]