[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select lookup_code,meaning
from fa_lookups_tl lk
where lk.lookup_type = 'IMPAIRMENT_CLASSIFICATION'
and lk.language = c_language;
select sob.currency_code
,sob.name
from gl_sets_of_books sob
where sob.set_of_books_id = c_sob_id;
select period_name
from fa_deprn_periods
where book_type_code = c_book
and period_counter = c_period_counter;
select description,
impairment_date
from FA_IMPAIRMENTS
where impairment_id = c_impairment_id;
l_debug_info := 'Select the main sql...';
SELECT CGU.CASH_GENERATING_UNIT
,CGU.CASH_GENERATING_UNIT_ID /*Bug# 9182681 */
,IMP.IMPAIRMENT_DATE
,IMP.NET_SELLING_PRICE
,IMP.VALUE_IN_USE
,IMP.GOODWILL_AMOUNT
,AD.ASSET_NUMBER
,IMP.IMPAIRMENT_ID
FROM FA_IMPAIRMENTS IMP
,FA_CASH_GEN_UNITS CGU
,FA_ADDITIONS_B AD
WHERE IMP.CASH_GENERATING_UNIT_ID = CGU.CASH_GENERATING_UNIT_ID(+)
AND IMP.GOODWILL_ASSET_ID = AD.ASSET_ID(+)
AND IMP.BOOK_TYPE_CODE = p_book_type_code
AND IMP.PERIOD_COUNTER_IMPAIRED = p_period_counter
AND IMP.REQUEST_ID = nvl(p_request_id, IMP.REQUEST_ID)
-- AND CGU.CASH_GENERATING_UNIT_ID = nvl(p_cash_gen_unit_id, CGU.CASH_GENERATING_UNIT_ID) -- bug# 5893164
AND IMP.STATUS = nvl(p_status, 'POSTED')
AND IMP.IMPAIRMENT_ID = nvl(p_impairment_id,IMP.IMPAIRMENT_ID); --Bug#8539194
SELECT CGU.CASH_GENERATING_UNIT
,CGU.CASH_GENERATING_UNIT_ID
,IMP.IMPAIRMENT_DATE
,IMP.NET_SELLING_PRICE
,IMP.VALUE_IN_USE
,IMP.GOODWILL_AMOUNT
,AD.ASSET_NUMBER
,IMP.IMPAIRMENT_ID
FROM FA_MC_IMPAIRMENTS IMP
,FA_CASH_GEN_UNITS CGU
,FA_ADDITIONS_B AD
WHERE IMP.CASH_GENERATING_UNIT_ID = CGU.CASH_GENERATING_UNIT_ID(+)
AND IMP.GOODWILL_ASSET_ID = AD.ASSET_ID(+)
AND IMP.BOOK_TYPE_CODE = p_book_type_code
AND IMP.PERIOD_COUNTER_IMPAIRED = p_period_counter
AND IMP.REQUEST_ID = nvl(p_request_id, IMP.REQUEST_ID)
-- AND CGU.CASH_GENERATING_UNIT_ID = nvl(p_cash_gen_unit_id, CGU.CASH_GENERATING_UNIT_ID) -- bug# 5893164
AND IMP.STATUS = nvl(p_status, 'POSTED')
AND IMP.IMPAIRMENT_ID = nvl(p_impairment_id,IMP.IMPAIRMENT_ID);
'SELECT AD.ASSET_NUMBER
,ITF.COST
,(ITF.NET_BOOK_VALUE - (ITF.IMPAIRMENT_AMOUNT + nvl(ITF.REVAL_RESERVE_ADJ_AMOUNT,0))) NEW_NBV
,ITF.NET_BOOK_VALUE OLD_NBV
,ITF.NET_BOOK_VALUE NET_BOOK_VALUE
,ITF.IMPAIRMENT_AMOUNT
, decode(ITF.impair_class ,''CPP'','''||l_cpp_meaning||''',''CEB'','''||l_ceb_meaning||''',''OTH'','''||l_oth_meaning||''') IMPAIR_CLASS,
ITF.reason,
ITF.REVAL_RESERVE_ADJ_AMOUNT,
ITF.impair_loss_acct,
ITF.split_impair_flag,
decode(ITF.split1_impair_class,''CPP'','''||l_cpp_meaning||''',''CEB'','''||l_ceb_meaning||''',''OTH'','''||l_oth_meaning||''') SPLIT1_IMPAIR_CLASS,
ITF.split1_reason,
ITF.SPLIT1_REVAL_RESERVE,
ITF.split1_loss_acct,
ITF.split1_loss_amount,
decode(ITF.split2_impair_class,''CPP'','''||l_cpp_meaning||''',''CEB'','''||l_ceb_meaning||''',''OTH'','''||l_oth_meaning||''') SPLIT2_IMPAIR_CLASS,
ITF.split2_reason,
ITF.SPLIT2_REVAL_RESERVE,
ITF.split2_loss_acct,
ITF.split2_loss_amount,
decode(ITF.split3_impair_class,''CPP'','''||l_cpp_meaning||''',''CEB'','''||l_ceb_meaning||''',''OTH'','''||l_oth_meaning||''') SPLIT3_IMPAIR_CLASS,
ITF.split3_reason,
ITF.SPLIT3_REVAL_RESERVE,
ITF.split3_loss_acct,
ITF.split3_loss_amount
,ITF.NET_SELLING_PRICE NET_SELLING_PRICE
,ITF.VALUE_IN_USE VALUE_IN_USE
FROM '||l_reporting_suffix||' ITF
,FA_ADDITIONS_B AD
WHERE ITF.ASSET_ID = AD.ASSET_ID
AND ITF.BOOK_TYPE_CODE = '''||p_book_type_code||'''
AND NVL(ITF.cash_generating_unit_id,-99) = '''||r_CGU_id||'''
AND ITF.REQUEST_ID = nvl( '''||p_request_id||''', ITF.REQUEST_ID)
AND EXISTS
(SELECT 1 FROM FA_IMPAIRMENTS IMP
WHERE IMP.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
AND IMP.PERIOD_COUNTER_IMPAIRED = '''||p_period_counter||'''
AND IMP.STATUS = nvl( '''||p_status||''', ''POSTED''))
ORDER BY AD.ASSET_NUMBER'
);
select sob.currency_code
,sob.name
from gl_sets_of_books sob
where sob.set_of_books_id = c_sob_id;
l_debug_info := 'Select the main sql...';
select cash_generating_unit
,cgu.cash_generating_unit_id
from fa_cash_gen_units cgu
where cgu.book_type_code = p_book_type_code
and cgu.cash_generating_unit_id = nvl(p_cash_gen_unit_id, cgu.cash_generating_unit_id)
and cgu.cash_generating_unit_id
in (select bk.cash_generating_unit_id
from fa_books bk
where bk.book_type_code = p_book_type_code
and bk.transaction_header_id_out is null
and bk.asset_id = nvl(p_asset_id, bk.asset_id)
);
select cash_generating_unit
,cgu.cash_generating_unit_id
from fa_cash_gen_units cgu
where cgu.book_type_code = p_book_type_code
and cgu.cash_generating_unit_id = nvl(p_cash_gen_unit_id, cgu.cash_generating_unit_id)
and cgu.cash_generating_unit_id
in (select bk.cash_generating_unit_id
from fa_mc_books bk
where bk.book_type_code = p_book_type_code
and bk.transaction_header_id_out is null
and bk.asset_id = nvl(p_asset_id, bk.asset_id)
);
'select ad.asset_number as asset_number
,bk.cost as cost
,bk.cost - ds.deprn_reserve - ds.impairment_reserve as net_book_value
,ds.impairment_reserve as accumulated_impairment
,ds.ytd_impairment as ytd_impairment
from fa_additions_b ad
,'||l_fa_books_tb_name||' bk
,'||l_fa_dpr_sum_tb_name||' ds
where bk.book_type_code = '''||p_book_type_code||'''
and ds.book_type_code = bk.book_type_code
and bk.asset_id = ad.asset_id
and bk.asset_id = ds.asset_id
and bk.cash_generating_unit_id = '||r_cash_gen_unit_id_text||'
and bk.asset_id = '||r_asset_id_text||'
and bk.transaction_header_id_out is null
and ds.period_counter =
(select max(period_counter)
from '||l_fa_dpr_sum_tb_name||' ds2
where ds2.book_type_code = '''||p_book_type_code||'''
and ds2.asset_id = bk.asset_id
)
order by ad.asset_number'
);