[Home] [Help]
PACKAGE BODY: APPS.ARPT_SQL_FUNC_UTIL
Source
1 PACKAGE BODY ARPT_SQL_FUNC_UTIL AS
2 /* $Header: ARTUSSFB.pls 120.42.12020000.3 2012/07/10 00:44:11 dgaurab ship $ */
3
4 pg_reference_column VARCHAR2(240);
5
6 /*===========================================================================+
7 | FUNCTION |
8 | get_cb_invoice |
9 | |
10 | DESCRIPTION |
14 | |
11 | Gets the invoice associated with a Chargeback. |
12 | |
13 | SCOPE - PUBLIC |
15 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
16 | |
17 | ARGUMENTS : IN: |
18 | p_customer_trx_line_id |
19 | p_class |
20 | OUT: |
21 | None |
22 | |
23 | RETURNS : the trx_number of the invoice associated with the chargeback.|
24 | |
25 | NOTES |
26 | |
27 | MODIFICATION HISTORY |
28 | 27-OCT-95 Charlie Tomberg Created |
29 +===========================================================================*/
30
31 FUNCTION get_cb_invoice( p_customer_trx_id IN number,
32 p_class IN varchar2)
33 RETURN VARCHAR2 IS
34
35 l_inv_trx_number ra_customer_trx.trx_number%type;
36
37 BEGIN
38
39 IF ( p_class <> 'CB' )
40 THEN RETURN( NULL );
41 ELSE
42
43 SELECT MAX( ct.trx_number )
44 INTO l_inv_trx_number
45 FROM ra_customer_trx ct,
46 ar_adjustments_all aa --anuj
47 WHERE aa.chargeback_customer_trx_id = p_customer_trx_id
48 and ct.org_id = aa.org_id
49 AND aa.customer_trx_id = ct.customer_trx_id;
50
51 RETURN(l_inv_trx_number);
52
53 END IF;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 RAISE;
58
59 END;
60
61
62 /*===========================================================================+
63 | FUNCTION |
64 | get_dispute_amount |
65 | |
66 | DESCRIPTION |
67 | Gets the amount in dispute for a specific transaction. |
68 | |
69 | SCOPE - PUBLIC |
70 | |
71 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
72 | |
73 | ARGUMENTS : IN: |
74 | p_customer_trx_line_id |
75 | p_class |
76 | p_open_receivable_flag |
77 | OUT: |
78 | None |
79 | |
80 | RETURNS : The dispute amount |
81 | |
82 | NOTES |
83 | |
84 | MODIFICATION HISTORY |
85 | 27-OCT-95 Charlie Tomberg Created
86 | 10/10/1996 Harri Kaukovuo Related to bug 410349, cm, dep and
87 | guarantees can have dispute amounts.
88 +===========================================================================*/
89
90 FUNCTION get_dispute_amount( p_customer_trx_id IN number,
91 p_class IN varchar2,
92 p_open_receivable_flag IN varchar2)
93 RETURN NUMBER IS
94
95 l_amount_in_dispute number;
96
97 BEGIN
98
99 /*------------------------------------------------------------+
100 | Return NULL immediately if the transaction cannot have a |
101 | dispute amount. |
102 +------------------------------------------------------------*/
103
104 IF ( p_open_receivable_flag = 'N')
105 THEN RETURN( NULL );
106 ELSE
107
108 SELECT SUM( NVL( ps.AMOUNT_IN_DISPUTE, 0) )
109 INTO l_amount_in_dispute
110 FROM ar_payment_schedules ps
111 WHERE ps.customer_trx_id = p_customer_trx_id;
112
113 RETURN(l_amount_in_dispute);
114
115 END IF;
116
117 EXCEPTION
118 WHEN OTHERS THEN
119 RAISE;
120
121 END;
122
123 /*===========================================================================+
124 | FUNCTION |
125 | get_dispute_date |
126 | |
127 | DESCRIPTION |
131 | |
128 | Gets the maximum current dispute date for a specific transaction. |
129 | |
130 | SCOPE - PUBLIC |
132 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
133 | |
134 | ARGUMENTS : IN: |
135 | p_customer_trx_line_id |
136 | p_class |
137 | p_open_receivable_flag |
138 | OUT: |
139 | None |
140 | |
141 | RETURNS : The dispute date |
142 | |
143 | NOTES |
144 | |
145 | MODIFICATION HISTORY |
146 | 27-OCT-95 Charlie Tomberg Created |
147 | 10/10/1996 Harri Kaukovuo Related to bug 410349, cm, dep and
148 | guarantees can have dispute amounts.
149 +===========================================================================*/
150
151 FUNCTION get_dispute_date( p_customer_trx_id IN number,
152 p_class IN varchar2,
153 p_open_receivable_flag IN varchar2)
154 RETURN DATE IS
155
156 l_dispute_date ar_payment_schedules.dispute_date%type;
157
158 BEGIN
159
160 /*------------------------------------------------------------+
161 | Return NULL immediately if the transaction cannot have a |
162 | dispute amount. |
163 +------------------------------------------------------------*/
164
165 IF (p_open_receivable_flag = 'N')
166 THEN RETURN( NULL );
167 ELSE
168
169 SELECT MAX(ps.dispute_date )
170 INTO l_dispute_date
171 FROM ar_payment_schedules ps
172 WHERE ps.customer_trx_id = p_customer_trx_id
173 AND ps.dispute_date IS NOT NULL;
174
175 RETURN(l_dispute_date);
176
177 END IF;
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 RAISE;
182
183 END;
184
185 /*===========================================================================+
186 | FUNCTION |
187 | get_max_dispute_date |
188 | |
189 | DESCRIPTION |
190 | Gets the maximum dispute date for a specific transaction. |
191 | |
192 | SCOPE - PUBLIC |
193 | |
194 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
195 | |
196 | ARGUMENTS : IN: |
197 | p_customer_trx_line_id |
198 | p_class |
199 | p_open_receivable_flag |
200 | OUT: |
201 | None |
202 | |
203 | RETURNS : The dispute date |
204 | |
205 | NOTES |
206 | |
207 | MODIFICATION HISTORY |
208 | 08-FEB-95 Charlie Tomberg Created |
209 | 10/10/1996 Harri Kaukovuo Related to bug 410349, cm, dep and
210 | guarantees can have dispute amounts.
211 +===========================================================================*/
212
213 FUNCTION get_max_dispute_date( p_customer_trx_id IN number,
214 p_class IN varchar2,
215 p_open_receivable_flag IN varchar2)
216 RETURN DATE IS
217
218 l_dispute_date ar_payment_schedules.dispute_date%type;
219
220 BEGIN
221
222 /*------------------------------------------------------------+
223 | Return NULL immediately if the transaction cannot have a |
224 | dispute amount. |
225 +------------------------------------------------------------*/
226
227 IF (p_open_receivable_flag = 'N')
228 THEN RETURN( NULL );
229 ELSE
230
231 SELECT MAX(h.start_date)
232 INTO l_dispute_date
233 FROM ar_dispute_history h,
234 ar_payment_schedules ps
238
235 WHERE h.payment_schedule_id = ps.payment_schedule_id
236 AND ps.customer_trx_id = p_customer_trx_id
237 AND end_date IS NULL;
239 RETURN(l_dispute_date);
240
241 END IF;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 RAISE;
246
247 END;
248
249
250 /*===========================================================================+
251 | FUNCTION |
252 | get_revenue_recog_run_flag |
253 | |
254 | DESCRIPTION |
255 | Determines if the revenue recognition program has created any |
256 | distributions for a specific transaction. |
257 | |
258 | SCOPE - PUBLIC |
259 | |
260 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
261 | |
262 | ARGUMENTS : IN: |
263 | p_customer_trx_line_id |
264 | p_invoicing_ruke_id |
265 | OUT: |
266 | None |
267 | |
268 | RETURNS : TRUE if revenue has been recognized, FALSE if it has not. |
269 | |
270 | NOTES |
271 | |
272 | MODIFICATION HISTORY |
273 | 27-OCT-95 Charlie Tomberg Created |
274 | 11-Apr-04 Kamsi Bug3251996 :Modified the function to check for the |
275 | same conditions for both Invoices and CM's.The |
276 | SELECT to determine whether Revenue Recognition is |
277 | run is also modified.The old code is commented out |
278 | below the new one. |
279 | |
280 +===========================================================================*/
281
282 FUNCTION get_revenue_recog_run_flag( p_customer_trx_id IN number,
283 p_invoicing_rule_id IN number)
284 RETURN VARCHAR2 IS
285 l_rule_flag varchar2(1) := 'N';
286
287 BEGIN
288
289 /* Check for invoicing_rule_id to ensure whether the Transactions have
290 Rules attached to them. */
291
292 IF ( p_invoicing_rule_id IS NULL )
293 THEN RETURN( 'N' );
294 END IF;
295
296 /* Check whether Revenue Recognition is run . */
297
298 Select decode(max(DUMMY), null , 'N','Y')
299 Into l_rule_flag
300 From dual
301 Where Exists ( Select 'Revenue recognition has been run'
302 From ra_cust_trx_line_gl_dist d
303 Where d.customer_trx_id = p_customer_trx_id
304 and d.account_class = 'REC'
305 and d.account_set_flag = 'N');
306
307 IF (l_rule_flag = 'N')
308 THEN RETURN( 'N' );
309 ELSE
310 RETURN('Y');
311 END IF;
312 EXCEPTION
313 WHEN OTHERS THEN
314 RAISE;
315
316 END;
317
318 /*===========================================================================+
319 | FUNCTION |
320 | get_nonmodal_row_exist_flag |
321 | |
322 | DESCRIPTION |
323 | Determines if the revenue recognition program has created any |
324 | distributions for non modal row . |
325 | |
326 | SCOPE - PUBLIC |
327 | |
328 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
329 | |
330 | ARGUMENTS : IN: |
331 | p_customer_trx_line_id |
332 | p_invoicing_ruke_id |
333 | OUT: |
334 | None |
335 | |
336 | RETURNS : TRUE if revenue has been recognized, FALSE if it has not. |
337 | |
338 | NOTES |
339 | |
340 | MODIFICATION HISTORY |
341 | 27-OCT-95 anjprasa Created |
345
342 | |
343 | |
344 +===========================================================================*/
346 FUNCTION get_nonmodal_row_exist_flag( p_customer_trx_id IN number,
347 p_invoicing_rule_id IN number)
348 RETURN VARCHAR2 IS
349 l_rule_flag varchar2(1) := 'N';
350
351 BEGIN
352
353 /* Check for invoicing_rule_id to ensure whether the Transactions have
354 Rules attached to them. */
355
356 IF ( p_invoicing_rule_id IS NULL )
357 THEN RETURN( 'N' );
358 END IF;
359
360 /* Check whether Revenue Recognition is run for Non Modal Row . */
361
362
363 Select decode(max(DUMMY), null , 'N','Y')
364 Into l_rule_flag
365 From dual
366 Where Exists ( Select 'Revenue recognition has been run'
367 From ra_cust_trx_line_gl_dist d
368 Where d.customer_trx_id = p_customer_trx_id
369 and d.account_class <> 'REC'
370 and d.account_set_flag = 'N');
371
372 IF (l_rule_flag = 'N')
373 THEN RETURN( 'N' );
374 ELSE
375 RETURN('Y');
376 END IF;
377 EXCEPTION
378 WHEN OTHERS THEN
379 RAISE;
380
381 END;
382
383
384
385
386 /*FUNCTION get_revenue_recog_run_flag( p_customer_trx_id IN number,
387 p_invoicing_rule_id IN number)
388 RETURN VARCHAR2 IS
389
390 l_temp_flag varchar2(1);
391 l_rule_flag varchar2(1);
392 l_cm_flag varchar2(1);
393
394 BEGIN
395
396 Check if it is a credit memo
397
398 SELECT decode(previous_customer_trx_id,NULL,'N','Y')
399 INTO l_cm_flag
400 FROM ra_customer_trx
401 WHERE customer_trx_id = p_customer_trx_id;
402
403 IF ( l_cm_flag ='Y')
404 THEN
405 Portion added so that we can see the
406 distributions on credit memo
407 SELECT decode( max(d.customer_trx_id),
408 null, 'N',
409 'Y')
410 INTO l_rule_flag
411 FROM ra_customer_trx trx,
412 ra_cust_trx_line_gl_dist d
413 WHERE trx.customer_trx_id = p_customer_trx_id
414 and trx.previous_customer_trx_id = d.customer_trx_id
415 and d.account_class in ('UNEARN', 'UNBILL')
416 and d.account_set_flag='N'; Added for bug 559954
417
418 Modified for Bug 559954
419 IF (l_rule_flag = 'N')
420 THEN RETURN( 'N' );
421 ELSE
422 RETURN('Y');
423 END IF;
424 End modifications for Bug 559954
425 END IF;
426
427 IF ( p_invoicing_rule_id IS NULL )
428 THEN RETURN( 'N' );
429 ELSE
430
431 Changed the search criteria from
432 autorule_duration_processed > 0
433 to autorule_duration_processed <> 0
434 Bug 461391
435
436 SELECT DECODE( MAX(DUMMY),
437 NULL, 'N',
438 'Y')
439 INTO l_temp_flag
440 FROM DUAL
441 WHERE EXISTS (
442 SELECT 'Revenue recognition has been run'
443 FROM ra_customer_trx_lines
444 WHERE customer_trx_id = p_customer_trx_id
445 AND autorule_duration_processed <> 0
446 );
447
448 RETURN(l_temp_flag);
449
450 END IF;
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 RAISE;
455
456 END; */
457
458
459 /*===========================================================================+
460 | FUNCTION |
461 | get_posted_flag |
462 | |
463 | DESCRIPTION |
464 | Determines if the specified transaction has been posted. |
465 | |
466 | SCOPE - PUBLIC |
467 | |
468 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
469 | |
470 | ARGUMENTS : IN: |
471 | p_customer_trx_line_id |
472 | p_post_to_gl_flag |
473 | p_complete_flag |
474 | OUT: |
475 | None |
476 | |
477 | RETURNS : TRUE if the transaction has been posted, |
478 | FALSE if it has not. |
479 | |
480 | NOTES |
484 | |
481 | |
482 | MODIFICATION HISTORY |
483 | 28-NOV-95 Charlie Tomberg Created |
485 +===========================================================================*/
486
487 FUNCTION get_posted_flag( p_customer_trx_id IN number,
488 p_post_to_gl_flag IN varchar2,
489 p_complete_flag IN varchar2,
490 p_class IN varchar2 DEFAULT NULL) RETURN VARCHAR2 IS
491
492 l_temp_flag varchar2(1);
493
494 BEGIN
495
496 IF (
497 NVL(p_post_to_gl_flag, 'Y') = 'N'
498 OR NVL(p_complete_flag, 'N') = 'N'
499 )
500 THEN RETURN( 'N' );
501 ELSE
502
503 /*-------------------------------------------------------+
504 | 04-AUG-2000 J Rautiainen BR Implementation |
505 | For Bills Receivable check the posting from table |
506 | ar_transaction_history, for other types use the |
507 | existing logic. |
508 +-------------------------------------------------------*/
509 IF (p_class = 'BR' )THEN
510
511 SELECT DECODE( MAX(DUMMY),
512 NULL, 'N',
513 'Y')
514 INTO l_temp_flag
515 FROM DUAL
516 WHERE EXISTS (
517 SELECT 'transaction has been posted'
518 FROM ar_transaction_history
519 WHERE customer_trx_id = p_customer_trx_id
520 AND gl_posted_date IS NOT NULL
521 );
522
523 ELSE
524 SELECT DECODE( MAX(DUMMY),
525 NULL, 'N',
526 'Y')
527 INTO l_temp_flag
528 FROM DUAL
529 WHERE EXISTS (
530 SELECT 'transaction has been posted'
531 FROM ra_cust_trx_line_gl_dist
532 WHERE customer_trx_id = p_customer_trx_id
533 AND gl_posted_date IS NOT NULL
534 );
535 END IF;
536
537 RETURN(l_temp_flag);
538
539 END IF;
540
541 EXCEPTION
542 WHEN OTHERS THEN
543 RAISE;
544
545 END;
546
547 /*===========================================================================+
548 | FUNCTION |
549 | get_posted_flag |
550 | |
551 | DESCRIPTION |
552 | Determines if the specified transaction has been posted. |
553 | |
554 | SCOPE - PUBLIC |
555 | |
556 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
557 | |
558 | ARGUMENTS : IN: |
559 | p_customer_trx_line_id |
560 | p_post_to_gl_flag |
561 | p_complete_flag |
562 | OUT: |
563 | None |
564 | |
565 | RETURNS : TRUE if the transaction has been posted, |
566 | FALSE if it has not. |
567 | |
568 | NOTES |
569 | |
570 | MODIFICATION HISTORY |
571 | 28-NOV-95 Charlie Tomberg Created |
572 | |
573 +===========================================================================*/
574
575 FUNCTION get_selected_for_payment_flag( p_customer_trx_id IN number,
576 p_open_receivables_flag IN varchar2,
577 p_complete_flag IN varchar2)
578 RETURN VARCHAR2 IS
579
580 l_auto_rec_count integer;
581 l_auto_rec_approved_count integer;
582
583 BEGIN
584
585 IF (
586 NVL(p_open_receivables_flag, 'Y') = 'N'
587 OR NVL(p_complete_flag, 'Y') = 'N'
588 )
589 THEN RETURN( 'N' );
590 ELSE
591 /*-------------------------------------------+
592 | Find out how many payment schedules have |
593 | been selected for automatic receipt. |
594 +-------------------------------------------*/
595
599 WHERE customer_trx_id = p_customer_trx_id
596 SELECT COUNT(*)
597 INTO l_auto_rec_count
598 FROM ar_payment_schedules
600 AND selected_for_receipt_batch_id IS NOT NULL;
601
602 /*-----------------------------------------------+
603 | If no payment schedules have been selected |
604 | for automatic receipt, return 'N'. |
605 +-----------------------------------------------*/
606
607 IF ( l_auto_rec_count = 0 )
608 THEN RETURN( 'N' );
609 ELSE
610
611 /*-------------------------------------------------------+
612 | Find out how many of the payment schedules selected |
613 | for automatic receipt have been approved. |
614 +-------------------------------------------------------*/
615
616 SELECT COUNT(DISTINCT ps.payment_schedule_id)
617 INTO l_auto_rec_approved_count
618 FROM ar_payment_schedules ps,
619 ar_receivable_applications ra,
620 ar_cash_receipt_history crh
621 WHERE ps.customer_trx_id = p_customer_trx_id
622 AND ra.applied_payment_schedule_id = ps.payment_schedule_id
623 AND ra.cash_receipt_id = crh.cash_receipt_id
624 AND ps.selected_for_receipt_batch_id = crh.batch_id
625 AND crh.batch_id = ps.selected_for_receipt_batch_id;
626
627 /*---------------------------------------------------------+
628 | If all of the payment schedules selected for automatic |
629 | receipt have been approved, then return 'N'. |
630 | Otherwise, return 'Y'. |
631 +---------------------------------------------------------*/
632
633 IF ( l_auto_rec_count = l_auto_rec_approved_count )
634 THEN RETURN('N');
635 ELSE RETURN('Y');
636 END IF;
637
638 END IF;
639
640 END IF;
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 RAISE;
645
646 END;
647
648
649 /*===========================================================================+
650 | FUNCTION |
651 | get_activity_flag |
652 | |
653 | DESCRIPTION |
654 | Determines if the specified transaction has been posted. |
655 | |
656 | SCOPE - PUBLIC |
657 | |
658 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
659 | |
660 | ARGUMENTS : IN: |
661 | p_customer_trx_line_id |
662 | p_post_to_gl_flag |
663 | p_complete_flag |
664 | OUT: |
665 | None |
666 | |
667 | RETURNS : TRUE if the transaction has been posted, |
668 | FALSE if it has not. |
669 | |
670 | NOTES |
671 | |
672 | MODIFICATION HISTORY |
673 | 28-NOV-95 Charlie Tomberg Created |
674 | 04-JUN-98 Debbie Jancis Fixed Bug 677474 |
675 | |
676 | 04-APR-06 Herve Yu Bug 4897195 consider posting as a |
677 | activity |
678 | 23-Aug-06 GGADHAMS Bug 5394382 consider collection |
679 | delinquency as activity |
680 +===========================================================================*/
681
682 FUNCTION get_activity_flag( p_customer_trx_id IN number,
683 p_open_receivables_flag IN varchar2,
684 p_complete_flag IN varchar2,
685 p_class IN varchar2,
686 p_initial_customer_trx_id IN number,
687 p_previous_customer_trx_id IN number
688 )
689 RETURN VARCHAR2 IS
690
691 l_activity_flag varchar2(1);
692
693 --{verif if 1 distribution has been posted 4897195
694 CURSOR c IS
695 SELECT 'Y' FROM ra_cust_trx_line_gl_dist
696 WHERE customer_trx_id = p_customer_trx_id
697 AND account_set_flag = 'N'
698 AND posting_control_id > 0;
699
700 l_found VARCHAR2(1);
701 --}
702
703 --Bug5394382
704 CURSOR col_del IS
705 SELECT 'Y' FROM iex_delinquencies
706 WHERE transaction_id = p_customer_trx_id;
707
708 BEGIN
712 IF c%NOTFOUND THEN
709 --{BUG 4897195
710 OPEN c;
711 FETCH c INTO l_found;
713 l_found := 'N';
714 END IF;
715 CLOSE c;
716 IF l_found = 'Y' THEN
717 RETURN 'Y';
718 END IF;
719 --}
720
721 --{BUG 5394382
722 OPEN col_del;
723 FETCH col_del INTO l_found;
724 IF col_del%NOTFOUND THEN
725 l_found := 'N';
726 END IF;
727 CLOSE col_del;
728 IF l_found = 'Y' THEN
729 RETURN 'Y';
730 END IF;
731 --}
732
733
734
735
736 IF (
737 NVL(p_open_receivables_flag, 'Y') = 'N'
738 /* Bug 640006: removed comparison to p_complete_flag because
739 if the transaction window is open when an activity is applied
740 against it, the incomplete button is still enabled and
741 p_complete_flag is 'N'. This function will incorrectly pass
742 back 'N' even though there is activity
743 OR NVL(p_complete_flag, 'Y') = 'N' */
744 )
745 THEN RETURN( 'N' );
746 ELSE
747
748 /*--------------------------------------------------------------+
749 | Transaction has activity if it is the child of a Guarantee |
750 +--------------------------------------------------------------*/
751
752 IF ( p_initial_customer_trx_id IS NOT NULL )
753 THEN
754 SELECT DECODE(ctt.type,
755 'GUAR', 'Y',
756 'N')
757 INTO l_activity_flag
758 FROM ra_customer_trx ct,
759 ra_cust_trx_types ctt
760 WHERE ct.cust_trx_type_id = ctt.cust_trx_type_id
761 AND ct.customer_trx_id = p_initial_customer_trx_id
762 --begin anuj
763 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
764 AND ct.org_id = ctt.org_id;
765 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
766 --end anuj
767
768
769 /* for invoices applied to guarantees we need to check
770 the complete flag. if it is incomplete then we need
771 to return no activity. Bug 677474 */
772 IF (l_activity_flag = 'Y') THEN
773 IF ( NVL(p_complete_flag, 'Y') = 'N') THEN
774 RETURN ('N');
775 ELSE
776 RETURN('Y');
777 END IF;
778 END IF;
779
780 END IF;
781
782 /*-------------------------------------------------------+
783 | Transaction has actvity if this is a commitment and |
784 | child transactions exist. |
785 +-------------------------------------------------------*/
786
787 IF (p_class IN ('DEP', 'GUAR') )
788 THEN
789
790 SELECT DECODE( MAX(ct.customer_trx_id),
791 NULL, 'N',
792 'Y')
793 INTO l_activity_flag
794 FROM ra_customer_trx ct
795 WHERE ct.initial_customer_trx_id = p_customer_trx_id;
796
797 IF (l_activity_flag = 'Y')
798 THEN RETURN('Y');
799 END IF;
800
801 END IF;
802
803 /*------------------------------------------------------+
804 | Check the payment schedule to see if any activity |
805 | can be detected there. |
806 +------------------------------------------------------*/
807
808 IF ( p_previous_customer_trx_id IS NULL )
809 THEN
810
811 SELECT DECODE( MAX(ps.payment_schedule_id),
812 NULL, 'N',
813 'Y')
814 INTO l_activity_flag
815 FROM ar_payment_schedules ps
816 WHERE ps.customer_trx_id = p_customer_trx_id
817 AND (
818 ps.amount_due_original <> ps.amount_due_remaining
819 OR NVL(ps.amount_applied,0) <> 0
820 OR NVL(ps.amount_credited,0) <> 0
821 OR NVL(ps.amount_adjusted,0) <> 0
822 OR NVL(ps.amount_in_dispute,0) <> 0
823 OR ps.selected_for_receipt_batch_id IS NOT NULL
824 OR exists
825 (
826 SELECT 'dunned'
827 FROM ar_correspondence_pay_sched cps
828 WHERE cps.payment_schedule_id =
829 ps.payment_schedule_id
830 )
831 );
832
833 IF (l_activity_flag = 'Y')
834 THEN RETURN('Y');
835 END IF;
836
837
838 /*------------------------------------------------------+
839 | Check to see if any applications exist against |
840 | this transaction. If the sum of these applications |
841 | equals zero, they would not have been detected in |
842 | the payment schedule check above. |
843 +------------------------------------------------------*/
847 'Y' )
844
845 SELECT DECODE( MAX( receivable_application_id ),
846 NULL, 'N',
848 INTO l_activity_flag
849 FROM ar_receivable_applications app
850 WHERE app.customer_trx_id = p_customer_trx_id
851 OR app.applied_customer_trx_id = p_customer_trx_id;
852
853 IF (l_activity_flag = 'Y')
854 THEN RETURN('Y');
855 END IF;
856
857 ELSE -- Credit Memo against a specific transaction case
858
859 /*-----------------------------------------------------+
860 | Check the payment schedule for activity. |
861 | This check is more limited than for non-specific |
862 | credit memos. If it were not, all specific credit |
863 | memos would always have activity since they are |
864 | immediately applied to the credited transaction. |
865 +-----------------------------------------------------*/
866
867 SELECT DECODE( MAX( payment_schedule_id ),
868 NULL, 'N',
869 'Y' )
870 INTO l_activity_flag
871 FROM ar_payment_schedules ps
872 WHERE (
873 (
874 NVL(ps.amount_credited, 0) <> 0
875 OR NVL(ps.amount_adjusted, 0) <> 0
876 OR NVL(ps.amount_in_dispute, 0) <> 0
877 )
878 OR
879 ps.selected_for_receipt_batch_id IS NOT NULL
880 )
881 AND ps.customer_trx_id = p_customer_trx_id;
882
883 IF (l_activity_flag = 'Y')
884 THEN RETURN('Y');
885 END IF;
886
887 /*---------------------------------------------------+
888 | If another CM against the same invoice has been |
889 | completed since the current CM was completed, |
890 | the current CM will be deemed to have activity. |
891 | This is to prevent changes that would make the |
892 | other credit memos invalid. |
893 +---------------------------------------------------*/
894
895 SELECT DECODE( MAX( other_ps.payment_schedule_id),
896 NULL, 'N',
897 'Y')
898 INTO l_activity_flag
899 FROM ar_payment_schedules this_ps,
900 ar_payment_schedules other_ps,
901 ra_customer_trx other_ct
902 WHERE this_ps.customer_trx_id = p_customer_trx_id
903 AND other_ct.previous_customer_trx_id =
904 p_previous_customer_trx_id
905 AND other_ct.customer_trx_id = other_ps.customer_trx_id
906 AND other_ps.creation_date > this_ps.creation_date;
907
908 IF (l_activity_flag = 'Y')
909 THEN RETURN('Y');
910 END IF;
911
912 END IF; -- previous_customer_trx_id check
913
914
915 /*---------------------------------------------------------------+
916 | ** The following checks must be done for all transactions ** |
917 +---------------------------------------------------------------*/
918
919 /*-----------------------------------------------------+
920 | Check to see if any adjustments exist against |
921 | this transaction. If the sum of these adjustments |
922 | equals zero, they would not have been detected in |
923 | the payment schedule check above. |
924 +-----------------------------------------------------*/
925
926 SELECT DECODE( MAX(adjustment_id),
927 NULL, 'N',
928 'Y')
929 INTO l_activity_flag
930 FROM ar_adjustments
931 WHERE customer_trx_id = p_customer_trx_id;
932
933 IF (l_activity_flag = 'Y')
934 THEN RETURN('Y');
935 END IF;
936
937
938 /*-------------------------------------------------------------+
939 | If the transaction exists in the postbatch interim tables, |
940 | then consider the transaction to have activity. |
941 +-------------------------------------------------------------*/
942
943 SELECT DECODE( MAX( customer_trx_id ),
944 NULL, 'N',
945 'Y')
946 INTO l_activity_flag
947 FROM ar_interim_cash_receipts
948 WHERE customer_trx_id = p_customer_trx_id;
949
950 IF (l_activity_flag = 'Y')
951 THEN RETURN('Y');
952 END IF;
953
954
955 SELECT DECODE( MAX( customer_trx_id ),
956 NULL, 'N',
957 'Y')
958 INTO l_activity_flag
959 FROM ar_interim_cash_receipt_lines
960 WHERE customer_trx_id = p_customer_trx_id;
961
962 /* Bug 666136: If a transaction is included in a consolidated
966 NULL, 'N',
963 billling invoice, consider it as an activity so that the
964 consolidated billling number cannot be overwritten. */
965 SELECT DECODE( MAX( cons_inv_id ),
967 'Y')
968 INTO l_activity_flag
969 FROM ar_payment_schedules
970 WHERE customer_trx_id = p_customer_trx_id;
971
972 IF (l_activity_flag = 'Y')
973 THEN RETURN('Y');
974 END IF;
975
976 /*-------------------------------------------------------+
977 | 25-SEP-2000 J Rautiainen BR Implementation |
978 | Assignements to Bills Receivable are considered as |
979 | activities. |
980 +-------------------------------------------------------*/
981
982 SELECT DECODE( MAX(customer_trx_line_id),
983 NULL, 'N',
984 'Y')
985 INTO l_activity_flag
986 FROM ra_customer_trx_lines ctl
987 WHERE br_ref_customer_trx_id = p_customer_trx_id;
988
989 IF (l_activity_flag = 'Y')
990 THEN RETURN('Y');
991 END IF;
992
993 /* bug 811796 - check if the transaction has been posted to GL */
994
995 /*-------------------------------------------------------+
996 | 04-AUG-2000 J Rautiainen BR Implementation |
997 | For Bills Receivable check the posting from table |
998 | ar_transaction_history, for other types use the |
999 | existing logic. |
1000 +-------------------------------------------------------*/
1001
1002 IF (p_class = 'BR' ) THEN
1003
1004 SELECT DECODE( MAX( gl_posted_date ), NULL, 'N', 'Y')
1005 INTO l_activity_flag
1006 FROM ar_transaction_history
1007 WHERE customer_trx_id = p_customer_trx_id;
1008
1009 ELSE
1010
1011 SELECT DECODE( MAX( gl_posted_date ), NULL, 'N', 'Y')
1012 INTO l_activity_flag
1013 FROM ra_cust_trx_line_gl_dist
1014 WHERE customer_trx_id = p_customer_trx_id;
1015
1016 END IF;
1017
1018 IF (l_activity_flag = 'Y')
1019 /* bug 881066: changed the return flag to 'G' to indicate that the GL posting is done */
1020 THEN
1021 RETURN('G');
1022 END IF;
1023
1024 /* bug 2207354 - Check if the transaction has an incomplete
1025 credit memo against it */
1026 SELECT decode (MAX(previous_customer_trx_id), NULL, 'N', 'Y')
1027 INTO l_activity_flag
1028 FROM ra_customer_trx
1029 WHERE previous_customer_trx_id = p_customer_trx_id;
1030
1031 IF (l_activity_flag = 'Y')
1032 THEN RETURN('Y');
1033 END IF;
1034
1035
1036 END IF;
1037
1038 RETURN('N');
1039
1040 EXCEPTION
1041 WHEN OTHERS THEN
1042 RAISE;
1043
1044 END;
1045
1046 /*===========================================================================+
1047 | FUNCTION |
1048 | Get_Reference |
1049 | |
1050 | DESCRIPTION |
1051 | Returns the value of the column in the transaction flexfield that has |
1052 | been designated the reference column by the AR_PA_CODE profile. |
1053 | |
1054 | SCOPE - PUBLIC |
1055 | |
1056 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1057 | |
1058 | ARGUMENTS : IN: |
1062 | None |
1059 | p_trx_rowid |
1060 | |
1061 | OUT: |
1063 | |
1064 | RETURNS : The value of the reference column |
1065 | |
1066 | NOTES |
1067 | |
1068 | MODIFICATION HISTORY |
1069 | 27-OCT-95 Charlie Tomberg Created |
1070 | |
1071 +===========================================================================*/
1072
1073 FUNCTION Get_Reference( p_trx_rowid IN ROWID)
1074 RETURN varchar2 IS
1075
1076 l_temp varchar2(150);
1077
1078 BEGIN
1079 IF p_trx_rowid IS NOT NULL
1080
1081 THEN
1082 SELECT DECODE(DEFAULT_REFERENCE,
1083 '1', ct.interface_header_attribute1,
1084 '2', ct.interface_header_attribute2,
1085 '3', ct.interface_header_attribute3,
1086 '4', ct.interface_header_attribute4,
1087 '5', ct.interface_header_attribute5,
1088 '6', ct.interface_header_attribute6,
1089 '7', ct.interface_header_attribute7,
1090 '8', ct.interface_header_attribute8,
1091 '9', ct.interface_header_attribute9,
1092 '10', ct.interface_header_attribute10,
1093 '11', ct.interface_header_attribute11,
1094 '12', ct.interface_header_attribute12,
1095 '13', ct.interface_header_attribute13,
1096 '14', ct.interface_header_attribute14,
1097 '15', ct.interface_header_attribute15,
1098 NULL )
1099 INTO l_temp
1100 FROM ra_customer_trx ct,
1101 ra_batch_sources bs
1102 WHERE ct.rowid = p_trx_rowid and
1103 bs.batch_source_id = ct.batch_source_id;
1104 END IF;
1105
1106 RETURN(l_temp);
1107
1108 END;
1109
1110
1111 /*===========================================================================+
1112 | FUNCTION |
1113 | Get_Line_Reference |
1114 | |
1115 | DESCRIPTION |
1119 | SCOPE - PUBLIC |
1116 | Returns the value of the column in the transaction flexfield that has |
1117 | been designated the reference column by the AR_PA_CODE profile. |
1118 | |
1120 | |
1121 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1122 | |
1123 | ARGUMENTS : IN: |
1124 | p_line_trx_rowid |
1125 | |
1126 | OUT: |
1127 | None |
1128 | |
1129 | RETURNS : The value of the reference column |
1130 | |
1131 | NOTES |
1132 | |
1133 | MODIFICATION HISTORY |
1134 | 11-Dec-03 Surendra Rajan Created |
1135 | |
1136 +===========================================================================*/
1137
1138 FUNCTION Get_Line_Reference( p_line_trx_rowid IN ROWID)
1139 RETURN varchar2 IS
1140
1141 l_line_temp varchar2(150);
1142
1143 BEGIN
1144 IF p_line_trx_rowid IS NOT NULL
1145
1146 THEN
1147 SELECT DECODE(DEFAULT_REFERENCE,
1148 '1', ctl.interface_line_attribute1,
1149 '2', ctl.interface_line_attribute2,
1150 '3', ctl.interface_line_attribute3,
1151 '4', ctl.interface_line_attribute4,
1152 '5', ctl.interface_line_attribute5,
1153 '6', ctl.interface_line_attribute6,
1154 '7', ctl.interface_line_attribute7,
1155 '8', ctl.interface_line_attribute8,
1156 '9', ctl.interface_line_attribute9,
1157 '10', ctl.interface_line_attribute10,
1158 '11', ctl.interface_line_attribute11,
1159 '12', ctl.interface_line_attribute12,
1160 '13', ctl.interface_line_attribute13,
1161 '14', ctl.interface_line_attribute14,
1162 '15', ctl.interface_line_attribute15,
1163 NULL )
1164 INTO l_line_temp
1165 FROM ra_customer_trx_lines ctl,
1166 ra_customer_trx ct,
1167 ra_batch_sources bs
1168 WHERE bs.batch_source_id = ct.batch_source_id and
1169 ctl.customer_trx_id = ct.customer_trx_id and
1170 ctl.rowid = p_line_trx_rowid ;
1171 END IF;
1172
1173 RETURN(l_line_temp);
1174
1175 END;
1176
1177 /*===========================================================================+
1181 | DESCRIPTION |
1178 | PROCEDURE |
1179 | Set_Reference_Column |
1180 | |
1182 | Sets a package variable with the value of the AR_PA_CODE profile. |
1183 | |
1184 | SCOPE - PUBLIC |
1185 | |
1186 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1187 | |
1188 | ARGUMENTS : IN: |
1189 | p_reference column |
1190 | |
1191 | OUT: |
1192 | None |
1193 | |
1194 | NOTES |
1195 | |
1196 | MODIFICATION HISTORY |
1197 | 27-OCT-95 Charlie Tomberg Created |
1198 | |
1199 +===========================================================================*/
1200
1201 PROCEDURE Set_Reference_Column(p_reference_column IN varchar2 ) AS
1202
1203 BEGIN
1204 pg_reference_column := p_reference_column;
1205
1206 END;
1207
1208
1209
1210
1211 /*===========================================================================+
1212 | FUNCTION |
1213 | Get_First_Due_Date |
1214 | |
1215 | DESCRIPTION |
1216 | Gets the first due date given the term and the transaction date |
1217 | |
1218 | SCOPE - PUBLIC |
1219 | |
1220 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1221 | |
1222 | ARGUMENTS : IN: |
1223 | p_term_id |
1224 | p_trx_date |
1225 | |
1226 | OUT: |
1227 | None |
1228 | |
1229 | NOTES |
1233 | |
1230 | |
1231 | MODIFICATION HISTORY |
1232 | 06-NOV-95 Charlie Tomberg Created |
1234 +===========================================================================*/
1235
1236 FUNCTION Get_First_Due_Date( p_term_id IN number,
1237 p_trx_date IN date)
1238 RETURN DATE IS
1239
1240 l_term_due_date DATE;
1241 l_bill_cycle NUMBER;
1242 l_bill_date DATE;
1243 l_bill_cycle_type VARCHAR2(30);
1244
1245 BEGIN
1246
1247 IF (
1248 p_term_id IS NOT NULL
1249 AND p_trx_date IS NOT NULL
1250 )
1251 THEN
1252
1253 IF ar_bfb_utils_pvt.is_payment_term_bfb(p_term_id) = 'Y' then
1254
1255 l_bill_cycle := ar_bfb_utils_pvt.get_billing_cycle(p_term_id);
1256
1257 l_bill_cycle_type := ar_bfb_utils_pvt.get_cycle_type(l_bill_cycle);
1258
1259 IF (l_bill_cycle_type is NOT NULL AND l_bill_cycle_type <> 'EVENT') THEN
1260 l_bill_date := ar_bfb_utils_pvt.get_billing_date
1264 l_term_due_date := ar_bfb_utils_pvt.get_due_date
1261 (l_bill_cycle,
1262 p_trx_date);
1263
1265 (l_bill_date, p_term_id);
1266
1267 RETURN( l_term_due_date );
1268 END IF;
1269 END IF;
1270
1271 SELECT
1272 DECODE( tl.due_days,
1273 NULL, NVL( tl.due_date,
1274 DECODE ( LEAST(
1275 TO_NUMBER(
1276 TO_CHAR(p_trx_date,
1277 'DD') ),
1278 NVL(t.due_cutoff_day, 32)
1279 ),
1280 t.due_cutoff_day,
1281 LAST_DAY(
1282 ADD_MONTHS(
1283 p_trx_date,
1284 tl.due_months_forward
1285 ) )
1286 + LEAST(tl.due_day_of_month,
1287 TO_NUMBER(
1288 TO_CHAR(
1289 LAST_DAY(
1290 ADD_MONTHS(p_trx_date,
1291 tl.due_months_forward +
1292 1 )
1293 ), 'DD'
1294 ) ) ),
1295 /*BUG 1702687 --ADDED decode(tl.due....)*/
1296 /* BUG 2019477 -- ADDED the decode(sign(trunc(t */
1297
1298 LAST_DAY( ADD_MONTHS(p_trx_date,
1299 (tl.due_months_forward +decode(tl.due_months_forward-trunc(tl.due_months_forward),0,-1,0)))+
1300 decode(sign(trunc(tl.due_months_forward)-tl.due_months_forward),-1,
1301 decode(sign(((TO_NUMBER(TO_CHAR(p_trx_date,'DD')))+
1302 (tl.due_months_forward-trunc(tl.due_months_forward))*30)-t.due_cutoff_day),-1,-30,0),0)
1303
1304 /*BUG 1702687 ends */
1305 ) +
1306 LEAST( tl.due_day_of_month,
1307 TO_NUMBER(
1308 TO_CHAR(
1309 LAST_DAY(
1310 ADD_MONTHS(p_trx_date,
1311 tl.due_months_forward)
1312 ), 'DD'
1313 ) )
1314 )
1315 )
1316 ),
1317 p_trx_date + tl.due_days
1318 )
1319 INTO l_term_due_date
1320 FROM ra_terms_lines tl,
1321 ra_terms t
1322 WHERE tl.term_id = p_term_id
1323 AND t.term_id = tl.term_id
1324 AND tl.sequence_num = (
1325 SELECT MIN(sequence_num)
1326 FROM ra_terms_lines
1327 WHERE term_id = p_term_id
1328 );
1329 END IF;
1330
1331 RETURN( l_term_due_date );
1332
1333 EXCEPTION
1334 WHEN NO_DATA_FOUND THEN NULL;
1335 WHEN OTHERS THEN RAISE;
1336
1337 END;
1338
1339
1340
1341 /*===========================================================================+
1342 | FUNCTION |
1343 | Get_First_Real_Due_Date |
1344 | |
1345 | DESCRIPTION |
1349 | the function Get_First_Due_Date in this package. |
1346 | Gets the first real due date given the customer_trx_id, based on the |
1347 | payment schedule record for the first due installment. If there is no |
1348 | payment schedule record, it calculates the first due date by calling |
1350 | |
1351 | This function is used in view ra_customer_trx_partial_v |
1352 | and was created in response to bug 486822. |
1353 | |
1354 | SCOPE - PUBLIC |
1355 | |
1356 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1357 | |
1358 | ARGUMENTS : IN: |
1359 | p_customer_trx_id |
1360 | p_term_id |
1361 | p_trx_date |
1362 | |
1363 | OUT: |
1364 | None |
1365 | |
1366 | NOTES |
1370 | |
1367 | |
1368 | MODIFICATION HISTORY |
1369 | 01-MAY-97 OSTEINME Created |
1371 +===========================================================================*/
1372
1373 FUNCTION Get_First_Real_Due_Date( p_customer_trx_id IN number,
1374 p_term_id IN number,
1375 p_trx_date IN date)
1376 RETURN DATE IS
1377
1378 l_term_due_date DATE;
1379
1380 BEGIN
1381 --Bug fix form 5589303, if the billing_date is not null, billing date shall be passed to calculate due date
1382
1383 SELECT NVL(MIN(ps.due_date),
1384 arpt_sql_func_util.get_first_due_date(p_term_id, nvl(ct.billing_date, p_trx_date)))
1385 INTO l_term_due_date
1386 FROM ar_payment_schedules ps,
1387 ra_customer_trx ct
1388 WHERE ct.customer_trx_id=ps.customer_trx_id(+)
1389 AND ct.customer_trx_id = p_customer_trx_id
1390 group by ct.billing_date;
1391
1392 RETURN( l_term_due_date );
1393
1394 EXCEPTION
1395 WHEN NO_DATA_FOUND THEN RETURN NULL;
1396 WHEN OTHERS THEN RAISE;
1397
1398 END;
1399
1400
1401
1402 /*===========================================================================+
1403 | FUNCTION |
1404 | Get_Number_Of_Due_Dates |
1405 | |
1406 | DESCRIPTION |
1407 | Gets the number of due dates on a payment term. |
1408 | |
1409 | SCOPE - PUBLIC |
1410 | |
1411 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1412 | |
1413 | ARGUMENTS : IN: |
1414 | p_term_id |
1415 | |
1416 | OUT: |
1417 | None |
1418 | |
1419 | NOTES |
1420 | |
1421 | MODIFICATION HISTORY |
1422 | 16-NOV-95 Charlie Tomberg Created |
1423 | |
1424 +===========================================================================*/
1425
1426 FUNCTION Get_Number_Of_Due_Dates( p_term_id IN number)
1427 RETURN NUMBER IS
1428
1429 l_count number;
1430
1431 BEGIN
1432
1433 IF ( p_term_id IS NOT NULL )
1434 THEN
1435
1436 SELECT COUNT(*)
1440
1437 INTO l_count
1438 FROM ra_terms_lines
1439 WHERE term_id = p_term_id;
1441 END IF;
1442
1443 RETURN( l_count );
1444
1445 EXCEPTION
1446 WHEN OTHERS THEN RAISE;
1447
1448 END;
1449
1450 /*===========================================================================+
1451 | FUNCTION |
1452 | get_period_name |
1453 | |
1454 | DESCRIPTION |
1455 | Gets the period name based on the incoming gl date. |
1456 | Used by the ra_batches_v view. |
1457 | |
1458 | SCOPE - PUBLIC |
1459 | |
1460 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1461 | |
1462 | ARGUMENTS : IN: |
1463 | p_gl_date |
1464 | |
1465 | OUT: |
1466 | None |
1467 | |
1468 | NOTES |
1469 | |
1470 | MODIFICATION HISTORY |
1471 | 04-JUN-96 Simon Leung Created. |
1472 | |
1473 +===========================================================================*/
1474
1475 FUNCTION get_period_name( p_gl_date IN DATE )
1476 RETURN VARCHAR2 IS
1477
1478 l_period_name gl_period_statuses.period_name%TYPE;
1479
1480 CURSOR c_period_name IS
1481 select /*+use_nl(sp,gps) index(gps gl_period_statuses_u1)*/
1482 gps.period_name
1483 from gl_period_statuses gps,
1484 ar_system_parameters sp
1485 where gps.application_id = 222
1486 and gps.adjustment_period_flag = 'N'
1487 and gps.set_of_books_id = sp.set_of_books_id
1488 and p_gl_date between gps.start_date and gps.end_date;
1489 BEGIN
1490 l_period_name := NULL;
1491 IF ( p_gl_date IS NOT NULL ) THEN
1492 OPEN c_period_name;
1493 FETCH c_period_name INTO l_period_name;
1494 CLOSE c_period_name;
1495 END IF;
1496 RETURN( l_period_name );
1497
1498 EXCEPTION
1499 WHEN OTHERS THEN RAISE;
1500 END;
1501
1502 /*===========================================================================+
1503 | FUNCTION |
1504 | get_territory |
1505 | |
1506 | DESCRIPTION |
1510 | SCOPE - PUBLIC |
1507 | Gets the territory name based on the incoming address id. |
1508 | Used by the ra_customer_trx_cr_trx_v view. |
1509 | |
1511 | |
1512 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1513 | |
1514 | ARGUMENTS : IN: |
1515 | p_address_id |
1516 | |
1517 | OUT: |
1518 | None |
1519 | |
1520 | NOTES |
1521 | |
1522 | MODIFICATION HISTORY |
1523 | 04-JUN-96 Simon Leung Created. |
1524 +===========================================================================*/
1525
1526 FUNCTION get_territory( p_address_id IN NUMBER )
1527 RETURN VARCHAR2 IS
1528
1529 l_territory fnd_territories_vl.territory_short_name%TYPE;
1530
1531 /* modified for tca uptake */
1532 CURSOR c_territory IS
1533 select ft.territory_short_name
1534 from fnd_territories_vl ft,
1535 hz_cust_acct_sites acct_site,
1536 hz_party_sites party_site,
1537 hz_locations loc
1538 where loc.country = ft.territory_code
1539 and acct_site.party_site_id = party_site.party_site_id
1540 and loc.location_id = party_site.location_id
1541 and acct_site.cust_acct_site_id = p_address_id;
1542
1543 BEGIN
1544 l_territory := NULL;
1545 IF ( p_address_id IS NOT NULL ) THEN
1546 OPEN c_territory;
1547 FETCH c_territory INTO l_territory;
1548 CLOSE c_territory;
1549 END IF;
1550 RETURN (l_territory);
1551
1552 EXCEPTION
1553 WHEN OTHERS THEN RAISE;
1554 END;
1555
1556 /*===========================================================================+
1557 | FUNCTION |
1558 | get_territory_rowid |
1559 | |
1560 | DESCRIPTION |
1561 | Gets the territory row id based on the incoming address id. |
1562 | Used by the ra_customer_trx_cm_v view. |
1563 | |
1564 | SCOPE - PUBLIC |
1565 | |
1566 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1567 | |
1568 | ARGUMENTS : IN: |
1569 | p_address_id |
1570 | |
1574 | NOTES |
1571 | OUT: |
1572 | None |
1573 | |
1575 | |
1576 | MODIFICATION HISTORY |
1577 | 04-JUN-96 Simon Leung Created. |
1578 +===========================================================================*/
1579
1580 FUNCTION get_territory_rowid( p_address_id IN NUMBER )
1581 RETURN ROWID IS
1582
1583 l_territory_rowid ROWID;
1584
1585 /* modified for tca uptake */
1586 CURSOR c_territory IS
1587 select ft.rowid
1588 from fnd_territories_vl ft,
1589 hz_cust_acct_sites acct_site,
1590 hz_party_sites party_site,
1591 hz_locations loc
1592 where loc.country = ft.territory_code
1593 and acct_site.party_site_id = party_site.party_site_id
1594 and loc.location_id = party_site.location_id
1595 and acct_site.cust_acct_site_id = p_address_id;
1596
1597 BEGIN
1598 l_territory_rowid := NULL;
1599 IF ( p_address_id IS NOT NULL ) THEN
1600 OPEN c_territory;
1601 FETCH c_territory INTO l_territory_rowid;
1602 CLOSE c_territory;
1603 END IF;
1604 RETURN (l_territory_rowid);
1605
1606 EXCEPTION
1607 WHEN OTHERS THEN RAISE;
1608 END;
1609
1610 /*===========================================================================+
1611 | FUNCTION |
1612 | get_commitments_exist_flag |
1613 | |
1614 | DESCRIPTION |
1615 | Determines whether any commitments can be used for a customer. |
1616 | |
1617 | SCOPE - PUBLIC |
1618 | |
1619 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1620 | |
1621 | ARGUMENTS : IN: |
1622 | p_bill_to_customer_id |
1623 | p_invoice_currency_code |
1624 | p_previous_customer_trx_id |
1625 | p_ct_prev_initial_cust_trx_id |
1626 | p_trx_date |
1627 | p_code_combination_id_gain |
1628 | p_base_currency |
1629 | |
1630 | OUT: |
1631 | None |
1632 | |
1633 | NOTES |
1634 | |
1635 | MODIFICATION HISTORY |
1636 | 19-JUN-96 Charlie Tomberg Created. |
1637 +===========================================================================*/
1638
1639 FUNCTION get_commitments_exist_flag(
1640 p_bill_to_customer_id IN number,
1641 p_invoice_currency_code IN varchar2,
1642 p_previous_customer_trx_id IN number,
1643 p_trx_date IN date,
1644 p_ct_prev_initial_cust_trx_id IN number
1645 DEFAULT NULL,
1646 p_code_combination_id_gain IN number
1647 DEFAULT NULL,
1648 p_base_currency IN varchar2
1649 DEFAULT NULL)
1650 RETURN varchar2 IS
1651
1652 l_commitments_exist_flag varchar2(1);
1653 l_code_combination_id_gain number;
1654 l_base_currency varchar2(15);
1655 l_ct_prev_initial_cust_trx_id number;
1656 l_trx_date date;
1657 BEGIN
1658
1659 IF (
1660 p_code_combination_id_gain IS NULL
1661 OR p_base_currency IS NULL
1662 )
1663 THEN
1664 SELECT sp.code_combination_id_gain,
1665 sb.currency_code
1666 INTO l_code_combination_id_gain,
1667 l_base_currency
1668 FROM ar_system_parameters sp,
1669 gl_sets_of_books sb
1670 WHERE sp.set_of_books_id = sb.set_of_books_id;
1671
1672 ELSE
1673 l_code_combination_id_gain := p_code_combination_id_gain;
1674 l_base_currency := p_base_currency;
1675 END IF;
1676
1677
1678 IF (
1679 p_previous_customer_trx_id IS NOT NULL
1680 AND p_ct_prev_initial_cust_trx_id IS NULL
1681 )
1682 THEN
1683 SELECT MAX(initial_customer_trx_id)
1687 ELSE
1684 INTO l_ct_prev_initial_cust_trx_id
1685 FROM ra_customer_trx
1686 WHERE customeR_trx_id = p_previous_customer_trx_id;
1688 l_ct_prev_initial_cust_trx_id := p_ct_prev_initial_cust_trx_id;
1689 END IF;
1690
1691 l_trx_date := NVL(p_trx_date, trunc(sysdate));
1692
1693 SELECT DECODE( MAX(dummy),
1694 NULL, 'N',
1695 'Y' )
1696 INTO l_commitments_exist_flag
1697 FROM DUAL
1698 WHERE EXISTS
1699 (
1700 SELECT 'commitments_exist'
1701 FROM hz_cust_accounts cust_acct,
1702 so_agreements soa,
1703 hz_cust_acct_sites acct_site,
1704 ra_cust_trx_types inv_type,
1705 ra_cust_trx_types type,
1706 ra_customer_trx trx
1707 WHERE trx.cust_trx_type_id = type.cust_trx_type_id
1708 AND trx.bill_to_customer_id = cust_acct.cust_account_id
1709 AND trx.remit_to_address_id = acct_site.cust_acct_site_id(+)
1710 AND 'A' = acct_site.status(+)
1711 AND trx.agreement_id = soa.agreement_id(+)
1712 AND type.subsequent_trx_type_id = inv_type.cust_trx_type_id(+)
1713 AND 'A' = inv_type.status(+)
1714 AND type.type in ('DEP','GUAR')
1715 AND trx.complete_flag = 'Y'
1716 AND trx.bill_to_customer_id
1717 in (
1718 select distinct cr.cust_account_id
1719 from hz_cust_acct_relate cr
1720 where cr.related_cust_account_id = p_bill_to_customer_id
1721 AND status = 'A'
1722 union
1723 select to_number(p_bill_to_customer_id)
1724 from dual
1725 UNION
1726 SELECT acc.cust_account_id
1727 FROM ar_paying_relationships_v rel,
1728 hz_cust_accounts acc
1729 WHERE rel.party_id = acc.party_id
1730 AND rel.related_cust_account_id = p_bill_to_customer_id
1731 AND l_trx_date BETWEEN effective_start_date
1732 AND effective_end_date
1733
1734 )
1735 AND trx.invoice_currency_code =
1736 DECODE(l_code_combination_id_gain,
1737 NULL, l_base_currency,
1738 p_invoice_currency_code
1739 ) /* non-on account credit memos must have the same
1740 commitment as the transactions that they are
1741 crediting. */
1742 AND (
1743 p_previous_customer_trx_id is NULL
1744 or trx.customer_trx_id = l_ct_prev_initial_cust_trx_id
1745 )
1746 /* check effectivity dates */
1747 AND l_trx_date
1748 BETWEEN NVL(trx.start_date_commitment, l_trx_date)
1749 AND NVL(trx.end_date_commitment, l_trx_date)
1750 AND l_trx_date
1751 BETWEEN NVL( soa.start_date_active(+), l_trx_date)
1752 AND NVL( soa.end_date_active(+), l_trx_date)
1753 AND l_trx_date
1754 BETWEEN NVL( inv_type.start_date(+), l_trx_date)
1755 AND NVL( inv_type.end_date(+), l_trx_date)
1756 );
1757
1758 RETURN( l_commitments_exist_flag );
1759
1760
1761 EXCEPTION
1762 WHEN OTHERS THEN RAISE;
1763 END;
1764
1765
1766 /*===========================================================================+
1767 | FUNCTION |
1768 | get_agreements_exist_flag |
1769 | |
1770 | DESCRIPTION |
1771 | Determines whether any agreements can be used for a customer. |
1772 | |
1773 | SCOPE - PUBLIC |
1774 | |
1775 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1776 | |
1777 | ARGUMENTS : IN: |
1778 | p_bill_to_customer_id |
1779 | p_trx_date |
1780 | |
1781 | OUT: |
1782 | None |
1783 | |
1784 | NOTES |
1788 | 10/10/1996 Harri Kaukovuo Changed UNION to UNION ALL to get little
1785 | |
1786 | MODIFICATION HISTORY |
1787 | 20-JUN-96 Charlie Tomberg Created. |
1789 | better performance.
1790 +===========================================================================*/
1791
1792 FUNCTION get_agreements_exist_flag(
1793 p_bill_to_customer_id IN number,
1794 p_trx_date IN date )
1795 RETURN varchar2 IS
1796
1797 l_agreements_exist_flag varchar2(1);
1798
1799 BEGIN
1800
1801 SELECT DECODE( MAX(dummy),
1802 NULL, 'N',
1803 'Y' )
1804 INTO l_agreements_exist_flag
1805 FROM DUAL
1806 WHERE EXISTS
1807 (
1808 SELECT 'agreements_exist'
1809 FROM so_agreements a
1810 WHERE a.customer_id IN
1811 (
1812 SELECT cr.cust_account_id
1813 FROM hz_cust_acct_relate cr
1814 WHERE cr.related_cust_account_id = p_bill_to_customer_id
1815 AND cr.status = 'A'
1816 UNION ALL
1817 SELECT to_number(p_bill_to_customer_id)
1818 FROM dual
1819 UNION ALL
1820 SELECT -1 /* no customer case */
1821 FROM dual
1822 )
1823 AND p_trx_date
1824 BETWEEN NVL( TRUNC( a.start_date_active ), p_trx_date )
1825 AND NVL( TRUNC( a.end_date_active ), p_trx_date )
1826 );
1827 RETURN( l_agreements_exist_flag );
1828
1829
1830 EXCEPTION
1831 WHEN OTHERS THEN RAISE;
1832 END;
1833
1834
1835
1836 /*===========================================================================+
1837 | FUNCTION |
1838 | get_override_terms |
1839 | |
1840 | DESCRIPTION |
1841 | Gets the override_terms value from the customer profile. |
1842 | |
1843 | SCOPE - PUBLIC |
1844 | |
1845 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1846 | |
1847 | ARGUMENTS : IN: |
1848 | p_customer_id |
1849 | p_site_use_id |
1850 | |
1851 | OUT: |
1852 | None |
1853 | |
1854 | NOTES |
1855 | |
1856 | MODIFICATION HISTORY |
1857 | 19-JUN-96 Charlie Tomberg Created.
1858 | 10/10/1996 Harri Kaukovuo Removed extra join to ra customers
1859 | table.
1860 +===========================================================================*/
1861
1862 FUNCTION get_override_terms(
1863 p_customer_id IN number,
1864 p_site_use_id IN NUMBER )
1865 RETURN varchar2 IS
1866
1867 l_override_terms VARCHAR2(1);
1868
1869 BEGIN
1870
1871 SELECT NVL(site.override_terms, cust.override_terms)
1872 INTO l_override_terms
1873 FROM hz_customer_profiles cust,
1874 hz_customer_profiles site
1875 WHERE cust.cust_account_id = p_customer_id
1876 AND cust.site_use_id IS NULL
1877 AND site.cust_account_id (+) = cust.cust_account_id
1878 AND site.site_use_id (+) = NVL(p_site_use_id,-44444);
1879
1880 RETURN( l_override_terms );
1881
1882
1883 EXCEPTION
1884 WHEN OTHERS THEN RAISE;
1885 END;
1886
1887
1888 /*===========================================================================+
1889 | FUNCTION |
1890 | get_bs_name_for_cb_invoice |
1891 | |
1892 | DESCRIPTION |
1893 | Gets the batch source name of the invoice associated with a Chargeback.|
1894 | |
1895 | SCOPE - PUBLIC |
1896 | |
1897 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1898 | |
1899 | ARGUMENTS : IN: |
1900 | p_customer_trx_id of the CB type transaction |
1901 | p_class |
1902 | OUT: |
1906 | chargeback. |
1903 | None |
1904 | |
1905 | RETURNS : Batch Source Name of the Invoice associated with the |
1907 | |
1908 | NOTES : Currently being used by -> AR_PAYMENT_SCHEDULES_V |
1909 | |
1910 | |
1911 | MODIFICATION HISTORY |
1912 | |
1913 | 08-MAY-1997 Neeraj Tandon Created Bug Fix : 480077 |
1914 +===========================================================================*/
1915
1916 FUNCTION get_bs_name_for_cb_invoice ( p_class IN varchar2,
1917 p_customer_trx_id IN number
1918 )
1919 RETURN VARCHAR2 is
1920
1921 l_bs_name ra_batch_sources.name%type;
1922 l_customer_trx_id ra_customer_trx.customer_trx_id%type;
1923
1924 BEGIN
1925
1926 IF ( p_class <> 'CB' ) THEN
1927 RETURN( NULL );
1928 ELSE
1929
1930 /*------------------------------------------------------------------------------------+
1931 | Find the Customer_Trx_Id of the Invoice Related with Transaction of Type 'CB' |
1932 | and then pass it as a parameter to the next SQL to determine the Batch Source Name |
1933 +------------------------------------------------------------------------------------*/
1934
1935 select bs.name
1936 into l_bs_name
1937 from ra_customer_trx ct,
1938 ra_batch_sources bs
1939 where ct.batch_source_id = bs.batch_source_id
1940 and ct.org_id = bs.org_id --anuj
1941 and ct.customer_trx_id = (select max(ctt.customer_trx_id)
1942 from ra_customer_trx ctt,
1943 ar_adjustments aa
1944 where aa.chargeback_customer_trx_id = p_customer_trx_id
1945 and aa.org_id = ctt.org_id --anuj
1946 and aa.customer_trx_id = ctt.customer_trx_id
1947 );
1948
1949 RETURN l_bs_name;
1950
1951 END IF;
1952
1953 EXCEPTION
1954 WHEN OTHERS THEN
1955 RAISE;
1956
1957 END;
1958
1959 /*===========================================================================+
1960 | FUNCTION |
1961 | get_dunning_date_last |
1962 | |
1963 | DESCRIPTION |
1964 | Gets the lastest dunning date of a trsanction |
1965 | |
1966 | SCOPE - PUBLIC |
1967 | |
1968 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1969 | |
1970 | ARGUMENTS : IN: |
1971 | p_payment_schedule_id |
1972 | |
1973 | OUT: |
1974 | none |
1975 | |
1976 | RETURNS : the lastest dunning date |
1977 | |
1978 | |
1979 | NOTES : Currently being used by -> AR_PAYMENT_SCHEDULES_V |
1980 | |
1981 | |
1982 | MODIFICATION HISTORY |
1983 | |
1984 | 01-JUN-1998 Yangya Kong Created Bug Fix : 627071 |
1985 +===========================================================================*/
1986 FUNCTION get_dunning_date_last (p_payment_schedule_id
1987 IN ar_correspondence_pay_sched.payment_schedule_id%type)
1988
1989 RETURN DATE is
1990
1991 l_dunning_date_last ar_correspondences.correspondence_date%type;
1992
1993 BEGIN
1994
1995 select MAX(arc.correspondence_date)
1996 into l_dunning_date_last
1997 from ar_correspondences arc,
1998 ar_correspondence_pay_sched arcps
1999 where arcps.payment_schedule_id = p_payment_schedule_id
2000 and arc.correspondence_id = arcps.correspondence_id
2001 and nvl(arc.preliminary_flag,'N') = 'N';
2002
2003 RETURN (l_dunning_date_last);
2004
2005 EXCEPTION
2006 WHEN OTHERS THEN
2007 RAISE;
2008
2009 END;
2010
2011 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2,
2012 p_lookup_code IN VARCHAR2)
2013 RETURN VARCHAR2 IS
2014 l_meaning ar_lookups.meaning%TYPE;
2015 l_hash_value NUMBER;
2016 BEGIN
2017 IF p_lookup_code IS NOT NULL AND
2018 p_lookup_type IS NOT NULL THEN
2019
2023 25000);
2020 l_hash_value := DBMS_UTILITY.get_hash_value(
2021 p_lookup_type||'@*?'||p_lookup_code,
2022 1000,
2024
2025 IF pg_ar_lookups_rec.EXISTS(l_hash_value) THEN
2026 l_meaning := pg_ar_lookups_rec(l_hash_value);
2027 ELSE
2028
2029 SELECT meaning
2030 INTO l_meaning
2031 FROM ar_lookups
2032 WHERE lookup_type = p_lookup_type
2033 AND lookup_code = p_lookup_code ;
2034
2035 pg_ar_lookups_rec(l_hash_value) := l_meaning;
2036
2037 END IF;
2038
2039 END IF;
2040
2041 return(l_meaning);
2042
2043 EXCEPTION
2044 WHEN no_data_found THEN
2045 return(null);
2046 WHEN OTHERS THEN
2047 raise;
2048 END;
2049
2050 --Add for bug 12955526
2051 FUNCTION get_fndlookup_meaning (p_lookup_type IN VARCHAR2,
2052 p_lookup_code IN VARCHAR2)
2053 RETURN VARCHAR2 IS
2054 l_meaning fnd_lookups.meaning%TYPE;
2055 BEGIN
2056 IF p_lookup_code IS NOT NULL AND
2057 p_lookup_type IS NOT NULL THEN
2058
2059
2060 SELECT meaning
2061 INTO l_meaning
2062 FROM fnd_lookups
2063 WHERE lookup_type = p_lookup_type
2064 AND lookup_code = p_lookup_code
2065 AND enabled_flag = 'Y';
2066
2067 END IF;
2068
2069 return(l_meaning);
2070
2071 EXCEPTION
2072 WHEN no_data_found THEN
2073 return(null);
2074 WHEN OTHERS THEN
2075 raise;
2076 END;
2077
2078
2079 --
2080 --The function get_salesrep_name_number would return a null in case if an
2081 --invalid or null salesrep_id being passed in
2082 --
2083 FUNCTION get_salesrep_name_number (p_salesrep_id IN NUMBER,
2084 p_name_number IN VARCHAR2,
2085 --begin anuj
2086 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
2087 p_org_id IN NUMBER DEFAULT NULL)
2088 /* Multi-Org Access Control Changes for SSA;End;anukumar;11/01/2002*/
2089 --end anuj
2090 RETURN VARCHAR2 IS
2091 l_salesrep_name VARCHAR2(240);
2092 l_salesrep_number VARCHAR2(30);
2093 BEGIN
2094
2095 IF p_salesrep_id IS NOT NULL THEN
2096 IF pg_salesrep_rec.EXISTS(p_salesrep_id) THEN
2097
2098 l_salesrep_name:= pg_salesrep_rec(p_salesrep_id).salesrep_name;
2099 l_salesrep_number := pg_salesrep_rec(p_salesrep_id).salesrep_number;
2100 ELSE
2101
2102 SELECT name, salesrep_number
2103 INTO l_salesrep_name, l_salesrep_number
2104 FROM ra_salesreps
2105 WHERE salesrep_id = p_salesrep_id and
2106 --begin anuj
2107 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
2108 org_id = p_org_id;
2109 /* Multi-Org Access Control Changes for SSA;end;anukumar;11/01/2002*/
2110 --end anuj
2111
2112 pg_salesrep_rec(p_salesrep_id).salesrep_name := l_salesrep_name;
2113 pg_salesrep_rec(p_salesrep_id).salesrep_number := l_salesrep_number;
2114
2115 END IF;
2116 END IF;
2117
2118 IF p_name_number = 'NAME' THEN
2119 RETURN(l_salesrep_name);
2120 ELSIF p_name_number = 'NUMBER' THEN
2121 RETURN(l_salesrep_number);
2122 ELSE
2123 RETURN(null);
2124 END IF;
2125
2126 EXCEPTION
2127 WHEN no_data_found THEN
2128 RETURN(null);
2129 WHEN others THEN
2130 raise;
2131
2132 END get_salesrep_name_number;
2133
2134 /* Bug 2544852/2558527 : increase size of l_territory_short_name from 60 to 80 */
2135
2136 FUNCTION get_address_details (p_address_id IN NUMBER,
2137 p_detail_type IN VARCHAR2
2138 )
2139 RETURN VARCHAR2 IS
2140 l_add1 VARCHAR2(240);
2141 l_add2 VARCHAR2(240);
2142 l_add3 VARCHAR2(240);
2143 l_add4 VARCHAR2(240);
2144 l_city VARCHAR2(60);
2145 l_state VARCHAR2(60);
2146 l_province VARCHAR2(60);
2147 l_territory_short_name VARCHAR2(80);
2148 l_postal_code VARCHAR2(60);
2149 l_country VARCHAR2(60);
2150 l_status VARCHAR2(1);
2151 BEGIN
2152
2153 IF p_address_id IS NOT NULL THEN
2154
2155 IF pg_address_rec.EXISTS(p_address_id) THEN
2156 l_add1 := pg_address_rec(p_address_id).add1;
2157 l_add2 := pg_address_rec(p_address_id).add2;
2158 l_add3 := pg_address_rec(p_address_id).add3;
2159 l_add4 := pg_address_rec(p_address_id).add4;
2160 l_city := pg_address_rec(p_address_id).city;
2161 l_state := pg_address_rec(p_address_id).state;
2162 l_province := pg_address_rec(p_address_id).province;
2163 l_postal_code := pg_address_rec(p_address_id).postal_code;
2164 l_country := pg_address_rec(p_address_id).country;
2165 l_status := pg_address_rec(p_address_id).status;
2166 l_territory_short_name := pg_address_rec(p_address_id).territory_short_name;
2167 ELSE
2168
2169 /* modified for tca uptake */
2170 SELECT loc.ADDRESS1, loc.ADDRESS2, loc.ADDRESS3, loc.ADDRESS4,
2171 loc.CITY, loc.STATE, loc.PROVINCE, loc.POSTAL_CODE, loc.COUNTRY,
2172 acct_site.STATUS
2173 INTO l_add1, l_add2, l_add3, l_add4,
2174 l_city, l_state,l_province, l_postal_code, l_country,
2175 l_status
2176 FROM hz_cust_acct_sites acct_site,
2177 hz_party_sites party_site,
2178 hz_locations loc
2179 WHERE acct_site.party_site_id = party_site.party_site_id
2180 AND loc.location_id = party_site.location_id
2184 SELECT territory_short_name
2181 AND acct_site.cust_acct_site_id = p_address_id;
2182
2183 BEGIN
2185 INTO l_territory_short_name
2186 FROM fnd_territories_vl
2187 WHERE territory_code = l_country;
2188 EXCEPTION
2189 WHEN no_data_found THEN
2190 l_territory_short_name := null;
2191 END;
2192
2193 pg_address_rec(p_address_id).add1 := l_add1;
2194 pg_address_rec(p_address_id).add2 := l_add2;
2195 pg_address_rec(p_address_id).add3 := l_add3;
2196 pg_address_rec(p_address_id).add4 := l_add4;
2197 pg_address_rec(p_address_id).city := l_city;
2198 pg_address_rec(p_address_id).state := l_state;
2199 pg_address_rec(p_address_id).province := l_province;
2200 pg_address_rec(p_address_id).postal_code := l_postal_code;
2201 pg_address_rec(p_address_id).country := l_country;
2202 pg_address_rec(p_address_id).status := l_status;
2203 pg_address_rec(p_address_id).territory_short_name := l_territory_short_name;
2204 END IF;
2205
2206 IF p_detail_type = 'ADD1' THEN
2207 return(l_add1);
2208 ELSIF p_detail_type = 'ADD2' THEN
2209 return(l_add2);
2210 ELSIF p_detail_type = 'ADD3' THEN
2211 return(l_add3);
2212 ELSIF p_detail_type = 'ADD4' THEN
2213 return(l_add4);
2214 ELSIF p_detail_type = 'CITY' THEN
2215 return(l_city);
2216 ELSIF p_detail_type = 'PROVINCE' THEN
2217 return(l_province);
2218 ELSIF p_detail_type = 'STATE' THEN
2219 return(l_state);
2220 ELSIF p_detail_type = 'POSTAL_CODE' THEN
2221 return(l_postal_code);
2222 ELSIF p_detail_type = 'TER_SHORT_NAME' THEN
2223 return(l_territory_short_name);
2224 ELSIF p_detail_type = 'ALL' THEN
2225 return(substrb(l_add1,1,25)||' '|| substrb(l_add2,1,25)
2226 ||' '|| substrb(l_add3,1,25)||' '|| substrb(l_add4,1,25)
2227 ||' '|| l_city||','|| ' '||nvl(l_state,
2228 l_province)||' '|| l_territory_short_name);
2229 ELSIF p_detail_type = 'STATUS' THEN
2230 return(l_status);
2231 END IF;
2232
2233 ELSE
2234 return(null);
2235 END IF;
2236
2237 EXCEPTION
2238 WHEN no_data_found THEN
2239 return(null);
2240
2241 END get_address_details;
2242
2243 FUNCTION get_phone_details (p_phone_id IN NUMBER,
2244 p_detail_type IN VARCHAR2)
2245 RETURN VARCHAR2 IS
2246 l_ph_num VARCHAR2(50);
2247 l_area_code VARCHAR2(10);
2248 l_ext VARCHAR2(20);
2249 BEGIN
2250
2251 IF p_phone_id IS NOT NULL THEN
2252
2253 IF pg_phone_rec.EXISTS(p_phone_id) THEN
2254
2255 l_ph_num:= pg_phone_rec(p_phone_id).phone_number;
2256 l_area_code := pg_phone_rec(p_phone_id).area_code;
2257 l_ext := pg_phone_rec(p_phone_id).extension;
2258 ELSE
2259
2260 /* modified for tca uptake */
2261 SELECT decode(cont_point.contact_point_type, 'TLX',
2262 cont_point.telex_number, cont_point.phone_number),
2263 cont_point.phone_area_code,
2264 cont_point.phone_extension
2265 INTO l_ph_num, l_area_code, l_ext
2266 FROM hz_contact_points cont_point
2267 WHERE cont_point.contact_point_id = p_phone_id;
2268
2269 pg_phone_rec(p_phone_id).extension := l_ext;
2270 pg_phone_rec(p_phone_id).area_code := l_area_code;
2271 pg_phone_rec(p_phone_id).phone_number := l_ph_num;
2272
2273 END IF;
2274 ELSE
2275 return(null);
2276 END IF;
2277
2278 IF p_detail_type = 'PHONE_NUMBER' THEN
2279 return(l_ph_num);
2280 ELSIF p_detail_type = 'AREA_CODE' THEN
2281 return(l_area_code);
2282 ELSIF p_detail_type = 'EXTENSION' THEN
2283 return(l_ext);
2284 END IF;
2285
2286 EXCEPTION
2287 WHEN no_data_found THEN
2288 return(null);
2289
2290 END get_phone_details;
2291
2292 /* Bug fix 3655704 */
2293 FUNCTION is_max_rowid (p_rowid IN ROWID)
2294 RETURN VARCHAR2 IS
2295 l_max_rowid ROWID;
2296 BEGIN
2297 select max(rowid)
2298 into l_max_rowid
2299 from gl_import_references
2300 where (je_header_id, je_batch_id, je_line_num,
2301 reference_2, reference_3,
2302 reference_8, reference_9) = (select je_header_id, je_batch_id,je_line_num,
2303 reference_2, reference_3,
2304 reference_8, reference_9
2305 from gl_import_references
2306 where rowid = p_rowid);
2307 IF l_max_rowid = p_rowid THEN
2308 return 'Y';
2309 ELSE
2310 return 'N';
2311 END IF;
2312 END is_max_rowid;
2313
2314 FUNCTION get_term_details (p_term_id IN NUMBER,
2315 p_detail_type IN VARCHAR2)
2316 RETURN VARCHAR2 IS
2317 l_name VARCHAR2(15);
2318 l_calc_disc_on_lines_flag VARCHAR2(1);
2319 l_partial_discount_flag VARCHAR2(1);
2320 BEGIN
2321
2322 IF p_term_id IS NOT NULL THEN
2323
2324 IF pg_term_rec.EXISTS(p_term_id) THEN
2325
2326 l_name:= pg_term_rec(p_term_id).name;
2327 l_calc_disc_on_lines_flag := pg_term_rec(p_term_id).calc_disc_on_lines_flag;
2328 l_partial_discount_flag := pg_term_rec(p_term_id).partial_discount_flag;
2329 ELSE
2330
2331 SELECT name,calc_discount_on_lines_flag,partial_discount_flag
2332 INTO l_name, l_calc_disc_on_lines_flag, l_partial_discount_flag
2333 FROM ra_terms
2334 WHERE term_id = p_term_id;
2335
2336 pg_term_rec(p_term_id).partial_discount_flag := l_partial_discount_flag;
2340 END IF;
2337 pg_term_rec(p_term_id).calc_disc_on_lines_flag := l_calc_disc_on_lines_flag;
2338 pg_term_rec(p_term_id).name := l_name;
2339
2341 ELSE
2342 return(null);
2343 END IF;
2344
2345 IF p_detail_type = 'NAME' THEN
2346 return(l_name);
2347 ELSIF p_detail_type = 'CALC_DISCOUNT_ON_LINES_FLAG' THEN
2348 return(l_calc_disc_on_lines_flag);
2349 ELSIF p_detail_type = 'PARTIAL_DISCOUNT_FLAG' THEN
2350 return(l_partial_discount_flag);
2351 END IF;
2352
2353 EXCEPTION
2354 WHEN no_data_found THEN
2355 return(null);
2356
2357 END get_term_details;
2358
2359 FUNCTION is_agreement_date_valid(p_trx_date IN DATE,
2360 p_agreement_id IN NUMBER)
2361 RETURN VARCHAR2 IS
2362 l_name so_agreements.name%type;
2363 l_valid_date VARCHAR2(10);
2364 l_start_date_active so_agreements.start_date_active%type;
2365 l_end_date_active so_agreements.end_date_active%type;
2366 BEGIN
2367 IF p_agreement_id IS NOT NULL THEN
2368 IF pg_agreement_rec.EXISTS(p_agreement_id) THEN
2369
2370 l_name:= pg_agreement_rec(p_agreement_id).name;
2371
2372 IF pg_agreement_rec(p_agreement_id).is_valid_date IS NOT NULL
2373 THEN
2374 l_valid_date := pg_agreement_rec(p_agreement_id).is_valid_date;
2375 ELSE
2376 l_start_date_active := pg_agreement_rec(p_agreement_id).start_date_active;
2377 l_end_date_active := pg_agreement_rec(p_agreement_id).end_date_active;
2378
2379 IF NVL(p_trx_date, trunc(sysdate)) >=
2380 NVL( l_start_date_active,NVL(p_trx_date, trunc(sysdate))) AND
2381 NVL(p_trx_date, trunc(sysdate)) <=
2382 NVL( l_end_date_active,NVL(p_trx_date, trunc(sysdate))) THEN --Bug 1522486 typo fixed in l_end_date_active
2383 l_valid_date := 'YES';
2384 ELSE
2385 l_valid_date := 'NO';
2386 END IF;
2387 pg_agreement_rec(p_agreement_id).is_valid_date := l_valid_date;
2388 END IF;
2389
2390 ELSE
2391 SELECT name,start_date_active, end_date_active
2392 INTO l_name, l_start_date_active, l_end_date_active
2393 FROM so_agreements
2394 WHERE agreement_id = p_agreement_id ;
2395
2396 IF NVL(p_trx_date, trunc(sysdate)) >=
2397 NVL( l_start_date_active,NVL(p_trx_date, trunc(sysdate))) AND
2398 NVL(p_trx_date, trunc(sysdate)) <=
2399 NVL( l_end_date_active,NVL(p_trx_date, trunc(sysdate))) THEN --Bug 1522486 typo fixed in l_end_date_active
2400 l_valid_date := 'YES';
2401 ELSE
2402 l_valid_date := 'NO';
2403 END IF;
2404
2405 pg_agreement_rec(p_agreement_id).name := l_name;
2406 pg_agreement_rec(p_agreement_id).is_valid_date := l_valid_date;
2407 pg_agreement_rec(p_agreement_id).start_date_active := l_start_date_active;
2408 pg_agreement_rec(p_agreement_id).end_date_active := l_end_date_active;
2409
2410 END IF;
2411 return(l_valid_date);
2412 ELSE
2413 return('YES');
2414 END IF;
2415
2416
2417 EXCEPTION
2418 WHEN no_data_found THEN
2419 return('YES');
2420 END is_agreement_date_valid;
2421
2422 FUNCTION get_agreement_name(p_agreement_id IN NUMBER)
2423 RETURN VARCHAR2 IS
2424 l_name so_agreements.name%type;
2425 l_start_date_active so_agreements.start_date_active%type;
2426 l_end_date_active so_agreements.end_date_active%type;
2427 BEGIN
2428
2429 IF p_agreement_id IS NOT NULL THEN
2430 IF pg_agreement_rec.EXISTS(p_agreement_id) THEN
2431
2432 l_name:= pg_agreement_rec(p_agreement_id).name;
2433
2434 ELSE
2435 SELECT name,start_date_active, end_date_active
2436 INTO l_name, l_start_date_active, l_end_date_active
2437 FROM so_agreements
2438 WHERE agreement_id = p_agreement_id ;
2439
2440 pg_agreement_rec(p_agreement_id).name := l_name;
2441 pg_agreement_rec(p_agreement_id).is_valid_date := NULL;
2442 pg_agreement_rec(p_agreement_id).start_date_active := l_start_date_active;
2443 pg_agreement_rec(p_agreement_id).end_date_active := l_end_date_active;
2444
2445 END IF;
2446 return(l_name);
2447 ELSE
2448 return(null);
2449 END IF;
2450
2451 EXCEPTION
2452 WHEN no_data_found THEN
2453 return(null);
2454
2455 END get_agreement_name;
2456
2457 FUNCTION get_trx_type_details(p_trx_type_id IN NUMBER,
2458 p_detail_type IN VARCHAR2,
2459 p_org_id IN NUMBER DEFAULT NULL) /* Bug fix 5462362*/
2460 RETURN VARCHAR2 IS
2461 l_name ra_cust_trx_types.name%type;
2462 l_type ra_cust_trx_types.type%type;
2463 l_subseq_trx_type_id ra_cust_trx_types.subsequent_trx_type_id%type;
2464 l_allow_overapplication_flag ra_cust_trx_types.allow_overapplication_flag%type;
2465 l_natural_application_flag ra_cust_trx_types.natural_application_only_flag%type;
2466 l_creation_sign ra_cust_trx_types.creation_sign%type;
2467 -- Bug 4221745
2468 l_post_to_gl ra_cust_trx_types.post_to_gl%type;
2469 BEGIN
2470 IF p_trx_type_id IS NOT NULL THEN
2471
2472 IF pg_trx_type_rec.EXISTS(p_trx_type_id) THEN
2473 l_name := pg_trx_type_rec(p_trx_type_id).name;
2474 l_type := pg_trx_type_rec(p_trx_type_id).type;
2478 l_creation_sign := pg_trx_type_rec(p_trx_type_id).creation_sign;
2475 l_subseq_trx_type_id := pg_trx_type_rec(p_trx_type_id).subseq_trx_type_id;
2476 l_allow_overapplication_flag := pg_trx_type_rec(p_trx_type_id).allow_overapplication_flag;
2477 l_natural_application_flag := pg_trx_type_rec(p_trx_type_id).natural_application_only_flag;
2479 l_post_to_gl := pg_trx_type_rec(p_trx_type_id).post_to_gl;
2480 ELSE
2481 /* Bug fix 5462362*/
2482 IF p_org_id IS NOT NULL THEN
2483 BEGIN
2484 SELECT name, type, subsequent_trx_type_id ,
2485 allow_overapplication_flag ,
2486 natural_application_only_flag,
2487 creation_sign, post_to_gl
2488 INTO l_name, l_type, l_subseq_trx_type_id,
2489 l_allow_overapplication_flag ,
2490 l_natural_application_flag,
2491 l_creation_sign, l_post_to_gl
2492 FROM ra_cust_trx_types_all
2493 WHERE cust_trx_type_id = p_trx_type_id
2494 AND org_id = p_org_id;
2495 EXCEPTION WHEN OTHERS THEN
2496 return('-99999999');
2497 END;
2498 ELSE
2499 BEGIN
2500 SELECT name, type, subsequent_trx_type_id ,
2501 allow_overapplication_flag ,
2502 natural_application_only_flag,
2503 creation_sign, post_to_gl
2504 INTO l_name, l_type, l_subseq_trx_type_id,
2505 l_allow_overapplication_flag ,
2506 l_natural_application_flag,
2507 l_creation_sign, l_post_to_gl
2508 FROM ra_cust_trx_types
2509 WHERE cust_trx_type_id = p_trx_type_id;
2510 EXCEPTION
2511 WHEN NO_DATA_FOUND THEN
2512 -- bug 4221745 : when running for Operating Unit, try from _ALL table
2513 BEGIN
2514 SELECT name, type, subsequent_trx_type_id ,
2515 allow_overapplication_flag ,
2516 natural_application_only_flag,
2517 creation_sign, post_to_gl
2518 INTO l_name, l_type, l_subseq_trx_type_id,
2519 l_allow_overapplication_flag ,
2520 l_natural_application_flag,
2521 l_creation_sign, l_post_to_gl
2522 FROM ra_cust_trx_types_all
2523 WHERE cust_trx_type_id = p_trx_type_id;
2524 EXCEPTION
2525 WHEN NO_DATA_FOUND THEN
2526 return('-99999999');
2527 END;
2528 END;
2529 END IF;
2530
2531 pg_trx_type_rec(p_trx_type_id).name := l_name;
2532 pg_trx_type_rec(p_trx_type_id).type := l_type;
2533 pg_trx_type_rec(p_trx_type_id).subseq_trx_type_id := l_subseq_trx_type_id;
2534 pg_trx_type_rec(p_trx_type_id).allow_overapplication_flag := l_allow_overapplication_flag;
2535 pg_trx_type_rec(p_trx_type_id).natural_application_only_flag := l_natural_application_flag;
2536 pg_trx_type_rec(p_trx_type_id).creation_sign := l_creation_sign;
2537 pg_trx_type_rec(p_trx_type_id).post_to_gl := l_post_to_gl;
2538 END IF;
2539
2540 IF p_detail_type = 'NAME' THEN
2541 return(l_name);
2542 ELSIF p_detail_type = 'TYPE' THEN
2543 return(l_type);
2544 ELSIF p_detail_type = 'SUBSEQ_TRX_TYPE' THEN
2545 return(to_char(l_subseq_trx_type_id));
2546 ELSIF p_detail_type = 'ALLOW_OVERAPPLICATION_FLAG' THEN
2547 return(l_allow_overapplication_flag);
2548 ELSIF p_detail_type = 'NATURAL_APPLICATION_ONLY_FLAG' THEN
2549 return(l_natural_application_flag);
2550 ELSIF p_detail_type = 'CREATION_SIGN' THEN
2551 return(l_creation_sign);
2552 ELSIF p_detail_type = 'POST' THEN
2553 return(l_post_to_gl);
2554 END IF;
2555
2556 ELSE
2557 return(null);
2558 END IF;
2559 EXCEPTION
2560 WHEN others THEN
2561 return('-99999999');
2562 END;
2563
2564 FUNCTION check_iclaim_installed
2565 RETURN VARCHAR2
2566 IS
2567 BEGIN
2568 IF arp_global.tm_installed_flag = 'Y'
2569 THEN
2570 RETURN 'T';
2571 ELSE
2572 RETURN 'F';
2573 END IF;
2574 END;
2575
2576 FUNCTION get_orig_gl_date(p_customer_trx_id IN NUMBER)
2577 RETURN DATE IS
2578 l_orig_gl_date DATE;
2579 BEGIN
2580
2581 SELECT gl_date
2582 INTO l_orig_gl_date
2583 FROM ar_transaction_history h
2584 WHERE h.customer_trx_id = p_customer_trx_id
2585 AND h.event in ('COMPLETED','ACCEPTED')
2586 AND h.transaction_history_id =
2587 (SELECT max(transaction_history_id)
2588 FROM ar_transaction_history h2
2589 WHERE h2.customer_trx_id = p_customer_trx_id
2590 AND h2.event IN ('COMPLETED','ACCEPTED'));
2591
2592 return l_orig_gl_date;
2593
2594 EXCEPTION
2595 WHEN NO_DATA_FOUND THEN
2596
2597 SELECT gl_date
2598 INTO l_orig_gl_date
2599 FROM ar_transaction_history h
2600 WHERE h.customer_trx_id = p_customer_trx_id
2601 AND h.current_record_flag = 'Y';
2602
2603 return l_orig_gl_date;
2604
2605 END;
2606
2607 FUNCTION get_sum_of_trx_lines(p_customer_trx_id IN NUMBER,
2608 p_line_type IN VARCHAR2)
2609 RETURN NUMBER IS
2610
2611 l_total NUMBER;
2612
2613 BEGIN
2614
2615 SELECT NVL(SUM(extended_amount),0)
2616 INTO l_total
2617 FROM ra_customer_trx_lines
2618 WHERE line_type = p_line_type
2619 AND customer_trx_id = p_customer_trx_id;
2620
2621 return l_total;
2622
2623 EXCEPTION
2627
2624 WHEN NO_DATA_FOUND THEN
2625
2626 return 0;
2628 END;
2629
2630
2631 /*===========================================================================+
2632 | FUNCTION |
2633 | get_balance_due_as_of_date |
2634 | |
2635 | DESCRIPTION |
2636 | Calculates the amount due in a transaction as on date |
2637 |
2638 | SCOPE - PUBLIC |
2639 | |
2640 | ARGUMENTS : IN: |
2641 | p_applied_payment_schedule_id |
2642 | p_as_of_date |
2643 | p_class |
2644 | OUT: |
2645 | none |
2646 | |
2647 | RETURNS : The amount due in a transaction as on date |
2648 | |
2649 | |
2650 | NOTES : Currently being used by Q_MAIN query in ARXPDI.rdf |
2651 | |
2652 | MODIFICATION HISTORY |
2653 | |
2654 | 03-JAN-2002 Smita Parasa Created Bug Fix : 2064286 |
2655 | 10-MAY-2004 SAPN Sarma Bug 3221397. Discounts also included |
2656 | while calculating the balance_due of the|
2657 | customer. Also, added the code not to |
2658 | pick up the APPROVED receipt applications|
2659 | while calculating the balance. |
2660 | 01-JUL-04 Bhushan Dhotkar bug3740861: Added paramter p_class to |
2661 | get_balance_due_as_of_date to prevent |
2662 | unnecessary execution of sql to CM amnt |
2663 +===========================================================================*/
2664
2665
2666 FUNCTION get_balance_due_as_of_date(
2667 p_applied_payment_schedule_id in number,
2668 p_as_of_date in date,
2669 p_class in varchar2 )
2670 RETURN number IS
2671
2672 p_amount_applied number;
2673 /*Bug 2453245 */
2674 p_adj_amount_applied number;
2675 p_actual_amount number;
2676 p_amt_due_original number;
2677 /* Bug 2610716 */
2678 p_cm_amount_applied number;
2679
2680 BEGIN
2681 SELECT nvl(sum(nvl(amount_applied,0) + nvl(earned_discount_taken,0) + nvl(unearned_discount_taken,0)), 0)
2682 INTO p_amount_applied
2683 FROM ar_receivable_applications
2684 WHERE applied_payment_schedule_id = p_applied_payment_schedule_id
2685 AND status = 'APP'
2686 AND nvl(confirmed_flag,'Y') = 'Y'
2687 AND apply_date <= p_as_of_date;
2688
2689 /* Added the query to take care of On-Account CM applications Bug 2610716*/
2690 IF p_class = 'CM' THEN
2691 SELECT nvl(sum(amount_applied),0)
2692 INTO p_cm_amount_applied
2693 FROM ar_receivable_applications
2694 WHERE payment_schedule_id = p_applied_payment_schedule_id
2695 AND apply_date <= p_as_of_date;
2696 END IF;
2697
2698 /* Bug 2453245 Added the query to retrieve the Adjustment
2699 Amount applied to the Invoice */
2700 SELECT nvl(sum(amount),0)
2701 INTO p_adj_amount_applied
2702 FROM ar_adjustments
2703 WHERE payment_schedule_id = p_applied_payment_schedule_id
2704 AND status = 'A'
2705 AND apply_date <= p_as_of_date;
2706
2707 SELECT amount_due_original
2708 INTO p_amt_due_original
2709 FROM ar_payment_schedules
2710 WHERE payment_schedule_id = p_applied_payment_schedule_id;
2711
2712 /*Bug 2453245 Added p_adj_amount_applied so that
2713 Adjustment amount is also taken into account while
2714 computing the Balance */
2715 /* bug4085823: Added nvl for p_cm_amount_applied */
2716 p_actual_amount := p_amt_due_original
2717 + p_adj_amount_applied
2718 - p_amount_applied + nvl(p_cm_amount_applied,0) ;
2719 RETURN(p_actual_amount);
2720 EXCEPTION
2724 WHEN OTHERS THEN
2721 /* bug3544286 added NO_DATA_FOUND */
2722 WHEN NO_DATA_FOUND THEN
2723 RETURN(null);
2725 NULL;
2726 END get_balance_due_as_of_date;
2727
2728
2729 FUNCTION bucket_function(p_buck_line_typ varchar2,
2730 p_amt_in_disp NUMBER,
2731 p_amt_adj_pen NUMBER,
2732 p_days_from NUMBER,
2733 p_days_to NUMBER,
2734 p_due_date DATE,
2735 p_bucket_category VARCHAR2,
2736 p_as_of DATE)
2737
2738 RETURN number IS
2739
2740 bucket_amount NUMBER;
2741
2742 BEGIN
2743
2744 select decode(p_buck_line_typ,
2745 'DISPUTE_ONLY',decode(nvl(p_amt_in_disp,0),0,0,1),
2746 'PENDADJ_ONLY',decode(nvl(p_amt_adj_pen,0),0,0,1),
2747 'DISPUTE_PENDADJ',decode(nvl(p_amt_in_disp,0),
2748 0,decode(nvl(p_amt_adj_pen,0),0,0,1),
2749 1),
2750 decode( greatest(p_days_from,
2751 ceil(p_as_of-p_due_date)),
2752 least(p_days_to,
2753 ceil(p_as_of-p_due_date)),1,
2754 0)
2755 * decode(nvl(p_amt_in_disp,0), 0, 1,
2756 decode(p_bucket_category,
2757 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2758 1))
2759 * decode(nvl(p_amt_adj_pen,0), 0, 1,
2760 decode(p_bucket_category,
2761 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0,
2762 1)))
2763 into bucket_amount
2764 from dual;
2765
2766 return(bucket_amount);
2767
2768 END;
2769
2770 /* 2362943:given the statement or dunning site_use_id return bill_to id */
2771 /*
2772 2357301 : After the code changes for bug2335304
2773 It is now possible to have an hz_customer_profiles cp row
2774 where site_use_id is for a site with purpose in (STMTS, DUN)
2775
2776 In such cases, this function will return the following :
2777
2778 if cp.site_use_id = p_site_use_id exists
2779 return p_site_use_id
2780 if cp.site_use_id = p_site_use_id DOES NOT exist
2781 - return the site_use_id for this same cust_acct_site_id that does
2782 have a row in hz_customer_profiles
2783
2784 So technically, this no longer returns the site_use_id with the BILL_TO purpose
2785 but rather it returns the site_use_id that exists in hz_customer_profiles
2786 the function should really be get_site_with_profile
2787
2788 Bug 4128837 : The whole premise of the new get_bill_id was written under
2789 the assumption that a distinct cust_acct_site_id, can only have one row
2790 in hz_customer_profiles. This was based on the current Customer Form behavior
2791
2792 However, it has been noted that old data actually shows that a cust_acct_site_id
2793 with 2 or more business purposes can have multiple rows in the profiles table
2794 and this was causing ORA-1422 error. The fix on the exception block ensures that
2795 only one row will ever be returned by the select.
2796
2797 */
2798
2799 FUNCTION get_bill_id(p_site_use_id IN NUMBER)
2800 RETURN NUMBER IS
2801
2802 l_site_with_profile NUMBER;
2803
2804 BEGIN
2805
2806 -- check if there is a row in customer profiles using this site_use_id
2807 -- if found, regardless of it's site_use_code, return that site_use_id
2808
2809 SELECT distinct site_use_id
2810 INTO l_site_with_profile
2811 FROM hz_customer_profiles
2812 WHERE site_use_id = p_site_use_id;
2813
2814 return l_site_with_profile;
2815
2816 EXCEPTION
2817 WHEN NO_DATA_FOUND THEN
2818
2819 BEGIN
2820
2821 -- Find site_use_id from hz_customer_profiles,
2822 -- that uses the same cust_acct_site_id as p_site_use_id
2823
2824 select site_use_id
2825 into l_site_with_profile
2826 from hz_customer_profiles
2827 where site_use_id in ( select site_use_id
2828 from hz_cust_site_uses
2829 where cust_acct_site_id =
2830 ( SELECT cust_acct_site_id
2831 FROM hz_cust_site_uses
2832 WHERE site_use_id = p_site_use_id)
2833 and status = 'A'
2834 and site_use_code in ('BILL_TO','DUN','STMTS'));
2835
2836
2837 return l_site_with_profile;
2838 EXCEPTION
2839 WHEN NO_DATA_FOUND THEN
2840 -- Bug 3763432 / 3722489 : define an exception block to return null when
2841 -- there are no site level profiles
2842 return null;
2843 WHEN TOO_MANY_ROWS THEN
2844 -- Bug 4128837 : this is the case for historical "bad" data,
2845 -- wherein one cust_acct_site_id can have multiple rows in hz_customer_profiles
2846 -- In this case, use the BILL_TO site (since this is the profile that
2847 -- users can access in the customer standard form)
2848
2849 BEGIN
2850
2851 select site_use_id
2852 into l_site_with_profile
2853 from hz_customer_profiles
2854 where site_use_id in ( select site_use_id
2855 from hz_cust_site_uses
2856 where cust_acct_site_id =
2857 ( SELECT cust_acct_site_id
2858 FROM hz_cust_site_uses
2859 WHERE site_use_id = p_site_use_id)
2860 and status = 'A'
2861 and site_use_code = 'BILL_TO');
2862 return l_site_with_profile; /*4913217 */
2863 EXCEPTION
2864 WHEN NO_DATA_FOUND THEN
2865 return null;
2866 END;
2867
2868 END;
2869
2870 END;
2871
2872 /* 2362943 : return statement cycle tied to a site_use_id */
2873 FUNCTION get_stmt_cycle(p_site_use_id IN NUMBER)
2874
2875 RETURN NUMBER IS
2876
2877 l_cycle_id NUMBER;
2878
2879 BEGIN
2880 SELECT statement_cycle_id
2881 INTO l_cycle_id
2882 FROM hz_customer_profiles
2883 WHERE site_use_id = arpt_sql_func_util.get_bill_id(p_site_use_id);
2884
2885 RETURN l_cycle_id;
2886 END;
2887
2888 /* 2362943 : return send_statements value of a site_use_id */
2889 FUNCTION get_send_stmt(p_site_use_id IN NUMBER)
2890 RETURN VARCHAR2 IS
2891
2892 l_send VARCHAR2(1);
2893
2894 BEGIN
2895 SELECT nvl(send_statements ,'N')
2896 INTO l_send
2897 FROM hz_customer_profiles
2898 WHERE site_use_id = arpt_sql_func_util.get_bill_id(p_site_use_id);
2899
2900 RETURN l_send;
2901 END;
2902
2903 /* 2362943 : return credit_balance_statements given a site_use_id */
2904 FUNCTION get_cred_bal(p_site_use_id IN NUMBER)
2905 RETURN VARCHAR2 IS
2906
2907 l_cred_bal VARCHAR2(1);
2908
2909 BEGIN
2910 SELECT nvl(credit_balance_statements ,'N')
2911 INTO l_cred_bal
2912 FROM hz_customer_profiles
2913 WHERE site_use_id = arpt_sql_func_util.get_bill_id(p_site_use_id);
2914
2915 RETURN l_cred_bal;
2916 END;
2917
2918
2919
2920 /*===========================================================================+
2921 | FUNCTION |
2922 | get_claim_amount |
2923 | |
2924 | DESCRIPTION |
2925 | Retrieves the net amount remaining from Trade management for a given |
2926 | claim |
2927 | |
2928 | SCOPE - PUBLIC |
2932 | |
2929 | |
2930 | ARGUMENTS : IN: p_claim_id |
2931 | OUT: |
2933 | RETURNS : The net amount due in claim currency |
2934 | |
2935 | NOTES : Used by ARXRWAPP.pld arp_app_folder_item.application_ref_num |
2936 | |
2937 | |
2938 | MODIFICATION HISTORY |
2939 | 08-MAY-2002 Jon Beckett Created (Bug 2353144) |
2940 | 30-MAY-2002 Jon Beckett Bug 2381718 - get_amount_remaining |
2941 | procedure moved to ams_claim_install |
2942 | package from ams_claim_grp. |
2943 +===========================================================================*/
2944
2945 FUNCTION get_claim_amount(p_claim_id IN NUMBER)
2946 RETURN NUMBER
2947 IS
2948 l_amount_remaining NUMBER;
2949 l_acctd_amount_remaining NUMBER;
2950 l_currency_code VARCHAR2(15);
2951 l_return_status VARCHAR2(1);
2952
2953 BEGIN
2954 arp_standard.debug('ARPT_SQL_FUNC_UTIL.get_claim_amount()+');
2955 OZF_Claim_Install.get_amount_remaining (
2956 p_claim_id => p_claim_id,
2957 x_amount_remaining => l_amount_remaining,
2958 x_acctd_amount_remaining => l_acctd_amount_remaining,
2959 x_currency_code => l_currency_code,
2960 x_return_status => l_return_status
2961 );
2962 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2963 THEN
2964 RETURN FND_API.G_MISS_NUM;
2965 ELSE
2966 RETURN l_amount_remaining;
2967 END IF;
2968
2969 arp_standard.debug('ARPT_SQL_FUNC_UTIL.get_claim_amount()-');
2970 EXCEPTION
2971 WHEN OTHERS THEN
2972 arp_standard.debug('Unexpected error '||sqlerrm||
2973 ' occurred in ARPT_SQL_FUNC_UTIL.get_claim_amount');
2974 RAISE;
2975 END get_claim_amount;
2976
2977 /* Bug3820605 */
2978 FUNCTION get_org_trx_type_details(p_trx_type_id IN NUMBER, p_org_id IN number)
2979 RETURN VARCHAR2 IS
2980
2981 p_concat_segments varchar2(100);
2982 tab_indx BINARY_INTEGER := 0;
2983 found BOOLEAN ;
2984 l_trx_name ra_cust_trx_types.name%type;
2985 l_hash_value NUMBER;
2986 BEGIN
2987 /*----------------------------------------------------------------+
2988 | Search the cache for the concantenated segments. |
2989 | Return the trx name if it is in the cache. |
2990 | |
2991 | If not found in cache, search the linear table (where |
2992 | trx name's will go if collision on the hash table |
2993 | occurs). |
2994 | |
2995 | If not found above then get it from databse |
2996 +----------------------------------------------------------------*/
2997
2998 -- 4140375 : need to handle case where p_trx_type_id is null
2999 IF p_trx_type_id is NULL THEN
3000 return('-99999999');
3001 ELSE
3002
3003 /* Bug4400069 : Removed ' org_id IF ' condition and added NVL for org_id below */
3004
3005 p_concat_segments := p_trx_type_id||'@*?'||p_org_id;
3006
3007 l_hash_value := DBMS_UTILITY.get_hash_value(p_concat_segments,
3008 1000,
3009 25000);
3010 found := FALSE;
3011 IF pg_get_hash_name_cache.exists(l_hash_value) THEN
3012 IF pg_get_hash_name_cache(l_hash_value) = p_concat_segments THEN
3013 l_trx_name := pg_get_hash_id_cache(l_hash_value);
3014 found := TRUE;
3015
3016 ELSE --- collision has occurred
3017 tab_indx := 1; -- start at top of linear table and search for match
3018
3019 WHILE ((tab_indx <= tab_size) AND (not FOUND)) LOOP
3020 IF pg_get_line_name_cache(tab_indx) = p_concat_segments THEN
3021 l_trx_name := pg_get_line_id_cache(tab_indx);
3022 found := TRUE;
3023 ELSE
3024 tab_indx := tab_indx + 1;
3025 END IF;
3026 END LOOP;
3027 END IF;
3028 END IF;
3029 IF found THEN
3030 RETURN(l_trx_name);
3031 ELSE
3032
3033 SELECT name
3034 INTO l_trx_name
3035 FROM ra_cust_trx_types_all
3036 WHERE cust_trx_type_id = p_trx_type_id
3037 AND NVL(org_id,-99) = NVL(p_org_id,-99);
3038
3039
3040 IF pg_get_hash_name_cache.exists(l_hash_value) then
3041 tab_size := tab_size + 1;
3042 pg_get_line_id_cache(tab_size) := l_trx_name;
3043 pg_get_line_name_cache(tab_size) := p_concat_segments;
3044 ELSE
3045 tab_size := tab_size + 1;
3046 pg_get_hash_id_cache(l_hash_value) := l_trx_name;
3047 pg_get_hash_name_cache(l_hash_value) := p_concat_segments;
3048 pg_get_line_id_cache(tab_size) := l_trx_name;
3049 pg_get_line_name_cache(tab_size) := p_concat_segments;
3050 END IF;
3051 RETURN(l_trx_name);
3052 END IF;
3053 END IF;
3054
3055 EXCEPTION
3056 WHEN no_data_found THEN
3057 return('-99999999');
3058 END get_org_trx_type_details;
3059
3060
3061 -- Bug 4221745
3062 FUNCTION get_rec_trx_type(p_rec_trx_id IN NUMBER,
3063 p_detail_type IN VARCHAR2 DEFAULT 'TYPE')
3064 RETURN VARCHAR2 IS
3065
3066 l_type ar_receivables_trx.type%TYPE;
3067 l_name ar_receivables_trx.name%TYPE;
3068
3069 BEGIN
3070
3071 select type, name
3072 into l_type, l_name
3073 from ar_receivables_trx
3074 where receivables_trx_id = p_rec_trx_id;
3075
3076 if p_detail_type = 'TYPE' THEN
3077 return l_type;
3078 elsif p_detail_type = 'NAME' THEN
3079 return l_name;
3080 else
3081 return('-99999999');
3082 end if;
3083
3084 EXCEPTION
3085 WHEN NO_DATA_FOUND THEN
3086 BEGIN
3087 select type, name
3088 into l_type, l_name
3089 from ar_receivables_trx_all
3090 where receivables_trx_id = p_rec_trx_id;
3091
3092 if p_detail_type = 'TYPE' THEN
3093 return l_type;
3094 elsif p_detail_type = 'NAME' THEN
3095 return l_name;
3096 else
3097 return('-99999999');
3098 end if;
3099
3100 EXCEPTION
3101 WHEN OTHERS THEN
3102 return '-99999999';
3103 END;
3104 WHEN OTHERS THEN
3105 return '-99999999';
3106 END;
3107
3108 FUNCTION check_BOE_paymeth (p_receipt_method_id IN NUMBER)
3109 RETURN VARCHAR2 IS
3110
3111 boe_flag AR_RECEIPT_CLASSES.BILL_OF_EXCHANGE_FLAG%TYPE;
3112
3113 BEGIN
3114 -- Added the check only for Receipt Classes with the creation method as Automatic
3115 select decode(rc.creation_method_code,'AUTOMATIC',nvl(rc.bill_of_exchange_flag,'N'),'N')
3116 into boe_flag
3117 from ar_receipt_classes rc,
3118 ar_receipt_methods rm
3119 where rm.receipt_method_id = p_receipt_method_id
3120 and rm.receipt_class_id = rc.receipt_class_id;
3121
3122 RETURN boe_flag;
3123 EXCEPTION
3124 WHEN OTHERS THEN
3125 RETURN ('N');
3126 END;
3127
3128 /* Bug 4761373 : Transferred from ARTAUTLB.pls
3129 New function Get_currency_code has been added for the bug 3043128 */
3130
3131 FUNCTION GET_CURRENCY_CODE(p_application_type in varchar2,
3132 p_status in varchar2,
3133 p_ard_source_type in varchar2,
3134 p_cr_currency_code in varchar2,
3135 p_inv_currency_code in varchar2) return varchar2 is
3136 l_curr varchar2(5);
3137 Begin
3138
3139 Select decode(p_application_type, 'CASH',
3140 decode(p_status,'APP',
3141 decode(substr(p_ard_source_type,1,5),
3142 'EXCH_',decode(p_cr_currency_code,
3143 arp_global.functional_currency,p_inv_currency_code,
3144 p_cr_currency_code),
3145 'CURR_',decode(p_cr_currency_code,
3146 arp_global.functional_currency,p_inv_currency_Code,
3147 p_cr_currency_code),
3148 p_inv_currency_code),
3149 p_cr_currency_code),
3150 'CM',p_inv_currency_code)
3151 into l_curr from dual;
3152 return(l_curr);
3153 END GET_CURRENCY_CODE;
3154
3155 END ARPT_SQL_FUNC_UTIL;