/
Analysis of Boolean fields across the Promise Grant App and ensure documentation is correct

Analysis of Boolean fields across the Promise Grant App and ensure documentation is correct

Sep 16, 2019

JIRA:  OPENAPPLY-7049 - Getting issue details... STATUS


FieldCurrent DB ValueBOG Data Dictionary Value
app_id

bigint

  • Data Type/Format: Bigint
  • Data Type from CC Promise Grant Application Downloadable Fields: bigint
  • Length: blank
  • Allows Null: No
  • Value: blank
ccc_id

character varying(8) 

  • Format, Length: Character varying(8)
  • Data Type from CC Promise Grant Application Downloadable Fields: String
  • Length: 8
  • Allows Null: No. This is a system assigned field and is not entered by the user.
  • Value:

The format of the ccc_id is:
Positions 1 - 3: AAA thru ZZZ (Sequentially assigned beginning with AAA)

Positions 4 – 7: 0001 thru 9999 (Sequentially assigned beginning with 0001 for each value in positions 1-3)

Position 8: For future expansion if all values become exhausted.

Note: In positions 1-3, “I” and “O” are not used to avoid confusion with “0” and “1”.

confirmation_number

character varying(25)

  • Data Type/Format: Character
  • Data Type from CC Promise Grant Application Downloadable Fields: String
  • Length: 25
  • Allows Null: blank
  • Value: blank
statuscharacter(1)
  • Data Type/Format: Character(1)
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 1
  • Allows Null: blank
  • Value

Blank – In progress

I = Initially written to submitted table

D= Downloaded by college

app_langcharacter(2)
  • Format/Length: bpchar, 2
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 2
  • Allows Null: blank
  • Value:

    es –Spanish

    en –English

college_idcharacter(3)
  • Data Type/Format: Character(3)
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 3
  • Allows Null: blank
  • Value: blank
year_codebigint
  • Data Type/Format: Character varying(15)
  • Data Type from CC Promise Grant Application Downloadable Fields Table: Blank
  • Length: blank
  • Allows Null: blank
  • Value: blank


year_descriptioncharacter varying(100)
  • Data Type/Format: Character varying(15)
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 100
  • Allows Null: blank
  • Value: blank
determined_residentcacharacter(1)
  • Data Type/Format: Yes/No 1
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 1
  • Allows Null: blank

  • Value:

    1 - Yes/Sí [internal default]

    0 - No

determined_ab540_eligiblecharacter(1)
  • Data Type/Format: Yes/No 1
  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 1
  • Allows Null: blank
  • Value: 

    1 - Yes/Sí [internal default]

    0 – No

determined_non_res_exemptcharacter(1)
  • Data Type/Format: determined_non-res_exempt
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 1
  • Allows Null: blank
  • Value: 

    1 - Yes/Sí [internal default]

    0 – No

lastnamecharacter varying(50)
  • Data Type/Format: char, 50

    (Extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: String
  • Length: 50 (current code in OpenCCC is 40; however, new OpenCCC will be coded to 50 so there is no need to update the DD).
  • Allows Null: blank
  • Value: Text string
firstnamecharacter varying(50)
  • Data Type/Format: char, 50

    (Extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Text string; may be null.
middlenamecharacter varying(50)
  • Data Type/Format: char, 50
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Text string; may be null
mainphonecharacter varying(14)
  • Data Type/Format: char, 19 (OpenCCC Account) char, 14

    (CCCApply Standard and Promise Grant (BOG) applications) char, 25 (International application) 10 to 14 characters, numeric plus: (nnn) nnn-nnnn

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 14
  • Allows Null: blank
  • Value: Null or text string
mainphone_extcharacter varying(4)
  • Data Type/Format: char, 4
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 4
  • Allows Null: blank
  • Value: Null or numeric string; integer up to 9999
mainphone_auth_textboolean
  • Data Type/Format: char, 1 (OpenCCC Account)

    boolean, 1 (CCCApply Standard and International and Promise Grant (BOG) applications)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: boolean
  • Length: blank
  • Allows Null: blank
  • Value: 

    1 = Yes (checked)
    0 = No (not checked)

emailcharacter varying(128)
  • Data Type/Format: char, 254

    Note: Field length for Promise Grant (BOG) application is 128, Two fields; each up to 48 characters, alphanumeric plus ‘@’ and ‘.’

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Text string in valid email address format
non_us_addressboolean
  • Data Type/Format: boolean
  • Data Type from CC Promise Grant Application Downloadable Fields Table: Boolean
  • Length: blank
  • Allows Null: blank
  • Value: 

    encrypted

streetaddress1character varying(50)
  • Data Type/Format: char, 50

    Two fields (extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Two text strings; second may be null
streetaddress2character varying(50)
  • Data Type/Format: char, 50

    Two fields (extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Two text strings; second may be null
citycharacter varying(50)
  • Data Type/Format: char, 50

    (Extended character set, allowing hyphens, letters with diacritical marks or accents, etc.)

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 50
  • Allows Null: blank
  • Value: Text string
statecharacter varying(2)
  • Data Type/Format: char, 2
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 2
  • Allows Null: blank
  • Value: blank/null or 2-character USPS state code
provincecharacter varying(30)
  • Data Type/Format: char, 30

    Extended character set, allowing hyphens, letters with diacritical marks or accents, etc.

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 30
  • Allows Null: blank
  • Value: Null or text string
countrycharacter varying(2)
  • Data Type/Format: char, 2
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 2
  • Allows Null: blank
  • Value: ISO 3166 International Standard for country codes Alpha-2 code (officially assigned)
postalcodecharacter varying(20)
  • Data Type/Format: char, 20 numeric-plus: nnnnn-nnnn or nnnnn

      extended character set, allowing hyphens, symbols, letters with diacritical marks or accents, etc.

  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: 20
  • Allows Null: blank
  • Value: Null or nnnnn-nnnn or nnnnn
ssnencrypted SSN
  • Data Type/Format: text, 11 - Data is encrypted
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length: blank
  • Allows Null: blank
  • Value: Null or numeric string with hyphens
ssn_hash



Note: the ssn_hash field hasn't been populated since December 2017, it appears. 

  • Data Type/Format: text
  • Data Type from CC Promise Grant Application Downloadable Fields Table: string
  • Length:
  • Allows Null:
  • Value: 
ssn_type
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
student_college_id
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
birthdate
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
marital_status
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
reg_dom_partner
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
born_before_23_year
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
married_or_rdp
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
us_veteran
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dependents
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
parents_deceased
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
emancipated_minor
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
legal_guardianship
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
homeless_youth_school
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
homeless_youth_hud
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
homeless_youth_other
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dependent_on_parent_taxes
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
living_with_parents
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dependency_status
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
cert_veteran_affairs
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
cert_national_guard
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_medal_honor
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_sept_11
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_police_fire
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tanf_calworks
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ssi_ssp
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
general_assistance character
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
parents_assistance character
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dep_number_household
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ind_number_household
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dep_gross_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ind_gross_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dep_other_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ind_other_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
dep_total_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ind_total_income
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_method_a
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_method_b
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
elig_bogfw
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
confirmation_parent_guardian
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
parent_guardian_name
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ack_fin_aid
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
confirmation_applicant
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
last_page
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ssn_last4
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tstmp_submit
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tstmp_create
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tstmp_update
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
tstmp_download
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
term_code
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
cryptokeyid integer
  • Data Type/Format: 
  • Data Type from CC Promise Grant Application Downloadable Fields Table:
  • Length:
  • Allows Null:
  • Value: 
ip_address

campaign1

campaign2

campaign3

ssn_exception

college_name

preferred_firstname

preferred_middlename

preferred_lastname

preferred_name

ssn_no

no_perm_address_homeless

no_mailing_address_homeless

determined_homeless

elig_method_d

mainphoneintl