[Home] [Help]
PACKAGE BODY: APPS.AR_ADJVALIDATE_PUB
Source
1 PACKAGE BODY AR_ADJVALIDATE_PUB AS
2 /* $Header: ARTAADVB.pls 115.9 2003/10/10 14:27:25 mraymond 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)
349 WHEN OTHERS THEN
346 ) ;
347 p_return_status := FND_API.G_RET_STS_ERROR;
348 RETURN;
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 | |
461 | |
458 | ARGUMENTS : IN: |
459 | OUT: p_return_status |
460 | IN/ OUT: |
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))
570 l_temp_rec Lookup_Rec_Type;
567 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
568
569 l_index BINARY_INTEGER default 0;
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
689 | Set the message |
686 arp_util.debug('Cache_Receivables_Trx: ' || 'No Adjustment Reason codes', G_MSG_HIGH);
687 END IF;
688 /*-----------------------------------------------+
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');
804 END Cache_Ussgl_code;
801 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
802 RETURN;
803
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 |
920 | - code combination ids |
917 | - ar_lookups for type = ADJUSTMENT_REASON |
918 | - ussgl transaction codes |
919 | - receivables trx for type = 'ADJUST' |
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
1029 /*-------------------------------------------------+
1026 RETURN ;
1027 END IF;
1028
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 |
1138 | |
1135 | |
1136 | MODIFICATION HISTORY |
1137 | Vivek Halder 13-JUN-97 |
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 );
1266 END Within_approval_limits;
1263 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1264 RETURN;
1265
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 |
1380 | transaction for which the adjustment is to be created |
1377 | |
1378 | DESCRIPTION |
1379 | This routine validates the payment schedule id of the |
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 ) ;
1485
1482 p_return_status := FND_API.G_RET_STS_ERROR;
1483 RETURN ;
1484 END ;
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;
1603
1600 RETURN ;
1601
1602 END IF ;
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 BEGIN
1704
1705 IF PG_DEBUG in ('Y', 'C') THEN
1706 arp_util.debug('Validate_amount()+', G_MSG_MEDIUM);
1707 END IF;
1708
1709 /*------------------------------------------+
1710 | Initialize the return status to SUCCESS |
1714
1711 +------------------------------------------*/
1712
1713 p_return_status := FND_API.G_RET_STS_SUCCESS;
1715 /*-----------------------------------------------+
1716 | If the type is INVOICE then the amount must |
1717 | close the invoice |
1718 +-----------------------------------------------*/
1719
1720 IF ( p_adj_rec.type = 'INVOICE' )
1721 THEN
1722
1723 /*-----------------------------------------------+
1724 | If amount is not specified then set it to |
1725 | close the transaction |
1726 +-----------------------------------------------*/
1727 IF ( p_adj_rec.amount IS NULL or p_adj_rec.amount = 0 )
1728 THEN
1729 /*-----------------------------------------------+
1730 | If amount is not specifiedand the amount due |
1731 | remaining is zero then should not create adj |
1732 +-----------------------------------------------*/
1733 IF ( p_ps_rec.amount_due_remaining = 0 )
1734 THEN
1735 aapi_message(
1736 p_application_name =>'AR',
1737 p_message_name => 'AR_AAPI_ADR_ZERO_INV'
1738 ) ;
1739 p_return_status := FND_API.G_RET_STS_ERROR ;
1740 RETURN ;
1741 END IF;
1742 p_adj_rec.amount := - p_ps_rec.amount_due_remaining ;
1743 ELSE
1744 IF ( p_adj_rec.amount + p_ps_rec.amount_due_remaining <> 0 )
1745 THEN
1746 aapi_message(
1747 p_application_name =>'AR',
1748 p_message_name => 'AR_TW_VAL_AMT_ADJ_INV'
1749 ) ;
1750 p_return_status := FND_API.G_RET_STS_ERROR;
1751 RETURN;
1752 END IF;
1753 END IF;
1754 END IF;
1755
1756 /*-----------------------------------------------+
1757 | Check if the adjustment amount is zero |
1758 +-----------------------------------------------*/
1759
1760 IF ( p_adj_rec.amount IS NULL OR p_adj_rec.amount = 0 )
1761 THEN
1762 /*--------------------------------------------+
1763 | Set the message and return |
1764 +--------------------------------------------*/
1765 aapi_message(
1766 p_application_name =>'AR',
1767 p_message_name => 'AR_AAPI_ADJ_AMOUNT_ZERO'
1768 ) ;
1769 p_return_status := FND_API.G_RET_STS_ERROR;
1770 RETURN;
1771 END IF;
1772
1773 /*------------------------------------------------+
1774 | Verify if amount is within user approval limits|
1775 +-------------------------------------------------*/
1776 /*----------------------------------------------------+
1777 | Special processing for bypassing limit check if |
1778 | created from 'CASH_ENGINE' and 'RECEIPT_REVERSAL' |
1779 +----------------------------------------------------*/
1780
1781 IF ( p_adj_rec.created_from LIKE 'CASH_ENGINE%' OR
1782 p_adj_rec.created_from LIKE 'RECEIPT_REVERSAL%' OR
1783 p_adj_rec.created_from LIKE 'SPLIT_MERGE%' OR
1784 p_adj_rec.created_from LIKE 'DMS_INTERFACE%' OR
1785 p_adj_rec.created_from LIKE 'ENHANCED_CASH%' )
1786 THEN
1787 l_approved_flag := FND_API.G_TRUE ;
1788 ELSE
1789 ar_adjvalidate_pub.Within_approval_limits (
1790 p_adj_rec.amount,
1791 p_ps_rec.invoice_currency_code,
1792 l_approved_flag,
1793 l_return_status
1794 ) ;
1795 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1796 THEN
1797 p_return_status := l_return_status ;
1798 RETURN;
1799 END IF ;
1800 END IF;
1801
1802 /*--------------------------------------------------+
1803 | Check Status. If null/blank then set the value |
1804 | based on l_approved_flag |
1805 +--------------------------------------------------*/
1806
1807 IF ( p_adj_rec.status IS NULL or p_adj_rec.status = ' ' )
1808 THEN
1809 IF ( l_approved_flag = FND_API.G_TRUE )
1810 THEN
1811 p_adj_rec.status := 'A' ;
1812 ELSE
1813 p_adj_rec.status := 'W' ;
1814 END IF ;
1815 ELSE
1816 /*-----------------------------------------------------+
1817 | Check valid status values provided by user |
1818 +-----------------------------------------------------*/
1819
1820 IF (p_adj_rec.status <> 'A' AND p_adj_rec.status <> 'W' AND
1821 p_adj_rec.status <> 'M' )
1822 THEN
1823 aapi_message(
1824 p_application_name =>'AR',
1825 p_message_name => 'AR_AAPI_INVALID_CREATE_STATUS',
1826 p_token1_name => 'STATUS',
1827 p_token1_value => p_adj_rec.status
1828 ) ;
1829 p_return_status := FND_API.G_RET_STS_ERROR ;
1830 RETURN ;
1831 END IF;
1832
1833 /*-----------------------------------------------------+
1834 | Handle the case for setting status to W if outside |
1838 IF ( l_approved_flag = FND_API.G_FALSE AND p_adj_rec.status = 'A' )
1835 | approval limits during creation of Adjustments |
1836 +-----------------------------------------------------*/
1837
1839 THEN
1840 p_adj_rec.status := 'W' ;
1841 END IF;
1842
1843 END IF;
1844
1845 IF PG_DEBUG in ('Y', 'C') THEN
1846 arp_util.debug('Validate_Amount()-', G_MSG_MEDIUM);
1847 END IF;
1848
1849 RETURN ;
1850
1851 EXCEPTION
1852 WHEN OTHERS THEN
1853
1854 IF PG_DEBUG in ('Y', 'C') THEN
1855 arp_util.debug('EXCEPTION: Validate_Amount() ', G_MSG_UERROR);
1856 END IF;
1857
1858 /*-----------------------------------------------+
1859 | Set unexpected error message and status |
1860 +-----------------------------------------------*/
1861 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Amount');
1862 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1863 RETURN;
1864
1865 END Validate_Amount;
1866
1867 /*===========================================================================+
1868 | PROCEDURE |
1869 | Validate_Rcvtrxccid |
1870 | |
1871 | DESCRIPTION |
1872 | This routine validates the Receivables Trx Id and CCId |
1873 | It sets the set_of_books_id value in the adjustment record |
1874 | and also the code combination id (if required) |
1875 | |
1876 | |
1877 | SCOPE - PUBLIC |
1878 | |
1879 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1880 | arp_util.disable_debug |
1881 | arp_util.enable_debug |
1882 | fnd_api.g_exc_unexpected_error |
1883 | fnd_api.g_ret_sts_error |
1884 | fnd_api.g_ret_sts_error |
1885 | fnd_api.g_ret_sts_success |
1886 | fnd_api.to_boolean |
1887 | |
1888 | ARGUMENTS : IN: |
1889 | |
1890 | OUT: |
1891 | p_return_status |
1892 | |
1893 | IN/ OUT: |
1894 | p_adj_rec |
1895 | |
1896 | RETURNS : NONE |
1897 | |
1898 | NOTES |
1899 | |
1900 | MODIFICATION HISTORY |
1901 | Vivek Halder 13-JUN-97 |
1902 | |
1903 +===========================================================================*/
1904
1905 PROCEDURE Validate_Rcvtrxccid (
1906 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
1907 p_return_status IN OUT NOCOPY varchar2
1908 ) IS
1909
1910 l_index number;
1911 l_set_of_books_id ar_receivables_trx.set_of_books_id%type := NULL;
1912 l_cc_id ar_receivables_trx.code_combination_id%type := NULL;
1913 l_count number;
1914 l_found BOOLEAN;
1915 BEGIN
1916
1917 IF PG_DEBUG in ('Y', 'C') THEN
1918 arp_util.debug('Validate_Rcvtrxccid()+', G_MSG_MEDIUM);
1919 END IF;
1920
1921 /*------------------------------------------+
1922 | Initialize the return status to ERROR |
1923 +------------------------------------------*/
1924
1925 p_return_status := FND_API.G_RET_STS_ERROR;
1926
1927 l_found := FALSE ;
1928
1929 FOR l_index IN 1..G_RCVTRX_TBL.COUNT LOOP
1930
1931 IF (p_adj_rec.receivables_trx_id =
1932 G_RCVTRX_TBL(l_index).receivables_trx_id )
1933 THEN
1934 G_receivables_name := G_RCVTRX_TBL(l_index).name ;
1935 l_cc_id := G_RCVTRX_TBL(l_index).code_combination_id;
1936 l_found := TRUE ;
1937 EXIT ;
1938 END IF;
1939
1940 END LOOP;
1941
1942 IF ( NOT l_found )
1943 THEN
1944 /*-----------------------------------------------+
1945 | Set the message |
1946 +-----------------------------------------------*/
1947 aapi_message(
1951 p_token1_value => to_char(p_adj_rec.receivables_trx_id)
1948 p_application_name =>'AR',
1949 p_message_name => 'AR_AAPI_INVALID_RCVABLE_TRX_ID',
1950 p_token1_name =>'RECEIVABLES_TRX_ID',
1952 ) ;
1953 RETURN ;
1954 END IF;
1955
1956 /*--------------------------------------------+
1957 | Check the Code Combination Id |
1958 | If no value is provided default it to the |
1959 | code combination Id of the receivables Trx |
1960 +--------------------------------------------*/
1961
1962 IF ( p_adj_rec.code_combination_id IS NULL OR
1963 p_adj_rec.code_combination_id = 0 )
1964 THEN
1965 /*--------------------------------------------+
1966 | If no default value exists and none is |
1967 | provided by user then set error |
1968 +--------------------------------------------*/
1969
1970 IF ( l_cc_id IS NULL OR l_cc_id = 0 )
1971 THEN
1972 aapi_message(
1973 p_application_name =>'AR',
1974 p_message_name => 'AR_AAPI_NO_CCID_FOR_ACTIVITY',
1975 p_token1_name => 'RECEIVABLES_TRX_ID',
1976 p_token1_value => to_char(p_adj_rec.receivables_trx_id)
1977 ) ;
1978 RETURN ;
1979 END IF ;
1980
1981 /*--------------------------------------------+
1982 | Else default to the CCid of the Receivables |
1983 | Activity |
1984 +--------------------------------------------*/
1985
1986 p_adj_rec.code_combination_id := l_cc_id ;
1987
1988 ELSE
1989 /*--------------------------------------------+
1990 | Validate the code combination Id provided |
1991 +--------------------------------------------*/
1992
1993 l_found := FALSE ;
1994
1995 FOR l_index IN 1..G_CCID_TBL.COUNT LOOP
1996
1997 IF (G_CCID_TBL.EXISTS (p_adj_rec.code_combination_id))
1998 THEN
1999 l_found := TRUE;
2000 EXIT ;
2001 END IF;
2002
2003 END LOOP;
2004
2005 IF ( NOT l_found)
2006 THEN
2007
2008 /*------------------------------------------+
2009 | Check the code combination from database |
2010 +------------------------------------------*/
2011 BEGIN
2012
2013 l_count := 0 ;
2014 SELECT count(*)
2015 INTO l_count
2016 FROM gl_code_combinations
2017 WHERE code_combination_id = p_adj_rec.code_combination_id
2018 AND enabled_flag = 'Y'
2019 AND SYSDATE BETWEEN NVL(start_date_active, sysdate)
2020 AND NVL(end_date_active, sysdate);
2021
2022 EXCEPTION
2023 WHEN OTHERS THEN
2024 /*---------------------------------------------+
2025 |Set unexpected error message and status |
2026 +---------------------------------------------*/
2027 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Rcvtrxccid');
2028 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2029 RETURN;
2030 END ;
2031
2032 IF ( l_count <> 1 )
2033 THEN
2034 /*-------------------------------------------+
2035 | Set the message |
2036 +-------------------------------------------*/
2037 aapi_message(
2038 p_application_name =>'AR',
2039 p_message_name => 'AR_AAPI_INVALID_CCID',
2040 p_token1_name => 'CCID',
2041 p_token1_value => p_adj_rec.code_combination_id
2042 ) ;
2043 RETURN ;
2044 END IF;
2045
2046 END IF;
2047
2048 /*--------------------------------------------+
2049 | Check that if the Profile Option : Allow |
2050 | Override of default activity is set to Y |
2051 | then value must be equal to l_cc_id |
2052 +--------------------------------------------*/
2053
2054 IF ( g_context_rec.override_activity_option = 'N' AND
2055 l_cc_id IS NOT NULL )
2056 THEN
2057 IF ( p_adj_rec.code_combination_id <> l_cc_id )
2058 THEN
2059 aapi_message (
2060 p_application_name =>'AR',
2061 p_message_name => 'AR_AAPI_OVERRIDE_CCID_DISALLOW'
2062 ) ;
2063 RETURN ;
2064 END IF;
2065 END IF;
2066
2067 END IF;
2068
2069 /*-----------------------------------------------+
2070 | Set the Set of books Id |
2071 +-----------------------------------------------*/
2072
2073 p_adj_rec.set_of_books_id := arp_global.set_of_books_id ;
2074
2075 p_return_status := FND_API.G_RET_STS_SUCCESS ;
2076
2077 IF PG_DEBUG in ('Y', 'C') THEN
2078 arp_util.debug('Validate_Rcvtrxccid()-', G_MSG_MEDIUM);
2082
2079 END IF;
2080
2081 RETURN ;
2083 EXCEPTION
2084 WHEN OTHERS THEN
2085
2086 IF PG_DEBUG in ('Y', 'C') THEN
2087 arp_util.debug('EXCEPTION: Validate_Rcvtrxccid', G_MSG_UERROR);
2088 END IF;
2089 /*-----------------------------------------------+
2090 | Set unexpected error message and status |
2091 +-----------------------------------------------*/
2092 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Rcvtrxccid');
2093 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2094
2095 RETURN;
2096
2097 END Validate_Rcvtrxccid;
2098
2099 /*===========================================================================+
2100 | PROCEDURE |
2101 | Validate_dates |
2102 | DESCRIPTION |
2103 | This routine validates the apply and gl dates for both |
2104 | creation and reversal |
2105 | |
2106 | SCOPE - PUBLIC |
2107 | |
2108 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2109 | |
2110 | ARGUMENTS : IN: |
2111 | p_adj_rec |
2112 | OUT: |
2113 | p_return_status |
2114 | IN/ OUT: |
2115 | |
2116 | RETURNS : NONE |
2117 | |
2118 | NOTES |
2119 | |
2120 | MODIFICATION HISTORY |
2121 | Vivek Halder 13-JUN-97 |
2122 | |
2123 +===========================================================================*/
2124
2125 PROCEDURE Validate_dates (
2126 p_apply_date IN ar_adjustments.apply_date%type,
2127 p_gl_date IN ar_adjustments.gl_date%type,
2128 p_ps_rec IN ar_payment_schedules%rowtype,
2129 p_return_status IN OUT NOCOPY varchar2
2130 ) IS
2131
2132 l_index number;
2133 l_found_flag BOOLEAN;
2134 l_count number ;
2135 l_set_of_books_id AR_SYSTEM_PARAMETERS.SET_OF_BOOKS_ID%TYPE ;
2136
2137 BEGIN
2138
2139 IF PG_DEBUG in ('Y', 'C') THEN
2140 arp_util.debug('Validate_dates()+', G_MSG_MEDIUM);
2141 END IF;
2142
2143 /*------------------------------------------+
2144 | Initialize the return status to ERROR |
2145 +------------------------------------------*/
2146
2147 p_return_status := FND_API.G_RET_STS_ERROR;
2148
2149 /*------------------------------------------+
2150 | The dates should not be null |
2151 +-----------------------------------------*/
2152
2153 IF ( p_apply_date IS NULL )
2154 THEN
2155 aapi_message(
2156 p_application_name =>'AR',
2157 p_message_name => 'AR_AAPI_NO_APPLY_DATE'
2158 ) ;
2159 RETURN ;
2160 END IF;
2161
2162 IF ( p_gl_date IS NULL )
2163 THEN
2164 aapi_message(
2165 p_application_name =>'AR',
2166 p_message_name => 'AR_AAPI_NO_GL_DATE'
2167 ) ;
2168 RETURN ;
2169 END IF;
2170
2171 /*------------------------------------------+
2172 | Validate from GL date from period cache |
2173 | Check that it lies in open/future period|
2174 +-----------------------------------------*/
2175
2176 l_found_flag := FALSE ;
2177
2178 FOR l_index IN 1..G_GLPERIOD_TBL.COUNT LOOP
2179
2180 IF (trunc(p_gl_date) >=
2181 nvl(G_GLPERIOD_TBL(l_index).start_date, trunc(p_gl_date))
2182 AND
2183 trunc(p_gl_date) <=
2184 nvl(G_GLPERIOD_TBL(l_index).end_date,trunc(p_gl_date))
2185 )
2186 THEN
2187 l_found_flag := TRUE ;
2188 EXIT ;
2189 END IF;
2190
2191 END LOOP;
2192
2193 /*------------------------------------------+
2194 | If it does not exist in cache validate it|
2195 | from database |
2196 +------------------------------------------*/
2197
2198 IF ( NOT l_found_flag )
2199 THEN
2200
2201 select set_of_books_id
2202 into l_set_of_books_id
2203 from ar_system_parameters;
2204
2205 l_count := 0 ;
2206
2210 gl_sets_of_books b
2207 SELECT count(*)
2208 INTO l_count
2209 FROM gl_period_statuses g,
2211 WHERE g.application_id = 222
2212 AND g.set_of_books_id = l_set_of_books_id
2213 AND g.set_of_books_id = b.set_of_books_id
2214 AND g.period_type = b.accounted_period_type
2215 AND g.adjustment_period_flag = 'N'
2216 AND g.closing_status IN ('O','F')
2217 AND trunc(p_gl_date) BETWEEN nvl(trunc(g.start_date),
2218 trunc(p_gl_date))
2219 AND nvl(trunc(g.end_date),trunc(p_gl_date)) ;
2220
2221 IF ( l_count > 0 )
2222 THEN
2223 l_found_flag := TRUE ;
2224 END IF;
2225
2226 END IF;
2227
2228 /*------------------------------------------+
2229 | If no valid period found then set message|
2230 | and return |
2231 +------------------------------------------*/
2232
2233 IF ( NOT l_found_flag )
2234 THEN
2235 aapi_message(
2236 p_application_name =>'AR',
2237 p_message_name => 'AR_AAPI_GLDATE_INVALID_PERIOD',
2238 p_token1_name => 'GL_DATE',
2239 p_token1_value => to_char(p_gl_date,'DD-MON-YYYY')
2240 ) ;
2241 RETURN;
2242 END IF;
2243
2244 /*---------------------------------------+
2245 | Check that apply date should be equal |
2246 | to or greater than the transaction date|
2247 +---------------------------------------*/
2248
2249 IF ( trunc(p_apply_date) < trunc(p_ps_rec.trx_date) )
2250 THEN
2251 aapi_message(
2252 p_application_name =>'AR',
2253 p_message_name => 'AR_AAPI_APPLYDATE_LT_TRXDATE',
2254 p_token1_name => 'APPLY_DATE',
2255 p_token1_value => to_char(p_apply_date,'DD-MON-YYYY'),
2256 p_token2_name => 'TRX_DATE',
2257 p_token2_value => to_char(p_ps_rec.trx_date,'DD-MON-YYYY')
2258 ) ;
2259 RETURN;
2260 END IF;
2261
2262 /*---------------------------------------+
2263 | Check that GL date should be equal to |
2264 | or greater than the transaction GL date|
2265 +---------------------------------------*/
2266
2267 IF ( trunc(p_gl_date) < trunc(p_ps_rec.gl_date) )
2268 THEN
2269 aapi_message(
2270 p_application_name =>'AR',
2271 p_message_name => 'AR_AAPI_GLDATE_LT_TRXGLDATE',
2272 p_token1_name => 'GL_DATE',
2273 p_token1_value => to_char(p_gl_date,'DD-MON-YYYY'),
2274 p_token2_name => 'TRX_GL_DATE',
2275 p_token2_value => to_char(p_ps_rec.gl_date,'DD-MON-YYYY')
2276 ) ;
2277 RETURN;
2278 END IF;
2279
2280 /*------------------------------------+
2281 | Check that GL date should be equal |
2282 | to or greater than the apply date |
2283 +------------------------------------*/
2284
2285 /* ------------------------------This check is no longer valid
2286 IF ( trunc(p_gl_date) < trunc(p_apply_date) )
2287 THEN
2288 aapi_message(
2289 p_application_name =>'AR',
2290 p_message_name => 'AR_AAPI_GLDATE_LT_APPLYDATE',
2291 p_token1_name => 'GL_DATE',
2292 p_token1_value => to_char(p_gl_date,'DD-MON-YYYY'),
2293 p_token2_name => 'APPLY_DATE',
2294 p_token2_value => to_char(p_apply_date,'DD-MON-YYYY')
2295 ) ;
2296 RETURN;
2297 END IF;
2298 ------------------------------------------------------------ */
2299 p_return_status := FND_API.G_RET_STS_SUCCESS;
2300
2301 IF PG_DEBUG in ('Y', 'C') THEN
2302 arp_util.debug('Validate_dates()-', G_MSG_MEDIUM);
2303 END IF;
2304
2305 RETURN ;
2306
2307 EXCEPTION
2308 WHEN OTHERS THEN
2309
2310 IF PG_DEBUG in ('Y', 'C') THEN
2311 arp_util.debug('EXCEPTION: Validate_dates ', G_MSG_UERROR);
2312 END IF;
2313 /*-----------------------------------------------+
2314 | Set unexpected error message and status |
2315 +-----------------------------------------------*/
2316 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_dates' );
2317 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2318
2319 RETURN;
2320
2321 END Validate_dates;
2322
2323
2324 /*===========================================================================+
2325 | PROCEDURE |
2326 | Validate_doc_seq |
2327 | |
2328 | DESCRIPTION |
2329 | This routine validates the Document Sequence value and sets |
2330 | the Id also |
2331 | |
2335 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2332 | |
2333 | SCOPE - PRIVATE |
2334 | |
2336 | arp_util.disable_debug |
2337 | arp_util.enable_debug |
2338 | fnd_api.g_exc_unexpected_error |
2339 | fnd_api.g_ret_sts_error |
2340 | fnd_api.g_ret_sts_error |
2341 | fnd_api.g_ret_sts_success |
2342 | fnd_api.to_boolean |
2343 | |
2344 | ARGUMENTS : IN: |
2345 | OUT: |
2346 | p_return_status |
2347 | |
2348 | IN/ OUT: |
2349 | p_adj_rec |
2350 | |
2351 | |
2352 | |
2353 | RETURNS : NONE |
2354 | |
2355 | NOTES |
2356 | |
2357 | MODIFICATION HISTORY |
2358 | Vivek Halder 13-JUN-97 |
2359 | |
2360 +===========================================================================*/
2361
2362 PROCEDURE Validate_doc_seq (
2363 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2364 p_return_status IN OUT NOCOPY varchar2
2365 ) IS
2366
2367 l_dummy BINARY_INTEGER;
2368 l_sequence_name fnd_document_sequences.name%type;
2369 l_doc_sequence_id fnd_document_sequences.doc_sequence_id%type ;
2370
2371 BEGIN
2372
2373 IF PG_DEBUG in ('Y', 'C') THEN
2374 arp_util.debug('Validate_doc_seq()+', G_MSG_MEDIUM);
2375 END IF;
2376
2377 /*------------------------------------------+
2378 | Initialize the return status to SUCCESS |
2379 +------------------------------------------*/
2380
2381 p_return_status := FND_API.G_RET_STS_SUCCESS;
2382
2383 /*------------------------------------------------+
2384 | Document sequences are only applicable if the |
2385 | unique seq number option is not equal to N |
2386 +------------------------------------------------*/
2387
2388 IF ( NVL(g_context_rec.unique_seq_numbers, 'N') = 'N' )
2389 THEN
2390 IF ( p_adj_rec.doc_sequence_value IS NOT NULL OR
2391 p_adj_rec.doc_sequence_value <> 0 )
2392 THEN
2393 aapi_message(
2394 p_application_name =>'AR',
2395 p_message_name => 'AR_AAPI_DOC_SEQ_NOT_REQD',
2396 p_token1_name => 'DOCUMENT_SEQ',
2397 p_token1_value => to_char(p_adj_rec.doc_sequence_value)
2398 );
2399 p_return_status := FND_API.G_RET_STS_ERROR;
2400 END IF;
2401 RETURN ;
2402 END IF;
2403
2404 /*-----------------------------+
2405 | Get the document sequence. |
2406 +------------------------------*/
2407
2408 p_adj_rec.doc_sequence_id := NULL;
2409
2410 fnd_seqnum.get_seq_name (
2411 arp_global.G_AR_APP_ID,
2412 G_receivables_name,
2413 arp_global.set_of_books_id,
2414 'M',
2415 p_adj_rec.apply_date,
2416 l_sequence_name,
2417 p_adj_rec.doc_sequence_id,
2418 l_dummy
2419 );
2420
2421 IF ( l_sequence_name IS NOT NULL AND
2422 p_adj_rec.doc_sequence_id IS NOT NULL )
2423 THEN
2424 /*-----------------------------------------+
2425 | Automatic Document Numbering case |
2426 | Document seuqence value should not exist|
2427 +-----------------------------------------*/
2428
2429 IF ( p_adj_rec.doc_sequence_value IS NOT NULL OR
2430 p_adj_rec.doc_sequence_value <> 0 )
2431 THEN
2432 aapi_message(
2433 p_application_name =>'AR',
2434 p_message_name => 'AR_AAPI_DOC_SEQ_NOT_REQD',
2435 p_token1_name => 'DOCUMENT_SEQ',
2436 p_token1_value => to_char(p_adj_rec.doc_sequence_value)
2437 );
2438 p_return_status := FND_API.G_RET_STS_ERROR;
2439 RETURN ;
2443 | Get the document sequence value |
2440 END IF;
2441
2442 /*-----------------------------------------+
2444 +-----------------------------------------*/
2445
2446 p_adj_rec.doc_sequence_value :=
2447 fnd_seqnum.get_next_sequence (
2448 arp_global.G_AR_APP_ID,
2449 G_receivables_name,
2450 arp_global.set_of_books_id,
2451 'M',
2452 p_adj_rec.apply_date,
2453 l_sequence_name,
2454 p_adj_rec.doc_sequence_id
2455 );
2456
2457 ELSIF ( p_adj_rec.doc_sequence_id IS NOT NULL AND
2458 p_adj_rec.doc_sequence_value IS NOT NULL )
2459 THEN
2460 /*------------------------------------+
2461 | Manual Document Numbering case |
2462 | with the document value specified. |
2463 | Use the specified value. |
2464 +-------------------------------------*/
2465 NULL;
2466
2467 ELSIF ( p_adj_rec.doc_sequence_id IS NOT NULL AND
2468 p_adj_rec.doc_sequence_value IS NULL )
2469 THEN
2470 /*-------------------------------------------+
2471 | Manual Document Numbering case |
2472 | with the document value not specified. |
2473 | Generate a document value mandatory error |
2474 +-----------------------------------------*/
2475 aapi_message(
2476 p_application_name =>'FND',
2477 p_message_name => 'UNIQUE-NO VALUE'
2478 );
2479 p_return_status := FND_API.G_RET_STS_ERROR;
2480 RETURN ;
2481 END IF;
2482
2483 IF PG_DEBUG in ('Y', 'C') THEN
2484 arp_util.debug('Validate_doc_seq()-', G_MSG_MEDIUM);
2485 END IF;
2486
2487 RETURN ;
2488
2489 EXCEPTION
2490
2491 WHEN NO_DATA_FOUND THEN
2492 /*-----------------------------------------+
2493 | No document assignment was found. |
2494 | Generate an error if document numbering |
2495 | is mandatory. |
2496 +-----------------------------------------*/
2497 IF (g_context_rec.unique_seq_numbers = 'A' )
2498 THEN
2499 aapi_message(
2500 p_application_name =>'FND',
2501 p_message_name => 'UNIQUE-ALWAYS USED'
2502 );
2503 p_return_status := FND_API.G_RET_STS_ERROR;
2504 ELSE
2505 p_adj_rec.doc_sequence_id := NULL;
2506 p_adj_rec.doc_sequence_value := NULL;
2507 END IF;
2508 RETURN;
2509
2510 WHEN OTHERS THEN
2511
2512 IF PG_DEBUG in ('Y', 'C') THEN
2513 arp_util.debug('EXCEPTION: Validate_doc_seq ', G_MSG_UERROR);
2514 END IF;
2515 /*-----------------------------------------------+
2516 | Set unexpected error message and status |
2517 +-----------------------------------------------*/
2518 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_doc_seq' );
2519 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2520 RETURN;
2521
2522 END Validate_doc_seq;
2523
2524
2525 /*===========================================================================+
2526 | PROCEDURE Validate_reason_code |
2527 | |
2528 | DESCRIPTION |
2529 | This routine validates the reason code of adjustment |
2530 | |
2531 | |
2532 | |
2533 | SCOPE - PUBLIC |
2534 | |
2535 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2536 | arp_util.disable_debug |
2537 | arp_util.enable_debug |
2538 | fnd_api.g_exc_unexpected_error |
2539 | fnd_api.g_ret_sts_error |
2540 | fnd_api.g_ret_sts_error |
2541 | fnd_api.g_ret_sts_success |
2542 | fnd_api.to_boolean |
2543 | |
2544 | ARGUMENTS : IN: |
2545 | |
2546 | OUT: |
2547 | |
2548 | IN/ OUT: |
2549 | |
2553 | RETURNS : NONE |
2550 | p_return_status |
2551 | p_adj_rec |
2552 | |
2554 | |
2555 | NOTES |
2556 | |
2557 | MODIFICATION HISTORY |
2558 | Vivek Halder 13-JUN-97 |
2559 | |
2560 +===========================================================================*/
2561
2562 PROCEDURE Validate_Reason_code (
2563 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2564 p_return_status IN OUT NOCOPY varchar2
2565 ) IS
2566
2567 l_index number;
2568 l_found BOOLEAN;
2569 BEGIN
2570
2571 IF PG_DEBUG in ('Y', 'C') THEN
2572 arp_util.debug('Validate_Reason_code()+', G_MSG_MEDIUM);
2573 END IF;
2574
2575 /*------------------------------------------+
2576 | Initialize the return status to ERROR |
2577 +------------------------------------------*/
2578
2579 p_return_status := FND_API.G_RET_STS_ERROR;
2580
2581 /*------------------------------------------+
2582 | Validate only is value is provided |
2583 +------------------------------------------*/
2584
2585 IF ( p_adj_rec.reason_code IS NOT NULL AND
2586 p_adj_rec.reason_code <> ' ' )
2587 THEN
2588
2589 l_found := FALSE ;
2590
2591 FOR l_index IN 1..G_REASON_TBL.COUNT LOOP
2592
2593 IF (p_adj_rec.reason_code = G_REASON_TBL(l_index).lookup_code)
2594 THEN
2595 l_found := TRUE ;
2596 EXIT ;
2597 END IF;
2598
2599 END LOOP;
2600
2601 IF ( NOT l_found )
2602 THEN
2603 /*-----------------------------------------------+
2604 | Set the message |
2605 +-----------------------------------------------*/
2606 aapi_message(
2607 p_application_name =>'AR',
2608 p_message_name => 'AR_AAPI_INVALID_REASON_CODE',
2609 p_token1_name => 'REASON_CODE',
2610 p_token1_value => p_adj_rec.reason_code
2611 ) ;
2612 RETURN ;
2613
2614 END IF;
2615
2616 END IF ;
2617
2618 p_return_status := FND_API.G_RET_STS_SUCCESS ;
2619
2620 IF PG_DEBUG in ('Y', 'C') THEN
2621 arp_util.debug('Validate_Reason_Code()-', G_MSG_MEDIUM);
2622 END IF;
2623
2624 RETURN ;
2625
2626 EXCEPTION
2627 WHEN OTHERS THEN
2628
2629 IF PG_DEBUG in ('Y', 'C') THEN
2630 arp_util.debug('EXCEPTION: Validate_Reason_code ', G_MSG_UERROR);
2631 END IF;
2632 /*-----------------------------------------------+
2633 | Set unexpected error message and status |
2634 +-----------------------------------------------*/
2635 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Reason_code' );
2636 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2637
2638 RETURN;
2639
2640 END Validate_Reason_code;
2641
2642
2643 /*===========================================================================+
2644 | PROCEDURE Validate_Desc_Flexfield |
2645 | |
2646 | DESCRIPTION |
2647 | Validates descriptive flexfields using the flex API. |
2648 | |
2649 | SCOPE - PRIVATE |
2650 | |
2651 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2652 | |
2653 | ARGUMENTS : IN: |
2654 | OUT: |
2655 | IN/ OUT: |
2656 | |
2657 | RETURNS : NONE |
2658 | |
2659 | NOTES |
2660 | This validation is currently disabled because it doesn't |
2661 | work correctly. The descriptive flexfield API functions |
2662 | that this routine uses are not yet production code and are |
2663 | unstable. |
2664 | |
2665 | MODIFICATION HISTORY |
2666 | Vivek Halder 01-JUL-97 Created |
2667 | |
2671 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2668 +===========================================================================*/
2669
2670 PROCEDURE Validate_Desc_Flexfield(
2672 p_return_status IN OUT NOCOPY varchar2
2673 ) IS
2674
2675 l_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%type;
2676
2677 BEGIN
2678
2679 IF PG_DEBUG in ('Y', 'C') THEN
2680 arp_util.debug('Validate_Desc_Flexfield()+', G_MSG_MEDIUM);
2681 END IF;
2682
2683 /*------------------------------------------+
2684 | Initialize the return status to SUCCESS |
2685 +------------------------------------------*/
2686
2687 p_return_status := FND_API.G_RET_STS_SUCCESS;
2688
2689 /*------------------------------------------+
2690 | Get the flexfield name |
2691 +------------------------------------------*/
2692
2693 BEGIN
2694
2695 SELECT descriptive_flexfield_name
2696 INTO l_flex_name
2697 FROM fnd_descriptive_flexs
2698 WHERE application_id = arp_global.G_AR_APP_ID AND
2699 application_table_name like 'AR_ADJUSTMENTS' ;
2700
2701 EXCEPTION
2702 WHEN NO_DATA_FOUND THEN
2703 RETURN;
2704
2705 WHEN OTHERS THEN
2706 IF PG_DEBUG in ('Y', 'C') THEN
2707 arp_util.debug('EXCEPTION: Validate_Desc_Flexfield', G_MSG_UERROR);
2708 END IF;
2709 /*-----------------------------------------------+
2710 | Set unexpected error message and status |
2711 +-----------------------------------------------*/
2712 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Desc_Flexfield' );
2713 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2714 RETURN;
2715 END ;
2716
2717
2718 /*--------------------------------------------------------------------+
2719 | Call the flexfield routines to validate the transaction flexfield. |
2720 +--------------------------------------------------------------------*/
2721
2722 fnd_flex_descval.set_context_value(p_adj_rec.attribute_category);
2723
2724 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_adj_rec.attribute1);
2725 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_adj_rec.attribute2);
2726 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_adj_rec.attribute3);
2727 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_adj_rec.attribute4);
2728 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_adj_rec.attribute5);
2729 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_adj_rec.attribute6);
2730 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_adj_rec.attribute7);
2731 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_adj_rec.attribute8);
2732 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_adj_rec.attribute9);
2733 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_adj_rec.attribute10);
2734 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_adj_rec.attribute11);
2735 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_adj_rec.attribute12);
2736 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_adj_rec.attribute13);
2737 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_adj_rec.attribute14);
2738 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_adj_rec.attribute15);
2739
2740 IF ( NOT fnd_flex_descval.validate_desccols ('AR', l_flex_name) )
2741 THEN
2742 p_return_status := FND_API.G_RET_STS_ERROR;
2743 aapi_message(
2744 p_application_name =>'AR',
2745 p_message_name => 'AR_AAPI_INVALID_DESC_FLEX'
2746 ) ;
2747 RETURN ;
2748 END IF;
2749
2750 IF PG_DEBUG in ('Y', 'C') THEN
2751 arp_util.debug('Validate_Desc_Flexfield()-', G_MSG_MEDIUM);
2752 END IF;
2753
2754 EXCEPTION
2755 WHEN OTHERS THEN
2756 IF PG_DEBUG in ('Y', 'C') THEN
2757 arp_util.debug('EXCEPTION: Validate_Desc_Flexfield', G_MSG_UERROR);
2758 END IF;
2759 /*-----------------------------------------------+
2760 | Set unexpected error message and status |
2761 +-----------------------------------------------*/
2762 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Desc_Flexfield' );
2763 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2764
2765 RETURN;
2766
2767 END Validate_Desc_Flexfield;
2768
2769 /*==========================================================================+
2770 | PROCEDURE |
2771 | Validate_Created_From |
2772 | |
2773 | DESCRIPTION |
2774 | This routine validates the Created From field of adjustment |
2775 | |
2776 | |
2777 | SCOPE - PRIVATE |
2778 | |
2779 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2780 | arp_util.disable_debug |
2784 | fnd_api.g_ret_sts_error |
2781 | arp_util.enable_debug |
2782 | fnd_api.g_exc_unexpected_error |
2783 | fnd_api.g_ret_sts_error |
2785 | fnd_api.g_ret_sts_success |
2786 | fnd_api.to_boolean |
2787 | |
2788 | ARGUMENTS : IN: |
2789 | p_adj_rec |
2790 | OUT: |
2791 | p_return_status |
2792 | |
2793 | IN/ OUT: |
2794 | |
2795 | RETURNS : NONE |
2796 | |
2797 | NOTES |
2798 | |
2799 | MODIFICATION HISTORY |
2800 | Vivek Halder 13-JUN-97 |
2801 | |
2802 +===========================================================================*/
2803
2804 PROCEDURE Validate_Created_From (
2805 p_adj_rec IN ar_adjustments%rowtype,
2806 p_return_status IN OUT NOCOPY varchar2
2807 ) IS
2808
2809 BEGIN
2810
2811 IF PG_DEBUG in ('Y', 'C') THEN
2812 arp_util.debug('Validate_Created_From()+', G_MSG_MEDIUM);
2813 END IF;
2814
2815 /*------------------------------------------+
2816 | Initialize the return status to SUCCESS |
2817 +------------------------------------------*/
2818
2819 p_return_status := FND_API.G_RET_STS_SUCCESS;
2820
2821 IF ( p_adj_rec.created_from IS NULL OR
2822 p_adj_rec.created_from = ' ' )
2823 THEN
2824 /*------------------------------------------+
2825 | Set the message |
2826 +------------------------------------------*/
2827 aapi_message(
2828 p_application_name =>'AR',
2829 p_message_name => 'AR_AAPI_NO_CREATED_FROM'
2830 ) ;
2831 p_return_status := FND_API.G_RET_STS_ERROR;
2832 RETURN ;
2833 END IF;
2834
2835
2836 IF PG_DEBUG in ('Y', 'C') THEN
2837 arp_util.debug('Validate_Created_From ()-', G_MSG_MEDIUM);
2838 END IF;
2839
2840 RETURN ;
2841
2842 EXCEPTION
2843 WHEN OTHERS THEN
2844
2845 IF PG_DEBUG in ('Y', 'C') THEN
2846 arp_util.debug('EXCEPTION: Validate_Created_From()', G_MSG_UERROR);
2847 END IF;
2848 /*-----------------------------------------------+
2849 | Set unexpected error message and status |
2850 +-----------------------------------------------*/
2851 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Created_From'
2852 );
2853 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2854 RETURN;
2855
2856 END Validate_Created_From;
2857
2858 /*===========================================================================+
2859 | PROCEDURE |
2860 | Validate_Ussgl_code |
2861 | |
2862 | DESCRIPTION |
2863 | This routine validates the USSGL code of adjustment |
2864 | and also sets the context |
2865 | |
2866 | |
2867 | SCOPE - PRIVATE |
2868 | |
2869 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2870 | arp_util.disable_debug |
2871 | arp_util.enable_debug |
2872 | fnd_api.g_exc_unexpected_error |
2873 | fnd_api.g_ret_sts_error |
2874 | fnd_api.g_ret_sts_error |
2875 | fnd_api.g_ret_sts_success |
2876 | fnd_api.to_boolean |
2877 | |
2878 | ARGUMENTS : IN: |
2879 | OUT: |
2880 | p_return_status |
2881 | |
2885 | RETURNS : NONE |
2882 | IN/ OUT: |
2883 | p_adj_rec |
2884 | |
2886 | |
2887 | NOTES |
2888 | |
2889 | MODIFICATION HISTORY |
2890 | Vivek Halder 13-JUN-97 |
2891 | |
2892 +===========================================================================*/
2893
2894 PROCEDURE Validate_Ussgl_code (
2895 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2896 p_return_status IN OUT NOCOPY varchar2
2897 ) IS
2898
2899 l_index number;
2900
2901 BEGIN
2902
2903 IF PG_DEBUG in ('Y', 'C') THEN
2904 arp_util.debug('Validate_Ussgl_code()+', G_MSG_MEDIUM);
2905 END IF;
2906
2907 /*------------------------------------------+
2908 | Initialize the return status to ERROR |
2909 +------------------------------------------*/
2910
2911 p_return_status := FND_API.G_RET_STS_ERROR;
2912
2913 /*------------------------------------------+
2914 | Validate based on option |
2915 +------------------------------------------*/
2916
2917 IF ( g_context_rec.ussgl_option = 'Y' )
2918 THEN
2919 /*------------------------------------------+
2920 | Validate from the cache |
2921 +------------------------------------------*/
2922 FOR l_index IN 1..G_USSGL_TBL.COUNT LOOP
2923
2924 IF (p_adj_rec.ussgl_transaction_code =
2925 G_USSGL_TBL(l_index).ussgl_code)
2926 THEN
2927 p_adj_rec.ussgl_transaction_code_context :=
2928 G_USSGL_TBL(l_index).ussgl_context;
2929 p_return_status := FND_API.G_RET_STS_SUCCESS;
2930 EXIT ;
2931 END IF;
2932
2933 END LOOP;
2934
2935 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
2936 THEN
2937 /*-----------------------------------------------+
2938 | Set the message |
2939 +-----------------------------------------------*/
2940 aapi_message(
2941 p_application_name =>'AR',
2942 p_message_name => 'AR_AAPI_INVALID_USSGL_CODE',
2943 p_token1_name => 'USSGL_CODE',
2944 p_token1_value => p_adj_rec.ussgl_transaction_code
2945 ) ;
2946 RETURN;
2947 END IF;
2948
2949 ELSE
2950
2951 /*------------------------------------------+
2952 | No USSGL code should be provided |
2953 +------------------------------------------*/
2954 IF ( p_adj_rec.ussgl_transaction_code IS NOT NULL AND
2955 p_adj_rec.ussgl_transaction_code <> ' ' )
2956 THEN
2957 aapi_message(
2958 p_application_name =>'AR',
2959 p_message_name => 'AR_AAPI_USSGL_CODE_DISALLOW',
2960 p_token1_name => 'USSGL_CODE',
2961 p_token1_value => p_adj_rec.ussgl_transaction_code
2962 ) ;
2963 RETURN;
2964 ELSE
2965 p_return_status := FND_API.G_RET_STS_SUCCESS;
2966 END IF ;
2967 END IF ;
2968
2969 IF PG_DEBUG in ('Y', 'C') THEN
2970 arp_util.debug('Validate_Ussgl_Code()-', G_MSG_MEDIUM);
2971 END IF;
2972
2973 RETURN ;
2974
2975 EXCEPTION
2976 WHEN OTHERS THEN
2977
2978 IF PG_DEBUG in ('Y', 'C') THEN
2979 arp_util.debug('EXCEPTION: Validate_Ussgl_code ', G_MSG_UERROR);
2980 END IF;
2981 /*-----------------------------------------------+
2982 | Set unexpected error message and status |
2983 +-----------------------------------------------*/
2984 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Ussgl_code' );
2985 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2986
2987 RETURN;
2988
2989 END Validate_Ussgl_code;
2990
2991 /*==========================================================================+
2992 | PROCEDURE |
2993 | Validate_Associated_Receipt |
2994 | |
2995 | DESCRIPTION |
2996 | This routine validates the associated cash_receipt_id |
2997 | |
2998 | |
2999 | SCOPE - PUBLIC |
3000 | |
3001 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3005 | fnd_api.g_ret_sts_error |
3002 | arp_util.disable_debug |
3003 | arp_util.enable_debug |
3004 | fnd_api.g_exc_unexpected_error |
3006 | fnd_api.g_ret_sts_error |
3007 | fnd_api.g_ret_sts_success |
3008 | fnd_api.to_boolean |
3009 | |
3010 | ARGUMENTS : IN: |
3011 | p_adj_rec |
3012 | |
3013 | OUT: |
3014 | p_return_status |
3015 | |
3016 | IN/ OUT: |
3017 | |
3018 | |
3019 | RETURNS : NONE |
3020 | |
3021 | NOTES |
3022 | |
3023 | MODIFICATION HISTORY |
3024 | Vivek Halder 13-JUN-97 |
3025 | |
3026 +===========================================================================*/
3027
3028 PROCEDURE Validate_Associated_Receipt (
3029 p_adj_rec IN ar_adjustments%rowtype,
3030 p_return_status IN OUT NOCOPY varchar2
3031 ) IS
3032
3033 l_count number;
3034
3035 BEGIN
3036
3037 IF PG_DEBUG in ('Y', 'C') THEN
3038 arp_util.debug('Validate_Associated_Receipt()+', G_MSG_MEDIUM);
3039 END IF;
3040
3041 /*------------------------------------------+
3042 | Initialize the return status to SUCCESS |
3043 +------------------------------------------*/
3044
3045 p_return_status := FND_API.G_RET_STS_SUCCESS;
3046
3047 IF ( p_adj_rec.associated_cash_receipt_id IS NOT NULL AND
3048 p_adj_rec.associated_cash_receipt_id <> 0 )
3049 THEN
3050 /*------------------------------------------+
3051 | Validate the Cash Receipt Id |
3052 +------------------------------------------*/
3053
3054 l_count := 0 ;
3055
3056 SELECT count(*)
3057 INTO l_count
3058 FROM ar_cash_receipts
3059 WHERE cash_receipt_id = p_adj_rec.associated_cash_receipt_id ;
3060
3061 IF ( l_count <> 1 )
3062 THEN
3063 /*------------------------------------------+
3064 | Set the message |
3065 +------------------------------------------*/
3066 aapi_message(
3067 p_application_name =>'AR',
3068 p_message_name => 'AR_AAPI_INVALID_RECEIPT_ID',
3069 p_token1_name =>'ASSOCIATED_CASH_RECEIPT_ID',
3070 p_token1_value =>to_char(p_adj_rec.associated_cash_receipt_id)
3071 ) ;
3072 p_return_status := FND_API.G_RET_STS_ERROR;
3073 RETURN;
3074 END IF;
3075
3076 END IF ;
3077
3078 IF PG_DEBUG in ('Y', 'C') THEN
3079 arp_util.debug('Validate_Associated_Receipt()-', G_MSG_MEDIUM);
3080 END IF;
3081
3082 RETURN ;
3083
3084 EXCEPTION
3085 WHEN OTHERS THEN
3086
3087 IF PG_DEBUG in ('Y', 'C') THEN
3088 arp_util.debug('EXCEPTION: Validate_Associated_Receipt', G_MSG_UERROR);
3089 arp_util.debug('EXCEPTION: Validate_Associated_Receipt for Receipt Id '
3090 || p_adj_rec.associated_cash_receipt_id, G_MSG_HIGH );
3091 END IF;
3092 /*-----------------------------------------------+
3093 | Set unexpected error message and status |
3094 +-----------------------------------------------*/
3095 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Associated_Receipt');
3096 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3097 RETURN;
3098
3099 END Validate_Associated_Receipt;
3100 END ar_adjvalidate_pub;