[Home] [Help]
PACKAGE BODY: APPS.IGS_RE_CDT_ATT_HIST_PKG
Source
1 package body IGS_RE_CDT_ATT_HIST_PKG as
2 /* $Header: IGSRI03B.pls 120.1 2005/07/04 00:41:15 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_re_val_cah.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 --svanukur 17-feb-2004 added logic after call to IGS_RE_VAL_CAH.resp_val_cah_hist_dt to
9 -- not raise an exceptio if the message is IGS_RE_ATND_HIST_STRT_CRS_ATM
10 --svanukur 13-APR-2004 removed the call to resp_val_cah_strt_dt since this procedure was modified
11 -- to return true in all cases. as part of bug 3544986
12 -------------------------------------------------------------------------------------------
13 l_rowid VARCHAR2(25);
14 old_references IGS_RE_CDT_ATT_HIST_ALL%RowType;
15 new_references IGS_RE_CDT_ATT_HIST_ALL%RowType;
16
17 PROCEDURE Set_Column_Values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2 DEFAULT NULL,
20 x_person_id IN NUMBER DEFAULT NULL,
21 x_ca_sequence_number IN NUMBER DEFAULT NULL,
22 x_sequence_number IN NUMBER DEFAULT NULL,
23 x_hist_start_dt IN DATE DEFAULT NULL,
24 x_hist_end_dt IN DATE DEFAULT NULL,
25 x_attendance_type IN VARCHAR2 DEFAULT NULL,
26 x_attendance_percentage IN NUMBER DEFAULT NULL,
27 x_creation_date IN DATE DEFAULT NULL,
28 x_created_by IN NUMBER DEFAULT NULL,
29 x_last_update_date IN DATE DEFAULT NULL,
30 x_last_updated_by IN NUMBER DEFAULT NULL,
31 x_last_update_login IN NUMBER DEFAULT NULL ,
32 x_org_id IN NUMBER DEFAULT NULL
33 ) AS
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_RE_CDT_ATT_HIST_ALL
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 Open cur_old_ref_values;
47 Fetch cur_old_ref_values INTO old_references;
48 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
49 Close cur_old_ref_values;
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_GE_MSG_STACK.ADD;
52 App_Exception.Raise_Exception;
53 Return;
54 END IF;
55 Close cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.person_id := x_person_id;
59 new_references.ca_sequence_number := x_ca_sequence_number;
60 new_references.sequence_number := x_sequence_number;
61 new_references.hist_start_dt := x_hist_start_dt;
62 new_references.hist_end_dt := x_hist_end_dt;
63 new_references.attendance_type := x_attendance_type;
64 new_references.attendance_percentage := x_attendance_percentage;
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72 new_references.last_update_date := x_last_update_date;
73 new_references.last_updated_by := x_last_updated_by;
74 new_references.last_update_login := x_last_update_login;
75 new_references.org_id := x_org_id;
76 END Set_Column_Values;
77
78 PROCEDURE BeforeRowInsertUpdateDelete1(
79 p_inserting IN BOOLEAN DEFAULT FALSE,
80 p_updating IN BOOLEAN DEFAULT FALSE,
81 p_deleting IN BOOLEAN DEFAULT FALSE
82 ) AS
83 v_message_name VARCHAR2(30);
84 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
85 v_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
86 v_person_id IGS_RE_CDT_ATT_HIST.person_id%TYPE;
87 v_ca_sequence_number IGS_RE_CDT_ATT_HIST.ca_sequence_number%TYPE;
88 BEGIN
89 -- Turn off trigger validation when performing insert of IGS_RE_CANDIDATURE details
90 -- as a result of IGS_PS_COURSE transfer
91 IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
92 IF p_inserting THEN
93 -- Validate insert against IGS_RE_CANDIDATURE
94 IF IGS_RE_VAL_CAH.resp_val_cah_ca_ins (
95 new_references.person_id,
96 new_references.ca_sequence_number,
97 NULL, -- ca.sca_course_cd
98 v_commencement_dt,
99 v_attendance_type,
100 v_message_name) = FALSE THEN
101 Fnd_Message.Set_Name ('IGS', v_message_name);
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105 END IF;
106 IF p_inserting OR
107 p_updating OR
108 p_deleting THEN
109 -- Validate updates are allowed
110 IF IGS_RE_VAL_CAH.resp_val_ca_childupd (
111 new_references.person_id,
112 new_references.ca_sequence_number,
113 v_message_name) = FALSE THEN
114 Fnd_Message.Set_Name ('IGS', v_message_name);
115 IGS_GE_MSG_STACK.ADD;
116 App_Exception.Raise_Exception;
117 END IF;
118 END IF;
119 IF p_inserting OR
120 p_updating THEN
121
122 -- Validate history end date.
123 IF p_inserting OR
124 (p_updating AND
125 (new_references.hist_end_dt <> old_references.hist_end_dt) OR
126 (new_references.hist_start_dt <> old_references.hist_start_dt)) THEN
127 IF IGS_RE_VAL_CAH.resp_val_cah_end_dt (
128 new_references.person_id,
129 new_references.ca_sequence_number,
130 new_references.hist_start_dt,
131 new_references.hist_end_dt,
132 v_message_name) = FALSE THEN
133 Fnd_Message.Set_Name ('IGS', v_message_name);
134 IGS_GE_MSG_STACK.ADD;
135 App_Exception.Raise_Exception;
136 END IF;
137 END IF;
138 END IF;
139 -- Save rowid to validate all IGS_RE_CANDIDATURE attendance histories are continuous
140 -- Do not validate at database level if being updated via form RESF3211
141 IF igs_as_val_suaap.genp_val_sdtt_sess('RESP_VAL_CAH_HIST_DT') THEN
142 IF p_inserting OR
143 p_updating THEN
144 v_person_id := new_references.person_id;
145 v_ca_sequence_number := new_references.ca_sequence_number;
146 ELSE
147 v_person_id := old_references.person_id;
148 v_ca_sequence_number := old_references.ca_sequence_number;
149 END IF;
150
151 IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
152 IF IGS_RE_VAL_CAH.resp_val_cah_hist_dt(
153 v_person_id,
154 v_ca_sequence_number,
155 'Y', -- validate first history start date
156 v_message_name) = FALSE THEN
157 Fnd_Message.Set_Name ('IGS', v_message_name);
158 IGS_GE_MSG_STACK.ADD;
159 App_Exception.Raise_Exception;
160 END IF;
161 END IF;
162 END IF;
163 END IF;
164 END BeforeRowInsertUpdateDelete1;
165
166 PROCEDURE Check_Constraints (
167 Column_Name in VARCHAR2 DEFAULT NULL ,
168 Column_Value in VARCHAR2 DEFAULT NULL
169 ) AS
170 BEGIN
171 IF Column_Name is null then
172 NULL;
173 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' THEN
174 new_references.ATTENDANCE_TYPE := COLUMN_VALUE ;
175 ELSIF upper(Column_name) = 'CA_SEQUENCE_NUMBER' THEN
176 new_references.CA_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
177 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' THEN
178 new_references.SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
179 ELSIF upper(Column_name) = 'ATTENDANCE_PERCENTAGE' THEN
180 new_references.ATTENDANCE_PERCENTAGE := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
181 END IF;
182
183 IF upper(column_name) = 'ATTENDANCE_TYPE' OR COLUMN_NAME IS NULL THEN
184 IF new_references.ATTENDANCE_TYPE <> upper(new_references.ATTENDANCE_TYPE) then
185 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
186 IGS_GE_MSG_STACK.ADD;
187 App_Exception.Raise_Exception ;
188 END IF;
189 END IF;
190
191 IF upper(column_name) = 'CA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
192 IF new_references.CA_SEQUENCE_NUMBER < 1 OR new_references.CA_SEQUENCE_NUMBER > 999999 then
193 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception ;
196 END IF;
197 END IF;
198
199 IF upper(column_name) = 'SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
200 IF new_references.SEQUENCE_NUMBER < 1 OR new_references.SEQUENCE_NUMBER > 999999 then
201 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
202 IGS_GE_MSG_STACK.ADD;
203 App_Exception.Raise_Exception ;
204 END IF;
205 END IF;
206
207 IF upper(column_name) = 'ATTENDANCE_PERCENTAGE' OR COLUMN_NAME IS NULL THEN
208 IF new_references.ATTENDANCE_PERCENTAGE < 1 OR new_references.ATTENDANCE_PERCENTAGE > 100 then
209 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
210 IGS_GE_MSG_STACK.ADD;
211 App_Exception.Raise_Exception ;
212 END IF;
213 END IF;
214
215 END Check_Constraints ;
216
217
218
219 PROCEDURE Check_Parent_Existance AS
220 BEGIN
221
222 IF (((old_references.attendance_type = new_references.attendance_type)) OR
223 ((new_references.attendance_type IS NULL))) THEN
224 NULL;
225 ELSE
226 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
227 new_references.attendance_type
228 ) THEN
229 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
230 IGS_GE_MSG_STACK.ADD;
231 App_Exception.Raise_Exception;
232 END IF;
233 END IF;
234
235 IF (((old_references.person_id = new_references.person_id) AND
236 (old_references.ca_sequence_number = new_references.ca_sequence_number)) OR
237 ((new_references.person_id IS NULL) OR
238 (new_references.ca_sequence_number IS NULL))) THEN
239 NULL;
240 ELSE
241 IF NOT IGS_RE_CANDIDATURE_PKG.Get_PK_For_Validation (
242 new_references.person_id,
243 new_references.ca_sequence_number
244 ) THEN
245 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
246 IGS_GE_MSG_STACK.ADD;
247 App_Exception.Raise_Exception;
248 END IF;
249 END IF;
250
251 END Check_Parent_Existance;
252
253 FUNCTION Get_PK_For_Validation (
254 x_person_id IN NUMBER,
255 x_ca_sequence_number IN NUMBER,
256 x_sequence_number IN NUMBER
257 )
258 RETURN BOOLEAN
259 AS
260 CURSOR cur_rowid IS
261 SELECT rowid
262 FROM IGS_RE_CDT_ATT_HIST_ALL
263 WHERE person_id = x_person_id
264 AND ca_sequence_number = x_ca_sequence_number
265 AND sequence_number = x_sequence_number
266 FOR UPDATE NOWAIT;
267
268 lv_rowid cur_rowid%RowType;
269
270 BEGIN
271
272 Open cur_rowid;
273 Fetch cur_rowid INTO lv_rowid;
274 IF (cur_rowid%FOUND) THEN
275 Close cur_rowid;
276 RETURN(TRUE);
277 ELSE
278 Close cur_rowid;
279 RETURN(FALSE);
280 END IF;
281 END Get_PK_For_Validation;
282
283 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
284 x_attendance_type IN VARCHAR2
285 ) AS
286
287 CURSOR cur_rowid IS
288 SELECT rowid
289 FROM IGS_RE_CDT_ATT_HIST_ALL
290 WHERE attendance_type = x_attendance_type ;
291
292 lv_rowid cur_rowid%RowType;
293
294 BEGIN
295
296 Open cur_rowid;
297 Fetch cur_rowid INTO lv_rowid;
298 IF (cur_rowid%FOUND) THEN
299 Close cur_rowid;
300 Fnd_Message.Set_Name ('IGS', 'IGS_RE_CAH_ATT_FK');
301 IGS_GE_MSG_STACK.ADD;
302 App_Exception.Raise_Exception;
303 Return;
304 END IF;
305 Close cur_rowid;
306
307 END GET_FK_IGS_EN_ATD_TYPE;
308
309 PROCEDURE GET_FK_IGS_RE_CANDIDATURE (
310 x_person_id IN NUMBER,
311 x_sequence_number IN NUMBER
312 ) AS
313
314 CURSOR cur_rowid IS
315 SELECT rowid
316 FROM IGS_RE_CDT_ATT_HIST_ALL
317 WHERE person_id = x_person_id
318 AND ca_sequence_number = x_sequence_number ;
319
320 lv_rowid cur_rowid%RowType;
321
322 BEGIN
323
324 Open cur_rowid;
325 Fetch cur_rowid INTO lv_rowid;
326 IF (cur_rowid%FOUND) THEN
327 Close cur_rowid;
328 Fnd_Message.Set_Name ('IGS', 'IGS_RE_CAH_CA_FK');
329 IGS_GE_MSG_STACK.ADD;
330 App_Exception.Raise_Exception;
331 Return;
332 END IF;
333 Close cur_rowid;
334
335 END GET_FK_IGS_RE_CANDIDATURE;
336
337 PROCEDURE Before_DML (
338 p_action IN VARCHAR2,
339 x_rowid IN VARCHAR2 DEFAULT NULL,
340 x_person_id IN NUMBER DEFAULT NULL,
341 x_ca_sequence_number IN NUMBER DEFAULT NULL,
342 x_sequence_number IN NUMBER DEFAULT NULL,
343 x_hist_start_dt IN DATE DEFAULT NULL,
344 x_hist_end_dt IN DATE DEFAULT NULL,
345 x_attendance_type IN VARCHAR2 DEFAULT NULL,
346 x_attendance_percentage IN NUMBER DEFAULT NULL,
347 x_creation_date IN DATE DEFAULT NULL,
348 x_created_by IN NUMBER DEFAULT NULL,
349 x_last_update_date IN DATE DEFAULT NULL,
350 x_last_updated_by IN NUMBER DEFAULT NULL,
351 x_last_update_login IN NUMBER DEFAULT NULL,
352 x_org_id IN NUMBER DEFAULT NULL
353 ) AS
354 BEGIN
355
356 Set_Column_Values (
357 p_action,
358 x_rowid,
359 x_person_id,
360 x_ca_sequence_number,
361 x_sequence_number,
362 x_hist_start_dt,
363 x_hist_end_dt,
364 x_attendance_type,
365 x_attendance_percentage,
366 x_creation_date,
367 x_created_by,
368 x_last_update_date,
369 x_last_updated_by,
370 x_last_update_login ,
371 x_org_id
372 );
373
374 IF (p_action = 'INSERT') THEN
375 -- Call all the procedures related to Before Insert.
376 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
377 IF Get_PK_For_Validation (
378 new_references.person_id,
379 new_references.ca_sequence_number,
380 new_references.sequence_number
381 ) THEN
382
383 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
384 IGS_GE_MSG_STACK.ADD;
385 App_Exception.Raise_Exception;
386 END IF;
387 Check_Constraints;
388 Check_Parent_Existance;
389 ELSIF (p_action = 'UPDATE') THEN
390 -- Call all the procedures related to Before Update.
391 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
392 Check_Constraints;
393 Check_Parent_Existance;
394 ELSIF (p_action = 'DELETE') THEN
395 -- Call all the procedures related to Before Delete.
396 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
397
398 ELSIF (p_action = 'VALIDATE_INSERT') THEN
399 IF Get_PK_For_Validation (
400 new_references.person_id,
401 new_references.ca_sequence_number,
402 new_references.sequence_number
403 ) THEN
404
405 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
406 IGS_GE_MSG_STACK.ADD;
407 App_Exception.Raise_Exception;
408 END IF;
409 Check_Constraints;
410 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
411 Check_Constraints;
412 END IF;
413 END Before_DML;
414
415 procedure INSERT_ROW (
416 X_ROWID in out NOCOPY VARCHAR2,
417 X_PERSON_ID in NUMBER,
418 X_CA_SEQUENCE_NUMBER in NUMBER,
419 X_SEQUENCE_NUMBER in NUMBER,
420 X_HIST_START_DT in DATE,
421 X_HIST_END_DT in DATE,
422 X_ATTENDANCE_TYPE in VARCHAR2,
423 X_ATTENDANCE_PERCENTAGE in NUMBER,
424 X_MODE in VARCHAR2 default 'R',
425 X_ORG_ID in NUMBER
426 ) as
427 cursor C is select ROWID from IGS_RE_CDT_ATT_HIST_ALL
428 where PERSON_ID = X_PERSON_ID
429 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
430 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
431 X_LAST_UPDATE_DATE DATE;
432 X_LAST_UPDATED_BY NUMBER;
433 X_LAST_UPDATE_LOGIN NUMBER;
434 begin
435 X_LAST_UPDATE_DATE := SYSDATE;
436 if(X_MODE = 'I') then
437 X_LAST_UPDATED_BY := 1;
438 X_LAST_UPDATE_LOGIN := 0;
439 elsif (X_MODE IN ('R', 'S')) then
440 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
441 if X_LAST_UPDATED_BY is NULL then
442 X_LAST_UPDATED_BY := -1;
443 end if;
444 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
445 if X_LAST_UPDATE_LOGIN is NULL then
446 X_LAST_UPDATE_LOGIN := -1;
447 end if;
448 else
449 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
450 IGS_GE_MSG_STACK.ADD;
451 app_exception.raise_exception;
452 end if;
453
454 Before_DML (
455 p_action => 'INSERT',
456 x_rowid => X_ROWID,
457 x_person_id => X_PERSON_ID,
458 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
459 x_sequence_number => X_SEQUENCE_NUMBER,
460 x_hist_start_dt => X_HIST_START_DT,
461 x_hist_end_dt => X_HIST_END_DT,
462 x_attendance_type => X_ATTENDANCE_TYPE,
463 x_attendance_percentage => X_ATTENDANCE_PERCENTAGE,
464 x_created_by => X_LAST_UPDATED_BY ,
465 x_creation_date => X_LAST_UPDATE_DATE,
466 x_last_updated_by => X_LAST_UPDATED_BY,
467 x_last_update_date => X_LAST_UPDATE_DATE,
468 x_last_update_login => X_LAST_UPDATE_LOGIN,
469 x_org_id => igs_ge_gen_003.get_org_id
470 );
471
472 IF (x_mode = 'S') THEN
473 igs_sc_gen_001.set_ctx('R');
474 END IF;
475 insert into IGS_RE_CDT_ATT_HIST_ALL (
476 PERSON_ID,
477 CA_SEQUENCE_NUMBER,
478 SEQUENCE_NUMBER,
479 HIST_START_DT,
480 HIST_END_DT,
481 ATTENDANCE_TYPE,
482 ATTENDANCE_PERCENTAGE,
483 CREATION_DATE,
484 CREATED_BY,
485 LAST_UPDATE_DATE,
486 LAST_UPDATED_BY,
487 LAST_UPDATE_LOGIN,
488 ORG_ID
489 ) values (
490 NEW_REFERENCES.PERSON_ID,
491 NEW_REFERENCES.CA_SEQUENCE_NUMBER,
492 NEW_REFERENCES.SEQUENCE_NUMBER,
493 NEW_REFERENCES.HIST_START_DT,
494 NEW_REFERENCES.HIST_END_DT,
495 NEW_REFERENCES.ATTENDANCE_TYPE,
496 NEW_REFERENCES.ATTENDANCE_PERCENTAGE,
497 X_LAST_UPDATE_DATE,
498 X_LAST_UPDATED_BY,
499 X_LAST_UPDATE_DATE,
500 X_LAST_UPDATED_BY,
501 X_LAST_UPDATE_LOGIN,
502 NEW_REFERENCES.ORG_ID
503 );
504 IF (x_mode = 'S') THEN
505 igs_sc_gen_001.unset_ctx('R');
506 END IF;
507
508
509 open c;
510 fetch c into X_ROWID;
511 if (c%notfound) then
512 close c;
513 raise no_data_found;
514 end if;
515 close c;
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
520 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
521 fnd_message.set_token ('ERR_CD', SQLCODE);
522 igs_ge_msg_stack.add;
523 igs_sc_gen_001.unset_ctx('R');
524 app_exception.raise_exception;
525 ELSE
526 igs_sc_gen_001.unset_ctx('R');
527 RAISE;
528 END IF;
529
530 end INSERT_ROW;
531
532 procedure LOCK_ROW (
533 X_ROWID in VARCHAR2,
534 X_PERSON_ID in NUMBER,
535 X_CA_SEQUENCE_NUMBER in NUMBER,
536 X_SEQUENCE_NUMBER in NUMBER,
537 X_HIST_START_DT in DATE,
538 X_HIST_END_DT in DATE,
539 X_ATTENDANCE_TYPE in VARCHAR2,
540 X_ATTENDANCE_PERCENTAGE in NUMBER
541 ) as
542 cursor c1 is select
543 HIST_START_DT,
544 HIST_END_DT,
545 ATTENDANCE_TYPE,
546 ATTENDANCE_PERCENTAGE
547 from IGS_RE_CDT_ATT_HIST_ALL
548 where ROWID = X_ROWID
549 for update nowait;
550 tlinfo c1%rowtype;
551
552 begin
553 open c1;
554 fetch c1 into tlinfo;
555 if (c1%notfound) then
556 close c1;
557 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
558 app_exception.raise_exception;
559 return;
560 end if;
561 close c1;
562
563 if ( (tlinfo.HIST_START_DT = X_HIST_START_DT)
564 AND (tlinfo.HIST_END_DT = X_HIST_END_DT)
565 AND (tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
566 AND (tlinfo.ATTENDANCE_PERCENTAGE = X_ATTENDANCE_PERCENTAGE)
567 ) then
568 null;
569 else
570 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
571 app_exception.raise_exception;
572 end if;
573 return;
574 end LOCK_ROW;
575
576 procedure UPDATE_ROW (
577 X_ROWID in VARCHAR2,
578 X_PERSON_ID in NUMBER,
579 X_CA_SEQUENCE_NUMBER in NUMBER,
580 X_SEQUENCE_NUMBER in NUMBER,
581 X_HIST_START_DT in DATE,
582 X_HIST_END_DT in DATE,
583 X_ATTENDANCE_TYPE in VARCHAR2,
584 X_ATTENDANCE_PERCENTAGE in NUMBER,
585 X_MODE in VARCHAR2 default 'R'
586 ) as
587 X_LAST_UPDATE_DATE DATE;
588 X_LAST_UPDATED_BY NUMBER;
589 X_LAST_UPDATE_LOGIN NUMBER;
590 begin
591 X_LAST_UPDATE_DATE := SYSDATE;
592 if(X_MODE = 'I') then
593 X_LAST_UPDATED_BY := 1;
594 X_LAST_UPDATE_LOGIN := 0;
595 elsif (X_MODE IN ('R', 'S')) then
596 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
597 if X_LAST_UPDATED_BY is NULL then
598 X_LAST_UPDATED_BY := -1;
599 end if;
600 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
601 if X_LAST_UPDATE_LOGIN is NULL then
602 X_LAST_UPDATE_LOGIN := -1;
603 end if;
604 else
605 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
606 IGS_GE_MSG_STACK.ADD;
607 app_exception.raise_exception;
608 end if;
609
610 Before_DML (
611 p_action => 'UPDATE',
612 x_rowid => X_ROWID,
613 x_person_id => X_PERSON_ID,
614 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
615 x_sequence_number => X_SEQUENCE_NUMBER,
616 x_hist_start_dt => X_HIST_START_DT,
617 x_hist_end_dt => X_HIST_END_DT,
618 x_attendance_type => X_ATTENDANCE_TYPE,
619 x_attendance_percentage => X_ATTENDANCE_PERCENTAGE,
620 x_created_by => X_LAST_UPDATED_BY ,
621 x_creation_date => X_LAST_UPDATE_DATE,
622 x_last_updated_by => X_LAST_UPDATED_BY,
623 x_last_update_date => X_LAST_UPDATE_DATE,
624 x_last_update_login => X_LAST_UPDATE_LOGIN
625 );
626
627 IF (x_mode = 'S') THEN
628 igs_sc_gen_001.set_ctx('R');
629 END IF;
630 update IGS_RE_CDT_ATT_HIST_ALL set
631 HIST_START_DT = NEW_REFERENCES.HIST_START_DT,
632 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
633 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
634 ATTENDANCE_PERCENTAGE = NEW_REFERENCES.ATTENDANCE_PERCENTAGE,
635 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
636 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
637 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
638 where ROWID = X_ROWID;
639
640 if (sql%notfound) then
641 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
642 igs_ge_msg_stack.add;
643 igs_sc_gen_001.unset_ctx('R');
644 app_exception.raise_exception;
645 end if;
646 IF (x_mode = 'S') THEN
647 igs_sc_gen_001.unset_ctx('R');
648 END IF;
649
650
651
652 EXCEPTION
653 WHEN OTHERS THEN
654 IF (SQLCODE = (-28115)) THEN
655 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
656 fnd_message.set_token ('ERR_CD', SQLCODE);
657 igs_ge_msg_stack.add;
658 igs_sc_gen_001.unset_ctx('R');
659 app_exception.raise_exception;
660 ELSE
661 igs_sc_gen_001.unset_ctx('R');
662 RAISE;
663 END IF;
664
665 end UPDATE_ROW;
666
667 procedure ADD_ROW (
668 X_ROWID in out NOCOPY VARCHAR2,
669 X_PERSON_ID in NUMBER,
670 X_CA_SEQUENCE_NUMBER in NUMBER,
671 X_SEQUENCE_NUMBER in NUMBER,
672 X_HIST_START_DT in DATE,
673 X_HIST_END_DT in DATE,
674 X_ATTENDANCE_TYPE in VARCHAR2,
675 X_ATTENDANCE_PERCENTAGE in NUMBER,
676 X_MODE in VARCHAR2 default 'R',
677 X_ORG_ID in NUMBER
678 ) as
679 cursor c1 is select rowid from IGS_RE_CDT_ATT_HIST_ALL
680 where PERSON_ID = X_PERSON_ID
681 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
682 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
683 ;
684 begin
685 open c1;
686 fetch c1 into X_ROWID;
687 if (c1%notfound) then
688 close c1;
689 INSERT_ROW (
690 X_ROWID,
691 X_PERSON_ID,
692 X_CA_SEQUENCE_NUMBER,
693 X_SEQUENCE_NUMBER,
694 X_HIST_START_DT,
695 X_HIST_END_DT,
696 X_ATTENDANCE_TYPE,
697 X_ATTENDANCE_PERCENTAGE,
698 X_MODE,
699 X_ORG_ID);
700 return;
701 end if;
702 close c1;
703 UPDATE_ROW (
704 X_ROWID,
705 X_PERSON_ID,
706 X_CA_SEQUENCE_NUMBER,
707 X_SEQUENCE_NUMBER,
708 X_HIST_START_DT,
709 X_HIST_END_DT,
710 X_ATTENDANCE_TYPE,
711 X_ATTENDANCE_PERCENTAGE,
712 X_MODE );
713 end ADD_ROW;
714
715 procedure DELETE_ROW (
716 X_ROWID in VARCHAR2,
717 x_mode IN VARCHAR2
718 ) as
719 begin
720 Before_DML (
721 p_action => 'DELETE',
722 x_rowid => X_ROWID
723 );
724
725 IF (x_mode = 'S') THEN
726 igs_sc_gen_001.set_ctx('R');
727 END IF;
728 delete from IGS_RE_CDT_ATT_HIST_ALL
729 where ROWID = X_ROWID;
730 if (sql%notfound) then
731 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
732 igs_ge_msg_stack.add;
733 igs_sc_gen_001.unset_ctx('R');
734 app_exception.raise_exception;
735 end if;
736 IF (x_mode = 'S') THEN
737 igs_sc_gen_001.unset_ctx('R');
738 END IF;
739
740
741 end DELETE_ROW;
742
743 end IGS_RE_CDT_ATT_HIST_PKG;