[Home] [Help]
PACKAGE BODY: APPS.ARP_CREDIT_MEMO_MODULE
Source
1 PACKAGE BODY arp_credit_memo_module AS
2 /* $Header: ARTECMMB.pls 120.59.12010000.6 2008/11/20 11:46:57 npanchak ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'Y');
4
5 ------------------------------------------------------------------------
6 -- Private types
7 ------------------------------------------------------------------------
8 -- Constants
9 --
10 -- Linefeed character
11 --
12 CRLF CONSTANT VARCHAR2(1) := arp_global.CRLF;
13
14 MSG_LEVEL_BASIC CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_BASIC;
15 MSG_LEVEL_TIMING CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_TIMING;
16 MSG_LEVEL_DEBUG CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG;
17 MSG_LEVEL_DEBUG2 CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEBUG2;
18 MSG_LEVEL_DEVELOP CONSTANT BINARY_INTEGER := arp_global.MSG_LEVEL_DEVELOP;
19
20 AI_MSG_LEVEL CONSTANT BINARY_INTEGER := arp_global.sysparam.AI_LOG_FILE_MESSAGE_LEVEL;
21
22 YES CONSTANT VARCHAR2(1) := arp_global.YES;
23 NO CONSTANT VARCHAR2(1) := arp_global.NO;
24
25 PRORATE CONSTANT VARCHAR2(1) := 'P';
26 LIFO CONSTANT VARCHAR2(1) := 'L';
27 UNIT CONSTANT VARCHAR2(1) := 'U';
28
29 I CONSTANT VARCHAR2(1) := 'I';
30 U CONSTANT VARCHAR2(1) := 'U';
31
32 --
33 -- User-defined exceptions
34 --
35 invalid_parameters EXCEPTION;
36 invalid_mode EXCEPTION;
37 missing_periods EXCEPTION;
38 overapp_not_allowed EXCEPTION;
39 invalid_cm_method_for_rules EXCEPTION;
40
41 error_defaulting_gl_date EXCEPTION;
42 no_net_revenue EXCEPTION;
43 cm_unit_overapp EXCEPTION;
44
45 inv_locked_by_another_session EXCEPTION;
46 PRAGMA EXCEPTION_INIT(inv_locked_by_another_session,-54) ;
47 --
48 -- Translated error messages
49 --
50 MSG_INVALID_PARAMETERS VARCHAR2(2000);
51 MSG_MISSING_PERIODS VARCHAR2(2000);
52 MSG_OVERAPP_NOT_ALLOWED VARCHAR2(2000);
53 MSG_NO_NET_REVENUE VARCHAR2(2000);
54
55 MSG_INV_LOCKED VARCHAR2(2000);
56 MSG_CM_UNIT_OVERAPP VARCHAR2(2000);
57 MSG_INV_LOCKED_BY_JL VARCHAR2(2000);
58
59
60
61 -- This record holds general information used by autoaccounting and
62 -- credit memo module. Passed as argument to most functions/procs.
63 --
64 system_info arp_trx_global.system_info_rec_type :=
65 arp_trx_global.system_info;
66
67 --
68 -- This record holds profile information used by autoaccounting and
69 -- credit memo module. Passed as argument to most functions/procs.
70 --
71 profile_info arp_trx_global.profile_rec_type :=
72 arp_trx_global.profile_info;
73
74
75 --
76 -- This record holds accounting flexfield information used by
77 -- autoaccounting and the credit memo module. Passed as argument to
78 -- most functions/procs.
79 --
80 flex_info arp_trx_global.acct_flex_info_rec_type :=
81 arp_trx_global.flex_info;
82
83 ------------------------------------------------------------------------
84 -- Data structs for net revenue schedule
85 ------------------------------------------------------------------------
86 TYPE net_rev_rec_type IS RECORD
87 (
88 customer_trx_line_id BINARY_INTEGER,
89 gl_date DATE,
90 amount NUMBER,
91 net_unit_price NUMBER,
92 inv_dist_exists VARCHAR(1)
93 );
94 net_revenue_rec net_rev_rec_type;
95
96 net_rev_start_index BINARY_INTEGER;
97 net_rev_index BINARY_INTEGER; -- keeps track of next row to insert
98
99 TYPE net_rev_ctlid_table_type IS
100 TABLE OF net_revenue_rec.customer_trx_line_id%type
101 INDEX BY BINARY_INTEGER;
102 net_rev_ctlid_t net_rev_ctlid_table_type;
103 null_net_rev_ctlid CONSTANT net_rev_ctlid_table_type := net_rev_ctlid_t;
104
105 TYPE net_rev_gl_date_table_type IS
106 TABLE OF net_revenue_rec.gl_date%type
107 INDEX BY BINARY_INTEGER;
108 net_rev_gl_date_t net_rev_gl_date_table_type;
109 null_net_rev_gl_date CONSTANT net_rev_gl_date_table_type := net_rev_gl_date_t;
110
111 TYPE net_rev_amount_table_type IS
112 TABLE OF net_revenue_rec.amount%type
113 INDEX BY BINARY_INTEGER;
114 net_rev_amount_t net_rev_amount_table_type;
115 null_net_rev_amount CONSTANT net_rev_amount_table_type := net_rev_amount_t;
116
117 TYPE net_rev_unit_table_type IS
118 TABLE OF net_revenue_rec.net_unit_price%type
119 INDEX BY BINARY_INTEGER;
120 net_rev_unit_t net_rev_unit_table_type;
121 null_net_rev_unit CONSTANT net_rev_unit_table_type := net_rev_unit_t;
122
123 TYPE net_rev_dist_exists_tab_type IS
124 TABLE OF net_revenue_rec.inv_dist_exists%type
125 INDEX BY BINARY_INTEGER;
126 net_rev_dist_exists_t net_rev_dist_exists_tab_type;
127 null_net_rev_dist_exists CONSTANT net_rev_dist_exists_tab_type :=
128 net_rev_dist_exists_t;
129
130 ------------------------------------------------------------------------
131 -- Data structs for cm schedule
132 ------------------------------------------------------------------------
133 TYPE cm_schedule_rec_type IS RECORD
134 (
135 customer_trx_line_id BINARY_INTEGER,
136 gl_date DATE,
137 orig_gl_date DATE,
138 amount NUMBER,
139 insert_dist VARCHAR2(1),
140 insert_cma VARCHAR2(1),
141 insert_offset VARCHAR2(1),
142 check_gl_date VARCHAR2(1) -- for crediting rel9 immed invoices
143 );
144 cm_schedule_rec cm_schedule_rec_type;
145
146 cm_sched_start_index BINARY_INTEGER;
147 cm_sched_index BINARY_INTEGER := 0; -- keeps track of next row to insert
148 cm_mrc_index INTEGER := 0;
149
150 TYPE cm_sched_ctlid_table_type IS
151 TABLE OF cm_schedule_rec.customer_trx_line_id%type
152 INDEX BY BINARY_INTEGER;
153 cm_sched_ctlid_t cm_sched_ctlid_table_type;
154 null_cm_sched_ctlid CONSTANT cm_sched_ctlid_table_type := cm_sched_ctlid_t;
155
156 TYPE cm_sched_gl_date_table_type IS
157 TABLE OF cm_schedule_rec.gl_date%type
158 INDEX BY BINARY_INTEGER;
159 cm_sched_gl_date_t cm_sched_gl_date_table_type;
160 null_cm_sched_gl_date CONSTANT cm_sched_gl_date_table_type :=
161 cm_sched_gl_date_t;
162
163 TYPE cm_sched_ogl_date_table_type IS
164 TABLE OF cm_schedule_rec.orig_gl_date%type
165 INDEX BY BINARY_INTEGER;
166 cm_sched_orig_gl_date_t cm_sched_ogl_date_table_type;
167 null_cm_sched_orig_gl_date CONSTANT cm_sched_ogl_date_table_type :=
168 cm_sched_orig_gl_date_t;
169
170 TYPE cm_sched_amount_table_type IS
171 TABLE OF cm_schedule_rec.amount%type
172 INDEX BY BINARY_INTEGER;
173 cm_sched_amount_t cm_sched_amount_table_type;
174 null_cm_sched_amount CONSTANT cm_sched_amount_table_type := cm_sched_amount_t;
175
176 TYPE cm_sched_insert_dist_tab_type IS
177 TABLE OF cm_schedule_rec.insert_dist%type
178 INDEX BY BINARY_INTEGER;
179 cm_sched_insert_dist_t cm_sched_insert_dist_tab_type;
180 null_cm_sched_insert_dist CONSTANT cm_sched_insert_dist_tab_type :=
181 cm_sched_insert_dist_t;
182
183 TYPE cm_sched_insert_cma_tab_type IS
184 TABLE OF cm_schedule_rec.insert_cma%type
185 INDEX BY BINARY_INTEGER;
186 cm_sched_insert_cma_t cm_sched_insert_cma_tab_type;
187 null_cm_sched_insert_cma CONSTANT cm_sched_insert_cma_tab_type :=
188 cm_sched_insert_cma_t;
189
190 TYPE cm_sched_ins_offset_tab_type IS
191 TABLE OF cm_schedule_rec.insert_offset%type
192 INDEX BY BINARY_INTEGER;
193 cm_sched_insert_offset_t cm_sched_ins_offset_tab_type;
194 null_cm_sched_insert_offset CONSTANT cm_sched_ins_offset_tab_type :=
195 cm_sched_insert_offset_t;
196
197 TYPE cm_sched_check_gld_tab_type IS
198 TABLE OF cm_schedule_rec.check_gl_date%type
199 INDEX BY BINARY_INTEGER;
200 cm_sched_check_gl_date_t cm_sched_check_gld_tab_type;
201 null_cm_sched_check_gl_date CONSTANT cm_sched_check_gld_tab_type :=
202 cm_sched_check_gl_date_t;
203
204 TYPE cm_mrc_cust_trx_line_id_type IS
205 TABLE OF ra_customer_trx_lines.customer_trx_line_id%type
206 INDEX BY BINARY_INTEGER;
207 mrc_cm_cust_trx_line_id cm_mrc_cust_trx_line_id_type;
208 mrc_cust_line_id ra_customer_trx_lines.customer_trx_line_id%TYPE;
209
210
211 --
212 --
213 --
214 TYPE control_rec_type IS RECORD
215 (
216 customer_trx_id BINARY_INTEGER,
217 customer_trx_line_id BINARY_INTEGER,
218 prev_customer_trx_id BINARY_INTEGER,
219 prev_cust_trx_line_id BINARY_INTEGER,
220 request_id BINARY_INTEGER
221 );
222
223 --
224 -- To hold values fetched from the Select stmt
225 --
226 TYPE select_rec_type IS RECORD
227 (
228 customer_trx_line_id BINARY_INTEGER,
229 prev_cust_trx_line_id BINARY_INTEGER,
230 allow_overapp_flag ra_cust_trx_types.allow_overapplication_flag%type,
231 cm_amount NUMBER,
232 credit_method_for_rules VARCHAR2(1),
233 last_period_to_credit NUMBER,
234 currency_code ra_customer_trx.invoice_currency_code%type,
235 inv_acct_rule_duration NUMBER,
236 allow_not_open_flag VARCHAR2(1),
237 partial_period_flag VARCHAR2(1),
238 cm_gl_date DATE,
239 invoice_quantity NUMBER,
240 cm_quantity NUMBER,
241 invoice_sign NUMBER, -- 3198525 from char(1) to number
242 cm_sign NUMBER, -- 3198525 from char(1) to number
243 rule_start_date DATE, -- output only
244 rule_end_date DATE, -- output only
245 cm_acct_rule_duration NUMBER, -- output only
246 inv_unit_price NUMBER, -- 4621029
247 cm_unit_price NUMBER -- 4621029
248 );
249
250 /* Bug 2560036 - Control test of collectibility */
251 g_test_collectibility BOOLEAN;
252 /* Bug 2347001 - unique identifier for each use of global tmp table */
253 g_session_id NUMBER := 0;
254 /* Bug 4633761 - stored inv line id for array processing */
255 g_prev_ctlid NUMBER := 0;
256
257 /* 6678560 - booleans to control finds for dynamic sql */
258 g_bind_line_14 BOOLEAN; -- line_id binds 1-4
259 g_bind_trx_12 BOOLEAN; -- trx_id binds 1-2
260 g_bind_req_12 BOOLEAN; -- req_id binds 1-2
261 g_bind_trx_3 BOOLEAN; -- trx_id bind 3 (inline)
262 g_bind_req_3 BOOLEAN; -- req_id bind 3
263
264 ------------------------------------------------------------------------
265 -- Private cursors
266 ------------------------------------------------------------------------
267 nonrule_insert_dist_c INTEGER;
268 nonrule_update_lines_c INTEGER;
269 nonrule_update_dist_c INTEGER;
270 nonrule_update_dist2_c INTEGER;
271
272 rule_select_cm_lines_c INTEGER;
273 rule_update_cm_lines_c INTEGER;
274 rule_insert_dist_c INTEGER;
275 rule_insert_cma_c INTEGER;
276
277 net_revenue_line_c INTEGER;
278
279 delete_header_dist_c INTEGER;
280 delete_line_dist_c INTEGER;
281 delete_header_cma_c INTEGER;
282 delete_line_cma_c INTEGER;
283 update_header_lines_c INTEGER;
284 update_lines_c INTEGER;
285
286 /* Bug 3477990 */
287 pg_closed_period_exists VARCHAR2(1) := NULL;
288
289 ------------------------------------------------------------------------
290 -- Covers
291 ------------------------------------------------------------------------
292 PROCEDURE debug( p_line IN VARCHAR2 ) IS
293 BEGIN
294 arp_standard.debug( p_line );
295 END;
296 --
297 PROCEDURE debug(
298 p_str VARCHAR2,
299 p_print_level BINARY_INTEGER ) IS
300 BEGIN
301 arp_standard.debug( p_str );
302 END;
303 --
304 PROCEDURE enable_debug IS
305 BEGIN
306 arp_standard.enable_debug;
307 END;
308 --
309 PROCEDURE enable_debug( buffer_size NUMBER ) IS
310 BEGIN
311 arp_standard.enable_debug;
312 END;
313 --
314 PROCEDURE disable_debug IS
315 BEGIN
316 arp_util.disable_debug;
317 END;
318 --
319 PROCEDURE print_fcn_label( p_label VARCHAR2 ) IS
320 BEGIN
321 arp_standard.debug( p_label );
322 END;
323 --
324 PROCEDURE print_fcn_label2( p_label VARCHAR2 ) IS
325 BEGIN
326 arp_standard.debug( p_label );
327 END;
328 --
329 PROCEDURE close_cursor( p_cursor_handle IN OUT NOCOPY INTEGER ) IS
330 BEGIN
331 arp_util.close_cursor( p_cursor_handle );
332 END;
333
334
335 ----------------------------------------------------------------------------
336 -- Functions and Procedures
337 ----------------------------------------------------------------------------
338 PROCEDURE close_cursors IS
339 BEGIN
340 close_cursor( nonrule_insert_dist_c );
341 close_cursor( nonrule_update_lines_c );
342 close_cursor( nonrule_update_dist_c );
343 close_cursor( nonrule_update_dist2_c );
344
345 close_cursor( rule_select_cm_lines_c );
346 close_cursor( rule_update_cm_lines_c );
347 close_cursor( rule_insert_dist_c );
348 close_cursor( rule_insert_cma_c );
349
350 close_cursor( net_revenue_line_c );
351 END;
352
353
354 PROCEDURE insert_into_error_table(
355 p_interface_line_id binary_integer,
356 p_message_text varchar2,
357 p_invalid_value varchar2 ) IS
358
359 BEGIN
360
361 INSERT INTO ra_interface_errors
362 (interface_line_id,
363 message_text,
364 invalid_value,
365 org_id)
366 VALUES
367 (p_interface_line_id,
368 p_message_text,
369 p_invalid_value,
370 arp_standard.sysparm.org_id);
371
372 END insert_into_error_table;
373
374 ----------------------------------------------------------------------------
375 PROCEDURE get_error_message_text is
376
377 l_msg_name VARCHAR2(100);
378
379 BEGIN
380
381 print_fcn_label( 'arp_credit_memo_module.get_error_message_text()+' );
382
383 ---
384 l_msg_name := 'AR-CREDMEMO_ACTION_PARAM';
385 fnd_message.set_name('AR', l_msg_name);
386 MSG_INVALID_PARAMETERS := fnd_message.get;
387
388 ----
389 l_msg_name := 'JL_BR_EI_CREDIT_ERROR';
390 fnd_message.set_name('JL', l_msg_name);
394 l_msg_name := 'AR_RAXTRX-1783';
391 MSG_INV_LOCKED_BY_JL := fnd_message.get;
392
393 ----
395 fnd_message.set_name('AR', l_msg_name);
396 MSG_MISSING_PERIODS := fnd_message.get;
397
398 ----
399 l_msg_name := 'AR_CKAP_OVERAPP';
400 fnd_message.set_name('AR', l_msg_name);
401 MSG_OVERAPP_NOT_ALLOWED := fnd_message.get;
402
403 ----
404 l_msg_name := 'AR_CREDMEMO_NO_NET_REV';
405 fnd_message.set_name('AR', l_msg_name);
406 MSG_NO_NET_REVENUE := fnd_message.get;
407
408 ----
409 l_msg_name := 'AR_RAXTRX-1801';
410 fnd_message.set_name('AR', l_msg_name);
411 MSG_INV_LOCKED := fnd_message.get;
412
413 /* 4621029 */
414 l_msg_name := 'AR_RAXTRX_UNIT_OVERAPP';
415 fnd_message.set_name('AR', l_msg_name);
416 MSG_CM_UNIT_OVERAPP := fnd_message.get;
417
418 -- print
419 debug( 'MSG_INVALID_PARAMETERS='||MSG_INVALID_PARAMETERS,
420 MSG_LEVEL_DEBUG );
421 debug( 'MSG_MISSING_PERIODS='||MSG_MISSING_PERIODS,
422 MSG_LEVEL_DEBUG );
423 debug( 'MSG_OVERAPP_NOT_ALLOWED='||MSG_OVERAPP_NOT_ALLOWED,
424 MSG_LEVEL_DEBUG );
425 debug( 'MSG_NO_NET_REVENUE='||MSG_NO_NET_REVENUE,
426 MSG_LEVEL_DEBUG );
427
428 debug( 'MSG_INV_LOCKED='||MSG_INV_LOCKED,
429 MSG_LEVEL_DEBUG );
430 debug( 'MSG_INV_LOCKED_BY_JL='||MSG_INV_LOCKED_BY_JL,
431 MSG_LEVEL_DEBUG );
432 debug( 'MSG_CM_UNIT_OVERAPP='||MSG_CM_UNIT_OVERAPP,
433 MSG_LEVEL_DEBUG );
434
435 print_fcn_label( 'arp_credit_memo_module.get_error_message_text()-' );
436
437 EXCEPTION
438 WHEN OTHERS THEN
439 debug('EXCEPTION: arp_credit_memo_module.get_error_message_text()');
440 RAISE;
441 END get_error_message_text;
442
443 /* 6129294 - function to validate gl_dates that were overridden
444 by user in revenue accounting */
445 FUNCTION get_valid_date(p_gl_date IN DATE,
446 p_inv_rule_id IN NUMBER,
447 p_set_of_books_id IN NUMBER) RETURN date
448 IS
449 l_gl_date DATE;
450 l_defaulting_rule_used VARCHAR2(100);
451 l_error_message VARCHAR2(512);
452 BEGIN
453 IF arp_standard.validate_and_default_gl_date(
454 p_gl_date,
455 NULL,
456 NULL,
457 NULL,
458 NULL,
459 NULL,
460 NULL,
461 NULL,
462 'N',
463 p_inv_rule_id,
464 p_set_of_books_id,
465 222,
466 l_gl_date,
467 l_defaulting_rule_used,
468 l_error_message)
469 THEN
470 IF PG_DEBUG in ('Y', 'C')
471 THEN
472 debug('get_valid_date() rule=' || l_defaulting_rule_used ||
473 ' date=' || l_gl_date);
474 END IF;
475 RETURN l_gl_date;
476 END IF;
477
478 /* If it reaches here, then the date could not be defaulted, so
479 return null */
480 IF PG_DEBUG in ('Y','C')
481 THEN
482 debug('get_valid_date() failure - date=' || p_gl_date ||
483 ' error=' || l_error_message);
484 END IF;
485 RETURN NULL;
486 END get_valid_date;
487
488 ----------------------------------------------------------------------------
489 PROCEDURE build_update_mode_sql(
490 p_delete_header_dist_c IN OUT NOCOPY INTEGER,
491 p_delete_line_dist_c IN OUT NOCOPY INTEGER,
492 p_delete_header_cma_c IN OUT NOCOPY INTEGER,
493 p_delete_line_cma_c IN OUT NOCOPY INTEGER,
494 p_update_header_lines_c IN OUT NOCOPY INTEGER,
495 p_update_lines_c IN OUT NOCOPY INTEGER ) IS
496
497 l_delete_header_dist_sql VARCHAR2(1000);
498 l_delete_line_dist_sql VARCHAR2(1000);
499 l_delete_header_cma_sql VARCHAR2(1000);
500 l_delete_line_cma_sql VARCHAR2(1000);
501 l_update_header_lines_sql VARCHAR2(1000);
502 l_update_lines_sql VARCHAR2(1000);
503
504
505 BEGIN
506
507 print_fcn_label( 'arp_credit_memo_module.build_update_mode_sql()+' );
508
509 l_delete_header_dist_sql :=
510 'DELETE from ra_cust_trx_line_gl_dist
511 WHERE customer_trx_id = :customer_trx_id
512 and account_class <> ''REC''
513 and account_set_flag = ''N'' ';
514
515 /* add returning clause for mrc */
516 l_delete_header_dist_sql := l_delete_header_dist_sql ||
517 ' RETURNING cust_trx_line_gl_dist_id INTO :gl_dist_key_value ';
518
519 debug(l_delete_header_dist_sql);
520 debug(' len(delete_header_dist_sql) = '||
521 to_char(lengthb(l_delete_header_dist_sql)));
522
523
524 l_delete_line_dist_sql :=
525 'DELETE from ra_cust_trx_line_gl_dist
529 /* add returning clause for mrc */
526 WHERE customer_trx_line_id = :customer_trx_line_id
527 AND account_set_flag = ''N'' ';
528
530 l_delete_line_dist_sql := l_delete_line_dist_sql ||
531 ' RETURNING cust_trx_line_gl_dist_id INTO :gl_dist_key_value ';
532
533 debug(l_delete_line_dist_sql);
534 debug(' len(delete_line_dist_sql) = '||
535 to_char(lengthb(l_delete_line_dist_sql)));
536
537 l_delete_header_cma_sql :=
538 'DELETE from ar_credit_memo_amounts
539 WHERE customer_trx_line_id in
540 (SELECT customer_trx_line_id
541 FROM ra_customer_trx_lines
542 WHERE line_type = ''LINE''
543 and customer_trx_id = :customer_trx_id)';
544
545 debug(l_delete_header_cma_sql);
546 debug(' len(delete_header_cma_sql) = '||
547 to_char(lengthb(l_delete_header_cma_sql)));
548
549 l_delete_line_cma_sql :=
550 'DELETE from ar_credit_memo_amounts
551 WHERE customer_trx_line_id = :customer_trx_line_id';
552
553 debug(l_delete_line_cma_sql);
554 debug(' len(delete_line_cma_sql) = '||
555 to_char(lengthb(l_delete_line_cma_sql)));
556
557 l_update_header_lines_sql :=
558 'UPDATE ra_customer_trx_lines
559 SET
560 rule_start_date = null,
561 rule_end_date = null,
562 accounting_rule_duration = null
563 WHERE customer_trx_id = :customer_trx_id
564 and line_type = ''LINE'' ';
565
566 debug(l_update_header_lines_sql);
567 debug(' len(update_header_lines_sql) = '||
568 to_char(lengthb(l_update_header_lines_sql)));
569
570 l_update_lines_sql :=
571 'UPDATE ra_customer_trx_lines
572 SET
573 rule_start_date = null,
574 rule_end_date = null,
575 accounting_rule_duration = null
576 WHERE customer_trx_line_id = :customer_trx_line_id';
577
578 debug(l_update_lines_sql);
579 debug(' len(update_lines_sql) = '||
580 to_char(lengthb(l_update_lines_sql)));
581
582
583 ------------------------------------------------
584 -- Parse sql stmts
585 ------------------------------------------------
586 BEGIN
587 debug( ' Parsing update mode stmts', MSG_LEVEL_DEBUG );
588
589 p_delete_header_dist_c := dbms_sql.open_cursor;
590 dbms_sql.parse( p_delete_header_dist_c, l_delete_header_dist_sql,
591 dbms_sql.v7 );
592
593 p_delete_line_dist_c := dbms_sql.open_cursor;
594 dbms_sql.parse( p_delete_line_dist_c, l_delete_line_dist_sql,
595 dbms_sql.v7 );
596
597 p_delete_header_cma_c := dbms_sql.open_cursor;
598 dbms_sql.parse( p_delete_header_cma_c, l_delete_header_cma_sql,
599 dbms_sql.v7 );
600
601 p_delete_line_cma_c := dbms_sql.open_cursor;
602 dbms_sql.parse( p_delete_line_cma_c, l_delete_line_cma_sql,
603 dbms_sql.v7 );
604
605 p_update_header_lines_c := dbms_sql.open_cursor;
606 dbms_sql.parse( p_update_header_lines_c, l_update_header_lines_sql,
607 dbms_sql.v7 );
608
609 p_update_lines_c := dbms_sql.open_cursor;
610 dbms_sql.parse( p_update_lines_c, l_update_lines_sql,
611 dbms_sql.v7 );
612
613 EXCEPTION
614 WHEN OTHERS THEN
615 debug( 'EXCEPTION: Error parsing update mode stmts' );
616 RAISE;
617 END;
618
619
620 print_fcn_label( 'arp_credit_memo_module.build_update_mode_sql()-' );
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 debug( 'EXCEPTION: arp_credit_memo_module.build_update_mode_sql()' );
625
626 RAISE;
627 END build_update_mode_sql;
628
629 ----------------------------------------------------------------------------
630 --
631 -- PROCEDURE NAME: build_nonrule_sql
632 --
633 -- DECSRIPTION:
634 --
635 -- ARGUMENTS:
636 -- IN:
637 -- system_info
638 -- profile_info
639 -- cm_control
640 --
641 -- IN/OUT:
642 -- nonrule_insert_dist_c
643 -- nonrule_update_lines_c
644 -- nonrule_update_dist_c
645 -- nonrule_update_dist2_c
646 --
647 -- OUT:
648 --
649 -- RETURNS:
650 --
651 -- NOTES:
652 --
653 -- HISTORY:
654 /* M Raymond 01-DEC-2004 Bug 4029814 - removed mock gl_dist_id
655 logic and allowed BRI trigger on table
656 to set IDs during insert.
657 */
658
659 ----------------------------------------------------------------------------
660 PROCEDURE build_nonrule_sql(
661 p_system_info IN arp_trx_global.system_info_rec_type,
662 p_profile_info IN arp_trx_global.profile_rec_type,
663 p_cm_control IN control_rec_type,
664 p_nonrule_insert_dist_c IN OUT NOCOPY INTEGER,
665 p_nonrule_update_lines_c IN OUT NOCOPY INTEGER,
666 p_nonrule_update_dist_c IN OUT NOCOPY INTEGER,
667 p_nonrule_update_dist2_c IN OUT NOCOPY INTEGER ) IS
668
669
670 l_nonrule_insert_dist_sql VARCHAR2(32767);
671 l_nonrule_update_lines_sql VARCHAR2(1000);
675 l_where_pred VARCHAR2(500);
672 l_nonrule_update_dist_sql VARCHAR2(1000);
673 l_nonrule_update_dist2_sql VARCHAR2(2000);
674
676 l_rec_where_pred VARCHAR2(500);
677 l_amount_fragment VARCHAR2(2500);
678 l_dbg_buffer VARCHAR2(500);
679
680 BEGIN
681
682 print_fcn_label( 'arp_credit_memo_module.build_nonrule_sql()+' );
683
684 ------------------------------------------------
685 -- Initialize
686 ------------------------------------------------
687 g_bind_line_14 := FALSE; -- line_id (4 separate binds) used
688 g_bind_trx_12 := FALSE; -- trx_id used
689 g_bind_req_12 := FALSE; -- req_id used
690 g_bind_trx_3 := FALSE; -- trx_id used (for inline srep join)
691 g_bind_req_3 := FALSE; -- req_id used (for inline srep join)
692
693 ------------------------------------------------
694 -- Construct where predicate
695 ------------------------------------------------
696 IF( p_cm_control.customer_trx_line_id IS NOT NULL ) THEN
697 ----------------------------------------------------
698 -- Passed line id
699 ----------------------------------------------------
700 /* bug fix 956189 */
701
702 l_where_pred :=
703 'AND (ctl.customer_trx_line_id =
704 :cm_cust_trx_line_id_1' || CRLF ||
705 ' OR
706 ctl.link_to_cust_trx_line_id = :cm_cust_trx_line_id_2';
707
708 l_rec_where_pred :=
709 'AND (ctl.customer_trx_line_id =
710 :cm_cust_trx_line_id_3' || CRLF ||
711 ' OR
712 ctl.link_to_cust_trx_line_id = :cm_cust_trx_line_id_4';
713
714 g_bind_line_14 := TRUE;
715
716 IF( p_cm_control.customer_trx_id IS NOT NULL ) THEN
717 ------------------------------------------------
718 -- Passed trx id
719 ------------------------------------------------
720 /* bug fix 956189 */
721 l_where_pred := l_where_pred || CRLF ||
722 ' OR
723 ctl.link_to_cust_trx_line_id is null)
724 AND ctl.customer_trx_id = :cm_customer_trx_id_1';
725
726 l_rec_where_pred := l_rec_where_pred || CRLF ||
727 ' OR
728 ctl.link_to_cust_trx_line_id is null)
729 AND ctl.customer_trx_id = :cm_customer_trx_id_2';
730
731 g_bind_trx_12 := TRUE;
732
733 ELSE
734
735 l_where_pred := l_where_pred || ')';
736 l_rec_where_pred := l_rec_where_pred || ')';
737
738 END IF;
739
740 ELSE
741 ----------------------------------------------------
742 -- Did not pass line id
743 ----------------------------------------------------
744 IF( p_cm_control.customer_trx_id IS NOT NULL ) THEN
745
746 /* bug fix 956189 */
747 l_where_pred :=
748 'AND ctl.customer_trx_id = :cm_customer_trx_id_1';
749
750 l_rec_where_pred :=
751 'AND ctl.customer_trx_id = :cm_customer_trx_id_2';
752
753 g_bind_trx_12 := TRUE;
754
755 ELSE
756
757 l_where_pred :=
758 'AND ctl.request_id = :request_id_1';
759
760 l_rec_where_pred :=
761 'AND ctl.request_id = :request_id_2';
762
763 g_bind_req_12 := TRUE;
764
765 END IF;
766
767 END IF;
768
769 ------------------------------------------------
770 -- Construct amount fragment
771 ------------------------------------------------
772 l_amount_fragment :=
773 '(
774 nvl(
775 prev_ctlgd.amount /
776 decode(
777 decode(prev_ctlgd.account_class,
778 ''REV'', prev_ctl.revenue_amount,
779 ''SUSPENSE'', (prev_ctl.extended_amount -
780 prev_ctl.revenue_amount),
781 prev_ctl.extended_amount),
782 0, 1,
783 decode(prev_ctlgd.account_class,
784 ''REV'', prev_ctl.revenue_amount,
785 ''SUSPENSE'', (prev_ctl.extended_amount -
786 prev_ctl.revenue_amount),
787 prev_ctl.extended_amount)
788 ),
789 1)) *
790 decode(
791 decode(prev_ctlgd.account_class,
792 ''REV'', ctl.revenue_amount,
793 ''SUSPENSE'', (ctl.extended_amount -
794 ctl.revenue_amount),
795 ctl.extended_amount),
796 0, decode(
797 decode(prev_ctlgd.account_class,
798 ''REV'', prev_ctl.revenue_amount,
799 ''SUSPENSE'', (ctl.extended_amount -
800 prev_ctl.revenue_amount),
801 prev_ctl.extended_amount),
802 0, -1,
803 0),
804 decode(prev_ctlgd.account_class,
805 ''REV'', ctl.revenue_amount,
806 ''SUSPENSE'', (ctl.extended_amount -
807 ctl.revenue_amount),
808 ctl.extended_amount)
812 ------------------------------------------------
809 )' ;
810
811
813 -- Construct insert into ra_cust_trx_line_gl_dist
814 ------------------------------------------------
815 l_nonrule_insert_dist_sql :=
816 'INSERT into ra_cust_trx_line_gl_dist
817 (
818 /* gl_dist_id used to be here - now populated by BRI trigger */
819 customer_trx_id, /* credit memo customer_trx_id */
820 customer_trx_line_id, /* credit memo customer_trx_line_id */
821 cust_trx_line_salesrep_id, /* id for cm srep line credited */
822 request_id,
823 set_of_books_id,
824 last_update_date,
825 last_updated_by,
826 creation_date,
827 created_by,
828 last_update_login,
829 program_application_id,
830 program_id,
831 program_update_date,
832 account_class, /* account class for the invoice */
833 /* assignment being credited */
834 account_set_flag,
835 percent,
836 amount ,
837 acctd_amount,
838 gl_date,
839 code_combination_id,
840 posting_control_id,
841 collected_tax_ccid,
842 ussgl_transaction_code, /*Bug 2246098*/
843 revenue_adjustment_id, /* Bug 2543675 - RAM id copied to CM dist */
844 rec_offset_flag, /* Bug 2560036 - non-collectible trans */
845 org_id
846 ) ';
847
848 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
849 ' /* Non Receivable account case */
850 SELECT
851 /* Bug 4029814 - removed gl_dist mock logic because of unique constraints */
852 ctl.customer_trx_id,
853 ctl.customer_trx_line_id,
854 ctls.cust_trx_line_salesrep_id,
855 ct.request_id,
856 ct.set_of_books_id,
857 sysdate,
858 ct.last_updated_by,
859 sysdate,
860 ct.created_by,
861 ct.last_update_login,
862 ct.program_application_id, /* program_appl_id */
863 ct.program_id, /* program_id */
864 sysdate, /* program_update_date */
865 prev_ctlgd.account_class,
866 ''N'', /* account set for rules flag */
867 decode(ctl.extended_amount,
868 0, prev_ctlgd.percent,
869 round(((decode(foreign_fc.minimum_accountable_unit,
870 null, round( ' || CRLF ||
871 l_amount_fragment || CRLF ||
872 ' , precision),
873 round( ' || CRLF ||
874 l_amount_fragment || CRLF ||
875 ' / foreign_fc.minimum_accountable_unit) *
876 foreign_fc.minimum_accountable_unit
877 ) /
878 decode(ctl.extended_amount, 0, 1, ctl.extended_amount)
879 ) * decode(ctl.extended_amount, 0, 0, 1)
880 ) * 100, 4)
881 ), /* percent */
882 decode(foreign_fc.minimum_accountable_unit,
883 null, round( ' || CRLF ||
884 l_amount_fragment || CRLF ||
885 ' , precision),
886 round( ' || CRLF ||
887 l_amount_fragment || CRLF ||
888 ' / foreign_fc.minimum_accountable_unit) *
889 foreign_fc.minimum_accountable_unit
890 ), /* amount */';
891
892
893 -------------------------------------------------------------------
894 -- Construct the amounts differently depending on whether the
895 -- minimum accountable unit is specified for the base currency.
896 -------------------------------------------------------------------
897
898 IF( p_system_info.base_min_acc_unit IS NULL ) THEN
899
900 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
901 ' round(decode(foreign_fc.minimum_accountable_unit,
902 null, round(' || CRLF ||
903 l_amount_fragment || CRLF ||
904 ' , precision),
905 round( ' || CRLF ||
906 l_amount_fragment || CRLF ||
907 ' / foreign_fc.minimum_accountable_unit) *
908 foreign_fc.minimum_accountable_unit
909 ) * nvl(ct.exchange_rate, 1),
910 ' || p_system_info.base_precision || CRLF ||
911 ' ), /* acctd_amount */';
912
913 ELSE
914
915 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
916 ' round(decode(foreign_fc.minimum_accountable_unit,
917 null, round( ' || CRLF ||
918 l_amount_fragment || CRLF ||
919 ' , precision),
920 round( ' || CRLF ||
921 l_amount_fragment || CRLF ||
922 ' / foreign_fc.minimum_accountable_unit) *
923 foreign_fc.minimum_accountable_unit
924 ) * nvl(ct.exchange_rate, 1) / ' ||
925 fnd_number.number_to_canonical(system_info.base_min_acc_unit) || CRLF ||
926 ' ) * ' || fnd_number.number_to_canonical(system_info.base_min_acc_unit) || ', /* acctd_amount */' ;
927
928
929 END IF;
930
931 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
932 ' rec_ctlgd.gl_date,
933 prev_ctlgd.code_combination_id,
934 -3,
935 prev_ctlgd.collected_tax_ccid,
936 ct.default_ussgl_transaction_code, /*Bug 2246098*/
940 FROM
937 prev_ctlgd.revenue_adjustment_id, /* Bug 2543675 */
938 prev_ctlgd.rec_offset_flag, /* Bug 2560036 */
939 ct.org_id
941 fnd_currencies foreign_fc,
942 ar_lookups al_rules,
943 ra_customer_trx ct,
944 ra_customer_trx_lines ctl,
945 ra_cust_trx_line_gl_dist ctlgd,
946 ra_cust_trx_line_gl_dist rec_ctlgd, /* cm rec dist */
947 ra_cust_trx_line_salesreps ctls,
948 ra_customer_trx prev_ct,
949 ra_customer_trx_lines prev_ctl,
950 ra_cust_trx_line_gl_dist prev_ctlgd,
951 ra_cust_trx_line_gl_dist prev_ctlgd2 /* inv rec dist */
952 WHERE ct.customer_trx_id = ctl.customer_trx_id
953 and ctl.customer_trx_line_id = ctlgd.customer_trx_line_id(+)
954 /* only look at invoices without an invoicing rule */
955 and al_rules.lookup_code = ''N''
956 /* join to the credit memo receivable account dist */
957 and ct.customer_trx_id = rec_ctlgd.customer_trx_id(+)
958 and rec_ctlgd.account_class(+) = ''REC''
959 and rec_ctlgd.latest_rec_flag(+) = ''Y''
960 /* get currency information */
961 and ct.invoice_currency_code = foreign_fc.currency_code
962 /* join to the invoice */
963 and ctl.previous_customer_trx_line_id
964 = prev_ctl.customer_trx_line_id
965 and prev_ctl.customer_trx_id = prev_ctlgd2.customer_trx_id
966 and prev_ctl.customer_trx_line_id
967 = prev_ctlgd.customer_trx_line_id
968 and prev_ctl.customer_trx_id = prev_ct.customer_trx_id
969 /* join for cust_trx_line_salesrep_id */';
970
971 -------------------------------------------------------------------
972 -- Add predicate based on input parameters
973 -------------------------------------------------------------------
974 IF( p_cm_control.customer_trx_id IS NOT NULL ) THEN
975
976 /* bug fix 956189 */
977 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
978 'and :cm_customer_trx_id_3 = ctls.customer_trx_id(+)';
979
980 g_bind_trx_3 := TRUE;
981
982 ELSE
983
984 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
985 'and :request_id_3 = ctls.request_id(+)';
986
987 g_bind_req_3 := TRUE;
988
989 END IF;
990
991 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
992 'and prev_ctlgd.cust_trx_line_salesrep_id
993 = ctls.prev_cust_trx_line_salesrep_id(+)
994 /* do not duplicate existing records */
995 and ctlgd.account_set_flag(+) = ''N''
996 and ctlgd.customer_trx_id is null
997 and ctl.previous_customer_trx_line_id is not null
998 and al_rules.lookup_type = ''YES/NO''
999 /* Use the presence of an invoicing rule to determine if the invoice has
1000 accounting rules, not the presence of an UNEARN/UNBILL distribution */
1001 and al_rules.lookup_code = DECODE(prev_ct.invoicing_rule_id,NULL,''N'',''Y'')
1002 /* Do not backout account sets for rules records.
1003 However, do use the account set record if this
1004 is a header frt only CM against an invoice with rules. */
1005 and prev_ctlgd.account_set_flag =
1006 decode(prev_ct.invoicing_rule_id, '''', ''N'', decode(al_rules.lookup_code,''N'',''N'',''Y''))
1007 /* insure that prev_ctlgd2 is the invoice rec record */
1008 and prev_ctlgd2.customer_trx_line_id+0 is null
1009 and prev_ctlgd2.account_class = ''REC''
1010 and prev_ctlgd2.account_set_flag = al_rules.lookup_code
1011 /* only reverse records in the invoice header gl date */
1012 and (prev_ctl.accounting_rule_id is null
1013 OR
1014 nvl(prev_ctlgd.gl_date,
1015 nvl(prev_ctlgd2.gl_date,
1016 to_date(''2415386'', ''J'')) ) =
1017 nvl(prev_ctlgd2.gl_date, to_date(''2415386'',
1018 ''J'')) )
1019 /* Accept all distributions for tax, freight and rec AND
1020 non revenue distributions with the same sign as the
1021 line. This includes invoices that do not use rules and
1022 unbilled or unearned account in the invoice GL date
1023 from which revenue is reclassed. */
1024 and (
1025 prev_ctl.line_type <> ''LINE''
1026 OR
1027 (prev_ctl.line_type = ''LINE'' AND
1028 prev_ctlgd.account_class = ''SUSPENSE'')
1029 OR
1030 ( prev_ctlgd.account_class NOT IN (''REV'',''UNEARN'') AND
1031 sign(prev_ctlgd.amount) =
1032 sign(prev_ctl.extended_amount))
1033 OR
1034 ( prev_ctl.accounting_rule_id is null OR
1035 al_rules.lookup_code = ''N'')
1036 )
1037 and decode(prev_ctlgd.account_class,
1038 ''SUSPENSE'', ctl.revenue_amount - ctl.extended_amount,
1039 1) <> 0 ' || CRLF || l_where_pred;
1040
1041 /* Bug 2560036/2639395 - Test for cash-based events before
1042 crediting RAM-created REV/UNEARN pairs */
1043 IF (g_test_collectibility) THEN
1044
1045 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
1046 'and (ar_revenue_management_pvt.line_collectible(prev_ctl.customer_trx_id,
1050
1047 prev_ctl.customer_trx_line_id)
1048 IN (1,2) or
1049 prev_ctlgd.revenue_adjustment_id is null)';
1051 END IF;
1052
1053 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
1054 'UNION
1055 /* Receivable account case */
1056 SELECT
1057 /* Bug 4029814 - removed gl_dist mock logic because of unique constraints */
1058 ctl.customer_trx_id,
1059 to_number(NULL), /* customer_trx_line_id */
1060 to_number(NULL), /* cust_trx_line_salesrep_id */
1061 max(ctl.request_id),
1062 max(ctl.set_of_books_id),
1063 max(ctl.last_update_date),
1064 max(ctl.last_updated_by),
1065 max(ctl.creation_date),
1066 max(ctl.created_by),
1067 max(ctl.last_update_login),
1068 max(ctl.program_application_id), /* program_appl_id */
1069 max(ctl.program_id), /* program_id */
1070 sysdate, /* program_update_date */
1071 ''REC'', /* account class */
1072 ''N'', /* account_set_flag */
1073 100, /* percent */
1074 sum(ctl.extended_amount), /* amount */
1075 sum( ' ;
1076
1077
1078 IF( p_system_info.base_min_acc_unit IS NULL ) THEN
1079
1080 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
1081 ' round(ctl.extended_amount * nvl(ct.exchange_rate, 1), ' ||
1082 p_system_info.base_precision || ')';
1083
1084 ELSE
1085
1086 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
1087 ' round(ctl.extended_amount * nvl(ct.exchange_rate, 1) / ' ||
1088 fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ') * ' ||
1089 fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) ;
1090
1091 END IF;
1092
1093 l_nonrule_insert_dist_sql := l_nonrule_insert_dist_sql || CRLF ||
1094 ' ), /* accounted amount */
1095 cm_rec.gl_date,
1096 inv_rec.code_combination_id,
1097 -3,
1098 inv_rec.collected_tax_ccid,
1099 ct.default_ussgl_transaction_code, /*Bug 2246098*/
1100 inv_rec.revenue_adjustment_id, /* Bug 2543675 */
1101 null, /* Bug 2560036 */
1102 ct.org_id
1103 FROM
1104 ra_customer_trx ct,
1105 ar_lookups al_rules,
1106 ra_cust_trx_line_gl_dist inv_rec,
1107 ra_cust_trx_line_gl_dist cm_rec,
1108 ra_cust_trx_line_gl_dist lgd,
1109 ra_customer_trx_lines ctl
1110 WHERE ct.customer_trx_id = ctl.customer_trx_id
1111 and ctl.customer_trx_id = lgd.customer_trx_id(+)
1112 and ''REC'' = lgd.account_class(+)
1113 and ''N'' = lgd.account_set_flag(+)
1114 and ctl.customer_trx_id = cm_rec.customer_trx_id
1115 and ''REC'' = cm_rec.account_class
1116 and ''Y'' = cm_rec.account_set_flag
1117 and lgd.customer_trx_id is null
1118 /* only create receivable records if the real invoice
1119 receivable record exists. */
1120 and ct.previous_customer_trx_id = inv_rec.customer_trx_id
1121 and ''REC'' = inv_rec.account_class
1122 and ''N'' = inv_rec.account_set_flag
1123 and al_rules.lookup_type = ''YES/NO''
1124 /* Use the presence of an invoicing rule to determine if the invoice has
1125 accounting rules, not the presence of an UNEARN or UNBILL distribution */
1126 and al_rules.lookup_code = DECODE(ct.invoicing_rule_id,NULL,''N'',''Y'')
1127 and al_rules.lookup_code = ''N'' ' || CRLF ||
1128 l_rec_where_pred || CRLF ||
1129 'GROUP BY
1130 ctl.customer_trx_id,
1131 inv_rec.cust_trx_line_gl_dist_id,
1132 inv_rec.gl_date,
1133 cm_rec.gl_date,
1134 inv_rec.code_combination_id,
1135 inv_rec.collected_tax_ccid,
1136 ct.default_ussgl_transaction_code, /*Bug 2246098*/
1137 inv_rec.revenue_adjustment_id, /* Bug 2543675 */
1138 ct.org_id'; /* 4156400 */
1139
1140 debug(l_nonrule_insert_dist_sql);
1141 debug(' len(nonrule_insert_dist_sql) = '||
1142 to_char(lengthb(l_nonrule_insert_dist_sql)));
1143
1144 /* 6678560 - dump booleans for binds */
1145 l_dbg_buffer := ' binds:';
1146 IF g_bind_trx_12
1147 THEN
1148 l_dbg_buffer := l_dbg_buffer || 'T~';
1149 ELSE
1150 l_dbg_buffer := l_dbg_buffer || 'F~';
1151 END IF;
1152 IF g_bind_trx_3
1153 THEN
1154 l_dbg_buffer := l_dbg_buffer || 'T~';
1155 ELSE
1156 l_dbg_buffer := l_dbg_buffer || 'F~';
1157 END IF;
1158 IF g_bind_line_14
1159 THEN
1160 l_dbg_buffer := l_dbg_buffer || 'T~';
1161 ELSE
1162 l_dbg_buffer := l_dbg_buffer || 'F~';
1163 END IF;
1164 IF g_bind_req_12
1165 THEN
1166 l_dbg_buffer := l_dbg_buffer || 'T~';
1167 ELSE
1168 l_dbg_buffer := l_dbg_buffer || 'F~';
1169 END IF;
1170 IF g_bind_req_3
1171 THEN
1172 l_dbg_buffer := l_dbg_buffer || 'T';
1173 ELSE
1174 l_dbg_buffer := l_dbg_buffer || 'F';
1175 END IF;
1176
1177 debug(l_dbg_buffer);
1178
1179 ------------------------------------------------------------------------
1180 -- If this is a Release 9 invoice with rules but no UNEARN or UNBILL
1184 ------------------------------------------------------------------------
1181 -- accounts, set the autorule complete flag to null since the
1182 -- distributions will already have been created.
1183 -- Also set the latest_rec_flag to 'N' for the account set receivable.
1185
1186 ------------------------------------------------
1187 -- Construct the update lines sql
1188 ------------------------------------------------
1189 l_nonrule_update_lines_sql :=
1190 'UPDATE ra_customer_trx_lines ctl
1191 SET autorule_complete_flag = '''',
1192 autorule_duration_processed = accounting_rule_duration
1193 WHERE ctl.accounting_rule_id is not null' || CRLF ||
1194 l_where_pred || CRLF ||
1195 'and ctl.autorule_complete_flag||'''' = ''N''
1196 and exists
1197 (
1198 SELECT ''x''
1199 FROM ra_cust_trx_line_gl_dist d
1200 WHERE d.customer_trx_id = ctl.customer_trx_id
1201 and d.account_class = ''REC''
1202 and d.account_set_flag = ''N''
1203 )';
1204
1205 --
1206 --
1207 debug(l_nonrule_update_lines_sql);
1208 debug(' len(nonrule_update_lines_sql) = '||
1209 to_char(lengthb(l_nonrule_update_lines_sql)));
1210
1211 ------------------------------------------------
1212 -- Construct the update dist sql
1213 ------------------------------------------------
1214 l_nonrule_update_dist_sql :=
1215 'UPDATE ra_cust_trx_line_gl_dist d
1216 SET latest_rec_flag = ''N''
1217 WHERE account_class = ''REC''
1218 and d.latest_rec_flag = ''Y''
1219 and d.account_set_flag = ''Y''
1220 and d.customer_trx_id in
1221 (
1222 SELECT ctl.customer_trx_id
1223 FROM ra_customer_trx_lines ctl
1224 WHERE 1 = 1' || CRLF ||
1225 l_where_pred || CRLF ||
1226 ')
1227 and exists
1228 (
1229 SELECT 1
1230 FROM ra_cust_trx_line_gl_dist d2
1231 WHERE d2.account_class = ''REC''
1232 and d2.latest_rec_flag = ''Y''
1233 and d2.customer_trx_id = d.customer_trx_id
1234 and d.rowid <> d2.rowid
1235 )';
1236
1237 --
1238 --
1239 debug(l_nonrule_update_dist_sql);
1240 debug(' len(nonrule_update_dist_sql) = '||
1241 to_char(lengthb(l_nonrule_update_dist_sql)));
1242
1243
1244 ------------------------------------------------
1245 -- Construct the update dist sql for rounding
1246 ------------------------------------------------
1247
1248 IF( system_info.base_min_acc_unit ) IS NULL THEN
1249
1250 l_amount_fragment :=
1251 'round((ctl.extended_amount * nvl(ct.exchange_rate, 1)), ' ||
1252 p_system_info.base_precision || ')';
1253
1254 ELSE
1255
1256 l_amount_fragment :=
1257 'round((ctl.extended_amount * nvl(ct.exchange_rate, 1)) / ' ||
1258 fnd_number.number_to_canonical(system_info.base_min_acc_unit) || ') * ' ||
1259 fnd_number.number_to_canonical(system_info.base_min_acc_unit);
1260
1261 END IF;
1262
1263 l_nonrule_update_dist2_sql :=
1264 'UPDATE ra_cust_trx_line_gl_dist lgd
1265 set
1266 (
1267 amount,
1268 acctd_amount
1269 ) =
1270 (
1271 SELECT
1272 (ctl.extended_amount - sum(lgd2.amount) ) + lgd.amount,' || CRLF ||
1273 ' (' || l_amount_fragment || CRLF ||
1274 ' - sum(lgd2.acctd_amount)) + lgd.acctd_amount
1275 FROM
1276 ra_customer_trx_lines ctl,
1277 ra_customer_trx ct,
1278 ra_cust_trx_line_gl_dist lgd2
1279 WHERE ctl.customer_trx_line_id = lgd2.customer_trx_line_id
1280 and ctl.customer_trx_line_id = lgd.customer_trx_line_id
1281 and ct.customer_trx_id = ctl.customer_trx_id
1282 GROUP BY
1283 ctl.customer_trx_line_id,
1284 ctl.line_number,
1285 ctl.extended_amount,
1286 ct.exchange_rate
1287 )
1288 WHERE lgd.cust_trx_line_gl_dist_id in
1289 (
1290 SELECT min(cust_trx_line_gl_dist_id)
1291 from
1292 ra_customer_trx_lines ctl,
1293 ra_customer_trx ct,
1294 ra_cust_trx_line_gl_dist lgd3
1295 where ctl.customer_trx_line_id = lgd3.customer_trx_line_id';
1296
1297 IF( p_cm_control.customer_trx_id IS NULL ) then
1298
1299 l_nonrule_update_dist2_sql := l_nonrule_update_dist2_sql || CRLF ||
1300 ' and ctl.request_id = :request_id';
1301
1302 ELSE
1303
1304 /* bug fix 956189 */
1305 l_nonrule_update_dist2_sql := l_nonrule_update_dist2_sql || CRLF ||
1306 ' and ctl.customer_trx_id = :cm_customer_trx_id';
1307
1308 END IF;
1309
1310 l_nonrule_update_dist2_sql := l_nonrule_update_dist2_sql || CRLF ||
1311 ' and ct.customer_trx_id = ctl.customer_trx_id
1312 GROUP BY
1313 ctl.customer_trx_line_id,
1314 ctl.line_number,
1315 ctl.extended_amount
1316 HAVING
1317 (
1318 sum(lgd3.amount) <> ctl.extended_amount ) or
1319 (sum(lgd3.acctd_amount) <>
1320 sum( ' || CRLF ||
1321 l_amount_fragment || CRLF ||
1322 ' )
1323 )
1324 )' ;
1325
1326 --
1327 --
1328 debug(l_nonrule_update_dist2_sql);
1329 debug(' len(nonrule_update_dist2_sql) = '||
1330 to_char(lengthb(l_nonrule_update_dist2_sql)));
1331
1332 ------------------------------------------------
1333 -- Parse sql stmts
1334 ------------------------------------------------
1335 BEGIN
1339 dbms_sql.parse( p_nonrule_insert_dist_c, l_nonrule_insert_dist_sql,
1336 debug( ' Parsing nonrule stmts', MSG_LEVEL_DEBUG );
1337
1338 p_nonrule_insert_dist_c := dbms_sql.open_cursor;
1340 dbms_sql.v7 );
1341
1342 p_nonrule_update_lines_c := dbms_sql.open_cursor;
1343 dbms_sql.parse( p_nonrule_update_lines_c, l_nonrule_update_lines_sql,
1344 dbms_sql.v7 );
1345
1346 p_nonrule_update_dist_c := dbms_sql.open_cursor;
1347 dbms_sql.parse( p_nonrule_update_dist_c, l_nonrule_update_dist_sql,
1348 dbms_sql.v7 );
1349
1350 p_nonrule_update_dist2_c := dbms_sql.open_cursor;
1351 dbms_sql.parse( p_nonrule_update_dist2_c, l_nonrule_update_dist2_sql,
1352 dbms_sql.v7 );
1353 EXCEPTION
1354 WHEN OTHERS THEN
1355 debug( 'EXCEPTION: Error parsing nonrule stmts' );
1356 RAISE;
1357 END;
1358
1359
1360 print_fcn_label( 'arp_credit_memo_module.build_nonrule_sql()-' );
1361
1362 EXCEPTION
1363 WHEN OTHERS THEN
1364 debug( 'EXCEPTION: arp_credit_memo_module.build_nonrule_sql()' );
1365 RAISE;
1366 END build_nonrule_sql;
1367
1368 ----------------------------------------------------------------------------
1369 --
1370 -- PROCEDURE NAME: build_rule_sql
1371 --
1372 -- DECSRIPTION:
1373 --
1374 -- ARGUMENTS:
1375 -- IN:
1376 -- system_info
1377 -- profile_info
1378 -- cm_control
1379 --
1380 -- IN/OUT:
1381 -- rule_select_cm_lines_c
1382 -- rule_update_cm_lines_c
1383 -- rule_insert_dist_c
1384 -- rule_insert_cma_c
1385 --
1386 -- OUT:
1387 --
1388 -- RETURNS:
1389 --
1390 -- NOTES:
1391 --
1392 -- HISTORY:
1393 --
1394 ----------------------------------------------------------------------------
1395 PROCEDURE build_rule_sql(
1396 p_system_info IN arp_trx_global.system_info_rec_type,
1397 p_profile_info IN arp_trx_global.profile_rec_type,
1398 p_cm_control IN control_rec_type,
1399 p_rule_select_cm_lines_c IN OUT NOCOPY INTEGER,
1400 p_rule_update_cm_lines_c IN OUT NOCOPY INTEGER,
1401 p_rule_insert_dist_c IN OUT NOCOPY INTEGER,
1402 p_rule_insert_cma_c IN OUT NOCOPY INTEGER ) IS
1403
1404 l_rule_select_cm_lines_sql VARCHAR2(5000);
1405 l_rule_update_cm_lines_sql VARCHAR2(2000);
1406 l_rule_insert_dist_sql VARCHAR2(32767);
1407 l_rule_insert_cma_sql VARCHAR2(2000);
1408 l_deferred_duration_sql VARCHAR2(1000);
1409 l_deferred_complete_sql VARCHAR2(1000);
1410
1411 l_where_pred VARCHAR2(1000);
1412 l_temp VARCHAR2(1000);
1413
1414
1415 BEGIN
1416
1417 print_fcn_label( 'arp_credit_memo_module.build_rule_sql()+' );
1418
1419 ------------------------------------------------
1420 -- Initialize
1421 ------------------------------------------------
1422
1423 ----------------------------------------------------
1424 -- Construct where predicate
1425 ----------------------------------------------------
1426 IF( p_cm_control.customer_trx_line_id IS NOT NULL ) THEN
1427 ----------------------------------------------------
1428 -- passed line id
1429 ----------------------------------------------------
1430
1431 /* bug fix 956189 */
1432 l_where_pred :=
1433 'and cm.customer_trx_line_id = :cm_cust_trx_line_id';
1434
1435 ELSE
1436 ----------------------------------------------------
1437 -- Did not pass line id
1438 ----------------------------------------------------
1439 IF( p_cm_control.customer_trx_id IS NOT NULL ) THEN
1440
1441 /* bug fix 956189 */
1442 l_where_pred :=
1443 'and cm.customer_trx_id = :cm_customer_trx_id';
1444
1445 ELSE
1446
1447 l_where_pred :=
1448 'and cm.request_id = :request_id';
1449
1450 END IF;
1451
1452
1453 END IF;
1454
1455
1456 ----------------------------------------------------
1457 -- Build select cm lines sql
1458 ----------------------------------------------------
1459 l_rule_select_cm_lines_sql :=
1460 'SELECT
1461 cm.customer_trx_line_id,
1462 cm.previous_customer_trx_line_id,
1463 inv_type.allow_overapplication_flag,
1464 cm.revenue_amount,
1465 decode(cmt.credit_method_for_rules,
1466 ''LIFO'', ''L'',
1467 ''PRORATE'', ''P'',
1468 ''UNIT'', ''U''),
1469 decode(cmt.credit_method_for_rules,
1470 ''UNIT'', nvl(cm.last_period_to_credit,
1471 nvl(inv.accounting_rule_duration,
1472 rule.occurrences)),
1473 0),
1474 cmt.invoice_currency_code,
1475 /* If the invoice is a Release 9 immediate invoice
1476 with rules, set the duration to -1 so that it can
1477 be processed specially. */
1478 decode(inv_trx.created_from || inv_rec.gl_date ||
1479 nvl(inv.accounting_rule_duration,
1480 rule.occurrences),
1484 ''Y'',
1481 ''RAXTRX_REL9'' || inv_rev.gl_date || ''1'', ''-1'',
1482 nvl(inv.accounting_rule_duration,
1483 rule.occurrences)),
1485 cm_rec.gl_date,
1486 decode(sign( nvl(inv.quantity_invoiced, 0)),
1487 sign(inv.extended_amount), nvl(inv.quantity_invoiced, 0),
1488 nvl(inv.quantity_invoiced, 0) * -1 ),
1489 decode(sign( nvl(cm.quantity_credited, 0)),
1490 sign(cm.extended_amount), nvl(cm.quantity_credited, 0),
1491 nvl(cm.quantity_credited, 0) * -1 ),
1492 to_char(sign(inv.revenue_amount)),
1493 to_char(sign(cm.revenue_amount)),
1494 CASE
1495 WHEN rule.type IN (''A'', ''ACC_DUR'') THEN
1496 ''N''
1497 ELSE
1498 ''Y''
1499 END partial_period_flag,
1500 inv.unit_selling_price,
1501 cm.unit_selling_price
1502 FROM
1503 ra_rules rule,
1504 ra_cust_trx_line_gl_dist cm_rec,
1505 ra_cust_trx_line_gl_dist inv_rec,
1506 ra_cust_trx_line_gl_dist inv_rev,
1507 ra_cust_trx_types inv_type,
1508 ra_customer_trx inv_trx,
1509 ra_customer_trx_lines inv,
1510 ra_customer_trx cmt,
1511 ra_customer_trx_lines cm
1512 WHERE cm.previous_customer_trx_line_id = inv.customer_trx_line_id
1513 and inv.customer_trx_id = inv_trx.customer_trx_id
1514 and inv_trx.cust_trx_type_id = inv_type.cust_trx_type_id
1515 and cm.customer_trx_id = cmt.customer_trx_id
1516 and inv.accounting_rule_id = rule.rule_id
1517 and cm.customer_trx_id = cm_rec.customer_trx_id
1518 and inv_trx.customer_trx_id = inv_rec.customer_trx_id
1519 and inv_rec.account_class = ''REC''
1520 and inv_rec.latest_rec_flag = ''Y''
1521 and cm_rec.account_class = ''REC''
1522 and cm_rec.account_set_flag = ''Y''
1523 and cm.rule_start_date is null
1524 and cm.line_type = ''LINE''
1525 and inv_rev.cust_trx_line_gl_dist_id =
1526 (
1527 SELECT nvl(min(inv_dist2.cust_trx_line_gl_dist_id),
1528 inv_rec.cust_trx_line_gl_dist_id)
1529 FROM ra_cust_trx_line_gl_dist inv_dist2
1530 WHERE inv.customer_trx_line_id = inv_dist2.customer_trx_line_id
1531 and inv_dist2.account_set_flag = ''N''
1532 and inv_dist2.account_class IN (''REV'',''UNEARN'')
1533 )
1534 /* Do not create distributions for immediate lines if they
1535 already exist. */
1536 and not exists
1537 (
1538 SELECT ''dists exist''
1539 FROM ra_cust_trx_line_gl_dist subdist
1540 WHERE subdist.customer_trx_line_id = cm.customer_trx_line_id
1541 and nvl(inv.accounting_rule_duration, 1) = 1
1542 and subdist.account_class IN (''REV'',''UNEARN'')
1543 and account_set_flag = ''N''
1544 ) ' || CRLF ||
1545 l_where_pred || CRLF ||
1546 'ORDER BY
1547 cm.previous_customer_trx_line_id,
1548 cmt.trx_date,
1549 cm_rec.gl_date,
1550 cm.customer_trx_line_id';
1551
1552 --
1553 --
1554 debug(l_rule_select_cm_lines_sql);
1555 debug(' len(rule_select_cm_lines_sql) = '||
1556 to_char(lengthb(l_rule_select_cm_lines_sql)));
1557
1558
1559 ----------------------------------------------------
1560 -- Build update cm lines sql
1561 ----------------------------------------------------
1562 /* Bug 2142941 - removed l_deferred_duration_sql and l_deferred_complete_sql */
1563 l_rule_update_cm_lines_sql :=
1564 'UPDATE ra_customer_trx_lines l
1565 SET
1566 l.rule_start_date = :rule_start_date,
1567 l.rule_end_date = :rule_end_date,
1568 l.accounting_rule_duration = :cm_acct_rule_duration,
1569 l.last_period_to_credit = decode(:credit_method,
1570 ''U'', :last_period_to_credit,
1571 l.last_period_to_credit)
1572 WHERE l.customer_trx_line_id = :customer_trx_line_id
1573 and :rule_start_date is not null
1574 and :cm_acct_rule_duration is not null ';
1575 /* Bug 2142941 - removed clauses with l_deferred_duration_sql and
1576 l_deferred_complete_sql from above code */
1577
1578 --
1579 --
1580 debug(l_rule_update_cm_lines_sql);
1581 debug(' len(rule_update_cm_lines_sql) = '||
1582 to_char(lengthb(l_rule_update_cm_lines_sql)));
1583
1584
1585 --------------------------------------------------
1586 -- Build insert stmt for ra_cust_trx_line_gl_dist
1587 --------------------------------------------------
1588 l_rule_insert_dist_sql :=
1589 'INSERT INTO ra_cust_trx_line_gl_dist
1590 (
1591 cust_trx_line_gl_dist_id,
1592 created_by,
1593 creation_date,
1594 last_updated_by,
1595 last_update_date,
1596 last_update_login,
1597 program_application_id,
1598 program_id,
1599 program_update_date,
1600 request_id,
1601 posting_control_id,
1602 customer_trx_id,
1603 customer_trx_line_id,
1604 cust_trx_line_salesrep_id,
1605 gl_date,
1606 original_gl_date,
1607 set_of_books_id,
1608 code_combination_id,
1609 concatenated_segments,
1610 account_class,
1611 account_set_flag,
1612 amount,
1613 acctd_amount,
1614 percent,
1615 ussgl_transaction_code,
1616 ussgl_transaction_code_context,
1617 comments,
1618 attribute_category,
1619 attribute1,
1620 attribute2,
1621 attribute3,
1622 attribute4,
1623 attribute5,
1624 attribute6,
1625 attribute7,
1629 attribute11,
1626 attribute8,
1627 attribute9,
1628 attribute10,
1630 attribute12,
1631 attribute13,
1632 attribute14,
1633 attribute15,
1634 collected_tax_ccid,
1635 collected_tax_concat_seg,
1636 revenue_adjustment_id, /* Bug 2543675 */
1637 org_id
1638 )';
1639
1640 --------------------------------------------------------
1641 -- Check Use invoice accounting profile
1642 --------------------------------------------------------
1643 IF( p_profile_info.use_inv_acct_for_cm_flag = YES ) THEN
1644
1645 l_temp :=
1646 '(decode(ara.amount, 0, 1, lgd.amount) /
1647 decode(ara.amount,
1648 0, decode(lgd.amount, 0, 1,lgd.amount),
1649 ara.amount) )';
1650
1651 /* Bug 2347001 - Added ORDERED hint and changed ar_revenue_assignments to
1652 ar_revenue_assignments_v (which in turn uses a global temporary
1653 table called ar_revenue_assignments_gt */
1654
1655
1656 /* Bug 2837488 - Changed ctls.cust_trx_line_salesrep_id to
1657 ctls.prev_cust_trx_line_salesrep_id */
1658
1659 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1660 '(SELECT /*+ ORDERED
1661 INDEX (ara.ragt ar_revenue_assignments_gt_n1) */
1662 ra_cust_trx_line_gl_dist_s.nextval, /* cust_trx_line_dist_id */
1663 ctl.created_by, /* created_by */
1664 sysdate, /* creation_date */
1665 ctl.last_updated_by, /* last_updated_by */
1666 sysdate, /* last_update_date */
1667 ctl.last_update_login, /* last_update_login */
1668 ctl.program_application_id, /* program_application_id */
1669 ctl.program_id, /* program_id */
1670 sysdate, /* program_update_date */
1671 ctl.request_id, /* request _id */
1672 -3,
1673 ctl.customer_trx_id, /* customer_trx_id */
1674 :customer_trx_line_id, /* customer_trx_line_id */
1675 ctls.cust_trx_line_salesrep_id, /* cust_trx_line_srep_id */
1676 /* Bug 2142941 - use lgd.gl_date and lgd.original_gl_date instead of bind
1677 variables :gl_date and :original_gl_date */
1678 /* Bug 2194742 - Used bind variable for gl_date */
1679 /* 6129294 - Honor inv gl_date for RAM dists when possible */
1680 DECODE(lgd.revenue_adjustment_id, NULL, :gl_date,
1681 DECODE(:gl_date_2, lgd.gl_date, :gl_date_3,
1682 NVL(arp_credit_memo_module.get_valid_date(
1683 lgd.gl_date,
1684 ct.invoicing_rule_id,
1685 lgd.set_of_books_id),
1686 :gl_date_4))), /* gl_date */
1687 lgd.original_gl_date, /* original_gl_date */
1688 lgd.set_of_books_id, /* set_of_books_id */
1689 lgd.code_combination_id, /* code_combination_id */
1690 lgd.concatenated_segments, /* concatenated_segments */
1691 lgd.account_class, /* account class */
1692 ''N'', /* account_set_flag */
1693 decode( fc.minimum_accountable_unit,
1694 NULL, round( (:amount * ' || l_temp || ' ),
1695 fc.precision),
1696 round( (:amount_1 * ' || l_temp || ' ) /
1697 fc.minimum_accountable_unit ) *
1698 fc.minimum_accountable_unit
1699 ) * decode(lgd.account_class,
1700 ''REV'', 1,
1701 -1), /* amount */';
1702
1703
1704 IF( p_system_info.base_min_acc_unit IS NULL ) THEN
1705
1706 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1707 'round( decode( fc.minimum_accountable_unit,
1708 null, round((:amount_2 * ' || l_temp || ' ),
1709 fc.precision),
1710 round((:amount_3 * ' || l_temp || ' ) /
1711 fc.minimum_accountable_unit)
1712 * fc.minimum_accountable_unit )
1713 * nvl(ct.exchange_rate, 1) , ' || p_system_info.base_precision ||
1714 ' )';
1715
1716 ELSE
1717
1718 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1719 '(round((decode( fc.minimum_accountable_unit,
1720 null, round((:amount_2 * ' || l_temp || ' ),
1721 fc.precision),
1722 round((:amount_3 * ' || l_temp || ' ) /
1723 fc.minimum_accountable_unit)
1724 * fc.minimum_accountable_unit )
1725 * nvl(ct.exchange_rate, 1)
1726 ) / ' || fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ' ) *' || CRLF ||
1727 fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ')';
1728
1729 END IF;
1730
1731 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1732 ' * decode(lgd.account_class,
1733 ''REV'', 1,
1734 -1), /* acctd_amount */
1735 decode(lgd.account_class,
1736 ''UNBILL'', -1 * round( ( (:amount_4 * ' || l_temp || ' )
1737 / decode(ctl.revenue_amount,
1738 0, 1, ctl.revenue_amount)
1739 ) * 100, 4),
1740 ''UNEARN'', -1 * round( ( (:amount_5 * ' || l_temp || ' )
1744 round( ( (:amount_6 * ' || l_temp || ' )
1741 / decode(ctl.revenue_amount,
1742 0, 1, ctl.revenue_amount)
1743 ) * 100, 4),
1745 / decode(ctl.revenue_amount,
1746 0, 1, ctl.revenue_amount)
1747 ) * 100, 4)
1748 ), /* percent */
1749 ct.default_ussgl_transaction_code, /* ussgl_trx_code */
1750 ct.default_ussgl_trx_code_context, /* ussgl_trx_code_context */
1751 NULL, /* comments */
1752 NULL, /* attribute_category */
1753 NULL, /* attribute1 */
1754 NULL, /* attribute2 */
1755 NULL, /* attribute3 */
1756 NULL, /* attribute4 */
1757 NULL, /* attribute5 */
1758 NULL, /* attribute6 */
1759 NULL, /* attribute7 */
1760 NULL, /* attribute8 */
1761 NULL, /* attribute9 */
1762 NULL, /* attribute10 */
1763 NULL, /* attribute11 */
1764 NULL, /* attribute12 */
1765 NULL, /* attribute13 */
1766 NULL, /* attribute14 */
1767 NULL, /* attribute15 */
1768 lgd.collected_tax_ccid, /* collected tax */
1769 lgd.collected_tax_concat_seg, /* collected tax seg */
1770 lgd.revenue_adjustment_id, /* revenue_adjustment_id */ /*Bug 2543675*/
1771 ct.org_id
1772 FROM
1773 ra_customer_trx_lines ctl,
1774 ra_customer_trx ct,
1775 fnd_currencies fc,
1776 ar_revenue_assignments_v ara /* Bug 2347001 */,
1777 ra_cust_trx_line_gl_dist inv_rec,
1778 ra_cust_trx_line_gl_dist lgd,
1779 ra_cust_trx_line_salesreps ctls
1780 WHERE lgd.customer_trx_line_id = ctl.previous_customer_trx_line_id
1781 and ctl.previous_customer_trx_line_id = ara.customer_trx_line_id
1782 and ara.session_id in (:session_id, -99) /**Bug 2347001 */
1783 and ara.gl_date = nvl(lgd.original_gl_date, lgd.gl_date)
1784 and ara.account_class = lgd.account_class
1785 and ara.period_set_name = :period_set_name /* 4254587 */
1786 and ct.customer_trx_id = ctl.customer_trx_id
1787 and inv_rec.customer_trx_id = ctl.previous_customer_trx_id
1788 and inv_rec.account_class = ''REC''
1789 and inv_rec.latest_rec_flag = ''Y''
1790 /* Bug 2899714 */
1791 and lgd.cust_trx_line_salesrep_id = ctls.prev_cust_trx_line_salesrep_id(+)
1792 /*7147479*/
1793 and ctls.customer_trx_line_id(+) = :customer_trx_line_id_1
1794 and ctl.customer_trx_line_id = :customer_trx_line_id_2
1795 and fc.currency_code = ct.invoice_currency_code
1796 and lgd.account_set_flag = ''N''
1797 and ( (lgd.account_class in (''REV'', ''UNEARN'', ''UNBILL'') and
1798 :insert_offset_1 = ''Y'' ) or
1799 (lgd.account_class = ''REV'' and :insert_offset_2 = ''N'' ) )
1800 /* inv_dist_exists is set to "F" when crediting a release 9
1801 immediate invoice. In this case, the cm gl_date may not correspond
1802 to any inv gl_date, so the date check cannot be done. */
1803 /* Bug 2142941 - include join onto lgd.original_gl_date */
1804 and (
1805 ( trunc(ara.gl_date) = lgd.original_gl_date AND
1806 lgd.original_gl_date = :original_gl_date_1)
1807 OR
1808 :check_gl_date_1 = ''N''
1809 )
1810 /* Bug 2535023 - Revamped fixes from bugs 1936152 and 2354805
1811 so that the insert now relies upon rec_offset_flag instead
1812 of that and-not stuff. Forced routine to only
1813 copy conventional distributions. */
1814 /* Bug 2543675 - include RAM distributions */
1815 and lgd.rec_offset_flag is null';
1816
1817 /* Bug 2560036/2639395 - Test for cash-based events before
1818 crediting RAM-created REV/UNEARN pairs */
1819 /* 6060283 - test for collectibility rather than the more limited
1820 cash-based condition */
1821 IF (g_test_collectibility) THEN
1822
1823 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1824 'and (ar_revenue_management_pvt.line_collectible(ctl.previous_customer_trx_id,
1825 ctl.previous_customer_trx_line_id)
1826 IN (1,2) or
1827 lgd.revenue_adjustment_id is null)';
1828
1829 END IF;
1830
1831 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF || ')';
1832
1833
1834 ELSE
1835
1836 --------------------------------------------------------
1837 -- Don't use invoice accounting
1838 --------------------------------------------------------
1839
1840 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1841 ' (SELECT
1842 ra_cust_trx_line_gl_dist_s.nextval, /* cust_trx_line_dist_id */
1843 ct.created_by, /* created_by */
1844 sysdate, /* creation_date */
1845 ct.last_updated_by, /* last_updated_by */
1846 sysdate, /* last_update_date */
1850 sysdate, /* program_update_date */
1847 ct.last_update_login, /* last_update_login */
1848 ct.program_application_id, /* program_application_id */
1849 ct.program_id, /* program_id */
1851 ct.request_id, /* request _id */
1852 -3,
1853 ct.customer_trx_id, /* customer_trx_id */
1854 :customer_trx_line_id, /* customer_trx_line_id */
1855 lgd.cust_trx_line_salesrep_id, /* cust_trx_line_srep_id */
1856 :gl_date, /* gl_date */
1857 :original_gl_date, /* original_gl_date */
1858 lgd.set_of_books_id, /* set_of_books_id */
1859 lgd.code_combination_id, /* code_combination_id */
1860 lgd.concatenated_segments, /* concatenated_segments */
1861 lgd.account_class, /* account class */
1862 ''N'', /* account_set_flag */
1863 decode( fc.minimum_accountable_unit,
1864 NULL, round( (:amount * (lgd.percent / 100) ),
1865 fc.precision),
1866 round( (:amount_1 * (lgd.percent / 100) ) /
1867 fc.minimum_accountable_unit ) *
1868 fc.minimum_accountable_unit
1869 ) * decode(lgd.account_class,
1870 ''REV'', 1,
1871 -1), /* amount */ ';
1872
1873
1874 IF( p_system_info.base_min_acc_unit IS NULL ) THEN
1875
1876 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1877 'round( decode( fc.minimum_accountable_unit,
1878 null, round((:amount_2 * (lgd.percent / 100) ),
1879 fc.precision),
1880 round((:amount_3 * (lgd.percent / 100) ) /
1881 fc.minimum_accountable_unit)
1882 * fc.minimum_accountable_unit )
1883 * nvl(ct.exchange_rate, 1), ' || p_system_info.base_precision ||
1884 ' )';
1885
1886 ELSE
1887
1888 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1889 '(round((decode( fc.minimum_accountable_unit,
1890 null, round((:amount_2 * (lgd.percent / 100) ),
1891 fc.precision),
1892 round((:amount_3 * (lgd.percent / 100) ) /
1893 fc.minimum_accountable_unit)
1894 * fc.minimum_accountable_unit )
1895 * nvl(ct.exchange_rate, 1)
1896 ) / ' || fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ' ) *' || CRLF ||
1897 fnd_number.number_to_canonical(p_system_info.base_min_acc_unit) || ')';
1898
1899 END IF;
1900
1901 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1902 ' * decode(lgd.account_class,
1903 ''REV'', 1,
1904 -1), /* acctd_amount */
1905 decode(lgd.account_class,
1906 ''UNBILL'', -1 * round( ( (:amount_4 * (lgd.percent / 100) )
1907 / decode(ctl.revenue_amount,
1908 0, 1, ctl.revenue_amount)
1909 ) * 100, 4),
1910 ''UNEARN'', -1 * round( ( (:amount_5 * (lgd.percent / 100) )
1911 / decode(ctl.revenue_amount,
1912 0, 1, ctl.revenue_amount)
1913 ) * 100, 4),
1914 round( ( (:amount_6 * (lgd.percent / 100) )
1915 / decode(ctl.revenue_amount,
1916 0, 1, ctl.revenue_amount)
1917 ) * 100, 4)
1918 ), /* percent */
1919 ct.default_ussgl_transaction_code, /* ussgl_trx_code */
1920 ct.default_ussgl_trx_code_context, /* ussgl_trx_code_context */
1921 lgd.comments, /* comments */
1922 lgd.attribute_category, /* attribute_category */
1923 lgd.attribute1, /* attribute1 */
1924 lgd.attribute2, /* attribute2 */
1925 lgd.attribute3, /* attribute3 */
1926 lgd.attribute4, /* attribute4 */
1927 lgd.attribute5, /* attribute5 */
1928 lgd.attribute6, /* attribute6 */
1929 lgd.attribute7, /* attribute7 */
1930 lgd.attribute8, /* attribute8 */
1931 lgd.attribute9, /* attribute9 */
1932 lgd.attribute10, /* attribute10 */
1933 lgd.attribute11, /* attribute11 */
1934 lgd.attribute12, /* attribute12 */
1935 lgd.attribute13, /* attribute13 */
1936 lgd.attribute14, /* attribute14 */
1937 lgd.attribute15, /* attribute1 */
1938 lgd.collected_tax_ccid, /* collected tax */
1939 lgd.collected_tax_concat_seg, /* collected tax seg */
1940 lgd.revenue_adjustment_id, /* revenue_adjustment_id */ /*Bug 2543675*/
1941 ct.org_id
1942 FROM
1943 ra_cust_trx_line_gl_dist lgd,
1944 fnd_currencies fc,
1945 ra_customer_trx_lines ctl,
1946 ra_customer_trx ct,
1947 ra_customer_trx_lines ictl,
1948 ra_rules ir
1949 WHERE lgd.customer_trx_id = ct.customer_trx_id
1950 and lgd.customer_trx_line_id = :customer_trx_line_id_1
1951 and lgd.customer_trx_line_id = ctl.customer_trx_line_id
1952 and fc.currency_code = ct.invoice_currency_code
1953 and account_set_flag = ''Y''
1957 /* Bug 2559653 - generate nothing for deferred rules
1954 and ( (lgd.account_class in (''REV'', ''UNEARN'', ''UNBILL'') and
1955 :insert_offset_1 = ''Y'' ) or
1956 (lgd.account_class = ''REV'' and :insert_offset_2 = ''N'' ) )
1958 unless it is an ARREARS transaction */
1959 and ctl.previous_customer_trx_line_id = ictl.customer_trx_line_id
1960 and ictl.accounting_rule_id = ir.rule_id
1961 and (nvl(ir.deferred_revenue_flag, ''N'') = ''N'' or
1962 ct.invoicing_rule_id = -3)
1963 /* no-effect pred, for binding purposes */
1964 and :check_gl_date_1 = :check_gl_date_2
1965 and :gl_date_1 is not null ';
1966
1967
1968 /* 4708369 - Do not create REV/UNEARN pairs if
1969 transaction is not collectible */
1970 /* 6060283 - test for the more general collectibility rather than
1971 the more narrow 'cash-based' condition */
1972 IF (g_test_collectibility) THEN
1973
1974 l_rule_insert_dist_sql := l_rule_insert_dist_sql || CRLF ||
1975 'and ar_revenue_management_pvt.line_collectible(ctl.previous_customer_trx_id,
1976 ctl.previous_customer_trx_line_id) IN (1,2) ';
1977
1978 END IF;
1979
1980 l_rule_insert_dist_sql := l_rule_insert_dist_sql || ')';
1981
1982 END IF;
1983
1984 /*--------------------------------------------------------+
1985 | added on variables for bulk collect for mrc processing |
1986 +--------------------------------------------------------*/
1987
1988 debug( l_rule_insert_dist_sql);
1989 debug(' len(rule_insert_dist_sql) = '||
1990 to_char(lengthb(l_rule_insert_dist_sql)));
1991
1992 ----------------------------------------------------
1993 -- Build insert stmt for ar_credit_memo_amounts
1994 ----------------------------------------------------
1995 l_rule_insert_cma_sql :=
1996 'INSERT INTO ar_credit_memo_amounts
1997 (
1998 credit_memo_amount_id,
1999 last_updated_by,
2000 last_update_date,
2001 last_update_login,
2002 created_by,
2003 creation_date,
2004 customer_trx_line_id,
2005 gl_date,
2006 amount,
2007 program_application_id,
2008 program_id,
2009 program_update_date,
2010 request_id
2011 )
2012 SELECT
2013 ar_credit_memo_amounts_s.nextval, /* credit_memo_amount_id */' || CRLF ||
2014 p_profile_info.user_id || ', /* last_updated_by */
2015 sysdate, /* last_update_date */' || CRLF ||
2016 p_profile_info.conc_login_id || ', /* last_update_login */' || CRLF ||
2017 p_profile_info.user_id || ', /* created_by */
2018 sysdate, /* creation_date */
2019 :customer_trx_line_id, /* customer_trx_line_id */
2020 :gl_date, /* gl_date */
2021 :amount, /* amount */' || CRLF ||
2022 profile_info.application_id || ', /* program_application_id */'
2023 || CRLF || profile_info.conc_program_id || ', /* program_id */
2024 sysdate, /* program_update_date */';
2025
2026 IF( p_cm_control.request_id IS NULL ) THEN
2027 l_rule_insert_cma_sql := l_rule_insert_cma_sql || CRLF ||
2028 '0' || ' /* request_id */';
2029 ELSE
2030 l_rule_insert_cma_sql := l_rule_insert_cma_sql || CRLF ||
2031 ':request_id /* request_id */';
2032 END IF;
2033
2034 l_rule_insert_cma_sql := l_rule_insert_cma_sql || CRLF ||
2035 'FROM dual ';
2036
2037
2038 --
2039 --
2040 debug(l_rule_insert_cma_sql);
2041 debug(' len(rule_insert_cma_sql) = '||
2042 to_char(lengthb(l_rule_insert_cma_sql)));
2043
2044 ------------------------------------------------
2045 -- Parse sql stmts
2046 ------------------------------------------------
2047 BEGIN
2048 debug( ' Parsing rule stmts', MSG_LEVEL_DEBUG );
2049
2050 p_rule_select_cm_lines_c := dbms_sql.open_cursor;
2051 dbms_sql.parse( p_rule_select_cm_lines_c, l_rule_select_cm_lines_sql,
2052 dbms_sql.v7 );
2053
2054 debug(' parsed p_rule_select_cm_lines_c');
2055
2056 p_rule_update_cm_lines_c := dbms_sql.open_cursor;
2057 dbms_sql.parse( p_rule_update_cm_lines_c, l_rule_update_cm_lines_sql,
2058 dbms_sql.v7 );
2059
2060 debug(' parsed p_rule_update_cm_lines_c');
2061
2062 p_rule_insert_dist_c := dbms_sql.open_cursor;
2063 dbms_sql.parse( p_rule_insert_dist_c, l_rule_insert_dist_sql,
2064 dbms_sql.v7 );
2065
2066 debug(' parsed p_rule_insert_dist_c');
2067
2068 p_rule_insert_cma_c := dbms_sql.open_cursor;
2069 dbms_sql.parse( p_rule_insert_cma_c, l_rule_insert_cma_sql,
2070 dbms_sql.v7 );
2071
2072 debug(' parsed p_rule_insert_cma_c');
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 debug( 'EXCEPTION: Error parsing rule stmts' );
2076 RAISE;
2077 END;
2078
2079
2080 print_fcn_label( 'arp_credit_memo_module.build_rule_sql()-' );
2081
2082 EXCEPTION
2083 WHEN OTHERS THEN
2084 debug( 'EXCEPTION: arp_credit_memo_module.build_rule_sql()' );
2088 ----------------------------------------------------------------------------
2085 RAISE;
2086 END build_rule_sql;
2087
2089 --
2090 -- PROCEDURE NAME: build_net_revenue_sql
2091 --
2092 -- DECSRIPTION:
2093 --
2094 -- ARGUMENTS:
2095 -- IN:
2096 -- system_info
2097 -- profile_info
2098 -- cm_control
2099 --
2100 -- IN/OUT:
2101 -- net_revenue_line_c
2102 --
2103 -- OUT:
2104 --
2105 -- RETURNS:
2106 --
2107 -- NOTES:
2108 --
2109 -- HISTORY:
2110 --
2111 ----------------------------------------------------------------------------
2112 PROCEDURE build_net_revenue_sql(
2113 p_system_info IN arp_trx_global.system_info_rec_type,
2114 p_profile_info IN arp_trx_global.profile_rec_type,
2115 p_cm_control IN control_rec_type,
2116 p_net_revenue_line_c IN OUT NOCOPY INTEGER ) IS
2117
2118 l_net_revenue_line_sql VARCHAR2(2000);
2119
2120 BEGIN
2121
2122 print_fcn_label( 'arp_credit_memo_module.build_net_revenue_sql()+' );
2123
2124 ------------------------------------------------
2125 -- Construct SELECT Statement
2126 ------------------------------------------------
2127 l_net_revenue_line_sql :=
2128 'SELECT
2129 distinct
2130 cnr.previous_customer_trx_line_id,
2131 cnr.gl_date,
2132 cnr.amount,
2133 cnr.net_unit_price,
2134 decode(trx.created_from,
2135 ''RAXTRX_REL9'', ''Y'',
2136 decode(lgd.customer_trx_id,
2137 NULL, ''N'',
2138 ''Y'')
2139 ) /* inv dist exists */
2140 FROM ra_cust_trx_line_gl_dist lgd,
2141 ra_customer_trx trx,
2142 ar_cm_net_revenue_form cnr
2143 WHERE cnr.previous_customer_trx_line_id = :start_prev_ctlid
2144 and cnr.previous_customer_trx_line_id = lgd.customer_trx_line_id(+)
2145 and cnr.previous_customer_trx_id = trx.customer_trx_id
2146 and cnr.gl_date = lgd.original_gl_date(+)
2147 and ''N'' = lgd.account_set_flag(+)
2148 and cnr.period_set_name = :period_set_name
2149 ORDER BY
2150 cnr.previous_customer_trx_line_id,
2151 cnr.gl_date';
2152
2153 debug(l_net_revenue_line_sql);
2154 debug(' len(net_revenue_line_sql) = '||
2155 to_char(lengthb(l_net_revenue_line_sql)));
2156
2157
2158 ------------------------------------------------
2159 -- Parse sql stmt
2160 ------------------------------------------------
2161 BEGIN
2162 debug( ' Parsing net revenue stmts', MSG_LEVEL_DEBUG );
2163
2164 p_net_revenue_line_c := dbms_sql.open_cursor;
2165 dbms_sql.parse( p_net_revenue_line_c, l_net_revenue_line_sql,
2166 dbms_sql.v7 );
2167
2168 EXCEPTION
2169 WHEN OTHERS THEN
2170 debug( 'EXCEPTION: Error parsing net revenue stmts' );
2171 RAISE;
2172 END;
2173
2174
2175 print_fcn_label( 'arp_credit_memo_module.build_net_revenue_sql()-' );
2176
2177 EXCEPTION
2178 WHEN OTHERS THEN
2179 debug( 'EXCEPTION: arp_credit_memo_module.build_net_revenue_sql()' );
2180 RAISE;
2181 END build_net_revenue_sql;
2182
2183
2184 ----------------------------------------------------------------------------
2185 --
2186 -- PROCEDURE NAME: load_net_revenue_schedule
2187 --
2188 -- DECSRIPTION:
2189 --
2190 -- ARGUMENTS:
2191 -- IN:
2192 -- system_info
2193 -- profile_info
2194 -- cm_control
2195 -- prev_cust_trx_line_id
2196 --
2197 -- IN/OUT:
2198 --
2199 -- OUT:
2200 --
2201 -- RETURNS:
2202 --
2203 -- NOTES:
2204 --
2205 -- HISTORY:
2206 -- 16-SEP-05 M Raymond 4602892 - Removed fix for bug 642590 as we needed
2207 -- to preserve net rev arrays.
2208 ----------------------------------------------------------------------------
2209 PROCEDURE load_net_revenue_schedule(
2210 p_system_info IN arp_trx_global.system_info_rec_type,
2211 p_profile_info IN arp_trx_global.profile_rec_type,
2212 p_cm_control IN control_rec_type,
2213 p_prev_cust_trx_line_id IN BINARY_INTEGER ) IS
2214
2215
2216 l_ignore INTEGER;
2217
2218
2219 BEGIN
2220
2221 print_fcn_label('arp_credit_memo_module.load_net_revenue_schedule()+' );
2222
2223 /* bugfix : 642590 -- removed from here */
2224
2225 --
2226 -- If net revenue for a line already exists, then no need to reload
2227 --
2228 BEGIN
2229
2230 IF( net_rev_ctlid_t( 0 ) = p_prev_cust_trx_line_id ) THEN
2231 print_fcn_label('arp_credit_memo_module.load_net_revenue_schedule()-' );
2232 RETURN;
2233 END IF;
2234
2235 EXCEPTION
2236 WHEN NO_DATA_FOUND THEN
2237 null;
2238 END;
2239
2240 ---------------------------------------------------------------
2241 -- Initialize tables
2242 ---------------------------------------------------------------
2243
2244 net_rev_ctlid_t := null_net_rev_ctlid;
2245 net_rev_gl_date_t := null_net_rev_gl_date;
2249
2246 net_rev_amount_t := null_net_rev_amount;
2247 net_rev_unit_t := null_net_rev_unit;
2248 net_rev_dist_exists_t := null_net_rev_dist_exists;
2250 net_rev_start_index := 0;
2251 net_rev_index := 0;
2252
2253 ---------------------------------------------------------------
2254 -- Bind variables
2255 ---------------------------------------------------------------
2256 BEGIN
2257 dbms_sql.bind_variable( net_revenue_line_c,
2258 'start_prev_ctlid',
2259 p_prev_cust_trx_line_id );
2260 dbms_sql.bind_variable( net_revenue_line_c,
2261 'period_set_name',
2262 system_info.period_set_name );
2263 EXCEPTION
2264 WHEN OTHERS THEN
2265 debug( 'EXCEPTION: Error in binding net_revenue_line_c' );
2266 RAISE;
2267 END;
2268
2269 ---------------------------------------------------------------
2270 -- Execute sql
2271 ---------------------------------------------------------------
2272 debug( ' Executing net revenue sql', MSG_LEVEL_DEBUG );
2273
2274 BEGIN
2275 l_ignore := dbms_sql.execute( net_revenue_line_c );
2276
2277 EXCEPTION
2278 WHEN OTHERS THEN
2279 debug( 'EXCEPTION: Error executing net revenue sql' );
2280 RAISE;
2281 END;
2282
2283
2284 ---------------------------------------------------------------
2285 -- Fetch rows
2286 ---------------------------------------------------------------
2287 BEGIN
2288 LOOP
2289
2290 IF dbms_sql.fetch_rows( net_revenue_line_c ) > 0 THEN
2291
2292 debug(' Fetched a row', MSG_LEVEL_DEBUG );
2293
2294 debug(' Load row into tables', MSG_LEVEL_DEBUG );
2295
2296 -----------------------------------------------
2297 -- Load row into table
2298 -----------------------------------------------
2299 dbms_sql.column_value( net_revenue_line_c, 1,
2300 net_rev_ctlid_t( net_rev_index ) );
2301 dbms_sql.column_value( net_revenue_line_c, 2,
2302 net_rev_gl_date_t( net_rev_index ) );
2303 dbms_sql.column_value( net_revenue_line_c, 3,
2304 net_rev_amount_t( net_rev_index ) );
2305 dbms_sql.column_value( net_revenue_line_c, 4,
2306 net_rev_unit_t( net_rev_index ) );
2307 dbms_sql.column_value( net_revenue_line_c, 5,
2308 net_rev_dist_exists_t( net_rev_index ));
2309
2310 net_rev_index := net_rev_index + 1;
2311
2312 ELSE
2313 EXIT;
2314 END IF;
2315
2316
2317 END LOOP;
2318
2319 EXCEPTION
2320 WHEN OTHERS THEN
2321 debug( 'EXCEPTION: Error fetching net revenue cursor' );
2322 RAISE;
2323
2324 END;
2325
2326 ---------------------------------------------------------------
2327 -- Dump table
2328 ---------------------------------------------------------------
2329 IF PG_DEBUG in ('Y', 'C') THEN
2330 debug('Net Revenue schedule ***');
2331 FOR i IN net_rev_start_index..net_rev_index - 1 LOOP
2332 debug('['|| i || ']: Ctlid <' ||
2333 net_rev_ctlid_t(i) || '> GL Date <' ||
2334 net_rev_gl_date_t(i) || '> Rev Amt <' ||
2335 net_rev_amount_t(i) || '> Rev unit < ' ||
2336 net_rev_unit_t(i) || '> Rev dist exists <' ||
2337 net_rev_dist_exists_t(i) || '>', MSG_LEVEL_DEBUG );
2338 END LOOP;
2339 END IF;
2340
2341 print_fcn_label('arp_credit_memo_module.load_net_revenue_schedule()-' );
2342
2343 EXCEPTION
2344 WHEN OTHERS THEN
2345 debug('EXCEPTION: arp_credit_memo_module.load_net_revenue_schedule('
2346 || to_char(p_prev_cust_trx_line_id) || ')' );
2347 RAISE;
2348
2349 END load_net_revenue_schedule;
2350
2351 ----------------------------------------------------------------------------
2352 --
2353 -- PROCEDURE NAME: credit_nonrule_transactions
2354 --
2355 -- DECSRIPTION:
2356 --
2357 -- ARGUMENTS:
2358 -- IN:
2359 -- system_info
2360 -- profile_info
2361 -- cm_control
2362 --
2363 -- IN/OUT:
2364 --
2365 -- OUT:
2366 --
2367 -- RETURNS:
2368 --
2369 -- NOTES:
2370 --
2371 -- HISTORY:
2372 --
2373 ----------------------------------------------------------------------------
2374 PROCEDURE credit_nonrule_transactions(
2375 p_system_info IN arp_trx_global.system_info_rec_type,
2376 p_profile_info IN arp_trx_global.profile_rec_type,
2377 p_cm_control IN control_rec_type ) IS
2378
2379 l_ignore INTEGER;
2380
2381 CURSOR mrc_gl_dist(p_trx_id NUMBER, p_trx_line_id NUMBER) IS
2382 SELECT cust_trx_line_gl_dist_id
2383 FROM ra_cust_trx_line_gl_dist gld
2384 WHERE gld.customer_trx_id = p_trx_id
2385 AND gld.customer_trx_line_id =
2389
2386 nvl(p_trx_line_id, gld.customer_trx_line_id);
2387
2388 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2390 BEGIN
2391
2392 print_fcn_label('arp_credit_memo_module.credit_nonrule_transactions()+' );
2393
2394 -----------------------------------------------------------------------
2395 -- Create dynamic sql
2396 -----------------------------------------------------------------------
2397 debug( ' Creating dynamic sql', MSG_LEVEL_DEBUG );
2398
2399 build_nonrule_sql( p_system_info,
2400 p_profile_info,
2401 p_cm_control,
2402 nonrule_insert_dist_c,
2403 nonrule_update_lines_c,
2404 nonrule_update_dist_c,
2405 nonrule_update_dist2_c );
2406 ---------------------------------------------------------------
2407 -- Bind variables
2408 ---------------------------------------------------------------
2409 /* bug fix 956189 */
2410 IF( p_cm_control.customer_trx_id IS NOT NULL ) THEN
2411 BEGIN
2412 IF g_bind_trx_12
2413 THEN
2414 /* INSERT */
2415 dbms_sql.bind_variable( nonrule_insert_dist_c,
2416 'cm_customer_trx_id_1',
2417 p_cm_control.customer_trx_id );
2418
2419 dbms_sql.bind_variable( nonrule_insert_dist_c,
2420 'cm_customer_trx_id_2',
2421 p_cm_control.customer_trx_id );
2422 /* UPDATE LINE */
2423 dbms_sql.bind_variable( nonrule_update_lines_c,
2424 'cm_customer_trx_id_1',
2425 p_cm_control.customer_trx_id );
2426
2427 /* UPDATE DIST */
2428 dbms_sql.bind_variable( nonrule_update_dist_c,
2429 'cm_customer_trx_id_1',
2430 p_cm_control.customer_trx_id );
2431
2432 END IF;
2433 IF g_bind_trx_3
2434 THEN
2435 dbms_sql.bind_variable( nonrule_insert_dist_c,
2436 'cm_customer_trx_id_3',
2437 p_cm_control.customer_trx_id );
2438
2439 END IF;
2440 EXCEPTION
2441 WHEN OTHERS THEN
2442 debug( 'EXCEPTION: Error in binding nonrule_insert_dist_c' );
2443 RAISE;
2444 END;
2445
2446 BEGIN
2447 dbms_sql.bind_variable( nonrule_update_dist2_c,
2448 'cm_customer_trx_id',
2449 p_cm_control.customer_trx_id );
2450 EXCEPTION
2451 WHEN OTHERS THEN
2452 debug( 'EXCEPTION: Error in binding nonrule_update_dist2_c' );
2453 RAISE;
2454 END;
2455 ELSE /* bug 3525326 */
2456 BEGIN
2457 IF g_bind_req_12
2458 THEN
2459 /* INSERT */
2460 dbms_sql.bind_variable( nonrule_insert_dist_c,
2461 'request_id_1',
2462 p_cm_control.request_id );
2463 dbms_sql.bind_variable( nonrule_insert_dist_c,
2464 'request_id_2',
2465 p_cm_control.request_id );
2466 /* UPDATE LINES */
2467 dbms_sql.bind_variable( nonrule_update_lines_c,
2468 'request_id_1',
2469 p_cm_control.request_id );
2470 /* UPDATE DISTS */
2471 dbms_sql.bind_variable( nonrule_update_dist_c,
2472 'request_id_1',
2473 p_cm_control.request_id );
2474
2475 END IF;
2476
2477 IF g_bind_req_3
2478 THEN
2479 dbms_sql.bind_variable( nonrule_insert_dist_c,
2480 'request_id_3',
2481 p_cm_control.request_id );
2482 END IF;
2483
2484 EXCEPTION
2485 WHEN OTHERS THEN
2486 debug( 'EXCEPTION: Error in binding nonrule_insert_dist_c' );
2487 RAISE;
2488 END;
2489
2490 BEGIN
2491 dbms_sql.bind_variable( nonrule_update_dist2_c,
2492 'request_id',
2493 p_cm_control.request_id );
2494 EXCEPTION
2495 WHEN OTHERS THEN
2496 debug( 'EXCEPTION: Error in binding nonrule_update_dist2_c' );
2497 RAISE;
2498 END;
2499 END IF;
2500
2501 /* bug fix 956189 */
2502 IF( p_cm_control.customer_trx_line_id IS NOT NULL ) THEN
2503 BEGIN
2504 IF g_bind_line_14
2505 THEN
2506 /* INSERT */
2507 dbms_sql.bind_variable( nonrule_insert_dist_c,
2508 'cm_cust_trx_line_id_1',
2509 p_cm_control.customer_trx_line_id );
2510 dbms_sql.bind_variable( nonrule_insert_dist_c,
2511 'cm_cust_trx_line_id_2',
2512 p_cm_control.customer_trx_line_id );
2513 dbms_sql.bind_variable( nonrule_insert_dist_c,
2517 'cm_cust_trx_line_id_4',
2514 'cm_cust_trx_line_id_3',
2515 p_cm_control.customer_trx_line_id );
2516 dbms_sql.bind_variable( nonrule_insert_dist_c,
2518 p_cm_control.customer_trx_line_id );
2519 /* UPDATE LINES */
2520 dbms_sql.bind_variable( nonrule_update_lines_c,
2521 'cm_cust_trx_line_id_1',
2522 p_cm_control.customer_trx_line_id );
2523 dbms_sql.bind_variable( nonrule_update_lines_c,
2524 'cm_cust_trx_line_id_2',
2525 p_cm_control.customer_trx_line_id );
2526 /* UPDATE DISTS */
2527 dbms_sql.bind_variable( nonrule_update_dist_c,
2528 'cm_cust_trx_line_id_1',
2529 p_cm_control.customer_trx_line_id );
2530 dbms_sql.bind_variable( nonrule_update_dist_c,
2531 'cm_cust_trx_line_id_2',
2532 p_cm_control.customer_trx_line_id );
2533 END IF;
2534 EXCEPTION
2535 WHEN OTHERS THEN
2536 debug( 'EXCEPTION: Error in binding nonrule_insert_dist_c' );
2537 RAISE;
2538 END;
2539
2540 END IF;
2541
2542 -----------------------------------------------------------------------
2543 -- Insert dist
2544 -----------------------------------------------------------------------
2545 debug( ' Inserting distributions', MSG_LEVEL_DEBUG );
2546
2547 BEGIN
2548 l_ignore := dbms_sql.execute( nonrule_insert_dist_c );
2549 close_cursor( nonrule_insert_dist_c );
2550
2551 debug( to_char(l_ignore) || ' row(s) inserted', MSG_LEVEL_DEBUG );
2552
2553 /* Bug 4029814 - MRC call at end of this procedure */
2554
2555 EXCEPTION
2556 WHEN OTHERS THEN
2557 debug( 'EXCEPTION: Error executing insert dist stmt' );
2558 RAISE;
2559 END;
2560
2561 -----------------------------------------------------------------------
2562 -- Update lines
2563 -----------------------------------------------------------------------
2564 debug( ' Updating lines', MSG_LEVEL_DEBUG );
2565
2566 BEGIN
2567 l_ignore := dbms_sql.execute( nonrule_update_lines_c );
2568 close_cursor( nonrule_update_lines_c );
2569 debug( to_char(l_ignore) || ' row(s) updated', MSG_LEVEL_DEBUG );
2570
2571 EXCEPTION
2572 WHEN OTHERS THEN
2573 debug( 'EXCEPTION: Error executing update lines stmt' );
2574 RAISE;
2575 END;
2576
2577 -----------------------------------------------------------------------
2578 -- Update dist
2579 -----------------------------------------------------------------------
2580 debug( ' Updating distributions', MSG_LEVEL_DEBUG );
2581
2582 BEGIN
2583 l_ignore := dbms_sql.execute( nonrule_update_dist_c );
2584 close_cursor( nonrule_update_dist_c );
2585
2586 debug( to_char(l_ignore) || ' row(s) updated', MSG_LEVEL_DEBUG );
2587
2588 /* Bug 4029814 - MRC call at end of this procedure */
2589
2590 EXCEPTION
2591 WHEN OTHERS THEN
2592 debug( 'EXCEPTION: Error executing update dist stmt' );
2593 RAISE;
2594 END;
2595
2596 -----------------------------------------------------------------------
2597 -- Update dist for rounding
2598 -----------------------------------------------------------------------
2599 debug( ' Updating distributions for rounding errors', MSG_LEVEL_DEBUG );
2600
2601 BEGIN
2602 l_ignore := dbms_sql.execute( nonrule_update_dist2_c );
2603 close_cursor( nonrule_update_dist2_c );
2604
2605 debug( to_char(l_ignore) || ' row(s) updated', MSG_LEVEL_DEBUG );
2606
2607 /* Bug 4029814 - MRC call at end of this procedure */
2608
2609 EXCEPTION
2610 WHEN OTHERS THEN
2611 debug( 'EXCEPTION: Error executing update stmt' );
2612 RAISE;
2613 END;
2614
2615 print_fcn_label('arp_credit_memo_module.credit_nonrule_transactions()-' );
2616
2617 EXCEPTION
2618 WHEN OTHERS THEN
2619 debug( 'EXCEPTION: arp_credit_memo_module.credit_nonrule_transactions()' );
2620 RAISE;
2621
2622 END credit_nonrule_transactions;
2623
2624
2625 ----------------------------------------------------------------------------
2626 --
2627 -- PROCEDURE NAME: define_select_columns
2628 --
2629 -- DECSRIPTION:
2630 --
2631 -- ARGUMENTS:
2632 -- IN:
2633 -- select_c
2634 -- select_rec
2635 --
2636 -- IN/OUT:
2637 --
2638 -- OUT:
2639 --
2640 -- RETURNS:
2641 --
2642 -- NOTES:
2643 --
2644 -- HISTORY:
2645 --
2646 ----------------------------------------------------------------------------
2647 PROCEDURE define_select_columns(
2648 p_select_c IN INTEGER,
2649 p_select_rec IN OUT NOCOPY select_rec_type ) IS
2650
2651 BEGIN
2652
2656 dbms_sql.define_column( p_select_c, 2,
2653 print_fcn_label2( 'arp_credit_memo_module.define_select_columns()+' );
2654
2655 dbms_sql.define_column( p_select_c, 1, p_select_rec.customer_trx_line_id );
2657 p_select_rec.prev_cust_trx_line_id );
2658 dbms_sql.define_column( p_select_c, 3,
2659 p_select_rec.allow_overapp_flag, 1 );
2660 dbms_sql.define_column( p_select_c, 4, p_select_rec.cm_amount );
2661 dbms_sql.define_column( p_select_c, 5,
2662 p_select_rec.credit_method_for_rules, 1 );
2663 dbms_sql.define_column( p_select_c, 6,
2664 p_select_rec.last_period_to_credit );
2665 dbms_sql.define_column( p_select_c, 7, p_select_rec.currency_code, 15 );
2666 dbms_sql.define_column( p_select_c, 8,
2667 p_select_rec.inv_acct_rule_duration );
2668 dbms_sql.define_column( p_select_c, 9,
2669 p_select_rec.allow_not_open_flag, 1 );
2670 dbms_sql.define_column( p_select_c, 10, p_select_rec.cm_gl_date );
2671 dbms_sql.define_column( p_select_c, 11, p_select_rec.invoice_quantity );
2672 dbms_sql.define_column( p_select_c, 12,
2673 p_select_rec.cm_quantity );
2674 dbms_sql.define_column( p_select_c, 13,
2675 p_select_rec.invoice_sign);
2676 dbms_sql.define_column( p_select_c, 14, p_select_rec.cm_sign);
2677 dbms_sql.define_column( p_select_c, 15, p_select_rec.partial_period_flag,1);
2678 /* 4621029 */
2679 dbms_sql.define_column( p_select_c, 16, p_select_rec.inv_unit_price);
2680 dbms_sql.define_column( p_select_c, 17, p_select_rec.cm_unit_price);
2681 /* 4621029 end */
2682
2683 print_fcn_label2( 'arp_credit_memo_module.define_select_columns()-' );
2684
2685 EXCEPTION
2686 WHEN OTHERS THEN
2687 debug('EXCEPTION: arp_credit_memo_module.define_select_columns()');
2688 RAISE;
2689 END define_select_columns;
2690
2691 ----------------------------------------------------------------------------
2692 --
2693 -- PROCEDURE NAME: get_select_column_values
2694 --
2695 -- DECSRIPTION:
2696 --
2697 -- ARGUMENTS:
2698 -- IN:
2699 -- select_c
2700 -- select_rec
2701 --
2702 -- IN/OUT:
2703 --
2704 -- OUT:
2705 --
2706 -- RETURNS:
2707 --
2708 -- NOTES:
2709 --
2710 -- HISTORY:
2711 --
2712 ----------------------------------------------------------------------------
2713 PROCEDURE get_select_column_values(
2714 p_select_c IN INTEGER,
2715 p_select_rec IN OUT NOCOPY select_rec_type ) IS
2716 BEGIN
2717
2718 print_fcn_label2( 'arp_credit_memo_module.get_select_column_values()+' );
2719
2720 dbms_sql.column_value( p_select_c, 1, p_select_rec.customer_trx_line_id );
2721 dbms_sql.column_value( p_select_c, 2, p_select_rec.prev_cust_trx_line_id );
2722 dbms_sql.column_value( p_select_c, 3,
2723 p_select_rec.allow_overapp_flag );
2724 dbms_sql.column_value( p_select_c, 4, p_select_rec.cm_amount );
2725 dbms_sql.column_value( p_select_c, 5,
2726 p_select_rec.credit_method_for_rules );
2727 dbms_sql.column_value( p_select_c, 6, p_select_rec.last_period_to_credit );
2728 dbms_sql.column_value( p_select_c, 7, p_select_rec.currency_code );
2729 dbms_sql.column_value( p_select_c, 8,
2730 p_select_rec.inv_acct_rule_duration );
2731 dbms_sql.column_value( p_select_c, 9, p_select_rec.allow_not_open_flag );
2732 dbms_sql.column_value( p_select_c, 10, p_select_rec.cm_gl_date );
2733 dbms_sql.column_value( p_select_c, 11, p_select_rec.invoice_quantity );
2734 dbms_sql.column_value( p_select_c, 12,
2735 p_select_rec.cm_quantity );
2736 dbms_sql.column_value( p_select_c, 13,
2737 p_select_rec.invoice_sign );
2738 dbms_sql.column_value( p_select_c, 14, p_select_rec.cm_sign );
2739 dbms_sql.column_value( p_select_c, 15, p_select_rec.partial_period_flag);
2740 /* 4621029 */
2741 dbms_sql.column_value( p_select_c, 16, p_select_rec.inv_unit_price);
2742 dbms_sql.column_value( p_select_c, 17, p_select_rec.cm_unit_price);
2743 /* 4621029 end */
2744
2745
2746 print_fcn_label2( 'arp_credit_memo_module.get_select_column_values()-' );
2747
2748 EXCEPTION
2749 WHEN OTHERS THEN
2750 debug('EXCEPTION: arp_credit_memo_module.get_select_column_values()');
2751 RAISE;
2752 END get_select_column_values;
2753
2754 ----------------------------------------------------------------------------
2755 --
2756 -- PROCEDURE NAME: dump_select_rec
2757 --
2758 -- DECSRIPTION:
2759 --
2760 -- ARGUMENTS:
2761 -- IN:
2762 -- select_rec
2763 --
2764 -- IN/OUT:
2765 --
2766 -- OUT:
2767 --
2768 -- RETURNS:
2769 --
2770 -- NOTES:
2771 --
2772 -- HISTORY:
2773 --
2774 ----------------------------------------------------------------------------
2775 PROCEDURE dump_select_rec( p_select_rec IN select_rec_type ) IS
2776 BEGIN
2777
2778 print_fcn_label2( 'arp_credit_memo_module.dump_select_rec()+' );
2779
2780 debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
2784 || to_char( p_select_rec.prev_cust_trx_line_id ), MSG_LEVEL_DEBUG );
2781 debug( ' customer_trx_line_id='
2782 || to_char( p_select_rec.customer_trx_line_id ), MSG_LEVEL_DEBUG );
2783 debug( ' prev_cust_trx_line_id='
2785 debug( ' allow_overapp_flag=' || p_select_rec.allow_overapp_flag ,
2786 MSG_LEVEL_DEBUG );
2787 debug( ' cm_amount='
2788 || to_char( p_select_rec.cm_amount ), MSG_LEVEL_DEBUG );
2789 debug( ' credit_method_for_rules='
2790 || p_select_rec.credit_method_for_rules, MSG_LEVEL_DEBUG );
2791 debug( ' last_period_to_credit='
2792 || to_char( p_select_rec.last_period_to_credit ), MSG_LEVEL_DEBUG );
2793 debug( ' currency_code=' || p_select_rec.currency_code, MSG_LEVEL_DEBUG );
2794 debug( ' inv_acct_rule_duration='
2795 || to_char( p_select_rec.inv_acct_rule_duration ), MSG_LEVEL_DEBUG );
2796 debug( ' allow_not_open_flag=' ||
2797 p_select_rec.allow_not_open_flag, MSG_LEVEL_DEBUG );
2798 debug( ' cm_gl_date=' || to_char( p_select_rec.cm_gl_date ),
2799 MSG_LEVEL_DEBUG );
2800 debug( ' invoice_quantity='
2801 || to_char( p_select_rec.invoice_quantity ), MSG_LEVEL_DEBUG );
2802 debug( ' cm_quantity=' ||
2803 to_char( p_select_rec.cm_quantity ), MSG_LEVEL_DEBUG );
2804 debug( ' invoice_sign='
2805 || p_select_rec.invoice_sign, MSG_LEVEL_DEBUG );
2806 debug( ' cm_sign='
2807 || p_select_rec.cm_sign, MSG_LEVEL_DEBUG );
2808 debug( ' inv_unit_price='
2809 || p_select_rec.inv_unit_price, MSG_LEVEL_DEBUG );
2810 debug( ' cm_unit_price='
2811 || p_select_rec.cm_unit_price, MSG_LEVEL_DEBUG );
2812
2813 debug( ' partial_period_flag='
2814 || p_select_rec.partial_period_flag, MSG_LEVEL_DEBUG );
2815
2816 print_fcn_label2( 'arp_credit_memo_module.dump_select_rec()-' );
2817
2818 EXCEPTION
2819 WHEN OTHERS THEN
2820 debug( 'EXCEPTION: arp_credit_memo_module.dump_select_rec()' );
2821 RAISE;
2822 END dump_select_rec;
2823
2824 ----------------------------------------------------------------------------
2825 --
2826 -- FUNCTION NAME: find_cm_schedule
2827 --
2828 -- DECSRIPTION:
2829 -- Given a line_id and search date, searches the cm schedule
2830 -- line_id and orig_gl_date tables for a match.
2831 -- Updates match_index with the index that matched.
2832 --
2833 -- ARGUMENTS:
2834 -- IN:
2835 -- line_id
2836 -- search_date
2837 --
2838 -- IN/OUT:
2839 -- match_index
2840 --
2841 -- OUT:
2842 --
2843 -- RETURNS:
2844 -- TRUE if found a match, FALSE otherwise
2845 --
2846 -- NOTES:
2847 --
2848 -- HISTORY:
2849 --
2850 ----------------------------------------------------------------------------
2851 FUNCTION find_cm_schedule(
2852 p_line_id IN BINARY_INTEGER,
2853 p_search_date IN DATE,
2854 p_match_index IN OUT NOCOPY BINARY_INTEGER )
2855
2856 RETURN BOOLEAN IS
2857
2858 BEGIN
2859
2860 print_fcn_label2('arp_credit_memo_module.find_cm_schedule()+' );
2861
2862 BEGIN
2863
2864 FOR i IN 0..cm_sched_index LOOP
2865 IF( cm_sched_ctlid_t( i ) = p_line_id AND
2866 cm_sched_orig_gl_date_t( i ) = p_search_date ) THEN
2867
2868 p_match_index := i;
2869 debug( ' Match at index ' || i, MSG_LEVEL_DEBUG );
2870 print_fcn_label('arp_credit_memo_module.find_cm_schedule()-' );
2871
2872 RETURN TRUE; -- Done
2873
2874 END IF;
2875 END LOOP;
2876
2877 EXCEPTION
2878 WHEN NO_DATA_FOUND THEN
2879 null; -- table not set up yet
2880 END;
2881
2882 debug( ' No match', MSG_LEVEL_DEBUG );
2883 print_fcn_label2('arp_credit_memo_module.find_cm_schedule()-' );
2884
2885 RETURN FALSE;
2886
2887 EXCEPTION
2888 WHEN OTHERS THEN
2889 debug( 'EXCEPTION: arp_credit_memo_module.find_cm_schedule('
2890 || to_char(p_line_id) || ', '
2891 || to_char(p_search_date) || ')' );
2892 RAISE;
2893
2894 END find_cm_schedule;
2895
2896 ------------------------------------------------------------------------
2897
2898 PROCEDURE write_cm_sched_to_table IS
2899 /*------------------------------------------------------------
2900 | Bug # 2988282 - ORASHID: 07-07-2003
2901 | The following cursor takes the credit memo line id and
2902 | fetches its extended amount, and does a self join to
2903 | the corresponding invoice line and invoice line's rule id
2904 | and rule start date.
2905 +------------------------------------------------------------*/
2906
2907 CURSOR lines (p_cm_line_id NUMBER) IS
2908 SELECT invline.accounting_rule_id,
2909 invline.rule_start_date,
2910 cmline.extended_amount line_amount
2911 FROM ra_customer_trx_lines_all cmline,
2912 ra_customer_trx_lines_all invline
2913 WHERE cmline.previous_customer_trx_line_id =
2914 invline.customer_trx_line_id
2918 l_accounting_rule_id ra_customer_trx_lines_all.accounting_rule_id%TYPE;
2915 AND cmline.customer_trx_line_id = p_cm_line_id;
2916
2917 l_ignore INTEGER;
2919 l_line_amount ra_customer_trx_lines_all.extended_amount%TYPE;
2920 l_rule_start_date ra_customer_trx_lines_all.rule_start_date%TYPE;
2921 l_original_gl_date ra_customer_trx_lines_all.rule_start_date%TYPE;
2922 gl_dist_id ra_cust_trx_line_gl_dist.cust_trx_line_gl_dist_id%TYPE;
2923
2924 BEGIN
2925
2926 print_fcn_label2('arp_credit_memo_module.write_cm_sched_to_table()+' );
2927
2928 /* Bug 1956518: Added 'REVERSE' for the 'FOR LOOP' so that the
2929 distribution lines will be inserted in ascending order of
2930 gl_date when the rules method is 'PRORATE'. */
2931 /* Bug 2136455: Added 'REVERSE' for all rule methods */
2932
2933 FOR i in REVERSE 0..cm_sched_index - 1 LOOP
2934
2935 debug(' customer_trx_line_id='||cm_sched_ctlid_t( i ),
2936 MSG_LEVEL_DEBUG);
2937 debug(' gl_date='||cm_sched_gl_date_t( i ),
2938 MSG_LEVEL_DEBUG);
2939 debug(' original_gl_date='||cm_sched_orig_gl_date_t( i ),
2940 MSG_LEVEL_DEBUG);
2941 debug(' amount='||cm_sched_amount_t( i ), MSG_LEVEL_DEBUG);
2942 debug(' insert_offset='||cm_sched_insert_offset_t( i ),
2943 MSG_LEVEL_DEBUG);
2944 debug(' check_gl_date='||cm_sched_check_gl_date_t( i ),
2945 MSG_LEVEL_DEBUG);
2946
2947
2948 IF( cm_sched_insert_dist_t( i ) = YES ) THEN
2949 -------------------------------------------------------------
2950 -- Insert into ra_cust_trx_line_gl_dist
2951 -------------------------------------------------------------
2952 -------------------------------------------------------------
2953 -- Bind vars
2954 -------------------------------------------------------------
2955 BEGIN
2956 debug( ' Binding rule_insert_dist_c', MSG_LEVEL_DEBUG );
2957
2958 dbms_sql.bind_variable( rule_insert_dist_c,
2959 'customer_trx_line_id',
2960 cm_sched_ctlid_t( i ) );
2961 /*7147479 added binding for customer_trx_line_id_1*/
2962 dbms_sql.bind_variable( rule_insert_dist_c,
2963 'customer_trx_line_id_1',
2964 cm_sched_ctlid_t( i ) );
2965 dbms_sql.bind_variable( rule_insert_dist_c,
2966 'gl_date',
2967 cm_sched_gl_date_t( i ) );
2968 dbms_sql.bind_variable( rule_insert_dist_c,
2969 'amount',
2970 cm_sched_amount_t( i ) );
2971 dbms_sql.bind_variable( rule_insert_dist_c,
2972 'amount_1',
2973 cm_sched_amount_t( i ) );
2974 dbms_sql.bind_variable( rule_insert_dist_c,
2975 'amount_2',
2976 cm_sched_amount_t( i ) );
2977 dbms_sql.bind_variable( rule_insert_dist_c,
2978 'amount_3',
2979 cm_sched_amount_t( i ) );
2980 dbms_sql.bind_variable( rule_insert_dist_c,
2981 'amount_4',
2982 cm_sched_amount_t( i ) );
2983 dbms_sql.bind_variable( rule_insert_dist_c,
2984 'amount_5',
2985 cm_sched_amount_t( i ) );
2986 dbms_sql.bind_variable( rule_insert_dist_c,
2987 'amount_6',
2988 cm_sched_amount_t( i ) );
2989 /*7147479 changed bind variable insert_offset_1*/
2990 dbms_sql.bind_variable( rule_insert_dist_c,
2991 'insert_offset_1',
2992 cm_sched_insert_offset_t( i ) );
2993 /*7147479 added extra bind variable insert_offset_2*/
2994 dbms_sql.bind_variable( rule_insert_dist_c,
2995 'insert_offset_2',
2996 cm_sched_insert_offset_t( i ) );
2997
2998 dbms_sql.bind_variable( rule_insert_dist_c,
2999 'check_gl_date_1',
3000 cm_sched_check_gl_date_t( i ) );
3001
3002
3003 /* Bug 2899714 - bind variable not defined when
3004 use_inv_acct set to no */
3005 IF( profile_info.use_inv_acct_for_cm_flag = YES ) THEN
3006 /* 4254587 */
3007 dbms_sql.bind_variable( rule_insert_dist_c,
3008 'period_set_name',
3009 system_info.period_set_name );
3010
3011 /* Bug 2347001 - session_id */
3012 dbms_sql.bind_variable( rule_insert_dist_c,
3013 'session_id',
3014 g_session_id);
3015
3019 cm_sched_ctlid_t( i ) );
3016 /*7147479 - added new bind variables*/
3017 dbms_sql.bind_variable( rule_insert_dist_c,
3018 'customer_trx_line_id_2',
3020 dbms_sql.bind_variable( rule_insert_dist_c,
3021 'original_gl_date_1',
3022 cm_sched_orig_gl_date_t( i ) );
3023 dbms_sql.bind_variable( rule_insert_dist_c,
3024 'gl_date_2',
3025 cm_sched_gl_date_t( i ) );
3026 dbms_sql.bind_variable( rule_insert_dist_c,
3027 'gl_date_3',
3028 cm_sched_gl_date_t( i ) );
3029 dbms_sql.bind_variable( rule_insert_dist_c,
3030 'gl_date_4',
3031 cm_sched_gl_date_t( i ) );
3032 /*bug-7147479 added For Use Invoice Accounting for CM is NO*/
3033 ELSE
3034 /* in SELECT portion of statement */
3035 dbms_sql.bind_variable( rule_insert_dist_c,
3036 'original_gl_date',
3037 cm_sched_orig_gl_date_t( i ) );
3038 /* in WHERE portion of statement */
3039 dbms_sql.bind_variable( rule_insert_dist_c,
3040 'gl_date_1',
3041 cm_sched_gl_date_t( i ) );
3042
3043 dbms_sql.bind_variable( rule_insert_dist_c,
3044 'check_gl_date_2',
3045 cm_sched_check_gl_date_t( i ) );
3046 END IF;
3047
3048 EXCEPTION
3049 WHEN OTHERS THEN
3050 debug( 'EXCEPTION: Error in binding rule_insert_dist_c' );
3051 RAISE;
3052 END;
3053
3054 -------------------------------------------------------------
3055 -- Execute
3056 -------------------------------------------------------------
3057 BEGIN
3058 arp_standard.debug( ' Inserting distributions');
3059 l_ignore := dbms_sql.execute( rule_insert_dist_c );
3060 EXCEPTION
3061 WHEN OTHERS THEN
3062 debug( 'EXCEPTION: Error executing insert dist stmt' );
3063 RAISE;
3064 END;
3065 END IF;
3066
3067 IF( cm_sched_insert_cma_t( i ) = YES ) THEN
3068 -------------------------------------------------------------
3069 -- Insert into ar_credit_memo_amounts
3070 -------------------------------------------------------------
3071
3072 /*--------------------------------------------------------------
3073 | Bug # 2988282 - ORASHID: 07-07-2003
3074 | If the credit memo line amount is zero and the corresponding
3075 | invoice line is rule based then use the invoice line's
3076 | rule start date as the gl date, otherwise continue with
3077 | exisitng flow.
3078 +--------------------------------------------------------------*/
3079
3080 OPEN lines(cm_sched_ctlid_t(i));
3081 FETCH lines INTO l_accounting_rule_id,
3082 l_rule_start_date,
3083 l_line_amount;
3084 CLOSE lines;
3085
3086 debug('Accounting Rule ID : ' || l_accounting_rule_id);
3087 debug('Rule Start Date : ' || l_rule_start_date);
3088 debug('Line Amount : ' || l_line_amount);
3089
3090 IF (l_line_amount = 0 AND l_accounting_rule_id IS NOT NULL) THEN
3091 l_original_gl_date := l_rule_start_date;
3092 ELSE
3093 l_original_gl_date := cm_sched_orig_gl_date_t(i);
3094 END IF;
3095
3096 -------------------------------------------------------------
3097 -- Bind vars
3098 -------------------------------------------------------------
3099 dbms_sql.bind_variable( rule_insert_cma_c,
3100 'customer_trx_line_id',
3101 cm_sched_ctlid_t( i ) );
3102 dbms_sql.bind_variable( rule_insert_cma_c,
3103 'gl_date',
3104 l_original_gl_date);
3105 dbms_sql.bind_variable( rule_insert_cma_c,
3106 'amount',
3107 cm_sched_amount_t( i ) );
3108 -------------------------------------------------------------
3109 -- Execute
3110 -------------------------------------------------------------
3111 BEGIN
3112
3113 debug( ' Inserting CM amounts', MSG_LEVEL_DEBUG );
3114 l_ignore := dbms_sql.execute( rule_insert_cma_c );
3115 debug( to_char(l_ignore) || ' row(s) inserted',
3116 MSG_LEVEL_DEBUG );
3117
3118 EXCEPTION
3119 WHEN OTHERS THEN
3120 debug( 'EXCEPTION: Error executing insert cma stmt' );
3124 END IF;
3121 RAISE;
3122 END;
3123
3125
3126 END LOOP;
3127
3128 print_fcn_label2('arp_credit_memo_module.write_cm_sched_to_table()-' );
3129
3130 EXCEPTION
3131 WHEN OTHERS THEN
3132 debug( 'EXCEPTION: arp_credit_memo_module.write_cm_sched_to_table()' );
3133 RAISE;
3134 END write_cm_sched_to_table;
3135
3136 ------------------------------------------------------------------------
3137
3138 PROCEDURE clear_cm_sched_tables IS
3139
3140 BEGIN
3141 print_fcn_label2('arp_credit_memo_module.clear_cm_sched_tables()+' );
3142
3143 cm_sched_ctlid_t := null_cm_sched_ctlid;
3144 cm_sched_gl_date_t:= null_cm_sched_gl_date;
3145 cm_sched_orig_gl_date_t:=null_cm_sched_orig_gl_date;
3146 cm_sched_amount_t:=null_cm_sched_amount;
3147 cm_sched_insert_dist_t:=null_cm_sched_insert_dist;
3148 cm_sched_insert_cma_t:=null_cm_sched_insert_cma;
3149 cm_sched_insert_offset_t:=null_cm_sched_insert_offset;
3150 cm_sched_check_gl_date_t:=null_cm_sched_check_gl_date;
3151
3152 cm_sched_index := 0;
3153
3154 /* 4602892 - reset net_rev arrays */
3155 net_rev_ctlid_t( 0 ) := 0;
3156
3157 print_fcn_label2('arp_credit_memo_module.clear_cm_sched_tables()-' );
3158
3159 EXCEPTION
3160 WHEN OTHERS THEN
3161 debug( 'EXCEPTION: arp_credit_memo_module.clear_cm_sched_tables()' );
3162 RAISE;
3163 END clear_cm_sched_tables;
3164
3165
3166 ----------------------------------------------------------------------------
3167 --
3168 -- PROCEDURE NAME: insert_cm_schedule
3169 --
3170 -- DECSRIPTION:
3171 --
3172 --
3173 --
3174 -- ARGUMENTS:
3175 -- IN:
3176 -- line_id
3177 -- gl_date
3178 -- orig_gl_date
3179 -- amount
3180 -- insert_dist_flag
3181 -- insert_cma_flag
3182 -- insert_offset_flag
3183 -- check_gl_date_flag
3184 --
3185 -- IN/OUT:
3186 --
3187 -- OUT:
3188 --
3189 -- RETURNS:
3190 --
3191 -- NOTES:
3192 --
3193 -- HISTORY:
3194 --
3195 ----------------------------------------------------------------------------
3196 PROCEDURE insert_cm_schedule(
3197 p_line_id IN BINARY_INTEGER,
3198 p_gl_date IN DATE,
3199 p_orig_gl_date IN DATE,
3200 p_amount IN NUMBER,
3201 p_insert_dist_flag IN VARCHAR,
3202 p_insert_cma_flag IN VARCHAR,
3203 p_insert_offset_flag IN VARCHAR,
3204 p_check_gl_date_flag IN VARCHAR ) IS
3205
3206 l_index BINARY_INTEGER;
3207
3208 BEGIN
3209
3210 print_fcn_label('arp_credit_memo_module.insert_cm_schedule()+' );
3211
3212 /* 4633761 - removed write and clear logic... It was
3213 interferring with LIFO CM processing. Moved to
3214 main processing area in credit_rule_transactions */
3215
3216 debug(' cm_sched_index='||cm_sched_index, MSG_LEVEL_DEBUG);
3217 debug(' p_line_id='||p_line_id, MSG_LEVEL_DEBUG);
3218 debug(' p_gl_date='||p_gl_date, MSG_LEVEL_DEBUG);
3219 debug(' p_orig_gl_date='||p_orig_gl_date, MSG_LEVEL_DEBUG);
3220 debug(' p_amount='||p_amount, MSG_LEVEL_DEBUG);
3221 debug(' p_insert_dist-flag='||p_insert_dist_flag, MSG_LEVEL_DEBUG);
3222 debug(' p_insert_cma_flag='||p_insert_cma_flag, MSG_LEVEL_DEBUG);
3223 debug(' p_insert_offset_flag='||p_insert_offset_flag, MSG_LEVEL_DEBUG);
3224 debug(' p_check_gl_date_flag='||p_check_gl_date_flag, MSG_LEVEL_DEBUG);
3225
3226
3227 cm_sched_ctlid_t( cm_sched_index ) := p_line_id;
3228 cm_sched_gl_date_t( cm_sched_index ) := p_gl_date;
3229 cm_sched_orig_gl_date_t( cm_sched_index ) := p_orig_gl_date;
3230 cm_sched_amount_t( cm_sched_index ) := p_amount;
3231 cm_sched_insert_dist_t( cm_sched_index ) := p_insert_dist_flag;
3232 cm_sched_insert_cma_t( cm_sched_index ) := p_insert_cma_flag;
3233 cm_sched_insert_offset_t( cm_sched_index ) := p_insert_offset_flag;
3234 cm_sched_check_gl_date_t( cm_sched_index ) := p_check_gl_date_flag;
3235
3236 cm_sched_index := cm_sched_index + 1;
3237
3238 print_fcn_label('arp_credit_memo_module.insert_cm_schedule()-' );
3239
3240
3241 EXCEPTION
3242 WHEN OTHERS THEN
3243 debug( 'EXCEPTION: arp_credit_memo_module.insert_cm_schedule('
3244 || to_char(p_line_id) || ', '
3245 || to_char(p_gl_date) || ', '
3246 || to_char(p_orig_gl_date) || ', '
3247 || to_char(p_amount) || ', '
3248 || p_insert_dist_flag || ', '
3249 || p_insert_cma_flag || ', '
3250 || p_insert_offset_flag || ', '
3251 || p_check_gl_date_flag || ')' );
3252 RAISE;
3253
3254 END insert_cm_schedule;
3255
3256 ----------------------------------------------------------------------------
3257 --
3258 -- FUNCTION NAME: update_cm_schedule
3259 --
3260 -- DECSRIPTION:
3261 --
3262 --
3263 --
3264 -- ARGUMENTS:
3265 -- IN:
3266 -- line_id
3267 -- gl_date
3268 -- amount
3269 --
3270 -- IN/OUT:
3271 --
3272 -- OUT:
3273 --
3274 -- RETURNS:
3275 -- TRUE if record is found, else FALSE
3276 --
3277 -- NOTES:
3278 --
3279 -- HISTORY:
3280 --
3284 p_gl_date IN DATE,
3281 ----------------------------------------------------------------------------
3282 FUNCTION update_cm_schedule(
3283 p_line_id IN BINARY_INTEGER,
3285 p_amount IN NUMBER )
3286
3287 RETURN BOOLEAN IS
3288
3289 l_index BINARY_INTEGER;
3290
3291 BEGIN
3292
3293 print_fcn_label('arp_credit_memo_module.update_cm_schedule()+' );
3294
3295 IF( find_cm_schedule( p_line_id,
3296 p_gl_date,
3297 l_index ) = FALSE ) THEN
3298
3299 print_fcn_label('arp_credit_memo_module.update_cm_schedule()-' );
3300
3301 RETURN FALSE; -- didn't find record
3302
3303 ELSE
3304 cm_sched_amount_t(l_index) := cm_sched_amount_t(l_index) +
3305 p_amount;
3306 RETURN TRUE;
3307
3308 END IF;
3309
3310 print_fcn_label('arp_credit_memo_module.update_cm_schedule()-' );
3311
3312
3313 EXCEPTION
3314 WHEN OTHERS THEN
3315 debug( 'EXCEPTION: arp_credit_memo_module.update_cm_schedule('
3316 || to_char(p_line_id) || ', '
3317 || to_char(p_gl_date) || ', '
3318 || to_char(p_amount) || ')' );
3319 RAISE;
3320
3321 END update_cm_schedule;
3322
3323 ----------------------------------------------------------------------------
3324 --
3325 -- PROCEDURE NAME: process_cm_schedule
3326 --
3327 -- DECSRIPTION:
3328 --
3329 --
3330 --
3331 -- ARGUMENTS:
3332 -- IN:
3333 -- mode
3334 -- line_id
3335 -- gl_date
3336 -- amount
3337 -- allow_not_open_flag
3338 -- insert_dist_flag
3339 -- insert_cma_flag
3340 -- insert_offset_flag
3341 -- check_gl_date_flag
3342 --
3343 -- IN/OUT:
3344 --
3345 -- OUT:
3346 --
3347 -- RETURNS:
3348 --
3349 -- NOTES:
3350 --
3351 -- HISTORY:
3352 --
3353 ----------------------------------------------------------------------------
3354 PROCEDURE process_cm_schedule(
3355 p_mode IN VARCHAR,
3356 p_line_id IN BINARY_INTEGER,
3357 p_gl_date IN DATE,
3358 p_amount IN NUMBER,
3359 p_allow_not_open_flag IN VARCHAR,
3360 p_insert_dist_flag IN VARCHAR,
3361 p_insert_cma_flag IN VARCHAR,
3362 p_insert_offset_flag IN VARCHAR,
3363 p_check_gl_date_flag IN VARCHAR )
3364 IS
3365
3366 l_gl_date DATE;
3367 l_bool BOOLEAN;
3368
3369 /* bug 3477990 */
3370 l_rule_id NUMBER;
3371 l_result_flag BOOLEAN;
3372 l_defaulting_rule_used varchar2(300);
3373 l_error_message varchar2(300);
3374
3375 BEGIN
3376
3377 print_fcn_label('arp_credit_memo_module.process_cm_schedule()+' );
3378
3379 debug(' p_mode='||p_mode, MSG_LEVEL_DEBUG);
3380 debug(' p_line_id='||p_line_id, MSG_LEVEL_DEBUG);
3381 debug(' p_gl_date='||to_char(p_gl_date), MSG_LEVEL_DEBUG);
3382 debug(' p_amount='||p_amount, MSG_LEVEL_DEBUG);
3383 debug(' p_allow_not_open_flag='||p_allow_not_open_flag, MSG_LEVEL_DEBUG);
3384 debug(' p_insert_dist_flag='||p_insert_dist_flag, MSG_LEVEL_DEBUG);
3385 debug(' p_insert_cma_flag='||p_insert_cma_flag, MSG_LEVEL_DEBUG);
3386 debug(' p_check_gl_date_flag='||p_check_gl_date_flag, MSG_LEVEL_DEBUG);
3387
3388 ------------------------------------------------------------------------
3389 -- For inserts only
3390 ------------------------------------------------------------------------
3391 /* Bug 3477990 */
3392
3393 IF( arp_util.is_gl_date_valid( p_gl_date,
3394 p_allow_not_open_flag ) ) THEN
3395
3396 --------------------------------------------------------------------
3397 -- p_gl_date is good, use it
3398 --------------------------------------------------------------------
3399 IF pg_closed_period_exists = 'Y' THEN
3400 debug('setting pg_closed_period_exists to NULL 1');
3401 pg_closed_period_exists := NULL;
3402 g_error_buffer := MSG_MISSING_PERIODS;
3403 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
3404 RAISE missing_periods;
3405
3406 ELSE
3407 l_gl_date := p_gl_date;
3408 END IF;
3409
3410 ELSE
3411 BEGIN
3412 SELECT invoicing_rule_id
3413 INTO l_rule_id
3414 FROM ra_customer_trx ct, ra_customer_trx_lines ctl
3415 WHERE ct.customer_trx_id = ctl.customer_trx_id
3416 AND ctl.customer_trx_line_id = p_line_id;
3417
3418 l_result_flag := arp_util.validate_and_default_gl_date(
3419 p_gl_date,
3420 NULL,
3421 NULL,
3422 NULL,
3423 null,
3424 NULL,
3425 null,
3426 null,
3427 p_allow_not_open_flag,
3428 l_rule_id,
3429 system_info.system_parameters.set_of_books_id ,
3433 l_error_message);
3430 222,
3431 l_gl_date,
3432 l_defaulting_rule_used,
3434 EXCEPTION
3435 WHEN OTHERS THEN
3436 debug( 'EXCEPTION: arp_credit_memo_module.process_cm_schedule Validate GL Date ');
3437 RAISE;
3438 END;
3439 debug('setting pg_closed_period_exists to Y');
3440 pg_closed_period_exists := 'Y';
3441 END IF;
3442
3443 /* End Bug 3477990 */
3444
3445 IF( p_mode = I ) THEN
3446 --------------------------------------------------------------------
3447 -- Insert mode
3448 --------------------------------------------------------------------
3449 insert_cm_schedule( p_line_id,
3450 l_gl_date,
3451 p_gl_date, -- orig_gl_date
3452 p_amount,
3453 p_insert_dist_flag,
3454 p_insert_cma_flag,
3455 p_insert_offset_flag,
3456 p_check_gl_date_flag );
3457
3458 ELSIF( p_mode = U ) THEN
3459 --------------------------------------------------------------------
3460 -- Update (overapplication) mode
3461 --------------------------------------------------------------------
3462 l_bool := update_cm_schedule( p_line_id,
3463 p_gl_date,
3464 p_amount );
3465
3466
3467 IF( l_bool = FALSE ) THEN
3468 ----------------------------------------------------------------
3469 -- gl_date not in cm schedule tables, update the database
3470 ----------------------------------------------------------------
3471 BEGIN
3472 debug( ' Updating ar_credit_memo_amounts table',
3473 MSG_LEVEL_DEBUG );
3474
3475 UPDATE ar_credit_memo_amounts
3476 SET amount = amount + p_amount
3477 WHERE customer_trx_line_id = p_line_id
3478 and gl_date = p_gl_date;
3479
3480 debug( SQL%ROWCOUNT||' row(s) updated', MSG_LEVEL_DEBUG );
3481
3482 IF( SQL%FOUND ) THEN
3483 ----------------------------------------------------
3484 -- Update successful
3485 --
3486 -- Only create REV dist,
3487 -- don't create cm amounts rec
3488 ----------------------------------------------------
3489 debug( ' Update successful', MSG_LEVEL_DEBUG );
3490 insert_cm_schedule( p_line_id,
3491 l_gl_date,
3492 p_gl_date, -- orig_gl_date
3493 p_amount,
3494 p_insert_dist_flag,
3495 NO, -- p_insert_cma_flag
3496 NO, -- p_insert_offset_flag
3497 YES -- p_check_gl_date_flag
3498 );
3499
3500 ELSE
3501 ----------------------------------------------------
3502 -- No cma record to update.
3503 -- Entire cm line is an overapplication
3504 -- Insert a cma record into array and increment
3505 -- cm_acct_rule_duration
3506 -- Create REV and UNEARN dist
3507 ----------------------------------------------------
3508
3509 debug( ' Update unsuccessful', MSG_LEVEL_DEBUG );
3510 debug( ' Entire cm line overapp',
3511 MSG_LEVEL_DEBUG );
3512 insert_cm_schedule( p_line_id,
3513 l_gl_date,
3514 p_gl_date, -- orig_gl_date
3515 p_amount,
3516 p_insert_dist_flag,
3517 YES, -- p_insert_cma_flag
3518 YES, -- p_insert_offset_flag
3519 YES -- p_check_gl_date_flag
3520 );
3521
3522
3523 -- p_cm_acct_rule_duration := p_cm_acct_rule_duration +1;
3524
3525 END IF;
3526
3527 END;
3528
3529 END IF;
3530
3531
3532 ELSE
3533 --------------------------------------------------------------------
3534 -- Invalid mode
3535 --------------------------------------------------------------------
3536 debug( ' raising invalid_mode', MSG_LEVEL_DEBUG );
3537 g_error_buffer := 'Invalid mode: ' || p_mode;
3538 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
3539 RAISE invalid_mode;
3540
3541 END IF;
3542
3543
3544 print_fcn_label('arp_credit_memo_module.process_cm_schedule()-' );
3545
3546 EXCEPTION
3547 WHEN OTHERS THEN
3548 debug( 'EXCEPTION: arp_credit_memo_module.process_cm_schedule('
3549 || p_mode || ', '
3550 || to_char(p_line_id) || ', '
3551 || to_char(p_gl_date) || ', '
3552 || to_char(p_amount) || ', '
3553 || p_insert_dist_flag || ', '
3554 || p_insert_cma_flag || ', '
3555 || p_insert_offset_flag || ', '
3556 || p_check_gl_date_flag || ')' );
3557 RAISE;
3558
3559 END process_cm_schedule;
3560
3561
3562 ----------------------------------------------------------------------------
3563 --
3564 -- FUNCTION NAME: find_net_revenue
3565 --
3566 -- DECSRIPTION:
3567 -- Given a line_id and search date, searches the net revenue
3568 -- gl_date table for a match.
3569 -- Updates match_index with the index that matched.
3570 --
3571 -- ARGUMENTS:
3572 -- IN:
3576 -- IN/OUT:
3573 -- line_id
3574 -- search_date
3575 --
3577 -- match_index
3578 --
3579 -- OUT:
3580 --
3581 -- RETURNS:
3582 -- TRUE if found a match, FALSE otherwise
3583 --
3584 -- NOTES:
3585 --
3586 -- HISTORY:
3587 --
3588 ----------------------------------------------------------------------------
3589 FUNCTION find_net_revenue(
3590 p_line_id IN BINARY_INTEGER,
3591 p_search_date IN DATE,
3592 p_match_index IN OUT NOCOPY BINARY_INTEGER )
3593
3594 RETURN BOOLEAN IS
3595
3596 BEGIN
3597
3598 print_fcn_label2('arp_credit_memo_module.find_net_revenue()+' );
3599
3600 debug( 'p_line_id='||p_line_id, MSG_LEVEL_DEBUG );
3601 debug( 'p_search_date='||to_char(p_search_date, 'DD-MON-YYYY'),
3602 MSG_LEVEL_DEBUG );
3603
3604 FOR i IN REVERSE net_rev_start_index..net_rev_index - 1 LOOP
3605 IF( net_rev_ctlid_t(i) = p_line_id AND
3606 net_rev_gl_date_t(i) = p_search_date ) THEN
3607
3608 p_match_index := i;
3609 debug( ' Match at index ' || i, MSG_LEVEL_DEBUG );
3610 print_fcn_label2('arp_credit_memo_module.find_net_revenue()-' );
3611
3612 RETURN TRUE;
3613
3614 END IF;
3615 END LOOP;
3616
3617 debug( ' No match', MSG_LEVEL_DEBUG );
3618 print_fcn_label2('arp_credit_memo_module.find_net_revenue()-' );
3619
3620 RETURN FALSE;
3621
3622 EXCEPTION
3623 WHEN OTHERS THEN
3624 debug( 'EXCEPTION: arp_credit_memo_module.find_net_revenue('
3625 || to_char(p_line_id) || ', '
3626 || to_char(p_search_date) || ')' );
3627 RAISE;
3628
3629 END find_net_revenue;
3630
3631 ------------------------------------------------------------------------
3632
3633 FUNCTION get_net_rev_dist_exists( p_index IN BINARY_INTEGER )
3634
3635 RETURN VARCHAR IS
3636
3637 BEGIN
3638
3639 RETURN net_rev_dist_exists_t( p_index );
3640
3641 EXCEPTION
3642 WHEN OTHERS THEN
3643 debug( 'EXCEPTION: arp_credit_memo_module.get_net_rev_dist_exists('
3644 || to_char(p_index) || ')' );
3645 RAISE;
3646
3647 END get_net_rev_dist_exists;
3648
3649 ------------------------------------------------------------------------
3650
3651 FUNCTION get_net_rev_gl_date( p_index IN BINARY_INTEGER )
3652
3653 RETURN DATE IS
3654
3655 BEGIN
3656
3657 RETURN net_rev_gl_date_t( p_index );
3658
3659 EXCEPTION
3660 WHEN OTHERS THEN
3661 debug( 'EXCEPTION: arp_credit_memo_module.get_net_rev_gl_date('
3662 || to_char(p_index) || ')' );
3663 RAISE;
3664
3665 END get_net_rev_gl_date;
3666
3667 ------------------------------------------------------------------------
3668
3669 FUNCTION get_net_rev_amount( p_index IN BINARY_INTEGER )
3670
3671 RETURN NUMBER IS
3672
3673 BEGIN
3674
3675 RETURN net_rev_amount_t( p_index );
3676
3677 EXCEPTION
3678 WHEN OTHERS THEN
3679 debug( 'EXCEPTION: arp_credit_memo_module.get_net_rev_amount('
3680 || to_char(p_index) || ')' );
3681 RAISE;
3682
3683 END get_net_rev_amount;
3684
3685 ------------------------------------------------------------------------
3686
3687 FUNCTION get_net_rev_total_amount(
3688 p_start_index IN BINARY_INTEGER,
3689 p_end_index IN BINARY_INTEGER )
3690
3691 RETURN NUMBER IS
3692
3693 l_amount NUMBER := 0;
3694
3695 BEGIN
3696
3697 FOR i IN p_start_index..p_end_index LOOP
3698 l_amount := l_amount + net_rev_amount_t(i);
3699 END LOOP;
3700
3701 RETURN l_amount;
3702
3703 EXCEPTION
3704 WHEN OTHERS THEN
3705 debug( 'EXCEPTION: arp_credit_memo_module.get_net_rev_total_amount('
3706 || to_char(p_start_index) || ', '
3707 || to_char(p_end_index) || ')' );
3708 RAISE;
3709
3710 END get_net_rev_total_amount;
3711
3712 ------------------------------------------------------------------------
3713
3714 FUNCTION get_net_rev_unit( p_index IN BINARY_INTEGER )
3715
3716 RETURN NUMBER IS
3717
3718 BEGIN
3719
3720 RETURN net_rev_unit_t( p_index );
3721
3722 EXCEPTION
3723 WHEN OTHERS THEN
3724 debug( 'EXCEPTION: arp_credit_memo_module.get_net_rev_unit('
3725 || to_char(p_index) || ')' );
3726 RAISE;
3727
3728 END get_net_rev_unit;
3729
3730 ------------------------------------------------------------------------
3731
3732 PROCEDURE update_net_revenue(
3733 p_index IN BINARY_INTEGER,
3734 p_amount IN NUMBER ) IS
3735
3736 BEGIN
3737
3738 net_rev_amount_t( p_index ) := p_amount;
3739
3740 EXCEPTION
3741 WHEN OTHERS THEN
3742 debug( 'EXCEPTION: arp_credit_memo_module.update_net_revenue('
3743 || to_char(p_index) || ')' );
3744 RAISE;
3745
3746 END update_net_revenue;
3747
3748 ------------------------------------------------------------------------
3749
3753
3750 PROCEDURE update_net_rev_unit(
3751 p_index IN BINARY_INTEGER,
3752 p_amount IN NUMBER ) IS
3754 BEGIN
3755
3756 net_rev_unit_t( p_index ) := p_amount;
3757
3758 EXCEPTION
3759 WHEN OTHERS THEN
3760 debug( 'EXCEPTION: arp_credit_memo_module.update_net_rev_unit('
3761 || to_char(p_index) || ')' );
3762 RAISE;
3763
3764 END update_net_rev_unit;
3765
3766 ------------------------------------------------------------------------
3767
3768 PROCEDURE process_prorate_cm(
3769 p_select_rec IN OUT NOCOPY select_rec_type,
3770 p_start_index IN BINARY_INTEGER,
3771 p_end_index IN BINARY_INTEGER,
3772 p_cm_amt_remaining IN OUT NOCOPY NUMBER ) IS
3773
3774 l_amount NUMBER;
3775 l_period_cm_amount NUMBER;
3776 l_inv_line_amt_remaining NUMBER;
3777 l_prorate_total_amount NUMBER;
3778
3779 l_found_last_nonzero_period BOOLEAN := FALSE;
3780
3781 BEGIN
3782
3783 print_fcn_label('arp_credit_memo_module.process_prorate_cm()+' );
3784
3785 debug( ' p_start_index='||p_start_index, MSG_LEVEL_DEBUG );
3786 debug( ' p_end_index='||p_end_index, MSG_LEVEL_DEBUG );
3787 -----------------------------------------------------------------
3788 -- Loop thru revenue schedule
3789 -----------------------------------------------------------------
3790 FOR i IN REVERSE p_start_index..p_end_index LOOP
3791
3792 debug(' i='||i, MSG_LEVEL_DEBUG);
3793 debug(' p_cm_amt_remaining='||p_cm_amt_remaining,
3794 MSG_LEVEL_DEBUG);
3795 debug(' get_net_rev_gl_date(i)='||get_net_rev_gl_date(i),
3796 MSG_LEVEL_DEBUG);
3797 debug(' get_net_rev_amount(i)='||get_net_rev_amount(i),
3798 MSG_LEVEL_DEBUG);
3799
3800 -------------------------------------------------------------
3801 -- Look for the last non-zero period
3802 -------------------------------------------------------------
3803 IF( l_found_last_nonzero_period = FALSE ) THEN
3804
3805 IF( get_net_rev_amount(i) <> 0 ) THEN
3806
3807 -----------------------------------------------------
3808 -- Set flag
3809 -----------------------------------------------------
3810 debug( ' Found last nonzero period', MSG_LEVEL_DEBUG );
3811 l_found_last_nonzero_period := TRUE;
3812
3813 -----------------------------------------------------
3814 -- Compute remaining revenue for this line
3815 -----------------------------------------------------
3816 l_inv_line_amt_remaining :=
3817 get_net_rev_total_amount( p_start_index, i );
3818
3819 debug( ' l_inv_line_amt_remaining = ' ||
3820 l_inv_line_amt_remaining, MSG_LEVEL_DEBUG );
3821
3822 -----------------------------------------------------
3823 -- Update rule_start_date and rule_duration
3824 -----------------------------------------------------
3825 p_select_rec.rule_start_date :=
3826 get_net_rev_gl_date( p_start_index );
3827 p_select_rec.cm_acct_rule_duration := i - p_start_index + 1;
3828
3829
3830 IF( SIGN( l_inv_line_amt_remaining ) =
3831 SIGN( l_inv_line_amt_remaining +
3832 p_select_rec.cm_amount )) THEN
3833
3834 -----------------------------------------------------
3835 -- cm amount < amt remaining: use total cm amount
3836 -----------------------------------------------------
3837 l_prorate_total_amount := p_select_rec.cm_amount;
3838
3839 ELSE
3840
3841 -----------------------------------------------------
3842 -- cm amount > amt remaining: use amt remaining
3843 -----------------------------------------------------
3844 l_prorate_total_amount := - l_inv_line_amt_remaining;
3845
3846 END IF;
3847
3848 ELSE -- loop until you find last nonzero period
3849
3850 GOTO continue;
3851
3852 END IF;
3853
3854 END IF;
3855
3856 -------------------------------------------------------------
3857 -- Compute amount to credit and round
3858 -------------------------------------------------------------
3859 /***********
3860 l_period_cm_amount :=
3861 arp_util.CurrRound( l_prorate_total_amount *
3862 (get_net_rev_amount(i) /
3863 l_inv_line_amt_remaining),
3864 p_select_rec.currency_code );
3865 ***********/
3866 l_period_cm_amount := ( l_prorate_total_amount *
3867 (get_net_rev_amount(i) /
3868 l_inv_line_amt_remaining));
3869
3870
3871
3872 -------------------------------------------------------------
3873 -- Check for rounding error
3874 -------------------------------------------------------------
3875 IF( SIGN( p_cm_amt_remaining ) <>
3876 SIGN( p_cm_amt_remaining - l_period_cm_amount ) ) THEN
3877
3878 l_period_cm_amount := p_cm_amt_remaining;
3879
3880 END IF;
3881
3882
3883 debug( ' l_period_cm_amount = ' || l_period_cm_amount,
3884 MSG_LEVEL_DEBUG );
3885
3889 p_cm_amt_remaining := p_cm_amt_remaining - l_period_cm_amount;
3886 -------------------------------------------------------------
3887 -- Update cm amount remaining
3888 -------------------------------------------------------------
3890
3891 debug( ' p_cm_amt_remaining = ' || p_cm_amt_remaining,
3892 MSG_LEVEL_DEBUG );
3893
3894 -------------------------------------------------------------
3895 -- Update net revenue amount
3896 -------------------------------------------------------------
3897 update_net_revenue( i, get_net_rev_amount(i) + l_period_cm_amount );
3898
3899 IF( get_net_rev_unit(i) <> 0 ) THEN
3900
3901 update_net_rev_unit( i,
3902 get_net_rev_unit(i) +
3903 (l_period_cm_amount /
3904 p_select_rec.invoice_quantity) );
3905
3906 END IF;
3907
3908 ----------------------------------------------------------
3909 -- Insert into cm schedule array
3910 -- (mode=I, array)
3911 ----------------------------------------------------------
3912 process_cm_schedule( 'I',
3913 p_select_rec.customer_trx_line_id,
3914 get_net_rev_gl_date(i),
3915 l_period_cm_amount,
3916 p_select_rec.allow_not_open_flag,
3917 get_net_rev_dist_exists( i ),
3918 YES, -- insert_cma_flag
3919 YES, -- insert_offset_flag
3920 YES -- check_gl_date_flag
3921 );
3922
3923
3924 <<continue>>
3925
3926 null;
3927
3928 END LOOP;
3929
3930 print_fcn_label('arp_credit_memo_module.process_prorate_cm()-' );
3931
3932 EXCEPTION
3933 WHEN OTHERS THEN
3934 debug( 'EXCEPTION: arp_credit_memo_module.process_prorate_cm('
3935 || to_char(p_start_index) || ', '
3936 || to_char(p_end_index) || ')' );
3937 RAISE;
3938 END;
3939
3940 ------------------------------------------------------------------------
3941
3942 PROCEDURE process_lifo_cm(
3943 p_select_rec IN OUT NOCOPY select_rec_type,
3944 p_start_index IN BINARY_INTEGER,
3945 p_end_index IN BINARY_INTEGER,
3946 p_cm_amt_remaining IN OUT NOCOPY NUMBER ) IS
3947
3948 l_amount NUMBER;
3949 BEGIN
3950
3951 print_fcn_label('arp_credit_memo_module.process_lifo_cm()+' );
3952
3953 debug( ' p_start_index='||p_start_index, MSG_LEVEL_DEBUG );
3954 debug( ' p_end_index='||p_end_index, MSG_LEVEL_DEBUG );
3955
3956 FOR i IN REVERSE p_start_index..p_end_index LOOP
3957
3958 debug(' i='||i, MSG_LEVEL_DEBUG);
3959 debug(' p_cm_amt_remaining='||p_cm_amt_remaining, MSG_LEVEL_DEBUG);
3960 debug(' get_net_rev_gl_date(i)='||get_net_rev_gl_date(i),
3961 MSG_LEVEL_DEBUG);
3962 debug(' get_net_rev_amount(i)='||get_net_rev_amount(i),
3963 MSG_LEVEL_DEBUG);
3964 debug(' SIGN( get_net_rev_amount(i) )='||
3965 SIGN( get_net_rev_amount(i) ), MSG_LEVEL_DEBUG);
3966 debug(' SIGN( get_net_rev_unit(i) )='||SIGN( get_net_rev_unit(i) ),
3967 MSG_LEVEL_DEBUG);
3968
3969 /* Bug 2853961 - We were using sign(qty) to test for overapp.
3970 this does not work if inv qty is negative. Changed code
3971 to use true sign(amt) from p_select_rec instead.
3972
3973 Bug 3198525 - Revised p_select_rec sign variables to be numbers
3974 to resolve 10g certification issue. That meant that we no
3975 longer needed to convert the char signs to numbers. */
3976
3977 -------------------------------------------------------------
3978 -- If the net amount remaining in this period is zero, or
3979 -- the invoice is aleady overapplied in this period
3980 -- then go to previous period
3981 -------------------------------------------------------------
3982 IF( NOT ( get_net_rev_amount(i) = 0 OR
3983 SIGN( get_net_rev_amount(i) ) <>
3984 SIGN(p_select_rec.invoice_sign) ) ) THEN
3985
3986 IF( SIGN( get_net_rev_amount(i) ) =
3987 SIGN( get_net_rev_amount(i) + p_cm_amt_remaining ) ) THEN
3988
3989 ------------------------------------------------------------
3990 -- The array amount + the cm_remaining is the same sign
3991 -- as the array amount. Therefore, this is a partial
3992 -- application of this period and no more processing will
3993 -- be done for this credit memo line.
3994 -- Set array_amount = array_amount + cm_remaining
3995 ------------------------------------------------------------
3996 debug( ' Partial application case', MSG_LEVEL_DEBUG );
3997 debug(' CM amount = ' || p_cm_amt_remaining, MSG_LEVEL_DEBUG);
3998
3999 ------------------------------------------------------------
4000 -- Update net revenue amount
4001 ------------------------------------------------------------
4002 update_net_revenue( i, get_net_rev_amount(i) +
4003 p_cm_amt_remaining );
4004
4005 ------------------------------------------------------------
4006 -- Update rule_start_date, rule_duration
4007 -- Only increment if not (net=0 or overapp)
4011 p_select_rec.cm_acct_rule_duration + 1;
4008 ------------------------------------------------------------
4009 p_select_rec.rule_start_date := get_net_rev_gl_date( i );
4010 p_select_rec.cm_acct_rule_duration :=
4012
4013
4014 ----------------------------------------------------------
4015 -- Insert into cm schedule array
4016 -- (mode=I, array)
4017 ----------------------------------------------------------
4018 process_cm_schedule(
4019 'I',
4020 p_select_rec.customer_trx_line_id,
4021 get_net_rev_gl_date( i ),
4022 p_cm_amt_remaining,
4023 p_select_rec.allow_not_open_flag,
4024 get_net_rev_dist_exists( i ),
4025 YES, -- insert_cma_flag
4026 YES, -- insert_offset_flag
4027 YES -- check_gl_date_flag
4028 );
4029
4030
4031 p_cm_amt_remaining := 0;
4032
4033 GOTO done;
4034
4035 ELSE
4036 ------------------------------------------------------------
4037 -- The array amount + the cm_remaining is not the same sign
4038 -- as the array amount. Therefore, this is a full
4039 -- application of this period.
4040 -- cm_remaining = cm_remaining + array_amount
4041 ------------------------------------------------------------
4042 debug( ' Full application case', MSG_LEVEL_DEBUG );
4043 debug( ' CM amount = ' || -get_net_rev_amount(i),
4044 MSG_LEVEL_DEBUG );
4045
4046 ------------------------------------------------------------
4047 -- Update remaining_amount
4048 ------------------------------------------------------------
4049 p_cm_amt_remaining := p_cm_amt_remaining +
4050 get_net_rev_amount(i);
4051
4052 ------------------------------------------------------------
4053 -- Increment rule_duration
4054 ------------------------------------------------------------
4055 p_select_rec.cm_acct_rule_duration :=
4056 p_select_rec.cm_acct_rule_duration + 1;
4057
4058 ----------------------------------------------------------
4059 -- Insert into cm schedule array
4060 -- (mode=I, array)
4061 ----------------------------------------------------------
4062 process_cm_schedule(
4063 'I',
4064 p_select_rec.customer_trx_line_id,
4065 get_net_rev_gl_date( i ),
4066 -get_net_rev_amount( i ),
4067 p_select_rec.allow_not_open_flag,
4068 get_net_rev_dist_exists( i ),
4069 YES, -- insert_cma_flag
4070 YES, -- insert_offset_flag
4071 YES -- check_gl_date_flag
4072 );
4073
4074 ------------------------------------------------------------
4075 -- Update net revenue amount
4076 ------------------------------------------------------------
4077 update_net_revenue( i, 0 );
4078
4079 ------------------------------------------------------------
4080 -- If the remaining amount is zero, then all of the cm
4081 -- amount has been used up and we are done.
4082 -- Set the rule_start_date and accounting_rule_duration
4083 ------------------------------------------------------------
4084 IF( p_cm_amt_remaining = 0 ) THEN
4085
4086 p_select_rec.rule_start_date := get_net_rev_gl_date( i );
4087
4088 GOTO done;
4089
4090 END IF;
4091
4092 END IF;
4093
4094 END IF;
4095
4096 END LOOP;
4097
4098 <<done>>
4099 print_fcn_label('arp_credit_memo_module.process_lifo_cm()-' );
4100
4101 EXCEPTION
4102 WHEN OTHERS THEN
4103 debug( 'EXCEPTION: arp_credit_memo_module.process_lifo_cm('
4104 || to_char(p_start_index) || ', '
4105 || to_char(p_end_index) || ')' );
4106 RAISE;
4107 END;
4108
4109 ------------------------------------------------------------------------
4110
4111 PROCEDURE process_unit_cm(
4112 p_select_rec IN OUT NOCOPY select_rec_type,
4113 p_start_index IN BINARY_INTEGER,
4114 p_end_index IN BINARY_INTEGER,
4115 p_cm_amt_remaining IN OUT NOCOPY NUMBER ) IS
4116
4117 l_amount NUMBER;
4118 l_period_cm_amount NUMBER;
4119 l_last_period_ratio NUMBER;
4120
4121 BEGIN
4122
4123 print_fcn_label('arp_credit_memo_module.process_unit_cm()+' );
4124
4125 FOR i IN REVERSE p_start_index..p_end_index LOOP
4126
4127 debug(' i='||i, MSG_LEVEL_DEBUG);
4128 debug(' p_cm_amt_remaining='||p_cm_amt_remaining,
4129 MSG_LEVEL_DEBUG);
4130 debug(' get_net_rev_gl_date(i)='||get_net_rev_gl_date(i),
4131 MSG_LEVEL_DEBUG);
4132 debug(' get_net_rev_amount(i)='||get_net_rev_amount(i),
4133 MSG_LEVEL_DEBUG);
4134 debug(' get_net_rev_unit(i)='||get_net_rev_unit(i),
4135 MSG_LEVEL_DEBUG);
4136
4137 -------------------------------------------------------------
4138 -- If the net amount remaining in this period is zero, or
4139 -- the invoice is aleady overapplied in this period
4140 -- then go to previous period
4141 -------------------------------------------------------------
4142
4146
4143 /* Bug 2853961 - We were using sign(qty) to test for overapp.
4144 this does not work if inv qty is negative. Changed code
4145 to use true sign(amt) from p_select_rec instead.
4147 Bug 3198525 - Original code converted char sign to
4148 number. To avoid 10g cert issue, we revised sql
4149 and structure to store sign as number.*/
4150
4151 IF( NOT ( get_net_rev_amount(i) = 0 OR
4152 SIGN( get_net_rev_amount(i) ) <>
4153 SIGN(p_select_rec.invoice_sign) ) ) THEN
4154
4155 l_period_cm_amount :=
4156 arp_util.CurrRound( p_select_rec.cm_quantity *
4157 get_net_rev_unit(i),
4158 p_select_rec.currency_code );
4159
4160 -------------------------------------------------------------
4161 -- If invoice negative, make cm_amount positive
4162 -------------------------------------------------------------
4163 /* Bug 2853961 - The original logic here was based on the sign
4164 of the invoice line. This goes on the mistaken assumption
4165 that the unit_cost of the invoice line is always positive.
4166 When the unit cost goes negative, then the sign of the
4167 line might be negative or positive depending on the sign
4168 of the quantity (+,+ +; +,- -; -,+ -; -,- +)
4169
4170 The calculation of l_period_cm_amount above uses a quantity
4171 that has already been reversed and the unit cost (from the inv).
4172 This can result in false values if the unit cost of the original
4173 invoice was negative.
4174
4175 We now reverse the sign of l_period_cm_amount when the unit cost
4176 (from the invoice) is negative. */
4177
4178 IF( sign(get_net_rev_unit(i)) = -1 ) THEN
4179 debug(' reverse sign!');
4180 l_period_cm_amount := - l_period_cm_amount;
4181 END IF;
4182
4183 -------------------------------------------------------------
4184 -- Check if at last period
4185 -------------------------------------------------------------
4186 IF( i = p_end_index ) THEN
4187
4188
4189 ---------------------------------------------------------
4190 -- Get fractional part, if applicable
4191 ---------------------------------------------------------
4192 l_last_period_ratio :=
4193 p_select_rec.last_period_to_credit -
4194 TRUNC( p_select_rec.last_period_to_credit );
4195
4196 IF( l_last_period_ratio <> 0 ) THEN
4197
4198 l_period_cm_amount :=
4199 arp_util.CurrRound( l_period_cm_amount *
4200 l_last_period_ratio,
4201 p_select_rec.currency_code );
4202
4203 END IF;
4204
4205 END IF;
4206
4207
4208 -------------------------------------------------------------
4209 -- If remaining cm amount < amount to credit,
4210 -- use remaining cm amount for amount to credit
4211 -------------------------------------------------------------
4212 IF( SIGN( p_cm_amt_remaining ) <>
4213 SIGN( p_cm_amt_remaining - l_period_cm_amount ) ) THEN
4214
4215 l_period_cm_amount := p_cm_amt_remaining;
4216
4217 END IF;
4218
4219 -------------------------------------------------------------
4220 -- If net amount for this period < amount to credit,
4221 -- set amount to credit = - net_amount
4222 -------------------------------------------------------------
4223 IF( SIGN( get_net_rev_amount(i) ) <>
4224 SIGN( get_net_rev_amount(i) + l_period_cm_amount ) ) THEN
4225
4226 l_period_cm_amount := - get_net_rev_amount(i);
4227
4228 END IF;
4229
4230
4231 debug( ' l_period_cm_amount = ' || l_period_cm_amount,
4232 MSG_LEVEL_DEBUG );
4233 p_cm_amt_remaining := p_cm_amt_remaining - l_period_cm_amount;
4234
4235 -------------------------------------------------------------
4236 -- Update rule_start_date, rule_duration
4237 -------------------------------------------------------------
4238 p_select_rec.rule_start_date := get_net_rev_gl_date( i );
4239 p_select_rec.cm_acct_rule_duration :=
4240 p_select_rec.cm_acct_rule_duration + 1;
4241
4242 ----------------------------------------------------------
4243 -- Insert into cm schedule array
4244 -- (mode=I, array)
4245 ----------------------------------------------------------
4246 process_cm_schedule( 'I',
4247 p_select_rec.customer_trx_line_id,
4248 get_net_rev_gl_date( i ),
4249 l_period_cm_amount,
4250 p_select_rec.allow_not_open_flag,
4251 get_net_rev_dist_exists( i ),
4252 YES, -- insert_cma_flag
4253 YES, -- insert_offset_flag
4254 YES -- check_gl_date_flag
4255 );
4256
4257
4258 -------------------------------------------------------------
4259 -- Update net revenue amount
4260 -------------------------------------------------------------
4261 update_net_revenue( i, get_net_rev_amount(i) +
4262 l_period_cm_amount );
4263
4264
4265 IF( p_cm_amt_remaining = 0 ) THEN
4266 -----------------------------------------------------
4267 -- Credit exhausted, done
4271
4268 -----------------------------------------------------
4269 debug( ' Credit exhausted', MSG_LEVEL_DEBUG );
4270 EXIT;
4272 END IF;
4273
4274 END IF;
4275
4276 END LOOP;
4277
4278 print_fcn_label('arp_credit_memo_module.process_unit_cm()-' );
4279
4280 EXCEPTION
4281 WHEN OTHERS THEN
4282 debug( 'EXCEPTION: arp_credit_memo_module.process_unit_cm('
4283 || to_char(p_start_index) || ', '
4284 || to_char(p_end_index) || ')' );
4285 RAISE;
4286 END;
4287
4288
4289 ----------------------------------------------------------------------------
4290 --
4291 -- PROCEDURE NAME: process_line
4292 --
4293 -- DECSRIPTION:
4294 --
4295 -- ARGUMENTS:
4296 -- IN:
4297 -- system_info
4298 -- profile_info
4299 -- cm_control
4300 --
4301 -- IN/OUT:
4302 -- select_rec
4303 --
4304 -- OUT:
4305 --
4306 -- RETURNS:
4307 --
4308 -- NOTES:
4309 --
4310 -- HISTORY:
4311 --
4312 ----------------------------------------------------------------------------
4313 PROCEDURE process_line(
4314 p_system_info IN arp_trx_global.system_info_rec_type,
4315 p_profile_info IN arp_trx_global.profile_rec_type,
4316 p_cm_control IN control_rec_type,
4317 p_select_rec IN OUT NOCOPY select_rec_type ) IS
4318
4319 l_start_index BINARY_INTEGER;
4320 l_end_index BINARY_INTEGER;
4321 l_overapp_index BINARY_INTEGER;
4322 l_amount NUMBER;
4323 l_cm_amt_remaining NUMBER;
4324
4325 BEGIN
4326
4327 print_fcn_label('arp_credit_memo_module.process_line()+' );
4328
4329 -----------------------------------------------------------------------
4330 -- Initialize
4331 -----------------------------------------------------------------------
4332 l_cm_amt_remaining := p_select_rec.cm_amount;
4333 p_select_rec.cm_acct_rule_duration := 0;
4334
4335 -----------------------------------------------------------------------
4336 -- Get net revenue information for a line
4337 -----------------------------------------------------------------------
4338 load_net_revenue_schedule( p_system_info,
4339 p_profile_info,
4340 p_cm_control,
4341 p_select_rec.prev_cust_trx_line_id );
4342
4343 l_start_index := net_rev_start_index;
4344
4345 -----------------------------------------------------------------------
4346 -- If all of the invoice's gl_dates are not present in the
4347 -- net revenue array, then the invoice is a Release 9 invoice
4348 -- that has gl_dates in gl_periods that do not exist for the
4349 -- period_type referenced in its rule. In this case, we bomb
4350 -- and tell the users to define periods for all of the invoice's
4351 -- gl_dates.
4352 --
4353 -- Don't do this check if the imvoice is a Release 9 invoice with
4354 -- immediate rules, however.
4355 -----------------------------------------------------------------------
4356 IF( p_select_rec.inv_acct_rule_duration <> -1 AND
4357 net_rev_index <> p_select_rec.inv_acct_rule_duration AND
4358 p_select_rec.cm_amount <> 0 ) THEN
4359
4360 --
4361 -- Error: missing_periods
4362 --
4363 debug( ' raising missing_periods', MSG_LEVEL_DEBUG );
4364 g_error_buffer := MSG_MISSING_PERIODS;
4365 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
4366 RAISE missing_periods;
4367
4368 END IF;
4369 --
4370 -- Set the rule_end_date for the partial period CMs
4371 --
4372 IF p_select_rec.partial_period_flag = 'Y' THEN
4373 p_select_rec.rule_end_date := get_net_rev_gl_date( net_rev_index - 1);
4374 ELSE
4375 p_select_rec.rule_end_date := null;
4376 END IF;
4377
4378 -----------------------------------------------------------------------
4379 -- Set ending period to credit.
4380 -- If this is a unit credit memo, set ending_period to the last period
4381 -- to credit. If the last period to credit is not an integer, use
4382 -- CEIL( last period to credit)
4383 -----------------------------------------------------------------------
4384 IF( p_select_rec.credit_method_for_rules = UNIT ) THEN
4385
4386 l_end_index := CEIL( p_select_rec.last_period_to_credit ) - 1;
4387
4388 /* 4621029 - check for mismatch of unit_selling_price
4389 and raise exception or error if CM exceeds INV */
4390 IF ABS(p_select_rec.inv_unit_price) -
4391 ABS(p_select_rec.cm_unit_price) < 0
4392 THEN
4393 debug( ' raising cm_unit_overapp, ' ||
4394 p_select_rec.inv_unit_price || ' vs ' ||
4395 p_select_rec.cm_unit_price, MSG_LEVEL_DEBUG );
4396 g_error_buffer := MSG_CM_UNIT_OVERAPP;
4397 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
4398 RAISE cm_unit_overapp;
4399 END IF;
4400
4401 ELSE
4402
4403 l_end_index := net_rev_index - 1;
4404
4405 END IF;
4406
4407
4408
4409 -----------------------------------------------------------------------
4410 -- If the invoice and the credit memo amounts have the same sign,
4414 -----------------------------------------------------------------------
4411 -- put the total amount in the period that corresponds to the
4412 -- credit memo's rule start date.
4413 -- bug 745945 : added OR p_select_rec.invoice_sign = '0'
4415 IF( p_select_rec.invoice_sign = p_select_rec.cm_sign OR
4416 p_select_rec.cm_sign = 0 OR
4417 p_select_rec.invoice_sign = 0) THEN
4418
4419 debug( ' Overapplication case', MSG_LEVEL_DEBUG );
4420
4421 ------------------------------------------------------------
4422 -- Error if overapplications are not allowed.
4423 ------------------------------------------------------------
4424 IF( p_select_rec.allow_overapp_flag = NO AND
4425 p_select_rec.cm_sign <> 0 ) THEN
4426
4427 --
4428 -- Error: overapplication not allowed
4429 --
4430 debug( ' overapp_not_allowed', MSG_LEVEL_DEBUG );
4431 g_error_buffer := MSG_OVERAPP_NOT_ALLOWED;
4432 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
4433 RAISE overapp_not_allowed;
4434
4435 END IF;
4436
4437 --------------------------------------------------------------------
4438 -- Get the date of the period to put the overapplication in.
4439 -- For LIFO credit memo lines, this is the invoice line's
4440 -- rule start date.
4441 -- For Unit credit memo lines, it is the last period to credit.
4442 -- For Prorate credit memo lines, it is the credit memo's
4443 -- rule start date.
4444 --------------------------------------------------------------------
4445 IF( p_select_rec.credit_method_for_rules = LIFO ) THEN
4446
4447 l_overapp_index := l_start_index;
4448
4449 ELSIF( p_select_rec.credit_method_for_rules = UNIT ) THEN
4450
4451 l_overapp_index := l_end_index;
4452
4453 ELSIF( p_select_rec.credit_method_for_rules = PRORATE ) THEN
4454
4455 -----------------------------------------------------------------
4456 -- Find gl_date in net rev array which matches cm header gl_date
4457 -- (search backwards)
4458 -----------------------------------------------------------------
4459 IF( find_net_revenue( p_select_rec.prev_cust_trx_line_id,
4460 p_select_rec.cm_gl_date,
4461 l_overapp_index ) = FALSE ) THEN
4462
4463 p_select_rec.rule_start_date := p_select_rec.cm_gl_date;
4464 p_select_rec.cm_acct_rule_duration := 1;
4465
4466 ----------------------------------------------------------
4467 -- Add to cm schedule array: cm amounts only
4468 -- (mode=I, N)
4469 ----------------------------------------------------------
4470 process_cm_schedule( 'I',
4471 p_select_rec.customer_trx_line_id,
4472 p_select_rec.cm_gl_date,
4473 p_select_rec.cm_amount,
4474 p_select_rec.allow_not_open_flag,
4475 NO, -- insert_dist_flag
4476 YES, -- insert_cma_flag
4477 NO, -- insert_offset_flag
4478 NO -- check_gl_date_flag
4479 );
4480
4481 GOTO done;
4482
4483 END IF;
4484
4485 ELSE
4486
4487 ---------------------------------------------------------------
4488 -- ERROR: Invalid Credit Method For Rules
4489 ---------------------------------------------------------------
4490 debug( ' raising invalid_cm_method_for_rules', MSG_LEVEL_DEBUG );
4491
4492 g_error_buffer := 'Invalid Credit Method for rules';
4493 debug( 'EXCEPTION: '|| g_error_buffer, MSG_LEVEL_BASIC );
4494 RAISE invalid_cm_method_for_rules;
4495
4496 END IF;
4497
4498 /* Bug 3477990 */
4499 debug('setting pg_closed_period_exists to NULL 2');
4500 pg_closed_period_exists := NULL;
4501
4502 debug( ' l_overapp_index = ' || l_overapp_index, MSG_LEVEL_DEBUG );
4503
4504 ---------------------------------------------------------------
4505 -- Update net rev array: amount
4506 ---------------------------------------------------------------
4507 update_net_revenue( l_overapp_index,
4508 get_net_rev_amount( l_overapp_index ) +
4509 p_select_rec.cm_amount );
4510
4511 -------------------------------------------------------------------
4512 -- Update rule_start_date = net rev gl_date, acct_rule_duration = 1
4513 -------------------------------------------------------------------
4514 p_select_rec.rule_start_date := get_net_rev_gl_date( l_overapp_index );
4515 p_select_rec.cm_acct_rule_duration := 1;
4516
4517 ----------------------------------------------------------
4518 -- Update cm schedule array: (mode=T, array)
4519 ----------------------------------------------------------
4520 process_cm_schedule( 'U',
4521 p_select_rec.customer_trx_line_id,
4522 get_net_rev_gl_date( l_overapp_index ),
4523 p_select_rec.cm_amount,
4524 p_select_rec.allow_not_open_flag,
4525 get_net_rev_dist_exists( l_overapp_index ),
4526 YES, -- insert_cma_flag
4527 YES, -- insert_offset_flag
4528 YES -- check_gl_date_flag
4532
4529 );
4530
4531 GOTO done;
4533 ELSE -- inv and cm different signs
4534
4535 ---------------------------------------------------------------
4536 -- Release 9 optimization:
4537 -- If this CM is crediting an immediate Release 9 invoice
4538 -- whose rule_start_date is equal to its receivable gl_date,
4539 -- then no unearned or unbilled records exist.
4540 -- In this case, set the credit memo's gl_date to the cm's
4541 -- receivable gl_date.
4542 ---------------------------------------------------------------
4543 IF( p_select_rec.inv_acct_rule_duration = -1 ) THEN
4544
4545 ----------------------------------------------------------
4546 -- Insert into cm schedule array: (mode=I, F)
4547 ----------------------------------------------------------
4548 process_cm_schedule( 'I',
4549 p_select_rec.customer_trx_line_id,
4550 p_select_rec.cm_gl_date,
4551 p_select_rec.cm_amount,
4552 p_select_rec.allow_not_open_flag,
4553 YES, -- insert_dist_flag
4554 YES, -- insert_cma_flag
4555 YES, -- insert_offset_flag
4556 NO -- check_gl_date_flag
4557 );
4558
4559
4560 ---------------------------------------------------------------
4561 -- set rule start date = cm_gl_date
4562 -- rule_duration = 1
4563 ---------------------------------------------------------------
4564 p_select_rec.rule_start_date := p_select_rec.cm_gl_date;
4565 p_select_rec.cm_acct_rule_duration := 1;
4566
4567 GOTO done;
4568
4569 END IF;
4570
4571 /* Bug 4278110/4352354 - we were not resetting this variable
4572 prior to the process calls below (which in turn call
4573 process_cm_schedule. This was causing the module to
4574 raise a closed_periods error if the previous line
4575 was accounted entirely in closed periods. */
4576 debug('setting pg_closed_period_exists to NULL 3');
4577 pg_closed_period_exists := NULL;
4578
4579 ---------------------------------------------------------------
4580 -- Check credit method
4581 ---------------------------------------------------------------
4582 IF( p_select_rec.credit_method_for_rules = PRORATE ) THEN
4583
4584 process_prorate_cm( p_select_rec,
4585 l_start_index,
4586 l_end_index,
4587 l_cm_amt_remaining );
4588
4589 ELSIF( p_select_rec.credit_method_for_rules = LIFO ) THEN
4590
4591 process_lifo_cm( p_select_rec,
4592 l_start_index,
4593 l_end_index,
4594 l_cm_amt_remaining );
4595
4596 ELSIF( p_select_rec.credit_method_for_rules = UNIT ) THEN
4597
4598 process_unit_cm( p_select_rec,
4599 l_start_index,
4600 l_end_index,
4601 l_cm_amt_remaining );
4602
4603 ELSE
4604 ---------------------------------------------------------------
4605 -- ERROR: Invalid Credit Method For Rules
4606 ---------------------------------------------------------------
4607 debug( ' raising invalid_cm_method_for_rules', MSG_LEVEL_DEBUG );
4608
4609 g_error_buffer := 'Invalid Credit Method for rules';
4610 debug( 'EXCEPTION: '|| g_error_buffer, MSG_LEVEL_BASIC );
4611 RAISE invalid_cm_method_for_rules;
4612
4613 END IF;
4614
4615 debug( ' l_cm_amt_remaining = ' || l_cm_amt_remaining,
4616 MSG_LEVEL_DEBUG );
4617
4618 /* Bug 3477990 */
4619 debug('setting pg_closed_period_exists to NULL 4');
4620 pg_closed_period_exists := NULL;
4621
4622 IF( l_cm_amt_remaining <> 0 ) THEN
4623
4624 -------------------------------------------------------------
4625 -- Not all of the cm line amount has been applied to the
4626 -- invoice. Therefore, this is an overapplication or a
4627 -- rounding error correction.
4628 -------------------------------------------------------------
4629
4630 IF( p_select_rec.credit_method_for_rules = LIFO ) THEN
4631
4632 l_overapp_index := l_start_index;
4633
4634 ---------------------------------------------------------
4635 -- Update the rule start date for LIFO case
4636 ---------------------------------------------------------
4637 p_select_rec.rule_start_date :=
4638 get_net_rev_gl_date( l_overapp_index );
4639
4640 ELSE -- PRORATE / UNIT case
4641
4642 IF( find_net_revenue( p_select_rec.prev_cust_trx_line_id,
4643 p_select_rec.rule_start_date,
4644 l_overapp_index ) = FALSE ) THEN
4645
4646 -- Bug Fix 624157
4647 -- Do not raise an exception, instead populate rule_start_date
4648 -- with the credit memo gl_date and rule_durration = 1
4649 /**********************************************************
4650 --
4651 -- ERROR: No net revenue
4652 --
4653 debug( ' raising no_net_revenue',
4654 MSG_LEVEL_DEBUG );
4655
4659 *********************************************************/
4656 g_error_buffer := MSG_NO_NET_REVENUE;
4657 debug( 'EXCEPTION '|| g_error_buffer, MSG_LEVEL_BASIC );
4658 RAISE no_net_revenue;
4660
4661 p_select_rec.rule_start_date := p_select_rec.cm_gl_date;
4662 p_select_rec.cm_acct_rule_duration := 1;
4663
4664 -- End Bug Fix
4665
4666 END IF;
4667
4668 END IF;
4669
4670 -------------------------------------------------------------
4671 -- Bug 348948 :
4672 -- the following assignment statement will force all rounding
4673 -- differences to be put into the last period
4674 -------------------------------------------------------------
4675 l_overapp_index := l_end_index;
4676
4677 -------------------------------------------------------------
4678 -- Update net rev array
4679 -------------------------------------------------------------
4680 update_net_revenue( l_overapp_index,
4681 get_net_rev_amount( l_overapp_index ) +
4682 l_cm_amt_remaining );
4683
4684 ----------------------------------------------------------
4685 -- Update cm schedule array: (mode=U, array)
4686 ----------------------------------------------------------
4687 process_cm_schedule( 'U',
4688 p_select_rec.customer_trx_line_id,
4689 get_net_rev_gl_date( l_overapp_index ),
4690 l_cm_amt_remaining,
4691 p_select_rec.allow_not_open_flag,
4692 get_net_rev_dist_exists( l_overapp_index ),
4693 YES, -- insert_cma_flag
4694 YES, -- insert_offset_flag
4695 YES -- check_gl_date_flag
4696 );
4697
4698 END IF;
4699
4700
4701 END IF; -- if inv and cm same sign
4702
4703 <<done>>
4704
4705 /* 4357664 - pg_closed not getting reset for some overapp cases */
4706 debug('Resetting pg_closed_period_exists to NULL 5');
4707 pg_closed_period_exists := NULL;
4708 /* End 4357764 */
4709
4710 print_fcn_label('arp_credit_memo_module.process_line()-' );
4711
4712 EXCEPTION
4713 WHEN OTHERS THEN
4714 debug( 'EXCEPTION: arp_credit_memo_module.process_line()' );
4715 RAISE;
4716 END process_line;
4717
4718 ----------------------------------------------------------------------------
4719 --
4720 -- PROCEDURE NAME: credit_rule_transactions
4721 --
4722 -- DECSRIPTION:
4723 --
4724 -- ARGUMENTS:
4725 -- IN:
4726 -- system_info
4727 -- profile_info
4728 -- cm_control
4729 --
4730 -- IN/OUT:
4731 -- rule_start_date
4732 -- accounting_rule_duration
4733 --
4734 -- OUT:
4735 --
4736 -- RETURNS:
4737 --
4738 -- NOTES:
4739 --
4740 -- HISTORY:
4741 -- 27-SEP-2005 MRAYMOND 4633761 - Added conditional call to
4742 -- write_cm_sched and clear_cm_sched
4743 -- based on prev_ctlid
4744 ----------------------------------------------------------------------------
4745 PROCEDURE credit_rule_transactions(
4746 p_system_info IN arp_trx_global.system_info_rec_type,
4747 p_profile_info IN arp_trx_global.profile_rec_type,
4748 p_cm_control IN control_rec_type,
4749 p_failure_count IN OUT NOCOPY NUMBER,
4750 p_rule_start_date IN OUT NOCOPY DATE,
4751 p_accounting_rule_duration IN OUT NOCOPY NUMBER ) IS
4752
4753 l_ignore INTEGER;
4754 l_first_fetch BOOLEAN;
4755
4756 l_select_rec select_rec_type;
4757 l_null_rec CONSTANT select_rec_type := l_select_rec;
4758
4759
4760 BEGIN
4761
4762 print_fcn_label('arp_credit_memo_module.credit_rule_transactions()+' );
4763
4764 ---------------------------------------------------------------
4765 -- Create dynamic sql
4766 ---------------------------------------------------------------
4767 debug( ' Creating dynamic sql', MSG_LEVEL_DEBUG );
4768
4769 build_rule_sql(p_system_info,
4770 p_profile_info,
4771 p_cm_control,
4772 rule_select_cm_lines_c,
4773 rule_update_cm_lines_c,
4774 rule_insert_dist_c,
4775 rule_insert_cma_c );
4776
4777 define_select_columns( rule_select_cm_lines_c, l_select_rec );
4778
4779
4780 ---------------------------------------------------------------
4781 -- Create dynamic sql for net revenue
4782 ---------------------------------------------------------------
4783 debug( ' Creating dynamic sql', MSG_LEVEL_DEBUG );
4784
4785 build_net_revenue_sql(p_system_info,
4786 p_profile_info,
4787 p_cm_control,
4788 net_revenue_line_c );
4789
4790 BEGIN
4791 debug( ' Defining columns for net_revenue_line_c', MSG_LEVEL_BASIC );
4792
4793 dbms_sql.define_column( net_revenue_line_c, 1,
4797 dbms_sql.define_column( net_revenue_line_c, 3,
4794 net_revenue_rec.customer_trx_line_id );
4795 dbms_sql.define_column( net_revenue_line_c, 2,
4796 net_revenue_rec.gl_date );
4798 net_revenue_rec.amount );
4799 dbms_sql.define_column( net_revenue_line_c, 4,
4800 net_revenue_rec.net_unit_price );
4801 dbms_sql.define_column( net_revenue_line_c, 5,
4802 net_revenue_rec.inv_dist_exists, 1 );
4803 EXCEPTION
4804 WHEN OTHERS THEN
4805 debug( 'EXCEPTION: Error defining columns for net_revenue_line_c' );
4806 RAISE;
4807 END;
4808
4809 /* bug fix 956189 */
4810 IF( p_cm_control.customer_trx_line_id IS NOT NULL ) THEN
4811 BEGIN
4812 dbms_sql.bind_variable( rule_select_cm_lines_c,
4813 'cm_cust_trx_line_id',
4814 p_cm_control.customer_trx_line_id );
4815 EXCEPTION
4816 WHEN OTHERS THEN
4817 debug( 'EXCEPTION: Error in binding rule_select_cm_lines_c' );
4818 RAISE;
4819 END;
4820
4821 ELSE
4822 IF( p_cm_control.customer_trx_id IS NOT NULL ) THEN
4823 BEGIN
4824 dbms_sql.bind_variable( rule_select_cm_lines_c,
4825 'cm_customer_trx_id',
4826 p_cm_control.customer_trx_id );
4827 EXCEPTION
4828 WHEN OTHERS THEN
4829 debug( 'EXCEPTION: Error in binding rule_select_cm_lines_c' );
4830 RAISE;
4831 END;
4832 ELSE /* bug 3525326 */
4833 BEGIN
4834 dbms_sql.bind_variable( rule_select_cm_lines_c,
4835 'request_id',
4836 p_cm_control.request_id );
4837 EXCEPTION
4838 WHEN OTHERS THEN
4839 debug( 'EXCEPTION: Error in binding rule_select_cm_lines_c' );
4840 RAISE;
4841 END;
4842 END IF;
4843 END IF;
4844
4845 /* bug 3525326 */
4846 IF (p_cm_control.request_id IS NOT NULL) THEN
4847 BEGIN
4848 dbms_sql.bind_variable( rule_insert_cma_c,
4849 'request_id',
4850 p_cm_control.request_id );
4851 EXCEPTION
4852 WHEN OTHERS THEN
4853 debug( 'EXCEPTION: Error in binding rule_insert_cma_c' );
4854 RAISE;
4855 END;
4856 END IF;
4857
4858 ---------------------------------------------------------------
4859 -- Populate ar_revenue_assignments_gt
4860 -- per bug 2347001
4861 ---------------------------------------------------------------
4862 BEGIN
4863 /* Increment session ID */
4864 g_session_id := g_session_id + 1;
4865
4866 /* Now populate gt table with unique session id */
4867 arp_revenue_assignments.build_for_credit(
4868 g_session_id,
4869 system_info.period_set_name,
4870 p_cm_control.request_id,
4871 p_cm_control.customer_trx_id,
4872 p_cm_control.customer_trx_line_id
4873 );
4874
4875 EXCEPTION
4876 WHEN OTHERS THEN
4877 debug( 'EXCEPTION: Error populating ar_revenue_assignment_gt' );
4878 RAISE;
4879 END;
4880
4881
4882 ---------------------------------------------------------------
4883 -- Execute sql
4884 ---------------------------------------------------------------
4885 debug( ' Executing select sql', MSG_LEVEL_DEBUG );
4886
4887 BEGIN
4888 l_ignore := dbms_sql.execute( rule_select_cm_lines_c );
4889
4890 EXCEPTION
4891 WHEN OTHERS THEN
4892 debug( 'EXCEPTION: Error executing select cm lines sql' );
4893 RAISE;
4894 END;
4895
4896 l_first_fetch := TRUE;
4897
4898 ---------------------------------------------------------------
4899 -- Fetch rows
4900 ---------------------------------------------------------------
4901 debug( ' Fetching select stmt', MSG_LEVEL_DEBUG );
4902
4903 BEGIN
4904 LOOP
4905
4906
4907 IF dbms_sql.fetch_rows( rule_select_cm_lines_c ) > 0 THEN
4908
4909 debug(' fetched a row', MSG_LEVEL_DEBUG );
4910
4911 l_first_fetch := FALSE;
4912
4913 ------------------------------------------------------------
4914 -- Get column values
4915 ------------------------------------------------------------
4916 l_select_rec := l_null_rec;
4917 get_select_column_values( rule_select_cm_lines_c,
4918 l_select_rec );
4919
4920 dump_select_rec( l_select_rec );
4921
4922 ELSE
4923 ------------------------------------------------------------
4924 -- No more rows to fetch
4925 ------------------------------------------------------------
4926 debug( ' Done fetching', MSG_LEVEL_DEBUG );
4927
4928 IF( l_first_fetch ) THEN
4929 --------------------------------------------------------
4933 EXIT;
4930 -- No rows selected
4931 --------------------------------------------------------
4932 debug( ' raising NO_DATA_FOUND', MSG_LEVEL_DEBUG );
4934 END IF;
4935
4936 EXIT;
4937 END IF;
4938
4939 ------------------------------------------------------------
4940 -- Process the line
4941 ------------------------------------------------------------
4942 DECLARE
4943 PROCEDURE insert_error_if_autoinv IS
4944 BEGIN
4945 insert_into_error_table(
4946 l_select_rec.customer_trx_line_id,
4947 g_error_buffer,
4948 NULL );
4949 END;
4950
4951 BEGIN
4952 /* 4633761 - Write to DB and clear when the
4953 invoice line changes */
4954 IF g_prev_ctlid <> l_select_rec.prev_cust_trx_line_id
4955 THEN
4956 debug('write arrays to db and clear arrays...');
4957 g_prev_ctlid := l_select_rec.prev_cust_trx_line_id;
4958 write_cm_sched_to_table;
4959 clear_cm_sched_tables;
4960 END IF;
4961
4962 process_line( p_system_info,
4963 p_profile_info,
4964 p_cm_control,
4965 l_select_rec );
4966 EXCEPTION
4967 WHEN missing_periods OR
4968 overapp_not_allowed OR
4969 invalid_cm_method_for_rules OR
4970 no_net_revenue OR
4971 cm_unit_overapp THEN
4972
4973 IF( p_cm_control.request_id IS NOT NULL ) THEN
4974
4975 p_failure_count := p_failure_count + 1;
4976 insert_error_if_autoinv;
4977
4978 ELSE
4979 RAISE;
4980 END IF;
4981
4982 WHEN OTHERS THEN
4983 RAISE;
4984 END;
4985
4986 ------------------------------------------------------------
4987 -- Bind variables for update
4988 ------------------------------------------------------------
4989 BEGIN
4990 debug(' Binding variables for update', MSG_LEVEL_DEBUG);
4991 debug(' customer_trx_line_id='||
4992 l_select_rec.customer_trx_line_id,
4993 MSG_LEVEL_DEBUG);
4994 debug(' rule_start_date='||
4995 to_char(l_select_rec.rule_start_date),
4996 MSG_LEVEL_DEBUG);
4997 debug(' rule_end_date='||
4998 to_char(l_select_rec.rule_end_date),
4999 MSG_LEVEL_DEBUG);
5000 debug(' cm_acct_rule_duration='||
5001 l_select_rec.cm_acct_rule_duration,
5002 MSG_LEVEL_DEBUG);
5003
5004 dbms_sql.bind_variable( rule_update_cm_lines_c,
5005 'rule_start_date',
5006 l_select_rec.rule_start_date );
5007 dbms_sql.bind_variable( rule_update_cm_lines_c,
5008 'rule_end_date',
5009 l_select_rec.rule_end_date );
5010 dbms_sql.bind_variable( rule_update_cm_lines_c,
5011 'cm_acct_rule_duration',
5012 l_select_rec.cm_acct_rule_duration );
5013 dbms_sql.bind_variable( rule_update_cm_lines_c,
5014 'credit_method',
5015 l_select_rec.credit_method_for_rules);
5016 dbms_sql.bind_variable( rule_update_cm_lines_c,
5017 'last_period_to_credit',
5018 l_select_rec.last_period_to_credit );
5019 dbms_sql.bind_variable( rule_update_cm_lines_c,
5020 'customer_trx_line_id',
5021 l_select_rec.customer_trx_line_id );
5022 EXCEPTION
5023 WHEN OTHERS THEN
5024 debug('EXCEPTION: Error in binding rule_update_cm_lines_c');
5025 RAISE;
5026 END;
5027
5028 -----------------------------------------------------------
5029 -- Execute the update
5030 -----------------------------------------------------------
5031 debug( ' Updating lines', MSG_LEVEL_DEBUG );
5032
5033 BEGIN
5034 l_ignore := dbms_sql.execute( rule_update_cm_lines_c );
5035
5036 debug( to_char(l_ignore) || ' row(s) updated',
5037 MSG_LEVEL_DEBUG );
5038
5039 EXCEPTION
5040 WHEN OTHERS THEN
5041 debug( 'EXCEPTION: Error executing update lines stmt' );
5042 RAISE;
5043 END;
5044
5045
5046 -----------------------------------------------------------
5047 -- Return the rule_start_date and acct rule duration
5048 -- derived if calling the CM module at the line level
5049 -----------------------------------------------------------
5050 IF( p_cm_control.customer_trx_line_id IS NOT NULL ) THEN
5051
5052 p_rule_start_date := l_select_rec.rule_start_date;
5053 p_accounting_rule_duration :=
5054 l_select_rec.cm_acct_rule_duration;
5055 END IF;
5056
5057
5058 END LOOP;
5059
5063 write_cm_sched_to_table;
5060 -----------------------------------------------------------
5061 -- Flush out remaining data in cm sched tables to disk
5062 -----------------------------------------------------------
5064 clear_cm_sched_tables;
5065
5066 EXCEPTION
5067 WHEN NO_DATA_FOUND THEN
5068 RAISE;
5069 WHEN OTHERS THEN
5070 debug( 'EXCEPTION: Error fetching select cm lines' );
5071 RAISE;
5072
5073 END;
5074
5075
5076
5077 print_fcn_label('arp_credit_memo_module.credit_rule_transactions()-' );
5078
5079 EXCEPTION
5080 WHEN NO_DATA_FOUND THEN
5081 RAISE;
5082 WHEN OTHERS THEN
5083 debug( 'EXCEPTION: arp_credit_memo_module.credit_rule_transactions()');
5084 RAISE;
5085
5086 END credit_rule_transactions;
5087
5088
5089 ----------------------------------------------------------------------------
5090 --
5091 -- PROCEDURE NAME: credit_transactions_ins_mode
5092 --
5093 -- DECSRIPTION:
5094 -- Main internal procedure which credits transactions
5095 --
5096 -- ARGUMENTS:
5097 -- IN:
5098 -- customer_trx_id
5099 -- customer_trx_line_id
5100 -- prev_customer_trx_id
5101 -- prev_cust_trx_line_id
5102 -- request_id
5103 --
5104 -- IN/OUT:
5105 -- failure_count
5106 -- rule_start_date
5107 -- accounting_rule_duration
5108 --
5109 -- OUT:
5110 --
5111 -- NOTES:
5112 -- Raises the exception arp_credit_memo_module.no_ccid if autoaccounting
5113 -- could not derive a valid code combination. The public variable
5114 -- g_error_buffer is populated for more information.
5115 --
5116 -- Raises the exception NO_DATA_FOUND if no rows were selected for
5117 -- processing.
5118 --
5119 -- Exception raised if Oracle error.
5120 -- App_exception is raised for all other fatal errors and a message
5121 -- is put on the AOL stack. The public variable g_error_buffer is
5122 -- populated for both types of errors.
5123 --
5124 --
5125 -- HISTORY:
5126 --
5127 ----------------------------------------------------------------------------
5128 PROCEDURE credit_transactions_ins_mode(
5129 p_customer_trx_id IN NUMBER,
5130 p_customer_trx_line_id IN NUMBER,
5131 p_prev_customer_trx_id IN NUMBER,
5132 p_prev_cust_trx_line_id IN NUMBER,
5133 p_request_id IN NUMBER,
5134 p_failure_count IN OUT NOCOPY NUMBER,
5135 p_rule_start_date IN OUT NOCOPY DATE,
5136 p_accounting_rule_duration IN OUT NOCOPY NUMBER ,
5137 p_run_autoaccounting_flag IN BOOLEAN
5138 ) IS
5139
5140 l_ignore INTEGER;
5141
5142 l_cm_control control_rec_type;
5143 l_null_rec CONSTANT control_rec_type := l_cm_control;
5144
5145 l_rule_flag VARCHAR2(1) := NO;
5146
5147 l_ccid BINARY_INTEGER;
5148 l_concat_segments VARCHAR2(1000);
5149
5150 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
5151
5152 rows NUMBER := 0;
5153
5154 l_result NUMBER;
5155 NO_REC_OFFSET_EXCEPTION EXCEPTION;
5156 BEGIN
5157
5158 print_fcn_label('arp_credit_memo_module.credit_transactions_ins_mode()+' );
5159
5160 ------------------------------
5161 -- Populate control record
5162 ------------------------------
5163 l_cm_control := l_null_rec;
5164
5165 l_cm_control.customer_trx_id := p_customer_trx_id;
5166 l_cm_control.customer_trx_line_id := p_customer_trx_line_id;
5167 l_cm_control.prev_customer_trx_id := p_prev_customer_trx_id;
5168 l_cm_control.prev_cust_trx_line_id := p_prev_cust_trx_line_id;
5169 l_cm_control.request_id := p_request_id;
5170
5171 SAVEPOINT ar_credit_memo_module;
5172
5173 --------------------------------------------------------------------
5174 -- Check parameters
5175 --------------------------------------------------------------------
5176 IF( ( p_customer_trx_id IS NULL AND
5177 p_customer_trx_line_id IS NULL AND
5178 p_request_id IS NULL )
5179 OR
5180 ( p_request_id IS NOT NULL AND
5181 ( p_customer_trx_id IS NOT NULL OR
5182 p_customer_trx_line_id IS NOT NULL OR
5183 p_prev_cust_trx_line_id IS NOT NULL )
5184 )
5185 ) THEN
5186
5187 ----------------------------------------------------------------
5188 -- Invalid parameters
5189 ----------------------------------------------------------------
5190 debug( ' raising invalid_parameters', MSG_LEVEL_DEBUG );
5191
5192 g_error_buffer := MSG_INVALID_PARAMETERS;
5193 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
5194 RAISE invalid_parameters;
5195
5196 END IF;
5197
5198 --------------------------------------------------------------------
5199 -- For regular CMs , lock the corresponding invoice payment schedule
5200 -- before processing the CM ( Bug Fix : 1121920 )
5201 --------------------------------------------------------------------
5202
5203 IF p_request_id IS NOT NULL THEN
5204
5205 DECLARE
5206
5207 -- Get all the CMs being processed in this run
5208
5209 CURSOR int_regular_cms IS
5210 SELECT ps.payment_schedule_id,
5214 FROM ra_interface_lines int,
5211 int.previous_customer_trx_id,
5212 int.interface_line_id,
5213 ps.selected_for_receipt_batch_id
5215 ar_payment_schedules ps
5216 WHERE int.request_id = p_request_id
5217 AND int.previous_customer_trx_id IS NOT NULL
5218 AND int.previous_customer_trx_id = ps.customer_trx_id ;
5219
5220 l_locked VARCHAR2(1) := 'N' ;
5221 l_interface_line_id NUMBER ;
5222
5223 PROCEDURE insert_errors(p_selected_for_rcpt_batch_id IN NUMBER) IS
5224 l_jgzz_product_code VARCHAR2(100);
5225 BEGIN
5226 debug( ' inv_locked_by_another_session ', MSG_LEVEL_DEBUG );
5227 l_jgzz_product_code := AR_GDF_VALIDATION.is_jg_installed;
5228 if (l_jgzz_product_code is not null) and
5229 (p_selected_for_rcpt_batch_id = -999) then
5230 g_error_buffer := MSG_INV_LOCKED_BY_JL;
5231 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC);
5232 insert_into_error_table( l_interface_line_id ,
5233 g_error_buffer,
5234 NULL ) ;
5235 else
5236 g_error_buffer := MSG_INV_LOCKED ;
5237 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
5238 insert_into_error_table( l_interface_line_id ,
5239 g_error_buffer,
5240 NULL ) ;
5241 end if;
5242 END ;
5243
5244
5245 BEGIN
5246
5247 FOR i IN int_regular_cms
5248 LOOP
5249 l_interface_line_id := i.interface_line_id ;
5250
5251 BEGIN
5252 SELECT 'Y'
5253 INTO l_locked
5254 FROM ar_payment_schedules
5255 WHERE payment_schedule_id = i.payment_schedule_id
5256 --AND selected_for_receipt_batch_id IS NULL /* Bug fix 3142217 */
5257 --Commented The Above Line And Added The Following 3 Lines For Bug Fix 6339084
5258 /*Bug Fix 6339084 Starts */
5259 AND (selected_for_receipt_batch_id IS NULL
5260 OR (selected_for_receipt_batch_id IS NOT NULL
5261 AND NVL(global_attribute20,'~XX~X') = 'COLLECTION'))
5262 /*Ends*/
5263
5264 FOR UPDATE OF payment_schedule_id NOWAIT ;
5265
5266
5267 EXCEPTION
5268 WHEN NO_DATA_FOUND THEN
5269 debug( 'EXCEPTION: Autorec is locking the invoice');
5270 p_failure_count := p_failure_count + 1 ;
5271 insert_errors(i.selected_for_receipt_batch_id);
5272
5273 WHEN inv_locked_by_another_session THEN
5274
5275 p_failure_count := p_failure_count + 1 ;
5276 insert_errors(i.selected_for_receipt_batch_id);
5277
5278 WHEN OTHERS THEN
5279 debug( 'EXCEPTION: Error locking invoice payment schedule');
5280 RAISE ;
5281 END ;
5282
5283 END LOOP ;
5284
5285 END ;
5286 END IF;
5287
5288
5289 --------------------------------------------------------------------
5290 -- Call autoaccounting to create account sets
5291 --------------------------------------------------------------------
5292 IF ( p_run_autoaccounting_flag = TRUE)
5293 THEN
5294 BEGIN
5295 arp_auto_accounting.do_autoaccounting(
5296 I, -- mode
5297 'ALL', -- account_class
5298 p_customer_trx_id,
5299 p_customer_trx_line_id,
5300 NULL, -- salesrep_id
5301 p_request_id,
5302 NULL, -- gl_date
5303 NULL, -- original_gl_date
5304 NULL, -- total_trx_amount
5305 NULL, -- passed_ccid
5306 'N', -- force_account_set_no
5307 NULL, -- cust_trx_type_id
5308 NULL, -- primary_salesrep_id
5309 NULL, -- inventory_item_id
5310 NULL, -- memo_line_id
5311 l_ccid, -- ccid
5312 l_concat_segments, -- concat_segments
5313 p_failure_count );
5314 EXCEPTION
5315 WHEN arp_auto_accounting.no_ccid THEN
5316 g_error_buffer := arp_auto_accounting.g_error_buffer;
5317 RAISE no_ccid;
5318 WHEN NO_DATA_FOUND THEN
5319 debug( ' arp_auto_accounting raises NO_DATA_FOUND',
5320 MSG_LEVEL_DEBUG );
5321 NULL; -- ignore this exception
5322 END;
5323
5324 /* Check for header level rounding and create model ROUND row if
5325 one is needed */
5326
5327 IF (arp_global.sysparam.TRX_HEADER_LEVEL_ROUNDING = 'Y')
5328 THEN
5329
5330 DECLARE
5331
5332 rows_processed NUMBER;
5333 error_message VARCHAR2(255);
5334 BEGIN
5335
5336 IF (arp_rounding.insert_round_records(
5337 p_request_id,
5338 p_customer_trx_id,
5339 rows_processed,
5343 'CM', -- this doesn't matter!
5340 error_message,
5341 0,
5342 0,
5344 arp_global.sysparam.TRX_HEADER_ROUND_CCID) <> 0)
5345 THEN
5346 debug('arp_rounding.insert_round_rows returned FALSE');
5347 debug(' error: ' || error_message);
5348 END IF;
5349
5350 EXCEPTION
5351 WHEN OTHERS THEN
5352 debug('arp_rounding.insert_round_records raised EXCEPTION');
5353 debug(' error: ' || error_message);
5354
5355 /*Note that this exception will not halt the program
5356 and ultimately, line-level rounding will be enforced
5357 on the credit memo. */
5358
5359 END;
5360
5361 END IF;
5362
5363
5364 END IF;
5365
5366 --------------------------------------------------------------------
5367 -- If the request_id was not specified, find out NOCOPY if CM uses rules
5368 --------------------------------------------------------------------
5369 IF( p_customer_trx_id IS NOT NULL ) THEN
5370
5371 BEGIN
5372
5373 SELECT decode( trx.invoicing_rule_id, null, 'N', 'Y')
5374 INTO l_rule_flag
5375 FROM ra_customer_trx trx
5376 WHERE trx.customer_trx_id = p_customer_trx_id;
5377
5378 EXCEPTION
5379 WHEN OTHERS THEN
5380 debug( 'EXCEPTION: Error in selecting into l_rule_flag' );
5381
5382 RAISE;
5383
5384 END;
5385
5386 END IF;
5387
5388 debug( ' l_rule_flag=' || l_rule_flag, MSG_LEVEL_DEBUG);
5389
5390 -----------------------------------
5391 -- Credit non-rule transactions
5392 -----------------------------------
5393 IF( profile_info.use_inv_acct_for_cm_flag = YES AND
5394 l_rule_flag = NO AND
5395 ( p_prev_customer_trx_id IS NOT NULL OR
5396 p_request_id IS NOT NULL )
5397 ) THEN
5398
5399 credit_nonrule_transactions( system_info,
5400 profile_info,
5401 l_cm_control );
5402 END IF;
5403
5404
5405 ---------------------------------
5406 -- Credit rule transactions
5407 ---------------------------------
5408 IF( l_rule_flag = YES OR p_request_id IS NOT NULL ) THEN
5409
5410 /* Bug 2535023 - set rec_offset_flags on older invoices
5411 before we attempt to clone the distributions */
5412 IF (profile_info.use_inv_acct_for_cm_flag = YES) THEN
5413 IF (p_request_id is NOT NULL) THEN
5414 arp_rounding.set_rec_offset_flag(null, p_request_id, l_result);
5415 ELSE
5416 arp_rounding.set_rec_offset_flag(p_prev_customer_trx_id, null, l_result);
5417 END IF;
5418
5419 /* 6782405 - Check result of set_rec_offset call and raise exception if
5420 it is unsuccessful
5421
5422 This particular call is only important for old transactions that
5423 have been through Rev rec. New ones (that have not) won't
5424 do anything in this call and will return l_result of zero
5425 since there was nothing to be done */
5426 IF l_result = -1
5427 THEN
5428 RAISE NO_REC_OFFSET_EXCEPTION;
5429 END IF;
5430
5431 END IF;
5432
5433 credit_rule_transactions( system_info,
5434 profile_info,
5435 l_cm_control,
5436 p_failure_count,
5437 p_rule_start_date,
5438 p_accounting_rule_duration );
5439
5440 END IF;
5441
5442 /* Bug 4029814 - removed followon MRC call. We no longer
5443 update the gl_dist_id this late. MRC calls will have to occur
5444 inline */
5445
5446 close_cursors;
5447
5448 print_fcn_label('arp_credit_memo_module.credit_transactions_ins_mode()-' );
5449
5450 EXCEPTION
5451 WHEN NO_REC_OFFSET_EXCEPTION THEN
5452 /* set_rec_offset_flag executed and was unable to set
5453 the flag even though none were set, so stopping to prevent
5454 data corruption */
5455 close_cursors;
5456 debug( 'EXCEPTION: set_rof - credit_transactions_ins_mode('
5457 || to_char(p_customer_trx_id) || ', '
5458 || to_char(p_customer_trx_line_id) || ', '
5459 || to_char(p_prev_customer_trx_id) || ', '
5460 || to_char(p_prev_cust_trx_line_id) || ', '
5461 || to_char(p_request_id) || ')' );
5462 close_cursors;
5463 ROLLBACK TO ar_credit_memo_module;
5464 WHEN no_ccid OR NO_DATA_FOUND THEN
5465
5466 close_cursors;
5467
5468 IF( p_request_id IS NOT NULL ) THEN
5469
5470 NULL; -- Don't raise for Autoinvoice,
5471 -- otherwise the IN/OUT variables
5472 -- ccid, concat_segments and failure_count
5473 -- do not get populated.
5474 ELSE
5475 RAISE;
5476 END IF;
5477
5478 WHEN OTHERS THEN
5479 debug( 'EXCEPTION: arp_credit_memo_module.credit_transactions_ins_mode('
5483 || to_char(p_prev_cust_trx_line_id) || ', '
5480 || to_char(p_customer_trx_id) || ', '
5481 || to_char(p_customer_trx_line_id) || ', '
5482 || to_char(p_prev_customer_trx_id) || ', '
5484 || to_char(p_request_id) || ')' );
5485
5486 close_cursors;
5487 ROLLBACK TO ar_credit_memo_module;
5488
5489 IF( sqlcode = 1 ) THEN
5490 ----------------------------------------------------------------
5491 -- User-defined exception
5492 ----------------------------------------------------------------
5493 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
5494 FND_MESSAGE.set_token( 'GENERIC_TEXT', g_error_buffer );
5495 APP_EXCEPTION.raise_exception;
5496
5497 ELSE
5498 ----------------------------------------------------------------
5499 -- Oracle error
5500 ----------------------------------------------------------------
5501 g_error_buffer := SQLERRM;
5502
5503 RAISE;
5504
5505 END IF;
5506
5507 END credit_transactions_ins_mode;
5508
5509 ----------------------------------------------------------------------------
5510 --
5511 -- PROCEDURE NAME: credit_transactions
5512 --
5513 -- DECSRIPTION:
5514 -- Server-side entry point for the CM module.
5515 --
5516 -- ARGUMENTS:
5517 -- IN:
5518 -- customer_trx_id
5519 -- customer_trx_line_id
5520 -- prev_customer_trx_id
5521 -- prev_cust_trx_line_id
5522 -- request_id
5523 -- process_mode (I)nsert or (U)pdate
5524 --
5525 -- IN/OUT:
5526 -- failure_count
5527 -- rule_start_date
5528 -- accounting_rule_duration
5529 --
5530 -- OUT:
5531 --
5532 -- NOTES:
5533 -- Calls credit_transactions_ins_mode.
5534 --
5535 -- Raises the exception arp_credit_memo_module.no_ccid if autoaccounting
5536 -- could not derive a valid code combination. The public variable
5537 -- g_error_buffer is populated for more information.
5538 --
5539 -- Raises the exception NO_DATA_FOUND if no rows were selected for
5540 -- processing.
5541 --
5542 -- Exception raised if Oracle error.
5543 -- App_exception is raised for all other fatal errors and a message
5544 -- is put on the AOL stack. The public variable g_error_buffer is
5545 -- populated for both types of errors.
5546 --
5547 --
5548 -- HISTORY:
5549 --
5550 ----------------------------------------------------------------------------
5551 PROCEDURE credit_transactions(
5552 p_customer_trx_id IN NUMBER,
5553 p_customer_trx_line_id IN NUMBER,
5554 p_prev_customer_trx_id IN NUMBER,
5555 p_prev_cust_trx_line_id IN NUMBER,
5556 p_request_id IN NUMBER,
5557 p_failure_count IN OUT NOCOPY NUMBER,
5558 p_rule_start_date IN OUT NOCOPY DATE,
5559 p_accounting_rule_duration IN OUT NOCOPY NUMBER,
5560 p_process_mode IN VARCHAR2,
5561 p_run_autoaccounting_flag IN BOOLEAN
5562 ) IS
5563
5564 l_ignore INTEGER;
5565 gl_header_dist_array dbms_sql.number_table; /* mrc */
5566 gl_line_dist_array dbms_sql.number_table; /* mrc */
5567 l_xla_ev_rec ARP_XLA_EVENTS.XLA_EVENTS_TYPE; -- bug5870933
5568
5569 BEGIN
5570 print_fcn_label('arp_credit_memo_module.credit_transactions()+' );
5571
5572
5573 --------------------------------------------------------------------
5574 -- Check parameters
5575 --------------------------------------------------------------------
5576 IF( ( p_customer_trx_id IS NULL AND
5577 p_customer_trx_line_id IS NULL AND
5578 p_request_id IS NULL )
5579 OR
5580 ( p_request_id IS NOT NULL AND
5581 ( p_customer_trx_id IS NOT NULL OR
5582 p_customer_trx_line_id IS NOT NULL OR
5583 p_prev_cust_trx_line_id IS NOT NULL )
5584 )
5585 ) THEN
5586
5587 --------------------------------------------------------------------
5588 -- Invalid parameters
5589 --------------------------------------------------------------------
5590 debug( ' raising invalid_parameters', MSG_LEVEL_DEBUG );
5591 g_error_buffer := MSG_INVALID_PARAMETERS;
5592 debug( 'EXCEPTION: '||g_error_buffer, MSG_LEVEL_BASIC );
5593 RAISE invalid_parameters;
5594
5595 END IF;
5596
5597
5598 IF( p_process_mode = U ) THEN
5599 ------------------------------
5600 -- Process Update mode
5601 ------------------------------
5602 debug( ' Update mode', MSG_LEVEL_DEBUG );
5603
5604 IF( NOT( dbms_sql.is_open( delete_header_dist_c ) AND
5605 dbms_sql.is_open( delete_line_dist_c ) AND
5606 dbms_sql.is_open( delete_header_cma_c ) AND
5607 dbms_sql.is_open( delete_line_cma_c ) AND
5608 dbms_sql.is_open( update_header_lines_c ) AND
5609 dbms_sql.is_open( update_lines_c ) ) ) THEN
5610
5611 ----------------------------------------------------------------
5612 -- Build dynamic sql
5613 ----------------------------------------------------------------
5614 build_update_mode_sql(
5615 delete_header_dist_c,
5616 delete_line_dist_c,
5617 delete_header_cma_c,
5618 delete_line_cma_c,
5619 update_header_lines_c,
5620 update_lines_c );
5621
5622 END IF;
5626 BEGIN
5623
5624 SAVEPOINT ar_credit_memo_module2;
5625
5627
5628 ----------------------------------------------------------------
5629 -- Delete distributions, credit_memo_amounts
5630 -- and update lines (rule_start_date, accounting_rule_duration)
5631 ----------------------------------------------------------------
5632 IF( p_customer_trx_line_id IS NOT NULL ) THEN
5633
5634 ---------------------------------------------------------------
5635 -- Line-level processing
5636 ---------------------------------------------------------------
5637 debug( ' Line-level processing', MSG_LEVEL_DEBUG );
5638
5639 ---------------------------------------------------------------
5640 -- Bind variables
5641 ---------------------------------------------------------------
5642 dbms_sql.bind_variable(
5643 delete_line_dist_c,
5644 'customer_trx_line_id',
5645 p_customer_trx_line_id );
5646
5647 dbms_sql.bind_variable(
5648 delete_line_cma_c,
5649 'customer_trx_line_id',
5650 p_customer_trx_line_id );
5651
5652 dbms_sql.bind_variable(
5653 update_lines_c,
5654 'customer_trx_line_id',
5655 p_customer_trx_line_id );
5656
5657 --------------------------------------------------------------
5658 -- Bind output variables
5659 --------------------------------------------------------------
5660 dbms_sql.bind_array(delete_line_dist_c, ':gl_dist_key_value',
5661 gl_line_dist_array);
5662
5663
5664 ---------------------------------------------------------------
5665 -- Execute sql
5666 ---------------------------------------------------------------
5667 debug( ' Executing delete dist sql', MSG_LEVEL_DEBUG );
5668 BEGIN
5669 l_ignore := dbms_sql.execute( delete_line_dist_c );
5670
5671 debug( to_char(l_ignore) || ' row(s) deleted',
5672 MSG_LEVEL_DEBUG );
5673
5674 /*------------------------------------------+
5675 | get RETURNING COLUMN into OUT NOCOPY bind array |
5676 +------------------------------------------*/
5677
5678 dbms_sql.variable_value( delete_line_dist_c,
5679 ':gl_dist_key_value',
5680 gl_line_dist_array);
5681 EXCEPTION
5682 WHEN OTHERS THEN
5683 debug( 'EXCEPTION: Error executing delete dist sql' );
5684 RAISE;
5685 END;
5686
5687 debug( ' Executing delete cma sql', MSG_LEVEL_DEBUG );
5688 BEGIN
5689 l_ignore := dbms_sql.execute( delete_line_cma_c );
5690
5691 debug( to_char(l_ignore) || ' row(s) deleted',
5692 MSG_LEVEL_DEBUG );
5693 EXCEPTION
5694 WHEN OTHERS THEN
5695 debug( 'EXCEPTION: Error executing delete cma sql' );
5696 RAISE;
5697 END;
5698
5699 debug( ' Executing update lines sql', MSG_LEVEL_DEBUG );
5700 BEGIN
5701 l_ignore := dbms_sql.execute( update_lines_c );
5702
5703 debug( to_char(l_ignore) || ' row(s) updated',
5704 MSG_LEVEL_DEBUG );
5705 EXCEPTION
5706 WHEN OTHERS THEN
5707 debug( 'EXCEPTION: Error executing update lines sql' );
5708 RAISE;
5709 END;
5710
5711 ELSE
5712
5713 ---------------------------------------------------------------
5714 -- Header-level processing
5715 ---------------------------------------------------------------
5716 debug( ' Header-level processing', MSG_LEVEL_DEBUG );
5717
5718 ---------------------------------------------------------------
5719 -- Bind variables
5720 ---------------------------------------------------------------
5721 dbms_sql.bind_variable(
5722 delete_header_dist_c,
5723 'customer_trx_id',
5724 p_customer_trx_id );
5725
5726 dbms_sql.bind_variable(
5727 delete_header_cma_c,
5728 'customer_trx_id',
5729 p_customer_trx_id );
5730
5731 dbms_sql.bind_variable(
5732 update_header_lines_c,
5733 'customer_trx_id',
5734 p_customer_trx_id );
5735
5736 --------------------------------------------------------------
5737 -- Bind output variables
5738 --------------------------------------------------------------
5739 dbms_sql.bind_array(delete_header_dist_c, ':gl_dist_key_value',
5740 gl_header_dist_array);
5741
5742 ---------------------------------------------------------------
5743 -- Execute sql
5747 l_ignore := dbms_sql.execute( delete_header_dist_c );
5744 ---------------------------------------------------------------
5745 debug( ' Executing delete dist sql', MSG_LEVEL_DEBUG );
5746 BEGIN
5748
5749 debug( to_char(l_ignore) || ' row(s) deleted',
5750 MSG_LEVEL_DEBUG );
5751 /*------------------------------------------+
5752 | get RETURNING COLUMN into OUT NOCOPY bind array |
5753 +------------------------------------------*/
5754
5755 dbms_sql.variable_value( delete_header_dist_c,
5756 ':gl_dist_key_value',
5757 gl_header_dist_array);
5758 EXCEPTION
5759 WHEN OTHERS THEN
5760 debug( 'EXCEPTION: Error executing delete dist sql' );
5761 RAISE;
5762 END;
5763
5764 debug( ' Executing delete cma sql', MSG_LEVEL_DEBUG );
5765 BEGIN
5766 l_ignore := dbms_sql.execute( delete_header_cma_c );
5767
5768 debug( to_char(l_ignore) || ' row(s) deleted',
5769 MSG_LEVEL_DEBUG );
5770 EXCEPTION
5771 WHEN OTHERS THEN
5772 debug( 'EXCEPTION: Error executing delete cma sql' );
5773 RAISE;
5774 END;
5775
5776 debug( ' Executing update lines sql', MSG_LEVEL_DEBUG );
5777 BEGIN
5778 l_ignore := dbms_sql.execute( update_header_lines_c );
5779
5780 debug( to_char(l_ignore) || ' row(s) updated',
5781 MSG_LEVEL_DEBUG );
5782 EXCEPTION
5783 WHEN OTHERS THEN
5784 debug( 'EXCEPTION: Error executing update lines sql' );
5785 RAISE;
5786 END;
5787
5788
5789 END IF;
5790
5791 EXCEPTION
5792 WHEN OTHERS THEN
5793
5794 ROLLBACK TO ar_credit_memo_module2;
5795 g_error_buffer := SQLERRM;
5796 RAISE;
5797
5798 END;
5799
5800
5801 END IF;
5802
5803
5804 --------------------------------------------------------------------
5805 -- Call cm module in I mode
5806 --------------------------------------------------------------------
5807 credit_transactions_ins_mode(
5808 p_customer_trx_id,
5809 p_customer_trx_line_id,
5810 p_prev_customer_trx_id,
5811 p_prev_cust_trx_line_id,
5812 p_request_id,
5813 p_failure_count,
5814 p_rule_start_date,
5815 p_accounting_rule_duration,
5816 p_run_autoaccounting_flag
5817 );
5818
5819 --bug 5870933
5820 /*-----------------------------------------------------+
5821 | Need to call ARP_XLA for denormalizing the event_id |
5822 | on rev distribution from CM Workflow |
5823 +-----------------------------------------------------*/
5824 IF( p_customer_trx_id IS NOT NULL ) THEN
5825 l_xla_ev_rec.xla_from_doc_id := p_customer_trx_id;
5826 l_xla_ev_rec.xla_to_doc_id := p_customer_trx_id;
5827 l_xla_ev_rec.xla_doc_table := 'CT';
5828 l_xla_ev_rec.xla_mode := 'O';
5829 l_xla_ev_rec.xla_call := 'D';
5830 arp_xla_events.create_events(l_xla_ev_rec);
5831 END IF;
5832
5833
5834 print_fcn_label('arp_credit_memo_module.credit_transactions()-' );
5835
5836 END credit_transactions;
5837
5838 ----------------------------------------------------------------------------
5839 --
5840 -- PROCEDURE NAME: credit_transactions
5841 --
5842 -- DECSRIPTION:
5843 -- Server-side entry point for the CM module.
5844 --
5845 --
5846 -- ARGUMENTS:
5847 -- IN:
5848 -- customer_trx_id
5849 -- customer_trx_line_id
5850 -- prev_customer_trx_id
5851 -- prev_cust_trx_line_id
5852 -- request_id
5853 -- process_mode (I)nsert or (U)pdate
5854 --
5855 -- IN/OUT:
5856 -- failure_count
5857 --
5858 -- OUT:
5859 --
5860 -- NOTES:
5861 -- This is the older version of the API and is a cover to the new version.
5862 -- It exists for backward compatibillity.
5863 --
5864 -- Raises the exception arp_credit_memo_module.no_ccid if autoaccounting
5865 -- could not derive a valid code combination. The public variable
5866 -- g_error_buffer is populated for more information.
5867 --
5868 -- Raises the exception NO_DATA_FOUND if no rows were selected for
5869 -- processing.
5870 --
5871 -- Exception raised if Oracle error.
5872 -- App_exception is raised for all other fatal errors and a message
5873 -- is put on the AOL stack. The public variable g_error_buffer is
5874 -- populated for both types of errors.
5875 --
5876 --
5877 -- HISTORY:
5878 --
5879 ----------------------------------------------------------------------------
5880 PROCEDURE credit_transactions(
5881 p_customer_trx_id IN NUMBER,
5882 p_customer_trx_line_id IN NUMBER,
5883 p_prev_customer_trx_id IN NUMBER,
5887 p_process_mode IN VARCHAR2
5884 p_prev_cust_trx_line_id IN NUMBER,
5885 p_request_id IN NUMBER,
5886 p_failure_count IN OUT NOCOPY NUMBER,
5888 ) IS
5889
5890 l_date DATE;
5891 l_number NUMBER;
5892
5893 BEGIN
5894
5895 print_fcn_label('arp_credit_memo_module.credit_transactions_cover()+' );
5896
5897 credit_transactions(
5898 p_customer_trx_id,
5899 p_customer_trx_line_id,
5900 p_prev_customer_trx_id,
5901 p_prev_cust_trx_line_id,
5902 p_request_id,
5903 p_failure_count,
5904 l_date, -- rule_start_date
5905 l_number, -- accounting_rule_duration
5906 p_process_mode
5907 );
5908
5909 print_fcn_label('arp_credit_memo_module.credit_transactions_cover()-' );
5910
5911 END credit_transactions;
5912
5913
5914 ---------------------------------------------------------------------------
5915 -- Test Functions
5916 ---------------------------------------------------------------------------
5917 PROCEDURE test_build_nonrule_sql is
5918
5919 l_cm_control control_rec_type;
5920 l_null_rec control_rec_type := l_cm_control;
5921
5922 BEGIN
5923
5924 -- enable_debug( 1000000 );
5925
5926
5927 -- sys info
5928 debug( 'coa_id='||to_char(system_info.chart_of_accounts_id), MSG_LEVEL_DEBUG);
5929 debug( 'curr='||system_info.base_currency, MSG_LEVEL_DEBUG);
5930 debug( 'prec='||to_char(system_info.base_precision), MSG_LEVEL_DEBUG);
5931 debug( 'mau='||to_char(system_info.base_min_acc_unit), MSG_LEVEL_DEBUG);
5932
5933 -- profile info
5934 debug( 'login_id='||profile_info.conc_login_id, MSG_LEVEL_DEBUG );
5935 debug( 'program_id='||profile_info.conc_program_id, MSG_LEVEL_DEBUG );
5936 debug( 'user_id='||profile_info.user_id, MSG_LEVEL_DEBUG );
5937 debug( 'use_inv_acct='||profile_info.use_inv_acct_for_cm_flag,
5938 MSG_LEVEL_DEBUG );
5939 debug( 'org_id='||oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id), MSG_LEVEL_DEBUG );
5940
5941 -- flex info
5942 debug( 'nsegs='||to_char(flex_info.number_segments), MSG_LEVEL_DEBUG);
5943 debug( 'delim='||flex_info.delim, MSG_LEVEL_DEBUG);
5944
5945 debug('PASS LINE ID');
5946
5947 l_cm_control.customer_trx_id := 1001;
5948 l_cm_control.customer_trx_line_id := 2001;
5949 l_cm_control.request_id := null;
5950
5951 build_nonrule_sql(system_info,
5952 profile_info,
5953 l_cm_control,
5954 nonrule_insert_dist_c,
5955 nonrule_update_lines_c,
5956 nonrule_update_dist_c,
5957 nonrule_update_dist2_c );
5958
5959 debug('PASS TRX ID');
5960
5961 l_cm_control.customer_trx_id := 1001;
5962 l_cm_control.customer_trx_line_id := null;
5963 l_cm_control.request_id := null;
5964
5965 build_nonrule_sql(system_info,
5966 profile_info,
5967 l_cm_control,
5968 nonrule_insert_dist_c,
5969 nonrule_update_lines_c,
5970 nonrule_update_dist_c,
5971 nonrule_update_dist2_c );
5972
5973 debug('PASS REQ ID');
5974
5975 l_cm_control.customer_trx_id := null;
5976 l_cm_control.customer_trx_line_id := null;
5977 l_cm_control.request_id := 101;
5978
5979 build_nonrule_sql(system_info,
5980 profile_info,
5981 l_cm_control,
5982 nonrule_insert_dist_c,
5983 nonrule_update_lines_c,
5984 nonrule_update_dist_c,
5985 nonrule_update_dist2_c );
5986
5987 disable_debug;
5988
5989
5990 END;
5991
5992
5993 --
5994 --
5995 --
5996 PROCEDURE test_build_rule_sql is
5997
5998 l_cm_control control_rec_type;
5999 l_null_rec control_rec_type := l_cm_control;
6000
6001 BEGIN
6002
6003 -- enable_debug;
6004
6005
6006 -- sys info
6007 debug( 'coa_id='||to_char(system_info.chart_of_accounts_id), MSG_LEVEL_DEBUG);
6008 debug( 'curr='||system_info.base_currency, MSG_LEVEL_DEBUG);
6009 debug( 'prec='||to_char(system_info.base_precision), MSG_LEVEL_DEBUG);
6010 debug( 'mau='||to_char(system_info.base_min_acc_unit), MSG_LEVEL_DEBUG);
6011
6012 -- profile info
6013 debug( 'login_id='||profile_info.conc_login_id, MSG_LEVEL_DEBUG );
6014 debug( 'program_id='||profile_info.conc_program_id, MSG_LEVEL_DEBUG );
6015 debug( 'user_id='||profile_info.user_id, MSG_LEVEL_DEBUG );
6016 debug( 'use_inv_acct='||profile_info.use_inv_acct_for_cm_flag,
6017 MSG_LEVEL_DEBUG );
6018 debug( 'org_id='||oe_profile.value('SO_ORGANIZATION_ID',arp_global.sysparam.org_id), MSG_LEVEL_DEBUG );
6019
6020 -- flex info
6021 debug( 'nsegs='||to_char(flex_info.number_segments), MSG_LEVEL_DEBUG);
6022 debug( 'delim='||flex_info.delim, MSG_LEVEL_DEBUG);
6023
6024
6025 -- system_info.base_min_acc_unit := .009;
6026
6027 debug('PASS LINE ID');
6028
6029 l_cm_control.customer_trx_id := 1001;
6030 l_cm_control.customer_trx_line_id := 2001;
6034 profile_info,
6031 l_cm_control.request_id := null;
6032
6033 build_rule_sql(system_info,
6035 l_cm_control,
6036 rule_select_cm_lines_c,
6037 rule_update_cm_lines_c,
6038 rule_insert_dist_c,
6039 rule_insert_cma_c );
6040
6041 debug('PASS TRX ID');
6042
6043 l_cm_control.customer_trx_id := 1001;
6044 l_cm_control.customer_trx_line_id := null;
6045 l_cm_control.request_id := null;
6046
6047 build_rule_sql(system_info,
6048 profile_info,
6049 l_cm_control,
6050 rule_select_cm_lines_c,
6051 rule_update_cm_lines_c,
6052 rule_insert_dist_c,
6053 rule_insert_cma_c );
6054
6055 debug('PASS REQ ID');
6056
6057 l_cm_control.customer_trx_id := null;
6058 l_cm_control.customer_trx_line_id := null;
6059 l_cm_control.request_id := 101;
6060
6061 build_rule_sql(system_info,
6062 profile_info,
6063 l_cm_control,
6064 rule_select_cm_lines_c,
6065 rule_update_cm_lines_c,
6066 rule_insert_dist_c,
6067 rule_insert_cma_c );
6068
6069 disable_debug;
6070
6071
6072 END;
6073
6074
6075 --
6076 --
6077 --
6078 PROCEDURE test_build_net_revenue_sql is
6079
6080 l_cm_control control_rec_type;
6081 l_null_rec control_rec_type := l_cm_control;
6082
6083 BEGIN
6084
6085 -- enable_debug;
6086
6087
6088 debug('PASS LINE ID');
6089 l_cm_control.prev_customer_trx_id := NULL;
6090 l_cm_control.customer_trx_line_id := 2001;
6091 l_cm_control.prev_cust_trx_line_id := 3001;
6092 l_cm_control.request_id := null;
6093
6094 build_net_revenue_sql(system_info,
6095 profile_info,
6096 l_cm_control,
6097 net_revenue_line_c );
6098
6099
6100 debug('PASS TRX ID');
6101 l_cm_control.prev_customer_trx_id := 1001;
6102 l_cm_control.customer_trx_line_id := NULL;
6103 l_cm_control.prev_cust_trx_line_id := NULL;
6104 l_cm_control.request_id := NULL;
6105
6106 build_net_revenue_sql(system_info,
6107 profile_info,
6108 l_cm_control,
6109 net_revenue_line_c );
6110
6111 debug('PASS REQUEST ID');
6112 l_cm_control.prev_customer_trx_id := NULL;
6113 l_cm_control.customer_trx_line_id := NULL;
6114 l_cm_control.prev_cust_trx_line_id := NULL;
6115 l_cm_control.request_id := 101;
6116
6117 build_net_revenue_sql(system_info,
6118 profile_info,
6119 l_cm_control,
6120 net_revenue_line_c );
6121
6122 disable_debug;
6123
6124
6125 END;
6126
6127
6128 PROCEDURE test_build_update_mode_sql is
6129
6130 l_cm_control control_rec_type;
6131 l_null_rec control_rec_type := l_cm_control;
6132
6133 BEGIN
6134
6135 -- enable_debug( 1000000 );
6136
6137 build_update_mode_sql(
6138 delete_header_dist_c,
6139 delete_line_dist_c,
6140 delete_header_cma_c,
6141 delete_line_cma_c,
6142 update_header_lines_c,
6143 update_lines_c );
6144
6145
6146 END;
6147
6148
6149 --
6150 --
6151 --
6152 PROCEDURE test_load_net_revenue( p_prev_ctlid NUMBER ) is
6153
6154 l_cm_control control_rec_type;
6155 l_null_rec control_rec_type := l_cm_control;
6156
6157 BEGIN
6158
6159 -- enable_debug;
6160
6161 l_cm_control.prev_customer_trx_id := NULL;
6162 l_cm_control.customer_trx_line_id := 2001;
6163 l_cm_control.prev_cust_trx_line_id := p_prev_ctlid;
6164 l_cm_control.request_id := NULL;
6165
6166 load_net_revenue_schedule(
6167 system_info,
6168 profile_info,
6169 l_cm_control, p_prev_ctlid );
6170
6171 disable_debug;
6172
6173
6174 END;
6175
6176 --
6177 --
6178 --
6179 PROCEDURE test_credit_nonrule_trxs(
6180 p_customer_trx_id NUMBER,
6181 p_customer_trx_line_id NUMBER,
6182 p_request_id NUMBER
6183 ) IS
6184
6185 l_cm_control control_rec_type;
6186 l_null_rec control_rec_type := l_cm_control;
6187
6188 BEGIN
6189
6190 -- enable_debug( 1000000 );
6191 arp_global.msg_level := 99;
6192
6193 l_cm_control.customer_trx_id := p_customer_trx_id;
6194 l_cm_control.customer_trx_line_id := p_customer_trx_line_id;
6195 l_cm_control.request_id := p_request_id;
6196
6197 credit_nonrule_transactions( system_info,
6198 profile_info,
6199 l_cm_control );
6200
6201
6202
6203 disable_debug;
6204
6205
6206 END;
6207 --
6208 --
6209 --
6210 PROCEDURE test_credit_rule_trxs(
6211 p_customer_trx_id NUMBER,
6212 p_prev_customer_trx_id NUMBER,
6213 p_customer_trx_line_id NUMBER,
6214 p_prev_cust_trx_line_id NUMBER,
6215 p_request_id NUMBER
6216 ) IS
6217
6218 l_cm_control control_rec_type;
6219 l_null_rec control_rec_type := l_cm_control;
6220 l_rule_start_date DATE;
6221 l_accounting_rule_duration NUMBER;
6222 l_number NUMBER;
6223
6224 BEGIN
6225
6226 -- enable_debug( 1000000 );
6227 arp_global.msg_level := MSG_LEVEL_DEBUG;
6228
6229 l_cm_control.customer_trx_id := p_customer_trx_id;
6230 l_cm_control.prev_customer_trx_id := p_prev_customer_trx_id;
6231 l_cm_control.customer_trx_line_id := p_customer_trx_line_id;
6232 l_cm_control.prev_cust_trx_line_id := p_prev_cust_trx_line_id;
6233 l_cm_control.request_id := p_request_id;
6234
6235 credit_rule_transactions( system_info,
6236 profile_info,
6237 l_cm_control,
6238 l_number,
6239 l_rule_start_date,
6240 l_accounting_rule_duration );
6241
6242
6243
6244 disable_debug;
6245
6246
6247 END;
6248
6249 --
6250 -- Constructor code
6251 --
6252 PROCEDURE init IS
6253 BEGIN
6254
6255 print_fcn_label( 'arp_credit_memo_module.constructor()+' );
6256
6257 /* Bug 2560036 - determine if collectibility is enabled */
6258 g_test_collectibility :=
6259 ar_revenue_management_pvt.revenue_management_enabled;
6260
6261 get_error_message_text;
6262
6263 print_fcn_label( 'arp_credit_memo_module.constructor()-' );
6264
6265
6266 EXCEPTION
6267 WHEN OTHERS THEN
6268 debug('EXCEPTION: arp_credit_memo_module.constructor()');
6269 debug(SQLERRM);
6270 RAISE;
6271 END init;
6272
6273 BEGIN
6274 init;
6275
6276 END arp_credit_memo_module;