Untitled
unknown
plain_text
2 months ago
121 kB
2
Indexable
create table reporting.nov_30th_2024 as
WITH `loan_details` AS (
WITH `lpd` AS (
SELECT
`_q_0`.`created_on` AS `created_on`,
`_q_0`.`created_by` AS `created_by`,
`_q_0`.`updated_on` AS `updated_on`,
`_q_0`.`updated_by` AS `updated_by`,
`_q_0`.`deleted_on` AS `deleted_on`,
`_q_0`.`loan_process_detail_id` AS `loan_process_detail_id`,
`_q_0`.`loan_stage` AS `loan_stage`,
`_q_0`.`loan_state` AS `loan_state`,
`_q_0`.`loan_sequence_no` AS `loan_sequence_no`,
`_q_0`.`loan_type` AS `loan_type`,
`_q_0`.`external_loan_id` AS `external_loan_id`,
`_q_0`.`comments` AS `comments`,
`_q_0`.`loan_info_id` AS `loan_info_id`,
`_q_0`.`loan_economics_id` AS `loan_economics_id`,
`_q_0`.`loan_detail_id` AS `loan_detail_id`,
`_q_0`.`loan_type_id` AS `loan_type_id`,
`_q_0`.`errors_pending` AS `errors_pending`,
`_q_0`.`waivers_pending` AS `waivers_pending`,
`_q_0`.`exceptions_pending` AS `exceptions_pending`,
`_q_0`.`enquiries_pending` AS `enquiries_pending`,
`_q_0`.`docs_pending` AS `docs_pending`,
`_q_0`.`fields_pending` AS `fields_pending`,
`_q_0`.`loan_condominium_id` AS `loan_condominium_id`,
`_q_0`.`loan_credit_id` AS `loan_credit_id`,
`_q_0`.`workflow_state` AS `workflow_state`,
`_q_0`.`loan_process_type` AS `loan_process_type`,
`_q_0`.`assigned_to` AS `assigned_to`,
`_q_0`.`role_of_assignee` AS `role_of_assignee`,
`_q_0`.`qc_required` AS `qc_required`,
`_q_0`.`loan_sizer_type` AS `loan_sizer_type`,
`_q_0`.`on_hold_by` AS `on_hold_by`,
`_q_0`.`tape_to_file_status` AS `tape_to_file_status`,
`_q_0`.`dd_report_status` AS `dd_report_status`,
`_q_0`.`loan_config_id` AS `loan_config_id`,
`_q_0`.`loan_summary_id` AS `loan_summary_id`,
`_q_0`.`tape_to_file_assignee` AS `tape_to_file_assignee`,
`_q_0`.`rate_lock_expiry_date` AS `rate_lock_expiry_date`,
`_q_0`.`rate_lock_sync_status` AS `rate_lock_sync_status`,
`_q_0`.`sizer_rate_lock_sync_status` AS `sizer_rate_lock_sync_status`,
`_q_0`.`sizer_rate_lock_expiry_date` AS `sizer_rate_lock_expiry_date`,
`_q_0`.`row_num` AS `row_num`
FROM (
SELECT
`lpd`.`payload`.`created_on` AS `created_on`,
`lpd`.`payload`.`created_by` AS `created_by`,
`lpd`.`payload`.`updated_on` AS `updated_on`,
`lpd`.`payload`.`updated_by` AS `updated_by`,
`lpd`.`payload`.`deleted_on` AS `deleted_on`,
`lpd`.`payload`.`loan_process_detail_id` AS `loan_process_detail_id`,
`lpd`.`payload`.`loan_stage` AS `loan_stage`,
`lpd`.`payload`.`loan_state` AS `loan_state`,
`lpd`.`payload`.`loan_sequence_no` AS `loan_sequence_no`,
`lpd`.`payload`.`loan_type` AS `loan_type`,
`lpd`.`payload`.`external_loan_id` AS `external_loan_id`,
`lpd`.`payload`.`comments` AS `comments`,
`lpd`.`payload`.`loan_info_id` AS `loan_info_id`,
`lpd`.`payload`.`loan_economics_id` AS `loan_economics_id`,
`lpd`.`payload`.`loan_detail_id` AS `loan_detail_id`,
`lpd`.`payload`.`loan_type_id` AS `loan_type_id`,
`lpd`.`payload`.`errors_pending` AS `errors_pending`,
`lpd`.`payload`.`waivers_pending` AS `waivers_pending`,
`lpd`.`payload`.`exceptions_pending` AS `exceptions_pending`,
`lpd`.`payload`.`enquiries_pending` AS `enquiries_pending`,
`lpd`.`payload`.`docs_pending` AS `docs_pending`,
`lpd`.`payload`.`fields_pending` AS `fields_pending`,
`lpd`.`payload`.`loan_condominium_id` AS `loan_condominium_id`,
`lpd`.`payload`.`loan_credit_id` AS `loan_credit_id`,
`lpd`.`payload`.`workflow_state` AS `workflow_state`,
`lpd`.`payload`.`loan_process_type` AS `loan_process_type`,
`lpd`.`payload`.`assigned_to` AS `assigned_to`,
`lpd`.`payload`.`role_of_assignee` AS `role_of_assignee`,
`lpd`.`payload`.`qc_required` AS `qc_required`,
`lpd`.`payload`.`loan_sizer_type` AS `loan_sizer_type`,
`lpd`.`payload`.`on_hold_by` AS `on_hold_by`,
`lpd`.`payload`.`tape_to_file_status` AS `tape_to_file_status`,
`lpd`.`payload`.`dd_report_status` AS `dd_report_status`,
`lpd`.`payload`.`loan_config_id` AS `loan_config_id`,
`lpd`.`payload`.`loan_summary_id` AS `loan_summary_id`,
`lpd`.`payload`.`tape_to_file_assignee` AS `tape_to_file_assignee`,
`lpd`.`payload`.`rate_lock_expiry_date` AS `rate_lock_expiry_date`,
`lpd`.`payload`.`rate_lock_sync_status` AS `rate_lock_sync_status`,
`lpd`.`payload`.`sizer_rate_lock_sync_status` AS `sizer_rate_lock_sync_status`,
`lpd`.`payload`.`sizer_rate_lock_expiry_date` AS `sizer_rate_lock_expiry_date`,
ROW_NUMBER() OVER (PARTITION BY `lpd`.`payload`.`loan_process_detail_id` ORDER BY
COALESCE(`lpd`.`payload`.`updated_on`, `lpd`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_loan_loan_process_detail` AS `lpd` /* np-toorak.bronze.tc_loan_loan_process_detail
*/
WHERE
`lpd`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`lpd`.`payload`.`updated_on`)
AND DATE(`lpd`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_0`
WHERE
`_q_0`.`row_num` = 1
), `li` AS (
SELECT
`_q_1`.`created_on` AS `created_on`,
`_q_1`.`created_by` AS `created_by`,
`_q_1`.`updated_on` AS `updated_on`,
`_q_1`.`updated_by` AS `updated_by`,
`_q_1`.`deleted_on` AS `deleted_on`,
`_q_1`.`loan_info_id` AS `loan_info_id`,
`_q_1`.`primary_loan_id` AS `primary_loan_id`,
`_q_1`.`loan_structure` AS `loan_structure`,
`_q_1`.`loan_purpose` AS `loan_purpose`,
`_q_1`.`cut_off_date` AS `cut_off_date`,
`_q_1`.`origination_date` AS `origination_date`,
`_q_1`.`first_payment_date_of_loan` AS `first_payment_date_of_loan`,
`_q_1`.`original_maturity_date` AS `original_maturity_date`,
`_q_1`.`recourse` AS `recourse`,
`_q_1`.`cross_collaterlization` AS `cross_collaterlization`,
`_q_1`.`extension_options` AS `extension_options`,
`_q_1`.`toorak_product` AS `toorak_product`,
`_q_1`.`pledge_of_equity` AS `pledge_of_equity`,
`_q_1`.`no_of_properties` AS `no_of_properties`,
`_q_1`.`condo_eligibility` AS `condo_eligibility`,
`_q_1`.`cashout_flag` AS `cashout_flag`,
`_q_1`.`borrower_proceeds` AS `borrower_proceeds`,
`_q_1`.`predominant_state` AS `predominant_state`,
`_q_1`.`predominant_property_type` AS `predominant_property_type`,
`_q_1`.`loan_term_monthly` AS `loan_term_monthly`,
`_q_1`.`calculated_condo_eligibility` AS `calculated_condo_eligibility`,
`_q_1`.`exit_strategy` AS `exit_strategy`,
`_q_1`.`existing_debt` AS `existing_debt`,
`_q_1`.`closing_costs` AS `closing_costs`,
`_q_1`.`exceptions` AS `exceptions`,
`_q_1`.`approved` AS `approved`,
`_q_1`.`comments` AS `comments`,
`_q_1`.`excess_first_loss` AS `excess_first_loss`,
`_q_1`.`interest_reserve_ir` AS `interest_reserve_ir`,
`_q_1`.`twelve_month_ir` AS `twelve_month_ir`,
`_q_1`.`annual_ds_initial_loan` AS `annual_ds_initial_loan`,
`_q_1`.`annual_ds_max_loan` AS `annual_ds_max_loan`,
`_q_1`.`appraiser_dy_as_stabilized` AS `appraiser_dy_as_stabilized`,
`_q_1`.`loan_term` AS `loan_term`,
`_q_1`.`required_interest_reserves_amount` AS `required_interest_reserves_amount`,
`_q_1`.`required_interest_reserves_months` AS `required_interest_reserves_months`,
`_q_1`.`recourse_string` AS `recourse_string`,
`_q_1`.`default_rate` AS `default_rate`,
`_q_1`.`default_rate_curable` AS `default_rate_curable`,
`_q_1`.`ein_or_ss_number` AS `ein_or_ss_number`,
`_q_1`.`expected_closing_date` AS `expected_closing_date`,
`_q_1`.`target_purchase_date` AS `target_purchase_date`,
`_q_1`.`ss_number` AS `ss_number`,
`_q_1`.`edited_on` AS `edited_on`,
`_q_1`.`edited_by` AS `edited_by`,
`_q_1`.`ir_funding_source` AS `ir_funding_source`,
`_q_1`.`is_permit_approval_and_full_plans_in_place` AS `is_permit_approval_and_full_plans_in_place`,
`_q_1`.`pricing_payment_due_date` AS `pricing_payment_due_date`,
`_q_1`.`takeout_partner` AS `takeout_partner`,
`_q_1`.`takeout_partner_status` AS `takeout_partner_status`,
`_q_1`.`credit_event` AS `credit_event`,
`_q_1`.`row_num` AS `row_num`
FROM (
SELECT
`li`.`payload`.`created_on` AS `created_on`,
`li`.`payload`.`created_by` AS `created_by`,
`li`.`payload`.`updated_on` AS `updated_on`,
`li`.`payload`.`updated_by` AS `updated_by`,
`li`.`payload`.`deleted_on` AS `deleted_on`,
`li`.`payload`.`loan_info_id` AS `loan_info_id`,
`li`.`payload`.`primary_loan_id` AS `primary_loan_id`,
`li`.`payload`.`loan_structure` AS `loan_structure`,
`li`.`payload`.`loan_purpose` AS `loan_purpose`,
`li`.`payload`.`cut_off_date` AS `cut_off_date`,
`li`.`payload`.`origination_date` AS `origination_date`,
`li`.`payload`.`first_payment_date_of_loan` AS `first_payment_date_of_loan`,
`li`.`payload`.`original_maturity_date` AS `original_maturity_date`,
`li`.`payload`.`recourse` AS `recourse`,
`li`.`payload`.`cross_collaterlization` AS `cross_collaterlization`,
`li`.`payload`.`extension_options` AS `extension_options`,
`li`.`payload`.`toorak_product` AS `toorak_product`,
`li`.`payload`.`pledge_of_equity` AS `pledge_of_equity`,
`li`.`payload`.`no_of_properties` AS `no_of_properties`,
`li`.`payload`.`condo_eligibility` AS `condo_eligibility`,
`li`.`payload`.`cashout_flag` AS `cashout_flag`,
`li`.`payload`.`borrower_proceeds` AS `borrower_proceeds`,
`li`.`payload`.`predominant_state` AS `predominant_state`,
`li`.`payload`.`predominant_property_type` AS `predominant_property_type`,
`li`.`payload`.`loan_term_monthly` AS `loan_term_monthly`,
`li`.`payload`.`calculated_condo_eligibility` AS `calculated_condo_eligibility`,
`li`.`payload`.`exit_strategy` AS `exit_strategy`,
`li`.`payload`.`existing_debt` AS `existing_debt`,
`li`.`payload`.`closing_costs` AS `closing_costs`,
`li`.`payload`.`exceptions` AS `exceptions`,
`li`.`payload`.`approved` AS `approved`,
`li`.`payload`.`comments` AS `comments`,
`li`.`payload`.`excess_first_loss` AS `excess_first_loss`,
`li`.`payload`.`interest_reserve_ir` AS `interest_reserve_ir`,
`li`.`payload`.`twelve_month_ir` AS `twelve_month_ir`,
`li`.`payload`.`annual_ds_initial_loan` AS `annual_ds_initial_loan`,
`li`.`payload`.`annual_ds_max_loan` AS `annual_ds_max_loan`,
`li`.`payload`.`appraiser_dy_as_stabilized` AS `appraiser_dy_as_stabilized`,
`li`.`payload`.`loan_term` AS `loan_term`,
`li`.`payload`.`required_interest_reserves_amount` AS `required_interest_reserves_amount`,
`li`.`payload`.`required_interest_reserves_months` AS `required_interest_reserves_months`,
`li`.`payload`.`recourse_string` AS `recourse_string`,
`li`.`payload`.`default_rate` AS `default_rate`,
`li`.`payload`.`default_rate_curable` AS `default_rate_curable`,
`li`.`payload`.`ein_or_ss_number` AS `ein_or_ss_number`,
`li`.`payload`.`expected_closing_date` AS `expected_closing_date`,
`li`.`payload`.`target_purchase_date` AS `target_purchase_date`,
`li`.`payload`.`ss_number` AS `ss_number`,
`li`.`payload`.`edited_on` AS `edited_on`,
`li`.`payload`.`edited_by` AS `edited_by`,
`li`.`payload`.`ir_funding_source` AS `ir_funding_source`,
`li`.`payload`.`is_permit_approval_and_full_plans_in_place` AS `is_permit_approval_and_full_plans_in_place`,
`li`.`payload`.`pricing_payment_due_date` AS `pricing_payment_due_date`,
`li`.`payload`.`takeout_partner` AS `takeout_partner`,
`li`.`payload`.`takeout_partner_status` AS `takeout_partner_status`,
`li`.`payload`.`credit_event` AS `credit_event`,
ROW_NUMBER() OVER (PARTITION BY `li`.`payload`.`loan_info_id` ORDER BY COALESCE(`li`.`payload`.`updated_on`,
`li`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_loan_loan_info` AS `li` /* np-toorak.bronze.tc_loan_loan_info */
WHERE
`li`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`li`.`payload`.`updated_on`)
AND DATE(`li`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_1`
WHERE
`_q_1`.`row_num` = 1
), `le` AS (
SELECT
`_q_2`.`created_on` AS `created_on`,
`_q_2`.`created_by` AS `created_by`,
`_q_2`.`updated_on` AS `updated_on`,
`_q_2`.`updated_by` AS `updated_by`,
`_q_2`.`deleted_on` AS `deleted_on`,
`_q_2`.`loan_economics_id` AS `loan_economics_id`,
`_q_2`.`financed_interest_reserve` AS `financed_interest_reserve`,
`_q_2`.`original_loan_amount` AS `original_loan_amount`,
`_q_2`.`original_maximum_loan_amount` AS `original_maximum_loan_amount`,
`_q_2`.`cut_off_date_loan_amount` AS `cut_off_date_loan_amount`,
`_q_2`.`cutoff_date_maximum_loan_amount` AS `cutoff_date_maximum_loan_amount`,
`_q_2`.`accrual_type` AS `accrual_type`,
`_q_2`.`total_budget_amount` AS `total_budget_amount`,
`_q_2`.`cash_out_amount` AS `cash_out_amount`,
`_q_2`.`current_loan_balance` AS `current_loan_balance`,
`_q_2`.`rate_type` AS `rate_type`,
`_q_2`.`arm_product_type` AS `arm_product_type`,
`_q_2`.`arm_index` AS `arm_index`,
`_q_2`.`gross_arm_margin` AS `gross_arm_margin`,
`_q_2`.`interest_only_period` AS `interest_only_period`,
`_q_2`.`original_monthly_pi_payment` AS `original_monthly_pi_payment`,
`_q_2`.`initial_rate_adjustment_period` AS `initial_rate_adjustment_period`,
`_q_2`.`initial_pay_adjustment_period` AS `initial_pay_adjustment_period`,
`_q_2`.`initial_periodic_cap` AS `initial_periodic_cap`,
`_q_2`.`subsequent_periodic_cap` AS `subsequent_periodic_cap`,
`_q_2`.`payment_adjustment_frequency` AS `payment_adjustment_frequency`,
`_q_2`.`rate_adjustment_frequency` AS `rate_adjustment_frequency`,
`_q_2`.`prepayment_penalty_months` AS `prepayment_penalty_months`,
`_q_2`.`prepay_penalty_type` AS `prepay_penalty_type`,
`_q_2`.`debt_service_coverage_ratio` AS `debt_service_coverage_ratio`,
`_q_2`.`insurance_and_amortization` AS `insurance_and_amortization`,
`_q_2`.`lifetime_max_rate` AS `lifetime_max_rate`,
`_q_2`.`borrower_liquidity` AS `borrower_liquidity`,
`_q_2`.`original_qualifying_payment_monthly` AS `original_qualifying_payment_monthly`,
`_q_2`.`qualifying_payment_in_reserves_monthly` AS `qualifying_payment_in_reserves_monthly`,
`_q_2`.`lifetime_min_rate` AS `lifetime_min_rate`,
`_q_2`.`subordinate_financing` AS `subordinate_financing`,
`_q_2`.`business_purpose_occupancy` AS `business_purpose_occupancy`,
`_q_2`.`rehab_amount` AS `rehab_amount`,
`_q_2`.`assignment_fee` AS `assignment_fee`,
`_q_2`.`interest_rate_as_of_cut_off_date` AS `interest_rate_as_of_cut_off_date`,
`_q_2`.`cost_to_loan_ratio` AS `cost_to_loan_ratio`,
`_q_2`.`financed_budget_amount` AS `financed_budget_amount`,
`_q_2`.`total_origination_and_discount_points` AS `total_origination_and_discount_points`,
`_q_2`.`interest_only` AS `interest_only`,
`_q_2`.`exist_debt` AS `exist_debt`,
`_q_2`.`hard_cost` AS `hard_cost`,
`_q_2`.`soft_cost` AS `soft_cost`,
`_q_2`.`arm_rounding_factor` AS `arm_rounding_factor`,
`_q_2`.`initial_interest_rate_up` AS `initial_interest_rate_up`,
`_q_2`.`initial_interest_rate_down` AS `initial_interest_rate_down`,
`_q_2`.`subsequent_interest_rate_up` AS `subsequent_interest_rate_up`,
`_q_2`.`subsequent_interest_rate_down` AS `subsequent_interest_rate_down`,
`_q_2`.`property_validation_type` AS `property_validation_type`,
`_q_2`.`borrower_is_llc` AS `borrower_is_llc`,
`_q_2`.`hoa_payment_frequency` AS `hoa_payment_frequency`,
`_q_2`.`liquid_cash_reserves` AS `liquid_cash_reserves`,
`_q_2`.`interest_reserve` AS `interest_reserve`,
`_q_2`.`life_rate_cap` AS `life_rate_cap`,
`_q_2`.`late_charge` AS `late_charge`,
`_q_2`.`arm_round_flag` AS `arm_round_flag`,
`_q_2`.`arm_look_back_days` AS `arm_look_back_days`,
`_q_2`.`late_charge_percentage` AS `late_charge_percentage`,
`_q_2`.`gross_amount_due_from_borrower_hud` AS `gross_amount_due_from_borrower_hud`,
`_q_2`.`include_out_of_pocket_budget_arltv` AS `include_out_of_pocket_budget_arltv`,
`_q_2`.`hoi_dwelling_coverage` AS `hoi_dwelling_coverage`,
`_q_2`.`flood_insurance_coverage` AS `flood_insurance_coverage`,
`_q_2`.`default_interest_rate_type` AS `default_interest_rate_type`,
`_q_2`.`row_num` AS `row_num`
FROM (
SELECT
`le`.`payload`.`created_on` AS `created_on`,
`le`.`payload`.`created_by` AS `created_by`,
`le`.`payload`.`updated_on` AS `updated_on`,
`le`.`payload`.`updated_by` AS `updated_by`,
`le`.`payload`.`deleted_on` AS `deleted_on`,
`le`.`payload`.`loan_economics_id` AS `loan_economics_id`,
`le`.`payload`.`financed_interest_reserve` AS `financed_interest_reserve`,
`le`.`payload`.`original_loan_amount` AS `original_loan_amount`,
`le`.`payload`.`original_maximum_loan_amount` AS `original_maximum_loan_amount`,
`le`.`payload`.`cut_off_date_loan_amount` AS `cut_off_date_loan_amount`,
`le`.`payload`.`cutoff_date_maximum_loan_amount` AS `cutoff_date_maximum_loan_amount`,
`le`.`payload`.`accrual_type` AS `accrual_type`,
`le`.`payload`.`total_budget_amount` AS `total_budget_amount`,
`le`.`payload`.`cash_out_amount` AS `cash_out_amount`,
`le`.`payload`.`current_loan_balance` AS `current_loan_balance`,
`le`.`payload`.`rate_type` AS `rate_type`,
`le`.`payload`.`arm_product_type` AS `arm_product_type`,
`le`.`payload`.`arm_index` AS `arm_index`,
`le`.`payload`.`gross_arm_margin` AS `gross_arm_margin`,
`le`.`payload`.`interest_only_period` AS `interest_only_period`,
`le`.`payload`.`original_monthly_pi_payment` AS `original_monthly_pi_payment`,
`le`.`payload`.`initial_rate_adjustment_period` AS `initial_rate_adjustment_period`,
`le`.`payload`.`initial_pay_adjustment_period` AS `initial_pay_adjustment_period`,
`le`.`payload`.`initial_periodic_cap` AS `initial_periodic_cap`,
`le`.`payload`.`subsequent_periodic_cap` AS `subsequent_periodic_cap`,
`le`.`payload`.`payment_adjustment_frequency` AS `payment_adjustment_frequency`,
`le`.`payload`.`rate_adjustment_frequency` AS `rate_adjustment_frequency`,
`le`.`payload`.`prepayment_penalty_months` AS `prepayment_penalty_months`,
`le`.`payload`.`prepay_penalty_type` AS `prepay_penalty_type`,
`le`.`payload`.`debt_service_coverage_ratio` AS `debt_service_coverage_ratio`,
`le`.`payload`.`insurance_and_amortization` AS `insurance_and_amortization`,
`le`.`payload`.`lifetime_max_rate` AS `lifetime_max_rate`,
`le`.`payload`.`borrower_liquidity` AS `borrower_liquidity`,
`le`.`payload`.`original_qualifying_payment_monthly` AS `original_qualifying_payment_monthly`,
`le`.`payload`.`qualifying_payment_in_reserves_monthly` AS `qualifying_payment_in_reserves_monthly`,
`le`.`payload`.`lifetime_min_rate` AS `lifetime_min_rate`,
`le`.`payload`.`subordinate_financing` AS `subordinate_financing`,
`le`.`payload`.`business_purpose_occupancy` AS `business_purpose_occupancy`,
`le`.`payload`.`rehab_amount` AS `rehab_amount`,
`le`.`payload`.`assignment_fee` AS `assignment_fee`,
`le`.`payload`.`interest_rate_as_of_cut_off_date` AS `interest_rate_as_of_cut_off_date`,
`le`.`payload`.`cost_to_loan_ratio` AS `cost_to_loan_ratio`,
`le`.`payload`.`financed_budget_amount` AS `financed_budget_amount`,
`le`.`payload`.`total_origination_and_discount_points` AS `total_origination_and_discount_points`,
`le`.`payload`.`interest_only` AS `interest_only`,
`le`.`payload`.`exist_debt` AS `exist_debt`,
`le`.`payload`.`hard_cost` AS `hard_cost`,
`le`.`payload`.`soft_cost` AS `soft_cost`,
`le`.`payload`.`arm_rounding_factor` AS `arm_rounding_factor`,
`le`.`payload`.`initial_interest_rate_up` AS `initial_interest_rate_up`,
`le`.`payload`.`initial_interest_rate_down` AS `initial_interest_rate_down`,
`le`.`payload`.`subsequent_interest_rate_up` AS `subsequent_interest_rate_up`,
`le`.`payload`.`subsequent_interest_rate_down` AS `subsequent_interest_rate_down`,
`le`.`payload`.`property_validation_type` AS `property_validation_type`,
`le`.`payload`.`borrower_is_llc` AS `borrower_is_llc`,
`le`.`payload`.`hoa_payment_frequency` AS `hoa_payment_frequency`,
`le`.`payload`.`liquid_cash_reserves` AS `liquid_cash_reserves`,
`le`.`payload`.`interest_reserve` AS `interest_reserve`,
`le`.`payload`.`life_rate_cap` AS `life_rate_cap`,
`le`.`payload`.`late_charge` AS `late_charge`,
`le`.`payload`.`arm_round_flag` AS `arm_round_flag`,
`le`.`payload`.`arm_look_back_days` AS `arm_look_back_days`,
`le`.`payload`.`late_charge_percentage` AS `late_charge_percentage`,
`le`.`payload`.`gross_amount_due_from_borrower_hud` AS `gross_amount_due_from_borrower_hud`,
`le`.`payload`.`include_out_of_pocket_budget_arltv` AS `include_out_of_pocket_budget_arltv`,
`le`.`payload`.`hoi_dwelling_coverage` AS `hoi_dwelling_coverage`,
`le`.`payload`.`flood_insurance_coverage` AS `flood_insurance_coverage`,
`le`.`payload`.`default_interest_rate_type` AS `default_interest_rate_type`,
ROW_NUMBER() OVER (PARTITION BY `le`.`payload`.`loan_economics_id` ORDER BY
COALESCE(`le`.`payload`.`updated_on`, `le`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_loan_loan_economics` AS `le` /* np-toorak.bronze.tc_loan_loan_economics */
WHERE
`le`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`le`.`payload`.`updated_on`)
AND DATE(`le`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_2`
WHERE
`_q_2`.`row_num` = 1
), `lc` AS (
SELECT
`_q_3`.`created_on` AS `created_on`,
`_q_3`.`created_by` AS `created_by`,
`_q_3`.`updated_on` AS `updated_on`,
`_q_3`.`updated_by` AS `updated_by`,
`_q_3`.`deleted_on` AS `deleted_on`,
`_q_3`.`loan_config_id` AS `loan_config_id`,
`_q_3`.`appraisal` AS `appraisal`,
`_q_3`.`street_view` AS `street_view`,
`_q_3`.`zillow` AS `zillow`,
`_q_3`.`trulia_crime` AS `trulia_crime`,
`_q_3`.`tape_to_file` AS `tape_to_file`,
`_q_3`.`due_diligence_party_id` AS `due_diligence_party_id`,
`_q_3`.`date_uploaded` AS `date_uploaded`,
`_q_3`.`email_sent_to_originator` AS `email_sent_to_originator`,
`_q_3`.`date_purchased` AS `date_purchased`,
`_q_3`.`rule_set_version` AS `rule_set_version`,
`_q_3`.`lat_analyst_party_id` AS `lat_analyst_party_id`,
`_q_3`.`lat_analyst_approver_party_id` AS `lat_analyst_approver_party_id`,
`_q_3`.`status` AS `status`,
`_q_3`.`comments` AS `comments`,
`_q_3`.`toorak_eligibility` AS `toorak_eligibility`,
`_q_3`.`custody_documents` AS `custody_documents`,
`_q_3`.`appraiser_list` AS `appraiser_list`,
`_q_3`.`rate_lock_period` AS `rate_lock_period`,
`_q_3`.`spoc_party_id` AS `spoc_party_id`,
`_q_3`.`servicer_party_id` AS `servicer_party_id`,
`_q_3`.`servicer_name` AS `servicer_name`,
`_q_3`.`sizer_rate_lock_period` AS `sizer_rate_lock_period`,
`_q_3`.`ttf_version_id` AS `ttf_version_id`,
`_q_3`.`closing_legal_firm` AS `closing_legal_firm`,
`_q_3`.`closing_escrow_company` AS `closing_escrow_company`,
`_q_3`.`wire_to_legal_or_escrow` AS `wire_to_legal_or_escrow`,
`_q_3`.`seller_party_id` AS `seller_party_id`,
`_q_3`.`enable_budget_review` AS `enable_budget_review`,
`_q_3`.`rate_locked_date` AS `rate_locked_date`,
`_q_3`.`sizer_rate_locked_date` AS `sizer_rate_locked_date`,
`_q_3`.`rate_locked_rate_sheet` AS `rate_locked_rate_sheet`,
`_q_3`.`row_num` AS `row_num`
FROM (
SELECT
`lc`.`payload`.`created_on` AS `created_on`,
`lc`.`payload`.`created_by` AS `created_by`,
`lc`.`payload`.`updated_on` AS `updated_on`,
`lc`.`payload`.`updated_by` AS `updated_by`,
`lc`.`payload`.`deleted_on` AS `deleted_on`,
`lc`.`payload`.`loan_config_id` AS `loan_config_id`,
`lc`.`payload`.`appraisal` AS `appraisal`,
`lc`.`payload`.`street_view` AS `street_view`,
`lc`.`payload`.`zillow` AS `zillow`,
`lc`.`payload`.`trulia_crime` AS `trulia_crime`,
`lc`.`payload`.`tape_to_file` AS `tape_to_file`,
`lc`.`payload`.`due_diligence_party_id` AS `due_diligence_party_id`,
`lc`.`payload`.`date_uploaded` AS `date_uploaded`,
`lc`.`payload`.`email_sent_to_originator` AS `email_sent_to_originator`,
`lc`.`payload`.`date_purchased` AS `date_purchased`,
`lc`.`payload`.`rule_set_version` AS `rule_set_version`,
`lc`.`payload`.`lat_analyst_party_id` AS `lat_analyst_party_id`,
`lc`.`payload`.`lat_analyst_approver_party_id` AS `lat_analyst_approver_party_id`,
`lc`.`payload`.`status` AS `status`,
`lc`.`payload`.`comments` AS `comments`,
`lc`.`payload`.`toorak_eligibility` AS `toorak_eligibility`,
`lc`.`payload`.`custody_documents` AS `custody_documents`,
`lc`.`payload`.`appraiser_list` AS `appraiser_list`,
`lc`.`payload`.`rate_lock_period` AS `rate_lock_period`,
`lc`.`payload`.`spoc_party_id` AS `spoc_party_id`,
`lc`.`payload`.`servicer_party_id` AS `servicer_party_id`,
`lc`.`payload`.`servicer_name` AS `servicer_name`,
`lc`.`payload`.`sizer_rate_lock_period` AS `sizer_rate_lock_period`,
`lc`.`payload`.`ttf_version_id` AS `ttf_version_id`,
`lc`.`payload`.`closing_legal_firm` AS `closing_legal_firm`,
`lc`.`payload`.`closing_escrow_company` AS `closing_escrow_company`,
`lc`.`payload`.`wire_to_legal_or_escrow` AS `wire_to_legal_or_escrow`,
`lc`.`payload`.`seller_party_id` AS `seller_party_id`,
`lc`.`payload`.`enable_budget_review` AS `enable_budget_review`,
`lc`.`payload`.`rate_locked_date` AS `rate_locked_date`,
`lc`.`payload`.`sizer_rate_locked_date` AS `sizer_rate_locked_date`,
`lc`.`payload`.`rate_locked_rate_sheet` AS `rate_locked_rate_sheet`,
ROW_NUMBER() OVER (PARTITION BY `lc`.`payload`.`loan_config_id` ORDER BY COALESCE(`lc`.`payload`.`updated_on`,
`lc`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_loan_loan_config` AS `lc` /* np-toorak.bronze.tc_loan_loan_config */
WHERE
`lc`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`lc`.`payload`.`updated_on`)
AND DATE(`lc`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_3`
WHERE
`_q_3`.`row_num` = 1
), `ld` AS (
SELECT
`_q_4`.`created_on` AS `created_on`,
`_q_4`.`created_by` AS `created_by`,
`_q_4`.`updated_on` AS `updated_on`,
`_q_4`.`updated_by` AS `updated_by`,
`_q_4`.`deleted_on` AS `deleted_on`,
`_q_4`.`loan_detail_id` AS `loan_detail_id`,
`_q_4`.`loan_process_detail_id` AS `loan_process_detail_id`,
`_q_4`.`loan_id_prefix` AS `loan_id_prefix`,
`_q_4`.`loan_id` AS `loan_id`,
`_q_4`.`originator_party_id` AS `originator_party_id`,
`_q_4`.`loan_config_id` AS `loan_config_id`,
`_q_4`.`loan_summary_id` AS `loan_summary_id`,
`_q_4`.`trade_id` AS `trade_id`,
`_q_4`.`toorak_loan_id` AS `toorak_loan_id`,
`_q_4`.`toorak_loan_id_sequence` AS `toorak_loan_id_sequence`,
`_q_4`.`funding_type` AS `funding_type`,
`_q_4`.`is_warehouse_funded` AS `is_warehouse_funded`,
`_q_4`.`originator_category_bucket` AS `originator_category_bucket`,
`_q_4`.`last_updated_on` AS `last_updated_on`,
`_q_4`.`row_num` AS `row_num`
FROM (
SELECT
`ld`.`payload`.`created_on` AS `created_on`,
`ld`.`payload`.`created_by` AS `created_by`,
`ld`.`payload`.`updated_on` AS `updated_on`,
`ld`.`payload`.`updated_by` AS `updated_by`,
`ld`.`payload`.`deleted_on` AS `deleted_on`,
`ld`.`payload`.`loan_detail_id` AS `loan_detail_id`,
`ld`.`payload`.`loan_process_detail_id` AS `loan_process_detail_id`,
`ld`.`payload`.`loan_id_prefix` AS `loan_id_prefix`,
`ld`.`payload`.`loan_id` AS `loan_id`,
`ld`.`payload`.`originator_party_id` AS `originator_party_id`,
`ld`.`payload`.`loan_config_id` AS `loan_config_id`,
`ld`.`payload`.`loan_summary_id` AS `loan_summary_id`,
`ld`.`payload`.`trade_id` AS `trade_id`,
`ld`.`payload`.`toorak_loan_id` AS `toorak_loan_id`,
`ld`.`payload`.`toorak_loan_id_sequence` AS `toorak_loan_id_sequence`,
`ld`.`payload`.`funding_type` AS `funding_type`,
`ld`.`payload`.`is_warehouse_funded` AS `is_warehouse_funded`,
`ld`.`payload`.`originator_category_bucket` AS `originator_category_bucket`,
`ld`.`payload`.`last_updated_on` AS `last_updated_on`,
ROW_NUMBER() OVER (PARTITION BY `ld`.`payload`.`loan_detail_id` ORDER BY COALESCE(`ld`.`payload`.`updated_on`,
`ld`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_loan_loan_detail` AS `ld` /* np-toorak.bronze.tc_loan_loan_detail */
WHERE
`ld`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`ld`.`payload`.`updated_on`)
AND DATE(`ld`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_4`
WHERE
`_q_4`.`row_num` = 1
)
SELECT DISTINCT
`ld`.`toorak_loan_id` AS `toorak_loan_id`,
`ld`.`originator_party_id` AS `originator_party_id`,
`lpd`.`loan_stage` AS `loan_stage`,
`lpd`.`loan_state` AS `loan_state`,
`lpd`.`loan_type` AS `loan_type`,
`li`.`primary_loan_id` AS `primary_loan_id`,
`li`.`loan_purpose` AS `loan_purpose`,
`li`.`toorak_product` AS `toorak_product`,
`li`.`no_of_properties` AS `no_of_properties`,
`le`.`interest_rate_as_of_cut_off_date` AS `interest_rate_as_of_cut_off_date`,
`le`.`original_loan_amount` AS `original_loan_amount`,
`le`.`cut_off_date_loan_amount` AS `cut_off_date_loan_amount`,
`lpd`.`on_hold_by` AS `on_hold_by`,
`le`.`original_maximum_loan_amount` AS `original_maximum_loan_amount`
FROM `lpd` AS `lpd`
LEFT JOIN `li` AS `li`
ON `li`.`loan_info_id` = `lpd`.`loan_info_id`
LEFT JOIN `le` AS `le`
ON `le`.`loan_economics_id` = `lpd`.`loan_economics_id`
LEFT JOIN `ld` AS `ld`
ON `ld`.`loan_process_detail_id` = `lpd`.`loan_process_detail_id`
LEFT JOIN `lc` AS `lc`
ON `lc`.`loan_config_id` = `ld`.`loan_config_id`
WHERE
`lpd`.`loan_state` IN ('Initial Review', 'Approved', 'Final Review', 'Due Diligence Review', 'Purchase Initiated')
AND (
`lpd`.`on_hold_by` IN ('null', 'NONE') OR `lpd`.`on_hold_by` IS NULL
)
AND NOT `ld`.`originator_party_id` IN ('8a558506-5d72-432d-92b1-5cb813fcbb7f',
'400ca061-2733-45af-b0b3-6c722d9b17d2', 'a1b81126-b896-400c-a1c6-f296e6b54c1c', 'd7287374-3bf7-463d-90c7-0854e708ee04',
'f953682b-6081-4860-8696-0d0005125bac', 'b9a08e5e-3acb-4cb8-a5ee-3003fc0efe6d')
), `property_details` AS (
WITH `ps` AS (
SELECT
`_q_5`.`created_on` AS `created_on`,
`_q_5`.`created_by` AS `created_by`,
`_q_5`.`updated_on` AS `updated_on`,
`_q_5`.`updated_by` AS `updated_by`,
`_q_5`.`deleted_on` AS `deleted_on`,
`_q_5`.`property_set_id` AS `property_set_id`,
`_q_5`.`loan_property_id` AS `loan_property_id`,
`_q_5`.`loan_property_order` AS `loan_property_order`,
`_q_5`.`loan_property_detail_id` AS `loan_property_detail_id`,
`_q_5`.`property_id` AS `property_id`,
`_q_5`.`has_errors` AS `has_errors`,
`_q_5`.`row_num` AS `row_num`
FROM (
SELECT
`ps`.`payload`.`created_on` AS `created_on`,
`ps`.`payload`.`created_by` AS `created_by`,
`ps`.`payload`.`updated_on` AS `updated_on`,
`ps`.`payload`.`updated_by` AS `updated_by`,
`ps`.`payload`.`deleted_on` AS `deleted_on`,
`ps`.`payload`.`property_set_id` AS `property_set_id`,
`ps`.`payload`.`loan_property_id` AS `loan_property_id`,
`ps`.`payload`.`loan_property_order` AS `loan_property_order`,
`ps`.`payload`.`loan_property_detail_id` AS `loan_property_detail_id`,
`ps`.`payload`.`property_id` AS `property_id`,
`ps`.`payload`.`has_errors` AS `has_errors`,
ROW_NUMBER() OVER (PARTITION BY `ps`.`payload`.`property_set_id` ORDER BY COALESCE(`ps`.`payload`.`updated_on`,
`ps`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_property_property_set` AS `ps` /* np-toorak.bronze.tc_property_property_set */
WHERE
`ps`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`ps`.`payload`.`updated_on`)
AND DATE(`ps`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_5`
WHERE
`_q_5`.`row_num` = 1
), `lpm` AS (
SELECT
`_q_6`.`created_on` AS `created_on`,
`_q_6`.`created_by` AS `created_by`,
`_q_6`.`updated_on` AS `updated_on`,
`_q_6`.`updated_by` AS `updated_by`,
`_q_6`.`deleted_on` AS `deleted_on`,
`_q_6`.`loan_property_detail_id` AS `loan_property_detail_id`,
`_q_6`.`loan_detail_id` AS `loan_detail_id`,
`_q_6`.`loan_status` AS `loan_status`,
`_q_6`.`loan_stage` AS `loan_stage`,
`_q_6`.`row_num` AS `row_num`
FROM (
SELECT
`lpm`.`payload`.`created_on` AS `created_on`,
`lpm`.`payload`.`created_by` AS `created_by`,
`lpm`.`payload`.`updated_on` AS `updated_on`,
`lpm`.`payload`.`updated_by` AS `updated_by`,
`lpm`.`payload`.`deleted_on` AS `deleted_on`,
`lpm`.`payload`.`loan_property_detail_id` AS `loan_property_detail_id`,
`lpm`.`payload`.`loan_detail_id` AS `loan_detail_id`,
`lpm`.`payload`.`loan_status` AS `loan_status`,
`lpm`.`payload`.`loan_stage` AS `loan_stage`,
ROW_NUMBER() OVER (PARTITION BY `lpm`.`payload`.`loan_property_detail_id` ORDER BY
COALESCE(`lpm`.`payload`.`updated_on`, `lpm`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_property_loan_property_map` AS `lpm` /*
np-toorak.bronze.tc_property_loan_property_map */
WHERE
`lpm`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`lpm`.`payload`.`updated_on`)
AND DATE(`lpm`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_6`
WHERE
`_q_6`.`row_num` = 1
), `p` AS (
SELECT
`_q_7`.`created_on` AS `created_on`,
`_q_7`.`created_by` AS `created_by`,
`_q_7`.`updated_on` AS `updated_on`,
`_q_7`.`updated_by` AS `updated_by`,
`_q_7`.`deleted_on` AS `deleted_on`,
`_q_7`.`property_id` AS `property_id`,
`_q_7`.`property_info_id` AS `property_info_id`,
`_q_7`.`property_location_id` AS `property_location_id`,
`_q_7`.`property_economics_id` AS `property_economics_id`,
`_q_7`.`property_summary_id` AS `property_summary_id`,
`_q_7`.`row_num` AS `row_num`
FROM (
SELECT
`p`.`payload`.`created_on` AS `created_on`,
`p`.`payload`.`created_by` AS `created_by`,
`p`.`payload`.`updated_on` AS `updated_on`,
`p`.`payload`.`updated_by` AS `updated_by`,
`p`.`payload`.`deleted_on` AS `deleted_on`,
`p`.`payload`.`property_id` AS `property_id`,
`p`.`payload`.`property_info_id` AS `property_info_id`,
`p`.`payload`.`property_location_id` AS `property_location_id`,
`p`.`payload`.`property_economics_id` AS `property_economics_id`,
`p`.`payload`.`property_summary_id` AS `property_summary_id`,
ROW_NUMBER() OVER (PARTITION BY `p`.`payload`.`property_id` ORDER BY COALESCE(`p`.`payload`.`updated_on`,
`p`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_property_property` AS `p` /* np-toorak.bronze.tc_property_property */
WHERE
`p`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`p`.`payload`.`updated_on`)
AND DATE(`p`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_7`
WHERE
`_q_7`.`row_num` = 1
), `pi` AS (
SELECT
`_q_8`.`created_on` AS `created_on`,
`_q_8`.`created_by` AS `created_by`,
`_q_8`.`updated_on` AS `updated_on`,
`_q_8`.`updated_by` AS `updated_by`,
`_q_8`.`deleted_on` AS `deleted_on`,
`_q_8`.`property_info_id` AS `property_info_id`,
`_q_8`.`property_type` AS `property_type`,
`_q_8`.`recent_property_valuation_date` AS `recent_property_valuation_date`,
`_q_8`.`property_acquisition_date` AS `property_acquisition_date`,
`_q_8`.`change_in_use_case` AS `change_in_use_case`,
`_q_8`.`pre_rehab_square_footage` AS `pre_rehab_square_footage`,
`_q_8`.`post_rehab_square_footage` AS `post_rehab_square_footage`,
`_q_8`.`occupied_percentage` AS `occupied_percentage`,
`_q_8`.`property_units` AS `property_units`,
`_q_8`.`square_footage` AS `square_footage`,
`_q_8`.`appraisal_vendor` AS `appraisal_vendor`,
`_q_8`.`valuation_source` AS `valuation_source`,
`_q_8`.`property_condition` AS `property_condition`,
`_q_8`.`is_purchased_with_debt` AS `is_purchased_with_debt`,
`_q_8`.`description` AS `description`,
`_q_8`.`scenario_type` AS `scenario_type`,
`_q_8`.`is_property_purchased_last_2_years` AS `is_property_purchased_last_2_years`,
`_q_8`.`is_entitled_land` AS `is_entitled_land`,
`_q_8`.`collateral_id` AS `collateral_id`,
`_q_8`.`appraisal_name` AS `appraisal_name`,
`_q_8`.`appraisal_state` AS `appraisal_state`,
`_q_8`.`appraisal_type` AS `appraisal_type`,
`_q_8`.`appraisal_certification` AS `appraisal_certification`,
`_q_8`.`property_valuation_type` AS `property_valuation_type`,
`_q_8`.`property_valuation_date` AS `property_valuation_date`,
`_q_8`.`toorval_id` AS `toorval_id`,
`_q_8`.`pre_rehab_units` AS `pre_rehab_units`,
`_q_8`.`post_rehab_units` AS `post_rehab_units`,
`_q_8`.`appraisal_expiration_date` AS `appraisal_expiration_date`,
`_q_8`.`row_num` AS `row_num`
FROM (
SELECT
`pi`.`payload`.`created_on` AS `created_on`,
`pi`.`payload`.`created_by` AS `created_by`,
`pi`.`payload`.`updated_on` AS `updated_on`,
`pi`.`payload`.`updated_by` AS `updated_by`,
`pi`.`payload`.`deleted_on` AS `deleted_on`,
`pi`.`payload`.`property_info_id` AS `property_info_id`,
`pi`.`payload`.`property_type` AS `property_type`,
`pi`.`payload`.`recent_property_valuation_date` AS `recent_property_valuation_date`,
`pi`.`payload`.`property_acquisition_date` AS `property_acquisition_date`,
`pi`.`payload`.`change_in_use_case` AS `change_in_use_case`,
`pi`.`payload`.`pre_rehab_square_footage` AS `pre_rehab_square_footage`,
`pi`.`payload`.`post_rehab_square_footage` AS `post_rehab_square_footage`,
`pi`.`payload`.`occupied_percentage` AS `occupied_percentage`,
`pi`.`payload`.`property_units` AS `property_units`,
`pi`.`payload`.`square_footage` AS `square_footage`,
`pi`.`payload`.`appraisal_vendor` AS `appraisal_vendor`,
`pi`.`payload`.`valuation_source` AS `valuation_source`,
`pi`.`payload`.`property_condition` AS `property_condition`,
`pi`.`payload`.`is_purchased_with_debt` AS `is_purchased_with_debt`,
`pi`.`payload`.`description` AS `description`,
`pi`.`payload`.`scenario_type` AS `scenario_type`,
`pi`.`payload`.`is_property_purchased_last_2_years` AS `is_property_purchased_last_2_years`,
`pi`.`payload`.`is_entitled_land` AS `is_entitled_land`,
`pi`.`payload`.`collateral_id` AS `collateral_id`,
`pi`.`payload`.`appraisal_name` AS `appraisal_name`,
`pi`.`payload`.`appraisal_state` AS `appraisal_state`,
`pi`.`payload`.`appraisal_type` AS `appraisal_type`,
`pi`.`payload`.`appraisal_certification` AS `appraisal_certification`,
`pi`.`payload`.`property_valuation_type` AS `property_valuation_type`,
`pi`.`payload`.`property_valuation_date` AS `property_valuation_date`,
`pi`.`payload`.`toorval_id` AS `toorval_id`,
`pi`.`payload`.`pre_rehab_units` AS `pre_rehab_units`,
`pi`.`payload`.`post_rehab_units` AS `post_rehab_units`,
`pi`.`payload`.`appraisal_expiration_date` AS `appraisal_expiration_date`,
ROW_NUMBER() OVER (PARTITION BY `pi`.`payload`.`property_info_id` ORDER BY
COALESCE(`pi`.`payload`.`updated_on`, `pi`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_property_property_info` AS `pi` /* np-toorak.bronze.tc_property_property_info */
WHERE
`pi`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`pi`.`payload`.`updated_on`)
AND DATE(`pi`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_8`
WHERE
`_q_8`.`row_num` = 1
), `pe` AS (
SELECT
`_q_9`.`created_on` AS `created_on`,
`_q_9`.`created_by` AS `created_by`,
`_q_9`.`updated_on` AS `updated_on`,
`_q_9`.`updated_by` AS `updated_by`,
`_q_9`.`deleted_on` AS `deleted_on`,
`_q_9`.`property_economics_id` AS `property_economics_id`,
`_q_9`.`original_as_is_appraisal_value` AS `original_as_is_appraisal_value`,
`_q_9`.`original_as_repaired_appraised_value` AS `original_as_repaired_appraised_value`,
`_q_9`.`purchase_price` AS `purchase_price`,
`_q_9`.`cost_basis` AS `cost_basis`,
`_q_9`.`gross_potential_rent` AS `gross_potential_rent`,
`_q_9`.`annual_property_taxes` AS `annual_property_taxes`,
`_q_9`.`insurance` AS `insurance`,
`_q_9`.`financed_budget_amount` AS `financed_budget_amount`,
`_q_9`.`rental_cashflow_ratio` AS `rental_cashflow_ratio`,
`_q_9`.`annual_hazard_insurance` AS `annual_hazard_insurance`,
`_q_9`.`annual_flood_insurance` AS `annual_flood_insurance`,
`_q_9`.`annual_hoa_fee` AS `annual_hoa_fee`,
`_q_9`.`third_party_valuation` AS `third_party_valuation`,
`_q_9`.`third_party_valuation_status` AS `third_party_valuation_status`,
`_q_9`.`third_party_valuation_property_value` AS `third_party_valuation_property_value`,
`_q_9`.`appraisal_reported_noi_ncf` AS `appraisal_reported_noi_ncf`,
`_q_9`.`closing_cost` AS `closing_cost`,
`_q_9`.`additional_eligible_cost` AS `additional_eligible_cost`,
`_q_9`.`borrower_paid_costs` AS `borrower_paid_costs`,
`_q_9`.`gross_condo_sellout_value` AS `gross_condo_sellout_value`,
`_q_9`.`market_rent_per_month` AS `market_rent_per_month`,
`_q_9`.`assignment_fees` AS `assignment_fees`,
`_q_9`.`declining_markets` AS `declining_markets`,
`_q_9`.`row_num` AS `row_num`
FROM (
SELECT
`pe`.`payload`.`created_on` AS `created_on`,
`pe`.`payload`.`created_by` AS `created_by`,
`pe`.`payload`.`updated_on` AS `updated_on`,
`pe`.`payload`.`updated_by` AS `updated_by`,
`pe`.`payload`.`deleted_on` AS `deleted_on`,
`pe`.`payload`.`property_economics_id` AS `property_economics_id`,
`pe`.`payload`.`original_as_is_appraisal_value` AS `original_as_is_appraisal_value`,
`pe`.`payload`.`original_as_repaired_appraised_value` AS `original_as_repaired_appraised_value`,
`pe`.`payload`.`purchase_price` AS `purchase_price`,
`pe`.`payload`.`cost_basis` AS `cost_basis`,
`pe`.`payload`.`gross_potential_rent` AS `gross_potential_rent`,
`pe`.`payload`.`annual_property_taxes` AS `annual_property_taxes`,
`pe`.`payload`.`insurance` AS `insurance`,
`pe`.`payload`.`financed_budget_amount` AS `financed_budget_amount`,
`pe`.`payload`.`rental_cashflow_ratio` AS `rental_cashflow_ratio`,
`pe`.`payload`.`annual_hazard_insurance` AS `annual_hazard_insurance`,
`pe`.`payload`.`annual_flood_insurance` AS `annual_flood_insurance`,
`pe`.`payload`.`annual_hoa_fee` AS `annual_hoa_fee`,
`pe`.`payload`.`third_party_valuation` AS `third_party_valuation`,
`pe`.`payload`.`third_party_valuation_status` AS `third_party_valuation_status`,
`pe`.`payload`.`third_party_valuation_property_value` AS `third_party_valuation_property_value`,
`pe`.`payload`.`appraisal_reported_noi_ncf` AS `appraisal_reported_noi_ncf`,
`pe`.`payload`.`closing_cost` AS `closing_cost`,
`pe`.`payload`.`additional_eligible_cost` AS `additional_eligible_cost`,
`pe`.`payload`.`borrower_paid_costs` AS `borrower_paid_costs`,
`pe`.`payload`.`gross_condo_sellout_value` AS `gross_condo_sellout_value`,
`pe`.`payload`.`market_rent_per_month` AS `market_rent_per_month`,
`pe`.`payload`.`assignment_fees` AS `assignment_fees`,
`pe`.`payload`.`declining_markets` AS `declining_markets`,
ROW_NUMBER() OVER (PARTITION BY `pe`.`payload`.`property_economics_id` ORDER BY
COALESCE(`pe`.`payload`.`updated_on`, `pe`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_property_property_economics` AS `pe` /*
np-toorak.bronze.tc_property_property_economics */
WHERE
`pe`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`pe`.`payload`.`updated_on`)
AND DATE(`pe`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_9`
WHERE
`_q_9`.`row_num` = 1
), `pu` AS (
SELECT
`_q_10`.`created_on` AS `created_on`,
`_q_10`.`created_by` AS `created_by`,
`_q_10`.`updated_on` AS `updated_on`,
`_q_10`.`updated_by` AS `updated_by`,
`_q_10`.`deleted_on` AS `deleted_on`,
`_q_10`.`property_unit_primary_id` AS `property_unit_primary_id`,
`_q_10`.`property_unit_id` AS `property_unit_id`,
`_q_10`.`property_unit_order` AS `property_unit_order`,
`_q_10`.`no_of_bathrooms` AS `no_of_bathrooms`,
`_q_10`.`no_of_bedrooms` AS `no_of_bedrooms`,
`_q_10`.`borrower_rent_estimate` AS `borrower_rent_estimate`,
`_q_10`.`property_id` AS `property_id`,
`_q_10`.`lease_status` AS `lease_status`,
`_q_10`.`market_rent_monthly` AS `market_rent_monthly`,
`_q_10`.`most_recent_lease_rent_monthly` AS `most_recent_lease_rent_monthly`,
`_q_10`.`in_place_lease_rent_monthly` AS `in_place_lease_rent_monthly`,
`_q_10`.`current_lease_term_monthly` AS `current_lease_term_monthly`,
`_q_10`.`lease_start_date` AS `lease_start_date`,
`_q_10`.`lease_end_date` AS `lease_end_date`,
`_q_10`.`monthly_lease` AS `monthly_lease`,
`_q_10`.`lease_duration_morethan_12_mos` AS `lease_duration_morethan_12_mos`,
`_q_10`.`lease_duration_lessthan_12_mos` AS `lease_duration_lessthan_12_mos`,
`_q_10`.`vacancy_status` AS `vacancy_status`,
`_q_10`.`unit_category` AS `unit_category`,
`_q_10`.`rental_characterization` AS `rental_characterization`,
`_q_10`.`percentage_occupied` AS `percentage_occupied`,
`_q_10`.`pre_rehab_units` AS `pre_rehab_units`,
`_q_10`.`post_rehab_units` AS `post_rehab_units`,
`_q_10`.`third_party_rent_range_amount` AS `third_party_rent_range_amount`,
`_q_10`.`row_num` AS `row_num`
FROM (
SELECT
`pu`.`payload`.`created_on` AS `created_on`,
`pu`.`payload`.`created_by` AS `created_by`,
`pu`.`payload`.`updated_on` AS `updated_on`,
`pu`.`payload`.`updated_by` AS `updated_by`,
`pu`.`payload`.`deleted_on` AS `deleted_on`,
`pu`.`payload`.`property_unit_primary_id` AS `property_unit_primary_id`,
`pu`.`payload`.`property_unit_id` AS `property_unit_id`,
`pu`.`payload`.`property_unit_order` AS `property_unit_order`,
`pu`.`payload`.`no_of_bathrooms` AS `no_of_bathrooms`,
`pu`.`payload`.`no_of_bedrooms` AS `no_of_bedrooms`,
`pu`.`payload`.`borrower_rent_estimate` AS `borrower_rent_estimate`,
`pu`.`payload`.`property_id` AS `property_id`,
`pu`.`payload`.`lease_status` AS `lease_status`,
`pu`.`payload`.`market_rent_monthly` AS `market_rent_monthly`,
`pu`.`payload`.`most_recent_lease_rent_monthly` AS `most_recent_lease_rent_monthly`,
`pu`.`payload`.`in_place_lease_rent_monthly` AS `in_place_lease_rent_monthly`,
`pu`.`payload`.`current_lease_term_monthly` AS `current_lease_term_monthly`,
`pu`.`payload`.`lease_start_date` AS `lease_start_date`,
`pu`.`payload`.`lease_end_date` AS `lease_end_date`,
`pu`.`payload`.`monthly_lease` AS `monthly_lease`,
`pu`.`payload`.`lease_duration_morethan_12_mos` AS `lease_duration_morethan_12_mos`,
`pu`.`payload`.`lease_duration_lessthan_12_mos` AS `lease_duration_lessthan_12_mos`,
`pu`.`payload`.`vacancy_status` AS `vacancy_status`,
`pu`.`payload`.`unit_category` AS `unit_category`,
`pu`.`payload`.`rental_characterization` AS `rental_characterization`,
`pu`.`payload`.`percentage_occupied` AS `percentage_occupied`,
`pu`.`payload`.`pre_rehab_units` AS `pre_rehab_units`,
`pu`.`payload`.`post_rehab_units` AS `post_rehab_units`,
`pu`.`payload`.`third_party_rent_range_amount` AS `third_party_rent_range_amount`,
ROW_NUMBER() OVER (PARTITION BY `pu`.`payload`.`property_unit_id` ORDER BY
COALESCE(`pu`.`payload`.`updated_on`, `pu`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_property_property_unit` AS `pu` /* np-toorak.bronze.tc_property_property_unit */
WHERE
`pu`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`pu`.`payload`.`updated_on`)
AND DATE(`pu`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_10`
WHERE
`_q_10`.`row_num` = 1
), `pl` AS (
SELECT
`_q_11`.`created_on` AS `created_on`,
`_q_11`.`created_by` AS `created_by`,
`_q_11`.`updated_on` AS `updated_on`,
`_q_11`.`updated_by` AS `updated_by`,
`_q_11`.`deleted_on` AS `deleted_on`,
`_q_11`.`property_location_id` AS `property_location_id`,
`_q_11`.`address_line_1` AS `address_line_1`,
`_q_11`.`address_line_2` AS `address_line_2`,
`_q_11`.`city` AS `city`,
`_q_11`.`state` AS `state`,
`_q_11`.`postal_code` AS `postal_code`,
`_q_11`.`country` AS `country`,
`_q_11`.`location_validation_status` AS `location_validation_status`,
`_q_11`.`duplicate_loan_ids` AS `duplicate_loan_ids`,
`_q_11`.`geo_location` AS `geo_location`,
`_q_11`.`row_num` AS `row_num`
FROM (
SELECT
`pl`.`payload`.`created_on` AS `created_on`,
`pl`.`payload`.`created_by` AS `created_by`,
`pl`.`payload`.`updated_on` AS `updated_on`,
`pl`.`payload`.`updated_by` AS `updated_by`,
`pl`.`payload`.`deleted_on` AS `deleted_on`,
`pl`.`payload`.`property_location_id` AS `property_location_id`,
`pl`.`payload`.`address_line_1` AS `address_line_1`,
`pl`.`payload`.`address_line_2` AS `address_line_2`,
`pl`.`payload`.`city` AS `city`,
`pl`.`payload`.`state` AS `state`,
`pl`.`payload`.`postal_code` AS `postal_code`,
`pl`.`payload`.`country` AS `country`,
`pl`.`payload`.`location_validation_status` AS `location_validation_status`,
`pl`.`payload`.`duplicate_loan_ids` AS `duplicate_loan_ids`,
`pl`.`payload`.`geo_location` AS `geo_location`,
ROW_NUMBER() OVER (PARTITION BY `pl`.`payload`.`property_location_id` ORDER BY
COALESCE(`pl`.`payload`.`updated_on`, `pl`.`payload`.`created_on`) DESC) AS `row_num`
FROM `np-toorak`.`bronze`.`tc_property_property_location` AS `pl` /*
np-toorak.bronze.tc_property_property_location */
WHERE
`pl`.`payload`.`deleted_on` IS NULL
AND CURRENT_DATE - 30 >= DATE(`pl`.`payload`.`updated_on`)
AND DATE(`pl`.`payload`.`updated_on`) >= '1970-01-01'
) AS `_q_11`
WHERE
`_q_11`.`row_num` = 1
)
SELECT DISTINCT
`lpm`.`loan_detail_id` AS `loan_detail_id`,
`lpm`.`loan_stage` AS `loan_stage`,
`pi`.`property_type` AS `property_type`,
`ps`.`loan_property_order` AS `loan_property_order`,
`pi`.`property_units` AS `property_units`,
`pl`.`city` AS `city`,
`pl`.`state` AS `state`,
`pl`.`address_line_1` AS `address_line_1`
FROM `ps` AS `ps`
LEFT JOIN `lpm` AS `lpm`
ON CAST(`lpm`.`loan_property_detail_id` AS INT64) = CAST(`ps`.`loan_property_detail_id` AS INT64)
LEFT JOIN `p` AS `p`
ON `p`.`property_id` = `ps`.`property_id`
LEFT JOIN `pi` AS `pi`
ON `p`.`property_info_id` = `pi`.`property_info_id`
LEFT JOIN `pe` AS `pe`
ON `p`.`property_economics_id` = `pe`.`property_economics_id`
LEFT JOIN `pu` AS `pu`
ON `p`.`property_id` = `pu`.`property_id`
LEFT JOIN `pl` AS `pl`
ON `p`.`property_location_id` = `pl`.`property_location_id`
WHERE
NOT `pi`.`property_type` IS NULL
)
SELECT
CURRENT_DATE - 30 AS `date`,
`ld`.`originator_party_id` AS `originator_party_id`,
`ld`.`toorak_loan_id` AS `toorak_loan_id`,
`ld`.`loan_stage` AS `loan_stage`,
`ld`.`loan_state` AS `loan_state`,
CAST(CASE
WHEN `ld`.`loan_type` = '30 Year Loan'
THEN 'DSCR'
WHEN `ld`.`loan_type` = 'Bridge Loan'
THEN 'Bridge'
ELSE `ld`.`loan_type`
END AS STRING) AS `loan_type`,
`ld`.`primary_loan_id` AS `originator_loan_number`,
`ld`.`loan_purpose` AS `loan_purpose`,
CASE
WHEN `ld`.`toorak_product` IN ('Ground Up', 'Ground-Up')
THEN 'Ground Up'
WHEN `ld`.`toorak_product` IN ('30 Yr SFR', '30 Year Rental')
THEN '30 Yr SFR'
WHEN `ld`.`toorak_product` IN ('Bridge', 'Rehab')
THEN 'Rehab'
WHEN `ld`.`toorak_product` IN ('SFR', '2+1 Rental', '2+1')
THEN 'SFR'
ELSE `ld`.`toorak_product`
END AS `toorak_product`,
CAST(`ld`.`no_of_properties` AS INT64) AS `no_of_properties`,
SAFE_CAST(REGEXP_REPLACE(`ld`.`interest_rate_as_of_cut_off_date`, ',|\\$', '') AS FLOAT64) AS
`interest_rate_as_of_cut_off_date`,
SAFE_CAST(REGEXP_REPLACE(`ld`.`original_loan_amount`, ',|\\$', '') AS FLOAT64) AS `original_loan_amount`,
SAFE_CAST(REGEXP_REPLACE(`ld`.`cut_off_date_loan_amount`, ',|\\$', '') AS FLOAT64) AS `cut_off_date_loan_amount`,
SAFE_CAST(REGEXP_REPLACE(`ld`.`original_maximum_loan_amount`, ',|\\$', '') AS FLOAT64) AS
`original_maximum_loan_amount`,
UPPER(`pd`.`property_type`) AS `property_type`,
CAST(`pd`.`property_units` AS INT64) AS `property_units`,
`pd`.`city` AS `city`,
`pd`.`state` AS `state`,
`pd`.`address_line_1` AS `address_line_1`,
CAST(CURRENT_TIMESTAMP() AS TIMESTAMP) AS `updated_at`
FROM `loan_details` AS `ld`
LEFT JOIN `property_details` AS `pd`
ON `ld`.`loan_stage` = `pd`.`loan_stage`
AND `ld`.`toorak_loan_id` = `pd`.`loan_detail_id`
/* LEFT JOIN gold.originator go */ /* ON ld.originator_party_id = go.originator_id */
WHERE
`ld`.`loan_stage` IN ('PRE', 'POST')
AND `pd`.`loan_property_order` = '1'
AND NOT lower(`ld`.`primary_loan_id`) LIKE '%test%' /* AND go.originator_name IS NOT NULL */
insert into ``sqlmesh__reporting`.`reporting__dhr_pipeline_loans__3181120715`
select * from reporting.nov_30th_2024Editor is loading...
Leave a Comment