MS SQL DB Functions Accela Automation Server Database Function Reference Guide
User Manual:
Open the PDF directly: View PDF 
.
Page Count: 38
| Download | |
| Open PDF In Browser | View PDF |
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
TABLE OF C ONTENTS
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
– iii –
I NTRODUCTION TO D ATABASE F UNCTIONS
T
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
Accela Automation SQL Server Database Function Reference Guide
– v –
Introduction to Database Functions
Typographic Conventions
This guide uses the following typographical conventions:
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.
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
Accela Automation SQL Server Database Function Reference Guide
– vi –
Introduction to Database Functions
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
– vii –
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 se ction 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.
Accela Automation SQL Server Database Function Reference Guide
– 7 –
Accounting Information
SQL Server
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 Deta ils
ClientID
This is equivalent to ServProvCode. This is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Accela Automation SQL Server Database Function Reference Guide
– 8 –
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 se ction 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}
List 3: FN_GET_ADDRESS _ATTRIBUTE Argument Deta il
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.
FN_GET_PRI_ADDRESS_FULL
This function retrieves and returns the full primary address in two lines. For example,
555 N Oak Ln
Accela Automation SQL Server Database Function Reference Guide
– 9 –
Address Information
SQL Server
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)
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.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
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
Accela Automation SQL Server Database Function Reference Guide
– 10 –
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 Ar gument De ta il s
ClientID
This is equivalent to ServProvCode. This is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Accela Automation SQL Server Database Function Reference Guide
– 11 –
Application Information
SQL Server
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 se ction 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}
Li s t 6 : FN_ GET_ APP_ SP EC_I NFO Ar gume nt De ta il s
ClientID
This is equivalent to ServProvCode.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Fiel dLabel
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
– 12 –
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_ SP EC_I NFO_BYGROUP Argument Details
ClientID
This is equivalent to ServProvCode. This is also the
agency code used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking
Number.
Pri maryTrackin gID3
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.
Fiel dLabel
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.
Accela Automation SQL Server Database Function Reference Guide
– 13 –
Application Information
SQL Server
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 Ar gument De ta il s
ClientID
This is equivalent to ServProvCode. This is also the agency
code used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
KeyCo lumnName
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.
KeyCo lumnVal ue
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
– 14 –
Application Information
SQL Server
List 8: FN_GET_ASI _TABLE_ VALUE Ar gument De ta il s
ColumnName
The column name for the data to be retrieved.
TableName ( opt ional )
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.
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}
List 9: FN_GET_PARENT_APP Ar gument De ta il s
ClientID
This is equivalent to ServProvCode. This is also the agency
code used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
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.
Accela Automation SQL Server Database Function Reference Guide
– 15 –
Application Information
SQL Server
Returned Results
Construction Type Code{VALUE_DESC}
List 10: FN_G ET_CONST_TYPE_ DE SC Ar gume nt De ta il s
ClientID
This is equivalent to ServProvCode. This is also the agency
code used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
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)
List 11: FN_IS_CO NDITIO NS_MET Argument De ta ils
ClientID
This is equivalent to ServProvCode. This is also the agency
code used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Condi tio nStat us
The status of a condition. Use this argument to determine if all
conditions on an application meet this status.
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
Accela Automation SQL Server Database Function Reference Guide
– 16 –
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 _LATES T 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_G ET_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.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
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.
Accela Automation SQL Server Database Function Reference Guide
– 17 –
Contact Information
SQL Server
Contact Information
The contact information functions are used to return specific contact details including primary
contact information and custom attributes.
This se ction 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_G ET_CONTACT_ATTRIBUTE Ar gument De ta ils
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Contact Type ( opt ion al )
The type of contact. Use this argument to select a contact of
a specific type.
Contact Att ribut e
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.
Pri maryCon tactF lag
(o pti onal)
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
– 18 –
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_I NFO Returned Results
Get_Field
Returned
Database Field
Description
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.
Argument
Accela Automation SQL Server Database Function Reference Guide
– 19 –
Contact Information
SQL Server
Table 2: FN_GET_CONTACT_I NFO Returned Results
Get_Field
Returned
Database Field
Description
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.
Accela Automation SQL Server Database Function Reference Guide
– 20 –
Contact Information
SQL Server
List 14: FN_G ET_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.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Contact Type ( opt ion al )
The type of contact. Use this argument to select a contact of a
specific type.
Relati on ( opt ion al)
The relationship of the contact. Use this argument to return
information for a contact with the type of relationship
specified.
Pri maryCon tactF lag
(o pti onal)
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.
NameF ormat( opt ional)
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
Accela Automation SQL Server Database Function Reference Guide
– 21 –
Inspection Information
SQL Server
Inspection Information
The inspection functions are used to retrieve inspection information such as inspection and result
comments and latest scheduled and completed inspections.
This se ction 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}
List 15: FN_G ET_I NSP_COMMENT Ar gument De ta ils
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Activi tyNum
The sequence number (unique identifier) for the inspection.
Commen tType
The type of comment. Use this argument to return either
‘Schedule’ and ‘Result’ comments.
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.
Accela Automation SQL Server Database Function Reference Guide
– 22 –
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.
Accela Automation SQL Server Database Function Reference Guide
– 23 –
Inspection Information
SQL Server
List 16: FN_G ET_I NSP_LATEST Arguments Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Activi tyDesc (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 (o pti onal)
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 (opti onal)
The number of days added to the inspection date to return the
follow up date. Use only if GetField is ‘Follow Up Date’.
Disposi tio nType
(o pti onal)
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.
InspDat e ( opt ional )
The inspection date. Use this argument to return the last
inspection on this date.
Stat us ( opt ional )
The status of the inspection. Use this argument to return the
latest inspection having this status or result.
Commen tType (op ti onal)
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
– 24 –
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 se ction 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_I NFO 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.
Accela Automation SQL Server Database Function Reference Guide
– 25 –
Owner Information
SQL Server
Table 4: FN_GET_OWNER_I NFO Returned Results
GetField
Argument
Returned
Database Field
Description
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_G ET_O WNER_I NFO Argument Deta ils
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Pri maryOwnerFlag
(o pti onal)
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.
Accela Automation SQL Server Database Function Reference Guide
– 26 –
Owner Information
SQL Server
List 17: FN_G ET_O WNER_I NFO Argument Deta ils
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.
N ameF or mat ( opt ion al )
Not applicable. SEt this argument to NULL.
Case (o pti onal)
The case of the value that function returns. Set ‘U’ for
uppercase letters. Set ‘I’ for initial-caps and NULL for
original case
Accela Automation SQL Server Database Function Reference Guide
– 27 –
Parcel Information
SQL Server
Parcel Information
The parcel functions are used to retrieve parcel information such as Parcel Number and Parcel
attributes.
This se ction 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}
List 18: FN_G ET_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.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number. .
Pri maryTrackin gID3
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.
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.
Accela Automation SQL Server Database Function Reference Guide
– 28 –
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}
List 19: FN_G ET_PARCEL_ NBR Argument Deta ils
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
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 20: FN_G ET_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.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Accela Automation SQL Server Database Function Reference Guide
– 29 –
Parcel Information
SQL Server
List 20: FN_G ET_PARCEL_ NBR_ATTRIBUTE Argument Details
Parcel Nb r
The parcel number.
Parcel At tri bute
The parcel attribute name. The parcel attribute name (found in
the attribute’s configuration screen) may not be the same as its
label
Accela Automation SQL Server Database Function Reference Guide
– 30 –
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 se ction 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_S TDCHOICE_VALUEDESC Argument Deta ils
ClientID
This is equivalent to ServProvCode. It is also the agency
code used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking
Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
Stand ard Ch oicesItemName
The standard choice item name for the description you
want to retrieve.
Stand ard Ch oicesVal ue
The standard choice value whose description you want
to retrieve.
Accela Automation SQL Server Database Function Reference Guide
– 31 –
Workflow Task Information
SQL Server
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 se ction 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_TAS K_ EARLIE ST 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
– 32 –
Workflow Task Information
SQL Server
Li s t 2 2 : FN_G ET_TAS K_ EARLIE ST Ar gument De ta i ls
ClientID
This is equivalent to ServProvCode. It is also the agency
code used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking
Number.
Pri maryTrackin gID3
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]),
TaskDescri pti on (option al)
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.
TaskDisposi tio nDesc
(o pti onal)
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%’.
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.
Table 6: FN_GET_TAS K_ 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.
Accela Automation SQL Server Database Function Reference Guide
– 33 –
Workflow Task Information
SQL Server
Table 6: FN_GET_TAS K_ LATEST Returned Results
Get Field
Argument
Returned
DB Field
Description
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_G ET_TAS K_ LATEST Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
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]),
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.
Accela Automation SQL Server Database Function Reference Guide
– 34 –
Workflow Task Information
SQL Server
Returned Results
Value of the Task Specific Info Field {B1_CHECKLIST_COMMENT}
Li s t 2 4 : FN_G ET_TAS K_ SP EC_I NFO_CS 2 Argument Deta i ls
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
FirstTaskDescri pti on
(o pti onal)
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.
CurrentTaskDescrip tio
n
The name of the task whose task specific information field is
retrieved. This argument is case-sensitive.
Fiel dLabel
The label of the task specific information field. This argument
is case-sensitive.
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 25: FN_G ET_TAS K_ STATUS Argument Details
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Accela Automation SQL Server Database Function Reference Guide
– 35 –
Workflow Task Information
SQL Server
List 25: FN_G ET_TAS K_ STATUS Argument Details
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
CurrentTaskDescrip tio n
The name of the workflow task.
CurrentTaskStatus
(o pti onal)
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.
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}
List 26: FN_G ET_TAS K_ STATUS_DATE Argument De ta ils
ClientID
This is equivalent to ServProvCode. It is also the agency code
used at log in and saved on each record.
Pri maryTrackin gID1
The first 5 characters of the Universal Tracking number.
Pri maryTrackin gID2
The middle 5 characters of the Universal Tracking Number.
Pri maryTrackin gID3
The last 5 characters for the Universal Tracking Number.
CurrentTaskDescrip tio n
The name of the workflow task.
CurrentTaskStatus
(o pti onal)
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.
Accela Automation SQL Server Database Function Reference Guide
– 36 –
Source Exif Data:
File Type : PDF File Type Extension : pdf MIME Type : application/pdf PDF Version : 1.6 Linearized : No Page Mode : UseOutlines XMP Toolkit : Adobe XMP Core 4.0-c316 44.253921, Sun Oct 01 2006 17:14:39 Format : application/pdf Title : MS SQL DB Functions.book Creator : msquires Create Date : 2010:04:07 13:47:06Z Creator Tool : FrameMaker 8.0 Modify Date : 2010:04:07 16:50:13-06:00 Metadata Date : 2010:04:07 16:50:13-06:00 Producer : Acrobat Distiller 8.1.0 (Windows) Document ID : uuid:4668f1f6-e273-439e-be1f-18cffdfb4f57 Instance ID : uuid:7c589279-f72f-4d62-be6e-f202d982fbf5 Page Count : 38 Author : msquiresEXIF Metadata provided by EXIF.tools