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