103-UT-006

Initialize Mobile Database

Version 6.0 / Version 6.1

Overview

The Initialize Mobile Database process is executed on the source server to create a copy, i.e. a Mobile mirror package of the deployment. The process creates an empty, detached database structure, and select dataset information. Copies of the system resources, i.e. reports, tables, and so on are also copied. In addition, general and selected module parameters and tables for given years are created. This process is executed at the initial setup of the Mobile device, or when changes occur in the source database. Changes include database structure, e.g. tables and fields, product resources, i.e. Views, Forms, tables, reports, and so on.

NOTE: Running this process on a database that is being used will result in the destination database being overwritten by the source. In addition, the size of some of the tables being processed can be a factor in the processing time.

What’s New!

Exclude Database Objects during Initialization (Manual process)

The Database Objects exclusion feature is an enhancement intended for the Govern Mobile application. This enhancement will allow users to manage the initialization of database objects, i.e. tables, functions, or views. In order to implement this enhancement, the (Table: SY_MOBILE_DB_OBJ_EXCLUSION) has been added to Govern Reference. This table will contain all object names that should be excluded during the GNA Initialize Mobile Database process. Refer to Mobile Configuration Editor for object exclusion through Govern UI.

GNA User Interface Changes

As a result of the added functionality and enhancements to the Govern Mobile application, changes have been made to the Govern New Administration (GNA) user interface. Changes are as follows; the Initialize Mobile Database process icon has been moved from the Database Utilities group. A new Mobile group is now present on the right hand side of the Import / Export Tools group. It is under this new Mobile group that mobile related functions will be found. Currently there are two mobile related options, the new Mobile Configuration, and the new location for the Initialize Mobile Database process.

NOTE: The functions within the Mobile group can be found using the expander arrowhead beside the Mobile Tools icon label.

Functionality

NEW! This new feature targets the Govern Mobile application in that it provides users with the ability to exclude database objects, i.e. a Table, a View, or a Function. It is not necessary to specify which of these three objects is contained in the table as the names of the content of the table are unique.
, and update user tables, during the Initialize Mobile Database, and the Get Latest process. For this implementation two (2) new tables have been added to the Govern reference; SY_MOBILE_TABLE and SY_MOBILE_COLUMN
This table has only one column called NAME. This column contains the name of the database object that is to be excluded.

WARNING: Administrators using this feature should take care when manually populating their source database.

Table Details
The (Table: SY_MOBILE_DB_OBJ_EXCLUSION) is a table with only one column. The column name is NAME. Refer to the Mobile – Database Objects Exclusion Technical Documentation for technical descriptions of this table.

Requirements

Prior to using this feature, run the verify database process Database Verification from the GNA ribbon to ensure that the source database has been updated with required tables.

The Process

1 – Populate the Table Manually

In order for the synchronization process to occur, users must populate the SY_MOBILE_DB_OBJ_EXCLUSION table manually. A recommended method is through Microsoft SQL Server Management Studio (MSQL MS). To populate the table through SQL Management Studio, use a Copy and Paste process to manually copy the information to the table.
(Table: SY_MOBILE_DB_OBJ_EXCLUSION).

2 – Verify the Content of the Database

After the table has been populated, finalize the changes. Verify that the copied tables are the ones that are to be excluded. All records that now appear in the table will be excluded, i.e. not copied to during the Initialize Mobile Database process.
SELECT*
FROM SY_MOBILE_DB_OBJ_EXCLUSION;

3 – Run the Initialize Mobile Database Process

Once the Source database table has been populated with the database objects that are required for exclusion, run the Initialize Mobile Database process in GNA.

NOTE: Read the confirmation that appears prior to the process being run.

 

TIP: Upon completion, as this is a manual process, always perform a random verification to ensure that the objects in the list of excluded objects table (Table: SY_MOBILE_DB_OBJ_EXCLUSION), do not appear in the destination table.

Tables

The following is the table, or list of tables, that were modified or created to implement this feature.

  • SY_MOBILE_DB_OBJ_EXCLUSION

Add and Update User tables during Initialization and Get Latest

NEW! The new feature in the Govern Mobile application is the ability to add, and update user tables, during the Initialize Mobile Database, and the Get Latest process. For this implementation two (2) new tables have been added to the Govern reference; SY_MOBILE_TABLE and SY_MOBILE_COLUMN
NOTE: The system still maintains a fixed list of system tables through the MobileTables.xml XML file, only user defined custom tables are added to the new SY_MOBILE_TABLE, and SY_MOBILE_COLUMN tables.

WARNING: Administrators using this feature should take care when manually populating their Source database

Table Details

Below are the columns within the data tables. Refer to the Technical Documentation for further descriptions of the tables.
SY_MOBILE_TABLE – Contains custom tables that can be used in data extraction

COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH NULLABLE COLUMN_DEF SQL_DATA_TYPE
NAME 12 varchar 30 30 0 NULL 12
READ_ONLY 5 smallint 5 2 0 (‘0’) 5
DOWNLOAD_ALL 5 smallint 5 2 0 (‘0’) 5
KEY_MASTER 12 varchar 30 30 1 NULL 12
IS_KEY_DYNAMIC 5 smallint 5 2 0 (‘0’) 5
KEY_COLUMN 12 varchar 30 30 1 NULL 12
OPTIONAL 5 smallint 5 2 0 (‘0’) 5
UPLOAD_ONLY 5 smallint 5 2 0 (‘0’) 5

SY_MOBILE_COLUMN – Contains custom columns that can be used in data extraction

COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH NULLABLE COLUMN_DEF SQL_DATA_TYPE
TABLE_NAME 12 varchar 30 30 0 NULL 12
NAME 12 varchar 30 30 0 NULL 12
REFERENCES 12 varchar 61 61 0 NULL 12
CONDITION -1 text 2147483647 2147483647 1 NULL -1
ONE_TO_MANY 5 smallint 5 2 0 (‘0’) 5
IGNORE 5 smallint 5 2 0 (‘0’) 5
READ_ONLY 5 smallint 5 2 0 (‘0’) 5

Requirements

Prior to using this feature, run the database verification process to ensure that the source database has been updated with the latest tables.

NOTE: In the current implementation of this feature, any required user tables must be specified manually.

Populating the Tables Manually

As indicated, in order for the synchronization process to occur with user defined tables, users must populate the SY_MOBILE_TABLE manually. A recommended method is through Microsoft SQL Server Management Studio (MS SQL MS). To populate the table through SQL Management Studio, use an INSERT statement to copy the required tables into (Table: SY_MOBILE_TABLE).

1 – Determine the Structure of the Tables

exec sp_columns SY_MOBILE_TABLE;
exec sp_columns SY_MOBILE_COLUMN;

The results will display the table columns that help when designing the action query to populate the tables. For example, an INSERT statement can be as follows…

2 – Design and run the INSERT Query

INSERTINTO SY_MOBILE_TABLE
VALUES (‘USR_MYTABLENAME01′,”,”,’USR_MYTABLENAME_COL1’,”,”,”,”);

Where USR_MYTABLENAME01 is the value entered in the NAME column, and so on. Insert all user tables that are required in both SY_MOBILE tables.

3 – Verify the INSERT Query

The INSERT action can be verified with a SELECT statement. As an example, the result of the following SELECT query will help to confirm that data has been inserted into the table.
SELECT*
FROM SY_MOBILE_TABLE;

…repeat for the SY_MOBILE_COLUMN table.

3 – Run Initialize Mobile Database Process

Once the Source database tables have been populated with required user tables, run the Initialize Mobile Database process in GNA when creating a new mobile deployment.

OR

3 – Synchronize on the Mobile Application

When the mobile application is running, and the Status indicates Connected, it will verify that a change has been made to the source database; the Get Latest icon will be active. Click Get Latest on the Mobile ribbon. During the Get Latest process, the Source and Mobile database will synchronize with any values found in the tables that are listed (Table: SY_MOBILE_TABLE).
For additional information on the process, refer to the Mobile Initialization section of the 202-[tech]-Mobile page on the Govern Wiki.

TIP: Upon completion, as this is a manual process, always perform a random verification to ensure that the requested user tables appearing in (Table: SY_MOBILE_TABLE, SY_MOBILE_COLUMN), appear in the destination table.

Tables

The following is the table, or list of tables, that were modified or created to implement this feature.

  • SY_MOBILE_COLUMN
  • SY_MOBILE_TABLE

See Also

Govern Mobile Technology

*

 

 

103-ut-006

 

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.Loading...

GNA Conversion MA Parameters

GNA Conversion – Mass Appraisal Parameters

Overview

[In Development] Because of the changes introduced in Mass Appraisal 6.1, table and parameter conversion is mandatory. This GNA function converts the Mass Appraisal (ma) Parameter Tables to OF version 6.1 format.

Process Steps

  • Backup COPY of all MA_PARM tables created AUTOMATICALLY to MA_PARM_xxx_BACKUP
    • When?: 1st Database Verification or … to be verified … would be nice to refresh backup
  • CONVERT (Start Process button) – change button label to CONVERT..
    • ALL (or selected) TABLES
    • ALL (or selected) YEAR
  • DELETE MA_PARM_xxx Backup copies – change Start Process button to DELETE –

User Options (UI)

Selection

  • Tables ALL or selected (clicked) Table(s) –RIGHT Bar
  • Year ALL or One 1 selected year

Process Action Button

  • Convert
  • Delete

Recommendations

  • UI Button resources change to CONVERT and DELETE instead of START PROCESS
  • First Time Converters – Try a small sample of tables and only 1 year … quick environment validation … if all is well, select all tables and all years

 

103-ma-cvt-001

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.Loading...

MA Single Entry Tables Income Added Items by Category

MA Single Entry Tables – Income Added Items by Category

Release 6.1

Overview

The Mass Appraisal Income Added Field has been changed. It has been removed and replaced with a generic editor. Currently it has been labeled as Income Added Fields, and is, for now, only for MA Income.

Scripts

Below are scripts that correspond with any changes to MA Income Added Fields.

Modifications on MA Parm Income Added Fields Editor (as of 2015.11.25 Wed.)

The Mass Appraisal (MA) Income Added Field has been changed. It has been removed and replaced by a generic editor (currently, it is labeled as Income Added Fields, and is, for now, only for MA Income.

As an alternative to adding a script to the MiscCorrectionScripts, premature at this stage of development, execute the following script on relevant Release 6.1 databases:

DELETE FROM vt_user WHERE code = ‘incadd0’ AND table_name = ‘MA_TABLE’
DELETE FROM ma_tables WHERE table_name = ‘incadd’

In addition, execute the following missing script (if (Table: MA_TABLES_BACKUP) exists.

DELETE FROM ma_tables_backup WHERE table_name = ‘incadd’

The above removes the incadd table type in Mass Appraisal’s Single Entry Tables.
DEVNOTE: Location of MiscCorrectionScripts (LOCATION: ServerNamedeploymentsBinariesSYSTEMrelNum)

the Mass Appraisal Income Added Field has been changed. It has been removed and replaced by a more generic editor(for now, it is labeled as “Income Added Fields”), and is, for now, only for MA Income.
Instead of adding a script in MiscCorrectionScripts which isn’t relevant for this stage of development, I ask you to execute this script on your DBs 6.1:

 

 

103-ma-parm-add

 

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.Loading...

Std. Integrity Business Rules

Govern Standard Integrity Business Rules (To be or not to be!)

Overview

The following integrity rules ensure that record dependencies are maintained when inactivating or deleting a record to ensure no orphan records are created.
These rules are referred to as Govern’s “To be, or not to be” business rules.

  • Inactive parcels are grayed out in the tree view and the records are read-only.
  • A effective and Inactive year is used in Govern to determine if a property or building is active.
  • In some modules (Self Reported business tax and Personal Property tax…) it is possible to temporarily deactivate an account

Property Control

Parcels (Property Info)

The property master information record PC_PARCEL is not year based.

  • It is considered ACTIVE when the EFFECTIVE YEAR is equal to or less than the current year.
  • It is considered INACTIVE when the INACTIVE YEAR is equal to or less than the current year.

If a parcel was created or inactivated in the middle of a role year (splits merges, new subdivision), then the dates are used and verified.

  • It is considered ACTIVE when the EFFECTIVE YEAR = or < than the current year AND the current date is equal or greater than the EFFECTIVE DATE.
  • It is considered INACTIVE when the INACTIVE YEAR = or < than the current year AND the current date is equal or greater than the INACTIVE DATE. (validate if inactive year must be year + 1 of the inactive date)
  • When a parcel is inactive all the records in the different tables must be inactivated for the current AND the future years.
  • All tables with frozen_id and year_id are set to inactive by setting their frozen id to -32766.
  • All link tables with EFFECTIVE_YEAR, INACTIVE_YEAR, FROM_YEAR, END_YEAR, EFFECTIVE_DATE, INACTIVE_DATE, FROM_DATE or END_DATE must also be inactivated

Parcel Linking

Parcels can be linked together using the parcel linking function PC_PARCEL_LINKS, generally used for condos.

  • A property is considered part of the link if its parcel linking effective date is less than or equal to the current date and the effective date is greater than the current date
  • If a property that is the MASTER record of a link is inactivated, than all the records in the link are inactivated

Buildings

The physical buildings are linked to the property using PC_LK_PARCEL_BLDG records that are not year based.

  • It is considered ACTIVE if the property is ACTIVE
  • It is considered ACTIVE when the EFFECTIVE YEAR is equal to or less than the current year.
  • It is considered INACTIVE if the property is INACTIVE
  • It is considered INACTIVE when when the INACTIVE YEAR = or < than the current year AND the current date is equal or greater than the INACTIVE DATE (same validation)

If a building is inactivated, then

  • Mass Appraisal Building Information MA_BUILDINGS is inactivated (-32766)
  • Mass Appraisal Miscellaneous Structures linked to a MA_BUILDINGS (to be verified)

Building Linking

Buildings can be linked together PC_LK_BLDG_TO_BLDG using the Building Linking function

  • The same rules apply as Parcel Linking

Names

Name records NA_NAMES cannot be deleted if in used:

  • Owner
  • Occupant
  • Releated Name
  • Grantor and Grantee

Property Locations

  • Property Location records PC_LOCATION cannot be deleted if a building is linked to it.

Mass Appraisal

Appraisal records are year and frozen id based.

MA Property Information

The Mass Appraisal Property Information MA_MASTER must exist for any computation to take place.

  • The record cannot be deleted if any appraisal components are linked to it
  • If an appraisal record is created in land, building, site, MRA, income, personal property, miscellaneous structure, the MA MASTER will be created for the specific year and frozen id if it does not exists. Note: The Valuation Method will be set to Cost (1).

MA Sites

All Mass Appraisal components are linked to a site record MA_SITE

  • Still Active Site Record Cannot be Deleted! if a site record is used by another mass appraisal component, it cannot be deleted.

 

Linked to INCOME or MRA

  • Land records linked to an active Income or MRA record cannot be deleted
  • Building records linked to an active Income or MRA record cannot be deleted

Tables Considered

The following tables are considered when applying these rules: (to be reviewed)
Refer to each module section for the list of tables applicable.

Property Control (PC)

  • PC_PARCEL
  • PC_LEGAL_INFO
  • PC_AREA
  • PC_PARCEL_LINKS
  • PC_LK_PARCEL_BLDG
  • PC_LK_BLDG_TO_BLDG

 

Real Property Tax (TX)

  • TX_EXEMPTIONS
  • TX_LEVY_MASTER
  • TX_LK_LEVY_EXEMPT
  • TX_QC_REP_FISCALE
  • TX_RE_ASSESSMENT

 

Mass Appraisal (MA)

  • MA_CA_VAL_TRACKING
  • MA_INCOME
  • MA_INC_INFO
  • MA_INC_SIMULATION
  • MA_LAND
  • MA_LD_INFO
  • MA_LK_EXEMPTIONS
  • MA_LK_INC_FIELD
  • MA_MASTER
  • MA_MISC_STRUCTURES
  • MA_MRA
  • MA_OVERRIDE
  • MA_SALE_SAVED_P
  • MA_SITE
  • MA_VALUES_TABLE

 

Personal Property (PP)

An account can be temporarily inactivated (to be validated)

  • PP_ASSESSMENT
  • PP_ASSESSMENT_DET
  • PP_AUDIT_INFO
  • PP_EXEMPTIONS
  • PP_LEVY_MASTER
  • PP_LK_LEVY_EXEMPT
  • PP_TEMPLATE_AS

 

100-std-brules

 

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.Loading...

100-Installation-FromScratch

Govern Installation from Scratch

Version 6.0 | Version 6.1

Overview

This section covers the steps and the best practices when installing 6.0 from scratch.
For upgrades or updates see 100-Upgrade where you will find tools, tips and training material to help with the upgrade.
Return to the Main Installation page 100-Install

Before Getting Started
Database Server (optional)

  • Install and/or start MS SQL SERVER 2014
  • Create Database / Restore Databases for upgrades
  • Roles and Security

Step by Step

OpenForms Initial Installation from a blank database.

  1. Create Govern Database(s) – make sure to add Govern as the owner of the database
  2. Install DeployEZ
  3. Create Deployment and Connexion key (note you will need to supply a Serial Number License Key)
  4. Install GNA
  5. GNA Run Database Verification (twice – first time prepares what it needs)
  6. GNA Set Organization parameters & SY REGISTRY, Create department, Create new sysadmin user
  7. GNA Import (EMT) System Queries & Tables (DynamicSearch.xml) and basic forms (FormReference.xml)
  8. GNA Create a report
  9. GNA Create a batch process
  10. GNA Create a profile
    • Set Forms, Batch programs, A/R allowed, Reports…
  11. GSM Create Roles
  12. GSM Synchronize
  13. GSM Setup Access rights (Apps, menu, profile)
  14. In SQL, make sure to enter 0 or 1 in USR_KEY_MASTER (until 1504)

See Also
100-Standards
100-Technical Requirements

 

100-Installation-FromScratch

 

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.Loading...

 

100-Installation-Hybrid108

Govern 6.0 HYBRID Installation (Govern for Windows)

[The content on this page is under review]

Overview
Quick Reference guide for the installation of Govern for Windows 10.8 Components.
Download requirements and installation steps are covered here.

Requirements

When installing from scratch, the 10.8 REFERENCE DATABASE & BINARIES must be used as a starting point.
Download FULL SERVICE PACK 10.8 and password file.

MS ACCESS is REQUIRED suggested as Govern for Windows uses Microsoft Attach Tables Methodology for improved client/server performance.

Installation

Read More...

100-(ui)-updata-[v10.8].png
100-(ui)-updata-[v10.8].png

STEP 1 – System Installation packs

Unzip pack and perform SETUP – to be verified with Richard Support

STEP 2 – Create ODBC Drivers Client connections

For each OpenForms Govern Environment (Production, Staging, Training, etc.)

STEP 3 – Setup Organization Licenses INI files

Create a .INI file that will be configured with an Organization Name and Serial Number.
Below is a sample .ini entry…

‘—————————– START —————————–
[General]
organization name=MyCompany
serial number=123456-78910111213-141516
primary activation key=
secondary activation key=

SQL=sqlserver
GLOBAL_USR_ADM=
GLOBAL_PASSWORD_ADM=XXXXXXX
USE_ACTIVE_DIRECTORY=no

PASSWORD_DOUBLE_CHECK=no
password=XXXXXXXXX
password suffix=
userid prefix=
User=CITY
Login Web Service URL=

[Database]
DSN=GOV_PSTRAIN
DB=GOV_PSTRAIN
Local Name=
DatabaseName=MYSERVERGovernDatabaseGOV_PSTRAIN_MYSERVER_SQL.mdb

[Upgrade]
Grant Name List=upgduser
‘—————————– STOP —————————–

STEP 4 – Run UPDATA – Database UPGRADE

Run the Govern for Windows Updata.exe application.

STEP 5 – Run UPDATA – Create … ATTACH TABLES

If you need more info on installing Govern for Windows, click here for the Getting Started in 10.8 manual.

STEP 6 – OpenForms Installation

OpenForms Initial Setup needs to be performed after installing Govern for Windows. After the Govern for Windows installation, OpenForms Database Verification needs to be performed.

STEP 7- OpenForms Update

Database Verification in GNA
Synchronize in GSM

 

Related Links

The following are links that are related to the upgrade process (refer to the indicated sections on the page).

 

100-installation-hybrid108

 

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.Loading...

OpenForms Reference

OpenForms Reference

Overview

The following is listed for information only. When there is a discrepancy, refer to the List of Changes Tables section of the Govern Database Diagrams page for the latest information and changes in the database structure or forms.

EMT Tool (Import Export)

XML files are located in the system library under:

  • FormReference.xml
  • DynamicSearch.xml

List of OpenForms Standard Forms

The following are links to the list of OpenForms standard forms that are found in the OpenForms release reference database.

Changes

Read More...

Changes made to the OpenForms supported items (forms, models, system tables, etc.) will be listed by date and version on this page (100-ofr-new). We suggest you setup a notification if you are in an implementation project or just to be informed.

Tables deleted or obsolete

OFR Product Catalog Codes

System Module Type Version Name OFR
101 ma ofr 1 [v6.0] CAMA Building Drawing ma001
101 ma ofr 14 [v6.0] CAMA Property Info ma014
101 ma ofr 15 [v6.0] CAMA Sale info ma015
101 ma ofr 17 [v6.0] CAMA Land ma017
101 ma ofr 21 [v6.0] CAMA Value Override ma026
101 ma ofr 26 [v6.0] CAMA Buildings (contains BSE) ma026
101 ma ofr 71 [v6.0] Combined CAMA Prop Info (014) + PC Area (017) + PC Legal (018)
101 na ofr 1 [v6.0] Name & Adress na001
101 na ofr 2 dash [v6.0] Name Summary na_sum
101 na ofr 35 [v6.0] Name Linking na035
101 pc ofr 1 [v6.0] Property Parcel Information pc001
101 pc ofr 2 dash [v6.0] Property Summary pc_sum
101 pc ofr 17 [v6.0] Property Area pc017
101 pc ofr 18 [v6.0] Legal pc018
101 pc ofr 38 [v6.0] Deed pc038
101 pc ofr 39 [v6.0] Combined PC Parcel (012) + PC Property Location (004)

 

 

100-ofr

 

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this.Loading...