VIEW: APPS.FA_EXTENDED_DEPRN_V

Source

View Text - Preformatted

SELECT ad.asset_number, adt.description, ad.asset_type, ad.attribute_category_code, fab.book_type_code, fab.date_placed_in_service, fab.deprn_method_code, fab.life_in_months, fab.cost, fab.original_cost, fab.salvage_value, fab.prorate_convention_code, fab.depreciate_flag, fab.itc_amount_id, fab.basic_rate, fab.adjusted_rate, fab.bonus_rule, fab.ceiling_name, fab.fully_rsvd_revals_counter, fab.period_counter_fully_reserved, fab.production_capacity, fab.reval_amortization_basis, fab.reval_ceiling, fab.unrevalued_cost, fab.allowed_deprn_limit, fab.allowed_deprn_limit_amount, fab.short_fiscal_year_flag, fab.conversion_date, fab.original_deprn_start_date, fab.group_asset_id, fab.deprn_limit_type, fab.extended_deprn_flag, fab.extended_depreciation_period, fath.transaction_name, fath.amortization_start_date, fads.deprn_reserve, fads.ytd_deprn, fads.reval_reserve, fads.ytd_reval_deprn_expense, fadp.period_name, fads.impairment_reserve FROM fa_additions_b ad, fa_additions_tl adt, fa_books fab, fa_transaction_headers fath, fa_deprn_summary fads, fa_deprn_periods fadp WHERE ad.asset_id = adt.asset_id AND ad.asset_id = fab.asset_id AND ad.asset_id = fath.asset_id AND ad.asset_id = fads.asset_id AND fab.book_type_code = fath.book_type_code AND fab.book_type_code = fadp.book_type_code AND fab.book_type_code = fads.book_type_code AND fab.transaction_header_id_in = fath.transaction_header_id AND fab.period_counter_fully_reserved IS NOT NULL AND fab.period_counter_fully_retired IS NULL AND fab.period_counter_fully_extended IS NULL AND fab.transaction_header_id_out IS NULL AND fab.depreciate_flag = 'YES' AND fadp.period_close_date IS NULL AND fads.period_counter = (SELECT MAX(fads2.period_counter) FROM fa_deprn_summary fads2, fa_deprn_periods fadp2 WHERE fads2.book_type_code = fads.book_type_code AND fads2.asset_id = fads.asset_id AND fadp2.book_type_code = fads2.book_type_code AND fads2.period_counter = fadp2.period_counter AND fadp2.period_close_date is not null) AND fab.cost > (fads.impairment_reserve + fads.deprn_reserve + (SELECT NVL(SUM(DECODE(faadj.adjustment_type, 'EXPENSE', DECODE (faadj.debit_credit_flag, 'DR', faadj.adjustment_amount, -1 * faadj.adjustment_amount), 'RESERVE', DECODE (faadj.debit_credit_flag, 'CR', faadj.adjustment_amount, -1 * faadj.adjustment_amount))),0) FROM fa_adjustments faadj WHERE faadj.period_counter_created = fadp.period_counter AND faadj.asset_id = fads.asset_id AND faadj.book_type_code = fads.book_type_code)) AND EXISTS ( Select '1' from fa_books fabk where fabk.asset_id = fab.asset_id and fabk.book_type_code = fab.book_type_code and fabk.deprn_method_code <> 'JP-STL-EXTND') AND adt.LANGUAGE = userenv('LANG')

View Text - HTML Formatted

SELECT AD.ASSET_NUMBER
, ADT.DESCRIPTION
, AD.ASSET_TYPE
, AD.ATTRIBUTE_CATEGORY_CODE
, FAB.BOOK_TYPE_CODE
, FAB.DATE_PLACED_IN_SERVICE
, FAB.DEPRN_METHOD_CODE
, FAB.LIFE_IN_MONTHS
, FAB.COST
, FAB.ORIGINAL_COST
, FAB.SALVAGE_VALUE
, FAB.PRORATE_CONVENTION_CODE
, FAB.DEPRECIATE_FLAG
, FAB.ITC_AMOUNT_ID
, FAB.BASIC_RATE
, FAB.ADJUSTED_RATE
, FAB.BONUS_RULE
, FAB.CEILING_NAME
, FAB.FULLY_RSVD_REVALS_COUNTER
, FAB.PERIOD_COUNTER_FULLY_RESERVED
, FAB.PRODUCTION_CAPACITY
, FAB.REVAL_AMORTIZATION_BASIS
, FAB.REVAL_CEILING
, FAB.UNREVALUED_COST
, FAB.ALLOWED_DEPRN_LIMIT
, FAB.ALLOWED_DEPRN_LIMIT_AMOUNT
, FAB.SHORT_FISCAL_YEAR_FLAG
, FAB.CONVERSION_DATE
, FAB.ORIGINAL_DEPRN_START_DATE
, FAB.GROUP_ASSET_ID
, FAB.DEPRN_LIMIT_TYPE
, FAB.EXTENDED_DEPRN_FLAG
, FAB.EXTENDED_DEPRECIATION_PERIOD
, FATH.TRANSACTION_NAME
, FATH.AMORTIZATION_START_DATE
, FADS.DEPRN_RESERVE
, FADS.YTD_DEPRN
, FADS.REVAL_RESERVE
, FADS.YTD_REVAL_DEPRN_EXPENSE
, FADP.PERIOD_NAME
, FADS.IMPAIRMENT_RESERVE
FROM FA_ADDITIONS_B AD
, FA_ADDITIONS_TL ADT
, FA_BOOKS FAB
, FA_TRANSACTION_HEADERS FATH
, FA_DEPRN_SUMMARY FADS
, FA_DEPRN_PERIODS FADP
WHERE AD.ASSET_ID = ADT.ASSET_ID
AND AD.ASSET_ID = FAB.ASSET_ID
AND AD.ASSET_ID = FATH.ASSET_ID
AND AD.ASSET_ID = FADS.ASSET_ID
AND FAB.BOOK_TYPE_CODE = FATH.BOOK_TYPE_CODE
AND FAB.BOOK_TYPE_CODE = FADP.BOOK_TYPE_CODE
AND FAB.BOOK_TYPE_CODE = FADS.BOOK_TYPE_CODE
AND FAB.TRANSACTION_HEADER_ID_IN = FATH.TRANSACTION_HEADER_ID
AND FAB.PERIOD_COUNTER_FULLY_RESERVED IS NOT NULL
AND FAB.PERIOD_COUNTER_FULLY_RETIRED IS NULL
AND FAB.PERIOD_COUNTER_FULLY_EXTENDED IS NULL
AND FAB.TRANSACTION_HEADER_ID_OUT IS NULL
AND FAB.DEPRECIATE_FLAG = 'YES'
AND FADP.PERIOD_CLOSE_DATE IS NULL
AND FADS.PERIOD_COUNTER = (SELECT MAX(FADS2.PERIOD_COUNTER)
FROM FA_DEPRN_SUMMARY FADS2
, FA_DEPRN_PERIODS FADP2
WHERE FADS2.BOOK_TYPE_CODE = FADS.BOOK_TYPE_CODE
AND FADS2.ASSET_ID = FADS.ASSET_ID
AND FADP2.BOOK_TYPE_CODE = FADS2.BOOK_TYPE_CODE
AND FADS2.PERIOD_COUNTER = FADP2.PERIOD_COUNTER
AND FADP2.PERIOD_CLOSE_DATE IS NOT NULL)
AND FAB.COST > (FADS.IMPAIRMENT_RESERVE + FADS.DEPRN_RESERVE + (SELECT NVL(SUM(DECODE(FAADJ.ADJUSTMENT_TYPE
, 'EXPENSE'
, DECODE (FAADJ.DEBIT_CREDIT_FLAG
, 'DR'
, FAADJ.ADJUSTMENT_AMOUNT
, -1 * FAADJ.ADJUSTMENT_AMOUNT)
, 'RESERVE'
, DECODE (FAADJ.DEBIT_CREDIT_FLAG
, 'CR'
, FAADJ.ADJUSTMENT_AMOUNT
, -1 * FAADJ.ADJUSTMENT_AMOUNT)))
, 0)
FROM FA_ADJUSTMENTS FAADJ
WHERE FAADJ.PERIOD_COUNTER_CREATED = FADP.PERIOD_COUNTER
AND FAADJ.ASSET_ID = FADS.ASSET_ID
AND FAADJ.BOOK_TYPE_CODE = FADS.BOOK_TYPE_CODE))
AND EXISTS ( SELECT '1'
FROM FA_BOOKS FABK
WHERE FABK.ASSET_ID = FAB.ASSET_ID
AND FABK.BOOK_TYPE_CODE = FAB.BOOK_TYPE_CODE
AND FABK.DEPRN_METHOD_CODE <> 'JP-STL-EXTND')
AND ADT.LANGUAGE = USERENV('LANG')