Accreditation
See Accreditation Dashboard -> Accreditation Dashboard
See Program Quality Standards Dashboard -> Program Quality Standards
The Accreditation data contains the Accreditation and Program Quality Standards information. It consists of these tables:
ApplicationChangesApplicationStatusProgramQualityStandards
ApplicationStatus
This table tells you the Accreditation status of each program and the status of their current application. Each program has an overall status of if it is accredited, and then it has a status and mid cycle status of it's current application. Accreditation cycles are 2 year cycles. This table is very important for the Finance team. This is used in the Accreditation dashboard.
| colname | colvalues | coltype | detail |
|---|---|---|---|
ApplicationYear |
integer | Year of the accreditation application. Accreditation is on a two year cycle | |
CycleId |
integer | Id of the cycle, this is helpful for ordering the cycles and understanding which year application is in which cycle | |
CycleYearRange |
Ex: 2025-2026 | varchar | Year range of the cycle |
CycleStartDate |
datetime | Cycle start date | |
CycleEndDate |
datetime | Cycle end date | |
NationalOrInternational |
National, International | varchar | If the application is from an international or national program |
StageTitle |
International Stage 1, International Stage 2, National Stage 1, National Stage 2 | varchar | Title of the stage as it shows on the accreditation portal |
RegionId |
integer | Region ID | |
Region |
varchar | Region | |
ProgramId |
integer | Program ID | |
ProgramName |
varchar | Program | |
ApplicationId |
integer | Accreditation application Id | |
ApplicationStatus |
In Progress, Submitted, Approved, Cancelled | varchar | Accreditation application status |
ApplicationStatusUpdatedDate |
datetime | Date that the ApplicationStatus was updated | |
MidCycleStatus |
In Progress, Submitted, Approved | varchar | Accreditation application mid cycle status, remember that the cycle is 2 years |
MidCycleStatusUpdatedDate |
datetime | Date that the MidCycleStatus was updated | |
ProgramStatus |
OFAC Santions Apply, Accredited, Founding Committee, Not Accredited, Conditionally Accredited | varchar | Program overall accreditation status |
ProgramStatusWithExpiration |
Ex: Accredited to 03/31/2027 | varchar | The ProgramStatus with the date that the program is accredited to. To mimic the portal |
ProgramStatusExpirationDate |
datetime | Date that the ProgramStatus expires | |
ProgramStatusCreatedDate |
datetime | Date that the ProgramStatus was created | |
CreatedDateTime |
datetime | Created date time of the table in the pipeline | |
AssetId |
varchar | Asset ID of the pipeline |
ApplicationChanges
This table shows the specific questions and answers in the accreditation application. Questions are referred to as ChecklistItems. This table is very important for the Finance team. This is used in the Accreditation dashboard.
| colname | colvalues | coltype | detail |
|---|---|---|---|
ApplicationId |
integer | Accreditation application Id | |
ApplicationYear |
integer | Year of the accreditation application. Accreditation is on a two year cycle | |
NationalOrInternational |
National, International | varchar | If the application is from an international or national program |
RegionId |
integer | Region ID | |
Region |
varchar | Region | |
ProgramId |
integer | Program ID | |
ProgramName |
varchar | Program | |
ChecklistItemId |
integer | ID of the ChecklistItem. This is helpful for sorting the ChecklistItem in order of how it appears on the census system | |
ChecklistItem |
varchar | Question asked in the accreditation application | |
ChecklistItemParentCategory |
varchar | Category that the ChecklistItem is included under, like a header for the section of questions | |
CheckListItemResponse |
varchar | Answer to the ChecklistItem question | |
ChecklistItemUpdatedDate |
datetime | Date that ChecklistItem was updated | |
IsDocRequired |
True, False | bit | If a document is required to upload for the particular ChecklistItem on the system. This is not always meaningful for the finance team, as often it's not required by the system even if it's required. |
ChecklistItemApprovalStatus |
Approve, Pending, Reject | varchar | Approval status of the specific ChecklistItem |
ChecklistItemApprovalCreatedDate |
datetime | Date that ChecklistItemApprovalStatus was chosen | |
Note |
varchar | Note on the ChecklistItem, if it exists | |
NoteCreatedDate |
datetime | Datetime that the Note was created | |
NotePersonName |
varchar | Name of the person who created Note | |
DocumentName |
Ex: document.pdf | varchar | Name of the uploaded document |
DocumentCreatedDate |
datetime | Date that DocumentName was uploaded | |
CreatedDateTime |
datetime | Created date time of the table in the pipeline | |
AssetId |
varchar | Asset ID of the pipeline |
ProgramQualityStandards
This table contains the formatted program quality standards information. Program quality standards is one of the documents that needs to be uploaded to the accreditation portal. This takes the files from the accreditation system, saves them to the pqs container on the data lake, and then formats them into a table here. This is used in the Program Quality Standards dashboard, which is used by LOD.
| colname | colvalues | coltype | detail |
|---|---|---|---|
Year |
integer | Year that the file was uploaded into the accreditation system. | |
ProgramId |
integer | Program ID | |
ProgramName |
varchar | Program | |
ProgramRaw |
varchar | Raw name of Program before data cleaning | |
RegionId |
integer | Region ID | |
Region |
varchar | Region | |
RegionRaw |
varchar | Raw name of Region before data cleaning | |
CompletedBy |
varchar | Person who put their name as completd by on the file | |
Date |
datetime | Date | |
CompletionProgress |
float | Completion progress listed on the file | |
StrategicPlanReference |
varchar | Strategic Plan Reference, this is a set list that LOD created. This is the larger section | |
AreaOfPQS |
varchar | Area of PQS, this is a set list that LOD created. This is a step under StrategicPlanReferences | |
StageOne |
varchar | Description of what stage 1 at this AreaOfPQS looks like. This is specified by LOD | |
StageTwo |
varchar | Description of what stage 2 at this AreaOfPQS looks like. This is specified by LOD | |
StageThree |
varchar | Description of what stage 3 at this AreaOfPQS looks like. This is specified by LOD | |
CurrentStage |
Havent reached Stage 1 yet, Stage 1, Stage 2, Stage 3, Stage 3+ | varchar | Current stage that the program is at for the AreaOfPQS |
TargetStage |
Havent reached Stage 1 yet, Stage 1, Stage 2, Stage 3, Stage 3+, Highesst stage reached, Not currently a priority | varchar | Target stage for the program for the AreaOfPQS |
HighPriority |
1 or null | integer | If the program lists this AreaOfPQS as high priorty |
StageComments |
varchar | Comments on the stage of AreaOfPQS | |
ProgressReport |
On track, Not on track, Slightly off-track, Complete | varchar | Progress report |
ProgressReportComments |
varchar | Comments on the ProgressReport | |
FileName |
varchar | Name of the file uploaded to the accreditation portal | |
OriginalForm |
varchar | If the AreaOfPQS was on the original form. If not, it was an additional one added by SONA | |
CurrentStageNumber |
0, 1, 2, 3, 4 | integer | Number of the CurrentStage for the AreaOfPQS |
TargetStageNumber |
-1, 0, 1, 2, 3, 4, 5 | integer | Number of the TargetStage for the AreaOfPQS |
ProgressReportNumber |
0, 1, 2, 3 | integer | Number of the ProgressReport |
StrategicPlanArea |
S1, S2, S3, E1, E2, E3, E4 | varchar | Strategic Plan Area. This is the highest level of category |
CreatedDateTime |
datetime | Created date time of the table in the pipeline | |
AssetId |
varchar | Asset ID of the pipeline |