FA Asset Additions By Cost Center Report- Not Supported: Reserved For Future Use

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Asset Additions By Cost Center Report
Application: Assets
Source: Asset Additions By Cost Center Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS421_XML
DB package: FA_FAS421_XMLP_PKG
Run FA Asset Additions By Cost Center Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT
        UPS.MEANING                                             		ASSET_TYPE,
--        nvl(&acct_flex_cost_seg,'None')                        		COST_CENTER,
--        nvl(&acct_flex_bal_seg, 'None')                         		COMP_CODE,
        decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
               CB.ASSET_COST_ACCT)                              		GL_ACCOUNT,
--        &acct_flex_acct_seg                                  		EXP_ACCOUNT,
        AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION              ASSET_NUMBER,
        BOOKS.DATE_PLACED_IN_SERVICE		START_DATE,
        BOOKS.DEPRN_METHOD_CODE                                 	METHOD,
        BOOKS.LIFE_IN_MONTHS     			LIFE,
        BOOKS.PRODUCTION_CAPACITY			PROD,
        BOOKS.ADJUSTED_RATE				ADJ_RATE,
        DS.BONUS_RATE				BONUS_RATE,
	sum(decode(adj.debit_credit_flag,'DR',1,-1) 
				* nvl(adj.adjustment_amount,0))  COST,
	DS.YTD_DEPRN                       YTD_DEPRN,
	DS.DEPRN_RESERVE                   DEPRN_RESERVE,
	sum( decode(adj.debit_credit_flag,'DR',1,-1)
				* nvl(adj.adjustment_amount,0)) -
            nvl(DS.DEPRN_RESERVE,0)                            	NBV, 
/*	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_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER, 
	&D_EXP_ACCOUNT D_EXP_ACCOUNT, */
	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, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER1, 
	&D_EXP_ACCOUNT D_EXP_ACCOUNT1, 
	FA_FAS421_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE, DS.BONUS_RATE, BOOKS.PRODUCTION_CAPACITY) D_LIFE
FROM 
        FA_LOOKUPS              UPS,
        FA_CATEGORY_BOOKS       CB,
        GL_CODE_COMBINATIONS    DHCC,
        FA_DISTRIBUTION_HISTORY DH,
        FA_DEPRN_SUMMARY DS,
        FA_ADJUSTMENTS ADJ,
        FA_DEPRN_DETAIL	DD_B,
        FA_ASSET_HISTORY        AH,
        FA_ADDITIONS            AD,
        FA_BOOKS                BOOKS,
        FA_TRANSACTION_HEADERS  THADD
WHERE
        &ACCT_FLEX_COST_SEG_W AND
        THADD.DATE_EFFECTIVE           >=  :Period1_POD  AND
        THADD.DATE_EFFECTIVE            <  :Period1_PCD  AND
        THADD.BOOK_TYPE_CODE            =  :P_Book                        AND
        (
	  ( THADD.TRANSACTION_TYPE_CODE = 'CIP ADDITION' AND AD.ASSET_TYPE = 'CIP' ) OR
                  ( THADD.TRANSACTION_TYPE_CODE = 'ADDITION' AND AD.ASSET_TYPE = 'CAPITALIZED' )
       )
 AND
        BOOKS.TRANSACTION_HEADER_ID_IN  =  THADD.TRANSACTION_HEADER_ID  AND
        BOOKS.BOOK_TYPE_CODE            =   :P_Book                       AND
        BOOKS.ASSET_ID                  =  THADD.ASSET_ID
AND
        DS.BOOK_TYPE_CODE               =  :P_Book                        AND
        DS.ASSET_ID                     =  BOOKS.ASSET_ID               AND
        DS.DEPRN_SOURCE_CODE            =  'BOOKS'
AND
        ADJ.BOOK_TYPE_CODE               = THADD.BOOK_TYPE_CODE        AND
        ADJ.ASSET_ID                   =  THADD.ASSET_ID               		AND
        ADJ.TRANSACTION_HEADER_ID = THADD.TRANSACTION_HEADER_ID AND
        ADJ.PERIOD_COUNTER_CREATED = :PERIOD1_PC  AND
        ADJ.adjustment_type	= DECODE(THADD.transaction_type_code, 'CIP ADDITION','CIP COST', 'ADDITION', 'COST')			AND
        ADJ.DISTRIBUTION_ID              =  DH.DISTRIBUTION_ID       
AND
       DD_B.BOOK_TYPE_CODE(+) 		= :P_BOOK 		AND
       DD_B.ASSET_ID(+)			= DH.ASSET_ID		AND
       DD_B.DEPRN_SOURCE_CODE(+) 		= 'B'			AND
       DD_B.DISTRIBUTION_ID(+)	 	= DH.DISTRIBUTION_ID
AND
        DH.BOOK_TYPE_CODE               =  :distribution_source_book      AND
        DH.ASSET_ID                     =  THADD.ASSET_ID
AND
        DHCC.CODE_COMBINATION_ID          =  DH.CODE_COMBINATION_ID   AND
        AD.ASSET_ID                     =  THADD.ASSET_ID
AND
        AH.ASSET_ID                     =  AD.ASSET_ID                  AND
        AH.DATE_EFFECTIVE              <=  :Period1_PCD  AND
        nvl(AH.DATE_INEFFECTIVE,
            sysdate)                   >=  :Period1_PCD  AND
        AH.ASSET_TYPE                   =  UPS.LOOKUP_CODE
AND
        UPS.LOOKUP_TYPE                 =  'ASSET TYPE'
AND
        CB.CATEGORY_ID                  =  AH.CATEGORY_ID               AND
        CB.BOOK_TYPE_CODE               =  THADD.BOOK_TYPE_CODE
GROUP BY
        UPS.MEANING,
        THADD.BOOK_TYPE_CODE,
--        nvl(&acct_flex_cost_seg, 'None'),
--        nvl(&acct_flex_bal_seg, 'None'),
        decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,CB.ASSET_COST_ACCT),
--        &acct_flex_acct_seg,
        AD.ASSET_NUMBER,
        AD.DESCRIPTION,
        BOOKS.DATE_PLACED_IN_SERVICE,
        BOOKS.DEPRN_METHOD_CODE,
        BOOKS.LIFE_IN_MONTHS,
        BOOKS.PRODUCTION_CAPACITY,
        BOOKS.ADJUSTED_RATE,
        DS.BONUS_RATE
--Added during DT Fix
		,DS.YTD_DEPRN,
		DS.DEPRN_RESERVE
		,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'),
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
		&D_EXP_ACCOUNT
--End of DT Fix
UNION
SELECT
        UPS.MEANING                                             		ASSET_TYPE,
--        nvl(&acct_flex_cost_seg,'None')                        		COST_CENTER,
--        nvl(&acct_flex_bal_seg, 'None')                         		COMP_CODE,
        decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,
               CB.ASSET_COST_ACCT)                              		GL_ACCOUNT,
--        &acct_flex_acct_seg                                  		EXP_ACCOUNT,
        AD.ASSET_NUMBER || ' - ' || AD.DESCRIPTION              ASSET_NUMBER,
        BOOKS.DATE_PLACED_IN_SERVICE		START_DATE,
        BOOKS.DEPRN_METHOD_CODE                                 	METHOD,
        BOOKS.LIFE_IN_MONTHS     			LIFE,
        BOOKS.PRODUCTION_CAPACITY			PROD,
        BOOKS.ADJUSTED_RATE				ADJ_RATE,
        DS.BONUS_RATE				BONUS_RATE,
	sum(decode(adj.debit_credit_flag,'DR',1,-1) 
				* nvl(adj.adjustment_amount,0))  COST,
	DS.YTD_DEPRN                       YTD_DEPRN,
	DS.DEPRN_RESERVE                   DEPRN_RESERVE,
	sum( decode(adj.debit_credit_flag,'DR',1,-1)
				* nvl(adj.adjustment_amount,0)) -
            nvl(DS.DEPRN_RESERVE,0)                            	NBV
/*	,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_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER, 
	&D_EXP_ACCOUNT D_EXP_ACCOUNT */
--Added during DT Fix
	,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, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER1, 
	&D_EXP_ACCOUNT D_EXP_ACCOUNT1, 
	FA_FAS421_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE, DS.BONUS_RATE, BOOKS.PRODUCTION_CAPACITY) D_LIFE
--End of DT Fix
FROM 
        FA_LOOKUPS              UPS,
        FA_CATEGORY_BOOKS       CB,
        GL_CODE_COMBINATIONS    DHCC,
        FA_DISTRIBUTION_HISTORY DH,
        FA_DEPRN_SUMMARY 	DS,
        FA_ASSET_HISTORY        AH,
        FA_ADDITIONS            AD,
        FA_BOOKS                BOOKS,
        FA_TRANSACTION_HEADERS  THADD,
        fa_adjustments		adj
WHERE
        &ACCT_FLEX_COST_SEG_W
AND
        THADD.DATE_EFFECTIVE           >=  :Period1_POD  AND
        THADD.DATE_EFFECTIVE            <  :Period1_PCD  AND
        THADD.BOOK_TYPE_CODE            =  :P_BOOK   AND
        THADD.TRANSACTION_TYPE_CODE   in ('ADDITION')
AND
        BOOKS.TRANSACTION_HEADER_ID_IN  =  THADD.TRANSACTION_HEADER_ID  AND
        BOOKS.BOOK_TYPE_CODE            =  thadd.book_type_code                       AND
        BOOKS.ASSET_ID                  =  THADD.ASSET_ID
AND
        DS.BOOK_TYPE_CODE               =  books.book_type_code         AND
        DS.ASSET_ID                     =  BOOKS.ASSET_ID               AND
        DS.DEPRN_SOURCE_CODE            =  'BOOKS'
AND EXISTS (
Select 'x'
/* The below code is unnecessary and so commented
, 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_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CENTER, 
	&D_EXP_ACCOUNT D_EXP_ACCOUNT, 
	FA_FAS421_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE, DS.BONUS_RATE, BOOKS.PRODUCTION_CAPACITY) D_LIFE
*/
from fa_adjustments subadj, 
			fa_adjustments subadj2
	Where subadj.book_type_code = ds.book_type_code
	And     subadj.asset_id 	= ds.asset_id
	And     subadj.source_type_code = 'ADDITION'
	And     subadj.period_counter_created = :Period1_PC
	And     subadj2.book_type_code  = subadj.book_type_code
	And     subadj2.asset_id	= subadj.asset_id
	And     subadj2.source_type_code = 'ADJUSTMENT'
	And     subadj2.period_counter_created = subadj.period_counter_created)
AND 	ADJ.BOOK_TYPE_CODE  = DS.book_type_code 	AND
	ADJ.ASSET_ID  = DS.ASSET_ID 		AND
	ADJ.ADJUSTMENT_TYPE  LIKE '%COST'     AND
	ADJ.SOURCE_TYPE_CODE like 'CIP A%'      AND
                ADJ.Distribution_id = DH.Distribution_id
AND
        DH.BOOK_TYPE_CODE               = :Distribution_Source_Book      AND 
        DH.ASSET_ID                     =  THADD.ASSET_ID		AND
        DH.DATE_EFFECTIVE 	<= :Period1_PCD  AND
        NVL(DH.DATE_INEFFECTIVE,SYSDATE) >= :Period1_PCD 
AND
        DHCC.CODE_COMBINATION_ID          =  DH.CODE_COMBINATION_ID   AND
        AD.ASSET_ID                     =  THADD.ASSET_ID
AND
        AH.ASSET_ID                     =  AD.ASSET_ID                  AND
        AH.DATE_EFFECTIVE              <=  :Period1_PCD          AND
        nvl(AH.DATE_INEFFECTIVE,
            sysdate)                   >=  :Period1_PCD                      AND
        AH.ASSET_TYPE                   =  UPS.LOOKUP_CODE
AND
        UPS.LOOKUP_TYPE                 =  'ASSET TYPE'
AND
        CB.CATEGORY_ID                  =  AH.CATEGORY_ID               AND
        CB.BOOK_TYPE_CODE               =  books.book_type_code
GROUP BY
        UPS.MEANING,
        THADD.BOOK_TYPE_CODE,
--        nvl(&acct_flex_cost_seg, 'None'),
--        nvl(&acct_flex_bal_seg, 'None'),
        decode(AH.ASSET_TYPE, 'CIP', CB.CIP_COST_ACCT,CB.ASSET_COST_ACCT),
--        &acct_flex_acct_seg,
        AD.ASSET_NUMBER,
        AD.DESCRIPTION,
        BOOKS.DATE_PLACED_IN_SERVICE,
        BOOKS.DEPRN_METHOD_CODE,
        BOOKS.LIFE_IN_MONTHS,
        BOOKS.PRODUCTION_CAPACITY,
        BOOKS.ADJUSTED_RATE,
        DS.BONUS_RATE,
	DS.YTD_DEPRN,
	DS.DEPRN_RESERVE
--Added during DT Fix
		,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'),
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_center', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
		&D_EXP_ACCOUNT
--End of DT Fix
ORDER BY
14,1,15,2,16,3,4,5,6,7,8,9,10,11,12,13
Parameter Name SQL text Validation
Book 
 
 LOV Oracle
Period 
 
 LOV Oracle
From Cost Center 
 
 Char
To Cost Center 
 
 Char
Download
Related Blitz Reports
FA Asset Additions FA Asset Additions - draft FA Asset Inventory FA CIP Statistics for the Central Statistical Office (KSH) (Hungary) FA Additions By Source FA Conversion Assets - draft FA Asset Additions Responsibility Report- Not Supported: Reserved For Future Use