View: FA_SORP_ASSET_LINK_REVAL_V

Product:	OFA - Assets
Description:	This view is used to retrieve impairments and related information selected by user for linking while performing revaluation at category level. In addition it retrieves the eligible impairment amount and avialble revaluation reserve amount a
Implementation/DBA Data:	APPS.FA_SORP_ASSET_LINK_REVAL_V

View Text

SELECT REVLK.MASS_REVAL_ID
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, REVLK.IMP_INCLUDE_FLAG
, IMP.IMPAIRMENT_ID
, REVLK.SPLIT_NUMBER
, PER.PERIOD_NAME
, IMP.IMPAIRMENT_DATE
, IMP.IMPAIR_CLASS
, IMP.REASON
, DECODE (IMP.IMPAIR_CLASS
, 'CEB'
, ITF.IMPAIRMENT_AMOUNT
, (ITF.IMPAIRMENT_AMOUNT + NVL(ITF.REVAL_RESERVE_ADJ_AMOUNT
, 0)))
, DECODE (IMP.IMPAIR_CLASS
, 'CEB'
, NVL (ITF.IMPAIRMENT_AMOUNT - (NVL (ITF.REVERSED_IMP_AMT
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT
, 0) + NVL (ITF.REVERSED_REVAL_AMT
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT
, 0))
, ITF.IMPAIRMENT_AMOUNT)
, NVL ((ITF.IMPAIRMENT_AMOUNT + NVL (ITF.REVAL_RESERVE_ADJ_AMOUNT
, 0) )-( NVL (ITF.REVERSED_IMP_AMT
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT
, 0) + NVL (ITF.REVERSED_REVAL_AMT
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT
, 0))
, (ITF.IMPAIRMENT_AMOUNT + NVL (ITF.REVAL_RESERVE_ADJ_AMOUNT
, 0))))
, (ITF.IMPAIRMENT_AMOUNT - ( NVL(ITF.REVERSED_IMP_AMT
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT
, 0))) CALC_IMP_LOSS_AMOUNT
, NVL(ITF.REVAL_RESERVE_ADJ_AMOUNT - (NVL (ITF.REVERSED_REVAL_AMT
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT
, 0))
, 0) REVAL_RSV_ADJ_AMOUNT
, IMP.IMPAIR_LOSS_ACCT
, DECODE(ITF.REVAL_RESERVE_ADJ_AMOUNT
, 0
, 'N'
, 'Y')
, ITF.SPLIT_IMPAIR_FLAG
, REVLK.CREATION_DATE
, REVLK.CREATED_BY
, REVLK.LAST_UPDATE_DATE
, REVLK.LAST_UPDATED_BY
, REVLK.LAST_UPDATE_LOGIN
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_ASSET REVLK
WHERE IMP.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
AND IMP.BOOK_TYPE_CODE = ITF.BOOK_TYPE_CODE
AND IMP.ASSET_ID = ITF.ASSET_ID
AND IMP.BOOK_TYPE_CODE = PER.BOOK_TYPE_CODE
AND ITF.PERIOD_COUNTER = PER.PERIOD_COUNTER
AND REVLK.ASSET_ID = ITF.ASSET_ID
AND REVLK.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') != 'Y' UNION SELECT REVLK.MASS_REVAL_ID
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, REVLK.IMP_INCLUDE_FLAG
, TO_NUMBER(IMP.IMPAIRMENT_ID||'.1') IMPAIRMENT_ID
, REVLK.SPLIT_NUMBER
, PER.PERIOD_NAME
, IMP.IMPAIRMENT_DATE
, IMP.SPLIT1_IMPAIR_CLASS IMPAIR_CLASS
, IMP.SPLIT1_REASON REASON
, DECODE (IMP.SPLIT1_IMPAIR_CLASS
, 'CEB'
, ITF.SPLIT1_LOSS_AMOUNT
, (ITF.SPLIT1_LOSS_AMOUNT + NVL (ITF.SPLIT1_REVAL_RESERVE
, 0)))
, DECODE (IMP.SPLIT1_IMPAIR_CLASS
, 'CEB'
, NVL(ITF.SPLIT1_LOSS_AMOUNT - ( NVL (ITF.REVERSED_IMP_AMT_S1
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT_S1
, 0) + NVL (ITF.REVERSED_REVAL_AMT_S1
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT_S1
, 0))
, ITF.SPLIT1_LOSS_AMOUNT)
, NVL ((ITF.SPLIT1_LOSS_AMOUNT + NVL (ITF.SPLIT1_REVAL_RESERVE
, 0)) - ( NVL (ITF.REVERSED_IMP_AMT_S1
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT_S1
, 0) + NVL (ITF.REVERSED_REVAL_AMT_S1
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT_S1
, 0))
, (ITF.SPLIT1_LOSS_AMOUNT + NVL (ITF.SPLIT1_REVAL_RESERVE
, 0))))
, (ITF.SPLIT1_LOSS_AMOUNT - (NVL (ITF.REVERSED_IMP_AMT_S1
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT_S1
, 0))) CALC_IMP_LOSS_AMOUNT
, NVL(ITF.SPLIT1_REVAL_RESERVE - (NVL (ITF.REVERSED_REVAL_AMT_S1
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT_S1
, 0))
, 0) REVAL_RSV_ADJ_AMOUNT
, ITF.SPLIT1_LOSS_ACCT
, DECODE(ITF.SPLIT1_REVAL_RESERVE
, 0
, 'N'
, 'Y')
, ITF.SPLIT_IMPAIR_FLAG
, REVLK.CREATION_DATE
, REVLK.CREATED_BY
, REVLK.LAST_UPDATE_DATE
, REVLK.LAST_UPDATED_BY
, REVLK.LAST_UPDATE_LOGIN
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_ASSET REVLK
WHERE IMP.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
AND IMP.BOOK_TYPE_CODE = ITF.BOOK_TYPE_CODE
AND IMP.ASSET_ID = ITF.ASSET_ID
AND IMP.BOOK_TYPE_CODE = PER.BOOK_TYPE_CODE
AND ITF.PERIOD_COUNTER = PER.PERIOD_COUNTER
AND REVLK.ASSET_ID = ITF.ASSET_ID
AND REVLK.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') = 'Y'
AND ITF.SPLIT1_IMPAIR_CLASS IS NOT NULL
AND REVLK.SPLIT_NUMBER = 1 UNION SELECT REVLK.MASS_REVAL_ID
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, REVLK.IMP_INCLUDE_FLAG
, TO_NUMBER(IMP.IMPAIRMENT_ID||'.2') IMPAIRMENT_ID
, REVLK.SPLIT_NUMBER
, PER.PERIOD_NAME
, IMP.IMPAIRMENT_DATE
, IMP.SPLIT2_IMPAIR_CLASS IMPAIR_CLASS
, IMP.SPLIT2_REASON REASON
, DECODE (IMP.SPLIT2_IMPAIR_CLASS
, 'CEB'
, ITF.SPLIT2_LOSS_AMOUNT
, (ITF.SPLIT2_LOSS_AMOUNT + NVL (ITF.SPLIT2_REVAL_RESERVE
, 0)))
, DECODE (IMP.SPLIT2_IMPAIR_CLASS
, 'CEB'
, NVL(ITF.SPLIT2_LOSS_AMOUNT - ( NVL (ITF.REVERSED_IMP_AMT_S2
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT_S2
, 0) + NVL (ITF.REVERSED_REVAL_AMT_S2
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT_S2
, 0))
, ITF.SPLIT2_LOSS_AMOUNT)
, NVL ((ITF.SPLIT2_LOSS_AMOUNT + NVL (ITF.SPLIT2_REVAL_RESERVE
, 0)) - ( NVL (ITF.REVERSED_IMP_AMT_S2
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT_S2
, 0) + NVL (ITF.REVERSED_REVAL_AMT_S2
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT_S2
, 0))
, (ITF.SPLIT2_LOSS_AMOUNT + NVL (ITF.SPLIT2_REVAL_RESERVE
, 0))))
, (ITF.SPLIT2_LOSS_AMOUNT - (NVL (ITF.REVERSED_IMP_AMT_S2
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT_S2
, 0))) CALC_IMP_LOSS_AMOUNT
, NVL(ITF.SPLIT2_REVAL_RESERVE - (NVL (ITF.REVERSED_REVAL_AMT_S2
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT_S2
, 0))
, 0) REVAL_RSV_ADJ_AMOUNT
, ITF.SPLIT2_LOSS_ACCT
, DECODE(ITF.SPLIT2_REVAL_RESERVE
, 0
, 'N'
, 'Y')
, ITF.SPLIT_IMPAIR_FLAG
, REVLK.CREATION_DATE
, REVLK.CREATED_BY
, REVLK.LAST_UPDATE_DATE
, REVLK.LAST_UPDATED_BY
, REVLK.LAST_UPDATE_LOGIN
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_ASSET REVLK
WHERE IMP.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
AND IMP.BOOK_TYPE_CODE = ITF.BOOK_TYPE_CODE
AND IMP.ASSET_ID = ITF.ASSET_ID
AND IMP.BOOK_TYPE_CODE = PER.BOOK_TYPE_CODE
AND ITF.PERIOD_COUNTER = PER.PERIOD_COUNTER
AND REVLK.ASSET_ID = ITF.ASSET_ID
AND REVLK.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') = 'Y'
AND ITF.SPLIT2_IMPAIR_CLASS IS NOT NULL
AND REVLK.SPLIT_NUMBER = 2 UNION SELECT REVLK.MASS_REVAL_ID
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, REVLK.IMP_INCLUDE_FLAG
, TO_NUMBER(IMP.IMPAIRMENT_ID||'.3') IMPAIRMENT_ID
, REVLK.SPLIT_NUMBER
, PER.PERIOD_NAME
, IMP.IMPAIRMENT_DATE
, IMP.SPLIT3_IMPAIR_CLASS IMPAIR_CLASS
, IMP.SPLIT3_REASON REASON
, DECODE (IMP.SPLIT3_IMPAIR_CLASS
, 'CEB'
, ITF.SPLIT3_LOSS_AMOUNT
, (ITF.SPLIT3_LOSS_AMOUNT + NVL (ITF.SPLIT3_REVAL_RESERVE
, 0)))
, DECODE (IMP.SPLIT3_IMPAIR_CLASS
, 'CEB'
, NVL(ITF.SPLIT3_LOSS_AMOUNT - ( NVL (ITF.REVERSED_IMP_AMT_S3
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT_S3
, 0) + NVL (ITF.REVERSED_REVAL_AMT_S3
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT_S3
, 0))
, ITF.SPLIT3_LOSS_AMOUNT)
, NVL ((ITF.SPLIT3_LOSS_AMOUNT + NVL (ITF.SPLIT3_REVAL_RESERVE
, 0)) - ( NVL (ITF.REVERSED_IMP_AMT_S3
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT_S3
, 0) + NVL (ITF.REVERSED_REVAL_AMT_S3
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT_S3
, 0))
, (ITF.SPLIT3_LOSS_AMOUNT + NVL (ITF.SPLIT3_REVAL_RESERVE
, 0))))
, (ITF.SPLIT3_LOSS_AMOUNT - (NVL (ITF.REVERSED_IMP_AMT_S3
, 0) + NVL (ITF.REVERSED_DEPRN_IMPACT_S3
, 0))) CALC_IMP_LOSS_AMOUNT
, NVL(ITF.SPLIT3_REVAL_RESERVE - (NVL (ITF.REVERSED_REVAL_AMT_S3
, 0) + NVL (ITF.REVERSED_REVAL_IMPACT_S3
, 0))
, 0) REVAL_RSV_ADJ_AMOUNT
, ITF.SPLIT3_LOSS_ACCT
, DECODE(ITF.SPLIT3_REVAL_RESERVE
, 0
, 'N'
, 'Y')
, ITF.SPLIT_IMPAIR_FLAG
, REVLK.CREATION_DATE
, REVLK.CREATED_BY
, REVLK.LAST_UPDATE_DATE
, REVLK.LAST_UPDATED_BY
, REVLK.LAST_UPDATE_LOGIN
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_ASSET REVLK
WHERE IMP.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
AND IMP.BOOK_TYPE_CODE = ITF.BOOK_TYPE_CODE
AND IMP.ASSET_ID = ITF.ASSET_ID
AND IMP.BOOK_TYPE_CODE = PER.BOOK_TYPE_CODE
AND ITF.PERIOD_COUNTER = PER.PERIOD_COUNTER
AND REVLK.ASSET_ID = ITF.ASSET_ID
AND REVLK.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') = 'Y'
AND ITF.SPLIT3_IMPAIR_CLASS IS NOT NULL
AND REVLK.SPLIT_NUMBER = 3

Columns