[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_VALIDATE
Source
1 PACKAGE BODY ARP_TRX_VALIDATE AS
2 /* $Header: ARTUVALB.pls 120.27.12010000.2 2009/08/27 13:09:52 rasarasw 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 EXISTS
1135 (
1136 SELECT 'X'
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 AND CUST_ACCT.CUST_ACCOUNT_ID = CR.CUST_ACCOUNT_ID
1142 UNION ALL
1143 SELECT 'X'
1144 FROM dual
1145 where cust_acct.cust_account_id = TO_NUMBER(p_ct_prev_paying_customer_id)
1146 UNION ALL
1147 SELECT 'X'
1148 FROM dual
1149 WHERE cust_acct.cust_account_id =TO_NUMBER(p_bill_to_customer_id)
1150 UNION ALL
1151 SELECT 'X'
1152 FROM ar_paying_relationships_v rel,
1153 hz_cust_accounts acc
1154 WHERE rel.party_id = acc.party_id
1155 AND rel.related_cust_account_id = p_bill_to_customer_id
1156 AND p_ct_prev_trx_date BETWEEN effective_start_date
1157 AND effective_end_date
1158 AND CUST_ACCT.CUST_ACCOUNT_ID = ACC.CUST_ACCOUNT_ID
1159 );
1160
1161 END IF;
1162
1163 EXCEPTION
1164 WHEN no_data_found THEN
1165 l_paying_customer_is_valid := 'N';
1166
1167 END ;
1168
1169
1170 IF (l_paying_customer_is_valid = 'Y')
1171 THEN RETURN(TRUE);
1172 ELSE RETURN(FALSE);
1173 END IF;
1174
1175 EXCEPTION
1176 WHEN OTHERS THEN RAISE;
1177 END;
1178
1179 arp_util.debug('ARP_TRX_VALIDATE.validate_paying_customer()-');
1180
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 arp_util.debug('EXCEPTION: ARP_TRX_VALIDATE.validate_paying_customer()');
1184 RAISE;
1185
1186 END;
1187
1188 /*===========================================================================+
1189 | PROCEDURE |
1190 | validate_trx_date() |
1191 | |
1192 | DESCRIPTION |
1193 | Validates that all entities that have date ranges are still valid after|
1194 | the transaction date changes. |
1195 | |
1196 | SCOPE - PUBLIC |
1197 | |
1198 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1199 | arp_util.debug |
1200 | |
1201 | ARGUMENTS : IN: |
1202 | p_trx_date |
1203 | p_prev_trx_date |
1204 | p_commitment_trx_date |
1205 | p_customer_trx_id |
1206 | p_customer_trx_id |
1207 | p_previous_customer_trx_id |
1208 | p_initial_customer_trx_id |
1209 | p_agreement_id |
1210 | p_batch_source_id |
1211 | p_cust_trx_type_id |
1212 | p_term_id |
1213 | p_ship_method_code |
1214 | p_primary_salesrep_id |
1215 | p_reason_code |
1216 | p_status_trx |
1217 | p_invoice_currency_code |
1218 | p_receipt_method_id |
1219 | p_bank_account_id |
1220 | OUT: |
1221 | p_due_date |
1222 | p_result_flag |
1223 | p_commitment_invalid_flag |
1224 | p_invalid_agreement_flag |
1225 | p_invalid_source_flag |
1226 | p_invalid_type_flag |
1227 | p_invalid_term_flag |
1228 | p_invalid_ship_method_flag |
1229 | p_invalid_primary_srep_flag |
1230 | p_invalid_reason_flag |
1231 | p_invalid_status_flag |
1232 | p_invalid_currency_flag |
1233 | p_invalid_payment_mthd_flag |
1234 | p_invalid_bank_flag |
1235 | p_invalid_salesrep_flag |
1236 | p_invalid_memo_line_flag |
1237 | p_invalid_uom_flag |
1238 | p_invalid_tax_flag |
1239 | p_invalid_cm_date_flag |
1240 | p_invalid_child_date_flag |
1241 | |
1242 | IN / OUT: |
1243 | p_error_count |
1244 | RETURNS : NONE |
1245 | |
1246 | NOTES |
1247 | |
1248 | MODIFICATION HISTORY |
1249 | 31-OCT-95 Charlie Tomberg Created |
1250 | 18-MAR-96 Martin Johnson Removed validation of UOM since it |
1251 | is no longer validated against trx_date|
1252 | (part of BugNo:337819) |
1253 | 22-MAY-2000 J Rautiainen BR Implementation |
1254 | 28-SEP-2001 M Raymond Performance problem in validate_trx_date
1255 | caused by FTS of ra_cust_receipt_methods
1256 | See bug 2001878 for details. |
1257 | 18-JAN-2002 M Raymond Fix for 2001878 introduced a requirement
1258 | for payment methods at site level.
1259 | Revised fix to allow for null methods
1260 | at site level. See bug 2176210 for det.
1261 | 22-FEB-2002 M Raymond Bug 2195793 - added validation for
1262 | commitments with zero balance.
1263 | 02-MAY-2002 M Raymond Bug 2340543 - Validation for 2195793
1264 | should not fire for credit memos.
1265 |
1266 +===========================================================================*/
1267
1268 PROCEDURE validate_trx_date( p_error_mode IN VARCHAR2,
1269 p_trx_date IN DATE,
1270 p_prev_trx_date IN DATE,
1271 p_commitment_trx_date IN DATE,
1272 p_customer_trx_id IN NUMBER,
1273 p_trx_number IN VARCHAR2,
1274 p_previous_customer_trx_id IN NUMBER,
1275 p_initial_customer_trx_id IN NUMBER,
1276 p_agreement_id IN NUMBER,
1277 p_batch_source_id IN NUMBER,
1278 p_cust_trx_type_id IN NUMBER,
1279 p_term_id IN NUMBER,
1280 p_ship_method_code IN VARCHAR2,
1281 p_primary_salesrep_id IN NUMBER,
1282 p_reason_code IN VARCHAR2,
1283 p_status_trx IN VARCHAR2,
1284 p_invoice_currency_code IN VARCHAR2,
1285 p_receipt_method_id IN NUMBER,
1286 p_bank_account_id IN NUMBER,
1287 p_due_date OUT NOCOPY date,
1288 p_result_flag OUT NOCOPY boolean,
1289 p_commitment_invalid_flag OUT NOCOPY boolean,
1290 p_invalid_agreement_flag OUT NOCOPY boolean,
1291 p_invalid_source_flag OUT NOCOPY boolean,
1292 p_invalid_type_flag OUT NOCOPY boolean,
1293 p_invalid_term_flag OUT NOCOPY boolean,
1294 p_invalid_ship_method_flag OUT NOCOPY boolean,
1295 p_invalid_primary_srep_flag OUT NOCOPY boolean,
1296 p_invalid_reason_flag OUT NOCOPY boolean,
1297 p_invalid_status_flag OUT NOCOPY boolean,
1298 p_invalid_currency_flag OUT NOCOPY boolean,
1299 p_invalid_payment_mthd_flag OUT NOCOPY boolean,
1300 p_invalid_bank_flag OUT NOCOPY boolean,
1301 p_invalid_salesrep_flag OUT NOCOPY boolean,
1302 p_invalid_memo_line_flag OUT NOCOPY boolean,
1303 p_invalid_uom_flag OUT NOCOPY boolean,
1304 p_invalid_tax_flag OUT NOCOPY boolean,
1305 p_invalid_cm_date_flag OUT NOCOPY boolean,
1306 p_invalid_child_date_flag OUT NOCOPY boolean,
1307 p_error_count IN OUT NOCOPY integer
1308 ) IS
1309
1310 l_temp varchar2(128);
1311 l_temp2 varchar2(128);
1312 l_commit_bal NUMBER;
1313 l_so_source_code varchar2(100);
1314
1315 /*--------------------------------------------------------------------+
1316 | 23-MAY-2000 J Rautiainen BR Implementation |
1317 | BR payment method does not have bank account associated with it |
1318 | This cursor is used to branch code into a correct select statement |
1319 | in the payment method validation. |
1320 +--------------------------------------------------------------------*/
1321 CURSOR receipt_creation_method_cur IS
1322 SELECT arc.creation_method_code
1323 FROM ar_receipt_methods arm,
1324 ar_receipt_classes arc
1325 WHERE arm.receipt_class_id = arc.receipt_class_id
1326 AND arm.receipt_method_id = p_receipt_method_id;
1327
1328 receipt_creation_method_rec receipt_creation_method_cur%ROWTYPE;
1329
1330 /*Bug3348454 */
1331 l_bill_to_customer_id ra_customer_trx.bill_to_customer_id%TYPE;
1332 l_paying_customer_id ra_customer_trx.paying_customer_id%TYPE;
1333
1334 BEGIN
1335
1336 arp_util.debug('ARP_TRX_VALIDATE.validate_trx_date()+');
1337
1338 p_result_flag := TRUE;
1339
1340 /*------------------------------------------------------------------+
1341 | Validate that CM Date is >= the credited transaction's trx_date |
1342 +------------------------------------------------------------------*/
1343
1344 arp_util.debug('Validate that CM Date is >= the credited transaction''s '||
1345 'trx_date');
1346
1347 IF ( p_trx_date < p_prev_trx_date )
1348 THEN
1349 p_invalid_cm_date_flag := TRUE;
1350 p_result_flag := FALSE;
1351
1352 add_to_error_list(
1353 p_error_mode,
1354 p_error_count,
1355 p_customer_trx_id,
1356 p_trx_number,
1357 NULL,
1358 NULL,
1359 'AR_TW_BAD_DATE_CM_DATE',
1360 'TGW_HEADER.TRX_DATE',
1361 NULL,
1362 NULL,
1363 NULL,
1364 NULL
1365 );
1366
1367 END IF;
1368
1369 /*---------------------------------------------------------------+
1370 | Validate that the Child Date is >= the commitment's trx_date |
1371 +----------------------------------------------------------------*/
1372
1373 arp_util.debug(
1374 'Validate that the Child Date is >= the commitment''s trx_date');
1375
1376 IF ( p_trx_date < p_commitment_trx_date )
1377 THEN
1378 p_invalid_child_date_flag := TRUE;
1379 p_result_flag := FALSE;
1380
1381
1382 add_to_error_list(
1383 p_error_mode,
1384 p_error_count,
1385 p_customer_trx_id,
1386 p_trx_number,
1387 NULL,
1388 NULL,
1389 'AR_TW_BAD_DATE_CHILD_DATE',
1390 'TGW_HEADER.TRX_DATE',
1391 NULL,
1392 NULL,
1393 NULL,
1394 NULL
1395 );
1396
1397
1398 END IF;
1399
1400 /*-----------------------+
1401 | Validate Commitment |
1402 +-----------------------*/
1403
1404 arp_util.debug('Validate Commitment');
1405
1406 /* Bug 2340543 - Do not test commitment balance or date for
1407 credit memos */
1408 IF ( p_initial_customer_trx_id IS NOT NULL AND
1409 p_previous_customer_trx_id IS NULL)
1410 THEN
1411
1412 /* Bug 2195793 - We have determined that it is a problem if
1413 you have an invoice pointing to a commitment if that
1414 commitment was exhausted by prior transactions.
1415
1416 This could be caused by invoice copy (where commitment
1417 gets exhausted before last invoice is created) or if
1418 you leave invoices incomplete with a commitment assigned
1419 to them.
1420
1421 To prevent this, we added a call to arp_bal_util to
1422 get the commitment balance at that moment. If it is
1423 exhausted, we will flag an error. */
1424
1425 BEGIN
1426 oe_profile.get( 'SO_SOURCE_CODE', l_so_source_code );
1427
1428 SELECT 'invoice date is ok',
1429 arp_bal_util.get_commitment_balance(
1430 p_initial_customer_trx_id,
1431 tt.TYPE,
1432 l_so_source_code,
1433 'N')
1434 INTO l_temp,
1435 l_commit_bal
1436 FROM ra_customer_trx t,
1437 ra_cust_trx_types tt
1438 WHERE t.customer_trx_id = p_initial_customer_trx_id
1439 AND t.cust_trx_type_id = tt.cust_trx_type_id
1440 AND p_trx_date
1441 BETWEEN NVL( t.start_date_commitment, p_trx_date )
1442 AND NVL( t.end_date_commitment, p_trx_date );
1443
1444 IF (l_commit_bal = 0)
1445 THEN
1446 p_commitment_invalid_flag := TRUE;
1447 p_result_flag := FALSE;
1448
1449 add_to_error_list(
1450 p_error_mode,
1451 p_error_count,
1452 p_customer_trx_id,
1453 p_trx_number,
1454 NULL,
1455 NULL,
1456 'AR_ZERO_COMMITMENT',
1457 'TGW_HEADER.CT_COMMITMENT_NUMBER',
1458 NULL,
1459 NULL,
1460 NULL,
1461 NULL
1462 );
1463
1464 END IF;
1465
1466 EXCEPTION
1467 WHEN NO_DATA_FOUND THEN
1468 p_commitment_invalid_flag := TRUE;
1469 p_result_flag := FALSE;
1470
1471 add_to_error_list(
1472 p_error_mode,
1473 p_error_count,
1474 p_customer_trx_id,
1475 p_trx_number,
1476 NULL,
1477 NULL,
1478 'AR_TW_BAD_DATE_COMMITMENT',
1479 'TGW_HEADER.CT_COMMITMENT_NUMBER',
1480 NULL,
1481 NULL,
1482 NULL,
1483 NULL
1484 );
1485
1486 WHEN OTHERS THEN RAISE;
1487 END;
1488
1489 END IF;
1490
1491
1492 /*----------------------+
1493 | Validate Agreement |
1494 +----------------------*/
1495
1496 arp_util.debug('Validate Agreement');
1497
1498 IF (p_agreement_id IS NOT NULL)
1499 THEN
1500 BEGIN
1501 SELECT 'invoice date is ok'
1502 INTO l_temp
1503 FROM so_agreements
1504 WHERE agreement_id = p_agreement_id
1505 AND p_trx_date BETWEEN NVL(TRUNC(start_date_active),
1506 p_trx_date )
1507 AND NVL(TRUNC(end_date_active),
1508 p_trx_date);
1509
1510 EXCEPTION
1511 WHEN NO_DATA_FOUND THEN
1512 p_invalid_agreement_flag := TRUE;
1513 p_result_flag := FALSE;
1514
1515 add_to_error_list(
1516 p_error_mode,
1517 p_error_count,
1518 p_customer_trx_id,
1519 p_trx_number,
1520 NULL,
1521 NULL,
1522 'AR_TW_BAD_DATE_AGREEMENT',
1523 'TGW_HEADER.SOA_AGREEMENT_NAME',
1524 NULL,
1525 NULL,
1526 NULL,
1527 NULL
1528 );
1529
1530 WHEN OTHERS THEN RAISE;
1531 END;
1532 END IF;
1533
1534 /*-------------------------+
1535 | Validate Batch Source |
1536 +-------------------------*/
1537
1538 arp_util.debug('Validate Batch Source');
1539
1540 IF (p_batch_source_id IS NOT NULL)
1541 THEN
1542 BEGIN
1543
1544 SELECT 'invoice date is ok'
1545 INTO l_temp
1546 FROM ra_batch_sources
1547 WHERE batch_source_id = p_batch_source_id
1548 AND p_trx_date BETWEEN NVL(start_date, p_trx_date)
1549 AND NVL(end_date, p_trx_date);
1550
1551 EXCEPTION
1552 WHEN NO_DATA_FOUND THEN
1553 p_invalid_source_flag := TRUE;
1554 p_result_flag := FALSE;
1555
1556 add_to_error_list(
1557 p_error_mode,
1558 p_error_count,
1559 p_customer_trx_id,
1560 p_trx_number,
1561 NULL,
1562 NULL,
1563 'AR_TW_BAD_DATE_SOURCE',
1564 'TGW_HEADER.BS_BATCH_SOURCE_NAME',
1565 NULL,
1566 NULL,
1567 NULL,
1568 NULL
1569 );
1570
1571 WHEN OTHERS THEN RAISE;
1572 END;
1573 END IF;
1574
1575
1576 /*-----------------+
1577 | Validate Type |
1578 +-----------------*/
1579
1580 arp_util.debug('Validate Type');
1581
1582 IF ( p_cust_trx_type_id IS NOT NULL )
1583 THEN
1584 BEGIN
1585 SELECT tax_calculation_flag
1586 INTO l_temp
1587 FROM ra_cust_trx_types
1588 WHERE CUST_TRX_TYPE_ID = p_cust_trx_type_id
1589 AND p_trx_date BETWEEN START_DATE
1590 AND NVL(END_DATE, p_trx_date);
1591
1592
1593 EXCEPTION
1594 WHEN NO_DATA_FOUND THEN
1595 p_invalid_type_flag := TRUE;
1596 p_result_flag := FALSE;
1597
1598 add_to_error_list(
1599 p_error_mode,
1600 p_error_count,
1601 p_customer_trx_id,
1602 p_trx_number,
1603 NULL,
1604 NULL,
1605 'AR_TW_BAD_DATE_TRX_TYPE',
1606 'TGW_HEADER.CTT_TYPE_NAME',
1607 NULL,
1608 NULL,
1609 NULL,
1610 NULL
1611 );
1612
1613 WHEN OTHERS THEN RAISE;
1614 END;
1615 END IF;
1616
1617
1618
1619 /*------------------+
1620 | Validate Terms |
1621 +------------------*/
1622
1623 arp_util.debug('Validate Terms');
1624
1625 IF ( p_term_id IS NOT NULL )
1626 THEN
1627 BEGIN
1628
1629 SELECT 'invoice date is ok'
1630 INTO l_temp
1631 FROM ra_terms
1632 WHERE term_id = p_term_id
1633 AND p_trx_date BETWEEN START_DATE_ACTIVE
1634 AND NVL(END_DATE_ACTIVE, p_trx_date);
1635
1636 EXCEPTION
1637 WHEN NO_DATA_FOUND THEN
1638 p_invalid_term_flag := TRUE;
1639 p_result_flag := FALSE;
1640
1641 add_to_error_list(
1642 p_error_mode,
1643 p_error_count,
1644 p_customer_trx_id,
1645 p_trx_number,
1646 NULL,
1647 NULL,
1648 'AR_TW_BAD_DATE_TERM',
1649 'TGW_HEADER.RAT_TERM_NAME',
1650 NULL,
1651 NULL,
1652 NULL,
1653 NULL
1654 );
1655
1656 WHEN OTHERS THEN RAISE;
1657 END;
1658 END IF;
1659
1660
1661
1662 /*---------------------+
1663 | Validate ship via |
1664 +---------------------*/
1665
1666 arp_util.debug('Validate ship via');
1667
1668 IF ( p_ship_method_code IS NOT NULL )
1669 THEN
1670
1671 BEGIN
1672
1673 SELECT 'invoice date is ok'
1674 INTO l_temp
1675 FROM ORG_FREIGHT
1676 WHERE freight_code = p_ship_method_code
1677 AND organization_id =
1678 to_number(oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id))
1679 AND p_trx_date < NVL(TRUNC(DISABLE_DATE), p_trx_date + 1);
1680
1681 EXCEPTION
1682 WHEN NO_DATA_FOUND THEN
1683 p_invalid_ship_method_flag := TRUE;
1684 p_result_flag := FALSE;
1685
1686 add_to_error_list(
1687 p_error_mode,
1688 p_error_count,
1689 p_customer_trx_id,
1690 p_trx_number,
1691 NULL,
1692 NULL,
1693 'AR_TW_BAD_DATE_SHIP_METHOD',
1694 'TFRT_HEADER.of_ship_via_description',
1695 NULL,
1696 NULL,
1697 NULL,
1698 NULL
1699 );
1700
1701 WHEN OTHERS THEN RAISE;
1702 END;
1703 END IF;
1704
1705
1706 /*---------------------+
1707 | Validate CM Reason |
1708 +---------------------*/
1709
1710 arp_util.debug('Validate CM Reason');
1711
1712 IF ( p_previous_customer_trx_id IS NOT NULL
1713 AND
1714 p_reason_code IS NOT NULL
1715 )
1716 THEN
1717 BEGIN
1718
1719 SELECT 'reason code is ok'
1720 INTO l_temp
1721 FROM ar_lookups
1722 WHERE lookup_type = 'CREDIT_MEMO_REASON'
1723 AND lookup_code = p_reason_code
1724 AND p_trx_date
1725 BETWEEN NVL( start_date_active, p_trx_date )
1726 AND NVL( end_date_active, p_trx_date );
1727
1728 EXCEPTION
1729 WHEN NO_DATA_FOUND THEN
1730 p_invalid_reason_flag := TRUE;
1731 p_result_flag := FALSE;
1732
1733 add_to_error_list(
1734 p_error_mode,
1735 p_error_count,
1736 p_customer_trx_id,
1737 p_trx_number,
1738 NULL,
1739 NULL,
1740 'AR_TW_BAD_DATE_REASON',
1741 'TGW_HEADER.AL_REASON_MEANING',
1742 NULL,
1743 NULL,
1744 NULL,
1745 NULL
1746 );
1747
1748 WHEN OTHERS THEN RAISE;
1749 END;
1750
1751 END IF;
1752
1753
1754
1755 /*-------------------+
1756 | Validate Status |
1757 +-------------------*/
1758
1759 arp_util.debug('Validate Status');
1760
1761 IF ( p_status_trx IS NOT NULL )
1762 THEN
1763 BEGIN
1764
1765 SELECT 'status code is ok'
1766 INTO l_temp
1767 FROM ar_lookups
1768 WHERE lookup_type = 'INVOICE_TRX_STATUS'
1769 AND lookup_code = p_status_trx
1770 AND p_trx_date
1771 BETWEEN NVL( start_date_active, p_trx_date )
1772 AND NVL( end_date_active, p_trx_date );
1773
1774 EXCEPTION
1775 WHEN NO_DATA_FOUND THEN
1776 p_invalid_status_flag := TRUE;
1777 p_result_flag := FALSE;
1778
1779 add_to_error_list(
1780 p_error_mode,
1781 p_error_count,
1782 p_customer_trx_id,
1783 p_trx_number,
1784 NULL,
1785 NULL,
1786 'AR_TW_BAD_DATE_STATUS',
1787 'TGW_HEADER.STATUS_TRX',
1788 NULL,
1789 NULL,
1790 NULL,
1791 NULL
1792 );
1793
1794 WHEN OTHERS THEN RAISE;
1795 END;
1796
1797 END IF;
1798
1799
1800
1801 /*----------------------------------------------------------------------+
1802 | If the transaction that is being validated is a credit memo |
1803 | against a specific invoice, commitment or debit memo, the |
1804 | following validations are not done in order to allow these |
1805 | entities to default in from the transaction that is being credited. |
1806 +----------------------------------------------------------------------*/
1807
1808 IF (p_previous_customer_trx_id IS NULL)
1809 THEN
1810
1811 /*-----------------------------+
1812 | Validate Primary Salesrep |
1813 +-----------------------------*/
1814
1815 arp_util.debug('Validate Primary Salesrep');
1816
1817 IF ( p_primary_salesrep_id IS NOT NULL )
1818 THEN
1819 BEGIN
1820
1821 SELECT 'invoice date is ok'
1822 INTO l_temp
1823 FROM ra_salesreps
1824 WHERE salesrep_id = p_primary_salesrep_id
1825 AND p_trx_date BETWEEN NVL(start_date_active,
1826 p_trx_date)
1827 AND NVL(end_date_active,
1828 p_trx_date);
1829
1830
1831 EXCEPTION
1832 WHEN NO_DATA_FOUND THEN
1833 p_invalid_primary_srep_flag := TRUE;
1834 p_result_flag := FALSE;
1835
1836 /* Bug 2191739 - call to message API for degovtized message */
1837 add_to_error_list(
1838 p_error_mode,
1839 p_error_count,
1840 p_customer_trx_id,
1841 p_trx_number,
1842 NULL,
1843 NULL,
1844 gl_public_sector.get_message_name
1845 (p_message_name => 'AR_TW_BAD_DATE_PRIMARY_SREP',
1846 p_app_short_name => 'AR'),
1847 'TGW_HEADER.RAS_PRIMARY_SALESREP_NAME',
1848 NULL,
1849 NULL,
1850 NULL,
1851 NULL
1852 );
1853
1854 WHEN OTHERS THEN RAISE;
1855 END;
1856 END IF;
1857
1858 /*---------------------+
1859 | Validate Currency |
1860 +---------------------*/
1861
1862 arp_util.debug('Validate Currency');
1863
1864 IF ( p_invoice_currency_code IS NOT NULL )
1865 THEN
1866 BEGIN
1867
1868 SELECT 'invoice date is ok'
1869 INTo l_temp
1870 FROM fnd_currencies
1871 WHERE currency_code = p_invoice_currency_code
1872 AND p_trx_date BETWEEN NVL(START_DATE_ACTIVE, p_trx_date)
1873 AND NVL(END_DATE_ACTIVE, p_trx_date);
1874
1875 EXCEPTION
1876 WHEN NO_DATA_FOUND THEN
1877 p_invalid_currency_flag := TRUE;
1878 p_result_flag := FALSE;
1879
1880 add_to_error_list(
1881 p_error_mode,
1882 p_error_count,
1883 p_customer_trx_id,
1884 p_trx_number,
1885 NULL,
1886 NULL,
1887 'AR_TW_BAD_DATE_CURRENCY',
1888 'TGW_HEADER.INVOICE_CURRENCY_CODE',
1889 NULL,
1890 NULL,
1891 NULL,
1892 NULL
1893 );
1894
1895 WHEN OTHERS THEN RAISE;
1896 END;
1897 END IF;
1898
1899
1900
1901 /*---------------------------+
1902 | Validate Receipt Method |
1903 +---------------------------*/
1904
1905 arp_util.debug('Validate Receipt Method');
1906
1907 --Modified the IF condition to fix Bug 2162888, added the check for customer_trx_id
1908 IF ( p_receipt_method_id IS NOT NULL ) AND ( p_customer_trx_id IS NOT NULL )
1909 THEN
1910
1911 /*--------------------------------------------------------------------+
1912 | 23-MAY-2000 J Rautiainen BR Implementation |
1913 | BR payment method does not have bank account associated with it |
1914 +--------------------------------------------------------------------*/
1915 OPEN receipt_creation_method_cur;
1916 FETCH receipt_creation_method_cur INTO receipt_creation_method_rec;
1917 CLOSE receipt_creation_method_cur;
1918
1919 IF NVL(receipt_creation_method_rec.creation_method_code,'INV') = 'BR' THEN
1920 BEGIN
1921
1922 /*Bug3348454 Need not validate the customer attachment.
1923 Only validate the receipt method*/
1924
1925 SELECT 'invalid_payment method'
1926 INTO l_temp
1927 FROM ar_receipt_methods arm,
1928 ar_receipt_classes arc
1929 WHERE arm.receipt_class_id = arc.receipt_class_id
1930 AND arm.receipt_method_id = p_receipt_method_id
1931 AND p_trx_date BETWEEN NVL(arm.start_date,
1932 p_trx_date)
1933 AND NVL(arm.end_date,
1934 p_trx_date)
1935 AND rownum = 1;
1936
1937 EXCEPTION
1938 WHEN NO_DATA_FOUND THEN
1939 p_invalid_payment_mthd_flag := TRUE;
1940 p_result_flag := FALSE;
1941
1942 add_to_error_list(
1943 p_error_mode,
1944 p_error_count,
1945 p_customer_trx_id,
1946 p_trx_number,
1947 NULL,
1948 NULL,
1949 'AR_TW_BAD_DATE_PAYMENT_METHOD',
1950 'TGW_HEADER.ARM_RECEIPT_METHOD_NAME',
1951 NULL,
1952 NULL,
1953 NULL,
1954 NULL
1955 );
1956
1957 WHEN OTHERS THEN RAISE;
1958 END;
1959
1960 ELSE
1961 BEGIN
1962
1963 /* If Payment Creation Code is NON BR then validate bank accounts also for this
1964 receipt method accounts */
1965
1966 BEGIN
1967 SELECT arp_trx_defaults_3.get_party_id(paying_customer_id),
1968 arp_trx_defaults_3.get_party_id(bill_to_customer_id)
1969 INTO l_paying_customer_id,l_bill_to_customer_id
1970 FROM RA_CUSTOMER_TRX
1971 WHERE customer_trx_id=p_customer_trx_id;
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 RAISE;
1975 END;
1976
1977 /* We need to validate the following.
1978 1. Receipt Method end date.
1979 2. Receipt method account end date
1980 3. Receipt method should have atleast one
1981 bank account with valid end dates
1982 4. Also bank account should be of invoice currency or
1983 multi currency enabled.
1984 5. and that valid bank account should have
1985 atleast one bank valid branch.
1986 6. Additionally If payment method creation is MANUAL or AUTOMATIC
1987 then the trx currency is as same as payment method currency or
1988 multi currency flag should be 'Y'
1989 7. For Automatic methods if Payment type is NOT CREDIT_CARD
1990 additionally the currency should be defined or associated
1991 with paying or bill to customer bank accounts.This condition is
1992 taken from paying customer payment method LOV.. to keep the
1993 both validations in sync.*/
1994
1995 SELECT 'invalid_payment method'
1996 INTO l_temp
1997 FROM ar_receipt_methods arm,
1998 ar_receipt_method_accounts arma,
1999 ce_bank_accounts cba,
2000 ce_bank_acct_uses aba,
2001 ar_receipt_classes arc,
2002 ce_bank_branches_v bp
2003 WHERE arm.receipt_method_id = arma.receipt_method_id
2004 AND arm.receipt_class_id = arc.receipt_class_id
2005 AND arma.remit_bank_acct_use_id = aba.bank_acct_use_id
2006 AND aba.bank_account_id = cba.bank_account_id
2007 /* New Condition added Begin*/
2008 AND bp.branch_party_id = cba.bank_branch_id
2009 AND p_trx_date <= NVL(bp.end_date,p_trx_date)
2010 AND (cba.currency_code = p_invoice_currency_code or
2011 cba.receipt_multi_currency_flag ='Y') /* New condition */
2012 /*Removing the join consition based on currency code as part of bug fix 5346710
2013 AND (arc.creation_method_code='MANUAL'
2014 or (arc.creation_method_code='AUTOMATIC'
2015 and ( (nvl(arm.payment_channel_code,'*') = 'CREDIT_CARD' )
2016 or
2017 (nvl(arm.payment_channel_code,'*') <> 'CREDIT_CARD'
2018 AND p_invoice_currency_code in
2019 (select currency_code from iby_fndcpt_payer_assgn_instr_v where
2020 party_id in (l_paying_customer_id,l_bill_to_customer_id))))))*/
2021 /* New Condition added Ends*/
2022 -- AND aba.set_of_books_id = arp_global.set_of_books_id
2023 AND arm.receipt_method_id = p_receipt_method_id
2024 AND p_trx_date < NVL(cba.end_date,
2025 TO_DATE('01/01/2200','DD/MM/YYYY') )
2026 AND p_trx_date BETWEEN NVL(arm.start_date,
2027 p_trx_date)
2028 AND NVL(arm.end_date,
2029 p_trx_date)
2030 AND p_trx_date BETWEEN NVL(arma.start_date,
2031 p_trx_date)
2032 AND NVL(arma.end_date,
2033 p_trx_date)
2034 AND rownum = 1;
2035
2036 EXCEPTION
2037 WHEN NO_DATA_FOUND THEN
2038 p_invalid_payment_mthd_flag := TRUE;
2039 p_result_flag := FALSE;
2040
2041 add_to_error_list(
2042 p_error_mode,
2043 p_error_count,
2044 p_customer_trx_id,
2045 p_trx_number,
2046 NULL,
2047 NULL,
2048 'AR_TW_BAD_DATE_PAYMENT_METHOD',
2049 'TGW_HEADER.ARM_RECEIPT_METHOD_NAME',
2050 NULL,
2051 NULL,
2052 NULL,
2053 NULL
2054 );
2055
2056 WHEN OTHERS THEN RAISE;
2057 END;
2058 END IF;
2059 END IF;
2060
2061
2062 /*--------------------------+
2063 | Validate Customer Bank |
2064 +--------------------------*/
2065
2066 arp_util.debug('Validate Customer Bank'|| 'not reqd any more');
2067 /* payment uptake removed validation for customer bank bug4646161 */
2068
2069
2070
2071 /*----------------------+
2072 | Validate Salesreps |
2073 +----------------------*/
2074
2075 arp_util.debug('Validate Salesreps');
2076
2077 IF ( p_customer_trx_id IS NOT NULL )
2078 THEN
2079 BEGIN
2080 l_temp := NULL;
2081
2082 SELECT MIN(s.name),
2083 TO_CHAR(MIN(ctl.line_number))
2084 INTO l_temp,
2085 l_temp2
2086 FROM ra_cust_trx_line_salesreps ls,
2087 ra_customer_trx_lines ctl,
2088 ra_salesreps s
2089 WHERE ls.salesrep_id = s.salesrep_id
2090 AND ls.customer_trx_id = p_customer_trx_id
2091 AND ls.customer_trx_line_id = ctl.customer_trx_line_id(+)
2092 AND p_trx_date NOT BETWEEN NVL(s.start_date_active,
2093 p_trx_date)
2094 AND NVL(s.end_date_active,
2095 p_trx_date);
2096
2097 IF ( l_temp || l_temp2 IS NOT NULL )
2098 THEN
2099 p_invalid_salesrep_flag := TRUE;
2100 p_result_flag := FALSE;
2101
2102
2103 /*----------------------------------------+
2104 | If no line number has been selected, |
2105 | this is a default salescredit line. |
2106 +----------------------------------------*/
2107
2108 IF (l_temp2 IS NOT NULL)
2109 THEN
2110
2111 /* Bug 2191739 - call to message API for degovtized message */
2112 add_to_error_list(
2113 p_error_mode,
2114 p_error_count,
2115 p_customer_trx_id,
2116 p_trx_number,
2117 l_temp2,
2118 NULL,
2119 gl_public_sector.get_message_name
2120 (p_message_name => 'AR_TW_BAD_DATE_SALESREP',
2121 p_app_short_name => 'AR'),
2122 'TGW_HEADER.TRX_DATE',
2123 'SALESREP_NAME',
2124 l_temp,
2125 'LINE_NUMBER',
2126 l_temp2
2127 );
2128
2129 ELSE
2130
2131 /* Bug 2191739 - call to message API for degovtized message */
2132 add_to_error_list(
2133 p_error_mode,
2134 p_error_count,
2135 p_customer_trx_id,
2136 p_trx_number,
2137 l_temp2,
2138 NULL,
2139 gl_public_sector.get_message_name
2140 (p_message_name => 'AR_TW_BAD_DATE_DEFAULT_SREP',
2141 p_app_short_name => 'AR'),
2142 'TGW_HEADER.TRX_DATE',
2143 'SALESREP_NAME',
2144 l_temp,
2145 NULL,
2146 NULL
2147 );
2148
2149 END IF;
2150
2151 END IF;
2152
2153 EXCEPTION
2154 WHEN NO_DATA_FOUND THEN NULL;
2155 WHEN OTHERS THEN RAISE;
2156 END;
2157 END IF;
2158
2159
2160 /*----------------------+
2161 | Validate Memo Line |
2162 +----------------------*/
2163
2164 arp_util.debug('Validate Memo Line');
2165
2166 IF ( p_customer_trx_id IS NOT NULL )
2167 THEN
2168 BEGIN
2169
2170 SELECT TO_CHAR(MIN(lines.line_number))
2171 INTO l_temp
2172 FROM ra_customer_trx_lines lines,
2173 ar_memo_lines aml
2174 WHERE lines.customer_trx_id = p_customer_trx_id
2175 AND lines.memo_line_id = aml.memo_line_id
2176 AND p_trx_date NOT BETWEEN NVL(aml.start_date, p_trx_date)
2177 AND NVL(aml.end_date, p_trx_date);
2178
2179 IF (l_temp IS NOT NULL )
2180 THEN
2181
2182 p_invalid_memo_line_flag := TRUE;
2183 p_result_flag := FALSE;
2184
2185 add_to_error_list(
2186 p_error_mode,
2187 p_error_count,
2188 p_customer_trx_id,
2189 p_trx_number,
2190 l_temp,
2191 NULL,
2192 'AR_TW_BAD_DATE_MEMO_LINE',
2193 'TGW_HEADER.TRX_DATE',
2194 'LINE_NUMBER',
2195 l_temp,
2196 NULL,
2197 NULL
2198 );
2199 END IF;
2200
2201
2202 EXCEPTION
2203 WHEN NO_DATA_FOUND THEN NULL;
2204 WHEN OTHERS THEN RAISE;
2205 END;
2206 END IF;
2207
2208 /*---------------------+
2209 | Validate tax code |
2210 +---------------------*/
2211
2212 /* 4594101 - Validation tests autotax rows (ar_vat_tax)
2213 and compares effective dates from table against trx_date.
2214 This logic is now handled by eTax. This validation has been
2215 removed. */
2216
2217 END IF;
2218
2219 p_due_date := arpt_sql_func_util.Get_First_Due_Date( p_term_id,
2220 p_trx_date );
2221
2222 arp_util.debug('ARP_TRX_VALIDATE.validate_trx_date()-');
2223
2224
2225 EXCEPTION
2226 WHEN OTHERS THEN
2227 arp_util.debug('EXCEPTION: ARP_TRX_VALIDATE.validate_trx_date()');
2228 RAISE;
2229
2230 END;
2231
2232 /*----------------------------------------------------------------------+
2233 | This overloaded call is provided for backward compatibility with |
2234 | an older version of this routine that did not have the p_error_mode |
2235 | parameter. |
2236 +----------------------------------------------------------------------*/
2237
2238 PROCEDURE validate_trx_date( p_trx_date IN DATE,
2239 p_prev_trx_date IN DATE,
2240 p_commitment_trx_date IN DATE,
2241 p_customer_trx_id IN NUMBER,
2242 p_trx_number IN VARCHAR2,
2243 p_previous_customer_trx_id IN NUMBER,
2244 p_initial_customer_trx_id IN NUMBER,
2245 p_agreement_id IN NUMBER,
2246 p_batch_source_id IN NUMBER,
2247 p_cust_trx_type_id IN NUMBER,
2248 p_term_id IN NUMBER,
2249 p_ship_method_code IN VARCHAR2,
2250 p_primary_salesrep_id IN NUMBER,
2251 p_reason_code IN VARCHAR2,
2252 p_status_trx IN VARCHAR2,
2253 p_invoice_currency_code IN VARCHAR2,
2254 p_receipt_method_id IN NUMBER,
2255 p_bank_account_id IN NUMBER,
2256 p_due_date OUT NOCOPY date,
2257 p_result_flag OUT NOCOPY boolean,
2258 p_commitment_invalid_flag OUT NOCOPY boolean,
2259 p_invalid_agreement_flag OUT NOCOPY boolean,
2260 p_invalid_source_flag OUT NOCOPY boolean,
2261 p_invalid_type_flag OUT NOCOPY boolean,
2262 p_invalid_term_flag OUT NOCOPY boolean,
2263 p_invalid_ship_method_flag OUT NOCOPY boolean,
2264 p_invalid_primary_srep_flag OUT NOCOPY boolean,
2265 p_invalid_reason_flag OUT NOCOPY boolean,
2266 p_invalid_status_flag OUT NOCOPY boolean,
2267 p_invalid_currency_flag OUT NOCOPY boolean,
2268 p_invalid_payment_mthd_flag OUT NOCOPY boolean,
2269 p_invalid_bank_flag OUT NOCOPY boolean,
2270 p_invalid_salesrep_flag OUT NOCOPY boolean,
2271 p_invalid_memo_line_flag OUT NOCOPY boolean,
2272 p_invalid_uom_flag OUT NOCOPY boolean,
2273 p_invalid_tax_flag OUT NOCOPY boolean,
2274 p_invalid_cm_date_flag OUT NOCOPY boolean,
2275 p_invalid_child_date_flag OUT NOCOPY boolean,
2276 p_error_count IN OUT NOCOPY integer
2277 ) IS
2278 BEGIN
2279
2280 validate_trx_date( 'NO_EXCEPTION',
2281 p_trx_date,
2282 p_prev_trx_date,
2283 p_commitment_trx_date,
2284 p_customer_trx_id,
2285 p_trx_number,
2286 p_previous_customer_trx_id,
2287 p_initial_customer_trx_id,
2288 p_agreement_id,
2289 p_batch_source_id,
2290 p_cust_trx_type_id,
2291 p_term_id,
2292 p_ship_method_code,
2293 p_primary_salesrep_id,
2294 p_reason_code,
2295 p_status_trx,
2296 p_invoice_currency_code,
2297 p_receipt_method_id,
2298 p_bank_account_id,
2299 p_due_date,
2300 p_result_flag,
2301 p_commitment_invalid_flag,
2302 p_invalid_agreement_flag,
2303 p_invalid_source_flag,
2304 p_invalid_type_flag,
2305 p_invalid_term_flag,
2306 p_invalid_ship_method_flag,
2307 p_invalid_primary_srep_flag,
2308 p_invalid_reason_flag,
2309 p_invalid_status_flag,
2310 p_invalid_currency_flag,
2311 p_invalid_payment_mthd_flag,
2312 p_invalid_bank_flag,
2313 p_invalid_salesrep_flag,
2314 p_invalid_memo_line_flag,
2315 p_invalid_uom_flag,
2316 p_invalid_tax_flag,
2317 p_invalid_cm_date_flag,
2318 p_invalid_child_date_flag,
2319 p_error_count);
2320
2321 END;
2322
2323 /*===========================================================================+
2324 | PROCEDURE |
2325 | val_gl_dist_amounts |
2326 | |
2327 | DESCRIPTION |
2328 | This procedure validates that the sum of the distribution amounts |
2329 | for a given transaction line is correct in each gl_date. The |
2330 | distributions with the header GL date must add up to the line's |
2331 | extended amount. The distributions with other GL dates must add up |
2332 | to zero for each GL date. |
2333 | |
2334 | SCOPE - PUBLIC |
2335 | |
2336 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2337 | arp_util.debug |
2338 | |
2339 | ARGUMENTS : IN: p_customer_trx_line_id |
2340 | OUT: |
2341 | p_result |
2342 | |
2343 | RETURNS : NONE |
2344 | |
2345 | NOTES |
2346 | |
2347 | MODIFICATION HISTORY |
2348 | 10-NOV-95 Martin Johnson Created |
2349 | 14-DEC-95 Martin Johnson Split error message into two messages |
2350 | |
2351 +===========================================================================*/
2352
2353 PROCEDURE val_gl_dist_amounts(
2354 p_customer_trx_line_id IN NUMBER,
2355 p_result OUT NOCOPY boolean ) IS
2356
2357 l_d_gl_date date;
2358 l_gl_date date;
2359 l_rec_gl_date date;
2360 l_amount number;
2361
2362 /*--------------------------------------------------+
2363 | This sql will return no rows if data is valid. |
2364 | Needs to be a cursor to avoid error if multi |
2365 | rows rturned. |
2366 +--------------------------------------------------*/
2367
2368 /*--------------------------------------------------+
2369 | Bug 1332304. |
2370 | Reverting the change done in patch 959747. |
2371 | We need to pass the validation if either the |
2372 | percent or the amount are correct. |
2373 +--------------------------------------------------*/
2374
2375 CURSOR val_gl_dist_amounts IS
2376
2377 SELECT NVL(d.gl_date, rec.gl_date),
2378 DECODE(NVL(d.gl_date, rec.gl_date),
2379 rec.gl_date, l.extended_amount,
2380 0),
2381 d.gl_date,
2382 rec.gl_date
2383 FROM ra_cust_trx_line_gl_dist rec,
2384 ra_cust_trx_line_gl_dist d,
2385 ra_customer_trx_lines l,
2386 ra_customer_trx t
2387 WHERE l.customer_trx_line_id = d.customer_trx_line_id(+)
2388 AND l.customer_trx_line_id = p_customer_trx_line_id
2389 AND l.customer_trx_id = t.customer_trx_id
2390 AND rec.customer_trx_id = l.customer_trx_id
2391 AND rec.account_class = 'REC'
2392 AND rec.latest_rec_flag = 'Y'
2393 AND d.account_set_flag(+) = 'N'
2394 GROUP by d.customer_trx_line_id,
2395 d.gl_date,
2396 rec.gl_date,
2397 l.extended_amount
2398 HAVING (
2399 (
2400 SUM(d.amount) <> DECODE( nvl(d.gl_date, rec.gl_date),
2401 rec.gl_date, l.extended_amount,
2402 0)
2403 )
2404 AND -- Changed 'OR' into 'AND'. Bug 1332304.
2405 (
2406 SUM(d.percent) <> DECODE( nvl(d.gl_date, rec.gl_date),
2407 rec.gl_date, 100,
2408 0)
2409 )
2410 ) OR
2411 SUM(d.cust_trx_line_gl_dist_id) IS NULL
2412 ORDER BY d.gl_date;
2413
2414 BEGIN
2415
2416 IF PG_DEBUG in ('Y', 'C') THEN
2417 arp_util.debug('ARP_TRX_VALIDATE.val_gl_dist_amounts()+');
2418 END IF;
2419
2420 OPEN val_gl_dist_amounts;
2421
2422 FETCH val_gl_dist_amounts
2423 INTO l_gl_date,
2424 l_amount,
2425 l_d_gl_date,
2426 l_rec_gl_date;
2427
2428 IF ( val_gl_dist_amounts%NOTFOUND )
2429 THEN
2430 CLOSE val_gl_dist_amounts;
2431 p_result := TRUE;
2432
2433 ELSE
2434
2435 CLOSE val_gl_dist_amounts;
2436 p_result := FALSE;
2437
2438 IF ( l_gl_date = l_rec_gl_date )
2439 THEN
2440 fnd_message.set_name('AR', 'AR_TW_ACC_ASSGN_SUM_REC');
2441 fnd_message.set_token('GL_DATE',
2442 TO_CHAR(l_gl_date, 'DD-MON-YYYY'));
2443
2444 ELSE
2445 fnd_message.set_name('AR', 'AR_TW_ACC_ASSGN_SUM_ZERO');
2446 fnd_message.set_token('GL_DATE',
2447 TO_CHAR(l_d_gl_date, 'DD-MON-YYYY'));
2448
2449 END IF;
2450
2451 app_exception.raise_exception;
2452
2453 END IF;
2454
2455 IF PG_DEBUG in ('Y', 'C') THEN
2456 arp_util.debug('ARP_TRX_VALIDATE.val_gl_dist_amounts()-');
2457 END IF;
2458
2459 EXCEPTION
2460 WHEN OTHERS
2461 THEN
2462 IF PG_DEBUG in ('Y', 'C') THEN
2463 arp_util.debug('EXCEPTION: ARP_TRX_VALIDATE.val_gl_dist_amounts()');
2464 arp_util.debug('val_gl_dist_amounts: ' || ' p_customer_trx_line_id = ' ||
2465 p_customer_trx_line_id );
2466 arp_util.debug('val_gl_dist_amounts: ' || ' l_gl_date = ' || l_gl_date );
2467 arp_util.debug('val_gl_dist_amounts: ' || ' l_amount = ' || l_amount );
2468 END IF;
2469 RAISE;
2470
2471 END val_gl_dist_amounts;
2472
2473
2474 /*===========================================================================+
2475 | PROCEDURE |
2476 | val_and_dflt_pay_mthd_and_bank() |
2477 | |
2478 | DESCRIPTION |
2479 | Validates the payment method and the bank columns. |
2480 | If either is invalid, they are redefaulted. |
2481 | |
2482 | SCOPE - PUBLIC |
2483 | |
2484 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2485 | arp_util.debug |
2486 | arp_process_credit_util.check_payment_method |
2487 | arp_process_credit_util.check_bank_account |
2488 | |
2489 | ARGUMENTS : IN: |
2490 | p_trx_date |
2491 | p_currency_code |
2492 | p_paying_customer_id |
2493 | p_paying_site_use_id |
2494 | p_bill_to_customer_id |
2495 | p_bill_to_site_use_id |
2496 | p_in_receipt_method_id |
2497 | p_in_customer_bank_account_id |
2498 | OUT: |
2499 | p_payment_method_name |
2500 | p_receipt_method_id |
2501 | p_creation_method_code |
2502 | p_customer_bank_account_id |
2503 | p_bank_account_num |
2504 | p_bank_name |
2505 | p_bank_branch_name |
2506 | p_bank_branch_id |
2507 | |
2508 | RETURNS : NONE |
2509 | |
2510 | NOTES |
2511 | |
2512 | MODIFICATION HISTORY |
2513 | 08-FEB-96 Charlie Tomberg Created |
2514 | 23-JUN-99 Ajay Pandit Modified the procedure for fixing |
2515 | bug no 913071 so that the defaulting |
2516 | and validation for the bank accounts |
2517 | is not done when payment method is |
2518 | MANUAL |
2519 | 06-OCT-04 Surendra Rajan Added parameter p_trx_manual_flag to |
2520 | fix bug 3770337 |
2521 +===========================================================================*/
2522
2523 PROCEDURE val_and_dflt_pay_mthd_and_bank(
2524 p_trx_date IN date,
2525 p_currency_code IN varchar2,
2526 p_paying_customer_id IN number,
2527 p_paying_site_use_id IN number,
2528 p_bill_to_customer_id IN number,
2529 p_bill_to_site_use_id IN number,
2530 p_in_receipt_method_id IN number,
2531 p_in_customer_bank_account_id IN number,
2532 p_payment_type_code IN varchar2,
2533 p_payment_method_name OUT NOCOPY varchar2,
2534 p_receipt_method_id OUT NOCOPY number,
2535 p_creation_method_code OUT NOCOPY varchar2,
2536 p_customer_bank_account_id OUT NOCOPY number,
2537 p_bank_account_num OUT NOCOPY varchar2,
2538 p_bank_name OUT NOCOPY varchar2,
2539 p_bank_branch_name OUT NOCOPY varchar2,
2540 p_bank_branch_id OUT NOCOPY number,
2541 p_trx_manual_flag IN VARCHAR2 DEFAULT 'N'
2542 ) IS
2543
2544 l_dummy integer;
2545
2546 BEGIN
2547
2548 IF PG_DEBUG in ('Y', 'C') THEN
2549 arp_util.debug('arp_trx_validate.val_and_dflt_pay_mthd_and_bank()+');
2550 END IF;
2551
2552 IF ( arp_process_credit_util.check_payment_method
2553 (
2554 p_trx_date,
2555 p_bill_to_customer_id,
2556 p_bill_to_site_use_id,
2557 p_paying_customer_id,
2558 p_paying_site_use_id,
2559 p_currency_code,
2560 l_dummy,
2561 p_payment_method_name,
2562 p_receipt_method_id,
2563 p_creation_method_code
2564 ) = FALSE )
2565 THEN
2566
2567 arp_trx_defaults_3.get_payment_method_default(
2568 p_trx_date,
2569 p_currency_code,
2570 p_paying_customer_id,
2571 p_paying_site_use_id,
2572 p_bill_to_customer_id,
2573 p_bill_to_site_use_id,
2574 p_payment_method_name,
2575 p_receipt_method_id,
2576 p_creation_method_code,
2577 p_trx_manual_flag
2578 );
2579 END IF;
2580 /*Bug 913072 : */
2581 IF (p_creation_method_code = 'MANUAL'or p_creation_method_code IS NULL) THEN /*Bug 3312212*/
2582 p_customer_bank_account_id := NULL;
2583 p_bank_account_num := NULL;
2584 p_bank_name := NULL;
2585 p_bank_branch_name := NULL;
2586 p_bank_branch_id := NULL;
2587 ELSE
2588
2589 IF ( arp_process_credit_util.check_bank_account(
2590 p_trx_date,
2591 p_currency_code,
2592 p_bill_to_customer_id,
2593 p_bill_to_site_use_id,
2594 p_paying_customer_id,
2595 p_paying_site_use_id,
2596 l_dummy,
2597 p_customer_bank_account_id,
2598 l_dummy) = FALSE )
2599 THEN
2600
2601 arp_trx_defaults_3.get_bank_defaults(
2602 p_trx_date,
2603 p_currency_code,
2604 p_paying_customer_id,
2605 p_paying_site_use_id,
2606 p_bill_to_customer_id,
2607 p_bill_to_site_use_id,
2608 p_payment_type_code,
2609 p_customer_bank_account_id,
2610 p_bank_account_num,
2611 p_bank_name,
2612 p_bank_branch_name,
2613 p_bank_branch_id,
2614 p_trx_manual_flag
2615 );
2616 END IF;
2617 END IF;
2618
2619 IF PG_DEBUG in ('Y', 'C') THEN
2620 arp_util.debug('arp_trx_validater.val_and_dflt_pay_mthd_and_bank()-');
2621 END IF;
2622
2623 EXCEPTION
2624 WHEN NO_DATA_FOUND THEN NULL;
2625 WHEN OTHERS THEN
2626 IF PG_DEBUG in ('Y', 'C') THEN
2627 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' ||
2628 'EXCEPTION: arp_process_header.val_and_dflt_pay_mthd_and_bank()');
2629 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' ||
2630 '------- parameters for val_and_dflt_pay_mthd_and_bank ----');
2631 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_trx_date = ' ||
2632 TO_CHAR(p_trx_date) );
2633 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_currency_code = ' ||
2634 p_currency_code );
2635 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_paying_customer_id = ' ||
2636 p_paying_customer_id );
2637 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_paying_site_use_id = ' ||
2638 p_paying_site_use_id );
2639 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_bill_to_customer_id = ' ||
2640 p_bill_to_customer_id );
2641 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_bill_to_site_use_id = ' ||
2642 p_bill_to_site_use_id );
2643 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_in_receipt_method_id = ' ||
2644 p_in_receipt_method_id );
2645 arp_util.debug('val_and_dflt_pay_mthd_and_bank: ' || 'p_in_customer_bank_account_id = ' ||
2646 p_in_customer_bank_account_id );
2647 END IF;
2648
2649 RAISE;
2650
2651 END val_and_dflt_pay_mthd_and_bank;
2652
2653
2654 /*===========================================================================+
2655 | PROCEDURE |
2656 | do_completion_checking() |
2657 | |
2658 | DESCRIPTION |
2659 | Checks if the transaction can be completed. |
2660 | |
2661 | The following checks are performed: |
2662 | - Insure that at least one line or freight line exists. |
2663 | - Insure that all entities that have start / end dates are valid for |
2664 | the specified trx date. |
2665 | - Insure that if a commitment has been specified, it is valid with |
2666 | the transaction's trx_date and gl_date |
2667 | - If salescredits are required, the total salescredits for each line |
2668 | must equal 100% of the line amount. |
2669 | - If salescredits are not required, either no salescredits exist for |
2670 | a line or they sum to 100%. |
2671 | - Check the existance and validtity of account assignments or |
2672 | account sets: |
2673 | Constraints: |
2674 | - records exists for each line |
2675 | - all code combinations are valid |
2676 | - For account assignments, the sum of the assignment amounts |
2677 | must equal the line amount. |
2678 | - For account sets, the sum of the percents for each line and |
2679 | account class must equal 100%. |
2680 | - If an invoicing rule has been specified, verify that all lines |
2681 | have accounting rules and rule start dates. |
2682 | - If TAX_CALCULATION_FLAG is Yes, then tax is required for all invoice |
2683 | lines unless it's a memo line not of type LINE. |
2684 | - Tax is also required if TAX_CALCULATION_FLAG is No and |
2685 | TAX_EXEMPT_FLAG is Require. |
2686 | - Check the creation sign of the transaction |
2687 | - Verify that the GL Date is in an Opened, Future or |
2688 | Never Opened (Arrears only) Period. |
2689 | |
2690 | The following validations only apply to credit memos against |
2691 | transactions. |
2692 | |
2693 | - Check for illegal overapplications. |
2694 | - The GL Date must be >= the credited transaction's GL Date. |
2695 | - There can be no later credit memos applied to the same transaction. |
2696 | |
2697 | SCOPE - PUBLIC |
2698 | |
2699 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2700 | arp_bal_util.get_commitment_balance |
2701 | arp_ct_pkg.fetch_p |
2702 | arp_non_db_pkg.check_creation_sign |
2703 | arp_non_db_pkg.check_natural_application |
2704 | arp_trx_global.profile_info.use_inv_acct_for_cm_flag |
2705 | arp_trx_util.get_summary_trx_balances |
2706 | arp_trx_validate.validate_trx_date |
2707 | arp_util.debug |
2708 | arp_util.validate_and_default_gl_date |
2709 | |
2710 | ARGUMENTS : IN: |
2711 | p_customer_trx_id |
2712 | p_so_source_code |
2713 | p_so_installed_flag |
2714 | |
2715 | OUT: |
2716 | p_error_count |
2717 | |
2718 | RETURNS : p_error_count |
2719 | |
2720 | NOTES |
2721 | |
2722 | MODIFICATION HISTORY |
2723 | 06-DEC-95 Charlie Tomberg Created |
2724 | |
2725 +===========================================================================*/
2726
2727 PROCEDURE do_completion_checking(
2728 p_customer_trx_id IN NUMBER,
2729 p_so_source_code IN varchar2,
2730 p_so_installed_flag IN varchar2,
2731 p_error_mode IN VARCHAR2,
2732 p_error_count OUT NOCOPY number
2733 ) IS
2734
2735
2736 BEGIN
2737 arp_trx_complete_chk.do_completion_checking(
2738 p_customer_trx_id,
2739 p_so_source_code,
2740 p_so_installed_flag,
2741 p_error_mode,
2742 p_error_count);
2743 END;
2744
2745 PROCEDURE init IS
2746 BEGIN
2747
2748 pg_base_curr_code := arp_global.functional_currency;
2749 pg_salesrep_required_flag :=
2750 arp_trx_global.system_info.system_parameters.salesrep_required_flag;
2751 pg_set_of_books_id :=
2752 arp_trx_global.system_info.system_parameters.set_of_books_id;
2753 END init;
2754
2755 BEGIN
2756 init;
2757 END ARP_TRX_VALIDATE;