[Home] [Help]
PACKAGE BODY: APPS.ARP_AUTO_RULE
Source
1 PACKAGE BODY arp_auto_rule AS
2 /* $Header: ARPLARLB.pls 120.45.12010000.3 2008/11/13 20:12:56 mraymond ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'Y');
4
5 cr CONSTANT char(1) := '
6 ';
7
8 /* GLOBAL declarations */
9
10 TYPE gl_start_table_type IS TABLE OF
11 gl_period_statuses.start_date%TYPE
12 INDEX BY BINARY_INTEGER;
13
14 gl_start_t gl_start_table_type;
15
16 TYPE gl_end_table_type IS TABLE OF
17 gl_period_statuses.end_date%TYPE
18 INDEX BY BINARY_INTEGER;
19
20 gl_end_t gl_end_table_type;
21
22 TYPE gl_status_table_type IS TABLE OF
23 gl_period_statuses.closing_status%TYPE
24 INDEX BY BINARY_INTEGER;
25
26 gl_status_t gl_status_table_type;
27
28 TYPE gl_bump_table_type IS TABLE OF
29 gl_period_statuses.start_date%TYPE
30 INDEX BY BINARY_INTEGER;
31
32 gl_bump_t gl_bump_table_type;
33
34 g_rows NUMBER;
35 glp_index_start NUMBER := 1;
36 glp_index_end NUMBER;
37 glp_index_rec NUMBER;
38
39 min_gl_date DATE;
40 max_gl_date DATE;
41 rec_gl_date DATE;
42
43 org_id NUMBER;
44 sob_id NUMBER;
45 g_rev_mgt_installed VARCHAR2(1); -- Bug 2560048
46 g_last_valid_date DATE; -- Bug 3879222
47 g_valid_start_index NUMBER; -- Bug 3879222
48 g_use_inv_acctg VARCHAR2(1); -- 5598773
49
50 /* bug 4284925 : introduce a variable string which will hold various
51 account_class values : REC, TAX, ROUND, FREIGHT which is used in multiple
52 selects to avoid re-parsing similar statements
53 */
54 acct_class RA_CUST_TRX_LINE_GL_DIST.ACCOUNT_CLASS%TYPE;
55
56 /*-------------------------------------------------------------------------+
57 | PRIVATE PROCEDURE |
58 | assign_glp_index |
59 | |
60 | DESCRIPTION |
61 | Assigns the start and end indexes in the glp table for the least and |
62 | greatest GL dates for the transaction. This will speed up the search |
63 | for assigning the GL date for each revenue distribution. |
64 | |
65 | PARAMETERS |
66 | |
67 | RETURNS |
68 | None. |
69 | |
70 | NOTES |
71 | |
72 | EXAMPLE |
73 | |
74 | assign_glp_index; |
75 | |
76 | MODIFICATION HISTORY |
77 | 26-AUG-98 Srini Jandyala created. |
78 +-------------------------------------------------------------------------*/
79
80 FUNCTION assign_glp_index RETURN NUMBER IS
81 error_message FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE; -- bug 4194220
82 BEGIN
83 arp_standard.debug('arp_auto_rule.assign_glp_index()+ ' ||
84 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
85
86 /* Set glp_index_start to the period having MIN GL date for the trx. */
87
88 FOR i IN 1..g_rows
89 LOOP
90
91 IF ( min_gl_date >= gl_start_t(i) AND min_gl_date <= gl_end_t(i) )
92 THEN
93 glp_index_start := i;
94 EXIT;
95 END IF;
96
97 END LOOP;
98
99 /* Set glp_index_end to the period having MAX GL date for the trx. */
100
101 FOR i IN glp_index_start..g_rows
102 LOOP
103
104 IF ( max_gl_date >= gl_start_t(i) AND max_gl_date <= gl_end_t(i) )
105 THEN
106 glp_index_end := i;
107 EXIT;
108 END IF;
109
110 END LOOP;
111
112 IF (glp_index_end IS NULL)
113 THEN
114 glp_index_end := g_rows;
115 END IF;
116
117 arp_standard.debug('glp table index start = '||glp_index_start||', end = '||glp_index_end);
118
119 /* bug 3477990 */
120 FOR i IN glp_index_start..glp_index_end
121 LOOP
122 IF gl_bump_t(i) IS NOT NULL
123 THEN
124 IF gl_bump_t.EXISTS(i-1)
125 AND
126 gl_bump_t(i-1) IS NULL
127 THEN
128 error_message := FND_MESSAGE.GET_STRING('AR','AR_RAXTRX-1783');
129 arp_standard.debug('Cannot process this transaction...');
130 arp_standard.debug('Closed or closed pending period exists at ' || i || 'with start date ' ||
131 TO_CHAR( gl_start_t( i) , 'DD-MON-YY HH:MI:SS'));
132 arp_standard.debug(error_message);
133
134 RETURN -1;
135 END IF;
136 END IF;
137 END LOOP;
138
139 RETURN 0;
140 arp_standard.debug('arp_auto_rule.assign_glp_index()- ' ||
141 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
142
143 END assign_glp_index;
144
145 /*-------------------------------------------------------------------------+
146 | PRIVATE PROCEDURE |
147 | assign_glp_rec |
148 | |
149 | DESCRIPTION |
150 | Assigns the index for the latest_rec_flag record |
151 | |
152 | PARAMETERS |
153 | |
154 | RETURNS |
155 | None. |
156 | |
157 | NOTES |
158 | |
159 | EXAMPLE |
160 | |
161 | assign_glp_rec; |
162 | |
163 | MODIFICATION HISTORY |
164 | 10-FEB-99 Victoria Smith Created |
165 +-------------------------------------------------------------------------*/
166 PROCEDURE assign_glp_rec IS
167
168 BEGIN
169 arp_standard.debug('arp_auto_rule.assign_glp_rec()+ ' ||
170 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
171
172 /* Set glp_index_rec to the period having REC record's gl date */
173
174 FOR i IN 1..g_rows
175 LOOP
176
177 IF ( rec_gl_date >= gl_start_t(i) AND rec_gl_date <= gl_end_t(i) )
178 THEN
179 glp_index_rec := i;
180 EXIT;
181 END IF;
182
183 END LOOP;
184
185 arp_standard.debug('glp table index rec = '||glp_index_rec);
186
187 arp_standard.debug('arp_auto_rule.assign_glp_rec()- ' ||
188 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
189
190 END assign_glp_rec;
191
192 /*-------------------------------------------------------------------------+
193 | PRIVATE PROCEDURE |
194 | populate_glp_table |
195 | |
196 | DESCRIPTION |
197 | Builds the GL periods table (PL/SQL). |
198 | |
199 | The table has the following columns: |
200 | start_date, end_date, closing_status, bump_date. |
201 | |
202 | This procedure is called by main
203
204 function create_distributions(). |
205 | |
206 | PARAMETERS |
207 | p_sob IN NUMBER, - set_of_books_id |
208 | p_appl_id IN NUMBER - application_id |
209 | |
210 | RETURNS |
211 | None. |
212 | |
213 | NOTES |
214 | |
215 | EXAMPLE |
216 | populate_glp_table(2, 222); |
217 | |
218 | MODIFICATION HISTORY |
219 | 20-MAR-98 Srinivasan Jandyala Created. |
220 | 03-DEC-01 M Raymond - Bug 2133254 - rewrote logic to execute this
221 | routine only the first time in.
222 | Also eliminated
223 | the inner SELECT with a pair of nested for
224 | loops and some simple PLSQL code.
225 | Finally, replaced FOR-SELECT structure with
226 | modern CURSOR LOOP to reduce parsing.
227 | 17-SEP-04 M Raymond - Bug 3879222 - Moved call-once logic inside
228 | this procedure to make it more useful to
229 | external applications.
230 +-------------------------------------------------------------------------*/
231
232 PROCEDURE populate_glp_table(
233 p_sob IN NUMBER,
234 p_appl_id IN NUMBER) IS
235
236 CURSOR c_gl_period_rec(v_sob NUMBER, v_appl_id NUMBER) IS
237 SELECT
238 start_date,
239 end_date,
240 closing_status
241 FROM
242 gl_period_statuses
243 WHERE
244 application_id = v_appl_id
245 AND set_of_books_id = v_sob
246 AND adjustment_period_flag = 'N'
247 ORDER BY
248 period_year,
249 period_num,
250 start_date,
251 end_date;
252
253 last_good_date DATE;
254
255 BEGIN
256 IF PG_DEBUG in ('Y', 'C') THEN
257 arp_standard.debug('arp_auto_rule.populate_glp_table()+ ' ||
258 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
259 arp_standard.debug('Populating gl_period_statuses table.');
260 END IF;
261
262 /* Bug 2133254 - only call this the first time in for a given SOB */
263 /* Bug 3879222 - moved this logic inside the procedure */
264 /* NOTE: sob_id is a global that gets set the first time it executes */
265 IF (sob_id is null OR
266 sob_id <> arp_standard.sysparm.set_of_books_id)
267 THEN
268 IF PG_DEBUG in ('Y', 'C') THEN
269 arp_standard.debug( 'glp tables need to be built' ||
270 ' sob_id: ' || sob_id);
271 END IF;
272
273 sob_id := arp_standard.sysparm.set_of_books_id;
274
275 /* Original logic starts here */
276 g_rows := 0;
277
278 FOR gl_period_rec IN c_gl_period_rec(p_sob, p_appl_id) LOOP
279
280 g_rows := g_rows + 1;
281 gl_start_t(g_rows) := gl_period_rec.start_date;
282 gl_end_t(g_rows) := gl_period_rec.end_date;
283 gl_status_t(g_rows) := gl_period_rec.closing_status;
284 IF PG_DEBUG in ('Y', 'C') THEN
285 arp_standard.debug('Row ['|| g_rows ||']: Start Dt <' ||
286 gl_period_rec.start_date || '> End Date <' || gl_period_rec.end_date
287 || '> Status <' || gl_period_rec.closing_status || '>');
288 END IF;
289
290 END LOOP;
291
292 /* Determine bump dates for closed periods */
293 FOR bump_pos IN REVERSE 1 .. g_rows LOOP
294
295 IF gl_status_t(bump_pos) = 'F' OR
296 gl_status_t(bump_pos) = 'O' OR
297 gl_status_t(bump_pos) = 'N'
298 THEN
299 last_good_date := gl_start_t(bump_pos);
300 gl_bump_t(bump_pos) := null;
301 ELSIF gl_status_t(bump_pos) = 'C' OR
302 gl_status_t(bump_pos) = 'W'
303 THEN
304 gl_bump_t(bump_pos) := last_good_date;
305 END IF;
306
307 END LOOP;
308 /* Original logic ends here */
309
310 END IF; /* end of call-once case */
311
312 IF PG_DEBUG in ('Y', 'C') THEN
313 arp_standard.debug('arp_auto_rule.populate_glp_table()- ');
314 END IF;
315
316 END populate_glp_table;
317
318 /*-------------------------------------------------------------------------+
319 | PUBLIC PROCEDURE |
320 | refresh |
321 | |
322 | DESCRIPTION |
323 | Updates ar_periods and ar_period_types tables with latest changes in |
324 | gl_periods table. |
325 | |
326 | PARAMETERS |
327 | INPUT |
328 | None. |
329 | |
330 | OUTPUT |
331 | Errbuf VARCHAR2 -- Conc Pgm Error mesgs. |
332 | RetCode VARCHAR2 -- Conc Pgm Error Code. |
333 | 0 - Success, 2 - Failure. |
337 | EXAMPLE |
334 | |
335 | NOTES |
336 | |
338 | refresh; |
339 | |
340 | MODIFICATION HISTORY |
341 | 28-JUL-97 Srinivasan Jandyala Created. |
342 +-------------------------------------------------------------------------*/
343
344 PROCEDURE refresh (Errbuf OUT NOCOPY VARCHAR2,
345 Retcode OUT NOCOPY VARCHAR2) IS
346 BEGIN
347
348 IF PG_DEBUG in ('Y', 'C') THEN
349 arp_standard.debug( 'arp_auto_rule.refresh()+ ' ||
350 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
351 END IF;
352
353 SAVEPOINT AR_PERIODS_1;
354
355 IF PG_DEBUG in ('Y', 'C') THEN
356 arp_standard.debug('Updating ar_period_types: ');
357 END IF;
358
359 UPDATE ar_period_types apt
360 SET
361 max_regular_period_length =
362 (
363 SELECT MAX(g.end_date - g.start_date) + 1
364 FROM gl_periods g
365 WHERE g.period_type = apt.period_type
366 AND g.adjustment_period_flag = 'N'
367 )
368 WHERE
369 max_regular_period_length <>
370 (
371 SELECT MAX(g.end_date - g.start_date) + 1
372 FROM gl_periods g
373 WHERE g.period_type = apt.period_type
374 AND g.adjustment_period_flag = 'N'
375 );
376
377 IF PG_DEBUG in ('Y', 'C') THEN
378 arp_standard.debug('row(s) updated: ' || TO_CHAR(sql%rowcount));
379 arp_standard.debug('Inserting into ar_period_types: ');
380 END IF;
381
382 INSERT
383 INTO ar_period_types
384 ( period_type, max_regular_period_length )
385 (
386 SELECT
387 g.period_type,
388 MAX(g.end_date - g.start_date) + 1 max_regular_period_length
389 FROM
390 gl_periods g
391 WHERE
392 g.adjustment_period_flag = 'N'
393 AND NOT EXISTS
394 (
395 SELECT NULL
396 FROM ar_period_types apt
397 WHERE apt.period_type = g.period_type
398 )
399 GROUP BY period_type
400 );
401
402 IF PG_DEBUG in ('Y', 'C') THEN
403 arp_standard.debug('row(s) updated: ' || TO_CHAR(sql%rowcount));
404 arp_standard.debug('Deleting redundent ar_periods: ');
405 END IF;
406
407 DELETE
408 FROM ar_periods ap
409 WHERE
410 NOT EXISTS
411 (
412 SELECT NULL
413 FROM gl_periods gp
414 WHERE gp.period_name = ap.period_name
415 AND gp.period_set_name = ap.period_set_name
416 AND gp.adjustment_period_flag = 'N'
417 );
418
419 IF PG_DEBUG in ('Y', 'C') THEN
420 arp_standard.debug('row(s) deleted: ' || TO_CHAR(sql%rowcount));
421 arp_standard.debug('Updating start, end dates in ar_periods: ');
422 END IF;
423
424 UPDATE ar_periods ap
425 SET
426 (period_type, start_date, end_date) =
427 (
428 SELECT period_type, start_date, end_date
429 FROM gl_periods gp
430 WHERE gp.period_name = ap.period_name
431 AND gp.period_set_name = ap.period_set_name
432 )
433 WHERE
434 EXISTS
435 (
436 SELECT NULL
437 FROM gl_periods gp
438 WHERE
439 gp.period_name = ap.period_name
440 AND gp.period_set_name = ap.period_set_name
441 AND NOT (gp.period_type = ap.period_type AND
442 gp.start_date = ap.start_date AND
443 gp.end_date = ap.end_date
444 )
445 );
446
447 IF PG_DEBUG in ('Y', 'C') THEN
448 arp_standard.debug('row(s) updated: ' || TO_CHAR(sql%rowcount));
449 arp_standard.debug('Inserting into ar_periods: ');
450 END IF;
451
452 INSERT
453 INTO ar_periods
454 (period_set_name , period_type, start_date, end_date,
455 new_period_num, period_name
456 )
457 (SELECT
458 period_set_name, period_type, start_date, end_date,
459 9999 + ROWNUM new_period_num,
460 period_name
461 FROM
462 gl_periods gp
463 WHERE
464 gp.adjustment_period_flag = 'N'
465 AND NOT EXISTS
466 (
467 SELECT NULL
468 FROM ar_periods ap
469 WHERE gp.period_name = ap.period_name
470 AND gp.period_set_name = ap.period_set_name
471 )
472 );
473
474 IF PG_DEBUG in ('Y', 'C') THEN
475 arp_standard.debug('row(s) inserted: ' || TO_CHAR(sql%rowcount));
476 arp_standard.debug('Updating period sequence number in ar_periods: ');
480 SET
477 END IF;
478
479 UPDATE ar_periods p1
481 new_period_num =
482 (
483 SELECT COUNT(*)
484 FROM ar_periods p2
485 WHERE p1.period_type = p2.period_type
486 AND p1.period_set_name = p2.period_set_name
487 AND p1.start_date >= p2.start_date
488 )
489 WHERE
490 new_period_num <>
491 (
492 SELECT COUNT(*)
493 FROM AR_PERIODS p2
494 WHERE p1.period_type = p2.period_type
495 AND p1.period_set_name = p2.period_set_name
496 AND p1.start_date >= p2.start_date
497 );
498
499 IF PG_DEBUG in ('Y', 'C') THEN
500 arp_standard.debug('row(s) updated: ' || TO_CHAR(sql%rowcount));
501 arp_standard.debug( 'arp_auto_rule.refresh()- ' ||
502 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
503 END IF;
504
505 EXCEPTION
506 WHEN OTHERS THEN
507
508 IF PG_DEBUG in ('Y', 'C') THEN
509 arp_standard.debug('Exception occured in: arp_auto_rule.refresh()');
510 END IF;
511
512 ROLLBACK TO SAVEPOINT AR_PERIODS_1;
513 RAISE;
514
515 END refresh;
516
517 /*-------------------------------------------------------------------------+
518 | PUBLIC FUNCTION |
519 | assign_gl_date |
520 | |
521 | DESCRIPTION |
522 | Check if GL date of a distribution is in a OPEN or FUTURE or |
523 | NOT OPEN period. |
524 | If it is, then we shouldn't bump it. If the GL date is in a 'Closed' |
525 | or 'Closed Pending' period, bump it to the next Open/Future/Not Open |
526 | period. |
527 | |
528 | PARAMETERS |
529 | p_gl_date IN DATE, - Rev distribution GL date |
530 | |
531 | RETURNS |
532 | p_gl_date, if GL date is in Open/Future/Not Open period |
533 | bump_date, if GL date is in Closed/Close Pending period |
534 | |
535 | NOTES |
536 | |
537 | EXAMPLE |
538 | assign_gl_date(to_date('01-JAN-1998', 'DD-MON-YYYY'); |
539 | |
540 | MODIFICATION HISTORY |
541 | 03-MAR-98 Srini Jandyala Created. |
542 | 28-FEB-03 M Raymond Bug 2810336 - added debug messages to
543 | catch and document calendar gaps.
544 | 17-SEP-04 M Raymond Bug 3879222 - added call to
545 | populate_glp_table to allow this
546 | to be used outside of this package.
547 +-------------------------------------------------------------------------*/
548
549 FUNCTION assign_gl_date(p_gl_date IN DATE)
550
551 RETURN DATE IS
552
553 i NUMBER;
554 l_temp_index NUMBER;
555
556 BEGIN
557 /* Populate the GL periods PL/SQL table. */
558 populate_glp_table(arp_standard.sysparm.set_of_books_id,
559 arp_standard.application_id);
560
561 /* 3879222 - Added NVLs to accomodate external
562 calls to this routine. Also attempt to set
563 a start index for subsequent calls */
564
565 IF p_gl_date = g_last_valid_date
566 THEN
567 RETURN(p_gl_date);
568 ELSIF p_gl_date > g_last_valid_date
569 THEN
570 /* Set a temp start index to bypass all the old periods */
571 l_temp_index := g_valid_start_index;
572 ELSE
573 /* Set temp index to 1 (start from beginning of table) */
574 l_temp_index := 1;
575 END IF;
576
577 FOR i IN NVL(glp_index_start,l_temp_index)..NVL(glp_index_end, g_rows)
578 LOOP
579
580 IF ( p_gl_date >= gl_start_t(i) AND p_gl_date <= gl_end_t(i) )
581 THEN
582 IF (gl_status_t(i) IN ('O', 'F', 'N') )
583 THEN
584 /* Store starting points for subsequent calls */
585 g_last_valid_date := p_gl_date;
586 g_valid_start_index := i;
587 RETURN( p_gl_date );
588 ELSE
589 RETURN( gl_bump_t(i) );
590 END IF;
591 END IF;
592
593 END LOOP;
594
595 /* NOTE: This only executes if the loop falls through without
596 finding a period for the passed date. It is a soft error
597 condition */
598 arp_standard.debug('ERROR: Unable to find period for ' || p_gl_date);
599
600 FOR i IN 1..g_rows LOOP
601
605 TO_CHAR(gl_start_t(i), 'DD-MON-RRRR')||', '||
602 IF PG_DEBUG in ('Y', 'C') THEN
603 arp_standard.debug(
604 'index = '||i||', '||
606 TO_CHAR(gl_end_t(i), 'DD-MON-RRRR')||', '||
607 gl_status_t(i)||', '||
608 TO_CHAR(gl_bump_t(i), 'DD-MON-RRRR') );
609 END IF;
610
611 END LOOP;
612
613 END assign_gl_date;
614
615 /*-------------------------------------------------------------------------+
616 | PUBLIC FUNCTION |
617 | assign_gl_rec |
618 | |
619 | DESCRIPTION |
620 | Check if GL date of REC distribution is in a OPEN or FUTURE or |
621 | NOT OPEN period. |
622 | If it is, then we shouldn't bump it. If the GL date is in a 'Closed' |
623 | or 'Closed Pending' period, bump it to the next Open/Future/Not Open |
624 | period. |
625 | |
626 | PARAMETERS |
627 | p_gl_date IN DATE, - REC distribution GL date |
628 | |
629 | RETURNS |
630 | p_gl_date, if GL date is in Open/Future/Not Open period |
631 | bump_date, if GL date is in Closed/Close Pending period |
632 | |
633 | NOTES |
634 | |
635 | EXAMPLE |
636 | assign_gl_rec(to_date('01-JAN-1998', 'DD-MON-YYYY')); |
637 | |
638 | MODIFICATION HISTORY |
639 | 10-FEB-99 Victoria Smith Created
640 | 17-SEP-04 M Raymond Bug 3879222 - added populate_glp_table
641 | to make this work when called from other
642 | packages. |
643 +-------------------------------------------------------------------------*/
644
645 FUNCTION assign_gl_rec(p_gl_date IN DATE)
646
647 RETURN DATE IS
648
649 BEGIN
650
651 /* Populate the GL periods PL/SQL table. */
652 populate_glp_table(arp_standard.sysparm.set_of_books_id,
653 arp_standard.application_id);
654
655 IF (gl_status_t(glp_index_rec) IN ('O', 'F', 'N') )
656 THEN
657 RETURN( p_gl_date );
658 ELSE
659 RETURN( gl_bump_t(glp_index_rec) );
660 END IF;
661
662 END assign_gl_rec;
663
664 /*-------------------------------------------------------------------------+
665 | PRIVATE FUNCTION |
666 | create_assignments |
667 | |
668 | DESCRIPTION |
669 | Create revenue assignments in ra_cust_trx_line_gl_dist from any |
670 | un-expanded model accounts within the specified date range |
671 | |
672 | PARAMETERS |
673 | p_trx_id IN NUMBER, |
674 | p_period_set_name IN VARCHAR, |
675 | p_base_precision IN NUMBER, |
676 | p_bmau IN NUMBER |
677 | |
678 | RETURNS |
679 | row count of number of records inserted. |
680 | |
681 | NOTES |
682 | |
683 | EXAMPLE |
684 | |
685 | MODIFICATION HISTORY |
686 | 22-JAN-93 Nigel Smith created. |
687 | 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
688 | 20-MAR-98 S.Jandyala Modified the function to create |
689 | revenue account assignments by trx_id |
690 | 04-OCT-00 Jon Beckett Assignments not created if revenue |
691 | deferred(bug 1551488 Rev Mgmt phase II)|
692 | 10-DEC-01 M Raymond Added ORDERED hint to gl_date sql |
693 | that uses ar_revenue_assignments view. |
694 | See bug 2143064 for details. |
695 | 13-Aug-02 Debbie Jancis Modified for MRC Trigger replacement |
699 | rows where rec_offset_flag is null.
696 | added calls for |
697 | ra_cust_trx_line_gl_dist processing |
698 | 31-JAN-03 M Raymond Modified MRC cursor to include UNEARN
700 | 02-MAY-03 M Raymond Modified REV insert to include an
701 | outer join to ra_cust_trx_line_salesreps
702 | so we can assign proper salesrep id
703 | on CM distributions.
704 +-------------------------------------------------------------------------*/
705
706 FUNCTION create_assignments(
707 p_trx_id IN NUMBER,
708 p_period_set_name IN VARCHAR,
709 p_base_precision IN NUMBER,
710 p_bmau IN NUMBER)
711
712 RETURN NUMBER IS
713
714 /* added for MRC Trigger Replacement */
715 l_rows NUMBER;
716 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
717
718 /* bug 3477990 */
719 l_return_status NUMBER;
720 BEGIN
721
722 IF PG_DEBUG in ('Y', 'C') THEN
723 arp_standard.debug( 'arp_auto_rule.create_assignments()+ ' ||
724 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
725 END IF;
726
727 SELECT
728 gl_date
729 INTO
730 rec_gl_date
731 FROM
732 ra_cust_trx_line_gl_dist
733 WHERE
734 account_class = 'REC'
735 AND account_set_flag = 'Y'
736 AND customer_trx_id = p_trx_id ;
737
738 IF PG_DEBUG in ('Y', 'C') THEN
739 arp_standard.debug('rec date: ' ||
740 TO_CHAR(rec_gl_date));
741 END IF;
742
743 /* Bug 2143064 - Added ORDERED hint */
744 SELECT /*+ ORDERED */
745 MIN(gl_date),
746 MAX(gl_date)
747 INTO
748 min_gl_date,
749 max_gl_date
750 FROM
751 ar_revenue_assignments
752 WHERE
753 customer_trx_id = p_trx_id
754 AND period_set_name = p_period_set_name ;
755
756 IF PG_DEBUG in ('Y', 'C') THEN
757 arp_standard.debug('min date: ' ||
758 TO_CHAR(min_gl_date) || ', max date: '||
759 TO_CHAR(max_gl_date));
760 END IF;
761
762 IF ( min_gl_date IS NULL OR max_gl_date IS NULL )
763 THEN
764 IF PG_DEBUG in ('Y', 'C') THEN
765 arp_standard.debug('Min/Max gl_dates are NULL!!. Check trx..');
766 END IF;
767 RETURN ( -2 );
768 END IF;
769
770 /* Set the start and end indexes in glp table for faster search. */
771
772 l_return_status := assign_glp_index;
773 IF l_return_status = -1 THEN
774 RETURN l_return_status;
775 END IF;
776
777 assign_glp_rec;
778 IF PG_DEBUG in ('Y', 'C') THEN
779 arp_standard.debug('before insert....');
780 arp_standard.debug('g_rev_mgt_installed : ' ||g_rev_mgt_installed);
781 END IF;
782 INSERT INTO ra_cust_trx_line_gl_dist /* REV lines */
783 (
784 customer_trx_line_id,
785 customer_trx_id,
786 code_combination_id,
787 set_of_books_id,
788 account_class,
789 account_set_flag,
790 percent,
791 amount,
792 acctd_amount,
793 gl_date,
794 cust_trx_line_salesrep_id,
795 request_id,
796 program_application_id,
797 program_id,
798 program_update_date,
799 creation_date,
800 created_by,
801 last_update_date,
802 last_updated_by,
803 posting_control_id,
804 original_gl_date,
805 cust_trx_line_gl_dist_id,
806 org_id
807 ) /* Bug 2118867 - added ORDERED hint */
808 SELECT /*+ ORDERED */
809 ass.customer_trx_line_id, /* customer_trx_line_id */
810 lines.customer_trx_id, /* customer_trx_id */
811 dist.code_combination_id, /* code_combination_id */
812 arp_standard.sysparm.set_of_books_id, /* set_of_books_id */
813 ass.account_class, /* account_class */
814 'N', /* account_set_flag */
815 ROUND(
816 (DECODE(fc.minimum_accountable_unit,
817 NULL, ROUND( (dist.percent/100) *
818 DECODE(ass.amount, 0,
819 DECODE(ass.account_class,
820 'REV', DECODE( lines.previous_customer_trx_id,
821 NULL, 1, -1
822 ),
823 DECODE( lines.previous_customer_trx_id,
824 NULL, -1, 1
825 )
826 ),
827 ass.amount
828 ), fc.precision),
832 'REV', DECODE( lines.previous_customer_trx_id,
829 ROUND( ((dist.percent/100) *
830 DECODE(ass.amount,
831 0, DECODE(ass.account_class,
833 NULL, 1, -1),
834 DECODE( lines.previous_customer_trx_id,
835 NULL, -1, 1)
836 ),
837 ass.amount) ) /
838 fc.minimum_accountable_unit) *
839 fc.minimum_accountable_unit) /
840 DECODE(lines.extended_amount,
841 0,1,
842 lines.extended_amount)) * decode(ass.amount, 0, 0, 100), /* Bug 944929 */
843 4), /* percent */
844 DECODE(fc.minimum_accountable_unit,
845 NULL, ROUND( (dist.percent/100) * ass.amount, fc.precision),
846 ROUND( ((dist.percent/100) * ass.amount) /
847 fc.minimum_accountable_unit) *
848 fc.minimum_accountable_unit), /* amount */
849 DECODE(p_bmau,
850 NULL, ROUND(
851 NVL(header.exchange_rate, 1) *
852 DECODE(fc.minimum_accountable_unit,
853 NULL, ROUND( (dist.percent/100)
854 * ass.amount,
855 fc.precision),
856 ROUND( ((dist.percent/100) *
857 ass.amount) /
858 fc.minimum_accountable_unit) *
859 fc.minimum_accountable_unit
860 ),
861 p_base_precision),
862 ROUND(
863 ( NVL(header.exchange_rate, 1) *
864 DECODE(fc.minimum_accountable_unit,
865 NULL, ROUND( (dist.percent/100) * ass.amount,
866 fc.precision),
867 ROUND( ( (dist.percent/100) * ass.amount) /
868 fc.minimum_accountable_unit) *
869 fc.minimum_accountable_unit
870 )
871 ) / p_bmau) * p_bmau
872 ), /* acctd_amount */
873 /*
874 Use the bump GL date if the actual Rev distribution GL date is in a
875 'Closed' OR 'Closed Pending' period.
876
877 Insert a NULL GL date if the transaction is post to GL = No.
878 */
879 DECODE(rec.gl_date,
880 NULL, NULL,
881 assign_gl_date(ass.gl_date)
882 ), /* derived gl_date */
883 DECODE(dist.customer_trx_id, header.customer_trx_id,
884 dist.cust_trx_line_salesrep_id,
885 cmsrep.cust_trx_line_salesrep_id), /* cust_trx_line_salesrep_id */
886 arp_standard.profile.request_id,
887 arp_standard.application_id,
888 arp_standard.profile.program_id,
889 sysdate,
890 sysdate,
891 arp_standard.profile.user_id,
892 sysdate,
893 arp_standard.profile.user_id,
894 -3,
895 ass.gl_date, /* original_gl_date */
896 ra_cust_trx_line_gl_dist_s.NEXTVAL, /* cust_trx_line_gl_dist_id */
897 header.org_id
898 FROM
899 ra_customer_trx header,
900 fnd_currencies fc,
901 ra_customer_trx_lines lines,
902 ra_rules acc_rule,
903 ra_cust_trx_line_gl_dist rec,
904 ra_cust_trx_line_gl_dist dist,
905 ra_cust_trx_line_salesreps cmsrep,
906 ar_revenue_assignments ass
907 WHERE
908 header.customer_trx_id = p_trx_id
909 AND ass.customer_trx_id = p_trx_id /* 5752668 */
910 AND header.complete_flag = 'Y'
911 AND fc.currency_code = header.invoice_currency_code
912 /* only lines that are not comlpete with respect to autorule */
913 /* get accounting from view for line */
914 AND lines.customer_trx_id = header.customer_trx_id
915 AND lines.autorule_complete_flag||'' = 'N'
916 AND ass.customer_trx_line_id = lines.customer_trx_line_id
917 AND ass.period_set_name = p_period_set_name
918 AND acc_rule.rule_id = lines.accounting_rule_id
919 /* Bug 2560048/2639395 RAM-C - call collectivity engine to determine
920 if revenue should be deferred for INV or CM */
921 /* 6060283 - changed credits from cash_based to line_collectible
922 so they honor deferrals other than cash-based ones */
923 AND decode(header.invoicing_rule_id, -3, ar_revenue_management_pvt.collect,
924 decode(nvl(acc_rule.deferred_revenue_flag, 'N'),
925 'Y', ar_revenue_management_pvt.defer,
926 decode(g_rev_mgt_installed, 'N', ar_revenue_management_pvt.collect,
930 lines.previous_customer_trx_id,
927 decode(header.previous_customer_trx_id, NULL,
928 ar_revenue_management_pvt.line_collectibility(p_trx_id, lines.customer_trx_line_id),
929 ar_revenue_management_pvt.line_collectible(
931 lines.previous_customer_trx_line_id)))))
932 <> ar_revenue_management_pvt.defer
933 AND rec.customer_trx_id = header.customer_trx_id
934 AND rec.account_class = 'REC'
935 AND rec.latest_rec_flag = 'Y'
936 /* join account set distribution to the transaction with the
937 account set. */
938 AND dist.customer_trx_line_id =
939 (SELECT
940 DECODE(COUNT(cust_trx_line_gl_dist_id),
941 0, NVL(lines.previous_customer_trx_line_id,
942 lines.customer_trx_line_id),
943 lines.customer_trx_line_id)
944 FROM
945 ra_cust_trx_line_gl_dist subdist2
946 WHERE
947 subdist2.customer_trx_line_id = lines.customer_trx_line_id
948 AND subdist2.account_set_flag = 'Y'
949 AND subdist2.gl_date IS NULL
950 AND ROWNUM < 2
951 )
952 AND dist.account_class = ass.account_class
953 /* only pick up account set accounts */
954 AND dist.account_set_flag = 'Y' /* model accounts */
955 /* Bug 2899714 */
956 AND dist.cust_trx_line_salesrep_id = cmsrep.prev_cust_trx_line_salesrep_id (+)
957 AND p_trx_id = cmsrep.customer_trx_id (+)
958 /* don't recreate those that already exist */
959 AND NOT EXISTS
960 (
961 SELECT
962 'distribution exists'
963 FROM
964 ra_cust_trx_line_gl_dist subdist
965 WHERE
966 subdist.customer_trx_line_id = ass.customer_trx_line_id
967 AND subdist.customer_trx_id + 0 = lines.customer_trx_id
968 AND subdist.account_set_flag = 'N'
969 AND subdist.account_class = ass.account_class
970 AND subdist.original_gl_date = ass.gl_date
971 );
972
973 /* need to process mrc data as well. Need to save the row count for
974 final return */
975 l_rows := sql%rowcount;
976
977 IF PG_DEBUG in ('Y', 'C') THEN
978 arp_standard.debug('Revenue lines inserted: ' ||
979 l_rows);
980 arp_standard.debug('arp_auto_rule.create_assignments()- ' ||
981 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
982 END IF;
983
984 RETURN( l_rows);
985
986 EXCEPTION
987 WHEN OTHERS THEN
988
989 IF PG_DEBUG in ('Y', 'C') THEN
990 arp_standard.debug('EXCEPTION: arp_auto_rule.create_assignments()');
991 arp_standard.debug(SQLERRM);
992 arp_standard.debug( 'arp_auto_rule.create_assignments()- ' ||
993 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
994 END IF;
995
996 RETURN( -1 );
997
998 END create_assignments;
999
1000 /*-------------------------------------------------------------------------+
1001 | PRIVATE FUNCTION |
1002 | create_other_receivable |
1003 | |
1004 | DESCRIPTION |
1005 | |
1006 | Create real receivable record if it doesn't already exist. |
1007 | |
1008 | PARAMETERS |
1009 | |
1010 | p_trx_id IN NUMBER, |
1011 | p_base_precision IN NUMBER, |
1012 | p_bmau IN NUMBER |
1013 | |
1014 | RETURNS |
1015 | Row count of number of records inserted. |
1016 | |
1017 | NOTES |
1018 | |
1019 | EXAMPLE |
1020 | |
1021 | MODIFICATION HISTORY |
1022 | 22-JAN-93 Nigel Smith Created. |
1023 | 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
1024 | 20-MAR-98 S.Jandyala Modified the function to create |
1025 | revenue account assignments by trx_id |
1026 +-------------------------------------------------------------------------*/
1027
1028 FUNCTION create_other_receivable(
1029 p_trx_id IN NUMBER,
1033 RETURN NUMBER IS
1030 p_base_precision IN NUMBER,
1031 p_bmau IN NUMBER)
1032
1034
1035 /* added for MRC Trigger Replacement */
1036 l_rows NUMBER;
1037 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr; /* mrc */
1038
1039 /* bug 1947192 */
1040 cursor c_update_ps( l_trx_id NUMBER) is
1041 SELECT ps.payment_schedule_id ps_id,
1042 gld.gl_date gl_date
1043 FROM ar_payment_schedules ps,
1044 ra_cust_trx_line_gl_dist gld
1045 WHERE gld.customer_trx_id = l_trx_id
1046 AND gld.account_class = 'REC'
1047 AND gld.account_set_flag = 'N'
1048 AND gld.customer_trx_id = ps.customer_trx_id
1049 AND gld.gl_date <> ps.gl_date;
1050
1051 BEGIN
1052
1053 IF PG_DEBUG in ('Y', 'C') THEN
1054 arp_standard.debug( 'arp_auto_rule.create_other_receivable()+ ' ||
1055 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1056 END IF;
1057
1058 INSERT INTO ra_cust_trx_line_gl_dist /* REC line */
1059 (
1060 customer_trx_id,
1061 code_combination_id,
1062 set_of_books_id,
1063 account_class,
1064 account_set_flag,
1065 latest_rec_flag,
1066 percent,
1067 amount,
1068 acctd_amount,
1069 gl_date,
1070 request_id,
1071 created_by,
1072 creation_date,
1073 last_updated_by,
1074 last_update_date,
1075 program_application_id,
1076 program_id,
1077 program_update_date,
1078 posting_control_id,
1079 original_gl_date,
1080 cust_trx_line_gl_dist_id,
1081 org_id
1082 ) /* Bug 1544343 - added RULE hint, bug 2110069 - removed
1083 RULE hint and changed subquery join (below) */
1084 SELECT
1085 rec.customer_trx_id,
1086 rec.code_combination_id,
1087 arp_standard.sysparm.set_of_books_id,
1088 rec.account_class,
1089 'N', /* account_set_flag */
1090 'Y', /* latest_rec_flag */
1091 rec.percent,
1092 rec.amount,
1093 rec.acctd_amount,
1094 DECODE(rec.gl_date,
1095 NULL, NULL,
1096 assign_gl_rec(rec.gl_date)
1097 ), /* derived gl_date */
1098 arp_standard.profile.request_id,
1099 arp_standard.profile.user_id,
1100 sysdate,
1101 arp_standard.profile.user_id,
1102 sysdate,
1103 arp_standard.application_id,
1104 arp_standard.profile.program_id,
1105 sysdate,
1106 -3, /* posting_control_id */
1107 NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
1108 ra_cust_trx_line_gl_dist_s.NEXTVAL,
1109 header.org_id
1110 FROM
1111 ra_cust_trx_line_gl_dist rec,
1112 ra_customer_trx header
1113 WHERE
1114 header.customer_trx_id = p_trx_id
1115 AND header.complete_flag = 'Y'
1116 AND rec.customer_trx_id = header.customer_trx_id
1117 AND rec.account_class = 'REC'
1118 AND rec.latest_rec_flag = 'Y'
1119 AND rec.account_set_flag = 'Y'
1120 /* ensure that the receivable doesn't already exist */
1121 AND NOT EXISTS
1122 (
1123 SELECT
1124 'exist'
1125 FROM
1126 ra_cust_trx_line_gl_dist real_rec
1127 WHERE
1128 real_rec.customer_trx_id = rec.customer_trx_id
1129 AND real_rec.account_class = 'REC'
1130 AND real_rec.account_set_flag = 'N'
1131 );
1132 l_rows := sql%rowcount;
1133
1134 IF PG_DEBUG in ('Y', 'C') THEN
1135 arp_standard.debug('REC lines inserted: ' ||
1136 l_rows);
1137 END IF;
1138
1139 IF PG_DEBUG in ('Y', 'C') THEN
1140 arp_standard.debug( 'create_other_receivable(-)');
1141 END IF;
1142
1143
1144 /* Erase the latest_rec_flag of the receivable account set
1145 records for the transactions whose actual receivable records
1146 have just been created. */
1147
1148 /* no mrc columns affected so no update to mrc table needed */
1149
1150 /* Bug 3416070 - Removed request_id from where clause */
1151
1152 UPDATE ra_cust_trx_line_gl_dist
1153 SET
1154 latest_rec_flag = 'N',
1155 last_updated_by = arp_standard.profile.user_id,
1156 last_update_date = sysdate
1157 WHERE
1158 account_set_flag = 'Y'
1159 AND account_class = 'REC'
1160 AND latest_rec_flag = 'Y'
1161 AND customer_trx_id IN
1162 (
1163 SELECT
1164 customer_trx_id
1165 FROM
1169 AND account_class = 'REC'
1166 ra_cust_trx_line_gl_dist
1167 WHERE
1168 customer_trx_id = p_trx_id
1170 AND account_set_flag = 'N'
1171 );
1172
1173 l_rows := sql%rowcount;
1174
1175
1176 /* bug 1947192 */
1177 FOR i IN c_update_ps(p_trx_id) LOOP
1178 IF PG_DEBUG in ('Y', 'C') THEN
1179 arp_standard.debug('arp_auto_rule.create_other_receivable(): Change Payment Schedule Gl Date');
1180 END IF;
1181 UPDATE ar_payment_schedules
1182 SET gl_date = i.gl_date ,
1183 last_updated_by = arp_standard.profile.user_id,
1184 last_update_date = sysdate
1185 WHERE payment_schedule_id = i.ps_id;
1186 END LOOP;
1187
1188 IF PG_DEBUG in ('Y', 'C') THEN
1189 arp_standard.debug( 'arp_auto_rule.create_other_receivable()- ' ||
1190 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1191 END IF;
1192
1193 RETURN( l_rows );
1194
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197
1198 IF PG_DEBUG in ('Y', 'C') THEN
1199 arp_standard.debug( 'EXCEPTION: arp_auto_rule.create_other_receivable()');
1200 arp_standard.debug( SQLERRM);
1201 arp_standard.debug( 'arp_auto_rule.create_other_receivable()- ' ||
1202 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1203 END IF;
1204
1205 RETURN( -1 );
1206
1207 END create_other_receivable;
1208
1209 /*-------------------------------------------------------------------------+
1210 | PRIVATE FUNCTION |
1211 | create_round |
1212 | |
1213 | DESCRIPTION |
1214 | |
1215 | Create real round record if it doesn't already exist. |
1216 | |
1217 | PARAMETERS |
1218 | |
1219 | p_trx_id IN NUMBER, |
1220 | p_base_precision IN NUMBER, |
1221 | p_bmau IN NUMBER |
1222 | |
1223 | RETURNS |
1224 | Row count of number of records inserted. |
1225 | |
1226 | NOTES |
1227 | |
1228 | EXAMPLE |
1229 | |
1230 | MODIFICATION HISTORY |
1231 | 11-Sep-98 Ramakant Alat Added call to create_round and changed |
1232 | 17-JAN-02 M Raymond If original gl_date is in closed period,
1233 | REC row was getting bumped to next open,
1234 | but ROUND was getting created in closed
1235 | period. Now, ROUND gets bumped to same
1236 | date as REC.
1237 | See bug 2172061 for details.
1238 | |
1239 +-------------------------------------------------------------------------*/
1240
1241 FUNCTION create_round(
1242 p_trx_id IN NUMBER,
1243 p_base_precision IN NUMBER,
1244 p_bmau IN NUMBER)
1245
1246 RETURN NUMBER IS
1247
1248 /* added for mrc */
1249 l_rows NUMBER;
1250 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1251
1252 BEGIN
1253
1254 IF PG_DEBUG in ('Y', 'C') THEN
1255 arp_standard.debug( 'arp_auto_rule.create_round()+ ' ||
1256 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1257 END IF;
1258
1259
1260 INSERT INTO ra_cust_trx_line_gl_dist /* ROUND line */
1261 ( /* drive from gl_dist */
1262 customer_trx_id,
1263 code_combination_id,
1264 set_of_books_id,
1265 account_class,
1266 account_set_flag,
1267 latest_rec_flag,
1268 percent,
1269 amount,
1270 acctd_amount,
1271 gl_date,
1272 request_id,
1273 created_by,
1274 creation_date,
1275 last_updated_by,
1276 last_update_date,
1277 program_application_id,
1278 program_id,
1279 program_update_date,
1280 posting_control_id,
1281 original_gl_date,
1282 cust_trx_line_gl_dist_id,
1283 org_id
1284 )
1285 SELECT
1286 rec.customer_trx_id,
1290 'N', /* account_set_flag */
1287 rec.code_combination_id,
1288 arp_standard.sysparm.set_of_books_id,
1289 rec.account_class,
1291 null, /* latest_rec_flag */
1292 rec.percent,
1293 rec.amount,
1294 rec.acctd_amount,
1295 rrec.gl_date, /* 2172061 - now fetches date from REC row */
1296 arp_standard.profile.request_id,
1297 arp_standard.profile.user_id,
1298 sysdate,
1299 arp_standard.profile.user_id,
1300 sysdate,
1301 arp_standard.application_id,
1302 arp_standard.profile.program_id,
1303 sysdate,
1304 -3, /* posting_control_id */
1305 NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
1306 ra_cust_trx_line_gl_dist_s.nextval,
1307 header.org_id
1308 FROM
1309 ra_customer_trx header,
1310 ra_cust_trx_line_gl_dist rec, /* ROUND row */
1311 ra_cust_trx_line_gl_dist rrec /* REC row */
1312 WHERE
1313 header.customer_trx_id = p_trx_id
1314 AND header.complete_flag = 'Y'
1315 AND header.customer_trx_id = rec.customer_trx_id
1316 AND rec.account_class = 'ROUND'
1317 AND rec.account_set_flag = 'Y'
1318 AND header.customer_trx_id = rrec.customer_trx_id
1319 AND rrec.account_class = 'REC'
1320 AND rrec.latest_rec_flag = 'Y'
1321 /* ensure that the round record doesn't already exist */
1322 AND NOT EXISTS
1323 (
1324 SELECT 'exist'
1325 FROM
1326 ra_cust_trx_line_gl_dist real_rec
1327 WHERE
1328 real_rec.customer_trx_id = rec.customer_trx_id
1329 AND real_rec.account_class = 'ROUND'
1330 AND real_rec.account_set_flag = 'N'
1331 );
1332
1333 l_rows := sql%rowcount;
1334
1335 IF PG_DEBUG in ('Y', 'C') THEN
1336 arp_standard.debug( 'ROUND lines inserted: ' ||
1337 l_rows);
1338 arp_standard.debug( 'arp_auto_rule.create_round()- ' ||
1339 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1340 END IF;
1341
1342 RETURN( l_rows);
1343
1344 EXCEPTION
1345 WHEN OTHERS THEN
1346
1347 IF PG_DEBUG in ('Y', 'C') THEN
1348 arp_standard.debug( 'EXCEPTION: arp_auto_rule.create_round()');
1349 arp_standard.debug( SQLERRM);
1350 arp_standard.debug( 'arp_auto_rule.create_round()- ' ||
1351 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1352 END IF;
1353
1354 RETURN( -1 );
1355
1356 END create_round;
1357 /*-------------------------------------------------------------------------+
1358 | PRIVATE FUNCTION |
1359 | create_other_plug |
1360 | |
1361 | DESCRIPTION |
1362 | |
1363 | REQUIRES |
1364 | |
1365 | RETURNS |
1366 | Row count of number of records inserted. |
1367 | |
1368 | NOTES |
1369 | |
1370 | EXAMPLE |
1371 | |
1372 | MODIFICATION HISTORY |
1373 | 22-JAN-93 Nigel Smith created. |
1374 | 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
1375 | 20-MAR-98 S.Jandyala Modified the function to create |
1376 | revenue account assignments by trx_id |
1377 | 10-MAY-02 M Raymond Added column rec_offset_flag to
1378 | ra_cust_trx_line_gl_dist_all. Added
1379 | logic to this insert to populate it
1380 | with a 'Y' if inserting UNEARN or
1381 | UNBILL lines.
1382 | See bug 2150541 for details.
1383 | 18-SEP-02 J Beckett Bug 2560048 RAM-C - create cr UNEARN / |
1384 | dr UNBILL if deferred/arrears. Select |
1385 | restructured into cursor fetched into |
1386 | variables. |
1387 | 09-OCT-02 J Beckett Bug 2560048: U-turn on the above |
1388 | approach - code is reverted to prior |
1389 | state |
1390 | 31-JAN-03 M Raymond Bug 2779454 - Added logic to limit
1391 | the processing of UNEARN/UNBILL rows
1395 | 14-APR-03 M Raymond Bug 2899714 - Corrected assignment of
1392 | to only those with rof = Y. Rows with
1393 | rof=null are processed in
1394 | create_assignments
1396 | cust_trx_line_salesrep_id for Credit
1397 | Memos. Also removed some old RELEASE 9
1398 | logic to improve performance a bit.
1399 | 17-SEP-05 M Raymond Bug 4602892 - We now allow multiple
1400 | lines on one CM to point to a single
1401 | invoice line. The fix from 2899714
1402 | causes too many rows to be inserted.
1403 +-------------------------------------------------------------------------*/
1404
1405
1406 FUNCTION create_other_plug(
1407 p_trx_id IN NUMBER,
1408 p_base_precision IN NUMBER,
1409 p_bmau IN NUMBER)
1410
1411 RETURN NUMBER IS
1412
1413 /* added for MRC */
1414 l_rows NUMBER;
1415 /* bug 3550426 */
1416 l_ctt_id ra_cust_trx_types.cust_trx_type_id%TYPE;
1417 l_inv_rule_id ra_customer_trx.invoicing_rule_id%TYPE;
1418 l_ctt_type ra_cust_trx_types.type%TYPE;
1419 l_revrec_run_flag VARCHAR2(1);
1420 l_prev_cust_trx_id ra_customer_trx.customer_trx_id%TYPE;
1421
1422 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1423
1424 /*FP 5450534 for bug 5260489 take a dummy parameter to pass in call to rev. rec.*/
1425 l_request_id ra_customer_trx.request_id%type;
1426
1427 l_result NUMBER;
1428 NO_ROF_EXCEPTION EXCEPTION;
1429
1430 BEGIN
1431
1432 IF PG_DEBUG in ('Y', 'C') THEN
1433 arp_standard.debug( 'arp_auto_rule.create_other_plug()+ ' ||
1434 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1435 arp_standard.debug(' use_inv_acctg = ' || g_use_inv_acctg);
1436 END IF;
1437
1438 /* Bug 3550426 */
1439 SELECT cust_trx_type_id ,
1440 invoicing_rule_id,
1441 previous_customer_trx_id
1442 INTO l_ctt_id,
1443 l_inv_rule_id,
1444 l_prev_cust_trx_id
1445 FROM ra_customer_trx
1446 WHERE customer_trx_id = p_trx_id;
1447
1448 arp_standard.debug(' prev ct id ' || l_prev_cust_trx_id);
1449
1450 l_ctt_type := arpt_sql_func_util.get_trx_type_details(l_ctt_id, 'TYPE') ;
1451 arp_standard.debug(' l_ctt_type ' || l_ctt_type);
1452
1453 IF l_ctt_type = 'CM' AND
1454 g_use_inv_acctg = 'Y'
1455 THEN
1456 /* 5598773 - no reason to get the l_revrec_run_flag if
1457 use_inv_acctg is 'N' */
1458 l_revrec_run_flag := arpt_sql_func_util.get_revenue_recog_run_flag(l_prev_cust_trx_id,
1459 l_inv_rule_id);
1460 arp_standard.debug(' l_revrec_run_flag ' || l_revrec_run_flag);
1461 END IF;
1462
1463 IF l_ctt_type in ('INV','DM') /*Bug 4547416*/
1464 OR
1465 ( l_ctt_type = 'CM' AND l_revrec_run_flag = 'N')
1466 OR
1467 ( l_ctt_type = 'CM' AND g_use_inv_acctg = 'N') /* 5598773 */
1468 THEN
1469 INSERT INTO ra_cust_trx_line_gl_dist /* OTHER */
1470 (
1471 customer_trx_line_id,
1472 customer_trx_id,
1473 code_combination_id,
1474 set_of_books_id,
1475 account_class,
1476 account_set_flag,
1477 percent,
1478 amount,
1479 acctd_amount,
1480 gl_date,
1481 cust_trx_line_salesrep_id,
1482 request_id,
1483 created_by,
1484 creation_date,
1485 last_updated_by,
1486 last_update_date,
1487 program_application_id,
1488 program_id,
1489 program_update_date,
1490 posting_control_id,
1491 original_gl_date,
1492 cust_trx_line_gl_dist_id,
1493 rec_offset_flag, /* Bug 2150541 */
1494 org_id
1495 )
1496 SELECT
1497 lines.customer_trx_line_id,
1498 lines.customer_trx_id,
1499 psum.code_combination_id,
1500 arp_standard.sysparm.set_of_books_id,
1501 psum.account_class,
1502 'N', /* account_set_flag */
1503 ROUND((DECODE(psum.account_class,
1504 'SUSPENSE', (lines.extended_amount -
1505 lines.revenue_amount),
1506 decode(lines.revenue_amount,0,1,lines.revenue_amount)) /
1507 DECODE(psum.account_class,
1508 'SUSPENSE',decode((lines.extended_amount -
1509 lines.revenue_amount),0,1,
1510 (lines.extended_amount -
1511 lines.revenue_amount)),
1512 DECODE(lines.extended_amount,
1513 0,1,
1514 lines.extended_amount)) /*3550426*/
1515 ) * psum.percent, 4
1516 ), /* percent */
1520 'SUSPENSE', (lines.extended_amount -
1517 DECODE(fc.minimum_accountable_unit,
1518 NULL, ROUND( ((psum.percent / 100) *
1519 DECODE(psum.account_class,
1521 lines.revenue_amount),
1522 lines.revenue_amount)), fc.precision),
1523 ROUND( ((psum.percent / 100) *
1524 DECODE(psum.account_class,
1525 'SUSPENSE', (lines.extended_amount -
1526 lines.revenue_amount),
1527 lines.revenue_amount)) /
1528 fc.minimum_accountable_unit) *
1529 fc.minimum_accountable_unit
1530 ), /* amount */
1531 DECODE(p_bmau,
1532 NULL, ROUND(
1533 NVL(trx.exchange_rate, 1) *
1534 DECODE(fc.minimum_accountable_unit,
1535 NULL, ROUND( ((psum.percent / 100) *
1536 DECODE(psum.account_class,
1537 'SUSPENSE',
1538 (lines.extended_amount -
1539 lines.revenue_amount),
1540 lines.revenue_amount)),
1541 fc.precision),
1542 ROUND( ((psum.percent / 100) *
1543 DECODE(psum.account_class,
1544 'SUSPENSE',
1545 (lines.extended_amount -
1546 lines.revenue_amount),
1547 lines.revenue_amount)) /
1548 fc.minimum_accountable_unit) *
1549 fc.minimum_accountable_unit),
1550 p_base_precision),
1551 ROUND(
1552 ( NVL(trx.exchange_rate, 1) *
1553 DECODE(fc.minimum_accountable_unit,
1554 NULL, ROUND( ((psum.percent / 100) *
1555 DECODE(psum.account_class,
1556 'SUSPENSE',
1557 (lines.extended_amount -
1558 lines.revenue_amount),
1559 lines.revenue_amount)),
1560 fc.precision),
1561 ROUND( ((psum.percent / 100) *
1562 DECODE(psum.account_class,
1563 'SUSPENSE',
1564 (lines.extended_amount -
1565 lines.revenue_amount),
1566 lines.revenue_amount)) /
1567 fc.minimum_accountable_unit) *
1568 fc.minimum_accountable_unit)) /
1569 p_bmau) *
1570 p_bmau), /* acctd_amount */
1571 DECODE(rec.gl_date,
1572 NULL, NULL,
1573 assign_gl_rec(rec.gl_date)
1574 ), /* derived gl_date */
1575 DECODE(psum.customer_trx_id, trx.customer_trx_id,
1576 psum.cust_trx_line_salesrep_id,
1577 cmsrep.cust_trx_line_salesrep_id), /* salescred ID */
1578 arp_standard.profile.request_id,
1579 arp_standard.profile.user_id,
1580 sysdate,
1581 arp_standard.profile.user_id,
1582 sysdate,
1583 arp_standard.application_id,
1584 arp_standard.profile.program_id,
1585 sysdate,
1586 -3, /* posting_control_id */
1587 NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
1588 ra_cust_trx_line_gl_dist_s.NEXTVAL,
1589 /* Bug 2150541 */
1590 DECODE(psum.account_class, 'UNEARN', 'Y',
1591 'UNBILL', 'Y',
1592 NULL) ,
1593 trx.org_id
1594 FROM
1595 ra_customer_trx_lines psum_lines,
1596 ra_customer_trx psum_trx,
1597 ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
1598 ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
1599 ra_customer_trx_lines lines,
1600 ra_cust_trx_line_gl_dist rec, /* model receivable account */
1601 fnd_currencies fc,
1602 ra_customer_trx trx
1603 WHERE
1604 trx.customer_trx_id = p_trx_id
1605 AND trx.complete_flag = 'Y'
1606 AND fc.currency_code = trx.invoice_currency_code
1607 AND rec.customer_trx_id = trx.customer_trx_id
1608 AND rec.account_class = 'REC'
1609 AND rec.latest_rec_flag = 'Y'
1610 AND rec.customer_trx_line_id IS NULL
1614 AND psum_lines.customer_trx_line_id = psum.customer_trx_line_id
1611 AND lines.customer_trx_id = trx.customer_trx_id
1612 AND lines.autorule_complete_flag||'' = 'N'
1613 AND psum_trx.customer_trx_id = psum.customer_trx_id
1615 AND psum.account_class IN
1616 (
1617 'SUSPENSE'||
1618 DECODE(lines.extended_amount - NVL(lines.revenue_amount, 0),
1619 0, 'X',
1620 NULL),
1621 DECODE(trx.invoicing_rule_id,
1622 -2, 'UNEARN',
1623 -3, 'UNBILL')
1624 )
1625 AND psum.customer_trx_line_id =
1626 (SELECT
1627 DECODE(COUNT(cust_trx_line_gl_dist_id),
1628 0, NVL(lines.previous_customer_trx_line_id,
1629 lines.customer_trx_line_id),
1630 lines.customer_trx_line_id)
1631 FROM
1632 ra_cust_trx_line_gl_dist subdist2
1633 WHERE
1634 subdist2.customer_trx_line_id = lines.customer_trx_line_id
1635 AND subdist2.account_set_flag = 'Y'
1636 AND subdist2.gl_date IS NULL
1637 AND ROWNUM < 2
1638 )
1639 /* Bug 2899714 */
1640 AND cmsrep.prev_cust_trx_line_salesrep_id (+) =
1641 psum.cust_trx_line_salesrep_id
1642 AND cmsrep.customer_trx_id (+) = p_trx_id
1643 /* Bug 4602892 - avoid cartesian product for CMs
1644 w/ multiple lines against 1 invoice line. */
1645 AND lines.customer_trx_line_id = DECODE(lines.previous_customer_trx_id,
1646 NULL, lines.customer_trx_line_id,
1647 NVL(cmsrep.customer_trx_line_id,
1648 lines.customer_trx_line_id))
1649 /* Bug 2899714 - removed RELEASE 9 code */
1650 AND psum.account_set_flag = 'Y'
1651 AND NOT EXISTS
1652 (
1653 SELECT
1654 'plug sum account exists'
1655 FROM
1656 ra_cust_trx_line_gl_dist subdist
1657 WHERE
1658 subdist.account_class IN
1659 ( 'SUSPENSE', DECODE(trx.invoicing_rule_id,
1660 -2, 'UNEARN',
1661 -3, 'UNBILL')
1662 )
1663 AND subdist.customer_trx_line_id = lines.customer_trx_line_id
1664 AND subdist.account_set_flag = 'N'
1665 AND subdist.rec_offset_flag = 'Y');
1666
1667 l_rows := sql%rowcount;
1668
1669 /*3550426 */
1670 ELSE
1671
1672 INSERT INTO ra_cust_trx_line_gl_dist /* OTHER */
1673 (
1674 customer_trx_line_id,
1675 customer_trx_id,
1676 code_combination_id,
1677 set_of_books_id,
1678 account_class,
1679 account_set_flag,
1680 percent,
1681 amount,
1682 acctd_amount,
1683 gl_date,
1684 cust_trx_line_salesrep_id,
1685 request_id,
1686 created_by,
1687 creation_date,
1688 last_updated_by,
1689 last_update_date,
1690 program_application_id,
1691 program_id,
1692 program_update_date,
1693 posting_control_id,
1694 original_gl_date,
1695 cust_trx_line_gl_dist_id,
1696 rec_offset_flag, /* Bug 2150541 */
1697 org_id
1698 )
1699 SELECT
1700 lines.customer_trx_line_id,
1701 lines.customer_trx_id,
1702 psum.code_combination_id,
1703 arp_standard.sysparm.set_of_books_id,
1704 psum.account_class,
1705 'N', /* account_set_flag */
1706 ROUND((DECODE(psum.account_class,
1707 'SUSPENSE', (lines.extended_amount -
1708 lines.revenue_amount),
1709 decode(lines.revenue_amount,0,1,lines.revenue_amount)) /
1710 DECODE(psum.account_class,
1711 'SUSPENSE', decode((lines.extended_amount -
1712 lines.revenue_amount),0,1,
1713 (lines.extended_amount -
1714 lines.revenue_amount)),
1715 DECODE(lines.extended_amount,
1716 0,1,
1717 lines.extended_amount))
1718 ) * psum.percent, 4
1719 ), /* percent */
1720 DECODE(fc.minimum_accountable_unit,
1721 NULL, ROUND( ((psum.percent / 100) *
1722 DECODE(psum.account_class,
1723 'SUSPENSE', (lines.extended_amount -
1724 lines.revenue_amount),
1725 lines.revenue_amount)), fc.precision),
1726 ROUND( ((psum.percent / 100) *
1730 lines.revenue_amount)) /
1727 DECODE(psum.account_class,
1728 'SUSPENSE', (lines.extended_amount -
1729 lines.revenue_amount),
1731 fc.minimum_accountable_unit) *
1732 fc.minimum_accountable_unit
1733 ), /* amount */
1734 DECODE(p_bmau,
1735 NULL, ROUND(
1736 NVL(trx.exchange_rate, 1) *
1737 DECODE(fc.minimum_accountable_unit,
1738 NULL, ROUND( ((psum.percent / 100) *
1739 DECODE(psum.account_class,
1740 'SUSPENSE',
1741 (lines.extended_amount -
1742 lines.revenue_amount),
1743 lines.revenue_amount)),
1744 fc.precision),
1745 ROUND( ((psum.percent / 100) *
1746 DECODE(psum.account_class,
1747 'SUSPENSE',
1748 (lines.extended_amount -
1749 lines.revenue_amount),
1750 lines.revenue_amount)) /
1751 fc.minimum_accountable_unit) *
1752 fc.minimum_accountable_unit),
1753 p_base_precision),
1754 ROUND(
1755 ( NVL(trx.exchange_rate, 1) *
1756 DECODE(fc.minimum_accountable_unit,
1757 NULL, ROUND( ((psum.percent / 100) *
1758 DECODE(psum.account_class,
1759 'SUSPENSE',
1760 (lines.extended_amount -
1761 lines.revenue_amount),
1762 lines.revenue_amount)),
1763 fc.precision),
1764 ROUND( ((psum.percent / 100) *
1765 DECODE(psum.account_class,
1766 'SUSPENSE',
1767 (lines.extended_amount -
1768 lines.revenue_amount),
1769 lines.revenue_amount)) /
1770 fc.minimum_accountable_unit) *
1771 fc.minimum_accountable_unit)) /
1772 p_bmau) *
1773 p_bmau), /* acctd_amount */
1774 DECODE(rec.gl_date,
1775 NULL, NULL,
1776 assign_gl_rec(rec.gl_date)
1777 ), /* derived gl_date */
1778 DECODE(psum.customer_trx_id, trx.customer_trx_id,
1779 psum.cust_trx_line_salesrep_id,
1780 cmsrep.cust_trx_line_salesrep_id), /* salescred ID */
1781 arp_standard.profile.request_id,
1782 arp_standard.profile.user_id,
1783 sysdate,
1784 arp_standard.profile.user_id,
1785 sysdate,
1786 arp_standard.application_id,
1787 arp_standard.profile.program_id,
1788 sysdate,
1789 -3, /* posting_control_id */
1790 NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
1791 ra_cust_trx_line_gl_dist_s.NEXTVAL,
1792 /* Bug 2150541 */
1793 DECODE(psum.account_class, 'UNEARN', 'Y',
1794 'UNBILL', 'Y',
1795 NULL),
1796 trx.org_id
1797 FROM
1798 ra_customer_trx_lines psum_lines,
1799 ra_customer_trx psum_trx,
1800 ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
1801 ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
1802 ra_customer_trx_lines lines,
1803 ra_cust_trx_line_gl_dist rec, /* model receivable account */
1804 fnd_currencies fc,
1805 ra_customer_trx trx
1806 WHERE
1807 trx.customer_trx_id = p_trx_id
1808 AND trx.complete_flag = 'Y'
1809 AND fc.currency_code = trx.invoice_currency_code
1810 AND rec.customer_trx_id = trx.customer_trx_id
1811 AND rec.account_class = 'REC'
1812 AND rec.latest_rec_flag = 'Y'
1813 AND rec.customer_trx_line_id IS NULL
1814 AND lines.customer_trx_id = trx.customer_trx_id
1815 AND lines.autorule_complete_flag||'' = 'N'
1816 AND psum_trx.customer_trx_id = psum.customer_trx_id
1817 AND psum_lines.customer_trx_line_id = psum.customer_trx_line_id
1818 AND psum.customer_trx_line_id = lines.previous_customer_trx_line_id
1819 AND psum.account_set_flag = 'N'
1823 )
1820 AND ( ( psum.account_class IN ('UNEARN', 'UNBILL')
1821 AND
1822 psum.rec_offset_flag = 'Y'
1824 OR
1825 ( psum.account_class = 'SUSPENSE'
1826 AND
1827 psum.rec_offset_flag IS NULL
1828 )
1829 )
1830 AND cmsrep.prev_cust_trx_line_salesrep_id (+) =
1831 psum.cust_trx_line_salesrep_id
1832 AND cmsrep.customer_trx_id (+) = p_trx_id
1833 /* Bug 4602892 - avoid cartesian product for CMs
1834 w/ multiple lines against 1 invoice line. */
1835 AND lines.customer_trx_line_id = DECODE(lines.previous_customer_trx_id,
1836 NULL, lines.customer_trx_line_id,
1837 NVL(cmsrep.customer_trx_line_id,
1838 lines.customer_trx_line_id))
1839 AND NOT EXISTS
1840 (
1841 SELECT
1842 'plug sum account exists'
1843 FROM
1844 ra_cust_trx_line_gl_dist subdist
1845 WHERE
1846 subdist.account_class IN
1847 ( 'SUSPENSE', DECODE(trx.invoicing_rule_id,
1848 -2, 'UNEARN',
1849 -3, 'UNBILL')
1850 )
1851 AND subdist.customer_trx_line_id = lines.customer_trx_line_id
1852 AND subdist.account_set_flag = 'N'
1853 AND subdist.rec_offset_flag = 'Y');
1854
1855 l_rows := sql%rowcount;
1856
1857 END IF;
1858
1859 IF PG_DEBUG in ('Y', 'C') THEN
1860 arp_standard.debug( 'Other plug lines inserted: ' ||
1861 l_rows);
1862 arp_standard.debug( 'arp_auto_rule.create_other_plug()- ' ||
1863 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1864 END IF;
1865
1866 /*Bug 5450534 FP of bug 5260489*/
1867
1868 IF (l_rows = 0) THEN
1869 arp_rounding.SET_REC_OFFSET_FLAG(l_prev_cust_trx_id,l_request_id,l_result);
1870
1871 /* 6782405 - basically, we try setting rec_offset if create_other_plug
1872 inserts no rows. To re-execute this function, we have to return -99.
1873 The result values are -1 = None created, 0 = None needed, 1 = rows created */
1874 IF l_result = 1
1875 THEN
1876 /* We set rof on some lines, so make the second call.
1877
1878 If result is 0, nothing was needed, and if it was -1, then we
1879 have some sort of problem where we can't set rof when we think
1880 one is needed */
1881 l_rows := -99;
1882 ELSIF l_result = -1
1883 THEN
1884 /* This is an error condition in that we could not set rof and
1885 the program felt that they were required */
1886 RAISE NO_ROF_EXCEPTION;
1887 ELSE
1888 /* l_result was 0, no action required */
1889 NULL;
1890 END IF;
1891 END IF;
1892
1893 RETURN( l_rows );
1894
1895 EXCEPTION
1896 WHEN NO_ROF_EXCEPTION THEN
1897 IF PG_DEBUG in ('Y', 'C') THEN
1898 arp_standard.debug( 'EXCEPTION: arp_auto_rule.create_other_plug()');
1899 arp_standard.debug( 'set_rec_offset_flag unable to set flag properly');
1900 arp_standard.debug( 'arp_auto_rule.create_other_plug()- ' ||
1901 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1902 END IF;
1903 RETURN( -1 );
1904 WHEN OTHERS THEN
1905
1906 IF PG_DEBUG in ('Y', 'C') THEN
1907 arp_standard.debug( 'EXCEPTION: arp_auto_rule.create_other_plug()');
1908 arp_standard.debug( SQLERRM);
1909 arp_standard.debug( 'arp_auto_rule.create_other_plug()- ' ||
1910 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1911 END IF;
1912
1913 RETURN( -1 );
1914
1915 END create_other_plug;
1916
1917 /*-------------------------------------------------------------------------+
1918 | PRIVATE FUNCTION |
1919 | create_other_tax |
1920 | |
1921 | DESCRIPTION |
1922 | |
1923 | REQUIRES |
1924 | |
1925 | RETURNS |
1926 | Row count of number of records inserted. |
1927 | |
1928 | NOTES |
1929 | |
1930 | EXAMPLE |
1931 | |
1932 | MODIFICATION HISTORY |
1933 | 22-JAN-93 Nigel Smith created. |
1934 | 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
1935 | 20-MAR-98 S.Jandyala Modified the function to create |
1936 | revenue account assignments by trx_id |
1940 p_trx_id IN NUMBER,
1937 +-------------------------------------------------------------------------*/
1938
1939 FUNCTION create_other_tax(
1941 p_base_precision IN NUMBER,
1942 p_bmau IN NUMBER,
1943 p_ignore_rule_flag IN VARCHAR2 DEFAULT NULL)
1944
1945 RETURN NUMBER IS
1946
1947 /* added for mrc */
1948 l_rows NUMBER;
1949 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
1950 l_ignore_rule_flag VARCHAR2(1);
1951
1952 BEGIN
1953
1954 IF PG_DEBUG in ('Y', 'C') THEN
1955 arp_standard.debug( 'arp_auto_rule.create_other_tax()+ ' ||
1956 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1957 END IF;
1958
1959 /* 7131147 - ignore autorule_complete_flag on an
1960 explicit (external) call */
1961 IF p_ignore_rule_flag = 'Y'
1962 THEN
1963 l_ignore_rule_flag := 'Y';
1964 ELSE
1965 l_ignore_rule_flag := 'N';
1966 END IF;
1967
1968 INSERT INTO ra_cust_trx_line_gl_dist /* TAX Lines */
1969 (
1970 customer_trx_line_id,
1971 customer_trx_id,
1972 code_combination_id,
1973 set_of_books_id,
1974 account_class,
1975 account_set_flag,
1976 percent,
1977 amount,
1978 acctd_amount,
1979 gl_date,
1980 cust_trx_line_salesrep_id,
1981 request_id,
1982 created_by,
1983 creation_date,
1984 last_updated_by,
1985 last_update_date,
1986 program_application_id,
1987 program_id,
1988 program_update_date,
1989 posting_control_id,
1990 original_gl_date,
1991 cust_trx_line_gl_dist_id,
1992 collected_tax_ccid,
1993 org_id
1994 )
1995 SELECT
1996 tax_line.customer_trx_line_id,
1997 tax_line.customer_trx_id,
1998 tax.code_combination_id,
1999 arp_standard.sysparm.set_of_books_id,
2000 tax.account_class,
2001 'N',
2002 tax.percent,
2003 DECODE(fc.minimum_accountable_unit,
2004 NULL, ROUND( ((tax.percent / 100) *
2005 tax_line.extended_amount), fc.precision),
2006 ROUND( ((tax.percent / 100) *
2007 tax_line.extended_amount) /
2008 fc.minimum_accountable_unit) *
2009 fc.minimum_accountable_unit), /* amount */
2010 DECODE(p_bmau,
2011 NULL, ROUND(
2012 NVL(trx.exchange_rate, 1) *
2013 DECODE(fc.minimum_accountable_unit,
2014 NULL, ROUND( ((tax.percent / 100) *
2015 tax_line.extended_amount),
2016 fc.precision),
2017 ROUND( ((tax.percent / 100) *
2018 tax_line.extended_amount) /
2019 fc.minimum_accountable_unit) *
2020 fc.minimum_accountable_unit),
2021 p_base_precision),
2022 ROUND(
2023 ( NVL(trx.exchange_rate, 1) *
2024 DECODE(fc.minimum_accountable_unit,
2025 NULL, ROUND( ((tax.percent / 100) *
2026 tax_line.extended_amount),
2027 fc.precision),
2028 ROUND( ((tax.percent / 100) *
2029 tax_line.extended_amount) /
2030 fc.minimum_accountable_unit) *
2031 fc.minimum_accountable_unit)) /
2032 p_bmau) *
2033 p_bmau), /* acctd_amount */
2034 DECODE(rec.gl_date,
2035 NULL, NULL,
2036 assign_gl_rec(rec.gl_date)
2037 ), /* derived gl_date */
2038 tax.cust_trx_line_salesrep_id,
2039 arp_standard.profile.request_id,
2040 arp_standard.profile.user_id,
2041 sysdate,
2042 arp_standard.profile.user_id,
2043 sysdate,
2044 arp_standard.application_id,
2045 arp_standard.profile.program_id,
2046 sysdate,
2047 -3,
2048 NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
2049 ra_cust_trx_line_gl_dist_s.NEXTVAL,
2050 tax.collected_tax_ccid,
2051 trx.org_id
2052 FROM
2053 ra_customer_trx model_trx,
2054 ra_cust_trx_line_gl_dist tax,
2055 ra_customer_trx_lines tax_line,
2056 ra_customer_trx_lines line_line,
2057 ra_cust_trx_line_gl_dist rec,
2058 fnd_currencies fc,
2059 ra_customer_trx trx
2060 WHERE
2061 trx.customer_trx_id = p_trx_id
2062 AND trx.complete_flag = 'Y'
2066 AND rec.latest_rec_flag = 'Y'
2063 AND fc.currency_code = trx.invoice_currency_code
2064 AND rec.customer_trx_id = trx.customer_trx_id
2065 AND rec.account_class = 'REC'
2067 AND line_line.customer_trx_id = rec.customer_trx_id
2068 AND (line_line.autorule_complete_flag||'' = 'N'
2069 OR l_ignore_rule_flag = 'Y')
2070 AND tax_line.link_to_cust_trx_line_id = line_line.customer_trx_line_id
2071 AND tax_line.line_type = 'TAX'
2072 AND tax_line.customer_trx_id + 0 = line_line.customer_trx_id
2073 AND trx.customer_trx_id = tax_line.customer_trx_id
2074 AND model_trx.customer_trx_id = tax.customer_trx_id
2075 AND tax.account_class = 'TAX'
2076 AND tax.customer_trx_line_id =
2077 (SELECT
2078 DECODE( COUNT(cust_trx_line_gl_dist_id),
2079 0, NVL(tax_line.previous_customer_trx_line_id,
2080 tax_line.customer_trx_line_id),
2081 tax_line.customer_trx_line_id)
2082 FROM
2083 ra_cust_trx_line_gl_dist subdist2
2084 WHERE
2085 subdist2.customer_trx_line_id=tax_line.customer_trx_line_id
2086 AND subdist2.account_set_flag = 'Y'
2087 AND subdist2.gl_date IS NULL
2088 AND ROWNUM < 2
2089 )
2090 AND ( tax.account_set_flag = 'Y'
2091 OR
2092 model_trx.created_from IN ('RAXTRX_REL9', 'FORM_REL9')
2093 )
2094 AND NOT EXISTS
2095 (SELECT
2096 'tax account exists'
2097 FROM
2098 ra_cust_trx_line_gl_dist subdist
2099 WHERE
2100 tax_line.customer_trx_line_id = subdist.customer_trx_line_id
2101 AND subdist.account_set_flag = 'N'
2102 AND subdist.gl_date IS NOT NULL
2103 AND subdist.account_class = 'TAX'
2104 );
2105
2106 l_rows := sql%rowcount;
2107
2108 IF PG_DEBUG in ('Y', 'C') THEN
2109 arp_standard.debug( 'Tax lines inserted: ' ||
2110 l_rows);
2111 arp_standard.debug( 'arp_auto_rule.create_other_tax()- ' ||
2112 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2113 END IF;
2114
2115 RETURN( l_rows );
2116
2117 EXCEPTION
2118 WHEN OTHERS THEN
2119
2120 IF PG_DEBUG in ('Y', 'C') THEN
2121 arp_standard.debug( 'EXCEPTION: arp_auto_rule.create_other_tax()');
2122 arp_standard.debug( SQLERRM);
2123 arp_standard.debug( 'arp_auto_rule.create_other_tax()- ' ||
2124 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2125 END IF;
2126
2127 RETURN( -1 );
2128
2129 END create_other_tax;
2130
2131 /*-------------------------------------------------------------------------+
2132 | PRIVATE FUNCTION |
2133 | create_other_freight |
2134 | |
2135 | DESCRIPTION |
2136 | |
2137 | REQUIRES |
2138 | |
2139 | RETURNS |
2140 | Row count of number of records inserted. |
2141 | |
2142 | NOTES |
2143 | |
2144 | EXAMPLE |
2145 | |
2146 | MODIFICATION HISTORY |
2147 | 22-JAN-93 Nigel Smith created. |
2148 | 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
2149 | 20-MAR-98 S.Jandyala Modified the function to create |
2150 | revenue account assignments by trx_id |
2151 +-------------------------------------------------------------------------*/
2152
2153 FUNCTION create_other_freight(
2154 p_trx_id IN NUMBER,
2155 p_base_precision IN NUMBER,
2156 p_bmau IN NUMBER)
2157
2158 RETURN NUMBER IS
2159
2160 /* added for mrc */
2161 l_rows NUMBER;
2162 l_gl_dist_key_value_list gl_ca_utility_pkg.r_key_value_arr;
2163
2164 BEGIN
2165
2166 IF PG_DEBUG in ('Y', 'C') THEN
2167 arp_standard.debug( 'arp_auto_rule.create_other_freight()+ ' ||
2168 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2169 END IF;
2170
2171 INSERT INTO ra_cust_trx_line_gl_dist /* FREIGHT Lines */
2172 (
2173 customer_trx_line_id,
2174 customer_trx_id,
2175 code_combination_id,
2176 set_of_books_id,
2177 account_class,
2181 acctd_amount,
2178 account_set_flag,
2179 percent,
2180 amount,
2182 gl_date,
2183 cust_trx_line_salesrep_id,
2184 request_id,
2185 created_by,
2186 creation_date,
2187 last_updated_by,
2188 last_update_date,
2189 program_application_id,
2190 program_id,
2191 program_update_date,
2192 posting_control_id,
2193 original_gl_date,
2194 cust_trx_line_gl_dist_id,
2195 org_id
2196 )
2197 SELECT
2198 lines.customer_trx_line_id,
2199 lines.customer_trx_id,
2200 freight.code_combination_id,
2201 arp_standard.sysparm.set_of_books_id,
2202 freight.account_class,
2203 'N',
2204 freight.percent,
2205 DECODE(fc.minimum_accountable_unit,
2206 NULL, ROUND( ((freight.percent / 100) *
2207 lines.extended_amount), fc.precision),
2208 ROUND( ((freight.percent / 100) *
2209 lines.extended_amount) /
2210 fc.minimum_accountable_unit) *
2211 fc.minimum_accountable_unit), /* amount */
2212 DECODE(p_bmau,
2213 NULL, ROUND(
2214 NVL(trx.exchange_rate, 1) *
2215 DECODE(fc.minimum_accountable_unit,
2216 NULL, ROUND( ((freight.percent / 100) *
2217 lines.extended_amount),
2218 fc.precision),
2219 ROUND( ((freight.percent / 100) *
2220 lines.extended_amount) /
2221 fc.minimum_accountable_unit) *
2222 fc.minimum_accountable_unit),
2223 p_base_precision),
2224 ROUND(
2225 ( NVL(trx.exchange_rate, 1) *
2226 DECODE(fc.minimum_accountable_unit,
2227 NULL, ROUND( ((freight.percent / 100) *
2228 lines.extended_amount),
2229 fc.precision),
2230 ROUND( ((freight.percent / 100) *
2231 lines.extended_amount) /
2232 fc.minimum_accountable_unit) *
2233 fc.minimum_accountable_unit)) /
2234 p_bmau) *
2235 p_bmau), /* acctd_amount */
2236 rec.gl_date,
2237 freight.cust_trx_line_salesrep_id,
2238 arp_standard.profile.request_id,
2239 arp_standard.profile.user_id,
2240 sysdate,
2241 arp_standard.profile.user_id,
2242 sysdate,
2243 arp_standard.application_id,
2244 arp_standard.profile.program_id,
2245 sysdate,
2246 -3,
2247 NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
2248 ra_cust_trx_line_gl_dist_s.NEXTVAL,
2249 trx.org_id
2250 FROM
2251 ra_customer_trx model_trx,
2252 ra_cust_trx_line_gl_dist freight,
2253 ra_customer_trx_lines lines,
2254 ra_cust_trx_line_gl_dist rec,
2255 fnd_currencies fc,
2256 ra_customer_trx trx
2257 WHERE
2258 trx.customer_trx_id = p_trx_id
2259 AND trx.complete_flag = 'Y'
2260 AND fc.currency_code = trx.invoice_currency_code
2261 AND rec.customer_trx_id = trx.customer_trx_id
2262 AND rec.account_class = 'REC'
2263 AND rec.latest_rec_flag = 'Y'
2264 AND rec.customer_trx_line_id IS NULL
2265 AND EXISTS
2266 (
2267 SELECT 1
2268 FROM ra_customer_trx_lines line_line
2269 WHERE line_line.customer_trx_id = trx.customer_trx_id
2270 AND line_line.autorule_complete_flag||'' = 'N'
2271 )
2272 AND lines.customer_trx_id = rec.customer_trx_id
2273 AND lines.line_type = 'FREIGHT'
2274 AND model_trx.customer_trx_id = freight.customer_trx_id
2275 /* for CMs: use the invoice's account set
2276 if USE_INV_ACCT_FOR_CM_FLAG = Yes. */
2277 AND freight.customer_trx_line_id =
2278 (SELECT
2279 DECODE( COUNT(cust_trx_line_gl_dist_id),
2280 0, NVL(lines.previous_customer_trx_line_id,
2281 lines.customer_trx_line_id),
2282 lines.customer_trx_line_id)
2283 FROM
2284 ra_cust_trx_line_gl_dist subdist2
2285 WHERE
2286 subdist2.customer_trx_line_id = lines.customer_trx_line_id
2287 AND subdist2.account_set_flag = 'Y'
2288 AND subdist2.gl_date IS NULL
2289 AND ROWNUM < 2
2290 )
2291 AND freight.account_class = 'FREIGHT'
2292 AND ( freight.account_set_flag = 'Y'
2296 AND NOT EXISTS
2293 OR
2294 model_trx.created_from IN ( 'RAXTRX_REL9', 'FORM_REL9')
2295 )
2297 (SELECT
2298 'freight account exists'
2299 FROM
2300 ra_cust_trx_line_gl_dist subdist
2301 WHERE
2302 subdist.customer_trx_line_id = lines.customer_trx_line_id
2303 AND subdist.account_set_flag = 'N'
2304 AND subdist.gl_date IS NOT NULL
2305 AND subdist.account_class = 'FREIGHT'
2306 );
2307
2308 l_rows := sql%rowcount;
2309
2310 IF PG_DEBUG in ('Y', 'C') THEN
2311 arp_standard.debug( 'Freight lines inserted: ' ||
2312 l_rows);
2313 arp_standard.debug( 'arp_auto_rule.create_other_freight()- ' ||
2314 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2315 END IF;
2316
2317 RETURN( l_rows );
2318
2319 EXCEPTION
2320 WHEN OTHERS THEN
2321
2322 IF PG_DEBUG in ('Y', 'C') THEN
2323 arp_standard.debug( 'EXCEPTION: arp_auto_rule.create_other_freight()');
2324 arp_standard.debug( SQLERRM);
2325 arp_standard.debug( 'arp_auto_rule.create_other_freight()- ' ||
2326 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2327 END IF;
2328
2329 RETURN( -1 );
2330
2331 END create_other_freight;
2332
2333 /*-------------------------------------------------------------------------+
2334 | PRIVATE FUNCTION |
2335 | update_durations |
2336 | |
2337 | DESCRIPTION |
2338 | Increment autorule_duration_processed and autorule_complete_flag for |
2339 | lines for which we have created distributions. |
2340 | |
2341 | REQUIRES |
2342 | |
2343 | RETURNS |
2344 | row count of number of records updated. |
2345 | |
2346 | NOTES |
2347 | |
2348 | EXAMPLE |
2349 | |
2350 | MODIFICATION HISTORY |
2351 | 22-JAN-93 Nigel Smith created. |
2352 | 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
2353 | 20-MAR-98 S.Jandyala Modified the function to update |
2354 | by trx_id |
2355 | 05-OCT-00 Jon Beckett BUG 1551488 Deferred revenue |
2356 | 19-SEP-02 J Beckett Bug 2560048 RAM-C - revenue can be |
2357 | deferred on arrears or deferred due to |
2358 | collectibility decision |
2359 | 09-OCT-02 J Beckett Bug 2560048: above only applies to |
2360 | advance invoicing rule |
2361 | 29-JAN-03 O RASHID Added the fix for bug # 2774432. |
2362 | credit memos on ramc invoices with |
2363 | rules should stamp the |
2364 | autorule_complete_flag. |
2365 | 19-FEB-03 M Raymond Bug 2584263 - redesigned logic in
2366 | this function to always update any
2367 | transactions where distributions were
2368 | created.
2369 | 07-MAR-04 M Raymond Bug 3416070 - created branched logic
2370 | that utilizes request_id when it is
2371 | present or skips it when it is null
2372 +-------------------------------------------------------------------------*/
2373
2374
2375 FUNCTION update_durations( p_trx_id IN NUMBER )
2376
2377 RETURN NUMBER IS
2378 l_rows NUMBER;
2379
2380 BEGIN
2381 IF PG_DEBUG in ('Y', 'C') THEN
2382
2383 arp_standard.debug( 'arp_auto_rule.update_durations()+ ' ||
2384 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2385 END IF;
2386
2387 /* This statement needs to update the autorule_complete_flag
2388 and autorule_duration_processed for any transactions that
2389 were picked up by the rev rec process. This includes:
2390
2391 o INV w/ rules
2392 o INV w/ deferred rules
2393 o INV that are non-collectible
2394 o CMs targeting above transactions
2395 o Either type of transaction if tax or customer is modified
2396
2397 We can do this based on the existance of a row with the
2398 rev rec request_id because, at this time, no transaction will
2402 other reasons, process.
2399 ever intentionally pass through revenue recognition more
2400 than once. The EXISTS clause is really just a safety net
2401 to prevent us from flagging transactions that did not, for
2403 */
2404
2405 /* Bug 3416070/3403067 - Modified update to only be dependent on
2406 request_id when one is set. ARP_ALLOCATIONS_PKG calls this
2407 code without one. */
2408 /*4578927 suppressed the index on autorule_complete_flag*/
2409 IF arp_standard.profile.request_id IS NOT NULL
2410 THEN
2411 /* Existing logic - request_id is set */
2412
2413 update ra_customer_trx_lines ul
2414 set autorule_complete_flag = null,
2415 autorule_duration_processed =
2416 accounting_rule_duration,
2417 last_update_date = sysdate,
2418 last_updated_by = arp_standard.profile.user_id,
2419 program_application_id = arp_standard.application_id,
2420 program_update_date = sysdate,
2421 program_id = arp_standard.profile.program_id
2422 where customer_trx_id = p_trx_id
2423 and autorule_complete_flag||'' = 'N'
2424 and (exists (select 'at least one distribution'
2425 from ra_cust_trx_line_gl_dist gl
2426 where gl.customer_trx_line_id = ul.customer_trx_line_id
2427 and gl.account_set_flag = 'N'
2428 and gl.request_id = arp_standard.profile.request_id)
2429 or exists (select 'a distribution for a linked line'
2430 from ra_customer_trx_lines tl,
2431 ra_cust_trx_line_gl_dist tgl
2432 where tl.customer_trx_id = ul.customer_trx_id
2433 and tl.link_to_cust_trx_line_id = ul.customer_trx_line_id
2434 and tgl.customer_trx_line_id = tl.customer_trx_line_id
2435 and tgl.account_set_flag = 'N'
2436 and tgl.request_id = arp_standard.profile.request_id));
2437
2438 ELSE
2439 /* Request_id is not set */
2440 update ra_customer_trx_lines ul
2441 set autorule_complete_flag = null,
2442 autorule_duration_processed =
2443 accounting_rule_duration,
2444 last_update_date = sysdate,
2445 last_updated_by = arp_standard.profile.user_id,
2446 program_application_id = arp_standard.application_id,
2447 program_update_date = sysdate,
2448 program_id = arp_standard.profile.program_id
2449 where customer_trx_id = p_trx_id
2450 and autorule_complete_flag||'' = 'N'
2451 and (exists (select 'at least one distribution'
2452 from ra_cust_trx_line_gl_dist gl
2453 where gl.customer_trx_line_id = ul.customer_trx_line_id
2454 and gl.account_set_flag = 'N')
2455 or exists (select 'a distribution for a linked line'
2456 from ra_customer_trx_lines tl,
2457 ra_cust_trx_line_gl_dist tgl
2458 where tl.customer_trx_id = ul.customer_trx_id
2459 and tl.link_to_cust_trx_line_id = ul.customer_trx_line_id
2460 and tgl.customer_trx_line_id = tl.customer_trx_line_id
2461 and tgl.account_set_flag = 'N'));
2462
2463 END IF;
2464
2465 l_rows := sql%rowcount;
2466
2467 IF (PG_DEBUG = 'Y') THEN
2468 arp_standard.debug(' rows updated: ' ||
2469 l_rows);
2470
2471 arp_standard.debug( 'arp_auto_rule.update_durations()- ' ||
2472 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS' || cr));
2473 END IF;
2474
2475 RETURN( l_rows );
2476
2477 EXCEPTION
2478 WHEN OTHERS THEN
2479
2480 arp_standard.debug('EXCEPTION: arp_auto_rule.update_durations()');
2481 arp_standard.debug(SQLERRM);
2482 arp_standard.debug( 'arp_auto_rule.update_durations()- ' ||
2483 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2484
2485 RETURN( -1 );
2486
2487 END update_durations;
2488
2489
2490 /*-------------------------------------------------------------------------+
2491 | PUBLIC FUNCTION |
2492 | |
2493 | create_distributions |
2494 | |
2495 | DESCRIPTION |
2496 | This function will generate General Ledger distributions for all |
2497 | transaction lines that have model accounts and have incomplete |
2498 | autorule expansions against them. |
2499 | |
2500 | REQUIRES |
2501 | user_id AOL who information |
2502 | request_id |
2503 | program_application_id |
2504 | program_id |
2505 | |
2506 | CALLS |
2507 | populate_glp_table |
2508 | create_assignments |
2512 | create_other_freight |
2509 | create_round |
2510 | create_other_receivable |
2511 | create_other_plug |
2513 | update_durations |
2514 | |
2515 | RETURNS |
2516 | stats structure, with rowcount for each major operation |
2517 | |
2518 | EXCEPTIONS RAISED |
2519 | None |
2520 | |
2521 | NOTES |
2522 | |
2523 | EXAMPLE |
2524 | |
2525 | stats := auto_rule.create_distributions( |
2526 | p_commit_at_end, |
2527 | p_debug_flag); |
2528 | |
2529 | MODIFICATION HISTORY |
2530 | 22-Jan-93 Nigel Smith Created. |
2531 | 04-Apr-98 Srini Jandyala Redesigned the function to create |
2532 | distribution by customer_trx_id. |
2533 | Removed gl_date range and create all |
2534 | distributions the first time it runs |
2535 | for a transaction with rules. |
2536 | 11-Sep-98 Ramakant Alat Added call to create_round and changed |
2537 | RECORD stat_type (added round) |
2538 | 15-MAY-02 M Raymond Bug 2150541 - Added parameter that
2539 | allows us to supress rounding call
2540 | This is specifically for diagnosing
2541 | rounding-related problems.
2542 | 08-JUL-02 M Raymond Bug 2399504 - Added parameter that
2543 | permits us to override the on-error-
2544 | halt behavior for bad lines. This
2545 | means that the only
2546 | remaining ungenerated transactions
2547 | after a run
2548 | (with this parameter set) would be those
2549 | that had some problem.
2550 | 01-OCT-02 M Raymond Bug 2552858 - Swapped positions of
2551 | create_other_rec and create_round to
2552 | make sure REC row exists when ROUND row
2553 | is created.
2554 | 25-FEB-03 M Raymond Bug 2649674 - Added logic to allow
2555 | report to end (WARNING) if transactions
2556 | were skipped based on p_continue_on_error
2557 | 17-SEP-04 M Raymond Bug 3879222 - Moved call-once logic around
2558 | populate_glp_table inside the routine.
2559 +-------------------------------------------------------------------------*/
2560
2561 FUNCTION create_distributions( p_commit IN VARCHAR2,
2562 p_debug IN VARCHAR2,
2563 p_trx_id IN NUMBER,
2564 p_suppress_round IN VARCHAR2,
2565 p_continue_on_error IN VARCHAR2)
2566 RETURN NUMBER IS
2567
2568 TYPE stat_type is RECORD -- Return statistics from create_distributions
2569 (
2570 assignments NUMBER := 0, -- Number of new rev distributions created
2571 round NUMBER := 0, -- Number of new round distributions created
2572 receivables NUMBER := 0, -- Number of new receivable distributions created
2573 tax NUMBER := 0, -- Number of new tax distributions created
2574 freight NUMBER := 0, -- Number of new freight distributions created
2575 plugs NUMBER := 0, -- Number of new plug distributions created
2576 durations NUMBER := 0 -- Number of durations updated.
2577 );
2578
2579 /* Cursor selects all the transactions for which distributions are not
2580 completely created. */
2581
2582 /* Bug 2133254 - Changed code from using a single c_trx cursor to
2583 using either c_trx or c_trx_no_id cursors. (in Bug 2122202,
2584 we had done this with dynamic cursors - but this caused the
2585 cursors to be reparsed. */
2586
2587 /* Bug 2399504 - Added the autorule_duration_processed
2588 condition to both cursors. This should help avoid
2589 problems with CMs that are not flagged properly */
2590
2591 /*Change for bug-5444411 to suppress index on autorule_complete_flag*/
2592 CURSOR c_trx IS
2593 SELECT
2594 ct.customer_trx_id,
2595 ct.trx_number
2596 FROM
2597 ra_customer_trx ct
2598 WHERE
2602 SELECT 'line needing dists'
2599 ct.complete_flag = 'Y'
2600 AND ct.customer_trx_id = p_trx_id
2601 AND EXISTS (
2603 FROM ra_customer_trx_lines ctl
2604 WHERE ctl.customer_trx_id = ct.customer_trx_id
2605 AND ctl.autorule_complete_flag||'' = 'N'
2606 AND (ctl.autorule_duration_processed <
2607 ctl.accounting_rule_duration OR
2608 ctl.autorule_duration_processed is NULL));
2609
2610 /* bug3282969 added nvl func for performance */
2611 CURSOR c_trx_no_id IS
2612 SELECT
2613 DISTINCT ctl.customer_trx_id,
2614 ct.trx_number
2615 FROM
2616 ra_customer_trx ct,
2617 ra_customer_trx_lines ctl
2618 WHERE
2619 ctl.autorule_complete_flag = 'N'
2620 AND nvl(ctl.autorule_duration_processed,-2) <
2621 nvl(ctl.accounting_rule_duration,-1)
2622 AND ct.customer_trx_id = ctl.customer_trx_id
2623 AND ct.complete_flag = 'Y';
2624
2625 stats STAT_TYPE;
2626 period_set_name VARCHAR(15);
2627 error_message FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE; -- Bug 4127356
2628 base_precision NUMBER;
2629 base_min_acc_unit NUMBER; -- base_minimum_accountable_unit
2630 num_round_err_corr NUMBER; -- num_rounding_errors_corrected
2631
2632 i NUMBER;
2633 trx_id NUMBER;
2634 trx_num VARCHAR(30);
2635 sum_dist_created NUMBER := 0;
2636 trx_dist_created NUMBER := 0;
2637 return_warning BOOLEAN := FALSE;
2638 --Bug#2750340
2639 l_xla_ev_rec arp_xla_events.xla_events_type;
2640
2641 BEGIN
2642
2643 IF (p_debug = 'Y')
2644 THEN
2645 arp_standard.enable_debug;
2646 END IF;
2647
2648 IF PG_DEBUG in ('Y', 'C') THEN
2649 arp_standard.debug( 'arp_auto_rule.create_distributions()+ ' ||
2650 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2651 arp_standard.debug( 'debug = '|| p_debug ||', commit = ' || p_commit);
2652 arp_standard.debug( 'round = ' || p_suppress_round || ', continue on error = ' ||
2653 p_continue_on_error);
2654 END IF;
2655
2656 -- 11i OE/OM change
2657 -- fnd_profile.get( 'SO_ORGANIZATION_ID', org_id );
2658 oe_profile.get( 'SO_ORGANIZATION_ID', org_id );
2659
2660 IF PG_DEBUG in ('Y', 'C') THEN
2661 arp_standard.debug( 'org_id = '|| to_char(org_id));
2662 END IF;
2663
2664 stats.round := 0;
2665 stats.receivables := 0;
2666 stats.plugs := 0;
2667 stats.tax := 0;
2668 stats.freight := 0;
2669 stats.durations := 0;
2670
2671 /*-----------------------------------------------------------------------+
2672 | Validate each of the extended who columns |
2673 +-----------------------------------------------------------------------*/
2674
2675 IF arp_standard.profile.request_id IS NULL
2676 THEN
2677 IF PG_DEBUG in ('Y', 'C') THEN
2678 arp_standard.debug('create_distributions(): NULL Request_id');
2679 END IF;
2680 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER',
2681 'OBJECT', 'AUTO_RULE.CREATE_DISTRIBUTIONS',
2682 'PARAMETER', 'ARP_STANDARD.PROFILE.REQUEST_ID' );
2683 END IF;
2684
2685 IF arp_standard.profile.program_id IS NULL
2686 THEN
2687 IF PG_DEBUG in ('Y', 'C') THEN
2688 arp_standard.debug('create_distributions(): NULL Program_id');
2689 END IF;
2690 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER',
2691 'OBJECT', 'AUTO_RULE.CREATE_DISTRIBUTIONS',
2692 'PARAMETER', 'ARP_STANDARD.PROFILE.PROGRAM_ID' );
2693 END IF;
2694
2695 IF arp_standard.profile.user_id IS NULL
2696 THEN
2697 IF PG_DEBUG in ('Y', 'C') THEN
2698 arp_standard.debug('create_distributions(): NULL User_id');
2699 END IF;
2700 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER',
2701 'OBJECT', 'AUTO_RULE.CREATE_DISTRIBUTIONS',
2702 'PARAMETER', 'ARP_STANDARD.PROFILE.USER_ID' );
2703 END IF;
2704
2705 IF arp_standard.application_id IS NULL
2706 THEN
2707 IF PG_DEBUG in ('Y', 'C') THEN
2708 arp_standard.debug('create_distributions(): NULL Program_app_id');
2709 END IF;
2710 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER',
2711 'OBJECT', 'AUTO_RULE.CREATE_DISTRIBUTIONS',
2712 'PARAMETER', 'ARP_STANDARD.APPLICATION_ID' );
2713 END IF;
2714
2715 IF arp_standard.profile.last_update_login IS NULL
2716 THEN
2717 IF PG_DEBUG in ('Y', 'C') THEN
2718 arp_standard.debug('create_distributions(): NULL Last_update_login');
2719 END IF;
2720 arp_standard.fnd_message( 'AR_PP_NULL_PARAMETER',
2721 'OBJECT', 'AUTO_RULE.CREATE_DISTRIBUTIONS',
2722 'PARAMETER', 'ARP_STANDARD.PROFILE.LAST_UPDATE_LOGIN' );
2723 END IF;
2724
2725 IF PG_DEBUG in ('Y', 'C') THEN
2726 arp_standard.debug('create_distributions(): Passed validation');
2730 | Get the period_set_name and currency information |
2727 END IF;
2728
2729 /*---------------------------------------------------+
2731 +--------------------------------------------------*/
2732
2733 SELECT
2734 period_set_name,
2735 precision,
2736 minimum_accountable_unit
2737 INTO
2738 period_set_name,
2739 base_precision,
2740 base_min_acc_unit
2741 FROM
2742 fnd_currencies fc,
2743 gl_sets_of_books gsb,
2744 ar_system_parameters asp
2745 WHERE
2746 gsb.set_of_books_id = asp.set_of_books_id
2747 AND fc.currency_code = gsb.currency_code;
2748
2749 IF PG_DEBUG in ('Y', 'C') THEN
2750 arp_standard.debug(
2751 'create_distributions(): '||
2752 ' Period Set: '|| period_set_name ||
2753 ' Base Precision: '|| base_precision ||
2754 ' Base Minimum Accountable Unit: ' ||
2755 NVL(base_min_acc_unit, 0));
2756 END IF;
2757
2758 /* Populate the GL periods PL/SQL table. */
2759 populate_glp_table(arp_standard.sysparm.set_of_books_id,
2760 arp_standard.application_id);
2761
2762 /* DEBUG INFO. */
2763 /* FOR i IN 1..rows LOOP
2764
2765 IF PG_DEBUG in ('Y', 'C') THEN
2766 arp_standard.debug(
2767 'index = '||i||', '||
2768 TO_CHAR(gl_start_t(i), 'DD-MON-RRRR')||', '||
2769 TO_CHAR(gl_end_t(i), 'DD-MON-RRRR')||', '||
2770 gl_status_t(i)||', '||
2771 TO_CHAR(gl_bump_t(i), 'DD-MON-RRRR') );
2772 END IF;
2773
2774 END LOOP;
2775 */
2776
2777 /* Bug 2122202/2133254 - open appropriate cursor using
2778 different sql depending on
2779 whether p_trx_id is null or not. */
2780
2781 IF (p_trx_id IS NOT NULL) THEN
2782 /* This is the modified logic using a sub-query - bug 2122202 */
2783 IF PG_DEBUG in ('Y', 'C') THEN
2784 arp_standard.debug('p_trx_id is NOT null, using subquery');
2785 END IF;
2786
2787 OPEN c_trx;
2788 ELSE
2789 /* This is almost exactly like original select */
2790 IF PG_DEBUG in ('Y', 'C') THEN
2791 arp_standard.debug('p_trx_id is null, using joined tables');
2792 END IF;
2793
2794 OPEN c_trx_no_id;
2795 END IF;
2796
2797 LOOP /* Loop through all transactions returned by cusror trx_id */
2798
2799 <<next_trx>>
2800
2801 /* Bug 2133254 - Fetch from correct cursor */
2802 IF (p_trx_id IS NOT NULL) THEN
2803 FETCH c_trx INTO trx_id, trx_num;
2804 EXIT WHEN c_trx%NOTFOUND;
2805 ELSE
2806 FETCH c_trx_no_id INTO trx_id, trx_num;
2807 EXIT WHEN c_trx_no_id%NOTFOUND;
2808 END IF;
2809
2810 IF PG_DEBUG in ('Y', 'C') THEN
2811 arp_standard.debug(
2812 'Creating distributions for trx_number = '|| trx_num);
2813 END IF;
2814
2815 /*------------------------------------------------------------------+
2816 | Generate new account distributions, building plug, tax, freight |
2817 | and receivable accounts as well as standard distributions |
2818 +------------------------------------------------------------------*/
2819
2820 trx_dist_created := 0;
2821
2822 SAVEPOINT AR_AUTORULE_1;
2823
2824 stats.assignments := create_assignments(
2825 trx_id,
2826 period_set_name,
2827 base_precision,
2828 base_min_acc_unit );
2829
2830 IF (stats.assignments = -2)
2831 THEN
2832
2833 glp_index_start := 1; /* reset glp table index, start */
2834 glp_index_end := g_rows; /* reset glp table index, end */
2835
2836 GOTO next_trx;
2837
2838 END IF;
2839
2840 /*
2841 If create_assignments() fails, rollback and exit function.
2842 */
2843
2844 IF (stats.assignments = -1)
2845 THEN
2846 ROLLBACK TO SAVEPOINT AR_AUTORULE_1;
2847 /* Bug 2399504 - added handler for p_continue_on_error */
2848 IF (p_continue_on_error IS NULL) THEN
2849 RETURN( -1 );
2850 ELSE
2851 return_warning := TRUE;
2852 GOTO next_trx;
2853 END IF;
2854 END IF;
2855
2856 trx_dist_created := trx_dist_created + stats.assignments;
2857
2858 stats.receivables := create_other_receivable(
2859 trx_id,
2860 base_precision,
2861 base_min_acc_unit );
2862
2863 /*
2864 If create_other_receivable() fails, rollback and exit function.
2865 */
2866
2867 IF (stats.receivables = -1)
2868 THEN
2869 ROLLBACK TO SAVEPOINT AR_AUTORULE_1;
2870 /* Bug 2399504 - added handler for p_continue_on_error */
2871 IF (p_continue_on_error IS NULL) THEN
2872 RETURN( -1 );
2873 ELSE
2874 return_warning := TRUE;
2878
2875 GOTO next_trx;
2876 END IF;
2877 END IF;
2879 trx_dist_created := trx_dist_created + stats.receivables;
2880
2881 stats.round := create_round(
2882 trx_id,
2883 base_precision,
2884 base_min_acc_unit );
2885
2886 /*
2887 If create_round() fails, rollback and exit function.
2888 */
2889
2890 IF (stats.round = -1)
2891 THEN
2892 ROLLBACK TO SAVEPOINT AR_AUTORULE_1;
2893 IF (p_continue_on_error IS NULL) THEN
2894 RETURN( -1 );
2895 ELSE
2896 return_warning := TRUE;
2897 GOTO next_trx;
2898 END IF;
2899 END IF;
2900
2901 trx_dist_created := trx_dist_created + stats.round;
2902
2903 stats.plugs := create_other_plug(
2904 trx_id,
2905 base_precision,
2906 base_min_acc_unit );
2907 /* Bug 5450534 FP of 5260489 check for status to make a
2908 recursive call to create other plug*/
2909 /* 6782405 - Revised this logic (internal to create_other_plug)
2910 so that it only returns -99 if set_rec_offset_flag was
2911 able to do anything. Otherwise it returns rows as-is and
2912 continues or fails accordingly */
2913 IF stats.plugs = -99
2914 THEN
2915 stats.plugs := create_other_plug(
2916 trx_id,
2917 base_precision,
2918 base_min_acc_unit );
2919 /* This would only be -99 the second time if the second call
2920 to set_rec_offset_flag updated more rows (should really never happen) */
2921 IF stats.plugs = -99
2922 THEN
2923 stats.plugs := -1;
2924 END IF;
2925 END IF;
2926
2927 /*
2928 If create_other_plug() fails, rollback and exit function.
2929 */
2930
2931 IF (stats.plugs = -1)
2932 THEN
2933 ROLLBACK TO SAVEPOINT AR_AUTORULE_1;
2934 /* Bug 2399504 - added handler for p_continue_on_error */
2935 IF (p_continue_on_error IS NULL) THEN
2936 RETURN( -1 );
2937 ELSE
2938 return_warning := TRUE;
2939 GOTO next_trx;
2940 END IF;
2941 END IF;
2942
2943 trx_dist_created := trx_dist_created + stats.plugs;
2944
2945 stats.tax := create_other_tax(
2946 trx_id,
2947 base_precision,
2948 base_min_acc_unit );
2949
2950 /*
2951 If create_other_tax() fails, rollback and exit function.
2952 */
2953
2954 IF (stats.tax = -1)
2955 THEN
2956 ROLLBACK TO SAVEPOINT AR_AUTORULE_1;
2957 /* Bug 2399504 - added handler for p_continue_on_error */
2958 IF (p_continue_on_error IS NULL) THEN
2959 RETURN( -1 );
2960 ELSE
2961 return_warning := TRUE;
2962 GOTO next_trx;
2963 END IF;
2964 END IF;
2965
2966 trx_dist_created := trx_dist_created + stats.tax;
2967
2968 stats.freight := create_other_freight(
2969 trx_id,
2970 base_precision,
2971 base_min_acc_unit );
2972
2973 /*
2974 If create_other_freight() fails, rollback and exit function.
2975 */
2976
2977 IF (stats.freight = -1)
2978 THEN
2979 ROLLBACK TO SAVEPOINT AR_AUTORULE_1;
2980 /* Bug 2399504 - added handler for p_continue_on_error */
2981 IF (p_continue_on_error IS NULL) THEN
2982 RETURN( -1 );
2983 ELSE
2984 return_warning := TRUE;
2985 GOTO next_trx;
2986 END IF;
2987 END IF;
2988
2989 trx_dist_created := trx_dist_created + stats.freight;
2990
2991 /* Update durations processed and correct rounding errors ONLY if any
2992 distributions are created for this transaction in this run. */
2993
2994 IF ( trx_dist_created > 0 )
2995 THEN
2996
2997 IF PG_DEBUG in ('Y', 'C') THEN
2998 arp_standard.debug( 'trx_id = '|| trx_id ||
2999 ', distributions created = '|| trx_dist_created);
3000 END IF;
3001
3002 stats.durations := update_durations(trx_id);
3003
3004 /*
3005 If update_durations() fails, rollback and exit function.
3006 */
3007
3008 IF (stats.durations = -1)
3009 THEN
3010 ROLLBACK TO SAVEPOINT AR_AUTORULE_1;
3011 /* Bug 2399504 - added handler for p_continue_on_error */
3012 IF (p_continue_on_error IS NULL) THEN
3013 RETURN( -1 );
3014 ELSE
3015 return_warning := TRUE;
3016 GOTO next_trx;
3017 END IF;
3018 END IF;
3022 THEN
3019 /* Bug 2150541 - allow bypass of rounding
3020 for diagnostic purposes */
3021 IF (p_suppress_round IS NULL)
3023
3024 /* Bug 2497841 - Allow bypass of set_rec_offset_flag
3025 routine inside rounding logic */
3026 IF (arp_rounding.correct_dist_rounding_errors(
3027 NULL,
3028 trx_id,
3029 NULL,
3030 num_round_err_corr,
3031 error_message,
3032 NULL,
3033 NULL,
3034 'ALL',
3035 'Y',
3036 p_debug,
3037 arp_global.sysparam.trx_header_level_rounding,
3038 'N',
3039 'N') = 0 )
3040 THEN
3041
3042 ROLLBACK TO SAVEPOINT AR_AUTORULE_1;
3043
3044 IF PG_DEBUG in ('Y', 'C') THEN
3045 arp_standard.debug('create_distributions(): '|| error_message);
3046 END IF;
3047
3048 /* Bug 2399504 - added handler for p_continue_on_error */
3049 IF (p_continue_on_error IS NULL) THEN
3050 /* This FND_MESSAGE call halts execution of program */
3051 arp_standard.fnd_message( 'GENERIC_MESSAGE',
3052 'GENERIC_TEXT', error_message);
3053 RETURN( -1 );
3054 ELSE
3055 return_warning := TRUE;
3056 GOTO next_trx;
3057 END IF;
3058
3059 ELSE
3060
3061 IF PG_DEBUG in ('Y', 'C') THEN
3062 arp_standard.debug(
3063 'Rounding errors corrected for: '||
3064 num_round_err_corr || ' rows.');
3065 END IF;
3066
3067 END IF; /* rounding */
3068
3069 ELSE
3070 IF PG_DEBUG in ('Y', 'C') THEN
3071 arp_standard.debug( 'ROUNDING SUPPRESSED!');
3072 END IF;
3073 END IF;
3074
3075 /* All the distributions are created, rounding done, so we can
3076 safely commit here, if, Rev Rec is run in COMMIT mode. */
3077
3078 l_xla_ev_rec.xla_from_doc_id := trx_id;
3079 l_xla_ev_rec.xla_to_doc_id := trx_id;
3080 l_xla_ev_rec.xla_doc_table := 'CT';
3081 l_xla_ev_rec.xla_mode := 'O';
3082 l_xla_ev_rec.xla_call := 'B';
3083 l_xla_ev_rec.xla_fetch_size := 999;
3084 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
3085
3086 IF (p_commit = 'Y')
3087 THEN
3088 COMMIT WORK;
3089 END IF;
3090
3091 ELSE
3092
3093 IF PG_DEBUG in ('Y', 'C') THEN
3094 arp_standard.debug( 'trx_id = '|| trx_id ||
3095 ', distributions created = '|| trx_dist_created);
3096 END IF;
3097
3098 END IF; /* if (trx_dist_created > 0) */
3099
3100 sum_dist_created := sum_dist_created + nvl(trx_dist_created,0);
3101
3102 glp_index_start := 1; /* reset glp table index, start */
3103 glp_index_end := g_rows; /* reset glp table index, end */
3104
3105
3106 END LOOP; /* Cursor trx_id loop */
3107
3108 IF PG_DEBUG in ('Y', 'C') THEN
3109 arp_standard.debug( 'create_distributions(): ' ||
3110 'Total number of create_distributions created = '||
3111 TO_CHAR(NVL(sum_dist_created, 0)));
3112 END IF;
3113
3114 /* Bug 2118867 - close those cursors */
3115 IF (p_trx_id IS NOT NULL) THEN
3116 CLOSE c_trx;
3117 ELSE
3118 CLOSE c_trx_no_id;
3119 END IF;
3120
3121 /* Bug 2649674 - Return WARNING if something went wrong for at
3122 least one transaction that was processed by this (single thread)
3123 run. */
3124 IF (return_warning AND p_trx_id IS NULL)
3125 THEN
3126 arp_standard.debug('Attempting to set WARNING return status');
3127 error_message := FND_MESSAGE.GET_STRING('AR','ARBARL_WARN_BAD_TRX');
3128
3129 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', error_message) = FALSE)
3130 THEN
3131 arp_standard.debug('Unable to set WARNING return status');
3132 END IF;
3133 END IF;
3134
3135 IF PG_DEBUG in ('Y', 'C') THEN
3136 arp_standard.debug( 'arp_auto_rule.create_distributions()- ' ||
3137 TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
3138 END IF;
3139
3140 RETURN( NVL(sum_dist_created, 0) );
3141
3142 END create_distributions;
3143
3144 BEGIN
3145 /* Bug 2560048: check if revenue management is installed */
3146 IF ar_revenue_management_pvt.revenue_management_enabled
3147 THEN
3148 g_rev_mgt_installed := 'Y';
3149 ELSE
3150 g_rev_mgt_installed := 'N';
3151 END IF;
3152
3153 /* 5598773 - get profile for USE_INV_ACCT */
3154 fnd_profile.get('AR_USE_INV_ACCT_FOR_CM_FLAG',
3155 g_use_inv_acctg );
3156 IF g_use_inv_acctg IS NULL
3157 THEN
3158 g_use_inv_acctg := 'N';
3159 END IF;
3160
3161 END ARP_AUTO_RULE;