SSIS Data Interchange Specification

Data Interchange – Vendor Import

Prepared By:

_Greg_Doyle_

Last Updated:

03/03/05

Change Sheet

Revision

Description

Date

Original document

8/1/04

A

Product Team review

8/23/04

B

Added: • verbiage to specify import would trim trailing blanks, • import scenario showing placement of data in import record, • descriptions of import status & action codes, • note that we will use U - update unlike push pull’s C - change to avoid confusion with IFS’ C which is a legacy code, • design decision to produce an operator’s guide • design decisions to produce an implementation document.

8/30/04

Removed: • removed non-IFS file and field references in import / export, • section 6-design because sections 1-5 describe the design. C

Updated logical model to include import log’s table.

9/10/04

Added SQL data types to import file and vendors in SSIS file. Added four fields to import file: Unique ID, date processed, time processed, results of processing. Added example of import log in Appendix D Removed underlines from data import scenario. Corrected table misspelling: BUS_ORD to BUS_ORG. Defined results of processing code found on vendor import file and SSIS import log. Added SERVICE_AUTH to list of DB changes. Spelling corrections D

Change import field names. Add two warning messages to import.

10/26/04

E

Allow these fields to be individually set to editable even if import is turned on. This is to accommodate counties that may not have a particular field in their County Accounting System to be imported.

12/15/04

VIFSSN, VIPHON, VIFAX, VIEMAL, VIPAYE, VIMAID,

VI1099, VINPI, VICNTY. VINPI is not editable in any circumstances until national provider identifiers are assigned by some external agency. Added Description field to custom county vendor import file. This field does not exist in the IFS version of the table, but IFS does not have data for this field. Using an ODBC connection will allow the import service to access both versions of the table without error. Change VIKEY from type Varchar2 to Number. F

Change initial load narrative to say inactive vendors will be updated but won’t be added to SSIS’ database.

1/6/05

G

Removed ‘record locked’ in SSIS import error list.

3/3/05

Table Of Contents SECTION ONE: INTRODUCTION ....................................................................... 1 1.0 Introduction .......................................................................................... 1 1.1 Overview .............................................................................................. 1 1.2 Project Objectives .................................................................................. 1 SECTION TWO: DATA MODEL .......................................................................... 2 2.0 Introduction .......................................................................................... 2 2.1 Data Model ........................................................................................... 2 2.1.1

Vendor Data................................................................................. 4

2.1.1.1 2.1.2

Imported Vendor data items ...................................................... 4

SSIS table data ............................................................................ 5

2.1.2.1

BUS_ORG ............................................................................... 5

2.1.2.2

ADDRESS ............................................................................... 8

2.1.2.3

PHONE - for telephone #........................................................... 9

2.1.2.4

PHONE - for fax # ...................................................................11

2.1.2.5

'Vendors in SSIS' table ............................................................13

2.2 Code Definitions ...................................................................................14 2.3 Data transfer scenario ...........................................................................15 SECTION THREE: DATA INTERCHANGE ............................................................18 3.0 Introduction .........................................................................................18 3.1 Data Exchange Format ..........................................................................18 3.2 Interchange Protocol .............................................................................18 3.2.1

County Accounting System to SSIS data flow...................................21

3.2.2

County Accounting System actions .................................................21

SECTION FOUR: ERROR HANDLING .................................................................24 4.0 Introduction .........................................................................................24 4.1 Data Errors ..........................................................................................24 4.2 System Errors ......................................................................................27 4.3 Network Errors .....................................................................................27 SECTION FIVE: SUPPORTABILITY AND USABILITY .............................................28 5.0 Introduction .........................................................................................28 5.1 Supportability.......................................................................................28 5.1.1

Software Maintenance ..................................................................28

5.1.2

System Operation ........................................................................28

5.2 Usability ..............................................................................................28

5.2.1

Security .....................................................................................28

5.2.2

Interfaces ...................................................................................28

5.2.3

Performance ...............................................................................29

5.2.4

Error notification..........................................................................29

5.2.5

Scheduled operations ...................................................................29

APPENDIX A: ISSUES AND DESIGN DECISIONS ...............................................30 APPENDIX B: Interface processing / flow diagrams ...........................................32 APPENDIX C: Initial load ...............................................................................34 APPENDIX D: Import log ...............................................................................35 RELATED DOCUMENTS...................................................................................36 GLOSSARY ...................................................................................................37

SECTION ONE: INTRODUCTION

1.0 Introduction This document is the Data interchange Specification for the Vendor data, County Accounting System interface. It describes the functional requirements and design approach between SSIS and County Accounting Systems for timely transfer of vendor data to SSIS and for return of verification of vendors in use to the County Accounting System. Section one provides a project overview and Sections two through five describe requirements and design.

1.1 Overview The interconnections between the County Accounting System and SSIS are shown in Figure 1-1. County accounting system (IFS or other)

Social Services vendor data

SSIS Fiscal

Vendor numbers in SSIS / Vendor numbers allowable to delete

Figure 1-1. Overview information flow Diagram

1.2 Project Objectives The primary objectives of the County Accounting System interface development are to ensure vendor information is the same in each system and to reduce duplicate data entry. The interface will do this by importing social services vendor information into SSIS so that SSIS will have the same information as in the County Accounting System. When the appropriate conditions are met, the County Accounting System will also be able to send a vendor purge request to SSIS, and SSIS will purge that vendor from the database.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 1 of 37

SECTION TWO: DATA MODEL

2.0 Introduction This section describes the data exchanged between the County Accounting System and SSIS. It defines the interface files, and shows the relevant sections of the SSIS database.

2.1 Data Model The data model depicted in Table 2-1 shows the SSIS Oracle tables where vendor data resides. Also shown in Tables 2-2 through 2-13 are the record layout of the imported vendor table, the SSIS tables that will have records created or updated to store the imported data, and the table of vendors in SSIS with their delete flags that are updated on the County Accounting System.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 2 of 37

TMALT_FACILITY

TBUS_ORG_XREF

0..*

TBUS_ORG_TYPE BUS_ORG_ID

0..1

TBUS_ORG_RELATION

# BUS_ORG_XREF_ID: Double; # BUS_ORG_ID: TBUS_ORG; # BUS_ORG_ID2: TBUS_ORG; # BUS_ORG_RELATION_ID: TBUS_ORG_RELATION; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; 0..* BUS_ORG_ID2

BUS_ORG_TYPE_ID

0..*

# BUS_ORG_RELATION_ID: Double; 1 + BUS_ORG_REL_DESC: string; + BUS_ORG_REL_DESC2: string; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime;

BUS_ORG_RELATION_ID

0..* BUS_ORG_ID

TWILL_SERVE_PREFS

TWG_FOLDER 1

0..* BUS_ORG_ID TPROVIDER

1

1

1

# WILL_SERVE_PREFS_ID: Double; + GENDER_WILL_SRV_CD: string; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; + MAX_AGE_WILL_SRV: Double; + MIN_AGE_WILL_SRV: Double; + PREF_CLIENTS_CD: string;

TPROV_LIC_DETAIL

TBUS_ORG

0..* BUS_ORG_ID

# BUS_ORG_ID: Double; # BUS_ORG_TYPE_ID: TBUS_ORG_TYPE; + BUS_NAME: string; + BUS_ORG_DESC: string; BUS_ORG_ID 1 + BUS_ORG_STATUS_CD: string; + BUS_ORG_STATUS_DT: TDateTime; 0..1 + CNTY_PROV_NUM: string; + FED_TID: string; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; + MMIS_PROV_NUM: string; + VOUCHER_METH_CD: string; 1

# PROV_ID: Double; # BUS_ORG_ID: TBUS_ORG; + CHILD_FC_CD: string; + IV_E_ELIG_PROV_IND: string; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; + REGISTERED_CC_IND: string; + SCHOOL_DIST_NUM: string; + STRUCT_FOST_FAM_CD: string; 1

1

1

1..* WILL_SERVE_PREFS_ID 0..* 1

TBUS_ORG_WSP_XREF 1 0..*

BUS_ORG_ID BUS_ORG_ID

0..*

0..* TIND_PROVIDER

TBUS_ORG_ADDR_XREF

1 TADDRESS BUS_ORG_ID

# ADDRESS_ID: Double; + ADDR_1: string; + ADDR_2: string; + ADDR_3: string; + CITY: string; + CNTY_CD: string; + COUNTRY: string; + DIRECTIONS: string; + FOREIGN_POST_CODE: string; + FOREIGN_STATE_PROV: string; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; + STATE_CD: string; + UPPER_CITY: string; + ZIP_CODE: string; + ZIP_CODE_EXT: string;

BUS_ORG_ID

EMAIL_ID

1 TEMAIL # EMAIL_ID: Double; + EMAIL_ADDRESS: string; + EMAIL_COMMENT: string; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; 1 EMAIL_ID

0..* TPERSON_SCHOOL

0..*

PERSON_ID

0..*

TBUS_ORG_PHONE_XREF # BUS_ORG_PHONE_ID: Double; # BUS_ORG_ID: TBUS_ORG; # PHONE_ID: TPHONE; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; + PHONE_TYPE_CD: string; 0..* PHONE_ID

1 TPHONE # PHONE_ID: Double; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; + PHONE: string; + PHONE_COMMENT: string; 1

1

0..*

TPERSON_EMAIL_XREF # PERSON_EMAIL_ID: Double; # EMAIL_ID: TEMAIL; # PERSON_ID: TPERSON; + EMAIL_TYPE_CD: string; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; PERSON_ID 0..*

0..*

0..* ADDRESS_ID

0..*

0..*

# BUS_ORG_WSP_XREF_ID: Double; # BUS_ORG_ID: TBUS_ORG; # WILL_SERVE_PREFS_ID: TWILL_SERVE_PREFS; + DEFAULT_WSP_IND: string; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime;

0..*

TBUS_ORG_EMAIL_XREF # BUS_ORG_EMAIL_ID: Double; # BUS_ORG_ID: TBUS_ORG; # EMAIL_ID: TEMAIL; + EMAIL_TYPE_CD: string; + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime;

BUS_ORG_ID

1

1

BUS_ORG_ID PROV_ID

1

ADDRESS_ID

TPERSON_WORK

PHONE_ID

0..* 0..* TPERSON_ADDRESS_XREF 0..*

0..*

PERSON_ID

TPERSON_PHONE_XREF # PERSON_PHONE_ID: Double; # PERSON_ID: TPERSON; # PHONE_ID: TPHONE; 0..* + LAST_CHGD_BY: Double; + LAST_CHGD_DT: TDateTime; + PHONE_TYPE_CD: string;

PERSON_ID PERSON_ID

1

1

1 1 1

PERSON_ID

TPERSON 1 IMPORT_LOG + SYNC_LOG_ID: NUMBER(10); + SYNC_TYPE_CD: Char; + SYNC_STATUS_CD: Char; + SYNC_STATUS_DT: DATE; + SYNC_STATUS_MESSAGE: string; + SYNC_DATA: BLOB; + LAST_CHGD_DT: DATE; + LAST_CHGD_BY: NUMBER(10);

Figure 2-1 Logical Data Model

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 3 of 37

2.1.1

Vendor Data

Vendor data will be imported from the County Accounting System which, in 75 counties, is IFS.

2.1.1.1

Imported Vendor data items

Table 2-1 shows the layout for the data being received from a County Accounting System on the left, and on the right the table and field name in SSIS where the data is stored. IFS and non IFS counties may have differing sizes for a data item. In the table below the SSIS field size is the maximum that the field can be. Smaller length fields are acceptable as long as they are big enough to contain the necessary information. IFS is constrained to fixed length fields, but non-IFS counties may store their data in variable length fields. The incoming data is assumed to be left justified in the fields. However, the import utility contains routines to left justify the data if necessary and remove trailing blanks. Section 2.3 contains a data import scenario showing how data being imported is placed in the fields of the import record. County accounting system Field Description name VIKEY (10) Unique record ID for this file VIVNO Vendor number

Req’d

SSIS Fieldname Size

Table Yes

--

Yes

BUS_ORG

Yes Yes No No Yes No Yes

BUS_ORG ADDRESS ADDRESS ADDRESS ADDRESS ADDRESS ADDRESS

No

ADDRESS

VIAME VIADD1 VIADD2 VIADD3 VICITY VIST VIZIP (first 5 digits) VIZIP (last 4 digits) VIFSSN VIPHON VIFAX VIEMAL

Vendor name Address line 1 Address line 2 Address line 3 City State Zip code

Fed ID / SSN Phone number Fax number e-mail address

No No No No

BUS_ORG PHONE PHONE EMAIL

VISTAT

Status

Yes

BUS_ORG

VIPAYE

Payee Vendor number **See Note MMIS MA Provider ID 1099 Vendor

No

BUS_ORG _XREF

No

BUS_ORG

No

BUS_ORG

VIMAID VI1099

DI Vendor Import

Last Updated: 03/03/05

(this value is not stored in SSIS) CNTY_ PROV_NUM BUS_NAME ADDR_1 ADDR_2 ADDR_3 CITY STATE_CD ZIP_CODE ZIP_CODE_EX T FED_TID PHONE PHONE EMAIL_ ADDRESS BUS_ORG_ STATUS_ CD BUS_ORD_ ID2

MMIS_ PROV_NUM DEFAULT_ 1099_CD

Attrib

--

--

SQL data type Number

20

String

Varchar2

50 100 100 100 25 2 5

String String String String String String String

Varchar2 Varchar2 Varchar2 Varchar2 Varchar2 Char(2) Char(5)

4

String

Varchar2

9 50 50 100

String String String String

Varchar2 Varchar2 Varchar2 Varchar2

1

Alpha

Char

20

String

Varchar2

10

String

Varchar2

1

Alpha

Char

Data Interchange Vendor Import

Page 4 of 37

County accounting system Field Description name VINPI National provider identifier VICNTY County code VIP_DT Date / Time (date) processed in to SSIS. VIR_CD Results of process (char) attempt (successful, failed) VIA_CD Processing action VIDESC

Description

Req’d

SSIS Fieldname Size

Table

Attrib

SQL data type Varchar2

No

BUS_ORG

NPI

10

String

No No

ADDRESS --

CNTY_CD (this value is not stored in SSIS)

2 --

String --

Varchar2 Date / Time

No

--

(this value is not stored in SSIS)

--

--

Char

Yes

--

(this value is not stored in SSIS) No BUS_ORG BUS_ORG_ DESC Table 2-1 imported vendor data items

--

--

Char

String

Varchar2

200

**Note – the imported payee number is not stored as it was imported. The corresponding Bus Org ID is looked up in the BUSORG table, and that value is stored instead. The three fields: VIKEY, VIP_DT and VIR_CD are used to allow SSIS to correctly process the records in the file. VIKEY is an ascending sequential number used for the key. It is populated by the county accounting system when the record is written to the file. The fields VIP_DT and VIR_CD are populated by SSIS when the record is processed. They are the processing date and time by SSIS of this record, and the results of the import process. When an import processing pass completes, the import process will remove records with processing dates that are more than a year old. Doing so will retain the last year’s vendor import records in the file as an aid to troubleshooting. If an attempted import fails, the vendor information in the County Accounting System must be updated to correct the error. When the corrections are made the vendor data will be re-sent to SSIS for another import attempt.

2.1.2

SSIS table data

This section shows the tables in SSIS that are populated with imported data. Some SSIS fields are not available for import from the county accounting system. For those fields, the ‘Value’ column contains the words . When the import process creates or updates the SSIS record, fields designated are left null.

2.1.2.1

BUS_ORG

Table 2-2 shows, for a Bus Org record, the source of the data on the left and on the right the fields where the data will be stored. A BUS_ORG record is created or updated with each successful import. Value System generated VIVNO** See note

Field Description Unique key for this record. Vendor number

DI Vendor Import

Last Updated: 03/03/05

Required / Optional System generated Required

BUS_ORG. Field Name BUS_ORG_ID CNTY_PROV_NUM

Data Interchange Vendor Import

Page 5 of 37

Value

Field Description

VINAME ‘other’ code VIMAID

Vendor name Description

VISTAT system date, when VISTAT changes VI1099

Status Effective date of a change

VINPI VIFSSN ’import process’

system date

Bus Org type MMIS provider ID

1099 billing indicator National provider ID Fed ID / SSN

Required / Optional Required Optional

BUS_ORG. Field Name BUS_NAME BUS_ORG_DESC

System generated Optional *See Note Required System generated

BUS_ORG_TYPE_ID

Optional *See Note Optional *See Note Optional *See Note System generated

DEFAULT_ 1099_CD

MMIS_PROV_NUM BUS_ORG_STATUS_CD BUS_ORG_STATUS_ DT

NPI FED_TID

User ID of person LAST_CHGD_BY or process that last changed this record. Date this record System LAST_CHGD_DT was added, or generated changed last. Table 2-2 Bus Org table showing sources of data

* Note – some fields may not be available in the County Accounting System for import. In the absence of these fields being available, the county may elect to individually exclude them from import and make them editable in SSIS even though they would otherwise not be. These fields include: VIFSSN VIMAID VINPI VI1099

Fed ID / SSN MMIS MA Provider ID NPIP (National Provider Indicator) 1099 Vendor

** Note: VIVNO is the key for linking the County Accounting System vendor information to SSIS for both entering new information and for updating existing information. Table 2-3 shows, for a BUS_ORG_XREF record, the source of the data on the left and on the right the fields where the data will be stored. This table is maintained by the system when an imported vendor has a value in ‘VIPAYE’. The BUS_ORG_XREF table is used to store imported payee relationship information. If a payee number is sent, SSIS will use it to locate the corresponding BUS_ORG_ID. SSIS will then use the county vendor number to look up the current vendor’s BUS_ORG_ID and use these two IDs in the BUS_ORG_XREF record. By having the two BUS_ORG_IDs and the relation code SSIS knows who to pay. If no payee is specified, this record is not created. If the payee number is changed, SSIS will update the BUS_ORG_ID field below. If the payee number is removed, SSIS will delete the corresponding BUS_ORG_XREF record.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 6 of 37

Value

Field Description

System generated VIPAYE

Unique key for this record. Bus Org ID of Payee ** See Note Bus Org ID of this vendor ** See Note Relation code

VIVNO

‘2’ (Payee)

’import process’

system date

Required / Optional System generated System generated System generated System generated. Based on the presence of a Payee # in the imported data. *See Note System generated

BUS_ORG_XREF. Field Name BUS_ORG_XREF_ID BUS_ORG_ID

BUS_ORG_ID2

BUS_ORG_RELATION_ ID

User ID of person or process that last LAST_CHGD_BY changed this record. Date this record System was added, or generated LAST_CHGD_DT changed last. Table 2-3 Bus Org Xref table showing sources of data *See Note

* Note – some fields may not be available in the County Accounting System for import. In the absence of these fields being available, the county may elect to individually exclude them from import and make them editable in SSIS even though they would otherwise not be. These fields include: VIPAYE

Payee

**Note – the imported payee and vendor numbers are not stored as they were imported. The corresponding Bus Org IDs are looked up in the BUS_ORG table, and those values are stored instead.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 7 of 37

2.1.2.2

ADDRESS

Table 2-4 shows, for an Address record, the source of the data on the left and on the right the fields where the data will be stored. An ADDRESS record is created or updated with each successful import. Value System generated VICNTY

Unique key for this record. County code

VIADD1 VIADD2

Address Line 1 Address Line 2

VIADD3

Address Line 3

VICITY

City

VIST VIZIP VIZIP System generated

state Zip code (1-5) Zip code (6-9) Foreign country

Required / Optional System generated Optional ** See note Required Optional *See Note Optional *See Note Optional *See Note Required Required Optional Optional

Foreign post code

Optional

Directions

Optional

Upper case address used for searches. Foreign state

System generated

System generated ’import process’

system date

Field Description

Optional

ADDRESS. Field Name ADDRESS_ID CNTY_CD ADDR_1 ADDR_2 ADDR_3 CITY STATE_CD ZIP_CODE ZIP_CODE_EXT COUNTRY FOREIGN_POST_CODE DIRECTIONS UPPER_CITY FOREIGN_STATE_ PROV

Concatenated System CONCAT_ADDR address used for generated searches. User ID of person System or process that generated LAST_CHGD_BY last changed this record. Date this record System was added, or generated LAST_CHGD_DT changed last. Table 2-4 Address table showing sources of data

* Note: If VIADD1 is blank, the import process will shift the other address lines up and attempt to fill Address Line 1. ** Note – some fields may not be available in the County Accounting System for import. In the absence of these fields being available, the county may elect to individually exclude them from import and make them editable in SSIS even though they would otherwise not be. These fields include: VIADD2 VIADD3 VICNTY

Address Line 2 Address Line 3 County code

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 8 of 37

Table 2-5 shows, for an Address cross reference record, the source of the data on the left and on the right the fields where the data will be stored. This table is maintained by the system for the imported vendor address. An address cross reference record is required to connect a BUS_ORG record to the ADDRESS record. When an address is changed the import process will put an end date of system date minus one day on the old address record and write another record with the system date for the start date. Value System generated System generated System generated ‘A’ (Payment Address) system date System generated ’import process’

system date

Field Description Unique key for this record. Bus Org this record pertains to. Address this record pertains to. Type of address Address start effective date. Address end effective date. Care of line.

Required / Optional System generated System generated System generated System generated System generated System generated **See note Optional

BUS_ORG_ADDR_XREF. Field Name BUS_ORG_ADDR_ID BUS_ORG_ID ADDRESS_ID ADDR_TYPE_CD START_DT END_DT

CARE_OF_LINE

User ID of person System LAST_CHGD_BY or process that last generated changed this record. Date this record System LAST_CHGD_DT was added, or generated changed last. Table 2-5 Bus Org Address Xref table showing sources of data

** Note: END_DT above is left null until this address record is superseded. When that happens, this record’s end date is set to the day before the next record’s start date.

2.1.2.3

PHONE - for telephone #

Table 2-6 shows, for a Phone record, the source of the data on the left and on the right the fields where the data will be stored. This record is created based on the presence of a phone number in VIPHON in the Vendor import table. Value

Field Description

System generated VIPHON

Unique key for this record. Phone number



Comment.

DI Vendor Import

Last Updated: 03/03/05

Required / Optional System generated Optional *See Note Optional

PHONE. Field Name PHONE_ID PHONE PHONE_COMMENT

Data Interchange Vendor Import

Page 9 of 37

Value

Field Description

’import process’

User ID of person LAST_CHGD_BY or process that last changed this record. Date this record System LAST_CHGD_DT was added, or generated changed last. Table 2-6 Phone table showing sources of data *See Note

system date

Required / Optional System generated

PHONE. Field Name

** Note – some fields may not be available in the County Accounting System for import. In the absence of these fields being available, the county may elect to individually exclude them from import and make them editable in SSIS even though they would otherwise not be. These fields include: VIPHON

Phone number

Table 2-7 shows, for a Phone cross reference record, the source of the data on the left and on the right the fields where the data will be stored. This record is created based on the presence of a phone number in the Vendor import table. This table is maintained by the system for the imported vendor phone. A phone cross reference record is required to connect a BUS_ORG record to the PHONE record. The BUS_ORG_PHONE_XREF table is used to associate a phone number with a Bus Org. If a phone number is sent, SSIS will compare it to the previous phone number. If it has changed, SSIS will update the PHONE_ID in the existing BUS_ORG_PHONE_XREF record. If the phone number is blank, SSIS will remove this record and the associated PHONE record. If this is a new phone number (the previous phone number was blank), SSIS will create the PHONE record and BUS_ORG_PHONE_XREF record. The import process will only create or update a phone type of ‘0’ which is a payment phone. Value System generated System generated System generated ‘0’ (Payment) ’import process’

system date

Field Description

Required / Optional System generated System generated System generated System generated System generated

BUS_ORG_PHONE_XREF. Field Name BUS_ORG_PHONE_ID

Unique key for this record. Bus Org this record BUS_ORG_ID pertains to. Phone number this PHONE_ID record pertains to. Type of phone PHONE_TYPE_CD number User ID of person LAST_CHGD_BY or process that last changed this record. Date this record System LAST_CHGD_DT was added, or generated changed last. Table 2-7 Bus Org Phone Xref table showing sources of data

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 10 of 37

2.1.2.4

PHONE - for fax #

Table 2-8 shows, for a Fax record, the source of the data on the left and on the right the fields where the data will be stored. This record is created based on the presence of a phone number in VIFAX in the Vendor import table. Value

Field Description

System generated VIFAX

Unique key for this record. Fax number

’import process’

Comment.

Required / Optional System generated Optional *See Note Optional

PHONE. Field Name PHONE_ID PHONE PHONE_COMMENT

User ID of person System LAST_CHGD_BY or process that last generated changed this record. system date Date this record System LAST_CHGD_DT was added, or generated changed last. Table 2-8 Phone table showing sources of data for the fax # *See Note ** Note – some fields may not be available in the County Accounting System for import. In the absence of these fields being available, the county may elect to individually exclude them from import and make them editable in SSIS even though they would otherwise not be. These fields include: VIFAX

Fax number

Table 2-9 shows, for a Fax cross reference record, the source of the data on the left and on the right the fields where the data will be stored. This record is created based on the presence of a Fax number in the Vendor import table. This table is maintained by the system for the imported vendor fax. A phone cross reference record is required to connect a BUS_ORG record to the PHONE record. The BUS_ORG_PHONE_XREF table is also used to associate a fax number with a Bus Org. If a fax number is sent, SSIS will compare it to the previous fax number. If it has changed, SSIS will update the PHONE_ID in the existing BUS_ORG_PHONE_XREF record. If the fax number is blank, SSIS will remove this record and the associated PHONE record. If this is a new fax number (the previous fax number was blank), SSIS will create the PHONE record and BUS_ORG_PHONE_XREF record. The import process will only create or update a fax type of ‘A’ which is a payment fax. Value System generated System generated System generated

Field Description Unique key for this record. Bus Org this record pertains to. Phone number this record pertains to.

DI Vendor Import

Last Updated: 03/03/05

Required / Optional System generated System generated System generated

BUS_ORG_PHONE_XREF. Field Name BUS_ORG_PHONE_ID BUS_ORG_ID PHONE_ID

Data Interchange Vendor Import

Page 11 of 37

Value ‘A’ (Payment fax) ’import process’

system date

Field Description Type of phone number.

Required / Optional System generated

BUS_ORG_PHONE_XREF. Field Name PHONE_TYPE_CD

User ID of person System LAST_CHGD_BY or process that last generated changed this record. Date this record System LAST_CHGD_DT was added, or generated changed last. Table 2-9 Bus Org Phone Xref table showing sources of data

EMAIL Table 2-10 shows, for an Email record, the source of the data on the left and on the right the fields where the data will be stored. This record is created based on the presence of an email address in VIEMAL in the Vendor import table. Value

Field Description

System generated VIEMAL

Unique key for this record. E-mail address

’import process’

Comment.

Required / Optional System generated Optional *See Note Optional

EMAIL. Field Name EMAIL_ID EMAIL_ADDRESS EMAIL_COMMENT

User ID of person System LAST_CHGD_BY or process that last generated changed this record. Date this record System LAST_CHGD_DT was added, or generated changed last. Table 2-10 Email table showing sources of data *See Note

system date

** Note – some fields may not be available in the County Accounting System for import. In the absence of these fields being available, the county may elect to individually exclude them from import and make them editable in SSIS even though they would otherwise not be. These fields include: VIEMAL

e-mail address

Table 2-11 shows, for an Email cross reference record, the source of the data on the left and on the right the fields where the data will be stored. This record is created based on the presence of an email address in the Vendor import table. This table is maintained by the system for the imported vendor email. An email cross reference record is required to connect a BUS_ORG record to the EMAIL record. The BUS_ORG_EMAIL_XREF table is also used to associate an email address with a Bus Org. If an email address is sent, SSIS will compare it to the previous email address. If it has changed, SSIS will update the EMAIL_ID in the existing BUS_ORG_EMAIL_XREF

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 12 of 37

record. If the email address is blank, SSIS will remove this record and the associated EMAIL record. If this is a new email address (the previous email address was blank), SSIS will create the EMAIL record and BUS_ORG_EMAIL_XREF record. The import process will only create or update an email type of ‘0’ which is a payment email. Value System generated System generated System generated ‘0’ (Payment) ’import process’

system date

Field Description

Required / Optional System generated System generated System generated System generated System generated

BUS_ORG_EMAIL_XREF. Field Name BUS_ORG_EMAIL_ID

Unique key for this record. Bus Org this record BUS_ORG_ID pertains to. Email address this EMAIL_ID record pertains to. Type of email EMAIL_TYPE_CD address. User ID of person LAST_CHGD_BY or process that last changed this record. Date this record System LAST_CHGD_DT was added, or generated changed last. Table 2-11 Bus Org Email Xref table showing sources of data

2.1.2.5

'Vendors in SSIS' table

SSIS is responsible for returning a list of county vendor numbers it uses to the County Accounting System. The structure of the exported information is shown in the table below. The table name is SSISVNDR. Import data Field name

VIVNO VIDELT

Description

Size

Attrib

SQL data type

County provider # 20 Alpha Varchar2 Allow delete flag 1 Alpha Varchar Table 2-12. Vendors in SSIS (SSISVNDR) reference table.

This table is populated during vendor import. After the import service has processed all the records in the import file, it queries SSIS for a list of all vendor numbers in the system, and for all those vendor numbers that have payments, service agreements, licenses or placements. If a vendor has any of these records attached to them the VIDELT flag is set to ‘N’. Otherwise it is set to ‘Y’.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 13 of 37

2.2 Code Definitions Table 2-13 describes the codes passed between SSIS and the County Accounting System, their definitions and valid values. Action code (field name-VIA_CD) from the County Accounting System CODE DESCRIPTION A This import record is an Add U This import record is an Update P This import record is a Purge (delete) X This import record is an Initial load Status code (field name-VISTAT) from the County Accounting System CODE DESCRIPTION Null or blank This import record is Active A This import record is Active. C This import record is an Active Social Services vendor. **See note below I This import record is Inactive D This import record is Marked for deletion Results of processing code (field name-VIR_CD) from SSIS CODE DESCRIPTION 0 This import attempt was successful 1 This import attempt generated a warning 2 This import attempt generated an error Allow Delete code (field name-VIDELT) from SSIS CODE DESCRIPTION Y This vendor can be purged from SSIS N This vendor can not be purged from SSIS Table 2-13. Interchange Code Definitions ** Note: IFS has vendor status codes of blank and C, and both indicate an active vendor. The ‘C’ code is to accommodate legacy data.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 14 of 37

2.3 Data transfer scenario The table below is an example of how the county accounting system will fill in one record in the vendor import table. The accounting system must left justify the data it puts in the fields of the vendor import file when the data does not completely fill the field. For alphanumeric fields, if the data being sent is greater than the length of the import field, the rightmost characters are truncated to the length of the import field. Type of data being imported

Data in the County Accounting System

Unique record ID

Data in the vendor import file

Comment

0000000001

Data is required. Field is numeric

Vendor number

1234567890

1234567890

Data required. Data is left justified in vendor import file.

Vendor name

Society for the preservation of the Western American Lifestyle

Society for the preservation of the Western Americ

Data required. Data is left justified in vendor import file.

Address line 1

7058 Constitution Avenue

7058 Constitution Avenue

Data required. Data is left justified in vendor import file.

Address line 2

Data is left justified in vendor import file

Address line 3

Data is left justified in vendor import file. For IFS the field is blank filled.

City

Oakdale

Oakdale

Data is left justified in vendor import file.

State

MN

MN

If data is entered, field must be filled

Zip

551280000

551280000

First 5 digits of data is required. Field must be filled

Fed tax ID

417708571

417708571

If data is entered, field must be filled

Phone

6517723769

6517713769

Data is left justified in vendor import file.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 15 of 37

Type of data being imported

Data in the County Accounting System

Data in the vendor import file

Comment

Fax

6517723768

6517713768

Data is left justified in vendor import file.

Email

sendmeanote@myemail .com

sendmeanote@myemail. com

Data is left justified in vendor import file.

Status

A

A

Data required. Field must be filled

Payee number

1234567855

1234567855

Data is left justified in vendor import file.

MMIS MA ID number

7654321

7654321

Data is left justified in vendor import file.

1099 flag

N

N

If present, Field must be filled

NPI

Vendor county code

Field is not used yet, and is null. AS/400 alpha fields will be blank filled. 23

23

If present, field must be filled

Date / Time processed in to SSIS



SSIS will populate this field.

Results of process attempt



SSIS will populate this field.

Action code

U

Data required. Field must be filled

Description

Data is left justified in vendor import file.

Assuming no errors, SSIS will update the data in its database (the Action code above is ‘U’ for update existing SSIS data) and update the corresponding record in the ‘Vendors in SSIS’ file on the County Accounting System. The related record is shown below. The table below is an example of how SSIS will fill in one ‘Vendors in SSIS’ record.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 16 of 37

Type of data being exported

Data in SSIS

Data in the ‘Vendors in SSIS’ file

Comment

Vendor number

1234567890

1234567890

Data required. Data is left justified in the field.

Allow delete flag

N

N

Data is required.

Table 2-14. Data transfer scenario.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 17 of 37

SECTION THREE: DATA INTERCHANGE

3.0 Introduction This section describes the data exchange between the SSIS and the County Accounting System.

3.1 Data Exchange Format The data exchange format is database to database field exchange. The exchange is by SSIS or the County Accounting System reading the interface tables. SSIS or the County Accounting System will write to the interface tables only.

3.2 Interchange Protocol Data exchange is via ODBC on an IP connection. A possible alternate protocol for Version 4.0 is the use of a SOAP server and SOAP client. The following information is supplied by the county IT staff in order to connect SSIS to the County Accounting System. To access the interface tables, SSIS will need the following import information: • Host name or host IP address • Database type • Database identity (name, number or whatever is used to reference a particular database on the host) • Interface table names for “Import” and “Vendors in SSIS”. We will pre fill the fields with default table names that can be changed if necessary. • User ID and password, which will be encrypted for storage in SSIS. An initial setup document produced by SSIS will contain instructions for setting up the connection to the vendor import file. Refer to appendix A3, Design decision #8 for specifics.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 18 of 37

The import information will be stored in the admin module. County Preferences has a tab called ‘Vendor Import Preferences’ where the information is entered. An example of the screen is shown below.

THIS SCREEN IS A PLACEHOLDER FOR THE IMPORT CONNECTION PARAMETERS PREFERENCE SCREEN THAT WILL BE IN VENDOR IMPORT. The screen is nearly complete but still in process.

Figure 3-1 County Vendor Import Preferences The County Vendor Import Preferences screen is accessed through the Admin module. It is filled in by the county during setup. This information is used by the import service to access the import files and to control how often the import service will run. Another field identifies who will be notified of errors in the process.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 19 of 37

if not all fields are available from the County Accounting System, some can be set to editable if they are not required when a vendor is initially imported. This import selection information will be stored in the admin module. County Preferences has a tab called ‘Vendor Import Field Preferences’ where the information is entered. An example of the screen is shown below.

THIS SCREEN IS A PLACEHOLDER FOR THE IMPORT FIELD SELECTION PREFERENCE SCREEN THAT WILL BE IN VENDOR IMPORT. The following fields may be individually disabled for vendor import. These fields would not be imported even if present. However, they would be editable in SSIS and could be entered that way.

VIFSSN ADDR2 ADDR3 VIPHON VIFAX VIEMAL VIPAYE VIMAID VI1099 VICNTY VIDESC VINPI

Fed ID / SSN Address line 2 Address line 3 Phone number Fax number e-mail address Payee Vendor number MMIS MA Provider ID (after it becomes editable) 1099 Vendor County code Description National Provider Indicator (globally disabled until values are assigned by Federal Government)

Figure 3-2 County Vendor Import Field Preferences The County Vendor Import Preferences screen is accessed through the Admin module. It is filled in by the county during setup. This information is used by the import service to determine which fields will not be imported from the County Accounting System and will still be editable in SSIS even after import is turned on.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 20 of 37

3.2.1

County Accounting System to SSIS data flow

The sequence diagram below depicts the actions of the SSIS vendor import service when it retrieves vendor data. First, the service performs integrity checks on the data to confirm that no fatal errors exist. Second, the service looks for and retrieves data from the vendor import interface table. During this step records in the interface table are marked as having been processed. Third, the service updates the interface table containing the county vendor numbers of all vendor data in SSIS. The data exchanged is described in Section Two of this specification. The integrity checks performed in the first step are described in Appendix A3 Design Decisions.

: SSIS County Server

: County Accounting System

Validate table integrity

Check for / get vendor data

Vendor data returned

Update "Vendors in SSIS" table

Figure 3-3. SSIS get vendor data/put vendor IDs Sequence Diagram.

3.2.2

County Accounting System actions

The action field in the Vendor data import table is populated by the County Accounting System in the following way: •

•

When a vendor is sent to SSIS for the first time (i.e., the vendor number is not found in the SSIS vendors table), the County Accounting System will use the action code of ‘A’. SSIS will add the vendor number to its database and to the SSIS vendors table that resides on the County Accounting System). When updating a vendor previously sent to SSIS (i.e., the county vendor number is found in the SSIS vendors table), the County Accounting System will use an action code of ‘U’.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 21 of 37

•

•

SSIS will update the data on its database and refresh the SSIS vendors table that resides on the County Accounting System. When purging a vendor previously sent to SSIS, the County Accounting System) will use an action code of ‘P’. SSIS will remove the vendor number from its database and from the SSIS vendors table that resides on the County Accounting System. In the case of the initial load/refresh, the County Accounting System will use an action code of ‘X’. SSIS will add/update its database as needed and add/update the vendor number in the SSIS vendors table as needed.

When SSIS receives a vendor record, it will process it according to the action code and status code settings as shown in the table below. This table lists the imported action and status codes from the County Accounting System and what SSIS will do with this imported data. VIA_CD – this code tells SSIS what to do with this imported vendor record. The codes are: • X-Initial load. Add or update this vendor’s data in SSIS as appropriate. • A-Add this record to SSIS. • U-Update this vendor’s data in SSIS. • P-Purge (delete) this vendor’s data from SSIS. VISTAT – this code tells SSIS what the status of this vendor is on the County Accounting System. The codes are: • Null/blank/A – this vendor is active. • C – This vendor is active. (Describes legacy data). • I – This vendor is inactive. • D – This vendor is inactive and pending deletion. VIA_CD VISTAT What will the Vendor Sync Process Do? X X

A,

Add or Update the vendor to SSIS

C

Set BUS_ORG_STATUS_CD = ‘1 - Active”

I

Update the vendor in SSIS if it exists, but DO NOT ADD IT IF IT DOES NOT EXIST. Set BUS_ORG_STATUS_CD = ‘2 - Inactive”

A

A,

Add the vendor to SSIS

C

Set BUS_ORG_STATUS_CD = ‘1 - Active”

U ** see note

A,

Update the vendor in SSIS

C

Set BUS_ORG_STATUS_CD = ‘1 - Active”

U

I,

Update the vendor in SSIS

D

Set BUS_ORG_STATUS_CD = ‘2 - Inactive”

D

Delete the Bus Org in SSIS

P

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 22 of 37

Table 3-1 SSIS’ actions during import * Note – during the initial load only, vendors marked as inactive WILL NOT BE ADDED TO SSIS. This is to keep from re-adding vendors that have been deleted from SSIS and made inactive in the County Accounting System as part of the cleanup effort. ** Note – vendor import action codes are not the same as the action codes used in push pull. Push pull uses ‘A’ for add and ‘C’ for change. Vendor import uses ‘A’ for add and ‘U’ for update. This different code is used to avoid confusion with the status code (VISTAT) which can contain a ‘C’ to indicate legacy social services vendor data. SSIS will send the “SSIS vendors” table back to the County Accounting System to tell what vendors are in SSIS and what vendors can be safely purged from the County Accounting System. This table will contain the vendor number and an ‘allow delete’ flag. The presence of a vendor number in this table means the vendor number exists in SSIS. If the allow delete flag is set to ‘Y’, the County Accounting System is allowed to purge the record from their tables. They then need to send the record to SSIS with a status of ‘D’ and an action of ‘P’. On receipt of this record, SSIS will delete the BUS_ORG record from its database and remove the corresponding record from the “SSIS vendors” table in the County Accounting System. The table below indicates how the County Accounting System will use the information in the SSISVNDR table. Codes in the SSISVNDR table on the County Accounting System VIDELT Y

What will the County Accounting System Do? Notify SSIS of all updates Will allow this vendor to be purged

N

Notify SSIS of all updates Will NOT allow this vendor to be purged Table 3-2 County Accounting System response, SSISVNDR table

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 23 of 37

SECTION FOUR: ERROR HANDLING

4.0 Introduction This section describes requirements for detecting and handling exceptions and errors for the County accounting system interface software.

4.1 Data Errors Data errors will be detected by the service that imports data. Errors detected by the import process will be posted to the import log for further follow-up. The following types of errors may occur during vendor information import. Error message

Explanation

Severity (Error / Warning)

Vendor record in SSIS is not found when attempting an update.

The County accounting system has requested an update, so the record should exist in SSIS. However, the record was not found in SSIS based on the county vendor number.

Error

Vendor record in SSIS already exists when attempting an add.

The County Accounting System has requested an add, so the record should not exist in SSIS. However, the record was found in SSIS based on the county vendor number.

Error

Payee vendor number is invalid.

A payee vendor number is specified, but that vendor number is not in the SSIS Bus Org table.

Error

Imported Vendor number is blank – required field.

Vendor number cannot be blank.

Error

Imported Vendor name is blank – required field.

Vendor name cannot be blank.

Error

Imported Status code is not ‘A’, ‘I’, ‘C’ or ‘D’.

Occurs when imported status code is not ‘A’-active, ‘I’-inactive, ‘C’-active social services vendor, or ‘D’-marked for deletion.

Error

Imported Action code is not ‘A’, ‘U’ ‘P’ or ‘X’.

Occurs when imported Action code is not ‘A’-Add, ‘U’-Update, ‘P’-Purge, or ‘X’-Initial load.

Error

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 24 of 37

Error message

Explanation

Severity (Error / Warning)

Cannot purge this Vendor, detail records exist.

Occurs when an attempt is made to remove a Bus Org from SSIS but there are still related records in SSIS. A Vendor cannot be purged from SSIS if they have work groups, placements, payments or cases attached.

Error

Imported vendor number length is greater than 20 characters.

Vendor number is greater than the SSIS defined length.

Error

Imported vendor name length is greater than 50 characters.

Vendor name is greater than the SSIS defined length.

Error

Imported Address 1 length is greater than 100 characters.

Address 1 is greater than the SSIS defined length.

Error

Imported Address 2 length is greater than 100 characters.

Address 2 is greater than the SSIS defined length.

Error

Imported Address 3 length is greater than 100 characters.

Address 3 is greater than the SSIS defined length.

Error

Imported Address 1 is blank. Required field.

Address 1 is a required field.

Error

Imported City length is greater than 25 characters.

City is greater than the SSIS defined length.

Error

Imported State code is invalid.

State code is not a valid code.

Error

Imported Zip code length is greater than 9 characters.

Zip code is greater than the SSIS defined length.

Error

Imported Zip code (1-5) is blank. Required field.

Zip code (1-5) is a required field.

Error

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 25 of 37

Error message

Explanation

Severity (Error / Warning)

Imported Fed ID/SSN length is greater than 9 characters.

Fed ID is greater than the SSIS defined length.

Error

Imported Phone Number length is greater than 50 characters.

Phone number is greater than the SSIS defined length.

Error

Imported Fax Number length is greater than 50 characters.

Fax number is greater than the SSIS defined length.

Error

Imported Email Address length is greater than 100 characters.

Email address is greater than the SSIS defined length.

Error

Imported Payee Vendor Number length is greater than 20 characters.

Payee Vendor Number is greater than the SSIS defined length.

Error

Imported MMIS MA Provider ID length is greater than 10 characters.

MMIS MA Provider ID is greater than the SSIS defined length.

Error

Imported 1099 Vendor flag is invalid.

1099 Vendor flag is not ‘Y’, ‘N’ or blank.

Error

Imported National Provider Identifier length is greater than 10 characters.

National Provider Identifier is greater than the SSIS defined length.

Error

Imported county code is invalid

County code is not a valid code

Error

This Bus Org has been inactivated, but open service arrangements exist.

Payments can not be made on the service arrangements if the Bus Org is inactive.

Warning

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 26 of 37

Error message

Explanation

Severity (Error / Warning)

Payee ID has been changed, but open service arrangements exist.

A warning to the user to make sure they will pay the correct payee.

Warning

Attempting to add an inactive vendor.

A vendor must be active before it can be imported.

Warning

Imported Description length is greater than 200 characters.

Description is greater than the SSIS defined length.

Error

Table 4-1. Bus Org import errors. In addition to the import log there is also a synchronization log that contains an entry for each synch attempt. Each record notes the run date and time, the number of vendors the process attempted to import the number successfully imported and the number not imported. The name of this file is SyncLog.txt. It resides in the same directory as the executables. A second log contains information about errors that keep the import process from running. This log contains an entry for each import attempt that failed to run. Each record notes the attempted run date and time, and diagnostic information about the error. The name of this file is SyncError.err. It also resides in the same directory as the executables.

4.2 System Errors All system errors will be trapped by PL/SQL exception handling and will be logged to a table for manual follow-up. An operator’s guide produced by SSIS will contain instructions for resolving common system errors. Refer to Appendix A3, Design decision #7 for specifics.

4.3 Network Errors Vendor data is accessed using an Oracle heterogeneous connection and ODBC over TCP/IP. Any errors in connecting to the County Accounting System will be logged to an error table and resolved manually by county staff. An operator’s guide produced by SSIS will contain instructions for checking the condition of the connection to the County Accounting System. Refer to Appendix A3, Design decision #7 for specifics.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 27 of 37

SECTION FIVE: SUPPORTABILITY AND USABILITY

5.0 Introduction This Section describes the supportability and usability requirements for Bus Org import software.

5.1 Supportability Supportability includes both support of software during maintenance and enhancement, as well as support of executable code during operation. Supportability requirements address testability, maintainability, and other qualities required to keep the system current once operational.

5.1.1

Software Maintenance

Supportability/usability requirement 1: Pascal and SQL source must be commented with header blocks and descriptive comments where code functionality is not obvious. Supportability/usability requirement 2: Source code header blocks must reference associated software specification documents.

5.1.2

System Operation

This software is executed by a service on the application server, and is not run manually. The service automatically starts when the server is booted. It can also be manually started if needed.

5.2 Usability Usability includes setup and operation as well as security, reliability and impact on current systems. Usability requirements address ease of use and other qualities related to secure, efficient operations.

5.2.1

Security

The security for this software is controlled by the county vendor import preferences screen shown in Figure 3-1 and 3-2. The preferences screen is located in the Admin module and is only available to users with the required role. This screen is used during setup when the database tables are identified and the user name and password is supplied. The password is encrypted for storage in SSIS.

5.2.2

Interfaces

This software interfaces to the County Accounting System through ODBC connections to two files, a vendor import file and a ‘vendors in SSIS’ file. The system most commonly interfaced to is the AS/400, although the import process can access any file that is

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 28 of 37

supported by an ODBC connection as long as it matches the schema specified in this document. The interface files are identified to the system in the vendor import preferences screen of Figure 3-1.

5.2.3

Performance

The volume of data transferred by the system during normal use is low. Vendor data is only placed in the interface file for import when a social services vendor has been added or changed. The maximum length of each vendor record is approximately 650 bytes. Some of the smaller counties may enter or change two to three vendors a month. Even if a county updated 100 vendors a day, the data transferred would be about 65K. Since the import process begins with manual maintenance of the vendor file in the County Accounting System, vendors are added to the import file at a very low rate. Depending on the number of minutes to wait before checking the import file, the import process should only process a few records at a time.

5.2.4

Error notification

When errors are encountered on import, the import process will notify the user identified in the ‘User to receive alerts’ field on the vendor import preferences screen of Figure 3-1. Regardless of notification, any user may check the import error log to determine the exact nature of an import error for each vendor processed.

5.2.5

Scheduled operations

This software is executed by a service on the application server, and not run manually. The county has the ability to set how often the service will run on the vendor import preferences screen of Figure 3-1. This is done by selecting the number of minutes to wait before checking for vendor data to import.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 29 of 37

APPENDIX A: ISSUES AND DESIGN DECISIONS A.1 Introduction This section describes issues and decisions about design and implementation of Bus Org vendor import software.

A.2 Issues 1. Some counties have expressed concern with having the import process resident on the database server. They would like it on the application server for performance reasons. Keep them in the loop on what we decide. 62 2. Is there a need to limit the use of the initial load function to before import begins? Initial load is more of a refresh, might not be an issue.

A.3 Design Decisions 1. The County Accounting System will code vendor data to be imported to SSIS with an action code indicating what SSIS should do with the record. 2. If a county elects to use a SOAP client to transfer the vendor information to SSIS, they will be responsible for ensuring the integrity of the imported data, including relational integrity among the tables. For example, they may add, update or delete address, phone number, fax number, email address or payee ID. They will then be responsible for adding, updating or removing the corresponding entries in the cross reference files. The following tables in SSIS are updated as a normal part of the import process: • • • • • • • • •

BUS_ORG BUS_ORG_XREF PHONE BUS_ORG_PHONE_XREF ADDRESS BUS_ORG_ADDR_XREF EMAIL BUS_ORG_EMAIL_XREF IMPORT ERROR LOG

3. We will create a unique identifier for the “Last Changed By” field that will identify records created in the vendor import process. 4. We will add a field for national provider ID to the import tables and to BUS_ORG. This field won’t be used immediately but will probably be needed in the next year. 5. Version 3.8 will not have a feature to allow the user to manually execute a check for imported vendor data. This is a wish list item. 6. During their purge process, the County Accounting System will check the VIDELT flag in SSISVNDR for each vendor to be purged. If the vendor number is found in the SSISVNDR file, the VIDELT flag must be set to ‘Y’ for the vendor to be deleted from the County Accounting System. If the vendor number is not found in the SSISVNDR file, that vendor is not in SSIS.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 30 of 37

7. System and network errors will be addressed by an operators guide. This guide is intended for the use of technical personnel in the counties and for support staff within SSIS (WT’s group and the help desk). This document should include: • • • •

Import process overview / theory of operation How to determine if the connection is experiencing problems Common causes of connection problems / Actions to resolve them Who to contact with questions

8. Instructions for the initial setup of the connection between the County Accounting System and SSIS will be contained in an implementation document. Refer to the document SSIS Implementation Memo Number 22, dated August 17, 1999 for document form and ideas. This document must address: • •

• • •

Instructions for setting up the County Accounting System/SSIS connection for vendor import Specific information needed to set up the connection Host name/IP address, database type, database identity (name, number, etc.), both import and ‘vendors in SSIS’ table names, user ID name, password Vendor import testing checklist Who to contact with questions How to use the import log to resolve errors

9. Add columns to the vendor import file for Unique record ID, Process date, Process time, and Status of processing. This will allow us to retain the records in the import file for one year as an aid to troubleshooting. Records older than one year will be deleted in the normal course of import processing. 10. The import process will perform integrity checks before importing vendor data. The objective of these checks is to ensure that no fatal errors exist that could result in data loss. The errors that are checked for are: •

Duplicated county vendor number within the SSIS Bus Org table.

•

Invalid county vendor number within the SSIS Bus Org table (not left justified numeric value)

•

The payee of a vendor does not themselves have a county vendor number within the SSIS Bus Org table

•

A county vendor number used in SSIS does not exist in the County Accounting System (for initial load only).

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 31 of 37

APPENDIX B: Interface processing / flow diagrams

County Accounting System VIA_CD code of 'A' or 'U' Yes Yes County Accounting System User Interface

Vendor marked to go to ssis?

Save

No

In SSIS vendor list?

No

Purge Process Doesn't Send To SSIS No

Is the vendor # in SSIS Vendor list and is allow delete (VIDELT) = 'Y'

Yes

VIA_CD code of 'P'

SSIS Vendors Table with AlllowDelete Boolean

File name - SSISVNDR

File name - VNIMPORT

Vendor Import Table

SSIS Application/Database Server SSIS will add/update/delete as necessary Vendor Sync Utility Running as a service at a user defined interval

SSIS Database

Add / Update / Delete successful?

Post successful update message to log

Update / Delete the interface record

Post unsuccessful update message to log

Update / Delete the interface record

File:Vendor Sync Process

Figure B-1. Flow diagram with standard action codes. (A, U, P)

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 32 of 37

County Accounting System County Accounting System User Interface

VIA_CD code of 'X'

Initial load extract / load process

SSIS Vendors Table with AlllowDelete Boolean

File name - SSISVNDR

File name - VNIMPORT

IFS to SSIS Vendor Import Table

SSIS Application/Database Server SSIS will add/update/delete as necessary Vendor Sync Utility Running as a service at a user defined interval

SSIS Database

Add / Update successful?

Post successful update message to log

Update / Delete the interface record

Post unsuccessful update message to log

Update / Delete the interface record

File:Vendor Sync Process

Figure B-2. Flow diagram for initial load. (X)

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 33 of 37

APPENDIX C: Initial load During the initial load, the county accounting system will need to supply the complete file of social service providers. This file will be processed against SSIS’ Business Organization table to set the baseline against which all future adds and changes will be made. ‘Initial load’ refers to the first import of all the social services vendors into SSIS. The VIA_CD code used to perform an initial load is ‘X’. When the import service processes vendor information that has an X VIA_CD code it checks to see if it can find that vendor’s data in the tables in SSIS. If so, it updates the data in the SSIS tables with what was imported. If not, it adds the data to the SSIS tables for that vendor. When doing the initial load, the vendor import file must contain a record for each county vendor number that is present in the SSIS BUS_ORG table. The import process will confirm that every county vendor number present in SSIS is also present in the import file. If this is not the case, the import process will issue an error message and end. If the import file for the initial load is missing a vendor number that is present in SSIS, there is a chance that the missing number could be assigned to a new vendor at some point in the future. This could result in unrelated vendor data being imported and overwriting the vendor data in SSIS. The same record layout is used for initial load as for the ongoing data import.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 34 of 37

APPENDIX D: Import log An example of the import log is shown below and is based on the information in the data transfer scenario of section 2.3. All of the imported data is shown in the log as well as the processing date and time, the results of processing, and any errors detected by the import process. The import log contains one record for each record in the vendor import file. The user may sort and filter by any of the columns in the log, and the columns are resizable. The log is shown here in two sections for space reasons. Unique ID

Vendor #

Name

Addr 1

000000 0001

123456 7890

Kalduns ki’s house of Kids

7058 Constit ution Avenue

Addr 2

Status

Payee #

MMIS MA ID

Default 1099 code

A

1234567 855

7654321

N

Addr 3

NPI

City

State

Zip

Fed TID

Phone

Fax

Email

Oakdal e

MN

551280 000

417708 571

651772 3769

651772 3768

sendme anote@ myemai l.com

Vendor County Code

Date / Time processed

Results

Action code

23

2004-0910

F

U

10:22:15

Table D-1. Import log example.

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 35 of 37

Description (custom counties)

Errors

Imported vendor name length is greater than 50 characters

RELATED DOCUMENTS The magnetic media version of this specification is stored in SourceSafe at M:\Design Team\SourceSafe\SSIS Fiscal\ The following documents are related to this specification. Advanced Planning Document -- Appendix A, Minnesota SACWIS Functional Requirements SSIS Fiscal System Software Specification Bus Org Software Specification SACWIS review

DI Vendor Import

Last Updated: 03/03/05

Data Interchange Vendor Import

Page 36 of 37

GLOSSARY Term Bus Org IFS Payee SACWIS SOAP

SSIS Vendor

DI Vendor Import

Last Updated: 03/03/05

Definition Business Organization – An entity that does business with the county. Bus Orgs can be paid vendors as well as work, school, or organizations for professional collaterals. Integrated Financial System – an accounting system package developed by TriMin Systems, Inc. An entity who receives payment. This may or may not be the entity who supplies the goods or services. Statewide Automated Child Welfare Information System Simple Object Access Protocol (SOAP) is an XML based protocol for exchanging information over the Internet. It is a tool for building web services and performing distributed computations. Social Services Information System. An entity that does business with the county and receives payment as a normal consequence of business. A vendor may supply goods or services to the county or to others on the county’s behalf. Some vendors are imported into SSIS and become Business Organizations.

Data Interchange Vendor Import

Page 37 of 37