VIEW: APPS.JL_ZZ_FA_ADJ_SUMMARY_V

Source

View Text - Preformatted

SELECT ad.asset_id, ad.asset_number, ad.tag_number, ad.asset_type, ad.current_units, ad.description, ad.asset_category_id, ad.parent_asset_id, bca.book_type_code, bca.distribution_source_book, dpa.period_counter, dpa.period_name, bka.cost, dsa.deprn_reserve, dsa.ytd_deprn, nvl(jl_zz_fa_calc_adjustment_pkg.calc_corp_book_cost(bca.distribution_source_book, dpa.period_counter, bka.asset_id, 'CC'),0) corp_cost, nvl(jl_zz_fa_calc_adjustment_pkg.calc_corp_deprn_reserve(bca.distribution_source_book, dpa.period_counter, bka.asset_id, 'CC'),0) corp_deprn_reserve, nvl(jl_zz_fa_calc_adjustment_pkg.calc_corp_ytd_deprn(bca.distribution_source_book, dpa.period_counter, bka.asset_id, 'CC'),0) corp_ytd_deprn FROM fa_deprn_summary dsa, fa_deprn_periods dpa, fa_additions ad, fa_books bka, fa_book_controls bca WHERE bca.book_class = 'TAX' AND bca.global_attribute1 = 'Y' AND dpa.book_type_code = bca.book_type_code AND dpa.period_counter <= bca.last_period_counter AND bka.book_type_code = dpa.book_type_code AND bka.date_effective <= nvl (dpa.period_close_date, sysdate) AND nvl (dpa.period_close_date, sysdate) <= nvl (bka.date_ineffective, sysdate) AND ad.asset_id = bka.asset_id AND dsa.asset_id = ad.asset_id AND dsa.book_type_code = dpa.book_type_code AND dsa.period_counter = dpa.period_counter UNION SELECT ad.asset_id, ad.asset_number, ad.tag_number, ad.asset_type, ad.current_units, ad.description, ad.asset_category_id, ad.parent_asset_id, bca.book_type_code, bca.distribution_source_book, dpa.period_counter, dpa.period_name, bka.cost, dsa.deprn_reserve - jl_zz_fa_calc_adjustment_pkg.calc_adjustments (bca.book_type_code, dpa.period_counter, ad.asset_id, 'RESERVE', 'NET' ), dsa.ytd_deprn + jl_zz_fa_calc_adjustment_pkg.calc_adjustments (bca.book_type_code, dpa.period_counter, ad.asset_id, 'EXPENSE', 'NET' ), nvl(jl_zz_fa_calc_adjustment_pkg.calc_corp_book_cost(bca.distribution_source_book, dpa.period_counter, bka.asset_id, 'CO'),0), nvl(jl_zz_fa_calc_adjustment_pkg.calc_corp_deprn_reserve(bca.distribution_source_book, dpa.period_counter, bka.asset_id, 'CO'),0), nvl(jl_zz_fa_calc_adjustment_pkg.calc_corp_ytd_deprn(bca.distribution_source_book, dpa.period_counter, bka.asset_id, 'CO'),0) FROM fa_deprn_summary dsa, fa_deprn_periods dpa, fa_additions ad, fa_books bka, fa_book_controls bca WHERE bca.book_class = 'TAX' AND bca.global_attribute1 = 'Y' AND dpa.book_type_code = bca.book_type_code AND dpa.period_counter = bca.last_period_counter + 1 AND bka.book_type_code = dpa.book_type_code AND bka.date_effective <= nvl (dpa.period_close_date, sysdate) AND nvl (dpa.period_close_date, sysdate) <= nvl (bka.date_ineffective, sysdate) AND ad.asset_id = bka.asset_id AND dsa.asset_id = ad.asset_id AND dsa.book_type_code = dpa.book_type_code AND dsa.period_counter = bca.last_period_counter UNION SELECT ad.asset_id, ad.asset_number, ad.tag_number, ad.asset_type, ad.current_units, ad.description, ad.asset_category_id, ad.parent_asset_id, bca.book_type_code, bca.distribution_source_book, dpa.period_counter, dpa.period_name, bka.cost, dsa.deprn_reserve - jl_zz_fa_calc_adjustment_pkg.calc_adjustments (bca.book_type_code, dpa.period_counter, ad.asset_id, 'RESERVE', 'NET' ), dsa.ytd_deprn + jl_zz_fa_calc_adjustment_pkg.calc_adjustments (bca.book_type_code, dpa.period_counter, ad.asset_id, 'EXPENSE', 'NET' ), nvl(jl_zz_fa_calc_adjustment_pkg.calc_corp_book_cost(bca.distribution_source_book, dpa.period_counter, bka.asset_id, 'CO'),0), nvl(jl_zz_fa_calc_adjustment_pkg.calc_corp_deprn_reserve(bca.distribution_source_book, dpa.period_counter, bka.asset_id, 'CO'),0), nvl(jl_zz_fa_calc_adjustment_pkg.calc_corp_ytd_deprn(bca.distribution_source_book, dpa.period_counter, bka.asset_id, 'CO'),0) FROM fa_deprn_summary dsa, fa_deprn_periods dpa, fa_additions ad, fa_books bka, fa_book_controls bca WHERE bca.book_class = 'TAX' AND bca.global_attribute1 = 'Y' AND dpa.book_type_code = bca.book_type_code AND dpa.period_counter = bca.last_period_counter + 1 AND bka.book_type_code = dpa.book_type_code AND bka.global_attribute1 = 'Y' AND bka.date_effective <= nvl (dpa.period_close_date, sysdate) AND nvl (dpa.period_close_date, sysdate) <= nvl (bka.date_ineffective, sysdate) AND ad.asset_id = bka.asset_id AND dsa.asset_id = ad.asset_id AND dsa.book_type_code = dpa.book_type_code AND dsa.period_counter = bca.last_period_counter

View Text - HTML Formatted

SELECT AD.ASSET_ID
, AD.ASSET_NUMBER
, AD.TAG_NUMBER
, AD.ASSET_TYPE
, AD.CURRENT_UNITS
, AD.DESCRIPTION
, AD.ASSET_CATEGORY_ID
, AD.PARENT_ASSET_ID
, BCA.BOOK_TYPE_CODE
, BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, DPA.PERIOD_NAME
, BKA.COST
, DSA.DEPRN_RESERVE
, DSA.YTD_DEPRN
, NVL(JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_CORP_BOOK_COST(BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, BKA.ASSET_ID
, 'CC')
, 0) CORP_COST
, NVL(JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_CORP_DEPRN_RESERVE(BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, BKA.ASSET_ID
, 'CC')
, 0) CORP_DEPRN_RESERVE
, NVL(JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_CORP_YTD_DEPRN(BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, BKA.ASSET_ID
, 'CC')
, 0) CORP_YTD_DEPRN
FROM FA_DEPRN_SUMMARY DSA
, FA_DEPRN_PERIODS DPA
, FA_ADDITIONS AD
, FA_BOOKS BKA
, FA_BOOK_CONTROLS BCA
WHERE BCA.BOOK_CLASS = 'TAX'
AND BCA.GLOBAL_ATTRIBUTE1 = 'Y'
AND DPA.BOOK_TYPE_CODE = BCA.BOOK_TYPE_CODE
AND DPA.PERIOD_COUNTER <= BCA.LAST_PERIOD_COUNTER
AND BKA.BOOK_TYPE_CODE = DPA.BOOK_TYPE_CODE
AND BKA.DATE_EFFECTIVE <= NVL (DPA.PERIOD_CLOSE_DATE
, SYSDATE)
AND NVL (DPA.PERIOD_CLOSE_DATE
, SYSDATE) <= NVL (BKA.DATE_INEFFECTIVE
, SYSDATE)
AND AD.ASSET_ID = BKA.ASSET_ID
AND DSA.ASSET_ID = AD.ASSET_ID
AND DSA.BOOK_TYPE_CODE = DPA.BOOK_TYPE_CODE
AND DSA.PERIOD_COUNTER = DPA.PERIOD_COUNTER UNION SELECT AD.ASSET_ID
, AD.ASSET_NUMBER
, AD.TAG_NUMBER
, AD.ASSET_TYPE
, AD.CURRENT_UNITS
, AD.DESCRIPTION
, AD.ASSET_CATEGORY_ID
, AD.PARENT_ASSET_ID
, BCA.BOOK_TYPE_CODE
, BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, DPA.PERIOD_NAME
, BKA.COST
, DSA.DEPRN_RESERVE - JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_ADJUSTMENTS (BCA.BOOK_TYPE_CODE
, DPA.PERIOD_COUNTER
, AD.ASSET_ID
, 'RESERVE'
, 'NET' )
, DSA.YTD_DEPRN + JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_ADJUSTMENTS (BCA.BOOK_TYPE_CODE
, DPA.PERIOD_COUNTER
, AD.ASSET_ID
, 'EXPENSE'
, 'NET' )
, NVL(JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_CORP_BOOK_COST(BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, BKA.ASSET_ID
, 'CO')
, 0)
, NVL(JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_CORP_DEPRN_RESERVE(BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, BKA.ASSET_ID
, 'CO')
, 0)
, NVL(JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_CORP_YTD_DEPRN(BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, BKA.ASSET_ID
, 'CO')
, 0)
FROM FA_DEPRN_SUMMARY DSA
, FA_DEPRN_PERIODS DPA
, FA_ADDITIONS AD
, FA_BOOKS BKA
, FA_BOOK_CONTROLS BCA
WHERE BCA.BOOK_CLASS = 'TAX'
AND BCA.GLOBAL_ATTRIBUTE1 = 'Y'
AND DPA.BOOK_TYPE_CODE = BCA.BOOK_TYPE_CODE
AND DPA.PERIOD_COUNTER = BCA.LAST_PERIOD_COUNTER + 1
AND BKA.BOOK_TYPE_CODE = DPA.BOOK_TYPE_CODE
AND BKA.DATE_EFFECTIVE <= NVL (DPA.PERIOD_CLOSE_DATE
, SYSDATE)
AND NVL (DPA.PERIOD_CLOSE_DATE
, SYSDATE) <= NVL (BKA.DATE_INEFFECTIVE
, SYSDATE)
AND AD.ASSET_ID = BKA.ASSET_ID
AND DSA.ASSET_ID = AD.ASSET_ID
AND DSA.BOOK_TYPE_CODE = DPA.BOOK_TYPE_CODE
AND DSA.PERIOD_COUNTER = BCA.LAST_PERIOD_COUNTER UNION SELECT AD.ASSET_ID
, AD.ASSET_NUMBER
, AD.TAG_NUMBER
, AD.ASSET_TYPE
, AD.CURRENT_UNITS
, AD.DESCRIPTION
, AD.ASSET_CATEGORY_ID
, AD.PARENT_ASSET_ID
, BCA.BOOK_TYPE_CODE
, BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, DPA.PERIOD_NAME
, BKA.COST
, DSA.DEPRN_RESERVE - JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_ADJUSTMENTS (BCA.BOOK_TYPE_CODE
, DPA.PERIOD_COUNTER
, AD.ASSET_ID
, 'RESERVE'
, 'NET' )
, DSA.YTD_DEPRN + JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_ADJUSTMENTS (BCA.BOOK_TYPE_CODE
, DPA.PERIOD_COUNTER
, AD.ASSET_ID
, 'EXPENSE'
, 'NET' )
, NVL(JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_CORP_BOOK_COST(BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, BKA.ASSET_ID
, 'CO')
, 0)
, NVL(JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_CORP_DEPRN_RESERVE(BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, BKA.ASSET_ID
, 'CO')
, 0)
, NVL(JL_ZZ_FA_CALC_ADJUSTMENT_PKG.CALC_CORP_YTD_DEPRN(BCA.DISTRIBUTION_SOURCE_BOOK
, DPA.PERIOD_COUNTER
, BKA.ASSET_ID
, 'CO')
, 0)
FROM FA_DEPRN_SUMMARY DSA
, FA_DEPRN_PERIODS DPA
, FA_ADDITIONS AD
, FA_BOOKS BKA
, FA_BOOK_CONTROLS BCA
WHERE BCA.BOOK_CLASS = 'TAX'
AND BCA.GLOBAL_ATTRIBUTE1 = 'Y'
AND DPA.BOOK_TYPE_CODE = BCA.BOOK_TYPE_CODE
AND DPA.PERIOD_COUNTER = BCA.LAST_PERIOD_COUNTER + 1
AND BKA.BOOK_TYPE_CODE = DPA.BOOK_TYPE_CODE
AND BKA.GLOBAL_ATTRIBUTE1 = 'Y'
AND BKA.DATE_EFFECTIVE <= NVL (DPA.PERIOD_CLOSE_DATE
, SYSDATE)
AND NVL (DPA.PERIOD_CLOSE_DATE
, SYSDATE) <= NVL (BKA.DATE_INEFFECTIVE
, SYSDATE)
AND AD.ASSET_ID = BKA.ASSET_ID
AND DSA.ASSET_ID = AD.ASSET_ID
AND DSA.BOOK_TYPE_CODE = DPA.BOOK_TYPE_CODE
AND DSA.PERIOD_COUNTER = BCA.LAST_PERIOD_COUNTER