[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CANT_DELETE_BALANCES EXCEPTION;
FUNCTION call_update_balances RETURN BOOLEAN IS
l_batch_code VARCHAR2(100) := p_batch_code;
update_balances(l_batch_code,l_purge_mode);
END call_update_balances;
SELECT gps.period_year
FROM gl_period_statuses gps,
xla_ac_balances_int bal
WHERE gps.ledger_id = bal.ledger_id
-- AND gps.application_id = bal.application_id
AND gps.application_id = 101 --bug 11811413
AND gps.adjustment_period_flag = 'N'
AND gps.period_name = cp_period_name;
PROCEDURE insert_balances_rec(
p_ac_balance_int_rec xla_ac_balances%ROWTYPE
) IS
l_log_module VARCHAR2(240);
l_log_module := c_default_module || '.insert';
TRACE(p_msg => 'BEGIN of function insert',
p_module => l_log_module,
p_level => c_level_procedure
);
INSERT INTO xla_ac_balances
(application_id,
ledger_id,
code_combination_id,
analytical_criterion_code,
analytical_criterion_type_code,
amb_context_code,
ac1,
ac2,
ac3,
ac4,
ac5,
period_name,
beginning_balance_dr,
beginning_balance_cr,
period_balance_dr,
period_balance_cr,
initial_balance_flag,
first_period_flag,
period_year,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_update_date,
program_application_id,
program_id,
request_id
)
VALUES (p_ac_balance_int_rec.application_id,
p_ac_balance_int_rec.ledger_id,
p_ac_balance_int_rec.code_combination_id,
p_ac_balance_int_rec.analytical_criterion_code,
p_ac_balance_int_rec.analytical_criterion_type_code,
p_ac_balance_int_rec.amb_context_code,
p_ac_balance_int_rec.ac1,
p_ac_balance_int_rec.ac2,
p_ac_balance_int_rec.ac3,
p_ac_balance_int_rec.ac4,
p_ac_balance_int_rec.ac5,
p_ac_balance_int_rec.period_name,
p_ac_balance_int_rec.beginning_balance_dr,
p_ac_balance_int_rec.beginning_balance_cr,
p_ac_balance_int_rec.period_balance_dr,
p_ac_balance_int_rec.period_balance_cr,
p_ac_balance_int_rec.initial_balance_flag,
p_ac_balance_int_rec.first_period_flag,
l_period_year,
g_date,
g_user_id,
g_date,
g_user_id,
g_login_id,
g_date,
g_prog_appl_id,
g_prog_id,
g_req_id
);
p_msg => l_row_count || ' initial balances inserted',
p_level => c_level_statement
);
(p_location => 'xla_ac_balances_pkg.insert_balances_rec');
END insert_balances_rec;
PROCEDURE update_balances_rec(
p_ac_balance_int_rec xla_ac_balances%ROWTYPE
) IS
l_log_module VARCHAR2(240);
l_log_module := c_default_module || '.update_balances_rec';
TRACE(p_msg => 'BEGIN of function update_balances_rec',
p_module => l_log_module,
p_level => c_level_procedure
);
UPDATE xla_ac_balances
SET period_name = p_ac_balance_int_rec.period_name,
beginning_balance_dr = p_ac_balance_int_rec.beginning_balance_dr,
beginning_balance_cr = p_ac_balance_int_rec.beginning_balance_cr,
initial_balance_flag = p_ac_balance_int_rec.initial_balance_flag,
first_period_flag = p_ac_balance_int_rec.first_period_flag,
period_year = p_ac_balance_int_rec.period_year,
last_update_date = g_date,
program_update_date = g_date,
last_updated_by = g_user_id,
last_update_login = g_login_id,
program_application_id = g_prog_appl_id,
program_id = g_prog_id,
request_id = g_req_id
WHERE application_id = p_ac_balance_int_rec.application_id
AND ledger_id = p_ac_balance_int_rec.ledger_id
AND code_combination_id = p_ac_balance_int_rec.code_combination_id
AND analytical_criterion_code = p_ac_balance_int_rec.analytical_criterion_code
AND analytical_criterion_type_code = p_ac_balance_int_rec.analytical_criterion_type_code
AND amb_context_code = p_ac_balance_int_rec.amb_context_code
AND period_name = p_ac_balance_int_rec.period_name
AND NVL(ac1,'*') = NVL(p_ac_balance_int_rec.ac1,'*')
AND NVL(ac2,'*') = NVL(p_ac_balance_int_rec.ac2,'*')
AND NVL(ac3,'*') = NVL(p_ac_balance_int_rec.ac3,'*')
AND NVL(ac4,'*') = NVL(p_ac_balance_int_rec.ac4,'*')
AND NVL(ac5,'*') = NVL(p_ac_balance_int_rec.ac5,'*');
p_msg => l_row_count || ' initial balances updated',
p_level => c_level_statement
);
(p_location => 'xla_ac_balances_pkg.update_balances_rec');
END update_balances_rec;
PROCEDURE delete_balances_rec(
p_ac_balance_int_rec xla_ac_balances%ROWTYPE
) IS
l_log_module VARCHAR2(240);
l_log_module := c_default_module || '.delete_balances_rec';
TRACE(p_msg => 'BEGIN of function delete_balances_rec',
p_module => l_log_module,
p_level => c_level_procedure
);
DELETE xla_ac_balances xab
WHERE xab.application_id = p_ac_balance_int_rec.application_id
AND xab.ledger_id = p_ac_balance_int_rec.ledger_id
AND xab.code_combination_id = p_ac_balance_int_rec.code_combination_id
AND xab.analytical_criterion_code = p_ac_balance_int_rec.analytical_criterion_code
AND xab.analytical_criterion_type_code = p_ac_balance_int_rec.analytical_criterion_type_code
AND xab.amb_context_code = p_ac_balance_int_rec.amb_context_code
AND NVL(xab.ac1,'*') = NVL(p_ac_balance_int_rec.ac1,'*')
AND NVL(xab.ac2,'*') = NVL(p_ac_balance_int_rec.ac2,'*')
AND NVL(xab.ac3,'*') = NVL(p_ac_balance_int_rec.ac3,'*')
AND NVL(xab.ac4,'*') = NVL(p_ac_balance_int_rec.ac4,'*')
AND NVL(xab.ac5,'*') = NVL(p_ac_balance_int_rec.ac5,'*')
AND xab.period_name = p_ac_balance_int_rec.period_name;
p_msg => l_row_count || ' initial balances deleted',
p_level => c_level_statement
);
(p_location => 'xla_ac_balances_pkg.delete_balances_rec');
END delete_balances_rec;
SELECT xab.*
FROM xla_ac_balances xab
WHERE xab.ledger_id = cp_ledger_id
AND xab.code_combination_id = cp_code_combination_id
AND xab.analytical_criterion_code = cp_analytical_criterion_code
AND xab.analytical_criterion_type_code = cp_criterion_type_code
AND xab.amb_context_code = cp_amb_context_code
AND xab.period_name = cp_period_name
AND NVL(xab.ac1,'*') = NVL(cp_ac1,'*')
AND NVL(xab.ac2,'*') = NVL(cp_ac2,'*')
AND NVL(xab.ac3,'*') = NVL(cp_ac3,'*')
AND NVL(xab.ac4,'*') = NVL(cp_ac4,'*')
AND NVL(xab.ac5,'*') = NVL(cp_ac5,'*');
SELECT xab.*
FROM xla_ac_balances xab,
gl_ledgers ledger,
gl_periods fut_periods,
gl_period_types period_types,
gl_period_statuses fut_period_statuses,
gl_period_sets period_sets
WHERE ledger.accounted_period_type = period_types.period_type
AND period_types.period_type = fut_periods.period_type
AND fut_period_statuses.ledger_id = ledger.ledger_id
AND fut_period_statuses.period_name = fut_periods.period_name
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.closing_status IN('O','C','F')
AND fut_period_statuses.adjustment_period_flag = 'N'
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.period_name = fut_periods.period_name
AND period_sets.period_set_name = fut_periods.period_set_name
AND ledger.period_set_name = period_sets.period_set_name
AND ledger.accounted_period_type = period_types.period_type
AND ledger.ledger_id = cp_ledger_id
--AND fut_period_statuses.application_id = cp_application_id
AND fut_period_statuses.application_id = 101 --bug 11811413
AND xab.ledger_id = ledger.ledger_id
AND fut_periods.period_name = xab.period_name
AND xab.ledger_id = cp_ledger_id
AND xab.code_combination_id = cp_code_combination_id
AND xab.analytical_criterion_code = cp_analytical_criterion_code
AND xab.analytical_criterion_type_code = cp_criterion_type_code
AND xab.amb_context_code = cp_amb_context_code
AND NVL(xab.ac1,'*') = NVL(cp_ac1,'*')
AND NVL(xab.ac2,'*') = NVL(cp_ac2,'*')
AND NVL(xab.ac3,'*') = NVL(cp_ac3,'*')
AND NVL(xab.ac4,'*') = NVL(cp_ac4,'*')
AND NVL(xab.ac5,'*') = NVL(cp_ac5,'*')
AND xab.period_year = NVL(cp_period_year,xab.period_year)
AND xab.period_name <> cp_period_name
ORDER BY fut_periods.start_date;
SELECT NVL(xah.balancing_flag,'N') balancing_flag,
xah.year_end_carry_forward_code
FROM xla_analytical_hdrs_b xah
WHERE xah.amb_context_code = cp_amb_context_code
AND xah.analytical_criterion_code = cp_analytical_criterion_code
AND xah.analytical_criterion_type_code = cp_criterion_type_code;
SELECT periods.end_date
FROM gl_ledgers ledger,
gl_periods periods,
gl_period_types period_types,
gl_period_statuses period_statuses,
gl_period_sets period_sets
WHERE ledger.accounted_period_type = period_types.period_type
AND period_types.period_type = periods.period_type
AND period_statuses.ledger_id = ledger.ledger_id
AND period_statuses.period_name = periods.period_name
AND period_statuses.period_type = period_types.period_type
--AND period_statuses.closing_status IN('O', 'C', 'P')
AND period_statuses.adjustment_period_flag = 'N'
AND period_statuses.period_type = period_types.period_type
AND period_statuses.period_name = periods.period_name
AND period_sets.period_set_name = periods.period_set_name
AND ledger.period_set_name = period_sets.period_set_name
AND ledger.accounted_period_type = period_types.period_type
AND ledger.ledger_id = cp_ledger_id
--AND period_statuses.application_id = cp_application_id
AND period_statuses.application_id = 101
AND periods.period_year = NVL(cp_period_year, periods.period_year)
AND periods.period_name = cp_period_name;
SELECT periods.start_date
FROM xla_ac_balances xab,
gl_ledgers ledger,
gl_periods periods,
gl_period_types period_types,
gl_period_statuses period_statuses,
gl_period_sets period_sets
WHERE ledger.accounted_period_type = period_types.period_type
AND period_types.period_type = periods.period_type
AND period_statuses.ledger_id = ledger.ledger_id
AND period_statuses.period_name = periods.period_name
AND period_statuses.period_type = period_types.period_type
AND period_statuses.adjustment_period_flag = 'N'
AND period_statuses.period_type = period_types.period_type
AND period_statuses.period_name = periods.period_name
AND period_sets.period_set_name = periods.period_set_name
AND ledger.period_set_name = period_sets.period_set_name
AND ledger.accounted_period_type = period_types.period_type
AND ledger.ledger_id = cp_ledger_id
AND periods.period_year = NVL(cp_period_year, periods.period_year)
AND xab.ledger_id = ledger.ledger_id
AND periods.period_name = xab.period_name
AND xab.ledger_id = cp_ledger_id
AND xab.code_combination_id = cp_code_combination_id
AND xab.analytical_criterion_code = cp_analytical_criterion_code
AND xab.analytical_criterion_type_code = cp_criterion_type_code
AND xab.amb_context_code = cp_amb_context_code
--AND period_statuses.application_id = cp_application_id
AND period_statuses.application_id = 101 --bug 11811413
AND NVL(xab.ac1,'*') = NVL(cp_ac1,'*')
AND NVL(xab.ac2,'*') = NVL(cp_ac2,'*')
AND NVL(xab.ac3,'*') = NVL(cp_ac3,'*')
AND NVL(xab.ac4,'*') = NVL(cp_ac4,'*')
AND NVL(xab.ac5,'*') = NVL(cp_ac5,'*')
AND xab.period_name <> cp_period_name
ORDER BY periods.start_date;
SELECT fut_periods.period_name,
fut_periods.period_year,
fut_periods.period_num
FROM gl_ledgers ledger,
gl_periods fut_periods,
gl_period_types period_types,
gl_period_statuses fut_period_statuses,
gl_period_sets period_sets
WHERE ledger.accounted_period_type = period_types.period_type
AND period_types.period_type = fut_periods.period_type
AND fut_period_statuses.ledger_id = ledger.ledger_id
AND fut_period_statuses.period_name = fut_periods.period_name
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.adjustment_period_flag = 'N'
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.period_name = fut_periods.period_name
AND period_sets.period_set_name = fut_periods.period_set_name
AND ledger.period_set_name = period_sets.period_set_name
AND ledger.accounted_period_type = period_types.period_type
AND ledger.ledger_id = cp_ledger_id
--AND fut_period_statuses.application_id = cp_application_id
AND fut_period_statuses.application_id = 101 --bug 11811413
AND fut_periods.period_year = NVL(cp_period_year, fut_periods.period_year)
AND fut_periods.start_date > cp_earliest_start_date
AND fut_periods.end_date < cp_latest_end_date;
SELECT account_type
FROM gl_code_combinations
WHERE code_combination_id = cp_cc_id;
SELECT periods.period_num
FROM gl_periods periods,
gl_ledgers ledger
WHERE ledger.ledger_id = cp_application_id
AND ledger.period_set_name = periods.period_set_name
AND periods.period_name = cp_period_name;
SELECT fut_periods.period_name,
fut_periods.period_num
FROM gl_ledgers ledger,
gl_periods fut_periods,
gl_period_types period_types,
gl_period_statuses fut_period_statuses,
gl_period_sets period_sets,
gl_periods ref_period
WHERE ledger.accounted_period_type = period_types.period_type
AND period_types.period_type = fut_periods.period_type
AND fut_period_statuses.ledger_id = ledger.ledger_id
AND fut_period_statuses.period_name = fut_periods.period_name
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.closing_status IN('O', 'F')
AND fut_period_statuses.adjustment_period_flag = 'N'
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.period_name = fut_periods.period_name
AND period_sets.period_set_name = fut_periods.period_set_name
AND ledger.period_set_name = period_sets.period_set_name
AND ledger.accounted_period_type = period_types.period_type
AND ledger.ledger_id = cp_ledger_id
--AND fut_period_statuses.application_id = cp_application_id
AND fut_period_statuses.application_id = 101 --bug 11811413
AND fut_periods.period_year = NVL(cp_period_year, fut_periods.period_year)
AND ref_period.period_name = cp_period_name
AND ref_period.period_type = period_types.period_type
AND period_sets.period_set_name = ref_period.period_set_name
AND ref_period.start_date < fut_periods.start_date;
SELECT fut_periods.period_name,
fut_periods.period_num
FROM gl_ledgers ledger,
gl_periods fut_periods,
gl_period_types period_types,
gl_period_statuses fut_period_statuses,
gl_period_sets period_sets,
gl_periods ref_period
WHERE ledger.accounted_period_type = period_types.period_type
AND period_types.period_type = fut_periods.period_type
AND fut_period_statuses.ledger_id = ledger.ledger_id
AND fut_period_statuses.period_name = fut_periods.period_name
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.closing_status = 'C'
AND fut_period_statuses.adjustment_period_flag = 'N'
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.period_name = fut_periods.period_name
AND period_sets.period_set_name = fut_periods.period_set_name
AND ledger.period_set_name = period_sets.period_set_name
AND ledger.accounted_period_type = period_types.period_type
AND ledger.ledger_id = cp_ledger_id
--AND fut_period_statuses.application_id = cp_application_id
AND fut_period_statuses.application_id = 101 --bug 11811413
AND fut_periods.period_year = NVL(cp_period_year, fut_periods.period_year)
AND ref_period.period_name = cp_period_name
AND ref_period.period_type = period_types.period_type
AND period_sets.period_set_name = ref_period.period_set_name
AND ref_period.start_date < fut_periods.start_date;
l_delete_cr_delta NUMBER;
l_delete_dr_delta NUMBER;
CURSOR c_delete_records(
cp_application_id xla_ac_balances.application_id%TYPE,
cp_ledger_id xla_ac_balances.ledger_id%TYPE,
cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
cp_ac1 xla_ac_balances.ac1%TYPE,
cp_ac2 xla_ac_balances.ac2%TYPE,
cp_ac3 xla_ac_balances.ac3%TYPE,
cp_ac4 xla_ac_balances.ac4%TYPE,
cp_ac5 xla_ac_balances.ac5%TYPE,
cp_period_year xla_ac_balances.period_year%TYPE
) IS
SELECT xab.*
FROM xla_ac_balances xab,
gl_ledgers ledger,
gl_periods fut_periods,
gl_period_types period_types,
gl_period_statuses fut_period_statuses,
gl_period_sets period_sets
WHERE ledger.accounted_period_type = period_types.period_type
AND period_types.period_type = fut_periods.period_type
AND fut_period_statuses.ledger_id = ledger.ledger_id
AND fut_period_statuses.period_name = fut_periods.period_name
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.adjustment_period_flag = 'N'
AND fut_period_statuses.period_type = period_types.period_type
AND fut_period_statuses.period_name = fut_periods.period_name
AND period_sets.period_set_name = fut_periods.period_set_name
AND ledger.period_set_name = period_sets.period_set_name
AND ledger.accounted_period_type = period_types.period_type
AND ledger.ledger_id = cp_ledger_id
--AND fut_period_statuses.application_id = cp_application_id
AND fut_period_statuses.application_id = 101 --bug 11811413
AND xab.ledger_id = ledger.ledger_id
AND fut_periods.period_name = xab.period_name
AND xab.ledger_id = cp_ledger_id
AND xab.code_combination_id = cp_code_combination_id
AND xab.analytical_criterion_code = cp_analytical_criterion_code
AND xab.analytical_criterion_type_code = cp_criterion_type_code
AND xab.amb_context_code = cp_amb_context_code
AND NVL(xab.ac1, '*') = NVL(cp_ac1, '*')
AND NVL(xab.ac2, '*') = NVL(cp_ac2, '*')
AND NVL(xab.ac3, '*') = NVL(cp_ac3, '*')
AND NVL(xab.ac4, '*') = NVL(cp_ac4, '*')
AND NVL(xab.ac5, '*') = NVL(cp_ac5, '*')
AND fut_periods.period_year <> cp_period_year
ORDER BY fut_periods.start_date;
* data is being imported and the "next period". We need to insert new records for such intermediate periods
*/
OPEN c_next_period_start_date(p_ac_balance_int_rec.application_id,
p_ac_balance_int_rec.ledger_id,
p_ac_balance_int_rec.code_combination_id,
p_ac_balance_int_rec.analytical_criterion_code,
p_ac_balance_int_rec.analytical_criterion_type_code,
p_ac_balance_int_rec.amb_context_code,
p_ac_balance_int_rec.ac1,
p_ac_balance_int_rec.ac2,
p_ac_balance_int_rec.ac3,
p_ac_balance_int_rec.ac4,
p_ac_balance_int_rec.ac5,
l_period_year,
p_ac_balance_int_rec.period_name);
update_balances_rec(l_exist_balance);
update_balances_rec(l_exist_balance);
update_balances_rec(l_exist_balance);
trace(p_module => l_log_module,p_msg => 'calling delete_balances_rec',p_level => C_LEVEL_STATEMENT);
delete_balances_rec(l_exist_balance);
RAISE CANT_DELETE_BALANCES;
* If we are deleting a balances record, we need to delete future balances records
* which don't have any period activity
*/
IF (l_subsequent_periods.beginning_balance_dr IS NULL AND l_subsequent_periods.beginning_balance_cr IS NULL AND NVL(l_subsequent_periods.period_balance_cr,0) = 0 AND NVL(l_subsequent_periods.period_balance_dr,0) = 0) THEN
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace(p_module => l_log_module,p_msg => 'calling delete_balances_rec for period:'||l_subsequent_periods.period_name,p_level => C_LEVEL_STATEMENT);
delete_balances_rec(l_subsequent_periods);
update_balances_rec(l_subsequent_periods);
* There are future open periods. so insert new records for them with zero period activity
*/
WHILE c_future_open_periods%FOUND LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace(p_module => l_log_module,p_msg => 'c_future_open_periods fetched period:'||l_future_open_periods.period_name,p_level => C_LEVEL_STATEMENT);
insert_balances_rec(l_balances_rec);
trace(p_module => l_log_module,p_msg => 'opening c_delete_records for year :'||l_period_year,p_level => C_LEVEL_STATEMENT);
FOR l_delete_records IN c_delete_records(p_ac_balance_int_rec.application_id,
p_ac_balance_int_rec.ledger_id,
p_ac_balance_int_rec.code_combination_id,
p_ac_balance_int_rec.analytical_criterion_code,
p_ac_balance_int_rec.analytical_criterion_type_code,
p_ac_balance_int_rec.amb_context_code,
p_ac_balance_int_rec.ac1,
p_ac_balance_int_rec.ac2,
p_ac_balance_int_rec.ac3,
p_ac_balance_int_rec.ac4,
p_ac_balance_int_rec.ac5,
l_period_year) LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace(p_module => l_log_module,p_msg => 'c_delete_records fetched period:'||l_delete_records.period_name,p_level => C_LEVEL_STATEMENT);
IF c_delete_records%ROWCOUNT = 1 AND (l_delete_records.period_balance_dr IS NOT NULL OR l_delete_records.period_balance_cr IS NOT NULL) THEN
EXIT;
IF c_delete_records%ROWCOUNT = 1 AND l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
delete_balances_rec(l_delete_records);
l_delete_cr_delta := l_delete_records.beginning_balance_cr;
l_delete_dr_delta := l_delete_records.beginning_balance_dr;
l_prev_year := l_delete_records.period_year;
IF l_prev_year = l_delete_records.period_year THEN
IF l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
delete_balances_rec(l_delete_records);
l_delete_records.beginning_balance_cr := NVL(l_delete_records.beginning_balance_cr,0) + NVL(l_delete_cr_delta,0);
l_delete_records.beginning_balance_dr := NVL(l_delete_records.beginning_balance_dr,0) + NVL(l_delete_dr_delta,0);
update_balances_rec(l_delete_records);
trace(p_module => l_log_module,p_msg => 'opening c_delete_records for year :'||l_period_year,p_level => C_LEVEL_STATEMENT);
FOR l_delete_records IN c_delete_records(p_ac_balance_int_rec.application_id,
p_ac_balance_int_rec.ledger_id,
p_ac_balance_int_rec.code_combination_id,
p_ac_balance_int_rec.analytical_criterion_code,
p_ac_balance_int_rec.analytical_criterion_type_code,
p_ac_balance_int_rec.amb_context_code,
p_ac_balance_int_rec.ac1,
p_ac_balance_int_rec.ac2,
p_ac_balance_int_rec.ac3,
p_ac_balance_int_rec.ac4,
p_ac_balance_int_rec.ac5,
l_period_year) LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace(p_module => l_log_module,p_msg => 'c_delete_records fetched period:'||l_delete_records.period_name,p_level => C_LEVEL_STATEMENT);
IF c_delete_records%ROWCOUNT = 1 AND (l_delete_records.period_balance_dr IS NOT NULL OR l_delete_records.period_balance_cr IS NOT NULL) THEN
EXIT;
IF c_delete_records%ROWCOUNT = 1 AND l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
delete_balances_rec(l_delete_records);
l_delete_cr_delta := l_delete_records.beginning_balance_cr;
l_delete_dr_delta := l_delete_records.beginning_balance_dr;
l_prev_year := l_delete_records.period_year;
IF l_prev_year = l_delete_records.period_year THEN
IF l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
delete_balances_rec(l_delete_records);
l_delete_records.beginning_balance_cr := NVL(l_delete_records.beginning_balance_cr,0) + NVL(l_delete_cr_delta,0);
l_delete_records.beginning_balance_dr := NVL(l_delete_records.beginning_balance_dr,0) + NVL(l_delete_dr_delta,0);
update_balances_rec(l_delete_records);
RAISE CANT_DELETE_BALANCES;
trace(p_module => l_log_module,p_msg => 'calling insert_balances_rec',p_level => C_LEVEL_STATEMENT);
insert_balances_rec(l_balances_rec);
update_balances_rec(l_subsequent_periods);
trace(p_module => l_log_module,p_msg => 'starting insert/update for future periods',p_level => C_LEVEL_STATEMENT);
insert_balances_rec(l_balances_rec);
* In that case, insert new balances for periods until the last open or future entry period
* If no open/future entry periods exist, then go until the last closed period
*/
OPEN c_future_open_periods(p_ac_balance_int_rec.application_id,
p_ac_balance_int_rec.ledger_id,
p_ac_balance_int_rec.period_name,
l_period_year);
* There are future open periods. so insert new records for them with zero period activity
*/
WHILE c_future_open_periods%FOUND LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace(p_module => l_log_module,p_msg => 'c_future_open_periods fetched period:'||l_future_open_periods.period_name,p_level => C_LEVEL_STATEMENT);
insert_balances_rec(l_balances_rec);
* There are no future open periods. so insert new records for closed periods in the current year with zero period activity, only for
* E Expense
* R Revenue
* C Budgetary (CR)
* D Budgetary (DR)
*/
IF l_account_type IN ('E','R','C','D') THEN
FOR l_closed_periods IN c_closed_periods(p_ac_balance_int_rec.application_id,p_ac_balance_int_rec.ledger_id,p_ac_balance_int_rec.period_name,l_period_year) LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace(p_module => l_log_module,p_msg => 'c_closed_periods fetched period:'||l_closed_periods.period_name,p_level => C_LEVEL_STATEMENT);
insert_balances_rec(l_balances_rec);
trace(p_module => l_log_module,p_msg => 'opening c_delete_records for year :'||l_period_year,p_level => C_LEVEL_STATEMENT);
FOR l_delete_records IN c_delete_records(p_ac_balance_int_rec.application_id,
p_ac_balance_int_rec.ledger_id,
p_ac_balance_int_rec.code_combination_id,
p_ac_balance_int_rec.analytical_criterion_code,
p_ac_balance_int_rec.analytical_criterion_type_code,
p_ac_balance_int_rec.amb_context_code,
p_ac_balance_int_rec.ac1,
p_ac_balance_int_rec.ac2,
p_ac_balance_int_rec.ac3,
p_ac_balance_int_rec.ac4,
p_ac_balance_int_rec.ac5,
l_period_year) LOOP
IF (C_LEVEL_STATEMENT >= g_log_level) THEN
trace(p_module => l_log_module,p_msg => 'c_delete_records fetched period:'||l_delete_records.period_name,p_level => C_LEVEL_STATEMENT);
IF c_delete_records%ROWCOUNT = 1 AND (l_delete_records.period_balance_dr IS NOT NULL OR l_delete_records.period_balance_cr IS NOT NULL) THEN
EXIT;
IF c_delete_records%ROWCOUNT = 1 AND l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
delete_balances_rec(l_delete_records);
l_delete_cr_delta := l_delete_records.beginning_balance_cr;
l_delete_dr_delta := l_delete_records.beginning_balance_dr;
l_prev_year := l_delete_records.period_year;
IF l_prev_year = l_delete_records.period_year THEN
IF l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
delete_balances_rec(l_delete_records);
l_delete_records.beginning_balance_cr := NVL(l_delete_records.beginning_balance_cr,0) + NVL(l_delete_cr_delta,0);
l_delete_records.beginning_balance_dr := NVL(l_delete_records.beginning_balance_dr,0) + NVL(l_delete_dr_delta,0);
update_balances_rec(l_delete_records);
WHEN CANT_DELETE_BALANCES THEN
RAISE;
select 1
from xla_subledgers
where application_id = p_app_id;
select ledger_category_code
from gl_ledgers
where ledger_id = p_ledger_id;
select 1
from gl_ledger_relationships
where primary_ledger_id = p_ledger_id
and relationship_type_code='SUBLEDGER';
select chart_of_accounts_id
from gl_ledgers
where ledger_id = p_ledger_id;
select 1
from gl_ledgers lg,
gl_code_combinations cc
where lg.ledger_id = p_ledger_id
and lg.chart_of_accounts_id = cc.chart_of_accounts_id
and cc.code_combination_id = p_code_comb_id;
select gl.code_combination_id
from gl_code_combinations gl
where NVL(gl.segment1, 'X') = NVL(p_rec.segment1, 'X')
and NVL(gl.segment2, 'X') = NVL(p_rec.segment2, 'X')
and NVL(gl.segment3, 'X') = NVL(p_rec.segment3, 'X')
and NVL(gl.segment4, 'X') = NVL(p_rec.segment4, 'X')
and NVL(gl.segment5, 'X') = NVL(p_rec.segment5, 'X')
and NVL(gl.segment6, 'X') = NVL(p_rec.segment6, 'X')
and NVL(gl.segment7, 'X') = NVL(p_rec.segment7, 'X')
and NVL(gl.segment8, 'X') = NVL(p_rec.segment8, 'X')
and NVL(gl.segment9, 'X') = NVL(p_rec.segment9, 'X')
and NVL(gl.segment10, 'X') = NVL(p_rec.segment10, 'X')
and NVL(gl.segment11, 'X') = NVL(p_rec.segment11, 'X')
and NVL(gl.segment12, 'X') = NVL(p_rec.segment12, 'X')
and NVL(gl.segment13, 'X') = NVL(p_rec.segment13, 'X')
and NVL(gl.segment14, 'X') = NVL(p_rec.segment14, 'X')
and NVL(gl.segment15, 'X') = NVL(p_rec.segment15, 'X')
and NVL(gl.segment16, 'X') = NVL(p_rec.segment16, 'X')
and NVL(gl.segment17, 'X') = NVL(p_rec.segment17, 'X')
and NVL(gl.segment18, 'X') = NVL(p_rec.segment18, 'X')
and NVL(gl.segment19, 'X') = NVL(p_rec.segment19, 'X')
and NVL(gl.segment20, 'X') = NVL(p_rec.segment20, 'X')
and NVL(gl.segment21, 'X') = NVL(p_rec.segment21, 'X')
and NVL(gl.segment22, 'X') = NVL(p_rec.segment22, 'X')
and NVL(gl.segment23, 'X') = NVL(p_rec.segment23, 'X')
and NVL(gl.segment24, 'X') = NVL(p_rec.segment24, 'X')
and NVL(gl.segment25, 'X') = NVL(p_rec.segment25, 'X')
and NVL(gl.segment26, 'X') = NVL(p_rec.segment26, 'X')
and NVL(gl.segment27, 'X') = NVL(p_rec.segment27, 'X')
and NVL(gl.segment28, 'X') = NVL(p_rec.segment28, 'X')
and NVL(gl.segment29, 'X') = NVL(p_rec.segment29, 'X')
and NVL(gl.segment30, 'X') = NVL(p_rec.segment30, 'X');
SELECT 1
FROM XLA_ANALYTICAL_HDRS_B
WHERE analytical_criterion_code = p_anal_crit_code
AND analytical_criterion_type_code = p_anal_crit_type_code
AND amb_context_code = p_amb_context_code
AND balancing_flag = 'Y'
AND enabled_flag = 'Y';
select 1
from gl_period_statuses
where application_id =101 --bug 11811413
and ledger_id = p_ledger_id
and period_name=p_period_name
AND closing_status IN ('O','C');
select 1
from xla_ac_balances
where ledger_id = p_rec.ledger_id
and code_combination_id = p_rec.code_combination_id
and analytical_criterion_code = p_rec.analytical_criterion_code
and analytical_criterion_type_code = p_rec.analytical_criterion_type_code
and amb_context_code = p_rec.amb_context_code
AND NVL(ac1,'*') = NVL(p_rec.ac1,'*')
AND NVL(ac2,'*') = NVL(p_rec.ac2,'*')
AND NVL(ac3,'*') = NVL(p_rec.ac3,'*')
AND NVL(ac4,'*') = NVL(p_rec.ac4,'*')
AND NVL(ac5,'*') = NVL(p_rec.ac5,'*')
and period_name in (
select per.period_name
from gl_periods per,
gl_ledgers led,
gl_periods ref_per
where per.adjustment_period_flag = 'N'
and led.accounted_period_type = per.period_type
and led.period_set_name = per.period_set_name
and led.ledger_id = p_rec.ledger_id
and per.start_date < ref_per.start_date
and ref_per.period_name = p_rec.period_name
and ref_per.period_type = per.period_type
and ref_per.period_set_name = per.period_set_name
);
,select_comb_from_view => NULL
)
THEN
l_error_codes := l_error_codes || 'IB005,';
,select_comb_from_view => NULL
)
THEN
l_error_codes := l_error_codes || 'IB006,';
* Delete records based on p_purge_mode
* If p_purge_mode = A, then delete all records for the p_batch_code passed
* If p_purge_mode = S, then delete all records that were imported in this run
* If p_purge_mode = N, then do not delete anything
*/
DELETE xla_ac_balances_int xib
WHERE ( ( p_batch_code IS NOT NULL
AND p_batch_code = xib.batch_code)
OR ( p_batch_code IS NULL
AND 1 = 1))
AND ( ( p_purge_mode = 'N'
AND 1 = 2)
OR ( p_purge_mode = 'S'
AND xib.status = 'IMPORTED')
OR ( p_purge_mode = 'A'
AND xib.status IN('IMPORTED', 'ERROR')));
PROCEDURE update_balances
( p_errbuf OUT NOCOPY VARCHAR2
,p_retcode OUT NOCOPY NUMBER
,p_batch_code IN VARCHAR2
,p_purge_mode IN VARCHAR2
)
IS
BEGIN
update_balances(p_batch_code,p_purge_mode);
END update_balances;
PROCEDURE update_balances
( p_batch_code IN VARCHAR2
,p_purge_mode IN VARCHAR2
)
IS
/*======================================================================+
| |
| Public Function |
| |
| Description |
| ----------- |
| Just the SRS wrapper |
| |
| Pseudo-code |
| ----------- |
| Call update_balances and assign its return code to |
| p_retcode |
| RETURN p_retcode (0=success, 1=warning, 2=error) |
| |
| Open issues |
| ----------- |
| |
| |
| |
| |
+======================================================================*/
l_commit_flag VARCHAR2(1);
SELECT xib.*
FROM xla_ac_balances_int xib
WHERE ( xib.status IS NULL
OR xib.status = 'ERROR')
AND (xib.batch_code = NVL(cp_batch_code, xib.batch_code))
ORDER BY batch_code DESC
FOR UPDATE OF status NOWAIT;
l_log_module := C_DEFAULT_MODULE||'.update_balances';
UPDATE xla_ac_balances_int
SET status = 'ERROR',
message_codes = l_message_codes,
last_updated_by = g_user_id,
last_update_date = g_date,
last_update_login = g_login_id
WHERE CURRENT OF c_balances_int;
* Successfully merged the records. Update the status of the interface record
*/
fnd_file.put_line(fnd_file.log,'Import Succeeded');
UPDATE xla_ac_balances_int
SET status = 'IMPORTED',
message_codes = NULL,
last_updated_by = g_user_id,
last_update_date = g_date,
last_update_login = g_login_id
WHERE CURRENT OF c_balances_int;
WHEN CANT_DELETE_BALANCES THEN
UPDATE xla_ac_balances_int
SET status = 'ERROR',
message_codes = 'IB018',
last_updated_by = g_user_id,
last_update_date = g_date,
last_update_login = g_login_id
WHERE CURRENT OF c_balances_int;
UPDATE xla_ac_balances_int
SET status = 'ERROR',
message_codes = l_sql_err,
last_updated_by = g_user_id,
last_update_date = g_date,
last_update_login = g_login_id
WHERE CURRENT OF c_balances_int;
(p_location => 'xla_ac_balances_pkg.update_balances');
END update_balances;