Import Awards and Disbursements Data Dictionary
- Alex Georgiou
- Heather Smith
Data Dictionary
See example schema file on Import Awards and Disbursements Process
Column Name | Description | Required/ Optional | Type | Min Length | Max Length | Values | Schema Field Name |
Created Date | Date / Time stamp for the file. If an older file is imported (for example, if a file dated 4/1/14 is imported AFTER a file dated 4/15/14 was imported) the older file will NOT overwrite the existing data. The data in the older file will be ignored. | Required | Date/time |
|
| YYYY-MM-DDTHH:MM:ss (standard XML dateTime format) | createdDate |
Student Information | |||||||
External ID 1 | Student's External ID 1, if applicable. | Required | Text | 1 | 60 | Alpha - Numeric | externalStudentId1 |
External ID 2 | Student's External ID 2, if applicable. | Optional | Text | 1 | 60 | Alpha - Numeric | externalStudentId2 |
External ID 3 | Student's External ID 3, if applicable. | Optional | Text | 1 | 60 | Alpha - Numeric | externalStudentId3 |
External ID 4 | Student's External ID 4, if applicable. | Optional | Text | 1 | 60 | Alpha - Numeric | externalStudentId4 |
Award Information | |||||||
Internal Award ID | In order to update an award, the school must send either the Internal Award ID or the External Award ID in the XML file for matching purposes. If neither are provided, a new award record will be created. School would obtain this ID via a report, and add it to their updated import file, in order for us to match the award. Both the Internal Award ID and the External Fund ID must match in order to match and update a previously imported award. If internal award ID doesn't match any internal award IDs in the DB - throw a validation error.
| Optional | Integer |
|
|
| internalAwardId |
External Award ID | (In order to update an award, the school must send either the Internal Award ID or the External Award ID in the XML file for matching purposes. If neither are provided, a new record will create awards). | Required | Text |
|
|
| externalAwardId |
Financial Award ID | Used to match TitleIV funds imported via this process to data received on the COD rebuild files (DL Rebuild and TEACH Rebuild) | Optional | Text | 21 | 21 |
Expected to be in the format: {SSN}{loan type}{fay}{school code / OPEID}{sequence number}
| financialAwardId |
Pell ID | Used to match Title IV funds imported via this process to data received on the COD rebuild files (Pell Rebuild YTD)
| Optional | Text | 6 | 6 |
| pellId |
External Fund ID | Used for matching imported awards to funds in Regent Award fund setup. | Required | Text | 1 | 60 | Alpha - Numeric | externalFundId |
Award Status | The status of the award
| Required | Text |
|
|
| awardStatus |
Award Amount | The amount of the award | Required | Numeric |
|
| 0.00 to 999999.99 | awardAmount |
Manual Award | Indicates whether or not packaging should treat this as a "manual award." If the award is treated as a manual award, packaging will create the award, however, subsequent auto re-packaging will not be performed on this award. | Optional | Boolean |
|
| true/false (lowercase) Disbursements are only required when COD related awards are sent. | manualAward |
Award Start Date | Use the start and end date to match to the appropriate loan period and award period based on the fund scope. | Required | Date |
|
|
| awardStartDate |
Award End Date | Required | Date |
|
|
| awardEndDate | |
Federal Award Year | Federal Award Year associated to the award. | Required | CCYY |
|
| 2011 thru 2030 | federalAwardYear |
Offered Date | Date the award was offered.
For example: If the award was created with a status of ‘estimated’ on 6/1/2014 there will be no ‘offeredOnDate’. However, once the award status is changed to ‘Offered’ or ‘Accepted’ the ‘offeredOnDate’ will populate with the date the status was changed. | Required only if Perkins or SEOG | Date |
|
|
| offeredOnDate |
Max Disbursement Number | If provided, this value identifies the last disbursement number for that award. | Optional | Integer | 1 | Infinity |
| maxDisbursementNumber |
Disbursement Information | |||||||
The Disbursement node is optional; however, disbursements MUST be provided with a COD related award. There is a validation that checks whether or not the award is COD related. If it is COD related, disbursements are expected. If the node is not provided for a COD related award, the file will process, but the COD related award will not process. | |||||||
Internal Disbursement ID | In order to update a disbursement, the school must send either the Internal Disbursement ID or the External Disbursement ID in the XML file for matching purposes. If neither are provided, a new award record will be created The Internal Disbursement ID is our primary key for the disbursement. If this value is not provided, Regent Award will create this ID and use it for the Export Student Transaction (EST). Schools would obtain this ID via a report, and add it to their updated import file, in order for us to match the disbursement. Both the Internal Disbursement ID and the External Fund ID must match in order to match and update a previously imported disbursement. If internal disbursement ID doesn't match any internal disbursement IDs in the DB - throw a validation error. | Optional | Integer | 1 | 60 | minLength value="1" maxLength value="60" | internalDisbursementId |
External Disbursement ID | In order to update a disbursement, the school must send either the Internal Disbursement ID or the External Disbursement ID in the XML file for matching purposes. If neither are provided, a new award record will be created. If the Internal Disbursement ID is not provided, Regent Award will create one and use that value in the Export Student Transaction (EST) file. | Required | Text | 1 | 60 | This value must be unique across Institutions.
minLength value="1" maxLength value="60" | externalDisbursementId |
Disbursement Number | Disbursement Number | Optional | Integer | 1 | Infinity | Required for COD related funds, not used for any other fund type
minInclusive value="1" | number |
Gross Disbursement Amount |
| Required | Numeric |
|
| 0.00 to 999999.99 | grossDisbursementAmount |
Net Disbursement Amount |
| Optional | Numeric |
|
| 0.00 to 999999.99 | netDiscDisbursementAmount |
Fee Amount |
| Optional | Numeric |
|
| 0.00 to 999999.99 | feeAmount |
Paid Amount | Update-able only if this is a non-disbursable fund. If the fund is disbursable, the value can be provided on the create import; however, this field is not update-able for disbursable funds. NOTE: A zero amount should only be sent when a previous positive amount was provided and it is now being reduced to zero. | Required when the disbursement status is ‘Paid’. If the disbursement status does not equal ‘Paid’, then the ‘Paid Amount’ and ‘Paid Date’ must be excluded from the <disbursementInformation> node. | Numeric |
|
| 0.00 to 999999.99 | paidAmount |
Scheduled Date | The anticipated disbursement date. | Required | Text |
|
| YYYY-MM-DD (standard XML date format) | scheduledDate |
Paid Date | The date the disbursement was paid. | Required when the disbursement status is ‘Paid’. If the disbursement status does not equal ‘Paid’, then the ‘Paid Amount’ and ‘Paid Date’ must be excluded from the <disbursementInformation> node. | Text |
|
| YYYY-MM-DD (standard XML date format) | paidDate |
Disbursement Status | If the disbursement status equals ‘Paid’, then the ‘Paid Amount’ and ‘Paid Date’ are required fields. If the disbursement status is any status other than ‘Paid’, ‘Paid Amount’ and ‘Paid Date’ should be excluded from the node. | Required |
|
|
|
| disbursementStatus |
Payment Period Start Date | Used for matching a disbursement to the correct payment period. | Required | Date |
|
| YYYY-MM-DD (standard XML date format) | paymentPeriodStartDate |
Payment Period End Date | Required | Date |
|
| YYYY-MM-DD(standard XML date format) | paymentPeriodEndDate | |
External Term ID | If this is provided, it will be used first to match disbursements, before PP start and end dates. | Required for Term-based programs | Text | 1 | 60 | Alpha-numeric | externalTermId |
Additional Guidance
Topic | Additional Information |
|---|---|
Importing New Awards | The Import Award Process can be used for adding new awards to a student’s Academic Plan. When using the process for this scenario, awards can be imported without sending disbursement information. When using this method, Regent Award will package the awards based on the data provided in the award section of the import schema and use the configured disbursement schedules to setup the disbursements. The awards can be optionally set to auto-package (manualAward=false), and Regent Award will manage the award after import. |
Importing (Rebuilding) Historical Awards | When using the process to import historical awards, if possible, provide accurate payment period and disbursement dates for all paid and scheduled disbursements. Payment Period dates should be based on the Federal Award Year or the Academic Year depending on the type of fund (as defined in Fund Setup). Failure to provide accurate dates may result in disbursements not being associated to the correct period. See additional guidance about Payment Period dates below. See additional guidance about Payment Period dates below. Since non-COD funds will include disbursement data, these awards must be marked as manual (manualAward=True) as indicated in the spec above. |
Updating Awards | When updating existing awards, Award Period and Payment Period dates must match the dates that are in the student’s current Academic Plan. Schools can use the EST Process, reports, a data extract, or a web service integration to get the correct award and disbursement dates. The method should be decided as part of the implementation process. Since these awards will include disbursement data, they must be marked as manual (manualAward=True) as indicated in the spec above. |
Payment Period Dates | Payment Period start and end dates are required when sending disbursement information, however, it may not always be known what the correct payment period dates are for a given disbursement. When this is the case, these dates can be sent as the same date as the award period. A correction script can be run in this situation to use the disbursement scheduled date to match to an existing payment period on the student’s academic plan that Regent Award generates. |
Disbursement Dates | The scheduled date for a disbursement must be provided in the import regardless of the status of the disbursement. If the disbursement is paid, 2 additional fields must be provided, the disbursement paid amount and the disbursement paid date. |
Award Period Dates | For Federal Award Year (FAY)-based funds, use the standard FAY dates for full year award (July 1 to June 30) for the award period dates. The exception is when the award is less than a full year. In which case, send the award period date range so that it is contained in the Federal Award Year range. For Academic Year (AY)-based funds, use the academic year start and end dates or the loan period (if known) associated to the award for the award period dates. It is critical that the award period dates do not overlap multiple academic years, otherwise Regent Award will not be able to determine which FAY or AY to associate. |
Sending Data Updates | When sending data updates, all elements within an updated section must be sent—not just the data element being updated. For example, the following elements are sent in the Award Information section for a student: External Award ID, External Fund ID, Award Status, Award Start and End date, Federal Award Year, and Award Amount. If there is an update to the student’s Award Amount, all the existing data values for the section must be re-sent, along with the updated Award Amount. If this procedure is not followed and the file includes only the updated element, all the other fields will be updated to “blank.” If there are no updates to a section, that section can be left out of the updated Import Awards File, and there will be no changes to those fields in Regent Award. |
Cancelling Awards | To cancel the award amount via an Import Award file, Regent uses the ExternalAwardId/ExternalDisbursementId to match to the previously loaded awards/disbursements. Both the Award and Disbursement nodes should be provided in the import file, including all the data elements within the sections. Award Status should be updated to ‘Cancelled’ and the Award Amount updated to ‘$0’. For each disbursement, the Disbursement Status should be updated to ‘Cancelled’ and the Disbursement Amount should be updated to ‘$0’. Each row should be closed with the following: /> |