Top of Proposals Index Table of Contents Feedback

CRSJ6010 - Approve Course Transfer

Table of Contents

 

Overview

This job transfers approved Course information from the Curriculum Amendments and Proposals (CAPS) area to the Callista Course Structure and Planning (CRS) Subsystem.

A course proposal is ready for transfer when it has been approved and has a System Transfer Status of READY or ERROR. (Records with ERROR will be processed again to see if the issue has been fixed).

As an outcome of this process: a new Course could be created, a new version of an existing Course could be created, or details of an existing Course version could be updated or deleted.

For a proposal that requests a course update, the job can update all appropriate fields that exist in the CRS subsystem except for the primary key of any table.

Note: Where a new course version is created that is based on an existing course version, the elements not specified in the proposal will be inherited from the course version on which the proposal is based, not necessarily the latest version of the course. When creating a new version of a course, it is recommended that it is based on the latest version of the course and that any data required from earlier versions, be imported into the proposal.

Verification of all existing course business rules occurs and if a business rule fails when validating, then a System Transfer Status of ERROR is recorded for the affected proposal. When a validation fails, the transfer job rolls back all Inserts Updates and Deletes for the proposal and the processing moves onto any remaining approved proposals that are ready to transfer.

To assist in identifying errors raised during the transfer, all errors are logged into the system extract logs. More information can be accessed through Generate Extract XML File (GENJ1000), which retrieves the error messages from the log.

A successful transfer moves the proposal to a COMPLETE status. Once the job finishes, a summary in the run log will appear noting how many successful Inserts, Updates and Deletes were transferred and the number of errors that occurred.

There are no parameters for this job. It can be run in Immediate or Scheduled mode, but it is recommended that this job is run as an overnight process.

Return to Top

Summary

This is a high level summary of the actions performed by this job.

  1. Create an S_EXTRACT record where all errors will be logged.
  2. Check that the required system data is present.
  3. Determine the proposals to transfer.
  4. For each proposal, determine the data to be transferred.
  5. For each proposal, insert, update or delete data in Course tables in the following order:

    6. And then update the status of the proposal.

  6. Generate the completion message in the run log.
Return to Top

Job Detail

The following provides a more detailed view of the processing required to transfer approved course proposal information from the Course Proposals to the Callista Course Structure and Planning subsystem.

1. Insert into S_EXTRACT table

A record is inserted into the s_extract table, as this is the parent table to s_extract_record where all errors will be logged. The parameters to be inserted are:

2. Check for required system data

Check that the required system transfer statuses and system proposal outcomes have been defined.

System Proposal Outcomes - NEW-CRS, NEW-CRS-V, UPD-CRS-V, NO-TRF-CRS, and System Transfer Statuses - ERROR, READY, COMPLETE must be defined for this job to transfer proposals, if not, the job will not proceed.

3. Determine the Proposals to transfer

For a proposal to be transferred: the System Proposal Instance Status must be APPROVED, Proposal Transfer Status must be READY or ERROR and the Proposal Type must have a System Proposal Type of COURSE, but the System Proposal Outcome Type must not be CRS-POC.

4. For each proposal, determine the data to be transferred.

5. For each of the Proposals identified, select the data from the proposal to be inserted into the Course Structure and Planning tables

Use this table to determine if the data in each section below should be inserted, updated or deleted.

Outcome Insert
(Element)		 Update
(Item)		 Delete
(Element)		 Action
NEW-CRS Any Any N Insert and validate.
NEW-CRS N N Y Ignore (Do not Insert).
NEW-CRS-V N N N For CRS and CRV data the record will need to be inserted. For all other data it will be rolled over.
NEW-CRS-V Y N N Insert and validate (New Data).
NEW-CRS-V N Y N Update and validate (update existing data).
NEW-CRS-V N N Y Update and validate (delete existing data).
NEW-CRS-V Y N Y Ignore (New data entered but now deleted)(a).
UPD-CRS-V N N N Context information, don't validate as not inserting.
UPD-CRS-V Y N N Insert and validate (New Data).
UPD-CRS-V N Y N Update and validate (new data).
UPD-CRS-V N N Y Delete and validate (delete existing data).
UPD-CRS-V Y N Y Ignore (new data entered but now deleted).

 

a). COURSE and COURSE_VERSION tables

  1. Select all the data from the proposal to be inserted into the COURSE_VERSION table. Note: there may be multiple Course Versions in the one proposal.
  2. If a new course is being created, the version number is incremented.
  3. If it is a version update, use the version number defined in the proposal instance item. If a new version is being created, set the Expiry Date for the existing ACTIVE version of the course version to yesterday's date.
  4. If the installed sector is not CROSS and the s_sector_cd field is NULL, then set this to the installed sector.
  5. If this is a new course, insert a record into the COURSE table.
  6. If inserting the course version record, then insert, otherwise update the course version record. In the update, build the statement dynamically to ensure only the changed fields are updated.
  7. If required, delete the Course Version. If this course version is the only course version for the course, then delete the course record.
  8. If a new version of the course version is being created, the course version related data is rolled over into the new version.

b). ALTERNATIVE_EXIT table

  1. Select all the data from the proposal to be inserted into the ALTERNATIVE_EXIT table.
  2. Determine if the data should be inserted, updated or deleted (According to the table above).
  3. Test all Alternative Exit validations. If an error occurs log the error to the s_extract_record.
  4. Insert or delete where allowed, and if updating the alternative exit record, only update the changed fields.

c). COURSE_AWARD table

  1. Select all the data from the proposal to be inserted into the COURSE_AWARD table.
  2. Determine if the data should be inserted, updated or deleted (According to the table above).
  3. Test all Course Award validations. If an error occurs log the error to the s_extract_record.
  4. Insert or delete where allowed, and if updating the course award record, only update the changed fields.

d). COURSE_CATEGORISATION table

  1. Select all the data from the proposal that would be inserted into the COURSE_CATEGORISATION table.
  2. Determine if the data should be inserted, updated or deleted (According to the table above).
  3. Test all Course Categorisation validations. If an error occurs log the error to the s_extract_record.
  4. Insert or delete where allowed, and if updating the Course Categorisation record, only update the changed fields.

e). COURSE_GROUP_MEMBER table

  1. Select all the data from the proposal to be inserted into the COURSE_GROUP_MEMBER table.
  2. Determine if the data should be inserted, updated or deleted (According to the table above).
  3. Test all Course Group Member validations. If an error occurs log the error to the s_extract_record
  4. Insert or delete where allowed, and if updating the Course Group Member record, only update the changed fields.

f). DFLT_MILESTONE_SET table

  1. Select all the data from the proposal to be inserted into the DFLT_MILESTONE_SET table.
  2. Determine if the data should be inserted, updated or deleted (According to the table above).
  3. Test all Default Milestone Set validations. If an error occurs log the error to the s_extract_record.
  4. Insert or delete where allowed, and if updating the Default Milestone Set record, only update the changed fields.

g). COURSE_FIELD_OF_STUDY table

  1. Select all the data from the proposal to be inserted into the COURSE_FIELD_OF_STUDY table.
  2. Determine if the data should be inserted, updated or deleted (According to the table above).
  3. Test all Course Field of Study validations. If an error occurs log the error to the s_extract_record.
  4. Insert or delete where allowed, and if updating the Course Field of Study record, only update the changed fields.
  5. Select all the data from the proposal to be inserted into the FUNDING_SOURCE_RESTRICTION table.
  6. Determine if the data should be inserted, updated or deleted (According to the table above).
  7. Test all Funding Source Restriction validations. If an error occurs log the error to the s_extract_record.
  8. Insert or delete where allowed, and if updating the Funding Source Restriction record, only update the changed fields.

i). COURSE_OWNERSHIP table

  1. Select all the data from the proposal to be inserted into the COURSE_OWNERSHIP table.
  2. Determine if the data should be inserted, updated or deleted (According to the table above).
  3. Test all Course Ownership validations. If an error occurs log the error to the s_extract_record.
  4. Insert or delete where allowed, and if updating the Course Ownership record, only update the changed fields.

j). COURSE_REFERENCE_CD table

  1. Select all the data from the proposal to be inserted into the COURSE_REFERENCE_CD table.
  2. Determine if the data should be inserted, updated or deleted (According to the table above).
  3. Test all Course Reference Code validations. If an error occurs log the error to the s_extract_record.
  4. Insert or delete where allowed, and if updating the Course Reference Code record, only update the changed fields.

k). COURSE_OFFERING and related tables

  1. Select all the data from the proposal to be inserted into the COURSE_OFFERING table.
  2. Determine if the data should be inserted, updated or deleted (According to the table above).
  3. Test all Course Offering validations. If an error occurs log the error to the s_extract_record.
  4. Insert or delete where allowed, and if updating the Course Offering record, only update the changed fields.

 

l). BUSINESS_ACTVTY_PRCS_PE_REF and the BUSINESS_ACTIVITYPRCS_PE_PIG tables for the course version business activity process.

  1. Select all the data from the proposal to be inserted into the BUS_ACTVTY_PRCS_TBL_REF and the BUS_ACTVTY_PRCS_PE_PIG tables for the Course Version Business Activity Process.
  2. Determine if the data for BUS_ACTVTY_PRCS_TBL_REF should be inserted, updated or deleted (According to the table above).
  3. Test all Business Activity Process Table Reference validations. If an error occurs log the error to the s_extract_record.
  4. Insert or delete where allowed, and if updating the Business Activity Process Table Reference record, only update the changed fields.
  5. Determine if the data for BUS_ACTVTY_PRCS_PE_PIG should be inserted, updated or deleted (According to the table above).
  6. Test all Business Activity Process Person PIG validations. If an error occurs log the error to the s_extract_record.
  7. If inserting the BUS_ACTVTY_PRCS_PE_PIG, then insert. If it is a new version of the unit set then insert a record into the BUS_ACTVTY_PRCS_PE_PIG table with the new BAP sequence number, otherwise update the BUS_ACTVTY_PRCS_PE_PIG record, changing only the fields that are updated. If defined, delete the BUS_ACTVTY_PRCS_PE_PIG.

 

m) For each of the Proposals, update Proposal Status

If errors occur update the proposal instance transfer status to the value where the system value is ERROR and the system default ind is selected.

If no errors have occurred update the proposal instance transfer status to the value where the system value is COMPLETE and the system default ind is selected.

Once a NEW-CRS, NEW_CRS_V or UPD_CRS_V proposal has been successfully transferred, the new course version details are inserted into the PROPOSAL_INSTANCE_CRS_VERSION table. This is to help identify which proposal created which course version.

6. Generate Completion message in run log

On completion the following details are recorded in the run log: 

Started processing date and time
   Total records processed: NN (Successful: NN, Unsuccessful: NN)
   Total number of system extract records created: NN
   Completed processing date and time
Please query the system extract records for details of all errors and exceptions.
   System Log:
   - System Extract Type: PRPSL-CRV
   - System Extract Creation Date: date and time

Return to Top

Notes

Where the Proposal Type has a Single Core Instance and the Proposal Element is not multi-instance, the basic course details may be split across many elements. When the job is identifying data to be transferred for these proposals, the process searches over the whole proposal instance for the items with a system proposal element of COURSE.

The ability for the client to alter uppercase constraints should be taken into account. All fields in the uppercase constraint should have an upper clause added to the data when inserting or updating values. If the field is not in the uppercase constraint, then the data should be inserted as per the case entered into the proposal subsystem.

Each time a validation is not passed it will be logged to the s_extract_record table and the error count will be incremented. This error count is displayed in the run log once the job has finished processing (Total Proposals in error).

The information logged to s_extract_record depends on the area that is being validated and the information available.>

When an error is logged: the processing of the proposal stops and if any inserts, updates or deletes had already been performed, then these are rolled back. Warnings are logged, but do not stop the processing of the proposal.

For the deletion of data, child records are deleted along with the parent record.

An exception will occur when an attempt is made to update a locked record.

Return to Top

Page last modified: 6 June, 2012 2:06 PM

History Information:

Release Version Project Change to Document
15.0 1722 - 11g Upgrade Help page rewritten for 11g.