1 PACKAGE BODY IGS_CA_VAL_CAT AS
2 /* $Header: IGSCA04B.pls 115.3 2002/11/28 22:56:59 nsidana ship $ */
3 -- Validate when System Calendar Category is changed.
4 FUNCTION calp_val_sys_cal_cat(
5 p_cal_type IN VARCHAR2 ,
6 p_message_name OUT NOCOPY VARCHAR2 )
7 RETURN BOOLEAN AS
8 BEGIN
9 DECLARE
10 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
11 cst_inactive CONSTANT VARCHAR2(10) := 'INACTIVE';
12 CURSOR c_cal_instance_rec (
13 cp_cal_type IGS_CA_TYPE.CAL_TYPE%TYPE) IS
14 SELECT DISTINCT 'x'
15 FROM IGS_CA_INST ci,
16 IGS_CA_STAT cs
17 WHERE ci.CAL_TYPE = cp_cal_type AND
18 cs.CAL_STATUS = ci.CAL_STATUS AND
19 cs.s_cal_status IN (cst_active, cst_inactive);
20 v_other_detail VARCHAR2(255);
21 v_cal_instance_rec IGS_CA_INST%ROWTYPE;
22 v_check CHAR;
23 BEGIN
24 -- This module validates that IGS_CA_TYPE.S_CAL_CAT
25 -- cannot be changed if ACTIVE calendar instances
26 -- exist for the calendar type.
27 -- validate the input parameters
28 IF (p_cal_type IS NULL) THEN
29 p_message_name := 'IGS_GE_MANDATORY_FLD';
30 RETURN FALSE;
31 END IF;
32 -- check for the existance of IGS_CA_INST
33 -- other than planned
34 OPEN c_cal_instance_rec(p_cal_type);
35 FETCH c_cal_instance_rec INTO v_check;
36 IF (c_cal_instance_rec%NOTFOUND) THEN
37 CLOSE c_cal_instance_rec;
38 p_message_name := NULL;
39 RETURN TRUE;
40 ELSE
41 CLOSE c_cal_instance_rec;
42 p_message_name := 'IGS_CA_SYSCALCAT_CANNOT_CHG';
43 RETURN FALSE;
44 END IF;
45
46 END;
47 END calp_val_sys_cal_cat;
48 --
49 -- Validate if ARTS teaching calendar type code is closed.
50 FUNCTION calp_val_atctc_clsd(
51 p_arts_teaching_cal_type_cd IN VARCHAR2 ,
52 p_message_name OUT NOCOPY VARCHAR2 )
53 RETURN BOOLEAN AS
54 gv_other_detail VARCHAR2(255);
55 BEGIN
56 DECLARE
57 v_closed_ind IGS_CA_ARTS_TC_CA_CD.closed_ind%TYPE;
58 CURSOR c_atctc IS
59 SELECT atctc.closed_ind
60 FROM IGS_CA_ARTS_TC_CA_CD atctc
61 WHERE atctc.ARTS_TEACHING_CAL_TYPE_CD = p_arts_teaching_cal_type_cd;
62 BEGIN
63 -- Validate if IGS_CA_ARTS_TC_CA_CD.ARTS_TEACHING_CAL_TYPE_CD is closed.
64 OPEN c_atctc;
65 FETCH c_atctc INTO v_closed_ind;
66 IF (c_atctc%FOUND) THEN
67 IF (v_closed_ind = 'Y') THEN
68 CLOSE c_atctc;
69 p_message_name := 'IGS_CA_ARTS_TYPECD_CLOSED';
70 RETURN FALSE;
71 END IF;
72 END IF;
73 CLOSE c_atctc;
74 p_message_name := NULL;
75 RETURN TRUE;
76
77 END;
78 END calp_val_atctc_clsd;
79 --
80 -- Validate Calendar Type ARTS Teaching Code.
81 FUNCTION calp_val_cat_arts_cd(
82 p_s_cal_cat IN VARCHAR2 ,
83 p_arts_teaching_cal_type_cd IN VARCHAR2 ,
84 p_message_name OUT NOCOPY VARCHAR2 )
85 RETURN BOOLEAN AS
86 gv_other_detail VARCHAR2(255);
87 BEGIN
88 DECLARE
89 cst_teaching CONSTANT IGS_CA_TYPE.S_CAL_CAT%TYPE := 'TEACHING';
90 BEGIN
91 -- Validate if IGS_CA_TYPE.ARTS_TEACHING_CAL_TYPE_CD exists,
92 -- and only exists for IGS_CA_TYPE.S_CAL_CAT 'TEACHING'.
93 IF (p_arts_teaching_cal_type_cd IS NULL) THEN
94 IF (p_s_cal_cat = cst_teaching) THEN
95 p_message_name := 'IGS_CA_ARTS_CALTYPE_TEACHING';
96 RETURN FALSE;
97 END IF;
98 ELSE
99 IF (p_s_cal_cat <> cst_teaching) THEN
100 p_message_name := 'IGS_CA_ARTS_CALTYPE_NOTCAT';
101 RETURN FALSE;
102 END IF;
103 END IF;
104 p_message_name :=NULL;
105 RETURN TRUE;
106 END;
107 END calp_val_cat_arts_cd;
108 END IGS_CA_VAL_CAT;