View: FA_SORP_CAT_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 asset level. In addition it retrieves the eligible impairment amount and avialble revaluation reserve amount avil
Implementation/DBA Data:	APPS.FA_SORP_CAT_LINK_REVAL_V

View Text

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

Columns