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
Run FA Form 4684 - Casualties and Thefts - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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',