| Top of Proposals | Index | Table of Contents | Feedback | |
This job transfers approved Unit Set proposals from the Proposal subsystem to the Course Structure and Planning (CRS) subsystem.
A Unit Set 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 the issue has been fixed).
As an outcome of this process: a new unit set could be created, a new version of an existing unit set could be created, or details of an existing unit set version could be updated or deleted.
For a proposal that requests a unit set update, the job can update all appropriate fields that exist in the CRS subsystem except for the primary key of any table.
Verification of all business rules on CRS tables occurs and if a validation fails, then a System Transfer Status of ERROR is recorded for that proposal and the transfer job rolls back all inserts updates and deletes for the proposal before moving onto the next approved proposal that is ready for transferring.
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 can retrieve 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/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.
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, insert, update or delete data in Unit Set-related tables, in the following order:
And then update the status of the proposal.
5. Generate the completion message in the run log.
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:
Check that the system transfer statuses and system proposal outcomes have been defined.
System Proposal Outcomes NEW-US, NEW-US-V, UPD-US-V and NO-TRF-US and System Transfer Statuses ERROR, READY, COMPLETE and must be defined, for this job to process proposals. If not, the job will not proceed.
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 UNITSET.
If a proposal has a System Proposal Outcome Status of NO-TFN-US (approved no transfer), then the unit set proposal will not be transferred and the proposal's transfer status will be set to 'COMPLETE'.
The table below describes how the action taken is determined.
| Outcome | Insert (Element) |
Update (Item) |
Delete (Element) |
Action |
|---|---|---|---|---|
| NEW-US | Any | Any | N | Insert and validate. |
| NEW-US | N | N | Y | Ignore (Do not Insert). |
| NEW-US-V | N | N | N | For Unit Set data the record will need to be inserted. For all other data it will be rolled over. |
| NEW-US-V | Y | N | N | Insert and validate (New Data). |
| NEW-US-V | N | Y | N | Update and validate (update existing data). |
| NEW-US-V | N | N | Y | Update and validate (delete existing data). |
| NEW-US-V | Y | N | Y | Ignore (New data entered but now deleted)(a). |
| UPD-US-V | N | N | N | Context information, don't validate as not inserting. |
| UPD-US-V | Y | N | N | Insert and validate (New Data). |
| UPD-US-V | N | Y | N | Update and validate (new data). |
| UPD-US-V | N | N | Y | Delete and validate (delete existing data). |
| UPD-US-V | Y | N | Y | Ignore (new data entered but now deleted). |
Determine if the unit set data should be inserted, updated or deleted. This is determined via the system outcome type and the insert, updated and deleted indicators in the Proposal Instance.Note: there may be multiple unit sets in the one proposal.
If a new unit set or new version is being created, the version number is incremented, if it is a version update the version number is the version defined in the proposal instance.
If a new version is being created the expiry date for the existing ACTIVE version of the unit set is set to yesterday's date.
All unit set validations are completed. If an error occurs, log the error to s_extract_record.
If inserting the unit set record, then insert, else update the changed fields. If defined, delete from the unit set.
If a new version of the unit set is being created, all unit set related data is rolled over.
An error is generated if the proposal is NEW and the unit set code already exists.
Where the Proposal Type has a Single Core Instance and the Proposal Element is not multi-instance, the basic unit set 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 UNITSET.
Select all the data from the proposal relating to the unit set course type:
Determine if the unit set course type should be inserted, updated or deleted using the system outcome type and the insert, updated and deleted indicators associated to the proposal instance. Refer to table above.
Complete all unit set course type validations. If an error occurs, log the error to the s_extract_record.
If inserting the unit set course type record, then insert, else update the unit set course type record. In the update, build the statement dynamically to ensure only the changed fields are updated. If defined, delete the unit set course type.
Determine if the unit set field of study should be inserted, updated or deleted using the system outcome type and the insert, updated and deleted indicators associated to the proposal instance. Refer to table above.
Complete all unit set field of study validations. If an error occurs, log the error to the s_extract_record.
If inserting the unit set field of study record, then insert, else update the unit set field of study record. In the update, build the statement dynamically to ensure only the changed fields are updated. If defined, delete the unit set field of study.
Determine if the unit set rule or rule should be inserted, updated or deleted using the system outcome type and the insert, updated and deleted indicators associated to the proposal instance
Complete all unit set rule or rule validations. If an error occurs, log the error to the s_extract_record.
If inserting the unit set rule or rule record, then insert. If it is a new version of the unit set then call rulp_ins_copy_rule and insert a record into the unit set rule with the new rule sequence number, else update the unit set rule record. In the update, build the statement dynamically to ensure only the changed fields are updated. If defined, delete the unit set rule.
Determine if the unit set note or note should be inserted, updated or deleted using the system outcome type and the insert, updated and deleted indicators associated to the proposal instance
If inserting the unit set note or note record, then insert. If it is a new version of the unit set note then insert a record into the unit set note and the note table with the new note sequence number, else update the unit set note record. In the update, build the statement dynamically to ensure only the changed fields are updated. If defined, delete the unit set note.
Determine if the BUS_ACTVTY_PRCS_TBL_REF data should be inserted, updated or deleted using the system outcome type and the insert, updated and deleted indicators associated to the proposal instance
Complete all BUS_ACTVTY_PRCS_TBL_REF validations. If an error occurs, log the error to the s_extract_record.
If inserting the BUS_ACTVTY_PRCS_TBL_REF, then insert. If it is a new version of the unit set then insert a record into the BUS_ACTVTY_PRCS_TBL_REF table with the new BAP sequence number.
Determine if the BUS_ACTVTY_PRCS_PE_PIG should be inserted, updated or deleted using the system outcome type and the insert, updated and deleted indicators associated to the proposal instance
Complete all BUS_ACTVTY_PRCS_PE_PIG validations. If an error occurs, log the error to the s_extract_record.
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, else update the BUS_ACTVTY_PRCS_PE_PIG record. In the update, build the statement dynamically to ensure only the changed fields are updated. If defined, delete the BUS_ACTVTY_PRCS_PE_PIG.
If errors have occurred update the Proposal Instance Transfer Status to ERROR.
If no errors occur, update the Proposal Instance Transfer Status to COMPLETE.
Insert a record into the PROPOSAL_INSTANCE_UNIT_SET table. This is to help identify which proposal created which unit set version.
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-US - System Extract Creation Date: date and time |
|---|
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 after the job has finished processing (Total Proposals in error).
When an error is logged; 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 must be deleted before the 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.
Page last modified: 31 May, 2012 3:56 PM
History Information:
| Release Version | Project | Change to Document |
| 15.0 | 1722 - 11g Upgrade | Help page rewitten for 11g. |