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