MS SQL DB Functions Accela Automation Server Database Function Reference Guide

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 38

Accela Automation
™
SQL Server Database Function Reference Guide
Accela Automation SQL Server Database Function Reference Guide
© 2010 Accela, Inc. All rights reserved.
Accela, the Accela logo, the Accela logo with “Government Software” notation, Accela Automation,
Accela Asset Management, Accela Citizen Access, Accela Mobile Citizen Access, Accela ERS, Accela
GIS, Accela IVR, Accela Land Management, Accela Licensing, Accela Mobile Office, Accela Public
Health and Safety, Accela Service Request, Accela Wireless, Kiva DMS, Kiva Development
Management System, 'PERMITS' Plus, SiteSynch, Tidemark Advantage, VelocityHall, Vantage360,
and other Accela logos, devices, product names, and service names are trademarks or service marks of
Accela, Inc. Brava! Viewer is a trademark of Informative Graphics Corporation. Windows is a
registered trademark of Microsoft Corporation. Acrobat is a trademark of Adobe Systems Incorporated.
All other company names, product names, and designs mentioned herein are held by their respective
owners.
April 7, 2010
Corporate Headquarters
2633 Camino Ramon
Suite 120
Bishop Ranch 3
San Ramon, CA 94583
Tel: (888) 722-2352
Fax: (925) 659-3201
www.accela.com
Accela Automation SQL Server Database Function Reference Guide
– iii –
TABLE OF CONTENTS
Introduction to Database Functions v
Accounting Information...................................................................................7
FN_GET_INVOICED_FEE_TOTAL.......................................................7
FN_GET_JOB_VALUE ...........................................................................8
Address Information ........................................................................................9
FN_GET_ADDRESS_ATTRIBUTE........................................................9
FN_GET_PRI_ADDRESS_FULL............................................................9
FN_GET_PRI_ADDRESS_PARTIAL...................................................10
Application Information.................................................................................12
FN_GET_APP_SPEC_INFO..................................................................12
FN_GET_APP_SPEC_INFO_BYGROUP.............................................13
FN_GET_ASI_TABLE_VALUE ...........................................................14
FN_GET_PARENT_APP .......................................................................15
FN_GET_CONST_TYPE_DESC...........................................................15
FN_IS_CONDITIONS_MET .................................................................16
FN_GET_HEARING_LATEST .............................................................16
Contact Information.......................................................................................18
FN_GET_CONTACT_ATTRIBUTE.....................................................18
FN_GET_CONTACT_INFO..................................................................19
Inspection Information...................................................................................22
FN_GET_INSP_COMMENT.................................................................22
FN_GET_INSP_LATEST.......................................................................22
Owner Information.........................................................................................25
FN_GET_OWNER_INFO......................................................................25
Parcel Information .........................................................................................28
FN_GET_PARCEL INFO ......................................................................28
FN_GET_PARCEL_NBR.......................................................................28
FN_GET_PARCEL_NBR_ATTRIBUTE ..............................................29
Standard Choices ...........................................................................................31
FN_STDCHOICE_VALUEDESC..........................................................31
Workflow Task Information ..........................................................................32
FN_GET_TASK_EARLIEST.................................................................32
FN_GET_TASK_LATEST.....................................................................33
FN_GET_TASK_SPEC_INFO_CS2......................................................34
FN_GET_TASK_STATUS.....................................................................35
FN_GET_TASK_STATUS_DATE........................................................36
Accela Automation SQL Server Database Function Reference Guide
– v –
INTRODUCTION TO DATABASE FUNCTIONS
his guide provides detail on the twenty six critical reporting database functions supported by
Accela Automation.
MS SQL Server and Oracle Accela Automation databases support the database functions
described in this guide. These functions are also universal to all report development tools that
support MS SQL Server and Oracle database functions including Accela Report Writer, Crystal
Reports, Oracle Reports, and Microsoft Reporting Services. This guide describes how to use
functions in the MS SQL Server Accela Automation database.
The database functions are broken up into types of information that can be retrieved from the
Accela Automation database. This might assist you in finding the information you are looking for
when developing a report. The functions under each section display in alphabetic order to easily
navigate to a specific function.
Each function includes a brief explanation about the information returned and how it behaves. The
function call and the arguments required to execute a function are provided as well as the order the
arguments display in the list. The order or the arguments provided in the function call is set and
must be in the correct order or it will error or provide incorrect results. The details for the values
returned is also provided. This includes the label from the default settings for each associated
portlet or window from the Accela Automation products, the database field name, and a brief
description. This is helpful when retrieving the results and organizing the presentation in your
report.
The functions explained in this guide reflect the default settings and database fields that are
delivered with new installations. The Accela Automation product and the tasks performed depend
on your agency’s implementation package and the way your system administrator set up you
system, including access permissions assigned to your user ID. Some of the functions in this guide,
may not be applicable to your agency.
This guide assumes that you have a working knowledge of the Accela Automation database
including how database functions behave and how to work with reporting tools. If you not familiar
with these concepts and products, refer to your database and/or reporting documentation. This
guide also assumes that you have a working knowledge of your agency’s permitting system. If you
do not, see the user or administrator guide that came with your permitting system.
This reference guide includes the following types of functions:
• Accounting Information
• Address Information
• Application Information
• Contact Information
• Inspection Information
• Owner Information
• Parcel Information
• Standard Choices
• Workflow Task Information
T
Introduction to Database Functions
Accela Automation SQL Server Database Function Reference Guide
– vi –
Typographic Conventions
This guide uses the following typographical conventions:
Related Publications
Accela provides documentation for your permitting system. For information on the fields returned
from the database functions in this reference guide, refer to the appropriate user or administrator
guide:
Administrator Guides:
Accela Automation Administrator Guide
Accela Automation Vantage360 Administrator Guide
Accela Report Designer
Accela Report Object Designer
Blue text When reading this guide in PDF format, click the blue hyperlink to
access the related section of documentation. For example:
For instructions on changing your password during an Accela
session, see “Changing your Password at Log In” on page 49.
This blue hyperlink text displays within sections, steps, lists, or tables
in all chapters of this guide.
In addition, all information in the Table of Contents, Parts, Lists of
Figures, and Index are hyperlinks associated sections of
documentation. To avoid overuse of the blue text on these pages of
the guide, the hyperlink text in these sections displays in black font.
Bold text In step-by-step instructions, bold sans serif indicates names of
menus, buttons, and fields. For example,
Click the Submit button.
“” In step-by-step instructions, quotation marks around text indicates
what you need to enter in a field. For example,
Enter “Pending” in the Status field.
Courier font Courier font indicates configuration file content, command line
text, SQL statements, or other similar information that must be
entered for installation or configuration. For example,
INSERT INTO B1PERMIT
Italic Text in italic indicates the results of an action in an Accela product.
For example,
Click the Save button.
Accela Automation saves the changes you made.
Introduction to Database Functions
Accela Automation SQL Server Database Function Reference Guide
– vii –
User Guides:
Accela Automation User Guide
Accela Automation Land Management User Guide
Accela Automation Asset Management User Guide
Obtaining Technical Assistance
As a starting point for all technical assistance, visit the Accela Customer Resource Center (CRC)
site at http://www.accela.com/support/support_overview.asp. At this site you can search the
knowledge base to find answers to commonly asked questions about our products and register at
the Accela Forum to join in an information exchange with other Accela users.
If you still have questions after visiting Accela’s CRC site, or if you encounter any problems as you
use the product, contact your system administrator. If you determine that you need professional
technical assistance, have your agency’s designated contact call the CRC at (888) 7-ACCELA, ext.
5 or (888) 722-2352 ext. 5. The Accela CRC is available Monday through Friday from 6:00 AM to
6:00 PM Pacific Daylight/Standard Time.
Before calling, please have this information available for the CRC representative:
• The Accela product name and version number
• Steps to replicate the issue, including any error message or error number
• Screenshots, if possible
• Whether the problem is specific to a machine or to a user
• Exactly when the problem began
• Anything that changed on your computer or your network (for example, was new software
loaded?)
• A copy of your configuration file, if appropriate
Documentation Feedback
Accela wants to provide you with the most accurate and useful documentation possible. We
welcome your feedback in helping us improve future versions of this guide. Send an e-mail
message with your feedback to documentation@accela.com. Please include the product name and
version number, whether the feedback involves a printed manual or online help, the topic title, and
a brief description and context of the suggestion.
Accela Automation SQL Server Database Function Reference Guide
– 7 –
Accounting Information SQL Server
Accounting Information
The accounting functions are used to retrieve accounting information such as fee item totals and
contractor job values for an application.
This section includes the following functions:
• FN_GET_INVOICED_FEE_TOTAL
• FN_GET_JOB_VALUE
FN_GET_INVOICED_FEE_TOTAL
This function retrieves and returns the sum of invoiced fees for an application. The fees returned
can be filtered based on the Fee Item Status or Fee Item Description. This function may be used to
return the sum of new (not invoiced) fees instead; see FeeItemStatusFlag in List 1:
“FN_GET_INVOICED_FEE_TOTAL arguement details on page 8.
Function Call
FN_GET_INVOICE_FEE_TOTAL (CLIENTID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, FeeItemStatusFlag, FeeItemDescription)
For details on the arguments in this function, see List 1, “FN_GET_INVOICED_FEE_TOTAL
Argument Details,” on page 7.
Returned Results
Sum of fees for an application (SUM{GF_FEE}),
List 1: FN_GET_INVOICED_FEE_TOTAL Argument Details
ClientID This is equivalent to ServProvCode. This is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1 The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2 The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3 The last 5 characters for the Universal Tracking Number.
FeeItemStatusFlag
(optional) The fee item status. This is optional and is used to filter the type of
fee items to total. Options: ‘NEW’, ‘INVOICED’,
‘VOIDED’,’CREDITED’. If set to ‘NEW’, function returns sum of
fees that rea assessed but not invoiced. If set to ‘NULL’, invoiced
fee items are used.
FeeItemDescription
(optional) The fee item description. This is optional and is used to filter fee
items to total by their description. If set to ‘NULL’, fee items are
summed regardless of description.
Accounting Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 8 –
FN_GET_JOB_VALUE
This function retrieves and returns the contractor job value or calculated job value depending on
which is selected for the application.
Function Call
FN_GET_JOB_VALUE (CLIENTID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3)
For details on the function arguments in this function, see List 2, “FN_GET_JOB_VALUE
Argument Details,” on page 8.
Returned Results
Contractor Job Value {G3_VALUE_TTL}or Calculated Job Value (SUM {B1_VALUE_TTL})
List 2: FN_GET_JOB_VALUE Argument Details
ClientID
This is equivalent to ServProvCode. This is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
Accela Automation SQL Server Database Function Reference Guide
– 9 –
Address Information SQL Server
Address Information
The following functions are used to return different types of address information for reporting
purposes. The includes; full address, partial address, and attribute address information.
This section includes the following functions:
• FN_GET_ADDRESS_ATTRIBUTE
• FN_GET_PRI_ADDRESS_FULL
• FN_GET_PRI_ADDRESS_PARTIAL
FN_GET_ADDRESS_ATTRIBUTE
This function retrieves and returns the value of a custom attribute for the application’s address. If
the attribute is not found, the function returns NULL.
Function Call
FN_GET_ADDRESS_ATTRIBUTE (ClientID, Primary Tracking ID1, PrimaryTrackingID2,
PrimaryTrackingID3, addressNbr, addressAttribute)
For details on the function arguments for this function, see List 3,
“FN_GET_ADDRESS_ATTRIBUTE Argument Detail,” on page 9.
Returned Results
Custom Address Attribute Value {B1_ATTRIBUTE_VALUE}
FN_GET_PRI_ADDRESS_FULL
This function retrieves and returns the full primary address in two lines. For example,
555 N Oak Ln
List 3: FN_GET_ADDRESS_ATTRIBUTE Argument Detail
ClientID This is equivalent to ServProvCode. This is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1 The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2 The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3 The last 5 characters for the Universal Tracking Number.
AddressNbr The address ID. This is used to select a specific address on the
application if there are multiple addresses. If set to NULL, the first
address found will be selected.
AddressAttribute The address attribute name. Note: The attribute name (found in
the attribute’s configuration screen) may not be the same as its
label.
Address Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 10 –
Sandy, UT, 84070
If the application has no primary address, the function retrieves the first address found.
Function Call
FN_GET_PRI_ADDRESS_FULL(ClientID, PrimaryTrackingID1, PrimaryTracking2,
PrimaryTracking3)
For detailed information on the function arguments, see List 4, “FN_GET_PRI_ADDRESS_FULL
Argument Details,” on page 10.
Returned Results
Primary or first address in full {B1_HSE_NBR_START + B1_HSE_NBR_END +
B1_HSE_FRAC_NBR_START + B1_ HSE_FRAC_NBR_END + B1_STR_DIR +
B1_STR_NAME + B1_STR_SUFFIX + B1_UNIT_TYPE + B1_UNIT_START +
B1_UNIT_END + B1_SITUS_CITY + B1_SITUS_STATE + B1_SITUS_ZIP)
FN_GET_PRI_ADDRESS_PARTIAL
This function retrieves and returns a partial primary address in a single line. This partial address
excludes city, state, and zip code. If no primary address is found, the first address is returned. If no
addresses are found, NULL is returned.
Function Call
FN_GET_PRI_ADDRESS_PARTIAL(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID2)
For details on the arguments in this function, see List 5, “FN_GET_PRI_ADDRESS_PARTIAL
Argument Details,” on page 11
List 4: FN_GET_PRI_ADDRESS_FULL Argument Details
ClientID
This is equivalent to ServProvCode. This is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
Accela Automation SQL Server Database Function Reference Guide
– 11 –
Address Information SQL Server
Returned Results
Primary or first address, excluding city, state, zip {B1_HSE_NBR_START +
B1_HSE_FRAC_NBR_START + B1_STR_DIR + B1_STR_NAME + B1_STR_SUFFIX +
B1_UNIT_TYPE + B1_UNIT_START).
List 5: FN_GET_PRI_ADDRESS_PARTIAL Argument Details
ClientID
This is equivalent to ServProvCode. This is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
Application Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 12 –
Application Information
The application functions are used to return application specific information fields, values, parent
applications, construction types, hearings, and to determine if conditions have been met.
For accounting information on an application see “Accounting Information” on page 7.
This section includes the following functions:
• FN_GET_APP_SPEC_INFO
• FN_GET_APP_SPEC_INFO_BYGROUP
• FN_GET_ASI_TABLE_VALUE
• FN_GET_PARENT_APP
• FN_GET_CONST_TYPE_DESC
• FN_IS_CONDITIONS_MET
• FN_GET_HEARING_LATEST
FN_GET_APP_SPEC_INFO
This function returns an application specific information field.
FN_GET_APP_SPEC_INFO(ClientId, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, FieldLabel)
For details on the arguments in the function, see List 6, “FN_GET_APP_SPEC_INFO Argument
Details,” on page 12.
Returned Results
Application Specific Information Field {B1_CHECKLIST_COMMENT}
List 6: FN_GET_APP_SPEC_INFO Argument Details
ClientID
This is equivalent to ServProvCode.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
FieldLabel
The application specific information field label. The
wildcard (%) may be used. For example, to retrieve the
application specific field labeled “Proposed Use” from an
application, use ‘Proposed Use’ or ‘Proposed%’ for this
arguement. If two fields match the wildcard, the function
retrieves the first.
Accela Automation SQL Server Database Function Reference Guide
– 13 –
Application Information SQL Server
FN_GET_APP_SPEC_INFO_BYGROUP
This function returns an application specific information field belonging to a subgroup. Use this
function if an application specific information field is duplicated across different subgroups in the
application.where group name is like Check List Type Level and label is like Check List
Description.
This function returns the value of the first application specific information field whose subgroup
name is ChecklistTypeLevel and whose label is FieldLabel. If the application specific information
field is not found, NULL is returned.
Function Call
FN_GET_APP_SPEC_INFO_BYGROUP(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, ChecklistTypeLevel, FieldLabel)
For details on the arguments in the function, see List 7, “FN_GET_APP_SPEC_INFO_BYGROUP
Argument Details,” on page 13.
Returned Results
Application Specific Info field value {B1_CHECKLIST_COMMENT}
List 7: FN_GET_APP_SPEC_INFO_BYGROUP Argument Details
ClientID
This is equivalent to ServProvCode. This is also the
agency code used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking
Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking
Number.
ChecklistTypeLevel
The application specific information subgroup. Use the
argument to return an application specific information
field in a particular subgroup. The wildcard (%) option
may be used in the argument. If two subgroups match
the wildcard, the function retrieves the first.
FieldLabel
The application specific information field label. The
wildcard (%) may be used in the argument. For
example, to retrieve the application specific field
labeled ‘Proposed Use’ from an application, use
‘Proposed Use’ or ‘Proposed%’ for this argument. If
two fields match the wildcard, the function retrieves the
first.
Application Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 14 –
FN_GET_ASI_TABLE_VALUE
This function retrieves the value of the column called {ColumnName} that is related to (i.e. in the
same row as) the value {KeyColumnValue} in column {KeyColumnName} of the Application
Specific Information Table {TableName}. For example an application has the following
application specific information table called TEAM_INFO with two rows of values:
If there are no values found, NULL is returned.
Function Call
FN_GET_ASI_TABLE_VALUE(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, KeyColumnName, KeyColumnValue, ColumnName, TableName)
To retrieve the name of the Project Leader (i.e. TOM SMITH) from ARW, use the following
expression/function call:
FN_GET_ASI_TABLE_VALUE (&$$agencyid$$, B1PERMIT.B1_PER_ID1,
B1PERMIT.BT_PER_ID2, B1PERMIT.B1_PER_ID3, ‘STAFF_ROLE’, ‘Project Leader’,
‘NAME’, ‘TEAM_INFO’)
For details on the arguments in this function, see List 8, “FN_GET_ASI_TABLE_VALUE
Argument Details,” on page 14.
Returned Results
Value in Application Specific Information Table {ATTRUBYTE_VALUE}
List 8: FN_GET_ASI_TABLE_VALUE Argument Details
ClientID
This is equivalent to ServProvCode. This is also the agency
code used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
KeyColumnName
The column to be used as key for retrieving related
information. Each row in this column must have a unique
value. This is typically the first column in the applicaiton
specific info table.
KeyColumnValue
The value in the key column (see KeyColumnName) that is in
the same row as the data to be retrieved.
Accela Automation SQL Server Database Function Reference Guide
– 15 –
Application Information SQL Server
FN_GET_PARENT_APP
This function retrieves and returns the application’s parent application number. If the application
has more than one parent, the first parent is retrieved. If a parent is not found, an empty string is
returned.
Function Call
FN_GET_PARENT_APP(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3)
For details on the arguments in this function, see List 9, “FN_GET_PARENT_APP Argument
Details,” on page 15.
Returned Results
Parent Application Number {B1_ALT_ID}
FN_GET_CONST_TYPE_DESC
This function returns the construction type description for the application’s construction type code.
The construction type codes correspond with the standard C404 report codes for different types of
residential structures. If the construction type description is not found, NULL is returned.
Function Call
FN_GET_CONST_TYPE_DESC(ClientID, PrimaryTrackingID1, PrimaryTrackikngID2,
PrimaryTrackingID3)
For details on the arguments in this function, see List 10, “FN_GET_CONST_TYPE_DESC
Argument Details,” on page 16.
ColumnName
The column name for the data to be retrieved.
TableName (optional)
The name of the application specific information table. Use
this argument only if an application has multiple application
specific information tables with duplicate column names. Set
argument to NULL if it is not used.
List 9: FN_GET_PARENT_APP Argument Details
ClientID
This is equivalent to ServProvCode. This is also the agency
code used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
List 8: FN_GET_ASI_TABLE_VALUE Argument Details
Application Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 16 –
Returned Results
Construction Type Code{VALUE_DESC}
FN_IS_CONDITIONS_MET
This function determines if all conditions on the application have been met. It returns ‘Y’ if there
are no conditions for the application or if all conditions have the specified condition status. It
returns ‘N’ if conditions that do not have the specified condition status are present on the
application..
Function Call
FN_IS_CONDITIONS_MET(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTracking3, ConditionStatus)
For details on the arguments in this function, see List 11: “FN_IS_CONDITIONS_MET Argument
Details” on page 20.
Returned Results
Application Condition Status {‘Y’ if no conditions exist. ‘N’ if conditions exist)
FN_GET_HEARING_LATEST
This function returns information about the latest hearing scheduled for the application for the
hearing body. If the hearing body is not specified in the arguments, information about the latest
List 10: FN_GET_CONST_TYPE_DESC Argument Details
ClientID
This is equivalent to ServProvCode. This is also the agency
code used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
List 11: FN_IS_CONDITIONS_MET Argument Details
ClientID
This is equivalent to ServProvCode. This is also the agency
code used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
ConditionStatus
The status of a condition. Use this argument to determine if all
conditions on an application meet this status.
Accela Automation SQL Server Database Function Reference Guide
– 17 –
Application Information SQL Server
hearing scheduled for the application is returned. Use the GetField argument to specify whether to
retrieve the hearing date, time, or hearing body.
For details on the value returned using the Get_Field argument, see Table 1,
“FN_GET_HEARING_LATEST Returned Results,” on page 17.
Function Call
FN_GET_HEARING_LATEST(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, GetField, HearingBody)
For details on the arguments in this function, see List 12, “FN_GET_HEARING_LATEST
Argument Details,” on page 17
Table 1: FN_GET_HEARING_LATEST Returned Results
Get_Field
Argument Returned DB Fields Description
Date HearingDate P3_SCHED_DATE The hearing date, e.g.
11/15/2007
Time Hearing Time P3_SCHED_TIME The hearing time, e.g.
3:30 P.M.
Location Hearing Location P3_SCHED_PLACE_CODE The hearing location.
Hearing Body Hearing Body P3_SCHED_ACT_DESC The hearing body.
List 12: FN_GET_HEARING_LATEST Argument Details
ClientID
This is equivalent to ServProvCode. This is also the agency
code used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
Get_Field
The hearing information field you want to retrieve. Use one of
the following options to specify the field you want to return:
‘DATE’, ‘TIME’, ‘LOCATION’, ‘HEARING BODY’. See
Table 1: “FN_GET HEARING_LATEST Returned Results”
HearingBody
The hearing body. Use this argument to return the latest hearing
scheduled for this hearing body for the application.
Contact Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 18 –
Contact Information
The contact information functions are used to return specific contact details including primary
contact information and custom attributes.
This section includes the following functions:
• FN_GET_CONTACT_ATTRIBUTE
• FN_GET_CONTACT_INFO
FN_GET_CONTACT_ATTRIBUTE
This function retrieves and returns the value of a custom contact attribute for a contact on the
application. If the attribute value is not found, NULL is returned.
Function Call
FN_GET_CONTACT_ATTRIBUTE(ClientId, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, ContactType, ContactAttribute, PrimaryContactFlag)
For details on the arguments in this function, see List 13, “FN_GET_CONTACT_ATTRIBUTE
Argument Details,” on page 18.
Returned Results
Custom Contact Attribute Value {B1_ATTRIBUTE_VALUE}
List 13: FN_GET_CONTACT_ATTRIBUTE Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
ContactType (optional)
The type of contact. Use this argument to select a contact of
a specific type.
ContactAttribute
The custom attribute name. The wildcard (%) may be used.
For example, to find either ‘Cell Phone Number’ or ‘Cellular
Phone Number’, use ‘Cell%Phone Number’. Note: The
attribute name (found in the attribute’s configuration screen)
may not be the same as its label.
PrimaryContactFlag
(optional)
To retrieve the primary contact, set the PrimaryContactFlag
argument to ‘Y’. If the PrimaryContactFlag argument set to
NULL, the primary contact is retrieved if available, otherwise
the function retrieves the first contact found.
Accela Automation SQL Server Database Function Reference Guide
– 19 –
Contact Information SQL Server
FN_GET_CONTACT_INFO
This function retrieves and returns information for a contact on an application.
The function retrieves the primary ocntact when the Primary ContactFlag argument is set to ‘Y’. If
the PrimaryContactFlag is set to NULL, the function retrieves the primary contact, or if there is no
primary contact, the first contact found.
The contact information returned can also be filtered by Contact Type and Relationship.
Use the Get_Field argument to specify what data for the selected contact is to be returned. For
example, you can return a contact’s full name in [First Middle Last] format such as Tom Wilson
Smith. This is done by using ‘FullName’ as the Get_Field argument and ‘FML’ in the NameFormat
argument.
For details on the values returned for different Get_Field argument values, see Table 2,
“FN_GET_CONTACT_INFO Returned Results,” on page 19.
Function Call
FN_GET_CONTACT_INFO(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, ContactType, Relation, PrimaryContactFlag, Get_Field, NameFormat, Case)
For details on the arguments in this function, see List 14, “FN_GET_CONTACT_INFO Argument
Details,” on page 21.
Table 2: FN_GET_CONTACT_INFO Returned Results
Get_Field Returned Database Field Description
Argument
FullName Full Name B1_FNAME
B1_MNAME
B1_LNAME
The full name of the contact
FirstName First Name B1_FNAME The first name of the contact.
MiddleName Middle Name B1_MNAME The middle name of the contact.
LastName Last Name B1_LNAME The last name of the contact.
Title Job Title B1_TITLE The Job title of the contact.
ContactType Contact Type B1_CONTACT_TY
PE The Contact Type
ContactRelations
hip Contact
Relationship
B1_RELATION The contact relationship.
OrgName Business Name B1_BUSINESS_NA
ME The business name of the contact.
Contact Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 20 –
Address1 Address Line1 B1_ADDRESS1 The first line of the contact’s
address.
Address2 Address Line2 B1_ADDRESS2 The second line of the contact’s
address
Address3 Address Line3 B1_ADDRESS3 The third line of the contact’s
address
City City B1_CITY The city of the contact.
State State B1_STATE The state of the contact.
Zip Zip Code B1_ZIP The zip code of the contact.
Country Country B1_COUNTRY The country of the contact.
Phone1 Phone 1 B1_PHONE1 The phone number for the
contact.
Phone2 Phone 2 B1_PHONE2 The second phone number for the
contact.
Fax Fax Number B1_FAX The fax number for the contact.
Email Email B1_EMAIL The email address for the contact.
FullAddr_Block Full Address B1_ADDRESS1
B1_ADDRESS2
B1_ADDRESS3
B1_CITY
B1_STATE
B1_ZIP
The contact’s full address in a
block (separate lines). Excludes
country.
FullAddr_Line Full Address B1_ADDRESS1
B1_ADDRESS2
B1_ADDRESS3
B1_CITY
B1_STATE
B1_ZIP
The contact’s full address, in a
single line. Excludes country.
Table 2: FN_GET_CONTACT_INFO Returned Results
Get_Field Returned Database Field Description
Accela Automation SQL Server Database Function Reference Guide
– 21 –
Contact Information SQL Server
List 14: FN_GET_CONTACT_INFO Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
ContactType (optional)
The type of contact. Use this argument to select a contact of a
specific type.
Relation (optional)
The relationship of the contact. Use this argument to return
information for a contact with the type of relationship
specified.
PrimaryContactFlag
(optional)
To retrieve the primary contact, set the PrimaryContactFlag
argument to ‘Y’. If the PrimaryContactFlag argument is set to
NULL, the primary contact is retrieved if avaailable, otherwise
the function retrieves the first contact found.
GetField
The contact information field you want to retrieve. Use one
of the options listed in Table 2 to specify the field you want to
return. See Table 2, “FN_GET_CONTACT_INFO Returned
Results,” on page 19.
NameFormat(optional)
The format of the contact name. Use this argument to identify
the name format to use when Get_Field is ‘FullName’.
Options: ‘FML’ [First Middle Last], ‘LFM’ [Last First
Middle], ‘FL’ [First Last], ‘LF’ [Last First], ‘FMIL’ [First
Middle Initial Last]. If set to NULL and Get_Field is set to
‘Full Name’, ‘FML’ is used.
Case
The case of the value that the function returns. Set ‘U’ for
uppercase letters. Set ‘I’ for initial-caps and NULL for
original case
Inspection Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 22 –
Inspection Information
The inspection functions are used to retrieve inspection information such as inspection and result
comments and latest scheduled and completed inspections.
This section includes the following functions:
• FN_GET_INSP_COMMENT
• FN_GET_INSP_LATEST
FN_GET_INSP_COMMENT
This function retrieves and returns an inspection’s schedule comments or result comments. If
Comment Type is set to ‘Schedule’, the schedule comments are returned. If the Comment Type is
‘Result’, the result comments are returned. If there are no comments, NULL is returned.
Function Call
FN_GET_INSP_COMMENT(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, ActivityNum, CommentType)
For details on the arguments in this function, see List 15, “FN_GET_INSP_COMMENT Argument
Details,” on page 22.
Returned Results
Inspection Comments {TEXT}
FN_GET_INSP_LATEST
This function retrieves and returns information on the latest inspection for an application. The
inspection can be selected by one or more of the following criteria: Inspection Description,
Disposition Type, Inspection Date, Inspection Status or Result.
Use the GetField argument to specify the data for the selected inspection to be returned. The
available options are listed in Table 3, “FN_GET_INSP_LATEST Returned Results,” on page 23.
List 15: FN_GET_INSP_COMMENT Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
ActivityNum
The sequence number (unique identifier) for the inspection.
CommentType
The type of comment. Use this argument to return either
‘Schedule’ and ‘Result’ comments.
Accela Automation SQL Server Database Function Reference Guide
– 23 –
Inspection Information SQL Server
Function Call
FN_GET_INSP_LATEST(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, ActivityDesc, GetField, Case, AddDays, DispositionType, InspDate, Status,
CommentType)
For details on the arguments in this function, see List 16, “FN_GET_INSP_LATEST Arguments
Details,” on page 24..
Table 3: FN_GET_INSP_LATEST Returned Results
Get Field
Argument Returned Database Field Description
Inspection Inspection Type G6_ACT_TYP The description of the inspection.
Inspector Inspector Name GA_FNAME
GA_MNAME
GA_LNAME
The full name of the inspector.
Result Status or Result G6_STATUS ‘Scheduled’ if the inspection is not
yet performed, or the result if the
inspection has been performed.
Insp Date Inspection Date G6_ACT_DD or
G6_COMPL_DD The schedule date of the inspection or
the completion date of the inspection,
depending on the DispositionType
argument.
Follow Up
Date Follow Up Date G6_ACT_DD +
AddDays or
G6_COMPL_DD
+AddDays
This date is derived by adding the
number of days specified in the
AddDays argument to the inspection
date.
Comment Schedule Comment
or Result Comment TEXT The Schedule comment or the Result
comment for the inspection,
depending on the CommentType
argument.
Insp n Date Inspection Type and
Date G6_ACT_TYP
G6_ACT_DD or
G6_COMPL_DD
The inspection type and inspection
date, separated by a space. The
schedule date or completion date is
used depending on the
DispositionType arguement.
Inspection Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 24 –
List 16: FN_GET_INSP_LATEST Arguments Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
ActivityDesc (optional)
The inspection description
GetField
The inspection information field you want to retrieve. Use
one of the options listed in Table 3 to specify the field you
want to return. See “GetField Argument” in Table 3,
“FN_GET_INSP_LATEST Returned Results,” on page 23.
Case (optional)
The case of the value that the function returns. Set ‘U’ for
uppercase letters. Set ‘I’ for initial-caps (title case), and
NULL for the original case.
Add_Days (optional)
The number of days added to the inspection date to return the
follow up date. Use only if GetField is ‘Follow Up Date’.
DispositionType
(optional)
The disposition type for the inspection. Use this argument to
return a specific disposition inspection type. Options:
‘SCHEDULED’ ‘COMPLETED’, ‘DENIED’ (cancelled),
‘RESULTED’ (completed or cancelled). If set to NULL,
‘SCHEDULED’ is used.
InspDate (optional)
The inspection date. Use this argument to return the last
inspection on this date.
Status (optional)
The status of the inspection. Use this argument to return the
latest inspection having this status or result.
CommentType (optional)
The type of inspection comment. Use this argument to return
the latest inspection’s ‘Inspection Request Comment’ or
‘Inspection Result Comment’. Use only if GetField is
‘Comment’.
Accela Automation SQL Server Database Function Reference Guide
– 25 –
Owner Information SQL Server
Owner Information
The owner functions are used to retrieve owner information such as primary owner for an
application or owner information.
This section includes these functions
• FN_GET_OWNER_INFO
FN_GET_OWNER_INFO
This function is used to retrieve and return the primary owner or first owner information. If the
PrimaryOwnerFlag argument is set to ‘Y’, the primary owner is returned. If set to ‘B’, the primary
owner is returned if available. If not available, or if the Primary Owner Flag is set to ‘N’ or NULL,
the first non-primary owner is returned.
Use the GetField argument to specify the data for the selected owner to be returned. The case of the
data returned can be changed by using ‘U’ for uppercase, ‘I’ for initial caps, and NULL for original
case in the Case argument.
For details on the returned values, see Table 4, “FN_GET_OWNER_INFO Returned Results,” on
page 25.
Function Call
FN_GET_OWNER_INFO(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, PrimaryOwnerFlag, GetField, NameFormat, Case)
For details on the arguments in the function, see List 17, “FN_GET_OWNER_INFO Argument
Details,” on page 26.
Table 4: FN_GET_OWNER_INFO Returned Results
GetField
Argument Returned Database Field Description
Name Owner Name B1_OWNER_FULL_N
AME The full name of the owner.
MAddress1 Mail Address 1 B1_MAIL_ADDRESS1 The mail address of the owner.
MAddress2 Mail Address 2 B1_MAIL_ADDRESS2 The second line of the mail
address of the owner.
MAddress3 Mail Address 3 B1_MAIL_ADDRESS3 The third line of the mail address
of the owner.
MCity Mail City B1_MAIL_CITY The city for the mail address of
the owner.
MState Mail State B1_MAIL_STATE The state for the mail address of
the owner.
Owner Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 26 –
MZip Mail Zip B1_MAIL_ZIP The zip code for the mail address
of the owner.
MCounty Mail Country B1_MAIL_COUNTRY The country for the mail address
of the owner.
MFullAddr_
Block Full Mail
Address B1_MAIL_ADDRESS1
B1_MAIL_ADDRESS2
B1_MAIL_ADDRESS3
B1_MAIL_CITY
B1_MAIL_STATE
B1_MAIL_ZIP
Full mail address of the owner in
a block (separate lines). Excludes
country.
MFullAddr_
Line Full Mail
Address B1_MAIL_ADDRESS1
B1_MAIL_ADDRESS2
B1_MAIL_ADDRESS3
B1_MAIL_CITY
B1_MAIL_STATE
B1_MAIL_ZIP
Full mail address of the owner, in
a single line. Excludes country.
Phone Phone B1_PHONE The phone number of the owner.
List 17: FN_GET_OWNER_INFO Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
PrimaryOwnerFlag
(optional)
To retrieve the primary owner, set the PrimaryOwnerFlag
argument to ‘Y’. To retrieve the first non-primary owner, set
the PrimaryOwnerFlag argument to ‘N’. If the
PrimaryContactFlag argument is set to NULL, the primary
contact is retrieved if available, otherwise the function
retrieves the first contact found.
Table 4: FN_GET_OWNER_INFO Returned Results
GetField
Argument Returned Database Field Description
Accela Automation SQL Server Database Function Reference Guide
– 27 –
Owner Information SQL Server
GetField
The owner information field you want to retrieve. Use one of
the options listed in Table 4 to specify the field you want to
return. See “GetField Argument” in Table 4,
“FN_GET_OWNER_INFO Returned Results,” on page 25.
NameFormat (optional)
Not applicable. SEt this argument to NULL.
Case (optional)
The case of the value that function returns. Set ‘U’ for
uppercase letters. Set ‘I’ for initial-caps and NULL for
original case
List 17: FN_GET_OWNER_INFO Argument Details
Parcel Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 28 –
Parcel Information
The parcel functions are used to retrieve parcel information such as Parcel Number and Parcel
attributes.
This section includes these functions:
• FN_GET_PARCEL INFO
• FN_GET_PARCEL_NBR
• FN_GET_PARCEL_NBR_ATTRIBUTE
FN_GET_PARCEL INFO
This function retrieves and returns information on the first parcel for an application. Use the
GetField argument to specify the data for the parcel to be returned. For example, to return the
Parcel Number, use ‘PARCEL NBR’. The other options include: Book, Page, Lot, Block, Parcel
Area, Legal Description, Inspection Destination, Map Number, and Council District.
Function Call
FN_GET_PARCEL_INFO(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, GetField)
For details on the arguments in this function, see List 18, “FN_GET_PARCEL_INFO Arguments
Details,” on page 28.
Returned Results
Parcel Information identified in the GetField Argument {Get_Field}
FN_GET_PARCEL_NBR
This function returns the first parcel number found on the application. If a parcel number is not
found, NULL is returned.
List 18: FN_GET_PARCEL_INFO Arguments Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number. .
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
GetField
The parcel information field you want to retrieve. Options:
‘PARCEL NBR’, ‘BOOK’, ‘PAGE’, ‘PARCEL’, ‘TRACT’
‘LOT’, ‘BLOCK’, ‘PARCEL AREA’, ‘LEGAL
DESCRIPTION’, ‘CENSUS TRACT’, ‘MAP_NUM’,
‘COUNCIL_DIST’. If set to NULL, ‘PARCEL NBR’ is used.
Accela Automation SQL Server Database Function Reference Guide
– 29 –
Parcel Information SQL Server
Function Call
FN_GET_PARCEL_NBR(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3)
For details on the arguments in this function, see List 19, “FN_GET_PARCEL_NBR Argument
Details,” on page 29.
Returned Results
Parcel Number {B1_PARCEL_NBR}
FN_GET_PARCEL_NBR_ATTRIBUTE
This function returns the value of a parcel custom attribute for a parcel on an application. If the
custom attribute is not found, NULL is returned.
Function Call
FN_GET_PARCEL_NBR_ATTRIBUTE(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, ParcelNbr, ParcelAttribute)
For details on the arguments in this function, see List 20,
“FN_GET_PARCEL_NBR_ATTRIBUTE Argument Details,” on page 29.
Returned Results
Custom Parcel Attribute Value {B1_ATTRIBUTE_VALUE}
List 19: FN_GET_PARCEL_NBR Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
List 20: FN_GET_PARCEL_NBR_ATTRIBUTE Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
Parcel Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 30 –
ParcelNbr
The parcel number.
ParcelAttribute
The parcel attribute name. The parcel attribute name (found in
the attribute’s configuration screen) may not be the same as its
label
List 20: FN_GET_PARCEL_NBR_ATTRIBUTE Argument Details
Accela Automation SQL Server Database Function Reference Guide
– 31 –
Standard Choices SQL Server
Standard Choices
The Standard Choices functions return specific standard choice values from any of the portlets or
screens in Accela Automation.
This section includes these functions:
• FN_STDCHOICE_VALUEDESC
FN_STDCHOICE_VALUEDESC
This function returns the value description for a value from the specified Standard Choices Item. If
the value is not found, an empty string (“) is returned.
Function Call
FN_STDCHOICE_VALUEDESC(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, StandardChoicesItemName, StandardChoicesValue)
For details on the arguments in this function, see List 21, “FN_STDCHOICE_VALUEDESC
Argument Details,” on page 31.
Returned Results
Standard Choice Value Description {VALUE_DESC}
List 21: FN_STDCHOICE_VALUEDESC Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency
code used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking
Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
StandardChoicesItemName
The standard choice item name for the description you
want to retrieve.
StandardChoicesValue
The standard choice value whose description you want
to retrieve.
Workflow Task Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 32 –
Workflow Task Information
The workflow task functions are used to retrieve workflow task information. This information
includes, the earliest and latest workflow tasks, task specific information fields and values, task
status, and status date information.
This section includes these functions:
• FN_GET_TASK_EARLIEST
• FN_GET_TASK_LATEST
• FN_GET_TASK_SPEC_INFO_CS2
• FN_GET_TASK_STATUS
• FN_GET_TASK_STATUS_DATE
FN_GET_TASK_EARLIEST
This function retrieves and returns information about the workflow history task with the earliest
status date. If the Task Description or Task Disposition is specified, information about the earliest
workflow history task for the specified task or status is returned.
Use the GetField argument to specify the data for the selected task that is to be returned. For more
detailed information on the returned values, see Table 5, “FN_GET_TASK_EARLIEST Returned
Results,” on page 32.
Function Call
FN_GET_TASK_EARLIEST(ClientId, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, GetField, TaskDescription TaskDispositionDesc)
For details on the arguments in this function, see List 22, “FN_GET_TASK_EARLIEST Argument
Details,” on page 33.
Table 5: FN_GET_TASK_EARLIEST Returned Results
Get_Field
Argument Returned DB Field Description
Task Task SD_PRO_DES The description of the workflow
task.
Status Status SD_APP_DES The status of the workflow task.
Date Status Date SD_APP_DD The date of the workflow task
status.
Staff Action By G6_ISS_FNAME
G6_ISS_LNAME The first initial and last name of
the staff member who last
updated the task status.
Accela Automation SQL Server Database Function Reference Guide
– 33 –
Workflow Task Information SQL Server
FN_GET_TASK_LATEST
This function retrieves and returns information about the workflow task with the latest status date.
Use the GetField argument to specify what data for the selected task is to be returned. For details on
the returned values, see Table 6, “FN_GET_TASK_LATEST Returned Results,” on page 33.
Function Call
FN_GET_TASK_LATEST(ClientId, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, GetField)
For details on the arguments in this function, see List 23, “FN_GET_TASK_LATEST Argument
Details,” on page 34.
List 22: FN_GET_TASK_EARLIEST Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency
code used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking
Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
GetField
The task information type. Use this argument to specify
what info is returned for the earliest task. Options:
‘TASK’, ‘STATUS’, ‘DATE’ (format MM/DD/YYY),
‘STAFF’ (format [First Initial] [Last Name]),
TaskDescription (optional)
The description of the task. The wildcard % may be used.
For example, to retrieve the earliest task ending with the
word ‘Review’, set this argument to % Review.
TaskDispositionDesc
(optional)
The status of the task. The wildcard % may be used. For
example, to retrieve the earliest task whose status contains
the word ‘Complete’, set this argument to ‘%Complete%’.
Table 6: FN_GET_TASK_LATEST Returned Results
Get Field
Argument Returned DB Field Description
Task Task SD_PRO_DES The name of the workflow.
Status Status SD_APP_DES The status of the workflow task.
Workflow Task Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 34 –
FN_GET_TASK_SPEC_INFO_CS2
This function returns the value of a task specific information field. Use the FieldLabel and
CurrentTaskDescription arguments to specify the fieldand the task it belongs to. If the
FirstTaskDescription is specified, the function uses the task whose parent task is
FirstTaskDescription.
Function Call
FN_GET_TASK_SPEC_INFO_CS2(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, FirstTaskDescription, CurrentTaskDescription,FieldLabel)
For details on the arguments in this function, see List 24, “FN_GET_TASK_SPEC_INFO_CS2
Argument Details,” on page 35.
Date Status Date SD_APP_DD The date of the workflow task status.
Staff Staff G6_ISS_FNAME
G6_ISS_LNAME
The first initial and last name of the
staff member who updated the task
status.
List 23: FN_GET_TASK_LATEST Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
GetField
The task information type. Use this argument to specify what
information is returned for the latest task. Options: the
information to be returned. Options: ‘TASK’, ‘STATUS’,
‘DATE’ (format MM/DD/YYY), ‘STAFF’ (format [First
Initial] [Last Name]),
Table 6: FN_GET_TASK_LATEST Returned Results
Get Field
Argument Returned DB Field Description
Accela Automation SQL Server Database Function Reference Guide
– 35 –
Workflow Task Information SQL Server
Returned Results
Value of the Task Specific Info Field {B1_CHECKLIST_COMMENT}
FN_GET_TASK_STATUS
This function returns the current status of a workflow task. If a list of statuses are given in the
CurrentTaskStatus argument, this function retrieves the task only if the status is in the list. If the
task status is not found, NULL is returned.
Function Call
FN_GET_TASK_STATUS(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, CurrentTaskDescription, CurrentTaskStatus)
For details on the arguments in this function, see List 25, “FN_GET_TASK_STATUS Argument
Details,” on page 35.
Returned Results
Current Task Status {SD_APP_DES}
List 24: FN_GET_TASK_SPEC_INFO_CS2 Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
FirstTaskDescription
(optional)
The parent task of the task whose task specific information
field is retrieved. Use this argument when there are duplicate
task specific information fields on the workflow. Set to
NULL if not used. This argument is case-sensitive.
CurrentTaskDescriptio
n
The name of the task whose task specific information field is
retrieved. This argument is case-sensitive.
FieldLabel
The label of the task specific information field. This argument
is case-sensitive.
List 25: FN_GET_TASK_STATUS Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
Workflow Task Information SQL Server
Accela Automation SQL Server Database Function Reference Guide
– 36 –
FN_GET_TASK_STATUS_DATE
This function returns the current status date of a workflow task. If a list of statuses is given in the
CurrentTaskStatus argument, this function retrieves the task only if its current status is in the list. If
the task is not found, NULL is returned..
Function Call
FN_GET_TASK_STATUS_DATE(ClientID, PrimaryTrackingID1, PrimaryTrackingID2,
PrimaryTrackingID3, CurrentTaskDescription, CurrentTaskStatus)
For details on the arguments in this function, see List 26, “FN_GET_TASK_STATUS_DATE
Argument Details,” on page 36.
Returned Results
Current Task Status Date {G6_STAT_DD}
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
CurrentTaskDescription
The name of the workflow task.
CurrentTaskStatus
(optional)
The current status of the workflow task. Use this argument to
return a workflow task with a specific status. More than one
status can be used by separating values by commas. Set
argument to NULL if not used.
List 26: FN_GET_TASK_STATUS_DATE Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
PrimaryTrackingID1
The first 5 characters of the Universal Tracking number.
PrimaryTrackingID2
The middle 5 characters of the Universal Tracking Number.
PrimaryTrackingID3
The last 5 characters for the Universal Tracking Number.
CurrentTaskDescription
The name of the workflow task.
CurrentTaskStatus
(optional)
The current status of the current workflow task. Use this
argument to return a workflow task with a specific status. More
than one status can be used by separating statuses by commas.
Set argument to NULL if not used.
List 25: FN_GET_TASK_STATUS Argument Details

Navigation menu