FA Unplanned Depreciation - draft

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