FA Form 4684 - Casualties and Thefts - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Assets
Source: Form 4684 - Casualties and Thefts Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS449_XML
DB package: FA_FAS449_XMLP_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 	
--Start of DT Fix
--&ACCT_FLEX_BAL_SEG                   		comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')	COMP_CODE_DSP1,
--End of DT Fix
	LU.MEANING			RETIREMENT_TYPE,
	LU2.meaning			term,
--Start of DT Fix
	--&CAT_FLEX_ALL_SEG                         		category,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE')	D_CATEGORY1,
--End of DT Fix
	ad.asset_number || ' - ' || ad.description		no,
	books.date_placed_in_service 			dt,
	ret.date_retired		     		dr,
	round(sum(decode(ret.units, NULL, 
		(decode(th.transaction_type_code, 'REINSTATEMENT',
	  	(-1) * ret.cost_retired,  ret.cost_retired)
 		* (dh.units_assigned /ah.units)),
		(decode(th.transaction_type_code, 'REINSTATEMENT',
	  	(-1) * ret.cost_retired,  ret.cost_retired)
	   	* abs(dh.transaction_units) / ret.units))),:PRECISION)	cost,
--Start of DT Fix
	--&LOC_FLEX_ALL_SEG			location,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
--End of DT Fix
	decode(th.transaction_type_code, 'REINSTATEMENT', 
		'*', null)			  	code
FROM	
	fa_retirements				ret,
	fa_asset_history				ah,
	fa_additions				ad,
	fa_books					books,
	fa_transaction_headers			th,
	fa_distribution_history			dh,
              	fa_lookups 				lu,
	fa_lookups				lu2,
	fa_categories				cat,
	fa_locations				loc,
	gl_code_combinations			dhcc
WHERE 	
	books.book_type_code = :P_BOOK                         
AND	books.transaction_header_id_out = th.transaction_header_id	
AND	books.asset_id = th.asset_id
AND	cat.category_id = ah.category_id
AND	ah.asset_id = ad.asset_id					
AND	ad.asset_id = th.asset_id
AND	ah.date_effective <= th.date_effective				
AND	nvl(ah.date_ineffective, sysdate) > th.date_effective
AND	loc.location_id = dh.location_id
AND	dhcc.code_combination_id = dh.code_combination_id
AND	(dh.retirement_id is null or :Distribution_Source_book != :P_book)
AND	dh.book_type_code = :Distribution_Source_Book          
AND	(ret.date_effective >= dh.date_effective       and
		ret.date_effective < nvl(dh.date_ineffective,sysdate))
AND	dh.asset_id = th.asset_id	
AND      	ret.retirement_type_code = lu.lookup_code(+)
AND  	lu.lookup_type(+) = 'RETIREMENT'			
AND	 ret.units is null
AND	(lu2.lookup_type = 'ASSET TERM'
AND	 	decode (sign (months_between (ret.date_retired,
			books.date_placed_in_service)
			- 12), 1, 'LONG', 'SHORT')	   = lu2.lookup_code)
AND     decode(:P_RET_TYPE,null,nvl(ret.retirement_type_code,-1),:P_RET_TYPE) = 
		nvl(ret.retirement_type_code,-1)
AND	th.transaction_header_id = ret.transaction_header_id_in
AND	th.transaction_type_code like '%RETIREMENT'
AND	th.date_effective	 >= :Period1_POD                    
AND	th.date_effective	 <= :Period2_PCD             
AND	th.book_type_code = :P_BOOK
AND	th.transaction_key = 'R'
group by
	--&ACCT_FLEX_BAL_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	LU.MEANING,
	LU2.MEANING,
	th.transaction_type_code,
	th.asset_id,
	ad.asset_number,
 	ad.description,
	books.date_placed_in_service,
	ret.date_retired,
	th.transaction_header_id,
	decode(th.transaction_type_code, 'REINSTATEMENT', '*', null),
	/*&CAT_FLEX_ALL_SEG,
 	&LOC_FLEX_ALL_SEG*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE')
UNION ALL
SELECT 	
--Start of DT Fix
--&ACCT_FLEX_BAL_SEG                   		comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')	COMP_CODE_DSP1,
--End of DT Fix
	LU.MEANING			RETIREMENT_TYPE,
	LU2.MEANING			term,
--Start of DT Fix
	--&CAT_FLEX_ALL_SEG                         		category,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE')	D_CATEGORY1,
--End of DT Fix
	ad.asset_number || ' - ' || ad.description		no,
	books.date_placed_in_service 			dt,
	ret.date_retired		     		dr,
	round(sum(decode(ret.units, NULL, 
		(decode(th.transaction_type_code, 'REINSTATEMENT',
	  	(-1) * ret.cost_retired,  ret.cost_retired)
 		* (dh.units_assigned /ah.units)),
		(decode(th.transaction_type_code, 'REINSTATEMENT',
	  	(-1) * ret.cost_retired,  ret.cost_retired)
	   	* abs(dh.transaction_units) / ret.units))),:PRECISION)	cost,
--Start of DT Fix
	--&LOC_FLEX_ALL_SEG			location,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
--End of DT Fix
	decode(th.transaction_type_code, 'REINSTATEMENT', 
		'*', null)			  	code
FROM	
	fa_retirements				ret,
	fa_asset_history				ah,
	fa_additions				ad,
	fa_lookups				lu,
	fa_lookups				lu2,
	fa_books					books,
	fa_transaction_headers			th,
	fa_distribution_history			dh,
	fa_categories				cat,
	fa_locations				loc,
	gl_code_combinations			dhcc
WHERE 	
	books.book_type_code = :P_BOOK                         
AND	books.transaction_header_id_out = th.transaction_header_id	
AND	books.asset_id = th.asset_id
AND	cat.category_id = ah.category_id
AND	ah.asset_id = ad.asset_id					
AND	ad.asset_id = th.asset_id
AND	ah.date_effective <= th.date_effective				
AND	nvl(ah.date_ineffective, sysdate) > th.date_effective
AND	loc.location_id = dh.location_id
AND	dhcc.code_combination_id = dh.code_combination_id
AND	(dh.retirement_id is not null or :Distribution_Source_Book != :P_book)
AND	dh.book_type_code = :Distribution_Source_Book          
AND	(ret.date_effective >= dh.date_effective       and
		ret.date_effective < nvl(dh.date_ineffective,sysdate))
AND	dh.asset_id = th.asset_id		
AND     	(ret.retirement_type_code = lu.lookup_code(+)
AND  	lu.lookup_type(+) 	         = 'RETIREMENT')
AND	(lu2.lookup_type = 'ASSET TERM'
AND	 	decode (sign (months_between (ret.date_retired,
			books.date_placed_in_service)
			- 12), 1, 'LONG', 'SHORT')	   = lu2.lookup_code)		
AND	 ret.units is not null
AND     decode(:P_RET_TYPE,null,nvl(ret.retirement_type_code,-1),:P_RET_TYPE) = 
		nvl(ret.retirement_type_code,-1)
AND	th.transaction_header_id = ret.transaction_header_id_out
AND	th.transaction_type_code  = 'REINSTATEMENT'
AND	th.date_effective	 >= :Period1_POD                    
AND	th.date_effective	 <= :Period2_PCD             
AND	th.book_type_code = :P_BOOK
AND	th.transaction_key = 'R'
group by
	--&ACCT_FLEX_BAL_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	LU.MEANING,
	LU2.MEANING,
	th.transaction_type_code,
	th.asset_id,
	ad.asset_number,
 	ad.description,
	books.date_placed_in_service,
	ret.date_retired,
	th.transaction_header_id,
	decode(th.transaction_type_code, 'REINSTATEMENT', '*', null),
	/*&CAT_FLEX_ALL_SEG,
 	&LOC_FLEX_ALL_SEG*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE')
UNION ALL
SELECT 	
--Start of DT Fix
--&ACCT_FLEX_BAL_SEG                   		comp_code,
fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')	COMP_CODE_DSP1,
--End of DT Fix
	LU.MEANING		RETIREMENT_TYPE,
	LU2.MEANING		term,
--Start of DT Fix
	--&CAT_FLEX_ALL_SEG                         		category,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE')	D_CATEGORY1,
--End of DT Fix
	ad.asset_number || ' - ' || ad.description		no,
	books.date_placed_in_service 			dt,
	ret.date_retired		     		dr,
	round(sum(decode(ret.units, NULL, 
		(decode(th.transaction_type_code, 'REINSTATEMENT',
	  	(-1) * ret.cost_retired,  ret.cost_retired)
 		* (dh.units_assigned /ah.units)),
		(decode(th.transaction_type_code, 'REINSTATEMENT',
	  	(-1) * ret.cost_retired,  ret.cost_retired)
	   	* abs(dh.transaction_units) / ret.units))),:PRECISION)	cost,
--Start of DT Fix
	--&LOC_FLEX_ALL_SEG			location,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
--End of DT Fix
	decode(th.transaction_type_code, 'REINSTATEMENT', 
		'*', null)			  	code
FROM	
	fa_retirements				ret,
	fa_asset_history				ah,
	fa_additions				ad,
	fa_books					books,
	fa_lookups				lu,
	fa_lookups				lu2,
	fa_transaction_headers			th,
	fa_distribution_history			dh,
	fa_categories				cat,
	fa_locations				loc,
	gl_code_combinations			dhcc
WHERE 	
	books.book_type_code = :P_BOOK                         
AND	books.transaction_header_id_out = th.transaction_header_id	
AND	books.asset_id = th.asset_id
AND	cat.category_id = ah.category_id
AND	ah.asset_id = ad.asset_id					
AND	ad.asset_id = th.asset_id
AND	ah.date_effective <= th.date_effective				
AND	nvl(ah.date_ineffective, sysdate) > th.date_effective
AND	loc.location_id = dh.location_id
AND	dhcc.code_combination_id = dh.code_combination_id
AND	dh.retirement_id is not null
AND	dh.book_type_code = :Distribution_Source_Book          
AND	dh.retirement_id = ret.retirement_id
AND	(ret.retirement_type_code = lu.lookup_code(+)
AND	lu.lookup_type(+) 	         = 'RETIREMENT')
AND	(lu2.lookup_type = 'ASSET TERM'
AND	 	decode (sign (months_between (ret.date_retired,
			books.date_placed_in_service)
			- 12), 1, 'LONG', 'SHORT')	   = lu2.lookup_code)
AND	dh.asset_id = th.asset_id				
AND	 ret.units is not null
AND     decode(:P_RET_TYPE,null,nvl(ret.retirement_type_code,-1),:P_RET_TYPE) = 
		nvl(ret.retirement_type_code,-1)
AND	th.transaction_header_id = ret.transaction_header_id_in
AND	th.transaction_type_code like '%RETIREMENT'
AND	th.date_effective	 >= :Period1_POD                    
AND	th.date_effective	 <= :Period2_PCD             
AND	th.book_type_code = :P_BOOK
AND	th.transaction_key = 'R'
group by
	--&ACCT_FLEX_BAL_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	LU.MEANING,
	LU2.MEANING,
	th.transaction_type_code,
	th.asset_id,
	ad.asset_number,
 	ad.description,
	books.date_placed_in_service,
	ret.date_retired,
	th.transaction_header_id,
	decode(th.transaction_type_code, 'REINSTATEMENT', '*', null),
	/*&CAT_FLEX_ALL_SEG,
 	&LOC_FLEX_ALL_SEG*/
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE')
ORDER BY
	1,2,3,4,5
Parameter Name SQL text Validation
Book 
 
 LOV Oracle
From Period 
 
 LOV Oracle
To Period 
 
 LOV Oracle
Retirement Type 
 
 LOV Oracle
Download
 
Related Blitz Reports
JH Asset details FA Asset Retirements FA Form 4797 - Gain From Disposition of 1245 Property - draft FA Reinstated Assets - draft FA Asset Disposals Responsibility Report- Not Supported: Reserved For Future Use FA Asset Retirements By Cost Center Report- Not Supported: Reserved For Future Use FA Mass Retirements - draft FA Asset Retirements Report- Not Supported: Reserved For Future Use FA Asset Additions
Blitz Report™

Blitz Report™ provides multiple benefits: