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