1 package body JTF_CAL_EXCEPTION_ASSIGN_PKG as
2 /* $Header: jtfcleab.pls 115.18 2002/11/15 14:42:10 sukulkar ship $ */
3 procedure INSERT_ROW (
4 X_ERROR out NOCOPY VARCHAR2,
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_CAL_EXCEPTION_ASSIGN_ID in out NOCOPY NUMBER,
7 X_OBJECT_VERSION_NUMBER in NUMBER,
8 X_EXCEPTION_REASON in VARCHAR2,
9 X_CALENDAR_ID in NUMBER,
10 X_EXCEPTION_ID in NUMBER,
11 X_START_DATE_ACTIVE in DATE,
12 X_END_DATE_ACTIVE in DATE,
13 X_ATTRIBUTE1 in VARCHAR2,
14 X_ATTRIBUTE2 in VARCHAR2,
15 X_ATTRIBUTE3 in VARCHAR2,
16 X_ATTRIBUTE4 in VARCHAR2,
17 X_ATTRIBUTE5 in VARCHAR2,
18 X_ATTRIBUTE6 in VARCHAR2,
19 X_ATTRIBUTE7 in VARCHAR2,
20 X_ATTRIBUTE8 in VARCHAR2,
21 X_ATTRIBUTE9 in VARCHAR2,
22 X_ATTRIBUTE10 in VARCHAR2,
23 X_ATTRIBUTE11 in VARCHAR2,
24 X_ATTRIBUTE12 in VARCHAR2,
25 X_ATTRIBUTE13 in VARCHAR2,
26 X_ATTRIBUTE14 in VARCHAR2,
27 X_ATTRIBUTE15 in VARCHAR2,
28 X_ATTRIBUTE_CATEGORY in VARCHAR2,
29 X_CREATION_DATE in DATE,
30 X_CREATED_BY in NUMBER,
31 X_LAST_UPDATE_DATE in DATE,
32 X_LAST_UPDATED_BY in NUMBER,
33 X_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35
36 cursor C is select ROWID from JTF_CAL_EXCEPTION_ASSIGN
37 where CAL_EXCEPTION_ASSIGN_ID = X_CAL_EXCEPTION_ASSIGN_ID;
38
39 v_error CHAR := 'N';
40 v_cal_exception_assign_id_s NUMBER;
41 begin
42 fnd_msg_pub.initialize;
43 IF JTF_CAL_EXCEPTION_ASSIGN_PKG.duplicate_excep(X_CALENDAR_ID,X_EXCEPTION_ID,
44 X_EXCEPTION_REASON, X_START_DATE_ACTIVE,
45 -- nvl(X_END_DATE_ACTIVE,'31-DEC-4712')) = FALSE THEN
46 --changed this for nls issue inavlid month .. sudarsana 12th Nov 2001
47 nvl(X_END_DATE_ACTIVE,fnd_api.g_miss_date)) = FALSE THEN
48 fnd_message.set_name('JTF','JTF_CAL_EXCEPTION_EXISTS');
49 -- fnd_message.set_name('JTF', 'JTF_CAL_ALREADY_EXISTS');
50 -- fnd_message.set_token('P_Name', X_EXCEPTION_REASON);
51 fnd_msg_pub.add;
52 v_error := 'Y';
53 END IF;
54
55 /*IF JTF_CAL_EXCEPTION_ASSIGN_PKG.NOT_NULL(X_EXCEPTION_ID) = FALSE THEN
56 --fnd_message.set_name('JTF', 'EXCEPTION_ID CANNOT BE NULL');
57 --app_exception.raise_exception;
58 fnd_message.set_name('JTF', 'JTF_CAL_REQUIRED');
59 fnd_message.set_token('P_Name', X_EXCEPTION_ID);
60 fnd_msg_pub.add;
61 v_error := 'Y';
62 END IF;
63 */
64
65 IF JTF_CAL_EXCEPTION_ASSIGN_PKG.NOT_NULL(X_START_DATE_ACTIVE) = FALSE THEN
66 --fnd_message.set_name('JTF', 'START_DATE CANNOT BE NULL');
67 --app_exception.raise_exception;
68 fnd_message.set_name('JTF', 'JTF_CAL_START_DATE');
69 fnd_msg_pub.add;
70 v_error := 'Y';
71 END IF;
72
73 IF JTF_CAL_EXCEPTION_ASSIGN_PKG.END_GREATER_THAN_BEGIN(X_START_DATE_ACTIVE, X_END_DATE_ACTIVE) = FALSE THEN
74 --fnd_message.set_name('JTF', 'END_DATE IS INCORRECT');
75 --app_exception.raise_exception;
76 fnd_message.set_name('JTF', 'JTF_CAL_END_DATE');
77 fnd_message.set_token('P_Start_Date', X_START_DATE_ACTIVE);
78 fnd_message.set_token('P_End_Date', X_END_DATE_ACTIVE);
79 fnd_msg_pub.add;
80 v_error := 'Y';
81 END IF;
82
83 IF v_error = 'Y' THEN
84 X_ERROR := 'Y';
85 return;
86 ELSE
87
88 SELECT jtf_cal_exception_assign_s.nextval
89 INTO v_cal_exception_assign_id_s
90 FROM dual;
91 X_CAL_EXCEPTION_ASSIGN_ID := v_cal_exception_assign_id_s;
92
93 insert into JTF_CAL_EXCEPTION_ASSIGN (
94 OBJECT_VERSION_NUMBER,
95 CAL_EXCEPTION_ASSIGN_ID,
96 EXCEPTION_REASON,
97 CALENDAR_ID,
98 EXCEPTION_ID,
99 START_DATE_ACTIVE,
100 END_DATE_ACTIVE,
101 CREATED_BY,
102 CREATION_DATE,
103 LAST_UPDATED_BY,
104 LAST_UPDATE_DATE,
105 LAST_UPDATE_LOGIN,
106 ATTRIBUTE1,
107 ATTRIBUTE2,
108 ATTRIBUTE3,
109 ATTRIBUTE4,
110 ATTRIBUTE5,
111 ATTRIBUTE6,
112 ATTRIBUTE7,
113 ATTRIBUTE8,
114 ATTRIBUTE9,
115 ATTRIBUTE10,
116 ATTRIBUTE11,
117 ATTRIBUTE12,
118 ATTRIBUTE13,
119 ATTRIBUTE14,
120 ATTRIBUTE15,
121 ATTRIBUTE_CATEGORY
122 ) values
123 ( 1,
124 v_CAL_EXCEPTION_ASSIGN_ID_S,
125 X_EXCEPTION_REASON,
126 X_CALENDAR_ID,
127 X_EXCEPTION_ID,
128 X_START_DATE_ACTIVE,
129 X_END_DATE_ACTIVE,
130 FND_GLOBAL.USER_ID,
131 sysdate,
132 FND_GLOBAL.USER_ID,
133 sysdate,
134 NULL,
135 X_ATTRIBUTE1,
136 X_ATTRIBUTE2,
137 X_ATTRIBUTE3,
138 X_ATTRIBUTE4,
139 X_ATTRIBUTE5,
140 X_ATTRIBUTE6,
141 X_ATTRIBUTE7,
142 X_ATTRIBUTE8,
143 X_ATTRIBUTE9,
144 X_ATTRIBUTE10,
145 X_ATTRIBUTE11,
146 X_ATTRIBUTE12,
147 X_ATTRIBUTE13,
148 X_ATTRIBUTE14,
149 X_ATTRIBUTE15,
150 X_ATTRIBUTE_CATEGORY);
151
152 -- open c;
153 -- fetch c into X_ROWID;
154 -- if (c%notfound) then
155 -- close c;
156 -- raise no_data_found;
157 -- end if;
158 -- close c;
159 END IF;
160 end INSERT_ROW;
161
162 procedure LOCK_ROW (
163 X_CAL_EXCEPTION_ASSIGN_ID in NUMBER,
164 X_OBJECT_VERSION_NUMBER in NUMBER,
165 X_EXCEPTION_REASON in VARCHAR2,
166 X_CALENDAR_ID in NUMBER,
167 X_EXCEPTION_ID in NUMBER,
168 X_START_DATE_ACTIVE in DATE,
169 X_END_DATE_ACTIVE in DATE,
170 X_ATTRIBUTE1 in VARCHAR2,
171 X_ATTRIBUTE2 in VARCHAR2,
172 X_ATTRIBUTE3 in VARCHAR2,
173 X_ATTRIBUTE4 in VARCHAR2,
174 X_ATTRIBUTE5 in VARCHAR2,
175 X_ATTRIBUTE6 in VARCHAR2,
176 X_ATTRIBUTE7 in VARCHAR2,
177 X_ATTRIBUTE8 in VARCHAR2,
178 X_ATTRIBUTE9 in VARCHAR2,
179 X_ATTRIBUTE10 in VARCHAR2,
180 X_ATTRIBUTE11 in VARCHAR2,
181 X_ATTRIBUTE12 in VARCHAR2,
182 X_ATTRIBUTE13 in VARCHAR2,
183 X_ATTRIBUTE14 in VARCHAR2,
184 X_ATTRIBUTE15 in VARCHAR2,
185 X_ATTRIBUTE_CATEGORY in VARCHAR2
186 ) is
187 cursor c1 is select
188 OBJECT_VERSION_NUMBER,
189 EXCEPTION_REASON,
190 CALENDAR_ID,
191 EXCEPTION_ID,
192 START_DATE_ACTIVE,
193 END_DATE_ACTIVE,
194 ATTRIBUTE1,
195 ATTRIBUTE2,
196 ATTRIBUTE3,
197 ATTRIBUTE4,
198 ATTRIBUTE5,
199 ATTRIBUTE6,
200 ATTRIBUTE7,
201 ATTRIBUTE8,
202 ATTRIBUTE9,
203 ATTRIBUTE10,
204 ATTRIBUTE11,
205 ATTRIBUTE12,
206 ATTRIBUTE13,
207 ATTRIBUTE14,
208 ATTRIBUTE15,
209 ATTRIBUTE_CATEGORY,
210 CAL_EXCEPTION_ASSIGN_ID
211 from JTF_CAL_EXCEPTION_ASSIGN
212 where CAL_EXCEPTION_ASSIGN_ID = X_CAL_EXCEPTION_ASSIGN_ID
213 for update of CAL_EXCEPTION_ASSIGN_ID nowait;
214 begin
215 for tlinfo in c1 loop
216 -- if (tlinfo.BASELANG = 'Y') then
217 if ( (tlinfo.CAL_EXCEPTION_ASSIGN_ID = X_CAL_EXCEPTION_ASSIGN_ID)
218 AND ((tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
219 OR ((tlinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
220 AND ((tlinfo.EXCEPTION_REASON = X_EXCEPTION_REASON)
221 OR ((tlinfo.EXCEPTION_REASON is null) AND (X_EXCEPTION_REASON is null)))
222 AND (tlinfo.CALENDAR_ID = X_CALENDAR_ID)
223 AND (tlinfo.EXCEPTION_ID = X_EXCEPTION_ID)
224 AND ((tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
225 OR ((tlinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
226 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
227 OR ((tlinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
228 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
229 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
230 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
231 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
232 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
233 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
234 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
235 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
236 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
237 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
238 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
239 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
240 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
241 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
242 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
243 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
244 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
245 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
246 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
247 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
248 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
249 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
250 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
251 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
252 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
253 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
254 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
255 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
256 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
257 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
258 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
259 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
260 ) then
261 null;
262 else
263 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
264 app_exception.raise_exception;
265 end if;
266 -- end if;
267 end loop;
268 return;
269 end LOCK_ROW;
270
271 procedure UPDATE_ROW (
272 X_ERROR out NOCOPY VARCHAR2,
273 X_CAL_EXCEPTION_ASSIGN_ID in NUMBER,
274 X_OBJECT_VERSION_NUMBER in OUT NOCOPY NUMBER,
275 X_EXCEPTION_REASON in VARCHAR2,
276 X_CALENDAR_ID in NUMBER,
277 X_EXCEPTION_ID in NUMBER,
278 X_START_DATE_ACTIVE in DATE,
279 X_END_DATE_ACTIVE in DATE,
280 X_ATTRIBUTE1 in VARCHAR2,
281 X_ATTRIBUTE2 in VARCHAR2,
282 X_ATTRIBUTE3 in VARCHAR2,
283 X_ATTRIBUTE4 in VARCHAR2,
284 X_ATTRIBUTE5 in VARCHAR2,
285 X_ATTRIBUTE6 in VARCHAR2,
286 X_ATTRIBUTE7 in VARCHAR2,
287 X_ATTRIBUTE8 in VARCHAR2,
288 X_ATTRIBUTE9 in VARCHAR2,
289 X_ATTRIBUTE10 in VARCHAR2,
290 X_ATTRIBUTE11 in VARCHAR2,
291 X_ATTRIBUTE12 in VARCHAR2,
292 X_ATTRIBUTE13 in VARCHAR2,
293 X_ATTRIBUTE14 in VARCHAR2,
294 X_ATTRIBUTE15 in VARCHAR2,
295 X_ATTRIBUTE_CATEGORY in VARCHAR2,
296 X_LAST_UPDATE_DATE in DATE,
297 X_LAST_UPDATED_BY in NUMBER,
298 X_LAST_UPDATE_LOGIN in NUMBER
299 ) is
300 v_error CHAR := 'N';
301 v_count NUMBER;
302 begin
303 fnd_msg_pub.initialize;
304 -- To check duplication of exception
305 SELECT count(*)
306 INTO v_count
307 FROM jtf_cal_exception_assign
308 WHERE calendar_id = X_CALENDAR_ID
309 and exception_id = X_EXCEPTION_ID;
310
311 IF v_count > 1 THEN
312 fnd_message.set_name('JTF','JTF_CAL_EXCEPTION_EXISTS');
313 fnd_msg_pub.add;
314 v_error := 'Y';
315 END IF;
316
317 /*
318 IF JTF_CAL_EXCEPTION_ASSIGN_PKG.duplicate_excep(X_CALENDAR_ID,X_EXCEPTION_ID, X_EXCEPTION_REASON,X_START_DATE_ACTIVE, nvl(X_END_DATE_ACTIVE,'31-DEC-4712')) = FALSE THEN
319 fnd_message.set_name('JTF','JTF_CAL_EXCEPTION_EXISTS');
320 fnd_msg_pub.add;
321 v_error := 'Y';
322 END IF;
323 */
324
325
326 IF JTF_CAL_EXCEPTION_ASSIGN_PKG.NOT_NULL(X_START_DATE_ACTIVE) = FALSE THEN
327 fnd_message.set_name('JTF', 'JTF_CAL_START_DATE');
328 fnd_msg_pub.add;
329 v_error := 'Y';
330 END IF;
331
332 IF JTF_CAL_EXCEPTION_ASSIGN_PKG.END_GREATER_THAN_BEGIN(X_START_DATE_ACTIVE, X_END_DATE_ACTIVE) = FALSE THEN
333 fnd_message.set_name('JTF', 'JTF_CAL_END_DATE');
334 fnd_message.set_token('P_Start_Date', X_START_DATE_ACTIVE);
335 fnd_message.set_token('P_End_Date', X_END_DATE_ACTIVE);
336 fnd_msg_pub.add;
337 v_error := 'Y';
338 END IF;
339
340 IF v_error = 'Y' THEN
341 X_ERROR := 'Y';
342 return;
343 ELSE
344 X_ERROR := 'N';
345 X_OBJECT_VERSION_NUMBER := X_OBJECT_VERSION_NUMBER + 1;
346
347 update JTF_CAL_EXCEPTION_ASSIGN set
348 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
349 EXCEPTION_REASON = X_EXCEPTION_REASON,
350 CALENDAR_ID = X_CALENDAR_ID,
351 EXCEPTION_ID = X_EXCEPTION_ID,
352 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
353 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
354 ATTRIBUTE1 = X_ATTRIBUTE1,
355 ATTRIBUTE2 = X_ATTRIBUTE2,
356 ATTRIBUTE3 = X_ATTRIBUTE3,
357 ATTRIBUTE4 = X_ATTRIBUTE4,
358 ATTRIBUTE5 = X_ATTRIBUTE5,
359 ATTRIBUTE6 = X_ATTRIBUTE6,
360 ATTRIBUTE7 = X_ATTRIBUTE7,
361 ATTRIBUTE8 = X_ATTRIBUTE8,
362 ATTRIBUTE9 = X_ATTRIBUTE9,
363 ATTRIBUTE10 = X_ATTRIBUTE10,
364 ATTRIBUTE11 = X_ATTRIBUTE11,
365 ATTRIBUTE12 = X_ATTRIBUTE12,
366 ATTRIBUTE13 = X_ATTRIBUTE13,
367 ATTRIBUTE14 = X_ATTRIBUTE14,
368 ATTRIBUTE15 = X_ATTRIBUTE15,
369 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
370 CAL_EXCEPTION_ASSIGN_ID = X_CAL_EXCEPTION_ASSIGN_ID,
371 LAST_UPDATE_DATE = sysdate,
372 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
373 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
374 where CAL_EXCEPTION_ASSIGN_ID = X_CAL_EXCEPTION_ASSIGN_ID;
375
376 -- if (sql%notfound) then
377 -- raise no_data_found;
378 -- end if;
379 END IF;
380 end UPDATE_ROW;
381
382 procedure DELETE_ROW (
383 X_CAL_EXCEPTION_ASSIGN_ID in NUMBER
384 ) is
385 begin
386 delete from JTF_CAL_EXCEPTION_ASSIGN
387 where CAL_EXCEPTION_ASSIGN_ID = X_CAL_EXCEPTION_ASSIGN_ID;
388
389 if (sql%notfound) then
390 raise no_data_found;
391 end if;
392
393 end DELETE_ROW;
394 /*************************************************************************/
395 FUNCTION not_null(column_to_check IN CHAR) RETURN boolean IS
396 BEGIN
397 IF column_to_check IS NULL THEN
398 return(FALSE);
399 ELSE
400 return(TRUE);
401 END IF;
402 END;
403 /*************************************************************************/
404 FUNCTION end_greater_than_begin(start_date IN DATE, end_date IN DATE) RETURN boolean IS
405 BEGIN
406 IF start_date > end_date THEN
407 return(FALSE);
408 ELSE
409 return(TRUE);
410 END IF;
411 END;
412 /*************************************************************************/
413 FUNCTION duplicate_excep(X_CALENDAR_ID in NUMBER,X_EXCEPTION_ID IN NUMBER, X_EXCEPTION_REASON IN CHAR, X_START_DATE_ACTIVE IN DATE, X_END_DATE_ACTIVE IN DATE) RETURN boolean IS
414
415 X_FOUND CHAR := 'N';
416 -- Just check the duplication by name.
417 cursor dup is
418 select 'x'
419 from jtf_cal_exception_assign
420 where calendar_id = X_CALENDAR_ID
424 -- OR ( X_START_DATE_ACTIVE BETWEEN start_date_active and end_date_active)
421 and exception_id = X_EXCEPTION_ID
422 -- and exception_reason= X_EXCEPTION_REASON
423 -- and (( X_START_DATE_ACTIVE <= start_date_active and nvl(X_END_DATE_ACTIVE,'31-DEC-4712') >= end_date_active )
425 -- OR ( nvl(X_END_DATE_ACTIVE,'31-DEC-4712') BETWEEN start_date_active and end_date_active)
426 -- OR ((X_START_DATE_ACTIVE < start_date_active) AND (nvl(X_END_DATE_ACTIVE,'31-DEC-4712') > end_date_active )))
427 AND rownum < 2;
428
429 BEGIN
430 -- Excep is unique
431 open dup;
432 fetch dup into X_FOUND;
433 if (dup%notfound) then
434 return(TRUE);
435 close dup;
436 else
437 return(FALSE);
438 end if;
439 close dup;
440
441 END;
442 end JTF_CAL_EXCEPTION_ASSIGN_PKG;
[Home] [Help]