[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_SCH_OCR_CFIG_PKG
Source
1 PACKAGE BODY igs_ps_sch_ocr_cfig_pkg AS
2 /* $Header: IGSPI3QB.pls 120.1 2005/09/08 14:46:34 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_sch_ocr_cfig%ROWTYPE;
6 new_references igs_ps_sch_ocr_cfig%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_ocr_cfig_id IN NUMBER,
12 x_to_be_announced_roll_flag IN VARCHAR2,
13 x_day_roll_flag IN VARCHAR2,
14 x_time_roll_flag IN VARCHAR2,
15 x_instructor_roll_flag IN VARCHAR2,
16 x_facility_roll_flag IN VARCHAR2,
17 x_schd_not_rqd_roll_flag IN VARCHAR2,
18 x_ref_cd_roll_flag IN VARCHAR2,
19 x_preferred_bld_roll_flag IN VARCHAR2,
20 x_preferred_room_roll_flag IN VARCHAR2,
21 x_dedicated_bld_roll_flag IN VARCHAR2,
22 x_dedicated_room_roll_flag IN VARCHAR2,
23 x_scheduled_bld_roll_flag IN VARCHAR2,
24 x_scheduled_room_roll_flag IN VARCHAR2,
25 x_preferred_region_roll_flag IN VARCHAR2,
26 x_occur_flexfield_roll_flag IN VARCHAR2,
27 x_inc_ins_cng_notfy_roll_flag IN VARCHAR2,
28 x_date_ovrd_flag IN VARCHAR2,
29 x_day_ovrd_flag IN VARCHAR2,
30 x_time_ovrd_flag IN VARCHAR2,
31 x_instructor_ovrd_flag IN VARCHAR2,
32 x_scheduled_bld_ovrd_flag IN VARCHAR2,
33 x_scheduled_room_ovrd_flag IN VARCHAR2,
34 x_creation_date IN DATE,
35 x_created_by IN NUMBER,
36 x_last_update_date IN DATE,
37 x_last_updated_by IN NUMBER,
38 x_last_update_login IN NUMBER
39 ) AS
40 /*
41 || Created By : [email protected]
42 || Created On : 10-MAY-2005
43 || Purpose : Initialises the Old and New references for the columns of the table.
44 || Known limitations, enhancements or remarks :
45 || Change History :
46 || Who When What
47 || (reverse chronological order - newest change first)
48 */
49
50 CURSOR cur_old_ref_values IS
51 SELECT *
52 FROM igs_ps_sch_ocr_cfig
53 WHERE rowid = x_rowid;
54
55 BEGIN
56
57 l_rowid := x_rowid;
58
59 -- Code for setting the Old and New Reference Values.
60 -- Populate Old Values.
61 OPEN cur_old_ref_values;
62 FETCH cur_old_ref_values INTO old_references;
63 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
64 CLOSE cur_old_ref_values;
65 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
66 igs_ge_msg_stack.add;
67 app_exception.raise_exception;
68 RETURN;
69 END IF;
70 CLOSE cur_old_ref_values;
71
72 -- Populate New Values.
73 new_references.ocr_cfig_id := x_ocr_cfig_id;
74 new_references.to_be_announced_roll_flag := x_to_be_announced_roll_flag;
75 new_references.day_roll_flag := x_day_roll_flag;
76 new_references.time_roll_flag := x_time_roll_flag;
77 new_references.instructor_roll_flag := x_instructor_roll_flag;
78 new_references.facility_roll_flag := x_facility_roll_flag;
79 new_references.schd_not_rqd_roll_flag := x_schd_not_rqd_roll_flag;
80 new_references.ref_cd_roll_flag := x_ref_cd_roll_flag;
81 new_references.preferred_bld_roll_flag := x_preferred_bld_roll_flag;
82 new_references.preferred_room_roll_flag := x_preferred_room_roll_flag;
83 new_references.dedicated_bld_roll_flag := x_dedicated_bld_roll_flag;
84 new_references.dedicated_room_roll_flag := x_dedicated_room_roll_flag;
85 new_references.scheduled_bld_roll_flag := x_scheduled_bld_roll_flag;
86 new_references.scheduled_room_roll_flag := x_scheduled_room_roll_flag;
87 new_references.preferred_region_roll_flag := x_preferred_region_roll_flag;
88 new_references.occur_flexfield_roll_flag := x_occur_flexfield_roll_flag;
89 new_references.inc_ins_change_notfy_roll_flag := x_inc_ins_cng_notfy_roll_flag;
90 new_references.date_ovrd_flag := x_date_ovrd_flag;
91 new_references.day_ovrd_flag := x_day_ovrd_flag;
92 new_references.time_ovrd_flag := x_time_ovrd_flag;
93 new_references.instructor_ovrd_flag := x_instructor_ovrd_flag;
94 new_references.scheduled_bld_ovrd_flag := x_scheduled_bld_ovrd_flag;
95 new_references.scheduled_room_ovrd_flag := x_scheduled_room_ovrd_flag;
96
97 IF (p_action = 'UPDATE') THEN
98 new_references.creation_date := old_references.creation_date;
99 new_references.created_by := old_references.created_by;
100 ELSE
101 new_references.creation_date := x_creation_date;
102 new_references.created_by := x_created_by;
103 END IF;
104
105 new_references.last_update_date := x_last_update_date;
106 new_references.last_updated_by := x_last_updated_by;
107 new_references.last_update_login := x_last_update_login;
108
109 END set_column_values;
110
111
112 FUNCTION get_pk_for_validation (
113 x_ocr_cfig_id IN NUMBER
114 ) RETURN BOOLEAN AS
115 /*
116 || Created By : [email protected]
117 || Created On : 10-MAY-2005
118 || Purpose : Validates the Primary Key of the table.
119 || Known limitations, enhancements or remarks :
120 || Change History :
121 || Who When What
122 || (reverse chronological order - newest change first)
123 */
124 CURSOR cur_rowid IS
125 SELECT rowid
126 FROM igs_ps_sch_ocr_cfig
127 WHERE ocr_cfig_id = x_ocr_cfig_id
128 FOR UPDATE NOWAIT;
129
130 lv_rowid cur_rowid%RowType;
131
132 BEGIN
133
134 OPEN cur_rowid;
135 FETCH cur_rowid INTO lv_rowid;
136 IF (cur_rowid%FOUND) THEN
137 CLOSE cur_rowid;
138 RETURN(TRUE);
139 ELSE
140 CLOSE cur_rowid;
141 RETURN(FALSE);
142 END IF;
143
144 END get_pk_for_validation;
145
146
147 PROCEDURE before_dml (
148 p_action IN VARCHAR2,
149 x_rowid IN VARCHAR2,
150 x_ocr_cfig_id IN NUMBER,
151 x_to_be_announced_roll_flag IN VARCHAR2,
152 x_day_roll_flag IN VARCHAR2,
153 x_time_roll_flag IN VARCHAR2,
154 x_instructor_roll_flag IN VARCHAR2,
155 x_facility_roll_flag IN VARCHAR2,
156 x_schd_not_rqd_roll_flag IN VARCHAR2,
157 x_ref_cd_roll_flag IN VARCHAR2,
158 x_preferred_bld_roll_flag IN VARCHAR2,
159 x_preferred_room_roll_flag IN VARCHAR2,
160 x_dedicated_bld_roll_flag IN VARCHAR2,
161 x_dedicated_room_roll_flag IN VARCHAR2,
162 x_scheduled_bld_roll_flag IN VARCHAR2,
163 x_scheduled_room_roll_flag IN VARCHAR2,
164 x_preferred_region_roll_flag IN VARCHAR2,
165 x_occur_flexfield_roll_flag IN VARCHAR2,
166 x_inc_ins_cng_notfy_roll_flag IN VARCHAR2,
167 x_date_ovrd_flag IN VARCHAR2,
168 x_day_ovrd_flag IN VARCHAR2,
169 x_time_ovrd_flag IN VARCHAR2,
170 x_instructor_ovrd_flag IN VARCHAR2,
171 x_scheduled_bld_ovrd_flag IN VARCHAR2,
172 x_scheduled_room_ovrd_flag IN VARCHAR2,
173 x_creation_date IN DATE,
174 x_created_by IN NUMBER,
175 x_last_update_date IN DATE,
176 x_last_updated_by IN NUMBER,
177 x_last_update_login IN NUMBER
178 ) AS
179 /*
180 || Created By : [email protected]
181 || Created On : 10-MAY-2005
182 || Purpose : Initialises the columns, Checks Constraints, Calls the
183 || Trigger Handlers for the table, before any DML operation.
184 || Known limitations, enhancements or remarks :
185 || Change History :
186 || Who When What
187 || (reverse chronological order - newest change first)
188 */
189 BEGIN
190
191 set_column_values (
192 p_action,
193 x_rowid,
194 x_ocr_cfig_id,
195 x_to_be_announced_roll_flag,
196 x_day_roll_flag,
197 x_time_roll_flag,
198 x_instructor_roll_flag,
199 x_facility_roll_flag,
200 x_schd_not_rqd_roll_flag,
201 x_ref_cd_roll_flag,
202 x_preferred_bld_roll_flag,
203 x_preferred_room_roll_flag,
204 x_dedicated_bld_roll_flag,
205 x_dedicated_room_roll_flag,
206 x_scheduled_bld_roll_flag,
207 x_scheduled_room_roll_flag,
208 x_preferred_region_roll_flag,
209 x_occur_flexfield_roll_flag,
210 x_inc_ins_cng_notfy_roll_flag,
211 x_date_ovrd_flag,
212 x_day_ovrd_flag,
213 x_time_ovrd_flag,
214 x_instructor_ovrd_flag,
215 x_scheduled_bld_ovrd_flag,
216 x_scheduled_room_ovrd_flag,
217 x_creation_date,
218 x_created_by,
219 x_last_update_date,
220 x_last_updated_by,
221 x_last_update_login
222 );
223
224 IF (p_action = 'INSERT') THEN
225 -- Call all the procedures related to Before Insert.
226 IF ( get_pk_for_validation(
227 new_references.ocr_cfig_id
228 )
229 ) THEN
230 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
231 igs_ge_msg_stack.add;
232 app_exception.raise_exception;
233 END IF;
234 ELSIF (p_action = 'VALIDATE_INSERT') THEN
235 -- Call all the procedures related to Before Insert.
236 IF ( get_pk_for_validation (
237 new_references.ocr_cfig_id
238 )
239 ) THEN
240 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
241 igs_ge_msg_stack.add;
242 app_exception.raise_exception;
243 END IF;
244 END IF;
245
246 END before_dml;
247
248
249 PROCEDURE insert_row (
250 x_rowid IN OUT NOCOPY VARCHAR2,
251 x_ocr_cfig_id IN OUT NOCOPY NUMBER,
252 x_to_be_announced_roll_flag IN VARCHAR2,
253 x_day_roll_flag IN VARCHAR2,
254 x_time_roll_flag IN VARCHAR2,
255 x_instructor_roll_flag IN VARCHAR2,
256 x_facility_roll_flag IN VARCHAR2,
257 x_schd_not_rqd_roll_flag IN VARCHAR2,
258 x_ref_cd_roll_flag IN VARCHAR2,
259 x_preferred_bld_roll_flag IN VARCHAR2,
260 x_preferred_room_roll_flag IN VARCHAR2,
261 x_dedicated_bld_roll_flag IN VARCHAR2,
262 x_dedicated_room_roll_flag IN VARCHAR2,
263 x_scheduled_bld_roll_flag IN VARCHAR2,
264 x_scheduled_room_roll_flag IN VARCHAR2,
265 x_preferred_region_roll_flag IN VARCHAR2,
266 x_occur_flexfield_roll_flag IN VARCHAR2,
267 x_inc_ins_cng_notfy_roll_flag IN VARCHAR2,
268 x_date_ovrd_flag IN VARCHAR2,
269 x_day_ovrd_flag IN VARCHAR2,
270 x_time_ovrd_flag IN VARCHAR2,
271 x_instructor_ovrd_flag IN VARCHAR2,
272 x_scheduled_bld_ovrd_flag IN VARCHAR2,
273 x_scheduled_room_ovrd_flag IN VARCHAR2,
274 x_mode IN VARCHAR2
275 ) AS
276 /*
277 || Created By : [email protected]
278 || Created On : 10-MAY-2005
279 || Purpose : Handles the INSERT DML logic for the table.
280 || Known limitations, enhancements or remarks :
281 || Change History :
282 || Who When What
283 || (reverse chronological order - newest change first)
284 */
285
286 x_last_update_date DATE;
287 x_last_updated_by NUMBER;
288 x_last_update_login NUMBER;
289
290 BEGIN
291
292 x_last_update_date := SYSDATE;
293 IF (x_mode = 'I') THEN
294 x_last_updated_by := 1;
295 x_last_update_login := 0;
296 ELSIF (x_mode = 'R') THEN
297 x_last_updated_by := fnd_global.user_id;
298 IF (x_last_updated_by IS NULL) THEN
299 x_last_updated_by := -1;
300 END IF;
301 x_last_update_login := fnd_global.login_id;
302 IF (x_last_update_login IS NULL) THEN
303 x_last_update_login := -1;
304 END IF;
305 ELSE
306 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
307 fnd_message.set_token ('ROUTINE', 'IGS_PS_SCH_OCR_CFIG_PKG.INSERT_ROW');
308 igs_ge_msg_stack.add;
309 app_exception.raise_exception;
310 END IF;
311
312 x_ocr_cfig_id := NULL;
316 x_rowid => x_rowid,
313
314 before_dml(
315 p_action => 'INSERT',
317 x_ocr_cfig_id => x_ocr_cfig_id,
318 x_to_be_announced_roll_flag => x_to_be_announced_roll_flag,
319 x_day_roll_flag => x_day_roll_flag,
320 x_time_roll_flag => x_time_roll_flag,
321 x_instructor_roll_flag => x_instructor_roll_flag,
322 x_facility_roll_flag => x_facility_roll_flag,
323 x_schd_not_rqd_roll_flag => x_schd_not_rqd_roll_flag,
324 x_ref_cd_roll_flag => x_ref_cd_roll_flag,
325 x_preferred_bld_roll_flag => x_preferred_bld_roll_flag,
326 x_preferred_room_roll_flag => x_preferred_room_roll_flag,
327 x_dedicated_bld_roll_flag => x_dedicated_bld_roll_flag,
328 x_dedicated_room_roll_flag => x_dedicated_room_roll_flag,
329 x_scheduled_bld_roll_flag => x_scheduled_bld_roll_flag,
330 x_scheduled_room_roll_flag => x_scheduled_room_roll_flag,
331 x_preferred_region_roll_flag => x_preferred_region_roll_flag,
332 x_occur_flexfield_roll_flag => x_occur_flexfield_roll_flag,
333 x_inc_ins_cng_notfy_roll_flag => x_inc_ins_cng_notfy_roll_flag,
334 x_date_ovrd_flag => x_date_ovrd_flag,
335 x_day_ovrd_flag => x_day_ovrd_flag,
336 x_time_ovrd_flag => x_time_ovrd_flag,
337 x_instructor_ovrd_flag => x_instructor_ovrd_flag,
338 x_scheduled_bld_ovrd_flag => x_scheduled_bld_ovrd_flag,
339 x_scheduled_room_ovrd_flag => x_scheduled_room_ovrd_flag,
340 x_creation_date => x_last_update_date,
341 x_created_by => x_last_updated_by,
342 x_last_update_date => x_last_update_date,
343 x_last_updated_by => x_last_updated_by,
344 x_last_update_login => x_last_update_login
345 );
346
347 INSERT INTO igs_ps_sch_ocr_cfig (
348 ocr_cfig_id,
349 to_be_announced_roll_flag,
350 day_roll_flag,
351 time_roll_flag,
352 instructor_roll_flag,
353 facility_roll_flag,
354 schd_not_rqd_roll_flag,
355 ref_cd_roll_flag,
356 preferred_bld_roll_flag,
357 preferred_room_roll_flag,
358 dedicated_bld_roll_flag,
359 dedicated_room_roll_flag,
360 scheduled_bld_roll_flag,
361 scheduled_room_roll_flag,
362 preferred_region_roll_flag,
363 occur_flexfield_roll_flag,
364 inc_ins_change_notfy_roll_flag,
365 date_ovrd_flag,
366 day_ovrd_flag,
367 time_ovrd_flag,
368 instructor_ovrd_flag,
369 scheduled_bld_ovrd_flag,
370 scheduled_room_ovrd_flag,
371 creation_date,
372 created_by,
373 last_update_date,
374 last_updated_by,
375 last_update_login
376 ) VALUES (
377 igs_ps_sch_ocr_cfig_s.NEXTVAL,
378 new_references.to_be_announced_roll_flag,
379 new_references.day_roll_flag,
380 new_references.time_roll_flag,
381 new_references.instructor_roll_flag,
382 new_references.facility_roll_flag,
383 new_references.schd_not_rqd_roll_flag,
384 new_references.ref_cd_roll_flag,
385 new_references.preferred_bld_roll_flag,
386 new_references.preferred_room_roll_flag,
387 new_references.dedicated_bld_roll_flag,
388 new_references.dedicated_room_roll_flag,
389 new_references.scheduled_bld_roll_flag,
390 new_references.scheduled_room_roll_flag,
391 new_references.preferred_region_roll_flag,
392 new_references.occur_flexfield_roll_flag,
393 new_references.inc_ins_change_notfy_roll_flag,
394 new_references.date_ovrd_flag,
395 new_references.day_ovrd_flag,
396 new_references.time_ovrd_flag,
397 new_references.instructor_ovrd_flag,
398 new_references.scheduled_bld_ovrd_flag,
399 new_references.scheduled_room_ovrd_flag,
400 x_last_update_date,
401 x_last_updated_by,
402 x_last_update_date,
403 x_last_updated_by,
404 x_last_update_login
405 ) RETURNING ROWID, ocr_cfig_id INTO x_rowid, x_ocr_cfig_id;
406
407 END insert_row;
408
409
410 PROCEDURE lock_row (
411 x_rowid IN VARCHAR2,
412 x_ocr_cfig_id IN NUMBER,
413 x_to_be_announced_roll_flag IN VARCHAR2,
414 x_day_roll_flag IN VARCHAR2,
415 x_time_roll_flag IN VARCHAR2,
416 x_instructor_roll_flag IN VARCHAR2,
417 x_facility_roll_flag IN VARCHAR2,
418 x_schd_not_rqd_roll_flag IN VARCHAR2,
419 x_ref_cd_roll_flag IN VARCHAR2,
420 x_preferred_bld_roll_flag IN VARCHAR2,
421 x_preferred_room_roll_flag IN VARCHAR2,
422 x_dedicated_bld_roll_flag IN VARCHAR2,
423 x_dedicated_room_roll_flag IN VARCHAR2,
424 x_scheduled_bld_roll_flag IN VARCHAR2,
425 x_scheduled_room_roll_flag IN VARCHAR2,
426 x_preferred_region_roll_flag IN VARCHAR2,
427 x_occur_flexfield_roll_flag IN VARCHAR2,
428 x_inc_ins_cng_notfy_roll_flag IN VARCHAR2,
429 x_date_ovrd_flag IN VARCHAR2,
430 x_day_ovrd_flag IN VARCHAR2,
431 x_time_ovrd_flag IN VARCHAR2,
435 ) AS
432 x_instructor_ovrd_flag IN VARCHAR2,
433 x_scheduled_bld_ovrd_flag IN VARCHAR2,
434 x_scheduled_room_ovrd_flag IN VARCHAR2
436 /*
437 || Created By : [email protected]
438 || Created On : 10-MAY-2005
439 || Purpose : Handles the LOCK mechanism for the table.
440 || Known limitations, enhancements or remarks :
441 || Change History :
442 || Who When What
443 || (reverse chronological order - newest change first)
444 */
445 CURSOR c1 IS
446 SELECT
447 to_be_announced_roll_flag,
448 day_roll_flag,
449 time_roll_flag,
450 instructor_roll_flag,
451 facility_roll_flag,
452 schd_not_rqd_roll_flag,
453 ref_cd_roll_flag,
454 preferred_bld_roll_flag,
455 preferred_room_roll_flag,
456 dedicated_bld_roll_flag,
457 dedicated_room_roll_flag,
458 scheduled_bld_roll_flag,
459 scheduled_room_roll_flag,
460 preferred_region_roll_flag,
461 occur_flexfield_roll_flag,
462 inc_ins_change_notfy_roll_flag,
463 date_ovrd_flag,
464 day_ovrd_flag,
465 time_ovrd_flag,
466 instructor_ovrd_flag,
467 scheduled_bld_ovrd_flag,
468 scheduled_room_ovrd_flag
469 FROM igs_ps_sch_ocr_cfig
470 WHERE rowid = x_rowid
471 FOR UPDATE NOWAIT;
472
473 tlinfo c1%ROWTYPE;
474
475 BEGIN
476
477 OPEN c1;
478 FETCH c1 INTO tlinfo;
479 IF (c1%notfound) THEN
480 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
481 igs_ge_msg_stack.add;
482 CLOSE c1;
483 app_exception.raise_exception;
484 RETURN;
485 END IF;
486 CLOSE c1;
487
488 IF (
489 (tlinfo.to_be_announced_roll_flag = x_to_be_announced_roll_flag)
490 AND (tlinfo.day_roll_flag = x_day_roll_flag)
491 AND (tlinfo.time_roll_flag = x_time_roll_flag)
492 AND (tlinfo.instructor_roll_flag = x_instructor_roll_flag)
493 AND (tlinfo.facility_roll_flag = x_facility_roll_flag)
494 AND (tlinfo.schd_not_rqd_roll_flag = x_schd_not_rqd_roll_flag)
495 AND (tlinfo.ref_cd_roll_flag = x_ref_cd_roll_flag)
496 AND (tlinfo.preferred_bld_roll_flag = x_preferred_bld_roll_flag)
497 AND (tlinfo.preferred_room_roll_flag = x_preferred_room_roll_flag)
498 AND (tlinfo.dedicated_bld_roll_flag = x_dedicated_bld_roll_flag)
499 AND (tlinfo.dedicated_room_roll_flag = x_dedicated_room_roll_flag)
500 AND (tlinfo.scheduled_bld_roll_flag = x_scheduled_bld_roll_flag)
501 AND (tlinfo.scheduled_room_roll_flag = x_scheduled_room_roll_flag)
502 AND (tlinfo.preferred_region_roll_flag = x_preferred_region_roll_flag)
503 AND (tlinfo.occur_flexfield_roll_flag = x_occur_flexfield_roll_flag)
504 AND (tlinfo.inc_ins_change_notfy_roll_flag = x_inc_ins_cng_notfy_roll_flag)
505 AND (tlinfo.date_ovrd_flag = x_date_ovrd_flag)
506 AND (tlinfo.day_ovrd_flag = x_day_ovrd_flag)
507 AND (tlinfo.time_ovrd_flag = x_time_ovrd_flag)
508 AND (tlinfo.instructor_ovrd_flag = x_instructor_ovrd_flag)
509 AND (tlinfo.scheduled_bld_ovrd_flag = x_scheduled_bld_ovrd_flag)
510 AND (tlinfo.scheduled_room_ovrd_flag = x_scheduled_room_ovrd_flag)
511 ) THEN
512 NULL;
513 ELSE
514 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
515 igs_ge_msg_stack.add;
516 app_exception.raise_exception;
517 END IF;
518
519 RETURN;
520
521 END lock_row;
522
523
524 PROCEDURE update_row (
525 x_rowid IN VARCHAR2,
526 x_ocr_cfig_id IN NUMBER,
527 x_to_be_announced_roll_flag IN VARCHAR2,
528 x_day_roll_flag IN VARCHAR2,
529 x_time_roll_flag IN VARCHAR2,
530 x_instructor_roll_flag IN VARCHAR2,
531 x_facility_roll_flag IN VARCHAR2,
532 x_schd_not_rqd_roll_flag IN VARCHAR2,
533 x_ref_cd_roll_flag IN VARCHAR2,
534 x_preferred_bld_roll_flag IN VARCHAR2,
535 x_preferred_room_roll_flag IN VARCHAR2,
536 x_dedicated_bld_roll_flag IN VARCHAR2,
537 x_dedicated_room_roll_flag IN VARCHAR2,
538 x_scheduled_bld_roll_flag IN VARCHAR2,
539 x_scheduled_room_roll_flag IN VARCHAR2,
540 x_preferred_region_roll_flag IN VARCHAR2,
541 x_occur_flexfield_roll_flag IN VARCHAR2,
542 x_inc_ins_cng_notfy_roll_flag IN VARCHAR2,
543 x_date_ovrd_flag IN VARCHAR2,
544 x_day_ovrd_flag IN VARCHAR2,
545 x_time_ovrd_flag IN VARCHAR2,
546 x_instructor_ovrd_flag IN VARCHAR2,
547 x_scheduled_bld_ovrd_flag IN VARCHAR2,
548 x_scheduled_room_ovrd_flag IN VARCHAR2,
549 x_mode IN VARCHAR2
550 ) AS
551 /*
552 || Created By : [email protected]
553 || Created On : 10-MAY-2005
554 || Purpose : Handles the UPDATE DML logic for the table.
555 || Known limitations, enhancements or remarks :
556 || Change History :
557 || Who When What
558 || (reverse chronological order - newest change first)
559 */
560 x_last_update_date DATE ;
561 x_last_updated_by NUMBER;
562 x_last_update_login NUMBER;
563
567 IF (X_MODE = 'I') THEN
564 BEGIN
565
566 x_last_update_date := SYSDATE;
568 x_last_updated_by := 1;
569 x_last_update_login := 0;
570 ELSIF (x_mode = 'R') THEN
571 x_last_updated_by := fnd_global.user_id;
572 IF x_last_updated_by IS NULL THEN
573 x_last_updated_by := -1;
574 END IF;
575 x_last_update_login := fnd_global.login_id;
576 IF (x_last_update_login IS NULL) THEN
577 x_last_update_login := -1;
578 END IF;
579 ELSE
580 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
581 fnd_message.set_token ('ROUTINE', 'IGS_PS_SCH_OCR_CFIG_PKG.UPDATE_ROW');
582 igs_ge_msg_stack.add;
583 app_exception.raise_exception;
584 END IF;
585
586 before_dml(
587 p_action => 'UPDATE',
588 x_rowid => x_rowid,
589 x_ocr_cfig_id => x_ocr_cfig_id,
590 x_to_be_announced_roll_flag => x_to_be_announced_roll_flag,
591 x_day_roll_flag => x_day_roll_flag,
592 x_time_roll_flag => x_time_roll_flag,
593 x_instructor_roll_flag => x_instructor_roll_flag,
594 x_facility_roll_flag => x_facility_roll_flag,
595 x_schd_not_rqd_roll_flag => x_schd_not_rqd_roll_flag,
596 x_ref_cd_roll_flag => x_ref_cd_roll_flag,
597 x_preferred_bld_roll_flag => x_preferred_bld_roll_flag,
598 x_preferred_room_roll_flag => x_preferred_room_roll_flag,
599 x_dedicated_bld_roll_flag => x_dedicated_bld_roll_flag,
600 x_dedicated_room_roll_flag => x_dedicated_room_roll_flag,
601 x_scheduled_bld_roll_flag => x_scheduled_bld_roll_flag,
602 x_scheduled_room_roll_flag => x_scheduled_room_roll_flag,
603 x_preferred_region_roll_flag => x_preferred_region_roll_flag,
604 x_occur_flexfield_roll_flag => x_occur_flexfield_roll_flag,
605 x_inc_ins_cng_notfy_roll_flag => x_inc_ins_cng_notfy_roll_flag,
606 x_date_ovrd_flag => x_date_ovrd_flag,
607 x_day_ovrd_flag => x_day_ovrd_flag,
608 x_time_ovrd_flag => x_time_ovrd_flag,
609 x_instructor_ovrd_flag => x_instructor_ovrd_flag,
610 x_scheduled_bld_ovrd_flag => x_scheduled_bld_ovrd_flag,
611 x_scheduled_room_ovrd_flag => x_scheduled_room_ovrd_flag,
612 x_creation_date => x_last_update_date,
613 x_created_by => x_last_updated_by,
614 x_last_update_date => x_last_update_date,
615 x_last_updated_by => x_last_updated_by,
616 x_last_update_login => x_last_update_login
617 );
618
619 UPDATE igs_ps_sch_ocr_cfig
620 SET
621 to_be_announced_roll_flag = new_references.to_be_announced_roll_flag,
622 day_roll_flag = new_references.day_roll_flag,
623 time_roll_flag = new_references.time_roll_flag,
624 instructor_roll_flag = new_references.instructor_roll_flag,
625 facility_roll_flag = new_references.facility_roll_flag,
626 schd_not_rqd_roll_flag = new_references.schd_not_rqd_roll_flag,
627 ref_cd_roll_flag = new_references.ref_cd_roll_flag,
628 preferred_bld_roll_flag = new_references.preferred_bld_roll_flag,
629 preferred_room_roll_flag = new_references.preferred_room_roll_flag,
630 dedicated_bld_roll_flag = new_references.dedicated_bld_roll_flag,
631 dedicated_room_roll_flag = new_references.dedicated_room_roll_flag,
632 scheduled_bld_roll_flag = new_references.scheduled_bld_roll_flag,
633 scheduled_room_roll_flag = new_references.scheduled_room_roll_flag,
634 preferred_region_roll_flag = new_references.preferred_region_roll_flag,
635 occur_flexfield_roll_flag = new_references.occur_flexfield_roll_flag,
636 inc_ins_change_notfy_roll_flag = new_references.inc_ins_change_notfy_roll_flag,
637 date_ovrd_flag = new_references.date_ovrd_flag,
638 day_ovrd_flag = new_references.day_ovrd_flag,
639 time_ovrd_flag = new_references.time_ovrd_flag,
640 instructor_ovrd_flag = new_references.instructor_ovrd_flag,
641 scheduled_bld_ovrd_flag = new_references.scheduled_bld_ovrd_flag,
642 scheduled_room_ovrd_flag = new_references.scheduled_room_ovrd_flag,
643 last_update_date = x_last_update_date,
644 last_updated_by = x_last_updated_by,
645 last_update_login = x_last_update_login
646 WHERE rowid = x_rowid;
647
648 IF (SQL%NOTFOUND) THEN
649 RAISE NO_DATA_FOUND;
650 END IF;
651
652 END update_row;
653
654
655 PROCEDURE add_row (
656 x_rowid IN OUT NOCOPY VARCHAR2,
657 x_ocr_cfig_id IN OUT NOCOPY NUMBER,
658 x_to_be_announced_roll_flag IN VARCHAR2,
659 x_day_roll_flag IN VARCHAR2,
660 x_time_roll_flag IN VARCHAR2,
661 x_instructor_roll_flag IN VARCHAR2,
662 x_facility_roll_flag IN VARCHAR2,
663 x_schd_not_rqd_roll_flag IN VARCHAR2,
664 x_ref_cd_roll_flag IN VARCHAR2,
665 x_preferred_bld_roll_flag IN VARCHAR2,
666 x_preferred_room_roll_flag IN VARCHAR2,
667 x_dedicated_bld_roll_flag IN VARCHAR2,
668 x_dedicated_room_roll_flag IN VARCHAR2,
669 x_scheduled_bld_roll_flag IN VARCHAR2,
670 x_scheduled_room_roll_flag IN VARCHAR2,
671 x_preferred_region_roll_flag IN VARCHAR2,
672 x_occur_flexfield_roll_flag IN VARCHAR2,
673 x_inc_ins_cng_notfy_roll_flag IN VARCHAR2,
674 x_date_ovrd_flag IN VARCHAR2,
675 x_day_ovrd_flag IN VARCHAR2,
676 x_time_ovrd_flag IN VARCHAR2,
677 x_instructor_ovrd_flag IN VARCHAR2,
678 x_scheduled_bld_ovrd_flag IN VARCHAR2,
679 x_scheduled_room_ovrd_flag IN VARCHAR2,
680 x_mode IN VARCHAR2
681 ) AS
682 /*
683 || Created By : [email protected]
684 || Created On : 10-MAY-2005
685 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
686 || Known limitations, enhancements or remarks :
687 || Change History :
688 || Who When What
689 || (reverse chronological order - newest change first)
690 */
691 CURSOR c1 IS
692 SELECT rowid
693 FROM igs_ps_sch_ocr_cfig
694 WHERE ocr_cfig_id = x_ocr_cfig_id;
695
696 BEGIN
697
698 OPEN c1;
699 FETCH c1 INTO x_rowid;
700 IF (c1%NOTFOUND) THEN
701 CLOSE c1;
702
703 insert_row (
704 x_rowid,
705 x_ocr_cfig_id,
706 x_to_be_announced_roll_flag,
707 x_day_roll_flag,
708 x_time_roll_flag,
709 x_instructor_roll_flag,
710 x_facility_roll_flag,
711 x_schd_not_rqd_roll_flag,
712 x_ref_cd_roll_flag,
713 x_preferred_bld_roll_flag,
714 x_preferred_room_roll_flag,
715 x_dedicated_bld_roll_flag,
716 x_dedicated_room_roll_flag,
717 x_scheduled_bld_roll_flag,
718 x_scheduled_room_roll_flag,
719 x_preferred_region_roll_flag,
720 x_occur_flexfield_roll_flag,
721 x_inc_ins_cng_notfy_roll_flag,
722 x_date_ovrd_flag,
723 x_day_ovrd_flag,
724 x_time_ovrd_flag,
725 x_instructor_ovrd_flag,
726 x_scheduled_bld_ovrd_flag,
727 x_scheduled_room_ovrd_flag,
728 x_mode
729 );
730 RETURN;
731 END IF;
732 CLOSE c1;
733
734 update_row (
735 x_rowid,
736 x_ocr_cfig_id,
737 x_to_be_announced_roll_flag,
738 x_day_roll_flag,
739 x_time_roll_flag,
740 x_instructor_roll_flag,
741 x_facility_roll_flag,
742 x_schd_not_rqd_roll_flag,
743 x_ref_cd_roll_flag,
744 x_preferred_bld_roll_flag,
745 x_preferred_room_roll_flag,
746 x_dedicated_bld_roll_flag,
747 x_dedicated_room_roll_flag,
748 x_scheduled_bld_roll_flag,
749 x_scheduled_room_roll_flag,
750 x_preferred_region_roll_flag,
751 x_occur_flexfield_roll_flag,
752 x_inc_ins_cng_notfy_roll_flag,
753 x_date_ovrd_flag,
754 x_day_ovrd_flag,
755 x_time_ovrd_flag,
756 x_instructor_ovrd_flag,
757 x_scheduled_bld_ovrd_flag,
758 x_scheduled_room_ovrd_flag,
759 x_mode
760 );
761
762 END add_row;
763
764
765 PROCEDURE delete_row (
766 x_rowid IN VARCHAR2
767 ) AS
768 /*
769 || Created By : [email protected]
770 || Created On : 10-MAY-2005
771 || Purpose : Handles the DELETE DML logic for the table.
772 || Known limitations, enhancements or remarks :
773 || Change History :
774 || Who When What
775 || (reverse chronological order - newest change first)
776 */
777 BEGIN
778
779 before_dml (
780 p_action => 'DELETE',
781 x_rowid => x_rowid
782 );
783
784 DELETE FROM igs_ps_sch_ocr_cfig
785 WHERE rowid = x_rowid;
786
787 IF (SQL%NOTFOUND) THEN
788 RAISE NO_DATA_FOUND;
789 END IF;
790
791 END delete_row;
792
793
794 END igs_ps_sch_ocr_cfig_pkg;