SELECT
&C_ALL_SEGS ASSET_CATEGORY,
FADD.ASSET_CATEGORY_ID ASSET_CAT_ID,
FDS.ASSET_ID ASSET_ID,
BKS.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN,
BKS.TRANSACTION_HEADER_ID_OUT TRANSACTION_HEADER_ID_OUT,
FADD.ASSET_NUMBER ASSET_NUMBER,
FADD.DESCRIPTION DESCRIPTION,
BKS.DATE_PLACED_IN_SERVICE ACQDATE,
BKS.PRORATE_DATE PRORATE_DATE,
TO_NUMBER(NULL) RETIREMENT_ID,
NVL( BKS.COST,0) ORIGINAL_COST,
NVL( FDS.DEPRN_RESERVE,0) DEPRN_RESERVE,
FDS.PERIOD_COUNTER,
BKS.PERIOD_COUNTER_FULLY_RESERVED,
'N' STATUS,
0 RETIREMENT_ID_ORDER,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_segs', 'OFA', 'CAT#', 101, NULL, ct.CATEGORY_ID, 'ALL', 'Y', 'DESCRIPTION') cat_flex_all_segs,
JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('N', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, TO_NUMBER ( NULL ), NVL ( BKS.COST , 0 )) C_ACCUM_DEPRN,
JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL ( BKS.COST , 0 ), TO_NUMBER ( NULL )) C_ACCUM_DEPRN_PREV_YR,
JL_JLMXFISR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('N', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, TO_NUMBER ( NULL ), NVL ( BKS.COST , 0 )), JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL ( BKS.COST , 0 ), TO_NUMBER ( NULL ))) C_ACCUM_DEPRN_CURR_YR,
JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE) C_PRICE_INDEX,
JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE) C_INDEX_VALUE,
JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(TO_NUMBER ( NULL ), BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)) C_INDEX_VALUE_HALF_PERIOD,
JL_JLMXFISR_XMLP_PKG.c_corr_factorformula(JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(TO_NUMBER ( NULL ), BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)), JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE)) C_CORR_FACTOR,
JL_JLMXFISR_XMLP_PKG.c_adj_accum_deprnformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('N', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, TO_NUMBER ( NULL ), NVL ( BKS.COST , 0 )), JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL ( BKS.COST , 0 ), TO_NUMBER ( NULL ))),JL_JLMXFISR_XMLP_PKG.c_corr_factorformula(JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(TO_NUMBER ( NULL ), BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)), JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE)), FDS.ASSET_ID, BKS.TRANSACTION_HEADER_ID_IN, BKS.TRANSACTION_HEADER_ID_OUT, FADD.DESCRIPTION, FADD.ASSET_CATEGORY_ID, FADD.ASSET_NUMBER, BKS.DATE_PLACED_IN_SERVICE, BKS.PRORATE_DATE, TO_NUMBER ( NULL ),JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE), NVL ( BKS.COST , 0 ),JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL ( BKS.COST , 0 ), TO_NUMBER ( NULL )), BKS.PERIOD_COUNTER_FULLY_RESERVED) C_ADJ_ACCUM_DEPRN,
JL_JLMXFISR_XMLP_PKG.cf_retirement_date(TO_NUMBER ( NULL )) CF_RETIREMENT_DATE
FROM FA_CATEGORIES CT,
FA_ADDITIONS FADD,
&lp_fa_books BKS,
&lp_fa_deprn_summary FDS,
FA_TRANSACTION_HEADERS TRH
WHERE FDS.BOOK_TYPE_CODE = :P_BOOK_TYPE_CODE
AND BKS.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND BKS.ASSET_ID = FDS.ASSET_ID
AND BKS.TRANSACTION_HEADER_ID_IN = TRH.TRANSACTION_HEADER_ID
AND TRH.TRANSACTION_TYPE_CODE <> 'FULL RETIREMENT'
AND BKS.TRANSACTION_HEADER_ID_IN = (SELECT MAX(BKS1.TRANSACTION_HEADER_ID_IN)
FROM &lp_fa_books BKS1,
FA_TRANSACTION_HEADERS TRH1
WHERE BKS1.BOOK_TYPE_CODE = BKS.BOOK_TYPE_CODE
AND BKS1.ASSET_ID = BKS.ASSET_ID
AND BKS1.TRANSACTION_HEADER_ID_IN = TRH1.TRANSACTION_HEADER_ID
AND TRH1.TRANSACTION_DATE_ENTERED <= :C_FISCAL_END_DATE)
AND BKS.DATE_PLACED_IN_SERVICE <= :C_FISCAL_END_DATE
AND FADD.ASSET_ID = BKS.ASSET_ID
AND FADD.ASSET_TYPE = 'CAPITALIZED'
AND FDS.PERIOD_COUNTER >= (SELECT MIN(FDP2.PERIOD_COUNTER)
FROM FA_ASSET_HISTORY FAH2,
FA_DEPRN_PERIODS FDP2
WHERE FAH2.ASSET_ID = FADD.ASSET_ID
AND FAH2.ASSET_TYPE = 'CAPITALIZED'
AND FDP2.FISCAL_YEAR <= :P_CURR_FY
AND FDP2.BOOK_TYPE_CODE = :P_BOOK_TYPE_CODE
AND FAH2.DATE_EFFECTIVE BETWEEN FDP2.PERIOD_OPEN_DATE
AND FDP2.PERIOD_CLOSE_DATE)
AND CT.CATEGORY_ID = FADD.ASSET_CATEGORY_ID
AND FDS.PERIOD_COUNTER = (SELECT MAX(PERIOD_COUNTER)
FROM &lp_fa_deprn_summary FDS2
WHERE FDS2.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND FDS2.ASSET_ID = FDS.ASSET_ID
AND FDS2.PERIOD_COUNTER >= decode(:p_include_zero_nbv_assets,'Y',fds2.period_counter,:C_MIN_PERIOD_COUNTER)
AND FDS2.PERIOD_COUNTER <= :C_MAX_PERIOD_COUNTER)
UNION
SELECT
&C_ALL_SEGS ASSET_CATEGORY,
FADD.ASSET_CATEGORY_ID ASSET_CAT_ID,
FDS.ASSET_ID ASSET_ID,
BKS.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN,
BKS.TRANSACTION_HEADER_ID_OUT TRANSACTION_HEADER_ID_OUT,
FADD.ASSET_NUMBER ASSET_NUMBER,
FADD.DESCRIPTION DESCRIPTION,
BKS.DATE_PLACED_IN_SERVICE ACQDATE,
BKS.PRORATE_DATE PRORATE_DATE,
BKS.RETIREMENT_ID RETIREMENT_ID,
NVL( RET.COST_RETIRED,0) ORIGINAL_COST,
NVL( FDS.DEPRN_RESERVE,0) DEPRN_RESERVE,
FDS.PERIOD_COUNTER,
BKS.PERIOD_COUNTER_FULLY_RESERVED,
'R' STATUS,
BKS.RETIREMENT_ID RETIREMENT_ID_ORDER,
fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_segs', 'OFA', 'CAT#', 101, NULL, ct.CATEGORY_ID, 'ALL', 'Y', 'DESCRIPTION') cat_flex_all_segs,
JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('R', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, BKS.RETIREMENT_ID, NVL( RET.COST_RETIRED,0)) C_ACCUM_DEPRN,
JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL( RET.COST_RETIRED,0), BKS.RETIREMENT_ID) C_ACCUM_DEPRN_PREV_YR,
JL_JLMXFISR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('R', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, BKS.RETIREMENT_ID, NVL( RET.COST_RETIRED,0)), JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL( RET.COST_RETIRED,0), BKS.RETIREMENT_ID)) C_ACCUM_DEPRN_CURR_YR,
JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE) C_PRICE_INDEX,
JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE) C_INDEX_VALUE,
JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(BKS.RETIREMENT_ID, BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)) C_INDEX_VALUE_HALF_PERIOD,
JL_JLMXFISR_XMLP_PKG.c_corr_factorformula(JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(BKS.RETIREMENT_ID, BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)) ,JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE)) C_CORR_FACTOR,
JL_JLMXFISR_XMLP_PKG.c_adj_accum_deprnformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('R', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, BKS.RETIREMENT_ID, NVL( RET.COST_RETIRED,0)), JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL( RET.COST_RETIRED,0), BKS.RETIREMENT_ID)),JL_JLMXFISR_XMLP_PKG.c_corr_factorformula(JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(BKS.RETIREMENT_ID, BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)), JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE)), FDS.ASSET_ID, BKS.TRANSACTION_HEADER_ID_IN, BKS.TRANSACTION_HEADER_ID_OUT, FADD.DESCRIPTION, FADD.ASSET_CATEGORY_ID, FADD.ASSET_NUMBER, BKS.DATE_PLACED_IN_SERVICE, BKS.PRORATE_DATE, BKS.RETIREMENT_ID,JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE), NVL( RET.COST_RETIRED,0),JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL( RET.COST_RETIRED,0), BKS.RETIREMENT_ID), BKS.PERIOD_COUNTER_FULLY_RESERVED) C_ADJ_ACCUM_DEPRN,
JL_JLMXFISR_XMLP_PKG.cf_retirement_date(BKS.RETIREMENT_ID) CF_RETIREMENT_DATE
FROM FA_CATEGORIES CT,
FA_ADDITIONS FADD,
&lp_fa_books BKS,
&lp_fa_deprn_summary FDS,
&lp_fa_retirements RET,
&lp_fa_deprn_periods FDP
WHERE FDS.BOOK_TYPE_CODE = :P_BOOK_TYPE_CODE
AND BKS.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
AND BKS.ASSET_ID = FDS.ASSET_ID
AND BKS.DATE_PLACED_IN_SERVICE <= :C_FISCAL_END_DATE
AND BKS.RETIREMENT_ID = RET.RETIREMENT_ID
AND RET.STATUS <> 'DELETED'
AND (RET.DATE_RETIRED BETWEEN :C_FISCAL_START_DATE AND :C_FISCAL_END_DATE)
AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
AND RET.DATE_RETIRED BETWEEN FDP.CALENDAR_PERIOD_OPEN_DATE AND FDP.CALENDAR_PERIOD_CLOSE_DATE
AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER
AND FADD.ASSET_ID = BKS.ASSET_ID
AND FADD.ASSET_TYPE = 'CAPITALIZED'
AND FDP.PERIOD_COUNTER >= (
SELECT MIN(FDP2.PERIOD_COUNTER)
FROM FA_ASSET_HISTORY FAH2,
FA_DEPRN_PERIODS FDP2
WHERE FAH2.ASSET_ID = FADD.ASSET_ID
AND FAH2.ASSET_TYPE = 'CAPITALIZED'
AND FDP2.FISCAL_YEAR <= :P_CURR_FY
AND FDP2.BOOK_TYPE_CODE = :P_BOOK_TYPE_CODE
AND FAH2.DATE_EFFECTIVE BETWEEN FDP2.PERIOD_OPEN_DATE
AND FDP2.PERIOD_CLOSE_DATE)
AND CT.CATEGORY_ID = FADD.ASSET_CATEGORY_ID
ORDER BY 1,6,7,16 |