FA Form 4626 - AMT Summary - draft

Description
Categories: BI Publisher
Application: Assets
Source: Form 4626 - AMT Summary Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASAMTXS_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
Federal Book
 
LOV Oracle
AMT Book
 
LOV Oracle
Period
 
LOV Oracle
From Asset Account
 
Char
To Asset Account
 
Char
Acct Flex Structure
 
Number
Adjusted Report