[Home] [Help]
43: l_org_id NUMBER(15);
44: BEGIN
45: -- grdp_ins_graduand
46: -- This module is used to identify potential graduands and to create
47: -- IGS_GR_GRADUAND records if they haven't already been created.
48: igs_ge_gen_003.set_org_id(p_org_id);
49: --Block for Parameter Validation/Splitting of Parameters
50: retcode:=0;
51:
178: spaa.AWARD_CD = p_award_cd )
179: ORDER BY sca.person_id;
180:
181: CURSOR c_gr (
182: cp_person_id igs_gr_graduand.person_id%TYPE,
183: cp_course_cd igs_gr_graduand.course_cd%TYPE,
184: cp_award_cd igs_en_spa_awd_aim.award_cd%TYPE) IS
185: SELECT 'x'
186: FROM igs_gr_graduand gr,
179: ORDER BY sca.person_id;
180:
181: CURSOR c_gr (
182: cp_person_id igs_gr_graduand.person_id%TYPE,
183: cp_course_cd igs_gr_graduand.course_cd%TYPE,
184: cp_award_cd igs_en_spa_awd_aim.award_cd%TYPE) IS
185: SELECT 'x'
186: FROM igs_gr_graduand gr,
187: IGS_GR_STAT gst
182: cp_person_id igs_gr_graduand.person_id%TYPE,
183: cp_course_cd igs_gr_graduand.course_cd%TYPE,
184: cp_award_cd igs_en_spa_awd_aim.award_cd%TYPE) IS
185: SELECT 'x'
186: FROM igs_gr_graduand gr,
187: IGS_GR_STAT gst
188: WHERE gr.person_id = cp_person_id AND
189: gr.course_cd = cp_course_cd AND
190: gr.award_cd = cp_award_cd AND
239: cst_waiting,
240: cst_approved);
241: v_gas_rec c_gas%ROWTYPE;
242: BEGIN
243: -- Validate the potential IGS_GR_GRADUAND status value
244: OPEN c_gst(
245: p_potential_graduand_status,
246: cst_potential);
247: FETCH c_gst INTO v_gst_rec;
258: --App_Exception.Raise_Exception;
259: FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
260: RETURN;
261: END IF;
262: -- Validate the eligible IGS_GR_GRADUAND status value
263: OPEN c_gst(
264: p_eligible_graduand_status,
265: cst_eligible);
266: FETCH c_gst INTO v_gst_rec;
277: --App_Exception.Raise_Exception;
278: FND_FILE.PUT_LINE(FND_FILE.LOG,fnd_message.get); --Added as per bug# 2690151
279: RETURN;
280: END IF;
281: -- Validate the IGS_GR_GRADUAND approval status value
282: OPEN c_gas;
283: FETCH c_gas INTO v_gas_rec;
284: IF c_gas%NOTFOUND THEN
285: CLOSE c_gas;
344: AND ((spaaa.end_dt is NULL OR spaaa.complete_ind = 'Y')
345: OR(spaaa.conferral_date IS NOT NULL))
346: AND NOT EXISTS (
347: SELECT 'x'
348: FROM IGS_GR_GRADUAND gr
349: WHERE gr.person_id = p_person_id
350: AND gr.course_cd = p_course_cd
351: AND gr.grd_cal_type = p_grd_cal_type
352: AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
366: AND spaaa.course_cd = p_course_cd
367: AND spaaa.award_cd = p_award_cd
368: AND NOT EXISTS (
369: SELECT 'x'
370: FROM igs_gr_graduand gr
371: WHERE gr.person_id = p_person_id
372: AND gr.course_cd = p_course_cd
373: AND gr.grd_cal_type = p_grd_cal_type
374: AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
424: v_graduand_status := p_potential_graduand_status;
425: END IF;
426: --
427: -- To change the CREATE_DT value by one second to pass the primary key
428: -- validation for the IGS_GR_GRADUAND table, which is the combination of the PERSON_ID and CREATE_DT.
429: IF p_person_id = v_person_id THEN
430: dbms_lock.sleep(1);
431: ELSE
432: v_person_id := p_person_id;
465: -- Because Conferral Date should not be mentioned with the 'Graduand Status' of 'ELIGIBLE' or 'POTANTIAL'.
466: v_approval_status := p_graduand_appr_status;
467: END IF;
468: --
469: -- Insert IGS_GR_GRADUAND record
470: --
471: DECLARE
472: lv_rowid VARCHAR2(25);
473: lv_create_dt DATE DEFAULT NULL;
473: lv_create_dt DATE DEFAULT NULL;
474: BEGIN
475: l_org_id := igs_ge_gen_003.get_org_id;
476: l_hold := 'N';
477: IGS_GR_GRADUAND_PKG.INSERT_ROW(
478: X_ROWID => lv_rowid,
479: X_PERSON_ID => p_person_id,
480: X_CREATE_DT => lv_create_dt,
481: X_GRD_CAL_TYPE => p_grd_cal_type,
595: AND spaaa.course_cd = p_course_cd
596: AND spaaa.award_cd = p_award_cd
597: AND EXISTS (
598: SELECT 'x'
599: FROM igs_gr_graduand gr
600: WHERE gr.person_id = p_person_id
601: AND gr.course_cd = p_course_cd
602: AND gr.grd_cal_type = p_grd_cal_type
603: AND gr.grd_ci_sequence_number = p_grd_ci_sequence_number
722:
723: -- 2. Find Potential Graduands
724: -- establish the ceremony rounds to process within.
725: -- get the completion period to target potential graduands.
726: -- get the target potential IGS_GR_GRADUAND details.
727: l_log_person_id := 'NULL';
728: l_chk_hold := 'NULL';
729:
730: FOR v_crd_sca_rec IN c_crd_sca LOOP
771: END IF;
772: END IF;
773: IF l_chk_hold = 'NULL' OR
774: l_chk_hold <> v_crd_sca_rec.person_id||'-'||v_crd_sca_rec.course_cd THEN
775: -- Do 2.1 Create New IGS_GR_GRADUAND
776: grdpl_ins_new_graduand(
777: v_crd_sca_rec.person_id,
778: v_crd_sca_rec.course_cd,
779: v_crd_sca_rec.version_number,
809: END grdp_ins_graduand;
810:
811:
812: PROCEDURE grdp_ins_gr_hist(
813: p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
814: p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
815: p_old_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
816: p_new_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
817: p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
810:
811:
812: PROCEDURE grdp_ins_gr_hist(
813: p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
814: p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
815: p_old_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
816: p_new_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
817: p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
818: p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
811:
812: PROCEDURE grdp_ins_gr_hist(
813: p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
814: p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
815: p_old_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
816: p_new_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
817: p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
818: p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
819: p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
812: PROCEDURE grdp_ins_gr_hist(
813: p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
814: p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
815: p_old_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
816: p_new_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
817: p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
818: p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
819: p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
820: p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
813: p_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE ,
814: p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
815: p_old_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
816: p_new_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
817: p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
818: p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
819: p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
820: p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
821: p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
814: p_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE ,
815: p_old_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
816: p_new_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
817: p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
818: p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
819: p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
820: p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
821: p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
822: p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
815: p_old_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
816: p_new_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
817: p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
818: p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
819: p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
820: p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
821: p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
822: p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
823: p_old_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
816: p_new_grd_cal_type IGS_GR_GRADUAND_ALL.grd_cal_type%TYPE ,
817: p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
818: p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
819: p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
820: p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
821: p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
822: p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
823: p_old_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
824: p_new_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
817: p_old_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
818: p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
819: p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
820: p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
821: p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
822: p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
823: p_old_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
824: p_new_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
825: p_old_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
818: p_new_grd_ci_sequence_number IGS_GR_GRADUAND_ALL.grd_ci_sequence_number%TYPE ,
819: p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
820: p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
821: p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
822: p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
823: p_old_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
824: p_new_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
825: p_old_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
826: p_new_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
819: p_old_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
820: p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
821: p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
822: p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
823: p_old_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
824: p_new_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
825: p_old_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
826: p_new_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
827: p_old_honours_level VARCHAR2 DEFAULT NULL,
820: p_new_course_cd IGS_GR_GRADUAND_ALL.course_cd%TYPE ,
821: p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
822: p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
823: p_old_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
824: p_new_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
825: p_old_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
826: p_new_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
827: p_old_honours_level VARCHAR2 DEFAULT NULL,
828: p_new_honours_level VARCHAR2 DEFAULT NULL,
821: p_old_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
822: p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
823: p_old_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
824: p_new_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
825: p_old_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
826: p_new_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
827: p_old_honours_level VARCHAR2 DEFAULT NULL,
828: p_new_honours_level VARCHAR2 DEFAULT NULL,
829: p_old_conferral_dt DATE DEFAULT NULL,
822: p_new_award_course_cd IGS_GR_GRADUAND_ALL.award_course_cd%TYPE ,
823: p_old_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
824: p_new_award_crs_version_number IGS_GR_GRADUAND_ALL.award_crs_version_number%TYPE ,
825: p_old_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
826: p_new_award_cd IGS_GR_GRADUAND_ALL.award_cd%TYPE ,
827: p_old_honours_level VARCHAR2 DEFAULT NULL,
828: p_new_honours_level VARCHAR2 DEFAULT NULL,
829: p_old_conferral_dt DATE DEFAULT NULL,
830: p_new_conferral_dt DATE DEFAULT NULL,
827: p_old_honours_level VARCHAR2 DEFAULT NULL,
828: p_new_honours_level VARCHAR2 DEFAULT NULL,
829: p_old_conferral_dt DATE DEFAULT NULL,
830: p_new_conferral_dt DATE DEFAULT NULL,
831: p_old_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
832: p_new_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
833: p_old_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
834: p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
835: p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
828: p_new_honours_level VARCHAR2 DEFAULT NULL,
829: p_old_conferral_dt DATE DEFAULT NULL,
830: p_new_conferral_dt DATE DEFAULT NULL,
831: p_old_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
832: p_new_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
833: p_old_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
834: p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
835: p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
836: p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
829: p_old_conferral_dt DATE DEFAULT NULL,
830: p_new_conferral_dt DATE DEFAULT NULL,
831: p_old_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
832: p_new_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
833: p_old_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
834: p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
835: p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
836: p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
837: p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
830: p_new_conferral_dt DATE DEFAULT NULL,
831: p_old_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
832: p_new_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
833: p_old_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
834: p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
835: p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
836: p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
837: p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
838: p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
831: p_old_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
832: p_new_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
833: p_old_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
834: p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
835: p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
836: p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
837: p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
838: p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
839: p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
832: p_new_graduand_status IGS_GR_GRADUAND_ALL.graduand_status%TYPE ,
833: p_old_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
834: p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
835: p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
836: p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
837: p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
838: p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
839: p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
840: p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
833: p_old_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
834: p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
835: p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
836: p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
837: p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
838: p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
839: p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
840: p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
841: p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
834: p_new_graduand_appr_status IGS_GR_GRADUAND_ALL.graduand_appr_status%TYPE ,
835: p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
836: p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
837: p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
838: p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
839: p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
840: p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
841: p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
842: p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
835: p_old_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
836: p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
837: p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
838: p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
839: p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
840: p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
841: p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
842: p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
843: p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
836: p_new_s_graduand_type IGS_GR_GRADUAND_ALL.s_graduand_type%TYPE ,
837: p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
838: p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
839: p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
840: p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
841: p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
842: p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
843: p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
844: p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
837: p_old_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
838: p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
839: p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
840: p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
841: p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
842: p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
843: p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
844: p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
845: p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
838: p_new_graduation_name IN IGS_GR_GRADUAND_ALL.graduation_name%TYPE ,
839: p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
840: p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
841: p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
842: p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
843: p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
844: p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
845: p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
846: p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
839: p_old_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
840: p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
841: p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
842: p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
843: p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
844: p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
845: p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
846: p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
847: p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
840: p_new_proxy_award_ind IGS_GR_GRADUAND_ALL.proxy_award_ind%TYPE ,
841: p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
842: p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
843: p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
844: p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
845: p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
846: p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
847: p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
848: p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
841: p_old_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
842: p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
843: p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
844: p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
845: p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
846: p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
847: p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
848: p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
849: p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
842: p_new_proxy_award_person_id IGS_GR_GRADUAND_ALL.proxy_award_person_id%TYPE ,
843: p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
844: p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
845: p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
846: p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
847: p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
848: p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
849: p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
850: p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
843: p_old_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
844: p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
845: p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
846: p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
847: p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
848: p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
849: p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
850: p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
851: p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
844: p_new_previous_qualifications IGS_GR_GRADUAND_ALL.previous_qualifications%TYPE ,
845: p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
846: p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
847: p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
848: p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
849: p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
850: p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
851: p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
852: p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
845: p_old_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
846: p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
847: p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
848: p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
849: p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
850: p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
851: p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
852: p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
853: p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
846: p_new_convocation_memb_ind IGS_GR_GRADUAND_ALL.convocation_membership_ind%TYPE ,
847: p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
848: p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
849: p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
850: p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
851: p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
852: p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
853: p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
854: p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
847: p_old_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
848: p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
849: p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
850: p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
851: p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
852: p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
853: p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
854: p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
855: p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
848: p_new_sur_for_course_cd IGS_GR_GRADUAND_ALL.sur_for_course_cd%TYPE ,
849: p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
850: p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
851: p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
852: p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
853: p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
854: p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
855: p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
856: p_new_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
849: p_old_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
850: p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
851: p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
852: p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
853: p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
854: p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
855: p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
856: p_new_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
857: p_old_comments IGS_GR_GRADUAND_ALL.comments%TYPE ,
850: p_new_sur_for_crs_version_numb IGS_GR_GRADUAND_ALL.sur_for_crs_version_number%TYPE ,
851: p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
852: p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
853: p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
854: p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
855: p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
856: p_new_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
857: p_old_comments IGS_GR_GRADUAND_ALL.comments%TYPE ,
858: p_new_comments IGS_GR_GRADUAND_ALL.comments%TYPE )
851: p_old_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
852: p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
853: p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
854: p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
855: p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
856: p_new_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
857: p_old_comments IGS_GR_GRADUAND_ALL.comments%TYPE ,
858: p_new_comments IGS_GR_GRADUAND_ALL.comments%TYPE )
859: AS
852: p_new_sur_for_award_cd IGS_GR_GRADUAND_ALL.sur_for_award_cd%TYPE ,
853: p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
854: p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
855: p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
856: p_new_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
857: p_old_comments IGS_GR_GRADUAND_ALL.comments%TYPE ,
858: p_new_comments IGS_GR_GRADUAND_ALL.comments%TYPE )
859: AS
860: l_org_id NUMBER(15);
853: p_old_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
854: p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
855: p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
856: p_new_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
857: p_old_comments IGS_GR_GRADUAND_ALL.comments%TYPE ,
858: p_new_comments IGS_GR_GRADUAND_ALL.comments%TYPE )
859: AS
860: l_org_id NUMBER(15);
861: BEGIN -- grdp_ins_gr_hist
854: p_new_update_who IGS_GR_GRADUAND_ALL.last_updated_by%TYPE ,
855: p_old_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
856: p_new_update_on IGS_GR_GRADUAND_ALL.last_update_date%TYPE ,
857: p_old_comments IGS_GR_GRADUAND_ALL.comments%TYPE ,
858: p_new_comments IGS_GR_GRADUAND_ALL.comments%TYPE )
859: AS
860: l_org_id NUMBER(15);
861: BEGIN -- grdp_ins_gr_hist
862: -- Insert IGS_GR_GRADUAND history(IGS_GR_GRADUAND_HIST)
858: p_new_comments IGS_GR_GRADUAND_ALL.comments%TYPE )
859: AS
860: l_org_id NUMBER(15);
861: BEGIN -- grdp_ins_gr_hist
862: -- Insert IGS_GR_GRADUAND history(IGS_GR_GRADUAND_HIST)
863: DECLARE
864: v_gr_rec IGS_GR_GRADUAND_HIST%ROWTYPE;
865: v_create_history BOOLEAN := FALSE;
866:
860: l_org_id NUMBER(15);
861: BEGIN -- grdp_ins_gr_hist
862: -- Insert IGS_GR_GRADUAND history(IGS_GR_GRADUAND_HIST)
863: DECLARE
864: v_gr_rec IGS_GR_GRADUAND_HIST%ROWTYPE;
865: v_create_history BOOLEAN := FALSE;
866:
867: BEGIN -- If any of the old values (p_old_
868: -- different from the associated new values (p_new_
957: DECLARE
958: lv_rowid VARCHAR2(25);
959: BEGIN
960: l_org_id := igs_ge_gen_003.get_org_id;
961: IGS_GR_GRADUAND_HIST_PKG.INSERT_ROW(
962: X_ROWID => lv_rowid,
963: X_PERSON_ID => v_gr_rec.person_id,
964: X_CREATE_DT => v_gr_rec.create_dt,
965: X_HIST_START_DT => v_gr_rec.hist_start_dt,
1011: p_location_cd IGS_AD_LOCATION.LOCATION_CD%TYPE;
1012: p_graduand_status IGS_GR_STAT.GRADUAND_STATUS%TYPE;
1013:
1014: BEGIN -- grdp_prc_gac
1015: -- This process manages the initial allocation of IGS_GR_GRADUAND records to
1016: -- ceremonies by creating IGS_GR_AWD_CRMN records and the
1017: -- re-alloaction of graduands when IGS_GR_AWD_CEREMONY and
1018: -- IGS_GR_AWD_CRM_US_GP records are closed.
1019: -- The process finds closed IGS_GR_AWD_CEREMONY records for the specified
1021: -- graduands.
1022: -- The process finds closed IGS_GR_AWD_CRM_US_GP records for the specified
1023: -- IGS_GR_CRMN_ROUND, calls GENP_PRC_ACUSG_CLOSE to re-allocate any associated
1024: -- graduands.
1025: -- The process finds any IGS_GR_GRADUAND records for the specified IGS_GR_CRMN_ROUND,
1026: -- location_cd, and IGS_GR_STAT which do not have an existing
1027: -- IGS_GR_AWD_CRMN record and calls GENP_INS_GAC to determine if a
1028: -- ceremony exists suitable for the IGS_GR_GRADUAND and if one is suitable create
1029: -- a IGS_GR_AWD_CRMN record linking the IGS_GR_GRADUAND to it.
1024: -- graduands.
1025: -- The process finds any IGS_GR_GRADUAND records for the specified IGS_GR_CRMN_ROUND,
1026: -- location_cd, and IGS_GR_STAT which do not have an existing
1027: -- IGS_GR_AWD_CRMN record and calls GENP_INS_GAC to determine if a
1028: -- ceremony exists suitable for the IGS_GR_GRADUAND and if one is suitable create
1029: -- a IGS_GR_AWD_CRMN record linking the IGS_GR_GRADUAND to it.
1030:
1031: --
1032: -- Change History :
1025: -- The process finds any IGS_GR_GRADUAND records for the specified IGS_GR_CRMN_ROUND,
1026: -- location_cd, and IGS_GR_STAT which do not have an existing
1027: -- IGS_GR_AWD_CRMN record and calls GENP_INS_GAC to determine if a
1028: -- ceremony exists suitable for the IGS_GR_GRADUAND and if one is suitable create
1029: -- a IGS_GR_AWD_CRMN record linking the IGS_GR_GRADUAND to it.
1030:
1031: --
1032: -- Change History :
1033: -- Who When What
1084: gr.create_dt,
1085: gr.s_graduand_type,
1086: gr.grd_cal_type,
1087: gr.grd_ci_sequence_number
1088: FROM IGS_GR_GRADUAND gr,
1089: IGS_GR_STAT gst,
1090: IGS_EN_STDNT_PS_ATT sca
1091: WHERE gr.grd_cal_type = p_grd_cal_type AND
1092: gr.grd_ci_sequence_number = p_grd_ci_sequence_number AND
1107: --
1108: -- sepalani Bug# 5074150
1109: --
1110: CURSOR c_gr_upd (
1111: cp_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE,
1112: cp_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE) IS
1113: SELECT rowid, gr.*
1114: FROM IGS_GR_GRADUAND_ALL gr
1115: WHERE gr.person_id = cp_person_id AND
1108: -- sepalani Bug# 5074150
1109: --
1110: CURSOR c_gr_upd (
1111: cp_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE,
1112: cp_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE) IS
1113: SELECT rowid, gr.*
1114: FROM IGS_GR_GRADUAND_ALL gr
1115: WHERE gr.person_id = cp_person_id AND
1116: gr.create_dt = cp_create_dt
1110: CURSOR c_gr_upd (
1111: cp_person_id IGS_GR_GRADUAND_ALL.person_id%TYPE,
1112: cp_create_dt IGS_GR_GRADUAND_ALL.create_dt%TYPE) IS
1113: SELECT rowid, gr.*
1114: FROM IGS_GR_GRADUAND_ALL gr
1115: WHERE gr.person_id = cp_person_id AND
1116: gr.create_dt = cp_create_dt
1117: FOR UPDATE OF s_graduand_type NOWAIT;
1118: v_gr_del c_gr_upd%ROWTYPE;
1178: Fnd_Message.Set_Name('IGS', v_message_name);
1179: App_Exception.Raise_Exception;
1180: END IF;
1181: END LOOP; -- c_acusg
1182: -- 4.1 Find any IGS_GR_GRADUAND records matching the IGS_GR_CRMN_ROUND, location_cd
1183: -- and IGS_GR_STAT specified which do not have an existing
1184: -- IGS_GR_AWD_CRMN record and loop through them.
1185: FOR v_gr_rec IN c_gr LOOP
1186: SAVEPOINT sp_prc_gac;
1183: -- and IGS_GR_STAT specified which do not have an existing
1184: -- IGS_GR_AWD_CRMN record and loop through them.
1185: FOR v_gr_rec IN c_gr LOOP
1186: SAVEPOINT sp_prc_gac;
1187: -- 4.2 If the IGS_GR_GRADUAND has been deferred into this IGS_GR_CRMN_ROUND update the
1188: -- s_graduand_type from DEFERRED to UNKOWN. DO NOT COMMIT THIS UPDATE.
1189: IF v_gr_rec.s_graduand_type = cst_deferred THEN
1190: BEGIN
1191: OPEN c_gr_upd(
1193: v_gr_rec.create_dt);
1194: FETCH c_gr_upd INTO v_gr_del;
1195: -- 3.Delete the existing IGS_GR_AWD_CRMN record
1196: IF (c_gr_upd%FOUND) THEN
1197: IGS_GR_GRADUAND_PKG.UPDATE_ROW(
1198: X_ROWID => v_gr_del.rowid,
1199: X_PERSON_ID => v_gr_del.person_id,
1200: X_CREATE_DT => v_gr_del.create_dt,
1201: X_GRD_CAL_TYPE => v_gr_del.grd_cal_type,
1253: ROLLBACK TO sp_prc_gac;
1254: RAISE;
1255: END;
1256: END IF;
1257: -- 4.3 Call GENP_PRC_GAC_CRMNY for each IGS_GR_GRADUAND record found which will
1258: -- create a IGS_GR_AWD_CRMN record allocating it to an appropriate
1259: -- ceremony if one is available.
1260: IF IGS_GR_PRC_GAC.grdp_ins_gac(
1261: v_gr_rec.person_id,
1277: v_message_name) = FALSE THEN
1278: Fnd_Message.Set_Name('IGS', v_message_name);
1279: App_Exception.Raise_Exception;
1280: END IF;
1281: -- 4.4 If the IGS_GR_GRADUAND has been deferred into this IGS_GR_CRMN_ROUND and they
1282: -- have not been placed in a ceremony, ROLLBACK the change to the
1283: -- s_graduand_type to DEFERRED. If they were placed in a ceremony round
1284: -- this change would have been commited on the insert of the
1285: -- IGS_GR_AWD_CRMN record.
1329: p_approval_status IGS_GR_APRV_STAT.graduand_appr_status%TYPE)
1330: AS
1331:
1332: BEGIN -- grdp_set_gr_gst
1333: -- This module checks if a IGS_GR_GRADUAND with a 'POTENTIAL' IGS_GR_GRADUAND status is
1334: -- now 'ELIGIBLE'. It also checks if an 'ELIGIBLE' IGS_GR_GRADUAND is no longer
1335: -- eligible. It sets the status accordingly.
1336: retcode:=0;
1337: igs_ge_gen_003.set_org_id(p_org_id);
1330: AS
1331:
1332: BEGIN -- grdp_set_gr_gst
1333: -- This module checks if a IGS_GR_GRADUAND with a 'POTENTIAL' IGS_GR_GRADUAND status is
1334: -- now 'ELIGIBLE'. It also checks if an 'ELIGIBLE' IGS_GR_GRADUAND is no longer
1335: -- eligible. It sets the status accordingly.
1336: retcode:=0;
1337: igs_ge_gen_003.set_org_id(p_org_id);
1338: DECLARE
1352: WHERE gst.graduand_status = cp_graduand_status AND
1353: gst.s_graduand_status = cp_s_graduand_status;
1354:
1355: CURSOR c_sp_exists(
1356: cp_person_id IGS_GR_GRADUAND.person_id%TYPE,
1357: cp_course_cd IGS_GR_GRADUAND.course_cd%TYPE,
1358: cp_award_cd IGS_GR_GRADUAND.award_cd%TYPE) IS
1359: SELECT '1'
1360: FROM IGS_EN_SPA_AWD_AIM spaaa
1353: gst.s_graduand_status = cp_s_graduand_status;
1354:
1355: CURSOR c_sp_exists(
1356: cp_person_id IGS_GR_GRADUAND.person_id%TYPE,
1357: cp_course_cd IGS_GR_GRADUAND.course_cd%TYPE,
1358: cp_award_cd IGS_GR_GRADUAND.award_cd%TYPE) IS
1359: SELECT '1'
1360: FROM IGS_EN_SPA_AWD_AIM spaaa
1361: WHERE spaaa.person_id = cp_person_id
1354:
1355: CURSOR c_sp_exists(
1356: cp_person_id IGS_GR_GRADUAND.person_id%TYPE,
1357: cp_course_cd IGS_GR_GRADUAND.course_cd%TYPE,
1358: cp_award_cd IGS_GR_GRADUAND.award_cd%TYPE) IS
1359: SELECT '1'
1360: FROM IGS_EN_SPA_AWD_AIM spaaa
1361: WHERE spaaa.person_id = cp_person_id
1362: AND spaaa.course_cd = cp_course_cd
1407: gr.attribute17,
1408: gr.attribute18,
1409: gr.attribute19,
1410: gr.attribute20
1411: FROM IGS_GR_GRADUAND gr,
1412: IGS_GR_STAT gst
1413: WHERE gr.graduand_status = gst.graduand_status AND
1414: gst.s_graduand_status IN
1415: ('POTENTIAL',
1505: (c_sp_exists%FOUND AND p_graduand_status IS NOT NULL AND p_approval_status IS NOT NULL) THEN
1506: DECLARE
1507: l_message_text fnd_new_messages.message_text%TYPE;
1508: BEGIN
1509: IGS_GR_GRADUAND_PKG.UPDATE_ROW(
1510: X_ROWID => v_gr_gst_rec.rowid,
1511: X_PERSON_ID => v_gr_gst_rec.person_id,
1512: X_CREATE_DT => v_gr_gst_rec.create_dt,
1513: X_GRD_CAL_TYPE => v_gr_gst_rec.grd_cal_type,
1914: 'gac.grd_cal_type, ' ||
1915: 'gac.grd_ci_sequence_number, '||
1916: 'gac.ceremony_number ' ||
1917: 'FROM IGS_GR_AWD_CRMN gac, ' ||
1918: 'IGS_GR_GRADUAND gr, ' ||
1919: 'IGS_GR_STAT gst, ' ||
1920: 'IGS_PE_PERSON pe, ' ||
1921: 'IGS_GR_AWD_CEREMONY awc, ' ||
1922: 'IGS_GR_AWD_CRM_US_GP acusg, '||