init query

 USE ${RLOB_WORK_DATABASE};

SET hivevar:RLOB_MONTH_YM=CAST(${RLOB_MONTH_END}/100 as INT);


SET hivevar:MULT_SYS_LOOKUP_PATH = '${RLOB_LOOKUPS_PATH}/ref_multimap_keyvalue/src_system=MULT_SYS';


SET hivevar:SOL_LEGAL_NAME = 

CASE 

    WHEN NOT empty_null(NAME_OF_CONTACT) AND upper_trim(NAME_OF_CONTACT) <> 'SELF EMPLOYED' THEN upper_trim(parse_name(NAME_OF_CONTACT))

    WHEN NOT empty_null(SOLICITOR_LAWYER_NAME) THEN upper_trim(parse_name(SOLICITOR_LAWYER_NAME))

    ELSE NULL

END

;


SET hivevar:MACRO_PARTY = 

(

SELECT *, RANK() OVER (PARTITION BY SRC_KEY_PARTY_ID ORDER BY SRC_SYSTEM_CODE DESC, SNAPSHOT_DT DESC) AS MAX_SNAPSHOT_DT

FROM

(

    SELECT SRC_KEY_PARTY_ID, PARTY_ID, SNAPSHOT_DT, SRC_SYSTEM_CODE

    FROM ${RLOB_DATABASE}.PARTY 

    WHERE SRC_SYSTEM_CODE IN ('${RLOB_SOURCE_SYSTEM_CODE}')

    AND SNAPSHOT_DT < ${RLOB_SNAPSHOT_DATE}

    

    UNION ALL

    

    SELECT SRC_KEY_PARTY_ID, PARTY_ID, SNAPSHOT_DT, SRC_SYSTEM_CODE

    FROM ${RLOB_DATABASE}.PARTY 

    WHERE SRC_SYSTEM_CODE IN ('TARGET_MO')

    AND SNAPSHOT_DT <= ${RLOB_SNAPSHOT_DATE}

) a

)

 ; 



SET hivevar:MACRO_PREV_PARTY_ADDRESS = 

(SELECT * FROM (SELECT SRC_KEY_PARTY_ID,

SRC_KEY_PARTY_ADDR_ID,

PARTY_ID,

SNAPSHOT_DT,

SRC_SYSTEM_CODE,

ADDR_LINE_1,

ADDR_LINE_2,

CITY_TOWN_NAME,

POSTAL_CODE,

PROVINCE_CODE,

RANK () OVER (PARTITION BY SRC_KEY_PARTY_ADDR_ID ORDER BY SNAPSHOT_DT DESC) AS CUST_RNK

FROM ${RLOB_DATABASE}.PARTY_ADDRESS

WHERE SNAPSHOT_DT < ${RLOB_SNAPSHOT_DATE}

AND SRC_SYSTEM_CODE = '${RLOB_SOURCE_SYSTEM_CODE}') tmp

WHERE CUST_RNK = 1

)

 ;


SET hivevar:MACRO_PREV_PARTY_DETAIL = 

(

SELECT DISTINCT *

FROM (SELECT  

SRC_KEY_PARTY_ID,

SRC_KEY_PARTY_DETAIL_ID,

APPL_STATUS_DT,

EMPLT_DURATION,

MTH_INCOME,

OCCUPATION_CATEG,

SELF_EMPLOYED_IND,

BASIC_INCOME_TYPE,

OCCUPATION_DESC,

OCCUPATION_DETAILED_DESC,

RANK() OVER (PARTITION BY SRC_KEY_PARTY_DETAIL_ID ORDER BY SNAPSHOT_DT DESC) AS RANK


FROM ${RLOB_DATABASE}.PARTY_DETAIL

WHERE SNAPSHOT_DT < ${RLOB_SNAPSHOT_DATE}

AND SRC_SYSTEM_CODE = '${RLOB_SOURCE_SYSTEM_CODE}'

) c1

WHERE RANK = 1

)

 ;


SET hivevar:MACRO_ADR1 =

(

CASE

WHEN p.ADDR_LINE_1 IS NOT NULL AND p.ADDR_LINE_1 = party_all.ADDR_LINE_1 THEN 'N'

WHEN p.SRC_KEY_PARTY_ADDR_ID IS NOT NULL AND p.ADDR_LINE_1 IS NULL AND party_all.ADDR_LINE_1 IS NULL THEN 'N'

ELSE 'Y'

END

)

;


SET hivevar:MACRO_CITY_TOWN_NAME =

(

CASE

WHEN p.CITY_TOWN_NAME IS NOT NULL AND p.CITY_TOWN_NAME = party_all.CITY_TOWN_NAME THEN 'N'

WHEN p.SRC_KEY_PARTY_ADDR_ID IS NOT NULL AND p.CITY_TOWN_NAME IS NULL AND party_all.CITY_TOWN_NAME IS NULL THEN 'N'

ELSE 'Y'

END

)

;


SET hivevar:MACRO_POSTAL_CODE =

(

CASE

WHEN p.POSTAL_CODE IS NOT NULL AND p.POSTAL_CODE = party_all.POSTAL_CODE THEN 'N'

WHEN p.SRC_KEY_PARTY_ADDR_ID IS NOT NULL AND p.POSTAL_CODE IS NULL AND party_all.POSTAL_CODE IS NULL THEN 'N'

ELSE 'Y'

END

)

;


SET hivevar:MACRO_PROVINCE_CODE =

(

CASE

WHEN p.PROVINCE_CODE IS NOT NULL AND p.PROVINCE_CODE = party_all.PROVINCE_CODE THEN 'N'

WHEN p.SRC_KEY_PARTY_ADDR_ID IS NOT NULL AND p.PROVINCE_CODE IS NULL AND party_all.PROVINCE_CODE IS NULL THEN 'N'

ELSE 'Y'

END

)

;


SET hivevar:MACRO_BASIC_INCOME_TYPE = 

(

CASE

WHEN p.BASIC_INCOME_TYPE IS NOT NULL AND p.BASIC_INCOME_TYPE = party_all.BASIC_INCOME_TYPE THEN 'N'

WHEN p.SRC_KEY_PARTY_DETAIL_ID IS NOT NULL AND p.BASIC_INCOME_TYPE IS NULL AND party_all.BASIC_INCOME_TYPE IS NULL THEN 'N'

ELSE 'Y'

END

)

 ; 


SET hivevar:MACRO_EMPLT_DURATION = 

(

CASE

WHEN p.EMPLT_DURATION IS NOT NULL AND p.EMPLT_DURATION = party_all.EMPLT_DURATION THEN 'N'

WHEN p.SRC_KEY_PARTY_DETAIL_ID IS NOT NULL AND p.EMPLT_DURATION IS NULL AND party_all.EMPLT_DURATION IS NULL THEN 'N'

ELSE 'Y'

END

)

 ; 


SET hivevar:MACRO_OCCUPATION_DESC = 

(

CASE

WHEN p.OCCUPATION_DESC IS NOT NULL AND p.OCCUPATION_DESC = party_all.OCCUPATION_DESC THEN 'N'

WHEN p.SRC_KEY_PARTY_DETAIL_ID IS NOT NULL AND p.OCCUPATION_DESC IS NULL AND party_all.OCCUPATION_DESC IS NULL THEN 'N'

ELSE 'Y'

END

)

 ; 


SET hivevar:MACRO_MTH_INCOME = 

(

CASE

WHEN p.MTH_INCOME IS NOT NULL AND p.MTH_INCOME = ROUND(party_all.MTH_INCOME,2) THEN 'N'

WHEN p.SRC_KEY_PARTY_DETAIL_ID IS NOT NULL AND p.MTH_INCOME IS NULL AND party_all.MTH_INCOME IS NULL THEN 'N'

ELSE 'Y'

END

)

 ; 


SET hivevar:MACRO_OCCUPATION_CATEG = 

(

CASE

WHEN p.OCCUPATION_CATEG IS NOT NULL AND p.OCCUPATION_CATEG = party_all.OCCUPATION_CATEG THEN 'N'

WHEN p.SRC_KEY_PARTY_DETAIL_ID IS NOT NULL AND p.OCCUPATION_CATEG IS NULL AND party_all.OCCUPATION_CATEG IS NULL THEN 'N'

ELSE 'Y'

END

)

 ;


SET hivevar:MACRO_OCCUPATION_DETAILED_DESC = 

(

CASE

WHEN p.OCCUPATION_DETAILED_DESC IS NOT NULL AND p.OCCUPATION_DETAILED_DESC = party_all.OCCUPATION_DETAILED_DESC THEN 'N'

WHEN p.SRC_KEY_PARTY_DETAIL_ID IS NOT NULL AND p.OCCUPATION_DETAILED_DESC IS NULL AND party_all.OCCUPATION_DETAILED_DESC IS NULL THEN 'N'

ELSE 'Y'

END

)

 ;


SET hivevar:MACRO_DOB_PRIMARY = 

CASE WHEN year_month_day(CLIENT_DOB_ORG) = 19000101

     THEN NULL

     ELSE CLIENT_DOB_ORG

END

 ;

 

SET hivevar:MACRO_DOB_CO_BRWR1 = 

CASE WHEN year_month_day(CLIENT_DOB_CO_BRWR1_ORG) = 19000101

     THEN NULL

     ELSE CLIENT_DOB_CO_BRWR1_ORG

END 

 ;


SET hivevar:MACRO_DOB_CO_BRWR2 = 

CASE WHEN year_month_day(CLIENT_DOB_CO_BRWR2_ORG) = 19000101

     THEN NULL

     ELSE CLIENT_DOB_CO_BRWR2_ORG

END

 ;


SET hivevar:MACRO_SIN_PRIMARY = 

CASE WHEN CLIENT_SIN_NUM = 0

     THEN NULL

     ELSE CLIENT_SIN_NUM

END

 ;


SET hivevar:MACRO_SIN_CO_BRWR1 = 

CASE WHEN CLIENT_SIN_CO_BRWR1 = 0

     THEN NULL

     ELSE CLIENT_SIN_CO_BRWR1

END 

 ;

 

SET hivevar:MACRO_SIN_CO_BRWR2 = 

CASE WHEN CLIENT_SIN_CO_BRWR2 = 0

     THEN NULL

     ELSE CLIENT_SIN_CO_BRWR2

END 

 ;


SET hivevar:MACRO_CRED_APPL_MPL_FULL = 

(

SELECT SRC_KEY_CRED_APPL_MPL_FULL_ID, CRED_APPL_MPL_ID, APPL_NUM, APPL_DT, APPL_STATUS_DT, NUM_OF_RENTAL_PROPERTIES, SRC_SYSTEM_CODE, SRC_SNAPSHOT_DT, SRC_LOAD_DT_TIME, SNAPSHOT_DT, LOAD_DT_TIME,HPP_IND,LAST_STATUS_CNCL_IND,HPP_RELATED_APPL_NUM,MORTGAGE_NUM

FROM ${RLOB_DATABASE}.CREDIT_APPLICATION_MPL_FULL

WHERE SNAPSHOT_DT = ${RLOB_SNAPSHOT_DATE}

)

 ;



CREATE TEMPORARY FUNCTION safe_concat as '${RLOB_UDF_VERSION}.SafeConcat' ;


CREATE TEMPORARY FUNCTION multimap_lookup as '${RLOB_UDF_VERSION}.RefLookup';


CREATE TEMPORARY MACRO year_month(t TIMESTAMP) year(t)*100+month(t);


CREATE TEMPORARY MACRO year_month_day(t TIMESTAMP) year(t)*10000+month(t)*100+day(t);


CREATE TEMPORARY MACRO year_month_day_time(t TIMESTAMP) from_unixtime(unix_timestamp(t), 'yyyyMMdd HH:mm:ss');


CREATE TEMPORARY MACRO upper_trim(s STRING) upper(trim(s));


CREATE TEMPORARY MACRO empty_null(s STRING) CASE WHEN s IS NULL THEN TRUE WHEN trim(s) = '' THEN TRUE ELSE FALSE END;


CREATE TEMPORARY MACRO parse_name(name STRING)

CASE

    WHEN length(regexp_replace(trim(name) , '[_./?:#-*]+', '')) = 0 THEN NULL

    WHEN substr(name,1,1) = '-' AND cast(name as INT) IS NOT NULL THEN name

    WHEN regexp_replace(trim(name) , '^[^a-zA-Z0-9\\P{ASCII}()#$%&*]+', '') = '' THEN NULL

    ELSE regexp_replace(trim(name) , '^[^a-zA-Z0-9\\P{ASCII}()#$%&*"]+', '')

END 

;


USE ${RLOB_WORK_DATABASE};


DROP TABLE IF EXISTS TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP;

CREATE TABLE TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP AS 

SELECT * 

FROM ${RLOB_EDH_MPL_DATABASE}.V_MORTGAGE_ACCOUNT_MANAGEMENT 

WHERE PROC_DT_DRVD BETWEEN ${RLOB_MONTH_START} and ${RLOB_MONTH_END} 

AND 

(

     ((CURRENT_PRINCIPAL_BAL + LINE_OF_CREDIT_BAL) > 0.00) 

     OR

     upper_trim(MORTGAGE_PRODUCT) = 'LOC' 

     OR 

     upper_trim(SUB_PRODUCT1) = 'IMO'

) 

AND 

(

     upper_trim(SERVICING_STATUS) IN ('INTERIM','ACTIVE') 

     OR 

     (    upper_trim(SERVICING_STATUS) = 'DISCHARGED' 

          AND ((CURRENT_PRINCIPAL_BAL + LINE_OF_CREDIT_BAL) > 0.00) 

     ) 

     OR 

     (    upper_trim(SERVICING_STATUS) = 'DISCHARGED'  

          AND ((CURRENT_PRINCIPAL_BAL + LINE_OF_CREDIT_BAL) = 0.00) 

          AND (year_month_day(EFFECTIVE_DATE_OF_DISCHARGE_ORG) BETWEEN ${RLOB_MONTH_START} AND ${RLOB_MONTH_END})

     )

) 

;


CREATE TEMPORARY TABLE TMP_PARTY_ALL AS

SELECT

'BORROWER-01' AS PARTY_TYPE,

'BORROWER' AS PARTY_ROLE,

1 AS REL_RANK,

safe_concat('${RLOB_SOURCE_SYSTEM_CODE}', '-', ${RLOB_SNAPSHOT_DATE}, '-', MORTGAGE_NUM) AS ACCT_MPL_ID,

MORTGAGE_NUM AS SRC_KEY_ACCT_MPL_ID,

safe_concat(upper_trim(CUST_NAME), '-',${MACRO_SIN_PRIMARY}, '-', year_month_day(${MACRO_DOB_PRIMARY})) AS SRC_KEY_PARTY_ID,

APPLICATION_DATE_ORG AS APPL_DT,

MORTGAGE_NUM AS APPL_NUM,

multimap_lookup(${MULT_SYS_LOOKUP_PATH},0,'MPL_MULT_SYS_BSIC_INCME_TYP','INCOME CODE',TYPE_OF_BASIC_INC) AS BASIC_INCOME_TYPE,

upper_trim(CUST_NAME) AS LEGAL_NAME,

${MACRO_DOB_PRIMARY} AS BIRTH_DT,

'1' AS ADDR_CODE,

IF(NOT empty_null(CUST_ADDRESS), upper_trim(CUST_ADDRESS), NULL) AS ADDR_LINE_1,

IF(NOT empty_null(CUST_CITY), upper_trim(CUST_CITY), NULL) AS CITY_TOWN_NAME,

EMPLOYMENT_TENURE AS EMPLT_DURATION,

MORTGAGE_NUM AS MORTGAGE_NUM,

(BASIC_INC_ANNUAL/12) AS MTH_INCOME,

IF(NOT empty_null(OCCUPTN_CATEGORY), upper_trim(OCCUPTN_CATEGORY), NULL) AS OCCUPATION_CATEG,

IF(NOT empty_null(OCCUPTN_DESC), upper_trim(OCCUPTN_DESC), NULL) AS OCCUPATION_DESC,

IF(NOT empty_null(DETAILED_DESC), upper_trim(DETAILED_DESC), NULL) AS OCCUPATION_DETAILED_DESC,

IF(NOT empty_null(CUST_POSTAL_CODE), regexp_replace(upper_trim(CUST_POSTAL_CODE), '\\s+', ''), NULL) AS POSTAL_CODE,

IF(NOT empty_null(CUST_PROVINCE), upper_trim(CUST_PROVINCE), NULL) AS PROVINCE_CODE,

CASE 

    WHEN upper_trim(parse_name(EMPLOYER_NAME)) IN ('BFS', 'SELF') THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC) = 'BFS' THEN 'Y'

    WHEN upper_trim(OCCUPTN_CATEGORY) = 'SELF-EMPLOYED' THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC) = 'SELF EMPLOYED' AND upper_trim(substr(OCCUPTN_DESC, 1, 5)) IN ('OWNER', '') THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC) = 'SELF EMPLOYED' AND upper_trim(substr(OCCUPTN_DESC, 1, 4)) IN ('SELF', '') THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC) = 'SALARY' AND upper_trim(substr(OCCUPTN_DESC, 1, 3)) IN ('BFS', '') THEN 'Y'

    ELSE 'N' 

END AS SELF_EMPLOYED_IND,

${MACRO_SIN_PRIMARY} AS SOCIAL_INSURANCE_NUM,

DATE_OF_FUNDING_ORG,

PROPOSED_DATE_OF_ADVANCE_ORG,

MORTGAGE_STATUS,

PROC_DT_DRVD,

ROW_LOADED_DATE_DRVD,

STATUS_DATE_ORG


FROM ${RLOB_WORK_DATABASE}.TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP tmp


WHERE NOT (empty_null(CUST_NAME) AND nvl(CLIENT_SIN_NUM,0)=0 AND nvl(year_month_day(CLIENT_DOB_ORG), '19000101') = '19000101')


UNION ALL


SELECT

'BORROWER-02' AS PARTY_TYPE,

'BORROWER' AS PARTY_ROLE,

2 AS REL_RANK,

safe_concat('${RLOB_SOURCE_SYSTEM_CODE}', '-', ${RLOB_SNAPSHOT_DATE}, '-', MORTGAGE_NUM) AS ACCT_MPL_ID,

MORTGAGE_NUM AS SRC_KEY_ACCT_MPL_ID,

safe_concat(upper_trim(CUST_NAME_CO_BRWR1), '-',${MACRO_SIN_CO_BRWR1}, '-', year_month_day(${MACRO_DOB_CO_BRWR1})) AS SRC_KEY_PARTY_ID,

APPLICATION_DATE_ORG AS APPL_DT,

MORTGAGE_NUM AS APPL_NUM,

multimap_lookup(${MULT_SYS_LOOKUP_PATH},0,'MPL_MULT_SYS_BSIC_INCME_TYP','INCOME CODE',TYPE_OF_BASIC_INC_CO_BRWR1) AS BASIC_INCOME_TYPE,

upper_trim(CUST_NAME_CO_BRWR1) AS LEGAL_NAME,

${MACRO_DOB_CO_BRWR1} AS BIRTH_DT,

'1' AS ADDR_CODE,

IF(NOT empty_null(CUST_ADDRESS_CO_BRWR1), upper_trim(CUST_ADDRESS_CO_BRWR1), NULL) AS ADDR_LINE_1,

IF(NOT empty_null(CUST_CITY_CO_BRWR1), upper_trim(CUST_CITY_CO_BRWR1), NULL) AS CITY_TOWN_NAME,

EMPLOYMENT_TENURE_CO_BRWR1 AS EMPLT_DURATION,

MORTGAGE_NUM AS MORTGAGE_NUM,

(BASIC_INC_ANNUAL_CO_BRWR1/12) AS MTH_INCOME,

IF(NOT empty_null(OCCUPTN_CATEGORY_CO_BRWR1), upper_trim(OCCUPTN_CATEGORY_CO_BRWR1), NULL) AS OCCUPATION_CATEG,

IF(NOT empty_null(OCCUPTN_DESC_CO_BRWR_1), upper_trim(OCCUPTN_DESC_CO_BRWR_1), NULL) AS OCCUPATION_DESC,

IF(NOT empty_null(DETAILED_DESC_CO_BRWR1), upper_trim(DETAILED_DESC_CO_BRWR1), NULL) AS OCCUPATION_DETAILED_DESC,

IF(NOT empty_null(CUST_POSTAL_CODE_CO_BRWR1), regexp_replace(upper_trim(CUST_POSTAL_CODE_CO_BRWR1), '\\s+', ''), NULL) AS POSTAL_CODE,

IF(NOT empty_null(CUST_PROVINCE_CO_BRWR1), upper_trim(CUST_PROVINCE_CO_BRWR1), NULL) AS PROVINCE_CODE,

CASE 

    WHEN upper_trim(parse_name(EMPLOYER_NAME_CO_BRWR1)) IN ('BFS', 'SELF') THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC_CO_BRWR1) = 'BFS' THEN 'Y'

    WHEN upper_trim(OCCUPTN_CATEGORY_CO_BRWR1) = 'SELF-EMPLOYED' THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC_CO_BRWR1) = 'SELF EMPLOYED' AND upper_trim(substr(OCCUPTN_DESC_CO_BRWR_1, 1, 5)) IN ('OWNER', '') THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC_CO_BRWR1) = 'SELF EMPLOYED' AND upper_trim(substr(OCCUPTN_DESC_CO_BRWR_1, 1, 4)) IN ('SELF', '') THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC_CO_BRWR1) = 'SALARY' AND upper_trim(substr(OCCUPTN_DESC_CO_BRWR_1, 1, 3)) IN ('BFS', '') THEN 'Y'

    ELSE 'N' 

END AS SELF_EMPLOYED_IND,

${MACRO_SIN_CO_BRWR1} AS SOCIAL_INSURANCE_NUM,

NULL AS DATE_OF_FUNDING_ORG,

NULL AS PROPOSED_DATE_OF_ADVANCE_ORG,

NULL AS MORTGAGE_STATUS,

PROC_DT_DRVD,

ROW_LOADED_DATE_DRVD,

STATUS_DATE_ORG


FROM ${RLOB_WORK_DATABASE}.TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP

WHERE NOT (empty_null(CUST_NAME_CO_BRWR1) AND nvl(CLIENT_SIN_CO_BRWR1,0)=0 AND nvl(year_month_day(CLIENT_DOB_CO_BRWR1_ORG), '19000101') = '19000101')


UNION ALL


SELECT

'BORROWER-03' AS PARTY_TYPE,

'BORROWER' AS PARTY_ROLE,

3 AS REL_RANK,

safe_concat('${RLOB_SOURCE_SYSTEM_CODE}', '-', ${RLOB_SNAPSHOT_DATE}, '-', MORTGAGE_NUM) AS ACCT_MPL_ID,

MORTGAGE_NUM AS SRC_KEY_ACCT_MPL_ID,

safe_concat(upper_trim(CUST_NAME_CO_BRWR2), '-',${MACRO_SIN_CO_BRWR2}, '-', year_month_day(${MACRO_DOB_CO_BRWR2})) AS SRC_KEY_PARTY_ID,

APPLICATION_DATE_ORG AS APPL_DT,

MORTGAGE_NUM AS APPL_NUM,

multimap_lookup(${MULT_SYS_LOOKUP_PATH},0,'MPL_MULT_SYS_BSIC_INCME_TYP','INCOME CODE',TYPE_OF_BASIC_INC_CO_BRWR2) AS BASIC_INCOME_TYPE,

upper_trim(CUST_NAME_CO_BRWR2) AS LEGAL_NAME,

${MACRO_DOB_CO_BRWR2} AS BIRTH_DT,

'1' AS ADDR_CODE,

IF(NOT empty_null(CUST_ADDRESS_CO_BRWR2), upper_trim(CUST_ADDRESS_CO_BRWR2), NULL) AS ADDR_LINE_1,

IF(NOT empty_null(CUST_CITY_CO_BRWR2), upper_trim(CUST_CITY_CO_BRWR2), NULL) AS CITY_TOWN_NAME,

EMPLOYMENT_TENURE_CO_BRWR2 AS EMPLT_DURATION,

MORTGAGE_NUM AS MORTGAGE_NUM,

(BASIC_INC_ANNUAL_CO_BRWR2/12) AS MTH_INCOME,

IF(NOT empty_null(OCCUPTN_CATEGORY_CO_BRWR2), upper_trim(OCCUPTN_CATEGORY_CO_BRWR2), NULL) AS OCCUPATION_CATEG,

IF(NOT empty_null(OCCUPTN_DESC_CO_BRWR_2), upper_trim(OCCUPTN_DESC_CO_BRWR_2), NULL) AS OCCUPATION_DESC,

IF(NOT empty_null(DETAILED_DESC_CO_BRWR2), upper_trim(DETAILED_DESC_CO_BRWR2), NULL) AS OCCUPATION_DETAILED_DESC,

IF(NOT empty_null(CUST_POSTAL_CODE_CO_BRWR2), regexp_replace(upper_trim(CUST_POSTAL_CODE_CO_BRWR2), '\\s+', ''), NULL) AS POSTAL_CODE,

IF(NOT empty_null(CUST_PROVINCE_CO_BRWR2), upper_trim(CUST_PROVINCE_CO_BRWR2), NULL) AS PROVINCE_CODE,

CASE 

    WHEN upper_trim(parse_name(EMPLOYER_NAME_CO_BRWR2)) IN ('BFS', 'SELF') THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC_CO_BRWR2) = 'BFS' THEN 'Y'

    WHEN upper_trim(OCCUPTN_CATEGORY_CO_BRWR2) = 'SELF-EMPLOYED' THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC_CO_BRWR2) = 'SELF EMPLOYED' AND upper_trim(substr(OCCUPTN_DESC_CO_BRWR_2, 1, 5)) IN ('OWNER', '') THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC_CO_BRWR2) = 'SELF EMPLOYED' AND upper_trim(substr(OCCUPTN_DESC_CO_BRWR_2, 1, 4)) IN ('SELF', '') THEN 'Y'

    WHEN upper_trim(TYPE_OF_BASIC_INC_CO_BRWR2) = 'SALARY' AND upper_trim(substr(OCCUPTN_DESC_CO_BRWR_2, 1, 3)) IN ('BFS', '') THEN 'Y'

    ELSE 'N' 

END AS SELF_EMPLOYED_IND,

${MACRO_SIN_CO_BRWR2} AS SOCIAL_INSURANCE_NUM,

NULL AS DATE_OF_FUNDING_ORG,

NULL AS PROPOSED_DATE_OF_ADVANCE_ORG,

NULL AS MORTGAGE_STATUS,

PROC_DT_DRVD,

ROW_LOADED_DATE_DRVD,

STATUS_DATE_ORG


FROM ${RLOB_WORK_DATABASE}.TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP

WHERE NOT (empty_null(CUST_NAME_CO_BRWR2) AND nvl(CLIENT_SIN_CO_BRWR2,0)=0 AND nvl(year_month_day(CLIENT_DOB_CO_BRWR2_ORG), '19000101') = '19000101')


UNION ALL


SELECT

'EMPLOYER_NAME-01' AS PARTY_TYPE,

'EMPLOYER' AS PARTY_ROLE,

1 AS REL_RANK,

safe_concat('${RLOB_SOURCE_SYSTEM_CODE}', '-', ${RLOB_SNAPSHOT_DATE}, '-', MORTGAGE_NUM) AS ACCT_MPL_ID,

MORTGAGE_NUM AS SRC_KEY_ACCT_MPL_ID,

safe_concat(upper_trim(parse_name(EMPLOYER_NAME)), '-', NULL, '-', NULL) AS SRC_KEY_PARTY_ID,

APPLICATION_DATE_ORG AS APPL_DT,

MORTGAGE_NUM AS APPL_NUM,

NULL AS BASIC_INCOME_TYPE,

upper_trim(parse_name(EMPLOYER_NAME)) AS LEGAL_NAME,

NULL AS BIRTH_DT,

NULL AS ADDR_CODE,

NULL AS ADDR_LINE_1,

NULL AS CITY_TOWN_NAME,

NULL AS EMPLT_DURATION,

MORTGAGE_NUM AS MORTGAGE_NUM,

NULL AS MTH_INCOME,

NULL AS OCCUPATION_CATEG,

NULL AS OCCUPATION_DESC,

NULL AS OCCUPATION_DETAILED_DESC,

NULL AS POSTAL_CODE,

NULL AS PROVINCE_CODE,

NULL AS SELF_EMPLOYED_IND,

NULL AS SOCIAL_INSURANCE_NUM,

NULL AS DATE_OF_FUNDING_ORG,

NULL AS PROPOSED_DATE_OF_ADVANCE_ORG,

NULL AS MORTGAGE_STATUS,

PROC_DT_DRVD,

ROW_LOADED_DATE_DRVD,

STATUS_DATE_ORG


FROM ${RLOB_WORK_DATABASE}.TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP

WHERE NOT empty_null(parse_name(EMPLOYER_NAME))


UNION ALL


SELECT

'EMPLOYER_NAME-02' AS PARTY_TYPE,

'EMPLOYER' AS PARTY_ROLE,

2 AS REL_RANK,

safe_concat('${RLOB_SOURCE_SYSTEM_CODE}', '-', ${RLOB_SNAPSHOT_DATE}, '-', MORTGAGE_NUM) AS ACCT_MPL_ID,

MORTGAGE_NUM AS SRC_KEY_ACCT_MPL_ID,

safe_concat(upper_trim(parse_name(EMPLOYER_NAME_CO_BRWR1)), '-', NULL, '-', NULL) AS SRC_KEY_PARTY_ID,

APPLICATION_DATE_ORG AS APPL_DT,

MORTGAGE_NUM AS APPL_NUM,

NULL AS BASIC_INCOME_TYPE,

upper_trim(parse_name(EMPLOYER_NAME_CO_BRWR1)) AS LEGAL_NAME,

NULL AS BIRTH_DT,

NULL AS ADDR_CODE,

NULL AS ADDR_LINE_1,

NULL AS CITY_TOWN_NAME,

NULL AS EMPLT_DURATION,

MORTGAGE_NUM AS MORTGAGE_NUM,

NULL AS MTH_INCOME,

NULL AS OCCUPATION_CATEG,

NULL AS OCCUPATION_DESC,

NULL AS OCCUPATION_DETAILED_DESC,

NULL AS POSTAL_CODE,

NULL AS PROVINCE_CODE,

NULL AS SELF_EMPLOYED_IND,

NULL AS SOCIAL_INSURANCE_NUM,

NULL AS DATE_OF_FUNDING_ORG,

NULL AS PROPOSED_DATE_OF_ADVANCE_ORG,

NULL AS MORTGAGE_STATUS,

PROC_DT_DRVD,

ROW_LOADED_DATE_DRVD,

STATUS_DATE_ORG


FROM ${RLOB_WORK_DATABASE}.TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP

WHERE NOT empty_null(parse_name(EMPLOYER_NAME_CO_BRWR1))


UNION ALL


SELECT

'EMPLOYER_NAME-03' AS PARTY_TYPE,

'EMPLOYER' AS PARTY_ROLE,

3 AS REL_RANK,

safe_concat('${RLOB_SOURCE_SYSTEM_CODE}', '-', ${RLOB_SNAPSHOT_DATE}, '-', MORTGAGE_NUM) AS ACCT_MPL_ID,

MORTGAGE_NUM AS SRC_KEY_ACCT_MPL_ID,

safe_concat(upper_trim(parse_name(EMPLOYER_NAME_CO_BRWR2)), '-', NULL, '-', NULL) AS SRC_KEY_PARTY_ID,

APPLICATION_DATE_ORG AS APPL_DT,

MORTGAGE_NUM AS APPL_NUM,

NULL AS BASIC_INCOME_TYPE,

upper_trim(parse_name(EMPLOYER_NAME_CO_BRWR2)) AS LEGAL_NAME,

NULL AS BIRTH_DT,

NULL AS ADDR_CODE,

NULL AS ADDR_LINE_1,

NULL AS CITY_TOWN_NAME,

NULL AS EMPLT_DURATION,

MORTGAGE_NUM AS MORTGAGE_NUM,

NULL AS MTH_INCOME,

NULL AS OCCUPATION_CATEG,

NULL AS OCCUPATION_DESC,

NULL AS OCCUPATION_DETAILED_DESC,

NULL AS POSTAL_CODE,

NULL AS PROVINCE_CODE,

NULL AS SELF_EMPLOYED_IND,

NULL AS SOCIAL_INSURANCE_NUM,

NULL AS DATE_OF_FUNDING_ORG,

NULL AS PROPOSED_DATE_OF_ADVANCE_ORG,

NULL AS MORTGAGE_STATUS,

PROC_DT_DRVD,

ROW_LOADED_DATE_DRVD,

STATUS_DATE_ORG


FROM ${RLOB_WORK_DATABASE}.TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP

WHERE NOT empty_null(parse_name(EMPLOYER_NAME_CO_BRWR2))


UNION ALL


SELECT

'APPRAISER' AS PARTY_TYPE,

'APPRAISER' AS PARTY_ROLE,

NULL AS REL_RANK,

safe_concat('${RLOB_SOURCE_SYSTEM_CODE}', '-', ${RLOB_SNAPSHOT_DATE}, '-', MORTGAGE_NUM) AS ACCT_MPL_ID,

MORTGAGE_NUM AS SRC_KEY_ACCT_MPL_ID,

safe_concat(upper_trim(parse_name(NAME_OF_APPRAISER)), '-', NULL, '-', NULL) AS SRC_KEY_PARTY_ID,

APPLICATION_DATE_ORG AS APPL_DT,

MORTGAGE_NUM AS APPL_NUM,

NULL AS BASIC_INCOME_TYPE,

upper_trim(parse_name(NAME_OF_APPRAISER)) AS LEGAL_NAME,

NULL AS BIRTH_DT,

NULL AS ADDR_CODE,

NULL AS ADDR_LINE_1,

NULL AS CITY_TOWN_NAME,

NULL AS EMPLT_DURATION,

MORTGAGE_NUM AS MORTGAGE_NUM,

NULL AS MTH_INCOME,

NULL AS OCCUPATION_CATEG,

NULL AS OCCUPATION_DESC,

NULL AS OCCUPATION_DETAILED_DESC,

NULL AS POSTAL_CODE,

NULL AS PROVINCE_CODE,

NULL AS SELF_EMPLOYED_IND,

NULL AS SOCIAL_INSURANCE_NUM,

NULL AS DATE_OF_FUNDING_ORG,

NULL AS PROPOSED_DATE_OF_ADVANCE_ORG,

NULL AS MORTGAGE_STATUS,

PROC_DT_DRVD,

ROW_LOADED_DATE_DRVD,

STATUS_DATE_ORG


FROM ${RLOB_WORK_DATABASE}.TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP

WHERE NOT empty_null(parse_name(NAME_OF_APPRAISER)) AND upper_trim(parse_name(NAME_OF_APPRAISER)) <> ''


UNION ALL


SELECT

'SOLICITOR' AS PARTY_TYPE,

IF((upper_trim(NAME_OF_CONTACT) IS NOT NULL OR upper_trim(parse_name(SOLICITOR_LAWYER_NAME)) IS NOT NULL),'SOLICITOR FIRM',NULL) AS PARTY_ROLE,

NULL AS REL_RANK,

safe_concat('${RLOB_SOURCE_SYSTEM_CODE}', '-', ${RLOB_SNAPSHOT_DATE}, '-', MORTGAGE_NUM) AS ACCT_MPL_ID,

MORTGAGE_NUM AS SRC_KEY_ACCT_MPL_ID,

safe_concat(${SOL_LEGAL_NAME}, '-', NULL, '-', NULL) AS SRC_KEY_PARTY_ID,

APPLICATION_DATE_ORG AS APPL_DT,

MORTGAGE_NUM AS APPL_NUM,

NULL AS BASIC_INCOME_TYPE,

${SOL_LEGAL_NAME} AS LEGAL_NAME,

NULL AS BIRTH_DT,

'1' AS ADDR_CODE,

NULL AS ADDR_LINE_1,

NULL AS CITY_TOWN_NAME,

NULL AS EMPLT_DURATION,

MORTGAGE_NUM AS MORTGAGE_NUM,

NULL AS MTH_INCOME,

NULL AS OCCUPATION_CATEG,

NULL AS OCCUPATION_DESC,

NULL AS OCCUPATION_DETAILED_DESC,

IF(NOT empty_null(POSTAL_CODE_OF_LAWYER_ADDRESS), regexp_replace(upper_trim(POSTAL_CODE_OF_LAWYER_ADDRESS), '\\s+', ''), NULL) AS POSTAL_CODE,

IF(NOT empty_null(PROVINCE_OF_LAWYER_ADDRESS), upper_trim(PROVINCE_OF_LAWYER_ADDRESS), NULL) AS PROVINCE_CODE,

NULL AS SELF_EMPLOYED_IND,

NULL AS SOCIAL_INSURANCE_NUM,

NULL AS DATE_OF_FUNDING_ORG,

NULL AS PROPOSED_DATE_OF_ADVANCE_ORG,

NULL AS MORTGAGE_STATUS,

PROC_DT_DRVD,

ROW_LOADED_DATE_DRVD,

STATUS_DATE_ORG


FROM ${RLOB_WORK_DATABASE}.TARGET_MORTGAGE_ACCOUNT_MANAGEMENT_MP

WHERE (NOT empty_null(NAME_OF_CONTACT) AND upper_trim(NAME_OF_CONTACT) <> 'SELF EMPLOYED') OR ((empty_null(NAME_OF_CONTACT) OR upper_trim(NAME_OF_CONTACT) = 'SELF EMPLOYED') AND NOT empty_null(SOLICITOR_LAWYER_NAME))

;


DROP TABLE IF EXISTS TARGET_MP_PARTY_ALL;

CREATE TABLE TARGET_MP_PARTY_ALL AS

SELECT party_all.*,

COALESCE(upper_trim(party.PARTY_ID), safe_concat('${RLOB_SOURCE_SYSTEM_CODE}', '-', ${RLOB_SNAPSHOT_DATE}, '-', upper_trim(party_all.SRC_KEY_PARTY_ID))) AS PARTY_ID


FROM ${RLOB_WORK_DATABASE}.TMP_PARTY_ALL party_all


LEFT JOIN ${MACRO_PARTY} party 

   ON party.SRC_KEY_PARTY_ID = party_all.SRC_KEY_PARTY_ID  

   AND party.MAX_SNAPSHOT_DT=1

;


DROP TABLE IF EXISTS TARGET_MP_PARTY;

CREATE TABLE TARGET_MP_PARTY AS

SELECT DISTINCT

party_all.PARTY_ID,

party_all.SRC_KEY_PARTY_ID,

party_all.LEGAL_NAME,

party_all.BIRTH_DT,

party_all.SOCIAL_INSURANCE_NUM,

party_all.PROC_DT_DRVD AS SRC_SNAPSHOT_DT,

party_all.ROW_LOADED_DATE_DRVD AS SRC_LOAD_DT_TIME


FROM ${RLOB_WORK_DATABASE}.TARGET_MP_PARTY_ALL party_all


LEFT JOIN ${MACRO_PARTY} party 

   ON party.PARTY_ID = party_all.PARTY_ID  

   AND party.MAX_SNAPSHOT_DT=1


WHERE party.PARTY_ID IS NULL

;


DROP TABLE IF EXISTS TARGET_MP_PARTY_REL;

CREATE TABLE TARGET_MP_PARTY_REL AS

SELECT DISTINCT

party_all.ACCT_MPL_ID,

party_all.SRC_KEY_ACCT_MPL_ID,

party_all.PARTY_ID,

party_all.SRC_KEY_PARTY_ID,

party_all.APPL_DT,

party_all.APPL_NUM,

party_all.PARTY_ROLE,

party_all.REL_RANK,

party_all.PROC_DT_DRVD AS SRC_SNAPSHOT_DT,

party_all.ROW_LOADED_DATE_DRVD AS SRC_LOAD_DT_TIME


FROM ${RLOB_WORK_DATABASE}.TARGET_MP_PARTY_ALL party_all

;


DROP TABLE IF EXISTS TARGET_MP_PARTY_DETAIL;

CREATE TABLE TARGET_MP_PARTY_DETAIL AS

SELECT party_detail.*

FROM

(

     SELECT DISTINCT *

     FROM 

     (

         SELECT *, RANK() OVER(PARTITION BY SRC_KEY_PARTY_ID ORDER BY APPL_NUM DESC) AS RN

         FROM

         (

             SELECT 

party_all.PARTY_ID,

party_all.SRC_KEY_PARTY_ID,

party_all.APPL_DT,

party_all.APPL_NUM,

party_all.BASIC_INCOME_TYPE,

party_all.EMPLT_DURATION,

party_all.MTH_INCOME,

party_all.OCCUPATION_CATEG,

party_all.OCCUPATION_DESC,

party_all.OCCUPATION_DETAILED_DESC,

party_all.SELF_EMPLOYED_IND,

party_all.PROC_DT_DRVD AS SRC_SNAPSHOT_DT,

party_all.ROW_LOADED_DATE_DRVD AS SRC_LOAD_DT_TIME,

party_all.STATUS_DATE_ORG AS STATUS_DATE_ORG,

IF (${MACRO_BASIC_INCOME_TYPE} = 'N' AND ${MACRO_EMPLT_DURATION} = 'N' AND ${MACRO_OCCUPATION_DESC} = 'N' AND ${MACRO_MTH_INCOME} = 'N' AND ${MACRO_OCCUPATION_CATEG} = 'N' AND ${MACRO_OCCUPATION_DETAILED_DESC} = 'N' AND party_all.SRC_KEY_PARTY_ID = p.SRC_KEY_PARTY_ID, 'N', 'Y') AS CHANGE_DETAILS_IND


FROM ${RLOB_WORK_DATABASE}.TARGET_MP_PARTY_ALL party_all


LEFT JOIN ${MACRO_PREV_PARTY_DETAIL} p 

   ON safe_concat(party_all.SRC_KEY_PARTY_ID,'-',year_month_day_time(party_all.STATUS_DATE_ORG)) = p.SRC_KEY_PARTY_DETAIL_ID


WHERE party_all.PARTY_ROLE = 'BORROWER'

         ) a 

WHERE a.CHANGE_DETAILS_IND = 'Y') aaa WHERE RN = 1

) party_detail

;


DROP TABLE IF EXISTS TARGET_MP_PARTY_ADDR;

CREATE TABLE TARGET_MP_PARTY_ADDR AS

SELECT party_addr.*

FROM

(

     SELECT DISTINCT *

     FROM 

     (

         SELECT *, RANK() OVER(PARTITION BY SRC_KEY_PARTY_ID ORDER BY APPL_NUM DESC) AS RN

         FROM

         (

SELECT 

party_all.PARTY_ID,

party_all.SRC_KEY_PARTY_ID,

party_all.APPL_DT,

party_all.APPL_NUM,

party_all.ADDR_CODE,

party_all.ADDR_LINE_1,

party_all.CITY_TOWN_NAME,

party_all.POSTAL_CODE,

party_all.PROVINCE_CODE,

party_all.PROC_DT_DRVD AS SRC_SNAPSHOT_DT,

party_all.ROW_LOADED_DATE_DRVD AS SRC_LOAD_DT_TIME,

party_all.STATUS_DATE_ORG AS STATUS_DATE_ORG,

IF (${MACRO_ADR1} = 'N' AND ${MACRO_CITY_TOWN_NAME} = 'N' AND ${MACRO_POSTAL_CODE} = 'N' AND ${MACRO_PROVINCE_CODE} = 'N' AND party_all.SRC_KEY_PARTY_ID = p.SRC_KEY_PARTY_ID, 'N', 'Y') AS CHANGE_ADDRESS_IND


FROM ${RLOB_WORK_DATABASE}.TARGET_MP_PARTY_ALL party_all


LEFT JOIN ${MACRO_PREV_PARTY_ADDRESS} p

ON safe_concat(party_all.SRC_KEY_PARTY_ID,'-','1','-',year_month_day_time(party_all.STATUS_DATE_ORG)) = p.SRC_KEY_PARTY_ADDR_ID

WHERE (party_all.PARTY_ROLE = 'BORROWER' OR  party_all.PARTY_ROLE = 'SOLICITOR FIRM')) a 

WHERE a.CHANGE_ADDRESS_IND = 'Y') aaa WHERE RN = 1

) party_addr

;


DROP TABLE IF EXISTS TARGET_MP_SRC_ORIGINATION_DATA;

CREATE TABLE TARGET_MP_SRC_ORIGINATION_DATA AS

SELECT DISTINCT

      cred_appl_full.CRED_APPL_MPL_ID

     ,cred_appl_full.MORTGAGE_NUM

     ,cred_appl_full.LAST_STATUS_CNCL_IND

     ,cred_appl_full.HPP_RELATED_APPL_NUM

     ,cred_appl_full.SNAPSHOT_DT

     ,cred_appl_full.SRC_SNAPSHOT_DT 

     ,cred_appl_full.APPL_NUM

     ,cred_appl_full.SRC_LOAD_DT_TIME

     ,cred_appl_full.LOAD_DT_TIME

     ,cred_appl_full.SRC_SYSTEM_CODE

     ,cred_appl_full.APPL_DT

     ,cred_appl_full.NUM_OF_RENTAL_PROPERTIES

     ,cred_appl_full.SRC_KEY_CRED_APPL_MPL_FULL_ID AS SRC_KEY_CRED_APPL_MPL_ID

     ,beacon_score.CRED_SCORE AS BEACON_CRED_SCORE

     ,beacon_score.CRED_SRC AS CRED_SRC

     ,beacon_score.CRED_SCORE_DT AS BEACON_CRED_SCORE_DT

     ,CAST(NULL AS SMALLINT) AS BNI_CRED_SCORE

     ,CAST(NULL AS TIMESTAMP) AS BNI_CRED_SCORE_DT

     ,adj_meas.TDSR

     ,security.NON_CONFORM_PROP_IND


FROM ${MACRO_CRED_APPL_MPL_FULL} cred_appl_full


LEFT JOIN ${RLOB_DATABASE}.CRED_APPL_MPL_PARTY_REL party_rel

  ON  cred_appl_full.CRED_APPL_MPL_ID = party_rel.CRED_APPL_MPL_ID

  AND cred_appl_full.APPL_STATUS_DT = party_rel.CA_APPL_STATUS_DT

  AND cred_appl_full.SRC_SYSTEM_CODE = party_rel.SRC_SYSTEM_CODE

  AND party_rel.PARTY_ROLE = 'BORROWER'

  AND party_rel.REL_RANK = 1


LEFT JOIN ${RLOB_DATABASE}.CREDIT_APPLICATION_MPL_SCORE beacon_score

   ON  party_rel.CRED_APPL_MPL_ID = beacon_score.CRED_APPL_MPL_ID

   AND party_rel.APPL_STATUS_DT = beacon_score.APPL_STATUS_DT

   AND party_rel.PARTY_ID = beacon_score.PARTY_ID

   AND party_rel.SRC_SYSTEM_CODE = beacon_score.SRC_SYSTEM_CODE

   AND beacon_score.SCORE_TYPE='BEACON'


LEFT JOIN ${RLOB_DATABASE}.ADJUDICATION adj

  ON  cred_appl_full.CRED_APPL_MPL_ID = adj.CRED_APPL_MPL_ID

   AND cred_appl_full.APPL_STATUS_DT = adj.CA_APPL_STATUS_DT

   AND cred_appl_full.SRC_SYSTEM_CODE = adj.SRC_SYSTEM_CODE


LEFT JOIN ${RLOB_DATABASE}.ADJUDICATION_MEASUREMENT adj_meas

  ON  adj.ADJUD_ID = adj_meas.ADJUD_ID

   AND adj.SRC_SYSTEM_CODE = adj_meas.SRC_SYSTEM_CODE


LEFT JOIN ${RLOB_DATABASE}.SECURITY_ORIGINATION security

   ON  cred_appl_full.CRED_APPL_MPL_ID = security.CRED_APPL_MPL_ID

   AND cred_appl_full.APPL_STATUS_DT = security.CA_APPL_STATUS_DT

   AND cred_appl_full.SRC_SYSTEM_CODE = security.SRC_SYSTEM_CODE

;


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