select
&ACCT_FLEX_BAL_SEG comp_code,
&ACCT_FLEX_ALL_SEG expense_account,
&KEY_FLEX_ALL_SEG asset_key,
fa.asset_number,
fa.tag_number,
fa.description,
fd.period_name,
decode(substr(ft.transaction_name, 1,5),'UNPL ',substr(upper(ft.transaction_name),6),
'UNPLA',substr(upper(ft.transaction_name),23),
ft.transaction_subtype ) t_name,
fj.adjustment_amount,
fj.debit_credit_flag,
fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP,
fnd_flex_xml_publisher_apis.process_kff_combination_1('expense_account_dsp', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') EXPENSE_ACCOUNT_DSP,
fnd_flex_xml_publisher_apis.process_kff_combination_1('asset_key_dsp', 'OFA', 'KEY#', 101, NULL, fk.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') ASSET_KEY_DSP1,
FA_FASUNPLD_XMLP_PKG.C_base_currency_code_p C_base_currency_code
from fa_additions fa,
fa_deprn_periods fd,
fa_transaction_headers ft,
fa_adjustments fj,
gl_code_combinations cc,
fa_asset_keywords fk
where ft.asset_id = fa.asset_id
and fj.asset_id = fa.asset_id
and ft.transaction_header_id = fj.transaction_header_id
and fd.period_counter = fj.period_counter_created
and fj.source_type_code = 'DEPRECIATION'
and fj.adjustment_type = 'EXPENSE'
and fj.period_counter_adjusted between :start_period_COUNTER and :end_period_COUNTER
and ((ft.transaction_subtype = &P_DEPRN_TYPE_1 or
ft.transaction_name like 'UNPL'||'%'||&P_DEPRN_TYPE_1)
or(ft.transaction_key like 'U%' and &P_DEPRN_TYPE_1 = '%'
))
and fj.book_type_code = :P_ASSET_BOOK_TYPE
and fd.book_type_code = fj.book_type_code
and cc.code_combination_id = fj.code_combination_id
and fk.code_combination_id = fa.asset_key_ccid
UNION ALL
select
null comp_code,
null expense_account,
null asset_key,
a.asset_number,
null tag_number,
null description,
null period_name,
null t_name,
sum(nvl(d.deprn_adjustment_amount,0)) adjustment_amount,
null debit_credit_flag,
--added
null COMP_CODE_DSP,
null EXPENSE_ACCOUNT_DSP,
null ASSET_KEY_DSP1,
FA_FASUNPLD_XMLP_PKG.C_base_currency_code_p C_base_currency_code
from fa_deprn_detail d,
fa_additions a
where d.book_type_code = :P_ASSET_BOOK_TYPE
and d.deprn_source_code = 'B'
and a.asset_id = d.asset_id
group by null,
null,
null,
a.asset_number,
null,
null,
null,
null,
null ,null,null,null
having sum(nvl(d.deprn_adjustment_amount,0)) != 0
order by asset_number, period_name |
