[Home] [Help]
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
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)
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
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
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,
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
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: - nvl(rec.acctd_amount, 0)
1755: ), /* acctd_amount */
1756: rec.percent + (100 - rec.percent) /* percent */
1757: from ra_customer_trx t,
1758: ra_customer_trx_lines l
1759: where t.customer_trx_id = l.customer_trx_id
1760: and l.customer_trx_id = rec.customer_trx_id
1761: group by l.customer_trx_id,
1762: t.trx_number,
1769: last_update_date = sysdate
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'
1924: - NVL(rec.acctd_amount, 0)
1925: ), /* acctd_amount */
1926: rec.percent + (100 - rec.percent) /* percent */
1927: FROM
1928: ra_customer_trx_lines l,
1929: ra_customer_trx t
1930: WHERE
1931: t.customer_trx_id = rec.customer_trx_id
1932: AND l.customer_trx_id = t.customer_trx_id
1939: WHERE customer_trx_id IN
1940: ( SELECT
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
2012: - NVL(rec.acctd_amount, 0)
2013: ),
2014: rec.percent + (100 - rec.percent) /* percent */
2015: FROM
2016: ra_customer_trx_lines l,
2017: ra_customer_trx t
2018: WHERE
2019: t.customer_trx_id = rec.customer_trx_id
2020: AND l.customer_trx_id = t.customer_trx_id
2028: ( SELECT
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
2222: )
2223: )
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
2285: MIN(DECODE(lgd3.gl_posted_date,
2286: NULL, lgd3.cust_trx_line_gl_dist_id,
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
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
2360: AND T.CUSTOMER_TRX_ID = REC5.CUSTOMER_TRX_ID
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
2447: rec1.customer_trx_id = lgd.customer_trx_id
2501: last_updated_by = arp_global.last_updated_by, /* Bug 2089972 */
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,
2508: MIN(DECODE(lgd3.gl_posted_date,
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
2580: )
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||
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
2611: AND rec5.customer_trx_id = lgd5.customer_trx_id
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
2694: rec1.customer_trx_id = lgd.customer_trx_id
2748: (SELECT MIN(DECODE(lgd3.gl_posted_date,
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
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
2845: AND rec5.account_class = 'REC'
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
2930: rec1.customer_trx_id = lgd.customer_trx_id
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
2994: AND t.customer_trx_id = ctl.customer_trx_id
2995: AND rec3.customer_trx_id = t.customer_trx_id
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
3079: AND rec5.account_class = 'REC'
3394: * DECODE(g.account_class, 'REV', 1,
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
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)
3581: and g.account_set_flag = 'N'
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)
3751: and g.account_set_flag = 'N'
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)
3994: * DECODE(g.account_class, 'REV', 1, -1)))
3995: * DECODE(g.account_class, 'REV', 1, -1) ROUND_ACCT_AMT,
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
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)
4124: and g.account_set_flag = 'N'
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)
4315: and g.account_set_flag = 'N'
5427: SUM(
5428: NVL(ctls1.revenue_percent_split, 0)
5429: )
5430: )
5431: FROM ra_customer_trx_lines ctl1,
5432: ra_cust_trx_line_salesreps ctls1
5433: WHERE ctl1.customer_trx_line_id = ctls1.customer_trx_line_id
5434: AND ctls.customer_trx_line_id = ctls1.customer_trx_line_id
5435: GROUP BY ctls1.customer_trx_line_id,
5440: WHERE ctls.cust_trx_line_salesrep_id in
5441: (
5442: SELECT MIN(cust_trx_line_salesrep_id)
5443: FROM ra_cust_trx_line_salesreps ctls,
5444: ra_customer_trx_lines ctl
5445: WHERE ctl.customer_trx_line_id = ctls.customer_trx_line_id
5446: AND ctl.customer_trx_id = p_customer_trx_id
5447: GROUP BY ctls.customer_trx_line_id,
5448: ctl.extended_amount