[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_FEE_AS_RT_PKG
Source
1 package body IGS_FI_FEE_AS_RT_PKG as
2 /* $Header: IGSSI20B.pls 120.2 2006/05/26 13:42:06 skharida ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_FI_FEE_AS_RT%RowType;
6 new_references IGS_FI_FEE_AS_RT%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_fee_type IN VARCHAR2 DEFAULT NULL,
13 x_start_dt IN DATE DEFAULT NULL,
14 x_end_dt IN DATE DEFAULT NULL,
15 x_location_cd IN VARCHAR2 DEFAULT NULL,
16 x_attendance_type IN VARCHAR2 DEFAULT NULL,
17 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
18 x_chg_rate IN NUMBER DEFAULT NULL,
19 x_lower_nrml_rate_ovrd_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 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_FI_FEE_AS_RT
29 WHERE rowid = x_rowid;
30 BEGIN
31 l_rowid := x_rowid;
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
37 Close cur_old_ref_values;
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44 -- Populate New Values.
45 new_references.person_id := x_person_id;
46 new_references.course_cd := x_course_cd;
47 new_references.fee_type := x_fee_type;
48 new_references.start_dt := x_start_dt;
49 new_references.end_dt := x_end_dt;
50 new_references.location_cd := x_location_cd;
51 new_references.attendance_type := x_attendance_type;
52 new_references.attendance_mode := x_attendance_mode;
53 new_references.chg_rate := x_chg_rate;
54 new_references.lower_nrml_rate_ovrd_ind := x_lower_nrml_rate_ovrd_ind;
55 IF (p_action = 'UPDATE') THEN
56 new_references.creation_date := old_references.creation_date;
57 new_references.created_by := old_references.created_by;
58 ELSE
59 new_references.creation_date := x_creation_date;
60 new_references.created_by := x_created_by;
61 END IF;
62 new_references.last_update_date := x_last_update_date;
63 new_references.last_updated_by := x_last_updated_by;
64 new_references.last_update_login := x_last_update_login;
65 END Set_Column_Values;
66 -- Trigger description :-
67 -- "OSS_TST".trg_cfar_br_iu
68 -- BEFORE INSERT OR UPDATE
69 -- ON IGS_FI_FEE_AS_RT
70 -- FOR EACH ROW
71 PROCEDURE BeforeRowInsertUpdate1(
72 p_inserting IN BOOLEAN DEFAULT FALSE,
73 p_updating IN BOOLEAN DEFAULT FALSE,
74 p_deleting IN BOOLEAN DEFAULT FALSE
75 ) AS
76 v_message_name varchar2(30);
77 BEGIN
78 -- Validate contract fee assessment rate
79 IF p_inserting THEN
80 IF IGS_FI_VAL_CFAR.finp_val_cfar_ins(
81 new_references.person_id,
82 new_references.course_cd,
83 new_references.fee_type,
84 v_message_name) = FALSE THEN
85 Fnd_Message.Set_Name('IGS',v_message_name);
86 IGS_GE_MSG_STACK.ADD;
87 App_Exception.Raise_Exception;
88 END IF;
89 END IF;
90 IF p_inserting OR
91 (p_updating AND new_references.end_dt IS NOT NULL AND
92 (new_references.start_dt <> old_references.start_dt OR new_references.end_dt <> old_references.end_dt)) THEN
93 IF IGS_FI_VAL_CFAR.finp_val_cfar_end_dt(
94 new_references.start_dt,
95 new_references.end_dt,
96 v_message_name) = FALSE THEN
97 Fnd_Message.Set_Name('IGS',v_message_name);
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 END IF;
101 END IF;
102 IF p_inserting OR (p_updating AND
103 new_references.fee_type <> old_references.fee_type) THEN
104 IF IGS_FI_VAL_CFAR.finp_val_ft_closed(
105 new_references.fee_type,
106 v_message_name) = FALSE THEN
107 Fnd_Message.Set_Name('IGS',v_message_name);
108 IGS_GE_MSG_STACK.ADD;
109 App_Exception.Raise_Exception;
110 END IF;
111 END IF;
112 IF p_inserting OR (p_updating AND
113 new_references.attendance_type <> old_references.attendance_type) THEN
114 IF IGS_FI_VAL_CFAR.finp_val_att_closed(
115 new_references.attendance_type,
116 v_message_name) = FALSE THEN
117 Fnd_Message.Set_Name('IGS',v_message_name);
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END IF;
121 END IF;
122 IF p_inserting OR (p_updating AND
123 new_references.attendance_mode <> old_references.attendance_mode) THEN
124 IF IGS_FI_VAL_CFAR.finp_val_am_closed(
125 new_references.attendance_mode,
126 v_message_name) = FALSE THEN
127 Fnd_Message.Set_Name('IGS',v_message_name);
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
130 END IF;
131 END IF;
132 IF p_inserting OR (p_updating AND
133 new_references.location_cd <> old_references.location_cd) THEN
134 IF IGS_FI_VAL_CFAR.finp_val_loc_closed(
135 new_references.location_cd,
136 v_message_name) = FALSE THEN
137 Fnd_Message.Set_Name('IGS',v_message_name);
138 IGS_GE_MSG_STACK.ADD;
139 App_Exception.Raise_Exception;
140 END IF;
141 END IF;
142 END BeforeRowInsertUpdate1;
143 -- Trigger description :-
144 -- "OSS_TST".trg_cfar_ar_u_hist
145 -- AFTER UPDATE
146 -- ON IGS_FI_FEE_AS_RT
147 -- FOR EACH ROW
148 PROCEDURE AfterRowUpdate3(
149 p_inserting IN BOOLEAN DEFAULT FALSE,
150 p_updating IN BOOLEAN DEFAULT FALSE,
151 p_deleting IN BOOLEAN DEFAULT FALSE
152 ) AS
153 BEGIN
154 -- create a history
155 IGS_FI_GEN_002.finp_ins_cfar_hist(old_references.person_id,
156 old_references.course_cd,
157 old_references.fee_type,
158 old_references.start_dt,
159 new_references.end_dt,
160 old_references.end_dt,
161 new_references.location_cd,
162 old_references.location_cd,
163 new_references.attendance_type,
164 old_references.attendance_type,
165 new_references.attendance_mode,
166 old_references.attendance_mode,
167 new_references.chg_rate,
168 old_references.chg_rate,
169 new_references.lower_nrml_rate_ovrd_ind,
170 old_references.lower_nrml_rate_ovrd_ind,
171 new_references.last_updated_by,
172 old_references.last_updated_by,
173 new_references.last_update_date,
174 old_references.last_update_date);
175 END AfterRowUpdate3;
176 -- Trigger description :-
177 -- "OSS_TST".trg_cfar_as_iu
178 -- AFTER INSERT OR UPDATE
179 -- ON IGS_FI_FEE_AS_RT
180 PROCEDURE AfterStmtInsertUpdate4(
181 p_inserting IN BOOLEAN DEFAULT FALSE,
182 p_updating IN BOOLEAN DEFAULT FALSE,
183 p_deleting IN BOOLEAN DEFAULT FALSE
184 ) AS
185 v_message_name varchar2(30);
186 BEGIN
187 -- Validate the start and end dates
188 IF p_inserting OR p_updating THEN
189 IF new_references.end_dt IS NULL THEN
190 IF IGS_FI_VAL_CFAR.finp_val_cfar_open (
191 new_references.person_id,
192 new_references.course_cd,
193 new_references.fee_type,
194 new_references.start_dt,
195 v_message_name) = FALSE THEN
196 Fnd_Message.Set_Name('IGS',v_message_name);
197 IGS_GE_MSG_STACK.ADD;
198 App_Exception.Raise_Exception;
199 END IF;
200 END IF;
201 IF IGS_FI_VAL_CFAR.finp_val_cfar_ovrlp (
202 new_references.person_id,
203 new_references.course_cd,
204 new_references.fee_type,
205 new_references.start_dt,
206 new_references.end_dt,
207 v_message_name) = FALSE THEN
208 Fnd_Message.Set_Name('IGS',v_message_name);
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF;
212 END IF;
213 END AfterStmtInsertUpdate4;
214 PROCEDURE Check_Constraints (
215 Column_Name IN VARCHAR2 DEFAULT NULL,
216 Column_Value IN VARCHAR2 DEFAULT NULL
217 ) AS
218 /*----------------------------------------------------------------------------
219 || Created By :
220 || Created On :
221 || Purpose :
222 || Known limitations, enhancements or remarks :
223 || Change History :
224 || Who When What
225 || (reverse chronological order - newest change first)
226 || skharida 26-May-2006 Bug 5217319 Removed the hardcoded precision check
227 || vvutukur 17-May-2002 removed upper check on fee_type column.bug#2344826.
228 ----------------------------------------------------------------------------*/
229 BEGIN
230 IF column_name is null then
231 NULL;
232 ELSIF upper(Column_name) = 'CHG_RATE' then
233 new_references.chg_rate := igs_ge_number.to_num(column_value);
234 ELSIF upper(Column_name) = 'ATTENDANCE_MODE' then
235 new_references.attendance_mode := column_value;
236 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' then
237 new_references.attendance_type := column_value;
238 ELSIF upper(Column_name) = 'COURSE_CD' then
239 new_references.course_cd := column_value;
240 ELSIF upper(Column_name) = 'LOCATION_CD' then
241 new_references.location_cd := column_value;
242 ELSIF upper(Column_name) = 'LOWER_NRML_RATE_OVRD_IND' then
243 new_references.lower_nrml_rate_ovrd_ind := column_value;
244 End if;
245
246 IF upper(column_name) = 'CHG_RATE' OR
247 column_name is null Then
248 IF new_references.chg_rate < 0 Then
249 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END IF;
253 END IF;
254
255 IF upper(column_name) = 'ATTENDANCE_MODE' OR
256 column_name is null Then
257 IF new_references.attendance_mode <>
258 UPPER(new_references.attendance_mode) Then
259 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
260 IGS_GE_MSG_STACK.ADD;
261 App_Exception.Raise_Exception;
262 END IF;
263 END IF;
264
265 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
266 column_name is null Then
267 IF new_references.ATTENDANCE_TYPE <>
268 UPPER(new_references.ATTENDANCE_TYPE) Then
269 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272 END IF;
273 END IF;
274
275 IF upper(column_name) = 'COURSE_CD' OR
276 column_name is null Then
277 IF new_references.COURSE_CD <>
278 UPPER(new_references.COURSE_CD) Then
279 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
280 IGS_GE_MSG_STACK.ADD;
281 App_Exception.Raise_Exception;
282 END IF;
283 END IF;
284
285 IF upper(column_name) = 'LOCATION_CD' OR
286 column_name is null Then
287 IF new_references.LOCATION_CD <>
288 UPPER(new_references.LOCATION_CD) Then
289 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
290 IGS_GE_MSG_STACK.ADD;
291 App_Exception.Raise_Exception;
292 END IF;
293 END IF;
294
295 IF upper(column_name) = 'LOWER_NRML_RATE_OVRD_IND' OR
296 column_name is null Then
297 IF new_references.LOWER_NRML_RATE_OVRD_IND <>
298 UPPER(new_references.LOWER_NRML_RATE_OVRD_IND) Then
299 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
300 IGS_GE_MSG_STACK.ADD;
301 App_Exception.Raise_Exception;
302 END IF;
303 END IF;
304
305 IF upper(column_name) = 'LOWER_NRML_RATE_OVRD_IND' OR
306 column_name is null Then
307 IF (new_references.lower_nrml_rate_ovrd_ind not in ('Y', 'N')) Then
308 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 END IF;
312 END IF;
313 END Check_Constraints;
314 PROCEDURE Check_Parent_Existance AS
315 BEGIN
316 IF (((old_references.attendance_mode = new_references.attendance_mode)) OR
317 ((new_references.attendance_mode IS NULL))) THEN
318 NULL;
319 ELSE
320 IF NOT IGS_EN_ATD_MODE_PKG.Get_PK_For_Validation (
321 new_references.attendance_mode
322 ) THEN
323 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
324 IGS_GE_MSG_STACK.ADD;
325 App_Exception.Raise_Exception;
326 END IF;
327 END IF;
328 IF (((old_references.attendance_type = new_references.attendance_type)) OR
329 ((new_references.attendance_type IS NULL))) THEN
330 NULL;
331 ELSE
332 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
333 new_references.attendance_type
334 ) THEN
335 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
336 IGS_GE_MSG_STACK.ADD;
337 App_Exception.Raise_Exception;
338 END IF;
339 END IF;
340 IF (((old_references.fee_type = new_references.fee_type)) OR
341 ((new_references.fee_type IS NULL))) THEN
342 NULL;
343 ELSE
344 IF NOT IGS_FI_FEE_TYPE_PKG.Get_PK_For_Validation (
345 new_references.fee_type
346 ) THEN
347 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
348 IGS_GE_MSG_STACK.ADD;
349 App_Exception.Raise_Exception;
350 END IF;
351 END IF;
352 IF (((old_references.location_cd = new_references.location_cd)) OR
353 ((new_references.location_cd IS NULL))) THEN
354 NULL;
355 ELSE
356 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
357 new_references.location_cd ,
358 'N'
359 ) THEN
360 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
361 IGS_GE_MSG_STACK.ADD;
362 App_Exception.Raise_Exception;
363 END IF;
364 END IF;
365 IF (((old_references.person_id = new_references.person_id) AND
366 (old_references.course_cd = new_references.course_cd)) OR
367 ((new_references.person_id IS NULL) OR
368 (new_references.course_cd IS NULL))) THEN
369 NULL;
370 ELSE
371 IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
372 new_references.person_id,
373 new_references.course_cd
374 ) THEN
375 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
376 IGS_GE_MSG_STACK.ADD;
377 App_Exception.Raise_Exception;
378 END IF;
379 END IF;
380 END Check_Parent_Existance;
381 Function Get_PK_For_Validation (
382 x_person_id IN NUMBER,
383 x_course_cd IN VARCHAR2,
384 x_fee_type IN VARCHAR2,
385 x_start_dt IN DATE
386 ) Return Boolean
387 AS
388 CURSOR cur_rowid IS
389 SELECT rowid
390 FROM IGS_FI_FEE_AS_RT
391 WHERE person_id = x_person_id
392 AND course_cd = x_course_cd
393 AND fee_type = x_fee_type
394 AND start_dt = x_start_dt
395 FOR UPDATE NOWAIT;
396 lv_rowid cur_rowid%RowType;
397 BEGIN
398 Open cur_rowid;
399 Fetch cur_rowid INTO lv_rowid;
400 IF (cur_rowid%FOUND) THEN
401 Close cur_rowid;
402 Return (TRUE);
403 ELSE
404 Close cur_rowid;
405 Return (FALSE);
406 END IF;
407 END Get_PK_For_Validation;
408 PROCEDURE GET_FK_IGS_EN_ATD_MODE (
409 x_attendance_mode IN VARCHAR2
410 ) AS
411 CURSOR cur_rowid IS
412 SELECT rowid
413 FROM IGS_FI_FEE_AS_RT
414 WHERE attendance_mode = x_attendance_mode ;
415 lv_rowid cur_rowid%RowType;
416 BEGIN
417 Open cur_rowid;
418 Fetch cur_rowid INTO lv_rowid;
419 IF (cur_rowid%FOUND) THEN
420 Close cur_rowid;
421 Fnd_Message.Set_Name ('IGS', 'IGS_FI_CFAR_AM_FK');
422 IGS_GE_MSG_STACK.ADD;
423 App_Exception.Raise_Exception;
424 Return;
425 END IF;
426 Close cur_rowid;
427 END GET_FK_IGS_EN_ATD_MODE;
428 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
429 x_attendance_type IN VARCHAR2
430 ) AS
431 CURSOR cur_rowid IS
432 SELECT rowid
433 FROM IGS_FI_FEE_AS_RT
434 WHERE attendance_type = x_attendance_type ;
435 lv_rowid cur_rowid%RowType;
436 BEGIN
437 Open cur_rowid;
438 Fetch cur_rowid INTO lv_rowid;
439 IF (cur_rowid%FOUND) THEN
440 Close cur_rowid;
441 Fnd_Message.Set_Name ('IGS', 'IGS_FI_CFAR_ATT_FK');
442 IGS_GE_MSG_STACK.ADD;
443 App_Exception.Raise_Exception;
444 Return;
445 END IF;
446 Close cur_rowid;
447 END GET_FK_IGS_EN_ATD_TYPE;
448
449 PROCEDURE GET_FK_IGS_AD_LOCATION (
450 x_location_cd IN VARCHAR2
451 ) AS
452 CURSOR cur_rowid IS
453 SELECT rowid
454 FROM IGS_FI_FEE_AS_RT
455 WHERE location_cd = x_location_cd ;
456 lv_rowid cur_rowid%RowType;
457 BEGIN
458 Open cur_rowid;
459 Fetch cur_rowid INTO lv_rowid;
460 IF (cur_rowid%FOUND) THEN
461 Close cur_rowid;
462 Fnd_Message.Set_Name ('IGS', 'IGS_FI_CFAR_LOC_FK');
463 IGS_GE_MSG_STACK.ADD;
464 App_Exception.Raise_Exception;
465 Return;
466 END IF;
467 Close cur_rowid;
468 END GET_FK_IGS_AD_LOCATION;
469 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
470 x_person_id IN NUMBER,
471 x_course_cd IN VARCHAR2
472 ) AS
473 CURSOR cur_rowid IS
474 SELECT rowid
475 FROM IGS_FI_FEE_AS_RT
476 WHERE person_id = x_person_id
477 AND course_cd = x_course_cd ;
478 lv_rowid cur_rowid%RowType;
479 BEGIN
480 Open cur_rowid;
481 Fetch cur_rowid INTO lv_rowid;
482 IF (cur_rowid%FOUND) THEN
483 Close cur_rowid;
484 Fnd_Message.Set_Name ('IGS', 'IGS_FI_CFAR_SCA_FK');
485 IGS_GE_MSG_STACK.ADD;
486 App_Exception.Raise_Exception;
487 Return;
488 END IF;
489 Close cur_rowid;
490 END GET_FK_IGS_EN_STDNT_PS_ATT;
491 PROCEDURE Before_DML (
492 p_action IN VARCHAR2,
493 x_rowid IN VARCHAR2 DEFAULT NULL,
494 x_person_id IN NUMBER DEFAULT NULL,
495 x_course_cd IN VARCHAR2 DEFAULT NULL,
496 x_fee_type IN VARCHAR2 DEFAULT NULL,
497 x_start_dt IN DATE DEFAULT NULL,
498 x_end_dt IN DATE DEFAULT NULL,
499 x_location_cd IN VARCHAR2 DEFAULT NULL,
500 x_attendance_type IN VARCHAR2 DEFAULT NULL,
501 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
502 x_chg_rate IN NUMBER DEFAULT NULL,
503 x_lower_nrml_rate_ovrd_ind IN VARCHAR2 DEFAULT NULL,
504 x_creation_date IN DATE DEFAULT NULL,
505 x_created_by IN NUMBER DEFAULT NULL,
506 x_last_update_date IN DATE DEFAULT NULL,
507 x_last_updated_by IN NUMBER DEFAULT NULL,
508 x_last_update_login IN NUMBER DEFAULT NULL
509 ) AS
510 BEGIN
511 Set_Column_Values (
512 p_action,
513 x_rowid,
514 x_person_id,
515 x_course_cd,
516 x_fee_type,
517 x_start_dt,
518 x_end_dt,
519 x_location_cd,
520 x_attendance_type,
521 x_attendance_mode,
522 x_chg_rate,
523 x_lower_nrml_rate_ovrd_ind,
524 x_creation_date,
525 x_created_by,
526 x_last_update_date,
527 x_last_updated_by,
528 x_last_update_login
529 );
530 IF (p_action = 'INSERT') THEN
531 -- Call all the procedures related to Before Insert.
532 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
533 IF Get_PK_For_Validation (
534 new_references.person_id ,
535 new_references.course_cd ,
536 new_references.fee_type ,
537 new_references.start_dt
538 ) THEN
539 Fnd_Message.Set_Name ('IGS', 'IGS_FI_CONTRACT_EXISTS_FEETYP');
540 IGS_GE_MSG_STACK.ADD;
541 App_Exception.Raise_Exception;
542 END IF;
543 Check_Constraints;
544 Check_Parent_Existance;
545 ELSIF (p_action = 'UPDATE') THEN
546 -- Call all the procedures related to Before Update.
547 BeforeRowInsertUpdate1 ( p_updating => TRUE );
548 Check_Constraints;
549 Check_Parent_Existance;
550 ELSIF (p_action = 'DELETE') THEN
551 -- Call all the procedures related to Before Delete.
552 Null;
553 ELSIF (p_action = 'VALIDATE_INSERT') THEN
554 IF Get_PK_For_Validation (
555 new_references.person_id ,
556 new_references.course_cd ,
557 new_references.fee_type ,
558 new_references.start_dt
559 ) THEN
560 Fnd_Message.Set_Name ('IGS', 'IGS_FI_CONTRACT_EXISTS_FEETYP');
561 IGS_GE_MSG_STACK.ADD;
562 App_Exception.Raise_Exception;
563 END IF;
564 Check_Constraints;
565 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
566 Check_Constraints;
567 ELSIF (p_action = 'VALIDATE_DELETE') THEN
568 Null;
569 END IF;
570 END Before_DML;
571 PROCEDURE After_DML (
572 p_action IN VARCHAR2,
573 x_rowid IN VARCHAR2
574 ) AS
575 BEGIN
576 l_rowid := x_rowid;
577 IF (p_action = 'INSERT') THEN
578 -- Call all the procedures related to After Insert.
579 AfterStmtInsertUpdate4 ( p_inserting => TRUE );
580 ELSIF (p_action = 'UPDATE') THEN
581 -- Call all the procedures related to After Update.
582 AfterRowUpdate3 ( p_updating => TRUE );
583 AfterStmtInsertUpdate4 ( p_updating => TRUE );
584 ELSIF (p_action = 'DELETE') THEN
585 -- Call all the procedures related to After Delete.
586 Null;
587 END IF;
588 END After_DML;
589 procedure INSERT_ROW (
590 X_ROWID in out NOCOPY VARCHAR2,
591 X_PERSON_ID in NUMBER,
592 X_COURSE_CD in VARCHAR2,
593 X_FEE_TYPE in VARCHAR2,
594 X_START_DT in DATE,
595 X_END_DT in DATE,
596 X_LOCATION_CD in VARCHAR2,
597 X_ATTENDANCE_TYPE in VARCHAR2,
598 X_ATTENDANCE_MODE in VARCHAR2,
599 X_CHG_RATE in NUMBER,
600 X_LOWER_NRML_RATE_OVRD_IND in VARCHAR2,
601 X_MODE in VARCHAR2 default 'R'
602 ) as
603 cursor C is select ROWID from IGS_FI_FEE_AS_RT
604 where PERSON_ID = X_PERSON_ID
605 and COURSE_CD = X_COURSE_CD
606 and FEE_TYPE = X_FEE_TYPE
607 and START_DT = X_START_DT;
608 X_LAST_UPDATE_DATE DATE;
609 X_LAST_UPDATED_BY NUMBER;
610 X_LAST_UPDATE_LOGIN NUMBER;
611 X_REQUEST_ID NUMBER;
612 X_PROGRAM_ID NUMBER;
613 X_PROGRAM_APPLICATION_ID NUMBER;
614 X_PROGRAM_UPDATE_DATE DATE;
615 begin
616 X_LAST_UPDATE_DATE := SYSDATE;
617 if(X_MODE = 'I') then
618 X_LAST_UPDATED_BY := 1;
619 X_LAST_UPDATE_LOGIN := 0;
620 elsif (X_MODE IN ('R', 'S')) then
621 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
622 if X_LAST_UPDATED_BY is NULL then
623 X_LAST_UPDATED_BY := -1;
624 end if;
625 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
626 if X_LAST_UPDATE_LOGIN is NULL then
627 X_LAST_UPDATE_LOGIN := -1;
628 end if;
629 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
630 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
631 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
632 if (X_REQUEST_ID = -1) then
633 X_REQUEST_ID := NULL;
634 X_PROGRAM_ID := NULL;
635 X_PROGRAM_APPLICATION_ID := NULL;
636 X_PROGRAM_UPDATE_DATE := NULL;
637 else
638 X_PROGRAM_UPDATE_DATE := SYSDATE;
639 end if;
640 else
641 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
642 IGS_GE_MSG_STACK.ADD;
643 app_exception.raise_exception;
644 end if;
645 Before_DML(
646 p_action=>'INSERT',
647 x_rowid=>X_ROWID,
648 x_attendance_mode=>X_ATTENDANCE_MODE,
649 x_attendance_type=>X_ATTENDANCE_TYPE,
650 x_chg_rate=>X_CHG_RATE,
651 x_course_cd=>X_COURSE_CD,
652 x_end_dt=>X_END_DT,
653 x_fee_type=>X_FEE_TYPE,
654 x_location_cd=>X_LOCATION_CD,
655 x_lower_nrml_rate_ovrd_ind=>NVL(X_LOWER_NRML_RATE_OVRD_IND,'N'),
656 x_person_id=>X_PERSON_ID,
657 x_start_dt=>X_START_DT,
658 x_creation_date=>X_LAST_UPDATE_DATE,
659 x_created_by=>X_LAST_UPDATED_BY,
660 x_last_update_date=>X_LAST_UPDATE_DATE,
661 x_last_updated_by=>X_LAST_UPDATED_BY,
662 x_last_update_login=>X_LAST_UPDATE_LOGIN
663 );
664 IF (x_mode = 'S') THEN
665 igs_sc_gen_001.set_ctx('R');
666 END IF;
667 insert into IGS_FI_FEE_AS_RT (
668 PERSON_ID,
669 COURSE_CD,
670 FEE_TYPE,
671 START_DT,
672 END_DT,
673 LOCATION_CD,
674 ATTENDANCE_TYPE,
675 ATTENDANCE_MODE,
676 CHG_RATE,
677 LOWER_NRML_RATE_OVRD_IND,
678 CREATION_DATE,
679 CREATED_BY,
680 LAST_UPDATE_DATE,
681 LAST_UPDATED_BY,
682 LAST_UPDATE_LOGIN,
683 REQUEST_ID,
684 PROGRAM_ID,
685 PROGRAM_APPLICATION_ID,
686 PROGRAM_UPDATE_DATE
687 ) values (
688 NEW_REFERENCES.PERSON_ID,
689 NEW_REFERENCES.COURSE_CD,
690 NEW_REFERENCES.FEE_TYPE,
691 NEW_REFERENCES.START_DT,
692 NEW_REFERENCES.END_DT,
693 NEW_REFERENCES.LOCATION_CD,
694 NEW_REFERENCES.ATTENDANCE_TYPE,
695 NEW_REFERENCES.ATTENDANCE_MODE,
696 NEW_REFERENCES.CHG_RATE,
697 NEW_REFERENCES.LOWER_NRML_RATE_OVRD_IND,
698 X_LAST_UPDATE_DATE,
699 X_LAST_UPDATED_BY,
700 X_LAST_UPDATE_DATE,
701 X_LAST_UPDATED_BY,
702 X_LAST_UPDATE_LOGIN,
703 X_REQUEST_ID,
704 X_PROGRAM_ID,
705 X_PROGRAM_APPLICATION_ID,
706 X_PROGRAM_UPDATE_DATE
707 );
708 IF (x_mode = 'S') THEN
709 igs_sc_gen_001.unset_ctx('R');
710 END IF;
711
712 open c;
713 fetch c into X_ROWID;
714 if (c%notfound) then
715 close c;
716 raise no_data_found;
717 end if;
718 close c;
719 After_DML(
720 p_action => 'INSERT',
721 x_rowid => X_ROWID
722 );
723 EXCEPTION
724 WHEN OTHERS THEN
725 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
726 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
727 fnd_message.set_token ('ERR_CD', SQLCODE);
728 igs_ge_msg_stack.add;
729 igs_sc_gen_001.unset_ctx('R');
730 app_exception.raise_exception;
731 ELSE
732 igs_sc_gen_001.unset_ctx('R');
733 RAISE;
734 END IF;
735
736 end INSERT_ROW;
737 procedure LOCK_ROW (
738 X_ROWID in VARCHAR2,
739 X_PERSON_ID in NUMBER,
740 X_COURSE_CD in VARCHAR2,
741 X_FEE_TYPE in VARCHAR2,
742 X_START_DT in DATE,
743 X_END_DT in DATE,
744 X_LOCATION_CD in VARCHAR2,
745 X_ATTENDANCE_TYPE in VARCHAR2,
746 X_ATTENDANCE_MODE in VARCHAR2,
747 X_CHG_RATE in NUMBER,
748 X_LOWER_NRML_RATE_OVRD_IND in VARCHAR2
749 ) as
750 cursor c1 is select
751 END_DT,
752 LOCATION_CD,
753 ATTENDANCE_TYPE,
754 ATTENDANCE_MODE,
755 CHG_RATE,
756 LOWER_NRML_RATE_OVRD_IND
757 from IGS_FI_FEE_AS_RT
758 where ROWID = X_ROWID
759 for update nowait;
760 tlinfo c1%rowtype;
761 begin
762 open c1;
763 fetch c1 into tlinfo;
764 if (c1%notfound) then
765 close c1;
766 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
767 IGS_GE_MSG_STACK.ADD;
768 app_exception.raise_exception;
769 return;
770 end if;
771 close c1;
772 if ( ((tlinfo.END_DT = X_END_DT)
773 OR ((tlinfo.END_DT is null)
774 AND (X_END_DT is null)))
775 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
776 OR ((tlinfo.LOCATION_CD is null)
777 AND (X_LOCATION_CD is null)))
778 AND ((tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
779 OR ((tlinfo.ATTENDANCE_TYPE is null)
780 AND (X_ATTENDANCE_TYPE is null)))
781 AND ((tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
782 OR ((tlinfo.ATTENDANCE_MODE is null)
783 AND (X_ATTENDANCE_MODE is null)))
784 AND (tlinfo.CHG_RATE = X_CHG_RATE)
785 AND (tlinfo.LOWER_NRML_RATE_OVRD_IND = X_LOWER_NRML_RATE_OVRD_IND)
786 ) then
787 null;
788 else
789 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
790 IGS_GE_MSG_STACK.ADD;
791 app_exception.raise_exception;
792 end if;
793 return;
794 end LOCK_ROW;
795 procedure UPDATE_ROW (
796 X_ROWID in VARCHAR2,
797 X_PERSON_ID in NUMBER,
798 X_COURSE_CD in VARCHAR2,
799 X_FEE_TYPE in VARCHAR2,
800 X_START_DT in DATE,
801 X_END_DT in DATE,
802 X_LOCATION_CD in VARCHAR2,
803 X_ATTENDANCE_TYPE in VARCHAR2,
804 X_ATTENDANCE_MODE in VARCHAR2,
805 X_CHG_RATE in NUMBER,
806 X_LOWER_NRML_RATE_OVRD_IND in VARCHAR2,
807 X_MODE in VARCHAR2 default 'R'
808 ) as
809 X_LAST_UPDATE_DATE DATE;
810 X_LAST_UPDATED_BY NUMBER;
811 X_LAST_UPDATE_LOGIN NUMBER;
812 X_REQUEST_ID NUMBER;
813 X_PROGRAM_ID NUMBER;
814 X_PROGRAM_APPLICATION_ID NUMBER;
815 X_PROGRAM_UPDATE_DATE DATE;
816 begin
817 X_LAST_UPDATE_DATE := SYSDATE;
818 if(X_MODE = 'I') then
819 X_LAST_UPDATED_BY := 1;
820 X_LAST_UPDATE_LOGIN := 0;
821 elsif (X_MODE IN ('R', 'S')) then
822 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
823 if X_LAST_UPDATED_BY is NULL then
824 X_LAST_UPDATED_BY := -1;
825 end if;
826 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
827 if X_LAST_UPDATE_LOGIN is NULL then
828 X_LAST_UPDATE_LOGIN := -1;
829 end if;
830 Before_DML(
831 p_action=>'UPDATE',
832 x_rowid=>X_ROWID,
833 x_attendance_mode=>X_ATTENDANCE_MODE,
834 x_attendance_type=>X_ATTENDANCE_TYPE,
835 x_chg_rate=>X_CHG_RATE,
836 x_course_cd=>X_COURSE_CD,
837 x_end_dt=>X_END_DT,
838 x_fee_type=>X_FEE_TYPE,
839 x_location_cd=>X_LOCATION_CD,
840 x_lower_nrml_rate_ovrd_ind=>X_LOWER_NRML_RATE_OVRD_IND,
841 x_person_id=>X_PERSON_ID,
842 x_start_dt=>X_START_DT,
843 x_creation_date=>X_LAST_UPDATE_DATE,
844 x_created_by=>X_LAST_UPDATED_BY,
845 x_last_update_date=>X_LAST_UPDATE_DATE,
846 x_last_updated_by=>X_LAST_UPDATED_BY,
847 x_last_update_login=>X_LAST_UPDATE_LOGIN
848 );
849 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
850 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
851 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
852 if (X_REQUEST_ID = -1) then
853 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
854 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
855 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
856 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
857 else
858 X_PROGRAM_UPDATE_DATE := SYSDATE;
859 end if;
860 else
861 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
862 IGS_GE_MSG_STACK.ADD;
863 app_exception.raise_exception;
864 end if;
865 IF (x_mode = 'S') THEN
866 igs_sc_gen_001.set_ctx('R');
867 END IF;
868 update IGS_FI_FEE_AS_RT set
869 END_DT = NEW_REFERENCES.END_DT,
870 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
871 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
872 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
873 CHG_RATE = NEW_REFERENCES.CHG_RATE,
874 LOWER_NRML_RATE_OVRD_IND = NEW_REFERENCES.LOWER_NRML_RATE_OVRD_IND,
875 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
876 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
877 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
878 REQUEST_ID = X_REQUEST_ID,
879 PROGRAM_ID = X_PROGRAM_ID,
880 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
881 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
882 where ROWID = X_ROWID;
883 if (sql%notfound) then
884 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
885 igs_ge_msg_stack.add;
886 igs_sc_gen_001.unset_ctx('R');
887 app_exception.raise_exception;
888 end if;
889 IF (x_mode = 'S') THEN
890 igs_sc_gen_001.unset_ctx('R');
891 END IF;
892
893 After_DML(
894 p_action => 'UPDATE',
895 x_rowid => X_ROWID
896 );
897 EXCEPTION
898 WHEN OTHERS THEN
899 IF (SQLCODE = (-28115)) THEN
900 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
901 fnd_message.set_token ('ERR_CD', SQLCODE);
902 igs_ge_msg_stack.add;
903 igs_sc_gen_001.unset_ctx('R');
904 app_exception.raise_exception;
905 ELSE
906 igs_sc_gen_001.unset_ctx('R');
907 RAISE;
908 END IF;
909
910 end UPDATE_ROW;
911 procedure ADD_ROW (
912 X_ROWID in OUT NOCOPY VARCHAR2,
913 X_PERSON_ID in NUMBER,
914 X_COURSE_CD in VARCHAR2,
915 X_FEE_TYPE in VARCHAR2,
916 X_START_DT in DATE,
917 X_END_DT in DATE,
918 X_LOCATION_CD in VARCHAR2,
919 X_ATTENDANCE_TYPE in VARCHAR2,
920 X_ATTENDANCE_MODE in VARCHAR2,
921 X_CHG_RATE in NUMBER,
922 X_LOWER_NRML_RATE_OVRD_IND in VARCHAR2,
923 X_MODE in VARCHAR2 default 'R'
924 ) as
925 cursor c1 is select rowid from IGS_FI_FEE_AS_RT
926 where PERSON_ID = X_PERSON_ID
927 and COURSE_CD = X_COURSE_CD
928 and FEE_TYPE = X_FEE_TYPE
929 and START_DT = X_START_DT
930 ;
931 begin
932 open c1;
933 fetch c1 into X_ROWID;
934 if (c1%notfound) then
935 close c1;
936 INSERT_ROW (
937 X_ROWID,
938 X_PERSON_ID,
939 X_COURSE_CD,
940 X_FEE_TYPE,
941 X_START_DT,
942 X_END_DT,
943 X_LOCATION_CD,
944 X_ATTENDANCE_TYPE,
945 X_ATTENDANCE_MODE,
946 X_CHG_RATE,
947 X_LOWER_NRML_RATE_OVRD_IND,
948 X_MODE);
949 return;
950 end if;
951 close c1;
952 UPDATE_ROW (
953 X_ROWID,
954 X_PERSON_ID,
955 X_COURSE_CD,
956 X_FEE_TYPE,
957 X_START_DT,
958 X_END_DT,
959 X_LOCATION_CD,
960 X_ATTENDANCE_TYPE,
961 X_ATTENDANCE_MODE,
962 X_CHG_RATE,
963 X_LOWER_NRML_RATE_OVRD_IND,
964 X_MODE);
965 end ADD_ROW;
966 procedure DELETE_ROW (
967 X_ROWID in VARCHAR2,
968 x_mode IN VARCHAR2
969 ) as
970 begin
971 Before_DML(
972 p_action => 'DELETE',
973 x_rowid => X_ROWID
974 );
975 IF (x_mode = 'S') THEN
976 igs_sc_gen_001.set_ctx('R');
977 END IF;
978 delete from IGS_FI_FEE_AS_RT
979 where ROWID = X_ROWID;
980 if (sql%notfound) then
981 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
982 igs_ge_msg_stack.add;
983 igs_sc_gen_001.unset_ctx('R');
984 app_exception.raise_exception;
985 end if;
986 IF (x_mode = 'S') THEN
987 igs_sc_gen_001.unset_ctx('R');
988 END IF;
989
990 After_DML(
991 p_action => 'DELETE',
992 x_rowid => X_ROWID
993 );
994 END delete_row;
995 END igs_fi_fee_as_rt_pkg;