FA Asset Disposals Responsibility - draft

Description
Categories: BI Publisher
Application: Assets
Source: Asset Disposals Responsibility Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS500_XML
DB package: FA_FAS500_XMLP_PKG
SELECT 	
	--&ACCT_FLEX_COST_SEG					Cost_Ctr,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1,
	--&ACCT_FLEX_BAL_SEG					Comp_Code,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1,
	EMP.FULL_Name						Name,
	--&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,
	AD.Description						Asset_Desc,
	abs(DH.Transaction_Units)				Units,
	AD.Serial_Number					S_Number,
	AD.Tag_Number						T_Number,
	AD.Asset_Number						Asset_Number,
	min(ROUND(decode(TH.Transaction_Type_Code,'TRANSFER',(BOOKS.COST 
		 * abs(DH.Transaction_Units) / AH.Units), 
		(NVL(MC.Cost_Retired,RET.Cost_Retired) / RET.Units * abs(DH.Transaction_Units))),:PRECISION))
									D_Cost,
	min(ROUND(decode(TH.Transaction_Type_Code,'TRANSFER',((BOOKS.COST  
		* abs(DH.Transaction_Units) / Ah.Units - 
		(nvl(DD.Deprn_Reserve,0) * abs(DH.Transaction_Units) / 
		DH.Units_Assigned))), (NVL(MC.NBV_Retired,nvl(RET.NBV_Retired,0))/ RET.Units * 
		abs(DH.Transaction_Units))),:PRECISION))			NBV,
	min(ROUND(decode(TH.Transaction_Type_Code,'TRANSFER', (-1 * (BOOKS.COST  
		* abs(DH.Transaction_Units) / Ah.Units - 
		(nvl(DD.Deprn_Reserve,0) * abs(DH.Transaction_Units) / 
		DH.Units_Assigned))), 
		(NVL(MC.Gain_Loss_Amount,nvl(RET.Gain_Loss_Amount,0)) / RET.Units * 
		abs(DH.Transaction_Units))),:PRECISION))			Gain_Loss,
	min(decode(	TH.Transaction_Type_Code,'TRANSFER OUT',
		(decode(RET.TRANSACTION_HEADER_ID_OUT, NULL,'P', '*')),
		'TRANSFER','T', (decode(RET.TRANSACTION_HEADER_ID_OUT, NULL,
		NULL, '*')))	)				Type
FROM	FA_MC_RETIREMENTS			                  MC,
	FA_RETIREMENTS_BAS				RET,
	FA_ADDITIONS					AD,
	GL_CODE_COMBINATIONS				DHCC,
	FA_BOOKS 					BOOKS,
	FA_ASSET_HISTORY				AH,
	FA_DISTRIBUTION_HISTORY				DH,
	FA_DISTRIBUTION_HISTORY				DH2,
	FA_TRANSACTION_HEADERS				TH,
	PER_ALL_PEOPLE_F					EMP,
	FA_LOCATIONS					LOC,
	FA_DEPRN_DETAIL					DD
WHERE 	
	&ACCT_FLEX_COST_SEG_W AND
	TH.DATE_EFFECTIVE	>= :PERIOD1_POD  AND
	TH.DATE_EFFECTIVE	<= :PERIOD1_PCD  AND
	TH.BOOK_TYPE_CODE 	= :P_BOOK  AND
	TH.TRANSACTION_TYPE_CODE in ('FULL RETIREMENT','TRANSFER',
	'TRANSFER OUT')
AND
	AH.ASSET_ID		= TH.ASSET_ID			AND
	TH.TRANSACTION_HEADER_ID BETWEEN 
		AH.TRANSACTION_HEADER_ID_IN AND 
		NVL(AH.TRANSACTION_HEADER_ID_OUT, TH.TRANSACTION_HEADER_ID)
AND
	DH.ASSET_ID = TH.ASSET_ID					AND
	DH.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE		   		AND
	NVL(DH.TRANSACTION_HEADER_ID_OUT,0) = decode(TH.TRANSACTION_TYPE_CODE,
				'FULL RETIREMENT', 
                                                                         nvl(DH.TRANSACTION_HEADER_ID_OUT,0),
				TH.TRANSACTION_HEADER_ID)	AND
	nvl(DH.TRANSACTION_UNITS,0) < 0
AND
	RET.DATE_EFFECTIVE	>= :PERIOD1_POD  AND   	RET.DATE_EFFECTIVE	<= :PERIOD1_PCD  AND   
	DH.RETIREMENT_ID = RET.RETIREMENT_ID(+)        AND
   	MC.RETIREMENT_ID(+)  = DECODE(:MRC_SOB_TYPE_CODE,'R',RET.RETIREMENT_ID,NULL)  AND
    	MC.SET_OF_BOOKS_ID(+) = :SET_OF_BOOKS_ID  AND
	DH.DISTRIBUTION_ID = DD.DISTRIBUTION_ID(+)	AND
	DD.PERIOD_COUNTER(+)  = :PERIOD1_PC  - 1          AND
	DD.BOOK_TYPE_CODE(+) = :P_BOOK
AND
	DH2.TRANSACTION_HEADER_ID_IN (+)= DH.TRANSACTION_HEADER_ID_OUT	AND
	(DH2.CODE_COMBINATION_ID != DH.CODE_COMBINATION_ID            OR
	 TH.TRANSACTION_TYPE_CODE !=  'TRANSFER')
AND
	AD.ASSET_ID = DH.ASSET_ID
AND
	TH.TRANSACTION_HEADER_ID  > BOOKS.TRANSACTION_HEADER_ID_IN AND
	TH.TRANSACTION_HEADER_ID  <=  NVL(BOOKS.TRANSACTION_HEADER_ID_OUT, 
		    TH.TRANSACTION_HEADER_ID)				AND
	BOOKS.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE			AND
	BOOKS.ASSET_ID = TH.ASSET_ID	
AND
	LOC.LOCATION_ID = DH.LOCATION_ID
AND
	EMP.PERSON_ID(+) = DH.ASSIGNED_TO
AND  
	TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+) AND EMP.EFFECTIVE_END_DATE(+)
AND
	DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID				
GROUP BY 
	--&ACCT_FLEX_BAL_SEG,
	--&ACCT_FLEX_COST_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
	EMP.FULL_NAME,
	--&LOC_FLEX_ALL_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE'),
	AD.ASSET_NUMBER,
	AD.DESCRIPTION,
	ABS(DH.TRANSACTION_UNITS),
	AD.SERIAL_NUMBER,
	AD.TAG_NUMBER
--Added during DT Fix
order by 2, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13
--End of DT Fix
Parameter NameSQL textValidation
Book
 
LOV Oracle
Period
 
LOV Oracle
From Cost Center
 
To Cost Center
 
Acct Flex Structure
 
Number