/*
*** STANDARD DISCLAIMER ***
Use any of this code at your own risk.  By using any of this code, 
you agree to hold harmless Chabot - Las Positas Community College District or
any of the authors from any situation that may arise from the use of this code.
*/

set echo off
set termout off
set serveroutput on
set feedback off
set time off
set timing off
set pause off
set verify off
set tab off
set wrap off 

-- debug
-- set echo on
-- set termout on
-- set feedback on
-- set verify on

/*****************************************************************************
Program:        syqfxcu.sql

Author:         Dave Suhr

Date Created:   September 2006

Description:    Audit/Fix Curriculum data.
                This program compares the new Banner 7 tables
                SORLCUR and SORLFOS with the older Banner 6 tables
                SRBRECR, SARADAP, SGBSTDN and SHRDGMR and looks for
                out of sync data or missing rows.

                This program is meant to be used while we are running
                the phased in approach of the new curriculum tables where
                both the old tables and new tables are supposed to be in
                sync.  This phased in approach is taking place during
                Banner 7.x lifecyle.

                OLD Curriculum and Field Of Study tables and record types:
                    (R) SRBRECR = RECRUIT DATA    
                    (A) SARADAP = ADMISSIONS DATA 
                    (L) SGBSTDN = LEARNER         
                    (O) SHRDGMR = OUTCOME         

                NEW Curriculum and Field of Study record types:
                    (C) SORLCUR which has program, level, college, 
                                degree and campus data for all modules 
                                RECRUIT, ADMISSIONS, LEARNER, and OUTCOME and
                                contains unlimited rows per term.
                    (F) SORLFOS which has majors, minors and concentrations 
                                and contains unlimited rows per term.

                NEW packages
                        SB_FIELDOFSTUDY          updates   SORLFOS
                                                 called in SOKLCUR
                        SB_CURRICULUM            updates   SORLCUR 
                                                 called in SOKLCUR
                        SB_RECRUIT               backfills SRBRECR
                        SB_ADMISSIONSAPPLICATION backfills SARADAP
                        SB_LEARNER               backfills SGBSTDN
                        SB_LEARNEROUTCOME        backfills SHRDGMR

Parms:          01) Audit or Update Mode (A or U)
                02) Beginning Term that you want to check for.
                03) Ending Term that you want to check for.

Output:         syqfxcu.lis - detail error report.
                syqfxcu.sum - summary count report.
                syqfxcu.csv - csv file of key tables and columns
                              that can be used to check errors against.

Instructions:   1. Change the 3 define c_parm_1 thru c_parm_3 statements
                   to reflect your desired values.
                   Suggestion, run in audit mode for just a few terms
                   to see how your run times are.
                2. Set your oracle environment to the desired instance.
                3. Use the following unix command to run the process:
                     sqlplus userid/passwd @syqfxcu_a > syqfxcu_a.log 2>&1 &

Revision Hist:

*****************************************************************************/

--
-- Define the 3 Job Parameters (change 3 parm values as needed for the run)
--
--
-- Define Parm 1 as A for Audit mode or U for Update Mode.
--
define  c_parm_1 = 'A'
column  c_parm_run_mode new_value c_parm_run_mode
column  c_parm_run_mode_desc new_value c_parm_run_mode_desc
select  upper('&c_parm_1')              c_parm_run_mode,
        decode(upper('&c_parm_1'),'A','AUDIT MODE','U','UPDATE MODE',
               'UNKNOWN MODE')          c_parm_run_mode_desc
from    sys.dual;

--
-- Define Parm 2 as the beginning term to process.
--
define  c_parm_2 = '200501'
column  c_parm_beg_term_code new_value c_parm_beg_term_code
column  c_parm_beg_term_desc new_value c_parm_beg_term_desc
select  upper(&c_parm_2)               c_parm_beg_term_code,
        stvterm_desc                   c_parm_beg_term_desc
from    stvterm
where   stvterm_code = '&c_parm_2';

--
-- Define Parm 3 as the beginning term to process.
--
define  c_parm_3 = '200603'
column  c_parm_end_term_code new_value c_parm_end_term_code
column  c_parm_end_term_desc new_value c_parm_end_term_desc
select  upper(&c_parm_3)               c_parm_end_term_code,
        stvterm_desc                   c_parm_end_term_desc
from    stvterm
where   stvterm_code = '&c_parm_3';

--
-- Set up global variables
--
column  c_release_no noprint new_value c_release_no
select  '7.2' c_release_no
from    sys.dual;

column  c_run_date noprint new_value c_run_date
column  c_run_time noprint new_value c_run_time
column  c_user noprint new_value c_user
select  user c_user,
        to_char(sysdate, 'fmMonth dd, yyyy') c_run_date, 
        to_char(sysdate, 'hh24:mi') c_run_time
from    sys.dual;

column  gubinst_name noprint new_value c_gubinst_name
select  gubinst_name
from    gubinst
where   gubinst_key = 'INST';

column  c_job_name noprint new_value c_job_name
select  'syqfxcu_a' c_job_name from sys.dual;

-- Load variables from soblmod table
column c_max_recruit_lcur     new_value  c_max_recruit_lcur
column c_max_recruit_lfos     new_value  c_max_recruit_lfos
column c_max_admissions_lcur  new_value  c_max_admissions_lcur
column c_max_admissions_lfos  new_value  c_max_admissions_lfos
column c_max_learner_lcur     new_value  c_max_learner_lcur
column c_max_learner_lfos     new_value  c_max_learner_lfos
column c_max_outcome_lcur     new_value  c_max_outcome_lcur
column c_max_outcome_lfos     new_value  c_max_outcome_lfos
select  soblmod_max_curricula   c_max_recruit_lcur,
        soblmod_max_majors      c_max_recruit_lfos
from    soblmod
where   soblmod_lmod_code = 'RECRUIT';
select  soblmod_max_curricula   c_max_admissions_lcur,
        soblmod_max_majors      c_max_admissions_lfos
from    soblmod
where   soblmod_lmod_code = 'ADMISSIONS';
select  soblmod_max_curricula   c_max_learner_lcur,
        soblmod_max_majors      c_max_learner_lfos
from    soblmod
where   soblmod_lmod_code = 'LEARNER';
select  soblmod_max_curricula   c_max_outcome_lcur,
        soblmod_max_majors      c_max_outcome_lfos
from    soblmod
where   soblmod_lmod_code = 'OUTCOME';

--
-- Create Message Code table to be used for reporting purposes.
--
-- Coding scheme for messages is
--   1st char indicates the driver table (See list of tables in comments).
--   2nd char indicates the secondary table (See list of tables in comments).
--   3rd-5th char indicates MAT=Match MIS=Secondary is Missing DIF=Different.
--   6th-7th char is a unique sequence number within first 5 positions.
-- error_update_ind N means no update, R means report only, 
--                  U means update if running in update mode.
--
drop table syqfxcu_message_codes;
create table syqfxcu_message_codes as
select  'ACDIF01'                       message_code,
        'SARADAP LEVL           and SORLCUR ADMISSIONS Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'ACDIF02'                       message_code,
        'SARADAP LEVL_2         and SORLCUR ADMISSIONS Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'ACMAT01'                       message_code,
        'SARADAP LEVL           and SORLCUR ADMISSIONS Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'ACMAT02'                       message_code,
        'SARADAP LEVL_2         and SORLCUR ADMISSIONS Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'ACMIS01'                       message_code,
        'SARADAP LEVL   OK      and SORLCUR ADMISSIONS Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'ACMIS02'                       message_code,
        'SARADAP LEVL_2 OK      and SORLCUR ADMISSIONS Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'ACMIS03'                       message_code,
        'SARADAP LEVL or LEVL_2 Missing and SORLCUR ADMISSIONS OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'LCDIF01'                       message_code,
        'SGBSTDN LEVL           and SORLCUR LEARNER Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'LCDIF02'                       message_code,
        'SGBSTDN LEVL_2         and SORLCUR LEARNER Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'LCMAT01'                       message_code,
        'SGBSTDN LEVL           and SORLCUR LEARNER Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'LCMAT02'                       message_code,
        'SGBSTDN LEVL_2         and SORLCUR LEARNER Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'LCMIS01'                       message_code,
        'SGBSTDN LEVL   OK      and SORLCUR LEARNER Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'LCMIS02'                       message_code,
        'SGBSTDN LEVL_2 OK      and SORLCUR LEARNER Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'LCMIS03'                       message_code,
        'SGBSTDN LEVL   Missing and SORLCUR LEARNER OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'LCMIS04'                       message_code,
        'SGBSTDN LEVL_2 Missing and SORLCUR LEARNER OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'OCDIF01'                       message_code,
        'SHRDGMR COLL           and SORLCUR OUTCOME Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'OCDIF02'                       message_code,
        'SHRDGMR COLL_2         and SORLCUR OUTCOME Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'OCMAT01'                       message_code,
        'SHRDGMR COLL           and SORLCUR OUTCOME Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'OCMAT02'                       message_code,
        'SHRDGMR COLL_2         and SORLCUR OUTCOME Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'OCMIS01'                       message_code,
        'SHRDGMR COLL   OK      and SORLCUR OUTCOME Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'OCMIS02'                       message_code,
        'SHRDGMR COLL_2 OK      and SORLCUR OUTCOME Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'OCMIS03'                       message_code,
        'SHRDGMR COLL   Missing and SORLCUR OUTCOME OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'OCMIS04'                       message_code,
        'SHRDGMR COLL_2 Missing and SORLCUR OUTCOME OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'AFDIF01'                       message_code,
        'SARADAP MAJR_1         and SORLFOS MAJOR Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'AFDIF02'                       message_code,
        'SARADAP MAJR_2         and SORLFOS MAJOR Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'AFMAT01'                       message_code,
        'SARADAP MAJR_1         and SORLFOS MAJOR Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'AFMAT02'                       message_code,
        'SARADAP MAJR_2         and SORLFOS MAJOR Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'AFMIS01'                       message_code,
        'SARADAP MAJR_1 OK      and SORLFOS MAJOR Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'AFMIS02'                       message_code,
        'SARADAP MAJR_2 OK      and SORLFOS MAJOR Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'AFMIS03'                       message_code,
        'SARADAP MAJR_1 Missing and SORLFOS MAJOR OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'AFMIS04'                       message_code,
        'SARADAP MAJR_2 Missing and SORLFOS MAJOR OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'LFDIF01'                       message_code,
        'SGBSTDN MAJR_1         and SORLFOS MAJOR Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'LFDIF02'                       message_code,
        'SGBSTDN MAJR_2         and SORLFOS MAJOR Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'LFMAT01'                       message_code,
        'SGBSTDN MAJR_1         and SORLFOS MAJOR Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'LFMAT02'                       message_code,
        'SGBSTDN MAJR_2         and SORLFOS MAJOR Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'LFMIS01'                       message_code,
        'SGBSTDN MAJR_1 OK      and SORLFOS MAJOR Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'LFMIS02'                       message_code,
        'SGBSTDN MAJR_2 OK      and SORLFOS MAJOR Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'LFMIS03'                       message_code,
        'SGBSTDN MAJR_1 Missing and SORLFOS MAJOR OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'LFMIS04'                       message_code,
        'SGBSTDN MAJR_2 Missing and SORLFOS MAJOR OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'OFDIF01'                       message_code,
        'SHRDGMR MAJR_1         and SORLFOS MAJOR Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'OFDIF02'                       message_code,
        'SHRDGMR MAJR_2         and SORLFOS MAJOR Different.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'OFMAT01'                       message_code,
        'SHRDGMR MAJR_1         and SORLFOS MAJOR Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'OFMAT02'                       message_code,
        'SHRDGMR MAJR_2         and SORLFOS MAJOR Match.'
                                        message_desc,
        'N'                             error_update_ind
from    dual
union all
select  'OFMIS01'                       message_code,
        'SHRDGMR MAJR_1 OK      and SORLFOS MAJOR Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'OFMIS02'                       message_code,
        'SHRDGMR MAJR_2 OK      and SORLFOS MAJOR Missing.'
                                        message_desc,
        'U'                             error_update_ind
from    dual
union all
select  'OFMIS03'                       message_code,
        'SHRDGMR MAJR_1 Missing and SORLFOS MAJOR OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
union all
select  'OFMIS04'                       message_code,
        'SHRDGMR MAJR_2 Missing and SORLFOS MAJOR OK.'
                                        message_desc,
        'R'                             error_update_ind
from    dual
;

--
-- Audit Curriculum tables and write summary counts of those that match.
--
drop table syqfxcu_match_counts;
create table syqfxcu_match_counts as
select  '       '                       message_code,
        '      '                        term_code,
        0                               message_count
from    dual;

-- SARADAP code 1 matches SORLCUR
insert  into syqfxcu_match_counts
select  'ACMAT01'                       message_code,
        saradap_term_code_entry         term_code,
        count(*)                        message_count
from    spriden, sorlcur, saradap
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no)
and     saradap_levl_code          is not null
and     nvl(sorlcur_levl_code,'*') = nvl(saradap_levl_code,'*')
and     nvl(sorlcur_coll_code,'*') = nvl(saradap_coll_code_1,'*')
-- CLPCCD degc code blank on saradap and zero on sorlcur
-- and     nvl(sorlcur_degc_code,'*') = nvl(saradap_degc_code_1,'*')
and     nvl(sorlcur_camp_code,'#') = nvl(saradap_camp_code,'*')
and     nvl(sorlcur_program,'*')   = nvl(saradap_program_1,'*')
and     nvl(sorlcur_curr_rule,0)   = nvl(saradap_curr_rule_1,0)
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'ACMAT01',
         saradap_term_code_entry;

-- SARADAP code 2 matches SORLCUR
insert  into syqfxcu_match_counts
select  'ACMAT02'                       message_code,
        saradap_term_code_entry         term_code,
        count(*)                        message_count
from    spriden, sorlcur, saradap
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no)
and     saradap_levl_code_2        is not null
and     nvl(sorlcur_levl_code,'*') = nvl(saradap_levl_code_2,'*')
and     nvl(sorlcur_coll_code,'*') = nvl(saradap_coll_code_2,'*')
-- CLPCCD degc code blank on saradap and zero on sorlcur
-- and     nvl(sorlcur_degc_code,'*') = nvl(saradap_degc_code_2,'*')
and     nvl(sorlcur_camp_code,'#') = nvl(saradap_camp_code_2,'*')
and     nvl(sorlcur_program,'*')   = nvl(saradap_program_2,'*')
and     nvl(sorlcur_curr_rule,0)   = nvl(saradap_curr_rule_2,0)
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'ACMAT02',
         saradap_term_code_entry;

-- SGBSTDN code 1 matches SORLCUR
insert  into syqfxcu_match_counts
select  'LCMAT01'                       message_code,
        sgbstdn_term_code_eff           term_code,
        count(*)                        message_count
from    spriden, sorlcur, sgbstdn
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 1
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 1)
and     sgbstdn_levl_code                is not null
and     nvl(sorlcur_levl_code,'*')       = nvl(sgbstdn_levl_code,'*')
and     nvl(sorlcur_coll_code,'*')       = nvl(sgbstdn_coll_code_1,'*')
and     nvl(sorlcur_degc_code,'*')       = nvl(sgbstdn_degc_code_1,'*')
and     nvl(sorlcur_camp_code,'#')       = nvl(sgbstdn_camp_code,'*')
and     nvl(sorlcur_program,'*')         = nvl(sgbstdn_program_1,'*')
and     nvl(sorlcur_term_code_admit,'*') = nvl(sgbstdn_term_code_admit,'*')
and     nvl(sorlcur_curr_rule,0)         = nvl(sgbstdn_curr_rule_1,0)
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'LCMAT01',
         sgbstdn_term_code_eff;

-- SGBSTDN code 2 matches SORLCUR
insert  into syqfxcu_match_counts
select  'LCMAT02'                       message_code,
        sgbstdn_term_code_eff           term_code,
        count(*)                        message_count
from    spriden, sorlcur, sgbstdn
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 2
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 2)
and     sgbstdn_levl_code_2              is not null
and     nvl(sorlcur_levl_code,'*')       = nvl(sgbstdn_levl_code_2,'*')
and     nvl(sorlcur_coll_code,'*')       = nvl(sgbstdn_coll_code_2,'*')
and     nvl(sorlcur_degc_code,'*')       = nvl(sgbstdn_degc_code_2,'*')
and     nvl(sorlcur_camp_code,'#')       = nvl(sgbstdn_camp_code_2,'*')
and     nvl(sorlcur_program,'*')         = nvl(sgbstdn_program_2,'*')
and     nvl(sorlcur_term_code_admit,'*') = nvl(sgbstdn_term_code_admit_2,'*')
and     nvl(sorlcur_curr_rule,0)         = nvl(sgbstdn_curr_rule_2,0)
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'LCMAT02',
         sgbstdn_term_code_eff;

-- SHRDGMR code 1 matches SORLCUR
insert  into syqfxcu_match_counts
select  'OCMAT01'                       message_code,
        shrdgmr_term_code_sturec        term_code,
        count(*)                        message_count
from    spriden, sorlcur, shrdgmr
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_priority_no = 1
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 1)
and     shrdgmr_coll_code_1        is not null
and     nvl(sorlcur_levl_code,'*') = nvl(shrdgmr_levl_code,'*')
and     nvl(sorlcur_coll_code,'*') = nvl(shrdgmr_coll_code_1,'*')
and     nvl(sorlcur_degc_code,'*') = nvl(shrdgmr_degc_code,'*')
and     nvl(sorlcur_camp_code,'#') = nvl(shrdgmr_camp_code,'*')
and     nvl(sorlcur_program,'*')   = nvl(shrdgmr_program,'*')
and     nvl(sorlcur_curr_rule,0)   = nvl(shrdgmr_curr_rule_1,0)
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'OCMAT01',
         shrdgmr_term_code_sturec;

-- SHRDGMR code 2 matches SORLCUR
insert  into syqfxcu_match_counts
select  'OCMAT02'                       message_code,
        shrdgmr_term_code_sturec        term_code,
        count(*)                        message_count
from    spriden, sorlcur, shrdgmr
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_priority_no = 2
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 2)
and     shrdgmr_coll_code_2        is not null
and     nvl(sorlcur_levl_code,'*') = nvl(shrdgmr_levl_code,'*')
and     nvl(sorlcur_coll_code,'*') = nvl(shrdgmr_coll_code_2,'*')
and     nvl(sorlcur_degc_code,'*') = nvl(shrdgmr_degc_code,'*')
and     nvl(sorlcur_camp_code,'#') = nvl(shrdgmr_camp_code_2,'*')
and     nvl(sorlcur_program,'*')   = nvl(shrdgmr_program,'*')
and     nvl(sorlcur_curr_rule,0)   = nvl(shrdgmr_curr_rule_2,0)
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'OCMAT02',
         shrdgmr_term_code_sturec;

-- SARADAP code 1 matches SORLFOS
insert  into syqfxcu_match_counts
select  'AFMAT01'                       message_code,
        saradap_term_code_entry         term_code,
        count(*)                        message_count
from    spriden, sorlfos, sorlcur, saradap
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = saradap_pidm
and     sorlfos_term_code   = saradap_term_code_entry
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 1
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_priority_no = 1
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no
        and     sorlcur_priority_no = 1)
and     saradap_majr_code_1 is not null
and     nvl(sorlfos_majr_code,'*') = saradap_majr_code_1
and     nvl(sorlcur_camp_code,'#') = nvl(saradap_camp_code,'*')
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'AFMAT01',
         saradap_term_code_entry;

-- SARADAP code 2 matches SORLFOS
insert  into syqfxcu_match_counts
select  'AFMAT02'                       message_code,
        saradap_term_code_entry         term_code,
        count(*)                        message_count
from    spriden, sorlfos, sorlcur, saradap
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = saradap_pidm
and     sorlfos_term_code   = saradap_term_code_entry
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 2
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_priority_no = 2
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no
        and     sorlcur_priority_no = 2)
and     saradap_majr_code_2 is not null
and     nvl(sorlfos_majr_code,'*') = saradap_majr_code_2
and     nvl(sorlcur_camp_code,'#') = nvl(saradap_camp_code_2,'*')
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'AFMAT02',
         saradap_term_code_entry;

-- SGBSTDN code 1 matches SORLFOS
insert  into syqfxcu_match_counts
select  'LFMAT01'                       message_code,
        sgbstdn_term_code_eff           term_code,
        count(*)                        message_count
from    spriden, sorlfos, sorlcur, sgbstdn
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = sgbstdn_pidm
and     sorlfos_term_code   = sgbstdn_term_code_eff
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 1
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 1
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 1)
and     sgbstdn_majr_code_1 is not null
and     nvl(sorlfos_majr_code,'*') = sgbstdn_majr_code_1
and     nvl(sorlcur_camp_code,'#') = nvl(sgbstdn_camp_code,'*')
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'LFMAT01',
         sgbstdn_term_code_eff;

-- SGBSTDN code 2 matches SORLFOS
insert  into syqfxcu_match_counts
select  'LFMAT02'                       message_code,
        sgbstdn_term_code_eff           term_code,
        count(*)                        message_count
from    spriden, sorlfos, sorlcur, sgbstdn
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = sgbstdn_pidm
and     sorlfos_term_code   = sgbstdn_term_code_eff
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 2
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 2
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 2)
and     sgbstdn_majr_code_2 is not null
and     nvl(sorlfos_majr_code,'*') = sgbstdn_majr_code_2
and     nvl(sorlcur_camp_code,'#') = nvl(sgbstdn_camp_code_2,'*')
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'LFMAT02',
         sgbstdn_term_code_eff;

-- SHRDGMR code 1 matches SORLFOS
insert  into syqfxcu_match_counts
select  'OFMAT01'                       message_code,
        shrdgmr_term_code_sturec        term_code,
        count(*)                        message_count
from    spriden, sorlfos, sorlcur, shrdgmr
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = shrdgmr_pidm
and     sorlfos_term_code   = shrdgmr_term_code_sturec
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 1
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 1)
and     shrdgmr_majr_code_1 is not null
and     nvl(sorlfos_majr_code,'*') = shrdgmr_majr_code_1
and     nvl(sorlcur_camp_code,'#') = nvl(shrdgmr_camp_code,'*')
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'OFMAT01',
         shrdgmr_term_code_sturec;

-- SHRDGMR code 2 matches SORLFOS
insert  into syqfxcu_match_counts
select  'OFMAT02'                       message_code,
        shrdgmr_term_code_sturec        term_code,
        count(*)                        message_count
from    spriden, sorlfos, sorlcur, shrdgmr
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = shrdgmr_pidm
and     sorlfos_term_code   = shrdgmr_term_code_sturec
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 2
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 2)
and     shrdgmr_majr_code_2 is not null
and     nvl(sorlfos_majr_code,'*') = shrdgmr_majr_code_2
and     nvl(sorlcur_camp_code,'#') = nvl(shrdgmr_camp_code_2,'*')
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code'
group by 'OFMAT02',
         shrdgmr_term_code_sturec;

--
-- Audit Curriculum tables and write detail error records for missing records
-- or those records that don't match or don't exist.
--
drop table syqfxcu_errors;
create table syqfxcu_errors as
select  0                                pidm,
        '      '                         term_code,
        '       '                        message_code,
        '          '                     id,
        '                              ' name,
        '                              ' old_data_origin,
        '                              ' old_user_id,
        '              '                 old_activity_date,
        '                              ' new_data_origin,
        '                              ' new_user_id,
        '              '                 new_activity_date,
        '                  '             old_rowid
from    dual;

-- SARADAP code 1 is different than SORLCUR
insert  into syqfxcu_errors
select  saradap_pidm                    pidm,
        saradap_term_code_entry         term_code,
        'ACDIF01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        saradap_data_origin             old_data_origin,
        saradap_user_id                 old_user_id,
        to_char(saradap_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlcur_data_origin             new_data_origin,
        sorlcur_user_id                 new_user_id,
        to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_rowid
from    spriden, sorlcur, saradap a
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no)
and     saradap_levl_code                   is not null
and     (       nvl(sorlcur_levl_code,'*')  <> nvl(saradap_levl_code,'*')
        or      nvl(sorlcur_coll_code,'*')  <> nvl(saradap_coll_code_1,'*')
-- CLPCCD degc code blank on saradap and zero on sorlcur
--        or      nvl(sorlcur_degc_code,'*')  <> nvl(saradap_degc_code_1,'*')
        or      nvl(sorlcur_camp_code,'#')  <> nvl(saradap_camp_code,'*')
        or      nvl(sorlcur_program,'*')    <> nvl(saradap_program_1,'*')
        or      nvl(sorlcur_curr_rule,0)    <> nvl(saradap_curr_rule_1,0)
        )
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SARADAP code 2 is different than SORLCUR
insert  into syqfxcu_errors
select  saradap_pidm                    pidm,
        saradap_term_code_entry         term_code,
        'ACDIF02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        saradap_data_origin             old_data_origin,
        saradap_user_id                 old_user_id,
        to_char(saradap_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlcur_data_origin             new_data_origin,
        sorlcur_user_id                 new_user_id,
        to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_rowid
from    spriden, sorlcur, saradap a
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no)
and     saradap_levl_code_2                 is not null
and     (       nvl(sorlcur_levl_code,'*')  <> nvl(saradap_levl_code_2,'*')
        or      nvl(sorlcur_coll_code,'*')  <> nvl(saradap_coll_code_2,'*')
-- CLPCCD degc code blank on saradap and zero on sorlcur
--        or      nvl(sorlcur_degc_code,'*')  <> nvl(saradap_degc_code_2,'*')
        or      nvl(sorlcur_camp_code,'#')  <> nvl(saradap_camp_code_2,'*')
        or      nvl(sorlcur_program,'*')    <> nvl(saradap_program_2,'*')
        or      nvl(sorlcur_curr_rule,0)    <> nvl(saradap_curr_rule_2,0)
        )
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SARADAP code 1 does not have a matching SORLCUR
insert  into syqfxcu_errors
select  saradap_pidm                    pidm,
        saradap_term_code_entry         term_code,
        'ACMIS01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        saradap_data_origin             old_data_origin,
        saradap_user_id                 old_user_id,
        to_char(saradap_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_rowid
from    spriden, saradap a
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     not exists
        (select 'x'
        from    sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no
        and     sorlcur_seqno =
                (select max(sorlcur_seqno)
                from    sorlcur
                where   sorlcur_pidm        = saradap_pidm
                and     sorlcur_term_code   = saradap_term_code_entry
                and     sorlcur_lmod_code   = 'ADMISSIONS'
                and     sorlcur_key_seqno   = saradap_appl_no))
and     saradap_levl_code           is not null
and     &c_max_admissions_lcur > 
        (select count(*)
        from    saradap
        where   saradap_pidm = a.saradap_pidm
        and     saradap_term_code_entry > a.saradap_term_code_entry)
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SARADAP code 2 does not have a matching SORLCUR
insert  into syqfxcu_errors
select  saradap_pidm                    pidm,
        saradap_term_code_entry         term_code,
        'ACMIS02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        saradap_data_origin             old_data_origin,
        saradap_user_id                 old_user_id,
        to_char(saradap_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_rowid
from    spriden, saradap a
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     not exists
        (select 'x'
        from    sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no
        and     sorlcur_seqno =
                (select max(sorlcur_seqno)
                from    sorlcur
                where   sorlcur_pidm        = saradap_pidm
                and     sorlcur_term_code   = saradap_term_code_entry
                and     sorlcur_lmod_code   = 'ADMISSIONS'
                and     sorlcur_key_seqno   = saradap_appl_no))
and     saradap_levl_code_2         is not null
and     &c_max_admissions_lcur > 
        (select count(*)
        from    saradap
        where   saradap_pidm = a.saradap_pidm
        and     saradap_term_code_entry > a.saradap_term_code_entry)
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLCUR does not have a matching SARADAP code 1
insert  into syqfxcu_errors
select  sorlcur_pidm                    pidm,
        sorlcur_term_code               term_code,
        'ACMIS03'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        ' '                             old_data_origin,
        ' '                             old_user_id,
        ' '                             old_activity_date,
        sorlcur_data_origin             new_data_origin,
        sorlcur_user_id                 new_user_id,
        to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_rowid
from    spriden, sorlcur a
where   spriden_pidm        = sorlcur_pidm
and     spriden_change_ind  is null
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = a.sorlcur_pidm
        and     sorlcur_term_code   = a.sorlcur_term_code
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = a.sorlcur_key_seqno)
and     not exists
        (select 'x'
        from    saradap
        where   saradap_pidm            = a.sorlcur_pidm
        and     saradap_term_code_entry = a.sorlcur_term_code
        and     saradap_appl_no         = a.sorlcur_key_seqno)
and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SGBSTDN code 1 is different than SORLCUR
insert  into syqfxcu_errors
select  sgbstdn_pidm                    pidm,
        sgbstdn_term_code_eff           term_code,
        'LCDIF01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        sgbstdn_data_origin             old_data_origin,
        sgbstdn_user_id                 old_user_id,
        to_char(sgbstdn_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlcur_data_origin             new_data_origin,
        sorlcur_user_id                 new_user_id,
        to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_rowid
from    spriden, sorlcur, sgbstdn a
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 1
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 1)
and     sgbstdn_levl_code                is not null
and     (       nvl(sorlcur_levl_code,'*')       <> nvl(sgbstdn_levl_code,'*')
        or      nvl(sorlcur_coll_code,'*')       <> nvl(sgbstdn_coll_code_1,'*')
        or      nvl(sorlcur_degc_code,'*')       <> nvl(sgbstdn_degc_code_1,'*')
        or      nvl(sorlcur_camp_code,'#')       <> nvl(sgbstdn_camp_code,'*')
        or      nvl(sorlcur_program,'*')         <> nvl(sgbstdn_program_1,'*')
        or      nvl(sorlcur_term_code_admit,'*') <> nvl(sgbstdn_term_code_admit,'*')
        or      nvl(sorlcur_curr_rule,0)         <> nvl(sgbstdn_curr_rule_1,0)
        )
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SGBSTDN code 2 is different than SORLCUR
insert  into syqfxcu_errors
select  sgbstdn_pidm                    pidm,
        sgbstdn_term_code_eff           term_code,
        'LCDIF02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        sgbstdn_data_origin             old_data_origin,
        sgbstdn_user_id                 old_user_id,
        to_char(sgbstdn_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlcur_data_origin             new_data_origin,
        sorlcur_user_id                 new_user_id,
        to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_rowid
from    spriden, sorlcur, sgbstdn a
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 2
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 2)
and     sgbstdn_levl_code_2              is not null
and     (       nvl(sorlcur_levl_code,'*')       <> nvl(sgbstdn_levl_code_2,'*')
        or      nvl(sorlcur_coll_code,'*')       <> nvl(sgbstdn_coll_code_2,'*')
        or      nvl(sorlcur_degc_code,'*')       <> nvl(sgbstdn_degc_code_2,'*')
        or      nvl(sorlcur_camp_code,'#')       <> nvl(sgbstdn_camp_code_2,'*')
        or      nvl(sorlcur_program,'*')         <> nvl(sgbstdn_program_2,'*')
        or      nvl(sorlcur_term_code_admit,'*') <> nvl(sgbstdn_term_code_admit_2,'*')
        or      nvl(sorlcur_curr_rule,0)         <> nvl(sgbstdn_curr_rule_2,0)
        )
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SGBSTDN code 1 does not have a matching SORLCUR
insert  into syqfxcu_errors
select  sgbstdn_pidm                    pidm,
        sgbstdn_term_code_eff           term_code,
        'LCMIS01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        sgbstdn_data_origin             old_data_origin,
        sgbstdn_user_id                 old_user_id,
        to_char(sgbstdn_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, sgbstdn a
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     not exists
        (select 'x'
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 1
        and     sorlcur_seqno =
                (select max(sorlcur_seqno)
                from    sorlcur
                where   sorlcur_pidm        = sgbstdn_pidm
                and     sorlcur_term_code   = sgbstdn_term_code_eff
                and     sorlcur_lmod_code   = 'LEARNER'
                and     sorlcur_priority_no = 1))
and     sgbstdn_levl_code                is not null
and     &c_max_learner_lcur > 
        (select count(*)
        from    sgbstdn
        where   sgbstdn_pidm = a.sgbstdn_pidm
        and     sgbstdn_term_code_eff > a.sgbstdn_term_code_eff)
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SGBSTDN code 2 does not have a matching SORLCUR
insert  into syqfxcu_errors
select  sgbstdn_pidm                    pidm,
        sgbstdn_term_code_eff           term_code,
        'LCMIS02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        sgbstdn_data_origin             old_data_origin,
        sgbstdn_user_id                 old_user_id,
        to_char(sgbstdn_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, sgbstdn a
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     not exists
        (select 'x'
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 2
        and     sorlcur_seqno =
                (select max(sorlcur_seqno)
                from    sorlcur
                where   sorlcur_pidm        = sgbstdn_pidm
                and     sorlcur_term_code   = sgbstdn_term_code_eff
                and     sorlcur_lmod_code   = 'LEARNER'
                and     sorlcur_priority_no = 2))
and     sgbstdn_levl_code_2              is not null
and     &c_max_learner_lcur > 
        (select count(*)
        from    sgbstdn
        where   sgbstdn_pidm = a.sgbstdn_pidm
        and     sgbstdn_term_code_eff > a.sgbstdn_term_code_eff)
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLCUR does not have a matching SGBSTDN code 1
insert  into syqfxcu_errors
select  sorlcur_pidm                    pidm,
        sorlcur_term_code               term_code,
        'LCMIS03'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        ' '                             old_data_origin,
        ' '                             old_user_id,
        ' '                             old_activity_date,
        sorlcur_data_origin             new_data_origin,
        sorlcur_user_id                 new_user_id,
        to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur a
where   spriden_pidm        = sorlcur_pidm
and     spriden_change_ind  is null
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 1
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = a.sorlcur_pidm
        and     sorlcur_term_code   = a.sorlcur_term_code
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 1)
and     not exists
        (select 'x'
        from    sgbstdn
        where   sgbstdn_pidm          = sorlcur_pidm
        and     sgbstdn_term_code_eff = sorlcur_term_code
        and     sgbstdn_levl_code     is not null)
and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLCUR does not have a matching SGBSTDN code 2
insert  into syqfxcu_errors
select  sorlcur_pidm                    pidm,
        sorlcur_term_code               term_code,
        'LCMIS04'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        ' '                             old_data_origin,
        ' '                             old_user_id,
        ' '                             old_activity_date,
        sorlcur_data_origin             new_data_origin,
        sorlcur_user_id                 new_user_id,
        to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur a
where   spriden_pidm        = sorlcur_pidm
and     spriden_change_ind  is null
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 2
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = a.sorlcur_pidm
        and     sorlcur_term_code   = a.sorlcur_term_code
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 2)
and     not exists
        (select 'x'
        from    sgbstdn
        where   sgbstdn_pidm          = a.sorlcur_pidm
        and     sgbstdn_term_code_eff = a.sorlcur_term_code
        and     sgbstdn_levl_code_2   is not null)
and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SHRDGMR code 1 is different than SORLCUR
insert  into syqfxcu_errors
select  shrdgmr_pidm                    pidm,
        shrdgmr_term_code_sturec        term_code,
        'OCDIF01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        shrdgmr_data_origin             old_data_origin,
        shrdgmr_user_id                 old_user_id,
        to_char(shrdgmr_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlcur_data_origin             new_data_origin,
        sorlcur_user_id                 new_user_id,
        to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur, shrdgmr a
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_priority_no = 1
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 1)
and     shrdgmr_coll_code_1        is not null
and     (       nvl(sorlcur_levl_code,'*') <> nvl(shrdgmr_levl_code,'*')
        or      nvl(sorlcur_coll_code,'*') <> nvl(shrdgmr_coll_code_1,'*')
        or      nvl(sorlcur_degc_code,'*') <> nvl(shrdgmr_degc_code,'*')
        or      nvl(sorlcur_camp_code,'#') <> nvl(shrdgmr_camp_code,'*')
        or      nvl(sorlcur_program,'*')   <> nvl(shrdgmr_program,'*')
        or      nvl(sorlcur_curr_rule,0)   <> nvl(shrdgmr_curr_rule_1,0)
        )
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SHRDGMR code 2 is different than SORLCUR
insert  into syqfxcu_errors
select  shrdgmr_pidm                    pidm,
        shrdgmr_term_code_sturec        term_code,
        'OCDIF02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        shrdgmr_data_origin             old_data_origin,
        shrdgmr_user_id                 old_user_id,
        to_char(shrdgmr_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlcur_data_origin             new_data_origin,
        sorlcur_user_id                 new_user_id,
        to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur, shrdgmr a
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_priority_no = 2
and     sorlcur_seqno =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 2)
and     shrdgmr_coll_code_2        is not null
and     (       nvl(sorlcur_levl_code,'*') <> nvl(shrdgmr_levl_code,'*')
        or      nvl(sorlcur_coll_code,'*') <> nvl(shrdgmr_coll_code_2,'*')
        or      nvl(sorlcur_degc_code,'*') <> nvl(shrdgmr_degc_code,'*')
        or      nvl(sorlcur_camp_code,'#') <> nvl(shrdgmr_camp_code_2,'*')
        or      nvl(sorlcur_program,'*')   <> nvl(shrdgmr_program,'*')
        or      nvl(sorlcur_curr_rule,0)   <> nvl(shrdgmr_curr_rule_2,0)
        )
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SHRDGMR code 1 does not have a matching SORLCUR
insert  into syqfxcu_errors
select  shrdgmr_pidm                    pidm,
        shrdgmr_term_code_sturec        term_code,
        'OCMIS01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        shrdgmr_data_origin             old_data_origin,
        shrdgmr_user_id                 old_user_id,
        to_char(shrdgmr_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, shrdgmr a
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     not exists
        (select 'x'
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 1
        and     sorlcur_seqno =
                (select max(sorlcur_seqno)
                from    sorlcur
                where   sorlcur_pidm        = shrdgmr_pidm
                and     sorlcur_term_code   = shrdgmr_term_code_sturec
                and     sorlcur_lmod_code   = 'OUTCOME'
                and     sorlcur_key_seqno   = shrdgmr_seq_no
                and     sorlcur_priority_no = 1))
and     shrdgmr_coll_code_1        is not null
and     &c_max_outcome_lcur > 
        (select count(*)
        from    shrdgmr
        where   shrdgmr_pidm = a.shrdgmr_pidm
        and     shrdgmr_term_code_sturec > a.shrdgmr_term_code_sturec)
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SHRDGMR code 2 does not have a matching SORLCUR
insert  into syqfxcu_errors
select  shrdgmr_pidm                    pidm,
        shrdgmr_term_code_sturec        term_code,
        'OCMIS02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        shrdgmr_data_origin             old_data_origin,
        shrdgmr_user_id                 old_user_id,
        to_char(shrdgmr_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, shrdgmr a
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     not exists
        (select 'x'
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 2
        and     sorlcur_seqno =
                (select max(sorlcur_seqno)
                from    sorlcur
                where   sorlcur_pidm        = shrdgmr_pidm
                and     sorlcur_term_code   = shrdgmr_term_code_sturec
                and     sorlcur_lmod_code   = 'OUTCOME'
                and     sorlcur_key_seqno   = shrdgmr_seq_no
                and     sorlcur_priority_no = 2))
and     shrdgmr_coll_code_2        is not null
and     &c_max_outcome_lcur > 
        (select count(*)
        from    shrdgmr
        where   shrdgmr_pidm = a.shrdgmr_pidm
        and     shrdgmr_term_code_sturec > a.shrdgmr_term_code_sturec)
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLCUR does not have a matching SHRDGMR code 1
-- Comment out this edit for now as we don't roll SHRDGMR's which makes
-- this check impractical to process.
-- insert  into syqfxcu_errors
-- select  sorlcur_pidm                    pidm,
--         sorlcur_term_code               term_code,
--         'OCMIS03'                       message_code,
--         spriden_id                      id,
--         substr(spriden_last_name||', '||
--                spriden_first_name|| ' '||
--                spriden_mi,1,30)         name,
--         ' '                             old_data_origin,
--         ' '                             old_user_id,
--         ' '                             old_activity_date,
--         sorlcur_data_origin             new_data_origin,
--         sorlcur_user_id                 new_user_id,
--         to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
--                                         new_activity_date,
--         a.rowid                         old_row_id
-- from    spriden, sorlcur a
-- where   spriden_pidm        = sorlcur_pidm
-- and     spriden_change_ind  is null
-- and     sorlcur_lmod_code   = 'OUTCOME'
-- and     sorlcur_priority_no = 1
-- and     sorlcur_seqno =
--         (select max(sorlcur_seqno)
--         from    sorlcur
--         where   sorlcur_pidm        = a.sorlcur_pidm
--         and     sorlcur_lmod_code   = 'OUTCOME'
--         and     sorlcur_key_seqno   = a.sorlcur_key_seqno
--         and     sorlcur_priority_no = 1)
-- and     not exists
--         (select 'x'
--         from    shrdgmr
--         where   shrdgmr_pidm             = sorlcur_pidm
--         and     shrdgmr_term_code_sturec = sorlcur_term_code
--         and     shrdgmr_seq_no           = sorlcur_key_seqno
--         and     shrdgmr_coll_code_1      is not null)
-- and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLCUR does not have a matching SHRDGMR code 2
-- Comment out this edit for now as we don't roll SHRDGMR's which makes
-- this check impractical to process.
-- insert  into syqfxcu_errors
-- select  sorlcur_pidm                    pidm,
--         sorlcur_term_code               term_code,
--         'OCMIS04'                       message_code,
--         spriden_id                      id,
--         substr(spriden_last_name||', '||
--                spriden_first_name|| ' '||
--                spriden_mi,1,30)         name,
--         ' '                             old_data_origin,
--         ' '                             old_user_id,
--         ' '                             old_activity_date,
--         sorlcur_data_origin             new_data_origin,
--         sorlcur_user_id                 new_user_id,
--         to_char(sorlcur_activity_date,'yyyymmdd hh24:mi')
--                                         new_activity_date,
--         a.rowid                         old_row_id
-- from    spriden, sorlcur a
-- where   spriden_pidm        = sorlcur_pidm
-- and     spriden_change_ind  is null
-- and     sorlcur_lmod_code   = 'OUTCOME'
-- and     sorlcur_priority_no = 2
-- and     sorlcur_seqno =
--         (select max(sorlcur_seqno)
--         from    sorlcur
--         where   sorlcur_pidm        = a.sorlcur_pidm
--         and     sorlcur_lmod_code   = 'OUTCOME'
--         and     sorlcur_key_seqno   = a.sorlcur_key_seqno
--         and     sorlcur_priority_no = 2)
-- and     not exists
--         (select 'x'
--         from    shrdgmr
--         where   shrdgmr_pidm             = sorlcur_pidm
--         and     shrdgmr_term_code_sturec = sorlcur_term_code
--         and     shrdgmr_seq_no           = sorlcur_key_seqno
--         and     shrdgmr_coll_code_2      is not null)
-- and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SARADAP code 1 is different than SORLFOS
insert  into syqfxcu_errors
select  saradap_pidm                    pidm,
        saradap_term_code_entry         term_code,
        'AFDIF01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        saradap_data_origin             old_data_origin,
        saradap_user_id                 old_user_id,
        to_char(saradap_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur, saradap a
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = saradap_pidm
and     sorlfos_term_code   = saradap_term_code_entry
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 1
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_priority_no = 1
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no
        and     sorlcur_priority_no = 1)
and     saradap_majr_code_1 is not null
and     nvl(sorlfos_majr_code,'*') <> saradap_majr_code_1
and     nvl(sorlcur_camp_code,'#') = nvl(saradap_camp_code,'*')
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SARADAP code 2 is different than SORLFOS
insert  into syqfxcu_errors
select  saradap_pidm                    pidm,
        saradap_term_code_entry         term_code,
        'AFDIF02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        saradap_data_origin             old_data_origin,
        saradap_user_id                 old_user_id,
        to_char(saradap_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur, saradap a
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = saradap_pidm
and     sorlfos_term_code   = saradap_term_code_entry
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 2
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_priority_no = 2
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no
        and     sorlcur_priority_no = 2)
and     saradap_majr_code_2 is not null
and     nvl(sorlfos_majr_code,'*') <> saradap_majr_code_2
and     nvl(sorlcur_camp_code,'#') = nvl(saradap_camp_code_2,'*')
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SARADAP code 1 does not have a matching SORLFOS
insert  into syqfxcu_errors
select  saradap_pidm                    pidm,
        saradap_term_code_entry         term_code,
        'AFMIS01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        saradap_data_origin             old_data_origin,
        saradap_user_id                 old_user_id,
        to_char(saradap_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur, saradap a
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_priority_no = 1
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no
        and     sorlcur_priority_no = 1)
and     not exists
        (select 'x'
        from    sorlfos
        where   sorlfos_pidm        = saradap_pidm
        and     sorlfos_term_code   = saradap_term_code_entry
        and     sorlfos_lcur_seqno  = sorlcur_seqno
        and     sorlfos_lfst_code   = 'MAJOR'
        and     sorlfos_priority_no = 1)
and     saradap_majr_code_1 is not null
and     &c_max_admissions_lfos > 
        (select count(*)
        from    saradap
        where   saradap_pidm = a.saradap_pidm
        and     saradap_term_code_entry > a.saradap_term_code_entry)
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SARADAP code 2 does not have a matching SORLFOS
insert  into syqfxcu_errors
select  saradap_pidm                    pidm,
        saradap_term_code_entry         term_code,
        'AFMIS02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        saradap_data_origin             old_data_origin,
        saradap_user_id                 old_user_id,
        to_char(saradap_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur, saradap a
where   spriden_pidm        = saradap_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = saradap_pidm
and     sorlcur_term_code   = saradap_term_code_entry
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_key_seqno   = saradap_appl_no
and     sorlcur_priority_no = 2
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from sorlcur
        where   sorlcur_pidm        = saradap_pidm
        and     sorlcur_term_code   = saradap_term_code_entry
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_key_seqno   = saradap_appl_no
        and     sorlcur_priority_no = 2)
and     not exists
        (select 'x'
        from    sorlfos
        where   sorlfos_pidm        = saradap_pidm
        and     sorlfos_term_code   = saradap_term_code_entry
        and     sorlfos_lcur_seqno  = sorlcur_seqno
        and     sorlfos_lfst_code   = 'MAJOR'
        and     sorlfos_priority_no = 2)
and     saradap_majr_code_2 is not null
and     &c_max_admissions_lfos > 
        (select count(*)
        from    saradap
        where   saradap_pidm = a.saradap_pidm
        and     saradap_term_code_entry > a.saradap_term_code_entry)
and     saradap_term_code_entry between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLFOS does not have a matching SARADAP code 1
insert  into syqfxcu_errors
select  sorlfos_pidm                    pidm,
        sorlfos_term_code               term_code,
        'AFMIS03'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        ' '                             old_data_origin,
        ' '                             old_user_id,
        ' '                             old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur a
where   spriden_pidm        = sorlcur_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = sorlcur_pidm
and     sorlfos_term_code   = sorlcur_term_code
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 1
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_priority_no = 1
and     sorlcur_seqno       =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = a.sorlcur_pidm
        and     sorlcur_term_code   = a.sorlcur_term_code
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_priority_no = 1)
and     not exists
        (select 'x'
        from    saradap
        where   saradap_pidm            = sorlfos_pidm
        and     saradap_term_code_entry = sorlfos_term_code
        and     saradap_appl_no         = sorlcur_key_seqno)
and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLFOS does not have a matching SARADAP code 2
insert  into syqfxcu_errors
select  sorlfos_pidm                    pidm,
        sorlfos_term_code               term_code,
        'AFMIS04'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        ' '                             old_data_origin,
        ' '                             old_user_id,
        ' '                             old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur a
where   spriden_pidm        = sorlcur_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = sorlcur_pidm
and     sorlfos_term_code   = sorlcur_term_code
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 2
and     sorlcur_lmod_code   = 'ADMISSIONS'
and     sorlcur_priority_no = 2
and     sorlcur_seqno       =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = a.sorlcur_pidm
        and     sorlcur_term_code   = a.sorlcur_term_code
        and     sorlcur_lmod_code   = 'ADMISSIONS'
        and     sorlcur_priority_no = 2)
and     not exists
        (select 'x'
        from    saradap
        where   saradap_pidm            = sorlfos_pidm
        and     saradap_term_code_entry = sorlfos_term_code
        and     saradap_appl_no         = sorlcur_key_seqno)
and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SGBSTDN code 1 is different than SORLFOS
-- CLPCCD potential problem - we created LEARNER sorlcur's and
-- sorlfos majr codes from saradap and LEARNER sorlcur's are supposed
-- to be created from sgbstdn.  This is showing up in LFDIF01 errors.
insert  into syqfxcu_errors
select  sgbstdn_pidm                    pidm,
        sgbstdn_term_code_eff           term_code,
        'LFDIF01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        sgbstdn_data_origin             old_data_origin,
        sgbstdn_user_id                 old_user_id,
        to_char(sgbstdn_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur, sgbstdn a
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = sgbstdn_pidm
and     sorlfos_term_code   = sgbstdn_term_code_eff
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 1
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 1
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 1)
and     sgbstdn_majr_code_1 is not null
and     nvl(sorlfos_majr_code,'*') <> sgbstdn_majr_code_1
and     nvl(sorlcur_camp_code,'#') = nvl(sgbstdn_camp_code,'*')
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SGBSTDN code 2 is different than SORLFOS
insert  into syqfxcu_errors
select  sgbstdn_pidm                    pidm,
        sgbstdn_term_code_eff           term_code,
        'LFDIF02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        sgbstdn_data_origin             old_data_origin,
        sgbstdn_user_id                 old_user_id,
        to_char(sgbstdn_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur, sgbstdn a
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = sgbstdn_pidm
and     sorlfos_term_code   = sgbstdn_term_code_eff
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 2
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 2
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 2)
and     sgbstdn_majr_code_1 is not null
and     nvl(sorlfos_majr_code,'*') <> sgbstdn_majr_code_2
and     nvl(sorlcur_camp_code,'#') = nvl(sgbstdn_camp_code_2,'*')
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SGBSTDN code 1 does not have a matching SORLFOS
insert  into syqfxcu_errors
select  sgbstdn_pidm                    pidm,
        sgbstdn_term_code_eff           term_code,
        'LFMIS01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        sgbstdn_data_origin             old_data_origin,
        sgbstdn_user_id                 old_user_id,
        to_char(sgbstdn_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur, sgbstdn a
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 1
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 1)
and     not exists
        (select 'x'
        from    sorlfos
        where   sorlfos_pidm        = sgbstdn_pidm
        and     sorlfos_term_code   = sgbstdn_term_code_eff
        and     sorlfos_lcur_seqno  = sorlcur_seqno
        and     sorlfos_lfst_code   = 'MAJOR'
        and     sorlfos_priority_no = 1)
and     sgbstdn_majr_code_1 is not null
and     &c_max_learner_lfos > 
        (select count(*)
        from    sgbstdn
        where   sgbstdn_pidm = a.sgbstdn_pidm
        and     sgbstdn_term_code_eff > a.sgbstdn_term_code_eff)
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SGBSTDN code 2 does not have a matching SORLFOS
insert  into syqfxcu_errors
select  sgbstdn_pidm                    pidm,
        sgbstdn_term_code_eff           term_code,
        'LFMIS02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        sgbstdn_data_origin             old_data_origin,
        sgbstdn_user_id                 old_user_id,
        to_char(sgbstdn_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur, sgbstdn a
where   spriden_pidm        = sgbstdn_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = sgbstdn_pidm
and     sorlcur_term_code   = sgbstdn_term_code_eff
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 2
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = sgbstdn_pidm
        and     sorlcur_term_code   = sgbstdn_term_code_eff
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 2)
and     not exists
        (select 'x'
        from    sorlfos
        where   sorlfos_pidm        = sgbstdn_pidm
        and     sorlfos_term_code   = sgbstdn_term_code_eff
        and     sorlfos_lcur_seqno  = sorlcur_seqno
        and     sorlfos_lfst_code   = 'MAJOR'
        and     sorlfos_priority_no = 2)
and     sgbstdn_majr_code_2 is not null
and     &c_max_learner_lfos > 
        (select count(*)
        from    sgbstdn
        where   sgbstdn_pidm = a.sgbstdn_pidm
        and     sgbstdn_term_code_eff > a.sgbstdn_term_code_eff)
and     sgbstdn_term_code_eff between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLFOS does not have a matching SGBSTDN code 1
insert  into syqfxcu_errors
select  sorlfos_pidm                    pidm,
        sorlfos_term_code               term_code,
        'LFMIS03'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        ' '                             old_data_origin,
        ' '                             old_user_id,
        ' '                             old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur a
where   spriden_pidm        = sorlfos_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = sorlcur_pidm
and     sorlfos_term_code   = sorlcur_term_code
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 1
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 1
and     sorlcur_seqno       =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = a.sorlcur_pidm
        and     sorlcur_term_code   = a.sorlcur_term_code
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 1)                
and     not exists
        (select 'x'
        from    sgbstdn
        where   sgbstdn_pidm    = sorlfos_pidm
        and     sgbstdn_term_code_eff = sorlfos_term_code)
and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLFOS does not have a matching SGBSTDN code 2
insert  into syqfxcu_errors
select  sorlfos_pidm                    pidm,
        sorlfos_term_code               term_code,
        'LFMIS04'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        ' '                             old_data_origin,
        ' '                             old_user_id,
        ' '                             old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur a
where   spriden_pidm        = sorlfos_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = sorlcur_pidm
and     sorlfos_term_code   = sorlcur_term_code
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 2
and     sorlcur_lmod_code   = 'LEARNER'
and     sorlcur_priority_no = 2
and     sorlcur_seqno       =
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = a.sorlcur_pidm
        and     sorlcur_term_code   = a.sorlcur_term_code
        and     sorlcur_lmod_code   = 'LEARNER'
        and     sorlcur_priority_no = 2)                
and     not exists
        (select 'x'
        from    sgbstdn
        where   sgbstdn_pidm    = sorlfos_pidm
        and     sgbstdn_term_code_eff = sorlfos_term_code)
and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SHRDGMR code 1 is different than SORLFOS
insert  into syqfxcu_errors
select  shrdgmr_pidm                    pidm,
        shrdgmr_term_code_sturec        term_code,
        'OFDIF01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        shrdgmr_data_origin             old_data_origin,
        shrdgmr_user_id                 old_user_id,
        to_char(shrdgmr_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur, shrdgmr a
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = shrdgmr_pidm
and     sorlfos_term_code   = shrdgmr_term_code_sturec
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 1
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 1)
and     shrdgmr_majr_code_1 is not null
and     nvl(sorlfos_majr_code,'*') <> shrdgmr_majr_code_1
and     nvl(sorlcur_camp_code,'#') = nvl(shrdgmr_camp_code,'*')
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SHRDGMR code 2 is different than SORLFOS
insert  into syqfxcu_errors
select  shrdgmr_pidm                    pidm,
        shrdgmr_term_code_sturec        term_code,
        'OFDIF02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        shrdgmr_data_origin             old_data_origin,
        shrdgmr_user_id                 old_user_id,
        to_char(shrdgmr_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        sorlfos_data_origin             new_data_origin,
        sorlfos_user_id                 new_user_id,
        to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
                                        new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlfos, sorlcur, shrdgmr a
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlfos_pidm        = shrdgmr_pidm
and     sorlfos_term_code   = shrdgmr_term_code_sturec
and     sorlfos_lcur_seqno  = sorlcur_seqno
and     sorlfos_lfst_code   = 'MAJOR'
and     sorlfos_priority_no = 2
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 2)
and     shrdgmr_majr_code_2 is not null
and     nvl(sorlfos_majr_code,'*') <> shrdgmr_majr_code_2
and     nvl(sorlcur_camp_code,'#') = nvl(shrdgmr_camp_code_2,'*')
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SHRDGMR code 1 does not have a matching SORLFOS
insert  into syqfxcu_errors
select  shrdgmr_pidm                    pidm,
        shrdgmr_term_code_sturec        term_code,
        'OFMIS01'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        shrdgmr_data_origin             old_data_origin,
        shrdgmr_user_id                 old_user_id,
        to_char(shrdgmr_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur, shrdgmr a
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 1)
and     shrdgmr_majr_code_1 is not null
and     not exists
        (select 'x'
        from    sorlfos
        where   sorlfos_pidm        = shrdgmr_pidm
        and     sorlfos_term_code   = shrdgmr_term_code_sturec
        and     sorlfos_lcur_seqno  = sorlcur_seqno
        and     sorlfos_lfst_code   = 'MAJOR'
        and     sorlfos_priority_no = 1)
and     &c_max_outcome_lfos > 
        (select count(*)
        from    shrdgmr
        where   shrdgmr_pidm = a.shrdgmr_pidm
        and     shrdgmr_term_code_sturec > a.shrdgmr_term_code_sturec)
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SHRDGMR code 2 does not have a matching SORLFOS
insert  into syqfxcu_errors
select  shrdgmr_pidm                    pidm,
        shrdgmr_term_code_sturec        term_code,
        'OFMIS02'                       message_code,
        spriden_id                      id,
        substr(spriden_last_name||', '||
               spriden_first_name|| ' '||
               spriden_mi,1,30)         name,
        shrdgmr_data_origin             old_data_origin,
        shrdgmr_user_id                 old_user_id,
        to_char(shrdgmr_activity_date,'yyyymmdd hh24:mi')
                                        old_activity_date,
        ' '                             new_data_origin,
        ' '                             new_user_id,
        ' '                             new_activity_date,
        a.rowid                         old_row_id
from    spriden, sorlcur, shrdgmr a
where   spriden_pidm        = shrdgmr_pidm
and     spriden_change_ind  is null
and     sorlcur_pidm        = shrdgmr_pidm
and     sorlcur_term_code   = shrdgmr_term_code_sturec
and     sorlcur_lmod_code   = 'OUTCOME'
and     sorlcur_key_seqno   = shrdgmr_seq_no
and     sorlcur_seqno       = 
        (select max(sorlcur_seqno)
        from    sorlcur
        where   sorlcur_pidm        = shrdgmr_pidm
        and     sorlcur_term_code   = shrdgmr_term_code_sturec
        and     sorlcur_lmod_code   = 'OUTCOME'
        and     sorlcur_key_seqno   = shrdgmr_seq_no
        and     sorlcur_priority_no = 2)
and     shrdgmr_majr_code_2 is not null
and     not exists
        (select 'x'
        from    sorlfos
        where   sorlfos_pidm        = shrdgmr_pidm
        and     sorlfos_term_code   = shrdgmr_term_code_sturec
        and     sorlfos_lcur_seqno  = sorlcur_seqno
        and     sorlfos_lfst_code   = 'MAJOR'
        and     sorlfos_priority_no = 2)
and     &c_max_outcome_lfos > 
        (select count(*)
        from    shrdgmr
        where   shrdgmr_pidm = a.shrdgmr_pidm
        and     shrdgmr_term_code_sturec > a.shrdgmr_term_code_sturec)
and     shrdgmr_term_code_sturec between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLFOS does not have a matching SHRDGMR code 1
-- Comment out this edit for now as we don't roll SHRDGMR's which makes
-- this check impractical to process.
-- insert  into syqfxcu_errors
-- select  sorlfos_pidm                    pidm,
--         sorlfos_term_code               term_code,
--         'OFMIS03'                       message_code,
--         spriden_id                      id,
--         substr(spriden_last_name||', '||
--                spriden_first_name|| ' '||
--                spriden_mi,1,30)         name,
--         ' '                             old_data_origin,
--         ' '                             old_user_id,
--         ' '                             old_activity_date,
--         sorlfos_data_origin             new_data_origin,
--         sorlfos_user_id                 new_user_id,
--         to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
--                                         new_activity_date,
--         a.rowid                         old_row_id
-- from    spriden, sorlfos, sorlcur a
-- where   spriden_pidm        = sorlcur_pidm
-- and     spriden_change_ind  is null
-- and     sorlfos_pidm        = sorlcur_pidm
-- and     sorlfos_term_code   = sorlcur_term_code
-- and     sorlfos_lcur_seqno  = sorlcur_seqno
-- and     sorlfos_lfst_code   = 'MAJOR'
-- and     sorlfos_priority_no = 1
-- and     sorlcur_seqno       = 
--         (select max(sorlcur_seqno)
--         from    sorlcur
--         where   sorlcur_pidm        = a.sorlcur_pidm
--         and     sorlcur_term_code   = a.sorlcur_term_code
--         and     sorlcur_lmod_code   = 'OUTCOME')
-- and     not exists
--         (select 'x'
--         from    shrdgmr
--         where   shrdgmr_pidm             = sorlcur_pidm
--         and     shrdgmr_term_code_sturec = sorlcur_term_code
--         and     shrdgmr_seq_no           = sorlcur_key_seqno)
-- and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

-- SORLFOS does not have a matching SHRDGMR code 2
-- Comment out this edit for now as we don't roll SHRDGMR's which makes
-- this check impractical to process.
-- insert  into syqfxcu_errors
-- select  sorlfos_pidm                    pidm,
--         sorlfos_term_code               term_code,
--         'OFMIS04'                       message_code,
--         spriden_id                      id,
--         substr(spriden_last_name||', '||
--                spriden_first_name|| ' '||
--                spriden_mi,1,30)         name,
--         ' '                             old_data_origin,
--         ' '                             old_user_id,
--         ' '                             old_activity_date,
--         sorlfos_data_origin             new_data_origin,
--         sorlfos_user_id                 new_user_id,
--         to_char(sorlfos_activity_date,'yyyymmdd hh24:mi')
--                                         new_activity_date,
--         a.rowid                         old_row_id
-- from    spriden, sorlfos, sorlcur a
-- where   spriden_pidm        = sorlcur_pidm
-- and     spriden_change_ind  is null
-- and     sorlfos_pidm        = sorlcur_pidm
-- and     sorlfos_term_code   = sorlcur_term_code
-- and     sorlfos_lcur_seqno  = sorlcur_seqno
-- and     sorlfos_lfst_code   = 'MAJOR'
-- and     sorlfos_priority_no = 4
-- and     sorlcur_seqno       = 
--         (select max(sorlcur_seqno)
--         from    sorlcur
--         where   sorlcur_pidm        = a.sorlcur_pidm
--         and     sorlcur_term_code   = a.sorlcur_term_code
--         and     sorlcur_lmod_code   = 'OUTCOME')
-- and     not exists
--         (select 'x'
--         from    shrdgmr
--         where   shrdgmr_pidm             = sorlcur_pidm
--         and     shrdgmr_term_code_sturec = sorlcur_term_code
--         and     shrdgmr_seq_no           = sorlcur_key_seqno)
-- and     sorlcur_term_code between '&c_parm_beg_term_code' and '&c_parm_end_term_code';

commit;

--
-- If running program in Update Mode, then
-- update missing rows in SORLCUR and SORLFOS from syqfxcu_errors table.
--
declare

cursor  update_cursor is
        select  pidm            u_pidm,
                old_rowid       u_rowid,
                e.message_code  u_message_code
        from    syqfxcu_message_codes c, syqfxcu_errors e
        where   c.message_code       = e.message_code
        and     c.error_update_ind   = 'U'
        and     '&c_parm_run_mode'   = 'U';

begin
    for a_row in update_cursor loop
        if a_row.u_message_code in ('ACMIS01','ACMIS02','AFMIS01','AFMIS02')
        then
            soklcur.p_convert_curr (sb_curriculum_str.f_admissions,
                                    a_row.u_pidm,
                                    a_row.u_rowid);
        elsif a_row.u_message_code in ('LCMIS01','LCMIS02','LFMIS01','LFMIS02')
        then
            soklcur.p_convert_curr (sb_curriculum_str.f_learner,
                                    a_row.u_pidm,
                                    a_row.u_rowid);
        elsif a_row.u_message_code in ('OCMIS01','OCMIS02','OFMIS01','OFMIS02')
        then
            soklcur.p_convert_curr (sb_curriculum_str.f_outcome,
                                    a_row.u_pidm,
                                    a_row.u_rowid);
        end if;
    end loop;
end;
/

--
-- Build popsel for SOPLCCV in case it is run.
--
delete  glbextr
where   glbextr_application = 'STUDENT'
and     glbextr_selection = 'SOPLCCV'
and     glbextr_creator_id = user;

delete  glbslct
where   glbslct_application = 'STUDENT'
and     glbslct_selection   = 'SOPLCCV'
and     glbslct_creator_id  = user;

commit;

insert into glbslct
select  'STUDENT',
        'SOPLCCV',
        user,
        'SOPLCCV Rerun',
        'N',
        sysdate,
        null
from    dual; 

insert into glbextr
select  distinct
        'STUDENT',
        'SOPLCCV',
        user,
        user,
        to_char(pidm),
        sysdate,
        'S',
        null
from    syqfxcu_message_codes m, syqfxcu_errors e
where   m.message_code = e.message_code
and     m.error_update_ind in ('R','U');
commit;

--
-- Short Detail Report of Errors (1 line per error)
--
set heading on
set newpage 0
set linesize 132
set pagesize 63

column  c_spool_file noprint new_value c_spool_file
select  lower('&c_job_name') || '.lis'  c_spool_file
from    sys.dual;
spool $HOME/&c_spool_file

ttitle  left '&c_job_name' '.sql &c_release_no' -
        center '&c_gubinst_name' -
        right &c_run_date -
        skip -
        left sql.user - 
        center 'CURRICULUM AUDIT &c_parm_beg_term_desc thru &c_parm_end_term_desc - &c_parm_run_mode_desc' -
        right &c_run_time ' Page' format 999 sql.pno -
        skip -
        center 'DETAIL ERROR REPORT - W/O USER AND ACTIVITY DATES' -
        skip 2

column c_name                   heading 'NAME' format a25
column c_id                     heading 'ID' format a9
column c_term_code              heading 'TERM' format a6
column c_pidm                   heading 'PIDM' format 99999999
column c_message_code           heading 'MESSAGE' format a7
column c_message_desc           heading 'MESSAGE DESC' format a70

select  name                    c_name,
        id                      c_id,
        term_code               c_term_code,
        pidm                    c_pidm,
        b.message_code          c_message_code,
        a.message_desc          c_message_desc
from    syqfxcu_message_codes a, syqfxcu_errors b
where   a.message_code = b.message_code
and     a.error_update_ind in ('R','U')
order by 1,2,3;
spool off

--
-- Long Detail Report of Errors
--
-- set heading on
-- set newpage 0
-- set linesize 132
-- set pagesize 63
-- 
-- column  c_spool_file noprint new_value c_spool_file
-- select  lower('&c_job_name') || '.li2'  c_spool_file
-- from    sys.dual;
-- spool $HOME/&c_spool_file
-- 
-- ttitle  left '&c_job_name' '.sql &c_release_no' -
--         center '&c_gubinst_name' -
--         right &c_run_date -
--         skip -
--         left sql.user - 
--         center 'CURRICULUM AUDIT &c_parm_beg_term_desc thru &c_parm_end_term_desc - &c_parm_run_mode_desc' -
--         right &c_run_time ' Page' format 999 sql.pno -
--         skip -
--         center 'DETAIL ERROR REPORT - W/ USER AND ACTIVITY DATES' -
--         skip 2
-- 
-- column c_name                   heading 'NAME' format a25
-- column c_id                     heading 'ID' format a9
-- column c_term_code              heading 'TERM' format a6
-- column c_pidm                   heading 'PIDM' format 99999999
-- column c_message_code           heading 'MESSAGE' format a7
-- column c_message_desc           heading 'MESSAGE DESC' format a70
-- column c_old_data_origin        heading 'OLD|DATA ORIGIN' format a11
-- column c_old_user_id            heading 'OLD|USER ID' format a10
-- column c_old_activity_date      heading 'OLD|ACTIVITY DATE' format a15
-- column c_new_data_origin        heading 'NEW|DATA ORIGIN' format a11
-- column c_new_user_id            heading 'NEW|USER ID' format a10
-- column c_new_activity_date      heading 'NEW|ACTIVITY DATE' format a15
-- column nl                       newline
-- 
-- select  name                    c_name,
--         id                      c_id,
--         term_code               c_term_code,
--         pidm                    c_pidm,
--         old_user_id             c_old_user_id,
--         old_data_origin         c_old_data_origin,
--         old_activity_date       c_old_activity_date,
--         new_user_id             c_new_user_id,
--         new_data_origin         c_new_data_origin,
--         new_activity_date       c_new_activity_date,
--         '        '              nl,
--         b.message_code          c_message_code,
--         a.message_desc          c_message_desc
-- from    syqfxcu_message_codes a, syqfxcu_errors b
-- where   a.message_code = b.message_code
-- and     a.error_update_ind in ('R','U')
-- order by 1,2,3;
-- spool off

--
-- Summary Counts Report
--
set heading on
set newpage 0
set linesize 132
set pagesize 63

column  c_spool_file noprint new_value c_spool_file
select  lower('&c_job_name') || '.sum'  c_spool_file
from    sys.dual;
spool $HOME/&c_spool_file

ttitle  left '&c_job_name' '.sql &c_release_no' -
        center '&c_gubinst_name' -
        right &c_run_date -
        skip -
        left sql.user - 
        center 'CURRICULUM AUDIT &c_parm_beg_term_desc thru &c_parm_end_term_desc - &c_parm_run_mode_desc' -
        right &c_run_time ' Page' format 999 sql.pno -
        skip -
        center 'SUMMARY COUNTS REPORT' -
        skip 2

column c_term_code              heading 'TERM' format a6
column c_message_code           heading 'MESSAGE' format a7
column c_message_desc           heading 'MESSAGE DESC' format a80
column c_count                  heading 'COUNT' format 99,999,999

select  term_code               c_term_code,
        b.message_code          c_message_code,
        a.message_desc          c_message_desc,
        count(*)                c_count
from    syqfxcu_message_codes a, syqfxcu_errors b
where   a.message_code = b.message_code
group by term_code, b.message_code, a.message_desc
union all
select  b.term_code             c_term_code,
        b.message_code          c_message_code,
        a.message_desc          c_message_desc,
        max(message_count)      c_count
from    syqfxcu_message_codes a, syqfxcu_match_counts b
where   a.message_code = b.message_code
group by b.term_code, b.message_code, a.message_desc
order by 1,2,3;

spool off

--
-- Build comma separated value file of key tables and columns for 
-- any Student that had errors.
--
-- Initial set commands
set echo off
set pause off
set linesize 500
set pagesize 0
set heading off
set feedback off
set verify off
set tab off
set wrap off
set space 0
set timing off
set trimout on
set trimspool on
set termout off
set embedded on

-- debug
-- set echo on
-- set termout on
-- set feedback on
-- set verify on

column  c_spool_file noprint new_value c_spool_file
select  lower('&c_job_name') || '.csv'  c_spool_file
from    sys.dual;
spool $HOME/&c_spool_file

-- SARADAP
select  chr(34)||'SARADAP'||chr(34)||chr(44)||
        chr(34)||'ID'||chr(34)||chr(44)||
        chr(34)||'PIDM'||chr(34)||chr(44)||
        chr(34)||'TERM_CODE_ENTRY'||chr(34)||chr(44)||
        chr(34)||'APPL_NO'||chr(34)||chr(44)||
        chr(34)||'LEVL_CODE'||chr(34)||chr(44)||
        chr(34)||'COLL_CODE_1'||chr(34)||chr(44)||
        chr(34)||'CAMP_CODE'||chr(34)||chr(44)||
        chr(34)||'PROGRAM_1'||chr(34)||chr(44)||
        chr(34)||'CURR_RULE_1'||chr(34)||chr(44)||
        chr(34)||'MAJR_CODE_1'||chr(34)||chr(44)||
        chr(34)||'LEVL_CODE_2'||chr(34)||chr(44)||
        chr(34)||'COLL_CODE_2'||chr(34)||chr(44)||
        chr(34)||'CAMP_CODE_2'||chr(34)||chr(44)||
        chr(34)||'PROGRAM_2'||chr(34)||chr(44)||
        chr(34)||'CURR_RULE_2'||chr(34)||chr(44)||
        chr(34)||'MAJR_CODE_2'||chr(34)||chr(44)||
        chr(34)||'DATA_ORIGIN'||chr(34)||chr(44)||
        chr(34)||'USER_ID'||chr(34)||chr(44)||
        chr(34)||'ACTIVITY_DATE'||chr(34)
from    dual;

select  distinct chr(34)||'SARADAP'||chr(34)||chr(44)||
        chr(34)||spriden_id||chr(34)||chr(44)||
        chr(34)||saradap_pidm||chr(34)||chr(44)||
        chr(34)||saradap_term_code_entry||chr(34)||chr(44)||
        chr(34)||saradap_appl_no||chr(34)||chr(44)||
        chr(34)||saradap_levl_code||chr(34)||chr(44)||
        chr(34)||saradap_coll_code_1||chr(34)||chr(44)||
        chr(34)||saradap_camp_code||chr(34)||chr(44)||
        chr(34)||saradap_program_1||chr(34)||chr(44)||
        chr(34)||saradap_curr_rule_1||chr(34)||chr(44)||
        chr(34)||saradap_majr_code_1||chr(34)||chr(44)||
        chr(34)||saradap_levl_code_2||chr(34)||chr(44)||
        chr(34)||saradap_coll_code_2||chr(34)||chr(44)||
        chr(34)||saradap_camp_code_2||chr(34)||chr(44)||
        chr(34)||saradap_program_2||chr(34)||chr(44)||
        chr(34)||saradap_curr_rule_2||chr(34)||chr(44)||
        chr(34)||saradap_majr_code_2||chr(34)||chr(44)||
        chr(34)||saradap_data_origin||chr(34)||chr(44)||
        chr(34)||saradap_user_id||chr(34)||chr(44)||
        chr(34)||saradap_activity_date||chr(34)
from    spriden, saradap, syqfxcu_errors
where   spriden_pidm                            = saradap_pidm
and     spriden_change_ind                      is null
and     saradap_pidm                            = pidm
order by 1;

select  chr(34)||' '||chr(34)
from    dual;

-- SGBSTDN
select  chr(34)||'SGBSTDN'||chr(34)||chr(44)||
        chr(34)||'ID'||chr(34)||chr(44)||
        chr(34)||'PIDM'||chr(34)||chr(44)||
        chr(34)||'TERM_CODE_EFF'||chr(34)||chr(44)||
        chr(34)||'LEVL_CODE'||chr(34)||chr(44)||
        chr(34)||'COLL_CODE_1'||chr(34)||chr(44)||
        chr(34)||'DEGC_CODE_1'||chr(34)||chr(44)||
        chr(34)||'CAMP_CODE'||chr(34)||chr(44)||
        chr(34)||'PROGRAM_1'||chr(34)||chr(44)||
        chr(34)||'TERM_CODE_ADMIT'||chr(34)||chr(44)||
        chr(34)||'CURR_RULE_1'||chr(34)||chr(44)||
        chr(34)||'MAJR_CODE_1'||chr(34)||chr(44)||
        chr(34)||'LEVL_CODE_2'||chr(34)||chr(44)||
        chr(34)||'COLL_CODE_2'||chr(34)||chr(44)||
        chr(34)||'DEGC_CODE_2'||chr(34)||chr(44)||
        chr(34)||'CAMP_CODE_2'||chr(34)||chr(44)||
        chr(34)||'PROGRAM_2'||chr(34)||chr(44)||
        chr(34)||'TERM_CODE_ADMIT_2'||chr(34)||chr(44)||
        chr(34)||'CURR_RULE_2'||chr(34)||chr(44)||
        chr(34)||'MAJR_CODE_2'||chr(34)||chr(44)||
        chr(34)||'DATA_ORIGIN'||chr(34)||chr(44)||
        chr(34)||'USER_ID'||chr(34)||chr(44)||
        chr(34)||'ACTIVITY_DATE'||chr(34)
from    dual;

select  distinct chr(34)||'SGBSTDN'||chr(34)||chr(44)||
        chr(34)||spriden_id||chr(34)||chr(44)||
        chr(34)||sgbstdn_pidm||chr(34)||chr(44)||
        chr(34)||sgbstdn_term_code_eff||chr(34)||chr(44)||
        chr(34)||sgbstdn_levl_code||chr(34)||chr(44)||
        chr(34)||sgbstdn_coll_code_1||chr(34)||chr(44)||
        chr(34)||sgbstdn_degc_code_1||chr(34)||chr(44)||
        chr(34)||sgbstdn_camp_code||chr(34)||chr(44)||
        chr(34)||sgbstdn_program_1||chr(34)||chr(44)||
        chr(34)||sgbstdn_term_code_admit||chr(34)||chr(44)||
        chr(34)||sgbstdn_curr_rule_1||chr(34)||chr(44)||
        chr(34)||sgbstdn_majr_code_1||chr(34)||chr(44)||
        chr(34)||sgbstdn_levl_code_2||chr(34)||chr(44)||
        chr(34)||sgbstdn_coll_code_2||chr(34)||chr(44)||
        chr(34)||sgbstdn_degc_code_2||chr(34)||chr(44)||
        chr(34)||sgbstdn_camp_code_2||chr(34)||chr(44)||
        chr(34)||sgbstdn_program_2||chr(34)||chr(44)||
        chr(34)||sgbstdn_term_code_admit_2||chr(34)||chr(44)||
        chr(34)||sgbstdn_curr_rule_2||chr(34)||chr(44)||
        chr(34)||sgbstdn_majr_code_2||chr(34)||chr(44)||
        chr(34)||sgbstdn_data_origin||chr(34)||chr(44)||
        chr(34)||sgbstdn_user_id||chr(34)||chr(44)||
        chr(34)||sgbstdn_activity_date||chr(34)
from    spriden, sgbstdn, syqfxcu_errors
where   spriden_pidm                            = sgbstdn_pidm
and     spriden_change_ind                      is null
and     sgbstdn_pidm                            = pidm
order by 1;

select  chr(34)||' '||chr(34)
from    dual;

-- SHRDGMR
select  chr(34)||'SHRDGMR'||chr(34)||chr(44)||
        chr(34)||'ID'||chr(34)||chr(44)||
        chr(34)||'PIDM'||chr(34)||chr(44)||
        chr(34)||'TERM_CODE_STUREC'||chr(34)||chr(44)||
        chr(34)||'SEQ_NO'||chr(34)||chr(44)||
        chr(34)||'LEVL_CODE'||chr(34)||chr(44)||
        chr(34)||'COLL_CODE_1'||chr(34)||chr(44)||
        chr(34)||'DEGC_CODE'||chr(34)||chr(44)||
        chr(34)||'CAMP_CODE'||chr(34)||chr(44)||
        chr(34)||'PROGRAM'||chr(34)||chr(44)||
        chr(34)||'CURR_RULE_1'||chr(34)||chr(44)||
        chr(34)||'MAJR_CODE_1'||chr(34)||chr(44)||
        chr(34)||'COLL_CODE_2'||chr(34)||chr(44)||
        chr(34)||'CAMP_CODE_2'||chr(34)||chr(44)||
        chr(34)||'CURR_RULE_2'||chr(34)||chr(44)||
        chr(34)||'MAJR_CODE_2'||chr(34)||chr(44)||
        chr(34)||'DATA_ORIGIN'||chr(34)||chr(44)||
        chr(34)||'USER_ID'||chr(34)||chr(44)||
        chr(34)||'ACTIVITY_DATE'||chr(34)
from    dual;

select  distinct chr(34)||'SHRDGMR'||chr(34)||chr(44)||
        chr(34)||spriden_id||chr(34)||chr(44)||
        chr(34)||shrdgmr_pidm||chr(34)||chr(44)||
        chr(34)||shrdgmr_term_code_sturec||chr(34)||chr(44)||
        chr(34)||shrdgmr_seq_no||chr(34)||chr(44)||
        chr(34)||shrdgmr_levl_code||chr(34)||chr(44)||
        chr(34)||shrdgmr_coll_code_1||chr(34)||chr(44)||
        chr(34)||shrdgmr_degc_code||chr(34)||chr(44)||
        chr(34)||shrdgmr_camp_code||chr(34)||chr(44)||
        chr(34)||shrdgmr_program||chr(34)||chr(44)||
        chr(34)||shrdgmr_curr_rule_1||chr(34)||chr(44)||
        chr(34)||shrdgmr_majr_code_1||chr(34)||chr(44)||
        chr(34)||shrdgmr_coll_code_2||chr(34)||chr(44)||
        chr(34)||shrdgmr_camp_code_2||chr(34)||chr(44)||
        chr(34)||shrdgmr_curr_rule_2||chr(34)||chr(44)||
        chr(34)||shrdgmr_majr_code_2||chr(34)||chr(44)||
        chr(34)||shrdgmr_data_origin||chr(34)||chr(44)||
        chr(34)||shrdgmr_user_id||chr(34)||chr(44)||
        chr(34)||shrdgmr_activity_date||chr(34)
from    spriden, shrdgmr, syqfxcu_errors
where   spriden_pidm                            = shrdgmr_pidm
and     spriden_change_ind                      is null
and     shrdgmr_pidm                            = pidm
order by 1;

select  chr(34)||' '||chr(34)
from    dual;

-- SORLCUR
select  chr(34)||'SORLCUR'||chr(34)||chr(44)||
        chr(34)||'ID'||chr(34)||chr(44)||
        chr(34)||'PIDM'||chr(34)||chr(44)||
        chr(34)||'TERM_CODE'||chr(34)||chr(44)||
        chr(34)||'SEQNO'||chr(34)||chr(44)||
        chr(34)||'KEY_SEQNO'||chr(34)||chr(44)||
        chr(34)||'LMOD_CODE'||chr(34)||chr(44)||
        chr(34)||'LEVL_CODE'||chr(34)||chr(44)||
        chr(34)||'COLL_CODE'||chr(34)||chr(44)||
        chr(34)||'CAMP_CODE'||chr(34)||chr(44)||
        chr(34)||'PROGRAM'||chr(34)||chr(44)||
        chr(34)||'CURR_RULE'||chr(34)||chr(44)||
        chr(34)||'DEGC_CODE'||chr(34)||chr(44)||
        chr(34)||'TERM_CODE_ADMIT'||chr(34)||chr(44)||
        chr(34)||'PRIORITY_NO'||chr(34)||chr(44)||
        chr(34)||'DATA_ORIGIN'||chr(34)||chr(44)||
        chr(34)||'USER_ID'||chr(34)||chr(44)||
        chr(34)||'ACTIVITY_DATE'||chr(34)
from    dual;

select  distinct chr(34)||'SORLCUR'||chr(34)||chr(44)||
        chr(34)||spriden_id||chr(34)||chr(44)||
        chr(34)||sorlcur_pidm||chr(34)||chr(44)||
        chr(34)||sorlcur_term_code||chr(34)||chr(44)||
        chr(34)||sorlcur_seqno||chr(34)||chr(44)||
        chr(34)||sorlcur_key_seqno||chr(34)||chr(44)||
        chr(34)||sorlcur_lmod_code||chr(34)||chr(44)||
        chr(34)||sorlcur_levl_code||chr(34)||chr(44)||
        chr(34)||sorlcur_coll_code||chr(34)||chr(44)||
        chr(34)||sorlcur_camp_code||chr(34)||chr(44)||
        chr(34)||sorlcur_program||chr(34)||chr(44)||
        chr(34)||sorlcur_curr_rule||chr(34)||chr(44)||
        chr(34)||sorlcur_degc_code||chr(34)||chr(44)||
        chr(34)||sorlcur_term_code_admit||chr(34)||chr(44)||
        chr(34)||sorlcur_priority_no||chr(34)||chr(44)||
        chr(34)||sorlcur_data_origin||chr(34)||chr(44)||
        chr(34)||sorlcur_user_id||chr(34)||chr(44)||
        chr(34)||sorlcur_activity_date||chr(34)
from    spriden, sorlcur, syqfxcu_errors
where   spriden_pidm                            = sorlcur_pidm
and     spriden_change_ind                      is null
and     sorlcur_pidm                            = pidm
order by 1;

select  chr(34)||' '||chr(34)
from    dual;

-- SORLFOS
select  chr(34)||'SORLFOS'||chr(34)||chr(44)||
        chr(34)||'ID'||chr(34)||chr(44)||
        chr(34)||'PIDM'||chr(34)||chr(44)||
        chr(34)||'TERM_CODE'||chr(34)||chr(44)||
        chr(34)||'LCUR_SEQNO'||chr(34)||chr(44)||
        chr(34)||'LFST_CODE'||chr(34)||chr(44)||
        chr(34)||'PRIORITY_NO'||chr(34)||chr(44)||
        chr(34)||'MAJR_CODE'||chr(34)||chr(44)||
        chr(34)||'DATA_ORIGIN'||chr(34)||chr(44)||
        chr(34)||'USER_ID'||chr(34)||chr(44)||
        chr(34)||'ACTIVITY_DATE'||chr(34)
from    dual;

select  distinct chr(34)||'SORLFOS'||chr(34)||chr(44)||
        chr(34)||spriden_id||chr(34)||chr(44)||
        chr(34)||sorlfos_pidm||chr(34)||chr(44)||
        chr(34)||sorlfos_term_code||chr(34)||chr(44)||
        chr(34)||sorlfos_lcur_seqno||chr(34)||chr(44)||
        chr(34)||sorlfos_lfst_code||chr(34)||chr(44)||
        chr(34)||sorlfos_priority_no||chr(34)||chr(44)||
        chr(34)||sorlfos_majr_code||chr(34)||chr(44)||
        chr(34)||sorlfos_data_origin||chr(34)||chr(44)||
        chr(34)||sorlfos_user_id||chr(34)||chr(44)||
        chr(34)||sorlfos_activity_date||chr(34)
from    spriden, sorlfos, syqfxcu_errors
where   spriden_pidm                            = sorlfos_pidm
and     spriden_change_ind                      is null
and     sorlfos_pidm                            = pidm
order by 1;

select  chr(34)||' '||chr(34)
from    dual;

spool off

--
-- Cleanup
--
drop table syqfxcu_message_codes;
drop table syqfxcu_errors;
drop table syqfxcu_match_counts;

exit