Only Standard Subjects (subject_container.subject_type = "Standard") will be backfilled to CIS. Administrative and Cross Registration subjects will not be backfilled to CIS.

Subjects for current Academic Year will be backfilled to CIS for Catalog Year. ie. if a subject is edited for current year say 2020SU , this subject will be backfilled to CatalogYear 2021FA in CIS.

Proposed as well as Approved subjects are backfilled to CIS. Proposed subjects in CIS is used by Scheduling application.

Subject data from SCASUBJI does not backfill to CIS.

Only Standard Subjects for which the Effective From Term (subject_template.effective_from_term) is in the current Academic Year or the Catalog Year will be backfilled to the CIS tables. This condition is implemented in CIS Batch Process using subject_mgmt_config value ranges between cisBackfillAllowedFromYear _and{}cisBackfillAllowedUptoYear._ Please note that if CIS is backfilled using CIS Backfill API this effective_from_term condition is skipped and not checked.

Backfill Data Flow Diagram

*Updated 6/16/2017

Technical Documentation

CIS Backfill is implemented using Mule flows in Anypoint Studio IDE. Both CIS Batch process and CIS Backfill API uses the same flows and hence processing logic is the same for both except for how it is called. Former is a batch process and picks up records from database at regular intervals while API is called one queueId at a time. When CIS batch picks up records effective_from_term condition is also checked while API ignores this condition and tries to backfill any queueId given to it as long as it is a standard subject.

When a subject is edited/created, multiple queue records may be created in subject_backfill_queue table. All the generated record(s) need to be processed to reflect the status of the subject correctly in CIS or MITSIS. If any queue record has a processing error in CIS all subsequent records for the subject and its related subjects will be blocked and will not be processed until the error is cleared and subject_backfill_error table is cleared for the subject.

CIS Backfill uses legacy CIS oracle stored procedures (with minimal changes) so as to backfill according to the legacy CIS business rules. There are some new stored procedures too as explained under Stored Procedures section below.

A major change in legacy business rule is that a subject number need not be archived before it can be re-used.

Backfill Processing Logic

CIS backfill processing processes one subject_backfill_queue record at a time. This record is usually referred to as just queue record.

checks if the record change needs to be backfilled. If backfill_cis = Y and backfill_process_type <> SR and new_term_code and new_term_code within the range of gate config values, backfill is attempted on the record.

SCASUBJI changes are not backfilled to CIS. This is because legacy CIS only saved catalog year changes in CIS and so the stored procedures only support that functionality. So there were some issues with using existing CIS stored procedures and versioning. The major issue is current year changes, but we decided to be consistent and not backfill anything from SCASUBJI - since Catalog Year changes can be made via CIM Courses (and the Admin Save function) if necessary. So SCASUBJI changes are marked with backfill_CIS = N and so CIS backfill is skipped.

all subjects are backfilled to CIS only to catalog year. ie. a subject edited for current year will be backfilled to CIS only to catalog year (one year ahead). This subject can then be back dated (backfill_process_type=BD) to the current year if needed.

previous_data column (old data column) and new_data column is read from queue record. Note that these columns could have different JSON structure according to backfill_process_type.

old data and new data is read and compared to determine if the queue record was a special change operation - renumber (MR), master swap (MS) and partial master swap (PMS) or whether the operation is a mere new subject creation or editing an existing subject or deletion of a proposed subject.

always delete the subject's proposed record before adding a proposed change or processing an approved change. Deleting a proposed change is by calling legacy SCRCI_POST_PRC procedure with in_request parameter as 'Delete'.

if backfill_process_type = DE , the subject is assumed to be of proposed type and is to be deleted. Approved subjects can only be inactivated and cannot be deleted. Flow backfill-cis-delete-in-progress deletes a proposed subject just like any delete in the previous bullet.

if old data is null and new data exists, the subject is probably a brand new subject. So verify if the new subject number is valid using legacy stored procedure SCRCI_NEW_RECORD_PRC. If the subject number is valid, create the subject using SP_CR_CIS_SUBJECT_BACKFILL with in_request_type parameter as 'Add' .

if editing an existing subject is the operation, then call SP_CR_CIS_SUBJECT_BACKFILL with in_request_type parameter as 'NewVersion.IW.Y'.

if operation = PMS (partial master swap), mule flow backfill_cis_partial_masterswap adds the old number as cross listed child and then does a master swap operation.

master calculation - during all these operations if the subject has an equivalent (EQ) or scheduling relationship (MW), then scrci_cluster should have an entry. In scrci_cluster one of the EQ/MW has to be the master. The master calculation is done so as to mimic legacy CIS application as much as possible. In legacy CIS application, master was always known to the user and hence EQ and SR child subjects in the cluster was always added to the master only. To simulate this in the Mule flow, scrci_cluster entries are checked to see if a master already exists. If a master exists, then that master is edited using stored procedure SP_CR_CIS_SUBJECT_BACKFILL to add/bookend equivalents and scheduling relationships. If a master does not exist and if this is a new cluster, then the current subject being edited is made the master and EQ/SR is added to this subject.

scrci_proposal.rationale will be standard text Backfilled from CIMCourses for all subjects except for TGrade subjects which will be Backfilled from CIMCourses . The actual rationale for any change is CIM only data.

scrci_proposal.compare_version will not be populated. This field was used for workflow management in legacy CIS application.

SCRCI_POST_PRC - is the head of legacy stored procedure tree which initiates a subject/seminar creation or editing. This calls other procedures as needed according to "in_request" parameter value - 'Add', 'Delete', NewVersion'. To make this procedure versatile "virtual_columns" parameter is used which is actually a long list of optional parameters.

SCRCI_PROPOSAL_PRC - called by SCRCI_POST_PRC for subject creation and modifications.

SCRCI_SEMINAR_PRC - called by SCRCI_POST_PRC for seminar creation and modifications.

SCRCI_TERM_PLAN_PRC - called by SCRCI_POST_PRC for term plan only changes.

SCRCI_NEW_RECORD_PRC - called before a new subject is created to check if the subject number is valid for the term.

SCRCI_PROPOSAL_DELETE - deletes a proposed subject.

SCRCI_VALIDATE_CLUSTER_CHILD - procedure which checks if the child is valid in an equivalent or meetsWith cluster.

New stored procedures were also written for CimCourses project. Some were written as a wrapper to simulate stepping through different workflow stages in CIS or to streamline operations like master swap which was done manually earlier.

SP_CR_CIS_SUBJECT_BACKFILL - simulates progressing through old CIS workflow calling multiple legacy stored procedures. This is the main procedure called for creating or editing a subject/seminar. "virtual_columns" parameter in this is the exact same expected by SCRCI_POST_PRC and these optional parameters are built in getVirtualColumnsForSP method call in CISSubject java class.

SP_CIS_BACKFILL_MASTER_SWAP - implements a master swap. Since old CIS application did not have a method to do this easily, this was a manual process and is now replace dwith this stored procedure.

SP_BACKFILL_SCRCI_URLS - scrci_urls were manually updated from a separate application and uploading to tables was a manual process. A new stored procedure was added so that URLs can be updated from SCASUBJI.

SP_BACKFILL_MITSIS_MW_CLUSTER - this is a helper procedure which is used to reset cluster_type in scrcu_table for meets-with subjects which does not have a child and this is called during MITSIS processing of an SR queue record.

SP_CR_SUBJECT_BACKFILL_ERROR - procedure which inserts errored subject keys into subject_backfill_errorkey table so that once a queue record has an error, all other queue records for those subjects are kept on hold.

SP_MOVE_TO_SUB_BACKFILL_Q_ARCH - queue records whose CIS and MITSIS backfill processes are completed are moved to subject_backfill_queue_archive table for record keeping log. This moving logic is done by this procedure.