{{
config(
materialized = 'incremental',
unique_key = 'ROW_WID',
comment='Warehouse Layer WC_CAR_PRODUCT_D',
tags = ['analytics']
)
}}
with
PRODUCT_DELETED AS (
select * from {{ ref('CAR_PRODUCT_DELETED') }}
),
CAR_INCR as (
select * from {{ ref('CAR_Incr') }}
),
APPLICATION_PRODUCT as (
select * from {{ ref('curfl_Application_Product') }} ap
),
APPLICATION as (
select
*
from {{ ref('curfl_Application') }} a
{% if is_incremental() %}
inner join CAR_INCR oi on a.ROW_ID = oi.ROW_ID
{% endif %}
),
WC_CAR_PRODUCT_DS as (
Select
{{ generate_surrogate_key(['1', 'APPLICATION_PRODUCT.ROW_ID']) }} AS ROW_WID,
{{ var('etl_proc_wid') }} AS ETL_PROC_WID,
'1' as DATASOURCE_NUM_ID,
APPLICATION_PRODUCT.ROW_ID as INTEGRATION_ID,
REPAY_OPTION,
LOAN_PURPOSE,
PROD_INTEREST_RATE,
PROD_INTRO_RATE,
COMPARISON_RATE,
REPAY_AMT,
REPAY_AMT_INT_ONLY,
INT_RATE,
FUNDED_DT,
ASSET_NUM,
MAX_TERM,
APPLICATION_PRODUCT. APPLICATION_FEE,
SVR_DISCOUNT_AMT as DISCOUNT_RATE
From APPLICATION_PRODUCT
inner join APPLICATION on APPLICATION_PRODUCT.ACTIVITY_ID = APPLICATION.PR_ACT_ID
where APPLICATION.STG_NAME <> 'Being Prepared' AND APPLICATION.STG_NAME <> 'Lodged for QA'
AND (APPLICATION.STG_NAME <> 'Not Proceeding' OR APPLICATION.RECEIVED_DT IS NOT NULL)
AND APPLICATION.TRAIL_TRANSFER_FLG = 'N'
AND (NVL(APPLICATION_PRODUCT.CONSIDERED_FLG, 'N') <> 'Y' OR APPLICATION_PRODUCT.SELECTED_FLG = 'Y')
Union
Select
ROW_WID,
ETL_PROC_WID,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
REPAY_OPTION,
LOAN_PURPOSE,
PROD_INTEREST_RATE,
PROD_INTRO_RATE,
COMPARISON_RATE,
REPAY_AMT,
REPAY_AMT_INT_ONLY,
INT_RATE,
FUNDED_DT,
ASSET_NUM,
MAX_TERM,
APPLICATION_FEE,
DISCOUNT_RATE
From PRODUCT_DELETED
)
select
*
from WC_CAR_PRODUCT_DS
{{
config(
materialized = 'incremental',
unique_key = 'ROW_WID',
comment='Warehouse Layer WC_CAR_PRODUCT_D',
tags = ['analytics']
)
}}
with
PRODUCT_DELETED AS (
select * from {{ ref('CAR_PRODUCT_DELETED') }}
),
CAR_INCR as (
select * from {{ ref('CAR_Incr') }}
),
APPLICATION_PRODUCT as (
select * from {{ ref('curfl_Application_Product') }} ap
),
APPLICATION as (
select
*
from {{ ref('curfl_Application') }} a
{% if is_incremental() %}
inner join CAR_INCR oi on a.ROW_ID = oi.ROW_ID
{% endif %}
),
WC_CAR_PRODUCT_DS as (
Select
{{ generate_surrogate_key(['1', 'APPLICATION_PRODUCT.ROW_ID']) }} AS ROW_WID,
{{ var('etl_proc_wid') }} AS ETL_PROC_WID,
'1' as DATASOURCE_NUM_ID,
APPLICATION_PRODUCT.ROW_ID as INTEGRATION_ID,
REPAY_OPTION,
LOAN_PURPOSE,
PROD_INTEREST_RATE,
PROD_INTRO_RATE,
COMPARISON_RATE,
REPAY_AMT,
REPAY_AMT_INT_ONLY,
INT_RATE,
FUNDED_DT,
ASSET_NUM,
MAX_TERM,
APPLICATION_PRODUCT. APPLICATION_FEE,
SVR_DISCOUNT_AMT as DISCOUNT_RATE
From APPLICATION_PRODUCT
inner join APPLICATION on APPLICATION_PRODUCT.ACTIVITY_ID = APPLICATION.PR_ACT_ID
where APPLICATION.STG_NAME <> 'Being Prepared' AND APPLICATION.STG_NAME <> 'Lodged for QA'
AND (APPLICATION.STG_NAME <> 'Not Proceeding' OR APPLICATION.RECEIVED_DT IS NOT NULL)
AND APPLICATION.TRAIL_TRANSFER_FLG = 'N'
AND (NVL(APPLICATION_PRODUCT.CONSIDERED_FLG, 'N') <> 'Y' OR APPLICATION_PRODUCT.SELECTED_FLG = 'Y')
Union
Select
ROW_WID,
ETL_PROC_WID,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
REPAY_OPTION,
LOAN_PURPOSE,
PROD_INTEREST_RATE,
PROD_INTRO_RATE,
COMPARISON_RATE,
REPAY_AMT,
REPAY_AMT_INT_ONLY,
INT_RATE,
FUNDED_DT,
ASSET_NUM,
MAX_TERM,
APPLICATION_FEE,
DISCOUNT_RATE
From PRODUCT_DELETED
)
select
*
from WC_CAR_PRODUCT_DS