1 PACKAGE BODY igs_ad_up_header_pkg AS
2 /* $Header: IGSAI92B.pls 115.12 2003/10/30 13:24:44 rghosh ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_up_header%RowType;
5 new_references igs_ad_up_header%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_max_score IN NUMBER DEFAULT NULL,
11 x_up_header_id IN NUMBER DEFAULT NULL,
12 x_admission_test_type IN VARCHAR2 DEFAULT NULL,
13 x_test_segment_id IN NUMBER DEFAULT NULL,
14 x_definition_level IN VARCHAR2 DEFAULT NULL,
15 x_min_score IN NUMBER DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22
23 /*************************************************************
24 Created By : Kamalakar N.
25 Date Created By : 15/May/2000
26 Purpose :
27 Know limitations, enhancements or remarks
28 Change History
29 Who When What
30
31 (reverse chronological order - newest change first)
32 ***************************************************************/
33
34 CURSOR cur_old_ref_values IS
35 SELECT *
36 FROM IGS_AD_UP_HEADER
37 WHERE rowid = x_rowid;
38
39 BEGIN
40
41 l_rowid := x_rowid;
42
43 -- Code for setting the Old and New Reference Values.
44 -- Populate Old Values.
45 Open cur_old_ref_values;
46 Fetch cur_old_ref_values INTO old_references;
47 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
48 Close cur_old_ref_values;
49 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
50 IGS_GE_MSG_STACK.ADD;
51 App_Exception.Raise_Exception;
52 Return;
53 END IF;
54 Close cur_old_ref_values;
55
56 -- Populate New Values.
57 new_references.max_score := x_max_score;
58 new_references.up_header_id := x_up_header_id;
59 new_references.admission_test_type := x_admission_test_type;
60 new_references.test_segment_id := x_test_segment_id;
61 new_references.definition_level := x_definition_level;
62 new_references.min_score := x_min_score;
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70 new_references.last_update_date := x_last_update_date;
71 new_references.last_updated_by := x_last_updated_by;
72 new_references.last_update_login := x_last_update_login;
73
74 END Set_Column_Values;
75
76 PROCEDURE Check_Constraints (
77 Column_Name IN VARCHAR2 DEFAULT NULL,
78 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
79 /*************************************************************
80 Created By : Kamalakar N.
81 Date Created By : 15/May/2000
82 Purpose :
83 Know limitations, enhancements or remarks
84 Change History
85 Who When What
86
87 (reverse chronological order - newest change first)
88 ***************************************************************/
89
90 BEGIN
91
92 IF column_name IS NULL THEN
93 NULL;
94 ELSIF UPPER(column_name) = 'MIN_SCORE' THEN
95 new_references.min_score := IGS_GE_NUMBER.TO_NUM(column_value);
96 ELSIF UPPER(column_name) = 'MAX_SCORE' THEN
97 new_references.max_score := IGS_GE_NUMBER.TO_NUM(column_value);
98 ELSIF UPPER(column_name) = 'DEFINITION_LEVEL' THEN
99 new_references.definition_level := column_value;
100 NULL;
101 END IF;
102
103 -- The following code checks for check constraints on the Columns.
104 IF Upper(Column_Name) = 'MIN_SCORE' OR
105 Column_Name IS NULL THEN
106 IF NOT (new_references.min_score >= 0) THEN
107 Fnd_Message.Set_Name('IGS','IGS_AD_MIN_NOT_LT_0');
108 IGS_GE_MSG_STACK.ADD;
109 App_Exception.Raise_Exception;
110 END IF;
111 END IF;
112
113
114 -- The following code checks for check constraints on the Columns.
115 IF Upper(Column_Name) = 'MIN_SCORE' OR
116 Column_Name IS NULL THEN
117 IF NOT (new_references.min_score <= new_references.max_score) THEN
118 Fnd_Message.Set_Name('IGS','IGS_AD_MIN_NOT_GT_MAX');
119 IGS_GE_MSG_STACK.ADD;
120 App_Exception.Raise_Exception;
121 END IF;
122 END IF;
123
124
125 -- The following code checks for check constraints on the Columns.
126 IF Upper(Column_Name) = 'MAX_SCORE' OR
127 Column_Name IS NULL THEN
128 IF NOT (new_references.max_score >= new_references.min_score) THEN
129 Fnd_Message.Set_Name('IGS','IGS_AD_MAX_NOT_LT_MIN');
130 IGS_GE_MSG_STACK.ADD;
131 App_Exception.Raise_Exception;
132 END IF;
133 END IF;
134
135 -- The following code checks for check constraints on the Columns.
136 IF Upper(Column_Name) = 'DEFINITION_LEVEL' OR
137 Column_Name IS NULL THEN
138 IF NOT (new_references.definition_level IN ('T','S')) THEN
139 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
140 IGS_GE_MSG_STACK.ADD;
141 App_Exception.Raise_Exception;
142 END IF;
143 END IF;
144
145
146 END Check_Constraints;
147
148
149 PROCEDURE Check_Uniqueness AS
150 /*************************************************************
151 Created By : vdixit
152 Date Created On : 11-Oct-2001
153 Purpose :
154 Know limitations, enhancements or remarks
155 Change History
156 Who When What
157
158 (reverse chronological order - newest change first)
159 ***************************************************************/
160 Begin
161 IF Get_Uk_For_Validation (
162 new_references.admission_test_type,
163 new_references.test_segment_id
164 ) THEN
165 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
166 IGS_GE_MSG_STACK.ADD;
167 app_exception.raise_exception;
168 END IF;
169 END Check_Uniqueness ;
170
171
172
173 PROCEDURE Check_Parent_Existance AS
174 /*************************************************************
175 Created By : Kamalakar N.
176 Date Created By : 15/May/2000
177 Purpose :
178 Know limitations, enhancements or remarks
179 Change History
180 Who When What
181
182 (reverse chronological order - newest change first)
183 ***************************************************************/
184
185 BEGIN
186
187 IF (((old_references.admission_test_type = new_references.admission_test_type)) OR
188 ((new_references.admission_test_type IS NULL))) THEN
189 NULL;
190 ELSIF NOT Igs_Ad_Test_Type_Pkg.Get_PK_For_Validation (
191 new_references.admission_test_type,
192 'N'
193 ) THEN
194 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
195 IGS_GE_MSG_STACK.ADD;
196 App_Exception.Raise_Exception;
197 END IF;
198
199 IF (((old_references.test_segment_id = new_references.test_segment_id)) OR
200 ((new_references.test_segment_id IS NULL))) THEN
201 NULL;
202 ELSIF NOT Igs_Ad_Test_Segments_Pkg.Get_PK_For_Validation (
203 new_references.test_segment_id ,
204 'N'
205 ) THEN
206 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 END IF;
210
211 END Check_Parent_Existance;
212
213 PROCEDURE Check_Child_Existance IS
214 /*************************************************************
215 Created By : Kamalakar N.
216 Date Created By : 15/May/2000
217 Purpose :
218 Know limitations, enhancements or remarks
219 Change History
220 Who When What
221
222 (reverse chronological order - newest change first)
223 ***************************************************************/
224
225 BEGIN
226
227 Igs_Ad_Up_Detail_Pkg.Get_FK_Igs_Ad_Up_Header (
228 old_references.up_header_id
229 );
230
231 END Check_Child_Existance;
232
233 FUNCTION Get_PK_For_Validation (
234 x_up_header_id IN NUMBER
235 ) RETURN BOOLEAN AS
236
237 /*************************************************************
238 Created By : Kamalakar N.
239 Date Created By : 15/May/2000
240 Purpose :
241 Know limitations, enhancements or remarks
242 Change History
243 Who When What
244
245 (reverse chronological order - newest change first)
246 ***************************************************************/
247
248 CURSOR cur_rowid IS
249 SELECT rowid
250 FROM igs_ad_up_header
251 WHERE up_header_id = x_up_header_id
252 FOR UPDATE NOWAIT;
253
254 lv_rowid cur_rowid%RowType;
255
256 BEGIN
257
258 Open cur_rowid;
259 Fetch cur_rowid INTO lv_rowid;
260 IF (cur_rowid%FOUND) THEN
261 Close cur_rowid;
262 Return(TRUE);
263 ELSE
264 Close cur_rowid;
265 Return(FALSE);
266 END IF;
267 END Get_PK_For_Validation;
268
269 FUNCTION Get_UK_For_Validation (
270 x_admission_test_type IN VARCHAR2,
271 x_test_segment_id IN NUMBER
272 ) RETURN BOOLEAN AS
273
274 /*************************************************************
275 Created By : vdixit
276 Date Created On : 11-Oct-2001
277 Purpose :
278 Know limitations, enhancements or remarks
279 Change History
280 Who When What
281 rrengara 22-mar-2002 1. Added parameter x_test_segment_id to the procedure Bug fix for 2269985
282 2. Added check for uniquness on Admission test type and segment if the test_segment_id is not null Bug fix for 2269985
283 (reverse chronological order - newest change first)
284 ***************************************************************/
285
286 CURSOR cur_rowid IS
287 SELECT rowid
288 FROM igs_ad_up_header
289 WHERE admission_test_type = x_admission_test_type
290 and ((l_rowid is null) or (rowid <> l_rowid));
291
292 CURSOR test_type_seg_cur IS
293 SELECT rowid
294 FROM igs_ad_up_header
295 WHERE admission_test_type = x_admission_test_type AND
296 test_segment_id = x_test_segment_id AND
297 ((l_rowid is null) or (rowid <> l_rowid));
298
299 lv_rowid cur_rowid%RowType;
300 test_type_seg_rec test_type_seg_cur%ROWTYPE;
301 BEGIN
302
303
304 IF x_test_segment_id IS NULL THEN
305 Open cur_rowid;
306 Fetch cur_rowid INTO lv_rowid;
307 IF (cur_rowid%FOUND) THEN
308 Close cur_rowid;
309 return (true);
310 ELSE
311 close cur_rowid;
312 return(false);
313 END IF;
314 ELSE
315 open test_type_seg_cur;
316 Fetch test_type_seg_cur INTO test_type_seg_rec;
317 IF (test_type_seg_cur%FOUND) THEN
318 Close test_type_seg_cur;
319 return (true);
320 ELSE
321 close test_type_seg_cur;
322 return(false);
323 END IF;
324 END IF;
325 END Get_UK_For_Validation ;
326
327
328 PROCEDURE Get_FK_Igs_Ad_Test_Type (
329 x_admission_test_type IN VARCHAR2
330 ) AS
331
332 /*************************************************************
333 Created By : Kamalakar N.
334 Date Created By : 15/May/2000
335 Purpose :
336 Know limitations, enhancements or remarks
337 Change History
338 Who When What
339
340 (reverse chronological order - newest change first)
341 ***************************************************************/
342
343 CURSOR cur_rowid IS
344 SELECT rowid
345 FROM igs_ad_up_header
346 WHERE admission_test_type = x_admission_test_type ;
347
348 lv_rowid cur_rowid%RowType;
349
350 BEGIN
351
352 Open cur_rowid;
353 Fetch cur_rowid INTO lv_rowid;
354 IF (cur_rowid%FOUND) THEN
355 Close cur_rowid;
356 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AUH_ADMTT_FK');
357 IGS_GE_MSG_STACK.ADD;
358 App_Exception.Raise_Exception;
359 Return;
360 END IF;
361 Close cur_rowid;
362
363 END Get_FK_Igs_Ad_Test_Type;
364
365 PROCEDURE Get_FK_Igs_Ad_Test_Segments (
366 x_test_segment_id IN NUMBER
367 ) AS
368
369 /*************************************************************
370 Created By : Kamalakar N.
371 Date Created By : 15/May/2000
372 Purpose :
373 Know limitations, enhancements or remarks
374 Change History
375 Who When What
376
377 (reverse chronological order - newest change first)
378 ***************************************************************/
379
380 CURSOR cur_rowid IS
381 SELECT rowid
382 FROM igs_ad_up_header
383 WHERE test_segment_id = x_test_segment_id ;
384
385 lv_rowid cur_rowid%RowType;
386
387 BEGIN
388
389 Open cur_rowid;
390 Fetch cur_rowid INTO lv_rowid;
391 IF (cur_rowid%FOUND) THEN
392 Close cur_rowid;
393 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AUH_ATS_FK');
394 IGS_GE_MSG_STACK.ADD;
395 App_Exception.Raise_Exception;
396 Return;
397 END IF;
398 Close cur_rowid;
399
400 END Get_FK_Igs_Ad_Test_Segments;
401
402 PROCEDURE Before_DML (
403 p_action IN VARCHAR2,
404 x_rowid IN VARCHAR2 DEFAULT NULL,
405 x_max_score IN NUMBER DEFAULT NULL,
406 x_up_header_id IN NUMBER DEFAULT NULL,
407 x_admission_test_type IN VARCHAR2 DEFAULT NULL,
408 x_test_segment_id IN NUMBER DEFAULT NULL,
409 x_definition_level IN VARCHAR2 DEFAULT NULL,
410 x_min_score IN NUMBER DEFAULT NULL,
411 x_creation_date IN DATE DEFAULT NULL,
412 x_created_by IN NUMBER DEFAULT NULL,
413 x_last_update_date IN DATE DEFAULT NULL,
414 x_last_updated_by IN NUMBER DEFAULT NULL,
415 x_last_update_login IN NUMBER DEFAULT NULL
416 ) AS
417 /*************************************************************
418 Created By : Kamalakar N.
419 Date Created By : 15/May/2000
420 Purpose :
421 Know limitations, enhancements or remarks
422 Change History
423 Who When What
424
425 (reverse chronological order - newest change first)
426 ***************************************************************/
427
428 BEGIN
429
430 Set_Column_Values (
431 p_action,
432 x_rowid,
433 x_max_score,
434 x_up_header_id,
435 x_admission_test_type,
436 x_test_segment_id,
437 x_definition_level,
438 x_min_score,
439 x_creation_date,
440 x_created_by,
441 x_last_update_date,
442 x_last_updated_by,
443 x_last_update_login
444 );
445
446 IF (p_action = 'INSERT') THEN
447 -- Call all the procedures related to Before Insert.
448 Null;
449 IF Get_Pk_For_Validation(
450 new_references.up_header_id) THEN
451 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
452 IGS_GE_MSG_STACK.ADD;
453 App_Exception.Raise_Exception;
454 END IF;
455 Check_Constraints;
456 Check_Uniqueness;
457 Check_Parent_Existance;
458 ELSIF (p_action = 'UPDATE') THEN
459 -- Call all the procedures related to Before Update.
460 Null;
461 Check_Constraints;
462 Check_Uniqueness;
463 Check_Parent_Existance;
464 ELSIF (p_action = 'DELETE') THEN
465 -- Call all the procedures related to Before Delete.
466
467 Check_Child_Existance;
468 ELSIF (p_action = 'VALIDATE_INSERT') THEN
469 -- Call all the procedures related to Before Insert.
470 IF Get_PK_For_Validation (
471 new_references.up_header_id) THEN
472 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
473 IGS_GE_MSG_STACK.ADD;
474 App_Exception.Raise_Exception;
475 END IF;
476 Check_Constraints;
477 Check_Uniqueness;
478 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
479 Check_Uniqueness;
480 Check_Constraints;
481 ELSIF (p_action = 'VALIDATE_DELETE') THEN
482 Check_Child_Existance;
483 END IF;
484 l_rowid := NULL;
485 END Before_DML;
486
487 PROCEDURE After_DML (
488 p_action IN VARCHAR2,
489 x_rowid IN VARCHAR2
490 ) IS
491 /*************************************************************
492 Created By : Kamalakar N.
493 Date Created By : 15/May/2000
494 Purpose :
495 Know limitations, enhancements or remarks
496 Change History
497 Who When What
498
499 (reverse chronological order - newest change first)
500 ***************************************************************/
501
502 BEGIN
503
504 l_rowid := x_rowid;
505
506 IF (p_action = 'INSERT') THEN
507 -- Call all the procedures related to After Insert.
508 Null;
509 ELSIF (p_action = 'UPDATE') THEN
510 -- Call all the procedures related to After Update.
511 Null;
512 ELSIF (p_action = 'DELETE') THEN
513 -- Call all the procedures related to After Delete.
514 Null;
515 END IF;
516 l_rowid := NULL;
517 END After_DML;
518
519 procedure INSERT_ROW (
520 X_ROWID in out NOCOPY VARCHAR2,
521 x_MAX_SCORE IN NUMBER,
522 x_UP_HEADER_ID IN OUT NOCOPY NUMBER,
523 x_ADMISSION_TEST_TYPE IN VARCHAR2,
524 x_TEST_SEGMENT_ID IN NUMBER,
525 x_DEFINITION_LEVEL IN VARCHAR2,
526 x_MIN_SCORE IN NUMBER,
527 X_MODE in VARCHAR2 default 'R'
528 ) AS
529 /*************************************************************
530 Created By : Kamalakar N.
531 Date Created By : 15/May/2000
532 Purpose :
533 Know limitations, enhancements or remarks
534 Change History
535 Who When What
536
537 (reverse chronological order - newest change first)
538 ***************************************************************/
539
540 cursor C is select ROWID from IGS_AD_UP_HEADER
541 where UP_HEADER_ID= X_UP_HEADER_ID
542 ;
543 X_LAST_UPDATE_DATE DATE ;
544 X_LAST_UPDATED_BY NUMBER ;
545 X_LAST_UPDATE_LOGIN NUMBER ;
546 begin
547 X_LAST_UPDATE_DATE := SYSDATE;
548 if(X_MODE = 'I') then
549 X_LAST_UPDATED_BY := 1;
550 X_LAST_UPDATE_LOGIN := 0;
551 elsif (X_MODE = 'R') then
552 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
553 if X_LAST_UPDATED_BY is NULL then
554 X_LAST_UPDATED_BY := -1;
555 end if;
556 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
557 if X_LAST_UPDATE_LOGIN is NULL then
558 X_LAST_UPDATE_LOGIN := -1;
559 end if;
560 else
561 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
562 IGS_GE_MSG_STACK.ADD;
563 app_exception.raise_exception;
564 end if;
565
566 X_UP_HEADER_ID := -1;
567 Before_DML(
568 p_action=>'INSERT',
569 x_rowid=>X_ROWID,
570 x_max_score=>X_MAX_SCORE,
571 x_up_header_id=>X_UP_HEADER_ID,
572 x_admission_test_type=>X_ADMISSION_TEST_TYPE,
573 x_test_segment_id=>X_TEST_SEGMENT_ID,
574 x_definition_level=>X_DEFINITION_LEVEL,
575 x_min_score=>X_MIN_SCORE,
576 x_creation_date=>X_LAST_UPDATE_DATE,
577 x_created_by=>X_LAST_UPDATED_BY,
578 x_last_update_date=>X_LAST_UPDATE_DATE,
579 x_last_updated_by=>X_LAST_UPDATED_BY,
580 x_last_update_login=>X_LAST_UPDATE_LOGIN);
581 insert into IGS_AD_UP_HEADER (
582 MAX_SCORE
583 ,UP_HEADER_ID
584 ,ADMISSION_TEST_TYPE
585 ,TEST_SEGMENT_ID
586 ,DEFINITION_LEVEL
587 ,MIN_SCORE
588 ,CREATION_DATE
589 ,CREATED_BY
590 ,LAST_UPDATE_DATE
591 ,LAST_UPDATED_BY
592 ,LAST_UPDATE_LOGIN
593 ) values (
594 NEW_REFERENCES.MAX_SCORE
595 ,IGS_AD_UP_HEADER_S.NEXTVAL
596 ,NEW_REFERENCES.ADMISSION_TEST_TYPE
597 ,NEW_REFERENCES.TEST_SEGMENT_ID
598 ,NEW_REFERENCES.DEFINITION_LEVEL
599 ,NEW_REFERENCES.MIN_SCORE
600 ,X_LAST_UPDATE_DATE
601 ,X_LAST_UPDATED_BY
602 ,X_LAST_UPDATE_DATE
603 ,X_LAST_UPDATED_BY
604 ,X_LAST_UPDATE_LOGIN
605 )RETURNING UP_HEADER_ID INTO X_UP_HEADER_ID ;
606 open c;
607 fetch c into X_ROWID;
608 if (c%notfound) then
609 close c;
610 raise no_data_found;
611 end if;
612 close c;
613 After_DML (
614 p_action => 'INSERT' ,
615 x_rowid => X_ROWID );
616 end INSERT_ROW;
617 procedure LOCK_ROW (
618 X_ROWID in VARCHAR2,
619 x_MAX_SCORE IN NUMBER,
620 x_UP_HEADER_ID IN NUMBER,
621 x_ADMISSION_TEST_TYPE IN VARCHAR2,
622 x_TEST_SEGMENT_ID IN NUMBER,
623 x_DEFINITION_LEVEL IN VARCHAR2,
624 x_MIN_SCORE IN NUMBER ) AS
625 /*************************************************************
626 Created By : Kamalakar N.
627 Date Created By : 15/May/2000
628 Purpose :
629 Know limitations, enhancements or remarks
630 Change History
631 Who When What
632
633 (reverse chronological order - newest change first)
634 ***************************************************************/
635
636 cursor c1 is select
637 MAX_SCORE
638 , ADMISSION_TEST_TYPE
639 , TEST_SEGMENT_ID
640 , DEFINITION_LEVEL
641 , MIN_SCORE
642 from IGS_AD_UP_HEADER
643 where ROWID = X_ROWID
644 for update nowait;
645 tlinfo c1%rowtype;
646 begin
647 open c1;
648 fetch c1 into tlinfo;
649 if (c1%notfound) then
650 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
651 IGS_GE_MSG_STACK.ADD;
652 close c1;
653 app_exception.raise_exception;
654 return;
655 end if;
656 close c1;
657 if ( ( tlinfo.MAX_SCORE = X_MAX_SCORE)
658 AND (tlinfo.ADMISSION_TEST_TYPE = X_ADMISSION_TEST_TYPE)
659 AND (tlinfo.TEST_SEGMENT_ID = X_TEST_SEGMENT_ID OR tlinfo.TEST_SEGMENT_ID IS NULL)
660 AND (tlinfo.DEFINITION_LEVEL = X_DEFINITION_LEVEL)
661 AND (tlinfo.MIN_SCORE = X_MIN_SCORE)
662 ) then
663 null;
664 else
665 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
666 IGS_GE_MSG_STACK.ADD;
667 app_exception.raise_exception;
668 end if;
669 return;
670 end LOCK_ROW;
671 Procedure UPDATE_ROW (
672 X_ROWID in VARCHAR2,
673 x_MAX_SCORE IN NUMBER,
674 x_UP_HEADER_ID IN NUMBER,
675 x_ADMISSION_TEST_TYPE IN VARCHAR2,
676 x_TEST_SEGMENT_ID IN NUMBER,
677 x_DEFINITION_LEVEL IN VARCHAR2,
678 x_MIN_SCORE IN NUMBER,
679 X_MODE in VARCHAR2 default 'R'
680 ) AS
681 /*************************************************************
682 Created By : Kamalakar N.
683 Date Created By : 15/May/2000
684 Purpose :
685 Know limitations, enhancements or remarks
686 Change History
687 Who When What
688
689 (reverse chronological order - newest change first)
690 ***************************************************************/
691
692 X_LAST_UPDATE_DATE DATE ;
693 X_LAST_UPDATED_BY NUMBER ;
694 X_LAST_UPDATE_LOGIN NUMBER ;
695 begin
696 X_LAST_UPDATE_DATE := SYSDATE;
697 if(X_MODE = 'I') then
698 X_LAST_UPDATED_BY := 1;
699 X_LAST_UPDATE_LOGIN := 0;
700 elsif (X_MODE = 'R') then
701 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
702 if X_LAST_UPDATED_BY is NULL then
703 X_LAST_UPDATED_BY := -1;
704 end if;
705 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
706 if X_LAST_UPDATE_LOGIN is NULL then
707 X_LAST_UPDATE_LOGIN := -1;
708 end if;
709 else
710 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
711 IGS_GE_MSG_STACK.ADD;
712 app_exception.raise_exception;
713 end if;
714 Before_DML(
715 p_action=>'UPDATE',
716 x_rowid=>X_ROWID,
717 x_max_score=>X_MAX_SCORE,
718 x_up_header_id=>X_UP_HEADER_ID,
719 x_admission_test_type=>X_ADMISSION_TEST_TYPE,
720 x_test_segment_id=>X_TEST_SEGMENT_ID,
721 x_definition_level=>X_DEFINITION_LEVEL,
722 x_min_score=>X_MIN_SCORE,
723 x_creation_date=>X_LAST_UPDATE_DATE,
724 x_created_by=>X_LAST_UPDATED_BY,
725 x_last_update_date=>X_LAST_UPDATE_DATE,
726 x_last_updated_by=>X_LAST_UPDATED_BY,
727 x_last_update_login=>X_LAST_UPDATE_LOGIN);
728 update IGS_AD_UP_HEADER set
729 MAX_SCORE = NEW_REFERENCES.MAX_SCORE,
730 ADMISSION_TEST_TYPE = NEW_REFERENCES.ADMISSION_TEST_TYPE,
731 TEST_SEGMENT_ID = NEW_REFERENCES.TEST_SEGMENT_ID,
732 DEFINITION_LEVEL = NEW_REFERENCES.DEFINITION_LEVEL,
733 MIN_SCORE = NEW_REFERENCES.MIN_SCORE,
734 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
735 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
736 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
737 where ROWID = X_ROWID;
738 if (sql%notfound) then
739 raise no_data_found;
740 end if;
741
742 After_DML (
743 p_action => 'UPDATE' ,
744 x_rowid => X_ROWID
745 );
746 end UPDATE_ROW;
747 procedure ADD_ROW (
748 X_ROWID in out NOCOPY VARCHAR2,
749 x_MAX_SCORE IN NUMBER,
750 x_UP_HEADER_ID IN OUT NOCOPY NUMBER,
751 x_ADMISSION_TEST_TYPE IN VARCHAR2,
752 x_TEST_SEGMENT_ID IN NUMBER,
753 x_DEFINITION_LEVEL IN VARCHAR2,
754 x_MIN_SCORE IN NUMBER,
755 X_MODE in VARCHAR2 default 'R'
756 ) AS
757 /*************************************************************
758 Created By : Kamalakar N.
759 Date Created By : 15/May/2000
760 Purpose :
761 Know limitations, enhancements or remarks
762 Change History
763 Who When What
764
765 (reverse chronological order - newest change first)
766 ***************************************************************/
767
768 cursor c1 is select ROWID from IGS_AD_UP_HEADER
769 where UP_HEADER_ID= X_UP_HEADER_ID
770 ;
771 begin
772 open c1;
773 fetch c1 into X_ROWID;
774 if (c1%notfound) then
775 close c1;
776 INSERT_ROW (
777 X_ROWID,
778 X_MAX_SCORE,
779 X_UP_HEADER_ID,
780 X_ADMISSION_TEST_TYPE,
781 X_TEST_SEGMENT_ID,
782 X_DEFINITION_LEVEL,
783 X_MIN_SCORE,
784 X_MODE );
785 return;
786 end if;
787 close c1;
788 UPDATE_ROW (
789 X_ROWID,
790 X_MAX_SCORE,
791 X_UP_HEADER_ID,
792 X_ADMISSION_TEST_TYPE,
793 X_TEST_SEGMENT_ID,
794 X_DEFINITION_LEVEL,
795 X_MIN_SCORE,
796 X_MODE );
797 end ADD_ROW;
798 procedure DELETE_ROW (
799 X_ROWID in VARCHAR2
800 ) AS
801 /*************************************************************
802 Created By : Kamalakar N.
803 Date Created By : 15/May/2000
804 Purpose :
805 Know limitations, enhancements or remarks
806 Change History
807 Who When What
808
809 (reverse chronological order - newest change first)
810 ***************************************************************/
811
812 begin
813 Before_DML (
814 p_action => 'DELETE',
815 x_rowid => X_ROWID
816 );
817 delete from IGS_AD_UP_HEADER
818 where ROWID = X_ROWID;
819 if (sql%notfound) then
820 raise no_data_found;
821 end if;
822 After_DML (
823 p_action => 'DELETE',
824 x_rowid => X_ROWID
825 );
826 end DELETE_ROW;
827 END igs_ad_up_header_pkg;
[Home] [Help]