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