FA Cost Adjustments - draft

SELECT	
	&ACCT_FLEX_BAL_SEG	 		COMP_CODE,
	UPS.MEANING   				ASSET_TYPE,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
                     	CB.ASSET_COST_ACCT) 		ASSET_ACCOUNT,
	&ACCT_FLEX_COST_SEG	  		COST_CENTER,
	ad.asset_number				ASSET,
	TH.TRANSACTION_HEADER_ID		THID,
	&CAT_FLEX_ALL_SEG     			CATEGORY,
	ad.description				DESCR,
	round(sum(books_old.cost * nvl(dh.units_assigned,ah.units)/
		ah.units), :PRECISION) 	OLD_COST,
	round(sum(books_new.cost * nvl(dh.units_assigned,ah.units)/
		ah.units), :PRECISION)  	NEW_COST,
	round(sum((books_new.cost - books_old.cost) 
		* nvl(dh.units_assigned,ah.units)/ah.units), :PRECISION) 	CHANGE, 
	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, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY
FROM
	FA_ASSET_HISTORY		AH,
	FA_ADDITIONS			AD,
	FA_CATEGORIES			CAT,
	FA_CATEGORY_BOOKS		CB,
		&LP_FA_BOOKS			BOOKS_OLD,   		&LP_FA_BOOKS			BOOKS_NEW, 	FA_LOOKUPS			UPS,
		&LP_FA_DEPRN_PERIODS		DP, 	FA_DISTRIBUTION_HISTORY 		DH,
	GL_CODE_COMBINATIONS		DHCC,
	FA_TRANSACTION_HEADERS		TH
WHERE
	DP.BOOK_TYPE_CODE = :P_BOOK AND
	DP.PERIOD_COUNTER >= :PERIOD1_PC AND
	DP.PERIOD_COUNTER <= NVL(:PERIOD2_PC, DP.PERIOD_COUNTER)
	AND
	TH.BOOK_TYPE_CODE      	=	DP.BOOK_TYPE_CODE		AND
	TH.DATE_EFFECTIVE BETWEEN DP.PERIOD_OPEN_DATE AND    
		NVL(DP.PERIOD_CLOSE_DATE, SYSDATE)			AND
	TH.TRANSACTION_TYPE_CODE IN ('ADJUSTMENT','CIP ADJUSTMENT')
AND	
	BOOKS_OLD.TRANSACTION_HEADER_ID_OUT = TH.TRANSACTION_HEADER_ID	AND
	BOOKS_OLD.BOOK_TYPE_CODE	    = TH.BOOK_TYPE_CODE
AND
	BOOKS_NEW.TRANSACTION_HEADER_ID_IN  = TH.TRANSACTION_HEADER_ID	AND
	BOOKS_NEW.BOOK_TYPE_CODE   	    = TH.BOOK_TYPE_CODE
AND
	AD.ASSET_ID		=	TH.ASSET_ID
AND
	UPS.LOOKUP_TYPE		=	'ASSET TYPE'	
AND
	CB.CATEGORY_ID		=	AH.CATEGORY_ID			AND
	CB.BOOK_TYPE_CODE	=	TH.BOOK_TYPE_CODE
AND
	CAT.CATEGORY_ID		=	CB.CATEGORY_ID
AND
	AH.ASSET_ID		=	AD.ASSET_ID			AND
	AH.ASSET_TYPE		= 	UPS.LOOKUP_CODE		AND
	TH.TRANSACTION_HEADER_ID >= AH.TRANSACTION_HEADER_ID_IN AND
	TH.TRANSACTION_HEADER_ID < NVL(AH.TRANSACTION_HEADER_ID_OUT, TH.TRANSACTION_HEADER_ID + 1)		
AND
	th.asset_id		=	dh.asset_id			and
	:DISTRIBUTION_SOURCE_BOOK  = 	dh.book_type_code			and
	TH.TRANSACTION_HEADER_ID >=  DH.TRANSACTION_HEADER_ID_IN 		AND
	TH.TRANSACTION_HEADER_ID < NVL(DH.TRANSACTION_HEADER_ID_OUT, TH.TRANSACTION_HEADER_ID + 1)
and
	dh.code_combination_id	=	dhcc.code_combination_id
HAVING
	round(sum(books_old.cost * nvl(dh.units_assigned,ah.units)/ah.units), :PRECISION)	
	!= round(sum(books_new.cost * nvl(dh.units_assigned,ah.units)/ah.units), :PRECISION)
GROUP BY
	UPS.MEANING,
	&ACCT_FLEX_BAL_SEG,
	&ACCT_FLEX_COST_SEG,
	DECODE(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
		CB.ASSET_COST_ACCT),
	ad.asset_number,
	TH.TRANSACTION_HEADER_ID,
	&CAT_FLEX_ALL_SEG,
	ad.description,
	--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') , 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') 
--ORDER BY 1,2,3,4,5,6
ORDER BY D_COMP_CODE,ASSET_TYPE,ASSET_ACCOUNT,D_COST_CTR,ASSET,THID,D_CATEGORY,
DESCR,OLD_COST,NEW_COST,CHANGE