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];
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...
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...
Comments
Post a Comment