[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_ATD_TYPE_PKG
Source
1 package body IGS_EN_ATD_TYPE_PKG as
2 /* $Header: IGSEI14B.pls 115.11 2003/02/04 08:24:39 srdirisa ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_EN_ATD_TYPE_ALL%RowType;
6 new_references IGS_EN_ATD_TYPE_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_type IN VARCHAR2 DEFAULT NULL,
14 x_description IN VARCHAR2 DEFAULT NULL,
15 x_govt_attendance_type IN VARCHAR2 DEFAULT NULL,
16 x_lower_enr_load_range IN NUMBER DEFAULT NULL,
17 x_upper_enr_load_range IN NUMBER DEFAULT NULL,
18 x_research_percentage IN NUMBER DEFAULT NULL,
19 x_closed_ind IN VARCHAR2 DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM IGS_EN_ATD_TYPE_ALL
30 WHERE rowid = x_rowid;
31
32 BEGIN
33
34 l_rowid := x_rowid;
35
36 -- Code for setting the Old and New Reference Values.
37 -- Populate Old Values.
38 Open cur_old_ref_values;
39 Fetch cur_old_ref_values INTO old_references;
40 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
41 Close cur_old_ref_values;
42 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
43 IGS_GE_MSG_STACK.ADD;
44 App_Exception.Raise_Exception;
45 Return;
46 END IF;
47 Close cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.org_id := x_org_id;
51 new_references.attendance_type := x_attendance_type;
52 new_references.description := x_description;
53 new_references.govt_attendance_type := x_govt_attendance_type;
54 new_references.lower_enr_load_range := x_lower_enr_load_range;
55 new_references.upper_enr_load_range := x_upper_enr_load_range;
56 new_references.research_percentage := x_research_percentage;
57 new_references.closed_ind := x_closed_ind;
58 IF (p_action = 'UPDATE') THEN
59 new_references.creation_date := old_references.creation_date;
60 new_references.created_by := old_references.created_by;
61 ELSE
62 new_references.creation_date := x_creation_date;
63 new_references.created_by := x_created_by;
64 END IF;
65 new_references.last_update_date := x_last_update_date;
66 new_references.last_updated_by := x_last_updated_by;
67 new_references.last_update_login := x_last_update_login;
68
69 END Set_Column_Values;
70 -- Trigger description :-
71 -- "OSS_TST".trg_att_br_iu
72 -- BEFORE INSERT OR UPDATE
73 -- ON IGS_EN_ATD_TYPE
74 -- FOR EACH ROW
75
76 PROCEDURE BeforeRowInsertUpdate1(
77 p_inserting IN BOOLEAN DEFAULT FALSE,
78 p_updating IN BOOLEAN DEFAULT FALSE,
79 p_deleting IN BOOLEAN DEFAULT FALSE
80 ) AS
81 v_message_name varchar2(30);
82 BEGIN
83
84 -- Validate Govt attendance type.
85 IF p_inserting OR
86 (p_updating AND
87 (old_references.govt_attendance_type <> new_references.govt_attendance_type OR
88 old_references.closed_ind <> new_references.closed_ind)) THEN
89 IF IGS_PS_VAL_ATT.crsp_val_att_govt(
90 new_references.govt_attendance_type,
91 v_message_name) = FALSE THEN
92 Fnd_Message.Set_Name('IGS', v_message_name);
93 IGS_GE_MSG_STACK.ADD;
94 App_Exception.Raise_Exception;
95 END IF;
96 END IF;
97
98
99 END BeforeRowInsertUpdate1;
100
101 procedure Check_constraints(
102 column_name IN VARCHAR2 DEFAULT NULL,
103 column_value IN VARCHAR2 DEFAULT NULL
104 ) AS
105 begin
106 IF column_name is null then
107 NULL;
108 ELSIF upper(column_name) = 'LOWER_ENR_LOAD_RANGE' THEN
109 new_references.lower_enr_load_range := IGS_GE_NUMBER.TO_NUM(column_value);
110 ELSIF upper(column_name) = 'RESEARCH_PERCENTAGE' THEN
111 new_references.research_percentage := IGS_GE_NUMBER.TO_NUM(column_value);
112 ELSIF upper(column_name) = 'CLOSED_IND' THEN
113 new_references.closed_ind := column_value;
114 ELSIF upper(column_name) = 'UPPER_ENR_LOAD_RANGE' THEN
115 new_references.upper_enr_load_range := IGS_GE_NUMBER.TO_NUM(column_value);
116 ELSIF upper(column_name) = 'ATTENDANCE_TYPE' THEN
117 new_references.attendance_type := column_value;
118 ELSIF upper(column_name) = 'GOVT_ATTENDANCE_TYPE' THEN
119 new_references.govt_attendance_type := column_value;
120 END IF;
121
122 IF upper(column_name) = 'LOWER_ENR_LOAD_RANGE' OR
123 Column_name is null THEN
124 IF new_references.lower_enr_load_range < 0 OR
125 new_references.lower_enr_load_range > 9999.999THEN
126 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END IF;
130 END IF;
131
132 IF upper(column_name) = 'RESEARCH_PERCENTAGE' OR
133 Column_name is null THEN
134 IF new_references.research_percentage < 0 OR
135 new_references.research_percentage > 100 THEN
136 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 END IF;
140 END IF;
141
142 IF upper(column_name) = 'CLOSED_IND' OR
143 Column_name is null THEN
144 IF new_references.closed_ind NOT IN ('Y','N') 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) = 'UPPER_ENR_LOAD_RANGE' OR
152 Column_name is null THEN
153 IF new_references.upper_enr_load_range < 0 OR
154 new_references.upper_enr_load_range > 9999.999 THEN
155 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
156 IGS_GE_MSG_STACK.ADD;
157 App_Exception.Raise_Exception;
158 END IF;
159 END IF;
160
161 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
162 Column_name is null THEN
163 IF new_references.attendance_type <> UPPER(new_references.attendance_type) THEN
164 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169
170 IF upper(column_name) = 'GOVT_ATTENDANCE_TYPE' OR
171 Column_name is null THEN
172 IF new_references.govt_attendance_type <> UPPER(new_references.govt_attendance_type) THEN
173 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END IF;
177 END IF;
178
179 END check_constraints;
180
181 PROCEDURE Check_Parent_Existance AS
182 BEGIN
183
184 IF (((old_references.govt_attendance_type = new_references.govt_attendance_type)) OR
185 ((new_references.govt_attendance_type IS NULL))) THEN
186 NULL;
187 ELSE
188 IF NOT IGS_PS_GOVT_ATD_TYPE_PKG.Get_PK_For_Validation (
189 new_references.govt_attendance_type
190 ) THEN
191 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception;
194 END IF;
195 END IF;
196
197 END Check_Parent_Existance;
198
199 PROCEDURE Check_Child_Existance AS
200 BEGIN
201
202 IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_EN_ATD_TYPE (
203 old_references.attendance_type
204 );
205
206 IGS_AD_PRD_PS_OF_OPT_PKG.GET_FK_IGS_EN_ATD_TYPE (
207 old_references.attendance_type
208 );
209
210 IGS_AD_PECRS_OFOP_DT_PKG.GET_FK_IGS_EN_ATD_TYPE (
211 old_references.attendance_type
212 );
213
214 IGS_EN_ATD_TYPE_LOAD_PKG.GET_FK_IGS_EN_ATD_TYPE (
215 old_references.attendance_type
216 );
217
218 IGS_RE_CDT_ATT_HIST_PKG.GET_FK_IGS_EN_ATD_TYPE (
219 old_references.attendance_type
220 );
221
222 IGS_FI_FEE_AS_RT_PKG.GET_FK_IGS_EN_ATD_TYPE (
223 old_references.attendance_type
224 );
225
226 IGS_PS_FEE_TRG_PKG.GET_FK_IGS_EN_ATD_TYPE (
227 old_references.attendance_type
228 );
229
230 IGS_PS_OFR_OPT_PKG.GET_FK_IGS_EN_ATD_TYPE (
231 old_references.attendance_type
232 );
233
234 IGS_RE_DFLT_MS_SET_PKG.GET_FK_IGS_EN_ATD_TYPE (
235 old_references.attendance_type
236 );
237
238 IGS_FI_FEE_AS_RATE_PKG.GET_FK_IGS_EN_ATD_TYPE (
239 old_references.attendance_type
240 );
241
242 IGS_PS_PAT_OF_STUDY_PKG.GET_FK_IGS_EN_ATD_TYPE (
243 old_references.attendance_type
244 );
245
246 IGS_PE_PERSENC_EFFCT_PKG.GET_FK_IGS_EN_ATD_TYPE (
247 old_references.attendance_type
248 );
249
250 IGS_PR_OU_TYPE_PKG.GET_FK_IGS_EN_ATD_TYPE (
251 old_references.attendance_type
252 );
253
254 IGS_PR_RU_APPL_PKG.GET_FK_IGS_EN_ATD_TYPE (
255 old_references.attendance_type
256 );
257
258 IGS_PR_RU_OU_PKG.GET_FK_IGS_EN_ATD_TYPE (
259 old_references.attendance_type
260 );
261
262 IGS_AD_SBM_PS_FNTRGT_PKG.GET_FK_IGS_EN_ATD_TYPE (
263 old_references.attendance_type
264 );
265
266 IGS_EN_STDNT_PS_ATT_PKG.GET_FK_IGS_EN_ATD_TYPE (
267 old_references.attendance_type
268 );
269
270 IGS_PR_STDNT_PR_OU_PKG.GET_FK_IGS_EN_ATD_TYPE (
271 old_references.attendance_type
272 );
273
274 END Check_Child_Existance;
275
276 FUNCTION Get_PK_For_Validation (
277 x_attendance_type IN VARCHAR2
278 ) RETURN BOOLEAN AS
279
280 CURSOR cur_rowid IS
281 SELECT rowid
282 FROM IGS_EN_ATD_TYPE_ALL
283 WHERE attendance_type = x_attendance_type;
284
285 lv_rowid cur_rowid%RowType;
286
287 BEGIN
288
289 Open cur_rowid;
290 Fetch cur_rowid INTO lv_rowid;
291 IF (cur_rowid%FOUND) THEN
292 Close cur_rowid;
293 return(TRUE);
294 else
295 Close cur_rowid;
296 Return(FALSE);
297 END IF;
298
299 END Get_PK_For_Validation;
300
301 PROCEDURE GET_FK_IGS_PS_GOVT_ATD_TYPE (
302 x_govt_attendance_type IN VARCHAR2
303 ) AS
304
305 CURSOR cur_rowid IS
306 SELECT rowid
307 FROM IGS_EN_ATD_TYPE_ALL
308 WHERE govt_attendance_type = x_govt_attendance_type ;
309
310 lv_rowid cur_rowid%RowType;
311
312 BEGIN
313
314 Open cur_rowid;
315 Fetch cur_rowid INTO lv_rowid;
316 IF (cur_rowid%FOUND) THEN
317 Close cur_rowid;
318 Fnd_Message.Set_Name ('IGS', 'IGS_EN_ATT_GAT_FK');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321 Return;
322 END IF;
323 Close cur_rowid;
324
325 END GET_FK_IGS_PS_GOVT_ATD_TYPE;
326
327 PROCEDURE Before_DML (
328 p_action IN VARCHAR2,
329 x_rowid IN VARCHAR2 DEFAULT NULL,
330 x_org_id IN NUMBER DEFAULT NULL,
331 x_attendance_type IN VARCHAR2 DEFAULT NULL,
332 x_description IN VARCHAR2 DEFAULT NULL,
333 x_govt_attendance_type IN VARCHAR2 DEFAULT NULL,
334 x_lower_enr_load_range IN NUMBER DEFAULT NULL,
335 x_upper_enr_load_range IN NUMBER DEFAULT NULL,
336 x_research_percentage IN NUMBER DEFAULT NULL,
337 x_closed_ind IN VARCHAR2 DEFAULT NULL,
338 x_creation_date IN DATE DEFAULT NULL,
339 x_created_by IN NUMBER DEFAULT NULL,
340 x_last_update_date IN DATE DEFAULT NULL,
341 x_last_updated_by IN NUMBER DEFAULT NULL,
342 x_last_update_login IN NUMBER DEFAULT NULL
343 )AS
344 BEGIN
345
346 Set_Column_Values (
347 p_action,
348 x_rowid,
349 x_org_id,
350 x_attendance_type,
351 x_description,
352 x_govt_attendance_type,
353 x_lower_enr_load_range,
354 x_upper_enr_load_range,
355 x_research_percentage,
356 x_closed_ind,
357 x_creation_date,
358 x_created_by,
359 x_last_update_date,
360 x_last_updated_by,
361 x_last_update_login
362 );
363
364 IF (p_action = 'INSERT') THEN
365 -- Call all the procedures related to Before Insert.
366 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
367 IF Get_PK_For_Validation (
368 new_references.attendance_type
369 ) THEN
370 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
371 IGS_GE_MSG_STACK.ADD;
372 App_Exception.Raise_Exception;
373 END IF;
374 Check_constraints;
375 Check_Parent_Existance;
376 ELSIF (p_action = 'UPDATE') THEN
377 -- Call all the procedures related to Before Update.
378 BeforeRowInsertUpdate1 ( p_updating => TRUE );
379 Check_constraints;
380 Check_Parent_Existance;
381 ELSIF (p_action = 'DELETE') THEN
382 -- Call all the procedures related to Before Delete.
383 beforerowdelete;
384 Check_Child_Existance;
385 ELSIF (p_action = 'VALIDATE_INSERT') then
386 IF Get_PK_For_Validation (
387 new_references.attendance_type
388 ) THEN
389 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
390 IGS_GE_MSG_STACK.ADD;
391 App_Exception.Raise_Exception;
392 END IF;
393 Check_constraints;
394 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
395 Check_constraints;
396 ELSIF (p_action = 'VALIDATE_DELETE') THEN
397 Check_Child_Existance;
398 END IF;
399
400 END Before_DML;
401
402 PROCEDURE After_DML (
403 p_action IN VARCHAR2,
404 x_rowid IN VARCHAR2
405 ) AS
406 BEGIN
407
408 l_rowid := x_rowid;
409
410 IF (p_action = 'INSERT') THEN
411 -- Call all the procedures related to After Insert.
412 Null;
413 ELSIF (p_action = 'UPDATE') THEN
414 -- Call all the procedures related to After Update.
415 Null;
416 ELSIF (p_action = 'DELETE') THEN
417 -- Call all the procedures related to After Delete.
418 Null;
419 END IF;
420
421 END After_DML;
422
423 procedure INSERT_ROW (
424 X_ROWID in out NOCOPY VARCHAR2,
425 X_ORG_ID in NUMBER,
426 X_ATTENDANCE_TYPE in VARCHAR2,
427 X_DESCRIPTION in VARCHAR2,
428 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
429 X_LOWER_ENR_LOAD_RANGE in NUMBER,
430 X_UPPER_ENR_LOAD_RANGE in NUMBER,
431 X_RESEARCH_PERCENTAGE in NUMBER,
432 X_CLOSED_IND in VARCHAR2,
433 X_MODE in VARCHAR2 default 'R'
434 ) AS
435 cursor C is select ROWID from IGS_EN_ATD_TYPE_ALL
436 where ATTENDANCE_TYPE = X_ATTENDANCE_TYPE;
437 X_LAST_UPDATE_DATE DATE;
438 X_LAST_UPDATED_BY NUMBER;
439 X_LAST_UPDATE_LOGIN NUMBER;
440 begin
441 X_LAST_UPDATE_DATE := SYSDATE;
442 if(X_MODE = 'I') then
443 X_LAST_UPDATED_BY := 1;
444 X_LAST_UPDATE_LOGIN := 0;
445 elsif (X_MODE = 'R') then
446 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
447 if X_LAST_UPDATED_BY is NULL then
448 X_LAST_UPDATED_BY := -1;
449 end if;
450 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
451 if X_LAST_UPDATE_LOGIN is NULL then
452 X_LAST_UPDATE_LOGIN := -1;
453 end if;
454 else
455 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
456 IGS_GE_MSG_STACK.ADD;
457 app_exception.raise_exception;
458 end if;
459
460 Before_DML(
461 p_action => 'INSERT',
462 x_rowid => X_ROWID,
463 x_org_id => igs_ge_gen_003.get_org_id,
464 x_attendance_type => X_ATTENDANCE_TYPE,
465 x_description => X_DESCRIPTION,
466 x_govt_attendance_type => X_GOVT_ATTENDANCE_TYPE,
467 x_lower_enr_load_range => X_LOWER_ENR_LOAD_RANGE,
468 x_upper_enr_load_range => X_UPPER_ENR_LOAD_RANGE,
469 x_research_percentage => X_RESEARCH_PERCENTAGE,
470 x_closed_ind => X_CLOSED_IND,
471 x_creation_date => X_LAST_UPDATE_DATE,
472 x_created_by => X_LAST_UPDATED_BY,
473 x_last_update_date =>X_LAST_UPDATE_DATE,
474 x_last_updated_by => X_LAST_UPDATED_BY,
475 x_last_update_login => X_LAST_UPDATE_LOGIN
476 );
477
478 insert into IGS_EN_ATD_TYPE_ALL (
479 org_id,
480 ATTENDANCE_TYPE,
481 DESCRIPTION,
482 GOVT_ATTENDANCE_TYPE,
483 LOWER_ENR_LOAD_RANGE,
484 UPPER_ENR_LOAD_RANGE,
485 RESEARCH_PERCENTAGE,
486 CLOSED_IND,
487 CREATION_DATE,
488 CREATED_BY,
489 LAST_UPDATE_DATE,
490 LAST_UPDATED_BY,
491 LAST_UPDATE_LOGIN
492 ) values (
493 NEW_REFERENCES.ORG_ID,
494 NEW_REFERENCES.ATTENDANCE_TYPE,
495 NEW_REFERENCES.DESCRIPTION,
496 NEW_REFERENCES.GOVT_ATTENDANCE_TYPE,
497 NEW_REFERENCES.LOWER_ENR_LOAD_RANGE,
498 NEW_REFERENCES.UPPER_ENR_LOAD_RANGE,
499 NEW_REFERENCES.RESEARCH_PERCENTAGE,
500 NEW_REFERENCES.CLOSED_IND,
501 X_LAST_UPDATE_DATE,
502 X_LAST_UPDATED_BY,
503 X_LAST_UPDATE_DATE,
504 X_LAST_UPDATED_BY,
505 X_LAST_UPDATE_LOGIN
506 );
507
508 open c;
509 fetch c into X_ROWID;
510 if (c%notfound) then
511 close c;
512 raise no_data_found;
513 end if;
514 close c;
515
516 After_DML(
517 p_action => 'INSERT',
518 x_rowid => X_ROWID
519 );
520
521 end INSERT_ROW;
522
523 procedure LOCK_ROW (
524 X_ROWID in VARCHAR2,
525 X_ATTENDANCE_TYPE in VARCHAR2,
526 X_DESCRIPTION in VARCHAR2,
527 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
528 X_LOWER_ENR_LOAD_RANGE in NUMBER,
529 X_UPPER_ENR_LOAD_RANGE in NUMBER,
530 X_RESEARCH_PERCENTAGE in NUMBER,
531 X_CLOSED_IND in VARCHAR2
532 ) AS
533 cursor c1 is select
534
535 DESCRIPTION,
536 GOVT_ATTENDANCE_TYPE,
537 LOWER_ENR_LOAD_RANGE,
538 UPPER_ENR_LOAD_RANGE,
539 RESEARCH_PERCENTAGE,
540 CLOSED_IND
541 from IGS_EN_ATD_TYPE_ALL
542 where ROWID = X_ROWID
543 for update nowait;
544 tlinfo c1%rowtype;
545
546 begin
547 open c1;
548 fetch c1 into tlinfo;
549 if (c1%notfound) then
550 close c1;
551 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
552 IGS_GE_MSG_STACK.ADD;
553 app_exception.raise_exception;
554 return;
555 end if;
556 close c1;
557
558 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
559 AND (tlinfo.GOVT_ATTENDANCE_TYPE = X_GOVT_ATTENDANCE_TYPE)
560 AND (tlinfo.LOWER_ENR_LOAD_RANGE = X_LOWER_ENR_LOAD_RANGE)
561 AND (tlinfo.UPPER_ENR_LOAD_RANGE = X_UPPER_ENR_LOAD_RANGE)
562 AND ((tlinfo.RESEARCH_PERCENTAGE = X_RESEARCH_PERCENTAGE)
563 OR ((tlinfo.RESEARCH_PERCENTAGE is null)
564 AND (X_RESEARCH_PERCENTAGE is null)))
565 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
566 ) then
567 null;
568 else
569 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
570 IGS_GE_MSG_STACK.ADD;
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_ATTENDANCE_TYPE in VARCHAR2,
579 X_DESCRIPTION in VARCHAR2,
580 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
581 X_LOWER_ENR_LOAD_RANGE in NUMBER,
582 X_UPPER_ENR_LOAD_RANGE in NUMBER,
583 X_RESEARCH_PERCENTAGE in NUMBER,
584 X_CLOSED_IND in VARCHAR2,
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 = 'R') 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_attendance_type => X_ATTENDANCE_TYPE,
614 x_description => X_DESCRIPTION,
615 x_govt_attendance_type => X_GOVT_ATTENDANCE_TYPE,
616 x_lower_enr_load_range => X_LOWER_ENR_LOAD_RANGE,
617 x_upper_enr_load_range => X_UPPER_ENR_LOAD_RANGE,
618 x_research_percentage => X_RESEARCH_PERCENTAGE,
619 x_closed_ind => X_CLOSED_IND,
620 x_creation_date => X_LAST_UPDATE_DATE,
621 x_created_by => X_LAST_UPDATED_BY,
622 x_last_update_date =>X_LAST_UPDATE_DATE,
623 x_last_updated_by => X_LAST_UPDATED_BY,
624 x_last_update_login => X_LAST_UPDATE_LOGIN
625 );
626
627 update IGS_EN_ATD_TYPE_ALL set
628 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
629 GOVT_ATTENDANCE_TYPE = NEW_REFERENCES.GOVT_ATTENDANCE_TYPE,
630 LOWER_ENR_LOAD_RANGE = NEW_REFERENCES.LOWER_ENR_LOAD_RANGE,
631 UPPER_ENR_LOAD_RANGE = NEW_REFERENCES.UPPER_ENR_LOAD_RANGE,
632 RESEARCH_PERCENTAGE = NEW_REFERENCES.RESEARCH_PERCENTAGE,
633 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
634 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
635 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
636 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
637 where ROWID = X_ROWID
638 ;
639 if (sql%notfound) then
640 raise no_data_found;
641 end if;
642
643 After_DML(
644 p_action => 'UPDATE',
645 x_rowid => X_ROWID
646 );
647
648 end UPDATE_ROW;
649
650 procedure ADD_ROW (
651 X_ROWID in out NOCOPY VARCHAR2,
652 X_ORG_ID in NUMBER,
653 X_ATTENDANCE_TYPE in VARCHAR2,
654 X_DESCRIPTION in VARCHAR2,
655 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
656 X_LOWER_ENR_LOAD_RANGE in NUMBER,
657 X_UPPER_ENR_LOAD_RANGE in NUMBER,
658 X_RESEARCH_PERCENTAGE in NUMBER,
659 X_CLOSED_IND in VARCHAR2,
660 X_MODE in VARCHAR2 default 'R'
661 ) AS
662 cursor c1 is select rowid from IGS_EN_ATD_TYPE_ALL
663 where ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
664 ;
665
666 begin
667 open c1;
668 fetch c1 into X_ROWID;
669 if (c1%notfound) then
670 close c1;
671 INSERT_ROW (
672 X_ROWID,
673 x_org_id,
674 X_ATTENDANCE_TYPE,
675 X_DESCRIPTION,
676 X_GOVT_ATTENDANCE_TYPE,
677 X_LOWER_ENR_LOAD_RANGE,
678 X_UPPER_ENR_LOAD_RANGE,
679 X_RESEARCH_PERCENTAGE,
680 X_CLOSED_IND,
681 X_MODE);
682 return;
683 end if;
684 close c1;
685 UPDATE_ROW (
686 X_ROWID,
687 X_ATTENDANCE_TYPE,
688 X_DESCRIPTION,
689 X_GOVT_ATTENDANCE_TYPE,
690 X_LOWER_ENR_LOAD_RANGE,
691 X_UPPER_ENR_LOAD_RANGE,
692 X_RESEARCH_PERCENTAGE,
693 X_CLOSED_IND,
694 X_MODE);
695 end ADD_ROW;
696
697 procedure DELETE_ROW (
698 X_ROWID in VARCHAR2
699 ) AS
700 begin
701 Before_DML(
702 p_action => 'DELETE',
703 x_rowid => X_ROWID
704 );
705
706
707 delete from IGS_EN_ATD_TYPE_ALL
708 where ROWID = X_ROWID;
709 if (sql%notfound) then
710 raise no_data_found;
711 end if;
712
713 After_DML(
714 p_action => 'DELETE',
715 x_rowid => X_ROWID
716 );
717
718 end DELETE_ROW;
719
720 PROCEDURE beforerowdelete AS
721 ------------------------------------------------------------------
722 --Created by : smvk, Oracle India
723 --Date created: 03-Jan-2003
724 --
725 --Purpose: Attendance Type records can be deleted logically by setting the closed_ind as 'Y'
726 -- No physical deletion is allowed. As a part of Bug # 2729917
727 --
728 --
729 --Known limitations/enhancements and/or remarks:
730 --
731 --Change History:
732 --Who When What
733 -------------------------------------------------------------------
734 BEGIN
735 -- Preventing deletion of the Attendance Type records. As a part of Bug # 2729917
736 FND_MESSAGE.SET_NAME('IGS','IGS_FI_DEL_NOT_ALLWD');
737 IGS_GE_MSG_STACK.ADD;
738 APP_EXCEPTION.RAISE_EXCEPTION;
739 END beforerowdelete;
740
741 end IGS_EN_ATD_TYPE_PKG;