FEMA_RFI_HSFE90-14-I-0018_Attachment_B_JADE-TSA_Contractor_ICD.pdf

Transitional Sheltering Assistance (TSA)

JADE and TSA Contractor Interface Control Document

Attachment #1

Version 1.0

March 18, 2014

JADE and TSA Contractor Interface Control Document

For Official Use Only Do Not Distribute - Procurement Sensitative Information Page 2 of 8

Table of Contents

1. Purpose

2. Scope

3. System Identification

3.1 TSA System

3.2 JADE TSA Transfer

3.3 JADE TSA Import

3.4 JADE TSA Export

4. JADE TSA / Transfer-Interface Requirements

4.1 Interface Overview

4.1.1 Importing to FEMA

4.1.2 Exporting to TSA

4.2 Data Transfer

4.2.1 Importing to FEMA

4.2.2 Exporting to TSA

4.3 Interface Initiation

4.4 Nonfunctional Requirements

4.5 Security and Integrity

5. Detailed TSA Interface Design

5.1 Transaction Characteristics

6. Appendix A: Acronyms and Glossary

6.1 Acronyms

6.2 Glossary

For Official Use Only Do Not Distribute - Procurement Sensitative Information Page 3 of 8

1. Purpose The purpose of the ICD is to specify interface requirements between the FEMA JAVA Automation Determination Engine (JADE) Application processes and a TSA Contractor System.

It describes the operations for the interface, defines the message structure and protocols which govern the interchange of data, and identifies the communication paths along which the data is expected to flow.

2. Scope

The scope of this document is to provide an overview of the interface between FEMA’s JADE processes Transitional Sheltering Assistance (TSA) and the external TSA Contractor System interface. This will include a description of the declaration of the stored procedure and Structured Query Language (SQL) calls by TSA and describe the data exchange, exception handling, and potential risks that may impact this solution.

3. System Identification This section describes the two applications that are involved in the interface for importing and exporting.

Import to FEMA

Export to TSA Contractor

For Official Use Only Do Not Distribute - Procurement Sensitative Information Page 4 of 8

3.1 TSA System

FEMA will use a TSA Contractor to assist qualified disaster applicants with lodging needs. The TSA Contractor will provide lodging services and solutions for corporate rate lodging. The TSA Contractor system shall use a Secure File Transfer Protocol (SFTP) to import and export disaster applicant and lodging data. The contractor is responsible for development and deployment of a .com SFTP service.

3.2 JADE TSA Transfer

The JADE TSA Transfer process copies Comma Separated Value (CSV) files between FEMA and the contractor using Secure File Transfer Protocol (SFTP). CSV files containing eligible applicant information are copied from FEMA to the contractor, while billing and hotel information are copied from the contractor to FEMA.

The TSA Transfer is a JADE process running in a multi-tier web application that is hosted on an Oracle Application Server 10g (12C) platform (NOTE: FEMA is pending a 12C upgrade). The background process is written in Java. The FEMA contracted developer is responsible for development and deployment of TSA Transfer.

3.3 JADE TSA Import

The JADE Transitional Sheltering Assistance (TSA) Import process is responsible for importing lodging assistance data form the contractor system into FEMA NEMIS system. The TSA Import process waits for import files transferred to JADE system server(s) by the TSA Transfer Process and imports the CSV files.

TSA Import is a JADE process running in a multi-tier web application that is hosted on an Oracle Application Server 10g (or 12C) platform. The background process is written in Java.

The backend consists of Oracle PLSQL executing on an Oracle 11g database. The processes can be monitored in the JADE Console. The FEMA Contracted developer is responsible for development and deployment of TSA.

3.4 JADE TSA Export

The JADE TSA Export process is responsible for exporting disaster survivor data that are eligible for lodging assistance from FEMA NEMIS system to the TSA Contractor systems. The TSA Export process gets qualified logging assistance registration cases from NEMIS database and generates CSV files for exporting. The TSA Contractor Transfer process copies the exported CSV files to the TSA contractor.

TSA Export is JADE process running in a multi-tier web application that is hosted on an Oracle Application Server 10g (or 12C) platform. The background process is written in Java. The backend consists of Oracle PLSQL executing on an Oracle 11g database. The processes can be

For Official Use Only Do Not Distribute - Procurement Sensitative Information Page 5 of 8 monitored in the JADE Console. The FEMA Contracted developer is responsible for development and deployment of the interface and database for TSA.

4. JADE TSA / Transfer-Interface Requirements The JADE TSA Transfer Processes interfaces with the TSA Contractor System to import and export data files. The JADE TSA Export process generates data files to export data to the TSA Contractor System. The JADE TSA Import Process handles import data files transferred into JADE from the TSA Contractor’s Simple Mail Transfer Protocol (STMP) site.

4.1 Interface Overview

4.1.1 Importing to FEMA

The JADE TSA Transfer Process monitors two directories on the TSA Contractor’s Secure File Transfer Protocol (SFTP) site for new TSA Billing and Hotel files to import. The TSA Contractor site shall have a directory location to store billing CSV files and another directory location to store hotel CSV files. This process transfers each CSV file via SFTP from the TSA Contractor SFTP site to the JADE server.

The JADE TSA Import process waits for the TSA Transfer Process to download files from the TSA Contractor. When a file is received, each line of the file is parsed and a database table on NPSC Server is updated. Depending on the type of file, different tables can be updated:

Billing file – Updates ela_import_file and ela_import_record Hotel file – Updates ela_hotel_def

CSV files that have been successfully imported are stored in a directory on the JADE server for auditing purposes.

4.1.2 Exporting to TSA

The JADE TSA Export process checks the NEMIS registration queue for cases in ‘ELAELIG’ status to setup Transitional Sheltering Assistance (TSA) for qualified applicants. It writes applicant and eligibility information to a CSV file and records the export transaction in ELA_EXPORT_RECORD table. A CSV file is closed for transfer when either condition occurs:

1. Contains 10,000 records

2. Has not had any new records in 15 minutes

3. Was created an hour ago.

When the CSV file is closed, it is copied to a TSA Transfer export directory.

JADE TSA Transfer process monitors the export directory for CSV files ready for transfer. The directory is checked every 10 minutes. This process transfers each CSV file via Secure File

For Official Use Only Do Not Distribute - Procurement Sensitative Information Page 6 of 8

Transfer Protocol (SFTP) to the TSA Contractor SFTP site. The export tells the TSA Contractor system the applicants that are eligible for TSA program.

4.2 Data Transfer

4.2.1 Importing to FEMA

TSA Transfer Process checks the configuration <downloads> elements to obtain the contractors SFTP sites remote directories <remote_dir> for import file locations. The process also checks <downloads> for the JADE server import target directories <local dir>; FEMA JADE copy to location (See Appendix). Currently the Process imports:

TSAR Imports TSA Hotel Imports Leasing Imports Billing Imports

Files that are imported into NEMIS will reside on the JADE server until processed by TSA Import Process. All imported CSV files are defined as the same column layout as the ELA_IMPORT_REC table for bill files. The filename and the status indicate the record type and determine what columns in the record are filled. A hotel file will import and store its data in the ELA_HOTEL_DEF table.

Filename contains ELA_import_record Status

Additional JADE Processing

ELA_CHECKINOUT I or O

403ELA_BILL B

ELA_HOTEL n/a Stored in ELA_hotel_def

_APPRVAL_ REIMBURSE_APPRV

TSAR_403BILL or _TSAR_BILL_ REIMBURSE_BILL

TSAR_408BILL 408_TSAR_BILL

_DENY_ REIMBURSE_DENY

_INCOMP_ REMIBURSE_NCOMP

408ELA_BILL 408

TSA Importing processes the received CSV files and imports the data into NPSC tables.

4.2.2 Exporting to TSA

For Official Use Only Do Not Distribute - Procurement Sensitative Information Page 7 of 8

TSA Exporting generates Exporting CSV file(s) and when the process is done creating the file it copies the file to the TSA Transfer exporting directory when the process is completed.

TSA Process check it’s configuration <uploads> elements to obtain export directory locations on JADE server and the TSA Contractor’s SFTP sites remote directories <remote_dir> export to location to transfer the CSV files to the TSA Contractor.

4.3 Interface Initiation

To access NEMIS NPSC database, a data source with database server’s host name and port is configured in the JADE processes configuration files. A service id with execute privilege is created for JADE by the DB application. To access TSA Contractor system, the JADE process has configuration files with server host and import and export configuration parameters.

4.4 Nonfunctional Requirements

The JADE TSA Leasing processes for importing and exporting are available 24 hours a day and 7 days a week to meet the needs of FEMA NEMIS system requirements. The TSA System should also be available 24 hours a day and 7 days a week to meet FEMA NEMIS system requirements.

4.5 Security and Integrity

JADE processes accesses oracle database through a JDBC connection. In order to connect to the NEMIS NPSC database and execute the necessary SQL procedures, the FEMA & FEMA Contracted Developer DB teams must grant the JADE service account execute privileges on the NPSC database.

JADE processes access remote importing and exporting SFTP directories on the TSA Contractor’s .com site. To access the TSA system and execute SFTP request the appropriate security certificates must be installed into the JDK on JADE servers.

5. Detailed TSA Interface Design

5.1 Transaction Characteristics

5.1.1 Importing to FEMA

TSA Transfer Process checks the TSA Contractor’s SFTP sites remote directories and transfers CSV files found into the JADE server(s) for TSA Import Processing.

All imported CSV files are defined as the same column layout as the ELA_IMPORT_RECORD table for bill files. The filename and the status indicate the record type and determine what columns in the record are filled. A hotel file will import and store data in the ELA_HOTEL_DEF table.

For Official Use Only Do Not Distribute - Procurement Sensitative Information Page 8 of 8

5.1.2 Exporting to TSA Contractor

The TSA Export process invokes a stored procedure ELA_REQUEST to setup Transitional Sheltering Assistance for qualified applicants. The TSA Process selects cases for export that are written to the registration queue with queue_type of 'ELAELIG, 'ELAAPPEAL', 'ELAD' or 'ELAUPDATE'. The registration is added to the CSV export file and to the ELA_EXPORT_RECORD table.

6. Appendix A: Acronyms and Glossary

6.1 Acronyms

Acronym Explanation

COCO Contractor Owned Contractor Operated

EFT Electronic Funds Transfer

FEMA Federal Emergency Management Agency

JADE Java Auto Determination Engine

JDBC Java Database Connectivity

OC4J Oracle10iAS Containers For J2EE

NEMIS National Emergency Management Information System

SQL Structured Query Language

ST&T System Testing and Training

TSA Transitional Sheltering Assistance

TSAI Transitional Sheltering Assistance Information

TSAR Transitional Sheltering Assistance Reimbursement

6.2 Glossary

Term Definition