VACOLS DB Schema - TISTATechnologies/caseflow GitHub Wiki

Caseflow's VACOLS models

• appeal_repository.rb to query and update VACOLS
• case.rb has mappings of VACOLS values to more intuitive values
• legacy_appeal.rb - model that queries VACOLS for Legacy appeal info
• vacols_helper.rb - utility methods to handle VACOLS quirks
• associated_vacols_model.rb
  • vacols_attr_accessors will lazy load the underlying data from the VACOLS DB upon first call. For example, appeal = LegacyAppeal.find(id) will not make any calls to load the data from VACOLS, but soon as we call appeal.veteran_first_name, it will trigger the VACOLS DB lookup and fill in all instance variables for the appeal. Further requests will pull the values from memory and not do subsequent VACOLS DB lookups
  • AppealRepository.load_vacols_data calls set_vacols_values to load appeal (aka "case") information from VACOLS.
• Location codes - in the code
• Docs in the VACOLS repo
  • VACOLS Reference docs - VACOLS_Table_Joins.xls
  • Feb 2017 VACOLS doc in appeals-data repo - explanations of example queries

Also check out visualization of table relationships.

• BFKEY - Appeal ID (connects to FOLDER)
• BFCORLID - Veteran ID, SSN (VBMS ID)
• BFCORKEY - connects to CORRES
• BFREGOFF - Regional Office
• BFSO - Service organization (exclude 'T')
• BFCURLOC - Location Code (99 - Full Grant, 97 - Partial/Remand)
• BFDEC - Decision date (just visual, but make it equals TIOCTIME)
  • Appeal Status: BFMPRO, 3 chars
    • Stats for Legacy Appeal status
    • Status code definitions
    • ACT: ACTIVE (Case currently at BVA)
    • ADV: ADVANCE (NOD Filed. Case currently at RO)
    • REM: REMAND (Case has been Remanded to RO or AMC)
    • HIS: HISTORY (BVA action is complete)
    • MOT: MOTION (appellant has filed a motion for reconsideration)
  • From Caseflow: case.rb
• BFDC - Disposition of Appeal; see VACOLS.System.Codes.doc at VACOLS Reference Docs for Disposition Codes
  • 1: Allowed
  • 3: Remanded
  • 4: Denied
  • 5: Vacated
  • 6: Dismissed, Other
  • 8: Dismissed, Death
  • 9: Withdrawn
  • D: Designation of Record
  • M: Merged Appeal
  • R: Reconsideration by Letter
  • Above are codes for "BVA Disposition". There are other disposition codes that refer to "Field Disposition"
• BFDLOOUT - represents how long the case has been ready to distribute

• SNAMEF, SNAMEL, SSN - basic Info
• STAFKEY - connects to BRIEFF

• TICKNUM - connects to BRIEFF
• TIOCTIME - Outcoding date (make sure BFDEC is the same)
• TIVBMS - Appeal in VBMS?
  • From Caseflow: folder.rb

• From Caseflow: case_hearing.rb
• Caseflow’s legacy_hearings.vacols_id corresponds to VACOLS’ hearsched.hearing_pkseq
  • mentioned in dsva-vacols#124

• From Caseflow: issue.rb

• CLMFLD - 3 characters; Claims folder volumes; "for virtual appeals the value should be left null ... should only be entered for paper cases and is normally 1 or 2 volumes" ref

• representative.rb

• VACOLS::Staff.count => 4072
• Active/Inactive Flag: SACTIVE, 1 char, A= Active; I= Inactive
  • VACOLS::Staff.select(:sactive).distinct.pluck(:sactive) => ["A", "I", nil, "V"]
• Attorney Number: SATTYID, 4 chars
  • VACOLS::Staff.select(:sattyid).distinct.size => 2047
• VLJ (A or J): SVLJ, 1 char
  • VACOLS::Staff.select(:svlj).distinct.pluck(:svlj) => [nil, "I", "A", "J"]
    • svlj = "J" : Judge
    • svlj = "A" : Acting Judge
    • svlj = nil : Attorney
• Stats for Attorneys, Judges, Acting Judges
• Uses from Caseflow: attorney_repository.rb, judge_repository.rb, user_repository.rb

CORRES table contains most of Veteran's info (should match VBMS)

TIVBMS field allows us to determine if the claim is ready for VBMS

TIOCTIME of no greater than 3 days ago will let us pull that specific Full Grant (no time limit on Partial/Remands)

BRIEFF.BFREGOFF and BFCURLOC, PRIORLOC.LOCDIN and LOCDOUT, ASSIGN.TSKRQACT will be updated by Dispatch must be validated