VIEW: APPS.IGI_MHC_REVAL_SUMMARY_PL_V

Source

View Text - Preformatted

SELECT MRS.ROWID "ROW_ID", MRS.MASS_REVAL_ID, MRS.ASSET_ID, MRS.CATEGORY_ID, MRS.BOOK_TYPE_CODE, MRS.CURRENT_MHC_PERIOD_COUNTER, MRS.PERIOD_COUNTER, MRS.DPIS_MHC_PERIOD_COUNTER, MRS.LIFE_CHANGE_COUNT, MRS.ACTIVE_FLAG, MRS.RUN_MODE, MRS.REVAL_MODE, MRS.ORIGINAL_COST, MRS.NEW_ASSET_COST,MRS.NEW_SALVAGE_VALUE, MRS.ORIGINAL_LIFE, MRS.PREVIOUS_LIFE, MRS.CURRENT_LIFE, MRS.OLD_ACCUM_DEPRN, MRS.OLD_REVAL_RESERVE, MRS.NEW_ACCUM_DEPRN, MRS.NEW_REVAL_RESERVE, MRS.NEW_CURR_YR_EXPENSE, MRS.NEW_BACKLOG_DEPRN, MRS.NEW_CURR_YR_OP_EXPENSE, MRS.NEW_PRIOR_YR_OP_EXPENSE, MRS.NEW_GENERAL_FUND, MRS.NEW_ACCUM_GENERAL_FUND, MRS.LAST_TXN_EVENT, MRS.CREATED_BY, MRS.CREATION_DATE, MRS.LAST_UPDATED_BY, MRS.LAST_UPDATE_DATE, MRS.LAST_UPDATE_LOGIN FROM IGI_MHC_REVALUATION_SUMMARY MRS WHERE RUN_MODE = 'L' AND NVL(ACTIVE_FLAG,'Y') <> 'N' AND( MASS_REVAL_ID, ASSET_ID, CURRENT_MHC_PERIOD_COUNTER, ROWID) = ( SELECT IMRS2. MASS_REVAL_ID, IMRS2.ASSET_ID, IMRS2.CURRENT_MHC_PERIOD_COUNTER,MAX(IMRS2.ROWID) FROM IGI_MHC_REVALUATION_SUMMARY IMRS2 WHERE IMRS2.ASSET_ID = MRS.ASSET_ID AND IMRS2.BOOK_TYPE_CODE = MRS.BOOK_TYPE_CODE AND IMRS2.RUN_MODE = MRS.RUN_MODE AND NVL(IMRS2.ACTIVE_FLAG,'Y') <> 'N' AND IMRS2.CURRENT_MHC_PERIOD_COUNTER = MRS.CURRENT_MHC_PERIOD_COUNTER AND IMRS2.MASS_REVAL_ID = ( SELECT MAX(IMRS3.MASS_REVAL_ID) FROM IGI_MHC_REVALUATION_SUMMARY IMRS3 WHERE /* IMRS3.MASS_REVAL_ID = IMRS2.MASS_REVAL_ID AND */ IMRS3.ASSET_ID = IMRS2.ASSET_ID AND IMRS3.BOOK_TYPE_CODE = IMRS2.BOOK_TYPE_CODE AND IMRS3.RUN_MODE = IMRS2.RUN_MODE AND IMRS3.CURRENT_MHC_PERIOD_COUNTER = IMRS2.CURRENT_MHC_PERIOD_COUNTER AND NVL(IMRS3.ACTIVE_FLAG,'Y') <> 'N' ) GROUP BY IMRS2.MASS_REVAL_ID, IMRS2.ASSET_ID, IMRS2.CURRENT_MHC_PERIOD_COUNTER )

View Text - HTML Formatted

SELECT MRS.ROWID "ROW_ID"
, MRS.MASS_REVAL_ID
, MRS.ASSET_ID
, MRS.CATEGORY_ID
, MRS.BOOK_TYPE_CODE
, MRS.CURRENT_MHC_PERIOD_COUNTER
, MRS.PERIOD_COUNTER
, MRS.DPIS_MHC_PERIOD_COUNTER
, MRS.LIFE_CHANGE_COUNT
, MRS.ACTIVE_FLAG
, MRS.RUN_MODE
, MRS.REVAL_MODE
, MRS.ORIGINAL_COST
, MRS.NEW_ASSET_COST
, MRS.NEW_SALVAGE_VALUE
, MRS.ORIGINAL_LIFE
, MRS.PREVIOUS_LIFE
, MRS.CURRENT_LIFE
, MRS.OLD_ACCUM_DEPRN
, MRS.OLD_REVAL_RESERVE
, MRS.NEW_ACCUM_DEPRN
, MRS.NEW_REVAL_RESERVE
, MRS.NEW_CURR_YR_EXPENSE
, MRS.NEW_BACKLOG_DEPRN
, MRS.NEW_CURR_YR_OP_EXPENSE
, MRS.NEW_PRIOR_YR_OP_EXPENSE
, MRS.NEW_GENERAL_FUND
, MRS.NEW_ACCUM_GENERAL_FUND
, MRS.LAST_TXN_EVENT
, MRS.CREATED_BY
, MRS.CREATION_DATE
, MRS.LAST_UPDATED_BY
, MRS.LAST_UPDATE_DATE
, MRS.LAST_UPDATE_LOGIN
FROM IGI_MHC_REVALUATION_SUMMARY MRS
WHERE RUN_MODE = 'L'
AND NVL(ACTIVE_FLAG
, 'Y') <> 'N' AND( MASS_REVAL_ID
, ASSET_ID
, CURRENT_MHC_PERIOD_COUNTER
, ROWID) = ( SELECT IMRS2. MASS_REVAL_ID
, IMRS2.ASSET_ID
, IMRS2.CURRENT_MHC_PERIOD_COUNTER
, MAX(IMRS2.ROWID)
FROM IGI_MHC_REVALUATION_SUMMARY IMRS2
WHERE IMRS2.ASSET_ID = MRS.ASSET_ID
AND IMRS2.BOOK_TYPE_CODE = MRS.BOOK_TYPE_CODE
AND IMRS2.RUN_MODE = MRS.RUN_MODE
AND NVL(IMRS2.ACTIVE_FLAG
, 'Y') <> 'N'
AND IMRS2.CURRENT_MHC_PERIOD_COUNTER = MRS.CURRENT_MHC_PERIOD_COUNTER
AND IMRS2.MASS_REVAL_ID = ( SELECT MAX(IMRS3.MASS_REVAL_ID)
FROM IGI_MHC_REVALUATION_SUMMARY IMRS3
WHERE /* IMRS3.MASS_REVAL_ID = IMRS2.MASS_REVAL_ID
AND */ IMRS3.ASSET_ID = IMRS2.ASSET_ID
AND IMRS3.BOOK_TYPE_CODE = IMRS2.BOOK_TYPE_CODE
AND IMRS3.RUN_MODE = IMRS2.RUN_MODE
AND IMRS3.CURRENT_MHC_PERIOD_COUNTER = IMRS2.CURRENT_MHC_PERIOD_COUNTER
AND NVL(IMRS3.ACTIVE_FLAG
, 'Y') <> 'N' ) GROUP BY IMRS2.MASS_REVAL_ID
, IMRS2.ASSET_ID
, IMRS2.CURRENT_MHC_PERIOD_COUNTER )