VIEW: APPS.FA_SORP_ASSET_LINK_REVAL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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