1 PACKAGE BODY igs_ad_past_history_pkg AS
2 /* $Header: IGSAI88B.pls 115.15 2003/12/09 11:07:14 akadam ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_past_history_all%RowType;
5 new_references igs_ad_past_history_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_id IN NUMBER DEFAULT NULL,
11 x_past_history_id IN NUMBER DEFAULT NULL,
12 x_years_of_study IN NUMBER DEFAULT NULL,
13 x_subject_id IN NUMBER DEFAULT NULL,
14 x_honors IN VARCHAR2 DEFAULT NULL,
15 x_person_id 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 : samaresh.in
25 Date Created By : 15-MAY-2000
26 Purpose : to set column values
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_past_history_all
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.org_id := x_org_id;
58 new_references.past_history_id := x_past_history_id;
59 new_references.years_of_study := x_years_of_study;
60 new_references.subject_id := x_subject_id;
61 new_references.honors := x_honors;
62 new_references.person_id := x_person_id;
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 : samaresh.in
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) = 'YEARS_OF_STUDY' THEN
95 new_references.years_of_study := IGS_GE_NUMBER.TO_NUM(column_value);
96 ELSIF UPPER(column_name) = 'HONORS' THEN
97 new_references.honors := column_value;
98 NULL;
99 END IF;
100
101
102
103 -- The following code checks for check constraints on the Columns.
104 IF Upper(Column_Name) = 'YEARS_OF_STUDY' OR
105 Column_Name IS NULL THEN
106 IF NOT (new_references.years_of_study <= 10
107 AND new_references.years_of_study > 0) THEN
108 Fnd_Message.Set_Name('IGS','IGS_AD_YOS');
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception;
111 END IF;
112 END IF;
113
114 -- The following code checks for check constraints on the Columns.
115 IF Upper(Column_Name) = 'HONORS' OR
116 Column_Name IS NULL THEN
117 IF NOT (new_references.honors IN ('Y','N')) THEN
118 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
119 IGS_GE_MSG_STACK.ADD;
120 App_Exception.Raise_Exception;
121 END IF;
122 END IF;
123
124
125 END Check_Constraints;
126
127 PROCEDURE Check_Uniqueness AS
128 /*************************************************************
129 Created By : samaresh.in
130 Date Created By : 15-MAY-2000
131 Purpose :
132 Know limitations, enhancements or remarks
133 Change History
134 Who When What
135
136 (reverse chronological order - newest change first)
137 ***************************************************************/
138
139 begin
140 IF Get_Uk_For_Validation (
141 new_references.person_id
142 ,new_references.subject_id
143 ) THEN
144 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
145 IGS_GE_MSG_STACK.ADD;
146 app_exception.raise_exception;
147 END IF;
148 END Check_Uniqueness ;
149 PROCEDURE Check_Parent_Existance AS
150 /*************************************************************
151 Created By : samaresh.in
152 Date Created By : 15-MAY-2000
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
161 BEGIN
162
163 IF (((old_references.subject_id = new_references.subject_id)) OR
164 ((new_references.subject_id IS NULL))) THEN
165 NULL;
166 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
167 new_references.subject_id ,
168 'SUBJECT_OF_STUDY',
169 'N'
170 ) THEN
171 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
172 IGS_GE_MSG_STACK.ADD;
173 App_Exception.Raise_Exception;
174 END IF;
175
176 IF (((old_references.person_id = new_references.person_id)) OR
177 ((new_references.person_id IS NULL))) THEN
178 NULL;
179 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
180 new_references.person_id
181 ) THEN
182 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END IF;
186
187 END Check_Parent_Existance;
188
189 FUNCTION Get_PK_For_Validation (
190 x_past_history_id IN NUMBER
191 ) RETURN BOOLEAN AS
192
193 /*************************************************************
194 Created By : samaresh.in
195 Date Created By : 15-MAY-2000
196 Purpose :
197 Know limitations, enhancements or remarks
198 Change History
199 Who When What
200
201 (reverse chronological order - newest change first)
202 ***************************************************************/
203
204 CURSOR cur_rowid IS
205 SELECT rowid
206 FROM igs_ad_past_history_all
207 WHERE past_history_id = x_past_history_id
208 FOR UPDATE NOWAIT;
209
210 lv_rowid cur_rowid%RowType;
211
212 BEGIN
213
214 Open cur_rowid;
215 Fetch cur_rowid INTO lv_rowid;
216 IF (cur_rowid%FOUND) THEN
217 Close cur_rowid;
218 Return(TRUE);
219 ELSE
220 Close cur_rowid;
221 Return(FALSE);
222 END IF;
223 END Get_PK_For_Validation;
224
225 FUNCTION Get_UK_For_Validation (
226 x_person_id IN NUMBER,
227 x_subject_id IN NUMBER
228 ) RETURN BOOLEAN AS
229
230 /*************************************************************
231 Created By : samaresh.in
232 Date Created By : 15-MAY-2000
233 Purpose :
234 Know limitations, enhancements or remarks
235 Change History
236 Who When What
237
238 (reverse chronological order - newest change first)
239 ***************************************************************/
240
241 CURSOR cur_rowid IS
242 SELECT rowid
243 FROM igs_ad_past_history_all
244 WHERE person_id = x_person_id
245 AND subject_id = x_subject_id and ((l_rowid is null) or (rowid <> l_rowid))
246
247 ;
248 lv_rowid cur_rowid%RowType;
249
250 BEGIN
251
252 Open cur_rowid;
253 Fetch cur_rowid INTO lv_rowid;
254 IF (cur_rowid%FOUND) THEN
255 Close cur_rowid;
256 return (true);
257 ELSE
258 close cur_rowid;
259 return(false);
260 END IF;
261 END Get_UK_For_Validation ;
262 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
263 x_code_id IN NUMBER
264 ) AS
265
266 /*************************************************************
267 Created By : samaresh.in
268 Date Created By : 15-MAY-2000
269 Purpose :
270 Know limitations, enhancements or remarks
271 Change History
272 Who When What
273
274 (reverse chronological order - newest change first)
275 ***************************************************************/
276
277 CURSOR cur_rowid IS
278 SELECT rowid
279 FROM igs_ad_past_history_all
280 WHERE subject_id = x_code_id ;
281
282 lv_rowid cur_rowid%RowType;
283
284 BEGIN
285
286 Open cur_rowid;
287 Fetch cur_rowid INTO lv_rowid;
288 IF (cur_rowid%FOUND) THEN
289 Close cur_rowid;
290 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APH_ACDC_FK');
291 IGS_GE_MSG_STACK.ADD;
292 App_Exception.Raise_Exception;
293 Return;
294 END IF;
295 Close cur_rowid;
296
297 END Get_FK_Igs_Ad_Code_Classes;
298
299 PROCEDURE Get_FK_Igs_Pe_Person (
300 x_person_id IN NUMBER
301 ) AS
302
303 /*************************************************************
304 Created By : samaresh.in
305 Date Created By : 15-MAY-2000
306 Purpose :
307 Know limitations, enhancements or remarks
308 Change History
309 Who When What
310
311 (reverse chronological order - newest change first)
312 ***************************************************************/
313
314 CURSOR cur_rowid IS
315 SELECT rowid
316 FROM igs_ad_past_history_all
317 WHERE past_history_id = x_person_id ;
318
319 lv_rowid cur_rowid%RowType;
320
321 BEGIN
322
323 Open cur_rowid;
324 Fetch cur_rowid INTO lv_rowid;
325 IF (cur_rowid%FOUND) THEN
326 Close cur_rowid;
327 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APH_PE_FK');
328 IGS_GE_MSG_STACK.ADD;
329 App_Exception.Raise_Exception;
330 Return;
331 END IF;
332 Close cur_rowid;
333
334 END Get_FK_Igs_Pe_Person;
335
336 PROCEDURE Before_DML (
337 p_action IN VARCHAR2,
338 x_rowid IN VARCHAR2 DEFAULT NULL,
339 x_org_id IN NUMBER DEFAULT NULL,
340 x_past_history_id IN NUMBER DEFAULT NULL,
341 x_years_of_study IN NUMBER DEFAULT NULL,
342 x_subject_id IN NUMBER DEFAULT NULL,
343 x_honors IN VARCHAR2 DEFAULT NULL,
344 x_person_id IN NUMBER DEFAULT NULL,
345 x_creation_date IN DATE DEFAULT NULL,
346 x_created_by IN NUMBER DEFAULT NULL,
347 x_last_update_date IN DATE DEFAULT NULL,
348 x_last_updated_by IN NUMBER DEFAULT NULL,
349 x_last_update_login IN NUMBER DEFAULT NULL
350 ) AS
351 /*************************************************************
352 Created By : samaresh.in
353 Date Created By : 15-MAY-2000
354 Purpose :
355 Know limitations, enhancements or remarks
356 Change History
357 Who When What
358
359 (reverse chronological order - newest change first)
360 ***************************************************************/
361
362 BEGIN
363
364 Set_Column_Values (
365 p_action,
366 x_rowid,
367 x_org_id,
368 x_past_history_id,
369 x_years_of_study,
370 x_subject_id,
371 x_honors,
372 x_person_id,
373 x_creation_date,
374 x_created_by,
375 x_last_update_date,
376 x_last_updated_by,
377 x_last_update_login
378 );
379
380 IF (p_action = 'INSERT') THEN
381 -- Call all the procedures related to Before Insert.
382 Null;
383 IF Get_Pk_For_Validation(
384 new_references.past_history_id) THEN
385 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
386 IGS_GE_MSG_STACK.ADD;
387 App_Exception.Raise_Exception;
388 END IF;
389 Check_Uniqueness;
390 Check_Constraints;
391 Check_Parent_Existance;
392 ELSIF (p_action = 'UPDATE') THEN
393 -- Call all the procedures related to Before Update.
394 Null;
395 Check_Uniqueness;
396 Check_Constraints;
397 Check_Parent_Existance;
398 ELSIF (p_action = 'DELETE') THEN
399 -- Call all the procedures related to Before Delete.
400 Null;
401 ELSIF (p_action = 'VALIDATE_INSERT') THEN
402 -- Call all the procedures related to Before Insert.
403 IF Get_PK_For_Validation (
404 new_references.past_history_id) THEN
405 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
406 IGS_GE_MSG_STACK.ADD;
407 App_Exception.Raise_Exception;
408 END IF;
409 Check_Uniqueness;
410 Check_Constraints;
411 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
412 Check_Uniqueness;
413 Check_Constraints;
414 ELSIF (p_action = 'VALIDATE_DELETE') THEN
415 Null;
416 END IF;
417
418 l_rowid := NULL; --Bug:2863832
419
420 END Before_DML;
421
422 PROCEDURE After_DML (
423 p_action IN VARCHAR2,
424 x_rowid IN VARCHAR2
425 ) IS
426 /*************************************************************
427 Created By : samaresh.in
428 Date Created By : 15-MAY-2000
429 Purpose :
430 Know limitations, enhancements or remarks
431 Change History
432 Who When What
433
434 (reverse chronological order - newest change first)
435 ***************************************************************/
436
437 BEGIN
438
439 l_rowid := x_rowid;
440
441 IF (p_action = 'INSERT') THEN
442 -- Call all the procedures related to After Insert.
443 Null;
444 ELSIF (p_action = 'UPDATE') THEN
445 -- Call all the procedures related to After Update.
446 Null;
447 ELSIF (p_action = 'DELETE') THEN
448 -- Call all the procedures related to After Delete.
449 Null;
450 END IF;
451
452 l_rowid:=NULL;
453 END After_DML;
454
455 procedure INSERT_ROW (
456 X_ROWID in out NOCOPY VARCHAR2,
457 X_ORG_ID in NUMBER,
458 x_PAST_HISTORY_ID IN OUT NOCOPY NUMBER,
459 x_YEARS_OF_STUDY IN NUMBER,
460 x_SUBJECT_ID IN NUMBER,
461 x_HONORS IN VARCHAR2,
462 x_PERSON_ID IN NUMBER,
463 X_MODE in VARCHAR2 default 'R'
464 ) AS
465 /*************************************************************
466 Created By : samaresh.in
467 Date Created By : 15-MAY-2000
468 Purpose :
469 Know limitations, enhancements or remarks
470 Change History
471 Who When What
472
473 (reverse chronological order - newest change first)
474 ***************************************************************/
475
476 cursor C is select ROWID from igs_ad_past_history_all
477 where PAST_HISTORY_ID= X_PAST_HISTORY_ID
478 ;
479 X_LAST_UPDATE_DATE DATE ;
480 X_LAST_UPDATED_BY NUMBER ;
481 X_LAST_UPDATE_LOGIN NUMBER ;
482 begin
483 X_LAST_UPDATE_DATE := SYSDATE;
484 if(X_MODE = 'I') then
485 X_LAST_UPDATED_BY := 1;
486 X_LAST_UPDATE_LOGIN := 0;
487 elsif (X_MODE = 'R') then
488 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
489 if X_LAST_UPDATED_BY is NULL then
490 X_LAST_UPDATED_BY := -1;
491 end if;
492 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
493 if X_LAST_UPDATE_LOGIN is NULL then
494 X_LAST_UPDATE_LOGIN := -1;
495 end if;
496 else
497 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
498 IGS_GE_MSG_STACK.ADD;
499 app_exception.raise_exception;
500 end if;
501
502 X_PAST_HISTORY_ID := -1;
503 Before_DML(
504 p_action=>'INSERT',
505 x_rowid=>X_ROWID,
506 x_org_id=>igs_ge_gen_003.get_org_id,
507 x_past_history_id=>X_PAST_HISTORY_ID,
508 x_years_of_study=>X_YEARS_OF_STUDY,
509 x_subject_id=>X_SUBJECT_ID,
510 x_honors=>NVL(X_HONORS,'N'
511 ),
512 x_person_id=>X_PERSON_ID,
513 x_creation_date=>X_LAST_UPDATE_DATE,
514 x_created_by=>X_LAST_UPDATED_BY,
515 x_last_update_date=>X_LAST_UPDATE_DATE,
516 x_last_updated_by=>X_LAST_UPDATED_BY,
517 x_last_update_login=>X_LAST_UPDATE_LOGIN);
518 insert into igs_ad_past_history_all (
519 PAST_HISTORY_ID
520 ,YEARS_OF_STUDY
521 ,SUBJECT_ID
522 ,HONORS
523 ,PERSON_ID
524 ,CREATION_DATE
525 ,CREATED_BY
526 ,LAST_UPDATE_DATE
527 ,LAST_UPDATED_BY
528 ,LAST_UPDATE_LOGIN
529 ,ORG_ID
530 ) values (
531 IGS_AD_PAST_HISTORY_S.NEXTVAL
532 ,NEW_REFERENCES.YEARS_OF_STUDY
533 ,NEW_REFERENCES.SUBJECT_ID
534 ,NEW_REFERENCES.HONORS
535 ,NEW_REFERENCES.PERSON_ID
536 ,X_LAST_UPDATE_DATE
537 ,X_LAST_UPDATED_BY
538 ,X_LAST_UPDATE_DATE
539 ,X_LAST_UPDATED_BY
540 ,X_LAST_UPDATE_LOGIN
541 ,NEW_REFERENCES.ORG_ID
542 )RETURNING PAST_HISTORY_ID INTO X_PAST_HISTORY_ID ;
543 open c;
544 fetch c into X_ROWID;
545 if (c%notfound) then
546 close c;
547 raise no_data_found;
548 end if;
549 close c;
550 After_DML (
551 p_action => 'INSERT' ,
552 x_rowid => X_ROWID );
553
554 end INSERT_ROW;
555
556 procedure LOCK_ROW (
557 X_ROWID in VARCHAR2,
558 x_PAST_HISTORY_ID IN NUMBER,
559 x_YEARS_OF_STUDY IN NUMBER,
560 x_SUBJECT_ID IN NUMBER,
561 x_HONORS IN VARCHAR2,
562 x_PERSON_ID IN NUMBER ) AS
563 /*************************************************************
564 Created By : samaresh.in
565 Date Created By : 15-MAY-2000
566 Purpose :
567 Know limitations, enhancements or remarks
568 Change History
569 Who When What
570
571 (reverse chronological order - newest change first)
572 ***************************************************************/
573
574 cursor c1 is select
575 YEARS_OF_STUDY
576 , SUBJECT_ID
577 , HONORS
578 , PERSON_ID
579 from igs_ad_past_history_all
580 where ROWID = X_ROWID
581 for update nowait;
582 tlinfo c1%rowtype;
583 begin
584 open c1;
585 fetch c1 into tlinfo;
586 if (c1%notfound) then
587 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
588 IGS_GE_MSG_STACK.ADD;
589 close c1;
590 app_exception.raise_exception;
591 return;
592 end if;
593 close c1;
594 if ( ( tlinfo.YEARS_OF_STUDY = X_YEARS_OF_STUDY)
595 AND (tlinfo.SUBJECT_ID = X_SUBJECT_ID)
596 AND ((tlinfo.HONORS = X_HONORS)
597 OR ((tlinfo.HONORS is null)
598 AND (X_HONORS is null)))
599 AND (tlinfo.PERSON_ID = X_PERSON_ID)
600 ) then
601 null;
602 else
603 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
604 IGS_GE_MSG_STACK.ADD;
605 app_exception.raise_exception;
606 end if;
607 return;
608 end LOCK_ROW;
609 Procedure UPDATE_ROW (
610 X_ROWID in VARCHAR2,
611 x_PAST_HISTORY_ID IN NUMBER,
612 x_YEARS_OF_STUDY IN NUMBER,
613 x_SUBJECT_ID IN NUMBER,
614 x_HONORS IN VARCHAR2,
615 x_PERSON_ID IN NUMBER,
616 X_MODE in VARCHAR2 default 'R'
617 ) AS
618 /*************************************************************
619 Created By : samaresh.in
620 Date Created By : 15-MAY-2000
621 Purpose :
622 Know limitations, enhancements or remarks
623 Change History
624 Who When What
625
626 (reverse chronological order - newest change first)
627 ***************************************************************/
628
629 X_LAST_UPDATE_DATE DATE ;
630 X_LAST_UPDATED_BY NUMBER ;
631 X_LAST_UPDATE_LOGIN NUMBER ;
632 begin
633 X_LAST_UPDATE_DATE := SYSDATE;
634 if(X_MODE = 'I') then
635 X_LAST_UPDATED_BY := 1;
636 X_LAST_UPDATE_LOGIN := 0;
637 elsif (X_MODE = 'R') then
638 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
639 if X_LAST_UPDATED_BY is NULL then
640 X_LAST_UPDATED_BY := -1;
641 end if;
642 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
643 if X_LAST_UPDATE_LOGIN is NULL then
644 X_LAST_UPDATE_LOGIN := -1;
645 end if;
646 else
647 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
648 IGS_GE_MSG_STACK.ADD;
649 app_exception.raise_exception;
650 end if;
651 Before_DML(
652 p_action=>'UPDATE',
653 x_rowid=>X_ROWID,
654 x_past_history_id=>X_PAST_HISTORY_ID,
655 x_years_of_study=>X_YEARS_OF_STUDY,
656 x_subject_id=>X_SUBJECT_ID,
657 x_honors=>NVL(X_HONORS,'N'
658 ),
659 x_person_id=>X_PERSON_ID,
660 x_creation_date=>X_LAST_UPDATE_DATE,
661 x_created_by=>X_LAST_UPDATED_BY,
662 x_last_update_date=>X_LAST_UPDATE_DATE,
663 x_last_updated_by=>X_LAST_UPDATED_BY,
664 x_last_update_login=>X_LAST_UPDATE_LOGIN);
665 update igs_ad_past_history_all set
666 YEARS_OF_STUDY = NEW_REFERENCES.YEARS_OF_STUDY,
667 SUBJECT_ID = NEW_REFERENCES.SUBJECT_ID,
668 HONORS = NEW_REFERENCES.HONORS,
669 PERSON_ID = NEW_REFERENCES.PERSON_ID,
670 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
671 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
672 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
673 where ROWID = X_ROWID;
674 if (sql%notfound) then
675 raise no_data_found;
676 end if;
677
678 After_DML (
679 p_action => 'UPDATE' ,
680 x_rowid => X_ROWID
681 );
682 end UPDATE_ROW;
683 procedure ADD_ROW (
684 X_ROWID in out NOCOPY VARCHAR2,
685 X_ORG_ID in NUMBER,
686 x_PAST_HISTORY_ID IN OUT NOCOPY NUMBER,
687 x_YEARS_OF_STUDY IN NUMBER,
688 x_SUBJECT_ID IN NUMBER,
689 x_HONORS IN VARCHAR2,
690 x_PERSON_ID IN NUMBER,
691 X_MODE in VARCHAR2 default 'R'
692 ) AS
693 /*************************************************************
694 Created By : samaresh.in
695 Date Created By : 15-MAY-2000
696 Purpose :
697 Know limitations, enhancements or remarks
698 Change History
699 Who When What
700
701 (reverse chronological order - newest change first)
702 ***************************************************************/
703
704 cursor c1 is select ROWID from igs_ad_past_history_all
705 where PAST_HISTORY_ID= X_PAST_HISTORY_ID
706 ;
707 begin
708 open c1;
709 fetch c1 into X_ROWID;
710 if (c1%notfound) then
711 close c1;
712 INSERT_ROW (
713 X_ROWID,
714 X_ORG_ID,
715 X_PAST_HISTORY_ID,
716 X_YEARS_OF_STUDY,
717 X_SUBJECT_ID,
718 X_HONORS,
719 X_PERSON_ID,
720 X_MODE );
721 return;
722 end if;
723 close c1;
724 UPDATE_ROW (
725 X_ROWID,
726 X_PAST_HISTORY_ID,
727 X_YEARS_OF_STUDY,
728 X_SUBJECT_ID,
729 X_HONORS,
730 X_PERSON_ID,
731 X_MODE );
732 end ADD_ROW;
733 procedure DELETE_ROW (
734 X_ROWID in VARCHAR2
735 ) AS
736 /*************************************************************
737 Created By : samaresh.in
738 Date Created By : 15-MAY-2000
739 Purpose :
740 Know limitations, enhancements or remarks
741 Change History
742 Who When What
743
744 (reverse chronological order - newest change first)
745 ***************************************************************/
746
747 begin
748 Before_DML (
749 p_action => 'DELETE',
750 x_rowid => X_ROWID
751 );
752 delete from igs_ad_past_history_all
753 where ROWID = X_ROWID;
754 if (sql%notfound) then
755 raise no_data_found;
756 end if;
757 After_DML (
758 p_action => 'DELETE',
759 x_rowid => X_ROWID
760 );
761 end DELETE_ROW;
762 END igs_ad_past_history_pkg;
[Home] [Help]