[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_AUSE_ED_OT_SC_PKG
Source
1 package body IGS_AD_AUSE_ED_OT_SC_PKG as
2 /* $Header: IGSAI50B.pls 115.4 2002/11/28 22:07:50 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_AUSE_ED_OT_SC%RowType;
6 new_references IGS_AD_AUSE_ED_OT_SC%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_result_obtained_yr IN NUMBER DEFAULT NULL,
12 x_score_type IN VARCHAR2 DEFAULT NULL,
13 x_score IN VARCHAR2 DEFAULT NULL,
14 x_person_id IN NUMBER DEFAULT NULL,
15 x_ase_sequence_number 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 CURSOR cur_old_ref_values IS
24 SELECT *
25 FROM IGS_AD_AUSE_ED_OT_SC
26 WHERE rowid = x_rowid;
27
28 BEGIN
29
30 l_rowid := x_rowid;
31
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 App_Exception.Raise_Exception;
40 Close cur_old_ref_values;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.result_obtained_yr := x_result_obtained_yr;
47 new_references.score_type := x_score_type;
48 new_references.score := x_score;
49 new_references.person_id := x_person_id;
50 new_references.ase_sequence_number := x_ase_sequence_number;
51 IF (p_action = 'UPDATE') THEN
52 new_references.creation_date := old_references.creation_date;
53 new_references.created_by := old_references.created_by;
54 ELSE
55 new_references.creation_date := x_creation_date;
56 new_references.created_by := x_created_by;
57 END IF;
58 new_references.last_update_date := x_last_update_date;
59 new_references.last_updated_by := x_last_updated_by;
60 new_references.last_update_login := x_last_update_login;
61
62 END Set_Column_Values;
63
64 PROCEDURE Check_Constraints (
65
66
67
68
69
70
71 Column_Name IN VARCHAR2 DEFAULT NULL,
72
73
74
75
76
77
78 Column_Value IN VARCHAR2 DEFAULT NULL
79
80
81
82
83
84
85 )
86
87
88
89
90
91
92 AS
93
94
95
96
97
98
99 BEGIN
100
101
102
103
104
105
106 IF Column_Name is null then
107
108
109
110
111
112
113 NULL;
114
115
116
117
118
119
120 ELSIF upper(Column_Name) = 'SCORE_TYPE' then
121
122
123
124
125
126
127 new_references.score_type := column_value;
128
129
130
131
132
133
134 ELSIF upper(Column_Name) = 'ASE_SEQUENCE_NUMBER' then
135
136
137
138
139
140
141 new_references.ase_sequence_number := igs_ge_number.to_num(column_value);
142
143
144
145
146
147
148 ELSIF upper(Column_Name) = 'RESULT_OBTAINED_YR' then
149
150
151
152
153
154
155 new_references.result_obtained_yr := igs_ge_number.to_num(column_value);
156
157
158
159
160
161
162 END IF;
163
164
165
166
167
168
169
170
171
172
173
174
175
176 IF ((UPPER (column_name) = 'SCORE_TYPE') OR (column_name IS NULL)) THEN
177
178
179
180
181
182
183 IF (new_references.score_type <> UPPER (new_references.score_type)) THEN
184
185
186
187
188
189
190 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
191 IGS_GE_MSG_STACK.ADD;
192
193
194
195
196
197 App_Exception.Raise_Exception;
198
199
200
201
202
203
204 END IF;
205
206
207
208
209
210
211 END IF;
212
213
214
215
216
217
218 IF ((UPPER (column_name) = 'ASE_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
219
220
221
222
223
224
225 IF ((new_references.ase_sequence_number < 1) OR (new_references.ase_sequence_number > 9999999999)) THEN
226
227
228
229
230
231
232 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
233 IGS_GE_MSG_STACK.ADD;
234
235
236
237
238
239
240 App_Exception.Raise_Exception;
241
242
243
244
245
246
247 END IF;
248
249
250
251
252
253
254 END IF;
255
256
257
258
259
260
261 IF ((UPPER (column_name) = 'RESULT_OBTAINED_YR') OR (column_name IS NULL)) THEN
262
263
264
265
266
267
268 IF ((new_references.result_obtained_yr < 1900) OR (new_references.result_obtained_yr > 2050)) THEN
269
270
271
272
273
274
275 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
276 IGS_GE_MSG_STACK.ADD;
277
278
279
280
281
282
283 App_Exception.Raise_Exception;
284
285
286
287
288
289
290 END IF;
291
292
293
294
295
296
297 END IF;
298
299
300
301
302
303
304
305
306
307
308
309
310
311 END Check_Constraints;
312
313
314
315
316
317
318
319 PROCEDURE Check_Parent_Existance AS
320 BEGIN
321
322 IF (((old_references.person_id = new_references.person_id) AND
323 (old_references.ase_sequence_number = new_references.ase_sequence_number)) OR
324 ((new_references.person_id IS NULL) OR
325 (new_references.ase_sequence_number IS NULL))) THEN
326 NULL;
327 ELSE
328 IF NOT IGS_AD_AUS_SEC_EDU_PKG.Get_PK_For_Validation (
329 new_references.person_id,
330 new_references.ase_sequence_number
331 ) THEN
332 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
333 IGS_GE_MSG_STACK.ADD;
334 App_Exception.Raise_Exception;
335 END IF;
336 END IF;
337
338 END Check_Parent_Existance;
339
340 FUNCTION Get_PK_For_Validation (
341 x_person_id IN NUMBER,
342 x_ase_sequence_number IN NUMBER,
343 x_result_obtained_yr IN NUMBER,
344 x_score_type IN VARCHAR2
345 )return BOOLEAN AS
346 CURSOR cur_rowid IS
347 SELECT rowid
348 FROM IGS_AD_AUSE_ED_OT_SC
349 WHERE person_id = x_person_id
350 AND ase_sequence_number = x_ase_sequence_number
351 AND result_obtained_yr = x_result_obtained_yr
352 AND score_type = x_score_type
353 FOR UPDATE NOWAIT;
354
355 lv_rowid cur_rowid%RowType;
356
357 BEGIN
358
359 Open cur_rowid;
360 Fetch cur_rowid INTO lv_rowid;
361 IF (cur_rowid%FOUND) THEN
362
363
364
365
366
367
368 Close cur_rowid;
369
370
371
372
373
374
375 Return(TRUE);
376
377
378
379
380
381
382 ELSE
383
384
385
386
387
388
389 Close cur_rowid;
390
391
392
393
394
395
396 Return(FALSE);
397
398
399
400
401
402
403 END IF;
404
405
406
407
408
409
410
411 END Get_PK_For_Validation;
412
413 PROCEDURE GET_FK_IGS_AD_AUS_SEC_EDU (
414 x_person_id IN NUMBER,
415 x_sequence_number IN NUMBER
416 ) AS
417
418 CURSOR cur_rowid IS
419 SELECT rowid
420 FROM IGS_AD_AUSE_ED_OT_SC
421 WHERE person_id = x_person_id
422 AND ase_sequence_number = x_sequence_number ;
423
424 lv_rowid cur_rowid%RowType;
425
426 BEGIN
427
428 Open cur_rowid;
429 Fetch cur_rowid INTO lv_rowid;
430 IF (cur_rowid%FOUND) THEN
431 Close cur_rowid;
432 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ASEOS_ASE_FK');
433 IGS_GE_MSG_STACK.ADD;
434 App_Exception.Raise_Exception;
435 Return;
436 END IF;
437 Close cur_rowid;
438
439 END GET_FK_IGS_AD_AUS_SEC_EDU;
440
441 PROCEDURE Before_DML (
442 p_action IN VARCHAR2,
443 x_rowid IN VARCHAR2 DEFAULT NULL,
444 x_result_obtained_yr IN NUMBER DEFAULT NULL,
445 x_score_type IN VARCHAR2 DEFAULT NULL,
446 x_score IN VARCHAR2 DEFAULT NULL,
447 x_person_id IN NUMBER DEFAULT NULL,
448 x_ase_sequence_number IN NUMBER DEFAULT NULL,
449 x_creation_date IN DATE DEFAULT NULL,
450 x_created_by IN NUMBER DEFAULT NULL,
451 x_last_update_date IN DATE DEFAULT NULL,
452 x_last_updated_by IN NUMBER DEFAULT NULL,
453 x_last_update_login IN NUMBER DEFAULT NULL
454 ) AS
455 BEGIN
456 Set_Column_Values (
457 p_action,
458 x_rowid,
459 x_result_obtained_yr,
460 x_score_type,
461 x_score,
462 x_person_id,
463 x_ase_sequence_number,
464 x_creation_date,
465 x_created_by,
466 x_last_update_date,
467 x_last_updated_by,
468 x_last_update_login
469 );
470
471 IF (p_action = 'INSERT') THEN
472 IF Get_PK_For_Validation (
473 new_references.person_id,
474 new_references.ase_sequence_number,
475 new_references.result_obtained_yr,
476 new_references.score_type
477 ) THEN
478 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
479 IGS_GE_MSG_STACK.ADD;
480 App_Exception.Raise_Exception;
481 END IF;
482 Check_Constraints;
483 Check_Parent_Existance;
484 ELSIF (p_action = 'UPDATE') THEN
485 Check_Constraints;
486 Check_Parent_Existance;
490 new_references.ase_sequence_number,
487 ELSIF (p_action = 'VALIDATE_INSERT') THEN
488 IF Get_PK_For_Validation (
489 new_references.person_id,
491 new_references.result_obtained_yr,
495 IGS_GE_MSG_STACK.ADD;
492 new_references.score_type
493 ) THEN
494 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
496 App_Exception.Raise_Exception;
497 END IF;
498 Check_Constraints;
499 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
500 Check_Constraints;
501 END IF;
502 END Before_DML;
503
504 PROCEDURE After_DML (
505 p_action IN VARCHAR2,
506 x_rowid IN VARCHAR2
507 ) AS
508 BEGIN
509
510 l_rowid := x_rowid;
511
512 END After_DML;
513
514 procedure INSERT_ROW (
515 X_ROWID in out NOCOPY VARCHAR2,
516 X_PERSON_ID in NUMBER,
517 X_ASE_SEQUENCE_NUMBER in NUMBER,
518 X_RESULT_OBTAINED_YR in NUMBER,
519 X_SCORE_TYPE in VARCHAR2,
520 X_SCORE in VARCHAR2,
521 X_MODE in VARCHAR2 default 'R'
522 ) AS
523 cursor C is select ROWID from IGS_AD_AUSE_ED_OT_SC
524 where PERSON_ID = X_PERSON_ID
525 and ASE_SEQUENCE_NUMBER = X_ASE_SEQUENCE_NUMBER
526 and RESULT_OBTAINED_YR = X_RESULT_OBTAINED_YR
527 and SCORE_TYPE = X_SCORE_TYPE;
528 X_LAST_UPDATE_DATE DATE;
529 X_LAST_UPDATED_BY NUMBER;
530 X_LAST_UPDATE_LOGIN NUMBER;
531 begin
532 X_LAST_UPDATE_DATE := SYSDATE;
533 if(X_MODE = 'I') then
534 X_LAST_UPDATED_BY := 1;
535 X_LAST_UPDATE_LOGIN := 0;
536 elsif (X_MODE = 'R') then
537 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
538 if X_LAST_UPDATED_BY is NULL then
539 X_LAST_UPDATED_BY := -1;
540 end if;
541 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
542 if X_LAST_UPDATE_LOGIN is NULL then
543 X_LAST_UPDATE_LOGIN := -1;
544 end if;
545 else
546 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
547 IGS_GE_MSG_STACK.ADD;
548 app_exception.raise_exception;
549 end if;
550 Before_DML(
551 p_action=>'INSERT',
552 x_rowid=>X_ROWID,
553 x_ase_sequence_number=>X_ASE_SEQUENCE_NUMBER,
554 x_person_id=>X_PERSON_ID,
555 x_result_obtained_yr=>X_RESULT_OBTAINED_YR,
556 x_score=>X_SCORE,
557 x_score_type=>X_SCORE_TYPE,
558 x_creation_date=>X_LAST_UPDATE_DATE,
559 x_created_by=>X_LAST_UPDATED_BY,
560 x_last_update_date=>X_LAST_UPDATE_DATE,
561 x_last_updated_by=>X_LAST_UPDATED_BY,
562 x_last_update_login=>X_LAST_UPDATE_LOGIN
563 );
564
565 insert into IGS_AD_AUSE_ED_OT_SC (
566 PERSON_ID,
567 ASE_SEQUENCE_NUMBER,
568 RESULT_OBTAINED_YR,
569 SCORE_TYPE,
570 SCORE,
571 CREATION_DATE,
572 CREATED_BY,
573 LAST_UPDATE_DATE,
574 LAST_UPDATED_BY,
575 LAST_UPDATE_LOGIN
576 ) values (
577 NEW_REFERENCES.PERSON_ID,
578 NEW_REFERENCES.ASE_SEQUENCE_NUMBER,
579 NEW_REFERENCES.RESULT_OBTAINED_YR,
580 NEW_REFERENCES.SCORE_TYPE,
581 NEW_REFERENCES.SCORE,
582 X_LAST_UPDATE_DATE,
583 X_LAST_UPDATED_BY,
584 X_LAST_UPDATE_DATE,
585 X_LAST_UPDATED_BY,
586 X_LAST_UPDATE_LOGIN
587 );
588
589 open c;
590 fetch c into X_ROWID;
591 if (c%notfound) then
592 close c;
593 raise no_data_found;
594 end if;
595 close c;
596
597 After_DML (
598 p_action => 'INSERT',
599 x_rowid => X_ROWID);
600
601
602 end INSERT_ROW;
603
604 procedure LOCK_ROW (
605 X_ROWID in VARCHAR2,
606 X_PERSON_ID in NUMBER,
607 X_ASE_SEQUENCE_NUMBER in NUMBER,
608 X_RESULT_OBTAINED_YR in NUMBER,
609 X_SCORE_TYPE in VARCHAR2,
610 X_SCORE in VARCHAR2
611 ) AS
612 cursor c1 is select
613 SCORE
614 from IGS_AD_AUSE_ED_OT_SC
615 where ROWID = X_ROWID for update nowait;
616 tlinfo c1%rowtype;
617
618 begin
619 open c1;
623 IGS_GE_MSG_STACK.ADD;
620 fetch c1 into tlinfo;
621 if (c1%notfound) then
622 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
624 app_exception.raise_exception;
625 close c1;
626 return;
627 end if;
628 close c1;
629
630 if ( (tlinfo.SCORE = X_SCORE)
631 ) then
632 null;
633 else
634 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
635 IGS_GE_MSG_STACK.ADD;
636 app_exception.raise_exception;
637 end if;
638 return;
639 end LOCK_ROW;
640
641 procedure UPDATE_ROW (
642 X_ROWID in VARCHAR2,
643 X_PERSON_ID in NUMBER,
644 X_ASE_SEQUENCE_NUMBER in NUMBER,
645 X_RESULT_OBTAINED_YR in NUMBER,
646 X_SCORE_TYPE in VARCHAR2,
647 X_SCORE in VARCHAR2,
648 X_MODE in VARCHAR2 default 'R'
649 ) AS
650 X_LAST_UPDATE_DATE DATE;
651 X_LAST_UPDATED_BY NUMBER;
652 X_LAST_UPDATE_LOGIN NUMBER;
653 begin
654 X_LAST_UPDATE_DATE := SYSDATE;
655 if(X_MODE = 'I') then
656 X_LAST_UPDATED_BY := 1;
657 X_LAST_UPDATE_LOGIN := 0;
658 elsif (X_MODE = 'R') then
659 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
660 if X_LAST_UPDATED_BY is NULL then
661 X_LAST_UPDATED_BY := -1;
662 end if;
663 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
664 if X_LAST_UPDATE_LOGIN is NULL then
665 X_LAST_UPDATE_LOGIN := -1;
666 end if;
667 else
668 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
669 IGS_GE_MSG_STACK.ADD;
670 app_exception.raise_exception;
671 end if;
672
673 Before_DML(
674 p_action=>'UPDATE',
675 x_rowid=>X_ROWID,
676 x_ase_sequence_number=>X_ASE_SEQUENCE_NUMBER,
677 x_person_id=>X_PERSON_ID,
678 x_result_obtained_yr=>X_RESULT_OBTAINED_YR,
679 x_score=>X_SCORE,
680 x_score_type=>X_SCORE_TYPE,
684 x_last_updated_by=>X_LAST_UPDATED_BY,
681 x_creation_date=>X_LAST_UPDATE_DATE,
682 x_created_by=>X_LAST_UPDATED_BY,
683 x_last_update_date=>X_LAST_UPDATE_DATE,
685 x_last_update_login=>X_LAST_UPDATE_LOGIN
686 );
687
688 update IGS_AD_AUSE_ED_OT_SC set
689 SCORE = NEW_REFERENCES.SCORE,
690 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
691 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
692 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
693 where ROWID = X_ROWID
694 ;
695 if (sql%notfound) then
696 raise no_data_found;
697 end if;
698
699 After_DML (
700 p_action => 'UPDATE',
701 x_rowid => X_ROWID);
702
703 end UPDATE_ROW;
704
705 procedure ADD_ROW (
706 X_ROWID in out NOCOPY VARCHAR2,
707 X_PERSON_ID in NUMBER,
708 X_ASE_SEQUENCE_NUMBER in NUMBER,
709 X_RESULT_OBTAINED_YR in NUMBER,
710 X_SCORE_TYPE in VARCHAR2,
711 X_SCORE in VARCHAR2,
712 X_MODE in VARCHAR2 default 'R'
713 ) AS
714 cursor c1 is select rowid from IGS_AD_AUSE_ED_OT_SC
715 where PERSON_ID = X_PERSON_ID
716 and ASE_SEQUENCE_NUMBER = X_ASE_SEQUENCE_NUMBER
717 and RESULT_OBTAINED_YR = X_RESULT_OBTAINED_YR
718 and SCORE_TYPE = X_SCORE_TYPE
719 ;
720 begin
721 open c1;
722 fetch c1 into X_ROWID;
723 if (c1%notfound) then
724 close c1;
725 INSERT_ROW (
726 X_ROWID,
727 X_PERSON_ID,
728 X_ASE_SEQUENCE_NUMBER,
729 X_RESULT_OBTAINED_YR,
730 X_SCORE_TYPE,
731 X_SCORE,
732 X_MODE);
733 return;
734 end if;
735 close c1;
736 UPDATE_ROW (
737 X_ROWID,
738 X_PERSON_ID,
739 X_ASE_SEQUENCE_NUMBER,
740 X_RESULT_OBTAINED_YR,
741 X_SCORE_TYPE,
742 X_SCORE,
743 X_MODE);
744 end ADD_ROW;
745
746 procedure DELETE_ROW (
747 X_ROWID in VARCHAR2
748 ) AS
749 begin
750
751 Before_DML (
752 p_action => 'DELETE',
753 x_rowid => X_ROWID);
754
755 delete from IGS_AD_AUSE_ED_OT_SC
756 where ROWID = X_ROWID;
757 if (sql%notfound) then
758 raise no_data_found;
759 end if;
763 x_rowid => X_ROWID);
760
761 After_DML (
762 p_action => 'DELETE',
764
765 end DELETE_ROW;
766
767 end IGS_AD_AUSE_ED_OT_SC_PKG;