1 PACKAGE BODY igs_ca_val_sys_dt_type_pkg AS
2 /* $Header: IGSCA16B.pls 120.1 2005/08/11 05:39:26 appldev noship $ */
3 /*****************************************************
4 || Created By : Navin Sidana
5 || Created On : 11/4/2004
6 || Purpose : Package for validating System date types
7 || for each module.
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || nsidana 10/13/2004 Created
12 *****************************************************/
13
14 CURSOR chk_one_per_flag(cp_sys_date_type VARCHAR2)
15 IS
16 SELECT one_per_cal_flag
17 FROM IGS_CA_DA_CONFIGS
18 WHERE sys_date_type = cp_sys_date_type;
19
20 l_err_msg varchar2(30);
21 l_one_per_cal_flag varchar2(1);
22
23
24 FUNCTION chk_one_per_cal(p_dt_alias IN VARCHAR2,
25 p_cal_type IN VARCHAR2,
26 p_seq_num IN NUMBER) RETURN VARCHAR2
27 IS
28 CURSOR count_dai_ci
29 IS
30 SELECT count(*)
31 FROM IGS_CA_DA_INST
32 WHERE dt_alias = p_dt_alias AND
33 cal_type = p_cal_type AND
34 ci_sequence_number = p_seq_num;
35
36 l_count NUMBER := 0;
37 BEGIN
38 OPEN count_dai_ci;
39 FETCH count_dai_ci INTO l_count;
40 CLOSE count_dai_ci;
41
42 IF (l_count > 1)
43 THEN
44 return 'IGS_CA_DA_GR_THAN_ONE_INST';
45 ELSE
46 return NULL;
47 END IF;
48 END chk_one_per_cal;
49
50 PROCEDURE execute_validation_proc(proc_name IN VARCHAR2,p_sys_date IN VARCHAR2,p_dt_alias IN VARCHAR2,p_cal_type IN VARCHAR2,p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
51 IS
52 l_stmt varchar2(2000) := null;
53 l_out varchar2(2000);
54 BEGIN
55 l_stmt := 'BEGIN '||proc_name||'( p_sys_date_type => :1,p_dt_alias => :2,p_cal_type => :3,p_seq_num => :4,p_err_msg_list => :5); END;';
56 EXECUTE IMMEDIATE(l_stmt) USING p_sys_date,p_dt_alias,p_cal_type,p_seq_num, OUT l_out;
57 p_err_msg_list := l_out;
58 RETURN;
59 EXCEPTION
60 WHEN OTHERS THEN
61 p_err_msg_list := NULL;
62 END;
63
64 PROCEDURE val_ad_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
65 IS
66 BEGIN
67 p_err_msg_list := NULL;
68 l_one_per_cal_flag := 'N';
69 OPEN chk_one_per_flag(p_sys_date_type);
70 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
71 CLOSE chk_one_per_flag;
72 IF (l_one_per_cal_flag = 'Y')
73 THEN
74 -- check if the passed DA violates the one per cal restriction.
75 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
76 IF (l_err_msg IS NOT NULL) THEN
77 p_err_msg_list := p_err_msg_list||'*'||l_err_msg;
78 END IF;
79 END IF;
80 /*
81 More logic to be coded by individual teams
82 */
83 RETURN;
84 EXCEPTION
85 WHEN OTHERS THEN
86 null;
87 END;
88
89 PROCEDURE val_en_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
90 IS
91 BEGIN
92 p_err_msg_list := NULL;
93 l_one_per_cal_flag := 'N';
94 OPEN chk_one_per_flag(p_sys_date_type);
95 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
96 CLOSE chk_one_per_flag;
97 IF (l_one_per_cal_flag = 'Y')
98 THEN
99 -- check if the passed DA violates the one per cal restriction.
100 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
101 IF (l_err_msg IS NOT NULL) THEN
102 p_err_msg_list := p_err_msg_list||l_err_msg;
103 END IF;
104 END IF;
105 /*
106 More logic to be coded by individual teams
107 */
108 RETURN;
109 EXCEPTION
110 WHEN OTHERS THEN
111 null;
112 END;
113
114 PROCEDURE val_rec_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
115 IS
116 BEGIN
117 p_err_msg_list := NULL;
118 l_one_per_cal_flag := 'N';
119 OPEN chk_one_per_flag(p_sys_date_type);
120 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
121 CLOSE chk_one_per_flag;
122 IF (l_one_per_cal_flag = 'Y')
123 THEN
124 -- check if the passed DA violates the one per cal restriction.
125 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
126 IF (l_err_msg IS NOT NULL) THEN
127 p_err_msg_list := p_err_msg_list||l_err_msg;
128 END IF;
129 END IF;
130 /*
131 More logic to be coded by individual teams
132 */
133 RETURN;
134 EXCEPTION
135 WHEN OTHERS THEN
136 null;
137 END;
138
139 PROCEDURE val_fi_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
140 IS
141 BEGIN
142 p_err_msg_list := NULL;
143 l_one_per_cal_flag := 'N';
144 OPEN chk_one_per_flag(p_sys_date_type);
145 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
146 CLOSE chk_one_per_flag;
147 IF (l_one_per_cal_flag = 'Y')
148 THEN
149 -- check if the passed DA violates the one per cal restriction.
150 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
151 IF (l_err_msg IS NOT NULL) THEN
152 p_err_msg_list := p_err_msg_list||l_err_msg;
153 END IF;
154 END IF;
155 /*
156 More logic to be coded by individual teams
157 */
158 RETURN;
159 EXCEPTION
160 WHEN OTHERS THEN
161 null;
162 END;
163
164 PROCEDURE val_ps_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
165 IS
166 BEGIN
167 p_err_msg_list := NULL;
168 l_one_per_cal_flag := 'N';
169 OPEN chk_one_per_flag(p_sys_date_type);
170 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
171 CLOSE chk_one_per_flag;
172 IF (l_one_per_cal_flag = 'Y')
173 THEN
174 -- check if the passed DA violates the one per cal restriction.
175 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
176 IF (l_err_msg IS NOT NULL) THEN
177 p_err_msg_list := p_err_msg_list||l_err_msg;
178 END IF;
179 END IF;
180 /*
181 More logic to be coded by individual teams
182 */
183 RETURN;
184 EXCEPTION
185 WHEN OTHERS THEN
186 null;
187 END;
188
189 PROCEDURE val_sws_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
190 IS
191 BEGIN
192 p_err_msg_list := NULL;
193 l_one_per_cal_flag := 'N';
194 OPEN chk_one_per_flag(p_sys_date_type);
195 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
196 CLOSE chk_one_per_flag;
197 IF (l_one_per_cal_flag = 'Y')
198 THEN
199 -- check if the passed DA violates the one per cal restriction.
200 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
201 IF (l_err_msg IS NOT NULL) THEN
202 p_err_msg_list := p_err_msg_list||l_err_msg;
203 END IF;
204 END IF;
205 /*
206 More logic to be coded by individual teams
207 */
208 RETURN;
209 EXCEPTION
210 WHEN OTHERS THEN
211 null;
212 END;
213
214 PROCEDURE val_rct_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
215 IS
216 BEGIN
217 p_err_msg_list := NULL;
218 l_one_per_cal_flag := 'N';
219 OPEN chk_one_per_flag(p_sys_date_type);
220 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
221 CLOSE chk_one_per_flag;
222 IF (l_one_per_cal_flag = 'Y')
223 THEN
224 -- check if the passed DA violates the one per cal restriction.
225 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
226 IF (l_err_msg IS NOT NULL) THEN
227 p_err_msg_list := p_err_msg_list||l_err_msg;
228 END IF;
229 END IF;
230 /*
231 More logic to be coded by individual teams
232 */
233 RETURN;
234 EXCEPTION
235 WHEN OTHERS THEN
236 null;
237 END;
238
239 PROCEDURE val_fa_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
240 IS
241 BEGIN
242 p_err_msg_list := NULL;
243 l_one_per_cal_flag := 'N';
244 OPEN chk_one_per_flag(p_sys_date_type);
245 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
246 CLOSE chk_one_per_flag;
247 IF (l_one_per_cal_flag = 'Y')
248 THEN
249 -- check if the passed DA violates the one per cal restriction.
250 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
251 IF (l_err_msg IS NOT NULL) THEN
252 p_err_msg_list := p_err_msg_list||l_err_msg;
253 END IF;
254 END IF;
255 /*
256 More logic to be coded by individual teams
257 */
258 RETURN;
259 EXCEPTION
260 WHEN OTHERS THEN
261 null;
262 END;
263
264 PROCEDURE val_ucas_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
265 IS
266 BEGIN
267 p_err_msg_list := NULL;
268 l_one_per_cal_flag := 'N';
269 OPEN chk_one_per_flag(p_sys_date_type);
270 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
271 CLOSE chk_one_per_flag;
272 IF (l_one_per_cal_flag = 'Y')
273 THEN
274 -- check if the passed DA violates the one per cal restriction.
275 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
276 IF (l_err_msg IS NOT NULL) THEN
277 p_err_msg_list := p_err_msg_list||l_err_msg;
278 END IF;
279 END IF;
280 /*
281 More logic to be coded by individual teams
282 */
283 RETURN;
284 EXCEPTION
285 WHEN OTHERS THEN
286 null;
287 END;
288
289 PROCEDURE val_hesa_sda(p_sys_date_type IN VARCHAR2,p_dt_alias IN VARCHAR2, p_cal_type IN VARCHAR2, p_seq_num IN NUMBER,p_err_msg_list OUT NOCOPY VARCHAR2)
290 IS
291 BEGIN
292 p_err_msg_list := NULL;
293 l_one_per_cal_flag := 'N';
294 OPEN chk_one_per_flag(p_sys_date_type);
295 FETCH chk_one_per_flag INTO l_one_per_cal_flag;
296 CLOSE chk_one_per_flag;
297 IF (l_one_per_cal_flag = 'Y')
298 THEN
299 -- check if the passed DA violates the one per cal restriction.
300 l_err_msg := chk_one_per_cal(p_dt_alias,p_cal_type,p_seq_num);
301 IF (l_err_msg IS NOT NULL) THEN
302 p_err_msg_list := p_err_msg_list||l_err_msg;
303 END IF;
304 END IF;
305 /*
306 More logic to be coded by individual teams
307 */
308 RETURN;
309 EXCEPTION
310 WHEN OTHERS THEN
311 null;
312 END;
313 END igs_ca_val_sys_dt_type_pkg;
[Home] [Help]