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