[Home] [Help]
9: iTRUE CONSTANT NUMBER := 1;
10: iFALSE CONSTANT NUMBER := 0;
11: cr CONSTANT char(1) := NULL;
12:
13: trx_for_rof ra_cust_trx_line_gl_dist_all.customer_trx_id%type;
14: /*Added for Bugs 2480898, 2493896, 2497841 */
15: rqid_for_rof ra_cust_trx_line_gl_dist_all.request_id%type;
16:
17: /* 7039838 - determines if call is from an autoinvoice inspired
11: cr CONSTANT char(1) := NULL;
12:
13: trx_for_rof ra_cust_trx_line_gl_dist_all.customer_trx_id%type;
14: /*Added for Bugs 2480898, 2493896, 2497841 */
15: rqid_for_rof ra_cust_trx_line_gl_dist_all.request_id%type;
16:
17: /* 7039838 - determines if call is from an autoinvoice inspired
18: session */
19: g_autoinv BOOLEAN;
18: session */
19: g_autoinv BOOLEAN;
20: g_autoinv_request_id NUMBER;
21:
22: TYPE l_line_id_type IS TABLE OF ra_cust_trx_line_gl_dist_all.customer_trx_line_id%type
23: INDEX BY BINARY_INTEGER;
24: TYPE l_amount_type IS TABLE OF ra_cust_trx_line_gl_dist_all.amount%type
25: INDEX BY BINARY_INTEGER;
26: TYPE l_percent_type IS TABLE OF ra_cust_trx_line_gl_dist_all.percent%type
20: g_autoinv_request_id NUMBER;
21:
22: TYPE l_line_id_type IS TABLE OF ra_cust_trx_line_gl_dist_all.customer_trx_line_id%type
23: INDEX BY BINARY_INTEGER;
24: TYPE l_amount_type IS TABLE OF ra_cust_trx_line_gl_dist_all.amount%type
25: INDEX BY BINARY_INTEGER;
26: TYPE l_percent_type IS TABLE OF ra_cust_trx_line_gl_dist_all.percent%type
27: INDEX BY BINARY_INTEGER;
28: TYPE l_acct_class IS TABLE OF ra_cust_trx_line_gl_dist_all.account_class%type
22: TYPE l_line_id_type IS TABLE OF ra_cust_trx_line_gl_dist_all.customer_trx_line_id%type
23: INDEX BY BINARY_INTEGER;
24: TYPE l_amount_type IS TABLE OF ra_cust_trx_line_gl_dist_all.amount%type
25: INDEX BY BINARY_INTEGER;
26: TYPE l_percent_type IS TABLE OF ra_cust_trx_line_gl_dist_all.percent%type
27: INDEX BY BINARY_INTEGER;
28: TYPE l_acct_class IS TABLE OF ra_cust_trx_line_gl_dist_all.account_class%type
29: INDEX BY BINARY_INTEGER;
30: TYPE l_rec_offset IS TABLE OF ra_cust_trx_line_gl_dist_all.rec_offset_flag%type
24: TYPE l_amount_type IS TABLE OF ra_cust_trx_line_gl_dist_all.amount%type
25: INDEX BY BINARY_INTEGER;
26: TYPE l_percent_type IS TABLE OF ra_cust_trx_line_gl_dist_all.percent%type
27: INDEX BY BINARY_INTEGER;
28: TYPE l_acct_class IS TABLE OF ra_cust_trx_line_gl_dist_all.account_class%type
29: INDEX BY BINARY_INTEGER;
30: TYPE l_rec_offset IS TABLE OF ra_cust_trx_line_gl_dist_all.rec_offset_flag%type
31: INDEX BY BINARY_INTEGER;
32: TYPE l_date_type IS TABLE OF ra_cust_trx_line_gl_dist_all.gl_date%type
26: TYPE l_percent_type IS TABLE OF ra_cust_trx_line_gl_dist_all.percent%type
27: INDEX BY BINARY_INTEGER;
28: TYPE l_acct_class IS TABLE OF ra_cust_trx_line_gl_dist_all.account_class%type
29: INDEX BY BINARY_INTEGER;
30: TYPE l_rec_offset IS TABLE OF ra_cust_trx_line_gl_dist_all.rec_offset_flag%type
31: INDEX BY BINARY_INTEGER;
32: TYPE l_date_type IS TABLE OF ra_cust_trx_line_gl_dist_all.gl_date%type
33: INDEX BY BINARY_INTEGER;
34:
28: TYPE l_acct_class IS TABLE OF ra_cust_trx_line_gl_dist_all.account_class%type
29: INDEX BY BINARY_INTEGER;
30: TYPE l_rec_offset IS TABLE OF ra_cust_trx_line_gl_dist_all.rec_offset_flag%type
31: INDEX BY BINARY_INTEGER;
32: TYPE l_date_type IS TABLE OF ra_cust_trx_line_gl_dist_all.gl_date%type
33: INDEX BY BINARY_INTEGER;
34:
35: -- Private cursor
36:
49: | PROCEDURE |
50: | set_rec_offset_flag |
51: | |
52: | DESCRIPTION |
53: | Sets the rec_offset_flag in ra_cust_trx_line_gl_dist for REC offsetting|
54: | UNEARN/UNBILL rows if the flag has not been set already. Procedure
55: | has two parameters. If called with customer_trx_id, it sets the flags
56: | for that transaction. If called by request_id, it sets the flags
57: | for invoices targeted by CM transactions in that request_id group.
100: CURSOR inv_needing_rof(pp_request_id NUMBER) IS
101: SELECT DISTINCT inv_trx.customer_trx_id
102: FROM RA_CUSTOMER_TRX cm_trx,
103: RA_CUSTOMER_TRX inv_trx,
104: RA_CUST_TRX_LINE_GL_DIST inv_rec
105: WHERE cm_trx.request_id = pp_request_id
106: AND cm_trx.previous_customer_trx_id = inv_trx.customer_trx_id
107: AND inv_trx.invoicing_rule_id IS NOT NULL
108: AND inv_trx.customer_trx_id = inv_rec.customer_trx_id
111: AND inv_rec.latest_rec_flag = 'Y'
112: AND NOT EXISTS
113: (SELECT /*+ NO_UNNEST NO_PUSH_SUBQ */
114: 'rof already set'
115: FROM ra_cust_trx_line_gl_dist g2
116: WHERE g2.customer_trx_id = inv_trx.customer_trx_id
117: AND g2.account_set_flag = 'N'
118: AND g2.account_class in ('UNEARN','UNBILL')
119: AND g2.rec_offset_flag = 'Y');
149: AND tl.accounting_rule_id IS NOT NULL
150: AND NOT EXISTS
151: (SELECT /*+ NO_UNNEST NO_PUSH_SUBQ */
152: 'rof already set'
153: FROM ra_cust_trx_line_gl_dist g2
154: WHERE g2.customer_trx_id = tl.customer_trx_id
155: AND g2.account_set_flag = 'N'
156: AND g2.account_class in ('UNEARN','UNBILL')
157: AND g2.rec_offset_flag = 'Y');
172: FT tuning effort */
173: IF g_autoinv
174: THEN
175:
176: UPDATE RA_CUST_TRX_LINE_GL_DIST
177: SET rec_offset_flag = 'Y'
178: WHERE cust_trx_line_gl_dist_id in
179: (SELECT /*+ PUSH_SUBQ UNNEST
180: index(tl RA_CUSTOMER_TRX_LINES_N4) */
178: WHERE cust_trx_line_gl_dist_id in
179: (SELECT /*+ PUSH_SUBQ UNNEST
180: index(tl RA_CUSTOMER_TRX_LINES_N4) */
181: g.cust_trx_line_gl_dist_id
182: FROM ra_cust_trx_line_gl_dist g,
183: ra_customer_trx_lines tl,
184: ra_cust_trx_line_gl_dist grec
185: WHERE tl.customer_trx_id = p_customer_trx_id
186: AND tl.request_id = g_autoinv_request_id
180: index(tl RA_CUSTOMER_TRX_LINES_N4) */
181: g.cust_trx_line_gl_dist_id
182: FROM ra_cust_trx_line_gl_dist g,
183: ra_customer_trx_lines tl,
184: ra_cust_trx_line_gl_dist grec
185: WHERE tl.customer_trx_id = p_customer_trx_id
186: AND tl.request_id = g_autoinv_request_id
187: AND tl.accounting_rule_id is not null
188: AND tl.customer_trx_line_id = g.customer_trx_line_id
199: AND g.rec_offset_flag is null);
200:
201: ELSE
202:
203: UPDATE RA_CUST_TRX_LINE_GL_DIST
204: SET rec_offset_flag = 'Y'
205: WHERE cust_trx_line_gl_dist_id in
206: (SELECT /*+ PUSH_SUBQ UNNEST */
207: g.cust_trx_line_gl_dist_id
204: SET rec_offset_flag = 'Y'
205: WHERE cust_trx_line_gl_dist_id in
206: (SELECT /*+ PUSH_SUBQ UNNEST */
207: g.cust_trx_line_gl_dist_id
208: FROM ra_cust_trx_line_gl_dist g,
209: ra_customer_trx_lines tl,
210: ra_cust_trx_line_gl_dist grec
211: WHERE tl.customer_trx_id = p_customer_trx_id
212: AND tl.accounting_rule_id is not null
206: (SELECT /*+ PUSH_SUBQ UNNEST */
207: g.cust_trx_line_gl_dist_id
208: FROM ra_cust_trx_line_gl_dist g,
209: ra_customer_trx_lines tl,
210: ra_cust_trx_line_gl_dist grec
211: WHERE tl.customer_trx_id = p_customer_trx_id
212: AND tl.accounting_rule_id is not null
213: AND tl.customer_trx_line_id = g.customer_trx_line_id
214: AND tl.line_type = 'LINE'
265:
266: IF l_no_rof > 0
267: THEN
268: FORALL i IN t_trx_id.FIRST .. t_trx_id.LAST
269: UPDATE RA_CUST_TRX_LINE_GL_DIST G
270: SET rec_offset_flag = 'Y'
271: WHERE G.cust_trx_line_gl_dist_id in
272: (SELECT /*+ PUSH_SUBQ ORDERED UNNEST */
273: inv_g.cust_trx_line_gl_dist_id
271: WHERE G.cust_trx_line_gl_dist_id in
272: (SELECT /*+ PUSH_SUBQ ORDERED UNNEST */
273: inv_g.cust_trx_line_gl_dist_id
274: FROM ra_customer_trx_lines inv_l,
275: ra_cust_trx_line_gl_dist inv_g,
276: ra_cust_trx_line_gl_dist inv_grec
277: WHERE inv_l.customer_trx_id = t_trx_id(i)
278: AND inv_l.accounting_rule_id is not null
279: AND inv_l.customer_trx_line_id =
272: (SELECT /*+ PUSH_SUBQ ORDERED UNNEST */
273: inv_g.cust_trx_line_gl_dist_id
274: FROM ra_customer_trx_lines inv_l,
275: ra_cust_trx_line_gl_dist inv_g,
276: ra_cust_trx_line_gl_dist inv_grec
277: WHERE inv_l.customer_trx_id = t_trx_id(i)
278: AND inv_l.accounting_rule_id is not null
279: AND inv_l.customer_trx_line_id =
280: inv_g.customer_trx_line_id
375: CURSOR true_rows_by_date(p_trx_id NUMBER) IS
376: select g.customer_trx_line_id, g.gl_date,
377: sum(g.amount), sum(g.acctd_amount), sum(g.percent),
378: nvl(revenue_adjustment_id, -99) revenue_adjustment_id
379: from ra_cust_trx_line_gl_dist g,
380: ra_customer_trx h,
381: ra_customer_trx prev_h
382: where h.customer_trx_id = p_trx_id
383: and h.previous_customer_trx_id = prev_h.customer_trx_id (+)
393: CURSOR true_rows_by_date_gt IS
394: select g.customer_trx_line_id, g.gl_date,
395: sum(g.amount), sum(g.acctd_amount), sum(g.percent),
396: nvl(g.revenue_adjustment_id, -99) revenue_adjustment_id
397: from ra_cust_trx_line_gl_dist g,
398: ar_line_rev_adj_gt gt,
399: ra_customer_trx h,
400: ra_customer_trx prev_h
401: where h.customer_trx_id = g.customer_trx_id
461:
462: IF (l_rows_needing_truing > 0) THEN
463:
464: FORALL i IN t_true_line_id.FIRST .. t_true_line_id.LAST
465: UPDATE ra_cust_trx_line_gl_dist g
466: SET amount = amount - t_true_amount(i),
467: percent = percent - t_true_percent(i),
468: acctd_amount = acctd_amount - t_true_acctd(i),
469: last_updated_by = arp_global.last_updated_by,
471: WHERE cust_trx_line_gl_dist_id in (
472: /* SELECT GL_DIST_ID FOR EACH DATE THAT
473: REQUIRES TRUING */
474: select MAX(g.cust_trx_line_gl_dist_id)
475: from ra_cust_trx_line_gl_dist g,
476: ra_customer_trx_lines tl
477: where g.customer_trx_line_id = t_true_line_id(i)
478: and g.gl_date = t_true_gl_date(i)
479: and g.customer_trx_line_id = tl.customer_trx_line_id
560: +===========================================================================*/
561: PROCEDURE correct_suspense(p_customer_trx_id IN
562: ra_customer_trx.customer_trx_id%type) IS
563:
564: l_acctd_correction ra_cust_trx_line_gl_dist.acctd_amount%type;
565: l_rows NUMBER;
566: BEGIN
567: arp_util.debug('arp_rounding.correct_suspense()+');
568:
573: get_dist_round_acctd_amount(p_customer_trx_id);
574:
575: IF (l_acctd_correction <> 0)
576: THEN
577: UPDATE RA_CUST_TRX_LINE_GL_DIST
578: SET acctd_amount = acctd_amount + l_acctd_correction
579: WHERE cust_trx_line_gl_dist_id in
580: (SELECT MAX(g.cust_trx_line_gl_dist_id)
581: FROM ra_cust_trx_line_gl_dist g
577: UPDATE RA_CUST_TRX_LINE_GL_DIST
578: SET acctd_amount = acctd_amount + l_acctd_correction
579: WHERE cust_trx_line_gl_dist_id in
580: (SELECT MAX(g.cust_trx_line_gl_dist_id)
581: FROM ra_cust_trx_line_gl_dist g
582: WHERE g.account_class = 'SUSPENSE'
583: AND g.account_set_flag = 'N'
584: AND g.customer_trx_id = p_customer_trx_id
585: AND g.posting_control_id = -3
584: AND g.customer_trx_id = p_customer_trx_id
585: AND g.posting_control_id = -3
586: AND g.acctd_amount = (
587: SELECT MAX(g2.acctd_amount)
588: FROM ra_cust_trx_line_gl_dist g2
589: WHERE g2.customer_trx_id = p_customer_trx_id
590: AND g2.account_class = 'SUSPENSE'
591: AND g2.account_set_flag = 'N'
592: AND g2.posting_control_id = -3));
923: | insert_round_records() |
924: | |
925: | DESCRIPTION |
926: | This function inserts one record of account_class ROUND into the |
927: | ra_cust_trx_line_gl_dist table. |
928: | |
929: | If the ROUND record already exist for a transaction then it is not |
930: | inserted again. Like the REC record there will be only 1 (2 in case of|
931: | transaction with rule) ROUND record for each transaction. |
951: | |
952: | MODIFICATION HISTORY |
953: | 13-Aug-2002 Debbie Jancis Modified for mrc trigger replacement |
954: | added calls for insert into |
955: | ra_cust_trx_line_gl_dist |
956: | 24-SEP-2002 M.Ryzhikova Modified for mrc trigger replacement. |
957: | 01-OCT-2003 M Raymond Bug 3067588 - made this function public
958: +-------------------------------------------------------------------------*/
959:
986: IF PG_DEBUG in ('Y', 'C') THEN
987: arp_standard.debug('p_request_id is not null ....' || to_char(p_request_id));
988: END IF;
989:
990: insert into ra_cust_trx_line_gl_dist
991: (POST_REQUEST_ID ,
992: POSTING_CONTROL_ID ,
993: ACCOUNT_CLASS ,
994: RA_POST_LOOP_NUMBER ,
1052: ATTRIBUTE14,
1053: ATTRIBUTE15,
1054: NULL, /* LATEST_REC_FLAG */
1055: ORG_ID,
1056: RA_CUST_TRX_LINE_GL_DIST_s.nextval,
1057: CUSTOMER_TRX_LINE_ID,
1058: P_TRX_HEADER_ROUND_CCID, /* CODE_COMBINATION_ID */
1059: SET_OF_BOOKS_ID,
1060: SYSDATE,
1084: arp_global.program_id,
1085: arp_global.program_update_date,
1086: CONCATENATED_SEGMENTS,
1087: ORIGINAL_GL_DATE
1088: from ra_cust_trx_line_gl_dist rec
1089: where account_class = 'REC'
1090: and latest_rec_flag = 'Y'
1091: and gl_posted_date is null
1092: and rec.request_id = p_request_id
1091: and gl_posted_date is null
1092: and rec.request_id = p_request_id
1093: /* bug3311759 : Removed
1094: and not exists ( select 1
1095: from ra_cust_trx_line_gl_dist dist2
1096: where dist2.customer_trx_id = rec.customer_trx_id
1097: and dist2.account_class in ('UNEARN','UNBILL')
1098: and dist2.account_set_flag = 'N')
1099: */
1097: and dist2.account_class in ('UNEARN','UNBILL')
1098: and dist2.account_set_flag = 'N')
1099: */
1100: and not exists ( select 1
1101: from ra_cust_trx_line_gl_dist dist2
1102: where dist2.customer_trx_id = rec.customer_trx_id
1103: and dist2.account_class = 'ROUND'
1104: and dist2.account_set_flag = rec.account_set_flag);
1105:
1120: IF PG_DEBUG in ('Y', 'C') THEN
1121: arp_standard.debug('customer trx id is not null.... === ' || to_char(p_customer_trx_id));
1122: END IF;
1123:
1124: insert into ra_cust_trx_line_gl_dist
1125: (POST_REQUEST_ID ,
1126: POSTING_CONTROL_ID ,
1127: ACCOUNT_CLASS ,
1128: RA_POST_LOOP_NUMBER ,
1186: ATTRIBUTE14,
1187: ATTRIBUTE15,
1188: NULL, /* LATEST_REC_FLAG */
1189: ORG_ID,
1190: RA_CUST_TRX_LINE_GL_DIST_s.nextval,
1191: CUSTOMER_TRX_LINE_ID,
1192: P_TRX_HEADER_ROUND_CCID, /* CODE_COMBINATION_ID */
1193: SET_OF_BOOKS_ID,
1194: SYSDATE,
1218: arp_global.program_id,
1219: arp_global.program_update_date,
1220: CONCATENATED_SEGMENTS,
1221: ORIGINAL_GL_DATE
1222: from ra_cust_trx_line_gl_dist rec
1223: where account_class = 'REC'
1224: and latest_rec_flag = 'Y'
1225: and gl_posted_date is null
1226: and rec.customer_trx_id = p_customer_trx_id
1225: and gl_posted_date is null
1226: and rec.customer_trx_id = p_customer_trx_id
1227: /* bug3311759 : Removed
1228: and not exists ( select 1
1229: from ra_cust_trx_line_gl_dist dist2
1230: where dist2.customer_trx_id = rec.customer_trx_id
1231: and dist2.account_class in ('UNEARN','UNBILL')
1232: and dist2.account_set_flag = 'N')
1233: */
1231: and dist2.account_class in ('UNEARN','UNBILL')
1232: and dist2.account_set_flag = 'N')
1233: */
1234: and not exists ( select 1
1235: from ra_cust_trx_line_gl_dist dist2
1236: where dist2.customer_trx_id = rec.customer_trx_id
1237: and dist2.account_class = 'ROUND'
1238: and dist2.account_set_flag = rec.account_set_flag);
1239:
1245: END IF;
1246:
1247: SELECT cust_trx_line_gl_dist_id
1248: BULK COLLECT INTO l_gl_dist_key_value_list
1249: FROM ra_cust_trx_line_gl_dist rec
1250: where rec.customer_trx_id = p_customer_trx_id
1251: and account_class = 'ROUND';
1252:
1253:
1259: END IF;
1260:
1261: ar_mrc_engine.maintain_mrc_data(
1262: p_event_mode => 'INSERT',
1263: p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',
1264: p_mode => 'BATCH',
1265: p_key_value_list => l_gl_dist_key_value_list) ;
1266:
1267: --BUG#2750340
1331: )
1332: into l_round_acctd_amount
1333: from ra_customer_trx ct,
1334: ra_customer_trx_lines l,
1335: ra_cust_trx_line_gl_dist rec,
1336: fnd_currencies fc,
1337: gl_sets_of_books gsb
1338: where ct.customer_trx_id = l.customer_trx_id
1339: and ct.customer_trx_id = rec.customer_trx_id
1366: begin
1367: select
1368: nvl(rec.acctd_amount,0) - sum(nvl(lgd.acctd_amount,0))
1369: into l_round_acctd_amount
1370: from ra_cust_trx_line_gl_dist lgd,
1371: ra_cust_trx_line_gl_dist rec
1372: where lgd.customer_trx_id = rec.customer_trx_id
1373: and rec.customer_trx_id = P_CUSTOMER_TRX_ID
1374: and rec.account_class = 'REC'
1367: select
1368: nvl(rec.acctd_amount,0) - sum(nvl(lgd.acctd_amount,0))
1369: into l_round_acctd_amount
1370: from ra_cust_trx_line_gl_dist lgd,
1371: ra_cust_trx_line_gl_dist rec
1372: where lgd.customer_trx_id = rec.customer_trx_id
1373: and rec.customer_trx_id = P_CUSTOMER_TRX_ID
1374: and rec.account_class = 'REC'
1375: and rec.latest_rec_flag = 'Y'
1451: arp_standard.debug('P_CUSTOMER_TRX_ID: ' || P_CUSTOMER_TRX_ID);
1452: END IF;
1453:
1454:
1455: update ra_cust_trx_line_gl_dist dist
1456: set (amount, acctd_amount, code_combination_id, concatenated_segments) =
1457: (select 0,
1458: l_line_round_acctd_amount,
1459: nvl(balanced_round_ccid,-1),
1558: * Bug 13434104 *
1559: * Removed the call to gl_currency_api.convert_amount *
1560: ******************************************************/
1561:
1562: update ra_cust_trx_line_gl_dist rec
1563: set (amount, acctd_amount, percent) =
1564: ( select nvl(rec.amount, 0) +
1565: (sum(l.extended_amount) - nvl(rec.amount, 0) ),
1566: nvl(rec.acctd_amount, 0) +
1592: where customer_trx_id in
1593: ( select l.customer_trx_id
1594: from ra_customer_trx_lines l,
1595: ra_customer_trx t,
1596: ra_cust_trx_line_gl_dist d
1597: where t.customer_trx_id = l.customer_trx_id
1598: and t.customer_trx_id = d.customer_trx_id
1599: and d.account_class = 'REC'
1600: and d.latest_rec_flag = 'Y'
1650: /* 7039838 - If executed from autoinv, then added several hints
1651: and additional binds for performance */
1652: IF g_autoinv
1653: THEN
1654: update ra_cust_trx_line_gl_dist rec
1655: set (amount, acctd_amount, percent) =
1656: ( select /*+ index(L RA_CUSTOMER_TRX_LINES_N4) */
1657: nvl(rec.amount, 0) +
1658: (sum(l.extended_amount) - nvl(rec.amount, 0) ),
1688: index(L RA_CUSTOMER_TRX_LINES_N4) */
1689: l.customer_trx_id
1690: from ra_customer_trx t,
1691: ra_customer_trx_lines l,
1692: ra_cust_trx_line_gl_dist d
1693: where t.customer_trx_id = l.customer_trx_id
1694: and l.customer_trx_id = d.customer_trx_id
1695: and l.request_id = g_autoinv_request_id -- 7039838
1696: and l.customer_trx_id = p_customer_trx_id -- 7039838
1736: and rec.gl_posted_date is null;
1737:
1738: ELSE
1739: /* Not autoinvoice, probably Rev Rec or forms logic */
1740: update ra_cust_trx_line_gl_dist rec
1741: set (amount, acctd_amount, percent) =
1742: ( select nvl(rec.amount, 0) +
1743: (sum(l.extended_amount) - nvl(rec.amount, 0) ),
1744: nvl(rec.acctd_amount, 0) +
1770: where customer_trx_id in
1771: ( select l.customer_trx_id
1772: from ra_customer_trx t,
1773: ra_customer_trx_lines l,
1774: ra_cust_trx_line_gl_dist d
1775: where t.customer_trx_id = l.customer_trx_id
1776: and l.customer_trx_id = d.customer_trx_id
1777: and d.account_class = 'REC'
1778: and d.latest_rec_flag = 'Y'
1903: * Bug 13434104 *
1904: * Removed the call to gl_currency_api.convert_amount *
1905: ******************************************************/
1906:
1907: UPDATE ra_cust_trx_line_gl_dist rec
1908: SET (amount, acctd_amount, percent) =
1909: ( SELECT
1910: NVL(rec.amount, 0) +
1911: (SUM(l.extended_amount) - NVL(rec.amount, 0) ),
1941: l.customer_trx_id
1942: FROM
1943: ra_customer_trx_lines l,
1944: ra_customer_trx t,
1945: ra_cust_trx_line_gl_dist d
1946: WHERE
1947: t.customer_trx_id = d.customer_trx_id
1948: AND l.customer_trx_id = t.customer_trx_id
1949: AND d.account_class = 'REC'
1990:
1991: IF (p_customer_trx_id IS NOT NULL)
1992: THEN
1993:
1994: UPDATE ra_cust_trx_line_gl_dist rec
1995: SET (amount, acctd_amount, percent) =
1996: ( SELECT
1997: NVL(rec.amount, 0) +
1998: (SUM(l.extended_amount) - NVL(rec.amount, 0) ),
2029: l.customer_trx_id
2030: FROM
2031: ra_customer_trx t,
2032: ra_customer_trx_lines l,
2033: ra_cust_trx_line_gl_dist d
2034: WHERE
2035: t.customer_trx_id = d.customer_trx_id
2036: AND l.customer_trx_id = t.customer_trx_id
2037: AND d.account_class = 'REC'
2192: performance in large databases. I basically restructured the join
2193: order around ra_customer_trx instead of the gl_dist tables.
2194: */
2195:
2196: UPDATE ra_cust_trx_line_gl_dist lgd
2197: SET (amount, acctd_amount) =
2198: (SELECT NVL(lgd.amount, 0) -
2199: (
2200: SUM(lgd2.amount) -
2224: ) /* accounted amount */
2225: FROM
2226: ra_customer_trx_lines ctl,
2227: ra_customer_trx ct,
2228: ra_cust_trx_line_gl_dist lgd2,
2229: ra_cust_trx_line_gl_dist rec1
2230: WHERE
2231: ctl.customer_trx_line_id = lgd2.customer_trx_line_id
2232: AND ctl.customer_trx_id = ct.customer_trx_id
2225: FROM
2226: ra_customer_trx_lines ctl,
2227: ra_customer_trx ct,
2228: ra_cust_trx_line_gl_dist lgd2,
2229: ra_cust_trx_line_gl_dist rec1
2230: WHERE
2231: ctl.customer_trx_line_id = lgd2.customer_trx_line_id
2232: AND ctl.customer_trx_id = ct.customer_trx_id
2233: AND lgd.customer_trx_line_id = ctl.customer_trx_line_id
2260: 0)
2261: )
2262: ) /* percent */
2263: FROM
2264: ra_cust_trx_line_gl_dist lgd4,
2265: ra_cust_trx_line_gl_dist rec2
2266: WHERE
2267: lgd.customer_trx_line_id = lgd4.customer_trx_line_id
2268: AND rec2.customer_trx_id = lgd.customer_trx_id
2261: )
2262: ) /* percent */
2263: FROM
2264: ra_cust_trx_line_gl_dist lgd4,
2265: ra_cust_trx_line_gl_dist rec2
2266: WHERE
2267: lgd.customer_trx_line_id = lgd4.customer_trx_line_id
2268: AND rec2.customer_trx_id = lgd.customer_trx_id
2269: AND rec2.account_class = 'REC'
2280: ),
2281: last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
2282: last_update_date = sysdate
2283: WHERE cust_trx_line_gl_dist_id IN
2284: (SELECT /*+ INDEX (lgd3 ra_cust_trx_line_gl_dist_n7) */
2285: MIN(DECODE(lgd3.gl_posted_date,
2286: NULL, lgd3.cust_trx_line_gl_dist_id,
2287: NULL) )
2288: FROM
2287: NULL) )
2288: FROM
2289: ra_customer_trx_lines ctl,
2290: ra_customer_trx t,
2291: ra_cust_trx_line_gl_dist lgd3,
2292: ra_cust_trx_line_gl_dist rec3
2293: WHERE
2294: t.request_id = p_request_id
2295: AND T.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
2288: FROM
2289: ra_customer_trx_lines ctl,
2290: ra_customer_trx t,
2291: ra_cust_trx_line_gl_dist lgd3,
2292: ra_cust_trx_line_gl_dist rec3
2293: WHERE
2294: t.request_id = p_request_id
2295: AND T.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
2296: AND (CTL.LINE_TYPE IN ( 'TAX','FREIGHT','CHARGES','SUSPENSE' ) OR
2333: ) / p_base_mau ) * p_base_mau
2334: )
2335: )
2336: UNION
2337: SELECT /*+ INDEX (lgd5 ra_cust_trx_line_gl_dist_n7) */
2338: TO_NUMBER(
2339: MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
2340: lgd5.account_set_flag,
2341: 'REVN', lgd5.cust_trx_line_gl_dist_id,
2350: 'UNBILLY', lgd5.cust_trx_line_gl_dist_id,
2351: NULL ) )
2352: )
2353: FROM
2354: ra_cust_trx_line_gl_dist lgd5,
2355: ra_cust_trx_line_gl_dist rec5,
2356: ra_customer_trx_lines ctl2,
2357: ra_customer_trx t
2358: WHERE
2351: NULL ) )
2352: )
2353: FROM
2354: ra_cust_trx_line_gl_dist lgd5,
2355: ra_cust_trx_line_gl_dist rec5,
2356: ra_customer_trx_lines ctl2,
2357: ra_customer_trx t
2358: WHERE
2359: T.REQUEST_ID = p_request_id
2388:
2389: IF g_autoinv
2390: THEN
2391: /* version tuned for autoinvoice with request_id joins */
2392: UPDATE ra_cust_trx_line_gl_dist lgd
2393: SET (amount, acctd_amount) =
2394: (SELECT /*+ index(LGD2 RA_CUST_TRX_LINE_GL_DIST_N10) */
2395: NVL(lgd.amount, 0) -
2396: (
2390: THEN
2391: /* version tuned for autoinvoice with request_id joins */
2392: UPDATE ra_cust_trx_line_gl_dist lgd
2393: SET (amount, acctd_amount) =
2394: (SELECT /*+ index(LGD2 RA_CUST_TRX_LINE_GL_DIST_N10) */
2395: NVL(lgd.amount, 0) -
2396: (
2397: SUM(lgd2.amount) -
2398: (
2438: )
2439: )
2440: ) /* accounted amount */
2441: FROM
2442: ra_cust_trx_line_gl_dist lgd2,
2443: ra_customer_trx_lines ctl,
2444: ra_customer_trx ct,
2445: ra_cust_trx_line_gl_dist rec1
2446: WHERE
2441: FROM
2442: ra_cust_trx_line_gl_dist lgd2,
2443: ra_customer_trx_lines ctl,
2444: ra_customer_trx ct,
2445: ra_cust_trx_line_gl_dist rec1
2446: WHERE
2447: rec1.customer_trx_id = lgd.customer_trx_id
2448: AND rec1.account_class = 'REC'
2449: AND rec1.latest_rec_flag = 'Y'
2465: ctl.revenue_amount,
2466: ct.exchange_rate
2467: ),
2468: percent =
2469: (SELECT /*+ index(LGD4 RA_CUST_TRX_LINE_GL_DIST_N10) */
2470: DECODE(lgd.account_class || lgd.account_set_flag,
2471: 'SUSPENSEN', lgd.percent,
2472: 'UNBILLN', lgd.percent,
2473: 'UNEARNN', lgd.percent,
2479: 100, 0)
2480: )
2481: ) /* percent */
2482: FROM
2483: ra_cust_trx_line_gl_dist lgd4,
2484: ra_cust_trx_line_gl_dist rec2
2485: WHERE
2486: rec2.customer_trx_id = lgd.customer_trx_id
2487: AND rec2.account_class = 'REC'
2480: )
2481: ) /* percent */
2482: FROM
2483: ra_cust_trx_line_gl_dist lgd4,
2484: ra_cust_trx_line_gl_dist rec2
2485: WHERE
2486: rec2.customer_trx_id = lgd.customer_trx_id
2487: AND rec2.account_class = 'REC'
2488: AND rec2.latest_rec_flag = 'Y'
2502: last_update_date = sysdate
2503: WHERE cust_trx_line_gl_dist_id IN
2504: (SELECT /*+ leading(T,LGD3,REC3,CTL)
2505: use_hash(CTL) index(CTL RA_CUSTOMER_TRX_LINES_N4)
2506: index(LGD3 RA_CUST_TRX_LINE_GL_DIST_N6)
2507: index(REC3 RA_CUST_TRX_LINE_GL_DIST_N6) */
2508: MIN(DECODE(lgd3.gl_posted_date,
2509: NULL, lgd3.cust_trx_line_gl_dist_id,
2510: NULL) )
2503: WHERE cust_trx_line_gl_dist_id IN
2504: (SELECT /*+ leading(T,LGD3,REC3,CTL)
2505: use_hash(CTL) index(CTL RA_CUSTOMER_TRX_LINES_N4)
2506: index(LGD3 RA_CUST_TRX_LINE_GL_DIST_N6)
2507: index(REC3 RA_CUST_TRX_LINE_GL_DIST_N6) */
2508: MIN(DECODE(lgd3.gl_posted_date,
2509: NULL, lgd3.cust_trx_line_gl_dist_id,
2510: NULL) )
2511: FROM
2509: NULL, lgd3.cust_trx_line_gl_dist_id,
2510: NULL) )
2511: FROM
2512: ra_customer_trx_lines ctl,
2513: ra_cust_trx_line_gl_dist lgd3,
2514: ra_cust_trx_line_gl_dist rec3,
2515: ra_customer_trx t
2516: WHERE
2517: t.customer_trx_id = p_customer_trx_id
2510: NULL) )
2511: FROM
2512: ra_customer_trx_lines ctl,
2513: ra_cust_trx_line_gl_dist lgd3,
2514: ra_cust_trx_line_gl_dist rec3,
2515: ra_customer_trx t
2516: WHERE
2517: t.customer_trx_id = p_customer_trx_id
2518: AND rec3.customer_trx_id = t.customer_trx_id
2581: )
2582: UNION
2583: SELECT /*+ leading(CTL2 LGD5,REC5)
2584: use_hash(LGD5) index(CTL2 RA_CUSTOMER_TRX_LINES_N4)
2585: index(REC5 RA_CUST_TRX_LINE_GL_DIST_N6)
2586: index(LGD5 RA_CUST_TRX_LINE_GL_DIST_N6) */
2587: TO_NUMBER(
2588: MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
2589: lgd5.account_set_flag,
2582: UNION
2583: SELECT /*+ leading(CTL2 LGD5,REC5)
2584: use_hash(LGD5) index(CTL2 RA_CUSTOMER_TRX_LINES_N4)
2585: index(REC5 RA_CUST_TRX_LINE_GL_DIST_N6)
2586: index(LGD5 RA_CUST_TRX_LINE_GL_DIST_N6) */
2587: TO_NUMBER(
2588: MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
2589: lgd5.account_set_flag,
2590: 'REVN', lgd5.cust_trx_line_gl_dist_id,
2601: )
2602: )
2603: )
2604: FROM
2605: ra_cust_trx_line_gl_dist rec5,
2606: ra_cust_trx_line_gl_dist lgd5,
2607: ra_customer_trx_lines ctl2
2608: WHERE
2609: ctl2.customer_trx_id = p_customer_trx_id
2602: )
2603: )
2604: FROM
2605: ra_cust_trx_line_gl_dist rec5,
2606: ra_cust_trx_line_gl_dist lgd5,
2607: ra_customer_trx_lines ctl2
2608: WHERE
2609: ctl2.customer_trx_id = p_customer_trx_id
2610: AND ctl2.request_id = g_autoinv_request_id
2636: );
2637:
2638: ELSE
2639: /* original version (used by forms and Rev Rec */
2640: UPDATE ra_cust_trx_line_gl_dist lgd
2641: SET (amount, acctd_amount) =
2642: (SELECT NVL(lgd.amount, 0) -
2643: (
2644: SUM(lgd2.amount) -
2685: )
2686: )
2687: ) /* accounted amount */
2688: FROM
2689: ra_cust_trx_line_gl_dist lgd2,
2690: ra_customer_trx_lines ctl,
2691: ra_customer_trx ct,
2692: ra_cust_trx_line_gl_dist rec1
2693: WHERE
2688: FROM
2689: ra_cust_trx_line_gl_dist lgd2,
2690: ra_customer_trx_lines ctl,
2691: ra_customer_trx ct,
2692: ra_cust_trx_line_gl_dist rec1
2693: WHERE
2694: rec1.customer_trx_id = lgd.customer_trx_id
2695: AND rec1.account_class = 'REC'
2696: AND rec1.latest_rec_flag = 'Y'
2724: 100, 0)
2725: )
2726: ) /* percent */
2727: FROM
2728: ra_cust_trx_line_gl_dist lgd4,
2729: ra_cust_trx_line_gl_dist rec2
2730: WHERE
2731: rec2.customer_trx_id = lgd.customer_trx_id
2732: AND rec2.account_class = 'REC'
2725: )
2726: ) /* percent */
2727: FROM
2728: ra_cust_trx_line_gl_dist lgd4,
2729: ra_cust_trx_line_gl_dist rec2
2730: WHERE
2731: rec2.customer_trx_id = lgd.customer_trx_id
2732: AND rec2.account_class = 'REC'
2733: AND rec2.latest_rec_flag = 'Y'
2749: NULL, lgd3.cust_trx_line_gl_dist_id,
2750: NULL) )
2751: FROM
2752: ra_customer_trx_lines ctl,
2753: ra_cust_trx_line_gl_dist lgd3,
2754: ra_cust_trx_line_gl_dist rec3,
2755: ra_customer_trx t
2756: WHERE
2757: t.customer_trx_id = p_customer_trx_id
2750: NULL) )
2751: FROM
2752: ra_customer_trx_lines ctl,
2753: ra_cust_trx_line_gl_dist lgd3,
2754: ra_cust_trx_line_gl_dist rec3,
2755: ra_customer_trx t
2756: WHERE
2757: t.customer_trx_id = p_customer_trx_id
2758: AND rec3.customer_trx_id = t.customer_trx_id
2835: )
2836: )
2837: )
2838: FROM
2839: ra_cust_trx_line_gl_dist rec5,
2840: ra_cust_trx_line_gl_dist lgd5,
2841: ra_customer_trx_lines ctl2
2842: WHERE
2843: ctl2.customer_trx_id = p_customer_trx_id
2836: )
2837: )
2838: FROM
2839: ra_cust_trx_line_gl_dist rec5,
2840: ra_cust_trx_line_gl_dist lgd5,
2841: ra_customer_trx_lines ctl2
2842: WHERE
2843: ctl2.customer_trx_id = p_customer_trx_id
2844: AND rec5.customer_trx_id = lgd5.customer_trx_id
2872:
2873: IF (p_customer_trx_line_id IS NOT NULL)
2874: THEN
2875:
2876: UPDATE ra_cust_trx_line_gl_dist lgd
2877: SET (amount, acctd_amount) =
2878: (SELECT NVL(lgd.amount, 0) -
2879: (
2880: SUM(lgd2.amount) -
2921: )
2922: )
2923: ) /* accounted amount */
2924: FROM
2925: ra_cust_trx_line_gl_dist lgd2,
2926: ra_customer_trx_lines ctl,
2927: ra_customer_trx ct,
2928: ra_cust_trx_line_gl_dist rec1
2929: WHERE
2924: FROM
2925: ra_cust_trx_line_gl_dist lgd2,
2926: ra_customer_trx_lines ctl,
2927: ra_customer_trx ct,
2928: ra_cust_trx_line_gl_dist rec1
2929: WHERE
2930: rec1.customer_trx_id = lgd.customer_trx_id
2931: and rec1.account_class = 'REC'
2932: and rec1.latest_rec_flag = 'Y'
2960: 100, 0)
2961: )
2962: ) /* percent */
2963: FROM
2964: ra_cust_trx_line_gl_dist lgd4,
2965: ra_cust_trx_line_gl_dist rec2
2966: WHERE
2967: rec2.customer_trx_id = lgd.customer_trx_id
2968: AND rec2.account_class = 'REC'
2961: )
2962: ) /* percent */
2963: FROM
2964: ra_cust_trx_line_gl_dist lgd4,
2965: ra_cust_trx_line_gl_dist rec2
2966: WHERE
2967: rec2.customer_trx_id = lgd.customer_trx_id
2968: AND rec2.account_class = 'REC'
2969: AND rec2.latest_rec_flag = 'Y'
2984: (SELECT MIN(DECODE(lgd3.gl_posted_date,
2985: NULL, lgd3.cust_trx_line_gl_dist_id,
2986: NULL) )
2987: FROM
2988: ra_cust_trx_line_gl_dist lgd3,
2989: ra_cust_trx_line_gl_dist rec3,
2990: ra_customer_trx t,
2991: ra_customer_trx_lines ctl
2992: WHERE
2985: NULL, lgd3.cust_trx_line_gl_dist_id,
2986: NULL) )
2987: FROM
2988: ra_cust_trx_line_gl_dist lgd3,
2989: ra_cust_trx_line_gl_dist rec3,
2990: ra_customer_trx t,
2991: ra_customer_trx_lines ctl
2992: WHERE
2993: ctl.customer_trx_line_id = p_customer_trx_line_id
3069: 'UNBILLY', lgd5.cust_trx_line_gl_dist_id,
3070: NULL) )
3071: )
3072: FROM
3073: ra_cust_trx_line_gl_dist lgd5,
3074: ra_cust_trx_line_gl_dist rec5,
3075: ra_customer_trx_lines ctl2
3076: WHERE
3077: ctl2.customer_trx_line_id = p_customer_trx_line_id
3070: NULL) )
3071: )
3072: FROM
3073: ra_cust_trx_line_gl_dist lgd5,
3074: ra_cust_trx_line_gl_dist rec5,
3075: ra_customer_trx_lines ctl2
3076: WHERE
3077: ctl2.customer_trx_line_id = p_customer_trx_line_id
3078: AND rec5.customer_trx_id = lgd5.customer_trx_id
3395: DECODE(g.rec_offset_flag, 'Y', 1, -1)) ROUND_ACCT_AMT,
3396: /* END ACCTD_AMOUNT LOGIC */
3397: g.rec_offset_flag
3398: from ra_customer_trx_lines l,
3399: ra_cust_trx_line_gl_dist g,
3400: ra_customer_trx t,
3401: ra_rules r
3402: where t.customer_trx_id = p_trx_id
3403: and l.customer_trx_id = t.customer_trx_id
3413: and g.account_class IN ('REV','UNEARN','UNBILL')
3414: and g.account_set_flag = 'N'
3415: /* Only round lines that actually have a rec_offset row */
3416: and exists ( SELECT 'has rof row'
3417: FROM ra_cust_trx_line_gl_dist rof
3418: WHERE rof.customer_trx_line_id = g.customer_trx_line_id
3419: AND rof.account_set_flag = 'N'
3420: AND rof.account_class in ('UNEARN','UNBILL')
3421: AND rof.rec_offset_flag = 'Y')
3561:
3562: IF l_phase <= 3
3563: THEN
3564: FORALL i IN t_line_id.FIRST .. t_line_id.LAST
3565: UPDATE ra_cust_trx_line_gl_dist
3566: SET amount = amount + t_round_amount(i),
3567: percent = percent + t_round_percent(i),
3568: acctd_amount = acctd_amount + t_round_acctd(i),
3569: last_updated_by = arp_global.last_updated_by,
3571: WHERE cust_trx_line_gl_dist_id in (
3572: /* SELECT GL_DIST_ID FOR EACH LINE THAT
3573: REQUIRES ROUNDING */
3574: select MAX(g.cust_trx_line_gl_dist_id)
3575: from ra_cust_trx_line_gl_dist g,
3576: ra_cust_trx_line_gl_dist gmax,
3577: ra_customer_trx_lines tl
3578: where g.customer_trx_line_id = t_line_id(i)
3579: and tl.customer_trx_line_id = g.customer_trx_line_id
3572: /* SELECT GL_DIST_ID FOR EACH LINE THAT
3573: REQUIRES ROUNDING */
3574: select MAX(g.cust_trx_line_gl_dist_id)
3575: from ra_cust_trx_line_gl_dist g,
3576: ra_cust_trx_line_gl_dist gmax,
3577: ra_customer_trx_lines tl
3578: where g.customer_trx_line_id = t_line_id(i)
3579: and tl.customer_trx_line_id = g.customer_trx_line_id
3580: and g.account_class = t_account_class(i)
3585: and nvl(g.rec_offset_flag, '~') = nvl(t_rec_offset(i), '~')
3586: /* FORCES USE OF ROW IN LAST PERIOD */
3587: and g.gl_date = (
3588: select max(gl_date)
3589: from ra_cust_trx_line_gl_dist gdmax
3590: where gdmax.customer_trx_line_id = g.customer_trx_line_id
3591: and gdmax.account_class = g.account_class
3592: and nvl(gdmax.rec_offset_flag, '~') =
3593: nvl(g.rec_offset_flag, '~')
3628: cover some odd corner cases. */
3629:
3630:
3631: FORALL i in t_line_id.first .. t_line_id.last
3632: INSERT INTO RA_CUST_TRX_LINE_GL_DIST
3633: (CUST_TRX_LINE_GL_DIST_ID,
3634: CREATED_BY,
3635: CREATION_DATE,
3636: LAST_UPDATED_BY,
3677: CUST_TRX_LINE_SALESREP_ID,
3678: ROUNDING_CORRECTION_FLAG
3679: )
3680: SELECT
3681: RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL,
3682: CREATED_BY,
3683: CREATION_DATE,
3684: LAST_UPDATED_BY,
3685: LAST_UPDATE_DATE,
3727: ORG_ID,
3728: REQUEST_ID,
3729: CUST_TRX_LINE_SALESREP_ID,
3730: 'Y'
3731: FROM RA_CUST_TRX_LINE_GL_DIST_ALL
3732: WHERE CUST_TRX_LINE_GL_DIST_ID IN (
3733: /* SELECT GL_DIST_ID FOR EACH LINE THAT
3734: REQUIRES ROUNDING */
3735: select
3742: sign(tl.revenue_amount * -1), '2', '1') ||
3743: ltrim(to_char(abs(g.amount),'099999999999999.00')) ||
3744: ltrim(to_char(g.cust_trx_line_gl_dist_id,
3745: '0999999999999999999999'))),28))
3746: from ra_cust_trx_line_gl_dist g,
3747: ra_customer_trx_lines tl
3748: where g.customer_trx_line_id = t_line_id(i)
3749: and tl.customer_trx_line_id = g.customer_trx_line_id
3750: and g.account_class = t_account_class(i)
3972: for each account_class */
3973:
3974: CURSOR round_rows_by_trx(p_base_mau NUMBER,
3975: p_base_precision NUMBER) IS
3976: select /*+ leading(gt t) index(l ra_customer_trx_lines_u1) index(g ra_cust_trx_line_gl_dist_n1)*/
3977: l.customer_trx_line_id, g.account_class,
3978: /* AMOUNT LOGIC */
3979: (gt.amount
3980: - (sum(g.amount)
3996: /* END ACCTD_AMOUNT LOGIC */
3997: gt.revenue_adjustment_id
3998: from ra_customer_trx_lines l,
3999: ar_line_rev_adj_gt gt,
4000: ra_cust_trx_line_gl_dist g,
4001: ra_customer_trx t
4002: where t.customer_trx_id = gt.customer_trx_id
4003: and l.customer_trx_id = t.customer_trx_id
4004: and l.customer_trx_id = g.customer_trx_id
4104:
4105: IF l_phase <=3
4106: THEN
4107: FORALL i IN t_line_id.FIRST .. t_line_id.LAST
4108: UPDATE ra_cust_trx_line_gl_dist
4109: SET amount = amount + t_round_amount(i),
4110: percent = percent + t_round_percent(i),
4111: acctd_amount = acctd_amount + t_round_acctd(i),
4112: last_updated_by = arp_global.last_updated_by,
4114: WHERE cust_trx_line_gl_dist_id in (
4115: /* SELECT GL_DIST_ID FOR EACH LINE THAT
4116: REQUIRES ROUNDING */
4117: select MAX(g.cust_trx_line_gl_dist_id)
4118: from ra_cust_trx_line_gl_dist g,
4119: ra_cust_trx_line_gl_dist gmax,
4120: ra_customer_trx_lines tl
4121: where g.customer_trx_line_id = t_line_id(i)
4122: and tl.customer_trx_line_id = g.customer_trx_line_id
4115: /* SELECT GL_DIST_ID FOR EACH LINE THAT
4116: REQUIRES ROUNDING */
4117: select MAX(g.cust_trx_line_gl_dist_id)
4118: from ra_cust_trx_line_gl_dist g,
4119: ra_cust_trx_line_gl_dist gmax,
4120: ra_customer_trx_lines tl
4121: where g.customer_trx_line_id = t_line_id(i)
4122: and tl.customer_trx_line_id = g.customer_trx_line_id
4123: and g.account_class = t_account_class(i)
4130: and g.revenue_adjustment_id = t_rev_adj_id(i)
4131: /* FORCES USE OF ROW IN LAST PERIOD */
4132: and g.gl_date = (
4133: select max(gl_date)
4134: from ra_cust_trx_line_gl_dist gdmax
4135: where gdmax.customer_trx_line_id = g.customer_trx_line_id
4136: and gdmax.account_class = g.account_class
4137: and nvl(gdmax.rec_offset_flag, '~') =
4138: nvl(g.rec_offset_flag, '~')
4188: and the effect of that rounding makes the distributions
4189: change signs unpredictably. This is just FYI */
4190:
4191: FORALL i in t_line_id.first .. t_line_id.last
4192: INSERT INTO RA_CUST_TRX_LINE_GL_DIST
4193: (CUST_TRX_LINE_GL_DIST_ID,
4194: CREATED_BY,
4195: CREATION_DATE,
4196: LAST_UPDATED_BY,
4239: EVENT_ID,
4240: ROUNDING_CORRECTION_FLAG
4241: )
4242: SELECT
4243: RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL,
4244: CREATED_BY,
4245: CREATION_DATE,
4246: LAST_UPDATED_BY,
4247: LAST_UPDATE_DATE,
4291: CUST_TRX_LINE_SALESREP_ID,
4292: REVENUE_ADJUSTMENT_ID,
4293: EVENT_ID,
4294: 'Y'
4295: FROM RA_CUST_TRX_LINE_GL_DIST_ALL
4296: WHERE CUST_TRX_LINE_GL_DIST_ID IN (
4297: /* SELECT GL_DIST_ID FOR EACH LINE THAT
4298: REQUIRES ROUNDING */
4299: select
4306: sign(tl.revenue_amount * -1), '2', '1') ||
4307: ltrim(to_char(abs(g.amount),'099999999999999.00')) ||
4308: ltrim(to_char(g.cust_trx_line_gl_dist_id,
4309: '0999999999999999999999'))),28))
4310: from ra_cust_trx_line_gl_dist g,
4311: ra_customer_trx_lines tl
4312: where g.customer_trx_line_id = t_line_id(i)
4313: and tl.customer_trx_line_id = g.customer_trx_line_id
4314: and g.account_class = t_account_class(i)
4497: | correct_line_level_rounding |
4498: | |
4499: | DESCRIPTION |
4500: | This function calls functions to correct rounding errors in |
4501: | ra_cust_trx_line_gl_dist. |
4502: | |
4503: | REQUIRES |
4504: | P_CUSTOMER_TRX_ID |
4505: | |
4674: | correct_header_level_rounding |
4675: | |
4676: | DESCRIPTION |
4677: | This function calls functions to correct rounding errors in |
4678: | ra_cust_trx_line_gl_dist. |
4679: | |
4680: | REQUIRES |
4681: | P_CUSTOMER_TRX_ID |
4682: | |
4922: 'select rec.customer_trx_id,
4923: rec.code_combination_id,
4924: round.customer_trx_id
4925: from
4926: ra_cust_trx_line_gl_dist rec,
4927: ra_cust_trx_line_gl_dist round
4928: where
4929: rec.customer_trx_id = round.customer_trx_id(+)
4930: and rec.account_set_flag = round.account_set_flag(+)' ||
4923: rec.code_combination_id,
4924: round.customer_trx_id
4925: from
4926: ra_cust_trx_line_gl_dist rec,
4927: ra_cust_trx_line_gl_dist round
4928: where
4929: rec.customer_trx_id = round.customer_trx_id(+)
4930: and rec.account_set_flag = round.account_set_flag(+)' ||
4931: l_where_pred ||
4988: | do_header_level_rounding |
4989: | |
4990: | DESCRIPTION |
4991: | This function inserts a record of account_class = ROUND into |
4992: | ra_cust_trx_line_gl_dist table. If the transaction was created before |
4993: | setting the header level rounding option On then this function will |
4994: | insert the round record only if there is no activity on it otherwise |
4995: | it will do the release 10 rounding (do_line_level_rounding). |
4996: | Also if arp_rounding is called from revenue recognition program then |
5184: | correct_dist_rounding_errors() |
5185: | |
5186: | DESCRIPTION |
5187: | This function corrects all rounding errors in the |
5188: | ra_cust_trx_line_gl_dist table. |
5189: | |
5190: | REQUIRES |
5191: | P_REQUEST_ID, P_CUSTOMER_TRX_ID or P_CUSTOMER_TRX_LINE_ID |
5192: | If header level rounding is enforced then requires either of |