[Home] [Help]
50: /* bug 4284925 : introduce a variable string which will hold various
51: account_class values : REC, TAX, ROUND, FREIGHT which is used in multiple
52: selects to avoid re-parsing similar statements
53: */
54: acct_class RA_CUST_TRX_LINE_GL_DIST.ACCOUNT_CLASS%TYPE;
55:
56: /*-------------------------------------------------------------------------+
57: | PRIVATE PROCEDURE |
58: | assign_glp_index |
665: | PRIVATE FUNCTION |
666: | create_assignments |
667: | |
668: | DESCRIPTION |
669: | Create revenue assignments in ra_cust_trx_line_gl_dist from any |
670: | un-expanded model accounts within the specified date range |
671: | |
672: | PARAMETERS |
673: | p_trx_id IN NUMBER, |
693: | that uses ar_revenue_assignments view. |
694: | See bug 2143064 for details. |
695: | 13-Aug-02 Debbie Jancis Modified for MRC Trigger replacement |
696: | added calls for |
697: | ra_cust_trx_line_gl_dist processing |
698: | 31-JAN-03 M Raymond Modified MRC cursor to include UNEARN
699: | rows where rec_offset_flag is null.
700: | 02-MAY-03 M Raymond Modified REV insert to include an
701: | outer join to ra_cust_trx_line_salesreps
728: gl_date
729: INTO
730: rec_gl_date
731: FROM
732: ra_cust_trx_line_gl_dist
733: WHERE
734: account_class = 'REC'
735: AND account_set_flag = 'Y'
736: AND customer_trx_id = p_trx_id ;
778: IF PG_DEBUG in ('Y', 'C') THEN
779: arp_standard.debug('before insert....');
780: arp_standard.debug('g_rev_mgt_installed : ' ||g_rev_mgt_installed);
781: END IF;
782: INSERT INTO ra_cust_trx_line_gl_dist /* REV lines */
783: (
784: customer_trx_line_id,
785: customer_trx_id,
786: code_combination_id,
892: sysdate,
893: arp_standard.profile.user_id,
894: -3,
895: ass.gl_date, /* original_gl_date */
896: ra_cust_trx_line_gl_dist_s.NEXTVAL, /* cust_trx_line_gl_dist_id */
897: header.org_id
898: FROM
899: ra_customer_trx header,
900: fnd_currencies fc,
899: ra_customer_trx header,
900: fnd_currencies fc,
901: ra_customer_trx_lines lines,
902: ra_rules acc_rule,
903: ra_cust_trx_line_gl_dist rec,
904: ra_cust_trx_line_gl_dist dist,
905: ra_cust_trx_line_salesreps cmsrep,
906: ar_revenue_assignments ass
907: WHERE
900: fnd_currencies fc,
901: ra_customer_trx_lines lines,
902: ra_rules acc_rule,
903: ra_cust_trx_line_gl_dist rec,
904: ra_cust_trx_line_gl_dist dist,
905: ra_cust_trx_line_salesreps cmsrep,
906: ar_revenue_assignments ass
907: WHERE
908: header.customer_trx_id = p_trx_id
941: 0, NVL(lines.previous_customer_trx_line_id,
942: lines.customer_trx_line_id),
943: lines.customer_trx_line_id)
944: FROM
945: ra_cust_trx_line_gl_dist subdist2
946: WHERE
947: subdist2.customer_trx_line_id = lines.customer_trx_line_id
948: AND subdist2.account_set_flag = 'Y'
949: AND subdist2.gl_date IS NULL
960: (
961: SELECT
962: 'distribution exists'
963: FROM
964: ra_cust_trx_line_gl_dist subdist
965: WHERE
966: subdist.customer_trx_line_id = ass.customer_trx_line_id
967: AND subdist.customer_trx_id + 0 = lines.customer_trx_id
968: AND subdist.account_set_flag = 'N'
1040: cursor c_update_ps( l_trx_id NUMBER) is
1041: SELECT ps.payment_schedule_id ps_id,
1042: gld.gl_date gl_date
1043: FROM ar_payment_schedules ps,
1044: ra_cust_trx_line_gl_dist gld
1045: WHERE gld.customer_trx_id = l_trx_id
1046: AND gld.account_class = 'REC'
1047: AND gld.account_set_flag = 'N'
1048: AND gld.customer_trx_id = ps.customer_trx_id
1054: arp_standard.debug( 'arp_auto_rule.create_other_receivable()+ ' ||
1055: TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1056: END IF;
1057:
1058: INSERT INTO ra_cust_trx_line_gl_dist /* REC line */
1059: (
1060: customer_trx_id,
1061: code_combination_id,
1062: set_of_books_id,
1104: arp_standard.profile.program_id,
1105: sysdate,
1106: -3, /* posting_control_id */
1107: NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
1108: ra_cust_trx_line_gl_dist_s.NEXTVAL,
1109: header.org_id
1110: FROM
1111: ra_cust_trx_line_gl_dist rec,
1112: ra_customer_trx header
1107: NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
1108: ra_cust_trx_line_gl_dist_s.NEXTVAL,
1109: header.org_id
1110: FROM
1111: ra_cust_trx_line_gl_dist rec,
1112: ra_customer_trx header
1113: WHERE
1114: header.customer_trx_id = p_trx_id
1115: AND header.complete_flag = 'Y'
1122: (
1123: SELECT
1124: 'exist'
1125: FROM
1126: ra_cust_trx_line_gl_dist real_rec
1127: WHERE
1128: real_rec.customer_trx_id = rec.customer_trx_id
1129: AND real_rec.account_class = 'REC'
1130: AND real_rec.account_set_flag = 'N'
1148: /* no mrc columns affected so no update to mrc table needed */
1149:
1150: /* Bug 3416070 - Removed request_id from where clause */
1151:
1152: UPDATE ra_cust_trx_line_gl_dist
1153: SET
1154: latest_rec_flag = 'N',
1155: last_updated_by = arp_standard.profile.user_id,
1156: last_update_date = sysdate
1162: (
1163: SELECT
1164: customer_trx_id
1165: FROM
1166: ra_cust_trx_line_gl_dist
1167: WHERE
1168: customer_trx_id = p_trx_id
1169: AND account_class = 'REC'
1170: AND account_set_flag = 'N'
1256: TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
1257: END IF;
1258:
1259:
1260: INSERT INTO ra_cust_trx_line_gl_dist /* ROUND line */
1261: ( /* drive from gl_dist */
1262: customer_trx_id,
1263: code_combination_id,
1264: set_of_books_id,
1302: arp_standard.profile.program_id,
1303: sysdate,
1304: -3, /* posting_control_id */
1305: NVL(NVL(rec.original_gl_date, rec.gl_date), header.trx_date),
1306: ra_cust_trx_line_gl_dist_s.nextval,
1307: header.org_id
1308: FROM
1309: ra_customer_trx header,
1310: ra_cust_trx_line_gl_dist rec, /* ROUND row */
1306: ra_cust_trx_line_gl_dist_s.nextval,
1307: header.org_id
1308: FROM
1309: ra_customer_trx header,
1310: ra_cust_trx_line_gl_dist rec, /* ROUND row */
1311: ra_cust_trx_line_gl_dist rrec /* REC row */
1312: WHERE
1313: header.customer_trx_id = p_trx_id
1314: AND header.complete_flag = 'Y'
1307: header.org_id
1308: FROM
1309: ra_customer_trx header,
1310: ra_cust_trx_line_gl_dist rec, /* ROUND row */
1311: ra_cust_trx_line_gl_dist rrec /* REC row */
1312: WHERE
1313: header.customer_trx_id = p_trx_id
1314: AND header.complete_flag = 'Y'
1315: AND header.customer_trx_id = rec.customer_trx_id
1322: AND NOT EXISTS
1323: (
1324: SELECT 'exist'
1325: FROM
1326: ra_cust_trx_line_gl_dist real_rec
1327: WHERE
1328: real_rec.customer_trx_id = rec.customer_trx_id
1329: AND real_rec.account_class = 'ROUND'
1330: AND real_rec.account_set_flag = 'N'
1374: | 11-MAY-93 Charlie Tomberg Rewrote to perform the desired function|
1375: | 20-MAR-98 S.Jandyala Modified the function to create |
1376: | revenue account assignments by trx_id |
1377: | 10-MAY-02 M Raymond Added column rec_offset_flag to
1378: | ra_cust_trx_line_gl_dist_all. Added
1379: | logic to this insert to populate it
1380: | with a 'Y' if inserting UNEARN or
1381: | UNBILL lines.
1382: | See bug 2150541 for details.
1465: ( l_ctt_type = 'CM' AND l_revrec_run_flag = 'N')
1466: OR
1467: ( l_ctt_type = 'CM' AND g_use_inv_acctg = 'N') /* 5598773 */
1468: THEN
1469: INSERT INTO ra_cust_trx_line_gl_dist /* OTHER */
1470: (
1471: customer_trx_line_id,
1472: customer_trx_id,
1473: code_combination_id,
1584: arp_standard.profile.program_id,
1585: sysdate,
1586: -3, /* posting_control_id */
1587: NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
1588: ra_cust_trx_line_gl_dist_s.NEXTVAL,
1589: /* Bug 2150541 */
1590: DECODE(psum.account_class, 'UNEARN', 'Y',
1591: 'UNBILL', 'Y',
1592: NULL) ,
1593: trx.org_id
1594: FROM
1595: ra_customer_trx_lines psum_lines,
1596: ra_customer_trx psum_trx,
1597: ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
1598: ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
1599: ra_customer_trx_lines lines,
1600: ra_cust_trx_line_gl_dist rec, /* model receivable account */
1601: fnd_currencies fc,
1596: ra_customer_trx psum_trx,
1597: ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
1598: ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
1599: ra_customer_trx_lines lines,
1600: ra_cust_trx_line_gl_dist rec, /* model receivable account */
1601: fnd_currencies fc,
1602: ra_customer_trx trx
1603: WHERE
1604: trx.customer_trx_id = p_trx_id
1628: 0, NVL(lines.previous_customer_trx_line_id,
1629: lines.customer_trx_line_id),
1630: lines.customer_trx_line_id)
1631: FROM
1632: ra_cust_trx_line_gl_dist subdist2
1633: WHERE
1634: subdist2.customer_trx_line_id = lines.customer_trx_line_id
1635: AND subdist2.account_set_flag = 'Y'
1636: AND subdist2.gl_date IS NULL
1652: (
1653: SELECT
1654: 'plug sum account exists'
1655: FROM
1656: ra_cust_trx_line_gl_dist subdist
1657: WHERE
1658: subdist.account_class IN
1659: ( 'SUSPENSE', DECODE(trx.invoicing_rule_id,
1660: -2, 'UNEARN',
1668:
1669: /*3550426 */
1670: ELSE
1671:
1672: INSERT INTO ra_cust_trx_line_gl_dist /* OTHER */
1673: (
1674: customer_trx_line_id,
1675: customer_trx_id,
1676: code_combination_id,
1787: arp_standard.profile.program_id,
1788: sysdate,
1789: -3, /* posting_control_id */
1790: NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
1791: ra_cust_trx_line_gl_dist_s.NEXTVAL,
1792: /* Bug 2150541 */
1793: DECODE(psum.account_class, 'UNEARN', 'Y',
1794: 'UNBILL', 'Y',
1795: NULL),
1796: trx.org_id
1797: FROM
1798: ra_customer_trx_lines psum_lines,
1799: ra_customer_trx psum_trx,
1800: ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
1801: ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
1802: ra_customer_trx_lines lines,
1803: ra_cust_trx_line_gl_dist rec, /* model receivable account */
1804: fnd_currencies fc,
1799: ra_customer_trx psum_trx,
1800: ra_cust_trx_line_gl_dist psum, /* model plug account assignments */
1801: ra_cust_trx_line_salesreps cmsrep, /* 2899714 */
1802: ra_customer_trx_lines lines,
1803: ra_cust_trx_line_gl_dist rec, /* model receivable account */
1804: fnd_currencies fc,
1805: ra_customer_trx trx
1806: WHERE
1807: trx.customer_trx_id = p_trx_id
1840: (
1841: SELECT
1842: 'plug sum account exists'
1843: FROM
1844: ra_cust_trx_line_gl_dist subdist
1845: WHERE
1846: subdist.account_class IN
1847: ( 'SUSPENSE', DECODE(trx.invoicing_rule_id,
1848: -2, 'UNEARN',
1964: ELSE
1965: l_ignore_rule_flag := 'N';
1966: END IF;
1967:
1968: INSERT INTO ra_cust_trx_line_gl_dist /* TAX Lines */
1969: (
1970: customer_trx_line_id,
1971: customer_trx_id,
1972: code_combination_id,
2045: arp_standard.profile.program_id,
2046: sysdate,
2047: -3,
2048: NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
2049: ra_cust_trx_line_gl_dist_s.NEXTVAL,
2050: tax.collected_tax_ccid,
2051: trx.org_id
2052: FROM
2053: ra_customer_trx model_trx,
2050: tax.collected_tax_ccid,
2051: trx.org_id
2052: FROM
2053: ra_customer_trx model_trx,
2054: ra_cust_trx_line_gl_dist tax,
2055: ra_customer_trx_lines tax_line,
2056: ra_customer_trx_lines line_line,
2057: ra_cust_trx_line_gl_dist rec,
2058: fnd_currencies fc,
2053: ra_customer_trx model_trx,
2054: ra_cust_trx_line_gl_dist tax,
2055: ra_customer_trx_lines tax_line,
2056: ra_customer_trx_lines line_line,
2057: ra_cust_trx_line_gl_dist rec,
2058: fnd_currencies fc,
2059: ra_customer_trx trx
2060: WHERE
2061: trx.customer_trx_id = p_trx_id
2079: 0, NVL(tax_line.previous_customer_trx_line_id,
2080: tax_line.customer_trx_line_id),
2081: tax_line.customer_trx_line_id)
2082: FROM
2083: ra_cust_trx_line_gl_dist subdist2
2084: WHERE
2085: subdist2.customer_trx_line_id=tax_line.customer_trx_line_id
2086: AND subdist2.account_set_flag = 'Y'
2087: AND subdist2.gl_date IS NULL
2094: AND NOT EXISTS
2095: (SELECT
2096: 'tax account exists'
2097: FROM
2098: ra_cust_trx_line_gl_dist subdist
2099: WHERE
2100: tax_line.customer_trx_line_id = subdist.customer_trx_line_id
2101: AND subdist.account_set_flag = 'N'
2102: AND subdist.gl_date IS NOT NULL
2167: arp_standard.debug( 'arp_auto_rule.create_other_freight()+ ' ||
2168: TO_CHAR(sysdate, 'DD-MON-YY HH:MI:SS'));
2169: END IF;
2170:
2171: INSERT INTO ra_cust_trx_line_gl_dist /* FREIGHT Lines */
2172: (
2173: customer_trx_line_id,
2174: customer_trx_id,
2175: code_combination_id,
2244: arp_standard.profile.program_id,
2245: sysdate,
2246: -3,
2247: NVL( NVL(rec.original_gl_date, rec.gl_date), trx.trx_date),
2248: ra_cust_trx_line_gl_dist_s.NEXTVAL,
2249: trx.org_id
2250: FROM
2251: ra_customer_trx model_trx,
2252: ra_cust_trx_line_gl_dist freight,
2248: ra_cust_trx_line_gl_dist_s.NEXTVAL,
2249: trx.org_id
2250: FROM
2251: ra_customer_trx model_trx,
2252: ra_cust_trx_line_gl_dist freight,
2253: ra_customer_trx_lines lines,
2254: ra_cust_trx_line_gl_dist rec,
2255: fnd_currencies fc,
2256: ra_customer_trx trx
2250: FROM
2251: ra_customer_trx model_trx,
2252: ra_cust_trx_line_gl_dist freight,
2253: ra_customer_trx_lines lines,
2254: ra_cust_trx_line_gl_dist rec,
2255: fnd_currencies fc,
2256: ra_customer_trx trx
2257: WHERE
2258: trx.customer_trx_id = p_trx_id
2280: 0, NVL(lines.previous_customer_trx_line_id,
2281: lines.customer_trx_line_id),
2282: lines.customer_trx_line_id)
2283: FROM
2284: ra_cust_trx_line_gl_dist subdist2
2285: WHERE
2286: subdist2.customer_trx_line_id = lines.customer_trx_line_id
2287: AND subdist2.account_set_flag = 'Y'
2288: AND subdist2.gl_date IS NULL
2296: AND NOT EXISTS
2297: (SELECT
2298: 'freight account exists'
2299: FROM
2300: ra_cust_trx_line_gl_dist subdist
2301: WHERE
2302: subdist.customer_trx_line_id = lines.customer_trx_line_id
2303: AND subdist.account_set_flag = 'N'
2304: AND subdist.gl_date IS NOT NULL
2421: program_id = arp_standard.profile.program_id
2422: where customer_trx_id = p_trx_id
2423: and autorule_complete_flag||'' = 'N'
2424: and (exists (select 'at least one distribution'
2425: from ra_cust_trx_line_gl_dist gl
2426: where gl.customer_trx_line_id = ul.customer_trx_line_id
2427: and gl.account_set_flag = 'N'
2428: and gl.request_id = arp_standard.profile.request_id)
2429: or exists (select 'a distribution for a linked line'
2427: and gl.account_set_flag = 'N'
2428: and gl.request_id = arp_standard.profile.request_id)
2429: or exists (select 'a distribution for a linked line'
2430: from ra_customer_trx_lines tl,
2431: ra_cust_trx_line_gl_dist tgl
2432: where tl.customer_trx_id = ul.customer_trx_id
2433: and tl.link_to_cust_trx_line_id = ul.customer_trx_line_id
2434: and tgl.customer_trx_line_id = tl.customer_trx_line_id
2435: and tgl.account_set_flag = 'N'
2448: program_id = arp_standard.profile.program_id
2449: where customer_trx_id = p_trx_id
2450: and autorule_complete_flag||'' = 'N'
2451: and (exists (select 'at least one distribution'
2452: from ra_cust_trx_line_gl_dist gl
2453: where gl.customer_trx_line_id = ul.customer_trx_line_id
2454: and gl.account_set_flag = 'N')
2455: or exists (select 'a distribution for a linked line'
2456: from ra_customer_trx_lines tl,
2453: where gl.customer_trx_line_id = ul.customer_trx_line_id
2454: and gl.account_set_flag = 'N')
2455: or exists (select 'a distribution for a linked line'
2456: from ra_customer_trx_lines tl,
2457: ra_cust_trx_line_gl_dist tgl
2458: where tl.customer_trx_id = ul.customer_trx_id
2459: and tl.link_to_cust_trx_line_id = ul.customer_trx_line_id
2460: and tgl.customer_trx_line_id = tl.customer_trx_line_id
2461: and tgl.account_set_flag = 'N'));