USE oe_mf_staging
GO

/**********************************************************************************************
* Drop existing hud held loan table
**********************************************************************************************/
IF EXISTS(SELECT name FROM sysobjects WHERE  name = N'mf_hud_held_loan' AND type = 'U')
    DROP TABLE mf_hud_held_loan
GO

/**********************************************************************************************
* Select columns from stg_financing_instrument to insert into table
**********************************************************************************************/
SELECT DISTINCT
--primary key
 pk_financing_instrument_id = Cast(pk_financing_instrument_id as integer),
--foreign keys
 fk_dap_assisted_housing_code,
 fk_dap_section8_cntrct_number,
 fk_dap_soa_code,
 fk_dap_status_code,
 fk_fha_number,
 fk_financing_current_status_cd,
 fk_latest_claim_type_id,
 fk_loan_termination_reason_cd,
 fk_mars_status_code,
 fk_primary_loan_code,
 fk_property_id,
 fk_soa_code,
 fk_status_asgnd_by_field_off ,
   --tags
 tag_ad_field_off_status_desc,
 tag_fha_number_suffix,
 tag_fha_number_with_suffix,
 tag_mortgage_holder_id,
 tag_mortgage_originator,
 tag_mortgage_srvcr_curr_name,
 tag_mortgage_servicing_id,
 tag_original_holder_id , --ZBW: 3.27.2012
 tag_original_holder_name, --ZBW: 3.27.2012

--categories
 cat_dap_application_type,
 cat_dap_status,
 cat_financing_current_status,
 cat_financing_curr_status_type,
 cat_financing_type,
 cat_latest_claim_type,
 cat_loan_termination_reason,
 cat_major_program_type,
 cat_mddr_default_status,
 cat_soa_description,
 cat_soa_name,
 cat_status_asgnd_by_field_off,
 cat_underwriting_type = CAST(cat_underwriting_type as varchar(20)),
--metrics
 mtr_amortized_upb = MARS_CRNT_UNPAID_PRNCPL_AMT,
 mtr_dap_202_811_cap_rsvd_amt,
 mtr_dap_final_req_mortgage_amt,
 mtr_dap_init_req_mortgage_amt,
 mtr_dap_nonrevenue_unit_count,
 mtr_dap_revenue_unit_count,
 mtr_dap_section8_unit_count,
 mtr_default_count,
 mtr_default_count_early,
 mtr_default_count_recent,
 mtr_financing_unit_quantity,
 mtr_loan_age_months,
 mtr_loan_term_months,
 mtr_original_interest_rate,
 mtr_original_loan_amount,
 mtr_original_owner_equity,
--dates
 date_dap_202_811_cap_reserved,
 date_dap_constr_completion,
 date_dap_construction_start,
 date_dap_cost_certification,
 date_dap_final_endorsement,
 date_dap_initial_endorsement,
 date_endorsement,
 date_endorsement_year,
 date_final_endorsement,
 date_first_payment, 
 date_initial_endorsement,
 date_loan_maturity,
 date_loan_termination,
 date_mfcs_default,
--indicators
 ind_automatic_status_update,
 ind_claimed,
 ind_dap_converted_from_fomns,
 ind_dap_tax_credit,
 ind_dap_tax_exempt_bond,
 ind_is_221d3_loan,
 ind_is_221d4_loan,
 ind_is_236_loan,
 ind_is_active,
 ind_is_bmi_rate_loan,
 ind_is_co_insured_loan,
 ind_is_currently_hud_held,
 ind_is_hud_owned,
 ind_is_insured_loan,
 ind_is_mortgagee_in_poss_loan,
 ind_is_pipeline_loan,
 ind_is_primary_fha_loan,
 ind_is_refinanced_loan,
 ind_is_underserved_area_loan,
 ind_is_voluntary_early_payoff,
 ind_received_irp,
 ind_underwent_small_proj_proc
INTO mf_hud_held_loan
FROM stg_financing_instrument
WHERE fk_mars_status_code IS NOT NULL
AND	fk_mars_status_code <> 0
GO

ALTER TABLE mf_hud_held_loan ALTER COLUMN pk_financing_instrument_id int NOT NULL
GO
CREATE CLUSTERED INDEX mhhl1 ON mf_hud_held_loan(fk_property_id, pk_financing_instrument_id)
GO
ALTER TABLE mf_hud_held_loan ADD CONSTRAINT mhhlpk1 PRIMARY KEY  (pk_financing_instrument_id)
GO