[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_selection_dt IN DATE,
p_confirmed_ind IN VARCHAR2,
p_log_creation_dt IN DATE,
p_message_name OUT NOCOPY VARCHAR2
)
RETURN BOOLEAN;
p_selection_dt IN DATE,
p_confirmed_ind IN VARCHAR2,
p_authorised_person_id IN NUMBER,
p_authorised_on IN DATE,
p_seqval OUT NOCOPY NUMBER,
p_log_creation_dt IN DATE,
p_message_name OUT NOCOPY VARCHAR2
)
RETURN BOOLEAN;
FUNCTION update_stream_unit_sets(
p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_old_admin_unit_set IN VARCHAR2,
p_rqrmnts_complete_ind IN VARCHAR2,
p_rqrmnts_complete_dt IN DATE,
p_selection_dt IN DATE,
p_confirmed_ind IN VARCHAR2,
p_log_creation_dt IN DATE,
p_message_name OUT NOCOPY VARCHAR2
)
RETURN BOOLEAN;
bdeviset 29-JUL-2004 Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW in a check is
made to see that their is no overlapping of selection,completion and
end dates for any two unit sets by calling check_usa_overlap.If it returns
false log entry is made and the insert or update is not carried out for bug 3149133.
knag 29-OCT-2002 Bug 2647482 addded parameters attendance_mode, location_cd for calculation
of proposed completion date by procedure igs_ad_gen_004.admp_get_crv_comp_dt
ayedubat 4-JUN-2002 Changed the Code of YOP for default Unit Set pre-enrollment before
the Units pre-enrollment Code for the bug fix: 2391842
ayedubat 25-MAY-2002 Changed the cursors c_acaiv and c_acaiv1 to replace the view,IGS_AD_PS_APPL_INST_APLINST_V
with the base table,IGS_AD_PS_APPL_INST as part of the bug fix:2384449
ayedubat 21-MAY-2002 Modified the cursor,c_first_us to select always the Unit Set with mapping
sequence number of '1' as part of the bug fix:2348709
ayedubat 15-MAY-2002 Changed the cursor,c_chk_census_dt to consider only the SUA records with
unit attempt status 'ENROLLED','DISCONTIN','DUPLICATE' or 'COMPLETED' and
also added the TRUNC to SYDATE as part of the bug:2372892
svanukur 10-jul-2003 checking for parameter P_PROGRESS_STAT , if it is set to 'ADVANCE' as part of bug #3043374
knaraset 06-Aug-2003 Modified the Pre-enrollment of new students to pass NULL for Nominated completion columns while
creation of program attempt, and also removed the references to Admission's Nominated/expected completion columns.
ptandon 06-Oct-2003 Modified the inline procedures enrpl_copy_adm_sua and enrpl_copy_param_sua
as part of Prevent Dropping Core Units. Enh Bug# 3052432.
svanukur 02-jul-2004 MOdified Pre-enrollment of new students to pass the selection date of unit set attempts
as the SPA commencement date instead of the sysdate in the YOP mode
as part of bug fix 3687470
svanukur 20-jul-2004 Added a check after call to procedure enrpl_create_pos_sua to return false to igs_en_gen_008
so that the message successfully preenrolled is not shown in the log file. BUG 3032588.
*/
BEGIN -- enrp_ins_snew_prenrl
-- This process will pre-enrol a single new student in the specified
-- IGS_PS_COURSE. The following steps will be performed :
-- * Check the students eligibility to enrol in the specified IGS_PS_COURSE
-- in the specified academic calendar.
-- * Create a IGS_EN_STDNT_PS_ATT record
-- * Create a IGS_AS_SC_ATMPT_ENR record
-- * Create any required IGS_AS_SU_SETATMPT details
-- * Create default IGS_EN_STDNTPSHECSOP details
-- * Pre-enrol IGS_PS_UNIT attempts entered during Admissions, as parameter
-- to the process or through the Pattern of Study.
-- If at any point it becomes impossible to pre-enrol the student,
-- the routine will return FALSE and message number of a message
-- indicating the reason for failure ; the log error indicator will be
SELECT acaiv.person_id,
aa.acad_cal_type,
NVL(acaiv.adm_cal_type,aa.adm_cal_type) adm_cal_type,
NVL(acaiv.adm_ci_sequence_number,aa.adm_ci_sequence_number) adm_ci_sequence_number,
acaiv.location_cd,
acaiv.attendance_type,
acaiv.attendance_mode,
acaiv.admission_appl_number,
acaiv.nominated_course_cd,
acaiv.sequence_number,
acaiv.course_cd,
acaiv.crv_version_number,
acaiv.fee_cat,
acaiv.correspondence_cat,
acaiv.enrolment_cat,
acaiv.unit_set_cd,
acaiv.us_version_number,
acaiv.hecs_payment_option,
acaiv.adm_outcome_status,
acaiv.funding_source,
aa.admission_cat,
aa.s_admission_process_type,
aos.s_adm_outcome_status,
acaiv.adm_cndtnl_offer_status,
acaiv.adm_offer_resp_status,
aors.s_adm_offer_resp_status,
acaiv.actual_response_dt,
acaiv.expected_completion_yr,
acaiv.expected_completion_perd,
acaiv.offer_dt
FROM IGS_AD_PS_APPL_INST acaiv,
IGS_AD_APPL aa,
IGS_AD_OU_STAT aos,
IGS_AD_OFR_RESP_STAT aors
WHERE
acaiv.person_id = p_person_id AND
acaiv.course_cd = p_course_cd AND
aa.acad_cal_type = p_acad_cal_type AND
aa.acad_ci_sequence_number = p_acad_sequence_number AND
aa.person_id = acaiv.person_id AND
aa.admission_appl_number = acaiv.admission_appl_number AND
aos.adm_outcome_status = acaiv.adm_outcome_status AND
aors.adm_offer_resp_status = acaiv.adm_offer_resp_status AND
aos.s_adm_outcome_status IN ('OFFER','COND-OFFER') AND
aors.s_adm_offer_resp_status NOT IN ('LAPSED','REJECTED')
ORDER BY acaiv.offer_dt DESC;
SELECT acaiv.person_id,
aa.acad_cal_type,
NVL(acaiv.adm_cal_type,aa.adm_cal_type) adm_cal_type,
NVL(acaiv.adm_ci_sequence_number,aa.adm_ci_sequence_number) adm_ci_sequence_number,
acaiv.location_cd,
acaiv.attendance_type,
acaiv.attendance_mode,
acaiv.admission_appl_number,
acaiv.nominated_course_cd,
acaiv.sequence_number,
acaiv.course_cd,
acaiv.crv_version_number,
acaiv.fee_cat,
acaiv.correspondence_cat,
acaiv.enrolment_cat,
acaiv.unit_set_cd,
acaiv.us_version_number,
acaiv.hecs_payment_option,
acaiv.adm_outcome_status,
acaiv.funding_source,
aa.admission_cat,
aa.s_admission_process_type,
aos.s_adm_outcome_status,
acaiv.adm_cndtnl_offer_status,
acaiv.adm_offer_resp_status,
aors.s_adm_offer_resp_status,
acaiv.actual_response_dt,
acaiv.expected_completion_yr,
acaiv.expected_completion_perd,
acaiv.offer_dt
FROM IGS_AD_PS_APPL_INST acaiv,
IGS_AD_APPL aa,
IGS_AD_OU_STAT aos,
IGS_AD_OFR_RESP_STAT aors
WHERE acaiv.person_id = p_person_id AND
acaiv.course_cd = p_course_cd AND
acaiv.admission_appl_number = p_acai_admission_appl_number AND
acaiv.nominated_course_cd = p_acai_nominated_course_cd AND
acaiv.sequence_number = p_acai_sequence_number AND
aa.person_id = acaiv.person_id AND
aa.admission_appl_number = acaiv.admission_appl_number AND
aos.adm_outcome_status = acaiv.adm_outcome_status AND
aors.adm_offer_resp_status = acaiv.adm_offer_resp_status;
cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE ;
CURSOR c_prog_outcome(cp_select_dt igs_as_su_setatmpt.selection_dt%TYPE) IS
SELECT pou.decision_dt, pout.s_progression_outcome_type
FROM igs_pr_stdnt_pr_ou_all pou , igs_pr_ou_type pout
WHERE pou.person_id = p_person_id AND
pou.course_cd = p_course_cd AND
pou.decision_status = 'APPROVED' AND
pou.decision_dt IS NOT NULL AND
pou.decision_dt > cp_select_dt AND
pou.progression_outcome_type = pout.progression_outcome_type
ORDER BY pou.decision_dt desc ;
SELECT susa.*
FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.selection_dt IS NOT NULL AND
susa.end_dt IS NULL AND
susa.rqrmnts_complete_dt IS NULL AND
susa.unit_set_cd = us.unit_set_cd AND
us.unit_set_cat = usc.unit_set_cat AND
usc.s_unit_set_cat = 'PRENRL_YR' ;
SELECT cf1.unit_set_cd , cf1.sequence_no
FROM igs_ps_us_prenr_cfg cf1 , igs_ps_us_prenr_cfg cf2
WHERE cf2.mapping_set_cd = cf1.mapping_set_cd AND
cf2.unit_set_cd = cp_unit_set_cd AND
cf1.sequence_no > cf2.sequence_no
ORDER BY cf1.sequence_no asc;
SELECT coous.us_version_number
FROM igs_en_unit_set_stat uss, igs_ps_ofr_opt_unit_set_v coous, igs_en_stdnt_ps_att sca
WHERE sca.person_id = cp_person_id AND
sca.course_cd = cp_course_cd AND
sca.coo_id = coous.coo_id AND
coous.unit_set_cd = cp_unit_set_cd AND
coous.expiry_dt IS NULL AND
coous.unit_set_status = uss.unit_set_status AND
uss.s_unit_set_status = 'ACTIVE' ;
SELECT coou.unit_set_cd, coou.us_version_number
FROM igs_ps_ofr_opt_unit_set_v coou , igs_en_unit_set_cat usc
WHERE coou.course_cd = p_course_cd AND
coou.crv_version_number = v_acaiv_rec.crv_version_number AND
coou.cal_type = v_acaiv_rec.acad_cal_type AND
coou.location_cd = v_acaiv_rec.location_cd AND
coou.attendance_mode = v_acaiv_rec.attendance_mode AND
coou.attendance_type = v_acaiv_rec.attendance_type AND
coou.unit_set_cat = usc.unit_set_cat AND
usc.s_unit_set_cat = 'PRENRL_YR' AND
coou.unit_set_cd IN ( SELECT a.unit_set_cd
FROM igs_ps_us_prenr_cfg a
WHERE a.sequence_no = 1 );
SELECT sua.*
FROM igs_en_sua_year_v sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_set_cd = cp_unit_set_cd AND
sua.unit_attempt_status IN ('ENROLLED','DISCONTIN','DUPLICATE','COMPLETED') AND
IGS_EN_GEN_015.get_effective_census_date(Null,Null,sua.cal_type,sua.ci_sequence_number) < TRUNC(SYSDATE) ;
SELECT sequence_number , student_confirmed_ind
FROM IGS_AS_SU_SETATMPT
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
unit_set_cd = cp_unit_set_cd AND
us_version_number = cp_us_version_number ;
SELECT rowid,IGS_AS_SU_SETATMPT.*
FROM IGS_AS_SU_SETATMPT
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
unit_set_cd = cp_unit_set_cd AND
us_version_number = cp_us_version_number AND
sequence_number = cp_sequence_number
FOR UPDATE OF RQRMNTS_COMPLETE_IND ,
RQRMNTS_COMPLETE_DT , student_confirmed_ind NOWAIT;
SELECT rel.sub_cal_type, rel.sub_ci_sequence_number FROM igs_ca_inst_rel rel,
igs_ca_inst ci,
igs_ca_type cal
WHERE rel.sup_cal_type = p_acad_cal_type
AND rel.sup_ci_sequence_number = p_acad_seq_num
AND rel.sub_cal_type = ci.cal_type
AND rel.sub_ci_sequence_number = ci.sequence_number
AND rel.sub_cal_type = cal.cal_type
AND cal.s_cal_cat = 'LOAD'
AND cal.closed_ind = 'N'
ORDER BY ci.start_dt;
SELECT spa.commencement_dt
FROM IGS_EN_STDNT_PS_ATT spa
WHERE spa.person_id = p_person_id AND
spa.course_cd = p_course_cd;
SELECT 'X'
FROM igs_en_unit_set us , igs_en_unit_set_cat usc
WHERE us.unit_set_cd = cp_unit_set_cd AND
us.unit_set_cat = usc.unit_set_cat AND
usc.s_unit_set_cat = 'PRENRL_YR' ;
SELECT ca.sequence_number
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id AND
ca.sca_course_cd = p_course_cd;
SELECT ca.sequence_number
FROM IGS_RE_CANDIDATURE ca
WHERE ca.person_id = p_person_id AND
ca.acai_admission_appl_number = v_acaiv_rec.admission_appl_number AND
ca.acai_nominated_course_cd = v_acaiv_rec.nominated_course_cd AND
ca.acai_sequence_number = v_acaiv_rec.sequence_number;
SELECT ROWID,
IGS_RE_CANDIDATURE.*
FROM IGS_RE_CANDIDATURE
WHERE person_id = p_person_id AND
sequence_number = cp_sequence_number
FOR UPDATE OF
acai_admission_appl_number,
acai_nominated_course_cd,
acai_sequence_number NOWAIT;
IGS_RE_CANDIDATURE_PKG.UPDATE_ROW(
X_ROWID => v_c_ca_upd_rec.rowid,
X_PERSON_ID => v_c_ca_upd_rec.PERSON_ID,
X_SEQUENCE_NUMBER => v_c_ca_upd_rec.SEQUENCE_NUMBER,
X_SCA_COURSE_CD => v_c_ca_upd_rec.SCA_COURSE_CD,
X_ACAI_ADMISSION_APPL_NUMBER => v_acaiv_rec.admission_appl_number,
X_ACAI_NOMINATED_COURSE_CD => v_acaiv_rec.nominated_course_cd,
X_ACAI_SEQUENCE_NUMBER => v_acaiv_rec.sequence_number,
X_ATTENDANCE_PERCENTAGE => v_c_ca_upd_rec.ATTENDANCE_PERCENTAGE,
X_GOVT_TYPE_OF_ACTIVITY_CD => v_c_ca_upd_rec.GOVT_TYPE_OF_ACTIVITY_CD,
X_MAX_SUBMISSION_DT => v_c_ca_upd_rec.MAX_SUBMISSION_DT,
X_MIN_SUBMISSION_DT => v_c_ca_upd_rec.MIN_SUBMISSION_DT,
X_RESEARCH_TOPIC => v_c_ca_upd_rec.RESEARCH_TOPIC,
X_INDUSTRY_LINKS => v_c_ca_upd_rec.INDUSTRY_LINKS,
X_MODE => 'R' );
IGS_RE_CANDIDATURE_PKG.UPDATE_ROW(
X_ROWID => v_c_ca_upd_rec.rowid,
X_PERSON_ID => v_c_ca_upd_rec.PERSON_ID,
X_SEQUENCE_NUMBER => v_c_ca_upd_rec.SEQUENCE_NUMBER,
X_SCA_COURSE_CD => v_acaiv_rec.course_cd,
X_ACAI_ADMISSION_APPL_NUMBER => v_c_ca_upd_rec.acai_admission_appl_number,
X_ACAI_NOMINATED_COURSE_CD => v_c_ca_upd_rec.acai_nominated_course_cd,
X_ACAI_SEQUENCE_NUMBER => v_c_ca_upd_rec.acai_sequence_number,
X_ATTENDANCE_PERCENTAGE => v_c_ca_upd_rec.ATTENDANCE_PERCENTAGE,
X_GOVT_TYPE_OF_ACTIVITY_CD => v_c_ca_upd_rec.GOVT_TYPE_OF_ACTIVITY_CD,
X_MAX_SUBMISSION_DT => v_c_ca_upd_rec.MAX_SUBMISSION_DT,
X_MIN_SUBMISSION_DT => v_c_ca_upd_rec.MIN_SUBMISSION_DT,
X_RESEARCH_TOPIC => v_c_ca_upd_rec.RESEARCH_TOPIC,
X_INDUSTRY_LINKS => v_c_ca_upd_rec.INDUSTRY_LINKS,
X_MODE => 'R'
);
sarakshi 16-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the insert row,update call of IGS_EN_STDNT_PS_ATT_PKG
ckasu 05-Apr-2004 Modified IGS_EN_STDNT_PS_ATT_Pkg.update_Row procedure
call as a part of bug 3544927.
smaddali modified this procedure to create the HESA UK statistics record
whenever oss program attempt record is being created for bug#2350629
svanukur 15-APR-2004 Passing the values for catalog fields while creating or updating a SPA. bug 3548376
ctyagi 15-march-2005 Modify cursor cur_catalog_details for bug #4238062 (INCORRECT SPA CATALOG CODE )
*****************************************************************************/
BEGIN -- enrpl_create_sca
-- Create a IGS_EN_STDNT_PS_ATT record
DECLARE
CURSOR c_crv IS
SELECT 'x'
FROM IGS_PS_VER crv,
IGS_PS_TYPE cty
WHERE crv.course_cd = v_acaiv_rec.course_cd AND
crv.version_number = v_acaiv_rec.crv_version_number AND
cty.COURSE_TYPE = crv.COURSE_TYPE AND
cty.research_type_ind = 'Y';
SELECT course_attempt_status,
student_confirmed_ind,
commencement_dt,
discontinued_dt,
fee_cat,
correspondence_cat,
funding_source,
location_cd,
attendance_mode,
attendance_type,
nominated_completion_yr,
nominated_completion_perd,
adm_admission_appl_number,
adm_nominated_course_cd,
adm_sequence_number,
provisional_ind
FROM IGS_EN_STDNT_PS_ATT sca
WHERE person_id = p_person_id AND
course_cd = p_course_cd;
SELECT rowid,IGS_EN_STDNT_PS_ATT.*
FROM IGS_EN_STDNT_PS_ATT
WHERE person_id = p_person_id AND
course_cd = p_course_cd
FOR UPDATE OF course_attempt_status,
fee_cat,
correspondence_cat,
funding_source,
provisional_ind,
location_cd,
attendance_mode,
attendance_type,
adm_admission_appl_number,
adm_nominated_course_cd,
adm_sequence_number,
catalog_cal_type,
catalog_seq_num NOWAIT;
SELECT primary_program_type
FROM IGS_EN_STDNT_PS_ATT
WHERE rowid = cp_rowid;
SELECT ps.course_type
FROM IGS_PS_VER ps,
igs_en_stdnt_ps_att spa
WHERE spa.rowid= cp_rowid
AND ps.course_cd = spa.course_cd
AND ps.version_number = spa.version_number;
SELECT 'X'
FROM igs_en_stdnt_ps_att spa,
igs_ps_ver pv
WHERE spa.person_id = p_person_id
AND spa.primary_program_type = 'PRIMARY'
AND spa.course_cd = pv.course_cd
AND spa.version_number = pv.version_number
AND pv.course_type = cp_course_type;
SELECT ci.cal_type catalog_cal_type,
ci.sequence_number catalog_seq_num
FROM igs_ca_inst ci,
igs_ca_type ct,
igs_ca_stat cs,
igs_ca_inst_rel cir
WHERE ci.cal_type = ct.cal_type
AND ct.s_cal_cat = 'LOAD'
AND cs.cal_status = ci.cal_status
AND cs.s_cal_status = 'ACTIVE'
AND p_commencement_date BETWEEN ci.start_dt AND ci.end_dt
AND ci.cal_type=cir.sub_cal_type
AND ci.sequence_number=cir.sub_ci_sequence_number
AND cir.sup_cal_type=p_cal_type
ORDER BY ci.end_dt desc;
IGS_EN_STDNT_PS_ATT_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_version_number => v_acaiv_rec.crv_version_number,
x_cal_type => v_acaiv_rec.acad_cal_type,
x_location_cd => v_acaiv_rec.location_cd,
x_attendance_mode => v_acaiv_rec.attendance_mode,
x_attendance_type =>v_acaiv_rec.attendance_type ,
x_coo_id => NULL,
x_student_confirmed_ind => v_confirmed_ind,
x_commencement_dt => v_commencement_dt,
x_course_attempt_status => v_course_attempt_status,
x_derived_att_type => NULL,
x_derived_att_mode => NULL,
x_provisional_ind => v_provisional_ind,
x_discontinued_dt => NULL,
x_discontinuation_reason_cd => NULL,
x_lapsed_dt => NULL,
x_funding_source => v_funding_source,
x_exam_location_cd => NULL,
x_derived_completion_yr => NULL,
x_derived_completion_perd => NULL,
x_nominated_completion_yr => NULL,
x_nominated_completion_perd => NULL,
x_rule_check_ind => NULL,
x_waive_option_check_ind =>NULL,
x_last_rule_check_dt => NULL,
x_publish_outcomes_ind => NULL,
x_course_rqrmnt_complete_ind => NULL,
x_override_time_limitation => NULL,
x_course_rqrmnts_complete_dt => NULL,
x_advanced_standing_ind => NULL,
x_fee_cat => v_fee_cat,
x_correspondence_cat => v_correspondence_cat,
x_self_help_group_ind => NULL,
x_logical_delete_dt => NULL,
x_adm_admission_appl_number => v_acaiv_rec.admission_appl_number,
x_adm_nominated_course_cd => v_acaiv_rec.nominated_course_cd,
x_adm_sequence_number =>v_acaiv_rec.sequence_number,
x_mode => 'R',
x_progression_status => NULL,
X_S_COMPLETED_SOURCE_TYPE => 'MANUAL',
x_org_id => l_org_id,
x_last_date_of_attendance => NULL,
x_dropped_by => NULL,
X_IGS_PR_CLASS_STD_ID => NULL,
x_primary_program_type => NULL,
x_primary_prog_type_source => NULL,
x_catalog_cal_type => catalog_cal_type,
x_catalog_seq_num => catalog_seq_num,
x_key_program => NULL ,
x_override_cmpl_dt => NULL,
x_manual_ovr_cmpl_dt_ind => NULL,
-- added by ckasu as aprt of bug # 3544927
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_FUTURE_DATED_TRANS_FLAG => 'N');
cst_deleted) AND
(v_sca_rec.course_attempt_status = cst_deleted OR
v_sca_rec.location_cd <> v_acaiv_rec.location_cd OR
v_sca_rec.attendance_mode <> v_acaiv_rec.attendance_mode OR
v_sca_rec.attendance_type <> v_acaiv_rec.attendance_type OR
NVL(v_sca_rec.funding_source,'NULL') <> NVL(v_funding_source,'NULL') OR
v_sca_rec.provisional_ind <> v_provisional_ind OR
NVL(v_sca_rec.fee_cat,'NULL') <> NVL(v_fee_cat,'NULL') OR
NVL(v_sca_rec.correspondence_cat,'NULL') <>
NVL(v_correspondence_cat,'NULL') OR
NVL(v_sca_rec.nominated_completion_yr,9999) <>
NVL(v_acaiv_rec.expected_completion_yr,9999) OR
NVL(v_sca_rec.nominated_completion_perd,'X') <>
NVL(v_acaiv_rec.expected_completion_perd,'X') OR
NVL(v_sca_rec.adm_admission_appl_number,9999) <>
v_acaiv_rec.admission_appl_number OR
NVL(v_sca_rec.adm_nominated_course_cd,'NULL') <>
v_acaiv_rec.nominated_course_cd OR
NVL(v_sca_rec.adm_sequence_number,9999999) <>
v_acaiv_rec.sequence_number OR
(v_sca_rec.course_attempt_status = cst_unconfirm AND
p_dflt_confirmed_course_ind = 'Y')) THEN
BEGIN
OPEN c_sca_upd;
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => v_sca_upd_rec.rowid,
X_PERSON_ID => v_sca_upd_rec.PERSON_ID,
X_COURSE_CD => v_sca_upd_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => v_sca_upd_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => v_fee_cat,
X_CORRESPONDENCE_CAT => v_correspondence_cat,
X_SELF_HELP_GROUP_IND => v_sca_upd_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => NULL,
X_ADM_ADMISSION_APPL_NUMBER => v_acaiv_rec.admission_appl_number,
X_ADM_NOMINATED_COURSE_CD => v_acaiv_rec.nominated_course_cd,
X_ADM_SEQUENCE_NUMBER => v_acaiv_rec.sequence_number,
X_VERSION_NUMBER => v_sca_upd_rec.version_number,
X_CAL_TYPE => v_sca_upd_rec.cal_type,
X_LOCATION_CD => v_acaiv_rec.location_cd,
X_ATTENDANCE_MODE => v_acaiv_rec.attendance_mode,
X_ATTENDANCE_TYPE => v_acaiv_rec.attendance_type,
X_COO_ID => v_sca_upd_rec.coo_id,
X_STUDENT_CONFIRMED_IND => v_confirmed_ind,
X_COMMENCEMENT_DT => v_commencement_dt,
X_COURSE_ATTEMPT_STATUS => cst_unconfirm,
X_PROGRESSION_STATUS => v_sca_upd_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => v_sca_upd_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => v_sca_upd_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => v_provisional_ind,
X_DISCONTINUED_DT => NULL,
X_DISCONTINUATION_REASON_CD => NULL,
X_LAPSED_DT => v_sca_upd_rec.LAPSED_DT,
X_FUNDING_SOURCE => v_funding_source,
X_EXAM_LOCATION_CD => v_sca_upd_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => v_sca_upd_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => v_sca_upd_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => v_sca_upd_rec.nominated_completion_yr,
X_NOMINATED_COMPLETION_PERD => v_sca_upd_rec.nominated_completion_perd,
X_RULE_CHECK_IND => v_sca_upd_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => v_sca_upd_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => v_sca_upd_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => v_sca_upd_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => v_sca_upd_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_upd_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_sca_upd_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => v_sca_upd_rec.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
x_last_date_of_attendance => v_sca_upd_rec.last_date_of_attendance,
x_dropped_by => v_sca_upd_rec.dropped_by,
X_IGS_PR_CLASS_STD_ID => v_sca_upd_rec.igs_pr_class_std_id,
x_primary_program_type => v_sca_upd_rec.primary_program_type,
x_primary_prog_type_source => v_sca_upd_rec.primary_prog_type_source,
x_catalog_cal_type => catalog_cal_type,
x_catalog_seq_num => catalog_seq_num,
x_key_program => v_sca_upd_rec.key_program,
x_override_cmpl_dt => v_sca_upd_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => v_sca_upd_rec.manual_ovr_cmpl_dt_ind,
-- added by ckasu as aprt of bug # 3544927
X_ATTRIBUTE_CATEGORY => v_sca_upd_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => v_sca_upd_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => v_sca_upd_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => v_sca_upd_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => v_sca_upd_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => v_sca_upd_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => v_sca_upd_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => v_sca_upd_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => v_sca_upd_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => v_sca_upd_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => v_sca_upd_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => v_sca_upd_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => v_sca_upd_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => v_sca_upd_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => v_sca_upd_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => v_sca_upd_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => v_sca_upd_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => v_sca_upd_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => v_sca_upd_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => v_sca_upd_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => v_sca_upd_rec.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => v_sca_upd_rec.FUTURE_DATED_TRANS_FLAG);
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => v_sca_upd_rec.rowid,
X_PERSON_ID => v_sca_upd_rec.PERSON_ID,
X_COURSE_CD => v_sca_upd_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => v_sca_upd_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => v_fee_cat,
X_CORRESPONDENCE_CAT => v_correspondence_cat,
X_SELF_HELP_GROUP_IND => v_sca_upd_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => v_sca_upd_rec.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => v_acaiv_rec.admission_appl_number,
X_ADM_NOMINATED_COURSE_CD => v_acaiv_rec.nominated_course_cd,
X_ADM_SEQUENCE_NUMBER => v_acaiv_rec.sequence_number,
X_VERSION_NUMBER => v_sca_upd_rec.version_number,
X_CAL_TYPE => v_sca_upd_rec.cal_type,
X_LOCATION_CD => v_acaiv_rec.location_cd,
X_ATTENDANCE_MODE => v_acaiv_rec.attendance_mode,
X_ATTENDANCE_TYPE => v_acaiv_rec.attendance_type,
X_COO_ID => v_sca_upd_rec.coo_id,
X_STUDENT_CONFIRMED_IND => v_sca_upd_rec.STUDENT_confirmed_ind,
X_COMMENCEMENT_DT => v_sca_upd_rec.commencement_dt,
X_COURSE_ATTEMPT_STATUS => v_sca_upd_rec.COURSE_ATTEMPT_STATUS,
X_PROGRESSION_STATUS => v_sca_upd_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => v_sca_upd_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => v_sca_upd_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => v_provisional_ind,
X_DISCONTINUED_DT => NULL,
X_DISCONTINUATION_REASON_CD => NULL,
X_LAPSED_DT => NULL,
X_FUNDING_SOURCE => v_funding_source,
X_EXAM_LOCATION_CD => v_sca_upd_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => v_sca_upd_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => v_sca_upd_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => v_sca_upd_rec.nominated_completion_yr,
X_NOMINATED_COMPLETION_PERD => v_sca_upd_rec.nominated_completion_perd,
X_RULE_CHECK_IND => v_sca_upd_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => v_sca_upd_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => v_sca_upd_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => v_sca_upd_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => v_sca_upd_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_upd_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_sca_upd_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => v_sca_upd_rec.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
x_last_date_of_attendance => v_sca_upd_rec.last_date_of_attendance,
x_dropped_by => v_sca_upd_rec.dropped_by,
X_IGS_PR_CLASS_STD_ID => v_sca_upd_rec.igs_pr_class_std_id,
x_primary_program_type => v_sca_upd_rec.primary_program_type,
x_primary_prog_type_source => v_sca_upd_rec.primary_prog_type_source,
x_catalog_cal_type => v_sca_upd_rec.catalog_cal_type,
x_catalog_seq_num => v_sca_upd_rec.catalog_seq_num,
x_key_program => v_sca_upd_rec.key_program,
x_override_cmpl_dt => v_sca_upd_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => v_sca_upd_rec.manual_ovr_cmpl_dt_ind,
-- added by ckasu as aprt of bug # 3544927
X_ATTRIBUTE_CATEGORY => v_sca_upd_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => v_sca_upd_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => v_sca_upd_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => v_sca_upd_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => v_sca_upd_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => v_sca_upd_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => v_sca_upd_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => v_sca_upd_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => v_sca_upd_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => v_sca_upd_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => v_sca_upd_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => v_sca_upd_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => v_sca_upd_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => v_sca_upd_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => v_sca_upd_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => v_sca_upd_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => v_sca_upd_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => v_sca_upd_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => v_sca_upd_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => v_sca_upd_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => v_sca_upd_rec.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => v_sca_upd_rec.FUTURE_DATED_TRANS_FLAG);
IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
X_ROWID => v_sca_upd_rec.rowid,
X_PERSON_ID => v_sca_upd_rec.PERSON_ID,
X_COURSE_CD => v_sca_upd_rec.COURSE_CD,
X_ADVANCED_STANDING_IND => v_sca_upd_rec.ADVANCED_STANDING_IND,
X_FEE_CAT => v_fee_cat,
X_CORRESPONDENCE_CAT => v_correspondence_cat,
X_SELF_HELP_GROUP_IND => v_sca_upd_rec.SELF_HELP_GROUP_IND,
X_LOGICAL_DELETE_DT => v_sca_upd_rec.LOGICAL_DELETE_DT,
X_ADM_ADMISSION_APPL_NUMBER => v_acaiv_rec.admission_appl_number,
X_ADM_NOMINATED_COURSE_CD => v_acaiv_rec.nominated_course_cd,
X_ADM_SEQUENCE_NUMBER => v_acaiv_rec.sequence_number,
X_VERSION_NUMBER => v_sca_upd_rec.version_number,
X_CAL_TYPE => v_sca_upd_rec.cal_type,
X_LOCATION_CD => v_acaiv_rec.location_cd,
X_ATTENDANCE_MODE => v_acaiv_rec.attendance_mode,
X_ATTENDANCE_TYPE => v_acaiv_rec.attendance_type,
X_COO_ID => v_sca_upd_rec.coo_id,
X_STUDENT_CONFIRMED_IND => v_sca_upd_rec.STUDENT_confirmed_ind,
X_COMMENCEMENT_DT => v_sca_upd_rec.commencement_dt,
X_COURSE_ATTEMPT_STATUS => v_sca_upd_rec.COURSE_ATTEMPT_STATUS,
X_PROGRESSION_STATUS => v_sca_upd_rec.PROGRESSION_STATUS,
X_DERIVED_ATT_TYPE => v_sca_upd_rec.DERIVED_ATT_TYPE,
X_DERIVED_ATT_MODE => v_sca_upd_rec.DERIVED_ATT_MODE,
X_PROVISIONAL_IND => v_provisional_ind,
X_DISCONTINUED_DT => v_sca_upd_rec.DISCONTINUED_DT,
X_DISCONTINUATION_REASON_CD => v_sca_upd_rec.DISCONTINUATION_REASON_CD ,
X_LAPSED_DT => NULL,
X_FUNDING_SOURCE => v_funding_source,
X_EXAM_LOCATION_CD => v_sca_upd_rec.EXAM_LOCATION_CD,
X_DERIVED_COMPLETION_YR => v_sca_upd_rec.DERIVED_COMPLETION_YR,
X_DERIVED_COMPLETION_PERD => v_sca_upd_rec.DERIVED_COMPLETION_PERD,
X_NOMINATED_COMPLETION_YR => v_sca_upd_rec.nominated_completion_yr,
X_NOMINATED_COMPLETION_PERD => v_sca_upd_rec.nominated_completion_perd,
X_RULE_CHECK_IND => v_sca_upd_rec.RULE_CHECK_IND,
X_WAIVE_OPTION_CHECK_IND => v_sca_upd_rec.WAIVE_OPTION_CHECK_IND,
X_LAST_RULE_CHECK_DT => v_sca_upd_rec.LAST_RULE_CHECK_DT,
X_PUBLISH_OUTCOMES_IND => v_sca_upd_rec.PUBLISH_OUTCOMES_IND,
X_COURSE_RQRMNT_COMPLETE_IND => v_sca_upd_rec.COURSE_RQRMNT_COMPLETE_IND,
X_COURSE_RQRMNTS_COMPLETE_DT => v_sca_upd_rec.COURSE_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE => v_sca_upd_rec.S_COMPLETED_SOURCE_TYPE,
X_OVERRIDE_TIME_LIMITATION => v_sca_upd_rec.OVERRIDE_TIME_LIMITATION,
X_MODE => 'R',
x_last_date_of_attendance => v_sca_upd_rec.last_date_of_attendance,
x_dropped_by => v_sca_upd_rec.dropped_by,
X_IGS_PR_CLASS_STD_ID => v_sca_upd_rec.igs_pr_class_std_id,
x_primary_program_type => v_sca_upd_rec.primary_program_type,
x_primary_prog_type_source => v_sca_upd_rec.primary_prog_type_source,
x_catalog_cal_type => v_sca_upd_rec.catalog_cal_type,
x_catalog_seq_num => v_sca_upd_rec.catalog_seq_num,
x_key_program => v_sca_upd_rec.key_program,
x_override_cmpl_dt => v_sca_upd_rec.override_cmpl_dt,
x_manual_ovr_cmpl_dt_ind => v_sca_upd_rec.manual_ovr_cmpl_dt_ind,
-- added by ckasu as aprt of bug # 3544927
X_ATTRIBUTE_CATEGORY => v_sca_upd_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => v_sca_upd_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => v_sca_upd_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => v_sca_upd_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => v_sca_upd_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => v_sca_upd_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => v_sca_upd_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => v_sca_upd_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => v_sca_upd_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => v_sca_upd_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => v_sca_upd_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => v_sca_upd_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => v_sca_upd_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => v_sca_upd_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => v_sca_upd_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => v_sca_upd_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => v_sca_upd_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => v_sca_upd_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => v_sca_upd_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => v_sca_upd_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => v_sca_upd_rec.ATTRIBUTE20,
X_FUTURE_DATED_TRANS_FLAG => v_sca_upd_rec.FUTURE_DATED_TRANS_FLAG);
SELECT enrolment_cat
FROM IGS_AS_SC_ATMPT_ENR scae
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
cal_type = cp_enr_cal_type AND
ci_sequence_number = cp_enr_sequence_number;
SELECT rowid,
IGS_AS_SC_ATMPT_ENR.*
FROM IGS_AS_SC_ATMPT_ENR
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
cal_type = cp_enr_cal_type AND
ci_sequence_number = cp_enr_sequence_number
FOR UPDATE OF enrolment_cat NOWAIT;
IGS_AS_SC_ATMPT_ENR_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_cal_type => v_enr_cal_type,
x_ci_sequence_number => v_enr_sequence_number,
x_enrolment_cat => v_enrolment_cat,
x_enrolled_dt => NULL,
x_enr_form_due_dt => p_override_enr_form_due_dt,
x_enr_pckg_prod_dt => p_override_enr_pckg_prod_dt,
x_enr_form_received_dt => NULL );
IGS_AS_SC_ATMPT_ENR_PKG.UPDATE_ROW(
X_ROWID => v_scae_upd_rec.rowid,
X_PERSON_ID => v_scae_upd_rec.PERSON_ID,
X_COURSE_CD => v_scae_upd_rec.COURSE_CD,
X_CAL_TYPE => v_scae_upd_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => v_scae_upd_rec.CI_SEQUENCE_NUMBER,
X_ENROLMENT_CAT => v_enrolment_cat,
X_ENROLLED_DT => v_scae_upd_rec.ENROLLED_DT,
X_ENR_FORM_DUE_DT => v_scae_upd_rec.ENR_FORM_DUE_DT,
X_ENR_PCKG_PROD_DT => v_scae_upd_rec.ENR_PCKG_PROD_DT ,
X_ENR_FORM_RECEIVED_DT => v_scae_upd_rec.ENR_FORM_RECEIVED_DT,
X_MODE => 'R' );
svanukur 01-jul-2004 setting the selection date of the unit set attempt to SPA commencement date
as part of bug fix 3687470
*/
RETURN BOOLEAN
AS
BEGIN -- enrpl_copy_adm_unit_sets
-- Create any required IGS_AS_SU_SETATMPT details
DECLARE
-- Bug#2347141
-- This cursor got modified to retrive the all the values of the columns which
-- helps in case of updation
CURSOR c_susa IS
SELECT susa.*,susa.rowid
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = v_acaiv_rec.unit_set_cd AND
susa.us_version_number = v_acaiv_rec.us_version_number AND
susa.end_dt IS NULL;
SELECT us.authorisation_rqrd_ind,
usc.s_unit_set_cat
FROM IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_CAT usc
WHERE us.unit_set_cd = cp_unit_set_cd AND
us.version_number = cp_version_number AND
usc.unit_set_cat = us.unit_set_cat;
SELECT spa.student_confirmed_ind,
spa.commencement_dt ,spa.version_number
FROM IGS_EN_STDNT_PS_ATT spa
WHERE spa.person_id = p_person_id AND
spa.course_cd = p_course_cd;
v_selection_dt IGS_AS_SU_SETATMPT.selection_dt%TYPE;
v_selection_dt := NULL;
v_selection_dt := v_spa_rec.commencement_dt;
TRUNC(v_selection_dt),
v_susa_rec.rqrmnts_complete_dt,
v_susa_rec.end_dt,
v_susa_rec.sequence_number,
v_susa_rec.unit_set_cd,
v_susa_rec.us_version_number,
p_message_name) = FALSE THEN
p_warn_level := cst_error;
IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW(
X_ROWID => v_susa_rec.rowid,
X_PERSON_ID => v_susa_rec.person_id ,
X_COURSE_CD => v_susa_rec.course_cd ,
X_UNIT_SET_CD => v_susa_rec.unit_set_cd ,
X_SEQUENCE_NUMBER => v_susa_rec.sequence_number,
X_US_VERSION_NUMBER => v_susa_rec.us_version_number,
X_SELECTION_DT => TRUNC(v_selection_dt),
X_STUDENT_CONFIRMED_IND => v_confirmed_ind,
X_END_DT => v_susa_rec.end_dt ,
X_PARENT_UNIT_SET_CD => v_susa_rec.parent_unit_set_cd ,
X_PARENT_SEQUENCE_NUMBER => v_susa_rec.parent_sequence_number ,
X_PRIMARY_SET_IND => v_susa_rec.primary_set_ind ,
X_VOLUNTARY_END_IND => v_susa_rec.voluntary_end_ind ,
X_AUTHORISED_PERSON_ID => v_susa_rec.authorised_person_id ,
X_AUTHORISED_ON => v_susa_rec.authorised_on ,
X_OVERRIDE_TITLE => v_susa_rec.override_title ,
X_RQRMNTS_COMPLETE_IND => v_susa_rec.rqrmnts_complete_ind ,
X_RQRMNTS_COMPLETE_DT => v_susa_rec.rqrmnts_complete_dt ,
X_S_COMPLETED_SOURCE_TYPE => v_susa_rec.s_completed_source_type ,
X_CATALOG_CAL_TYPE => v_susa_rec.catalog_cal_type ,
X_CATALOG_SEQ_NUM => v_susa_rec.catalog_seq_num ,
X_ATTRIBUTE_CATEGORY => v_susa_rec.attribute_category ,
X_ATTRIBUTE1 => v_susa_rec.attribute1 ,
X_ATTRIBUTE2 => v_susa_rec.attribute2 ,
X_ATTRIBUTE3 => v_susa_rec.attribute3 ,
X_ATTRIBUTE4 => v_susa_rec.attribute4 ,
X_ATTRIBUTE5 => v_susa_rec.attribute5 ,
X_ATTRIBUTE6 => v_susa_rec.attribute6 ,
X_ATTRIBUTE7 => v_susa_rec.attribute7 ,
X_ATTRIBUTE8 => v_susa_rec.attribute8 ,
X_ATTRIBUTE9 => v_susa_rec.attribute9 ,
X_ATTRIBUTE10 => v_susa_rec.attribute10 ,
X_ATTRIBUTE11 => v_susa_rec.attribute11 ,
X_ATTRIBUTE12 => v_susa_rec.attribute12 ,
X_ATTRIBUTE13 => v_susa_rec.attribute13 ,
X_ATTRIBUTE14 => v_susa_rec.attribute14 ,
X_ATTRIBUTE15 => v_susa_rec.attribute15 ,
X_ATTRIBUTE16 => v_susa_rec.attribute16 ,
X_ATTRIBUTE17 => v_susa_rec.attribute17 ,
X_ATTRIBUTE18 => v_susa_rec.attribute18 ,
X_ATTRIBUTE19 => v_susa_rec.attribute19 ,
X_ATTRIBUTE20 => v_susa_rec.attribute20 ,
X_MODE => 'R');
IF NOT update_stream_unit_sets(
p_person_id,
p_course_cd,
v_susa_rec.unit_set_cd,
v_susa_rec.rqrmnts_complete_ind,
v_susa_rec.rqrmnts_complete_dt,
v_selection_dt,
v_confirmed_ind,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
v_selection_dt,
v_confirmed_ind,
v_authorised_person_id,
v_authorised_on,
l_seqval,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
v_selection_dt,
v_confirmed_ind,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
SELECT acaiu.unit_cd,
acaiu.uv_version_number,
acaiu.cal_type,
acaiu.ci_sequence_number,
acaiu.location_cd,
acaiu.unit_class
FROM IGS_AD_PS_APLINSTUNT acaiu,
IGS_AD_UNIT_OU_STAT auos,
IGS_PS_UNIT_OFR_OPT uoo
WHERE acaiu.person_id = v_acaiv_rec.person_id AND
acaiu.admission_appl_number = v_acaiv_rec.admission_appl_number AND
acaiu.nominated_course_cd = v_acaiv_rec.nominated_course_cd AND
acaiu.acai_sequence_number = v_acaiv_rec.sequence_number AND
auos.ADM_UNIT_OUTCOME_STATUS = acaiu.ADM_UNIT_OUTCOME_STATUS AND
auos.s_adm_outcome_status = cst_offer AND
acaiu.unit_cd = uoo.unit_cd AND
acaiu.uv_version_number = uoo.version_number AND
acaiu.cal_type = uoo.cal_type AND
acaiu.ci_sequence_number = uoo.ci_sequence_number AND
acaiu.location_cd = uoo.location_cd AND
acaiu.unit_class = uoo.unit_class
ORDER BY uoo.sup_uoo_id DESC;
SELECT 'x'
FROM IGS_EN_SU_ATTEMPT sua
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
unit_cd = cp_unit_cd AND
version_number = cp_uv_version_number;
SELECT uoo_id
FROM IGS_PS_UNIT_OFR_OPT
WHERE unit_cd = cp_unit_cd
AND cal_type = cp_cal_type
AND ci_sequence_number = cp_ci_sequence_number
AND location_cd = cp_location_cd
AND unit_class = cp_unit_class;
SELECT DECODE(sua.unit_attempt_status, 'UNCONFIRM', 'N', 'WAITLISTED', 'Y' , NULL)
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_uoo_id;
SELECT cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT am.GOVT_ATTENDANCE_MODE
FROM IGS_EN_ATD_MODE am
WHERE am.attendance_mode = cp_attendance_mode;
SELECT ci.sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_sequence_number AND
ci.cal_type = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
ci.cal_type = cp_cal_type AND
cat.cal_type = ci.cal_type AND
cat.S_CAL_CAT = 'TEACHING' AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_cal_status = 'ACTIVE'
ORDER BY ci.start_dt;
SELECT 'x'
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd AND
unit_cd = cp_unit_cd AND
cal_type = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number AND
location_cd = cp_location_cd AND
unit_class = cp_unit_class;
SELECT DECODE(sua.unit_attempt_status, 'UNCONFIRM', 'N', 'WAITLISTED', 'Y' , NULL)
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_uoo_id;
SELECT relation_type
FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = p_uoo_id;
SELECT cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT DECODE( v_counter,
1, p_unit1_unit_cd,
2, p_unit2_unit_cd,
3, p_unit3_unit_cd,
4, p_unit4_unit_cd,
5, p_unit5_unit_cd,
6, p_unit6_unit_cd,
7, p_unit7_unit_cd,
8, p_unit8_unit_cd,
9, p_unit9_unit_cd,
10, p_unit10_unit_cd,
11, p_unit11_unit_cd,
12, p_unit12_unit_cd,
NULL),
DECODE( v_counter,
1, p_unit1_cal_type,
2, p_unit2_cal_type,
3, p_unit3_cal_type,
4, p_unit4_cal_type,
5, p_unit5_cal_type,
6, p_unit6_cal_type,
7, p_unit7_cal_type,
8, p_unit8_cal_type,
9, p_unit9_cal_type,
10, p_unit10_cal_type,
11, p_unit11_cal_type,
12, p_unit12_cal_type,
NULL),
DECODE( v_counter,
1, p_unit1_location_cd,
2, p_unit2_location_cd,
3, p_unit3_location_cd,
4, p_unit4_location_cd,
5, p_unit5_location_cd,
6, p_unit6_location_cd,
7, p_unit7_location_cd,
8, p_unit8_location_cd,
9, p_unit9_location_cd,
10, p_unit10_location_cd,
11, p_unit11_location_cd,
12, p_unit12_location_cd,
NULL),
DECODE( v_counter,
1, p_unit1_unit_class,
2, p_unit2_unit_class,
3, p_unit3_unit_class,
4, p_unit4_unit_class,
5, p_unit5_unit_class,
6, p_unit6_unit_class,
7, p_unit7_unit_class,
8, p_unit8_unit_class,
9, p_unit9_unit_class,
10, p_unit10_unit_class,
11, p_unit11_unit_class,
12, p_unit12_unit_class,
NULL)
INTO v_unit_cd,
v_cal_type,
v_location_cd,
v_unit_class
FROM DUAL;
SELECT 'x'
FROM IGS_AD_PRCS_CAT_STEP apcs
WHERE apcs.admission_cat = v_acaiv_rec.admission_cat AND
apcs.s_admission_process_type = v_acaiv_rec.s_admission_process_type AND
apcs.s_admission_step_type = cst_fee_cntrct AND
apcs.step_group_type <> 'TRACK'; -- 2402377
OPEN c_prog_outcome(c_active_us_rec.selection_dt) ;
c_susa_upd_rec.selection_dt,
v_rqrmnts_complete_dt,
c_susa_upd_rec.end_dt,
c_susa_upd_rec.sequence_number,
c_susa_upd_rec.unit_set_cd,
c_susa_upd_rec.us_version_number,
p_message_name) = FALSE THEN
p_warn_level := cst_error;
IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
X_ROWID => c_susa_upd_rec.rowid,
X_PERSON_ID => c_susa_upd_rec.person_id ,
X_COURSE_CD => c_susa_upd_rec.course_cd ,
X_UNIT_SET_CD => c_susa_upd_rec.unit_set_cd ,
X_SEQUENCE_NUMBER => c_susa_upd_rec.sequence_number ,
X_US_VERSION_NUMBER => c_susa_upd_rec.us_version_number,
X_SELECTION_DT => c_susa_upd_rec.selection_dt ,
X_STUDENT_CONFIRMED_IND => c_susa_upd_rec.student_confirmed_ind ,
X_END_DT => c_susa_upd_rec.end_dt ,
X_PARENT_UNIT_SET_CD => c_susa_upd_rec.parent_unit_set_cd,
X_PARENT_SEQUENCE_NUMBER => c_susa_upd_rec.parent_sequence_number ,
X_PRIMARY_SET_IND => c_susa_upd_rec.primary_set_ind ,
X_VOLUNTARY_END_IND => c_susa_upd_rec.voluntary_end_ind ,
X_AUTHORISED_PERSON_ID => c_susa_upd_rec.authorised_person_id,
X_AUTHORISED_ON => c_susa_upd_rec.authorised_on ,
X_OVERRIDE_TITLE => c_susa_upd_rec.override_title ,
X_RQRMNTS_COMPLETE_IND => 'Y' ,
X_RQRMNTS_COMPLETE_DT => v_rqrmnts_complete_dt ,
X_S_COMPLETED_SOURCE_TYPE => c_susa_upd_rec.s_completed_source_type,
X_CATALOG_CAL_TYPE => c_susa_upd_rec.catalog_cal_type ,
X_CATALOG_SEQ_NUM => c_susa_upd_rec.catalog_seq_num,
X_ATTRIBUTE_CATEGORY => c_susa_upd_rec.attribute_category,
X_ATTRIBUTE1 => c_susa_upd_rec.attribute1 ,
X_ATTRIBUTE2 => c_susa_upd_rec.attribute2 ,
X_ATTRIBUTE3 => c_susa_upd_rec.attribute3,
X_ATTRIBUTE4 => c_susa_upd_rec.attribute4,
X_ATTRIBUTE5 => c_susa_upd_rec.attribute5,
X_ATTRIBUTE6 => c_susa_upd_rec.attribute6,
X_ATTRIBUTE7 => c_susa_upd_rec.attribute7,
X_ATTRIBUTE8 => c_susa_upd_rec.attribute8,
X_ATTRIBUTE9 => c_susa_upd_rec.attribute9,
X_ATTRIBUTE10 => c_susa_upd_rec.attribute10,
X_ATTRIBUTE11 => c_susa_upd_rec.attribute11,
X_ATTRIBUTE12 => c_susa_upd_rec.attribute12,
X_ATTRIBUTE13 => c_susa_upd_rec.attribute13,
X_ATTRIBUTE14 => c_susa_upd_rec.attribute14,
X_ATTRIBUTE15 => c_susa_upd_rec.attribute15,
X_ATTRIBUTE16 => c_susa_upd_rec.attribute16,
X_ATTRIBUTE17 => c_susa_upd_rec.attribute17,
X_ATTRIBUTE18 => c_susa_upd_rec.attribute18,
X_ATTRIBUTE19 => c_susa_upd_rec.attribute19,
X_ATTRIBUTE20 => c_susa_upd_rec.attribute20,
X_MODE => 'R' );
IF NOT update_stream_unit_sets(
p_person_id,
p_course_Cd,
c_susa_upd_rec.unit_set_cd,
'Y', --RQRMNTS_COMPLETE_IND
v_rqrmnts_complete_dt,
c_susa_upd_rec.selection_dt,
c_susa_upd_rec.student_confirmed_ind,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
v_selection_dt := NULL;
FETCH cur_spa INTO v_selection_dt;
TRUNC(v_selection_dt),
c_susa_upd_rec.RQRMNTS_COMPLETE_DT,
c_susa_upd_rec.end_dt,
c_susa_upd_rec.sequence_number,
c_susa_upd_rec.unit_set_cd,
c_susa_upd_rec.us_version_number,
p_message_name) = FALSE THEN
p_warn_level := cst_error;
IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
X_ROWID => c_susa_upd_rec.rowid,
X_PERSON_ID => c_susa_upd_rec.person_id,
X_COURSE_CD => c_susa_upd_rec.course_cd ,
X_UNIT_SET_CD => c_susa_upd_rec.unit_set_cd ,
X_SEQUENCE_NUMBER => c_susa_upd_rec.sequence_number ,
X_US_VERSION_NUMBER => c_susa_upd_rec.us_version_number ,
X_SELECTION_DT => TRUNC(v_selection_dt) ,
X_STUDENT_CONFIRMED_IND => l_confirmed_ind ,
X_END_DT => c_susa_upd_rec.end_dt ,
X_PARENT_UNIT_SET_CD => c_susa_upd_rec.parent_unit_set_cd,
X_PARENT_SEQUENCE_NUMBER => c_susa_upd_rec.PARENT_SEQUENCE_NUMBER ,
X_PRIMARY_SET_IND => c_susa_upd_rec.PRIMARY_SET_IND ,
X_VOLUNTARY_END_IND => c_susa_upd_rec.VOLUNTARY_END_IND ,
X_AUTHORISED_PERSON_ID => c_susa_upd_rec.AUTHORISED_PERSON_ID ,
X_AUTHORISED_ON => c_susa_upd_rec.AUTHORISED_ON ,
X_OVERRIDE_TITLE => c_susa_upd_rec.OVERRIDE_TITLE ,
X_RQRMNTS_COMPLETE_IND => c_susa_upd_rec.RQRMNTS_COMPLETE_IND ,
X_RQRMNTS_COMPLETE_DT => c_susa_upd_rec.RQRMNTS_COMPLETE_DT ,
X_S_COMPLETED_SOURCE_TYPE => c_susa_upd_rec.S_COMPLETED_SOURCE_TYPE,
X_CATALOG_CAL_TYPE => c_susa_upd_rec.CATALOG_CAL_TYPE,
X_CATALOG_SEQ_NUM => c_susa_upd_rec.CATALOG_SEQ_NUM,
X_ATTRIBUTE_CATEGORY => c_susa_upd_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_susa_upd_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_susa_upd_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_susa_upd_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_susa_upd_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_susa_upd_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_susa_upd_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_susa_upd_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_susa_upd_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_susa_upd_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_susa_upd_rec.ATTRIBUTE10,
X_ATTRIBUTE11 =>c_susa_upd_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_susa_upd_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_susa_upd_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_susa_upd_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_susa_upd_rec.ATTRIBUTE15,
X_ATTRIBUTE16 => c_susa_upd_rec.ATTRIBUTE16,
X_ATTRIBUTE17 => c_susa_upd_rec.ATTRIBUTE17,
X_ATTRIBUTE18 => c_susa_upd_rec.ATTRIBUTE18,
X_ATTRIBUTE19 => c_susa_upd_rec.ATTRIBUTE19,
X_ATTRIBUTE20 => c_susa_upd_rec.ATTRIBUTE20,
X_MODE => 'R' );
IF NOT update_stream_unit_sets(
p_person_id,
p_course_cd,
c_susa_upd_rec.unit_set_cd,
c_susa_upd_rec.rqrmnts_complete_ind,
c_susa_upd_rec.rqrmnts_complete_dt,
v_selection_dt,
l_confirmed_ind,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
v_selection_dt,
l_confirmed_ind,
NULL,
NULL,
l_seqval,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
v_selection_dt,
l_confirmed_ind,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
END IF; -- if unit set selected in the admissions is not of type year
p_selection_date IN DATE ,
--Added the parameter p_completion_date - ENCR030(UK Enh) Build - Enh Bug#2708430 - 16DEC2002
p_completion_date IN DATE ,
p_progress_stat IN VARCHAR2,
p_dflt_enr_method IN VARCHAR2,
p_load_cal_type IN VARCHAR2,
p_load_ci_seq_num IN NUMBER
)
RETURN boolean AS
/* HISTORY
WHO WHEN WHAT
bdeviset 29-JUL-2004 Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW in a check is
made to see that their is no overlapping of selection,completion and
end dates for any two unit sets by calling check_usa_overlap.If it returns
false log entry is made and the insert or update is not carried out for bug 3149133.
ayedubat 4-JUN-2002 Changed the Code of YOP for Unit Set pre-enrollment before
the Units pre-enrollment Code for the bug fix: 2391842
ayedubat 25-MAY-2002 Changed the cursors c_acaiv to replace the view,IGS_AD_PS_APPL_INST_APLINST_V
with the base table,IGS_AD_PS_APPL_INST as part of the bug fix:2384449
ayedubat 15-MAY-2002 Changed the cursor,c_chk_census_dt to consider only the SUA records with unit attempt status
'ENROLLED','DISCONTIN','DUPLICATE' or 'COMPLETED' as part of the bug:2372892
Nishikant 07OCT2002 UK Enhancement Build - Enh Bug#2580731 - Added the parameter p_selection_date in this Function
Nishikant 16DEC2002 ENCR030(UK Enh) Build - Enh Bug#2708430 - Added the parameter p_completion_date in this Function
svanukur 10-jul-2003 checking for parameter P_PROGRESS_STAT , if it is set to 'ADVANCE' as part of bug #3043374
ptandon 06-Oct-2003 Modified the inline procedure enrpl_copy_param_sua as part of Prevent Dropping Core Units.
Enh Bug# 3052432.
svanukur 20-jul-2004 Added a check after call to procedure IGS_EN_GEN_009.enrp_ins_pre_pos to return false to igs_en_gen_008
so that the message successfully preenrolled is not shown in the log file. BUG 3032588.
*/
BEGIN -- enrp_ins_sret_prenrl
-- This process will pre-enrol a single returning student in the specified
-- IGS_PS_COURSE. The following steps will be performed :
-- * Check the students eligibility to enrol in the specified IGS_PS_COURSE in the
-- specified academic calendar.
-- * Determine the enrolment category from either a previous pre-enrolment
-- or the default enrolment category passed to the routine.
-- * Create IGS_AS_SC_ATMPT_ENR record.
-- * Pre-enrol the students IGS_EN_SU_ATTEMPT records (next phase)
-- If at any point it becomes impossible to pre-enrol the student, the
-- routine will return FALSE and message number of a message indicating the
-- reason for failure. This can be used by the calling routine (whether batch
-- or online) to indicate who was and wasn't pre-enrolled.
DECLARE
cst_return CONSTANT VARCHAR2(10) := 'RETURN';
SELECT sca.cal_type,
sca.course_cd,
sca.version_number,
sca.location_cd,
sca.attendance_mode,
sca.attendance_type,
sca.adm_admission_appl_number,
sca.adm_nominated_course_cd,
sca.adm_sequence_number
FROM IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = p_person_id AND
sca.course_cd = p_course_cd;
SELECT scae.enrolment_cat
FROM IGS_AS_SC_ATMPT_ENR scae,
IGS_CA_INST ci
WHERE scae.person_id = p_person_id AND
scae.course_cd = p_course_cd AND
ci.cal_type = scae.cal_type AND
ci.sequence_number = scae.ci_sequence_number
ORDER BY ci.end_dt DESC;
SELECT enrolment_cat
FROM IGS_AS_SC_ATMPT_ENR scae
WHERE scae.person_id = p_person_id AND
scae.course_cd = p_course_cd AND
scae.cal_type = p_enrol_cal_type AND
scae.ci_sequence_number = p_enrol_sequence_number;
SELECT rowid,
IGS_AS_SC_ATMPT_ENR.*
FROM IGS_AS_SC_ATMPT_ENR
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
cal_type = p_enrol_cal_type AND
ci_sequence_number = p_enrol_sequence_number
FOR UPDATE OF enrolment_cat NOWAIT;
SELECT 'x'
FROM IGS_PS_VER crv,
IGS_PS_STAT cs,
IGS_PS_OFR_PAT cop
WHERE crv.course_cd = cp_course_cd AND
crv.version_number = cp_version_number AND
crv.expiry_dt IS NULL AND
cs.COURSE_STATUS = crv.COURSE_STATUS AND
cs.s_course_status = 'ACTIVE' AND
cop.course_cd = crv.course_cd AND
cop.version_number = crv.version_number AND
cop.cal_type = cp_cal_type AND
cop.ci_sequence_number = p_acad_sequence_number AND
cop.location_cd = cp_location_cd AND
cop.attendance_mode = cp_attendance_mode AND
cop.attendance_type = cp_attendance_type AND
cop.offered_ind = 'Y';
SELECT acaiv.unit_set_cd,
acaiv.adm_cal_type,
aa.admission_cat
FROM IGS_AD_PS_APPL_INST acaiv,
IGS_AD_APPL aa
WHERE
acaiv.person_id = p_person_id AND
acaiv.admission_appl_number = cp_adm_admission_appl_number AND
acaiv.nominated_course_cd = cp_adm_nominated_course_cd AND
acaiv.sequence_number = cp_adm_sequence_number AND
aa.person_id = acaiv.person_id AND
aa.admission_appl_number = acaiv.admission_appl_number;
SELECT susa.unit_set_cd
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL;
SELECT unit_set_cd
FROM IGS_PS_PAT_OF_STUDY pos
WHERE course_cd = p_course_cd AND
version_number = cp_version_number AND
cal_type = p_acad_cal_type AND
unit_set_cd IN
( SELECT susa.unit_set_cd
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = pos.course_cd AND
susa.student_confirmed_ind = 'Y' AND
susa.end_dt IS NULL);
CURSOR c_prog_outcome(cp_select_dt igs_as_su_setatmpt.selection_dt%TYPE) IS
SELECT pou.decision_dt, pout.s_progression_outcome_type
FROM igs_pr_stdnt_pr_ou_all pou , igs_pr_ou_type pout
WHERE pou.person_id = p_person_id AND
pou.course_cd = p_course_cd AND
pou.decision_status = 'APPROVED' AND
pou.decision_dt IS NOT NULL AND
pou.decision_dt > cp_select_dt AND
pou.progression_outcome_type = pout.progression_outcome_type
ORDER BY pou.decision_dt desc ;
SELECT susa.*
FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.selection_dt IS NOT NULL AND
susa.end_dt IS NULL AND
susa.rqrmnts_complete_dt IS NULL AND
susa.unit_set_cd = us.unit_set_cd AND
us.unit_set_cat = usc.unit_set_cat AND
usc.s_unit_set_cat = 'PRENRL_YR' ;
SELECT cf1.unit_set_cd , cf1.sequence_no
FROM igs_ps_us_prenr_cfg cf1 , igs_ps_us_prenr_cfg cf2
WHERE cf2.mapping_set_cd = cf1.mapping_set_cd AND
cf2.unit_set_cd = cp_unit_set_cd AND
cf1.sequence_no > cf2.sequence_no
ORDER BY cf1.sequence_no asc;
SELECT coous.us_version_number
FROM igs_en_unit_set_stat uss, igs_ps_ofr_opt_unit_set_v coous, igs_en_stdnt_ps_att sca
WHERE sca.person_id = cp_person_id AND
sca.course_cd = cp_course_cd AND
sca.coo_id = coous.coo_id AND
coous.unit_set_cd = cp_unit_set_cd AND
coous.expiry_dt IS NULL AND
coous.unit_set_status = uss.unit_set_status AND
uss.s_unit_set_status = 'ACTIVE' ;
SELECT susa.unit_set_cd, susa.us_version_number ,susa.sequence_number ,susa.rqrmnts_complete_dt
, susa.selection_dt
FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.rqrmnts_complete_dt IS NOT NULL AND
susa.unit_set_cd = us.unit_set_cd AND
us.unit_set_cat = usc.unit_set_cat AND
usc.s_unit_set_cat = 'PRENRL_YR'
ORDER BY susa.rqrmnts_complete_dt desc ;
SELECT sua.*
FROM igs_en_sua_year_v sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.unit_set_cd = cp_unit_set_cd AND
sua.unit_attempt_status IN ('ENROLLED','DISCONTIN','DUPLICATE','COMPLETED') AND
IGS_EN_GEN_015.get_effective_census_date(Null,Null,sua.cal_type,sua.ci_sequence_number) < TRUNC(SYSDATE);
SELECT rel.sub_cal_type, rel.sub_ci_sequence_number FROM igs_ca_inst_rel rel,
igs_ca_inst ci,
igs_ca_type cal
WHERE rel.sup_cal_type = p_acad_cal_type
AND rel.sup_ci_sequence_number = p_acad_seq_num
AND rel.sub_cal_type = ci.cal_type
AND rel.sub_ci_sequence_number = ci.sequence_number
AND rel.sub_cal_type = cal.cal_type
AND cal.s_cal_cat = 'LOAD'
AND cal.closed_ind = 'N'
ORDER BY ci.start_dt;
SELECT am.GOVT_ATTENDANCE_MODE
FROM IGS_EN_ATD_MODE am
WHERE am.attendance_mode = cp_attendance_mode;
SELECT ci.sequence_number
FROM IGS_CA_INST_REL cir,
IGS_CA_INST ci,
IGS_CA_TYPE cat,
IGS_CA_STAT cs
WHERE cir.sup_cal_type = p_acad_cal_type AND
cir.sup_ci_sequence_number = p_acad_sequence_number AND
ci.cal_type = cir.sub_cal_type AND
ci.sequence_number = cir.sub_ci_sequence_number AND
ci.cal_type = cp_cal_type AND
cat.cal_type = ci.cal_type AND
cat.S_CAL_CAT = 'TEACHING' AND
cs.CAL_STATUS = ci.CAL_STATUS AND
cs.s_cal_status = 'ACTIVE'
ORDER BY ci.start_dt;
SELECT 'x'
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd AND
unit_cd = cp_unit_cd AND
cal_type = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number AND
location_cd = cp_location_cd AND
unit_class = cp_unit_class;
SELECT DECODE(sua.unit_attempt_status, 'UNCONFIRM', 'N', 'WAITLISTED', 'Y' , NULL)
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_uoo_id;
SELECT relation_type
FROM IGS_PS_UNIT_OFR_OPT
WHERE uoo_id = p_uoo_id;
SELECT cal_type, ci_sequence_number
FROM igs_ps_unit_ofr_opt
WHERE uoo_id = p_uoo_id;
SELECT DECODE( v_counter,
1, p_unit1_unit_cd,
2, p_unit2_unit_cd,
3, p_unit3_unit_cd,
4, p_unit4_unit_cd,
5, p_unit5_unit_cd,
6, p_unit6_unit_cd,
7, p_unit7_unit_cd,
8, p_unit8_unit_cd,
9, p_unit9_unit_cd,
10, p_unit10_unit_cd,
11, p_unit11_unit_cd,
12, p_unit12_unit_cd,
NULL),
DECODE( v_counter,
1, p_unit1_cal_type,
2, p_unit2_cal_type,
3, p_unit3_cal_type,
4, p_unit4_cal_type,
5, p_unit5_cal_type,
6, p_unit6_cal_type,
7, p_unit7_cal_type,
8, p_unit8_cal_type,
9, p_unit9_cal_type,
10, p_unit10_cal_type,
11, p_unit11_cal_type,
12, p_unit12_cal_type,
NULL),
DECODE( v_counter,
1, p_unit1_location_cd,
2, p_unit2_location_cd,
3, p_unit3_location_cd,
4, p_unit4_location_cd,
5, p_unit5_location_cd,
6, p_unit6_location_cd,
7, p_unit7_location_cd,
8, p_unit8_location_cd,
9, p_unit9_location_cd,
10, p_unit10_location_cd,
11, p_unit11_location_cd,
12, p_unit12_location_cd,
NULL),
DECODE( v_counter,
1, p_unit1_unit_class,
2, p_unit2_unit_class,
3, p_unit3_unit_class,
4, p_unit4_unit_class,
5, p_unit5_unit_class,
6, p_unit6_unit_class,
7, p_unit7_unit_class,
8, p_unit8_unit_class,
9, p_unit9_unit_class,
10, p_unit10_unit_class,
11, p_unit11_unit_class,
12, p_unit12_unit_class,
NULL)
INTO v_unit_cd,
v_cal_type,
v_location_cd,
v_unit_class
FROM DUAL;
IGS_AS_SC_ATMPT_ENR_PKG.UPDATE_ROW(
X_ROWID => v_scae_upd_rec.rowid,
X_PERSON_ID => v_scae_upd_rec.PERSON_ID,
X_COURSE_CD => v_scae_upd_rec.COURSE_CD,
X_CAL_TYPE => v_scae_upd_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => v_scae_upd_rec.CI_SEQUENCE_NUMBER,
X_ENROLMENT_CAT => v_enrolment_cat,
X_ENROLLED_DT => v_scae_upd_rec.ENROLLED_DT,
X_ENR_FORM_DUE_DT => v_scae_upd_rec.ENR_FORM_DUE_DT,
X_ENR_PCKG_PROD_DT => v_scae_upd_rec.ENR_PCKG_PROD_DT ,
X_ENR_FORM_RECEIVED_DT => v_scae_upd_rec.ENR_FORM_RECEIVED_DT,
X_MODE => 'R' );
IGS_AS_SC_ATMPT_ENR_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_cal_type => p_enrol_cal_type,
x_ci_sequence_number => p_enrol_sequence_number,
x_enrolment_cat => v_enrolment_cat,
x_enrolled_dt => NULL,
x_enr_form_due_dt => p_override_enr_form_due_dt,
x_enr_pckg_prod_dt => p_override_enr_pckg_prod_dt,
x_enr_form_received_dt => NULL );
OPEN c_prog_outcome(c_active_us_rec.selection_dt) ;
SELECT rowid,IGS_AS_SU_SETATMPT.*
FROM IGS_AS_SU_SETATMPT
WHERE person_id = c_active_us_rec.person_id AND
course_cd = c_active_us_rec.course_cd AND
unit_set_cd = c_active_us_rec.unit_set_cd AND
us_version_number = c_active_us_rec.us_version_number AND
sequence_number = c_active_us_rec.sequence_number
FOR UPDATE OF RQRMNTS_COMPLETE_IND ,
RQRMNTS_COMPLETE_DT NOWAIT;
IF p_completion_date IS NULL AND p_selection_date IS NULL THEN
l_completion_date := SYSDATE - 1;
ELSIF p_completion_date IS NULL AND p_selection_date IS NOT NULL THEN
l_completion_date := p_selection_date - 1;
c_susa_upd_rec.selection_dt,
NVL(p_completion_date, TRUNC(l_completion_date)),
c_susa_upd_rec.end_dt,
c_susa_upd_rec.sequence_number,
c_susa_upd_rec.unit_set_cd,
c_susa_upd_rec.us_version_number,
p_message_name) = FALSE THEN
p_warn_level := cst_error;
IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
X_ROWID => c_susa_upd_rec.rowid,
X_PERSON_ID => c_susa_upd_rec.person_id ,
X_COURSE_CD => c_susa_upd_rec.course_cd ,
X_UNIT_SET_CD => c_susa_upd_rec.unit_set_cd ,
X_SEQUENCE_NUMBER => c_susa_upd_rec.sequence_number ,
X_US_VERSION_NUMBER => c_susa_upd_rec.us_version_number,
X_SELECTION_DT => c_susa_upd_rec.selection_dt ,
X_STUDENT_CONFIRMED_IND => c_susa_upd_rec.student_confirmed_ind ,
X_END_DT => c_susa_upd_rec.end_dt ,
X_PARENT_UNIT_SET_CD => c_susa_upd_rec.parent_unit_set_cd,
X_PARENT_SEQUENCE_NUMBER => c_susa_upd_rec.parent_sequence_number ,
X_PRIMARY_SET_IND => c_susa_upd_rec.primary_set_ind ,
X_VOLUNTARY_END_IND => c_susa_upd_rec.voluntary_end_ind ,
X_AUTHORISED_PERSON_ID => c_susa_upd_rec.authorised_person_id,
X_AUTHORISED_ON => c_susa_upd_rec.authorised_on ,
X_OVERRIDE_TITLE => c_susa_upd_rec.override_title ,
X_RQRMNTS_COMPLETE_IND => 'Y' ,
-- The user entered parameter p_selection_date has been passed for the field X_RQRMNTS_COMPLETE_DT
-- In the UK Enh Build - Bug#2580731 - 04OCT2002.
-- In ENCR030(UK Enh) - Bug#2708430 - 16DEC2002. the X_RQRMNTS_COMPLETE_DT parameter below will be the parameter
-- p_completion_date or l_completion_date derived just above.
X_RQRMNTS_COMPLETE_DT => NVL(p_completion_date, TRUNC(l_completion_date)) ,
X_S_COMPLETED_SOURCE_TYPE => c_susa_upd_rec.s_completed_source_type,
X_CATALOG_CAL_TYPE => c_susa_upd_rec.catalog_cal_type ,
X_CATALOG_SEQ_NUM => c_susa_upd_rec.catalog_seq_num,
X_ATTRIBUTE_CATEGORY => c_susa_upd_rec.attribute_category,
X_ATTRIBUTE1 => c_susa_upd_rec.attribute1 ,
X_ATTRIBUTE2 => c_susa_upd_rec.attribute2 ,
X_ATTRIBUTE3 => c_susa_upd_rec.attribute3,
X_ATTRIBUTE4 => c_susa_upd_rec.attribute4,
X_ATTRIBUTE5 => c_susa_upd_rec.attribute5,
X_ATTRIBUTE6 => c_susa_upd_rec.attribute6,
X_ATTRIBUTE7 => c_susa_upd_rec.attribute7,
X_ATTRIBUTE8 => c_susa_upd_rec.attribute8,
X_ATTRIBUTE9 => c_susa_upd_rec.attribute9,
X_ATTRIBUTE10 => c_susa_upd_rec.attribute10,
X_ATTRIBUTE11 => c_susa_upd_rec.attribute11,
X_ATTRIBUTE12 => c_susa_upd_rec.attribute12,
X_ATTRIBUTE13 => c_susa_upd_rec.attribute13,
X_ATTRIBUTE14 => c_susa_upd_rec.attribute14,
X_ATTRIBUTE15 => c_susa_upd_rec.attribute15,
X_ATTRIBUTE16 => c_susa_upd_rec.attribute16,
X_ATTRIBUTE17 => c_susa_upd_rec.attribute17,
X_ATTRIBUTE18 => c_susa_upd_rec.attribute18,
X_ATTRIBUTE19 => c_susa_upd_rec.attribute19,
X_ATTRIBUTE20 => c_susa_upd_rec.attribute20,
X_MODE => 'R'
);
IF NOT update_stream_unit_sets(
p_person_id,
p_course_cd,
c_susa_upd_rec.unit_set_cd,
'Y',
NVL(p_completion_date, TRUNC(l_completion_date)),
c_susa_upd_rec.selection_dt,
c_susa_upd_rec.student_confirmed_ind,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
NVL(p_selection_date,SYSDATE),
'Y',
NULL,
NULL,
l_seqval,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
NVL(p_selection_date,TRUNC(SYSDATE)),
'Y',
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
OPEN c_prog_outcome(l_last_us.selection_dt) ;
NVL(p_selection_date,SYSDATE),
'Y',
NULL,
NULL,
l_seqval,
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
NVL(p_selection_date,TRUNC(SYSDATE)),
'Y',
p_log_creation_dt,
p_message_name
) THEN
RETURN FALSE;
SELECT 1
FROM IGS_AS_SU_STMPTOUT
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd AND
uoo_id = cp_uoo_id AND
outcome_dt = cp_discontinued_dt;
IGS_AS_SU_STMPTOUT_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_unit_cd => p_unit_cd,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_ci_sequence_number,
x_ci_start_dt => p_ci_start_dt,
x_ci_end_dt => p_ci_end_dt,
x_outcome_dt => p_discontinued_dt,
x_grading_schema_cd => gv_grading_schema_cd,
x_version_number => gv_version_number ,
x_grade => gv_grade,
x_s_grade_creation_method_type => 'DISCONTIN',
x_finalised_outcome_ind => 'Y',
x_mark => NULL,
X_number_times_keyed => NULL,
X_translated_grading_schema_cd => NULL,
X_translated_version_number => NULL,
X_translated_grade => NULL,
X_translated_dt => NULL,
X_mode => 'R',
x_org_id => l_org_id,
X_attribute_category => NULL,
X_attribute1 => NULL,
X_attribute2 => NULL,
X_attribute3 => NULL,
X_attribute4 => NULL,
X_attribute5 => NULL,
X_attribute6 => NULL,
X_attribute7 => NULL,
X_attribute8 => NULL,
X_attribute9 => NULL,
X_attribute10 => NULL,
X_attribute11 => NULL,
X_attribute12 => NULL,
X_attribute13 => NULL,
X_attribute14 => NULL,
X_attribute15 => NULL,
X_attribute16 => NULL,
X_attribute17 => NULL,
X_attribute18 => NULL,
X_attribute19 => NULL,
X_attribute20 => NULL,
x_uoo_id => p_uoo_id,
x_mark_capped_flag => 'N',
x_show_on_academic_histry_flag => 'Y',
x_release_date => NULL,
x_manual_override_flag => 'N',
x_incomp_deadline_date => NULL,
x_incomp_grading_schema_cd => NULL,
x_incomp_version_number => NULL,
x_incomp_default_grade => NULL,
x_incomp_default_mark => NULL,
x_comments => NULL,
x_grading_period_cd => 'FINAL'
);
p_new_update_who IN IGS_EN_SU_ATTEMPT_ALL.last_updated_by%TYPE ,
p_old_update_who IN IGS_EN_SU_ATTEMPT_ALL.last_updated_by%TYPE ,
p_new_update_on IN IGS_EN_SU_ATTEMPT_ALL.last_update_date%TYPE ,
p_old_update_on IN IGS_EN_SU_ATTEMPT_ALL.last_update_date%TYPE ,
p_new_dcnt_reason_Cd IN IGS_EN_SU_ATTEMPT_ALL.dcnt_reason_cd%TYPE,
p_old_dcnt_reason_Cd IN IGS_EN_SU_ATTEMPT_ALL.dcnt_reason_cd%TYPE,
p_uoo_id IN IGS_EN_SU_ATTEMPT_ALL.uoo_id%TYPE,
p_new_core_indicator_code IN IGS_EN_SU_ATTEMPT_ALL.core_indicator_code%TYPE, -- ptandon, Prevent Dropping Core Units build
p_old_core_indicator_code IN IGS_EN_SU_ATTEMPT_ALL.core_indicator_code%TYPE -- ptandon, Prevent Dropping Core Units build
)
AS
-------------------------------------------------------------------------------------------
--Change History:
--Who When What
--kkillams -04-2003 New parameters p_new_uoo_id and p_old_uoo_id to the function.
-- w.r.t. bug number 2829262
--ptandon 06-Oct-2003 New parameters p_new_core_indicator_code and p_old_core_indicator_code
-- added to the function as part of Prevent Dropping Core Units.
-- Enh Bug# 3052432.
-------------------------------------------------------------------------------------------
gv_other_detail VARCHAR2(255);
SELECT description
FROM IGS_AD_ADM_UNIT_STAT
WHERE administrative_unit_status = r_suah.administrative_unit_status;
SELECT description
FROM IGS_AD_LOCATION
WHERE location_cd = r_suah.exam_location_cd;
r_suah.hist_start_dt := p_old_update_on;
r_suah.hist_end_dt := p_new_update_on;
r_suah.hist_who := p_old_update_who;
IGS_EN_SU_ATTEMPT_H_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_person_id =>r_suah.person_id ,
x_course_cd => r_suah.course_cd,
x_unit_cd => r_suah.unit_cd,
x_version_number => r_suah.version_number,
x_cal_type => r_suah.cal_type,
x_ci_sequence_number => r_suah.ci_sequence_number,
x_hist_start_dt => r_suah.hist_start_dt,
x_hist_end_dt => r_suah.hist_end_dt,
x_hist_who => r_suah.hist_who,
x_location_cd => r_suah.location_cd,
x_unit_class => r_suah.unit_class,
x_enrolled_dt => r_suah.enrolled_dt,
x_unit_attempt_status => r_suah.unit_attempt_status,
x_administrative_unit_status => r_suah.administrative_unit_status,
x_aus_description => r_suah.aus_description,
x_discontinued_dt => r_suah.discontinued_dt,
x_rule_waived_dt => r_suah.rule_waived_dt ,
x_rule_waived_person_id => r_suah.rule_waived_person_id ,
x_no_assessment_ind => r_suah.no_assessment_ind,
x_exam_location_cd => r_suah.exam_location_cd,
x_elo_description => r_suah.elo_description,
x_sup_unit_cd => r_suah.sup_unit_cd,
x_sup_version_number => r_suah.sup_version_number,
x_alternative_title => r_suah.alternative_title,
x_override_enrolled_cp => r_suah.override_enrolled_cp,
x_override_eftsu => r_suah.override_eftsu,
x_override_achievable_cp => r_suah.override_achievable_cp,
x_override_outcome_due_dt => r_suah.override_outcome_due_dt,
x_override_credit_reason => r_suah.override_credit_reason,
x_dcnt_reason_Cd => r_suah.dcnt_reason_cd,
x_uoo_id => r_suah.uoo_id,
x_org_id => l_org_id, --, -- x_deg_aud_detail_id =>r_suah.deg_aud_detail_id
x_core_indicator_code => r_suah.core_indicator_code -- ptandon, Prevent Dropping Core Units build
);
SELECT cal_type
FROM igs_en_stdnt_ps_att
WHERE person_id = p_person_id AND
course_cd = p_dest_course_cd ;
SELECT start_dt
FROM igs_ca_inst
WHERE cal_type = cp_term_cal_type
AND sequence_number = cp_term_ci_seq_num;
SELECT 'X'
FROM igs_ps_unit_ofr_opt uoo,
igs_ca_teach_to_load_v tl
WHERE tl.teach_cal_type = uoo.cal_type
AND tl.teach_ci_sequence_number = uoo.ci_sequence_number
AND tl.load_cal_type = cp_load_cal_type
AND tl.load_ci_sequence_number = cp_load_ci_seq_num
AND uoo.uoo_id = cp_uoo_id;
SELECT cir.sub_cal_type term_cal_type,
cir.sub_ci_sequence_number term_sequence_number
FROM igs_ca_inst_rel cir,
igs_ca_inst ci,
igs_ca_type ct,
igs_ca_stat cs
WHERE cir.sup_cal_type = cp_acad_cal_type
AND ct.cal_type = cir.sub_cal_type
AND ct.s_cal_cat = 'LOAD'
AND ci.cal_type = cir.sub_cal_type
AND ci.sequence_number = cir.sub_ci_sequence_number
AND cs.cal_status = ci.cal_status
AND cs.s_cal_status = 'ACTIVE'
AND ci.start_dt > cp_curr_term_start_dt
ORDER BY ci.start_dt ASC;
Select src_ref.rowid, src_ref.*
From IGS_AS_SUA_REF_CDS src_ref
Where person_id = cp_person_id
And course_cd = cp_source_course_cd
And uoo_id = cp_uoo_id
And deleted_date is null
And not exists ( Select 'x'
From IGS_AS_SUA_REF_CDS dest_ref
WHERE dest_ref.person_id = src_ref.person_id
And dest_ref.course_cd = cp_dest_course_cd
And dest_ref.uoo_id = src_ref.uoo_id
And dest_ref.deleted_date is null
And dest_ref. REFERENCE_CODE_ID = src_ref. REFERENCE_CODE_ID
And dest_ref. APPLIED_COURSE_CD = src_ref. APPLIED_COURSE_CD
);
igs_as_sua_ref_cds_pkg.insert_row (
x_rowid => l_rowid,
x_suar_id => l_suarid,
x_person_id => vc_source_ref_cd.person_id,
x_course_cd => p_dest_course_cd,
x_uoo_id => vc_source_ref_cd.uoo_id,
x_reference_code_id => vc_source_ref_cd.reference_code_id,
x_reference_cd_type => vc_source_ref_cd.reference_cd_type,
x_reference_cd => vc_source_ref_cd.reference_cd,
x_applied_course_cd => vc_source_ref_cd.applied_course_cd,
x_deleted_date => vc_source_ref_cd.deleted_date,
x_mode => 'R' );
SELECT sua.person_id,
sua.course_cd,
sua.unit_cd,
sua.version_number,
sua.cal_type,
sua.ci_sequence_number,
sua.location_cd,
sua.unit_class,
sua.ci_start_dt,
sua.ci_end_dt,
sua.uoo_id,
sua.enrolled_dt,
sua.unit_attempt_status,
sua.administrative_unit_status,
sua.discontinued_dt,
sua.waitlist_dt,
sua.rule_waived_dt,
sua.rule_waived_person_id,
sua.no_assessment_ind,
sua.sup_unit_cd,
sua.sup_version_number,
sua.exam_location_cd,
sua.alternative_title,
sua.override_enrolled_cp,
sua.override_eftsu,
sua.override_achievable_cp,
sua.override_outcome_due_dt,
sua.override_credit_reason,
sua.org_unit_cd,
sua.grading_schema_code,
sua.gs_version_number,
sua.deg_aud_detail_id,
sua.student_career_transcript,
sua.student_career_statistics,
sua.administrative_priority,
sua.dcnt_reason_cd,
sua.session_id,
sua.attribute_category,
sua.attribute1,
sua.attribute2,
sua.attribute3,
sua.attribute4,
sua.attribute5,
sua.attribute6,
sua.attribute7,
sua.attribute8,
sua.attribute9,
sua.attribute10,
sua.attribute11,
sua.attribute12,
sua.attribute13,
sua.attribute14,
sua.attribute15,
sua.attribute16,
sua.attribute17,
sua.attribute18,
sua.attribute19,
sua.attribute20,
sua.waitlist_manual_ind,
sua.wlst_priority_weight_num,
sua.wlst_preference_weight_num,
sua.core_indicator_code
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_course_cd AND
sua.uoo_id = p_uoo_id;
CURSOR c_sua_delete IS
SELECT sua.rowid rowid1,
sua.unit_attempt_status unit_attempt_status
FROM IGS_EN_SU_ATTEMPT sua
WHERE sua.person_id = p_person_id AND
sua.course_cd = p_transfer_course_cd AND
sua.uoo_id = p_uoo_id
FOR UPDATE OF sua.unit_attempt_status NOWAIT;
SELECT ver.course_type
FROM IGS_PS_VER ver ,
igs_en_stdnt_ps_att spa
WHERE ver.course_cd = p_course_cd AND
ver.version_number = spa.version_number AND
spa.course_cd = p_course_cd AND
spa.person_id = p_person_id;
FOR v_uas IN c_sua_delete LOOP
-- If unit_attempt_status = 'UNCONFIRM' then delete the record
-- added dropped in the IF condition, to delete dropped unit records also, bug #2394594 by kkillams
-- removed cst_dropped and added ELSIF by ckasu as a part of bug #4080883 inorder to allow update of
-- unit attempt rather than deleting of unit attempt when unit attempt status is 'DROPPED'
IF v_uas.unit_attempt_status IN (cst_unconfirm) THEN
IGS_EN_SU_ATTEMPT_PKG.DELETE_ROW( v_uas.rowid1 );
IGS_EN_SU_ATTEMPT_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_PERSON_ID => p_person_id,
X_COURSE_CD => p_transfer_course_cd,
X_UNIT_CD => p_unit_cd,
X_VERSION_NUMBER => p_version_number,
X_CAL_TYPE => p_cal_type,
X_CI_SEQUENCE_NUMBER => p_ci_sequence_number,
X_LOCATION_CD => v_sua_rec.location_cd,
X_UNIT_CLASS => v_sua_rec.unit_class,
X_CI_START_DT => v_sua_rec.ci_start_dt,
X_CI_END_DT => v_sua_rec.ci_end_dt,
X_UOO_ID => v_sua_rec.uoo_id,
X_ENROLLED_DT => v_sua_rec.enrolled_dt,
X_ADMINISTRATIVE_UNIT_STATUS => v_sua_rec.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => v_sua_rec.administrative_priority,
X_unit_attempt_status => v_sua_rec.unit_attempt_status,
X_DISCONTINUED_DT => v_sua_rec.discontinued_dt,
X_RULE_WAIVED_DT => v_sua_rec.rule_waived_dt,
X_RULE_WAIVED_PERSON_ID => v_sua_rec.rule_waived_person_id,
X_NO_ASSESSMENT_IND => v_sua_rec.no_assessment_ind,
X_SUP_UNIT_CD => v_sua_rec.sup_unit_cd,
X_SUP_VERSION_NUMBER => v_sua_rec.sup_version_number,
X_EXAM_LOCATION_CD => v_sua_rec.exam_location_cd,
X_ALTERNATIVE_TITLE => v_sua_rec.alternative_title,
X_OVERRIDE_ENROLLED_CP => v_sua_rec.override_enrolled_cp,
X_OVERRIDE_EFTSU => v_sua_rec.override_eftsu,
X_OVERRIDE_ACHIEVABLE_CP => v_sua_rec.override_achievable_cp,
X_OVERRIDE_OUTCOME_DUE_DT => v_sua_rec.override_outcome_due_dt,
X_OVERRIDE_CREDIT_REASON => v_sua_rec.override_credit_reason,
X_WAITLIST_DT => v_sua_rec.waitlist_dt,
X_DCNT_REASON_CD => v_sua_rec.dcnt_reason_cd,
X_MODE => 'R',
X_ORG_ID => l_org_id,
X_ORG_UNIT_CD => v_sua_rec.org_unit_cd,
X_SESSION_ID => v_sua_rec.session_id, --This column has been added as per the Bug# 2172380.
-- Added the columns grading schema code and gs_version_number as a part of the bug 2037897. - aiyer
X_GRADING_SCHEMA_CODE => v_sua_rec.grading_schema_code,
X_GS_VERSION_NUMBER => v_sua_rec.gs_version_number,
-- Added the column deg_aud_detail_id as part of Degree Audit Interface build. (Bug# 2033208)
X_DEG_AUD_DETAIL_ID => v_sua_rec.deg_aud_detail_id,
-- These columns insert values depending on whether the course being transferred to belongs to
-- the same Career (course Type) or not .Bug # 2158626.
X_STUDENT_CAREER_TRANSCRIPT => v_sua_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => v_sua_rec.student_career_statistics ,
X_ATTRIBUTE_CATEGORY => v_sua_rec.attribute_category,
X_ATTRIBUTE1 => v_sua_rec.attribute1,
X_ATTRIBUTE2 => v_sua_rec.attribute2,
X_ATTRIBUTE3 => v_sua_rec.attribute3,
X_ATTRIBUTE4 => v_sua_rec.attribute4,
X_ATTRIBUTE5 => v_sua_rec.attribute5,
X_ATTRIBUTE6 => v_sua_rec.attribute6,
X_ATTRIBUTE7 => v_sua_rec.attribute7,
X_ATTRIBUTE8 => v_sua_rec.attribute8,
X_ATTRIBUTE9 => v_sua_rec.attribute9,
X_ATTRIBUTE10 => v_sua_rec.attribute10,
X_ATTRIBUTE11 => v_sua_rec.attribute11,
X_ATTRIBUTE12 => v_sua_rec.attribute12,
X_ATTRIBUTE13 => v_sua_rec.attribute13,
X_ATTRIBUTE14 => v_sua_rec.attribute14,
X_ATTRIBUTE15 => v_sua_rec.attribute15,
X_ATTRIBUTE16 => v_sua_rec.attribute16,
X_ATTRIBUTE17 => v_sua_rec.attribute17,
X_ATTRIBUTE18 => v_sua_rec.attribute18,
X_ATTRIBUTE19 => v_sua_rec.attribute19,
X_ATTRIBUTE20 => v_sua_rec.attribute20,
X_WAITLIST_MANUAL_IND => v_sua_rec.waitlist_manual_ind,
X_wlst_priority_weight_num => v_sua_rec.wlst_priority_weight_num,
X_wlst_preference_weight_num => v_sua_rec.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => v_sua_rec.core_indicator_code,
X_UPD_AUDIT_FLAG => 'N',
X_SS_SOURCE_IND => 'A');
p_delete_source => TRUE );
p_delete_source => TRUE);
IF (c_sua_delete%ISOPEN) THEN
CLOSE c_sua_delete;
p_new_selection_dt IN DATE ,
p_old_selection_dt IN DATE ,
p_new_student_confirmed_ind IN VARCHAR2 ,
p_old_student_confirmed_ind IN VARCHAR2 ,
p_new_end_dt IN DATE ,
p_old_end_dt IN DATE ,
p_new_parent_unit_set_cd IN VARCHAR2 ,
p_old_parent_unit_set_cd IN VARCHAR2 ,
p_new_parent_sequence_number IN NUMBER ,
p_old_parent_sequence_number IN NUMBER ,
p_new_primary_set_ind IN VARCHAR2 ,
p_old_primary_set_ind IN VARCHAR2 ,
p_new_voluntary_end_ind IN VARCHAR2 ,
p_old_voluntary_end_ind IN VARCHAR2,
p_new_authorised_person_id IN NUMBER ,
p_old_authorised_person_id IN NUMBER ,
p_new_authorised_on IN DATE ,
p_old_authorised_on IN DATE ,
p_new_override_title IN VARCHAR2 ,
p_old_override_title IN VARCHAR2 ,
p_new_rqrmnts_complete_ind IN VARCHAR2 ,
p_old_rqrmnts_complete_ind IN VARCHAR2 ,
p_new_rqrmnts_complete_dt IN DATE ,
p_old_rqrmnts_complete_dt IN DATE ,
p_new_s_completed_source_type IN VARCHAR2 ,
p_old_s_completed_source_type IN VARCHAR2 ,
p_new_catalog_cal_type IN VARCHAR2 ,
p_old_catalog_cal_type IN VARCHAR2 ,
p_new_catalog_seq_num IN NUMBER ,
p_old_catalog_seq_num IN NUMBER ,
p_new_update_who IN IGS_EN_SU_ATTEMPT_ALL.last_updated_by%TYPE ,
p_old_update_who IN IGS_EN_SU_ATTEMPT_ALL.last_updated_by%TYPE ,
p_new_update_on IN IGS_EN_SU_ATTEMPT_ALL.last_update_DATE%TYPE,
p_old_update_on IN IGS_EN_SU_ATTEMPT_ALL.last_update_DATE%TYPE )
AS
gv_other_detail VARCHAR2(255);
IF NVL(p_new_selection_dt, igs_ge_date.igsdate('1900/01/01')) <>
NVL(p_old_selection_dt,igs_ge_date.igsdate('1900/01/01')) THEN
v_susa_rec.selection_dt := p_old_selection_dt;
v_susa_rec.hist_start_dt := p_old_update_on;
v_susa_rec.hist_end_dt := p_new_update_on;
v_susa_rec.hist_who := p_old_update_who;
IGS_AS_SU_SETATMPT_H_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_person_id => v_susa_rec.person_id,
x_course_cd => v_susa_rec.course_cd,
x_unit_set_cd => v_susa_rec.unit_set_cd,
x_us_version_number => v_susa_rec.us_version_number,
x_sequence_number => v_susa_rec.sequence_number,
x_hist_start_dt => v_susa_rec.hist_start_dt,
x_hist_end_dt => v_susa_rec.hist_end_dt,
x_hist_who => v_susa_rec.hist_who,
x_selection_dt => v_susa_rec.selection_dt,
x_student_confirmed_ind =>v_susa_rec.student_confirmed_ind ,
x_end_dt => v_susa_rec.end_dt,
x_parent_unit_set_cd => v_susa_rec.parent_unit_set_cd,
x_parent_sequence_number => v_susa_rec.parent_sequence_number,
x_primary_set_ind => v_susa_rec.primary_set_ind,
x_voluntary_end_ind => v_susa_rec.voluntary_end_ind,
x_authorised_person_id => v_susa_rec.authorised_person_id,
x_authorised_on => v_susa_rec.authorised_on,
x_override_title => v_susa_rec.override_title,
x_rqrmnts_complete_ind => v_susa_rec.rqrmnts_complete_ind,
x_rqrmnts_complete_dt => v_susa_rec.rqrmnts_complete_dt,
x_s_completed_source_type => v_susa_rec.s_completed_source_type ,
x_catalog_cal_type => v_susa_rec.catalog_cal_type ,
x_catalog_seq_num => v_susa_rec.catalog_seq_num,
x_org_id => l_org_id);
SELECT susa.us_version_number,
susa.sequence_number,
susa.selection_dt,
susa.student_confirmed_ind,
susa.end_dt,
susa.parent_unit_set_cd,
susa.parent_sequence_number,
susa.voluntary_end_ind,
susa.authorised_person_id,
susa.authorised_on,
susa.override_title,
susa.rqrmnts_complete_ind,
susa.rqrmnts_complete_dt,
susa.s_completed_source_type
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_course_cd AND
susa.unit_set_cd = p_unit_set_cd AND
susa.us_version_number = p_us_version_number AND
susa.sequence_number = p_sequence_number;
SELECT 'x'
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = p_person_id AND
susa.course_cd = p_transfer_course_cd AND
susa.unit_set_cd = p_unit_set_cd AND
susa.us_version_number = p_us_version_number;
v_c_susa_rec.selection_dt,
v_c_susa_rec.student_confirmed_ind,
v_c_susa_rec.end_dt,
v_c_susa_rec.parent_unit_set_cd,
v_c_susa_rec.parent_sequence_number,
v_primary_set_ind,
v_c_susa_rec.voluntary_end_ind,
v_c_susa_rec.authorised_person_id,
v_c_susa_rec.authorised_on,
v_c_susa_rec.override_title,
v_c_susa_rec.rqrmnts_complete_ind,
v_c_susa_rec.rqrmnts_complete_dt,
v_c_susa_rec.s_completed_source_type,
'INSERT',
v_message_name,
v_message_text);
IGS_AS_SU_SETATMPT_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_person_id => p_person_id,
x_course_cd => p_transfer_course_cd,
x_unit_set_cd => p_unit_set_cd,
x_us_version_number => p_us_version_number,
x_sequence_number => v_c_susa_rec.sequence_number,
x_selection_dt => v_c_susa_rec.selection_dt,
x_end_dt => v_c_susa_rec.end_dt,
x_parent_unit_set_cd => v_c_susa_rec.parent_unit_set_cd,
x_parent_sequence_number => v_c_susa_rec.parent_sequence_number,
x_primary_set_ind => v_primary_set_ind,
x_voluntary_end_ind => v_c_susa_rec.voluntary_end_ind,
x_authorised_person_id => v_c_susa_rec.authorised_person_id,
x_authorised_on => v_c_susa_rec.authorised_on,
x_override_title => v_c_susa_rec.override_title,
x_rqrmnts_complete_ind => v_c_susa_rec.rqrmnts_complete_ind,
x_rqrmnts_complete_dt => v_c_susa_rec.rqrmnts_complete_dt,
x_s_completed_source_type => v_c_susa_rec.s_completed_source_type,
x_student_confirmed_ind => v_c_susa_rec.student_confirmed_ind,
X_CATALOG_CAL_TYPE => NULL,
X_CATALOG_SEQ_NUM => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_MODE => 'R');
IGS_PS_STDNT_UNT_TRN_PKG.INSERT_ROW(
x_rowid => l_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_transfer_course_cd =>p_transfer_course_cd ,
x_transfer_dt => p_transfer_dt,
x_unit_cd => p_unit_cd,
x_cal_type => p_cal_type,
x_ci_sequence_number => p_ci_sequence_number,
x_uoo_id => p_uoo_id);
SELECT exemption_institution_cd
FROM igs_av_adv_standing
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND version_number = cp_version_number;
SELECT avs.ROWID,
avs.*
FROM igs_av_adv_standing avs
WHERE avs.person_id = cp_person_id
AND avs.course_cd = cp_course_cd_new
AND avs.version_number = cp_version_number_new
AND avs.exemption_institution_cd = cp_exemption_institution_cd;
SELECT asu.ROWID,
asu.*
FROM igs_av_stnd_unit_all asu
WHERE asu.person_id = cp_person_id
AND asu.as_course_cd = cp_course_cd
AND asu.as_version_number = cp_version_number
AND asu.exemption_institution_cd = cp_exemption_institution_cd
AND asu.s_adv_stnd_granting_status IN ('APPROVED', 'GRANTED');
SELECT asu.ROWID,
asu.*
FROM igs_av_stnd_unit_all asu
WHERE asu.person_id = cp_person_id
AND asu.as_course_cd = cp_as_course_cd
AND asu.as_version_number = cp_as_version_number
AND asu.s_adv_stnd_type = cp_s_adv_stnd_type
AND asu.unit_cd = cp_unit_cd
AND asu.version_number = cp_version_number
AND asu.exemption_institution_cd = cp_exemption_institution_cd
AND (asu.unit_details_id = cp_unit_details_id
OR asu.tst_rslt_dtls_id = cp_tst_rslt_dtls_id);
SELECT *
FROM igs_av_std_unt_basis asub
WHERE asub.av_stnd_unit_id = cp_av_stnd_unit_id;
SELECT *
FROM igs_av_stnd_alt_unit asau
WHERE asau.av_stnd_unit_id = cp_av_stnd_unit_id;
SELECT asule.ROWID,
asule.*
FROM igs_av_stnd_unit_lvl_all asule
WHERE asule.person_id = cp_person_id
AND asule.as_course_cd = cp_course_cd
AND asule.as_version_number = cp_version_number
AND asule.exemption_institution_cd = cp_exemption_institution_cd
AND asule.s_adv_stnd_granting_status IN ('APPROVED', 'GRANTED');
SELECT asule.ROWID,
asule.*
FROM igs_av_stnd_unit_lvl_all asule
WHERE asule.person_id = cp_person_id
AND asule.as_course_cd = cp_as_course_cd
AND asule.as_version_number = cp_as_version_number
AND asule.s_adv_stnd_type = cp_s_adv_stnd_type
AND asule.unit_level = cp_unit_level
AND asule.crs_group_ind = cp_crs_group_ind
AND asule.exemption_institution_cd = cp_exemption_institution_cd
AND (asule.unit_details_id = cp_unit_details_id
OR asule.tst_rslt_dtls_id = cp_tst_rslt_dtls_id
OR asule.qual_dets_id = cp_qual_dets_id);
SELECT *
FROM igs_av_std_ulvlbasis asuleb
WHERE asuleb.av_stnd_unit_lvl_id = cp_av_stnd_unit_lvl_id;
igs_av_adv_standing_pkg.insert_row (
x_rowid => l_rowid1,
x_person_id => p_person_id,
x_course_cd => p_course_cd_new,
x_version_number => p_version_number_new,
x_total_exmptn_approved => 0,
x_total_exmptn_granted => 0,
x_total_exmptn_perc_grntd => 0,
x_exemption_institution_cd => rec_adv_stnd.exemption_institution_cd,
x_mode => 'R',
x_org_id => l_org_id
);
g_module_head || 'adv_stand_trans.igs_av_adv_standing_pkg_insert_exception',
'Error:' || SQLERRM
);
igs_av_stnd_unit_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid2,
x_person_id => p_person_id,
x_as_course_cd => p_course_cd_new,
x_as_version_number => p_version_number_new,
x_s_adv_stnd_type => rec_unit_dtls.s_adv_stnd_type,
x_unit_cd => rec_unit_dtls.unit_cd,
x_version_number => rec_unit_dtls.version_number,
x_s_adv_stnd_granting_status => rec_unit_dtls.s_adv_stnd_granting_status,
x_credit_percentage => NULL,
x_s_adv_stnd_recognition_type => rec_unit_dtls.s_adv_stnd_recognition_type,
x_approved_dt => rec_unit_dtls.approved_dt,
x_authorising_person_id => rec_unit_dtls.authorising_person_id,
x_crs_group_ind => rec_unit_dtls.crs_group_ind,
x_exemption_institution_cd => rec_unit_dtls.exemption_institution_cd,
x_granted_dt => rec_unit_dtls.granted_dt,
x_expiry_dt => rec_unit_dtls.expiry_dt,
x_cancelled_dt => rec_unit_dtls.cancelled_dt,
x_revoked_dt => rec_unit_dtls.revoked_dt,
x_comments => rec_unit_dtls.comments,
x_av_stnd_unit_id => rec_unit_dtls.av_stnd_unit_id,
x_cal_type => rec_unit_dtls.cal_type,
x_ci_sequence_number => rec_unit_dtls.ci_sequence_number,
x_institution_cd => rec_unit_dtls.institution_cd,
x_unit_details_id => rec_unit_dtls.unit_details_id,
x_tst_rslt_dtls_id => rec_unit_dtls.tst_rslt_dtls_id,
x_grading_schema_cd => rec_unit_dtls.grading_schema_cd,
x_grd_sch_version_number => rec_unit_dtls.grd_sch_version_number,
x_grade => rec_unit_dtls.grade,
x_achievable_credit_points => rec_unit_dtls.achievable_credit_points,
x_org_id => l_org_id,
x_adv_stnd_trans => 'Y'
);
g_module_head || 'adv_stand_trans.igs_av_stnd_unit_pkg_insert_exception',
'Error:' || SQLERRM
);
igs_av_std_unt_basis_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid3,
x_av_stnd_unit_id => rec_unit_dtls.av_stnd_unit_id,
x_basis_course_type => rec_unit_bas_dtls.basis_course_type,
x_basis_year => rec_unit_bas_dtls.basis_year,
x_basis_completion_ind => rec_unit_bas_dtls.basis_completion_ind,
x_org_id => l_org_id
);
g_module_head || 'adv_stand_trans.igs_av_std_unt_basis_pkg_insert_exception',
'Error:' || SQLERRM
);
igs_av_stnd_alt_unit_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid4,
x_av_stnd_unit_id => rec_unit_dtls.av_stnd_unit_id,
x_alt_unit_cd => rec_alt_unit_dtls.alt_unit_cd,
x_alt_version_number => rec_alt_unit_dtls.alt_version_number,
x_optional_ind => rec_alt_unit_dtls.optional_ind
);
g_module_head || 'adv_stand_trans.igs_av_stnd_alt_unit_pkg_insert_exception',
'Error:' || SQLERRM
);
igs_av_stnd_unit_pkg.update_row (
x_mode => 'R',
x_rowid => rec_dest_unit_dtls.ROWID,
x_person_id => rec_dest_unit_dtls.person_id,
x_as_course_cd => rec_dest_unit_dtls.as_course_cd,
x_as_version_number => rec_dest_unit_dtls.as_version_number,
x_s_adv_stnd_type => rec_dest_unit_dtls.s_adv_stnd_type,
x_unit_cd => rec_dest_unit_dtls.unit_cd,
x_version_number => rec_dest_unit_dtls.version_number,
x_s_adv_stnd_granting_status => rec_unit_dtls.s_adv_stnd_granting_status,
x_credit_percentage => NULL,
x_s_adv_stnd_recognition_type => rec_dest_unit_dtls.s_adv_stnd_recognition_type,
x_approved_dt => rec_dest_unit_dtls.approved_dt,
x_authorising_person_id => rec_dest_unit_dtls.authorising_person_id,
x_crs_group_ind => rec_dest_unit_dtls.crs_group_ind,
x_exemption_institution_cd => rec_dest_unit_dtls.exemption_institution_cd,
x_granted_dt => rec_dest_unit_dtls.granted_dt,
x_expiry_dt => rec_dest_unit_dtls.expiry_dt,
x_cancelled_dt => rec_dest_unit_dtls.cancelled_dt,
x_revoked_dt => rec_dest_unit_dtls.revoked_dt,
x_comments => rec_dest_unit_dtls.comments,
x_av_stnd_unit_id => rec_dest_unit_dtls.av_stnd_unit_id,
x_cal_type => rec_dest_unit_dtls.cal_type,
x_ci_sequence_number => rec_dest_unit_dtls.ci_sequence_number,
x_institution_cd => rec_dest_unit_dtls.institution_cd,
x_unit_details_id => rec_dest_unit_dtls.unit_details_id,
x_tst_rslt_dtls_id => rec_dest_unit_dtls.tst_rslt_dtls_id,
x_grading_schema_cd => rec_dest_unit_dtls.grading_schema_cd,
x_grd_sch_version_number => rec_dest_unit_dtls.grd_sch_version_number,
x_grade => rec_dest_unit_dtls.grade,
x_achievable_credit_points => rec_dest_unit_dtls.achievable_credit_points
);
g_module_head || 'adv_stand_trans.igs_av_stnd_unit_pkg_dest_update_exception',
'Error:' || SQLERRM
);
igs_av_stnd_unit_pkg.update_row (
x_mode => 'R',
x_rowid => rec_unit_dtls.ROWID,
x_person_id => rec_unit_dtls.person_id,
x_as_course_cd => rec_unit_dtls.as_course_cd,
x_as_version_number => rec_unit_dtls.as_version_number,
x_s_adv_stnd_type => rec_unit_dtls.s_adv_stnd_type,
x_unit_cd => rec_unit_dtls.unit_cd,
x_version_number => rec_unit_dtls.version_number,
x_s_adv_stnd_granting_status => 'TRANSFERRED',
x_credit_percentage => NULL,
x_s_adv_stnd_recognition_type => rec_unit_dtls.s_adv_stnd_recognition_type,
x_approved_dt => rec_unit_dtls.approved_dt,
x_authorising_person_id => rec_unit_dtls.authorising_person_id,
x_crs_group_ind => rec_unit_dtls.crs_group_ind,
x_exemption_institution_cd => rec_unit_dtls.exemption_institution_cd,
x_granted_dt => rec_unit_dtls.granted_dt,
x_expiry_dt => rec_unit_dtls.expiry_dt,
x_cancelled_dt => rec_unit_dtls.cancelled_dt,
x_revoked_dt => rec_unit_dtls.revoked_dt,
x_comments => rec_unit_dtls.comments,
x_av_stnd_unit_id => rec_unit_dtls.av_stnd_unit_id,
x_cal_type => rec_unit_dtls.cal_type,
x_ci_sequence_number => rec_unit_dtls.ci_sequence_number,
x_institution_cd => rec_unit_dtls.institution_cd,
x_unit_details_id => rec_unit_dtls.unit_details_id,
x_tst_rslt_dtls_id => rec_unit_dtls.tst_rslt_dtls_id,
x_grading_schema_cd => rec_unit_dtls.grading_schema_cd,
x_grd_sch_version_number => rec_unit_dtls.grd_sch_version_number,
x_grade => rec_unit_dtls.grade,
x_achievable_credit_points => rec_unit_dtls.achievable_credit_points
);
g_module_head || 'adv_stand_trans.igs_av_stnd_unit_pkg_src_update_exception',
'Error:' || SQLERRM
);
igs_av_stnd_unit_lvl_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid5,
x_person_id => p_person_id,
x_as_course_cd => p_course_cd_new,
x_as_version_number => p_version_number_new,
x_s_adv_stnd_type => rec_unit_lvl_dtls.s_adv_stnd_type,
x_unit_level => rec_unit_lvl_dtls.unit_level,
x_crs_group_ind => rec_unit_lvl_dtls.crs_group_ind,
x_exemption_institution_cd => rec_unit_lvl_dtls.exemption_institution_cd,
x_s_adv_stnd_granting_status => rec_unit_lvl_dtls.s_adv_stnd_granting_status,
x_credit_points => rec_unit_lvl_dtls.credit_points,
x_approved_dt => rec_unit_lvl_dtls.approved_dt,
x_authorising_person_id => rec_unit_lvl_dtls.authorising_person_id,
x_granted_dt => rec_unit_lvl_dtls.granted_dt,
x_expiry_dt => rec_unit_lvl_dtls.expiry_dt,
x_cancelled_dt => rec_unit_lvl_dtls.cancelled_dt,
x_revoked_dt => rec_unit_lvl_dtls.revoked_dt,
x_comments => rec_unit_lvl_dtls.comments,
x_av_stnd_unit_lvl_id => rec_unit_lvl_dtls.av_stnd_unit_lvl_id,
x_cal_type => rec_unit_lvl_dtls.cal_type,
x_ci_sequence_number => rec_unit_lvl_dtls.ci_sequence_number,
x_institution_cd => rec_unit_lvl_dtls.institution_cd,
x_unit_details_id => rec_unit_lvl_dtls.unit_details_id,
x_tst_rslt_dtls_id => rec_unit_lvl_dtls.tst_rslt_dtls_id,
x_org_id => l_org_id,
x_adv_stnd_trans => 'Y',
x_qual_dets_id => rec_unit_lvl_dtls.qual_dets_id
);
g_module_head || 'adv_stand_trans.igs_av_stnd_unit_lvl_pkg_insert_exception',
'Error:' || SQLERRM
);
igs_av_std_ulvlbasis_pkg.insert_row (
x_mode => 'R',
x_rowid => l_rowid6,
x_av_stnd_unit_lvl_id => rec_unit_lvl_dtls.av_stnd_unit_lvl_id,
x_basis_course_type => rec_unit_lvl_bas_dtls.basis_course_type,
x_basis_year => rec_unit_lvl_bas_dtls.basis_year,
x_basis_completion_ind => rec_unit_lvl_bas_dtls.basis_completion_ind,
x_org_id => l_org_id
);
g_module_head || 'adv_stand_trans.igs_av_stnd_unit_lvl_pkg_insert_exception',
'Error:' || SQLERRM
);
igs_av_stnd_unit_lvl_pkg.update_row (
x_mode => 'R',
x_rowid => rec_dest_unit_lvl_dtls.ROWID,
x_person_id => rec_dest_unit_lvl_dtls.person_id,
x_as_course_cd => rec_dest_unit_lvl_dtls.as_course_cd,
x_as_version_number => rec_dest_unit_lvl_dtls.as_version_number,
x_s_adv_stnd_type => rec_dest_unit_lvl_dtls.s_adv_stnd_type,
x_unit_level => rec_dest_unit_lvl_dtls.unit_level,
x_crs_group_ind => rec_dest_unit_lvl_dtls.crs_group_ind,
x_exemption_institution_cd => rec_dest_unit_lvl_dtls.exemption_institution_cd,
x_s_adv_stnd_granting_status => rec_unit_lvl_dtls.s_adv_stnd_granting_status,
x_credit_points => rec_dest_unit_lvl_dtls.credit_points,
x_approved_dt => rec_dest_unit_lvl_dtls.approved_dt,
x_authorising_person_id => rec_dest_unit_lvl_dtls.authorising_person_id,
x_granted_dt => rec_dest_unit_lvl_dtls.granted_dt,
x_expiry_dt => rec_dest_unit_lvl_dtls.expiry_dt,
x_cancelled_dt => rec_dest_unit_lvl_dtls.cancelled_dt,
x_revoked_dt => rec_dest_unit_lvl_dtls.revoked_dt,
x_comments => rec_dest_unit_lvl_dtls.comments,
x_av_stnd_unit_lvl_id => rec_dest_unit_lvl_dtls.av_stnd_unit_lvl_id,
x_cal_type => rec_dest_unit_lvl_dtls.cal_type,
x_ci_sequence_number => rec_dest_unit_lvl_dtls.ci_sequence_number,
x_institution_cd => rec_dest_unit_lvl_dtls.institution_cd,
x_unit_details_id => rec_dest_unit_lvl_dtls.unit_details_id,
x_tst_rslt_dtls_id => rec_dest_unit_lvl_dtls.tst_rslt_dtls_id,
x_qual_dets_id => rec_dest_unit_lvl_dtls.qual_dets_id
);
g_module_head || 'adv_stand_trans.igs_av_stnd_unit_lvl_pkg_dest_update_exception',
'Error:' || SQLERRM
);
igs_av_stnd_unit_lvl_pkg.update_row (
x_mode => 'R',
x_rowid => rec_unit_lvl_dtls.ROWID,
x_person_id => rec_unit_lvl_dtls.person_id,
x_as_course_cd => rec_unit_lvl_dtls.as_course_cd,
x_as_version_number => rec_unit_lvl_dtls.as_version_number,
x_s_adv_stnd_type => rec_unit_lvl_dtls.s_adv_stnd_type,
x_unit_level => rec_unit_lvl_dtls.unit_level,
x_crs_group_ind => rec_unit_lvl_dtls.crs_group_ind,
x_exemption_institution_cd => rec_unit_lvl_dtls.exemption_institution_cd,
x_s_adv_stnd_granting_status => 'TRANSFERRED',
x_credit_points => rec_unit_lvl_dtls.credit_points,
x_approved_dt => rec_unit_lvl_dtls.approved_dt,
x_authorising_person_id => rec_unit_lvl_dtls.authorising_person_id,
x_granted_dt => rec_unit_lvl_dtls.granted_dt,
x_expiry_dt => rec_unit_lvl_dtls.expiry_dt,
x_cancelled_dt => rec_unit_lvl_dtls.cancelled_dt,
x_revoked_dt => rec_unit_lvl_dtls.revoked_dt,
x_comments => rec_unit_lvl_dtls.comments,
x_av_stnd_unit_lvl_id => rec_unit_lvl_dtls.av_stnd_unit_lvl_id,
x_cal_type => rec_unit_lvl_dtls.cal_type,
x_ci_sequence_number => rec_unit_lvl_dtls.ci_sequence_number,
x_institution_cd => rec_unit_lvl_dtls.institution_cd,
x_unit_details_id => rec_unit_lvl_dtls.unit_details_id,
x_tst_rslt_dtls_id => rec_unit_lvl_dtls.tst_rslt_dtls_id,
x_qual_dets_id => rec_unit_lvl_dtls.qual_dets_id
);
g_module_head || 'adv_stand_trans.igs_av_stnd_unit_lvl_pkg_src_update_exception',
'Error:' || SQLERRM
);
p_selection_dt IN DATE,
p_confirmed_ind IN VARCHAR2,
p_log_creation_dt IN DATE,
p_message_name OUT NOCOPY VARCHAR2
) RETURN BOOLEAN AS
------------------------------------------------------------------------------------------------
--Change History:
--Who When What
--bdeviset 29-JUL-2004 Added p_log_creation_dt as parameter.Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW
-- a check is made to see that their is no overlapping of selection,completion and
-- end dates for any two unit sets by calling check_usa_overlap.If it returns
-- false log entry is made and the insert or update is not carried out for bug 3149133.
------------------------------------------------------------------------------------------------
CURSOR c_acad_us (cp_new_admin_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE) IS
SELECT usm.stream_unit_set_Cd
FROM igs_en_unit_set_map usm,
igs_ps_us_prenr_cfg upc
WHERE upc.unit_set_cd = cp_new_admin_unit_set_cd
AND usm.mapping_set_cd = upc.mapping_set_cd
AND usm.sequence_no = upc.sequence_no;
SELECT coous.us_version_number
FROM igs_en_unit_set_stat uss, igs_ps_ofr_opt_unit_set_v coous, igs_en_stdnt_ps_att sca
WHERE sca.person_id = cp_person_id AND
sca.course_cd = cp_course_cd AND
sca.coo_id = coous.coo_id AND
coous.unit_set_cd = cp_unit_set_cd AND
coous.expiry_dt IS NULL AND
coous.unit_set_status = uss.unit_set_status AND
uss.s_unit_set_status = 'ACTIVE' ;
SELECT susa.*, susa.rowid
FROM igs_as_su_setatmpt susa
WHERE susa.unit_set_cd = cp_stream_unit_set_cd
AND susa.us_version_number = cp_us_version_number
AND susa.person_id = cp_person_id
AND susa.course_cd = cp_course_cd
AND susa.end_dt IS NULL
ORDER BY susa.selection_dt desc;
v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
v_selection_dt := NVL(p_selection_dt,SYSDATE) ;
v_selection_dt := NULL;
SELECT IGS_AS_SU_SETATMPT_SEQ_NUM_S.NEXTVAL INTO vl_sequence_val FROM dual;
TRUNC(v_selection_dt),
NULL,
NULL,
vl_sequence_val,
vc_acad_us_rec.stream_unit_set_cd,
v_us_version_number,
p_message_name) = FALSE THEN
p_warn_level := cst_error;
IGS_AS_SU_SETATMPT_PKG.INSERT_ROW (
X_ROWID => vl_rowid,
X_PERSON_ID => p_person_id ,
X_COURSE_CD => p_course_cd ,
X_UNIT_SET_CD => vc_acad_us_rec.stream_unit_set_cd ,
X_SEQUENCE_NUMBER => vl_sequence_val ,
X_US_VERSION_NUMBER => v_us_version_number,
X_SELECTION_DT => TRUNC(v_selection_dt) ,
X_STUDENT_CONFIRMED_IND => v_confirmed_ind ,
X_END_DT => NULL ,
X_PARENT_UNIT_SET_CD => NULL,
X_PARENT_SEQUENCE_NUMBER => NULL ,
X_PRIMARY_SET_IND => NULL ,
X_VOLUNTARY_END_IND => NULL ,
X_AUTHORISED_PERSON_ID => NULL ,
X_AUTHORISED_ON => NULL ,
X_OVERRIDE_TITLE => NULL ,
X_RQRMNTS_COMPLETE_IND => NULL ,
X_RQRMNTS_COMPLETE_DT => NULL,
X_S_COMPLETED_SOURCE_TYPE => NULL,
X_CATALOG_CAL_TYPE => NULL ,
X_CATALOG_SEQ_NUM => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_MODE => 'R'
);
v_selection_dt := NULL;
IF v_selection_dt IS NOT NULL AND vc_susa_upd_rec.selection_dt < v_selection_dt THEN
v_selection_dt := vc_susa_upd_rec.selection_dt;
v_selection_dt := NVL(v_selection_dt,vc_susa_upd_rec.selection_dt);
IF NVL(v_selection_dt,igs_ge_date.igsdate('1000/01/01 00:00:00') )
<> NVL(vc_susa_upd_rec.selection_dt,igs_ge_date.igsdate('1000/01/01 00:00:00'))
OR v_confirmed_ind <> vc_susa_upd_rec.student_confirmed_ind
THEN
IF igs_en_gen_legacy.check_usa_overlap(
vc_susa_upd_rec.person_id,
vc_susa_upd_rec.course_cd,
TRUNC(v_selection_dt),
vc_susa_upd_rec.rqrmnts_complete_dt,
vc_susa_upd_rec.end_dt,
vc_susa_upd_rec.sequence_number,
vc_susa_upd_rec.unit_set_cd,
vc_susa_upd_rec.us_version_number,
p_message_name) = FALSE THEN
p_warn_level := cst_error;
IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
X_ROWID => vc_susa_upd_rec.rowid,
X_PERSON_ID => vc_susa_upd_rec.person_id ,
X_COURSE_CD => vc_susa_upd_rec.course_cd ,
X_UNIT_SET_CD => vc_susa_upd_rec.unit_set_cd ,
X_SEQUENCE_NUMBER => vc_susa_upd_rec.sequence_number ,
X_US_VERSION_NUMBER => vc_susa_upd_rec.us_version_number,
X_SELECTION_DT => TRUNC(v_selection_dt) ,
X_STUDENT_CONFIRMED_IND => v_confirmed_ind,
X_END_DT => vc_susa_upd_rec.end_dt ,
X_PARENT_UNIT_SET_CD => vc_susa_upd_rec.parent_unit_set_cd,
X_PARENT_SEQUENCE_NUMBER => vc_susa_upd_rec.parent_sequence_number ,
X_PRIMARY_SET_IND => vc_susa_upd_rec.primary_set_ind ,
X_VOLUNTARY_END_IND => vc_susa_upd_rec.voluntary_end_ind ,
X_AUTHORISED_PERSON_ID => vc_susa_upd_rec.authorised_person_id,
X_AUTHORISED_ON => vc_susa_upd_rec.authorised_on ,
X_OVERRIDE_TITLE => vc_susa_upd_rec.override_title ,
X_RQRMNTS_COMPLETE_IND => vc_susa_upd_rec.rqrmnts_complete_ind,
X_RQRMNTS_COMPLETE_DT => vc_susa_upd_rec.rqrmnts_complete_dt,
X_S_COMPLETED_SOURCE_TYPE => vc_susa_upd_rec.s_completed_source_type,
X_CATALOG_CAL_TYPE => vc_susa_upd_rec.catalog_cal_type ,
X_CATALOG_SEQ_NUM => vc_susa_upd_rec.catalog_seq_num,
X_ATTRIBUTE_CATEGORY => vc_susa_upd_rec.attribute_category,
X_ATTRIBUTE1 => vc_susa_upd_rec.attribute1 ,
X_ATTRIBUTE2 => vc_susa_upd_rec.attribute2 ,
X_ATTRIBUTE3 => vc_susa_upd_rec.attribute3,
X_ATTRIBUTE4 => vc_susa_upd_rec.attribute4,
X_ATTRIBUTE5 => vc_susa_upd_rec.attribute5,
X_ATTRIBUTE6 => vc_susa_upd_rec.attribute6,
X_ATTRIBUTE7 => vc_susa_upd_rec.attribute7,
X_ATTRIBUTE8 => vc_susa_upd_rec.attribute8,
X_ATTRIBUTE9 => vc_susa_upd_rec.attribute9,
X_ATTRIBUTE10 => vc_susa_upd_rec.attribute10,
X_ATTRIBUTE11 => vc_susa_upd_rec.attribute11,
X_ATTRIBUTE12 => vc_susa_upd_rec.attribute12,
X_ATTRIBUTE13 => vc_susa_upd_rec.attribute13,
X_ATTRIBUTE14 => vc_susa_upd_rec.attribute14,
X_ATTRIBUTE15 => vc_susa_upd_rec.attribute15,
X_ATTRIBUTE16 => vc_susa_upd_rec.attribute16,
X_ATTRIBUTE17 => vc_susa_upd_rec.attribute17,
X_ATTRIBUTE18 => vc_susa_upd_rec.attribute18,
X_ATTRIBUTE19 => vc_susa_upd_rec.attribute19,
X_ATTRIBUTE20 => vc_susa_upd_rec.attribute20,
X_MODE => 'R'
);
p_selection_dt IN DATE,
p_confirmed_ind IN VARCHAR2,
p_authorised_person_id IN NUMBER,
p_authorised_on IN DATE,
p_seqval OUT NOCOPY NUMBER,
p_log_creation_dt IN DATE,
p_message_name OUT NOCOPY VARCHAR2
) RETURN BOOLEAN AS
------------------------------------------------------------------------------------------------
--Change History:
--Who When What
--bdeviset 29-JUL-2004 Added p_log_creation_dt as parameter.Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW
-- a check is made to see that their is no overlapping of selection,completion and
-- end dates for any two unit sets by calling check_usa_overlap.If it returns
-- false log entry is made and the insert or update is not carried out for bug 3149133.
------------------------------------------------------------------------------------------------
p_warn_level varchar2(5);
v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
SELECT susa.*,susa.rowid
FROM IGS_AS_SU_SETATMPT susa
WHERE susa.person_id = cp_person_id AND
susa.course_cd = cp_course_cd AND
susa.unit_set_cd = cp_unit_set_cd AND
susa.us_version_number = cp_us_version_number AND
susa.end_dt IS NULL;
v_Selection_Dt := null;
IF p_selection_dt IS NOT NULL AND v_susa_rec.selection_dt < p_selection_dt THEN
v_selection_dt := v_susa_rec.selection_dt;
v_selection_Dt := p_selection_Dt;
v_selection_dt := NVL(NVL(v_selection_dt,v_susa_rec.selection_dt),sysdate);
IF NVL(v_selection_dt,igs_ge_date.igsdate('1000/01/01 00:00:00'))
<> NVL(v_susa_rec.selection_dt,igs_ge_date.igsdate('1000/01/01 00:00:00'))
OR v_confirmed_ind <> v_susa_rec.student_confirmed_ind
THEN
IF igs_en_gen_legacy.check_usa_overlap(
v_susa_rec.person_id,
v_susa_rec.course_cd,
TRUNC(v_selection_dt),
v_susa_rec.rqrmnts_complete_dt,
v_susa_rec.end_dt,
v_susa_rec.sequence_number,
v_susa_rec.unit_set_cd ,
v_susa_rec.us_version_number,
p_message_name) = FALSE THEN
p_warn_level := cst_error;
IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
X_ROWID => v_susa_rec.rowid,
X_PERSON_ID => v_susa_rec.person_id ,
X_COURSE_CD => v_susa_rec.course_cd ,
X_UNIT_SET_CD => v_susa_rec.unit_set_cd ,
X_SEQUENCE_NUMBER => v_susa_rec.sequence_number ,
X_US_VERSION_NUMBER => v_susa_rec.us_version_number,
X_SELECTION_DT => TRUNC(v_selection_dt),
X_STUDENT_CONFIRMED_IND => v_confirmed_ind,
X_END_DT => v_susa_rec.end_dt ,
X_PARENT_UNIT_SET_CD => v_susa_rec.parent_unit_set_cd,
X_PARENT_SEQUENCE_NUMBER => v_susa_rec.parent_sequence_number ,
X_PRIMARY_SET_IND => v_susa_rec.primary_set_ind ,
X_VOLUNTARY_END_IND => v_susa_rec.voluntary_end_ind ,
X_AUTHORISED_PERSON_ID => NVL(p_authorised_person_id,v_susa_rec.authorised_person_id),
X_AUTHORISED_ON => NVL(p_authorised_on,v_susa_rec.authorised_on),
X_OVERRIDE_TITLE => v_susa_rec.override_title ,
X_RQRMNTS_COMPLETE_IND => v_susa_rec.rqrmnts_complete_ind,
X_RQRMNTS_COMPLETE_DT => v_susa_rec.rqrmnts_complete_dt,
X_S_COMPLETED_SOURCE_TYPE => v_susa_rec.s_completed_source_type,
X_CATALOG_CAL_TYPE => v_susa_rec.catalog_cal_type ,
X_CATALOG_SEQ_NUM => v_susa_rec.catalog_seq_num,
X_ATTRIBUTE_CATEGORY => v_susa_rec.attribute_category,
X_ATTRIBUTE1 => v_susa_rec.attribute1 ,
X_ATTRIBUTE2 => v_susa_rec.attribute2 ,
X_ATTRIBUTE3 => v_susa_rec.attribute3,
X_ATTRIBUTE4 => v_susa_rec.attribute4,
X_ATTRIBUTE5 => v_susa_rec.attribute5,
X_ATTRIBUTE6 => v_susa_rec.attribute6,
X_ATTRIBUTE7 => v_susa_rec.attribute7,
X_ATTRIBUTE8 => v_susa_rec.attribute8,
X_ATTRIBUTE9 => v_susa_rec.attribute9,
X_ATTRIBUTE10 => v_susa_rec.attribute10,
X_ATTRIBUTE11 => v_susa_rec.attribute11,
X_ATTRIBUTE12 => v_susa_rec.attribute12,
X_ATTRIBUTE13 => v_susa_rec.attribute13,
X_ATTRIBUTE14 => v_susa_rec.attribute14,
X_ATTRIBUTE15 => v_susa_rec.attribute15,
X_ATTRIBUTE16 => v_susa_rec.attribute16,
X_ATTRIBUTE17 => v_susa_rec.attribute17,
X_ATTRIBUTE18 => v_susa_rec.attribute18,
X_ATTRIBUTE19 => v_susa_rec.attribute19,
X_ATTRIBUTE20 => v_susa_rec.attribute20,
X_MODE => 'R'
);
SELECT IGS_AS_SU_SETATMPT_SEQ_NUM_S.NEXTVAL
INTO l_seqval
FROM dual;
v_selection_dt := NVL(p_selection_dt,sysdate);
v_selection_dt := NULL;
TRUNC(v_selection_dt),
NULL,
NULL,
l_seqval,
p_unit_set_cd,
p_us_version_number,
p_message_name) = FALSE THEN
p_warn_level := cst_error;
IGS_AS_SU_SETATMPT_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_person_id => p_person_id,
x_course_cd => p_course_cd,
x_unit_set_cd => p_unit_set_cd,
x_sequence_number => l_seqval,
x_us_version_number => p_us_version_number,
x_selection_dt => TRUNC(v_selection_dt ),
x_student_confirmed_ind =>v_confirmed_ind,
x_end_dt => NULL,
x_PARENT_UNIT_SET_CD => NULL,
X_PARENT_SEQUENCE_NUMBER => NULL,
X_PRIMARY_SET_IND => NULL,
X_VOLUNTARY_END_IND => NULL,
x_authorised_person_id => p_authorised_person_id,
x_authorised_on => p_authorised_on,
X_OVERRIDE_TITLE => NULL,
X_RQRMNTS_COMPLETE_IND => NULL,
X_RQRMNTS_COMPLETE_DT => NULL,
X_S_COMPLETED_SOURCE_TYPE => NULL,
X_CATALOG_CAL_TYPE => NULL,
X_CATALOG_SEQ_NUM => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
x_mode => 'R');
FUNCTION update_stream_unit_sets(
p_person_id IN NUMBER,
p_course_cd IN VARCHAR2,
p_old_admin_unit_set IN VARCHAR2,
p_rqrmnts_complete_ind IN VARCHAR2,
p_rqrmnts_complete_dt IN DATE,
p_selection_dt IN DATE,
p_confirmed_ind IN VARCHAR2,
p_log_creation_dt IN DATE,
p_message_name OUT NOCOPY VARCHAR2
) RETURN BOOLEAN AS
------------------------------------------------------------------------------------------------
--Change History:
--Who When What
--bdeviset 29-JUL-2004 Added p_log_creation_dt as parameter.Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW
-- a check is made to see that their is no overlapping of selection,completion and
-- end dates for any two unit sets by calling check_usa_overlap.If it returns
-- false log entry is made and the insert or update is not carried out for bug 3149133.
------------------------------------------------------------------------------------------------
CURSOR c_acad_us (cp_old_admin_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE) IS
SELECT usm.stream_unit_set_Cd
FROM igs_en_unit_set_map usm,
igs_ps_us_prenr_cfg upc
WHERE upc.unit_set_cd = cp_old_admin_unit_set_cd
AND usm.mapping_set_cd = upc.mapping_set_cd
AND usm.sequence_no = upc.sequence_no;
SELECT susa.rowid, susa.*
FROM igs_as_su_setatmpt susa
WHERE susa.unit_set_cd = cp_stream_unit_set_cd
AND susa.person_id = cp_person_id
AND susa.course_cd = cp_course_cd
AND susa.end_dt IS NULL
order by selection_dt desc;
v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
v_Selection_Dt := null;
IF p_selection_dt IS NOT NULL AND vc_susa_upd_rec.selection_dt < p_selection_dt THEN
v_selection_dt := vc_susa_upd_rec.selection_dt;
v_selection_Dt := p_selection_Dt;
v_selection_dt := NVL(NVL(v_selection_dt,vc_susa_upd_rec.selection_dt),sysdate);
OR NVL(v_selection_dt,igs_ge_date.igsdate('1000/01/01 00:00:00'))
<> NVL(vc_susa_upd_rec.selection_dt,igs_ge_date.igsdate('1000/01/01 00:00:00'))
OR v_confirmed_ind <> vc_susa_upd_rec.student_confirmed_ind
THEN
IF igs_en_gen_legacy.check_usa_overlap(
vc_susa_upd_rec.person_id,
vc_susa_upd_rec.course_cd,
TRUNC(v_selection_dt),
v_rqrmnts_complete_dt,
vc_susa_upd_rec.end_dt,
vc_susa_upd_rec.sequence_number,
vc_susa_upd_rec.unit_set_cd,
vc_susa_upd_rec.us_version_number,
p_message_name) = FALSE THEN
p_warn_level := cst_error;
IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
X_ROWID => vc_susa_upd_rec.rowid,
X_PERSON_ID => vc_susa_upd_rec.person_id ,
X_COURSE_CD => vc_susa_upd_rec.course_cd ,
X_UNIT_SET_CD => vc_susa_upd_rec.unit_set_cd ,
X_SEQUENCE_NUMBER => vc_susa_upd_rec.sequence_number ,
X_US_VERSION_NUMBER => vc_susa_upd_rec.us_version_number,
X_SELECTION_DT => TRUNC(v_selection_dt),
X_STUDENT_CONFIRMED_IND => v_confirmed_ind,
X_END_DT => vc_susa_upd_rec.end_dt ,
X_PARENT_UNIT_SET_CD => vc_susa_upd_rec.parent_unit_set_cd,
X_PARENT_SEQUENCE_NUMBER => vc_susa_upd_rec.parent_sequence_number ,
X_PRIMARY_SET_IND => vc_susa_upd_rec.primary_set_ind ,
X_VOLUNTARY_END_IND => vc_susa_upd_rec.voluntary_end_ind ,
X_AUTHORISED_PERSON_ID => vc_susa_upd_rec.authorised_person_id,
X_AUTHORISED_ON => vc_susa_upd_rec.authorised_on ,
X_OVERRIDE_TITLE => vc_susa_upd_rec.override_title ,
X_RQRMNTS_COMPLETE_IND => v_rqrmnts_complete_ind,
X_RQRMNTS_COMPLETE_DT => v_rqrmnts_complete_dt,
X_S_COMPLETED_SOURCE_TYPE => vc_susa_upd_rec.s_completed_source_type,
X_CATALOG_CAL_TYPE => vc_susa_upd_rec.catalog_cal_type ,
X_CATALOG_SEQ_NUM => vc_susa_upd_rec.catalog_seq_num,
X_ATTRIBUTE_CATEGORY => vc_susa_upd_rec.attribute_category,
X_ATTRIBUTE1 => vc_susa_upd_rec.attribute1 ,
X_ATTRIBUTE2 => vc_susa_upd_rec.attribute2 ,
X_ATTRIBUTE3 => vc_susa_upd_rec.attribute3,
X_ATTRIBUTE4 => vc_susa_upd_rec.attribute4,
X_ATTRIBUTE5 => vc_susa_upd_rec.attribute5,
X_ATTRIBUTE6 => vc_susa_upd_rec.attribute6,
X_ATTRIBUTE7 => vc_susa_upd_rec.attribute7,
X_ATTRIBUTE8 => vc_susa_upd_rec.attribute8,
X_ATTRIBUTE9 => vc_susa_upd_rec.attribute9,
X_ATTRIBUTE10 => vc_susa_upd_rec.attribute10,
X_ATTRIBUTE11 => vc_susa_upd_rec.attribute11,
X_ATTRIBUTE12 => vc_susa_upd_rec.attribute12,
X_ATTRIBUTE13 => vc_susa_upd_rec.attribute13,
X_ATTRIBUTE14 => vc_susa_upd_rec.attribute14,
X_ATTRIBUTE15 => vc_susa_upd_rec.attribute15,
X_ATTRIBUTE16 => vc_susa_upd_rec.attribute16,
X_ATTRIBUTE17 => vc_susa_upd_rec.attribute17,
X_ATTRIBUTE18 => vc_susa_upd_rec.attribute18,
X_ATTRIBUTE19 => vc_susa_upd_rec.attribute19,
X_ATTRIBUTE20 => vc_susa_upd_rec.attribute20,
X_MODE => 'R'
);
END update_stream_unit_sets;
SELECT uoo_id FROM IGS_PS_UNIT_OFR_OPT
WHERE unit_cd = p_unit_cd
AND version_number = p_version_number
AND cal_type = p_teach_cal_type
AND ci_sequence_number = p_teach_seq_num
AND location_cd = p_location_cd
AND unit_class = p_unit_class;
IS SELECT party_number FROM hz_parties
WHERE party_id = p_person_id;
SELECT course_type
FROM IGS_PS_VER pv,
IGS_EN_STDNT_PS_ATT sca
WHERE sca.person_id = cp_person_id
AND sca.course_cd = cp_course_cd
AND pv.course_cd = sca.course_cd
AND pv.version_number = sca.version_number;
igs_ss_en_wrappers.insert_into_enr_worksheet(p_person_number =>l_person_number,
p_course_cd =>l_primary_program_cd ,
p_uoo_id =>l_uoo_id,
p_waitlist_ind =>l_waitlist_ind,
p_session_id =>NULL,
p_return_status =>l_ret_stat,
p_message =>p_message,
p_cal_type =>p_load_cal_type,
p_ci_sequence_number =>p_load_seq_num,
p_audit_requested =>'N',
p_enr_method =>p_enr_method,
p_override_cp =>null,
p_subtitle =>null,
p_gradsch_cd =>null,
p_gs_version_num =>null,
p_core_indicator_code =>p_core_indicator_code, -- ptandon, Prevent Dropping Core Units build
p_calling_obj =>'JOB'
);
SELECT load_cal_type, load_ci_sequence_number, load_start_dt, teach_cal_type, teach_ci_sequence_number
FROM IGS_EN_SU_ATTEMPT sua,
IGS_CA_TEACH_TO_LOAD_V tl
WHERE sua.person_id = cp_person_id
AND sua.course_cd = cp_source_course_cd
AND sua.uoo_id = cp_uoo_id
AND sua.cal_type = tl.teach_cal_type
AND sua.ci_sequence_number = tl.teach_ci_sequence_number
order by tl.load_start_dt asc;
SELECT outcome_dt
FROM igs_as_su_stmptout source_suao
WHERE person_id = cp_person_id
AND course_cd = cp_source_course_cd
AND uoo_id = cp_uoo_id
AND finalised_outcome_ind = cp_finalised_outcome_ind
ORDER BY outcome_dt desc;
SELECT load_cal_type, load_ci_sequence_number, load_start_dt
FROM IGS_CA_TEACH_TO_LOAD_V
WHERE cp_date between load_start_dt and load_end_dt
AND load_start_dt >= cp_load_start_dt
AND teach_cal_type = cp_teach_Cal_type
AND teach_ci_sequence_number = cp_teach_sequence_number
ORDER BY load_start_dt asc;
SELECT start_dt
FROM IGS_CA_INST
WHERE cal_type = cp_cal_type
AND sequence_number = cp_sequence_number;
p_delete_source IN BOOLEAN) AS
CURSOR c_suao (cp_person_id IGS_EN_SU_ATTEMPT.PERSON_ID%TYPE,
cp_source_course_cd IGS_EN_SU_ATTEMPT.COURSE_CD%TYPE,
cp_dest_course_cd IGS_EN_SU_ATTEMPT.COURSE_CD%TYPE,
cp_source_uoo_id IGS_EN_SU_ATTEMPT.UOO_ID%TYPE,
cp_dest_uoo_id IGS_EN_SU_ATTEMPT.UOO_ID%TYPE) IS
SELECT source_suao.rowid,source_suao.*
FROM igs_as_su_stmptout source_suao
WHERE person_id = cp_person_id
AND course_cd = cp_source_course_cd
AND uoo_id = cp_source_uoo_id
AND NOT EXISTS (SELECT 'x'
FROM igs_as_su_stmptout dest_suao
WHERE dest_suao.person_id = source_suao.person_id
AND dest_suao.course_cd = cp_dest_course_cd
AND dest_suao.outcome_dt = source_suao.outcome_dt
AND dest_suao.grading_period_cd = source_suao.grading_period_cd
AND dest_suao.uoo_id = cp_dest_uoo_id)
order by outcome_dt asc;
SELECT usec.UNIT_CD,
usec.CAL_TYPE,
usec.CI_SEQUENCE_NUMBER,
CI.START_DT,
CI.END_DT
FROM IGS_PS_UNIT_OFR_OPT usec,
IGS_CA_INST ci
WHERE usec.UOO_ID = cp_uoo_id
and ci.cal_type = usec.cal_type
and ci.sequence_number = usec.ci_sequence_number;
IF p_delete_source THEN
igs_as_su_stmptout_pkg.DELETE_ROW(v_suao_rec.rowid);
igs_as_su_stmptout_pkg.insert_row(
X_ROWID => l_ROWID ,
X_ORG_ID => v_suao_rec.ORG_ID ,
X_PERSON_ID => v_suao_rec.PERSON_ID ,
X_COURSE_CD => P_DEST_COURSE_CD ,
X_UNIT_CD => v_dest_usec_rec.UNIT_CD ,
X_CAL_TYPE => v_dest_usec_rec.CAL_TYPE ,
X_CI_SEQUENCE_NUMBER => v_dest_usec_rec.CI_SEQUENCE_NUMBER ,
X_OUTCOME_DT => v_suao_rec.OUTCOME_DT ,
X_CI_START_DT => v_dest_usec_rec.START_DT ,
X_CI_END_DT => v_dest_usec_rec.END_DT ,
X_GRADING_SCHEMA_CD => v_suao_rec.GRADING_SCHEMA_CD ,
X_VERSION_NUMBER => v_suao_rec.VERSION_NUMBER ,
X_GRADE => v_suao_rec.GRADE ,
X_S_GRADE_CREATION_METHOD_TYPE => v_suao_rec.S_GRADE_CREATION_METHOD_TYPE ,
X_FINALISED_OUTCOME_IND => v_suao_rec.FINALISED_OUTCOME_IND ,
X_MARK => v_suao_rec.MARK ,
X_NUMBER_TIMES_KEYED => v_suao_rec.NUMBER_TIMES_KEYED ,
X_TRANSLATED_GRADING_SCHEMA_CD => v_suao_rec. TRANSLATED_GRADING_SCHEMA_CD ,
X_TRANSLATED_VERSION_NUMBER => v_suao_rec.TRANSLATED_VERSION_NUMBER ,
X_TRANSLATED_GRADE => v_suao_rec.TRANSLATED_GRADE ,
X_TRANSLATED_DT => v_suao_rec.TRANSLATED_DT ,
X_MODE => 'R' ,
X_GRADING_PERIOD_CD => v_suao_rec.GRADING_PERIOD_CD ,
X_ATTRIBUTE_CATEGORY => v_suao_rec.ATTRIBUTE_CATEGORY ,
X_ATTRIBUTE1 => v_suao_rec.ATTRIBUTE1 ,
X_ATTRIBUTE2 => v_suao_rec.ATTRIBUTE2 ,
X_ATTRIBUTE3 => v_suao_rec.ATTRIBUTE3 ,
X_ATTRIBUTE4 => v_suao_rec.ATTRIBUTE4 ,
X_ATTRIBUTE5 => v_suao_rec.ATTRIBUTE5 ,
X_ATTRIBUTE6 => v_suao_rec.ATTRIBUTE6 ,
X_ATTRIBUTE7 => v_suao_rec.ATTRIBUTE7 ,
X_ATTRIBUTE8 => v_suao_rec.ATTRIBUTE8 ,
X_ATTRIBUTE9 => v_suao_rec.ATTRIBUTE9 ,
X_ATTRIBUTE10 => v_suao_rec.ATTRIBUTE10 ,
X_ATTRIBUTE11 => v_suao_rec.ATTRIBUTE11 ,
X_ATTRIBUTE12 => v_suao_rec.ATTRIBUTE12 ,
X_ATTRIBUTE13 => v_suao_rec.ATTRIBUTE13 ,
X_ATTRIBUTE14 => v_suao_rec.ATTRIBUTE14 ,
X_ATTRIBUTE15 => v_suao_rec.ATTRIBUTE15 ,
X_ATTRIBUTE16 => v_suao_rec.ATTRIBUTE16 ,
X_ATTRIBUTE17 => v_suao_rec.ATTRIBUTE17 ,
X_ATTRIBUTE18 => v_suao_rec.ATTRIBUTE18 ,
X_ATTRIBUTE19 => v_suao_rec.ATTRIBUTE19 ,
X_ATTRIBUTE20 => v_suao_rec.ATTRIBUTE20 ,
X_INCOMP_DEADLINE_DATE => v_suao_rec.INCOMP_DEADLINE_DATE ,
X_INCOMP_GRADING_SCHEMA_CD => v_suao_rec.INCOMP_GRADING_SCHEMA_CD ,
X_INCOMP_VERSION_NUMBER => v_suao_rec.INCOMP_VERSION_NUMBER ,
X_INCOMP_DEFAULT_GRADE => v_suao_rec.INCOMP_DEFAULT_GRADE ,
X_INCOMP_DEFAULT_MARK => v_suao_rec.INCOMP_DEFAULT_MARK ,
X_COMMENTS => v_suao_rec.COMMENTS ,
X_UOO_ID => v_suao_rec.UOO_ID ,
X_MARK_CAPPED_FLAG => v_suao_rec.MARK_CAPPED_FLAG ,
X_RELEASE_DATE => v_suao_rec.RELEASE_DATE ,
X_MANUAL_OVERRIDE_FLAG => v_suao_rec.MANUAL_OVERRIDE_FLAG ,
X_SHOW_ON_ACADEMIC_HISTRY_FLAG => v_suao_rec. SHOW_ON_ACADEMIC_HISTRY_FLAG );
SELECT rowid, SPLACEMENT_ID, person_id, course_cd, uoo_id,start_date,
end_date, institution_code, title, description, category_code,
placement_type_code, SPECIALTY_CODE, compensation_flag,
attendance_type, location, notes
FROM IGS_EN_SPLACEMENTS
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
SELECT rowid, supervisor_id
FROM IGS_EN_SPLACE_SUPS
WHERE Splacement_id=c_splacement_id;
SELECT rowid, FACULTY_ID
FROM IGS_EN_SPLACE_FACS
WHERE Splacement_id=c_splacement_id;
igs_en_splace_sups_pkg.delete_ROW(v_cur_sp_rec.rowid);
IGS_EN_SPLACE_FACS_pkg.delete_row(v_cur_fac_rec.rowid);
igs_en_splacements_pkg.delete_row(v_source_splace.rowid);
igs_en_splacements_pkg.insert_row (
x_rowid => l_splace_rowid ,
x_splacement_id => l_splacement_id ,
x_person_id => v_source_splace.person_id ,
x_course_cd => p_dest_course_cd ,
x_uoo_id => p_dest_uoo_id ,
x_start_date => v_source_splace.start_date ,
x_end_date => v_source_splace.end_date ,
x_institution_code => v_source_splace.institution_code ,
x_title => v_source_splace.title ,
x_description => v_source_splace.description ,
x_category_code => v_source_splace.category_code ,
x_placement_type_code=> v_source_splace.placement_type_code,
x_specialty_code => v_source_splace.specialty_code ,
x_compensation_flag => v_source_splace.compensation_flag ,
x_attendance_type => v_source_splace.attendance_type ,
x_location => v_source_splace.location ,
x_notes => v_source_splace.notes ,
x_mode => 'R' );
igs_en_splace_sups_pkg.INSERT_ROW(
x_rowid => l_rowid,
x_splacement_id => l_splacement_id,
x_supervisor_id => v_sup_tab(v_sup_index).supervisor_id,
x_mode => 'R');
IGS_EN_SPLACE_FACS_pkg.INSERT_ROW(
x_rowid => l_rowid,
x_splacement_id => l_splacement_id,
x_FACULTY_ID => v_fac_tab(v_fac_index).faculty_id,
x_mode => 'R');
|| unselected enrolled or waitlisted or invalid units when transfer
|| is across careers and discontinue source is set to 'NO' as a
|| part of bug#4061818
|| smaddali 21-dec-04 Modified for bug#4083358 , to change logic for dropping selected unit attempts across terms
*/
v_uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE;
SELECT *
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = cp_person_id
and course_cd = cp_source_course_cd
and uoo_id = cp_uoo_id;
SELECT 'X'
FROM IGS_PS_UNIT_OFR_OPT uoo
WHERE uoo.relation_type = 'SUBORDINATE'
AND uoo.sup_uoo_id = cp_uoo_id
AND exists (SELECT 'X'
FROM IGS_EN_SU_ATTEMPT sub_sua
WHERE sub_sua.person_id = cp_person_id
AND sub_sua.course_cd = cp_source_program_cd
AND sub_sua.uoo_id = uoo.uoo_id
AND sub_sua.unit_attempt_status <> cst_dropped);
SELECT uoo_id FROM IGS_EN_SU_ATTEMPT
WHERE person_id = p_person_id AND
course_cd = p_source_course_cd AND
unit_attempt_status NOT IN (cst_dropped,cst_duplicate)
ORDER BY SUP_UNIT_CD ;
SELECT ver.course_type
FROM IGS_PS_VER ver ,
igs_en_stdnt_ps_att spa
WHERE ver.course_cd = spa.course_cd AND
ver.version_number = spa.version_number AND
spa.course_cd = cp_course_cd AND
spa.person_id = cp_person_id;
SELECT DISCONTINUATION_REASON_CD
FROM IGS_EN_DCNT_REASONCD
WHERE S_DISCONTINUATION_REASON_TYPE = 'UNIT_TRANS'
AND DCNT_UNIT_IND = 'Y'
AND SYS_DFLT_IND = 'Y'
AND CLOSED_IND = 'N';
SELECT discontinuation_reason_cd
FROM igs_en_dcnt_reasoncd
WHERE NVL(closed_ind,'N') ='N'
AND dflt_ind ='Y'
AND dcnt_unit_ind ='Y'
AND s_discontinuation_reason_type IS NULL;
igs_en_sua_api.update_unit_attempt( -- calling the API since the counts have to be updated
X_ROWID => v_sua_source_rec.ROW_ID,
X_PERSON_ID => v_sua_source_rec.PERSON_ID,
X_COURSE_CD => v_sua_source_rec.COURSE_CD ,
X_UNIT_CD => v_sua_source_rec.UNIT_CD,
X_CAL_TYPE => v_sua_source_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => v_sua_source_rec.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => v_sua_source_rec.VERSION_NUMBER ,
X_LOCATION_CD => v_sua_source_rec.LOCATION_CD,
X_UNIT_CLASS => v_sua_source_rec.UNIT_CLASS ,
X_CI_START_DT => v_sua_source_rec.CI_START_DT,
X_CI_END_DT => v_sua_source_rec.CI_END_DT,
X_UOO_ID => v_sua_source_rec.UOO_ID ,
X_ENROLLED_DT => v_sua_source_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => cst_dropped, -- updating the status to dropped
X_ADMINISTRATIVE_UNIT_STATUS => v_sua_source_rec.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => v_sua_source_rec.administrative_PRIORITY,
X_DISCONTINUED_DT => nvl(v_sua_source_rec.discontinued_dt,trunc(SYSDATE)),
X_DCNT_REASON_CD => l_dflt_disc_code,
X_RULE_WAIVED_DT => v_sua_source_rec.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => v_sua_source_rec.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => v_sua_source_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => v_sua_source_rec.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => v_sua_source_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => v_sua_source_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => v_sua_source_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => v_sua_source_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => v_sua_source_rec.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => v_sua_source_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => v_sua_source_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => v_sua_source_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => v_sua_source_rec.waitlist_dt,
X_MODE => 'R',
X_GS_VERSION_NUMBER => v_sua_source_rec.gs_version_number,
X_ENR_METHOD_TYPE => v_sua_source_rec.enr_method_type,
X_FAILED_UNIT_RULE => v_sua_source_rec.FAILED_UNIT_RULE,
X_CART => v_sua_source_rec.CART,
X_RSV_SEAT_EXT_ID => v_sua_source_rec.RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD => v_sua_source_rec.org_unit_cd ,
X_SESSION_ID => v_sua_source_rec.session_id,
X_GRADING_SCHEMA_CODE => v_sua_source_rec.grading_schema_code,
X_DEG_AUD_DETAIL_ID => v_sua_source_rec.deg_aud_detail_id,
X_SUBTITLE => v_sua_source_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => v_sua_source_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => v_sua_source_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => v_sua_source_rec.attribute_category,
X_ATTRIBUTE1 => v_sua_source_rec.attribute1,
X_ATTRIBUTE2 => v_sua_source_rec.attribute2,
X_ATTRIBUTE3 => v_sua_source_rec.attribute3,
X_ATTRIBUTE4 => v_sua_source_rec.attribute4,
X_ATTRIBUTE5 => v_sua_source_rec.attribute5,
X_ATTRIBUTE6 => v_sua_source_rec.attribute6,
X_ATTRIBUTE7 => v_sua_source_rec.attribute7,
X_ATTRIBUTE8 => v_sua_source_rec.attribute8,
X_ATTRIBUTE9 => v_sua_source_rec.attribute9,
X_ATTRIBUTE10 => v_sua_source_rec.attribute10,
X_ATTRIBUTE11 => v_sua_source_rec.attribute11,
X_ATTRIBUTE12 => v_sua_source_rec.attribute12,
X_ATTRIBUTE13 => v_sua_source_rec.attribute13,
X_ATTRIBUTE14 => v_sua_source_rec.attribute14,
X_ATTRIBUTE15 => v_sua_source_rec.attribute15,
X_ATTRIBUTE16 => v_sua_source_rec.attribute16,
X_ATTRIBUTE17 => v_sua_source_rec.attribute17,
X_ATTRIBUTE18 => v_sua_source_rec.attribute18,
X_ATTRIBUTE19 => v_sua_source_rec.attribute19,
X_ATTRIBUTE20 => v_sua_source_rec.attribute20,
X_WAITLIST_MANUAL_IND => v_sua_source_rec.waitlist_manual_ind,
X_WLST_PRIORITY_WEIGHT_NUM => v_sua_source_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => v_sua_source_rec.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => v_sua_source_rec.core_indicator_code
);
IGS_EN_SU_ATTEMPT_PKG.UPDATE_ROW( -- calling the TBH since the count need not be updated
X_ROWID => v_sua_source_rec.ROW_ID,
X_PERSON_ID => v_sua_source_rec.PERSON_ID,
X_COURSE_CD => v_sua_source_rec.COURSE_CD ,
X_UNIT_CD => v_sua_source_rec.UNIT_CD,
X_CAL_TYPE => v_sua_source_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => v_sua_source_rec.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => v_sua_source_rec.VERSION_NUMBER ,
X_LOCATION_CD => v_sua_source_rec.LOCATION_CD,
X_UNIT_CLASS => v_sua_source_rec.UNIT_CLASS ,
X_CI_START_DT => v_sua_source_rec.CI_START_DT,
X_CI_END_DT => v_sua_source_rec.CI_END_DT,
X_UOO_ID => v_sua_source_rec.UOO_ID ,
X_ENROLLED_DT => v_sua_source_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => cst_dropped, -- modifying the status to dropped
X_ADMINISTRATIVE_UNIT_STATUS => v_sua_source_rec.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => v_sua_source_rec.administrative_PRIORITY,
X_DISCONTINUED_DT => nvl(v_sua_source_rec.discontinued_dt,SYSDATE),
X_DCNT_REASON_CD => l_unt_disc_code,
X_RULE_WAIVED_DT => v_sua_source_rec.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => v_sua_source_rec.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => v_sua_source_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => v_sua_source_rec.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => v_sua_source_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => v_sua_source_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => v_sua_source_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => v_sua_source_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => v_sua_source_rec.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => v_sua_source_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => v_sua_source_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => v_sua_source_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => v_sua_source_rec.waitlist_dt,
X_MODE => 'R',
X_GS_VERSION_NUMBER => v_sua_source_rec.gs_version_number,
X_ENR_METHOD_TYPE => v_sua_source_rec.enr_method_type,
X_FAILED_UNIT_RULE => v_sua_source_rec.FAILED_UNIT_RULE,
X_CART => v_sua_source_rec.CART,
X_RSV_SEAT_EXT_ID => v_sua_source_rec.RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD => v_sua_source_rec.org_unit_cd ,
X_SESSION_ID => v_sua_source_rec.session_id,
X_GRADING_SCHEMA_CODE => v_sua_source_rec.grading_schema_code,
X_DEG_AUD_DETAIL_ID => v_sua_source_rec.deg_aud_detail_id,
X_SUBTITLE => v_sua_source_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => v_sua_source_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => v_sua_source_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => v_sua_source_rec.attribute_category,
X_ATTRIBUTE1 => v_sua_source_rec.attribute1,
X_ATTRIBUTE2 => v_sua_source_rec.attribute2,
X_ATTRIBUTE3 => v_sua_source_rec.attribute3,
X_ATTRIBUTE4 => v_sua_source_rec.attribute4,
X_ATTRIBUTE5 => v_sua_source_rec.attribute5,
X_ATTRIBUTE6 => v_sua_source_rec.attribute6,
X_ATTRIBUTE7 => v_sua_source_rec.attribute7,
X_ATTRIBUTE8 => v_sua_source_rec.attribute8,
X_ATTRIBUTE9 => v_sua_source_rec.attribute9,
X_ATTRIBUTE10 => v_sua_source_rec.attribute10,
X_ATTRIBUTE11 => v_sua_source_rec.attribute11,
X_ATTRIBUTE12 => v_sua_source_rec.attribute12,
X_ATTRIBUTE13 => v_sua_source_rec.attribute13,
X_ATTRIBUTE14 => v_sua_source_rec.attribute14,
X_ATTRIBUTE15 => v_sua_source_rec.attribute15,
X_ATTRIBUTE16 => v_sua_source_rec.attribute16,
X_ATTRIBUTE17 => v_sua_source_rec.attribute17,
X_ATTRIBUTE18 => v_sua_source_rec.attribute18,
X_ATTRIBUTE19 => v_sua_source_rec.attribute19,
X_ATTRIBUTE20 => v_sua_source_rec.attribute20,
X_WAITLIST_MANUAL_IND => v_sua_source_rec.waitlist_manual_ind ,
X_WLST_PRIORITY_WEIGHT_NUM => v_sua_source_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => v_sua_source_rec.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => v_sua_source_rec.core_indicator_code,
X_UPD_AUDIT_FLAG => v_sua_source_rec.upd_audit_flag,
X_SS_SOURCE_IND => v_sua_source_rec.ss_source_ind
);
igs_en_sua_api.update_unit_attempt( -- calling the API since the counts have to be updated
X_ROWID => v_sua_source_rec.ROW_ID,
X_PERSON_ID => v_sua_source_rec.PERSON_ID,
X_COURSE_CD => v_sua_source_rec.COURSE_CD ,
X_UNIT_CD => v_sua_source_rec.UNIT_CD,
X_CAL_TYPE => v_sua_source_rec.CAL_TYPE,
X_CI_SEQUENCE_NUMBER => v_sua_source_rec.CI_SEQUENCE_NUMBER ,
X_VERSION_NUMBER => v_sua_source_rec.VERSION_NUMBER ,
X_LOCATION_CD => v_sua_source_rec.LOCATION_CD,
X_UNIT_CLASS => v_sua_source_rec.UNIT_CLASS ,
X_CI_START_DT => v_sua_source_rec.CI_START_DT,
X_CI_END_DT => v_sua_source_rec.CI_END_DT,
X_UOO_ID => v_sua_source_rec.UOO_ID ,
X_ENROLLED_DT => v_sua_source_rec.ENROLLED_DT,
X_UNIT_ATTEMPT_STATUS => cst_dropped, -- updating the status to dropped
X_ADMINISTRATIVE_UNIT_STATUS => v_sua_source_rec.administrative_unit_status,
X_ADMINISTRATIVE_PRIORITY => v_sua_source_rec.administrative_PRIORITY,
X_DISCONTINUED_DT => nvl(v_sua_source_rec.discontinued_dt,trunc(SYSDATE)),
X_DCNT_REASON_CD => l_dflt_disc_code,
X_RULE_WAIVED_DT => v_sua_source_rec.RULE_WAIVED_DT ,
X_RULE_WAIVED_PERSON_ID => v_sua_source_rec.RULE_WAIVED_PERSON_ID ,
X_NO_ASSESSMENT_IND => v_sua_source_rec.NO_ASSESSMENT_IND,
X_SUP_UNIT_CD => v_sua_source_rec.SUP_UNIT_CD ,
X_SUP_VERSION_NUMBER => v_sua_source_rec.SUP_VERSION_NUMBER,
X_EXAM_LOCATION_CD => v_sua_source_rec.EXAM_LOCATION_CD,
X_ALTERNATIVE_TITLE => v_sua_source_rec.ALTERNATIVE_TITLE,
X_OVERRIDE_ENROLLED_CP => v_sua_source_rec.OVERRIDE_ENROLLED_CP,
X_OVERRIDE_EFTSU => v_sua_source_rec.OVERRIDE_EFTSU ,
X_OVERRIDE_ACHIEVABLE_CP => v_sua_source_rec.OVERRIDE_ACHIEVABLE_CP,
X_OVERRIDE_OUTCOME_DUE_DT => v_sua_source_rec.OVERRIDE_OUTCOME_DUE_DT,
X_OVERRIDE_CREDIT_REASON => v_sua_source_rec.OVERRIDE_CREDIT_REASON,
X_WAITLIST_DT => v_sua_source_rec.waitlist_dt,
X_MODE => 'R',
X_GS_VERSION_NUMBER => v_sua_source_rec.gs_version_number,
X_ENR_METHOD_TYPE => v_sua_source_rec.enr_method_type,
X_FAILED_UNIT_RULE => v_sua_source_rec.FAILED_UNIT_RULE,
X_CART => v_sua_source_rec.CART,
X_RSV_SEAT_EXT_ID => v_sua_source_rec.RSV_SEAT_EXT_ID ,
X_ORG_UNIT_CD => v_sua_source_rec.org_unit_cd ,
X_SESSION_ID => v_sua_source_rec.session_id,
X_GRADING_SCHEMA_CODE => v_sua_source_rec.grading_schema_code,
X_DEG_AUD_DETAIL_ID => v_sua_source_rec.deg_aud_detail_id,
X_SUBTITLE => v_sua_source_rec.subtitle,
X_STUDENT_CAREER_TRANSCRIPT => v_sua_source_rec.student_career_transcript,
X_STUDENT_CAREER_STATISTICS => v_sua_source_rec.student_career_statistics,
X_ATTRIBUTE_CATEGORY => v_sua_source_rec.attribute_category,
X_ATTRIBUTE1 => v_sua_source_rec.attribute1,
X_ATTRIBUTE2 => v_sua_source_rec.attribute2,
X_ATTRIBUTE3 => v_sua_source_rec.attribute3,
X_ATTRIBUTE4 => v_sua_source_rec.attribute4,
X_ATTRIBUTE5 => v_sua_source_rec.attribute5,
X_ATTRIBUTE6 => v_sua_source_rec.attribute6,
X_ATTRIBUTE7 => v_sua_source_rec.attribute7,
X_ATTRIBUTE8 => v_sua_source_rec.attribute8,
X_ATTRIBUTE9 => v_sua_source_rec.attribute9,
X_ATTRIBUTE10 => v_sua_source_rec.attribute10,
X_ATTRIBUTE11 => v_sua_source_rec.attribute11,
X_ATTRIBUTE12 => v_sua_source_rec.attribute12,
X_ATTRIBUTE13 => v_sua_source_rec.attribute13,
X_ATTRIBUTE14 => v_sua_source_rec.attribute14,
X_ATTRIBUTE15 => v_sua_source_rec.attribute15,
X_ATTRIBUTE16 => v_sua_source_rec.attribute16,
X_ATTRIBUTE17 => v_sua_source_rec.attribute17,
X_ATTRIBUTE18 => v_sua_source_rec.attribute18,
X_ATTRIBUTE19 => v_sua_source_rec.attribute19,
X_ATTRIBUTE20 => v_sua_source_rec.attribute20,
X_WAITLIST_MANUAL_IND => v_sua_source_rec.waitlist_manual_ind,
X_WLST_PRIORITY_WEIGHT_NUM => v_sua_source_rec.wlst_priority_weight_num,
X_WLST_PREFERENCE_WEIGHT_NUM => v_sua_source_rec.wlst_preference_weight_num,
X_CORE_INDICATOR_CODE => v_sua_source_rec.core_indicator_code
);
CURSOR cur_supuoo_id IS select sup_uoo_id
from IGS_PS_UNIT_OFR_OPT where uoo_id=p_uoo_id;
Select 1 from IGS_EN_SU_ATTEMPT
Where person_id=cp_person_id AND
course_cd=cp_course_cd AND
uoo_id=cp_uoo_id AND
unit_attempt_status <> 'DROPPED';
SELECT sup_unit_Cd
FROM IGS_EN_SU_ATTEMPT
WHERE person_id = cp_person_id
AND course_cd = cp_course_cd
AND uoo_id = cp_uoo_id;
p_delete_source IN BOOLEAN)
IS
Cursor c_source_ai_group(cp_person_id NUMBER,
cp_source_course_cd VARCHAR2,
cp_source_uoo_id NUMBER) IS
SELECT sag.rowid, sag.*
FROM IGS_AS_SUA_AI_GROUP sag
WHERE sag.person_id = cp_person_id
AND sag.course_cd = cp_source_course_cd
AND sag.uoo_id = cp_source_uoo_id
AND logical_delete_date IS NULL;
SELECT GROUP_NAME
FROM IGS_AS_SUA_AI_GROUP
WHERE person_id = cp_person_id
and course_cd = cp_dest_course_cd
and uoo_id = cp_dest_uoo_id
and group_name = cp_group_name;
SELECT COUNT (person_id)
FROM igs_as_usecai_sua_v
WHERE person_id = cp_person_id
AND course_cd = cp_dest_course_cd
AND uoo_id = cp_dest_uoo_id
AND usai_logical_delete_dt IS NULL;
SELECT asit.rowid, asit.*
FROM IGS_AS_SU_ATMPT_ITM asit
WHERE asit.person_id = cp_person_id
and asit.course_cd = cp_course_cd
and asit.uoo_id = cp_uoo_id
and asit.SUA_ASS_ITEM_GROUP_ID = c_sua_ass_item_group_id
and asit.LOGICAL_DELETE_DT IS NULL;
SELECT usai_dest.UNIT_SECTION_ASS_ITEM_ID
FROM igs_ps_unitass_item_v usai_source,
igs_ps_unitass_item_v usai_dest,
igs_as_assessmnt_itm ai_dest,
igs_as_assessmnt_itm ai_source
WHERE usai_source.unit_section_ass_item_id = cp_unit_section_ass_item_id
and usai_dest.uoo_id = cp_dest_uoo_id
and usai_dest.ass_id = ai_dest.ass_id
and usai_source.ass_id = ai_source.ass_id
and ai_source.ASSESSMENT_TYPE = ai_dest.ASSESSMENT_TYPE
and usai_source.REFERENCE = usai_dest.REFERENCE
and usai_source.GRADING_SCHEMA_CD = usai_dest.GRADING_SCHEMA_CD
and usai_source.GS_VERSION_NUMBER = usai_dest.GS_VERSION_NUMBER;
SELECT suv_dest.unit_ass_item_id
FROM igs_as_uai_sua_v suv_dest ,
igs_as_assessmnt_itm ai_dest,
igs_as_uai_sua_v suv_source
WHERE suv_dest.uai_logical_delete_dt is null
and ai_dest.ass_id = suv_dest.ass_id
and suv_dest.person_id = cp_person_id
and suv_dest.course_cd = cp_dest_course_cd
and suv_dest.uoo_id = cp_dest_uoo_id
and ai_dest.closed_ind = 'N'
and suv_source.unit_ass_item_id = cp_source_unit_ass_item_id
and suv_source.ass_id = suv_dest.ass_id
and suv_source.sequence_number = suv_dest.sequence_number
and suv_source.person_id = cp_person_id
and suv_source.course_cd = cp_source_course_cd;
igs_as_sua_ai_group_pkg.insert_row (
x_rowid => l_rowid,
x_sua_ass_item_group_id => l_sua_ass_itemgrp_id,
x_person_id => vc_source_ai_group.person_id,
x_course_cd => p_dest_course_cd,
x_uoo_id => p_dest_uoo_id,
x_group_name => vc_source_ai_group.group_name,
x_midterm_formula_code => vc_source_ai_group.midterm_formula_code,
x_midterm_formula_qty => vc_source_ai_group.midterm_formula_qty,
x_midterm_weight_qty => vc_source_ai_group.midterm_weight_qty,
x_final_formula_code => vc_source_ai_group.final_formula_code,
x_final_formula_qty => vc_source_ai_group.final_formula_qty,
x_final_weight_qty => vc_source_ai_group.final_weight_qty,
x_unit_ass_item_group_id => vc_source_ai_group.unit_ass_item_group_id,
x_us_ass_item_group_id => vc_source_ai_group.us_ass_item_group_id,
x_logical_delete_date => vc_source_ai_group.logical_delete_date,
x_mode => 'R');
igs_as_su_atmpt_itm_pkg.insert_row (
x_rowid => l_rowid2,
x_person_id => p_person_id,
x_course_cd => p_dest_course_cd,
x_unit_cd => vc_source_su_itm.unit_cd,
x_cal_type => vc_source_su_itm.cal_type,
x_ci_sequence_number => vc_source_su_itm.ci_sequence_number,
x_ass_id => vc_source_su_itm.ass_id,
x_creation_dt => vc_source_su_itm.creation_dt,
x_attempt_number => vc_source_su_itm.attempt_number,
x_outcome_dt => vc_source_su_itm.outcome_dt,
x_override_due_dt => vc_source_su_itm.override_due_dt,
x_tracking_id => vc_source_su_itm.tracking_id,
x_logical_delete_dt => vc_source_su_itm.logical_delete_dt,
x_s_default_ind => vc_source_su_itm.s_default_ind,
x_ass_pattern_id => vc_source_su_itm.ass_pattern_id,
x_mode => 'R',
x_grading_schema_cd => vc_source_su_itm.grading_schema_cd,
x_gs_version_number => vc_source_su_itm.gs_version_number,
x_grade => vc_source_su_itm.grade,
x_outcome_comment_code => vc_source_su_itm.outcome_comment_code,
x_mark => vc_source_su_itm.mark,
x_attribute_category => vc_source_su_itm.attribute_category,
x_attribute1 => vc_source_su_itm.attribute1,
x_attribute2 => vc_source_su_itm.attribute2,
x_attribute3 => vc_source_su_itm.attribute3,
x_attribute4 => vc_source_su_itm.attribute4,
x_attribute5 => vc_source_su_itm.attribute5,
x_attribute6 => vc_source_su_itm.attribute6,
x_attribute7 => vc_source_su_itm.attribute7,
x_attribute8 => vc_source_su_itm.attribute8,
x_attribute9 => vc_source_su_itm.attribute9,
x_attribute10 => vc_source_su_itm.attribute10,
x_attribute11 => vc_source_su_itm.attribute11,
x_attribute12 => vc_source_su_itm.attribute12,
x_attribute13 => vc_source_su_itm.attribute13,
x_attribute14 => vc_source_su_itm.attribute14,
x_attribute15 => vc_source_su_itm.attribute15,
x_attribute16 => vc_source_su_itm.attribute16,
x_attribute17 => vc_source_su_itm.attribute17,
x_attribute18 => vc_source_su_itm.attribute18,
x_attribute19 => vc_source_su_itm.attribute19,
x_attribute20 => vc_source_su_itm.attribute20,
x_uoo_id => p_dest_uoo_id,
x_unit_section_ass_item_id => vc_source_su_itm.unit_section_ass_item_id, -- this parameter is overriden
x_unit_ass_item_id => vc_source_su_itm.unit_ass_item_id,-- this parameter is overriden
x_sua_ass_item_group_id => l_sua_ass_itemgrp_id,
x_midterm_mandatory_type_code => vc_source_su_itm.midterm_mandatory_type_code,
x_midterm_weight_qty => vc_source_su_itm.midterm_weight_qty,
x_final_mandatory_type_code => vc_source_su_itm.final_mandatory_type_code,
x_final_weight_qty => vc_source_su_itm.final_weight_qty,
x_submitted_date => vc_source_su_itm.submitted_date,
x_waived_flag => vc_source_su_itm.waived_flag,
x_penalty_applied_flag => vc_source_su_itm.penalty_applied_flag );
IF (p_delete_source = TRUE) THEN
IGS_AS_SU_ATMPT_ITM_PKG.delete_row (x_rowid => vc_source_su_itm.rowid);
IF (p_delete_source = TRUE) THEN
IGS_AS_SUA_AI_GROUP_PKG.DELETE_ROW(x_rowid => vc_source_ai_group.rowid);
SELECT sca.version_number,
sca.coo_id,
sca.commencement_dt
FROM IGS_EN_STDNT_PS_ATT sca
WHERE person_id = p_person_id AND
course_cd = p_course_cd;
SELECT uoo_id
FROM igs_ps_unit_ofr_opt
WHERE unit_cd = p_unit_cd
AND version_number = p_uv_version_number
AND cal_type = p_cal_type
AND ci_sequence_number = p_ci_sequence_number
AND location_cd = p_location_cd
AND unit_class = p_unit_class;
IF NOT IGS_EN_VAL_SUA.enrp_val_sua_insert(
p_person_id,
p_course_cd,
cst_enrolled,
v_message_name) THEN
p_fail_type := cst_course;