[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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');
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);
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)
SELECT main.group_id, main.ledger_id, main.bal_seg_val, main.gl_date, NVL(vals.legal_entity_id, -99)
FROM (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.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_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)
SELECT 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,
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,
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
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
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)));
UPDATE fun_bal_inter_int_gt upd
SET driving_dr_le_bsv =
(SELECT DECODE((COUNT(DISTINCT le_bsv_map.bal_seg_val)),
1, MIN(le_bsv_map.bal_seg_val),
'Many')
FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE upd.group_id = le_bsv_map.group_id
AND upd.driving_dr_le_id = le_bsv_map.le_id
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
UPDATE fun_bal_inter_int_gt upd
SET driving_cr_le_bsv =
(SELECT DECODE((COUNT(DISTINCT le_bsv_map.bal_seg_val)),
1, MIN(le_bsv_map.bal_seg_val),
'Many')
FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE upd.group_id = le_bsv_map.group_id
AND upd.driving_cr_le_id = le_bsv_map.le_id
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
UPDATE fun_bal_inter_int_gt upd
SET line_le_bsv =
(SELECT DECODE((COUNT(DISTINCT le_bsv_map.bal_seg_val)),
1, MIN(le_bsv_map.bal_seg_val),
'Many')
FROM fun_bal_le_bsv_map_gt le_bsv_map
WHERE upd.group_id = le_bsv_map.group_id
AND upd.le_id = le_bsv_map.le_id
GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
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 -- To LE "Other"
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 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 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 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,
NULL, NULL, NULL, NULL),
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 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,
NULL, NULL, NULL, NULL),
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 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.
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, lines.entered_currency_code, lines.exchange_date,
lines.exchange_rate, lines.exchange_rate_type,
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');
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, lines.entered_currency_code, lines.exchange_date,
lines.exchange_rate, lines.exchange_rate_type,
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');
-- 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,
NULL, NULL, NULL, NULL),
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 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,
NULL, NULL, NULL, NULL),
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 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 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
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 '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
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 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
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;
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, 'Y', 'Y'
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, DECODE(le_bsv_map.intercompany_column_number,
NULL, 'Y', 'N'), 'N'
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', DECODE(le_bsv_map.intercompany_column_number,
NULL, 'Y', '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.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, 'Y', 'Y'
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, DECODE(le_bsv_map.intercompany_column_number,
NULL, 'Y', 'N'), 'N'
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', DECODE(le_bsv_map.intercompany_column_number,
NULL, 'Y', 'N')
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'
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 =
DECODE(le_bsv_map.dr_cr_debit_complete, 'Y', le_bsv_map.dr_cr_debit_ccid,
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,
4, le_bsv_map.bal_seg_val,
5, le_bsv_map.clearing_bsv,
NULL),
le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
le_bsv_map.gl_date)),
dr_cr_credit_ccid =
DECODE(le_bsv_map.dr_cr_credit_complete, 'Y', le_bsv_map.dr_cr_credit_ccid,
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),
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 =
DECODE(le_bsv_map.cr_dr_debit_complete, 'Y', le_bsv_map.cr_dr_debit_ccid,
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),
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(le_bsv_map.cr_dr_credit_complete, 'Y', le_bsv_map.cr_dr_credit_ccid,
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,
4, le_bsv_map.bal_seg_val,
5, le_bsv_map.clearing_bsv,
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,
lines.exchange_date, lines.exchange_rate, lines.exchange_rate_type,
-- 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,
lines.exchange_date, lines.exchange_rate, lines.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,
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, lines.exchange_date, lines.exchange_rate,
lines.exchange_rate_type, 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 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);