[Home] [Help]
92: | 0 = no action needed, 1 = set rows, -1 = failure
93: | 14-MAY-2008 M Raymond 7039838 - FT tuning
94: +===========================================================================*/
95: PROCEDURE set_rec_offset_flag(p_customer_trx_id IN
96: ra_customer_trx.customer_trx_id%type,
97: p_request_id IN ra_customer_trx.request_id%type,
98: p_result OUT NOCOPY NUMBER ) IS
99:
100: CURSOR inv_needing_rof(pp_request_id NUMBER) IS
93: | 14-MAY-2008 M Raymond 7039838 - FT tuning
94: +===========================================================================*/
95: PROCEDURE set_rec_offset_flag(p_customer_trx_id IN
96: ra_customer_trx.customer_trx_id%type,
97: p_request_id IN ra_customer_trx.request_id%type,
98: p_result OUT NOCOPY NUMBER ) IS
99:
100: CURSOR inv_needing_rof(pp_request_id NUMBER) IS
101: SELECT DISTINCT inv_trx.customer_trx_id
98: p_result OUT NOCOPY NUMBER ) IS
99:
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
99:
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
141: */
142:
143: SELECT count(*)
144: INTO l_no_rof
145: FROM ra_customer_trx_lines tl
146: WHERE tl.customer_trx_id = p_customer_trx_id
147: AND tl.line_type = 'LINE'
148: AND tl.autorule_complete_flag IS NULL
149: AND tl.accounting_rule_id IS NOT NULL
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) */
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
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
187: AND tl.accounting_rule_id is not null
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
213: AND tl.customer_trx_line_id = g.customer_trx_line_id
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
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
363: | that they balance, too.
364: |
365: +===========================================================================*/
366: PROCEDURE true_lines_by_gl_date(p_customer_trx_id IN
367: ra_customer_trx.customer_trx_id%type) IS
368:
369: /* Cursor for TRUing by gl_date
370: Detects GL_DATES that do not sum to zero.
371: This is usually due to behavior of older
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 (+)
384: and nvl(h.invoicing_rule_id, prev_h.invoicing_rule_id) is not null
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 (+)
384: and nvl(h.invoicing_rule_id, prev_h.invoicing_rule_id) is not null
385: and g.customer_trx_id = h.customer_trx_id
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
402: and h.previous_customer_trx_id = prev_h.customer_trx_id (+)
403: and nvl(h.invoicing_rule_id, prev_h.invoicing_rule_id) is not null
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
402: and h.previous_customer_trx_id = prev_h.customer_trx_id (+)
403: and nvl(h.invoicing_rule_id, prev_h.invoicing_rule_id) is not null
404: and g.customer_trx_line_id = gt.customer_trx_line_id
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
480: and sign(g.amount) = sign(tl.revenue_amount)
558: | as it was raising an error unnecessarily
559: | 01-JUN-04 M Raymond Converted from FUNCTION to PROCEDURE
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
1329: ) * fc.minimum_accountable_unit
1330: )
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
1330: )
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
1457: (select 0,
1458: l_line_round_acctd_amount,
1459: nvl(balanced_round_ccid,-1),
1460: concatenated_segments
1461: from ra_customer_trx ct
1462: where ct.customer_trx_id = dist.customer_trx_id
1463: ),
1464: last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
1465: last_update_date = sysdate
1575: )
1576: - nvl(rec.acctd_amount, 0)
1577: ), /* acctd_amount */
1578: rec.percent + (100 - rec.percent) /* percent */
1579: from ra_customer_trx t,
1580: ra_customer_trx_lines l
1581: where t.customer_trx_id = l.customer_trx_id
1582: and t.customer_trx_id = rec.customer_trx_id
1583: group by l.customer_trx_id,
1576: - nvl(rec.acctd_amount, 0)
1577: ), /* acctd_amount */
1578: rec.percent + (100 - rec.percent) /* percent */
1579: from ra_customer_trx t,
1580: ra_customer_trx_lines l
1581: where t.customer_trx_id = l.customer_trx_id
1582: and t.customer_trx_id = rec.customer_trx_id
1583: group by l.customer_trx_id,
1584: t.trx_number,
1590: last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
1591: last_update_date = sysdate
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
1591: last_update_date = sysdate
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'
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) ),
1659: nvl(rec.acctd_amount, 0) +
1660: ( decode(p_base_mau,
1668: )
1669: - nvl(rec.acctd_amount, 0)
1670: ), /* acctd_amount */
1671: rec.percent + (100 - rec.percent) /* percent */
1672: from ra_customer_trx t,
1673: ra_customer_trx_lines l
1674: where t.customer_trx_id = l.customer_trx_id
1675: and l.customer_trx_id = rec.customer_trx_id
1676: and l.request_id = g_autoinv_request_id -- 7039838
1669: - nvl(rec.acctd_amount, 0)
1670: ), /* acctd_amount */
1671: rec.percent + (100 - rec.percent) /* percent */
1672: from ra_customer_trx t,
1673: ra_customer_trx_lines l
1674: where t.customer_trx_id = l.customer_trx_id
1675: and l.customer_trx_id = rec.customer_trx_id
1676: and l.request_id = g_autoinv_request_id -- 7039838
1677: group by l.customer_trx_id,
1684: last_updated_by = arp_global.last_updated_by, /*Bug 2089972 */
1685: last_update_date = sysdate
1686: where customer_trx_id in
1687: ( select /*+ leading(T,D,L) use_hash(L)
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
1686: where customer_trx_id in
1687: ( select /*+ leading(T,D,L) use_hash(L)
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
1687: ( select /*+ leading(T,D,L) use_hash(L)
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
1754: )
1755: - nvl(rec.acctd_amount, 0)
1756: ), /* acctd_amount */
1757: rec.percent + (100 - rec.percent) /* percent */
1758: from ra_customer_trx t,
1759: ra_customer_trx_lines l
1760: where t.customer_trx_id = l.customer_trx_id
1761: and l.customer_trx_id = rec.customer_trx_id
1762: group by l.customer_trx_id,
1755: - nvl(rec.acctd_amount, 0)
1756: ), /* acctd_amount */
1757: rec.percent + (100 - rec.percent) /* percent */
1758: from ra_customer_trx t,
1759: ra_customer_trx_lines l
1760: where t.customer_trx_id = l.customer_trx_id
1761: and l.customer_trx_id = rec.customer_trx_id
1762: group by l.customer_trx_id,
1763: t.trx_number,
1769: last_updated_by = arp_global.last_updated_by, /*Bug 2089972 */
1770: last_update_date = sysdate
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
1770: last_update_date = sysdate
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'
1925: - NVL(rec.acctd_amount, 0)
1926: ), /* acctd_amount */
1927: rec.percent + (100 - rec.percent) /* percent */
1928: FROM
1929: ra_customer_trx_lines l,
1930: ra_customer_trx t
1931: WHERE
1932: t.customer_trx_id = rec.customer_trx_id
1933: AND l.customer_trx_id = t.customer_trx_id
1926: ), /* acctd_amount */
1927: rec.percent + (100 - rec.percent) /* percent */
1928: FROM
1929: ra_customer_trx_lines l,
1930: ra_customer_trx t
1931: WHERE
1932: t.customer_trx_id = rec.customer_trx_id
1933: AND l.customer_trx_id = t.customer_trx_id
1934: GROUP BY
1940: WHERE customer_trx_id IN
1941: ( SELECT
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
1941: ( SELECT
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
2013: - NVL(rec.acctd_amount, 0)
2014: ),
2015: rec.percent + (100 - rec.percent) /* percent */
2016: FROM
2017: ra_customer_trx_lines l,
2018: ra_customer_trx t
2019: WHERE
2020: t.customer_trx_id = rec.customer_trx_id
2021: AND l.customer_trx_id = t.customer_trx_id
2014: ),
2015: rec.percent + (100 - rec.percent) /* percent */
2016: FROM
2017: ra_customer_trx_lines l,
2018: ra_customer_trx t
2019: WHERE
2020: t.customer_trx_id = rec.customer_trx_id
2021: AND l.customer_trx_id = t.customer_trx_id
2022: GROUP BY
2028: WHERE customer_trx_id IN
2029: ( SELECT
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
2029: ( SELECT
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
2190: THEN
2191:
2192: /* Bug 2539296 - The sql below was slightly restructured for better
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) =
2223: )
2224: )
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
2224: )
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
2287: MIN(DECODE(lgd3.gl_posted_date,
2288: NULL, lgd3.cust_trx_line_gl_dist_id,
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
2288: NULL, lgd3.cust_trx_line_gl_dist_id,
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
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,
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
2362: AND T.CUSTOMER_TRX_ID = REC5.CUSTOMER_TRX_ID
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
2362: AND T.CUSTOMER_TRX_ID = REC5.CUSTOMER_TRX_ID
2363: AND CTL2.CUSTOMER_TRX_LINE_ID = LGD5.CUSTOMER_TRX_LINE_ID
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
2449: rec1.customer_trx_id = lgd.customer_trx_id
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
2449: rec1.customer_trx_id = lgd.customer_trx_id
2450: AND rec1.account_class = 'REC'
2503: last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
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,
2510: MIN(DECODE(lgd3.gl_posted_date,
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
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
2521: AND rec3.account_class = 'REC'
2582: )
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||
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
2613: AND rec5.customer_trx_id = lgd5.customer_trx_id
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
2699: rec1.customer_trx_id = lgd.customer_trx_id
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
2699: rec1.customer_trx_id = lgd.customer_trx_id
2700: AND rec1.account_class = 'REC'
2753: (SELECT MIN(DECODE(lgd3.gl_posted_date,
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
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
2764: AND rec3.account_class = 'REC'
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'
2851: AND rec5.latest_rec_flag = 'Y'
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
2932: rec1.customer_trx_id = lgd.customer_trx_id
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
2932: rec1.customer_trx_id = lgd.customer_trx_id
2933: and rec1.account_class = 'REC'
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
2996: AND t.customer_trx_id = ctl.customer_trx_id
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
2996: AND t.customer_trx_id = ctl.customer_trx_id
2997: AND rec3.customer_trx_id = t.customer_trx_id
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
3081: AND rec5.account_class = 'REC'
3396: * DECODE(g.account_class, 'REV', 1,
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
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
3406: and l.customer_trx_id = g.customer_trx_id
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)
3620: and g.account_set_flag = 'N'
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)
3760: and g.account_set_flag = 'N'
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)
4003: * DECODE(g.account_class, 'REV', 1, -1)))
4004: * DECODE(g.account_class, 'REV', 1, -1) ROUND_ACCT_AMT,
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
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
4014: and l.customer_trx_line_id = g.customer_trx_line_id
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)
4133: and g.account_set_flag = 'N'
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)
4324: and g.account_set_flag = 'N'
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'
5363: AND line_dist.account_set_flag = 'N'
5497: SUM(
5498: NVL(ctls1.revenue_percent_split, 0)
5499: )
5500: )
5501: FROM ra_customer_trx_lines ctl1,
5502: ra_cust_trx_line_salesreps ctls1
5503: WHERE ctl1.customer_trx_line_id = ctls1.customer_trx_line_id
5504: AND ctls.customer_trx_line_id = ctls1.customer_trx_line_id
5505: GROUP BY ctls1.customer_trx_line_id,
5510: WHERE ctls.cust_trx_line_salesrep_id in
5511: (
5512: SELECT MIN(cust_trx_line_salesrep_id)
5513: FROM ra_cust_trx_line_salesreps ctls,
5514: ra_customer_trx_lines ctl
5515: WHERE ctl.customer_trx_line_id = ctls.customer_trx_line_id
5516: AND ctl.customer_trx_id = p_customer_trx_id
5517: GROUP BY ctls.customer_trx_line_id,
5518: ctl.extended_amount