[Home] [Help]
54: /* bug 4284925 : introduce a variable string which will hold various
55: account_class values : REC, TAX, ROUND, FREIGHT which is used in multiple
56: selects to avoid re-parsing similar statements
57: */
58: acct_class RA_CUST_TRX_LINE_GL_DIST.ACCOUNT_CLASS%TYPE;
59:
60: /*-------------------------------------------------------------------------+
61: | PRIVATE PROCEDURE |
62: | assign_glp_index |
669: | PRIVATE FUNCTION |
670: | create_assignments |
671: | |
672: | DESCRIPTION |
673: | Create revenue assignments in ra_cust_trx_line_gl_dist from any |
674: | un-expanded model accounts within the specified date range |
675: | |
676: | PARAMETERS |
677: | p_trx_id IN NUMBER, |
697: | that uses ar_revenue_assignments view. |
698: | See bug 2143064 for details. |
699: | 13-Aug-02 Debbie Jancis Modified for MRC Trigger replacement |
700: | added calls for |
701: | ra_cust_trx_line_gl_dist processing |
702: | 31-JAN-03 M Raymond Modified MRC cursor to include UNEARN
703: | rows where rec_offset_flag is null.
704: | 02-MAY-03 M Raymond Modified REV insert to include an
705: | outer join to ra_cust_trx_line_salesreps
739: gl_date
740: INTO
741: rec_gl_date
742: FROM
743: ra_cust_trx_line_gl_dist
744: WHERE
745: account_class = 'REC'
746: AND account_set_flag = 'Y'
747: AND customer_trx_id = p_trx_id ;
803: l_need_rev_rec,
804: l_max_rule_end_date
805: FROM ra_customer_trx_lines tl,
806: ra_rules rl,
807: ra_cust_trx_line_gl_dist gld
808: WHERE tl.customer_trx_id = p_trx_id
809: AND tl.accounting_rule_id = rl.rule_id
810: AND tl.customer_trx_id = gld.customer_trx_id (+)
811: AND tl.line_type = 'LINE'
834: /* if A/ACC_DUR rules */
835: IF l_standard_rules > 0
836: THEN
837:
838: INSERT INTO ra_cust_trx_line_gl_dist /* REV lines */
839: (
840: customer_trx_line_id,
841: customer_trx_id,
842: code_combination_id,
948: sysdate,
949: arp_standard.profile.user_id,
950: -3,
951: ass.gl_date, /* original_gl_date */
952: ra_cust_trx_line_gl_dist_s.NEXTVAL, /* cust_trx_line_gl_dist_id */
953: header.org_id
954: FROM
955: ra_customer_trx header,
956: fnd_currencies fc,
955: ra_customer_trx header,
956: fnd_currencies fc,
957: ra_customer_trx_lines lines,
958: ra_rules acc_rule,
959: ra_cust_trx_line_gl_dist rec,
960: ra_cust_trx_line_gl_dist dist,
961: ra_cust_trx_line_salesreps cmsrep,
962: ar_rev_assign_for_std_v ass -- 9560174 only regular rules
963: WHERE
956: fnd_currencies fc,
957: ra_customer_trx_lines lines,
958: ra_rules acc_rule,
959: ra_cust_trx_line_gl_dist rec,
960: ra_cust_trx_line_gl_dist dist,
961: ra_cust_trx_line_salesreps cmsrep,
962: ar_rev_assign_for_std_v ass -- 9560174 only regular rules
963: WHERE
964: header.customer_trx_id = p_trx_id
998: 0, NVL(lines.previous_customer_trx_line_id,
999: lines.customer_trx_line_id),
1000: lines.customer_trx_line_id)
1001: FROM
1002: ra_cust_trx_line_gl_dist subdist2
1003: WHERE
1004: subdist2.customer_trx_line_id = lines.customer_trx_line_id
1005: AND subdist2.account_set_flag = 'Y'
1006: AND subdist2.gl_date IS NULL
1017: (
1018: SELECT
1019: 'distribution exists'
1020: FROM
1021: ra_cust_trx_line_gl_dist subdist
1022: WHERE
1023: subdist.customer_trx_line_id = ass.customer_trx_line_id
1024: AND subdist.customer_trx_id + 0 = lines.customer_trx_id
1025: AND subdist.account_set_flag = 'N'
1068: return -2; --fail and reset indexes
1069: END;
1070: END IF;
1071:
1072: INSERT INTO ra_cust_trx_line_gl_dist /* REV lines */
1073: (
1074: customer_trx_line_id,
1075: customer_trx_id,
1076: code_combination_id,
1182: sysdate,
1183: arp_standard.profile.user_id,
1184: -3,
1185: ass.gl_date, /* original_gl_date */
1186: ra_cust_trx_line_gl_dist_s.NEXTVAL, /* cust_trx_line_gl_dist_id */
1187: header.org_id
1188: FROM
1189: ra_customer_trx header,
1190: fnd_currencies fc,
1189: ra_customer_trx header,
1190: fnd_currencies fc,
1191: ra_customer_trx_lines lines,
1192: ra_rules acc_rule,
1193: ra_cust_trx_line_gl_dist rec,
1194: ra_cust_trx_line_gl_dist dist,
1195: ra_cust_trx_line_salesreps cmsrep,
1196: ar_rev_assign_for_pprr_v ass -- 9560174 PPRR rules only.
1197: WHERE
1190: fnd_currencies fc,
1191: ra_customer_trx_lines lines,
1192: ra_rules acc_rule,
1193: ra_cust_trx_line_gl_dist rec,
1194: ra_cust_trx_line_gl_dist dist,
1195: ra_cust_trx_line_salesreps cmsrep,
1196: ar_rev_assign_for_pprr_v ass -- 9560174 PPRR rules only.
1197: WHERE
1198: header.customer_trx_id = p_trx_id
1232: 0, NVL(lines.previous_customer_trx_line_id,
1233: lines.customer_trx_line_id),
1234: lines.customer_trx_line_id)
1235: FROM
1236: ra_cust_trx_line_gl_dist subdist2
1237: WHERE
1238: subdist2.customer_trx_line_id = lines.customer_trx_line_id
1239: AND subdist2.account_set_flag = 'Y'
1240: AND subdist2.gl_date IS NULL
1251: (
1252: SELECT
1253: 'distribution exists'
1254: FROM
1255: ra_cust_trx_line_gl_dist subdist
1256: WHERE
1257: subdist.customer_trx_line_id = ass.customer_trx_line_id
1258: AND subdist.customer_trx_id + 0 = lines.customer_trx_id
1259: AND subdist.account_set_flag = 'N'
1347: cursor c_update_ps( l_trx_id NUMBER) is
1348: SELECT ps.payment_schedule_id ps_id,
1349: gld.gl_date gl_date
1350: FROM ar_payment_schedules ps,
1351: ra_cust_trx_line_gl_dist gld
1352: WHERE gld.customer_trx_id = l_trx_id
1353: AND gld.account_class = 'REC'
1354: AND gld.account_set_flag = 'N'
1355: AND gld.customer_trx_id = ps.customer_trx_id
1361: arp_standard.debug( 'arp_auto_rule.create_other_receivable()+ ' ||
1362: TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1363: END IF;
1364:
1365: INSERT INTO ra_cust_trx_line_gl_dist /* REC line */
1366: (
1367: customer_trx_id,
1368: code_combination_id,
1369: set_of_books_id,
1411: arp_standard.profile.program_id,
1412: sysdate,
1413: -3, /* posting_control_id */
1414: NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
1415: ra_cust_trx_line_gl_dist_s.NEXTVAL,
1416: header.org_id
1417: FROM
1418: ra_cust_trx_line_gl_dist rec,
1419: ra_customer_trx header
1414: NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
1415: ra_cust_trx_line_gl_dist_s.NEXTVAL,
1416: header.org_id
1417: FROM
1418: ra_cust_trx_line_gl_dist rec,
1419: ra_customer_trx header
1420: WHERE
1421: header.customer_trx_id = p_trx_id
1422: AND header.complete_flag = 'Y'
1429: (
1430: SELECT
1431: 'exist'
1432: FROM
1433: ra_cust_trx_line_gl_dist real_rec
1434: WHERE
1435: real_rec.customer_trx_id = rec.customer_trx_id
1436: AND real_rec.account_class = 'REC'
1437: AND real_rec.account_set_flag = 'N'
1455: /* no mrc columns affected so no update to mrc table needed */
1456:
1457: /* Bug 3416070 - Removed request_id from where clause */
1458:
1459: UPDATE ra_cust_trx_line_gl_dist
1460: SET
1461: latest_rec_flag = 'N',
1462: last_updated_by = arp_standard.profile.user_id,
1463: last_update_date = sysdate
1469: (
1470: SELECT
1471: customer_trx_id
1472: FROM
1473: ra_cust_trx_line_gl_dist
1474: WHERE
1475: customer_trx_id = p_trx_id
1476: AND account_class = 'REC'
1477: AND account_set_flag = 'N'
1563: TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1564: END IF;
1565:
1566:
1567: INSERT INTO ra_cust_trx_line_gl_dist /* ROUND line */
1568: ( /* drive from gl_dist */
1569: customer_trx_id,
1570: code_combination_id,
1571: set_of_books_id,
1609: arp_standard.profile.program_id,
1610: sysdate,
1611: -3, /* posting_control_id */
1612: NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
1613: ra_cust_trx_line_gl_dist_s.nextval,
1614: header.org_id
1615: FROM
1616: ra_customer_trx header,
1617: ra_cust_trx_line_gl_dist rec, /* ROUND row */
1613: ra_cust_trx_line_gl_dist_s.nextval,
1614: header.org_id
1615: FROM
1616: ra_customer_trx header,
1617: ra_cust_trx_line_gl_dist rec, /* ROUND row */
1618: ra_cust_trx_line_gl_dist rrec /* REC row */
1619: WHERE
1620: header.customer_trx_id = p_trx_id
1621: AND header.complete_flag = 'Y'
1614: header.org_id
1615: FROM
1616: ra_customer_trx header,
1617: ra_cust_trx_line_gl_dist rec, /* ROUND row */
1618: ra_cust_trx_line_gl_dist rrec /* REC row */
1619: WHERE
1620: header.customer_trx_id = p_trx_id
1621: AND header.complete_flag = 'Y'
1622: AND header.customer_trx_id = rec.customer_trx_id
1629: AND NOT EXISTS
1630: (
1631: SELECT 'exist'
1632: FROM
1633: ra_cust_trx_line_gl_dist real_rec
1634: WHERE
1635: real_rec.customer_trx_id = rec.customer_trx_id
1636: AND real_rec.account_class = 'ROUND'
1637: AND real_rec.account_set_flag = 'N'
1681: | 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
1682: | 20-MAR-98 S.Jandyala Modified the function to create |
1683: | revenue account assignments by trx_id |
1684: | 10-MAY-02 M Raymond Added column rec_offset_flag to
1685: | ra_cust_trx_line_gl_dist_all. Added
1686: | logic to this insert to populate it
1687: | with a 'Y' if inserting UNEARN or
1688: | UNBILL lines.
1689: | See bug 2150541 for details.
1772: ( l_ctt_type = 'CM' AND l_revrec_run_flag = 'N')
1773: OR
1774: ( l_ctt_type = 'CM' AND g_use_inv_acctg = 'N') /* 5598773 */
1775: THEN
1776: INSERT INTO ra_cust_trx_line_gl_dist /* OTHER */
1777: (
1778: customer_trx_line_id,
1779: customer_trx_id,
1780: code_combination_id,
1891: arp_standard.profile.program_id,
1892: sysdate,
1893: -3, /* posting_control_id */
1894: NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
1895: ra_cust_trx_line_gl_dist_s.NEXTVAL,
1896: /* Bug 2150541 */
1897: DECODE(psum.account_class, 'UNEARN', 'Y',
1898: 'UNBILL', 'Y',
1899: NULL) ,
1900: trx.org_id
1901: FROM
1902: ra_customer_trx_lines psum_lines,
1903: ra_customer_trx psum_trx,
1904: ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
1905: ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
1906: ra_customer_trx_lines lines,
1907: ra_cust_trx_line_gl_dist rec, /* model receivable account */
1908: fnd_currencies fc,
1903: ra_customer_trx psum_trx,
1904: ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
1905: ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
1906: ra_customer_trx_lines lines,
1907: ra_cust_trx_line_gl_dist rec, /* model receivable account */
1908: fnd_currencies fc,
1909: ra_customer_trx trx
1910: WHERE
1911: trx.customer_trx_id = p_trx_id
1935: 0, NVL(lines.previous_customer_trx_line_id,
1936: lines.customer_trx_line_id),
1937: lines.customer_trx_line_id)
1938: FROM
1939: ra_cust_trx_line_gl_dist subdist2
1940: WHERE
1941: subdist2.customer_trx_line_id = lines.customer_trx_line_id
1942: AND subdist2.account_set_flag = 'Y'
1943: AND subdist2.gl_date IS NULL
1959: (
1960: SELECT
1961: 'plug sum account exists'
1962: FROM
1963: ra_cust_trx_line_gl_dist subdist
1964: WHERE
1965: subdist.account_class IN
1966: ( 'SUSPENSE', DECODE(trx.invoicing_rule_id,
1967: -2, 'UNEARN',
1975:
1976: /*3550426 */
1977: ELSE
1978:
1979: INSERT INTO ra_cust_trx_line_gl_dist /* OTHER */
1980: (
1981: customer_trx_line_id,
1982: customer_trx_id,
1983: code_combination_id,
2094: arp_standard.profile.program_id,
2095: sysdate,
2096: -3, /* posting_control_id */
2097: NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
2098: ra_cust_trx_line_gl_dist_s.NEXTVAL,
2099: /* Bug 2150541 */
2100: DECODE(psum.account_class, 'UNEARN', 'Y',
2101: 'UNBILL', 'Y',
2102: NULL),
2103: trx.org_id
2104: FROM
2105: ra_customer_trx_lines psum_lines,
2106: ra_customer_trx psum_trx,
2107: ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
2108: ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
2109: ra_customer_trx_lines lines,
2110: ra_cust_trx_line_gl_dist rec, /* model receivable account */
2111: fnd_currencies fc,
2106: ra_customer_trx psum_trx,
2107: ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
2108: ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
2109: ra_customer_trx_lines lines,
2110: ra_cust_trx_line_gl_dist rec, /* model receivable account */
2111: fnd_currencies fc,
2112: ra_customer_trx trx
2113: WHERE
2114: trx.customer_trx_id = p_trx_id
2147: (
2148: SELECT
2149: 'plug sum account exists'
2150: FROM
2151: ra_cust_trx_line_gl_dist subdist
2152: WHERE
2153: subdist.account_class IN
2154: ( 'SUSPENSE', DECODE(trx.invoicing_rule_id,
2155: -2, 'UNEARN',
2271: ELSE
2272: l_ignore_rule_flag := 'N';
2273: END IF;
2274:
2275: INSERT INTO ra_cust_trx_line_gl_dist /* TAX Lines */
2276: (
2277: customer_trx_line_id,
2278: customer_trx_id,
2279: code_combination_id,
2352: arp_standard.profile.program_id,
2353: sysdate,
2354: -3,
2355: NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
2356: ra_cust_trx_line_gl_dist_s.NEXTVAL,
2357: tax.collected_tax_ccid,
2358: trx.org_id
2359: FROM
2360: ra_customer_trx model_trx,
2357: tax.collected_tax_ccid,
2358: trx.org_id
2359: FROM
2360: ra_customer_trx model_trx,
2361: ra_cust_trx_line_gl_dist tax,
2362: ra_customer_trx_lines tax_line,
2363: ra_customer_trx_lines line_line,
2364: ra_cust_trx_line_gl_dist rec,
2365: fnd_currencies fc,
2360: ra_customer_trx model_trx,
2361: ra_cust_trx_line_gl_dist tax,
2362: ra_customer_trx_lines tax_line,
2363: ra_customer_trx_lines line_line,
2364: ra_cust_trx_line_gl_dist rec,
2365: fnd_currencies fc,
2366: ra_customer_trx trx
2367: WHERE
2368: trx.customer_trx_id = p_trx_id
2386: 0, NVL(tax_line.previous_customer_trx_line_id,
2387: tax_line.customer_trx_line_id),
2388: tax_line.customer_trx_line_id)
2389: FROM
2390: ra_cust_trx_line_gl_dist subdist2
2391: WHERE
2392: subdist2.customer_trx_line_id=tax_line.customer_trx_line_id
2393: AND subdist2.account_set_flag = 'Y'
2394: AND subdist2.gl_date IS NULL
2401: AND NOT EXISTS
2402: (SELECT
2403: 'tax account exists'
2404: FROM
2405: ra_cust_trx_line_gl_dist subdist
2406: WHERE
2407: tax_line.customer_trx_line_id = subdist.customer_trx_line_id
2408: AND subdist.account_set_flag = 'N'
2409: AND subdist.gl_date IS NOT NULL
2474: arp_standard.debug( 'arp_auto_rule.create_other_freight()+ ' ||
2475: TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2476: END IF;
2477:
2478: INSERT INTO ra_cust_trx_line_gl_dist /* FREIGHT Lines */
2479: (
2480: customer_trx_line_id,
2481: customer_trx_id,
2482: code_combination_id,
2551: arp_standard.profile.program_id,
2552: sysdate,
2553: -3,
2554: NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
2555: ra_cust_trx_line_gl_dist_s.NEXTVAL,
2556: trx.org_id
2557: FROM
2558: ra_customer_trx model_trx,
2559: ra_cust_trx_line_gl_dist freight,
2555: ra_cust_trx_line_gl_dist_s.NEXTVAL,
2556: trx.org_id
2557: FROM
2558: ra_customer_trx model_trx,
2559: ra_cust_trx_line_gl_dist freight,
2560: ra_customer_trx_lines lines,
2561: ra_cust_trx_line_gl_dist rec,
2562: fnd_currencies fc,
2563: ra_customer_trx trx
2557: FROM
2558: ra_customer_trx model_trx,
2559: ra_cust_trx_line_gl_dist freight,
2560: ra_customer_trx_lines lines,
2561: ra_cust_trx_line_gl_dist rec,
2562: fnd_currencies fc,
2563: ra_customer_trx trx
2564: WHERE
2565: trx.customer_trx_id = p_trx_id
2587: 0, NVL(lines.previous_customer_trx_line_id,
2588: lines.customer_trx_line_id),
2589: lines.customer_trx_line_id)
2590: FROM
2591: ra_cust_trx_line_gl_dist subdist2
2592: WHERE
2593: subdist2.customer_trx_line_id = lines.customer_trx_line_id
2594: AND subdist2.account_set_flag = 'Y'
2595: AND subdist2.gl_date IS NULL
2603: AND NOT EXISTS
2604: (SELECT
2605: 'freight account exists'
2606: FROM
2607: ra_cust_trx_line_gl_dist subdist
2608: WHERE
2609: subdist.customer_trx_line_id = lines.customer_trx_line_id
2610: AND subdist.account_set_flag = 'N'
2611: AND subdist.gl_date IS NOT NULL
2728: program_id = arp_standard.profile.program_id
2729: where customer_trx_id = p_trx_id
2730: and autorule_complete_flag||'' = 'N'
2731: and (exists (select 'at least one distribution'
2732: from ra_cust_trx_line_gl_dist gl
2733: where gl.customer_trx_line_id = ul.customer_trx_line_id
2734: and gl.account_set_flag = 'N'
2735: and gl.request_id = arp_standard.profile.request_id)
2736: or exists (select 'a distribution for a linked line'
2734: and gl.account_set_flag = 'N'
2735: and gl.request_id = arp_standard.profile.request_id)
2736: or exists (select 'a distribution for a linked line'
2737: from ra_customer_trx_lines tl,
2738: ra_cust_trx_line_gl_dist tgl
2739: where tl.customer_trx_id = ul.customer_trx_id
2740: and tl.link_to_cust_trx_line_id = ul.customer_trx_line_id
2741: and tgl.customer_trx_line_id = tl.customer_trx_line_id
2742: and tgl.account_set_flag = 'N'
2755: program_id = arp_standard.profile.program_id
2756: where customer_trx_id = p_trx_id
2757: and autorule_complete_flag||'' = 'N'
2758: and (exists (select 'at least one distribution'
2759: from ra_cust_trx_line_gl_dist gl
2760: where gl.customer_trx_line_id = ul.customer_trx_line_id
2761: and gl.account_set_flag = 'N')
2762: or exists (select 'a distribution for a linked line'
2763: from ra_customer_trx_lines tl,
2760: where gl.customer_trx_line_id = ul.customer_trx_line_id
2761: and gl.account_set_flag = 'N')
2762: or exists (select 'a distribution for a linked line'
2763: from ra_customer_trx_lines tl,
2764: ra_cust_trx_line_gl_dist tgl
2765: where tl.customer_trx_id = ul.customer_trx_id
2766: and tl.link_to_cust_trx_line_id = ul.customer_trx_line_id
2767: and tgl.customer_trx_line_id = tl.customer_trx_line_id
2768: and tgl.account_set_flag = 'N'));