[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rowid,
balancing_segment,
account_segment
FROM gl_rx_trial_balance_itf
WHERE request_id = l_request_id;
SELECT led.ledger_id,
led.currency_code,
led.chart_of_accounts_id,
ps.period_year
INTO l_ledger_id,
l_functional_currency,
l_chart_of_accounts_id,
l_fiscal_year
FROM gl_ledgers led,
gl_period_statuses ps
WHERE led.name = p_ledger_name
AND led.ledger_id = ps.ledger_id
AND ps.application_id = l_application_id
AND ps.period_name = p_period_name;
SELECT period_name,
adjustment_period_flag
INTO l_first_period_name,
l_adjustment_flag
FROM gl_period_statuses
WHERE application_id = l_application_id
AND ledger_id = l_ledger_id
AND period_year = l_fiscal_year
AND period_num = ( SELECT MIN(period_num)
FROM gl_period_statuses
WHERE application_id = l_application_id
AND ledger_id = l_ledger_id
AND period_year = l_fiscal_year);
l_dyn_stmt := 'insert into gl_rx_trial_balance_itf (
request_id,
period_name,
ledger_id,
ledger_name,
chart_of_accounts_id,
currency_code,
balancing_segment,
account_segment,
summary_flag,
begin_year_balance_dr,
begin_year_balance_cr,
begin_adj_period_net_dr,
begin_adj_period_net_cr,
begin_period_balance_dr,
begin_period_balance_cr,
period_net_dr,
period_net_cr)
SELECT
'||to_char(l_request_id)||',
'||''''||p_period_name||''''||',
'||to_char(l_ledger_id)||',
'||''''||p_ledger_name||''''||',
'||to_char(l_chart_of_accounts_id)||',
'||''''||l_currency_code||''''||',
gcc.'||l_balancing_segment_column||',
'||'decode('||''''||p_summary_flag||''''||','||
''''||'N'||''''||','||'gcc.'||l_account_segment_column||','||
''''||'Y'||''''||',substr(gcc.'||l_account_segment_column||
',1,'||to_char(nvl(p_summary_digits,1))||')),
'||''''||p_summary_flag||''''||',
sum(decode(period_name,'||''''||l_first_period_name||''''||',
nvl(begin_balance_dr,0), 0)),
sum(decode(period_name,'||''''||l_first_period_name||''''||',
nvl(begin_balance_cr,0), 0)),
sum(decode('||''''||l_adjustment_flag||''''||',
'||''''||'Y'||''''||
', decode(period_name,'||''''||l_first_period_name||''''||',
nvl(period_net_dr,0), 0), 0)),
sum(decode('||''''||l_adjustment_flag||''''||',
'||''''||'Y'||''''||
', decode(period_name,'||''''||l_first_period_name||''''||',
nvl(period_net_cr,0), 0), 0)),
sum(decode(period_name,'||''''||p_period_name||''''||',
nvl(begin_balance_dr,0), 0)),
sum(decode(period_name,'||''''||p_period_name||''''||',
nvl(begin_balance_cr,0), 0)),
sum(decode(period_name,'||''''||p_period_name||''''||',
nvl(period_net_dr,0), 0)),
sum(decode(period_name,'||''''||p_period_name||''''||',
nvl(period_net_cr,0), 0))
FROM gl_balances bal,
gl_code_combinations gcc
WHERE bal.code_combination_id = gcc.code_combination_id
AND bal.ledger_id = '||to_char(l_ledger_id)||'
AND bal.currency_code = '||''''||l_currency_code||''''||'
AND bal.period_name in ('||''''||p_period_name||''''||','||
''''||l_first_period_name||''''||')
AND bal.actual_flag = '||''''||'A'||''''||'
AND gcc.chart_of_accounts_id = '||to_char(l_chart_of_accounts_id)||'
AND gcc.template_id is null'||
l_balancing_where||
l_account_from_where||
l_account_to_where||
l_translate_where||'
GROUP BY
gcc.'||l_balancing_segment_column||',
decode('||''''||p_summary_flag||''''||','||
''''||'N'||''''||',gcc.'||l_account_segment_column||','||
''''||'Y'||''''||',substr(gcc.'||l_account_segment_column||',1,'||
to_char(nvl(p_summary_digits,1))||'))
HAVING
sum(decode(period_name,'||''''||l_first_period_name||''''||',
nvl(begin_balance_dr,0), 0)) <> 0 OR
sum(decode(period_name,'||''''||l_first_period_name||''''||',
nvl(begin_balance_cr,0), 0)) <> 0 OR
sum(decode('||''''||l_adjustment_flag||''''||',
'||''''||'Y'||''''||',decode(period_name,'||''''||l_first_period_name||''''||',
nvl(period_net_dr,0), 0), 0)) <> 0 OR
sum(decode('||''''||l_adjustment_flag||''''||',
'||''''||'Y'||''''||',decode(period_name,'||''''||l_first_period_name||''''||',
nvl(period_net_cr,0), 0), 0)) <> 0 OR
sum(decode(period_name,'||''''||p_period_name||''''||',
nvl(begin_balance_dr,0), 0)) <> 0 OR
sum(decode(period_name,'||''''||p_period_name||''''||',
nvl(begin_balance_cr,0), 0)) <> 0 OR
sum(decode(period_name,'||''''||p_period_name||''''||',
nvl(period_net_dr,0), 0)) <> 0 OR
sum(decode(period_name,'||''''||p_period_name||''''||',
nvl(period_net_cr,0), 0)) <> 0';
UPDATE gl_rx_trial_balance_itf set
begin_year_balance_dr = 0,
begin_year_balance_cr = 0,
begin_period_balance_dr = (begin_period_balance_dr - begin_year_balance_dr),
begin_period_balance_cr = (begin_period_balance_cr - begin_year_balance_cr)
WHERE request_id = l_request_id;
UPDATE gl_rx_trial_balance_itf SET
balancing_description = l_balancing_description,
account_description = l_account_description,
prior_periods_net_dr = (begin_period_balance_dr - (begin_year_balance_dr + begin_adj_period_net_dr)),
prior_periods_net_cr = (begin_period_balance_cr - (begin_year_balance_cr + begin_adj_period_net_cr)),
year_to_date_balance_dr = (begin_period_balance_dr + period_net_dr),
year_to_date_balance_cr = (begin_period_balance_cr + period_net_cr),
year_to_date_net_difference_dr = DECODE(SIGN((begin_period_balance_dr + period_net_dr) -
(begin_period_balance_cr + period_net_cr)), 1,
((begin_period_balance_dr + period_net_dr) -
(begin_period_balance_cr + period_net_cr)), 0),
year_to_date_net_difference_cr = DECODE(SIGN((begin_period_balance_dr + period_net_dr) -
(begin_period_balance_cr + period_net_cr)), -1,
abs((begin_period_balance_dr + period_net_dr) -
(begin_period_balance_cr + period_net_cr)), 0)
WHERE
rowid = l_rowid;