...

...

...

...

...

...

...

...

...

Definitions of Hash Values as found in dataExtract_AwardChange_View_v001

Introduction

This document contains the definitions of each of the hash values in the dataExtract_AwardChange_View_v001 Regent Award Data View.
Each hash value is comprised of either a single value or a collection of values concatenated together. Each hash value is meant to provide visibility into the state of the award at the time of each change.

Version Information

The first three characters each hash determine the version of the hash. Therefore, in the following hash value example, the version is 001. Ex:

0010000000007974000653520170101

Each hash value is a varchar(200) TSQL data type.

awardEligibleAmountHash

The award's eligibleAmount. This is a value in the Award Data Model. Award data is in

Example: 001000002204 001000002204.000 – the eligible amount in this hash is $2204.000

awardAmountsHash

This hash is the combination of the following data values. The order below is the order of concatenation.

• eligibleAmount
• estimatedAmount
• acceptedAmount
• offeredAmount
• autoAcceptedAmount
• amount
• budgetedAmount

These are values in the Award Data Model. Award data is located in the dataExtract_Award_View_v001 and dataExtract_Award_View_v002 data views.
Each amount in this hash is padded on the left with leading zeroes to a final length of 13 characters. The amounts are in decimal format with a precision of 3.

Example showing the breakdown of each amount in the hash: 001000002204.000000002204000000002204.000000000000000000000000.000000002204000000002204.000000000000000000000000.000000002204000000002204.000000000000000000000000.000

isirHash

The combination of the following columns, in the order below, for the ISIR attached to the award.

• isirRecordDataId
  • corresponding to dataExtract_ISIR_View.isirRecordDataId
  • Length = 20, Padded with leading 0's
• awardYear – The FAY of the ISIR attached to the award.
  • This is the 4-digit year where '2020' would correspond to FAY 19/20.
  • Length = 4
• transactionNumber
  • corresponding to dataExtract_ISIR_View.transactionNumber
  • Length = 2
• isirRecordPackageOptionId (not available in the data views)
  • Length = 2

...

• NSLDSAggregateSubsidizedTotal
  • corresponding to dataExtract_ISIR_View.NSLDSAggregateSubsidizedTotal
  • Length = 13
• NSLDSAggregateCombinedTotal
  • corresponding to dataExtract_ISIR_View.NSLDSAggregateCombinedTotal
  • Length = 13
• NSLDSPellLifetimeEligibilityUsed
  • corresponding to dataExtract_ISIR_View.NSLDSPellLifetimeEligibilityUsed
  • Length = 13
• offeredOnDate
  • corresponding to dataExtract_Award_View_v002.offeredOnDate
  • Length = 8
  • Format (YYYYMMDD)

...

The combination of the following columns, in the order below, for the ISIR attached to the award. The link of the ISIR is on the following column: a. loanPeriodId

• loanperiod.needFM
  • corresponding to dataExtract_LoanPeriod_View_v001
  • Length = 13
• loanperiod.needIM
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• loanperiod.cost
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• loanperiod.directCost
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• loanperiod.billedByInstCost
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• loanperiod.unmetNeedFM
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• loanperiod.unmetNeedIM
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• loanperiod.unmetCost unmetCostt
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• loanperiod.unmetDirectCost
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• loanperiod.unmetBilledByInstCost
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• (loanperiod.manualResources + loanperiod.resources)
  • corresponding to to dataExtract_LoanPeriod_View_v001
  • Length = 13
• Award.pellCOA
  • corresponding to dataExtract_Award_View_v002
  • Length = 13

Each amount in this hash is padded on the left with leading zeroes to a final length of 13 characters. The amounts are in decimal format with a precision of 3.
Example showing the breakdown of each amount in the hash: For example, the hash value below is broken up in the following manner (whitespace added at each hash begin/end point). 

001000014404.000000000000.000000014906.000000009050.000000009050.000000007132.000000007132.000000000007634.000000001778.000000001778.000000000000.000000000000.000

001 000014404.000 000000000.000 000014906.000 000009050.000 000009050.000 000007132.000000007132000 000007132.000 000007634000000007634.000000001778000 000001778.000 000001778.000000000000000 000000000.000000000000000 000000000.000

Award Period Hash

The combination of the following columns, in the order below, for the ISIR attached to the award The link of the ISIR is on the following column: a.awardPeriodId

• awardPeriod.startDate – The Award Period Start Date attached to the award
  • corresponding to dataExtract_AwardPeriod_View_v001.startdate
  • Length = 8
  • Format (YYYYMMDD)
• awardPeriod.endDate – The Award Period End Date attached to the award
  • corresponding to dataExtract_AwardPeriod_View_v001.enddate
  • Length = 8
  • Format (YYYYMMDD)
• federalawardyear.awardYear – The FAY of the award period attached to the award
  • corresponding to dataExtract_AwardPeriod_View_v001.right(federalAwardYear,4)awardYear
  • This is the 4-digit year where '2020' would correspond to FAY 19/20.
  • Length = 4

...

• loanperiod.startDate – The loan period start date
  • corresponding to dataExtract_LoanPeriod_View_v001.startdate
  • Length = 8
  • Format (YYYYMMDD)
• loanperiod.endDate – The loan period end date
  • corresponding to dataExtract_LoanPeriod_View_v001.enddate
  • Length = 8
  • Format (YYYYMMDD)

...

• EnrollmentLevelType.enrollmentLevelTypeId – The PK from the EnrollmentLevelType table
  • corresponding to dataExtract_Enrollment_View_v001.enrollmentLevelTypeCode
    • Dataview already does the conversation from the PK to the Name
  • Length = 1
  • Format 1 number from 0 to 4
    • 0 = Full Time
    • 1 = Three Quarter Time
    • 2 = Half Time
    • 3 = Less Than Half Time
    • 4 = None

...

The document hash is created by looking to see if there are any blocking documents that are directly related to the fund type. If the award (fund) has a blocking document then the value will be set to 1, else the value will be 0.
If there are any ('NEEDED','INCOMPLETE','UNSATISFIED') documents that will stop the award from being paid, the value will be flipped to 1, else 0.
Example showing the breakdown of each value in the hash: 0010

Task Hash

The task hash is created by looking to see if there are any blocking tasks that are directly related to the fund type. If the award (fund) has a blocking tasks then the value will be set to 1, else the value will be 0.
If there are any ('OPEN','INPROCESS','REOPENED','REVIEWED') tasks that will stop the award from being paid, the value will be flipped to 1, else 0.
Example showing the breakdown of each value in the hash: 0010

Grade Level Hash

The grade level hash is created by looking at the awards enrollment and using the column grade level on the enrollment row

• Enrollment.gradeLevel
  • corresponding to dataExtract_Enrollment_View_v001.gradeLevel
  • Length = 1

Example showing the breakdown of each value in the hash: 0010

Award Status Hash

The award Status Hash is created by looking at the award's current award status name

• award.awardStatusCode
  • corresponding to dataExtract_Award_View_v002.awardStatusCode

Example showing the breakdown of each value in the hash: 001Offered