Top of Proposals

Index

Table of Contents

Feedback

CRSJ6000 - Approved Unit Transfer

Table of Contents

• Overview
• Summary
• Transfer Process
  • Insert into S_EXTRACT table
  • Check for Required System data
  • Determine the Proposals to transfer
  • Determine the data from each proposal to transfer
  • Update the status of the proposal
  • Generate Completion Message
• Notes
  • Validations
  • Insert, Update and Delete Indicators
  • PII and PIE Sequence Numbers (superior functionality)
  • Field Types and Concatenated Fields
  • Units with Basic Details split betweeen many Elements
  • Unit Version Rules
  • Unit Assessment Pattern and Unit asessment Pattern Item
  • User Defined fields

Overview

This job transfers approved proposal information to Course Structure and Planning (CRS). All information provided will be validated against existing business rules to ensure that the data given will transfer successfully. It is recommended that this job be run as an overnight process.

A Unit Proposal is considered ready for transfer once it has been approved and has a system transfer status of READY or ERROR. (Records with ERROR will be processed again to see if issue has been fixed).

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

A number of Unit-related tables may be populated with Proposal information - see list below:

The order of the tables in that list shows the sequence in which the inserts/updates are done. The opposite order will apply when deleting, for example, Unit Offering Pattern records will be deleted before Unit Offering records.
The inserts, updates and deletes that will be carried out will be based on the number of Proposal Elements/Items supplied by the Proposer.

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

The table below provides a simple mapping example for a new unit proposal:

For a Proposal that requests a unit update, the job can update all fields that exist in Callista SMS. However, the primary key of each table will not be updated.
In a proposal that calls for the deletion of records from Course Structure and Planning the job first identifies which records need to be deleted, with child records deleted before parent records. If a relationship exists and a record cannot be deleted, then the error will be logged and the child and parent data will remain.

Before any transfer of data is done, all business rules in Course Structure and Planning are run through. If a business rule is not met when validating, then a system transfer status of ERROR appears against the Proposal. When a validation fails, all inserts, updates and deletes are rolled back. All business rules are validated even if an error occurs, so all errors are logged for an administrator to view.

For errors, more information can be accessed through Generate Extract XML File (GENJ1000) which can retrieve the error messages from the log.

If all validations are passed, the Transfer Status then the Proposal will be changed to COMPLETE. Once the job finishes, a summary in the run log will appear noting how many proposals were processed and the number of errors that occurred.

There are no parameters for this job.
This job 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

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 to be transferred:

• Determine the data to be transferred.
• Insert, update or delete data in Unit related tables in the order described below.

And then update the status off the proposal and insert a record into the PROPOSAL_INSTANCE_UNIT_VERSION table

5. Generate Completion message in the run log.

Return to Top

Transfer Process

The following provides a more detailed view of the processing required to transfer approved unit proposal information from the Proposals data structure to Course Structure and Planning (CRS).

1. Insert into S_EXTRACT table

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

• s_extract_type => PRPSL-UNIT
• key => p_request_job_run_id
• request_job_run_id => p_request_job_run_id
• creation_dt => SISDATE

2. Check for required system data

Check that the system transfer statuses and system proposal outcomes have been defined.
System Proposal Outcomes NEW, NEW-V, UPD-V and NO-TRF-UN and System Transfer Statuses ERROR, READY, COMPLETE and must be defined, for this job to process proposals. If not, the job will not proceed.

3. Determine the Proposals to transfer

For a proposal to be transferred: the Proposal Transfer Status must be READY or ERROR, and the Proposal Type must have a System Proposal Type of UNIT.

If a proposal has a System Proposal Outcome Status of NO-TFN-UN (approved no transfer), then the unit proposal will not be transferred and the proposal's transfer status will be set to 'COMPLETE'.

a. Select the data from the proposal to be inserted into the Course Structure and Planning tables

Process Unit-related data in the following order:

• Unit
• Unit Version
• Unit Offering
• Unit Offering Pattern
• Unit Offering Option
• Unit Version Sector
• Teaching Responsibility
• Teaching Responsibility Override
• Unit Discipline
• Subordinate Unit Relationship
• Unit Categorisation
• Course Unit Level
• Unit Reference Code
• Training Package Unit
• Business Activity Process (for Unit Version, Unit Offering and Unit Offering Option)
• Business Activity Process Member (for Unit Version, Unit Offering and Unit Offering Option)
• Assessment Item
• Unit Assessment Item
• Assessment Item Assessor
• Assessment Course Type
• Assessment Item Exam Material
• Unit Assessment Pattern
• Unit Assessment Pattern Item
• Unit Version Rule
• Notes
• Unit Offering Pattern Notes
• Unit Offering Notes
• Unit Offering Option Notes
• Unit Version Notes

In each case, use the table below to determine if the data should be inserted, updated or deleted.

b. Update Proposal Transfer Status

Once a proposal has been processed, the PROPOSAL TRANSFER STATUS is set to COMPLETE when the proposal is successfully processed, or ERROR when an error was found when processing.

Once a NEW, NEW_V or UPD_V proposal has been successfully transferred, the new unit version details are inserted into the PROPOSAL_INSTANCE_UNIT_VERSION table. This is to help identify which proposal created which unit version.

4. 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-UNIT - System Extract Creation Date: date and time

Return to Top

Notes

Validations

Trigger validations are checked, starting with Unit Version validations.
There are validations that repeat, for example, message number 241 (message text: Changes cannot be made to unit version details because the status of the unit version is inactive. Only unit status, expiry dt and end date can be updated). In these cases, the business rule is validated at the highest level. In relation to message number 241, this is at Unit Version.

Each time a validation is not passed it is logged to the S_EXTRACT_RECORD table and the error count is 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 a business rule returns FALSE, the error is logged and the proposal is no longer validated (for any more sections). e.g. If a proposal returns false for the first Unit Offering Pattern validation then the rest of the validations for this section are still processed, but no further validations for other sections are done (e.g. Unit Offering Option validations). The next proposal is then moved onto.
If any inserts, updates or deletes have been done before a business rule returns FALSE, then these will be rolled back. Even units in the proposal that were successful will be rolled back.

Warnings are logged, but do not stop processing of the proposal. There are four warnings that could be reported. These are:

• 4066 - Warning: There are UNCONFIRMED or INVALID unit attempts associated with the version.
• 7033 - Warning. The Unit Code exceeds the maximum AVETMISS length. Characters in excess of 12 will be truncated for reporting purposes
• 7048 - The number of Supervised Contact Hours should not normally be greater than 400.
• 401 - Sub-ordinate unit(s) exist with a status of planned or inactive.
  

Insert, Update and Delete indicators

There are indicators on the Proposal_Instance_Element and Proposal_Instance_Item tables that assist the job in identifying the data to insert, update or delete.

For a NEW proposal outcome - All indicators will be N. Data is to be inserted.

For NEW_V proposal outcome - Insert, Update and Delete indicators will be N for records (proposal elements and items) that are to be rolled over and not changed. For example, if unit JKC111.1 is being rolled over to JKC111.2 then each indicator will be N for the unit details.

However, if the unit JKC111.2 is to have a new Unit Discipline compared to JKC111.1 then this Unit Discipline element will be marked as INSERT_IND = Y. Likewise, if a Unit Discipline from JKC111.1 is to be deleted, then the delete_ind = Y. It is also possible to be updating the proposal element, which will have update_ind = Y against the proposal element item, e.g. update to percentage for unit discipline. Below is this example shown in data form.
Note: For a new Unit Version, the MAX version number is to be found for the Unit Code.

Proposal Instance Element

Proposal Instance Item

In the table above, for the new unit JKC111.2, three records will be inserted for Unit Discipline. Discipline Group Code of 501 will be inserted but the percentage is to be updated to 100. Discipline Group Code of 502 will be inserted as new record. Discipline Group Code of 503 will be inserted as well (this is a carried over from JKC111.1). Discipline Group Code of 504 will be deleted, rather than rolled over.
Note that for the UNIT proposal instance item, the data is shown as JKC111|. This is because there is no version number defined for the new unit as this job will determine the version to be used ('Field Types and Concatenated Fields' explained further on).

For UPD_V proposal outcome - Insert, Update and Delete indicators will be N for records (proposal elements and items) that are used as context information. For example, if the percentage for teaching responsibility 01111.1 is to be changed from 80% to 100% then the Unit Version element and Teaching Responsibility element will have insert_ind = N and delete_ind = N, with the percentage proposal item update_ind = Y.
Below is this example shown in data form.

Proposal Instance Element

Proposal Instance Item

In the table above, the inserts, updates and deletes will be done against unit JKC111.1 (shown concatenated as JKC111|1). This is saved into the Proposal Instance Element and Proposal Instance Item tables only as context.
No insert is done into the UNIT_VERSION tale for this record.
For teaching responsibility of 01 (01/01/1992), an update is done to the percentage.
For teaching responsibility of 02 (01/01/1992), this record is to be deleted.
For teaching responsibility of 03 (01/01/1992), this record is to be inserted.

There are areas where a new sequence number is to be generated for inserts. For example, a new Unit Offering Option requires a new UOO_ID. Alternatively, a new Training Package Unit requires a new TPU_SEQUENCE_NUMBER. Where a new sequence number is required the next number in the sequence is generated.
For the deletion of data, a child record is deleted before a parent record. If child records exist when deleting, then an exception will occur. An exception will also occur when an attempt is made to update a locked record and when a trigger validation fires when inserting, updating or deleting.
When a delete is to be done for a table that has a logical delete date column, this field should be updated with SYSDATE. The tables with logical delete date columns are:

• SUB_UNIT_RELATIONSHIP
• UNIT_ASSESSMENT_ITEM
• UNIT_ASSESSMENT_PATTERN

For updating, only fields that are not part of the primary key of the table can be updated.

PII and PIE Sequence Numbers (superior functionality)

To determine which proposal instance element is associated with which proposal instance item, the PROPOSAL_INSTANCE_ELEMENT and PROPOSAL_INSTANCE_ITEM sequence numbers are used. They are also used to ascertain if a Proposal Instance Element is a superior to another Proposal Instance Element.
Below is an example of superior proposal instance elements:

In the table above, UNIT is superior to UNIT OFFERING and UNIT OFFERING is superior to UNIT OFFERING PATTERN. Unit is also a superior to TEACHING RESPONSIBILITY and UNIT DISCIPLINE.
Superiority can also be determined through the S_PROPOSAL_ELEMENT table.

Return to Top

Field Types and Concatenated Fields

The values to be inserted into Course Structure and Planning tables are stored in the Character, Number, Date or Clob field in the PROPOSAL_INSTANCE_ITEM table. The field type is determined by the TABLE_NAME.COLUMN_NAME of where the data is to be transferred. For example, the UNIT_DISCIPLINE.PERCENTAGE value would be stored in the Number field.
However, there is exception to this rule with concatenated fields. Concatenated fields are values that should be kept together. For example, Unit Code and Version Number or Org Unit and Start Date. These values are stored in the character field, even if both values are of type Number. They are stored as MAA101|1 or 01|01/01/1992.
The TABLE_NAME and COLUMN_NAME are stored in the S_PROPOSAL_ITEM table. The concatenation type is also stored in this table (S_FIELD_TYPE), with the details recorded in S_CONCAT_TYPE. The details determine the type of the concatenated fields.

Proposal Instance Item

Proposal Item

System Proposal Item

System Concat Type

In the table above, the value of SEM-1 will be inserted into UNIT_OFFERING_OPTION.CAL_TYPE and 1924 will be inserted into UNIT_OFFERING_OPTION.CI_SEQUENCE_NUMBER.
From the system tables we can identify that UNIT_OFFERING_OPTION.CAL_TYPE is of type VARCHAR and UNIT_OFFERING_OPTION.CI_SEQUENCE_NUMBER is of type NUMBER.

Proposal Instance Item

Proposal Item

The value of 100 would be inserted into UNIT_DISCIPLINE.PERCENTAGE.

Return to Top

Units with Basic Unit Details split between many Elements

Where the Proposal Type has a Single Core Instance and the Proposal Element is not multi-instance, the basic unit 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 UNIT.

Return to Top

Unit Version Rules

For Unit Version Rules, the Rule Call Code is decoded from the Proposal Item Code as shown below:

Unit Assessment Pattern and Unit Assessment Pattern Item

For Unit Assessment Pattern and Unit Assessment Pattern Item, a Unit Assessment Item must first be defined which also requires an Assessment Item. This means that only one Assessment Item can be defined per Unit Assessment Pattern Item.

Return to Top

User Defined fields

User defined fields are not to be transferred. These fields do not have a TABLE_NAME or COLUMN_NAME defined against them in the S_PROPOSAL_ITEM table.

Return to Top

Page last modified: 31 May, 2012 3:57 PM

History Information: