july 22, 2009 sunflower project monthly conversion meeting – july

July 22, 2009

Sunflower Project Monthly Conversion Meeting – July

2

Welcome & Introductions

Fred Barnes – Data Conversion and Interfaces Lead, State

Jenny Brately – Data Conversion Coordinator, Accenture

Dean Arzoian – SMART Technical Team Manager, State

Bruce Myers – SMART Technical Team Manager, Accenture

3

Meeting Objectives

• Provide information about the SMART conversion process.

• Explain agency conversion tasks.

• Communicate updates on project decisions related to conversion.

• Address agency questions.

4

Today’s Topics

• Converting into SMART

• Data Conversion Timeline

• General Conversion Updates

• Conversion File Layout Updates

• Conversion Support

5

What Is Data Conversion?• One-time transition of data from an existing source (e.g., agency

system, Excel spreadsheet maintained by an agency, central system) into SMART.

How Is Data Converted?• The Sunflower Project is using three different conversion methods:

1. Flat File Upload

2. Excel File Upload

3. Online Entry

• The options that are available for a particular type of data conversion vary by SMART module and data type.

Converting into SMART

6

*New decision, based on results of data conversion survey (Task 215).

**TBD, based on results of data conversion survey (Task 215).

SMART Module Conversion Object Available Options

Flat File Upload

Excel File Upload

Online Entry

Accounts Receivable Customers Yes No Yes

Accounts Receivable Open Receivables (a.k.a. – Pending Items)

Yes No Yes

Project Costing Projects Yes No Yes

Asset Management Assets, except for leases* Yes Yes No

Asset Management Leased Assets* No No Yes

Purchasing Open Purchasing Contracts not in Procurement Manager Plus

TBD** No Yes

Grants/Contracts Grants/Contracts No No Yes

Converting into SMARTAgency Conversions

7

Flat File Upload

How does this work?

• Existing agency system produces a flat file (text file) containing the data.

• Flat file must be in the required format, as outlined in the SMART file layout.

• Agencies will load the file to a secure location on the DISC mainframe for processing by SMART.

• Agencies will need to modify their existing system in order to transform the data and export it into a flat file.

What do I need to be doing?

• The data mapping task, which was due 7/16, is applicable for flat file upload conversions (Task ID 37).

• Agencies will need to modify their system in order to transform/export the data into the required flat file format.

• Agencies will be required to participate in conversion connectivity testing in August.

• Agencies will be required to participate in iterative conversion testing cycles. The first test file is due 9/18 (Task ID 23).


8

Excel File Upload

How does this work?

• Agency data is put into an Excel spreadsheet.

• The data entry template spreadsheet provided by the Sunflower Project needs to be used. Columns can NOT be added, removed, or moved around in the spreadsheet.

• Agencies will email the file to SMART for processing.

How will I create my Excel spreadsheet? This will differ depending on your current data source!

• If you have an existing “system” from which you are going to generate an Excel spreadsheet, you may need to modify that system so that it transforms/exports the data into the correct format. This could be the case if you currently track your assets in an Access database.

• If you currently track your assets in an Excel spreadsheet, you would need to transfer the data from that spreadsheet into the SMART data entry template spreadsheet (e.g., by copying/pasting).

• If you currently track your assets on paper, you would type the asset information into the data entry template spreadsheet.


• The data mapping task, which was due 7/16, is applicable for excel file upload conversions (Task ID 37).

• Agencies will be required to participate in iterative conversion testing cycles. The first test file is due 9/18 (Task ID 23).


9

Online Entry

How does this work?

• Agency enters the data directly online in SMART.

• The data mapping task (Task 37) is not applicable for this conversion method.

• Agency does not need to participate in iterative testing cycles.

• Guidance for data entry will be provided by the Sunflower Project at a later time.


• Identify the agency resources that will perform your data entry.


10

• Interfacing agencies are currently participating in “connectivity testing.”• Agencies that are not interfacing but are converting data into SMART via a

flat file upload will need to participate in a “connectivity test” in August to make sure that they can deposit/retrieve files on the DISC mainframe.

• We will reach out to the applicable agencies in August and ask them to complete this testing.

• The agencies that will be asked to complete this are those that did not complete the interface connectivity testing (the Sunflower Project has record of this) AND that have responded that they are performing flat file upload conversion(s) in the data conversion survey (Task 215).

Converting into SMART Connectivity Testing

11

Data Conversion Timeline

12

General Conversion UpdatesAsset Management

Leased Assets• Leased assets will not be loaded by the flat file upload or Excel

spreadsheet upload.• Agencies must exclude leased assets from the flat file or spreadsheet that

is provided to the Sunflower Project for loading into SMART. • These assets will be entered online by agencies into SMART prior to go-

live. Guidance for data entry will be provided by the Sunflower Project at a later date.

• The reason for this is the low volume of leases that need to be converted into SMART by agencies (as gathered in the data conversion survey, Task 215) relative to the complexity of loading leased data via a flat file upload or Excel spreadsheet upload.

Timing of Asset Conversions• Assets will be converted into SMART prior to go-live. • This applies for all agencies, except Regents institutions.

13

General Conversion Update Purchasing

Encumbrances/Purchase Orders • Outstanding encumbrances will be converted from STARS to SMART by

the Department of Administration.• Affected encumbrances will be converted in one of two ways, determined

by the original document used to record the encumbrance in STARS.• Encumbrances originally recorded using a DA 118 and having no

known vendor will be recorded as GL encumbrances. • All other STARS encumbrances will be converted to purchase orders in

SMART. The STARS encumbrance # will be converted into the PO Ref field on the SMART purchase order.

Purchasing Contracts• SMART Conversion Team is using results of data conversion survey (Task

215) to determine need for an Agency Conversion Workshop for purchasing contracts.

14

General Conversion Update Accounts Receivable

• All State agencies will be loaded as customers for each Business Unit in Accounts Receivable by the Sunflower Project.

• Agencies do not need to include other State agencies in their conversion effort.

15

Conversion File Layout UpdatesGeneral Information

• Conversion file layouts have been updated and posted on the secure SMART website: https://www.da.ks.gov/smart/layouts/technicalresourcedocuments.html

• How will conversion file layout changes be communicated?• Bi-weekly email updates• Monthly Conversion Meetings – Next meetings: 8/26 and 9/23.

16

Conversion File Layout UpdatesDate Format

• The format for date fields has been changed to:MM/DD/YYYY

• The format for date/time stamp fields has been changed to MM/DD/YYYY HH:MM:SS:999999

• This applies to date fields on all conversion file layouts:• Customers Conversion• AR Pending Items Conversion• Projects Conversion• Assets Conversion

17

Conversion File Layout UpdatesAsset Management

• For agencies submitting a flat file, the required file format is now a pipe-delimited flat file (delimiting character: |).

• Tag Number no longer has to be unique within your agency/Business Unit.

• following fields are now optional for the conversion and are not “required”: • Department ID (DEPTID)• Fund (FUND_CODE)• Budget Unit (BUDGET_REF)• Program Code (PROGRAM_CODE)

• Acquisition Description field can be used to track explanatory information about the acquisition of the asset. It can also be used to track the asset's purchase order/voucher number using the following format: V########;PO:XXXXXXXX(where V######## is the voucher number and XXXXXXXX is the purchase order number).

18

Conversion File Layout UpdatesAsset Management – continued

• Detailed Description (DESCR_LONG) was added to the file layout. This field is 254 characters long and can be used to load a comment or additional information about an asset.

• The following fields were removed from the file layout: Color, Weight, Weight Unit of Measure, Height, Height Unit of Measure, Width, Width Unit of Measure, Length, Length Unit of Measure, Capacity, Capacity Unit of Measure, Asset Comments, Agency ID.

• The Short Description field (DESCRSHORT) and the Area ID field (AREA_ID) were changed so that they are not required on the conversion file

• The valid values for the Asset Category field (CATEGORY) were updated.

• The length of the Condition Code field (AM_AST_UD_CHAR1) was changed from 10 to 4 characters.

19

Conversion Support

•Email [email protected] with questions. Include ‘Conversion’ in the Subject line of all emails to us.

• Schedule Office Hours with the SMART Conversion Team. Email [email protected] to schedule an appointment.

•Attend Monthly Conversion Meetings. Next meetings: 8/26 and 9/23.

• Visit secure SMART website to obtain file layouts: http://www.da.ks.gov/smart/ Look for ‘Click to View Documents’ link on the Technical Resources

tab. Don’t have access? Email [email protected].

• Provide feedback on how we’re doing by completing the evaluation sent to you after this meeting.

• Refer to Questions & Answers from this meeting, posted on the SMART website Look for ‘Questions & Answers’ under the ‘Recent updates or additions’ section of the home page.