1.Import State Assessed Property File
- Menu Option: PACS Menu > Import > State Assessed Properties
- The dialog contains:
- Import File Path – a file chooser allowing user to select a file to import. The file must be located in a valid network path.
- The dialog contains:
- Click Import
- A new record is added to the import_properties_run table with the following field values:
| Field Name | Value |
| import_properties_run_id | auto-incrementing record ID |
| status | “I” |
| import_date | The current date and time |
| import_user_id | The ID of the currently logged in PACS user from the [pacs_user] table. |
| record_count | The number of records read from the import file. |
| valid_properties_count | NULL |
| invalid_properties_count | NULL |
| processed_date | NULL |
| processed_by_id | NULL |
| file_path | {user selection from the dialog options} |
| in_current_year | 1 |
| in_future_year | 0 |
| state_assessed_year | year read from the import file |
| deleted_properties_count | NULL |
| created_properties_count | NULL |
- Click Process
- When the user clicks the “Process” button , the application will
perform the following steps:
- Validate whether or not the current year specified [import_properties_run].[state_assessed_year] has been certified.
- Attempt to match the imported records to an existing property in PACS
2.View Unmatched Property
Purpose
View Unmatched Property
Procedure
- Click ‘View Unmatched’ button from Import Runs View grid tool strip.
- This will render a dialog that reflects:
- Columns:
- File Line
- FEI Number
- Name
- Address Line 1
- Address Line 2
- City
- State
- Zip
- Tax Area
- Error Message
3.Create New Properties
Purpose
Create New Accounts
Procedure
- Click ‘Create New Accounts’ button from View Unmatched Properties Dialog grid
- The following dialog below will be rendered:
- Do Not Create the New Taxpayer Records for unmatched FEI Records
- Create a new property record in PACS with a new [prop_id], and set the [geo_id]
- Create New Taxpayer Records for unmatched FEI Records
- Create a new [account] record with a new acct_id and set the following field values from the import record
- Create a new property record in PACS with a new [prop_id], and set the [geo_id]
- Leave Geo_id Blank
- Leave Geo_id blank so user many manually update the record
- Create new Geo_id
- Create new Geo_id based on the Sequence # applied
- Apply custom formatting – user may set format of their unique geo_id
- Do Not Create the New Taxpayer Records for unmatched FEI Records
4.Addendum: Import Data Layouts
To support importing data from the new library that can read data directly from an Excel workbook, the following records are available in the excel_import_export_defs table.
| sheet_number | column_number | column_title | reader_field_name |
| 1 | 4 | 'FEI Number' | federal_employer_identification_number |
| 1 | 5 | 'Name' | business_name |
| 1 | 6 | 'Address1' | addr_line1 |
| 1 | 7 | 'Address2' | addr_line2 |
| 1 | 8 | 'City' | addr_city |
| 1 | 9 | 'State' | addr_state |
| 1 | 10 | 'Zip' | zip |
| 1 | 11 | 'Zip Ext.' | cass |
| 1 | 13 | 'Equalized Value' | market_value |
| 1 | 14 | 'Exempt Value' | exemption_value |
| 1 | 15 | 'Taxable Value' | taxable_value |
| 1 | 16 | 'Penalty' | penalty_amount |
| 1 | 17 | 'District Code' | tax_area_number |
| 1 | 18 | 'Value Type' | value_type |
The file format of the import file must be an Excel workbook (XLSX) file defined above where each row represents a record with the following data. As each row is read from the file, a new record will be added to the [import_properties_run_record] table, mapping the following elements to the fields documented below. When the user brings in the data into PACS, we DO NOT need the headers in the file, just the data in the correct layout.
| Column Name | Table Field Name | Notes |
| - | import_properties_run_record_id | auto-incrementing record ID |
| - | import_properties_run_id | FK value to [import_properties_run] record |
| - | line_number | Incrementing number for each record in import file starting at 1. |
| - | parcel_id | Will be set to ‘’ (an empty string) since this is a non-nullable field. |
| County Number | - | Ignored |
| County Name | - | Ignored |
| Year | - | used to set the [import_properties_run].[state_assessed_year], but otherwise ignored. |
| FEI Number | federal_employee_identification_number | Required. |
| Name | business_name | |
| Address 1 | addr_line1 | |
| Address 2 | addr_line2 | |
| City | addr_city | |
| State | addr_state | |
| Zip | zip | |
| Zip Route | If provided, the appended to [zip] | |
| District | tax_area_number | Ignored |
| Equalized Value | market_value | |
| Exempt Value | exemption_vaue | |
| Taxable Value | taxable_value | The import process will translate any value in the text file displayed as “NULL” into 0. |
| Penalty Amount | penalty_amount | |
| District Code | tax_area_number | This is a new field the DOR has agreed to provide in the export representing only the identifier for the Tax area. |
| Value Type | value_type | This is a new field the DOR has agreed to provide in the export representing the type of value the row represents, and will be used to help identify the appropriate property to update. |
5.Addendum: Taking snapshot of current state assessed accounts before import (optional)
Process
When the import is run, the result will be that the only active accounts will be what is in import file. Query below will take snapshot of current data that is State Assessed in system currently. This allows the user to put in Excel file to keep as a ‘back-up’.
Procedure
This query will pull all existing active State Assessed accounts that can then be modified for testing. Update the year to the current year in whatever db noted with ‘Change Year’).
/*To create simple import files.
(Query provided by DEV) Execute the following SQL in SSMS and then export with headers to CSV. Values in the file can be modified as needed for testing.*/
select
[County Number] = ‘Ignored’,
[County Name] = ‘Ignored’,
[Year] = pv.prop_val_yr,
[FEI Number] = ‘”‘ + a.federal_employer_identification_number + ‘”‘,
[Name] = isnull(a.business_name, a.file_as_name),
[Address1] = isnull(ad.addr_line1, ”),
[Address2] = isnull(ad.addr_line2, ”),
[City] = isnull(ad.addr_city, ”),
[State] = isnull(ad.addr_state, ”),
[Zip] = isnull(ad.zip, ”),
[Zip Ext.] = isnull(ad.cass, ”),
[District] = ta.tax_area_number,
[Equalized Value] = pv.market,
[Exempt Value] = pv.exemption_val,
[Taxable Value] = pv.taxable_val,
[Penalty] = pv.penalty_amount,
[District Code] = ta.tax_area_number,
[Value Type] =
case pv.sub_type
when ‘UP’ then ‘TPP’
when ‘UPR’ then ‘PCL’
when ‘UR’ then ‘Real’
end
from property as p with (nolock)
join property_val as pv with (nolock) on pv.prop_id = p.prop_id
join owner as o with (nolock) on
o.owner_tax_yr = pv.prop_val_yr and
o.sup_num = pv.sup_num and
o.prop_id = pv.prop_id and
o.primary_owner = 1
join account as a with (nolock) on
a.acct_id = o.owner_id
join address as ad with (nolock) on
ad.acct_id = a.acct_id and
ad.primary_addr = ‘Y’
join property_tax_area as pta with (nolock) on
pta.[year] = pv.prop_val_yr and
pta.sup_num = pv.sup_num and
pta.prop_id = pv.prop_id
join tax_area as ta with (nolock) on
ta.tax_area_id = pta.tax_area_id
where
pv.prop_val_yr = 2022 and —————-Change year
pv.sup_num = 0 and
pv.sub_type in (‘UP’, ‘UPR’, ‘UR’)
and pv.prop_inactive_dt is null
order by name