FA Adjusted Form 4626 - AMT Summary - draft

Description
Categories: BI Publisher
Application: Assets
Source: Adjusted Form 4626 - AMT Summary Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASAAMTS_XML
DB package: FA_FASAAMTS_XMLP_PKG
SELECT	DISTINCT
	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,
	BK_FED.DEPRN_METHOD_CODE			FED_METHOD,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_seg', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE')				CATEGORY,
	DECODE(AH.ASSET_TYPE,'CIP',CB_AMT.CIP_COST_ACCT,
               			   CB_AMT.ASSET_COST_ACCT)	ACCT,
	AD.asset_number					ASSET_NUMBER,
	ad.asset_id					ASSET_ID, 
	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, 
	--&COMP_CODE 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')  BAL_SEG, 
	FA_FASAAMTS_XMLP_PKG.fed_deprnformula(:FED_DD,:FED_ADJUST) FED_DEPRN, 
	FA_FASAAMTS_XMLP_PKG.amt_deprnformula(:AMT_DD,:AMT_ADJUST) AMT_DEPRN, 
	FA_FASAAMTS_XMLP_PKG.diff_deprnformula(:FED_DEPRN, :AMT_DEPRN) DIFF_DEPRN
FROM
	FA_CATEGORY_BOOKS				CB_FED,
	FA_CATEGORY_BOOKS				CB_AMT,
	FA_CATEGORIES					CAT,
	FA_BOOKS					BK_FED,
	FA_BOOKS					BK_AMT,
	FA_DEPRN_PERIODS				DP_FED,
	FA_DEPRN_PERIODS				DP_AMT,
	FA_ADDITIONS					AD,
	GL_CODE_COMBINATIONS				DHCC,
	FA_ASSET_HISTORY 				AH,
	FA_DEPRN_DETAIL					DD,
	FA_DISTRIBUTION_HISTORY				DH
WHERE 	DD.ASSET_ID		=	AD.ASSET_ID			AND
	DD.BOOK_TYPE_CODE IN(:P_FED_BOOK,:P_AMT_BOOK)AND
	DD.PERIOD_COUNTER between :FED_START_PERIOD_PC and 
	:FED_END_PERIOD_PC AND
	DD.DEPRN_SOURCE_CODE	= 'D'
AND		
	BK_FED.BOOK_TYPE_CODE = :P_FED_BOOK AND 
	BK_AMT.BOOK_TYPE_CODE = :P_AMT_BOOK AND 
	BK_FED.ASSET_ID			= AD.ASSET_ID			AND
	BK_AMT.ASSET_ID			= AD.ASSET_ID			AND
	DP_FED.PERIOD_CLOSE_DATE	BETWEEN
		BK_FED.DATE_EFFECTIVE 	AND
		NVL(BK_FED.DATE_INEFFECTIVE, DP_FED.PERIOD_CLOSE_DATE)	AND
	DP_AMT.PERIOD_CLOSE_DATE	BETWEEN
		BK_AMT.DATE_EFFECTIVE	AND
		NVL(BK_AMT.DATE_INEFFECTIVE, DP_AMT.PERIOD_CLOSE_DATE)
AND
	CB_FED.BOOK_TYPE_CODE = :P_FED_BOOK AND
	CB_FED.CATEGORY_ID		= CB_AMT.CATEGORY_ID		AND
	CB_AMT.BOOK_TYPE_CODE = :P_AMT_BOOK AND 
	CB_AMT.CATEGORY_ID		= AH.CATEGORY_ID	
AND
	AH.ASSET_ID			= AD.ASSET_ID			AND
	DP_FED.PERIOD_CLOSE_DATE	BETWEEN
		AH.DATE_EFFECTIVE	AND
		NVL(AH.DATE_INEFFECTIVE, DP_FED.PERIOD_CLOSE_DATE)	AND
	DECODE(AH.ASSET_TYPE,'CIP',CB_AMT.CIP_COST_ACCT,
               			   CB_AMT.ASSET_COST_ACCT) 	
		BETWEEN NVL(:P_FROM_ACCT, DECODE(AH.ASSET_TYPE,'CIP',
				CB_AMT.CIP_COST_ACCT, CB_AMT.ASSET_COST_ACCT)) AND 
			NVL(:P_TO_ACCT, DECODE(AH.ASSET_TYPE,'CIP',
				CB_AMT.CIP_COST_ACCT, CB_AMT.ASSET_COST_ACCT))
AND
	dp_fed.period_counter=:PERIOD1_PC and 
	dp_fed.book_type_code = :P_FED_BOOK and 
	dp_AMT.period_counter=:PERIOD1_PC and
	dp_AMT.book_type_code=:P_AMT_BOOK 
	AND
	CB_AMT.CATEGORY_ID		= CAT.CATEGORY_ID
AND
	DH.DISTRIBUTION_ID		= DD.DISTRIBUTION_ID
AND
	DHCC.CODE_COMBINATION_ID		= DH.CODE_COMBINATION_ID
UNION
SELECT	DISTINCT
	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,
	BK_FED.DEPRN_METHOD_CODE			FED_METHOD,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_seg', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE')				CATEGORY,
	DECODE(AH.ASSET_TYPE,'CIP',CB_AMT.CIP_COST_ACCT,
               			   CB_AMT.ASSET_COST_ACCT)	ACCT,
	AD.asset_number					ASSET_NUMBER,
	ad.asset_id					ASSET_ID, 
	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, 
	--&COMP_CODE 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')  BAL_SEG,
	FA_FASAAMTS_XMLP_PKG.fed_deprnformula(:FED_DD,:FED_ADJUST) FED_DEPRN, 
	FA_FASAAMTS_XMLP_PKG.amt_deprnformula(:AMT_DD,:AMT_ADJUST) AMT_DEPRN, 
	FA_FASAAMTS_XMLP_PKG.diff_deprnformula(:FED_DEPRN,:AMT_DEPRN) DIFF_DEPRN
FROM
	FA_CATEGORY_BOOKS				CB_FED,
	FA_CATEGORY_BOOKS				CB_AMT,
	FA_CATEGORIES					CAT,
	FA_BOOKS					BK_FED,
	FA_BOOKS					BK_AMT,
	FA_DEPRN_PERIODS				DP_FED,
	FA_DEPRN_PERIODS				DP_AMT,
	FA_ADDITIONS					AD,
	GL_CODE_COMBINATIONS				DHCC,
	FA_ASSET_HISTORY 				AH,
	FA_ADJUSTMENTS					ADJ
       ,xla_ae_lines lines
       ,xla_distribution_links links
       ,fa_transaction_headers th
WHERE	
	AD.ASSET_ID 	=	ADJ.ASSET_ID		AND
	ADJ.BOOK_TYPE_CODE IN (:P_FED_BOOK,:P_AMT_BOOK)	AND
	ADJ.PERIOD_COUNTER_ADJUSTED 
	BETWEEN :FED_START_PERIOD_PC AND :FED_END_PERIOD_PC
AND		
	BK_FED.BOOK_TYPE_CODE = :P_FED_BOOK AND 
	BK_AMT.BOOK_TYPE_CODE = :P_AMT_BOOK AND 
	BK_FED.ASSET_ID			= AD.ASSET_ID			AND
	BK_AMT.ASSET_ID			= AD.ASSET_ID			AND
	DP_FED.PERIOD_CLOSE_DATE	BETWEEN
		BK_FED.DATE_EFFECTIVE 	AND
		NVL(BK_FED.DATE_INEFFECTIVE, DP_FED.PERIOD_CLOSE_DATE)	AND
	DP_AMT.PERIOD_CLOSE_DATE	BETWEEN
		BK_AMT.DATE_EFFECTIVE	AND
		NVL(BK_AMT.DATE_INEFFECTIVE, DP_AMT.PERIOD_CLOSE_DATE)
AND
	CB_FED.BOOK_TYPE_CODE = :P_FED_BOOK AND 
	CB_FED.CATEGORY_ID		= CB_AMT.CATEGORY_ID		AND
	CB_AMT.BOOK_TYPE_CODE = :P_AMT_BOOK AND 
	CB_AMT.CATEGORY_ID		= AH.CATEGORY_ID	
AND
	AH.ASSET_ID			= AD.ASSET_ID			AND
	DP_FED.PERIOD_CLOSE_DATE	BETWEEN
		AH.DATE_EFFECTIVE	AND
		NVL(AH.DATE_INEFFECTIVE, DP_FED.PERIOD_CLOSE_DATE)	AND
	DECODE(AH.ASSET_TYPE,'CIP',CB_AMT.CIP_COST_ACCT,
               			   CB_AMT.ASSET_COST_ACCT) 	
		BETWEEN NVL(:P_FROM_ACCT, DECODE(AH.ASSET_TYPE,'CIP',
				CB_AMT.CIP_COST_ACCT, CB_AMT.ASSET_COST_ACCT)) AND 
			NVL(:P_TO_ACCT, DECODE(AH.ASSET_TYPE,'CIP',
				CB_AMT.CIP_COST_ACCT, CB_AMT.ASSET_COST_ACCT))
AND
	dp_fed.period_counter=:PERIOD1_PC and 
	dp_fed.book_type_code = :P_FED_BOOK and 
	dp_AMT.period_counter=:PERIOD1_PC and 
	dp_AMT.book_type_code=:P_AMT_BOOK 
	AND
	CB_AMT.CATEGORY_ID		= CAT.CATEGORY_ID
AND
		lines.CODE_COMBINATION_ID	=	DHCC.CODE_COMBINATION_ID
AND :P_ADJUSTED ='TRUE'
and th.transaction_header_id = adj.transaction_header_id
and links.event_id = th.event_id
and links.Source_distribution_id_num_1 = adj.transaction_header_id
and links.Source_distribution_id_num_2 = adj.adjustment_line_id
and links.application_id = 140
and lines.ae_header_id = links.ae_header_id
and lines.ae_line_num = links.ae_line_num
and lines.application_id = 140
and links.source_distribution_type = 'TRX'
--ORDER BY 1,2,3,4,5
order by D_CATEGORY
Parameter Name SQL text Validation
Adjusted Report
 
Acct Flex Structure
 
Number
To Asset Account
 
From Asset Account
 
End Period
 
LOV Oracle
AMT Book
 
LOV Oracle
Federal Book
 
LOV Oracle