FA Conversion Assets - draft

Description
Categories: BI Publisher
Columns: Asset Type, Gl Account, Res Account, Asset Number, Start Date, Method, Rate Source Rule, Life, Adj Rate, Bonus Rate ...
Application: Assets
Source: Conversion Assets Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS423_XML
DB package: FA_FAS423_XMLP_PKG
SELECT
	UPS.MEANING						ASSET_TYPE, 
	--&ACCT_FLEX_BAL_SEG					COMP_CODE,
	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
	       CB.ASSET_COST_ACCT)					GL_ACCOUNT,
        	decode(AH.ASSET_TYPE, 'CIP', NULL, 
	       CB.DEPRN_RESERVE_ACCT)				RES_ACCOUNT,
        	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION			ASSET_NUMBER,
	BOOKS.DATE_PLACED_IN_SERVICE				START_DATE,
	BOOKS.DEPRN_METHOD_CODE				METHOD,
	MD.RATE_SOURCE_RULE					RATE_SOURCE_RULE,
BOOKS.LIFE_IN_MONTHS  LIFE,
	BOOKS.ADJUSTED_RATE					ADJ_RATE,
	DS.BONUS_RATE						BONUS_RATE,
	BOOKS.PRODUCTION_CAPACITY				PROD,
                BOOKS.cost                                     COST,
	sum(nvl(DD.YTD_DEPRN,0))					YTD_DEPRN,
	sum(nvl(DD.DEPRN_RESERVE,0))				DEPRN_RESERVE,
	THADD.TRANSACTION_HEADER_ID				TH_ID, 
	--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_DSP, 
	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, 
	FA_FAS423_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE, DS.BONUS_RATE, BOOKS.PRODUCTION_CAPACITY) D_LIFE
FROM
	FA_METHODS		MD,
	FA_DEPRN_DETAIL		DD,
	FA_LOOKUPS		UPS,
	FA_DEPRN_PERIODS	DP,
	FA_TRANSACTION_HEADERS  THADD,
	FA_TRANSACTION_HEADERS  THDIS,
	FA_BOOKS		BOOKS,
	FA_ADDITIONS            	AD,
	FA_ASSET_HISTORY	AH,
	FA_CATEGORY_BOOKS	CB,
	GL_CODE_COMBINATIONS	DHCC,
	FA_DISTRIBUTION_HISTORY	DH,
	FA_DEPRN_SUMMARY	DS
WHERE
	DP.BOOK_TYPE_CODE		=  :P_BOOK  AND
	DP.PERIOD_COUNTER	       	>=  :PERIOD1_PC  AND
	DP.PERIOD_COUNTER	       	<=  nvl(:PERIOD2_PC,
					       DP.PERIOD_COUNTER)	
AND
        THADD.DATE_EFFECTIVE 	       		>=  DP.PERIOD_OPEN_DATE		AND
	THADD.DATE_EFFECTIVE		<  nvl(DP.PERIOD_CLOSE_DATE,
					       sysdate)			AND
	THADD.BOOK_TYPE_CODE		=  :P_BOOK  AND
	THADD.TRANSACTION_TYPE_CODE 	=  'ADDITION'
AND
	THDIS.BOOK_TYPE_CODE		=  :DISTRIBUTION_SOURCE_BOOK  AND
	THDIS.TRANSACTION_TYPE_CODE	=  'TRANSFER IN'		AND
	THDIS.ASSET_ID			=  THADD.ASSET_ID
AND
	BOOKS.TRANSACTION_HEADER_ID_IN	=  THADD.TRANSACTION_HEADER_ID	AND
	BOOKS.BOOK_TYPE_CODE		=  :P_BOOK  AND
	BOOKS.ASSET_ID			=  THADD.ASSET_ID
AND
	MD.METHOD_CODE			=  BOOKS.DEPRN_METHOD_CODE	AND
	nvl(MD.LIFE_IN_MONTHS,-1)		=  nvl(BOOKS.LIFE_IN_MONTHS,-1)
AND
	DS.BOOK_TYPE_CODE		=  :P_BOOK  AND
	DS.ASSET_ID			=  BOOKS.ASSET_ID		AND
	DS.DEPRN_RESERVE		!=  0				AND
	DS.DEPRN_SOURCE_CODE		=  'BOOKS'
AND
	DD.BOOK_TYPE_CODE		=  :P_BOOK  AND
	DD.ASSET_ID			=  THADD.ASSET_ID		AND
	DD.DISTRIBUTION_ID		=  DH.DISTRIBUTION_ID		AND
	DD.DEPRN_SOURCE_CODE		=  'B'
AND
        	DH.DATE_EFFECTIVE 	       	<=  nvl(DP.PERIOD_CLOSE_DATE,
					       sysdate)			AND
	nvl(DH.DATE_INEFFECTIVE,
	    sysdate)	       	       	>=  nvl(DP.PERIOD_CLOSE_DATE,
					       sysdate)			AND
	DH.BOOK_TYPE_CODE		=  :DISTRIBUTION_SOURCE_BOOK  AND
	DH.ASSET_ID			=  THDIS.ASSET_ID
AND
	DHCC.CODE_COMBINATION_ID	= DH.CODE_COMBINATION_ID	
AND
	AD.ASSET_ID			=  THADD.ASSET_ID		
AND
	AH.ASSET_ID			=  AD.ASSET_ID			AND
	AH.DATE_EFFECTIVE	       	<= THADD.DATE_EFFECTIVE		AND
	nvl(AH.DATE_INEFFECTIVE,sysdate)	>  THADD.DATE_EFFECTIVE
AND
	AH.ASSET_TYPE			=  UPS.LOOKUP_CODE
AND
	UPS.LOOKUP_TYPE			= 'ASSET TYPE'		
AND
	CB.CATEGORY_ID			=  AH.CATEGORY_ID		AND
        	CB.BOOK_TYPE_CODE		=  BOOKS.BOOK_TYPE_CODE            
GROUP BY
	UPS.MEANING,
--	&ACCT_FLEX_BAL_SEG,
	decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT, CB.ASSET_COST_ACCT),
        decode(AH.ASSET_TYPE, 'CIP', NULL, CB.DEPRN_RESERVE_ACCT),
	AD.ASSET_NUMBER,
	AD.DESCRIPTION,
	BOOKS.DATE_PLACED_IN_SERVICE,
	MD.RATE_SOURCE_RULE,
	BOOKS.DEPRN_METHOD_CODE,
	BOOKS.ADJUSTED_RATE,
	DS.BONUS_RATE,
	BOOKS.PRODUCTION_CAPACITY,
	BOOKS.LIFE_IN_MONTHS,
	THADD.TRANSACTION_HEADER_ID,
                 BOOKS.COST
--Added during DT Fix
	,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')
--End of DT Fix
/*ORDER BY
	2,
	1,
	3,
	AD.ASSET_NUMBER*/
ORDER BY
15, 1, 2, 3, 4, 5, 6, 8, 14, 12, 13, 9, 10, 11, 12, 7
Parameter Name SQL text Validation
Book
 
LOV Oracle
From Period
 
LOV Oracle
To Period
 
LOV Oracle