[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'found'
from fnd_product_installations fpi
where fpi.application_id = 201
and fpi.status = 'I';
gl_budgets_pkg.select_columns(
x_budget_name,
x_ledger_id,
x_budget_type,
x_budget_status,
x_bj_required_flag,
x_latest_opened_year,
x_first_valid_period,
x_last_valid_period);
0 SELECT STATEMENT HINT: RULE
2 SORT (AGGREGATE)
0 CONCATENATION
6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
*/
CURSOR c_balances IS
SELECT
nvl(sum(decode(actual_flag, 'A',
decode(x_amount_type,
'PTD',
decode(period_name, x_period_name,
nvl(period_net_dr,0) - nvl(period_net_cr,0), 0),
'QTDE',
decode(period_name, x_period_used_for_ext_actuals,
nvl(period_net_dr,0) - nvl(period_net_cr,0) +
nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
0),
'YTDE',
decode(bal.period_name, x_first_period_of_year_name,
-(nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0)), 0) +
decode(bal.period_name, x_period_used_for_ext_actuals,
nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0) +
nvl(period_net_dr,0) - nvl(period_net_cr,0),0),
'PJTD',
decode(period_name, x_period_used_for_ext_actuals,
nvl(period_net_dr,0) - nvl(period_net_cr,0) +
nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),0),
0),0)),0),
nvl(sum(decode(actual_flag, 'B',
decode(period_name, x_period_name,
nvl(period_net_dr,0) - nvl(period_net_cr,0) +
decode(x_amount_type,
'QTDE',
nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
'YTDE',
nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),
'PJTD',
nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),
0),0),0)),0),
nvl(sum(decode(actual_flag, 'E',
decode(period_name, x_period_name,
nvl(period_net_dr,0) - nvl(period_net_cr,0) +
decode(x_amount_type,
'QTDE',
nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
'YTDE',
nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),
'PJTD',
nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),
0),0),0)),0),
nvl(sum(decode(actual_flag, 'E',
decode(period_name, x_period_name,
decode(encumbrance_type_id,
nvl(x_req_encumbrance_id,-2),
nvl(period_net_dr,0) - nvl(period_net_cr,0) +
decode(x_amount_type,
'QTDE',
nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
'YTDE',
nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),
'PJTD',
nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),
0),0),0),0)),0),
nvl(sum(decode(actual_flag, 'E',
decode(period_name, x_period_name,
decode(encumbrance_type_id,
nvl(x_po_encumbrance_id,-2),
nvl(period_net_dr,0) - nvl(period_net_cr,0) +
decode(x_amount_type,
'QTDE',
nvl(quarter_to_date_dr,0) - nvl(quarter_to_date_cr,0),
'YTDE',
nvl(begin_balance_dr,0) - nvl(begin_balance_cr,0),
'PJTD',
nvl(project_to_date_dr,0) - nvl(project_to_date_cr,0),
0),0),0),0)),0)
FROM
gl_balances bal
WHERE
bal.ledger_id = x_ledger_id
and bal.code_combination_id = x_code_combination_id
--and bal.currency_code = decode(actual_flag, 'B', x_currency_code, bal.currency_code)
and bal.currency_code = x_currency_code
--and bal.currency_code <> 'STAT'
and bal.period_name in (x_period_name, decode(x_amount_type, 'YTDE',
x_period_used_for_ext_actuals,'QTDE', x_period_used_for_ext_actuals,
'PJTD', x_period_used_for_ext_actuals, x_period_name),
decode(x_amount_type, 'YTDE', x_first_period_of_year_name,
x_period_name))
and nvl(bal.budget_version_id, -1) = nvl(decode(actual_flag, 'B',
x_budget_version_id, bal.budget_version_id),-1)
and decode(actual_flag,
'E',decode(x_encumbrance_type_id, -1, -1, bal.encumbrance_type_id),
x_encumbrance_type_id) = x_encumbrance_type_id;
0 SELECT STATEMENT HINT: RULE
165 SORT (AGGREGATE)
165 NESTED LOOPS
139 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKET_ARRIVAL_ORDER_U3' (UNIQUE)
197 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BC_PACKETS'
335 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKETS_N2' (NON-UNIQUE)
*/
CURSOR c_packets IS
SELECT
nvl(sum(decode(actual_flag, 'A',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(actual_flag, 'B',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(actual_flag, 'E',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(actual_flag, 'E',
decode(encumbrance_type_id,
nvl(x_req_encumbrance_id,-2),
nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0),
nvl(sum(decode(actual_flag, 'E',
decode(encumbrance_type_id,
nvl(x_po_encumbrance_id,-2),
nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0)
FROM
gl_bc_packets pac, gl_bc_packet_arrival_order arr
WHERE
pac.code_combination_id = x_code_combination_id
and pac.currency_code = decode(pac.actual_flag, 'B', x_currency_code, pac.currency_code)
and pac.currency_code <> 'STAT'
and ((x_amount_type = 'PJTD' and
((pac.period_year < x_period_year) or
(pac.period_num <= x_period_num and
pac.period_year = x_period_year))) or
(x_amount_type = 'PTD' and
pac.period_name = x_period_name) or
(x_amount_type = 'YTDE' and
pac.period_year = x_period_year and
pac.period_num <= x_period_num) or
(x_amount_type = 'QTDE' and
pac.period_year = x_period_year and
pac.quarter_num = x_quarter_num and
pac.period_num <= x_period_num))
and NVL(pac.funding_budget_version_id, x_budget_version_id) = x_budget_version_id
and decode(actual_flag,
'E',decode(x_encumbrance_type_id, -1, -1, pac.encumbrance_type_id),
x_encumbrance_type_id) = x_encumbrance_type_id
and pac.status_code = 'A'
and pac.ledger_id = arr.ledger_id
and pac.packet_id = arr.packet_id
and arr.ledger_id = x_ledger_id
and arr.affect_funds_flag = 'Y';
select period_name, period_num, period_year, quarter_num
from gl_period_statuses
where application_id = x_application_id
and ledger_id = x_ledger_id
and period_name = (select latest_opened_period_name
from gl_ledgers
where ledger_id = x_ledger_id);
SELECT st.account_category_code
FROM gl_summary_templates st
WHERE st.template_id = x_template_id
AND st.ledger_id = x_ledger_id;
gl_period_statuses_pkg.select_year_1st_period(
101,
x_ledger_id,
x_period_year,
x_first_period_of_year_name );
0 SELECT STATEMENT HINT: RULE
2 SORT (AGGREGATE)
0 CONCATENATION
6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
6 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BALANCES'
7 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BALANCES_N1' (NON-UNIQUE)
*/
CURSOR c_balances IS
SELECT
nvl(sum(decode(actual_flag, 'A',(nvl(period_net_dr,0) - nvl(period_net_cr,0)),0)),0),
nvl(sum(decode(actual_flag, 'B',(nvl(period_net_dr,0) - nvl(period_net_cr,0)),0)),0),
nvl(sum(decode(actual_flag, 'E',(nvl(period_net_dr,0) - nvl(period_net_cr,0)),0)),0),
nvl(sum(decode(actual_flag, 'E', decode(encumbrance_type_id,
nvl(x_req_encumbrance_id,-2), nvl(period_net_dr,0) - nvl(period_net_cr,0)))),0),
nvl(sum(decode(actual_flag, 'E', decode(encumbrance_type_id,
nvl(x_po_encumbrance_id,-2), nvl(period_net_dr,0) - nvl(period_net_cr,0)))),0)
FROM
gl_balances bal
WHERE
bal.ledger_id = x_ledger_id
and bal.code_combination_id = x_code_combination_id
--and bal.currency_code = decode(actual_flag, 'B', x_currency_code, bal.currency_code)
and bal.currency_code = x_currency_code
--and bal.currency_code <> 'STAT'
and bal.period_name = x_period_name
and nvl(bal.budget_version_id, -1) = nvl(decode(actual_flag, 'B',
x_budget_version_id, bal.budget_version_id),-1)
and decode(actual_flag,
'E',decode(x_encumbrance_type_id, -1, -1, bal.encumbrance_type_id),
x_encumbrance_type_id) = x_encumbrance_type_id;
0 SELECT STATEMENT HINT: RULE
165 SORT (AGGREGATE)
165 NESTED LOOPS
139 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKET_ARRIVAL_ORDER_U3' (UNIQUE)
197 TABLE ACCESS HINT: ANALYZED (BY ROWID) OF 'GL_BC_PACKETS'
335 INDEX HINT: ANALYZED (RANGE SCAN) OF 'GL_BC_PACKETS_N2' (NON-UNIQUE)
*/
CURSOR c_packets IS
SELECT
nvl(sum(decode(actual_flag, 'A',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(actual_flag, 'B',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(actual_flag, 'E',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(actual_flag, 'E',
decode(encumbrance_type_id,
nvl(x_req_encumbrance_id,-2),
nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0),
nvl(sum(decode(actual_flag, 'E',
decode(encumbrance_type_id,
nvl(x_po_encumbrance_id,-2),
nvl(accounted_dr,0) - nvl(accounted_cr,0),0),0)),0)
FROM
gl_bc_packets pac, gl_bc_packet_arrival_order arr
WHERE
pac.code_combination_id = x_code_combination_id
and pac.currency_code = decode(pac.actual_flag, 'B', x_currency_code, pac.currency_code)
and pac.currency_code <> 'STAT'
AND pac.period_name = x_period_name
and NVL(pac.funding_budget_version_id, x_budget_version_id) = x_budget_version_id
and decode(actual_flag,
'E',decode(x_encumbrance_type_id, -1, -1, pac.encumbrance_type_id),
x_encumbrance_type_id) = x_encumbrance_type_id
and pac.status_code = 'A'
and pac.ledger_id = arr.ledger_id
and pac.packet_id = arr.packet_id
and arr.ledger_id = x_ledger_id
and arr.affect_funds_flag = 'Y';
SELECT st.account_category_code
FROM gl_summary_templates st
WHERE st.template_id = x_template_id
AND st.ledger_id = x_ledger_id;
SELECT period_num, quarter_num, period_year, closing_status
FROM Gl_Period_Statuses
WHERE application_id = 201
AND ledger_id = cp_ledger_id
AND period_name = cp_period_name;
SELECT period_set_name, accounted_period_type
FROM gl_ledgers
WHERE ledger_id = cp_ledger_id;
SELECT amount_type, funding_budget_version_id
FROM gl_summary_templates
WHERE ledger_id = cp_ledger_id
AND template_id = cp_template_id;
select decode(l_amt_type, 'YTD', 'YTDE', 'QTD', 'QTDE', l_amount_type)
into l_amount_type
from dual;