o/p 2

SELECT [ACCOUNT_MPL.MORTGAGE_BAL]
INTO [TEMP.MORTGAGE_BAL]
FROM [ACCOUNT_MPL]
WHERE [ACCOUNT_MPL.appl_num] = [appl_num]
AND [ACCOUNT_MPL.snapshot_month] = DATEADD(MONTH, -1, CURRENT_DATE);

IF UPPER(TRIM([V_SERVICING.LOAN_TYPE])) = 'MORTGAGE' THEN
    IF UPPER(TRIM([V_MORTGAGE_ACCOUNT_MANAGEMENT.SERVICING_STATUS])) <> 'DISCHARGED' THEN
        ASSIGN NVL([V_SERVICING.YTD_PREPAY_MADE], 0) TO [TEMP.CAL_YR_TO_DT_ADD_PAID_AMT];
    ELSE
        IF (YEAR(CURRENT_DATE) - YEAR([V_MORTGAGE_ACCOUNT_MANAGEMENT.EFFECTIVE_DATE_OF_DISCHARGE_ORG])) > 0 THEN
            ASSIGN 0 TO [TEMP.CAL_YR_TO_DT_ADD_PAID_AMT];
        ELSE
            ASSIGN NVL([V_SERVICING.YTD_PREPAY_MADE], 0) + 
                   (NVL([V_MORTGAGE_ACCOUNT_MANAGEMENT.CURRENT_PRINCIPAL_BAL], 0) - 
                   NVL([TEMP.MORTGAGE_BAL], 0)) TO [TEMP.CAL_YR_TO_DT_ADD_PAID_AMT];
        END IF;
    END IF;
END IF;

ASSIGN [TEMP.CAL_YR_TO_DT_ADD_PAID_AMT] TO [ACCOUNT_MPL.CAL_YR_TO_DT_ADD_PAID_AMT];

Comments

Post a Comment

Popular posts from this blog

o/p

IF UPPER(TRIM([V_SERVICING.LOAN_TYPE])) = 'MORTGAGE' THEN     IF UPPER(TRIM([V_MORTGAGE_ACCOUNT_MANAGEMENT.SERVICING_STATUS])) = 'DISCHARGED' AND         (YEAR(CURRENT_DATE) - YEAR([V_MORTGAGE_ACCOUNT_MANAGEMENT.EFFECTIVE_DATE_OF_DISCHARGE_ORG])) > 0 THEN         ASSIGN 0 TO [TEMP.CAL_YR_TO_DT_INTRST_PAID_AMT];     ELSE         ASSIGN NVL([V_SERVICING.YTD_INT_PAID],0) TO [TEMP.CAL_YR_TO_DT_INTRST_PAID_AMT];     END IF; END IF; ASSIGN [TEMP.CAL_YR_TO_DT_INTRST_PAID_AMT] TO [ACCOUNT_MPL.CAL_YR_TO_DT_INTRST_PAID_AMT];
Read more

dmrtl-slo

 SELECT SRC_KEY_ASSET_ID , count(*) number_of_duplicates  FROM z4_rlob_sit4.asset WHERE SRC_SYSTEM_CODE='DM_RTL_SLO'   AND SNAPSHOT_DT BETWEEN 20210930 AND 20231130 GROUP BY SRC_KEY_ASSET_ID  HAVING count(*) > 1; SELECT *  FROM z4_rlob_sit4.asset WHERE SRC_KEY_ASSET_ID = '500000018553749-20231026-23191828890-20231026 14:22:09'   AND SRC_SYSTEM_CODE='DM_RTL_SLO'   AND SNAPSHOT_DT=20231031; SELECT * FROM z4_rlob_sit4.asset WHERE SRC_KEY_ASSET_ID IN (     SELECT SRC_KEY_ASSET_ID     FROM z4_rlob_sit4.asset     WHERE SRC_SYSTEM_CODE='DM_RTL_SLO'     AND SNAPSHOT_DT BETWEEN 20210930 AND 20231130     GROUP BY SRC_KEY_ASSET_ID     HAVING count(*) > 1 ); WITH Duplicates AS (     SELECT SRC_KEY_ASSET_ID     FROM z4_rlob_sit4.asset     WHERE SRC_SYSTEM_CODE='DM_RTL_SLO'     AND SNAPSHOT_DT BETWEEN 20210930 AND 20231130     GROUP BY SRC_KEY_ASSET_ID...
Read more

xxyyzz

 SET hivevar:MACRO_CALENDAR_YEAR_TO_DATE = ( CASE      WHEN upper_trim_std(ampl.LOAN_TYPE) = 'MORTGAGE' THEN      CASE         WHEN upper_trim_std(ampl.SERVICING_STATUS) <> 'DISCHARGED' THEN             nvl(ampl.ytd_reg_prin,0)         WHEN upper_trim_std(ampl.SERVICING_STATUS) = 'DISCHARGED' THEN             CASE                 WHEN year_month_day_time(${RLOB_SNAPSHOT_DATE}) - year_month_day_time(ampl.effective_date_of_discharge_org) > 0 THEN 0                                 ELSE nvl(ampl.last_mth_prin_paid,0)             END     END END )  ; SET hivevar:MACRO_CALENDAR_YEAR_TO_DATE1 = (  CASE      WHEN upper_trim_std(ampl.LOAN_TYPE) = 'MORTGAGE' THEN...
Read more