FA CIP Capitalization - draft

SELECT &ACCT_FLEX_BAL_SEG		                COMP_CODE,
       FACB.CIP_COST_ACCT				ACCOUNT,
       &ACCT_FLEX_COST_SEG                             COST_CTR,
       FACB.ASSET_COST_ACCT				ASSET_ACCOUNT,
       FADD.ASSET_NUMBER||' - '||FADD.DESCRIPTION 	ASSET,
       FABKS.DATE_PLACED_IN_SERVICE 			DATE_IN_SERVICE, 
       FABKS.DEPRN_METHOD_CODE 			DEPRN_METHOD, 
       FABKS.LIFE_IN_MONTHS 			LIFE,
       FABKS.PRODUCTION_CAPACITY		CAPACITY,
       FABKS.ADJUSTED_RATE			ADJ_RATE,
       ROUND(DECODE(FATRANS.TRANSACTION_TYPE_CODE,'CIP REVERSE',-1*(FABKS.COST),
       FABKS.COST)*(SUM(FADH.UNITS_ASSIGNED)/FAHIST.UNITS),:PRECISION)
       COST, 
	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_CODE, 
	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_CTR, 
	FA_FASWIPCR_XMLP_PKG.d_lifeformula(FABKS.LIFE_IN_MONTHS, FABKS.ADJUSTED_RATE, FABKS.PRODUCTION_CAPACITY) D_LIFE
FROM	GL_CODE_COMBINATIONS 		DHCC,
	FA_DISTRIBUTION_HISTORY 	FADH,
	FA_CATEGORY_BOOKS 		FACB,
	FA_ASSET_HISTORY 		FAHIST,
	FA_ADDITIONS 			FADD,
	FA_BOOKS 			FABKS,
	FA_TRANSACTION_HEADERS 		FATRANS
WHERE	 FATRANS.TRANSACTION_TYPE_CODE = 'ADDITION'
AND	 FATRANS.BOOK_TYPE_CODE = :P_BOOK
AND	 FATRANS.DATE_EFFECTIVE BETWEEN
		:PERIOD1_POD 
		AND :PERIOD2_PCD
AND	 EXISTS (SELECT NULL
		 FROM FA_ASSET_HISTORY AH
		 WHERE AH.ASSET_ID = FATRANS.ASSET_ID
		 AND AH.ASSET_TYPE = 'CIP')
AND 	 FATRANS.ASSET_ID 				= FADH.ASSET_ID
AND 	 FATRANS.BOOK_TYPE_CODE				= FADH.BOOK_TYPE_CODE
AND 	 FATRANS.DATE_EFFECTIVE >= FADH.DATE_EFFECTIVE
AND 	 FATRANS.DATE_EFFECTIVE < NVL(FADH.DATE_INEFFECTIVE, SYSDATE)
AND	 FADH.CODE_COMBINATION_ID 			= DHCC.CODE_COMBINATION_ID
AND	 FATRANS.TRANSACTION_HEADER_ID 			= FABKS.TRANSACTION_HEADER_ID_IN
AND	 FABKS.ASSET_ID 				= FADD.ASSET_ID
AND	 FATRANS.TRANSACTION_HEADER_ID		= FAHIST.TRANSACTION_HEADER_ID_IN
AND	 FATRANS.ASSET_ID 				= FAHIST.ASSET_ID
AND	 FAHIST.CATEGORY_ID 				= FACB.CATEGORY_ID
AND	 FACB.BOOK_TYPE_CODE = :P_BOOK
GROUP BY &ACCT_FLEX_BAL_SEG,
         FACB.CIP_COST_ACCT,
         &ACCT_FLEX_COST_SEG,
	 FACB.ASSET_COST_ACCT,
         FADD.ASSET_NUMBER,
         FADD.DESCRIPTION,
         FABKS.DATE_PLACED_IN_SERVICE,
         FABKS.DEPRN_METHOD_CODE,
         FABKS.LIFE_IN_MONTHS,
         FABKS.PRODUCTION_CAPACITY,
         FABKS.ADJUSTED_RATE,
         FATRANS.TRANSACTION_TYPE_CODE,
         FABKS.COST,
         FAHIST.UNITS,
         FATRANS.ASSET_ID,
         FABKS.DATE_EFFECTIVE
		 --added
		 ,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')
ORDER BY &ACCT_FLEX_BAL_SEG,
	 FACB.CIP_COST_ACCT,
	 &ACCT_FLEX_COST_SEG,
	 FACB.ASSET_COST_ACCT,
	 FATRANS.ASSET_ID,
	 FABKS.DATE_EFFECTIVE