FA Diminishing Value - draft

select 
       nvl(&ACCT_FLEX_BAL_SEG,'None')		COMP_CODE,
       cb.asset_cost_acct			ASSET_ACCT,
       adt.asset_number||' - '||adt.description	ASSET_NUMBER,
       to_char(bks.adjusted_rate*100, '99D99') || '%' 	RATE,
       bks.date_placed_in_service		PURCH_DATE,
       round(sum(decode(bks.period_counter_fully_retired, '', bks.cost, 0)
       * dh.units_assigned / ah.units), :PRECISION) COST,
       round(sum(decode(d1.deprn_reserve,'', 0, d1.deprn_reserve) 
                 * dh.units_assigned / ah.units), :PRECISION)   PRV_DEPRN, 
       round(sum(decode(d1.deprn_reserve,'', (bks.cost - dc.deprn_reserve),
            (bks.cost - d1.deprn_reserve)) * dh.units_assigned / ah.units),
           :PRECISION) O_NBV,
       round(sum(nvl(dn.ytd_deprn,0) 
             * dh.units_assigned/ ah.units), :PRECISION)  YTD_DEP,
       round(sum(decode(bks.period_counter_fully_retired, '',
            (bks.cost - dn.deprn_reserve), 0) * dh.units_assigned/ ah.units),
          :PRECISION) C_NBV, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE
from   
       fa_distribution_history dh,
       fa_asset_history ah,
       fa_additions adt,
       gl_code_combinations dhcc,
       fa_category_books cb,
       fa_books bks, 
       fa_methods mth,
       fa_deprn_summary dn,
       fa_deprn_summary d1,
       fa_deprn_summary dc
where  
       dc.book_type_code    = :P_BOOK and
       dc.deprn_source_code = 'BOOKS'
and
       d1.book_type_code(+)  = :P_BOOK and
       d1.period_COUNTER(+)  = :OPEN_PC
and    dn.book_type_code  = :P_BOOK  		and
       dn.period_COUNTER = ( select dp.period_COUNTER
                          from    fa_deprn_periods dp
                          where   dp.book_type_code = :P_BOOK
                          and     dp.period_counter =
                              ( select max(dpz.period_counter)
                                from   fa_deprn_summary dsz,
                                            fa_deprn_periods dpz
                                where  dpz.book_type_code = :P_BOOK
                                and    dpz.period_counter <= 
						      :PERIOD1_PC
                                and    dsz.book_type_code = :P_BOOK
				and    dsz.period_counter = dpz.period_counter
				     and    dsz.asset_id = dn.asset_id
                                 ))
and
       dc.asset_id          = dn.asset_id	and
       d1.asset_id(+)        = dn.asset_id
and    
       bks.book_type_code = :P_BOOK 					and
       bks.asset_id       = dn.asset_id					and
       nvl(bks.date_ineffective,sysdate) >  
		:PERIOD1_PCD	and
       bks.date_effective 		 <= 
		:PERIOD1_PCD	and 
       nvl(bks.period_counter_fully_retired,:PERIOD1_PC) in 
		( select dpy.period_counter
		  from fa_deprn_periods dpy
                  where dpy.book_type_code = :P_BOOK
                  and   dpy.fiscal_year    = :PERIOD1_FY ) and
       bks.deprn_method_code = mth.method_code
and    
       mth.deprn_basis_rule  = 'NBV'	 		and
       mth.life_in_months is null
and           adt.asset_type        = 'CAPITALIZED'	 	and
       adt.asset_id          = dn.asset_id		and
       adt.asset_category_id = cb.category_id
and    
       cb.book_type_code     = :P_BOOK 			
and
       adt.asset_id          = dh.asset_id		and
       dh.book_type_code     = :DISTRIBUTION_SOURCE_BOOK  and
       nvl(dh.date_ineffective,sysdate) >  
		:PERIOD1_PCD	and
       dh.date_effective 		 <= 
		:PERIOD1_PCD
and
       dhcc.code_combination_id(+) = dh.code_combination_id and
       ah.asset_id = adt.asset_id and
       nvl(ah.date_ineffective,sysdate) >  
		:PERIOD1_PCD	and
       ah.date_effective 		 <= 
		:PERIOD1_PCD
group by
       nvl(&ACCT_FLEX_BAL_SEG,'None'),
       cb.asset_cost_acct,
       adt.asset_number||' - '||adt.description,
       bks.adjusted_rate,
       bks.date_placed_in_service,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
order by 1,2,
         adt.asset_number||' - '||adt.description