1 PACKAGE BODY igs_en_timeslot_rslt_pkg AS
2 /* $Header: IGSEI42B.pls 115.4 2002/11/28 23:42:34 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_en_timeslot_rslt%ROWTYPE;
5 new_references igs_en_timeslot_rslt%ROWTYPE;
6 PROCEDURE set_column_values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_igs_en_timeslot_rslt_id IN NUMBER DEFAULT NULL,
10 x_igs_en_timeslot_para_id IN NUMBER DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_start_dt_time IN DATE DEFAULT NULL,
13 x_end_dt_time IN DATE DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL
19 ) AS
20 /*
21 || Created By : hchauhan
22 || Created On : 08-DEC-2000
23 || Purpose : Initialises the Old and New references for the columns of the table.
24 || Known limitations, enhancements or remarks :
25 || Change History :
26 || Who When What
27 || (reverse chronological order - newest change first)
28 */
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM IGS_EN_TIMESLOT_RSLT
32 WHERE rowid = x_rowid;
33 BEGIN
34 l_rowid := x_rowid;
35 -- Code for setting the Old and New Reference Values.
36 -- Populate Old Values.
37 OPEN cur_old_ref_values;
38 FETCH cur_old_ref_values INTO old_references;
39 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
40 CLOSE cur_old_ref_values;
41 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
42 igs_ge_msg_stack.add;
43 app_exception.raise_exception;
44 RETURN;
45 END IF;
46 CLOSE cur_old_ref_values;
47 -- Populate New Values.
48 new_references.igs_en_timeslot_rslt_id := x_igs_en_timeslot_rslt_id;
49 new_references.igs_en_timeslot_para_id := x_igs_en_timeslot_para_id;
50 new_references.person_id := x_person_id;
51 new_references.start_dt_time := x_start_dt_time;
52 new_references.end_dt_time := x_end_dt_time;
53 IF (p_action = 'UPDATE') THEN
54 new_references.creation_date := old_references.creation_date;
55 new_references.created_by := old_references.created_by;
56 ELSE
57 new_references.creation_date := x_creation_date;
58 new_references.created_by := x_created_by;
59 END IF;
60 new_references.last_update_date := x_last_update_date;
61 new_references.last_updated_by := x_last_updated_by;
62 new_references.last_update_login := x_last_update_login;
63 END set_column_values;
64 PROCEDURE check_parent_existance AS
65 /*
66 || Created By : hchauhan
67 || Created On : 08-DEC-2000
68 || Purpose : Checks for the existance of Parent records.
69 || Known limitations, enhancements or remarks :
70 || Change History :
71 || Who When What
72 || (reverse chronological order - newest change first)
73 */
74 BEGIN
75 IF (((old_references.igs_en_timeslot_para_id = new_references.igs_en_timeslot_para_id)) OR
76 ((new_references.igs_en_timeslot_para_id IS NULL))) THEN
77 NULL;
78 ELSIF NOT igs_en_timeslot_para_pkg.get_pk_for_validation (
79 new_references.igs_en_timeslot_para_id
80 ) THEN
81 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
82 igs_ge_msg_stack.add;
83 app_exception.raise_exception;
84 END IF;
85 END check_parent_existance;
86 FUNCTION get_pk_for_validation (
87 x_igs_en_timeslot_rslt_id IN NUMBER
88 ) RETURN BOOLEAN AS
89 /*
90 || Created By : hchauhan
91 || Created On : 08-DEC-2000
92 || Purpose : Validates the Primary Key of the table.
93 || Known limitations, enhancements or remarks :
94 || Change History :
95 || Who When What
96 || (reverse chronological order - newest change first)
97 */
98 CURSOR cur_rowid IS
99 SELECT rowid
100 FROM igs_en_timeslot_rslt
101 WHERE igs_en_timeslot_rslt_id = x_igs_en_timeslot_rslt_id
102 FOR UPDATE NOWAIT;
103 lv_rowid cur_rowid%RowType;
104 BEGIN
105 OPEN cur_rowid;
106 FETCH cur_rowid INTO lv_rowid;
107 IF (cur_rowid%FOUND) THEN
108 CLOSE cur_rowid;
109 RETURN(TRUE);
110 ELSE
111 CLOSE cur_rowid;
112 RETURN(FALSE);
113 END IF;
114 END get_pk_for_validation;
115 PROCEDURE get_fk_igs_en_timeslot_para (
116 x_igs_en_timeslot_para_id IN NUMBER
117 ) AS
118 /*
119 || Created By : hchauhan
120 || Created On : 08-DEC-2000
121 || Purpose : Validates the Foreign Keys for the table.
122 || Known limitations, enhancements or remarks :
123 || Change History :
124 || Who When What
125 || (reverse chronological order - newest change first)
126 */
127 CURSOR cur_rowid IS
128 SELECT rowid
129 FROM igs_en_timeslot_rslt
130 WHERE ((igs_en_timeslot_para_id = x_igs_en_timeslot_para_id));
131 lv_rowid cur_rowid%RowType;
132 BEGIN
133 OPEN cur_rowid;
134 FETCH cur_rowid INTO lv_rowid;
135 IF (cur_rowid%FOUND) THEN
136 CLOSE cur_rowid;
137 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
138 igs_ge_msg_stack.add;
139 app_exception.raise_exception;
140 RETURN;
141 END IF;
142 CLOSE cur_rowid;
143 END get_fk_igs_en_timeslot_para;
144 PROCEDURE before_dml (
145 p_action IN VARCHAR2,
146 x_rowid IN VARCHAR2 DEFAULT NULL,
147 x_igs_en_timeslot_rslt_id IN NUMBER DEFAULT NULL,
148 x_igs_en_timeslot_para_id IN NUMBER DEFAULT NULL,
149 x_person_id IN NUMBER DEFAULT NULL,
150 x_start_dt_time IN DATE DEFAULT NULL,
151 x_end_dt_time IN DATE DEFAULT NULL,
152 x_creation_date IN DATE DEFAULT NULL,
153 x_created_by IN NUMBER DEFAULT NULL,
154 x_last_update_date IN DATE DEFAULT NULL,
155 x_last_updated_by IN NUMBER DEFAULT NULL,
156 x_last_update_login IN NUMBER DEFAULT NULL
157 ) AS
158 /*
159 || Created By : hchauhan
160 || Created On : 08-DEC-2000
161 || Purpose : Initialises the columns, Checks Constraints, Calls the
162 || Trigger Handlers for the table, before any DML operation.
163 || Known limitations, enhancements or remarks :
164 || Change History :
165 || Who When What
166 || (reverse chronological order - newest change first)
167 */
168 BEGIN
169 set_column_values (
170 p_action,
171 x_rowid,
172 x_igs_en_timeslot_rslt_id,
173 x_igs_en_timeslot_para_id,
174 x_person_id,
175 x_start_dt_time,
176 x_end_dt_time,
177 x_creation_date,
178 x_created_by,
179 x_last_update_date,
180 x_last_updated_by,
181 x_last_update_login
182 );
183 IF (p_action = 'INSERT') THEN
184 -- Call all the procedures related to Before Insert.
185 IF ( get_pk_for_validation(
186 new_references.igs_en_timeslot_rslt_id
187 )
188 ) THEN
189 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
190 igs_ge_msg_stack.add;
191 app_exception.raise_exception;
192 END IF;
193 check_parent_existance;
194 ELSIF (p_action = 'UPDATE') THEN
195 -- Call all the procedures related to Before Update.
196 check_parent_existance;
197 ELSIF (p_action = 'VALIDATE_INSERT') THEN
198 -- Call all the procedures related to Before Insert.
199 IF ( get_pk_for_validation (
200 new_references.igs_en_timeslot_rslt_id
201 )
202 ) THEN
203 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
204 igs_ge_msg_stack.add;
205 app_exception.raise_exception;
206 END IF;
207 END IF;
208 END before_dml;
209 PROCEDURE insert_row (
210 x_rowid IN OUT NOCOPY VARCHAR2,
211 x_igs_en_timeslot_rslt_id IN OUT NOCOPY NUMBER,
212 x_igs_en_timeslot_para_id IN NUMBER,
213 x_person_id IN NUMBER,
214 x_start_dt_time IN DATE,
215 x_end_dt_time IN DATE,
216 x_mode IN VARCHAR2 DEFAULT 'R'
217 ) AS
218 /*
219 || Created By : hchauhan
220 || Created On : 08-DEC-2000
221 || Purpose : Handles the INSERT DML logic for the table.
222 || Known limitations, enhancements or remarks :
223 || Change History :
224 || Who When What
225 || (reverse chronological order - newest change first)
226 */
227 CURSOR c IS
228 SELECT rowid
229 FROM igs_en_timeslot_rslt
230 WHERE igs_en_timeslot_rslt_id = x_igs_en_timeslot_rslt_id;
231 x_last_update_date DATE;
232 x_last_updated_by NUMBER;
233 x_last_update_login NUMBER;
234 x_request_id NUMBER;
235 x_program_id NUMBER;
236 x_program_application_id NUMBER;
237 x_program_update_date DATE;
238 BEGIN
239 x_last_update_date := SYSDATE;
240 IF (x_mode = 'I') THEN
241 x_last_updated_by := 1;
242 x_last_update_login := 0;
243 ELSIF (x_mode = 'R') THEN
244 x_last_updated_by := fnd_global.user_id;
245 IF (x_last_updated_by IS NULL) THEN
246 x_last_updated_by := -1;
247 END IF;
248 x_last_update_login := fnd_global.login_id;
249 IF (x_last_update_login IS NULL) THEN
250 x_last_update_login := -1;
251 END IF;
252 x_request_id := fnd_global.conc_request_id;
253 x_program_id := fnd_global.conc_program_id;
254 x_program_application_id := fnd_global.prog_appl_id;
255 IF (x_request_id = -1) THEN
256 x_request_id := NULL;
257 x_program_id := NULL;
258 x_program_application_id := NULL;
259 x_program_update_date := NULL;
260 ELSE
261 x_program_update_date := SYSDATE;
262 END IF;
263 ELSE
264 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
265 igs_ge_msg_stack.add;
266 app_exception.raise_exception;
267 END IF;
268 SELECT igs_en_timeslot_rslt_s.NEXTVAL INTO x_igs_en_timeslot_rslt_id FROM DUAL;
269 before_dml(
270 p_action => 'INSERT',
271 x_rowid => x_rowid,
272 x_igs_en_timeslot_rslt_id => x_igs_en_timeslot_rslt_id,
273 x_igs_en_timeslot_para_id => x_igs_en_timeslot_para_id,
274 x_person_id => x_person_id,
275 x_start_dt_time => x_start_dt_time,
276 x_end_dt_time => x_end_dt_time,
277 x_creation_date => x_last_update_date,
278 x_created_by => x_last_updated_by,
279 x_last_update_date => x_last_update_date,
280 x_last_updated_by => x_last_updated_by,
281 x_last_update_login => x_last_update_login
282 );
283 INSERT INTO igs_en_timeslot_rslt (
284 igs_en_timeslot_rslt_id,
285 igs_en_timeslot_para_id,
286 person_id,
287 start_dt_time,
288 end_dt_time,
289 creation_date,
290 created_by,
291 last_update_date,
292 last_updated_by,
293 last_update_login,
294 request_id,
295 program_id,
296 program_application_id,
297 program_update_date
298 ) VALUES (
299 new_references.igs_en_timeslot_rslt_id,
300 new_references.igs_en_timeslot_para_id,
301 new_references.person_id,
302 new_references.start_dt_time,
303 new_references.end_dt_time,
304 x_last_update_date,
305 x_last_updated_by,
306 x_last_update_date,
307 x_last_updated_by,
308 x_last_update_login ,
309 x_request_id,
310 x_program_id,
311 x_program_application_id,
312 x_program_update_date
313 );
314 OPEN c;
315 FETCH c INTO x_rowid;
316 IF (c%NOTFOUND) THEN
317 CLOSE c;
318 RAISE NO_DATA_FOUND;
319 END IF;
320 CLOSE c;
321 END insert_row;
322 PROCEDURE lock_row (
323 x_rowid IN VARCHAR2,
324 x_igs_en_timeslot_rslt_id IN NUMBER,
325 x_igs_en_timeslot_para_id IN NUMBER,
326 x_person_id IN NUMBER,
327 x_start_dt_time IN DATE,
328 x_end_dt_time IN DATE
329 ) AS
330 /*
331 || Created By : hchauhan
332 || Created On : 08-DEC-2000
333 || Purpose : Handles the LOCK mechanism for the table.
334 || Known limitations, enhancements or remarks :
335 || Change History :
336 || Who When What
337 || (reverse chronological order - newest change first)
338 */
339 CURSOR c1 IS
340 SELECT
341 igs_en_timeslot_para_id,
342 person_id,
343 start_dt_time,
344 end_dt_time
345 FROM igs_en_timeslot_rslt
346 WHERE rowid = x_rowid
347 FOR UPDATE NOWAIT;
348 tlinfo c1%ROWTYPE;
349 BEGIN
350 OPEN c1;
351 FETCH c1 INTO tlinfo;
352 IF (c1%notfound) THEN
353 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
354 igs_ge_msg_stack.add;
355 CLOSE c1;
356 app_exception.raise_exception;
357 RETURN;
358 END IF;
359 CLOSE c1;
360
361 IF (
362 (tlinfo.igs_en_timeslot_para_id = x_igs_en_timeslot_para_id)
363 AND (tlinfo.person_id = x_person_id)
364 AND ((tlinfo.start_dt_time = x_start_dt_time) OR ((tlinfo.start_dt_time IS NULL) AND (X_start_dt_time IS NULL)))
365 AND ((tlinfo.end_dt_time = x_end_dt_time) OR ((tlinfo.end_dt_time IS NULL) AND (X_end_dt_time IS NULL)))
366 ) THEN
367 NULL;
368 ELSE
369 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
370 igs_ge_msg_stack.add;
371 app_exception.raise_exception;
372 END IF;
373 RETURN;
374 END lock_row;
375 PROCEDURE update_row (
376 x_rowid IN VARCHAR2,
377 x_igs_en_timeslot_rslt_id IN NUMBER,
378 x_igs_en_timeslot_para_id IN NUMBER,
379 x_person_id IN NUMBER,
380 x_start_dt_time IN DATE,
381 x_end_dt_time IN DATE,
382 x_mode IN VARCHAR2 DEFAULT 'R'
383 ) AS
384 /*
385 || Created By : hchauhan
386 || Created On : 08-DEC-2000
390 || Who When What
387 || Purpose : Handles the UPDATE DML logic for the table.
388 || Known limitations, enhancements or remarks :
389 || Change History :
391 || (reverse chronological order - newest change first)
392 */
393 x_last_update_date DATE ;
394 x_last_updated_by NUMBER;
395 x_last_update_login NUMBER;
396 x_request_id NUMBER;
397 x_program_id NUMBER;
398 x_program_application_id NUMBER;
399 x_program_update_date DATE;
400 BEGIN
401 x_last_update_date := SYSDATE;
402 IF (X_MODE = 'I') THEN
403 x_last_updated_by := 1;
404 x_last_update_login := 0;
405 ELSIF (x_mode = 'R') THEN
406 x_last_updated_by := fnd_global.user_id;
407 IF x_last_updated_by IS NULL THEN
408 x_last_updated_by := -1;
409 END IF;
410 x_last_update_login := fnd_global.login_id;
411 IF (x_last_update_login IS NULL) THEN
412 x_last_update_login := -1;
413 END IF;
414 ELSE
415 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
416 igs_ge_msg_stack.add;
417 app_exception.raise_exception;
418 END IF;
419 before_dml(
420 p_action => 'UPDATE',
421 x_rowid => x_rowid,
422 x_igs_en_timeslot_rslt_id => x_igs_en_timeslot_rslt_id,
423 x_igs_en_timeslot_para_id => x_igs_en_timeslot_para_id,
424 x_person_id => x_person_id,
425 x_start_dt_time => x_start_dt_time,
426 x_end_dt_time => x_end_dt_time,
427 x_creation_date => x_last_update_date,
428 x_created_by => x_last_updated_by,
429 x_last_update_date => x_last_update_date,
430 x_last_updated_by => x_last_updated_by,
431 x_last_update_login => x_last_update_login
432 );
433 IF (x_mode = 'R') THEN
434 x_request_id := fnd_global.conc_request_id;
435 x_program_id := fnd_global.conc_program_id;
436 x_program_application_id := fnd_global.prog_appl_id;
437 IF (x_request_id = -1) THEN
438 x_request_id := old_references.request_id;
439 x_program_id := old_references.program_id;
440 x_program_application_id := old_references.program_application_id;
441 x_program_update_date := old_references.program_update_date;
442 ELSE
443 x_program_update_date := SYSDATE;
444 END IF;
445 END IF;
446 UPDATE igs_en_timeslot_rslt
447 SET
448 igs_en_timeslot_para_id = new_references.igs_en_timeslot_para_id,
449 person_id = new_references.person_id,
450 start_dt_time = new_references.start_dt_time,
451 end_dt_time = new_references.end_dt_time,
452 last_update_date = x_last_update_date,
453 last_updated_by = x_last_updated_by,
454 last_update_login = x_last_update_login ,
455 request_id = x_request_id,
456 program_id = x_program_id,
457 program_application_id = x_program_application_id,
458 program_update_date = x_program_update_date
459 WHERE rowid = x_rowid;
460 IF (SQL%NOTFOUND) THEN
461 RAISE NO_DATA_FOUND;
462 END IF;
463 END update_row;
464 PROCEDURE add_row (
465 x_rowid IN OUT NOCOPY VARCHAR2,
466 x_igs_en_timeslot_rslt_id IN OUT NOCOPY NUMBER,
467 x_igs_en_timeslot_para_id IN NUMBER,
468 x_person_id IN NUMBER,
469 x_start_dt_time IN DATE,
470 x_end_dt_time IN DATE,
471 x_mode IN VARCHAR2 DEFAULT 'R'
472 ) AS
473 /*
474 || Created By : hchauhan
475 || Created On : 08-DEC-2000
476 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
477 || Known limitations, enhancements or remarks :
478 || Change History :
479 || Who When What
480 || (reverse chronological order - newest change first)
481 */
482 CURSOR c1 IS
483 SELECT rowid
484 FROM igs_en_timeslot_rslt
485 WHERE igs_en_timeslot_rslt_id = x_igs_en_timeslot_rslt_id;
486 BEGIN
487 OPEN c1;
488 FETCH c1 INTO x_rowid;
489 IF (c1%NOTFOUND) THEN
490 CLOSE c1;
491 insert_row (
492 x_rowid,
493 x_igs_en_timeslot_rslt_id,
494 x_igs_en_timeslot_para_id,
495 x_person_id,
496 x_start_dt_time,
497 x_end_dt_time,
498 x_mode
499 );
500 RETURN;
501 END IF;
502 CLOSE c1;
503 update_row (
504 x_rowid,
505 x_igs_en_timeslot_rslt_id,
506 x_igs_en_timeslot_para_id,
507 x_person_id,
508 x_start_dt_time,
509 x_end_dt_time,
510 x_mode
511 );
512 END add_row;
513 PROCEDURE delete_row (
514 x_rowid IN VARCHAR2
515 ) AS
516 /*
517 || Created By : hchauhan
518 || Created On : 08-DEC-2000
519 || Purpose : Handles the DELETE DML logic for the table.
520 || Known limitations, enhancements or remarks :
521 || Change History :
522 || Who When What
523 || (reverse chronological order - newest change first)
524 */
525 BEGIN
526 before_dml (
527 p_action => 'DELETE',
528 x_rowid => x_rowid
529 );
530 DELETE FROM igs_en_timeslot_rslt
531 WHERE rowid = x_rowid;
532 IF (SQL%NOTFOUND) THEN
533 RAISE NO_DATA_FOUND;
534 END IF;
535 END delete_row;
536 END igs_en_timeslot_rslt_pkg;
[Home] [Help]