GNA – Reporting in OpenForms (Developers)

Reporting in OpenForms (Developers)

Overview

The following page is intended for developers.
For additional information, see Standard Feature Reports in Govern (std-fea-013) and Report Editor and Report Criteria setup in Govern Administrator.

Specifications

The following standard features are available for reporting

Standard and Custom Reports Definitions
SAP Crystal Reports and Crystal Reports server are supported
- Use of GovernActions in Crystal ReportsTM
Microsoft SQL Server Reporting Services are supported
Reports are setup by Profiles
Security can be setup by Profile User and Role
Run-Time parameters (required and not required) and selection criteria can be setup by report definition
User persistent run-time parameters by user (parameters and criteria entered are saved by user)
User defined output report formats
Reports can be executed automatically upon a batch definition execution (after batch process completion)
Reports can be scheduled using Govern Scheduler
Reports output format can be configured

Read More...Read Less...

System Registry Parameters

Default paths and server information are stored in the system registry.

To access, in GNA, select Parameters, OpenForms General Settings.
REGISTRY FILTER = REPORTS

Use Crystal Report
Use SSRS
Standard Report Path
Custom Report Path
Standard Log Path – deprecated in 6.1 – now specified at the report level
SSRS service and connection information

Report Definition Editor

There is an underlying intelligence in the functioning of the Reports editor.
When setting up a report, both SQL Server Reporting Service (SSRS) and Crystal Reports reports are read, and the data sources are scanned.
On run-time, connection key(s), parameters and predefined criteria are used to execute the report.
Multiple report definitions can be configured for the same report.

The report definition contains:

Report Name
- Physical report name selected from the standard or the custom report path
Audit Trail
- deprecated in 6.1 – special action can be used
Recordset Key to use
- Govern IDs
Criteria Key
- User Selection Report Criteria Prompts that can be configured using any of the fields of tables used in the report
Alternate Connections Key
- The deployment connection key is used when executing a report. In some cases, a secondary connection key can be provided
Report Group – deprecated in 6.1 – now linked to profiles

Report definitions are setup in Govern Administrator.

To access, in GNA, select Parameters > Report Editor Parameters
For detail information, see Report Editor and Report Criteria

Report Parameters

The following are standard report parameters

Parameter Name	Value returned
Govern ID code	Govern IDs (p_id, na_id, wm_id, etc.)
dept	Ids.Dept
dept_label	Vt_user WHERE tableName=’depart’ AND code=Ids.Dept
language	Cuurent set language
year	Ids.Year
rpt_county	Sy_registry WHERE sectionName=‘general’ AND keyName-‘county’
rpt_date	‘ShortDate LongDate’ (as string)
rpt_default_date	‘ShortDate’
rpt_login	Ids.User
rpt_logo	Path : ‘{RegistryVaue}ReportCompagnyLogo.bmp’ Registry Value is found in the User/System Regostry using those rules: Try to get value from usr_registry WHERE sectionName=’govern’ AND keyName=’bitmap’ If no value found, fall back on sy_registry WHERE sectionName=’path’ AND keyName=’bitmap’
rpt_name	Report Path
rpt_range	Return, as string, all the filter dynamically applied on the Report
rpt_state	Sy_registry WHERE sectionName=’general´AND keyName=’state’
rpt_title	Report description (as set in GNA)
rpt_ub_max_variation	Sy_Registry WHERE sectionName=ub parameters’ AND keyName=’Max Variation’
rpt_uid	Ids.User
rpt_username	OrganizationName set in GovernNetConfig.xml
uniform	Sy_registry WHERE sectionName-‘work_text’ AND key_name=’Assessment Equalisation Rate’

Formulas for Crystal Reports

This section lists the recommended, general, read-only and conditional formulas to include when creating reports through Crystal Reports.

Recommended Formulas

Include the following formulas when you create Crystal Reports:

Formula	Definition
@rpt_date (totext (currentdate))	Date & Time
@rpt_name	Report Name
@rpt_Username	Town or Company Name

General Formulas

Govern populates the following Crystal Reports formulas automatically, if they are included in your reports:

Formula	Definition
@dept	Department (code)
@dept_label	Department label
@language	Language
@rpt_county	County
@rpt_default_date	Default Date
@rpt_login	User name
@rpt_range	Range Text
@rpt_state	State
@rpt_title	Report Title
@rpt_uid	User identification code (UserID)
@uniform	Assessment Equalization Rate
@year	Fiscal year in use by current user in Govern

Read-Only Formulas

The following Crystal Reports formulas are read-only; i.e., they must contain a non-blank value in the report. These formulas trigger specific actions or populate specific formulas:

Formula	Action
@acbill	Refreshes the AC_POST_MASTER table with the current Aircraft ID or the current Name ID (in duplicate bill printing).
@all_ar_needed_ac	Populates the value of several formulas for the current NA_ID and for the all Tax sub-systems. See Conditional Formulas.
@all_int (under revision)	Updates the following fields in the AR_ SUMMARY table for all installments with positive balance, for all Taxation and Assessment sub-systems and for the current NA_ID. (All updated installment records within a sub-system will contain the same information). LATE_CHARGES (Accrued Penalty or Interest) CHARGE_DATE (Current date as defined in Govern) BALANCE_AS_OF_DATE (Total balance due, exclusive of late charges)
@btbill	Refreshes the BT_POST_MASTER table with the current Boat ID or the current Name ID (in duplicate bill printing).
@drilldown	Enables Crystal Reports Drill Down feature
@govern_tx_interest *This formula is used when calling a Web Crystal Report	Updates the LATE_CHARGES column in the AR_SUMMARY table. Use when calling a Web Crystal Report in the Real Property Tax Subsystem.
@govern_ub_interest *This formula is used when calling a Web Crystal Report	Updates the LATE_CHARGES column in the AR_SUMMARY table. Use when calling a Web Crystal Report in the Utility Billing Subsystem.
@mv_ar_needed_ac	Populates the value of several formulas for the current NA_ID and for the Motor Vehicle Excise Tax sub-system. See Conditional Formulas.
@mvbill	Refreshes the MV_POST_MASTER table with current Motor Vehicle ID or the current Name ID (in duplicate bill printing).
@mv_int	Updates the following fields in the AR_ SUMMARY table with motor vehicle excise tax information, for all installments having a positive balance for the current MV_ID. (All updated installment records will contain the same information). LATE_CHARGES (Accrued Penalty or Interest) CHARGE_DATE (Current date as defined in Govern) BALANCE_AS_OF_DATE (Total Balance Due, excluding late charges.)
@pp_ar_needed	Populates the value of several formulas for the current PP_ID and for the Personal Property Tax sub-system. See Conditional Formulas.
@pp_ar_needed_ac	Populates the value of several formulas for the current NA_ID and for the Personal Property Tax sub-system. See Conditional Formulas.
@ppbill	Refreshes the PP_POST_MASTER table with the current Personal Property ID or the current Name ID (in duplicate bill printing).
@pp_int	Updates the following fields in the AR_ SUMMARY table, for the current PP_ID, with Personal Property Tax information, for all installments having a positive balance. (All updated installment records will contain the same information). LATE_CHARGES (Accrued Penalty or Interest) CHARGE_DATE (Current date as defined in Govern) BALANCE_AS_OF_DATE (Total Balance Due, excluding late charges)
@re_ar_needed	Populates the value of several formulas for the current P_ID and for the Real Estate Tax sub-system. See Conditional Formulas.
@re_ar_needed_ac	Populates the value of several formulas for the current NA_ID and for the Real Estate Tax sub-system. See Conditional Formulas.
@re_int	Updates the following columns in the AR_SUMMARY table, for the current P_ID, with Real Estate Tax information, for all installments having a positive balance. (All updated installment records will contain the same information). LATE_CHARGES (Accrued Penalty or Interest) CHARGE_DATE (Current date as defined in Govern) BALANCE_AS_OF_DATE (Total Balance Due, excluding late charges)
@td_ar_needed	Populates the value of several formulas for the current P_ID and for the Tax Deferral sub-system. See Conditional Formulas.
@td_int	Updates the following fields in the AR_SUMMARY table for all installments having a positive balance, with Tax Deferral information for the current P_ID. (All updated installment records will contain the same information). LATE_CHARGES (Accrued Penalty or Interest) CHARGE_DATE (Current date as defined in Govern) BALANCE_AS_OF_DATE (Total Balance Due, excluding late charges)
@tt_ar_needed	Populates the value of several formulas for the current P_ID and for the Real Property Tax Title sub-system. See Conditional Formulas.
@tt_int	Updates the following fields in the AR_SUMMARY table, for the current P_ID, with Tax Title/Lien information, for all installments having a positive balance. (All updated installment records will contain the same information). LATE_CHARGES (Accrued Penalty or Interest) CHARGE_DATE (Current date as defined in Govern) BALANCE_AS_OF_DATE (Total Balance Due, excluding late charges)
@txbill	Refreshes the TX_POST_MASTER table with the current Property ID or the current Name ID (in duplicate bill printing).

Conditional Formulas

The Crystal Reports formulas listed in this table are populated by Govern conditionally, on the presence of the following read-only formulas:

@all_ar_needed_ac
@mv_ar_needed_ac
@pp_ar_needed
@pp_ar_needed_ac
@re_ar_needed
@re_ar_needed_ac
@td_ar_needed
@tt_ar_needed

Formula	Action
@ar_interest_1 @ar_interest_2 @ar_interest_3 @ar_interest_4	Calculates the interest and penalties committed for the current fiscal year and the specified installment.
@current_due	Calculates the total balance due for the current fiscal year, including late charges.
@daily_interest	Calculates the daily interest and penalties.
@prior_ar_fee_1 @prior_ar_fee_2 @prior_ar_fee_3 @prior_ar_fee_4	Calculates the demands, charges and fees committed for prior fiscal years and the specified installment.
@prior_ar_interest_1 @prior_ar_interest_2 @prior_ar_interest_3 @prior_ar_interest_4	Calculates the interest and penalties committed for the prior fiscal years and for the specified installment.
@prior_due	Calculates the total balance due for the prior fiscal years, including late charges.

Tables/Fields and Naming Standards

Report File Name

Physical report name available in the standard or custom report path
Report File Name Maximum Length is 15
Recorded in VT_SYSTEM where table_name = REPORTS

Report Options

Standard report parameters that can be used in the report (ex: Department, City/Town Name, Report Title, etc.)
Recorded in VT_SYSTEM where table_name = RPT_OPT

Report Record Set Keys

Standard Govern IDs located in table VT_SYSTEM where table_name = BRULE

Report Definition

Report definitions are stored in table USR_REPORT_DEF
Table contains:
- File Name (available in Standard or Custom report paths)
- Audit Trail – deprecated?
- Record Key
- LK_Brule

Batch Processing and Reports

Parameters defined for a batch process can be used by reports.

Click to enlarge

Batch Process On Completion Report

It is possible to setup a report to be executed upon a batch process definition completion.

Batch Process to Schedule Reports

It is possible to setup a batch process definition to schedule reports.

Report Formats

You can specify the output format of a report that will be generated by a batch process. Verify with IT for the list of file formats allowed for the organization.

Batch Process Parameters

The parameters defined for the batch process can be accessed by the report.

Standard Reports List

The following is a list of the Standard Reports that are available in Govern.

Standard Reports

Utility Billing (In Development)

101-ub-rpt-001-UBSummaryCard (UBSumCard.rpt)
101-ub-rpt-002-GenericUBBill (GenUBBill.rpt)
101-ub-rpt-003-WaterLien-PrelimDetail (WatrLnPrDtl.rpt)
101-ub-rpt-004-WaterLien-Summary (WatrLnSum.rpt)
101-ub-rpt-005-WaterLien-NotSubjectToLien (WatrLnNSTLn.rpt)
101-ub-rpt-006-UBReadingHistory (UBRdHist.rpt)
101-ub-rpt-007-UBReadingEditList (UBRdEdList.rpt)
101-ub-rpt-008-ComputationRegisterTentative (CompRegTent.rpt)
101-ub-rpt-009-ComputationRegisterPosted (CompRegPost.rpt)

Accounts Receivable / Cash Collection / Misc. Billing

Property Control

101-pc-rpt-001-OwnerLabels

Tax

Workflow Management

Troubleshooting

A new installation pack for Crystal Report^TM is required to be installed on each client when upgrading to GA1706.

What’s New

Workaround to resolve printing issues with Landscape orientation Crystal Reports

100-Reporting_Dev

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

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.

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

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 5)
You need to be a registered member to rate this.

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 5)
You need to be a registered member to rate this.

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 5)
You need to be a registered member to rate this.

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.

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

100-Installation-FromScratch

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

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...Read Less...

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

Reporting in OpenForms (Developers)

Overview

Specifications

System Registry Parameters

Report Definition Editor

Report Parameters

Formulas for Crystal Reports

Recommended Formulas

General Formulas

Read-Only Formulas

Conditional Formulas

Tables/Fields and Naming Standards

Report File Name

Report Options

Report Record Set Keys

Report Definition

Batch Processing and Reports

Batch Process On Completion Report

Batch Process to Schedule Reports

Report Formats

Batch Process Parameters

Standard Reports List

Standard Reports

Utility Billing (In Development)

Accounts Receivable / Cash Collection / Misc. Billing

Property Control

Tax

Workflow Management

Related Topics

Troubleshooting

What’s New

Initialize Mobile Database

Overview

What’s New!

Exclude Database Objects during Initialization (Manual process)

GNA User Interface Changes

Functionality

Requirements

The Process

1 – Populate the Table Manually

2 – Verify the Content of the Database

3 – Run the Initialize Mobile Database Process

Tables

Add and Update User tables during Initialization and Get Latest

Table Details

Requirements

Populating the Tables Manually

1 – Determine the Structure of the Tables

2 – Design and run the INSERT Query

3 – Verify the INSERT Query

3 – Run Initialize Mobile Database Process

3 – Synchronize on the Mobile Application

Tables

See Also

GNA Conversion – Mass Appraisal Parameters

Overview

Process Steps

User Options (UI)

Selection

Recommendations

MA Single Entry Tables – Income Added Items by Category

Overview

Scripts

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

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

Overview

Property Control

Parcels (Property Info)

Parcel Linking

Buildings

Building Linking

Names

Property Locations

Mass Appraisal

MA Property Information

MA Sites

Linked to INCOME or MRA

Property Control (PC)

Real Property Tax (TX)

Mass Appraisal (MA)