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