FA Asset Additions Responsibility - draft

Description
Categories: BI Publisher
Application: Assets
Source: Asset Additions Responsibility Report (XML) - Not Supported: Reserved For Future Use
Short Name: FAS490_XML
DB package: FA_FAS490_XMLP_PKG
SELECT
        --&ACCT_FLEX_BAL_SEG                                COMP_CODE,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1, 
        --&ACCT_FLEX_COST_SEG                             COST_CTR,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1,
        EMP.FULL_NAME                                                   NAME,
        --&LOC_FLEX_ALL_SEG                                   LOCATION,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
        AD.ASSET_NUMBER                                    ASSET_NUMBER,
        AD.DESCRIPTION                                          ASSET_DESC,
        DH.UNITS_ASSIGNED	                      UNITS,
        AD.SERIAL_NUMBER                                    S_NUMBER,
        AD.TAG_NUMBER                                         T_NUMBER,
        BOOKS.LIFE_IN_MONTHS		BOOKS_LIFE,
        BOOKS.ADJUSTED_RATE		ADJ_RATE,
        BOOKS.PRODUCTION_CAPACITY	PROD,
        NVL(DS.BONUS_RATE,0)		BONUS_RATE,
        NVL(BOOKS.cost,0) COST,
        nvl(DD.DEPRN_RESERVE,0)   		RESERVE,
        NULL					TYPE
--Added during DT Fix
	, FA_FAS490_XMLP_PKG.nbvformula(:AS_COST, :AS_RESERVE) NBV, 
	FA_FAS490_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE, NVL ( DS.BONUS_RATE , 0 ), BOOKS.PRODUCTION_CAPACITY) D_LIFE
--End of DT Fix
FROM
        FA_TRANSACTION_HEADERS  TH,
        PER_ALL_PEOPLE_F            EMP,
        FA_LOCATIONS            LOC,
        GL_CODE_COMBINATIONS    CC,
        FA_ADDITIONS            AD,
        FA_DISTRIBUTION_HISTORY DH,
        FA_BOOKS                BOOKS,
        FA_DEPRN_SUMMARY        DS,
        FA_DEPRN_DETAIL         DD
WHERE
	&ACCT_FLEX_COST_SEG_W AND
        TH.DATE_EFFECTIVE              >= :Period1_POD  AND
        TH.DATE_EFFECTIVE              <= :Period1_PCD  AND
        TH.BOOK_TYPE_CODE               = :P_Book  AND
        TH.TRANSACTION_TYPE_CODE = 'TRANSFER IN'		AND
        TH.DATE_EFFECTIVE BETWEEN DH.DATE_EFFECTIVE AND NVL(DH.DATE_INEFFECTIVE,SYSDATE)
AND
       TH.DATE_EFFECTIVE BETWEEN   :PERIOD1_POD  AND   NVL(:PERIOD1_PCD,sysdate) AND
        DH.BOOK_TYPE_CODE               =  :P_Book  AND
        DH.ASSET_ID                     =  TH.ASSET_ID
AND
        BOOKS.DATE_EFFECTIVE           <=  TH.DATE_EFFECTIVE              AND
        nvl(BOOKS.DATE_INEFFECTIVE,SYSDATE)         >  TH.DATE_EFFECTIVE              AND
        BOOKS.ASSET_ID                  =  TH.ASSET_ID                    AND
        BOOKS.BOOK_TYPE_CODE            =  :P_Book  
AND
        DD.BOOK_TYPE_CODE               =  :P_Book  AND
        DD.ASSET_ID                     =  TH.ASSET_ID                    AND
        DD.DISTRIBUTION_ID              =  DH.DISTRIBUTION_ID             AND
        DD.DEPRN_SOURCE_CODE            =  'B'		AND
	DD.PERIOD_COUNTER = :Period1_PC-1
AND
        CC.CODE_COMBINATION_ID          =  DH.CODE_COMBINATION_ID         
AND
        AD.ASSET_ID                     =  TH.ASSET_ID
AND
        EMP.PERSON_ID(+)              =  DH.ASSIGNED_TO
AND
      TRUNC(SYSDATE)  BETWEEN EMP.EFFECTIVE_START_DATE(+) AND EMP.EFFECTIVE_END_DATE(+)
AND
        LOC.LOCATION_ID                 =  DH.LOCATION_ID
AND
        DS.ASSET_ID  (+)        =  BOOKS.ASSET_ID                 AND
        DS.BOOK_TYPE_CODE   (+)            =  :P_Book  AND
        DS.PERIOD_COUNTER    (+)           =  :Period1_PC
UNION all
SELECT
        --&ACCT_FLEX_BAL_SEG                                COMP_CODE,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1,
        --&ACCT_FLEX_COST_SEG                             COST_CTR,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1,
        EMP.FULL_NAME                                                   NAME,
        --&LOC_FLEX_ALL_SEG                                   LOCATION,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
        AD.ASSET_NUMBER                                    ASSET_NUMBER,
        AD.DESCRIPTION                                          ASSET_DESC,
        DH.UNITS_ASSIGNED	                     UNITS,
        AD.SERIAL_NUMBER                                    S_NUMBER,
        AD.TAG_NUMBER                                         T_NUMBER,
        BOOKS.LIFE_IN_MONTHS		BOOKS_LIFE,
        BOOKS.ADJUSTED_RATE		ADJ_RATE,
        BOOKS.PRODUCTION_CAPACITY	PROD,
        NVL(DS.BONUS_RATE,0)		BONUS_RATE,
        sum(CADJ.ADJUSTMENT_AMOUNT	*
	DECODE(CADJ.DEBIT_CREDIT_FLAG,'CR',-1,'DR',1))	COST,
        0  				RESERVE,
        'T'				TYPE
--Added during DT Fix
	, FA_FAS490_XMLP_PKG.nbvformula(:AS_COST, :AS_RESERVE) NBV, 
	FA_FAS490_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE, NVL ( DS.BONUS_RATE , 0 ), BOOKS.PRODUCTION_CAPACITY) D_LIFE
--End of DT Fix
FROM
        FA_TRANSACTION_HEADERS  TH,
        PER_ALL_PEOPLE_F           EMP,
        FA_LOCATIONS            LOC,
        GL_CODE_COMBINATIONS    CC,
        FA_ADDITIONS            AD,
        FA_DISTRIBUTION_HISTORY DH,
        FA_BOOKS                BOOKS,
	FA_DEPRN_SUMMARY        DS,
        	FA_ADJUSTMENTS CADJ
WHERE
        TH.DATE_EFFECTIVE              >= :Period1_POD  AND
        TH.DATE_EFFECTIVE              <= :Period1_PCD  AND
        TH.BOOK_TYPE_CODE               = :P_Book  AND
        TH.TRANSACTION_TYPE_CODE = 'TRANSFER'
AND
        DH.TRANSACTION_HEADER_ID_IN     =  TH.TRANSACTION_HEADER_ID       AND
        nvl(DH.DATE_INEFFECTIVE, :Period1_PCD+1)  >  :Period1_PCD           AND
        DH.BOOK_TYPE_CODE               =  :P_Book                          AND
        DH.ASSET_ID                     =  TH.ASSET_ID
AND
        BOOKS.DATE_EFFECTIVE           <=  TH.DATE_EFFECTIVE              AND
        nvl(BOOKS.DATE_INEFFECTIVE, SYSDATE)   >  TH.DATE_EFFECTIVE              AND
        BOOKS.ASSET_ID                  =  TH.ASSET_ID                    AND
        BOOKS.BOOK_TYPE_CODE            =  :P_Book       
AND
        CC.CODE_COMBINATION_ID          =  DH.CODE_COMBINATION_ID         AND
        &ACCT_FLEX_COST_SEG_W AND
	CADJ.BOOK_TYPE_CODE		= :P_BOOK  AND
	CADJ.ASSET_ID			= TH.ASSET_ID AND
	CADJ.DISTRIBUTION_ID                            	= DH.DISTRIBUTION_ID AND
	CADJ.TRANSACTION_HEADER_ID	= TH.TRANSACTION_HEADER_ID AND
	CADJ.SOURCE_TYPE_CODE		= 'TRANSFER' AND
	CADJ.PERIOD_COUNTER_CREATED = :Period1_PC  AND
	CADJ.ADJUSTMENT_TYPE		in ('COST','CIP COST') 
AND
        AD.ASSET_ID                     =  TH.ASSET_ID
AND
        EMP.PERSON_ID(+)              =  DH.ASSIGNED_TO
AND
        TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+) AND EMP.EFFECTIVE_END_DATE(+)
AND
        LOC.LOCATION_ID                 =  DH.LOCATION_ID
AND
        DS.ASSET_ID  (+)                   =  BOOKS.ASSET_ID                 AND
        DS.BOOK_TYPE_CODE (+)              =  :P_Book  AND
        DS.PERIOD_COUNTER   (+)            =  :Period1_PC
GROUP BY
      --&ACCT_FLEX_BAL_SEG,
        --&ACCT_FLEX_COST_SEG,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
        EMP.FULL_NAME,
        --&LOC_FLEX_ALL_SEG,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE'),
        AD.DESCRIPTION,
        DH.UNITS_ASSIGNED,
        AD.SERIAL_NUMBER,
        AD.TAG_NUMBER,
        AD.ASSET_NUMBER,
        BOOKS.LIFE_IN_MONTHS,
        BOOKS.ADJUSTED_RATE,
        BOOKS.PRODUCTION_CAPACITY,
        DS.BONUS_RATE
UNION all
SELECT
        --&ACCT_FLEX_BAL_SEG                                COMP_CODE,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1,
        --&ACCT_FLEX_COST_SEG                             COST_CTR,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE') D_COST_CTR1,
        EMP.FULL_NAME                                                   NAME,
        --&LOC_FLEX_ALL_SEG                                   LOCATION,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE') D_LOCATION1,
        AD.ASSET_NUMBER                                    ASSET_NUMBER,
        AD.DESCRIPTION                                          ASSET_DESC,
        DH.UNITS_ASSIGNED	                     UNITS,
        AD.SERIAL_NUMBER                                    S_NUMBER,
        AD.TAG_NUMBER                                         T_NUMBER,
        BOOKS.LIFE_IN_MONTHS		BOOKS_LIFE,
        BOOKS.ADJUSTED_RATE		ADJ_RATE,
        BOOKS.PRODUCTION_CAPACITY	PROD,
        NVL(DS.BONUS_RATE,0)		BONUS_RATE,
        0				COST,
        sum(RADJ.ADJUSTMENT_AMOUNT *
	DECODE(RADJ.DEBIT_CREDIT_FLAG,'CR',1,'DR',-1)) 	RESERVE,
        'T'				TYPE
--Added during DT Fix
	, FA_FAS490_XMLP_PKG.nbvformula(:AS_COST, :AS_RESERVE) NBV, 
	FA_FAS490_XMLP_PKG.d_lifeformula(BOOKS.LIFE_IN_MONTHS, BOOKS.ADJUSTED_RATE, NVL ( DS.BONUS_RATE , 0 ), BOOKS.PRODUCTION_CAPACITY) D_LIFE
--End of DT Fix
FROM
        FA_TRANSACTION_HEADERS  TH,
        PER_ALL_PEOPLE_F           EMP,
        FA_LOCATIONS            LOC,
        GL_CODE_COMBINATIONS    CC,
        FA_ADDITIONS            AD,
        FA_DISTRIBUTION_HISTORY DH,
        FA_DEPRN_SUMMARY        DS,
        FA_BOOKS                BOOKS,
	FA_ADJUSTMENTS RADJ
WHERE
        TH.DATE_EFFECTIVE              >= :Period1_POD  AND
        TH.DATE_EFFECTIVE              <= :Period1_PCD  AND
        TH.BOOK_TYPE_CODE               = :P_Book  AND
        TH.TRANSACTION_TYPE_CODE = 'TRANSFER'
AND
        DH.TRANSACTION_HEADER_ID_IN     =  TH.TRANSACTION_HEADER_ID       AND
        nvl(DH.DATE_INEFFECTIVE, :Period1_PCD+1)  >  :Period1_PCD  AND
        DH.BOOK_TYPE_CODE               =  :P_Book  AND
        DH.ASSET_ID                     =  TH.ASSET_ID
AND
        BOOKS.DATE_EFFECTIVE           <=  TH.DATE_EFFECTIVE              AND
        nvl(BOOKS.DATE_INEFFECTIVE, SYSDATE)   >  TH.DATE_EFFECTIVE              AND
        BOOKS.ASSET_ID                  =  TH.ASSET_ID                    AND
        BOOKS.BOOK_TYPE_CODE            =  :P_Book       
AND
        CC.CODE_COMBINATION_ID          =  DH.CODE_COMBINATION_ID         AND
        &ACCT_FLEX_COST_SEG_W AND
	RADJ.BOOK_TYPE_CODE		= :P_BOOK  AND
	RADJ.ASSET_ID			= TH.ASSET_ID AND
	RADJ.DISTRIBUTION_ID                            	= DH.DISTRIBUTION_ID AND
	RADJ.TRANSACTION_HEADER_ID	= TH.TRANSACTION_HEADER_ID AND
	RADJ.SOURCE_TYPE_CODE		= 'TRANSFER' AND
	RADJ.PERIOD_COUNTER_CREATED = :Period1_PC  AND
	RADJ.ADJUSTMENT_TYPE		= 'RESERVE'
AND
        AD.ASSET_ID                     =  TH.ASSET_ID
AND
        EMP.PERSON_ID(+)              =  DH.ASSIGNED_TO
AND
       TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE(+) AND EMP.EFFECTIVE_END_DATE(+)
AND
        LOC.LOCATION_ID                 =  DH.LOCATION_ID
AND
        DS.ASSET_ID  (+)                   =  BOOKS.ASSET_ID                 AND
        DS.BOOK_TYPE_CODE (+)              =  :P_Book  AND
        DS.PERIOD_COUNTER   (+)            =  :Period1_PC
GROUP BY
      --&ACCT_FLEX_BAL_SEG,
        --&ACCT_FLEX_COST_SEG,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_cost_ctr', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'FA_COST_CTR', 'Y', 'VALUE'),
        EMP.FULL_NAME,
        --&LOC_FLEX_ALL_SEG,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('d_location', 'OFA', 'LOC#', 101, NULL, loc.LOCATION_ID, 'ALL', 'Y', 'VALUE'),
        AD.DESCRIPTION,
        DH.UNITS_ASSIGNED,
        AD.SERIAL_NUMBER,
        AD.TAG_NUMBER,
        AD.ASSET_NUMBER,
        BOOKS.LIFE_IN_MONTHS,
        BOOKS.ADJUSTED_RATE,
        BOOKS.PRODUCTION_CAPACITY,
        DS.BONUS_RATE
--ORDER BY 1,2,3,4,5
ORDER BY 1, 2, 3, 4, 11, 12, 13, 6, 7, 10, 8, 9, 5, 16
Parameter Name SQL text Validation
Acct Flex Structure
 
Number
To Cost Center
 
From Cost Center
 
Period
 
LOV Oracle
Book
 
LOV Oracle