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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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.description		asset
		,FAD.asset_number								asset_ord
		,2												ord_by
		,PV.segment1									vend_num
		,PV.vendor_name									vend_name
		,FAI.invoice_number								inv_num
		,FAI.ap_distribution_line_number				line_num
		,FAI.description								description
		,FAI.payables_cost 								orig_cost
		,round(SUM(FDH.units_assigned/FAH.UNITS * FAI.fixed_assets_cost), &gn_precision)	inv_cost
		,TO_NUMBER(NULL)								cost
		,DECODE(FIT.transaction_type,'INVOICE ADDITION','M',
				   'INVOICE ADJUSTMENT','A',
				   'INVOICE TRANSFER','T',
				   'INVOICE REINSTATE','R',NULL)      	flag
		,FTH.transaction_date_entered					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_asset_invoices 					FAI
		,fa_invoice_transactions    		FIT
		,fa_transaction_headers 			FTH
		,fa_distribution_history 			FDH
		,fa_asset_history 					FAH
		,fa_category_books 					FCB
		,fa_lookups 						FALU
		,po_vendors 						PV
		,fa_additions 						FAD
		,gl_code_combinations 				GLCC
		,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		FIT.invoice_transaction_id 		= 	FAI.invoice_transaction_id_in
AND		FAI.asset_id 					= 	FTH.asset_id				
AND		FAI.date_effective 				<=  NVL(FDP.period_close_date, SYSDATE)		
AND		NVL(FAI.date_ineffective, SYSDATE+1) > NVL(FDP.period_close_date, SYSDATE)	
AND		FAI.deleted_flag 				= 	'NO'
AND		PV.vendor_id(+) 				= 	FAI.po_vendor_id
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
		,PV.segment1
		,PV.vendor_name
		,FAI.invoice_number
		,FAI.ap_distribution_line_number
		,FAI.description
		,FAI.payables_cost
		,FTH.transaction_date_entered		
		,DECODE(FIT.transaction_type,'INVOICE ADDITION','M',
					   'INVOICE ADJUSTMENT','A',
					   'INVOICE TRANSFER','T',
					   'INVOICE REINSTATE','R',NULL)
		,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
		,2											ord_by
		,PV.segment1								vend_num
		,PV.vendor_name								vend_name
		,FAI.invoice_number							inv_num
		,FAI.ap_distribution_line_number			line_num
		,FAI.description							description
		,FAI.payables_cost 							orig_cost
		,round(SUM(FDH.units_assigned/FAH.units * FAI.fixed_assets_cost),&gn_precision)	inv_cost
		,TO_NUMBER(NULL)							cost
		,DECODE(FIT.TRANSACTION_TYPE,'INVOICE ADDITION','M',
				   'INVOICE ADJUSTMENT','A',
				   'INVOICE TRANSFER','T',
				   'INVOICE REINSTATE','R',NULL)    flag
		,FTH.transaction_date_entered				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_asset_invoices 				FAI
		,fa_invoice_transactions    	FIT
		,fa_transaction_headers 		FTH1
		,fa_distribution_history 		FDH
		,fa_asset_history 				FAH
		,fa_category_books 				FCB
		,fa_lookups 					FALU
		,po_vendors 					PV
		,fa_additions 					FAD
		,gl_code_combinations 			GLCC
		,fa_transaction_headers 		FTH
		,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		FDH.book_type_code			= 	:P_BOOK			
AND		FDH.asset_id					= 	FTH.asset_id			
AND		FDH.code_combination_id		= 	GLCC.code_combination_id	
AND		FDH.date_effective			<= 	FTH.date_effective 		
AND		NVL(FDH.date_ineffective, SYSDATE)	> FTH.date_effective
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		FIT.invoice_transaction_id 	= 	FAI.invoice_transaction_id_in
AND		FAI.asset_id 				= 	FTH.asset_id				
AND		FAI.date_effective 			<=  NVL(FDP.period_close_date, SYSDATE)		
AND		NVL(FAI.date_ineffective, SYSDATE+1) > NVL(FDP.period_close_date, SYSDATE)	
AND		FAI.deleted_flag 			= 	'NO'
AND		PV.vendor_id(+) 			= 	FAI.po_vendor_id
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
		,PV.segment1
		,PV.vendor_name
		,FAI.invoice_number
		,FAI.ap_distribution_line_number
		,FAI.description
		,FAI.payables_cost
		,FTH.transaction_date_entered		
		,DECODE(FIT.transaction_type,'INVOICE ADDITION','M',
					   'INVOICE ADJUSTMENT','A',
					   'INVOICE TRANSFER','T',
					   'INVOICE REINSTATE','R',NULL)
		,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')
ORDER BY 1,2,3,5,6,8,9,10,11,12
Parameter Name SQL text Validation
Book 
 
 LOV Oracle
From Period 
 
 LOV Oracle
To Period 
 
 LOV Oracle
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: