[Home] [Help]
PACKAGE BODY: APPS.AR_ADJVALIDATE_PVT
Source
1 PACKAGE BODY AR_ADJVALIDATE_PVT AS
2 /* $Header: ARXVADJB.pls 120.16.12020000.4 2013/02/05 11:57:53 vpotti ship $*/
3 G_PKG_NAME CONSTANT VARCHAR2(30) :='AR_ADJVALIDATE_PVT';
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 G_cache_org_id ar_receivables_trx.org_id%TYPE;
15
16 TYPE Context_Rec_Type IS RECORD
17 (
18 validation_level NUMBER ,
19 ussgl_option fnd_profile_option_values.profile_option_value%type,
20 override_activity_option fnd_profile_option_values.profile_option_value%type,
21 unique_seq_numbers fnd_profile_option_values.profile_option_value%type
22 );
23
24 TYPE Lookup_Rec_Type IS RECORD
25 (
26 lookup_code ar_lookups.lookup_code%type
27 ) ;
28 TYPE Approval_Cache_Tbl_type IS
29 TABLE OF Lookup_Rec_Type
30 INDEX BY BINARY_INTEGER;
31 TYPE Adjtype_Cache_Tbl_type IS
32 TABLE OF Lookup_Rec_Type
33 INDEX BY BINARY_INTEGER;
34 TYPE Adjreason_Cache_Tbl_type IS
35 TABLE OF Lookup_Rec_Type
36 INDEX BY BINARY_INTEGER;
37
38 /*--------------------------------------------------------------------------+
39 | Accounting_affect_flag has been added for the BR/BOE project. |
40 | This flag would indicate that whether any accounting enteries need to |
41 | created or not. Also the code_combination_id will be set to null if the |
42 | accounting_affect_flag is set to 'N' |
43 +---------------------------------------------------------------------------*/
44 TYPE Rcvtrx_Rec_Type IS RECORD
45 (
46 receivables_trx_id ar_receivables_trx.RECEIVABLES_TRX_ID%type,
47 name ar_receivables_trx.NAME%type,
48 type ar_receivables_trx.TYPE%type,
49 code_combination_id ar_receivables_trx.CODE_COMBINATION_ID%type,
50 accounting_affect_flag ar_receivables_trx.ACCOUNTING_AFFECT_FLAG%type,
51 gl_account_source ar_receivables_trx.GL_ACCOUNT_SOURCE%type /*Bug 2925924*/
52 ) ;
53 TYPE Rcvtrx_Cache_Tbl_type IS
54 TABLE OF Rcvtrx_Rec_Type
55 INDEX BY BINARY_INTEGER;
56
57
58 TYPE Ussgl_Rec_Type IS RECORD
59 (
60 Ussgl_code gl_ussgl_transaction_codes.ussgl_transaction_code%type,
61 Ussgl_context gl_ussgl_transaction_codes.context%type
62 ) ;
63 TYPE Ussgl_Cache_Tbl_Type IS
64 TABLE OF Ussgl_Rec_Type
65 INDEX BY BINARY_INTEGER;
66
67
68 TYPE Glperiod_Rec_Type IS RECORD
69 (
70 start_date gl_period_statuses.start_date%type,
71 end_date gl_period_statuses.end_date%type
72 ) ;
73 TYPE Glperiod_Cache_Tbl_Type IS
74 TABLE OF GLperiod_Rec_Type
75 INDEX BY BINARY_INTEGER;
76
77
78 TYPE Ccid_Rec_Type IS RECORD
79 (
80 dummy varchar2(1)
81 );
82 TYPE CCid_Cache_Tbl_Type IS
83 TABLE OF Ccid_Rec_Type
84 INDEX BY BINARY_INTEGER;
85
86 G_APPROVAL_TBL Approval_Cache_Tbl_Type;
87 G_REASON_TBL Adjreason_Cache_Tbl_Type;
88 G_ADJTYPE_TBL Adjtype_Cache_Tbl_Type;
89 G_RCVTRX_TBL Rcvtrx_Cache_Tbl_type;
90 G_USSGL_TBL Ussgl_Cache_Tbl_Type;
91 G_GLPERIOD_TBL Glperiod_Cache_Tbl_Type;
92 G_CCID_TBL Ccid_Cache_Tbl_type;
93
94 G_CONTEXT_REC Context_Rec_Type;
95
96 G_CCID_CACHE_SIZE BINARY_INTEGER := 1000;
97 G_GLPERIOD_CACHE_SIZE BINARY_INTEGER := 1000;
98
99 /*
100 bug 3751203 : make l_lookup_csr global to the package, reuse it
101 by parameterizing the lookup_type affects : Cache_Approval_Type,
102 Cache_Adjustment_Type and Cache_Adjustment_Reason
103 */
104
105 CURSOR l_lookup_csr (l_lookup_type IN AR_LOOKUPS.LOOKUP_TYPE%TYPE) IS
106 SELECT lookup_code
107 FROM ar_lookups
108 WHERE lookup_type = l_lookup_type
109 AND enabled_flag = 'Y'
110 AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
111 trunc(sysdate))
112 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
113
114 /*===========================================================================+
115 | PROCEDURE |
116 | Init_Context_Rec |
117 | |
118 | DESCRIPTION |
119 | Initializes the context record that is passed into most of |
120 | the other functions. Many of its values are set when the |
121 | context variable is instantiated. |
122 | |
123 | SCOPE - PUBLIC |
124 | |
125 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
126 | |
127 | ARGUMENTS : IN: |
128 | p_validation_level |
129 | OUT: |
130 | p_return_status |
131 | IN/ OUT: |
132 | |
133 | |
134 | RETURNS : NONE |
135 | |
136 | NOTES |
137 | |
138 | MODIFICATION HISTORY |
139 | Vivek Halder 06-JUN-97 |
140 | |
141 +===========================================================================*/
142
143 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
144
145 PROCEDURE Init_Context_Rec(
146 p_validation_level IN VARCHAR2,
147 p_return_status IN OUT NOCOPY varchar2
148 ) IS
149
150
151 BEGIN
152 IF PG_DEBUG in ('Y', 'C') THEN
153 arp_util.debug('Init_Context_Rec()+' , G_MSG_HIGH);
154 END IF;
155
156
157 /*---------------------------+
158 | Set the validation level |
159 +----------------------------*/
160
161 g_context_rec.validation_level := p_validation_level;
162
163 /*-------------------------------------------------------+
164 | Set the profile options for USSGL, DOCUMENT SEQUENCES |
165 | and the OVERRIDE ACTIVITY option |
166 +-------------------------------------------------------*/
167
168 g_context_rec.ussgl_option :=
169 rtrim(FND_PROFILE.VALUE( 'USSGL_OPTION' ));
170
171 g_context_rec.unique_seq_numbers :=
172 rtrim(FND_PROFILE.VALUE('UNIQUE:SEQ_NUMBERS'));
173
174 g_context_rec.override_activity_option :=
175 rtrim(FND_PROFILE.VALUE( 'AR_OVERRIDE_ADJUSTMENT_ACTIVITY_ACCOUNT'));
176
177
178 p_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 IF PG_DEBUG in ('Y', 'C') THEN
181 arp_util.debug('Init_Context_Rec()-' , G_MSG_HIGH);
182 END IF;
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 IF PG_DEBUG in ('Y', 'C') THEN
187 arp_util.debug('EXCEPTION: Init_Context_Rec() ', G_MSG_UERROR);
188 END IF;
189
190 FND_MSG_PUB.Add_Exc_Msg (
191 G_PKG_NAME,
192 'Init_Context_Rec'
193 );
194
195 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
196 RETURN;
197
198 END Init_Context_Rec;
199
200 /*===========================================================================+
201 | PROCEDURE Cache_Gl_Periods |
202 | |
203 | DESCRIPTION This function is called during start_up to fetch the |
204 | opened, future-enterable in a pl/sql table. |
205 | |
206 | SCOPE - PRIVATE |
207 | |
208 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
209 | |
210 | ARGUMENTS : IN: |
211 | OUT: p_return_status |
212 | IN/ OUT: |
213 | |
214 | RETURNS : NONE |
215 | |
216 | NOTES |
217 | |
218 | MODIFICATION HISTORY |
219 | Vivek halder 10-JUL-97 Created |
220 | |
221 +===========================================================================*/
222
223 PROCEDURE Cache_Gl_Periods (p_return_status IN OUT NOCOPY VARCHAR2 )
224 IS
225
226 l_set_of_books_id ar_system_parameters.set_of_books_id%type;
227
228 CURSOR l_periods_csr IS
229 SELECT trunc(g.start_date) start_date,
230 trunc(g.end_date) end_date
231 FROM gl_period_statuses g,
232 gl_sets_of_books b
233 WHERE g.application_id = 222
234 AND g.set_of_books_id = l_set_of_books_id
235 AND g.set_of_books_id = b.set_of_books_id
236 AND g.period_type = b.accounted_period_type
237 AND g.adjustment_period_flag = 'N'
238 AND g.closing_status IN ('O','F') ;
239
240
241 l_index BINARY_INTEGER default 0;
242 l_temp_rec Glperiod_Rec_Type;
243
244 BEGIN
245
246 IF PG_DEBUG in ('Y', 'C') THEN
247 arp_util.debug('Cache_Gl_Periods()+' , G_MSG_HIGH);
248 END IF;
249
250 p_return_status := FND_API.G_RET_STS_SUCCESS;
251
252 BEGIN
253 SELECT set_of_books_id
254 INTO l_set_of_books_id
255 FROM ar_system_parameters ;
256 EXCEPTION
257 WHEN NO_DATA_FOUND THEN
258 IF PG_DEBUG in ('Y', 'C') THEN
259 arp_util.debug('Cache_Gl_Periods: ' || 'No Open/Future Enterable GL periods exist',G_MSG_HIGH);
260 END IF;
261 /*-----------------------------------------------+
262 | Set the message |
263 +-----------------------------------------------*/
264 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_OPEN_FUTURE_PERIOD');
265 FND_MESSAGE.SET_TOKEN ( 'SET_OF_BOOKS_ID', to_char(arp_global.set_of_books_id)) ;
266 FND_MSG_PUB.ADD ;
267
268 p_return_status := FND_API.G_RET_STS_ERROR;
269
270 WHEN OTHERS THEN
271 IF PG_DEBUG in ('Y', 'C') THEN
272 arp_util.debug('EXCEPTION: Cache_Gl_Periods()', G_MSG_UERROR);
273 END IF;
274 /*-----------------------------------------------+
275 | Set unexpected error message and status |
276 +-----------------------------------------------*/
277 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Gl_Periods');
278 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
279 END;
280
281 l_index := 0 ;
282 FOR l_temp_rec IN l_periods_csr LOOP
283 l_index := l_index + 1;
284 IF ( l_index > G_GLPERIOD_CACHE_SIZE )
285 THEN
286 EXIT ;
287 END IF;
288 G_GLPERIOD_TBL(l_index) := l_temp_rec;
289 END LOOP;
290
291 IF PG_DEBUG in ('Y', 'C') THEN
292 arp_util.debug ('Cache_Gl_Periods: ' || 'G_GLPERIOD_TBL count = '|| to_char(g_glperiod_tbl.count), G_MSG_HIGH);
293 arp_util.debug ('Cache_Gl_Periods()-' , G_MSG_HIGH);
294 END IF;
295
296 RETURN;
297
298 EXCEPTION
299 WHEN NO_DATA_FOUND THEN
300 IF PG_DEBUG in ('Y', 'C') THEN
301 arp_util.debug('Cache_Gl_Periods: ' || 'No Open/Future Enterable GL periods exist',G_MSG_HIGH);
302 END IF;
303 /*-----------------------------------------------+
304 | Set the message |
305 +-----------------------------------------------*/
306 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_OPEN_FUTURE_PERIOD');
307 FND_MESSAGE.SET_TOKEN ( 'SET_OF_BOOKS_ID', to_char(arp_global.set_of_books_id)) ;
308 FND_MSG_PUB.ADD ;
309
310 p_return_status := FND_API.G_RET_STS_ERROR;
311 WHEN OTHERS THEN
312 IF PG_DEBUG in ('Y', 'C') THEN
313 arp_util.debug('EXCEPTION: Cache_Gl_Periods()', G_MSG_UERROR);
314 END IF;
315 /*-----------------------------------------------+
316 | Set unexpected error message and status |
317 +-----------------------------------------------*/
318 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Gl_Periods');
319 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
320 RETURN;
321
322 END Cache_Gl_Periods;
323
324
325 /*===========================================================================+
326 | PROCEDURE Cache_Approval_Type |
327 | |
328 | DESCRIPTION This function is called during start_up to fetch the |
329 | approval codes in a pl/sql table. |
330 | |
331 | SCOPE - PRIVATE |
332 | |
333 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
334 | |
335 | ARGUMENTS : IN: |
336 | OUT: p_return_status |
337 | IN/ OUT: |
338 | |
339 | RETURNS : NONE |
340 | |
341 | NOTES |
342 | |
343 | MODIFICATION HISTORY |
344 | Vivek Halder 10-JUL-97 Created |
345 | |
346 +===========================================================================*/
347
348 PROCEDURE Cache_Approval_Type (p_return_status IN OUT NOCOPY VARCHAR2 )
349 IS
350
351 l_index BINARY_INTEGER default 0;
352 l_temp_rec Lookup_Rec_Type;
353
354 BEGIN
355
356 IF PG_DEBUG in ('Y', 'C') THEN
357 arp_util.debug('Cache_Approval_Type()+' , G_MSG_HIGH);
358 END IF;
359
360 p_return_status := FND_API.G_RET_STS_SUCCESS;
361
362 FOR l_temp_rec IN l_lookup_csr('APPROVAL_TYPE') LOOP
363 l_index := l_index + 1;
364 G_APPROVAL_TBL(l_index) := l_temp_rec;
365 END LOOP;
366
367 IF PG_DEBUG in ('Y', 'C') THEN
368 arp_util.debug ('Cache_Approval_Type: ' || 'G_APPROVAL_TBL count = '|| to_char(G_APPROVAL_TBL.count), G_MSG_HIGH);
369 arp_util.debug ('Cache_Approval_Type()-' , G_MSG_HIGH);
370 END IF;
371
372 RETURN;
373
374 EXCEPTION
375 WHEN NO_DATA_FOUND THEN
376 IF PG_DEBUG in ('Y', 'C') THEN
377 arp_util.debug('Cache_Approval_Type: ' || 'No Approval Codes exist ', G_MSG_HIGH);
378 END IF;
379 /*-----------------------------------------------+
380 | Set the message |
381 +-----------------------------------------------*/
382
383 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_APPROVAL_CODES');
384 FND_MSG_PUB.ADD ;
385
386 p_return_status := FND_API.G_RET_STS_ERROR ;
387
388 WHEN OTHERS THEN
389 IF PG_DEBUG in ('Y', 'C') THEN
390 arp_util.debug('EXCEPTION: Cache_Approval_Type()', G_MSG_UERROR);
391 END IF;
392 /*-----------------------------------------------+
393 | Set unexpected error message and status |
394 +-----------------------------------------------*/
395 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Approval_Type');
396 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
397 RETURN;
398
399 END Cache_Approval_Type;
400
401 /*==========================================================================+
402 | PROCEDURE Cache_Adjustment_Type |
403 | |
404 | DESCRIPTION This function is called during start_up to fetch the |
405 | types in a pl/sql table. |
406 | |
407 | SCOPE - PRIVATE |
408 | |
409 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
410 | |
411 | ARGUMENTS : IN: |
412 | OUT: p_return_status |
413 | IN/ OUT: |
414 | |
415 | RETURNS : NONE |
416 | |
417 | NOTES |
418 | |
419 | MODIFICATION HISTORY |
420 | Vivek Halder 11-JUL-97 Created |
421 | |
422 +===========================================================================*/
423
424 PROCEDURE Cache_Adjustment_Type (p_return_status IN OUT NOCOPY VARCHAR2 )
425 IS
426
427 l_index BINARY_INTEGER default 0;
428 l_temp_rec Lookup_Rec_Type;
429
430 BEGIN
431
432 IF PG_DEBUG in ('Y', 'C') THEN
433 arp_util.debug('Cache_Adjustment_Type()+' , G_MSG_HIGH);
434 END IF;
435
436 p_return_status := FND_API.G_RET_STS_SUCCESS;
437
438 FOR l_temp_rec IN l_lookup_csr('ADJUSTMENT_TYPE') LOOP
439 l_index := l_index + 1;
440 G_ADJTYPE_TBL(l_index) := l_temp_rec;
441 END LOOP;
442
443 IF PG_DEBUG in ('Y', 'C') THEN
444 arp_util.debug ('Cache_Adjustment_Type: ' || 'G_ADJTYPE_TBL count = '|| to_char(G_ADJTYPE_TBL.count), G_MSG_HIGH);
445 arp_util.debug ('Cache_Adjustment_Type()-' , G_MSG_HIGH);
446 END IF;
447
448 RETURN;
449
450 EXCEPTION
451 WHEN NO_DATA_FOUND THEN
452 IF PG_DEBUG in ('Y', 'C') THEN
453 arp_util.debug('Cache_Adjustment_Type: ' || 'No Adjustment Type codes', G_MSG_HIGH);
454 END IF;
455 /*-----------------------------------------------+
456 | Set the message |
457 +-----------------------------------------------*/
458 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_TYPE_CODES');
459 FND_MSG_PUB.ADD ;
460
461 p_return_status := FND_API.G_RET_STS_ERROR ;
462
463 WHEN OTHERS THEN
464 IF PG_DEBUG in ('Y', 'C') THEN
465 arp_util.debug('EXCEPTION: Cache_Adjustment_Type()', G_MSG_UERROR);
466 END IF;
467 /*-----------------------------------------------+
468 | Set unexpected error message and status |
469 +-----------------------------------------------*/
470 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Adjustment_Type');
471 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
472 RETURN;
473
474 END Cache_Adjustment_Type;
475
476 /*==========================================================================+
477 | PROCEDURE Cache_Adjustment_Reason |
478 | |
479 | DESCRIPTION This function is called during start_up to fetch the |
480 | adjustment reason codes in a pl/sql table. |
481 | |
482 | SCOPE - PRIVATE |
483 | |
484 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
485 | |
486 | ARGUMENTS : IN: |
487 | OUT: p_return_status |
488 | IN/ OUT: |
489 | |
490 | RETURNS : NONE |
491 | |
492 | NOTES |
493 | |
494 | MODIFICATION HISTORY |
495 | Vivek Halder 11-JUL-97 Created |
496 | |
497 +===========================================================================*/
498
499 PROCEDURE Cache_Adjustment_Reason (p_return_status IN OUT NOCOPY VARCHAR2 )
500 IS
501
502 l_index BINARY_INTEGER default 0;
503 l_temp_rec Lookup_Rec_Type;
504
505 BEGIN
506
507 IF PG_DEBUG in ('Y', 'C') THEN
508 arp_util.debug('Cache_Adjustment_Reason()+' , G_MSG_HIGH);
509 END IF;
510
511 p_return_status := FND_API.G_RET_STS_SUCCESS;
512
513 FOR l_temp_rec IN l_lookup_csr('ADJUST_REASON') LOOP
514 l_index := l_index + 1;
515 G_REASON_TBL(l_index) := l_temp_rec;
516 END LOOP;
517
518 IF PG_DEBUG in ('Y', 'C') THEN
519 arp_util.debug ('Cache_Adjustment_Reason: ' || 'G_REASON_TBL count = '|| to_char(G_REASON_TBL.count), G_MSG_HIGH);
520 arp_util.debug ('Cache_Adjustment_Reason()-' , G_MSG_HIGH);
521 END IF;
522
523 RETURN;
524
525 EXCEPTION
526 WHEN NO_DATA_FOUND THEN
527 IF PG_DEBUG in ('Y', 'C') THEN
528 arp_util.debug('Cache_Adjustment_Reason: ' || 'No Adjustment Reason codes', G_MSG_HIGH);
529 END IF;
530 /*-----------------------------------------------+
531 | Set the message |
532 +-----------------------------------------------*/
533 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_REASON_CODES');
534 FND_MSG_PUB.ADD ;
535
536 p_return_status := FND_API.G_RET_STS_ERROR ;
537
538 WHEN OTHERS THEN
539 IF PG_DEBUG in ('Y', 'C') THEN
540 arp_util.debug('EXCEPTION: Cache_Adjustment_Reason()', G_MSG_UERROR);
541 END IF;
542 /*-----------------------------------------------+
543 | Set unexpected error message and status |
544 +-----------------------------------------------*/
545 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Adjustment_Reason');
546 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
547 RETURN;
548
549 END Cache_Adjustment_Reason;
550
551
552 /*==========================================================================+
553 | PROCEDURE Cache_Receivables_Trx |
554 | |
555 | DESCRIPTION This function is called during start_up to fetch the |
556 | Receivables Trx codes in a pl/sql table. |
557 | |
558 | SCOPE - PRIVATE |
559 | |
560 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
561 | |
562 | ARGUMENTS : IN: |
563 | OUT: p_return_status |
564 | IN/ OUT: |
565 | |
566 | RETURNS : NONE |
567 | |
568 | NOTES |
569 | |
570 | MODIFICATION HISTORY |
571 | Vivek Halder 11-JUL-97 Created |
572 | Saloni Shah 03-FEB-00 Changes made for BOE/BR project. |
573 | Added a column (accounting_affect_flag) in |
574 | the select clause. |
575 | SNAMBIAR 31-May-00 Bug 1290698 . Included type ENDORSEMENT also |
576 | BOE/BR
577 | SNAMBIAR 31-Jan-01 Bug 1620930 . |
578 | SNAMBIAR 02-Apr-01 Modified the cursor to pickup receivables trx|
579 | id -12 which is used for deduction chargeback|
580 | reversal
581 | M Raymond 30-JUL-02 Bug 2441496 - Need to add FINCHRG to
582 | list of cached receivables trx.
583 +===========================================================================*/
584
585 PROCEDURE Cache_Receivables_Trx (p_return_status IN OUT NOCOPY VARCHAR2 )
586 IS
587
588 CURSOR l_receivables_csr IS
589 SELECT receivables_trx_id,name,type,code_combination_id ,accounting_affect_flag,
590 gl_account_source /*Bug 2925924*/
591 FROM ar_receivables_trx
592 WHERE nvl(status,'A') = 'A'
593 AND type in ('ADJUST','ENDORSEMENT','FINCHRG')
594 AND receivables_trx_id not in (-11,-13 )
595 AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
596 trunc(sysdate))
597 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
598
599
600 l_index BINARY_INTEGER default 0;
601 l_temp_rec Rcvtrx_Rec_Type;
602
603 BEGIN
604
605 IF PG_DEBUG in ('Y', 'C') THEN
606 arp_util.debug('Cache_Receivables_Trx()+' , G_MSG_HIGH);
607 END IF;
608
609 p_return_status := FND_API.G_RET_STS_SUCCESS;
610
611 FOR l_temp_rec IN l_receivables_csr LOOP
612 l_index := l_index + 1;
613 G_RCVTRX_TBL(l_index) := l_temp_rec;
614 END LOOP;
615
616 IF PG_DEBUG in ('Y', 'C') THEN
617 arp_util.debug ('Cache_Receivables_Trx: ' || 'G_RCVTRX_TBL count = '|| to_char(G_RCVTRX_TBL.count), G_MSG_HIGH);
618 arp_util.debug ('Cache_Receivables_Trx()-' , G_MSG_HIGH);
619 END IF;
620
621 RETURN;
622
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 IF PG_DEBUG in ('Y', 'C') THEN
626 arp_util.debug('Cache_Receivables_Trx: ' || 'No Adjustment Reason codes', G_MSG_HIGH);
627 END IF;
628 /*-----------------------------------------------+
629 | Set the message |
630 +-----------------------------------------------*/
631
632 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_RECEIVABLES_TRX');
633 FND_MSG_PUB.ADD ;
634
635 p_return_status := FND_API.G_RET_STS_ERROR ;
636
637 WHEN OTHERS THEN
638 IF PG_DEBUG in ('Y', 'C') THEN
639 arp_util.debug('EXCEPTION: Cache_Receivables_Trx()', G_MSG_UERROR);
640 END IF;
641 /*-----------------------------------------------+
642 | Set unexpected error message and status |
643 +-----------------------------------------------*/
644 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Receivables_Trx');
645 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
646 RETURN;
647
648 END Cache_Receivables_Trx;
649
650 /*===========================================================================+
651 | PROCEDURE Cache_Ussgl_code |
652 | |
653 | DESCRIPTION This function is called during start_up to fetch the |
654 | Ussgl codes in a pl/sql table. |
655 | |
656 | SCOPE - PRIVATE |
657 | |
658 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
659 | |
660 | ARGUMENTS : IN: |
661 | OUT: p_return_status |
662 | IN/ OUT: |
663 | |
664 | RETURNS : NONE |
665 | |
666 | NOTES |
667 | |
668 | MODIFICATION HISTORY |
669 | Vivek Halder 11-JUL-97 Created |
670 | |
671 +===========================================================================*/
672
673 PROCEDURE Cache_Ussgl_Code (p_return_status IN OUT NOCOPY VARCHAR2 )
674 IS
675
676 CURSOR l_ussgl_csr IS
677 SELECT ussgl_transaction_code,context
678 FROM gl_ussgl_transaction_codes
679 WHERE chart_of_accounts_id = arp_global.chart_of_accounts_id
680 AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
681 trunc(sysdate))
682 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
683
684
685 l_index BINARY_INTEGER default 0;
686 l_temp_rec Ussgl_Rec_Type;
687
688 BEGIN
689
690 IF PG_DEBUG in ('Y', 'C') THEN
691 arp_util.debug('Cache_Ussgl_Code()+' , G_MSG_HIGH);
692 END IF;
693
694 p_return_status := FND_API.G_RET_STS_SUCCESS;
695
696 /*-----------------------------------------------+
697 | Load the USSGL based on profile option |
698 +-----------------------------------------------*/
699
700 IF ( g_context_rec.ussgl_option <> 'Y' )
701 THEN
702 RETURN ;
703 END IF;
704
705 FOR l_temp_rec IN l_ussgl_csr LOOP
706 l_index := l_index + 1;
707 G_USSGL_TBL(l_index) := l_temp_rec;
708 END LOOP;
709
710 IF PG_DEBUG in ('Y', 'C') THEN
711 arp_util.debug ('Cache_Ussgl_Code: ' || 'G_USSGL_TBL count = '|| to_char(G_USSGL_TBL.count), G_MSG_HIGH);
712 arp_util.debug ('Cache_Ussgl_Code()-' , G_MSG_HIGH);
713 END IF;
714
715 RETURN;
716
717 EXCEPTION
718 WHEN NO_DATA_FOUND THEN
719 IF PG_DEBUG in ('Y', 'C') THEN
720 arp_util.debug('Cache_Ussgl_Code: ' || 'No USSGL codes', G_MSG_HIGH);
721 END IF;
722 /*-----------------------------------------------+
723 | Set the message |
724 +-----------------------------------------------*/
725
726 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_USSGL_CODES');
727 FND_MSG_PUB.ADD ;
728
729 p_return_status := FND_API.G_RET_STS_SUCCESS ;
730
731 WHEN OTHERS THEN
732 IF PG_DEBUG in ('Y', 'C') THEN
733 arp_util.debug('EXCEPTION: Cache_Ussgl_Code()', G_MSG_UERROR);
734 END IF;
735 /*-----------------------------------------------+
736 | Set unexpected error message and status |
737 +-----------------------------------------------*/
738 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Ussgl_Code');
739 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
740 RETURN;
741
742 END Cache_Ussgl_code;
743
744 /*===========================================================================+
745 | PROCEDURE Cache_Code_Combination |
746 | |
747 | DESCRIPTION This function is called during start_up to fetch the |
748 | Code Combination Ids in a pl/sql table. |
749 | |
750 | SCOPE - PRIVATE |
751 | |
752 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
753 | |
754 | ARGUMENTS : IN: |
755 | OUT: p_return_status |
756 | IN/ OUT: |
757 | |
758 | RETURNS : NONE |
759 | |
760 | NOTES |
761 | |
762 | MODIFICATION HISTORY |
763 | Vivek Halder 11-JUL-97 Created |
764 | |
765 +===========================================================================*/
766
767 PROCEDURE Cache_Code_Combination (p_return_status IN OUT NOCOPY VARCHAR2 )
768 IS
769
770 CURSOR l_ccid_csr IS
771 SELECT code_combination_id
772 FROM gl_code_combinations
773 WHERE chart_of_accounts_id = arp_global.chart_of_accounts_id
774 AND enabled_flag = 'Y'
775 AND trunc(sysdate) BETWEEN nvl(trunc(start_date_active),
776 trunc(sysdate))
777 AND nvl(trunc(end_date_active),trunc(sysdate)) ;
778
779
780 l_index BINARY_INTEGER default 0;
781
782 TYPE ccid_rec IS RECORD
783 (
784 code_combination_id gl_code_combinations.code_combination_id%type
785 ) ;
786
787 l_ccid_rec ccid_rec;
788
789 BEGIN
790
791 IF PG_DEBUG in ('Y', 'C') THEN
792 arp_util.debug('Cache_Code_Combination()+' , G_MSG_HIGH);
793 END IF;
794
795 p_return_status := FND_API.G_RET_STS_SUCCESS;
796
797 FOR l_ccid_rec IN l_ccid_csr LOOP
798 l_index := l_index + 1 ;
799 IF ( l_index > G_CCID_CACHE_SIZE )
800 THEN
801 EXIT;
802 END IF;
803 G_CCID_TBL(l_ccid_rec.code_combination_id).dummy := FND_API.G_TRUE;
804 END LOOP;
805
806 IF PG_DEBUG in ('Y', 'C') THEN
807 arp_util.debug ('Cache_Code_Combination: ' || 'G_CCID_TBL count = '|| to_char(G_CCID_TBL.count), G_MSG_HIGH);
808 arp_util.debug ('Cache_Code_Combination()-' , G_MSG_HIGH);
809 END IF;
810
811 RETURN;
812
813 EXCEPTION
814 WHEN NO_DATA_FOUND THEN
815 IF PG_DEBUG in ('Y', 'C') THEN
816 arp_util.debug('Cache_Code_Combination: ' || 'No USSGL codes', G_MSG_HIGH);
817 END IF;
818 /*-----------------------------------------------+
819 | Set the message |
820 +-----------------------------------------------*/
821
822 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_CCID');
823 FND_MSG_PUB.ADD ;
824
825 p_return_status := FND_API.G_RET_STS_ERROR ;
826
827 WHEN OTHERS THEN
828 IF PG_DEBUG in ('Y', 'C') THEN
829 arp_util.debug('EXCEPTION: Cache_Code_Combination()', G_MSG_UERROR);
830 END IF;
831 /*-----------------------------------------------+
832 | Set unexpected error message and status |
833 +-----------------------------------------------*/
834 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Code_Combination');
835 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
836 RETURN;
837
838 END Cache_Code_Combination;
839
840
841
842 /*===========================================================================+
843 | PROCEDURE |
844 | Cache_Details |
845 | |
846 | DESCRIPTION |
847 | Caches data when it is first used so that values can easily |
848 | be accessed later and need not be fetched from the database |
849 | for future transactions. |
850 | |
851 | The following tables are cached |
852 | - ar_lookups for type = APPROVAL_TYPE |
853 | - ar_lookups for type = ADJUSTMENT_TYPE |
854 | - ar_lookups for type = ADJUSTMENT_REASON |
855 | - ussgl transaction codes |
856 | - receivables trx for type = 'ADJUST' |
857 | - code combination ids |
858 | - gl periods |
859 | |
860 | SCOPE - PUBLIC |
861 | |
862 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
863 | cache_approval_type(); |
864 | cache_adjustment_type(); |
865 | cache_adjustment_reason(); |
866 | cache_ussgl_code(); |
867 | cache_receivables_trx(); |
868 | cache_gl_periods(); |
869 | cache_code_combination(); |
870 | |
871 | ARGUMENTS : IN: |
872 | OUT: |
873 | IN/ OUT: p_return_status |
874 | |
875 | RETURNS : NONE |
876 | |
877 | NOTES |
878 | |
879 | MODIFICATION HISTORY |
880 | Vivek Halder 05-JUL-97 Created |
881 | |
882 +===========================================================================*/
883
884 PROCEDURE Cache_Details (
885 p_return_status IN OUT NOCOPY varchar2
886 ) IS
887
888 l_tobe_cached_flag varchar2(1) ;
889 l_return_status varchar2(1);
890 l_current_org_id ar_receivables_trx.org_id%TYPE;
891
892 BEGIN
893
894 IF PG_DEBUG in ('Y', 'C') THEN
895 arp_util.debug('Cache_Details()+' , G_MSG_HIGH);
896 END IF;
897
898 /*--------------------------------------------------+
899 | Check if caching is needed. Caching is needed if|
900 | g_caching_done is FALSE or sysdate > cache_date |
901 +--------------------------------------------------*/
902
903 l_tobe_cached_flag := FND_API.G_FALSE ;
904
905 IF PG_DEBUG in ('Y', 'C') THEN
906 arp_util.debug ('Cache_Details: ' || 'G_Caching done = ' || G_caching_done, G_MSG_HIGH);
907 arp_util.debug ('Cache_Details: ' || 'G_Cache_date = ' || to_char(G_cache_date,'DD-MON-YY'),
908 G_MSG_HIGH);
909 arp_util.debug ('Cache_Details: ' || 'Sysdate = ' || to_char(sysdate,'DD-MON-YY'),G_MSG_HIGH);
910 END IF;
911
912 IF ( G_caching_done = FND_API.G_FALSE )
913 THEN
914 l_tobe_cached_flag := FND_API.G_TRUE ;
915 G_cache_date := trunc(sysdate) ;
916 ELSE
917 IF ( G_cache_date < trunc(sysdate) )
918 THEN
919 l_tobe_cached_flag := FND_API.G_TRUE ;
920 G_cache_date := trunc(sysdate) ;
921 END IF;
922 END IF;
923
924 -- bug 2822474 : this line does not seem necessary
925 -- Bug 4038942 : re-cache the data only if org_id has changed
926 l_current_org_id := fnd_global.org_id;
927 IF NVL(l_current_org_id,-88888) <> NVL(G_cache_org_id,-88888) THEN
928 G_cache_org_id := l_current_org_id;
929 l_tobe_cached_flag := FND_API.G_TRUE;
930 END IF;
931
932 IF PG_DEBUG in ('Y', 'C') THEN
933 arp_util.debug ('Cache_Details: ' || 'Caching Flag : '|| l_tobe_cached_flag,G_MSG_HIGH);
934 END IF;
935
936 IF ( l_tobe_cached_flag = FND_API.G_FALSE )
937 THEN
938 p_return_status := FND_API.G_RET_STS_SUCCESS;
939 RETURN ;
940 ELSE
941 /*-------------------------------------------------+
942 | Initialise the PL/SQL cache tables |
943 +-------------------------------------------------*/
944 G_APPROVAL_TBL.DELETE;
945 G_REASON_TBL.DELETE;
946 G_ADJTYPE_TBL.DELETE;
947 G_RCVTRX_TBL.DELETE;
948 G_USSGL_TBL.DELETE;
949 G_GLPERIOD_TBL.DELETE;
950 G_CCID_TBL.DELETE;
951 END IF;
952
953 /*-------------------------------------------------+
954 | Initialize return status to SUCCESS |
955 +-------------------------------------------------*/
956 p_return_status := FND_API.G_RET_STS_SUCCESS;
957
958
959 /*-------------------------------------------------+
960 | Cache Approval type. To be used for validation |
961 | of status |
962 +-------------------------------------------------*/
963
964 AR_ADJVALIDATE_PVT.cache_approval_type (l_return_status);
965
966 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
967 THEN
968 p_return_status := l_return_status;
969 IF PG_DEBUG in ('Y', 'C') THEN
970 arp_util.debug('Cache_Details: ' || ' failed to cache approval_type');
971 END IF;
972 END IF;
973 /*-------------------------------------------------+
974 | Cache reason codes for adjustment. To be used |
975 | for validation of adjustment reason codes |
976 +-------------------------------------------------*/
977
978 AR_ADJVALIDATE_PVT.cache_adjustment_reason (l_return_status);
979 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
980 THEN
981 p_return_status := l_return_status;
982 IF PG_DEBUG in ('Y', 'C') THEN
983 arp_util.debug ('Cache_Details: ' || ' failed to cache adjustment_reason');
984 END IF;
985 END IF;
986
987 /*-------------------------------------------------+
988 | Cache adjustment types i.e. INVOICE, LINE etc. |
989 | To be used for validation of type |
990 +-------------------------------------------------*/
991
992 AR_ADJVALIDATE_PVT.cache_adjustment_type (l_return_status);
993 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
994 THEN
995 p_return_status := l_return_status;
996 IF PG_DEBUG in ('Y', 'C') THEN
997 arp_util.debug ('Cache_Details: ' || ' failed to cache adjustment_type');
998 END IF;
999 END IF;
1000
1001 /*-------------------------------------------------+
1002 | Cache Receivables transaction ids. To be used |
1003 | for validation of Receivables trx id |
1004 +-------------------------------------------------*/
1005
1006 AR_ADJVALIDATE_PVT.cache_receivables_trx (l_return_status) ;
1007
1008 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1009 THEN
1010 p_return_status := l_return_status;
1011 IF PG_DEBUG in ('Y', 'C') THEN
1012 arp_util.debug ('Cache_Details: ' || ' failed to cache receivables_trx');
1013 END IF;
1014 END IF;
1015
1016 /*-------------------------------------------------+
1017 | Cache USSGL transaction information. To be used|
1018 | for validation of USSGL transaction code |
1019 +-------------------------------------------------*/
1020
1021 AR_ADJVALIDATE_PVT.cache_ussgl_code (l_return_status);
1022 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1023 THEN
1024 p_return_status := l_return_status;
1025 IF PG_DEBUG in ('Y', 'C') THEN
1026 arp_util.debug ('Cache_Details: ' || ' failed to cache ussgl_code');
1027 END IF;
1028 END IF;
1029
1030 /*--------------------------------------------------+
1031 | Cache GL periods. To be used to validate if GL |
1032 | dates lie within open or future enterable period|
1033 +--------------------------------------------------*/
1034
1035 AR_ADJVALIDATE_PVT.cache_gl_periods(l_return_status);
1036 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1037 THEN
1038 p_return_status := l_return_status;
1039 IF PG_DEBUG in ('Y', 'C') THEN
1040 arp_util.debug ('Cache_Details: ' || ' failed to cache gl_periods');
1041 END IF;
1042 END IF;
1043
1044 /*--------------------------------------------------+
1045 | Cache Code combination Ids. To be used to |
1046 | validate input provided by user |
1047 +--------------------------------------------------*/
1048
1049 AR_ADJVALIDATE_PVT.cache_code_combination (l_return_status);
1050 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
1051 THEN
1052 p_return_status := l_return_status;
1053 IF PG_DEBUG in ('Y', 'C') THEN
1054 arp_util.debug ('Cache_Details: ' || ' failed to cache code_combination');
1055 END IF;
1056 END IF;
1057
1058 G_caching_done := FND_API.G_TRUE ;
1059
1060 IF PG_DEBUG in ('Y', 'C') THEN
1061 arp_util.debug('Cache_Details ()-' , G_MSG_HIGH);
1062 END IF;
1063
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 IF PG_DEBUG in ('Y', 'C') THEN
1067 arp_util.debug('EXCEPTION: Cache_Details() ', G_MSG_UERROR);
1068 END IF;
1069
1070 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Cache_Details');
1071 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1072 RETURN;
1073
1074 END Cache_Details;
1075
1076 /*===========================================================================+
1077 | PROCEDURE |
1078 | Within_approval_limits |
1079 | |
1080 | DESCRIPTION |
1081 | This routine checks if the amount is within the approval |
1082 | limits of the user |
1083 | |
1084 | |
1085 | SCOPE - PRIVATE |
1086 | |
1087 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1088 | arp_util.disable_debug |
1089 | arp_util.enable_debug |
1090 | fnd_api.g_exc_unexpected_error |
1091 | fnd_api.g_ret_sts_error |
1092 | fnd_api.g_ret_sts_error |
1093 | fnd_api.g_ret_sts_success |
1094 | fnd_api.to_boolean |
1095 | |
1096 | ARGUMENTS : IN: |
1097 | p_adj_rec |
1098 | p_inv_curr_code |
1099 | OUT: |
1100 | p_return_status |
1101 | |
1102 | IN/ OUT: |
1103 | p_approved_flag |
1104 | |
1105 | RETURNS : NONE |
1106 | |
1107 | NOTES |
1108 | |
1109 | MODIFICATION HISTORY |
1110 | Vivek Halder 13-JUN-97 |
1111 | |
1112 +===========================================================================*/
1113
1114 PROCEDURE Within_approval_limits(
1115 p_adj_amount IN ar_adjustments.amount%type,
1116 p_inv_curr_code IN ar_payment_schedules.invoice_currency_code%type,
1117 p_approved_flag IN OUT NOCOPY varchar2,
1118 p_return_status IN OUT NOCOPY varchar2
1119 ) IS
1120
1121 l_user_id ar_approval_user_limits.user_id%type;
1122 l_approval_amount_to ar_approval_user_limits.amount_to%type;
1123 l_approval_amount_from ar_approval_user_limits.amount_from%type;
1124
1125 BEGIN
1126
1127 IF PG_DEBUG in ('Y', 'C') THEN
1128 arp_util.debug('Within_approval_limits()+', G_MSG_MEDIUM);
1129 END IF;
1130
1131 /*------------------------------------------+
1132 | Initialize the return status to SUCCESS |
1133 +------------------------------------------*/
1134
1135 p_return_status := FND_API.G_RET_STS_SUCCESS;
1136
1137 p_approved_flag := FND_API.G_TRUE ;
1138
1139 /*------------------------------------------+
1140 | Get the user Id |
1141 +------------------------------------------*/
1142
1143 l_user_id := FND_GLOBAL.USER_ID ;
1144
1145 BEGIN
1146 SELECT amount_to,
1147 amount_from
1148 INTO l_approval_amount_to,
1149 l_approval_amount_from
1150 FROM ar_approval_user_limits
1151 WHERE user_id = l_user_id
1152 AND currency_code = p_inv_curr_code
1153 AND document_type = 'ADJ';
1154
1155 EXCEPTION
1156 WHEN NO_DATA_FOUND THEN
1157
1158 IF PG_DEBUG in ('Y', 'C') THEN
1159 arp_util.debug ('Within_approval_limits: ' || 'User Id : ' || l_user_id);
1160 arp_util.debug ('Within_approval_limits: ' ||
1161 'User does not have approval limits for currency ' ||
1162 p_inv_curr_code, G_MSG_HIGH
1163 );
1164 END IF;
1165
1166 FND_MESSAGE.SET_NAME ('AR', 'AR_VAL_USER_LIMIT');
1167 FND_MESSAGE.SET_TOKEN ( 'CURRENCY', p_inv_curr_code ) ;
1168 FND_MSG_PUB.ADD ;
1169 p_approved_flag := FND_API.G_FALSE;
1170
1171 WHEN OTHERS THEN
1172
1173 IF PG_DEBUG in ('Y', 'C') THEN
1174 arp_util.debug ('EXCEPTION: Within_approval_limits',G_MSG_UERROR);
1175 END IF;
1176
1177 /*-------------------------------------------------+
1178 | Set unexpected error message, status and return |
1179 +-------------------------------------------------*/
1180 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Within_approval_limits');
1181 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1182 END;
1183
1184 /*-----------------------------------------------+
1185 | Ensure that approval data has been selected |
1186 +-----------------------------------------------*/
1187
1188 IF ( p_approved_flag = FND_API.G_TRUE )
1189 THEN
1190
1191 /*--------------------------------------------+
1192 | Perform actual check of approval limits. |
1193 +--------------------------------------------*/
1194
1195 IF (( p_adj_amount > l_approval_amount_to ) OR
1196 ( p_adj_amount < l_approval_amount_from ))
1197 THEN
1198 IF PG_DEBUG in ('Y', 'C') THEN
1199 arp_util.debug('Within_approval_limits: ' || 'User ID: ' || l_user_id ||
1200 ' Amount: ' || p_adj_amount ||
1201 ' From: ' || l_approval_amount_from ||
1202 ' To: ' || l_approval_amount_to ||
1203 ' exceeds approval limit', G_MSG_HIGH );
1204 END IF;
1205 /*--------------------------------------+
1206 | Add a message. But do not signal error|
1207 +--------------------------------------*/
1208
1209 FND_MESSAGE.SET_NAME ('AR', 'AR_VAL_AMT_APPROVAL_LIMIT');
1210 FND_MSG_PUB.ADD ;
1211
1212 p_approved_flag := FND_API.G_FALSE;
1213
1214 END IF;
1215
1216 END IF;
1217
1218 IF PG_DEBUG in ('Y', 'C') THEN
1219 arp_util.debug('Within_approval_limits()-', G_MSG_HIGH);
1220 END IF;
1221 RETURN ;
1222
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225
1226 IF PG_DEBUG in ('Y', 'C') THEN
1227 arp_util.debug('EXCEPTION: Within_approval_limits()', G_MSG_UERROR);
1228 END IF;
1229 /*-----------------------------------------------+
1230 | Set unexpected error message and status |
1231 +-----------------------------------------------*/
1232 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Within_approval_limits'
1233 );
1234 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1235 RETURN;
1236
1237 END Within_approval_limits;
1238
1239 /*===========================================================================+
1240 | PROCEDURE |
1241 | validate_buckets |
1242 | |
1243 | DESCRIPTION |
1244 | This routine checks if the various buckets of an adjustment |
1245 | are correct incase they have been specified while creating |
1246 | an INVOICE type adjustment. |
1247 | |
1248 | SCOPE - PRIVATE |
1249 | |
1250 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1251 | fnd_api.g_exc_unexpected_error |
1252 | fnd_api.g_ret_sts_error |
1253 | fnd_api.g_ret_sts_success |
1254 | |
1255 | ARGUMENTS : IN: |
1256 | OUT: |
1257 | |
1258 | IN/ OUT: |
1259 | |
1260 | RETURNS : NONE |
1261 | |
1262 | NOTES |
1263 | |
1264 | MODIFICATION HISTORY |
1265 | JASSING 01-MAY-2005 Bug 4258945 |
1266 | |
1267 +===========================================================================*/
1268 PROCEDURE Validate_buckets(
1269 p_adj_rec IN ar_adjustments%rowtype,
1270 p_ps_rec IN ar_payment_schedules%rowtype,
1271 p_return_status IN OUT NOCOPY varchar2
1272 ) IS
1273
1274 BEGIN
1275
1276 IF PG_DEBUG in ('Y', 'C') THEN
1277 arp_util.debug('Validate buckets()+', G_MSG_MEDIUM);
1278 END IF;
1279
1280 /*------------------------------------------+
1281 | Initialize the return status to SUCCESS |
1282 +------------------------------------------*/
1283
1284 p_return_status := FND_API.G_RET_STS_SUCCESS;
1285
1286 /*Verify the data in other buckets if they have been entered by the user.
1287 If data is entered for any of the bucket while invoking the API, then
1288 it must be validated for all the buckets.
1289 */
1290
1291 IF (p_adj_rec.line_adjusted IS NOT NULL
1292 OR p_adj_rec.tax_adjusted IS NOT NULL
1293 OR p_adj_rec.freight_adjusted IS NOT NULL
1294 OR p_adj_rec.receivables_charges_adjusted IS NOT NULL)
1295 THEN
1296 /*Check line bucket*/
1297
1298 IF (p_adj_rec.line_adjusted is NULL
1299 AND p_ps_rec.amount_line_items_remaining <> 0)
1300 THEN
1301 FND_MESSAGE.SET_NAME('AR','AR_TW_VAL_AMT_INC_BUCKET');
1302 FND_MESSAGE.SET_TOKEN('BUCKET','LINE');
1303 FND_MSG_PUB.ADD;
1304
1305 p_return_status := FND_API.G_RET_STS_ERROR;
1306 ELSIF (p_adj_rec.line_adjusted + p_ps_rec.amount_line_items_remaining <> 0)
1307 THEN
1308 FND_MESSAGE.SET_NAME('AR','AR_TW_VAL_AMT_ADJ_BUCKETS');
1309 FND_MESSAGE.SET_TOKEN('BUCKET','LINE');
1310 FND_MSG_PUB.ADD;
1311
1312 p_return_status := FND_API.G_RET_STS_ERROR;
1313 END IF;
1314
1315 /*Check tax bucket*/
1316
1317 IF (p_adj_rec.tax_adjusted is NULL
1318 AND p_ps_rec.tax_remaining <> 0)
1319 THEN
1320 FND_MESSAGE.SET_NAME('AR','AR_TW_VAL_AMT_INC_BUCKET');
1321 FND_MESSAGE.SET_TOKEN('BUCKET','TAX');
1322 FND_MSG_PUB.ADD;
1323
1324 p_return_status := FND_API.G_RET_STS_ERROR;
1325 ELSIF (p_adj_rec.tax_adjusted + p_ps_rec.tax_remaining <> 0)
1326 THEN
1327 FND_MESSAGE.SET_NAME('AR','AR_TW_VAL_AMT_ADJ_BUCKETS');
1328 FND_MESSAGE.SET_TOKEN('BUCKET','TAX');
1329 FND_MSG_PUB.ADD;
1330
1331 p_return_status := FND_API.G_RET_STS_ERROR;
1332 END IF;
1333
1334 /*Check freight bucket*/
1335
1336 IF (p_adj_rec.freight_adjusted is NULL
1337 AND p_ps_rec.freight_remaining <> 0)
1338 THEN
1339 FND_MESSAGE.SET_NAME('AR','AR_TW_VAL_AMT_INC_BUCKET');
1340 FND_MESSAGE.SET_TOKEN('BUCKET','FREIGHT');
1341 FND_MSG_PUB.ADD;
1342
1343 p_return_status := FND_API.G_RET_STS_ERROR;
1344 ELSIF (p_adj_rec.freight_adjusted + p_ps_rec.freight_remaining <> 0)
1345 THEN
1346 FND_MESSAGE.SET_NAME('AR','AR_TW_VAL_AMT_ADJ_BUCKETS');
1347 FND_MESSAGE.SET_TOKEN('BUCKET','FREIGHT');
1348 FND_MSG_PUB.ADD;
1349
1350 p_return_status := FND_API.G_RET_STS_ERROR;
1351 END IF;
1352
1353 /*Check charges bucket*/
1354
1355 IF (p_adj_rec.receivables_charges_adjusted is NULL
1356 AND p_ps_rec.receivables_charges_remaining <> 0)
1357 THEN
1358 FND_MESSAGE.SET_NAME('AR','AR_TW_VAL_AMT_INC_BUCKET');
1359 FND_MESSAGE.SET_TOKEN('BUCKET','CHARGES');
1360 FND_MSG_PUB.ADD;
1361
1362 p_return_status := FND_API.G_RET_STS_ERROR;
1363 ELSIF (p_adj_rec.receivables_charges_adjusted + p_ps_rec.receivables_charges_remaining <> 0)
1364 THEN
1365 FND_MESSAGE.SET_NAME('AR','AR_TW_VAL_AMT_ADJ_BUCKETS');
1366 FND_MESSAGE.SET_TOKEN('BUCKET','CHARGES');
1367 FND_MSG_PUB.ADD;
1368
1369 p_return_status := FND_API.G_RET_STS_ERROR;
1370 END IF;
1371 END IF;
1372
1373 IF PG_DEBUG in ('Y', 'C') THEN
1374 arp_util.debug('Validate buckets()-', G_MSG_MEDIUM);
1375 END IF;
1376
1377 RETURN;
1378
1379 EXCEPTION
1380 WHEN OTHERS THEN
1381
1382 IF PG_DEBUG in ('Y', 'C') THEN
1383 arp_util.debug('EXCEPTION: Validate_buckets()', G_MSG_UERROR);
1384 END IF;
1385 /*-----------------------------------------------+
1386 | Set unexpected error message and status |
1387 +-----------------------------------------------*/
1388 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_buckets'
1389 );
1390 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1391 RETURN;
1392
1393 END Validate_buckets;
1394
1395
1396 /*===========================================================================+
1397 | PROCEDURE |
1398 | Validate_Type |
1399 | |
1400 | DESCRIPTION |
1401 | This routine validates the type of adjustment |
1402 | |
1403 | |
1404 | |
1405 | SCOPE - PUBLIC |
1406 | |
1407 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1408 | arp_util.disable_debug |
1409 | arp_util.enable_debug |
1410 | fnd_api.g_exc_unexpected_error |
1411 | fnd_api.g_ret_sts_error |
1412 | fnd_api.g_ret_sts_error |
1413 | fnd_api.g_ret_sts_success |
1414 | fnd_api.to_boolean |
1415 | |
1416 | ARGUMENTS : IN: |
1417 | p_adj_rec |
1418 | |
1419 | OUT: |
1420 | |
1421 | IN/ OUT: |
1422 | p_return_status |
1423 | |
1424 | RETURNS : NONE |
1425 | |
1426 | NOTES |
1427 | |
1428 | MODIFICATION HISTORY |
1429 | Vivek Halder 13-JUN-97 |
1430 | |
1431 +===========================================================================*/
1432
1433 PROCEDURE Validate_Type (
1434 p_adj_rec IN ar_adjustments%rowtype,
1435 p_return_status IN OUT NOCOPY varchar2
1436 ) IS
1437
1438 l_index number;
1439 BEGIN
1440
1441 IF PG_DEBUG in ('Y', 'C') THEN
1442 arp_util.debug('Validate_Type()+', G_MSG_HIGH);
1443 END IF;
1444
1445 /*------------------------------------------+
1446 | Initialize the return status to ERROR |
1447 +------------------------------------------*/
1448
1449 p_return_status := FND_API.G_RET_STS_ERROR;
1450
1451 FOR l_index IN 1..G_ADJTYPE_TBL.COUNT LOOP
1452
1453 IF (p_adj_rec.type = G_ADJTYPE_TBL(l_index).lookup_code)
1454 THEN
1455 p_return_status := FND_API.G_RET_STS_SUCCESS;
1456 EXIT ;
1457 END IF;
1458
1459 END LOOP;
1460
1461 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
1462 THEN
1463 /*-----------------------------------------------+
1464 | Set the message |
1465 +-----------------------------------------------*/
1466
1467 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_ADJUSTMENT_TYPE');
1468 FND_MESSAGE.SET_TOKEN ( 'TYPE', p_adj_rec.type ) ;
1469 FND_MSG_PUB.ADD ;
1470
1471 END IF;
1472
1473 IF PG_DEBUG in ('Y', 'C') THEN
1474 arp_util.debug('Validate_Type()-', G_MSG_MEDIUM);
1475 END IF;
1476
1477
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480
1481 IF PG_DEBUG in ('Y', 'C') THEN
1482 arp_util.debug('EXCEPTION: Validate_Type()', G_MSG_UERROR );
1483 arp_util.debug('Validate_Type for type = ' ||p_adj_rec.type,G_MSG_HIGH);
1484 END IF;
1485
1486 /*-----------------------------------------------+
1487 | Set unexpected error message and status |
1488 +-----------------------------------------------*/
1489 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Type' );
1490
1491 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1492
1493 RETURN;
1494
1495 END Validate_Type;
1496
1497 /*===========================================================================+
1498 | PROCEDURE |
1499 | Validate_Payschd |
1500 | |
1501 | DESCRIPTION |
1502 | This routine validates the payment schedule id of the |
1503 | transaction for which the adjustment is to be created |
1504 | In case it is valid it populates the customer_trx_id |
1505 | and customer id in the adjustment record. |
1506 | It also validates the customer trx line Id if type = 'LINE' |
1507 | |
1508 | SCOPE - PUBLIC |
1509 | |
1510 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1511 | fnd_api.g_exc_unexpected_error |
1512 | fnd_api.g_ret_sts_error |
1513 | fnd_api.g_ret_sts_success |
1514 | |
1515 | ARGUMENTS : IN: |
1516 | |
1517 | OUT: |
1518 | p_return_status |
1519 | |
1520 | IN/ OUT: |
1521 | p_adj_rec |
1522 | p_ps_rec |
1523 | |
1524 | |
1525 | RETURNS : NONE |
1526 | |
1527 | NOTES |
1528 | |
1529 | MODIFICATION HISTORY |
1530 | Vivek Halder 30-JUNE-97 Created |
1531 | Satheesh Nambir 01-Jun-00 Bug 1290698. Added one more class 'BR' for |
1532 | BOE/BR Project
1533 | Satheesh Nambiar 25-Aug-00 Modified the code to process $0 adjustment |
1534 | and process line without customer_trx_line_id
1535 | Bug 1395396 |
1536 +==========================================================================*/
1537
1538 PROCEDURE Validate_Payschd (
1539 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
1540 p_ps_rec IN OUT NOCOPY ar_payment_schedules%rowtype,
1541 p_return_status OUT NOCOPY Varchar2,
1542 p_from_llca_call IN varchar2 DEFAULT 'N'
1543 ) IS
1544
1545 l_index BINARY_INTEGER ;
1546 l_count number:= 0 ;
1547
1548 -- Line Level Adjustment
1549 l_customer_trx_id number;
1550 l_customer_trx_line_id number;
1551 l_receivables_trx_id number;
1552
1553 BEGIN
1554
1555 IF PG_DEBUG in ('Y', 'C') THEN
1556 arp_util.debug('Validate_Payschd()+', G_MSG_MEDIUM);
1557 arp_util.debug('p_from_llca_call :'|| p_from_llca_call);
1558 END IF;
1559
1560 /*------------------------------------------+
1561 | Initialize the return status to SUCCESS |
1562 +------------------------------------------*/
1563
1564 p_return_status := FND_API.G_RET_STS_SUCCESS;
1565
1566 -- Initialize Line Level Adjustment
1567 l_customer_trx_id := p_adj_rec.customer_trx_id;
1568 l_customer_trx_line_id := p_adj_rec.customer_trx_line_id;
1569 l_receivables_trx_id := p_adj_rec.receivables_trx_id;
1570
1571 /*-----------------------------------------------+
1572 | Check if the payment schedule Id is 0 or null |
1573 | If so return with failure |
1574 +-----------------------------------------------*/
1575
1576 IF ( p_adj_rec.payment_schedule_id IS NULL or
1577 p_adj_rec.payment_schedule_id <= 0 )
1578 THEN
1579
1580 /*-----------------------------------------------+
1581 | Set the message and return |
1582 +-----------------------------------------------*/
1583
1584 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_PAYSCHD');
1585 FND_MESSAGE.SET_TOKEN ( 'PAYMENT_SCHEDULE_ID', to_char(p_adj_rec.payment_schedule_id)) ;
1586 FND_MSG_PUB.ADD ;
1587
1588 p_return_status := FND_API.G_RET_STS_ERROR;
1589
1590 IF PG_DEBUG in ('Y', 'C') THEN
1591 arp_util.debug('Validate_Payschd: ' || 'payment schedule id is invalid');
1592 END IF;
1593
1594 END IF ;
1595
1596 /*--------------------------------------------------+
1597 | Check if the payment schedule Id exists. Get the |
1598 | Customer Id and Customer Trx Id |
1599 +--------------------------------------------------*/
1600
1601 BEGIN
1602
1603 SELECT *
1604 INTO p_ps_rec
1605 FROM ar_payment_schedules
1606 WHERE payment_schedule_id = p_adj_rec.payment_schedule_id;
1607
1608 EXCEPTION
1609 WHEN NO_DATA_FOUND THEN
1610
1611 /*-----------------------------------------------+
1612 | Payment schedule Id does not exist |
1613 | Set the message and status accordingly |
1614 +-----------------------------------------------*/
1615
1616 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_PAYSCHD');
1617 FND_MESSAGE.SET_TOKEN('PAYMENT_SCHEDULE_ID',to_char(p_adj_rec.payment_schedule_id)) ;
1618 FND_MSG_PUB.ADD ;
1619 p_return_status := FND_API.G_RET_STS_ERROR;
1620 IF PG_DEBUG in ('Y', 'C') THEN
1621 arp_util.debug('Validate_Payschd: ' || 'payment schedule id is invalid');
1622 END IF;
1623 END ;
1624
1625 /*-----------------------------------------------+
1626 | Check that the class of transaction is valid |
1627 +-----------------------------------------------*/
1628 /*------------------------------------------------------------+
1629 | Bug 1290698- Added one more class 'BR' for BOE/BR project |
1630 +-------------------------------------------------------------*/
1631 --snambiar added chargeback also in the list for adjustment
1632 IF ( p_ps_rec.class NOT IN ( 'INV','DM','CM','DEP','GUAR','BR','CB') )
1633 THEN
1634
1635 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_TRX_CLASS');
1636 FND_MESSAGE.SET_TOKEN ( 'CLASS', p_ps_rec.class ) ;
1637 FND_MSG_PUB.ADD ;
1638
1639 p_return_status := FND_API.G_RET_STS_ERROR;
1640 IF PG_DEBUG in ('Y', 'C') THEN
1641 arp_util.debug('Validate_Payschd: ' || 'class of transaction is invalid');
1642 END IF;
1643 END IF;
1644
1645 /*-----------------------------------------------+
1646 | Check that the Customer Trx Id exists in the |
1647 | payment schedule record. If not, return error |
1648 +-----------------------------------------------*/
1649
1650 IF ( p_ps_rec.customer_trx_id IS NULL OR p_ps_rec.customer_trx_id = 0 )
1651 THEN
1652
1653 /*-----------------------------------------------+
1654 | Set the message accordingly |
1655 +-----------------------------------------------*/
1656
1657 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_CUSTOMER_TRX_ID');
1658 FND_MESSAGE.SET_TOKEN('PAYMENT_SCHEDULE_ID',to_char(p_adj_rec.payment_schedule_id));
1659 FND_MSG_PUB.ADD ;
1660 p_return_status := FND_API.G_RET_STS_ERROR;
1661 IF PG_DEBUG in ('Y', 'C') THEN
1662 arp_util.debug('Validate_Payschd: ' || 'there is no valid customer trx id for the paysch id');
1663 END IF;
1664
1665 END IF;
1666
1667 /*-----------------------------------------------+
1668 | Check that the Customer Id exists in the |
1669 | payment schedule record. If not, return error |
1670 +-----------------------------------------------*/
1671
1672 IF ( p_ps_rec.customer_id IS NULL OR p_ps_rec.customer_id = 0 )
1673 THEN
1674
1675 /*-----------------------------------------------+
1676 | Set the message accordingly |
1677 +-----------------------------------------------*/
1678
1679 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_CUSTOMER_ID');
1680 FND_MESSAGE.SET_TOKEN('PAYMENT_SCHEDULE_ID',to_char(p_adj_rec.payment_schedule_id));
1681 FND_MSG_PUB.ADD ;
1682
1683 p_return_status := FND_API.G_RET_STS_ERROR;
1684 IF PG_DEBUG in ('Y', 'C') THEN
1685 arp_util.debug('Validate_Payschd: ' || 'there is no valid customer id for the paysch id');
1686 END IF;
1687
1688 END IF;
1689
1690 /*-----------------------------------------------+
1691 | Since the validation is successful populate |
1692 | the customer_trx_id and customer_id in adj rec|
1693 +-----------------------------------------------*/
1694
1695 p_adj_rec.customer_trx_id := p_ps_rec.customer_trx_id ;
1696
1697 /*-----------------------------------------------+
1698 | Check if the customer trx line Id is there if |
1699 | the Invoice type is LINE |
1700 +-----------------------------------------------*/
1701
1702 IF p_adj_rec.type = 'LINE'
1703 THEN
1704 --Bug 1395396 Modified <> to check for customer_trx_line_id for handling seperate
1705 IF (nvl(p_adj_rec.customer_trx_line_id,0) > 0 ) THEN
1706 l_count := 0 ;
1707 BEGIN
1708 SELECT count(*)
1709 INTO l_count
1710 FROM RA_CUSTOMER_TRX_LINES
1711 WHERE customer_trx_id = p_adj_rec.customer_trx_id AND
1712 customer_trx_line_id = p_adj_rec.customer_trx_line_id ;
1713
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 IF PG_DEBUG in ('Y', 'C') THEN
1717 arp_util.debug('Validate_Payschd: ' ||
1718 'EXCEPTION: Validate_Payschd() for CustTrxLineId = '||
1719 to_char(p_adj_rec.customer_trx_line_id), G_MSG_HIGH);
1720 END IF;
1721
1722 /*-----------------------------------------------+
1723 | Set unexpected error message and status |
1724 +-----------------------------------------------*/
1725 IF p_from_llca_call <> 'Y' THEN
1726 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Payschd');
1727 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1728 RETURN;
1729 ELSE
1730 insert into ar_llca_adj_trx_errors_gt
1731 (
1732 customer_trx_id,
1733 customer_trx_line_id,
1734 receivables_trx_id,
1735 error_message,
1736 invalid_value
1737 )
1738 values
1739 (
1740 l_customer_trx_id,
1741 l_customer_trx_line_id,
1742 l_receivables_trx_id,
1743 'Validate_Payschd',
1744 'payment_sch_id'
1745 );
1746 p_return_status := FND_API.G_RET_STS_ERROR;
1747 END IF;
1748
1749 END ;
1750
1751 IF ( l_count <> 1 ) THEN
1752 /*-----------------------------------------------+
1753 | Set error message and status |
1754 +-----------------------------------------------*/
1755 IF p_from_llca_call <> 'Y' THEN
1756 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_CUSTOMER_TRX_LINEID');
1757 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_LINE_ID',to_char(p_adj_rec.customer_trx_line_id) ) ;
1758 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_ID',to_char(p_adj_rec.customer_trx_id));
1759 FND_MSG_PUB.ADD ;
1760
1761 p_return_status := FND_API.G_RET_STS_ERROR;
1762 IF PG_DEBUG in ('Y', 'C') THEN
1763 arp_util.debug('Validate_Payschd: ' || 'customer trx line id is invalid');
1764 END IF;
1765
1766 ELSE
1767 insert into ar_llca_adj_trx_errors_gt
1768 (
1769 customer_trx_id,
1770 customer_trx_line_id,
1771 receivables_trx_id,
1772 error_message,
1773 invalid_value
1774 )
1775 values
1776 (
1777 l_customer_trx_id,
1778 l_customer_trx_line_id,
1779 l_receivables_trx_id,
1780 'AR_AAPI_NO_CUSTOMER_TRX_LINEID',
1781 'payment_sch_id'
1782 );
1783 p_return_status := FND_API.G_RET_STS_ERROR;
1784 END IF;
1785
1786 END IF ;
1787 -- Bug 1395396 - IF customer trx line id is null then do not raise error message
1788 -- Modification for LINE adjustment without line id
1789 /*
1790
1791 ELSE
1792 -- ARTA Changes, removing the check if type='LINE' then Line Id
1793 -- must be provided
1794 IF nvl(arp_global.sysparam.ta_installed_flag,'N') = 'Y' THEN
1795 null;
1796 ELSE
1797
1798 -- Set error message when the type is line and the |
1799 -- customer_trx_line_id is null |
1800 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_CUSTOMER_TRX_LINEID');
1801 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_LINE_ID',to_char(p_adj_rec.customer_trx_line_id) ) ;
1802 FND_MESSAGE.SET_TOKEN('CUSTOMER_TRX_ID',to_char(p_adj_rec.customer_trx_id));
1803 FND_MSG_PUB.ADD ;
1804
1805 p_return_status := FND_API.G_RET_STS_ERROR;
1806
1807 IF PG_DEBUG in ('Y', 'C') THEN
1808 arp_util.debug('Validate_Payschd: ' || 'customer trx line id is missing for adj type = LINE');
1809 END IF;
1810 END IF;
1811 */
1812 END IF ;
1813
1814 ELSE
1815 /*-----------------------------------------------+
1816 | The Customer Trx Line Id should not be there |
1817 +-----------------------------------------------*/
1818
1819 IF ( p_adj_rec.customer_trx_line_id IS NOT NULL OR
1820 p_adj_rec.customer_trx_line_id <> 0 )
1821 THEN
1822
1823 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_LINE_ID_FOR_NONLINE');
1824 FND_MESSAGE.SET_TOKEN ( 'CUSTOMER_TRX_LINE_ID', to_char(p_adj_rec.customer_trx_line_id) ) ;
1825 FND_MESSAGE.SET_TOKEN ( 'TYPE', p_ps_rec.class ) ;
1826 FND_MSG_PUB.ADD ;
1827
1828 p_return_status := FND_API.G_RET_STS_ERROR;
1829 IF PG_DEBUG in ('Y', 'C') THEN
1830 arp_util.debug('Validate_Payschd: ' || 'customer trx line id is not required for adj type ');
1831 END IF;
1832 END IF;
1833
1834 END IF;
1835
1836 IF PG_DEBUG in ('Y', 'C') THEN
1837 arp_util.debug('Validate_Payschd()-', G_MSG_MEDIUM);
1838 END IF;
1839
1840 RETURN ;
1841
1842
1843 EXCEPTION
1844
1845 WHEN OTHERS THEN
1846
1847 IF PG_DEBUG in ('Y', 'C') THEN
1848 arp_util.debug('EXCEPTION: Validate_Payschd() ', G_MSG_UERROR);
1849 arp_util.debug('Validate_Payschd: ' || 'Payment Schedule = ' ||
1850 p_adj_rec.payment_schedule_id, G_MSG_HIGH);
1851 END IF;
1852 /*-----------------------------------------------+
1853 | Set unexpected error message and status |
1854 +-----------------------------------------------*/
1855 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Validate_Payschd' );
1856 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1857 RETURN;
1858
1859 END Validate_Payschd;
1860
1861
1862 /*===========================================================================+
1863 | PROCEDURE |
1864 | Validate_Amount |
1865 | |
1866 | DESCRIPTION |
1867 | This routine validates the adjustment amount and status. It |
1868 | checks for the user approval limits, validates status and |
1869 | set the adjustment status accordingly. |
1870 | |
1871 | |
1872 | SCOPE - PUBLIC |
1873 | |
1874 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
1875 | arp_util.disable_debug |
1876 | arp_util.enable_debug |
1877 | fnd_api.g_exc_unexpected_error |
1878 | fnd_api.g_ret_sts_error |
1879 | fnd_api.g_ret_sts_error |
1880 | fnd_api.g_ret_sts_success |
1881 | fnd_api.to_boolean |
1882 | |
1883 | ARGUMENTS : IN: |
1884 | p_ps_rec |
1885 | OUT: |
1886 | IN/ OUT: |
1887 | p_return_status |
1888 | p_adj_rec |
1889 | |
1890 | RETURNS : NONE |
1891 | |
1892 | NOTES |
1893 | |
1894 | MODIFICATION HISTORY |
1895 | Vivek Halder 30-JUN-97 Created |
1896 | |
1897 | Saloni Shah 03-FEB-00 Changes for BOE/BR project has been made. |
1898 | Two new parameters have been introduced. |
1899 | p_chk_approval_limits: this flag indicates |
1900 | if the check for the adjustment amount should|
1901 | be validated against the approval user limits|
1902 | or not. |
1903 | p_check_amount: this flag is set to 'N' |
1904 | indicates that this is a reversal of an |
1905 | adjustment and hence the amount_due_remaining|
1906 | will not be zero. |
1907 | Satheesh Nambiar 25-Aug-00 Bug 1395396 Modified the code process $0 |
1908 | adjustment |
1909 | skoukunt 31-MAY-01 Bug 1773947, should not check approval limits|
1910 | when creating adjustment from cash engine, |
1911 | split merge, DMS interface, deductions in |
1912 | receipts W/B, this should works if we change |
1913 | p_chk_approval_limits to false while calling |
1914 | adjustment API, but the changes need to be |
1915 | made in number of files and not sure if |
1916 | setting the parameter would effect anything |
1917 | else. This condition was removed while making|
1918 | the API generic. |
1919 +===========================================================================*/
1920
1921 PROCEDURE Validate_amount (
1922 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
1923 p_ps_rec IN ar_payment_schedules%rowtype,
1924 p_chk_approval_limits IN varchar2,
1925 p_check_amount IN varchar2,
1926 p_return_status IN OUT NOCOPY varchar2
1927 ) IS
1928
1929 l_index number;
1930 l_user_id BINARY_INTEGER;
1931 l_approval_amount_to ar_approval_user_limits.amount_to%type;
1932 l_approval_amount_from ar_approval_user_limits.amount_from%type;
1933 l_approved_flag VARCHAR2(1);
1934 l_return_status VARCHAR2(1);
1935 l_currency_code VARCHAR2(10);
1936 l_amount Number;
1937 l_round_amount Number;
1938 BEGIN
1939
1940 IF PG_DEBUG in ('Y', 'C') THEN
1941 arp_util.debug('Validate_amount()+', G_MSG_MEDIUM);
1942 END IF;
1943
1944 /*------------------------------------------+
1945 | Initialize the return status to SUCCESS |
1946 +------------------------------------------*/
1947
1948 p_return_status := FND_API.G_RET_STS_SUCCESS;
1949 Begin
1950 select invoice_currency_code into l_currency_code
1951 from ra_customer_trx
1952 where customer_trx_id=p_adj_rec.customer_trx_id;
1953
1954 l_amount := p_adj_rec.amount;
1955
1956 l_round_amount := arp_util.currround(l_amount,l_currency_code);
1957
1958 IF l_amount <> l_round_amount then
1959 --Raise Validateion
1960 FND_MESSAGE.SET_NAME ('AR','ARTA_ARTADESM_INVALID_AMOUNT');
1961 FND_MSG_PUB.ADD ;
1962
1963 p_return_status := FND_API.G_RET_STS_ERROR;
1964
1965
1966 END If;
1967 exception
1968 when others then
1969 p_return_status := FND_API.G_RET_STS_ERROR;
1970 FND_MESSAGE.SET_NAME ('AR','ARTA_ARTADESM_INVALID_AMOUNT');
1971 FND_MSG_PUB.ADD ;
1972 end;
1973 /*----------------------------------------------------------------------+
1974 | If the type is INVOICE then the amount must close the invoice |
1975 | |
1976 | Change for the BOE/BR project has been made if the value of the |
1977 | flag p_check_amount is 'F'indicating that this is an adjustment |
1978 | reversal at invoice level, then amount_due remaining will not be zero |
1979 +-----------------------------------------------------------------------*/
1980 IF ( p_adj_rec.type = 'INVOICE' AND
1981 p_check_amount = FND_API.G_TRUE)
1982 THEN
1983
1984 /*-----------------------------------------------+
1985 | If amount is not specified then set it to |
1986 | close the transaction |
1987 +-----------------------------------------------*/
1988 IF ( p_adj_rec.amount IS NULL or p_adj_rec.amount = 0 )
1989 THEN
1990 /*-----------------------------------------------+
1991 | If amount is not specifiedand the amount due |
1992 | remaining is zero then should not create adj |
1993 +-----------------------------------------------*/
1994 IF ( p_ps_rec.amount_due_remaining = 0 )
1995 THEN
1996
1997 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_ADR_ZERO_INV');
1998 FND_MSG_PUB.ADD ;
1999
2000 p_return_status := FND_API.G_RET_STS_ERROR ;
2001 END IF;
2002 p_adj_rec.amount := - p_ps_rec.amount_due_remaining ;
2003 /*Bug 4258945*/
2004 validate_buckets(p_adj_rec,
2005 p_ps_rec,
2006 l_return_status);
2007 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
2008 THEN
2009 p_return_status := l_return_status ;
2010 END IF ;
2011 ELSE
2012 IF ( p_adj_rec.amount + p_ps_rec.amount_due_remaining <> 0 )
2013 THEN
2014
2015 FND_MESSAGE.SET_NAME ('AR', 'AR_TW_VAL_AMT_ADJ_INV');
2016 FND_MSG_PUB.ADD ;
2017
2018 p_return_status := FND_API.G_RET_STS_ERROR;
2019 END IF;
2020 /*Bug 4258945*/
2021 validate_buckets(p_adj_rec,
2022 p_ps_rec,
2023 l_return_status);
2024 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
2025 THEN
2026 p_return_status := l_return_status ;
2027 END IF ;
2028 END IF;
2029
2030 /*Bug 4258945
2031 Calculate all the buckets over here.
2032 */
2033 p_adj_rec.line_adjusted := -p_ps_rec.amount_line_items_remaining;
2034 p_adj_rec.tax_adjusted := -p_ps_rec.tax_remaining;
2035 p_adj_rec.freight_adjusted := -p_ps_rec.freight_remaining;
2036 p_adj_rec.receivables_charges_adjusted := -p_ps_rec.receivables_charges_remaining;
2037 END IF;
2038
2039 /*-----------------------------------------------+
2040 | Check if the adjustment amount is zero |
2041 +-----------------------------------------------*/
2042 --Bug 1395396 Removed the check for p_adj_rec.amount = 0 for
2043 --Processing $0 adjustment
2044
2045 IF ( p_adj_rec.amount IS NULL)
2046 THEN
2047 /*--------------------------------------------+
2048 | Set the message and return |
2049 +--------------------------------------------*/
2050
2051 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_ADJ_AMOUNT_ZERO');
2052 FND_MSG_PUB.ADD ;
2053
2054 p_return_status := FND_API.G_RET_STS_ERROR;
2055 END IF;
2056
2057 /*------------------------------------------------+
2058 | Verify if amount is within user approval limits|
2059 +-------------------------------------------------*/
2060 /*----------------------------------------------------+
2061 | Change introduced for the BR/BOE project. |
2062 | Special processing for bypassing limit check if |
2063 | p_chk_approval_limits is set to 'F' |
2064 +---------------------------------------------------*/
2065 -- Added OR conditions for bug fix 1773947
2066 IF ( p_chk_approval_limits = FND_API.G_FALSE OR
2067 p_adj_rec.created_from LIKE 'CASH_ENGINE%' OR
2068 p_adj_rec.created_from LIKE 'RECEIPT_REVERSAL%' OR
2069 p_adj_rec.created_from LIKE 'SPLIT_MERGE%' OR
2070 p_adj_rec.created_from LIKE 'DMS_INTERFACE%' OR
2071 p_adj_rec.created_from LIKE 'ENHANCED_CASH%' )
2072 THEN
2073 l_approved_flag := FND_API.G_TRUE ;
2074 ELSE
2075 AR_ADJVALIDATE_PVT.Within_approval_limits (
2076 p_adj_rec.amount,
2077 p_ps_rec.invoice_currency_code,
2078 l_approved_flag,
2079 l_return_status
2080 ) ;
2081 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
2082 THEN
2083 p_return_status := l_return_status ;
2084 END IF ;
2085
2086 END IF;
2087
2088 /*--------------------------------------------------+
2089 | Check Status. If null/blank then set the value |
2090 | based on l_approved_flag |
2091 +--------------------------------------------------*/
2092
2093 IF ( p_adj_rec.status IS NULL or p_adj_rec.status = ' ' )
2094 THEN
2095 IF ( l_approved_flag = FND_API.G_TRUE )
2096 THEN
2097 p_adj_rec.status := 'A' ;
2098 ELSE
2099 p_adj_rec.status := 'W' ;
2100 END IF ;
2101 ELSE
2102 /*-----------------------------------------------------+
2103 | Check valid status values provided by user |
2104 +-----------------------------------------------------*/
2105
2106 IF (p_adj_rec.status <> 'A' AND p_adj_rec.status <> 'W' AND
2107 p_adj_rec.status <> 'M' )
2108 THEN
2109
2110 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_CREATE_STATUS');
2111 FND_MESSAGE.SET_TOKEN ( 'STATUS', p_adj_rec.status ) ;
2112 FND_MSG_PUB.ADD ;
2113
2114 p_return_status := FND_API.G_RET_STS_ERROR ;
2115 END IF;
2116
2117 /*-----------------------------------------------------+
2118 | Handle the case for setting status to W if outside |
2119 | approval limits during creation of Adjustments |
2120 +-----------------------------------------------------*/
2121
2122 IF ( l_approved_flag = FND_API.G_FALSE AND p_adj_rec.status = 'A' )
2123 THEN
2124 p_adj_rec.status := 'W' ;
2125 END IF;
2126
2127 END IF;
2128
2129 IF PG_DEBUG in ('Y', 'C') THEN
2130 arp_util.debug('Validate_Amount()-', G_MSG_MEDIUM);
2131 END IF;
2132
2133 RETURN ;
2134
2135 EXCEPTION
2136 WHEN OTHERS THEN
2137
2138 IF PG_DEBUG in ('Y', 'C') THEN
2139 arp_util.debug('EXCEPTION: Validate_Amount() ', G_MSG_UERROR);
2140 END IF;
2141
2142 /*-----------------------------------------------+
2143 | Set unexpected error message and status |
2144 +-----------------------------------------------*/
2145 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Amount');
2146 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2147 RETURN;
2148
2149 END Validate_Amount;
2150
2151 /*===========================================================================+
2152 | PROCEDURE |
2153 | Validate_Rcvtrxccid |
2154 | |
2155 | DESCRIPTION |
2156 | This routine validates the Receivables Trx Id and CCId |
2157 | It sets the set_of_books_id value in the adjustment record |
2158 | and also the code combination id (if required) |
2159 | |
2160 | |
2161 | SCOPE - PUBLIC |
2162 | |
2163 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2164 | arp_util.disable_debug |
2165 | arp_util.enable_debug |
2166 | fnd_api.g_exc_unexpected_error |
2167 | fnd_api.g_ret_sts_error |
2168 | fnd_api.g_ret_sts_error |
2169 | fnd_api.g_ret_sts_success |
2170 | fnd_api.to_boolean |
2171 | |
2172 | ARGUMENTS : IN: |
2173 | |
2174 | OUT: |
2175 | p_return_status |
2176 | |
2177 | IN/ OUT: |
2178 | p_adj_rec |
2179 | |
2180 | RETURNS : NONE |
2181 | |
2182 | NOTES |
2183 | |
2184 | MODIFICATION HISTORY |
2185 | Vivek Halder 13-JUN-97 |
2186 | Saloni Shah 03-FEB-00 Changes made for BOE/BR project. |
2187 | Defined a local variable |
2188 | l_accounting_affect. If the value of this |
2189 | flag is set to 'N', then the code |
2190 | combination id will be set to null. |
2191 | Satheesh Nambiar 01-Jun-00 Bug 1290698. Modified Validate_Rcvtrxccid |
2192 | to include PS record also. For BOE/BR, the |
2193 | PS class 'BR' can only be adjusted by |
2194 | receivables trx type 'ENDORSMENT' |
2195 | Satheesh Nambiar 29-Jun-00 Bug 1343351.Fixed the validation for 'BR' class
2196 +===========================================================================*/
2197
2198 PROCEDURE Validate_Rcvtrxccid (
2199 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2200 p_ps_rec IN ar_payment_schedules%rowtype,
2201 p_return_status IN OUT NOCOPY varchar2,
2202 p_from_llca_call IN varchar2 DEFAULT 'N'
2203 ) IS
2204
2205 l_index number;
2206 l_set_of_books_id ar_receivables_trx.set_of_books_id%type := NULL;
2207 l_cc_id ar_receivables_trx.code_combination_id%type := NULL;
2208 l_count number;
2209 l_found BOOLEAN;
2210 l_accounting_affect_flag varchar2(1);
2211 l_receivable_trx_type ar_receivables_trx.TYPE%type := NULL;
2212 l_gl_account_source ar_receivables_trx.GL_ACCOUNT_SOURCE%type; /*Bug 2925924*/
2213
2214 -- Line Level Adjustment
2215 l_customer_trx_id number;
2216 l_customer_trx_line_id number;
2217 l_receivables_trx_id number;
2218
2219 BEGIN
2220
2221 IF PG_DEBUG in ('Y', 'C') THEN
2222 arp_util.debug('Validate_Rcvtrxccid()+', G_MSG_MEDIUM);
2223 arp_util.debug('p_from_llca_call : ' || p_from_llca_call);
2224 END IF;
2225
2226 /*------------------------------------------+
2227 | Initialize the return status to SUCCESS |
2228 +------------------------------------------*/
2229
2230 p_return_status := FND_API.G_RET_STS_SUCCESS;
2231
2232 -- Initialize Line Level Adjustment
2233 l_customer_trx_id := p_adj_rec.customer_trx_id;
2234 l_customer_trx_line_id := p_adj_rec.customer_trx_line_id;
2235 l_receivables_trx_id := p_adj_rec.receivables_trx_id;
2236
2237
2238 l_found := FALSE ;
2239
2240 FOR l_index IN 1..G_RCVTRX_TBL.COUNT LOOP
2241
2242 IF (p_adj_rec.receivables_trx_id =
2243 G_RCVTRX_TBL(l_index).receivables_trx_id )
2244 THEN
2245 G_receivables_name := G_RCVTRX_TBL(l_index).name ;
2246 l_cc_id := G_RCVTRX_TBL(l_index).code_combination_id;
2247 l_accounting_affect_flag := G_RCVTRX_TBL(l_index).accounting_affect_flag;
2248 l_receivable_trx_type := G_RCVTRX_TBL(l_index).type;
2249 l_gl_account_source := G_RCVTRX_TBL(l_index).gl_account_source; /*Bug 2925924*/
2250 l_found := TRUE ;
2251
2252 EXIT ;
2253 END IF;
2254
2255 END LOOP;
2256
2257 /*------------------------------------------------+
2258 | Bug 1290698. For BOE/BR, a PS class 'BR' |
2259 | can only be adjusted by adjustment_type |
2260 | 'E'-ENDORSMENT or 'X'- EXCHANGE |
2261 | Bug 1343351- Fixed the IF condition |
2262 +------------------------------------------------*/
2263 IF (p_ps_rec.class = 'BR' and ((l_receivable_trx_type <> 'ENDORSEMENT')AND
2264 (p_adj_rec.receivables_trx_id <> -15 )))
2265 THEN
2266 IF PG_DEBUG in ('Y', 'C') THEN
2267 arp_util.debug('Validate_Rcvtrxccid: ' || 'For Payment schedule class BR,Receivable trx id
2268 should be of type ENDORSEMENT or -15',G_MSG_HIGH);
2269 END IF;
2270
2271 IF p_from_llca_call <> 'Y' THEN
2272 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_RCVABLE_TRX_ID');
2273 FND_MESSAGE.SET_TOKEN ( 'RECEIVABLES_TRX_ID', to_char(p_adj_rec.receivables_trx_id) ) ;
2274 FND_MSG_PUB.ADD ;
2275 p_return_status := FND_API.G_RET_STS_ERROR;
2276 ELSE
2277 insert into ar_llca_adj_trx_errors_gt
2278 (
2279 customer_trx_id,
2280 customer_trx_line_id,
2281 receivables_trx_id,
2282 error_message,
2283 invalid_value
2284 )
2285 values
2286 (
2287 l_customer_trx_id,
2288 l_customer_trx_line_id,
2289 l_receivables_trx_id,
2290 'AR_AAPI_INVALID_RCVABLE_TRX_ID',
2291 'receivables_trx_id'
2292 );
2293 p_return_status := FND_API.G_RET_STS_ERROR;
2294 END IF;
2295
2296
2297 END IF;
2298 /*-------------------------------------------------------+
2299 |Re-defaulting adjustment_type to 'E' for ENDORSEMENT |
2300 |and 'X' for receivables_trx_id -15 Except for Reversal |
2301 +-------------------------------------------------------*/
2302 IF (l_receivable_trx_type = 'ENDORSEMENT'
2303 AND p_adj_rec.created_from <> 'REVERSE_ADJUSTMENT')
2304 THEN
2305 p_adj_rec.adjustment_type:='E';
2306 END IF;
2307 IF (p_adj_rec.receivables_trx_id = -15
2308 AND p_adj_rec.created_from <> 'REVERSE_ADJUSTMENT') THEN
2309 p_adj_rec.adjustment_type:='X';
2310 END IF;
2311
2312 IF ( NOT l_found )
2313 THEN
2314 /*-----------------------------------------------+
2315 | Set the message |
2316 +-----------------------------------------------*/
2317 IF p_from_llca_call <> 'Y' THEN
2318
2319 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_RCVABLE_TRX_ID');
2320 FND_MESSAGE.SET_TOKEN ( 'RECEIVABLES_TRX_ID', to_char(p_adj_rec.receivables_trx_id) ) ;
2321 FND_MSG_PUB.ADD ;
2322 p_return_status := FND_API.G_RET_STS_ERROR;
2323
2324 ELSE
2325 insert into ar_llca_adj_trx_errors_gt
2326 (
2327 customer_trx_id,
2328 customer_trx_line_id,
2329 receivables_trx_id,
2330 error_message,
2331 invalid_value
2332 )
2333 values
2334 (
2335 l_customer_trx_id,
2336 l_customer_trx_line_id,
2337 l_receivables_trx_id,
2338 'AR_AAPI_INVALID_RCVABLE_TRX_ID',
2339 'receivables_trx_id'
2340 );
2341 p_return_status := FND_API.G_RET_STS_ERROR;
2342 END IF;
2343 END IF;
2344
2345 /*--------------------------------------------------------------------------+
2346 | This validation has been added for the BR/BOE project. |
2347 | This flag would indicate that whether any accounting enteries need to |
2348 | created or not. Also the code_combination_id will be set to null if the |
2349 | accounting_affect_flag is set to 'N' |
2350 +---------------------------------------------------------------------------*/
2351 IF (l_accounting_affect_flag = 'N')
2352 THEN
2353 IF PG_DEBUG in ('Y', 'C') THEN
2354 arp_util.debug('Validate_Rcvtrxccid: ' || 'for adj without accounting information - do not check the ccid');
2355 END IF;
2356 p_adj_rec.code_combination_id := NULL;
2357 return;
2358 END IF;
2359
2360 /*--------------------------------------------+
2361 | Check the Code Combination Id |
2362 | If no value is provided default it to the |
2363 | code combination Id of the receivables Trx |
2364 +--------------------------------------------*/
2365
2366 IF ( p_adj_rec.code_combination_id IS NULL OR
2367 p_adj_rec.code_combination_id = 0 )
2368 THEN
2369 /*--------------------------------------------+
2370 | If no default value exists and none is |
2371 | provided by user then set error |
2372 +--------------------------------------------*/
2373
2374 IF ( (l_cc_id IS NULL OR l_cc_id = 0) and l_gl_account_source = 'ACTIVITY_GL_ACCOUNT')
2375 THEN
2376 IF p_from_llca_call <> 'Y' THEN
2377 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_CCID_FOR_ACTIVITY');
2378 FND_MESSAGE.SET_TOKEN ( 'RECEIVABLES_TRX_ID', to_char(p_adj_rec.receivables_trx_id) ) ;
2379 FND_MSG_PUB.ADD ;
2380 p_return_status := FND_API.G_RET_STS_ERROR;
2381 ELSE
2382 insert into ar_llca_adj_trx_errors_gt
2383 (
2384 customer_trx_id,
2385 customer_trx_line_id,
2386 receivables_trx_id,
2387 error_message,
2388 invalid_value
2389 )
2390 values
2391 (
2392 l_customer_trx_id,
2393 l_customer_trx_line_id,
2394 l_receivables_trx_id,
2395 'AR_AAPI_NO_CCID_FOR_ACTIVITY',
2396 'receivables_trx_id'
2397 );
2398 p_return_status := FND_API.G_RET_STS_ERROR;
2399 END IF;
2400
2401 END IF ;
2402
2403 /*--------------------------------------------+
2404 | Else default to the CCid of the Receivables |
2405 | Activity |
2406 +--------------------------------------------*/
2407
2408 p_adj_rec.code_combination_id := l_cc_id ;
2409
2410 ELSE
2411 /*--------------------------------------------+
2412 | Validate the code combination Id provided |
2413 +--------------------------------------------*/
2414
2415 l_found := FALSE ;
2416
2417 FOR l_index IN 1..G_CCID_TBL.COUNT LOOP
2418
2419 IF (G_CCID_TBL.EXISTS (p_adj_rec.code_combination_id))
2420 THEN
2421 l_found := TRUE;
2422 EXIT ;
2423 END IF;
2424
2425 END LOOP;
2426
2427 IF ( NOT l_found)
2428 THEN
2429
2430 /*------------------------------------------+
2431 | Check the code combination from database |
2432 +------------------------------------------*/
2433 BEGIN
2434
2435 l_count := 0 ;
2436 SELECT count(*)
2437 INTO l_count
2438 FROM gl_code_combinations
2439 WHERE code_combination_id = p_adj_rec.code_combination_id
2440 AND enabled_flag = 'Y'
2441 AND SYSDATE BETWEEN NVL(start_date_active, sysdate)
2442 AND NVL(end_date_active, sysdate);
2443
2444 EXCEPTION
2445 WHEN OTHERS THEN
2446 /*---------------------------------------------+
2447 |Set unexpected error message and status |
2448 +---------------------------------------------*/
2449 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Rcvtrxccid');
2450 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2451 END ;
2452
2453 IF ( l_count <> 1 )
2454 THEN
2455 /*-------------------------------------------+
2456 | Set the message |
2457 +-------------------------------------------*/
2458 IF p_from_llca_call <> 'Y' THEN
2459 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_CCID');
2460 FND_MESSAGE.SET_TOKEN ( 'CCID', p_adj_rec.code_combination_id ) ;
2461 FND_MSG_PUB.ADD ;
2462 p_return_status := FND_API.G_RET_STS_ERROR;
2463 ELSE
2464 insert into ar_llca_adj_trx_errors_gt
2465 (
2466 customer_trx_id,
2467 customer_trx_line_id,
2468 receivables_trx_id,
2469 error_message,
2470 invalid_value
2471 )
2472 values
2473 (
2474 l_customer_trx_id,
2475 l_customer_trx_line_id,
2476 l_receivables_trx_id,
2477 'AR_AAPI_INVALID_CCID',
2478 'receivables_trx_id'
2479 );
2480 p_return_status := FND_API.G_RET_STS_ERROR;
2481 END IF;
2482
2483 END IF;
2484
2485 END IF;
2486
2487 /*--------------------------------------------+
2488 | Check that if the Profile Option : Allow |
2489 | Override of default activity is set to Y |
2490 | then value must be equal to l_cc_id |
2491 +--------------------------------------------*/
2492
2493 IF ( g_context_rec.override_activity_option = 'N' AND
2494 l_cc_id IS NOT NULL )
2495 THEN
2496 IF ( p_adj_rec.code_combination_id <> l_cc_id )
2497 THEN
2498 IF p_from_llca_call <> 'Y' THEN
2499 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_OVERRIDE_CCID_DISALLOW');
2500 FND_MSG_PUB.ADD ;
2501 p_return_status := FND_API.G_RET_STS_ERROR;
2502 ELSE
2503 insert into ar_llca_adj_trx_errors_gt
2504 (
2505 customer_trx_id,
2506 customer_trx_line_id,
2507 receivables_trx_id,
2508 error_message,
2509 invalid_value
2510 )
2511 values
2512 (
2513 l_customer_trx_id,
2514 l_customer_trx_line_id,
2515 l_receivables_trx_id,
2516 'AR_AAPI_OVERRIDE_CCID_DISALLOW',
2517 'receivables_trx_id'
2518 );
2519 p_return_status := FND_API.G_RET_STS_ERROR;
2520 END IF;
2521 END IF;
2522 END IF;
2523
2524 END IF;
2525
2526 /*-----------------------------------------------+
2527 | Set the Set of books Id |
2528 +-----------------------------------------------*/
2529
2530 p_adj_rec.set_of_books_id := arp_global.set_of_books_id ;
2531 IF PG_DEBUG in ('Y', 'C') THEN
2532 arp_util.debug('Validate_Rcvtrxccid()-', G_MSG_MEDIUM);
2533 END IF;
2534 RETURN ;
2535
2536 EXCEPTION
2537 WHEN OTHERS THEN
2538
2539 IF PG_DEBUG in ('Y', 'C') THEN
2540 arp_util.debug('EXCEPTION: Validate_Rcvtrxccid', G_MSG_UERROR);
2541 END IF;
2542 /*-----------------------------------------------+
2543 | Set unexpected error message and status |
2544 +-----------------------------------------------*/
2545 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Rcvtrxccid');
2546 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2547
2548 RETURN;
2549
2550 END Validate_Rcvtrxccid;
2551
2552 /*===========================================================================+
2553 | PROCEDURE |
2554 | Validate_dates |
2555 | DESCRIPTION |
2556 | This routine validates the apply and gl dates for both |
2557 | creation and reversal |
2558 | |
2559 | SCOPE - PUBLIC |
2560 | |
2561 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2562 | |
2563 | ARGUMENTS : IN: |
2564 | p_adj_rec |
2565 | OUT: |
2566 | p_return_status |
2567 | IN/ OUT: |
2568 | |
2569 | RETURNS : NONE |
2570 | |
2571 | NOTES |
2572 | |
2573 | MODIFICATION HISTORY |
2574 | Vivek Halder 13-JUN-97 |
2575 | |
2576 +===========================================================================*/
2577
2578 PROCEDURE Validate_dates (
2579 p_apply_date IN ar_adjustments.apply_date%type,
2580 p_gl_date IN ar_adjustments.gl_date%type,
2581 p_ps_rec IN ar_payment_schedules%rowtype,
2582 p_return_status IN OUT NOCOPY varchar2
2583 ) IS
2584
2585 l_index number;
2586 l_found_flag BOOLEAN;
2587 l_count number ;
2588 l_set_of_books_id AR_SYSTEM_PARAMETERS.SET_OF_BOOKS_ID%TYPE ;
2589
2590 BEGIN
2591
2592 IF PG_DEBUG in ('Y', 'C') THEN
2593 arp_util.debug('Validate_dates()+', G_MSG_MEDIUM);
2594 END IF;
2595
2596 /*------------------------------------------+
2597 | Initialize the return status to SUCCESS |
2598 +------------------------------------------*/
2599
2600 p_return_status := FND_API.G_RET_STS_SUCCESS;
2601
2602 /*------------------------------------------+
2603 | The dates should not be null |
2604 +-----------------------------------------*/
2605
2606 IF ( p_apply_date IS NULL )
2607 THEN
2608
2609 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_APPLY_DATE');
2610 FND_MSG_PUB.ADD ;
2611 p_return_status := FND_API.G_RET_STS_ERROR;
2612 END IF;
2613
2614 IF ( p_gl_date IS NULL )
2615 THEN
2616
2617 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_GL_DATE');
2618 FND_MSG_PUB.ADD ;
2619 p_return_status := FND_API.G_RET_STS_ERROR;
2620 END IF;
2621
2622 /*------------------------------------------+
2623 | Validate from GL date from period cache |
2624 | Check that it lies in open/future period|
2625 +-----------------------------------------*/
2626
2627 l_found_flag := FALSE ;
2628
2629 FOR l_index IN 1..G_GLPERIOD_TBL.COUNT LOOP
2630
2631 IF (trunc(p_gl_date) >=
2632 nvl(G_GLPERIOD_TBL(l_index).start_date, trunc(p_gl_date))
2633 AND
2634 trunc(p_gl_date) <=
2635 nvl(G_GLPERIOD_TBL(l_index).end_date,trunc(p_gl_date))
2636 )
2637 THEN
2638 l_found_flag := TRUE ;
2639 EXIT ;
2640 END IF;
2641
2642 END LOOP;
2643
2644 /*------------------------------------------+
2645 | If it does not exist in cache validate it|
2646 | from database |
2647 +------------------------------------------*/
2648
2649 IF ( NOT l_found_flag )
2650 THEN
2651
2652 select set_of_books_id
2653 into l_set_of_books_id
2654 from ar_system_parameters;
2655
2656 l_count := 0 ;
2657
2658 SELECT count(*)
2659 INTO l_count
2660 FROM gl_period_statuses g,
2661 gl_sets_of_books b
2662 WHERE g.application_id = 222
2663 AND g.set_of_books_id = l_set_of_books_id
2664 AND g.set_of_books_id = b.set_of_books_id
2665 AND g.period_type = b.accounted_period_type
2666 AND g.adjustment_period_flag = 'N'
2667 AND g.closing_status IN ('O','F')
2668 AND trunc(p_gl_date) BETWEEN nvl(trunc(g.start_date),
2669 trunc(p_gl_date))
2670 AND nvl(trunc(g.end_date),trunc(p_gl_date)) ;
2671
2672 IF ( l_count > 0 )
2673 THEN
2674 l_found_flag := TRUE ;
2675 END IF;
2676
2677 END IF;
2678
2679 /*------------------------------------------+
2680 | If no valid period found then set message|
2681 | and return |
2682 +------------------------------------------*/
2683
2684 IF ( NOT l_found_flag )
2685 THEN
2686 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_GLDATE_INVALID_PERIOD');
2687 --Int'l Calendar Project
2688 FND_MESSAGE.SET_TOKEN ( 'GL_DATE', fnd_date.date_to_chardate(p_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt) ) ;
2689 FND_MSG_PUB.ADD ;
2690 p_return_status := FND_API.G_RET_STS_ERROR;
2691 END IF;
2692
2693 /*---------------------------------------+
2694 | Check that apply date should be equal |
2695 | to or greater than the transaction date|
2696 +---------------------------------------*/
2697
2698 IF ( trunc(p_apply_date) < trunc(p_ps_rec.trx_date) )
2699 THEN
2700 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_APPLYDATE_LT_TRXDATE');
2701 --Int'l Calendar Project
2702 FND_MESSAGE.SET_TOKEN ( 'APPLY_DATE', fnd_date.date_to_chardate(p_apply_date, calendar_aware=> FND_DATE.calendar_aware_alt) ) ;
2703 FND_MESSAGE.SET_TOKEN ( 'TRX_DATE', fnd_date.date_to_chardate(p_ps_rec.trx_date, calendar_aware=> FND_DATE.calendar_aware_alt) ) ;
2704 FND_MSG_PUB.ADD ;
2705 p_return_status := FND_API.G_RET_STS_ERROR;
2706
2707 END IF;
2708
2709 /*---------------------------------------+
2710 | Check that GL date should be equal to |
2711 | or greater than the transaction GL date|
2712 +---------------------------------------*/
2713
2714 IF ( trunc(p_gl_date) < trunc(p_ps_rec.gl_date) )
2715 THEN
2716 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_GLDATE_LT_TRXGLDATE');
2717 --Int'l Calendar Project
2718 FND_MESSAGE.SET_TOKEN ( 'GL_DATE', fnd_date.date_to_chardate(p_gl_date, calendar_aware=> FND_DATE.calendar_aware_alt) ) ;
2719 FND_MESSAGE.SET_TOKEN ( 'TRX_GL_DATE', fnd_date.date_to_chardate(p_ps_rec.gl_date, calendar_aware=> FND_DATE.calendar_aware_alt) ) ;
2720 FND_MSG_PUB.ADD ;
2721 p_return_status := FND_API.G_RET_STS_ERROR;
2722 END IF;
2723
2724 IF PG_DEBUG in ('Y', 'C') THEN
2725 arp_util.debug('Validate_dates()-', G_MSG_MEDIUM);
2726 END IF;
2727
2728 RETURN ;
2729
2730 EXCEPTION
2731 WHEN OTHERS THEN
2732
2733 IF PG_DEBUG in ('Y', 'C') THEN
2734 arp_util.debug('EXCEPTION: Validate_dates ', G_MSG_UERROR);
2735 END IF;
2736 /*-----------------------------------------------+
2737 | Set unexpected error message and status |
2738 +-----------------------------------------------*/
2739 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_dates' );
2740 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2741
2742 RETURN;
2743
2744 END Validate_dates;
2745
2746
2747 /*===========================================================================+
2748 | PROCEDURE |
2749 | Validate_doc_seq |
2750 | |
2751 | DESCRIPTION |
2752 | This routine validates the Document Sequence value and sets |
2753 | the Id also |
2754 | |
2755 | |
2756 | SCOPE - PRIVATE |
2757 | |
2758 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2759 | arp_util.disable_debug |
2760 | arp_util.enable_debug |
2761 | fnd_api.g_exc_unexpected_error |
2762 | fnd_api.g_ret_sts_error |
2763 | fnd_api.g_ret_sts_error |
2764 | fnd_api.g_ret_sts_success |
2765 | fnd_api.to_boolean |
2766 | |
2767 | ARGUMENTS : IN: |
2768 | OUT: |
2769 | p_return_status |
2770 | |
2771 | IN/ OUT: |
2772 | p_adj_rec |
2773 | |
2774 | |
2775 | |
2776 | RETURNS : NONE |
2777 | |
2778 | NOTES |
2779 | |
2780 | MODIFICATION HISTORY |
2781 | Vivek Halder 13-JUN-97 |
2782 | |
2783 +===========================================================================*/
2784
2785 PROCEDURE Validate_doc_seq (
2786 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2787 p_return_status IN OUT NOCOPY varchar2
2788 ) IS
2789
2790 l_dummy BINARY_INTEGER;
2791 l_sequence_name fnd_document_sequences.name%type;
2792 l_doc_sequence_id fnd_document_sequences.doc_sequence_id%type ;
2793
2794 --bug2629883
2795 l_seq_assign_id fnd_doc_sequence_assignments.doc_sequence_assignment_id%TYPE;
2796 l_sequence_type fnd_document_sequences.type%TYPE;
2797 l_db_sequence_name fnd_document_sequences.db_sequence_name%TYPE;
2798 l_prod_table_name fnd_document_sequences.table_name%TYPE;
2799 l_audit_table_name fnd_document_sequences.audit_table_name%TYPE;
2800 l_mesg_flag fnd_document_sequences.message_flag%TYPE;
2801
2802 BEGIN
2803
2804 IF PG_DEBUG in ('Y', 'C') THEN
2805 arp_util.debug('Validate_doc_seq()+', G_MSG_MEDIUM);
2806 END IF;
2807
2808 /*------------------------------------------+
2809 | Initialize the return status to SUCCESS |
2810 +------------------------------------------*/
2811
2812 p_return_status := FND_API.G_RET_STS_SUCCESS;
2813
2814 /*------------------------------------------------+
2815 | Document sequences are only applicable if the |
2816 | unique seq number option is not equal to N |
2817 +------------------------------------------------*/
2818 IF PG_DEBUG in ('Y', 'C') THEN
2819 arp_util.debug('Validate_doc_seq():'|| g_context_rec.unique_seq_numbers );
2820 END IF;
2821
2822 IF ( NVL(g_context_rec.unique_seq_numbers, 'N') = 'N' )
2823 THEN
2824 IF ( p_adj_rec.doc_sequence_id IS NOT NULL OR
2825 p_adj_rec.doc_sequence_id <> 0 OR
2826 p_adj_rec.doc_sequence_value IS NOT NULL OR
2827 p_adj_rec.doc_sequence_value <> 0 )
2828 THEN
2829 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_DOC_SEQ_NOT_REQD');
2830 FND_MESSAGE.SET_TOKEN ( 'DOCUMENT_SEQ',
2831 to_char(nvl(p_adj_rec.doc_sequence_id,p_adj_rec.doc_sequence_value ))) ;
2832 FND_MSG_PUB.ADD ;
2833 p_return_status := FND_API.G_RET_STS_ERROR;
2834 END IF;
2835 ELSE
2836 /*--------------------------------------------------------+
2840 p_adj_rec.doc_sequence_id := NULL;
2837 | Get info about whether any doc seq assignment exists. |
2838 +---------------------------------------------------------*/
2839
2841 BEGIN
2842 l_dummy := fnd_seqnum.get_seq_info(
2843 222,
2844 G_receivables_name,
2845 arp_global.set_of_books_id,
2846 'A',
2847 p_adj_rec.apply_date,
2848 p_adj_rec.doc_sequence_id,
2849 l_sequence_type,
2850 l_sequence_name,
2851 l_db_sequence_name,
2852 l_seq_assign_id,
2853 l_prod_table_name,
2854 l_audit_table_name,
2855 l_mesg_flag,'y','y');
2856 IF PG_DEBUG in ('Y', 'C') THEN
2857 arp_util.debug('Validate_doc_seq():'|| p_adj_rec.doc_sequence_id );
2858 END IF;
2859 EXCEPTION
2860 WHEN OTHERS THEN
2861 arp_util.debug('Validate_doc_seq() : Exception raised by get_seq_info');
2862 p_return_status := FND_API.G_RET_STS_ERROR;
2863 END;
2864
2865
2866
2867 /*------------------------------------------------------------------+
2868 | Get the doc_seq_val if we found that doc seq assignment exists. |
2869 +-------------------------------------------------------------------*/
2870
2871 IF (p_adj_rec.doc_sequence_id IS NOT NULL )
2872 THEN
2873
2874 IF (p_adj_rec.doc_sequence_value IS NOT NULL )
2875 THEN
2876 IF ( l_sequence_type in ( 'A' , 'G' ))
2877 THEN
2878 /*-----------------------------------------+
2879 | Automatic Document Numbering case |
2880 | Document seuqence value should not exist|
2881 +-----------------------------------------*/
2882 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_DOC_SEQ_NOT_REQD');
2883 FND_MESSAGE.SET_TOKEN ( 'DOCUMENT_SEQ',
2884 to_char(p_adj_rec.doc_sequence_value) ) ;
2885 FND_MSG_PUB.ADD ;
2886
2887 p_return_status := FND_API.G_RET_STS_ERROR;
2888 END IF;
2889 END IF;
2890
2891 /*---------------------------------------------------+
2892 | Auto Doc Num with doc seq setup exists , |
2893 | so, call the get_seq_val to get next seq value |
2894 +----------------------------------------------------*/
2895 IF ( p_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2896
2897 BEGIN
2898 l_dummy := fnd_seqnum.get_seq_val (
2899 arp_global.G_AR_APP_ID,
2900 G_receivables_name,
2901 arp_global.set_of_books_id,
2902 'A',
2903 p_adj_rec.apply_date,
2907 IF PG_DEBUG in ('Y', 'C') THEN
2904 p_adj_rec.doc_sequence_value ,
2905 p_adj_rec.doc_sequence_id
2906 );
2908 arp_util.debug('Validate_doc_seq():'|| p_adj_rec.doc_sequence_value );
2909 END IF;
2910 EXCEPTION
2911 WHEN OTHERS THEN
2912 arp_util.debug('Validate_doc_seq() : Exception raised by get_seq_val');
2913 p_return_status := FND_API.G_RET_STS_ERROR;
2914 END;
2915
2916 END IF;
2917 ELSE
2918 IF PG_DEBUG in ('Y', 'C') THEN
2919 arp_util.debug('Validate_doc_seq() : No active document sequence assignments',
2920 G_MSG_MEDIUM);
2921 END IF;
2922 p_adj_rec.doc_sequence_value := NULL;
2923 IF (g_context_rec.unique_seq_numbers = 'A'
2924 AND p_adj_rec.doc_sequence_id IS NULL
2925 AND p_adj_rec.doc_sequence_value IS NULL )
2926 THEN
2927
2928 FND_MESSAGE.SET_NAME ('FND', 'UNIQUE-ALWAYS USED');
2929 FND_MSG_PUB.ADD ;
2930
2931 p_return_status := FND_API.G_RET_STS_ERROR;
2932 END IF;
2933
2934 END IF; -- Handled for both cases: when seq assign exists and when it doesn't..
2935
2936 END IF; ---Handled all combinations of Seq Numbering...
2937
2938
2939 IF PG_DEBUG in ('Y', 'C') THEN
2940 arp_util.debug('Validate_doc_seq()-', G_MSG_MEDIUM);
2941 END IF;
2942
2943 RETURN ;
2944 EXCEPTION
2945
2946 WHEN NO_DATA_FOUND THEN
2947 /*-----------------------------------------+
2948 | No document assignment was found. |
2949 | Generate an error if document numbering |
2950 | is mandatory. |
2951 +-----------------------------------------*/
2952 IF (g_context_rec.unique_seq_numbers = 'A' )
2953 THEN
2954
2955 FND_MESSAGE.SET_NAME ('FND', 'UNIQUE-ALWAYS USED');
2956 FND_MSG_PUB.ADD ;
2957
2958 p_return_status := FND_API.G_RET_STS_ERROR;
2959 ELSE
2960 p_adj_rec.doc_sequence_id := NULL;
2961 p_adj_rec.doc_sequence_value := NULL;
2962 END IF;
2963 RETURN;
2964 WHEN OTHERS THEN
2965
2966 IF PG_DEBUG in ('Y', 'C') THEN
2967 arp_util.debug('EXCEPTION: Validate_doc_seq ', G_MSG_UERROR);
2968 END IF;
2969 /*-----------------------------------------------+
2970 | Set unexpected error message and status |
2971 +-----------------------------------------------*/
2972 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_doc_seq' );
2973 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2974 RETURN;
2975
2976 END Validate_doc_seq;
2977
2978
2979 /*===========================================================================+
2980 | PROCEDURE Validate_reason_code |
2981 | |
2982 | DESCRIPTION |
2983 | This routine validates the reason code of adjustment |
2984 | |
2985 | |
2986 | |
2987 | SCOPE - PUBLIC |
2988 | |
2989 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
2990 | arp_util.disable_debug |
2991 | arp_util.enable_debug |
2992 | fnd_api.g_exc_unexpected_error |
2993 | fnd_api.g_ret_sts_error |
2994 | fnd_api.g_ret_sts_error |
2995 | fnd_api.g_ret_sts_success |
2996 | fnd_api.to_boolean |
2997 | |
2998 | ARGUMENTS : IN: |
2999 | |
3000 | OUT: |
3001 | |
3002 | IN/ OUT: |
3003 | |
3004 | p_return_status |
3005 | p_adj_rec |
3006 | |
3007 | RETURNS : NONE |
3008 | |
3009 | NOTES |
3010 | |
3011 | MODIFICATION HISTORY |
3012 | Vivek Halder 13-JUN-97 |
3013 | |
3014 +===========================================================================*/
3015
3016 PROCEDURE Validate_Reason_code (
3017 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
3018 p_return_status IN OUT NOCOPY varchar2
3019 ) IS
3020
3021 l_index number;
3022 l_found BOOLEAN;
3023 BEGIN
3024
3025 IF PG_DEBUG in ('Y', 'C') THEN
3026 arp_util.debug('Validate_Reason_code()+', G_MSG_MEDIUM);
3027 END IF;
3028
3029 /*------------------------------------------+
3030 | Initialize the return status to SUCCESS |
3031 +------------------------------------------*/
3032
3033 p_return_status := FND_API.G_RET_STS_SUCCESS;
3034
3035 /*------------------------------------------+
3036 | Validate only is value is provided |
3037 +------------------------------------------*/
3038
3039 IF ( p_adj_rec.reason_code IS NOT NULL AND
3040 p_adj_rec.reason_code <> ' ' )
3041 THEN
3042
3043 l_found := FALSE ;
3044
3045 FOR l_index IN 1..G_REASON_TBL.COUNT LOOP
3046
3047 IF (p_adj_rec.reason_code = G_REASON_TBL(l_index).lookup_code)
3048 THEN
3049 l_found := TRUE ;
3050 EXIT ;
3051 END IF;
3052
3053 END LOOP;
3054
3055 IF ( NOT l_found )
3056 THEN
3057 /*-----------------------------------------------+
3058 | Set the message |
3059 +-----------------------------------------------*/
3060 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_REASON_CODE');
3061 FND_MESSAGE.SET_TOKEN ( 'REASON_CODE', p_adj_rec.reason_code ) ;
3062 FND_MSG_PUB.ADD ;
3063 p_return_status := FND_API.G_RET_STS_ERROR;
3064
3065 END IF;
3066
3067 END IF ;
3068
3069 IF PG_DEBUG in ('Y', 'C') THEN
3070 arp_util.debug('Validate_Reason_Code()-', G_MSG_MEDIUM);
3071 END IF;
3072
3073 RETURN ;
3074
3075 EXCEPTION
3076 WHEN OTHERS THEN
3077
3078 IF PG_DEBUG in ('Y', 'C') THEN
3079 arp_util.debug('EXCEPTION: Validate_Reason_code ', G_MSG_UERROR);
3080 END IF;
3081 /*-----------------------------------------------+
3082 | Set unexpected error message and status |
3083 +-----------------------------------------------*/
3084 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Reason_code' );
3085 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3089 END Validate_Reason_code;
3086
3087 RETURN;
3088
3090
3091
3092 /*===========================================================================+
3093 | PROCEDURE Validate_Desc_Flexfield |
3094 | |
3095 | DESCRIPTION |
3096 | Validates descriptive flexfields using the flex API. |
3097 | |
3098 | SCOPE - PRIVATE |
3099 | |
3100 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3101 | |
3102 | ARGUMENTS : IN: |
3103 | OUT: |
3104 | IN/ OUT: |
3105 | |
3106 | RETURNS : NONE |
3107 | |
3108 | NOTES |
3109 | This validation is currently disabled because it doesn't |
3110 | work correctly. The descriptive flexfield API functions |
3111 | that this routine uses are not yet production code and are |
3112 | unstable. |
3113 | |
3114 | MODIFICATION HISTORY |
3115 | Vivek Halder 01-JUL-97 Created |
3116 | Satheesh Nambiar 16-Jun-00 Bug 1290698. Modified Validate_Desc_Flexfield
3117 | to call arp_util.Validate_Desc_Flexfield
3118 | |
3119 +===========================================================================*/
3120
3121 PROCEDURE Validate_Desc_Flexfield(
3122 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
3123 p_return_status IN OUT NOCOPY varchar2
3124 ) IS
3125
3126 l_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%type;
3127 l_count NUMBER;
3128 l_col_name VARCHAR2(50);
3129 p_desc_flex_rec arp_util.attribute_rec_type;
3130
3131 BEGIN
3132
3133 IF PG_DEBUG in ('Y', 'C') THEN
3134 arp_util.debug('Validate_Desc_Flexfield()+', G_MSG_MEDIUM);
3135 END IF;
3136
3137 /*------------------------------------------+
3138 | Initialize the return status to SUCCESS |
3139 +------------------------------------------*/
3140
3141 p_return_status := FND_API.G_RET_STS_SUCCESS;
3142
3143 /*------------------------------------------+
3144 | Get the flexfield name |
3145 +------------------------------------------*/
3146
3147 BEGIN
3148
3149 SELECT descriptive_flexfield_name
3150 INTO l_flex_name
3151 FROM fnd_descriptive_flexs
3152 WHERE application_id = arp_global.G_AR_APP_ID AND
3153 application_table_name like 'AR_ADJUSTMENTS' ;
3154
3158
3155 EXCEPTION
3156 WHEN NO_DATA_FOUND THEN
3157 RETURN;
3159 WHEN OTHERS THEN
3160 IF PG_DEBUG in ('Y', 'C') THEN
3161 arp_util.debug('EXCEPTION: Validate_Desc_Flexfield', G_MSG_UERROR);
3162 END IF;
3163 /*-----------------------------------------------+
3164 | Set unexpected error message and status |
3165 +-----------------------------------------------*/
3166 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Desc_Flexfield' );
3167 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3168 RETURN;
3169 END ;
3170
3171 --Bug 1290698 - Validate and default flex field
3172
3173 p_desc_flex_rec.attribute_category := p_adj_rec.attribute_category;
3174 p_desc_flex_rec.attribute1 := p_adj_rec.attribute1;
3175 p_desc_flex_rec.attribute2 := p_adj_rec.attribute2;
3176 p_desc_flex_rec.attribute3 := p_adj_rec.attribute3;
3177 p_desc_flex_rec.attribute4 := p_adj_rec.attribute4;
3178 p_desc_flex_rec.attribute5 := p_adj_rec.attribute5;
3179 p_desc_flex_rec.attribute6 := p_adj_rec.attribute6;
3180 p_desc_flex_rec.attribute7 := p_adj_rec.attribute7;
3181 p_desc_flex_rec.attribute8 := p_adj_rec.attribute8;
3182 p_desc_flex_rec.attribute9 := p_adj_rec.attribute9;
3183 p_desc_flex_rec.attribute10 := p_adj_rec.attribute10;
3184 p_desc_flex_rec.attribute11 := p_adj_rec.attribute11;
3185 p_desc_flex_rec.attribute12 := p_adj_rec.attribute12;
3186 p_desc_flex_rec.attribute13 := p_adj_rec.attribute13;
3187 p_desc_flex_rec.attribute14 := p_adj_rec.attribute14;
3188 p_desc_flex_rec.attribute15 := p_adj_rec.attribute15;
3189
3190 arp_util.Validate_Desc_Flexfield(p_desc_flex_rec ,
3191 l_flex_name,
3192 p_return_status);
3193
3194 IF ( p_return_status <>FND_API.G_RET_STS_SUCCESS)
3195 THEN
3196 IF PG_DEBUG in ('Y', 'C') THEN
3197 arp_util.debug('arp_util.Validate_Desc_Flexfield - Failed', G_MSG_UERROR);
3198 END IF;
3199 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_DESC_FLEX');
3200 FND_MSG_PUB.ADD ;
3201 p_return_status := FND_API.G_RET_STS_ERROR;
3202 END IF;
3203
3204 p_adj_rec.attribute_category := p_desc_flex_rec.attribute_category;
3205 p_adj_rec.attribute1 := p_desc_flex_rec.attribute1;
3206 p_adj_rec.attribute2 := p_desc_flex_rec.attribute2;
3207 p_adj_rec.attribute3 := p_desc_flex_rec.attribute3;
3208 p_adj_rec.attribute4 := p_desc_flex_rec.attribute4;
3209 p_adj_rec.attribute5 := p_desc_flex_rec.attribute5;
3210 p_adj_rec.attribute6 := p_desc_flex_rec.attribute6;
3211 p_adj_rec.attribute7 := p_desc_flex_rec.attribute7;
3212 p_adj_rec.attribute8 := p_desc_flex_rec.attribute8;
3213 p_adj_rec.attribute9 := p_desc_flex_rec.attribute9;
3214 p_adj_rec.attribute10 := p_desc_flex_rec.attribute10;
3215 p_adj_rec.attribute11 := p_desc_flex_rec.attribute11;
3216 p_adj_rec.attribute12 := p_desc_flex_rec.attribute12;
3217 p_adj_rec.attribute13 := p_desc_flex_rec.attribute13;
3218 p_adj_rec.attribute14 := p_desc_flex_rec.attribute14;
3219 p_adj_rec.attribute15 := p_desc_flex_rec.attribute15;
3220
3221 IF PG_DEBUG in ('Y', 'C') THEN
3222 arp_util.debug('Validate_Desc_Flexfield()-', G_MSG_MEDIUM);
3223 END IF;
3224
3225 EXCEPTION
3226 WHEN OTHERS THEN
3227 IF PG_DEBUG in ('Y', 'C') THEN
3228 arp_util.debug('EXCEPTION: Validate_Desc_Flexfield', G_MSG_UERROR);
3229 END IF;
3230 /*-----------------------------------------------+
3231 | Set unexpected error message and status |
3232 +-----------------------------------------------*/
3233 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Desc_Flexfield' );
3234 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3235
3236 RETURN;
3237
3238 END Validate_Desc_Flexfield;
3239
3240 /*==========================================================================+
3241 | PROCEDURE |
3242 | Validate_Created_From |
3243 | |
3244 | DESCRIPTION |
3245 | This routine validates the Created From field of adjustment |
3246 | |
3247 | |
3248 | SCOPE - PRIVATE |
3249 | |
3250 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3251 | arp_util.disable_debug |
3252 | arp_util.enable_debug |
3253 | fnd_api.g_exc_unexpected_error |
3254 | fnd_api.g_ret_sts_error |
3255 | fnd_api.g_ret_sts_error |
3256 | fnd_api.g_ret_sts_success |
3257 | fnd_api.to_boolean |
3258 | |
3259 | ARGUMENTS : IN: |
3260 | p_adj_rec |
3261 | OUT: |
3262 | p_return_status |
3263 | |
3264 | IN/ OUT: |
3265 | |
3266 | RETURNS : NONE |
3267 | |
3268 | NOTES |
3269 | |
3270 | MODIFICATION HISTORY |
3271 | Vivek Halder 13-JUN-97 |
3272 | |
3273 +===========================================================================*/
3274
3275 PROCEDURE Validate_Created_From (
3276 p_adj_rec IN ar_adjustments%rowtype,
3277 p_return_status IN OUT NOCOPY varchar2
3278 ) IS
3279
3280 BEGIN
3281
3282 IF PG_DEBUG in ('Y', 'C') THEN
3283 arp_util.debug('Validate_Created_From()+', G_MSG_MEDIUM);
3284 END IF;
3285
3286 /*------------------------------------------+
3287 | Initialize the return status to SUCCESS |
3288 +------------------------------------------*/
3289
3290 p_return_status := FND_API.G_RET_STS_SUCCESS;
3291
3292 IF ( p_adj_rec.created_from IS NULL OR
3293 p_adj_rec.created_from = ' ' )
3294 THEN
3295 /*------------------------------------------+
3296 | Set the message |
3297 +------------------------------------------*/
3298 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_NO_CREATED_FROM');
3299 FND_MSG_PUB.ADD ;
3300 p_return_status := FND_API.G_RET_STS_ERROR;
3301 END IF;
3302
3303
3304 IF PG_DEBUG in ('Y', 'C') THEN
3305 arp_util.debug('Validate_Created_From ()-', G_MSG_MEDIUM);
3306 END IF;
3307
3308 RETURN ;
3309
3310 EXCEPTION
3311 WHEN OTHERS THEN
3312
3313 IF PG_DEBUG in ('Y', 'C') THEN
3314 arp_util.debug('EXCEPTION: Validate_Created_From()', G_MSG_UERROR);
3315 END IF;
3316 /*-----------------------------------------------+
3317 | Set unexpected error message and status |
3318 +-----------------------------------------------*/
3319 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Created_From'
3320 );
3321 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3322 RETURN;
3323
3324 END Validate_Created_From;
3325
3326 /*===========================================================================+
3327 | PROCEDURE |
3328 | Validate_Ussgl_code |
3329 | |
3330 | DESCRIPTION |
3331 | This routine validates the USSGL code of adjustment |
3332 | and also sets the context |
3333 | |
3334 | |
3335 | SCOPE - PRIVATE |
3336 | |
3337 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3338 | arp_util.disable_debug |
3339 | arp_util.enable_debug |
3340 | fnd_api.g_exc_unexpected_error |
3341 | fnd_api.g_ret_sts_error |
3342 | fnd_api.g_ret_sts_error |
3343 | fnd_api.g_ret_sts_success |
3344 | fnd_api.to_boolean |
3345 | |
3346 | ARGUMENTS : IN: |
3347 | OUT: |
3348 | p_return_status |
3349 | |
3350 | IN/ OUT: |
3351 | p_adj_rec |
3352 | |
3353 | RETURNS : NONE |
3354 | |
3355 | NOTES |
3356 | |
3357 | MODIFICATION HISTORY |
3358 | Vivek Halder 13-JUN-97 |
3359 | |
3360 +===========================================================================*/
3361
3362 PROCEDURE Validate_Ussgl_code (
3363 p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
3364 p_return_status IN OUT NOCOPY varchar2
3365 ) IS
3366
3367 l_index number;
3368
3369 BEGIN
3370
3371 IF PG_DEBUG in ('Y', 'C') THEN
3372 arp_util.debug('Validate_Ussgl_code()+', G_MSG_MEDIUM);
3373 END IF;
3374
3375 /*------------------------------------------+
3376 | Initialize the return status to ERROR |
3377 +------------------------------------------*/
3378
3379 p_return_status := FND_API.G_RET_STS_ERROR;
3380
3381 /*------------------------------------------+
3382 | Validate based on option |
3383 +------------------------------------------*/
3384
3385 IF ( g_context_rec.ussgl_option = 'Y' )
3386 THEN
3387 /*------------------------------------------+
3388 | Validate from the cache |
3389 +------------------------------------------*/
3390 FOR l_index IN 1..G_USSGL_TBL.COUNT LOOP
3391
3392 IF (p_adj_rec.ussgl_transaction_code =
3393 G_USSGL_TBL(l_index).ussgl_code)
3394 THEN
3395 p_adj_rec.ussgl_transaction_code_context :=
3396 G_USSGL_TBL(l_index).ussgl_context;
3397 p_return_status := FND_API.G_RET_STS_SUCCESS;
3398 EXIT ;
3399 END IF;
3400
3401 END LOOP;
3402
3403 IF ( p_return_status <> FND_API.G_RET_STS_SUCCESS )
3404 THEN
3405 /*-----------------------------------------------+
3406 | Set the message |
3407 +-----------------------------------------------*/
3408 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_USSGL_CODE');
3409 FND_MESSAGE.SET_TOKEN ( 'USSGL_CODE', p_adj_rec.ussgl_transaction_code ) ;
3410 FND_MSG_PUB.ADD ;
3411
3412 END IF;
3413
3414 ELSE
3415
3416 /*------------------------------------------+
3417 | No USSGL code should be provided |
3418 +------------------------------------------*/
3419 IF ( p_adj_rec.ussgl_transaction_code IS NOT NULL AND
3420 p_adj_rec.ussgl_transaction_code <> ' ' )
3421 THEN
3422 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_USSGL_CODE_DISALLOW');
3423 FND_MESSAGE.SET_TOKEN ( 'USSGL_CODE', p_adj_rec.ussgl_transaction_code ) ;
3424 FND_MSG_PUB.ADD ;
3425
3426 ELSE
3427 p_return_status := FND_API.G_RET_STS_SUCCESS;
3428 END IF ;
3429 END IF ;
3430
3431 IF PG_DEBUG in ('Y', 'C') THEN
3432 arp_util.debug('Validate_Ussgl_Code()-', G_MSG_MEDIUM);
3433 END IF;
3434
3435 RETURN ;
3436
3437 EXCEPTION
3438 WHEN OTHERS THEN
3439
3440 IF PG_DEBUG in ('Y', 'C') THEN
3441 arp_util.debug('EXCEPTION: Validate_Ussgl_code ', G_MSG_UERROR);
3442 END IF;
3443 /*-----------------------------------------------+
3444 | Set unexpected error message and status |
3445 +-----------------------------------------------*/
3446 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Validate_Ussgl_code' );
3447 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3448
3449 RETURN;
3450
3451 END Validate_Ussgl_code;
3452
3453 /*==========================================================================+
3454 | PROCEDURE |
3455 | Validate_Associated_Receipt |
3456 | |
3457 | DESCRIPTION |
3458 | This routine validates the associated cash_receipt_id |
3459 | |
3460 | |
3461 | SCOPE - PUBLIC |
3462 | |
3463 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3464 | arp_util.disable_debug |
3465 | arp_util.enable_debug |
3466 | fnd_api.g_exc_unexpected_error |
3467 | fnd_api.g_ret_sts_error |
3468 | fnd_api.g_ret_sts_error |
3469 | fnd_api.g_ret_sts_success |
3470 | fnd_api.to_boolean |
3471 | |
3472 | ARGUMENTS : IN: |
3473 | p_adj_rec |
3474 | |
3475 | OUT: |
3476 | p_return_status |
3477 | |
3478 | IN/ OUT: |
3479 | |
3480 | |
3481 | RETURNS : NONE |
3482 | |
3483 | NOTES |
3484 | |
3485 | MODIFICATION HISTORY |
3486 | Vivek Halder 13-JUN-97 |
3487 | |
3488 +===========================================================================*/
3489
3490 PROCEDURE Validate_Associated_Receipt (
3491 p_adj_rec IN ar_adjustments%rowtype,
3492 p_return_status IN OUT NOCOPY varchar2
3493 ) IS
3494
3495 l_count number;
3496
3497 BEGIN
3498
3499 IF PG_DEBUG in ('Y', 'C') THEN
3500 arp_util.debug('Validate_Associated_Receipt()+', G_MSG_MEDIUM);
3501 END IF;
3502
3503 /*------------------------------------------+
3504 | Initialize the return status to SUCCESS |
3505 +------------------------------------------*/
3506
3507 p_return_status := FND_API.G_RET_STS_SUCCESS;
3508
3509 IF ( p_adj_rec.associated_cash_receipt_id IS NOT NULL AND
3510 p_adj_rec.associated_cash_receipt_id <> 0 )
3511 THEN
3512 /*------------------------------------------+
3513 | Validate the Cash Receipt Id |
3514 +------------------------------------------*/
3515
3516 l_count := 0 ;
3517
3518 SELECT count(*)
3519 INTO l_count
3520 FROM ar_cash_receipts
3521 WHERE cash_receipt_id = p_adj_rec.associated_cash_receipt_id ;
3522
3523 IF ( l_count <> 1 )
3524 THEN
3525 /*------------------------------------------+
3526 | Set the message |
3527 +------------------------------------------*/
3528 FND_MESSAGE.SET_NAME ('AR', 'AR_AAPI_INVALID_RECEIPT_ID');
3529 FND_MESSAGE.SET_TOKEN ( 'ASSOCIATED_CASH_RECEIPT_ID', to_char(p_adj_rec.associated_cash_receipt_id) ) ;
3530 FND_MSG_PUB.ADD ;
3531
3532 p_return_status := FND_API.G_RET_STS_ERROR;
3533 END IF;
3534
3535 END IF ;
3536
3537 IF PG_DEBUG in ('Y', 'C') THEN
3538 arp_util.debug('Validate_Associated_Receipt()-', G_MSG_MEDIUM);
3539 END IF;
3540
3541 RETURN ;
3542
3543 EXCEPTION
3544 WHEN OTHERS THEN
3545
3546 IF PG_DEBUG in ('Y', 'C') THEN
3547 arp_util.debug('EXCEPTION: Validate_Associated_Receipt', G_MSG_UERROR);
3548 arp_util.debug('EXCEPTION: Validate_Associated_Receipt for Receipt Id '
3549 || p_adj_rec.associated_cash_receipt_id, G_MSG_HIGH );
3550 END IF;
3551 /*-----------------------------------------------+
3552 | Set unexpected error message and status |
3553 +-----------------------------------------------*/
3554 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Associated_Receipt');
3555 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3556 RETURN;
3557
3558 END Validate_Associated_Receipt;
3559
3560 /*==========================================================================+
3561 |PROCEDURE |
3562 | validate_inv_line_amount |
3563 | |
3564 | DESCRIPTION |
3565 | validates that the adjustment is not for more than available invoiced |
3566 | line amount. |
3567 | |
3568 | SCOPE - PRIVATE |
3569 | |
3570 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3571 | arp_util.debug |
3572 | |
3573 | ARGUMENTS : IN: |
3574 | p_adj_rec |
3575 | p_ps_rec |
3576 | OUT: p_return_status |
3577 | IN/ OUT: |
3578 | |
3579 | NOTES |
3580 | |
3581 | MODIFICATION HISTORY |
3582 | 16-MAR-12 dradhakr Created For Bug - 12635650
3583 | |
3584 +===========================================================================*/
3585
3586 PROCEDURE validate_inv_line_amount(p_adj_rec IN
3587 ar_adjustments%rowtype,
3588 p_ps_rec IN ar_payment_schedules%ROWTYPE,
3589 p_return_status IN OUT NOCOPY varchar2)
3590 IS
3591
3592 CURSOR cu_rem_amt(p_customer_trx_id IN NUMBER, p_customer_trx_line_id IN NUMBER) IS
3593 SELECT b.sum_orig sum_orig
3594 ,b.sum_acctd_orig sum_acctd_orig
3595 ,SUM((DECODE(a.activity_bucket,'ADJ_CHRG',amt
3596 ,'APP_CHRG',DECODE(a.line_type,'LINE',amt,0) * -1,0))) CHRG_ON_REV_LINE
3597 ,SUM((DECODE(a.activity_bucket,'ADJ_CHRG',acctd_amt
3598 ,'APP_CHRG',DECODE(a.line_type,'LINE',acctd_amt,0) * -1,0))) ACCTD_CHRG_ON_REV_LINE
3599 ,SUM((DECODE(a.activity_bucket,'ADJ_FRT',amt
3600 ,'APP_FRT',DECODE(a.line_type,'LINE',amt,0) * -1,0))) FRT_ON_REV_LINE
3601 ,SUM((DECODE(a.activity_bucket,'ADJ_FRT',amt
3602 ,'APP_FRT',DECODE(a.line_type,'LINE',acctd_amt,0) * -1,0))) ACCTD_FRT_ON_REV_LINE
3603 ,SUM((DECODE(a.activity_bucket,'ED_FRT',amt,0))) ED_FRT_REV_LINE
3604 ,SUM((DECODE(a.activity_bucket,'ED_FRT',acctd_amt,0))) ACCTD_ED_FRT_REV_LINE
3605 ,SUM((DECODE(a.activity_bucket,'UNED_FRT',amt,0))) UNED_FRT_REV_LINE
3606 ,SUM((DECODE(a.activity_bucket,'UNED_FRT',acctd_amt,0))) ACCTD_UNED_FRT_REV_LINE
3607 ,SUM((DECODE(a.activity_bucket,'ADJ_LINE',amt
3608 ,'APP_LINE',(amt * -1)
3609 ,'ED_LINE' ,amt
3610 ,'UNED_LINE',amt -- line
3611 ,'ADJ_TAX' ,amt
3612 ,'APP_TAX' ,(amt * -1)
3613 ,'ED_TAX' ,amt
3614 ,'UNED_TAX',amt --tax
3615 ,'APP_FRT' ,(DECODE(a.line_type,'FREIGHT',amt,0) * -1)
3616 ,'APP_CHRG',(DECODE(a.line_type,'CHARGES',amt,0) * -1)
3617 ,0))) REM_TYPE_LINE
3618 ,SUM((DECODE(a.activity_bucket,'ADJ_LINE',acctd_amt
3619 ,'APP_LINE',(acctd_amt * -1)
3620 ,'ED_LINE' ,acctd_amt
3621 ,'UNED_LINE',acctd_amt -- line
3622 ,'ADJ_TAX' ,acctd_amt
3623 ,'APP_TAX' ,(acctd_amt * -1)
3624 ,'ED_TAX' ,acctd_amt
3625 ,'UNED_TAX',acctd_amt --tax
3626 ,'APP_FRT' ,(DECODE(a.line_type,'FREIGHT',acctd_amt,0) * -1)
3627 ,'APP_CHRG',(DECODE(a.line_type,'CHARGES',acctd_amt,0) * -1)
3628 ,0))) ACCTD_REM_TYPE_LINE
3629 ,b.customer_trx_line_id CUSTOMER_TRX_LINE_ID,
3630 ctl1.line_type line_type
3631 FROM
3632 (SELECT SUM( NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0) ) amt,
3633 SUM( NVL(ard.acctd_amount_cr,0) - NVL(ard.acctd_amount_dr,0)) acctd_amt,
3634 ard.ref_customer_trx_line_id,
3635 ard.ref_account_class,
3636 ard.activity_bucket,
3637 ctl.line_type
3638 FROM ar_distributions ard,
3639 ra_customer_trx_lines ctl
3640 WHERE ctl.customer_trx_id = p_customer_trx_id
3641 AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id
3642 AND (ctl.customer_trx_line_id = p_customer_trx_line_id
3643 OR
3644 (ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
3645 AND ctl.line_type = 'TAX'
3646 )
3647 )
3648 GROUP BY
3649 ard.ref_customer_trx_line_id,
3650 ard.ref_account_class,
3651 ard.activity_bucket,
3652 ctl.line_type) a,
3653 (SELECT SUM(ctlgd.AMOUNT) sum_orig,
3654 SUM(ctlgd.ACCTD_AMOUNT) sum_acctd_orig,
3655 ctlgd.customer_trx_line_id
3656 FROM ra_cust_trx_line_gl_dist ctlgd,
3657 ra_customer_trx_lines ctll
3658 WHERE ctlgd.customer_trx_id = p_customer_trx_id
3659 AND ctlgd.customer_trx_line_id = ctll.customer_trx_line_id
3660 AND (ctll.customer_trx_line_id = p_customer_trx_line_id
3661 OR
3662 (ctll.link_to_cust_trx_line_id = p_customer_trx_line_id
3663 AND ctll.line_type = 'TAX'
3664 )
3665 )
3666 GROUP BY ctlgd.customer_trx_line_id) b,
3667 ra_customer_trx_lines ctl1
3668 WHERE a.ref_customer_trx_line_id (+) = b.customer_trx_line_id
3669 and b.customer_trx_line_id=ctl1.customer_trx_line_id
3670 GROUP BY b.customer_trx_line_id,
3671 b.sum_orig,
3672 b.sum_acctd_orig, ctl1.line_type;
3673
3674
3675 l_result VARCHAR2(1);
3676 l_term_ratio NUMBER;
3677 l_line_original NUMBER;
3678 l_sum_line_adj NUMBER;
3679 l_line_credited NUMBER;
3680 l_line_applied NUMBER;
3681 l_line_tax VARCHAR2(10);
3682 l_remain_amt NUMBER;
3683
3684
3685 BEGIN
3686
3687 arp_util.debug('ar_adjvalidate_pvt.validate_inv_line_amount()+');
3688
3689 /*----------------------------------------------------------------+
3690 | IF the line number is filled in |
3691 | THEN validate adjustment is not more than available invoiced |
3692 | line amount |
3693 +----------------------------------------------------------------*/
3694 p_return_status := FND_API.G_RET_STS_SUCCESS;
3695
3696 IF ( p_adj_rec.customer_trx_line_id IS NOT NULL )
3697 THEN
3698
3699 l_remain_amt := 0;
3700 l_line_original:= 0;
3701
3702 FOR i IN cu_rem_amt(p_adj_rec.customer_trx_id, p_adj_rec.customer_trx_line_id) LOOP
3703 IF PG_DEBUG in ('Y', 'C') THEN
3704 arp_util.debug('Value of l_remain_amt: ' || l_remain_amt);
3705 arp_util.debug('Value of sum_orig: ' || i.sum_orig);
3706 arp_util.debug('Value of REM_TYPE_LINE: ' || i.REM_TYPE_LINE);
3707 arp_util.debug('Value of l_line_original: ' || l_line_original);
3708 END IF;
3709 l_remain_amt := l_remain_amt + i.sum_orig + i.REM_TYPE_LINE;
3710 l_line_original := l_line_original + i.sum_orig;
3711 END LOOP;
3712
3713 IF PG_DEBUG in ('Y', 'C') THEN
3714 arp_util.debug('Value of l_remain_amt: ' || l_remain_amt);
3715 arp_util.debug('Value of l_line_original: ' || l_line_original);
3716 END IF;
3717
3718
3719 IF (l_line_original > 0) THEN
3720 IF (
3721 p_adj_rec.amount +
3722 l_remain_amt < 0
3723 )
3724 THEN
3725 arp_util.debug( 'EXCEPTION: ar_adjvalidate_pvt.' ||
3726 'validate_inv_line_amount ()');
3727 arp_util.debug( 'Adjustments cannot be more than available ' ||
3728 'invoiced line amount.');
3729 p_return_status := FND_API.G_RET_STS_ERROR;
3730 FND_MESSAGE.SET_NAME('AR','AR_VAL_ADJ_INV_LINE_AMT');
3731 FND_MSG_PUB.Add;
3732 END IF;
3733 ELSIF (l_line_original < 0) THEN
3734 IF (
3735 p_adj_rec.amount +
3736 l_remain_amt > 0
3737 )
3738 THEN
3739 arp_util.debug( 'EXCEPTION: ar_adjvalidate_pvt.' ||
3740 'validate_inv_line_amount ()');
3741 arp_util.debug( 'Adjustments cannot be more than available ' ||
3742 'invoiced line amount.');
3743 p_return_status := FND_API.G_RET_STS_ERROR;
3744 FND_MESSAGE.SET_NAME('AR','AR_VAL_ADJ_INV_LINE_AMT');
3745 FND_MSG_PUB.Add;
3746 END IF;
3747 END IF;
3748
3749 END IF; -- end not approved or adjusted and line specified case
3750
3751
3752 arp_util.debug('ar_adjvalidate_pvt.validate_inv_line_amount()-');
3753
3754 EXCEPTION
3755 WHEN OTHERS THEN
3756 arp_util.debug('EXCEPTION: ar_adjvalidate_pvt.' ||
3757 'validate_inv_line_amount()');
3758 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'validate_inv_line_amount()');
3759 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3760 RETURN;
3761
3762 END validate_inv_line_amount;
3763
3764 /*==========================================================================+
3765 | PROCEDURE |
3766 | Validate_Over_Application |
3767 | |
3768 | DESCRIPTION |
3769 | This routine validates the whether the adjustment is over |
3770 | applying the transaction. |
3771 | |
3772 | |
3773 | SCOPE - PUBLIC |
3774 | |
3775 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3776 | arp_util.disable_debug |
3777 | fnd_api.g_exc_unexpected_error |
3778 | fnd_api.g_ret_sts_error |
3779 | fnd_api.g_ret_sts_error |
3780 | fnd_api.g_ret_sts_success |
3781 | fnd_api.to_boolean |
3782 | |
3783 | ARGUMENTS : IN: |
3784 | p_adj_rec |
3785 | p_ps_rec |
3786 | |
3787 | OUT: |
3788 | p_return_status |
3789 | |
3790 | IN/ OUT: |
3791 | |
3792 | |
3793 | RETURNS : NONE |
3794 | |
3795 | NOTES |
3796 | |
3797 | MODIFICATION HISTORY |
3798 | JASSING 17-AUG-04 Bug 3766262 |
3799 | |
3800 +===========================================================================*/
3801
3802 PROCEDURE Validate_Over_Application (
3803 p_adj_rec IN ar_adjustments%rowtype,
3804 p_ps_rec IN ar_payment_schedules%rowtype,
3805 p_return_status IN OUT NOCOPY varchar2
3806 ) IS
3807
3808 l_creation_sign varchar2(30);
3809 l_allow_overapp_flag varchar2(1);
3810 l_type_adr number;
3811 l_type_ado number;
3812 l_type_adj_amount number;
3813 l_message_name varchar2(50);
3814 BEGIN
3815
3816 IF PG_DEBUG in ('Y', 'C') THEN
3817 arp_util.debug('Validate_Over_Application()+', G_MSG_MEDIUM);
3818 END IF;
3819
3820 p_return_status := FND_API.G_RET_STS_SUCCESS;
3821
3822
3823 IF ( p_adj_rec.type = 'CHARGES' )
3824 THEN
3825 l_type_adr := p_ps_rec.amount_due_remaining;
3826 l_type_ado := p_ps_rec.amount_due_original;
3827 l_type_adj_amount := p_adj_rec.receivables_charges_adjusted;
3828 ELSIF ( p_adj_rec.type = 'FREIGHT' )
3829 THEN
3830 l_type_adr := p_ps_rec.freight_remaining;
3831 l_type_ado := p_ps_rec.freight_original;
3832 l_type_adj_amount := p_adj_rec.amount;
3833 ELSIF ( p_adj_rec.type = 'LINE' ) -- Modified for Bug 12635650
3834 THEN
3835 IF(p_adj_rec.customer_trx_line_id IS NOT NULL) THEN
3836 -- Bug 14009039.
3837 -- validate_inv_line_amount(p_adj_rec,p_ps_rec,p_return_status);
3838 ARP_PROCESS_ADJUSTMENT.validate_inv_line_amount_cover(p_adj_rec.customer_trx_line_id, p_adj_rec.customer_trx_id, p_adj_rec.payment_schedule_id, p_adj_rec.amount, p_adj_rec.receivables_trx_id);
3839 RETURN;
3840 ELSE
3841 l_type_adr := p_ps_rec.amount_line_items_remaining;
3842 l_type_ado := p_ps_rec.amount_line_items_original;
3843 l_type_adj_amount := p_adj_rec.line_adjusted;
3844 END IF;
3845 ELSIF ( p_adj_rec.type = 'TAX' )
3846 THEN
3847 l_type_adr := p_ps_rec.tax_remaining;
3848 l_type_ado := p_ps_rec.tax_original;
3849 l_type_adj_amount := p_adj_rec.amount;
3850 ELSIF ( p_adj_rec.type = 'INVOICE' )
3851 THEN
3852 l_type_adr := p_ps_rec.amount_due_remaining;
3853 l_type_ado := p_ps_rec.amount_due_original;
3854 END IF;
3855
3856
3857 SELECT creation_sign,
3858 allow_overapplication_flag
3859 INTO l_creation_sign,
3860 l_allow_overapp_flag
3861 FROM ra_cust_trx_types
3862 WHERE cust_trx_type_id = p_ps_rec.cust_trx_type_id;
3863
3864
3865
3866 IF ( p_adj_rec.type = 'INVOICE' )
3867 THEN
3868
3869 /*----------------------------------------------------------+
3870 | Invoice type adjustment must make the balance due zero |
3871 +----------------------------------------------------------*/
3872 /*This is validated while validating the amount, so not needed
3873 out here*/
3874
3875 NULL;
3876 /*
3877 IF ( l_type_adr + p_adj_rec.amount <> 0 )
3878 THEN
3879 p_return_status := FND_API.G_RET_STS_ERROR;
3880 FND_MESSAGE.SET_NAME('AR','AR_TW_VAL_AMT_ADJ_INV');
3881 FND_MSG_PUB.Add;
3882 END IF;*/
3883
3884
3885 ELSE
3886
3887 /*----------------------------------------------------------+
3888 | Check for overapplication based on the adjustment type |
3889 +----------------------------------------------------------*/
3890
3891 arp_non_db_pkg.check_natural_application(
3892 p_creation_sign => l_creation_sign,
3893 p_allow_overapplication_flag=> l_allow_overapp_flag,
3894 p_natural_app_only_flag => 'N',
3895 p_sign_of_ps => '+',
3896 p_chk_overapp_if_zero => null,
3897 p_payment_amount => l_type_adj_amount,
3898 p_discount_taken => 0,
3899 p_amount_due_remaining => nvl(l_type_adr,0),
3900 p_amount_due_original => nvl(l_type_ado,0),
3901 event => 'WHEN-VALIDATE-ITEM',
3902 p_message_name => l_message_name);
3903
3904
3905 IF ( l_message_name IS NOT NULL)
3906 THEN
3907 p_return_status := FND_API.G_RET_STS_ERROR;
3908 FND_MESSAGE.SET_NAME('AR',l_message_name);
3909 FND_MSG_PUB.Add;
3910 END IF;
3911
3912
3913 /*------------------------------------+
3914 | Check for overapplication of tax |
3915 +------------------------------------*/
3916
3917 IF p_adj_rec.type in ('CHARGES', 'LINE') and
3918 nvl(p_adj_rec.tax_adjusted,0) <> 0 THEN
3919 arp_non_db_pkg.check_natural_application(
3920 p_creation_sign => l_creation_sign,
3921 p_allow_overapplication_flag=> l_allow_overapp_flag,
3922 p_natural_app_only_flag => 'N',
3923 p_sign_of_ps => '+',
3924 p_chk_overapp_if_zero => null,
3925 p_payment_amount => p_adj_rec.tax_adjusted,
3926 p_discount_taken => 0,
3927 p_amount_due_remaining => nvl(p_ps_rec.tax_remaining,0),
3928 p_amount_due_original => nvl(p_ps_rec.tax_original,0),
3929 event => 'WHEN-VALIDATE-ITEM',
3930 p_message_name => l_message_name);
3931
3932 IF ( l_message_name IS NOT NULL)
3933 THEN
3934 p_return_status := FND_API.G_RET_STS_ERROR;
3935 FND_MESSAGE.SET_NAME('AR',l_message_name);
3936 FND_MSG_PUB.Add;
3937 END IF;
3938
3939 END IF;
3940
3941 END IF;
3942
3943 RETURN;
3944
3945 EXCEPTION
3946 WHEN OTHERS THEN
3947 IF PG_DEBUG in ('Y', 'C') THEN
3948 arp_util.debug('EXCEPTION: Validate_Over_Application', G_MSG_UERROR);
3949 arp_util.debug('EXCEPTION: Validate_Over_Application '
3950 || p_adj_rec.customer_trx_id, G_MSG_HIGH );
3951 END IF;
3952 /*-----------------------------------------------+
3953 | Set unexpected error message and status |
3954 +-----------------------------------------------*/
3955 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Over_Application');
3956 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3957 RETURN;
3958 END Validate_Over_Application;
3959
3960
3961 /*==========================================================================+
3962 | PROCEDURE |
3963 | Validate_Over_Application_llca |
3964 | |
3965 | DESCRIPTION |
3966 | This routine validates the whether the line adjustment |
3967 | is over applying the transaction. |
3968 | |
3969 | |
3970 | SCOPE - PUBLIC |
3971 | |
3972 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
3973 | arp_util.disable_debug |
3974 | fnd_api.g_exc_unexpected_error |
3975 | fnd_api.g_ret_sts_error |
3976 | fnd_api.g_ret_sts_error |
3977 | fnd_api.g_ret_sts_success |
3978 | fnd_api.to_boolean |
3979 | |
3980 | ARGUMENTS : IN: |
3981 | p_adj_rec |
3982 | |
3983 | |
3984 | OUT: |
3985 | p_return_status |
3986 | |
3987 | IN/ OUT: |
3988 | |
3989 | |
3990 | RETURNS : NONE |
3991 | |
3992 | NOTES |
3993 | |
3994 | MODIFICATION HISTORY |
3995 | mpsingh 05-Feb-2008 CREATED |
3996 | |
3997 +===========================================================================*/
3998
3999 PROCEDURE Validate_Over_Application_llca (
4000 p_adj_rec IN ar_adjustments%rowtype,
4001 p_ps_rec IN ar_payment_schedules%rowtype,
4002 p_return_status IN OUT NOCOPY varchar2
4003 ) IS
4004
4005 l_creation_sign varchar2(30);
4006 l_allow_overapp_flag varchar2(1);
4007 l_type_adj_amount number;
4008 l_message_name varchar2(50);
4009 l_line_remaining number;
4010 l_tax_remaining number;
4011 l_line_org number;
4012 l_tax_org number;
4013 l_invoice_currency_code varchar2(30);
4014 l_customer_trx_id number;
4015 l_customer_trx_line_id number;
4016 l_receivables_trx_id number;
4017
4018
4019 BEGIN
4020
4021 IF PG_DEBUG in ('Y', 'C') THEN
4022 arp_util.debug('Validate_Over_Application_llca()+', G_MSG_MEDIUM);
4023 END IF;
4024
4025 p_return_status := FND_API.G_RET_STS_SUCCESS;
4026
4027 l_type_adj_amount := p_adj_rec.amount;
4028 l_customer_trx_id := p_adj_rec.customer_trx_id;
4029 l_customer_trx_line_id := p_adj_rec.customer_trx_line_id;
4030 l_receivables_trx_id := p_adj_rec.receivables_trx_id;
4031
4032
4033
4034 SELECT creation_sign,
4035 allow_overapplication_flag
4036 INTO l_creation_sign,
4037 l_allow_overapp_flag
4038 FROM ra_cust_trx_types
4039 WHERE cust_trx_type_id = p_ps_rec.cust_trx_type_id;
4040
4041
4042 -- As per LLCA design, overapplication not allowed at Line level
4043 l_allow_overapp_flag := 'N';
4044
4045
4046 SELECT sum(DECODE (lines.line_type,
4047 'TAX',0,
4048 'FREIGHT',0 , 1) *
4049 DECODE(ct.complete_flag, 'N',
4050 0, lines.amount_due_remaining)), -- line adr
4051 sum(DECODE (lines.line_type,
4052 'TAX',1,0) *
4053 DECODE(ct.complete_flag,
4054 'N', 0,
4055 lines.amount_due_remaining )), -- tax adr
4056 sum(DECODE (lines.line_type,
4057 'TAX',0,
4058 'FREIGHT',0 , 1) *
4059 DECODE(ct.complete_flag, 'N',
4060 0, lines.amount_due_original)), -- line adr org
4061 sum(DECODE (lines.line_type,
4062 'TAX',1,0) *
4063 DECODE(ct.complete_flag,
4064 'N', 0,
4065 lines.amount_due_original)), -- tax adr org
4066 max(ct.invoice_currency_code) -- curr code
4067 INTO l_line_remaining,
4068 l_tax_remaining,
4069 l_line_org,
4070 l_tax_org,
4071 l_invoice_currency_code
4072 FROM ra_customer_trx ct,
4073 ra_customer_trx_lines lines
4074 WHERE (lines.customer_Trx_line_id = p_adj_rec.customer_trx_line_id or
4075 lines.link_to_cust_trx_line_id = p_adj_rec.customer_trx_line_id)
4076 AND ct.customer_Trx_id = lines.customer_trx_id;
4077
4078
4079
4080 /*----------------------------------------------------------+
4081 | Check for overapplication based on the adjustment type |
4082 +----------------------------------------------------------*/
4083
4084 arp_non_db_pkg.check_natural_application(
4085 p_creation_sign => l_creation_sign,
4086 p_allow_overapplication_flag=> l_allow_overapp_flag,
4087 p_natural_app_only_flag => 'N',
4088 p_sign_of_ps => '+',
4089 p_chk_overapp_if_zero => null,
4090 p_payment_amount => l_type_adj_amount,
4091 p_discount_taken => 0,
4092 p_amount_due_remaining => nvl(l_line_remaining,0),
4093 p_amount_due_original => nvl(l_line_org,0),
4094 event => 'WHEN-VALIDATE-ITEM',
4095 p_message_name => l_message_name);
4096
4097
4098 IF ( l_message_name IS NOT NULL)
4099 THEN
4100 insert into ar_llca_adj_trx_errors_gt
4101 (
4102 customer_trx_id,
4103 customer_trx_line_id,
4104 receivables_trx_id,
4105 error_message,
4106 invalid_value
4107 )
4108 values
4109 (
4110 l_customer_trx_id,
4111 l_customer_trx_line_id,
4112 l_receivables_trx_id,
4113 l_message_name,
4114 'Overapplication'
4115 );
4116 END IF;
4117
4118
4119 /*------------------------------------+
4120 | Check for overapplication of tax |
4121 +------------------------------------*/
4122
4123 IF p_adj_rec.type in ('LINE') and
4124 nvl(p_adj_rec.tax_adjusted,0) <> 0 THEN
4125 arp_non_db_pkg.check_natural_application(
4126 p_creation_sign => l_creation_sign,
4127 p_allow_overapplication_flag=> l_allow_overapp_flag,
4128 p_natural_app_only_flag => 'N',
4129 p_sign_of_ps => '+',
4130 p_chk_overapp_if_zero => null,
4131 p_payment_amount => p_adj_rec.tax_adjusted,
4132 p_discount_taken => 0,
4133 p_amount_due_remaining => nvl(l_tax_remaining,0),
4134 p_amount_due_original => nvl(l_tax_org,0),
4135 event => 'WHEN-VALIDATE-ITEM',
4136 p_message_name => l_message_name);
4137
4138 IF ( l_message_name IS NOT NULL)
4139 THEN
4140 insert into ar_llca_adj_trx_errors_gt
4141 (
4142 customer_trx_id,
4143 customer_trx_line_id,
4144 receivables_trx_id,
4145 error_message,
4146 invalid_value
4147 )
4148 values
4149 (
4150 l_customer_trx_id,
4151 l_customer_trx_line_id,
4152 l_receivables_trx_id,
4153 l_message_name,
4154 'Overapplication'
4155 );
4156 END IF;
4157
4158 END IF;
4159 RETURN;
4160
4161 EXCEPTION
4162 WHEN OTHERS THEN
4163 IF PG_DEBUG in ('Y', 'C') THEN
4164 arp_util.debug('EXCEPTION: Validate_Over_Application_llca', G_MSG_UERROR);
4165 arp_util.debug('EXCEPTION: Validate_Over_Application_llca '
4166 || p_adj_rec.customer_trx_id, G_MSG_HIGH );
4167 END IF;
4168 /*-----------------------------------------------+
4169 | Set unexpected error message and status |
4170 +-----------------------------------------------*/
4171 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,'Validate_Over_Application_llca');
4172 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4173 RETURN;
4174 END Validate_Over_Application_llca;
4175
4176 BEGIN
4177 arp_util.debug('initialization section of ar_adjvalidate_pvt');
4178 G_cache_org_id := -99999;
4179
4180 END AR_ADJVALIDATE_PVT;