JE Finnish Deferred Depreciation Expense - draft

SELECT 		substr(fad.description,1,11)  						Asset_Description, 
		substr(fad.asset_number,1,8) 						Asset_Number,
		sum(nvl(fdd3.deprn_reserve,0)) 					Tax_Dp_reserve,
		sum(nvl(fdd3.cost,0) - nvl(fdd3.deprn_reserve,0))			 	Initial_NBV,
		fad.asset_id 							Asset_id,
		fah.category_id 							Asset_Category_Id 
--JE_JEFIASDR_XMLP_PKG.cf_asset_close_nbvformula(:CF_ASS_INITIAL_NBV_ADJUSTED, :CF_ASS_TAX_ADDITIONS, :CS_ASS_TAX_RETIREMENT, --:a1_1, :CF_ASS_DEF_DP) CF_ASS_CLOSE_NBV, 
--&CS_ASS_TAX_ADDITIONS CF_ASS_TAX_ADDITIONS, 
--	JE_JEFIASDR_XMLP_PKG.cf_ass_def_dpformula(:CS_ASS_TAX_DEPRN, :a1_1, :CS_ass_this_fisadj) CF_ASS_DEF_DP,
--	JE_JEFIASDR_XMLP_PKG.cf_ass_initial_nbv_adjustedfor(substr ( fad.asset_number , 1 , 8 ), :CAT_ID, :ENABLED_FLAG, sum ( nvl --( fdd3.cost , 0 ) - nvl ( fdd3.deprn_reserve , 0 ) ), :CS_ASS_FISCAL_ADJUSTMENT) CF_ASS_INITIAL_NBV_ADJUSTED, 
--	JE_JEFIASDR_XMLP_PKG.cf_ass_tax_dp_reserve_adjusted(sum ( nvl ( fdd3.deprn_reserve , 0 ) ), :CS_ASS_FISCAL_ADJUSTMENT) --CF_ASS_TAX_DP_RESERVE_ADJUSTED, 
--&CS_ASS_CORP_ADDITIONS CF_ASS_CORP_ADDITIONS
FROM		fa_asset_history fah,
		fa_additions fad,
		fa_deprn_detail fdd3,
		fa_books bks
WHERE	 	 fah.asset_id 				= fad.asset_id
and		 fdd3.asset_id 				= fad.asset_id
and		 fad.asset_id 				= bks.asset_id
and		 fdd3.book_type_code 			= bks.book_type_code
and		 bks.book_type_code				= :P_TAX_BOOK
and		 (fdd3.period_counter  			= :C_FROM_COUNTER - 1
OR		 (fdd3.period_counter between :C_FROM_COUNTER and :C_TO_COUNTER and fdd3.deprn_source_code= 'B')
OR		 (bks.depreciate_flag = 'NO'  and fdd3.period_counter < :C_FROM_COUNTER))
and		 fah.transaction_header_id_in = (	SELECT	max(ah2.transaction_header_id_in)
					FROM	fa_asset_history ah2
					WHERE	ah2.asset_id = fah.asset_id
					and	ah2.category_id = fah.category_id
					and	to_date(ah2.date_effective,'dd-mon-yy') <=:C_TIME_TO_CLOSE_DATE
				and	nvl(ah2.date_ineffective,:C_TIME_TO_CLOSE_DATE) >= :C_TIME_TO_CLOSE_DATE	)
and	to_date(bks.date_effective,'dd-mon-yy')     <= :C_TO_CLOSE_DATE
and	nvl(bks.date_ineffective,:C_TO_CLOSE_DATE) >= :C_TO_OPEN_DATE
and	nvl(bks.period_counter_fully_retired,:C_FROM_COUNTER) >= :C_FROM_COUNTER
&C_ASSET_WHERE
and	 	bks.transaction_header_id_in =
			(SELECT min(bks2.transaction_header_id_in)
			FROM fa_books bks2
			WHERE bks2.book_type_code	= :P_TAX_BOOK 
			and bks2.asset_id		= fad.asset_id
			and to_date(bks2.date_effective,'dd-mon-yy') <= :C_TO_CLOSE_DATE
			and nvl(bks2.date_ineffective,:C_TO_CLOSE_DATE) >= :C_TO_OPEN_DATE
			and nvl(bks2.period_counter_fully_retired,:C_FROM_COUNTER)	>= :C_FROM_COUNTER)
GROUP BY		fad.description,
       	 		fad.asset_number,
			bks.cost,
			fad.asset_id,
        			fah.category_id
UNION ALL
SELECT 	substr(fad.description,1,11)		Asset_Description, 
	substr(fad.asset_number,1,8) 		Asset_Number,
	0 				Tax_Dp_reserve,
	0				Initial_NBV,
	fad.asset_id 			Asset_id,
	fah.category_id 			Asset_Category_Id
--	JE_JEFIASDR_XMLP_PKG.cf_asset_close_nbvformula(:CF_ASS_INITIAL_NBV_ADJUSTED, :CF_ASS_TAX_ADDITIONS, :CS_ASS_TAX_RETIREMENT, --:a1_1, :CF_ASS_DEF_DP) CF_ASS_CLOSE_NBV, 
--	&CS_ASS_TAX_ADDITIONS CF_ASS_TAX_ADDITIONS, 
--	JE_JEFIASDR_XMLP_PKG.cf_ass_def_dpformula(:CS_ASS_TAX_DEPRN, :a1_1, :CS_ass_this_fisadj) CF_ASS_DEF_DP, 
--	JE_JEFIASDR_XMLP_PKG.cf_ass_initial_nbv_adjustedfor(substr ( fad.asset_number , 1 , 8 ), :CAT_ID, :ENABLED_FLAG, 
--	sum ( nvl ( fdd3.cost , 0 ) - nvl ( fdd3.deprn_reserve , 0 ) ), :CS_ASS_FISCAL_ADJUSTMENT) --CF_ASS_INITIAL_NBV_ADJUSTED, 
--	JE_JEFIASDR_XMLP_PKG.cf_ass_tax_dp_reserve_adjusted(sum ( nvl ( fdd3.deprn_reserve , 0 ) ), :CS_ASS_FISCAL_ADJUSTMENT) --CF_ASS_TAX_DP_RESERVE_ADJUSTED, 
--	&CS_ASS_CORP_ADDITIONS CF_ASS_CORP_ADDITIONS
FROM	fa_asset_history fah,
	fa_additions fad,
	fa_books bks
WHERE	fah.asset_id 				= fad.asset_id
and	fad.asset_id 				= bks.asset_id
and	bks.book_type_code				= :P_TAX_BOOK
and 	not exists (select 'x'
                  	   from fa_deprn_detail fdd3
                 	 where fdd3.asset_id = fad.asset_id
                   	     and fdd3.book_type_code = :P_tax_book
                   	     and (	(fdd3.period_counter = :C_FROM_COUNTER - 1)
                        		OR (fdd3.period_counter between :C_FROM_COUNTER and :C_TO_COUNTER and fdd3.deprn_source_code= 'B')
                        		OR (bks.depreciate_flag = 'NO'  and fdd3.period_counter < :C_FROM_COUNTER)	)
	)
and 	exists (select 'x'
              	              from fa_deprn_detail fddcorp
	            where fddcorp.asset_id = fad.asset_id
               		and fddcorp.book_type_code = :C_distribution_source_book
               		and fddcorp.period_counter >= :C_corp_from_counter )
and	fah.transaction_header_id_in = (SELECT	max(ah2.transaction_header_id_in)
				  FROM	fa_asset_history ah2
				 WHERE	ah2.asset_id = fah.asset_id
				      and	ah2.category_id = fah.category_id
				      and	to_date(ah2.date_effective,'dd-mon-yy') <= :C_TIME_TO_CLOSE_DATE
				      and	nvl(ah2.date_ineffective, :C_TIME_TO_CLOSE_DATE) >= :C_TIME_TO_CLOSE_DATE)	
and	to_date(bks.date_effective,'dd-mon-yy') <= :C_TO_CLOSE_DATE
and	nvl(bks.date_ineffective, :C_TO_CLOSE_DATE) >= :C_TO_OPEN_DATE
and	nvl(bks.period_counter_fully_retired, :C_FROM_COUNTER) >= :C_FROM_COUNTER
&C_ASSET_WHERE
and	bks.transaction_header_id_in = (SELECT min(bks2.transaction_header_id_in)
				   FROM fa_books bks2
				 WHERE bks2.book_type_code	= :P_TAX_BOOK 
				       and bks2.asset_id = fad.asset_id
				       and to_date(bks2.date_effective,'dd-mon-yy') <= :C_TO_CLOSE_DATE
				       and nvl(bks2.date_ineffective, :C_TO_CLOSE_DATE) >= :C_TO_OPEN_DATE
				       and nvl(bks2.period_counter_fully_retired, :C_FROM_COUNTER) >= :C_FROM_COUNTER) 
 and fah.category_id=:Cat_id
GROUP BY		fad.description,
       	 		fad.asset_number,
			bks.cost,
			fad.asset_id,
        			fah.category_id
ORDER BY 		asset_number