[Home] [Help]
PACKAGE BODY: APPS.AR_ADJVALIDATE_PUB
Source
1 PACKAGE BODY AR_ADJVALIDATE_PUB AS
2 /* $Header: ARTAADVB.pls 120.4.12020000.4 2013/02/05 11:33:07 vpotti ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) :='AR_ADJVALIDATE_PUB';
4
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
8 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
9 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
10
11 G_caching_done varchar2(1) := FND_API.G_FALSE;
12 G_cache_date date := NULL ;
13 G_receivables_name ar_receivables_trx.name%type := NULL;
14
15 TYPE Context_Rec_Type IS RECORD
16 (
17 validation_level NUMBER ,
18 ussgl_option fnd_profile_option_values.profile_option_value%type,
19 override_activity_option fnd_profile_option_values.profile_option_value%type,
20 unique_seq_numbers fnd_profile_option_values.profile_option_value%type
21 );
22
23 TYPE Lookup_Rec_Type IS RECORD
24 (
25 lookup_code ar_lookups.lookup_code%type
26 ) ;
27 TYPE Approval_Cache_Tbl_type IS
28 TABLE OF Lookup_Rec_Type
29 INDEX BY BINARY_INTEGER;
30 TYPE Adjtype_Cache_Tbl_type IS
31 TABLE OF Lookup_Rec_Type
32 INDEX BY BINARY_INTEGER;
33 TYPE Adjreason_Cache_Tbl_type IS
34 TABLE OF Lookup_Rec_Type
35 INDEX BY BINARY_INTEGER;
36
37 TYPE Rcvtrx_Rec_Type IS RECORD
38 (
39 receivables_trx_id ar_receivables_trx.RECEIVABLES_TRX_ID%type,
40 name ar_receivables_trx.NAME%type,
41 code_combination_id ar_receivables_trx.CODE_COMBINATION_ID%type
42 ) ;
43 TYPE Rcvtrx_Cache_Tbl_type IS
44 TABLE OF Rcvtrx_Rec_Type
45 INDEX BY BINARY_INTEGER;
46
47
48 TYPE Ussgl_Rec_Type IS RECORD
49 (
50 Ussgl_code gl_ussgl_transaction_codes.ussgl_transaction_code%type,
51 Ussgl_context gl_ussgl_transaction_codes.context%type
52 ) ;
53 TYPE Ussgl_Cache_Tbl_Type IS
54 TABLE OF Ussgl_Rec_Type
55 INDEX BY BINARY_INTEGER;
56
57
58 TYPE Glperiod_Rec_Type IS RECORD
59 (
60 start_date gl_period_statuses.start_date%type,
61 end_date gl_period_statuses.end_date%type
62 ) ;
63 TYPE Glperiod_Cache_Tbl_Type IS
64 TABLE OF GLperiod_Rec_Type
65 INDEX BY BINARY_INTEGER;
66
67
68 TYPE Ccid_Rec_Type IS RECORD
69 (
70 dummy varchar2(1)
71 );
72 TYPE CCid_Cache_Tbl_Type IS
73 TABLE OF Ccid_Rec_Type
74 INDEX BY BINARY_INTEGER;
75
76 G_APPROVAL_TBL Approval_Cache_Tbl_Type;
77 G_REASON_TBL Adjreason_Cache_Tbl_Type;
78 G_ADJTYPE_TBL Adjtype_Cache_Tbl_Type;
79 G_RCVTRX_TBL Rcvtrx_Cache_Tbl_type;
80 G_USSGL_TBL Ussgl_Cache_Tbl_Type;
81 G_GLPERIOD_TBL Glperiod_Cache_Tbl_Type;
82 G_CCID_TBL Ccid_Cache_Tbl_type;
83
84 G_CONTEXT_REC Context_Rec_Type;
85
86 G_CCID_CACHE_SIZE BINARY_INTEGER := 1000;
87 G_GLPERIOD_CACHE_SIZE BINARY_INTEGER := 1000;
88
89
90 /*===========================================================================+
91 | PROCEDURE aapi_message |
92 | |
93 | DESCRIPTION This function is the message utility function used for |
94 | messaging in the Adjustment API |
95 | |
96 | SCOPE - PRIVATE |
97 | |
98 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
99 | |
100 | ARGUMENTS : IN: |
101 | OUT: |
102 | IN/ OUT: |
103 | |
104 | RETURNS : NONE |
105 | |
106 | NOTES |
107 | |
108 | MODIFICATION HISTORY |
109 | Vivek Halder 11-JUL-97 Created |
110 | |
111 +===========================================================================*/
112
113 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
114
115 PROCEDURE aapi_message (
116 p_application_name IN varchar2,
117 p_message_name IN varchar2,
118 p_token1_name IN varchar2 default NULL,
119 p_token1_value IN varchar2 default NULL,
120 p_token2_name IN varchar2 default NULL,
121 p_token2_value IN varchar2 default NULL,
122 p_token3_name IN varchar2 default NULL,
123 p_token3_value IN varchar2 default NULL,
124 p_msg_level IN number default FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
125 ) IS
126 l_mesg varchar2(2000);
127 l_msg_count number;
128 l_msg_data varchar2(2000);
129 l_app_name varchar2(30);
130 l_message_name varchar2(32);
131 BEGIN
132
133 IF PG_DEBUG in ('Y', 'C') THEN
134 arp_util.debug('aapi_message()+' , G_MSG_HIGH);
135 END IF;
136
137 FND_MESSAGE.SET_NAME (p_application_name, p_message_name);
138
139 IF ( p_token1_name IS NOT NULL )
140 THEN
141 FND_MESSAGE.SET_TOKEN ( p_token1_name, p_token1_value ) ;
142 END IF ;
143
144 IF ( p_token2_name IS NOT NULL )
145 THEN
146 FND_MESSAGE.SET_TOKEN ( p_token2_name, p_token2_value ) ;
147 END IF ;
148
149 IF ( p_token3_name IS NOT NULL )
150 THEN
151 FND_MESSAGE.SET_TOKEN ( p_token3_name, p_token3_value ) ;
152 END IF ;
153
154 FND_MSG_PUB.ADD ;
155
156 IF PG_DEBUG in ('Y', 'C') THEN
157 arp_util.debug ('aapi_message()-' , G_MSG_HIGH);
158 END IF;
159
160 RETURN;
161
162 EXCEPTION
163
164 WHEN OTHERS THEN
165 IF PG_DEBUG in ('Y', 'C') THEN
166 arp_util.debug('EXCEPTION: aapi_message()', G_MSG_UERROR);
167 END IF;
168 /*-----------------------------------------------+
169 | Set unexpected error message and status |
170 +-----------------------------------------------*/
171 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'aapi_message');
172
173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
174
175 END aapi_message;
176
177 /*===========================================================================+
178 | PROCEDURE |
179 | Init_Context_Rec |
180 | |
181 | DESCRIPTION |
182 | Initializes the context record that is passed into most of |
183 | the other functions. Many of its values are set when the |
184 | context variable is instantiated. |
185 | |
186 | SCOPE - PUBLIC |
187 | |
188 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
189 | |
190 | ARGUMENTS : IN: |
191 | p_validation_level |
192 | OUT: |
193 | p_return_status |
194 | IN/ OUT: |
195 | |
196 | |
197 | RETURNS : NONE |
198 | |
199 | NOTES |
200 | |
201 | MODIFICATION HISTORY |
202 | Vivek Halder 06-JUN-97 |
203 | |
204 +===========================================================================*/
205
206 PROCEDURE Init_Context_Rec(
207 p_validation_level IN VARCHAR2,
208 p_return_status IN OUT NOCOPY varchar2
209 ) IS
210
211
212 BEGIN
213 IF PG_DEBUG in ('Y', 'C') THEN
214 arp_util.debug('Init_Context_Rec()+' , G_MSG_HIGH);
215 END IF;
216
217
218 /*---------------------------+
219 | Set the validation level |
220 +----------------------------*/
221
222 g_context_rec.validation_level := p_validation_level;
223
224 /*-------------------------------------------------------+
225 | Set the profile options for USSGL, DOCUMENT SEQUENCES |
226 | and the OVERRIDE ACTIVITY option |
227 +-------------------------------------------------------*/
228
229 g_context_rec.ussgl_option :=
230 rtrim(FND_PROFILE.VALUE( 'USSGL_OPTION' ));
231
232 g_context_rec.unique_seq_numbers :=
233 rtrim(FND_PROFILE.VALUE('UNIQUE:SEQ_NUMBERS'));
234
235 g_context_rec.override_activity_option :=
236 rtrim(FND_PROFILE.VALUE( 'AR_OVERRIDE_ADJUSTMENT_ACTIVITY_ACCOUNT'));
237
238
239 p_return_status := FND_API.G_RET_STS_SUCCESS;
240
241 IF PG_DEBUG in ('Y', 'C') THEN
242 arp_util.debug('Init_Context_Rec()-' , G_MSG_HIGH);
243 END IF;
244
245 EXCEPTION
246 WHEN OTHERS THEN
247 IF PG_DEBUG in ('Y', 'C') THEN
248 arp_util.debug('EXCEPTION: Init_Context_Rec() ', G_MSG_UERROR);
249 END IF;
250
251 FND_MSG_PUB.Add_Exc_Msg (
252 G_PKG_NAME,
253 'Init_Context_Rec'
254 );
255 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
256 RETURN;
257
258 END Init_Context_Rec;
259
260 /*===========================================================================+
261 | PROCEDURE Cache_Gl_Periods |
262 | |
263 | DESCRIPTION This function is called during start_up to fetch the |
264 | opened, future-enterable in a pl/sql table. |
265 | |
266 | SCOPE - PRIVATE |
267 | |
268 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
269 | |
270 | ARGUMENTS : IN: |
271 | OUT: p_return_status |
272 | IN/ OUT: |
273 | |
274 | RETURNS : NONE |
275 | |
276 | NOTES |
277 | |
278 | MODIFICATION HISTORY |
279 | Vivek halder 10-JUL-97 Created |
280 | |
281 +===========================================================================*/
282
283 PROCEDURE Cache_Gl_Periods (p_return_status IN OUT NOCOPY VARCHAR2 )
284 IS
285
286 l_set_of_books_id ar_system_parameters.set_of_books_id%type;
287
288 CURSOR l_periods_csr IS
289 SELECT trunc(g.start_date) start_date,
290 trunc(g.end_date) end_date
291 FROM gl_period_statuses g,
292 gl_sets_of_books b
293 WHERE g.application_id = 222
294 AND g.set_of_books_id = l_set_of_books_id
295 AND g.set_of_books_id = b.set_of_books_id
296 AND g.period_type = b.accounted_period_type
297 AND g.adjustment_period_flag = 'N'
298 AND g.closing_status IN ('O','F') ;
299
300
301 l_index BINARY_INTEGER default 0;
302 l_temp_rec Glperiod_Rec_Type;
303
304 BEGIN
305
306 IF PG_DEBUG in ('Y', 'C') THEN
307 arp_util.debug('Cache_Gl_Periods()+' , G_MSG_HIGH);
308 END IF;
309
310 p_return_status := FND_API.G_RET_STS_SUCCESS;
311
312 SELECT set_of_books_id
313 INTO l_set_of_books_id
314 FROM ar_system_parameters ;
315
316 l_index := 0 ;
317 FOR l_temp_rec IN l_periods_csr LOOP
318 l_index := l_index + 1;
319 IF ( l_index > G_GLPERIOD_CACHE_SIZE )
320 THEN
321 EXIT ;
322 END IF;
323 G_GLPERIOD_TBL(l_index) := l_temp_rec;
324 END LOOP;
325
326 IF PG_DEBUG in ('Y', 'C') THEN
327 arp_util.debug ('Cache_Gl_Periods: ' || 'G_GLPERIOD_TBL count = '|| to_char(g_glperiod_tbl.count), G_MSG_HIGH);
328 arp_util.debug ('Cache_Gl_Periods()-' , G_MSG_HIGH);
329 END IF;
330
331 RETURN;
332
333 EXCEPTION
334 WHEN NO_DATA_FOUND THEN
335 IF PG_DEBUG in ('Y', 'C') THEN
336 arp_util.debug('Cache_Gl_Periods: ' || 'No Open/Future Enterable GL periods exist',G_MSG_HIGH);
337 END IF;
338 /*-----------------------------------------------+
339 | Set the message |
340 +-----------------------------------------------*/
341 aapi_message(
342 p_application_name =>'AR',
343 p_message_name => 'AR_AAPI_NO_OPEN_FUTURE_PERIOD',
344 p_token1_name => 'SET_OF_BOOKS_ID',
345 p_token1_value => to_char(arp_global.set_of_books_id)
346 ) ;
347 p_return_status := FND_API.G_RET_STS_ERROR;
348 RETURN;
349 WHEN OTHERS THEN
350 IF PG_DEBUG in ('Y', 'C') THEN
351 arp_util.debug('EXCEPTION: Cache_Gl_Periods()', G_MSG_UERROR);
352 END IF;
353 /*-----------------------------------------------+
354 | Set unexpected error message and status |
355 +-----------------------------------------------*/
356 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Gl_Periods');
357 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
358 RETURN;
359
360 END Cache_Gl_Periods;
361
362
363 /*===========================================================================+
364 | PROCEDURE Cache_Approval_Type |
365 | |
366 | DESCRIPTION This function is called during start_up to fetch the |
367 | approval codes in a pl/sql table. |
368 | |
369 | SCOPE - PRIVATE |
370 | |
371 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
372 | |
373 | ARGUMENTS : IN: |
374 | OUT: p_return_status |
375 | IN/ OUT: |
376 | |
377 | RETURNS : NONE |
378 | |
379 | NOTES |
380 | |
381 | MODIFICATION HISTORY |
382 | Vivek Halder 10-JUL-97 Created |
383 | |
384 +===========================================================================*/
385
386 PROCEDURE Cache_Approval_Type (p_return_status IN OUT NOCOPY VARCHAR2 )
387 IS
388
389 CURSOR l_lookup_csr IS
390 SELECT lookup_code
391 FROM ar_lookups
392 WHERE lookup_type = 'APPROVAL_TYPE'
393 AND enabled_flag = 'Y'
394 AND sysdate BETWEEN nvl(start_date_active,sysdate)
395 AND nvl(end_date_active,sysdate) ;
396
397 l_index BINARY_INTEGER default 0;
398 l_temp_rec Lookup_Rec_Type;
399
400 BEGIN
401
402 IF PG_DEBUG in ('Y', 'C') THEN
403 arp_util.debug('Cache_Approval_Type()+' , G_MSG_HIGH);
404 END IF;
405
406 p_return_status := FND_API.G_RET_STS_SUCCESS;
407
408 FOR l_temp_rec IN l_lookup_csr LOOP
409 l_index := l_index + 1;
410 G_APPROVAL_TBL(l_index) := l_temp_rec;
411 END LOOP;
412
413 IF PG_DEBUG in ('Y', 'C') THEN
414 arp_util.debug ('Cache_Approval_Type: ' || 'G_APPROVAL_TBL count = '|| to_char(G_APPROVAL_TBL.count), G_MSG_HIGH);
415 arp_util.debug ('Cache_Approval_Type()-' , G_MSG_HIGH);
416 END IF;
417
418 RETURN;
419
420 EXCEPTION
421 WHEN NO_DATA_FOUND THEN
422 IF PG_DEBUG in ('Y', 'C') THEN
423 arp_util.debug('Cache_Approval_Type: ' || 'No Approval Codes exist ', G_MSG_HIGH);
424 END IF;
425 /*-----------------------------------------------+
426 | Set the message |
427 +-----------------------------------------------*/
428 aapi_message(
429 p_application_name =>'AR',
430 p_message_name => 'AR_AAPI_NO_APPROVAL_CODES'
431 ) ;
432 p_return_status := FND_API.G_RET_STS_ERROR ;
433 RETURN;
434
435 WHEN OTHERS THEN
436 IF PG_DEBUG in ('Y', 'C') THEN
437 arp_util.debug('EXCEPTION: Cache_Approval_Type()', G_MSG_UERROR);
438 END IF;
439 /*-----------------------------------------------+
440 | Set unexpected error message and status |
441 +-----------------------------------------------*/
442 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Approval_Type');
443 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
444 RETURN;
445
446 END Cache_Approval_Type;
447
448 /*==========================================================================+
449 | PROCEDURE Cache_Adjustment_Type |
450 | |
451 | DESCRIPTION This function is called during start_up to fetch the |
452 | types in a pl/sql table. |
453 | |
454 | SCOPE - PRIVATE |
455 | |
456 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
457 | |
458 | ARGUMENTS : IN: |
459 | OUT: p_return_status |
460 | IN/ OUT: |
461 | |
462 | RETURNS : NONE |
463 | |
464 | NOTES |
465 | |
466 | MODIFICATION HISTORY |
467 | Vivek Halder 11-JUL-97 Created |
468 | |
469 +===========================================================================*/
470
471 PROCEDURE Cache_Adjustment_Type (p_return_status IN OUT NOCOPY VARCHAR2 )
472 IS
473
474 CURSOR l_lookup_csr IS
475 SELECT lookup_code
476 FROM ar_lookups
477 WHERE lookup_type = 'ADJUSTMENT_TYPE'
478 AND enabled_flag = 'Y'
479 AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
480 trunc(sysdate))
481 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
482
483 l_index BINARY_INTEGER default 0;
484 l_temp_rec Lookup_Rec_Type;
485
486 BEGIN
487
488 IF PG_DEBUG in ('Y', 'C') THEN
489 arp_util.debug('Cache_Adjustment_Type()+' , G_MSG_HIGH);
490 END IF;
491
492 p_return_status := FND_API.G_RET_STS_SUCCESS;
493
494 FOR l_temp_rec IN l_lookup_csr LOOP
495 l_index := l_index + 1;
496 G_ADJTYPE_TBL(l_index) := l_temp_rec;
497 END LOOP;
498
499 IF PG_DEBUG in ('Y', 'C') THEN
500 arp_util.debug ('Cache_Adjustment_Type: ' || 'G_ADJTYPE_TBL count = '|| to_char(G_ADJTYPE_TBL.count), G_MSG_HIGH);
501 arp_util.debug ('Cache_Adjustment_Type()-' , G_MSG_HIGH);
502 END IF;
503
504 RETURN;
505
506 EXCEPTION
507 WHEN NO_DATA_FOUND THEN
508 IF PG_DEBUG in ('Y', 'C') THEN
509 arp_util.debug('Cache_Adjustment_Type: ' || 'No Adjustment Type codes', G_MSG_HIGH);
510 END IF;
511 /*-----------------------------------------------+
512 | Set the message |
513 +-----------------------------------------------*/
514 aapi_message(
515 p_application_name =>'AR',
516 p_message_name => 'AR_AAPI_NO_TYPE_CODES'
517 ) ;
518 p_return_status := FND_API.G_RET_STS_ERROR ;
519 RETURN;
520
521 WHEN OTHERS THEN
522 IF PG_DEBUG in ('Y', 'C') THEN
523 arp_util.debug('EXCEPTION: Cache_Adjustment_Type()', G_MSG_UERROR);
524 END IF;
525 /*-----------------------------------------------+
526 | Set unexpected error message and status |
527 +-----------------------------------------------*/
528 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Adjustment_Type');
529 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
530 RETURN;
531
532 END Cache_Adjustment_Type;
533
534 /*==========================================================================+
535 | PROCEDURE Cache_Adjustment_Reason |
536 | |
537 | DESCRIPTION This function is called during start_up to fetch the |
538 | adjustment reason codes in a pl/sql table. |
539 | |
540 | SCOPE - PRIVATE |
541 | |
542 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
543 | |
544 | ARGUMENTS : IN: |
545 | OUT: p_return_status |
546 | IN/ OUT: |
547 | |
548 | RETURNS : NONE |
549 | |
550 | NOTES |
551 | |
552 | MODIFICATION HISTORY |
553 | Vivek Halder 11-JUL-97 Created |
554 | |
555 +===========================================================================*/
556
557 PROCEDURE Cache_Adjustment_Reason (p_return_status IN OUT NOCOPY VARCHAR2 )
558 IS
559
560 CURSOR l_lookup_csr IS
561 SELECT lookup_code
562 FROM ar_lookups
563 WHERE lookup_type = 'ADJUST_REASON'
564 AND enabled_flag = 'Y'
565 AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
566 trunc(sysdate))
567 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
568
569 l_index BINARY_INTEGER default 0;
570 l_temp_rec Lookup_Rec_Type;
571
572 BEGIN
573
574 IF PG_DEBUG in ('Y', 'C') THEN
575 arp_util.debug('Cache_Adjustment_Reason()+' , G_MSG_HIGH);
576 END IF;
577
578 p_return_status := FND_API.G_RET_STS_SUCCESS;
579
580 FOR l_temp_rec IN l_lookup_csr LOOP
581 l_index := l_index + 1;
582 G_REASON_TBL(l_index) := l_temp_rec;
583 END LOOP;
584
585 IF PG_DEBUG in ('Y', 'C') THEN
586 arp_util.debug ('Cache_Adjustment_Reason: ' || 'G_REASON_TBL count = '|| to_char(G_REASON_TBL.count), G_MSG_HIGH);
587 arp_util.debug ('Cache_Adjustment_Reason()-' , G_MSG_HIGH);
588 END IF;
589
590 RETURN;
591
592 EXCEPTION
593 WHEN NO_DATA_FOUND THEN
594 IF PG_DEBUG in ('Y', 'C') THEN
595 arp_util.debug('Cache_Adjustment_Reason: ' || 'No Adjustment Reason codes', G_MSG_HIGH);
596 END IF;
597 /*-----------------------------------------------+
598 | Set the message |
599 +-----------------------------------------------*/
600 aapi_message(
601 p_application_name =>'AR',
602 p_message_name => 'AR_AAPI_NO_REASON_CODES'
603 ) ;
604 p_return_status := FND_API.G_RET_STS_ERROR ;
605 RETURN;
606
607 WHEN OTHERS THEN
608 IF PG_DEBUG in ('Y', 'C') THEN
609 arp_util.debug('EXCEPTION: Cache_Adjustment_Reason()', G_MSG_UERROR);
610 END IF;
611 /*-----------------------------------------------+
612 | Set unexpected error message and status |
613 +-----------------------------------------------*/
614 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Adjustment_Reason');
615 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
616 RETURN;
617
618 END Cache_Adjustment_Reason;
619
620
621 /*==========================================================================+
622 | PROCEDURE Cache_Receivables_Trx |
623 | |
624 | DESCRIPTION This function is called during start_up to fetch the |
625 | Receivables Trx codes in a pl/sql table. |
626 | |
627 | SCOPE - PRIVATE |
628 | |
629 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
630 | |
631 | ARGUMENTS : IN: |
632 | OUT: p_return_status |
633 | IN/ OUT: |
634 | |
635 | RETURNS : NONE |
636 | |
637 | NOTES |
638 | |
639 | MODIFICATION HISTORY |
640 | Vivek Halder 11-JUL-97 Created |
641 | Satheesh Nambiar 31-May-00 Bug 1290698.Included type ENDORSEMENT for |
642 | BOE/BR
643 | |
644 +===========================================================================*/
645
646 PROCEDURE Cache_Receivables_Trx (p_return_status IN OUT NOCOPY VARCHAR2 )
647 IS
648
649 CURSOR l_receivables_csr IS
650 SELECT receivables_trx_id,name,code_combination_id
651 FROM ar_receivables_trx
652 WHERE nvl(status,'A') = 'A'
653 AND type in ('ADJUST','ENDORSEMENT')
654 AND receivables_trx_id not in ( -1,-11,-12,-13 )
655 AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
656 trunc(sysdate))
657 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
658
659
660 l_index BINARY_INTEGER default 0;
661 l_temp_rec Rcvtrx_Rec_Type;
662
663 BEGIN
664
665 IF PG_DEBUG in ('Y', 'C') THEN
666 arp_util.debug('Cache_Receivables_Trx()+' , G_MSG_HIGH);
667 END IF;
668
669 p_return_status := FND_API.G_RET_STS_SUCCESS;
670
671 FOR l_temp_rec IN l_receivables_csr LOOP
672 l_index := l_index + 1;
673 G_RCVTRX_TBL(l_index) := l_temp_rec;
674 END LOOP;
675
676 IF PG_DEBUG in ('Y', 'C') THEN
677 arp_util.debug ('Cache_Receivables_Trx: ' || 'G_RCVTRX_TBL count = '|| to_char(G_RCVTRX_TBL.count), G_MSG_HIGH);
678 arp_util.debug ('Cache_Receivables_Trx()-' , G_MSG_HIGH);
679 END IF;
680
681 RETURN;
682
683 EXCEPTION
684 WHEN NO_DATA_FOUND THEN
685 IF PG_DEBUG in ('Y', 'C') THEN
686 arp_util.debug('Cache_Receivables_Trx: ' || 'No Adjustment Reason codes', G_MSG_HIGH);
687 END IF;
688 /*-----------------------------------------------+
689 | Set the message |
690 +-----------------------------------------------*/
691 aapi_message(
692 p_application_name =>'AR',
693 p_message_name => 'AR_AAPI_NO_RECEIVABLES_TRX'
694 ) ;
695 p_return_status := FND_API.G_RET_STS_ERROR ;
696 RETURN;
697
698 WHEN OTHERS THEN
699 IF PG_DEBUG in ('Y', 'C') THEN
700 arp_util.debug('EXCEPTION: Cache_Receivables_Trx()', G_MSG_UERROR);
701 END IF;
702 /*-----------------------------------------------+
703 | Set unexpected error message and status |
704 +-----------------------------------------------*/
705 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Receivables_Trx');
706 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
707 RETURN;
708
709 END Cache_Receivables_Trx;
710
711 /*===========================================================================+
712 | PROCEDURE Cache_Ussgl_code |
713 | |
714 | DESCRIPTION This function is called during start_up to fetch the |
715 | Ussgl codes in a pl/sql table. |
716 | |
717 | SCOPE - PRIVATE |
718 | |
719 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
720 | |
721 | ARGUMENTS : IN: |
722 | OUT: p_return_status |
723 | IN/ OUT: |
724 | |
725 | RETURNS : NONE |
726 | |
727 | NOTES |
728 | |
729 | MODIFICATION HISTORY |
730 | Vivek Halder 11-JUL-97 Created |
731 | |
732 +===========================================================================*/
733
734 PROCEDURE Cache_Ussgl_Code (p_return_status IN OUT NOCOPY VARCHAR2 )
735 IS
736
737 CURSOR l_ussgl_csr IS
738 SELECT ussgl_transaction_code,context
739 FROM gl_ussgl_transaction_codes
740 WHERE chart_of_accounts_id = arp_global.chart_of_accounts_id
741 AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
742 trunc(sysdate))
743 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
744
745
746 l_index BINARY_INTEGER default 0;
747 l_temp_rec Ussgl_Rec_Type;
748
749 BEGIN
750
751 IF PG_DEBUG in ('Y', 'C') THEN
752 arp_util.debug('Cache_Ussgl_Code()+' , G_MSG_HIGH);
753 END IF;
754
755 p_return_status := FND_API.G_RET_STS_SUCCESS;
756
757 /*-----------------------------------------------+
758 | Load the USSGL based on profile option |
759 +-----------------------------------------------*/
760
761 IF ( g_context_rec.ussgl_option <> 'Y' )
762 THEN
763 RETURN ;
764 END IF;
765
766 FOR l_temp_rec IN l_ussgl_csr LOOP
767 l_index := l_index + 1;
768 G_USSGL_TBL(l_index) := l_temp_rec;
769 END LOOP;
770
771 IF PG_DEBUG in ('Y', 'C') THEN
772 arp_util.debug ('Cache_Ussgl_Code: ' || 'G_USSGL_TBL count = '|| to_char(G_USSGL_TBL.count), G_MSG_HIGH);
773 arp_util.debug ('Cache_Ussgl_Code()-' , G_MSG_HIGH);
774 END IF;
775
776 RETURN;
777
778 EXCEPTION
779 WHEN NO_DATA_FOUND THEN
780 IF PG_DEBUG in ('Y', 'C') THEN
781 arp_util.debug('Cache_Ussgl_Code: ' || 'No USSGL codes', G_MSG_HIGH);
782 END IF;
783 /*-----------------------------------------------+
784 | Set the message |
785 +-----------------------------------------------*/
786 aapi_message(
787 p_application_name =>'AR',
788 p_message_name => 'AR_AAPI_NO_USSGL_CODES'
789 ) ;
790 p_return_status := FND_API.G_RET_STS_SUCCESS ;
791 RETURN;
792
793 WHEN OTHERS THEN
794 IF PG_DEBUG in ('Y', 'C') THEN
795 arp_util.debug('EXCEPTION: Cache_Ussgl_Code()', G_MSG_UERROR);
796 END IF;
797 /*-----------------------------------------------+
798 | Set unexpected error message and status |
799 +-----------------------------------------------*/
800 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Ussgl_Code');
801 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
802 RETURN;
803
804 END Cache_Ussgl_code;
805
806 /*===========================================================================+
807 | PROCEDURE Cache_Code_Combination |
808 | |
809 | DESCRIPTION This function is called during start_up to fetch the |
810 | Code Combination Ids in a pl/sql table. |
811 | |
812 | SCOPE - PRIVATE |
813 | |
814 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
815 | |
816 | ARGUMENTS : IN: |
817 | OUT: p_return_status |
818 | IN/ OUT: |
819 | |
820 | RETURNS : NONE |
821 | |
822 | NOTES |
823 | |
824 | MODIFICATION HISTORY |
825 | Vivek Halder 11-JUL-97 Created |
826 | |
827 +===========================================================================*/
828
829 PROCEDURE Cache_Code_Combination (p_return_status IN OUT NOCOPY VARCHAR2 )
830 IS
831
832 CURSOR l_ccid_csr IS
833 SELECT code_combination_id
834 FROM gl_code_combinations
835 WHERE chart_of_accounts_id = arp_global.chart_of_accounts_id
836 AND enabled_flag = 'Y'
837 AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
838 trunc(sysdate))
839 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
840
841
842 l_index BINARY_INTEGER default 0;
843
844 TYPE ccid_rec IS RECORD
845 (
846 code_combination_id gl_code_combinations.code_combination_id%type
847 ) ;
848
849 l_ccid_rec ccid_rec;
850
851 BEGIN
852
853 IF PG_DEBUG in ('Y', 'C') THEN
854 arp_util.debug('Cache_Code_Combination()+' , G_MSG_HIGH);
855 END IF;
856
857 p_return_status := FND_API.G_RET_STS_SUCCESS;
858
859 FOR l_ccid_rec IN l_ccid_csr LOOP
860 l_index := l_index + 1 ;
861 IF ( l_index > G_CCID_CACHE_SIZE )
862 THEN
863 EXIT;
864 END IF;
865 G_CCID_TBL(l_ccid_rec.code_combination_id).dummy := FND_API.G_TRUE;
866 END LOOP;
867
868 IF PG_DEBUG in ('Y', 'C') THEN
869 arp_util.debug ('Cache_Code_Combination: ' || 'G_CCID_TBL count = '|| to_char(G_CCID_TBL.count), G_MSG_HIGH);
870 arp_util.debug ('Cache_Code_Combination()-' , G_MSG_HIGH);
871 END IF;
872
873 RETURN;
874
875 EXCEPTION
876 WHEN NO_DATA_FOUND THEN
877 IF PG_DEBUG in ('Y', 'C') THEN
878 arp_util.debug('Cache_Code_Combination: ' || 'No USSGL codes', G_MSG_HIGH);
879 END IF;
880 /*-----------------------------------------------+
881 | Set the message |
882 +-----------------------------------------------*/
883 aapi_message(
884 p_application_name =>'AR',
885 p_message_name => 'AR_AAPI_NO_CCID'
886 ) ;
887 p_return_status := FND_API.G_RET_STS_ERROR ;
888 RETURN;
889
890 WHEN OTHERS THEN
891 IF PG_DEBUG in ('Y', 'C') THEN
892 arp_util.debug('EXCEPTION: Cache_Code_Combination()', G_MSG_UERROR);
893 END IF;
894 /*-----------------------------------------------+
895 | Set unexpected error message and status |
896 +-----------------------------------------------*/
897 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Code_Combination');
898 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
899 RETURN;
900
901 END Cache_Code_Combination;
902
903
904
905 /*===========================================================================+
906 | PROCEDURE |
907 | Cache_Details |
908 | |
909 | DESCRIPTION |
910 | Caches data when it is first used so that values can easily |
911 | be accessed later and need not be fetched from the database |
912 | for future transactions. |
913 | |
914 | The following tables are cached |
915 | - ar_lookups for type = APPROVAL_TYPE |
916 | - ar_lookups for type = ADJUSTMENT_TYPE |
917 | - ar_lookups for type = ADJUSTMENT_REASON |
918 | - ussgl transaction codes |
919 | - receivables trx for type = 'ADJUST' |
920 | - code combination ids |
921 | - gl periods |
922 | |
923 | SCOPE - PUBLIC |
924 | |
925 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
926 | cache_approval_type(); |
927 | cache_adjustment_type(); |
928 | cache_adjustment_reason(); |
929 | cache_ussgl_code(); |
930 | cache_receivables_trx(); |
931 | cache_gl_periods(); |
932 | cache_code_combination(); |
933 | |
934 | ARGUMENTS : IN: |
935 | OUT: |
936 | IN/ OUT: p_return_status |
937 | |
938 | RETURNS : NONE |
939 | |
940 | NOTES |
941 | |
942 | MODIFICATION HISTORY |
943 | Vivek Halder 05-JUL-97 Created |
944 | |
945 +===========================================================================*/
946
947 PROCEDURE Cache_Details (
948 p_return_status IN OUT NOCOPY varchar2
949 ) IS
950
951 l_tobe_cached_flag varchar2(1) ;
952
953 BEGIN
954
955 IF PG_DEBUG in ('Y', 'C') THEN
956 arp_util.debug('Cache_Details()+' , G_MSG_HIGH);
957 END IF;
958
959 /*--------------------------------------------------+
960 | Check if caching is needed. Caching is needed if|
961 | g_caching_done is FALSE or sysdate > cache_date |
962 +--------------------------------------------------*/
963
964 l_tobe_cached_flag := FND_API.G_FALSE ;
965
966 IF PG_DEBUG in ('Y', 'C') THEN
967 arp_util.debug ('Cache_Details: ' || 'G_Caching done = ' || G_caching_done, G_MSG_HIGH);
968 arp_util.debug ('Cache_Details: ' || 'G_Cache_date = ' || to_char(G_cache_date,'DD-MON-YY'),
969 G_MSG_HIGH);
970 arp_util.debug ('Cache_Details: ' || 'Sysdate = ' || to_char(sysdate,'DD-MON-YY'),G_MSG_HIGH);
971 END IF;
972
973 IF ( G_caching_done = FND_API.G_FALSE )
974 THEN
975 l_tobe_cached_flag := FND_API.G_TRUE ;
976 G_cache_date := trunc(sysdate) ;
977 ELSE
978 IF ( G_cache_date < trunc(sysdate) )
979 THEN
980 l_tobe_cached_flag := FND_API.G_TRUE ;
981 G_cache_date := trunc(sysdate) ;
982 END IF;
983 END IF;
984
985 l_tobe_cached_flag := FND_API.G_TRUE;
986 IF PG_DEBUG in ('Y', 'C') THEN
987 arp_util.debug ('Cache_Details: ' || 'Caching Flag : '|| l_tobe_cached_flag,G_MSG_HIGH);
988 END IF;
989
990 IF ( l_tobe_cached_flag = FND_API.G_FALSE )
991 THEN
992 p_return_status := FND_API.G_RET_STS_SUCCESS;
993 RETURN ;
994 ELSE
995 /*-------------------------------------------------+
996 | Initialise the PL/SQL cache tables |
997 +-------------------------------------------------*/
998 G_APPROVAL_TBL.DELETE;
999 G_REASON_TBL.DELETE;
1000 G_ADJTYPE_TBL.DELETE;
1001 G_RCVTRX_TBL.DELETE;
1002 G_USSGL_TBL.DELETE;
1003 G_GLPERIOD_TBL.DELETE;
1004 G_CCID_TBL.DELETE;
1005 END IF;
1006
1007 /*-------------------------------------------------+
1008 | Cache Approval type. To be used for validation |
1009 | of status |
1010 +-------------------------------------------------*/
1011
1012 ar_adjvalidate_pub.cache_approval_type (p_return_status);
1013
1014 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
1015 THEN
1016 RETURN ;
1017 END IF;
1018 /*-------------------------------------------------+
1019 | Cache reason codes for adjustment. To be used |
1020 | for validation of adjustment reason codes |
1021 +-------------------------------------------------*/
1022
1023 ar_adjvalidate_pub.cache_adjustment_reason (p_return_status);
1024 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
1025 THEN
1026 RETURN ;
1027 END IF;
1028
1029 /*-------------------------------------------------+
1030 | Cache adjustment types i.e. INVOICE, LINE etc. |
1031 | To be used for validation of type |
1032 +-------------------------------------------------*/
1033
1034 ar_adjvalidate_pub.cache_adjustment_type (p_return_status);
1035 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
1036 THEN
1037 RETURN ;
1038 END IF;
1039
1040 /*-------------------------------------------------+
1041 | Cache Receivables transaction ids. To be used |
1042 | for validation of Receivables trx id |
1043 +-------------------------------------------------*/
1044
1045 ar_adjvalidate_pub.cache_receivables_trx (p_return_status) ;
1046
1047 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
1048 THEN
1049 RETURN ;
1050 END IF;
1051
1052 /*-------------------------------------------------+
1053 | Cache USSGL transaction information. To be used|
1054 | for validation of USSGL transaction code |
1055 +-------------------------------------------------*/
1056
1057 ar_adjvalidate_pub.cache_ussgl_code (p_return_status);
1058 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
1059 THEN
1060 RETURN ;
1061 END IF;
1062
1063 /*--------------------------------------------------+
1064 | Cache GL periods. To be used to validate if GL |
1065 | dates lie within open or future enterable period|
1066 +--------------------------------------------------*/
1067
1068 ar_adjvalidate_pub.cache_gl_periods(p_return_status);
1069 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
1070 THEN
1071 RETURN ;
1072 END IF;
1073
1074 /*--------------------------------------------------+
1075 | Cache Code combination Ids. To be used to |
1076 | validate input provided by user |
1077 +--------------------------------------------------*/
1078
1079 ar_adjvalidate_pub.cache_code_combination (p_return_status);
1080 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
1081 THEN
1082 RETURN ;
1083 END IF;
1084
1085 G_caching_done := FND_API.G_TRUE ;
1086
1087 IF PG_DEBUG in ('Y', 'C') THEN
1088 arp_util.debug('Cache_Details ()-' , G_MSG_HIGH);
1089 END IF;
1090
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 IF PG_DEBUG in ('Y', 'C') THEN
1094 arp_util.debug('EXCEPTION: Cache_Details() ', G_MSG_UERROR);
1095 END IF;
1096
1097 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Details');
1098 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1099 RETURN;
1100
1101 END Cache_Details;
1102
1103 /*===========================================================================+
1104 | PROCEDURE |
1105 | Within_approval_limits |
1106 | |
1107 | DESCRIPTION |
1108 | This routine checks if the amount is within the approval |
1109 | limits of the user |
1110 | |
1111 | |
1112 | SCOPE - PRIVATE |
1113 | |
1114 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1115 | arp_util.disable_debug |
1116 | arp_util.enable_debug |
1117 | fnd_api.g_exc_unexpected_error |
1118 | fnd_api.g_ret_sts_error |
1119 | fnd_api.g_ret_sts_error |
1120 | fnd_api.g_ret_sts_success |
1121 | fnd_api.to_boolean |
1122 | |
1123 | ARGUMENTS : IN: |
1124 | p_adj_rec |
1125 | p_inv_curr_code |
1126 | OUT: |
1127 | p_return_status |
1128 | |
1129 | IN/ OUT: |
1130 | p_approved_flag |
1131 | |
1132 | RETURNS : NONE |
1133 | |
1134 | NOTES |
1135 | |
1136 | MODIFICATION HISTORY |
1137 | Vivek Halder 13-JUN-97 |
1138 | |
1139 +===========================================================================*/
1140
1141 PROCEDURE Within_approval_limits(
1142 p_adj_amount IN ar_adjustments.amount%type,
1143 p_inv_curr_code IN ar_payment_schedules.invoice_currency_code%type,
1144 p_approved_flag IN OUT NOCOPY varchar2,
1145 p_return_status IN OUT NOCOPY varchar2
1146 ) IS
1147
1148 l_user_id ar_approval_user_limits.user_id%type;
1149 l_approval_amount_to ar_approval_user_limits.amount_to%type;
1150 l_approval_amount_from ar_approval_user_limits.amount_from%type;
1151
1152 BEGIN
1153
1154 IF PG_DEBUG in ('Y', 'C') THEN
1155 arp_util.debug('Within_approval_limits()+', G_MSG_MEDIUM);
1156 END IF;
1157
1158 /*------------------------------------------+
1159 | Initialize the return status to SUCCESS |
1160 +------------------------------------------*/
1161
1162 p_return_status := FND_API.G_RET_STS_SUCCESS;
1163
1164 p_approved_flag := FND_API.G_TRUE ;
1165
1166 /*------------------------------------------+
1167 | Get the user Id |
1168 +------------------------------------------*/
1169
1170 l_user_id := FND_GLOBAL.USER_ID ;
1171
1172 BEGIN
1173 SELECT amount_to,
1174 amount_from
1175 INTO l_approval_amount_to,
1176 l_approval_amount_from
1177 FROM ar_approval_user_limits
1178 WHERE user_id = l_user_id
1179 AND currency_code = p_inv_curr_code;
1180
1181 EXCEPTION
1182 WHEN NO_DATA_FOUND THEN
1183
1184 IF PG_DEBUG in ('Y', 'C') THEN
1185 arp_util.debug ('Within_approval_limits: ' ||
1186 'User does not have approval limits for currency ' ||
1187 p_inv_curr_code, G_MSG_HIGH
1188 );
1189 END IF;
1190 aapi_message(
1191 p_application_name =>'AR',
1192 p_message_name => 'AR_VAL_USER_LIMIT',
1193 p_token1_name => 'CURRENCY',
1194 p_token1_value => p_inv_curr_code
1195 ) ;
1196 p_approved_flag := FND_API.G_FALSE;
1197
1198 WHEN OTHERS THEN
1199
1200 IF PG_DEBUG in ('Y', 'C') THEN
1201 arp_util.debug ('EXCEPTION: Within_approval_limits',G_MSG_UERROR);
1202 END IF;
1203
1204 /*-------------------------------------------------+
1205 | Set unexpected error message, status and return |
1206 +-------------------------------------------------*/
1207 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Within_approval_limits');
1208 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1209 RETURN;
1210 END;
1211
1212 /*-----------------------------------------------+
1213 | Ensure that approval data has been selected |
1214 +-----------------------------------------------*/
1215
1216 IF ( p_approved_flag = FND_API.G_TRUE )
1217 THEN
1218
1219 /*--------------------------------------------+
1220 | Perform actual check of approval limits. |
1221 +--------------------------------------------*/
1222
1223 IF (( p_adj_amount > l_approval_amount_to ) OR
1224 ( p_adj_amount < l_approval_amount_from ))
1225 THEN
1226 IF PG_DEBUG in ('Y', 'C') THEN
1227 arp_util.debug('Within_approval_limits: ' || 'User ID: ' || l_user_id ||
1228 ' Amount: ' || p_adj_amount ||
1229 ' From: ' || l_approval_amount_from ||
1230 ' To: ' || l_approval_amount_to ||
1231 ' exceeds approval limit', G_MSG_HIGH );
1232 END IF;
1233 /*--------------------------------------+
1234 | Add a message. But do not signal error|
1235 +--------------------------------------*/
1236
1237 aapi_message(
1238 p_application_name =>'AR',
1239 p_message_name => 'AR_VAL_AMT_APPROVAL_LIMIT'
1240 ) ;
1241 p_approved_flag := FND_API.G_FALSE;
1242
1243 END IF;
1244
1245 END IF;
1246
1247 IF PG_DEBUG in ('Y', 'C') THEN
1248 arp_util.debug('Within_approval_limits()-', G_MSG_HIGH);
1249 END IF;
1250 RETURN ;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254
1255 IF PG_DEBUG in ('Y', 'C') THEN
1256 arp_util.debug('EXCEPTION: Within_approval_limits()', G_MSG_UERROR);
1257 END IF;
1258 /*-----------------------------------------------+
1259 | Set unexpected error message and status |
1260 +-----------------------------------------------*/
1261 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Within_approval_limits'
1262 );
1263 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1264 RETURN;
1265
1266 END Within_approval_limits;
1267
1268 /*===========================================================================+
1269 | PROCEDURE |
1270 | Validate_Type |
1271 | |
1272 | DESCRIPTION |
1273 | This routine validates the type of adjustment |
1274 | |
1275 | |
1276 | |
1277 | SCOPE - PUBLIC |
1278 | |
1279 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1280 | arp_util.disable_debug |
1281 | arp_util.enable_debug |
1282 | fnd_api.g_exc_unexpected_error |
1283 | fnd_api.g_ret_sts_error |
1284 | fnd_api.g_ret_sts_error |
1285 | fnd_api.g_ret_sts_success |
1286 | fnd_api.to_boolean |
1287 | |
1288 | ARGUMENTS : IN: |
1289 | p_adj_rec |
1290 | |
1291 | OUT: |
1292 | |
1293 | IN/ OUT: |
1294 | p_return_status |
1295 | |
1296 | RETURNS : NONE |
1297 | |
1298 | NOTES |
1299 | |
1300 | MODIFICATION HISTORY |
1301 | Vivek Halder 13-JUN-97 |
1302 | |
1303 +===========================================================================*/
1304
1305 PROCEDURE Validate_Type (
1306 p_adj_rec IN ar_adjustments%rowtype,
1307 p_return_status IN OUT NOCOPY varchar2
1308 ) IS
1309
1310 l_index number;
1311
1312 BEGIN
1313
1314 IF PG_DEBUG in ('Y', 'C') THEN
1315 arp_util.debug('Validate_Type()+', G_MSG_HIGH);
1316 END IF;
1317
1318 /*------------------------------------------+
1319 | Initialize the return status to ERROR |
1320 +------------------------------------------*/
1321
1322 p_return_status := FND_API.G_RET_STS_ERROR;
1323
1324 FOR l_index IN 1..G_ADJTYPE_TBL.COUNT LOOP
1325
1326 IF (p_adj_rec.type = G_ADJTYPE_TBL(l_index).lookup_code)
1327 THEN
1328 p_return_status := FND_API.G_RET_STS_SUCCESS;
1329 EXIT ;
1330 END IF;
1331
1332 END LOOP;
1333
1334 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
1335 THEN
1336 /*-----------------------------------------------+
1337 | Set the message |
1338 +-----------------------------------------------*/
1339 aapi_message(
1340 p_application_name =>'AR',
1341 p_message_name => 'AR_AAPI_INVALID_ADJUSTMENT_TYPE',
1342 p_token1_name => 'TYPE',
1343 p_token1_value => p_adj_rec.type
1344 ) ;
1345 RETURN ;
1346
1347 END IF;
1348
1349 IF PG_DEBUG in ('Y', 'C') THEN
1350 arp_util.debug('Validate_Type()-', G_MSG_MEDIUM);
1351 END IF;
1352
1353 RETURN ;
1354
1355 EXCEPTION
1356 WHEN OTHERS THEN
1357
1358 IF PG_DEBUG in ('Y', 'C') THEN
1359 arp_util.debug('EXCEPTION: Validate_Type()', G_MSG_UERROR );
1360 arp_util.debug('Validate_Type for type = ' ||p_adj_rec.type,G_MSG_HIGH);
1361 END IF;
1362
1363 /*-----------------------------------------------+
1364 | Set unexpected error message and status |
1365 +-----------------------------------------------*/
1366 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Type' );
1367
1368 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1369
1370 RETURN;
1371
1372 END Validate_Type;
1373
1374 /*===========================================================================+
1375 | PROCEDURE |
1376 | Validate_Payschd |
1377 | |
1378 | DESCRIPTION |
1379 | This routine validates the payment schedule id of the |
1380 | transaction for which the adjustment is to be created |
1381 | In case it is valid it populates the customer_trx_id |
1382 | and customer id in the adjustment record. |
1383 | It also validates the customer trx line Id if type = 'LINE' |
1384 | |
1385 | SCOPE - PUBLIC |
1386 | |
1387 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1388 | fnd_api.g_exc_unexpected_error |
1389 | fnd_api.g_ret_sts_error |
1390 | fnd_api.g_ret_sts_success |
1391 | |
1392 | ARGUMENTS : IN: |
1393 | |
1394 | OUT: |
1395 | p_return_status |
1396 | |
1397 | IN/ OUT: |
1398 | p_adj_rec |
1399 | p_ps_rec |
1400 | |
1401 | |
1402 | RETURNS : NONE |
1403 | |
1404 | NOTES |
1405 | |
1406 | MODIFICATION HISTORY |
1407 | Vivek Halder 30-JUNE-97 Created |
1408 | Satheesh Nambiar 01-Jun-00 Bug 1290698 Added one more class 'BR' to |
1409 | PS check for BOE/BR |
1410 | |
1411 +===========================================================================*/
1412
1413 PROCEDURE Validate_Payschd (
1414 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
1415 p_ps_rec IN OUT NOCOPY ar_payment_schedules%rowtype,
1416 p_return_status OUT NOCOPY Varchar2
1417 ) IS
1418
1419 l_index BINARY_INTEGER ;
1420 l_count number:= 0 ;
1421
1422 BEGIN
1423
1424 IF PG_DEBUG in ('Y', 'C') THEN
1425 arp_util.debug('Validate_Payschd()+', G_MSG_MEDIUM);
1426 END IF;
1427
1428 /*------------------------------------------+
1429 | Initialize the return status to SUCCESS |
1430 +------------------------------------------*/
1431
1432 p_return_status := FND_API.G_RET_STS_SUCCESS;
1433
1434 /*-----------------------------------------------+
1435 | Check if the payment schedule Id is 0 or null |
1436 | If so return with failure |
1437 +-----------------------------------------------*/
1438
1439 IF ( p_adj_rec.payment_schedule_id IS NULL or
1440 p_adj_rec.payment_schedule_id <= 0 )
1441 THEN
1442
1443 /*-----------------------------------------------+
1444 | Set the message and return |
1445 +-----------------------------------------------*/
1446 aapi_message(
1447 p_application_name =>'AR',
1448 p_message_name => 'AR_AAPI_INVALID_PAYSCHD',
1449 p_token1_name => 'PAYMENT_SCHEDULE_ID',
1450 p_token1_value => to_char(p_adj_rec.payment_schedule_id)
1451 ) ;
1452 p_return_status := FND_API.G_RET_STS_ERROR;
1453 RETURN;
1454
1455 END IF ;
1456
1457 /*--------------------------------------------------+
1458 | Check if the payment schedule Id exists. Get the |
1459 | Customer Id and Customer Trx Id |
1460 +--------------------------------------------------*/
1461
1462 BEGIN
1463
1464 SELECT *
1465 INTO p_ps_rec
1466 FROM ar_payment_schedules
1467 WHERE payment_schedule_id = p_adj_rec.payment_schedule_id;
1468
1469 EXCEPTION
1470 WHEN NO_DATA_FOUND THEN
1471
1472 /*-----------------------------------------------+
1473 | Payment schedule Id does not exist |
1474 | Set the message and status accordingly |
1475 +-----------------------------------------------*/
1476 aapi_message(
1477 p_application_name =>'AR',
1478 p_message_name => 'AR_AAPI_INVALID_PAYSCHD',
1479 p_token1_name => 'PAYMENT_SCHEDULE_ID',
1480 p_token1_value => to_char(p_adj_rec.payment_schedule_id)
1481 ) ;
1482 p_return_status := FND_API.G_RET_STS_ERROR;
1483 RETURN ;
1484 END ;
1485
1486 /*-----------------------------------------------+
1487 | Check that the class of transaction is valid |
1488 +-----------------------------------------------*/
1489 /*---------------------------------------------------------+
1490 | Bug 1290698 Added one more class 'BR' for BOE/BR project|
1491 +----------------------------------------------------------*/
1492 IF ( p_ps_rec.class NOT IN ( 'INV','DM','CM','DEP','GUAR','BR') )
1493 THEN
1494 aapi_message(
1495 p_application_name =>'AR',
1496 p_message_name => 'AR_AAPI_INVALID_TRX_CLASS',
1497 p_token1_name => 'CLASS',
1498 p_token1_value => p_ps_rec.class
1499 ) ;
1500 p_return_status := FND_API.G_RET_STS_ERROR;
1501 RETURN;
1502 END IF;
1503
1504 /*-----------------------------------------------+
1505 | Check that the Customer Trx Id exists in the |
1506 | payment schedule record. If not, return error |
1507 +-----------------------------------------------*/
1508
1509 IF ( p_ps_rec.customer_trx_id IS NULL OR p_ps_rec.customer_trx_id = 0 )
1510 THEN
1511
1512 /*-----------------------------------------------+
1513 | Set the message accordingly |
1514 +-----------------------------------------------*/
1515 aapi_message(
1516 p_application_name =>'AR',
1517 p_message_name => 'AR_AAPI_NO_CUSTOMER_TRX_ID',
1518 p_token1_name => 'PAYMENT_SCHEDULE_ID',
1519 p_token1_value => to_char(p_adj_rec.payment_schedule_id)
1520 ) ;
1521 p_return_status := FND_API.G_RET_STS_ERROR;
1522 RETURN ;
1523
1524 END IF;
1525
1526 /*-----------------------------------------------+
1527 | Check that the Customer Id exists in the |
1528 | payment schedule record. If not, return error |
1529 +-----------------------------------------------*/
1530
1531 IF ( p_ps_rec.customer_id IS NULL OR p_ps_rec.customer_id = 0 )
1532 THEN
1533
1534 /*-----------------------------------------------+
1535 | Set the message accordingly |
1536 +-----------------------------------------------*/
1537 aapi_message(
1538 p_application_name =>'AR',
1539 p_message_name => 'AR_AAPI_NO_CUSTOMER_ID',
1540 p_token1_name => 'PAYMENT_SCHEDULE_ID',
1541 p_token1_value => to_char(p_adj_rec.payment_schedule_id)
1542 ) ;
1543 p_return_status := FND_API.G_RET_STS_ERROR;
1544 RETURN ;
1545
1546 END IF;
1547
1548 /*-----------------------------------------------+
1549 | Since the validation is successful populate |
1550 | the customer_trx_id and customer_id in adj rec|
1551 +-----------------------------------------------*/
1552
1553 p_adj_rec.customer_trx_id := p_ps_rec.customer_trx_id ;
1554
1555 /*-----------------------------------------------+
1556 | Check if the customer trx line Id is there if |
1557 | the Invoice type is LINE |
1558 +-----------------------------------------------*/
1559
1560 IF p_adj_rec.type = 'LINE'
1561 THEN
1562 IF ( nvl(p_adj_rec.customer_trx_line_id,0) <> 0 ) THEN
1563 l_count := 0 ;
1564 BEGIN
1565 SELECT count(*)
1566 INTO l_count
1567 FROM RA_CUSTOMER_TRX_LINES
1568 WHERE customer_trx_id = p_adj_rec.customer_trx_id AND
1569 customer_trx_line_id = p_adj_rec.customer_trx_line_id ;
1570
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573 IF PG_DEBUG in ('Y', 'C') THEN
1574 arp_util.debug('Validate_Payschd: ' ||
1575 'EXCEPTION: Validate_Payschd() for CustTrxLineId = '||
1576 to_char(p_adj_rec.customer_trx_line_id), G_MSG_HIGH);
1577 END IF;
1578
1579 /*-----------------------------------------------+
1580 | Set unexpected error message and status |
1581 +-----------------------------------------------*/
1582 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Payschd');
1583 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1584 RETURN;
1585 END ;
1586
1587 IF ( l_count <> 1 ) THEN
1588 /*-----------------------------------------------+
1589 | Set error message and status |
1590 +-----------------------------------------------*/
1591 aapi_message(
1592 p_application_name =>'AR',
1593 p_message_name => 'AR_AAPI_INV_CUST_TRX_LINE_ID',
1594 p_token1_name => 'CUSTOMER_TRX_LINE_ID',
1595 p_token1_value => to_char(p_adj_rec.customer_trx_line_id),
1596 p_token2_name => 'CUSTOMER_TRX_ID',
1597 p_token2_value => to_char(p_adj_rec.customer_trx_id)
1598 ) ;
1599 p_return_status := FND_API.G_RET_STS_ERROR;
1600 RETURN ;
1601
1602 END IF ;
1603
1604 END IF ;
1605
1606 ELSE
1607 /*-----------------------------------------------+
1608 | The Customer Trx Line Id should not be there |
1609 +-----------------------------------------------*/
1610
1611 IF ( p_adj_rec.customer_trx_line_id IS NOT NULL OR
1612 p_adj_rec.customer_trx_line_id <> 0 )
1613 THEN
1614 aapi_message(
1615 p_application_name =>'AR',
1616 p_message_name => 'AR_AAPI_LINE_ID_FOR_NONLINE',
1617 p_token1_name => 'CUSTOMER_TRX_LINE_ID',
1618 p_token1_value => to_char(p_adj_rec.customer_trx_line_id),
1619 p_token2_name => 'TYPE',
1620 p_token2_value => p_ps_rec.class
1621 ) ;
1622 p_return_status := FND_API.G_RET_STS_ERROR;
1623 RETURN ;
1624 END IF;
1625
1626 END IF;
1627
1628 IF PG_DEBUG in ('Y', 'C') THEN
1629 arp_util.debug('Validate_Payschd()-', G_MSG_MEDIUM);
1630 END IF;
1631
1632 RETURN ;
1633
1634
1635 EXCEPTION
1636
1637 WHEN OTHERS THEN
1638
1639 IF PG_DEBUG in ('Y', 'C') THEN
1640 arp_util.debug('EXCEPTION: Validate_Payschd() ', G_MSG_UERROR);
1641 arp_util.debug('Validate_Payschd: ' || 'Payment Schedule = ' ||
1642 p_adj_rec.payment_schedule_id, G_MSG_HIGH);
1643 END IF;
1644 /*-----------------------------------------------+
1645 | Set unexpected error message and status |
1646 +-----------------------------------------------*/
1647 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Validate_Payschd' );
1648 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1649 RETURN;
1650
1651 END Validate_Payschd;
1652
1653
1654 /*===========================================================================+
1655 | PROCEDURE |
1656 | Validate_Amount |
1657 | |
1658 | DESCRIPTION |
1659 | This routine validates the adjustment amount and status. It |
1660 | checks for the user approval limits, validates status and |
1661 | set the adjustment status accordingly. |
1662 | |
1663 | |
1664 | SCOPE - PUBLIC |
1665 | |
1666 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1667 | arp_util.disable_debug |
1668 | arp_util.enable_debug |
1669 | fnd_api.g_exc_unexpected_error |
1670 | fnd_api.g_ret_sts_error |
1671 | fnd_api.g_ret_sts_error |
1672 | fnd_api.g_ret_sts_success |
1673 | fnd_api.to_boolean |
1674 | |
1675 | ARGUMENTS : IN: |
1676 | p_ps_rec |
1677 | OUT: |
1678 | IN/ OUT: |
1679 | p_return_status |
1680 | p_adj_rec |
1681 | |
1682 | RETURNS : NONE |
1683 | |
1684 | NOTES |
1685 | |
1686 | MODIFICATION HISTORY |
1687 | Vivek Halder 30-JUN-97 Created |
1688 | |
1689 +===========================================================================*/
1690
1691 PROCEDURE Validate_amount (
1692 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
1693 p_ps_rec IN ar_payment_schedules%rowtype,
1694 p_return_status IN OUT NOCOPY varchar2
1695 ) IS
1696
1697 l_index number;
1698 l_user_id BINARY_INTEGER;
1699 l_approval_amount_to ar_approval_user_limits.amount_to%type;
1700 l_approval_amount_from ar_approval_user_limits.amount_from%type;
1701 l_approved_flag VARCHAR2(1);
1702 l_return_status VARCHAR2(1);
1703 l_currency_code VARCHAR2(10);
1704 l_amount Number;
1705 l_round_amount Number;
1706 BEGIN
1707
1708 IF PG_DEBUG in ('Y', 'C') THEN
1709 arp_util.debug('Validate_amount()+', G_MSG_MEDIUM);
1710 END IF;
1711
1712 /*------------------------------------------+
1713 | Initialize the return status to SUCCESS |
1714 +------------------------------------------*/
1715
1716 p_return_status := FND_API.G_RET_STS_SUCCESS;
1717 Begin
1718 select invoice_currency_code into l_currency_code
1719 from ra_customer_trx
1720 where customer_trx_id=p_adj_rec.customer_trx_id;
1721
1722 l_amount := p_adj_rec.amount;
1723
1724 l_round_amount := arp_util.currround(l_amount,l_currency_code);
1725
1726 IF l_amount <> l_round_amount then
1727 --Raise Validateion
1728 aapi_message(
1729 p_application_name =>'AR',
1730 p_message_name => 'ARTA_ARTADESM_INVALID_AMOUNT'
1731 ) ;
1732
1733
1734 p_return_status := FND_API.G_RET_STS_ERROR;
1735
1736
1737 END If;
1738 exception
1739 when others then
1740 p_return_status := FND_API.G_RET_STS_ERROR;
1741 aapi_message(
1742 p_application_name =>'AR',
1743 p_message_name => 'ARTA_ARTADESM_INVALID_AMOUNT'
1744 ) ;
1745 end;
1746 /*-----------------------------------------------+
1747 | If the type is INVOICE then the amount must |
1748 | close the invoice |
1749 +-----------------------------------------------*/
1750
1751 IF ( p_adj_rec.type = 'INVOICE' )
1752 THEN
1753
1754 /*-----------------------------------------------+
1755 | If amount is not specified then set it to |
1756 | close the transaction |
1757 +-----------------------------------------------*/
1758 IF ( p_adj_rec.amount IS NULL or p_adj_rec.amount = 0 )
1759 THEN
1760 /*-----------------------------------------------+
1761 | If amount is not specifiedand the amount due |
1762 | remaining is zero then should not create adj |
1763 +-----------------------------------------------*/
1764 IF ( p_ps_rec.amount_due_remaining = 0 )
1765 THEN
1766 aapi_message(
1767 p_application_name =>'AR',
1768 p_message_name => 'AR_AAPI_ADR_ZERO_INV'
1769 ) ;
1770 p_return_status := FND_API.G_RET_STS_ERROR ;
1771 RETURN ;
1772 END IF;
1773 p_adj_rec.amount := - p_ps_rec.amount_due_remaining ;
1774 ELSE
1775 IF ( p_adj_rec.amount + p_ps_rec.amount_due_remaining <> 0 )
1776 THEN
1777 aapi_message(
1778 p_application_name =>'AR',
1779 p_message_name => 'AR_TW_VAL_AMT_ADJ_INV'
1780 ) ;
1781 p_return_status := FND_API.G_RET_STS_ERROR;
1782 RETURN;
1783 END IF;
1784 END IF;
1785 END IF;
1786
1787 /*-----------------------------------------------+
1788 | Check if the adjustment amount is zero |
1789 +-----------------------------------------------*/
1790
1791 IF ( p_adj_rec.amount IS NULL OR p_adj_rec.amount = 0 )
1792 THEN
1793 /*--------------------------------------------+
1794 | Set the message and return |
1795 +--------------------------------------------*/
1796 aapi_message(
1797 p_application_name =>'AR',
1798 p_message_name => 'AR_AAPI_ADJ_AMOUNT_ZERO'
1799 ) ;
1800 p_return_status := FND_API.G_RET_STS_ERROR;
1801 RETURN;
1802 END IF;
1803
1804 /*------------------------------------------------+
1805 | Verify if amount is within user approval limits|
1806 +-------------------------------------------------*/
1807 /*----------------------------------------------------+
1808 | Special processing for bypassing limit check if |
1809 | created from 'CASH_ENGINE' and 'RECEIPT_REVERSAL' |
1810 +----------------------------------------------------*/
1811
1812 IF ( p_adj_rec.created_from LIKE 'CASH_ENGINE%' OR
1813 p_adj_rec.created_from LIKE 'RECEIPT_REVERSAL%' OR
1814 p_adj_rec.created_from LIKE 'SPLIT_MERGE%' OR
1815 p_adj_rec.created_from LIKE 'DMS_INTERFACE%' OR
1816 p_adj_rec.created_from LIKE 'ENHANCED_CASH%' )
1817 THEN
1818 l_approved_flag := FND_API.G_TRUE ;
1819 ELSE
1820 ar_adjvalidate_pub.Within_approval_limits (
1821 p_adj_rec.amount,
1822 p_ps_rec.invoice_currency_code,
1823 l_approved_flag,
1824 l_return_status
1825 ) ;
1826 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1827 THEN
1828 p_return_status := l_return_status ;
1829 RETURN;
1830 END IF ;
1831 END IF;
1832
1833 /*--------------------------------------------------+
1834 | Check Status. If null/blank then set the value |
1835 | based on l_approved_flag |
1836 +--------------------------------------------------*/
1837
1838 IF ( p_adj_rec.status IS NULL or p_adj_rec.status = ' ' )
1839 THEN
1840 IF ( l_approved_flag = FND_API.G_TRUE )
1841 THEN
1842 p_adj_rec.status := 'A' ;
1843 ELSE
1844 p_adj_rec.status := 'W' ;
1845 END IF ;
1846 ELSE
1847 /*-----------------------------------------------------+
1848 | Check valid status values provided by user |
1849 +-----------------------------------------------------*/
1850
1851 IF (p_adj_rec.status <> 'A' AND p_adj_rec.status <> 'W' AND
1852 p_adj_rec.status <> 'M' )
1853 THEN
1854 aapi_message(
1855 p_application_name =>'AR',
1856 p_message_name => 'AR_AAPI_INVALID_CREATE_STATUS',
1857 p_token1_name => 'STATUS',
1858 p_token1_value => p_adj_rec.status
1859 ) ;
1860 p_return_status := FND_API.G_RET_STS_ERROR ;
1861 RETURN ;
1862 END IF;
1863
1864 /*-----------------------------------------------------+
1865 | Handle the case for setting status to W if outside |
1866 | approval limits during creation of Adjustments |
1867 +-----------------------------------------------------*/
1868
1869 IF ( l_approved_flag = FND_API.G_FALSE AND p_adj_rec.status = 'A' )
1870 THEN
1871 p_adj_rec.status := 'W' ;
1872 END IF;
1873
1874 END IF;
1875
1876 IF PG_DEBUG in ('Y', 'C') THEN
1877 arp_util.debug('Validate_Amount()-', G_MSG_MEDIUM);
1878 END IF;
1879
1880 RETURN ;
1881
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884
1885 IF PG_DEBUG in ('Y', 'C') THEN
1886 arp_util.debug('EXCEPTION: Validate_Amount() ', G_MSG_UERROR);
1887 END IF;
1888
1889 /*-----------------------------------------------+
1890 | Set unexpected error message and status |
1891 +-----------------------------------------------*/
1892 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Amount');
1893 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1894 RETURN;
1895
1896 END Validate_Amount;
1897
1898 /*===========================================================================+
1899 | PROCEDURE |
1900 | Validate_Rcvtrxccid |
1901 | |
1902 | DESCRIPTION |
1903 | This routine validates the Receivables Trx Id and CCId |
1904 | It sets the set_of_books_id value in the adjustment record |
1905 | and also the code combination id (if required) |
1906 | |
1907 | |
1908 | SCOPE - PUBLIC |
1909 | |
1910 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1911 | arp_util.disable_debug |
1912 | arp_util.enable_debug |
1913 | fnd_api.g_exc_unexpected_error |
1914 | fnd_api.g_ret_sts_error |
1915 | fnd_api.g_ret_sts_error |
1916 | fnd_api.g_ret_sts_success |
1917 | fnd_api.to_boolean |
1918 | |
1919 | ARGUMENTS : IN: |
1920 | |
1921 | OUT: |
1922 | p_return_status |
1923 | |
1924 | IN/ OUT: |
1925 | p_adj_rec |
1926 | |
1927 | RETURNS : NONE |
1928 | |
1929 | NOTES |
1930 | |
1931 | MODIFICATION HISTORY |
1932 | Vivek Halder 13-JUN-97 |
1933 | |
1934 +===========================================================================*/
1935
1936 PROCEDURE Validate_Rcvtrxccid (
1937 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
1938 p_return_status IN OUT NOCOPY varchar2
1939 ) IS
1940
1941 l_index number;
1942 l_set_of_books_id ar_receivables_trx.set_of_books_id%type := NULL;
1943 l_cc_id ar_receivables_trx.code_combination_id%type := NULL;
1944 l_count number;
1945 l_found BOOLEAN;
1946 BEGIN
1947
1948 IF PG_DEBUG in ('Y', 'C') THEN
1949 arp_util.debug('Validate_Rcvtrxccid()+', G_MSG_MEDIUM);
1950 END IF;
1951
1952 /*------------------------------------------+
1953 | Initialize the return status to ERROR |
1954 +------------------------------------------*/
1955
1956 p_return_status := FND_API.G_RET_STS_ERROR;
1957
1958 l_found := FALSE ;
1959
1960 FOR l_index IN 1..G_RCVTRX_TBL.COUNT LOOP
1961
1962 IF (p_adj_rec.receivables_trx_id =
1963 G_RCVTRX_TBL(l_index).receivables_trx_id )
1964 THEN
1965 G_receivables_name := G_RCVTRX_TBL(l_index).name ;
1966 l_cc_id := G_RCVTRX_TBL(l_index).code_combination_id;
1967 l_found := TRUE ;
1968 EXIT ;
1969 END IF;
1970
1971 END LOOP;
1972
1973 IF ( NOT l_found )
1974 THEN
1975 /*-----------------------------------------------+
1976 | Set the message |
1977 +-----------------------------------------------*/
1978 aapi_message(
1979 p_application_name =>'AR',
1980 p_message_name => 'AR_AAPI_INVALID_RCVABLE_TRX_ID',
1981 p_token1_name =>'RECEIVABLES_TRX_ID',
1982 p_token1_value => to_char(p_adj_rec.receivables_trx_id)
1983 ) ;
1984 RETURN ;
1985 END IF;
1986
1987 /*--------------------------------------------+
1988 | Check the Code Combination Id |
1989 | If no value is provided default it to the |
1990 | code combination Id of the receivables Trx |
1991 +--------------------------------------------*/
1992
1993 IF ( p_adj_rec.code_combination_id IS NULL OR
1994 p_adj_rec.code_combination_id = 0 )
1995 THEN
1996 /*--------------------------------------------+
1997 | If no default value exists and none is |
1998 | provided by user then set error |
1999 +--------------------------------------------*/
2000
2001 IF ( l_cc_id IS NULL OR l_cc_id = 0 )
2002 THEN
2003 aapi_message(
2004 p_application_name =>'AR',
2005 p_message_name => 'AR_AAPI_NO_CCID_FOR_ACTIVITY',
2006 p_token1_name => 'RECEIVABLES_TRX_ID',
2007 p_token1_value => to_char(p_adj_rec.receivables_trx_id)
2008 ) ;
2009 RETURN ;
2010 END IF ;
2011
2012 /*--------------------------------------------+
2013 | Else default to the CCid of the Receivables |
2014 | Activity |
2015 +--------------------------------------------*/
2016
2017 p_adj_rec.code_combination_id := l_cc_id ;
2018
2019 ELSE
2020 /*--------------------------------------------+
2021 | Validate the code combination Id provided |
2022 +--------------------------------------------*/
2023
2024 l_found := FALSE ;
2025
2026 FOR l_index IN 1..G_CCID_TBL.COUNT LOOP
2027
2028 IF (G_CCID_TBL.EXISTS (p_adj_rec.code_combination_id))
2029 THEN
2030 l_found := TRUE;
2031 EXIT ;
2032 END IF;
2033
2034 END LOOP;
2035
2036 IF ( NOT l_found)
2037 THEN
2038
2039 /*------------------------------------------+
2040 | Check the code combination from database |
2041 +------------------------------------------*/
2042 BEGIN
2043
2044 l_count := 0 ;
2045 SELECT count(*)
2046 INTO l_count
2047 FROM gl_code_combinations
2048 WHERE code_combination_id = p_adj_rec.code_combination_id
2049 AND enabled_flag = 'Y'
2050 AND SYSDATE BETWEEN NVL(start_date_active, sysdate)
2051 AND NVL(end_date_active, sysdate);
2052
2053 EXCEPTION
2054 WHEN OTHERS THEN
2055 /*---------------------------------------------+
2056 |Set unexpected error message and status |
2057 +---------------------------------------------*/
2058 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Rcvtrxccid');
2059 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2060 RETURN;
2061 END ;
2062
2063 IF ( l_count <> 1 )
2064 THEN
2065 /*-------------------------------------------+
2066 | Set the message |
2067 +-------------------------------------------*/
2068 aapi_message(
2069 p_application_name =>'AR',
2070 p_message_name => 'AR_AAPI_INVALID_CCID',
2071 p_token1_name => 'CCID',
2072 p_token1_value => p_adj_rec.code_combination_id
2073 ) ;
2074 RETURN ;
2075 END IF;
2076
2077 END IF;
2078
2079 /*--------------------------------------------+
2080 | Check that if the Profile Option : Allow |
2081 | Override of default activity is set to Y |
2082 | then value must be equal to l_cc_id |
2083 +--------------------------------------------*/
2084
2085 IF ( g_context_rec.override_activity_option = 'N' AND
2086 l_cc_id IS NOT NULL )
2087 THEN
2088 IF ( p_adj_rec.code_combination_id <> l_cc_id )
2089 THEN
2090 aapi_message (
2091 p_application_name =>'AR',
2092 p_message_name => 'AR_AAPI_OVERRIDE_CCID_DISALLOW'
2093 ) ;
2094 RETURN ;
2095 END IF;
2096 END IF;
2097
2098 END IF;
2099
2100 /*-----------------------------------------------+
2101 | Set the Set of books Id |
2102 +-----------------------------------------------*/
2103
2104 p_adj_rec.set_of_books_id := arp_global.set_of_books_id ;
2105
2106 p_return_status := FND_API.G_RET_STS_SUCCESS ;
2107
2108 IF PG_DEBUG in ('Y', 'C') THEN
2109 arp_util.debug('Validate_Rcvtrxccid()-', G_MSG_MEDIUM);
2110 END IF;
2111
2112 RETURN ;
2113
2114 EXCEPTION
2115 WHEN OTHERS THEN
2116
2117 IF PG_DEBUG in ('Y', 'C') THEN
2118 arp_util.debug('EXCEPTION: Validate_Rcvtrxccid', G_MSG_UERROR);
2119 END IF;
2120 /*-----------------------------------------------+
2121 | Set unexpected error message and status |
2122 +-----------------------------------------------*/
2123 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Rcvtrxccid');
2124 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2125
2126 RETURN;
2127
2128 END Validate_Rcvtrxccid;
2129
2130 /*===========================================================================+
2131 | PROCEDURE |
2132 | Validate_dates |
2133 | DESCRIPTION |
2134 | This routine validates the apply and gl dates for both |
2135 | creation and reversal |
2136 | |
2137 | SCOPE - PUBLIC |
2138 | |
2139 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2140 | |
2141 | ARGUMENTS : IN: |
2142 | p_adj_rec |
2143 | OUT: |
2144 | p_return_status |
2145 | IN/ OUT: |
2146 | |
2147 | RETURNS : NONE |
2148 | |
2149 | NOTES |
2150 | |
2151 | MODIFICATION HISTORY |
2152 | Vivek Halder 13-JUN-97 |
2153 | |
2154 +===========================================================================*/
2155
2156 PROCEDURE Validate_dates (
2157 p_apply_date IN ar_adjustments.apply_date%type,
2158 p_gl_date IN ar_adjustments.gl_date%type,
2159 p_ps_rec IN ar_payment_schedules%rowtype,
2160 p_return_status IN OUT NOCOPY varchar2
2161 ) IS
2162
2163 l_index number;
2164 l_found_flag BOOLEAN;
2165 l_count number ;
2166 l_set_of_books_id AR_SYSTEM_PARAMETERS.SET_OF_BOOKS_ID%TYPE ;
2167
2168 BEGIN
2169
2170 IF PG_DEBUG in ('Y', 'C') THEN
2171 arp_util.debug('Validate_dates()+', G_MSG_MEDIUM);
2172 END IF;
2173
2174 /*------------------------------------------+
2175 | Initialize the return status to ERROR |
2176 +------------------------------------------*/
2177
2178 p_return_status := FND_API.G_RET_STS_ERROR;
2179
2180 /*------------------------------------------+
2181 | The dates should not be null |
2182 +-----------------------------------------*/
2183
2184 IF ( p_apply_date IS NULL )
2185 THEN
2186 aapi_message(
2187 p_application_name =>'AR',
2188 p_message_name => 'AR_AAPI_NO_APPLY_DATE'
2189 ) ;
2190 RETURN ;
2191 END IF;
2192
2193 IF ( p_gl_date IS NULL )
2194 THEN
2195 aapi_message(
2196 p_application_name =>'AR',
2197 p_message_name => 'AR_AAPI_NO_GL_DATE'
2198 ) ;
2199 RETURN ;
2200 END IF;
2201
2202 /*------------------------------------------+
2203 | Validate from GL date from period cache |
2204 | Check that it lies in open/future period|
2205 +-----------------------------------------*/
2206
2207 l_found_flag := FALSE ;
2208
2209 FOR l_index IN 1..G_GLPERIOD_TBL.COUNT LOOP
2210
2211 IF (trunc(p_gl_date) >=
2212 nvl(G_GLPERIOD_TBL(l_index).start_date, trunc(p_gl_date))
2213 AND
2214 trunc(p_gl_date) <=
2215 nvl(G_GLPERIOD_TBL(l_index).end_date,trunc(p_gl_date))
2216 )
2217 THEN
2218 l_found_flag := TRUE ;
2219 EXIT ;
2220 END IF;
2221
2222 END LOOP;
2223
2224 /*------------------------------------------+
2225 | If it does not exist in cache validate it|
2226 | from database |
2227 +------------------------------------------*/
2228
2229 IF ( NOT l_found_flag )
2230 THEN
2231
2232 select set_of_books_id
2233 into l_set_of_books_id
2234 from ar_system_parameters;
2235
2236 l_count := 0 ;
2237
2238 SELECT count(*)
2239 INTO l_count
2240 FROM gl_period_statuses g,
2241 gl_sets_of_books b
2242 WHERE g.application_id = 222
2243 AND g.set_of_books_id = l_set_of_books_id
2244 AND g.set_of_books_id = b.set_of_books_id
2245 AND g.period_type = b.accounted_period_type
2246 AND g.adjustment_period_flag = 'N'
2247 AND g.closing_status IN ('O','F')
2248 AND trunc(p_gl_date) BETWEEN nvl(trunc(g.start_date),
2249 trunc(p_gl_date))
2250 AND nvl(trunc(g.end_date),trunc(p_gl_date)) ;
2251
2252 IF ( l_count > 0 )
2253 THEN
2254 l_found_flag := TRUE ;
2255 END IF;
2256
2257 END IF;
2258
2259 /*------------------------------------------+
2260 | If no valid period found then set message|
2261 | and return |
2262 +------------------------------------------*/
2263
2264 IF ( NOT l_found_flag )
2265 THEN
2266 aapi_message(
2267 p_application_name =>'AR',
2268 p_message_name => 'AR_AAPI_GLDATE_INVALID_PERIOD',
2269 p_token1_name => 'GL_DATE',
2270 p_token1_value => to_char(p_gl_date,'DD-MON-YYYY')
2271 ) ;
2272 RETURN;
2273 END IF;
2274
2275 /*---------------------------------------+
2276 | Check that apply date should be equal |
2277 | to or greater than the transaction date|
2278 +---------------------------------------*/
2279
2280 IF ( trunc(p_apply_date) < trunc(p_ps_rec.trx_date) )
2281 THEN
2282 aapi_message(
2283 p_application_name =>'AR',
2284 p_message_name => 'AR_AAPI_APPLYDATE_LT_TRXDATE',
2285 p_token1_name => 'APPLY_DATE',
2286 p_token1_value => to_char(p_apply_date,'DD-MON-YYYY'),
2287 p_token2_name => 'TRX_DATE',
2288 p_token2_value => to_char(p_ps_rec.trx_date,'DD-MON-YYYY')
2289 ) ;
2290 RETURN;
2291 END IF;
2292
2293 /*---------------------------------------+
2294 | Check that GL date should be equal to |
2295 | or greater than the transaction GL date|
2296 +---------------------------------------*/
2297
2298 IF ( trunc(p_gl_date) < trunc(p_ps_rec.gl_date) )
2299 THEN
2300 aapi_message(
2301 p_application_name =>'AR',
2302 p_message_name => 'AR_AAPI_GLDATE_LT_TRXGLDATE',
2303 p_token1_name => 'GL_DATE',
2304 p_token1_value => to_char(p_gl_date,'DD-MON-YYYY'),
2305 p_token2_name => 'TRX_GL_DATE',
2306 p_token2_value => to_char(p_ps_rec.gl_date,'DD-MON-YYYY')
2307 ) ;
2308 RETURN;
2309 END IF;
2310
2311 /*------------------------------------+
2312 | Check that GL date should be equal |
2313 | to or greater than the apply date |
2314 +------------------------------------*/
2315
2316 /* ------------------------------This check is no longer valid
2317 IF ( trunc(p_gl_date) < trunc(p_apply_date) )
2318 THEN
2319 aapi_message(
2320 p_application_name =>'AR',
2321 p_message_name => 'AR_AAPI_GLDATE_LT_APPLYDATE',
2322 p_token1_name => 'GL_DATE',
2323 p_token1_value => to_char(p_gl_date,'DD-MON-YYYY'),
2324 p_token2_name => 'APPLY_DATE',
2325 p_token2_value => to_char(p_apply_date,'DD-MON-YYYY')
2326 ) ;
2327 RETURN;
2328 END IF;
2329 ------------------------------------------------------------ */
2330 p_return_status := FND_API.G_RET_STS_SUCCESS;
2331
2332 IF PG_DEBUG in ('Y', 'C') THEN
2333 arp_util.debug('Validate_dates()-', G_MSG_MEDIUM);
2334 END IF;
2335
2336 RETURN ;
2337
2338 EXCEPTION
2339 WHEN OTHERS THEN
2340
2341 IF PG_DEBUG in ('Y', 'C') THEN
2342 arp_util.debug('EXCEPTION: Validate_dates ', G_MSG_UERROR);
2343 END IF;
2344 /*-----------------------------------------------+
2345 | Set unexpected error message and status |
2346 +-----------------------------------------------*/
2347 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_dates' );
2348 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2349
2350 RETURN;
2351
2352 END Validate_dates;
2353
2354
2355 /*===========================================================================+
2356 | PROCEDURE |
2357 | Validate_doc_seq |
2358 | |
2359 | DESCRIPTION |
2360 | This routine validates the Document Sequence value and sets |
2361 | the Id also |
2362 | |
2363 | |
2364 | SCOPE - PRIVATE |
2365 | |
2366 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2367 | arp_util.disable_debug |
2368 | arp_util.enable_debug |
2369 | fnd_api.g_exc_unexpected_error |
2370 | fnd_api.g_ret_sts_error |
2371 | fnd_api.g_ret_sts_error |
2372 | fnd_api.g_ret_sts_success |
2373 | fnd_api.to_boolean |
2374 | |
2375 | ARGUMENTS : IN: |
2376 | OUT: |
2377 | p_return_status |
2378 | |
2379 | IN/ OUT: |
2380 | p_adj_rec |
2381 | |
2382 | |
2383 | |
2384 | RETURNS : NONE |
2385 | |
2386 | NOTES |
2387 | |
2388 | MODIFICATION HISTORY |
2389 | Vivek Halder 13-JUN-97 |
2390 | |
2391 +===========================================================================*/
2392
2393 PROCEDURE Validate_doc_seq (
2394 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2395 p_return_status IN OUT NOCOPY varchar2
2396 ) IS
2397
2398 l_dummy BINARY_INTEGER;
2399 l_sequence_name fnd_document_sequences.name%type;
2400 l_doc_sequence_id fnd_document_sequences.doc_sequence_id%type ;
2401
2402 BEGIN
2403
2404 IF PG_DEBUG in ('Y', 'C') THEN
2405 arp_util.debug('Validate_doc_seq()+', G_MSG_MEDIUM);
2406 END IF;
2407
2408 /*------------------------------------------+
2409 | Initialize the return status to SUCCESS |
2410 +------------------------------------------*/
2411
2412 p_return_status := FND_API.G_RET_STS_SUCCESS;
2413
2414 /*------------------------------------------------+
2415 | Document sequences are only applicable if the |
2416 | unique seq number option is not equal to N |
2417 +------------------------------------------------*/
2418
2419 IF ( NVL(g_context_rec.unique_seq_numbers, 'N') = 'N' )
2420 THEN
2421 IF ( p_adj_rec.doc_sequence_value IS NOT NULL OR
2422 p_adj_rec.doc_sequence_value <> 0 )
2423 THEN
2424 aapi_message(
2425 p_application_name =>'AR',
2426 p_message_name => 'AR_AAPI_DOC_SEQ_NOT_REQD',
2427 p_token1_name => 'DOCUMENT_SEQ',
2428 p_token1_value => to_char(p_adj_rec.doc_sequence_value)
2429 );
2430 p_return_status := FND_API.G_RET_STS_ERROR;
2431 END IF;
2432 RETURN ;
2433 END IF;
2434
2435 /*-----------------------------+
2436 | Get the document sequence. |
2437 +------------------------------*/
2438
2439 p_adj_rec.doc_sequence_id := NULL;
2440
2441 fnd_seqnum.get_seq_name (
2442 arp_global.G_AR_APP_ID,
2443 G_receivables_name,
2444 arp_global.set_of_books_id,
2445 'M',
2446 p_adj_rec.apply_date,
2447 l_sequence_name,
2448 p_adj_rec.doc_sequence_id,
2449 l_dummy
2450 );
2451
2452 IF ( l_sequence_name IS NOT NULL AND
2453 p_adj_rec.doc_sequence_id IS NOT NULL )
2454 THEN
2455 /*-----------------------------------------+
2456 | Automatic Document Numbering case |
2457 | Document seuqence value should not exist|
2458 +-----------------------------------------*/
2459
2460 IF ( p_adj_rec.doc_sequence_value IS NOT NULL OR
2461 p_adj_rec.doc_sequence_value <> 0 )
2462 THEN
2463 aapi_message(
2464 p_application_name =>'AR',
2465 p_message_name => 'AR_AAPI_DOC_SEQ_NOT_REQD',
2466 p_token1_name => 'DOCUMENT_SEQ',
2467 p_token1_value => to_char(p_adj_rec.doc_sequence_value)
2468 );
2469 p_return_status := FND_API.G_RET_STS_ERROR;
2470 RETURN ;
2471 END IF;
2472
2473 /*-----------------------------------------+
2474 | Get the document sequence value |
2475 +-----------------------------------------*/
2476
2477 p_adj_rec.doc_sequence_value :=
2478 fnd_seqnum.get_next_sequence (
2479 arp_global.G_AR_APP_ID,
2480 G_receivables_name,
2481 arp_global.set_of_books_id,
2482 'M',
2483 p_adj_rec.apply_date,
2484 l_sequence_name,
2485 p_adj_rec.doc_sequence_id
2486 );
2487
2488 ELSIF ( p_adj_rec.doc_sequence_id IS NOT NULL AND
2489 p_adj_rec.doc_sequence_value IS NOT NULL )
2490 THEN
2491 /*------------------------------------+
2492 | Manual Document Numbering case |
2493 | with the document value specified. |
2494 | Use the specified value. |
2495 +-------------------------------------*/
2496 NULL;
2497
2498 ELSIF ( p_adj_rec.doc_sequence_id IS NOT NULL AND
2499 p_adj_rec.doc_sequence_value IS NULL )
2500 THEN
2501 /*-------------------------------------------+
2502 | Manual Document Numbering case |
2503 | with the document value not specified. |
2504 | Generate a document value mandatory error |
2505 +-----------------------------------------*/
2506 aapi_message(
2507 p_application_name =>'FND',
2508 p_message_name => 'UNIQUE-NO VALUE'
2509 );
2510 p_return_status := FND_API.G_RET_STS_ERROR;
2511 RETURN ;
2512 END IF;
2513
2514 IF PG_DEBUG in ('Y', 'C') THEN
2515 arp_util.debug('Validate_doc_seq()-', G_MSG_MEDIUM);
2516 END IF;
2517
2518 RETURN ;
2519
2520 EXCEPTION
2521
2522 WHEN NO_DATA_FOUND THEN
2523 /*-----------------------------------------+
2524 | No document assignment was found. |
2525 | Generate an error if document numbering |
2526 | is mandatory. |
2527 +-----------------------------------------*/
2528 IF (g_context_rec.unique_seq_numbers = 'A' )
2529 THEN
2530 aapi_message(
2531 p_application_name =>'FND',
2532 p_message_name => 'UNIQUE-ALWAYS USED'
2533 );
2534 p_return_status := FND_API.G_RET_STS_ERROR;
2535 ELSE
2536 p_adj_rec.doc_sequence_id := NULL;
2537 p_adj_rec.doc_sequence_value := NULL;
2538 END IF;
2539 RETURN;
2540
2541 WHEN OTHERS THEN
2542
2543 IF PG_DEBUG in ('Y', 'C') THEN
2544 arp_util.debug('EXCEPTION: Validate_doc_seq ', G_MSG_UERROR);
2545 END IF;
2546 /*-----------------------------------------------+
2547 | Set unexpected error message and status |
2548 +-----------------------------------------------*/
2549 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_doc_seq' );
2550 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2551 RETURN;
2552
2553 END Validate_doc_seq;
2554
2555
2556 /*===========================================================================+
2557 | PROCEDURE Validate_reason_code |
2558 | |
2559 | DESCRIPTION |
2560 | This routine validates the reason code of adjustment |
2561 | |
2562 | |
2563 | |
2564 | SCOPE - PUBLIC |
2565 | |
2566 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2567 | arp_util.disable_debug |
2568 | arp_util.enable_debug |
2569 | fnd_api.g_exc_unexpected_error |
2570 | fnd_api.g_ret_sts_error |
2571 | fnd_api.g_ret_sts_error |
2572 | fnd_api.g_ret_sts_success |
2573 | fnd_api.to_boolean |
2574 | |
2575 | ARGUMENTS : IN: |
2576 | |
2577 | OUT: |
2578 | |
2579 | IN/ OUT: |
2580 | |
2581 | p_return_status |
2582 | p_adj_rec |
2583 | |
2584 | RETURNS : NONE |
2585 | |
2586 | NOTES |
2587 | |
2588 | MODIFICATION HISTORY |
2589 | Vivek Halder 13-JUN-97 |
2590 | |
2591 +===========================================================================*/
2592
2593 PROCEDURE Validate_Reason_code (
2594 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2595 p_return_status IN OUT NOCOPY varchar2
2596 ) IS
2597
2598 l_index number;
2599 l_found BOOLEAN;
2600 BEGIN
2601
2602 IF PG_DEBUG in ('Y', 'C') THEN
2603 arp_util.debug('Validate_Reason_code()+', G_MSG_MEDIUM);
2604 END IF;
2605
2606 /*------------------------------------------+
2607 | Initialize the return status to ERROR |
2608 +------------------------------------------*/
2609
2610 p_return_status := FND_API.G_RET_STS_ERROR;
2611
2612 /*------------------------------------------+
2613 | Validate only is value is provided |
2614 +------------------------------------------*/
2615
2616 IF ( p_adj_rec.reason_code IS NOT NULL AND
2617 p_adj_rec.reason_code <> ' ' )
2618 THEN
2619
2620 l_found := FALSE ;
2621
2622 FOR l_index IN 1..G_REASON_TBL.COUNT LOOP
2623
2624 IF (p_adj_rec.reason_code = G_REASON_TBL(l_index).lookup_code)
2625 THEN
2626 l_found := TRUE ;
2627 EXIT ;
2628 END IF;
2629
2630 END LOOP;
2631
2632 IF ( NOT l_found )
2633 THEN
2634 /*-----------------------------------------------+
2635 | Set the message |
2636 +-----------------------------------------------*/
2637 aapi_message(
2638 p_application_name =>'AR',
2639 p_message_name => 'AR_AAPI_INVALID_REASON_CODE',
2640 p_token1_name => 'REASON_CODE',
2641 p_token1_value => p_adj_rec.reason_code
2642 ) ;
2643 RETURN ;
2644
2645 END IF;
2646
2647 END IF ;
2648
2649 p_return_status := FND_API.G_RET_STS_SUCCESS ;
2650
2651 IF PG_DEBUG in ('Y', 'C') THEN
2652 arp_util.debug('Validate_Reason_Code()-', G_MSG_MEDIUM);
2653 END IF;
2654
2655 RETURN ;
2656
2657 EXCEPTION
2658 WHEN OTHERS THEN
2659
2660 IF PG_DEBUG in ('Y', 'C') THEN
2661 arp_util.debug('EXCEPTION: Validate_Reason_code ', G_MSG_UERROR);
2662 END IF;
2663 /*-----------------------------------------------+
2664 | Set unexpected error message and status |
2665 +-----------------------------------------------*/
2666 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Reason_code' );
2667 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2668
2669 RETURN;
2670
2671 END Validate_Reason_code;
2672
2673
2674 /*===========================================================================+
2675 | PROCEDURE Validate_Desc_Flexfield |
2676 | |
2677 | DESCRIPTION |
2678 | Validates descriptive flexfields using the flex API. |
2679 | |
2680 | SCOPE - PRIVATE |
2681 | |
2682 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2683 | |
2684 | ARGUMENTS : IN: |
2685 | OUT: |
2686 | IN/ OUT: |
2687 | |
2688 | RETURNS : NONE |
2689 | |
2690 | NOTES |
2691 | This validation is currently disabled because it doesn't |
2692 | work correctly. The descriptive flexfield API functions |
2693 | that this routine uses are not yet production code and are |
2694 | unstable. |
2695 | |
2696 | MODIFICATION HISTORY |
2697 | Vivek Halder 01-JUL-97 Created |
2698 | |
2699 +===========================================================================*/
2700
2701 PROCEDURE Validate_Desc_Flexfield(
2702 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2703 p_return_status IN OUT NOCOPY varchar2
2704 ) IS
2705
2706 l_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%type;
2707
2708 BEGIN
2709
2710 IF PG_DEBUG in ('Y', 'C') THEN
2711 arp_util.debug('Validate_Desc_Flexfield()+', G_MSG_MEDIUM);
2712 END IF;
2713
2714 /*------------------------------------------+
2715 | Initialize the return status to SUCCESS |
2716 +------------------------------------------*/
2717
2718 p_return_status := FND_API.G_RET_STS_SUCCESS;
2719
2720 /*------------------------------------------+
2721 | Get the flexfield name |
2722 +------------------------------------------*/
2723
2724 BEGIN
2725
2726 SELECT descriptive_flexfield_name
2727 INTO l_flex_name
2728 FROM fnd_descriptive_flexs
2729 WHERE application_id = arp_global.G_AR_APP_ID AND
2730 application_table_name like 'AR_ADJUSTMENTS' ;
2731
2732 EXCEPTION
2733 WHEN NO_DATA_FOUND THEN
2734 RETURN;
2735
2736 WHEN OTHERS THEN
2737 IF PG_DEBUG in ('Y', 'C') THEN
2738 arp_util.debug('EXCEPTION: Validate_Desc_Flexfield', G_MSG_UERROR);
2739 END IF;
2740 /*-----------------------------------------------+
2741 | Set unexpected error message and status |
2742 +-----------------------------------------------*/
2743 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Desc_Flexfield' );
2744 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2745 RETURN;
2746 END ;
2747
2748
2749 /*--------------------------------------------------------------------+
2750 | Call the flexfield routines to validate the transaction flexfield. |
2751 +--------------------------------------------------------------------*/
2752
2753 fnd_flex_descval.set_context_value(p_adj_rec.attribute_category);
2754
2755 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_adj_rec.attribute1);
2756 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_adj_rec.attribute2);
2757 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_adj_rec.attribute3);
2758 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_adj_rec.attribute4);
2759 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_adj_rec.attribute5);
2760 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_adj_rec.attribute6);
2761 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_adj_rec.attribute7);
2762 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_adj_rec.attribute8);
2763 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_adj_rec.attribute9);
2764 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_adj_rec.attribute10);
2765 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_adj_rec.attribute11);
2766 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_adj_rec.attribute12);
2767 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_adj_rec.attribute13);
2768 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_adj_rec.attribute14);
2769 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_adj_rec.attribute15);
2770
2771 IF ( NOT fnd_flex_descval.validate_desccols ('AR', l_flex_name) )
2772 THEN
2773 p_return_status := FND_API.G_RET_STS_ERROR;
2774 aapi_message(
2775 p_application_name =>'AR',
2776 p_message_name => 'AR_AAPI_INVALID_DESC_FLEX'
2777 ) ;
2778 RETURN ;
2779 END IF;
2780
2781 IF PG_DEBUG in ('Y', 'C') THEN
2782 arp_util.debug('Validate_Desc_Flexfield()-', G_MSG_MEDIUM);
2783 END IF;
2784
2785 EXCEPTION
2786 WHEN OTHERS THEN
2787 IF PG_DEBUG in ('Y', 'C') THEN
2788 arp_util.debug('EXCEPTION: Validate_Desc_Flexfield', G_MSG_UERROR);
2789 END IF;
2790 /*-----------------------------------------------+
2791 | Set unexpected error message and status |
2792 +-----------------------------------------------*/
2793 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Desc_Flexfield' );
2794 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2795
2796 RETURN;
2797
2798 END Validate_Desc_Flexfield;
2799
2800 /*==========================================================================+
2801 | PROCEDURE |
2802 | Validate_Created_From |
2803 | |
2804 | DESCRIPTION |
2805 | This routine validates the Created From field of adjustment |
2806 | |
2807 | |
2808 | SCOPE - PRIVATE |
2809 | |
2810 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2811 | arp_util.disable_debug |
2812 | arp_util.enable_debug |
2813 | fnd_api.g_exc_unexpected_error |
2814 | fnd_api.g_ret_sts_error |
2815 | fnd_api.g_ret_sts_error |
2816 | fnd_api.g_ret_sts_success |
2817 | fnd_api.to_boolean |
2818 | |
2819 | ARGUMENTS : IN: |
2820 | p_adj_rec |
2821 | OUT: |
2822 | p_return_status |
2823 | |
2824 | IN/ OUT: |
2825 | |
2826 | RETURNS : NONE |
2827 | |
2828 | NOTES |
2829 | |
2830 | MODIFICATION HISTORY |
2831 | Vivek Halder 13-JUN-97 |
2832 | |
2833 +===========================================================================*/
2834
2835 PROCEDURE Validate_Created_From (
2836 p_adj_rec IN ar_adjustments%rowtype,
2837 p_return_status IN OUT NOCOPY varchar2
2838 ) IS
2839
2840 BEGIN
2841
2842 IF PG_DEBUG in ('Y', 'C') THEN
2843 arp_util.debug('Validate_Created_From()+', G_MSG_MEDIUM);
2844 END IF;
2845
2846 /*------------------------------------------+
2847 | Initialize the return status to SUCCESS |
2848 +------------------------------------------*/
2849
2850 p_return_status := FND_API.G_RET_STS_SUCCESS;
2851
2852 IF ( p_adj_rec.created_from IS NULL OR
2853 p_adj_rec.created_from = ' ' )
2854 THEN
2855 /*------------------------------------------+
2856 | Set the message |
2857 +------------------------------------------*/
2858 aapi_message(
2859 p_application_name =>'AR',
2860 p_message_name => 'AR_AAPI_NO_CREATED_FROM'
2861 ) ;
2862 p_return_status := FND_API.G_RET_STS_ERROR;
2863 RETURN ;
2864 END IF;
2865
2866
2867 IF PG_DEBUG in ('Y', 'C') THEN
2868 arp_util.debug('Validate_Created_From ()-', G_MSG_MEDIUM);
2869 END IF;
2870
2871 RETURN ;
2872
2873 EXCEPTION
2874 WHEN OTHERS THEN
2875
2876 IF PG_DEBUG in ('Y', 'C') THEN
2877 arp_util.debug('EXCEPTION: Validate_Created_From()', G_MSG_UERROR);
2878 END IF;
2879 /*-----------------------------------------------+
2880 | Set unexpected error message and status |
2881 +-----------------------------------------------*/
2882 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Created_From'
2883 );
2884 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2885 RETURN;
2886
2887 END Validate_Created_From;
2888
2889 /*===========================================================================+
2890 | PROCEDURE |
2891 | Validate_Ussgl_code |
2892 | |
2893 | DESCRIPTION |
2894 | This routine validates the USSGL code of adjustment |
2895 | and also sets the context |
2896 | |
2897 | |
2898 | SCOPE - PRIVATE |
2899 | |
2900 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2901 | arp_util.disable_debug |
2902 | arp_util.enable_debug |
2903 | fnd_api.g_exc_unexpected_error |
2904 | fnd_api.g_ret_sts_error |
2905 | fnd_api.g_ret_sts_error |
2906 | fnd_api.g_ret_sts_success |
2907 | fnd_api.to_boolean |
2908 | |
2909 | ARGUMENTS : IN: |
2910 | OUT: |
2911 | p_return_status |
2912 | |
2913 | IN/ OUT: |
2914 | p_adj_rec |
2915 | |
2916 | RETURNS : NONE |
2917 | |
2918 | NOTES |
2919 | |
2920 | MODIFICATION HISTORY |
2921 | Vivek Halder 13-JUN-97 |
2922 | |
2923 +===========================================================================*/
2924
2925 PROCEDURE Validate_Ussgl_code (
2926 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2927 p_return_status IN OUT NOCOPY varchar2
2928 ) IS
2929
2930 l_index number;
2931
2932 BEGIN
2933
2934 IF PG_DEBUG in ('Y', 'C') THEN
2935 arp_util.debug('Validate_Ussgl_code()+', G_MSG_MEDIUM);
2936 END IF;
2937
2938 /*------------------------------------------+
2939 | Initialize the return status to ERROR |
2940 +------------------------------------------*/
2941
2942 p_return_status := FND_API.G_RET_STS_ERROR;
2943
2944 /*------------------------------------------+
2945 | Validate based on option |
2946 +------------------------------------------*/
2947
2948 IF ( g_context_rec.ussgl_option = 'Y' )
2949 THEN
2950 /*------------------------------------------+
2951 | Validate from the cache |
2952 +------------------------------------------*/
2953 FOR l_index IN 1..G_USSGL_TBL.COUNT LOOP
2954
2955 IF (p_adj_rec.ussgl_transaction_code =
2956 G_USSGL_TBL(l_index).ussgl_code)
2957 THEN
2958 p_adj_rec.ussgl_transaction_code_context :=
2959 G_USSGL_TBL(l_index).ussgl_context;
2960 p_return_status := FND_API.G_RET_STS_SUCCESS;
2961 EXIT ;
2962 END IF;
2963
2964 END LOOP;
2965
2966 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
2967 THEN
2968 /*-----------------------------------------------+
2969 | Set the message |
2970 +-----------------------------------------------*/
2971 aapi_message(
2972 p_application_name =>'AR',
2973 p_message_name => 'AR_AAPI_INVALID_USSGL_CODE',
2974 p_token1_name => 'USSGL_CODE',
2975 p_token1_value => p_adj_rec.ussgl_transaction_code
2976 ) ;
2977 RETURN;
2978 END IF;
2979
2980 ELSE
2981
2982 /*------------------------------------------+
2983 | No USSGL code should be provided |
2984 +------------------------------------------*/
2985 IF ( p_adj_rec.ussgl_transaction_code IS NOT NULL AND
2986 p_adj_rec.ussgl_transaction_code <> ' ' )
2987 THEN
2988 aapi_message(
2989 p_application_name =>'AR',
2990 p_message_name => 'AR_AAPI_USSGL_CODE_DISALLOW',
2991 p_token1_name => 'USSGL_CODE',
2992 p_token1_value => p_adj_rec.ussgl_transaction_code
2993 ) ;
2994 RETURN;
2995 ELSE
2996 p_return_status := FND_API.G_RET_STS_SUCCESS;
2997 END IF ;
2998 END IF ;
2999
3000 IF PG_DEBUG in ('Y', 'C') THEN
3001 arp_util.debug('Validate_Ussgl_Code()-', G_MSG_MEDIUM);
3002 END IF;
3003
3004 RETURN ;
3005
3006 EXCEPTION
3007 WHEN OTHERS THEN
3008
3009 IF PG_DEBUG in ('Y', 'C') THEN
3010 arp_util.debug('EXCEPTION: Validate_Ussgl_code ', G_MSG_UERROR);
3011 END IF;
3012 /*-----------------------------------------------+
3013 | Set unexpected error message and status |
3014 +-----------------------------------------------*/
3015 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Ussgl_code' );
3016 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3017
3018 RETURN;
3019
3020 END Validate_Ussgl_code;
3021
3022 /*==========================================================================+
3023 | PROCEDURE |
3024 | Validate_Associated_Receipt |
3025 | |
3026 | DESCRIPTION |
3027 | This routine validates the associated cash_receipt_id |
3028 | |
3029 | |
3030 | SCOPE - PUBLIC |
3031 | |
3032 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3033 | arp_util.disable_debug |
3034 | arp_util.enable_debug |
3035 | fnd_api.g_exc_unexpected_error |
3036 | fnd_api.g_ret_sts_error |
3037 | fnd_api.g_ret_sts_error |
3038 | fnd_api.g_ret_sts_success |
3039 | fnd_api.to_boolean |
3040 | |
3041 | ARGUMENTS : IN: |
3042 | p_adj_rec |
3043 | |
3044 | OUT: |
3045 | p_return_status |
3046 | |
3047 | IN/ OUT: |
3048 | |
3049 | |
3050 | RETURNS : NONE |
3051 | |
3052 | NOTES |
3053 | |
3054 | MODIFICATION HISTORY |
3055 | Vivek Halder 13-JUN-97 |
3056 | |
3057 +===========================================================================*/
3058
3059 PROCEDURE Validate_Associated_Receipt (
3060 p_adj_rec IN ar_adjustments%rowtype,
3061 p_return_status IN OUT NOCOPY varchar2
3062 ) IS
3063
3064 l_count number;
3065
3066 BEGIN
3067
3068 IF PG_DEBUG in ('Y', 'C') THEN
3069 arp_util.debug('Validate_Associated_Receipt()+', G_MSG_MEDIUM);
3070 END IF;
3071
3072 /*------------------------------------------+
3073 | Initialize the return status to SUCCESS |
3074 +------------------------------------------*/
3075
3076 p_return_status := FND_API.G_RET_STS_SUCCESS;
3077
3078 IF ( p_adj_rec.associated_cash_receipt_id IS NOT NULL AND
3079 p_adj_rec.associated_cash_receipt_id <> 0 )
3080 THEN
3081 /*------------------------------------------+
3082 | Validate the Cash Receipt Id |
3083 +------------------------------------------*/
3084
3085 l_count := 0 ;
3086
3087 SELECT count(*)
3088 INTO l_count
3089 FROM ar_cash_receipts
3090 WHERE cash_receipt_id = p_adj_rec.associated_cash_receipt_id ;
3091
3092 IF ( l_count <> 1 )
3093 THEN
3094 /*------------------------------------------+
3095 | Set the message |
3096 +------------------------------------------*/
3097 aapi_message(
3098 p_application_name =>'AR',
3099 p_message_name => 'AR_AAPI_INVALID_RECEIPT_ID',
3100 p_token1_name =>'ASSOCIATED_CASH_RECEIPT_ID',
3101 p_token1_value =>to_char(p_adj_rec.associated_cash_receipt_id)
3102 ) ;
3103 p_return_status := FND_API.G_RET_STS_ERROR;
3104 RETURN;
3105 END IF;
3106
3107 END IF ;
3108
3109 IF PG_DEBUG in ('Y', 'C') THEN
3110 arp_util.debug('Validate_Associated_Receipt()-', G_MSG_MEDIUM);
3111 END IF;
3112
3113 RETURN ;
3114
3115 EXCEPTION
3116 WHEN OTHERS THEN
3117
3118 IF PG_DEBUG in ('Y', 'C') THEN
3119 arp_util.debug('EXCEPTION: Validate_Associated_Receipt', G_MSG_UERROR);
3120 arp_util.debug('EXCEPTION: Validate_Associated_Receipt for Receipt Id '
3121 || p_adj_rec.associated_cash_receipt_id, G_MSG_HIGH );
3122 END IF;
3123 /*-----------------------------------------------+
3124 | Set unexpected error message and status |
3125 +-----------------------------------------------*/
3126 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Associated_Receipt');
3127 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3128 RETURN;
3129
3130 END Validate_Associated_Receipt;
3131 END ar_adjvalidate_pub;