iTRAK Attachment C - Interface Requirements Final.pdf

National Science Foundation

Attachment C:

Interface Requirements

TABLE OF CONTENTS

1 Background

2 FAS Interfaces

Table 1: FAS Interfaces

3 iTRAK Interface Requirements

3.1 Proposed Integration Architecture:

3.2 Interfacing with Business Services Layer:

3.3 List of Business Transactions

Table 2: Business Transactions

3.4 Data Extraction

3.5 Integration with NSF‟s Enterprise Identity Management System

3.6 COTS Integration Capability

Table 3: Standard Federal Interfaces

4 System Requirements

Appendix A: FAS Interfaces Details

Awards System Interface eJacket System Interface eTravel Interface

GRFP Interface

Central Contractor Registry (CCR) Interface

Guest Travel System Interface

FPPS Interface

Training System Interface

Institution System Interface

Bank Information Validation Service Interface

COTS Interfaces

Purchase Card Interface

Treasury Interface

Appendix B: Sample Input and Output parameters for a Business Transaction

1 Background

The Financial Accounting System (FAS) is NSF‟s primary vehicle for managing funding.

Central transaction management represents the core of the system, handling transaction processing, accounts maintenance, transaction history, and rules processing. The system includes reporting capabilities to assist NSF in verifying funds throughout the fiscal year. The system supports extensive editing for transactions input through the FAS as well as through other systems that interface with FAS.

The iTRAK solution will replace FAS and should be a fully integrated financial management system. It shall use commercial off the shelf (COTS) products to leverage financial management system best practices.

2 FAS Interfaces

FAS is integrated with several grants management as well as administrative systems at NSF mainly for posting financial and non financial transactions; and utilizing FAS data as reference data for look ups, validations, and reporting. The following Table 1: FAS Interfaces provides a summary of the systems interfacing with FAS.

Table 1: FAS Interfaces

Name Interaction Description

Awards Financial and

Non-financial transactions

Provides the Division of Grants and Agreements (DGA) with the tools to process all award actions electronically to include approving, reviewing, and logging award actions. In addition, it allows DGA to compose an award letter and upon approval, send an electronic notification to the grantee.

eJacket Financial and

Non-financial transactions eJacket is an end-to-end web-based grants management system that allows NSF staff to view and process proposals electronically from receipt in the external facing grants applications systems through post-award management.

GRFP (Graduate

Research

Fellowship Program system)

Financial and

Non-financial transactions

Fellowship applicants, reference writers, fellows, and university officials can manage fellowship activities and exchange information online

Guest Travel Financial and

Non-financial transactions

The Guest Travel system is a web-based application for travel obligations and payment requests for non-NSF employees, specifically panelists.

Training System (at Financial and

Non-financial

Currently a custom built interface with a legacy training system for internal and external training needs. It will be

Name Interaction Description

NSF) transactions replaced by an eLearning system.

Note: This interface can be retired if the new solution has a built-in interface with the e-Learning service provider

Institution System Non-financial transactions

Institution system is a custom built application for maintaining vendors and grantees data and is used for the

NSF Grants Management Systems. Grant systems require specific information for reporting, audit and compliance.

In the future state, new grantee and vendor header records are updated in Institution and sent to the financial system.

Payment Processing Financial and

Non-financial transactions

A future interface, as a replacement and enhancement of

Federal Financial Reporting functionality in FastLane and

Research.gov systems, to be implemented before the iTRAK implementation. It will allow grantees to request payments at the individual grant level in near/real time.

Expected frequencies of payment transactions are between 2,000 and 25,000 daily, with an average expected frequency of 11,000 daily.

Data Warehouse Data Extract and

Load

A future interface that will store current and historic financial data providing end user reporting for financial and operational information across the enterprise.

eTravel interface (at

NSF)

Financial and

Non-financial transactions

Interfaces with the FedTraveler system hosted by HP with

FAS. \

Note: This interface can be retired if the new solution has a built-in interface with the e-Travel service provider

CCR batch program

(at NSF)

Non-financial transactions

Currently, CCR data is used for vendor payments, but not grantee payments. In the future state, the institution application will provide the header information to include name and DUNS +4, and CCR will provide detailed information including address and banking information for both vendors and grantees.

FPPS (Federal

Personnel Payroll

System) Interface program (at NSF)

Financial and

Non-financial transactions

FPPS is the NSF component of the DOI Federal

Personnel Payroll System hosted at National Business

Center (NBC). FPPS links payroll cost and personnel information from DOI/NBC‟s FPPS with NSF‟s Financial

Accounting System (FAS) and Integrated Time and

Attendance System (ITAS).

Name Interaction Description

Bank Information

Validation Service

Non-financial transactions

Updates bank information in FAS.

Purchase Card Financial and

Non-financial transactions

Procurement card transactions downloaded from the procurement card provider and uploaded into the financial system for payment and reporting.

Refer to Appendix A for additional information on the systems interfacing with FAS

3 iTRAK Interface Requirements

NSF is in the process of changing the FAS interfaces to operate on “Open Standards” such as

Web Services and Data Services. This transition is planned to be completed in time for the iTRAK implementation. With this being the assumption the iTRAK system integrator is expected to integrate the COTS system with NSF‟s legacy systems as listed in section 2.0 using a standards based open architecture APIs as listed below.

3.1 Proposed Integration Architecture:

For transactional interfaces NSF plans on Web Services architecture using SOAP, HTTPs, WSDL, XML and JMS. The security on the interface must be FIPS complaint. The solution should support both synchronous (real-time) and a-synchronous (near real-time) transactions as well as processing transactions in batch.

For the data integration platform NSF plans on using data services that would periodically push the data from iTRAK to the NSF‟s Enterprise Data Warehouse. NSF‟s Enterprise Data

Warehouse is hosted on Oracle 10g database platform as outlined in Figure 1: iTRAK Integration

Landscape.

3.2 Interfacing with Business Services Layer:

NSF will work with the iTRAK integrator to establish the contract that includes the schema definitions and WSDL signatures for processing a business transaction. A list of sample input and output parameters are identified in Appendix B. The integrator is expected to plug-in the respective COTS based APIs with in the business layer built by NSF as outlined in Figure 1:

iTRAK Integration Landscape.

Figure 1: iTRAK Integration Landscape iTRAK Integration

Legacy Systems

Other Data Sources

Enterprise

Authentication COTS Integration

Web Applications

NSF

Business Services

Data Warehouse

Integration with

Business

Services

Built in COTS

Integration

Data

Extraction

COTS Applications

Client/ Server Applications

CCR

FPPS

Treasury

Others eTravel System iTRAK Solution

Reports/Analytics

3.3 List of Business Transactions

The following Table 2: Business Transactions provides a list of business transactions consumed by the interfaced applications.

Table 2: Business Transactions

Service Name Description

Post Financial Transaction Post a financial transaction such as a commitment, obligation, payment, journal entry; as well as perform a funds check against the accounting system

Get Bill ID Get a new Bill ID for processing receivables

Get Disbursement ID Get a new Disbursement ID for processing payments

Insert Disbursement Record Insert Disbursement information for processing payments

Insert Travel Information Insert information related to a travel obligation

3.4 Data Extraction

As identified in Figure:1 iTRAK Integration Landscape, the iTRAK solution should have capability to extract data such as reference data and transactional data, from the Core Financial system and load it into NSF staging area for it to be available for other systems and reporting/analytics. The iTRAK system integrator would be responsible for extracting the data from the new system and loading it to the staging area as specified. The frequency of data load would be on a daily basis.

The new solution should have a capability to extract data into the Data Warehouse. NSF would work with the iTRAK system integrator to identify data objects to be loaded in the staging area.

A list of sample reference data objects is mentioned below:

- Appropriation

- Organization

- Program

- Object Class Code

- Individual data

- Grantee and Vendor data

- Custom Reference Codes (to be identified during implementation)

3.5 Integration with NSF’s Enterprise Identity Management

System

For the Identity integration NSF plans on using identity services to consume Authentication information to log into the application. iTRAK should have the capability to consume SAML

(Secured Assertion Markup Language) OR Web Services for user authentication to the system.

3.6 COTS Integration Capability

As identified under the COTS integration block in Figure: 1 iTRAK Integration Landscape, it is desired that the offered solution includes built-in interfaces with standard Federal interfaces that are used at NSF. A list of the standard Federal interfaces are identified in Table 3: Standard

Federal Interfaces below. Currently these products are integrated using custom interfaces and

NSF plans to retire them once the new iTRAK solution is implemented.

Table 3: Standard Federal Interfaces

Name Description

Central Contractor Registry

(CCR)

Integration with CCR.gov

Treasury Integration with Treasury to provide the following data extracts:

FACTS I

FACTS II

SF-224

GFRS Statements

SF-133

Intragovernmental Transaction Support List

Disbursement Support Listing

IFCS

IRAS

GTAS (January 2013)

IPAC

GOALS II/IAS RFC Agency Link

SPS

PAM

IPP

GWA

FPPS Interface with DOI‟s FPPS system eTravel System Interface with the eTravel system

Purchase Card Interface to import travel and purchase card transactions eLearning system Interface with an eLearning system for processing training requests for external training, modifications, and internal transfers.

Bank Information Validation

Service

Updates bank information in the financial system.

4 System Requirements Each business transaction should be processed in iTRAK and sent back to the NSF systems with 2 seconds on a near real-time scenario.

In a near real-time scenario, upon failure to communicate the response to NSF systems, the system should make 5 consecutive tries within a duration of 60 seconds before throwing any exceptions.

iTRAK should provide proper reconciliation reports for all business transaction through the interface.

The interfaces should be available 24x7x365 except on planned outages.

The interfaces should be able to support 50 concurrent transactions without performance degradation.

Any planned outages should be notified to NSF at least 6 weeks in advance.

All changes to the interfaces endpoints should be documented and communicated to NSF at least 8 weeks in advance.

All production incidents should be responded with 2 hours. The incidents should be documented and communicated to NSF via proper communication channels and escalated to the IT Help Center.

The data load from iTRAK to NSF staging area should happen on a daily basis (every 24 hours) or near/real time or on demand basis, Supporting documentation (data dictionary, data model) on the data should be provided to NSF.

The data transport mechanism should be secured per FIPS 140-2.

Appendix A: FAS Interfaces Details

Awards System Interface

Transaction Types

Transaction Code

Trigger Mechanism Frequency Volume Error Handling

Mechanism

Commitments Commitment of

NSF RAD (1036)

- 354: Award Maintenance/CGI Mass Commit

User Approval Post Server Real Time ~7000/year FAS generated error codes

Mass CGI Commitment Batch Job

Scheduled Batch

Batch Upload

User Defined/ Coincides with budget and

CR

passings

~7000/year FAS generated error codes

De-commitments

De-commitment of NSF RAD (1036) - 355

CGI Status Functionality for CGI De-commitments

Manual Not Real Time

~300/year FAS generated error codes

Obligations Obligation of

RAD (1036) -

452: New Project, Renewals, Accmpl Bsd Renewals, Supplements, Increments (funding and forward funding), Creativity Extension

User Approval Post Server Real Time Tran 452 :

~30000/year Tran 456 : ~ 1000/year

FAS

generated error codes

Obligation for PI Transfer – 456

De-obligations De-obligation of current fiscal year obligation -

DGA Request DFM for all De-obligation requests.

Manual Not Real Time

Tran Code 451:

~500/year Tran Code

453:

~1000/year

FAS

generated error codes

De-Obligation of prior fiscal year obligation - 453

Queries Expenditure/ Advance - Partial against previously

User Approval Post Server User Defined

~ 300/year FAS

Transaction Types

Transaction Code

Trigger Mechanism Frequency Volume Error Handling

Mechanism recorded obligation - 600:

Fund unliquidated balance check for Post Award approval eJacket System Interface

Types

Transaction Code &

Description Trigger Mechanism Frequency Volume

Error Handling

Mechanism

Obligation 452 - Auto CGI Obligation

PO Approval Call to FAS Post Server

Real Time FAS generated error codes

Commitment 354 - DD Concur Commitment

Division Director Approval

Call to FAS Post

Real Time Avg. of 100- 150 per day

FAS

generated error codes

Funding Inquiry

354 - DD

Concur Commitment

Commitment of Funds from DD Concur

FAS Post Real Time FAS generated error codes

PI Transfer - Unliquidated Balance Transfer

Commitment of Funds from DD Concur

FAS Post Real Time FAS eTravel Interface

Types

Transaction Code Trigger Mechanism Frequency Volume Error

Handling Mechanism

Obligations Employee Travel Obligation – 400

User Approval

Online/ Synchronous

Real Time 6500 per year for

Rejected Request/ Error Code

Non-Employee Travel Obligation – 402

6500 per year for

De-obligations Current Year De-obligations - 451

User Reservation Cancellation

Online/ Synchronous

Real Time 500 per year for

Rejected Request/ Error Code

Prior Year De-obligations - 453

50 per year for

Reimbursements Reimbursements - 610 Batch Batch in Nightly 8500 per Rejected

Accounts Receivable - The interface does not currently support Accounts Receivable transactions, it creates a bill ID for these transactions

Upload XML format year for

Request/ Error Code

GRFP Interface

Performs funds query to determine obligation and expenditure amounts for an award

Central Contractor Registry (CCR) Interface

CCR information is downloaded to CCR Data base at NSF. CCR batch interface program updates vendor information such as address and EFT details in FAS on a daily basis

Guest Travel System Interface

Transaction Types

Transacti on Code

Trigger Mechanism Frequenc y Volum e

Error Handling Mechanis m

Obligation 402 User Approval/ Nightly Batch

Online Web Application/Batc h job

Real time + Nightly Batch

Approx 2,000 a year

Rejected Request/ Error Code

Reimbursement 610 Nightly Batch

Batch job Once a day

Approx 20,000 a year

Rejected Request/ Error Code

FPPS Interface

File Formats - FTP

Files that are provided: Labor Cost File (COSTFILE), Combined Data File (COMBFILE), Banking Info File (IFIL0131 – Incremental & IFIL0079 – Full), IPA Extract File (IPAEXT)

Transmission : The data files are FTP‟d from DOI using a UNIX shell script

The data files are parsed by FPPS application programs and stored in FPPSDB. Employee Bi-weekly and daily banking information is consolidated and updated through an interface. Flow of logic for Normalized loading:

· ControlM invokes a script that calls an executable java class that runs the Bank Info

Normalized Loading interface.

· Retrieves all the records whose process status code is „01‟ from Labor Cost Staging table based on the batch Id.

· Validates the data and convert to the appropriate data type picking the data type from an xml file.

· Retrieves the existing record of the employee‟s Bank Info with the SSN from Bank Info

Normalized table.

· Compares the existing record with the new record.

· If there is no change in the profile, the system ignores the record. If any change, the record is made expired and inserted with the new account particulars.

· Job start time, Job end time, Job result written to the Job process log table

· Error logs written to the Record error log table.

All the data files are parsed by FPPS application programs and stored in FPPSDB. Labor Cost data would be consolidated, and merged with NSF translations rules to form accounting transactions and would be posted into FAS system.

Payroll accruals are posted manually using payroll transaction codes (TC 652, 654) by appropriation, program element and object class. The accrual is based on the pay period that will post immediately before the end of the quarter. At year end, additional amounts are accrued for terminal leave, intermittent consultants and overtime, as well as Presidential Rank awards (if signed by the President on or before 09/30).

Batch Job Job

Description Trigger Mechanism Frequency Volume

Error Handling

Mechanism

FPPS5010 FTP files from DOI (download)

Scheduled Upload

Batch Process

Daily ~ 10K Daily

FAS error codes

FPPS1010 Labor Cost File to staging table - (Dependent on

FPPS5010)

Scheduled Upload

Batch Process

First Wednesday of pay period

~ 20K - Monthly

FAS error codes

FPPS2010 Labor Cost staging to normalized table - (Dependent on

FPPS1010)

Scheduled Upload

Batch Process

First Wednesday of pay period

~ 20K - Monthly

FAS error codes

FPPS3010

No Post

FAS

Posting Interface - Prelim - (Dependent on

FPPS2010)

Scheduled Upload

Batch Process

First Wednesday of pay period

~1K Monthly

FAS error codes

FPPS3010

Post

FAS

Posting Interface - Final - (Dependent on

FPPS2010)

Scheduled Upload

Batch Process

First Thursday of pay period

~1K Monthly

FAS error codes

FPPS6010 Debts with balance or activity, Scheduled Upload

Batch Process

First Thursday of pay period

FPPS6010

~ 36 K Monthly

FPPS6020

~ 400 Monthly

FPPS6030

~ 1K Monthly

FAS error codes

FPPS6020

Debt report by Org, and

FPPS6030

Overall totals report (Dependent on

FPPS3010)

Training System Interface

Training System (at

NSF)

Financial and Non-financial transactions

Custom built training system for internal and external training needs for posting training commitments and obligations to the accounting system

Institution System Interface

Institution System Non-financial transactions

Institution system is a custom built application for maintaining vendors and grantees data and is used for the

NSF Grants Management Systems. Grant systems require specific information for reporting, audit and compliance.

New grantee records are initiated in Institution and sent to the financial system.

Bank Information Validation Service Interface

Thomson File Interface

Non-financial transactions

Updates bank information in FAS

COTS Interfaces

Purchase Card Interface

The technical staff manually upload manipulated data into FAS via a batch process on a monthly basis o The data files are pulled from JP Morgan Chase's site and uploaded into Assess o The data is manipulated in Access and exported to Excel o The Excel tables are batch uploaded into FAS o There are three batch uploads per month (Panel Travel, Staff Travel, and P-Card

Purchases)

The interface transactions include data related to tickets, obligation numbers, and amounts

Treasury Interface

- Transmission of payment files to Treasury multiple times during a day.

Appendix B: Sample Input and Output parameters for a Business Transaction

- Commitment Number

- Obligation Number

- Proposal Number

- Award Number

- Bill Number

- Disbursement Number

- Document Number

- Reference Document Number

- Document Type

- General Ledger Account code

- Post Date

- Funding Code Information

- Financial Classification Code Information

- Custom Reference Codes

- Allotment Information

- Cost Center Information

- Transaction Amount

- Vendor or Grantee information

- DUNS Number

- Individual Information

- Social Security Number

- Transaction Year

- Performance Year

- Transaction Request Information

- Transaction Code

- Transaction Flags

- Transaction ID

- Batch ID

- Post Status

- Error Message

- Audit Details