[Home] [Help]
PACKAGE BODY: APPS.ARP_MAINTAIN_PS2
Source
1 PACKAGE BODY arp_maintain_ps2 AS
2 /* $Header: ARTEMP2B.pls 120.26.12010000.4 2008/11/20 11:52:59 npanchak ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 /* =======================================================================
6 | Global Data Types
7 * ======================================================================*/
8 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
9
10 ------------------------------------------------------------------------
11 -- Private types
12 ------------------------------------------------------------------------
13 -- Constants
14 --
15 -- Linefeed character
16 --
17 CRLF CONSTANT VARCHAR2(1) := arp_global.CRLF;
18
19 MSG_LEVEL_BASIC CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_BASIC;
20 MSG_LEVEL_TIMING CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_TIMING;
21 MSG_LEVEL_DEBUG CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG;
22 MSG_LEVEL_DEBUG2 CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG2;
23 MSG_LEVEL_DEVELOP CONSTANT BINARY_INTEGER :=
24 arp_global.MSG_LEVEL_DEVELOP;
25
26 YES CONSTANT VARCHAR2(1) := arp_global.YES;
27 NO CONSTANT VARCHAR2(1) := arp_global.NO;
28
29 DEP CONSTANT VARCHAR2(10) := 'DEP';
30 GUAR CONSTANT VARCHAR2(10) := 'GUAR';
31
32 FIFO CONSTANT VARCHAR2(10) := 'FIFO';
33 LIFO CONSTANT VARCHAR2(10) := 'LIFO';
34 PRORATE CONSTANT VARCHAR2(10) := 'PRORATE';
35
36
37 --
38 -- User-defined exceptions
39 --
40
41
42 --
43 -- Translated error messages
44 --
45
46
47 -- This record holds general information used by autoaccounting and
48 -- credit memo module. Passed as argument to most functions/procs.
49 --
50 system_info arp_trx_global.system_info_rec_type :=
51 arp_trx_global.system_info;
52
53 --
54 -- This record holds profile information used by autoaccounting and
55 -- credit memo module. Passed as argument to most functions/procs.
56 --
57 profile_info arp_trx_global.profile_rec_type :=
58 arp_trx_global.profile_info;
59
60 /*
61 bug 4891386-4923502 Modified the datatype of every column having
62 Binary Integer to Number(15) for all record types select_ips_rec_type,
63 select_ira_rec_type,select_ups_rec_type, select_iad_rec_type.
64 */
65
66
67 TYPE select_ips_rec_type IS RECORD
68 (
69 customer_trx_id NUMBER(15),
70 trx_number ra_customer_trx.trx_number%type,
71 cust_trx_type_id NUMBER(15),
72 trx_type ra_cust_trx_types.type%type,
73 trx_date DATE,
74 gl_date DATE,
75 customer_id NUMBER(15),
76 site_use_id NUMBER(15),
77 reversed_cash_receipt_id NUMBER(15),
78 currency_code ra_customer_trx.invoice_currency_code%type,
79 precision NUMBER,
80 min_acc_unit NUMBER,
81 exchange_rate_type ra_customer_trx.exchange_rate_type%type,
82 exchange_rate NUMBER,
83 exchange_date DATE,
84 term_id NUMBER(15),
85 first_installment_code ra_terms.first_installment_code%type,
86 rec_acctd_amount NUMBER,
87 total_line_amount NUMBER,
88 total_tax_amount NUMBER,
89 total_freight_amount NUMBER,
90 total_charges_amount NUMBER,
91 term_sequence_num NUMBER,
92 percent NUMBER,
93 due_date DATE
94 );
95
96
97 TYPE select_ira_rec_type IS RECORD
98 (
99 customer_trx_id NUMBER(15),
100 trx_number ra_customer_trx.trx_number%type,
101 cust_trx_type_id NUMBER(15),
102 post_to_gl_flag ra_cust_trx_types.post_to_gl%type,
103 credit_method ra_customer_trx.credit_method_for_installments%type,
104 trx_date DATE,
105 gl_date DATE,
106 customer_id NUMBER(15),
107 site_use_id NUMBER(15),
108 currency_code ra_customer_trx.invoice_currency_code%type,
109 precision NUMBER,
110 min_acc_unit NUMBER,
111 exchange_rate_type ra_customer_trx.exchange_rate_type%type,
112 exchange_rate NUMBER,
113 exchange_date DATE,
114 rec_acctd_amount NUMBER,
115 total_cm_line_amount NUMBER,
116 total_cm_tax_amount NUMBER,
117 total_cm_freight_amount NUMBER,
118 total_cm_charges_amount NUMBER,
119 code_combination_id NUMBER(15),
120 gl_date_closed DATE,
121 actual_date_closed DATE,
122 inv_customer_trx_id NUMBER(15),
123 inv_precision NUMBER,
124 inv_min_acc_unit NUMBER,
125 inv_exchange_rate NUMBER,
126 inv_payment_schedule_id NUMBER(15),
127 inv_amount_due_remaining NUMBER,
128 inv_acctd_amt_due_rem NUMBER,
129 inv_line_remaining NUMBER,
130 inv_tax_remaining NUMBER,
131 inv_freight_remaining NUMBER,
132 inv_charges_remaining NUMBER,
133 inv_amount_credited NUMBER
134 );
135
136 TYPE select_ups_rec_type IS RECORD
137 (
138 set_of_books_id NUMBER(15),
139 customer_trx_id NUMBER(15),
140 post_to_gl_flag ra_cust_trx_types.post_to_gl%type,
141 trx_date DATE,
142 gl_date DATE,
143 precision NUMBER,
144 min_acc_unit NUMBER,
145 adjusted_trx_id NUMBER(15),
146 subsequent_trx_id NUMBER(15),
147 commitment_trx_id NUMBER(15),
148 commitment_type ra_cust_trx_types.type%type,
149 ps_currency_code ra_customer_trx.invoice_currency_code%type,
150 ps_exchange_rate NUMBER,
151 ps_precision NUMBER,
152 ps_min_acc_unit NUMBER,
153 code_combination_id NUMBER(15),
154 gl_date_closed DATE,
155 actual_date_closed DATE,
156 total_line_amount NUMBER,
157 payment_schedule_id NUMBER(15),
158 amount_due_remaining NUMBER,
159 acctd_amt_due_rem NUMBER,
160 line_remaining NUMBER,
161 amount_adjusted NUMBER,
162 percent NUMBER,
163 allocate_tax_freight ra_cust_trx_types_all.allocate_tax_freight%type,
164 adjustment_type ar_adjustments_all.type%type,
165 tax_remaining NUMBER,
166 freight_remaining NUMBER,
167 total_tax_amount NUMBER,
168 total_freight_amount NUMBER
169 );
170
171
172 TYPE select_iad_rec_type IS RECORD
173 (
174 set_of_books_id NUMBER(15),
175 customer_trx_id NUMBER(15),
176 post_to_gl_flag ra_cust_trx_types.post_to_gl%type,
177 trx_date DATE,
178 gl_date DATE,
179 precision NUMBER,
180 min_acc_unit NUMBER,
181 adjusted_trx_id NUMBER(15),
182 invoice_trx_id NUMBER(15),
183 ps_currency_code ra_customer_trx.invoice_currency_code%type,
184 ps_exchange_rate NUMBER,
185 ps_precision NUMBER,
186 ps_min_acc_unit NUMBER,
187 commitment_code NUMBER(15),
188 code_combination_id NUMBER(15),
189 gl_date_closed DATE,
190 actual_date_closed DATE,
191 total_cm_line_amount NUMBER,
192 total_inv_adj_amount NUMBER,
193 total_inv_line_remaining NUMBER,
194 payment_schedule_id NUMBER(15),
195 ps_amount_due_remaining NUMBER,
196 ps_acctd_amt_due_rem NUMBER,
197 ps_line_original NUMBER,
198 ps_line_remaining NUMBER,
199 ps_tax_original NUMBER,
200 ps_tax_remaining NUMBER,
201 ps_freight_original NUMBER,
202 ps_freight_remaining NUMBER,
203 ps_amount_adjusted NUMBER,
204 allocate_tax_freight ra_cust_trx_types.allocate_tax_freight%type,
205 adjustment_type ar_adjustments.type%type,
206 total_cm_tax_amount NUMBER,
207 total_cm_frt_amount NUMBER,
208 -- total_inv_line_adj NUMBER,
209 -- total_inv_tax_adj NUMBER,
210 -- total_inv_frt_adj NUMBER,
211 inv_line_adj NUMBER,
212 inv_tax_adj NUMBER,
213 inv_frt_adj NUMBER
214 );
215
216
217 TYPE id_table_type IS
218 TABLE OF BINARY_INTEGER
219 INDEX BY BINARY_INTEGER;
220
221 TYPE number_table_type IS
222 TABLE OF NUMBER
223 INDEX BY BINARY_INTEGER;
224
225 TYPE date_table_type IS
226 TABLE OF DATE
227 INDEX BY BINARY_INTEGER;
228
229 id_t id_table_type;
230 null_id_t CONSTANT id_table_type := id_t;
231
232 number_t number_table_type;
233 null_number_t CONSTANT number_table_type := number_t;
234
235 date_t date_table_type;
236 null_date_t CONSTANT date_table_type := date_t;
237
238 --
239 -- For the commitment balance package
240 --
241 g_oe_install_flag VARCHAR2(240);
242 g_so_source_code VARCHAR2(240);
243
244 ------------------------------------------------------------------------
245 -- Private cursors
246 ------------------------------------------------------------------------
247
248 ------------------------------------------------------------------------
249 -- Covers
250 ------------------------------------------------------------------------
251 PROCEDURE debug( p_line IN VARCHAR2 ) IS
252 BEGIN
253 IF PG_DEBUG IN ('Y','C')
254 THEN
255 arp_util.debug( p_line );
256 END IF;
257 END;
258 --
259 PROCEDURE debug(
260 p_str VARCHAR2,
261 p_print_level BINARY_INTEGER ) IS
262 BEGIN
263 IF PG_DEBUG IN ('Y', 'C')
264 THEN
265 arp_util.debug( p_str, p_print_level );
266 END IF;
267 END;
268 --
269 PROCEDURE enable_debug IS
270 BEGIN
271 arp_util.enable_debug;
272 END;
273 --
274 PROCEDURE enable_debug( buffer_size NUMBER ) IS
275 BEGIN
276 arp_util.enable_debug( buffer_size );
277 END;
278 --
279 PROCEDURE disable_debug IS
280 BEGIN
281 arp_util.disable_debug;
282 END;
283 --
284 PROCEDURE print_fcn_label( p_label VARCHAR2 ) IS
285 BEGIN
286 IF PG_DEBUG IN ('Y','C')
287 THEN
288 arp_util.print_fcn_label( p_label );
289 END IF;
290 END;
291 --
292 PROCEDURE print_fcn_label2( p_label VARCHAR2 ) IS
293 BEGIN
294 IF PG_DEBUG IN ('Y', 'C')
295 THEN
296 arp_util.print_fcn_label2( p_label );
297 END IF;
298 END;
299 --
300 PROCEDURE close_cursor( p_cursor_handle IN OUT NOCOPY INTEGER ) IS
301 BEGIN
302 arp_util.close_cursor( p_cursor_handle );
303 END;
304
305
306 ----------------------------------------------------------------------------
307 -- Functions and Procedures
308 ----------------------------------------------------------------------------
309
310 PROCEDURE distribute_amount(
311 p_count IN NUMBER,
312 p_currency_code IN VARCHAR2,
313 p_total IN NUMBER,
314 p_percent_t IN number_table_type,
315 p_amount_t IN OUT NOCOPY number_table_type ) IS
316
317
318 l_balance NUMBER := p_total;
319 l_amount NUMBER;
320
321
322 BEGIN
323 print_fcn_label2('arp_maintain_ps2.distribute_amount()+' );
324
325 DEBUG( ' Total = ' || p_total, MSG_LEVEL_DEBUG);
326
327 FOR i IN 0..p_count - 1 LOOP
328
329 ------------------------------------------------------------------
330 -- Check if last index position, if so, use remaining amount
331 -- which includes rounding errors
332 ------------------------------------------------------------------
333 IF( i = p_count - 1 ) THEN
334
335 p_amount_t( i ) := l_balance;
336
337 ELSE
338
339 l_amount := arp_util.CurrRound( p_total * p_percent_t( i ),
340 p_currency_code );
341 p_amount_t( i ) := l_amount;
342 l_balance := l_balance - l_amount;
343
344 END IF;
345
346 DEBUG(' Term: ' || i ||
347 ' Percent = ' || p_percent_t(i) ||
348 ' Amount = ' || p_amount_t(i),
349 MSG_LEVEL_DEBUG);
350
351 END LOOP;
352
353
354 print_fcn_label2('arp_maintain_ps2.distribute_amount()-' );
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 debug( 'EXCEPTION: arp_maintain_ps2.distribute_amount()',
359 MSG_LEVEL_BASIC );
360 RAISE;
361 END distribute_amount;
362
363
364 ------------------------------------------------------------------------
365
366 PROCEDURE compute_acctd_amount(
367 p_count IN NUMBER,
368 p_functional_currency IN VARCHAR2,
369 p_exchange_rate IN NUMBER,
370 p_line_amount_t IN number_table_type,
371 p_tax_amount_t IN number_table_type,
372 p_freight_amount_t IN number_table_type,
373 p_charges_amount_t IN number_table_type,
374 p_acctd_amount_t IN OUT NOCOPY number_table_type,
375 p_acctd_total IN NUMBER ) IS
376
377
378 l_balance NUMBER := p_acctd_total;
379 l_amount NUMBER;
380
381
382 BEGIN
383 print_fcn_label2('arp_maintain_ps2.compute_acctd_amount()+' );
384
385
386 FOR i IN 0..p_count - 1 LOOP
387
388 ------------------------------------------------------------------
389 -- Check if last index position, if so, use remaining amount
390 -- which includes rounding errors
391 ------------------------------------------------------------------
392 IF( i = p_count - 1 ) THEN
393
394 p_acctd_amount_t( i ) := l_balance;
395
396 ELSE
397
398 l_amount := arp_util.functional_amount(
399 p_line_amount_t( i ) +
400 p_tax_amount_t( i ) +
401 p_freight_amount_t( i ) +
402 p_charges_amount_t( i ),
403 p_functional_currency, p_exchange_rate,
404 null, null );
405
406 p_acctd_amount_t( i ) := l_amount;
407 l_balance := l_balance - l_amount;
408
409 END IF;
410
411 END LOOP;
412
413
414 print_fcn_label2('arp_maintain_ps2.compute_acctd_amount()-' );
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 debug( 'EXCEPTION: arp_maintain_ps2.compute_acctd_amount()',
419 MSG_LEVEL_BASIC );
420 RAISE;
421 END compute_acctd_amount;
422
423
424 ----------------------------------------------------------------------------
425 --
426 -- PROCEDURE NAME: build_ips_sql
427 --
428 -- DECSRIPTION:
429 --
430 -- ARGUMENTS:
431 -- IN:
432 -- system_info
433 -- profile_info
434 --
435 -- IN/OUT:
436 -- insert_ps_c
437 -- select_c
438 --
439 -- OUT:
440 --
441 -- RETURNS:
442 --
443 -- NOTES:
444 --
445 -- HISTORY:
446 --
447 ----------------------------------------------------------------------------
448 PROCEDURE build_ips_sql(
449 p_system_info IN arp_trx_global.system_info_rec_type,
450 p_profile_info IN arp_trx_global.profile_rec_type,
451 p_select_c IN OUT NOCOPY INTEGER,
452 p_insert_ps_c IN OUT NOCOPY INTEGER ) IS
453
454 l_insert_ps_sql VARCHAR2(5000);
455 l_select_sql VARCHAR2(5000);
456
457
458 BEGIN
459
460 print_fcn_label( 'arp_maintain_ps2.build_ips_sql()+' );
461
462 ------------------------------------------------
463 -- Select sql
464 ------------------------------------------------
465 l_select_sql :=
466 'SELECT
467 /* :user_id, */
468 ctl.customer_trx_id,
469 /* null, */
470 sum(decode(ctl.line_type, ''LINE'', ctl.extended_amount,
471 ''CB'', ctl.extended_amount, 0)),
472 sum(decode(ctl.line_type, ''TAX'', ctl.extended_amount, 0)),
473 sum(decode(ctl.line_type, ''FREIGHT'', ctl.extended_amount, 0)),
474 t.term_id,
475 tl.sequence_num,
476 /* Bug fix 5589303, If billing_date is not null, billing_date shall be used to calculate due date */
477 decode(ctt.type, ''CM'', ct.trx_date, nvl(tl.due_date,
478 decode(tl.due_days,
479 null,
480 decode(least(to_number(substrb(nvl(ct.billing_date, ct.trx_date),1,2)),
481 nvl(t.due_cutoff_day,32)),
482 t.due_cutoff_day,
483 last_day(add_months(nvl(ct.billing_date, ct.trx_date),
484 tl.due_months_forward)) +
485 least(tl.due_day_of_month,
486 to_number(substrb(last_day(add_months(
487 nvl(ct.billing_date, ct.trx_date),
488 tl.due_months_forward + 1)),1,2))),
489 last_day(add_months(nvl(ct.billing_date, ct.trx_date),
490 (tl.due_months_forward - 1)))
491 + least(tl.due_day_of_month,
492 to_number(substrb(last_day(add_months(
493 nvl(ct.billing_date, ct.trx_date),
494 tl.due_months_forward)),1,2)))),
495 nvl(ct.billing_date, ct.trx_date) + tl.due_days))),
496 ct.bill_to_customer_id,
497 ctt.type,
498 ct.bill_to_site_use_id,
499 ct.cust_trx_type_id,
500 ct.invoice_currency_code,
501 /* null, */
502 ct.exchange_rate_type,
503 ct.exchange_rate,
504 ct.exchange_date,
505 ct.trx_number,
506 ct.trx_date,
507 tl.relative_amount / t.base_amount,
508 c.precision,
509 /* 1, */
510 t.first_installment_code,
511 c.minimum_accountable_unit,
512 nvl(ctlgd.gl_date, ct.trx_date),
513 nvl(ctlgd.acctd_amount, 0),
514 /* null, */
515 /* :raagixlul, */
516 sum(decode(ctl.line_type, ''CHARGES'', ctl.extended_amount, 0)),
517 ct.reversed_cash_receipt_id /* Bug3328690 */
518 FROM
519 ra_terms t,
520 ra_terms_lines tl,
521 ra_cust_trx_types ctt,
522 ra_customer_trx ct,
523 ra_cust_trx_line_gl_dist ctlgd,
524 fnd_currencies c,
525 ra_customer_trx_lines ctl
526 WHERE ct.customer_trx_id = :customer_trx_id
527 AND ctl.customer_trx_id = ctlgd.customer_trx_id
528 AND ctlgd.account_class = ''REC''
529 AND ctlgd.latest_rec_flag = ''Y''
530 AND ctl.customer_trx_id = ct.customer_trx_id
531 AND ct.invoice_currency_code = c.currency_code
532 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
533 AND ctt.accounting_affect_flag = ''Y'' /* Open Receivables = Y */
534 AND ct.term_id = t.term_id(+)
535 AND t.term_id = tl.term_id(+)
536 and not (ctt.type = ''CM'' and ct.previous_customer_trx_id is not NULL)
537 GROUP BY
538 ctl.customer_trx_id,
539 t.term_id,
540 tl.relative_amount,
541 t.base_amount,
542 tl.sequence_num,
543 tl.due_date,
544 tl.due_days,
545 ct.trx_date,
546 t.due_cutoff_day,
547 tl.due_months_forward,
548 tl.due_day_of_month,
549 ct.bill_to_customer_id,
550 ctt.type,
551 ct.bill_to_site_use_id,
552 ct.cust_trx_type_id,
553 ct.invoice_currency_code,
554 ct.exchange_rate_type,
555 ct.exchange_rate,
556 ct.exchange_date,
557 ct.trx_number,
558 c.minimum_accountable_unit,
559 t.first_installment_code,
560 ctlgd.gl_date,
561 ctlgd.acctd_amount,
562 c.precision,
563 ct.reversed_cash_receipt_id, /*Bug3328690 */
564 ct.billing_date
565 ORDER BY
566 ctl.customer_trx_id,
567 tl.sequence_num';
568
569 debug(' select_sql = ' || CRLF ||
570 l_select_sql || CRLF,
571 MSG_LEVEL_DEBUG);
572 debug(' len(select_sql) = '||
573 to_char(length(l_select_sql)) || CRLF,
574 MSG_LEVEL_DEBUG);
575
576
577 ------------------------------------------------
578 -- Insert sql
579 ------------------------------------------------
580 l_insert_ps_sql :=
581 'INSERT INTO AR_PAYMENT_SCHEDULES
582 (
583 created_by,
584 creation_date,
585 last_updated_by,
586 last_update_date,
587 last_update_login,
588 request_id,
589 program_application_id,
590 program_id,
591 program_update_date,
592 payment_schedule_id,
593 customer_trx_id,
594 amount_due_original,
595 amount_due_remaining,
596 acctd_amount_due_remaining,
597 amount_line_items_original,
598 amount_line_items_remaining,
599 tax_original,
600 tax_remaining,
601 freight_original,
602 freight_remaining,
603 receivables_charges_charged,
604 receivables_charges_remaining,
605 amount_credited,
606 amount_applied,
607 term_id,
608 terms_sequence_number,
609 due_date,
610 customer_id,
611 class,
612 customer_site_use_id,
613 cust_trx_type_id,
614 number_of_due_dates,
615 status,
616 invoice_currency_code,
617 actual_date_closed,
618 exchange_rate_type,
619 exchange_rate,
620 exchange_date,
621 trx_number,
622 trx_date,
623 gl_date_closed,
624 gl_date,
625 reversed_cash_receipt_id
626 ,org_id
627 )
628 VALUES
629 (
630 :user_id, /* created_by */
631 sysdate, /* creation_date */
632 :user_id, /* last_updated_by */
633 sysdate, /* last_update_date */
634 :login_id, /* last_update_login */
635 :request_id, /* request_id */
636 decode(:application_id,
637 -1, null, :application_id), /* program_application_id */
638 decode(:program_id, -1, null, :program_id), /* program_id */
639 sysdate, /* program_update_date */
640 :payment_schedule_id,
641 :customer_trx_id, /* customer_trx_id */
642 :line_amt + nvl(:tax_amt, 0) + nvl(:frt_amt, 0) +
643 nvl(:charge_amt, 0), /* ado */
644 :line_amt + nvl(:tax_amt, 0) + nvl(:frt_amt, 0) +
645 nvl(:charge_amt, 0), /* adr */
646 :acctd_adr, /* acctd_amount_due_remaining */
647 :line_amt, /* alio */
648 :line_amt, /* alir */
649 nvl(:tax_amt, 0), /* tax_original */
650 nvl(:tax_amt, 0), /* tax_remaining */
651 nvl(:frt_amt, 0), /* freight_original */
652 nvl(:frt_amt, 0), /* freight_remaining */
653 nvl(:charge_amt, 0), /* receivables_charges_charged */
654 nvl(:charge_amt, 0), /* receivables_charges_remaining */
655 null, /* amount_credited */
656 null, /* amount_applied */
657 :term_id, /* term_id */
658 nvl(:terms_sequence_number,1), /* terms_sequence_number */
659 :due_date,
660 :customer_id,
661 :type, /* class */
662 :site_use_id, /* customer_site_use_id */
663 :cust_trx_type_id, /* cust_trx_type_id */
664 :number_of_due_dates, /* number_of_due_dates */
665 decode(:line_amt + nvl(:tax_amt, 0) + nvl(:frt_amt, 0)+ nvl(:charge_amt, 0),
666 0, ''CL'', ''OP''), /* status */
667 :currency_code,
668 nvl(decode(:line_amt + nvl(:tax_amt,0) + nvl(:frt_amt,0)+
669 nvl(:charge_amt,0),
670 0, :trx_date, null), to_date(''12/31/4712'',''MM/DD/YYYY'')),/* bug#2678029 lgandhi actual_date_closed */
671 :exchange_rate_type,
672 :exchange_rate,
673 :exchange_date,
674 :trx_number,
675 :trx_date,
676 nvl(decode(:line_amt + nvl(:tax_amt, 0) + nvl(:frt_amt, 0) +
677 nvl(:charge_amt, 0),
678 0, nvl(:gl_date, :trx_date), null),to_date(''12/31/4712'',''MM/DD/YYYY'')), /* gl_date_closed */
679 nvl(:gl_date, :trx_date),
680 :reversed_cash_receipt_id
681 , :org_id--arp_standard.sysparm.org_id /* SSA changes anuj */
682 )' ;
683
684 debug(' insert_ps_sql = ' || CRLF ||
685 l_insert_ps_sql || CRLF,
686 MSG_LEVEL_DEBUG);
687 debug(' len(insert_ps_sql) = '||
688 to_char(length(l_insert_ps_sql)) || CRLF,
689 MSG_LEVEL_DEBUG);
690
691
692
693
694 ------------------------------------------------
695 -- Parse sql stmts
696 ------------------------------------------------
697 BEGIN
698 debug( ' Parsing stmts', MSG_LEVEL_DEBUG );
699
700 p_insert_ps_c := dbms_sql.open_cursor;
701 dbms_sql.parse( p_insert_ps_c, l_insert_ps_sql,
702 dbms_sql.v7 );
703
704 p_select_c := dbms_sql.open_cursor;
705 dbms_sql.parse( p_select_c, l_select_sql,
706 dbms_sql.v7 );
707
708 EXCEPTION
709 WHEN OTHERS THEN
710 debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
711 RAISE;
712 END;
713
714 print_fcn_label( 'arp_maintain_ps2.build_ips_sql()-' );
715
716
717 EXCEPTION
718 WHEN OTHERS THEN
719 debug( 'EXCEPTION: arp_maintain_ps2.build_ips_sql()',
720 MSG_LEVEL_BASIC );
721
722 RAISE;
723 END build_ips_sql;
724
725
726 ----------------------------------------------------------------------------
727 PROCEDURE define_ips_select_columns(
728 p_select_c IN INTEGER,
729 p_select_rec IN select_ips_rec_type ) IS
730
731 BEGIN
732
733 print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()+' );
734
735 dbms_sql.define_column( p_select_c, 1, p_select_rec.customer_trx_id );
736 dbms_sql.define_column( p_select_c, 2, p_select_rec.total_line_amount );
737 dbms_sql.define_column( p_select_c, 3, p_select_rec.total_tax_amount );
738 dbms_sql.define_column( p_select_c, 4, p_select_rec.total_freight_amount );
739 dbms_sql.define_column( p_select_c, 5, p_select_rec.term_id );
740 dbms_sql.define_column( p_select_c, 6, p_select_rec.term_sequence_num );
741 dbms_sql.define_column( p_select_c, 7, p_select_rec.due_date );
742 dbms_sql.define_column( p_select_c, 8, p_select_rec.customer_id );
743 dbms_sql.define_column( p_select_c, 9, p_select_rec.trx_type, 20 );
744 dbms_sql.define_column( p_select_c, 10, p_select_rec.site_use_id );
745 dbms_sql.define_column( p_select_c, 11, p_select_rec.cust_trx_type_id );
746 dbms_sql.define_column( p_select_c, 12,
747 p_select_rec.currency_code, 15 );
748 dbms_sql.define_column( p_select_c, 13,
749 p_select_rec.exchange_rate_type, 30 );
750 dbms_sql.define_column( p_select_c, 14, p_select_rec.exchange_rate );
751 dbms_sql.define_column( p_select_c, 15, p_select_rec.exchange_date );
752 dbms_sql.define_column( p_select_c, 16, p_select_rec.trx_number, 20 );
753 dbms_sql.define_column( p_select_c, 17, p_select_rec.trx_date );
754 dbms_sql.define_column( p_select_c, 18, p_select_rec.percent );
755 dbms_sql.define_column( p_select_c, 19, p_select_rec.precision );
756 dbms_sql.define_column( p_select_c, 20,
757 p_select_rec.first_installment_code, 12 );
758 dbms_sql.define_column( p_select_c, 21,
759 p_select_rec.min_acc_unit );
760 dbms_sql.define_column( p_select_c, 22, p_select_rec.gl_date );
761
762 dbms_sql.define_column( p_select_c, 23, p_select_rec.rec_acctd_amount );
763 dbms_sql.define_column( p_select_c, 24,
764 p_select_rec.total_charges_amount );
765 dbms_sql.define_column( p_select_c, 25,p_select_rec.reversed_cash_receipt_id); /*Bug3328690 */
766
767
768 print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()-' );
769
770 EXCEPTION
771 WHEN OTHERS THEN
772 debug('EXCEPTION: arp_maintain_ps2.define_ips_select_columns()',
773 MSG_LEVEL_BASIC);
774 RAISE;
775 END define_ips_select_columns;
776
777
778 ----------------------------------------------------------------------------
779 PROCEDURE get_ips_column_values( p_select_c IN INTEGER,
780 p_select_rec IN OUT NOCOPY select_ips_rec_type ) IS
781 /* Bug 460927 - Modified IN to IN OUT in the above line - Oracle 8 */
782 BEGIN
783 print_fcn_label2( 'arp_maintain_ps2.get_ips_column_values()+' );
784
785 dbms_sql.column_value( p_select_c, 1, p_select_rec.customer_trx_id );
786 dbms_sql.column_value( p_select_c, 2, p_select_rec.total_line_amount );
787 dbms_sql.column_value( p_select_c, 3, p_select_rec.total_tax_amount );
788 dbms_sql.column_value( p_select_c, 4, p_select_rec.total_freight_amount );
789 dbms_sql.column_value( p_select_c, 5, p_select_rec.term_id );
790 dbms_sql.column_value( p_select_c, 6, p_select_rec.term_sequence_num );
791 dbms_sql.column_value( p_select_c, 7, p_select_rec.due_date );
792 dbms_sql.column_value( p_select_c, 8, p_select_rec.customer_id );
793 dbms_sql.column_value( p_select_c, 9, p_select_rec.trx_type );
794 dbms_sql.column_value( p_select_c, 10, p_select_rec.site_use_id );
795 dbms_sql.column_value( p_select_c, 11, p_select_rec.cust_trx_type_id );
796 dbms_sql.column_value( p_select_c, 12,
797 p_select_rec.currency_code );
798 dbms_sql.column_value( p_select_c, 13,
799 p_select_rec.exchange_rate_type );
800 dbms_sql.column_value( p_select_c, 14, p_select_rec.exchange_rate );
801 dbms_sql.column_value( p_select_c, 15, p_select_rec.exchange_date );
802 dbms_sql.column_value( p_select_c, 16, p_select_rec.trx_number );
803 dbms_sql.column_value( p_select_c, 17, p_select_rec.trx_date );
804 dbms_sql.column_value( p_select_c, 18, p_select_rec.percent );
805 dbms_sql.column_value( p_select_c, 19, p_select_rec.precision );
806 dbms_sql.column_value( p_select_c, 20,
807 p_select_rec.first_installment_code );
808 dbms_sql.column_value( p_select_c, 21,
809 p_select_rec.min_acc_unit );
810 dbms_sql.column_value( p_select_c, 22, p_select_rec.gl_date );
811
812 dbms_sql.column_value( p_select_c, 23, p_select_rec.rec_acctd_amount );
813 dbms_sql.column_value( p_select_c, 24, p_select_rec.total_charges_amount );
814 dbms_sql.column_value( p_select_c, 25, p_select_rec.reversed_cash_receipt_id); /*Bug3328690 */
815
816 print_fcn_label2( 'arp_maintain_ps2.get_ips_column_values()-' );
817 EXCEPTION
818 WHEN OTHERS THEN
819 debug('EXCEPTION: arp_maintain_ps2.get_ips_column_values()',
820 MSG_LEVEL_BASIC);
821 RAISE;
822 END get_ips_column_values;
823
824
825 ----------------------------------------------------------------------------
826 --
827 -- PROCEDURE NAME: dump_ips_select_rec
828 --
829 -- DECSRIPTION:
830 --
831 -- ARGUMENTS:
832 -- IN:
833 -- select_rec
834 --
835 -- IN/OUT:
836 --
837 -- OUT:
838 --
839 -- RETURNS:
840 --
841 -- NOTES:
842 --
843 -- HISTORY:
844 --
845 ----------------------------------------------------------------------------
846 PROCEDURE dump_ips_select_rec( p_select_rec IN select_ips_rec_type ) IS
847 BEGIN
848
849 print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()+' );
850
851 debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
852 debug( ' customer_trx_id='
853 || p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
854 debug( ' trx_number='
855 || p_select_rec.trx_number, MSG_LEVEL_DEBUG );
856 debug( ' cust_trx_type_id='
857 || p_select_rec.cust_trx_type_id, MSG_LEVEL_DEBUG );
858 debug( ' trx_type='
859 || p_select_rec.trx_type, MSG_LEVEL_DEBUG );
860 debug( ' trx_date='
861 || p_select_rec.trx_date, MSG_LEVEL_DEBUG );
862 debug( ' gl_date='
863 || p_select_rec.gl_date, MSG_LEVEL_DEBUG );
864 debug( ' customer_id='
865 || p_select_rec.customer_id, MSG_LEVEL_DEBUG );
866 debug( ' site_use_id='
867 || p_select_rec.site_use_id, MSG_LEVEL_DEBUG );
868 debug( ' reversed_cash_receipt_id='
869 || p_select_rec.reversed_cash_receipt_id, MSG_LEVEL_DEBUG );
870 debug( ' currency_code='
871 || p_select_rec.currency_code, MSG_LEVEL_DEBUG );
872 debug( ' precision='
873 || p_select_rec.precision, MSG_LEVEL_DEBUG );
874 debug( ' min_acc_unit='
875 || p_select_rec.min_acc_unit, MSG_LEVEL_DEBUG );
876 debug( ' exchange_rate_type='
877 || p_select_rec.exchange_rate_type, MSG_LEVEL_DEBUG );
878 debug( ' exchange_rate='
879 || p_select_rec.exchange_rate, MSG_LEVEL_DEBUG );
880 debug( ' exchange_date='
881 || p_select_rec.exchange_date, MSG_LEVEL_DEBUG );
882 debug( ' term_id='
883 || p_select_rec.term_id, MSG_LEVEL_DEBUG );
884 debug( ' first_installment_code='
885 || p_select_rec.first_installment_code, MSG_LEVEL_DEBUG );
886 debug( ' rec_acctd_amount='
887 || p_select_rec.rec_acctd_amount, MSG_LEVEL_DEBUG );
888 debug( ' total_line_amount='
889 || p_select_rec.total_line_amount, MSG_LEVEL_DEBUG );
890 debug( ' total_tax_amount='
891 || p_select_rec.total_tax_amount, MSG_LEVEL_DEBUG );
892 debug( ' total_freight_amount='
893 || p_select_rec.total_freight_amount, MSG_LEVEL_DEBUG );
894 debug( ' total_charges_amount='
895 || p_select_rec.total_charges_amount, MSG_LEVEL_DEBUG );
896 debug( ' term_sequence_num='
897 || p_select_rec.term_sequence_num, MSG_LEVEL_DEBUG );
898 debug( ' percent='
899 || p_select_rec.percent, MSG_LEVEL_DEBUG );
900 debug( ' due_date='
901 || p_select_rec.due_date, MSG_LEVEL_DEBUG );
902
903 print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()-' );
904
905 EXCEPTION
906 WHEN OTHERS THEN
907 debug( 'EXCEPTION: arp_maintain_ps2.dump_ips_select_rec()',
908 MSG_LEVEL_BASIC );
909 RAISE;
910 END dump_ips_select_rec;
911
912
913 ------------------------------------------------------------------------
914
915 PROCEDURE process_ips_data(
916 p_system_info IN arp_trx_global.system_info_rec_type,
917 p_profile_info IN arp_trx_global.profile_rec_type,
918 p_insert_ps_c IN INTEGER,
919 p_select_rec IN select_ips_rec_type,
920 p_number_of_due_dates IN NUMBER,
921 p_percent_t IN number_table_type,
922 p_terms_sequence_num_t IN number_table_type,
923 p_due_date_t IN date_table_type,
924 p_line_amount_t IN OUT NOCOPY number_table_type,
925 p_tax_amount_t IN OUT NOCOPY number_table_type,
926 p_freight_amount_t IN OUT NOCOPY number_table_type,
927 p_charges_amount_t IN OUT NOCOPY number_table_type,
928 p_acctd_amt_due_rem_t IN OUT NOCOPY number_table_type ) IS
929
930 l_ignore INTEGER;
931
932 l_ps_id ar_payment_schedules.payment_schedule_id%type;
933 /* BR Sped Project */
934 l_jgzz_product_code VARCHAR2(100);
935 lcursor NUMBER;
936 lignore NUMBER;
937 sqlstmt VARCHAR2(254);
938 l_return_value_jl NUMBER;
939 /* BR Sped Project */
940 BEGIN
941 print_fcn_label2('arp_maintain_ps2.process_ips_data()+' );
942
943
944 --------------------------------------------------------------------
945 -- Distribute line amount
946 --------------------------------------------------------------------
947 distribute_amount(
948 p_number_of_due_dates,
949 p_select_rec.currency_code,
950 p_select_rec.total_line_amount,
951 p_percent_t,
952 p_line_amount_t );
953
954 --------------------------------------------------------------------
955 -- Distribute charges amount
956 --------------------------------------------------------------------
957 distribute_amount(
958 p_number_of_due_dates,
959 p_select_rec.currency_code,
960 p_select_rec.total_charges_amount,
961 p_percent_t,
962 p_charges_amount_t );
963
964
965 --------------------------------------------------------------------
966 -- Distribute tax and freight amount
967 --------------------------------------------------------------------
968 IF( p_select_rec.first_installment_code = 'INCLUDE' ) THEN
969
970 --------------------------------------------------------------------
971 -- Put tax in 1st installment
972 --------------------------------------------------------------------
973 p_tax_amount_t( 0 ) := p_select_rec.total_tax_amount;
974
975 FOR i IN 1..p_number_of_due_dates - 1 LOOP
976 p_tax_amount_t( i ) := 0;
977 END LOOP;
978
979 --------------------------------------------------------------------
980 -- Put freight in 1st installment
981 --------------------------------------------------------------------
982 p_freight_amount_t( 0 ) := p_select_rec.total_freight_amount;
983
984 FOR i IN 1..p_number_of_due_dates - 1 LOOP
985 p_freight_amount_t( i ) := 0;
986 END LOOP;
987
988
989 ELSE
990
991 --------------------------------------------------------------------
992 -- Distribute tax amount
993 --------------------------------------------------------------------
994 distribute_amount(
995 p_number_of_due_dates,
996 p_select_rec.currency_code,
997 p_select_rec.total_tax_amount,
998 p_percent_t,
999 p_tax_amount_t );
1000
1001 --------------------------------------------------------------------
1002 -- Distribute freight amount
1003 --------------------------------------------------------------------
1004 distribute_amount(
1005 p_number_of_due_dates,
1006 p_select_rec.currency_code,
1007 p_select_rec.total_freight_amount,
1008 p_percent_t,
1009 p_freight_amount_t );
1010 END IF;
1011
1012 --------------------------------------------------------------------
1013 -- Calculate accounted amount due_remaining
1014 --------------------------------------------------------------------
1015 compute_acctd_amount(
1016 p_number_of_due_dates,
1017 p_system_info.base_currency,
1018 p_select_rec.exchange_rate,
1019 p_line_amount_t,
1020 p_tax_amount_t,
1021 p_freight_amount_t,
1022 p_charges_amount_t,
1023 p_acctd_amt_due_rem_t,
1024 p_select_rec.rec_acctd_amount );
1025
1026
1027
1028 -------------------------------------------------------------
1029 -- Insert into ar_payment_schedules
1030 -------------------------------------------------------------
1031
1032 FOR i IN 0..p_number_of_due_dates - 1 LOOP
1033
1034 -------------------------------------------------------------
1035 -- Bind vars
1036 -------------------------------------------------------------
1037 BEGIN
1038 debug( ' Binding insert_ps_c', MSG_LEVEL_DEBUG );
1039
1040 dbms_sql.bind_variable( p_insert_ps_c,
1041 'user_id',
1042 p_profile_info.user_id );
1043
1044 dbms_sql.bind_variable( p_insert_ps_c,
1045 'login_id',
1046 p_profile_info.conc_login_id );
1047
1048 dbms_sql.bind_variable( p_insert_ps_c,
1049 'request_id',
1050 p_profile_info.request_id );
1051
1052 dbms_sql.bind_variable( p_insert_ps_c,
1053 'application_id',
1054 p_profile_info.application_id );
1055
1056 dbms_sql.bind_variable( p_insert_ps_c,
1057 'program_id',
1058 p_profile_info.conc_program_id );
1059
1060 /* retrieve the next payment schedule id. Added for
1061 MRC trigger replacement */
1062
1063 SELECT ar_payment_schedules_s.nextval
1064 INTO l_ps_id
1065 FROM dual;
1066
1067 dbms_sql.bind_variable( p_insert_ps_c,
1068 'payment_schedule_id',
1069 l_ps_id );
1070
1071
1072 dbms_sql.bind_variable( p_insert_ps_c,
1073 'customer_trx_id',
1074 p_select_rec.customer_trx_id );
1075
1076 dbms_sql.bind_variable( p_insert_ps_c,
1077 'line_amt',
1078 p_line_amount_t(i) );
1079
1080 dbms_sql.bind_variable( p_insert_ps_c,
1081 'tax_amt',
1082 p_tax_amount_t(i) );
1083
1084 dbms_sql.bind_variable( p_insert_ps_c,
1085 'frt_amt',
1086 p_freight_amount_t(i) );
1087
1088 dbms_sql.bind_variable( p_insert_ps_c,
1089 'charge_amt',
1090 p_charges_amount_t(i) );
1091
1092 dbms_sql.bind_variable( p_insert_ps_c,
1093 'acctd_adr',
1094 p_acctd_amt_due_rem_t(i) );
1095
1096 dbms_sql.bind_variable( p_insert_ps_c,
1097 'terms_sequence_number',
1098 p_terms_sequence_num_t(i) );
1099
1100 dbms_sql.bind_variable( p_insert_ps_c,
1101 'due_date',
1102 p_due_date_t(i) );
1103
1104 dbms_sql.bind_variable( p_insert_ps_c,
1105 'term_id',
1106 p_select_rec.term_id );
1107
1108 dbms_sql.bind_variable( p_insert_ps_c,
1109 'customer_id',
1110 p_select_rec.customer_id );
1111
1112 dbms_sql.bind_variable( p_insert_ps_c,
1113 'type',
1114 p_select_rec.trx_type );
1115
1116 dbms_sql.bind_variable( p_insert_ps_c,
1117 'site_use_id',
1118 p_select_rec.site_use_id );
1119
1120 dbms_sql.bind_variable( p_insert_ps_c,
1121 'cust_trx_type_id',
1122 p_select_rec.cust_trx_type_id );
1123
1124 dbms_sql.bind_variable( p_insert_ps_c,
1125 'currency_code',
1126 p_select_rec.currency_code );
1127
1128 dbms_sql.bind_variable( p_insert_ps_c,
1129 'trx_date',
1130 p_select_rec.trx_date );
1131
1132 dbms_sql.bind_variable( p_insert_ps_c,
1133 'gl_date',
1134 p_select_rec.gl_date );
1135
1136 dbms_sql.bind_variable( p_insert_ps_c,
1137 'trx_number',
1138 p_select_rec.trx_number );
1139
1140 dbms_sql.bind_variable( p_insert_ps_c,
1141 'exchange_rate_type',
1142 p_select_rec.exchange_rate_type );
1143
1144 dbms_sql.bind_variable( p_insert_ps_c,
1145 'exchange_rate',
1146 p_select_rec.exchange_rate );
1147
1148 dbms_sql.bind_variable( p_insert_ps_c,
1149 'exchange_date',
1150 p_select_rec.exchange_date );
1151
1152 dbms_sql.bind_variable( p_insert_ps_c,
1153 'number_of_due_dates',
1154 p_number_of_due_dates );
1155
1156 dbms_sql.bind_variable( p_insert_ps_c,
1157 'reversed_cash_receipt_id',
1158 p_select_rec.reversed_cash_receipt_id );
1159
1160 --anuj
1161 dbms_sql.bind_variable( p_insert_ps_c,
1162 'org_id',
1163 arp_standard.sysparm.org_id /* SSA changes anuj */ );
1164
1165 EXCEPTION
1166 WHEN OTHERS THEN
1167 debug( 'EXCEPTION: Error in binding rule_insert_dist_c',
1168 MSG_LEVEL_BASIC );
1169 RAISE;
1170 END;
1171
1172 -------------------------------------------------------------
1173 -- Execute
1174 -------------------------------------------------------------
1175 BEGIN
1176 debug( ' Inserting payment schedules', MSG_LEVEL_DEBUG );
1177 l_ignore := dbms_sql.execute( p_insert_ps_c );
1178 debug( to_char(l_ignore) || ' row(s) inserted',
1179 MSG_LEVEL_DEBUG );
1180
1181 /* Call JL for locking the invoice for approval if JL is installed.
1182 l_jgzz_product_code := sys_context('JG','JGZZ_PRODUCT_CODE');*/
1183 l_jgzz_product_code := AR_GDF_VALIDATION.is_jg_installed;
1184
1185 IF (l_jgzz_product_code IS NOT NULL) AND (l_ignore > 0) THEN
1186 /* JL_BR_SPED_PKG package is installed, so OK to call the package. */
1187 BEGIN
1188 JL_BR_SPED_PKG.SET_TRX_LOCK_STATUS(p_select_rec.customer_trx_id);
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 IF PG_DEBUG in ('Y', 'C') THEN
1192 arp_standard.debug('LOCK_INVOICE_FROM_WORKBENCH: Exception calling BEGIN JL_BR_SPED_PKG.LOCK_INVOICE_FROM_WORKBENCH.');
1193 arp_standard.debug('LOCK_INVOICE_FROM_WORKBENCH: ' || SQLERRM);
1194 END IF;
1195 END;
1196 END IF;
1197
1198 /*-------------------------------------------+
1199 | Call central MRC library for insertion |
1200 | into MRC tables |
1201 +-------------------------------------------*/
1202
1203 ar_mrc_engine.maintain_mrc_data(
1204 p_event_mode => 'INSERT',
1205 p_table_name => 'AR_PAYMENT_SCHEDULES',
1206 p_mode => 'SINGLE',
1207 p_key_value => l_ps_id);
1208
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211 debug( 'EXCEPTION: Error executing insert ps stmt',
1212 MSG_LEVEL_BASIC );
1213 RAISE;
1214 END;
1215
1216 END LOOP;
1217
1218
1219 print_fcn_label2('arp_maintain_ps2.process_ips_data()-' );
1220
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 debug( 'EXCEPTION: arp_maintain_ps2.process_ips_data()',
1224 MSG_LEVEL_BASIC );
1225 RAISE;
1226 END process_ips_data;
1227
1228
1229 ----------------------------------------------------------------------------
1230 PROCEDURE insert_inv_ps_private(
1231 p_system_info IN arp_trx_global.system_info_rec_type,
1232 p_profile_info IN arp_trx_global.profile_rec_type,
1233 p_customer_trx_id IN BINARY_INTEGER,
1234 p_reversed_cash_receipt_id IN BINARY_INTEGER ) IS
1235
1236 l_ignore INTEGER;
1237 l_old_trx_id BINARY_INTEGER;
1238 l_customer_trx_id BINARY_INTEGER;
1239
1240 l_terms_sequence_num_t number_table_type;
1241 l_percent_t number_table_type;
1242 l_line_amount_t number_table_type;
1243 l_tax_amount_t number_table_type;
1244 l_freight_amount_t number_table_type;
1245 l_charges_amount_t number_table_type;
1246 l_acctd_amt_due_rem_t number_table_type;
1247
1248 l_due_date_t date_table_type;
1249 l_table_index BINARY_INTEGER := 0;
1250
1251 l_select_rec select_ips_rec_type;
1252
1253
1254
1255 PROCEDURE load_tables( p_select_rec IN select_ips_rec_type ) IS
1256
1257 BEGIN
1258 print_fcn_label2('arp_maintain_ps2.load_tables()+' );
1259
1260 l_terms_sequence_num_t( l_table_index ) :=
1261 p_select_rec.term_sequence_num;
1262 l_percent_t( l_table_index ) := p_select_rec.percent;
1263 l_due_date_t( l_table_index ) := p_select_rec.due_date;
1264 l_table_index := l_table_index + 1;
1265
1266 print_fcn_label2('arp_maintain_ps2.load_tables()-' );
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
1271 MSG_LEVEL_BASIC );
1272 RAISE;
1273 END load_tables;
1274
1275
1276 PROCEDURE clear_tables IS
1277
1278 BEGIN
1279 print_fcn_label2('arp_maintain_ps2.clear_tables()+' );
1280
1281
1282 l_line_amount_t := null_number_t;
1283 l_tax_amount_t := null_number_t;
1284 l_freight_amount_t := null_number_t;
1285 l_charges_amount_t := null_number_t;
1286 l_acctd_amt_due_rem_t := null_number_t;
1287 l_terms_sequence_num_t := null_number_t;
1288 l_percent_t := null_number_t;
1289
1290 l_due_date_t := null_date_t;
1291
1292 l_table_index := 0;
1293
1294 print_fcn_label2('arp_maintain_ps2.clear_tables()-' );
1295
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298 debug( 'EXCEPTION: arp_maintain_ps2.clear_tables()',
1299 MSG_LEVEL_BASIC );
1300 RAISE;
1301 END clear_tables;
1302
1303 BEGIN
1304
1305 print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()+' );
1306
1307 --
1308 clear_tables;
1309 --
1310 IF( NOT( dbms_sql.is_open( ips_select_c ) AND
1311 dbms_sql.is_open( ips_insert_ps_c ) )) THEN
1312
1313 build_ips_sql( system_info,
1314 profile_info,
1315 ips_select_c,
1316 ips_insert_ps_c );
1317 END IF;
1318
1319 --
1320 define_ips_select_columns( ips_select_c, l_select_rec );
1321
1322 ---------------------------------------------------------------
1323 -- Bind variables
1324 ---------------------------------------------------------------
1325 dbms_sql.bind_variable( ips_select_c,
1326 'customer_trx_id',
1327 p_customer_trx_id );
1328
1329 ---------------------------------------------------------------
1330 -- Execute sql
1331 ---------------------------------------------------------------
1332 debug( ' Executing select sql', MSG_LEVEL_DEBUG );
1333
1334 BEGIN
1335 l_ignore := dbms_sql.execute( ips_select_c );
1336
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 debug( 'EXCEPTION: Error executing select sql',
1340 MSG_LEVEL_BASIC );
1341 RAISE;
1342 END;
1343
1344
1345 ---------------------------------------------------------------
1346 -- Fetch rows
1347 ---------------------------------------------------------------
1348 BEGIN
1349 LOOP
1350
1351 IF dbms_sql.fetch_rows( ips_select_c ) > 0 THEN
1352
1353 debug(' Fetched a row', MSG_LEVEL_DEBUG );
1354
1355 -----------------------------------------------
1356 -- Load row into record
1357 -----------------------------------------------
1358 dbms_sql.column_value( ips_select_c, 1, l_customer_trx_id );
1359
1360 IF( l_old_trx_id IS NULL OR
1361 l_customer_trx_id <> l_old_trx_id ) THEN
1362
1363 IF( l_old_trx_id IS NOT NULL ) THEN
1364
1365 process_ips_data(
1366 system_info,
1367 profile_info,
1368 ips_insert_ps_c,
1369 l_select_rec,
1370 l_table_index,
1371 l_percent_t,
1372 l_terms_sequence_num_t,
1373 l_due_date_t,
1374 l_line_amount_t,
1375 l_tax_amount_t,
1376 l_freight_amount_t,
1377 l_charges_amount_t,
1378 l_acctd_amt_due_rem_t );
1379 END IF;
1380
1381 l_old_trx_id := l_customer_trx_id;
1382
1383 clear_tables;
1384
1385
1386 END IF;
1387
1388 get_ips_column_values( ips_select_c, l_select_rec );
1389
1390 /* Bug3328690 Check included for reversed_cash_receipt_id.
1391 If it is created for the first time then p_reversed_cash_receipt_id
1392 will not be null.During Completion of DM reversal p_reversed_cash_receipt
1393 will be null */
1394
1395 IF p_reversed_cash_receipt_id is NOT NULL THEN
1396 l_select_rec.reversed_cash_receipt_id :=
1397 p_reversed_cash_receipt_id;
1398 END IF;
1399
1400 dump_ips_select_rec( l_select_rec );
1401
1402 load_tables( l_select_rec );
1403 -- >> dump tables
1404
1405 ELSE
1406 process_ips_data(
1407 system_info,
1408 profile_info,
1409 ips_insert_ps_c,
1410 l_select_rec,
1411 l_table_index,
1412 l_percent_t,
1413 l_terms_sequence_num_t,
1414 l_due_date_t,
1415 l_line_amount_t,
1416 l_tax_amount_t,
1417 l_freight_amount_t,
1418 l_charges_amount_t,
1419 l_acctd_amt_due_rem_t );
1420 EXIT;
1421 END IF;
1422
1423
1424 END LOOP;
1425
1426 EXCEPTION
1427 WHEN OTHERS THEN
1428 debug( 'EXCEPTION: Error fetching select cursor',
1429 MSG_LEVEL_BASIC );
1430 RAISE;
1431
1432 END;
1433
1434 print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()-' );
1435
1436 EXCEPTION
1437 WHEN OTHERS THEN
1438 debug( 'EXCEPTION: arp_maintain_ps2.insert_inv_ps_private()',
1439 MSG_LEVEL_BASIC );
1440 RAISE;
1441
1442 END insert_inv_ps_private;
1443
1444
1445 ----------------------------------------------------------------------------
1446 --
1447 -- PROCEDURE NAME: build_ira_sql
1448 --
1449 -- DECSRIPTION:
1450 --
1451 -- ARGUMENTS:
1452 -- IN:
1453 -- system_info
1454 -- profile_info
1455 --
1456 -- IN/OUT:
1457 -- select_c
1458 -- insert_ps_c
1459 -- insert_ra_c
1460 -- update_ps_c
1461 --
1462 -- OUT:
1463 --
1464 -- RETURNS:
1465 --
1466 -- NOTES:
1467 --
1468 -- HISTORY:
1469 --
1470 ----------------------------------------------------------------------------
1471 PROCEDURE build_ira_sql(
1472 p_system_info IN arp_trx_global.system_info_rec_type,
1473 p_profile_info IN arp_trx_global.profile_rec_type,
1474 p_select_c IN OUT NOCOPY INTEGER,
1475 p_insert_ps_c IN OUT NOCOPY INTEGER,
1476 p_insert_ra_c IN OUT NOCOPY INTEGER,
1477 p_update_ps_c IN OUT NOCOPY INTEGER ) IS
1478
1479 l_insert_ps_sql VARCHAR2(3000);
1480 l_insert_ra_sql VARCHAR2(3000);
1481 l_update_ps_sql VARCHAR2(2000);
1482 l_select_sql VARCHAR2(6000);
1483
1484
1485 BEGIN
1486
1487 print_fcn_label( 'arp_maintain_ps2.build_ira_sql()+' );
1488
1489 ------------------------------------------------
1490 -- Select sql
1491 ------------------------------------------------
1492 l_select_sql :=
1493 'SELECT
1494 /* null, */
1495 nvl(ct.trx_date, sysdate),
1496 ct.bill_to_customer_id,
1497 ct.cust_trx_type_id,
1498 ctl.customer_trx_id,
1499 ct.invoice_currency_code,
1500 sum(decode(ctl.line_type,
1501 ''LINE'', ctl.extended_amount, 0)) /
1502 (count(distinct nvl(ra.receivable_application_id, -9.9)) *
1503 count(distinct nvl(adj.adjustment_id, -9.9))),
1504 sum(decode(ctl.line_type, ''FREIGHT'', ctl.extended_amount, 0)) /
1505 (count(distinct nvl(ra.receivable_application_id, -9.9)) *
1506 count(distinct nvl(adj.adjustment_id, -9.9))),
1507 sum(decode(ctl.line_type, ''TAX'', ctl.extended_amount, 0)) /
1508 (count(distinct nvl(ra.receivable_application_id, -9.9)) *
1509 count(distinct nvl(adj.adjustment_id, -9.9))),
1510 /* null, */
1511 ct.exchange_rate_type,
1512 ct.exchange_rate,
1513 ct.exchange_date,
1514 ct.trx_number,
1515 nvl(ctlgd.gl_date, ct.trx_date),
1516 ctlgd_inv.code_combination_id,
1517 ps.customer_trx_id,
1518 ps.payment_schedule_id,
1519 greatest(nvl(max(decode(ra.confirmed_flag,
1520 ''Y'', ra.gl_date,
1521 null, decode(ra.receivable_application_id,
1522 null, nvl(ctlgd.gl_date,
1523 ct.trx_date),
1524 ra.gl_date),
1525 nvl(ctlgd.gl_date, ct.trx_date))),
1526 nvl(ctlgd.gl_date, ct.trx_date)),
1527 nvl(max(decode(adj.status,
1528 ''A'',adj.gl_date,
1529 nvl(ctlgd.gl_date,ct.trx_date))),
1530 nvl(ctlgd.gl_date,ct.trx_date)),
1531 nvl(ctlgd.gl_date, ct.trx_date)),
1532 greatest(nvl(max(decode(ra.confirmed_flag,
1533 ''Y'', ra.apply_date,
1534 null, decode(ra.receivable_application_id,
1535 null, ct.trx_date,
1536 ra.apply_date),
1537 ct.trx_date)),
1538 ct.trx_date),
1539 nvl(max(decode(adj.status,
1540 ''A'',adj.apply_date,
1541 ct.trx_date)),
1542 ct.trx_date),
1543 ct.trx_date),
1544 c.precision,
1545 nvl(ps.amount_line_items_remaining,0),
1546 nvl(ps.freight_remaining,0),
1547 nvl(ps.tax_remaining,0),
1548 ct.bill_to_site_use_id,
1549 /* 0, */
1550 /* 0, */
1551 /* 0, */
1552 c.minimum_accountable_unit,
1553 ctt.post_to_gl,
1554 /* nvl(ctlgd.gl_date,ct.trx_date), */
1555 ct.credit_method_for_installments,
1556 nvl(ps.amount_credited,0),
1557 ps.amount_due_remaining,
1558 ps.acctd_amount_due_remaining,
1559 /* null, */
1560 /* null, */
1561 c_inv.precision,
1562 c_inv.minimum_accountable_unit,
1563 ct_inv.exchange_rate,
1564 ctlgd.acctd_amount,
1565 /* null, */
1566 /* null, */
1567 /* null, */
1568 /* null, */
1569 sum(decode(ctl.line_type, ''CHARGES'', ctl.extended_amount, 0)) /
1570 (count(distinct nvl(ra.receivable_application_id, -9.9)) *
1571 count(distinct nvl(adj.adjustment_id, -9.9))),
1572 /* null, */
1573 nvl(ps.receivables_charges_remaining,0)
1574 /* , 0 */
1575 FROM
1576 ar_receivable_applications ra,
1577 ar_payment_schedules ps,
1578 ar_adjustments adj,
1579 ra_cust_trx_types ctt,
1580 ra_cust_trx_line_gl_dist ctlgd,
1581 ra_customer_trx ct,
1582 fnd_currencies c,
1583 fnd_currencies c_inv,
1584 ra_customer_trx ct_inv,
1585 ra_cust_trx_line_gl_dist ctlgd_inv,
1586 ra_customer_trx_lines ctl
1587 WHERE ct.customer_trx_id = :customer_trx_id
1588 and ctl.customer_trx_id = ct.customer_trx_id
1589 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1590 AND ctt.type = ''CM''
1591 AND ctt.accounting_affect_flag = ''Y''
1592 AND ctl.previous_customer_trx_id = ps.customer_trx_id
1593 AND ps.customer_trx_id = ra.applied_customer_trx_id (+)
1594 AND ps.customer_trx_id = adj.customer_trx_id (+)
1595 AND c.currency_code = ct.invoice_currency_code
1596 AND ct.customer_trx_id = ctlgd.customer_trx_id
1597 AND ctlgd.account_class = ''REC''
1598 AND ctlgd.latest_rec_flag = ''Y''
1599 AND ps.customer_trx_id = ct_inv.customer_trx_id
1600 AND ct_inv.customer_trx_id = ctlgd_inv.customer_trx_id
1601 AND ctlgd_inv.account_class = ''REC''
1602 AND ctlgd_inv.latest_rec_flag= ''Y''
1603 AND ct_inv.invoice_currency_code = c_inv.currency_code
1604 GROUP BY
1605 ctl.customer_trx_id,
1606 ct.trx_date,
1607 ct.bill_to_customer_id,
1608 ct.cust_trx_type_id,
1609 ct.invoice_currency_code,
1610 ct.exchange_rate_type,
1611 ct.exchange_rate,
1612 ct.exchange_date,
1613 ct.trx_number,
1614 nvl(ctlgd.gl_date, ct.trx_date),
1615 ctlgd_inv.code_combination_id,
1616 ps.customer_trx_id,
1617 ps.payment_schedule_id,
1618 ps.freight_remaining,
1619 ps.tax_remaining,
1620 ps.amount_line_items_remaining,
1621 ps.gl_date,
1622 ra.applied_customer_trx_id,
1623 adj.customer_trx_id,
1624 ct.bill_to_site_use_id,
1625 c.precision,
1626 c.minimum_accountable_unit,
1627 ctlgd.gl_date,
1628 ct.trx_date,
1629 ct.credit_method_for_installments,
1630 ctt.post_to_gl,
1631 ps.amount_credited,
1632 c_inv.precision,
1633 c_inv.minimum_accountable_unit,
1634 ct_inv.exchange_rate,
1635 ctlgd.acctd_amount,
1636 ps.terms_sequence_number,
1637 ps.amount_due_remaining,
1638 ps.receivables_charges_remaining,
1639 ps.acctd_amount_due_remaining
1640 ORDER BY
1641 ps.customer_trx_id asc,
1642 ctl.customer_trx_id asc,
1643 ps.terms_sequence_number';
1644
1645
1646 debug(' select_sql = ' || CRLF ||
1647 l_select_sql || CRLF,
1648 MSG_LEVEL_DEBUG);
1649 debug(' len(select_sql) = '||
1650 to_char(length(l_select_sql)) || CRLF,
1651 MSG_LEVEL_DEBUG);
1652
1653
1654 ------------------------------------------------
1655 -- Insert ps sql
1656 ------------------------------------------------
1657 l_insert_ps_sql :=
1658 'INSERT INTO AR_PAYMENT_SCHEDULES
1659 (
1660 created_by,
1661 creation_date,
1662 last_updated_by,
1663 last_update_date,
1664 last_update_login,
1665 request_id,
1666 program_application_id,
1667 program_id,
1668 program_update_date,
1669 payment_schedule_id,
1670 customer_trx_id,
1671 amount_due_original,
1672 amount_due_remaining,
1673 acctd_amount_due_remaining,
1674 amount_line_items_original,
1675 amount_line_items_remaining,
1676 tax_original,
1677 tax_remaining,
1678 freight_original,
1679 freight_remaining,
1680 receivables_charges_charged,
1681 receivables_charges_remaining,
1682 amount_credited,
1683 amount_applied,
1684 term_id,
1685 terms_sequence_number,
1686 due_date,
1687 customer_id,
1688 class,
1689 customer_site_use_id,
1690 cust_trx_type_id,
1691 number_of_due_dates,
1692 status,
1693 invoice_currency_code,
1694 actual_date_closed,
1695 exchange_rate_type,
1696 exchange_rate,
1697 exchange_date,
1698 trx_number,
1699 trx_date,
1700 gl_date_closed,
1701 gl_date
1702 ,org_id
1703 )
1704 VALUES
1705 (
1706 :user_id, /* created_by */
1707 sysdate, /* creation_date */
1708 :user_id, /* last_updated_by */
1709 sysdate, /* last_update_date */
1710 :login_id, /* last_update_login */
1711 :request_id,
1712 decode(:application_id,
1713 -1, null, :application_id), /* program_application_id */
1714 decode(:program_id, -1, null, :program_id), /* program_id */
1715 sysdate, /* program_update_date */
1716 :payment_schedule_id, /* payment_schedule_id */
1717 :customer_trx_id,
1718 :amount_due_original,
1719 0, /* amount_due_remaining */
1720 0, /* acctd_amount_due_remaining */
1721 :amount_line_items_original,
1722 0, /* amount_line_items_remaining */
1723 :tax_original,
1724 0, /* tax_remaining */
1725 :freight_original,
1726 0, /* freight_remaining */
1727 :receivables_charges_charged,
1728 0, /* receivables_charges_remaining */
1729 0, /* amount_credited */
1730 :amount_applied,
1731 null, /* term_id */
1732 1, /* terms_sequence_number */
1733 :trx_date,
1734 :customer_id,
1735 ''CM'', /* class */
1736 :site_use_id,
1737 :cust_trx_type_id,
1738 1, /* number_of_due_dates */
1739 ''CL'', /* status */
1740 :currency_code,
1741 nvl(:trx_date, to_date(''12/31/4712'',''MM/DD/YYYY'')),
1742 :exchange_rate_type,
1743 :exchange_rate,
1744 :exchange_date,
1745 :trx_number,
1746 :trx_date,
1747 nvl(:gl_date, to_date(''12/31/4712'',''MM/DD/YYYY'')), /* gl_date_closed */
1748 :gl_date
1749 ,:org_id --arp_standard.sysparm.org_id /* SSA changes anuj */
1750 )' ;
1751
1752 debug(' insert_ps_sql = ' || CRLF ||
1753 l_insert_ps_sql || CRLF,
1754 MSG_LEVEL_DEBUG);
1755 debug(' len(insert_ps_sql) = '||
1756 to_char(length(l_insert_ps_sql)) || CRLF,
1757 MSG_LEVEL_DEBUG);
1758
1759
1760 ------------------------------------------------
1761 -- Insert ra sql
1762 ------------------------------------------------
1763 l_insert_ra_sql :=
1764 'INSERT INTO AR_RECEIVABLE_APPLICATIONS
1765 (
1766 created_by,
1767 creation_date,
1768 last_updated_by,
1769 last_update_date,
1770 request_id,
1771 program_application_id,
1772 program_id,
1773 program_update_date,
1774 last_update_login,
1775 receivable_application_id,
1776 customer_trx_id,
1777 payment_schedule_id,
1778 gl_date,
1779 code_combination_id,
1780 set_of_books_id,
1781 display,
1782 application_type,
1783 apply_date,
1784 applied_customer_trx_id,
1785 applied_payment_schedule_id,
1786 status,
1787 amount_applied,
1788 acctd_amount_applied_from,
1789 acctd_amount_applied_to,
1790 line_applied,
1791 tax_applied,
1792 freight_applied,
1793 receivables_charges_applied,
1794 application_rule,
1795 postable,
1796 posting_control_id,
1797 cash_receipt_history_id,
1798 ussgl_transaction_code
1799 ,org_id
1800 )
1801 SELECT
1802 :user_id, /* created_by */
1803 sysdate, /* creation_date */
1804 :user_id, /* last_updated_by */
1805 sysdate, /* last_update_date */
1806 :request_id,
1807 :application_id,
1808 :program_id,
1809 sysdate, /* program_update_date */
1810 :login_id, /* last_update_login */
1811 :receivable_application_id,
1812 :customer_trx_id,
1813 ps.payment_schedule_id,
1814 :gl_date,
1815 :code_combination_id,
1816 :set_of_books_id,
1817 ''Y'', /* display */
1818 ''CM'', /* application_type */
1819 :trx_date,
1820 :applied_customer_trx_id,
1821 :applied_payment_schedule_id,
1822 ''APP'', /* status */
1823 -:amount_applied,
1824 -:acctd_amount_applied_from,
1825 -:acctd_amount_applied_to,
1826 -:line_applied,
1827 -:tax_applied,
1828 -:freight_applied,
1829 -:receivables_charges_applied,
1830 67, /* application_rule */
1831 :post_to_gl_flag,
1832 -3, /* posting_control_id */
1833 null, /* cash_receipt_history_id */
1834 :ussgl_transaction_code /*Transaction code*/
1835 ,ps.org_id /* SSA changes anuj */
1836 FROM AR_PAYMENT_SCHEDULES ps
1837 /* assumes only one ps line exists for CM */
1838 WHERE :customer_trx_id = ps.customer_trx_id';
1839
1840 debug(' insert_ra_sql = ' || CRLF ||
1841 l_insert_ra_sql || CRLF,
1842 MSG_LEVEL_DEBUG);
1843 debug(' len(insert_ra_sql) = '||
1844 to_char(length(l_insert_ra_sql)) || CRLF,
1845 MSG_LEVEL_DEBUG);
1846
1847
1848 ------------------------------------------------
1849 -- Update ps sql
1850 ------------------------------------------------
1851 -- Modified the update statement to incorporate the hard-coded date if the transaction is open - For Bug:5491085
1852 l_update_ps_sql :=
1853 'UPDATE AR_PAYMENT_SCHEDULES
1854 SET
1855 last_update_date = sysdate,
1856 last_updated_by = :user_id,
1857 last_update_login = :login_id,
1858 status = decode(:amount_due_remaining, 0, ''CL'', ''OP''),
1859 gl_date_closed = decode(:amount_due_remaining, 0, :gl_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
1860 actual_date_closed =
1861 decode(:amount_due_remaining, 0, :actual_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
1862 amount_due_remaining = :amount_due_remaining,
1863 acctd_amount_due_remaining = :acctd_amount_due_remaining,
1864 amount_line_items_remaining = :amount_line_items_remaining,
1865 freight_remaining = :freight_remaining,
1866 tax_remaining = :tax_remaining,
1867 receivables_charges_remaining = :receivables_charges_remaining,
1868 amount_credited = :amount_credited
1869 WHERE payment_schedule_id = :applied_payment_schedule_id';
1870
1871 debug(' update_ps_sql = ' || CRLF ||
1872 l_update_ps_sql || CRLF,
1873 MSG_LEVEL_DEBUG);
1874 debug(' len(update_ps_sql) = '||
1875 to_char(length(l_update_ps_sql)) || CRLF,
1876 MSG_LEVEL_DEBUG);
1877
1878
1879
1880
1881 ------------------------------------------------
1882 -- Parse sql stmts
1883 ------------------------------------------------
1884 BEGIN
1885 debug( ' Parsing stmts', MSG_LEVEL_DEBUG );
1886
1887 debug( ' Parsing insert_ps_c', MSG_LEVEL_DEBUG );
1888 p_insert_ps_c := dbms_sql.open_cursor;
1889 dbms_sql.parse( p_insert_ps_c, l_insert_ps_sql,
1890 dbms_sql.v7 );
1891
1892 debug( ' Parsing insert_ra_c', MSG_LEVEL_DEBUG );
1893 p_insert_ra_c := dbms_sql.open_cursor;
1894 dbms_sql.parse( p_insert_ra_c, l_insert_ra_sql,
1895 dbms_sql.v7 );
1896
1897 debug( ' Parsing update_ps_c', MSG_LEVEL_DEBUG );
1898 p_update_ps_c := dbms_sql.open_cursor;
1899 dbms_sql.parse( p_update_ps_c, l_update_ps_sql,
1900 dbms_sql.v7 );
1901
1902 debug( ' Parsing select_c', MSG_LEVEL_DEBUG );
1903 p_select_c := dbms_sql.open_cursor;
1904 dbms_sql.parse( p_select_c, l_select_sql,
1905 dbms_sql.v7 );
1906
1907 EXCEPTION
1908 WHEN OTHERS THEN
1909 IF PG_DEBUG in ('Y', 'C') THEN
1910 arp_util.debug(SQLERRM);
1911 END IF;
1912 debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
1913 RAISE;
1914 END;
1915
1916 print_fcn_label( 'arp_maintain_ps2.build_ira_sql()-' );
1917
1918
1919 EXCEPTION
1920 WHEN OTHERS THEN
1921 debug( 'EXCEPTION: arp_maintain_ps2.build_ira_sql()',
1922 MSG_LEVEL_BASIC );
1923
1924 RAISE;
1925 END build_ira_sql;
1926
1927
1928 ----------------------------------------------------------------------------
1929 PROCEDURE define_ira_select_columns(
1930 p_select_c IN INTEGER,
1931 p_select_rec IN select_ira_rec_type ) IS
1932
1933 BEGIN
1934
1935 print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()+' );
1936
1937 dbms_sql.define_column( p_select_c, 1, p_select_rec.trx_date );
1938 dbms_sql.define_column( p_select_c, 2, p_select_rec.customer_id );
1939 dbms_sql.define_column( p_select_c, 3, p_select_rec.cust_trx_type_id );
1940 dbms_sql.define_column( p_select_c, 4, p_select_rec.customer_trx_id );
1941 dbms_sql.define_column( p_select_c, 5, p_select_rec.currency_code, 15 );
1942 dbms_sql.define_column( p_select_c, 6, p_select_rec.total_cm_line_amount );
1943 dbms_sql.define_column( p_select_c, 7,
1944 p_select_rec.total_cm_freight_amount );
1945 dbms_sql.define_column( p_select_c, 8, p_select_rec.total_cm_tax_amount );
1946 dbms_sql.define_column( p_select_c, 9,
1947 p_select_rec.exchange_rate_type, 30 );
1948 dbms_sql.define_column( p_select_c, 10, p_select_rec.exchange_rate );
1949 dbms_sql.define_column( p_select_c, 11, p_select_rec.exchange_date );
1950 dbms_sql.define_column( p_select_c, 12, p_select_rec.trx_number, 20 );
1951 dbms_sql.define_column( p_select_c, 13, p_select_rec.gl_date );
1952 dbms_sql.define_column( p_select_c, 14, p_select_rec.code_combination_id );
1953 dbms_sql.define_column( p_select_c, 15, p_select_rec.inv_customer_trx_id );
1954 dbms_sql.define_column( p_select_c, 16,
1955 p_select_rec.inv_payment_schedule_id );
1956 dbms_sql.define_column( p_select_c, 17, p_select_rec.gl_date_closed );
1957 dbms_sql.define_column( p_select_c, 18, p_select_rec.actual_date_closed );
1958 dbms_sql.define_column( p_select_c, 19, p_select_rec.precision );
1959 dbms_sql.define_column( p_select_c, 20, p_select_rec.inv_line_remaining );
1960 dbms_sql.define_column( p_select_c, 21,
1961 p_select_rec.inv_freight_remaining );
1962 dbms_sql.define_column( p_select_c, 22, p_select_rec.inv_tax_remaining );
1963 dbms_sql.define_column( p_select_c, 23, p_select_rec.site_use_id );
1964 dbms_sql.define_column( p_select_c, 24,
1965 p_select_rec.min_acc_unit );
1966 dbms_sql.define_column( p_select_c, 25,
1967 p_select_rec.post_to_gl_flag, 1 );
1968 dbms_sql.define_column( p_select_c, 26,
1969 p_select_rec.credit_method, 30 );
1970 dbms_sql.define_column( p_select_c, 27,
1971 p_select_rec.inv_amount_credited );
1972 dbms_sql.define_column( p_select_c, 28,
1973 p_select_rec.inv_amount_due_remaining );
1974 dbms_sql.define_column( p_select_c, 29,
1975 p_select_rec.inv_acctd_amt_due_rem );
1976 dbms_sql.define_column( p_select_c, 30, p_select_rec.inv_precision );
1977 dbms_sql.define_column( p_select_c, 31,
1978 p_select_rec.inv_min_acc_unit );
1979 dbms_sql.define_column( p_select_c, 32, p_select_rec.inv_exchange_rate );
1980 dbms_sql.define_column( p_select_c, 33, p_select_rec.rec_acctd_amount );
1981 dbms_sql.define_column( p_select_c, 34,
1982 p_select_rec.total_cm_charges_amount );
1983 dbms_sql.define_column( p_select_c, 35,
1984 p_select_rec.inv_charges_remaining );
1985
1986
1987 print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()-' );
1988
1989 EXCEPTION
1990 WHEN OTHERS THEN
1991 debug('EXCEPTION: arp_maintain_ps2.define_ira_select_columns()',
1992 MSG_LEVEL_BASIC);
1993 RAISE;
1994 END define_ira_select_columns;
1995
1996
1997 ----------------------------------------------------------------------------
1998 PROCEDURE get_ira_column_values( p_select_c IN INTEGER,
1999 p_select_rec IN OUT NOCOPY select_ira_rec_type ) IS
2000 /* Bug 460927 - Modified IN to IN OUT oin the above line - oracle 8 */
2001 BEGIN
2002 print_fcn_label2( 'arp_maintain_ps2.get_ira_column_values()+' );
2003
2004 dbms_sql.column_value( p_select_c, 1, p_select_rec.trx_date );
2005 dbms_sql.column_value( p_select_c, 2, p_select_rec.customer_id );
2006 dbms_sql.column_value( p_select_c, 3, p_select_rec.cust_trx_type_id );
2007 dbms_sql.column_value( p_select_c, 4, p_select_rec.customer_trx_id );
2008 dbms_sql.column_value( p_select_c, 5, p_select_rec.currency_code );
2009 dbms_sql.column_value( p_select_c, 6, p_select_rec.total_cm_line_amount );
2010 dbms_sql.column_value( p_select_c, 7,
2011 p_select_rec.total_cm_freight_amount );
2012 dbms_sql.column_value( p_select_c, 8, p_select_rec.total_cm_tax_amount );
2013 dbms_sql.column_value( p_select_c, 9,
2014 p_select_rec.exchange_rate_type );
2015 dbms_sql.column_value( p_select_c, 10, p_select_rec.exchange_rate );
2016 dbms_sql.column_value( p_select_c, 11, p_select_rec.exchange_date );
2017 dbms_sql.column_value( p_select_c, 12, p_select_rec.trx_number );
2018 dbms_sql.column_value( p_select_c, 13, p_select_rec.gl_date );
2019 dbms_sql.column_value( p_select_c, 14, p_select_rec.code_combination_id );
2020 dbms_sql.column_value( p_select_c, 15, p_select_rec.inv_customer_trx_id );
2021 dbms_sql.column_value( p_select_c, 16,
2022 p_select_rec.inv_payment_schedule_id );
2023 dbms_sql.column_value( p_select_c, 17, p_select_rec.gl_date_closed );
2024 dbms_sql.column_value( p_select_c, 18, p_select_rec.actual_date_closed );
2025 dbms_sql.column_value( p_select_c, 19, p_select_rec.precision );
2026 dbms_sql.column_value( p_select_c, 20, p_select_rec.inv_line_remaining );
2027 dbms_sql.column_value( p_select_c, 21,
2028 p_select_rec.inv_freight_remaining );
2029 dbms_sql.column_value( p_select_c, 22, p_select_rec.inv_tax_remaining );
2030 dbms_sql.column_value( p_select_c, 23, p_select_rec.site_use_id );
2031 dbms_sql.column_value( p_select_c, 24,
2032 p_select_rec.min_acc_unit );
2033 dbms_sql.column_value( p_select_c, 25,
2034 p_select_rec.post_to_gl_flag );
2035 dbms_sql.column_value( p_select_c, 26,
2036 p_select_rec.credit_method );
2037 dbms_sql.column_value( p_select_c, 27,
2038 p_select_rec.inv_amount_credited );
2039 dbms_sql.column_value( p_select_c, 28,
2040 p_select_rec.inv_amount_due_remaining );
2041 dbms_sql.column_value( p_select_c, 29,
2042 p_select_rec.inv_acctd_amt_due_rem );
2043 dbms_sql.column_value( p_select_c, 30, p_select_rec.inv_precision );
2044 dbms_sql.column_value( p_select_c, 31,
2045 p_select_rec.inv_min_acc_unit );
2046 dbms_sql.column_value( p_select_c, 32, p_select_rec.inv_exchange_rate );
2047 dbms_sql.column_value( p_select_c, 33, p_select_rec.rec_acctd_amount );
2048 dbms_sql.column_value( p_select_c, 34,
2049 p_select_rec.total_cm_charges_amount );
2050 dbms_sql.column_value( p_select_c, 35,
2051 p_select_rec.inv_charges_remaining );
2052
2053
2054 print_fcn_label2( 'arp_maintain_ps2.get_ira_column_values()-' );
2055 EXCEPTION
2056 WHEN OTHERS THEN
2057 debug('EXCEPTION: arp_maintain_ps2.get_ira_column_values()',
2058 MSG_LEVEL_BASIC);
2059 RAISE;
2060 END get_ira_column_values;
2061
2062
2063 ----------------------------------------------------------------------------
2064 --
2065 -- PROCEDURE NAME: dump_ira_select_rec
2066 --
2067 -- DECSRIPTION:
2068 --
2069 -- ARGUMENTS:
2070 -- IN:
2071 -- select_rec
2072 --
2073 -- IN/OUT:
2074 --
2075 -- OUT:
2076 --
2077 -- RETURNS:
2078 --
2079 -- NOTES:
2080 --
2081 -- HISTORY:
2082 --
2083 ----------------------------------------------------------------------------
2084 PROCEDURE dump_ira_select_rec( p_select_rec IN select_ira_rec_type ) IS
2085 BEGIN
2086
2087 print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()+' );
2088
2089 debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
2090 debug( ' customer_trx_id='
2091 || p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
2092 debug( ' trx_number='
2093 || p_select_rec.trx_number, MSG_LEVEL_DEBUG );
2094 debug( ' cust_trx_type_id='
2095 || p_select_rec.cust_trx_type_id, MSG_LEVEL_DEBUG );
2096 debug( ' post_to_gl_flag='
2097 || p_select_rec.post_to_gl_flag, MSG_LEVEL_DEBUG );
2098 debug( ' credit_method='
2099 || p_select_rec.credit_method, MSG_LEVEL_DEBUG );
2100 debug( ' trx_date='
2101 || p_select_rec.trx_date, MSG_LEVEL_DEBUG );
2102 debug( ' gl_date='
2103 || p_select_rec.gl_date, MSG_LEVEL_DEBUG );
2104 debug( ' customer_id='
2105 || p_select_rec.customer_id, MSG_LEVEL_DEBUG );
2106 debug( ' site_use_id='
2107 || p_select_rec.site_use_id, MSG_LEVEL_DEBUG );
2108 debug( ' currency_code='
2109 || p_select_rec.currency_code, MSG_LEVEL_DEBUG );
2110 debug( ' precision='
2111 || p_select_rec.precision, MSG_LEVEL_DEBUG );
2112 debug( ' min_acc_unit='
2113 || p_select_rec.min_acc_unit, MSG_LEVEL_DEBUG );
2114 debug( ' exchange_rate_type='
2115 || p_select_rec.exchange_rate_type, MSG_LEVEL_DEBUG );
2116 debug( ' exchange_rate='
2117 || p_select_rec.exchange_rate, MSG_LEVEL_DEBUG );
2118 debug( ' exchange_date='
2119 || p_select_rec.exchange_date, MSG_LEVEL_DEBUG );
2120 debug( ' rec_acctd_amount='
2121 || p_select_rec.rec_acctd_amount, MSG_LEVEL_DEBUG );
2122 debug( ' total_cm_line_amount='
2123 || p_select_rec.total_cm_line_amount, MSG_LEVEL_DEBUG );
2124 debug( ' total_cm_tax_amount='
2125 || p_select_rec.total_cm_tax_amount, MSG_LEVEL_DEBUG );
2126 debug( ' total_cm_freight_amount='
2127 || p_select_rec.total_cm_freight_amount, MSG_LEVEL_DEBUG );
2128 debug( ' total_cm_charges_amount='
2129 || p_select_rec.total_cm_charges_amount, MSG_LEVEL_DEBUG );
2130 debug( ' code_combination_id='
2131 || p_select_rec.code_combination_id, MSG_LEVEL_DEBUG );
2132 debug( ' gl_date_closed='
2133 || p_select_rec.gl_date_closed, MSG_LEVEL_DEBUG );
2134 debug( ' actual_date_closed='
2135 || p_select_rec.actual_date_closed, MSG_LEVEL_DEBUG );
2136 debug( ' inv_customer_trx_id='
2137 || p_select_rec.inv_customer_trx_id, MSG_LEVEL_DEBUG );
2138 debug( ' inv_precision='
2139 || p_select_rec.inv_precision, MSG_LEVEL_DEBUG );
2140 debug( ' inv_min_acc_unit='
2141 || p_select_rec.inv_min_acc_unit, MSG_LEVEL_DEBUG );
2142 debug( ' inv_exchange_rate='
2143 || p_select_rec.inv_exchange_rate, MSG_LEVEL_DEBUG );
2144 debug( ' inv_payment_schedule_id='
2145 || p_select_rec.inv_payment_schedule_id, MSG_LEVEL_DEBUG );
2146 debug( ' inv_amount_due_remaining='
2147 || p_select_rec.inv_amount_due_remaining, MSG_LEVEL_DEBUG );
2148 debug( ' inv_acctd_amt_due_rem='
2149 || p_select_rec.inv_acctd_amt_due_rem, MSG_LEVEL_DEBUG );
2150 debug( ' inv_line_remaining='
2151 || p_select_rec.inv_line_remaining, MSG_LEVEL_DEBUG );
2152 debug( ' inv_tax_remaining='
2153 || p_select_rec.inv_tax_remaining, MSG_LEVEL_DEBUG );
2154 debug( ' inv_freight_remaining='
2155 || p_select_rec.inv_freight_remaining, MSG_LEVEL_DEBUG );
2156 debug( ' inv_charges_remaining='
2157 || p_select_rec.inv_charges_remaining, MSG_LEVEL_DEBUG );
2158 debug( ' inv_amount_credited='
2159 || p_select_rec.inv_amount_credited, MSG_LEVEL_DEBUG );
2160
2161 print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()-' );
2162
2163 EXCEPTION
2164 WHEN OTHERS THEN
2165 debug( 'EXCEPTION: arp_maintain_ps2.dump_ira_select_rec()',
2166 MSG_LEVEL_BASIC );
2167 RAISE;
2168 END dump_ira_select_rec;
2169
2170
2171 ------------------------------------------------------------------------
2172
2173 PROCEDURE distribute_fifo_lifo(
2174 p_direction IN NUMBER, -- 1 for FIFO, -1 for LIFO
2175 p_start_index IN NUMBER,
2176 p_end_index IN NUMBER,
2177 p_cm_amount IN NUMBER,
2178 p_inv_rem_t IN OUT NOCOPY number_table_type,
2179 p_cm_applied_amt_t IN OUT NOCOPY number_table_type ) IS
2180
2181
2182 l_cm_rem NUMBER;
2183 l_cm_rem_sign NUMBER;
2184 l_inv_rem NUMBER;
2185 l_inv_rem_sign NUMBER;
2186 l_sum NUMBER;
2187
2188 l_index NUMBER;
2189 l_high_index NUMBER;
2190
2191 BEGIN
2192 print_fcn_label2('arp_maintain_ps2.distribute_fifo_lifo()+' );
2193
2194 debug( ' p_direction='||p_direction, MSG_LEVEL_DEBUG );
2195 debug( ' p_start_index='||p_start_index, MSG_LEVEL_DEBUG );
2196 debug( ' p_end_index='||p_end_index, MSG_LEVEL_DEBUG );
2197 debug( ' p_cm_amount='||p_cm_amount, MSG_LEVEL_DEBUG );
2198
2199 l_cm_rem := p_cm_amount;
2200
2201 l_index := p_start_index;
2202
2203
2204 WHILE( TRUE ) LOOP
2205 debug( ' l_index='||l_index, MSG_LEVEL_DEBUG );
2206 debug( ' l_cm_rem='||l_cm_rem, MSG_LEVEL_DEBUG );
2207
2208 -------------------------------------------------------------------
2209 -- Get cm sign
2210 -------------------------------------------------------------------
2211 l_cm_rem_sign := SIGN( l_cm_rem );
2212
2213 -------------------------------------------------------------------
2214 -- Get inv rem for current line
2215 -------------------------------------------------------------------
2216 l_inv_rem := p_inv_rem_t( l_index );
2217 l_inv_rem_sign := SIGN( l_inv_rem );
2218
2219 IF( l_cm_rem_sign = 0 OR
2220 l_cm_rem_sign = l_inv_rem_sign ) THEN
2221
2222 ---------------------------------------------------------------
2223 -- CM amount is zero or line overapplication
2224 -- do not apply
2225 ---------------------------------------------------------------
2226 p_cm_applied_amt_t( l_index ) := 0;
2227
2228 ELSE
2229
2230 l_sum := l_inv_rem + l_cm_rem;
2231
2232 IF( SIGN(l_sum) = SIGN(l_inv_rem) ) THEN
2233
2234 -----------------------------------------------------------
2235 -- Full application, no more cm remaining
2236 -----------------------------------------------------------
2237 p_inv_rem_t( l_index ) := l_sum;
2238 p_cm_applied_amt_t( l_index ) := l_cm_rem;
2239 l_cm_rem := 0;
2240
2241 ELSE
2242 -----------------------------------------------------------
2243 -- Partial application
2244 -----------------------------------------------------------
2245 p_inv_rem_t( l_index ) := 0;
2246 p_cm_applied_amt_t( l_index ) := - l_inv_rem;
2247 l_cm_rem := l_sum;
2248 END IF;
2249
2250 END IF;
2251
2252 IF( l_index = p_end_index ) THEN
2253
2254 -----------------------------------------------------------
2255 -- Done
2256 -----------------------------------------------------------
2257 EXIT;
2258 END IF;
2259
2260 l_index := l_index + p_direction;
2261
2262 END LOOP;
2263
2264 -------------------------------------------------------------------
2265 -- Put any excess CM amount into the LAST array position
2266 -- (numerically highest index value)
2267 -------------------------------------------------------------------
2268 IF( l_cm_rem <> 0 ) THEN
2269
2270 IF( p_direction = 1 ) THEN
2271 -----------------------------------------------------------
2272 -- FIFO
2273 -----------------------------------------------------------
2274 l_high_index := p_end_index;
2275 ELSE
2276 -----------------------------------------------------------
2277 -- LIFO
2278 -----------------------------------------------------------
2279 l_high_index := p_start_index;
2280 END IF;
2281
2282 p_inv_rem_t( l_high_index ) :=
2283 p_inv_rem_t( l_high_index ) + l_cm_rem;
2284
2285 p_cm_applied_amt_t( l_high_index ) :=
2286 p_cm_applied_amt_t( l_high_index ) + l_cm_rem;
2287
2288 END IF;
2289
2290
2291 print_fcn_label2('arp_maintain_ps2.distribute_fifo_lifo()-' );
2292
2293 EXCEPTION
2294 WHEN OTHERS THEN
2295 debug( 'EXCEPTION: arp_maintain_ps2.distribute_fifo_lifo()',
2296 MSG_LEVEL_BASIC );
2297 RAISE;
2298
2299 END distribute_fifo_lifo;
2300
2301 ------------------------------------------------------------------------
2302
2303 PROCEDURE distribute_prorate(
2304 p_select_rec IN select_ira_rec_type,
2305 p_count IN NUMBER,
2306 p_cm_amount IN NUMBER,
2307 p_inv_rem_t IN OUT NOCOPY number_table_type,
2308 p_cm_applied_amt_t IN OUT NOCOPY number_table_type ) IS
2309
2310 l_cm_amount_sign NUMBER;
2311 l_inv_rem_sum NUMBER;
2312 l_sum NUMBER;
2313
2314 l_apply_amount NUMBER;
2315 l_excess_amount NUMBER;
2316
2317 l_percent_t number_table_type;
2318
2319
2320 -----------------------------------------------------------------------
2321 FUNCTION get_sum(
2322 p_amount_t number_table_type,
2323 p_cm_sign NUMBER )
2324 RETURN NUMBER IS
2325
2326 l_total NUMBER := 0;
2327
2328 BEGIN
2329
2330 FOR i IN 0..p_count - 1 LOOP
2331
2332 IF( sign( p_amount_t( i ) ) <> p_cm_sign ) THEN
2333 l_total := l_total + p_amount_t( i );
2334 END IF;
2335
2336 END LOOP;
2337
2338 RETURN l_total;
2339
2340 END get_sum;
2341
2342 -----------------------------------------------------------------------
2343 PROCEDURE compute_percents(
2344 p_cm_amt_sign IN NUMBER,
2345 p_amt_rem_sum IN NUMBER,
2346 p_amt_rem_t IN number_table_type,
2347 p_percent_t IN OUT NOCOPY number_table_type ) IS
2348
2349 l_amt_rem NUMBER;
2350 l_amt_rem_sign NUMBER;
2351
2352 BEGIN
2353
2354 FOR i in 0..p_count - 1 LOOP
2355
2356 l_amt_rem := p_amt_rem_t( i );
2357 l_amt_rem_sign := SIGN( l_amt_rem );
2358
2359 IF( l_amt_rem_sign = p_cm_amt_sign OR
2360 p_cm_amt_sign = 0 ) THEN
2361
2362 p_percent_t( i ) := 0;
2363
2364 ELSE
2365 IF (p_amt_rem_sum = 0) THEN
2366 p_percent_t( i ) := l_amt_rem;
2367 ELSE
2368 p_percent_t( i ) := l_amt_rem / p_amt_rem_sum;
2369 END IF;
2370 END IF;
2371
2372 END LOOP;
2373
2374 END compute_percents;
2375 -----------------------------------------------------------------------
2376
2377 BEGIN
2378 print_fcn_label2('arp_maintain_ps2.distribute_prorate()+' );
2379
2380
2381 debug( ' p_cm_amount='||p_cm_amount, MSG_LEVEL_DEBUG );
2382
2383 l_cm_amount_sign := SIGN( p_cm_amount );
2384
2385 l_inv_rem_sum := get_sum( p_inv_rem_t, l_cm_amount_sign );
2386
2387 compute_percents(
2388 l_cm_amount_sign,
2389 l_inv_rem_sum,
2390 p_inv_rem_t,
2391 l_percent_t );
2392
2393
2394
2395 ---------------------------------------------------------------
2396 -- Get balance after cm application
2397 ---------------------------------------------------------------
2398 l_sum := l_inv_rem_sum + p_cm_amount;
2399
2400 IF( SIGN( l_sum ) = SIGN( l_inv_rem_sum ) ) THEN
2401
2402 ---------------------------------------------------------------
2403 -- Full application, use entire cm amount
2404 ---------------------------------------------------------------
2405 l_apply_amount := p_cm_amount;
2406 l_excess_amount := 0;
2407
2408 ELSE
2409
2410 ---------------------------------------------------------------
2411 -- Partial application
2412 ---------------------------------------------------------------
2413 l_apply_amount := -l_inv_rem_sum;
2414 l_excess_amount := l_sum;
2415
2416 END IF;
2417
2418 distribute_amount(
2419 p_count,
2420 p_select_rec.currency_code,
2421 l_apply_amount,
2422 l_percent_t,
2423 p_cm_applied_amt_t );
2424
2425 -------------------------------------------------------------------
2426 -- Apply excess to last row
2427 -------------------------------------------------------------------
2428 IF( l_excess_amount <> 0 ) THEN
2429
2430 p_cm_applied_amt_t( p_count - 1 ) :=
2431 p_cm_applied_amt_t( p_count - 1 ) + l_excess_amount;
2432
2433 END IF;
2434
2435 --
2436 -- Update invoice remaining amounts
2437 --
2438 FOR i IN 0..p_count - 1 LOOP
2439 p_inv_rem_t( i ) := p_inv_rem_t( i ) + p_cm_applied_amt_t( i );
2440 END LOOP;
2441
2442
2443 print_fcn_label2('arp_maintain_ps2.distribute_prorate()-' );
2444
2445 EXCEPTION
2446 WHEN OTHERS THEN
2447 debug( 'EXCEPTION: arp_maintain_ps2.distribute_prorate()',
2448 MSG_LEVEL_BASIC );
2449 RAISE;
2450
2451 END distribute_prorate;
2452
2453 ------------------------------------------------------------------------
2454
2455 PROCEDURE process_ira_data(
2456 p_system_info IN arp_trx_global.system_info_rec_type,
2457 p_profile_info IN arp_trx_global.profile_rec_type,
2458 p_insert_ps_c IN INTEGER,
2459 p_insert_ra_c IN INTEGER,
2460 p_update_ps_c IN INTEGER,
2461 p_select_rec IN select_ira_rec_type,
2462 p_number_records IN NUMBER,
2463 p_inv_ps_id_t IN id_table_type,
2464 p_inv_amount_due_rem_t IN OUT NOCOPY number_table_type,
2465 p_inv_acctd_amt_due_rem_t IN OUT NOCOPY number_table_type,
2466 p_inv_line_rem_t IN OUT NOCOPY number_table_type,
2467 p_inv_tax_rem_t IN OUT NOCOPY number_table_type,
2468 p_inv_freight_rem_t IN OUT NOCOPY number_table_type,
2469 p_inv_charges_rem_t IN OUT NOCOPY number_table_type,
2470 p_inv_amount_credited_t IN OUT NOCOPY number_table_type,
2471 p_line_applied_t IN OUT NOCOPY number_table_type,
2472 p_tax_applied_t IN OUT NOCOPY number_table_type,
2473 p_freight_applied_t IN OUT NOCOPY number_table_type,
2474 p_charges_applied_t IN OUT NOCOPY number_table_type,
2475 p_acctd_amt_applied_from_t IN OUT NOCOPY number_table_type,
2476 p_acctd_amt_applied_to_t IN OUT NOCOPY number_table_type ) IS
2477
2478 CURSOR get_appl_info (p_rec_app_id NUMBER) IS
2479 SELECT payment_schedule_id,
2480 applied_payment_schedule_id
2481 from ar_receivable_applications
2482 where receivable_application_id = p_rec_app_id;
2483
2484 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
2485
2486 l_ignore INTEGER;
2487
2488 l_direction NUMBER; -- 1 for FIFO, -1 for LIFO
2489 l_start_index NUMBER;
2490 l_end_index NUMBER;
2491
2492 l_amount_applied NUMBER;
2493 l_new_inv_adr NUMBER;
2494 l_new_inv_acctd_adr NUMBER;
2495 l_new_acctd_amt_applied_to NUMBER;
2496
2497 l_ae_doc_rec ae_doc_rec_type;
2498 l_ps_class ar_payment_schedules.class%type;
2499 l_ps_is ar_payment_schedules.payment_schedule_id%type;
2500 l_ps_id ar_payment_schedules.payment_schedule_id%type;
2501
2502 l_ussgl_transaction_code ar_receivable_applications.ussgl_transaction_code%type;
2503
2504 BEGIN
2505 print_fcn_label2('arp_maintain_ps2.process_ira_data()+' );
2506
2507 debug( ' checking credit method', MSG_LEVEL_DEBUG );
2508
2509 IF( p_select_rec.credit_method in (FIFO, LIFO) ) THEN
2510
2511 IF( p_select_rec.credit_method = FIFO ) THEN
2512 debug( ' FIFO processing', MSG_LEVEL_DEBUG );
2513 l_start_index := 0;
2514 l_end_index := p_number_records - 1;
2515 l_direction := 1;
2516 ELSE
2517 debug( ' LIFO processing', MSG_LEVEL_DEBUG );
2518 l_start_index := p_number_records - 1;
2519 l_end_index := 0;
2520 l_direction := -1;
2521 END IF;
2522
2523 --------------------------------------------------------------------
2524 -- Distribute line amount
2525 --------------------------------------------------------------------
2526 distribute_fifo_lifo(
2527 l_direction,
2528 l_start_index,
2529 l_end_index,
2530 p_select_rec.total_cm_line_amount,
2531 p_inv_line_rem_t,
2532 p_line_applied_t );
2533
2534 --------------------------------------------------------------------
2535 -- Distribute tax amount
2536 --------------------------------------------------------------------
2537 distribute_fifo_lifo(
2538 l_direction,
2539 l_start_index,
2540 l_end_index,
2541 p_select_rec.total_cm_tax_amount,
2542 p_inv_tax_rem_t,
2543 p_tax_applied_t );
2544
2545 --------------------------------------------------------------------
2546 -- Distribute freight amount
2547 --------------------------------------------------------------------
2548 distribute_fifo_lifo(
2549 l_direction,
2550 l_start_index,
2551 l_end_index,
2552 p_select_rec.total_cm_freight_amount,
2553 p_inv_freight_rem_t,
2554 p_freight_applied_t );
2555
2556 --------------------------------------------------------------------
2557 -- Distribute charges amount
2558 --------------------------------------------------------------------
2559 distribute_fifo_lifo(
2560 l_direction,
2561 l_start_index,
2562 l_end_index,
2563 p_select_rec.total_cm_charges_amount,
2564 p_inv_charges_rem_t,
2565 p_charges_applied_t );
2566
2567
2568 ELSE -- PRORATE processing
2569
2570 debug( ' PRORATE processing', MSG_LEVEL_DEBUG );
2571
2572 --------------------------------------------------------------------
2573 -- Distribute line amount
2574 --------------------------------------------------------------------
2575 distribute_prorate(
2576 p_select_rec,
2577 p_number_records,
2578 p_select_rec.total_cm_line_amount,
2579 p_inv_line_rem_t,
2580 p_line_applied_t );
2581
2582 --------------------------------------------------------------------
2583 -- Distribute tax amount
2584 --------------------------------------------------------------------
2585 distribute_prorate(
2586 p_select_rec,
2587 p_number_records,
2588 p_select_rec.total_cm_tax_amount,
2589 p_inv_tax_rem_t,
2590 p_tax_applied_t );
2591
2592 --------------------------------------------------------------------
2593 -- Distribute freight amount
2594 --------------------------------------------------------------------
2595 distribute_prorate(
2596 p_select_rec,
2597 p_number_records,
2598 p_select_rec.total_cm_freight_amount,
2599 p_inv_freight_rem_t,
2600 p_freight_applied_t );
2601
2602 --------------------------------------------------------------------
2603 -- Distribute charges amount
2604 --------------------------------------------------------------------
2605 distribute_prorate(
2606 p_select_rec,
2607 p_number_records,
2608 p_select_rec.total_cm_charges_amount,
2609 p_inv_charges_rem_t,
2610 p_charges_applied_t );
2611
2612
2613 END IF;
2614
2615 debug( ' updating amount tables', MSG_LEVEL_DEBUG );
2616
2617 --------------------------------------------------------------------
2618 -- Update various amounts in tables
2619 --------------------------------------------------------------------
2620 FOR i IN 0..p_number_records - 1 LOOP
2621
2622 l_amount_applied := p_line_applied_t( i ) +
2623 p_tax_applied_t( i ) +
2624 p_freight_applied_t( i ) +
2625 p_charges_applied_t( i );
2626
2627 --------------------------------------------------------------------
2628 -- Update amount_credited
2629 --------------------------------------------------------------------
2630 p_inv_amount_credited_t( i ) :=
2631 p_inv_amount_credited_t( i ) + l_amount_applied ;
2632
2633
2634 --------------------------------------------------------------------
2635 -- Compute new acctd_adr (aracc)
2636 --------------------------------------------------------------------
2637 arp_util.calc_acctd_amount(
2638 p_system_info.base_currency,
2639 NULL, -- precision
2640 NULL, -- mau
2641 p_select_rec.inv_exchange_rate,
2642 '+', -- type
2643 p_inv_amount_due_rem_t( i ), -- master_from
2644 p_inv_acctd_amt_due_rem_t( i ), -- acctd_master_from
2645 l_amount_applied, -- detail
2646 l_new_inv_adr, -- master_to
2647 l_new_inv_acctd_adr, -- acctd_master_to
2648 l_new_acctd_amt_applied_to -- acctd_detail
2649 );
2650
2651 --------------------------------------------------------------------
2652 -- Update amounts
2653 --------------------------------------------------------------------
2654 p_inv_amount_due_rem_t( i ) := l_new_inv_adr;
2655 p_inv_acctd_amt_due_rem_t( i ) := l_new_inv_acctd_adr;
2656 p_acctd_amt_applied_to_t( i ) := l_new_acctd_amt_applied_to;
2657
2658 END LOOP;
2659
2660 --------------------------------------------------------------------
2661 -- Calculate acctd_amt_applied_from
2662 --------------------------------------------------------------------
2663 compute_acctd_amount(
2664 p_number_records,
2665 p_system_info.base_currency,
2666 p_select_rec.exchange_rate,
2667 p_line_applied_t,
2668 p_tax_applied_t,
2669 p_freight_applied_t,
2670 p_charges_applied_t,
2671 p_acctd_amt_applied_from_t,
2672 p_select_rec.rec_acctd_amount );
2673
2674
2675
2676 -------------------------------------------------------------
2677 -- Insert into ar_payment_schedules
2678 -------------------------------------------------------------
2679
2680 -------------------------------------------------------------
2681 -- Bind vars
2682 -------------------------------------------------------------
2683 BEGIN
2684 debug( ' Binding insert_ps_c', MSG_LEVEL_DEBUG );
2685
2686 dbms_sql.bind_variable( p_insert_ps_c,
2687 'user_id',
2688 p_profile_info.user_id );
2689
2690 dbms_sql.bind_variable( p_insert_ps_c,
2691 'login_id',
2692 p_profile_info.conc_login_id );
2693
2694 dbms_sql.bind_variable( p_insert_ps_c,
2695 'request_id',
2696 p_profile_info.request_id );
2697
2698 dbms_sql.bind_variable( p_insert_ps_c,
2699 'application_id',
2700 p_profile_info.application_id );
2701
2702 dbms_sql.bind_variable( p_insert_ps_c,
2703 'program_id',
2704 p_profile_info.conc_program_id );
2705
2706 /* added for mrc trigger elimination */
2707 SELECT ar_payment_schedules_s.nextval
2708 INTO l_ps_id
2709 FROM dual;
2710
2711 dbms_sql.bind_variable( p_insert_ps_c,
2712 'payment_schedule_id',
2713 l_ps_id );
2714
2715 dbms_sql.bind_variable( p_insert_ps_c,
2716 'customer_trx_id',
2717 p_select_rec.customer_trx_id );
2718
2719 dbms_sql.bind_variable( p_insert_ps_c,
2720 'amount_due_original',
2721 p_select_rec.total_cm_line_amount +
2722 p_select_rec.total_cm_tax_amount +
2723 p_select_rec.total_cm_freight_amount +
2724 p_select_rec.total_cm_charges_amount );
2725
2726 dbms_sql.bind_variable( p_insert_ps_c,
2727 'amount_line_items_original',
2728 p_select_rec.total_cm_line_amount );
2729
2730 dbms_sql.bind_variable( p_insert_ps_c,
2731 'tax_original',
2732 p_select_rec.total_cm_tax_amount );
2733
2734 dbms_sql.bind_variable( p_insert_ps_c,
2735 'freight_original',
2736 p_select_rec.total_cm_freight_amount );
2737
2738 dbms_sql.bind_variable( p_insert_ps_c,
2739 'receivables_charges_charged',
2740 p_select_rec.total_cm_charges_amount );
2741
2742 dbms_sql.bind_variable( p_insert_ps_c,
2743 'amount_applied',
2744 p_select_rec.total_cm_line_amount +
2745 p_select_rec.total_cm_tax_amount +
2746 p_select_rec.total_cm_freight_amount +
2747 p_select_rec.total_cm_charges_amount );
2748
2749 dbms_sql.bind_variable( p_insert_ps_c,
2750 'trx_date',
2751 p_select_rec.trx_date );
2752
2753 dbms_sql.bind_variable( p_insert_ps_c,
2754 'customer_id',
2755 p_select_rec.customer_id );
2756
2757 dbms_sql.bind_variable( p_insert_ps_c,
2758 'site_use_id',
2759 p_select_rec.site_use_id );
2760
2761 dbms_sql.bind_variable( p_insert_ps_c,
2762 'cust_trx_type_id',
2763 p_select_rec.cust_trx_type_id );
2764
2765 dbms_sql.bind_variable( p_insert_ps_c,
2766 'currency_code',
2767 p_select_rec.currency_code );
2768
2769 dbms_sql.bind_variable( p_insert_ps_c,
2770 'exchange_rate_type',
2771 p_select_rec.exchange_rate_type );
2772
2773
2774 dbms_sql.bind_variable( p_insert_ps_c,
2775 'exchange_rate',
2776 p_select_rec.exchange_rate );
2777
2778
2779 dbms_sql.bind_variable( p_insert_ps_c,
2780 'exchange_date',
2781 p_select_rec.exchange_date );
2782
2783 dbms_sql.bind_variable( p_insert_ps_c,
2784 'gl_date',
2785 p_select_rec.gl_date );
2786
2787 dbms_sql.bind_variable( p_insert_ps_c,
2788 'trx_number',
2789 p_select_rec.trx_number );
2790 --begin anuj
2791 dbms_sql.bind_variable( p_insert_ps_c,
2792 'org_id',
2793 arp_standard.sysparm.org_id /* SSA changes anuj */
2794 );
2795 --end anuj
2796
2797
2798 EXCEPTION
2799 WHEN OTHERS THEN
2800 debug( 'EXCEPTION: Error in binding insert_ps',
2801 MSG_LEVEL_BASIC );
2802 RAISE;
2803 END;
2804
2805 -------------------------------------------------------------
2806 -- Execute
2807 -------------------------------------------------------------
2808 BEGIN
2809 debug( ' Inserting CM payment schedules', MSG_LEVEL_DEBUG );
2810 l_ignore := dbms_sql.execute( p_insert_ps_c );
2811 debug( to_char(l_ignore) || ' row(s) inserted',
2812 MSG_LEVEL_DEBUG );
2813
2814 /*-------------------------------------------+
2815 | Call central MRC library for insertion |
2816 | into MRC tables |
2817 +-------------------------------------------*/
2818
2819 ar_mrc_engine.maintain_mrc_data(
2820 p_event_mode => 'INSERT',
2821 p_table_name => 'AR_PAYMENT_SCHEDULES',
2822 p_mode => 'SINGLE',
2823 p_key_value => l_ps_id);
2824
2825 EXCEPTION
2826 WHEN OTHERS THEN
2827 debug( 'EXCEPTION: Error executing insert ps stmt',
2828 MSG_LEVEL_BASIC );
2829 RAISE;
2830 END;
2831 -------------------------------------------------------------
2832 -- Insert into ar_receivable_applications
2833 -------------------------------------------------------------
2834
2835 FOR i IN 0..p_number_records - 1 LOOP
2836
2837 -------------------------------------------------------------
2838 -- Bind vars
2839 -------------------------------------------------------------
2840 BEGIN
2841 debug( ' Binding insert_ra_c', MSG_LEVEL_DEBUG );
2842
2843 /*Bug :2246098-Used a bind var for insert*/
2844
2845 select ct.default_ussgl_transaction_code into l_ussgl_transaction_code
2846 from ra_customer_trx ct
2847 WHERE ct.customer_trx_id=p_select_rec.customer_trx_id;
2848
2849 dbms_sql.bind_variable( p_insert_ra_c,
2850 'user_id',
2851 p_profile_info.user_id );
2852
2853 dbms_sql.bind_variable( p_insert_ra_c,
2854 'login_id',
2855 p_profile_info.conc_login_id );
2856
2857 dbms_sql.bind_variable( p_insert_ra_c,
2858 'request_id',
2859 p_profile_info.request_id );
2860
2861 dbms_sql.bind_variable( p_insert_ra_c,
2862 'application_id',
2863 p_profile_info.application_id );
2864
2865 dbms_sql.bind_variable( p_insert_ra_c,
2866 'program_id',
2867 p_profile_info.conc_program_id );
2868
2869
2870 dbms_sql.bind_variable( p_insert_ra_c,
2871 'customer_trx_id',
2872 p_select_rec.customer_trx_id );
2873
2874 dbms_sql.bind_variable( p_insert_ra_c,
2875 'gl_date',
2876 p_select_rec.gl_date );
2877
2878 dbms_sql.bind_variable( p_insert_ra_c,
2879 'code_combination_id',
2880 p_select_rec.code_combination_id );
2881
2882 dbms_sql.bind_variable(
2883 p_insert_ra_c,
2884 'set_of_books_id',
2885 arp_standard.sysparm.set_of_books_id );
2886
2887 dbms_sql.bind_variable( p_insert_ra_c,
2888 'trx_date',
2889 p_select_rec.trx_date );
2890
2891 dbms_sql.bind_variable( p_insert_ra_c,
2892 'applied_customer_trx_id',
2893 p_select_rec.inv_customer_trx_id );
2894
2895
2896 dbms_sql.bind_variable( p_insert_ra_c,
2897 'applied_payment_schedule_id',
2898 p_inv_ps_id_t( i ) );
2899
2900 dbms_sql.bind_variable( p_insert_ra_c,
2901 'amount_applied',
2902 p_line_applied_t( i ) +
2903 p_tax_applied_t( i ) +
2904 p_freight_applied_t( i ) +
2905 p_charges_applied_t( i ) );
2906
2907
2908 dbms_sql.bind_variable( p_insert_ra_c,
2909 'acctd_amount_applied_from',
2910 p_acctd_amt_applied_from_t( i ) );
2911
2912 dbms_sql.bind_variable( p_insert_ra_c,
2913 'acctd_amount_applied_to',
2914 p_acctd_amt_applied_to_t( i ) );
2915
2916 dbms_sql.bind_variable( p_insert_ra_c,
2917 'line_applied',
2918 p_line_applied_t( i ) );
2919
2920 dbms_sql.bind_variable( p_insert_ra_c,
2921 'tax_applied',
2922 p_tax_applied_t( i ) );
2923
2924 dbms_sql.bind_variable( p_insert_ra_c,
2925 'freight_applied',
2926 p_freight_applied_t( i ) );
2927
2928 dbms_sql.bind_variable( p_insert_ra_c,
2929 'receivables_charges_applied',
2930 p_charges_applied_t( i ) );
2931
2932 dbms_sql.bind_variable( p_insert_ra_c,
2933 'post_to_gl_flag',
2934 p_select_rec.post_to_gl_flag );
2935
2936 select ar_receivable_applications_s.nextval
2937 into l_receivable_application_id
2938 from dual;
2939
2940 dbms_sql.bind_variable( p_insert_ra_c,
2941 'receivable_application_id',
2942 l_receivable_application_id );
2943
2944 dbms_sql.bind_variable( p_insert_ra_c,
2945 'ussgl_transaction_code',
2946 l_ussgl_transaction_code );
2947
2948
2949 EXCEPTION
2950 WHEN NO_DATA_FOUND THEN
2951 debug( 'EXCEPTION: Error in selecting sequence nextval',
2952 MSG_LEVEL_BASIC );
2953 RAISE;
2954
2955 WHEN OTHERS THEN
2956 debug( 'EXCEPTION: Error in binding insert_ra_c',
2957 MSG_LEVEL_BASIC );
2958 RAISE;
2959 END;
2960
2961 -------------------------------------------------------------
2962 -- Execute
2963 -------------------------------------------------------------
2964 BEGIN
2965 debug( ' Inserting applications', MSG_LEVEL_DEBUG );
2966 l_ignore := dbms_sql.execute( p_insert_ra_c );
2967
2968 debug( to_char(l_ignore) || ' row(s) inserted',
2969 MSG_LEVEL_DEBUG );
2970
2971 FOR l_app_info IN get_appl_info(l_receivable_application_id) LOOP
2972 ar_mrc_engine3.cm_application(
2973 l_app_info.payment_schedule_id,
2974 l_app_info.applied_payment_schedule_id,
2975 NULL,
2976 l_receivable_application_id);
2977 END LOOP;
2978
2979 EXCEPTION
2980 WHEN OTHERS THEN
2981 debug( 'EXCEPTION: Error executing insert ra stmt',
2982 MSG_LEVEL_BASIC );
2983 RAISE;
2984 END;
2985
2986 --
2987 --Release 11.5 VAT changes, create APP record accounting
2988 --in ar_distributions
2989 --
2990 l_ae_doc_rec.document_type := 'CREDIT_MEMO';
2991 l_ae_doc_rec.document_id := p_select_rec.customer_trx_id;
2992 l_ae_doc_rec.accounting_entity_level := 'ONE';
2993 l_ae_doc_rec.source_table := 'RA';
2994 l_ae_doc_rec.source_id := l_receivable_application_id;
2995 l_ae_doc_rec.source_id_old := '';
2996 l_ae_doc_rec.other_flag := '';
2997
2998 --Bug 1329091 - For CM, payment schedule is updated before accounting engine call
2999 BEGIN
3000 SELECT class INTO l_ps_class
3001 FROM ar_payment_schedules
3002 WHERE customer_trx_id = p_select_rec.customer_trx_id;
3003 EXCEPTION
3004 WHEN NO_DATA_FOUND then
3005 l_ps_class := 'CM';
3006 WHEN OTHERS then
3007 NULL;
3008 END;
3009
3010 --debug('Transaction PS class '||l_ps_class ||' Customer Trx id '||to_char(p_select_rec.customer_trx_id));
3011 debug('Transaction PS class '||l_ps_class ||' Customer Trx id '||to_char(p_inv_ps_id_t( i )));
3012
3013 IF l_ps_class = 'CM' THEN
3014 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
3015 END IF;
3016 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
3017 /*bug-6976549*/
3018 arp_balance_check.CHECK_APPLN_BALANCE(l_receivable_application_id,
3019 NULL,
3020 'N');
3021
3022
3023 -------------------------------------------------------------
3024 -- Update ar_payment_schedules
3025 -------------------------------------------------------------
3026
3027 -------------------------------------------------------------
3028 -- Bind vars
3029 -------------------------------------------------------------
3030 BEGIN
3031 debug( ' Binding update_ps_c', MSG_LEVEL_DEBUG );
3032
3033 dbms_sql.bind_variable( p_update_ps_c,
3034 'user_id',
3035 p_profile_info.user_id );
3036
3037 dbms_sql.bind_variable( p_update_ps_c,
3038 'login_id',
3039 p_profile_info.conc_login_id );
3040
3041 dbms_sql.bind_variable( p_update_ps_c,
3042 'amount_due_remaining',
3043 p_inv_amount_due_rem_t( i ) );
3044
3045 dbms_sql.bind_variable( p_update_ps_c,
3046 'gl_date_closed',
3047 p_select_rec.gl_date_closed );
3048
3049 dbms_sql.bind_variable( p_update_ps_c,
3050 'actual_date_closed',
3051 p_select_rec.actual_date_closed );
3052
3053 dbms_sql.bind_variable( p_update_ps_c,
3054 'amount_line_items_remaining',
3055 p_inv_line_rem_t( i ) );
3056
3057 dbms_sql.bind_variable( p_update_ps_c,
3058 'amount_credited',
3059 p_inv_amount_credited_t( i ) );
3060
3061 dbms_sql.bind_variable( p_update_ps_c,
3062 'freight_remaining',
3063 p_inv_freight_rem_t( i ) );
3064
3065 dbms_sql.bind_variable( p_update_ps_c,
3066 'tax_remaining',
3067 p_inv_tax_rem_t( i ) );
3068
3069 dbms_sql.bind_variable( p_update_ps_c,
3070 'acctd_amount_due_remaining',
3071 p_inv_acctd_amt_due_rem_t( i ) );
3072
3073 dbms_sql.bind_variable( p_update_ps_c,
3074 'receivables_charges_remaining',
3075 p_inv_charges_rem_t( i ) );
3076
3077 dbms_sql.bind_variable( p_update_ps_c,
3078 'applied_payment_schedule_id',
3079 p_inv_ps_id_t( i ) );
3080
3081
3082
3083 EXCEPTION
3084 WHEN OTHERS THEN
3085 debug( 'EXCEPTION: Error in binding update_ps_c',
3086 MSG_LEVEL_BASIC );
3087 RAISE;
3088 END;
3089
3090 -------------------------------------------------------------
3091 -- Execute
3092 -------------------------------------------------------------
3093 BEGIN
3094 debug( ' Updating invoice payment schedules', MSG_LEVEL_DEBUG );
3095 l_ignore := dbms_sql.execute( p_update_ps_c );
3096 debug( to_char(l_ignore) || ' row(s) updated',
3097 MSG_LEVEL_DEBUG );
3098
3099 /*-------------------------------------------+
3100 | Call central MRC library for update |
3101 | of AR_PAYMENT_SCHEDULES |
3102 +-------------------------------------------*/
3103
3104 ar_mrc_engine.maintain_mrc_data(
3105 p_event_mode => 'UPDATE',
3106 p_table_name => 'AR_PAYMENT_SCHEDULES',
3107 p_mode => 'SINGLE',
3108 p_key_value => p_inv_ps_id_t( i ));
3109
3110 EXCEPTION
3111 WHEN OTHERS THEN
3112 debug( 'EXCEPTION: Error executing update ps stmt',
3113 MSG_LEVEL_BASIC );
3114 debug('EXCEPTION: dynamic sql that got executed '||p_update_ps_c,
3115 MSG_LEVEL_BASIC);
3116 debug('EXCEPTION: sqlerrm '||sqlerrm, MSG_LEVEL_BASIC);
3117 RAISE;
3118 END;
3119
3120 END LOOP;
3121
3122
3123
3124 print_fcn_label2('arp_maintain_ps2.process_ira_data()-' );
3125
3126 EXCEPTION
3127 WHEN OTHERS THEN
3128 debug( 'EXCEPTION: arp_maintain_ps2.process_ira_data()',
3129 MSG_LEVEL_BASIC );
3130 RAISE;
3131 END process_ira_data;
3132
3133
3134 ----------------------------------------------------------------------------
3135 PROCEDURE insert_cm_ps_private(
3136 p_system_info IN arp_trx_global.system_info_rec_type,
3137 p_profile_info IN arp_trx_global.profile_rec_type,
3138 p_customer_trx_id IN BINARY_INTEGER ) IS
3139
3140 l_ignore INTEGER;
3141
3142 l_old_trx_id BINARY_INTEGER;
3143 l_customer_trx_id BINARY_INTEGER;
3144 l_old_inv_trx_id BINARY_INTEGER;
3145 l_inv_customer_trx_id BINARY_INTEGER;
3146
3147 l_load_inv_tables BOOLEAN := FALSE;
3148
3149 --
3150 -- Invoice ps attributes
3151 --
3152 l_inv_ps_id_t id_table_type;
3153 l_inv_amount_due_rem_t number_table_type;
3154 l_inv_acctd_amt_due_rem_t number_table_type;
3155 l_inv_line_rem_t number_table_type;
3156 l_inv_tax_rem_t number_table_type;
3157 l_inv_freight_rem_t number_table_type;
3158 l_inv_charges_rem_t number_table_type;
3159 l_inv_amount_credited_t number_table_type;
3160
3161 --
3162 -- Derived attributes
3163 --
3164 l_line_applied_t number_table_type;
3165 l_tax_applied_t number_table_type;
3166 l_freight_applied_t number_table_type;
3167 l_charges_applied_t number_table_type;
3168 l_acctd_amt_applied_from_t number_table_type;
3169 l_acctd_amt_applied_to_t number_table_type;
3170
3171 l_table_index BINARY_INTEGER := 0;
3172
3173 l_select_rec select_ira_rec_type;
3174
3175 -----------------------------------------------------------------------
3176 PROCEDURE load_tables( p_select_rec IN select_ira_rec_type ) IS
3177
3178 BEGIN
3179 print_fcn_label2('arp_maintain_ps2.load_tables()+' );
3180
3181 l_inv_ps_id_t( l_table_index ) :=
3182 p_select_rec.inv_payment_schedule_id;
3183 l_inv_amount_due_rem_t( l_table_index ) :=
3184 p_select_rec.inv_amount_due_remaining;
3185 l_inv_acctd_amt_due_rem_t( l_table_index ) :=
3186 p_select_rec.inv_acctd_amt_due_rem;
3187 l_inv_line_rem_t( l_table_index ) :=
3188 p_select_rec.inv_line_remaining;
3189 l_inv_tax_rem_t( l_table_index ) :=
3190 p_select_rec.inv_tax_remaining;
3191 l_inv_freight_rem_t( l_table_index ) :=
3192 p_select_rec.inv_freight_remaining;
3193 l_inv_charges_rem_t( l_table_index ) :=
3194 p_select_rec.inv_charges_remaining;
3195 l_inv_amount_credited_t( l_table_index ) :=
3196 p_select_rec.inv_amount_credited;
3197
3198 l_table_index := l_table_index + 1;
3199
3200 print_fcn_label2('arp_maintain_ps2.load_tables()-' );
3201
3202 EXCEPTION
3203 WHEN OTHERS THEN
3204 debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
3205 MSG_LEVEL_BASIC );
3206 RAISE;
3207 END load_tables;
3208
3209
3210 -----------------------------------------------------------------------
3211 PROCEDURE clear_cm_tables IS
3212
3213 BEGIN
3214 print_fcn_label2('arp_maintain_ps2.clear_cm_tables()+' );
3215
3216 l_line_applied_t := null_number_t;
3217 l_tax_applied_t := null_number_t;
3218 l_freight_applied_t := null_number_t;
3219 l_charges_applied_t := null_number_t;
3220 l_acctd_amt_applied_from_t := null_number_t;
3221 l_acctd_amt_applied_to_t := null_number_t;
3222
3223 print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
3224
3225 EXCEPTION
3226 WHEN OTHERS THEN
3227 debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
3228 MSG_LEVEL_BASIC );
3229 RAISE;
3230 END clear_cm_tables;
3231
3232
3233 -----------------------------------------------------------------------
3234 PROCEDURE clear_all_tables IS
3235
3236 BEGIN
3237 print_fcn_label2('arp_maintain_ps2.clear_all_tables()+' );
3238
3239 l_inv_ps_id_t := null_id_t;
3240 l_inv_amount_due_rem_t := null_number_t;
3241 l_inv_acctd_amt_due_rem_t := null_number_t;
3242 l_inv_line_rem_t := null_number_t;
3243 l_inv_tax_rem_t := null_number_t;
3244 l_inv_freight_rem_t := null_number_t;
3245 l_inv_charges_rem_t := null_number_t;
3246 l_inv_amount_credited_t := null_number_t;
3247
3248 clear_cm_tables;
3249
3250 l_table_index := 0;
3251
3252 print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
3253
3254 EXCEPTION
3255 WHEN OTHERS THEN
3256 debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
3257 MSG_LEVEL_BASIC );
3258 RAISE;
3259 END clear_all_tables;
3260
3261 -----------------------------------------------------------------------
3262 FUNCTION is_new_id( p_old_id BINARY_INTEGER, p_new_id BINARY_INTEGER )
3263 RETURN BOOLEAN IS
3264 BEGIN
3265
3266 RETURN( p_old_id IS NULL OR p_old_id <> p_new_id );
3267
3268 END is_new_id;
3269
3270 -----------------------------------------------------------------------
3271 FUNCTION is_new_cm RETURN BOOLEAN IS
3272 BEGIN
3273
3274 RETURN( l_old_trx_id IS NULL OR l_old_trx_id <> l_customer_trx_id );
3275
3276 END is_new_cm;
3277
3278 -----------------------------------------------------------------------
3279 FUNCTION is_new_inv RETURN BOOLEAN IS
3280 BEGIN
3281
3282 RETURN( l_old_inv_trx_id IS NULL OR
3283 l_old_inv_trx_id <> l_inv_customer_trx_id );
3284
3285 END is_new_inv;
3286
3287
3288 BEGIN
3289
3290 print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()+' );
3291
3292 --
3293 clear_all_tables;
3294 --
3295 IF( NOT( dbms_sql.is_open( ira_select_c ) AND
3296 dbms_sql.is_open( ira_insert_ps_c ) AND
3297 dbms_sql.is_open( ira_insert_ra_c ) AND
3298 dbms_sql.is_open( ira_update_ps_c ) )) THEN
3299
3300 build_ira_sql(
3301 system_info,
3302 profile_info,
3303 ira_select_c,
3304 ira_insert_ps_c,
3305 ira_insert_ra_c,
3306 ira_update_ps_c );
3307 END IF;
3308
3309 --
3310 define_ira_select_columns( ira_select_c, l_select_rec );
3311
3312 ---------------------------------------------------------------
3313 -- Bind variables
3314 ---------------------------------------------------------------
3315 dbms_sql.bind_variable( ira_select_c,
3316 'customer_trx_id',
3317 p_customer_trx_id );
3318
3319 ---------------------------------------------------------------
3320 -- Execute sql
3321 ---------------------------------------------------------------
3322 debug( ' Executing select sql', MSG_LEVEL_DEBUG );
3323
3324 BEGIN
3325 l_ignore := dbms_sql.execute( ira_select_c );
3326
3327 EXCEPTION
3328 WHEN OTHERS THEN
3329 debug( 'EXCEPTION: Error executing select sql',
3330 MSG_LEVEL_BASIC );
3331 RAISE;
3332 END;
3333
3334
3335 ---------------------------------------------------------------
3336 -- Fetch rows
3337 ---------------------------------------------------------------
3338 BEGIN
3339 LOOP
3340
3341 IF dbms_sql.fetch_rows( ira_select_c ) > 0 THEN
3342
3343 debug(' Fetched a row', MSG_LEVEL_DEBUG );
3344
3345 -----------------------------------------------
3346 -- Load row into record
3347 -----------------------------------------------
3348 dbms_sql.column_value( ira_select_c, 4, l_customer_trx_id );
3349 dbms_sql.column_value( ira_select_c, 15,
3350 l_inv_customer_trx_id );
3351
3352 -----------------------------------------------
3353 -- Check if invoice or cm changed
3354 -----------------------------------------------
3355 IF( is_new_inv OR is_new_cm ) THEN
3356
3357 debug( ' new invoice or cm', MSG_LEVEL_DEBUG );
3358
3359 -----------------------------------------------
3360 -- Check if invoice changed
3361 -----------------------------------------------
3362 IF( is_new_inv ) THEN
3363
3364 debug( ' new invoice', MSG_LEVEL_DEBUG );
3365
3366 ---------------------------------------------------
3367 -- Start loading invoice ps tables for new invoice
3368 ---------------------------------------------------
3369 l_load_inv_tables := TRUE;
3370
3371 END IF;
3372
3373 IF( l_old_inv_trx_id IS NOT NULL ) THEN
3374
3375 debug( ' process1', MSG_LEVEL_DEBUG );
3376
3377 process_ira_data(
3378 system_info,
3379 profile_info,
3380 ira_insert_ps_c,
3381 ira_insert_ra_c,
3382 ira_update_ps_c,
3383 l_select_rec,
3384 l_table_index,
3385 l_inv_ps_id_t,
3386 l_inv_amount_due_rem_t,
3387 l_inv_acctd_amt_due_rem_t,
3388 l_inv_line_rem_t,
3389 l_inv_tax_rem_t,
3390 l_inv_freight_rem_t,
3391 l_inv_charges_rem_t,
3392 l_inv_amount_credited_t,
3393 l_line_applied_t,
3394 l_tax_applied_t,
3395 l_freight_applied_t,
3396 l_charges_applied_t,
3397 l_acctd_amt_applied_from_t,
3398 l_acctd_amt_applied_to_t );
3399
3400 END IF;
3401
3402 -----------------------------------------------
3403 -- Check if new invoice
3404 -----------------------------------------------
3405 IF( is_new_inv ) THEN
3406
3407 clear_all_tables;
3408
3409 l_old_inv_trx_id := l_inv_customer_trx_id;
3410 l_old_trx_id := l_customer_trx_id;
3411
3412 -----------------------------------------------
3413 -- Else new CM
3414 -----------------------------------------------
3415 ELSE
3416
3417 clear_cm_tables;
3418
3419 l_load_inv_tables := FALSE;
3420 l_old_trx_id := l_customer_trx_id;
3421
3422 END IF;
3423
3424 END IF; -- END inv or cm changed
3425
3426 get_ira_column_values( ira_select_c, l_select_rec );
3427 dump_ira_select_rec( l_select_rec );
3428
3429
3430 IF( l_load_inv_tables ) THEN
3431 load_tables( l_select_rec );
3432 END IF;
3433
3434 -- >> dump tables
3435
3436 ELSE
3437 -----------------------------------------------
3438 -- No more rows to fetch, process last set
3439 -----------------------------------------------
3440
3441 debug( ' process2', MSG_LEVEL_DEBUG );
3442
3443 process_ira_data(
3444 system_info,
3445 profile_info,
3446 ira_insert_ps_c,
3447 ira_insert_ra_c,
3448 ira_update_ps_c,
3449 l_select_rec,
3450 l_table_index,
3451 l_inv_ps_id_t,
3452 l_inv_amount_due_rem_t,
3453 l_inv_acctd_amt_due_rem_t,
3454 l_inv_line_rem_t,
3455 l_inv_tax_rem_t,
3456 l_inv_freight_rem_t,
3457 l_inv_charges_rem_t,
3458 l_inv_amount_credited_t,
3459 l_line_applied_t,
3460 l_tax_applied_t,
3461 l_freight_applied_t,
3462 l_charges_applied_t,
3463 l_acctd_amt_applied_from_t,
3464 l_acctd_amt_applied_to_t );
3465
3466
3467 EXIT;
3468
3469 END IF;
3470
3471
3472 END LOOP;
3473
3474 EXCEPTION
3475 WHEN OTHERS THEN
3476 debug( 'EXCEPTION: Error fetching select cursor',
3477 MSG_LEVEL_BASIC );
3478 RAISE;
3479
3480 END;
3481
3482 print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()-' );
3483
3484 EXCEPTION
3485 WHEN OTHERS THEN
3486 debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_ps_private()',
3487 MSG_LEVEL_BASIC );
3488 RAISE;
3489
3490 END insert_cm_ps_private;
3491
3492
3493 ----------------------------------------------------------------------------
3494 --
3495 -- PROCEDURE NAME: build_ups_sql
3496 --
3497 -- DECSRIPTION:
3498 --
3499 -- ARGUMENTS:
3500 -- IN:
3501 -- system_info
3502 -- profile_info
3503 --
3504 -- IN/OUT:
3505 -- select_c
3506 -- insert_ps_c
3507 -- insert_ra_c
3508 -- update_ps_c
3509 --
3510 -- OUT:
3511 --
3512 -- RETURNS:
3513 --
3514 -- NOTES:
3515 --
3516 -- HISTORY:
3517 --
3518 -- 01-JUN-01 1483656 - Updated select, insert, and update to allocate
3519 -- tax and freight to deposits.
3520 ----------------------------------------------------------------------------
3521 PROCEDURE build_ups_sql(
3522 p_system_info IN arp_trx_global.system_info_rec_type,
3523 p_profile_info IN arp_trx_global.profile_rec_type,
3524 p_select_c IN OUT NOCOPY INTEGER,
3525 p_insert_adj_c IN OUT NOCOPY INTEGER,
3526 p_update_ps_c IN OUT NOCOPY INTEGER ) IS
3527
3528 l_insert_adj_sql VARCHAR2(2000);
3529 l_update_ps_sql VARCHAR2(2000);
3530 l_select_sql VARCHAR2(6000);
3531
3532
3533 BEGIN
3534
3535 print_fcn_label( 'arp_maintain_ps2.build_ups_sql()+' );
3536
3537 ------------------------------------------------
3538 -- Select sql
3539 ------------------------------------------------
3540 l_select_sql :=
3541 'SELECT
3542 /* :raagixuix, */
3543 ctl.set_of_books_id,
3544 /* -1, */
3545 /* -''Y'', */
3546 /* ''LINE'', overridden below */
3547 /* -''C'', */
3548 /* -''A'', */
3549 sum(ctl.extended_amount) /
3550 (count(distinct ps.payment_schedule_id) *
3551 count(distinct nvl(ra.receivable_application_id, -9.9)) *
3552 count(distinct nvl(adj.adjustment_id, -9.9))),
3553 nvl(:gl_date, nvl(ctlgd.gl_date, ct.trx_date)),
3554 ctlgd_com.code_combination_id,
3555 decode(ctt_com.type,
3556 ''DEP'', ctl.customer_trx_id,
3557 ctl_com.customer_trx_id),
3558 ps.payment_schedule_id,
3559 decode(ctt_com.type,
3560 ''DEP'', null,
3561 ctl.customer_trx_id),
3562 ''Y'', /* bugfix 2614759. Instead of ctt.post_to_gl, pass y always. */
3563 ct_com.customer_trx_id,
3564 tl.relative_amount / t.base_amount,
3565 c.precision,
3566 c.minimum_accountable_unit,
3567 greatest(nvl(max(decode(ra.confirmed_flag,
3568 ''Y'', ra.gl_date,
3569 null,
3570 decode(ra.receivable_application_id,
3571 null, nvl(ctlgd.gl_date,
3572 ct.trx_date),
3573 ra.gl_date),
3574 nvl(ctlgd.gl_date,
3575 ct.trx_date))),
3576 nvl(ctlgd.gl_date,ct.trx_date)),
3577 nvl(max(decode(adj.status,
3578 ''A'',adj.gl_date,
3579 nvl(ctlgd.gl_date,
3580 ct.trx_date))),
3581 nvl(ctlgd.gl_date,ct.trx_date)),
3582 nvl(:gl_date, nvl(ctlgd.gl_date, ct.trx_date))),
3583 greatest(nvl(max(decode(ra.confirmed_flag,
3584 ''Y'', ra.apply_date,
3585 null,
3586 decode(ra.receivable_application_id,
3587 null, ct.trx_date,
3588 ra.apply_date),
3589 ct.trx_date)),
3590 ct.trx_date),
3591 nvl(max(decode(adj.status,
3592 ''A'',adj.apply_date,
3593 ct.trx_date)),
3594 ct.trx_date),
3595 nvl(:apply_date, ct.trx_date),
3596 ct.trx_date),
3597 nvl(:apply_date, ct.trx_date),
3598 ctt_com.type,
3599 /* :raagixlul, */
3600 /* null, */
3601 nvl(ps.amount_line_items_remaining,0),
3602 ps.amount_due_remaining,
3603 ps.acctd_amount_due_remaining,
3604 nvl(ps.amount_adjusted,0),
3605 c_ps.precision,
3606 c_ps.minimum_accountable_unit,
3607 ct_ps.exchange_rate,
3608 ctl.customer_trx_id,
3609 ct_ps.invoice_currency_code,
3610 ctt_com.allocate_tax_freight,
3611 DECODE(ctt_com.allocate_tax_freight, ''Y'', ''INVOICE'',''LINE''), /*1483656 - LINE or INVOICE */
3612 nvl(ps.tax_remaining, 0),
3613 nvl(ps.freight_remaining, 0),
3614 ARPT_SQL_FUNC_UTIL.get_sum_of_trx_lines(ctl.customer_trx_id, ''TAX''),
3615 ARPT_SQL_FUNC_UTIL.get_sum_of_trx_lines(ctl.customer_trx_id, ''FREIGHT'')
3616 FROM
3617 ra_cust_trx_types ctt,
3618 ra_cust_trx_types ctt_com,
3619 ra_cust_trx_line_gl_dist ctlgd_com,
3620 ar_payment_schedules ps,
3621 ar_receivable_applications ra,
3622 ar_adjustments adj,
3623 fnd_currencies c,
3624 ra_terms t,
3625 ra_terms_lines tl,
3626 ra_customer_trx ct_com,
3627 ra_customer_trx_lines ctl_com,
3628 ra_customer_trx ct_ps,
3629 fnd_currencies c_ps,
3630 ra_customer_trx_lines ctl,
3631 ra_customer_trx ct,
3632 ra_cust_trx_line_gl_dist ctlgd
3633 WHERE ct.customer_trx_id = :customer_trx_id
3634 and ctl.customer_trx_id = ct.customer_trx_id
3635 and ctlgd.customer_trx_id = ct.customer_trx_id
3636 and ctlgd.account_class = ''REC''
3637 and ctlgd.latest_rec_flag = ''Y''
3638 and ctl.line_type = ''LINE''
3639 and exists
3640 (select ''x''
3641 from ra_customer_trx trx
3642 where trx.customer_trx_id = ctl.customer_trx_id)
3643 and ctl.initial_customer_trx_line_id is not null
3644 and ct.invoice_currency_code = c.currency_code
3645 and ct.cust_trx_type_id = ctt.cust_trx_type_id
3646 and ctt.type = ''INV''
3647 and ctl.initial_customer_trx_line_id = ctl_com.customer_trx_line_id
3648 and ctl_com.customer_trx_id = ct_com.customer_trx_id
3649 and ctl_com.customer_trx_line_id = ctlgd_com.customer_trx_line_id
3650 and ctlgd_com.account_class = ''REV''
3651 and ct_com.cust_trx_type_id = ctt_com.cust_trx_type_id
3652 and ps.customer_trx_id =
3653 decode(ctt_com.type,
3654 ''DEP'', ctl.customer_trx_id,
3655 ctl_com.customer_trx_id)
3656 and ps.customer_trx_id = ct_ps.customer_trx_id
3657 and ct_ps.invoice_currency_code = c_ps.currency_code
3658 and ps.term_id = t.term_id
3659 and ps.term_id = tl.term_id
3660 and ps.terms_sequence_number = tl.sequence_num
3661 and ps.customer_trx_id = ra.applied_customer_trx_id (+)
3662 and ps.customer_trx_id = adj.customer_trx_id (+)
3663 GROUP BY
3664 ctlgd_com.code_combination_id,
3665 ctl.customer_trx_id,
3666 ctl_com.customer_trx_id,
3667 ps.payment_schedule_id,
3668 /* bugfix 2614759. comment out ctt.post_to_gl, */
3669 ctt_com.type,
3670 ctt_com.allocate_tax_freight, /*1483656*/
3671 ct_com.customer_trx_id,
3672 tl.relative_amount / t.base_amount,
3673 c.precision,
3674 c.minimum_accountable_unit,
3675 ra.applied_customer_trx_id,
3676 adj.customer_trx_id,
3677 ctlgd.gl_date,
3678 ct.trx_date,
3679 ctlgd_com.gl_date,
3680 ct_com.trx_date,
3681 ctl.set_of_books_id,
3682 c_ps.precision,
3683 c_ps.minimum_accountable_unit,
3684 ct_ps.exchange_rate,
3685 ps.amount_line_items_remaining,
3686 ps.tax_remaining,
3687 ps.freight_remaining,
3688 ps.amount_due_remaining,
3689 ps.amount_adjusted,
3690 ps.acctd_amount_due_remaining,
3691 ps.terms_sequence_number,
3692 ct_ps.invoice_currency_code
3693 ORDER BY
3694 ct_com.customer_trx_id,
3695 ctl.customer_trx_id,
3696 ps.terms_sequence_number';
3697
3698
3699 debug(' select_sql = ' || CRLF ||
3700 l_select_sql || CRLF,
3701 MSG_LEVEL_DEBUG);
3702 debug(' len(select_sql) = '||
3703 to_char(length(l_select_sql)) || CRLF,
3704 MSG_LEVEL_DEBUG);
3705
3706
3707 ------------------------------------------------
3708 -- Insert adj sql
3709 ------------------------------------------------
3710 l_insert_adj_sql :=
3711 'INSERT INTO AR_ADJUSTMENTS
3712 (
3713 created_by,
3714 creation_date,
3715 last_updated_by,
3716 last_update_date,
3717 last_update_login,
3718 request_id,
3719 program_application_id,
3720 program_id,
3721 program_update_date,
3722 set_of_books_id,
3723 receivables_trx_id,
3724 automatically_generated,
3725 type,
3726 adjustment_type,
3727 status,
3728 apply_date,
3729 adjustment_id,
3730 gl_date,
3731 code_combination_id,
3732 customer_trx_id,
3733 payment_schedule_id,
3734 subsequent_trx_id,
3735 postable,
3736 adjustment_number,
3737 created_from,
3738 posting_control_id,
3739 amount,
3740 acctd_amount,
3741 line_adjusted,
3742 tax_adjusted,
3743 freight_adjusted
3744 ,org_id
3745 )
3746 VALUES
3747 (
3748 :user_id,
3749 sysdate,
3750 :user_id,
3751 sysdate,
3752 :login_id,
3753 :request_id,
3754 :application_id,
3755 :program_id,
3756 sysdate,
3757 :set_of_books_id,
3758 -1,
3759 ''Y'',
3760 :adjust_type,
3761 ''C'',
3762 ''A'',
3763 :trx_date,
3764 :adjustment_id,
3765 :gl_date,
3766 :code_combination_id,
3767 :adjusted_trx_id,
3768 :payment_schedule_id,
3769 :subsequent_trx_id,
3770 :post_to_gl_flag,
3771 to_char(ar_adjustment_number_s.nextval),
3772 ''RAXTRX'',
3773 -3,
3774 -1 * :adj_amount,
3775 -1 * :acctd_adj_amount,
3776 -1 * :line_adj_amount,
3777 -1 * :tax_adj_amount,
3778 -1 * :frt_adj_amount
3779 ,:org_id --arp_standard.sysparm.org_id /* SSA changes anuj */
3780 )';
3781
3782 debug(' insert_adj_sql = ' || CRLF ||
3783 l_insert_adj_sql || CRLF,
3784 MSG_LEVEL_DEBUG);
3785 debug(' len(insert_adj_sql) = '||
3786 to_char(length(l_insert_adj_sql)) || CRLF,
3787 MSG_LEVEL_DEBUG);
3788
3789
3790 ------------------------------------------------
3791 -- Update ps sql
3792 ------------------------------------------------
3793 -- Modified the update statement to incorporate the hard-coded date if the transaction is open - For Bug:5491085
3794 l_update_ps_sql :=
3795 'UPDATE AR_PAYMENT_SCHEDULES
3796 SET last_update_date = sysdate,
3797 last_updated_by = :user_id,
3798 last_update_login = :login_id,
3799 status = decode(:amount_due_remaining, 0, ''CL'', ''OP''),
3800 gl_date_closed =
3801 decode(:amount_due_remaining, 0, :gl_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
3802 actual_date_closed =
3803 decode(:amount_due_remaining, 0, :actual_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
3804 amount_due_remaining = :amount_due_remaining,
3805 acctd_amount_due_remaining = :acctd_amount_due_remaining,
3806 amount_line_items_remaining = :amount_line_items_remaining,
3807 amount_adjusted = :amount_adjusted,
3808 tax_remaining = :tax_remaining,
3809 freight_remaining = :freight_remaining
3810 WHERE payment_schedule_id = :payment_schedule_id';
3811
3812 debug(' update_ps_sql = ' || CRLF ||
3813 l_update_ps_sql || CRLF,
3814 MSG_LEVEL_DEBUG);
3815 debug(' len(update_ps_sql) = '||
3816 to_char(length(l_update_ps_sql)) || CRLF,
3817 MSG_LEVEL_DEBUG);
3818
3819
3820
3821
3822 ------------------------------------------------
3823 -- Parse sql stmts
3824 ------------------------------------------------
3825 BEGIN
3826 debug( ' Parsing stmts', MSG_LEVEL_DEBUG );
3827
3828 debug( ' Parsing insert_adj_c', MSG_LEVEL_DEBUG );
3829 p_insert_adj_c := dbms_sql.open_cursor;
3830 dbms_sql.parse( p_insert_adj_c, l_insert_adj_sql,
3831 dbms_sql.v7 );
3832
3833 debug( ' Parsing update_ps_c', MSG_LEVEL_DEBUG );
3834 p_update_ps_c := dbms_sql.open_cursor;
3835 dbms_sql.parse( p_update_ps_c, l_update_ps_sql,
3836 dbms_sql.v7 );
3837
3838 debug( ' Parsing select_c', MSG_LEVEL_DEBUG );
3839 p_select_c := dbms_sql.open_cursor;
3840 dbms_sql.parse( p_select_c, l_select_sql,
3841 dbms_sql.v7 );
3842
3843 EXCEPTION
3844 WHEN OTHERS THEN
3845 IF PG_DEBUG in ('Y', 'C') THEN
3846 arp_util.debug(SQLERRM);
3847 END IF;
3848 debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
3849 RAISE;
3850 END;
3851
3852 print_fcn_label( 'arp_maintain_ps2.build_ups_sql()-' );
3853
3854
3855 EXCEPTION
3856 WHEN OTHERS THEN
3857 debug( 'EXCEPTION: arp_maintain_ps2.build_ups_sql()',
3858 MSG_LEVEL_BASIC );
3859
3860 RAISE;
3861 END build_ups_sql;
3862
3863
3864 ----------------------------------------------------------------------------
3865 PROCEDURE define_ups_select_columns(
3866 p_select_c IN INTEGER,
3867 p_select_rec IN select_ups_rec_type ) IS
3868
3869 BEGIN
3870
3871 print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()+' );
3872
3873 dbms_sql.define_column( p_select_c, 1, p_select_rec.set_of_books_id );
3874 dbms_sql.define_column( p_select_c, 2, p_select_rec.total_line_amount );
3875 dbms_sql.define_column( p_select_c, 3, p_select_rec.gl_date );
3876 dbms_sql.define_column( p_select_c, 4, p_select_rec.code_combination_id );
3877 dbms_sql.define_column( p_select_c, 5,
3878 p_select_rec.adjusted_trx_id );
3879 dbms_sql.define_column( p_select_c, 6,
3880 p_select_rec.payment_schedule_id );
3881 dbms_sql.define_column( p_select_c, 7,
3882 p_select_rec.subsequent_trx_id );
3883 dbms_sql.define_column( p_select_c, 8,
3884 p_select_rec.post_to_gl_flag, 1 );
3885 dbms_sql.define_column( p_select_c, 9,
3886 p_select_rec.commitment_trx_id );
3887 dbms_sql.define_column( p_select_c, 10, p_select_rec.percent );
3888 dbms_sql.define_column( p_select_c, 11, p_select_rec.precision );
3889 dbms_sql.define_column( p_select_c, 12, p_select_rec.min_acc_unit );
3890 dbms_sql.define_column( p_select_c, 13, p_select_rec.gl_date_closed );
3891 dbms_sql.define_column( p_select_c, 14, p_select_rec.actual_date_closed );
3892 dbms_sql.define_column( p_select_c, 15, p_select_rec.trx_date );
3893 dbms_sql.define_column( p_select_c, 16, p_select_rec.commitment_type, 20 );
3894 dbms_sql.define_column( p_select_c, 17, p_select_rec.line_remaining );
3895 dbms_sql.define_column( p_select_c, 18,
3896 p_select_rec.amount_due_remaining );
3897 dbms_sql.define_column( p_select_c, 19,
3898 p_select_rec.acctd_amt_due_rem );
3899 dbms_sql.define_column( p_select_c, 20,
3900 p_select_rec.amount_adjusted );
3901 dbms_sql.define_column( p_select_c, 21, p_select_rec.ps_precision );
3902 dbms_sql.define_column( p_select_c, 22, p_select_rec.ps_min_acc_unit );
3903 dbms_sql.define_column( p_select_c, 23, p_select_rec.ps_exchange_rate );
3904 dbms_sql.define_column( p_select_c, 24, p_select_rec.customer_trx_id );
3905 dbms_sql.define_column( p_select_c, 25,
3906 p_select_rec.ps_currency_code, 15 );
3907 -- 1483656 new columns for allocating tax and freight
3908 dbms_sql.define_column( p_select_c, 26, p_select_rec.allocate_tax_freight,1 );
3909 dbms_sql.define_column( p_select_c, 27, p_select_rec.adjustment_type,8 );
3910 dbms_sql.define_column( p_select_c, 28, p_select_rec.tax_remaining );
3911 dbms_sql.define_column( p_select_c, 29, p_select_rec.freight_remaining );
3912 dbms_sql.define_column( p_select_c, 30, p_select_rec.total_tax_amount );
3913 dbms_sql.define_column( p_select_c, 31, p_select_rec.total_freight_amount );
3914
3915 print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()-' );
3916
3917 EXCEPTION
3918 WHEN OTHERS THEN
3919 debug('EXCEPTION: arp_maintain_ps2.define_ups_select_columns()',
3920 MSG_LEVEL_BASIC);
3921 RAISE;
3922 END define_ups_select_columns;
3923
3924
3925 ----------------------------------------------------------------------------
3926 PROCEDURE get_ups_column_values( p_select_c IN INTEGER,
3927 p_select_rec IN OUT NOCOPY select_ups_rec_type ) IS
3928 /* Bug 460927 - Changed IN to IN OUT in the above line - Oracle 8 */
3929 BEGIN
3930 print_fcn_label2( 'arp_maintain_ps2.get_ups_column_values()+' );
3931
3932 dbms_sql.column_value( p_select_c, 1, p_select_rec.set_of_books_id );
3933 dbms_sql.column_value( p_select_c, 2, p_select_rec.total_line_amount );
3934 dbms_sql.column_value( p_select_c, 3, p_select_rec.gl_date );
3935 dbms_sql.column_value( p_select_c, 4, p_select_rec.code_combination_id );
3936 dbms_sql.column_value( p_select_c, 5,
3937 p_select_rec.adjusted_trx_id );
3938 dbms_sql.column_value( p_select_c, 6, p_select_rec.payment_schedule_id );
3939 dbms_sql.column_value( p_select_c, 7, p_select_rec.subsequent_trx_id );
3940 dbms_sql.column_value( p_select_c, 8, p_select_rec.post_to_gl_flag );
3941 dbms_sql.column_value( p_select_c, 9, p_select_rec.commitment_trx_id );
3942 dbms_sql.column_value( p_select_c, 10, p_select_rec.percent );
3943 dbms_sql.column_value( p_select_c, 11, p_select_rec.precision );
3944 dbms_sql.column_value( p_select_c, 12, p_select_rec.min_acc_unit );
3945 dbms_sql.column_value( p_select_c, 13, p_select_rec.gl_date_closed );
3946 dbms_sql.column_value( p_select_c, 14, p_select_rec.actual_date_closed );
3947 dbms_sql.column_value( p_select_c, 15, p_select_rec.trx_date );
3948 dbms_sql.column_value( p_select_c, 16, p_select_rec.commitment_type );
3949 dbms_sql.column_value( p_select_c, 17, p_select_rec.line_remaining );
3950 dbms_sql.column_value( p_select_c, 18,
3951 p_select_rec.amount_due_remaining );
3952 dbms_sql.column_value( p_select_c, 19, p_select_rec.acctd_amt_due_rem );
3953 dbms_sql.column_value( p_select_c, 20, p_select_rec.amount_adjusted );
3954 dbms_sql.column_value( p_select_c, 21, p_select_rec.ps_precision );
3955 dbms_sql.column_value( p_select_c, 22, p_select_rec.ps_min_acc_unit );
3956 dbms_sql.column_value( p_select_c, 23, p_select_rec.ps_exchange_rate );
3957 dbms_sql.column_value( p_select_c, 24, p_select_rec.customer_trx_id );
3958 dbms_sql.column_value( p_select_c, 25, p_select_rec.ps_currency_code );
3959 -- 1483656 new columns for allocating tax and freight
3960 dbms_sql.column_value( p_select_c, 26, p_select_rec.allocate_tax_freight );
3961 dbms_sql.column_value( p_select_c, 27, p_select_rec.adjustment_type );
3962 dbms_sql.column_value( p_select_c, 28, p_select_rec.tax_remaining );
3963 dbms_sql.column_value( p_select_c, 29, p_select_rec.freight_remaining );
3964 dbms_sql.column_value( p_select_c, 30, p_select_rec.total_tax_amount );
3965 dbms_sql.column_value( p_select_c, 31, p_select_rec.total_freight_amount );
3966
3967 print_fcn_label2( 'arp_maintain_ps2.get_ups_column_values()-' );
3968 EXCEPTION
3969 WHEN OTHERS THEN
3970 debug('EXCEPTION: arp_maintain_ps2.get_ups_column_values()',
3971 MSG_LEVEL_BASIC);
3972 RAISE;
3973 END get_ups_column_values;
3974
3975
3976 ----------------------------------------------------------------------------
3977 --
3978 -- PROCEDURE NAME: dump_ups_select_rec
3979 --
3980 -- DECSRIPTION:
3981 --
3982 -- ARGUMENTS:
3983 -- IN:
3984 -- select_rec
3985 --
3986 -- IN/OUT:
3987 --
3988 -- OUT:
3989 --
3990 -- RETURNS:
3991 --
3992 -- NOTES:
3993 --
3994 -- HISTORY:
3995 --
3996 ----------------------------------------------------------------------------
3997 PROCEDURE dump_ups_select_rec( p_select_rec IN select_ups_rec_type ) IS
3998 BEGIN
3999
4000 print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()+' );
4001
4002 debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
4003 debug( ' set_of_books_id='
4004 || p_select_rec.set_of_books_id, MSG_LEVEL_DEBUG );
4005 debug( ' customer_trx_id='
4006 || p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
4007 debug( ' post_to_gl_flag='
4008 || p_select_rec.post_to_gl_flag, MSG_LEVEL_DEBUG );
4009 debug( ' trx_date='
4010 || p_select_rec.trx_date, MSG_LEVEL_DEBUG );
4011 debug( ' gl_date='
4012 || p_select_rec.gl_date, MSG_LEVEL_DEBUG );
4013 debug( ' precision='
4014 || p_select_rec.precision, MSG_LEVEL_DEBUG );
4015 debug( ' min_acc_unit='
4016 || p_select_rec.min_acc_unit, MSG_LEVEL_DEBUG );
4017 debug( ' adjusted_trx_id='
4018 || p_select_rec.adjusted_trx_id, MSG_LEVEL_DEBUG );
4019 debug( ' subsequent_trx_id='
4020 || p_select_rec.subsequent_trx_id, MSG_LEVEL_DEBUG );
4021 debug( ' commitment_trx_id='
4022 || p_select_rec.commitment_trx_id, MSG_LEVEL_DEBUG );
4023 debug( ' commitment_type='
4024 || p_select_rec.commitment_type, MSG_LEVEL_DEBUG );
4025 debug( ' ps_currency_code='
4026 || p_select_rec.ps_currency_code, MSG_LEVEL_DEBUG );
4027 debug( ' ps_exchange_rate='
4028 || p_select_rec.ps_exchange_rate, MSG_LEVEL_DEBUG );
4029 debug( ' ps_precision='
4030 || p_select_rec.ps_precision, MSG_LEVEL_DEBUG );
4031 debug( ' ps_min_acc_unit='
4032 || p_select_rec.ps_min_acc_unit, MSG_LEVEL_DEBUG );
4033 debug( ' code_combination_id='
4034 || p_select_rec.code_combination_id, MSG_LEVEL_DEBUG );
4035 debug( ' gl_date_closed='
4036 || p_select_rec.gl_date_closed, MSG_LEVEL_DEBUG );
4037 debug( ' actual_date_closed='
4038 || p_select_rec.actual_date_closed, MSG_LEVEL_DEBUG );
4039 debug( ' total_line_amount='
4040 || p_select_rec.total_line_amount, MSG_LEVEL_DEBUG );
4041 debug( ' payment_schedule_id='
4042 || p_select_rec.payment_schedule_id, MSG_LEVEL_DEBUG );
4043 debug( ' amount_due_remamining='
4044 || p_select_rec.amount_due_remaining, MSG_LEVEL_DEBUG );
4045 debug( ' acctd_amt_due_rem='
4046 || p_select_rec.acctd_amt_due_rem, MSG_LEVEL_DEBUG );
4047 debug( ' line_remaining='
4048 || p_select_rec.line_remaining, MSG_LEVEL_DEBUG );
4049 debug( ' amount_adjusted='
4050 || p_select_rec.amount_adjusted, MSG_LEVEL_DEBUG );
4051 debug( ' percent='
4052 || p_select_rec.percent, MSG_LEVEL_DEBUG );
4053 /* 1483656 - Commitments Project */
4054 debug( ' tax_remaining='
4055 || p_select_rec.tax_remaining, MSG_LEVEL_DEBUG );
4056 debug( ' freight_remaining='
4057 || p_select_rec.freight_remaining, MSG_LEVEL_DEBUG );
4058 debug( ' adjustment_type='
4059 || p_select_rec.adjustment_type, MSG_LEVEL_DEBUG );
4060
4061 print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()-' );
4062
4063 EXCEPTION
4064 WHEN OTHERS THEN
4065 debug( 'EXCEPTION: arp_maintain_ps2.dump_ups_select_rec()',
4066 MSG_LEVEL_BASIC );
4067 RAISE;
4068 END dump_ups_select_rec;
4069
4070
4071 ------------------------------------------------------------------------
4072 -- 1483656 - added 5 new parameters to this proceedure
4073
4074 PROCEDURE process_ups_data(
4075 p_system_info IN arp_trx_global.system_info_rec_type,
4076 p_profile_info IN arp_trx_global.profile_rec_type,
4077 p_insert_adj_c IN INTEGER,
4078 p_update_ps_c IN INTEGER,
4079 p_select_rec IN select_ups_rec_type,
4080 p_number_records IN NUMBER,
4081 p_ps_id_t IN id_table_type,
4082 p_ps_amount_due_rem_t IN OUT NOCOPY number_table_type,
4083 p_ps_acctd_amt_due_rem_t IN OUT NOCOPY number_table_type,
4084 p_ps_line_rem_t IN OUT NOCOPY number_table_type,
4085 p_ps_amount_adjusted_t IN OUT NOCOPY number_table_type,
4086 p_adj_amount_t IN OUT NOCOPY number_table_type,
4087 p_acctd_adj_amount_t IN OUT NOCOPY number_table_type,
4088 p_percent_t IN number_table_type,
4089 p_ps_tax_rem_t IN OUT NOCOPY number_table_type,
4090 p_ps_freight_rem_t IN OUT NOCOPY number_table_type,
4091 p_line_adj_t IN OUT NOCOPY number_table_type,
4092 p_tax_adj_t IN OUT NOCOPY number_table_type,
4093 p_frt_adj_t IN OUT NOCOPY number_table_type) IS
4094
4095
4096 l_ignore INTEGER;
4097
4098 l_new_ps_adr NUMBER;
4099 l_new_ps_acctd_adr NUMBER;
4100 l_new_acctd_adj_amount NUMBER;
4101
4102 l_commitment_bal NUMBER;
4103 l_amount NUMBER;
4104 /* VAT changes */
4105 l_ae_doc_rec ae_doc_rec_type;
4106 l_adjustment_id ar_adjustments.adjustment_id%type;
4107
4108 l_total_tax_adj NUMBER;
4109 l_total_frt_adj NUMBER;
4110 l_total_line_adj NUMBER;
4111
4112 /* Bug 3537233 */
4113 l_line_remaining NUMBER := 0;
4114 l_tax_remaining NUMBER := 0;
4115 l_freight_remaining NUMBER := 0;
4116
4117 /* Bug 3856079 */
4118 l_line_percent_t number_table_type;
4119 l_tax_percent_t number_table_type;
4120 l_frt_percent_t number_table_type;
4121
4122 BEGIN
4123 print_fcn_label2('arp_maintain_ps2.process_ups_data()+' );
4124
4125 --------------------------------------------------------------------
4126 -- Find commitment balance
4127 --------------------------------------------------------------------
4128 l_commitment_bal := arp_bal_util.get_commitment_balance(
4129 p_select_rec.commitment_trx_id,
4130 null, -- class
4131 g_oe_install_flag, -- oe_installed_flag
4132 g_so_source_code -- so_source_code
4133 );
4134
4135 -- 1483656 - figure total amount as either total line or total balance
4136 -- and determine how much of it should be tax or freight vs line
4137
4138 IF (p_select_rec.commitment_type = 'DEP')
4139 THEN
4140 /* Bug 3431804, 3537233
4141 Figure total amount remaining (L, T, and F) based on
4142 amounts for each PS row (installment) */
4143 FOR i IN p_ps_line_rem_t.FIRST..p_ps_line_rem_t.LAST LOOP
4144 l_line_remaining := l_line_remaining + nvl(p_ps_line_rem_t(i),0);
4145 l_tax_remaining := l_tax_remaining + nvl(p_ps_tax_rem_t(i),0);
4146 l_freight_remaining := l_freight_remaining +
4147 nvl(p_ps_freight_rem_t(i),0);
4148 END LOOP;
4149
4150 /* Bug 3856079 - figure percents for LTF based on
4151 amounts due remaining in those buckets */
4152 FOR i IN p_ps_line_rem_t.FIRST..p_ps_line_rem_t.LAST LOOP
4153 IF l_line_remaining <> 0
4154 THEN
4155 l_line_percent_t(i) := p_ps_line_rem_t(i) / l_line_remaining;
4156 ELSE
4157 l_line_percent_t(i) := 0;
4158 END IF;
4159
4160 IF l_tax_remaining <> 0
4161 THEN
4162 l_tax_percent_t(i) := p_ps_tax_rem_t(i) / l_tax_remaining;
4163 ELSE
4164 l_tax_percent_t(i) := 0;
4165 END IF;
4166
4167 IF l_freight_remaining <> 0
4168 THEN
4169 l_frt_percent_t(i) := p_ps_freight_rem_t(i) / l_freight_remaining;
4170 ELSE
4171 l_frt_percent_t(i) := 0;
4172 END IF;
4173 END LOOP;
4174 ELSE
4175 /* GUAR case */
4176
4177 /* Bug 3570404
4178 Guarantees use SELECT in build_ups_sql differently.
4179 The joins to PS are for the GUAR rather than the target
4180 invoice. This means that we have to use the line totals
4181 from ra_customer_trx_lines instead. */
4182 l_line_remaining := p_select_rec.total_line_amount;
4183
4184 /* Following columns are not used for GUARs - but
4185 I choose to initialize them for clarity */
4186 l_tax_remaining := 0;
4187 l_freight_remaining := 0;
4188 END IF;
4189
4190 IF( p_select_rec.allocate_tax_freight = 'Y') THEN
4191 /* Only DEPs can allocate tax and freight */
4192
4193 debug( ' allocating tax and freight (new logic)');
4194
4195 l_amount := LEAST( l_commitment_bal, (l_line_remaining +
4196 l_tax_remaining +
4197 l_freight_remaining));
4198
4199 debug( ' total_invoice_amount='||p_select_rec.total_line_amount,
4200 MSG_LEVEL_DEBUG );
4201 debug( ' l_commitment_bal='||l_commitment_bal, MSG_LEVEL_DEBUG );
4202 debug( ' l_amount='||l_amount, MSG_LEVEL_DEBUG );
4203 debug( ' total lines = '|| p_select_rec.total_line_amount, MSG_LEVEL_DEBUG );
4204 debug( ' total tax lines = '|| p_select_rec.total_tax_amount, MSG_LEVEL_DEBUG );
4205 debug( ' total freight lines = '|| p_select_rec.total_freight_amount, MSG_LEVEL_DEBUG );
4206 debug( ' lines rem = '|| l_line_remaining, MSG_LEVEL_DEBUG );
4207 debug( ' tax rem = '|| l_tax_remaining, MSG_LEVEL_DEBUG );
4208 debug( ' freight rem = '|| l_freight_remaining, MSG_LEVEL_DEBUG );
4209
4210 -- Determine how much of adjustment amount is tax and freight
4211
4212 l_total_tax_adj := arpcurr.currround((l_tax_remaining /
4213 (l_line_remaining +
4214 l_tax_remaining +
4215 l_freight_remaining)
4216 * l_amount),
4217 p_select_rec.ps_currency_code);
4218
4219 l_total_frt_adj := arpcurr.currround((l_freight_remaining /
4220 (l_line_remaining +
4221 l_tax_remaining +
4222 l_freight_remaining)
4223 * l_amount),
4224 p_select_rec.ps_currency_code);
4225
4226 l_total_line_adj := l_amount - (l_total_tax_adj + l_total_frt_adj);
4227
4228 debug( ' total line adj = '||l_total_line_adj, MSG_LEVEL_DEBUG );
4229 debug( ' total tax adj amount = '||l_total_tax_adj, MSG_LEVEL_DEBUG );
4230 debug( ' total freight adj amt= '||l_total_frt_adj, MSG_LEVEL_DEBUG );
4231
4232 ELSE
4233
4234 debug( ' allocating lines only (original logic) ');
4235
4236 l_amount := LEAST( l_commitment_bal, l_line_remaining);
4237
4238 debug( ' total_line_amount='||p_select_rec.total_line_amount,
4239 MSG_LEVEL_DEBUG );
4240 debug( ' line remaining = ' || l_line_remaining);
4241 debug( ' l_commitment_bal='||l_commitment_bal, MSG_LEVEL_DEBUG );
4242 debug( ' l_amount='||l_amount, MSG_LEVEL_DEBUG );
4243
4244 -- line and total adjustment are equal
4245 l_total_line_adj := l_amount;
4246 l_total_tax_adj := null;
4247 l_total_frt_adj := null;
4248
4249 debug( ' total line adj = '||l_total_line_adj, MSG_LEVEL_DEBUG );
4250 debug( ' total tax adj = '||l_total_tax_adj, MSG_LEVEL_DEBUG );
4251 debug( ' total freight adj = '||l_total_frt_adj, MSG_LEVEL_DEBUG );
4252
4253 END if;
4254
4255 --------------------------------------------------------------------
4256 -- Distribute amounts using percents
4257 -- 1483656 - The original logic split the entire adjustment
4258 -- amount across the periods and it was assumed to be
4259 -- equal to the line amount. We now split the line
4260 -- and conditionally the tax and freight. Then
4261 -- figure the total adjustment amount in the loop below as
4262 -- total = line + tax + frt
4263 --------------------------------------------------------------------
4264
4265 /* Bug 3856079 - replaced p_percent_t with l_line_percent_t,
4266 l_tax_percent_t, and l_frt_percent_t so the allocation
4267 of amounts is driven from the PS.ADR columns
4268
4269 Bug 4192201 - l_line_percent_t is defined only for DEP,
4270 for GUAR still use p_percent_t */
4271
4272 IF (p_select_rec.commitment_type = 'DEP')
4273 THEN
4274 distribute_amount(
4275 p_number_records,
4276 p_select_rec.ps_currency_code,
4277 l_total_line_adj,
4278 l_line_percent_t,
4279 p_line_adj_t);
4280 ELSE
4281 distribute_amount(
4282 p_number_records,
4283 p_select_rec.ps_currency_code,
4284 l_total_line_adj,
4285 p_percent_t,
4286 p_line_adj_t);
4287 END IF;
4288
4289 IF( p_select_rec.allocate_tax_freight = 'Y') THEN
4290
4291 distribute_amount(
4292 p_number_records,
4293 p_select_rec.ps_currency_code,
4294 l_total_tax_adj,
4295 l_tax_percent_t,
4296 p_tax_adj_t);
4297
4298 distribute_amount(
4299 p_number_records,
4300 p_select_rec.ps_currency_code,
4301 l_total_frt_adj,
4302 l_frt_percent_t,
4303 p_frt_adj_t);
4304
4305 END IF;
4306
4307 FOR i IN 0..p_number_records - 1 LOOP
4308
4309 --------------------------------------------------------------------
4310 -- 1483656 - figure total adjustment based on sum of line, tax, and
4311 -- freight.
4312 --------------------------------------------------------------------
4313
4314 p_adj_amount_t(i) := p_line_adj_t(i);
4315
4316 debug( '=== TERM ' || i || ' ===', MSG_LEVEL_DEBUG);
4317 debug( ' adj amount = '||p_adj_amount_t(i), MSG_LEVEL_DEBUG );
4318 debug( ' line adj = '||p_line_adj_t(i), MSG_LEVEL_DEBUG );
4319
4320 /* 1483656 - Any attempts to access fields or tables
4321 that have not been initialized will result in an
4322 exception. So, all of the following logic must be
4323 cased to prevent failure on non-TF commitments */
4324
4325 IF (p_select_rec.allocate_tax_freight = 'Y') THEN
4326
4327 p_adj_amount_t(i) := p_adj_amount_t(i) + p_tax_adj_t(i)
4328 + p_frt_adj_t(i);
4329
4330 p_ps_tax_rem_t(i) := p_ps_tax_rem_t(i) - p_tax_adj_t(i);
4331 p_ps_freight_rem_t(i) := p_ps_freight_rem_t(i) - p_frt_adj_t(i);
4332
4333 debug( ' tax adj = '||p_tax_adj_t(i), MSG_LEVEL_DEBUG);
4334 debug( ' freight adj = '||p_frt_adj_t(i), MSG_LEVEL_DEBUG );
4335
4336 ELSE
4337
4338 /* Set tax and freight adjustment amounts to NULL
4339 to prevent errors during insert of ADJ row */
4340
4341 p_tax_adj_t(i) := NULL;
4342 p_frt_adj_t(i) := NULL;
4343
4344 END IF;
4345
4346 --------------------------------------------------------------------
4347 -- Update line_items_rem
4348 --------------------------------------------------------------------
4349 p_ps_line_rem_t( i ) := p_ps_line_rem_t( i ) - p_line_adj_t( i );
4350
4351 --------------------------------------------------------------------
4352 -- Update amount_adj
4353 --------------------------------------------------------------------
4354 p_ps_amount_adjusted_t( i ) :=
4355 p_ps_amount_adjusted_t( i ) - p_adj_amount_t( i );
4356
4357 --------------------------------------------------------------------
4358 -- Compute new acctd_adr (aracc)
4359 --------------------------------------------------------------------
4360 arp_util.calc_acctd_amount(
4361 p_system_info.base_currency,
4362 NULL, -- precision
4363 NULL, -- mau
4364 p_select_rec.ps_exchange_rate,
4365 '-', -- type
4366 p_ps_amount_due_rem_t( i ), -- master_from
4367 p_ps_acctd_amt_due_rem_t( i ), -- acctd_master_from
4368 p_adj_amount_t( i ), -- detail
4369 l_new_ps_adr, -- master_to
4370 l_new_ps_acctd_adr, -- acctd_master_to
4371 l_new_acctd_adj_amount -- acctd_detail
4372 );
4373
4374 --------------------------------------------------------------------
4375 -- Update amt_due_rem, acctd_amt_due_rem
4376 --------------------------------------------------------------------
4377 p_ps_amount_due_rem_t( i ) := l_new_ps_adr;
4378 p_ps_acctd_amt_due_rem_t( i ) := l_new_ps_acctd_adr;
4379 p_acctd_adj_amount_t( i ) := l_new_acctd_adj_amount;
4380
4381 END LOOP;
4382
4383
4384 -------------------------------------------------------------
4385 -- Insert into ar_adjustments
4386 -------------------------------------------------------------
4387
4388 FOR i IN 0..p_number_records - 1 LOOP
4389
4390 -------------------------------------------------------------
4391 -- Skip rows with $0 amounts (do not insert $0 adjustments)
4392 -------------------------------------------------------------
4393 IF( p_adj_amount_t( i ) = 0 ) THEN
4394 GOTO skip;
4395 END IF;
4396
4397 -------------------------------------------------------------
4398 -- Bind vars
4399 -------------------------------------------------------------
4400 BEGIN
4401 debug( ' Binding insert_adj_c', MSG_LEVEL_DEBUG );
4402
4403 dbms_sql.bind_variable( p_insert_adj_c,
4404 'user_id',
4405 p_profile_info.user_id );
4406
4407 dbms_sql.bind_variable( p_insert_adj_c,
4408 'login_id',
4409 p_profile_info.conc_login_id );
4410
4411 dbms_sql.bind_variable( p_insert_adj_c,
4412 'request_id',
4413 p_profile_info.request_id );
4414
4415 dbms_sql.bind_variable( p_insert_adj_c,
4416 'application_id',
4417 p_profile_info.application_id );
4418
4419 dbms_sql.bind_variable( p_insert_adj_c,
4420 'program_id',
4421 p_profile_info.conc_program_id );
4422
4423
4424 dbms_sql.bind_variable(
4425 p_insert_adj_c,
4426 'set_of_books_id',
4427 p_select_rec.set_of_books_id );
4428
4429 dbms_sql.bind_variable( p_insert_adj_c,
4430 'trx_date',
4431 p_select_rec.trx_date );
4432
4433 dbms_sql.bind_variable( p_insert_adj_c,
4434 'gl_date',
4435 p_select_rec.gl_date );
4436
4437 dbms_sql.bind_variable( p_insert_adj_c,
4438 'code_combination_id',
4439 p_select_rec.code_combination_id );
4440
4441 dbms_sql.bind_variable( p_insert_adj_c,
4442 'adjusted_trx_id',
4443 p_select_rec.adjusted_trx_id );
4444
4445 dbms_sql.bind_variable( p_insert_adj_c,
4446 'payment_schedule_id',
4447 p_ps_id_t( i ) );
4448
4449 dbms_sql.bind_variable( p_insert_adj_c,
4450 'subsequent_trx_id',
4451 p_select_rec.subsequent_trx_id );
4452
4453 dbms_sql.bind_variable( p_insert_adj_c,
4454 'post_to_gl_flag',
4455 p_select_rec.post_to_gl_flag );
4456
4457 dbms_sql.bind_variable( p_insert_adj_c,
4458 'adj_amount',
4459 p_adj_amount_t( i ) );
4460
4461 dbms_sql.bind_variable( p_insert_adj_c,
4462 'acctd_adj_amount',
4463 p_acctd_adj_amount_t( i ) );
4464
4465 /* VAT changes */
4466 SELECT ar_adjustments_s.nextval
4467 INTO l_adjustment_id
4468 FROM dual;
4469
4470 dbms_sql.bind_variable( p_insert_adj_c,
4471 'adjustment_id',
4472 l_adjustment_id );
4473
4474 -- 1483656 - allocating tax and freight against commitments
4475 dbms_sql.bind_variable (p_insert_adj_c,
4476 'tax_adj_amount',
4477 p_tax_adj_t(i) );
4478
4479 dbms_sql.bind_variable (p_insert_adj_c,
4480 'frt_adj_amount',
4481 p_frt_adj_t(i) );
4482
4483 dbms_sql.bind_variable (p_insert_adj_c,
4484 'line_adj_amount',
4485 p_line_adj_t(i) );
4486
4487 dbms_sql.bind_variable (p_insert_adj_c,
4488 'adjust_type',
4489 p_select_rec.adjustment_type);
4490
4491 --begin anuj
4492 dbms_sql.bind_variable (p_insert_adj_c,
4493 'org_id',
4494 arp_standard.sysparm.org_id /* SSA changes anuj */);
4495 --end anuj
4496
4497 EXCEPTION
4498 WHEN OTHERS THEN
4499 debug( 'EXCEPTION: Error in binding insert_adj_c',
4500 MSG_LEVEL_BASIC );
4501 RAISE;
4502 END;
4503
4504 -------------------------------------------------------------
4505 -- Execute
4506 -------------------------------------------------------------
4507 BEGIN
4508 debug( ' Inserting adjustments', MSG_LEVEL_DEBUG );
4509 l_ignore := dbms_sql.execute( p_insert_adj_c );
4510 debug( to_char(l_ignore) || ' row(s) inserted',
4511 MSG_LEVEL_DEBUG );
4512
4513 /*-------------------------------------------+
4514 | Call central MRC library for insertion |
4515 | into MRC tables |
4516 +-------------------------------------------*/
4517
4518 ar_mrc_engine.maintain_mrc_data(
4519 p_event_mode => 'INSERT',
4520 p_table_name => 'AR_ADJUSTMENTS',
4521 p_mode => 'SINGLE',
4522 p_key_value => l_adjustment_id);
4523
4524 EXCEPTION
4525 WHEN OTHERS THEN
4526 debug( 'EXCEPTION: Error executing insert ra stmt',
4527 MSG_LEVEL_BASIC );
4528 RAISE;
4529 END;
4530
4531
4532 -------------------------------------------------------------
4533 -- Update ar_payment_schedules
4534 -------------------------------------------------------------
4535
4536 -------------------------------------------------------------
4537 -- Bind vars
4538 -------------------------------------------------------------
4539 BEGIN
4540 debug( ' Binding update_ps_c', MSG_LEVEL_DEBUG );
4541
4542 dbms_sql.bind_variable( p_update_ps_c,
4543 'user_id',
4544 p_profile_info.user_id );
4545
4546 dbms_sql.bind_variable( p_update_ps_c,
4547 'login_id',
4548 p_profile_info.conc_login_id );
4549
4550 dbms_sql.bind_variable( p_update_ps_c,
4551 'amount_due_remaining',
4552 p_ps_amount_due_rem_t( i ) );
4553
4554 dbms_sql.bind_variable( p_update_ps_c,
4555 'gl_date_closed',
4556 p_select_rec.gl_date_closed );
4557
4558 dbms_sql.bind_variable( p_update_ps_c,
4559 'actual_date_closed',
4560 p_select_rec.actual_date_closed );
4561
4562 dbms_sql.bind_variable( p_update_ps_c,
4563 'amount_line_items_remaining',
4564 p_ps_line_rem_t( i ) );
4565
4566 dbms_sql.bind_variable( p_update_ps_c,
4567 'amount_adjusted',
4568 p_ps_amount_adjusted_t( i ) );
4569
4570 dbms_sql.bind_variable( p_update_ps_c,
4571 'acctd_amount_due_remaining',
4572 p_ps_acctd_amt_due_rem_t( i ) );
4573
4574 dbms_sql.bind_variable( p_update_ps_c,
4575 'payment_schedule_id',
4576 p_ps_id_t( i ) );
4577
4578 /* 1483656 - Commitments Project */
4579 dbms_sql.bind_variable( p_update_ps_c,
4580 'tax_remaining',
4581 p_ps_tax_rem_t( i ) );
4582
4583 dbms_sql.bind_variable( p_update_ps_c,
4584 'freight_remaining',
4585 p_ps_freight_rem_t( i ) );
4586
4587
4588 EXCEPTION
4589 WHEN OTHERS THEN
4590 debug( 'EXCEPTION: Error in binding update_ps_c',
4591 MSG_LEVEL_BASIC );
4592 RAISE;
4593 END;
4594
4595 -------------------------------------------------------------
4596 -- Execute
4597 -------------------------------------------------------------
4598 BEGIN
4599 debug( ' Updating invoice payment schedules', MSG_LEVEL_DEBUG );
4600 l_ignore := dbms_sql.execute( p_update_ps_c );
4601 debug( to_char(l_ignore) || ' row(s) updated',
4602 MSG_LEVEL_DEBUG );
4603
4604 /*-------------------------------------------+
4605 | Call central MRC library for update |
4606 | of AR_PAYMENT_SCHEDULES |
4607 +-------------------------------------------*/
4608
4609 ar_mrc_engine.maintain_mrc_data(
4610 p_event_mode => 'UPDATE',
4611 p_table_name => 'AR_PAYMENT_SCHEDULES',
4612 p_mode => 'SINGLE',
4613 p_key_value => p_ps_id_t( i ));
4614
4615 EXCEPTION
4616 WHEN OTHERS THEN
4617 debug( 'EXCEPTION: Error executing update ps stmt',
4618 MSG_LEVEL_BASIC );
4619 RAISE;
4620 END;
4621
4622 -------------------------------------------------------------
4623 -- Insert into ar_distributions
4624 -------------------------------------------------------------
4625 /* VAT changes : create acct entry */
4626 l_ae_doc_rec.document_type := 'ADJUSTMENT';
4627 l_ae_doc_rec.document_id := l_adjustment_id;
4628 l_ae_doc_rec.accounting_entity_level := 'ONE';
4629 l_ae_doc_rec.source_table := 'ADJ';
4630 l_ae_doc_rec.source_id := l_adjustment_id;
4631 /* Pass CCID derived by autoaccounting and COMMITMENT flag */
4632
4633 --Bug 1329091 - PS is updated before Accounting Engine Call
4634
4635 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
4636
4637 l_ae_doc_rec.source_id_old := p_select_rec.code_combination_id;
4638 l_ae_doc_rec.other_flag := 'COMMITMENT';
4639 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
4640
4641 <<skip>>
4642 null;
4643
4644 END LOOP;
4645
4646
4647
4648 print_fcn_label2('arp_maintain_ps2.process_ups_data()-' );
4649
4650 EXCEPTION
4651 WHEN OTHERS THEN
4652 debug( 'EXCEPTION: arp_maintain_ps2.process_ups_data()',
4653 MSG_LEVEL_BASIC );
4654 RAISE;
4655 END process_ups_data;
4656
4657
4658 ----------------------------------------------------------------------------
4659 PROCEDURE insert_child_adj_private(
4660 p_system_info IN arp_trx_global.system_info_rec_type,
4661 p_profile_info IN arp_trx_global.profile_rec_type,
4662 p_customer_trx_id IN BINARY_INTEGER,
4663 p_adj_date IN DATE,
4664 p_gl_date IN DATE) IS
4665
4666
4667 l_ignore INTEGER;
4668
4669 l_old_trx_id BINARY_INTEGER;
4670 l_customer_trx_id BINARY_INTEGER;
4671
4672 l_old_adjusted_trx_id BINARY_INTEGER;
4673 l_adjusted_trx_id BINARY_INTEGER;
4674
4675 l_load_ps_tables BOOLEAN := FALSE;
4676
4677 --
4678 -- ps attributes
4679 --
4680 l_ps_id_t id_table_type;
4681 l_ps_amount_due_rem_t number_table_type;
4682 l_ps_acctd_amt_due_rem_t number_table_type;
4683 l_ps_line_rem_t number_table_type;
4684 l_ps_amount_adjusted_t number_table_type;
4685 l_percent_t number_table_type;
4686 l_ps_tax_rem_t number_table_type;
4687 l_ps_freight_rem_t number_table_type;
4688
4689 --
4690 -- Derived attributes
4691 --
4692 l_adj_amount_t number_table_type;
4693 l_acctd_adj_amount_t number_table_type;
4694 l_line_adj_t number_table_type;
4695 l_tax_adj_t number_table_type;
4696 l_frt_adj_t number_table_type;
4697
4698 l_table_index BINARY_INTEGER := 0;
4699
4700 l_select_rec select_ups_rec_type;
4701
4702 -----------------------------------------------------------------------
4703 PROCEDURE load_tables( p_select_rec IN select_ups_rec_type ) IS
4704
4705 BEGIN
4706 print_fcn_label2('arp_maintain_ps2.load_tables()+' );
4707
4708 /* DEBUG */
4709 -- arp_standard.enable_file_debug('/sqlcom/out/omvispt3','ARTEMP2B.log');
4710
4711 l_ps_id_t( l_table_index ) := p_select_rec.payment_schedule_id;
4712 l_ps_amount_due_rem_t( l_table_index ) :=
4713 p_select_rec.amount_due_remaining;
4714 l_ps_acctd_amt_due_rem_t( l_table_index ) :=
4715 p_select_rec.acctd_amt_due_rem;
4716 l_ps_line_rem_t( l_table_index ) :=
4717 p_select_rec.line_remaining;
4718 -- 1483656
4719 l_ps_tax_rem_t( l_table_index ) :=
4720 p_select_rec.tax_remaining;
4721 l_ps_freight_rem_t( l_table_index ) :=
4722 p_select_rec.freight_remaining;
4723
4724 l_ps_amount_adjusted_t( l_table_index ) :=
4725 p_select_rec.amount_adjusted;
4726
4727 l_percent_t( l_table_index ) := p_select_rec.percent;
4728
4729 l_table_index := l_table_index + 1;
4730
4731 print_fcn_label2('arp_maintain_ps2.load_tables()-' );
4732
4733 EXCEPTION
4734 WHEN OTHERS THEN
4735 debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
4736 MSG_LEVEL_BASIC );
4737 RAISE;
4738 END load_tables;
4739
4740
4741 -----------------------------------------------------------------------
4742 PROCEDURE clear_inv_tables IS
4743
4744 BEGIN
4745 print_fcn_label2('arp_maintain_ps2.clear_cm_tables()+' );
4746
4747 l_adj_amount_t := null_number_t;
4748 l_line_adj_t := null_number_t;
4749 l_tax_adj_t := null_number_t;
4750 l_frt_adj_t := null_number_t;
4751 l_acctd_adj_amount_t := null_number_t;
4752
4753 print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
4754
4755 EXCEPTION
4756 WHEN OTHERS THEN
4757 debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
4758 MSG_LEVEL_BASIC );
4759 RAISE;
4760 END clear_inv_tables;
4761
4762
4763 -----------------------------------------------------------------------
4764 PROCEDURE clear_all_tables IS
4765
4766 BEGIN
4767 print_fcn_label2('arp_maintain_ps2.clear_all_tables()+' );
4768
4769
4770
4771 l_ps_id_t := null_id_t;
4772 l_ps_amount_due_rem_t := null_number_t;
4773 l_ps_acctd_amt_due_rem_t := null_number_t;
4774 l_ps_line_rem_t := null_number_t;
4775 l_ps_amount_adjusted_t := null_number_t;
4776 l_percent_t := null_number_t;
4777 l_ps_tax_rem_t := null_number_t;
4778 l_ps_freight_rem_t := null_number_t;
4779
4780 clear_inv_tables;
4781
4782 l_table_index := 0;
4783
4784 print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
4785
4786 EXCEPTION
4787 WHEN OTHERS THEN
4788 debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
4789 MSG_LEVEL_BASIC );
4790 RAISE;
4791 END clear_all_tables;
4792
4793 -----------------------------------------------------------------------
4794 FUNCTION is_new_id( p_old_id BINARY_INTEGER, p_new_id BINARY_INTEGER )
4795 RETURN BOOLEAN IS
4796 BEGIN
4797
4798 RETURN( p_old_id IS NULL OR p_old_id <> p_new_id );
4799
4800 END is_new_id;
4801
4802 -----------------------------------------------------------------------
4803 FUNCTION is_new_inv RETURN BOOLEAN IS
4804 BEGIN
4805
4806 RETURN( l_old_trx_id IS NULL OR l_old_trx_id <> l_customer_trx_id );
4807
4808 END is_new_inv;
4809
4810 -----------------------------------------------------------------------
4811 FUNCTION is_new_adjusted_trx RETURN BOOLEAN IS
4812 BEGIN
4813
4814 RETURN( l_old_adjusted_trx_id IS NULL OR
4815 l_old_adjusted_trx_id <> l_adjusted_trx_id );
4816
4817 END is_new_adjusted_trx;
4818
4819
4820 BEGIN
4821
4822 print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()+' );
4823
4824 --
4825 clear_all_tables;
4826 --
4827
4828 IF( NOT( dbms_sql.is_open( ups_select_c ) AND
4829 dbms_sql.is_open( ups_insert_adj_c ) AND
4830 dbms_sql.is_open( ups_update_ps_c ) )) THEN
4831
4832 build_ups_sql(
4833 system_info,
4834 profile_info,
4835 ups_select_c,
4836 ups_insert_adj_c,
4837 ups_update_ps_c );
4838 END IF;
4839
4840 --
4841 define_ups_select_columns( ups_select_c, l_select_rec );
4842
4843 ---------------------------------------------------------------
4844 -- Bind variables
4845 ---------------------------------------------------------------
4846 dbms_sql.bind_variable( ups_select_c,
4847 'customer_trx_id',
4848 p_customer_trx_id );
4849
4850 /* bug 3431804 - bind dates for latecoming deposits */
4851 dbms_sql.bind_variable( ups_select_c,
4852 'gl_date',
4853 p_gl_date);
4854
4855 dbms_sql.bind_variable( ups_select_c,
4856 'apply_date',
4857 p_adj_date);
4858 ---------------------------------------------------------------
4859 -- Execute sql
4860 ---------------------------------------------------------------
4861 debug( ' Executing select sql', MSG_LEVEL_DEBUG );
4862
4863 BEGIN
4864 l_ignore := dbms_sql.execute( ups_select_c );
4865
4866 EXCEPTION
4867 WHEN OTHERS THEN
4868 debug( 'EXCEPTION: Error executing select sql',
4869 MSG_LEVEL_BASIC );
4870 RAISE;
4871 END;
4872
4873
4874 ---------------------------------------------------------------
4875 -- Fetch rows
4876 ---------------------------------------------------------------
4877 BEGIN
4878 LOOP
4879
4880 IF dbms_sql.fetch_rows( ups_select_c ) > 0 THEN
4881
4882 debug(' Fetched a row', MSG_LEVEL_DEBUG );
4883
4884 -----------------------------------------------
4885 -- Load row into record
4886 -----------------------------------------------
4887 dbms_sql.column_value( ups_select_c, 24, l_customer_trx_id );
4888 dbms_sql.column_value( ups_select_c, 5, l_adjusted_trx_id );
4889
4890 -----------------------------------------------
4891 -- Check if adjusted trx or invoice changed
4892 -----------------------------------------------
4893 IF( is_new_adjusted_trx OR is_new_inv ) THEN
4894
4895 debug( ' new adjusted trx or invoice',
4896 MSG_LEVEL_DEBUG );
4897
4898 -----------------------------------------------
4899 -- Check if adjusted trx changed
4900 -----------------------------------------------
4901 IF( is_new_adjusted_trx ) THEN
4902
4903 debug( ' new adjusted trx', MSG_LEVEL_DEBUG );
4904
4905 ---------------------------------------------------
4906 -- Start loading ps tables for new adjusted trx
4907 ---------------------------------------------------
4908 l_load_ps_tables := TRUE;
4909
4910 END IF;
4911
4912 IF( l_old_adjusted_trx_id IS NOT NULL ) THEN
4913
4914 debug( ' process1', MSG_LEVEL_DEBUG );
4915
4916 process_ups_data(
4917 system_info,
4918 profile_info,
4919 ups_insert_adj_c,
4920 ups_update_ps_c,
4921 l_select_rec,
4922 l_table_index,
4923 l_ps_id_t,
4924 l_ps_amount_due_rem_t,
4925 l_ps_acctd_amt_due_rem_t,
4926 l_ps_line_rem_t,
4927 l_ps_amount_adjusted_t,
4928 l_adj_amount_t,
4929 l_acctd_adj_amount_t,
4930 l_percent_t,
4931 l_ps_tax_rem_t,
4932 l_ps_freight_rem_t,
4933 l_line_adj_t,
4934 l_tax_adj_t,
4935 l_frt_adj_t);
4936
4937 END IF;
4938
4939 -----------------------------------------------
4940 -- Check if new adjusted trx
4941 -----------------------------------------------
4942 IF( is_new_adjusted_trx ) THEN
4943
4944 clear_all_tables;
4945
4946 l_old_adjusted_trx_id := l_adjusted_trx_id;
4947 l_old_trx_id := l_customer_trx_id;
4948
4949 -----------------------------------------------
4950 -- Else new invoice
4951 -----------------------------------------------
4952 ELSE
4953
4954 clear_inv_tables;
4955
4956 l_load_ps_tables := FALSE;
4957 l_old_trx_id := l_customer_trx_id;
4958
4959 END IF;
4960
4961 END IF; -- END adjusted trx or inv changed
4962
4963 get_ups_column_values( ups_select_c, l_select_rec );
4964 dump_ups_select_rec( l_select_rec );
4965
4966
4967 IF( l_load_ps_tables ) THEN
4968 load_tables( l_select_rec );
4969 END IF;
4970
4971 -- >> dump tables
4972
4973 ELSE
4974 -----------------------------------------------
4975 -- No more rows to fetch, process last set
4976 -----------------------------------------------
4977
4978 debug( ' process2', MSG_LEVEL_DEBUG );
4979
4980 process_ups_data(
4981 system_info,
4982 profile_info,
4983 ups_insert_adj_c,
4984 ups_update_ps_c,
4985 l_select_rec,
4986 l_table_index,
4987 l_ps_id_t,
4988 l_ps_amount_due_rem_t,
4989 l_ps_acctd_amt_due_rem_t,
4990 l_ps_line_rem_t,
4991 l_ps_amount_adjusted_t,
4992 l_adj_amount_t,
4993 l_acctd_adj_amount_t,
4994 l_percent_t,
4995 l_ps_tax_rem_t,
4996 l_ps_freight_rem_t,
4997 l_line_adj_t,
4998 l_tax_adj_t,
4999 l_frt_adj_t);
5000
5001
5002 EXIT;
5003
5004 END IF;
5005
5006
5007 END LOOP;
5008
5009 EXCEPTION
5010 WHEN OTHERS THEN
5011 debug( 'EXCEPTION: Error fetching select cursor',
5012 MSG_LEVEL_BASIC );
5013 RAISE;
5014
5015 END;
5016
5017 print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()-' );
5018
5019 EXCEPTION
5020 WHEN OTHERS THEN
5021 debug( 'EXCEPTION: arp_maintain_ps2.insert_child_adj_private()',
5022 MSG_LEVEL_BASIC );
5023 RAISE;
5024
5025 END insert_child_adj_private;
5026
5027 ----------------------------------------------------------------------------
5028 /* Bug 4642526 - new wrapper for insert_child_adj_private */
5029 PROCEDURE insert_child_adj_private(p_customer_trx_id IN BINARY_INTEGER,
5030 p_adj_date IN DATE DEFAULT NULL,
5031 p_gl_date IN DATE DEFAULT NULL )
5032 IS
5033 x_system_info arp_trx_global.system_info_rec_type;
5034 x_profile_info arp_trx_global.profile_rec_type;
5035 BEGIN
5036
5037 insert_child_adj_private(x_system_info, x_profile_info,
5038 p_customer_trx_id, p_adj_date, p_gl_date);
5039
5040 END;
5041
5042 ----------------------------------------------------------------------------
5043 --
5044 -- PROCEDURE NAME: build_iad_sql
5045 --
5046 -- DECSRIPTION:
5047 --
5048 -- ARGUMENTS:
5049 -- IN:
5050 -- system_info
5051 -- profile_info
5052 --
5053 -- IN/OUT:
5054 -- select_c
5055 -- insert_ps_c
5056 -- insert_ra_c
5057 -- update_ps_c
5058 --
5059 -- OUT:
5060 --
5061 -- RETURNS:
5062 --
5063 -- NOTES:
5064 --
5065 -- HISTORY:
5066 --
5067 ----------------------------------------------------------------------------
5068 PROCEDURE build_iad_sql(
5069 p_system_info IN arp_trx_global.system_info_rec_type,
5070 p_profile_info IN arp_trx_global.profile_rec_type,
5071 p_select_c IN OUT NOCOPY INTEGER,
5072 p_insert_adj_c IN OUT NOCOPY INTEGER,
5073 p_update_ps_c IN OUT NOCOPY INTEGER ) IS
5074
5075 l_insert_adj_sql VARCHAR2(2000);
5076 l_update_ps_sql VARCHAR2(2000);
5077 l_select_sql VARCHAR2(8000);
5078
5079
5080 BEGIN
5081
5082 print_fcn_label( 'arp_maintain_ps2.build_iad_sql()+' );
5083
5084 ------------------------------------------------
5085 -- Select sql
5086 ------------------------------------------------
5087 l_select_sql :=
5088 'SELECT
5089 ctl.set_of_books_id,
5090 ct.trx_date,
5091 /* nvl(ps.amount_line_items_remaining, 0), */
5092 nvl(ctlgd.gl_date, ct.trx_date),
5093 ctlgd_com.code_combination_id,
5094 decode(ctt_com.type,
5095 ''DEP'', ct_inv.customer_trx_id,
5096 ct_com.customer_trx_id),
5097 ps.payment_schedule_id,
5098 ctl.customer_trx_id,
5099 ''Y'', /* bugfix 2614759. ctt.post_to_gl */
5100 greatest(nvl(max(decode(ra.confirmed_flag,
5101 ''Y'', ra.apply_date,
5102 null, decode(ra.receivable_application_id,
5103 null, ct.trx_date,
5104 ra.apply_date),
5105 ct.trx_date)),
5106 ct.trx_date),
5107 nvl(max(decode(adj.status,
5108 ''A'',adj.apply_date,
5109 ct.trx_date)),
5110 ct.trx_date),
5111 ct.trx_date),
5112 greatest(nvl(max(decode(ra.confirmed_flag,
5113 ''Y'', ra.gl_date,
5114 null, decode(ra.receivable_application_id,
5115 null, nvl(ctlgd.gl_date,
5116 ct.trx_date),
5117 ra.gl_date),
5118 nvl(ctlgd.gl_date, ct.trx_date))),
5119 nvl(ctlgd.gl_date,ct.trx_date)),
5120 nvl(max(decode(adj.status,
5121 ''A'',adj.gl_date,
5122 nvl(ctlgd.gl_date,
5123 ct.trx_date))),
5124 nvl(ctlgd.gl_date,ct.trx_date)),
5125 nvl(ctlgd.gl_date, ct.trx_date)),
5126 c.minimum_accountable_unit,
5127 c.precision,
5128 sum(ctl.extended_amount) /
5129 (count(distinct ps.payment_schedule_id) *
5130 count(distinct ps_inv.payment_schedule_id) *
5131 count(distinct nvl(adj.adjustment_id, -9.9)) *
5132 count(distinct nvl(adjd.adjustment_id, -9.9)) *
5133 count(distinct nvl(ra.receivable_application_id, -9.9))),
5134 nvl(sum(decode(adj.adjustment_type, ''C'', adj.amount, 0)),0) /
5135 (count(distinct ps.payment_schedule_id) *
5136 count(distinct ps_inv.payment_schedule_id) *
5137 count(distinct ctl.customer_trx_line_id) *
5138 count(distinct nvl(ra.receivable_application_id, -9.9))),
5139 decode(ctt_com.type, ''DEP'', 1, 0),
5140 /* nvl(ps.amount_line_items_remaining, 0), */
5141 /* 0, */
5142 ct_inv.customer_trx_id,
5143 /* null, */
5144 nvl(ps.amount_line_items_remaining, 0),
5145 ps.amount_due_remaining,
5146 ps.acctd_amount_due_remaining,
5147 nvl(ps.amount_adjusted, 0),
5148 c_ps.precision,
5149 c_ps.minimum_accountable_unit,
5150 decode(ctt_com.type, ''DEP'', ct_inv.exchange_rate, ct_com.exchange_rate),
5151 sum(nvl(ps_inv.amount_line_items_remaining, 0))/
5152 (count(distinct ps.payment_schedule_id) *
5153 count(distinct ctl.customer_trx_line_id) *
5154 count(distinct nvl(adj.adjustment_id, -9.9)) *
5155 count(distinct nvl(ra.receivable_application_id, -9.9)) ),
5156 nvl(ps.amount_line_items_original, 0),
5157 /* 0 */
5158 decode(ctt_com.type,
5159 ''DEP'', ct_inv.invoice_currency_code,
5160 ct_com.invoice_currency_code),
5161 /* 1483656 */
5162 ctt_com.allocate_tax_freight,
5163 /* adj_type */
5164 DECODE(ctt_com.allocate_tax_freight, ''Y'', ''INVOICE'', ''LINE''),
5165 /* CM TAX and CM FREIGHT totals */
5166 ARPT_SQL_FUNC_UTIL.get_sum_of_trx_lines(ctl.customer_trx_id, ''TAX''),
5167 ARPT_SQL_FUNC_UTIL.get_sum_of_trx_lines(ctl.customer_trx_id, ''FREIGHT''),
5168 /* inv_line_adj, inv_tax_adj, inv_frt_adj */
5169 sum(nvl(decode(adjd.adjustment_type, ''C'', adjd.line_adjusted, 0),0)) /
5170 (count(distinct ps.payment_schedule_id) *
5171 count(distinct ps_inv.payment_schedule_id) *
5172 count(distinct ctl.customer_trx_line_id) *
5173 count(distinct adj.adjustment_id) *
5174 count(distinct nvl(ra.receivable_application_id, -9.9))),
5175 sum(nvl(decode(adjd.adjustment_type, ''C'', adjd.tax_adjusted, 0),0)) /
5176 (count(distinct ps.payment_schedule_id) *
5177 count(distinct ps_inv.payment_schedule_id) *
5178 count(distinct ctl.customer_trx_line_id) *
5179 count(distinct adj.adjustment_id) *
5180 count(distinct nvl(ra.receivable_application_id, -9.9))),
5181 sum(nvl(decode(adjd.adjustment_type, ''C'', adjd.freight_adjusted, 0),0)) /
5182 (count(distinct ps.payment_schedule_id) *
5183 count(distinct ps_inv.payment_schedule_id) *
5184 count(distinct ctl.customer_trx_line_id) *
5185 count(distinct adj.adjustment_id) *
5186 count(distinct nvl(ra.receivable_application_id, -9.9))),
5187 NVL(ps.tax_remaining, 0),
5188 NVL(ps.freight_remaining, 0),
5189 NVL(ps.tax_original, 0),
5190 NVL(ps.freight_original, 0)
5191 FROM
5192 ra_cust_trx_types ctt_com,
5193 ra_cust_trx_types ctt,
5194 ra_cust_trx_line_gl_dist ctlgd_com,
5195 ar_payment_schedules ps,
5196 ar_payment_schedules ps_inv,
5197 ar_receivable_applications ra,
5198 ar_adjustments adj,
5199 ar_adjustments adjd,
5200 fnd_currencies c,
5201 fnd_currencies c_ps,
5202 ra_customer_trx ct_com,
5203 ra_customer_trx ct_inv,
5204 ra_customer_trx_lines ctl,
5205 ra_customer_trx ct,
5206 ra_cust_trx_line_gl_dist ctlgd
5207 WHERE ct.customer_trx_id = :customer_trx_id
5208 and ctl.customer_trx_id = ct.customer_trx_id
5209 and ctlgd.customer_trx_id = ct.customer_trx_id
5210 and ctlgd.account_class = ''REC''
5211 and ctlgd.latest_rec_flag = ''Y''
5212 and ctl.line_type = ''LINE''
5213 and exists
5214 (select ''x''
5215 from ra_customer_trx h
5216 where h.customer_trx_id = ctl.customer_trx_id)
5217 and ct.invoice_currency_code = c.currency_code
5218 and ct.cust_trx_type_id = ctt.cust_trx_type_id
5219 and ctt.type = ''CM''
5220 and ctl.previous_customer_trx_id = ct_inv.customer_trx_id
5221 and ct_inv.initial_customer_trx_id = ct_com.customer_trx_id
5222 and ct_com.cust_trx_type_id = ctt_com.cust_trx_type_id
5223 and ct_com.customer_trx_id = ctlgd_com.customer_trx_id
5224 and ctlgd_com.account_class = ''REV''
5225 and ps.customer_trx_id =
5226 decode(ctt_com.type,
5227 ''DEP'', ct_inv.customer_trx_id,
5228 ct_com.customer_trx_id)
5229 and decode(ctt_com.type, ''DEP'', ct_inv.invoice_currency_code,
5230 ct_com.invoice_currency_code)
5231 = c_ps.currency_code
5232 and ps.customer_trx_id = ra.applied_customer_trx_id (+)
5233 and ps.customer_trx_id = adj.customer_trx_id (+)
5234 and ct_inv.customer_trx_id = ps_inv.customer_trx_id
5235 and decode(adj.subsequent_trx_id, null, ct_inv.customer_trx_id,
5236 adj.subsequent_trx_id) = ct_inv.customer_trx_id
5237 and ps.payment_schedule_id = adjd.payment_schedule_id (+)
5238 and adjd.adjustment_type (+) = ''C''
5239 and decode(ctt_com.type,''GUAR'', adjd.subsequent_trx_id,1)
5240 = decode(ctt_com.type,''GUAR'',ctl.previous_customer_trx_id,1)
5241 GROUP BY
5242 ctl.set_of_books_id,
5243 ctt_com.type,
5244 ctt_com.allocate_tax_freight,
5245 ctlgd.gl_date,
5246 ct.trx_date,
5247 ps.amount_line_items_remaining,
5248 ctlgd_com.code_combination_id,
5249 ct_inv.customer_trx_id,
5250 ct_com.customer_trx_id,
5251 ps.payment_schedule_id,
5252 ctl.customer_trx_id,
5253 /* Bugfix 2614759. comment out ctt.post_to_gl, */
5254 c.minimum_accountable_unit,
5255 c.precision,
5256 ps.amount_due_remaining,
5257 ps.amount_due_original,
5258 ps.acctd_amount_due_remaining,
5259 ps.amount_adjusted,
5260 ps.tax_original,
5261 ps.tax_remaining,
5262 ps.freight_original,
5263 ps.freight_remaining,
5264 c_ps.precision,
5265 c_ps.minimum_accountable_unit,
5266 ct_inv.exchange_rate,
5267 ct_com.exchange_rate,
5268 ps.terms_sequence_number,
5269 ps.amount_line_items_original,
5270 ct_inv.invoice_currency_code,
5271 ct_com.invoice_currency_code
5272 ORDER BY
5273 5 asc, /* adjusted_trx_id */
5274 ct_inv.customer_trx_id,
5275 ctl.customer_trx_id,
5276 ps.terms_sequence_number';
5277
5278
5279
5280 debug(' select_sql = ' || CRLF ||
5281 l_select_sql || CRLF,
5282 MSG_LEVEL_DEBUG);
5283 debug(' len(select_sql) = '||
5284 to_char(length(l_select_sql)) || CRLF,
5285 MSG_LEVEL_DEBUG);
5286
5287
5288 ------------------------------------------------
5289 -- Insert adj sql
5290 ------------------------------------------------
5291 --Bug 1544809 - Modified the string to include bind variable
5292 --adjustment_id instead of directly taking the value from
5293 --sequence. Bind variable :adjustment_id is expected while
5294 --assigning the bind variable
5295
5296 l_insert_adj_sql :=
5297 'INSERT INTO AR_ADJUSTMENTS
5298 (
5299 created_by,
5300 creation_date,
5301 last_updated_by,
5302 last_update_date,
5303 last_update_login,
5304 request_id,
5305 program_application_id,
5306 program_id,
5307 program_update_date,
5308 set_of_books_id,
5309 receivables_trx_id,
5310 automatically_generated,
5311 type,
5312 adjustment_type,
5313 status,
5314 apply_date,
5315 adjustment_id,
5316 gl_date,
5317 code_combination_id,
5318 customer_trx_id,
5319 payment_schedule_id,
5320 subsequent_trx_id,
5321 postable,
5322 adjustment_number,
5323 created_from,
5324 posting_control_id,
5325 amount,
5326 acctd_amount,
5327 line_adjusted,
5328 tax_adjusted,
5329 freight_adjusted
5330 ,org_id
5331 )
5332 VALUES
5333 (
5334 :user_id,
5335 sysdate,
5336 :user_id,
5337 sysdate,
5338 :login_id,
5339 :request_id,
5340 :application_id,
5341 :program_id,
5342 sysdate,
5343 :set_of_books_id,
5344 -1,
5345 ''Y'',
5346 :adj_type,
5347 ''C'',
5348 ''A'',
5349 :trx_date,
5350 :adjustment_id,
5351 :gl_date,
5352 :code_combination_id,
5353 :adjusted_trx_id,
5354 :payment_schedule_id,
5355 :subsequent_trx_id,
5356 :post_to_gl_flag,
5357 to_char(ar_adjustment_number_s.nextval),
5358 ''RAXTRX'',
5359 -3,
5360 -1 * :adj_amount,
5361 -1 * :acctd_adj_amount,
5362 -1 * :line_adjusted,
5363 -1 * :tax_adjusted,
5364 -1 * :freight_adjusted
5365 ,:org_id --arp_standard.sysparm.org_id /* SSA changes anuj */
5366 )';
5367
5368 debug(' insert_adj_sql = ' || CRLF ||
5369 l_insert_adj_sql || CRLF,
5370 MSG_LEVEL_DEBUG);
5371 debug(' len(insert_adj_sql) = '||
5372 to_char(length(l_insert_adj_sql)) || CRLF,
5373 MSG_LEVEL_DEBUG);
5374
5375
5376 ------------------------------------------------
5377 -- Update ps sql
5378 ------------------------------------------------
5379 -- Modified the update statement to incorporate the hard-coded date if the transaction is open - For Bug:5491085
5380 l_update_ps_sql :=
5381 'UPDATE AR_PAYMENT_SCHEDULES
5382 SET last_update_date = sysdate,
5383 last_updated_by = :user_id,
5384 last_update_login = :login_id,
5385 status = decode(:amount_due_remaining, 0, ''CL'', ''OP''),
5386 gl_date_closed =
5387 decode(:amount_due_remaining, 0, :gl_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
5388 actual_date_closed =
5389 decode(:amount_due_remaining, 0, :actual_date_closed,TO_DATE(''31-12-4712'',''DD-MM-YYYY'')),
5390 amount_due_remaining = :amount_due_remaining,
5391 acctd_amount_due_remaining = :acctd_amount_due_remaining,
5392 amount_line_items_remaining = :amount_line_items_remaining,
5393 amount_adjusted = :amount_adjusted,
5394 tax_remaining = :tax_remaining,
5395 freight_remaining = :freight_remaining
5396 WHERE payment_schedule_id = :payment_schedule_id';
5397
5398 debug(' update_ps_sql = ' || CRLF ||
5399 l_update_ps_sql || CRLF,
5400 MSG_LEVEL_DEBUG);
5401 debug(' len(update_ps_sql) = '||
5402 to_char(length(l_update_ps_sql)) || CRLF,
5403 MSG_LEVEL_DEBUG);
5404
5405
5406
5407
5408 ------------------------------------------------
5409 -- Parse sql stmts
5410 ------------------------------------------------
5411 BEGIN
5412 debug( ' Parsing stmts', MSG_LEVEL_DEBUG );
5413
5414 debug( ' Parsing insert_adj_c', MSG_LEVEL_DEBUG );
5415 p_insert_adj_c := dbms_sql.open_cursor;
5416 dbms_sql.parse( p_insert_adj_c, l_insert_adj_sql,
5417 dbms_sql.v7 );
5418
5419 debug( ' Parsing update_ps_c', MSG_LEVEL_DEBUG );
5420 p_update_ps_c := dbms_sql.open_cursor;
5421 dbms_sql.parse( p_update_ps_c, l_update_ps_sql,
5422 dbms_sql.v7 );
5423
5424 debug( ' Parsing select_c', MSG_LEVEL_DEBUG );
5425 p_select_c := dbms_sql.open_cursor;
5426 dbms_sql.parse( p_select_c, l_select_sql,
5427 dbms_sql.v7 );
5428
5429 EXCEPTION
5430 WHEN OTHERS THEN
5431 debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
5432 RAISE;
5433 END;
5434
5435 print_fcn_label( 'arp_maintain_ps2.build_iad_sql()-' );
5436
5437
5438 EXCEPTION
5439 WHEN OTHERS THEN
5440 debug( 'EXCEPTION: arp_maintain_ps2.build_iad_sql()',
5441 MSG_LEVEL_BASIC );
5442
5443 RAISE;
5444 END build_iad_sql;
5445
5446
5447 ----------------------------------------------------------------------------
5448 PROCEDURE define_iad_select_columns(
5449 p_select_c IN INTEGER,
5450 p_select_rec IN select_iad_rec_type ) IS
5451
5452 BEGIN
5453
5454 print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()+' );
5455
5456 dbms_sql.define_column( p_select_c, 1, p_select_rec.set_of_books_id );
5457 dbms_sql.define_column( p_select_c, 2, p_select_rec.trx_date );
5458 dbms_sql.define_column( p_select_c, 3, p_select_rec.gl_date );
5459 dbms_sql.define_column( p_select_c, 4, p_select_rec.code_combination_id );
5460 dbms_sql.define_column( p_select_c, 5, p_select_rec.adjusted_trx_id );
5461 dbms_sql.define_column( p_select_c, 6, p_select_rec.payment_schedule_id );
5462 dbms_sql.define_column( p_select_c, 7, p_select_rec.customer_trx_id );
5463 dbms_sql.define_column( p_select_c, 8,
5464 p_select_rec.post_to_gl_flag, 1 );
5465 dbms_sql.define_column( p_select_c, 9, p_select_rec.actual_date_closed );
5466 dbms_sql.define_column( p_select_c, 10, p_select_rec.gl_date_closed );
5467 dbms_sql.define_column( p_select_c, 11, p_select_rec.min_acc_unit );
5468 dbms_sql.define_column( p_select_c, 12, p_select_rec.precision );
5469 dbms_sql.define_column( p_select_c, 13,
5470 p_select_rec.total_cm_line_amount );
5471 dbms_sql.define_column( p_select_c, 14,
5472 p_select_rec.total_inv_adj_amount );
5473 dbms_sql.define_column( p_select_c, 15, p_select_rec.commitment_code );
5474 dbms_sql.define_column( p_select_c, 16, p_select_rec.invoice_trx_id );
5475 dbms_sql.define_column( p_select_c, 17, p_select_rec.ps_line_remaining );
5476 dbms_sql.define_column( p_select_c, 18,
5477 p_select_rec.ps_amount_due_remaining );
5478 dbms_sql.define_column( p_select_c, 19,
5479 p_select_rec.ps_acctd_amt_due_rem );
5480 dbms_sql.define_column( p_select_c, 20,
5481 p_select_rec.ps_amount_adjusted );
5482 dbms_sql.define_column( p_select_c, 21, p_select_rec.ps_precision );
5483 dbms_sql.define_column( p_select_c, 22, p_select_rec.ps_min_acc_unit );
5484 dbms_sql.define_column( p_select_c, 23, p_select_rec.ps_exchange_rate );
5485 dbms_sql.define_column( p_select_c, 24,
5486 p_select_rec.total_inv_line_remaining );
5487 dbms_sql.define_column( p_select_c, 25, p_select_rec.ps_line_original );
5488 dbms_sql.define_column( p_select_c, 26,
5489 p_select_rec.ps_currency_code, 15 );
5490 dbms_sql.define_column( p_select_c, 27, p_select_rec.allocate_tax_freight, 1);
5491 dbms_sql.define_column( p_select_c, 28, p_select_rec.adjustment_type, 8);
5492 dbms_sql.define_column( p_select_c, 29, p_select_rec.total_cm_tax_amount);
5493 dbms_sql.define_column( p_select_c, 30, p_select_rec.total_cm_frt_amount);
5494 dbms_sql.define_column( p_select_c, 31, p_select_rec.inv_line_adj);
5495 dbms_sql.define_column( p_select_c, 32, p_select_rec.inv_tax_adj);
5496 dbms_sql.define_column( p_select_c, 33, p_select_rec.inv_frt_adj);
5497 dbms_sql.define_column( p_select_c, 34, p_select_rec.ps_tax_remaining);
5498 dbms_sql.define_column( p_select_c, 35, p_select_rec.ps_freight_remaining);
5499 dbms_sql.define_column( p_select_c, 36, p_select_rec.ps_tax_original);
5500 dbms_sql.define_column( p_select_c, 37, p_select_rec.ps_freight_remaining);
5501
5502 print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()-' );
5503
5504 EXCEPTION
5505 WHEN OTHERS THEN
5506 debug('EXCEPTION: arp_maintain_ps2.define_iad_select_columns()',
5507 MSG_LEVEL_BASIC);
5508 RAISE;
5509 END define_iad_select_columns;
5510
5511
5512 ----------------------------------------------------------------------------
5513 PROCEDURE get_iad_column_values( p_select_c IN INTEGER,
5514 p_select_rec IN OUT NOCOPY select_iad_rec_type ) IS
5515 /* Bug 460927 - Changed IN to IN OUT in the above line - Oracle 8 */
5516 BEGIN
5517 print_fcn_label2( 'arp_maintain_ps2.get_iad_column_values()+' );
5518
5519 dbms_sql.column_value( p_select_c, 1, p_select_rec.set_of_books_id );
5520 dbms_sql.column_value( p_select_c, 2, p_select_rec.trx_date );
5521 dbms_sql.column_value( p_select_c, 3, p_select_rec.gl_date );
5522 dbms_sql.column_value( p_select_c, 4, p_select_rec.code_combination_id );
5523 dbms_sql.column_value( p_select_c, 5, p_select_rec.adjusted_trx_id );
5524 dbms_sql.column_value( p_select_c, 6, p_select_rec.payment_schedule_id );
5525 dbms_sql.column_value( p_select_c, 7, p_select_rec.customer_trx_id );
5526 dbms_sql.column_value( p_select_c, 8, p_select_rec.post_to_gl_flag );
5527 dbms_sql.column_value( p_select_c, 9, p_select_rec.actual_date_closed );
5528 dbms_sql.column_value( p_select_c, 10, p_select_rec.gl_date_closed );
5529 dbms_sql.column_value( p_select_c, 11, p_select_rec.min_acc_unit );
5530 dbms_sql.column_value( p_select_c, 12, p_select_rec.precision );
5531 dbms_sql.column_value( p_select_c, 13, p_select_rec.total_cm_line_amount );
5532 dbms_sql.column_value( p_select_c, 14, p_select_rec.total_inv_adj_amount );
5533 dbms_sql.column_value( p_select_c, 15, p_select_rec.commitment_code );
5534 dbms_sql.column_value( p_select_c, 16, p_select_rec.invoice_trx_id );
5535 dbms_sql.column_value( p_select_c, 17, p_select_rec.ps_line_remaining );
5536 dbms_sql.column_value( p_select_c, 18,
5537 p_select_rec.ps_amount_due_remaining );
5538 dbms_sql.column_value( p_select_c, 19,
5539 p_select_rec.ps_acctd_amt_due_rem );
5540 dbms_sql.column_value( p_select_c, 20,
5541 p_select_rec.ps_amount_adjusted );
5542 dbms_sql.column_value( p_select_c, 21, p_select_rec.ps_precision );
5543 dbms_sql.column_value( p_select_c, 22, p_select_rec.ps_min_acc_unit );
5544 dbms_sql.column_value( p_select_c, 23, p_select_rec.ps_exchange_rate );
5545 dbms_sql.column_value( p_select_c, 24,
5546 p_select_rec.total_inv_line_remaining );
5547 dbms_sql.column_value( p_select_c, 25, p_select_rec.ps_line_original );
5548 dbms_sql.column_value( p_select_c, 26, p_select_rec.ps_currency_code );
5549 dbms_sql.column_value( p_select_c, 27, p_select_rec.allocate_tax_freight);
5550 dbms_sql.column_value( p_select_c, 28, p_select_rec.adjustment_type);
5551 dbms_sql.column_value( p_select_c, 29, p_select_rec.total_cm_tax_amount);
5552 dbms_sql.column_value( p_select_c, 30, p_select_rec.total_cm_frt_amount);
5553 dbms_sql.column_value( p_select_c, 31, p_select_rec.inv_line_adj);
5554 dbms_sql.column_value( p_select_c, 32, p_select_rec.inv_tax_adj);
5555 dbms_sql.column_value( p_select_c, 33, p_select_rec.inv_frt_adj);
5556 dbms_sql.column_value( p_select_c, 34, p_select_rec.ps_tax_remaining);
5557 dbms_sql.column_value( p_select_c, 35, p_select_rec.ps_freight_remaining);
5558 dbms_sql.column_value( p_select_c, 36, p_select_rec.ps_tax_original);
5559 dbms_sql.column_value( p_select_c, 37, p_select_rec.ps_freight_original);
5560
5561 print_fcn_label2( 'arp_maintain_ps2.get_iad_column_values()-' );
5562 EXCEPTION
5563 WHEN OTHERS THEN
5564 debug('EXCEPTION: arp_maintain_ps2.get_iad_column_values()',
5565 MSG_LEVEL_BASIC);
5566 RAISE;
5567 END get_iad_column_values;
5568
5569
5570 ----------------------------------------------------------------------------
5571 --
5572 -- PROCEDURE NAME: dump_iad_select_rec
5573 --
5574 -- DECSRIPTION:
5575 --
5576 -- ARGUMENTS:
5577 -- IN:
5578 -- select_rec
5579 --
5580 -- IN/OUT:
5581 --
5582 -- OUT:
5583 --
5584 -- RETURNS:
5585 --
5586 -- NOTES:
5587 --
5588 -- HISTORY:
5589 --
5590 ----------------------------------------------------------------------------
5591 PROCEDURE dump_iad_select_rec( p_select_rec IN select_iad_rec_type ) IS
5592 BEGIN
5593
5594 print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()+' );
5595
5596 debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
5597 debug( ' set_of_books_id='
5598 || p_select_rec.set_of_books_id, MSG_LEVEL_DEBUG );
5599 debug( ' customer_trx_id ='
5600 || p_select_rec.customer_trx_id , MSG_LEVEL_DEBUG );
5601 debug( ' post_to_gl_flag='
5602 || p_select_rec.post_to_gl_flag, MSG_LEVEL_DEBUG );
5603 debug( ' trx_date='
5604 || p_select_rec.trx_date, MSG_LEVEL_DEBUG );
5605 debug( ' gl_date='
5606 || p_select_rec.gl_date, MSG_LEVEL_DEBUG );
5607 debug( ' precision='
5608 || p_select_rec.precision, MSG_LEVEL_DEBUG );
5609 debug( ' min_acc_unit='
5610 || p_select_rec.min_acc_unit, MSG_LEVEL_DEBUG );
5611 debug( ' adjusted_trx_id='
5612 || p_select_rec.adjusted_trx_id, MSG_LEVEL_DEBUG );
5613 debug( ' invoice_trx_id='
5614 || p_select_rec.invoice_trx_id, MSG_LEVEL_DEBUG );
5615 debug( ' ps_currency_code='
5616 || p_select_rec.ps_currency_code, MSG_LEVEL_DEBUG );
5617 debug( ' ps_exchange_rate='
5618 || p_select_rec.ps_exchange_rate, MSG_LEVEL_DEBUG );
5619 debug( ' ps_precision='
5620 || p_select_rec.ps_precision, MSG_LEVEL_DEBUG );
5621 debug( ' ps_min_acc_unit='
5622 || p_select_rec.ps_min_acc_unit, MSG_LEVEL_DEBUG );
5623 debug( ' commitment_code='
5624 || p_select_rec.commitment_code, MSG_LEVEL_DEBUG );
5625 debug( ' code_combination_id='
5626 || p_select_rec.code_combination_id, MSG_LEVEL_DEBUG );
5627 debug( ' gl_date_closed='
5628 || p_select_rec.gl_date_closed, MSG_LEVEL_DEBUG );
5629 debug( ' actual_date_closed='
5630 || p_select_rec.actual_date_closed, MSG_LEVEL_DEBUG );
5631 debug( ' total_cm_line_amount='
5632 || p_select_rec.total_cm_line_amount, MSG_LEVEL_DEBUG );
5633 debug( ' total_inv_adj_amount='
5634 || p_select_rec.total_inv_adj_amount, MSG_LEVEL_DEBUG );
5635 debug( ' total_inv_line_remaining='
5636 || p_select_rec.total_inv_line_remaining, MSG_LEVEL_DEBUG );
5637 debug( ' payment_schedule_id='
5638 || p_select_rec.payment_schedule_id, MSG_LEVEL_DEBUG );
5639 debug( ' ps_amount_due_remaining='
5640 || p_select_rec.ps_amount_due_remaining, MSG_LEVEL_DEBUG );
5641 debug( ' ps_acctd_amt_due_rem='
5642 || p_select_rec.ps_acctd_amt_due_rem, MSG_LEVEL_DEBUG );
5643 debug( ' ps_line_original='
5644 || p_select_rec.ps_line_original, MSG_LEVEL_DEBUG );
5645 debug( ' ps_line_remaining ='
5646 || p_select_rec.ps_line_remaining, MSG_LEVEL_DEBUG );
5647 debug( ' ps_amount_adjusted='
5648 || p_select_rec.ps_amount_adjusted, MSG_LEVEL_DEBUG );
5649 /* 1483656 */
5650 debug( ' allocate_tax_freight='
5651 || p_select_rec.allocate_tax_freight, MSG_LEVEL_DEBUG);
5652
5653
5654 print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()-' );
5655
5656 EXCEPTION
5657 WHEN OTHERS THEN
5658 debug( 'EXCEPTION: arp_maintain_ps2.dump_iad_select_rec()',
5659 MSG_LEVEL_BASIC );
5660 RAISE;
5661 END dump_iad_select_rec;
5662
5663
5664 ------------------------------------------------------------------------
5665
5666 PROCEDURE process_iad_data(
5667 p_system_info IN arp_trx_global.system_info_rec_type,
5668 p_profile_info IN arp_trx_global.profile_rec_type,
5669 p_insert_adj_c IN INTEGER,
5670 p_update_ps_c IN INTEGER,
5671 p_select_rec IN select_iad_rec_type,
5672 p_number_records IN NUMBER,
5673 p_ps_id_t IN id_table_type,
5674 p_ps_amount_due_rem_t IN OUT NOCOPY number_table_type,
5675 p_ps_acctd_amt_due_rem_t IN OUT NOCOPY number_table_type,
5676 p_ps_line_orig_t IN number_table_type,
5677 p_ps_line_rem_t IN OUT NOCOPY number_table_type,
5678 p_ps_amount_adjusted_t IN OUT NOCOPY number_table_type,
5679 p_adj_amount_t IN OUT NOCOPY number_table_type,
5680 p_acctd_adj_amount_t IN OUT NOCOPY number_table_type,
5681 p_eff_adj_line_total IN OUT NOCOPY NUMBER,
5682 p_eff_adj_tax_total IN OUT NOCOPY NUMBER,
5683 p_eff_adj_frt_total IN OUT NOCOPY NUMBER,
5684 p_eff_line_bal IN OUT NOCOPY NUMBER,
5685 p_eff_tax_bal IN OUT NOCOPY NUMBER,
5686 p_eff_frt_bal IN OUT NOCOPY NUMBER,
5687 p_line_adj_t IN OUT NOCOPY number_table_type,
5688 p_tax_adj_t IN OUT NOCOPY number_table_type,
5689 p_frt_adj_t IN OUT NOCOPY number_table_type,
5690 p_ps_tax_orig_t IN OUT NOCOPY number_table_type,
5691 p_ps_tax_rem_t IN OUT NOCOPY number_table_type,
5692 p_ps_frt_orig_t IN OUT NOCOPY number_table_type,
5693 p_ps_frt_rem_t IN OUT NOCOPY number_table_type,
5694 p_inv_line_adj_t IN OUT NOCOPY number_table_type,
5695 p_inv_tax_adj_t IN OUT NOCOPY number_table_type,
5696 p_inv_frt_adj_t IN OUT NOCOPY number_table_type,
5697 p_is_new_adj_trx IN boolean
5698 ) IS
5699
5700 l_ignore INTEGER;
5701
5702 l_new_ps_adr NUMBER;
5703 l_new_ps_acctd_adr NUMBER;
5704 l_new_acctd_adj_amount NUMBER;
5705
5706 l_total_ps_orig NUMBER :=0;
5707 l_total_ps_rem NUMBER :=0;
5708
5709 l_total_ps_line_rem NUMBER :=0;
5710 l_total_ps_tax_rem NUMBER :=0;
5711 l_total_ps_frt_rem NUMBER :=0;
5712
5713 l_total_line_adj NUMBER :=0;
5714 l_total_frt_adj NUMBER :=0;
5715 l_total_tax_adj NUMBER :=0;
5716
5717 l_amount NUMBER;
5718 l_reversal_adj NUMBER;
5719 l_percent_t number_table_type;
5720
5721 /* VAT changes */
5722 l_ae_doc_rec ae_doc_rec_type;
5723 l_adjustment_id ar_adjustments.adjustment_id%type;
5724
5725 /* Bug 3570404 - Guarantees */
5726 l_max_curr_adj NUMBER;
5727 l_bal_sign NUMBER;
5728 l_cm_adjustment_total NUMBER;
5729
5730
5731 PROCEDURE reverse_adjustments( p_rows NUMBER,
5732 p_new_adj IN OUT NOCOPY number_table_type,
5733 p_orig_adj IN OUT NOCOPY number_table_type ) IS
5734 BEGIN
5735 -- This procedure populates an adjustments
5736 -- table with the amount of the original
5737 -- adjustment. The insert statement uses
5738 -- -1 * this amount
5739
5740 FOR i IN 0..p_rows -1 LOOP
5741
5742 IF p_orig_adj(i) is not null THEN
5743 p_new_adj(i) := p_orig_adj(i);
5744 ELSE
5745 p_new_adj(i) := NULL;
5746 END IF;
5747
5748 DEBUG(' Term: ' || i || ' Amount = ' || p_new_adj(i),
5749 MSG_LEVEL_DEBUG);
5750
5751 END LOOP;
5752
5753 END reverse_adjustments;
5754
5755 BEGIN
5756 print_fcn_label2('arp_maintain_ps2.process_iad_data()+' );
5757
5758 /* MODULE FLOW (post 1483656 - TAX/FREIGHT modification)
5759
5760 1. LOOP to accumulate total PS remaining, total PS original,
5761 and total INV adj amounts
5762 2. If new transaction (INV or GUAR), set effective adj amounts
5763 3. If total PS remaining <> 0, compute percentages
5764 a. LOOP and compute percentages based on amounts remaining
5765 4. ELSE (PS remaining = 0)
5766 a. LOOP and compute percentages based on amounts original
5767 5. IF COMMITMENT is GUARANTEE
5768 a. retrieve accumulated credits
5769 b. calculate amount to be adjusted
5770 (amount credit vs. amount adj vs. commitment balance)
5771 b. DISTRIBUTE amount across terms
5772 6. ELSE (COMMITMENT IS DEPOSIT)
5773 a. LINE
5774 i. CALCULATE adj total for lines
5775 ii. IF it >= current amount adjusted, REVERSE existing adj
5776 iii.ELSE DISTRIBUTE amount across terms
5777 b. IF ra_cust_trx_types.allocate_tax_freight = 'Y'
5778 i. TAX
5779 A. CALCULATE adj total for tax
5780 B. If adj >= current amount adjusted, REVERSE existing adj
5781 C. ELSE DISTRIBUTE amount across terms
5782 ii. FREIGHT
5783 A. CALCULATE adj total for freight
5784 B. If adj >= current amount adjusted, REVERSE existing adj
5785 C. ELSE DISTRIBUTE amount across terms
5786 7. LOOP
5787 a. Figure total adjusted as sum of line, tax, and freight adj
5788 b. Calculate acctd_amount fields
5789 c. Calculate amount_due_remaining fields
5790 d. INSERT adjustments
5791 e. UPDATE payment schedules
5792 f. INSERT distributions
5793 */
5794
5795 --------------------------------------------------------------------
5796 -- Get total line orig/rem for adjusted ps
5797 --------------------------------------------------------------------
5798 FOR i IN 0..p_number_records - 1 LOOP
5799 l_total_ps_line_rem := l_total_ps_line_rem + p_ps_line_rem_t( i );
5800 l_total_ps_tax_rem := l_total_ps_tax_rem + p_ps_tax_rem_t( i );
5801 l_total_ps_frt_rem := l_total_ps_frt_rem + p_ps_frt_rem_t( i );
5802
5803 l_total_ps_orig := l_total_ps_orig + p_ps_line_orig_t( i );
5804 l_total_ps_rem := l_total_ps_rem + p_ps_line_rem_t( i );
5805
5806 -- Accumulate total adjustments
5807 -- (used to determine if we are closing a given term
5808 -- for deposit invoices)
5809 l_total_line_adj := l_total_line_adj + p_inv_line_adj_t(i);
5810
5811 /* 1483656 - include tax and freight where appropriate */
5812 IF (p_select_rec.allocate_tax_freight = 'Y') THEN
5813
5814 l_total_ps_orig := l_total_ps_orig + p_ps_tax_orig_t(i) +
5815 p_ps_frt_orig_t(i);
5816 l_total_ps_rem := l_total_ps_rem + p_ps_tax_rem_t(i) +
5817 p_ps_frt_rem_t(i);
5818
5819 l_total_tax_adj := l_total_tax_adj + p_inv_tax_adj_t(i);
5820 l_total_frt_adj := l_total_frt_adj + p_inv_frt_adj_t(i);
5821 END IF;
5822
5823 END LOOP;
5824
5825 -- Set effective adjustment totals (only if this is a new transaction)
5826 IF (p_is_new_adj_trx) THEN
5827
5828 DEBUG(' NEW INVOICE - setting eff adj totals', MSG_LEVEL_DEBUG);
5829
5830 p_eff_adj_line_total := l_total_line_adj;
5831 p_eff_adj_tax_total := l_total_tax_adj;
5832 p_eff_adj_frt_total := l_total_frt_adj;
5833
5834 -- Bug 1859293
5835 -- override effective balances (also) for new transaction
5836 -- NOTE: The sql does not always return these correct, but
5837 -- my accumulation above has proven more reliable.
5838 p_eff_line_bal := l_total_ps_line_rem;
5839 p_eff_tax_bal := l_total_ps_tax_rem;
5840 p_eff_frt_bal := l_total_ps_frt_rem;
5841
5842 END IF;
5843
5844 --------------------------------------------------------------------
5845 -- Compute percentages
5846 --------------------------------------------------------------------
5847 IF( l_total_ps_rem <> 0 ) THEN
5848
5849 FOR i IN 0..p_number_records - 1 LOOP
5850
5851 IF (p_select_rec.allocate_tax_freight = 'Y') THEN
5852 l_percent_t( i ) := (p_ps_line_rem_t( i ) + p_ps_tax_rem_t( i ) +
5853 p_ps_frt_rem_t( i )) / l_total_ps_rem;
5854 ELSE
5855 l_percent_t( i ) := p_ps_line_rem_t( i ) / l_total_ps_rem;
5856 END IF;
5857
5858 END LOOP;
5859
5860 ELSE
5861
5862 FOR i IN 0..p_number_records - 1 LOOP
5863
5864 IF (p_select_rec.allocate_tax_freight = 'Y') THEN
5865 l_percent_t( i ) := (p_ps_line_orig_t( i ) + p_ps_tax_orig_t( i ) +
5866 p_ps_frt_orig_t( i )) / l_total_ps_orig;
5867 ELSE
5868 l_percent_t( i ) := p_ps_line_orig_t( i ) / l_total_ps_orig;
5869 END IF;
5870
5871 END LOOP;
5872
5873 END IF;
5874
5875 --------------------------------------------------------------------
5876 -- Calculate amount to adjust
5877 --------------------------------------------------------------------
5878 debug( ' commitment_code='||p_select_rec.commitment_code,
5879 MSG_LEVEL_DEBUG );
5880 debug( ' allocate_tax_freight=' || p_select_rec.allocate_tax_freight,
5881 MSG_LEVEL_DEBUG );
5882
5883
5884 IF( p_select_rec.commitment_code = 0 ) THEN
5885
5886 ----------------------------------------------------------------
5887 -- GUAR case
5888 ----------------------------------------------------------------
5889 debug( ' GUAR case', MSG_LEVEL_DEBUG );
5890 debug( ' p_eff_adj_line_total='||p_eff_adj_line_total,
5891 MSG_LEVEL_DEBUG );
5892 debug( ' p_eff_line_bal='||p_eff_line_bal,
5893 MSG_LEVEL_DEBUG );
5894 debug( ' total_inv_line_remaining='||
5895 p_select_rec.total_inv_line_remaining,
5896 MSG_LEVEL_DEBUG );
5897 debug( ' adj_trx_id='||p_select_rec.adjusted_trx_id,
5898 MSG_LEVEL_DEBUG );
5899 debug( ' invoice_trx_id=' || p_select_rec.invoice_trx_id,
5900 MSG_LEVEL_DEBUG );
5901
5902 /* Bug 3570404 - cumulative cms not calculating correctly
5903 so figure the effect of prior CMs here and now */
5904
5905 select nvl(sum(adj.line_adjusted),0)
5906 into l_cm_adjustment_total
5907 from ar_adjustments adj,
5908 ra_customer_trx ocm
5909 where adj.customer_trx_id = p_select_rec.adjusted_trx_id
5910 and adj.subsequent_trx_id = ocm.customer_trx_id
5911 and ocm.previous_customer_trx_id = p_select_rec.invoice_trx_id;
5912
5913 /* set locals for calculations */
5914 l_max_curr_adj := p_eff_adj_line_total + l_cm_adjustment_total;
5915 l_bal_sign := sign(p_eff_adj_line_total);
5916
5917 debug(' l_cm_adjustment_total = ' || l_cm_adjustment_total,
5918 MSG_LEVEL_DEBUG);
5919 debug(' l_max_curr_adj = ' || l_max_curr_adj,
5920 MSG_LEVEL_DEBUG);
5921
5922 /* l_bal_sign is the sign of the adjustment amount currently
5923 out there against this guarantee for the specified invoice.
5924 if it is pos(+), then invoice is pos and cm is (usually) neg(-)
5925 if it is neg(-), then the invoice is neg and cm is pos. */
5926 IF l_bal_sign = -1
5927 THEN
5928 /* This is the NORMAL case, GUAR+, INV+, CM- */
5929 l_reversal_adj := GREATEST(p_select_rec.total_cm_line_amount,
5930 l_max_curr_adj);
5931 ELSIF l_bal_sign = 1
5932 THEN
5933 /* This is OPPOSITE case, GUAR+, INV-, CM+ */
5934 l_reversal_adj := LEAST(p_select_rec.total_cm_line_amount,
5935 l_max_curr_adj);
5936 ELSE
5937 l_reversal_adj := 0;
5938 END IF;
5939
5940 debug(' l_reversal_adj = ' || l_reversal_adj,
5941 MSG_LEVEL_DEBUG);
5942
5943 ----------------------------------------------------------------
5944 -- Update invoice line balance
5945 ----------------------------------------------------------------
5946 p_eff_line_bal := p_eff_line_bal +
5947 p_select_rec.total_cm_line_amount;
5948
5949 -- distribute amount across lines (amount_adjusted will be set
5950 -- later.
5951 distribute_amount(
5952 p_number_records,
5953 p_select_rec.ps_currency_code,
5954 l_reversal_adj,
5955 l_percent_t,
5956 p_line_adj_t );
5957
5958 ELSIF( p_select_rec.commitment_code = 1 ) THEN
5959
5960 ----------------------------------------------------------------
5961 -- DEP case
5962 -- 1483656 - modified to allocate tax and freight
5963 ----------------------------------------------------------------
5964 debug( ' DEP case', MSG_LEVEL_DEBUG );
5965
5966 ----------------------------------------------------------------
5967 -- If the amount being credited (for LINE, TAX, or FREIGHT)
5968 -- will close down the invoice,
5969 -- Then, make the CM adjustments equal the invoice ones
5970
5971 -- LINE
5972 IF (l_total_ps_line_rem +
5973 p_select_rec.total_cm_line_amount +
5974 (l_total_line_adj * -1) <= 0)
5975 THEN
5976 debug( ' l_total_ps_line_rem = '||
5977 l_total_ps_line_rem, MSG_LEVEL_DEBUG );
5978 debug( ' p_select_rec.total_cm_line = '||
5979 p_select_rec.total_cm_line_amount, MSG_LEVEL_DEBUG );
5980 debug( ' l_total_inv_adj = '||l_total_line_adj,
5981 MSG_LEVEL_DEBUG );
5982
5983
5984 debug( ' REVERSING LINE', MSG_LEVEL_DEBUG );
5985
5986 -- reverse remaining LINE adjustments
5987 reverse_adjustments(p_number_records,
5988 p_line_adj_t,
5989 p_inv_line_adj_t);
5990
5991 p_eff_line_bal := 0;
5992
5993 ELSE
5994 DEBUG(' p_eff_line_bal = ' || p_eff_line_bal,
5995 MSG_LEVEL_DEBUG);
5996
5997 DEBUG(' p_select_rec.total_cm_line_amount = ' ||
5998 p_select_rec.total_cm_line_amount,
5999 MSG_LEVEL_DEBUG);
6000
6001 DEBUG(' p_eff_adj_line_total = '||
6002 p_eff_adj_line_total,
6003 MSG_LEVEL_DEBUG);
6004
6005 -- figure how much, if any, of the CM amount
6006 -- gets to be adjusted using logic similar
6007 -- to the existing logic
6008
6009 IF( p_eff_line_bal + p_select_rec.total_cm_line_amount < 0 ) THEN
6010
6011 l_reversal_adj :=
6012 GREATEST( p_eff_adj_line_total,
6013 p_eff_line_bal +
6014 p_select_rec.total_cm_line_amount );
6015 ELSE
6016 l_reversal_adj := 0;
6017 END IF;
6018
6019 debug( ' DISTRIBUTING LINE', MSG_LEVEL_DEBUG);
6020
6021 distribute_amount(
6022 p_number_records,
6023 p_select_rec.ps_currency_code,
6024 l_reversal_adj,
6025 l_percent_t,
6026 p_line_adj_t );
6027
6028 -- Update invoice line balance
6029 p_eff_line_bal := p_eff_line_bal - l_reversal_adj +
6030 p_select_rec.total_cm_line_amount;
6031
6032 END IF;
6033
6034 -- TAX and FREIGHT
6035 IF (p_select_rec.allocate_tax_freight = 'Y') THEN
6036
6037 debug( ' ALLOCATING TAX AND FREIGHT', MSG_LEVEL_DEBUG);
6038
6039 -- TAX
6040 debug( ' l_total_ps_tax_rem = '||l_total_ps_tax_rem,
6041 MSG_LEVEL_DEBUG );
6042 debug( ' p_select_rec.total_cm_tax = '||p_select_rec.total_cm_tax_amount,
6043 MSG_LEVEL_DEBUG );
6044 debug( ' l_total_tax_adj = '||l_total_tax_adj,
6045 MSG_LEVEL_DEBUG );
6046
6047 IF (l_total_ps_tax_rem +
6048 p_select_rec.total_cm_tax_amount +
6049 (l_total_tax_adj * -1) <= 0)
6050 THEN
6051 debug( ' REVERSING TAX', MSG_LEVEL_DEBUG );
6052
6053 -- reverse remaining TAX adjustments
6054 reverse_adjustments(p_number_records,
6055 p_tax_adj_t,
6056 p_inv_tax_adj_t);
6057 p_eff_tax_bal := 0;
6058
6059 ELSE
6060 debug( ' DISTRIBUTING TAX', MSG_LEVEL_DEBUG );
6061
6062 DEBUG(' p_eff_tax_bal = ' || p_eff_tax_bal,
6063 MSG_LEVEL_DEBUG);
6064
6065 DEBUG(' p_select_rec.total_cm_tax_amount = ' ||
6066 p_select_rec.total_cm_tax_amount,
6067 MSG_LEVEL_DEBUG);
6068
6069 DEBUG(' p_eff_adj_tax_total = '||
6070 p_eff_adj_tax_total,
6071 MSG_LEVEL_DEBUG);
6072
6073 IF( p_eff_tax_bal + p_select_rec.total_cm_tax_amount < 0 ) THEN
6074
6075 l_reversal_adj :=
6076 GREATEST( p_eff_adj_tax_total,
6077 p_eff_tax_bal +
6078 p_select_rec.total_cm_tax_amount );
6079 ELSE
6080 l_reversal_adj := 0;
6081 END IF;
6082
6083 -- calculate and allocate TAX adjustments
6084 distribute_amount(
6085 p_number_records,
6086 p_select_rec.ps_currency_code,
6087 l_reversal_adj,
6088 l_percent_t,
6089 p_tax_adj_t );
6090
6091 -- Update invoice tax balance
6092 p_eff_tax_bal := p_eff_tax_bal - l_reversal_adj +
6093 p_select_rec.total_cm_tax_amount;
6094
6095 END IF;
6096
6097 -- FREIGHT
6098 debug( ' l_total_ps_frt_rem = '||l_total_ps_frt_rem,
6099 MSG_LEVEL_DEBUG );
6100 debug( ' p_select_rec.total_cm_frt = '||p_select_rec.total_cm_frt_amount,
6101 MSG_LEVEL_DEBUG );
6102 debug( ' l_total_frt_adj = '||l_total_frt_adj,
6103 MSG_LEVEL_DEBUG );
6104
6105 IF (l_total_ps_frt_rem +
6106 p_select_rec.total_cm_frt_amount +
6107 (l_total_frt_adj * -1) <= 0 )
6108 THEN
6109 debug( ' REVERSING FREIGHT', MSG_LEVEL_DEBUG );
6110
6111 -- reverse remaining FRT adjustments
6112 reverse_adjustments(p_number_records,
6113 p_frt_adj_t,
6114 p_inv_frt_adj_t);
6115
6116 p_eff_frt_bal := 0;
6117
6118 ELSE
6119 -- calculate and allocate FRT adjustments
6120
6121 debug( ' DISTRIBUTING FREIGHT', MSG_LEVEL_DEBUG );
6122
6123 IF( p_eff_frt_bal + p_select_rec.total_cm_frt_amount < 0 ) THEN
6124
6125 l_reversal_adj :=
6126 GREATEST( p_eff_adj_frt_total,
6127 p_eff_frt_bal +
6128 p_select_rec.total_cm_frt_amount );
6129 ELSE
6130 l_reversal_adj := 0;
6131 END IF;
6132
6133 distribute_amount(
6134 p_number_records,
6135 p_select_rec.ps_currency_code,
6136 l_reversal_adj,
6137 l_percent_t,
6138 p_frt_adj_t );
6139
6140 -- Update invoice frt balance
6141 p_eff_frt_bal := p_eff_frt_bal - l_reversal_adj +
6142 p_select_rec.total_cm_frt_amount;
6143
6144 END IF;
6145
6146 END IF; -- end ALLOCATE_TAX_FRIEGHT
6147
6148 ELSE
6149
6150 debug( ' bad commitment_code', MSG_LEVEL_DEBUG );
6151 l_reversal_adj := 0;
6152
6153 END IF; -- END GUARANTEE/DEPOSIT CASE
6154
6155
6156 -- Now figure the total adjusted and amounts remaining
6157
6158 FOR i IN 0..p_number_records - 1 LOOP
6159
6160 --------------------------------------------------------------------
6161 -- Figure amount adjusted
6162 -- Update line_items_rem
6163 --------------------------------------------------------------------
6164 p_adj_amount_t( i ) := p_line_adj_t( i );
6165 p_ps_line_rem_t( i ) := p_ps_line_rem_t( i ) - p_line_adj_t( i );
6166
6167 IF (p_select_rec.allocate_tax_freight = 'Y') THEN
6168 p_ps_tax_rem_t( i ) := p_ps_tax_rem_t( i ) - p_tax_adj_t( i );
6169 p_ps_frt_rem_t( i ) := p_ps_frt_rem_t( i ) - p_frt_adj_t( i );
6170
6171 -- add tax and freight to adjusted amount
6172 p_adj_amount_t( i ) := p_adj_amount_t( i ) + p_tax_adj_t( i )
6173 + p_frt_adj_t( i );
6174 ELSE
6175 -- initialize the fields to null for use in the insert
6176 p_tax_adj_t( i ) := null;
6177 p_frt_adj_t( i ) := null;
6178
6179 END IF;
6180
6181 --------------------------------------------------------------------
6182 -- Update PS amount_adj
6183 --------------------------------------------------------------------
6184 p_ps_amount_adjusted_t( i ) :=
6185 p_ps_amount_adjusted_t( i ) - p_adj_amount_t( i );
6186
6187 --------------------------------------------------------------------
6188 -- Compute new acctd_adr (aracc)
6189 --------------------------------------------------------------------
6190 arp_util.calc_acctd_amount(
6191 p_system_info.base_currency,
6192 NULL, -- precision
6193 NULL, -- mau
6194 p_select_rec.ps_exchange_rate,
6195 '-', -- type
6196 p_ps_amount_due_rem_t( i ), -- master_from
6197 p_ps_acctd_amt_due_rem_t( i ), -- acctd_master_from
6198 p_adj_amount_t( i ), -- detail
6199 l_new_ps_adr, -- master_to
6200 l_new_ps_acctd_adr, -- acctd_master_to
6201 l_new_acctd_adj_amount -- acctd_detail
6202 );
6203
6204 --------------------------------------------------------------------
6205 -- Update amt_due_rem, acctd_amt_due_rem
6206 --------------------------------------------------------------------
6207 p_ps_amount_due_rem_t( i ) := l_new_ps_adr;
6208 p_ps_acctd_amt_due_rem_t( i ) := l_new_ps_acctd_adr;
6209 p_acctd_adj_amount_t( i ) := l_new_acctd_adj_amount;
6210
6211 END LOOP;
6212
6213
6214 -------------------------------------------------------------
6215 -- Insert into ar_adjustments
6216 -------------------------------------------------------------
6217
6218 FOR i IN 0..p_number_records - 1 LOOP
6219
6220 -------------------------------------------------------------
6221 -- Skip rows with $0 amounts (do not insert $0 adjustments)
6222 -------------------------------------------------------------
6223 IF( p_adj_amount_t( i ) = 0 ) THEN
6224 GOTO skip;
6225 END IF;
6226
6227 -------------------------------------------------------------
6228 -- Bind vars
6229 -------------------------------------------------------------
6230 BEGIN
6231 debug( ' Binding insert_adj_c', MSG_LEVEL_DEBUG );
6232
6233 dbms_sql.bind_variable( p_insert_adj_c,
6234 'user_id',
6235 p_profile_info.user_id );
6236
6237 dbms_sql.bind_variable( p_insert_adj_c,
6238 'login_id',
6239 p_profile_info.conc_login_id );
6240
6241 dbms_sql.bind_variable( p_insert_adj_c,
6242 'request_id',
6243 p_profile_info.request_id );
6244
6245 dbms_sql.bind_variable( p_insert_adj_c,
6246 'application_id',
6247 p_profile_info.application_id );
6248
6249 dbms_sql.bind_variable( p_insert_adj_c,
6250 'program_id',
6251 p_profile_info.conc_program_id );
6252
6253
6254 dbms_sql.bind_variable(
6255 p_insert_adj_c,
6256 'set_of_books_id',
6257 p_select_rec.set_of_books_id );
6258
6259 dbms_sql.bind_variable( p_insert_adj_c,
6260 'trx_date',
6261 p_select_rec.trx_date );
6262
6263 dbms_sql.bind_variable( p_insert_adj_c,
6264 'gl_date',
6265 p_select_rec.gl_date );
6266
6267 dbms_sql.bind_variable( p_insert_adj_c,
6268 'code_combination_id',
6269 p_select_rec.code_combination_id );
6270
6271 dbms_sql.bind_variable( p_insert_adj_c,
6272 'adjusted_trx_id',
6273 p_select_rec.adjusted_trx_id );
6274
6275 dbms_sql.bind_variable( p_insert_adj_c,
6276 'payment_schedule_id',
6277 p_ps_id_t( i ) );
6278
6279 dbms_sql.bind_variable( p_insert_adj_c,
6280 'subsequent_trx_id',
6281 p_select_rec.customer_trx_id );
6282
6283 dbms_sql.bind_variable( p_insert_adj_c,
6284 'post_to_gl_flag',
6285 p_select_rec.post_to_gl_flag );
6286
6287 dbms_sql.bind_variable( p_insert_adj_c,
6288 'adj_amount',
6289 p_adj_amount_t( i ) );
6290
6291 dbms_sql.bind_variable( p_insert_adj_c,
6292 'acctd_adj_amount',
6293 p_acctd_adj_amount_t( i ) );
6294
6295 /* VAT changes */
6296 SELECT ar_adjustments_s.nextval
6297 INTO l_adjustment_id
6298 FROM dual;
6299
6300 dbms_sql.bind_variable( p_insert_adj_c,
6301 'adjustment_id',
6302 l_adjustment_id );
6303
6304 dbms_sql.bind_variable( p_insert_adj_c,
6305 'adj_type',
6306 p_select_rec.adjustment_type);
6307
6308 dbms_sql.bind_variable( p_insert_adj_c,
6309 'line_adjusted',
6310 p_line_adj_t(i));
6311
6312 dbms_sql.bind_variable( p_insert_adj_c,
6313 'tax_adjusted',
6314 p_tax_adj_t(i));
6315
6316 dbms_sql.bind_variable( p_insert_adj_c,
6317 'freight_adjusted',
6318 p_frt_adj_t(i));
6319 /* anuj: Corrected typo for SSA retrofit */
6320 dbms_sql.bind_variable( p_insert_adj_c,
6321 'org_id',
6322 arp_standard.sysparm.org_id);
6323
6324 EXCEPTION
6325 WHEN OTHERS THEN
6326 debug( 'EXCEPTION: Error in binding insert_adj_c',
6327 MSG_LEVEL_BASIC );
6328 RAISE;
6329 END;
6330
6331 -------------------------------------------------------------
6332 -- Execute
6333 -------------------------------------------------------------
6334 BEGIN
6335 debug( ' Inserting adjustments', MSG_LEVEL_DEBUG );
6336 l_ignore := dbms_sql.execute( p_insert_adj_c );
6337 debug( to_char(l_ignore) || ' row(s) inserted',
6338 MSG_LEVEL_DEBUG );
6339
6340 /*-------------------------------------------+
6341 | Call central MRC library for insertion |
6342 | into MRC tables |
6343 +-------------------------------------------*/
6344
6345 ar_mrc_engine.maintain_mrc_data(
6346 p_event_mode => 'INSERT',
6347 p_table_name => 'AR_ADJUSTMENTS',
6348 p_mode => 'SINGLE',
6349 p_key_value => l_adjustment_id);
6350
6351 EXCEPTION
6352 WHEN OTHERS THEN
6353 debug( 'EXCEPTION: Error executing insert ra stmt',
6354 MSG_LEVEL_BASIC );
6355 RAISE;
6356 END;
6357
6358
6359 -------------------------------------------------------------
6360 -- Update ar_payment_schedules
6361 -------------------------------------------------------------
6362
6363 -------------------------------------------------------------
6364 -- Bind vars
6365 -------------------------------------------------------------
6366 BEGIN
6367 debug( ' Binding update_ps_c', MSG_LEVEL_DEBUG );
6368
6369 dbms_sql.bind_variable( p_update_ps_c,
6370 'user_id',
6371 p_profile_info.user_id );
6372
6373 dbms_sql.bind_variable( p_update_ps_c,
6374 'login_id',
6375 p_profile_info.conc_login_id );
6376
6377 dbms_sql.bind_variable( p_update_ps_c,
6378 'amount_due_remaining',
6379 p_ps_amount_due_rem_t( i ) );
6380
6381 dbms_sql.bind_variable( p_update_ps_c,
6382 'gl_date_closed',
6383 p_select_rec.gl_date_closed );
6384
6385 dbms_sql.bind_variable( p_update_ps_c,
6386 'actual_date_closed',
6387 p_select_rec.actual_date_closed );
6388
6389 dbms_sql.bind_variable( p_update_ps_c,
6390 'amount_line_items_remaining',
6391 p_ps_line_rem_t( i ) );
6392
6393 dbms_sql.bind_variable( p_update_ps_c,
6394 'amount_adjusted',
6395 p_ps_amount_adjusted_t( i ) );
6396
6397 dbms_sql.bind_variable( p_update_ps_c,
6398 'acctd_amount_due_remaining',
6399 p_ps_acctd_amt_due_rem_t( i ) );
6400
6401 dbms_sql.bind_variable( p_update_ps_c,
6402 'payment_schedule_id',
6403 p_ps_id_t( i ) );
6404
6405 dbms_sql.bind_variable( p_update_ps_c,
6406 'tax_remaining',
6407 p_ps_tax_rem_t( i ) );
6408
6409 dbms_sql.bind_variable( p_update_ps_c,
6410 'freight_remaining',
6411 p_ps_frt_rem_t( i ) );
6412
6413 EXCEPTION
6414 WHEN OTHERS THEN
6415 debug( 'EXCEPTION: Error in binding update_ps_c',
6416 MSG_LEVEL_BASIC );
6417 RAISE;
6418 END;
6419
6420 -------------------------------------------------------------
6421 -- Execute
6422 -------------------------------------------------------------
6423 BEGIN
6424 debug( ' Updating invoice payment schedules', MSG_LEVEL_DEBUG );
6425 l_ignore := dbms_sql.execute( p_update_ps_c );
6426 debug( to_char(l_ignore) || ' row(s) updated',
6427 MSG_LEVEL_DEBUG );
6428
6429 /*-------------------------------------------+
6430 | Call central MRC library for update |
6431 | of AR_PAYMENT_SCHEDULES |
6432 +-------------------------------------------*/
6433
6434 ar_mrc_engine.maintain_mrc_data(
6435 p_event_mode => 'UPDATE',
6436 p_table_name => 'AR_PAYMENT_SCHEDULES',
6437 p_mode => 'SINGLE',
6438 p_key_value => p_ps_id_t( i ));
6439
6440 EXCEPTION
6441 WHEN OTHERS THEN
6442 debug( 'EXCEPTION: Error executing update ps stmt',
6443 MSG_LEVEL_BASIC );
6444 RAISE;
6445 END;
6446
6447 -------------------------------------------------------------
6448 -- Insert into ar_distributions
6449 -------------------------------------------------------------
6450 /* VAT changes : create acct entry */
6451 l_ae_doc_rec.document_type := 'ADJUSTMENT';
6452 l_ae_doc_rec.document_id := l_adjustment_id;
6453 l_ae_doc_rec.accounting_entity_level := 'ONE';
6454 l_ae_doc_rec.source_table := 'ADJ';
6455 l_ae_doc_rec.source_id := l_adjustment_id;
6456 l_ae_doc_rec.source_id_old := p_select_rec.code_combination_id;
6457 l_ae_doc_rec.other_flag := 'COMMITMENT';
6458
6459 --Bug 1329091 - PS is updated before Accounting Engine Call
6460
6461 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
6462
6463 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
6464
6465 <<skip>>
6466 null;
6467
6468 END LOOP;
6469
6470
6471
6472 print_fcn_label2('arp_maintain_ps2.process_iad_data()-' );
6473
6474 EXCEPTION
6475 WHEN OTHERS THEN
6476 debug( 'EXCEPTION: arp_maintain_ps2.process_iad_data()',
6477 MSG_LEVEL_BASIC );
6478 RAISE;
6479 END process_iad_data;
6480
6481
6482 ----------------------------------------------------------------------------
6483 PROCEDURE insert_cm_child_adj_private(
6484 p_system_info IN arp_trx_global.system_info_rec_type,
6485 p_profile_info IN arp_trx_global.profile_rec_type,
6486 p_customer_trx_id IN BINARY_INTEGER ) IS
6487
6488 l_ignore INTEGER;
6489
6490 l_old_trx_id BINARY_INTEGER;
6491 l_customer_trx_id BINARY_INTEGER;
6492
6493 l_old_inv_trx_id BINARY_INTEGER;
6494 l_inv_trx_id BINARY_INTEGER;
6495
6496 l_old_adjusted_trx_id BINARY_INTEGER;
6497 l_adjusted_trx_id BINARY_INTEGER;
6498
6499 l_load_ps_tables BOOLEAN := FALSE;
6500 l_is_new_adj_trx BOOLEAN;
6501
6502 --
6503 -- ps attributes
6504 --
6505 l_ps_id_t id_table_type;
6506 l_ps_amount_due_rem_t number_table_type;
6507 l_ps_acctd_amt_due_rem_t number_table_type;
6508 l_ps_line_orig_t number_table_type;
6509 l_ps_line_rem_t number_table_type;
6510 l_ps_amount_adjusted_t number_table_type;
6511 l_ps_tax_orig_t number_table_type;
6512 l_ps_tax_rem_t number_table_type;
6513 l_ps_frt_orig_t number_table_type;
6514 l_ps_frt_rem_t number_table_type;
6515
6516 -- Invoice attributes
6517 -- (these store the amount adjusted
6518 -- prior to this CM per PS row)
6519 l_inv_line_adj_t number_table_type;
6520 l_inv_tax_adj_t number_table_type;
6521 l_inv_frt_adj_t number_table_type;
6522
6523 --
6524 -- Derived attributes
6525 --
6526 l_adj_amount_t number_table_type;
6527 l_acctd_adj_amount_t number_table_type;
6528 l_line_adj_t number_table_type;
6529 l_tax_adj_t number_table_type;
6530 l_frt_adj_t number_table_type;
6531
6532 -- accumulators
6533 l_eff_adj_line_total NUMBER;
6534 l_eff_adj_tax_total NUMBER;
6535 l_eff_adj_frt_total NUMBER;
6536 l_eff_line_bal NUMBER;
6537 l_eff_tax_bal NUMBER;
6538 l_eff_frt_bal NUMBER;
6539
6540
6541 l_table_index BINARY_INTEGER := 0;
6542
6543 l_select_rec select_iad_rec_type;
6544
6545 -----------------------------------------------------------------------
6546 PROCEDURE load_tables( p_select_rec IN select_iad_rec_type ) IS
6547
6548 BEGIN
6549 print_fcn_label2('arp_maintain_ps2.load_tables()+' );
6550
6551 l_ps_id_t( l_table_index ) := p_select_rec.payment_schedule_id;
6552 l_ps_amount_due_rem_t( l_table_index ) :=
6553 p_select_rec.ps_amount_due_remaining;
6554 l_ps_acctd_amt_due_rem_t( l_table_index ) :=
6555 p_select_rec.ps_acctd_amt_due_rem;
6556 l_ps_line_orig_t( l_table_index ) :=
6557 p_select_rec.ps_line_original;
6558 l_ps_line_rem_t( l_table_index ) :=
6559 p_select_rec.ps_line_remaining;
6560 l_ps_amount_adjusted_t( l_table_index ) :=
6561 p_select_rec.ps_amount_adjusted;
6562
6563 l_ps_tax_orig_t( l_table_index) :=
6564 p_select_rec.ps_tax_original;
6565 l_ps_tax_rem_t( l_table_index ) :=
6566 p_select_rec.ps_tax_remaining;
6567 l_ps_frt_orig_t( l_table_index ) :=
6568 p_select_rec.ps_freight_original;
6569 l_ps_frt_rem_t( l_table_index ) :=
6570 p_select_rec.ps_freight_remaining;
6571
6572 l_inv_line_adj_t( l_table_index) :=
6573 p_select_rec.inv_line_adj;
6574 l_inv_tax_adj_t( l_table_index) :=
6575 p_select_rec.inv_tax_adj;
6576 l_inv_frt_adj_t( l_table_index) :=
6577 p_select_rec.inv_frt_adj;
6578
6579 l_table_index := l_table_index + 1;
6580
6581 print_fcn_label2('arp_maintain_ps2.load_tables()-' );
6582
6583 EXCEPTION
6584 WHEN OTHERS THEN
6585 debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
6586 MSG_LEVEL_BASIC );
6587 RAISE;
6588 END load_tables;
6589
6590
6591 -----------------------------------------------------------------------
6592 PROCEDURE clear_cm_tables IS
6593
6594 BEGIN
6595 print_fcn_label2('arp_maintain_ps2.clear_cm_tables()+' );
6596
6597 l_adj_amount_t := null_number_t;
6598 l_line_adj_t := null_number_t;
6599 l_tax_adj_t := null_number_t;
6600 l_frt_adj_t := null_number_t;
6601
6602 l_acctd_adj_amount_t := null_number_t;
6603
6604 print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
6605
6606 EXCEPTION
6607 WHEN OTHERS THEN
6608 debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
6609 MSG_LEVEL_BASIC );
6610 RAISE;
6611 END clear_cm_tables;
6612
6613
6614 -----------------------------------------------------------------------
6615 PROCEDURE clear_all_tables IS
6616
6617 BEGIN
6618 print_fcn_label2('arp_maintain_ps2.clear_all_tables()+' );
6619
6620
6621
6622 l_ps_id_t := null_id_t;
6623 l_ps_amount_due_rem_t := null_number_t;
6624 l_ps_acctd_amt_due_rem_t := null_number_t;
6625 l_ps_line_orig_t := null_number_t;
6626 l_ps_line_rem_t := null_number_t;
6627 l_ps_tax_orig_t := null_number_t;
6628 l_ps_tax_rem_t := null_number_t;
6629 l_ps_frt_orig_t := null_number_t;
6630 l_ps_frt_rem_t := null_number_t;
6631 l_ps_amount_adjusted_t := null_number_t;
6632
6633 l_inv_line_adj_t := null_number_t;
6634 l_inv_tax_adj_t := null_number_t;
6635 l_inv_frt_adj_t := null_number_t;
6636
6637 clear_cm_tables;
6638
6639 l_table_index := 0;
6640
6641 print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
6642
6643 EXCEPTION
6644 WHEN OTHERS THEN
6645 debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
6646 MSG_LEVEL_BASIC );
6647 RAISE;
6648 END clear_all_tables;
6649
6650 -----------------------------------------------------------------------
6651 FUNCTION is_new_id( p_old_id BINARY_INTEGER, p_new_id BINARY_INTEGER )
6652 RETURN BOOLEAN IS
6653 BEGIN
6654
6655 RETURN( p_old_id IS NULL OR p_old_id <> p_new_id );
6656
6657 END is_new_id;
6658
6659 -----------------------------------------------------------------------
6660 FUNCTION is_new_cm RETURN BOOLEAN IS
6661 BEGIN
6662
6663 RETURN( l_old_trx_id IS NULL OR l_old_trx_id <> l_customer_trx_id );
6664
6665 END is_new_cm;
6666
6667 -----------------------------------------------------------------------
6668 FUNCTION is_new_inv RETURN BOOLEAN IS
6669 BEGIN
6670
6671 RETURN( l_old_inv_trx_id IS NULL OR
6672 l_old_inv_trx_id <> l_inv_trx_id );
6673
6674 END is_new_inv;
6675
6676 -----------------------------------------------------------------------
6677 FUNCTION is_new_adjusted_trx RETURN BOOLEAN IS
6678 BEGIN
6679
6680 RETURN( l_old_adjusted_trx_id IS NULL OR
6681 l_old_adjusted_trx_id <> l_adjusted_trx_id );
6682
6683 END is_new_adjusted_trx;
6684
6685
6686 BEGIN
6687
6688 print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()+' );
6689
6690 --
6691 clear_all_tables;
6692 --
6693 IF( NOT( dbms_sql.is_open( iad_select_c ) AND
6694 dbms_sql.is_open( iad_insert_adj_c ) AND
6695 dbms_sql.is_open( iad_update_ps_c ) )) THEN
6696
6697 build_iad_sql(
6698 system_info,
6699 profile_info,
6700 iad_select_c,
6701 iad_insert_adj_c,
6702 iad_update_ps_c );
6703
6704 END IF;
6705
6706 --
6707 define_iad_select_columns( iad_select_c, l_select_rec );
6708
6709 ---------------------------------------------------------------
6710 -- Bind variables
6711 ---------------------------------------------------------------
6712 dbms_sql.bind_variable( iad_select_c,
6713 'customer_trx_id',
6714 p_customer_trx_id );
6715
6716 ---------------------------------------------------------------
6717 -- Execute sql
6718 ---------------------------------------------------------------
6719 debug( ' Executing select sql', MSG_LEVEL_DEBUG );
6720
6721 BEGIN
6722 l_ignore := dbms_sql.execute( iad_select_c );
6723
6724 EXCEPTION
6725 WHEN OTHERS THEN
6726 debug( 'EXCEPTION: Error executing select sql',
6727 MSG_LEVEL_BASIC );
6728 RAISE;
6729 END;
6730
6731
6732 ---------------------------------------------------------------
6733 -- Fetch rows
6734 ---------------------------------------------------------------
6735 BEGIN
6736 LOOP
6737
6738 IF dbms_sql.fetch_rows( iad_select_c ) > 0 THEN
6739
6740 debug(' Fetched a row', MSG_LEVEL_DEBUG );
6741
6742 -----------------------------------------------
6743 -- Load row into record
6744 -----------------------------------------------
6745 dbms_sql.column_value( iad_select_c, 7, l_customer_trx_id );
6746 dbms_sql.column_value( iad_select_c, 5, l_adjusted_trx_id );
6747 dbms_sql.column_value( iad_select_c, 16, l_inv_trx_id );
6748
6749 -----------------------------------------------
6750 -- Check if adjusted trx or invoice changed
6751 -----------------------------------------------
6752 IF( is_new_adjusted_trx OR is_new_inv OR is_new_cm ) THEN
6753
6754 debug( ' new adjusted trx or invoice or cm',
6755 MSG_LEVEL_DEBUG );
6756
6757 -----------------------------------------------
6758 -- Check if adjusted trx changed
6759 -----------------------------------------------
6760 IF( is_new_adjusted_trx ) THEN
6761
6762 debug( ' new adjusted trx', MSG_LEVEL_DEBUG );
6763
6764 ---------------------------------------------------
6765 -- Start loading ps tables for new adjusted trx
6766 ---------------------------------------------------
6767 l_load_ps_tables := TRUE;
6768
6769 END IF;
6770
6771 IF( l_old_adjusted_trx_id IS NOT NULL ) THEN
6772
6773 debug( ' process1', MSG_LEVEL_DEBUG );
6774
6775 process_iad_data(
6776 system_info,
6777 profile_info,
6778 iad_insert_adj_c,
6779 iad_update_ps_c,
6780 l_select_rec,
6781 l_table_index,
6782 l_ps_id_t,
6783 l_ps_amount_due_rem_t,
6784 l_ps_acctd_amt_due_rem_t,
6785 l_ps_line_orig_t,
6786 l_ps_line_rem_t,
6787 l_ps_amount_adjusted_t,
6788 l_adj_amount_t,
6789 l_acctd_adj_amount_t,
6790 l_eff_adj_line_total,
6791 l_eff_adj_tax_total,
6792 l_eff_adj_frt_total,
6793 l_eff_line_bal,
6794 l_eff_tax_bal,
6795 l_eff_frt_bal,
6796 l_line_adj_t,
6797 l_tax_adj_t,
6798 l_frt_adj_t,
6799 l_ps_tax_orig_t,
6800 l_ps_tax_rem_t,
6801 l_ps_frt_orig_t,
6802 l_ps_frt_rem_t,
6803 l_inv_line_adj_t,
6804 l_inv_tax_adj_t,
6805 l_inv_frt_adj_t,
6806 l_is_new_adj_trx );
6807
6808 END IF;
6809
6810 -----------------------------------------------
6811 -- Check if new adjusted trx
6812 -----------------------------------------------
6813 IF( is_new_adjusted_trx ) THEN
6814
6815 clear_all_tables;
6816
6817 l_old_adjusted_trx_id := l_adjusted_trx_id;
6818 l_old_trx_id := l_customer_trx_id;
6819
6820 -- get total adjustments for new adjusted trx
6821 l_is_new_adj_trx := TRUE;
6822
6823 /* dbms_sql.column_value( iad_select_c, 31,
6824 l_eff_adj_line_total );
6825 dbms_sql.column_value( iad_select_c, 32,
6826 l_eff_adj_tax_total );
6827 dbms_sql.column_value( iad_select_c, 33,
6828 l_eff_adj_frt_total );
6829 */
6830 -----------------------------------------------
6831 -- Else if new cm
6832 -----------------------------------------------
6833 ELSIF( is_new_cm ) THEN
6834
6835 clear_cm_tables;
6836
6837 l_load_ps_tables := FALSE;
6838 l_old_trx_id := l_customer_trx_id;
6839
6840 END IF;
6841
6842 IF( is_new_inv ) THEN
6843
6844 l_old_inv_trx_id := l_inv_trx_id;
6845 -- get invoice line, tax, frt remaining for new invoice
6846 dbms_sql.column_value( iad_select_c, 24 ,
6847 l_eff_line_bal );
6848 dbms_sql.column_value( iad_select_c, 34 ,
6849 l_eff_tax_bal );
6850 dbms_sql.column_value( iad_select_c, 35,
6851 l_eff_frt_bal );
6852
6853 END IF;
6854
6855 END IF; -- END adjusted trx or inv or cm changed
6856
6857 get_iad_column_values( iad_select_c, l_select_rec );
6858 dump_iad_select_rec( l_select_rec );
6859
6860
6861 IF( l_load_ps_tables ) THEN
6862 load_tables( l_select_rec );
6863 END IF;
6864
6865 -- >> dump tables
6866
6867 ELSE
6868 -----------------------------------------------
6869 -- No more rows to fetch, process last set
6870 -----------------------------------------------
6871
6872 debug( ' process2', MSG_LEVEL_DEBUG );
6873
6874 process_iad_data(
6875 system_info,
6876 profile_info,
6877 iad_insert_adj_c,
6878 iad_update_ps_c,
6879 l_select_rec,
6880 l_table_index,
6881 l_ps_id_t,
6882 l_ps_amount_due_rem_t,
6883 l_ps_acctd_amt_due_rem_t,
6884 l_ps_line_orig_t,
6885 l_ps_line_rem_t,
6886 l_ps_amount_adjusted_t,
6887 l_adj_amount_t,
6888 l_acctd_adj_amount_t,
6889 l_eff_adj_line_total,
6890 l_eff_adj_tax_total,
6891 l_eff_adj_frt_total,
6892 l_eff_line_bal,
6893 l_eff_tax_bal,
6894 l_eff_frt_bal,
6895 l_line_adj_t,
6896 l_tax_adj_t,
6897 l_frt_adj_t,
6898 l_ps_tax_orig_t,
6899 l_ps_tax_rem_t,
6900 l_ps_frt_orig_t,
6901 l_ps_frt_rem_t,
6902 l_inv_line_adj_t,
6903 l_inv_tax_adj_t,
6904 l_inv_frt_adj_t,
6905 l_is_new_adj_trx );
6906
6907
6908
6909 EXIT;
6910
6911 END IF;
6912
6913
6914 END LOOP;
6915
6916 EXCEPTION
6917 WHEN OTHERS THEN
6918 debug( 'EXCEPTION: Error fetching select cursor',
6919 MSG_LEVEL_BASIC );
6920 RAISE;
6921
6922 END;
6923
6924 print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()-' );
6925
6926 EXCEPTION
6927 WHEN OTHERS THEN
6928 debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_child_adj_private()',
6929 MSG_LEVEL_BASIC );
6930 RAISE;
6931
6932 END insert_cm_child_adj_private;
6933
6934
6935 ---------------------------------------------------------------------------
6936 -- Test Functions
6937 ---------------------------------------------------------------------------
6938
6939 PROCEDURE test_build_ips_sql
6940 IS
6941
6942 BEGIN
6943
6944 build_ips_sql(system_info, profile_info, ips_select_c, ips_insert_ps_c);
6945
6946
6947 END;
6948
6949 ---------------------------------------------------------------------------
6950 PROCEDURE test_build_ira_sql
6951 IS
6952
6953 BEGIN
6954
6955 build_ira_sql(
6956 system_info,
6957 profile_info,
6958 ira_select_c,
6959 ira_insert_ps_c,
6960 ira_insert_ra_c,
6961 ira_update_ps_c);
6962
6963
6964 END;
6965
6966 ---------------------------------------------------------------------------
6967 PROCEDURE test_build_ups_sql
6968 IS
6969
6970 BEGIN
6971
6972 build_ups_sql(
6973 system_info,
6974 profile_info,
6975 ups_select_c,
6976 ups_insert_adj_c,
6977 ups_update_ps_c);
6978
6979
6980 END;
6981
6982 ---------------------------------------------------------------------------
6983 PROCEDURE test_build_iad_sql
6984 IS
6985
6986 BEGIN
6987
6988 build_iad_sql(
6989 system_info,
6990 profile_info,
6991 iad_select_c,
6992 iad_insert_adj_c,
6993 iad_update_ps_c);
6994
6995
6996 END;
6997
6998 ---------------------------------------------------------------------------
6999 PROCEDURE test_insert_inv_ps(
7000 p_customer_trx_id BINARY_INTEGER,
7001 p_reversed_cash_receipt_id IN BINARY_INTEGER )
7002 IS
7003
7004 BEGIN
7005
7006 insert_inv_ps_private(
7007 system_info,
7008 profile_info,
7009 p_customer_trx_id,
7010 p_reversed_cash_receipt_id
7011 );
7012
7013
7014 END;
7015
7016 ---------------------------------------------------------------------------
7017 PROCEDURE test_ai_insert_inv_ps(
7018 p_request_id BINARY_INTEGER,
7019 p_select_sql VARCHAR2 )
7020 IS
7021
7022 BEGIN
7023
7024 print_fcn_label( 'arp_maintain_ps2.test_ai_insert_inv_ps()+' );
7025
7026 build_ips_sql( system_info,
7027 profile_info,
7028 ips_select_c,
7029 ips_insert_ps_c );
7030
7031
7032 -- use the select sql passed as arg
7033 --
7034
7035 dbms_sql.close_cursor( ips_select_c );
7036 ips_select_c := dbms_sql.open_cursor;
7037
7038 debug(' select_sql='||p_select_sql );
7039
7040 debug(' parsing new select sql');
7041 dbms_sql.parse( ips_select_c, p_select_sql, dbms_sql.v7 );
7042
7043
7044
7045 insert_inv_ps_private(
7046 system_info,
7047 profile_info,
7048 p_request_id,
7049 null);
7050
7051 print_fcn_label( 'arp_maintain_ps2.test_ai_insert_inv_ps()-' );
7052
7053 END;
7054
7055 ---------------------------------------------------------------------------
7056 PROCEDURE test_insert_cm_ps( p_customer_trx_id BINARY_INTEGER )
7057 IS
7058
7059 BEGIN
7060
7061 insert_cm_ps_private(system_info, profile_info, p_customer_trx_id);
7062
7063
7064 END;
7065
7066 ---------------------------------------------------------------------------
7067 PROCEDURE test_ai_insert_cm_ps(
7068 p_request_id BINARY_INTEGER,
7069 p_select_sql VARCHAR2 )
7070 IS
7071
7072 BEGIN
7073
7074 print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_ps()+' );
7075
7076 build_ira_sql( system_info,
7077 profile_info,
7078 ira_select_c,
7079 ira_insert_ps_c,
7080 ira_insert_ra_c,
7081 ira_update_ps_c );
7082
7083
7084 -- use the select sql passed as arg
7085 --
7086
7087 dbms_sql.close_cursor( ira_select_c );
7088 ira_select_c := dbms_sql.open_cursor;
7089
7090 debug(' select_sql='||p_select_sql );
7091
7092 debug(' parsing new select sql');
7093 dbms_sql.parse( ira_select_c, p_select_sql, dbms_sql.v7 );
7094
7095
7096
7097 insert_cm_ps_private(system_info, profile_info, p_request_id);
7098
7099 print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_ps()-' );
7100
7101 END;
7102 ---------------------------------------------------------------------------
7103 PROCEDURE test_insert_child_adj( p_customer_trx_id BINARY_INTEGER )
7104 IS
7105
7106 BEGIN
7107
7108 insert_child_adj_private(system_info, profile_info, p_customer_trx_id);
7109
7110
7111 END;
7112
7113 ---------------------------------------------------------------------------
7114 PROCEDURE test_ai_insert_child_adj(
7115 p_request_id BINARY_INTEGER,
7116 p_select_sql VARCHAR2 )
7117 IS
7118
7119 BEGIN
7120
7121 print_fcn_label( 'arp_maintain_ps2.test_ai_insert_child_adj()+' );
7122
7123 build_ups_sql( system_info,
7124 profile_info,
7125 ups_select_c,
7126 ups_insert_adj_c,
7127 ups_update_ps_c );
7128
7129
7130 -- use the select sql passed as arg
7131 --
7132
7133 dbms_sql.close_cursor( ups_select_c );
7134 ups_select_c := dbms_sql.open_cursor;
7135
7136 debug(' select_sql='||p_select_sql );
7137
7138 debug(' parsing new select sql');
7139 dbms_sql.parse( ups_select_c, p_select_sql, dbms_sql.v7 );
7140
7141
7142
7143 insert_child_adj_private(system_info, profile_info, p_request_id);
7144
7145 print_fcn_label( 'arp_maintain_ps2.test_ai_insert_child_adj()-' );
7146
7147 END;
7148
7149 ---------------------------------------------------------------------------
7150 PROCEDURE test_insert_cm_child_adj( p_customer_trx_id BINARY_INTEGER )
7151 IS
7152
7153 BEGIN
7154
7155 insert_cm_child_adj_private(system_info, profile_info, p_customer_trx_id);
7156
7157
7158 END;
7159
7160 ---------------------------------------------------------------------------
7161 PROCEDURE test_ai_insert_cm_child_adj(
7162 p_request_id BINARY_INTEGER,
7163 p_select_sql VARCHAR2 )
7164 IS
7165
7166 BEGIN
7167
7168 print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_child_adj()+' );
7169
7170 build_iad_sql( system_info,
7171 profile_info,
7172 iad_select_c,
7173 iad_insert_adj_c,
7174 iad_update_ps_c );
7175
7176
7177 -- use the select sql passed as arg
7178 --
7179
7180 dbms_sql.close_cursor( iad_select_c );
7181 iad_select_c := dbms_sql.open_cursor;
7182
7183 debug(' select_sql='||p_select_sql );
7184
7185 debug(' parsing new select sql');
7186 dbms_sql.parse( iad_select_c, p_select_sql, dbms_sql.v7 );
7187
7188
7189
7190 insert_cm_child_adj_private(system_info, profile_info, p_request_id);
7191
7192 print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_child_adj()-' );
7193
7194 END;
7195
7196 ---------------------------------------------------------------------------
7197 --
7198 -- Constructor code
7199 --
7200 PROCEDURE init IS
7201 BEGIN
7202
7203 print_fcn_label( 'arp_maintain_ps2.constructor()+' );
7204
7205 DECLARE
7206
7207 l_result boolean;
7208 l_dummy varchar2(240);
7209
7210 BEGIN
7211 l_result := fnd_installation.get_app_info(
7212 'OE',
7213 g_oe_install_flag,
7214 l_dummy,
7215 l_dummy );
7216 -- OE/OM changes
7217 -- fnd_profile.get( 'SO_SOURCE_CODE', g_so_source_code );
7218 --
7219 oe_profile.get( 'SO_SOURCE_CODE', g_so_source_code );
7220
7221 debug( ' g_oe_install_flag='||g_oe_install_flag, MSG_LEVEL_DEBUG );
7222 debug( ' g_so_source_code='||g_so_source_code, MSG_LEVEL_DEBUG );
7223
7224 END;
7225
7226
7227 print_fcn_label( 'arp_maintain_ps2.constructor()-' );
7228
7229
7230 EXCEPTION
7231 WHEN OTHERS THEN
7232 debug('EXCEPTION: arp_maintain_ps2.constructor()');
7233 debug(SQLERRM);
7234 RAISE;
7235 END init;
7236
7237 BEGIN
7238 init;
7239 END arp_maintain_ps2;