1 PACKAGE BODY AR_INVOICE_SQL_FUNC_PUB AS
2 /*$Header: ARTPSQBS.pls 120.2 2005/06/04 04:25:32 mraymond ship $*/
3 pg_reference_column VARCHAR2(240);
4
5 /*===========================================================================+
6 | FUNCTION get_description |
7 | |
8 | DESCRIPTION |
9 | This function is used for multi-lingual support installs. |
10 | SCOPE - PUBLIC |
11 | |
12 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
13 | |
14 | ARGUMENTS : IN: p_customer_trx_line_id |
18 | |
15 | |
16 | OUT: |
17 | RETURNS : |
19 | NOTES : Called by lines view |
20 | Function implemented is part of populate_mls_lexicals, which |
21 | is split into 2 functions get_description and |
22 | get_alt_description due to 2 return values. |
23 | |
24 | MODIFICATION HISTORY |
25 | 01-MAY-97 Ashim K Dey Created |
26 | 30-JUL-98 Victoria Smith This is now just a dummy/cover |
27 | function that always returns null |
28 | since single language installations |
29 | have no translated descriptions |
30 | 05-JAN-99 Victoria Smith modified to pick up translated_ |
31 | description |
32 | 08-APR-04 Naruhiko Yanagita expanded the length of |
33 | l_description from 240 to 1000. |
34 +===========================================================================*/
35
36 FUNCTION get_description (p_customer_trx_line_id IN NUMBER)
37 RETURN VARCHAR2 IS
38
39 l_description varchar2(1000);
40
41 BEGIN
42
43 select translated_description
44 into l_description
45 from ra_customer_trx_lines
46 where customer_trx_line_id = p_customer_trx_line_id;
47
48 return(l_description);
49
50 END get_description;
51
52 /*===========================================================================+
53 | FUNCTION get_inv_tax_code_name
54 | |
55 | DESCRIPTION |
56 | This function is used for getting inv_tax_code_name
57 | |
58 | SCOPE - PUBLIC |
59 | |
60 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
61 | |
62 | ARGUMENTS : IN: p_bill_to_site_use_id
63 | p_bill_to_customer_id
64 | p_tax_printing_option
65 | p_printed_tax_name
66 | p_tax_code
67 | OUT: |
68 | RETURNS : |
69 | |
70 | NOTES This function is used in view ar_invoice_tax_summary_v |
71 | |
72 | MODIFICATION HISTORY |
73 | 02-MAY-97 Ashim K Dey Created |
74 +===========================================================================*/
75
76 FUNCTION get_inv_tax_code_name(
77 p_bill_to_site_use_id IN NUMBER,
78 p_bill_to_customer_id IN NUMBER,
79 p_tax_printing_option IN VARCHAR2,
80 p_printed_tax_name IN VARCHAR2,
81 p_tax_code IN VARCHAR2)
82 RETURN VARCHAR2 IS
83
84 l_tax_printing_option VARCHAR2(30);
85
86 BEGIN
87 IF ( p_bill_to_site_use_id IS NULL
88 AND p_bill_to_customer_id IS NULL
89 AND p_tax_printing_option IS NULL)
90
91 THEN
92
93 RETURN(null) ;
94
95 END IF ;
96
97 SELECT nvl( cp_site.tax_printing_option,
98 nvl(cp_cust.tax_printing_option,p_tax_printing_option) )
99 INTO l_tax_printing_option
100 FROM hz_customer_profiles cp_site,
101 hz_customer_profiles cp_cust,
102 hz_cust_site_uses site
103 WHERE cp_site.site_use_id(+) = site.site_use_id
104 AND site.site_use_id = p_bill_to_site_use_id
105 AND cp_cust.cust_account_id = p_bill_to_customer_id
106 AND cp_cust.site_use_id is null;
107
108 IF ( l_tax_printing_option IS NULL
109 OR l_tax_printing_option = 'RECAP_BY_NAME')
110
111 THEN
112
113 RETURN(p_printed_tax_name) ;
114
115 ELSE
116
117 RETURN(p_tax_code) ;
118
119 END IF ;
120
121 EXCEPTION WHEN NO_DATA_FOUND THEN
122
123 l_tax_printing_option := p_tax_printing_option;
124
125 IF ( l_tax_printing_option IS NULL
126 OR l_tax_printing_option = 'RECAP_BY_NAME')
127
128 THEN
129
130 RETURN(p_printed_tax_name) ;
131
132 ELSE
133
134 RETURN(p_tax_code) ;
135 END IF ;
136
137 END get_inv_tax_code_name;
138
139 /*=============================================================================
140 || PRIVATE FUNCTION get_com_total_activity
141 ||
142 || DESCRIPTION Returns the commitment total activity
143 ||
144 || ARGUMENTS p_customer_trx_id
145 || p_commit_parent_type
146 p_init_cust_trx_id
147 ||
148 || RETURN
149 ||
150 || NOTE
151 || This function simulates the report local function
152 || commitments in RAXINV.rdf
153 ||
154 || MODIFICATION HISTORY
155 || 22-MAY-97 Ashim K Dey Created
156 =============================================================================*/
157
158 FUNCTION get_com_total_activity(
159 p_customer_trx_id IN NUMBER,
163
160 p_trx_type IN VARCHAR2,
161 p_init_cust_trx_id IN NUMBER)
162 RETURN NUMBER IS
164 commit_adjustments number := 0;
165 commit_total_activity number := 0;
166 commit_this_invoice number := 0;
167
168 BEGIN
169
170 IF p_trx_type = 'DEP'
171
172 THEN
173 /*-------------------------------------------+
174 | If the commitment type is for a DEPOSIT |
175 +-------------------------------------------*/
176
177 SELECT NVL(SUM(adj.amount),0)
178 INTO commit_adjustments
179 FROM ra_customer_trx trx,
180 ra_cust_trx_types type,
181 ar_adjustments adj
182 WHERE trx.cust_trx_type_id = type.cust_trx_type_id
183 AND type.type in ('INV', 'CM')
184 AND trx.complete_flag ='Y'
185 AND trx.initial_customer_trx_id = p_init_cust_trx_id
186 AND adj.customer_trx_id = DECODE(type.type,
187 'INV', trx.customer_trx_id,
188 'CM', trx.previous_customer_trx_id)
189 AND NVL(adj.subsequent_trx_id,-111) = DECODE(type.type,
190 'INV',-111,
191 'CM',trx.customer_trx_id)
192 AND adj.adjustment_type = 'C';
193
194 SELECT NVL(SUM(line.extended_amount),0)
195 INTO commit_total_activity
196 FROM ra_customer_trx trx,
197 ra_cust_trx_types type,
198 ra_customer_trx_lines line
199 WHERE trx.cust_trx_type_id = type.cust_trx_type_id
200 AND trx.customer_trx_id = line.customer_trx_id
201 AND type.type = 'CM'
202 AND trx.complete_flag = 'Y'
203 AND trx.previous_customer_trx_id = p_init_cust_trx_id;
204
205 ELSIF p_trx_type = 'GUAR'
206
207 THEN
208
209 /*----------------------------------------------------------+
210 | If the commitment type is for a GUARANTEE |
211 +----------------------------------------------------------*/
212
213 SELECT -1 * (NVL(SUM(amount_line_items_original), 0) -
214 NVL(SUM(amount_line_items_remaining), 0))
215 INTO commit_total_activity
216 FROM ar_payment_schedules
217 WHERE customer_trx_id = p_init_cust_trx_id;
218
219 ELSE commit_total_activity := 0;
220
221 END IF;
222
223 /*----------------------------------------------------------+
224 | commit_total_activity is including commit_this amount |
225 +----------------------------------------------------------*/
226
227 commit_total_activity := commit_total_activity+commit_adjustments;
228
229 /*----------------------------------------------------------+
230 | Now get commit_this_invoice and substract from total |
231 | activity |
232 +----------------------------------------------------------*/
233
234 SELECT SUM(Amount)
235 INTO commit_this_invoice
236 FROM ar_adjustments
237 WHERE adjustment_type = 'C'
238 AND ( ((customer_trx_id = p_customer_trx_id )
239 AND (subsequent_trx_id is null))
240 OR subsequent_trx_id = p_customer_trx_id);
241
242 commit_total_activity := commit_total_activity -
243 nvl(commit_this_invoice,0) ;
244
245 return( commit_total_activity ) ;
246
247 END get_com_total_activity ;
248
249 /*=============================================================================
250 || PRIVATE FUNCTION get_com_amt_uninvoiced
251 ||
252 || DESCRIPTION Returns the commitment uninvoiced amount
253 ||
254 || ARGUMENTS p_init_cust_trx_id
255 ||
256 || RETURN returns 0 if no data found; otherwise total uninvoiced amount
257 || for the transaction.
258 ||
259 || NOTE
260 || This function simulates the cursor in report local function
261 || "commitments" in RAXINV.rdf.
262 || Here It is not checked whether Order entry is installed, because
263 || the table so_lines will always be present.
264 ||
265 || MODIFICATION HISTORY
266 || 22-MAY-97 Ashim K Dey Created
267 =============================================================================*/
268
269 FUNCTION get_com_amt_uninvoiced(
270 p_init_cust_trx_id IN NUMBER)
271 RETURN NUMBER IS
272
273 BEGIN
274
275 RETURN(NVL(OE_Payments_Util.Get_Uninvoiced_Commitment_Bal(p_init_cust_trx_id), 0));
276
277 END get_com_amt_uninvoiced ;
278
279 /*=============================================================================
280 || PRIVATE FUNCTION get_commit_this_invoice
281 ||
282 || DESCRIPTION This function returns amount for this invoice
283 ||
284 || ARGUMENTS p_customer_trx_id
285 ||
286 || NOTE
287 || This function simulates the query Q_Commitment_Adjustment in RAXINV.rdf.
288 ||
289 || MODIFICATION HISTORY
290 || 23-MAY-97 Ashim K Dey Created
291 =============================================================================*/
292
293 FUNCTION get_commit_this_invoice(p_customer_trx_id IN NUMBER)
294 RETURN number IS
295
296 commit_this_invoice number := 0 ;
297
298 BEGIN
299
300 SELECT SUM(Amount)
301 INTO commit_this_invoice
302 FROM ar_adjustments
303 WHERE adjustment_type = 'C'
304 AND ( ((customer_trx_id = p_customer_trx_id )
305 AND (subsequent_trx_id is null))
306 OR subsequent_trx_id = p_customer_trx_id) ;
307
311
308 return( commit_this_invoice ) ;
309
310 END get_commit_this_invoice ;
312 /*=============================================================================
313 || PRIVATE FUNCTION get_com_balance
314 ||
315 || DESCRIPTION Returns the commitment balance
316 ||
317 || ARGUMENTS p_customer_trx_id
318 || p_commit_parent_type
319 || p_init_cust_trx_id
320 ||
321 || NOTE
322 || This function simulates the report local function
323 || commitments in RAXINV.rdf
324 ||
325 || MODIFICATION HISTORY
326 || 22-MAY-97 Ashim K Dey Created
327 =============================================================================*/
328
329 FUNCTION get_com_balance(
330 p_original_amount IN NUMBER,
331 p_trx_type IN VARCHAR2,
332 p_init_cust_trx_id IN NUMBER)
333 RETURN NUMBER IS
334
335 commit_adjustments number := 0;
336 commit_total_activity number := 0;
337 commit_this_invoice number := 0;
338 commit_balance number := 0;
339
340 BEGIN
341
342 IF p_trx_type = 'DEP'
343
344 THEN
345 /*-------------------------------------------+
346 | If the commitment type is for a DEPOSIT |
347 +-------------------------------------------*/
348
349 SELECT NVL(SUM(adj.amount),0)
350 INTO commit_adjustments
351 FROM ra_customer_trx trx,
352 ra_cust_trx_types type,
353 ar_adjustments adj
354 WHERE trx.cust_trx_type_id = type.cust_trx_type_id
355 AND type.type in ('INV', 'CM')
356 AND trx.complete_flag ='Y'
357 AND trx.initial_customer_trx_id = p_init_cust_trx_id
358 AND adj.customer_trx_id = DECODE(type.type,
359 'INV', trx.customer_trx_id,
360 'CM', trx.previous_customer_trx_id)
361 AND NVL(adj.subsequent_trx_id,-111) = DECODE(type.type,
362 'INV',-111,
363 'CM',trx.customer_trx_id)
364 AND adj.adjustment_type = 'C';
365
366 SELECT NVL(SUM(line.extended_amount),0)
367 INTO commit_total_activity
368 FROM ra_customer_trx trx,
369 ra_cust_trx_types type,
370 ra_customer_trx_lines line
371 WHERE trx.cust_trx_type_id = type.cust_trx_type_id
372 AND trx.customer_trx_id = line.customer_trx_id
373 AND type.type = 'CM'
374 AND trx.complete_flag = 'Y'
375 AND trx.previous_customer_trx_id = p_init_cust_trx_id;
376
377 ELSIF p_trx_type = 'GUAR'
378
379 THEN
380
381 /*----------------------------------------------------------+
382 | If the commitment type is for a GUARANTEE |
383 +----------------------------------------------------------*/
384
385 SELECT -1 * (NVL(SUM(amount_line_items_original), 0) -
386 NVL(SUM(amount_line_items_remaining), 0))
387 INTO commit_total_activity
388 FROM ar_payment_schedules
389 WHERE customer_trx_id = p_init_cust_trx_id;
390
391 ELSE commit_total_activity := 0;
392
393 END IF;
394
395 /*----------------------------------------------------------+
396 | commit_total_activity is including commit_this amount |
397 +----------------------------------------------------------*/
398
399 commit_total_activity := commit_total_activity+commit_adjustments;
400
401 /*----------------------------------------------------------+
402 | Now add this negetive total_activity with original_amount|
403 | to get the commit_balance |
404 +----------------------------------------------------------*/
405
406 commit_balance := commit_total_activity + p_original_amount ;
407
408 return( commit_balance ) ;
409
410 END get_com_balance ;
411
412 /*=============================================================================
413 || PRIVATE PROCEDURE update_customer_trx
414 ||
415 || DESCRIPTION This procedure updates the ra_customer_trx table
416 || and sets the printing information.
417 ||
418 || ARGUMENTS
419 || IN: p_choice
420 || p_customer_trx_id
421 || p_trx_type
422 || p_term_count
423 || p_term_sequence_number
424 || p_printing_count
425 || p_printing_original_date
426 ||
427 || OUT:
428 ||
429 || FUNCTION CALL
430 ||
431 || RETURN
432 ||
433 || NOTE This is a update procedure. So pragma restriction should not be
434 || imposed in its declaration.
435 ||
436 || MODIFICATION HISTORY
437 || 29-MAY-97 Ashim K Dey Created
438 =============================================================================*/
439 PROCEDURE update_customer_trx (
440 p_choice IN VARCHAR2,
441 p_customer_trx_id IN NUMBER,
442 p_trx_type IN VARCHAR2,
443 p_term_count IN NUMBER,
444 p_term_sequence_number IN NUMBER,
445 p_printing_count IN NUMBER,
446 p_printing_original_date IN DATE) IS
447 BEGIN
448
449 IF
450 p_choice <> 'ADJ'
451
452 THEN
453
454 /* 4188835 - freeze for tax if printing columns updated */
455 IF NVL(p_printing_count, 0) = 0
456 THEN
457 /* This is the first run for this one -- freeze it */
458 arp_etax_util.global_document_update(p_customer_trx_id,
462
459 null,
460 'PRINT');
461 END IF;
463 UPDATE ra_customer_trx
464 SET printing_pending =
465 decode (p_trx_type, 'CM', 'N',
466 decode (p_term_count,
467 greatest(nvl(last_printed_sequence_num,0),
468 p_term_sequence_number), 'N',
469 NULL, 'N',
470 1, 'N',
471 0, 'N',
472 'Y')),
473 printing_count = decode(p_printing_count,
474 NULL, 0,
475 p_printing_count) + 1,
476 printing_last_printed = SYSDATE,
477 printing_original_date = decode(p_printing_count, 0, SYSDATE,
478 p_printing_original_date),
479 last_printed_sequence_num =
480 decode(p_term_count,NULL,NULL,
481 greatest(nvl(last_printed_sequence_num,0),
482 p_term_sequence_number))
483 WHERE customer_trx_id = p_customer_trx_id;
484
485 END IF ;
486 END update_customer_trx ;
487
488 /*=============================================================================
489 || PRIVATE FUNCTION get_taxyn
490 ||
491 || DESCRIPTION
492 ||
493 || ARGUMENTS
494 || IN: p_customer_trx_line_id
495 ||
496 || OUT:
497 ||
498 || FUNCTION CALL
499 ||
500 || RETURN
501 ||
502 || NOTE For ease of translation ar_lookup table is referred for
503 || getting 'Yes' or 'No'
504 ||
505 || MODIFICATION HISTORY
506 || 29-MAY-97 Ashim K Dey Created
507 =============================================================================*/
508 FUNCTION get_taxyn ( p_customer_trx_line_id IN NUMBER)
509 return VARCHAR2 IS
510
511 CURSOR sel_taxyn( line_id in number ) IS
512 SELECT 'x' from dual where exists
513 ( SELECT 'x'
514 FROM ra_customer_trx_lines l
515 WHERE l.link_to_cust_trx_line_id = line_id
516 AND l.line_type = 'TAX'
517 AND l.extended_amount <> 0 );
518
519 l_taxyn varchar2(80);
520 dummy varchar2(2);
521
522 BEGIN
523
524 OPEN sel_taxyn( p_customer_trx_line_id );
525 FETCH sel_taxyn into dummy;
526 IF sel_taxyn%FOUND
527
528 THEN
529
530 SELECT meaning
531 INTO l_taxyn
532 FROM ar_lookups
533 WHERE lookup_type = 'YES/NO'
534 AND lookup_code = 'Y' ;
535
536 ELSE
537
538 SELECT meaning
539 INTO l_taxyn
540 FROM ar_lookups
541 WHERE lookup_type = 'YES/NO'
542 AND lookup_code = 'N' ;
543
544 END IF;
545
546 CLOSE sel_taxyn;
547 return(l_taxyn);
548
549 END get_taxyn ;
550
551 /*=============================================================================
552 || PRIVATE FUNCTION get_remit_to_given_bill_to
553 ||
554 || DESCRIPTION This function implements the report local function
555 || get_remit_to_given_bill_to in RAXINV.rdf
556 ||
557 ||
558 || ARGUMENTS p_bill_to_site_use_id
559 ||
560 || FUNCTION CALL
561 ||
562 || RETURN remit_address_id
563 ||
564 || NOTE
565 ||
566 || MODIFICATION HISTORY
567 || 29-MAY-97 Ashim K Dey Created
568 =============================================================================*/
569
570 FUNCTION get_remit_to_given_bill_to( p_bill_to_site_use_id in number )
571 RETURN NUMBER IS
572
573 CURSOR remit_derive( inv_country IN varchar2,
574 inv_state IN varchar2 ,
575 inv_postal_code IN varchar2) IS
576
577 SELECT rt.address_id
578 FROM hz_cust_acct_sites acct_site,
579 hz_party_sites party_site,
580 hz_locations loc,
581 ra_remit_tos rt
582 WHERE acct_site.cust_acct_site_id = rt.address_id
583 AND acct_site.party_site_id = party_site.party_site_id
584 AND loc.location_id = party_site.location_id
585 AND nvl(rt.status,'A') = 'A'
586 AND nvl(acct_site.status, 'A') = 'A'
587 AND (nvl(rt.state, inv_state)= inv_state
588 OR
589 (inv_state IS NULL AND
590 rt.state IS NULL))
591 AND ((inv_postal_code between
592 rt.postal_code_low and rt.postal_code_high)
593 OR
594 (rt.postal_code_high IS NULL and rt.postal_code_low IS NULL))
595 AND rt.country = inv_country
596 ORDER BY rt.postal_code_low,
597 rt.postal_code_high,
598 rt.state,
599 loc.address1,
600 loc.address2;
601
602
603
604 CURSOR address( bill_site_use_id IN number ) is
605 SELECT loc.state,
606 loc.country,
607 loc.postal_code
608 FROM hz_cust_acct_sites acct_site,
609 hz_party_sites party_site,
610 hz_locations loc,
611 hz_cust_site_uses site_uses
612 WHERE acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
613 AND site_uses.site_use_id = bill_site_use_id
614 and acct_site.party_site_id = party_site.party_site_id
615 and loc.location_id = party_site.location_id;
616
617 inv_state VARCHAR2(60);
618 inv_country VARCHAR2(60);
619 inv_postal_code VARCHAR2(60);
620 remit_address_id NUMBER;
621 d varchar2(240);
622
623 BEGIN
624
625 OPEN address( p_bill_to_site_use_id );
626 FETCH address into inv_state,
627 inv_country,
628 inv_postal_code;
629
630
631 IF address%NOTFOUND
632
633 THEN
634
635 /* No Default Remit to Address can be found, use the default */
636
637 inv_state := 'DEFAULT';
638 inv_country := 'DEFAULT';
639 inv_postal_code := null;
640
641 END IF;
642
643 CLOSE address;
644
645 OPEN remit_derive( inv_country, inv_state, inv_postal_code );
646 FETCH remit_derive into remit_address_id;
647
648
649 IF remit_derive%NOTFOUND
650
651 THEN
652
653 CLOSE remit_derive;
654 OPEN remit_derive( 'DEFAULT', inv_state, inv_postal_code );
655 FETCH remit_derive into remit_address_id;
656
657 IF remit_derive%NOTFOUND
658
659 THEN
660
661 CLOSE remit_derive;
662 OPEN remit_derive( 'DEFAULT', inv_state, '' );
663 FETCH remit_derive into remit_address_id;
664
665 IF remit_derive%notfound
666
667 THEN
668
669 CLOSE remit_derive;
670 OPEN remit_derive( 'DEFAULT', 'DEFAULT', '' );
671 FETCH remit_derive into remit_address_id;
672
673 END IF;
674
675 END IF;
676
677 END IF;
678
679 CLOSE remit_derive;
680 RETURN( remit_address_id );
681
682 END get_remit_to_given_bill_to;
683
684
685 /*=============================================================================
686 || PRIVATE FUNCTION get_remit_address_id
687 ||
688 || DESCRIPTION This function gets the remit_address_id
689 ||
690 ||
691 || ARGUMENTS
692 ||
693 || FUNCTION CALL
694 ||
695 || RETURN
696 ||
697 || NOTE THis function implements REMIT_TO_CONTROL_IDformula
698 || of RAXINV.rdf
699 ||
700 || MODIFICATION HISTORY
701 || 29-MAY-97 Ashim K Dey Created
702 =============================================================================*/
703
704 FUNCTION get_remit_address_id(
705 p_remit_to_address_id IN NUMBER,
706 p_previous_customer_trx_id IN NUMBER,
707 p_trx_type IN VARCHAR2,
708 p_bill_to_site_use_id IN NUMBER )
709 RETURN VARCHAR2 IS
710
711 l_remit_to_address_id number;
712
713 BEGIN
714
715 l_remit_to_address_id := p_remit_to_address_id;
716
717 IF
718 ( (l_remit_to_address_id IS NULL) AND
719 (p_trx_type = 'CM') AND
720 (p_previous_customer_trx_id IS NOT NULL)
721 )
722
723 THEN
724 /* ra_customer_trx.remit_to_address_id is not populated
725 for CM, need to get it from the invoice. */
726
727 SELECT remit_to_address_id
728 INTO l_remit_to_address_id
729 FROM ra_customer_trx
730 WHERE customer_trx_id = p_previous_customer_trx_id;
731
732 END IF;
733
734 IF
735
736 l_remit_to_address_id IS NULL
737
738 THEN
739 RETURN(
740 AR_INVOICE_SQL_FUNC_PUB.get_remit_to_given_bill_to(
741 p_bill_to_site_use_id )
742 ) ;
743 ELSE
744
745 RETURN(l_remit_to_address_id );
746
747 END IF;
748
749 END get_remit_address_id;
750
751 END AR_INVOICE_SQL_FUNC_PUB ;
[Home] [Help]