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