100-Queries-(Best Practices for SQL Queries)

Best Practices for SQL Queries

Specify Table and Field Names

When creating SQL queries, it is good practice to specify both the table and the field names in your WHERE query statement.

EXAMPLE 1

The Original query below would function in Govern for Windows, but would result in an error in Govern.NET. In order for it to function
in Govern.NET it is necessary to include the table name in the WHERE statement. This was done in the Corrected example by using a “.” as a
separator.

Read More...

Original

select count(*)
from pm_lk_permit_name,na_company_info
where key_id = permit id
and pm_lk_permit_name.na_id = na_company_info.na_id
and link_type = ‘contr’
and cont_type = ‘SI’
and (in_exp_date < current date or in_exp_date is null)

Corrected

select count(*)
from pm_lk_permit_name,na_company_info
where pm_lk_permit_name.key_id = permit id
and pm_lk_permit_name.na_id = na_company_info.na_id
and pm_lk_permit_name.link_type = ‘contr’
and na_company_info .cont_type = ‘SI’
and (na_company_info.in_exp_date < current date or
na_company_info.in_exp_date is null)

Specifying the table name in the WHERE statement is a good practice to follow when building SQL queries.

EXAMPLE 2

Another example of a query is the following. This is a SELECT query for PRIMARY_LAND

Original
SELECT DISTINCT
I.PRIMARY_LAND
FROM
PC_LEGAL_INFO I LEFT JOIN
PC_AREA A ON A.P_ID = I.P_ID
WHERE
I.P_ID = Parcel ID AND
I.YEAR_ID = Year ID AND
A.FROZEN_ID = Frozen IDCorrected
SELECT PC_LEGAL_INFO.PRIMARY_LAND FROM PC_LEGAL_INFO WHERE
PC_LEGAL_INFO.P_ID = Parcel ID AND PC_LEGAL_INFO.YEAR_ID =
Year ID AND PC_LEGAL_INFO.FROZEN_ID = Frozen ID

Results in the Treeview

Controlling the appearance of Results in the Treeview

One feature of the Govern.NET application is the users ability to control the appearance of results in the Treeview. This feature will appeal to users that perform queries that require that they display a Tax Map as opposed to a Parcel.

FOR EXAMPLE:

In the following query, the Tax Map Number (‘TAX_MAP’) is being replaced with the Roll Number (‘ROLL NO’).

SAMPLE LISTING:

Name: ParcelDesc001
SELECT
CASE
WHEN A.NON_PARCEL_FLAG = -1 AND NON_PARCEL_ID > 0 THEN ‘PREMISE ID: ‘ ||
CAST(A.NON_PARCEL_ID AS VARCHAR(30)) || ‘ ‘ || B.CURRENT_TENANT
WHEN A.NON_PARCEL_FLAG = -1 AND NON_PARCEL_ID IS NULL THEN ‘P ID: ‘ || CAST(A.P_ID
AS VARCHAR(30))
ELSE ‘ROLL NO: ‘ || A.TAX_MAP
END
FROM PC_PARCEL A LEFT JOIN V_PC_OCCUPANT_CURRENT_TENANT B ON A.P_ID = B.P_ID
WHERE A.P_ID = 261362 AND ROWNUM = 1

When the above sample code is incorporated in the query, results with Tax Map numbers will be displayed with the Roll Number.

Related Topics

Query Types
Advanced SQL Queries
Keywords
Best Practices for SQL Queries
Special Cases

100-Queries

 

 

 

100-Queries-(Best Practices for SQL Queries)

 

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-bp-ScheduleReport

Standard Batch Process – Report Scheduler

General

The Report Scheduler is used to schedule a report that is not associated with a batch process but needs to be run on a regular basis. You may, for example, want to run a report every night that does not require any data from a batch process.

  • Process description: Report Scheduler
  • Process name: co_NoProcess

Specification

Prerequisites

  • Batch Process Definition is created and linked to profile
  • SSRS or Crystal Report is configured
  • The same report can be used in multiple batch process definitions

Configuration

Create a batch definition process and select report to be executed

    • When you add the report to the batch process definition, you need to define or update the Export Format Type
    • PDF, CSV, RPT, etc.
  • Add newly created batch process definition to profile

Tools and Tips

Before getting started

  • Take down the report code as it will need to be specified (report selection window does not show description)

 

Troubleshooting

Execute the report stand-alone before setting it up with a batch process definition

 

 

100-bp-ScheduleReport

 

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-bp-TestBatch

Standard Batch Process – Test Batch

General

The Test Batch is used to test the batch execution and configuration, the batch scheduler service, etc.
It creates an Audit Batch ID, generate errors every 1000 records, etc. and records the information in the Audit Batch Table.

  • Process Description: Test Batch Process
  • Process Name: co_TestProcess

Specification

Prerequisites

  • Batch Process Definition is created with the process name
  • Batch Process Definition is linked to a profile

Features

  • Creates error messages for email notification
  • Creates attachment

Tools and Tips

Try first in Govern

  • First use the Test Batch Process in Govern as additional information for errors is provided in the process window (stack trace)

Schedule periodically

  • To validate the service is running

Schedule at specific times

  • After system maintenance, to validate service is up and running
  • After system upgrades, installation, change in reporting or notifications, etc.

Troubleshooting

Any information related to troubleshooting will be presented in this area. Currently there are no troubleshooting entries.

 

 

100-bp-TestBatch

 

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

Govern Impact Assessment

Impact Assessment

Overview

This page is a summary list of changes that occurred in a given version of the product that have an impact on new deployment installation or upgrades.

NOTE: For System Options and/or 3rd Party integrations, it is important to contact R&D as it may not be released at the same time as the module it complements.

Govern OpenForms Version 6

Read More...

 

Version Detail… System Requirements
6.1 Design for MS SQL Server 2014 (to be validated)
6.1 Detail VT_System and VT_User tables will be removed. All the tables are available in VT_SYSTEM and VT_USER. The VT_System table definition
6.0 Table Change Event: New record locking and notification feature. When upgrading, the first time a ‘Verify Database Process’ is run, a script is executed to create a job that requires SysAdmin rights (read-write) in SQL Server. It can be executed manually by the database SA. Script name: CreateCleanupJobForRecentTableChanges. Click here for the MS SQL SERVER SCRIPT commands to execute.
6.0? setup Batch Process Setup Attachment Folders Access: For each batch definition, you can now specify the path and folder where the attachments (logs & output files) are to be copied.
IMPORTANT: These folders need to be shared with Read/Write access granted to the users that will run the batch process AND to the server that hosts the Batch Scheduler Service. (To grant access to a server, use the format SERVERNAME$)
6.0 sysreq Twain for imaging is no longer supported supported by Windows. Replaced by WIA (Windows Image Acquisition).
6.0 Batch Processing Scheduler Windows Service (BSS) is now updated automatically. However, ONLY one (1) deployment connection key can be setup per machine. Appender Service is deprecated.
Database Resources and Data Model
6.1 In prior Govern for Windows release, because of the use of MS Access there was a limitation on the number of attributes in a table.
For this reason, multiple instances of the table were created (ex: LAND, LAND-2, LAND-3…) as well as an additional INFO table (Land_Info, Bldg_Info, etc.)
The following INFO tables have been merged. We will continue to create additional instances, but only after 1000 fields versus 256 as it was before,
6.1 BOECK Appraisal Methodology no longer supported. Table MA_PARM_BOECK is dropped from the standards.
6.1 Simulation Code Removed
6.0 Batch_ID was added to CC_MASTER
6.1 PC_PARCEL NON_PARCEL_FLAG and VIRTUAL PARCEL changed to Non Nullable
6.0 USR KEY MASTER Changes (impact to be verified)
6.0.1703 CREATE_DATE and CREATE_UID was added to the USR_MESSAGE (Global Messages)
Query Tool
6.1 Because of the changes in the VT system and user table, many if not all queries will need to be reviewed if not rewritten.
Mass Appraisal
6.1 Formulas and Logical Expressions are no longer supported. Replaced by expressions.
6,1 Multiple Assessment Rate (unavailable in 6.1)
6.1 Property Distance Weight (unavailable in 6.1)
6.1 Simulation code is remove. With 6.1 simulation will be done via the EMT tool.
6.1 Site records are now mandatory for all appraisal components (land, building, misc. structures, income, personal property, MRA).
The site description is setup in the query MASITELIST and will most likely be the concatenation of other fields. Split merge will group all the components by site description.
For instance Site Description = Class + Liability + Exemption
6.1 Total Override feature and new field for override amount added to land, land adjustments, bldg, misc. structures, income, mra, and personal property. Note: The reports will need to be changed because the total value if overridden will be located in TOTAL_VALUE_OVERRIDE instead of TOTAL_VALUE
6.1 MA APPRAISERS not available in 6.1.1511 See roadmap
6.1 Miscellaneous Structure not available in 6.1.1511 See roadmap
6.1 CAMA Exemptions not available in 6.1.1511 See roadmap
6.1 Copy MA Tables to Next Year not available in 6.1.1511 See roadmap
6.1 The Is Required option in mass appraisal tables will now use an expression. A new field was created to facilitate the conversion. [MA_TABLES.IS_REQUIRED_EXPRESSION]
6.1 Rename Sequence (Land, Building, MRA ..) not available in 6.1. See Roadmap
MA Income Simulation Table is no longer supported. Income method has changed.
6.1 Neighborhood Table can be set to work with jurisdictions. In lookups, the system will only show the neighborhoods for the property jurisdiction. See What’s New
Computation Sequences were changed from a small integer to a long integer. Will be formatted with 6 digits on the forms 999999.
6.1 Copy Land Record to Parcel or Dataset. It now copies the Lot Size, Lot Units Type, Units, and Unit Type where before it did not.
6.1 Formatting of MA PARM Table Values was added for the land, site, mra (adjustments) and building (elements). See What’s New and GNA Value Formatting Editor
6.1 PID (Current_PID) was added to the MA_BUILDING table. This impacts splits and merge as well as parcel duplication. A corrective script will be available the first time the Verify Database is run.
Tax Billing and Assessment
6.1.1611 TIF implementation (New Tables and Columns)
6.1.1611 Levies and Exemptions now use Expressions.
[6.1] The TIF levy parameter will always be auto-created in code. The user will not be able to see the TIF levy parameter on GNA. The only way to view a TIF levy parameter is to go in the database with the following query:
select*from TX_PARM_LEVY
where IS_TIF_LEVY =-1select*from TX_PARM_EXEMPT
where IS_TIF_EXEMPTION =-1
Name and Address (ACH Processing)
6.0.1609 ar-wnew ACH processing available in this version. A new field was added for the Transit and Bank Sequence Description, Field sizes have changed for encryption
NA_ACH_INFO.ACH_SEQ (new field)
NA_ACH_INFO.BANK_ACCOUNT_NO (new field lenght = 44)
NA_ACH_INFO.BANK_TRANSIT_NO (new field length = 32)
6.0.1609 New Standard System Code in MAILING INDEX TYPE
Cash Collection
6.0.1603 Cash Collection module is available on OpenForms.
6.0 PAYOR_NAME added to CC_MASTER (was stored previously in CC_MASTER_NOTES)
6.0 Proportional Distribution is no longer available (Deprecated)
6.0 USR Table CC_TYPE transferred to SY TABLE CC_TYPE
6.0 CC_ID is now a Govern ID
6.0 Batch_ID was added to AR BATCH INFO
6.0 New Log files for Payment Processors WEB and Point of Sale See 317-CSI Pay
Standards Forms and Features
6.1.1503 New Roll Forward Exclusions Function
6.1 Name Belongs To Group Security does is not functional in 6.1 (see TFS 15286-15473)
5.1 My workload deprecated until the new Land Management Module is released (see 101-std-fea-019)
GNA Editors
6.1 External Command Editor no longer supports Formulas and Logical Expressions. The commands will need to be recreated using the Expression Editor.
6.1 Formulas and Logical Expressions are no longer supported. Replaced by Expressions.
GIS Integration
6.0.1511 The GIS integration now uses the Standard Application Integration Browser (frm-005).
GNA GIS Parameters, no longer needed, was removed.
GNA GIS Synchronizing Tool was modified to use a GUID format for MATIX Integration.
6.0.1511 Tool Tips are not available in version 1511.
GNA Tool Tips, no longer needed, was removed.
6.0 PC Lien Certificate default version
The lien certificate function is not part of tax title but an extract function used for reporting all balances due for a given property.
This function is not on the roadmap because it is customized for each customers or State, and the default version in VB6 was not used.
6.1 Miscellaneous Billing
6.1.1606 General Availability of OpenForms Module
Changes were made to the Database Structure to support year base parameters and new parameter tables were added (MB_PARM_CATEGORY and MB_PARM_ITEM)
Mailing Index is now used and created automatically
Workflow Management (Previously Permits & Inspections)
6.1.1609 wm-wnew Many changes were made to the Data model. Please review the Workflow Management Install page for a checklist to review.
6.1.1609 USR_KEY_MASTER is replaced by USR_KEY_DYNAMICS (to support the Mobile option)

Version 6.0

Considered an hybrid version, it uses Govern release 10.8 Daemon for Mass Appraisal Computation.

Version Detail System Requirements
— See 6.1/6.0 above
Database Resources and Data Model
6.0 gna Initial DB creation and tables, attributes and index updates performed by GNA Verify Database function
6.0 dbchg See Obsolete Fields (database changes)
6.0 fea-017 Std-Fea What’s new
User Audit Trail Tables (3 new tables)
6.0 dbchg User Added Field Definition table change. See module SRT, PP, …to be validated
6.0 dbstd New Standard type of View
6.0 MA_BLDG_AREA removed and a new table added
Core Standards and Business Rules
6.0.1503 Roll Forward Exclusion – Configuration Screen is now available. Roll Forward will be modified at a later time. See Govern Admin Application Parameters.
Formulas and Logical Expressions in 6.0 (only) – Need to add the NO LOCK statement
Datasets, PC EXTERNAL, new Synchronize function
Property Control
6.0 Table PC_SALES is no longer used. MA_SALES will be used
Accounts Receivable Security
6.0.1503 Security for AR transaction was setup in GSM in prior releases. In version 6.0, the security setup is made in OFD.
6.0.1503 na-wnew ACH information centralized in NA NAMES and removed from the tax module assessment tables
A/R Cycles are now Mandatory for Real Estate, Personal Property, Boat, Aircraft and Motor Vehicle (need to validate – see roadmap)
Real Property
Personal Property
6.0.1503 pp-wnew New Version 6 Module
A/R Cycle is now mandatory
Self Reported Business Tax
6.0.1609 tech doc
setup		 Changes to support Estimated Filings (Garnishments)
New column NAICS created in ST_MASTER
New table ST_ESTIMATE
New form ST008
6.0.1606 Corporate Account Table created ST_CORP_ACCOUNT
6.0.1503 st-wnew New Version 6 Module (see Upgrade technical Information)
6.0.1502 st-wnew Exemptions linked to levies are no longer available
Mass Appraisal
6.0.1503 MA_BLDG_AREA was transferred to MA_BLDG_AREA_YEAR. Year of the area was added. The data will be converted in the verify database, but not the reports.
The Daemon was changed for Clay County and SAMA. More information will be provided on how to implement the new feature.
6.0.1503 gna Verify Database Process
NOTE: One of the recently updated scripts in the Verify Database Process will require SysAdmin rights in SQL Server.
Script Name: CreateCleanupJobForRecentTableChanges:

System Options and 3rd Party

System Options and 3rd Party Integration may or may not be released at the same time as the module. Please check with R&D.

Version ……… Detail ……. System Option
6.0.1503 e-Government General Availability release
6.0.1503 Mobile Option General Availability release for MA, SRT and RE (to validate)
3rd Party Integration
6.0. 301-apex APEX available. See Building Drawing
6.0.1503 Melissa Integration (to be verified)
6.0.1503

 

NOTE: 3rd Party upgrades to new versions is not part of the default roadmap. These upgrades are normally driven by customer project or general functionality needs.
Please contact R&D and make sure to review system (new 3rd party version to be installed or …) and functionality requirements with customer as this may change.

Govern for Windows to OpenForms

Major technology changes from Govern for Windows to OpenForms. Some features no longer applies, some were changed, some will be available in a future release, etc.
For the complete report prepared by R&D, please click Feature Compare VB6 to V6 (Last Updated April 7th). As the software evolves, the additional changes will be added here:

 

 

100-ImpactAssmt

 

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-encryption

Encryption Methodology

Version 6.0/ Version 6.1

Overview

A new standard encryption method was implemented in OpenForms 6.1.
For compatibility purposes, changes were made in the Business Entity Designer in order to select which encryption mode to use.
A Correction Script is provided to convert the data.

Encryption Mode

100-Encryption-ui-mode.png

Business Entity Designer Attribute Properties

 

The options include:

Legacy3

  • Compatible with Govern for Windows

Legacy5

  • Compatible with Govern for Windows

TripleDES

  • New Encryption mode available for OpenForms GA 1609 in version 6.0+

None

  • No Encryption – System Default

Configuration

The setup is made by attribute in the Business Entity Designer (BED).
The following are the standard OpenForms business entity attributes encrypted:

ACH Information by Name Entity

NA_ACHINFO

  • BANK_ACCOUNT_NO
  • BANK_TRANSIT_NO

 

Miscellaneous Correction Script
A Miscellaneous Correction Script is provided when executing the GNA – Utility Verify Database.
Upon execution, a warning message will be displayed.
When double-clicking the message, the warning along with an hyperlink to execute the Miscellaneous Script will be presented.
Should be run with care!
See also:

Troubleshooting

The User Password located in the USER MANAGEMENT users is encrypted.
Table USR_USERFILE

  • PWD

Please note that the User Password is not used in Govern OpenForms 6.1 except in certain circumstances:

  • Administrator Login Action Button (To Be Validated)
  • Deployment of Type GOVERN instead of MSAD (Validate: do we encrypt in 6.1 – was encrypted in Security Manager 6.0 and less?)

If the new encryption mode is used, and the circumstances can be met, it is recommended to open and make a modification to the users in order to RE-SAVE the records with the new encryption mode.

 

 

100-encryption

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-WM

Govern Installation from Scratch – Workflow Management

Version 6.1 (In Development)

Overview

This section covers the steps and the best practices when installing Workflow Management 6.1 from scratch.
For upgrades or updates see 100-Upgrade where you will find tools, tips and training material to help with the upgrade.
Click on Installation 100-Install to return to the main Installation page.

  • Note to Govern Employees : See Test Case 100-Install from Scratch Workflow Management (20238)

Step by Step

The following steps need to be accomplished to install and configure Workflow Management.

1- Create Empty Database

  • Create new db, deploy and initialize database (run Verify Database twice)

2-Create Departments

  • In GNA Parameters, Department Maintenance, create 2 new departments with a multimedia-code:
    • d1: Dept 01 by PID (A/R….)
      • Sub-System Allowed (Land Management, Tax Title, Miscellaneous Billing…)
      • Multimedia Code (d1-Memo) – Not shared with other departments (i.e. Information Private to Department)
    • d2: Dept 02 By Name
      • Sub-System Allowed (Land Management 2)
      • Multimedia Code (pdf) and Multimedia Code (Stored in Database

3-Set A/R Sub-System Configuration for Workflow

  • in GNA Application Configuration, select A/R General Parameters and click on Land Management in Related Sub-System Parameters
  • Select Link A/R to Names option
TIP: Configuring the Workflow
– When saving the Kind and Category parameters, the save applies to the Kind dependencies (Categories and their added fields).

 

4-Configure Workflow Kind & Categories

  • in GNA, Application Configuration, select Workflow Management, Kind & Categories

 

 

100-Installation-FromScratch-WM

 

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-install-SMTP4Dev

Test SMTP Server Installation

Overview

For testing purposes, you can install SMTP4Dev, a dummy Simple Mail Transport Protocol (SMTP) server that sits in the system tray and does not deliver the received messages. This Open Source Software is distributed by Code Plex.
To download and get more information, access the Code Plex website smtp4dev.codeplex.com

Downloading

You can download the SMTP4Dev server from Code Plex (Open Source Distributor)
www.smtp4dev.codeplex.com

Harris Govern Employees, can copy from the Shared Program Folder
FSERVERsharedPGSMTP4Dev

Alternatively, when testing on the Harris Govern server, you can use the Harris e-mail Server.
Contact Govern Support for the SMTP server information account information.

Installation and Setup

Read More...

Once downloaded and installed (double click on the documented downloaded), you will see an email icon in the system tray or on your desktop.
RIGHT-CLICK on the icon, and select OPTION in the menu
On the Server Tab, you need to setup:

Port Number

  • Default is 25 but it can be changed if already used.

Listen Interface

  • Select 0.0.0.0 for all or 127.0.0.1 for local

The same information needs to be setup in Govern Administrator, Parameters, General Settings Editor, Filter = General, Section Name = SMTP

FOR DETAIL INFORMATION and print screens, see SMTP4Dev Setup Presentation

See Also

106-(tech)-SMTP Server for Testing Purpose (smtp4).docx

Page Date Edited
No Links

 

 

100-install-SMTP4Dev

 

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