[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT concatenated_segments, chart_of_accounts_id
INTO l_concatenated_segments, l_chart_of_accounts_id
FROM gl_code_combinations_kfv
WHERE code_combination_id = ccid;
DELETE FROM fun_bal_results_gt;
DELETE FROM fun_bal_errors_gt;
DELETE FROM fun_bal_le_bsv_map_gt;
DELETE FROM fun_bal_inter_int_gt;
DELETE FROM fun_bal_intra_int_gt;
/* Using delete rather than truncate as shown in the code below. The reason is that truncate (or any DDL operations)
perform an implicit commit => need to use autonomous transaction to perform such operation. However, we would
like to make sure the calling program does not see the rows that gets deleted, therefore truncate is not used.
In addition, the truncate operation might not be able to delete the rows that the calling program has not commited yet,
which could result in that we think the rows got deleted but they still exist.
cur_hdl := dbms_sql.open_cursor;
PROCEDURE update_inter_seg_val IS
stmt_str varchar2(1000);
SELECT DISTINCT bal_seg_column_name
FROM fun_bal_headers_gt headers
WHERE headers.status = 'OK';
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.update_inter_seg_val.begin', 'begin');
stmt_str := 'UPDATE fun_bal_inter_int_gt inter_int ' ||
' SET rec_bsv = ' ||
' (SELECT ' || l_bal_seg_column_name ||
' FROM gl_code_combinations ' ||
' WHERE code_combination_id = inter_int.rec_acct ' ||
' AND inter_int.bal_seg_column_name = ''' || l_bal_seg_column_name || ''') ' ||
' WHERE inter_int.rec_acct IS NOT NULL AND inter_int.rec_acct <> -1';
stmt_str := 'UPDATE fun_bal_inter_int_gt inter_int ' ||
' SET pay_bsv = ' ||
' (SELECT ' || l_bal_seg_column_name ||
' FROM gl_code_combinations ' ||
' WHERE code_combination_id = inter_int.pay_acct ' ||
' AND inter_int.bal_seg_column_name = ''' || l_bal_seg_column_name || ''') ' ||
' WHERE inter_int.pay_acct IS NOT NULL AND inter_int.pay_acct <> -1';
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.update_inter_seg_val.end', 'end');
END update_inter_seg_val;
SELECT s.segment_num, sav.segment_attribute_type
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav
WHERE s.application_id = 101
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = p_chart_of_accounts_id
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = p_chart_of_accounts_id
AND sav.attribute_value = 'Y'
ORDER BY s.segment_num ASC;
INSERT INTO fun_bal_headers_t
VALUES headers_tab(i);
INSERT INTO fun_bal_lines_t
VALUES lines_tab(i);
INSERT INTO fun_bal_results_t
VALUES results_tab(i);
INSERT INTO fun_bal_errors_t
VALUES errors_tab(i);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'ins_inter_le_bsv_map_t.insert_begin');
INSERT INTO fun_bal_inter_bsv_map_t
VALUES le_bsv_map_tab(i);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'ins_inter_le_bsv_map_t.insert_end');
INSERT INTO fun_bal_inter_int_t
VALUES inter_int_tab(i);
INSERT INTO fun_bal_intra_bsv_map_t
VALUES le_bsv_map_tab(i);
INSERT INTO fun_bal_intra_int_t
VALUES intra_int_tab(i);
INSERT INTO fun_bal_headers_t
SELECT * FROM fun_bal_headers_gt;
INSERT INTO fun_bal_lines_t
SELECT * FROM fun_bal_lines_gt;
INSERT INTO fun_bal_results_t
SELECT * FROM fun_bal_results_gt;
INSERT INTO fun_bal_errors_t
SELECT * FROM fun_bal_errors_gt;
UPDATE fun_bal_headers_gt headers
SET (bal_seg_column_name, chart_of_accounts_id) =
(SELECT bal_seg_column_name, chart_of_accounts_id
FROM gl_ledgers ledgers
WHERE headers.ledger_id = ledgers.ledger_id);
UPDATE fun_bal_headers_gt headers
SET bal_seg_column_number = get_segment_index ( headers.chart_of_accounts_id,
'GL_BALANCING'),
intercompany_column_number = get_segment_index ( headers.chart_of_accounts_id,
'GL_INTERCOMPANY');
UPDATE fun_bal_lines_gt lines
SET entered_amt_dr = accounted_amt_dr,
entered_amt_cr = accounted_amt_cr,
entered_currency_code = (select gl.currency_code
from fun_bal_headers_gt headers, gl_ledgers gl
where gl.ledger_id = headers.ledger_id
and headers.group_id = lines.group_id),
exchange_date = null,
exchange_rate = null,
exchange_rate_type = null
WHERE 1 < (select count(distinct entered_currency_code)
from fun_bal_lines_gt lines2
where lines2.group_id = lines.group_id);
SELECT * FROM fun_bal_headers_gt;
SELECT * FROM fun_bal_lines_gt;
SELECT * FROM fun_bal_headers_gt;
SELECT * FROM fun_bal_lines_gt;
SELECT * FROM fun_bal_results_gt;
SELECT * FROM fun_bal_errors_gt;
INSERT INTO fun_bal_results_gt results(group_id, bal_seg_val, entered_amt_dr,
entered_amt_cr, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
accounted_amt_dr, accounted_amt_cr, ccid, balancing_type)
SELECT lines.group_id, lines.bal_seg_val, lines.entered_amt_dr,
lines.entered_amt_cr, lines.entered_currency_code, lines.exchange_date, lines.exchange_rate,
lines.exchange_rate_type, lines.accounted_amt_dr, lines.accounted_amt_cr,
lines.ccid, 'E'
FROM fun_bal_lines_gt lines
WHERE lines.generated = 'Y';
UPDATE fun_bal_results_gt results
SET entered_amt_dr = DECODE(entered_amt_dr, NULL, DECODE(accounted_amt_dr, NULL, entered_amt_dr, 0), entered_amt_dr),
entered_amt_cr = DECODE(entered_amt_cr, NULL, DECODE(accounted_amt_cr, NULL, entered_amt_cr, 0), entered_amt_cr),
accounted_amt_dr = DECODE(accounted_amt_dr, NULL, DECODE(entered_amt_dr, NULL, accounted_amt_dr, 0), accounted_amt_dr),
accounted_amt_cr = DECODE(accounted_amt_cr, NULL, DECODE(entered_amt_cr, NULL, accounted_amt_cr, 0), accounted_amt_cr),
exchange_rate = DECODE(EXCHANGE_RATE, NULL, NULL,
DECODE (NVL(ACCOUNTED_AMT_CR, 0), 0, DECODE(NVL(ENTERED_AMT_DR, 0), 0, 1, ACCOUNTED_AMT_DR / ENTERED_AMT_DR)
, DECODE(NVL(ENTERED_AMT_CR, 0), 0, 1, ACCOUNTED_AMT_CR / ENTERED_AMT_CR)));
SELECT COUNT(*) INTO l_errors_count
FROM fun_bal_errors_gt;
SELECT * FROM fun_bal_le_bsv_map_gt;
SELECT * FROM fun_bal_inter_int_gt;
INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date)
SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date
FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger,
gl_ledger_configurations config
WHERE hdrs.group_id = lines.group_id
AND hdrs.ledger_id = ledger.ledger_id
AND ledger.configuration_id = config.configuration_id
AND ledger.bal_seg_value_option_code = 'I';
UPDATE fun_bal_le_bsv_map_gt bsv_le_map
SET le_id =
NVL((SELECT vals.legal_entity_id
FROM gl_ledger_le_bsv_specific_v vals
WHERE bsv_le_map.bal_seg_val = vals.segment_value
AND (TRUNC(bsv_le_map.gl_date) BETWEEN TRUNC(NVL(vals.start_date, bsv_le_map.gl_date)) AND
TRUNC(NVL(vals.end_date, bsv_le_map.gl_date)))
AND bsv_le_map.ledger_id = vals.ledger_id
), -99);
INSERT INTO fun_bal_errors_gt(error_code, group_id, bal_seg_val)
SELECT 'FUN_BSV_INVALID', main.group_id, main.bal_seg_val
FROM (SELECT DISTINCT hdrs.group_id, lines.bal_seg_val, hdrs.gl_date, hdrs.ledger_id
FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger
WHERE hdrs.group_id = lines.group_id
AND hdrs.ledger_id = ledger.ledger_id(+)
AND ledger.bal_seg_value_option_code = 'I') main
WHERE main.bal_seg_val NOT IN (SELECT vals.segment_value
FROM gl_ledger_le_bsv_specific_v vals
WHERE main.ledger_id = vals.ledger_id
AND TRUNC(main.gl_date) BETWEEN
TRUNC(NVL(vals.start_date, main.gl_date)) AND
TRUNC(NVL(vals.end_date, main.gl_date)));
UPDATE fun_bal_headers_gt headers
SET status = 'ERROR',
error_code = 'FUN_BSV_INVALID'
WHERE EXISTS (SELECT 'Invalid BSV Error'
FROM FUN_BAL_ERRORS_GT errors
WHERE headers.group_id = errors.group_id
AND error_code IN ('FUN_BSV_INVALID'))
AND headers.status = 'OK';
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)
SELECT main.group_id, main.ledger_id, main.bal_seg_val, main.gl_date, NVL(vals.legal_entity_id, -99),
main.je_source_name, main.je_category_name
FROM (SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date, hdrs.je_source_name, hdrs.je_category_name
FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger,
gl_ledger_configurations config
WHERE hdrs.status = 'OK' -- Bug 3310453
AND hdrs.group_id = lines.group_id
AND hdrs.ledger_id = ledger.ledger_id(+)
AND ledger.configuration_id = config.configuration_id
AND ledger.configuration_id <> -2 -- Bug 3271446
AND ledger.bal_seg_value_option_code = 'I') main,
gl_ledger_le_bsv_specific_v vals
WHERE main.bal_seg_val = vals.segment_value(+)
AND (TRUNC(main.gl_date) BETWEEN TRUNC(NVL(vals.start_date, main.gl_date)) AND
TRUNC(NVL(vals.end_date, main.gl_date)))
AND main.ledger_id = vals.ledger_id(+);
UPDATE fun_bal_headers_gt headers
SET (le_id, le_count) =
(SELECT MIN(le_bsv_map.le_id), SUM(COUNT(DISTINCT le_bsv_map.le_id))
FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE headers.group_id = le_bsv_map.group_id
AND le_bsv_map.le_id <> -99
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
UPDATE fun_bal_headers_gt headers
SET status = DECODE(le_id, NULL, status, 'ERROR'),
error_code = DECODE(le_id, NULL, error_code, 'FUN_INTER_BSV_NOT_ASSIGNED'),
unmapped_bsv_le_id = -99
WHERE EXISTS (SELECT 'Unmapped BSV exists'
FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE le_bsv_map.group_id = headers.group_id
AND le_bsv_map.le_id = -99);
UPDATE fun_bal_headers_gt headers
SET status = 'ERROR',
error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR'
WHERE headers.status = 'OK'
AND headers.clearing_bsv IS NOT NULL
AND NOT (headers.le_count IS NULL AND headers.unmapped_bsv_le_id IS NULL) -- Bug 3278912
AND (headers.le_count > 1
OR
(headers.le_count = 1 AND headers.unmapped_bsv_le_id = -99)
OR
(headers.le_id IS NOT NULL
AND NOT EXISTS (SELECT 'BSV belongs to the LE'
FROM gl_ledger_le_bsv_specific_v vals
WHERE vals.segment_value = headers.clearing_bsv
AND vals.ledger_id = headers.ledger_id
AND vals.legal_entity_id = headers.le_id))
OR
(headers.le_id IS NULL
AND EXISTS (SELECT 'BSV belongs to Ledger'
FROM gl_ledger_le_bsv_specific_v vals
WHERE vals.segment_value = headers.clearing_bsv
AND vals.ledger_id = headers.ledger_id
AND vals.legal_entity_id IS NOT NULL))); -- Bug 3278912
UPDATE fun_bal_headers_gt hdrs
SET status = 'ERROR',
error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR'
WHERE hdrs.status = 'OK'
AND hdrs.clearing_bsv IS NOT NULL
AND NOT (hdrs.ledger_id IN (SELECT ledgers.ledger_id
FROM gl_ledgers ledgers
WHERE ledgers.bal_seg_value_option_code = 'A')
OR
hdrs.clearing_bsv IN (SELECT vals.segment_value
FROM gl_ledger_le_bsv_specific_v vals
WHERE hdrs.ledger_id = vals.ledger_id
AND TRUNC(hdrs.gl_date) BETWEEN
TRUNC(NVL(vals.start_date, hdrs.gl_date))
AND
TRUNC(NVL(vals.end_date, hdrs.gl_date))));
INSERT INTO fun_bal_errors_gt(error_code, group_id, clearing_bsv)
SELECT 'FUN_INTRA_OVERRIDE_BSV_ERROR', hdrs.group_id, hdrs.clearing_bsv
FROM fun_bal_headers_gt hdrs
WHERE hdrs.error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR';
INSERT INTO fun_bal_errors_gt(error_code, group_id, bal_seg_val)
SELECT 'FUN_INTER_BSV_NOT_ASSIGNED', hdrs.group_id, le_bsv_map.bal_seg_val
FROM fun_bal_headers_gt hdrs, fun_bal_le_bsv_map_gt le_bsv_map
WHERE hdrs.group_id = le_bsv_map.group_id
AND hdrs.error_code = 'FUN_INTER_BSV_NOT_ASSIGNED'
AND le_bsv_map.le_id = -99;
DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE group_id = (SELECT group_id
FROM fun_bal_headers_gt headers
WHERE headers.status = 'ERROR'
AND le_bsv_map.group_id = headers.group_id);
DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
WHERE EXISTS (SELECT 'LE already balanced'
FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
AND le_bsv_map_del.le_id = le_bsv_map.le_id
AND le_bsv_map.group_id = lines.group_id
AND le_bsv_map.bal_seg_val = lines.bal_seg_val
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id
HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) =
SUM(NVL(lines.accounted_amt_cr, 0)))
AND
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0))=
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)));
UPDATE fun_bal_headers_gt hdrs
SET (driving_dr_le_id, intercompany_mode) =
(SELECT MIN(le_bsv_map.le_id),
SUM(COUNT(DISTINCT(le_bsv_map.le_id)))
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
WHERE hdrs.group_id = le_bsv_map.group_id
AND le_bsv_map.group_id = lines.group_id
AND le_bsv_map.bal_seg_val = lines.bal_seg_val
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id
HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) >
SUM(NVL(lines.accounted_amt_cr, 0)))
OR
((SUM(NVL(lines.accounted_amt_dr, 0)) =
SUM(NVL(lines.accounted_amt_cr, 0))) AND
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) >
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0))))
WHERE status = 'OK';
DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE EXISTS
(SELECT 'Intercompany balancing is not required'
FROM fun_bal_headers_gt hdrs
WHERE le_bsv_map.group_id = hdrs.group_id
AND hdrs.status = 'OK'
AND hdrs.intercompany_mode IS NULL);
UPDATE fun_bal_headers_gt hdrs
SET (driving_cr_le_id, intercompany_mode) =
(SELECT MIN(le_bsv_map.le_id),
DECODE(SUM(COUNT(DISTINCT(le_bsv_map.le_id))), 1,
DECODE(hdrs.intercompany_mode, 1, 1, 3),
DECODE(hdrs.intercompany_mode, 1, 2, 4))
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
WHERE hdrs.group_id = le_bsv_map.group_id
AND le_bsv_map.group_id = lines.group_id
AND le_bsv_map.bal_seg_val = lines.bal_seg_val
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id
HAVING (SUM(NVL(lines.accounted_amt_cr, 0)) >
SUM(NVL(lines.accounted_amt_dr, 0)))
OR
((SUM(NVL(lines.accounted_amt_dr, 0)) =
SUM(NVL(lines.accounted_amt_cr, 0))) AND
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) >
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))))
WHERE status = 'OK';
DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE EXISTS (SELECT 'No Driving DR LE or Driving CR LE'
FROM fun_bal_headers_gt headers
WHERE headers.group_id = le_bsv_map.group_id
AND headers.status = 'OK'
AND (headers.driving_dr_le_id IS NULL
OR
headers.driving_cr_le_id IS NULL));
INSERT INTO FUN_BAL_INTER_INT2_GT
(GROUP_ID,
LEDGER_ID,
GL_DATE,
STATUS,
DRIVING_DR_LE_ID,
DRIVING_CR_LE_ID,
INTERCOMPANY_MODE,
LE_ID,
ENTERED_CURRENCY_CODE,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
ACCOUNTED_AMT_CR,
ACCOUNTED_AMT_DR,
ENTERED_AMT_CR,
ENTERED_AMT_DR,
BAL_SEG_COLUMN_NAME,
LINE_LE_BSV,
TYPE)
SELECT GROUP_ID,
LEDGER_ID,
GL_DATE,
STATUS,
DRIVING_DR_LE_ID,
DRIVING_CR_LE_ID,
INTERCOMPANY_MODE,
LE_ID,
ENTERED_CURRENCY_CODE,
SYSDATE,
EXCHANGE_RATE,
'User',
ACCOUNTED_AMT_CR,
ACCOUNTED_AMT_DR,
ENTERED_AMT_CR,
ENTERED_AMT_DR,
BAL_SEG_COLUMN_NAME,
BAL_SEG_VAL,
TYPE
FROM (SELECT HDRS.GROUP_ID GROUP_ID,
HDRS.LEDGER_ID LEDGER_ID,
HDRS.GL_DATE GL_DATE,
HDRS.STATUS STATUS,
HDRS.DRIVING_DR_LE_ID DRIVING_DR_LE_ID,
HDRS.DRIVING_CR_LE_ID DRIVING_CR_LE_ID,
HDRS.INTERCOMPANY_MODE INTERCOMPANY_MODE,
LE_BSV_MAP.LE_ID LE_ID,
LINES.ENTERED_CURRENCY_CODE ENTERED_CURRENCY_CODE,
MAX(LINES.EXCHANGE_DATE) EXCHANGE_DATE,
DECODE(MAX(LINES.EXCHANGE_RATE), NULL, NULL, 1) EXCHANGE_RATE, --Bug: 12887806
MAX(LINES.EXCHANGE_RATE_TYPE) EXCHANGE_RATE_TYPE,
DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1, ABS(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
NULL) ACCOUNTED_AMT_CR,
DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
-1, ABS(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
NULL) ACCOUNTED_AMT_DR,
DECODE(SIGN(SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0))),
1, ABS(SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0))),
NULL) ENTERED_AMT_CR,
DECODE(SIGN(SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0))),
-1, ABS(SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0))),
NULL) ENTERED_AMT_DR,
HDRS.BAL_SEG_COLUMN_NAME BAL_SEG_COLUMN_NAME,
LINES.BAL_SEG_VAL BAL_SEG_VAL,
DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1, 'C',
-1, 'D',
0, DECODE(SIGN((( SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0)) )) -
( SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) )), 1, 'C', 'D')) TYPE
FROM FUN_BAL_LE_BSV_MAP_GT LE_BSV_MAP,
FUN_BAL_LINES_GT LINES,
FUN_BAL_HEADERS_GT HDRS
WHERE HDRS.GROUP_ID = LINES.GROUP_ID
AND LINES.GROUP_ID = LE_BSV_MAP.GROUP_ID
AND LINES.BAL_SEG_VAL = LE_BSV_MAP.BAL_SEG_VAL
AND HDRS.INTERCOMPANY_MODE IN ( 1, 2, 3, 4 )
AND HDRS.STATUS = 'OK'
GROUP BY HDRS.GROUP_ID,
HDRS.LEDGER_ID,
HDRS.GL_DATE,
HDRS.STATUS,
HDRS.DRIVING_DR_LE_ID,
HDRS.DRIVING_CR_LE_ID,
HDRS.INTERCOMPANY_MODE,
LE_BSV_MAP.LE_ID,
LINES.ENTERED_CURRENCY_CODE,
-- LINES.EXCHANGE_DATE,
-- lines.exchange_rate,
-- LINES.EXCHANGE_RATE_TYPE,
HDRS.BAL_SEG_COLUMN_NAME,
HDRS.INTERCOMPANY_COLUMN_NUMBER,
LINES.BAL_SEG_VAL
HAVING SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) <> SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))
OR ( SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) = SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))
AND SUM(DECODE(LINES.EXCHANGE_RATE, NULL, NVL(LINES.ENTERED_AMT_CR, 0),
0)) <> SUM(DECODE(LINES.EXCHANGE_RATE, NULL, NVL(LINES.ENTERED_AMT_DR, 0),
0)) ));
delete fun_bal_inter_int3_gt;
INSERT INTO fun_bal_inter_int3_gt(
GROUP_ID,
LEDGER_ID,
LE_ID,
ACCOUNTED_AMT_CR,
ACCOUNTED_AMT_DR,
TYPE,
DRIVING_DR_LE_BSV,
DRIVING_CR_LE_BSV,
LINE_LE_BSV)
SELECT
GROUP_ID,
LEDGER_ID,
LE_ID,
sum(ACCOUNTED_AMT_CR ),
sum(ACCOUNTED_AMT_DR ),
DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1, 'C',
-1, 'D',
0, DECODE(SIGN((( SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) )) -
( SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) )), 1, 'C', 'D')) type,
null DR_LE_BSV,
null CR_LE_BSV,
LINE_LE_BSV
FROM fun_bal_inter_int2_gt lines
GROUP BY
GROUP_ID,
LEDGER_ID,
LE_ID,
LINE_LE_BSV;
UPDATE fun_bal_inter_int3_gt upd
SET driving_dr_le_bsv =
DECODE(upd.type, 'D', upd.line_le_bsv,
'C', DECODE((SELECT COUNT(DISTINCT cmp.line_le_bsv)
FROM fun_bal_inter_int3_gt cmp
WHERE upd.group_id = cmp.group_id
AND cmp.type='D'
),
1, (SELECT DISTINCT cmp1.line_le_bsv
FROM fun_bal_inter_int3_gt cmp1
WHERE upd.group_id = cmp1.group_id
AND cmp1.type='D'
),'Many'));
UPDATE fun_bal_inter_int3_gt upd
SET driving_cr_le_bsv =
DECODE(upd.type, 'C', upd.line_le_bsv,
'D', DECODE((SELECT COUNT(DISTINCT cmp.line_le_bsv)
FROM fun_bal_inter_int3_gt cmp
WHERE upd.group_id = cmp.group_id
AND cmp.type='C'
),
1, (SELECT DISTINCT cmp1.line_le_bsv
FROM fun_bal_inter_int3_gt cmp1
WHERE upd.group_id = cmp1.group_id
AND cmp1.type='C'
),'Many'));
update fun_bal_inter_int2_gt t2
set driving_dr_le_bsv = (select driving_dr_le_bsv
from fun_bal_inter_int3_gt dr_t3
where dr_t3.group_id = t2.group_id
and dr_t3.le_id = t2.le_id
and dr_t3.line_le_bsv = t2.line_le_bsv
);
update fun_bal_inter_int2_gt t2
set driving_cr_le_bsv = (select driving_cr_le_bsv
from fun_bal_inter_int3_gt dr_t3
where dr_t3.group_id = t2.group_id
and dr_t3.le_id = t2.le_id
and dr_t3.line_le_bsv = t2.line_le_bsv
);
Update fun_bal_inter_int2_gt
SET (driving_dr_le_bsv, driving_cr_le_bsv)=(Select 'Many','Many' from Dual)
where group_id IN (Select upd1.group_id from (select count(DISTINCT inter_int2.line_le_bsv) count1, group_id from fun_bal_inter_int2_gt inter_int2
where type='D'
GROUP by group_id) upd1,
(select count(DISTINCT inter_int2.line_le_bsv) count1, group_id from fun_bal_inter_int2_gt inter_int2
WHERE type='C'
GROUP by group_id) upd2
WHERE upd1.group_id=upd2.group_id
AND upd1.count1 > 1
AND upd2.count1 > 1) ;
UPDATE fun_bal_inter_int2_gt upd1
SET driving_dr_le_id = DECODE(upd1.TYPE, 'D', upd1.LE_ID, DECODE((select count(DISTINCT upd2.le_id ) from fun_bal_inter_int2_gt UPD2
WHERE type = 'D' AND upd2.group_id=upd1.group_id), 1, (SELECT DISTINCT LE_ID FROM fun_bal_inter_int2_gt upd3 WHERE TYPE = 'D' AND upd3.group_id= upd1.group_id), -1))
WHERE intercompany_mode IN (1,2,3)
AND ( driving_dr_le_bsv <>'Many'
OR driving_cr_le_bsv <>'Many');
UPDATE fun_bal_inter_int2_gt upd1
SET driving_cr_le_id = DECODE(upd1.TYPE, 'C', upd1.LE_ID, DECODE((select count(DISTINCT upd2.le_id) from fun_bal_inter_int2_gt UPD2
WHERE type = 'C' AND upd2.group_id=upd1.group_id), 1, (SELECT DISTINCT LE_ID FROM fun_bal_inter_int2_gt upd3 WHERE TYPE = 'C' AND upd3.group_id= upd1.group_id), -1))
WHERE intercompany_mode IN (1,2,3)
AND ( driving_dr_le_bsv <>'Many'
OR driving_cr_le_bsv <>'Many');
UPDATE fun_bal_inter_int2_gt inter_int1 SET status='DEL'
WHERE (inter_int1.type, inter_int1.le_id,inter_int1.group_id) IN
(SELECT DECODE(SIGN(NVL(cr_sum, 0) - NVL(dr_sum, 0)), 1, 'D', -1, 'C', 'X'),
le_id,
group_id
FROM
(SELECT SUM(entered_amt_cr) cr_sum,
SUM(entered_amt_dr) dr_sum,
le_id,
group_id
FROM fun_bal_inter_int2_gt inter_int2
WHERE (inter_int2.le_id,inter_int2.group_id) IN
(SELECT inter_int3.le_id,
inter_int3.group_id
FROM fun_bal_inter_int2_gt inter_int3
WHERE inter_int3.intercompany_mode IN (1,2,3)
AND ( inter_int3.driving_dr_le_bsv <>'Many'
OR inter_int3.driving_cr_le_bsv <>'Many')
HAVING COUNT(*) > 1
GROUP BY inter_int3.group_id,
inter_int3.le_id
)
HAVING SUM(NVL(entered_amt_cr,0)) <> 0
AND SUM(NVL(entered_amt_dr,0)) <> 0
GROUP BY le_id,
group_id
)
) ;
UPDATE fun_bal_inter_int2_gt inter_int2
SET inter_int2.status='DEL'
where
not exists (select 1 from fun_bal_inter_int2_gt inter_int3
where inter_int2.group_id = inter_int3.group_id
and inter_int2.le_id <> inter_int3.le_id
and inter_int2.type <> inter_int3.type)
AND inter_int2.intercompany_mode IN (1,2,3)
AND ( inter_int2.driving_dr_le_bsv <>'Many'
OR inter_int2.driving_cr_le_bsv <>'Many');
/*UPDATE fun_bal_inter_int2_gt inter_int1 SET status='DEL'
WHERE (inter_int1.type, inter_int1.le_id,inter_int1.group_id) IN
(SELECT DECODE(SIGN(NVL(cr_sum, 0) - NVL(dr_sum, 0)), 1, 'D', -1, 'C', 'X'),
le_id,
group_id
FROM
(SELECT SUM(entered_amt_cr) cr_sum,
SUM(entered_amt_dr) dr_sum,
le_id,
group_id
FROM fun_bal_inter_int2_gt inter_int2
WHERE (inter_int2.le_id,inter_int2.group_id) IN
(SELECT le_id,
group_id
FROM fun_bal_inter_int2_gt
WHERE intercompany_mode IN (1,2,3)
AND ((driving_cr_le_bsv ='Many'
AND driving_dr_le_bsv <>'Many')
OR (driving_dr_le_bsv ='Many'
AND driving_cr_le_bsv <>'Many'))
HAVING COUNT(*) > 1
GROUP BY group_id,
le_id
)
GROUP BY le_id,
group_id
)
) ;*/
UPDATE fun_bal_inter_int2_gt inter_int1
SET
(
inter_int1.entered_amt_cr
)
=
(SELECT NVL(inter_int1.entered_amt_cr,0) - NVL(SUM(NVL(inter_int2.entered_amt_dr,0)),0)
FROM fun_bal_inter_int2_gt inter_int2
WHERE inter_int2.type='D'
AND inter_int2.le_id= inter_int1.le_id
AND inter_int2.group_id = inter_int1.group_id
group by inter_int2.group_id)
WHERE inter_int1.type='C'
AND 'D'=(Select DISTINCT type from fun_bal_inter_int2_gt inter_int3
WHERE inter_int3.group_id= inter_int1.group_id
AND inter_int3.le_id= inter_int1.le_id
AND inter_int3.status='DEL');
UPDATE fun_bal_inter_int2_gt inter_int1
SET
(
inter_int1.accounted_amt_cr
)
=
(SELECT NVL(inter_int1.accounted_amt_cr,0) - NVL(SUM(NVL(inter_int2.accounted_amt_dr,0)),0)
FROM fun_bal_inter_int2_gt inter_int2
WHERE inter_int2.type='D'
AND inter_int2.le_id= inter_int1.le_id
AND inter_int2.group_id = inter_int1.group_id
)
WHERE inter_int1.type='C'
AND 'D'=(Select DISTINCT type from fun_bal_inter_int2_gt inter_int3
WHERE inter_int3.group_id= inter_int1.group_id
AND inter_int3.le_id= inter_int1.le_id
AND inter_int3.status='DEL');
UPDATE fun_bal_inter_int2_gt inter_int1
SET
(
inter_int1.entered_amt_dr
)
=
(SELECT NVL(inter_int1.entered_amt_dr,0) - NVL(SUM(NVL(inter_int2.entered_amt_cr,0)),0)
FROM fun_bal_inter_int2_gt inter_int2
WHERE inter_int2.type='C'
AND inter_int2.le_id= inter_int1.le_id
AND inter_int2.group_id = inter_int1.group_id)
WHERE inter_int1.type='D'
AND 'C'=(Select distinct type from fun_bal_inter_int2_gt inter_int3
WHERE inter_int3.group_id= inter_int1.group_id
AND inter_int3.le_id= inter_int1.le_id
AND inter_int3.status='DEL');
UPDATE fun_bal_inter_int2_gt inter_int1
SET
(
inter_int1.accounted_amt_dr
)
=
(SELECT NVL(inter_int1.accounted_amt_dr,0) - NVL(SUM(NVL(inter_int2.accounted_amt_cr,0)),0)
FROM fun_bal_inter_int2_gt inter_int2
WHERE inter_int2.type='C'
AND inter_int2.le_id= inter_int1.le_id
AND inter_int2.group_id = inter_int1.group_id)
WHERE inter_int1.type='D'
AND 'C'=(Select distinct type from fun_bal_inter_int2_gt inter_int3
WHERE inter_int3.group_id= inter_int1.group_id
AND inter_int3.le_id= inter_int1.le_id
AND inter_int3.status='DEL');
DELETE from fun_bal_inter_int2_gt
where status='DEL'
OR (NVL(entered_amt_cr,0)=0
AND NVL(entered_amt_dr,0)=0
AND NVL(accounted_amt_cr,0)=0
AND NVL(accounted_amt_dr,0)=0
);
DELETE from fun_bal_le_bsv_map_gt le_bsv_map
WHERE bal_seg_val NOT IN (Select line_le_bsv from fun_bal_inter_int2_gt
WHERE group_id= le_bsv_map.group_id
AND le_id= le_bsv_map.le_id
) ;
UPDATE fun_bal_inter_int2_gt upd
SET (driving_dr_le_id, driving_dr_le_bsv,pay_bsv) =
(SELECT DISTINCT le_bsv_map.le_id,le_bsv_map.bal_seg_val,le_bsv_map.bal_seg_val
FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE le_bsv_map.group_id= upd.group_id
AND upd.line_le_bsv= le_bsv_map.bal_seg_val
)
WHERE intercompany_mode IN (1,2,3)
AND upd.driving_dr_le_bsv='Many'
AND upd.driving_cr_le_bsv <>'Many'
AND upd.driving_cr_le_bsv<>upd.line_le_bsv
AND status = 'OK'
AND upd.type='D';
UPDATE fun_bal_inter_int2_gt upd
SET (driving_cr_le_id, driving_cr_le_bsv,rec_bsv) =
(SELECT DISTINCT le_bsv_map.le_id,le_bsv_map.bal_seg_val,le_bsv_map.bal_seg_val
FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE le_bsv_map.group_id= upd.group_id
AND upd.line_le_bsv= le_bsv_map.bal_seg_val
)
WHERE intercompany_mode IN (1,2,3)
AND upd.driving_cr_le_bsv='Many'
AND upd.driving_dr_le_bsv <>'Many'
AND upd.driving_dr_le_bsv<>upd.line_le_bsv
AND status = 'OK'
AND upd.type='C';
UPDATE fun_bal_inter_int2_gt upd1
SET driving_dr_le_bsv= DECODE(upd1.driving_dr_le_id,-1,'Many',
le_id, line_le_bsv,
(select bal_seg_val
from fun_bal_le_bsv_map_gt
where group_id=upd1.group_id
and le_id= upd1.driving_dr_le_id)),
driving_cr_le_bsv= DECODE(upd1.driving_cr_le_id,-1,'Many',
le_id, line_le_bsv,
(select bal_seg_val
from fun_bal_le_bsv_map_gt
where group_id=upd1.group_id
and le_id= upd1.driving_cr_le_id))
Where intercompany_mode in (1,2,3)
AND ( driving_dr_le_bsv <>'Many'
OR driving_cr_le_bsv <>'Many'); */
delete fun_bal_inter_int3_gt;
INSERT INTO fun_bal_inter_int3_gt(
GROUP_ID,
LEDGER_ID,
LE_ID,
ACCOUNTED_AMT_CR,
ACCOUNTED_AMT_DR,
TYPE,
DRIVING_DR_LE_BSV,
DRIVING_CR_LE_BSV,
LINE_LE_BSV)
SELECT
GROUP_ID,
LEDGER_ID,
LE_ID,
sum(ACCOUNTED_AMT_CR ),
sum(ACCOUNTED_AMT_DR ),
DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1, 'C',
-1, 'D',
0, DECODE(SIGN((( SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) )) -
( SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) )), 1, 'C', 'D')) type,
null DR_LE_BSV,
null CR_LE_BSV,
LINE_LE_BSV
FROM fun_bal_inter_int2_gt lines
GROUP BY
GROUP_ID,
LEDGER_ID,
LE_ID,
LINE_LE_BSV;
UPDATE fun_bal_inter_int3_gt upd
SET driving_dr_le_bsv =
DECODE(upd.type, 'D', upd.line_le_bsv,
'C', DECODE((SELECT COUNT(DISTINCT cmp.line_le_bsv)
FROM fun_bal_inter_int3_gt cmp
WHERE upd.group_id = cmp.group_id
AND cmp.type='D'
),
1, (SELECT DISTINCT cmp1.line_le_bsv
FROM fun_bal_inter_int3_gt cmp1
WHERE upd.group_id = cmp1.group_id
AND cmp1.type='D'
),'Many'));
UPDATE fun_bal_inter_int3_gt upd
SET driving_cr_le_bsv =
DECODE(upd.type, 'C', upd.line_le_bsv,
'D', DECODE((SELECT COUNT(DISTINCT cmp.line_le_bsv)
FROM fun_bal_inter_int3_gt cmp
WHERE upd.group_id = cmp.group_id
AND cmp.type='C'
),
1, (SELECT DISTINCT cmp1.line_le_bsv
FROM fun_bal_inter_int3_gt cmp1
WHERE upd.group_id = cmp1.group_id
AND cmp1.type='C'
),'Many'));
update fun_bal_inter_int2_gt t2
set driving_dr_le_bsv = (select driving_dr_le_bsv
from fun_bal_inter_int3_gt dr_t3
where dr_t3.group_id = t2.group_id
and dr_t3.le_id = t2.le_id
and dr_t3.line_le_bsv = t2.line_le_bsv
);
update fun_bal_inter_int2_gt t2
set driving_cr_le_bsv = (select driving_cr_le_bsv
from fun_bal_inter_int3_gt dr_t3
where dr_t3.group_id = t2.group_id
and dr_t3.le_id = t2.le_id
and dr_t3.line_le_bsv = t2.line_le_bsv
);
Update fun_bal_inter_int2_gt
SET (driving_dr_le_bsv, driving_cr_le_bsv)=(Select 'Many','Many' from Dual)
where group_id IN (Select upd1.group_id from (select count(DISTINCT inter_int2.line_le_bsv) count1, group_id from fun_bal_inter_int2_gt inter_int2
where type='D'
GROUP by group_id) upd1,
(select count(DISTINCT inter_int2.line_le_bsv) count1, group_id from fun_bal_inter_int2_gt inter_int2
WHERE type='C'
GROUP by group_id) upd2
WHERE upd1.group_id=upd2.group_id
AND upd1.count1 > 1
AND upd2.count1 > 1) ;
UPDATE fun_bal_inter_int2_gt SET REC_BSV = driving_cr_le_bsv,PAY_BSV = driving_dr_le_bsv
WHERE intercompany_mode IN (1,2,3)
AND driving_dr_le_bsv <>'Many'
OR driving_cr_le_bsv <>'Many';
UPDATE fun_bal_inter_int2_gt t1
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
where le_bsv.group_id = t1.group_id
and le_bsv.bal_seg_val = t1.driving_dr_le_bsv)),
driving_cr_le_id = decode(driving_cr_le_bsv, 'Many', -1, (select distinct le_id from FUN_BAL_LE_BSV_MAP_GT le_bsv1
where le_bsv1.group_id = t1.group_id
and le_bsv1.bal_seg_val = t1.driving_cr_le_bsv))
WHERE intercompany_mode IN (1,2,3)
AND driving_dr_le_bsv <>'Many'
AND driving_cr_le_bsv <>'Many';
UPDATE fun_bal_inter_int2_gt
SET REC_BSV = driving_dr_le_bsv,
PAY_BSV = driving_cr_le_bsv
WHERE intercompany_mode IN (1,2,3)
and driving_dr_le_bsv <> line_le_bsv
and TYPE = 'D';
UPDATE fun_bal_inter_int2_gt
SET REC_BSV = driving_dr_le_bsv,
PAY_BSV = driving_cr_le_bsv
WHERE intercompany_mode IN (1,2,3)
and driving_cr_le_bsv <> line_le_bsv
and TYPE = 'C';
INSERT into fun_bal_inter_int_gt
(Select * from fun_bal_inter_int2_gt
where driving_cr_le_bsv<>'Many'
and driving_dr_le_bsv<>'Many') ;
INSERT
INTO fun_bal_inter_int_gt
(
group_id,
ledger_id,
gl_date,
status,
driving_dr_le_id,
driving_cr_le_id,
intercompany_mode,
le_id,
entered_currency_code,
exchange_date,
exchange_rate,
exchange_rate_type,
accounted_amt_cr,
accounted_amt_dr,
entered_amt_cr,
entered_amt_dr,
bal_seg_column_name,
type,
driving_dr_le_bsv,
driving_cr_le_bsv
)
SELECT upd1.group_id,
upd1.ledger_id,
upd1.gl_date,
upd1.status,
upd1.driving_dr_le_id,
upd1.driving_cr_le_id,
upd1.intercompany_mode,
upd1.le_id,
upd1.entered_currency_code,
upd1.exchange_date,
upd1.exchange_rate,
upd1.exchange_rate_type,
DECODE(SIGN(SUM(NVL(upd1.accounted_amt_cr, 0)) - SUM(NVL(upd1.accounted_amt_dr, 0))), 1, ABS(SUM(NVL(upd1.accounted_amt_cr, 0)) - SUM(NVL(upd1.accounted_amt_dr, 0))), NULL) accounted_amt_cr,
DECODE(SIGN(SUM(NVL(upd1.accounted_amt_cr, 0)) - SUM(NVL(upd1.accounted_amt_dr, 0))), -1, ABS(SUM(NVL(upd1.accounted_amt_cr, 0)) - SUM(NVL(upd1.accounted_amt_dr, 0))), NULL) accounted_amt_dr,
DECODE(SIGN(SUM(NVL(upd1.entered_amt_cr, 0)) - SUM(NVL(upd1.entered_amt_dr, 0))), 1, ABS(SUM(NVL(upd1.entered_amt_cr, 0)) - SUM(NVL(upd1.entered_amt_dr, 0))), NULL) entered_amt_cr,
DECODE(SIGN(SUM(NVL(upd1.entered_amt_cr, 0)) - SUM(NVL(upd1.entered_amt_dr, 0))), -1, ABS(SUM(NVL(upd1.entered_amt_cr, 0)) - SUM(NVL(upd1.entered_amt_dr, 0))), NULL) entered_amt_dr,
upd1.bal_seg_column_name,
DECODE(SIGN(SUM(NVL(upd1.accounted_amt_cr, 0))-SUM(NVL(upd1.accounted_amt_dr,0))), 1, 'C', -1, 'D', 0,
DECODE(SIGN(((SUM(NVL(upd1.entered_amt_cr,0)) - SUM(NVL(upd1.entered_amt_dr,0)))) - (SUM(NVL(upd1.accounted_amt_dr,0)) - SUM(NVL(upd1.accounted_amt_cr,0)))), 1, 'C', 'D')) type,
'Many',
'Many'
FROM fun_bal_inter_int2_gt upd1
WHERE upd1.driving_dr_le_bsv='Many'
AND upd1.driving_cr_le_bsv='Many'
AND upd1.intercompany_mode IN (1,2,3,4)
GROUP BY upd1.group_id,
upd1.ledger_id,
upd1.gl_date,
upd1.status,
upd1.driving_dr_le_id,
upd1.driving_cr_le_id,
upd1.intercompany_mode,
upd1.le_id,
upd1.entered_currency_code,
upd1.exchange_date,
upd1.exchange_rate,
upd1.exchange_rate_type,
upd1.bal_seg_column_name
HAVING SUM(NVL(upd1.accounted_amt_cr, 0)) <> SUM(NVL(upd1.accounted_amt_dr,0))
OR (SUM(NVL(upd1.accounted_amt_cr, 0)) = SUM(NVL(upd1.accounted_amt_dr,0))
AND SUM(DECODE(upd1.exchange_rate, NULL, NVL(upd1.entered_amt_cr, 0), 0)) <> SUM(DECODE(upd1.exchange_rate, NULL, NVL(upd1.entered_amt_dr, 0), 0)));
Update fun_bal_inter_int_gt bal_inter_int
set REC_BSV = (select min_bal_seg_val from (
select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
fun_bal_headers_gt hdrs
WHERE hdrs.group_id = lines.group_id
AND lines.group_id = le_bsv_map.group_id
AND lines.bal_seg_val = le_bsv_map.bal_seg_val
AND hdrs.intercompany_mode IN (1,2,3)
AND hdrs.status = 'OK'
GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
hdrs.intercompany_mode, le_bsv_map.le_id,
hdrs.bal_seg_column_name, hdrs.intercompany_column_number
HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))) ) min_bsv
where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_CR_LE_ID,
2, (decode (bal_inter_int.type, 'C', bal_inter_int.LE_ID, bal_inter_int.DRIVING_DR_LE_ID)),
3, (decode (bal_inter_int.type, 'D', bal_inter_int.DRIVING_CR_LE_ID, bal_inter_int.LE_ID)), NULL)
and min_bsv.group_id = bal_inter_int.group_id
and bal_inter_int.status = 'OK')
where bal_inter_int.driving_dr_le_bsv='Many'
AND bal_inter_int.driving_cr_le_bsv='Many';
Update fun_bal_inter_int_gt bal_inter_int
set PAY_BSV = (select min_bal_seg_val from (
select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
fun_bal_headers_gt hdrs
WHERE hdrs.group_id = lines.group_id
AND lines.group_id = le_bsv_map.group_id
AND lines.bal_seg_val = le_bsv_map.bal_seg_val
AND hdrs.intercompany_mode IN (1,2,3)
AND hdrs.status = 'OK'
GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
hdrs.intercompany_mode, le_bsv_map.le_id,
hdrs.bal_seg_column_name, hdrs.intercompany_column_number
HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))) ) min_bsv
where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_DR_LE_ID,
2, (decode (bal_inter_int.type, 'C', bal_inter_int.DRIVING_DR_LE_ID, bal_inter_int.LE_ID)),
3, (decode (bal_inter_int.type, 'D', bal_inter_int.LE_ID, bal_inter_int.DRIVING_CR_LE_ID)), NULL)
and min_bsv.group_id = bal_inter_int.group_id
and bal_inter_int.status = 'OK')
where bal_inter_int.driving_dr_le_bsv='Many'
AND bal_inter_int.driving_cr_le_bsv='Many';
Update fun_bal_inter_int_gt bal_inter_int
set driving_cr_le_bsv = REC_BSV,
driving_dr_le_bsv = PAY_BSV ,
LINE_LE_BSV = decode(TYPE, 'D',PAY_BSV,REC_BSV)
where driving_dr_le_bsv='Many'
AND driving_cr_le_bsv='Many'
and group_id in (select group_id from fun_bal_headers_gt where intercompany_mode IN (1,2,3));
INSERT into fun_bal_inter_int_gt (GROUP_ID,
LEDGER_ID,
GL_DATE,
STATUS,
DRIVING_DR_LE_ID,
DRIVING_CR_LE_ID,
INTERCOMPANY_MODE,
LE_ID,
ENTERED_CURRENCY_CODE,
ACCOUNTED_AMT_CR,
ACCOUNTED_AMT_DR,
ENTERED_AMT_CR,
ENTERED_AMT_DR,
REC_ACCT,
PAY_ACCT,
BAL_SEG_COLUMN_NAME,
REC_BSV,
PAY_BSV,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
TYPE,
DRIVING_DR_LE_BSV,
DRIVING_CR_LE_BSV,
LINE_LE_BSV)
Select upd1.GROUP_ID,
upd1.LEDGER_ID,
upd1.GL_DATE,
upd1.STATUS,
upd1.DRIVING_DR_LE_ID,
upd1.DRIVING_CR_LE_ID,
upd1.INTERCOMPANY_MODE,
upd2.LE_ID,
upd1.ENTERED_CURRENCY_CODE,
upd1.ACCOUNTED_AMT_DR,
upd1.ACCOUNTED_AMT_CR,
upd1.ENTERED_AMT_DR,
upd1.ENTERED_AMT_CR,
upd1.REC_ACCT,
upd1.PAY_ACCT,
upd1.BAL_SEG_COLUMN_NAME,
upd1.REC_BSV,
upd1.PAY_BSV,
upd1.EXCHANGE_DATE,
upd1.EXCHANGE_RATE,
upd1.EXCHANGE_RATE_TYPE,
DECODE(upd1.TYPE,'C','D','C'),
upd1.DRIVING_DR_LE_BSV,
upd1.DRIVING_CR_LE_BSV,
upd2.LINE_LE_BSV
from fun_bal_inter_int2_gt upd1,
fun_bal_inter_int2_gt upd2
where upd2.group_id=upd1.group_id
AND upd1.intercompany_mode in (1,2,3)
AND upd2.intercompany_mode in (1,2,3)
AND NVL(upd1.entered_currency_code,'0')= NVL(upd2.entered_currency_code,'0')
AND ((upd2.driving_dr_le_bsv='Many' and upd2.driving_cr_le_bsv<>'Many' and upd1.driving_dr_le_bsv<>upd2.driving_dr_le_bsv)
or (upd2.driving_cr_le_bsv='Many' and upd2.driving_dr_le_bsv<>'Many' and upd1.driving_cr_le_bsv<>upd2.driving_cr_le_bsv)
);
UPDATE fun_bal_inter_int_gt inter_int
SET rec_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
NULL) = accts.from_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
NULL) = accts.to_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_bsv ,NULL),
DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL)),
2, DECODE(inter_int.type, 'C', line_le_bsv, driving_dr_le_bsv),
3, DECODE(inter_int.type, 'C', line_le_bsv,driving_cr_le_bsv),
NULL) = accts.trans_bsv -- From BSV
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL),
DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL)),
2, DECODE(inter_int.type, 'C', driving_dr_le_bsv,line_le_bsv),
3, DECODE(inter_int.type, 'C', driving_cr_le_bsv,line_le_bsv),
NULL) = accts.tp_bsv -- To BSV
AND accts.type = 'R'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.intercompany_mode IN (1,2,3)
AND driving_dr_le_bsv <> 'Many'
AND driving_cr_le_bsv <> 'Many';
UPDATE fun_bal_inter_int_gt inter_int
SET rec_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
NULL) = accts.from_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
NULL) = accts.to_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL),
DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL)),
2, DECODE(inter_int.type, 'C', line_le_bsv,driving_dr_le_bsv),
3, DECODE(inter_int.type, 'C', line_le_bsv,driving_cr_le_bsv),
NULL) = accts.trans_bsv -- From BSV
AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
AND accts.type = 'R'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.intercompany_mode IN (1,2,3)
AND inter_int.rec_acct IS NULL;
UPDATE fun_bal_inter_int_gt inter_int
SET rec_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
NULL) = accts.from_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
NULL) = accts.to_le_id
AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL),
DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL)),
2, DECODE(inter_int.type, 'C', driving_dr_le_bsv,line_le_bsv),
3, DECODE(inter_int.type, 'C', driving_cr_le_bsv,line_le_bsv),
NULL) = accts.tp_bsv -- To BSV
AND accts.type = 'R'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.intercompany_mode IN (1,2,3)
AND inter_int.rec_acct IS NULL;
UPDATE fun_bal_inter_int_gt inter_int
SET rec_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
NULL) = accts.from_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
NULL) = accts.to_le_id
AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
AND accts.type = 'R'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.intercompany_mode IN (1,2,3)
AND inter_int.rec_acct IS NULL;
Update Fun_bal_inter_int_gt Inter_int
Set Rec_acct = (Select Ccid
From fun_inter_accounts Accts
Where Inter_int.Ledger_id = Accts.Ledger_id
And Inter_int.Rec_acct Is Null
And Decode(Inter_int.Intercompany_mode, 1,
Decode(Le_id, Driving_cr_le_id,
Decode(Inter_int.Type, 'C', Driving_cr_le_id, Null),
Decode(Inter_int.Type, 'C', Driving_dr_le_id, Null)), 2,
Decode (Inter_int.Type, 'C', Le_id, Driving_dr_le_id), 3,
Decode(Inter_int.Type, 'C', Le_id, Driving_cr_le_id), 4,
Decode(Inter_int.Type, 'C', Le_id, Null), Null) =Accts.From_le_id
And Accts.To_le_id = -99
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL),
DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL)),
2, DECODE(inter_int.type, 'C', line_le_bsv,driving_dr_le_bsv),
3, DECODE(inter_int.type, 'C', line_le_bsv,driving_cr_le_bsv),
NULL) = accts.trans_bsv -- From BSV
AND 'OTHER1234567890123456789012345' = accts.tp_bsv
And Accts.Type = 'R'
And Accts.Default_flag = 'Y'
And (Trunc(Inter_int.Gl_date) Between Trunc(Nvl(Accts.Start_date, Inter_int.Gl_date))
And Trunc(Nvl(Accts.End_date, Inter_int.Gl_date))))
Where Inter_int.Rec_acct Is Null ;
Update Fun_bal_inter_int_gt Inter_int
Set Rec_acct = (Select Ccid
From fun_inter_accounts Accts
Where Inter_int.Ledger_id = Accts.Ledger_id
And Inter_int.Rec_acct Is Null
And Decode(Inter_int.Intercompany_mode, 1,
Decode(Le_id, Driving_cr_le_id,
Decode(Inter_int.Type, 'C', Driving_cr_le_id, Null),
Decode(Inter_int.Type, 'C', Driving_dr_le_id, Null)), 2,
Decode (Inter_int.Type, 'C', Le_id, Driving_dr_le_id), 3,
Decode(Inter_int.Type, 'C', Le_id, Driving_cr_le_id), 4,
Decode(Inter_int.Type, 'C', Le_id, Null), Null) =Accts.From_le_id
And Accts.To_le_id = -99
AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
AND 'OTHER1234567890123456789012345' = accts.tp_bsv
And Accts.Type = 'R'
And Accts.Default_flag = 'Y'
And (Trunc(Inter_int.Gl_date) Between Trunc(Nvl(Accts.Start_date, Inter_int.Gl_date))
And Trunc(Nvl(Accts.End_date, Inter_int.Gl_date))))
Where Inter_int.Rec_acct Is Null ;
UPDATE fun_bal_inter_int_gt inter_int
SET rec_acct = (SELECT dr_ccid
FROM fun_balance_accounts accts
WHERE
'OTHER1234567890123456789012345' = accts.cr_bsv
AND 'OTHER1234567890123456789012345' = accts.dr_bsv
AND accts.template_id = (SELECT NVL((SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = inter_int.ledger_id
AND opts.le_id = DECODE(inter_int.intercompany_mode,
1, DECODE(inter_int.le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
NULL)
AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
where le_bsv_map.group_id = inter_int.group_id)
AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
where le_bsv_map.group_id = inter_int.group_id)
AND opts.status_flag = 'Y'),
NVL((SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = inter_int.ledger_id
AND opts.le_id = DECODE(inter_int.intercompany_mode,
1, DECODE(inter_int.le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
NULL)
AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
where le_bsv_map.group_id = inter_int.group_id)
AND opts.je_category_name = 'Other'
AND opts.status_flag = 'Y'),
NVL((SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = inter_int.ledger_id
AND opts.le_id = DECODE(inter_int.intercompany_mode,
1, DECODE(inter_int.le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
NULL)
AND opts.je_source_name = 'Other'
AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
where le_bsv_map.group_id = inter_int.group_id)
AND opts.status_flag = 'Y'),
(SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = inter_int.ledger_id
AND opts.le_id = DECODE(inter_int.intercompany_mode,
1, DECODE(inter_int.le_id, driving_cr_le_id,
DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
NULL)
AND opts.je_source_name = 'Other'
AND opts.je_category_name = 'Other'
AND opts.status_flag = 'Y')))) template_id
From Dual))
WHERE inter_int.rec_acct IS NULL and fun_trx_pvt.get_inter_intra()='Y';
UPDATE fun_bal_inter_int_gt inter_int
SET pay_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
NULL) = accts.from_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
NULL) = accts.to_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id,driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_bsv,NULL),
DECODE(inter_int.type, 'D', driving_cr_le_bsv, NULL)),
2, DECODE(inter_int.type, 'D', line_le_bsv,driving_dr_le_bsv),
3, DECODE(inter_int.type, 'D', line_le_bsv,driving_cr_le_bsv),
NULL) = accts.trans_bsv
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id,driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_cr_le_bsv,NULL),
DECODE(inter_int.type, 'D', driving_dr_le_bsv, NULL)),
2, DECODE(inter_int.type, 'D', driving_dr_le_bsv,line_le_bsv),
3, DECODE(inter_int.type, 'D', driving_cr_le_bsv,line_le_bsv),
NULL) = accts.tp_bsv -- To BSV
AND accts.type = 'P'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.intercompany_mode IN (1,2,3)
AND driving_cr_le_bsv <> 'Many'
AND driving_dr_le_bsv <> 'Many';
UPDATE fun_bal_inter_int_gt inter_int
SET pay_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
NULL) = accts.from_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
NULL) = accts.to_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id,driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_bsv,NULL),
DECODE(inter_int.type, 'D', driving_cr_le_bsv, NULL)),
2, DECODE(inter_int.type, 'D', line_le_bsv,driving_dr_le_bsv),
3, DECODE(inter_int.type, 'D', line_le_bsv,driving_cr_le_bsv),
NULL) = accts.trans_bsv
AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
AND accts.type = 'P'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.intercompany_mode IN (1,2,3)
AND inter_int.pay_acct IS NULL;
UPDATE fun_bal_inter_int_gt inter_int
SET pay_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
NULL) = accts.from_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
NULL) = accts.to_le_id
AND 'OTHER1234567890123456789012345' = accts.trans_bsv
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id,driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_cr_le_bsv,NULL),
DECODE(inter_int.type, 'D', driving_dr_le_bsv, NULL)),
2, DECODE(inter_int.type, 'D', driving_dr_le_bsv,line_le_bsv),
3, DECODE(inter_int.type, 'D', driving_cr_le_bsv,line_le_bsv),
NULL) = accts.tp_bsv -- To BSV
AND accts.type = 'P'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.intercompany_mode IN (1,2,3)
AND inter_int.pay_acct IS NULL ;
UPDATE fun_bal_inter_int_gt inter_int
SET pay_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
NULL) = accts.from_le_id
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
NULL) = accts.to_le_id
AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
AND accts.type = 'P'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date)) AND
TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.intercompany_mode IN (1,2,3)
AND inter_int.pay_acct IS NULL;
UPDATE fun_bal_inter_int_gt inter_int
SET pay_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND inter_int.pay_acct IS NULL
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'D', le_id, NULL),
NULL) = accts.from_le_id
AND accts.to_le_id = -99 -- To LE "All Other"
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id,driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_bsv,NULL),
DECODE(inter_int.type, 'D', driving_cr_le_bsv, NULL)),
2, DECODE(inter_int.type, 'D', line_le_bsv,driving_dr_le_bsv),
3, DECODE(inter_int.type, 'D', line_le_bsv,driving_cr_le_bsv),
NULL) = accts.trans_bsv
AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
AND accts.type = 'P'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date)) AND
TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.pay_acct IS NULL;
UPDATE fun_bal_inter_int_gt inter_int
SET pay_acct =
(SELECT ccid
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND inter_int.pay_acct IS NULL
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'D', le_id, NULL),
NULL) = accts.from_le_id
AND accts.to_le_id = -99 -- To LE "All Other"
AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
AND accts.type = 'P'
AND accts.default_flag = 'Y'
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date)) AND
TRUNC(NVL(accts.end_date, inter_int.gl_date))))
WHERE inter_int.pay_acct IS NULL;
UPDATE fun_bal_inter_int_gt inter_int
SET pay_acct = (SELECT cr_ccid
FROM fun_balance_accounts accts
WHERE
'OTHER1234567890123456789012345' = accts.cr_bsv
AND 'OTHER1234567890123456789012345' = accts.dr_bsv
AND accts.template_id = (SELECT NVL((SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = inter_int.ledger_id
AND opts.le_id = DECODE(inter_int.intercompany_mode,
1, DECODE(inter_int.le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
NULL)
AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
where le_bsv_map.group_id = inter_int.group_id)
AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
where le_bsv_map.group_id = inter_int.group_id)
AND opts.status_flag = 'Y'),
NVL((SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = inter_int.ledger_id
AND opts.le_id = DECODE(inter_int.intercompany_mode,
1, DECODE(inter_int.le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
NULL)
AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
where le_bsv_map.group_id = inter_int.group_id)
AND opts.je_category_name = 'Other'
AND opts.status_flag = 'Y'),
NVL((SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = inter_int.ledger_id
AND opts.le_id = DECODE(inter_int.intercompany_mode,
1, DECODE(inter_int.le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
NULL)
AND opts.je_source_name = 'Other'
AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
where le_bsv_map.group_id = inter_int.group_id)
AND opts.status_flag = 'Y'),
(SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = inter_int.ledger_id
AND opts.le_id = DECODE(inter_int.intercompany_mode,
1, DECODE(inter_int.le_id, driving_dr_le_id,
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
NULL)
AND opts.je_source_name = 'Other'
AND opts.je_category_name = 'Other'
AND opts.status_flag = 'Y')))) template_id
From Dual))
WHERE inter_int.pay_acct IS NULL and fun_trx_pvt.get_inter_intra()='Y';
UPDATE fun_bal_inter_int_gt inter_int
SET rec_acct = -1
WHERE rec_acct IS NULL AND
EXISTS (SELECT 'Receivables Accounts exist but not defaulted'
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND accts.type = 'R'
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id,
driving_cr_le_id, DECODE(inter_int.type, 'C', driving_cr_le_id,
NULL),
DECODE(inter_int.type, 'C', driving_dr_le_id,
NULL)),
2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'C', le_id, NULL),
NULL) = accts.from_le_id
AND (DECODE(inter_int.intercompany_mode,
1, DECODE(le_id,
driving_cr_le_id, DECODE(inter_int.type, 'C', driving_dr_le_id,
NULL),
DECODE(inter_int.type, 'C', driving_cr_le_id,
NULL)),
2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
NULL) = accts.to_le_id
OR
accts.to_le_id = -99));
UPDATE fun_bal_inter_int_gt inter_int
SET pay_acct = -1
WHERE pay_acct IS NULL AND
EXISTS (SELECT 'Payables Accounts exist but not defaulted'
FROM fun_inter_accounts accts
WHERE inter_int.ledger_id = accts.ledger_id
AND accts.type = 'P'
AND DECODE(inter_int.intercompany_mode,
1, DECODE(le_id,
driving_dr_le_id, DECODE(inter_int.type, 'D', driving_dr_le_id,
NULL),
DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
4, DECODE(inter_int.type, 'C', le_id, NULL),
NULL) = accts.from_le_id
AND (DECODE(inter_int.intercompany_mode,
1, DECODE(le_id,
driving_dr_le_id, DECODE(inter_int.type, 'D', driving_cr_le_id,
NULL),
DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
3, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
NULL) = accts.to_le_id
OR
accts.to_le_id = -99));
INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
acct_type, ccid_concat_display,
dr_bsv, cr_bsv)
SELECT DISTINCT DECODE(inter_int.rec_acct, NULL, 'FUN_INTER_REC_NOT_ASSIGNED',
-1, 'FUN_INTER_REC_NO_DEFAULT',
'FUN_INTER_REC_NOT_VALID'),
inter_int.group_id,
DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
2, inter_int.le_id,
3, inter_int.driving_cr_le_id,
4, inter_int.le_id),
DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
2, inter_int.driving_dr_le_id,
3, inter_int.le_id,
4, NULL),
DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), 'R',
get_ccid_concat_disp(DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), hdrs.chart_of_accounts_id,
inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv, bal_seg_column_number, intercompany_column_number),
inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
WHERE inter_int.group_id = hdrs.group_id AND
((inter_int.intercompany_mode = 1 AND
inter_int.type = 'C')
OR
(inter_int.intercompany_mode = 2 AND
inter_int.le_id <> inter_int.driving_dr_le_id)
OR
(inter_int.intercompany_mode = 3 AND
inter_int.le_id <> inter_int.driving_cr_le_id)
OR
(inter_int.intercompany_mode = 4 AND
inter_int.type = 'C'))
AND (inter_int.rec_acct IS NULL
OR
inter_int.rec_acct = -1
OR
(inter_int.rec_acct IS NOT NULL AND
NOT EXISTS (SELECT 'Receivables account not valid'
FROM gl_code_combinations cc
WHERE inter_int.rec_acct = cc.code_combination_id
AND cc.detail_posting_allowed_flag = 'Y'
AND cc.enabled_flag = 'Y'
AND cc.summary_flag = 'N'
AND nvl(cc.reference3, 'N') IN ('N', 'R')
AND cc.template_id IS NULL
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
acct_type, ccid_concat_display,
dr_bsv, cr_bsv)
SELECT DISTINCT DECODE(inter_int.pay_acct, NULL, 'FUN_INTER_PAY_NOT_ASSIGNED',
-1, 'FUN_INTER_PAY_NO_DEFAULT',
'FUN_INTER_PAY_NOT_VALID'),
inter_int.group_id,
DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
2, inter_int.driving_dr_le_id,
3, inter_int.le_id,
4, inter_int.le_id),
DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
2, inter_int.le_id,
3, inter_int.driving_cr_le_id,
4, NULL),
DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), 'P',
get_ccid_concat_disp(DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), hdrs.chart_of_accounts_id,
inter_int.driving_cr_le_bsv, inter_int.driving_dr_le_bsv, bal_seg_column_number, intercompany_column_number),
inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
WHERE inter_int.group_id = hdrs.group_id AND
((inter_int.intercompany_mode = 1 AND
inter_int.type = 'D')
OR
(inter_int.intercompany_mode = 2 AND
inter_int.le_id <> inter_int.driving_dr_le_id)
OR
(inter_int.intercompany_mode = 3 AND
inter_int.le_id <> inter_int.driving_cr_le_id)
OR
(inter_int.intercompany_mode = 4 AND
inter_int.type = 'D'))
AND (inter_int.pay_acct IS NULL
OR
inter_int.pay_acct = -1
OR
(inter_int.pay_acct IS NOT NULL AND
NOT EXISTS (SELECT 'Payables account not valid'
FROM gl_code_combinations cc
WHERE inter_int.pay_acct = cc.code_combination_id
AND cc.detail_posting_allowed_flag = 'Y'
AND cc.enabled_flag = 'Y'
AND cc.summary_flag = 'N'
AND nvl(cc.reference3, 'N') IN ('N', 'R')
AND cc.template_id IS NULL
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
UPDATE fun_bal_headers_gt headers
SET status = 'ERROR'
WHERE EXISTS (SELECT 'Errors for Rec and Pay Accts'
FROM FUN_BAL_ERRORS_GT errors
WHERE headers.group_id = errors.group_id
AND error_code IN ('FUN_INTER_PAY_NOT_ASSIGNED',
'FUN_INTER_REC_NOT_ASSIGNED',
'FUN_INTER_PAY_NO_DEFAULT',
'FUN_INTER_REC_NO_DEFAULT',
'FUN_INTER_PAY_NOT_VALID',
'FUN_INTER_REC_NOT_VALID'))
AND headers.status = 'OK';
DELETE FROM fun_bal_inter_int_gt inter_int
WHERE EXISTS (SELECT group_id
FROM fun_bal_headers_gt headers
WHERE headers.status = 'ERROR'
AND inter_int.group_id = headers.group_id);
-- Update the Payable and receivable BSV with the minimum unbalanced bsv
-- for each of the transacting Legal Entity.
--bug: 9008776
Update fun_bal_inter_int_gt bal_inter_int
set Rec_BSV = (select min_bal_seg_val from (
select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
fun_bal_headers_gt hdrs
WHERE hdrs.group_id = lines.group_id
AND lines.group_id = le_bsv_map.group_id
AND lines.bal_seg_val = le_bsv_map.bal_seg_val
AND hdrs.intercompany_mode IN (1,2,3)
AND hdrs.status = 'OK'
GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
hdrs.intercompany_mode, le_bsv_map.le_id,
hdrs.bal_seg_column_name, hdrs.intercompany_column_number
HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))) ) min_bsv
where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_CR_LE_ID,
2, (decode (bal_inter_int.type, 'C', bal_inter_int.LE_ID, bal_inter_int.DRIVING_DR_LE_ID)),
3, (decode (bal_inter_int.type, 'D', bal_inter_int.DRIVING_CR_LE_ID, bal_inter_int.LE_ID)), NULL)
and min_bsv.group_id = bal_inter_int.group_id
and bal_inter_int.status = 'OK')
where REC_BSV IS NULL;
Update fun_bal_inter_int_gt bal_inter_int
set Pay_BSV = (select min_bal_seg_val from (
select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
fun_bal_headers_gt hdrs
WHERE hdrs.group_id = lines.group_id
AND lines.group_id = le_bsv_map.group_id
AND lines.bal_seg_val = le_bsv_map.bal_seg_val
AND hdrs.intercompany_mode IN (1,2,3)
AND hdrs.status = 'OK'
GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
hdrs.intercompany_mode, le_bsv_map.le_id,
hdrs.bal_seg_column_name, hdrs.intercompany_column_number
HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))) ) min_bsv
where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_DR_LE_ID,
2, (decode (bal_inter_int.type, 'C', bal_inter_int.DRIVING_DR_LE_ID, bal_inter_int.LE_ID)),
3, (decode (bal_inter_int.type, 'D', bal_inter_int.LE_ID, bal_inter_int.DRIVING_CR_LE_ID)), NULL)
and min_bsv.group_id = bal_inter_int.group_id
and bal_inter_int.status = 'OK')
where Pay_BSV IS NULL;
-- Payables and Receivables accounts. And update the table with
-- the new account numbers.
Update fun_bal_inter_int_gt bal_inter_int
Set (REC_ACCT, PAY_ACCT) =
(select get_ccid (bal_inter_int.REC_ACCT,
hdrs.CHART_OF_ACCOUNTS_ID,
bal_inter_int.REC_BSV,
bal_inter_int.PAY_BSV,
hdrs.BAL_SEG_COLUMN_NUMBER,
hdrs.INTERCOMPANY_COLUMN_NUMBER,
bal_inter_int.GL_DATE
),
get_ccid (bal_inter_int.PAY_ACCT,
hdrs.CHART_OF_ACCOUNTS_ID,
bal_inter_int.PAY_BSV,
bal_inter_int.REC_BSV,
hdrs.BAL_SEG_COLUMN_NUMBER,
hdrs.INTERCOMPANY_COLUMN_NUMBER,
bal_inter_int.GL_DATE
)
from fun_bal_headers_gt hdrs
where bal_inter_int.group_id = hdrs.group_id)
where bal_inter_int.intercompany_mode in (1, 2, 3)
and bal_inter_int.status = 'OK';
INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
acct_type, ccid_concat_display,
dr_bsv, cr_bsv)
SELECT DISTINCT DECODE(inter_int.rec_acct, NULL, 'FUN_INTER_REC_NOT_ASSIGNED',
-1, 'FUN_INTER_REC_NO_DEFAULT',
'FUN_INTER_REC_NOT_VALID'),
inter_int.group_id,
DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
2, inter_int.le_id,
3, inter_int.driving_cr_le_id,
4, inter_int.le_id),
DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
2, inter_int.driving_dr_le_id,
3, inter_int.le_id,
4, NULL),
DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), 'R',
get_ccid_concat_disp(DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), hdrs.chart_of_accounts_id,
inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv, bal_seg_column_number, intercompany_column_number),
inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
WHERE inter_int.group_id = hdrs.group_id AND
((inter_int.intercompany_mode = 1 AND
inter_int.type = 'C')
OR
(inter_int.intercompany_mode = 2 AND
inter_int.le_id <> inter_int.driving_dr_le_id)
OR
(inter_int.intercompany_mode = 3 AND
inter_int.le_id <> inter_int.driving_cr_le_id)
OR
(inter_int.intercompany_mode = 4 AND
inter_int.type = 'C'))
AND (inter_int.rec_acct IS NULL
OR
inter_int.rec_acct = -1
OR
(inter_int.rec_acct IS NOT NULL AND
NOT EXISTS (SELECT 'Receivables account not valid'
FROM gl_code_combinations cc
WHERE inter_int.rec_acct = cc.code_combination_id
AND cc.detail_posting_allowed_flag = 'Y'
AND cc.enabled_flag = 'Y'
AND cc.summary_flag = 'N'
AND nvl(cc.reference3, 'N') IN ('N', 'R')
AND cc.template_id IS NULL
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
acct_type, ccid_concat_display,
dr_bsv, cr_bsv)
SELECT DISTINCT DECODE(inter_int.pay_acct, NULL, 'FUN_INTER_PAY_NOT_ASSIGNED',
-1, 'FUN_INTER_PAY_NO_DEFAULT',
'FUN_INTER_PAY_NOT_VALID'),
inter_int.group_id,
DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
2, inter_int.driving_dr_le_id,
3, inter_int.le_id,
4, inter_int.le_id),
DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
2, inter_int.le_id,
3, inter_int.driving_cr_le_id,
4, NULL),
DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), 'P',
get_ccid_concat_disp(DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), hdrs.chart_of_accounts_id,
inter_int.driving_cr_le_bsv, inter_int.driving_dr_le_bsv, bal_seg_column_number, intercompany_column_number),
inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
WHERE inter_int.group_id = hdrs.group_id AND
((inter_int.intercompany_mode = 1 AND
inter_int.type = 'D')
OR
(inter_int.intercompany_mode = 2 AND
inter_int.le_id <> inter_int.driving_dr_le_id)
OR
(inter_int.intercompany_mode = 3 AND
inter_int.le_id <> inter_int.driving_cr_le_id)
OR
(inter_int.intercompany_mode = 4 AND
inter_int.type = 'D'))
AND (inter_int.pay_acct IS NULL
OR
inter_int.pay_acct = -1
OR
(inter_int.pay_acct IS NOT NULL AND
NOT EXISTS (SELECT 'Payables account not valid'
FROM gl_code_combinations cc
WHERE inter_int.pay_acct = cc.code_combination_id
AND cc.detail_posting_allowed_flag = 'Y'
AND cc.enabled_flag = 'Y'
AND cc.summary_flag = 'N'
AND nvl(cc.reference3, 'N') IN ('N', 'R')
AND cc.template_id IS NULL
AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
UPDATE fun_bal_headers_gt headers
SET status = 'ERROR'
WHERE EXISTS (SELECT 'Errors for Rec and Pay Accts'
FROM FUN_BAL_ERRORS_GT errors
WHERE headers.group_id = errors.group_id
AND error_code IN ('FUN_INTER_PAY_NOT_ASSIGNED',
'FUN_INTER_REC_NOT_ASSIGNED',
'FUN_INTER_PAY_NO_DEFAULT',
'FUN_INTER_REC_NO_DEFAULT',
'FUN_INTER_PAY_NOT_VALID',
'FUN_INTER_REC_NOT_VALID'))
AND headers.status = 'OK';
DELETE FROM fun_bal_inter_int_gt inter_int
WHERE EXISTS (SELECT group_id
FROM fun_bal_headers_gt headers
WHERE headers.status = 'ERROR'
AND inter_int.group_id = headers.group_id);
update_inter_seg_val;
INSERT INTO FUN_INTER_ACCOUNTS_ADDL
(FROM_LE_ID,
LEDGER_ID,
TO_LE_ID,
CCID,
TYPE,
START_DATE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRANS_BSV,
TP_BSV)
SELECT DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
BAL_INTER_INT.DRIVING_DR_LE_ID)),
3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
BAL_INTER_INT.LE_ID)),
NULL),
BAL_INTER_INT.LEDGER_ID,
DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
BAL_INTER_INT.LE_ID)),
3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
BAL_INTER_INT.DRIVING_CR_LE_ID)),
NULL),
BAL_INTER_INT.REC_ACCT,
'R',
SYSDATE,
'1',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
fnd_global.login_id,
BAL_INTER_INT.REC_BSV,
BAL_INTER_INT.PAY_BSV
FROM FUN_BAL_INTER_INT_GT BAL_INTER_INT
WHERE BAL_INTER_INT.STATUS = 'OK'
AND BAL_INTER_INT.REC_ACCT IS NOT NULL
AND BAL_INTER_INT.PAY_BSV IS NOT NULL
AND BAL_INTER_INT.REC_BSV IS NOT NULL
AND NOT EXISTS(
SELECT 'X'
FROM FUN_INTER_ACCOUNTS_V ACCTV
WHERE ACCTV.FROM_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
BAL_INTER_INT.DRIVING_DR_LE_ID)),
3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
BAL_INTER_INT.LE_ID)),
NULL)
AND ACCTV.LEDGER_ID = BAL_INTER_INT.LEDGER_ID
AND ACCTV.TO_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
BAL_INTER_INT.LE_ID)),
3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
BAL_INTER_INT.DRIVING_CR_LE_ID)),
NULL)
AND ACCTV.CCID = BAL_INTER_INT.REC_ACCT
AND ACCTV.TYPE = 'R'
AND ACCTV.TRANS_BSV = BAL_INTER_INT.REC_BSV
AND ACCTV.TP_BSV = BAL_INTER_INT.PAY_BSV
);
INSERT INTO FUN_INTER_ACCOUNTS_ADDL
(FROM_LE_ID,
LEDGER_ID,
TO_LE_ID,
CCID,
TYPE,
START_DATE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TRANS_BSV,
TP_BSV)
SELECT DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
BAL_INTER_INT.LE_ID)),
3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
BAL_INTER_INT.DRIVING_CR_LE_ID)),
NULL),
BAL_INTER_INT.LEDGER_ID,
DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
BAL_INTER_INT.DRIVING_DR_LE_ID)),
3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
BAL_INTER_INT.LE_ID)),
NULL),
BAL_INTER_INT.PAY_ACCT,
'P',
SYSDATE,
'1',
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
fnd_global.login_id,
BAL_INTER_INT.PAY_BSV,
BAL_INTER_INT.REC_BSV
FROM FUN_BAL_INTER_INT_GT BAL_INTER_INT
WHERE BAL_INTER_INT.STATUS = 'OK'
AND BAL_INTER_INT.PAY_ACCT IS NOT NULL
AND BAL_INTER_INT.PAY_BSV IS NOT NULL
AND BAL_INTER_INT.REC_BSV IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM FUN_INTER_ACCOUNTS_V ACCTV
WHERE ACCTV.FROM_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
BAL_INTER_INT.LE_ID)),
3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
BAL_INTER_INT.DRIVING_CR_LE_ID)),
NULL)
AND ACCTV.LEDGER_ID = BAL_INTER_INT.LEDGER_ID
AND ACCTV.TO_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
BAL_INTER_INT.DRIVING_DR_LE_ID)),
3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
BAL_INTER_INT.LE_ID)),
NULL)
AND ACCTV.CCID = BAL_INTER_INT.PAY_ACCT
AND ACCTV.TYPE = 'P'
AND ACCTV.TRANS_BSV = BAL_INTER_INT.PAY_BSV
AND ACCTV.TP_BSV = BAL_INTER_INT.REC_BSV
);
INSERT INTO fun_bal_lines_gt lines (group_id, bal_seg_val, entered_amt_dr,
entered_amt_cr, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
accounted_amt_dr, accounted_amt_cr, ccid, generated)
SELECT sum_lines.group_id,
DECODE(gen.value, 'D', sum_lines.rec_bsv,
'C', sum_lines.pay_bsv,
NULL),
DECODE(gen.value, 'D', DECODE(sum_lines.type, 'C', sum_lines.entered_amt_cr,
'D', sum_lines.entered_amt_dr),
NULL),
DECODE(gen.value, 'C', DECODE(sum_lines.type, 'C', sum_lines.entered_amt_cr,
'D', sum_lines.entered_amt_dr),
NULL),
sum_lines.entered_currency_code,
sum_lines.exchange_date, sum_lines.exchange_rate, sum_lines.exchange_rate_type,
DECODE(gen.value, 'D', DECODE(sum_lines.type, 'C', sum_lines.accounted_amt_cr,
'D', sum_lines.accounted_amt_dr),
NULL),
DECODE(gen.value, 'C', DECODE(sum_lines.type, 'C', sum_lines.accounted_amt_cr,
'D', sum_lines.accounted_amt_dr),
NULL),
DECODE(gen.value, 'C', sum_lines.pay_acct, 'D', sum_lines.rec_acct, NULL),
'Y'
FROM fun_bal_inter_int_gt sum_lines, fun_bal_generate_lines gen
WHERE gen.value = DECODE(sum_lines.intercompany_mode,
1, DECODE(sum_lines.type, gen.value, 'X', gen.value),
2, DECODE(sum_lines.le_id, sum_lines.driving_dr_le_id, 'X', gen.value),
3, DECODE(sum_lines.le_id, sum_lines.driving_cr_le_id, 'X', gen.value),
4, DECODE(sum_lines.type, gen.value, 'X', gen.value));
SELECT * FROM fun_bal_le_bsv_map_gt;
SELECT * FROM fun_bal_intra_int_gt;
DELETE FROM fun_bal_le_bsv_map_gt;
INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date,
je_source_name, je_category_name, clearing_bsv,
chart_of_accounts_id, bal_seg_column_number,intercompany_column_number)
SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date,
hdrs.je_source_name, hdrs.je_category_name, hdrs.clearing_bsv,
hdrs.chart_of_accounts_id, hdrs.bal_seg_column_number, hdrs.intercompany_column_number
FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines
WHERE hdrs.group_id = lines.group_id
AND hdrs.status = 'OK';
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.insert_le_bsv_map.finish', 'finish');
UPDATE fun_bal_le_bsv_map_gt bsv_le_map
SET le_id =
NVL((SELECT vals.legal_entity_id
FROM gl_ledger_le_bsv_specific_v vals
WHERE bsv_le_map.bal_seg_val = vals.segment_value
AND (TRUNC(bsv_le_map.gl_date) BETWEEN TRUNC(NVL(vals.start_date, bsv_le_map.gl_date)) AND
TRUNC(NVL(vals.end_date, bsv_le_map.gl_date)))
AND bsv_le_map.ledger_id = vals.ledger_id
), -99);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_le.finish', 'finish');
UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
SET (driving_dr_bsv, intracompany_mode) =
(SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
MIN(le_bsv_map.bal_seg_val), SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val)))
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
WHERE le_bsv_map.group_id = lines.group_id
AND le_bsv_map.bal_seg_val = lines.bal_seg_val
AND le_bsv_map.group_id = le_bsv_map_upd.group_id
AND le_bsv_map.le_id = le_bsv_map_upd.le_id
AND LE_BSV_MAP_UPD.LEDGER_ID = LE_BSV_MAP.LEDGER_ID
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id, le_bsv_map.bal_seg_val
HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) >
SUM(NVL(lines.accounted_amt_cr, 0)))
OR
((SUM(NVL(lines.accounted_amt_dr, 0)) =
SUM(NVL(lines.accounted_amt_cr, 0))) AND
(SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) >
SUM(DECODE(lines.exchange_rate, NULL,NVL(lines.entered_amt_cr,0), 0)))))
WHERE le_bsv_map_upd.intracompany_mode IS NULL; -- OR le_bsv_map_upd.intracompany_mode <> 5;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_intracompany_mode_1.finish', 'finish');
DELETE FROM fun_bal_le_bsv_map_gt
WHERE intracompany_mode IS NULL;
DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
WHERE EXISTS (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
'BSV already balanced'
FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
AND le_bsv_map_del.le_id = le_bsv_map.le_id
AND le_bsv_map_del.bal_seg_val = le_bsv_map.bal_seg_val
AND le_bsv_map.group_id = lines.group_id
AND le_bsv_map.bal_seg_val = lines.bal_seg_val
AND le_bsv_map_del.LEDGER_ID = le_bsv_map.LEDGER_ID
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id, le_bsv_map.bal_seg_val
HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) =
SUM(NVL(lines.accounted_amt_cr, 0)))
AND
(SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) =
SUM(DECODE(lines.exchange_rate, NULL,NVL(lines.entered_amt_cr,0),0))));
UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
SET (driving_cr_bsv, intracompany_mode) =
(SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
MIN(le_bsv_map.bal_seg_val), DECODE(SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val))),
1, DECODE(le_bsv_map_upd.intracompany_mode, 1, 1, 3),
DECODE(le_bsv_map_upd.intracompany_mode, 1, 2, 4))
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
WHERE le_bsv_map.group_id = lines.group_id
AND le_bsv_map.bal_seg_val = lines.bal_seg_val
AND le_bsv_map.group_id = le_bsv_map_upd.group_id
AND le_bsv_map.le_id = le_bsv_map_upd.le_id
AND LE_BSV_MAP_UPD.LEDGER_ID = LE_BSV_MAP.LEDGER_ID
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id, le_bsv_map.bal_seg_val
--HAVING (le_bsv_map.clearing_option = '1D' OR le_bsv_map.clearing_option = '4M')
-- No need for this having clause as it has brought to the higher level to check
HAVING (SUM(NVL(lines.accounted_amt_cr, 0)) >
SUM(NVL(lines.accounted_amt_dr, 0)))
OR
((SUM(NVL(lines.accounted_amt_dr, 0)) =
SUM(NVL(lines.accounted_amt_cr, 0))) AND
(SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) >
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)))))
WHERE le_bsv_map_upd.intracompany_mode IS NOT NULL;
DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE le_bsv_map.driving_dr_bsv IS NULL OR le_bsv_map.driving_cr_bsv IS NULL;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_intracompany_mode.finish', 'finish');
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET template_id =
(SELECT opts.template_id
FROM fun_balance_options opts
WHERE le_bsv_map.ledger_id = opts.ledger_id
AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
AND le_bsv_map.je_source_name = opts.je_source_name
AND le_bsv_map.je_category_name = opts.je_category_name
AND opts.status_flag = 'Y')
WHERE le_bsv_map.template_id IS NULL;
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET template_id =
(SELECT opts.template_id
FROM fun_balance_options opts
WHERE le_bsv_map.ledger_id = opts.ledger_id
AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
AND le_bsv_map.je_source_name = opts.je_source_name
AND opts.je_category_name = 'Other'
AND opts.status_flag = 'Y')
WHERE le_bsv_map.template_id IS NULL;
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET template_id =
(SELECT opts.template_id
FROM fun_balance_options opts
WHERE le_bsv_map.ledger_id = opts.ledger_id
AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
AND opts.je_source_name = 'Other'
AND le_bsv_map.je_category_name = opts.je_category_name
AND opts.status_flag = 'Y')
WHERE le_bsv_map.template_id IS NULL;
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET template_id =
(SELECT opts.template_id
FROM fun_balance_options opts
WHERE le_bsv_map.ledger_id = opts.ledger_id
AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
-- No error here if null, since both le_id is -99 if no legal entity is specified
AND opts.je_source_name = 'Other'
AND opts.je_category_name = 'Other'
AND opts.status_flag = 'Y')
WHERE le_bsv_map.template_id IS NULL;
INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, dr_bsv, cr_bsv)
SELECT 'FUN_INTRA_RULE_NOT_ASSIGNED',
le_bsv_map.group_id, le_bsv_map.template_id,
DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE le_bsv_map.template_id IS NULL;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_template.finish', 'finish');
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (balance_by, clearing_option, clearing_bsv, many_to_many_option) =
(SELECT opts.balance_by_flag, opts.clearing_option,
DECODE (opts.clearing_option,
'1A', DECODE (opts.many_to_many_option,
'2E', le_bsv_map.clearing_bsv,
'1C', Nvl(le_bsv_map.clearing_bsv,opts.clearing_bsv)),
'3M', DECODE (opts.many_to_many_option,
'2E', le_bsv_map.clearing_bsv,
'1C', DECODE (le_bsv_map.intracompany_mode,
4, Nvl(le_bsv_map.clearing_bsv,opts.clearing_bsv),
le_bsv_map.clearing_bsv),
'2D', DECODE (le_bsv_map.intracompany_mode,
4, NULL,
le_bsv_map.clearing_bsv)),
NULL),
opts.many_to_many_option
FROM fun_balance_options opts
WHERE le_bsv_map.template_id = opts.template_id
AND opts.status_flag = 'Y');
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET intracompany_mode = 5
WHERE le_bsv_map.clearing_bsv IS NOT NULL
AND ((le_bsv_map.clearing_option = '1A') OR
(le_bsv_map.clearing_option = '3M' AND
le_bsv_map.intracompany_mode = 4 AND
le_bsv_map.many_to_many_option IN ('2E', '1C')));
INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, dr_bsv, cr_bsv)
SELECT 'FUN_INTRA_NO_CLEARING_BSV',
le_bsv_map.group_id, le_bsv_map.template_id,
DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE le_bsv_map.clearing_bsv IS NULL
AND ((le_bsv_map.clearing_option = '1A') OR
(le_bsv_map.clearing_option = '3M' AND
le_bsv_map.intracompany_mode = 4 AND
le_bsv_map.many_to_many_option IN ('2E', '1C')));
INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, clearing_bsv,
dr_bsv, cr_bsv)
SELECT 'FUN_INTRA_CLEAR_BSV_INVALID',
le_bsv_map.group_id, le_bsv_map.template_id,
DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
le_bsv_map.clearing_bsv,
le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
FROM fun_bal_le_bsv_map_gt le_bsv_map,
gl_ledgers ledger
WHERE le_bsv_map.clearing_bsv IS NOT NULL
AND ledger.ledger_id = le_bsv_map.ledger_id
AND ledger.bal_seg_value_option_code = 'I'
AND NOT EXISTS
(SELECT 'X'
FROM gl_ledger_le_bsv_specific_v gl_seg
WHERE gl_seg.ledger_id = le_bsv_map.ledger_id
AND gl_seg.segment_value = le_bsv_map.clearing_bsv
AND TRUNC(le_bsv_map.gl_date) BETWEEN TRUNC(NVL(gl_seg.start_date, le_bsv_map.gl_date))
AND TRUNC(NVL(gl_seg.end_date, le_bsv_map.gl_date)));
UPDATE fun_bal_headers_gt headers
SET STATUS = 'ERROR'
WHERE EXISTS (SELECT 'Errors for no template or no clearing bsv or clearing bsv invalid'
FROM FUN_BAL_ERRORS_GT errors
WHERE headers.group_id = errors.group_id
AND error_code IN ('FUN_INTRA_RULE_NOT_ASSIGNED',
'FUN_INTRA_NO_CLEARING_BSV',
'FUN_INTRA_CLEAR_BSV_INVALID'))
AND headers.status = 'OK';
DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE EXISTS (SELECT group_id
FROM fun_bal_headers_gt headers
WHERE headers.status = 'ERROR'
AND le_bsv_map.group_id = headers.group_id);
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
(SELECT dr_ccid, cr_ccid, 'N', 'N'
FROM fun_balance_accounts accts
WHERE le_bsv_map.template_id = accts.template_id
AND ((le_bsv_map.intracompany_mode = 5
AND le_bsv_map.bal_seg_val = accts.dr_bsv
AND le_bsv_map.clearing_bsv = accts.cr_bsv)
OR (le_bsv_map.intracompany_mode = 1
AND le_bsv_map.bal_seg_val = accts.dr_bsv
AND 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) = accts.cr_bsv)
OR (le_bsv_map.intracompany_mode = 2
AND le_bsv_map.bal_seg_val = accts.dr_bsv
AND le_bsv_map.driving_dr_bsv = accts.cr_bsv)
OR (le_bsv_map.intracompany_mode = 3
AND le_bsv_map.bal_seg_val = accts.dr_bsv
AND le_bsv_map.driving_cr_bsv = accts.cr_bsv)));
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
(SELECT dr_ccid, cr_ccid, 'N','N' /* Bug 14565212 Set _complete to N for all cases */
FROM fun_balance_accounts accts
WHERE le_bsv_map.template_id = accts.template_id
AND ((le_bsv_map.intracompany_mode = 5
AND 'OTHER1234567890123456789012345' = accts.cr_bsv
AND le_bsv_map.bal_seg_val = accts.dr_bsv)
OR (le_bsv_map.intracompany_mode IN (1,2,3)
AND le_bsv_map.bal_seg_val = accts.dr_bsv
AND 'OTHER1234567890123456789012345' = accts.cr_bsv)))
WHERE dr_cr_debit_ccid IS NULL; --OR dr_cr_credit_ccid IS NULL; No need to check both
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
(SELECT dr_ccid, cr_ccid, 'N','N' /* Bug 14565212 Set _complete to N for all cases */
FROM fun_balance_accounts accts
WHERE le_bsv_map.template_id = accts.template_id
AND ((le_bsv_map.intracompany_mode = 5
AND le_bsv_map.clearing_bsv = accts.cr_bsv
AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
OR (le_bsv_map.intracompany_mode = 1
AND 'OTHER1234567890123456789012345' = accts.dr_bsv
AND 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) = accts.cr_bsv)
OR (le_bsv_map.intracompany_mode = 2
AND 'OTHER1234567890123456789012345' = accts.dr_bsv
AND le_bsv_map.driving_dr_bsv = accts.cr_bsv)
OR (le_bsv_map.intracompany_mode = 3
AND 'OTHER1234567890123456789012345' = accts.dr_bsv
AND le_bsv_map.driving_cr_bsv = accts.cr_bsv)))
WHERE dr_cr_debit_ccid IS NULL;
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
(SELECT dr_ccid, cr_ccid, 'N', 'N'
FROM fun_balance_accounts accts
WHERE le_bsv_map.template_id = accts.template_id
AND 'OTHER1234567890123456789012345' = accts.cr_bsv
AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
WHERE dr_cr_debit_ccid IS NULL ;
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
(SELECT dr_ccid, cr_ccid, 'N', 'N'
FROM fun_balance_accounts accts
WHERE le_bsv_map.template_id = accts.template_id
AND ((le_bsv_map.intracompany_mode = 5
AND le_bsv_map.bal_seg_val = accts.cr_bsv
AND le_bsv_map.clearing_bsv = accts.dr_bsv)
OR (le_bsv_map.intracompany_mode = 1
AND le_bsv_map.bal_seg_val = accts.cr_bsv
AND 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) = accts.dr_bsv)
OR (le_bsv_map.intracompany_mode = 2
AND le_bsv_map.bal_seg_val = accts.cr_bsv
AND le_bsv_map.driving_dr_bsv = accts.dr_bsv)
OR (le_bsv_map.intracompany_mode = 3
AND le_bsv_map.bal_seg_val = accts.cr_bsv
AND le_bsv_map.driving_cr_bsv = accts.dr_bsv)));
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
(SELECT dr_ccid, cr_ccid,'N','N'/* Bug 14565212 Set _complete to N for all cases */
FROM fun_balance_accounts accts
WHERE le_bsv_map.template_id = accts.template_id
AND ((le_bsv_map.intracompany_mode = 5
AND 'OTHER1234567890123456789012345' = accts.cr_bsv
AND le_bsv_map.clearing_bsv = accts.dr_bsv)
OR (le_bsv_map.intracompany_mode = 1
AND 'OTHER1234567890123456789012345' = accts.cr_bsv
AND 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) = accts.dr_bsv)
OR (le_bsv_map.intracompany_mode = 2
AND 'OTHER1234567890123456789012345' = accts.cr_bsv
AND le_bsv_map.driving_dr_bsv = accts.dr_bsv)
OR (le_bsv_map.intracompany_mode = 3
AND 'OTHER1234567890123456789012345' = accts.cr_bsv
AND le_bsv_map.driving_cr_bsv = accts.dr_bsv)))
WHERE cr_dr_debit_ccid IS NULL;
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
(SELECT dr_ccid, cr_ccid, 'N', 'N'/* SN */
FROM fun_balance_accounts accts
WHERE le_bsv_map.template_id = accts.template_id
AND ((le_bsv_map.intracompany_mode = 5
AND 'OTHER1234567890123456789012345' = accts.dr_bsv
AND le_bsv_map.bal_seg_val = accts.cr_bsv)
OR (le_bsv_map.intracompany_mode IN (1,2,3)
AND le_bsv_map.bal_seg_val = accts.cr_bsv
AND 'OTHER1234567890123456789012345' = accts.dr_bsv)))
WHERE cr_dr_debit_ccid IS NULL;
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
(SELECT dr_ccid, cr_ccid, 'N', 'N' /* SN */
FROM fun_balance_accounts accts
WHERE le_bsv_map.template_id = accts.template_id
AND 'OTHER1234567890123456789012345' = accts.cr_bsv
AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
WHERE cr_dr_debit_ccid IS NULL;
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET (dr_cr_debit_ccid, dr_cr_debit_complete) =
(SELECT code_combination_id, DECODE(ccid, NULL, 'N', 'Y')
FROM gl_code_combinations cc1,
gl_code_combinations cc2
WHERE le_bsv_map.dr_cr_debit_ccid = cc1.code_combination_id
AND cc1.segment1 = DECODE(le_bsv_map.bal_seg_column_no, 1, le_bsv_)
WHERE dr_cr_debit_complete = 'N'
*/
IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.get_ccid.begin', 'begin');
UPDATE fun_bal_le_bsv_map_gt le_bsv_map
SET dr_cr_debit_ccid =
/*remove decode */ get_ccid(le_bsv_map.dr_cr_debit_ccid, le_bsv_map.chart_of_accounts_id, le_bsv_map.bal_seg_val,
DECODE(le_bsv_map.intracompany_mode,
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),
2, le_bsv_map.driving_dr_bsv,
3, le_bsv_map.driving_cr_bsv,
5, le_bsv_map.clearing_bsv,
4,getintersegvalue(le_bsv_map.dr_cr_debit_ccid,le_bsv_map.intercompany_column_number),NULL),
le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
le_bsv_map.gl_date),
dr_cr_credit_ccid =
/* remove decode */ get_ccid(le_bsv_map.dr_cr_credit_ccid, le_bsv_map.chart_of_accounts_id,
DECODE(le_bsv_map.intracompany_mode,
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),
2, le_bsv_map.driving_dr_bsv,
3, le_bsv_map.driving_cr_bsv,
4, le_bsv_map.bal_seg_val,
5, le_bsv_map.clearing_bsv,
NULL),
DECODE(le_bsv_map.intracompany_mode, 4,getintersegvalue(le_bsv_map.dr_cr_credit_ccid,le_bsv_map.intercompany_column_number), le_bsv_map.bal_seg_val),
le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
le_bsv_map.gl_date),
cr_dr_debit_ccid =
/*remove decode*/ get_ccid(le_bsv_map.cr_dr_debit_ccid, le_bsv_map.chart_of_accounts_id,
DECODE(le_bsv_map.intracompany_mode,
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),
2, le_bsv_map.driving_dr_bsv,
3, le_bsv_map.driving_cr_bsv,
4, le_bsv_map.bal_seg_val,
5, le_bsv_map.clearing_bsv,
NULL),
DECODE(le_bsv_map.intracompany_mode, 4, getintersegvalue(le_bsv_map.cr_dr_debit_ccid,le_bsv_map.intercompany_column_number), le_bsv_map.bal_seg_val),
le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
le_bsv_map.gl_date),
cr_dr_credit_ccid =
/* decode*/
get_ccid(le_bsv_map.cr_dr_credit_ccid, le_bsv_map.chart_of_accounts_id, le_bsv_map.bal_seg_val,
DECODE(le_bsv_map.intracompany_mode,
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),
2, le_bsv_map.driving_dr_bsv,
3, le_bsv_map.driving_cr_bsv,
5, le_bsv_map.clearing_bsv,
4,getintersegvalue(le_bsv_map.cr_dr_credit_ccid,le_bsv_map.intercompany_column_number),
NULL),
le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
le_bsv_map.gl_date);
INSERT INTO fun_bal_intra_int_gt(group_id, gl_date, driving_dr_bsv, driving_cr_bsv,
intracompany_mode, balance_by, clearing_option, bal_seg_val, le_id, template_id, entered_currency_code,
exchange_date, exchange_rate, exchange_rate_type, accounted_amt_cr, accounted_amt_dr,
entered_amt_cr, entered_amt_dr,
dr_cr_debit_ccid, dr_cr_credit_ccid, cr_dr_debit_ccid, cr_dr_credit_ccid,
type, clearing_bsv)
(SELECT hdrs.group_id, hdrs.gl_date, le_bsv_map.driving_dr_bsv,
le_bsv_map.driving_cr_bsv, le_bsv_map.intracompany_mode, le_bsv_map.balance_by,
le_bsv_map.clearing_option, le_bsv_map.bal_seg_val,
le_bsv_map.le_id, le_bsv_map.template_id, lines.entered_currency_code,
SYSDATE,DECODE(LINES.EXCHANGE_RATE, NULL, NULL,DECODE (DECODE(SIGN(NVL(LINES.ACCOUNTED_AMT_CR, 0) - NVL(LINES.ACCOUNTED_AMT_DR, 0)),
1, 1,-1, -1,0, DECODE(SIGN((( NVL(LINES.ENTERED_AMT_CR, 0) - NVL(LINES.ENTERED_AMT_DR, 0)) ) - ( NVL(LINES.ACCOUNTED_AMT_DR, 0) - NVL(LINES.ACCOUNTED_AMT_CR, 0)) ),
1, 1, -1)), -1, DECODE(LINES.ENTERED_AMT_DR,0,1,LINES.ACCOUNTED_AMT_DR / LINES.ENTERED_AMT_DR), DECODE(LINES.ENTERED_AMT_CR,0,1,LINES.ACCOUNTED_AMT_CR / LINES.ENTERED_AMT_CR))) EXCHANGE_RATE,'User', --10249721
-- Bug 3223147 DECODE(SIGN(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)),
-- 1, ABS(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)), NULL)
lines.accounted_amt_cr,
-- Bug 3223147 DECODE(SIGN(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)),
-- -1, ABS(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)), NULL)
lines.accounted_amt_dr,
-- Bug 3223147 DECODE(SIGN(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)),
-- 1, ABS(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)), NULL)
lines.entered_amt_cr,
-- Bug 3223147 DECODE(SIGN(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)),
-- -1, ABS(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)), NULL)
lines.entered_amt_dr,
le_bsv_map.dr_cr_debit_ccid, le_bsv_map.dr_cr_credit_ccid, le_bsv_map.cr_dr_debit_ccid,
le_bsv_map.cr_dr_credit_ccid,
/* Bug 3223147
DECODE(SIGN(NVL(lines.accounted_amt_cr, 0)-NVL(lines.accounted_amt_dr,0)),
1, 'C',
-1, 'D',
0, DECODE(SIGN(NVL(lines.entered_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)),
1, 'C',
'D')) type,
*/
DECODE(lines.accounted_amt_cr, NULL, DECODE(lines.entered_amt_cr, NULL, 'D', 'C'), 'C') type,
le_bsv_map.clearing_bsv
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines, fun_bal_headers_gt hdrs
WHERE hdrs.group_id = lines.group_id
AND lines.group_id = le_bsv_map.group_id
AND lines.bal_seg_val = le_bsv_map.bal_seg_val
AND hdrs.status = 'OK'
AND le_bsv_map.balance_by = 'D'
UNION ALL
SELECT hdrs.group_id, hdrs.gl_date, le_bsv_map.driving_dr_bsv,
le_bsv_map.driving_cr_bsv, le_bsv_map.intracompany_mode, le_bsv_map.balance_by,
le_bsv_map.clearing_option, le_bsv_map.bal_seg_val,
le_bsv_map.le_id, le_bsv_map.template_id, lines.entered_currency_code,
SYSDATE,DECODE(MAX(LINES.EXCHANGE_RATE), NULL, NULL,DECODE(( SUM(NVL(LINES.ENTERED_AMT_DR, 0)) - SUM(NVL(LINES.ENTERED_AMT_CR, 0))),0,1,
( SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0))) / (( SUM(NVL(LINES.ENTERED_AMT_DR, 0)) - SUM(NVL(LINES.ENTERED_AMT_CR, 0))) )) ) EXCHANGE_RATE,'User',
--10249721
DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
1, ABS(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))), NULL)
accounted_amt_cr,
DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
-1, ABS(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))), NULL)
accounted_amt_dr,
DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))),
1, ABS(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))), NULL)
entered_amt_cr,
DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))),
-1, ABS(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))), NULL)
entered_amt_dr,
le_bsv_map.dr_cr_debit_ccid, le_bsv_map.dr_cr_credit_ccid, le_bsv_map.cr_dr_debit_ccid,
le_bsv_map.cr_dr_credit_ccid,
DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0))-SUM(NVL(lines.accounted_amt_dr,0))),
1, 'C',
-1, 'D',
0, DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
1, 'C',
'D')) type, le_bsv_map.clearing_bsv
FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
fun_bal_headers_gt hdrs
WHERE hdrs.group_id = lines.group_id
AND lines.group_id = le_bsv_map.group_id
AND lines.bal_seg_val = le_bsv_map.bal_seg_val
AND hdrs.status = 'OK'
AND le_bsv_map.balance_by = 'S'
GROUP BY hdrs.group_id, hdrs.gl_date, hdrs.status, le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
le_bsv_map.intracompany_mode, le_bsv_map.balance_by, le_bsv_map.clearing_option, le_bsv_map.bal_seg_val,
le_bsv_map.le_id, lines.entered_currency_code, le_bsv_map.dr_cr_debit_ccid, le_bsv_map.dr_cr_credit_ccid, le_bsv_map.cr_dr_debit_ccid,
le_bsv_map.cr_dr_credit_ccid, le_bsv_map.clearing_bsv, le_bsv_map.template_id
HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
AND
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))));
INSERT INTO fun_bal_results_gt lines(group_id, bal_seg_val, entered_amt_dr,
entered_amt_cr, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
accounted_amt_dr, accounted_amt_cr, ccid, dr_bsv, cr_bsv, acct_type, le_id, template_id, balancing_type)
SELECT intra_lines.group_id,
DECODE(intra_lines.intracompany_mode,
1, bal_seg_val,
2, DECODE(gen.value, 'C', intra_lines.driving_dr_bsv, intra_lines.bal_seg_val),
3, DECODE(gen.value, 'C', intra_lines.bal_seg_val, intra_lines.driving_cr_bsv),
4, bal_seg_val,
5, DECODE(gen.value, intra_lines.type, intra_lines.clearing_bsv, intra_lines.bal_seg_val),
NULL),
DECODE(intra_lines.intracompany_mode,
1, intra_lines.entered_amt_cr,
2, DECODE(gen.value, 'C', intra_lines.entered_amt_dr,
'D', intra_lines.entered_amt_cr,
-1),
3, DECODE(gen.value, 'C', intra_lines.entered_amt_cr,
'D', intra_lines.entered_amt_dr,
-1),
4, intra_lines.entered_amt_cr,
5, DECODE(gen.value, intra_lines.type, intra_lines.entered_amt_dr,
intra_lines.entered_amt_cr)),
DECODE(intra_lines.intracompany_mode,
1, intra_lines.entered_amt_dr,
2, DECODE(gen.value, 'C', intra_lines.entered_amt_cr,
'D', intra_lines.entered_amt_dr,
-1),
3, DECODE(gen.value, 'C', intra_lines.entered_amt_dr,
'D', intra_lines.entered_amt_cr,
-1),
4, intra_lines.entered_amt_dr,
5, DECODE(gen.value, intra_lines.type, intra_lines.entered_amt_cr,
intra_lines.entered_amt_dr)),
intra_lines.entered_currency_code,
intra_lines.exchange_date, intra_lines.exchange_rate, intra_lines.exchange_rate_type,
DECODE(intra_lines.intracompany_mode,
1, intra_lines.accounted_amt_cr,
2, DECODE(gen.value, 'C', intra_lines.accounted_amt_dr,
'D', intra_lines.accounted_amt_cr,
-1),
3, DECODE(gen.value, 'C', intra_lines.accounted_amt_cr,
'D', intra_lines.accounted_amt_dr,
-1),
4, intra_lines.accounted_amt_cr,
5, DECODE(gen.value, intra_lines.type, intra_lines.accounted_amt_dr,
intra_lines.accounted_amt_cr)),
DECODE(intra_lines.intracompany_mode,
1, intra_lines.accounted_amt_dr,
2, DECODE(gen.value, 'C', intra_lines.accounted_amt_cr,
'D', intra_lines.accounted_amt_dr,
-1),
3, DECODE(gen.value, 'C', intra_lines.accounted_amt_dr,
'D', intra_lines.accounted_amt_cr,
-1),
4, intra_lines.accounted_amt_dr,
5, DECODE(gen.value, intra_lines.type, intra_lines.accounted_amt_cr,
intra_lines.accounted_amt_dr)),
DECODE(intra_lines.intracompany_mode,
1, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', cr_dr_credit_ccid, -- bal_seg_val
'C', dr_cr_debit_ccid,
-1),
'D', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
'D', cr_dr_credit_ccid,
-1),
-1),
2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
'D', cr_dr_credit_ccid,
-1),
'C', DECODE(intra_lines.type, 'C', dr_cr_credit_ccid, -- other_seg_val
'D', cr_dr_debit_ccid,
-1),
-1),
3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
'D', cr_dr_credit_ccid,
-1),
'D', DECODE(intra_lines.type, 'D', cr_dr_debit_ccid, -- other_seg_val
'C', dr_cr_credit_ccid,
-1),
-1),
4, DECODE(gen.value, 'C', cr_dr_credit_ccid,
'D', dr_cr_debit_ccid,
-1),
5, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', cr_dr_credit_ccid, -- bal_seg_val
'C', dr_cr_credit_ccid, -- other_seg_val
-1),
'D', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
'D', cr_dr_debit_ccid, -- other_seg_val
-1),
-1)),
DECODE(intra_lines.intracompany_mode,
1, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', driving_cr_bsv,
'C', bal_seg_val,
-1),
'D', DECODE(intra_lines.type, 'C', bal_seg_val,
'D', driving_dr_bsv,
-1),
-1),
2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', bal_seg_val,
'D', driving_dr_bsv,
-1),
'C', DECODE(intra_lines.type, 'C', bal_seg_val,
'D', driving_dr_bsv,
-1),
-1),
3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', bal_seg_val,
'D', driving_cr_bsv,
-1),
'D', DECODE(intra_lines.type, 'D', driving_cr_bsv,
'C', bal_seg_val,
-1),
-1),
4, bal_seg_val,
5, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', clearing_bsv,
'C', bal_seg_val,
-1),
'D', DECODE(intra_lines.type, 'C', bal_seg_val,
'D', clearing_bsv,
-1),
-1)),
DECODE(intra_lines.intracompany_mode,
1, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', bal_seg_val,
'C', driving_cr_bsv,
-1),
'D', DECODE(intra_lines.type, 'C', driving_dr_bsv,
'D', bal_seg_val,
-1),
-1),
2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', driving_dr_bsv,
'D', bal_seg_val,
-1),
'C', DECODE(intra_lines.type, 'C', driving_dr_bsv,
'D', bal_seg_val,
-1),
-1),
3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', driving_cr_bsv,
'D', bal_seg_val,
-1),
'D', DECODE(intra_lines.type, 'D', bal_seg_val,
'C', driving_cr_bsv,
-1),
-1),
4, bal_seg_val,
5, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', bal_seg_val,
'C', clearing_bsv,
-1),
'D', DECODE(intra_lines.type, 'C', clearing_bsv,
'D', bal_seg_val,
-1),
-1)),
DECODE(intra_lines.intracompany_mode,
1, DECODE(intra_lines.type, 'D', 'C',
'C', 'D', -1),
2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', 'D',
'D', 'C',
-1),
'C', DECODE(intra_lines.type, 'C', 'C',
'D', 'D',
-1),
-1),
3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', 'D',
'D', 'C',
-1),
'D', DECODE(intra_lines.type, 'D', 'D',
'C', 'C',
-1),
-1),
4, gen.value,
5, gen.value,
-1),
intra_lines.le_id, intra_lines.template_id, 'R'
FROM FUN_BAL_INTRA_INT_GT intra_lines, FUN_BAL_GENERATE_LINES gen
WHERE gen.value = DECODE(intra_lines.intracompany_mode,
1, DECODE(gen.value, 'C', DECODE(intra_lines.bal_seg_val,
intra_lines.driving_cr_bsv, 'X', gen.value),
'D', DECODE(intra_lines.bal_seg_val,
intra_lines.driving_dr_bsv, 'X', gen.value),
'X'),
2, DECODE(intra_lines.bal_seg_val, intra_lines.driving_dr_bsv, 'X', gen.value),
3, DECODE(intra_lines.bal_seg_val, intra_lines.driving_cr_bsv, 'X', gen.value),
4, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', 'X', gen.value),
'D', DECODE(intra_lines.type, 'D', 'X', gen.value)),
5, DECODE(bal_seg_val, clearing_bsv, 'X', gen.value), -- bug 3203634
'X');
INSERT INTO fun_bal_errors_gt(error_code, group_id, template_id, le_id,
dr_bsv, cr_bsv, acct_type, ccid_concat_display)
SELECT DISTINCT DECODE(SIGN(NVL(results.ccid, 0)),
-1, 'FUN_INTRA_CC_NOT_CREATED',
0, 'FUN_INTRA_CC_NOT_CREATED',
DECODE(cc.summary_flag,
'Y', 'FUN_INTRA_CC_NOT_VALID',
DECODE(cc.template_id,
NULL, 'FUN_INTRA_CC_NOT_ACTIVE',
'FUN_INTRA_CC_NOT_VALID'))),
headers.group_id, results.template_id,
DECODE(results.le_id, -99, NULL, results.le_id),
results.dr_bsv, results.cr_bsv,
results.acct_type, get_ccid_concat_disp(results.ccid, headers.chart_of_accounts_id,
DECODE(results.acct_type, 'C', results.cr_bsv, results.dr_bsv),
DECODE(results.acct_type, 'C', results.dr_bsv, results.cr_bsv),
headers.bal_seg_column_number, headers.intercompany_column_number)
FROM fun_bal_headers_gt headers, fun_bal_results_gt results, gl_code_combinations cc
WHERE headers.group_id = results.group_id
AND headers.status = 'OK'
AND results.ccid = cc.code_combination_id(+)
AND (results.ccid < 0
OR results.ccid IS NULL -- NULL case should not happen, but just in case
OR NOT (cc.detail_posting_allowed_flag = 'Y'
AND cc.enabled_flag = 'Y'
AND cc.summary_flag = 'N'
AND nvl(cc.reference3, 'N') IN ('N', 'R')
AND cc.template_id IS NULL
AND (TRUNC(headers.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, headers.gl_date))
AND TRUNC(NVL(cc.end_date_active, headers.gl_date)))));
UPDATE fun_bal_headers_gt headers
SET STATUS = 'ERROR'
WHERE EXISTS (SELECT 'Invalid CCID error'
FROM FUN_BAL_ERRORS_GT errors
WHERE headers.group_id = errors.group_id
AND error_code IN ('FUN_INTRA_CC_NOT_VALID',
'FUN_INTRA_CC_NOT_CREATED',
'FUN_INTRA_CC_NOT_ACTIVE'));
DELETE FROM fun_bal_results_gt results
WHERE EXISTS (SELECT group_id
FROM fun_bal_headers_gt headers
WHERE headers.status = 'ERROR'
AND results.group_id = headers.group_id);
INSERT INTO fun_bal_errors_gt(error_code, group_id, error_message)
select 'FUN_INTER_UNEXPECTED_ERROR', FH.group_id, ERR_MESSAGE FROM FUN_BAL_HEADERS_GT FH;
SELECT COUNT(*) INTO l_errors_count
FROM fun_bal_errors_gt;
-- Delete and save data in temporary tables
do_save_in_error;
-- Delete and save data in temporary tables
do_save_in_error;
-- Delete and save data in temporary tables
do_save_in_error;