Interface Policies

Process Group/Policy Details

• Subject: Interfaces/ATS

• Process Group: Interface

• Title of Policy: Interfaces and Automated Document Submission (ATS) Spreadsheet

• Effective Date: 01/15/2015 

• Approved/Revision Date: 01/15/2015

• Approved by: Robert Jaros

Process Group Description

Interfaces, inbound and outbound, allow departments’ source systems and Colorado Operations Resource Engine (CORE) to interact with each other. The Automated Document Submission (ATS) enable department users to load documents in the online CORE system using spreadsheets.

Rationale or background to policy

Interfaces and ATS allow departments to load documents en masse. Interfaces are either automatic inbound file from a department’s source system database or an outbound file from the CORE system. Inbound interfaces are required to be used when there is a large and frequent import of 50 or more documents.

Conversely, ATS is a manual upload of documents through the online application using an Excel spreadsheet. ATS should be considered when there are 10 or more documents to enter.

Policy Statement

AR.PO.01 Interfaces and ATS Spreadsheet

• Departments must generate interface files from their source system to be uploaded into the CORE system.

• Departments are responsible for the content of the file and that it meets the requirements defined by DPA. For a list of supported documents please see the CORE website (core.state.co.us).

• DPA will not modify inbound interface files and only one interface file per document code will be processed automatically per day. Any additional interface files will have to be manually interfaced via user request.

• DPA will adhere to interface related requirements detailed in the Colorado Information Security Policies (CISP).

• Departments are responsible for pushing their inbound interface files to the CORE sFTP for processing and pulling the outbound interface files.

• Departments will be responsible for identifying failed interfaces and reconciling interface documents and data.

• ATS will follow the same workflow rules exactly as a manually entered document online.

Interfaces File Naming Convention

This interface file’s naming convention is comprised of six nodes. An example of an interface file name as well as an explanation of each of the naming convention nodes are described below.

• DESTINATION – The first node is maximum of eleven characters for the system identifier (e.g. CITIMANAGER = CitiDirect Card Management System from CitiBank).

• DOC_DEPT_ID – The second node is the four character CORE department id (e.g. HAAA = Department of Transportation).

• DOC_CD – The third node is a maximum of eight characters for the CORE transaction or page code (e.g. ACH = Automated Clearing House).

• SOURCE – The fourth node is a maximum of four characters for source system identifier (e.g. CORE = Colorado Operations Resource Engine).

• YYYYMMDDHHMM – The fifth node is twelve characters for the date and time the interface file is generated (e.g. 201306021730). This is required to reflect the system date and time when the file is created.

• File Extension – The sixth node is the file extension to identify the type of source file.  The extension of “.txt” will be used to identify the ASCII file format.

Testing Requirements

Inbound Interface Testing

The delivery of the inbound interface requires both unit and integration level testing. Unit testing must be completed before integration can begin.

• Unit Test

  • Unit testing is the department’s responsibility. Working with their system vendor, OIT or other development group the department must build an inbound source file that meets the following criteria:

    • Correct content

    • System Generated

    • Fulfill the field mapping document’s requirements supplied by DPA

    • Proper syntax

    • Successful transmission to the CORE sFTP

• Integration Test

  • DPA will test the file transfer process from the sFTP to CORE

  • DPA will validate the department source files meet the field specifications.
    NOTE: The actual content will not be reviewed for accuracy, but only that it meets the field mapping requirements.

  • DPA will attempt to load the file into CORE

  • The department is responsible for providing a source file with record counts representative of production volume.

  • The department will perform a test results analysis to verify document loaded successfully and the data is accurate.

Outbound Interface Testing

The delivery of the outbound interface requires both unit and integration level testing. Unit testing must be completed before integration can begin.

• Unit Test

• Unit testing is the DPA and Department’s responsibility. DPA is responsible for delivering the file to COREsftp. The Department is responsible for pulling the file and validating its content. The following criteria will be reviewed:

  • • Correct content

    • System Generated

    • Fulfill the field mapping document’s requirements supplied by DPA

    • Proper syntax

    • Successful transmission to the CORE sFTP

• Integration Test

  • DPA will test the generation of the automated outbound file and the file transfer process from CORE to the COREsftp.

  • The department must pull the outbound interface file from the COREsftp.

ATS Testing

New or updated ATS upload spreadsheets require both unit and integration level testing.  Unit testing must be completed before integration can begin.

• Unit Test

  • Unit testing is DPA’s responsibility. DPA will update the mapping configuration, the ADS spreadsheet and CORE configurations as needed. DPA will perform a preliminary ADS upload to verify its working in SYS.

• Integration Test

  • The department uploads documents using the ADS spreadsheet

  • The department will perform a test results analysis to verify documents loaded successfully and the data is accurate.

Interface/ATS Delivery Process

Migrating new interfaces and ATS updates to production occurs through 3 phases. Phase 1 and 2 are for testing and the 3rd Phase is the actual migration to production:

1. Phase 1: Conducted in SYS, this initial test phase will be when most issues and bugs are resolved. Sign-off to migrate to the second test environment will only take place after both the department and DPA believe the new interface has been thoroughly vetted and tested in SYS.

2. Phase 2: Conducted in UAT, this final test phase will be a sanity check and final acceptance test before migration to production. Sign-off for production migration will take place only after both the department and DPA believe the new interface has been thoroughly vetted and tested in UAT.

3. Phase 3: Migrate new interface to Production

Procedures

INT.PR.01.1 Establishing Interface

To establish a new interface with CORE:

1. The department will submit a request to the CORE Help Desk (core.help@state.co.us).  Include:

   1. • transaction code,

      • department code,

      • proposed frequency of interface,

      • department contact,

      • justification for interface.

DPA will review and follow-up as required. All changes are governed by the change management process defined by CORE Operations.

1. 1. CORE Change Advisory Board (CAB) will notify the department if the request has been approved or rejected. If approved the department will complete a Firewall Request Form to OIT in order to access the COREsftp.

      • Note: The departments are to expect at minimum 3 week turnaround before they have access to the CORE sFTP.

   2. See testing requirements above.

INT.PR.01.2 Manual Interface Load

1. Departments wanting to have an interface file manually loaded are required to submit a ticket to the CORE Help Desk. Request that the ticket is assigned to the Interface/Batch group. Note that the interface file must reside on the sFTP or the request will be denied. The ticket must include:
   a. Why it failed (if applicable)

b. Reason a manual load is required

c. The file’s transaction type

d. Name of the file

2. DPA will review the manual load request and contact the department via the CA service ticket if:
   a. it’s approved and when it will be loaded

b. or if additional information is required

c. or if the request is denied.

3. DPA will proceed with manually loading the file accordingly and contact the department via the CA service ticket when it has been completed.

INT.PR.01.3 Submitting a Document through ATS

1. Departments are required to always download an authorized ATS spreadsheet from the CORE website.

2. Fill out the spreadsheet with the correct data and save it.

   • Note: Do not delete or rename any of the spreadsheet tab or columns in the workbook.

3. Log on to CORE and navigate to the ATS page

4. Click the “Browse” button and select the excel file.

5. Click “Upload” and wait for confirmation the upload was successful. If an error occurs refer to INT.PR.01.04 for reporting ATS issues.

INT.PR.01.4 Reporting ATS Issue

1. Departments that experience an error when loading a spreadsheet via ATS are required to submit a ticket to the CORE Help Desk. The ticket must include:

   1. • Transaction code

      • Department code

      • User ID of person trying to load spreadsheet

      • Screenshot of the actual error code. Expand the message box if there are multiple errors so as to capture all error messages in a screenshot.

      • Completed ATS spreadsheet attached to the ticket

2. DPA will review the incident and process accordingly.

INT.PR.01.5 Updating ATS Spreadsheet

1. ADS Spreadsheets do not always have all the available fields that a user would find if they entered the document online. Departments have to contact the CORE Help Desk (core.help@state.co.us) to request additional fields be added to the ATS spreadsheet. They are required to submit a ticket to the CORE Help Desk. The ticket must include:

   • • Name of the ATS spreadsheet

     • Business justification for adding a field

2. A CORE Functional Specialist will review the incident and process accordingly. All changes are governed by the change management process defined by CORE Operations.