1.Import State Assessed Property File #

Purpose Import the State Assessed Property File Prerequisite Security Right:  Import > Import Create Properties (Execute) The file format of the import file must be an Excel workbook (XLSX) file (see Addendum). Additionally, if customer would like to capture current state assessed properties in an excel file, please run the query that is listed in Addedum.. Procedure
  • 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.
  • Click Import
    • A new record is added to the import_properties_run table with the following field values:

  • 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
Suggest edit
Last updated on December 30, 2021

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
Suggest edit
Last updated on March 18, 2020

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

 

Suggest edit
Last updated on March 18, 2020

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.

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.

Suggest edit
Last updated on December 29, 2021

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 

Suggest edit
Last updated on December 29, 2021
Suggest Edit
%d bloggers like this: