FA Asset Additions By Cost Center - draft

Description
Categories: BI Publisher
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
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
Acct Flex Structure
 
Number
To Cost Center
 
From Cost Center
 
Period
 
LOV Oracle
Book
 
LOV Oracle