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