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