FA Tax Additions - draft

SELECT	--null COMP_CODE,
&ACCT_FLEX_BAL_SEG  COMP_CODE,
	FY.FISCAL_YEAR	FISCAL_YEAR,
	CB.ASSET_COST_ACCT	GL_ACCOUNT,
       	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,
	BOOKS.LIFE_IN_MONTHS		LIFE,
	BOOKS.PRODUCTION_CAPACITY	PROD,
	BOOKS.ADJUSTED_RATE		ADJ_RATE,
	DS.BONUS_RATE			BONUS_RATE,
                SUM(NVL(BOOKS.COST,0))  COST,
	sum(nvl(DD.DEPRN_RESERVE,0))    DEPRN_RESERVE, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', COMCC.CHART_OF_ACCOUNTS_ID, NULL, COMCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE, 
	FA_FAS540_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE , DS.BONUS_RATE , BOOKS.PRODUCTION_CAPACITY) D_LIFE
FROM
	FA_DEPRN_DETAIL		DD,
	FA_ADDITIONS            	AD,
	FA_BOOKS		BOOKS,
	FA_ASSET_HISTORY	AH,
	FA_DISTRIBUTION_HISTORY	DH,
	FA_DEPRN_PERIODS	DP,
	FA_CATEGORY_BOOKS	CB,
	FA_FISCAL_YEAR		FY,
	FA_DEPRN_SUMMARY	DS,
	GL_CODE_COMBINATIONS	COMCC,
	FA_TRANSACTION_HEADERS	THDIS,
	FA_TRANSACTION_HEADERS  THADD
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 =:DIST_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 
	DS.BOOK_TYPE_CODE = :P_BOOK AND 
	DS.ASSET_ID			=  BOOKS.ASSET_ID AND 
	DS.DEPRN_SOURCE_CODE	=  'BOOKS'
AND 
	DD.BOOK_TYPE_CODE = :P_BOOK AND
	DD.ASSET_ID			=  THADD.ASSET_ID	 AND 
	DD.DEPRN_SOURCE_CODE	=  'B' AND 
	DD.DISTRIBUTION_ID		=  DH.DISTRIBUTION_ID AND 
	COMCC.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 
	CB.CATEGORY_ID			=  AH.CATEGORY_ID	AND 
	CB.BOOK_TYPE_CODE = :P_BOOK
	AND 
	FY.FISCAL_YEAR_NAME =:FISCAL_YEAR_NAME AND
	FY.START_DATE <=  BOOKS.DATE_PLACED_IN_SERVICE	AND 
	FY.END_DATE     >=  BOOKS.DATE_PLACED_IN_SERVICE
GROUP BY
	&ACCT_FLEX_BAL_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', COMCC.CHART_OF_ACCOUNTS_ID, NULL, COMCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	FY.FISCAL_YEAR,
	CB.ASSET_COST_ACCT,
        	CB.DEPRN_RESERVE_ACCT,
        	AD.ASSET_NUMBER,
	AD.DESCRIPTION,	
	BOOKS.DATE_PLACED_IN_SERVICE,
	BOOKS.DEPRN_METHOD_CODE,
        	BOOKS.LIFE_IN_MONTHS, 
	BOOKS.PRODUCTION_CAPACITY,
	BOOKS.ADJUSTED_RATE,
	DS.BONUS_RATE
--ORDER BY 	1,	2,	3, 	4,	5
order by
COMP_CODE,FISCAL_YEAR,GL_ACCOUNT,RES_ACCOUNT,ASSET_NUMBER,START_DATE,METHOD,LIFE,COST,
DEPRN_RESERVE,ADJ_RATE,PROD,BONUS_RATE