eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ARP_ROUNDING dependencies on RA_CUST_TRX_LINE_GL_DIST

Line 13: trx_for_rof ra_cust_trx_line_gl_dist_all.customer_trx_id%type;

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

Line 15: rqid_for_rof ra_cust_trx_line_gl_dist_all.request_id%type;

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;

Line 22: TYPE l_line_id_type IS TABLE OF ra_cust_trx_line_gl_dist_all.customer_trx_line_id%type

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

Line 24: TYPE l_amount_type IS TABLE OF ra_cust_trx_line_gl_dist_all.amount%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

Line 26: TYPE l_percent_type IS TABLE OF ra_cust_trx_line_gl_dist_all.percent%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

Line 28: TYPE l_acct_class IS TABLE OF ra_cust_trx_line_gl_dist_all.account_class%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

Line 30: TYPE l_rec_offset IS TABLE OF ra_cust_trx_line_gl_dist_all.rec_offset_flag%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:

Line 32: TYPE l_date_type IS TABLE OF ra_cust_trx_line_gl_dist_all.gl_date%type

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:

Line 53: | Sets the rec_offset_flag in ra_cust_trx_line_gl_dist for REC offsetting|

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.

Line 104: RA_CUST_TRX_LINE_GL_DIST inv_rec

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

Line 115: FROM ra_cust_trx_line_gl_dist g2

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');

Line 153: FROM ra_cust_trx_line_gl_dist g2

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');

Line 176: UPDATE RA_CUST_TRX_LINE_GL_DIST

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) */

Line 182: FROM ra_cust_trx_line_gl_dist g,

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

Line 184: ra_cust_trx_line_gl_dist grec

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

Line 203: UPDATE RA_CUST_TRX_LINE_GL_DIST

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

Line 208: FROM ra_cust_trx_line_gl_dist g,

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

Line 210: ra_cust_trx_line_gl_dist grec

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'

Line 269: UPDATE RA_CUST_TRX_LINE_GL_DIST G

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

Line 275: ra_cust_trx_line_gl_dist inv_g,

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 =

Line 276: ra_cust_trx_line_gl_dist inv_grec

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

Line 379: from ra_cust_trx_line_gl_dist g,

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 (+)

Line 397: from ra_cust_trx_line_gl_dist g,

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

Line 465: UPDATE ra_cust_trx_line_gl_dist g

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,

Line 475: from ra_cust_trx_line_gl_dist g,

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

Line 564: l_acctd_correction ra_cust_trx_line_gl_dist.acctd_amount%type;

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:

Line 577: UPDATE RA_CUST_TRX_LINE_GL_DIST

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

Line 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

Line 588: FROM ra_cust_trx_line_gl_dist g2

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));

Line 927: | ra_cust_trx_line_gl_dist table. |

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. |

Line 955: | ra_cust_trx_line_gl_dist |

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:

Line 990: insert into ra_cust_trx_line_gl_dist

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 ,

Line 1056: RA_CUST_TRX_LINE_GL_DIST_s.nextval,

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,

Line 1088: from ra_cust_trx_line_gl_dist rec

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

Line 1095: from ra_cust_trx_line_gl_dist dist2

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: */

Line 1101: from ra_cust_trx_line_gl_dist dist2

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:

Line 1124: insert into ra_cust_trx_line_gl_dist

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 ,

Line 1190: RA_CUST_TRX_LINE_GL_DIST_s.nextval,

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,

Line 1222: from ra_cust_trx_line_gl_dist rec

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

Line 1229: from ra_cust_trx_line_gl_dist dist2

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: */

Line 1235: from ra_cust_trx_line_gl_dist dist2

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:

Line 1249: FROM ra_cust_trx_line_gl_dist rec

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:

Line 1263: p_table_name => 'RA_CUST_TRX_LINE_GL_DIST',

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

Line 1335: ra_cust_trx_line_gl_dist rec,

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

Line 1370: from ra_cust_trx_line_gl_dist lgd,

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'

Line 1371: ra_cust_trx_line_gl_dist 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'

Line 1455: update ra_cust_trx_line_gl_dist dist

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),

Line 1562: update ra_cust_trx_line_gl_dist rec

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) +

Line 1596: ra_cust_trx_line_gl_dist d

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'

Line 1654: update ra_cust_trx_line_gl_dist rec

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) ),

Line 1692: ra_cust_trx_line_gl_dist d

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

Line 1741: update ra_cust_trx_line_gl_dist rec

1737:
1738: ELSE
1739:
1740: /* Not autoinvoice, probably Rev Rec or forms logic */
1741: update ra_cust_trx_line_gl_dist rec
1742: set (amount, acctd_amount, percent) =
1743: ( select nvl(rec.amount, 0) +
1744: (sum(l.extended_amount) - nvl(rec.amount, 0) ),
1745: nvl(rec.acctd_amount, 0) +

Line 1775: ra_cust_trx_line_gl_dist d

1771: where customer_trx_id in
1772: ( select l.customer_trx_id
1773: from ra_customer_trx t,
1774: ra_customer_trx_lines l,
1775: ra_cust_trx_line_gl_dist d
1776: where t.customer_trx_id = l.customer_trx_id
1777: and l.customer_trx_id = d.customer_trx_id
1778: and d.account_class = 'REC'
1779: and d.latest_rec_flag = 'Y'

Line 1908: UPDATE ra_cust_trx_line_gl_dist rec

1904: * Bug 13434104 *
1905: * Removed the call to gl_currency_api.convert_amount *
1906: ******************************************************/
1907:
1908: UPDATE ra_cust_trx_line_gl_dist rec
1909: SET (amount, acctd_amount, percent) =
1910: ( SELECT
1911: NVL(rec.amount, 0) +
1912: (SUM(l.extended_amount) - NVL(rec.amount, 0) ),

Line 1946: ra_cust_trx_line_gl_dist d

1942: l.customer_trx_id
1943: FROM
1944: ra_customer_trx_lines l,
1945: ra_customer_trx t,
1946: ra_cust_trx_line_gl_dist d
1947: WHERE
1948: t.customer_trx_id = d.customer_trx_id
1949: AND l.customer_trx_id = t.customer_trx_id
1950: AND d.account_class = 'REC'

Line 1995: UPDATE ra_cust_trx_line_gl_dist rec

1991:
1992: IF (p_customer_trx_id IS NOT NULL)
1993: THEN
1994:
1995: UPDATE ra_cust_trx_line_gl_dist rec
1996: SET (amount, acctd_amount, percent) =
1997: ( SELECT
1998: NVL(rec.amount, 0) +
1999: (SUM(l.extended_amount) - NVL(rec.amount, 0) ),

Line 2034: ra_cust_trx_line_gl_dist d

2030: l.customer_trx_id
2031: FROM
2032: ra_customer_trx t,
2033: ra_customer_trx_lines l,
2034: ra_cust_trx_line_gl_dist d
2035: WHERE
2036: t.customer_trx_id = d.customer_trx_id
2037: AND l.customer_trx_id = t.customer_trx_id
2038: AND d.account_class = 'REC'

Line 2197: UPDATE ra_cust_trx_line_gl_dist lgd

2193: performance in large databases. I basically restructured the join
2194: order around ra_customer_trx instead of the gl_dist tables.
2195: */
2196:
2197: UPDATE ra_cust_trx_line_gl_dist lgd
2198: SET (amount, acctd_amount) =
2199: (SELECT /*+ index(rec1 RA_CUST_TRX_LINE_GL_DIST_N6) ordered */ NVL(lgd.amount, 0) -
2200: (
2201: SUM(lgd2.amount) -

Line 2199: (SELECT /+ index(rec1 RA_CUST_TRX_LINE_GL_DIST_N6) ordered / NVL(lgd.amount, 0) -

2195: */
2196:
2197: UPDATE ra_cust_trx_line_gl_dist lgd
2198: SET (amount, acctd_amount) =
2199: (SELECT /*+ index(rec1 RA_CUST_TRX_LINE_GL_DIST_N6) ordered */ NVL(lgd.amount, 0) -
2200: (
2201: SUM(lgd2.amount) -
2202: (
2203: DECODE(lgd.gl_date,

Line 2229: ra_cust_trx_line_gl_dist lgd2,

2225: ) /* accounted amount */
2226: FROM
2227: ra_customer_trx_lines ctl,
2228: ra_customer_trx ct,
2229: ra_cust_trx_line_gl_dist lgd2,
2230: ra_cust_trx_line_gl_dist rec1
2231: WHERE
2232: ctl.customer_trx_line_id = lgd2.customer_trx_line_id
2233: AND ctl.customer_trx_id = ct.customer_trx_id

Line 2230: ra_cust_trx_line_gl_dist rec1

2226: FROM
2227: ra_customer_trx_lines ctl,
2228: ra_customer_trx ct,
2229: ra_cust_trx_line_gl_dist lgd2,
2230: ra_cust_trx_line_gl_dist rec1
2231: WHERE
2232: ctl.customer_trx_line_id = lgd2.customer_trx_line_id
2233: AND ctl.customer_trx_id = ct.customer_trx_id
2234: AND lgd.customer_trx_line_id = ctl.customer_trx_line_id

Line 2251: (SELECT /+ index(rec2 RA_CUST_TRX_LINE_GL_DIST_N6) / DECODE(lgd.account_class || lgd.account_set_flag,

2247: ctl.revenue_amount,
2248: ct.exchange_rate
2249: ),
2250: percent =
2251: (SELECT /*+ index(rec2 RA_CUST_TRX_LINE_GL_DIST_N6) */ DECODE(lgd.account_class || lgd.account_set_flag,
2252: 'SUSPENSEN', lgd.percent,
2253: 'UNBILLN', lgd.percent,
2254: 'UNEARNN', lgd.percent,
2255: NVL(lgd.percent, 0) -

Line 2265: ra_cust_trx_line_gl_dist lgd4,

2261: 0)
2262: )
2263: ) /* percent */
2264: FROM
2265: ra_cust_trx_line_gl_dist lgd4,
2266: ra_cust_trx_line_gl_dist rec2
2267: WHERE
2268: lgd.customer_trx_line_id = lgd4.customer_trx_line_id
2269: AND rec2.customer_trx_id = lgd.customer_trx_id

Line 2266: ra_cust_trx_line_gl_dist rec2

2262: )
2263: ) /* percent */
2264: FROM
2265: ra_cust_trx_line_gl_dist lgd4,
2266: ra_cust_trx_line_gl_dist rec2
2267: WHERE
2268: lgd.customer_trx_line_id = lgd4.customer_trx_line_id
2269: AND rec2.customer_trx_id = lgd.customer_trx_id
2270: AND rec2.customer_trx_id = lgd4.customer_trx_id

Line 2286: (SELECT /+ index(rec3 RA_CUST_TRX_LINE_GL_DIST_N6) /

2282: ),
2283: last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
2284: last_update_date = sysdate
2285: WHERE cust_trx_line_gl_dist_id IN
2286: (SELECT /*+ index(rec3 RA_CUST_TRX_LINE_GL_DIST_N6) */
2287: MIN(DECODE(lgd3.gl_posted_date,
2288: NULL, lgd3.cust_trx_line_gl_dist_id,
2289: NULL) )
2290: FROM

Line 2293: ra_cust_trx_line_gl_dist lgd3,

2289: NULL) )
2290: FROM
2291: ra_customer_trx_lines ctl,
2292: ra_customer_trx t,
2293: ra_cust_trx_line_gl_dist lgd3,
2294: ra_cust_trx_line_gl_dist rec3
2295: WHERE
2296: t.request_id = p_request_id
2297: AND T.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID

Line 2294: ra_cust_trx_line_gl_dist rec3

2290: FROM
2291: ra_customer_trx_lines ctl,
2292: ra_customer_trx t,
2293: ra_cust_trx_line_gl_dist lgd3,
2294: ra_cust_trx_line_gl_dist rec3
2295: WHERE
2296: t.request_id = p_request_id
2297: AND T.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
2298: AND (CTL.LINE_TYPE IN ( 'TAX','FREIGHT','CHARGES','SUSPENSE' ) OR

Line 2339: SELECT /+ index(rec5 RA_CUST_TRX_LINE_GL_DIST_N6) INDEX (lgd5 ra_cust_trx_line_gl_dist_n6) index(ctl2 ra_customer_trx_lines_u1) /

2335: ) / p_base_mau ) * p_base_mau
2336: )
2337: )
2338: UNION
2339: SELECT /*+ index(rec5 RA_CUST_TRX_LINE_GL_DIST_N6) INDEX (lgd5 ra_cust_trx_line_gl_dist_n6) index(ctl2 ra_customer_trx_lines_u1) */
2340: TO_NUMBER(
2341: MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
2342: lgd5.account_set_flag,
2343: 'REVN', lgd5.cust_trx_line_gl_dist_id,

Line 2356: ra_cust_trx_line_gl_dist lgd5,

2352: 'UNBILLY', lgd5.cust_trx_line_gl_dist_id,
2353: NULL ) )
2354: )
2355: FROM
2356: ra_cust_trx_line_gl_dist lgd5,
2357: ra_cust_trx_line_gl_dist rec5,
2358: ra_customer_trx_lines ctl2,
2359: ra_customer_trx t
2360: WHERE

Line 2357: ra_cust_trx_line_gl_dist rec5,

2353: NULL ) )
2354: )
2355: FROM
2356: ra_cust_trx_line_gl_dist lgd5,
2357: ra_cust_trx_line_gl_dist rec5,
2358: ra_customer_trx_lines ctl2,
2359: ra_customer_trx t
2360: WHERE
2361: T.REQUEST_ID = p_request_id

Line 2394: UPDATE ra_cust_trx_line_gl_dist lgd

2390:
2391: IF g_autoinv
2392: THEN
2393: /* version tuned for autoinvoice with request_id joins */
2394: UPDATE ra_cust_trx_line_gl_dist lgd
2395: SET (amount, acctd_amount) =
2396: (SELECT /*+ index(LGD2 RA_CUST_TRX_LINE_GL_DIST_N10) */
2397: NVL(lgd.amount, 0) -
2398: (

Line 2396: (SELECT /+ index(LGD2 RA_CUST_TRX_LINE_GL_DIST_N10) /

2392: THEN
2393: /* version tuned for autoinvoice with request_id joins */
2394: UPDATE ra_cust_trx_line_gl_dist lgd
2395: SET (amount, acctd_amount) =
2396: (SELECT /*+ index(LGD2 RA_CUST_TRX_LINE_GL_DIST_N10) */
2397: NVL(lgd.amount, 0) -
2398: (
2399: SUM(lgd2.amount) -
2400: (

Line 2444: ra_cust_trx_line_gl_dist lgd2,

2440: )
2441: )
2442: ) /* accounted amount */
2443: FROM
2444: ra_cust_trx_line_gl_dist lgd2,
2445: ra_customer_trx_lines ctl,
2446: ra_customer_trx ct,
2447: ra_cust_trx_line_gl_dist rec1
2448: WHERE

Line 2447: ra_cust_trx_line_gl_dist rec1

2443: FROM
2444: ra_cust_trx_line_gl_dist lgd2,
2445: ra_customer_trx_lines ctl,
2446: ra_customer_trx ct,
2447: ra_cust_trx_line_gl_dist rec1
2448: WHERE
2449: rec1.customer_trx_id = lgd.customer_trx_id
2450: AND rec1.account_class = 'REC'
2451: AND rec1.latest_rec_flag = 'Y'

Line 2471: (SELECT /+ index(LGD4 RA_CUST_TRX_LINE_GL_DIST_N10) /

2467: ctl.revenue_amount,
2468: ct.exchange_rate
2469: ),
2470: percent =
2471: (SELECT /*+ index(LGD4 RA_CUST_TRX_LINE_GL_DIST_N10) */
2472: DECODE(lgd.account_class || lgd.account_set_flag,
2473: 'SUSPENSEN', lgd.percent,
2474: 'UNBILLN', lgd.percent,
2475: 'UNEARNN', lgd.percent,

Line 2485: ra_cust_trx_line_gl_dist lgd4,

2481: 100, 0)
2482: )
2483: ) /* percent */
2484: FROM
2485: ra_cust_trx_line_gl_dist lgd4,
2486: ra_cust_trx_line_gl_dist rec2
2487: WHERE
2488: rec2.customer_trx_id = lgd.customer_trx_id
2489: AND rec2.account_class = 'REC'

Line 2486: ra_cust_trx_line_gl_dist rec2

2482: )
2483: ) /* percent */
2484: FROM
2485: ra_cust_trx_line_gl_dist lgd4,
2486: ra_cust_trx_line_gl_dist rec2
2487: WHERE
2488: rec2.customer_trx_id = lgd.customer_trx_id
2489: AND rec2.account_class = 'REC'
2490: AND rec2.latest_rec_flag = 'Y'

Line 2508: index(LGD3 RA_CUST_TRX_LINE_GL_DIST_N6)

2504: last_update_date = sysdate
2505: WHERE cust_trx_line_gl_dist_id IN
2506: (SELECT /*+ leading(T,LGD3,REC3,CTL)
2507: use_hash(CTL) index(CTL RA_CUSTOMER_TRX_LINES_N4)
2508: index(LGD3 RA_CUST_TRX_LINE_GL_DIST_N6)
2509: index(REC3 RA_CUST_TRX_LINE_GL_DIST_N6) */
2510: MIN(DECODE(lgd3.gl_posted_date,
2511: NULL, lgd3.cust_trx_line_gl_dist_id,
2512: NULL) )

Line 2509: index(REC3 RA_CUST_TRX_LINE_GL_DIST_N6) */

2505: WHERE cust_trx_line_gl_dist_id IN
2506: (SELECT /*+ leading(T,LGD3,REC3,CTL)
2507: use_hash(CTL) index(CTL RA_CUSTOMER_TRX_LINES_N4)
2508: index(LGD3 RA_CUST_TRX_LINE_GL_DIST_N6)
2509: index(REC3 RA_CUST_TRX_LINE_GL_DIST_N6) */
2510: MIN(DECODE(lgd3.gl_posted_date,
2511: NULL, lgd3.cust_trx_line_gl_dist_id,
2512: NULL) )
2513: FROM

Line 2515: ra_cust_trx_line_gl_dist lgd3,

2511: NULL, lgd3.cust_trx_line_gl_dist_id,
2512: NULL) )
2513: FROM
2514: ra_customer_trx_lines ctl,
2515: ra_cust_trx_line_gl_dist lgd3,
2516: ra_cust_trx_line_gl_dist rec3,
2517: ra_customer_trx t
2518: WHERE
2519: t.customer_trx_id = p_customer_trx_id

Line 2516: ra_cust_trx_line_gl_dist rec3,

2512: NULL) )
2513: FROM
2514: ra_customer_trx_lines ctl,
2515: ra_cust_trx_line_gl_dist lgd3,
2516: ra_cust_trx_line_gl_dist rec3,
2517: ra_customer_trx t
2518: WHERE
2519: t.customer_trx_id = p_customer_trx_id
2520: AND rec3.customer_trx_id = t.customer_trx_id

Line 2587: index(REC5 RA_CUST_TRX_LINE_GL_DIST_N6)

2583: )
2584: UNION
2585: SELECT /*+ leading(CTL2 LGD5,REC5)
2586: use_hash(LGD5) index(CTL2 RA_CUSTOMER_TRX_LINES_N4)
2587: index(REC5 RA_CUST_TRX_LINE_GL_DIST_N6)
2588: index(LGD5 RA_CUST_TRX_LINE_GL_DIST_N6) */
2589: TO_NUMBER(
2590: MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
2591: lgd5.account_set_flag,

Line 2588: index(LGD5 RA_CUST_TRX_LINE_GL_DIST_N6) */

2584: UNION
2585: SELECT /*+ leading(CTL2 LGD5,REC5)
2586: use_hash(LGD5) index(CTL2 RA_CUSTOMER_TRX_LINES_N4)
2587: index(REC5 RA_CUST_TRX_LINE_GL_DIST_N6)
2588: index(LGD5 RA_CUST_TRX_LINE_GL_DIST_N6) */
2589: TO_NUMBER(
2590: MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
2591: lgd5.account_set_flag,
2592: 'REVN', lgd5.cust_trx_line_gl_dist_id,

Line 2607: ra_cust_trx_line_gl_dist rec5,

2603: )
2604: )
2605: )
2606: FROM
2607: ra_cust_trx_line_gl_dist rec5,
2608: ra_cust_trx_line_gl_dist lgd5,
2609: ra_customer_trx_lines ctl2
2610: WHERE
2611: ctl2.customer_trx_id = p_customer_trx_id

Line 2608: ra_cust_trx_line_gl_dist lgd5,

2604: )
2605: )
2606: FROM
2607: ra_cust_trx_line_gl_dist rec5,
2608: ra_cust_trx_line_gl_dist lgd5,
2609: ra_customer_trx_lines ctl2
2610: WHERE
2611: ctl2.customer_trx_id = p_customer_trx_id
2612: AND ctl2.request_id = g_autoinv_request_id

Line 2645: UPDATE ra_cust_trx_line_gl_dist lgd

2641: /* original version (used by forms and Rev Rec */
2642:
2643: /* 9160123 - simplied where clause for this statement */
2644:
2645: UPDATE ra_cust_trx_line_gl_dist lgd
2646: SET (amount, acctd_amount) =
2647: (SELECT NVL(lgd.amount, 0) -
2648: (
2649: SUM(lgd2.amount) -

Line 2694: ra_cust_trx_line_gl_dist lgd2,

2690: )
2691: )
2692: ) /* accounted amount */
2693: FROM
2694: ra_cust_trx_line_gl_dist lgd2,
2695: ra_customer_trx_lines ctl,
2696: ra_customer_trx ct,
2697: ra_cust_trx_line_gl_dist rec1
2698: WHERE

Line 2697: ra_cust_trx_line_gl_dist rec1

2693: FROM
2694: ra_cust_trx_line_gl_dist lgd2,
2695: ra_customer_trx_lines ctl,
2696: ra_customer_trx ct,
2697: ra_cust_trx_line_gl_dist rec1
2698: WHERE
2699: rec1.customer_trx_id = lgd.customer_trx_id
2700: AND rec1.account_class = 'REC'
2701: AND rec1.latest_rec_flag = 'Y'

Line 2733: ra_cust_trx_line_gl_dist lgd4,

2729: 100, 0)
2730: )
2731: ) /* percent */
2732: FROM
2733: ra_cust_trx_line_gl_dist lgd4,
2734: ra_cust_trx_line_gl_dist rec2
2735: WHERE
2736: rec2.customer_trx_id = lgd.customer_trx_id
2737: AND rec2.account_class = 'REC'

Line 2734: ra_cust_trx_line_gl_dist rec2

2730: )
2731: ) /* percent */
2732: FROM
2733: ra_cust_trx_line_gl_dist lgd4,
2734: ra_cust_trx_line_gl_dist rec2
2735: WHERE
2736: rec2.customer_trx_id = lgd.customer_trx_id
2737: AND rec2.account_class = 'REC'
2738: AND rec2.latest_rec_flag = 'Y'

Line 2758: ra_cust_trx_line_gl_dist lgd3,

2754: NULL, lgd3.cust_trx_line_gl_dist_id,
2755: NULL) )
2756: FROM
2757: ra_customer_trx_lines ctl,
2758: ra_cust_trx_line_gl_dist lgd3,
2759: ra_cust_trx_line_gl_dist rec3,
2760: ra_customer_trx t
2761: WHERE
2762: t.customer_trx_id = p_customer_trx_id

Line 2759: ra_cust_trx_line_gl_dist rec3,

2755: NULL) )
2756: FROM
2757: ra_customer_trx_lines ctl,
2758: ra_cust_trx_line_gl_dist lgd3,
2759: ra_cust_trx_line_gl_dist rec3,
2760: ra_customer_trx t
2761: WHERE
2762: t.customer_trx_id = p_customer_trx_id
2763: AND rec3.customer_trx_id = t.customer_trx_id

Line 2826: SELECT /+ index( REC5 RA_CUST_TRX_LINE_GL_DIST_N6) /

2822: ) * p_base_mau
2823: )
2824: )
2825: UNION
2826: SELECT /*+ index( REC5 RA_CUST_TRX_LINE_GL_DIST_N6) */
2827: TO_NUMBER(
2828: MIN(DECODE(lgd5.gl_posted_date||lgd5.account_class||
2829: lgd5.account_set_flag,
2830: 'REVN', lgd5.cust_trx_line_gl_dist_id,

Line 2845: ra_cust_trx_line_gl_dist rec5,

2841: )
2842: )
2843: )
2844: FROM
2845: ra_cust_trx_line_gl_dist rec5,
2846: ra_cust_trx_line_gl_dist lgd5,
2847: ra_customer_trx_lines ctl2
2848: WHERE
2849: rec5.customer_trx_id = p_customer_trx_id

Line 2846: ra_cust_trx_line_gl_dist lgd5,

2842: )
2843: )
2844: FROM
2845: ra_cust_trx_line_gl_dist rec5,
2846: ra_cust_trx_line_gl_dist lgd5,
2847: ra_customer_trx_lines ctl2
2848: WHERE
2849: rec5.customer_trx_id = p_customer_trx_id
2850: AND rec5.account_class = 'REC'

Line 2878: UPDATE ra_cust_trx_line_gl_dist lgd

2874:
2875: IF (p_customer_trx_line_id IS NOT NULL)
2876: THEN
2877:
2878: UPDATE ra_cust_trx_line_gl_dist lgd
2879: SET (amount, acctd_amount) =
2880: (SELECT NVL(lgd.amount, 0) -
2881: (
2882: SUM(lgd2.amount) -

Line 2927: ra_cust_trx_line_gl_dist lgd2,

2923: )
2924: )
2925: ) /* accounted amount */
2926: FROM
2927: ra_cust_trx_line_gl_dist lgd2,
2928: ra_customer_trx_lines ctl,
2929: ra_customer_trx ct,
2930: ra_cust_trx_line_gl_dist rec1
2931: WHERE

Line 2930: ra_cust_trx_line_gl_dist rec1

2926: FROM
2927: ra_cust_trx_line_gl_dist lgd2,
2928: ra_customer_trx_lines ctl,
2929: ra_customer_trx ct,
2930: ra_cust_trx_line_gl_dist rec1
2931: WHERE
2932: rec1.customer_trx_id = lgd.customer_trx_id
2933: and rec1.account_class = 'REC'
2934: and rec1.latest_rec_flag = 'Y'

Line 2966: ra_cust_trx_line_gl_dist lgd4,

2962: 100, 0)
2963: )
2964: ) /* percent */
2965: FROM
2966: ra_cust_trx_line_gl_dist lgd4,
2967: ra_cust_trx_line_gl_dist rec2
2968: WHERE
2969: rec2.customer_trx_id = lgd.customer_trx_id
2970: AND rec2.account_class = 'REC'

Line 2967: ra_cust_trx_line_gl_dist rec2

2963: )
2964: ) /* percent */
2965: FROM
2966: ra_cust_trx_line_gl_dist lgd4,
2967: ra_cust_trx_line_gl_dist rec2
2968: WHERE
2969: rec2.customer_trx_id = lgd.customer_trx_id
2970: AND rec2.account_class = 'REC'
2971: AND rec2.latest_rec_flag = 'Y'

Line 2990: ra_cust_trx_line_gl_dist lgd3,

2986: (SELECT MIN(DECODE(lgd3.gl_posted_date,
2987: NULL, lgd3.cust_trx_line_gl_dist_id,
2988: NULL) )
2989: FROM
2990: ra_cust_trx_line_gl_dist lgd3,
2991: ra_cust_trx_line_gl_dist rec3,
2992: ra_customer_trx t,
2993: ra_customer_trx_lines ctl
2994: WHERE

Line 2991: ra_cust_trx_line_gl_dist rec3,

2987: NULL, lgd3.cust_trx_line_gl_dist_id,
2988: NULL) )
2989: FROM
2990: ra_cust_trx_line_gl_dist lgd3,
2991: ra_cust_trx_line_gl_dist rec3,
2992: ra_customer_trx t,
2993: ra_customer_trx_lines ctl
2994: WHERE
2995: ctl.customer_trx_line_id = p_customer_trx_line_id

Line 3075: ra_cust_trx_line_gl_dist lgd5,

3071: 'UNBILLY', lgd5.cust_trx_line_gl_dist_id,
3072: NULL) )
3073: )
3074: FROM
3075: ra_cust_trx_line_gl_dist lgd5,
3076: ra_cust_trx_line_gl_dist rec5,
3077: ra_customer_trx_lines ctl2
3078: WHERE
3079: ctl2.customer_trx_line_id = p_customer_trx_line_id

Line 3076: ra_cust_trx_line_gl_dist rec5,

3072: NULL) )
3073: )
3074: FROM
3075: ra_cust_trx_line_gl_dist lgd5,
3076: ra_cust_trx_line_gl_dist rec5,
3077: ra_customer_trx_lines ctl2
3078: WHERE
3079: ctl2.customer_trx_line_id = p_customer_trx_line_id
3080: AND rec5.customer_trx_id = lgd5.customer_trx_id

Line 3401: ra_cust_trx_line_gl_dist g,

3397: DECODE(g.rec_offset_flag, 'Y', 1, -1)) ROUND_ACCT_AMT,
3398: /* END ACCTD_AMOUNT LOGIC */
3399: g.rec_offset_flag
3400: from ra_customer_trx_lines l,
3401: ra_cust_trx_line_gl_dist g,
3402: ra_customer_trx t,
3403: ra_rules r
3404: where t.customer_trx_id = p_trx_id
3405: and l.customer_trx_id = t.customer_trx_id

Line 3419: FROM ra_cust_trx_line_gl_dist rof

3415: and g.account_class IN ('REV','UNEARN','UNBILL')
3416: and g.account_set_flag = 'N'
3417: /* Only round lines that actually have a rec_offset row */
3418: and exists ( SELECT 'has rof row'
3419: FROM ra_cust_trx_line_gl_dist rof
3420: WHERE rof.customer_trx_line_id = g.customer_trx_line_id
3421: AND rof.account_set_flag = 'N'
3422: AND rof.account_class in ('UNEARN','UNBILL')
3423: AND rof.rec_offset_flag = 'Y')

Line 3595: UPDATE ra_cust_trx_line_gl_dist

3591: one UNEARN(non-rof) for each trx_line_id. */
3592:
3593:
3594: FORALL i IN t_line_id.FIRST .. t_line_id.LAST
3595: UPDATE ra_cust_trx_line_gl_dist
3596: SET amount = amount + t_round_amount(i),
3597: percent = percent + t_round_percent(i),
3598: acctd_amount = acctd_amount + t_round_acctd(i),
3599: last_updated_by = arp_global.last_updated_by,

Line 3615: from ra_cust_trx_line_gl_dist g,

3611: sign(tl.revenue_amount * -1), '2', '1') ||
3612: ltrim(to_char(abs(g.amount),'099999999999999.00')) ||
3613: ltrim(to_char(g.cust_trx_line_gl_dist_id,
3614: '0999999999999999999999'))),28))
3615: from ra_cust_trx_line_gl_dist g,
3616: ra_customer_trx_lines tl
3617: where g.customer_trx_line_id = t_line_id(i)
3618: and tl.customer_trx_line_id = g.customer_trx_line_id
3619: and g.account_class = t_account_class(i)

Line 3641: INSERT INTO RA_CUST_TRX_LINE_GL_DIST

3637: cover some odd corner cases. */
3638:
3639:
3640: FORALL i in t_line_id.first .. t_line_id.last
3641: INSERT INTO RA_CUST_TRX_LINE_GL_DIST
3642: (CUST_TRX_LINE_GL_DIST_ID,
3643: CREATED_BY,
3644: CREATION_DATE,
3645: LAST_UPDATED_BY,

Line 3690: RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL,

3686: CUST_TRX_LINE_SALESREP_ID,
3687: ROUNDING_CORRECTION_FLAG
3688: )
3689: SELECT
3690: RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL,
3691: CREATED_BY,
3692: CREATION_DATE,
3693: LAST_UPDATED_BY,
3694: LAST_UPDATE_DATE,

Line 3740: FROM RA_CUST_TRX_LINE_GL_DIST_ALL

3736: ORG_ID,
3737: REQUEST_ID,
3738: CUST_TRX_LINE_SALESREP_ID,
3739: 'Y'
3740: FROM RA_CUST_TRX_LINE_GL_DIST_ALL
3741: WHERE CUST_TRX_LINE_GL_DIST_ID IN (
3742: /* SELECT GL_DIST_ID FOR EACH LINE THAT
3743: REQUIRES ROUNDING */
3744: select

Line 3755: from ra_cust_trx_line_gl_dist g,

3751: sign(tl.revenue_amount * -1), '2', '1') ||
3752: ltrim(to_char(abs(g.amount),'099999999999999.00')) ||
3753: ltrim(to_char(g.cust_trx_line_gl_dist_id,
3754: '0999999999999999999999'))),28))
3755: from ra_cust_trx_line_gl_dist g,
3756: ra_customer_trx_lines tl
3757: where g.customer_trx_line_id = t_line_id(i)
3758: and tl.customer_trx_line_id = g.customer_trx_line_id
3759: and g.account_class = t_account_class(i)

Line 3985: select /+ leading(gt t) index(l ra_customer_trx_lines_u1) index(g ra_cust_trx_line_gl_dist_n1)/

3981: for each account_class */
3982:
3983: CURSOR round_rows_by_trx(p_base_mau NUMBER,
3984: p_base_precision NUMBER) IS
3985: select /*+ leading(gt t) index(l ra_customer_trx_lines_u1) index(g ra_cust_trx_line_gl_dist_n1)*/
3986: l.customer_trx_line_id, g.account_class,
3987: /* AMOUNT LOGIC */
3988: (gt.amount
3989: - (sum(g.amount)

Line 4009: ra_cust_trx_line_gl_dist g,

4005: /* END ACCTD_AMOUNT LOGIC */
4006: gt.revenue_adjustment_id
4007: from ra_customer_trx_lines l,
4008: ar_line_rev_adj_gt gt,
4009: ra_cust_trx_line_gl_dist g,
4010: ra_customer_trx t
4011: where t.customer_trx_id = gt.customer_trx_id
4012: and l.customer_trx_id = t.customer_trx_id
4013: and l.customer_trx_id = g.customer_trx_id

Line 4117: UPDATE ra_cust_trx_line_gl_dist

4113:
4114: IF l_phase <=3
4115: THEN
4116: FORALL i IN t_line_id.FIRST .. t_line_id.LAST
4117: UPDATE ra_cust_trx_line_gl_dist
4118: SET amount = amount + t_round_amount(i),
4119: percent = percent + t_round_percent(i),
4120: acctd_amount = acctd_amount + t_round_acctd(i),
4121: last_updated_by = arp_global.last_updated_by,

Line 4127: from ra_cust_trx_line_gl_dist g,

4123: WHERE cust_trx_line_gl_dist_id in (
4124: /* SELECT GL_DIST_ID FOR EACH LINE THAT
4125: REQUIRES ROUNDING */
4126: select MAX(g.cust_trx_line_gl_dist_id)
4127: from ra_cust_trx_line_gl_dist g,
4128: ra_cust_trx_line_gl_dist gmax,
4129: ra_customer_trx_lines tl
4130: where g.customer_trx_line_id = t_line_id(i)
4131: and tl.customer_trx_line_id = g.customer_trx_line_id

Line 4128: ra_cust_trx_line_gl_dist gmax,

4124: /* SELECT GL_DIST_ID FOR EACH LINE THAT
4125: REQUIRES ROUNDING */
4126: select MAX(g.cust_trx_line_gl_dist_id)
4127: from ra_cust_trx_line_gl_dist g,
4128: ra_cust_trx_line_gl_dist gmax,
4129: ra_customer_trx_lines tl
4130: where g.customer_trx_line_id = t_line_id(i)
4131: and tl.customer_trx_line_id = g.customer_trx_line_id
4132: and g.account_class = t_account_class(i)

Line 4143: from ra_cust_trx_line_gl_dist gdmax

4139: and g.revenue_adjustment_id = t_rev_adj_id(i)
4140: /* FORCES USE OF ROW IN LAST PERIOD */
4141: and g.gl_date = (
4142: select max(gl_date)
4143: from ra_cust_trx_line_gl_dist gdmax
4144: where gdmax.customer_trx_line_id = g.customer_trx_line_id
4145: and gdmax.account_class = g.account_class
4146: and nvl(gdmax.rec_offset_flag, '~') =
4147: nvl(g.rec_offset_flag, '~')

Line 4201: INSERT INTO RA_CUST_TRX_LINE_GL_DIST

4197: and the effect of that rounding makes the distributions
4198: change signs unpredictably. This is just FYI */
4199:
4200: FORALL i in t_line_id.first .. t_line_id.last
4201: INSERT INTO RA_CUST_TRX_LINE_GL_DIST
4202: (CUST_TRX_LINE_GL_DIST_ID,
4203: CREATED_BY,
4204: CREATION_DATE,
4205: LAST_UPDATED_BY,

Line 4252: RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL,

4248: EVENT_ID,
4249: ROUNDING_CORRECTION_FLAG
4250: )
4251: SELECT
4252: RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL,
4253: CREATED_BY,
4254: CREATION_DATE,
4255: LAST_UPDATED_BY,
4256: LAST_UPDATE_DATE,

Line 4304: FROM RA_CUST_TRX_LINE_GL_DIST_ALL

4300: CUST_TRX_LINE_SALESREP_ID,
4301: REVENUE_ADJUSTMENT_ID,
4302: EVENT_ID,
4303: 'Y'
4304: FROM RA_CUST_TRX_LINE_GL_DIST_ALL
4305: WHERE CUST_TRX_LINE_GL_DIST_ID IN (
4306: /* SELECT GL_DIST_ID FOR EACH LINE THAT
4307: REQUIRES ROUNDING */
4308: select

Line 4319: from ra_cust_trx_line_gl_dist g,

4315: sign(tl.revenue_amount * -1), '2', '1') ||
4316: ltrim(to_char(abs(g.amount),'099999999999999.00')) ||
4317: ltrim(to_char(g.cust_trx_line_gl_dist_id,
4318: '0999999999999999999999'))),28))
4319: from ra_cust_trx_line_gl_dist g,
4320: ra_customer_trx_lines tl
4321: where g.customer_trx_line_id = t_line_id(i)
4322: and tl.customer_trx_line_id = g.customer_trx_line_id
4323: and g.account_class = t_account_class(i)

Line 4513: | ra_cust_trx_line_gl_dist. |

4509: | correct_line_level_rounding |
4510: | |
4511: | DESCRIPTION |
4512: | This function calls functions to correct rounding errors in |
4513: | ra_cust_trx_line_gl_dist. |
4514: | |
4515: | REQUIRES |
4516: | P_CUSTOMER_TRX_ID |
4517: | |

Line 4690: | ra_cust_trx_line_gl_dist. |

4686: | correct_header_level_rounding |
4687: | |
4688: | DESCRIPTION |
4689: | This function calls functions to correct rounding errors in |
4690: | ra_cust_trx_line_gl_dist. |
4691: | |
4692: | REQUIRES |
4693: | P_CUSTOMER_TRX_ID |
4694: | |

Line 4938: ra_cust_trx_line_gl_dist rec,

4934: 'select rec.customer_trx_id,
4935: rec.code_combination_id,
4936: round.customer_trx_id
4937: from
4938: ra_cust_trx_line_gl_dist rec,
4939: ra_cust_trx_line_gl_dist round
4940: where
4941: rec.customer_trx_id = round.customer_trx_id(+)
4942: and rec.account_set_flag = round.account_set_flag(+)' ||

Line 4939: ra_cust_trx_line_gl_dist round

4935: rec.code_combination_id,
4936: round.customer_trx_id
4937: from
4938: ra_cust_trx_line_gl_dist rec,
4939: ra_cust_trx_line_gl_dist round
4940: where
4941: rec.customer_trx_id = round.customer_trx_id(+)
4942: and rec.account_set_flag = round.account_set_flag(+)' ||
4943: l_where_pred ||

Line 5004: | ra_cust_trx_line_gl_dist table. If the transaction was created before |

5000: | do_header_level_rounding |
5001: | |
5002: | DESCRIPTION |
5003: | This function inserts a record of account_class = ROUND into |
5004: | ra_cust_trx_line_gl_dist table. If the transaction was created before |
5005: | setting the header level rounding option On then this function will |
5006: | insert the round record only if there is no activity on it otherwise |
5007: | it will do the release 10 rounding (do_line_level_rounding). |
5008: | Also if arp_rounding is called from revenue recognition program then |

Line 5200: | ra_cust_trx_line_gl_dist table. |

5196: | correct_dist_rounding_errors() |
5197: | |
5198: | DESCRIPTION |
5199: | This function corrects all rounding errors in the |
5200: | ra_cust_trx_line_gl_dist table. |
5201: | |
5202: | REQUIRES |
5203: | P_REQUEST_ID, P_CUSTOMER_TRX_ID or P_CUSTOMER_TRX_LINE_ID |
5204: | If header level rounding is enforced then requires either of |

Line 5358: FROM ra_cust_trx_line_gl_dist line_dist,

5354:
5355: SELECT line_dist.acctd_amount,line_dist.amount,
5356: arpcurr.currround(line_dist.amount * nvl(ct.exchange_rate,1),ct. invoice_currency_code)
5357: INTO l_rec_acctd_amt,l_rec_amt,l_actual_acctd_amt
5358: FROM ra_cust_trx_line_gl_dist line_dist,
5359: ra_customer_trx ct
5360: WHERE line_dist.customer_trx_id = ct.customer_trx_id
5361: AND line_dist.customer_trx_id = p_customer_trx_id
5362: AND line_dist.account_class = 'REC'

Line 5369: UPDATE ra_cust_trx_line_gl_dist

5365: AND ct.invoicing_rule_id is NULL;
5366:
5367: IF SIGN(l_rec_amt) <> SIGN(l_rec_acctd_amt) AND l_rec_amt <> 0 AND l_rec_acctd_amt <> 0 THEN
5368:
5369: UPDATE ra_cust_trx_line_gl_dist
5370: SET acctd_amount = acctd_amount -
5371: Decode(Sign(l_rec_acctd_amt),-1 ,(Abs(l_actual_acctd_amt)-Abs(l_rec_acctd_amt)),(l_rec_acctd_amt - l_actual_acctd_amt))
5372: WHERE cust_trx_line_gl_dist_id =
5373: (SELECT MAX(cust_trx_line_gl_dist_id)

Line 5374: FROM ra_cust_trx_line_gl_dist

5370: SET acctd_amount = acctd_amount -
5371: Decode(Sign(l_rec_acctd_amt),-1 ,(Abs(l_actual_acctd_amt)-Abs(l_rec_acctd_amt)),(l_rec_acctd_amt - l_actual_acctd_amt))
5372: WHERE cust_trx_line_gl_dist_id =
5373: (SELECT MAX(cust_trx_line_gl_dist_id)
5374: FROM ra_cust_trx_line_gl_dist
5375: WHERE customer_trx_id = p_customer_trx_id
5376: AND account_class = 'REV'
5377: AND account_set_flag = 'N'
5378: AND sign(acctd_amount) = Decode(Sign(l_rec_acctd_amt),-1,

Line 5385: UPDATE ra_cust_trx_line_gl_dist

5381: l_count := sql%ROWCOUNT;
5382:
5383: If (l_count > 0) THEN
5384:
5385: UPDATE ra_cust_trx_line_gl_dist
5386: SET acctd_amount = l_actual_acctd_amt
5387: WHERE customer_trx_id = p_customer_trx_id
5388: AND account_class = 'REC'
5389: AND account_set_flag = 'N'