[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(MC_SOURCE_FLAG,'N') from FA_BOOK_CONTROLS
where book_type_code = p_book_type_code;
SELECT code_combination_id
FROM gl_code_combinations_kfv
WHERE chart_of_accounts_id = p_acct_flex_struct
AND concatenated_segments = p_account;
SELECT application_column_name
FROM fnd_segment_attribute_values fndsav
WHERE fndsav.id_flex_code = 'GL#'
AND fndsav.segment_attribute_type = 'GL_ACCOUNT'
AND fndsav.attribute_value = 'Y'
AND application_id = 101
AND fndsav.id_flex_num = p_acct_flex_struct;
'SELECT '
|| v_appl_col_name
|| ' from GL_CODE_COMBINATIONS_KFV where chart_of_accounts_id = '
|| p_acct_flex_struct
|| ' and code_combination_id = '
|| p_ccid;
SELECT category_flex_structure
INTO v_cat_struct_id
FROM fa_system_controls;
SELECT DISTINCT attribute_category_code
FROM fa_additions_v
WHERE asset_category_id = p_category_id;
select itf.asset_id,
itf.impairment_id,
th.transaction_header_id,
adj.adjustment_type,
nvl(decode(adj.debit_credit_flag,'DR',(-1*adj.adjustment_amount)),0) adjustment_amount,
det.distribution_id
from
fa_itf_impairments itf,
fa_impairments imp,
fa_transaction_headers th,
fa_adjustments adj,
fa_deprn_detail det
where itf.impairment_id = imp.impairment_id
and itf.impairment_id = th.mass_transaction_id
and th.transaction_header_id = adj.transaction_header_id
and adj.distribution_id = det.distribution_id
and det.period_counter = p_period_counter
and det.period_counter = itf.period_counter
and det.asset_id = itf.asset_id
and itf.asset_id = p_asset_id
and det.book_type_code = p_book_type_code
and imp.status = 'POSTED'
and adj.adjustment_type = 'REVAL RESERVE'
and adj.source_type_code = 'ADJUSTMENT'
and substr(imp.description,1,3) = 'CEB'
and det.distribution_id = p_dist_id;
select itf.asset_id,
itf.impairment_id,
th.transaction_header_id,
adj.adjustment_type,
nvl(decode(adj.debit_credit_flag,'DR',(-1*adj.adjustment_amount)),0) adjustment_amount
from
fa_itf_impairments itf,
fa_impairments imp,
fa_transaction_headers th,
fa_adjustments adj,
fa_deprn_summary su
where itf.impairment_id = imp.impairment_id
and itf.impairment_id = th.mass_transaction_id
and th.transaction_header_id = adj.transaction_header_id
and su.period_counter = itf.period_counter
and itf.asset_id = su.asset_id
and su.period_counter = p_period_counter
and itf.asset_id = p_asset_id
and su.book_type_code = p_book_type_code
and imp.status = 'POSTED'
and adj.adjustment_type = 'REVAL RESERVE'
and adj.source_type_code = 'ADJUSTMENT'
and substr(imp.description,1,3) = 'CEB';
select itf.asset_id,
itf.impairment_id,
th.transaction_header_id,
adj.adjustment_type,
nvl(decode(adj.debit_credit_flag,'DR',(-1*adj.adjustment_amount)),0) adjustment_amount,
det.distribution_id
from
FA_MC_ITF_IMPAIRMENTS itf,
FA_MC_IMPAIRMENTS imp,
fa_transaction_headers th,
fa_adjustments adj,
FA_MC_DEPRN_DETAIL det
where itf.impairment_id = imp.impairment_id
and itf.impairment_id = th.mass_transaction_id
and th.transaction_header_id = adj.transaction_header_id
and adj.distribution_id = det.distribution_id
and det.period_counter = p_period_counter
and det.period_counter = itf.period_counter
and det.asset_id = itf.asset_id
and itf.asset_id = p_asset_id
and det.book_type_code = p_book_type_code
and imp.status = 'POSTED'
and adj.adjustment_type = 'REVAL RESERVE'
and adj.source_type_code = 'ADJUSTMENT'
and substr(imp.description,1,3) = 'CEB'
and det.distribution_id = p_dist_id
and det.set_of_books_id = NVL(p_set_of_books_id,det.set_of_books_id);
select itf.asset_id,
itf.impairment_id,
th.transaction_header_id,
adj.adjustment_type,
nvl(decode(adj.debit_credit_flag,'DR',(-1*adj.adjustment_amount)),0) adjustment_amount
from
FA_MC_ITF_IMPAIRMENTS itf,
FA_MC_IMPAIRMENTS imp,
fa_transaction_headers th,
fa_adjustments adj,
FA_MC_DEPRN_SUMMARY su
where itf.impairment_id = imp.impairment_id
and itf.impairment_id = th.mass_transaction_id
and th.transaction_header_id = adj.transaction_header_id
and su.period_counter = itf.period_counter
and itf.asset_id = su.asset_id
and su.period_counter = p_period_counter
and itf.asset_id = p_asset_id
and su.book_type_code = p_book_type_code
and imp.status = 'POSTED'
and adj.adjustment_type = 'REVAL RESERVE'
and adj.source_type_code = 'ADJUSTMENT'
and substr(imp.description,1,3) = 'CEB'
and su.set_of_books_id = NVL(p_set_of_books_id,su.set_of_books_id);
select NVL(SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',-1 * ADJUSTMENT_AMOUNT,ADJUSTMENT_AMOUNT)),0)
into l_reval_reserve_ca
from fa_adjustments
where asset_id = p_asset_id
and distribution_id = nvl(p_dist_id,distribution_id)
and period_counter_created = p_period_counter
and book_type_code = p_book_type_code
and source_type_code = 'RETIREMENT'
and adjustment_type = 'REVAL RESERVE';
select NVL(SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',ADJUSTMENT_AMOUNT,-1 * ADJUSTMENT_AMOUNT)),0)
into l_nbv_retired_ca
from fa_adjustments
where asset_id = p_asset_id
and distribution_id = nvl(p_dist_id,distribution_id)
and period_counter_created = p_period_counter
and book_type_code = p_book_type_code
and source_type_code = 'RETIREMENT'
and adjustment_type = 'NBV RETIRED';
SELECT
sum(nvl(capital_adjustment, 0)),
sum(nvl(general_fund, 0))
INTO
l_old_dist_ca,
l_old_dist_gf
FROM FA_DISTRIBUTION_HISTORY dh_old,
FA_DEPRN_DETAIL sumold
WHERE dh_old.distribution_id = sumold.distribution_id
AND dh_old.book_type_code = sumold.book_type_code
AND dh_old.asset_id = sumold.asset_id
ANd dh_old.retirement_id is not null
AND EXISTS
(SELECT 1
FROM FA_DISTRIBUTION_HISTORY dh_new
WHERE dh_new.distribution_id = p_dist_id
AND dh_new.asset_id = dh_old.asset_id
AND dh_new.transaction_header_id_in = dh_old.transaction_header_id_out
AND dh_new.location_id = dh_old.location_id
AND nvl(dh_new.assigned_to, -99) = nvl(dh_old.assigned_to, -99)
AND dh_new.code_combination_id = dh_old.code_combination_id
AND dh_new.book_type_code = dh_old.book_type_code
)
AND sumold.period_counter =
(SELECT MAX(period_counter)
FROM FA_DEPRN_DETAIL
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND distribution_id = p_dist_id
AND period_counter < p_period_counter);
select NVL(SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',-1 * ADJUSTMENT_AMOUNT,ADJUSTMENT_AMOUNT)),0)
into l_reval_reserve_ca
from fa_mc_adjustments
where asset_id = p_asset_id
and distribution_id = nvl(p_dist_id,distribution_id)
and period_counter_created = p_period_counter
and book_type_code = p_book_type_code
and source_type_code = 'RETIREMENT'
and adjustment_type = 'REVAL RESERVE'
and set_of_books_id = p_set_of_books_id;
select NVL(SUM(DECODE(DEBIT_CREDIT_FLAG,'DR',ADJUSTMENT_AMOUNT,-1 * ADJUSTMENT_AMOUNT)),0)
into l_nbv_retired_ca
from fa_mc_adjustments
where asset_id = p_asset_id
and distribution_id = nvl(p_dist_id,distribution_id)
and period_counter_created = p_period_counter
and book_type_code = p_book_type_code
and source_type_code = 'RETIREMENT'
and adjustment_type = 'NBV RETIRED'
and set_of_books_id = p_set_of_books_id;
SELECT
sum(nvl(capital_adjustment, 0)),
sum(nvl(general_fund, 0))
INTO
l_old_dist_ca,
l_old_dist_gf
FROM FA_DISTRIBUTION_HISTORY dh_old,
FA_MC_DEPRN_DETAIL sumold
WHERE dh_old.distribution_id = sumold.distribution_id
AND dh_old.book_type_code = sumold.book_type_code
AND dh_old.asset_id = sumold.asset_id
ANd dh_old.retirement_id is not null
AND sumold.set_of_books_id = p_set_of_books_id
AND EXISTS
(SELECT 1
FROM FA_DISTRIBUTION_HISTORY dh_new
WHERE dh_new.distribution_id = p_dist_id
AND dh_new.asset_id = dh_old.asset_id
AND dh_new.transaction_header_id_in = dh_old.transaction_header_id_out
AND dh_new.location_id = dh_old.location_id
AND nvl(dh_new.assigned_to, -99) = nvl(dh_old.assigned_to, -99)
AND dh_new.code_combination_id = dh_old.code_combination_id
AND dh_new.book_type_code = dh_old.book_type_code
)
AND sumold.period_counter =
(SELECT MAX(period_counter)
FROM FA_MC_DEPRN_DETAIL
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
AND distribution_id = p_dist_id
AND period_counter < p_period_counter
AND set_of_books_id=p_set_of_books_id);
SELECT book_type_code, category_id, reval_amortization_acct,
reval_amort_account_ccid, impair_expense_acct,
impair_expense_account_ccid, impair_reserve_acct,
impair_reserve_account_ccid
FROM fa_category_books
WHERE book_type_code = p_book
AND (capital_adj_acct IS NULL OR general_fund_acct IS NULL);
SELECT category_id, capital_adj_acct, capital_adj_account_ccid,
general_fund_acct, general_fund_account_ccid
FROM fa_sorp_upg_cat
WHERE book_type_code = p_book AND validation_flag <> 'I';
DELETE FROM fa_sorp_upg_cat;
INSERT INTO fa_sorp_upg_cat
(book_type_code,
category_id,
reval_amortization_acct,
reval_amort_account_ccid,
impair_expense_acct,
impair_expense_account_ccid,
impair_reserve_acct,
impair_reserve_account_ccid,
capital_adj_acct, capital_adj_account_ccid,
general_fund_acct, general_fund_account_ccid,
validation_flag, category_flex, run_mode,
run_date
)
VALUES (v_category_tab (i).book_type_code,
v_category_tab (i).category_id,
v_category_tab (i).reval_amortization_acct,
v_category_tab (i).reval_amort_account_ccid,
v_category_tab (i).impair_expense_acct,
v_category_tab (i).impair_expense_account_ccid,
v_category_tab (i).impair_reserve_acct,
v_category_tab (i).impair_reserve_account_ccid,
v_capital_adj_acct, v_capital_adj_ccid,
v_general_fund_acct, v_general_fund_ccid,
l_imp_acct_chk_flag, l_category_name, p_run_mode,
SYSDATE
);
INSERT INTO fa_sorp_upg_cat
(book_type_code,
category_id,
reval_amortization_acct,
reval_amort_account_ccid,
impair_expense_acct,
impair_expense_account_ccid,
impair_reserve_acct,
impair_reserve_account_ccid,
capital_adj_acct, capital_adj_account_ccid,
general_fund_acct, general_fund_account_ccid,
validation_flag, category_flex,
run_mode, run_date
)
VALUES (v_category_tab (i).book_type_code,
v_category_tab (i).category_id,
v_category_tab (i).reval_amortization_acct,
v_category_tab (i).reval_amort_account_ccid,
v_category_tab (i).impair_expense_acct,
v_category_tab (i).impair_expense_account_ccid,
v_category_tab (i).impair_reserve_acct,
v_category_tab (i).impair_reserve_account_ccid,
v_capital_adj_acct, v_capital_adj_ccid,
v_general_fund_acct, v_general_fund_ccid,
l_reval_amort_acct_chk_flag, l_category_name,
p_run_mode, SYSDATE
);
INSERT INTO fa_sorp_upg_cat
(book_type_code,
category_id,
reval_amortization_acct,
reval_amort_account_ccid,
impair_expense_acct,
impair_expense_account_ccid,
impair_reserve_acct,
impair_reserve_account_ccid,
capital_adj_acct, capital_adj_account_ccid,
general_fund_acct, general_fund_account_ccid,
validation_flag, category_flex, run_mode,
run_date
)
VALUES (v_category_tab (i).book_type_code,
v_category_tab (i).category_id,
v_category_tab (i).reval_amortization_acct,
v_category_tab (i).reval_amort_account_ccid,
v_category_tab (i).impair_expense_acct,
v_category_tab (i).impair_expense_account_ccid,
v_category_tab (i).impair_reserve_acct,
v_category_tab (i).impair_reserve_account_ccid,
v_capital_adj_acct, v_capital_adj_ccid,
v_general_fund_acct, v_general_fund_ccid,
l_success_chk_flag, l_category_name, p_run_mode,
SYSDATE
);
UPDATE fa_category_books
SET capital_adj_acct = v_final_cur.capital_adj_acct,
capital_adj_account_ccid =
v_final_cur.capital_adj_account_ccid,
general_fund_acct = v_final_cur.general_fund_acct,
general_fund_account_ccid =
v_final_cur.general_fund_account_ccid,
last_update_date = SYSDATE
WHERE book_type_code = p_book
AND category_id = v_final_cur.category_id;
SELECT fadep.period_counter, facalp.period_name
FROM fa_calendar_periods facalp,
fa_deprn_periods fadep,
fa_book_controls fabkctl
WHERE facalp.calendar_type = fabkctl.deprn_calendar
AND fabkctl.book_type_code = p_book_type_code
AND fadep.book_type_code = fabkctl.book_type_code
AND facalp.period_name = fadep.period_name
AND TO_DATE ('01-04-07', 'DD-MM-YY')
BETWEEN TO_DATE (TO_CHAR (facalp.start_date, 'DD-MM-YY'),
'DD-MM-YY'
)
AND TO_DATE (TO_CHAR (facalp.end_date, 'DD-MM-YY'),
'DD-MM-YY'
);
SELECT DISTINCT dep.asset_id, adda.asset_number,
adda.asset_category_id,
adda.attribute_category_code category_name
FROM fa_deprn_summary dep, fa_additions_v adda
WHERE adda.asset_id = dep.asset_id
AND book_type_code = p_book_type_code;
SELECT reval_reserve
FROM (SELECT dep.reval_reserve
FROM fa_deprn_summary dep
WHERE dep.book_type_code = p_book_type_code
AND dep.asset_id = v_asset_id
AND dep.period_counter < v_period_counter
ORDER BY dep.period_counter DESC)
WHERE ROWNUM < 2;
SELECT dep.reval_reserve
FROM fa_deprn_summary dep
WHERE dep.book_type_code = p_book_type_code
AND dep.asset_id = v_asset_id
AND dep.period_counter =
(SELECT MAX (period_counter)
FROM fa_deprn_summary
WHERE book_type_code = p_book_type_code
AND asset_id = v_asset_id
GROUP BY asset_id);
DELETE FROM fa_sorp_reval_chk;
INSERT INTO fa_sorp_reval_chk
(book_type_code, category_id, category_name,
asset_id, asset_number, apr_reval_rsv,
reval_rsv, status_code, status
)
VALUES (p_book_type_code, v_category_id, v_category_name,
v_asset_id, v_asset_number, v_apr_reval_rsv,
v_reval_rsv, l_status_code, l_message
);
SELECT DISTINCT adda.asset_id, adda.asset_number,
adda.description asset_description,
adda.asset_category_id,
adda.attribute_category_code category_name
FROM FA_MC_DEPRN_SUMMARY dep, fa_additions_v adda
WHERE adda.asset_id = dep.asset_id
AND book_type_code = p_book_type_code;
SELECT date_placed_in_service, COST current_cost
FROM fa_books_v
WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id;
SELECT distribution_id, deprn_reserve, deprn_amount,
reval_amortization, impairment_amount, period_counter,set_of_books_id
FROM FA_MC_DEPRN_DETAIL
WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id
ORDER BY SET_OF_BOOKS_ID ASC,period_counter;
SELECT deprn_reserve, deprn_amount, reval_amortization,
impairment_amount, period_counter,set_of_books_id
FROM FA_MC_DEPRN_SUMMARY
WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id
ORDER BY SET_OF_BOOKS_ID ASC,period_counter;
select book_type_code ,
asset_id,
asset_number ,
asset_description,
date_placed_in_service,
category_name,
current_cost,
depriciation_reserve,
revaluation_reserve,
impairment_reserve,
capital_adjustment_acct_amount,
general_fund_acct_amount
from fa_sorp_upg_cagf;
DELETE FROM fa_sorp_upg_cagf;
UPDATE FA_MC_DEPRN_DETAIL
SET capital_adjustment = v_capital_adj_amount,
general_fund = v_general_fund_amount
WHERE distribution_id = v_deprn_cur.distribution_id
AND book_type_code = p_book_type_code
AND period_counter = v_deprn_cur.period_counter
AND set_of_books_id = v_deprn_cur.set_of_books_id;
UPDATE FA_MC_DEPRN_DETAIL
SET capital_adjustment = v_capital_adj_amount,
general_fund = v_general_fund_amount
WHERE distribution_id = v_deprn_cur.distribution_id
AND book_type_code = p_book_type_code
AND period_counter = v_deprn_cur.period_counter
AND set_of_books_id = v_deprn_cur.set_of_books_id;
UPDATE FA_MC_DEPRN_SUMMARY
SET capital_adjustment = v_capital_adj_summary_amount,
general_fund = v_general_fund_summary_amount
WHERE asset_id = v_asset_id
AND book_type_code = p_book_type_code
AND period_counter = v_deprn_summary_cur.period_counter
AND set_of_books_id = v_deprn_summary_cur.set_of_books_id;
UPDATE FA_MC_DEPRN_SUMMARY
SET capital_adjustment = v_capital_adj_summary_amount,
general_fund = v_general_fund_summary_amount
WHERE asset_id = v_asset_id
AND book_type_code = p_book_type_code
AND period_counter = v_deprn_summary_cur.period_counter
AND set_of_books_id = v_deprn_summary_cur.set_of_books_id;
INSERT INTO fa_sorp_upg_cagf
(book_type_code, asset_id, asset_number,
asset_description,
date_placed_in_service,
category_name, current_cost,
depriciation_reserve, revaluation_reserve,
impairment_reserve, capital_adjustment_acct_amount,
general_fund_acct_amount
)
VALUES (p_book_type_code, v_asset_id, v_asset_cur.asset_number,
v_asset_cur.asset_description,
v_book_cur.date_placed_in_service,
v_asset_cur.category_name, v_book_cur.current_cost,
l_deprn_rsv, l_reval_rsv,
l_impairment_rsv, v_capital_adj_amount,
v_general_fund_amount
);
select period_name into v_period_name
from fa_deprn_periods
where book_type_code = P_FA_BOOK
and period_close_date is null;
INSERT INTO fa_sorp_upg_cagf_hist
(book_type_code,
asset_id,
asset_number,
asset_description,
date_placed_in_service,
category_name,
current_cost,
depriciation_reserve,
revaluation_reserve,
impairment_reserve,
capital_adjustment_acct_amount,
general_fund_acct_amount,
request_id,
report_mode,
period_name
)
VALUES
(v_fa_sorp_upg_cagf_hist_cur.book_type_code,
v_fa_sorp_upg_cagf_hist_cur.asset_id,
v_fa_sorp_upg_cagf_hist_cur.asset_number,
v_fa_sorp_upg_cagf_hist_cur.asset_description,
v_fa_sorp_upg_cagf_hist_cur.date_placed_in_service,
v_fa_sorp_upg_cagf_hist_cur.category_name,
v_fa_sorp_upg_cagf_hist_cur.current_cost,
v_fa_sorp_upg_cagf_hist_cur.depriciation_reserve,
v_fa_sorp_upg_cagf_hist_cur.revaluation_reserve,
v_fa_sorp_upg_cagf_hist_cur.impairment_reserve,
v_fa_sorp_upg_cagf_hist_cur.capital_adjustment_acct_amount,
v_fa_sorp_upg_cagf_hist_cur.general_fund_acct_amount,
l_request_id,
p_mode,
v_period_name);
SELECT DISTINCT adda.asset_id, adda.asset_number,
adda.description asset_description,
adda.asset_category_id,
adda.attribute_category_code category_name
FROM fa_deprn_summary dep, fa_additions_v adda
WHERE adda.asset_id = dep.asset_id
AND book_type_code = p_book_type_code;
SELECT date_placed_in_service, COST current_cost
FROM fa_books_v
WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id;
SELECT distribution_id, deprn_reserve, deprn_amount,
reval_amortization, impairment_amount, period_counter
FROM fa_deprn_detail
WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id
ORDER BY period_counter;
SELECT deprn_reserve, deprn_amount, reval_amortization,
impairment_amount, period_counter
FROM fa_deprn_summary
WHERE book_type_code = p_book_type_code AND asset_id = v_asset_id
ORDER BY period_counter;
select book_type_code ,
asset_id,
asset_number ,
asset_description,
date_placed_in_service,
category_name,
current_cost,
depriciation_reserve,
revaluation_reserve,
impairment_reserve,
capital_adjustment_acct_amount,
general_fund_acct_amount
from fa_sorp_upg_cagf;
DELETE FROM fa_sorp_upg_cagf;
select count(1) into v_final_cnt
from fa_sorp_upg_cagf_hist
where book_type_code = p_book_type_code
and report_mode = p_mode;
UPDATE fa_deprn_detail
SET capital_adjustment = v_capital_adj_amount,
general_fund = v_general_fund_amount
WHERE distribution_id = v_deprn_cur.distribution_id
AND book_type_code = p_book_type_code
AND period_counter = v_deprn_cur.period_counter;
UPDATE fa_deprn_detail
SET capital_adjustment = v_capital_adj_amount,
general_fund = v_general_fund_amount
WHERE distribution_id = v_deprn_cur.distribution_id
AND book_type_code = p_book_type_code
AND period_counter = v_deprn_cur.period_counter;
UPDATE fa_deprn_summary
SET capital_adjustment = v_capital_adj_summary_amount,
general_fund = v_general_fund_summary_amount
WHERE asset_id = v_asset_id
AND book_type_code = p_book_type_code
AND period_counter = v_deprn_summary_cur.period_counter;
UPDATE fa_deprn_summary
SET capital_adjustment = v_capital_adj_summary_amount,
general_fund = v_general_fund_summary_amount
WHERE asset_id = v_asset_id
AND book_type_code = p_book_type_code
AND period_counter = v_deprn_summary_cur.period_counter;
INSERT INTO fa_sorp_upg_cagf
(book_type_code, asset_id, asset_number,
asset_description,
date_placed_in_service,
category_name, current_cost,
depriciation_reserve, revaluation_reserve,
impairment_reserve, capital_adjustment_acct_amount,
general_fund_acct_amount
)
VALUES (p_book_type_code, v_asset_id, v_asset_cur.asset_number,
v_asset_cur.asset_description,
v_book_cur.date_placed_in_service,
v_asset_cur.category_name, v_book_cur.current_cost,
l_deprn_rsv, l_reval_rsv,
l_impairment_rsv, v_capital_adj_amount,
v_general_fund_amount
);
select period_name into v_period_name
from fa_deprn_periods
where book_type_code = P_FA_BOOK
and period_close_date is null;
INSERT INTO fa_sorp_upg_cagf_hist
(book_type_code,
asset_id,
asset_number,
asset_description,
date_placed_in_service,
category_name,
current_cost,
depriciation_reserve,
revaluation_reserve,
impairment_reserve,
capital_adjustment_acct_amount,
general_fund_acct_amount,
request_id,
report_mode,
period_name
)
VALUES
(v_fa_sorp_upg_cagf_hist_cur.book_type_code,
v_fa_sorp_upg_cagf_hist_cur.asset_id,
v_fa_sorp_upg_cagf_hist_cur.asset_number,
v_fa_sorp_upg_cagf_hist_cur.asset_description,
v_fa_sorp_upg_cagf_hist_cur.date_placed_in_service,
v_fa_sorp_upg_cagf_hist_cur.category_name,
v_fa_sorp_upg_cagf_hist_cur.current_cost,
v_fa_sorp_upg_cagf_hist_cur.depriciation_reserve,
v_fa_sorp_upg_cagf_hist_cur.revaluation_reserve,
v_fa_sorp_upg_cagf_hist_cur.impairment_reserve,
v_fa_sorp_upg_cagf_hist_cur.capital_adjustment_acct_amount,
v_fa_sorp_upg_cagf_hist_cur.general_fund_acct_amount,
l_request_id,
p_mode,
v_period_name);
CURSOR c_imp_asset_cur IS SELECT 'Impairment' transaction_type,
i.asset_id,
a.asset_number,
a.description asset_description, a.asset_category_id,
a.attribute_category_code category_name, i.impairment_id,
i.impairment_name,
NVL (i.description, 'Others') imp_description,
-- Bug#7704219
NVL (
decode(SUBSTR (i.description, 1, 3),'CPP','CPP',
'CEB','CEB',
'OTH'),
'OTH'
) impair_classification_type
FROM fa_additions_v a, FA_MC_IMPAIRMENTS i
WHERE a.asset_id = i.asset_id AND i.book_type_code = p_book;
SELECT impairment_amount
FROM FA_MC_ITF_IMPAIRMENTS
WHERE impairment_id = v_impairment_id
AND asset_id = v_asset_id
AND book_type_code = p_book;
select capital_adjustment,
general_fund
from FA_MC_DEPRN_SUMMARY
where book_type_code = l_book
and asset_id = l_asset_id
and period_counter = (select max(period_counter)
from FA_MC_DEPRN_SUMMARY
where book_type_code = l_book
and asset_id = l_asset_id
and period_counter < l_period_counter);
SELECT 'Impairment' transaction_type, i.cash_generating_unit_id,
a.cash_generating_unit, a.description asset_description,
NULL asset_category_id, NULL category_name, i.impairment_id,
i.impairment_name,
NVL (i.description, 'Others') imp_description,
NVL (SUBSTR (i.description, 1, 3),
'OTH'
) impair_classification_type
FROM fa_cash_gen_units a, FA_MC_IMPAIRMENTS i
WHERE a.cash_generating_unit_id = i.cash_generating_unit_id
AND i.book_type_code = p_book;
SELECT impairment_amount
FROM FA_MC_ITF_IMPAIRMENTS
WHERE impairment_id = v_impairment_id
AND cash_generating_unit_id = v_cash_id
AND book_type_code = p_book;
SELECT DISTINCT mass_reval_id, description
FROM fa_mass_revaluations
WHERE book_type_code = p_book;
SELECT 'Revaluation' transaction_type, r.asset_id, a.asset_number,
a.description asset_description, r.mass_reval_id,
r.reval_percent
FROM fa_additions_v a, fa_mass_revaluation_rules r,fa_mass_revaluations mr
WHERE a.asset_id = r.asset_id
AND r.mass_reval_id = mr.mass_reval_id
AND mr.mass_reval_id = v_mass_reval_id
AND r.category_id IS NULL
AND mr.book_type_code = p_book;
SELECT distinct 'Revaluation' transaction_type,
r.category_id asset_category_id,
a.attribute_category_code category_name, r.mass_reval_id,
r.reval_percent
FROM fa_additions_v a, fa_mass_revaluation_rules r,fa_mass_revaluations mr
WHERE a.asset_category_id = r.category_id
AND r.mass_reval_id = mr.mass_reval_id
AND mr.mass_reval_id = v_mass_reval_id
AND r.asset_id IS NULL
AND mr.book_type_code = p_book;
SELECT ID, impairment_id, imp_description, imp_class_type,
imp_amount, book_type_code
FROM fa_sorp_upg_impreval
WHERE transaction_type = 'Impairment' AND id_type = 'A';
SELECT ID, impairment_id, imp_description, imp_class_type,
imp_amount, book_type_code
FROM fa_sorp_upg_impreval
WHERE transaction_type = 'Impairment' AND id_type = 'C';
SELECT mass_reval_id, ID, reval_reason, book_type_code
FROM fa_sorp_upg_impreval
WHERE transaction_type = 'Revaluation' AND asset_category_id IS NULL;
SELECT mass_reval_id, asset_category_id category_id, reval_reason,
book_type_code
FROM fa_sorp_upg_impreval
WHERE transaction_type = 'Revaluation' AND ID IS NULL;
is select transaction_type ,
id,
name,
description,
asset_category_id,
category_name,
impairment_id,
impairment_name,
imp_description,
imp_class_type,
imp_amount,
mass_reval_id,
reval_percent,
reval_reason,
book_type_code,
report_mode,
id_type
from fa_sorp_upg_impreval;
DELETE FROM fa_sorp_upg_impreval;
select count(1) into v_final_cnt
from fa_sorp_upg_impreval_hist
where book_type_code = p_book
and report_mode = p_mode;
INSERT INTO fa_sorp_upg_impreval
(transaction_type,
ID,
NAME,
description,
asset_category_id,
category_name,
impairment_id,
impairment_name,
imp_description,
imp_class_type,
imp_amount, book_type_code, report_mode,
id_type
)
VALUES (v_imp_asset_cur.transaction_type,
v_imp_asset_cur.asset_id,
v_imp_asset_cur.asset_number,
v_imp_asset_cur.asset_description,
v_imp_asset_cur.asset_category_id,
v_imp_asset_cur.category_name,
v_imp_asset_cur.impairment_id,
v_imp_asset_cur.impairment_name,
v_imp_asset_cur.imp_description,
v_imp_asset_cur.impair_classification_type,
v_imp_itf_asset_cur.impairment_amount,
p_book,
p_mode,
'A'
);
INSERT INTO fa_sorp_upg_impreval
(transaction_type,
ID,
NAME,
description,
asset_category_id,
category_name,
impairment_id,
impairment_name,
imp_description,
imp_class_type,
imp_amount, book_type_code, report_mode,
id_type
)
VALUES (v_imp_cash_cur.transaction_type,
v_imp_cash_cur.cash_generating_unit_id,
v_imp_cash_cur.cash_generating_unit,
v_imp_cash_cur.asset_description,
v_imp_cash_cur.asset_category_id,
v_imp_cash_cur.category_name,
v_imp_cash_cur.impairment_id,
v_imp_cash_cur.impairment_name,
v_imp_cash_cur.imp_description,
v_imp_cash_cur.impair_classification_type,
v_imp_itf_cash_cur.impairment_amount, p_book, p_mode,
'C'
);
INSERT INTO fa_sorp_upg_impreval
(transaction_type,
ID,
NAME,
description,
mass_reval_id, reval_percent,
reval_reason, book_type_code, report_mode
)
VALUES (v_reval_asset_cur.transaction_type,
v_reval_asset_cur.asset_id,
v_reval_asset_cur.asset_number,
v_reval_asset_cur.asset_description,
v_mass_reval_id, v_reval_asset_cur.reval_percent,
v_mass_description, p_book, p_mode
);
INSERT INTO fa_sorp_upg_impreval
(transaction_type,
asset_category_id,
category_name, mass_reval_id,
reval_percent, reval_reason,
book_type_code, report_mode
)
VALUES (v_reval_cat_cur.transaction_type,
v_reval_cat_cur.asset_category_id,
v_reval_cat_cur.category_name, v_mass_reval_id,
v_reval_cat_cur.reval_percent, v_mass_description,
p_book, p_mode
);
select period_counter into l_counter
from fa_mc_itf_impairments
where impairment_id = v_final_imp_asset_cur.impairment_id;
UPDATE FA_MC_IMPAIRMENTS
SET reason = v_final_imp_asset_cur.imp_description,
impair_class = v_final_imp_asset_cur.imp_class_type,
split_impair_flag = 'N'
WHERE book_type_code = p_book
AND impairment_id = v_final_imp_asset_cur.impairment_id
AND asset_id = v_final_imp_asset_cur.ID;
UPDATE FA_MC_ITF_IMPAIRMENTS
SET capital_adjustment = l_capital_amount,
general_fund = l_general_fund_amount,
split_impair_flag = 'N'
WHERE book_type_code = p_book
AND impairment_id = v_final_imp_asset_cur.impairment_id
AND asset_id = v_final_imp_asset_cur.ID;
UPDATE FA_MC_IMPAIRMENTS
SET reason = v_final_imp_cash_cur.imp_description,
impair_class = v_final_imp_cash_cur.imp_class_type,
split_impair_flag = 'N'
WHERE book_type_code = p_book
AND impairment_id = v_final_imp_cash_cur.impairment_id
AND cash_generating_unit_id = v_final_imp_cash_cur.ID;
UPDATE FA_MC_ITF_IMPAIRMENTS
SET capital_adjustment = v_final_imp_cash_cur.imp_amount,
general_fund = v_final_imp_cash_cur.imp_amount,
split_impair_flag = 'N'
WHERE book_type_code = p_book
AND impairment_id = v_final_imp_cash_cur.impairment_id
AND cash_generating_unit_id = v_final_imp_cash_cur.ID;
UPDATE fa_mass_revaluation_rules
SET reason = v_final_reval_asset_cur.reval_reason,
value_type = 'PER',
book_type_code = p_book
WHERE mass_reval_id = v_final_reval_asset_cur.mass_reval_id
AND asset_id = v_final_reval_asset_cur.ID;
UPDATE fa_mass_revaluation_rules
SET reason = v_final_reval_cat_cur.reval_reason,
value_type = 'PER',
book_type_code = p_book
WHERE mass_reval_id = v_final_reval_cat_cur.mass_reval_id
AND category_id = v_final_reval_cat_cur.category_id;
INSERT INTO fa_sorp_upg_impreval_hist
(transaction_type ,
id,
name,
description,
asset_category_id,
category_name,
impairment_id,
impairment_name,
imp_description,
imp_class_type,
imp_amount,
mass_reval_id,
reval_percent,
reval_reason,
book_type_code,
report_mode,
id_type,
request_id)
VALUES
(v_srp_upg_impreval_hist_cur.transaction_type ,
v_srp_upg_impreval_hist_cur.id,
v_srp_upg_impreval_hist_cur.name,
v_srp_upg_impreval_hist_cur.description,
v_srp_upg_impreval_hist_cur.asset_category_id,
v_srp_upg_impreval_hist_cur.category_name,
v_srp_upg_impreval_hist_cur.impairment_id,
v_srp_upg_impreval_hist_cur.impairment_name,
v_srp_upg_impreval_hist_cur.imp_description,
v_srp_upg_impreval_hist_cur.imp_class_type,
v_srp_upg_impreval_hist_cur.imp_amount,
v_srp_upg_impreval_hist_cur.mass_reval_id,
v_srp_upg_impreval_hist_cur.reval_percent,
v_srp_upg_impreval_hist_cur.reval_reason,
v_srp_upg_impreval_hist_cur.book_type_code,
v_srp_upg_impreval_hist_cur.report_mode,
v_srp_upg_impreval_hist_cur.id_type,
l_request_id);
CURSOR c_imp_asset_cur IS SELECT 'Impairment' transaction_type,
i.asset_id,
a.asset_number,
a.description asset_description, a.asset_category_id,
a.attribute_category_code category_name, i.impairment_id,
i.impairment_name,
NVL (i.description, 'Others') imp_description,
-- Bug#7704219
NVL (
decode(SUBSTR (i.description, 1, 3),'CPP','CPP',
'CEB','CEB',
'OTH'),
'OTH'
) impair_classification_type
FROM fa_additions_v a, fa_impairments i
WHERE a.asset_id = i.asset_id AND i.book_type_code = p_book;
SELECT impairment_amount
FROM fa_itf_impairments
WHERE impairment_id = v_impairment_id
AND asset_id = v_asset_id
AND book_type_code = p_book;
select capital_adjustment,
general_fund
from fa_deprn_summary
where book_type_code = l_book
and asset_id = l_asset_id
and period_counter = (select max(period_counter)
from fa_deprn_summary
where book_type_code = l_book
and asset_id = l_asset_id
and period_counter < l_period_counter);
SELECT 'Impairment' transaction_type, i.cash_generating_unit_id,
a.cash_generating_unit, a.description asset_description,
NULL asset_category_id, NULL category_name, i.impairment_id,
i.impairment_name,
NVL (i.description, 'Others') imp_description,
NVL (SUBSTR (i.description, 1, 3),
'OTH'
) impair_classification_type
FROM fa_cash_gen_units a, fa_impairments i
WHERE a.cash_generating_unit_id = i.cash_generating_unit_id
AND i.book_type_code = p_book;
SELECT impairment_amount
FROM fa_itf_impairments
WHERE impairment_id = v_impairment_id
AND cash_generating_unit_id = v_cash_id
AND book_type_code = p_book;
SELECT DISTINCT mass_reval_id, description
FROM fa_mass_revaluations
WHERE book_type_code = p_book;
SELECT 'Revaluation' transaction_type, r.asset_id, a.asset_number,
a.description asset_description, r.mass_reval_id,
r.reval_percent
FROM fa_additions_v a, fa_mass_revaluation_rules r,fa_mass_revaluations mr
WHERE a.asset_id = r.asset_id
AND r.mass_reval_id = mr.mass_reval_id
AND mr.mass_reval_id = v_mass_reval_id
AND r.category_id IS NULL
AND mr.book_type_code = p_book;
SELECT distinct 'Revaluation' transaction_type,
r.category_id asset_category_id,
a.attribute_category_code category_name, r.mass_reval_id,
r.reval_percent
FROM fa_additions_v a, fa_mass_revaluation_rules r,fa_mass_revaluations mr
WHERE a.asset_category_id = r.category_id
AND r.mass_reval_id = mr.mass_reval_id
AND mr.mass_reval_id = v_mass_reval_id
AND r.asset_id IS NULL
AND mr.book_type_code = p_book;
SELECT ID, impairment_id, imp_description, imp_class_type,
imp_amount, book_type_code
FROM fa_sorp_upg_impreval
WHERE transaction_type = 'Impairment' AND id_type = 'A';
SELECT ID, impairment_id, imp_description, imp_class_type,
imp_amount, book_type_code
FROM fa_sorp_upg_impreval
WHERE transaction_type = 'Impairment' AND id_type = 'C';
SELECT mass_reval_id, ID, reval_reason, book_type_code
FROM fa_sorp_upg_impreval
WHERE transaction_type = 'Revaluation' AND asset_category_id IS NULL;
SELECT mass_reval_id, asset_category_id category_id, reval_reason,
book_type_code
FROM fa_sorp_upg_impreval
WHERE transaction_type = 'Revaluation' AND ID IS NULL;
is select transaction_type ,
id,
name,
description,
asset_category_id,
category_name,
impairment_id,
impairment_name,
imp_description,
imp_class_type,
imp_amount,
mass_reval_id,
reval_percent,
reval_reason,
book_type_code,
report_mode,
id_type
from fa_sorp_upg_impreval;
DELETE FROM fa_sorp_upg_impreval;
select count(1) into v_final_cnt
from fa_sorp_upg_impreval_hist
where book_type_code = p_book
and report_mode = p_mode;
INSERT INTO fa_sorp_upg_impreval
(transaction_type,
ID,
NAME,
description,
asset_category_id,
category_name,
impairment_id,
impairment_name,
imp_description,
imp_class_type,
imp_amount, book_type_code, report_mode,
id_type
)
VALUES (v_imp_asset_cur.transaction_type,
v_imp_asset_cur.asset_id,
v_imp_asset_cur.asset_number,
v_imp_asset_cur.asset_description,
v_imp_asset_cur.asset_category_id,
v_imp_asset_cur.category_name,
v_imp_asset_cur.impairment_id,
v_imp_asset_cur.impairment_name,
v_imp_asset_cur.imp_description,
v_imp_asset_cur.impair_classification_type,
v_imp_itf_asset_cur.impairment_amount,
p_book,
p_mode,
'A'
);
INSERT INTO fa_sorp_upg_impreval
(transaction_type,
ID,
NAME,
description,
asset_category_id,
category_name,
impairment_id,
impairment_name,
imp_description,
imp_class_type,
imp_amount, book_type_code, report_mode,
id_type
)
VALUES (v_imp_cash_cur.transaction_type,
v_imp_cash_cur.cash_generating_unit_id,
v_imp_cash_cur.cash_generating_unit,
v_imp_cash_cur.asset_description,
v_imp_cash_cur.asset_category_id,
v_imp_cash_cur.category_name,
v_imp_cash_cur.impairment_id,
v_imp_cash_cur.impairment_name,
v_imp_cash_cur.imp_description,
v_imp_cash_cur.impair_classification_type,
v_imp_itf_cash_cur.impairment_amount, p_book, p_mode,
'C'
);
INSERT INTO fa_sorp_upg_impreval
(transaction_type,
ID,
NAME,
description,
mass_reval_id, reval_percent,
reval_reason, book_type_code, report_mode
)
VALUES (v_reval_asset_cur.transaction_type,
v_reval_asset_cur.asset_id,
v_reval_asset_cur.asset_number,
v_reval_asset_cur.asset_description,
v_mass_reval_id, v_reval_asset_cur.reval_percent,
v_mass_description, p_book, p_mode
);
INSERT INTO fa_sorp_upg_impreval
(transaction_type,
asset_category_id,
category_name, mass_reval_id,
reval_percent, reval_reason,
book_type_code, report_mode
)
VALUES (v_reval_cat_cur.transaction_type,
v_reval_cat_cur.asset_category_id,
v_reval_cat_cur.category_name, v_mass_reval_id,
v_reval_cat_cur.reval_percent, v_mass_description,
p_book, p_mode
);
select period_counter into l_counter
from fa_itf_impairments
where impairment_id = v_final_imp_asset_cur.impairment_id;
UPDATE fa_impairments
SET reason = v_final_imp_asset_cur.imp_description,
impair_class = v_final_imp_asset_cur.imp_class_type,
split_impair_flag = 'N'
WHERE book_type_code = p_book
AND impairment_id = v_final_imp_asset_cur.impairment_id
AND asset_id = v_final_imp_asset_cur.ID;
UPDATE fa_itf_impairments
SET capital_adjustment = l_capital_amount,
general_fund = l_general_fund_amount,
split_impair_flag = 'N'
WHERE book_type_code = p_book
AND impairment_id = v_final_imp_asset_cur.impairment_id
AND asset_id = v_final_imp_asset_cur.ID;
UPDATE fa_impairments
SET reason = v_final_imp_cash_cur.imp_description,
impair_class = v_final_imp_cash_cur.imp_class_type,
split_impair_flag = 'N'
WHERE book_type_code = p_book
AND impairment_id = v_final_imp_cash_cur.impairment_id
AND cash_generating_unit_id = v_final_imp_cash_cur.ID;
UPDATE fa_itf_impairments
SET capital_adjustment = v_final_imp_cash_cur.imp_amount,
general_fund = v_final_imp_cash_cur.imp_amount,
split_impair_flag = 'N'
WHERE book_type_code = p_book
AND impairment_id = v_final_imp_cash_cur.impairment_id
AND cash_generating_unit_id = v_final_imp_cash_cur.ID;
UPDATE fa_mass_revaluation_rules
SET reason = v_final_reval_asset_cur.reval_reason,
value_type = 'PER',
book_type_code = p_book
WHERE mass_reval_id = v_final_reval_asset_cur.mass_reval_id
AND asset_id = v_final_reval_asset_cur.ID;
UPDATE fa_mass_revaluation_rules
SET reason = v_final_reval_cat_cur.reval_reason,
value_type = 'PER',
book_type_code = p_book
WHERE mass_reval_id = v_final_reval_cat_cur.mass_reval_id
AND category_id = v_final_reval_cat_cur.category_id;
INSERT INTO fa_sorp_upg_impreval_hist
(transaction_type ,
id,
name,
description,
asset_category_id,
category_name,
impairment_id,
impairment_name,
imp_description,
imp_class_type,
imp_amount,
mass_reval_id,
reval_percent,
reval_reason,
book_type_code,
report_mode,
id_type,
request_id)
VALUES
(v_srp_upg_impreval_hist_cur.transaction_type ,
v_srp_upg_impreval_hist_cur.id,
v_srp_upg_impreval_hist_cur.name,
v_srp_upg_impreval_hist_cur.description,
v_srp_upg_impreval_hist_cur.asset_category_id,
v_srp_upg_impreval_hist_cur.category_name,
v_srp_upg_impreval_hist_cur.impairment_id,
v_srp_upg_impreval_hist_cur.impairment_name,
v_srp_upg_impreval_hist_cur.imp_description,
v_srp_upg_impreval_hist_cur.imp_class_type,
v_srp_upg_impreval_hist_cur.imp_amount,
v_srp_upg_impreval_hist_cur.mass_reval_id,
v_srp_upg_impreval_hist_cur.reval_percent,
v_srp_upg_impreval_hist_cur.reval_reason,
v_srp_upg_impreval_hist_cur.book_type_code,
v_srp_upg_impreval_hist_cur.report_mode,
v_srp_upg_impreval_hist_cur.id_type,
l_request_id);