eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.FA_SORP_CAT_LINK_REVAL_V

Source

View Text - Preformatted


SELECT IMP.IMPAIRMENT_ID ,NULL SPLIT_NUMBER ,IMP.ASSET_ID ,IMP.BOOK_TYPE_CODE ,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) unused_imp_loss_amount ,CAT.MASS_REVAL_ID ,ADD_B.ASSET_CATEGORY_ID CATEGORY_ID ,IMP.IMPAIR_LOSS_ACCT ,DECODE (NVL (itf.reval_reserve_adj_amount, 0), 0, 'N', 'Y') ,imp.impairment_date, imp.impair_class, imp.reason ,(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 FROM FA_IMPAIRMENTS IMP, FA_ITF_IMPAIRMENTS ITF, FA_DEPRN_PERIODS PER, FA_REVAL_LINK_IMP_CAT CAT, FA_ADDITIONS_B ADD_B 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 ADD_B.ASSET_ID = IMP.ASSET_ID AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID AND IMP.IMPAIR_CLASS = 'CPP' AND IMP.STATUS = 'POSTED' AND CAT.CPP_ENABLED_FLAG = 'Y' AND NVL(ITF.SPLIT_IMPAIR_FLAG,'N') != 'Y' UNION SELECT IMP.IMPAIRMENT_ID , NULL SPLIT_NUMBER , IMP.ASSET_ID , IMP.BOOK_TYPE_CODE , 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) unused_imp_loss_amount , CAT.MASS_REVAL_ID , ADD_B.ASSET_CATEGORY_ID CATEGORY_ID , IMP.IMPAIR_LOSS_ACCT , DECODE (NVL (itf.reval_reserve_adj_amount, 0), 0, 'N', 'Y') , imp.impairment_date, imp.impair_class, imp.reason , (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 FROM FA_IMPAIRMENTS IMP, FA_ITF_IMPAIRMENTS ITF, FA_DEPRN_PERIODS PER, FA_REVAL_LINK_IMP_CAT CAT, FA_ADDITIONS_B ADD_B 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 ADD_B.ASSET_ID = IMP.ASSET_ID AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID AND IMP.IMPAIR_CLASS = 'OTH' AND IMP.STATUS = 'POSTED' AND CAT.OTH_ENABLED_FLAG = 'Y' AND NVL(ITF.SPLIT_IMPAIR_FLAG,'N') != 'Y' UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.1') IMPAIRMENT_ID, 1 SPLIT_NUMBER, IMP.ASSET_ID, IMP.BOOK_TYPE_CODE, 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) unused_imp_loss_amount, CAT.MASS_REVAL_ID, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID, ITF.SPLIT1_LOSS_ACCT, DECODE (NVL (itf.split1_reval_reserve, 0), 0, 'N', 'Y'), imp.impairment_date, imp.split1_impair_class, imp.split1_reason, (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 FROM FA_IMPAIRMENTS IMP, FA_ITF_IMPAIRMENTS ITF, FA_DEPRN_PERIODS PER, FA_REVAL_LINK_IMP_CAT CAT, FA_ADDITIONS_B ADD_B 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 ADD_B.ASSET_ID = IMP.ASSET_ID AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID AND IMP.SPLIT1_IMPAIR_CLASS = 'CPP' AND IMP.STATUS = 'POSTED' AND CAT.CPP_ENABLED_FLAG = 'Y' AND NVL(ITF.SPLIT_IMPAIR_FLAG,'N') = 'Y' AND ITF.SPLIT1_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.1') IMPAIRMENT_ID, 1 SPLIT_NUMBER, IMP.ASSET_ID, IMP.BOOK_TYPE_CODE, 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) unused_imp_loss_amount, CAT.MASS_REVAL_ID, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID, ITF.SPLIT1_LOSS_ACCT, DECODE (NVL (itf.split1_reval_reserve, 0), 0, 'N', 'Y'), imp.impairment_date, imp.split1_impair_class, imp.split1_reason, (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 FROM FA_IMPAIRMENTS IMP, FA_ITF_IMPAIRMENTS ITF, FA_DEPRN_PERIODS PER, FA_REVAL_LINK_IMP_CAT CAT, FA_ADDITIONS_B ADD_B 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 ADD_B.ASSET_ID = IMP.ASSET_ID AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID AND IMP.SPLIT1_IMPAIR_CLASS = 'OTH' AND IMP.STATUS = 'POSTED' AND CAT.OTH_ENABLED_FLAG = 'Y' AND NVL(ITF.SPLIT_IMPAIR_FLAG,'N') = 'Y' AND ITF.SPLIT1_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.2') IMPAIRMENT_ID, 2 SPLIT_NUMBER, IMP.ASSET_ID, IMP.BOOK_TYPE_CODE, 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) unused_imp_loss_amount, CAT.MASS_REVAL_ID, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID, ITF.SPLIT2_LOSS_ACCT, DECODE (NVL (itf.split2_reval_reserve, 0), 0, 'N', 'Y'), imp.impairment_date, imp.split2_impair_class, imp.split2_reason, (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 FROM FA_IMPAIRMENTS IMP, FA_ITF_IMPAIRMENTS ITF, FA_DEPRN_PERIODS PER, FA_REVAL_LINK_IMP_CAT CAT, FA_ADDITIONS_B ADD_B 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 ADD_B.ASSET_ID = IMP.ASSET_ID AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID AND IMP.SPLIT2_IMPAIR_CLASS = 'CPP' AND CAT.CPP_ENABLED_FLAG = 'Y' AND IMP.STATUS = 'POSTED' AND NVL(ITF.SPLIT_IMPAIR_FLAG,'N') = 'Y' AND ITF.SPLIT2_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.2') IMPAIRMENT_ID, 2 SPLIT_NUMBER, IMP.ASSET_ID, IMP.BOOK_TYPE_CODE, 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) unused_imp_loss_amount, CAT.MASS_REVAL_ID, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID, ITF.SPLIT2_LOSS_ACCT, DECODE (NVL (itf.split2_reval_reserve, 0), 0, 'N', 'Y'), imp.impairment_date, imp.split2_impair_class, imp.split2_reason, (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 FROM FA_IMPAIRMENTS IMP, FA_ITF_IMPAIRMENTS ITF, FA_DEPRN_PERIODS PER, FA_REVAL_LINK_IMP_CAT CAT, FA_ADDITIONS_B ADD_B 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 ADD_B.ASSET_ID = IMP.ASSET_ID AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID AND IMP.SPLIT2_IMPAIR_CLASS = 'OTH' AND IMP.STATUS = 'POSTED' AND CAT.OTH_ENABLED_FLAG = 'Y' AND NVL(ITF.SPLIT_IMPAIR_FLAG,'N') = 'Y' AND ITF.SPLIT2_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.3') IMPAIRMENT_ID, 3 SPLIT_NUMBER, IMP.ASSET_ID, IMP.BOOK_TYPE_CODE, 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) unused_imp_loss_amount, CAT.MASS_REVAL_ID, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID, ITF.SPLIT3_LOSS_ACCT, DECODE (NVL (itf.split3_reval_reserve, 0), 0, 'N', 'Y'), imp.impairment_date, imp.split3_impair_class, imp.split3_reason, (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 FROM FA_IMPAIRMENTS IMP, FA_ITF_IMPAIRMENTS ITF, FA_DEPRN_PERIODS PER, FA_REVAL_LINK_IMP_CAT CAT, FA_ADDITIONS_B ADD_B 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 ADD_B.ASSET_ID = IMP.ASSET_ID AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID AND IMP.SPLIT3_IMPAIR_CLASS = 'CPP' AND IMP.STATUS = 'POSTED' AND CAT.CPP_ENABLED_FLAG = 'Y' AND NVL(ITF.SPLIT_IMPAIR_FLAG,'N') = 'Y' AND ITF.SPLIT3_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.3') IMPAIRMENT_ID, 3 SPLIT_NUMBER, IMP.ASSET_ID, IMP.BOOK_TYPE_CODE, 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) unused_imp_loss_amount, CAT.MASS_REVAL_ID, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID, ITF.SPLIT3_LOSS_ACCT, DECODE (NVL (itf.split3_reval_reserve, 0), 0, 'N', 'Y'), imp.impairment_date, imp.split3_impair_class, imp.split3_reason, (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 FROM FA_IMPAIRMENTS IMP, FA_ITF_IMPAIRMENTS ITF, FA_DEPRN_PERIODS PER, FA_REVAL_LINK_IMP_CAT CAT, FA_ADDITIONS_B ADD_B 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 ADD_B.ASSET_ID = IMP.ASSET_ID AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID AND IMP.SPLIT3_IMPAIR_CLASS = 'OTH' AND IMP.STATUS = 'POSTED' AND CAT.OTH_ENABLED_FLAG = 'Y' AND NVL(ITF.SPLIT_IMPAIR_FLAG,'N') = 'Y' AND ITF.SPLIT3_IMPAIR_CLASS IS NOT NULL

View Text - HTML Formatted

SELECT IMP.IMPAIRMENT_ID
, NULL SPLIT_NUMBER
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, 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) UNUSED_IMP_LOSS_AMOUNT
, CAT.MASS_REVAL_ID
, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID
, IMP.IMPAIR_LOSS_ACCT
, DECODE (NVL (ITF.REVAL_RESERVE_ADJ_AMOUNT
, 0)
, 0
, 'N'
, 'Y')
, IMP.IMPAIRMENT_DATE
, IMP.IMPAIR_CLASS
, IMP.REASON
, (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
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_CAT CAT
, FA_ADDITIONS_B ADD_B
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 ADD_B.ASSET_ID = IMP.ASSET_ID
AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
AND IMP.IMPAIR_CLASS = 'CPP'
AND IMP.STATUS = 'POSTED'
AND CAT.CPP_ENABLED_FLAG = 'Y'
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') != 'Y' UNION SELECT IMP.IMPAIRMENT_ID
, NULL SPLIT_NUMBER
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, 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) UNUSED_IMP_LOSS_AMOUNT
, CAT.MASS_REVAL_ID
, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID
, IMP.IMPAIR_LOSS_ACCT
, DECODE (NVL (ITF.REVAL_RESERVE_ADJ_AMOUNT
, 0)
, 0
, 'N'
, 'Y')
, IMP.IMPAIRMENT_DATE
, IMP.IMPAIR_CLASS
, IMP.REASON
, (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
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_CAT CAT
, FA_ADDITIONS_B ADD_B
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 ADD_B.ASSET_ID = IMP.ASSET_ID
AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
AND IMP.IMPAIR_CLASS = 'OTH'
AND IMP.STATUS = 'POSTED'
AND CAT.OTH_ENABLED_FLAG = 'Y'
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') != 'Y' UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.1') IMPAIRMENT_ID
, 1 SPLIT_NUMBER
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, 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) UNUSED_IMP_LOSS_AMOUNT
, CAT.MASS_REVAL_ID
, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID
, ITF.SPLIT1_LOSS_ACCT
, DECODE (NVL (ITF.SPLIT1_REVAL_RESERVE
, 0)
, 0
, 'N'
, 'Y')
, IMP.IMPAIRMENT_DATE
, IMP.SPLIT1_IMPAIR_CLASS
, IMP.SPLIT1_REASON
, (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
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_CAT CAT
, FA_ADDITIONS_B ADD_B
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 ADD_B.ASSET_ID = IMP.ASSET_ID
AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
AND IMP.SPLIT1_IMPAIR_CLASS = 'CPP'
AND IMP.STATUS = 'POSTED'
AND CAT.CPP_ENABLED_FLAG = 'Y'
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') = 'Y'
AND ITF.SPLIT1_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.1') IMPAIRMENT_ID
, 1 SPLIT_NUMBER
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, 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) UNUSED_IMP_LOSS_AMOUNT
, CAT.MASS_REVAL_ID
, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID
, ITF.SPLIT1_LOSS_ACCT
, DECODE (NVL (ITF.SPLIT1_REVAL_RESERVE
, 0)
, 0
, 'N'
, 'Y')
, IMP.IMPAIRMENT_DATE
, IMP.SPLIT1_IMPAIR_CLASS
, IMP.SPLIT1_REASON
, (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
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_CAT CAT
, FA_ADDITIONS_B ADD_B
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 ADD_B.ASSET_ID = IMP.ASSET_ID
AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
AND IMP.SPLIT1_IMPAIR_CLASS = 'OTH'
AND IMP.STATUS = 'POSTED'
AND CAT.OTH_ENABLED_FLAG = 'Y'
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') = 'Y'
AND ITF.SPLIT1_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.2') IMPAIRMENT_ID
, 2 SPLIT_NUMBER
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, 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) UNUSED_IMP_LOSS_AMOUNT
, CAT.MASS_REVAL_ID
, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID
, ITF.SPLIT2_LOSS_ACCT
, DECODE (NVL (ITF.SPLIT2_REVAL_RESERVE
, 0)
, 0
, 'N'
, 'Y')
, IMP.IMPAIRMENT_DATE
, IMP.SPLIT2_IMPAIR_CLASS
, IMP.SPLIT2_REASON
, (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
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_CAT CAT
, FA_ADDITIONS_B ADD_B
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 ADD_B.ASSET_ID = IMP.ASSET_ID
AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
AND IMP.SPLIT2_IMPAIR_CLASS = 'CPP'
AND CAT.CPP_ENABLED_FLAG = 'Y'
AND IMP.STATUS = 'POSTED'
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') = 'Y'
AND ITF.SPLIT2_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.2') IMPAIRMENT_ID
, 2 SPLIT_NUMBER
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, 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) UNUSED_IMP_LOSS_AMOUNT
, CAT.MASS_REVAL_ID
, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID
, ITF.SPLIT2_LOSS_ACCT
, DECODE (NVL (ITF.SPLIT2_REVAL_RESERVE
, 0)
, 0
, 'N'
, 'Y')
, IMP.IMPAIRMENT_DATE
, IMP.SPLIT2_IMPAIR_CLASS
, IMP.SPLIT2_REASON
, (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
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_CAT CAT
, FA_ADDITIONS_B ADD_B
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 ADD_B.ASSET_ID = IMP.ASSET_ID
AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
AND IMP.SPLIT2_IMPAIR_CLASS = 'OTH'
AND IMP.STATUS = 'POSTED'
AND CAT.OTH_ENABLED_FLAG = 'Y'
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') = 'Y'
AND ITF.SPLIT2_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.3') IMPAIRMENT_ID
, 3 SPLIT_NUMBER
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, 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) UNUSED_IMP_LOSS_AMOUNT
, CAT.MASS_REVAL_ID
, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID
, ITF.SPLIT3_LOSS_ACCT
, DECODE (NVL (ITF.SPLIT3_REVAL_RESERVE
, 0)
, 0
, 'N'
, 'Y')
, IMP.IMPAIRMENT_DATE
, IMP.SPLIT3_IMPAIR_CLASS
, IMP.SPLIT3_REASON
, (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
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_CAT CAT
, FA_ADDITIONS_B ADD_B
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 ADD_B.ASSET_ID = IMP.ASSET_ID
AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
AND IMP.SPLIT3_IMPAIR_CLASS = 'CPP'
AND IMP.STATUS = 'POSTED'
AND CAT.CPP_ENABLED_FLAG = 'Y'
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') = 'Y'
AND ITF.SPLIT3_IMPAIR_CLASS IS NOT NULL UNION SELECT TO_NUMBER(IMP.IMPAIRMENT_ID||'.3') IMPAIRMENT_ID
, 3 SPLIT_NUMBER
, IMP.ASSET_ID
, IMP.BOOK_TYPE_CODE
, 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) UNUSED_IMP_LOSS_AMOUNT
, CAT.MASS_REVAL_ID
, ADD_B.ASSET_CATEGORY_ID CATEGORY_ID
, ITF.SPLIT3_LOSS_ACCT
, DECODE (NVL (ITF.SPLIT3_REVAL_RESERVE
, 0)
, 0
, 'N'
, 'Y')
, IMP.IMPAIRMENT_DATE
, IMP.SPLIT3_IMPAIR_CLASS
, IMP.SPLIT3_REASON
, (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
FROM FA_IMPAIRMENTS IMP
, FA_ITF_IMPAIRMENTS ITF
, FA_DEPRN_PERIODS PER
, FA_REVAL_LINK_IMP_CAT CAT
, FA_ADDITIONS_B ADD_B
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 ADD_B.ASSET_ID = IMP.ASSET_ID
AND ADD_B.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
AND IMP.SPLIT3_IMPAIR_CLASS = 'OTH'
AND IMP.STATUS = 'POSTED'
AND CAT.OTH_ENABLED_FLAG = 'Y'
AND NVL(ITF.SPLIT_IMPAIR_FLAG
, 'N') = 'Y'
AND ITF.SPLIT3_IMPAIR_CLASS IS NOT NULL