[Home] [Help]
7: --svanukur 08-apr-2004 BUG no 3453123. Removed default declaration in the body since this is
8: -- a GSCC violation
9: -- Removed procedure resp_val_cah_strt_dt as part of bug
10: -------------------------------------------------------------------------------------------
11: -- Allow for specified IGS_RE_CANDIDATURE trigger validation.
12: PROCEDURE RESP_VAL_CA_TRG(
13: p_table_name IN VARCHAR2 ,
14: p_insert_delete_ind IN VARCHAR2 )
15: AS
46: IGS_GE_MSG_STACK.ADD;
47: App_Exception.Raise_Exception;
48: END resp_val_ca_trg;
49: --
50: -- Validate IGS_RE_CANDIDATURE attendance history changes prior to census date.
51: FUNCTION resp_val_cah_census(
52: p_person_id IN NUMBER ,
53: p_ca_sequence_number IN NUMBER ,
54: p_effective_dt IN DATE ,
55: p_message_name OUT NOCOPY VARCHAR2 )
56: RETURN BOOLEAN AS
57: BEGIN -- resp_val_cah_census
58: DECLARE
59: v_ca_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
60: v_daiv_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
61: cst_unconfirm CONSTANT
62: IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'UNCONFIRM';
63: cst_duplicate CONSTANT
63: cst_duplicate CONSTANT
64: IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'DUPLICATE';
65: CURSOR c_ca IS
66: SELECT ca.sca_course_cd
67: FROM IGS_RE_CANDIDATURE ca
68: WHERE ca.person_id = p_person_id AND
69: ca.sequence_number = p_ca_sequence_number;
70: CURSOR c_sua_uv_daiv_sgcc IS
71: SELECT daiv.alias_val
125: IGS_GE_MSG_STACK.ADD;
126: App_Exception.Raise_Exception;
127: END resp_val_cah_census;
128: --
129: -- Validate IGS_RE_CANDIDATURE update.
130: FUNCTION resp_val_ca_childupd(
131: p_person_id IN NUMBER ,
132: p_ca_sequence_number IN NUMBER ,
133: p_message_name OUT NOCOPY VARCHAR2 )
136: -- Who When What
137: -- stutta 05-May-2004 Added c_awd_exists,c_incomp_awd cursors and modified logic to return false
138: -- only if a completed program attempt has all its awards completed.(Bug #3577988)
139: BEGIN -- resp_val_ca_childupd
140: -- This module validates the update of IGS_RE_CANDIDATURE child details.
141: -- Validations are:
142: -- IGS_RE_CANDIDATURE child details cannot be updated if
143: -- IGS_EN_STDNT_PS_ATT.course_attempt_status is 'COMPLETED' with all awards completed.
144: -- If atleast one award is incomplete or no award is associated, update is allowed.
138: -- only if a completed program attempt has all its awards completed.(Bug #3577988)
139: BEGIN -- resp_val_ca_childupd
140: -- This module validates the update of IGS_RE_CANDIDATURE child details.
141: -- Validations are:
142: -- IGS_RE_CANDIDATURE child details cannot be updated if
143: -- IGS_EN_STDNT_PS_ATT.course_attempt_status is 'COMPLETED' with all awards completed.
144: -- If atleast one award is incomplete or no award is associated, update is allowed.
145: DECLARE
146: cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
143: -- IGS_EN_STDNT_PS_ATT.course_attempt_status is 'COMPLETED' with all awards completed.
144: -- If atleast one award is incomplete or no award is associated, update is allowed.
145: DECLARE
146: cst_completed CONSTANT VARCHAR2(10) := 'COMPLETED';
147: v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
148: v_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
149: v_dummy VARCHAR2(1);
150:
151: CURSOR c_ca IS
149: v_dummy VARCHAR2(1);
150:
151: CURSOR c_ca IS
152: SELECT ca.sca_course_cd
153: FROM IGS_RE_CANDIDATURE ca
154: WHERE ca.person_id = p_person_id AND
155: ca.sequence_number = p_ca_sequence_number;
156: CURSOR c_sca (
157: cp_sca_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
237: IGS_GE_MSG_STACK.ADD;
238: App_Exception.Raise_Exception;
239: END resp_val_ca_childupd;
240: --
241: -- Validate IGS_RE_CANDIDATURE attendance history dates.
242: FUNCTION resp_val_cah_hist_dt(
243: p_person_id IN NUMBER ,
244: p_ca_sequence_number IN NUMBER ,
245: p_validate_first_hist_ind IN VARCHAR2 ,
245: p_validate_first_hist_ind IN VARCHAR2 ,
246: p_message_name OUT NOCOPY VARCHAR2 )
247: RETURN BOOLEAN AS
248: BEGIN -- resp_val_cah_hist_dt
249: -- This module validates for IGS_RE_CDT_ATT_HIST for the IGS_RE_CANDIDATURE. The
250: -- following is validated:
251: -- * first hist_start_dt = IGS_EN_STDNT_PS_ATT.commencement_dt
252: -- * IGS_RE_CDT_ATT_HIST do not overlap or have gaps ie. history is
253: -- continuous.
273: CURSOR c_ca_sca(
274: cp_person_id IGS_RE_CDT_ATT_HIST.person_id%TYPE,
275: cp_ca_sequence_number IGS_RE_CDT_ATT_HIST.ca_sequence_number%TYPE) IS
276: SELECT sca.commencement_dt
277: FROM IGS_RE_CANDIDATURE ca,
278: IGS_EN_STDNT_PS_ATT sca
279: WHERE ca.person_id = cp_person_id AND
280: ca.sequence_number = cp_ca_sequence_number AND
281: ca.person_id = sca.person_id AND
325: IGS_GE_MSG_STACK.ADD;
326: App_Exception.Raise_Exception;
327: END resp_val_cah_hist_dt;
328: --
329: -- Validate IGS_RE_CANDIDATURE attendance history insert.
330: FUNCTION resp_val_cah_ca_ins(
331: p_person_id IN NUMBER ,
332: p_ca_sequence_number IN NUMBER ,
333: p_sca_course_cd IN VARCHAR2 ,
336: p_message_name OUT NOCOPY VARCHAR2 )
337: RETURN BOOLEAN AS
338: BEGIN -- resp_val_cah_ca_ins
339: -- This module validates IGS_RE_CDT_ATT_HIST insert in the context of
340: -- IGS_RE_CANDIDATURE. The following is validated.
341: -- * IGS_RE_CANDIDATURE.sca_course_cd must exist.
342: -- * research IGS_RE_CANDIDATURE IGS_EN_STDNT_PS_ATT.student_confirmed_ind is
343: -- 'Y' and IGS_EN_STDNT_PS_ATT.commencement_dt exists.
344: DECLARE
337: RETURN BOOLEAN AS
338: BEGIN -- resp_val_cah_ca_ins
339: -- This module validates IGS_RE_CDT_ATT_HIST insert in the context of
340: -- IGS_RE_CANDIDATURE. The following is validated.
341: -- * IGS_RE_CANDIDATURE.sca_course_cd must exist.
342: -- * research IGS_RE_CANDIDATURE IGS_EN_STDNT_PS_ATT.student_confirmed_ind is
343: -- 'Y' and IGS_EN_STDNT_PS_ATT.commencement_dt exists.
344: DECLARE
345: v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
338: BEGIN -- resp_val_cah_ca_ins
339: -- This module validates IGS_RE_CDT_ATT_HIST insert in the context of
340: -- IGS_RE_CANDIDATURE. The following is validated.
341: -- * IGS_RE_CANDIDATURE.sca_course_cd must exist.
342: -- * research IGS_RE_CANDIDATURE IGS_EN_STDNT_PS_ATT.student_confirmed_ind is
343: -- 'Y' and IGS_EN_STDNT_PS_ATT.commencement_dt exists.
344: DECLARE
345: v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
346: v_sca_student_confirmed_ind
341: -- * IGS_RE_CANDIDATURE.sca_course_cd must exist.
342: -- * research IGS_RE_CANDIDATURE IGS_EN_STDNT_PS_ATT.student_confirmed_ind is
343: -- 'Y' and IGS_EN_STDNT_PS_ATT.commencement_dt exists.
344: DECLARE
345: v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
346: v_sca_student_confirmed_ind
347: IGS_EN_STDNT_PS_ATT.student_confirmed_ind%TYPE;
348: v_sca_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
349: v_sca_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
348: v_sca_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
349: v_sca_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
350: CURSOR c_ca IS
351: SELECT ca.sca_course_cd
352: FROM IGS_RE_CANDIDATURE ca
353: WHERE ca.person_id = p_person_id AND
354: ca.sequence_number = p_ca_sequence_number;
355: CURSOR c_sca(cp_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE)
356: IS
351: SELECT ca.sca_course_cd
352: FROM IGS_RE_CANDIDATURE ca
353: WHERE ca.person_id = p_person_id AND
354: ca.sequence_number = p_ca_sequence_number;
355: CURSOR c_sca(cp_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE)
356: IS
357: SELECT sca.student_confirmed_ind,
358: sca.commencement_dt,
359: sca.attendance_type
365: p_commencement_dt := NULL;
366: p_attendance_type := NULL;
367: IF p_sca_course_cd IS NULL THEN
368: -- Determine if student IGS_PS_COURSE attempt exists for the research
369: -- IGS_RE_CANDIDATURE
370: OPEN c_ca;
371: FETCH c_ca INTO v_sca_course_cd;
372: IF c_ca%NOTFOUND THEN
373: -- Invalid parameters this will be handled elsewhere
375: RETURN TRUE;
376: END IF;
377: CLOSE c_ca;
378: IF v_sca_course_cd IS NULL THEN
379: -- Changes to IGS_RE_CANDIDATURE attendance history are not recorded while
380: -- IGS_RE_CANDIDATURE is only associated with an admission IGS_PS_COURSE application
381: p_message_name := 'IGS_RE_CAND_STILL_UNDER_PROC';
382: RETURN FALSE;
383: END IF;
376: END IF;
377: CLOSE c_ca;
378: IF v_sca_course_cd IS NULL THEN
379: -- Changes to IGS_RE_CANDIDATURE attendance history are not recorded while
380: -- IGS_RE_CANDIDATURE is only associated with an admission IGS_PS_COURSE application
381: p_message_name := 'IGS_RE_CAND_STILL_UNDER_PROC';
382: RETURN FALSE;
383: END IF;
384: ELSE
422: IGS_GE_MSG_STACK.ADD;
423: App_Exception.Raise_Exception;
424: END resp_val_cah_ca_ins;
425: --
426: -- Validate IGS_RE_CANDIDATURE attendance hist start date and SCA commencement.
427: FUNCTION resp_val_cah_comm(
428: p_person_id IN NUMBER ,
429: p_ca_sequence_number IN NUMBER ,
430: p_sca_course_cd IN VARCHAR2 ,
432: p_message_name OUT NOCOPY VARCHAR2 )
433: RETURN BOOLEAN AS
434: BEGIN -- resp_val_cah_comm
435: -- Description: This module validates that the first IGS_RE_CDT_ATT_HIST.
436: -- hist_start_dt is the same as the research IGS_RE_CANDIDATURE IGS_EN_STDNT_PS_ATT
437: -- commencement_dt.
438: -- This module currently only returns a warning.
439: DECLARE
440: v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
436: -- hist_start_dt is the same as the research IGS_RE_CANDIDATURE IGS_EN_STDNT_PS_ATT
437: -- commencement_dt.
438: -- This module currently only returns a warning.
439: DECLARE
440: v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
441: v_cah_hist_start_dt IGS_RE_CDT_ATT_HIST.hist_start_dt%TYPE;
442: CURSOR c_ca IS
443: SELECT ca.sequence_number
444: FROM IGS_RE_CANDIDATURE ca
440: v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
441: v_cah_hist_start_dt IGS_RE_CDT_ATT_HIST.hist_start_dt%TYPE;
442: CURSOR c_ca IS
443: SELECT ca.sequence_number
444: FROM IGS_RE_CANDIDATURE ca
445: WHERE ca.person_id = p_person_id AND
446: ca.sca_course_cd = p_sca_course_cd;
447: CURSOR c_cah(
448: cp_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE)IS
444: FROM IGS_RE_CANDIDATURE ca
445: WHERE ca.person_id = p_person_id AND
446: ca.sca_course_cd = p_sca_course_cd;
447: CURSOR c_cah(
448: cp_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE)IS
449: SELECT cah.hist_start_dt
450: FROM IGS_RE_CDT_ATT_HIST cah
451: WHERE cah.person_id = p_person_id AND
452: cah.ca_sequence_number = cp_ca_sequence_number
458: OPEN c_ca;
459: FETCH c_ca INTO v_ca_sequence_number;
460: IF (c_ca%NOTFOUND) THEN
461: CLOSE c_ca;
462: --Student IGS_PS_COURSE attempt does not have IGS_RE_CANDIDATURE details, or
463: --Parameters are invalid
464: p_message_name := NULL;
465: RETURN TRUE;
466: END IF;
492: IGS_GE_MSG_STACK.ADD;
493: App_Exception.Raise_Exception;
494: END resp_val_cah_comm;
495: --
496: -- Validate IGS_RE_CANDIDATURE attendance history end date.
497: FUNCTION resp_val_cah_end_dt(
498: p_person_id IN NUMBER ,
499: p_ca_sequence_number IN NUMBER ,
500: p_hist_start_dt IN DATE ,