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