Reports2017-11-18T12:27:27+00:00

FA Adjusted Form 4626 - AMT Detail - draft

Description
Categories: BI Publisher
Application: Assets
Source: Adjusted Form 4626 - AMT Detail Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASAAMTD_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
By continuing to use the site, you agree to the use of cookies. Accept