1 PACKAGE BODY IGS_EN_VAL_DLA AS
2 /* $Header: IGSEN32B.pls 115.4 2002/11/28 23:57:01 nsidana ship $ */
3 --
4 -- Validate the calendar instance status has a system status of Active.
5 FUNCTION stap_val_ci_status(
6 p_cal_type IN VARCHAR2 ,
7 p_ci_sequence_number IN NUMBER ,
8 p_message_name OUT NOCOPY VARCHAR2)
9 RETURN BOOLEAN AS
10
11 BEGIN
12 DECLARE
13 cst_active IGS_CA_STAT.s_cal_status%TYPE DEFAULT 'ACTIVE';
14 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
15 CURSOR c_chk_scc IS
16 SELECT s_cal_status
17 FROM IGS_CA_STAT cs,
18 IGS_CA_INST ci
19 WHERE ci.cal_type = p_cal_type AND
20 ci.sequence_number = p_ci_sequence_number AND
21 cs.cal_status = ci.cal_status;
22 BEGIN
23 --- Set the default message number
24 p_message_name := null;
25 --- Retrieve the calendar data.
26 OPEN c_chk_scc;
27 FETCH c_chk_scc INTO v_s_cal_status;
28 IF c_chk_scc%FOUND THEN
29 --- Validate the calendar.
30 IF v_s_cal_status <> 'ACTIVE' then
31 CLOSE c_chk_scc;
32 p_message_name := 'IGS_ST_CAL_INST_NOT_ACTIVE';
33 RETURN FALSE;
34 END IF;
35 END IF;
36 CLOSE c_chk_scc;
37 --- Return the default value
38 RETURN TRUE;
39 END;
40 END stap_val_ci_status;
41 --
42 -- Validate the DLA calendar instance status is ACTIVE or PLANNED
43 FUNCTION enrp_val_dla_status(
44 p_cal_type IN VARCHAR2 ,
45 p_ci_sequence IN NUMBER ,
46 p_message_name OUT NOCOPY VARCHAR2)
47 RETURN BOOLEAN AS
48
49 BEGIN
50 DECLARE
51 v_cal_status IGS_CA_INST.cal_status%TYPE;
52 v_s_cal_status IGS_CA_STAT.s_cal_status%TYPE;
53 CURSOR c_ci IS
54 SELECT cal_status
55 FROM IGS_CA_INST
56 WHERE cal_type = p_cal_type AND
57 sequence_number = p_ci_sequence;
58 CURSOR c_cs (cp_cal_status IGS_CA_STAT.cal_status%TYPE) IS
59 SELECT s_cal_status
60 FROM IGS_CA_STAT
61 WHERE cal_status = cp_cal_status;
62 BEGIN
63 -- This function validates the IGS_CA_INST calendar status
64 -- for a IGS_ST_DFT_LOAD_APPO record being inserted.
65 --- Set the default message number
66 p_message_name := null;
67 OPEN c_ci;
68 FETCH c_ci INTO v_cal_status;
69 IF (c_ci%NOTFOUND) THEN
70 -- no records found
71 CLOSE c_ci;
72 RETURN TRUE;
73 END IF;
74 CLOSE c_ci;
75 OPEN c_cs (v_cal_status);
76 FETCH c_cs INTO v_s_cal_status;
77 IF (c_cs%NOTFOUND) THEN
78 -- no records found
79 CLOSE c_cs;
80 RETURN TRUE;
81 END IF;
82 CLOSE c_cs;
83 IF (v_s_cal_status <> 'ACTIVE') AND
84 (v_s_cal_status <> 'PLANNED') THEN
85 -- Calendar instance system calendar status
86 -- must be 'ACTIVE' or 'PLANNED'
87 p_message_name := 'IGS_CA_CALINST_ACTIVE_PLANNED';
88 RETURN FALSE;
89 END IF;
90 --- Return the default value
91 RETURN TRUE;
92 END;
93 EXCEPTION
94 WHEN OTHERS THEN
95 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
96 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_DLA.enrp_val_dla_status');
97 IGS_GE_MSG_STACK.ADD;
98 App_Exception.Raise_Exception;
99
100 END enrp_val_dla_status;
101 --
102 -- Validate the DLA calendar type s_cal_cat = 'LOAD' and closed_ind
103 FUNCTION enrp_val_dla_cat_ld(
104 p_cal_type IN VARCHAR2 ,
105 p_message_name OUT NOCOPY VARCHAR2)
106 RETURN BOOLEAN AS
107
108 BEGIN
109 DECLARE
110 v_closed_ind IGS_CA_TYPE.closed_ind%TYPE;
111 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
112 CURSOR c_ct IS
113 SELECT closed_ind,
114 s_cal_cat
115 FROM IGS_CA_TYPE
116 WHERE cal_type = p_cal_type;
117 BEGIN
118 -- This function checks that the cal_type calendar category
119 -- is 'LOAD' and validates the closed indicator for an
120 -- IGS_ST_DFT_LOAD_APPO record being inserted with each cal_type.
121 --- Set the default message number
122 p_message_name := null;
123 OPEN c_ct;
124 FETCH c_ct INTO v_closed_ind,
125 v_s_cal_cat;
126 IF (c_ct%NOTFOUND) THEN
127 -- no records found
128 CLOSE c_ct;
129 RETURN TRUE;
130 END IF;
131 CLOSE c_ct;
132 IF (v_closed_ind = 'Y') THEN
133 -- calandar type is closed
134 p_message_name := 'IGS_CA_CALTYPE_CLOSED';
135 RETURN FALSE;
136 END IF;
137 IF (v_s_cal_cat <> 'LOAD') THEN
138 -- calandar type must be a load period calendar
139 p_message_name := 'IGS_EN_CALTYPE_LOAD_PRDCAL';
140 RETURN FALSE;
141 END IF;
142 --- Return the default value
143 RETURN TRUE;
144 END;
145 EXCEPTION
146 WHEN OTHERS THEN
147 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
148 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_DLA.enrp_val_dla_cat_ld');
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151
152 END enrp_val_dla_cat_ld;
153 --
154 -- Validate the DLA calendar type s_cal_cat = 'TEACHING' and closed_ind
155 FUNCTION enrp_val_dla_cat(
156 p_cal_type IN VARCHAR2 ,
157 p_message_name OUT NOCOPY VARCHAR2)
158 RETURN BOOLEAN AS
159
160 BEGIN
161 DECLARE
162 v_closed_ind IGS_CA_TYPE.closed_ind%TYPE;
163 v_s_cal_cat IGS_CA_TYPE.s_cal_cat%TYPE;
164 CURSOR c_ct IS
165 SELECT closed_ind,
166 s_cal_cat
167 FROM IGS_CA_TYPE
168 WHERE cal_type = p_cal_type;
169 BEGIN
170 -- This function checks that the cal_type calendar category
171 -- is 'TEACHING' and validates the closed indicator for an
172 -- IGS_ST_DFT_LOAD_APPO record being inserted with each
173 -- teach_cal_type.
174 --- Set the default message number
175 p_message_name := null;
176 OPEN c_ct;
177 FETCH c_ct INTO v_closed_ind,
178 v_s_cal_cat;
179 IF (c_ct%NOTFOUND) THEN
180 -- no records found
181 CLOSE c_ct;
182 RETURN TRUE;
183 END IF;
184 CLOSE c_ct;
185 IF (v_closed_ind = 'Y') THEN
186 -- calandar type is closed
187 p_message_name := 'IGS_CA_CALTYPE_CLOSED';
188 RETURN FALSE;
189 END IF;
190 IF (v_s_cal_cat <> 'TEACHING') THEN
191 -- calandar type must be a teaching period calendar
192 p_message_name := 'IGS_EN_CAL_TYPE_MUST_BE_TEACH';
193 RETURN FALSE;
194 END IF;
195 --- Return the default value
196 RETURN TRUE;
197 END;
198 EXCEPTION
199 WHEN OTHERS THEN
200 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
201 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_DLA.enrp_val_dla_cat');
202 IGS_GE_MSG_STACK.ADD;
203 App_Exception.Raise_Exception;
204
205 END enrp_val_dla_cat;
206 END IGS_EN_VAL_DLA;