1 PACKAGE BODY IGS_CA_VAL_DAIOC AS
2 /* $Header: IGSCA09B.pls 115.3 2002/11/28 22:58:13 nsidana ship $ */
3 -- Ensure dt alias instance offset constraints can be created.
4 FUNCTION calp_val_daioc_ins(
5 p_cal_type IN VARCHAR2 ,
6 p_message_name OUT NOCOPY VARCHAR2 )
7 RETURN BOOLEAN AS
8 gv_other_detail VARCHAR2(255);
9 BEGIN -- calp_val_daioc_ins
10 -- Validate IGS_CA_TYPE.s_cal_cat. If IGS_CA_TYPE.s_cal_cat = ?HOLIDAY?, then
11 -- offset constraints cannot be defined.
12 DECLARE
13 CURSOR c_ct(
14 cp_cal_type IGS_CA_TYPE.cal_type%TYPE) IS
15 SELECT s_cal_cat
16 FROM IGS_CA_TYPE
17 WHERE cal_type = cp_cal_type;
18 v_ct_rec c_ct%ROWTYPE;
19 cst_holiday CONSTANT VARCHAR2(7) := 'HOLIDAY';
20 BEGIN
21 -- Set the default message number
22 p_message_name := NULL;
23 -- Check parameters
24 IF p_cal_type IS NULL THEN
25 RETURN TRUE;
26 END IF;
27 -- Get the system calendar category of the IGS_CA_TYPE.
28 OPEN c_ct (p_cal_type);
29 FETCH c_ct INTO v_ct_rec;
30 IF c_ct%NOTFOUND THEN
31 CLOSE c_ct;
32 RETURN TRUE;
33 END IF;
34 CLOSE c_ct;
35 IF v_ct_rec.s_cal_cat = cst_holiday THEN
36 p_message_name := 'IGS_CA_NOTDEFINE_HOLIDAY_CAT';
37 RETURN FALSE;
38 END IF;
39 -- Return the default value
40 RETURN TRUE;
41 END;
42 EXCEPTION
43 WHEN OTHERS THEN
44 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
45 FND_MESSAGE.SET_TOKEN('NAME','IGS_CA_VAL_DAIOC.calp_val_daioc_ins');
46 IGS_GE_MSG_STACK.ADD;
47 App_Exception.Raise_Exception;
48 END calp_val_daioc_ins;
49 --
50 -- Validate dt alias offset constraints do not clash.
51 FUNCTION calp_val_sdoct_clash(
52 p_dt_alias IN VARCHAR2 ,
53 p_offset_dt_alias IN VARCHAR2 ,
54 p_dai_sequence_number IN NUMBER ,
55 p_offset_dai_sequence_number IN NUMBER ,
56 p_cal_type IN VARCHAR2 ,
57 p_offset_cal_type IN VARCHAR2 ,
58 p_ci_sequence_number IN NUMBER ,
59 p_offset_ci_sequence_number IN NUMBER ,
60 p_s_dt_offset_constraint_type IN VARCHAR2 ,
61 p_constraint_condition IN VARCHAR2 ,
62 p_constraint_resolution IN NUMBER ,
63 p_message_name OUT NOCOPY VARCHAR2 )
64 RETURN BOOLEAN AS
65 gv_other_detail VARCHAR2(255);
66 BEGIN -- calp_val_sdoct_clash
67 -- Validate that IGS_CA_DA_OFFCNT or IGS_CA_DA_INST_OFCNT records
68 -- do not result in constraints that cannot be resolved. (eg. MUST BE MONDAY,
69 -- MUST BE WEDNESDAY).
70 -- Note that the primary keys prevent cases such as MUST BE MONDAY, MUST NOT
71 -- BE MONDAY from occurring.
72 -- Refer to S_DT_OFFSET_CONSTRAINT_TYPE table for list of valid constraint
73 -- types.
74 DECLARE
75 v_message_name VARCHAR2(30);
76 CURSOR c_daoc IS
77 SELECT daoc.s_dt_offset_constraint_type,
78 daoc.constraint_condition,
79 daoc.constraint_resolution
80 FROM IGS_CA_DA_OFFCNT daoc
81 WHERE daoc.dt_alias = p_dt_alias AND
82 daoc.offset_dt_alias = p_offset_dt_alias AND
83 daoc.s_dt_offset_constraint_type <> p_s_dt_offset_constraint_type;
84 CURSOR c_daioc IS
85 SELECT daioc.s_dt_offset_constraint_type,
86 daioc.constraint_condition,
87 daioc.constraint_resolution
88 FROM IGS_CA_DA_INST_OFCNT daioc
89 WHERE daioc.dt_alias = p_dt_alias AND
90 daioc.dai_sequence_number = p_dai_sequence_number AND
91 daioc.cal_type = p_cal_type AND
92 daioc.ci_sequence_number = p_ci_sequence_number AND
93 daioc.offset_dt_alias = p_offset_dt_alias AND
94 daioc.offset_dai_sequence_number = p_offset_dai_sequence_number AND
95 daioc.offset_cal_type = p_offset_cal_type AND
96 daioc.offset_ci_sequence_number = p_offset_ci_sequence_number AND
97 daioc.s_dt_offset_constraint_type <> p_s_dt_offset_constraint_type;
98 FUNCTION calpl_val_constraint (
99 p_s_dt_offset_constraint_type
100 IGS_CA_DA_OFFCNT.s_dt_offset_constraint_type%TYPE,
101 p_constraint_condition IGS_CA_DA_OFFCNT.constraint_condition%TYPE,
102 p_db_s_dt_offset_cstrnt_type
103 IGS_CA_DA_OFFCNT.s_dt_offset_constraint_type%TYPE,
104 p_db_constraint_condition IGS_CA_DA_OFFCNT.constraint_condition%TYPE,
105 p_db_constraint_resolution IGS_CA_DA_OFFCNT.constraint_resolution%TYPE)
106 RETURN VARCHAR2
107 AS
108 BEGIN
109 DECLARE
110 cst_must CONSTANT VARCHAR2(10) := 'MUST';
111 cst_must_not CONSTANT VARCHAR2(10) := 'MUST NOT';
112 cst_monday CONSTANT VARCHAR2(10) := 'MONDAY';
113 cst_tuesday CONSTANT VARCHAR2(10) := 'TUESDAY';
114 cst_wednesday CONSTANT VARCHAR2(10) := 'WEDNESDAY';
115 cst_thursday CONSTANT VARCHAR2(10) := 'THURSDAY';
116 cst_friday CONSTANT VARCHAR2(10) := 'FRIDAY';
117 cst_saturday CONSTANT VARCHAR2(10) := 'SATURDAY';
118 cst_sunday CONSTANT VARCHAR2(10) := 'SUNDAY';
119 cst_week_day CONSTANT VARCHAR2(10) := 'WEEK DAY';
120 cst_holiday CONSTANT VARCHAR2(10) := 'HOLIDAY';
121 BEGIN
122 -- If both constraint types are particular days of the week, then check that
123 -- the constraint conditions are not both set to 'MUST'. If so, an
124 -- unresolvable situation will occur.
125 IF p_s_dt_offset_constraint_type IN ( cst_monday,
126 cst_tuesday,
127 cst_wednesday,
128 cst_thursday,
129 cst_friday,
130 cst_saturday,
131 cst_sunday) AND
132 p_constraint_condition = cst_must AND
133 p_db_s_dt_offset_cstrnt_type IN ( cst_monday,
134 cst_tuesday,
135 cst_wednesday,
136 cst_thursday,
137 cst_friday,
138 cst_saturday,
139 cst_sunday) AND
140 p_db_constraint_condition = cst_must THEN
141 RETURN 'IGS_CA_CONSTRAINTS_CONFLICT';
142 END IF;
143 -- If both constraint types are particular days of the week and both
144 -- constraint conditions are set to 'MUST NOT', check that the resolution
145 -- values will allow the constraint to be resolved.
146 -- eg. MUST NOT BE MONDAY (+4) combined with MUST NOT BE FRIDAY (+3),
147 -- will result in an unsolvable situation.
148 IF p_s_dt_offset_constraint_type IN ( cst_monday,
149 cst_tuesday,
150 cst_wednesday,
151 cst_thursday,
152 cst_friday,
153 cst_saturday,
154 cst_sunday) AND
155 p_constraint_condition = cst_must_not AND
156 p_db_s_dt_offset_cstrnt_type IN ( cst_monday,
157 cst_tuesday,
158 cst_wednesday,
159 cst_thursday,
160 cst_friday,
161 cst_saturday,
162 cst_sunday) AND
163 p_db_constraint_condition = cst_must_not THEN
164 IF (p_constraint_resolution +
165 p_db_constraint_resolution) IN (7, -7, 0) THEN
166 RETURN 'IGS_CA_MUSTNOT_CONST_UNRSLVD';
167 END IF;
168 END IF;
169 -- If current constraint type is a weekend day and the constraint type of
170 -- the fetched record is 'WEEK DAY', check that the constraint conditions
171 -- are different. Vice-versa.
172 IF (( p_s_dt_offset_constraint_type IN ( cst_saturday,
173 cst_sunday) AND
174 p_db_s_dt_offset_cstrnt_type = cst_week_day
175 )
176 OR -- vice-versa
177 ( p_s_dt_offset_constraint_type = cst_week_day AND
178 p_db_s_dt_offset_cstrnt_type IN ( cst_saturday,
179 cst_sunday)
180 )
181 ) THEN
182 IF p_constraint_condition = cst_must AND
183 p_db_constraint_condition = cst_must THEN
184 RETURN 'IGS_CA_CONSTRAINTS_CONFLICT';
185 END IF;
186 END IF;
187 -- If current constraint type is a week day and the constraint type of the
188 -- fetched record is 'WEEK DAY', check that the constraint conditions are
189 -- not different. Vice-versa
190 IF (( p_s_dt_offset_constraint_type IN ( cst_monday,
191 cst_tuesday,
192 cst_wednesday,
193 cst_thursday,
194 cst_friday) AND
195 p_db_s_dt_offset_cstrnt_type = cst_week_day AND
196 p_constraint_condition = 'MUST' AND
197 p_db_constraint_condition = 'MUST NOT'
198 )
199 OR -- vice-versa
200 ( p_s_dt_offset_constraint_type = cst_week_day AND
201 p_db_s_dt_offset_cstrnt_type IN ( cst_monday,
202 cst_tuesday,
203 cst_wednesday,
204 cst_thursday,
205 cst_friday) AND
206 p_constraint_condition = 'MUST NOT' AND
207 p_db_constraint_condition = 'MUST'
208 )) THEN
209 RETURN 'IGS_CA_CONSTRAINTS_CONFLICT';
210 END IF;
211 -- If current constraint type is 'HOLIDAY'and the constraint type of the
212 -- fetched record is 'SATURDAY' or 'SUNDAY', check that the conditions
213 -- do not clash.
214 -- Note : This check does not cause the function to return FALSE. Processing
215 -- continues and if no further checks cause an error, the function will
216 -- return TRUE and the message number will be recognised as a warning.
217 IF (( p_s_dt_offset_constraint_type = cst_holiday AND
218 p_db_s_dt_offset_cstrnt_type IN ( cst_saturday,
219 cst_sunday)
220 )
221 OR -- vice-versa
222 ( p_s_dt_offset_constraint_type IN ( cst_saturday,
223 cst_sunday) AND
224 p_db_s_dt_offset_cstrnt_type = cst_holiday
225 )
226 ) AND
227 p_constraint_condition = cst_must AND
228 p_db_constraint_condition = cst_must THEN
229 RETURN 'IGS_CA_INVALID_CONSTRAINT';
230 END IF;
231 IF p_s_dt_offset_constraint_type = cst_holiday AND
232 p_db_s_dt_offset_cstrnt_type = cst_week_day AND
233 p_constraint_condition = cst_must AND
234 p_db_constraint_condition = cst_must_not THEN
235 RETURN 'IGS_CA_INVALID_CONSTRAINT';
236 END IF;
237 -- Vice-versa
238 IF p_s_dt_offset_constraint_type = cst_week_day AND
239 p_db_s_dt_offset_cstrnt_type = cst_holiday AND
240 p_constraint_condition = cst_must_not AND
241 p_db_constraint_condition = cst_must THEN
242 RETURN 'IGS_CA_INVALID_CONSTRAINT';
243 END IF;
244 RETURN NULL;
245 END;
246 END calpl_val_constraint;
247 BEGIN
248 -- Set default value.
249 p_message_name := NULL;
250 v_message_name := NULL;
251 -- 1. Check parameters
252 IF ( p_dt_alias IS NULL OR
253 p_offset_dt_alias IS NULL OR
254 p_s_dt_offset_constraint_type IS NULL OR
255 p_constraint_condition IS NULL OR
256 p_constraint_resolution IS NULL) THEN
257 RETURN TRUE;
258 END IF;
259 -- 2. Check constraint type / constraint resolution.
260 -- If constraint resolution is zero, resolution will be impossible.
261 IF p_constraint_resolution = 0 THEN
262 p_message_name := 'IGS_GE_INVALID_VALUE';
263 RETURN FALSE;
264 END IF;
265 -- If constraint type is a particular day, check that the resolution is not
266 -- plus or minus 7, as this will result in an unresolvable situation.
267 IF p_s_dt_offset_constraint_type IN ( 'MONDAY',
268 'TUESDAY',
269 'WEDNESDAY',
270 'THURSDAY',
271 'FRIDAY',
272 'SATURDAY',
273 'SUNDAY',
274 'WEEK DAY') AND
275 p_constraint_resolution IN (7, -7, 0) THEN
276 p_message_name := 'IGS_CA_CONSTRAINT_NOT_RESOLVE';
277 RETURN FALSE;
278 END IF;
279 -- 3. Use a loop to select each existing constraint record and determine if
280 -- the current s_dt_offset_constraint_type clashes with an existing
281 -- s_dt_offset_constraint_type for the same dt_alias/offset_dt_alias.
282 IF p_cal_type IS NULL THEN
283 -- function has been called from IGS_CA_DA_OFFCNT
284 FOR v_daoc_rec IN c_daoc LOOP
285 v_message_name := calpl_val_constraint(
286 p_s_dt_offset_constraint_type,
287 p_constraint_condition,
288 v_daoc_rec.s_dt_offset_constraint_type,
289 v_daoc_rec.constraint_condition,
290 v_daoc_rec.constraint_resolution);
291 IF v_message_name IN ('IGS_CA_CONSTRAINTS_CONFLICT','IGS_CA_CONSTRAINT_NOT_RESOLVE','IGS_CA_MUSTNOT_CONST_UNRSLVD')
292 THEN
293 p_message_name := v_message_name;
294 EXIT;
295 ELSIF v_message_name = 'IGS_CA_INVALID_CONSTRAINT' THEN
296 p_message_name := v_message_name;
297 -- continue check next record.
298 ELSE
299 -- continue check next record.
300 NULL;
301 END IF;
302 END LOOP;
303 ELSE
304 -- function has been called from IGS_CA_DA_INST_OFCNT
305 FOR v_daioc_rec IN c_daioc LOOP
306 v_message_name := calpl_val_constraint(
307 p_s_dt_offset_constraint_type,
308 p_constraint_condition,
309 v_daioc_rec.s_dt_offset_constraint_type,
310 v_daioc_rec.constraint_condition,
311 v_daioc_rec.constraint_resolution);
312 IF v_message_name IN ('IGS_CA_CONSTRAINTS_CONFLICT','IGS_CA_CONSTRAINT_NOT_RESOLVE','IGS_CA_MUSTNOT_CONST_UNRSLVD')
313 THEN
314 p_message_name := v_message_name;
315 EXIT;
316 ELSIF v_message_name = 'IGS_CA_INVALID_CONSTRAINT' THEN
317 p_message_name := v_message_name;
318 -- continue check next record.
319 ELSE
320 -- continue check next record.
321 NULL;
322 END IF;
323 END LOOP;
324 END IF;
325 IF v_message_name IS NULL OR
326 v_message_name = 'IGS_CA_INVALID_CONSTRAINT' THEN
327 RETURN TRUE;
328 ELSE
329 RETURN FALSE;
330 END IF;
331 END;
332
333 END calp_val_sdoct_clash;
334 --
335 -- Validate if date alias instance offset constraints exist.
336 FUNCTION calp_val_daioc_exist(
337 p_dt_alias IN VARCHAR2 ,
338 p_dai_sequence_number IN NUMBER ,
339 p_cal_type IN VARCHAR2 ,
340 p_ci_sequence_number IN NUMBER ,
341 p_message_name OUT NOCOPY VARCHAR2 )
342 RETURN BOOLEAN AS
343 gv_other_detail VARCHAR2(255);
344 BEGIN --calp_val_daioc_exist
345 --This module Validates if date alias instance offset constraints
346 -- exist for the date alias instance offset.
347 DECLARE
348 v_daioc_exists VARCHAR2(1);
349 CURSOR c_daioc IS
350 SELECT 'X'
351 FROM IGS_CA_DA_INST_OFST daio,
352 IGS_CA_DA_INST_OFCNT daioc
353 WHERE daio.dt_alias = p_dt_alias AND
354 daio.dai_sequence_number = p_dai_sequence_number AND
355 daio.cal_type = p_cal_type AND
356 daio.ci_sequence_number = p_ci_sequence_number AND
357 daioc.dt_alias = daio.dt_alias AND
358 daioc.dai_sequence_number = daio.dai_sequence_number AND
359 daioc.cal_type = daio.cal_type AND
360 daioc.ci_sequence_number = daio.ci_sequence_number;
361 BEGIN
362 --Set the default message number
363 p_message_name := NULL;
364 --If record exists then constraints exist, therefore set
365 -- p_message_name (warning only).
366 OPEN c_daioc;
367 FETCH c_daioc INTO v_daioc_exists;
368 IF (c_daioc%FOUND) THEN
369 p_message_name := 'IGS_CA_CONFLICTING_CONSTRAINT';
370 CLOSE c_daioc;
371 RETURN FALSE;
372 END IF;
373 CLOSE c_daioc;
374 RETURN TRUE;
375 END;
376 END calp_val_daioc_exist;
377 --
378 -- Validate if offset constraint type code is closed.
379 FUNCTION calp_val_sdoct_clsd(
380 p_s_dt_offset_constraint_type IN VARCHAR2 ,
381 p_message_name OUT NOCOPY VARCHAR2 )
382 RETURN BOOLEAN AS
383 gv_other_detail VARCHAR2(255);
384 BEGIN
385 DECLARE
386 v_closed_ind IGS_LOOKUPS_VIEW.closed_ind%TYPE;
387 CURSOR c_sdoct IS
388 SELECT sdoct.closed_ind
389 FROM IGS_LOOKUPS_VIEW sdoct
390 WHERE sdoct.LOOKUP_CODE = p_s_dt_offset_constraint_type
391 AND sdoct.LOOKUP_TYPE = 'DT_OFFSET_CONSTRAINT_TYPE';
392 BEGIN
393 -- Validate if S_DT_OFFSET_CONSTRAINT_TYPE.s_dt_offset_constraint_type
394 -- is closed.
395 OPEN c_sdoct;
396 FETCH c_sdoct INTO v_closed_ind;
397 IF (c_sdoct%FOUND) THEN
398 IF (v_closed_ind = 'Y') THEN
399 CLOSE c_sdoct;
400 p_message_name := 'IGS_CA_SYSOFFSET_CONSTYPE_CLS';
401 RETURN FALSE;
402 END IF;
403 END IF;
404 CLOSE c_sdoct;
405 p_message_name := NULL;
406 RETURN TRUE;
407 END;
408 END calp_val_sdoct_clsd;
409 END IGS_CA_VAL_DAIOC;
[Home] [Help]