5300 Project Phase 2: New CU*BASE Database for 5300 Call ...

5300 Project Phase 2:

Project # _______2_4_8_0_3_____

New CU*BASE Database for 5300 Call Report Data

February 10, 2009

Spec Writer: Dawn

Systems Affected:

; CU*BASE

This project specifically addresses Phase 2 of the project plan, "5300 Tools for CU*BASE" that was introduced and published on the CU*Answers Kitchen web page in November, 2008. Specifically, this spec outlines the new database and CU*BASE maintenance tools that form the backbone of a new system to help credit union CEOs complete the quarterly 5300 call report using CU*BASE tools and processes. Goals for this project: ? To store 5300 data in a native CU*BASE database that can be leveraged for analysis and trending ? To allow a CU CEO to view data from a previous report to use as a comparison when filling in the

next report ? To design an underlying database and maintenance program that will allow for easy modifications

without requiring the changes to be timed with a GOLD update (i.e., no screen changes)

This infrastructure must be designed so that we can begin the work outlined in Phase 3 (Integration with Third-Party Tools) and Phase 4 (Automation/Pulling in Data from CU*BASE Files), both of which are outlined in separate specification documents. It also provides the infrastructure so that links to a new context-sensitive online help engine can be established.

Project includes a new database file to store quarterly 5300 data (retention: indefinite at this point), new Call Report Maintenance programs for CU staff to input data and a matching view-only version of the maintenance tool for inquiry purposes, and some sort of report(s) for verification or internal auditing. Also includes a new back-end File Maintenance program to allow CU*Answers to add/change/delete account codes as announced by the NCUA (quarterly updates are anticipated).

Contents

New "Call Report" Database File Structure ............................................................................................................... 2 New File(s) ............................................................................................................................................................ 2 Data Fields on the 5300 Call Report ..................................................................................................................... 2 Data Types ............................................................................................................................................................ 2 More about the "Automation Routine" Concept .................................................................................................... 3 Other Decisions To be Made ................................................................................................................................ 4

(1) Master Control File ............................................................................................................................................... 5 (2) Quarterly Call Report Data File ............................................................................................................................ 6 (3) Auto-Pop Control File........................................................................................................................................... 7 (4) CU Configuration File........................................................................................................................................... 8 CU*BASE Call Report Maintenance/Inquiry (for CU Staff)........................................................................................ 9 Report ...................................................................................................................................................................... 12 File Maintenance Program (for NCUA-required file changes)................................................................................. 13 Menu Changes ........................................................................................................................................................ 15

New CU*BASE Database for 5300 Call Report Data

Project Requirements

1 of 15

New "Call Report" Database File Structure

New File(s)

1. Master Control File - Control file for CU*Answers to maintain that would allow for us to

add/suspend/rearrange fields according to NCUA-published changes each quarter. See Page 5

2. Quarterly Call Report Data File ? Database for the actual quarterly call report data. See Page 6 3. Auto-pop Control File ? Control file maintained by CU*Answers to store information about the

various automation sub-routines. See Page 7 and the separate specs for Phase 4.

4. CU Configuration File - Control file to store how CUs configure which automation sub-routines, if

any, they want to use for each account code (accessed via a special call from the same maintenance program for the call report data itself). See Page 8

Data Fields on the 5300 Call Report

Karen Sorensen has a separate worksheet that shows the nearly 700 fields that can be filled in on the NCUA 5300 Call Report.

Although the NCUA has flatly refused to provide us with field specifications (especially field length) we have been able to determine at least the bare essentials such as $ versus text, etc., and these notes are outlined on Karen's worksheet. We have also been in touch with Callahan & Associates to get their insights, since they also receive this data and manipulate it for their Peer-to-Peer Analysis tools. (They apparently couldn't get the NCUA to cough up the field specs, either.)

We asked Scott Patterson at Callahan & Assoc about field lengths: "There is unfortunately no good answer to this question. For us, it varies based on the specific field. You need to look at the fields and decide what kind of data length to make it. According to our developers, in our various databases some are `integers, some are `floats', some are `text', and some are `bit type / boolean'. It varies based on the account code. For example, Total assets is a `float' because it can become greater than $2B (Navy, etc). whereas "Members" is never going to be greater than 2billion so it is set as an `integer'. Header is usually text. A few are "bit type" (Boolean). We do all this for optimization for our various product lines. In your case/need you will probably be safe just setting everything to `float' and/or `integers'."

Data Types

Following is a summary of the types of fields we've encountered in our analysis. These would be helpful mostly to provide on-screen tips for filling in the data, and possibly to allow for conditional formatting by GOLD:

Type Description

Specs

$ Dollar value, like a G/L balance, total of loan account balances, etc.

Whole dollars

# Quantity, like a count or other value (# of loan accts, etc.) Integer

% Rate

000.00

D Date (can find only 4 of these)

MM CCYY (usually displayed in two boxes but with one acct code)

@ Free-form text, such as a name or address

New CU*BASE Database for 5300 Call Report Data

Project Requirements

2 of 15

More about the "Automation Routine" Concept

Files will be structured so that we can link an Account Code on the Call Report to a subroutine that calculates and pull in data from other CU*BASE files. To keep this spec size manageable, the details are outlined in a different document: See separate Phase 4 spec. But in general, this would be a program name that would be coded to: ? Pull from last quarter's report ? Pull from credit union master parameters ? Run XXXXXX sub-routine using XXXX configuration records ? Run xxxx report using xxxx settings ? etc.

The idea would be for us to develop a series of automation sub-routines/programs and then apply them in our master control file to various account codes. It is likely that one routine (such as one that pulls in G/L Acct Balances) would work for a lot of different account codes. There would also be a matching configuration file/record (by account code) to provide any variable parameters the routine might need (such as which G/L accounts get added together for that code, etc.). The idea would be to let the CU define those parameters individually by CU (since we have to have the parms anyway, we might as well let the CU have control of those).

For some account codes (not sure yet how many), we might actually develop more than one automation routine, then the CU picks which one they want to use and configures it accordingly. For example, for an account code that requires loan account information, we might allow them to use either the G/L Acct Balance routine, or a separate loan Purpose Code routine, or a separate Security Code routine. All routines would work to populate that particular account code; just the configuration parameters would be slightly different.

New CU*BASE Database for 5300 Call Report Data

Project Requirements

3 of 15

Other Decisions To be Made

In creating the mockup on Page 10, I came across the first of what I'm sure will be many oddball situations we need to address, one by one, as we work through the account codes. There is a code A969A1 for "confirm email address" that obviously is just a repeat of the email they

already typed, to check for typos. There is a code A969AChk that is just "Check here for no email address" They also have several that are things like this: "Checkbox to copy Patriot Act primary into

emergency contact primary" which obviously just causes some sort of sub-routine to execute in the NCUA software I think these should be hidden from our maintenance screen, but when we design the interface to upload the data to the NCUA's web tool they might have to be addressed somehow.

Regarding multiple occurrences of the same account code ? it's actually pretty rare, and some of the repeated ones are calculated fields anyway (not manually entered by the CU) so we might not need to do a lot of complicated handling for these, but rather just show the Acct Code once. (On the NCUA software, once you enter it

New CU*BASE Database for 5300 Call Report Data

Project Requirements

4 of 15

(1) Master Control File

For CU*Answers to maintain according to changes made each quarter by the NCUA.

Would need routines to synch up this master control file with each CU's individual configuration file

(add new codes, suspend old codes, add new automation sub-routines/configs, etc.).

File Name: _____________________

Database Structure

Field Name

Status NCUA Account Code Display Sequence #

Description Data type

Available Automation Routines

XML Tag

Description

Specifications

Not sure about this - maybe for add, delete, etc., for the synch- 1 alphanumeric up routine?

Key field

20 alphanumeric

To allow for easy insertion of new fields in proper sequence (automatically re-sequencing subsequent items so they are presented in the proper order)

6 numeric

Brief description to match against the printed NCUA form

40 alphanumeric

$, #, @, D, % - see Page 2

1 alphanumeric

Will also be used for blank lines (B) and break headings (H)

Up to 10 different program names could be entered here to indicate which automation sub-routines are available to automatically calculate/populate data into the field. (CU would be able to choose which one they wanted to use, although many acct codes would only have one or two choices.)

Ten 10-byte alphanumeric fields

See the Quarterly Call Report Data file and other sections below for more information.

For eventual upload to NCUA ? see Phase 3 specs

??

Data Retention n/a ? permanent config file

Somehow need to flag the file as "ready to pull" so if a CU tries to create a new report before we have finished making all of the adjustments to the master control file, they will be stopped.

New CU*BASE Database for 5300 Call Report Data

Project Requirements

5 of 15

(2) Quarterly Call Report Data File

This is where the actual quarterly data goes as entered by a CU employee. Fields might be autopopulated by various automation sub-routines, but ultimately the CU employee verifies each individual field using the maintenance program described starting on Page 9.

File Name: _____________________

Database Structure

Field Name

Status Corp ID Date

NCUA Account Code Display Sequence #

Description Data type Input field Last Maintained By

XML Tag

Description

??

One 5300 call report per corporation (CU charter #)

Year (CCYY) and Quarter # (1, 2, 3, 4)

Such as 20091, 20092, 20093, etc. (but may display like "1st Quarter 2009" in some places where appropriate)

Key field

This must be stored along with the account code so that when reviewing historical call report data we can present the data in the same order in which it appeared on the original call report (NCUA frequently moves things around).

*see NOTE below

Store here so that historical data can easily be recreated

Store here so that historical data can easily be recreated

For the actual data entry; will vary depending on the data type (can't get precise field lengths ? see Page 2)

Date and employee ID Will be blank until the individual data field is updated manually by the employee (i.e., if the data has been auto-populated, then this date and ID will be blank)

*see NOTE below

For eventual upload to NCUA ? see Phase 3 specs

Specifications

1 alphanumeric 2 numeric 5 numeric

20 alphanumeric 6 numeric

40 alphanumeric 1 alphanumeric 60 alphanumeric 8 numeric 2 alphanumeric

??

*NOTE: Should be one control/header record (display seq = 00) that stores a date and employee ID when the report is first created for that period. This would be displayed on the initial maintenance screen (see Page 9).

Data Retention No purge ? retain data indefinitely at this point. Want the CU to be able to choose any comparison quarter when they go in to complete their next quarter's report, so we need to have all past data available online at all times without having to load tape.

New CU*BASE Database for 5300 Call Report Data

Project Requirements

6 of 15

(3) Auto-Pop Control File

This would be where we would store details about the various automation routines, including parameters that will help with the configuration process. (Also see the Specs for Phase 4.)

File Name: _____________________

Database Structure

Field Name

Name Description

Data type

Single or Range

Calc type

Description

Name of the sub-routine? Or program call name?

Such as "Pull Balances from Specified G/L Accounts" or "Count Loans with Specified Purpose Codes" or something

this is what the CU would see when choosing a routine to autopopulate a particular account code

Specifies what type of configuration data will need to be gathered/stored for the routine, such as:

G/L account # DIVAPL LNCATG Purpose Code Security Code

Acct Suffix Investment Type FASB Code etc...

Specifies whether the config asks for one code at a time or whether a range of codes can be entered

ranges would probably only be relevant for G/L acct #s ? everything else would be entered singly, I imagine

Not sure about this ? I think the program itself needs to specify exactly what the calculation it should do (i.e., add together CURBAL amounts, or count # of accounts, or add together Disbursement Limits, etc.)

Specifications

10 bytes? 40 bytes? ??

1 byte ??

Data Retention n/a ? permanent config file

Will always need to create a default, standard config that can be pushed out to CUs when implemented; CUs can then tweak the settings if needed.

New CU*BASE Database for 5300 Call Report Data

Project Requirements

7 of 15

(4) CU Configuration File

This would be where the CU could elect whether to use any of the auto-pop routines (or not). Might also somehow contain the related configurations too, but more likely will use separate files for those. (See Specs for Phase 4)

File Name: _____________________

Database Structure

Field Name

Corp ID NCUA Account Code Automation Routine

From To

Same as Acct Code

Description

Specifications

Each corp might need to use a different config

2 numeric

Key field

20 alphanumeric

For CU to choose which automation routine they want to use (or One 10-byte

none)

alphanumeric field

See the Quarterly Call Report Data file and other sections below for more information.

For the starting G/L account number or DIVAPL or purpose code, etc.

10 alphanumeric

For the ending G/L account number or DIVAPL or purpose code, etc. ? only used when defining a range of codes (as defined in the auto-pop control file), so probably will only be used for the ending G/L acct # unless we can think of any others

10 alphanumeric

To be used instead of defining a from/to range, in cases where the same group of data should be used by multiple account codes - links two configs together so they will be consistent even though what is being calculated (balances vs. counts vs. average rates, etc.) might be different

20 alphanumeric

See Page 3 of the Phase 4 specs for examples of this

Data Retention n/a ? permanent config file

New CU*BASE Database for 5300 Call Report Data

Project Requirements

8 of 15

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download