| Top of Proposals | Index | Table of Contents | Feedback | |
This job transfers approved Unit Set proposals from the Proposal (PAA) 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 updates 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 appears 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:
5. Then update the status of the proposal.
6. Generate the completion message in the run log.
The following provides a more detailed description of the actions of this job.
Note: This job calls the package CRSK_PRC_CAPS_UNIT_SET_DATA which extracts and transfers each of the fields from the Proposals (PAA) subsystem into the CRS subsystem.
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.
| System Proposal 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 | Delete and validate (delete existing data). |
| NEW-US-V | Y | N | Y | Ignore (New data entered but now deleted). |
| 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 (update existing 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). |
| NEW-USRDEF | Any | Any | Any | Not transferred. |
The following data is processed:
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.
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 BAPs should be inserted, updated or deleted using the system outcome type and the insert, updated and deleted indicators for the proposal instance.
Complete all BAP validations. If an error occurs, log the error to the s_extract_record.
If it is a new version of the unit set then insert a record into the BAP table with the new BAP sequence number.
Determine if the people in the BAP should be inserted, updated or deleted using the system outcome type and the insert, updated and deleted indicators for the proposal instance.
Complete all BAP person/PIG validations. If an error occurs, log the error to the s_extract_record.
If it is a new version of the unit set then insert a record into the BAP person/PIG table with the new BAP sequence number, otherwise update the BAP person/PIG record so that only the changed fields are changed.
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 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 marked for removal within the proposal, delete the unit set note.
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 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.
Note: New, updated or deleted 'Plain Rule Text' associated with a Rule is also included in the transfer.
Determine if the unit set graduation statement should be inserted, updated or deleted using the System Outcome Type and the Insert, Updated and Deleted indicators for the Proposal Instance.
If it is a new version of the unit set statement then insert a record into the UNIT_SET_STATEMENT table with the new Unit Set Statement Sequence Number. For the update, build the statement dynamically to ensure only changed fields are updated. If marked for removal within the Proposal, delete the unit set statement.
Determine if the unit set Structure and Dependencies should be inserted, updated or deleted using the System Outcome Type and the Insert, Updated and Deleted indicators for the Proposal Instance.
If it is a new version of the unit set, then insert a record into the UNIT_SET_STRUCTURE table with the new Unit Set Structure Sequence Number, ID and Structure Start Date. In addition, apply a Relationship Start Date to all Relationships and curriculum items belonging to the Structure and/or Dependencies.
In addition, end-date the latest Structure and Dependencies instances for the unit set version in the CRS subsystem.
If marked for removal within the Proposal, delete the unit set Structure and Dependencies.
User defined data associated with each element (listed above), is transferred into the US_USER_DEF_DATA_STRUCTURE and US_USER_DEF_DATA tables in the Course Structure and Planning (CRS) subsystem and referenced to the unit set.
This user defined data may be transferred back to the PAA subsystem in the case where a new Proposal is created in PAA that is based on the same existing unit set.
Note: Each item in a Proposal has a Sequence number that identifies the Proposal data it is associated with and therefore which curriculum item (i.e. Unit Set) it will be associated with in the CRS subsystem.
The process for transferring user defined data from PAA to CRS is as follows:
| USUDDS Column (Primary Key) values: | Data Source from Proposals: |
|---|---|
| STRUCTURE_SEQUENCE_NUMBER | PROPOSAL_INSTANCE_ITEM.PROPOSAL_INSTANCE_ ELEMENT_SEQUENCE_NUMBER |
| PROPOSAL_ELEMENT_CD | PROPOSAL_INSTANCE_ITEM.PROPOSAL_ELEMENT_CD |
| SUP_STRUCTURE_SEQUENCE_NUMBER | If the PROPOSAL_INSTANCE_ITEM.PROPOSAL_ELEMENT_CD is a User Defined element, then V_SUP_STRUCTURE_SEQUENCE |
| SUP_PROPOSAL_ELEMENT_CD | If the PROPOSAL_INSTANCE_ITEM.PROPOSAL_ELEMENT_CD is a User Defined element, then V_SUP_PROPOSAL_ELEMENT_CD |
| STRUCTURE_LABEL | PROPOSAL_ELEMENT.MENU_TITLE |
| USUDD Column (Primary Key) values: | Data Source from Proposals: |
|---|---|
| UNIT_SET_CD | V_UNIT_SET_CD |
| VERSION_NUMBER | V_UNIT_SET_VERSION |
| PROPOSAL_ELEMENT_CD | PROPOSAL_INSTANCE_ITEM.PROPOSAL_ELEMENT_CD |
| PROPOSAL_ITEM_CD | PROPOSAL_INSTANCE_ITEM.PROPOSAL_ITEM_CD |
| STRUCTURE_SEQUENCE_NUMBER | V_STRUCTURE_SEQUENCE_NUMBER |
| TABLE_NAME | See the Field Values table below for this value. |
| SEQUENCE_NUMBER | US_USER_DEF_DATA_SEQUENCE_NUMBER.nextval, new sequential number to be created |
| EFFECTIVE _START_DT | SYSDATE |
| FIELDS 1 to 8 | Field values are set depending on the column mappings between the CRS table Primary Key columns, FIELDS 1 to 8 and TABLE_NAME. See the Field Values table below for these values. |
| EFFECTIVE_END_DT | NULL |
| S_FIELD_TYPE | S_PROPOSAL_ITEM.S_FIELD_TYPE |
| PROMPT | NVL(PROPOSAL_ITEM.OVERRIDE_PROMPT, S_PROPOSAL_ITEM.PROMPT) |
| FIELD_LENGTH | S_PROPOSAL_ITEM.FIELD_LENGTH |
| CHARACTER_FIELD | PROPOSAL_INSTANCE_ITEM.CHARACTER_FIELD |
| NUMBER_FIELD | PROPOSAL_INSTANCE_ITEM.NUMBER_FIELD |
| DATE_FIELD | PROPOSAL_INSTANCE_ITEM.DATE_FIELD |
| CLOB_FIELD | PROPOSAL_INSTANCE_ITEM.CLOB_FIELD |
| Source (System Element Code): | FIELD 1 | FIELD 2 | FIELD 3 | FIELD 4 | TABLE_NAME |
|---|---|---|---|---|---|
| UNITSET | NULL | NULL | NULL | NULL | UNIT_SET |
| USCRSTYPE | COURSE _TYPE | NULL | NULL | NULL | UNIT_SET_COURSE _TYPE |
| USFOS | FIELD_ OF_ STUDY | NULL | NULL | NULL | UNIT_SET _FIELD _OF _STUDY |
| USBAP | BAPPP. BUSINESS _ACTIVITY _PROCESS _CD | BAPPP. BAPTY_ SEQUENCE _NUMBER | BAPPP. SEQUENCE _NUMBER | NULL | BAP |
| USNOTE | REEFERENCE _NUMBER | NULL | NULL | NULL | UNIT_SET _NOTE |
| USRULE | NULL | NULL | NULL | NULL | UNIT_SET |
| USGRDSTMNT | SEQUENCE _NUMBER | NULL | NULL | NULL | UNIT_SET _STATEMENT |
| USER-US | NULL | NULL | NULL | NULL | UNIT_SET |
For a list of user defined data for each core System Element, see Proposals Unit Set System Data.
Deletion of User Defined Data in the CRS Subsystem:
If data is manually deleted from forms or tables in the CRS subsystem by an administrator, any associated child user defined data is also deleted. User defined data is located using the primary key value of its parent data. (Course user defined data is stored in the US_USER_DEF_DATA table.)
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 |
|---|
Page last modified: 25 June, 2014 4:18 PM
History Information:
| Release Version | Project | Change to Document |
| 17.0 | 1955 - Course Structures in PAA | Added Unit Set Structure data to section 5. |
| 17.0 | 1994 - New CS&P Data | Added a new section for user defined data and related information. |
| 16.1 | 1820 - Phase 3 | Added a note in the Job Detail section & section d), & added table values to the last items in the list. |
| 15.0.0.2 | 1722 - 11g Upgrade | Help page revised. |
| 13.0 | 1578 - CAPS 3 | New Help page. |