FA Fully Reserved Assets - draft

SELECT     
	DP.PERIOD_NAME						PERIOD_NAME,
	DP.PERIOD_COUNTER					PERIOD_COUNTER,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')					COMP_CODE,
	CB.ASSET_COST_ACCT					GL_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.ADJUSTED_RATE					ADJ_RATE,
	DS.BONUS_RATE						BONUS_RATE,
	BOOKS.PRODUCTION_CAPACITY				PROD,
	sum(DD.COST)						COST,
	sum(DD.DEPRN_AMOUNT)					DEPRN_AMOUNT,
	sum(DD.YTD_DEPRN)					YTD_DEPRN,
	sum(DD.DEPRN_RESERVE)					DEPRN_RESERVE, 
	--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_FAS402_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE, DS.BONUS_RATE, BOOKS.PRODUCTION_CAPACITY) D_LIFE
FROM        
	&lp_fa_deprn_periods DP, 
     	&lp_fa_deprn_detail DD, 
     	FA_DISTRIBUTION_HISTORY DH,
     	GL_CODE_COMBINATIONS DHCC,  
     	&lp_fa_books BOOKS, 
     	&lp_fa_book_controls BC,
     	FA_ADDITIONS_NON_SEC_VL AD, 
    	 &lp_fa_deprn_summary DS, 
     	FA_ASSET_HISTORY AH,
     	FA_CATEGORY_BOOKS CB
WHERE
	DP.BOOK_TYPE_CODE	=  :P_BOOK  AND
	DP.PERIOD_COUNTER	>= :PERIOD1_PC  AND
	DP.PERIOD_COUNTER	<= :PERIOD2_PC
AND
	BOOKS.PERIOD_COUNTER_FULLY_RESERVED = 
		(SELECT MAX(PERIOD_COUNTER_FULLY_RESERVED) 
		  FROM &LP_FA_BOOKS BKS_PC
		 WHERE BKS_PC.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
		 AND      BKS_PC.ASSET_ID  = BOOKS.ASSET_ID
		 AND      BKS_PC.PERIOD_COUNTER_FULLY_RESERVED BETWEEN :PERIOD1_PC AND :PERIOD2_PC)
AND
	BOOKS.PERIOD_COUNTER_FULLY_RESERVED =  DP.PERIOD_COUNTER 	AND
	BOOKS.BOOK_TYPE_CODE		=  DP.BOOK_TYPE_CODE		AND
    	BOOKS.DATE_EFFECTIVE		<= Nvl(DP.PERIOD_CLOSE_DATE, BOOKS.DATE_EFFECTIVE)		AND   
	NVL(BOOKS.DATE_INEFFECTIVE, SYSDATE+1) >  Nvl(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND
	DS.ASSET_ID		=  BOOKS.ASSET_ID			AND
	DS.PERIOD_COUNTER	=  DP.PERIOD_COUNTER			AND
	DS.BOOK_TYPE_CODE	=  DP.BOOK_TYPE_CODE
AND 
	DD.BOOK_TYPE_CODE		=  DP.BOOK_TYPE_CODE		AND
	DD.ASSET_ID			=  BOOKS.ASSET_ID		AND
	DD.DISTRIBUTION_ID		=  DH.DISTRIBUTION_ID		AND
	DD.PERIOD_COUNTER		=  DP.PERIOD_COUNTER 
AND
	AD.ASSET_ID		=  BOOKS.ASSET_ID
AND
	CB.CATEGORY_ID		=  AH.CATEGORY_ID			AND
	CB.BOOK_TYPE_CODE	=  BOOKS.BOOK_TYPE_CODE
AND
	AH.ASSET_ID		=  AD.ASSET_ID				AND
    	AH.DATE_EFFECTIVE	<= Nvl(DP.PERIOD_CLOSE_DATE, AH.DATE_EFFECTIVE)			AND   
	nvl(AH.DATE_INEFFECTIVE,SYSDATE + 1)   >  Nvl(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND
                  BC.BOOK_TYPE_CODE = BOOKS.BOOK_TYPE_CODE
AND
	DH.ASSET_ID = BOOKS.ASSET_ID					AND
	DH.BOOK_TYPE_CODE = BC.DISTRIBUTION_SOURCE_BOOK			AND
    	DH.DATE_EFFECTIVE <= Nvl(DP.PERIOD_CLOSE_DATE, DH.DATE_EFFECTIVE) and
	NVL(DH.DATE_INEFFECTIVE,SYSDATE + 1) > Nvl(DP.PERIOD_CLOSE_DATE, SYSDATE)
AND 	
	DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
GROUP BY
	DP.PERIOD_NAME,
	DP.PERIOD_COUNTER,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	CB.ASSET_COST_ACCT,
	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION,
	BOOKS.DATE_PLACED_IN_SERVICE,
	BOOKS.DEPRN_METHOD_CODE,
	BOOKS.LIFE_IN_MONTHS,
	BOOKS.ADJUSTED_RATE,
	DS.BONUS_RATE,
	BOOKS.PRODUCTION_CAPACITY,
	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')
ORDER BY
--Commented during DT Fix
/*	DP.PERIOD_COUNTER,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
	CB.ASSET_COST_ACCT,
	AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION*/
--Added during DT Fix
2,1,3,4,5,6,7,8,10,11,9,12,13,14,15
--End of DT Fix