[Home] [Help]
69: IGS_GE_MSG_STACK.ADD;
70: App_Exception.Raise_Exception;
71: END genp_val_pe_deceased;
72: --
73: -- To validate IGS_RE_THESIS examination submission date
74: FUNCTION RESP_VAL_TEX_SBMSN(
75: p_person_id IN NUMBER ,
76: p_ca_sequence_number IN NUMBER ,
77: p_the_sequence_number IN NUMBER ,
102: -- Cannot be cleared if any thesis_panel_member records exist
103: -- with their thesis_result_cd set
104: -- Cannot set if previous thesis_exam record exists which
105: -- has been submitted but for which no result has been recorded
106: -- Cannot set if parent IGS_RE_THESIS.final_title_ind = 'N'
107: -- Cannot be set if the parent IGS_EN_STDNT_PS_ATT.course_attempt_status
108: -- is not one of ENROLLED, INTERMIT or INACTIVE
109: -- Cannot be deceased person
110: -- Cannot be future dated
126: cst_intermit CONSTANT VARCHAR2(10) := 'INTERMIT';
127: cst_no_result CONSTANT VARCHAR2(10) := 'NORESULT';
128: CURSOR c_the IS
129: SELECT thes.final_title_ind
130: FROM IGS_RE_THESIS thes
131: WHERE thes.person_id = p_person_id AND
132: thes.ca_sequence_number = p_ca_sequence_number AND
133: thes.sequence_number = p_the_sequence_number;
134: v_the_rec c_the%ROWTYPE;
148: sca.course_cd = ca.sca_course_cd;
149: v_sca_rec c_sca%ROWTYPE;
150: CURSOR c_tex IS
151: SELECT 'x'
152: FROM IGS_RE_THESIS_EXAM tex
153: WHERE tex.person_id = p_person_id AND
154: tex.ca_sequence_number = p_ca_sequence_number AND
155: tex.the_sequence_number = p_the_sequence_number AND
156: tex.creation_dt < p_creation_dt AND
185: csc.ca_sequence_number = p_ca_sequence_number;
186: v_csc_rec c_csc%ROWTYPE;
187: CURSOR c_tex2 IS
188: SELECT 'x'
189: FROM IGS_RE_THESIS_EXAM tex
190: WHERE tex.person_id = p_person_id AND
191: tex.ca_sequence_number = p_ca_sequence_number AND
192: tex.the_sequence_number = p_the_sequence_number AND
193: tex.creation_dt < p_creation_dt AND
196: v_dummy VARCHAR2(1);
197: CURSOR c_tpm IS
198: SELECT 'x'
199: FROM IGS_RE_THS_PNL_MBR tpm,
200: IGS_RE_THESIS_RESULT thr
201: WHERE tpm.person_id = p_person_id AND
202: tpm.ca_sequence_number = p_ca_sequence_number AND
203: tpm.the_sequence_number = p_the_sequence_number AND
204: tpm.creation_dt = p_creation_dt AND
302: p_message_name := 'IGS_RE_CANT_SUB_UNLES_CUR_ENR';
303: RETURN FALSE;
304: END IF;
305: CLOSE c_sca;
306: -- 3. Cannot be set if previous submitted IGS_RE_THESIS examination
307: -- records exist with no result.
308: OPEN c_tex;
309: FETCH c_tex INTO v_tex_exists;
310: IF c_tex%FOUND THEN
431: END IF;
432: RETURN FALSE;
433: END IF;
434: IF p_legacy <> 'Y' THEN
435: -- 2. Cannot be cleared IF any IGS_RE_THESIS panel records exist
436: -- which have results recorded.
437: OPEN c_tpm;
438: FETCH c_tpm INTO v_tpm_exists;
439: IF c_tpm%FOUND THEN
480: IGS_GE_MSG_STACK.ADD;
481: App_Exception.Raise_Exception;
482: END resp_val_tex_sbmsn;
483: --
484: -- To validate the IGS_RE_THESIS examination update
485: FUNCTION RESP_VAL_TEX_UPD(
486: p_person_id IN NUMBER ,
487: p_ca_sequence_number IN NUMBER ,
488: p_the_sequence_number IN NUMBER ,
502: cst_intermit CONSTANT
503: IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INTERMIT';
504: cst_completed CONSTANT
505: IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'COMPLETED';
506: cst_examined CONSTANT IGS_RE_THESIS_V.thesis_status%TYPE := 'EXAMINED';
507: cst_deleted CONSTANT IGS_RE_THESIS_V.thesis_status%TYPE := 'DELETED';
508: v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
509: v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
510: v_thesis_status IGS_RE_THESIS_V.thesis_status%TYPE;
503: IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'INTERMIT';
504: cst_completed CONSTANT
505: IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'COMPLETED';
506: cst_examined CONSTANT IGS_RE_THESIS_V.thesis_status%TYPE := 'EXAMINED';
507: cst_deleted CONSTANT IGS_RE_THESIS_V.thesis_status%TYPE := 'DELETED';
508: v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
509: v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
510: v_thesis_status IGS_RE_THESIS_V.thesis_status%TYPE;
511: v_message_name VARCHAR2(30);
506: cst_examined CONSTANT IGS_RE_THESIS_V.thesis_status%TYPE := 'EXAMINED';
507: cst_deleted CONSTANT IGS_RE_THESIS_V.thesis_status%TYPE := 'DELETED';
508: v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
509: v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
510: v_thesis_status IGS_RE_THESIS_V.thesis_status%TYPE;
511: v_message_name VARCHAR2(30);
512: CURSOR c_ca IS
513: SELECT ca.sca_course_cd
514: FROM IGS_RE_CANDIDATURE ca
521: WHERE sca.person_id = p_person_id AND
522: sca.course_cd = cp_sca_course_cd;
523: CURSOR c_thev IS
524: SELECT thev.thesis_status
525: FROM IGS_RE_THESIS_V thev
526: WHERE thev.person_id = p_person_id AND
527: thev.ca_sequence_number = p_ca_sequence_number AND
528: thev.sequence_number = p_the_sequence_number;
529: BEGIN
565: cst_completed)) THEN
566: p_message_name := 'IGS_RE_CANT_IU_EXAM_DETAILS';
567: RETURN FALSE;
568: END IF;
569: -- 3. Not if the parent IGS_RE_THESIS is EXAMINED or DELETED
570: OPEN c_thev;
571: FETCH c_thev INTO v_thesis_status;
572: IF (c_thev%FOUND AND
573: v_thesis_status IN (
607: IGS_GE_MSG_STACK.ADD;
608: App_Exception.Raise_Exception;
609: END resp_val_tex_upd;
610: --
611: -- To validate IGS_RE_THESIS examination type
612: FUNCTION RESP_VAL_TEX_TET(
613: p_thesis_exam_type IN VARCHAR2 ,
614: p_message_name OUT NOCOPY VARCHAR2 )
615: RETURN BOOLEAN AS
630: OPEN c_tet;
631: FETCH c_tet INTO v_dummy;
632: IF c_tet%FOUND THEN
633: CLOSE c_tet;
634: p_message_name := 'IGS_RE_THESIS_EXAM_TYPE_CLOSE';
635: RETURN FALSE;
636: END IF;
637: CLOSE c_tet;
638: RETURN TRUE;
649: IGS_GE_MSG_STACK.ADD;
650: App_Exception.Raise_Exception;
651: END; -- resp_val_tex_tet
652: --
653: -- To validate IGS_RE_THESIS examination result code
654: FUNCTION RESP_VAL_TEX_THR(
655: p_person_id IN NUMBER ,
656: p_ca_sequence_number IN NUMBER ,
657: p_the_sequence_number IN NUMBER ,
661: p_thesis_panel_type IN VARCHAR2 ,
662: p_message_name OUT NOCOPY VARCHAR2 )
663: RETURN BOOLEAN AS
664: BEGIN -- resp_val_tex_thr
665: -- Validate IGS_RE_THESIS_EXAM.thesis_result_cd, checking for :
666: -- Code cannot be closed
667: -- Code cannot be specified if submission_dt is not set
668: -- Warning if code does not match any of the panel members results
669: -- (if any results exist)
666: -- Code cannot be closed
667: -- Code cannot be specified if submission_dt is not set
668: -- Warning if code does not match any of the panel members results
669: -- (if any results exist)
670: -- Cannot be cleared if IGS_RE_THESIS has been EXAMINED (ie. final result entered).
671: DECLARE
672: cst_examined CONSTANT VARCHAR2(10) := 'EXAMINED';
673: v_dummy VARCHAR2(1);
674: v_closed_ind IGS_RE_THESIS_RESULT.closed_ind%TYPE;
670: -- Cannot be cleared if IGS_RE_THESIS has been EXAMINED (ie. final result entered).
671: DECLARE
672: cst_examined CONSTANT VARCHAR2(10) := 'EXAMINED';
673: v_dummy VARCHAR2(1);
674: v_closed_ind IGS_RE_THESIS_RESULT.closed_ind%TYPE;
675: v_records_found BOOLEAN;
676: v_result_match BOOLEAN;
677: v_record_count NUMBER;
678: v_confirmed_count NUMBER;
678: v_confirmed_count NUMBER;
679: v_recommended_panel_size IGS_RE_THS_PNL_TYPE.recommended_panel_size%TYPE;
680: CURSOR c_thr IS
681: SELECT thr.closed_ind
682: FROM IGS_RE_THESIS_RESULT thr
683: WHERE thr.thesis_result_cd = p_thesis_result_cd;
684: CURSOR c_tpm IS
685: SELECT tpm.thesis_result_cd
686: FROM IGS_RE_THS_PNL_MBR tpm
702: tpm.the_sequence_number = p_the_sequence_number AND
703: tpm.creation_dt = p_creation_dt;
704: CURSOR c_thev IS
705: SELECT 'x'
706: FROM IGS_RE_THESIS_V thev
707: WHERE thev.person_id = p_person_id AND
708: thev.ca_sequence_number = p_ca_sequence_number AND
709: thev.sequence_number = p_the_sequence_number AND
710: thev.thesis_status = cst_examined;
709: thev.sequence_number = p_the_sequence_number AND
710: thev.thesis_status = cst_examined;
711: CURSOR c_tex IS
712: SELECT 'x'
713: FROM IGS_RE_THESIS_EXAM tex
714: WHERE tex.person_id = p_person_id AND
715: tex.ca_sequence_number = p_ca_sequence_number AND
716: tex.the_sequence_number = p_the_sequence_number AND
717: tex.creation_dt > p_creation_dt AND
721: p_message_name := NULL;
722: v_records_found := FALSE;
723: v_result_match := FALSE;
724: IF p_thesis_result_cd IS NOT NULL THEN
725: -- 1. Cannot be specified if not a submitted IGS_RE_THESIS.
726: IF p_submission_dt IS NULL THEN
727: p_message_name := 'IGS_RE_CANT_ENTER_RES_EXAM';
728: RETURN FALSE;
729: END IF;
736: RETURN TRUE;
737: END IF;
738: CLOSE c_thr;
739: IF v_closed_ind = 'Y' THEN
740: p_message_name := 'IGS_RE_THESIS_RESUILT_CLOSED';
741: RETURN FALSE;
742: END IF;
743: -- Validate that minimum panel size has been met.
744: OPEN c_tpt;
764: p_message_name := 'IGS_RE_CANT_ENTER_RESULT';
765: RETURN FALSE;
766: END IF;
767: END IF;
768: -- 3. Warn if result doesn't match at least one of the IGS_RE_THESIS panel results.
769: FOR v_tpm_rec IN c_tpm Loop
770: v_records_found := TRUE;
771: IF p_thesis_result_cd = v_tpm_rec.thesis_result_cd THEN
772: v_result_match := TRUE;
778: p_message_name := 'IGS_RE_RES_DOES_NOT_MATCH';
779: RETURN TRUE;
780: END IF;
781: ELSE -- result code null
782: --4. Cannot be cleared if the parent IGS_RE_THESIS status is EXAMINED.
783: OPEN c_thev;
784: FETCH c_thev INTO v_dummy;
785: IF c_thev%FOUND THEN
786: CLOSE c_thev;
847: OPEN c_tpt;
848: FETCH c_tpt INTO v_dummy;
849: IF c_tpt%FOUND THEN
850: CLOSE c_tpt;
851: p_message_name := 'IGS_RE_THESIS_EXAM_TYP_CLOSED';
852: RETURN FALSE;
853: END IF;
854: CLOSE c_tpt;
855: RETURN TRUE;