travis county auditor’s office - national association of ... · requirements 10-5-2015 final...
TRANSCRIPT
Page 1 of 28
Travis County Auditor’s Office VENDOR SELF-SERVICE SPECIFICATION AND DESIGN DOCUMENT
Tonya Mills,
Data Analyst II
September 2015
Vendor Self-Service
Page 2 of 28 Travis County Auditor
TABLE OF CONTENTS
Vendor Self-Service Specification and Design Document .................................................................. 1
1 Approvals and Version Control .............................................................................................................. 4
Approvals ........................................................................................................................................... 4
Version Control .................................................................................................................................. 4
2 Executive Overview .................................................................................................................................. 5
Purpose of this Document ............................................................................................................... 5
Project Organizational Structure ................................................................................................... 6
Assumptions ....................................................................................................................................... 6
Constraints ......................................................................................................................................... 7
Scope .................................................................................................................................................. 7
Context Diagram .............................................................................................................................. 7
Solution Functions ............................................................................................................................. 7
Operating Environment ................................................................................................................... 8
Resource Table .......................................................................................................................................... 8
3 Business Requirements ............................................................................................................................. 9
BR-01- Data Quality/Testing ............................................................................................................ 9
4 Functional Requirements & Technical Requirements ........................................................................ 9
FR-01-User Access ............................................................................................................................. 9
FR-02 – Usability (Search) ............................................................................................................... 10
FR-03 – Usability (Sort) ..................................................................................................................... 10
FR-04-Content (Payment Release) .............................................................................................. 10
TR-01-Source Data .......................................................................................................................... 11
TR-02-Staging and Application db .............................................................................................. 12
TR-04-Ancillary Information ........................................................................................................... 13
5 User Requirements .................................................................................................................................. 13
UR-01- Navigation and Help ......................................................................................................... 13
6 Data Requirements................................................................................................................................. 14
Data Flow Diagram ........................................................................................................................ 14
Data Framework and Relationships ............................................................................................ 15
Data Tables/Definitions.................................................................................................................. 16
DT-01- VssStage – AppDataStaging Table (Transparency Data Store) ........................................ 16
DT-02- VssApplicationData – Vendor Table (Transparency Data Store) ...................................... 17
DT-03- VssApplicationData – InvPay Table (Transparency Data Store) ....................................... 17
Web-Site Transparency Initiatives
Travis County Auditor Page 3 of 28
Data Transformations ..................................................................................................................... 17
7 Interface Requirements ......................................................................................................................... 18
Software Interfaces ........................................................................................................................ 18
Hardware Interfaces ...................................................................................................................... 18
User Interfaces ................................................................................................................................. 18
8 Improvements and Impacts ................................................................................................................. 19
Improvements to Existing Capabilities ........................................................................................ 19
Impacts ............................................................................................................................................. 19
9 Use Cases ................................................................................................................................................. 19
UC-001 Searching for Vendors ..................................................................................................... 20
UC-002 Sort, Search and Invoice Navigation ........................................................................... 21
10 Traceability Matrix ................................................................................................................................... 22
11 Application Architecture ....................................................................................................................... 23
Domain Model_BLL (Business Logic Layer) ................................................................................ 23
Logging ............................................................................................................................................. 24
VssData ............................................................................................................................................. 24
VssDataContracts ........................................................................................................................... 25
VssServices ....................................................................................................................................... 25
VssWebApp ..................................................................................................................................... 26
Vendor Self-Service
Page 4 of 28 Travis County Auditor
1 APPROVALS AND VERSION CONTROL
APPROVALS
Name Role/Department Approval
Yes/No
Date
Nicki Riley Primary Stakeholder/Auditor
Patti Smith Stakeholder/Auditor
Kelly Allen Project Manager/Auditor
Tonya Mills SQL Data & Tableau Developer/Auditor
Josh Dunlap SAP BI Developer/Auditor
Christopher Flanagan Technology Resource Manager/Auditor
VERSION CONTROL
Version/# Date Revision Notes Owner
Elicitation
Phase
9-8-2015 Document Kick-Off.
Elicitation follow-up with Stakeholders. T. Mills
Final
Requirements 10-5-2015
Final requirements following meetings with Nicki,
Patti, Donna, Melinda and Robyn.
T. Mills / K.
Allen
Development
Notes
T.Mills
Test Plan 03-01-2016
Added use cases and test plan, test cases.
Updated Traceability matrix. Updated HW/SW
and interface reqs.
T. Mills
05-10-2016 Added ITS Server Diagram. T. Mills
Release 1 05-18-2016
Added development notes to all requirements
section, updated tables and added Section 11
Application Architecture.
T. Mills
Release 1 05-25-2016 Updates from peer review T. Mills
Web-Site Transparency Initiatives
Travis County Auditor Page 5 of 28
2 EXECUTIVE OVERVIEW
The Vendor Self-Service (VSS) project is a piece of a larger initiative in the Travis County Auditor’s
Office (the Office) to provide public information in an efficient, cost-effective and easily
accessible manner.
VSS has the potential to benefit many users other than vendors alone. It is anticipated that this
solution will have a positive impact on Travis County departmental staff as well; either as direct
users to the solution or vicariously through assumed efficiencies created through a decrease in
vendor calls for information related to invoice and payment inquiries. While some of this is
speculative at this point, the team has gathered some baseline measures that could serve as key
data elements going forward. Additionally, it will be possible to gather data on the number of
times the solution is accessed, along with other useful metrics related to portal traffic and how the
application is used.
Together, this data along with other data elements and avenues for user feedback will provide
the Office regular and ongoing analysis of portal effectiveness. Additionally, this data will assist in
building measures around the efficacy, efficiency and life-cycle management of VSS as well as
to inform the future development of the Financial Transparency Portal.
VSS kicked off in August 2015, with the final requirements gathering/elicitation in early September
2015. Following this, revised documentation was provided & approved and design work began.
From baseline requirements and any user stories gathered during elicitation, work began in an
iterative sprint-release process, beginning in the design phase. This allowed project stakeholders
to “touch and feel” work as it was developed and to provide immediate feedback. Additionally,
it allowed for design to change as stakeholders saw their vision come to fruition. This process
allowed for stakeholders to have continuous design input and change directions at any point that
they deemed necessary.
One such change was a complete platform change early in the project where the design shifted
from a Business Intelligence platform to a custom development project. An MVC (dot).net
application was used to develop a portal and search engine which better met the stakeholder
vision, best brought the requirements to life and in the end provided a much better user
experience. This shift was 180 degrees from the original stakeholder design intent, and was
possible at an early phase in the project as a result of an Agile development process.
While not formally employed due to size, scope and resources, Agile processes were embraced
throughout as a guiding principle and development practice.
PURPOSE OF THIS DOCUMENT
At the project on-set, this document is intended to provide a brief overview of the project, project
scope & objectives and to provide in detail the specific functional, user, technical and data
requirements. This is done to draw consensus from stakeholders and to provide the design and
development detail so that there is adequate information to build a solution that meets all of the
required business needs.
Additionally, the document helps to provide a single source for reference to ensure that data
tables and any reference or look up tables are fully defined in the data dictionary and any
supporting documentation is collected from the stakeholders during the elicitation phase. This
Vendor Self-Service
Page 6 of 28 Travis County Auditor
helps to ensure that the solution which is designed is done so with all of the relevant information
necessary to build the best solution possible.
This document then becomes a living document that will be the guiding reference for the
development of the solution and provides a foundation for project members to review and agree
on critical project components at the onset to ensure a successful outcome.
The Traceability Matrix will be updated regularly and provided to project stakeholders for status
updates.
As development occurs, a section/s may be added to include Application Architecture which will
provide a high-level walk through the development solution. Code will not be outlined in detail,
however the basic solution design including classes, components, scripts and libraries in use will
be referenced.
After development is complete, this document should be maintained as the technical reference.
Future enhancements, releases, changes, etc. should be captured and documented as such. It
is important for historical reference that design intent, and development changes are available
should new developers need to refer to it. This document is all encompassing, capturing design,
data, technical/functional requirements, use cases, project scope, business requirements, etc.
PROJECT ORGANIZATIONAL STRUCTURE
The roles outlined in the table below are relevant to the project and may not necessarily reflect
true organizational relationships.
2.1.1 Project Roles
ASSUMPTIONS
Person Role
Nicki Riley Primary Stakeholder/ Project Sponsor
Patti Smith Stakeholder
Kelly Allen Project Manager
Tonya Mills SQL Data Developer & Application Developer
Josh Dunlap SAP BI Developer
Christopher Flanagan Technology Resource Manager/Auditor
Assumption Impact
Final requirements elicitation opens
Specification and Design Document.
This document will then become a living
document growing with the project.
All necessary hardware and software will be
available for development and production
level solution.
Breakdown in infrastructure can limit dataflow
and the ability to properly design, architect,
develop and test the solution.
Necessary data elements are either
accessible or made available via a file drop.
Data is the backbone to this solution.
Web-Site Transparency Initiatives
Travis County Auditor Page 7 of 28
CONSTRAINTS
Constraints Impact
Public Facing Architecture. At the on-set, the Auditor’s Office does not
have the infrastructure to publicly face a web
application. We will need to work with ITS to
procure these resources.
SCOPE
The project scope is to build an online solution for vendors to self-service basic invoice and
payment inquiries. Any additional development should directly support that function, the data
supplied to the solution or processes and/or infrastructure supporting the solution. Any additional
functionality or requirements should be scheduled in the back log and applied to the next
Financial Transparency Portal project as appropriate.
2.4.1 General Project/Solution Description
The Vendor Self-Service solution must provide users with an interactive online solution to verify the
status of invoice payments. This solution must allow users to search and sort/filter by vendor name,
invoice number, invoice date and payment number. It should also sort/filter by date, payment
number, amount and status.
CONTEXT DIAGRAM
SOLUTION FUNCTIONS
Function Description
01 Provide vendors/users a way to look up invoice/payment statuses
02 Solution should provide measures to QC data between systems (SAP to SQL)
03 Solution must be scalable and provide the framework for growth in to a larger scale
Financial Transparency Portal.
Vendor Self-Service
Page 8 of 28 Travis County Auditor
OPERATING ENVIRONMENT
All data originates in SAP ERP. It will be extracted from the SAP DW, transformed as necessary and
end up in a SQL data store.
The raw data is staged in a SQL database. Vendor data and Invoice data are provided unique
(integer) data store Id’s and data is transformed and split in to two SQL database tables for the
application/solution to access.
The application is built using a domain driven approach, utilizing Entity Framework and a data
repository to provide a layer of abstraction between the database and application. This also
allows for the database to change and revisions to be required only in the repository and not
throughout code, making code more manageable long-term, especially as the project grows.
Later in the project, Web-Services were added which now sit between the data and application.
Resource Table
Exists or
Develop? Type Name Version
developed MVC .net Web
Application VSS Application 1.0.0
developed Sql DataStore
CTS-AUD1DEV-DB(Instance)
VssApplicationData(Pre-Prod)
VssStage
SQL-2014
developed ETL/SSIS VssProd(VssStageToSource)
VSSPush(ProdPush)
VS2013
VS2013
developed Sql Database
DELTORO-
P\AUD3PRD01(Instance)
VssApplicationData(Prod)
SQL-2014
Not production
Ready (5-13-15)
ETL Server ITS-ETL01-P SQL-2012
In the application, data manipulation and export is handled client-side, minimizing server traffic.
Chrome, Firefox, IE, Opera and Safari are supported. (Known Issue: Export does not work in IE and
Safari as of Release 1).
The application is not touch enabled yet.
Web-Site Transparency Initiatives
Travis County Auditor Page 9 of 28
3 BUSINESS REQUIREMENTS
BR-01- DATA QUALITY/TESTING
# BR-01 Data Quality/Testing
Priority High
Requirement The solution must provide for data quality/testing against SAP ERP data easily with
each update to the solution and on a regular and ongoing basis.
Refer to 7.1 Software Interfaces (Tests exist in Staging ETL)
Use Cases NA
Through the Extract, Transform and Load (ETL) process, data are validated. Currently, the
validation/tests are checking to see that the BI report/drop provided valid data. A report is being
written to drop Total Invoice Amount by Vendor from SAP ERP for validation as well.
If the BI report does not provide valid data (at least as many rows of data to the staging table as
the day prior) then the ETL process stops running prior to truncating and filling the Vendor and
Invoice tables. This ensures that the publicly facing data remains valid. Staff will check to see why
the data failed, correct the issue and the data can be picked up on one of the following
scheduled ETL runs. If it is necessary to push data outside of a scheduled ETL run, this can be
accomplished by running the ETL packages from a desktop OR scheduling a one-time job on the
server.
The staging package MUST be run through the agent on the server, requiring it to be run via a
scheduled job.
4 FUNCTIONAL REQUIREMENTS & TECHNICAL REQUIREMENTS
FR-01-USER ACCESS
# FR-01 User Data
Priority High
Requirement The solution must provide users an interactive online method to verify invoice
payments.
Refer to https://financialtransparency.traviscountytx.gov
http://cts-test/vss/ (internal/network)
Section 11- Application Architecture
Use Cases UC-001 & UC-002
The Vendor Self-Service solution kicks off the larger Financial Transparency Portal, providing the
framework for adding and enhancing requirements.
The solution is an MVC application employing (dot).net 4.5.2. The VSS application is built on an
open source library, jqGrid. We are utilizing the free-fork of this library, as in August 2015, the
original open source library became a paid resource. The free-fork is maintained by one of the
original contributors.
Vendor Self-Service
Page 10 of 28 Travis County Auditor
The grid is the lifeblood of the VSS application, which allows users to search, sort and filter vendors
and invoices.
All data manipulation such as filtering, sorting and exporting is done client-side, minimizing server
calls.
FR-02 – USABILITY (SEARCH)
# FR-02 Usability (Search)
Priority High
Requirement The solution must provide users with the ability to search and sort by Vendor
Name, Invoice Number, Invoice Status, Invoice Date and Check.
Refer to Section 11 - Application Architecture
Use Cases UC-001 & UC-002
Search and sort in VSS are addressed natively through the functionality of the jqGrid and is
grounded in JavaScript.
A custom function (modifySearchingFilter) was included in the primary script (VSS) to allow for a
‘google’ style search.
All fields, with the exception of “Encashment Date” are searchable, filterable and sortable.
FR-03 – USABILITY (SORT)
# FR-03 Usability (Sort)
Priority High
Requirement The solution must provide users with the ability to sort by date, payment number,
amount and status.
Refer to 4.2 FR-02 Usability (Search)
Section 11- Application Architecture
DT-02
Use Cases UC-002
FR-04-CONTENT (PAYMENT RELEASE)
#FR-04 Content (Payment Release)
Priority High
Requirement The solution must clearly denote that “All payments are released on Tuesdays
following Commissioner’s Court approval.”
Refer to Section 11 – Application Architecture (Views/Other/Faqs)
Use Cases UC-003
This requirement is met through the FAQ/Help section of the application. “How long does it take
for an invoice to be paid?”
Web-Site Transparency Initiatives
Travis County Auditor Page 11 of 28
TR-01-SOURCE DATA
# TR-01 ETL
Priority HIGH
Requirement The solution must receive target data files from SAP BW.
Refer to Section 6.0 Data Requirements, Data Flow Diagram
Use Cases UC-004
Target data files are received via a file drop. An SAP BI process drops
Vendor_Transparency_Data.xlsx in the //aapdrop/sap/WP1/Transparency folder. This file is picked
up by the staging ETL and data are stored/staged in the SQL data store on CTS-AUD1DEV-DB.
4.5.1 TR-01-Source Data – Error Handling
Section 6.0 Data Requirements will cover the specifics of error handling/validation in greater detail,
however, during the staging ETL sequence, validation processes occur to ensure that the SAP BI
drop contains valid data. If validation tests are not passed, the table truncation and loading
portion of the ETL does not run. Staff intervene at this point to rectify any data quality or process
issues impacting the successful data run. If data issues are fixed and the data can be captured
within one of the other scheduled runs, then no other staff intervention is required. However, if
corrective action occurs outside of other scheduled jobs, it may be necessary for staff to manually
run the jobs from the server, CTS-AUD1DEV-DB.
More times than not, the errors that occur are that there are no rows in the BI report to pick up.
However, on occasion, failure occurs as a result of erroneous dates. When data is present, failed
rows are captured in the VssStage db in table StageFailedRows. Additionally, detailed reports
can be captured on the server.
Right mouse click the package and select Reports Standard Reports All Executions.
Vendor Self-Service
Page 12 of 28 Travis County Auditor
Should a job fail, selecting “All Messages” would provide specific details as to what occurred at
each step.
TR-02-STAGING AND APPLICATION DB
# TR-02 SQL Data Store
Priority High
Requirement The system must provide a secure SQL Data Store to receive the target data files in
DT01.
Refer to Context Diagram, Section 6.0 Data Requirements
Use Cases UC-004
The financial transparency data store is on CTS-AUD1DEV-DB. This instance has been repurposed
and now serves as the pre-production Financial Transparency data store source.
The application data base files reside at DELTORO-P\AUD3PRD01. Daily ETL packages move,
transform and validate staging data, load the data in the data store tables and then load
application data to the production application database.
Production and Pre-Production data are always in sync. Currently, http://CTS-TEST/VSS runs from
the pre-production data on CTS-AUD1DEV-DB.
Web-Site Transparency Initiatives
Travis County Auditor Page 13 of 28
TR-04-ANCILLARY INFORMATION
# TR-04
Priority High
Requirement The system must provide links to relevant pdf documents. (List/documents from
Melinda Grahmann).
Refer to Section 11 – Application Architecture (Views/Other/ACHLetter & Update Vendor)
Section 11 – Application Architecture (Content/Docs/…)
Use Cases UC-003
This requirement is met via page side-bar navigation. Additional links are provided within
instructional pages, also accessible via site side bar navigation.
5 USER REQUIREMENTS
UR-01- NAVIGATION AND HELP
# UR-01 Navigation and Help
Priority Medium
Requirement The solution must provide users with accessible navigation and easily identifiable
help and useful tips.
User Story/
Elicitation
As a user I should be able to hover an icon or symbol to see tips for search and
filters.
Refer To Section 11 – Application Architecture
Use Cases UC-003
The portal/site was designed with ample navigation. Site and page navigation are used as well
as breadcrumbs. In order to avoid users being required to employ the browser back button when
launching pdf documents, all documents are launch in a separate tab.
Help files are provided through the use of FAQ’s/Help. It is accessible via site navigation in the top
navigation bar. Additionally, on the VSS page, “quick help” links like Search, Sort and Filter are
provided where users are routed to the FAQ’s page with the appropriate FAQ’s section expanded.
Roll-over tips are provided at column headings and grid navigation points to aid users. An open
source library was used for this function called, qTips2. qTips is more intuitive and visually appealing
than the jQuery pop-ups that are native to the jqGrid.
Vendor Self-Service
Page 14 of 28 Travis County Auditor
6 DATA REQUIREMENTS
As discussed briefly in other requirements sections, data originate in SAP DW/BI and are dropped
in a shared file. Here they are picked up by a staging ETL, staged, validated, transformed and
deconstructed in to two data store tables; Vendor and Invoices (InvPay).
For the production data, a production ETL moves data from data store tables to the production
databse tables of the same name.
DATA FLOW DIAGRAM
Web-Site Transparency Initiatives
Travis County Auditor Page 15 of 28
DATA FRAMEWORK AND RELATIONSHIPS
The following sections will demonstrate data flow, to include database and table relationships.
Table details can be found in section 6.3 Data Tables/Definitions.
6.2.1 Excel to Staging
The diagram ‘Excel to Staging/Data Store’ outlines the portion of the data flow which ultimately
loads the pre-production application database.
The diagram includes databases, tables and relationships. Currently, tables are truncated with
each new load. The staging AppDataStaging table includes a full copy of the excel file. The only
transformations to the data include the addition of a data store ID (dsID), transforming data types
and reducing column size from the excel files automatically sized at 255.
Once staged, processes within the ETL look to the number of rows in the InvPay table which would
reflect the number of rows from the prior load. The new staged load should be equal to or greater
than the prior load in order for the ETL process to continue. Counts, including total invoice
amounts are stored in the TestDataCounts table in VssStage.
If validation is passed, the Vendor Table and Invoice Tables are loaded. The sequences in this
portion of the ETL are where the majority of the transformations occur. See section 6.4 Data
Transformations for details.
The ETL process assigns a unique integer as an ID in the Vendor table. This is referenced in the
InvPay table as the foreign key. Because vendor numbers are sometimes alpha-numeric, it was
necessary to provide a new ID for use within the application.
Vendor Self-Service
Page 16 of 28 Travis County Auditor
6.2.1 Staging to Production
Production data is passed from the tables in the VssApplicationData db of the data store to an
exact structure on the production server (DelTorro-P\AUD3PRD01).
DATA TABLES/DEFINITIONS
DT-01- VssStage – AppDataStaging Table (Transparency Data Store)
Field Name/Data
Element
Type/Detail
Definition
Required/
Optional
Friendly Display
Field Name FiscalYear string YYYY based on FY O Fiscal Year
VendorNo string Unique Vendor Number R Vendor No
VendorName String Vendor Name Vendor Name
VendorName2 string Dba R DBA
VendorNameCombined String Concatenated Vendor Name & DBA O VendorDBACombo
InvoiceNo string
Invoice Number (multiple checks
issued on an invoice) R
Invoice No
InvoiceDate date Date invoice rec’d in SAP? R Invoice Date
Amount currency Invoiced Amount R Invoice Amount
InvoiceStatus string Status of current invoice (paid,
pending, processing, not assigned) R
Invoice Status
PaymentID string Check or ACH Number R Check or ACH No
CheckAmount currency Amount of Payment (Check or ACH) R Check or ACH
Amount
PaymentDate date Date payment processed R Payment Date
DateCheckCashed date Date that the check cleared R Encashment Date
dsID Int Generated Data Store Id R
Web-Site Transparency Initiatives
Travis County Auditor Page 17 of 28
DT-02- VssApplicationData – Vendor Table (Transparency Data Store)
Field Name/Data
Element
Type/Detail
Definition
Required/
Optional
Friendly Display
Field Name ID (PK) Int Assigned data store vendor ID R ID
VendorNo string Unique Vendor Number R Vendor No
VendorName String Vendor Name Vendor Name
VendorName2 string Dba R DBA
VendorNameCombined String Concatenated Vendor Name & DBA O Not displayed
DT-03- VssApplicationData – InvPay Table (Transparency Data Store)
Field Name/Data
Element
Type/Detail
Definition
Required/
Optional
Friendly Display
Field Name dsID (PK) Int Data store ID R dsID
vendorID (FK) Int Foreign key. ID from Vendor table. R vendorID
InvoiceNo string
Invoice Number (multiple checks
issued on an invoice) R
Invoice No
InvoiceDate date Date invoice rec’d in SAP? R Invoice Date
Amount currency Invoiced Amount R Invoice Amount
InvoiceStatus string Status of current invoice (paid,
pending, processing, not assigned) R
Invoice Status
PaymentID string Check or ACH Number R Check or ACH No
CheckAmount currency Amount of Payment (Check or ACH) R Check or ACH
Amount
PaymentDate date Date payment processed R Payment Date
FiscalYear string YYYY based on FY O Fiscal Year
DateCheckCashed date Date that the check cleared R Encashment Date
dsID int Generated Data Store Id R
DATA TRANSFORMATIONS
Data transformations are outlined in the following table. All of the data transformation is
addressed in the staging ETL.
Data Element
Type
Detail dsID Int Added via a script component in the Staging Sequence.
NewPaymentID Derived Column Added during staging and mapped to Payment ID. This column ensures that
checks which have not yet cleared will not show a Payment Number.
NewInvoice9000
5 Derived Column
Added during staging and mapped to Invoice Number. This column ensures that
the invoice number, which reflects Jury Term for Grand Jurors, will be replaced with
“Null”.
Payment Date Date All Records prior to Jan 1, 2014 are tossed. This is handled via a conditional split in
the Staging Sequence.
Fill testStage Sub-process Fills the TestDataCounts table in VSS Stage.
TestCounts Script Script Component which tests/validates the data in TestDataCounts and has a
conditional constraint employed to prevent the rest of the ETL from running if it fails.
Aggregate Function
Aggregates and groups all unique vendor numbers so that vendors are only passed
to the Vendor table one time.
vendorID Int Added via a script component in the Vendor Sequence.
Lookup Function
This function runs in the Invoice Sequence and is used to lookup vendor numbers in
the Vendor Table and grab their assigned vendorID.
UpdateDate Date Derived Column…GetDate()-1. So the update date matches the SAP DW refresh.
Vendor Self-Service
Page 18 of 28 Travis County Auditor
7 INTERFACE REQUIREMENTS
SOFTWARE INTERFACES
Exists or
Create? Type Name Version Server
Originating
Data Source
Exists File
drop
Vendor_Transparency_Data.xl
sx Excel
\\aapdrop\SAP\WP1\Tr
ansparency SAP DW
Created ETL-
SSIS
VssProd
(VssStageToSource.dtsx) VS13
CTS-AUD1DEV-
DB\VssStage.db
\\aapdrop\SAP\WP1\Tra
nsparency
Created ETL-
SSIS
VssProd
(VssStageToSource.dtsx) VS13
CTS-AUD1DEV-
DB\VssApplicationData.
db
CTS-AUD1DEV-
DB\VssStage.db
Created ETL-
SSIS VssPush (ProdPush.dtsx) VS2013 DelTorro-P/AUD3PRD01
CTS-AUD1DEV-
DB\VssApplicationData
NOT IN
USE!
ETL-
SSIS
VssPush (ProdPush.dtsx) VS2012 ITS-ETL01-P CTS-AUD1DEV-
DB\VssApplicationData
HARDWARE INTERFACES
Exists or
Develop? Type Name Version Source
Exists Data server(test) CTS-AUD1DEV-DB SQL SERVER2014 Collaboration/Courts
Exists Application Server CTS-Test Windows Server2012 Collaboration/Courts
Exists
Public Facing
Database
Space/Server
DELTORO-
P\AUD3PRD01 SQL2012 ITS
Developed/Not
Prod Ready ETL Server ITS-ETL01-P SQL2012 ITS
Exists Public Facing
Application Server AUD-APPVSSVC-P Windows Server ITS
Exists Public Facing Web
Services Tier AUD-WEBVSSVC-P Windows Server ITS
USER INTERFACES
Exists or
Develop? Type Name
Location Version
Developed Web
Application VSS https://financialtransparency.traviscountytx.gov
1.0.0
Developed Web
Application VSS Test
CTS-Test/VSS/
Web-Site Transparency Initiatives
Travis County Auditor Page 19 of 28
8 IMPROVEMENTS AND IMPACTS
IMPROVEMENTS TO EXISTING CAPABILITIES
Current System Proposed System
Vendor List Vendor Self-Service
8.1.1 Upgrades and Enhancements to Existing Capabilities
Req # Title
NA New Development
IMPACTS
8.2.1 User Impacts
9 USE CASES
Primary
Actor
Use Case Name Brief Description
User UC-001 As a user I must be able to easily locate and view my
invoices.
User UC-002
As a user I should be able to sort and search my
invoices by date, invoice number, payment number
and amounts.
User UC-003
As a user I must have access to help and ancillary
information to make my user experience easy
regardless of my user experience level.
System UC-004 As the system I must have access to accurate and
timely data.
User UC-005 As a user I must have the ability to export or download
invoices.
Vendor Self-Service
Page 20 of 28 Travis County Auditor
UC-001 SEARCHING FOR VENDORS
Created by: T. Mills Updated By: T.Mills
Date Created: 03/01/2016 Date
Updated:
3/1/2016
Actors: Users
Brief
Description: As a user I must be able to easily locate and view my invoices.
Trigger: User searches for specific vendor results
Preconditions: User has accessed financialtransparency.traviscountytx.gov
Main Success
Scenario:
1. User enters Vendor Name or Vendor Number in search input at
“Vendor Name” or “Vendor Number” column headers.
2. User clicks on the appropriate Vendor row to expand invoice results.
Alternative Flow
#1: User sorts list and pages through result set.
Exceptions: 1. Data stored as acronyms with and without spaces may have to
be searched separately until corrected in source system.
Example: AT&T, A T & T, A T& T.
Includes:
Priority: High
Frequency of
Use: High frequency of use during application use.
Assumptions: NA
Web-Site Transparency Initiatives
Travis County Auditor Page 21 of 28
UC-002 SORT, SEARCH AND INVOICE NAVIGATION
Created by: T. Mills Updated By: T. Mills
Date Created: 03/01/2016 Date
Updated:
03/01/2016
Actors: Users
Brief
Description:
As a user I should be able to sort and search my invoices by date, invoice
number, payment number and amounts.
Trigger: Row click to expand.
Preconditions: User has selected a Vendor and expanded the row to show invoices.
Main Success
Scenario:
1. User clicks on the column header of any row to sort.
2. User types in the input below the column header of any column to
search and/or filter data:
a. Invoice Status (select list)
b. Invoice No. (enter any portion of an Invoice No)
c. Invoice Date (date range picker)
d. Invoice Amount (enter exact invoice amount)
e. Payment Date (date range picker)
f. Check or ACH No (enter any portion of Check or ACH No)
g. Check or ACH Amount (Enter exact amount)
Alternative Flow
#1: NA
Exceptions: NA
Includes:
Priority: HIGH
Frequency of
Use: High frequency of use during application use.
Assumptions:
Page 22 of 28
10 TRACEABILITY MATRIX
Req. # Description Priority Status Use Case Data
Table
Responsible
Person
Date Dev
Complete
Test
Case
Date Test
Complete
BR-01
The solution must provide for data
quality/testing against SAP ERP data
easily with each update to the
solution on a regular and ongoing
basis.
High Done UC-004 NA Millst 05-01-2015
FR-01
The solution must provide users an
interactive online method to verify
invoice payments.
High Done UC-001 NA Millst 05-01-2015
FR-02
The solution must provide users with
the ability to search and sort by
Vendor Name, Invoice Number,
Invoice Status, Invoice Date and
Payment.
High Done UC-002
DT-02
DT-03
Millst 05-01-2015
FR-03
The solution must provide users with
the ability to sort by date, Payment
number, amount and status.
High Done UC-002 DT-03 Millst 05-01-2015
FR-04
The solution must clearly denote
that “All payments are released on
Tuesdays following Commissioner’s
Court approval.”
High Done UC-003 NA Millst 05-01-2015
FR-05
The system must provide links to
relevant pdf documents. (List/docs
from Melinda Grahmann).
High Done NA NA Millst/AP 05-01-2015
TR-01 The solution must receive target
data files from SAP BW. High Done UC-004 DT-01 Millst/Josh 05-01-2015
TR-02
The system must provide a secure
SQL Data Store to receive the target
data files in DT01.
High Done UC-004 DT-01 Millst 05-01-2015
UR-01
The solution must provide users with
easily accessible/identifiable help
and useful tips.
High Done UC-004 NA Millst 05-01-2015
Page 23 of 28
11 APPLICATION ARCHITECTURE
The development solution (VendorSS) was built in Visual Studios 2015. The solution is compiled from
six projects:
C# Component Class Project – DomainModel_BLL
C# Component Class Project – Logging
C# Component Class Project – VssData
C# Component Class Project – VssDataContracts
WebSolution – VssWebServices
WebSolution – VssWebApp
Each project references various libraries or in some cases open-source projects, such as jqGrid
and Qtips which should be maintained and updated as required. In some cases, as with
MomentJS and jqGrid in the VssWebApp, great care should be used in understanding the release
notes, how the updates will impact code and if any code changes will be required.
As of Release 1, we are sitting on updates for MomentJS and jqGrid that were released too close
to go-live. These updates will be looked at and scheduled in the backlog for Release 2 or future
releases as appropriate.
DOMAIN MODEL_BLL (BUSINESS LOGIC LAYER)
This project contains three class objects currently in use.
C# - Filters – which is filter logic and grouping logic used in the grid.
C# - Invoice – the domain Invoice model
C# - Vendor – the domain Vendor model
Vendor Self-Service
Page 24 of 28 Travis County Auditor
LOGGING
The logging project contains seven class objects. An open source project, Log4net is used in the
application for basic logging. In order to extend the library and to allow for custom logging,
additional class objects, extensions and interfaces were required. This project extends these class
components.
VSSDATA
The VssData Project contains two folders; Models and Repositories.
The Models folder holds the data context and data models.
The Repositories folder contains the base and model repositories and are referred to by
the relevant interfaces/data contracts in the Data Contracts project.
Web-Site Transparency Initiatives
Travis County Auditor Page 25 of 28
VSSDATACONTRACTS
Data Contracts/Classes for all relevant repositories or services.
VSSSERVICES
Web Services to complete an n-Tier architecture is required for public facing applications. This
project includes Vendor, Invoice and Logging services along with a WCF Service Factory using
Unity.
Additionally, the Web Services contains Web.config files and Publish Profiles configures for specific
releases/environments.
Vendor Self-Service
Page 26 of 28 Travis County Auditor
11.5.1 VssWebServices Web Methods
The two primary web methods are Vendor and Invoice. Each creates a read-only instance of
their respective repositories and then mimics the methods within the repositories.
Vendor Service;
o SelectAll() - creates an IEnumberable list from the domainModel_Bll <Vendor> class
using the read-only _instance created.
o GetCount() - using the same instance gets a count of all vendors.
Invoice Service;
o SelectAll() - the same as Vendor to generate a list, however, it is using a parameter
of vendorID so that it is matching invoices to specific vendors.
o GetCount() - just as Vendor does.
o GetLastUpdate() – retrieves the last update date.
VSSWEBAPP
This is the MVC (dot).net application. This is a relatively standard implementation of an MVC
application.
The application, like Web Services also has publish profiles and Web.config files specifically
configured for deployment/release environment.
The open-source projects/libraries heavily relied on in this project are:
Unity
Entity Framework
Free-jqGrid
Qtips
jQuery & jQuery Validate
Bootstrap
Bootstrap DateRange Picker
MomentJS
Pdf.js
Within the Scripts folder is a sub-folder titled “app”. This folder contains the javascript files that
were constructed to run the VSS application grid, provide the update date to the home page
and to handle the export to excel.
Documents and images in use within the application are contained in Content/Docs/… or
Content/Images/…
Web-Site Transparency Initiatives
Travis County Auditor Page 27 of 28
All pages are cshtml files and are in the Views folder.
11.6.1 VssWebApplication Web Pages
Financial Transparency Home – (Views/Home/Index) from this page, Users can navigate
to any of the other site pages via the site navigation bar across the top of the page. There
is also button navigation to the VSS page, as well as links to other helpful pages.
o No data elements on page.
o Links to external sites will have to be managed to ensure they remain relevant.
Contact Us – (Views/Home/Contact) simple contact page with email links to Accounts
Payable (AP) and Website Transparency Support (WSTransparencySupport). Breadcrumb
navigation is added here.
o No data elements on this page.
o Links are to active email groups.
Help & FAQ’s – (Views/Other/Faqs) help topics and frequently asked questions.
Breadcrumb navigation available here as well.
o There are no data elements on this page.
o Images are maintained in Content/Images/…
VSS – The Vendor Self-Service page. This page has the addition of page relevant side bar
navigation. Here users can find links to additional pages relevant to Vendors and vendor
activities, forms as well as “jump” to quick tips related to VSS. Site Navigation remains
relevant, as well as breadcrumbs.
o The Data Update Date is filled via (Scripts/app/LastUpdate) using the
GetLastUpdate() method from the VSSController.
o The VSS grid is run by (Scripts/app/Vss.js) and calls applicable Vendor and Invoice
methods via the VssController.
Vendor Self-Service
Page 28 of 28 Travis County Auditor
JsonResults Vendor – using the SelectAll() Vendor.
JsonResults VendInvoiceSubGridData – using the SelectAll() Invoice.
Each of these methods format the data for paging and initial sort and return
JSON results required for the jqGrid.
Enrolling in ACH – (Views/Other/ACHLetter) provides users instructions for enrolling in ACH
as well as provides links to the ACH enrollment form and links to AP emails. Site navigation
and breadcrumbs are available on the page.
Updating Vendor Data – (Views/Other/UpdateVendor) provides users instructions for
updating vendor information and links to the W9 form & AP email addresses.