1 PACKAGE BODY igr_i_e_testtyps_pkg AS
2 /* $Header: IGSRH11B.pls 120.0 2005/06/01 19:35:20 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igr_i_e_testtyps%ROWTYPE;
6 new_references igr_i_e_testtyps%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_ent_test_type_id IN NUMBER DEFAULT NULL,
12 x_admission_test_type IN VARCHAR2 DEFAULT NULL,
13 x_closed_ind IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL,
19 x_inquiry_type_id IN NUMBER DEFAULT NULL --Added for APC Inegration Apadegal
20 ) AS
21 /*
22 || Created By : jbegum
23 || Created On : 28-MAY-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 IGR_I_E_TESTTYPS
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.ent_test_type_id := x_ent_test_type_id;
55 new_references.admission_test_type := x_admission_test_type;
56 new_references.closed_ind := x_closed_ind;
57 new_references.inquiry_type_id := x_inquiry_type_id; --Added for APC Inegration Apadegal
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_uniqueness AS
75 /*
76 || Created By : jbegum
77 || Created On : 28-MAY-2001
78 || Purpose : Handles the Unique Constraint logic defined for the columns.
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 ( get_uk_for_validation (
87 new_references.admission_test_type,
88 new_references.inquiry_type_id --Added for APC Inegration Apadegal
89
90 )
91 ) THEN
92 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
93 igs_ge_msg_stack.add;
94 app_exception.raise_exception;
95 END IF;
96
97 END check_uniqueness;
98
99
100 PROCEDURE check_parent_existance AS
101 /*
102 || Created By : jbegum
103 || Created On : 28-MAY-2001
104 || Purpose : Checks for the existance of Parent records.
105 || Known limitations, enhancements or remarks :
106 || Change History :
107 || Who When What
108 ||
109 || (reverse chronological order - newest change first)
110 */
111 BEGIN
112
113
114 -- Added the code for inquiry_type_id (new coloumn) -- for APC Inegration Apadegal
115 IF (((old_references.inquiry_type_id = new_references.inquiry_type_id)) OR
116 ((new_references.inquiry_type_id IS NULL))) THEN
117 NULL;
118 ELSIF NOT igr_i_inquiry_types_pkg.get_pk_for_validation (
119 new_references.inquiry_type_id
120 ) THEN
121 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
122 igs_ge_msg_stack.add;
123 app_exception.raise_exception;
124 END IF;
125
126 IF (((old_references.admission_test_type = new_references.admission_test_type)) OR
127 ((new_references.admission_test_type IS NULL))) THEN
128 NULL;
129 ELSIF NOT igs_ad_test_type_pkg.get_pk_for_validation (
130 new_references.admission_test_type ,
131 'N' ) THEN
132 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
133 igs_ge_msg_stack.add;
134 app_exception.raise_exception;
135 END IF;
136
137 END check_parent_existance;
138
139
140 FUNCTION get_pk_for_validation (
141 x_ent_test_type_id IN NUMBER
142 ) RETURN BOOLEAN AS
143 /*
144 || Created By : jbegum
145 || Created On : 28-MAY-2001
146 || Purpose : Validates the Primary Key of the table.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 CURSOR cur_rowid IS
153 SELECT rowid
154 FROM IGR_I_E_TESTTYPS
155 WHERE ent_test_type_id = x_ent_test_type_id
156 FOR UPDATE NOWAIT;
157
158 lv_rowid cur_rowid%RowType;
159
160 BEGIN
161
162 OPEN cur_rowid;
163 FETCH cur_rowid INTO lv_rowid;
164 IF (cur_rowid%FOUND) THEN
165 CLOSE cur_rowid;
166 RETURN(TRUE);
167 ELSE
168 CLOSE cur_rowid;
169 RETURN(FALSE);
170 END IF;
171
172 END get_pk_for_validation;
173
174
175 FUNCTION get_uk_for_validation (
176
177 x_admission_test_type IN VARCHAR2,
178 x_inquiry_type_id IN NUMBER --Added for APC Inegration- Apadegal
179 ) RETURN BOOLEAN AS
180 /*
181 || Created By : jbegum
182 || Created On : 28-MAY-2001
183 || Purpose : Validates the Unique Keys of the table.
184 || Known limitations, enhancements or remarks :
185 || Change History :
186 || Who When What
187 || Apadegal 07-Mar-2005 changed the condition to check inquiry type id instead of entry stat id.
188 || (reverse chronological order - newest change first)
189 */
190 CURSOR cur_rowid IS
191 SELECT rowid
192 FROM IGR_I_E_TESTTYPS
193 WHERE inquiry_type_id = x_inquiry_type_id
194 AND admission_test_type = x_admission_test_type
195 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
196
197
198 lv_rowid cur_rowid%RowType;
199
200 BEGIN
201
202 OPEN cur_rowid;
203 FETCH cur_rowid INTO lv_rowid;
204 IF (cur_rowid%FOUND) THEN
205 CLOSE cur_rowid;
206 RETURN (true);
207 ELSE
208 CLOSE cur_rowid;
209 RETURN(FALSE);
210 END IF;
211
212 END get_uk_for_validation ;
213
214
215
216
217
218 PROCEDURE get_fk_igs_ad_test_type (
219 x_admission_test_type IN VARCHAR2
220 ) AS
221 /*
222 || Created By : jbegum
223 || Created On : 28-MAY-2001
224 || Purpose : Validates the Foreign Keys for the table.
225 || Known limitations, enhancements or remarks :
226 || Change History :
227 || Who When What
228 || (reverse chronological order - newest change first)
229 */
230 CURSOR cur_rowid IS
231 SELECT rowid
232 FROM IGR_I_E_TESTTYPS
233 WHERE ((admission_test_type = x_admission_test_type));
234
235 lv_rowid cur_rowid%RowType;
236
237 BEGIN
238
239 OPEN cur_rowid;
240 FETCH cur_rowid INTO lv_rowid;
241 IF (cur_rowid%FOUND) THEN
242 CLOSE cur_rowid;
243 fnd_message.set_name ('IGS', 'IGS_AD_AIETT_ADTT_FK');
244 igs_ge_msg_stack.add;
245 app_exception.raise_exception;
246 RETURN;
247 END IF;
248 CLOSE cur_rowid;
249
250 END get_fk_igs_ad_test_type;
251
252
253 PROCEDURE before_dml (
254 p_action IN VARCHAR2,
255 x_rowid IN VARCHAR2 DEFAULT NULL,
256 x_ent_test_type_id IN NUMBER DEFAULT NULL,
257 x_admission_test_type IN VARCHAR2 DEFAULT NULL,
258 x_closed_ind IN VARCHAR2 DEFAULT NULL,
259 x_creation_date IN DATE DEFAULT NULL,
260 x_created_by IN NUMBER DEFAULT NULL,
261 x_last_update_date IN DATE DEFAULT NULL,
262 x_last_updated_by IN NUMBER DEFAULT NULL,
263 x_last_update_login IN NUMBER DEFAULT NULL,
264 x_inquiry_type_id IN NUMBER DEFAULT NULL --Added for APC Inegration Apadegal
265 ) AS
266 /*
267 || Created By : jbegum
268 || Created On : 28-MAY-2001
269 || Purpose : Initialises the columns, Checks Constraints, Calls the
270 || Trigger Handlers for the table, before any DML operation.
271 || Known limitations, enhancements or remarks :
272 || Change History :
273 || Who When What
274 || (reverse chronological order - newest change first)
275 */
276 BEGIN
277
278 set_column_values (
279 p_action,
280 x_rowid,
281 x_ent_test_type_id,
282 x_admission_test_type,
283 x_closed_ind,
284 x_creation_date,
285 x_created_by,
286 x_last_update_date,
287 x_last_updated_by,
288 x_last_update_login,
289 x_inquiry_type_id --Added for APC Inegration Apadegal
290 );
291
292 IF (p_action = 'INSERT') THEN
293 -- Call all the procedures related to Before Insert.
294 IF ( get_pk_for_validation(
295 new_references.ent_test_type_id
296 )
297 ) THEN
298 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
299 igs_ge_msg_stack.add;
300 app_exception.raise_exception;
301 END IF;
302 check_uniqueness;
303 check_parent_existance;
304 ELSIF (p_action = 'UPDATE') THEN
305 -- Call all the procedures related to Before Update.
306 check_uniqueness;
307 check_parent_existance;
308 ELSIF (p_action = 'VALIDATE_INSERT') THEN
309 -- Call all the procedures related to Before Insert.
310 IF ( get_pk_for_validation (
311 new_references.ent_test_type_id
312 )
313 ) THEN
314 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
315 igs_ge_msg_stack.add;
316 app_exception.raise_exception;
317 END IF;
318 check_uniqueness;
319 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
320 check_uniqueness;
321 END IF;
322
323 END before_dml;
324
325
326 PROCEDURE insert_row (
327 x_rowid IN OUT NOCOPY VARCHAR2,
328 x_ent_test_type_id IN OUT NOCOPY NUMBER,
329 x_admission_test_type IN VARCHAR2,
330 x_closed_ind IN VARCHAR2,
331 x_mode IN VARCHAR2 DEFAULT 'R' ,
332 x_inquiry_type_id IN NUMBER --Added for APC Inegration Apadegal
333 ) AS
334 /*
335 || Created By : jbegum
336 || Created On : 28-MAY-2001
337 || Purpose : Handles the INSERT DML logic for the table.
338 || Known limitations, enhancements or remarks :
339 || Change History :
340 || Who When What
341 || (reverse chronological order - newest change first)
342 */
343 CURSOR c IS
344 SELECT rowid
345 FROM IGR_I_E_TESTTYPS
346 WHERE ent_test_type_id = x_ent_test_type_id;
347
348 x_last_update_date DATE;
349 x_last_updated_by NUMBER;
350 x_last_update_login NUMBER;
351
352 BEGIN
353
354 x_last_update_date := SYSDATE;
355 IF (x_mode = 'I') THEN
356 x_last_updated_by := 1;
357 x_last_update_login := 0;
358 ELSIF (x_mode = 'R') THEN
359 x_last_updated_by := fnd_global.user_id;
360 IF (x_last_updated_by IS NULL) THEN
361 x_last_updated_by := -1;
362 END IF;
363 x_last_update_login := fnd_global.login_id;
364 IF (x_last_update_login IS NULL) THEN
365 x_last_update_login := -1;
366 END IF;
367 ELSE
368 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
369 igs_ge_msg_stack.add;
370 app_exception.raise_exception;
371 END IF;
372
373 X_ENT_TEST_TYPE_ID := -1;
374 before_dml(
375 p_action => 'INSERT',
376 x_rowid => x_rowid,
377 x_ent_test_type_id => x_ent_test_type_id,
378 x_admission_test_type => x_admission_test_type,
379 x_closed_ind => x_closed_ind,
380 x_creation_date => x_last_update_date,
381 x_created_by => x_last_updated_by,
382 x_last_update_date => x_last_update_date,
383 x_last_updated_by => x_last_updated_by,
384 x_last_update_login => x_last_update_login,
385 x_inquiry_type_id => x_inquiry_type_id --Added for APC Inegration Apadegal
386
387 );
388
389 INSERT INTO IGR_I_E_TESTTYPS (
390 ent_test_type_id,
391 admission_test_type,
392 closed_ind,
393 creation_date,
394 created_by,
395 last_update_date,
396 last_updated_by,
397 last_update_login,
398 inquiry_type_id --Added for APC Inegration Apadegal
399 ) VALUES (
400 IGR_I_E_TESTTYPS_S.NEXTVAL,
401 new_references.admission_test_type,
402 new_references.closed_ind,
403 x_last_update_date,
404 x_last_updated_by,
405 x_last_update_date,
406 x_last_updated_by,
407 x_last_update_login ,
408 new_references.inquiry_type_id --Added for APC Inegration Apadegal
409 )RETURNING ENT_TEST_TYPE_ID INTO X_ENT_TEST_TYPE_ID;
410
411 OPEN c;
412 FETCH c INTO x_rowid;
413 IF (c%NOTFOUND) THEN
414 CLOSE c;
415 RAISE NO_DATA_FOUND;
416 END IF;
417 CLOSE c;
418
419 END insert_row;
420
421
422 PROCEDURE lock_row (
423 x_rowid IN VARCHAR2,
424 x_ent_test_type_id IN NUMBER,
425 x_admission_test_type IN VARCHAR2,
426 x_closed_ind IN VARCHAR2,
427 x_inquiry_type_id IN NUMBER --Added for APC Inegration Apadegal
428 ) AS
429 /*
430 || Created By : jbegum
431 || Created On : 28-MAY-2001
432 || Purpose : Handles the LOCK mechanism for the table.
433 || Known limitations, enhancements or remarks :
434 || Change History :
435 || Who When What
436 || (reverse chronological order - newest change first)
437 */
438 CURSOR c1 IS
439 SELECT
440 inquiry_type_id , --Added for APC Inegration Apadegal
441 admission_test_type,
442 closed_ind
443 FROM IGR_I_E_TESTTYPS
444 WHERE rowid = x_rowid
445 FOR UPDATE NOWAIT;
446
447 tlinfo c1%ROWTYPE;
448
449 BEGIN
450
451 OPEN c1;
452 FETCH c1 INTO tlinfo;
453 IF (c1%notfound) THEN
454 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
455 igs_ge_msg_stack.add;
456 CLOSE c1;
457 app_exception.raise_exception;
458 RETURN;
459 END IF;
460 CLOSE c1;
461
462 IF (
463 (tlinfo.inquiry_type_id = x_inquiry_type_id) -- added for APC Inegration Apadegal
464 AND (tlinfo.admission_test_type = x_admission_test_type)
465 AND (tlinfo.closed_ind = x_closed_ind)
466 ) THEN
467 NULL;
468 ELSE
469 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
470 igs_ge_msg_stack.add;
471 app_exception.raise_exception;
472 END IF;
473
474 RETURN;
475
476 END lock_row;
477
478
479 PROCEDURE update_row (
480 x_rowid IN VARCHAR2,
481 x_ent_test_type_id IN NUMBER,
482 x_admission_test_type IN VARCHAR2,
483 x_closed_ind IN VARCHAR2,
484 x_mode IN VARCHAR2 DEFAULT 'R' ,
485 x_inquiry_type_id IN NUMBER --Added for APC Inegration Apadegal
486 ) AS
487 /*
488 || Created By : jbegum
489 || Created On : 28-MAY-2001
490 || Purpose : Handles the UPDATE DML logic for the table.
491 || Known limitations, enhancements or remarks :
492 || Change History :
493 || Who When What
494 || (reverse chronological order - newest change first)
495 */
496 x_last_update_date DATE ;
497 x_last_updated_by NUMBER;
498 x_last_update_login NUMBER;
499
500 BEGIN
501
502 x_last_update_date := SYSDATE;
503 IF (X_MODE = 'I') THEN
504 x_last_updated_by := 1;
505 x_last_update_login := 0;
506 ELSIF (x_mode = 'R') THEN
507 x_last_updated_by := fnd_global.user_id;
508 IF x_last_updated_by IS NULL THEN
509 x_last_updated_by := -1;
510 END IF;
511 x_last_update_login := fnd_global.login_id;
512 IF (x_last_update_login IS NULL) THEN
513 x_last_update_login := -1;
514 END IF;
515 ELSE
516 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
517 igs_ge_msg_stack.add;
518 app_exception.raise_exception;
519 END IF;
520
521 before_dml(
522 p_action => 'UPDATE',
523 x_rowid => x_rowid,
524 x_ent_test_type_id => x_ent_test_type_id,
525 x_admission_test_type => x_admission_test_type,
526 x_closed_ind => x_closed_ind,
527 x_creation_date => x_last_update_date,
528 x_created_by => x_last_updated_by,
529 x_last_update_date => x_last_update_date,
530 x_last_updated_by => x_last_updated_by,
531 x_last_update_login => x_last_update_login,
532 x_inquiry_type_id => x_inquiry_type_id --Added for APC Inegration Apadegal
533 );
534
535 UPDATE IGR_I_E_TESTTYPS
536 SET
537 admission_test_type = new_references.admission_test_type,
538 closed_ind = new_references.closed_ind,
539 last_update_date = x_last_update_date,
540 last_updated_by = x_last_updated_by,
541 last_update_login = x_last_update_login,
542 inquiry_type_id = new_references.inquiry_type_id --Added for APC Inegration Apadegal
543 WHERE rowid = x_rowid;
544
545 IF (SQL%NOTFOUND) THEN
546 RAISE NO_DATA_FOUND;
547 END IF;
548
549 END update_row;
550
551
552 PROCEDURE add_row (
553 x_rowid IN OUT NOCOPY VARCHAR2,
554 x_ent_test_type_id IN OUT NOCOPY NUMBER,
555 x_admission_test_type IN VARCHAR2,
556 x_closed_ind IN VARCHAR2,
557 x_mode IN VARCHAR2 DEFAULT 'R',
558 x_inquiry_type_id IN NUMBER --Added for APC Inegration Apadegal
559 ) AS
560 /*
561 || Created By : jbegum
562 || Created On : 28-MAY-2001
563 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
564 || Known limitations, enhancements or remarks :
565 || Change History :
566 || Who When What
567 || (reverse chronological order - newest change first)
568 */
569 CURSOR c1 IS
570 SELECT rowid
571 FROM IGR_I_E_TESTTYPS
572 WHERE ent_test_type_id = x_ent_test_type_id;
573
574 BEGIN
575
576 OPEN c1;
577 FETCH c1 INTO x_rowid;
578 IF (c1%NOTFOUND) THEN
579 CLOSE c1;
580
581 insert_row (
582 x_rowid,
583 x_ent_test_type_id,
584 x_admission_test_type,
585 x_closed_ind,
586 x_mode ,
587 x_inquiry_type_id --Added for APC Inegration Apadegal
588 );
589 RETURN;
590 END IF;
591 CLOSE c1;
592
593 update_row (
594 x_rowid,
595 x_ent_test_type_id,
596 x_admission_test_type,
597 x_closed_ind,
598 x_mode ,
599 x_inquiry_type_id --Added for APC Inegration Apadegal
600 );
601
602 END add_row;
603
604
605 PROCEDURE delete_row (
606 x_rowid IN VARCHAR2
607 ) AS
608 /*
609 || Created By : jbegum
610 || Created On : 28-MAY-2001
611 || Purpose : Handles the DELETE DML logic for the table.
612 || Known limitations, enhancements or remarks :
613 || Change History :
614 || Who When What
615 || (reverse chronological order - newest change first)
616 */
617 BEGIN
618
619 before_dml (
620 p_action => 'DELETE',
621 x_rowid => x_rowid
622 );
623
624 DELETE FROM igr_i_e_testtyps
625 WHERE rowid = x_rowid;
626
627 IF (SQL%NOTFOUND) THEN
628 RAISE NO_DATA_FOUND;
629 END IF;
630
631 END delete_row;
632
633 PROCEDURE get_fk_igr_i_inquiry_types (
634 x_inquiry_type_id IN NUMBER
635 )
636 ------------------------------------------------------------------
637 --Change History:
638 --Who When What
639 --apadegal 07-Mar-2005 APC build, Added the function for checking
640 -- child existence
641 -------------------------------------------------------------------
642 AS
643 CURSOR cur_rowid IS
644 SELECT rowid
645 FROM igr_i_e_testtyps
646 WHERE inquiry_type_id= x_inquiry_type_id;
647
648 lv_rowid cur_rowid%RowType;
649 BEGIN
650
651 Open cur_rowid;
652 Fetch cur_rowid INTO lv_rowid;
653 IF (cur_rowid%FOUND) THEN
654 Close cur_rowid;
655 Fnd_Message.Set_Name ('IGS', 'IGR_INQTYP_TSTTYP_FK');
656 IGS_GE_MSG_STACK.ADD;
657 App_Exception.Raise_Exception;
658 Return;
659 END IF;
660 Close cur_rowid;
661 END get_fk_igr_i_inquiry_types;
662 END igr_i_e_testtyps_pkg;
[Home] [Help]