FA Reserve Adjustments - draft

SELECT
                  AJ.BOOK_TYPE_CODE                          BOOK_TYPE,
	--&ACCT_FLEX_BAL_SEG	            COMP_CODE,
	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_CODE1, 
	DP.FISCAL_YEAR		            FISCAL_YEAR,
	CB.DEPRN_RESERVE_ACCT               RESERVE,	
	AD.ASSET_NUMBER	            ASSET_NUMBER,
	--&CAT_FLEX_ALL_SEG	            ASSET_CATEGORY,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY1,
	AD.DESCRIPTION		            DESCRIPTION,
	ROUND(sum(decode(aj.debit_credit_flag, 'CR',aj.adjustment_amount,aj.adjustment_amount
	 *(-1))* nvl(dh.units_assigned,ah.units) / ah.units),:PRECISION)	 adjustment,
	TH.DATE_EFFECTIVE	            TRANSACTION_DATE,
	TH.TRANSACTION_HEADER_ID            TRX_HEADER 
/*	,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_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY*/
FROM
	FA_DEPRN_PERIODS		  DP,
	FA_ADJUSTMENTS			  AJ,
	FA_TRANSACTION_HEADERS		  TH,
	FA_ADDITIONS			  AD,
	FA_ASSET_HISTORY		  AH,
	FA_CATEGORIES			  CAT,
	FA_CATEGORY_BOOKS		  CB,	
	fa_books				  books,
	fa_distribution_history		  dh,
	gl_code_combinations		  dhcc
WHERE
	AJ.book_type_code		=	:P_BOOK  AND
	AJ.source_type_code		=	'TAX'  AND
	AJ.adjustment_type		=	'RESERVE'  AND
	AJ.transaction_header_id	=	TH.transaction_header_id
AND
	TH.date_effective	>=	:PERIOD1_POD  AND
	TH.date_effective	<=	nvl(:PERIOD2_PCD,sysdate)
AND
	TH.book_type_code		=	:P_BOOK  AND
	TH.asset_id 			=	AJ.asset_id AND
	TH.transaction_type_code	=	'TAX'
AND
 	AD.asset_id			=	TH.asset_id AND
	AH.category_id			=	CAT.category_id	
AND
	AH.ASSET_ID			=	AD.ASSET_ID 	AND
	AH.DATE_EFFECTIVE		<=	TH.DATE_EFFECTIVE	AND
	nvl(AH.DATE_INEFFECTIVE,sysdate+1) >	TH.DATE_EFFECTIVE
AND
	DP.book_type_code		= 	TH.book_type_code AND
	DP.period_counter	        =       AJ.period_counter_adjusted 
AND
	CB.category_id			=	CAT.category_id AND
	CB.book_type_code		=	TH.book_type_code
and
	dhcc.code_combination_id	=	dh.code_combination_id
and
	books.book_type_code		=	th.book_type_code	and
	books.asset_id			=	th.asset_id		and
	th.date_effective		>	books.date_effective	and
	th.date_effective		<=	nvl(books.date_ineffective,
						th.date_effective)	
and
	dh.book_type_code(+)	=  :DISTRIBUTION_SOURCE_BOOK  and
	dh.asset_id(+)			=	th.asset_id		and
	th.date_effective		>	dh.date_effective	and
	th.date_effective		<=	nvl(dh.date_ineffective,
						th.date_effective)
GROUP BY
	AJ.BOOK_TYPE_CODE,
	--&ACCT_FLEX_BAL_SEG,
	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'),
	DP.FISCAL_YEAR,
	CB.DEPRN_RESERVE_ACCT,
	AD.ASSET_NUMBER,
	--&CAT_FLEX_ALL_SEG,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
	AD.DESCRIPTION,
	TH.DATE_EFFECTIVE,
	TH.TRANSACTION_HEADER_ID,
	TH.transaction_date_entered
ORDER BY
	DP.FISCAL_YEAR,
	CB.DEPRN_RESERVE_ACCT,
	AD.ASSET_NUMBER,
	TH.transaction_date_entered