[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure update_history_tab(tres_id number,v_flag varchar2); --- private subprogramme
procedure ins_delete_treasury_symbols; --- private subprogramme
procedure delete_treasury_symbols; --- private subprogramme
gbl_last_update_by number(15);
gbl_last_update_log number(15);
select treasury_symbol_id,treasury_symbol,
set_of_books_id,
fund_group_code
from fv_treasury_symbols
where set_of_books_id = gbl_set_of_books_id
and treasury_symbol= nvl(v_treasury_symbol,treasury_symbol)
and time_frame = nvl(v_time_frame,time_frame)
and established_fiscal_yr = NVL(n_year_established,established_fiscal_yr)
and nvl(to_char(trunc(cancellation_date)),'0') = NVL(to_char(gbl_cancellation_date),nvl(to_char(TRUNC(cancellation_date)),0))
and trunc(cancellation_date) < trunc(sysdate) - 365;
selected from Cursor "c_treasury_symbols"
+-------------------------------------------------------+*/
cursor c_fund_parameters(tres_id number, v_sob number) is
select fund_parameter_id,fund_value,
treasury_symbol,set_of_books_id,
fund_group_code
from Fv_fund_parameters
where treasury_symbol_id = tres_id
and set_of_books_id = v_sob;
SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = p_value_set_id
and flex_value NOT IN('4350','4201')
AND NOT EXISTS ( SELECT 1 from fnd_flex_value_hierarchies
where Flex_value_set_id = p_value_set_id
AND flex_value BETWEEN child_flex_value_low
AND child_flex_value_high
AND PARENT_FLEX_VALUE in ('4350','4201'))
AND summary_flag = 'N'
ORDER BY 1 ;
gbl_last_update_by := fnd_global.user_id;
gbl_last_update_log := fnd_global.login_id;
SELECT chart_of_accounts_id, period_set_name
INTO gbl_account_id , gbl_period_set_name
FROM gl_ledgers_public_v GL
WHERE gl.ledger_id = gbl_set_of_books_id ;
SELECT flex_value_set_id
INTO gbl_flex_value_set_id
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = gbl_account_id
AND segment_num = gbl_acct_segment_num ;
update_history_tab(c1_treasury_symb.treasury_symbol_id ,v_open_flag);
Ins_delete_treasury_symbols;
delete_treasury_symbols; --- procedure to delete treasury symbols --------
update_history_tab(null,'R');
update_history_tab(null,'R');
insert ALL into fv_treasury_symbols_history
(treasury_symbol_id,
treasury_symbol,
set_of_books_id,
sf224_type_code,
fund_group_code,
time_frame,
years_available,
established_fiscal_yr,
expiration_date,
cancellation_date,
department_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
federal_acct_symbol_id,
dept_transfer,
request_id,
open_flag)
select treasury_symbol_id,
treasury_symbol,
set_of_books_id,
sf224_type_code,
fund_group_code,
time_frame,
years_available,
established_fiscal_yr,
expiration_date,
cancellation_date,
department_id,
gbl_last_upd_date,
nvl(gbl_last_update_by,1),
nvl(gbl_last_update_log,1),
gbl_creation_date,
nvl(gbl_created_by,1),
federal_acct_symbol_id,
dept_transfer,
gbl_request_id,
populate_history_tab.v_flag
from fv_treasury_symbols
where treasury_symbol_id = tres_id
and set_of_books_id = gbl_set_of_books_id;
' -- Error in populate_history_tab when Inserting';
insert ALL into fv_fund_parameters_history
(fund_parameter_id,
fund_value,
treasury_symbol,
set_of_books_id,
fund_group_code,
fund_category,
fund_time_frame,
sf224_type_code,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
established_fiscal_yr,
treasury_symbol_id,
budget_authority,
unliquid_commitments,
unliquid_obligations,
expended_amount,
red_status,
prior_year_recoveries)
select fund_parameter_id,
fund_value,
treasury_symbol,
set_of_books_id,
fund_group_code,
fund_category,
fund_time_frame,
sf224_type_code,
gbl_last_upd_date,
nvl(gbl_last_update_by,1),
nvl(gbl_last_update_log,1),
gbl_creation_date,
nvl(gbl_created_by,1),
established_fiscal_yr,
treasury_symbol_id,
budget_authority,
unliquid_commitments,
unliquid_obligations,
expended_amount,
red_status,
prior_year_recoveries
from Fv_fund_parameters
where treasury_symbol_id = tres_id
and set_of_books_id = gbl_set_of_books_id;
' -- Error in populate_history_tab while Inserting';
select count(1) into n_cnt
from fv_treasury_symbols_history
where treasury_symbol_id = tres_id
and set_of_books_id = gbl_set_of_books_id
and date_purged is null;
procedure update_history_tab(tres_id number,v_flag varchar2) is
l_module_name VARCHAR2(200) := g_module_name || 'UPDATE_HISTORY_TAB';
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_statement, l_module_name,' inside update_history_tab');
update fv_treasury_symbols_history
set open_flag = v_flag ,
request_id = gbl_request_id,
last_update_date = sysdate,
last_updated_by = gbl_last_update_by,
last_update_login = gbl_last_update_log,
creation_date = sysdate,
created_by = gbl_created_by
where treasury_symbol_id = tres_id
and set_of_books_id = gbl_set_of_books_id;
update fv_treasury_symbols_history
set date_purged = TRUNC(sysdate),
request_id = gbl_request_id,
last_update_date = sysdate,
last_updated_by = gbl_last_update_by,
last_update_login = gbl_last_update_log,
creation_date = sysdate,
created_by = gbl_created_by
where treasury_symbol_id = tres_id
and set_of_books_id = gbl_set_of_books_id
and request_id = gbl_prelim_req_id
and date_purged is null;
update fv_treasury_symbols_history
set date_purged = null,
request_id = gbl_prelim_req_id,
last_update_date = sysdate,
last_updated_by = gbl_last_update_by,
last_update_login = gbl_last_update_log,
creation_date = sysdate,
created_by = gbl_created_by
where set_of_books_id = gbl_set_of_books_id
and request_id = gbl_request_id
and nvl(do_not_purge_flag,'N') = 'N';
end update_history_tab;
procedure ins_delete_treasury_symbols is
n_balance number;
l_module_name VARCHAR2(200):='INS_DELETE_TREASURY_SYMBOLS';
select treasury_symbol,treasury_symbol_id,set_of_books_id,do_not_purge_flag,open_flag
from fv_treasury_symbols_history fts
where set_of_books_id = gbl_set_of_books_id
and treasury_symbol= nvl(gbl_treasury_symbol,treasury_symbol)
and time_frame = nvl(gbl_time_frame,time_frame)
and established_fiscal_yr = NVL(gbl_year_established,established_fiscal_yr)
and nvl(to_char(trunc(cancellation_date)),'0') = NVL(to_char(gbl_cancellation_date),nvl(to_char(TRUNC(cancellation_date)),0))
and date_purged is null
and nvl(do_not_purge_flag,'N') = 'N'
and request_id = gbl_prelim_req_id;
select fund_value
from fv_fund_parameters_history
where set_of_books_id = gbl_set_of_books_id
and treasury_symbol_id = tres_id;*/
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_statement, l_module_name,' Inside ins_delete_treasury_symbols');
update_history_tab(c1.treasury_symbol_id,'D');
' -- Error in Delete_treasury_symbols when deleting treasury symbol' ;
END ins_delete_treasury_symbols;
v_query := 'SELECT NVL(SUM((period_net_dr + begin_balance_dr) - (period_net_cr + begin_balance_cr)),0)
FROM gl_code_combinations gcc,gl_balances gb
WHERE gb.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id =' || gbl_account_id ||
'AND (GB.PERIOD_NUM,GB.PERIOD_YEAR) IN (SELECT MAX(PERIOD_NUM),PERIOD_YEAR
FROM gl_period_statuses
WHERE period_year IN(' || prv_year ||','|| curr_year ||')
AND application_id ='|| 101||'
AND closing_status <>'||'''F'''||
'AND closing_status <>'||'''N'''||
'AND adjustment_period_flag ='||'''N'''||
'AND ledger_id ='|| gbl_set_of_books_id ||
'GROUP BY PERIOD_YEAR)
AND GB.TEMPLATE_ID IS NULL
AND GB.LEDGER_ID =' || gbl_set_of_books_id ||
'AND gb.actual_flag ='||'''A'''||
'AND gcc.'||gbl_bal_segment_name||' = :P_fund_value
AND gcc.'||gbl_acc_segment_name||' = :P_acc_value
AND gcc.enabled_flag='||'''Y''';
SELECT min(start_date), max(end_date)
INTO s_date,e_date
FROM gl_period_statuses
WHERE period_year = curr_year
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND adjustment_period_flag = 'N'
AND ledger_id = gbl_set_of_books_id;
v_query := 'SELECT count(1) FROM gl_je_lines gjl,gl_code_combinations gcc
WHERE gjl.ledger_id =' || gbl_set_of_books_id ||
'AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.chart_of_accounts_id ='|| gbl_account_id ||
'AND gcc.'||gbl_bal_segment_name ||' = :p
AND gcc.enabled_flag = '||'''Y'''||
'AND gcc.account_type ='||'''A'''||
'AND gcc.'||gbl_acc_segment_name||' not like'||'''4350%'''||
'AND gcc.'||gbl_acc_segment_name||' not like'||'''4201%'''||
'AND effective_date between :s_date
AND :e_date
AND gjl.status = '||'''U''';
SELECT period_year
INTO p_year
FROM gl_periods
WHERE period_set_name = gbl_period_set_name
AND adjustment_period_flag = 'N'
GROUP BY PERIOD_YEAR
HAVING sysdate between min(start_date) and max(end_date);
procedure delete_treasury_symbols IS
l_module_name VARCHAR2(200):= 'DELETE_TREASURY_SYMBOLS';
delete from fv_fund_parameters ffp
where exists ( select treasury_symbol_id
from fv_treasury_symbols_history fts
where fts.set_of_books_id = gbl_set_of_books_id
and fts.treasury_symbol_id = ffp.treasury_symbol_id
and fts.request_id = gbl_request_id
and fts.date_purged is not null)
and ffp.set_of_books_id = gbl_set_of_books_id;
' -- Error no-data-found of fund value in delete_treasury_symbols for treasury symbol '||gbl_treasury_symbol_id;
' -- Error in delete_treasury_symbols while deleting Treasury symbols from Fund Parameters';
delete from fv_treasury_symbols fts
where exists ( select treasury_symbol_id
from fv_treasury_symbols_history ftsh
where ftsh.set_of_books_id = gbl_set_of_books_id
and ftsh.treasury_symbol_id = fts.treasury_symbol_id
and ftsh.request_id = gbl_request_id
and ftsh.date_purged is not null)
and fts.set_of_books_id = gbl_set_of_books_id;
' -- Error no-data-found in delete_treasury_symbols ';
' -- Error in delete_treasury_symbols while deleting Treasury symbols';
end delete_treasury_symbols;
delete from fv_fund_parameters_history ffp
where ffp.set_of_books_id = gbl_set_of_books_id
and exists ( select treasury_symbol_id
from fv_treasury_symbols_history fts
where fts.set_of_books_id= gbl_set_of_books_id
and fts.treasury_symbol_id = ffp.treasury_symbol_id
and date_purged is null);
delete from fv_treasury_symbols_history fts
where fts.set_of_books_id= gbl_set_of_books_id
and date_purged is null;