secuvalorig

 SET hive.auto.convert.join=true;


USE ${RLOB_DATABASE};


ALTER TABLE ${RLOB_TARGET_TABLE} DROP IF EXISTS PARTITION (SRC_SYSTEM_CODE='${RLOB_SOURCE_SYSTEM_CODE}', ${RLOB_DATE_PARTITION});


INSERT INTO ${RLOB_TARGET_TABLE} PARTITION (SRC_SYSTEM_CODE='${RLOB_SOURCE_SYSTEM_CODE}', ${RLOB_DATE_PARTITION})

(

    security_val_orgntn_id, src_key_security_val_orgntn_id, src_key_cred_appl_mpl_id, asset_num, appl_status_dt,

    appl_num, appl_dt, ca_appl_status_dt, security_orgntn_id, cred_appl_mpl_id, src_key_security_orgntn_id,

    asset_hex_num, asset_value_amt, appraisal_amt, appraisal_dt, appraisal_type, appraisal_src,

    carried_forward_ind, src_snapshot_dt, src_load_dt_time, load_dt_time, last_update_user, update_reason,

    requestor_user, approver_user, batch_id, batch_code

)

SELECT DISTINCT

    IF(stg.APPL_NUM IS NOT NULL,

       safe_concat('DM_RTL_SLO', '-', ${RLOB_SNAPSHOT_DATE}, '-', security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE), '-', hex_to_decimal(stg.CLASS_ASSET_NUM), '-', year_month_day_time(security_val.SEC_APPSTATUSDATE)),

       safe_concat('DM_RTL_SLO', '-', ${RLOB_SNAPSHOT_DATE}, '-', security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE), '-', ${MACRO_ASSET_NUM}, '-', year_month_day_time(security_val.SEC_APPSTATUSDATE))

    ) AS SECURITY_VAL_ORGNTN_ID,

    IF(stg.APPL_NUM IS NOT NULL,

       safe_concat(security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE), '-', hex_to_decimal(stg.CLASS_ASSET_NUM), '-', year_month_day_time(security_val.SEC_APPSTATUSDATE)),

       safe_concat(security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE), '-', ${MACRO_ASSET_NUM}, '-', year_month_day_time(security_val.SEC_APPSTATUSDATE))

    ) AS SRC_KEY_SECURITY_VAL_ORGNTN_ID,

    safe_concat(security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE)) AS SRC_KEY_CRED_APPL_MPL_ID,

    IF(stg.ASSET_NUM IS NOT NULL, (hex_to_decimal(stg.CLASS_ASSET_NUM)), ${MACRO_ASSET_NUM}) AS ASSET_NUM,

    security_val.SEC_APPSTATUSDATE AS APPL_STATUS_DT,

    security_val.COL_CLASS_NUM AS APPL_NUM,

    security_val.APPLICATION_DATE AS APPL_DT,

    IF(security_val.SEC_APPSTATUSDATE = security_val.MAX_APPSTATUSDATE, security_val.PID_MAX_APPSTATUSDATE, security_val.SEC_APPSTATUSDATE) AS CA_APPL_STATUS_DT,

    IF(stg.APPL_NUM IS NOT NULL,

       safe_concat('DM_RTL_SLO', '-', ${RLOB_SNAPSHOT_DATE}, '-', security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE), '-', hex_to_decimal(stg.CLASS_ASSET_NUM), '-', year_month_day_time(security_val.SEC_APPSTATUSDATE)),

       safe_concat('DM_RTL_SLO', '-', ${RLOB_SNAPSHOT_DATE}, '-', security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE), '-', ${MACRO_ASSET_NUM}, '-', year_month_day_time(security_val.SEC_APPSTATUSDATE))

    ) AS SECURITY_ORGNTN_ID,

    safe_concat('DM_RTL_SLO', '-', ${RLOB_SNAPSHOT_DATE}, '-', security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE)) AS CRED_APPL_MPL_ID,

    IF(stg.APPL_NUM IS NOT NULL,

       safe_concat(security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE), '-', hex_to_decimal(stg.CLASS_ASSET_NUM), '-', year_month_day_time(security_val.SEC_APPSTATUSDATE)),

       safe_concat(security_val.COL_CLASS_NUM, '-', year_month_day(security_val.APPLICATION_DATE), '-', ${MACRO_ASSET_NUM}, '-', year_month_day_time(security_val.SEC_APPSTATUSDATE))

    ) AS SRC_KEY_SECURITY_ORGNTN_ID,

    IF(stg.APPL_NUM IS NOT NULL, 

       (stg.CLASS_ASSET_NUM), 

       upper(trim_leading_zero(security_val.ASSET_HEX_NUM))

    ) AS ASSET_HEX_NUM,

    security_val.ASSET_VALUE_AMT AS ASSET_VALUE_AMT,

    security_val.APPRAISAL_AMT AS APPRAISAL_AMT,

    security_val.APPRAISAL_DT AS APPRAISAL_DT,

    security_val.APPRAISAL_TYPE AS APPRAISAL_TYPE,

    security_val.APPRAISAL_SRC AS APPRAISAL_SRC,

    'N' AS CARRIED_FORWARD_IND,

    security_val.SRC_SNAPSHOT_DT AS SRC_SNAPSHOT_DT,

    security_val.SRC_LOAD_DT_TIME AS SRC_LOAD_DT_TIME,

    from_utc_timestamp(unix_timestamp() * 1000, 'America/Toronto') AS LOAD_DT_TIME,

    NULL AS LAST_UPDATE_USER,

    NULL AS UPDATE_REASON,

    NULL AS REQUESTOR_USER,

    NULL AS APPROVER_USER,

    ${RLOB_BATCH_ID} AS BATCH_ID,

    ${RLOB_BATCH_CODE} AS BATCH_CODE

FROM ${RLOB_WORK_DATABASE}.DMRTL_SECURITY_VAL_TMP security_val

LEFT JOIN ${MACRO_ADJUST_ASSET_ELEMENTS} stg

  ON (security_val.APPL_NUM = stg.APPL_NUM

  AND security_val.APPL_DT = stg.APPL_DT

  AND security_val.ASSET_HEX_NUM = stg.ASSET_HEX_NUM);


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