[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_VALIDATE
Source
1 PACKAGE BODY ARP_TRX_VALIDATE AS
2 /* $Header: ARTUVALB.pls 120.27.12010000.1 2008/07/24 16:58:23 appldev ship $ */
3
4 pg_ai_pds_exist_cursor integer;
5 pg_ai_overlapping_pds_cursor integer;
6 pg_form_pds_exist_cursor integer;
7 pg_form_overlapping_pds_cursor integer;
8
9 pg_salesrep_required_flag ar_system_parameters.salesrep_required_flag%type;
10 pg_set_of_books_id ar_system_parameters.set_of_books_id%type;
11 pg_base_curr_code gl_sets_of_books.currency_code%type;
12
13
14
15
16 /*===========================================================================+
17 | PROCEDURE |
18 | add_to_error_list() |
19 | |
20 | DESCRIPTION |
21 | Adds a message to the error list. |
22 | Currently, this function just puts the message on the stack and raises |
23 | an exception. Later, however, it will put the messages on a stack so |
24 | that we can display multiple messages at a time. |
25 | |
26 | SCOPE - PRIVATE |
27 | |
28 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
29 | arp_util.debug |
30 | |
31 | ARGUMENTS : IN: |
32 | p_mode |
33 | p_customer_trx_id |
34 | p_trx_number |
35 | p_line_number |
36 | p_other_line_number |
37 | p_message_name |
38 | p_error_location |
39 | p_token_name_1 |
40 | p_token_1 |
41 | p_token_name_2 |
42 | p_token_2 |
43 | |
44 | OUT: |
45 | None |
46 | |
47 | IN/OUT: |
48 | p_error_count |
49 | |
50 | RETURNS : NONE |
51 | |
52 | NOTES |
53 | |
54 | MODIFICATION HISTORY |
55 | 07-DEC-95 Charlie Tomberg Created |
56 | 21-Aug-97 Mahesh Sabapathy Default p_error_count to 0 when passed |
57 | in as NULL. |
58 | |
59 +===========================================================================*/
60
61 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
62
63 PROCEDURE add_to_error_list(
64 p_mode IN VARCHAR2,
65 p_error_count IN OUT NOCOPY INTEGER,
66 p_customer_trx_id IN NUMBER,
67 p_trx_number IN VARCHAR2,
68 p_line_number IN NUMBER,
69 p_other_line_number IN NUMBER,
70 p_message_name IN VARCHAR2,
71 p_error_location IN varchar2 DEFAULT NULL,
72 p_token_name_1 IN varchar2 DEFAULT NULL,
73 p_token_1 IN varchar2 DEFAULT NULL,
74 p_token_name_2 IN varchar2 DEFAULT NULL,
75 p_token_2 IN varchar2 DEFAULT NULL,
76 p_line_index IN NUMBER DEFAULT NULL,
77 p_tax_index IN NUMBER DEFAULT NULL,
78 p_freight_index IN NUMBER DEFAULT NULL,
79 p_salescredit_index IN NUMBER DEFAULT NULL
80 ) IS
81
82 l_new_index BINARY_INTEGER;
83
84 BEGIN
85
86 IF PG_DEBUG in ('Y', 'C') THEN
87 arp_util.debug('arp_trx_validate.add_to_error_list()+');
88 END IF;
89
90 p_error_count := nvl(p_error_count,0) + 1;
91
92 /*---------------------------------------------+
93 | Write error information to the debug pipe |
94 +---------------------------------------------*/
95
96 IF PG_DEBUG in ('Y', 'C') THEN
97 arp_util.debug('p_mode = ' || p_mode);
98 arp_util.debug('adding: ctid: ' || p_customer_trx_id ||
99 ' trx_number: ' || p_trx_number ||
100 ' line: ' || TO_CHAR(p_line_number) || ',' ||
101 TO_CHAR(p_other_line_number) );
102 arp_util.debug(' msg: ' || p_message_name || ' token 1 ' || p_token_1 ||
103 ' token 2 ' || p_token_2);
104 arp_util.debug('error location: ' || p_error_location );
105 END IF;
106
107 /*---------------------+
108 | Process the error |
109 +---------------------*/
110
111 fnd_message.set_name('AR', p_message_name);
112
113 IF (p_token_1 IS NOT NULL )
114 THEN
115 fnd_message.set_token(p_token_name_1,
116 p_token_1);
117 END IF;
118
119 IF (p_token_2 IS NOT NULL )
120 THEN
121 fnd_message.set_token(p_token_name_2,
122 p_token_2);
123 END IF;
124
125 -- bug 2415895 : include p_mode = STANDARD, to ensure messages are loaded to message table
126
127 -- Bug 2545343 : Reverted the fix for bug 2415895 to display proper error message
128
129 IF (p_mode in ('PL/SQL'))
130 THEN
131 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_ERROR ) )
132 THEN
133 fnd_message.set_name('AR', p_message_name);
134
135 FND_MSG_PUB.Add;
136
137 IF (p_token_1 IS NOT NULL )
138 THEN
139 fnd_message.set_token(p_token_name_1,
140 p_token_1);
141 END IF;
142
143 IF (p_token_2 IS NOT NULL )
144 THEN
145 fnd_message.set_token(p_token_name_2,
146 p_token_2);
147 END IF;
148
149 END IF;
150
151 l_new_index := NVL(pg_Message_Tbl.last, 0) + 1;
152
153 pg_Message_Tbl( l_new_index ).customer_trx_id := p_customer_trx_id;
154 pg_Message_Tbl( l_new_index ).line_number := p_line_number;
155 pg_Message_Tbl( l_new_index ).other_line_number :=
156 p_other_line_number;
157
158 pg_Message_Tbl( l_new_index ).line_index := p_line_index;
159 pg_Message_Tbl( l_new_index ).tax_index := p_tax_index;
160 pg_Message_Tbl( l_new_index ).freight_index := p_freight_index;
161 pg_Message_Tbl( l_new_index ).salescredit_index := p_salescredit_index;
162
163 pg_Message_Tbl( l_new_index ).message_name := p_message_name;
164 pg_Message_Tbl( l_new_index ).token_name_1 := p_token_name_1;
165 pg_Message_Tbl( l_new_index ).token_1 := p_token_1;
166 pg_Message_Tbl( l_new_index ).token_name_2 := p_token_name_2;
167 pg_Message_Tbl( l_new_index ).token_2 := p_token_2;
168 pg_Message_Tbl( l_new_index ).encoded_message :=
169 fnd_message.get_encoded;
170
171 fnd_message.set_name('AR', p_message_name);
172
173 IF (p_token_1 IS NOT NULL )
174 THEN
175 fnd_message.set_token(p_token_name_1,
176 p_token_1);
177 END IF;
178
179 IF (p_token_2 IS NOT NULL )
180 THEN
181 fnd_message.set_token(p_token_name_2,
182 p_token_2);
183 END IF;
184
185 pg_Message_Tbl( l_new_index ).translated_message := fnd_message.get;
186
187 ELSE
188
189 IF (p_mode <> 'NO_EXCEPTION')
190 THEN
191 app_exception.raise_exception;
192 END IF;
193 END IF;
194
195
196 IF PG_DEBUG in ('Y', 'C') THEN
197 arp_util.debug('arp_trx_validate.add_to_error_list()-');
198 END IF;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 IF PG_DEBUG in ('Y', 'C') THEN
203 arp_util.debug('add_to_error_list: ' ||
204 'EXCEPTION: arp_trx_validate.add_to_error_list()');
205 END IF;
206 RAISE;
207
208 END add_to_error_list;
209
210
211 /*===========================================================================+
212 | PROCEDURE |
213 | ar_entity_version_check() |
214 | |
215 | DESCRIPTION |
216 | |
217 | SCOPE - PRIVATE |
218 | |
219 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
220 | arp_util.debug |
221 | |
222 | ARGUMENTS : IN: |
223 | p_form_name |
224 | p_form_version |
225 | OUT: |
226 | None |
227 | |
228 | RETURNS : NONE |
229 | |
230 | NOTES |
231 | |
232 | MODIFICATION HISTORY |
233 | 05-JUL-95 Charlie Tomberg Created |
234 | |
235 +===========================================================================*/
236
237
238 PROCEDURE ar_entity_version_check(p_form_name IN varchar2,
239 p_form_version IN number) IS
240
241
242 BEGIN
243
244 arp_util.debug('ARP_TRX_VALIDATE.ar_entity_version_check()+');
245
246 arp_util.debug('ARP_TRX_VALIDATE.ar_entity_version_check()-');
247
248 EXCEPTION
249 WHEN OTHERS THEN
250 arp_util.debug('EXCEPTION: ARP_TRX_VALIDATE.insert_batch()');
251 RAISE;
252
253 END;
254
255 /*===========================================================================+
256 | PROCEDURE |
257 | validate_trx_number() |
258 | |
259 | DESCRIPTION |
260 | |
261 | SCOPE - PUBLIC |
262 | |
263 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
264 | arp_util.debug |
265 | |
266 | ARGUMENTS : IN: |
267 | p_batch_source_id |
268 | p_trx_number |
269 | p_customer_trx_id |
270 | OUT: |
271 | None |
272 | |
273 | RETURNS : NONE |
274 | |
275 | NOTES |
276 | |
277 | MODIFICATION HISTORY |
278 | 30-OCT-95 Charlie Tomberg Created |
279 | 05-AUG-05 M Raymond 4537055 - Added support for batch
280 | source flag
281 | allow_duplicate_trx_num_flag'
282 +===========================================================================*/
283
284 PROCEDURE validate_trx_number( p_batch_source_id IN NUMBER,
285 p_trx_number IN VARCHAR2,
286 p_customer_trx_id IN NUMBER) IS
287
288 -- get the flags for the batchsource
289 CURSOR flags IS
290 SELECT NVL(copy_doc_number_flag, 'N'),
291 NVL(allow_duplicate_trx_num_flag, 'N')
292 FROM ra_batch_sources
293 WHERE batch_source_id = p_batch_source_id;
294
295 -- check if the transaction number exists already
296 CURSOR duptrx IS
297 SELECT 'Y' -- already exists in the transaction table
298 FROM ra_customer_trx
299 WHERE batch_source_id = p_batch_source_id
300 AND trx_number = p_trx_number
301 AND customer_trx_id <> NVL(p_customer_trx_id, -99)
302 UNION
303 SELECT 'Y' -- already exists in the interim table
304 FROM ra_recur_interim ri,
305 ra_customer_trx ct
306 WHERE ct.customer_trx_id = ri.customer_trx_id
307 AND ct.batch_source_id = p_batch_source_id
308 AND ri.trx_number = p_trx_number
309 AND NVL(ri.new_customer_trx_id, -98) <> NVL(p_customer_trx_id, -99)
310 UNION
311 SELECT 'Y' -- already exists in the interface table
312 FROM ra_batch_sources bs,
313 ra_interface_lines ril
314 WHERE ril.batch_source_name = bs.name
315 AND bs.batch_source_id = p_batch_source_id
316 AND ril.trx_number = p_trx_number
317 AND ril.customer_trx_id <> NVL(p_customer_trx_id, -99);
318
319 l_temp VARCHAR2(1);
320 l_copy_doc_num_flag ra_batch_sources_all.copy_doc_number_flag%TYPE;
321 l_allow_duplicate_flag
322 ra_batch_sources_all.allow_duplicate_trx_num_flag%TYPE;
323
324 BEGIN
325
326 arp_util.debug('arp_trx_validate.validate_trx_number()+');
327
328 /* Bug 2681166 Re-introduced the code commented out for bug 2493165 */
329
330 /* Bug 2493165 Removed the following Document Sequencing changes
331 to check for the copy_doc_number_flag before validating the
332 Trx_Number . */
333
334 /* Document sequencing changes: Check the copy document number flag
335 in batch source. Validate the transaction number within batch
336 source only if this flag is No */
337
338 -- get the flags from the batch source.
339
340 OPEN flags;
341 FETCH flags INTO l_copy_doc_num_flag, l_allow_duplicate_flag;
342 CLOSE flags;
343
344 IF (l_copy_doc_num_flag = 'N' AND l_allow_duplicate_flag = 'N') THEN
345
346 IF (p_batch_source_id IS NOT NULL AND p_trx_number IS NOT NULL) THEN
347
348 -- the fact that we got here means that duplicate trx numbers
349 -- are not allowed.
350
351 OPEN duptrx;
352 FETCH duptrx INTO l_temp;
353
354 IF duptrx%FOUND THEN
355 -- a transaction number with the same value already exists,
356 -- so disallow the creation of another with the same name.
357 fnd_message.set_name('AR', 'AR_TW_INVALID_TRX_NUMBER');
358 app_exception.raise_exception;
359 END IF;
360
361 CLOSE duptrx;
362
363 END IF;
364
365 END IF;
366 arp_util.debug('ARP_TRX_VALIDATE.validate_trx_number()-');
367
368 EXCEPTION
369 WHEN OTHERS THEN
370 arp_util.debug('EXCEPTION: arp_trx_validate.validate_trx_number()');
371 RAISE;
372
373 END validate_trx_number;
374
375 /*===========================================================================+
376 | PROCEDURE |
377 | validate_doc_number() |
378 | |
379 | DESCRIPTION |
380 | |
381 | SCOPE - PUBLIC |
382 | |
383 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
384 | arp_util.debug |
385 | |
386 | ARGUMENTS : IN: |
387 | p_cust_trx_type_id |
388 | p_doc_sequence_value |
389 | p_customer_trx_id |
390 | OUT: |
391 | None |
392 | |
393 | RETURNS : NONE |
394 | |
395 | NOTES |
396 | |
397 | MODIFICATION HISTORY |
398 | 02-JUL-96 Charlie Tomberg Created |
399 | |
400 +===========================================================================*/
401
402
403 PROCEDURE validate_doc_number( p_cust_trx_type_id IN NUMBER,
404 p_doc_sequence_value IN NUMBER,
405 p_customer_trx_id IN NUMBER )
406 IS
407
408 l_temp varchar2(1);
409
410 BEGIN
411
412 arp_util.debug('ARP_TRX_VALIDATE.validate_doc_number()+');
413
414 BEGIN
415
416 IF (
417 p_cust_trx_type_id IS NOT NULL
418 AND
419 p_doc_sequence_value IS NOT NULL
420 )
421 THEN
422 SELECT 'Y' --already exists
423 INTO l_temp
424 FROM ra_recur_interim ri,
425 ra_customer_trx ct
426 WHERE ct.customer_trx_id = ri.customer_trx_id
427 AND ct.cust_trx_type_id = p_cust_trx_type_id
428 AND ri.doc_sequence_value = p_doc_sequence_value
429 AND NVL(ri.new_customer_trx_id, -98)
430 <> NVL(p_customer_trx_id, -99)
431 UNION
432 SELECT 'Y'
433 FROM ra_cust_trx_types ctt,
434 ra_interface_lines ril
435 WHERE ril.cust_trx_type_name = ctt.name(+)
436 AND NVL(ril.cust_trx_type_id,
437 ctt.cust_trx_type_id) = p_cust_trx_type_id
438 AND ril.document_number = p_doc_sequence_value
439 AND ril.customer_trx_id <> NVL(p_customer_trx_id, -99);
440
441 fnd_message.set_name('FND', 'UNIQUE-DUPLICATE SEQUENCE');
442 app_exception.raise_exception;
443
444 END IF;
445
446 EXCEPTION
447 WHEN NO_DATA_FOUND
448 THEN NULL;
449 WHEN OTHERS THEN RAISE;
450 END;
451
452 arp_util.debug('ARP_TRX_VALIDATE.validate_doc_number()-');
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 arp_util.debug('EXCEPTION: ARP_TRX_VALIDATE.validate_doc_number()');
457 RAISE;
458
459 END;
460
461 /*===========================================================================+
462 | PROCEDURE |
463 | check_dup_line_number |
464 | |
465 | DESCRIPTION |
466 | Checks to see if a line number has already been used on a particular |
467 | transaction. |
468 | |
469 | SCOPE - PUBLIC |
470 | |
471 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
472 | arp_util.debug |
473 | |
474 | ARGUMENTS : IN: |
475 | p_line_number |
476 | p_customer_trx_id |
477 | p_customer_trx_line_id |
478 | OUT: |
479 | None |
480 | |
481 | RETURNS : NONE |
482 | |
483 | NOTES |
484 | |
485 | MODIFICATION HISTORY |
486 | 24-JUL-95 Charlie Tomberg Created |
487 | 14-DEC-95 Martin Johnson Changed message name |
488 | |
489 +===========================================================================*/
490
491
492 PROCEDURE check_dup_line_number( p_line_number IN NUMBER,
493 p_customer_trx_id IN NUMBER,
494 p_customer_trx_line_id IN NUMBER)
495 IS
496
497 l_count number;
498
499 BEGIN
500
501 arp_util.debug('arp_trx_validate.check_dup_line_number()+');
502
503 SELECT count(*)
504 INTO l_count
505 FROM ra_customer_trx_lines
506 WHERE customer_trx_id = p_customer_trx_id
507 AND line_number = p_line_number
508 AND line_type = 'LINE'
509 AND customer_trx_line_id <> nvl(p_customer_trx_line_id, -100);
510
511 IF (l_count > 0)
512 THEN
513 fnd_message.set_name('AR', 'AR_TW_DUP_LINE_NUM');
514 app_exception.raise_exception;
515 END IF;
516
517 arp_util.debug('arp_trx_validate.check_dup_line_number()-');
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 arp_util.debug('EXCEPTION: arp_trx_validate.check_dup_line_number()');
522
523 arp_util.debug('');
524 arp_util.debug('----- parameters for check_dup_line_number() -------');
525
526 arp_util.debug('p_line_number = ' || p_line_number);
527 arp_util.debug('p_customer_trx_id = ' || p_customer_trx_id );
528
529 RAISE;
530
531 END;
532
533 /*===========================================================================+
534 | PROCEDURE |
535 | check_has_one_line |
536 | |
537 | DESCRIPTION |
538 | Checks to see if the line that is about to be deleted is the last line |
539 | on the transaction. |
540 | |
541 | SCOPE - PUBLIC |
542 | |
543 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
544 | arp_util.debug |
545 | |
546 | ARGUMENTS : IN: |
547 | p_customer_trx_id |
548 | p_display_message |
549 | OUT: |
550 | None |
551 | |
552 | RETURNS : NONE |
553 | |
554 | NOTES |
555 | |
556 | MODIFICATION HISTORY |
557 | 25-JUL-95 Charlie Tomberg Created |
558 | |
559 +===========================================================================*/
560
561
562 PROCEDURE check_has_one_line( p_customer_trx_id IN NUMBER,
563 p_display_message IN varchar2 default 'Y' )
564 IS
565
566 l_count number;
567
568 BEGIN
569
570 arp_util.debug('arp_trx_validate.check_has_one_line()+');
571
572 SELECT count(*)
573 INTO l_count
574 FROM ra_customer_trx_lines
575 WHERE customer_trx_id = p_customer_trx_id
576 AND link_to_cust_trx_line_id is NULL;
577
578 IF (l_count <= 1)
579 THEN
580
581 IF (p_display_message = 'Y')
582 THEN fnd_message.set_name('AR', '1210');
583 END IF;
584
585 app_exception.raise_exception;
586 END IF;
587
588 arp_util.debug('arp_trx_validate.check_has_one_line()-');
589
590 EXCEPTION
591 WHEN OTHERS THEN
592 arp_util.debug('EXCEPTION: arp_trx_validate.check_has_one_line()');
593
594 arp_util.debug('');
595 arp_util.debug('----- parameters for check_has_one_line() -------');
596
597 arp_util.debug('p_customer_trx_id = ' || p_customer_trx_id );
598 arp_util.debug('p_display_message = ' || p_display_message );
599
600 RAISE;
601
602 END;
603
604 /*===========================================================================+
605 | PROCEDURE |
606 | check_sign_and_overapp |
607 | |
608 | DESCRIPTION |
609 | Checks to see if the transaction violates the creation sign or |
610 | overapplication constraints. |
611 | |
612 | SCOPE - PUBLIC |
613 | |
614 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
615 | arp_util.debug |
616 | |
617 | ARGUMENTS : IN: |
618 | p_customer_trx_id |
619 | p_previous_customer_trx_id |
620 | p_trx_open_receivables_flag |
621 | p_prev_open_receivables_flag |
622 | p_creation_sign |
623 | p_allow_overapplication_flag |
624 | p_natural_application_flag |
625 | OUT: |
626 | None |
627 | |
628 | |
629 | NOTES |
630 | |
631 | MODIFICATION HISTORY |
632 | 05-SEP-95 Charlie Tomberg Created |
633 | |
634 +===========================================================================*/
635 PROCEDURE check_sign_and_overapp(
636 p_customer_trx_id IN NUMBER,
637 p_previous_customer_trx_id IN NUMBER,
638 p_trx_open_receivables_flag IN VARCHAR2,
639 p_prev_open_receivables_flag IN VARCHAR2,
640 p_creation_sign IN VARCHAR2,
641 p_allow_overapplication_flag IN VARCHAR2,
642 p_natural_application_flag IN VARCHAR2
643 )
644 IS
645
646 l_error_count NUMBER ;
647
648 BEGIN
649
650 IF PG_DEBUG in ('Y', 'C') THEN
651 arp_util.debug('arp_trx_validate.check_sign_and_overapp()+');
652 END IF;
653
654 check_sign_and_overapp(
655 p_customer_trx_id => p_customer_trx_id,
656 p_previous_customer_trx_id => p_previous_customer_trx_id,
657 p_trx_open_receivables_flag => p_trx_open_receivables_flag,
658 p_prev_open_receivables_flag => p_prev_open_receivables_flag,
659 p_creation_sign => p_creation_sign,
660 p_allow_overapplication_flag => p_allow_overapplication_flag ,
661 p_natural_application_flag => p_natural_application_flag ,
662 p_error_mode => 'STANDARD',
663 p_error_count => l_error_count
664 );
665
666 IF PG_DEBUG in ('Y', 'C') THEN
667 arp_util.debug('arp_trx_validate.check_sign_and_overapp()-');
668 END IF;
669
670
671 END check_sign_and_overapp;
672
673 /*===========================================================================+
674 | PROCEDURE |
675 | check_sign_and_overapp |
676 | |
677 | DESCRIPTION |
678 | Checks to see if the transaction violates the creation sign or |
679 | overapplication constraints. |
680 | |
681 | SCOPE - PUBLIC |
682 | |
683 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
684 | arp_util.debug |
685 | |
686 | ARGUMENTS : IN: |
687 | p_customer_trx_id |
688 | p_previous_customer_trx_id |
689 | p_trx_open_receivables_flag |
690 | p_prev_open_receivables_flag |
691 | p_creation_sign |
692 | p_allow_overapplication_flag |
693 | p_natural_application_flag |
694 | p_error_mode -- Bug3041195 |
695 | OUT: |
696 | p_error_count -- Bug3041195 |
697 | |
698 | |
699 | NOTES |
700 | |
701 | MODIFICATION HISTORY |
702 | Sahana Bug3041195: Overloaded procedure |
703 | check_sign_and_overapp. Additional |
704 | parameters p_error_mode and p_error_count. |
705 | Handling of error message is done |
706 | by add_to_error_list (similar |
707 | to do_completion_checking ) |
708 +===========================================================================*/
709
710
711 PROCEDURE check_sign_and_overapp(
712 p_customer_trx_id IN NUMBER,
713 p_previous_customer_trx_id IN NUMBER,
714 p_trx_open_receivables_flag IN VARCHAR2,
715 p_prev_open_receivables_flag IN VARCHAR2,
716 p_creation_sign IN VARCHAR2,
717 p_allow_overapplication_flag IN VARCHAR2,
718 p_natural_application_flag IN VARCHAR2,
719 p_error_mode IN VARCHAR2,
720 p_error_count OUT NOCOPY NUMBER
721 )
722 IS
723
724 l_line_original NUMBER;
725 l_line_remaining NUMBER;
726 l_tax_original NUMBER;
727 l_tax_remaining NUMBER;
728 l_freight_original NUMBER;
729 l_freight_remaining NUMBER;
730 l_charges_original NUMBER;
731 l_charges_remaining NUMBER;
732 l_total_original NUMBER;
733 l_total_remaining NUMBER;
734
735 l_prev_line_original NUMBER;
736 l_prev_line_remaining NUMBER;
737 l_prev_tax_original NUMBER;
738 l_prev_tax_remaining NUMBER;
739 l_prev_freight_original NUMBER;
740 l_prev_freight_remaining NUMBER;
741 l_prev_charges_original NUMBER;
742 l_prev_charges_remaining NUMBER;
743 l_prev_total_original NUMBER;
744 l_prev_total_remaining NUMBER;
745
746 l_new_line NUMBER;
747 l_new_tax NUMBER;
748 l_new_freight NUMBER;
749
750 /* Bug 882789 */
751 l_commit_adj_amount NUMBER;
752 /* Bug 2534132 */
753 l_commit_line_amount NUMBER;
754 l_commit_tax_amount NUMBER;
755 l_commit_frt_amount NUMBER;
756
757 l_error_count NUMBER := 0;
758 l_error_message VARCHAR2(30);
759
760
761 BEGIN
762
763 IF PG_DEBUG in ('Y', 'C') THEN
764 arp_util.debug('Overloaded:arp_trx_validate.check_sign_and_overapp()+');
765 END IF;
766
767
768 /*-------------------------------------------------------------------+
769 | Get the previous balance and the new balance of the transaction. |
770 | get_summary_trx_balances() returns the previous balances |
771 | because the payment schedules have not yet been updated. |
772 +-------------------------------------------------------------------*/
773
774 arp_trx_util.get_summary_trx_balances( p_customer_trx_id,
775 p_trx_open_receivables_flag,
776 l_line_original,
777 l_line_remaining,
778 l_tax_original,
779 l_tax_remaining,
780 l_freight_original,
781 l_freight_remaining,
782 l_charges_original,
783 l_charges_remaining,
784 l_total_original,
785 l_total_remaining );
786
787 SELECT SUM(
788 DECODE( ctl.line_type,
789 'TAX', 0,
790 'FREIGHT', 0,
791 ctl.extended_amount
792 )
793 ),
794 SUM(
795 DECODE( ctl.line_type,
796 'TAX', ctl.extended_amount,
797 0 )
798 ),
799 SUM(
800 DECODE( ctl.line_type,
801 'FREIGHT', ctl.extended_amount,
802 0 )
803 )
804 INTO l_new_line,
805 l_new_tax,
806 l_new_freight
807 FROM ra_customer_trx_lines ctl
808 WHERE customer_trx_id = p_customer_trx_id;
809
810
811 /*------------------------------------------------------+
812 | Check the creation sign of the entire transaction |
813 +------------------------------------------------------*/
814
815 arp_non_db_pkg.check_creation_sign(
816 p_creation_sign => p_creation_sign,
817 p_amount => l_new_line + l_new_tax + l_new_freight,
818 event => NULL,
819 p_message_name => l_error_message);
820
821
822 /*Bug3041195*/
823 IF ( l_error_message IS NOT NULL )
824 THEN
825 arp_trx_validate.add_to_error_list(
826 p_error_mode,
827 l_error_count,
828 p_previous_customer_trx_id,
829 NULL, -- trx_number,
830 NULL, -- line_number
831 NULL, -- other_line_number
832 l_error_message,
833 'Check Creation Sign',
834 NULL,
835 NULL,
836 NULL,
837 NULL
838 );
839 END IF;
840
841
842
843
844
845 /*------------------------------------------------+
846 | If the current transaction is a credit memo, |
847 | get the credited transaction balances |
848 +------------------------------------------------*/
849
850 IF ( p_previous_customer_trx_id IS NOT NULL )
851 THEN
852 arp_trx_util.get_summary_trx_balances( p_previous_customer_trx_id,
853 p_prev_open_receivables_flag,
854 l_prev_line_original,
855 l_prev_line_remaining,
856 l_prev_tax_original,
857 l_prev_tax_remaining,
858 l_prev_freight_original,
859 l_prev_freight_remaining,
860 l_prev_charges_original,
861 l_prev_charges_remaining,
862 l_prev_total_original,
863 l_prev_total_remaining );
864
865 /*-----------------------------------+
866 | Check overapplication for line |
867 +-----------------------------------*/
868
869 /* Bug 882789: Get commitment adjustment amount for the credited
870 transaction. This amount should be added to l_prev_line_remaining
871 when checking natural application since the commitment adjustment
872 will be reversed when we complete the credit memo. Otherwise,
873 natural application checking will fail since the credit amount
874 is more than the amount remaining for the credited transaction */
875
876 /* Bug 2534132: Get Line,tax and freight buckets of the Commitment Adjustment
877 and add to the line_remaining, tax_remaining and freight_remaining while
878 checking natural application since the commitment adjustment will be reversed
879 when we complete the credit memo. */
880
881 select nvl(sum(amount),0),nvl(sum(line_adjusted),0),nvl(sum(tax_adjusted),0),nvl(sum(freight_adjusted),0)
882 into l_commit_adj_amount,l_commit_line_amount,l_commit_tax_amount,l_commit_frt_amount
883 from ar_adjustments
884 where customer_trx_id = p_previous_customer_trx_id
885 and receivables_trx_id = -1;
886
887
888
889 arp_non_db_pkg.check_natural_application(
890 p_creation_sign => p_creation_sign,
891 p_allow_overapplication_flag =>
892 p_allow_overapplication_flag,
893 p_natural_app_only_flag => p_natural_application_flag,
894 p_sign_of_ps => '+',
895 p_chk_overapp_if_zero => 'Y',
896 p_payment_amount => l_new_line,
897 p_discount_taken => 0,
898 p_amount_due_remaining => l_prev_line_remaining -
899 l_commit_line_amount - /* Bug 2534132 */
900 nvl(l_line_original, 0),
901 p_amount_due_original => l_prev_line_original,
902 event => NULL,
903 p_message_name => l_error_message,
904 p_lockbox_record => FALSE
905 );
906
907
908 /*Bug3041195*/
909 IF ( l_error_message IS NOT NULL )
910 THEN
911 arp_trx_validate.add_to_error_list(
912 p_error_mode,
913 l_error_count,
914 p_previous_customer_trx_id,
915 NULL, -- trx_number,
916 NULL, -- line_number
917 NULL, -- other_line_number
918 l_error_message,
919 'Check overapplication for line',
920 NULL,
921 NULL,
922 NULL,
923 NULL
924 );
925 END IF;
926
927
928
929 /*----------------------------------+
930 | Check overapplication for tax |
931 +----------------------------------*/
932
933 arp_non_db_pkg.check_natural_application(
934 p_creation_sign => p_creation_sign,
935 p_allow_overapplication_flag =>
936 p_allow_overapplication_flag,
937 p_natural_app_only_flag => p_natural_application_flag,
938 p_sign_of_ps => '+',
939 p_chk_overapp_if_zero => 'Y',
940 p_payment_amount => l_new_tax,
941 p_discount_taken => 0,
942 p_amount_due_remaining => l_prev_tax_remaining-
943 l_commit_tax_amount-/*Bug2534132*/
944 nvl(l_tax_original,0),
945 p_amount_due_original => l_prev_tax_original,
946 event => NULL,
947 p_message_name => l_error_message,
948 p_lockbox_record => FALSE
949 );
950
951 /*Bug3041195*/
952 IF ( l_error_message IS NOT NULL )
953 THEN
954 arp_trx_validate.add_to_error_list(
955 p_error_mode,
956 l_error_count,
957 p_previous_customer_trx_id,
958 NULL, -- trx_number,
959 NULL, -- line_number
960 NULL, -- other_line_number
961 l_error_message,
962 'Check overapplication for Tax',
963 NULL,
964 NULL,
965 NULL,
966 NULL
967 );
968 END IF;
969
970
971 /*--------------------------------------+
972 | Check overapplication for freight |
973 +--------------------------------------*/
974
975 arp_non_db_pkg.check_natural_application(
976 p_creation_sign => p_creation_sign,
977 p_allow_overapplication_flag =>
978 p_allow_overapplication_flag,
979 p_natural_app_only_flag => p_natural_application_flag,
980 p_sign_of_ps => '+',
981 p_chk_overapp_if_zero => 'Y',
982 p_payment_amount => l_new_freight,
983 p_discount_taken => 0,
984 p_amount_due_remaining => l_prev_freight_remaining-
985 l_commit_frt_amount-/*Bug2534132*/
986 nvl(l_freight_original,0),
987 p_amount_due_original => l_prev_freight_original,
988 event => NULL,
989 p_message_name => l_error_message,
990 p_lockbox_record => FALSE
991 );
992
993 /*Bug3041195*/
994 IF ( l_error_message IS NOT NULL )
995 THEN
996 arp_trx_validate.add_to_error_list(
997 p_error_mode,
998 l_error_count,
999 p_previous_customer_trx_id,
1000 NULL, -- trx_number,
1001 NULL, -- line_number
1002 NULL, -- other_line_number
1003 l_error_message,
1004 'Check overapplication for Freight',
1005 NULL,
1006 NULL,
1007 NULL,
1008 NULL
1009 );
1010 END IF;
1011
1012
1013
1014
1015 END IF;
1016
1017 p_error_count := l_error_count;
1018 IF PG_DEBUG in ('Y', 'C') THEN
1019 arp_util.debug('Overloaded:arp_trx_validate.check_sign_and_overapp()+');
1020 END IF;
1021
1022
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 arp_util.debug('EXCEPTION: arp_trx_validate.check_sign_and_overapp()');
1026 p_error_count := l_error_count;
1027 arp_util.debug('');
1028 arp_util.debug('----- parameters for check_sign_and_overapp() ------');
1029
1030 arp_util.debug( 'p_customer_trx_id: = ' ||
1031 p_customer_trx_id );
1032 arp_util.debug( 'p_previous_customer_trx_id: = ' ||
1033 p_previous_customer_trx_id );
1034 arp_util.debug( 'p_trx_open_receivables_flag: = ' ||
1035 p_trx_open_receivables_flag );
1036 arp_util.debug( 'p_prev_open_receivables_flag: = ' ||
1037 p_prev_open_receivables_flag );
1038 arp_util.debug( 'p_creation_sign: = ' ||
1039 p_creation_sign );
1040 arp_util.debug( 'p_allow_overapplication_flag: = ' ||
1041 p_allow_overapplication_flag );
1042 arp_util.debug( 'p_natural_application_flag: = ' ||
1043 p_natural_application_flag );
1044
1045 RAISE;
1046
1047 END;
1048
1049 /*===========================================================================+
1050 | FUNCTION |
1051 | validate_paying_customer() |
1052 | |
1053 | DESCRIPTION |
1054 | |
1055 | SCOPE - PUBLIC |
1056 | |
1057 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1058 | arp_util.debug |
1059 | |
1060 | ARGUMENTS : IN: |
1061 | p_paying_customer_id |
1062 | p_trx_date |
1063 | p_bill_to_customer_id |
1064 | p_ct_prev_paying_customer_id |
1065 | p_currency_code |
1066 | p_pay_unrelated_invoices_flag |
1067 | OUT: |
1068 | None |
1069 | |
1070 | RETURNS : NONE |
1071 | |
1072 | NOTES |
1073 | |
1074 | MODIFICATION HISTORY |
1075 | 10-MAY-96 Charlie Tomberg Created |
1076 | 08-Sep-97 Debbie Jancis Modified the select when |
1077 | pay_unrelated_invoice_flag is N to fix |
1078 | defaulting problem in Bug 462569. |
1079 +===========================================================================*/
1080
1081
1082 FUNCTION validate_paying_customer( p_paying_customer_id IN NUMBER,
1083 p_trx_date IN date,
1084 p_bill_to_customer_id IN NUMBER,
1085 p_ct_prev_paying_customer_id IN NUMBER,
1086 p_currency_code IN varchar2,
1087 p_pay_unrelated_invoices_flag IN varchar2,
1088 p_ct_prev_trx_date IN date)
1089 RETURN BOOLEAN
1090 IS
1091
1092 l_paying_customer_is_valid varchar2(1);
1093
1094 BEGIN
1095
1096 arp_util.debug('ARP_TRX_VALIDATE.validate_paying_customer()+');
1097
1098 BEGIN
1099
1100 --ajay bug 1081390
1101 --removed the condition to check that the paying customer should have a
1102 --bank account.
1103
1104 BEGIN
1105
1106 IF (p_pay_unrelated_invoices_flag = 'Y')
1107 THEN
1108
1109 SELECT 'Y'
1110 INTO l_paying_customer_is_valid
1111 FROM hz_cust_accounts cust_acct
1112 WHERE cust_acct.cust_account_id = p_paying_customer_id
1113 AND (
1114 cust_acct.cust_account_id = p_ct_prev_paying_customer_id
1115 OR
1116 (
1117 cust_acct.status = 'A'
1118 )
1119 );
1120
1121 ELSE
1122
1123 SELECT 'Y'
1124 INTO l_paying_customer_is_valid
1125 FROM hz_cust_accounts cust_acct
1126 WHERE cust_acct.cust_account_id = p_paying_customer_id
1127 AND (
1128 cust_acct.cust_account_id = p_ct_prev_paying_customer_id
1129 OR
1130 (
1131 cust_acct.status = 'A'
1132 )
1133 )
1134 AND cust_acct.cust_account_id IN
1135 (
1136 SELECT DISTINCT cr.cust_account_id
1137 FROM hz_cust_acct_relate cr
1138 WHERE cr.related_cust_account_id = p_bill_to_customer_id
1139 AND cr.status = 'A'
1140 AND cr.bill_to_flag = 'Y'
1141 UNION
1142 SELECT TO_NUMBER(p_ct_prev_paying_customer_id)
1143 FROM dual
1144 UNION
1145 SELECT TO_NUMBER(p_bill_to_customer_id)
1146 FROM dual
1147 UNION
1148 SELECT acc.cust_account_id
1149 FROM ar_paying_relationships_v rel,
1150 hz_cust_accounts acc
1151 WHERE rel.party_id = acc.party_id
1152 AND rel.related_cust_account_id = p_bill_to_customer_id
1153 AND p_ct_prev_trx_date BETWEEN effective_start_date
1154 AND effective_end_date
1155
1156 );
1157
1158 END IF;
1159
1160 EXCEPTION
1161 WHEN no_data_found THEN
1162 l_paying_customer_is_valid := 'N';
1163
1164 END ;
1165
1166
1167 IF (l_paying_customer_is_valid = 'Y')
1168 THEN RETURN(TRUE);
1169 ELSE RETURN(FALSE);
1170 END IF;
1171
1172 EXCEPTION
1173 WHEN OTHERS THEN RAISE;
1174 END;
1175
1176 arp_util.debug('ARP_TRX_VALIDATE.validate_paying_customer()-');
1177
1178 EXCEPTION
1179 WHEN OTHERS THEN
1180 arp_util.debug('EXCEPTION: ARP_TRX_VALIDATE.validate_paying_customer()');
1181 RAISE;
1182
1183 END;
1184
1185 /*===========================================================================+
1186 | PROCEDURE |
1187 | validate_trx_date() |
1188 | |
1189 | DESCRIPTION |
1190 | Validates that all entities that have date ranges are still valid after|
1191 | the transaction date changes. |
1192 | |
1193 | SCOPE - PUBLIC |
1194 | |
1195 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1196 | arp_util.debug |
1197 | |
1198 | ARGUMENTS : IN: |
1199 | p_trx_date |
1200 | p_prev_trx_date |
1201 | p_commitment_trx_date |
1202 | p_customer_trx_id |
1203 | p_customer_trx_id |
1204 | p_previous_customer_trx_id |
1205 | p_initial_customer_trx_id |
1206 | p_agreement_id |
1207 | p_batch_source_id |
1208 | p_cust_trx_type_id |
1209 | p_term_id |
1210 | p_ship_method_code |
1211 | p_primary_salesrep_id |
1212 | p_reason_code |
1213 | p_status_trx |
1214 | p_invoice_currency_code |
1215 | p_receipt_method_id |
1216 | p_bank_account_id |
1217 | OUT: |
1218 | p_due_date |
1219 | p_result_flag |
1220 | p_commitment_invalid_flag |
1221 | p_invalid_agreement_flag |
1222 | p_invalid_source_flag |
1223 | p_invalid_type_flag |
1224 | p_invalid_term_flag |
1225 | p_invalid_ship_method_flag |
1226 | p_invalid_primary_srep_flag |
1227 | p_invalid_reason_flag |
1228 | p_invalid_status_flag |
1229 | p_invalid_currency_flag |
1230 | p_invalid_payment_mthd_flag |
1231 | p_invalid_bank_flag |
1232 | p_invalid_salesrep_flag |
1233 | p_invalid_memo_line_flag |
1234 | p_invalid_uom_flag |
1235 | p_invalid_tax_flag |
1236 | p_invalid_cm_date_flag |
1237 | p_invalid_child_date_flag |
1238 | |
1239 | IN / OUT: |
1240 | p_error_count |
1241 | RETURNS : NONE |
1242 | |
1243 | NOTES |
1244 | |
1245 | MODIFICATION HISTORY |
1246 | 31-OCT-95 Charlie Tomberg Created |
1247 | 18-MAR-96 Martin Johnson Removed validation of UOM since it |
1248 | is no longer validated against trx_date|
1249 | (part of BugNo:337819) |
1250 | 22-MAY-2000 J Rautiainen BR Implementation |
1251 | 28-SEP-2001 M Raymond Performance problem in validate_trx_date
1252 | caused by FTS of ra_cust_receipt_methods
1253 | See bug 2001878 for details. |
1254 | 18-JAN-2002 M Raymond Fix for 2001878 introduced a requirement
1255 | for payment methods at site level.
1256 | Revised fix to allow for null methods
1257 | at site level. See bug 2176210 for det.
1258 | 22-FEB-2002 M Raymond Bug 2195793 - added validation for
1259 | commitments with zero balance.
1260 | 02-MAY-2002 M Raymond Bug 2340543 - Validation for 2195793
1261 | should not fire for credit memos.
1262 |
1263 +===========================================================================*/
1264
1265 PROCEDURE validate_trx_date( p_error_mode IN VARCHAR2,
1266 p_trx_date IN DATE,
1267 p_prev_trx_date IN DATE,
1268 p_commitment_trx_date IN DATE,
1269 p_customer_trx_id IN NUMBER,
1270 p_trx_number IN VARCHAR2,
1271 p_previous_customer_trx_id IN NUMBER,
1272 p_initial_customer_trx_id IN NUMBER,
1273 p_agreement_id IN NUMBER,
1274 p_batch_source_id IN NUMBER,
1275 p_cust_trx_type_id IN NUMBER,
1276 p_term_id IN NUMBER,
1277 p_ship_method_code IN VARCHAR2,
1278 p_primary_salesrep_id IN NUMBER,
1279 p_reason_code IN VARCHAR2,
1280 p_status_trx IN VARCHAR2,
1281 p_invoice_currency_code IN VARCHAR2,
1282 p_receipt_method_id IN NUMBER,
1283 p_bank_account_id IN NUMBER,
1284 p_due_date OUT NOCOPY date,
1285 p_result_flag OUT NOCOPY boolean,
1286 p_commitment_invalid_flag OUT NOCOPY boolean,
1287 p_invalid_agreement_flag OUT NOCOPY boolean,
1288 p_invalid_source_flag OUT NOCOPY boolean,
1289 p_invalid_type_flag OUT NOCOPY boolean,
1290 p_invalid_term_flag OUT NOCOPY boolean,
1291 p_invalid_ship_method_flag OUT NOCOPY boolean,
1292 p_invalid_primary_srep_flag OUT NOCOPY boolean,
1293 p_invalid_reason_flag OUT NOCOPY boolean,
1294 p_invalid_status_flag OUT NOCOPY boolean,
1295 p_invalid_currency_flag OUT NOCOPY boolean,
1296 p_invalid_payment_mthd_flag OUT NOCOPY boolean,
1297 p_invalid_bank_flag OUT NOCOPY boolean,
1298 p_invalid_salesrep_flag OUT NOCOPY boolean,
1299 p_invalid_memo_line_flag OUT NOCOPY boolean,
1300 p_invalid_uom_flag OUT NOCOPY boolean,
1301 p_invalid_tax_flag OUT NOCOPY boolean,
1302 p_invalid_cm_date_flag OUT NOCOPY boolean,
1303 p_invalid_child_date_flag OUT NOCOPY boolean,
1304 p_error_count IN OUT NOCOPY integer
1305 ) IS
1306
1307 l_temp varchar2(128);
1308 l_temp2 varchar2(128);
1309 l_commit_bal NUMBER;
1310 l_so_source_code varchar2(100);
1311
1312 /*--------------------------------------------------------------------+
1313 | 23-MAY-2000 J Rautiainen BR Implementation |
1314 | BR payment method does not have bank account associated with it |
1315 | This cursor is used to branch code into a correct select statement |
1316 | in the payment method validation. |
1317 +--------------------------------------------------------------------*/
1318 CURSOR receipt_creation_method_cur IS
1319 SELECT arc.creation_method_code
1320 FROM ar_receipt_methods arm,
1321 ar_receipt_classes arc
1322 WHERE arm.receipt_class_id = arc.receipt_class_id
1323 AND arm.receipt_method_id = p_receipt_method_id;
1324
1325 receipt_creation_method_rec receipt_creation_method_cur%ROWTYPE;
1326
1327 /*Bug3348454 */
1328 l_bill_to_customer_id ra_customer_trx.bill_to_customer_id%TYPE;
1329 l_paying_customer_id ra_customer_trx.paying_customer_id%TYPE;
1330
1331 BEGIN
1332
1333 arp_util.debug('ARP_TRX_VALIDATE.validate_trx_date()+');
1334
1335 p_result_flag := TRUE;
1336
1337 /*------------------------------------------------------------------+
1338 | Validate that CM Date is >= the credited transaction's trx_date |
1339 +------------------------------------------------------------------*/
1340
1341 arp_util.debug('Validate that CM Date is >= the credited transaction''s '||
1342 'trx_date');
1343
1344 IF ( p_trx_date < p_prev_trx_date )
1345 THEN
1346 p_invalid_cm_date_flag := TRUE;
1347 p_result_flag := FALSE;
1348
1349 add_to_error_list(
1350 p_error_mode,
1351 p_error_count,
1352 p_customer_trx_id,
1353 p_trx_number,
1354 NULL,
1355 NULL,
1356 'AR_TW_BAD_DATE_CM_DATE',
1357 'TGW_HEADER.TRX_DATE',
1358 NULL,
1359 NULL,
1360 NULL,
1361 NULL
1362 );
1363
1364 END IF;
1365
1366 /*---------------------------------------------------------------+
1367 | Validate that the Child Date is >= the commitment's trx_date |
1368 +----------------------------------------------------------------*/
1369
1370 arp_util.debug(
1371 'Validate that the Child Date is >= the commitment''s trx_date');
1372
1373 IF ( p_trx_date < p_commitment_trx_date )
1374 THEN
1375 p_invalid_child_date_flag := TRUE;
1376 p_result_flag := FALSE;
1377
1378
1379 add_to_error_list(
1380 p_error_mode,
1381 p_error_count,
1382 p_customer_trx_id,
1383 p_trx_number,
1384 NULL,
1385 NULL,
1386 'AR_TW_BAD_DATE_CHILD_DATE',
1387 'TGW_HEADER.TRX_DATE',
1388 NULL,
1389 NULL,
1390 NULL,
1391 NULL
1392 );
1393
1394
1395 END IF;
1396
1397 /*-----------------------+
1398 | Validate Commitment |
1399 +-----------------------*/
1400
1401 arp_util.debug('Validate Commitment');
1402
1403 /* Bug 2340543 - Do not test commitment balance or date for
1404 credit memos */
1405 IF ( p_initial_customer_trx_id IS NOT NULL AND
1406 p_previous_customer_trx_id IS NULL)
1407 THEN
1408
1409 /* Bug 2195793 - We have determined that it is a problem if
1410 you have an invoice pointing to a commitment if that
1411 commitment was exhausted by prior transactions.
1412
1413 This could be caused by invoice copy (where commitment
1414 gets exhausted before last invoice is created) or if
1415 you leave invoices incomplete with a commitment assigned
1416 to them.
1417
1418 To prevent this, we added a call to arp_bal_util to
1419 get the commitment balance at that moment. If it is
1420 exhausted, we will flag an error. */
1421
1422 BEGIN
1423 oe_profile.get( 'SO_SOURCE_CODE', l_so_source_code );
1424
1425 SELECT 'invoice date is ok',
1426 arp_bal_util.get_commitment_balance(
1427 p_initial_customer_trx_id,
1428 tt.TYPE,
1429 l_so_source_code,
1430 'N')
1431 INTO l_temp,
1432 l_commit_bal
1433 FROM ra_customer_trx t,
1434 ra_cust_trx_types tt
1435 WHERE t.customer_trx_id = p_initial_customer_trx_id
1436 AND t.cust_trx_type_id = tt.cust_trx_type_id
1437 AND p_trx_date
1438 BETWEEN NVL( t.start_date_commitment, p_trx_date )
1439 AND NVL( t.end_date_commitment, p_trx_date );
1440
1441 IF (l_commit_bal = 0)
1442 THEN
1443 p_commitment_invalid_flag := TRUE;
1444 p_result_flag := FALSE;
1445
1446 add_to_error_list(
1447 p_error_mode,
1448 p_error_count,
1449 p_customer_trx_id,
1450 p_trx_number,
1451 NULL,
1452 NULL,
1453 'AR_ZERO_COMMITMENT',
1454 'TGW_HEADER.CT_COMMITMENT_NUMBER',
1455 NULL,
1456 NULL,
1457 NULL,
1458 NULL
1459 );
1460
1461 END IF;
1462
1463 EXCEPTION
1464 WHEN NO_DATA_FOUND THEN
1465 p_commitment_invalid_flag := TRUE;
1466 p_result_flag := FALSE;
1467
1468 add_to_error_list(
1469 p_error_mode,
1470 p_error_count,
1471 p_customer_trx_id,
1472 p_trx_number,
1473 NULL,
1474 NULL,
1475 'AR_TW_BAD_DATE_COMMITMENT',
1476 'TGW_HEADER.CT_COMMITMENT_NUMBER',
1477 NULL,
1478 NULL,
1479 NULL,
1480 NULL
1481 );
1482
1483 WHEN OTHERS THEN RAISE;
1484 END;
1485
1486 END IF;
1487
1488
1489 /*----------------------+
1490 | Validate Agreement |
1491 +----------------------*/
1492
1493 arp_util.debug('Validate Agreement');
1494
1495 IF (p_agreement_id IS NOT NULL)
1496 THEN
1497 BEGIN
1498 SELECT 'invoice date is ok'
1499 INTO l_temp
1500 FROM so_agreements
1501 WHERE agreement_id = p_agreement_id
1502 AND p_trx_date BETWEEN NVL(TRUNC(start_date_active),
1503 p_trx_date )
1504 AND NVL(TRUNC(end_date_active),
1505 p_trx_date);
1506
1507 EXCEPTION
1508 WHEN NO_DATA_FOUND THEN
1509 p_invalid_agreement_flag := TRUE;
1510 p_result_flag := FALSE;
1511
1512 add_to_error_list(
1513 p_error_mode,
1514 p_error_count,
1515 p_customer_trx_id,
1516 p_trx_number,
1517 NULL,
1518 NULL,
1519 'AR_TW_BAD_DATE_AGREEMENT',
1520 'TGW_HEADER.SOA_AGREEMENT_NAME',
1521 NULL,
1522 NULL,
1523 NULL,
1524 NULL
1525 );
1526
1527 WHEN OTHERS THEN RAISE;
1528 END;
1529 END IF;
1530
1531 /*-------------------------+
1532 | Validate Batch Source |
1533 +-------------------------*/
1534
1535 arp_util.debug('Validate Batch Source');
1536
1537 IF (p_batch_source_id IS NOT NULL)
1538 THEN
1539 BEGIN
1540
1541 SELECT 'invoice date is ok'
1542 INTO l_temp
1543 FROM ra_batch_sources
1544 WHERE batch_source_id = p_batch_source_id
1545 AND p_trx_date BETWEEN NVL(start_date, p_trx_date)
1546 AND NVL(end_date, p_trx_date);
1547
1548 EXCEPTION
1549 WHEN NO_DATA_FOUND THEN
1550 p_invalid_source_flag := TRUE;
1551 p_result_flag := FALSE;
1552
1553 add_to_error_list(
1554 p_error_mode,
1555 p_error_count,
1556 p_customer_trx_id,
1557 p_trx_number,
1558 NULL,
1559 NULL,
1560 'AR_TW_BAD_DATE_SOURCE',
1561 'TGW_HEADER.BS_BATCH_SOURCE_NAME',
1562 NULL,
1563 NULL,
1564 NULL,
1565 NULL
1566 );
1567
1568 WHEN OTHERS THEN RAISE;
1569 END;
1570 END IF;
1571
1572
1573 /*-----------------+
1574 | Validate Type |
1575 +-----------------*/
1576
1577 arp_util.debug('Validate Type');
1578
1579 IF ( p_cust_trx_type_id IS NOT NULL )
1580 THEN
1581 BEGIN
1582 SELECT tax_calculation_flag
1583 INTO l_temp
1584 FROM ra_cust_trx_types
1585 WHERE CUST_TRX_TYPE_ID = p_cust_trx_type_id
1586 AND p_trx_date BETWEEN START_DATE
1587 AND NVL(END_DATE, p_trx_date);
1588
1589
1590 EXCEPTION
1591 WHEN NO_DATA_FOUND THEN
1592 p_invalid_type_flag := TRUE;
1593 p_result_flag := FALSE;
1594
1595 add_to_error_list(
1596 p_error_mode,
1597 p_error_count,
1598 p_customer_trx_id,
1599 p_trx_number,
1600 NULL,
1601 NULL,
1602 'AR_TW_BAD_DATE_TRX_TYPE',
1603 'TGW_HEADER.CTT_TYPE_NAME',
1604 NULL,
1605 NULL,
1606 NULL,
1607 NULL
1608 );
1609
1610 WHEN OTHERS THEN RAISE;
1611 END;
1612 END IF;
1613
1614
1615
1616 /*------------------+
1617 | Validate Terms |
1618 +------------------*/
1619
1620 arp_util.debug('Validate Terms');
1621
1622 IF ( p_term_id IS NOT NULL )
1623 THEN
1624 BEGIN
1625
1626 SELECT 'invoice date is ok'
1627 INTO l_temp
1628 FROM ra_terms
1629 WHERE term_id = p_term_id
1630 AND p_trx_date BETWEEN START_DATE_ACTIVE
1631 AND NVL(END_DATE_ACTIVE, p_trx_date);
1632
1633 EXCEPTION
1634 WHEN NO_DATA_FOUND THEN
1635 p_invalid_term_flag := TRUE;
1636 p_result_flag := FALSE;
1637
1638 add_to_error_list(
1639 p_error_mode,
1640 p_error_count,
1641 p_customer_trx_id,
1642 p_trx_number,
1643 NULL,
1644 NULL,
1645 'AR_TW_BAD_DATE_TERM',
1646 'TGW_HEADER.RAT_TERM_NAME',
1647 NULL,
1648 NULL,
1649 NULL,
1650 NULL
1651 );
1652
1653 WHEN OTHERS THEN RAISE;
1654 END;
1655 END IF;
1656
1657
1658
1659 /*---------------------+
1660 | Validate ship via |
1661 +---------------------*/
1662
1663 arp_util.debug('Validate ship via');
1664
1665 IF ( p_ship_method_code IS NOT NULL )
1666 THEN
1667
1668 BEGIN
1669
1670 SELECT 'invoice date is ok'
1671 INTO l_temp
1672 FROM ORG_FREIGHT
1673 WHERE freight_code = p_ship_method_code
1674 AND organization_id =
1675 to_number(oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id))
1676 AND p_trx_date < NVL(TRUNC(DISABLE_DATE), p_trx_date + 1);
1677
1678 EXCEPTION
1679 WHEN NO_DATA_FOUND THEN
1680 p_invalid_ship_method_flag := TRUE;
1681 p_result_flag := FALSE;
1682
1683 add_to_error_list(
1684 p_error_mode,
1685 p_error_count,
1686 p_customer_trx_id,
1687 p_trx_number,
1688 NULL,
1689 NULL,
1690 'AR_TW_BAD_DATE_SHIP_METHOD',
1691 'TFRT_HEADER.of_ship_via_description',
1692 NULL,
1693 NULL,
1694 NULL,
1695 NULL
1696 );
1697
1698 WHEN OTHERS THEN RAISE;
1699 END;
1700 END IF;
1701
1702
1703 /*---------------------+
1704 | Validate CM Reason |
1705 +---------------------*/
1706
1707 arp_util.debug('Validate CM Reason');
1708
1709 IF ( p_previous_customer_trx_id IS NOT NULL
1710 AND
1711 p_reason_code IS NOT NULL
1712 )
1713 THEN
1714 BEGIN
1715
1716 SELECT 'reason code is ok'
1717 INTO l_temp
1718 FROM ar_lookups
1719 WHERE lookup_type = 'CREDIT_MEMO_REASON'
1720 AND lookup_code = p_reason_code
1721 AND p_trx_date
1722 BETWEEN NVL( start_date_active, p_trx_date )
1723 AND NVL( end_date_active, p_trx_date );
1724
1725 EXCEPTION
1726 WHEN NO_DATA_FOUND THEN
1727 p_invalid_reason_flag := TRUE;
1728 p_result_flag := FALSE;
1729
1730 add_to_error_list(
1731 p_error_mode,
1732 p_error_count,
1733 p_customer_trx_id,
1734 p_trx_number,
1735 NULL,
1736 NULL,
1737 'AR_TW_BAD_DATE_REASON',
1738 'TGW_HEADER.AL_REASON_MEANING',
1739 NULL,
1740 NULL,
1741 NULL,
1742 NULL
1743 );
1744
1745 WHEN OTHERS THEN RAISE;
1746 END;
1747
1748 END IF;
1749
1750
1751
1752 /*-------------------+
1753 | Validate Status |
1754 +-------------------*/
1755
1756 arp_util.debug('Validate Status');
1757
1758 IF ( p_status_trx IS NOT NULL )
1759 THEN
1760 BEGIN
1761
1762 SELECT 'status code is ok'
1763 INTO l_temp
1764 FROM ar_lookups
1765 WHERE lookup_type = 'INVOICE_TRX_STATUS'
1766 AND lookup_code = p_status_trx
1767 AND p_trx_date
1768 BETWEEN NVL( start_date_active, p_trx_date )
1769 AND NVL( end_date_active, p_trx_date );
1770
1771 EXCEPTION
1772 WHEN NO_DATA_FOUND THEN
1773 p_invalid_status_flag := TRUE;
1774 p_result_flag := FALSE;
1775
1776 add_to_error_list(
1777 p_error_mode,
1778 p_error_count,
1779 p_customer_trx_id,
1780 p_trx_number,
1781 NULL,
1782 NULL,
1783 'AR_TW_BAD_DATE_STATUS',
1784 'TGW_HEADER.STATUS_TRX',
1785 NULL,
1786 NULL,
1787 NULL,
1788 NULL
1789 );
1790
1791 WHEN OTHERS THEN RAISE;
1792 END;
1793
1794 END IF;
1795
1796
1797
1798 /*----------------------------------------------------------------------+
1799 | If the transaction that is being validated is a credit memo |
1800 | against a specific invoice, commitment or debit memo, the |
1801 | following validations are not done in order to allow these |
1802 | entities to default in from the transaction that is being credited. |
1803 +----------------------------------------------------------------------*/
1804
1805 IF (p_previous_customer_trx_id IS NULL)
1806 THEN
1807
1808 /*-----------------------------+
1809 | Validate Primary Salesrep |
1810 +-----------------------------*/
1811
1812 arp_util.debug('Validate Primary Salesrep');
1813
1814 IF ( p_primary_salesrep_id IS NOT NULL )
1815 THEN
1816 BEGIN
1817
1818 SELECT 'invoice date is ok'
1819 INTO l_temp
1820 FROM ra_salesreps
1821 WHERE salesrep_id = p_primary_salesrep_id
1822 AND p_trx_date BETWEEN NVL(start_date_active,
1823 p_trx_date)
1824 AND NVL(end_date_active,
1825 p_trx_date);
1826
1827
1828 EXCEPTION
1829 WHEN NO_DATA_FOUND THEN
1830 p_invalid_primary_srep_flag := TRUE;
1831 p_result_flag := FALSE;
1832
1833 /* Bug 2191739 - call to message API for degovtized message */
1834 add_to_error_list(
1835 p_error_mode,
1836 p_error_count,
1837 p_customer_trx_id,
1838 p_trx_number,
1839 NULL,
1840 NULL,
1841 gl_public_sector.get_message_name
1842 (p_message_name => 'AR_TW_BAD_DATE_PRIMARY_SREP',
1843 p_app_short_name => 'AR'),
1844 'TGW_HEADER.RAS_PRIMARY_SALESREP_NAME',
1845 NULL,
1846 NULL,
1847 NULL,
1848 NULL
1849 );
1850
1851 WHEN OTHERS THEN RAISE;
1852 END;
1853 END IF;
1854
1855 /*---------------------+
1856 | Validate Currency |
1857 +---------------------*/
1858
1859 arp_util.debug('Validate Currency');
1860
1861 IF ( p_invoice_currency_code IS NOT NULL )
1862 THEN
1863 BEGIN
1864
1865 SELECT 'invoice date is ok'
1866 INTo l_temp
1867 FROM fnd_currencies
1868 WHERE currency_code = p_invoice_currency_code
1869 AND p_trx_date BETWEEN NVL(START_DATE_ACTIVE, p_trx_date)
1870 AND NVL(END_DATE_ACTIVE, p_trx_date);
1871
1872 EXCEPTION
1873 WHEN NO_DATA_FOUND THEN
1874 p_invalid_currency_flag := TRUE;
1875 p_result_flag := FALSE;
1876
1877 add_to_error_list(
1878 p_error_mode,
1879 p_error_count,
1880 p_customer_trx_id,
1881 p_trx_number,
1882 NULL,
1883 NULL,
1884 'AR_TW_BAD_DATE_CURRENCY',
1885 'TGW_HEADER.INVOICE_CURRENCY_CODE',
1886 NULL,
1887 NULL,
1888 NULL,
1889 NULL
1890 );
1891
1892 WHEN OTHERS THEN RAISE;
1893 END;
1894 END IF;
1895
1896
1897
1898 /*---------------------------+
1899 | Validate Receipt Method |
1900 +---------------------------*/
1901
1902 arp_util.debug('Validate Receipt Method');
1903
1904 --Modified the IF condition to fix Bug 2162888, added the check for customer_trx_id
1905 IF ( p_receipt_method_id IS NOT NULL ) AND ( p_customer_trx_id IS NOT NULL )
1906 THEN
1907
1908 /*--------------------------------------------------------------------+
1909 | 23-MAY-2000 J Rautiainen BR Implementation |
1910 | BR payment method does not have bank account associated with it |
1911 +--------------------------------------------------------------------*/
1912 OPEN receipt_creation_method_cur;
1913 FETCH receipt_creation_method_cur INTO receipt_creation_method_rec;
1914 CLOSE receipt_creation_method_cur;
1915
1916 IF NVL(receipt_creation_method_rec.creation_method_code,'INV') = 'BR' THEN
1917 BEGIN
1918
1919 /*Bug3348454 Need not validate the customer attachment.
1920 Only validate the receipt method*/
1921
1922 SELECT 'invalid_payment method'
1923 INTO l_temp
1924 FROM ar_receipt_methods arm,
1925 ar_receipt_classes arc
1926 WHERE arm.receipt_class_id = arc.receipt_class_id
1927 AND arm.receipt_method_id = p_receipt_method_id
1928 AND p_trx_date BETWEEN NVL(arm.start_date,
1929 p_trx_date)
1930 AND NVL(arm.end_date,
1931 p_trx_date)
1932 AND rownum = 1;
1933
1934 EXCEPTION
1935 WHEN NO_DATA_FOUND THEN
1936 p_invalid_payment_mthd_flag := TRUE;
1937 p_result_flag := FALSE;
1938
1939 add_to_error_list(
1940 p_error_mode,
1941 p_error_count,
1942 p_customer_trx_id,
1943 p_trx_number,
1944 NULL,
1945 NULL,
1946 'AR_TW_BAD_DATE_PAYMENT_METHOD',
1947 'TGW_HEADER.ARM_RECEIPT_METHOD_NAME',
1948 NULL,
1949 NULL,
1950 NULL,
1951 NULL
1952 );
1953
1954 WHEN OTHERS THEN RAISE;
1955 END;
1956
1957 ELSE
1958 BEGIN
1959
1960 /* If Payment Creation Code is NON BR then validate bank accounts also for this
1961 receipt method accounts */
1962
1963 BEGIN
1964 SELECT arp_trx_defaults_3.get_party_id(paying_customer_id),
1965 arp_trx_defaults_3.get_party_id(bill_to_customer_id)
1966 INTO l_paying_customer_id,l_bill_to_customer_id
1967 FROM RA_CUSTOMER_TRX
1968 WHERE customer_trx_id=p_customer_trx_id;
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 RAISE;
1972 END;
1973
1974 /* We need to validate the following.
1975 1. Receipt Method end date.
1976 2. Receipt method account end date
1977 3. Receipt method should have atleast one
1978 bank account with valid end dates
1979 4. Also bank account should be of invoice currency or
1980 multi currency enabled.
1981 5. and that valid bank account should have
1982 atleast one bank valid branch.
1983 6. Additionally If payment method creation is MANUAL or AUTOMATIC
1984 then the trx currency is as same as payment method currency or
1985 multi currency flag should be 'Y'
1986 7. For Automatic methods if Payment type is NOT CREDIT_CARD
1987 additionally the currency should be defined or associated
1988 with paying or bill to customer bank accounts.This condition is
1989 taken from paying customer payment method LOV.. to keep the
1990 both validations in sync.*/
1991
1992 SELECT 'invalid_payment method'
1993 INTO l_temp
1994 FROM ar_receipt_methods arm,
1995 ar_receipt_method_accounts arma,
1996 ce_bank_accounts cba,
1997 ce_bank_acct_uses aba,
1998 ar_receipt_classes arc,
1999 ce_bank_branches_v bp
2000 WHERE arm.receipt_method_id = arma.receipt_method_id
2001 AND arm.receipt_class_id = arc.receipt_class_id
2002 AND arma.remit_bank_acct_use_id = aba.bank_acct_use_id
2003 AND aba.bank_account_id = cba.bank_account_id
2004 /* New Condition added Begin*/
2005 AND bp.branch_party_id = cba.bank_branch_id
2006 AND p_trx_date <= NVL(bp.end_date,p_trx_date)
2007 AND (cba.currency_code = p_invoice_currency_code or
2008 cba.receipt_multi_currency_flag ='Y') /* New condition */
2009 /*Removing the join consition based on currency code as part of bug fix 5346710
2010 AND (arc.creation_method_code='MANUAL'
2011 or (arc.creation_method_code='AUTOMATIC'
2012 and ( (nvl(arm.payment_channel_code,'*') = 'CREDIT_CARD' )
2013 or
2014 (nvl(arm.payment_channel_code,'*') <> 'CREDIT_CARD'
2015 AND p_invoice_currency_code in
2016 (select currency_code from iby_fndcpt_payer_assgn_instr_v where
2017 party_id in (l_paying_customer_id,l_bill_to_customer_id))))))*/
2018 /* New Condition added Ends*/
2019 -- AND aba.set_of_books_id = arp_global.set_of_books_id
2020 AND arm.receipt_method_id = p_receipt_method_id
2021 AND p_trx_date < NVL(cba.end_date,
2022 TO_DATE('01/01/2200','DD/MM/YYYY') )
2023 AND p_trx_date BETWEEN NVL(arm.start_date,
2024 p_trx_date)
2025 AND NVL(arm.end_date,
2026 p_trx_date)
2027 AND p_trx_date BETWEEN NVL(arma.start_date,
2028 p_trx_date)
2029 AND NVL(arma.end_date,
2030 p_trx_date)
2031 AND rownum = 1;
2032
2033 EXCEPTION
2034 WHEN NO_DATA_FOUND THEN
2035 p_invalid_payment_mthd_flag := TRUE;
2036 p_result_flag := FALSE;
2037
2038 add_to_error_list(
2039 p_error_mode,
2040 p_error_count,
2041 p_customer_trx_id,
2042 p_trx_number,
2043 NULL,
2044 NULL,
2045 'AR_TW_BAD_DATE_PAYMENT_METHOD',
2046 'TGW_HEADER.ARM_RECEIPT_METHOD_NAME',
2047 NULL,
2048 NULL,
2049 NULL,
2050 NULL
2051 );
2052
2053 WHEN OTHERS THEN RAISE;
2054 END;
2055 END IF;
2056 END IF;
2057
2058
2059 /*--------------------------+
2060 | Validate Customer Bank |
2061 +--------------------------*/
2062
2063 arp_util.debug('Validate Customer Bank'|| 'not reqd any more');
2064 /* payment uptake removed validation for customer bank bug4646161 */
2065
2066
2067
2068 /*----------------------+
2069 | Validate Salesreps |
2070 +----------------------*/
2071
2072 arp_util.debug('Validate Salesreps');
2073
2074 IF ( p_customer_trx_id IS NOT NULL )
2075 THEN
2076 BEGIN
2077 l_temp := NULL;
2078
2079 SELECT MIN(s.name),
2080 TO_CHAR(MIN(ctl.line_number))
2081 INTO l_temp,
2082 l_temp2
2083 FROM ra_cust_trx_line_salesreps ls,
2084 ra_customer_trx_lines ctl,
2085 ra_salesreps s
2086 WHERE ls.salesrep_id = s.salesrep_id
2087 AND ls.customer_trx_id = p_customer_trx_id
2088 AND ls.customer_trx_line_id = ctl.customer_trx_line_id(+)
2089 AND p_trx_date NOT BETWEEN NVL(s.start_date_active,
2090 p_trx_date)
2091 AND NVL(s.end_date_active,
2092 p_trx_date);
2093
2094 IF ( l_temp || l_temp2 IS NOT NULL )
2095 THEN
2096 p_invalid_salesrep_flag := TRUE;
2097 p_result_flag := FALSE;
2098
2099
2100 /*----------------------------------------+
2101 | If no line number has been selected, |
2102 | this is a default salescredit line. |
2103 +----------------------------------------*/
2104
2105 IF (l_temp2 IS NOT NULL)
2106 THEN
2107
2108 /* Bug 2191739 - call to message API for degovtized message */
2109 add_to_error_list(
2110 p_error_mode,
2111 p_error_count,
2112 p_customer_trx_id,
2113 p_trx_number,
2114 l_temp2,
2115 NULL,
2116 gl_public_sector.get_message_name
2117 (p_message_name => 'AR_TW_BAD_DATE_SALESREP',
2118 p_app_short_name => 'AR'),
2119 'TGW_HEADER.TRX_DATE',
2120 'SALESREP_NAME',
2121 l_temp,
2122 'LINE_NUMBER',
2123 l_temp2
2124 );
2125
2126 ELSE
2127
2128 /* Bug 2191739 - call to message API for degovtized message */
2129 add_to_error_list(
2130 p_error_mode,
2131 p_error_count,
2132 p_customer_trx_id,
2133 p_trx_number,
2134 l_temp2,
2135 NULL,
2136 gl_public_sector.get_message_name
2137 (p_message_name => 'AR_TW_BAD_DATE_DEFAULT_SREP',
2138 p_app_short_name => 'AR'),
2139 'TGW_HEADER.TRX_DATE',
2140 'SALESREP_NAME',
2141 l_temp,
2142 NULL,
2143 NULL
2144 );
2145
2146 END IF;
2147
2148 END IF;
2149
2150 EXCEPTION
2151 WHEN NO_DATA_FOUND THEN NULL;
2152 WHEN OTHERS THEN RAISE;
2153 END;
2154 END IF;
2155
2156
2157 /*----------------------+
2158 | Validate Memo Line |
2159 +----------------------*/
2160
2161 arp_util.debug('Validate Memo Line');
2162
2163 IF ( p_customer_trx_id IS NOT NULL )
2164 THEN
2165 BEGIN
2166
2167 SELECT TO_CHAR(MIN(lines.line_number))
2168 INTO l_temp
2169 FROM ra_customer_trx_lines lines,
2170 ar_memo_lines aml
2171 WHERE lines.customer_trx_id = p_customer_trx_id
2172 AND lines.memo_line_id = aml.memo_line_id
2173 AND p_trx_date NOT BETWEEN NVL(aml.start_date, p_trx_date)
2174 AND NVL(aml.end_date, p_trx_date);
2175
2176 IF (l_temp IS NOT NULL )
2177 THEN
2178
2179 p_invalid_memo_line_flag := TRUE;
2180 p_result_flag := FALSE;
2181
2182 add_to_error_list(
2183 p_error_mode,
2184 p_error_count,
2185 p_customer_trx_id,
2186 p_trx_number,
2187 l_temp,
2188 NULL,
2189 'AR_TW_BAD_DATE_MEMO_LINE',
2190 'TGW_HEADER.TRX_DATE',
2191 'LINE_NUMBER',
2192 l_temp,
2193 NULL,
2194 NULL
2195 );
2196 END IF;
2197
2198
2199 EXCEPTION
2200 WHEN NO_DATA_FOUND THEN NULL;
2201 WHEN OTHERS THEN RAISE;
2202 END;
2203 END IF;
2204
2205 /*---------------------+
2206 | Validate tax code |
2207 +---------------------*/
2208
2209 /* 4594101 - Validation tests autotax rows (ar_vat_tax)
2210 and compares effective dates from table against trx_date.
2211 This logic is now handled by eTax. This validation has been
2212 removed. */
2213
2214 END IF;
2215
2216 p_due_date := arpt_sql_func_util.Get_First_Due_Date( p_term_id,
2217 p_trx_date );
2218
2219 arp_util.debug('ARP_TRX_VALIDATE.validate_trx_date()-');
2220
2221
2222 EXCEPTION
2223 WHEN OTHERS THEN
2224 arp_util.debug('EXCEPTION: ARP_TRX_VALIDATE.validate_trx_date()');
2225 RAISE;
2226
2227 END;
2228
2229 /*----------------------------------------------------------------------+
2230 | This overloaded call is provided for backward compatibility with |
2231 | an older version of this routine that did not have the p_error_mode |
2232 | parameter. |
2233 +----------------------------------------------------------------------*/
2234
2235 PROCEDURE validate_trx_date( p_trx_date IN DATE,
2236 p_prev_trx_date IN DATE,
2237 p_commitment_trx_date IN DATE,
2238 p_customer_trx_id IN NUMBER,
2239 p_trx_number IN VARCHAR2,
2240 p_previous_customer_trx_id IN NUMBER,
2241 p_initial_customer_trx_id IN NUMBER,
2242 p_agreement_id IN NUMBER,
2243 p_batch_source_id IN NUMBER,
2244 p_cust_trx_type_id IN NUMBER,
2245 p_term_id IN NUMBER,
2246 p_ship_method_code IN VARCHAR2,
2247 p_primary_salesrep_id IN NUMBER,
2248 p_reason_code IN VARCHAR2,
2249 p_status_trx IN VARCHAR2,
2250 p_invoice_currency_code IN VARCHAR2,
2251 p_receipt_method_id IN NUMBER,
2252 p_bank_account_id IN NUMBER,
2253 p_due_date OUT NOCOPY date,
2254 p_result_flag OUT NOCOPY boolean,
2255 p_commitment_invalid_flag OUT NOCOPY boolean,
2256 p_invalid_agreement_flag OUT NOCOPY boolean,
2257 p_invalid_source_flag OUT NOCOPY boolean,
2258 p_invalid_type_flag OUT NOCOPY boolean,
2259 p_invalid_term_flag OUT NOCOPY boolean,
2260 p_invalid_ship_method_flag OUT NOCOPY boolean,
2261 p_invalid_primary_srep_flag OUT NOCOPY boolean,
2262 p_invalid_reason_flag OUT NOCOPY boolean,
2263 p_invalid_status_flag OUT NOCOPY boolean,
2264 p_invalid_currency_flag OUT NOCOPY boolean,
2265 p_invalid_payment_mthd_flag OUT NOCOPY boolean,
2266 p_invalid_bank_flag OUT NOCOPY boolean,
2267 p_invalid_salesrep_flag OUT NOCOPY boolean,
2268 p_invalid_memo_line_flag OUT NOCOPY boolean,
2269 p_invalid_uom_flag OUT NOCOPY boolean,
2270 p_invalid_tax_flag OUT NOCOPY boolean,
2271 p_invalid_cm_date_flag OUT NOCOPY boolean,
2272 p_invalid_child_date_flag OUT NOCOPY boolean,
2273 p_error_count IN OUT NOCOPY integer
2274 ) IS
2275 BEGIN
2276
2277 validate_trx_date( 'NO_EXCEPTION',
2278 p_trx_date,
2279 p_prev_trx_date,
2280 p_commitment_trx_date,
2281 p_customer_trx_id,
2282 p_trx_number,
2283 p_previous_customer_trx_id,
2284 p_initial_customer_trx_id,
2285 p_agreement_id,
2286 p_batch_source_id,
2287 p_cust_trx_type_id,
2288 p_term_id,
2289 p_ship_method_code,
2290 p_primary_salesrep_id,
2291 p_reason_code,
2292 p_status_trx,
2293 p_invoice_currency_code,
2294 p_receipt_method_id,
2295 p_bank_account_id,
2296 p_due_date,
2297 p_result_flag,
2298 p_commitment_invalid_flag,
2299 p_invalid_agreement_flag,
2300 p_invalid_source_flag,
2301 p_invalid_type_flag,
2302 p_invalid_term_flag,
2303 p_invalid_ship_method_flag,
2304 p_invalid_primary_srep_flag,
2305 p_invalid_reason_flag,
2306 p_invalid_status_flag,
2307 p_invalid_currency_flag,
2308 p_invalid_payment_mthd_flag,
2309 p_invalid_bank_flag,
2310 p_invalid_salesrep_flag,
2311 p_invalid_memo_line_flag,
2312 p_invalid_uom_flag,
2313 p_invalid_tax_flag,
2314 p_invalid_cm_date_flag,
2315 p_invalid_child_date_flag,
2316 p_error_count);
2317
2318 END;
2319
2320 /*===========================================================================+
2321 | PROCEDURE |
2322 | val_gl_dist_amounts |
2323 | |
2324 | DESCRIPTION |
2325 | This procedure validates that the sum of the distribution amounts |
2326 | for a given transaction line is correct in each gl_date. The |
2327 | distributions with the header GL date must add up to the line's |
2328 | extended amount. The distributions with other GL dates must add up |
2329 | to zero for each GL date. |
2330 | |
2331 | SCOPE - PUBLIC |
2332 | |
2333 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2334 | arp_util.debug |
2335 | |
2336 | ARGUMENTS : IN: p_customer_trx_line_id |
2337 | OUT: |
2338 | p_result |
2339 | |
2340 | RETURNS : NONE |
2341 | |
2342 | NOTES |
2343 | |
2344 | MODIFICATION HISTORY |
2345 | 10-NOV-95 Martin Johnson Created |
2346 | 14-DEC-95 Martin Johnson Split error message into two messages |
2347 | |
2348 +===========================================================================*/
2349
2350 PROCEDURE val_gl_dist_amounts(
2351 p_customer_trx_line_id IN NUMBER,
2352 p_result OUT NOCOPY boolean ) IS
2353
2354 l_d_gl_date date;
2355 l_gl_date date;
2356 l_rec_gl_date date;
2357 l_amount number;
2358
2359 /*--------------------------------------------------+
2360 | This sql will return no rows if data is valid. |
2361 | Needs to be a cursor to avoid error if multi |
2362 | rows rturned. |
2363 +--------------------------------------------------*/
2364
2365 /*--------------------------------------------------+
2366 | Bug 1332304. |
2367 | Reverting the change done in patch 959747. |
2368 | We need to pass the validation if either the |
2369 | percent or the amount are correct. |
2370 +--------------------------------------------------*/
2371
2372 CURSOR val_gl_dist_amounts IS
2373
2374 SELECT NVL(d.gl_date, rec.gl_date),
2375 DECODE(NVL(d.gl_date, rec.gl_date),
2376 rec.gl_date, l.extended_amount,
2377 0),
2378 d.gl_date,
2379 rec.gl_date
2380 FROM ra_cust_trx_line_gl_dist rec,
2381 ra_cust_trx_line_gl_dist d,
2382 ra_customer_trx_lines l,
2383 ra_customer_trx t
2384 WHERE l.customer_trx_line_id = d.customer_trx_line_id(+)
2385 AND l.customer_trx_line_id = p_customer_trx_line_id
2386 AND l.customer_trx_id = t.customer_trx_id
2387 AND rec.customer_trx_id = l.customer_trx_id
2388 AND rec.account_class = 'REC'
2389 AND rec.latest_rec_flag = 'Y'
2390 AND d.account_set_flag(+) = 'N'
2391 GROUP by d.customer_trx_line_id,
2392 d.gl_date,
2393 rec.gl_date,
2394 l.extended_amount
2395 HAVING (
2396 (
2397 SUM(d.amount) <> DECODE( nvl(d.gl_date, rec.gl_date),
2398 rec.gl_date, l.extended_amount,
2399 0)
2400 )
2401 AND -- Changed 'OR' into 'AND'. Bug 1332304.
2402 (
2403 SUM(d.percent) <> DECODE( nvl(d.gl_date, rec.gl_date),
2404 rec.gl_date, 100,
2405 0)
2406 )
2407 ) OR
2408 SUM(d.cust_trx_line_gl_dist_id) IS NULL
2409 ORDER BY d.gl_date;
2410
2411 BEGIN
2412
2413 IF PG_DEBUG in ('Y', 'C') THEN
2414 arp_util.debug('ARP_TRX_VALIDATE.val_gl_dist_amounts()+');
2415 END IF;
2416
2417 OPEN val_gl_dist_amounts;
2418
2419 FETCH val_gl_dist_amounts
2420 INTO l_gl_date,
2421 l_amount,
2422 l_d_gl_date,
2423 l_rec_gl_date;
2424
2425 IF ( val_gl_dist_amounts%NOTFOUND )
2426 THEN
2427 CLOSE val_gl_dist_amounts;
2428 p_result := TRUE;
2429
2430 ELSE
2431
2432 CLOSE val_gl_dist_amounts;
2433 p_result := FALSE;
2434
2435 IF ( l_gl_date = l_rec_gl_date )
2436 THEN
2437 fnd_message.set_name('AR', 'AR_TW_ACC_ASSGN_SUM_REC');
2438 fnd_message.set_token('GL_DATE',
2439 TO_CHAR(l_gl_date, 'DD-MON-YYYY'));
2440
2441 ELSE
2442 fnd_message.set_name('AR', 'AR_TW_ACC_ASSGN_SUM_ZERO');
2443 fnd_message.set_token('GL_DATE',
2444 TO_CHAR(l_d_gl_date, 'DD-MON-YYYY'));
2445
2446 END IF;
2447
2448 app_exception.raise_exception;
2449
2450 END IF;
2451
2452 IF PG_DEBUG in ('Y', 'C') THEN
2453 arp_util.debug('ARP_TRX_VALIDATE.val_gl_dist_amounts()-');
2454 END IF;
2455
2456 EXCEPTION
2457 WHEN OTHERS
2458 THEN
2459 IF PG_DEBUG in ('Y', 'C') THEN
2460 arp_util.debug('EXCEPTION: ARP_TRX_VALIDATE.val_gl_dist_amounts()');
2461 arp_util.debug('val_gl_dist_amounts: ' || ' p_customer_trx_line_id = ' ||
2462 p_customer_trx_line_id );
2463 arp_util.debug('val_gl_dist_amounts: ' || ' l_gl_date = ' || l_gl_date );
2464 arp_util.debug('val_gl_dist_amounts: ' || ' l_amount = ' || l_amount );
2465 END IF;
2466 RAISE;
2467
2468 END val_gl_dist_amounts;
2469
2470
2471 /*===========================================================================+
2472 | PROCEDURE |
2473 | val_and_dflt_pay_mthd_and_bank() |
2474 | |
2475 | DESCRIPTION |
2476 | Validates the payment method and the bank columns. |
2477 | If either is invalid, they are redefaulted. |
2478 | |
2479 | SCOPE - PUBLIC |
2480 | |
2481 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2482 | arp_util.debug |
2483 | arp_process_credit_util.check_payment_method |
2484 | arp_process_credit_util.check_bank_account |
2485 | |
2486 | ARGUMENTS : IN: |
2487 | p_trx_date |
2488 | p_currency_code |
2489 | p_paying_customer_id |
2490 | p_paying_site_use_id |
2491 | p_bill_to_customer_id |
2492 | p_bill_to_site_use_id |
2493 | p_in_receipt_method_id |
2494 | p_in_customer_bank_account_id |
2495 | OUT: |
2496 | p_payment_method_name |
2497 | p_receipt_method_id |
2498 | p_creation_method_code |
2499 | p_customer_bank_account_id |
2500 | p_bank_account_num |
2501 | p_bank_name |
2502 | p_bank_branch_name |
2503 | p_bank_branch_id |
2504 | |
2505 | RETURNS : NONE |
2506 | |
2507 | NOTES |
2508 | |
2509 | MODIFICATION HISTORY |
2510 | 08-FEB-96 Charlie Tomberg Created |
2511 | 23-JUN-99 Ajay Pandit Modified the procedure for fixing |
2512 | bug no 913071 so that the defaulting |
2513 | and validation for the bank accounts |
2514 | is not done when payment method is |
2515 | MANUAL |
2516 | 06-OCT-04 Surendra Rajan Added parameter p_trx_manual_flag to |
2517 | fix bug 3770337 |
2518 +===========================================================================*/
2519
2520 PROCEDURE val_and_dflt_pay_mthd_and_bank(
2521 p_trx_date IN date,
2522 p_currency_code IN varchar2,
2523 p_paying_customer_id IN number,
2524 p_paying_site_use_id IN number,
2525 p_bill_to_customer_id IN number,
2526 p_bill_to_site_use_id IN number,
2527 p_in_receipt_method_id IN number,
2528 p_in_customer_bank_account_id IN number,
2529 p_payment_type_code IN varchar2,
2530 p_payment_method_name OUT NOCOPY varchar2,
2531 p_receipt_method_id OUT NOCOPY number,
2532 p_creation_method_code OUT NOCOPY varchar2,
2533 p_customer_bank_account_id OUT NOCOPY number,
2534 p_bank_account_num OUT NOCOPY varchar2,
2535 p_bank_name OUT NOCOPY varchar2,
2536 p_bank_branch_name OUT NOCOPY varchar2,
2537 p_bank_branch_id OUT NOCOPY number,
2538 p_trx_manual_flag IN VARCHAR2 DEFAULT 'N'
2539 ) IS
2540
2541 l_dummy integer;
2542
2543 BEGIN
2544
2545 IF PG_DEBUG in ('Y', 'C') THEN
2546 arp_util.debug('arp_trx_validate.val_and_dflt_pay_mthd_and_bank()+');
2547 END IF;
2548
2549 IF ( arp_process_credit_util.check_payment_method
2550 (
2551 p_trx_date,
2552 p_bill_to_customer_id,
2553 p_bill_to_site_use_id,
2554 p_paying_customer_id,
2555 p_paying_site_use_id,
2556 p_currency_code,
2557 l_dummy,
2558 p_payment_method_name,
2559 p_receipt_method_id,
2560 p_creation_method_code
2561 ) = FALSE )
2562 THEN
2563
2564 arp_trx_defaults_3.get_payment_method_default(
2565 p_trx_date,
2566 p_currency_code,
2567 p_paying_customer_id,
2568 p_paying_site_use_id,
2569 p_bill_to_customer_id,
2570 p_bill_to_site_use_id,
2571 p_payment_method_name,
2572 p_receipt_method_id,
2573 p_creation_method_code,
2574 p_trx_manual_flag
2575 );
2576 END IF;
2577 /*Bug 913072 : */
2578 IF (p_creation_method_code = 'MANUAL'or p_creation_method_code IS NULL) THEN /*Bug 3312212*/
2579 p_customer_bank_account_id := NULL;
2580 p_bank_account_num := NULL;
2581 p_bank_name := NULL;
2582 p_bank_branch_name := NULL;
2583 p_bank_branch_id := NULL;
2584 ELSE
2585
2586 IF ( arp_process_credit_util.check_bank_account(
2587 p_trx_date,
2588 p_currency_code,
2589 p_bill_to_customer_id,
2590 p_bill_to_site_use_id,
2591 p_paying_customer_id,
2592 p_paying_site_use_id,
2593 l_dummy,
2594 p_customer_bank_account_id,
2595 l_dummy) = FALSE )
2596 THEN
2597
2598 arp_trx_defaults_3.get_bank_defaults(
2599 p_trx_date,
2600 p_currency_code,
2601 p_paying_customer_id,
2602 p_paying_site_use_id,
2603 p_bill_to_customer_id,
2604 p_bill_to_site_use_id,
2605 p_payment_type_code,
2606 p_customer_bank_account_id,
2607 p_bank_account_num,
2608 p_bank_name,
2609 p_bank_branch_name,
2610 p_bank_branch_id,
2611 p_trx_manual_flag
2612 );
2613 END IF;
2614 END IF;
2615
2616 IF PG_DEBUG in ('Y', 'C') THEN
2617 arp_util.debug('arp_trx_validater.val_and_dflt_pay_mthd_and_bank()-');
2618 END IF;
2619
2620 EXCEPTION
2621 WHEN NO_DATA_FOUND THEN NULL;
2622 WHEN OTHERS THEN
2623 IF PG_DEBUG in ('Y', 'C') THEN
2624 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' ||
2625 'EXCEPTION: arp_process_header.val_and_dflt_pay_mthd_and_bank()');
2626 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' ||
2627 '------- parameters for val_and_dflt_pay_mthd_and_bank ----');
2628 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_trx_date = ' ||
2629 TO_CHAR(p_trx_date) );
2630 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_currency_code = ' ||
2631 p_currency_code );
2632 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_paying_customer_id = ' ||
2633 p_paying_customer_id );
2634 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_paying_site_use_id = ' ||
2635 p_paying_site_use_id );
2636 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_bill_to_customer_id = ' ||
2637 p_bill_to_customer_id );
2638 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_bill_to_site_use_id = ' ||
2639 p_bill_to_site_use_id );
2640 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_in_receipt_method_id = ' ||
2641 p_in_receipt_method_id );
2642 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_in_customer_bank_account_id = ' ||
2643 p_in_customer_bank_account_id );
2644 END IF;
2645
2646 RAISE;
2647
2648 END val_and_dflt_pay_mthd_and_bank;
2649
2650
2651 /*===========================================================================+
2652 | PROCEDURE |
2653 | do_completion_checking() |
2654 | |
2655 | DESCRIPTION |
2656 | Checks if the transaction can be completed. |
2657 | |
2658 | The following checks are performed: |
2659 | - Insure that at least one line or freight line exists. |
2660 | - Insure that all entities that have start / end dates are valid for |
2661 | the specified trx date. |
2662 | - Insure that if a commitment has been specified, it is valid with |
2663 | the transaction's trx_date and gl_date |
2664 | - If salescredits are required, the total salescredits for each line |
2665 | must equal 100% of the line amount. |
2666 | - If salescredits are not required, either no salescredits exist for |
2667 | a line or they sum to 100%. |
2668 | - Check the existance and validtity of account assignments or |
2669 | account sets: |
2670 | Constraints: |
2671 | - records exists for each line |
2672 | - all code combinations are valid |
2673 | - For account assignments, the sum of the assignment amounts |
2674 | must equal the line amount. |
2675 | - For account sets, the sum of the percents for each line and |
2676 | account class must equal 100%. |
2677 | - If an invoicing rule has been specified, verify that all lines |
2678 | have accounting rules and rule start dates. |
2679 | - If TAX_CALCULATION_FLAG is Yes, then tax is required for all invoice |
2680 | lines unless it's a memo line not of type LINE. |
2681 | - Tax is also required if TAX_CALCULATION_FLAG is No and |
2682 | TAX_EXEMPT_FLAG is Require. |
2683 | - Check the creation sign of the transaction |
2684 | - Verify that the GL Date is in an Opened, Future or |
2685 | Never Opened (Arrears only) Period. |
2686 | |
2687 | The following validations only apply to credit memos against |
2688 | transactions. |
2689 | |
2690 | - Check for illegal overapplications. |
2691 | - The GL Date must be >= the credited transaction's GL Date. |
2692 | - There can be no later credit memos applied to the same transaction. |
2693 | |
2694 | SCOPE - PUBLIC |
2695 | |
2696 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2697 | arp_bal_util.get_commitment_balance |
2698 | arp_ct_pkg.fetch_p |
2699 | arp_non_db_pkg.check_creation_sign |
2700 | arp_non_db_pkg.check_natural_application |
2701 | arp_trx_global.profile_info.use_inv_acct_for_cm_flag |
2702 | arp_trx_util.get_summary_trx_balances |
2703 | arp_trx_validate.validate_trx_date |
2704 | arp_util.debug |
2705 | arp_util.validate_and_default_gl_date |
2706 | |
2707 | ARGUMENTS : IN: |
2708 | p_customer_trx_id |
2709 | p_so_source_code |
2710 | p_so_installed_flag |
2711 | |
2712 | OUT: |
2713 | p_error_count |
2714 | |
2715 | RETURNS : p_error_count |
2716 | |
2717 | NOTES |
2718 | |
2719 | MODIFICATION HISTORY |
2720 | 06-DEC-95 Charlie Tomberg Created |
2721 | |
2722 +===========================================================================*/
2723
2724 PROCEDURE do_completion_checking(
2725 p_customer_trx_id IN NUMBER,
2726 p_so_source_code IN varchar2,
2727 p_so_installed_flag IN varchar2,
2728 p_error_mode IN VARCHAR2,
2729 p_error_count OUT NOCOPY number
2730 ) IS
2731
2732
2733 BEGIN
2734 arp_trx_complete_chk.do_completion_checking(
2735 p_customer_trx_id,
2736 p_so_source_code,
2737 p_so_installed_flag,
2738 p_error_mode,
2739 p_error_count);
2740 END;
2741
2742 PROCEDURE init IS
2743 BEGIN
2744
2745 pg_base_curr_code := arp_global.functional_currency;
2746 pg_salesrep_required_flag :=
2747 arp_trx_global.system_info.system_parameters.salesrep_required_flag;
2748 pg_set_of_books_id :=
2749 arp_trx_global.system_info.system_parameters.set_of_books_id;
2750 END init;
2751
2752 BEGIN
2753 init;
2754 END ARP_TRX_VALIDATE;