FA CIP Statistics for the Central Statistical Office (KSH) (Hungary)

➜ Back to Library
Description
Categories: BI Publisher
Application: Assets
Source: FA CIP Statistics for the Central Statistical Office (KSH) (Hungary)
Short Name: FAWIPSTAT
DB package: FA_CIP_STAT_PKG
Run FA CIP Statistics for the Central Statistical Office (KSH) (Hungary) and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT	DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions')		source
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')							comp_code
		,FALU.meaning								asset_type_desc
		,FAH.asset_type								asset_type
		,DECODE(FAH.asset_type, 'CIP', FCB.cip_cost_acct,FCB.asset_cost_acct)	account
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')						cost_center
		,FAD.asset_number||' - '||FAD.description	asset
		,FAD.asset_number							asset_ord
		,1											ord_by
		,NULL										vend_num
		,NULL										vend_name
		,NULL										inv_num
		,TO_NUMBER(NULL)							line_num
		,NULL										description
		,TO_NUMBER(NULL)							orig_cost
		,TO_NUMBER(NULL)							inv_cost
		,SUM(NVL(FDD.addition_cost_to_clear, 0))	cost
		,NULL					     				flag
		,NULL 										transaction_date
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE')								addition_type
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE')								industry_code
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE')							statistical_code
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE')								tariff_num
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE')								item_number
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE')									reserve
FROM 
     	fa_distribution_history 		FDH
		,fa_asset_history 				FAH
		,fa_category_books 				FCB
		,fa_lookups 					FALU
		,fa_additions 					FAD
		,gl_code_combinations 			GLCC
		,fa_transaction_headers 		FTH
		,fa_deprn_detail				FDD
		,fa_deprn_periods				FDP
		,fa_asset_keywords				FAK
WHERE	FDP.book_type_code			=  	:P_BOOK    
AND		FDP.period_counter	       	>=  &gn_period1_pc	
AND		FDP.period_counter	       	<=  NVL(&gn_period2_pc,FDP.period_counter)	
AND		FTH.asset_id				= 	FDD.asset_id			
AND   	FTH.date_effective 	       	>=  FDP.period_open_date		
AND		FTH.date_effective			<  	NVL(FDP.period_close_date,sysdate)			
AND		FTH.book_type_code			=  	:P_BOOK			
AND		FTH.transaction_type_code 	in 	('TRANSFER IN','TRANSFER IN/VOID')
AND		FTH.transaction_header_id 	= 	(SELECT MIN(THVOID.transaction_header_id)
										FROM 	fa_transaction_headers THVOID	
										WHERE 	THVOID.book_type_code		= FTH.book_type_code
										AND   	THVOID.transaction_type_code IN ('TRANSFER IN/VOID'
												, 'TRANSFER IN') 
										AND   	THVOID.asset_id				= FTH.asset_id)
AND		FDH.book_type_code 			= 	:gc_distribution_source_book	
AND		FDH.asset_id 				= 	FDD.asset_id			
AND		GLCC.code_combination_id	=  	FDH.code_combination_id
AND		FDD.book_type_code			=  	:P_BOOK 			
AND		FDD.deprn_source_code		= 	'B'				
AND		FDD.distribution_id			=  	FDH.distribution_id		
AND		FDD.period_counter			=	FDP.period_counter - 1
AND		FCB.category_id				=  	FAH.category_id		
AND		FCB.book_type_code			=  	:P_BOOK 
AND		FAD.asset_id				=  	FDD.asset_id		
AND		FAH.asset_id				=  	FAD.asset_id			
AND		FAH.date_effective	      	<=  NVL(FDP.period_close_date, SYSDATE)	
AND		NVL(FAH.date_ineffective,SYSDATE+1) >  NVL(FDP.period_close_date, SYSDATE)
AND		FAH.asset_type				=  	FALU.lookup_code	
AND		FALU.lookup_type			= 	'ASSET TYPE'
AND		FAK.code_combination_id		=	FAD.asset_key_ccid
GROUP BY
		DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions')
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
		,FALU.meaning
		,FAH.asset_type
		,DECODE(FAH.asset_type, 'CIP', FCB.cip_cost_acct,FCB.asset_cost_acct)
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
		,FAD.asset_number||' - '||FAD.description
		,FAD.asset_number
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE')								
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE')								
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE')							
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE')
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE')
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE')
UNION ALL
SELECT	DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions')		source
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')							comp_code
		,FALU.MEANING								asset_type_desc
		,FAH.ASSET_TYPE								asset_type
		,DECODE(FAH.ASSET_TYPE, 'CIP', FCB.CIP_COST_ACCT,FCB.ASSET_COST_ACCT)	account
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')						cost_center
		,FAD.ASSET_NUMBER||' - '||FAD.DESCRIPTION		asset
		,FAD.ASSET_NUMBER							asset_ord
		,1											ord_by
		,NULL										vend_num
		,NULL										vend_name
		,NULL										inv_num
		,TO_NUMBER(NULL)							line_num
		,NULL										description
		,TO_NUMBER(NULL)							orig_cost
		,TO_NUMBER(NULL)							inv_cost
		,SUM(DECODE(ADJ.debit_credit_flag, 'DR',1,-1) *	ADJ.adjustment_amount)			cost
		,NULL										flag
		,NULL 										transaction_date
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE')								addition_type
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE')								industry_code
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE')							statistical_code
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE')								tariff_num
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE')								item_number
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE')									reserve
FROM
		fa_transaction_headers 			FTH
		,fa_transaction_headers			FTH1
		,fa_additions           		FAD
		,fa_asset_history				FAH
		,fa_category_books				FCB
		,fa_distribution_history 		FDH
		,gl_code_combinations			GLCC
		,gl_code_combinations			AJCC
		,fa_lookups						FALU
		,fa_adjustments					ADJ
		,fa_deprn_periods				FDP
		,fa_asset_keywords				FAK
WHERE	FDP.book_type_code			=  	:P_BOOK    
AND		FDP.period_counter	       	>=  &gn_period1_pc	
AND		FDP.period_counter	       	<=  NVL(&gn_period2_pc,FDP.period_counter)	
AND    	FTH.date_effective 	       	>=  FDP.PERIOD_OPEN_DATE		
AND		FTH.date_effective			<  	NVL(FDP.period_close_date,sysdate)			
AND		FTH.book_type_code			=  	:P_BOOK	
AND		FTH.transaction_type_code 	= 	'ADDITION'
AND		FTH1.transaction_type_code	= 	'TRANSFER IN'		
AND		FTH1.book_type_code			= 	:P_BOOK		
AND		FTH1.asset_id				= 	FTH.asset_id		
AND		FTH1.date_effective 		< 	FDP.period_open_date
AND		ADJ.book_type_code			= 	:P_BOOK			
AND		ADJ.asset_id 				= 	FTH.asset_id			
AND		ADJ.source_type_code 		= 	'ADDITION'			
AND		ADJ.adjustment_type 		= 	'COST'				
AND		ADJ.period_counter_created 	= 	FDP.period_counter		
AND		ADJ.code_combination_id		= 	AJCC.code_combination_id
AND		FDH.book_type_code			= 	:P_BOOK			
AND		FDH.asset_id				= 	FTH.asset_id			
AND		FDH.distribution_id			= 	ADJ.distribution_id		
AND		FDH.code_combination_id		= 	GLCC.code_combination_id
AND		FCB.category_id				=  	FAH.category_id		
AND		FCB.book_type_code			=  	:P_BOOK 
AND		FAD.asset_id				=  	FTH.asset_id		
AND		FAH.asset_id				=  	FTH.asset_id			
AND		FAH.date_effective	       	<=  FTH.date_effective	
AND		NVL(FAH.date_ineffective,SYSDATE+1) >  FTH.date_effective
AND		FAH.asset_type				=  	FALU.lookup_code	
AND		FALU.lookup_type			= 	'ASSET TYPE'
AND		FAK.code_combination_id		=	FAD.asset_key_ccid	
GROUP BY
		DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions')
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')
		,FALU.meaning
		,FAH.asset_type
		,DECODE(FAH.asset_type, 'CIP', FCB.cip_cost_acct,FCB.asset_cost_acct)
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')
		,FAD.asset_number||' - '||FAD.description
		,FAD.asset_number
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('addition_type', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ADDITION_TYPE', 'N', 'VALUE')								
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('industry_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'INDUSTRY_CODE', 'N', 'VALUE')								
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('statistical_code', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'STATISTICAL_CODE', 'N', 'VALUE')							
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('tariff_num', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'TARIFF_NUM', 'N', 'VALUE')
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_number', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'ITEM_NUMBER', 'N', 'VALUE')
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('reserve', 'OFA', 'KEY#', 101, NULL, fak.CODE_COMBINATION_ID, 'RESERVE', 'N', 'VALUE')
UNION ALL
SELECT	DECODE(FTH.mass_reference_id,NULL,'Manual Transactions','Mass Additions')			source
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')								comp_code
		,FALU.meaning									asset_type_desc
		,FAH.asset_type									asset_type
		,DECODE(FAH.asset_type, 'CIP', FCB.CIP_COST_ACCT,FCB.asset_cost_acct)	account
		,fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_cost_seg', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE')							cost_center
		,FAD.asset_number||' - '||FAD