[Home] [Help]
305: p_load_ci_seq_num IN NUMBER)
306: RETURN BOOLEAN AS
307: /* HISTORY
308: WHO WHEN WHAT
309: bdeviset 29-JUL-2004 Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW in a check is
310: made to see that their is no overlapping of selection,completion and
311: end dates for any two unit sets by calling check_usa_overlap.If it returns
312: false log entry is made and the insert or update is not carried out for bug 3149133.
313: knag 29-OCT-2002 Bug 2647482 addded parameters attendance_mode, location_cd for calculation
339: -- * Check the students eligibility to enrol in the specified IGS_PS_COURSE
340: -- in the specified academic calendar.
341: -- * Create a IGS_EN_STDNT_PS_ATT record
342: -- * Create a IGS_AS_SC_ATMPT_ENR record
343: -- * Create any required IGS_AS_SU_SETATMPT details
344: -- * Create default IGS_EN_STDNTPSHECSOP details
345: -- * Pre-enrol IGS_PS_UNIT attempts entered during Admissions, as parameter
346: -- to the process or through the Pattern of Study.
347: -- If at any point it becomes impossible to pre-enrol the student,
515: cst_fee_cntrct CONSTANT VARCHAR2(10) := 'FEE-CNTRCT';
516:
517: -- smaddali added these new cursors and function for YOP-EN dld bug#2156956
518: l_confirmed_ind VARCHAR2(1);
519: l_seqval igs_as_su_setatmpt.sequence_number%TYPE ;
520: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE ;
521: v_rqrmnts_complete_dt igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE;
522:
523: -- checks the eligibility of the student to be moved to the next year of program (unit set)
516:
517: -- smaddali added these new cursors and function for YOP-EN dld bug#2156956
518: l_confirmed_ind VARCHAR2(1);
519: l_seqval igs_as_su_setatmpt.sequence_number%TYPE ;
520: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE ;
521: v_rqrmnts_complete_dt igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE;
522:
523: -- checks the eligibility of the student to be moved to the next year of program (unit set)
524: -- by checking if there is any outcome preventing the progress of the student program attempt
517: -- smaddali added these new cursors and function for YOP-EN dld bug#2156956
518: l_confirmed_ind VARCHAR2(1);
519: l_seqval igs_as_su_setatmpt.sequence_number%TYPE ;
520: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE ;
521: v_rqrmnts_complete_dt igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE;
522:
523: -- checks the eligibility of the student to be moved to the next year of program (unit set)
524: -- by checking if there is any outcome preventing the progress of the student program attempt
525: CURSOR c_prog_outcome(cp_select_dt igs_as_su_setatmpt.selection_dt%TYPE) IS
521: v_rqrmnts_complete_dt igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE;
522:
523: -- checks the eligibility of the student to be moved to the next year of program (unit set)
524: -- by checking if there is any outcome preventing the progress of the student program attempt
525: CURSOR c_prog_outcome(cp_select_dt igs_as_su_setatmpt.selection_dt%TYPE) IS
526: SELECT pou.decision_dt, pout.s_progression_outcome_type
527: FROM igs_pr_stdnt_pr_ou_all pou , igs_pr_ou_type pout
528: WHERE pou.person_id = p_person_id AND
529: pou.course_cd = p_course_cd AND
538:
539: -- get the currently active unit set for the person course attempt
540: CURSOR c_active_us IS
541: SELECT susa.*
542: FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
543: WHERE susa.person_id = p_person_id AND
544: susa.course_cd = p_course_cd AND
545: susa.selection_dt IS NOT NULL AND
546: susa.end_dt IS NULL AND
606: IGS_EN_GEN_015.get_effective_census_date(Null,Null,sua.cal_type,sua.ci_sequence_number) < TRUNC(SYSDATE) ;
607: c_census_dt_rec c_chk_census_dt%ROWTYPE ;
608:
609: -- check if susa already exists and if so update it
610: CURSOR c_exists_susa(cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE ,
611: cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE ) IS
612: SELECT sequence_number , student_confirmed_ind
613: FROM IGS_AS_SU_SETATMPT
614: WHERE person_id = p_person_id AND
607: c_census_dt_rec c_chk_census_dt%ROWTYPE ;
608:
609: -- check if susa already exists and if so update it
610: CURSOR c_exists_susa(cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE ,
611: cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE ) IS
612: SELECT sequence_number , student_confirmed_ind
613: FROM IGS_AS_SU_SETATMPT
614: WHERE person_id = p_person_id AND
615: course_cd = p_course_cd AND
609: -- check if susa already exists and if so update it
610: CURSOR c_exists_susa(cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE ,
611: cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE ) IS
612: SELECT sequence_number , student_confirmed_ind
613: FROM IGS_AS_SU_SETATMPT
614: WHERE person_id = p_person_id AND
615: course_cd = p_course_cd AND
616: unit_set_cd = cp_unit_set_cd AND
617: us_version_number = cp_us_version_number ;
616: unit_set_cd = cp_unit_set_cd AND
617: us_version_number = cp_us_version_number ;
618: c_exists_susa_rec c_exists_susa%ROWTYPE ;
619:
620: CURSOR c_susa_upd ( cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE ,
621: cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE ,
622: cp_sequence_number igs_as_su_setatmpt.sequence_number%TYPE ) IS
623: SELECT rowid,IGS_AS_SU_SETATMPT.*
624: FROM IGS_AS_SU_SETATMPT
617: us_version_number = cp_us_version_number ;
618: c_exists_susa_rec c_exists_susa%ROWTYPE ;
619:
620: CURSOR c_susa_upd ( cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE ,
621: cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE ,
622: cp_sequence_number igs_as_su_setatmpt.sequence_number%TYPE ) IS
623: SELECT rowid,IGS_AS_SU_SETATMPT.*
624: FROM IGS_AS_SU_SETATMPT
625: WHERE person_id = p_person_id AND
618: c_exists_susa_rec c_exists_susa%ROWTYPE ;
619:
620: CURSOR c_susa_upd ( cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE ,
621: cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE ,
622: cp_sequence_number igs_as_su_setatmpt.sequence_number%TYPE ) IS
623: SELECT rowid,IGS_AS_SU_SETATMPT.*
624: FROM IGS_AS_SU_SETATMPT
625: WHERE person_id = p_person_id AND
626: course_cd = p_course_cd AND
619:
620: CURSOR c_susa_upd ( cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE ,
621: cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE ,
622: cp_sequence_number igs_as_su_setatmpt.sequence_number%TYPE ) IS
623: SELECT rowid,IGS_AS_SU_SETATMPT.*
624: FROM IGS_AS_SU_SETATMPT
625: WHERE person_id = p_person_id AND
626: course_cd = p_course_cd AND
627: unit_set_cd = cp_unit_set_cd AND
620: CURSOR c_susa_upd ( cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE ,
621: cp_us_version_number igs_as_su_setatmpt.us_version_number%TYPE ,
622: cp_sequence_number igs_as_su_setatmpt.sequence_number%TYPE ) IS
623: SELECT rowid,IGS_AS_SU_SETATMPT.*
624: FROM IGS_AS_SU_SETATMPT
625: WHERE person_id = p_person_id AND
626: course_cd = p_course_cd AND
627: unit_set_cd = cp_unit_set_cd AND
628: us_version_number = cp_us_version_number AND
2111: RETURN BOOLEAN
2112: AS
2113:
2114: BEGIN -- enrpl_copy_adm_unit_sets
2115: -- Create any required IGS_AS_SU_SETATMPT details
2116: DECLARE
2117: -- Bug#2347141
2118: -- This cursor got modified to retrive the all the values of the columns which
2119: -- helps in case of updation
2118: -- This cursor got modified to retrive the all the values of the columns which
2119: -- helps in case of updation
2120: CURSOR c_susa IS
2121: SELECT susa.*,susa.rowid
2122: FROM IGS_AS_SU_SETATMPT susa
2123: WHERE susa.person_id = p_person_id AND
2124: susa.course_cd = p_course_cd AND
2125: susa.unit_set_cd = v_acaiv_rec.unit_set_cd AND
2126: susa.us_version_number = v_acaiv_rec.us_version_number AND
2153: -- And also the variables v_confirmed_ind, v_selection_dt added newly.
2154: v_susa_rec c_susa%ROWTYPE;
2155: v_us_rec c_us%ROWTYPE;
2156: v_spa_rec c_spa%ROWTYPE;
2157: v_authorised_person_id IGS_AS_SU_SETATMPT.authorised_person_id%TYPE;
2158: v_authorised_on IGS_AS_SU_SETATMPT.authorised_on%TYPE;
2159:
2160: v_confirmed_ind IGS_AS_SU_SETATMPT.student_confirmed_ind%TYPE;
2161: v_selection_dt IGS_AS_SU_SETATMPT.selection_dt%TYPE;
2154: v_susa_rec c_susa%ROWTYPE;
2155: v_us_rec c_us%ROWTYPE;
2156: v_spa_rec c_spa%ROWTYPE;
2157: v_authorised_person_id IGS_AS_SU_SETATMPT.authorised_person_id%TYPE;
2158: v_authorised_on IGS_AS_SU_SETATMPT.authorised_on%TYPE;
2159:
2160: v_confirmed_ind IGS_AS_SU_SETATMPT.student_confirmed_ind%TYPE;
2161: v_selection_dt IGS_AS_SU_SETATMPT.selection_dt%TYPE;
2162:
2156: v_spa_rec c_spa%ROWTYPE;
2157: v_authorised_person_id IGS_AS_SU_SETATMPT.authorised_person_id%TYPE;
2158: v_authorised_on IGS_AS_SU_SETATMPT.authorised_on%TYPE;
2159:
2160: v_confirmed_ind IGS_AS_SU_SETATMPT.student_confirmed_ind%TYPE;
2161: v_selection_dt IGS_AS_SU_SETATMPT.selection_dt%TYPE;
2162:
2163:
2164: BEGIN
2157: v_authorised_person_id IGS_AS_SU_SETATMPT.authorised_person_id%TYPE;
2158: v_authorised_on IGS_AS_SU_SETATMPT.authorised_on%TYPE;
2159:
2160: v_confirmed_ind IGS_AS_SU_SETATMPT.student_confirmed_ind%TYPE;
2161: v_selection_dt IGS_AS_SU_SETATMPT.selection_dt%TYPE;
2162:
2163:
2164: BEGIN
2165:
2236:
2237: -- Bug#2347141
2238: -- Its updating the record in the Unit Set Attempt table with student conformed indicator as 'Y'
2239: -- and the Selection date as the value in the local variable.
2240: IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW(
2241: X_ROWID => v_susa_rec.rowid,
2242: X_PERSON_ID => v_susa_rec.person_id ,
2243: X_COURSE_CD => v_susa_rec.course_cd ,
2244: X_UNIT_SET_CD => v_susa_rec.unit_set_cd ,
3544: RETURN FALSE;
3545: END IF ;
3546:
3547: -- set the current year of program to completed
3548: IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
3549: X_ROWID => c_susa_upd_rec.rowid,
3550: X_PERSON_ID => c_susa_upd_rec.person_id ,
3551: X_COURSE_CD => c_susa_upd_rec.course_cd ,
3552: X_UNIT_SET_CD => c_susa_upd_rec.unit_set_cd ,
3726: END IF;
3727: RETURN FALSE;
3728: END IF;
3729:
3730: IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
3731: X_ROWID => c_susa_upd_rec.rowid,
3732: X_PERSON_ID => c_susa_upd_rec.person_id,
3733: X_COURSE_CD => c_susa_upd_rec.course_cd ,
3734: X_UNIT_SET_CD => c_susa_upd_rec.unit_set_cd ,
4057: )
4058: RETURN boolean AS
4059: /* HISTORY
4060: WHO WHEN WHAT
4061: bdeviset 29-JUL-2004 Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW in a check is
4062: made to see that their is no overlapping of selection,completion and
4063: end dates for any two unit sets by calling check_usa_overlap.If it returns
4064: false log entry is made and the insert or update is not carried out for bug 3149133.
4065: ayedubat 4-JUN-2002 Changed the Code of YOP for Unit Set pre-enrollment before
4200: v_acaiv_rec c_acaiv%ROWTYPE;
4201:
4202: CURSOR c_susa IS
4203: SELECT susa.unit_set_cd
4204: FROM IGS_AS_SU_SETATMPT susa
4205: WHERE susa.person_id = p_person_id AND
4206: susa.course_cd = p_course_cd AND
4207: susa.student_confirmed_ind = 'Y' AND
4208: susa.end_dt IS NULL;
4225: v_parm_added_ind VARCHAR2(1);
4226: cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
4227: l_pos_count NUMBER(1) := 0;
4228: l_rowid VARCHAR2(25);
4229: l_seqval igs_as_su_setatmpt.sequence_number%TYPE ;
4230:
4231: CURSOR c_pos_unit_sets( cp_version_number igs_ps_pat_of_study.version_number%TYPE) IS
4232: SELECT unit_set_cd
4233: FROM IGS_PS_PAT_OF_STUDY pos
4235: version_number = cp_version_number AND
4236: cal_type = p_acad_cal_type AND
4237: unit_set_cd IN
4238: ( SELECT susa.unit_set_cd
4239: FROM IGS_AS_SU_SETATMPT susa
4240: WHERE susa.person_id = p_person_id AND
4241: susa.course_cd = pos.course_cd AND
4242: susa.student_confirmed_ind = 'Y' AND
4243: susa.end_dt IS NULL);
4243: susa.end_dt IS NULL);
4244:
4245: -- checks the eligibility of the student to be moved to the next year of program (unit set)
4246: -- by checking if there is any outcome preventing the progress of the student program attempt
4247: CURSOR c_prog_outcome(cp_select_dt igs_as_su_setatmpt.selection_dt%TYPE) IS
4248: SELECT pou.decision_dt, pout.s_progression_outcome_type
4249: FROM igs_pr_stdnt_pr_ou_all pou , igs_pr_ou_type pout
4250: WHERE pou.person_id = p_person_id AND
4251: pou.course_cd = p_course_cd AND
4259:
4260: -- get the currently active unit set for the person course attempt
4261: CURSOR c_active_us IS
4262: SELECT susa.*
4263: FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
4264: WHERE susa.person_id = p_person_id AND
4265: susa.course_cd = p_course_cd AND
4266: susa.selection_dt IS NOT NULL AND
4267: susa.end_dt IS NULL AND
4298: -- find the last active unit set for the person program
4299: CURSOR c_last_us IS
4300: SELECT susa.unit_set_cd, susa.us_version_number ,susa.sequence_number ,susa.rqrmnts_complete_dt
4301: , susa.selection_dt
4302: FROM igs_as_su_setatmpt susa , igs_en_unit_set us , igs_en_unit_set_cat usc
4303: WHERE susa.person_id = p_person_id AND
4304: susa.course_cd = p_course_cd AND
4305: susa.rqrmnts_complete_dt IS NOT NULL AND
4306: susa.unit_set_cd = us.unit_set_cd AND
5130: IF c_chk_census_dt%FOUND THEN
5131:
5132: DECLARE
5133: CURSOR c_susa_upd IS
5134: SELECT rowid,IGS_AS_SU_SETATMPT.*
5135: FROM IGS_AS_SU_SETATMPT
5136: WHERE person_id = c_active_us_rec.person_id AND
5137: course_cd = c_active_us_rec.course_cd AND
5138: unit_set_cd = c_active_us_rec.unit_set_cd AND
5131:
5132: DECLARE
5133: CURSOR c_susa_upd IS
5134: SELECT rowid,IGS_AS_SU_SETATMPT.*
5135: FROM IGS_AS_SU_SETATMPT
5136: WHERE person_id = c_active_us_rec.person_id AND
5137: course_cd = c_active_us_rec.course_cd AND
5138: unit_set_cd = c_active_us_rec.unit_set_cd AND
5139: us_version_number = c_active_us_rec.us_version_number AND
5180: RETURN FALSE;
5181: END IF ;
5182:
5183: -- set the current year of program to completed
5184: IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
5185: X_ROWID => c_susa_upd_rec.rowid,
5186: X_PERSON_ID => c_susa_upd_rec.person_id ,
5187: X_COURSE_CD => c_susa_upd_rec.course_cd ,
5188: X_UNIT_SET_CD => c_susa_upd_rec.unit_set_cd ,
6663: gv_other_detail VARCHAR2(255);
6664: BEGIN -- enrp_ins_susa_hist
6665: -- Create a history for an IGS_AD_APPL record
6666: DECLARE
6667: v_susa_rec IGS_AS_SU_SETATMPT_H%ROWTYPE;
6668: v_create_history BOOLEAN := FALSE;
6669: BEGIN
6670: -- Create a history for a IGS_AS_SU_SETATMPT record.
6671: -- Check if any of the non-primary key fields have been changed
6666: DECLARE
6667: v_susa_rec IGS_AS_SU_SETATMPT_H%ROWTYPE;
6668: v_create_history BOOLEAN := FALSE;
6669: BEGIN
6670: -- Create a history for a IGS_AS_SU_SETATMPT record.
6671: -- Check if any of the non-primary key fields have been changed
6672: -- and set the flag v_create_history to indicate so.
6673: IF p_new_us_version_number <> p_old_us_version_number THEN
6674: v_susa_rec.us_version_number := p_old_us_version_number;
6762: DECLARE
6763: l_rowid VARCHAR2(25);
6764: l_org_id NUMBER := igs_ge_gen_003.get_org_id;
6765: BEGIN
6766: IGS_AS_SU_SETATMPT_H_PKG.INSERT_ROW (
6767: x_rowid => l_rowid,
6768: x_person_id => v_susa_rec.person_id,
6769: x_course_cd => v_susa_rec.course_cd,
6770: x_unit_set_cd => v_susa_rec.unit_set_cd,
6813: RETURN BOOLEAN AS
6814: INVALID_PARAM EXCEPTION ;
6815:
6816: BEGIN -- enrp_ins_susa_trnsfr
6817: -- Transfer a IGS_AS_SU_SETATMPT record as part of a IGS_PS_COURSE transfer.
6818: DECLARE
6819:
6820: CURSOR c_susa IS
6821: SELECT susa.us_version_number,
6831: susa.override_title,
6832: susa.rqrmnts_complete_ind,
6833: susa.rqrmnts_complete_dt,
6834: susa.s_completed_source_type
6835: FROM IGS_AS_SU_SETATMPT susa
6836: WHERE susa.person_id = p_person_id AND
6837: susa.course_cd = p_course_cd AND
6838: susa.unit_set_cd = p_unit_set_cd AND
6839: susa.us_version_number = p_us_version_number AND
6839: susa.us_version_number = p_us_version_number AND
6840: susa.sequence_number = p_sequence_number;
6841: CURSOR c_susa1 IS
6842: SELECT 'x'
6843: FROM IGS_AS_SU_SETATMPT susa
6844: WHERE susa.person_id = p_person_id AND
6845: susa.course_cd = p_transfer_course_cd AND
6846: susa.unit_set_cd = p_unit_set_cd AND
6847: susa.us_version_number = p_us_version_number;
6844: WHERE susa.person_id = p_person_id AND
6845: susa.course_cd = p_transfer_course_cd AND
6846: susa.unit_set_cd = p_unit_set_cd AND
6847: susa.us_version_number = p_us_version_number;
6848: v_primary_set_ind IGS_AS_SU_SETATMPT.primary_set_ind%TYPE ;
6849: v_ret BOOLEAN ;
6850: v_message_name VARCHAR2(2000);
6851: v_message_text VARCHAR2(2000);
6852: v_c_susa_rec c_susa%ROWTYPE;
6876: p_message_name := null;
6877: RETURN TRUE;
6878: END IF;
6879: CLOSE c_susa;
6880: -- Determine if IGS_AS_SU_SETATMPT is to be transferred
6881: -- as a primary IGS_PS_UNIT set.
6882: IF p_primary_set_ind = 'Y' THEN
6883: v_primary_set_ind := 'Y';
6884: ELSE
6883: v_primary_set_ind := 'Y';
6884: ELSE
6885: v_primary_set_ind := 'N';
6886: END IF;
6887: -- Perform validations on the IGS_AS_SU_SETATMPT record to be created.
6888: -- Call enrp_val_susa in IGS_EN_VAL_SUSA package to perform validations.
6889: v_ret := IGS_EN_VAL_SUSA.enrp_val_susa (
6890: p_person_id,
6891: p_transfer_course_cd,
6918: -- Check that the record to be inserted doesn't already exist.
6919: OPEN c_susa1;
6920: FETCH c_susa1 INTO v_c_susa1_found;
6921: IF (c_susa1%FOUND) THEN
6922: -- IGS_AS_SU_SETATMPT already exists.
6923: CLOSE c_susa1;
6924: p_message_name := 'IGS_EN_SUA_SET_ATT_TRNS_EXIST';
6925: RETURN FALSE;
6926: END IF;
6928: -- Transfer the IGS_EN_SU_ATTEMPT.
6929: DECLARE
6930: l_rowid VARCHAR2(25) := NULL;
6931: BEGIN
6932: IGS_AS_SU_SETATMPT_PKG.INSERT_ROW (
6933: x_rowid => l_rowid,
6934: x_person_id => p_person_id,
6935: x_course_cd => p_transfer_course_cd,
6936: x_unit_set_cd => p_unit_set_cd,
7935:
7936: ------------------------------------------------------------------------------------------------
7937: --Change History:
7938: --Who When What
7939: --bdeviset 29-JUL-2004 Added p_log_creation_dt as parameter.Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW
7940: -- a check is made to see that their is no overlapping of selection,completion and
7941: -- end dates for any two unit sets by calling check_usa_overlap.If it returns
7942: -- false log entry is made and the insert or update is not carried out for bug 3149133.
7943: ------------------------------------------------------------------------------------------------
7940: -- a check is made to see that their is no overlapping of selection,completion and
7941: -- end dates for any two unit sets by calling check_usa_overlap.If it returns
7942: -- false log entry is made and the insert or update is not carried out for bug 3149133.
7943: ------------------------------------------------------------------------------------------------
7944: CURSOR c_acad_us (cp_new_admin_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE) IS
7945: SELECT usm.stream_unit_set_Cd
7946: FROM igs_en_unit_set_map usm,
7947: igs_ps_us_prenr_cfg upc
7948: WHERE upc.unit_set_cd = cp_new_admin_unit_set_cd
7961: coous.expiry_dt IS NULL AND
7962: coous.unit_set_status = uss.unit_set_status AND
7963: uss.s_unit_set_status = 'ACTIVE' ;
7964:
7965: CURSOR c_susa_exists (cp_stream_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
7966: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE,
7967: cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
7968: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
7969: SELECT susa.*, susa.rowid
7962: coous.unit_set_status = uss.unit_set_status AND
7963: uss.s_unit_set_status = 'ACTIVE' ;
7964:
7965: CURSOR c_susa_exists (cp_stream_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
7966: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE,
7967: cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
7968: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
7969: SELECT susa.*, susa.rowid
7970: FROM igs_as_su_setatmpt susa
7963: uss.s_unit_set_status = 'ACTIVE' ;
7964:
7965: CURSOR c_susa_exists (cp_stream_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
7966: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE,
7967: cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
7968: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
7969: SELECT susa.*, susa.rowid
7970: FROM igs_as_su_setatmpt susa
7971: WHERE susa.unit_set_cd = cp_stream_unit_set_cd
7964:
7965: CURSOR c_susa_exists (cp_stream_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
7966: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE,
7967: cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
7968: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
7969: SELECT susa.*, susa.rowid
7970: FROM igs_as_su_setatmpt susa
7971: WHERE susa.unit_set_cd = cp_stream_unit_set_cd
7972: AND susa.us_version_number = cp_us_version_number
7966: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE,
7967: cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
7968: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
7969: SELECT susa.*, susa.rowid
7970: FROM igs_as_su_setatmpt susa
7971: WHERE susa.unit_set_cd = cp_stream_unit_set_cd
7972: AND susa.us_version_number = cp_us_version_number
7973: AND susa.person_id = cp_person_id
7974: AND susa.course_cd = cp_course_cd
7976: ORDER BY susa.selection_dt desc;
7977:
7978: vc_susa_upd_rec c_susa_exists%ROWTYPE;
7979: v_us_version_number igs_en_unit_set.version_number%TYPE;
7980: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
7981: vl_sequence_val igs_as_su_setatmpt.SEQUENCE_NUMBER%TYPE;
7982: vl_rowid VARCHAR2(25);
7983: v_confirmed_ind igs_as_su_setatmpt.student_confirmed_ind%TYPE;
7984: p_warn_level varchar2(5);
7977:
7978: vc_susa_upd_rec c_susa_exists%ROWTYPE;
7979: v_us_version_number igs_en_unit_set.version_number%TYPE;
7980: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
7981: vl_sequence_val igs_as_su_setatmpt.SEQUENCE_NUMBER%TYPE;
7982: vl_rowid VARCHAR2(25);
7983: v_confirmed_ind igs_as_su_setatmpt.student_confirmed_ind%TYPE;
7984: p_warn_level varchar2(5);
7985: cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
7979: v_us_version_number igs_en_unit_set.version_number%TYPE;
7980: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
7981: vl_sequence_val igs_as_su_setatmpt.SEQUENCE_NUMBER%TYPE;
7982: vl_rowid VARCHAR2(25);
7983: v_confirmed_ind igs_as_su_setatmpt.student_confirmed_ind%TYPE;
7984: p_warn_level varchar2(5);
7985: cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
7986: cst_error CONSTANT VARCHAR2(5) := 'ERROR';
7987:
8008: END IF;
8009:
8010: IF c_susa_exists%NOTFOUND THEN
8011:
8012: SELECT IGS_AS_SU_SETATMPT_SEQ_NUM_S.NEXTVAL INTO vl_sequence_val FROM dual;
8013:
8014: IF igs_en_gen_legacy.check_usa_overlap(
8015: p_person_id,
8016: p_course_cd,
8035: END IF;
8036: RETURN FALSE;
8037: END IF ;
8038:
8039: IGS_AS_SU_SETATMPT_PKG.INSERT_ROW (
8040: X_ROWID => vl_rowid,
8041: X_PERSON_ID => p_person_id ,
8042: X_COURSE_CD => p_course_cd ,
8043: X_UNIT_SET_CD => vc_acad_us_rec.stream_unit_set_cd ,
8125: END IF;
8126: RETURN FALSE;
8127: END IF ;
8128:
8129: IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
8130: X_ROWID => vc_susa_upd_rec.rowid,
8131: X_PERSON_ID => vc_susa_upd_rec.person_id ,
8132: X_COURSE_CD => vc_susa_upd_rec.course_cd ,
8133: X_UNIT_SET_CD => vc_susa_upd_rec.unit_set_cd ,
8203:
8204: ------------------------------------------------------------------------------------------------
8205: --Change History:
8206: --Who When What
8207: --bdeviset 29-JUL-2004 Added p_log_creation_dt as parameter.Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW
8208: -- a check is made to see that their is no overlapping of selection,completion and
8209: -- end dates for any two unit sets by calling check_usa_overlap.If it returns
8210: -- false log entry is made and the insert or update is not carried out for bug 3149133.
8211: ------------------------------------------------------------------------------------------------
8213: p_warn_level varchar2(5);
8214: cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
8215: cst_error CONSTANT VARCHAR2(5) := 'ERROR';
8216: l_rowid VARCHAR2(25);
8217: l_seqval IGS_AS_SU_SETATMPT.SEQUENCE_NUMBER%TYPE;
8218: v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
8219: v_confirmed_ind IGS_AS_SU_SETATMPT.STUDENT_CONFIRMED_IND%TYPE;
8220: CURSOR c_next_susa_exists (cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8221: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE,
8214: cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
8215: cst_error CONSTANT VARCHAR2(5) := 'ERROR';
8216: l_rowid VARCHAR2(25);
8217: l_seqval IGS_AS_SU_SETATMPT.SEQUENCE_NUMBER%TYPE;
8218: v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
8219: v_confirmed_ind IGS_AS_SU_SETATMPT.STUDENT_CONFIRMED_IND%TYPE;
8220: CURSOR c_next_susa_exists (cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8221: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE,
8222: cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8215: cst_error CONSTANT VARCHAR2(5) := 'ERROR';
8216: l_rowid VARCHAR2(25);
8217: l_seqval IGS_AS_SU_SETATMPT.SEQUENCE_NUMBER%TYPE;
8218: v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
8219: v_confirmed_ind IGS_AS_SU_SETATMPT.STUDENT_CONFIRMED_IND%TYPE;
8220: CURSOR c_next_susa_exists (cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8221: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE,
8222: cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8223: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE) IS
8216: l_rowid VARCHAR2(25);
8217: l_seqval IGS_AS_SU_SETATMPT.SEQUENCE_NUMBER%TYPE;
8218: v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
8219: v_confirmed_ind IGS_AS_SU_SETATMPT.STUDENT_CONFIRMED_IND%TYPE;
8220: CURSOR c_next_susa_exists (cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8221: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE,
8222: cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8223: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE) IS
8224: SELECT susa.*,susa.rowid
8217: l_seqval IGS_AS_SU_SETATMPT.SEQUENCE_NUMBER%TYPE;
8218: v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
8219: v_confirmed_ind IGS_AS_SU_SETATMPT.STUDENT_CONFIRMED_IND%TYPE;
8220: CURSOR c_next_susa_exists (cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8221: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE,
8222: cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8223: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE) IS
8224: SELECT susa.*,susa.rowid
8225: FROM IGS_AS_SU_SETATMPT susa
8218: v_selection_dt IGS_AS_SU_SETATMPT.SELECTION_DT%TYPE;
8219: v_confirmed_ind IGS_AS_SU_SETATMPT.STUDENT_CONFIRMED_IND%TYPE;
8220: CURSOR c_next_susa_exists (cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8221: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE,
8222: cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8223: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE) IS
8224: SELECT susa.*,susa.rowid
8225: FROM IGS_AS_SU_SETATMPT susa
8226: WHERE susa.person_id = cp_person_id AND
8219: v_confirmed_ind IGS_AS_SU_SETATMPT.STUDENT_CONFIRMED_IND%TYPE;
8220: CURSOR c_next_susa_exists (cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8221: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE,
8222: cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8223: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE) IS
8224: SELECT susa.*,susa.rowid
8225: FROM IGS_AS_SU_SETATMPT susa
8226: WHERE susa.person_id = cp_person_id AND
8227: susa.course_cd = cp_course_cd AND
8221: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE,
8222: cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8223: cp_us_version_number IGS_AS_SU_SETATMPT.US_VERSION_NUMBER%TYPE) IS
8224: SELECT susa.*,susa.rowid
8225: FROM IGS_AS_SU_SETATMPT susa
8226: WHERE susa.person_id = cp_person_id AND
8227: susa.course_cd = cp_course_cd AND
8228: susa.unit_set_cd = cp_unit_set_cd AND
8229: susa.us_version_number = cp_us_version_number AND
8286: END IF;
8287: RETURN FALSE;
8288: END IF ;
8289:
8290: IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
8291: X_ROWID => v_susa_rec.rowid,
8292: X_PERSON_ID => v_susa_rec.person_id ,
8293: X_COURSE_CD => v_susa_rec.course_cd ,
8294: X_UNIT_SET_CD => v_susa_rec.unit_set_cd ,
8334: );
8335: END IF;
8336: ELSE
8337: CLOSE c_next_susa_exists;
8338: SELECT IGS_AS_SU_SETATMPT_SEQ_NUM_S.NEXTVAL
8339: INTO l_seqval
8340: FROM dual;
8341:
8342: p_seqval := l_seqval;
8372: RETURN FALSE;
8373: END IF ;
8374:
8375:
8376: IGS_AS_SU_SETATMPT_PKG.INSERT_ROW (
8377: x_rowid => l_rowid,
8378: x_person_id => p_person_id,
8379: x_course_cd => p_course_cd,
8380: x_unit_set_cd => p_unit_set_cd,
8437:
8438: ------------------------------------------------------------------------------------------------
8439: --Change History:
8440: --Who When What
8441: --bdeviset 29-JUL-2004 Added p_log_creation_dt as parameter.Before calling IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW/INSERT_ROW
8442: -- a check is made to see that their is no overlapping of selection,completion and
8443: -- end dates for any two unit sets by calling check_usa_overlap.If it returns
8444: -- false log entry is made and the insert or update is not carried out for bug 3149133.
8445: ------------------------------------------------------------------------------------------------
8443: -- end dates for any two unit sets by calling check_usa_overlap.If it returns
8444: -- false log entry is made and the insert or update is not carried out for bug 3149133.
8445: ------------------------------------------------------------------------------------------------
8446:
8447: CURSOR c_acad_us (cp_old_admin_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE) IS
8448: SELECT usm.stream_unit_set_Cd
8449: FROM igs_en_unit_set_map usm,
8450: igs_ps_us_prenr_cfg upc
8451: WHERE upc.unit_set_cd = cp_old_admin_unit_set_cd
8451: WHERE upc.unit_set_cd = cp_old_admin_unit_set_cd
8452: AND usm.mapping_set_cd = upc.mapping_set_cd
8453: AND usm.sequence_no = upc.sequence_no;
8454:
8455: CURSOR c_susa_upd (cp_stream_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8456: cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8457: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
8458: SELECT susa.rowid, susa.*
8459: FROM igs_as_su_setatmpt susa
8452: AND usm.mapping_set_cd = upc.mapping_set_cd
8453: AND usm.sequence_no = upc.sequence_no;
8454:
8455: CURSOR c_susa_upd (cp_stream_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8456: cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8457: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
8458: SELECT susa.rowid, susa.*
8459: FROM igs_as_su_setatmpt susa
8460: WHERE susa.unit_set_cd = cp_stream_unit_set_cd
8453: AND usm.sequence_no = upc.sequence_no;
8454:
8455: CURSOR c_susa_upd (cp_stream_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8456: cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8457: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
8458: SELECT susa.rowid, susa.*
8459: FROM igs_as_su_setatmpt susa
8460: WHERE susa.unit_set_cd = cp_stream_unit_set_cd
8461: AND susa.person_id = cp_person_id
8455: CURSOR c_susa_upd (cp_stream_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
8456: cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
8457: cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
8458: SELECT susa.rowid, susa.*
8459: FROM igs_as_su_setatmpt susa
8460: WHERE susa.unit_set_cd = cp_stream_unit_set_cd
8461: AND susa.person_id = cp_person_id
8462: AND susa.course_cd = cp_course_cd
8463: AND susa.end_dt IS NULL
8463: AND susa.end_dt IS NULL
8464: order by selection_dt desc;
8465:
8466: vc_susa_upd_rec c_susa_upd%ROWTYPE;
8467: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
8468: v_confirmed_ind igs_as_su_setatmpt.student_confirmed_ind%TYPE;
8469: v_rqrmnts_complete_ind igs_as_su_setatmpt.rqrmnts_complete_ind%TYPE;
8470: v_rqrmnts_complete_dt igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE;
8471: p_warn_level varchar2(5);
8464: order by selection_dt desc;
8465:
8466: vc_susa_upd_rec c_susa_upd%ROWTYPE;
8467: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
8468: v_confirmed_ind igs_as_su_setatmpt.student_confirmed_ind%TYPE;
8469: v_rqrmnts_complete_ind igs_as_su_setatmpt.rqrmnts_complete_ind%TYPE;
8470: v_rqrmnts_complete_dt igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE;
8471: p_warn_level varchar2(5);
8472: cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
8465:
8466: vc_susa_upd_rec c_susa_upd%ROWTYPE;
8467: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
8468: v_confirmed_ind igs_as_su_setatmpt.student_confirmed_ind%TYPE;
8469: v_rqrmnts_complete_ind igs_as_su_setatmpt.rqrmnts_complete_ind%TYPE;
8470: v_rqrmnts_complete_dt igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE;
8471: p_warn_level varchar2(5);
8472: cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
8473: cst_error CONSTANT VARCHAR2(5) := 'ERROR';
8466: vc_susa_upd_rec c_susa_upd%ROWTYPE;
8467: v_selection_dt igs_as_su_setatmpt.selection_dt%TYPE;
8468: v_confirmed_ind igs_as_su_setatmpt.student_confirmed_ind%TYPE;
8469: v_rqrmnts_complete_ind igs_as_su_setatmpt.rqrmnts_complete_ind%TYPE;
8470: v_rqrmnts_complete_dt igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE;
8471: p_warn_level varchar2(5);
8472: cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
8473: cst_error CONSTANT VARCHAR2(5) := 'ERROR';
8474:
8541: RETURN FALSE;
8542: END IF ;
8543:
8544:
8545: IGS_AS_SU_SETATMPT_PKG.UPDATE_ROW (
8546: X_ROWID => vc_susa_upd_rec.rowid,
8547: X_PERSON_ID => vc_susa_upd_rec.person_id ,
8548: X_COURSE_CD => vc_susa_upd_rec.course_cd ,
8549: X_UNIT_SET_CD => vc_susa_upd_rec.unit_set_cd ,