[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_ATD_MODE_PKG
Source
1 package body IGS_EN_ATD_MODE_PKG as
2 /* $Header: IGSEI13B.pls 115.8 2003/02/04 08:24:00 srdirisa ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_EN_ATD_MODE_ALL%RowType;
6 new_references IGS_EN_ATD_MODE_ALL%RowType;
7 PROCEDURE beforerowdelete;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_org_id IN NUMBER DEFAULT NULL,
13 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
14 x_description IN VARCHAR2 DEFAULT NULL,
15 x_govt_attendance_mode IN VARCHAR2 DEFAULT NULL,
16 x_closed_ind IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL
22 ) AS
23
24 CURSOR cur_old_ref_values IS
25 SELECT *
26 FROM IGS_EN_ATD_MODE_ALL
27 WHERE rowid = x_rowid;
28
29 BEGIN
30
31 l_rowid := x_rowid;
32
33 -- Code for setting the Old and New Reference Values.
34 -- Populate Old Values.
35 Open cur_old_ref_values;
36 Fetch cur_old_ref_values INTO old_references;
37 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
38 Close cur_old_ref_values;
39 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
40 IGS_GE_MSG_STACK.ADD;
41 App_Exception.Raise_Exception;
42 Return;
43 END IF;
44 Close cur_old_ref_values;
45
46 -- Populate New Values.
47 new_references.org_id := x_org_id;
48 new_references.attendance_mode := x_attendance_mode;
49 new_references.description := x_description;
50 new_references.govt_attendance_mode := x_govt_attendance_mode;
51 new_references.closed_ind := x_closed_ind;
52 IF (p_action = 'UPDATE') THEN
53 new_references.creation_date := old_references.creation_date;
54 new_references.created_by := old_references.created_by;
55 ELSE
56 new_references.creation_date := x_creation_date;
57 new_references.created_by := x_created_by;
58 END IF;
59 new_references.last_update_date := x_last_update_date;
60 new_references.last_updated_by := x_last_updated_by;
61 new_references.last_update_login := x_last_update_login;
62
63 END Set_Column_Values;
64
65
66 -- Trigger description :-
67 -- "OSS_TST".trg_am_br_iu
68 -- BEFORE INSERT OR UPDATE
69 -- ON attendance_mode
70 -- FOR EACH ROW
71
72 PROCEDURE BeforeRowInsertUpdate1(
73 p_inserting IN BOOLEAN DEFAULT FALSE,
74 p_updating IN BOOLEAN DEFAULT FALSE,
75 p_deleting IN BOOLEAN DEFAULT FALSE
76 ) AS
77
78 v_message_name varchar2(30);
79 BEGIN
80
81 -- Validate Govt attendance mode.
82 IF p_inserting OR
83 (p_updating AND
84 (old_references.govt_attendance_mode <> new_references.govt_attendance_mode OR
85 old_references.closed_ind <> new_references.closed_ind)) THEN
86 IF IGS_PS_VAL_AM.crsp_val_am_govt(
87 new_references.govt_attendance_mode,
88 v_message_name) = FALSE THEN
89 Fnd_Message.Set_Name('IGS', v_message_name);
90 IGS_GE_MSG_STACK.ADD;
91 App_Exception.Raise_Exception;
92 END IF;
93 END IF;
94
95
96 END BeforeRowInsertUpdate1;
97
98 procedure Check_constraints(
99 column_name IN VARCHAR2 DEFAULT NULL,
100 column_value IN VARCHAR2 DEFAULT NULL
101 ) AS
102 begin
103 IF column_name is null then
104 NULL;
105 ELSIF upper(column_name) = 'CLOSED_IND' THEN
106 new_references.closed_ind := column_value;
107 ELSIF upper(column_name) = 'ATTENDANCE_MODE' THEN
108 new_references.attendance_mode := column_value;
109 ELSIF upper(column_name) = 'GOVT_ATTENDANCE_MODE' THEN
110 new_references.govt_attendance_mode := column_value;
111 END IF;
112
113 IF upper(column_name) = 'CLOSED_IND' OR
114 Column_name is null THEN
115 IF new_references.closed_ind NOT IN ('Y','N') THEN
116 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
117 IGS_GE_MSG_STACK.ADD;
118 App_Exception.Raise_Exception;
119 END IF;
120 END IF;
121
122 IF upper(column_name) = 'ATTENDANCE_MODE' OR
123 Column_name is null THEN
124 IF new_references.attendance_mode <> UPPER(new_references.attendance_mode) THEN
125 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
126 IGS_GE_MSG_STACK.ADD;
127 App_Exception.Raise_Exception;
128 END IF;
129 END IF;
130
131 IF upper(column_name) = 'GOVT_ATTENDANCE_MODE' OR
132 Column_name is null THEN
133 IF new_references.govt_attendance_mode <> UPPER(new_references.govt_attendance_mode) THEN
134 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
135 IGS_GE_MSG_STACK.ADD;
136 App_Exception.Raise_Exception;
137 END IF;
138 END IF;
139
140 END check_constraints;
141
142
143 PROCEDURE Check_Parent_Existance AS
144 BEGIN
145
146 IF (((old_references.govt_attendance_mode = new_references.govt_attendance_mode)) OR
147 ((new_references.govt_attendance_mode IS NULL))) THEN
148 NULL;
149 ELSE
150 IF NOT IGS_PS_GOVT_ATD_MODE_PKG.Get_PK_For_Validation (
151 new_references.govt_attendance_mode
152 ) THEN
153 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END IF;
157 END IF;
158
159 END Check_Parent_Existance;
160
161 PROCEDURE Check_Child_Existance AS
162 BEGIN
163
164 IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_EN_ATD_MODE (
165 old_references.attendance_mode
166 );
167
168 IGS_AD_PRD_PS_OF_OPT_PKG.GET_FK_IGS_EN_ATD_MODE (
169 old_references.attendance_mode
170 );
171
172 IGS_AD_PECRS_OFOP_DT_PKG.GET_FK_IGS_EN_ATD_MODE (
173 old_references.attendance_mode
174 );
175
176 IGS_FI_FEE_AS_RT_PKG.GET_FK_IGS_EN_ATD_MODE (
177 old_references.attendance_mode
178 );
179
180 IGS_PS_FEE_TRG_PKG.GET_FK_IGS_EN_ATD_MODE (
181 old_references.attendance_mode
182 );
183
184 IGS_PS_OFR_OPT_PKG.GET_FK_IGS_EN_ATD_MODE (
185 old_references.attendance_mode
186 );
187
188
189 IGS_FI_FEE_AS_RATE_PKG.GET_FK_IGS_EN_ATD_MODE (
190 old_references.attendance_mode
191 );
192
193 IGS_PS_PAT_OF_STUDY_PKG.GET_FK_IGS_EN_ATD_MODE (
194 old_references.attendance_mode
195 );
196
197 IGS_AD_SBMAO_FN_AMTT_PKG.GET_FK_IGS_EN_ATD_MODE (
198 old_references.attendance_mode
199 );
200
201 IGS_AD_SBM_PS_FNTRGT_PKG.GET_FK_IGS_EN_ATD_MODE (
202 old_references.attendance_mode
203 );
204
205 IGS_EN_STDNT_PS_ATT_PKG.GET_FK_IGS_EN_ATD_MODE (
206 old_references.attendance_mode
207 );
208
209 END Check_Child_Existance;
210
211 FUNCTION Get_PK_For_Validation (
212 x_attendance_mode IN VARCHAR2
213 ) RETURN BOOLEAN AS
214
215 CURSOR cur_rowid IS
216 SELECT rowid
217 FROM IGS_EN_ATD_MODE_ALL
218 WHERE attendance_mode = x_attendance_mode;
219
220 lv_rowid cur_rowid%RowType;
221
222 BEGIN
223
224 Open cur_rowid;
225 Fetch cur_rowid INTO lv_rowid;
226 IF (cur_rowid%FOUND) THEN
227 Close cur_rowid;
228 return(TRUE);
229 else
230 Close cur_rowid;
231 Return(FALSE);
232 END IF;
233
234 END Get_PK_For_Validation;
235
236 PROCEDURE GET_FK_IGS_PS_GOVT_ATD_MODE (
237 x_govt_attendance_mode IN VARCHAR2
238 ) AS
239
240 CURSOR cur_rowid IS
241 SELECT rowid
242 FROM IGS_EN_ATD_MODE_ALL
243 WHERE govt_attendance_mode = x_govt_attendance_mode ;
244
245 lv_rowid cur_rowid%RowType;
246
247 BEGIN
248
249 Open cur_rowid;
250 Fetch cur_rowid INTO lv_rowid;
251 IF (cur_rowid%FOUND) THEN
252 Close cur_rowid;
253 Fnd_Message.Set_Name ('IGS', 'IGS_EN_AM_GAM_FK');
254 IGS_GE_MSG_STACK.ADD;
255 App_Exception.Raise_Exception;
256 Return;
257 END IF;
258 Close cur_rowid;
259
260 END GET_FK_IGS_PS_GOVT_ATD_MODE;
261
262 PROCEDURE Before_DML (
263 p_action IN VARCHAR2,
264 x_rowid IN VARCHAR2 DEFAULT NULL,
265 x_org_id IN NUMBER DEFAULT NULL,
266 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
267 x_description IN VARCHAR2 DEFAULT NULL,
268 x_govt_attendance_mode IN VARCHAR2 DEFAULT NULL,
269 x_closed_ind IN VARCHAR2 DEFAULT NULL,
270 x_creation_date IN DATE DEFAULT NULL,
271 x_created_by IN NUMBER DEFAULT NULL,
272 x_last_update_date IN DATE DEFAULT NULL,
273 x_last_updated_by IN NUMBER DEFAULT NULL,
274 x_last_update_login IN NUMBER DEFAULT NULL
275 ) AS
276 BEGIN
277
278 Set_Column_Values (
279 p_action,
280 x_rowid,
281 x_org_id,
282 x_attendance_mode,
283 x_description,
284 x_govt_attendance_mode,
285 x_closed_ind,
286 x_creation_date,
287 x_created_by,
288 x_last_update_date,
289 x_last_updated_by,
290 x_last_update_login
291 );
292
293 IF (p_action = 'INSERT') THEN
294 -- Call all the procedures related to Before Insert.
295 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
296 IF Get_PK_For_Validation (
297 new_references.attendance_mode
298 ) Then
299 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
300 IGS_GE_MSG_STACK.ADD;
301 App_Exception.Raise_Exception;
302 END IF;
303 Check_constraints;
304 Check_Parent_Existance;
305 ELSIF (p_action = 'UPDATE') THEN
306 -- Call all the procedures related to Before Update.
307 BeforeRowInsertUpdate1 ( p_updating => TRUE );
308 Check_constraints;
309 Check_Parent_Existance;
310 ELSIF (p_action = 'DELETE') THEN
311 -- Call all the procedures related to Before Delete.
312 beforerowdelete;
313 Check_Child_Existance;
314 ELSIF (p_action = 'VALIDATE_INSERT') then
315 IF Get_PK_For_Validation (
316 new_references.attendance_mode
317 ) Then
318 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321 END IF;
322 Check_constraints;
323 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
324 Check_constraints;
325 ELSIF (p_action = 'VALIDATE_DELETE') THEN
326 Check_Child_Existance;
327 END IF;
328
329 END Before_DML;
330
331 PROCEDURE After_DML (
332 p_action IN VARCHAR2,
333 x_rowid IN VARCHAR2
334 ) AS
335 BEGIN
336
337 l_rowid := x_rowid;
338
339 IF (p_action = 'INSERT') THEN
340 -- Call all the procedures related to After Insert.
341 Null;
342 ELSIF (p_action = 'UPDATE') THEN
343 -- Call all the procedures related to After Update.
344 Null;
345 ELSIF (p_action = 'DELETE') THEN
346 -- Call all the procedures related to After Delete.
347 Null;
348 END IF;
349
350 END After_DML;
351
352
353 procedure INSERT_ROW (
354 X_ROWID in out NOCOPY VARCHAR2,
355 X_ORG_ID in NUMBER,
356 X_ATTENDANCE_MODE in VARCHAR2,
357 X_DESCRIPTION in VARCHAR2,
358 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
359 X_CLOSED_IND in VARCHAR2,
360 X_MODE in VARCHAR2 default 'R'
361 ) AS
362 cursor C is select ROWID from IGS_EN_ATD_MODE_ALL
363 where ATTENDANCE_MODE = X_ATTENDANCE_MODE;
364 X_LAST_UPDATE_DATE DATE;
365 X_LAST_UPDATED_BY NUMBER;
366 X_LAST_UPDATE_LOGIN NUMBER;
367 begin
368 X_LAST_UPDATE_DATE := SYSDATE;
369 if(X_MODE = 'I') then
370 X_LAST_UPDATED_BY := 1;
371 X_LAST_UPDATE_LOGIN := 0;
372 elsif (X_MODE = 'R') then
373 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
374 if X_LAST_UPDATED_BY is NULL then
375 X_LAST_UPDATED_BY := -1;
376 end if;
377 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
378 if X_LAST_UPDATE_LOGIN is NULL then
379 X_LAST_UPDATE_LOGIN := -1;
380 end if;
381 else
382 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
383 IGS_GE_MSG_STACK.ADD;
384 app_exception.raise_exception;
385 end if;
386
387 Before_DML(
388 p_action => 'INSERT',
389 x_rowid => X_ROWID,
390 x_org_id => igs_ge_gen_003.get_org_id,
391 x_attendance_mode => X_ATTENDANCE_MODE,
392 x_description => X_DESCRIPTION,
393 x_govt_attendance_mode => X_GOVT_ATTENDANCE_MODE,
394 x_closed_ind => X_CLOSED_IND,
395 x_creation_date => X_LAST_UPDATE_DATE,
396 x_created_by => X_LAST_UPDATED_BY,
397 x_last_update_date =>X_LAST_UPDATE_DATE,
398 x_last_updated_by => X_LAST_UPDATED_BY,
399 x_last_update_login => X_LAST_UPDATE_LOGIN
400 );
401
402 insert into IGS_EN_ATD_MODE_ALL (
403 org_id,
404 ATTENDANCE_MODE,
405 DESCRIPTION,
406 GOVT_ATTENDANCE_MODE,
407 CLOSED_IND,
408 CREATION_DATE,
409 CREATED_BY,
410 LAST_UPDATE_DATE,
411 LAST_UPDATED_BY,
412 LAST_UPDATE_LOGIN
413 ) values (
414 NEW_REFERENCES.ORG_ID,
415 NEW_REFERENCES.ATTENDANCE_MODE,
416 NEW_REFERENCES.DESCRIPTION,
417 NEW_REFERENCES.GOVT_ATTENDANCE_MODE,
418 NEW_REFERENCES.CLOSED_IND,
419 X_LAST_UPDATE_DATE,
420 X_LAST_UPDATED_BY,
421 X_LAST_UPDATE_DATE,
422 X_LAST_UPDATED_BY,
423 X_LAST_UPDATE_LOGIN
424 );
425
426
427
428 open c;
429 fetch c into X_ROWID;
430 if (c%notfound) then
431 close c;
432 raise no_data_found;
433 end if;
434 close c;
435
436 After_DML(
437 p_action => 'INSERT',
438 x_rowid => X_ROWID
439 );
440
441 end INSERT_ROW;
442
443 procedure LOCK_ROW (
444 X_ROWID in VARCHAR2,
445 X_ATTENDANCE_MODE in VARCHAR2,
446 X_DESCRIPTION in VARCHAR2,
447 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
448 X_CLOSED_IND in VARCHAR2
449 ) AS
450 cursor c1 is select
451 DESCRIPTION,
452 GOVT_ATTENDANCE_MODE,
453 CLOSED_IND
454 from IGS_EN_ATD_MODE_ALL
455 where ROWID = X_ROWID
456 for update nowait;
457 tlinfo c1%rowtype;
458
459 begin
460 open c1;
461 fetch c1 into tlinfo;
462 if (c1%notfound) then
463 close c1;
464 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
465 IGS_GE_MSG_STACK.ADD;
466 app_exception.raise_exception;
467 return;
468 end if;
469 close c1;
470
471 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
472 AND (tlinfo.GOVT_ATTENDANCE_MODE = X_GOVT_ATTENDANCE_MODE)
473 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
474 ) then
475 null;
476 else
477 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
478 IGS_GE_MSG_STACK.ADD;
479 app_exception.raise_exception;
480 end if;
481 return;
482 end LOCK_ROW;
483
484 procedure UPDATE_ROW (
485 X_ROWID in VARCHAR2,
486 X_ATTENDANCE_MODE in VARCHAR2,
487 X_DESCRIPTION in VARCHAR2,
488 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
489 X_CLOSED_IND in VARCHAR2,
490 X_MODE in VARCHAR2 default 'R'
491 ) AS
492 X_LAST_UPDATE_DATE DATE;
493 X_LAST_UPDATED_BY NUMBER;
494 X_LAST_UPDATE_LOGIN NUMBER;
495 begin
496 X_LAST_UPDATE_DATE := SYSDATE;
497 if(X_MODE = 'I') then
498 X_LAST_UPDATED_BY := 1;
499 X_LAST_UPDATE_LOGIN := 0;
500 elsif (X_MODE = 'R') then
501 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
502 if X_LAST_UPDATED_BY is NULL then
503 X_LAST_UPDATED_BY := -1;
504 end if;
505 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
506 if X_LAST_UPDATE_LOGIN is NULL then
507 X_LAST_UPDATE_LOGIN := -1;
508 end if;
509 else
510 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
511 IGS_GE_MSG_STACK.ADD;
512 app_exception.raise_exception;
513 end if;
514
515 Before_DML(
516 p_action => 'UPDATE',
517 x_rowid => X_ROWID,
518 x_attendance_mode => X_ATTENDANCE_MODE,
519 x_description => X_DESCRIPTION,
520 x_govt_attendance_mode => X_GOVT_ATTENDANCE_MODE,
521 x_closed_ind => X_CLOSED_IND,
522 x_creation_date => X_LAST_UPDATE_DATE,
523 x_created_by => X_LAST_UPDATED_BY,
524 x_last_update_date =>X_LAST_UPDATE_DATE,
525 x_last_updated_by => X_LAST_UPDATED_BY,
526 x_last_update_login => X_LAST_UPDATE_LOGIN
527 );
528
529 update IGS_EN_ATD_MODE_ALL set
530 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
531 GOVT_ATTENDANCE_MODE = NEW_REFERENCES.GOVT_ATTENDANCE_MODE,
532 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
533 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
534 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
535 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
536 where ROWID = X_ROWID
537 ;
538 if (sql%notfound) then
539 raise no_data_found;
540 end if;
541
542 After_DML(
543 p_action => 'UPDATE',
544 x_rowid => X_ROWID
545 );
546
547
548 end UPDATE_ROW;
549
550 procedure ADD_ROW (
551 X_ROWID in out NOCOPY VARCHAR2,
552 X_ORG_ID in NUMBER,
553 X_ATTENDANCE_MODE in VARCHAR2,
554 X_DESCRIPTION in VARCHAR2,
555 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
556 X_CLOSED_IND in VARCHAR2,
557 X_MODE in VARCHAR2 default 'R'
558 ) AS
559 cursor c1 is select rowid from IGS_EN_ATD_MODE_ALL
560 where ATTENDANCE_MODE = X_ATTENDANCE_MODE
561 ;
562
563 begin
564 open c1;
565 fetch c1 into X_ROWID;
566 if (c1%notfound) then
567 close c1;
568 INSERT_ROW (
569 X_ROWID,
570 x_org_id,
571 X_ATTENDANCE_MODE,
572 X_DESCRIPTION,
573 X_GOVT_ATTENDANCE_MODE,
574 X_CLOSED_IND,
575 X_MODE);
576 return;
577 end if;
578 close c1;
579 UPDATE_ROW (
580 X_ROWID,
581 X_ATTENDANCE_MODE,
582 X_DESCRIPTION,
583 X_GOVT_ATTENDANCE_MODE,
584 X_CLOSED_IND,
585 X_MODE);
586 end ADD_ROW;
587
588 procedure DELETE_ROW (
589 X_ROWID in VARCHAR2
590 ) AS
591 begin
592
593 Before_DML(
594 p_action => 'DELETE',
595 x_rowid => X_ROWID
596 );
597
598
599 delete from IGS_EN_ATD_MODE_ALL
600 where ROWID = X_ROWID;
601 if (sql%notfound) then
602 raise no_data_found;
603 end if;
604
605 After_DML(
606 p_action => 'DELETE',
607 x_rowid => X_ROWID
608 );
609
610 end DELETE_ROW;
611
612 PROCEDURE beforerowdelete AS
613 ------------------------------------------------------------------
614 --Created by : smvk, Oracle India
615 --Date created: 03-Jan-2003
616 --
617 --Purpose: Attendance Mode records can be deleted logically by setting the closed_ind as 'Y'
618 -- No physical deletion is allowed. As a part of Bug # 2729917
619 --
620 --
621 --Known limitations/enhancements and/or remarks:
622 --
623 --Change History:
624 --Who When What
625 -------------------------------------------------------------------
626 BEGIN
627 -- Preventing deletion of the Attendance Mode records. As a part of Bug # 2729917
628 FND_MESSAGE.SET_NAME('IGS','IGS_FI_DEL_NOT_ALLWD');
629 IGS_GE_MSG_STACK.ADD;
630 APP_EXCEPTION.RAISE_EXCEPTION;
631 END beforerowdelete;
632
633 end IGS_EN_ATD_MODE_PKG;