SUNY Tran Process and Functional Description

SUNY Tran Process and Functional Description

Project Title / SUNY Tran
Business Unit / Program Area (Customer): / Facilities Services
Prepared by (author): / Barbara Skoblick
Key Customer Contact, Phone #: / Jane VanDerzee, 5-7988
Business Analyst Contact, Phone #: / Barbara Skoblick 4-1340
Document Revision Log with Change Dates: / 01/18/2011BS Production version 02/10/15 jcc

Table of Contents

Process for Maintaining this Document

Annual SUNY Tran and Fiscal Year Rollover Flow Chart

Running the SUNY Tran Process

1.Load Data into SUNY “translator” database

2.Correct Errors

2A. Create Snap Shot (optional)

3.Download the CU File

4.Turn off the nightly snap shot

5.Create SUNY Tran data file

6.FTP SUNY Tran data file to SUNY

SUNY Tran Data File Layout and Field Descriptions

Data Selection Criteria

Exceptions and Special Processing

Assigning SPACE-TYPE and SUNY-ROOM-NAME

Creating SUNY-ACCOUNT

Prorate Space and Prorate Function Calculations

SUNY Tables

SUNY_SPACE_TYPES

SUNY_ACCOUNTS

SUNY_PARKING_LOTS

FUNCTION

ROOM_TYPES

Manage SUNY Tables

SUNY_SPACE_TYPES

SUNY_ACCOUNTS

SUNY_PARKING_LOTS

Viewing SUNY Tran Data

APPENDIX A: FUNCTION Table

APPENDIX B: ROOM_TYPES Table

APPENDIX C: SUNY_PARKING_LOTS Table

APPENDIX D: SUNY_SPACE_TYPES Table

APPENDIX E: SUNY_ACCOUNTS Table

APPENDIX F: SUNY Percent Calculation3

APPENDIX G: Snapshot Calculations for SUNY Tran4

APPENDIX H: SUNY AccountsRoom Type Exceptions...... 65

APPENDIX I: Cornell University EN Classification6

Process for Maintaining this Document

Changes to this document will typically occur when:

• SUNY changes something and notifies FI of the change
• FI requests a change to the process and CIT needs to make a coding change (either ColdFusion and/or PL/SQL)
• FI needs to change a data element (one that they are able to maintain)
• the university implements or changes a system that impacts FI and/or SUNY Tran (for example, new accounting system) and CIT performs remediation

For non-technical changes, FI staff will be responsible for updating this document to reflect the change (for example, data element change; some SUNY requested change) and notifying CIT production support staff ( ) of the change as FYI.

For technical changes, FI will forward this document to the responsible Tech Staff (as part of the production support process ). Tech staff will review, verify and update the doc as needed to reflect the change. They will send the document back to FI who "sign-off to production" by validating that this document has been updated appropriately.

All changes will be recorded in the document change log and will indicate when/who/what information for the change.

Document will be posted at: where the documentation for the Facilities Inventory System is maintained.

Annual SUNY Tran and Fiscal Year Rollover Flow Chart

Numbers correspond to steps in the section itemized below - Running the SUNY Tran Process

Running the SUNY Tran Process

The SUNY Tran process consists of the followingsteps that can be run from the Facilities Inventory Admin page as needed. (Items in red italics below, and in additional detail for items C and D at the end of the process are included here for purposes of completing the documentation for the functions found on the Administration page, but are not technically part of the SUNY Tran process.)

Before SUNY Tran is run, steps A and B (below)must be completedand the data approved by the Cost Accounting Office auditors.

Set ROOM_DEPT status as appropriate

A‘Set Hold/Freeze to Deep Freeze’ button

B‘Set Pending Obsolete rooms to Obsolete’ button

1. Load data into SUNY ‘translator’ database using the most current snap shot tables
2. Correct the errors generated by the database load, and save an Excel copy of the resulting warnings file after the errors have been cleared.

2ARun another snapshot to capture the corrected errors.

1. Download the Cornell data in the database and store as the data of record in local Inventory directory.
2. Freeze the system, stopping the nightly snap shot process and repeat step 1 to reload the SUNY database
3. Create SUNY Tran data file
4. FTP data file to SUNY (this is done in FileZilla)

The final steps to the yearend process include:

C Rolling the system over to the next fiscal year

D Turning the nightly snap shot back on.

Screen shot of the administration page where these functions are managed:

1.Load Data into SUNY “translator” database

Prior to running this step:

• Wait until nightly snap shot is created to pick up changes or create snap shot manually (see Step 2A)

Click on the ‘Load SUNY Tran DB’ button. The load process pulls data from the snap shot tables and populates the SUNY ‘translator’ database.

This step can be run as many times as needed until all data errors have been corrected. Corrections will not be included in the snap shot tables until after the nightly process runs(see section below for how to correct errors). However, there is an option to generate new snap shot tables right away (see Step2A).

If the load completes successfully, a control report will be emailed to . The report will contain:

• title: ‘SUNY Tran DB Load’
• date and time load was run
• count of ‘active’ Facility records read (not all will meet criteria for SUNY owned or SUNY occupied)
• count of active Facility records selected for SUNY Tran (these are facilities that met the criteria for SUNY owned or occupied)
• total number of SUNY rows written (one row for each Facility/Room/Dept /Function)
• count of records with errors (some rooms may have multiple errors, but will only be counted once – however, the details for every error will be viewable via the link)
• link to table to view detailed error descriptions
• count of rooms with warnings (these are rooms where the SUNY proration calculation will be less than 100% due to truncation; cannot be fixed by CU so a warning is generated

Sample email message:

From: SUNY Tran DB Load

Sent: Friday, August 27, 2010 9:13 AM

To: Jane B Van Derzee

Subject: SUNY Tran DB Load

The SUNY Tran DB load was run on 08/27/2010.

Fiscal Year 2009.

1904 active facility records were read.

25256 SUNY records were selected.

1824 errors were found.

(link:

490 room warnings were found.

If the process fails, an error will be generated and the details sent via email to . Facilities will email CIT Custom Apps at for assistance if needed. Do not rerun the process until notified that the error has been fixed. Do NOT run Step 5 or 6 until Step 1 has run successfully and all errors have been corrected.

2.Correct Errors

Errors Generated by Snap Shot

If the snap shot completes successfully, review snap shot data errors using either the link in the e-mail message or the link below.

Review the resulting report and make appropriate corrections in the FI system; rerun the Snap Shot if corrections are made:

Errors and Warnings Generated by SUNY Translator Process

There are a number of errors and one warning generated by the program. The errors and warning are written to a DB table and can be viewed online by using the link in the control report. For each error or warning, relevant data fields are included. Example of error report:

Make appropriate corrections following the comments in the error message and the table below by:

• highlighting the SUNY table that needs correcting and selecting the Manage SUNY Tables button or
• correcting the data in the FI system

Error / Reason for Error
Function Code is Null or Blank / There must be at least one function for a department.
Invalid SUNY Account - xxxxxxxxxx / The SUNY-ACCOUNT was not found in the SUNY_ACCOUNTS table, where xxxxxxxxxx is the account not found. Add the missing SUNY Account to the SUNY_ACCOUNTS table. See Manage SUNY Tables.
Invalid SUNY Room Type / The SUNY room type had an invalid value of ‘XXXX’.
The SUNY room type had an invalid value of less than ‘1001’ or greater than ‘9999’.
Correct the SUNY_ROOM_TYPE in the ROOM_TYPES table.
Missing Lot Type / LOT_TYPE is missing in the FACILITY_PARKING_LOTS_SS table. Enter a LOT_TYPE in the FACILITY_PARKING_LOTS table.
Missing SUNY Function Code / The CU FUNCTION_CD or SUNY_FUNCTION_CD needed for SUNY-ACCOUNT creation was not found in the ROOM_DEPT_FUNCTION_SS table. Update the FUNCTION table with the missing data.
Missing SUNY Room Type / The SUNY_ROOM_TYPE was null or blank in the ROOM_SS table. Enter a SUNY_ROOM_TYPE in the ROOM_TYPES table. Send e-mail to SUNY requesting appropriate room type for corresponding CU room type(s), have Facilities Services programmer add the codes to the ROOM_TYPES table.
Parking Lot – xxx Net Area Greater than 9999999 - nnnnnnnnnn / The LOT_AREA_SQ_FT for parking lot xxx was nnnnnnnnnn. The SUNY parking sq footage field can only accommodate 7 digits.
Room Area Greater Than 99999 / The SUNY net area field can accommodate only 5 digits and the net area was greater than 99999.
Room Type/Function not in SUNY_SPACE_TYPES Table - xxxxx / The concatenated value of ROOM_TYPE and FUNCTION_CD was not found in the SUNY_SPACE_TYPES table, where xxxxx is the value not found in the table. Update the SUNY_SPACE_TYPES table. See Manage SUNY Tables.
Too many Room/Dept/Function Combinations / SUNY can only accept up to 9 room/department/function combinations. There were more than 9 combinations.
Warning / Reason for Warning
Truncation May Cause SUNY Percent Calculation to Differ / When SUNY performs this calculation:
SUM(SUNY_DEPT_PERCENT * SUNY_FUNCTION_PERCENT)
they truncate the percent values to 2 digits. This sometimes results in a number less than 1 (100%) which will not match the Cornell calculated value. See Appendix F for details on performing this calculation.

Run the snap shot again (see step 2A). Repeat the process until satisfied that the remaining errors are appropriate.

Export the final search results to Excel for the record.

If you made changes to the database or the SUNY tables to correct errors you MUST create a new snap shot (or wait for the overnight version) AND reload the SUNY Tran DB (step 1).

This is very important. The corrections you made will NOT be included in the data unless you do both of these steps.

2A. Create Snap Shot (optional)

This step can be run if an updated snap shot is needed before the next day. Once it has completed, Step 1 can be rerun.

Click on ‘Create Snap Shot’ button.

An email will be sent to when the load starts:

From: Cornell Custom Apps - SS LOAD [mailto:

Sent: Tuesday, August 31, 2010 12:01 AM

To: Jane B Van Derzee

Subject: Facility Snapshot TEST Load Started - FULL

Facility Snapshot TEST Load Started – FULL

A second email is sent when the load completes:

From: Cornell Custom Apps - SS LOAD [mailto:

Sent: Tuesday, August 31, 2010 12:04 AM

To: Jane B Van Derzee

Subject: Facility Snapshot TEST Load Completed Successfully - FULL

Facility Snapshot TEST Load Completed Successfully – FULL

If the process fails, Facilities will email CIT Custom Apps at for assistance.

Sample error message:

From: Cornell Custom Apps - SS LOAD [mailto:
Sent: Thursday, September 23, 2010 1:16 PM
To: Facilities Inventory
Subject: Facility Snapshot PROD Load Failed - FULL

ORA-08103: object no longer exists

3.Download the CU File

Select the ‘Download CU File’ button to create a copy of the data of record used to generate the extract of data being sent to SUNY.

This data is maintained off line as a backup copy.

4.Turn off the nightly snap shot

Select the ‘Turn Off Snap Shot’ button. This will turn off the nightly snap shot and not allow you to create a new snap shot inadvertenly.

This button is a toggle. Select the same button to turn the snap shots back on.

5.Create SUNY Tran data file

This step can be run as many times as needed after all data errors have been corrected and Step 1 has run successfully. This step will NOT check for outstanding errors from Step 1, so results may not be valid if errors are not corrected.

Click on the ‘Create SUNY Tran File’ button.

If the process completes successfully, a control report will be emailed to . The report will contain:

• title: ‘SUNY Tran Data File Build’
• count of records written to the data file (this will be a total of card 1 and card 2 records)
• link to download the data file

Sample email:

From: SUNY Tran Data File Build
Sent: Monday, September 13, 2010 3:01 PM
To: Jane B Van Derzee
Subject: SUNY Tran Data File Build
SUNY Tran Data File was just built.
A total of 25249 records were written.
You can get the file here:

The data file will be created on the CIT server. It can be downloaded by Jane McLarty or Jane Van Derzee. This is the copy of record as sent to SUNY. Click on the link to download the file.

If the process fails, an error will be generated and the details sent via email to . Facilities will email CIT Custom Apps at for assistance if needed. Do not rerun the process until notified that the error has been fixed. Do NOT run Step 4 until Step 3 has run successfully.

6.FTP SUNY Tran data file to SUNY

Before attempting to run the FTP step, be sure the password has not expired. SUNY requires that passwords change every 90 days. A username and current password are needed for the FTP.

Refer to the 6/6/10 e-mail .pdf attachment from SUNY for complete FTP instructions. (This is a document internal to Facilities Inventory.)

Use Filezilla to FTP the file to SUNY over a secure connection (FTPS). File is loaded into the PSI directory, and e-mail sent to SUNY to notify them that the data was delivered.

C. Roll the system over to the next fiscal year:

At the end of the SUNY Tran process select the ‘Roll the system over to FYxx – this is VERY SCARY’ button. This action performs multiple functions (hence the “very scary” label and the multiple opportunities for you to change your mind and re-check the process).

D. Turn the nightly snap shot back on:

Repeat step 4 (select the ‘Turn On Snapshot’ button) to toggle on the nightly snap shots to capture data for the new fiscal year.

SUNY Tran Data File Layout and Field Descriptions

The SUNY Tran data file has fixed format records. There are two records (tran-type 1 and tran-type 2) created for every facility room/dept/function combination or parking lot. Each record is 80 alpha-numeric characters in length.

SUNY Field Name / Data Format/Length
A=alpha
N=numeric / Start Pos / End Pos / Fixed values / Oracle fields for facilities (from snapshot tables) / Oracle fields for parking lots (from snapshot tables)
First record/card
TRAN-CODE / A1 / 1 / 1 / 'A'
CAMPUS-CODE / A4 / 2 / 5 / ‘2843’
FACILITY-CODE / A6 / 6 / 11 / FACIL_CD in FACILITY_SS table / FACIL_CD in FACILITY_PARKING_LOTS_SS table
BLDG-ABBR-CHANGE / A6 / 12 / 17 / blank
ROOM-NO / A6 / 18 / 23 / ROOM in ROOM_SS table (if less than 6 characters, left justify and add blanks; if 3 characters add leading zero) / value always 'PRKLT' concatenated with lot number (e.g. PRKLT1, PRKLT2, etc)
ROOM-NO-CHANGE / A6 / 24 / 29 / blank
ROOM-REC-LINE-NUMBER / N1 / 30 / 30 / record number for this record - based on how many dept/function combinations exist - SUNY only takes 9 / always '1' for parking lots
STATUS-CODE / A1 / 31 / 31 / Based on ROOM_TYPE value in ROOM_SS: when ROOM_TYPE = 050 STATUS_CODE = U (unusable); when ROOM_TYPE = 060 STATUS_CODE = A (alteration); when ROOM_TYPE = 081 STATUS_CODE = N (inactive/unassigned); otherwise STATUS_CODE = C (current use) / always 'C' for parking lots
NUM-STATIONS / N4 / 32 / 35 / STATION_QTY in ROOM_SS
(right justified with leading zeroes) / NUM_SPACES in FACILITY_PARKING_LOTS_SS table(right justified with leading zeroes)
NET-SQ-FT / N5 / 36 / 40 / NET_AREA in ROOM_SS table (right justified with leading zeroes) / always '00000' for parking lots
SPACE-TYPE / A4 / 41 / 44 / complex criteria using ROOM_TYPE (ROOM_SS table), SUNY_ROOM_TYPE (ROOM_SS table), FUNCTION_CD (ROOM_DEPT_FUNCTION_SS table) and table lookup (SUNY_SPACE_TYPES) - see Assigning SPACE-TYPE and SUNY-ROOM-NAME for details / LOT_TYPE in FACILITY_PARKING_LOTS_SS table
USER-CODE / A4 / 45 / 48 / '0000'
SUNY-ROOM-NAME / A17 / 49 / 65 / use TYPE_ABBREV in ROOM_SS table for descriptive name; - see Assigning SPACE-TYPE and SUNY-ROOM-NAME for details / set to 'SURFACE PARK'
SUNY-ACCOUNT / A10 / 66 / 75 / build account string using SUNY_FUNCTION_CD in ROOM_DEPT_FUNCTION_SS table and DEPT_CD in ROOM_DEPT_SS; lookup in SUNY_ACCOUNTS table - see Creating SUNY-ACCOUNT for details / use SUNY_PARKING_CODE in FACILITY_PARKING_LOTS_SS table to lookup SUNY account in SUNY_PARKING_LOTS table
PRORATE-SPACE / A2 / 76 / 77 / SUNY_DEPT_PERCENT in ROOM_DEPT_SS table; when 100%, PRORATE-SPACE is set to ‘**’ / for parking lots, always '**'
PRORATE-FUNCTION / A2 / 78 / 79 / SUNY_FUNCTION_PERCENT in ROOM_DEPT_FUNCTION_SS table; when 100%, PRORATE_FUNCTION is set to ‘**’ / for parking lots, always '**'
TRAN-TYPE / A1 / 80 / 80 / '1'
Second record/card
TRAN-CODE / A1 / 1 / 1 / 'A'
CAMPUS-CODE / A4 / 2 / 5 / ‘2843’
FACILITY-CODE / A6 / 6 / 11 / FACIL_CD
BLDG-ABBR-CHANGE / A6 / 12 / 17 / blank
ROOM-NO / A6 / 18 / 23 / ROOM in ROOM_SS table(if less than 6 characters, left justify and add blanks; if 3 characters add leading zero) / value always 'PRKLT' concatenated with lot number (e.g. PRKLT1, PRKLT2, etc)
ROOM-NO-CHANGE / A6 / 24 / 29 / blank
ROOM-REC-LINE-NUMBER / N1 / 30 / 30 / record number for this record - based on how many dept/function combinations exist - SUNY only takes 9 / always '1' for parking lots
FIXED-SEATS / A1 / 31 / 31 / always '0' / always '0' for parking lots
AIR-CONDITIONING / A1 / 32 / 32 / blank
CLOSED-CIRCUIT-TV / A1 / 33 / 33 / '0'
CONDUIT / A1 / 34 / 34 / '0'
LIGHT-CONTROL / A1 / 35 / 35 / '0'
ACOUSTICAL-TREATMENT / A1 / 36 / 36 / '0'
LAB-SERVICE / A1 / 37 / 37 / ‘0’
NUMBER-PHONES / N2 / 38 / 39 / '00'
ROOM-HEIGHT / N2.2 / 40 / 43 / '0000'
BYPASS-EDIT-FLAG-1 / A1 / 44 / 44 / Y or blank - used to indicate room total area is less than 100% / always blank for parking lots
RESERVED / A4 / 45 / 48 / blank
COMPUTER-EQUIPMENT / A1 / 49 / 49 / blank
HOSPITAL-ISOLATION-ROOMS / A1 / 50 / 50 / if ROOM_TYPE (in ROOM_SS table) = 855 then HOSPITAL-ISOLATION-ROOMS = Y; otherwise blank / always blank for parking lots
INCUBATOR-LABS / A1 / 51 / 51 / if ROOM_TYPE (in ROOM_SS table) = 270 then INCUBATOR-LABS = Y; otherwise blank / always blank for parking lots
RADIATION-EQUIPMENT / A1 / 52 / 52 / blank
ICE-RINKS / N1 / 53 / 53 / if ROOM_TYPE (in ROOM_SS table) = 526 then ICE-RINKS = 1; otherwise 0 / always 0 for parking lots
ENVIROMENTAL-CHAMBERS / A2 / 54 / 55 / blank
SWIMMING-POOLS / N1 / 56 / 56 / if ROOM_TYPE (in ROOM_SS table) = 528 then SWIMMING-POOLS = 1; otherwise 0 / always 0 for parking lots
WALK-IN-COOLER-FREEZERS / A2 / 57 / 58 / blank
PARKING-LIGHTED / A1 / 59 / 59 / always blank if not parking lot / if HAS_LIGHTING (in FACILITY_PARKING_LOTS_SS table) = '1' set to 'Y', otherwise blank
PARKING-SQ-FOOTAGE / N7 / 60 / 66 / always ‘0000000’ if not parking lot / LOT_AREA_SQ_FT in FACILITY_PARKING_LOTS_SS table (right justified with leading zeroes)
AIR-CONDITIONED-SPACE / A1 / 67 / 67 / blank
BLANK / A12 / 68 / 79 / blank
TRAN-TYPE / A1 / 80 / 80 / '2'

Data Selection Criteria

The program selects data from the snapshot tables using the following criteria: (note that consistent business practices in setting status flags are necessary for selection process to work correctly)