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