[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_STD_TODO_REF_PKG
Source
1 package body IGS_PE_STD_TODO_REF_PKG AS
2 /* $Header: IGSNI37B.pls 120.0 2005/06/01 19:04:50 appldev noship $ */
3
4
5 l_rowid VARCHAR2(25);
6
7 old_references IGS_PE_STD_TODO_REF%RowType;
8
9 new_references IGS_PE_STD_TODO_REF%RowType;
10
11
12
13 PROCEDURE Set_Column_Values (
14
15 p_action IN VARCHAR2,
16
17 x_rowid IN VARCHAR2 DEFAULT NULL,
18
19 x_person_id IN NUMBER DEFAULT NULL,
20
21 x_s_student_todo_type IN VARCHAR2 DEFAULT NULL,
22
23 x_sequence_number IN NUMBER DEFAULT NULL,
24
25 x_reference_number IN NUMBER DEFAULT NULL,
26
27 x_cal_type IN VARCHAR2 DEFAULT NULL,
28
29 x_ci_sequence_number IN NUMBER DEFAULT NULL,
30
31 x_course_cd IN VARCHAR2 DEFAULT NULL,
32
33 x_unit_cd IN VARCHAR2 DEFAULT NULL,
34
35 x_other_reference IN VARCHAR2 DEFAULT NULL,
36
37 x_logical_delete_dt IN DATE DEFAULT NULL,
38
39 x_creation_date IN DATE DEFAULT NULL,
40
41 x_created_by IN NUMBER DEFAULT NULL,
42
43 x_last_update_date IN DATE DEFAULT NULL,
44
45 x_last_updated_by IN NUMBER DEFAULT NULL,
46
47 x_last_update_login IN NUMBER DEFAULT NULL,
48
49 x_uoo_id IN NUMBER DEFAULT NULL
50
51 ) AS
52
53
54
55 CURSOR cur_old_ref_values IS
56
57 SELECT *
58
59 FROM IGS_PE_STD_TODO_REF
60
61 WHERE rowid = x_rowid;
62
63
64
65 BEGIN
66
67
68
69 l_rowid := x_rowid;
70
71
72
73 -- Code for setting the Old and New Reference Values.
74
75 -- Populate Old Values.
76
77 Open cur_old_ref_values;
78
79 Fetch cur_old_ref_values INTO old_references;
80
81 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN( 'INSERT','VALIDATE_INSERT')) THEN
82
83 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
84 IGS_GE_MSG_STACK.ADD;
85
86
87 Close cur_old_ref_values;
88 App_Exception.Raise_Exception;
89 Return;
90
91 END IF;
92
93 Close cur_old_ref_values;
94
95
96
97 -- Populate New Values.
98
99 new_references.person_id := x_person_id;
100
101 new_references.s_student_todo_type := x_s_student_todo_type;
102
103 new_references.sequence_number := x_sequence_number;
104
105 new_references.reference_number := x_reference_number;
106
107 new_references.cal_type:= x_cal_type;
108
109 new_references.ci_sequence_number := x_ci_sequence_number;
110
111 new_references.course_cd := x_course_cd;
112
113 new_references.unit_cd := x_unit_cd;
114
115 new_references.other_reference := x_other_reference;
116
117 new_references.logical_delete_dt := x_logical_delete_dt;
118
119 new_references.uoo_id := x_uoo_id;
120
121 IF (p_action = 'UPDATE') THEN
122
123 new_references.creation_date := old_references.creation_date;
124
125 new_references.created_by := old_references.created_by;
126
127 ELSE
128
129 new_references.creation_date := x_creation_date;
130
131 new_references.created_by := x_created_by;
132
133 END IF;
134
135 new_references.last_update_date := x_last_update_date;
136
137 new_references.last_updated_by := x_last_updated_by;
138
139 new_references.last_update_login := x_last_update_login;
140
141
142
143 END Set_Column_Values;
144
145
146
147
148 PROCEDURE Check_Constraints (
149 Column_Name IN VARCHAR2 DEFAULT NULL,
150 Column_Value IN VARCHAR2 DEFAULT NULL
151 )
152 AS
153 BEGIN
154 IF column_name is null then
155 NULL;
156 ELSIF upper(Column_name) = 'CAL_TYPE' then
157 new_references.cal_type:= column_value;
158 ELSIF upper(Column_name) = 'COURSE_CD' then
159 new_references.course_cd:= column_value;
160 ELSIF upper(Column_name) = 'OTHER_REFERENCE' then
161 new_references.other_reference:= column_value;
162 ELSIF upper(Column_name) = 'S_STUDENT_TODO_TYPE' then
163 new_references.s_student_todo_type:= column_value;
164 ELSIF upper(Column_name) = 'UNIT_CD' then
165 new_references.unit_cd:= column_value;
166 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' then
167 new_references.sequence_number := IGS_GE_NUMBER.to_num(column_value);
168 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
169 new_references.ci_sequence_number :=IGS_GE_NUMBER.to_num(column_value);
170 ELSIF upper(Column_name) = 'REFERENCE_NUMBER' then
171 new_references.reference_number := IGS_GE_NUMBER.to_num(column_value);
172
173 END IF;
174
175 IF upper(column_name) = 'CAL_TYPE' OR
176 column_name is null Then
177 IF new_references.cal_type <>UPPER(new_references.cal_type )Then
178 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182 END IF;
183 IF upper(column_name) = 'COURSE_CD' OR
184 column_name is null Then
185 IF new_references.course_cd <>UPPER(new_references.course_cd)Then
186 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190 END IF;
191 IF upper(column_name) = 'OTHER_REFERENCE' OR
192 column_name is null Then
193 IF new_references.other_reference <>UPPER(new_references.other_reference)Then
194 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
195 IGS_GE_MSG_STACK.ADD;
196 App_Exception.Raise_Exception;
197 END IF;
198 END IF;
199
200 IF upper(column_name) = 'S_STUDENT_TODO_TYPE' OR
201 column_name is null Then
202 IF new_references.s_student_todo_type <>UPPER(new_references.s_student_todo_type)Then
203 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
204 IGS_GE_MSG_STACK.ADD;
205 App_Exception.Raise_Exception;
206 END IF;
207 END IF;
208 IF upper(column_name) = 'UNIT_CD' OR
209 column_name is null Then
210 IF new_references.unit_cd <>UPPER(new_references.unit_cd ) Then
211 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
212 IGS_GE_MSG_STACK.ADD;
213 App_Exception.Raise_Exception;
214 END IF;
215 END IF;
216
217 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR
218 column_name is null Then
219 IF new_references.ci_sequence_number < 1 OR new_references.ci_sequence_number > 999999 Then
220 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223 END IF;
224 END IF;
225
226 -- remove check on reference number.
227
228 END Check_Constraints;
229
230
231
232 PROCEDURE Check_Parent_Existance AS
233
234 BEGIN
235 IF (((old_references.person_id = new_references.person_id) AND
236 (old_references.s_student_todo_type = new_references.s_student_todo_type) AND
237 (old_references.sequence_number = new_references.sequence_number)) OR
238 ((new_references.person_id IS NULL) OR
239 (new_references.s_student_todo_type IS NULL) OR
240 (new_references.sequence_number IS NULL))) THEN
241 NULL;
242
243 ELSE
244 IF NOT IGS_PE_STD_TODO_PKG.Get_PK_For_Validation (
245 new_references.person_id,
246 new_references.s_student_todo_type,
247 new_references.sequence_number ) THEN
248 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
249 IGS_GE_MSG_STACK.ADD;
250 App_Exception.Raise_Exception;
251 END IF;
252 END IF;
253 END Check_Parent_Existance;
254
255
256
257 FUNCTION Get_PK_For_Validation (
258
259 x_person_id IN NUMBER,
260
261 x_s_student_todo_type IN VARCHAR2,
262
263 x_sequence_number IN NUMBER,
264
265 x_reference_number IN NUMBER
266
267 ) RETURN BOOLEAN AS
268
269
270
271 CURSOR cur_rowid IS
272
273 SELECT rowid
274
275 FROM IGS_PE_STD_TODO_REF
276
277 WHERE person_id = x_person_id
278
279 AND s_student_todo_type = x_s_student_todo_type
280
281 AND sequence_number = x_sequence_number
282
283 AND reference_number = x_reference_number
284
285 FOR UPDATE NOWAIT;
286
287
288
289 lv_rowid cur_rowid%RowType;
290
291
292
293 BEGIN
294 Open cur_rowid;
295 Fetch cur_rowid INTO lv_rowid;
296 IF (cur_rowid%FOUND) THEN
297 Close cur_rowid;
298 Return (TRUE);
299 ELSE
300 Close cur_rowid;
301 Return (FALSE);
302 END IF;
303 END Get_PK_For_Validation;
304
305
306
307 PROCEDURE GET_FK_IGS_PE_STD_TODO (
308
309 x_person_id IN NUMBER,
310
311 x_s_student_todo_type IN VARCHAR2,
312
313 x_sequence_number IN NUMBER
314
315 ) AS
316
317
318
319 CURSOR cur_rowid IS
320
321 SELECT rowid
322
323 FROM IGS_PE_STD_TODO_REF
324
325 WHERE person_id = x_person_id
326
327 AND s_student_todo_type = x_s_student_todo_type
328
329 AND sequence_number = x_sequence_number ;
330
331
332
333 lv_rowid cur_rowid%RowType;
334
335
336
337 BEGIN
338
339
340
341 Open cur_rowid;
342
343 Fetch cur_rowid INTO lv_rowid;
344
345 IF (cur_rowid%FOUND) THEN
346
347 Fnd_Message.Set_Name ('IGS', 'IGS_PE_STR_ST_FK');
348 IGS_GE_MSG_STACK.ADD;
349
350
351 Close cur_rowid;
352 App_Exception.Raise_Exception;
353 Return;
354
355 END IF;
356
357 Close cur_rowid;
358
359
360 END GET_FK_IGS_PE_STD_TODO;
361
362
363
364 PROCEDURE Before_DML (
365
366 p_action IN VARCHAR2,
367
368 x_rowid IN VARCHAR2 DEFAULT NULL,
369
370 x_person_id IN NUMBER DEFAULT NULL,
371
372 x_s_student_todo_type IN VARCHAR2 DEFAULT NULL,
373
374 x_sequence_number IN NUMBER DEFAULT NULL,
375
376 x_reference_number IN NUMBER DEFAULT NULL,
377
378 x_cal_type IN VARCHAR2 DEFAULT NULL,
379
380 x_ci_sequence_number IN NUMBER DEFAULT NULL,
381
382 x_course_cd IN VARCHAR2 DEFAULT NULL,
383
384 x_unit_cd IN VARCHAR2 DEFAULT NULL,
385
386 x_other_reference IN VARCHAR2 DEFAULT NULL,
387
388 x_logical_delete_dt IN DATE DEFAULT NULL,
389
390 x_creation_date IN DATE DEFAULT NULL,
391
392 x_created_by IN NUMBER DEFAULT NULL,
393
394 x_last_update_date IN DATE DEFAULT NULL,
395
396 x_last_updated_by IN NUMBER DEFAULT NULL,
397
398 x_last_update_login IN NUMBER DEFAULT NULL,
399
400 x_uoo_id IN NUMBER DEFAULT NULL
401
402 ) AS
403
404 BEGIN
405
406
407
408 Set_Column_Values (
409
410 p_action,
411
412 x_rowid,
413
414 x_person_id,
415
416 x_s_student_todo_type,
417
418 x_sequence_number,
419
420 x_reference_number,
421
422 x_cal_type,
423
424 x_ci_sequence_number,
425
426 x_course_cd,
427
428 x_unit_cd,
429
430 x_other_reference,
431
432 x_logical_delete_dt,
433
434 x_creation_date,
435
436 x_created_by,
437
438 x_last_update_date,
439
440 x_last_updated_by,
441
442 x_last_update_login,
443
444 x_uoo_id
445
446 );
447
448
449
450 IF (p_action = 'INSERT') THEN
451 -- Call all the procedures related to Before Insert.
452
453 IF Get_PK_For_Validation (
454 new_references.person_id ,
455 new_references.s_student_todo_type ,
456 new_references.sequence_number ,
457 new_references.reference_number ) THEN
458 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
459 IGS_GE_MSG_STACK.ADD;
460 App_Exception.Raise_Exception;
461 END IF;
462
463 Check_Constraints; -- if procedure present
464 Check_Parent_Existance; -- if procedure present
465 ELSIF (p_action = 'UPDATE') THEN
466 -- Call all the procedures related to Before Update.
467
468 Check_Constraints; -- if procedure present
469 Check_Parent_Existance; -- if procedure present
470
471 ELSIF (p_action = 'DELETE') THEN
472 -- Call all the procedures related to Before Delete.
473
474 NULL;
475 ELSIF (p_action = 'VALIDATE_INSERT') THEN
476 IF Get_PK_For_Validation (
477 new_references.person_id ,
478 new_references.s_student_todo_type ,
479 new_references.sequence_number ,
480 new_references.reference_number ) THEN
481 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
482 IGS_GE_MSG_STACK.ADD;
483 App_Exception.Raise_Exception;
484 END IF;
485
486 Check_Constraints; -- if procedure present
487 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
488
489 Check_Constraints; -- if procedure present
490
491 ELSIF (p_action = 'VALIDATE_DELETE') THEN
492 NULL;
493 END IF;
494
495
496
497 END Before_DML;
498
499
500
501 PROCEDURE After_DML (
502
503 p_action IN VARCHAR2,
504
505 x_rowid IN VARCHAR2
506
507 ) AS
508
509 BEGIN
510
511
512
513 l_rowid := x_rowid;
514
515
516
517 IF (p_action = 'INSERT') THEN
518
519 -- Call all the procedures related to After Insert.
520
521 Null;
522
523 ELSIF (p_action = 'UPDATE') THEN
524
525 -- Call all the procedures related to After Update.
526
527 Null;
528
529 ELSIF (p_action = 'DELETE') THEN
530
531 -- Call all the procedures related to After Delete.
532
533 Null;
534
535 END IF;
536
537
538
539 END After_DML;
540
541
542
543
544
545 --
546 procedure INSERT_ROW (
547 X_ROWID in out NOCOPY VARCHAR2,
548 X_PERSON_ID in NUMBER,
549 X_S_STUDENT_TODO_TYPE in VARCHAR2,
550 X_SEQUENCE_NUMBER in NUMBER,
551 X_REFERENCE_NUMBER in NUMBER,
552 X_CAL_TYPE in VARCHAR2,
553 X_CI_SEQUENCE_NUMBER in NUMBER,
554 X_COURSE_CD in VARCHAR2,
555 X_UNIT_CD in VARCHAR2,
556 X_OTHER_REFERENCE in VARCHAR2,
557 X_LOGICAL_DELETE_DT in DATE,
558 X_MODE in VARCHAR2 default 'R',
559 X_UOO_ID in NUMBER
560 ) AS
561 cursor C is select ROWID from IGS_PE_STD_TODO_REF
562 where PERSON_ID = X_PERSON_ID
563 and S_STUDENT_TODO_TYPE = X_S_STUDENT_TODO_TYPE
564 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
565 and REFERENCE_NUMBER = X_REFERENCE_NUMBER;
566 X_LAST_UPDATE_DATE DATE;
567 X_LAST_UPDATED_BY NUMBER;
568 X_LAST_UPDATE_LOGIN NUMBER;
569
570 X_REQUEST_ID NUMBER;
571
572 X_PROGRAM_ID NUMBER;
573
574 X_PROGRAM_APPLICATION_ID NUMBER;
575
576 X_PROGRAM_UPDATE_DATE DATE;
577 gv_other_detail VARCHAR2(255);
578 begin
579 X_LAST_UPDATE_DATE := SYSDATE;
580 if(X_MODE = 'I') then
581 X_LAST_UPDATED_BY := 1;
582 X_LAST_UPDATE_LOGIN := 0;
583 elsif (X_MODE = 'R') then
584 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
585 if X_LAST_UPDATED_BY is NULL then
586 X_LAST_UPDATED_BY := -1;
587 end if;
588 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
589 if X_LAST_UPDATE_LOGIN is NULL then
590 X_LAST_UPDATE_LOGIN := -1;
591 end if;
592
593 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
594
595 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
596
597
598
599 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
600
601 if (X_REQUEST_ID = -1) then
602
603 X_REQUEST_ID := NULL;
604
605 X_PROGRAM_ID := NULL;
606
607 X_PROGRAM_APPLICATION_ID := NULL;
608
609 X_PROGRAM_UPDATE_DATE := NULL;
610
611 else
612
613 X_PROGRAM_UPDATE_DATE := SYSDATE;
614
615 end if;
616 else
617 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
618 IGS_GE_MSG_STACK.ADD;
619 app_exception.raise_exception;
620 end if;
621
622
623
624 Before_DML(
625
626 p_action=>'INSERT',
627
628 x_rowid=>X_ROWID,
629
630 x_cal_type=>X_CAL_TYPE,
631
632 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
633
634 x_course_cd=>X_COURSE_CD,
635
636 x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
637
638 x_other_reference=>X_OTHER_REFERENCE,
639
640 x_person_id=>X_PERSON_ID,
641
642 x_reference_number=>X_REFERENCE_NUMBER,
643
644 x_s_student_todo_type=>X_S_STUDENT_TODO_TYPE,
645
646 x_sequence_number=>X_SEQUENCE_NUMBER,
647
648 x_unit_cd=>X_UNIT_CD,
649
650 x_creation_date=>X_LAST_UPDATE_DATE,
651
652 x_created_by=>X_LAST_UPDATED_BY,
653
654 x_last_update_date=>X_LAST_UPDATE_DATE,
655
656 x_last_updated_by=>X_LAST_UPDATED_BY,
657
658 x_last_update_login=>X_LAST_UPDATE_LOGIN,
659
660 x_uoo_id=>X_UOO_ID
661
662 );
663
664
665
666 insert into IGS_PE_STD_TODO_REF (
667 PERSON_ID,
668 S_STUDENT_TODO_TYPE,
669 SEQUENCE_NUMBER,
670 REFERENCE_NUMBER,
671 CAL_TYPE,
672 CI_SEQUENCE_NUMBER,
673 COURSE_CD,
674 UNIT_CD,
675 OTHER_REFERENCE,
676 LOGICAL_DELETE_DT,
677 CREATION_DATE,
678 CREATED_BY,
679 LAST_UPDATE_DATE,
680 LAST_UPDATED_BY,
681 LAST_UPDATE_LOGIN,
682
683 REQUEST_ID,
684
685 PROGRAM_ID,
686
687 PROGRAM_APPLICATION_ID,
688
689 PROGRAM_UPDATE_DATE,
690 UOO_ID
691 ) values (
692 NEW_REFERENCES.PERSON_ID,
693 NEW_REFERENCES.S_STUDENT_TODO_TYPE,
694 NEW_REFERENCES.SEQUENCE_NUMBER,
695 NEW_REFERENCES.REFERENCE_NUMBER,
696 NEW_REFERENCES.CAL_TYPE,
697 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
698 NEW_REFERENCES.COURSE_CD,
699 NEW_REFERENCES.UNIT_CD,
700 NEW_REFERENCES.OTHER_REFERENCE,
701 NEW_REFERENCES.LOGICAL_DELETE_DT,
702 X_LAST_UPDATE_DATE,
703 X_LAST_UPDATED_BY,
704 X_LAST_UPDATE_DATE,
705 X_LAST_UPDATED_BY,
706 X_LAST_UPDATE_LOGIN,
707
708 X_REQUEST_ID,
709
710 X_PROGRAM_ID,
711
712 X_PROGRAM_APPLICATION_ID,
713
714 X_PROGRAM_UPDATE_DATE ,
715 NEW_REFERENCES.UOO_ID
716 );
717
718 open c;
719 fetch c into X_ROWID;
720 if (c%notfound) then
721 close c;
722 raise no_data_found;
723 end if;
724 close c;
725
726
727 After_DML(
728
729 p_action => 'INSERT',
730
731 x_rowid => X_ROWID
732
733 );
734
735 EXCEPTION
736 WHEN OTHERS THEN
737 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
738 IGS_GE_MSG_STACK.ADD;
739 App_Exception.Raise_Exception;
740
741 end INSERT_ROW;
742
743 procedure LOCK_ROW (
744
745 X_ROWID in VARCHAR2,
746 X_PERSON_ID in NUMBER,
747 X_S_STUDENT_TODO_TYPE in VARCHAR2,
748 X_SEQUENCE_NUMBER in NUMBER,
749 X_REFERENCE_NUMBER in NUMBER,
750 X_CAL_TYPE in VARCHAR2,
751 X_CI_SEQUENCE_NUMBER in NUMBER,
752 X_COURSE_CD in VARCHAR2,
753 X_UNIT_CD in VARCHAR2,
754 X_OTHER_REFERENCE in VARCHAR2,
755 X_LOGICAL_DELETE_DT in DATE,
756 X_UOO_ID in NUMBER
757 ) AS
758 cursor c1 is select
759 CAL_TYPE,
760 CI_SEQUENCE_NUMBER,
761 COURSE_CD,
762 UNIT_CD,
763 OTHER_REFERENCE,
764 LOGICAL_DELETE_DT,
765 UOO_ID
766 from IGS_PE_STD_TODO_REF
767 where ROWID = X_ROWID
768 for update nowait;
769 tlinfo c1%rowtype;
770
771 begin
772 open c1;
773 fetch c1 into tlinfo;
774 if (c1%notfound) then
775 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
776 close c1;
777 App_Exception.Raise_Exception;
778 return;
779 end if;
780 close c1;
781
782 if ( ((tlinfo.CAL_TYPE = X_CAL_TYPE)
783 OR ((tlinfo.CAL_TYPE is null)
784 AND (X_CAL_TYPE is null)))
785 AND ((tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
786 OR ((tlinfo.CI_SEQUENCE_NUMBER is null)
787 AND (X_CI_SEQUENCE_NUMBER is null)))
788 AND ((tlinfo.COURSE_CD = X_COURSE_CD)
789 OR ((tlinfo.COURSE_CD is null)
790 AND (X_COURSE_CD is null)))
791 AND ((tlinfo.UNIT_CD = X_UNIT_CD)
792 OR ((tlinfo.UNIT_CD is null)
793 AND (X_UNIT_CD is null)))
794 AND ((tlinfo.OTHER_REFERENCE = X_OTHER_REFERENCE)
795 OR ((tlinfo.OTHER_REFERENCE is null)
796 AND (X_OTHER_REFERENCE is null)))
797 AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
798 OR ((tlinfo.LOGICAL_DELETE_DT is null)
799 AND (X_LOGICAL_DELETE_DT is null)))
800 AND ((tlinfo.UOO_ID = X_UOO_ID)
801 OR ((tlinfo.UOO_ID is null)
802 AND (X_UOO_ID is null)))
803
804 ) then
805 null;
806 else
807 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
808 app_exception.raise_exception;
809 end if;
810 return;
811 end LOCK_ROW;
812
813 procedure UPDATE_ROW (
814
815 X_ROWID in VARCHAR2,
816 X_PERSON_ID in NUMBER,
817 X_S_STUDENT_TODO_TYPE in VARCHAR2,
818 X_SEQUENCE_NUMBER in NUMBER,
819 X_REFERENCE_NUMBER in NUMBER,
820 X_CAL_TYPE in VARCHAR2,
821 X_CI_SEQUENCE_NUMBER in NUMBER,
822 X_COURSE_CD in VARCHAR2,
823 X_UNIT_CD in VARCHAR2,
824 X_OTHER_REFERENCE in VARCHAR2,
825 X_LOGICAL_DELETE_DT in DATE,
826 X_MODE in VARCHAR2 default 'R',
827 X_UOO_ID in NUMBER
828 ) AS
829 X_LAST_UPDATE_DATE DATE;
830 X_LAST_UPDATED_BY NUMBER;
831 X_LAST_UPDATE_LOGIN NUMBER;
832
833 X_REQUEST_ID NUMBER;
834
835 X_PROGRAM_ID NUMBER;
836
837 X_PROGRAM_APPLICATION_ID NUMBER;
838
839 X_PROGRAM_UPDATE_DATE DATE;
840 begin
841 X_LAST_UPDATE_DATE := SYSDATE;
842 if(X_MODE = 'I') then
843 X_LAST_UPDATED_BY := 1;
844 X_LAST_UPDATE_LOGIN := 0;
845 elsif (X_MODE = 'R') then
846 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
847 if X_LAST_UPDATED_BY is NULL then
848 X_LAST_UPDATED_BY := -1;
849 end if;
850 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
851 if X_LAST_UPDATE_LOGIN is NULL then
852 X_LAST_UPDATE_LOGIN := -1;
853 end if;
854 else
855 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
856 IGS_GE_MSG_STACK.ADD;
857 app_exception.raise_exception;
858 end if;
859
860 --
861
862
863
864 Before_DML(
865
866 p_action=>'UPDATE',
867
868 x_rowid=>X_ROWID,
869
870 x_cal_type=>X_CAL_TYPE,
871
872 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
873
874 x_course_cd=>X_COURSE_CD,
875
876 x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
877
878 x_other_reference=>X_OTHER_REFERENCE,
879
880 x_person_id=>X_PERSON_ID,
881
882 x_reference_number=>X_REFERENCE_NUMBER,
883
884 x_s_student_todo_type=>X_S_STUDENT_TODO_TYPE,
885
886 x_sequence_number=>X_SEQUENCE_NUMBER,
887
888 x_unit_cd=>X_UNIT_CD,
889
890 x_creation_date=>X_LAST_UPDATE_DATE,
891
892 x_created_by=>X_LAST_UPDATED_BY,
893
894 x_last_update_date=>X_LAST_UPDATE_DATE,
895
896 x_last_updated_by=>X_LAST_UPDATED_BY,
897
898 x_last_update_login=>X_LAST_UPDATE_LOGIN,
899 x_uoo_id=>X_UOO_ID
900
901
902 );
903
904
905
906 --
907
908 if (X_MODE = 'R') then
909
910 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
911
912 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
913
914 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
915
916 if (X_REQUEST_ID = -1) then
917
918 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
919
920 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
921
922 X_PROGRAM_APPLICATION_ID :=
923
924 OLD_REFERENCES.PROGRAM_APPLICATION_ID;
925
926 X_PROGRAM_UPDATE_DATE :=
927
928 OLD_REFERENCES.PROGRAM_UPDATE_DATE;
929
930 else
931
932 X_PROGRAM_UPDATE_DATE := SYSDATE;
933
934
935
936 end if;
937
938
939
940 end if;
941
942
943
944 update IGS_PE_STD_TODO_REF set
945 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
946 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
947 COURSE_CD = NEW_REFERENCES.COURSE_CD,
948 UNIT_CD = NEW_REFERENCES.UNIT_CD,
949 OTHER_REFERENCE = NEW_REFERENCES.OTHER_REFERENCE,
950 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
951 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
952 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
953 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
954
955 REQUEST_ID = X_REQUEST_ID,
956
957 PROGRAM_ID = X_PROGRAM_ID,
958
959 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
960
961 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
962 UOO_ID = NEW_REFERENCES.UOO_ID
963 where ROWID = X_ROWID
964 ;
965 if (sql%notfound) then
966 raise no_data_found;
967 end if;
968
969
970 After_DML(
971
972 p_action => 'UPDATE',
973
974 x_rowid => X_ROWID
975
976 );
977
978
979
980 end UPDATE_ROW;
981
982 procedure ADD_ROW (
983 X_ROWID in out NOCOPY VARCHAR2,
984 X_PERSON_ID in NUMBER,
985 X_S_STUDENT_TODO_TYPE in VARCHAR2,
986 X_SEQUENCE_NUMBER in NUMBER,
987 X_REFERENCE_NUMBER in NUMBER,
988 X_CAL_TYPE in VARCHAR2,
989 X_CI_SEQUENCE_NUMBER in NUMBER,
990 X_COURSE_CD in VARCHAR2,
991 X_UNIT_CD in VARCHAR2,
992 X_OTHER_REFERENCE in VARCHAR2,
993 X_LOGICAL_DELETE_DT in DATE,
994 X_MODE in VARCHAR2 default 'R',
995 X_UOO_ID in NUMBER
996 ) AS
997 cursor c1 is select rowid from IGS_PE_STD_TODO_REF
998 where PERSON_ID = X_PERSON_ID
999 and S_STUDENT_TODO_TYPE = X_S_STUDENT_TODO_TYPE
1000 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1001 and REFERENCE_NUMBER = X_REFERENCE_NUMBER
1002 ;
1003
1004 begin
1005 open c1;
1006 fetch c1 into X_ROWID;
1007 if (c1%notfound) then
1008 close c1;
1009 INSERT_ROW (
1010 X_ROWID,
1011 X_PERSON_ID,
1012 X_S_STUDENT_TODO_TYPE,
1013 X_SEQUENCE_NUMBER,
1014 X_REFERENCE_NUMBER,
1015 X_CAL_TYPE,
1016 X_CI_SEQUENCE_NUMBER,
1017 X_COURSE_CD,
1018 X_UNIT_CD,
1019 X_OTHER_REFERENCE,
1020 X_LOGICAL_DELETE_DT,
1021 X_MODE,
1022 X_UOO_ID);
1023 return;
1024 end if;
1025 close c1;
1026 UPDATE_ROW (
1027
1028 X_ROWID,
1029 X_PERSON_ID,
1030 X_S_STUDENT_TODO_TYPE,
1031 X_SEQUENCE_NUMBER,
1032 X_REFERENCE_NUMBER,
1033 X_CAL_TYPE,
1034 X_CI_SEQUENCE_NUMBER,
1035 X_COURSE_CD,
1036 X_UNIT_CD,
1037 X_OTHER_REFERENCE,
1038 X_LOGICAL_DELETE_DT,
1039 X_MODE,
1040 X_UOO_ID);
1041 end ADD_ROW;
1042
1043 procedure DELETE_ROW (
1044 X_ROWID in VARCHAR2
1045 ) AS
1046 begin
1047
1048
1049
1050 Before_DML(
1051
1052 p_action => 'DELETE',
1053
1054 x_rowid => X_ROWID
1055
1056 );
1057
1058
1059 delete from IGS_PE_STD_TODO_REF
1060 where ROWID = X_ROWID;
1061 if (sql%notfound) then
1062 raise no_data_found;
1063 end if;
1064
1065
1066
1067 After_DML(
1068
1069 p_action => 'DELETE',
1070
1071 x_rowid => X_ROWID
1072
1073 );
1074 end DELETE_ROW;
1075
1076 end IGS_PE_STD_TODO_REF_PKG;