Planning for File Parser and External Award Processing
Use the File Parser utility to map external award data files and convert data to the External Award staging tables. The delivered External Award context definition defines the target External Award staging tables, fields, and mapping action. Use this section to determine how to create File Parser mapping definitions for external award processing.
This section discusses how to:
Use the context definition.
Use the file mapping definition.
Review the External Award staging tables.
The context definition identifies the target table that the File Parser populates with external data. A context definition identifies the hierarchy of the target record structure and fields available to be mapped and updated. Your Campus Solution database is provided with the context definition for the External Award process. The External Award context definition contains the External Award Stage records and fields, which are the target records for mapping any external data file for the External Award process. Do not change any record or fields defined in the External Award context definition unless you need to reduce the number of fields that are visible for mapping.
Use the File Mapping Definition component to map and define a data file to be converted to target staging tables. The key element in a File Mapping definition is the context definition to be referenced. After you identify the context definition, identify the external data file layout. The external fields are then mapped to the available target records and fields.
Use the File Definition tab to identify the context definition and general attributes of the data file to be processed. Use the External Award Load context definition for external files that contain data that you want to convert and update the external award staging tables. If your external file has multiple row types, then only identify the row types that have data that you want to convert.
Use the File Layout tab to identify the external file fields and format.
Use the Mapping tab to define how the target staging table and fields are updated when the external file is processed. For example, your external file probably contains a field comparable to the required SFA_EA_REPORT_CD field on the staging table record SFA_EASTAGE_DTL. On the Mapping tab for this field, set the Mapping Action field to Default Value and enter A=Append, R=Replace, or N=New. When the external file is processed, the SFA_EA_REPORT_CD field is then populated with that value.
The record layout and field definitions describe the External Award staging tables. The layout also identifies which fields are visible for mapping in the File Parser Mapping Definition component. The four records are:
SFA_EASTAGE_HDR: The header record, which contains the highest keys.
SFA_EASTAGE_DTL: The award details at an annual award level.
SFA_EASTAGE_DSB: Award disbursement details.
SFA_EASTAGE_CNT: Summary counter and amounts.
External Award Stage Header Record and Fields
This is the parent record to the SFA_EASTAGE_DTL and SFA_ESTAGE_DSB records.
Field Name |
Description |
Visible for Mapping |
Comment |
---|---|---|---|
SFA_EA_TRANS_NBR |
Transaction number |
Key assigned by the system. |
|
TRANSACTION_DATE |
Transaction date |
Assigned by the File Staging process. |
|
PROCESS_INSTANCE |
Process instance |
||
SFA_EA_TRANS_SRCE |
Transaction source |
Assigned by the system. 01 = Manual Entry, 02 = External Data file, 03 = Loan Certification file (not currently used), 04 = Student Financials Feed, 05 = Self Service. |
|
OPRID |
User ID |
Assigned by the File Staging process. |
|
SCCFP_MAP_ID |
File mapping ID |
Assigned by the system. |
|
FILENAME |
File name |
Assigned by the File Staging process. |
|
CPS_SCHOOL_CODE |
School code |
Yes |
Not currently used. Context Definition is Visible for Mapping. |
SFA_EA_STDNT_COUNT |
Transaction student count |
Field exists on the SFA_EASTAGE_CNT record. If mapped, the field is used to capture source transaction student count. |
|
SFA_EA_AWARD_TOTAL |
Transaction award total |
Field exists on the SFA_EASTAGE_CNT record. If mapped, the field is used to capture source transaction award total. |
|
SFA_EA_RSLT_COUNT |
Actual count |
Number of row counts in SFA_EASTAGE_DTL when source is external file. |
|
SFA_EA_RSLT_TOTAL |
Actual total |
Sum of awards in SFA_EASTAGE_DT, when source is external file. |
|
SFA_EA_FILE_STATUS |
File read status |
Assigned by the File Staging process. |
|
PKG_STATUS_CD |
Packaging status |
Error number. Assigned by File Staging process. |
External Award Stage Detail Record and Fields
This is the child record to the SFA_EASTAGE_HDR record.
Field Name |
Description |
Visible for Mapping |
Comment |
---|---|---|---|
SFA_EA_TRANS_NBR |
Transaction number |
Key assigned by the system |
|
SFA_EA_SEQNO |
Sequence number |
Key assigned by the system |
|
NATIONAL_ID |
National ID |
Yes |
Available as a Search/Match element. If EMPLID is provided, the Search/Match routine is bypassed. |
BIRTHDATE |
Date of birth |
Yes |
Available as a Search/Match element. If EMPLID is provided, the Search/Match routine is bypassed. |
LAST_NAME |
Last name |
Yes |
Available as a Search/Match element. If EMPLID is provided, the Search/Match routine is bypassed. |
FIRST_NAME |
First name |
Yes |
Available as a Search/Match element. If EMPLID is provided, the Search/Match routine is bypassed |
MIDDLE_NAME |
Middle name |
Yes |
Available as a Search/Match element. If EMPLID is provided, the Search/Match routine is bypassed. |
EMPLID |
Employee ID |
Yes |
If EMPLID is provided, then Search/Match parameters are bypassed during the External Award Load process. |
INSTITUTION |
Academic Institution |
||
AID_YEAR |
Aid Year |
||
ACAD_CAREER |
Academic Career |
Yes |
(Optional) The External Award Load process uses an active career, as determined from the FA Term record for the student ID. If the student has multiple careers within one aid year, the External Award Load process errors out with a message indicating that the student has more than one active career. If you are running the External Award Load process from batch, you can allow the External Award Load process to post an external award transaction to the first active career found. |
SFA_EA_PROC_STAT |
Processing Status |
The processing status of the External Award transaction:
|
|
SFA_EA_PROC_DTTM |
Date/Time Processed |
||
SFA_EA_EA_TYPE |
External Award Type |
Yes |
(Optional) If no financial aid item type is provided, then external award attributes of Type, Source, or Program code are reviewed against External Award Cross-Reference item type setup. |
SFA_EA_SOURCE |
External Award Source |
Yes |
(Optional) If no financial aid item type is provided, then external award attributes of Type, Source, or Program code are reviewed against External Award Cross-Reference item Type setup. |
SFA_EA_PROGRAM_CD |
External Award Program Code |
Yes |
(Optional) |
SFA_EA_LOAN_CERT |
Loan Certification |
Yes |
(Optional) Indicates external award data is based on a Loan Certification Request. If yes, then a corresponding Related Item Type Group or Item Type value must be identified. If yes, Reporting Code becomes New by default. |
SFA_EA_REPORT_CD |
Reporting Code |
Yes |
(Required) Determines how transaction data is posted to a student's award package:
|
ITEM_TYPE |
Item Type |
Yes |
If this field is blank, the External Award Load process uses the External Award Item Type Cross Reference setup to determine the item type to be added or updated. |
REL_ITEM_TYPE |
Related Item Group |
Yes |
Available only when Loan Certification equals Yes. This value is intended to determine Stafford eligibility. The Related Item Type group should identify both Subsidized and Unsubsidized Stafford item types. |
DESCR |
Description |
Yes |
(Optional) Enter a 30-character award description. The description is displayed in Self Service. |
DISBURSEMENT_PLAN |
Disbursement Plan |
Yes |
(Optional) If provided, the disbursement plan (DP) is used. If the disbursement plan is not provided, the default DP from the item type's career default DP/Split Code (SC) is used. If no default DP/SC is defined for career, a DP/SC is used from the student's term enrollment. If a DP/SC is not found, External Award Load errors out the transaction. For Replace/Append transactions, this must be the same value used on the posted (noncanceled) award. A Split code does not need to be provided if a Disbursement Plan code exists. Providing only a Disbursement Plan code allows the External Award Load process to establish the term disbursement structure and define the specific distribution based on amounts provided in transaction. |
SPLIT_CODE |
Split Code |
Yes |
(Optional) If provided, this split code is used to determine the disbursement split formula. Split code is set to XX if disbursement data is provided on the External Award transaction. |
NET_AWARD_AMT |
Net Award Amount |
Yes |
(Optional) The annual amount to be posted. If scheduled disbursement amounts are provided, the cumulative scheduled disbursement amounts must equal the net award amount. If this field is blank, and scheduled disbursement detail is provided, then the net award amount is assigned the sum of scheduled disbursement amounts. |
DISBURSED_AMOUNT |
Disbursed Amount |
Yes |
(Optional) The total disbursed amount to be posted. If disbursement detail amounts are provided, the cumulative amounts must equal the disbursed amount. If this field is blank and disbursement detail is provided, then disbursed amount is assigned the sum of the disbursement detail amounts. Note: The disbursed amount on the posted award is updated only when the disburse method for the item type is No. |
SFA_EA_PAY_STATUS |
Paid Status |
Yes |
(Optional) Indicates that the annual amount has been fully paid to the student. If you select Yes, the annual disbursed amount is equal to the net award amount. Note: The disbursed amount on the posted award is updated only when the Disburse method for the item type is No. |
SFA_EA_ADJ_REASON |
Adjustment Reason |
Yes |
(Optional) Use this 3-character field to capture additional information. Any data mapped to this field is visible when you are viewing external award transaction data. |
SFA_EA_INELIGIBLE |
Ineligibility Reason Code |
Yes |
(Optional) Use this 3-character field to capture additional information. Any data mapped to this field is visible when you are viewing external award transaction data. |
SFA_EA_SUSPEND_RSN |
Suspense Reason |
1-character suspense code. Currently not being used. |
|
SFA_EA_LOAD_ERROR |
Load Error Code |
External Award Load Error Message codes are maintained in the Message Catalog/Message Set Number = 14402/Message Set range 600s. |
|
SFA_EA_SS_VISIBLE |
Self Service Visible Code |
Yes/No. This field is available on the Award Detail component for each external award transaction. Select Yes to show the DESCRLONG value as award detail in Self Service. |
|
FIN_AID_TYPE |
Financial Aid Type |
Used by External Award Load process to evaluate award posting. |
|
DESCRLONG |
Description |
Used with the SFA_EA_SS_VISIBLE field. If the Visible field is selected, descriptions entered are available for the student to view in Self Service, Reporting Outside Aid-Award Details. |
External Award Stage Disbursement Record and Fields
This is the child record to the SFA_EASTAGE_DTL record.
Field |
Description |
Visible for Mapping |
Comment |
---|---|---|---|
SFA_EA_TRANS_NBR |
Transaction number |
A key assigned by the system. |
|
SFA_EA_SEQNO |
Sequence number |
A key assigned by the system. |
|
SFA_EA_DISB_SEQNO |
Disbursement Sequence |
Yes. |
(Required) A key assigned by system. It represents each unique disbursement detail for the transaction. If you map several disbursement detail rows from an external file, when you define the mapping, set Mapping Action to become a 2-digit numeric value by default. This ensures that the key is created when the external file is processed. |
STRM |
Term |
Yes |
(Optional) If provided, the External Award Load process determines whether it is valid with the student's enrollment. If a disbursement ID is not provided, then the first disbursement ID for STRM is updated. |
DISBURSEMENT_ID |
Disbursement ID |
Yes |
(Optional) Use to append the disbursement amount to an existing award. |
SCHEDULED_AWARD |
Scheduled Award |
Yes |
The scheduled or allocated disbursement amount at the disbursement sequence level. |
DISBURSED_AMOUNT |
Disbursed Amount |
Yes |
The disbursed or paid amount at the disbursement sequence level. Note: The disbursed amount on the posted award is updated only when the Disburse method for the item type is No. |
SFA_EA_PAY_STATUS |
Paid Status |
Yes |
(Optional) Identify the paid status at the disbursement sequence level. If you select Yes, the disbursement sequence amount is equal to the resulting scheduled award amount. Note: The disbursed amount on the posted award is updated only when the Disburse method for the item type is No. |
External Award Stage Counter Record and Fields
This is the child record to the SFA_EASTAGE_HDR record.
Field Name |
Description |
Visible for Mapping |
Comment |
---|---|---|---|
SFA_EA_TRANS_NBR |
Transaction number |
A key assigned by the system. |
|
SFA_EA_STDNT_COUNT |
Yes |
If mapped, this field is used to capture source transaction student count, but no processing logic is associated with the field. |
|
SFA_EA_AWARD_TOTAL |
Yes |
If mapped, this field is used to capture source transaction award total, but no processing logic is associated with the field. |