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