Top of Proposals

Index

Table of Contents

Feedback

CRSJ6000 - Approved Unit Transfer - New for Release 12.0.0.2

Transfer Process

The following provides an overview 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 => SYSDATE

2. Check for required system data
Validate that the system transfer statuses and system proposal outcomes have been defined.
The following System Proposal Outcomes must be defined for job to process proposals:

• NEW
• NEW_V
• UPD_V

The following System Transfer Statuses must be defined for job to process proposals:

• ERROR
• READY
• COMPLETE

If these are not defined, the job will stop processing.

3. Determine the Proposals to transfer
For a proposal to be transferred the Proposal Transfer Status must be READY or ERROR.
If no proposals are processed then the following should appear in the job run log:

Started processing 21/01/2009 11:13:05
     Total records processed: 77 (Successful: 70, Unsuccessful: 7)
     Total number of system extract records created: 7
Completed processing 21/01/2009 11:13:59

Please query the system extract records for details of all errors and exceptions.
System Log:
         - System Extract Type: PRPSL-UNIT
         - System Extract Creation Date: 21/01/2009 11:13:05

Where:
'Total records processed' = Number of proposals found to transfer
'Successful' = Number of these proposals with Transfer Status = COMPLETE
'Unsuccessful' = Number of these proposals with Transfer Status = ERROR
'Total number of system extract records created' = the number of errors found for the proposal before processing stopped (for that proposal).
System Extract Type: PRPSL-UNIT (if errors found)
System Extract Creation Date: when the error occurred (if errors found)

There can be one or multiple units to transfer for one Proposal ID.
The proposals must be of S_PROPOSAL_TYPE of UNIT.

4. 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 on to.
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 were successful, all 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.
  

5. 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 tables 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 UNIT_VERSION 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 ia 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.

6. PIE and PII 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:

Proposal Instance Element

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.
           
7. 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.

Below is an example of a concatenated field:

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.

Below is an example of a non-concatenated field:
Proposal Instance Item

Proposal Item

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

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

9. 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.

10. 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.

11. Exceptions
There are exceptions that may occur when inserting, updating or deleting proposal data.
The exceptions reported are:

• Resource busy (-54)
• Child Records exist (-2292)
• Trigger validation fired (-20999 to -20000)
• Message returned from validation that fired
• Others (to capture all other types of exceptions)

These will be reported into the S_EXTRACT_RECORD table with the table alias of where the error occurred. The exception will form part of the error count to be reported at the end of processing.
Similarly for validations, if an exception occurs then processing will stop for the current proposal but will continue on for other proposals.

12. Insert into Proposal_Instance_Unit_Version table
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.

13. Generate Extract XML File (GENJ1000)
Using GENJ1000, the errors recorded in the s_extract_record table are extracted into an XML file with Extract Type = PRPSL-UNIT.

14. 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.

Last modified on 28 October, 2009 12:06 PM

History Information