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