FA Form 4562 - Depreciation and Amortization Detail

select       fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')    	                                          	company_code,
	decode(fy.fiscal_year, :P_fiscal_year,
			to_char(fy.fiscal_year),:PREVIOUS_FISCAL_YEAR)	fiscal_year_added,
	BK.deprn_method_code				deprn_method,
    BK.date_placed_in_service				date_placed_in_service,
	&CAT_FLEX_ALL_SEG 				category,
	AD.asset_number asset_number,  
    AD.description	description,
	AD.ASSET_ID					ASSET_ID,
	sum(dd.cost)					cost,
	bk.life_in_months					life_in_months,
	bk.adjusted_rate					adjusted_rate,
	ds.bonus_rate					bonus_rate,
	bk.production_capacity				production,
	BK.prorate_convention_code				prorate_convention,
 	sum(dd.ytd_deprn)					ytd_deprn,
                 decode(  instr(bk.deprn_method_code, '30B'), 0, 
                               decode ( instr(bk.deprn_method_code, '50B'), 0, 0,
                                              decode(fy.fiscal_year, :P_FISCAL_YEAR, (bk.recoverable_cost * .5 ) , 0) )              
                             , decode(fy.fiscal_year, :P_FISCAL_YEAR, (bk.recoverable_cost * .3 ) , 0) 
                             )  special_deprn, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_company_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMPANY_CODE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_flex', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') C_CAT_FLEX, 
	--&COMPANY_CODE BAL_SEG, 
	FA_FAS771_XMLP_PKG.d_lifeformula(bk.life_in_months, bk.adjusted_rate, ds.bonus_rate, bk.production_capacity) D_LIFE, 
	FA_FAS771_XMLP_PKG.ytdformula(:YTD_ADJ, sum ( dd.ytd_deprn )) YTD, 
	FA_FAS771_XMLP_PKG.specialformula(AD.ASSET_ID, :Book, decode ( fy.fiscal_year , :P_fiscal_year , to_char ( fy.fiscal_year ) , :PREVIOUS_FISCAL_YEAR ), BK.deprn_method_code, decode ( instr ( bk.deprn_method_code , '30B' ) , 0 , decode ( instr ( bk.deprn_method_code , '50B' ) , 0 , 0 , decode ( fy.fiscal_year , :P_FISCAL_YEAR , ( bk.recoverable_cost * .5 ) , 0 ) ) , decode ( fy.fiscal_year , :P_FISCAL_YEAR , ( bk.recoverable_cost * .3 ) , 0 ) ), :YTD) SPECIAL,
    ((FA_FAS771_XMLP_PKG.ytdformula(:YTD_ADJ, sum ( dd.ytd_deprn ))) - (FA_FAS771_XMLP_PKG.specialformula(AD.ASSET_ID, :Book, decode ( fy.fiscal_year , :P_fiscal_year , to_char ( fy.fiscal_year ) , :PREVIOUS_FISCAL_YEAR ), BK.deprn_method_code, decode ( instr ( bk.deprn_method_code , '30B' ) , 0 , decode ( instr ( bk.deprn_method_code , '50B' ) , 0 , 0 , decode ( fy.fiscal_year , :P_FISCAL_YEAR , ( bk.recoverable_cost * .5 ) , 0 ) ) , decode ( fy.fiscal_year , :P_FISCAL_YEAR , ( bk.recoverable_cost * .3 ) , 0 ) ), :YTD) )) DEPRN_DEC ,
  --  ((sum(dd.cost)) - (FA_FAS771_XMLP_PKG.specialformula(AD.ASSET_ID, :Book, decode ( fy.fiscal_year , :P_fiscal_year , to_char ( fy.fiscal_year ) , :PREVIOUS_FISCAL_YEAR ), BK.deprn_method_code, decode ( instr ( bk.deprn_method_code , '30B' ) , 0 , decode ( instr ( bk.deprn_method_code , '50B' ) , 0 , 0 , decode ( fy.fiscal_year , :P_FISCAL_YEAR , ( bk.recoverable_cost * .5 ) , 0 ) ) , decode ( fy.fiscal_year , :P_FISCAL_YEAR , ( bk.recoverable_cost * .3 ) , 0 ) ), :YTD))) COST_BASIS
    FA_FAS771_XMLP_PKG.costbasis(sum(dd.cost),FA_FAS771_XMLP_PKG.specialformula(AD.ASSET_ID, :Book, decode ( fy.fiscal_year , :P_fiscal_year , to_char ( fy.fiscal_year ) , :PREVIOUS_FISCAL_YEAR ), BK.deprn_method_code, decode ( instr ( bk.deprn_method_code , '30B' ) , 0 , decode ( instr ( bk.deprn_method_code , '50B' ) , 0 , 0 , decode ( fy.fiscal_year , :P_FISCAL_YEAR , ( bk.recoverable_cost * .5 ) , 0 ) ) , decode ( fy.fiscal_year , :P_FISCAL_YEAR , ( bk.recoverable_cost * .3 ) , 0 ) ), :YTD),BK.deprn_method_code,decode(fy.fiscal_year, :P_fiscal_year,
	to_char(fy.fiscal_year),:PREVIOUS_FISCAL_YEAR) ) COST_BASIS
    from
	FA_DEPRN_DETAIL				dd,
	FA_DEPRN_SUMMARY			DS,
	FA_ADDITIONS				AD,
	FA_ASSET_HISTORY			                  AH,
	FA_CATEGORIES				CAT,
	FA_BOOKS				BK,
	GL_CODE_COMBINATIONS			DHCC,
	FA_DISTRIBUTION_HISTORY			DH,
	FA_FISCAL_YEAR				FY,
	FA_BOOK_CONTROLS			BC
where
--	BK.book_type_code		= :P_BOOK			and
	BK.book_type_code		= :P_BOOK  and
	BK.asset_id		= AD.asset_id			and
	:report_period_close_date between BK.date_effective and
		nvl(BK.date_ineffective,sysdate)			and  
	BK.date_placed_in_service between FY.start_date and FY.end_date	and
	nvl(BK.period_counter_fully_retired,:start_period_counter)	
					>= :start_period_counter  
and nvl(BK.period_counter_fully_reserved,:start_period_counter)>= :start_period_counter 
and	
	bc.book_type_code 		= :P_BOOK 			and
	bc.fiscal_year_name 	= fy.fiscal_year_name
and
	dd.book_type_code		= :P_BOOK 			and
	dd.asset_id		= BK.asset_id			and
	dd.deprn_source_code       	= 'D'				and
	dd.period_counter		=
				(select max(dd_sub.period_counter)
				 from fa_deprn_detail dd_sub
				 where dd_sub.book_type_code = :p_book  and
				            dd_sub.distribution_id = dd.distribution_id	and
				            dd_sub.period_counter			between
				            :start_period_counter  and
				            :end_period_counter)
and
	dh.distribution_id		= dd.distribution_id			
and
	ds.book_type_code		= :P_BOOK  and
	ds.asset_id 		= BK.ASSET_ID			and
	ds.period_counter		= dd.period_counter
and
	ah.asset_id		= bk.asset_id		and
	:report_period_close_date between ah.date_effective and
		nvl(ah.date_ineffective,sysdate)
and  
	cat.category_id			= ah.category_id
and
	dh.code_combination_id		= dhcc.code_combination_id (+)
group by
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	fy.fiscal_year,
	BK.deprn_method_code,
    BK.date_placed_in_service,
	&CAT_FLEX_ALL_SEG,
       	AD.asset_number,
	AD.description,
	AD.ASSET_ID,
	bk.life_in_months,
	bk.adjusted_rate,
	bk.production_capacity,
	ds.bonus_rate,
	BK.prorate_convention_code,
--	bk.recoverable_cost order by
	bk.recoverable_cost 
--Added during DT Fix
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_company_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
	,fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_flex', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
	,&COMPANY_CODE
--End of DT Fix
	order by
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_company_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	decode(fy.fiscal_year,:P_fiscal_year, 0, 1),
    bk.life_in_months,
	BK.deprn_method_code,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_cat_flex', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
	AD.asset_number