Difference between revisions of "CU Customization"

From Vital Soft Wiki
Jump to: navigation, search
>Manager
>Manager
(→‎Member Attributes Sheet)
 
(10 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
==Overview==
+
==Summary==
 
The CU Customization spreadsheet contains several sheets: Overview, Extraction Macros, Custom Ranges and Member Attributes.
 
The CU Customization spreadsheet contains several sheets: Overview, Extraction Macros, Custom Ranges and Member Attributes.
 +
 +
This spreadsheet is given to the Credit Union prior to the Vital Signs install and should be returned to Vital Soft after it has been completed.
  
 
The '''Overview''' sheet explains the rest of the CU Customization document and has several fields of input, specifically the Credit Union Name will be what is entered into Vital Signs.
 
The '''Overview''' sheet explains the rest of the CU Customization document and has several fields of input, specifically the Credit Union Name will be what is entered into Vital Signs.
Line 11: Line 13:
  
  
−
==Extraction Macros==
+
==Overview Sheet==
 +
 
 +
Contains a brief overview of the document and entries for contact information.
 +
 
 +
The Credit Union Name entry will be used in Vital Signs in the Summary Applications.
 +
 
 +
 
 +
==Extraction Macros Sheet==
 +
 
 
The following are core member attributes in Vital Signs defined by the Credit Union
 
The following are core member attributes in Vital Signs defined by the Credit Union
  
Line 85: Line 95:
  
 
'''Member Relate Code'''
 
'''Member Relate Code'''
 +
 
How should Vital Signs gather the MEMBER RELATE code?
 
How should Vital Signs gather the MEMBER RELATE code?
  
Line 97: Line 108:
  
 
'''Share Record Status Open'''
 
'''Share Record Status Open'''
 +
 
How should Vital Signs determine that a share is open?
 
How should Vital Signs determine that a share is open?
  
Line 105: Line 117:
  
 
'''Share Record Countable'''
 
'''Share Record Countable'''
 +
 
How should Vital Signs determine that a share can be counted in the total shares?
 
How should Vital Signs determine that a share can be counted in the total shares?
  
Line 114: Line 127:
  
 
'''Share Charged Off'''
 
'''Share Charged Off'''
 +
 
How should Vital Signs determine that a share is charged off?
 
How should Vital Signs determine that a share is charged off?
  
Line 121: Line 135:
  
 
'''Loan Record Status Open'''
 
'''Loan Record Status Open'''
 +
 
How should Vital Signs determine that a loan is open?
 
How should Vital Signs determine that a loan is open?
  
Line 129: Line 144:
  
 
'''Loan Record Countable'''
 
'''Loan Record Countable'''
 +
 
How should Vital Signs determine that a loan can be counted in the total loans?
 
How should Vital Signs determine that a loan can be counted in the total loans?
  
Line 137: Line 153:
  
 
'''Loan Charged Off'''
 
'''Loan Charged Off'''
 +
 
How should Vital Signs determine that a loan is charged off?
 
How should Vital Signs determine that a loan is charged off?
 
    
 
    
Line 143: Line 160:
  
  
−
==Custom Ranges==
+
==Custom Ranges Sheet==
 +
 
 
The customizable ranges used in Vital Signs
 
The customizable ranges used in Vital Signs
  
Line 151: Line 169:
  
 
     Example:
 
     Example:
−
     Tier Min Value Description
+
     Tier   Min Value   Description
−
    U 0     U (Undefined No Score)
+
      U           0   U (Undefined No Score)
−
    E 1     E (Less than 520)
+
      E           1   E (Less than 520)
−
    D 520     D (520 – 580)
+
      D         520   D (520 – 580)
−
    C 581     C ( 581 – 620)
+
      C         581   C ( 581 – 620)
−
    B 621     B (621 – 700)
+
      B         621   B (621 – 700)
−
    A 701     A (701-730)
+
      A         701   A (701-730)
−
    A+ 730     A+ (Over 730)
+
      A+       730   A+ (Over 730)
  
  
Line 166: Line 184:
  
 
     Example:
 
     Example:
−
     Range Description
+
      Range     Description
−
     0         No Age
+
          0     No Age
−
    1 – 15 15 or Less
+
    1 – 15     15 or Less
−
     16 – 21 16 – 21
+
     16 – 21     16 – 21
−
     22 – 29 22 – 29
+
     22 – 29     22 – 29
−
     30 – 39 30 – 39
+
     30 – 39     30 – 39
−
     40 – 49 40 – 49
+
     40 – 49     40 – 49
−
     50 – 65 50 – 65
+
     50 – 65     50 – 65
−
    66+         Over 65
+
        66+   Over 65
  
  
Line 182: Line 200:
  
 
     Example:
 
     Example:
−
     Range Description
+
     Range       Description
−
         0 < 1 Year
+
         0       < 1 Year
−
         1 1 Years
+
         1       1 Years
−
         2 2 Years
+
         2       2 Years
−
     3 – 5 3 – 5 Years
+
     3 – 5       3 – 5 Years
−
   6 – 10 6 – 10 Years
+
   6 – 10       6 – 10 Years
−
       10+ Over 10 Years
+
       10+     Over 10 Years
  
  
Line 196: Line 214:
  
 
     Example:
 
     Example:
−
     Min Value Description
+
     Min Value   Description
−
             - Negative
+
             -   Negative
−
         $0.00 Zero
+
         $0.00   Zero
−
         $0.01 $0.01 - $4.99
+
         $0.01   $0.01 - $4.99
−
         $5.00 $5 - $49.99
+
         $5.00   $5 - $49.99
−
       $50.00 $50 – $99.99
+
       $50.00   $50 – $99.99
−
       $100.00 $100 - $499.99
+
       $100.00   $100 - $499.99
−
       $500.00 $500 - $999.99
+
       $500.00   $500 - $999.99
−
     $1,000.00 $1K - $9,999.99
+
     $1,000.00   $1K - $9,999.99
−
   $10,000.00 $10K - $99,999.99
+
   $10,000.00   $10K - $99,999.99
−
   $100,000.00 $100K and Over
+
   $100,000.00   $100K and Over
  
  
Line 214: Line 232:
  
 
     Example:
 
     Example:
−
     Min Value Description
+
     Min Value   Description
−
             - Negative
+
             -   Negative
−
         $0.00 Zero
+
         $0.00   Zero
−
         $0.01 $0.01 - $4.99
+
         $0.01   $0.01 - $4.99
−
         $5.00 $5 - $49.99
+
         $5.00   $5 - $49.99
−
       $50.00 $50 – $99.99
+
       $50.00   $50 – $99.99
−
       $100.00 $100 - $499.99
+
       $100.00   $100 - $499.99
−
       $500.00 $500 - $999.99
+
       $500.00   $500 - $999.99
−
     $1,000.00 $1K - $9,999.99
+
     $1,000.00   $1K - $9,999.99
−
   $10,000.00 $10K - $99,999.99
+
   $10,000.00   $10K - $99,999.99
−
   $100,000.00 $100K and Over
+
   $100,000.00   $100K and Over
  
  
Line 233: Line 251:
 
     Example:
 
     Example:
 
     Min Value  Description
 
     Min Value  Description
−
         0.00% Zero
+
         0.00%   Zero
−
         0.00% <= 0.50%
+
         0.00%   <= 0.50%
−
         0.50% >= 0.50%  < 1.00%
+
         0.50%   >= 0.50%  < 1.00%
−
         1.00% >= 1.00%  < 1.50%
+
         1.00%   >= 1.00%  < 1.50%
−
         1.50% >= 1.50%  < 2.00%
+
         1.50%   >= 1.50%  < 2.00%
−
         2.00% >= 2.00%  < 2.50%
+
         2.00%   >= 2.00%  < 2.50%
−
         2.50% >= 2.50%  < 3.00%
+
         2.50%   >= 2.50%  < 3.00%
−
         3.00% >= 3.00%  < 3.50%
+
         3.00%   >= 3.00%  < 3.50%
−
         3.50% >= 3.50%  < 3.75%
+
         3.50%   >= 3.50%  < 3.75%
−
         3.75% >= 3.75%  < 4.00%
+
         3.75%   >= 3.75%  < 4.00%
−
         4.00% >= 4.00%  < 4.25%
+
         4.00%   >= 4.00%  < 4.25%
−
         4.25% >= 4.25%  < 4.50%
+
         4.25%   >= 4.25%  < 4.50%
−
         4.50% >= 4.50%  < 4.75%
+
         4.50%   >= 4.50%  < 4.75%
−
         4.75% >= 4.75%  < 5.00%
+
         4.75%   >= 4.75%  < 5.00%
−
         5.00% >= 5.00%  < 5.25%
+
         5.00%   >= 5.00%  < 5.25%
−
         5.25% >= 5.25%  < 5.50%
+
         5.25%   >= 5.25%  < 5.50%
−
         5.50%   >= 5.50%  < 5.75%
+
         5.50%   >= 5.50%  < 5.75%
−
         5.75% >= 5.75%  < 6.00%
+
         5.75%   >= 5.75%  < 6.00%
−
         6.00% >= 6.00%  < 6.25%
+
         6.00%   >= 6.00%  < 6.25%
−
         6.25% >= 6.25%  < 6.50%
+
         6.25%   >= 6.25%  < 6.50%
−
         6.50% >= 6.50%  < 6.75%
+
         6.50%   >= 6.50%  < 6.75%
−
         6.75% >= 6.75%  < 7.00%
+
         6.75%   >= 6.75%  < 7.00%
−
         7.00% >= 7.00%  < 7.25%
+
         7.00%   >= 7.00%  < 7.25%
−
         7.25% >= 7.25%  < 7.50%
+
         7.25%   >= 7.25%  < 7.50%
−
         7.50% >= 7.50%
+
         7.50%   >= 7.50%
  
  
Line 266: Line 284:
 
     Example:
 
     Example:
 
     Min Value  Description
 
     Min Value  Description
−
        Negative
+
                Negative
−
         0.00% Zero
+
         0.00%   Zero
−
         0.01% <=  80%
+
         0.01%   <=  80%
−
       80.01% 80.01% - 90%
+
       80.01%   80.01% - 90%
−
       90.01% 90.01% - 100%
+
       90.01%   90.01% - 100%
−
       100.01% Over 100%
+
       100.01%   Over 100%
  
  
Line 280: Line 298:
 
     Example:
 
     Example:
 
     Min Value  Description
 
     Min Value  Description
−
        1-30 Days
+
                1-30 Days
−
           31 31-60 Days
+
           31   31-60 Days
−
           61 61-90 Days
+
           61   61-90 Days
−
           91 91-120 Days
+
           91   91-120 Days
−
           121 121-180 Days
+
           121   121-180 Days
−
           181 6 Mths - 1 Year
+
           181   6 Mths - 1 Year
−
           366 1 – 2 Years
+
           366   1 – 2 Years
−
           731 2 – 3 Years
+
           731   2 – 3 Years
−
         1,096 3 – 4 Years
+
         1,096   3 – 4 Years
−
         1,461 4 – 5 Years
+
         1,461   4 – 5 Years
−
         1,826 Over 5 Years
+
         1,826   Over 5 Years
  
  
Line 299: Line 317:
 
     Example:
 
     Example:
 
     Min Value  Description
 
     Min Value  Description
−
          Current
+
                Current
−
             1 1-15 Days
+
             1   1-15 Days
−
           16 16-30 Days
+
           16   16-30 Days
−
           31 31-60 Days
+
           31   31-60 Days
−
           61 61-90 Days
+
           61   61-90 Days
−
           91 91-120 Days
+
           91   91-120 Days
−
           121 121-180 Days
+
           121   121-180 Days
−
           181 6 Mths - 1 Year
+
           181   6 Mths - 1 Year
−
           366 Over 1 Year
+
           366   Over 1 Year
 +
 
 +
 
 +
==Member Attributes Sheet==
 +
 
 +
The Member Attributes Sheet contains a table with columns: Attribute #, Product #, Name and Criteria.
 +
 
 +
Example Screen shot of
 +
 
 +
 
 +
'''Attribute #'''
 +
 
 +
The attribute number is the number that corresponds to where the attribute will be displayed on the Vital Signs Mbr Attribute tab.  There are 2 columns of 15 attributes each.
 +
 
 +
    Valid range is 1-30
 +
 
 +
 
 +
'''Product #'''
 +
 
 +
The product number is the number that corresponds to where the product will be displayed in the Member Product Counts area of the Mbr Attributes tab.  The Member Product Counts area contains two columns with 10 Products in each one. A product # should only be given to an Attribute that is countable : Debit Card or Vehicle Loan.
 +
 
 +
    Valid range is 1-10
 +
 
 +
 
 +
'''Name'''
 +
 
 +
The name of the attribute/product.  This name will appear in Vital Signs.
 +
 
 +
 
 +
'''Criteria'''
 +
 
 +
The criteria from core fields in Spectrum that define the attribute
 +
 
 +
 
 +
'''Comment'''
 +
 
 +
Any additional comments you would like to give Vital Soft for implementation of the attribute.
 +
 
 +
 
 +
'''Recommended Attributes'''
 +
 
 +
    Attribute #  Product #  Description      Criteria                                                                Comments
 +
        1                    Single Service    SHLNTOTS.MBR-ACTIVE-SH-CNT = 1 AND SHLNTOTS.MBR-ACTIVE-LN-CNT=0
 +
        2                    Suffix 0 Funded  SHARE-FILE.SUFFIX = 0 AND SHARE-FILE.BALANCE >=$5.00
 +
        3                    Employee          MEMBER-FILE.CONTROL-FLAGS(73)=1                                          Criteria showing that a member is also an employee.
 +
        4                    Charged Off      MEMBER-FILE.CONTROL-FLAGS(34)=1 OR MEMBER-FILE.CONTROL-FLAGS(31)=1
 +
        5                    OK to Mail        MEMBER-FILE.CONTROL-FLAGS(21) <> 1 AND
 +
                                              MEMBER-FILE.CONTROL-FLAGS(24) <> 1 AND
 +
                                              MEMBER-FILE.CONTROL-FLAGS(29) <> 1                                      This attribute indicates that it is ok to mail these individuals.

Latest revision as of 22:40, 14 September 2012

Summary

The CU Customization spreadsheet contains several sheets: Overview, Extraction Macros, Custom Ranges and Member Attributes.

This spreadsheet is given to the Credit Union prior to the Vital Signs install and should be returned to Vital Soft after it has been completed.

The Overview sheet explains the rest of the CU Customization document and has several fields of input, specifically the Credit Union Name will be what is entered into Vital Signs.

The Extraction Macros sheet contains core definitions for attributes of the member set during the extraction of core data on the HPUX machine.

The Custom Ranges sheet contains the customizable ranges used in Vital Signs.

The Member Attributes sheet contains the 30 possible Custom Attributes that are defined by the Credit Union.


Overview Sheet

Contains a brief overview of the document and entries for contact information.

The Credit Union Name entry will be used in Vital Signs in the Summary Applications.


Extraction Macros Sheet

The following are core member attributes in Vital Signs defined by the Credit Union

Member Record Status Open

How should Vital Signs determine that a member is open? 

  Default Criteria:
  (MEMBER-FILE.REST-FLAG-11 <> 80, 81 AND 
   MEMBER-FILE.REST-FLAG-12 <> 80, 81)


Member PSA Funded

What is the minimum required balance in a primary savings account? 

   Default Criteria:
   (SHARE-FILE.BALANCE >= 500)


Member Charged Off Loan

How should Vital Signs determine that a member has a charged off loan? 

   Default Criteria:
   (MEMBER-FILE.CONTROL-FLAG(31) = 1)


Member Charged Off Share

How should Vital Signs determine that a member has a charged off share? 

   Default Criteria:
   (MEMBER-FILE.CONTROL-FLAG(34) = 1)


Member Dormant

How should Vital Signs determine that a member is dormant? 

   Default Criteria:
   (MEMBER-FILE.REST-FLAG-11 = 79)


Member Do Not Mail

How should Vital Signs determine that a member should not receive mail? 

   Default Criteria:
   MEMBER-FILE.CONTROL-FLAGS(21) = 1


Member Bad Address

How should Vital Signs determine that a member has a Bad Address? 

   Default Criteria:
   MEMBER-FILE.CONTROL-FLAGS(24) = 1


Member Credit Rating

How should Vital Signs gather the credit FICO score for a member? 

   Available Options:

   Default
   (MEMBER-FILE.CREDIT-RATING)

   The alternative is to gather this information from the most recent loan.
   (LNCRSCORE.LN-LAST-CRSCORE)


Member Relate Code

How should Vital Signs gather the MEMBER RELATE code? 

   Available Options:

   Default
  (MEMBER-RELATE.CURRENT-CODE)

   A common alternative is to gather this information from 
   (MEMBER-FILE.DEPARTMENT)


Share Record Status Open

How should Vital Signs determine that a share is open? 

   Default Criteria:
   (SHARE-FILE.REST-FLAG-11 <> 80, 81 AND 
    SHARE-FILE.REST-FLAG-12 <> 80, 81)


Share Record Countable

How should Vital Signs determine that a share can be counted in the total shares? 

   Default Criteria:
   (SHARE-FILE.REST-FLAG-11 <> 80, 81 AND 
    SHARE-FILE.REST-FLAG-12 <> 80, 81 AND 
    SHARE-FILE.DESC-ABRV="COS")


Share Charged Off

How should Vital Signs determine that a share is charged off? 

   Default Criteria:
   (SHARE-FILE.DESC-ABRV="COS")


Loan Record Status Open

How should Vital Signs determine that a loan is open? 

   Default Criteria:
   (LOAN-FILE.REST-FLAG-11 <> 80, 81 AND 
    LOAN-FILE.REST-FLAG-12 <> 80, 81)


Loan Record Countable

How should Vital Signs determine that a loan can be counted in the total loans? 

   Default Criteria:
   (LOAN-FILE.REST-FLAG-11 <> 80, 81 AND LOAN-FILE.REST-FLAG-12 <> 80, 81 AND
   (LOAN-FILE.BALANCE > 0 OR LOAN-FILE.CREDIT-LIM > 0) AND LOAN-FILE.ACTION = "D")


Loan Charged Off

How should Vital Signs determine that a loan is charged off? 

   Default Criteria:
   (LOAN-FILE.COLLATERAL = 999 OR LOAN-FILE.ACTION = "O")


Custom Ranges Sheet

The customizable ranges used in Vital Signs

Credit Tiers

Credit Tiers for the FICO score that can be displayed in Vital Signs. A max of 10 tiers is allowed. 

   Example:
   Tier   Min Value   Description
      U           0   U (Undefined No Score)
      E           1   E (Less than 520)
      D         520   D (520 – 580)
      C         581   C ( 581 – 620)
      B         621   B (621 – 700)
      A         701   A (701-730)
      A+        730   A+ (Over 730)


Member Age Range

Grouping by Members age 

   Example:
     Range     Description
         0     No Age
    1 – 15     15 or Less
   16 – 21     16 – 21
   22 – 29     22 – 29
   30 – 39     30 – 39
   40 – 49     40 – 49
   50 – 65     50 – 65
        66+    Over 65


Account Age Range

Grouping by Account age 

   Example:
   Range       Description
       0       < 1 Year
       1       1 Years
       2       2 Years
   3 – 5       3 – 5 Years
  6 – 10       6 – 10 Years
      10+      Over 10 Years


Share Balance Tiers

Grouping by individual Share balances 

   Example:
   Min Value   Description
           -   Negative
       $0.00   Zero
       $0.01   $0.01 - $4.99
       $5.00   $5 - $49.99
      $50.00   $50 – $99.99
     $100.00   $100 - $499.99
     $500.00   $500 - $999.99
   $1,000.00   $1K - $9,999.99
  $10,000.00   $10K - $99,999.99
 $100,000.00   $100K and Over


Loan Balance Tiers

Grouping by individual Loan balances 

   Example:
   Min Value   Description
           -   Negative
       $0.00   Zero
       $0.01   $0.01 - $4.99
       $5.00   $5 - $49.99
      $50.00   $50 – $99.99
     $100.00   $100 - $499.99
     $500.00   $500 - $999.99
   $1,000.00   $1K - $9,999.99
  $10,000.00   $10K - $99,999.99
 $100,000.00   $100K and Over


Rates

Apr grouping for both Share dividends and Loan aprs 

   Example:
   Min Value   Description
       0.00%   Zero
       0.00%   <= 0.50%
       0.50%   >= 0.50%  < 1.00%
       1.00%   >= 1.00%  < 1.50%
       1.50%   >= 1.50%  < 2.00%
       2.00%   >= 2.00%  < 2.50%
       2.50%   >= 2.50%  < 3.00%
       3.00%   >= 3.00%  < 3.50%
       3.50%   >= 3.50%  < 3.75%
       3.75%   >= 3.75%  < 4.00%
       4.00%   >= 4.00%  < 4.25%
       4.25%   >= 4.25%  < 4.50%
       4.50%   >= 4.50%  < 4.75%
       4.75%   >= 4.75%  < 5.00%
       5.00%   >= 5.00%  < 5.25%
       5.25%   >= 5.25%  < 5.50%
       5.50%   >= 5.50%  < 5.75%
       5.75%   >= 5.75%  < 6.00%
       6.00%   >= 6.00%  < 6.25%
       6.25%   >= 6.25%  < 6.50%
       6.50%   >= 6.50%  < 6.75%
       6.75%   >= 6.75%  < 7.00%
       7.00%   >= 7.00%  < 7.25%
       7.25%   >= 7.25%  < 7.50%
       7.50%   >= 7.50%


Loan To Value

Range for Loan to Value percentages 

   Example:
   Min Value   Description
               Negative
       0.00%   Zero
       0.01%   <=  80%
      80.01%   80.01% - 90%
      90.01%   90.01% - 100%
     100.01%   Over 100%


Maturity Date

Day ranges for Maturing Shares and Loans 

   Example:
   Min Value   Description
               1-30 Days
          31   31-60 Days
          61   61-90 Days
          91   91-120 Days
         121   121-180 Days
         181   6 Mths - 1 Year
         366   1 – 2 Years
         731   2 – 3 Years
       1,096   3 – 4 Years
       1,461   4 – 5 Years
       1,826   Over 5 Years


Delinquent Days

Day ranges for Delinquent Loans 

   Example:
   Min Value   Description
               Current
           1   1-15 Days
          16   16-30 Days
          31   31-60 Days
          61   61-90 Days
          91   91-120 Days
         121   121-180 Days
         181   6 Mths - 1 Year
         366   Over 1 Year


Member Attributes Sheet

The Member Attributes Sheet contains a table with columns: Attribute #, Product #, Name and Criteria.

Example Screen shot of


Attribute #

The attribute number is the number that corresponds to where the attribute will be displayed on the Vital Signs Mbr Attribute tab. There are 2 columns of 15 attributes each. 

   Valid range is 1-30


Product #

The product number is the number that corresponds to where the product will be displayed in the Member Product Counts area of the Mbr Attributes tab. The Member Product Counts area contains two columns with 10 Products in each one. A product # should only be given to an Attribute that is countable : Debit Card or Vehicle Loan. 

   Valid range is 1-10


Name

The name of the attribute/product. This name will appear in Vital Signs.


Criteria

The criteria from core fields in Spectrum that define the attribute


Comment

Any additional comments you would like to give Vital Soft for implementation of the attribute.


Recommended Attributes 

   Attribute #  Product #   Description       Criteria                                                                 Comments
       1                    Single Service    SHLNTOTS.MBR-ACTIVE-SH-CNT = 1 AND SHLNTOTS.MBR-ACTIVE-LN-CNT=0
       2                    Suffix 0 Funded   SHARE-FILE.SUFFIX = 0 AND SHARE-FILE.BALANCE >=$5.00
       3                    Employee          MEMBER-FILE.CONTROL-FLAGS(73)=1                                          Criteria showing that a member is also an employee.
       4                    Charged Off       MEMBER-FILE.CONTROL-FLAGS(34)=1 OR MEMBER-FILE.CONTROL-FLAGS(31)=1
       5                    OK to Mail        MEMBER-FILE.CONTROL-FLAGS(21) <> 1 AND 
                                              MEMBER-FILE.CONTROL-FLAGS(24) <> 1 AND
                                              MEMBER-FILE.CONTROL-FLAGS(29) <> 1                                       This attribute indicates that it is ok to mail these individuals.
Retrieved from "https://wiki.vital-soft.com/index.php?title=CU_Customization&oldid=947"