VIEW: APPS.IGI_MHC_CURR_REVAL_DTL_V

Source

View Text - Preformatted

SELECT MRS1.DETAIL_SEQ_ID, MRS1.MASS_REVAL_ID, MRS1.ASSET_ID , MRS1.CATEGORY_ID, MRS1.BOOK_TYPE_CODE , MRS1.DISTRIBUTION_ID, MRS1.CURRENT_MHC_PERIOD_COUNTER, MRS1.PERIOD_COUNTER, MRS1.NUMBER_OF_UNITS_ASSIGNED, MRS1.NUMBER_OF_UNITS_RETIRED, MRS1.LIFE_CHANGE_COUNT, MRS1.RUN_MODE , MRS1.ACTIVE_FLAG, MRS1.ASSET_COST, MRS1.SALVAGE_VALUE , MRS1.ACCUMULATED_DEPRECIATION , MRS1.PRIOR_YRS_ACCUM_DEPRECIATION , MRS1.YTD_DEPRECIATION_RESERVE , MRS1.CURR_MHC_PRD_BACKLOG_DEPRN, MRS1.ACCUMULATED_BACKLOG_DEPRN, MRS1.NET_BOOK_VALUE , MRS1.REVALUATION_RESERVE , MRS1.DEPRECIATION_EXPENSE , MRS1.CURR_YEAR_OPERATING_EXPENSE , MRS1.CURR_YR_PRIOR_PRDS_DEPRN , MRS1.CURR_YR_PRIOR_PRDS_DEPRN_REVAL , MRS1.PRIOR_YEAR_OPERATING_EXPENSE , MRS1.GENERAL_FUND , MRS1.CREATION_DATE , MRS1.ACCUMULATED_GENERAL_FUND , MRS1.CREATED_BY , MRS1.LAST_UPDATE_DATE, MRS1.LAST_UPDATE_BY , MRS1.LAST_UPDATE_LOGIN , MRS1.REVAL_MODE, MRS1.PROCESS_STATUS_CODE, MRS1.DEPRN_PROCESSED_FLAG FROM IGI_MHC_REVALUATION_DETAIL MRS1 WHERE MRS1.ACTIVE_FLAG = 'Y' AND MRS1.RUN_MODE = 'L' AND NVL(MRS1.ACTIVE_FLAG,'Y') <> 'N' AND (MRS1.BOOK_TYPE_CODE,MRS1.ASSET_ID,MRS1.CURRENT_MHC_PERIOD_COUNTER,mrs1.ROWID ) IN (SELECT MRS2.BOOK_TYPE_CODE, MRS2.ASSET_ID, MRS2.CURRENT_MHC_PERIOD_COUNTER, MAX(MRS2.ROWID) FROM IGI_MHC_REVALUATION_DETAIL MRS2 WHERE MRS2.BOOK_TYPE_CODE=MRS1.BOOK_TYPE_CODE AND MRS2.ASSET_ID=MRS1.ASSET_ID AND MRS2.CURRENT_MHC_PERIOD_COUNTER = MRS1.CURRENT_MHC_PERIOD_COUNTER AND MRS2.MASS_REVAL_ID IN ( SELECT MAX(MRS3.MASS_REVAL_ID) FROM IGI_MHC_REVALUATION_DETAIL MRS3 WHERE MRS3.BOOK_TYPE_CODE=MRS2.BOOK_TYPE_CODE AND MRS3.ASSET_ID=MRS2.ASSET_ID AND MRS3.CURRENT_MHC_PERIOD_COUNTER = MRS2.CURRENT_MHC_PERIOD_COUNTER) GROUP BY MRS2.BOOK_TYPE_CODE,MRS2.ASSET_ID,MRS2.CURRENT_MHC_PERIOD_COUNTER)

View Text - HTML Formatted

SELECT MRS1.DETAIL_SEQ_ID
, MRS1.MASS_REVAL_ID
, MRS1.ASSET_ID
, MRS1.CATEGORY_ID
, MRS1.BOOK_TYPE_CODE
, MRS1.DISTRIBUTION_ID
, MRS1.CURRENT_MHC_PERIOD_COUNTER
, MRS1.PERIOD_COUNTER
, MRS1.NUMBER_OF_UNITS_ASSIGNED
, MRS1.NUMBER_OF_UNITS_RETIRED
, MRS1.LIFE_CHANGE_COUNT
, MRS1.RUN_MODE
, MRS1.ACTIVE_FLAG
, MRS1.ASSET_COST
, MRS1.SALVAGE_VALUE
, MRS1.ACCUMULATED_DEPRECIATION
, MRS1.PRIOR_YRS_ACCUM_DEPRECIATION
, MRS1.YTD_DEPRECIATION_RESERVE
, MRS1.CURR_MHC_PRD_BACKLOG_DEPRN
, MRS1.ACCUMULATED_BACKLOG_DEPRN
, MRS1.NET_BOOK_VALUE
, MRS1.REVALUATION_RESERVE
, MRS1.DEPRECIATION_EXPENSE
, MRS1.CURR_YEAR_OPERATING_EXPENSE
, MRS1.CURR_YR_PRIOR_PRDS_DEPRN
, MRS1.CURR_YR_PRIOR_PRDS_DEPRN_REVAL
, MRS1.PRIOR_YEAR_OPERATING_EXPENSE
, MRS1.GENERAL_FUND
, MRS1.CREATION_DATE
, MRS1.ACCUMULATED_GENERAL_FUND
, MRS1.CREATED_BY
, MRS1.LAST_UPDATE_DATE
, MRS1.LAST_UPDATE_BY
, MRS1.LAST_UPDATE_LOGIN
, MRS1.REVAL_MODE
, MRS1.PROCESS_STATUS_CODE
, MRS1.DEPRN_PROCESSED_FLAG
FROM IGI_MHC_REVALUATION_DETAIL MRS1
WHERE MRS1.ACTIVE_FLAG = 'Y'
AND MRS1.RUN_MODE = 'L'
AND NVL(MRS1.ACTIVE_FLAG
, 'Y') <> 'N'
AND (MRS1.BOOK_TYPE_CODE
, MRS1.ASSET_ID
, MRS1.CURRENT_MHC_PERIOD_COUNTER
, MRS1.ROWID ) IN (SELECT MRS2.BOOK_TYPE_CODE
, MRS2.ASSET_ID
, MRS2.CURRENT_MHC_PERIOD_COUNTER
, MAX(MRS2.ROWID)
FROM IGI_MHC_REVALUATION_DETAIL MRS2
WHERE MRS2.BOOK_TYPE_CODE=MRS1.BOOK_TYPE_CODE
AND MRS2.ASSET_ID=MRS1.ASSET_ID
AND MRS2.CURRENT_MHC_PERIOD_COUNTER = MRS1.CURRENT_MHC_PERIOD_COUNTER
AND MRS2.MASS_REVAL_ID IN ( SELECT MAX(MRS3.MASS_REVAL_ID)
FROM IGI_MHC_REVALUATION_DETAIL MRS3
WHERE MRS3.BOOK_TYPE_CODE=MRS2.BOOK_TYPE_CODE
AND MRS3.ASSET_ID=MRS2.ASSET_ID
AND MRS3.CURRENT_MHC_PERIOD_COUNTER = MRS2.CURRENT_MHC_PERIOD_COUNTER) GROUP BY MRS2.BOOK_TYPE_CODE
, MRS2.ASSET_ID
, MRS2.CURRENT_MHC_PERIOD_COUNTER)