Attachment_2_OPIIS_DED.pdf

US Department of Housing and Urban Development

Data Element Dictionary

06/30/2015

Table of Contents

1. Introduction

1.1. Purpose

1.2. Scope

1.3. System Overview

1.4. Data Mart Tables

1.4.1. Primary Reporting Tables

1.4.2. Secondary Reporting Tables

1.4.3. History Tables

1.4.4. Reference Tables

1.4.5. Utility Table

1.5. Data Element Dictionary Organization

1.5.1. Business Column Name

1.5.2. Physical Name

1.5.3. Data Type

1.5.4. Nulls Allowed

1.5.5. Definition

1.6. Data Mart Tables

1.7. New Table Additions

2. Dictionary

1. Introduction

This document presents a description of the data contained in the OPIIS 2.0 data mart. This document provides a comprehensive source of documentation for all data contained in the data mart, including the organizational structure of the data within the data mart as well as business definitions for each column.

1.1.Purpose

The purpose of this document is to provide direct-access data users with a comprehensive resource for understanding the data found in the OPIIS 2.0 data mart. The data element dictionary (DED) provides definitions and descriptions for each column found in the OPIIS 2.0 data mart.

As users make use of the data found in the data mart, they can refer to the DED to ensure that they are using and interpreting the data correctly.

1.2.Scope

This document covers all tables and columns included in the OPIIS 2.0 data mart. Though the OPIIS 2.0 data mart includes data from various multifamily source systems, only a subset of that source data is included in the data mart. Only the data columns actually included in the OPIIS 2.0 data mart are documented here. Please refer to the source systems’ DED’s for information regarding source system columns not included in the OPIIS 2.0 data mart.

1.3.System Overview

The OPIIS 2.0 data mart is a consolidated source of data compiled from HUD’s various multifamily systems. The OPIIS 2.0 data mart includes property-specific information related to loans, grants, assistance contracts, and annual financial statements, among other things.

Sources of the data within the OPIIS 2.0 data mart include the Financial Assessment Subsystem (FASS), Physical Assessment Subsystem (PASS), Real Estate Management System (REMS), Development Application Processing (DAP) System, and multifamily data from external sources.

1.4.Data Mart Tables

The OPIIS 2.0 data mart contains five types of tables: primary reporting tables, secondary reporting tables, history tables, reference tables, and utility tables. Figure 1 contains a comprehensive list of tables by table type found in the data mart. Each type is described in the following sections.

1.4.1. Primary Reporting Tables

Primary reporting tables contain characteristics relating to people, places, things, or events. The data in primary tables has value independent of other primary tables, and the data in primary tables may change frequently.

The primary reporting tables in the OPIIS 2.0 data mart will be used regularly by most users.

In general, these tables contain the most commonly used high-level fact data associated with a particular entity (e.g., property, contract, AFS, disposition, inspection, use restriction, etc.).

1.4.2. Secondary Reporting Tables

Secondary reporting tables contain detail-level data that is dependent upon a primary parent table for meaning. Secondary tables may have one or more records for each record in their parent tables.

The secondary reporting tables in the OPIIS 2.0 data mart include all of the annual financial statement (AFS) child tables. Each of these tables includes an AFS Id, Header Id, Project Id, and differing numbers of tags, metrics, and dates. These tables are dependent on primary AFS reporting tables. An AFS Id cannot exist in a child table without existing in a primary AFS table.

The contents of each table are based on the account drill path documentation provided by the FASS team.

The data in the secondary reporting tables is stored at a more granular level than the data contained in the primary AFS reporting tables. It is important to note that the consistency and validity of the data in these secondary tables is completely dependent upon the data found in the source system (FASS).

1.4.3. History Tables

History tables store data to track a sequence of actions or data changes. History tables may shadow one or more primary tables and record when changes are made to the data in those primary tables.

The OPIIS 2.0 data mart includes five history tables. Each history table contains a running history of a specific type of data associated with a property. For example, the mf_history_property_active table contains a history of the active/inactive status for each property in the data mart. Each time the active status for a property changes, a new record is added to this table.

1.4.4. Reference Tables

Reference tables contain data that only exists to characterize data in other tables. The data in reference tables usually changes infrequently.

There are approximately 80 reference tables in the OPIIS 2.0 data mart. These tables, also known as lookup tables, are provided as a means of looking up related information across multiple reporting tables. The reference tables typically consist of a primary key column as well as some sort of characteristic. The primary keys can be used to link the reference table to a reporting table containing the same column as a foreign key.

1.4.5. Utility Table

Utility tables track process states or queues, such as the last date/time an extract, transform, and load (ETL) process ran or a list of actions waiting to be processed. Utility tables are often independent; they tend to be unrelated to any other table.

The OPIIS 2.0 data mart has one utility table. This table tracks when the data in other tables in the data mart was last refreshed.

1.5.Data Element Dictionary Organization

The contents of the data element dictionary are organized alphabetically by table name. For each table in the data mart, the documentation provides the table name and a brief description of the table contents followed by a detailed list of all columns in the table with the business name, physical name, data type, nulls-allowed indicator, and business definition for each.

The columns are organized in the same order that they appear in the data mart. This column ordering convention organizes columns alphabetically within each data class, with the data classes in the following order: primary keys (data fields beginning with pk), foreign keys (fk, sequences)

1.5.1. Business Column Name

The business column name is simply the physical name translated into full words. In many cases, the business column name is almost identical to the physical name. This is because the physical names selected for the data mart were intended to be as descriptive as possible, using very few abbreviations and only using acronyms that are commonly used within HUD.

1.5.2. Physical Name

The physical name is the exact name of the column as seen in the data mart. This name follows the column naming convention outlined in “Table and Column Convention Options for OPIIS

2.0 Data Mart” dated November 9, 2007. For further information regarding the physical names in the data mart, please refer to this document.

1.5.3. Data Type

The data type identifies the format in which the data in each column is stored. Given that the OPIIS 2.0 data mart is a compilation of data from other multifamily data sources, many of the data types are dependent upon the data types in the source systems. When the data is pulled from the various source systems, the most appropriate data type is automatically identified based on the data found in the column, unless otherwise specified in the data extraction process.

The data type column for each documented table includes both the data type and the precision of the data stored in a given table and column, where appropriate. The precision component applies to character, variable character, and decimal data types and consists of one or two numbers in parentheses. When there is one number only, this number represents the maximum number of characters allowed. When there are two numbers separated by a comma, the first number identifies the total length of the data, while the second number identifies the number of places to the right of the decimal place. For example, a precision of (255) indicates that the column can store a maximum of 255 characters. The precision of (8,2) indicates that the column contains decimals that are 10 total numbers in length, with eight numbers to the left of the decimal place and two numbers to the right of the decimal place.

In most cases, the data types are documented exactly as they are stored in the data mart.

However, for numeric and decimal data types, which are essentially identical, the documentation refers to a data type of decimal for both.

1.5.4. Nulls Allowed

The nulls allowed column identifies whether null values are allowed in a given data column. A “No” in this column indicates that a data column does not allow nulls.

1.5.5. Definition

The definition column for each documented table contains a business definition for each column in the table. The business definition is intended to provide OPIIS 2.0 data mart users with information regarding the actual contents of a column to help users utilize and interpret the data correctly. In many cases, the definition also identifies the original source of the data in a column.

1.6.Data Mart Tables mf_232_active_financing_participant mf_232_afs_compl_data mf_232_afs_refer_data mf_232_participant_address mf_24_1400_10 mf_24_1400_20 mf_a1_pipe_snapshot mf_a1_pipe_snapshot_pnl_mthly mf_a2_pipeline_act_pnl_mnthly mf_a2_pipeline_actions mf_a3_firm_commit mf_a3_firm_commit_pnl_mnthly mf_a4_init_endorse_pnl_mthly mf_a4_initial_endorse mf_action_plans mf_afs_auditor_rpt_s2100_020 mf_afs_auditor_rpt_s2100_100 mf_afs_auditor_rpt_s2200 mf_afs_auditor_rpt_s2300 mf_afs_auditor_rpt_s2400 mf_afs_auditor_rpt_s2500 mf_afs_auditor_rpt_s3400_020 mf_afs_auditor_rpt_s3400_100 mf_afs_auditor_rpt_s3500 mf_afs_auditor_rpt_s3600 mf_afs_auditor_rpt_s4000 mf_afs_balance_sheet mf_afs_balance_sheet_1140 mf_afs_balance_sheet_1145 mf_afs_balance_sheet_1190 mf_afs_balance_sheet_1590 mf_afs_balance_sheet_2132 mf_afs_balance_sheet_2172 mf_afs_balance_sheet_2190 mf_afs_balance_sheet_2322 mf_afs_balance_sheet_2390 mf_afs_cash_flow mf_afs_cash_flow_s1200_036 mf_afs_cash_flow_s1200_226 mf_afs_cash_flow_s1200_341 mf_afs_cash_flow_s1200_346 mf_afs_cash_flow_s1200_451 mf_afs_cash_flow_s1200_456 mf_afs_cash_flow_s1200_536 mf_afs_cash_flow_s1200_601 mf_afs_cash_flow_s1200_606 mf_afs_certification_s2900 mf_afs_certification_s3000 mf_afs_certification_s3200 mf_afs_comment mf_afs_compliance_flag mf_afs_fed_awards_s3300_020 mf_afs_fed_awards_s3300_500 mf_afs_findings_s2700_005 mf_afs_findings_s2700_057 mf_afs_findings_s2700_110 mf_afs_findings_s2800 mf_afs_findings_s3700 mf_afs_findings_s3800_010 mf_afs_findings_s3800_037 mf_afs_findings_s3800_160 mf_afs_findings_s3900 mf_afs_header mf_afs_header_pnl_mthly mf_afs_notes_s3100_010 mf_afs_notes_s3100_060 mf_afs_notes_s3100_140 mf_afs_notes_S3100_200 mf_afs_notes_s3100_210 mf_afs_notes_s3100_240 mf_afs_nursing_home mf_afs_owner_equity mf_afs_owner_equity_s1100_030 mf_afs_owner_equity_s1100_056 mf_afs_prof_and_loss_pnl_mthly mf_afs_profit_and_loss mf_afs_profit_and_loss_5190 mf_afs_profit_and_loss_5290 mf_afs_profit_and_loss_5490 mf_afs_profit_and_loss_5990 mf_afs_profit_and_loss_6390 mf_afs_profit_and_loss_6590 mf_afs_profit_and_loss_6790 mf_afs_profit_and_loss_6890 mf_afs_profit_and_loss_7190 mf_afs_reserve mf_afs_reserve_1320od mf_afs_reserve_1320ow mf_afs_reserve_1340od mf_afs_reserve_1340ow mf_afs_surplus_cash mf_afs_surplus_cash_1410a mf_afs_surplus_cash_1410d mf_afs_surplus_cash_1420a mf_afs_surplus_cash_1420d mf_afs_surplus_cash_1440a mf_afs_surplus_cash_1440d mf_afs_surplus_cash_1450a mf_afs_surplus_cash_1450d mf_afs_surplus_cash_1460a mf_afs_surplus_cash_1460d mf_afs_surplus_cash_1465a mf_afs_surplus_cash_1465d mf_afs_surplus_cash_1470a mf_afs_surplus_cash_1470d mf_afs_surplus_cash_1480a mf_afs_surplus_cash_1480d mf_afs_surplus_cash_1490a mf_afs_surplus_cash_1490d mf_afs_surplus_cash_s1300_031 mf_afs_surplus_cash_s1300_120 mf_afs_surplus_cash_s1400_041 mf_afs_surplus_cash_s1400_160 mf_am_portfolio_ranking mf_am_portfolio_ranking_nonIns mf_assist_contract_pnl_mthly mf_assistance_contract mf_concept_meetings mf_construction_progress mf_contract_exp_history mf_dec_active_referrals mf_dec_referral_rpt mf_ded_table_attributes mf_ded_table_data_status mf_direct_loan mf_direct_loan_pnl_mnthly mf_direct_loan_pnl_mthly mf_disposition_sale mf_f47_survivorship mf_fair_market_rates mf_fha_claim mf_fha_loan_performance mf_flexible_subsidy mf_flexible_subsidy_pnl_mthly mf_fy mf_global_insight_data mf_grant_capital_adv_pnl_mthly mf_grant_capital_advance mf_history_default_status_rpt mf_history_int_risk_pnl_mthly mf_history_integrated_risk mf_history_property_active mf_history_property_event mf_history_property_owner mf_history_proprty_mgmt_agnt mf_hud_held_loan mf_hud_held_loan_pnl_mthly mf_insured_loan mf_insured_loan_pnl_mthly mf_loan_mods mf_loan_perf_quartly_panel mf_loan_perf_quartly_panel_1 mf_loans mf_loans_mods mf_mddr_del_actionstaken mf_mddr_del_def mf_mddr_del_def_pnl_mthly mf_mddr_del_reason mf_meap mf_mip_activity mf_mit_raw_data_all mf_mit_raw_data_apt mf_moodys_raw_data mf_non_insured_loan mf_note_sales_all mf_npv_tax mf_obm2_components mf_ohp_appraisal_data mf_ohp_history_defaults mf_original_holder mf_peoplesoft_survivorship mf_pfy mf_physical_inspect_pnl_mthly mf_physical_inspection

MF_PIPELINE_PROPERTY

mf_pmm mf_pms_active mf_project_app_phase

MF_PROP_GEO

mf_property mf_property_comment_new mf_property_disposition mf_property_pnl_mthly mf_req_fund mf_request_fund mf_service_coordinator_cntrct mf_service_coordinator_grant mf_tax_credit mf_use_restriction mf_use_restriction_pnl_mthly mf_voucher_edit mf_voucher_status mf_waivers ohf_ews_batch_run ohf_ews_missing_data ohf_ews_score_points ohf_EWS_SCORES ohf_facility ohf_fsi_missing_data ohf_fsi_scores ohf_historical ohf_loan ohf_pwlist ohf_pwlist_pnl_mthly ohp_history_def orcf_cms_balance_sheet orcf_cms_consolidated_stmt orcf_cms_profit_and_loss orcf_tsi_report orcf_tsi_report_panel ref_action_plans ref_afs_chart_of_accounts ref_afs_compl_unchecked_reason ref_afs_compliance_flag ref_afs_no_score_reason ref_afs_status ref_afs_submission_category ref_afs_submission_type ref_afs_submission_type ref_amortized_upb_range ref_building_type ref_census_division ref_census_region ref_claim_type ref_client_group ref_client_group ref_client_group_type ref_coefficients ref_combined_survivorship ref_company_type ref_contract_document_type ref_country ref_county_state_hub_mappings ref_current_contract_status ref_dap_application_type ref_dap_status ref_datetime ref_dec_referral_resolution ref_dec_referral_resolution ref_dec_referral_source ref_dec_referral_status ref_dec_referral_type ref_ded_table_information ref_disposition_sale ref_disposition_type ref_dynaxis_status_codes ref_fama_score_mapping ref_fass_compliance_status ref_fass_management_warning ref_fass_score_category ref_fha_active_criteria ref_FHA_lender ref_FHA_suffixes ref_field_office ref_financing_curr_status_type ref_financing_current_status ref_financing_instrument ref_financing_type ref_health_and_safety_status ref_history_code ref_hub ref_hud_region ref_insured_port_risk_rating ref_insured_rating ref_intgrtd_rsk_score_category ref_legal_structure ref_ln_term_survivorship ref_loan_payment_status ref_loan_termination_reason ref_loan_termination_survivorship ref_ltv_dscr ref_ltv_dscr_old ref_management_review_status ref_mars_status ref_mddr_actionstaken ref_mddr_default_status ref_mddr_event_type ref_mddr_reason ref_mddr_submission_type ref_metro_statistical_area ref_mgmt_conditions_status ref_mgmt_rvw_overall_rating ref_mroc_score_mapping ref_mroc_variable_baseline ref_mrod_coefficients ref_oahp_processing_type ref_oahp_watch_list_status ref_obm_score_range ref_obm2_coefficients_outliers ref_ohf_ews_ref ref_ohf_facility_type ref_other_funding ref_other_funding_type ref_overdue_afs_status ref_phys_inspection_score_rng ref_program_type ref_program_type_group ref_project_manager ref_property_event_source_tble ref_property_event_type ref_reis_area ref_reis_area_type ref_reis_market ref_reis_region ref_reis_state

89101112 ref_reis_submarket ref_renewal_option ref_renewal_status ref_renewal_type ref_rent_to_fair_market_rent ref_req_fund ref_section8_rule ref_soa_opiis ref_soa_opiis_pnl_mthly ref_source_systems ref_srvc_coord_funding_source ref_status_asgnd_by_field_off ref_svcr_coord_Funding_source ref_terminate_reason ref_tracs_contract_status ref_troubled_status ref_underwriting_type ref_uninsured_rating ref_unit_count_range ref_us_county ref_us_state ref_use_restriction_status ref_use_restriction_term_type ref_voucher_status reis_condition reis_condition_A reis_condition_BC

1.7.New Table Additions

This section provides a table outline of the new table additions to the data mart as of 06/30/2015.

Table Name Type Release mf_24_1400_10 Primary Reporting 9/30/2011 mf_24_1400_20 Primary Reporting 9/30/2011 mf_a1_pipe_snapshot Primary Reporting 9/30/2011 mf_a2_pipeline_actions Primary Reporting 9/30/2011 mf_a3_firm_commitment Primary Reporting 9/30/2011 mf_a4_initial_endorse Primary Reporting 9/30/2011 mf_loan_perf_quartly_panel Primary Reporting 9/30/2011 mf_fy Primary Reporting 9/30/2011 mf_mddr_del_def Primary Reporting 9/30/2011 mf_meap Primary Reporting 9/30/2011 mf_note_sales_all Primary Reporting 9/30/2011 mf_pfy Primary Reporting 9/30/2011 mf_pmm Primary Reporting 9/30/2011 mf_pms_active Primary Reporting 9/30/2011 reis_condition_a Primary Reporting 9/30/2011 reis_condition_bc Primary Reporting 9/30/2011 mf_pipeline_property Primary Reporting 12/30/2011 ref_section8_rule Reference 12/30/2011 mf_construction_progress Primary Reporting 3/31/2012 mf_global_insight_data Primary Reporting 3/31/2012 mf_mit_raw_data_all Primary Reporting 3/31/2012 mf_mit_raw_data_apt Primary Reporting 3/31/2012 mf_moodys_raw_data Primary Reporting 3/31/2012 ref_coefficients Reference 3/31/2012 ref_county_state_hub_mappings Reference 3/31/2012 ref_loan_termination_survivorshipReference 3/31/2012 ref_ltv_dscr Reference 3/31/2012 mf_mip_activity Primary Reporting 06/30/2012 mf_loans_mods Primary Reporting 06/30/2012 mf_request_fund Primary Reporting 06/30/2012 ref_req_fund Reference 06/30/2012 ohp_history_def Primary Reporting 6/30/2012 mf_am_portfolio_ranking Primary Reporting 09/30/2012 mf_mddr_del_actionstaken History 12/31/2012 mf_mddr_del_reason History 12/31/2012 ref_mddr_actionstaken Reference 12/31/2012 ref_mddr_del_reasons Reference 12/31/2012 mf_project_app_phase Primary Reporting 03/31/2013 mf_am_portfolio_ranking Primary Reporting 06/30/2013 mf_am_portfolio_ranking_n onins

Primary Reporting 06/30/2013 mf_dec_active_referral Primary Reporting 06/30/2013 mf_tax_credit Primary Reporting 06/30/2013 ref_fha_suffixes Reference 06/30/2013 ref_insured_rating Reference 06/30/2013 ref_uninsured_rating Reference 06/30/2013 ohf_ews_batch_run Primary Reporting 09/30/2013 ohf_ews_missing data Primary Reporting 09/30/2013 ohf_ews_score_points Primary Reporting 09/30/2013 ohf_ews_scores Primary Reporting 09/30/2013 ohf_facility Primary Reporting 09/30/2013 ohf_fsi_missing data Primary Reporting 09/30/2013 ohf_fsi_scores Primary Reporting 09/30/2013 ohf_historical Primary Reporting 09/30/2013 ohf_loan Primary Reporting 09/30/2013 ohf_pwlist Primary Reporting 09/30/2013 ohf_pwlist_pnl_mthly History 09/30/2013 ref_ohf_ews Reference 09/30/2013 ref_ohf_facility_type Reference 09/30/2013 ref_source_systems Reference 12/31/2013 ref_action_plans Reference 12/31/2013 mf_action_plans Primary Reporting

12/31/2013 mf_dec_referral_rpt Primary Reporting

03/31/2014 mf_ohp_appraisal_data Primary Reporting

03/31/2014 mf_tax_credit Primary Reporting

03/31/2014 mf_afs_compliance_flags Primary Reporting

03/31/2014 ref_afs_compliance_flag Reference 03/31/2014 mf_a1_pipe_snapshot_pnl_ mnthly

History 03/31/2014 mf_a2_pipeline_actions_pnl _mnthly

History 03/31/2014 mf_a3_firm_commitment_p nl_mnthly

History 03/31/2014 mf_a4_initial_endorse_pnl_ mnthly

History 03/31/2014 mf_other_funding_source Primary Reporting 06/30/2014 ref_other_funding_source Reference 06/30/2014 ref_lenders Reference 06/30/2014 mf_voucher_edit Primary Reporting 09/30/2014 mf_voucher_status Primary Reporting 09/30/2014 ref_voucher_status Reference 09/30/2014 ref_terminate_reason Reference 09/30/2014 orcf_tsi_report Primary Reporting 12/31/2014 orcf_tsi_report_panel Primary Reporting 12/31/2014 orcf_cms_consolidated Primary Reporting 12/31/2014 mf_232_afs_complete_data Primary Reporting 12/31/2014 mf_232_afs_refer_data Primary Reporting 12/31/2014 mf_232_active_financing_pa rticipant

Primary Reporting 12/31/2014 mf_232_participant_address Primary Reporting 12/31/2014 mf_waivers Primary Reporting 12/31/2014 ref_client_group Reference Table 12/31/2014 ref_afs_submission_type Reference Table 12/31/2014 ref_mrod_coefficients Reference Table 12/31/2014 orcf_cms_balance_sheet Primary Reporting 03/31/2015 orcf_profit_and_loss Primary Report 03/31/2015 orcf_consolidated_stmt Primary Reporting 03/31/2015 ref_dynaxis_status_codes Reference Table 03/31/2015 mf_fy Modification to primary reporting table

03/31/2015 mf_property Modification to primary reporting table

06/30/2015 mf_property_disposition Primary Reporting 06/30/2015 mf_disposition_sale Primary Reporting 06/30/2015 ref_disposition_sale Reference Table 06/30/2015 mf_am_portfolio_ranking Modification to primary reporting table

06/30/2015 mf_am_portfolio_ranking_n onins

Modification to primary reporting table

06/30/2015

2. Dictionary

OPIIS Data Element Dictionary

Business Column Name Physical Column Name Data Type

Nulls Allowed Definition mf_232_active_financing_participant

This table contains data on active participants for each project in the MF and Section 232 portfolios.

Table Name:

Table Description:

pk_idOPIIs-asigned sequence number OPIIs-asigned sequence numberLong Integer Yes fk_fha_numberFHA Project Number FHA Project NumberText No fk_financing_instrument_idFinancing Instrument ID Financing Instrument IDDecimal Yes fk_holder_mortgagee_idCurrent Mortgag2 Holder ID Current Mortgag2 Holder IDText No fk_property_idProperty ID Property IDDecimal No fk_servicing_mortgagee_idServicer ID Servicer IDText No tag_fha_number_suffixFHA Number with Suffix FHA Number with SuffixText No tag_fha_number_with_suffixFHA Number (without Suffix) FHA Number (without Suffix)Text No tag_holder_mortagee_zip_4Current Mortgage Holder (Mortgagee) Address, Line1

Current Mortgage Holder (Mortgagee) Address, Line1Text No tag_holder_mortgagee_address_1Current Mortgage Holder (Mortgagee) Address, Line1

Current Mortgage Holder (Mortgagee) Address, Line1Text No tag_holder_mortgagee_address_2Current Mortgage Holder (Mortgagee) Address, Line1

Current Mortgage Holder (Mortgagee) Address, Line1Text No tag_holder_mortgagee_cityCurrent Mortgage Holder (Mortgagee) Address, Line1

Current Mortgage Holder (Mortgagee) Address, Line1Text No

Wednesday, September 02, 2015 Page 1 of 644 mf_232_active_financing_participantTable Name:

Nulls Allowed Definition tag_holder_mortgagee_nameCurrent Mortgage Holder (Mortgagee) Address, Line1

Current Mortgage Holder (Mortgagee) Address, Line1Text No tag_holder_mortgagee_stateCurrent Mortgage Holder (Mortgagee) Address, Line1

Current Mortgage Holder (Mortgagee) Address, Line1Text No tag_holder_mortgagee_state_nmCurrent Mortgage Holder (Mortgagee) Address, Line1

Current Mortgage Holder (Mortgagee) Address, Line1Text No tag_holder_mortgagee_zipCurrent Mortgage Holder (Mortgagee) Address, Line1

Current Mortgage Holder (Mortgagee) Address, Line1Text No tag_servicing_mortgagee_address_1Servicer Address, Line 1 Servicer Address, Line 1Text No tag_servicing_mortgagee_address_2Servicer Address, Line 2 Servicer Address, Line 2Text No tag_servicing_mortgagee_cityServicer City Servicer CityText No tag_servicing_mortgagee_nameServicer Company/Lender Name Servicer Company/Lender NameText No tag_servicing_mortgagee_stateServicer Abbreviated Stated Servicer Abbreviated StatedText No tag_servicing_mortgagee_state_nameServicer Full State Name Servicer Full State NameText No tag_servicing_mortgagee_zipServicer 5-digit zip code Servicer 5-digit zip codeText No tag_servicing_mortgagee_zip_4Servier full 9-digit zip code Servier full 9-digit zip codeText No

Wednesday, September 02, 2015 Page 2 of 644 mf_232_active_financing_participantTable Name:

Allowed Definition mf_232_afs_compl_data

This table contains compliance data on all annual financial statement submissions. Instances of non-compliance are recorded as 'flags'.

Table Name:

Table Description:

pk_project_afs_header_idAFS Header ID AFS Header IDDecimal Yes fk_contract_numberAssistance Contract Number Assistance Contract NumberText Yes fk_fha_numberFHA Project Number FHA Project NumberText No fk_participant_idParticipant ID Participant IDDecimal No fk_property_idProperty ID Property IDDecimal No fk_tax_idMortgagor Tax ID Mortgagor Tax IDText No tag_compl_not_assessed_descExplanation or Description for non-assessment of compliance flags

Explanation or Description for non-assessment of compliance flagsMemo No cat_submission_typeSubmission Type AFS Submission TypeText No mtr_total_flagsTotal Number of non-compliance flags raised by or found in AFS submission

Total Number of non-compliance flags raised by or found in AFS submission

Decimal No mtr_total_not_assessed_flagsTotal Number of compliance flags that were not assessed

Total Number of compliance flags that were not assessedDecimal No date_afs_fyeAFS Fiscal Year End AFS Fiscal Year EndDate/Time No date_receivedDate AFS received Date AFS receivedDate/Time No

Wednesday, September 02, 2015 Page 3 of 644 mf_232_afs_compl_dataTable Name:

Allowed Definition mf_232_afs_refer_data

This table identifies the business unit or organization to which the property was referred for negative findings as discovered in the annual financial statement referenced.

Table Name:

Table Description:

pk_project_afs_header_idAFS Header ID AFS Header IDDecimal Yes fk_contract_numberAssistance Contract Number Assistance Contract NumberText Yes fk_fha_numberFHA Project Number FHA Project NumberText No fk_participant_idParticipant ID Participant IDDecimal No fk_property_idProperty ID Property IDDecimal No fk_submission_type_reference_idSubmission Type Reference ID Submission Type Reference IDDecimal No fk_tax_idMortgagor Tax ID Mortgagor Tax IDText No tag_referral_toBusiness Unit to whom Project was referred based on AFS review findings

Business Unit to whom Project was referred based on AFS review findings

Text No cat_submission_typeSubmission Type AFS Submission TypeText No date_afs_fyeAFS Fiscal Year End AFS Fiscal Year EndDate/Time No date_receivedDate AFS received Date AFS receivedDate/Time No date_referral_date_effectiveReferral Activation Date Referral Activation DateDate/Time No ind_m2m_indicatorMark-to-Market project indicator Mark-to-Market project indicatorText No

Wednesday, September 02, 2015 Page 4 of 644 mf_232_afs_refer_dataTable Name:

Allowed Definition ind_np_distribution_allowedNon-Profit Project Distribution Permitted Indicator

Indicator to designate that the project, if it is non-profit, is permitted to distribute excess cash to owners

Text No

Wednesday, September 02, 2015 Page 5 of 644 mf_232_afs_refer_dataTable Name:

Allowed Definition mf_232_participant_address

This table contains participant addresses.

Table Name:

Table Description:

pk_idOPIIs-asigned sequence number OPIIs-asigned sequence numberLong Integer Yes fk_address_idAddress Id Address IdDecimal Yes fk_participant_idParticipant ID Participant IDLong Integer No fk_update_user_idUpdate User ID Update User IDText No cat_irems_sync_action_type_cdIREMS synchronized action type code

IREMS synchronized action type codeText No date_last_update_dateDate of Update Date of UpdateDate/Time No

Wednesday, September 02, 2015 Page 6 of 644 mf_232_participant_addressTable Name:

Allowed Definition mf_24_1400_10

This information is used to determine loss rates for the multifamily insurance programs and is based on assets data in the inventory. This table is used in recording the 1% UPB Reserve for foreclosure expenses and establishing payables for assignment of new notes to the HUD Held inventory. Entries associated with 24-1400-10 show the universal detail information including the amounts established as well as the subsequent reversed amounts in 24-1400-10 prior to a change in business process of then capitalizing the amounts to the 24-1400-20 or 24-1400-25 GL accounts. 24-1400-10 is an establishment of a 1% holdback. 24-1400-20 is for capitalizing the expense at termination of the note as part of the cumulative profit/loss. 24-1400-25 is exclusively for asset sales termination.

Table Name:

Table Description:

pk_FHAFederal Housing Administration ID FHA Project Number as defined when a mortgage is either insured by made directly by the Federal Housing Administration (FHA).

Text Yes tag_BGLACNOBalancing General Ledger Account Number

Balancing General Ledger Account Number required to record an accounting transaction within CSMS.

Text Yes tag_bpostcodeBalancing Postcode Balancing post code required to record an accounting transaction within

CSMS.

Text Yes tag_GLACNOGeneral Ledger Account Number CSMS General Ledger Account Number. 24-1400-10 is used for the establishment of the 1% holdback for foreclosure expenses at time of a default assignment to FHA.

Text Yes tag_postcodePostcode Postcodes are assorted 2-digit codes that represent a transaction that takes place. The postcode indicates the account within a note from where the funds should be placed or taken from.

Text Yes cat_statusCase Status A range of numbers from 1 to 18 that corresponds to the progression of a mortgage's status while it is in Mortgage Loan Servicing Database of

CSMS.

Text Yes mtr_Cr_AmtCredit Amount Credit value of the specific accounting transaction when recorded.Double Yes mtr_Dr_AmtDebit Amount Debit value of the specific accounting transaction when recorded.Double Yes

Wednesday, September 02, 2015 Page 7 of 644 mf_24_1400_10Table Name:

Allowed Definition mf_24_1400_20

This information is used to determine loss rates for the multifamily insurance programs and is based on assets data in the inventory. This table is used in calculating the 1% holdback value for capitalized foreclosure expenses when the notes are sold or paid off; profit and loss rates are calculated. The 1% holdback by HUD is considered as a collection."

Table Name:

Table Description:

pk_FHAFederal Housing Administration ID FHA Project Number as defined when a mortgage is either insured by made directly by the Federal Housing Administration (FHA).

Text Yes tag_GlacnoGeneral Ledger Account Number CSMS General Ledger Account NumberText Yes cat_statusCase Status A range of numbers from 1 to 18 that corresponds to the progression of a mortgage's status while it is in Mortgage Loan Servicing Database of

CSMS.

Text Yes mtr_balanceGeneral Ledger Account Balance Cumulative amount of all accounting activity from all reporting postcodes at the end of an accounting period

Double Yes

Wednesday, September 02, 2015 Page 8 of 644 mf_24_1400_20Table Name:

Allowed Definition mf_a1_pipe_snapshot

This table contains one row for every application in the production pipeline, except for applications whose current status in DAP fits into one of the following pipeline status groups: a) applications that owners withdrew (at any phase) more than 60 days before the data was extracted. b) Preliminary or final rejects issued more than 120 days before the data was extracted. c) Commitments, invites, and application letters that expired more than 120 days before the data was extracted. d) Final endorsements effective more than 60 days before the data was extracted.

Table Name:

Table Description:

pk_dap_project_idDAP Project ID Project ID for the loan application.numeric(9) Yes fk_cur_ldr_idCurrent Lender ID Lender ID at current status.char(5) Yes fk_cur_stat_codeCurrent applicationlication Status Code for application's current status in DAP.char(3) Yes fk_dap_applicationlication_idDAP applicationlication ID Application ID for the loan application.numeric(9) Yes fk_dap_current_phase_idCurrent Phase ID Phase ID for loan application.numeric(9) Yes fk_dap_current_status_idCurrent Status ID Status ID for current status.numeric(9) Yes fk_financing_instrument_idREMS Financing Instrument ID REMS Financing Instrument ID.numeric(10) Yes fk_hub_geoHUB Sort Order Number Sort order number for HUBs. Sorts geographically from Northeast corner of US (Boston) to SW (Los Angeles) corner.

numeric(3) Yes fk_msa_codeProject MSA Code MSA Code for project's metro area (from REMS).char(5) Yes fk_parent_noPrimary Project Number Parent/Primary Project Number assigned to this application.char(11) Yes fk_pipe_stat_grp_ordPipeline Status Sort Order Number Sort Order for the Pipeline Status Group used to sort the Pipeline Status groups.

numeric(4) Yes fk_pipe_stat_ordStatus Code Sort Order Number Sort order for individual status codes used to sort statuses.char(4) Yes fk_pjt_noFHA Number Project Number / FHA Number assigned to application.varchar(11) Yes

Wednesday, September 02, 2015 Page 9 of 644 mf_a1_pipe_snapshotTable Name:

Allowed Definition fk_rems_property_idREMS Property ID REMS Property ID.numeric(10) Yes fk_soaSOA Code 3 digit alpha code for Section of the Act/ Law under which loan will be insured.

char(3) Yes fk_soa_catgy_ordSOA Sort Order Number Category Number for the application's SoA used to sort SoA.numeric(4) Yes fk_supercede_no_1Superceded Project Number 1 Superseded Project Number 1.char(11) Yes fk_supercede_no_2Superceded Project Number 2 Superseded Project Number 2.char(11) Yes fk_supercede_no_3Superceded Project Number 3 Superseded Project Number 3.char(11) Yes tag_a_and_e_repArchitectural/Engineering Representative

A&E Representative assigned to application.varchar(32) Yes tag_appsrapplicationraiser Appraiser assigned to application.varchar(32) Yes tag_asset_mgrAsset Manager Asset manager (REMS application Manager) assigned to application.char(25) Yes tag_const_analystConstruction Analyst Construction Analyst assigned to application.varchar(32) Yes tag_contractorPrime Contractor Name of Prime Contractor.varchar(38) Yes tag_cost_analystCost Analyst Cost Analyst assigned to application.varchar(32) Yes tag_cur_ldr_nameLender (Short Name) Short Name of Lender at current Status.char(40) Yes tag_dap_mgmt_agentManagement Agent (DAP) Name of Management Agent entered in DAP.varchar(38) Yes tag_dap_mgmt_agent_addressManagement Agent Address Address for Management Agent.varchar(50) Yes

Wednesday, September 02, 2015 Page 10 of 644

Allowed Definition tag_dap_mgmt_agent_tinManagement Agent Tax ID Number

Tax ID Number (TIN) for Management Agent.varchar(21) Yes tag_developerDeveloper Name of Sponsor/Developer.varchar(38) Yes tag_devmt_project_mgrMultifamily Representative MF Rep or Development Project Manager assigned to this application.char(35) Yes tag_emas_economistEconomist Economist assigned to application.varchar(32) Yes tag_genl_partnerGeneral Partner Name of General Partner.varchar(38) Yes tag_mc_repMortgage Credit Examiner Mortgage Credit Examiner assigned to application.varchar(32) Yes tag_mtgor_entityMortgagor Name of Mortgagor.varchar(38) Yes tag_pjt_nameProject Name Name of Project.varchar(50) Yes tag_primary_street_addrProject Street Address Street Address for primary property.varchar(50) Yes tag_rems_mgmt_agentManagement Agent (REMS) Management Agent Name read from REMS.char(38) Yes tag_rems_mgmt_agent_contactManagement Agent Contact

(REMS)

Name of Management Agent from REMS.char(38) Yes tag_team_leaderTeam Leader Team Leader assigned to this project.varchar(32) Yes cat_activityTransaction Purpose Activity (NC, SR, Refinance, Purchase/Acquisition, Improvements, Adds, or Operating Loss) for the application Users load to refine larger activity group set by SoA.

varchar(18) Yes cat_activity_grpTransaction Activity Group Activity group (NC/SR, Refinance/Purchase, Improvements/Adds, Operating Loss) for this application's SoA. Use to filter out or group projects.

char(35) Yes

Wednesday, September 02, 2015 Page 11 of 644

Allowed Definition cat_apts_hcApartment or Healthcare? Indicates if the project is Apartments or Health Care. Use to roll data up to a level higher than the facility type. Coops & SROs are within the apartments category of this field.

char(20) Yes cat_cityProject City City of the primary address of this project.varchar(30) Yes cat_cur_phaseCurrent Development Phase Latest development phase recorded in DAP for this application.varchar(30) Yes cat_cur_stat_nameCurrent Status (DAP) Name of application's current status in DAP.char(30) Yes cat_dap_county_nameCounty (DAP) County of the primary address of this project from DAP.char(30) Yes cat_facilityFacility Type Facility type (Apartments, Coops, Nursing Home/Intermediate Care Facility, Assisted Living Facility, Board and Care, etc.) for the application.

char(16) Yes cat_facility_grpFacility Type Group Facility Type Group (Apartments, SRO, Assisted Living etc) for the SoA.

char(40) Yes cat_hubHUB HUB responsible for this application.varchar(25) Yes cat_insd_advcTiming of Mortgage Insurance Timing of Insurance: A = Insured Advances ; C= upon Completion.char(1) Yes cat_map_tapProcessing Method Processing method (MAP or TAP) for this application.char(3) Yes cat_msa_nameMSA Name of metropolitan statistical area (MSA) in which project is located.char(30) Yes cat_mtgor_typeMortgagor Type Type of Mortgagor (e.g., profit/non-profit).char(3) Yes cat_pipe_stat_grpPipeline Status Group Pipeline Status Group (IP, O, Wdrn, Fin Reject, Exp) for the Current Status used to group and count loans.

char(16) Yes cat_production_officeProduction Office Processing Office responsible for this application.char(16) Yes

Wednesday, September 02, 2015 Page 12 of 644

Allowed Definition cat_program_centerProgram Center Field Office this application/loan is assigned to, using office prefix definition - the first 3 digits of the project number.

char(16) Yes cat_rems_county_nameCounty (REMS) County of the primary address of this project from REMS.char(30) Yes cat_soa_catgySOA Category Category (FHA NC/SR Apartments, 223f Apartments, 232 Health Care, etc) for the application's SoA used to filter out or group projects.

char(30) Yes cat_soa_main_grpSOA Main Group Main Group (Basic FHA, Risk Sharing, Hosp & Group Practice) for the application's SoA used to filter out or group projects.

char(30) Yes cat_soa_subcatgySOA Subcategory Sub-Category for the application's SoA. Use to select subsets of a category (e.g., select 221d4s or 221d3 part of NC/SR Apartments category).

char(40) Yes cat_stState State abbreviation of the primary site.varchar(2) Yes mtr_alf_bedsAssisted Living Facility Beds Number of Assisted Living Facility Beds.numeric(5) Yes mtr_alf_non_rev_unitsAssisted Living Facility Non-revenue Units

Assisted Living Facility non-revenue units.numeric(5) Yes mtr_alf_rev_unitsAssisted Living Facility Revenue Units

Assisted Living Facility revenue units.numeric(5) Yes mtr_bc_bedsBoard and Care Beds Number of Board and Care beds.numeric(5) Yes mtr_bc_non_rev_unitsBoard and Care Non-revenue Units Board and Care non-revenue units.numeric(5) Yes mtr_bc_rev_unitsBoard and Care Revenue Units Board and Care revenue units.numeric(5) Yes mtr_cur_interestCurrent Interest Rate Interest Rate at current status.numeric(11,8) Yes mtr_cur_mtgeCurrent Mortgage Amount Mortgage Amount at current status.numeric(10) Yes

Wednesday, September 02, 2015 Page 13 of 644

Allowed Definition mtr_cur_units_or_bedsTotal Number of Units Units /Beds for current status. Note: For health care projects, the total number of beds for all facility types. For Apartments/ coop projects, the total number of revenue units or mobile home spaces.

numeric(5) Yes mtr_days_since_application_recDays since First applicationlication Received

Number of days elapsed since receipt of first application (pre-application, SAMA, Conditional, or Firm).

numeric(6) Yes mtr_days_since_firm_invitedDays since Firm Commmitment Issued

Number of days elapsed since Firm Commitment was issued.numeric(6) Yes mtr_days_to_firm_decisionDays from Pre-applicationlication Decision

Number of days elapsed from preapplication decision (issue or reject).numeric(6) Yes mtr_days_to_phase_decisionDays from HUD Credit Decision Number of days elapsed to HUD decision (invite/issue or reject).numeric(6) Yes mtr_days_to_preapplication_decisionDays from Number of days elapsed from preapplication decision (invite or reject).numeric(6) Yes mtr_icf_bedsIntermediate Care Facility Beds Number of Intermediate Care Facility beds.numeric(5) Yes mtr_icf_non_rev_unitsIntermediate Care Facility Non-revenue Units

Intermediate Care Facility non-revenue units.numeric(5) Yes mtr_mtg_chg_since_recMortgage Amount Change from Original Requested

Mortgage amount at current status less mortgage amount requested in first application (pre-application, SAMA, Conditional, or Firm).

numeric(10) Yes mtr_nh_bedsNursing Home Beds Number of Nursing Home/Intermediate Care Facility Bedsnumeric(5) Yes mtr_nh_non_rev_unitsNursing Home Non-revenue Units Nursing Home non-revenue units.numeric(5) Yes mtr_no_bldgsNumber of Buildings Number of buildings in the project.numeric(4) Yes mtr_no_sitesNumber of Sites Number of sites.numeric(5) Yes mtr_non_rev_unitsTotal Number of Non-revenue Units

Total Number of Non-Revenue Units.numeric(5) Yes

Wednesday, September 02, 2015 Page 14 of 644

Allowed Definition mtr_rev_unitsTotal Number of Revenue Units Total Number of Revenue Units.numeric(5) Yes mtr_tot_bedsTotal Beds Total Number of Beds.numeric(5) Yes date_application_rec_dateapplicationlication Acceptance Date

Date first application (pre-application, SAMA, Conditional, or Firm) was accepted for processing.

datetime Yes date_cur_stat_dtDate of Current Status Date Current Status became effective.datetime Yes date_cur_stat_updt_dtSystem Update of Current Status Date the Current Status was entered into DAP.datetime Yes date_firm_decision_dtFirm Decision Date Firm decision date.datetime Yes date_invite_comt_expire_dtInvitation/Commitment Expiration Date

Date the Invite/Commitment Expires.datetime Yes date_phase_decision_tget_dtHUD Decision Target Date Target Date for HUD decision (invite/issue or reject).datetime Yes date_preapp_decision_dtPre-applicationlication Decision Date

Preapplication decision date.datetime Yes ind_cdbgCommunity Development Block Grant (CDBG) Funds Indicator

Does this project have CDBG funds?char(1) Yes ind_declg_areaDeclining Area Indicator Is the property in a declining area?char(1) Yes ind_homeHOME Project Indicator Is this a HOME project?char(1) Yes ind_hope_viHOPE VI Indicator Is this HOPE VI project?char(1) Yes ind_irp_decplgIRP Decoupling Loan Indicator Is this an IRP Decoupling loan?char(1) Yes ind_lihtcLow Income Housing Tax Credit (LIHTC) Indicator

Does this loan have LIHTC?char(1) Yes

Wednesday, September 02, 2015 Page 15 of 644

Allowed Definition ind_oahpOAHP Indicator Is this an OAHP project?char(1) Yes ind_refi_202_loan202 Refi Indicator Is this a refinance of 202 loan?char(1) Yes ind_rems_undrsvdUnderserved Area Indicator Is this property in an underserved area? Note: Underserved Area indicator is read from the REMS system.

char(1) Yes ind_skip_preappPre-applicationlication Skipped Indicator

Did MAP lender skip Pre-application and start at the Firm Phase? (This applies only to new construction/ sub rehab MAP cases.)

Char(1) Yes ind_sppSmall Project (SPP) Indicator Is this a Small Project (SPP)?char(1) Yes ind_te_bondsTax Exempt Bonds Indicator Does this loan have Tax Exempt Bonds?char(1) Yes

Wednesday, September 02, 2015 Page 16 of 644

Allowed Definition mf_a1_pipe_snapshot_pnl_mthly

This is a history table containing monthly panels of the mf_a1_pipe_snapshot table.

Table Name:

Table Description:

pk_dap_project_idDAP Project ID Project ID for the loan application.numeric(9) Yes fk_cur_ldr_idCurrent Lender ID Lender ID at current status.char(5) Yes fk_cur_stat_codeCurrent applicationlication Status Code for application's current status in DAP.char(3) Yes fk_dap_applicationlication_idDAP applicationlication ID Application ID for the loan application.numeric(9) Yes fk_dap_current_phase_idCurrent Phase ID Phase ID for loan application.numeric(9) Yes fk_dap_current_status_idCurrent Status ID Status ID for current status.numeric(9) Yes fk_financing_instrument_idREMS Financing Instrument ID REMS Financing Instrument ID.numeric(10) Yes fk_hub_geoHUB Sort Order Number Sort order number for HUBs. Sorts geographically from Northeast corner of US (Boston) to SW (Los Angeles) corner.

numeric(3) Yes fk_msa_codeProject MSA Code MSA Code for project's metro area (from REMS).char(5) Yes fk_parent_noPrimary Project Number Parent/Primary Project Number assigned to this application.char(11) Yes fk_pipe_stat_grp_ordPipeline Status Sort Order Number Sort Order for the Pipeline Status Group used to sort the Pipeline Status groups.

numeric(4) Yes fk_pipe_stat_ordStatus Code Sort Order Number Sort order for individual status codes used to sort statuses.char(4) Yes fk_pjt_noFHA Number Project Number / FHA Number assigned to application.varchar(11) Yes fk_rems_property_idREMS Property ID REMS Property ID.numeric(10) Yes

Wednesday, September 02, 2015 Page 17 of 644 mf_a1_pipe_snapshot_pnl_mthlyTable Name:

Allowed Definition fk_soaSOA Code 3 digit alpha code for Section of the Act/ Law under which loan will be insured.

char(3) Yes fk_soa_catgy_ordSOA Sort Order Number Category Number for the application's SoA used to sort SoA.numeric(4) Yes fk_supercede_no_1Superceded Project Number 1 Superseded Project Number 1.char(11) Yes fk_supercede_no_2Superceded Project Number 2 Superseded Project Number 2.char(11) Yes fk_supercede_no_3Superceded Project Number 3 Superseded Project Number 3.char(11) Yes tag_a_and_e_repArchitectural/Engineering Representative

A&E Representative assigned to application.varchar(32) Yes tag_appsrapplicationraiser Appraiser assigned to application.varchar(32) Yes tag_asset_mgrAsset Manager Asset manager (REMS application Manager) assigned to application.char(25) Yes tag_const_analystConstruction Analyst Construction Analyst assigned to application.varchar(32) Yes tag_contractorPrime Contractor Name of Prime Contractor.varchar(38) Yes tag_cost_analystCost Analyst Cost Analyst assigned to application.varchar(32) Yes tag_cur_ldr_nameLender (Short Name) Short Name of Lender at current Status.char(40) Yes tag_dap_mgmt_agentManagement Agent (DAP) Name of Management Agent entered in DAP.varchar(38) Yes tag_dap_mgmt_agent_addressManagement Agent Address Address for Management Agent.varchar(50) Yes tag_dap_mgmt_agent_tinManagement Agent Tax ID Number

Tax ID Number (TIN) for Management Agent.varchar(21) Yes

Wednesday, September 02, 2015 Page 18 of 644

Allowed Definition tag_developerDeveloper Name of Sponsor/Developer.varchar(38) Yes tag_devmt_project_mgrMultifamily Representative MF Rep or Development Project Manager assigned to this application.char(35) Yes tag_emas_economistEconomist Economist assigned to application.varchar(32) Yes tag_genl_partnerGeneral Partner Name of General Partner.varchar(38) Yes tag_mc_repMortgage Credit Examiner Mortgage Credit Examiner assigned to application.varchar(32) Yes tag_mtgor_entityMortgagor Name of Mortgagor.varchar(38) Yes tag_pjt_nameProject Name Name of Project.varchar(50) Yes tag_primary_street_addrProject Street Address Street Address for primary property.varchar(50) Yes tag_rems_mgmt_agentManagement Agent (REMS) Management Agent Name read from REMS.char(38) Yes tag_rems_mgmt_agent_contactManagement Agent Contact

(REMS)

Name of Management Agent from REMS.char(38) Yes tag_team_leaderTeam Leader Team Leader assigned to this project.varchar(32) Yes cat_activityTransaction Purpose Activity (NC, SR, Refinance, Purchase/Acquisition, Improvements, Adds, or Operating Loss) for the application Users load to refine larger activity group set by SoA.

varchar(18) Yes cat_activity_grpTransaction Activity Group Activity group (NC/SR, Refinance/Purchase, Improvements/Adds, Operating Loss) for this application's SoA. Use to filter out or group projects.

char(35) Yes cat_apts_hcApartment or Healthcare? Indicates if the project is Apartments or Health Care. Use to roll data up to a level higher than the facility type. Coops & SROs are within the apartments category of this field.

char(20) Yes

Wednesday, September 02, 2015 Page 19 of 644

Allowed Definition cat_cityProject City City of the primary address of this project.varchar(30) Yes cat_cur_phaseCurrent Development Phase Latest development phase recorded in DAP for this application.varchar(30) Yes cat_cur_stat_nameCurrent Status (DAP) Name of application's current status in DAP.char(30) Yes cat_dap_county_nameCounty (DAP) County of the primary address of this project from DAP.char(30) Yes cat_facilityFacility Type Facility type (Apartments, Coops, Nursing Home/Intermediate Care Facility, Assisted Living Facility, Board and Care, etc.) for the application.

char(16) Yes cat_facility_grpFacility Type Group Facility Type Group (Apartments, SRO, Assisted Living etc) for the SoA.

char(40) Yes cat_hubHUB HUB responsible for this application.varchar(25) Yes cat_insd_advcTiming of Mortgage Insurance Timing of Insurance: A = Insured Advances ; C= upon Completion.char(1) Yes cat_map_tapProcessing Method Processing method (MAP or TAP) for this application.char(3) Yes cat_msa_nameMSA Name of metropolitan statistical area (MSA) in which project is located.char(30) Yes cat_mtgor_typeMortgagor Type Type of Mortgagor (e.g., profit/non-profit).char(3) Yes cat_pipe_stat_grpPipeline Status Group Pipeline Status Group (IP, O, Wdrn, Fin Reject, Exp) for the Current Status used to group and count loans.

char(16) Yes…