Reports2017-11-18T12:27:27+00:00

JE Finnish Deferred Depreciation Expense - draft

Description
Categories: BI Publisher
Application: European Localizations
Source: Finnish Deferred Depreciation Expense Report (XML) - Not Supported: Reserved For Future Use
Short Name: JEFIASDR_XML
DB package: JE_JEFIASDR_XMLP_PKG
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
Parameter Name SQL text Validation
Asset Number To
 
LOV Oracle
Asset Number From
 
LOV Oracle
Category To
 
Category From
 
Report Type
 
LOV Oracle
Period
 
LOV Oracle
Tax Book
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept