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