Delta
-- Sample monthly delta extract of ACCOUNT_MPL data
-- from EDH2.0 TARGET data source for mortgage performance.
-- Subquery to extract Target accounts
-- for the current snapshot period.
with target_extract as (
select distinct
-- Key elements
am.mortgage_num
-- Base extract filter elements.
-- Include here for debugging even if they
-- are not needed to populate the RLOB entity.
,current_principal_bal
,effective_date_of_discharge_org
,line_of_credit_bal
,mortgage_product
,am.proc_dt_drvd
,servicing_status
,sub_product1
-- Remaining elements
,access_level
,amortization
,application_date_org
,assumption_date_org
,brokerage_house
,channel
,commitment_amt
,current_appraised_amt
,current_legal_axn_code
,current_term_of_mortgage_mon
,date_of_funding_org
,date_of_last_conversion_org
,date_of_last_discharge_org
,date_of_last_early_renewal_org
,date_of_last_renewal_org
,dmp_counter
,equ_flag
,gvs_flag
,imm_flag
,int_rate
,investor_num
,last_dmp_event_date
,last_remediation_event_date_org
,legal_axn_status_change_date_org
,legal_status
,lender
,am.loan_type
,loc_limit
,loc_minimum_pymnt
,maturity_date_org
,monthly_total_pymnt
,mortgage_position
,mortgage_status
,mortgage_type
,next_pymnt_date_org
,num_of_late_pymnts
,original_adjustment_org
,original_appraisal_date_org
,original_appraised_value
,outstanding_loc_amt_in_arrears
,paid_to_date_org
,principal_int_pymnt
,prop_address_postal_code
,proposed_date_of_advance_org
,pymnt_frequency
,remaining_amortization_mons
,remaining_term_mons
,remediation_counter
,renewal_type
,revised_original_amortization
,row_loaded_date_drvd
,sale_price
,special_program_indicator
,sub_product2
,total_arrears
,total_npna_arrears
--v_servicing attributes
,ytd_reg_prin
,ytd_int_paid
,ytd_prepay_made
,mtd_prin
--------------
--[SC]. TFS 89962 - TARGET values for Mortgages for YTD Payment, YTD Interest and Prepayments
,case when upper(trim(servicing_status)) <> 'DISCHARGED' then nvl(s.ytd_reg_prin,0)
when upper(trim(servicing_status)) = 'DISCHARGED' then
case when cast(YEAR(FROM_UNIXTIME(unix_timestamp('2023-09-30 00:00:00.0'))) as integer) -
cast(YEAR(FROM_UNIXTIME(unix_timestamp(effective_date_of_discharge_org))) as integer) > 0 then 0
else nvl(acct_mpl.cal_yr_to_dt_prin_paid_amt,0) end
end as cal_yr_to_dt_prin_paid_amt
,case when upper(trim(servicing_status)) = 'DISCHARGED'
and cast(YEAR(FROM_UNIXTIME(unix_timestamp('2023-09-30 00:00:00.0'))) as integer) -
cast(YEAR(FROM_UNIXTIME(unix_timestamp(effective_date_of_discharge_org))) as integer) > 0 then 0 else nvl(s.ytd_int_paid,0) end as cal_yr_to_dt_intrst_paid_amt
,case when upper(trim(servicing_status)) <> 'DISCHARGED' then nvl(s.ytd_prepay_made,0)
when upper(trim(servicing_status)) = 'DISCHARGED' then
case when cast(YEAR(FROM_UNIXTIME(unix_timestamp('2023-09-30 00:00:00.0'))) as integer) -
cast(YEAR(FROM_UNIXTIME(unix_timestamp(effective_date_of_discharge_org))) as integer) > 0 then 0
else nvl(s.ytd_prepay_made,0) + (nvl(am.current_principal_bal,0) - nvl(acct_mpl.mortgage_bal,0)) end
end as cal_yr_to_dt_add_paid_amt
--[SC]. TFS 89962 - TARGET values for Mortgages for YTD Payment, YTD Interest and Prepayments
from z4_rlob_land_mpl.v_mortgage_account_management am left join (
select cast(trunc(mno) as bigint) as mortgage_num,loan_type
,cast(proc_dt_drvd as integer) as proc_dt_drvd
,ytd_reg_prin
,ytd_int_paid
,ytd_prepay_made
,mtd_prin
from z4_rlob_land_mpl.v_servicing
where cast(proc_dt_drvd as integer) = 20230930 --EOM record
and upper(trim(loan_type)) = 'MORTGAGE'
)s
on(am.mortgage_num = s.mortgage_num and am.proc_dt_drvd = s.proc_dt_drvd)
--[SC]. TFS 89962 - TARGET values for Mortgages for YTD Payment, YTD Interest and Prepayments
--Fetch the cal_yr_to_dt_prin_paid_amt for the previous snapshot_dt which will be used in the calculation
--of current month cal_yr_to_dt_prin_paid_amt for discharged mortgages
--Fetch the mortgage_bal for the previous snapshot_dt which will be used in the calculation
--of current month cal_yr_to_dt_add_paid_amt for discharged mortgages
left join (
select appl_num,appl_dt
,cal_yr_to_dt_prin_paid_amt
,mortgage_bal
from z4_rlob_prod.account_mpl
where src_system_code = 'TARGET_MP'
and snapshot_dt = 20230831
) acct_mpl
on(am.mortgage_num = cast(acct_mpl.appl_num as bigint))
where
-- Filter on proc_dt to extract records associated
-- with the current month-end snapshot
(am.proc_dt_drvd >= 20230901 and am.proc_dt_drvd < 20231001)
and (
-- where there is an outstanding balance on the account
-- or there is a HELOC product associated to the account
((current_principal_bal + line_of_credit_bal) > 0.00)
or
upper(trim(mortgage_product)) = 'LOC'
or
upper(trim(sub_product1)) = 'IMO'
)
and (
-- the account is an active or interim state; or
-- the account is discharged, but there is a remaining balance; or
-- the account was discharged during the reporting period
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 (
effective_date_of_discharge_org >= cast('2023-09-01 00:00:00.0' as timestamp)
and
effective_date_of_discharge_org < cast('2023-10-01 00:00:00.0' as timestamp)
)
)
)
)
-- Main query
,main_query as (
select distinct * from target_extract
)
-- Test for duplicate primary keys
,test_for_dups as (
select mortgage_num
,count (*)
from main_query
group by
mortgage_num
having count(*) > 1
)
-- Uncomment to execute main query
select * from main_query
-- Uncomment to test for duplicates
-- select * from test_for_dups
-- Uncomment to order and limit results for testing
order by
mortgage_num
-- limit 100
;
Comments
Post a Comment