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