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.FUN_BAL_PKG dependencies on FUN_BAL_LE_BSV_MAP_GT

Line 69: DELETE FROM fun_bal_le_bsv_map_gt;

65: END IF;
66:
67: DELETE FROM fun_bal_results_gt;
68: DELETE FROM fun_bal_errors_gt;
69: DELETE FROM fun_bal_le_bsv_map_gt;
70: DELETE FROM fun_bal_inter_int_gt;
71: DELETE FROM fun_bal_intra_int_gt;
72: /* Using delete rather than truncate as shown in the code below. The reason is that truncate (or any DDL operations)
73: perform an implicit commit => need to use autonomous transaction to perform such operation. However, we would

Line 80: dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_LE_BSV_MAP_GT', dbms_sql.native);

76: which could result in that we think the rows got deleted but they still exist.
77: cur_hdl := dbms_sql.open_cursor;
78: dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_RESULTS_GT', dbms_sql.native);
79: dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_ERRORS_GT', dbms_sql.native);
80: dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_LE_BSV_MAP_GT', dbms_sql.native);
81: dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_INT_GT', dbms_sql.native);
82: dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_INT_GT', dbms_sql.native);
83: dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_RESULTS_T', dbms_sql.native);
84: dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_ERRORS_T', dbms_sql.native);

Line 736: SELECT * FROM fun_bal_le_bsv_map_gt;

732: FUNCTION do_inter_bal RETURN VARCHAR2 IS
733: l_le_bsv_map_tab inter_le_bsv_map_tab_type;
734: l_inter_int_tab inter_int_tab_type;
735: CURSOR l_le_bsv_map_cursor IS
736: SELECT * FROM fun_bal_le_bsv_map_gt;
737: CURSOR l_inter_int_cursor IS
738: SELECT * FROM fun_bal_inter_int_gt;
739: l_le_bsv_map_count NUMBER;
740: l_inter_int_count NUMBER;

Line 748: INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date)

744: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.begin', 'begin');
745: END IF;
746:
747: /* Replaced by sql below from performance review
748: INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date)
749: SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date
750: FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger,
751: gl_ledger_configurations config
752: WHERE hdrs.group_id = lines.group_id

Line 758: -- Update legal entity column in FUN_BAL_LE_BSV_MAP_GT

754: AND ledger.configuration_id = config.configuration_id
755: AND ledger.bal_seg_value_option_code = 'I';
756: -- Only possible scenario to perform Intercompany is when bal_seg_value_option_code is 'I' and acct_env_code is 'SHARED'
757:
758: -- Update legal entity column in FUN_BAL_LE_BSV_MAP_GT
759:
760: -- Legal entity can only be either null or has a specific value
761: UPDATE fun_bal_le_bsv_map_gt bsv_le_map
762: SET le_id =

Line 761: UPDATE fun_bal_le_bsv_map_gt bsv_le_map

757:
758: -- Update legal entity column in FUN_BAL_LE_BSV_MAP_GT
759:
760: -- Legal entity can only be either null or has a specific value
761: UPDATE fun_bal_le_bsv_map_gt bsv_le_map
762: SET le_id =
763: NVL((SELECT vals.legal_entity_id
764: FROM gl_ledger_le_bsv_specific_v vals
765: WHERE bsv_le_map.bal_seg_val = vals.segment_value

Line 798: -- FUN_BAL_LE_BSV_MAP_GT. We are only inserting the journals with ledgers in shared

794: AND error_code IN ('FUN_BSV_INVALID'))
795: AND headers.status = 'OK';
796:
797: -- Select the distinct combination of GROUP_ID, LEDGER_ID and BAL_SEG_VAL into
798: -- FUN_BAL_LE_BSV_MAP_GT. We are only inserting the journals with ledgers in shared
799: -- mode configuration, as intercompany balancing should only be performed in shared mode.
800: -- Doing so should decrease the amount of processing time required at a later stage.
801:
802: -- Only possible scenario to perform Intercompany is when bal_seg_value_option_code is 'I' and acct_env_code is 'SHARED'

Line 803: -- Update legal entity column in FUN_BAL_LE_BSV_MAP_GT

799: -- mode configuration, as intercompany balancing should only be performed in shared mode.
800: -- Doing so should decrease the amount of processing time required at a later stage.
801:
802: -- Only possible scenario to perform Intercompany is when bal_seg_value_option_code is 'I' and acct_env_code is 'SHARED'
803: -- Update legal entity column in FUN_BAL_LE_BSV_MAP_GT
804: -- Legal entity can only be either null or has a specific value
805:
806: --ER: 8588074
807: --Bug: 9183927

Line 809: INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date, le_id, je_source_name, je_category_name)

805:
806: --ER: 8588074
807: --Bug: 9183927
808:
809: INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date, le_id, je_source_name, je_category_name)
810: SELECT main.group_id, main.ledger_id, main.bal_seg_val, main.gl_date, NVL(vals.legal_entity_id, -99),
811: main.je_source_name, main.je_category_name
812: FROM (SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date, hdrs.je_source_name, hdrs.je_category_name
813: FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger,

Line 831: FROM fun_bal_le_bsv_map_gt le_bsv_map

827: --Bug: 12834036.
828: UPDATE fun_bal_headers_gt headers
829: SET (le_id, le_count) =
830: (SELECT MIN(le_bsv_map.le_id), SUM(COUNT(DISTINCT le_bsv_map.le_id))
831: FROM fun_bal_le_bsv_map_gt le_bsv_map
832: WHERE headers.group_id = le_bsv_map.group_id
833: AND le_bsv_map.le_id <> -99
834: GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
835:

Line 841: FROM fun_bal_le_bsv_map_gt le_bsv_map

837: SET status = DECODE(le_id, NULL, status, 'ERROR'),
838: error_code = DECODE(le_id, NULL, error_code, 'FUN_INTER_BSV_NOT_ASSIGNED'),
839: unmapped_bsv_le_id = -99
840: WHERE EXISTS (SELECT 'Unmapped BSV exists'
841: FROM fun_bal_le_bsv_map_gt le_bsv_map
842: WHERE le_bsv_map.group_id = headers.group_id
843: AND le_bsv_map.le_id = -99);
844:
845:

Line 902: FROM fun_bal_headers_gt hdrs, fun_bal_le_bsv_map_gt le_bsv_map

898: WHERE hdrs.error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR';
899:
900: INSERT INTO fun_bal_errors_gt(error_code, group_id, bal_seg_val)
901: SELECT 'FUN_INTER_BSV_NOT_ASSIGNED', hdrs.group_id, le_bsv_map.bal_seg_val
902: FROM fun_bal_headers_gt hdrs, fun_bal_le_bsv_map_gt le_bsv_map
903: WHERE hdrs.group_id = le_bsv_map.group_id
904: AND hdrs.error_code = 'FUN_INTER_BSV_NOT_ASSIGNED'
905: AND le_bsv_map.le_id = -99;
906:

Line 907: --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LE_BSV_MAP_GT');

903: WHERE hdrs.group_id = le_bsv_map.group_id
904: AND hdrs.error_code = 'FUN_INTER_BSV_NOT_ASSIGNED'
905: AND le_bsv_map.le_id = -99;
906:
907: --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LE_BSV_MAP_GT');
908:
909: IF g_debug = FND_API.G_TRUE THEN
910: OPEN l_le_bsv_map_cursor;
911: FETCH l_le_bsv_map_cursor BULK COLLECT INTO l_le_bsv_map_tab;

Line 925: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map

921:
922: END IF;
923:
924:
925: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
926: WHERE group_id = (SELECT group_id
927: FROM fun_bal_headers_gt headers
928: WHERE headers.status = 'ERROR'
929: AND le_bsv_map.group_id = headers.group_id);

Line 931: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del

927: FROM fun_bal_headers_gt headers
928: WHERE headers.status = 'ERROR'
929: AND le_bsv_map.group_id = headers.group_id);
930:
931: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
932: WHERE EXISTS (SELECT 'LE already balanced'
933: FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
934: WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
935: AND le_bsv_map_del.le_id = le_bsv_map.le_id

Line 933: FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map

929: AND le_bsv_map.group_id = headers.group_id);
930:
931: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
932: WHERE EXISTS (SELECT 'LE already balanced'
933: FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
934: WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
935: AND le_bsv_map_del.le_id = le_bsv_map.le_id
936: AND le_bsv_map.group_id = lines.group_id
937: AND le_bsv_map.bal_seg_val = lines.bal_seg_val

Line 951: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines

947: UPDATE fun_bal_headers_gt hdrs
948: SET (driving_dr_le_id, intercompany_mode) =
949: (SELECT MIN(le_bsv_map.le_id),
950: SUM(COUNT(DISTINCT(le_bsv_map.le_id)))
951: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
952: WHERE hdrs.group_id = le_bsv_map.group_id
953: AND le_bsv_map.group_id = lines.group_id
954: AND le_bsv_map.bal_seg_val = lines.bal_seg_val
955: GROUP BY le_bsv_map.group_id, le_bsv_map.le_id

Line 969: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map

965:
966: -- Deleting the records that do not require intercompany balancing.
967: -- Deleting these records first should make the code perform better,
968: -- as there won't be any more join to these lines.
969: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
970: WHERE EXISTS
971: (SELECT 'Intercompany balancing is not required'
972: FROM fun_bal_headers_gt hdrs
973: WHERE le_bsv_map.group_id = hdrs.group_id

Line 984: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines

980: (SELECT MIN(le_bsv_map.le_id),
981: DECODE(SUM(COUNT(DISTINCT(le_bsv_map.le_id))), 1,
982: DECODE(hdrs.intercompany_mode, 1, 1, 3),
983: DECODE(hdrs.intercompany_mode, 1, 2, 4))
984: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
985: WHERE hdrs.group_id = le_bsv_map.group_id
986: AND le_bsv_map.group_id = lines.group_id
987: AND le_bsv_map.bal_seg_val = lines.bal_seg_val
988: GROUP BY le_bsv_map.group_id, le_bsv_map.le_id

Line 998: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map

994: SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) >
995: SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))))
996: WHERE status = 'OK';
997:
998: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
999: WHERE EXISTS (SELECT 'No Driving DR LE or Driving CR LE'
1000: FROM fun_bal_headers_gt headers
1001: WHERE headers.group_id = le_bsv_map.group_id
1002: AND headers.status = 'OK'

Line 1080: FROM FUN_BAL_LE_BSV_MAP_GT LE_BSV_MAP,

1076: 1, 'C',
1077: -1, 'D',
1078: 0, DECODE(SIGN((( SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0)) )) -
1079: ( SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) )), 1, 'C', 'D')) TYPE
1080: FROM FUN_BAL_LE_BSV_MAP_GT LE_BSV_MAP,
1081: FUN_BAL_LINES_GT LINES,
1082: FUN_BAL_HEADERS_GT HDRS
1083: WHERE HDRS.GROUP_ID = LINES.GROUP_ID
1084: AND LINES.GROUP_ID = LE_BSV_MAP.GROUP_ID

Line 1388: --Deleting those BSVs from the fun_bal_le_bsv_map_gt which do not need intercompany balancing #9392684

1384: AND NVL(accounted_amt_cr,0)=0
1385: AND NVL(accounted_amt_dr,0)=0
1386: );
1387:
1388: --Deleting those BSVs from the fun_bal_le_bsv_map_gt which do not need intercompany balancing #9392684
1389: DELETE from fun_bal_le_bsv_map_gt le_bsv_map
1390: WHERE bal_seg_val NOT IN (Select line_le_bsv from fun_bal_inter_int2_gt
1391: WHERE group_id= le_bsv_map.group_id
1392: AND le_id= le_bsv_map.le_id

Line 1389: DELETE from fun_bal_le_bsv_map_gt le_bsv_map

1385: AND NVL(accounted_amt_dr,0)=0
1386: );
1387:
1388: --Deleting those BSVs from the fun_bal_le_bsv_map_gt which do not need intercompany balancing #9392684
1389: DELETE from fun_bal_le_bsv_map_gt le_bsv_map
1390: WHERE bal_seg_val NOT IN (Select line_le_bsv from fun_bal_inter_int2_gt
1391: WHERE group_id= le_bsv_map.group_id
1392: AND le_id= le_bsv_map.le_id
1393: ) ;

Line 1400: FROM fun_bal_le_bsv_map_gt le_bsv_map

1396: --updating the driving_dr_le_id, driving_dr_le_bsv and pay_bsv for the 1:M and M:1 case #9392684
1397: UPDATE fun_bal_inter_int2_gt upd
1398: SET (driving_dr_le_id, driving_dr_le_bsv,pay_bsv) =
1399: (SELECT DISTINCT le_bsv_map.le_id,le_bsv_map.bal_seg_val,le_bsv_map.bal_seg_val
1400: FROM fun_bal_le_bsv_map_gt le_bsv_map
1401: WHERE le_bsv_map.group_id= upd.group_id
1402: AND upd.line_le_bsv= le_bsv_map.bal_seg_val
1403: )
1404: WHERE intercompany_mode IN (1,2,3)

Line 1414: FROM fun_bal_le_bsv_map_gt le_bsv_map

1410: --updating the driving_cr_le_id, driving_cr_le_bsv and rec_bsv for the 1:M and M:1 case #9392684
1411: UPDATE fun_bal_inter_int2_gt upd
1412: SET (driving_cr_le_id, driving_cr_le_bsv,rec_bsv) =
1413: (SELECT DISTINCT le_bsv_map.le_id,le_bsv_map.bal_seg_val,le_bsv_map.bal_seg_val
1414: FROM fun_bal_le_bsv_map_gt le_bsv_map
1415: WHERE le_bsv_map.group_id= upd.group_id
1416: AND upd.line_le_bsv= le_bsv_map.bal_seg_val
1417: )
1418: WHERE intercompany_mode IN (1,2,3)

Line 1431: from fun_bal_le_bsv_map_gt

1427: UPDATE fun_bal_inter_int2_gt upd1
1428: SET driving_dr_le_bsv= DECODE(upd1.driving_dr_le_id,-1,'Many',
1429: le_id, line_le_bsv,
1430: (select bal_seg_val
1431: from fun_bal_le_bsv_map_gt
1432: where group_id=upd1.group_id
1433: and le_id= upd1.driving_dr_le_id)),
1434: driving_cr_le_bsv= DECODE(upd1.driving_cr_le_id,-1,'Many',
1435: le_id, line_le_bsv,

Line 1437: from fun_bal_le_bsv_map_gt

1433: and le_id= upd1.driving_dr_le_id)),
1434: driving_cr_le_bsv= DECODE(upd1.driving_cr_le_id,-1,'Many',
1435: le_id, line_le_bsv,
1436: (select bal_seg_val
1437: from fun_bal_le_bsv_map_gt
1438: where group_id=upd1.group_id
1439: and le_id= upd1.driving_cr_le_id))
1440: Where intercompany_mode in (1,2,3)
1441: AND ( driving_dr_le_bsv <>'Many'

Line 1546: SET driving_dr_le_id = decode(driving_dr_le_bsv, 'Many', -1, (select distinct le_id from FUN_BAL_LE_BSV_MAP_GT le_bsv

1542: OR driving_cr_le_bsv <>'Many';
1543:
1544: --Bug: 12354478
1545: UPDATE fun_bal_inter_int2_gt t1
1546: SET driving_dr_le_id = decode(driving_dr_le_bsv, 'Many', -1, (select distinct le_id from FUN_BAL_LE_BSV_MAP_GT le_bsv
1547: where le_bsv.group_id = t1.group_id
1548: and le_bsv.bal_seg_val = t1.driving_dr_le_bsv)),
1549: driving_cr_le_id = decode(driving_cr_le_bsv, 'Many', -1, (select distinct le_id from FUN_BAL_LE_BSV_MAP_GT le_bsv1
1550: where le_bsv1.group_id = t1.group_id

Line 1549: driving_cr_le_id = decode(driving_cr_le_bsv, 'Many', -1, (select distinct le_id from FUN_BAL_LE_BSV_MAP_GT le_bsv1

1545: UPDATE fun_bal_inter_int2_gt t1
1546: SET driving_dr_le_id = decode(driving_dr_le_bsv, 'Many', -1, (select distinct le_id from FUN_BAL_LE_BSV_MAP_GT le_bsv
1547: where le_bsv.group_id = t1.group_id
1548: and le_bsv.bal_seg_val = t1.driving_dr_le_bsv)),
1549: driving_cr_le_id = decode(driving_cr_le_bsv, 'Many', -1, (select distinct le_id from FUN_BAL_LE_BSV_MAP_GT le_bsv1
1550: where le_bsv1.group_id = t1.group_id
1551: and le_bsv1.bal_seg_val = t1.driving_cr_le_bsv))
1552: WHERE intercompany_mode IN (1,2,3)
1553: AND driving_dr_le_bsv <>'Many'

Line 1648: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,

1644:
1645: Update fun_bal_inter_int_gt bal_inter_int
1646: set REC_BSV = (select min_bal_seg_val from (
1647: select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
1648: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
1649: fun_bal_headers_gt hdrs
1650: WHERE hdrs.group_id = lines.group_id
1651: AND lines.group_id = le_bsv_map.group_id
1652: AND lines.bal_seg_val = le_bsv_map.bal_seg_val

Line 1674: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,

1670:
1671: Update fun_bal_inter_int_gt bal_inter_int
1672: set PAY_BSV = (select min_bal_seg_val from (
1673: select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
1674: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
1675: fun_bal_headers_gt hdrs
1676: WHERE hdrs.group_id = lines.group_id
1677: AND lines.group_id = le_bsv_map.group_id
1678: AND lines.bal_seg_val = le_bsv_map.bal_seg_val

Line 2014: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map

2010: 2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
2011: 3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
2012: 4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
2013: NULL)
2014: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2015: where le_bsv_map.group_id = inter_int.group_id)
2016: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2017: where le_bsv_map.group_id = inter_int.group_id)
2018: AND opts.status_flag = 'Y'),

Line 2016: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map

2012: 4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
2013: NULL)
2014: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2015: where le_bsv_map.group_id = inter_int.group_id)
2016: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2017: where le_bsv_map.group_id = inter_int.group_id)
2018: AND opts.status_flag = 'Y'),
2019: NVL((SELECT opts.template_id
2020: FROM fun_balance_options opts

Line 2030: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map

2026: 2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
2027: 3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
2028: 4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
2029: NULL)
2030: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2031: where le_bsv_map.group_id = inter_int.group_id)
2032: AND opts.je_category_name = 'Other'
2033: AND opts.status_flag = 'Y'),
2034: NVL((SELECT opts.template_id

Line 2046: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map

2042: 3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
2043: 4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
2044: NULL)
2045: AND opts.je_source_name = 'Other'
2046: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2047: where le_bsv_map.group_id = inter_int.group_id)
2048: AND opts.status_flag = 'Y'),
2049: (SELECT opts.template_id
2050: FROM fun_balance_options opts

Line 2285: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map

2281: 2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
2282: 3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
2283: 4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
2284: NULL)
2285: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2286: where le_bsv_map.group_id = inter_int.group_id)
2287: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2288: where le_bsv_map.group_id = inter_int.group_id)
2289: AND opts.status_flag = 'Y'),

Line 2287: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map

2283: 4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
2284: NULL)
2285: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2286: where le_bsv_map.group_id = inter_int.group_id)
2287: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2288: where le_bsv_map.group_id = inter_int.group_id)
2289: AND opts.status_flag = 'Y'),
2290: NVL((SELECT opts.template_id
2291: FROM fun_balance_options opts

Line 2301: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map

2297: 2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
2298: 3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
2299: 4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
2300: NULL)
2301: AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2302: where le_bsv_map.group_id = inter_int.group_id)
2303: AND opts.je_category_name = 'Other'
2304: AND opts.status_flag = 'Y'),
2305: NVL((SELECT opts.template_id

Line 2317: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map

2313: 3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
2314: 4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
2315: NULL)
2316: AND opts.je_source_name = 'Other'
2317: AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2318: where le_bsv_map.group_id = inter_int.group_id)
2319: AND opts.status_flag = 'Y'),
2320: (SELECT opts.template_id
2321: FROM fun_balance_options opts

Line 2529: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,

2525: --bug: 9008776
2526: Update fun_bal_inter_int_gt bal_inter_int
2527: set Rec_BSV = (select min_bal_seg_val from (
2528: select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
2529: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
2530: fun_bal_headers_gt hdrs
2531: WHERE hdrs.group_id = lines.group_id
2532: AND lines.group_id = le_bsv_map.group_id
2533: AND lines.bal_seg_val = le_bsv_map.bal_seg_val

Line 2553: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,

2549:
2550: Update fun_bal_inter_int_gt bal_inter_int
2551: set Pay_BSV = (select min_bal_seg_val from (
2552: select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
2553: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
2554: fun_bal_headers_gt hdrs
2555: WHERE hdrs.group_id = lines.group_id
2556: AND lines.group_id = le_bsv_map.group_id
2557: AND lines.bal_seg_val = le_bsv_map.bal_seg_val

Line 2909: SELECT * FROM fun_bal_le_bsv_map_gt;

2905: l_intra_int_tab intra_int_tab_type;
2906: l_le_bsv_map_count NUMBER;
2907: l_intra_int_count NUMBER;
2908: CURSOR l_le_bsv_map_cursor IS
2909: SELECT * FROM fun_bal_le_bsv_map_gt;
2910: CURSOR l_intra_int_cursor IS
2911: SELECT * FROM fun_bal_intra_int_gt;
2912: BEGIN
2913: IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN

Line 2918: -- Delete all records from FUN_BAL_LE_BSV_MAP_GT for reuse.

2914: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.begin', 'begin');
2915: END IF;
2916:
2917:
2918: -- Delete all records from FUN_BAL_LE_BSV_MAP_GT for reuse.
2919: DELETE FROM fun_bal_le_bsv_map_gt;
2920:
2921: -- Insert records into FUN_BAL_LE_BSV_MAP_GT
2922: INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date,

Line 2919: DELETE FROM fun_bal_le_bsv_map_gt;

2915: END IF;
2916:
2917:
2918: -- Delete all records from FUN_BAL_LE_BSV_MAP_GT for reuse.
2919: DELETE FROM fun_bal_le_bsv_map_gt;
2920:
2921: -- Insert records into FUN_BAL_LE_BSV_MAP_GT
2922: INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date,
2923: je_source_name, je_category_name, clearing_bsv,

Line 2921: -- Insert records into FUN_BAL_LE_BSV_MAP_GT

2917:
2918: -- Delete all records from FUN_BAL_LE_BSV_MAP_GT for reuse.
2919: DELETE FROM fun_bal_le_bsv_map_gt;
2920:
2921: -- Insert records into FUN_BAL_LE_BSV_MAP_GT
2922: INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date,
2923: je_source_name, je_category_name, clearing_bsv,
2924: chart_of_accounts_id, bal_seg_column_number,intercompany_column_number)
2925: SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date,

Line 2922: INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date,

2918: -- Delete all records from FUN_BAL_LE_BSV_MAP_GT for reuse.
2919: DELETE FROM fun_bal_le_bsv_map_gt;
2920:
2921: -- Insert records into FUN_BAL_LE_BSV_MAP_GT
2922: INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date,
2923: je_source_name, je_category_name, clearing_bsv,
2924: chart_of_accounts_id, bal_seg_column_number,intercompany_column_number)
2925: SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date,
2926: hdrs.je_source_name, hdrs.je_category_name, hdrs.clearing_bsv,

Line 2939: UPDATE fun_bal_le_bsv_map_gt bsv_le_map

2935: END IF;
2936:
2937:
2938: -- Update Legal entity for each ledger, BSV combination. Legal entity can only be either null or has a specific value
2939: UPDATE fun_bal_le_bsv_map_gt bsv_le_map
2940: SET le_id =
2941: NVL((SELECT vals.legal_entity_id
2942: FROM gl_ledger_le_bsv_specific_v vals
2943: WHERE bsv_le_map.bal_seg_val = vals.segment_value

Line 2956: UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd

2952: END IF;
2953:
2954: -- Determine intracompany mode, driving_dr_bsv and driving_cr_bsv
2955: -- improve performance for not updating the lines of LE that uses clearing company
2956: UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
2957: SET (driving_dr_bsv, intracompany_mode) =
2958: (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
2959: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
2960: MIN(le_bsv_map.bal_seg_val), SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val)))

Line 2959: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */

2955: -- improve performance for not updating the lines of LE that uses clearing company
2956: UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
2957: SET (driving_dr_bsv, intracompany_mode) =
2958: (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
2959: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
2960: MIN(le_bsv_map.bal_seg_val), SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val)))
2961: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
2962: WHERE le_bsv_map.group_id = lines.group_id
2963: AND le_bsv_map.bal_seg_val = lines.bal_seg_val

Line 2961: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines

2957: SET (driving_dr_bsv, intracompany_mode) =
2958: (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
2959: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
2960: MIN(le_bsv_map.bal_seg_val), SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val)))
2961: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
2962: WHERE le_bsv_map.group_id = lines.group_id
2963: AND le_bsv_map.bal_seg_val = lines.bal_seg_val
2964: AND le_bsv_map.group_id = le_bsv_map_upd.group_id
2965: AND le_bsv_map.le_id = le_bsv_map_upd.le_id

Line 2984: DELETE FROM fun_bal_le_bsv_map_gt

2980: END IF;
2981:
2982:
2983: --Delete records that has intracompany mode NULL
2984: DELETE FROM fun_bal_le_bsv_map_gt
2985: WHERE intracompany_mode IS NULL;
2986:
2987: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
2988: WHERE EXISTS (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)

Line 2987: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del

2983: --Delete records that has intracompany mode NULL
2984: DELETE FROM fun_bal_le_bsv_map_gt
2985: WHERE intracompany_mode IS NULL;
2986:
2987: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
2988: WHERE EXISTS (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
2989: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
2990: 'BSV already balanced'
2991: FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map

Line 2989: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */

2985: WHERE intracompany_mode IS NULL;
2986:
2987: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
2988: WHERE EXISTS (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
2989: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
2990: 'BSV already balanced'
2991: FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
2992: WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
2993: AND le_bsv_map_del.le_id = le_bsv_map.le_id

Line 2991: FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map

2987: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
2988: WHERE EXISTS (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
2989: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
2990: 'BSV already balanced'
2991: FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
2992: WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
2993: AND le_bsv_map_del.le_id = le_bsv_map.le_id
2994: AND le_bsv_map_del.bal_seg_val = le_bsv_map.bal_seg_val
2995: AND le_bsv_map.group_id = lines.group_id

Line 3005: UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd

3001: AND
3002: (SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) =
3003: SUM(DECODE(lines.exchange_rate, NULL,NVL(lines.entered_amt_cr,0),0))));
3004:
3005: UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
3006: SET (driving_cr_bsv, intracompany_mode) =
3007: (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
3008: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
3009: MIN(le_bsv_map.bal_seg_val), DECODE(SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val))),

Line 3008: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */

3004:
3005: UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
3006: SET (driving_cr_bsv, intracompany_mode) =
3007: (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
3008: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
3009: MIN(le_bsv_map.bal_seg_val), DECODE(SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val))),
3010: 1, DECODE(le_bsv_map_upd.intracompany_mode, 1, 1, 3),
3011: DECODE(le_bsv_map_upd.intracompany_mode, 1, 2, 4))
3012: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines

Line 3012: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines

3008: index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
3009: MIN(le_bsv_map.bal_seg_val), DECODE(SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val))),
3010: 1, DECODE(le_bsv_map_upd.intracompany_mode, 1, 1, 3),
3011: DECODE(le_bsv_map_upd.intracompany_mode, 1, 2, 4))
3012: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
3013: WHERE le_bsv_map.group_id = lines.group_id
3014: AND le_bsv_map.bal_seg_val = lines.bal_seg_val
3015: AND le_bsv_map.group_id = le_bsv_map_upd.group_id
3016: AND le_bsv_map.le_id = le_bsv_map_upd.le_id

Line 3032: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map

3028: WHERE le_bsv_map_upd.intracompany_mode IS NOT NULL;
3029: -- AND le_bsv_map_upd.intracompany_mode <> 5;
3030:
3031: -- Don't balance for journals that does not have a credit or debit side
3032: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
3033: WHERE le_bsv_map.driving_dr_bsv IS NULL OR le_bsv_map.driving_cr_bsv IS NULL;
3034:
3035:
3036: IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN

Line 3042: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3038: END IF;
3039:
3040:
3041: -- Update intra_template_id in FUN_BAL_LE_BSV_MAP
3042: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3043: SET template_id =
3044: (SELECT opts.template_id
3045: FROM fun_balance_options opts
3046: WHERE le_bsv_map.ledger_id = opts.ledger_id

Line 3053: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3049: AND le_bsv_map.je_category_name = opts.je_category_name
3050: AND opts.status_flag = 'Y')
3051: WHERE le_bsv_map.template_id IS NULL;
3052:
3053: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3054: SET template_id =
3055: (SELECT opts.template_id
3056: FROM fun_balance_options opts
3057: WHERE le_bsv_map.ledger_id = opts.ledger_id

Line 3064: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3060: AND opts.je_category_name = 'Other'
3061: AND opts.status_flag = 'Y')
3062: WHERE le_bsv_map.template_id IS NULL;
3063:
3064: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3065: SET template_id =
3066: (SELECT opts.template_id
3067: FROM fun_balance_options opts
3068: WHERE le_bsv_map.ledger_id = opts.ledger_id

Line 3075: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3071: AND le_bsv_map.je_category_name = opts.je_category_name
3072: AND opts.status_flag = 'Y')
3073: WHERE le_bsv_map.template_id IS NULL;
3074:
3075: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3076: SET template_id =
3077: (SELECT opts.template_id
3078: FROM fun_balance_options opts
3079: WHERE le_bsv_map.ledger_id = opts.ledger_id

Line 3092: FROM fun_bal_le_bsv_map_gt le_bsv_map

3088: SELECT 'FUN_INTRA_RULE_NOT_ASSIGNED',
3089: le_bsv_map.group_id, le_bsv_map.template_id,
3090: DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
3091: le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
3092: FROM fun_bal_le_bsv_map_gt le_bsv_map
3093: WHERE le_bsv_map.template_id IS NULL;
3094:
3095: IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3096: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_template.finish', 'finish');

Line 3131: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3127: -- 3M 1C 1,2,3 journal N
3128: -- 3M 2D M:M (4) None N
3129: -- 3M 2D 1,2,3 journal N
3130: ---------------------------------------------------------------------------------
3131: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3132: SET (balance_by, clearing_option, clearing_bsv, many_to_many_option) =
3133: (SELECT opts.balance_by_flag, opts.clearing_option,
3134: DECODE (opts.clearing_option,
3135: '1A', DECODE (opts.many_to_many_option,

Line 3154: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3150: AND opts.status_flag = 'Y');
3151:
3152:
3153: -- Note: A new intracompany mode 5 is introduced. Intracompany mode is 5 if clearing BSV is used
3154: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3155: SET intracompany_mode = 5
3156: WHERE le_bsv_map.clearing_bsv IS NOT NULL
3157: AND ((le_bsv_map.clearing_option = '1A') OR
3158: (le_bsv_map.clearing_option = '3M' AND

Line 3162: --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LE_BSV_MAP_GT');

3158: (le_bsv_map.clearing_option = '3M' AND
3159: le_bsv_map.intracompany_mode = 4 AND
3160: le_bsv_map.many_to_many_option IN ('2E', '1C')));
3161:
3162: --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LE_BSV_MAP_GT');
3163:
3164: INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, dr_bsv, cr_bsv)
3165: SELECT 'FUN_INTRA_NO_CLEARING_BSV',
3166: le_bsv_map.group_id, le_bsv_map.template_id,

Line 3169: FROM fun_bal_le_bsv_map_gt le_bsv_map

3165: SELECT 'FUN_INTRA_NO_CLEARING_BSV',
3166: le_bsv_map.group_id, le_bsv_map.template_id,
3167: DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
3168: le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
3169: FROM fun_bal_le_bsv_map_gt le_bsv_map
3170: WHERE le_bsv_map.clearing_bsv IS NULL
3171: AND ((le_bsv_map.clearing_option = '1A') OR
3172: (le_bsv_map.clearing_option = '3M' AND
3173: le_bsv_map.intracompany_mode = 4 AND

Line 3186: FROM fun_bal_le_bsv_map_gt le_bsv_map,

3182: le_bsv_map.group_id, le_bsv_map.template_id,
3183: DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
3184: le_bsv_map.clearing_bsv,
3185: le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
3186: FROM fun_bal_le_bsv_map_gt le_bsv_map,
3187: gl_ledgers ledger
3188: WHERE le_bsv_map.clearing_bsv IS NOT NULL
3189: AND ledger.ledger_id = le_bsv_map.ledger_id
3190: AND ledger.bal_seg_value_option_code = 'I'

Line 3211: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map

3207: 'FUN_INTRA_NO_CLEARING_BSV',
3208: 'FUN_INTRA_CLEAR_BSV_INVALID'))
3209: AND headers.status = 'OK';
3210:
3211: DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
3212: WHERE EXISTS (SELECT group_id
3213: FROM fun_bal_headers_gt headers
3214: WHERE headers.status = 'ERROR'
3215: AND le_bsv_map.group_id = headers.group_id);

Line 3218: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3214: WHERE headers.status = 'ERROR'
3215: AND le_bsv_map.group_id = headers.group_id);
3216:
3217: -- Update ccid for each DB BSV and CR BSV /* change here */
3218: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3219: SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
3220: (SELECT dr_ccid, cr_ccid, 'N', 'N'
3221: FROM fun_balance_accounts accts
3222: WHERE le_bsv_map.template_id = accts.template_id

Line 3239: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3235: AND le_bsv_map.bal_seg_val = accts.dr_bsv
3236: AND le_bsv_map.driving_cr_bsv = accts.cr_bsv)));
3237:
3238:
3239: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3240: SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
3241: (SELECT dr_ccid, cr_ccid, 'N','N' /* Bug 14565212 Set _complete to N for all cases */
3242: FROM fun_balance_accounts accts
3243: WHERE le_bsv_map.template_id = accts.template_id

Line 3252: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3248: AND le_bsv_map.bal_seg_val = accts.dr_bsv
3249: AND 'OTHER1234567890123456789012345' = accts.cr_bsv)))
3250: WHERE dr_cr_debit_ccid IS NULL; --OR dr_cr_credit_ccid IS NULL; No need to check both
3251:
3252: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3253: SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
3254: (SELECT dr_ccid, cr_ccid, 'N','N' /* Bug 14565212 Set _complete to N for all cases */
3255: FROM fun_balance_accounts accts
3256: WHERE le_bsv_map.template_id = accts.template_id

Line 3273: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3269: AND 'OTHER1234567890123456789012345' = accts.dr_bsv
3270: AND le_bsv_map.driving_cr_bsv = accts.cr_bsv)))
3271: WHERE dr_cr_debit_ccid IS NULL;
3272:
3273: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3274: SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
3275: (SELECT dr_ccid, cr_ccid, 'N', 'N'
3276: FROM fun_balance_accounts accts
3277: WHERE le_bsv_map.template_id = accts.template_id

Line 3283: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3279: AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
3280: WHERE dr_cr_debit_ccid IS NULL ;
3281:
3282: -- Upating cr_dr_debit_ccid, cr_dr_credit_ccid
3283: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3284: SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
3285: (SELECT dr_ccid, cr_ccid, 'N', 'N'
3286: FROM fun_balance_accounts accts
3287: WHERE le_bsv_map.template_id = accts.template_id

Line 3303: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3299: OR (le_bsv_map.intracompany_mode = 3
3300: AND le_bsv_map.bal_seg_val = accts.cr_bsv
3301: AND le_bsv_map.driving_cr_bsv = accts.dr_bsv)));
3302:
3303: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3304: SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
3305: (SELECT dr_ccid, cr_ccid,'N','N'/* Bug 14565212 Set _complete to N for all cases */
3306: FROM fun_balance_accounts accts
3307: WHERE le_bsv_map.template_id = accts.template_id

Line 3324: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3320: AND 'OTHER1234567890123456789012345' = accts.cr_bsv
3321: AND le_bsv_map.driving_cr_bsv = accts.dr_bsv)))
3322: WHERE cr_dr_debit_ccid IS NULL;
3323:
3324: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3325: SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
3326: (SELECT dr_ccid, cr_ccid, 'N', 'N'/* SN */
3327: FROM fun_balance_accounts accts
3328: WHERE le_bsv_map.template_id = accts.template_id

Line 3337: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3333: AND le_bsv_map.bal_seg_val = accts.cr_bsv
3334: AND 'OTHER1234567890123456789012345' = accts.dr_bsv)))
3335: WHERE cr_dr_debit_ccid IS NULL;
3336:
3337: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3338: SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
3339: (SELECT dr_ccid, cr_ccid, 'N', 'N' /* SN */
3340: FROM fun_balance_accounts accts
3341: WHERE le_bsv_map.template_id = accts.template_id

Line 3347: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3343: AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
3344: WHERE cr_dr_debit_ccid IS NULL;
3345:
3346: /* Not done for checking ccid valid through gl_code_combinations directly
3347: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3348: SET (dr_cr_debit_ccid, dr_cr_debit_complete) =
3349: (SELECT code_combination_id, DECODE(ccid, NULL, 'N', 'Y')
3350: FROM gl_code_combinations cc1,
3351: gl_code_combinations cc2

Line 3363: UPDATE fun_bal_le_bsv_map_gt le_bsv_map

3359: END IF;
3360:
3361: --Bug: 11665072.
3362: -- Retrieve correct ccid by replacing balancing segment and intercompany segment
3363: UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3364: SET dr_cr_debit_ccid =
3365: /*remove decode */ get_ccid(le_bsv_map.dr_cr_debit_ccid, le_bsv_map.chart_of_accounts_id, le_bsv_map.bal_seg_val,
3366: DECODE(le_bsv_map.intracompany_mode,
3367: 1, DECODE(le_bsv_map.bal_seg_val, le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv, le_bsv_map.driving_dr_bsv),

Line 3463: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines, fun_bal_headers_gt hdrs

3459: 'D')) type,
3460: */
3461: DECODE(lines.accounted_amt_cr, NULL, DECODE(lines.entered_amt_cr, NULL, 'D', 'C'), 'C') type,
3462: le_bsv_map.clearing_bsv
3463: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines, fun_bal_headers_gt hdrs
3464: WHERE hdrs.group_id = lines.group_id
3465: AND lines.group_id = le_bsv_map.group_id
3466: AND lines.bal_seg_val = le_bsv_map.bal_seg_val
3467: AND hdrs.status = 'OK'

Line 3497: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,

3493: -1, 'D',
3494: 0, DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
3495: 1, 'C',
3496: 'D')) type, le_bsv_map.clearing_bsv
3497: FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
3498: fun_bal_headers_gt hdrs
3499: WHERE hdrs.group_id = lines.group_id
3500: AND lines.group_id = le_bsv_map.group_id
3501: AND lines.bal_seg_val = le_bsv_map.bal_seg_val