Importing UCAS and Teacher Training Applications and Applicant Data
This section provides an overview of importing applications and applicant data into your database, and discusses how to:
View the transaction setup.
Define the search/match criteria.
Enter the applicant import parameters.
Import new applicants.
Import HESA data.
Review and edit the suspense data.
View, update, and delete application staging data.
Define a time stamp for reimporting data.
When you import applicant data, the system creates new person records in your database or updates details about existing person records. First, you must ensure that you aren't going to create any duplicate data by setting up the search/match parameters. Then, you run the Import Applicant Data process to load the application and applicant data into your database. The search/match process determines if the person might already exist in your database. The purpose of the search/match is to prevent duplicate records. You can specify what you want the post process to do when search/match finds a match or multiple matches. It is recommended that you set the status as Suspend when you set up the search/match criteria for multiple matches. Based on the results of the search/match process, the import process can update the record in your database that matches the imported data if you are sure that the person in your database is the same person to whom the imported data relates.
During authentication for UCAS, if Use XML for Applicants and Import Live are both selected, a request is sent that checks that the combination of user ID and password is correct. If the check fails, the processing that loads data to the staging records is skipped and an error message is logged. If the check passes, the processing moves on to the individual calls to request applicant data and load data to the staging records. If there's a UCAS decryption profile ID defined on the SA Features page, the stored encrypted password is decrypted for use with each request. Otherwise, the unencrypted password is used.
The Import Applicant Data process allows data to be imported from:
UCAS iv views (ODBC) or applicant methods (XML), if you selected UCAS on the UCAS Configuration page
Apply applications, if you selected Use Apply.
If Import Live is selected, the system imports data into Campus Solutions tables through the staging tables based on the import options selected for UCAS and Apply. You can select Import Live only if you selected it in UCAS Configuration.
If Personal ID and Application Code are specified, all application records for the applicant are imported irrespective of time stamp.
If Personal ID and Application Code aren't selected, records are imported to the staging tables based on the time stamp records for each system. For UCAS, internal time stamp records for each view/method are considered. For Apply, a single internal time stamp record is considered.
If there is an internal time stamp record, records with a higher time stamp than the internal time stamp value (set on the previous run of the import) are imported unless Override time stamp is defined, in which case records with a higher time stamp than that value are imported.
If there is no internal time stamp record, for UCAS all records are imported. For Apply, records updated since 2020-01-01T00:00:00Z are imported. At the end of the import, the internal time stamp records are updated to reflect the highest imported time stamp value.
For XML-link processing, if an Override time stamp isn't defined, the internal time stamp value plus one second is used for the lastUpdated request parameter.
If Import Live isn't selected, new records aren't imported and existing staging records are re-processed based on the import options selected for UCAS and Apply.
The Import Applicant Data process imports data from the ivContextual view or Applicant Contextual Data only if the Contextual check box is selected and Cycle is 2012 or later on the UCAS Configuration page.
Also, the process imports data from the Unicode version of views/methods only if the Unicode check box is selected on the UCAS Configuration page. For example, if you have selected the Unicode check box, the process imports data using the Unicode methods, such as getUCASApplicantsDetails_U and getUCASApplicantsEmployment_U. Conversely, if you haven't selected the Unicode check box, the process imports data using the non-Unicode methods, such as getUCASApplicantsDetails and getUCASApplicantsEmployment.
You can view, update, or delete the suspended UCAS and Apply application data in the staging tables using the Staging Data pages. You can search for the suspended data based on various search parameters including import status, application code and personal ID.
The import process creates a corresponding Campus Solutions application record for each imported application choice. If an applicant has more than one ChoiceNo for an AppCode at your institution, the import process creates a separate Campus Solutions application record for each AppCode and ChoiceNo combination. You can use the Maintain Applications component to access the Campus Solutions application record. Each application record will have an effective date that indicates the date when you imported the application. If you import updates to the same application on different days, the system creates new application records with the different effective dates. If you import updates to the same application multiple times on a particular day, the system updates the Campus Solutions application record for that particular date.
The import process also creates (or updates) the Campus Solutions applications for imported Apply applications where Accredited Institution is selected in the institution reference data record for an institution code. The process also creates an application choice record in Campus Solutions for the imported application choice. You can use the UCAS Applications or Apply Applications components to view the imported application choice. For more information about these components, see Reviewing Imported UCAS and Apply Applications and Applicant Data.
Suppose you are importing a UCAS choice and its decision value already exists in UCAS. For such a choice, when you run the import process for the first time, the process creates the Campus Solutions application and populates the Program Action and Action Reason of the Application Program Data record based on the following mappings set up on the program action page of the UCAS Configuration component:
UCAS processing:
If Decision = I, use action/reason for Interview
If Decision = F, use action/reason for Course Full
If Decision = R, use action/reason for Rejection
If Decision = W, use action/reason for Withdrawal
If Decision = C or D and Reply = F, use action/reason for Conditional Firm
If Decision = C or D and Reply = I, use action/reason for Conditional Insurance
If Decision = C or D and Reply = D, use action/reason for Conditional Decline
If Decision = C or D and Reply = blank, use action/reason for Conditional Offer
If Decision = U and Reply = F, use action/reason for Unconditional Firm
If Decision = U and Reply = I, use action/reason for Unconditional Insurance
If Decision = U and Reply = D, use action/reason for Unconditional Decline
If Decision = U and Reply = blank, use action/reason for Unconditional Offer
If Decision and Reply are blank (i.e. most new applications) or any other combination, then set Program Action = APPL, Action Reason = default value or blank
Else set program action = APPL and reason = default value (if defined) from the UCAS Application Import page of the UCAS Configuration component.
Processing updates ensure that for applications where a decision has been made but a reply hasn't yet been received, if a new row is added for a change of course/campus, term or entry point, then a second new row is added to ensure that the most recent row reflects the decision value.
A new row is added based on the Decision value of the choice if during import these conditions are satisfied:
A new row is added for any of the following: Changed Course/Campus, Changed Term, Changed Entry Point.
The new Decision value isn't blank.
Both the new and existing Reply values are blank (no subsequent Reply Change row will be added).
For example, if the Decision = C, then the program action and reason for Conditional Offer are used for the new row.
For applications previously rejected or declined by default and then reinstated, processing updates add a row to reflect the decision and reply of the choice.
If the new Action value is blank and the existing Action for the choice is D or R, the Campus Solutions application is updated based on the new decision/reply values. For example, if the Decision = C and Reply = F, the program action and reason for Conditional Firm are used for the new row.
When choices are initially imported for UCAS, if there are existing values for Decision or Reply, then the program action/reason of the newly created application is set to the appropriate combination. However, there is a limitation on which program action values can be used when a Campus Solutions application is initially created. For the first effective-dated record, only these program actions are valid: ADMT (Admit), DENY (Deny/Reject), RAPP (Re-admit), RECN (Re-consideration), WAPP (Applicant Withdrawal) or WADM (Administrative Withdrawal). When these program actions are used to create the initial record, the application is created with that program action. Otherwise, the application is created using APPL program action and the default action reason (if available) or blank. If the mapped program action is a value other than ADMT, APPL, DEIN, DENY, RECN, WADM or WAPP, then a second row is added to the application record to reflect the decision/reply mapping.
The Import Applicant Data process uses the Nationality page mapping to import the passport field values and create the citizenship and passport records. If the nationality value in an imported record doesn't have a corresponding country code mapping, then the process doesn't create a citizenship and passport record. The process logs an error message and the record is retained in the staging tables.
Suppose you have mapped a nationality with a country code but didn't map the same nationality with a citizenship status. In this case, if a citizenship record doesn't exist in Campus Solutions for the imported record, the process creates a new blank citizenship record. This means the process enters only the Country value and leaves the Citizenship Status value as blank on the Citizenship Detail page (Add/Update a Person component).
Suppose you have mapped a nationality 610 with a country code CHN and citizenship status 1 (Citizen). The citizenship record for a person named Eric with the same nationality exists in Campus Solutions with a different citizenship status 2 (Naturalized). In this case, when the process again imports Eric's record that has a nationality of 610, it updates Eric's citizenship record in Campus Solutions with the mapped citizenship status 1.
For Dual Nationality, a similar process is used to create or update citizenship records.
The Import Applicant Data process processes an incoming passport record if you have mapped the Nationality and Country values and if UCAS has provided a passport number. The process creates or updates a passport record for the applicant in the following ways:
If the applicant's passport record with the Country and Passport Number combination exists in Campus Solutions, then the process updates that record with the incoming passport issue date, expiry date and place issued values.
If the applicant's passport record with the Country and Passport Number combination does not exist in Campus Solutions, then the process creates a new record using the incoming values.
The import process uses the Forenames and Surname values to create or update a Primary and Preferred Name record in Campus Solutions. However, if Preferred First Name value exists, then the import process uses this value to create or update the Preferred Name record for the applicant. A Preferred First Name isn't supplied in Apply, so the preferred name record is created using the same values in Forenames in the primary name record.
If the LastEducation value exists and the school code is mapped to a Campus Solutions External Org in the imported School reference data, the process populates this mapped External Org value in the Application School/Recruiting page for the Campus Solutions application record. If the LastEducation value doesn't exist, but the School value exists and the school code is mapped to a Campus Solutions External Org in the imported School reference data, the process populates this mapped External Org value in the Application School/Recruiting page.
|
Page Name |
Definition Name |
Navigation |
Usage |
|---|---|---|---|
|
Transaction Setup |
SCC_TRANSAC_DTLS |
|
View the delivered UCAS and Apply transactions that you can use in the Transaction Search/Match Setup page. Don't modify any values for these delivered transactions or create new ones. |
|
Search/Match Setup |
SCC_SEARCH_PARMS |
|
Define search and match parameters for the delivered UCAS transaction, which specify what UCAS data the system will append to existing records in your database and which UCAS data the system will suspend or ignore. |
|
Import Applicant Data |
SAD_UC_RUN_CNTL |
|
Import the UCAS and Apply applicant data into your system. Before you import the data, set up the search/match parameters on the Search/Match Processing pages. |
|
Import Applicant Log |
SAD_UC_IMPAPXML_CP |
|
View requests for the importing of applicant data for a particular process instance. You'll see each call and the corresponding response. |
|
Transaction Data |
SCC_TRANSAC_DATA |
|
After you run the Import Applicant Data process, use this page to review the status of a UCAS record with regard to loading, searching/matching, and posting processing. For example, determine if a record is waiting to be processed or if a record caused a new person to be added to your database. |
|
UCAS School Contextual Staging
|
SAD_UC_I_IVCONTXT |
|
View, update, or delete the suspended School Contextual data stored in the staging table. |
|
UCAS Contextual Staging
|
SAD_UC_IVCONTEXT |
|
View, update or delete applicant Contextual (MCDS) data stored in the staging table. |
|
UCAS Education Staging |
SAD_UC_I_EDUCAT |
|
View, update, or delete the suspended Applicant Education data stored in the staging tables. |
|
UCAS Employment Staging |
SAD_UC_I_IVEMPL |
|
View, update, or delete the suspended Applicant Employment data stored in the staging tables. |
|
UCAS Qualifications Staging |
SAD_UC_I_IVFMQ |
|
View, update, or delete the suspended Applicant Qualification data stored in the staging tables. |
|
UCAS Choice Offer Staging |
SAD_UC_I_OFFER |
|
View, update, or delete the suspended Choice Offer data stored in the staging tables. |
|
UCAS HE Activities Staging |
SAD_UC_I_IVPREP |
|
View, update, or delete the suspended Applicant HE Activities data stored in the staging tables. |
|
UCAS Results Match Staging |
SAD_UC_I_IVQUAL |
|
View, update, or delete the suspended Results Match Status data stored in the staging tables. |
|
UCAS Reference Staging |
SAD_UC_I_IVREF |
|
View, update, or delete the suspended Applicant Referee data stored in the staging tables. |
|
UCAS Contact Details Staging |
SAD_UC_I_STARA |
|
View, update, or delete the suspended Applicant Detail data stored in the staging tables. |
|
UCAS Choices Staging |
SAD_UC_I_IVSTRC |
|
View, update, or delete the suspended Applicant Choice data stored in the staging tables. |
|
UCAS Background Staging |
SAD_UC_I_IVSTH |
|
View, update, or delete the suspended Applicant Background data stored in the staging tables. |
|
UCAS HESA Staging |
SAD_UC_I_IVSTJ |
|
View, update, or delete the suspended HESA data stored in the staging tables. |
|
UCAS Biographical Staging |
SAD_UC_I_IVSTK |
|
View, update, or delete the suspended Applicant Detail data stored in the staging tables. |
|
UCAS Applicant Staging |
SAD_UC_I_STARN |
|
View, update, or delete the suspended Applicant Detail data stored in the staging tables. |
|
UCAS Exam Results Staging |
SAD_UC_I_IVPQR |
|
View, update, or delete the suspended Applicant Result data stored in the staging tables. |
|
UCAS Cancelled Applicant Staging |
SAD_UC_I_STARW |
|
View, update, or delete the suspended Applicant Cancelled data stored in the staging tables. |
|
UCAS StarX Staging Data |
SAD_UC_I_IVSTX |
|
View, update, or delete the suspended ivStarX data stored in the staging tables. This staging data isn't used in XML-link processing. |
|
UCAS Clearing Choices Staging |
SAD_UC_I_STARZ1 |
|
View, update, or delete the suspended Clearing Choice data stored in the staging tables. |
|
UCAS Statement Staging |
SAD_UC_I_IVSTAT |
|
View, update, or delete the suspended Applicant Personal Statement data stored in the staging tables. |
|
UCAS Unit Results Staging |
SAD_UC_I_IVUNRS |
|
View, update, or delete the suspended Applicant Units data stored in the staging tables. |
|
UCAS Self Release Staging |
SAD_UC_I_IVASRR_CM |
|
View, update, or delete the suspended Self Released Applicants data stored in the staging tables. |
|
UCAS Widening Participation Staging |
SAD_UC_I_WPDATA |
|
View, update, or delete the Applicant Widening Participation data stored in the staging tables. |
|
Applicant Staging |
SAD_UC_I_GSTARN |
|
View, update, or delete the suspended Apply candidate data stored in the staging tables. |
|
Biographical Staging |
SAD_UC_I_IVGSTK |
|
View, update, or delete the suspended Apply application data stored in the staging tables. |
|
Choices Staging |
SAD_UC_I_IVSTRG |
|
View, update, or delete the suspended Apply application data stored in the staging tables. |
|
Contact Details Staging |
SAD_UC_I_GSTARA |
|
View, update, or delete the suspended Apply contact details data stored in the staging tables. |
|
Offer Conditions Staging |
SAD_UC_I_ACOND_PG |
|
View, create, update, or delete data stored in the staging tables for any conditions associated with an Apply offer. This record allows multiple conditions per choice. |
|
Qualifications Staging |
SAD_UC_I_AQUAL_PG |
|
View, create, or update Apply qualifications data stored in the staging tables. |
|
References Staging |
SAD_UC_I_IVGREF |
|
View, update, or delete the suspended Apply reference data stored in the staging tables. |
|
Statement Staging |
SAD_UC_I_IVGSTA |
|
View, update, or delete the suspended Apply personal statement data stored in the staging tables. |
|
Work Experience Staging |
SAD_UC_I_IVGEXP |
|
View, update, or delete the suspended Apply work experience data stored in the staging tables. |
|
Internal Timestamp |
SAD_UC_TIMESTMP |
|
For an institution, reset a time stamp to a previous date if you want to re-import records. See the highest time stamp when the view was last imported. Additionally, see the number of records that were added, deleted, or amended when the view was last imported. |
Access the Transaction Setup page ().
This example illustrates the fields and controls on the Transaction Setup page (1 of 2).
This example illustrates the fields and controls on the Transaction Setup page (2 of 2).
The system uses the delivered UCAS and Apply transactions to process the incoming UCAS and Apply data. Don't change any values for this transaction.
Access the Search/Match Setup page ().
This example illustrates the fields and controls on the Search/Match Setup page. You can find definitions for the fields and controls later on this page.
The system uses this delivered UCAS and Apply transactions to process the incoming UCAS and Apply data. Therefore, you will need to define search match parameters for the UCAS and Apply transactions.
Use the Search Parameter field to select the group of predefined search parameters that you want to use for the Import Applicant Data process.
Use the Search Result Field field to select a search result value that you defined in the Search Result page.
It is recommended that you change the Number of Characters of FirstNameSrchRule and LastNameSrchRule to 7 for PSCS_10, PSCS_30, PSCS_40 and PSCS_50 in the Search/Match Rules page. Select to navigate to the Search/Match Rules page.
For information about setting up search/match rules, search parameters and results, see Understanding Search/Match and Setting Up Search/Match
Note: You must define at least one Suffix value in the Name Suffix table ().
Match(es) Found
This group box contains one row for each search/match criteria order defined by your institution. Define search/match orders on the Search/Match Criteria page.
Field or Control |
Description |
|---|---|
Search Order Nbr (search order number) |
For each order number, select what you want to do with the record if the search/match/post process discovers one or more matching records. |
One Match and Multiple Matches |
Select whether you want to add, update, suspend, or ignore matching records: Add: Add a new record to your database using the staging table record. Update: Update the existing record with the data in the staging table record. Suspend: Keep the suspense record back in the staging table. Determine manually whether or not this record matches a record in your database. Ignore: Ignore the staging table record that matched a record in your database. The process marks the record to be purged. |
No Match Found
Select one of the following options to specify what the search/match/post process should do when it doesn't find a matching record in your database:
Add: Add the unmatched record, including personal data, to your database.
Suspend: Keep the unmatched record in the staging tables to be looked at manually.
Ignore: Ignore the unmatched record . The process marks the record to be purged.
Access the Import Applicant Data page ().
This example illustrates the fields and controls on the Import Applicant Data page. You can find definitions for the fields and controls later on this page.
Field or Control |
Description |
|---|---|
Academic Institution |
Enter the academic institution for which you want to import data. |
Import Live |
Select if you want the import process to load the data into the staging tables and then into the Campus Solutions tables. If you don't select this check box, the import process loads the data from the staging tables into the Campus Solutions tables. By default, the system selects this check box if the Import Live check box on the UCAS Configuration page is selected. |
Import Applicants |
Indicates whether the process imports applicant data from UCAS or Apply. Either one of these options can be selected, but not both.
See Preparing for UCAS and Teacher Training Data Processing. |
Import Biographical Data |
Select the UCAS check box if you want to import application biographical data from the following UCAS views/XML objects:
The processing of staging records also includes any applicant contextual data (MCDS) that has been loaded to the SAD_UC_I_CONTEX staging record using File Parser |
Import Examination Results |
Select the UCAS check box if you want to import data from ivStarPQR and ivUnitResults views, or ApplicantResult and Applicant Units methods. |
Import Application Choices |
Select the UCAS check box if you want to import application choices from these UCAS views/XML objects:
|
Import HESA Data |
Select the UCAS check box if you want to import the ivStarj view or the UCASHesaData object. |
Personal ID and Application Code |
Specify the UCAS personal ID and application code, or the Apply candidate ID and application ID if you want to re-import all the records of a particular applicant (that has previously been incorrectly matched and then deleted). If you specify the personal ID and application code here, then during the selection from applicant views/XML objects, the import process will select only records for that applicant, irrespective of the Earliest Application Year that you configured and time stamp values. If you leave these two fields blank, the process selects records for all applicants based on the time stamp in the views and updates the internal time stamp with the highest imported time stamp value. For more information on the internal time stamp settings: Defining a Timestamp for Reimporting Data The Personal ID and Application Code fields are available only when you select the Import Live check box on this page. Note: By using the Personal ID and Application Code fields, you can specify only one applicant for a single run of the Import Applicant Data process and you need to ensure that the correct UCAS or Apply import check boxes are selected on this page. Also, note that if you specify a personal ID, then the application code is required. Application code is expanded to a 10–digit value to allow a 10–digit Apply application ID. |
If you have installed CRM for Higher Education, you can run an EIP batch replay process following the main Import Applicant Data process. The following example shows the options available on the Process Scheduler Request page when CRM for HE is installed.
This example illustrates the fields and controls on the Process Scheduler Request page for the Import Applicant Data process. You can find definitions for the fields and controls later on this page.
If you select the Batch Replay UCAS option, the Import Applicant Data process (SAD_UC_IMPAP) runs first and then the EIP Batch Replay (SCC_BAT_RPLY) runs. If you select the SAD_UC_IMPAP option, only the Import Applicant Data process runs.
For more information about the EIP Batch Replay process see CRM for Higher Education Developer's Guide.
To process the applicant staging records, the Import Applicant Data process performs the following initial processing steps to determine if the applicant already exists in the system. The steps are followed until an EMPLID is found. If an EMPLID isn't found, then the Import Applicant Data process invokes the Constituent Transaction Management (CTM) search/match processing.
Check if there is a matching applicant record for the local institution in SAD_UC_APPLREC. This check is made by matching the applicant's Institution, Personal ID and App Code.
For Apply, Personal ID is populated with candidate ID and App Code with application ID.
Check if there is a matching applicant record for another institution in SAD_UC_APPLREC. This check is made by matching the applicant's Personal ID and App Code.
Check if there is a matching applicant record from previous cycle or other system (UCAS or UTT) in SAD_UC_APPLREC. This check is made by matching only the Personal ID of either:
Applicant record for the same system for a previous cycle with a different App Code.
Applicant record for the other system (that's, UTT for UCAS applicants and UCAS for UTT applicants).
Check the External System ID of the same system. This check is made by matching Personal ID to External System ID of the same system, that's, UC for UCAS applicants.
Check the External System ID of other system. This check is made by matching Personal ID to External System ID of the other system, that's, GT for UCAS applicants.
Check the Legacy External System ID. This check is made by matching Personal ID to the UP External System ID. This check is for legacy records that your institution may have imported using a localisation provided by a vendor.
For Apply applications, the matching is restricted to only existing Apply applications. Matching on external system ID isn’t relevant to Apply applications. Before records are added to SAD_UC_APPLREC, the values in the new row are compared to the previous row (if they exist) to determine whether updates are required. For new applicants that aren’t matched to an existing applicant or person, constituent staging names, address, phone, email, NID, and citizenship records are created and the core records are created or updated via CTM.
Mapping Applicant Data
The following tables show the mappings between the UCAS applicant views and XML objects and the various staging and base records in Campus Solutions.
Import Applicants
odbc-link View
XML-Link Object
Staging Record
Record
ivStarN
UCASApplicantDetail
SAD_UC_I_STARN
SAD_UC_APPLREC
ivStarA
UCASApplicantDetail
SAD_UC_I_STARA
SAD_UC_APPLREC
ivStarK
UCASApplicantDetail, ApplicantAnswer (for NMCPin)
SAD_UC_I_IVSTK
SAD_UC_IVSTK
Import Biographic Data
odbc-link View
XML-Link Object
Staging Record
Record
ivContextual
ApplicantContextualData
SAD_UC_I_IVCNTX
SAD_UC_IVCONTXT
ivEducation
ApplicantEducation
SAD_UC_I_EDUCAT
SAD_UC_EDUCAT
ivEmployment
ApplicantEmployment
SAD_UC_I_IVEMPL
SAD_UC_IVEMPL
ivFormQuals
ApplicantQualification
SAD_UC_I_IVFMQ
SAD_UC_FRMQUAL
ivQualification
ResultsMatchStatus
SAD_UC_I_IVQUAL
SAD_UC_IVQUAL
ivPrepActivities
ApplicantHigherEducationActivities
SAD_UC_I_IVPREP
SAD_UC_IVPREP
ivStarH
UCASApplicantBackground
SAD_UC_I_IVSTH
SAD_UC_IVSTH
N/A
ApplicantCaringResponsibilities
ApplicantEstrangedFromParents
ApplicantFreeSchoolMeals
ApplicantLivingWithCondition
ApplicantParentCarerInArmedForces
ApplicantParentingResponsibilities
ApplicantRefugeeAsylumSeekerStatus
ApplicantServedInArmedForces
SAD_UC_I_WPDATA
SAD_UC_WPDATA
N/AN/A
SAD_UC_I_CONTEX
SAD_UC_I_CONTEXT
ivStarX
N/A
SAD_UC_I_IVSTX
SAD_UC_IVSTX
Import Choices
odbc-link View
XML-Link Object
Staging Record
Record
ivAppSelfReleasedReport
SelfReleasedApplicants
SAD_UC_I_IVASRR
SAD_UC_IVASRR
ivStarZ1
UCASApplicantChoice (for clearing choices)
SAD_UC_I_STARZ1
SAD_UC_IVSTARC
ivStarC
UCASApplicantChoice
SAD_UC_I_IVSTRC
SAD_UC_IVSTARC
SAD_UC_OTH_CH
ivOffer
UCASApplicantChoice (for conditions)
SAD_UC_I_OFFER
SAD_UC_OFFER
SAD_UC_OTH_OFF
ivReference
UCASApplicantReferee
SAD_UC_I_IVREF
SAD_UC_REFERENC
ivStatement
ApplicantPersonalStatement
SAD_UC_I_IVSTAT
SAD_UC_STATEMNT
ivStarW
UCASApplicantCancelled
SAD_UC_I_STARW
SAD_UC_STARW
Import Exam Results
odbc-link View
XML-link Object
Staging Record
Record
ivStarPQR
ApplicantResult
SAD_UC_I_IVPQR
SAD_UC_FRMQUAL
ivUnitResults
ApplicantUnits
SAD_UC_I_IVUNRS
SAD_UC_IVUNRS
Import HESA Data
odbc-link View
XML-Link Object
Staging Record
Record
ivStarJ
UCASHesaData
SAD_UC_I_IVSTJ
SCC_HE_PERSON
SCC_HE_QUAL_ENT
SCC_HE_INST_FLD
SAD_UC_POCC2010
ivStarJ
UCASHesaData
SAD_UC_I_IVSTJQ
SCC_HE_QUAL_ENT
Mapping Apply Applicant Data
The following tables show the mappings between the Apply applicant views and the various staging and base records in Campus Solutions.
The records marked with a single asterisk are shared with UCAS.
|
Data |
Staging Record |
Record |
|---|---|---|
|
Candidate |
*SAD_UC_I_STARN |
*SAD_UC_APPLREC |
|
Contact Details |
*SAD_UC_I_STARA |
*SAD_UC_APPLREC |
|
Application |
SAD_UC_I_IVGSTK |
SAD_UC_IVGSTK |
|
Course/Offer |
SAD_UC_I_IVSTRG |
SAD_UC_IVSTARG |
|
Offer/Conditions |
SAD_UC_I_ACOND |
SAD_UC_ACOND |
|
Personal Statement |
SAD_UC_I_IVGSTA |
SAD_UC_STATEMNT |
|
Qualification |
SAD_UC_I_AQUAL |
SAD_UC_AQUAL |
|
Jobs, Volunteering |
SAD_UC_I_IVGEXP |
SAD_UC_IVGEXP |
|
References |
SAD_UC_I_IVGREF |
SAD_UC_REFERENC |
A subprocess is called by the Import Applicant Data process that imports HESA reporting data from UCAS when the UCAS check boxes for Import HESA Data is selected on the Import Applicant Data page.
For more information about the Import Applicant Data page, see Entering the Applicant Import Parameters.
Business Rules
If you select the UCAS check box for the Import HESA Data sub-process, either the UCAS view ivStarJ (via odbc-link) or UCASHesaData (via XML-link) is imported to the staging table then processed.
The following tables are loaded if this subprocess is selected:
|
UCAS View/XML Object |
Staging Table |
Base Table |
|---|---|---|
|
IVSTARJ/ivStarJU UCASHESAData |
PS_SAD_UC_I_IVSTJ |
|
General Process Logic
The following is the logic for the Import HESA Data subprocess:
The subprocess considers the Update Matriculated Students check box setting on the UCAS Configuration page for processing External System ID records.
The subprocess updates HESA-specific records regardless of the Update Matriculated Students check box setting to ensure that the records are synchronized with the corresponding UCAS view. Updates are controlled by the fields on the HESA Data Import tab on the UCAS Configuration page.
For more information about the UCAS Configuration page, see Setting Up UCAS and Teacher Training Options.
Prerequisites
For a successful run of the Import HESA Data sub-process, the Person HESA Data and HESA Instance Data records must have been created for each applicant whose details you want to import.
See Creating HESA Instance and Person HESA Data Records for Students.
Import Records to Staging Table
If you have selected the Import Live Data from UCAS check box on the UCAS configuration page, then records are imported into the relevant staging table based on the time stamp value of each record. If the time stamp is greater than the internal time stamp for the view, then the record is imported. At the end of the Import Applicant Data process, the internal time stamp is updated with the highest time stamp of the records that have been imported.
Processing of Staging Table Records
For each staging table record with status of New, Pending or Error:
The record is matched to an existing UCAS applicant record in PS_SAD_UC_APPLREC using UCAS Personal ID and Application Code to find the EMPLID, or if applicant record isn't found, then the EMPLID is selected from matching External System ID records with ID type UC.
The imported Surname, Fnames, Birthdate and Gender values are compared with the values in the Campus Solutions primary person record. If the values are different then a message is logged. If a Surname, Fnames, Birthdate or Gender value is different and the Process Mismatched Applicants check box is selected, then the system adds a warning message to the log file (for example, “Warning: Personal ID: 0035697369, AppCode:UC01, ID:0278 - SURNAME value doesn't match person record”) and the subprocess continues to process the staging record. If a Surname, Fnames, Birthdate or Gender value is different and the Process Mismatched Applicants check box is not selected, the system sets the staging record status to Error. doesn't process the record and adds a message to the log file (for example, “Processing skipped for Personal ID: 0035697369, AppCode:UC01, ID:0278 - SURNAME value doesn't match person record”).
Person HESA Data Update: If a Person HESA Data record exists, then fields are added or updated using the incoming values (see table below). The inserts/updates are done depending on the configuration values for each Staging Field on the UCAS Configuration page. Each staging field value is validated against active codes for the HESA Field defined in UCAS Configuration.
External System ID (SCN/ULN):
If the Import check box is selected, external System ID records are created or updated based on the imported value and the selected Update Option. For Add/Update, a new effective-dated external ID record is created for the imported value. For Add If Blank, a new external ID is only created if there is no existing ID for the person.
Blank staging values and default values that are 999999999 for SCN and 9999999999 for ULN aren't processed.
For matriculated students, updates/inserts are done only if the Update Matriculated Students check box is selected.
HESA Instance – Entry Profile Data Update:
HESA Instance records are selected by matching from existing UCAS application choice records via the related Student Program records
Fields are added/updated using the incoming values (see table below) and inserts/updates are done depending on the configuration values for each Staging Field on the UCAS Configuration page. Each staging field value is validated against active codes for the HESA Field defined in UCAS Configuration.
-
HESA Instance – Entry Profile Data - Entry Qualifications Update
If the Import Entry Qualifications check box is selected, the individual field values are validated against active codes for the corresponding HESA Field.
Imported entry qualifications are matched to existing records (in PS_SSR_HE_QUAL_ENR) based on TYPE, SUBJECT, YEAR and SITTING and if a record already exists, then the GRADE value is updated, otherwise a new record is created
Records with errors (E) and pending records (P) are retained in the staging table and can be reprocessed once errors are corrected.
Data Mapping
The following table shows the Person Data mapping:
|
UCAS Value |
Comment/Format |
Staging Table Column: PS_SAD_UC_I_IVSTJ |
Compared with (Page name - Field name) |
|---|---|---|---|
|
Surname |
Surname |
SAD_UC_SURNAME |
Names - Last Name (for primary name record) |
|
Fnames |
Forenames |
SAD_UC_FORNAME |
Names - First Name, Middle Name (for primary name record) |
|
Birthdte |
Date of Birth |
SAD_UC_BIRTHDTE |
Biographical Details - Date of Birth |
|
Gender |
Gender (1 = Male or 2 = Female) |
SAD_UC_GENDER |
Biographical Details – Gender |
The following table shows the HESA Person Data mapping:
|
UCAS Value |
Comment/Format |
Staging Table Column: PS_SAD_UC_I_IVSTJ |
PS_SCC_HE_PER_FLD |
|---|---|---|---|
|
Carer |
HESA Carer code. Validated against the imported HESA codes for HESA Field defined in UCAS Configuration. |
SAD_UC_CARER |
CARER |
|
Disable |
HESA Disability code. Validated against imported HESA codes for the HESA Field defined in UCAS Configuration. (To view the codes that you have imported, select Records and Enrollment, HESA Reporting, Codes and Mappings, Codes). Your institution may have already imported Disability values which the system will have used to create person Disability/Impairment records. |
SAD_UC_DISABLE |
DISABILITY |
|
Ethnic |
HESA Ethnicity code. Validated against imported HESA codes for the HESA Field defined in UCAS Configuration. Your institution may have already imported Ethnic values which the system will have used to create person Ethnicity records. |
SAD_UC_ETHNIC |
ETHNICS |
|
Nation |
HESA country code. Validated against imported HESA codes for the HESA Field defined in UCAS Configuration. Your institution may have already imported Nationality values which the system will have used to create person Citizenship records. |
SAD_UC_NATIONALITY |
NATION |
|
ReligiousBelief |
2-digit code. Validated against imported HESA codes for the HESA Field defined in UCAS Configuration. |
SAD_UC_RELBLF |
ReligiousBelief |
|
SexualOrientation |
2-digit code. Validated against imported HESA codes for the HESA Field defined in UCAS Configuration. |
SAD_UC_SEXORT |
SexualOrientation |
The following table shows the External System ID mapping:
|
UCAS Value |
Comment/Format |
Staging Table Column: PS_SAD_UC_I_IVSTJ |
Base Table — Column |
|---|---|---|---|
|
SCN |
Scottish Candidate Number (default is 999999999). Your institution may have already imported SCN values which the system will have used to create SCN external system ID records. The system doesn't use the default value 999999999. |
SAD_UC_ SCN |
External System ID – SCN |
|
ULN |
Unique Learner Number (default is 9999999999). Your institution may have already imported ULN values which the system will have used to create ULN external system ID records. The system doesn't use the default value 9999999999. |
SAD_UC_ULN |
External System ID – ULN |
The following table shows the data mapping for HESA Entry Profile:
|
UCAS Value |
Comment/Format |
Staging Table Column: PS_SAD_UC_I_IVSTJ |
HESA Base Table: PS_SSR_HE_INST_FLD |
|---|---|---|---|
|
CareLeaver |
Value of 04 indicates if an applicant has been in care for more than 3 months. Value of 05 indicates if an applicant has been in care for less than 3 months, otherwise 99. |
SAD_UC_CARELEAVER |
CARELEAVER |
|
Domicile |
HESA country code Validated against imported HESA codes for the HESA Field defined in UCAS Configuration. Your institution may have already imported APR which the system will have used to create person Residency Self-Report records. |
SAD_UC_APR |
PERMADDCOUNTRY |
|
Estranged |
Validated against imported HESA codes for the HESA Field defined in UCAS Configuration. |
SAD_UC_ESTRANGED |
ESTRANGED |
|
ParEd |
Validated against imported HESA codes for the HESA Field defined in UCAS Configuration. |
SAD_UC_PARENTINHE |
PAREDE |
|
Postcode |
No validation applied. |
SAD_UC_POSTCODE |
PERMADDPOSTCODE |
|
Previnst |
No validation applied. |
SAD_UC_PRVINST |
PREVIOUSPROVIDER |
|
Qualent3 |
Validated against imported HESA codes for the HESA Field defined in UCAS Configuration. |
SAD_UC_QUALENT2 |
HIGHESTQOE |
|
Sec2020 |
Socio-Economic classification (ReferenceSocialEconomic). For applications up to 2021 cycle these aren't populated For applications from 2022 onwards these are populated using the value from SOC 2020 data. |
SAD_UC_SOCECO2020 |
SECE |
|
Soc2020 |
For applications up to 2021 cycle these aren't populated For applications from 2022 onwards these are populated using the value from SOC 2020 data. |
SAD_UC_POCC2020 |
SOC2020 |
|
Yrllinst |
No validation applied. |
SAD_UC_YRLLINST |
YRLLPROV |
The following table shows the data mapping for Entry Qualifications:
|
UCAS Value |
Comment/Format |
Staging Table Column: PS_SAD_UC_I_IVSTJ |
HESA Base Table: PS_SSR_HE_QUAL_ENT |
|---|---|---|---|
|
QualType1 – 30 |
Validated against imported HESA codes for QUALTYPEID. |
SAD_UC_QUALTYPE1 to 30 |
SSR_HE_QUAL_TYPE |
|
QualSbj1 – 30 |
Validated against imported HESA codes for SUBJECTID. |
SAD_UC_QUALSBJ1 to 30 |
SSR_HE_QUAL_SUBJ |
|
QualSit1 – 30 |
S (Summer) or W (Winter). Validated against translate values for SSR_HE_QUAL_SIT. |
SAD_UC_QUALSIT1 to 30 |
SSR_HE_QUAL_SIT |
|
QualGrade1 – 30 |
Validated against imported HESA codes for ENTRYQUALAWARDRESULT. |
SAD_UC_QUALGRADE1 to 30 |
SSR_HE_QUAL_GRADE |
|
QualYear1 – 30 |
4-digit Year value. No validation applied. |
SAD_UC_QUALYEAR1 to 30 |
SSR_HE_QUAL_YEAR |
Access the Transaction Data page ().
Field or Control |
Description |
|---|---|
Personal ID |
Displays the value imported from the StarN staging record for suspended records and from PS_SAD_UC_APPLREC for updated or added records. |
Previous Surname |
Displays the value from the StarN staging record for suspended records. This field doesn't display a Previous Surname value for updated or added records. |
Possible values for the Status field include:
New ID Created: The import process added a record with a new ID to your database when the post process was run. Or the system added a record with a new ID to your database when you clicked the Add button.
ID Updated: The import process updated an existing ID record with the data from the UCAS record. Or the system updated an existing ID record with the data from the UCAS record when you selected the existing ID in the Results region and then clicked the Update button.
Suspended: The import process has completed running the search/match process and indicated that the record should be held for later processing.
Ignore: The import process has completed running the search/match process and indicated that the record should be ignored for any processing.
Error: The import process suspends error records. The Error Messages region indicates which value is invalid. You must correct the invalid data specified by each error message before selecting the Add button or the Update button to process the record.
Note: To resolve the “First Name is required” error:
Enter dummy values in the First Name field for both the primary and preferred Name Types.
Select Save.
Select Add to create a new person or Update to match to an existing person.
Other staging statuses that you can see on the Transaction Data search page aren't currently used in UCAS.
You define the logic to determine the status values in the Search/Match Setup page. For example, to give the ID Updated status to a record, you may have specified that if the import process doesn't find a matching record, then add the imported record to the tables.
The Select a Duplicate region displays the single or multiple matching records. The system disables the Add and Update buttons and hides the Select a Duplicate region if the data has been posted. Also, all the fields appear as read-only when the status is New ID Created or ID Updated.
Select a duplicate
Displays a single or multiple matching records for the imported UCAS record where the status is suspended or error.
Click the Detail link to navigate to the Add/Update a Person component.
Field or Control |
Description |
|---|---|
Add |
Click to ignore any potential duplicates, manually add a new ID, and post the imported UCAS record to your database. When you click this button, the system posts the record and assigns an ID to the person. |
Update |
Click to match to an existing record and update the person record selected in the Select a Duplicate region with data from the UCAS record. |
|
Additional Information |
Click to access:
|
National ID
National ID staging records are created for UCAS and Apply applicants. You must select one row as the primary row.
This region becomes read-only once the system processes the constituent record and the status is updated to either “New ID Created” or “ID Updated.”
Citizenship
Citizenship staging records are created only for Apply applicants.
This region becomes read-only once the system processes the constituent record and the status is updated to either “New ID Created” or “ID Updated.”
Residency Official
Residency Official staging records are created only for Apply applicants.
This region becomes read-only once the system processes the constituent record and the status is updated to either “New ID Created” or “ID Updated.”
Residency Self Report
Residency Self Report staging records are created only for Apply applicants.
This region becomes read-only once the system processes the constituent record and the status is updated to either “New ID Created” or “ID Updated.”
Use the Staging Data pages to view, update, or delete the suspense data in the staging tables. The following is an example of a Staging Data page.
This example illustrates the fields and controls of a Staging Data page.
The Import Status field displays any one of the following values:
Error: If the Import Applicant Data process can't load the record into the Campus Solutions tables.
Imported: If the Import Applicant Data process has loaded the record into the Campus Solutions tables.
New: If the Import Applicant Data process hasn't loaded the record into the Campus Solutions tables.
Pending: If the Import Applicant Data process load is waiting for the creation of a new person ID for the applicant.
In the UCAS Contact Details Staging search page, the Country Code search field searches both the Correspondence Country and the Home Country values. The search results therefore display records that have a matched search value in either the Correspondence Country field or Home Country field or both fields.
The staging data pages can display data from either the Unicode or non-Unicode views or objects. For example, if you have selected the Unicode check box on the UCAS Configuration page, the Contact Details Data page displays the data returned by getUCASApplicantsDetails_U. Conversely, if you haven't selected the Unicode check box on the UCAS Configuration page, the Contact Details Staging Data displays the data returned by data returned by getUCASApplicantsDetails.
Access the Internal Timestamp page ().
This example illustrates the fields and controls on the Internal timestamp page. You can find definitions for the fields and controls later on this page.
Field or Control |
Description |
|---|---|
Timestamp |
Displays the highest UCAS time stamp for the view when it was last imported. |
Override |
Specify an override date if you want to reload a particular view. After specifying the override date, run the import process. For odbc, the import process then imports the UCAS view records that have a time stamp of on and after the specified override date. For XML, the import process then imports records from the XML object where lastUpdated is on or after the specified override date. The default system time for the specified override date is 00:00:00. After the import process finishes, the system sets the Override field value to blank. |
Last Run time stamp |
Displays the latest time stamp used by the import process when it was last run. |
Records Added |
Displays the number of records that the import process added when the process last ran. |
Records Amended |
Displays the number of records that the import process modified when the process last ran. |
Records Deleted |
Displays the number of records that the import process deleted when the process last ran. |
The following scenario describes how the Internal timestamp page displays its field values:
You have already imported records for a view that has UCAS time stamps up to and including July 31, 2008. Therefore, the Timestamp field on the Internal time stamp page displays the date July 31, 2008.
You now want to re-import data for the view that UCAS has modified and added on or after May 31, 2008, 12 AM.
On August 4, 2008 enter an override date of May 31, 2008 in the Internal time stamp page and run the Import Applicant Data process.
After the Import Applicant Data process completes importing the data, the system sets the Override date field as blank and sets the Last Run time stamp field as May 31, 2008 because May 31,2008 was the override time stamp date that the system used when the process was run.
The Internal time stamp page displays the number of records that the import process added, amended, and deleted on August 4, 2008.
The Timestamp field on the Internal time stamp page still displays the date July 31, 2008 if no records were found in the UCAS view with time stamps greater than July 31. If the import process imported records in the UCAS view that had a time stamp of August 1, 2008, then the Timestamp field displays the date August 1, 2008.
Add a New Value
Use the Internal Timestamps - Add a New Value page to manually add timestamp records especially if you only want to import recent updates and not the full data set. If you need to, you can also set the timestamp to a future date to prevent any records from being imported.
When you manually add records, the Timestamp is editable, while the Override Timestamp field is display-only. Once you've saved the record, Timestamp becomes display-only and Override Timestamp is editable.
To enter a timestamp value, you must use this format: DD/MM/YY HH:MM:SS or MM/DD/YY HH:MM:SS (depending on the date format settings in My Preferences).