1 PACKAGE BODY AR_CM_VAL_PVT AS
2 /* $Header: ARXVCMEB.pls 120.0.12000000.1 2007/02/27 12:06:50 mpsingh noship $ */
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 cm.paying_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
323 WHERE rel.party_id = acc.party_id
320 SELECT rel.related_cust_account_id
321 FROM ar_paying_relationships_v rel,
322 hz_cust_accounts acc
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;
477 p_cm_gl_date IN DATE,
474
475 PROCEDURE validate_apply_gl_date(p_apply_gl_date IN DATE,
476 p_inv_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 FND_MESSAGE.set_token( 'GL_DATE', TO_CHAR( p_apply_gl_date ));
512 FND_MSG_PUB.Add;
513 p_return_status := FND_API.G_RET_STS_ERROR;
514 END IF;
515
516
517 END IF;
518
519 IF PG_DEBUG in ('Y', 'C') THEN
520 arp_util.debug('Validate_apply_gl_date ()-');
521 END IF;
522
523
524 END validate_apply_gl_date;
525
526 PROCEDURE validate_amount_applied(
527 p_amount_applied IN NUMBER,
528 p_applied_payment_schedule_id IN NUMBER,
529 p_customer_trx_line_id IN NUMBER,
530 p_inv_line_amount IN NUMBER,
531 p_creation_sign IN VARCHAR2 ,
532 p_allow_overappln_flag IN VARCHAR2,
533 p_natural_appln_only_flag IN VARCHAR2,
534 p_amount_due_remaining IN NUMBER,
535 p_return_status OUT NOCOPY VARCHAR2
536 )
537 IS
538
539 BEGIN
540
541 IF PG_DEBUG in ('Y', 'C') THEN
542 arp_util.debug('Validate_amount_applied ()+');
543 END IF;
544 p_return_status := FND_API.G_RET_STS_SUCCESS;
545
546
547 IF p_amount_applied IS NULL THEN
548 p_return_status := FND_API.G_RET_STS_ERROR;
549 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APPLIED_AMT_NULL');
550 FND_MSG_PUB.Add;
551 return;
552 END IF;
553
554
555 -- Should we check for natural application here???
556
557 IF PG_DEBUG in ('Y', 'C') THEN
558 arp_util.debug('Validate_amount_applied ()-');
559 END IF;
560
561 END validate_amount_applied;
562
563
564
565 FUNCTION Get_trx_ps_id(p_inv_customer_trx_id IN OUT NOCOPY NUMBER,
566 p_installment IN NUMBER,
567 p_return_status OUT NOCOPY VARCHAR2
568 ) RETURN NUMBER IS
569 l_inv_ps_id NUMBER;
570
571 BEGIN
572 IF PG_DEBUG in ('Y', 'C') THEN
573 arp_util.debug('Get_trx_ps_id ()+');
574 END IF;
575 p_return_status := FND_API.G_RET_STS_SUCCESS;
576
577 IF p_installment IS NOT NULL THEN
578 BEGIN
579 SELECT ps.payment_schedule_id
580 INTO l_inv_ps_id
581 FROM ra_customer_trx ct,
582 ar_payment_schedules ps
583 WHERE ct.customer_trx_id = p_inv_customer_trx_id
584 AND ct.customer_trx_id = ps.customer_trx_id
585 --AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
586 AND ps.terms_sequence_number = p_installment
587 ;
588 EXCEPTION
589 WHEN no_data_found THEN
590 IF ar_cm_api_pub.Original_cm_unapp_info.inv_customer_trx_id IS NOT NULL THEN
591 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_ID_INST_INVALID');
592 FND_MSG_PUB.Add;
593 p_return_status := FND_API.G_RET_STS_ERROR;
594 ELSIF ar_cm_api_pub.Original_cm_unapp_info.inv_trx_number IS NOT NULL THEN
595 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_NUM_INST_INVALID');
596 FND_MSG_PUB.Add;
597 p_return_status := FND_API.G_RET_STS_ERROR;
598 END IF;
599
600 END;
601 ELSE
602 --if the user has not entered the installment then if the transaction
603 --has only one installment, get the ps_id for that installment
604 BEGIN
605 SELECT ps.payment_schedule_id
606 INTO l_inv_ps_id
607 FROM ra_customer_trx ct,
608 ar_payment_schedules ps
609 WHERE ct.customer_trx_id = p_inv_customer_trx_id
610 AND ct.customer_trx_id = ps.customer_trx_id
611 --AND ps.class IN ('CB','CM','DEP','DM','INV','BR')
612 ;
613 EXCEPTION
614 WHEN no_data_found THEN
615 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_INVALID');
616 FND_MSG_PUB.Add;
617 p_return_status := FND_API.G_RET_STS_ERROR;
618 WHEN too_many_rows THEN
619 FND_MESSAGE.SET_NAME('AR','AR_RAPI_INSTALL_NULL');
620 FND_MSG_PUB.Add;
621 p_return_status := FND_API.G_RET_STS_ERROR;
622 END;
623
624
628
625 END IF;
626
627 RETURN(l_inv_ps_id);
629 IF PG_DEBUG in ('Y', 'C') THEN
630 arp_util.debug('Get_trx_ps_id ()-');
631 END IF;
632
633 EXCEPTION
634 WHEN others THEN
635 IF PG_DEBUG in ('Y', 'C') THEN
636 arp_util.debug('EXCEPTION: Get_trx_ps_id()');
637 END IF;
638 raise;
639 END Get_trx_ps_id;
640
641 PROCEDURE get_ra_info(
642 p_ra_id IN NUMBER,
643 p_ra_app_ps_id OUT NOCOPY NUMBER,
644 p_inv_customer_trx_id OUT NOCOPY NUMBER,
645 p_apply_gl_date OUT NOCOPY DATE,
646 p_return_status OUT NOCOPY VARCHAR2
647 ) IS
648 CURSOR rec_apppln IS
649 SELECT ra.applied_customer_trx_id, ra.applied_payment_schedule_id, ra.gl_date
650 FROM ar_receivable_applications ra,
651 ar_payment_schedules ps
652 WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
653 AND ra.receivable_application_id = p_ra_id
654 AND ra.display = 'Y'
655 AND ra.status = 'APP'
656 AND ps.reserved_value IS NULL
657 AND ps.reserved_type IS NULL;
658
659 BEGIN
660
661 IF PG_DEBUG in ('Y', 'C') THEN
662 arp_util.debug('get_ra_info ()+');
663 END IF;
664
665 p_return_status := FND_API.G_RET_STS_SUCCESS;
666
667 OPEN rec_apppln;
668 FETCH rec_apppln INTO p_inv_customer_trx_id, p_ra_app_ps_id,p_apply_gl_date;
669 IF rec_apppln%NOTFOUND THEN
670 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_INVALID');
671 FND_MSG_PUB.Add;
672 p_return_status := FND_API.G_RET_STS_ERROR;
673 END IF;
674 CLOSE rec_apppln;
675
676
677 IF PG_DEBUG in ('Y', 'C') THEN
678 arp_util.debug('Get_ra_info ()-');
679 arp_util.debug('Applied PS ID: '|| p_ra_app_ps_id ||
680 'Applied Trx ID: '|| p_inv_customer_trx_id ||
681 'Apply GL date: '|| p_apply_gl_date);
682 END IF;
683
684
685
686 END get_ra_info;
687
688 PROCEDURE default_ra_id(
689 p_cm_customer_trx_id IN NUMBER,
690 p_applied_payment_schedule_id IN NUMBER,
691 p_apply_gl_date OUT NOCOPY DATE,
692 p_receivable_application_id OUT NOCOPY NUMBER,
693 p_return_status OUT NOCOPY VARCHAR2
694 ) IS
698 arp_util.debug('Default_ra_id ()+');
695
696 BEGIN
697 IF PG_DEBUG in ('Y', 'C') THEN
699 END IF;
700 p_return_status := FND_API.G_RET_STS_SUCCESS;
701 IF p_cm_customer_trx_id IS NOT NULL AND
702 p_applied_payment_schedule_id IS NOT NULL
703 THEN
704 SELECT receivable_application_id, gl_date
705 INTO p_receivable_application_id, p_apply_gl_date
706 FROM ar_receivable_applications ra
707 WHERE ra.customer_trx_id = p_cm_customer_trx_id
708 AND ra.applied_payment_schedule_id = p_applied_payment_schedule_id
709 AND ra.display = 'Y'
710 AND ra.status = 'APP'
711 AND ra.application_type = 'CM';
712
713 END IF;
714 IF PG_DEBUG in ('Y', 'C') THEN
715 arp_util.debug('Default_ra_id ()+');
716 END IF;
717 EXCEPTION
718 WHEN no_data_found THEN
719 FND_MESSAGE.SET_NAME('AR','AR_CMAPI_CM_NOT_APP_TO_INV');
720 FND_MSG_PUB.Add;
721 p_return_status := FND_API.G_RET_STS_ERROR;
722 raise;
723 WHEN others THEN
724 IF PG_DEBUG in ('Y', 'C') THEN
725 arp_util.debug('Default_ra_id: ' || 'EXCEPTION: Get_ra_id()');
726 END IF;
727 raise;
728 END default_ra_id;
729
730 PROCEDURE default_reversal_gl_date(
731 p_receivable_application_id IN NUMBER,
732 p_reversal_gl_date IN OUT NOCOPY DATE,
733 p_apply_gl_date IN OUT NOCOPY DATE,
734 p_cm_customer_trx_id IN OUT NOCOPY NUMBER
735 ) IS
736 l_apply_gl_date DATE;
737 l_default_gl_date DATE;
738 l_defaulting_rule_used VARCHAR2(100);
739 l_error_message VARCHAR2(240);
740 BEGIN
741 IF PG_DEBUG in ('Y', 'C') THEN
742 arp_util.debug('Default_reversal_gl_date ()+');
743 END IF;
744
745 l_apply_gl_date := p_apply_gl_date;
746
747 IF p_receivable_application_id IS NOT NULL THEN
748 IF p_apply_gl_date IS NULL THEN
749 --get the gl_date for the application
750 BEGIN
751 SELECT gl_date, customer_trx_id
752 INTO l_apply_gl_date, p_cm_customer_trx_id
753 FROM ar_receivable_applications
754 WHERE receivable_application_id =
755 p_receivable_application_id;
756 EXCEPTION
757 WHEN OTHERS THEN
758 NULL;
759 END;
760 END IF;
761
762 IF p_reversal_gl_date is null THEN
763 IF (arp_util.validate_and_default_gl_date(
764 nvl(l_apply_gl_date,trunc(sysdate)),
765 NULL,
766 l_apply_gl_date,
767 NULL,
768 NULL,
769 NULL,
770 NULL,
771 NULL,
772 'N',
773 NULL,
774 arp_global.set_of_books_id,
775 222,
776 l_default_gl_date,
777 l_defaulting_rule_used,
778 l_error_message) = TRUE) THEN
779
780 p_reversal_gl_date := l_default_gl_date;
781 ELSE
782 --we were not able to default the gl_date put the message
783 --here on the stack, but the return status will be set
784 --to FND_API.G_RET_STS_ERROR in the validation phase.
785 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
786 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', l_error_message);
787 FND_MSG_PUB.Add;
788 END IF;
789 END IF;
790 END IF;
791 IF PG_DEBUG in ('Y', 'C') THEN
792 arp_util.debug('Default_reversal_gl_date ()-');
793 END IF;
794 EXCEPTION
795 When others THEN
796 IF PG_DEBUG in ('Y', 'C') THEN
797 arp_util.debug('EXCEPTION: Default_reversal_gl_date()');
798 END IF;
799 raise;
800 END Default_reversal_gl_date;
801
802
803 PROCEDURE Validate_ra_id(
804 p_receivable_application_id IN NUMBER,
805 p_application_type IN VARCHAR2,
806 p_return_status OUT NOCOPY VARCHAR2) IS
807 l_valid NUMBER;
808 BEGIN
809 p_return_status := FND_API.G_RET_STS_SUCCESS;
810 IF PG_DEBUG in ('Y', 'C') THEN
811 arp_util.debug('Validate_ra_id ()+');
812 END IF;
813 --validate the receivable application id only if it was passed in
814 --directly as a parameter. No need to validate if it was derived.
815 IF p_receivable_application_id IS NOT NULL AND
816 ar_cm_api_pub.original_cm_unapp_info.receivable_application_id IS NOT NULL
817 THEN
818 SELECT count(*)
819 INTO l_valid
820 FROM AR_RECEIVABLE_APPLICATIONS ra
821 WHERE ra.receivable_application_id = p_receivable_application_id
822 and ra.display = 'Y'
823 and ra.status = p_application_type
824 and ra.application_type = 'CM';
825
826 IF l_valid = 0 THEN
827 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_INVALID');
828 FND_MSG_PUB.Add;
829 p_return_status := FND_API.G_RET_STS_ERROR;
830 END IF;
831
832 ELSIF p_receivable_application_id IS NULL THEN
833 IF ar_cm_api_pub.original_cm_unapp_info.cm_trx_number IS NULL AND
834 ar_cm_api_pub.original_cm_unapp_info.cm_customer_trx_id IS NULL AND
835 ar_cm_api_pub.original_cm_unapp_info.applied_ps_id IS NULL AND
836 ar_cm_api_pub.original_cm_unapp_info.inv_customer_trx_id IS NULL AND
837 ar_cm_api_pub.original_cm_unapp_info.inv_trx_number IS NULL
838 THEN
839 --receivable application id is null
840 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REC_APP_ID_NULL');
844 ar_cm_api_pub.original_cm_unapp_info.inv_customer_trx_id IS NULL AND
841 FND_MSG_PUB.Add;
842 p_return_status := FND_API.G_RET_STS_ERROR;
843 ELSIF ar_cm_api_pub.original_cm_unapp_info.inv_trx_number IS NULL AND
845 ar_cm_api_pub.original_cm_unapp_info.applied_ps_id IS NULL AND
846 (ar_cm_api_pub.original_cm_unapp_info.cm_customer_trx_id IS NOT NULL OR
847 ar_cm_api_pub.original_cm_unapp_info.cm_trx_number IS NOT NULL)
848 THEN
849 --the transaction was not specified
850 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_NULL');
851 FND_MSG_PUB.Add;
852 p_return_status := FND_API.G_RET_STS_ERROR;
853 ELSIF (ar_cm_api_pub.original_cm_unapp_info.inv_trx_number IS NOT NULL OR
854 ar_cm_api_pub.original_cm_unapp_info.inv_customer_trx_id IS NOT NULL OR
855 ar_cm_api_pub.original_cm_unapp_info.applied_ps_id IS NOT NULL) AND
856 ar_cm_api_pub.original_cm_unapp_info.cm_customer_trx_id IS NULL AND
857 ar_cm_api_pub.original_cm_unapp_info.cm_trx_number IS NULL
858 THEN
859 --the credit memo was not specified
860 FND_MESSAGE.SET_NAME('AR','AR_CMAPI_CM_TRX_ID_NULL');
861 FND_MSG_PUB.Add;
862 p_return_status := FND_API.G_RET_STS_ERROR;
863 END IF;
864
865 END IF;
866 IF PG_DEBUG in ('Y', 'C') THEN
867 arp_util.debug('Validate_receivable_appln_id ()-');
868 END IF;
869 EXCEPTION
870 WHEN others THEN
871 IF PG_DEBUG in ('Y', 'C') THEN
872 arp_util.debug('EXCEPTION: Validate_ra_id(-)');
873 END IF;
874 raise;
875
876 END Validate_ra_id;
877
878
879 PROCEDURE Validate_Rev_gl_date(p_reversal_gl_date IN DATE,
880 p_apply_gl_date IN DATE,
881 p_cm_gl_date IN DATE,
882 p_return_status OUT NOCOPY VARCHAR2
883 ) IS
884
885 BEGIN
886 IF PG_DEBUG in ('Y', 'C') THEN
887 arp_util.debug('Validate_Rev_gl_date ()+');
888 END IF;
889 p_return_status := FND_API.G_RET_STS_SUCCESS;
890 IF p_reversal_gl_date IS NOT NULL THEN
891
892 IF p_reversal_gl_date < NVL(p_apply_gl_date,p_reversal_gl_date) THEN
893 FND_MESSAGE.SET_NAME('AR','AR_RW_BEFORE_APP_GL_DATE');
894 FND_MESSAGE.SET_TOKEN('GL_DATE', p_apply_gl_date);
895 FND_MSG_PUB.Add;
896 p_return_status := FND_API.G_RET_STS_ERROR;
897 END IF;
898 IF p_reversal_gl_date < nvl(p_cm_gl_date,p_reversal_gl_date) THEN
899 FND_MESSAGE.SET_NAME('AR','AR_RW_BEFORE_CM_GL_DATE');
900 FND_MESSAGE.SET_TOKEN('GL_DATE', p_cm_gl_date);
901 FND_MSG_PUB.Add;
902 p_return_status := FND_API.G_RET_STS_ERROR;
903 END IF;
904
905 IF ( NOT arp_util.is_gl_date_valid(p_reversal_gl_date)) THEN
906 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
907 FND_MESSAGE.set_token( 'GL_DATE', TO_CHAR( p_reversal_gl_date ));
908 FND_MSG_PUB.Add;
909 p_return_status := FND_API.G_RET_STS_ERROR;
910 END IF;
911
912 ELSE
913 FND_MESSAGE.SET_NAME('AR','AR_RAPI_REV_GL_DATE_NULL');
914 FND_MSG_PUB.Add;
915 p_return_status := FND_API.G_RET_STS_ERROR;
916 IF PG_DEBUG in ('Y', 'C') THEN
917 arp_util.debug('Validate_Rev_gl_date: ' || 'The Reversal gl date is null ');
918 END IF;
919 END IF;
920
921 IF PG_DEBUG in ('Y', 'C') THEN
922 arp_util.debug('Validate_Rev_gl_date ()+');
923 END IF;
924 EXCEPTION
925 WHEN others THEN
926 IF PG_DEBUG in ('Y', 'C') THEN
927 arp_util.debug('EXCEPTION: Validate_rev_gl_date() ');
928 END IF;
929 raise;
930 END Validate_Rev_gl_date;
931
932 -- PUBLIC PROCEDURES/FUNCTIONS
933
934 PROCEDURE default_app_ids(
935 p_cm_customer_trx_id IN OUT NOCOPY NUMBER,
936 p_cm_trx_number IN VARCHAR2,
937 p_inv_customer_trx_id IN OUT NOCOPY NUMBER,
938 p_inv_trx_number IN VARCHAR2,
939 p_inv_customer_trx_line_id IN OUT NOCOPY NUMBER,
940 p_inv_line_number IN NUMBER,
941 p_installment IN OUT NOCOPY NUMBER,
942 p_applied_payment_schedule_id IN NUMBER,
943 p_return_status OUT NOCOPY VARCHAR2 )
944
945 IS
946
947
948 CURSOR c_pay_sched IS
949 SELECT customer_trx_id, terms_sequence_number
950 FROM ar_payment_schedules
951 WHERE payment_schedule_id = p_applied_payment_schedule_id and
952 payment_schedule_id >0 and
953 class in ('INV','DM'); -- Should we include DM, DEP, GUAR, CB?
954
955 l_inv_customer_trx_id NUMBER;
956 l_installment NUMBER;
957 p_inv_return_status_lines VARCHAR2(100);
958
959 BEGIN
960
961 IF PG_DEBUG in ('Y', 'C') THEN
962 arp_util.debug('default_app_ids(+)');
963 END IF;
964
965 p_return_status := FND_API.G_RET_STS_SUCCESS;
966
967 -- Step 1: Get a valid value for CM customer_trx_id
968 IF p_cm_trx_number IS NOT NULL THEN
969 default_customer_trx_id(p_cm_customer_trx_id ,
970 p_cm_trx_number ,
971 p_return_status);
972 END IF;
973
974 -- Step 2: Get a valid value for DM customer_trx_id
975 IF p_inv_trx_number IS NOT NULL THEN
976 default_customer_trx_id(p_inv_customer_trx_id ,
977 p_inv_trx_number ,
978 p_return_status);
979 END IF;
980
981 -- Step 3: Get a valid value for DM customer trx line id
985 p_inv_return_status_lines);
982 default_customer_trx_line_id(p_inv_customer_trx_id,
983 p_inv_customer_trx_line_id,
984 p_inv_line_number,
986
987 -- Step 4: Get inv_customer_trx_id from applied_payment_schedule_id and
988 -- installment
989
990 IF p_applied_payment_schedule_id IS NOT NULL THEN
991 OPEN c_pay_sched;
992 FETCH c_pay_sched
993 INTO l_inv_customer_trx_id,
994 l_installment;
995 IF c_pay_sched%NOTFOUND THEN
996 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APP_PS_ID_INVALID');
997 FND_MSG_PUB.Add;
998 p_return_status := FND_API.G_RET_STS_ERROR ;
999 END IF;
1000 CLOSE c_pay_sched;
1001
1002 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
1003 IF nvl(p_inv_customer_trx_id,nvl(l_inv_customer_trx_id,-99)) = nvl(l_inv_customer_trx_id,-99) THEN
1004 p_inv_customer_trx_id := l_inv_customer_trx_id;
1005 END IF;
1006 IF nvl(p_installment,nvl(l_installment,-99)) = nvl(l_installment,-99) THEN
1007 p_installment := l_installment;
1008 END IF;
1009 END IF;
1010 ELSE -- p_payment_schedule_id is null
1011 --default the installment from the customer_trx_id if not entered
1012 IF p_inv_customer_trx_id IS NOT NULL THEN
1013 IF p_installment IS NULL THEN
1014 BEGIN
1015 SELECT terms_sequence_number
1016 INTO p_installment
1017 FROM ar_payment_schedules
1018 WHERE customer_trx_id = p_inv_customer_trx_id;
1019 EXCEPTION
1020 WHEN no_data_found THEN
1021 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUST_TRX_ID_INVALID');
1022 FND_MSG_PUB.Add;
1023 p_return_status := FND_API.G_RET_STS_ERROR;
1024 WHEN too_many_rows THEN
1025 FND_MESSAGE.SET_NAME('AR','AR_RAPI_INSTALL_NULL');
1026 FND_MSG_PUB.Add;
1027 p_return_status := FND_API.G_RET_STS_ERROR;
1028 END;
1029 END IF;
1030 END IF;
1031 END IF;
1032
1033
1034 IF (p_inv_return_status_lines = FND_API.G_RET_STS_ERROR OR
1035 p_return_status = FND_API.G_RET_STS_ERROR) THEN
1036 p_return_status := FND_API.G_RET_STS_ERROR;
1037 END IF;
1038
1039 IF PG_DEBUG in ('Y', 'C') THEN
1040 arp_util.debug('Default_app_ids: ' || 'Defaulted Value for the application ids');
1041 arp_util.debug('Default_app_ids: ' || 'p_cm_customer_trx_id :'||to_char(p_cm_customer_trx_id));
1042 arp_util.debug('Default_app_ids: ' || 'p_inv_customer_trx_id :'||to_char(p_inv_customer_trx_id));
1043 arp_util.debug('Default_appln_ids: ' || 'p_installment :'||to_char(p_installment));
1044 arp_util.debug('Default_app_ids: ' || 'p_applied_payment_schedule_id :'||to_char(p_applied_payment_schedule_id));
1045 arp_util.debug('Default_app_ids ()- ');
1046 END IF;
1047 EXCEPTION
1048 WHEN others THEN
1049 IF PG_DEBUG in ('Y', 'C') THEN
1050 arp_util.debug('EXCEPTION: Default_appln_ids()');
1051 END IF;
1052 RAISE;
1053
1054 END Default_app_ids;
1055
1056
1057 PROCEDURE default_app_info(
1058 p_cm_customer_trx_id IN NUMBER,
1059 p_inv_customer_trx_id IN NUMBER,
1060 p_inv_customer_trx_line_id IN NUMBER,
1061 p_show_closed_invoices IN VARCHAR2,
1062 p_installment IN OUT NOCOPY NUMBER,
1063 p_apply_date IN OUT NOCOPY DATE,
1064 p_apply_gl_date IN OUT NOCOPY DATE,
1065 p_amount_applied IN OUT NOCOPY NUMBER,
1066 p_applied_payment_schedule_id IN OUT NOCOPY NUMBER,
1067 p_cm_gl_date OUT NOCOPY DATE,
1068 p_cm_trx_date OUT NOCOPY DATE,
1069 p_cm_amount_rem OUT NOCOPY NUMBER,
1070 p_cm_currency_code OUT NOCOPY VARCHAR2,
1071 p_inv_due_date OUT NOCOPY DATE,
1072 p_inv_currency_code OUT NOCOPY VARCHAR2,
1073 p_inv_amount_rem OUT NOCOPY NUMBER,
1074 p_inv_trx_date OUT NOCOPY DATE,
1075 p_inv_gl_date OUT NOCOPY DATE,
1076 p_allow_overappln_flag OUT NOCOPY VARCHAR2,
1077 p_natural_appln_only_flag OUT NOCOPY VARCHAR2,
1078 p_creation_sign OUT NOCOPY VARCHAR2,
1079 p_cm_payment_schedule_id OUT NOCOPY NUMBER,
1080 p_inv_line_amount OUT NOCOPY NUMBER,
1081 p_return_status OUT NOCOPY VARCHAR2
1082 )
1083 IS
1084
1085 l_cm_gl_date DATE;
1086 l_cm_amount_rem NUMBER;
1087 l_cm_trx_date DATE;
1088 l_cm_ps_id NUMBER;
1089 l_cm_currency_code fnd_currencies.currency_code%TYPE;
1090 l_cm_customer_id NUMBER;
1091 l_cm_info_return_status VARCHAR2(1);
1092
1093 l_inv_customer_id NUMBER; --customer on transaction
1094 l_inv_cust_trx_type_id NUMBER;
1095 l_inv_due_date DATE;
1096 l_inv_trx_date DATE;
1097 l_inv_gl_date DATE;
1098 l_allow_overappln_flag VARCHAR2(1);
1099 l_natural_appln_only_flag VARCHAR2(1);
1100 l_creation_sign VARCHAR2(1);
1101 --l_applied_payment_schedule_id NUMBER;
1102 l_app_gl_date DATE;
1103 l_inv_amount_rem NUMBER;
1104 l_trx_info_return_status VARCHAR2(1);
1105 l_inv_line_amount NUMBER;
1106
1107 l_inv_currency_code fnd_currencies.currency_code%TYPE;
1108 l_def_amt_return_status VARCHAR2(1);
1109
1110 l_return BOOLEAN;
1111 l_default_gl_date DATE;
1112 l_defaulting_rule_used VARCHAR2(100);
1113 l_error_message VARCHAR2(200);
1114
1115 BEGIN
1116
1117 IF PG_DEBUG in ('Y', 'C') THEN
1121 p_return_status := FND_API.G_RET_STS_SUCCESS;
1118 arp_util.debug('Default_app_info ()+');
1119 END IF;
1120
1122
1123 -- Step 1: Default CM Info:
1124 default_cm_info(
1125 p_cm_customer_trx_id,
1126 l_cm_gl_date,
1127 l_cm_amount_rem,
1128 l_cm_trx_date,
1129 l_cm_ps_id,
1130 l_cm_currency_code,
1131 l_cm_customer_id,
1132 l_cm_info_return_status );
1133
1134 IF PG_DEBUG in ('Y', 'C') THEN
1135 arp_util.debug('Default_app_info: ' || 'Default_CM_Info return status = '||l_cm_info_return_status);
1136 END IF;
1137
1138 -- Step 2: Default DM info
1139
1140 IF l_cm_info_return_status = FND_API.G_RET_STS_SUCCESS THEN
1141 Default_Trx_Info(
1142 p_inv_customer_trx_id ,
1143 p_inv_customer_trx_line_id ,
1144 p_show_closed_invoices ,
1145 l_cm_gl_date ,
1146 l_cm_customer_id ,
1147 l_cm_currency_code ,
1148 l_cm_ps_id ,
1149 l_cm_trx_date ,
1150 --- Out variables
1151 l_inv_customer_id , --customer on transaction
1152 l_inv_cust_trx_type_id ,
1153 l_inv_due_date ,
1154 l_inv_trx_date ,
1155 l_inv_gl_date ,
1156 l_allow_overappln_flag ,
1157 l_natural_appln_only_flag ,
1158 l_creation_sign ,
1159 p_applied_payment_schedule_id ,
1160 l_app_gl_date , --this is the application gl_date
1161 p_installment ,
1162 l_inv_amount_rem ,
1163 l_inv_currency_code ,
1164 l_trx_info_return_status
1165 );
1166
1167
1168 END IF;
1169
1170 IF PG_DEBUG in ('Y', 'C') THEN
1171 arp_util.debug('Default_app_info: ' || 'Default trx info return status = '||l_trx_info_return_status);
1172 arp_util.debug('Applied PS ID : ' || p_applied_payment_schedule_id);
1173 END IF;
1174
1175
1176 -- Step 3: Default apply date
1177
1178 IF p_apply_date IS NULL THEN
1179 p_apply_date := GREATEST(sysdate,
1180 GREATEST(NVL(l_cm_trx_date,sysdate),
1181 NVL(l_inv_trx_date,sysdate)));
1182 END IF;
1183
1184
1185 -- Step 4: Default GL Date
1186
1187 IF p_apply_gl_date IS NULL THEN
1188 l_return :=
1189 arp_util.validate_and_default_gl_date(
1190 gl_date => l_app_gl_date,
1191 trx_date => null,
1192 validation_date1 => null,
1193 validation_date2 => null,
1194 validation_date3 => null,
1195 default_date1 => l_app_gl_date,
1196 default_date2 => null,
1197 default_date3 => null,
1198 p_allow_not_open_flag => 'N',
1199 p_invoicing_rule_id => null,
1200 p_set_of_books_id => arp_global.set_of_books_id,
1201 p_application_id => 222,
1202 default_gl_date => l_default_gl_date ,
1203 defaulting_rule_used => l_defaulting_rule_used,
1204 error_message => l_error_message);
1205
1206 IF l_return = TRUE THEN
1207 p_apply_gl_date := l_default_gl_date;
1208 END IF;
1209
1210 END IF;
1211
1212
1213 -- Step 5: Default amount applied
1214 default_amt_applied(
1215 l_inv_currency_code ,
1216 l_cm_currency_code ,
1217 l_cm_amount_rem ,
1218 l_inv_amount_rem ,
1219 p_amount_applied ,
1220 l_def_amt_return_status
1221 );
1222
1223
1224 p_cm_gl_date := l_cm_gl_date;
1225 p_cm_trx_date := l_cm_trx_date;
1226 p_cm_amount_rem := l_cm_amount_rem;
1227 p_cm_currency_code := l_cm_currency_code;
1228 p_inv_due_date := l_inv_due_date;
1229 p_inv_currency_code := l_inv_currency_code;
1230 p_inv_amount_rem := l_inv_amount_rem;
1231 p_inv_trx_date := l_inv_trx_date;
1232 p_inv_gl_date := l_inv_gl_date;
1233 p_allow_overappln_flag := l_allow_overappln_flag;
1234 p_natural_appln_only_flag := l_natural_appln_only_flag;
1235 p_creation_sign := l_creation_sign;
1236 p_cm_payment_schedule_id := l_cm_ps_id;
1237 p_inv_line_amount := l_inv_line_amount;
1238
1239
1240
1241 IF PG_DEBUG in ('Y', 'C') THEN
1242 arp_util.debug('Default_appl_info: ' || 'Default amount return status :'||l_def_amt_return_status );
1243 END IF;
1244
1245
1246 END default_app_info;
1247
1248
1249 PROCEDURE validate_app_info(
1250 p_apply_date IN DATE,
1251 p_cm_trx_date IN DATE,
1252 p_inv_trx_date IN DATE,
1253 p_apply_gl_date IN DATE,
1254 p_cm_gl_date IN DATE,
1255 p_inv_gl_date IN DATE,
1256 p_amount_applied IN NUMBER,
1257 p_applied_payment_schedule_id IN NUMBER,
1261 p_allow_overappln_flag IN VARCHAR2,
1258 p_customer_trx_line_id IN NUMBER,
1259 p_inv_line_amount IN NUMBER,
1260 p_creation_sign IN VARCHAR2,
1262 p_natural_appln_only_flag IN VARCHAR2,
1263 p_cm_amount_rem IN NUMBER,
1264 p_inv_amount_rem IN NUMBER,
1265 p_cm_currency_code IN VARCHAR2,
1266 p_inv_currency_code IN VARCHAR2,
1267 p_return_status OUT NOCOPY VARCHAR2
1268 ) IS
1269
1270 l_gl_date_return_status VARCHAR2(1);
1271 l_amt_applied_return_status VARCHAR2(1);
1272 l_apply_date_return_status VARCHAR2(1);
1273
1274
1275 BEGIN
1276
1277 -- Validations of cm_customer_trx_id, inv_customer_trx_id,
1278 -- applied_payment_schedule_id are done in defaulting routines
1279
1280 IF PG_DEBUG in ('Y', 'C') THEN
1281 arp_util.debug('Validate_App_info ()+');
1282 END IF;
1283
1284 p_return_status := FND_API.G_RET_STS_SUCCESS;
1285
1286 validate_apply_date(p_apply_date,
1287 p_inv_trx_date,
1288 p_cm_trx_date,
1289 l_apply_date_return_status
1290 );
1291
1292 validate_apply_gl_date(p_apply_gl_date ,
1293 p_inv_gl_date ,
1294 p_cm_gl_date ,
1295 l_gl_date_return_status
1296 );
1297
1298 IF PG_DEBUG in ('Y', 'C') THEN
1299 arp_util.debug('Validate_Application_info: ' || 'Apply gl_date return status :'||l_gl_date_return_status);
1300 END IF;
1301 validate_amount_applied(
1302 p_amount_applied ,
1303 p_applied_payment_schedule_id ,
1304 p_customer_trx_line_id ,
1305 p_inv_line_amount ,
1306 p_creation_sign ,
1307 p_allow_overappln_flag ,
1308 p_natural_appln_only_flag ,
1309 p_inv_amount_rem ,
1310 l_amt_applied_return_status
1311 );
1312 IF l_gl_date_return_status <> FND_API.G_RET_STS_SUCCESS OR
1313 l_amt_applied_return_status <> FND_API.G_RET_STS_SUCCESS OR
1314 l_apply_date_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1315 p_return_status := FND_API.G_RET_STS_ERROR;
1316 END IF;
1317
1318 IF PG_DEBUG in ('Y', 'C') THEN
1319 arp_util.debug('Validate_App_info ()-');
1320 END IF;
1321 EXCEPTION
1322 WHEN others THEN
1323 IF PG_DEBUG in ('Y', 'C') THEN
1324 arp_util.debug('EXCEPTION: Validate_App_Info() ');
1325 END IF;
1326 raise;
1327
1328
1329 END validate_app_info;
1330
1331 PROCEDURE Default_unapp_ids(
1332 p_cm_trx_number IN VARCHAR2,
1333 p_cm_customer_trx_id IN OUT NOCOPY NUMBER,
1334 p_inv_trx_number IN VARCHAR2,
1335 p_inv_customer_trx_id IN OUT NOCOPY NUMBER,
1336 p_receivable_application_id IN OUT NOCOPY NUMBER,
1337 p_installment IN NUMBER,
1338 p_applied_payment_schedule_id IN OUT NOCOPY NUMBER,
1339 p_apply_gl_date OUT NOCOPY DATE,
1340 p_return_status OUT NOCOPY VARCHAR2
1341 ) IS
1342
1343 CURSOR c_pay_sched IS
1344 SELECT customer_trx_id, terms_sequence_number
1345 FROM ar_payment_schedules
1346 WHERE payment_schedule_id = p_applied_payment_schedule_id and
1347 payment_schedule_id >0 and
1348 class in ('INV','DM'); -- Should we include BR, DEP, GUAR, CB?
1349
1350
1351 l_cm_cust_trx_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1352 l_inv_cust_trx_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1353 l_applied_ps_id_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1354 l_inv_customer_trx_id NUMBER;
1355 l_applied_payment_schedule_id NUMBER;
1356 l_ra_app_ps_id NUMBER;
1357 l_receivable_application_id NUMBER;
1358 l_installment NUMBER(15);
1359 l_ra_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1360
1361 BEGIN
1362
1363
1364 IF PG_DEBUG in ('Y', 'C') THEN
1365 arp_util.debug('Default_unapp_ids ()+');
1366 END IF;
1367 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1368
1369 --Step 1: Get a valid value for the CM customer_trx_id
1370 IF p_cm_trx_number IS NOT NULL THEN
1371 Default_customer_trx_id(p_cm_customer_trx_id ,
1372 p_cm_trx_number ,
1373 l_cm_cust_trx_return_status);
1374 END IF;
1375
1376 -- Step 2: Get a valid value for DM customer_trx_id
1377 IF p_inv_trx_number IS NOT NULL THEN
1378 default_customer_trx_id(p_inv_customer_trx_id ,
1379 p_inv_trx_number ,
1380 l_inv_cust_trx_return_status);
1381 END IF;
1382
1383
1384 -- Step 3: Get payment schedule info
1385 --if error is raised in deriving the customer_trx_id from the trx_number,
1386 --do not process the applied_payment_schedule_id any further.
1387 IF l_cm_cust_trx_return_status= FND_API.G_RET_STS_SUCCESS AND
1388 l_inv_cust_trx_return_status= FND_API.G_RET_STS_SUCCESS THEN
1389 IF p_applied_payment_schedule_id IS NOT NULL THEN
1393 l_installment;
1390 OPEN c_pay_sched;
1391 FETCH c_pay_sched
1392 INTO l_inv_customer_trx_id,
1394 IF c_pay_sched%NOTFOUND THEN
1395 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APP_PS_ID_INVALID');
1396 FND_MSG_PUB.Add;
1397 p_return_status := FND_API.G_RET_STS_ERROR ;
1398 END IF;
1399 CLOSE c_pay_sched;
1400
1401 IF p_return_status = FND_API.G_RET_STS_SUCCESS THEN
1402 IF (nvl(p_inv_customer_trx_id,l_inv_customer_trx_id) <> l_inv_customer_trx_id OR
1403 nvl(p_installment,l_installment) <> l_installment) THEN
1404 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_PS_ID_X_INVALID');
1405 FND_MSG_PUB.Add;
1406 p_return_status := FND_API.G_RET_STS_ERROR;
1407 ELSE
1408 p_inv_customer_trx_id := l_inv_customer_trx_id;
1409 END IF;
1410
1411 END IF;
1412 ELSE -- p_applied_ayment_schedule_id is null
1413 IF p_inv_customer_trx_id IS NOT NULL THEN
1414 l_applied_payment_schedule_id :=
1415 Get_trx_ps_id(p_inv_customer_trx_id,
1416 p_installment,
1417 l_applied_ps_id_return_status);
1418 p_applied_payment_schedule_id
1419 :=l_applied_payment_schedule_id;
1420 END IF;
1421 END IF;
1422
1423 END IF;
1424
1425 -- Step 4: get related info for receivable_application_id
1426
1427 IF p_receivable_application_id IS NOT NULL THEN
1428 get_ra_info(p_receivable_application_id,
1429 l_ra_app_ps_id,
1430 l_inv_customer_trx_id,
1431 p_apply_gl_date,
1432 l_ra_return_status);
1433
1434 IF nvl( l_ra_app_ps_id,-99) <> nvl(p_applied_payment_schedule_id,
1435 nvl( l_ra_app_ps_id,-99))
1436 THEN
1437 IF ar_cm_api_pub.original_cm_unapp_info.inv_customer_trx_id IS NOT NULL OR
1438 ar_cm_api_pub.original_cm_unapp_info.inv_trx_number IS NOT NULL THEN
1439 FND_MESSAGE.SET_NAME('AR','AR_RAPI_TRX_RA_ID_X_INVALID');
1440 FND_MSG_PUB.Add;
1441 p_return_status := FND_API.G_RET_STS_ERROR;
1442 ELSIF ar_cm_api_pub.original_cm_unapp_info.applied_ps_id IS NOT NULL THEN
1443 FND_MESSAGE.SET_NAME('AR','AR_RAPI_APP_PS_RA_ID_X_INVALID');
1444 FND_MSG_PUB.Add;
1445 p_return_status := FND_API.G_RET_STS_ERROR;
1446 END IF;
1447 ELSE
1448 p_applied_payment_schedule_id := l_ra_app_ps_id;
1449 END IF;
1450
1451 IF nvl(l_inv_customer_trx_id,-99) <> nvl(p_inv_customer_trx_id,nvl(l_inv_customer_trx_id,-99)) THEN
1452 --Invalid receivable application identifier for the entered
1453 -- invoice customer trx id
1454 FND_MESSAGE.SET_NAME('AR','AR_RAPI_RCPT_RA_ID_X_INVALID');
1455 FND_MSG_PUB.Add;
1456 p_return_status := FND_API.G_RET_STS_ERROR;
1457 ELSE
1458 p_inv_customer_trx_id := l_inv_customer_trx_id;
1459 END IF;
1460 ELSE --the user has not passed in the receivable application id
1461 --
1462 -- derive receivable_application_id
1463 --
1464 --If app_ps_id and the cash_receipt_id are not null then
1465 --get the default receivable_application_id which will be
1466 --used for defaulting or cross-validation
1467 IF p_cm_customer_trx_id IS NOT NULL AND
1468 p_applied_payment_schedule_id IS NOT NULL
1469 THEN
1470 --derive the receivable application id using the CM customer trx id
1471 --and the applied payment schedule id
1472 default_ra_id(
1473 p_cm_customer_trx_id,
1474 p_applied_payment_schedule_id,
1475 p_apply_gl_date,
1476 l_receivable_application_id,
1477 l_ra_return_status);
1478 p_receivable_application_id := l_receivable_application_id;
1479 END IF;
1480
1481 END IF;
1482
1483
1484 END default_unapp_ids;
1485
1486
1487 PROCEDURE Default_unapp_info(
1488 p_receivable_application_id IN NUMBER,
1489 p_apply_gl_date IN DATE,
1490 p_cm_customer_trx_id IN NUMBER,
1491 p_reversal_gl_date IN OUT NOCOPY DATE,
1492 p_cm_gl_date OUT NOCOPY DATE
1493 ) IS
1494 l_cm_customer_trx_id NUMBER(15);
1495 l_apply_gl_date DATE;
1496 BEGIN
1497
1498 IF PG_DEBUG in ('Y', 'C') THEN
1499 arp_util.debug('Default_unapp_info ()+');
1500 END IF;
1501
1502 l_apply_gl_date := p_apply_gl_date;
1503 l_cm_customer_trx_id := p_cm_customer_trx_id;
1504
1505 default_reversal_gl_date(p_receivable_application_id,
1506 p_reversal_gl_date,
1507 l_apply_gl_date,
1508 l_cm_customer_trx_id);
1509
1510 --default the cm gl date which is to be used later
1511 --in the validation of the reversal gl date.
1512 IF p_cm_gl_date IS NULL AND
1513 l_cm_customer_trx_id IS NOT NULL
1514 THEN
1515 BEGIN
1516 SELECT gl_date
1517 INTO p_cm_gl_date
1518 FROM ar_payment_schedules
1519 WHERE customer_trx_id = l_cm_customer_trx_id;
1520 EXCEPTION
1521 WHEN no_data_found THEN
1522 null;
1523 IF PG_DEBUG in ('Y', 'C') THEN
1524 arp_util.debug('Default_unapp_info: ' || 'Could not get the cm_gl_date. ');
1525 END IF;
1526 END;
1527 END IF;
1528 IF PG_DEBUG in ('Y', 'C') THEN
1529 arp_util.debug('Default_unapp_info ()-');
1530 END IF;
1531
1532 END default_unapp_info;
1533
1534 PROCEDURE Validate_unapp_info(
1535 p_cm_gl_date IN DATE,
1536 p_receivable_application_id IN NUMBER,
1537 p_reversal_gl_date IN DATE,
1538 p_apply_gl_date IN DATE,
1539 p_return_status OUT NOCOPY VARCHAR2
1540 ) IS
1541 l_rec_app_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1542 l_rev_gl_date_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1543
1544 BEGIN
1545
1546 p_return_status := FND_API.G_RET_STS_SUCCESS;
1547 IF PG_DEBUG in ('Y', 'C') THEN
1548 arp_util.debug('Validate_unapp_info ()+');
1549 END IF;
1550
1551 --In case the user has entered the receivable application id
1552 -- as well as the receipt and transaction info, then the cross validation
1553 --is done at the defaulting phase itself so no need to do that here.
1554 Validate_ra_id(
1555 p_receivable_application_id,
1556 'APP',
1557 l_rec_app_return_status);
1558
1559 Validate_rev_gl_date( p_reversal_gl_date ,
1560 p_apply_gl_date ,
1561 p_cm_gl_date,
1562 l_rev_gl_date_return_status
1563 );
1564 END validate_unapp_info;
1565
1566 END AR_CM_VAL_PVT;
[Home] [Help]