FA Form 4626 - AMT Detail - draft

Description
Categories: BI Publisher
Application: Assets
Source: Form 4626 - AMT Detail Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASAMTXD_XML
DB package: FA_FASAAMTD_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,
	--&CAT_FLEX_ALL_SEG				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('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP, 
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY1, 
	--&COMP_CODE BAL_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') BAL_SEG, 
	FA_FASAAMTD_XMLP_PKG.fed_deprnformula(:FED_DD, :FED_ADJUST) FED_DEPRN, 
	FA_FASAAMTD_XMLP_PKG.amt_deprnformula(:AMT_DD, :AMT_ADJUST) AMT_DEPRN, 
	FA_FASAAMTD_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,
	--&CAT_FLEX_ALL_SEG				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('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP, 
	--fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') COMP_CODE_DSP1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_category', 'OFA', 'CAT#', 101, NULL, cat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_CATEGORY1, 
	--&COMP_CODE BAL_SEG, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') BAL_SEG, 
	FA_FASAAMTD_XMLP_PKG.fed_deprnformula(:FED_DD, :FED_ADJUST) FED_DEPRN, 
	FA_FASAAMTD_XMLP_PKG.amt_deprnformula(:AMT_DD, :AMT_ADJUST) AMT_DEPRN, 
	FA_FASAAMTD_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	5, 1, 2, 6, 3
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
Ask a question