[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_FEE_TRG_PKG
Source
1 package body IGS_PS_FEE_TRG_PKG AS
2 /* $Header: IGSPI11B.pls 120.1 2005/09/08 15:56:49 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_FEE_TRG%RowType;
6 new_references IGS_PS_FEE_TRG%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_fee_cat IN VARCHAR2 DEFAULT NULL,
12 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
13 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
14 x_fee_type IN VARCHAR2 DEFAULT NULL,
15 x_course_cd IN VARCHAR2 DEFAULT NULL,
16 x_sequence_number IN NUMBER DEFAULT NULL,
17 x_version_number IN NUMBER DEFAULT NULL,
18 x_cal_type IN VARCHAR2 DEFAULT NULL,
19 x_location_cd IN VARCHAR2 DEFAULT NULL,
20 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
21 x_attendance_type IN VARCHAR2 DEFAULT NULL,
22 x_create_dt IN DATE DEFAULT NULL,
23 x_fee_trigger_group_number IN NUMBER DEFAULT NULL,
24 x_logical_delete_dt IN DATE DEFAULT NULL,
25 x_creation_date IN DATE DEFAULT NULL,
26 x_created_by IN NUMBER DEFAULT NULL,
27 x_last_update_date IN DATE DEFAULT NULL,
28 x_last_updated_by IN NUMBER DEFAULT NULL,
29 x_last_update_login IN NUMBER DEFAULT NULL
30 ) AS
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_PS_FEE_TRG
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 Open cur_old_ref_values;
44 Fetch cur_old_ref_values INTO old_references;
45 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
46 Close cur_old_ref_values;
47 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
48 IGS_GE_MSG_STACK.ADD;
49 App_Exception.Raise_Exception;
50 Return;
51 END IF;
52 Close cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.fee_cat := x_fee_cat;
56 new_references.fee_cal_type := x_fee_cal_type;
57 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
58 new_references.fee_type := x_fee_type;
59 new_references.course_cd := x_course_cd;
60 new_references.sequence_number := x_sequence_number;
61 new_references.version_number := x_version_number;
62 new_references.cal_type := x_cal_type;
63 new_references.location_cd := x_location_cd;
64 new_references.attendance_mode := x_attendance_mode;
65 new_references.attendance_type := x_attendance_type;
66 new_references.create_dt := x_create_dt;
67 new_references.fee_trigger_group_number := x_fee_trigger_group_number;
68 new_references.logical_delete_dt := x_logical_delete_dt;
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76 new_references.last_update_date := x_last_update_date;
77 new_references.last_updated_by := x_last_updated_by;
78 new_references.last_update_login := x_last_update_login;
79
80 END Set_Column_Values;
81
82 -- Trigger description :-
83 -- "OSS_TST".trg_cft_br_iud
84 -- BEFORE INSERT OR DELETE OR UPDATE
85 -- ON IGS_PS_FEE_TRG
86 -- FOR EACH ROW
87
88 PROCEDURE BeforeRowInsertUpdateDelete1(
89 p_inserting IN BOOLEAN DEFAULT FALSE,
90 p_updating IN BOOLEAN DEFAULT FALSE,
91 p_deleting IN BOOLEAN DEFAULT FALSE
92 ) AS
93 v_message_name varchar2(30);
94 BEGIN
95 IF p_inserting THEN
96 -- Validate trigger can be defined.
97 IF IGS_FI_VAL_CFT.finp_val_cft_ins (
98 new_references.fee_type,
99 v_message_name) = FALSE THEN
100 Fnd_Message.Set_Name('IGS',v_message_name);
101 IGS_GE_MSG_STACK.ADD;
102 App_Exception.Raise_Exception;
103 END IF;
104 END IF;
105 IF p_inserting OR p_updating THEN
106 -- Validate IGS_AD_LOCATION not closed and of type CAMPUS.
107
108 -- As part of the bug# 1956374 changed to the below call from IGS_FI_VAL_CFT.crsp_val_loc_cd
109 IF IGS_PS_VAL_UOO.crsp_val_loc_cd (
110 new_references.location_cd,
111 v_message_name) = FALSE THEN
112 Fnd_Message.Set_Name('IGS',v_message_name);
113 IGS_GE_MSG_STACK.ADD;
114 App_Exception.Raise_Exception;
115 END IF;
116 -- Validate Attendance Type not closed.
117 --
118 -- bug id : 1956374
119 -- sjadhav, 28-aug-2001
120 -- call changed from igs_fi_val_cft.enrp_val_att_closed
121 -- to igs_en_val_pee.enrp_val_att_closed
122 --
123 IF IGS_EN_VAL_PEE.enrp_val_att_closed (
124 new_references.attendance_type,
125 v_message_name) = FALSE THEN
126 Fnd_Message.Set_Name('IGS',v_message_name);
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END IF;
130 -- Validate Attendance Mode not closed.
131 -- As part of the bug 1956374 changed the following call from
132 -- IGS_FI_VAL_CFT.enrp_val_am_closed
133 IF IGS_FI_VAL_FAR.enrp_val_am_closed (
134 new_references.attendance_mode,
135 v_message_name) = FALSE THEN
136 Fnd_Message.Set_Name('IGS',v_message_name);
137 IGS_GE_MSG_STACK.ADD;
138 App_Exception.Raise_Exception;
139 END IF;
140 -- Validate Calendar Type not closed.
141 IF IGS_FI_VAL_CFT.calp_val_cat_closed (
142 new_references.cal_type,
143 v_message_name) = FALSE THEN
144 Fnd_Message.Set_Name('IGS',v_message_name);
145 IGS_GE_MSG_STACK.ADD;
146 App_Exception.Raise_Exception;
147 END IF;
148 -- Validate Calendar Category is 'ACADEMIC'.
149 IF IGS_FI_VAL_CFT.finp_val_ct_academic (
150 new_references.cal_type,
151 v_message_name) = FALSE THEN
152 Fnd_Message.Set_Name('IGS',v_message_name);
153 IGS_GE_MSG_STACK.ADD;
154 App_Exception.Raise_Exception;
155 END IF;
156 -- Validate IGS_PS_COURSE code.
157 IF IGS_FI_VAL_CFT.finp_val_cft_crs (
158 new_references.course_cd,
159 v_message_name) = FALSE THEN
160 Fnd_Message.Set_Name('IGS',v_message_name);
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception;
163 END IF;
164 END IF;
165
166
167 END BeforeRowInsertUpdateDelete1;
168
169 -- Trigger description :-
170 -- "OSS_TST".trg_cft_ar_iu
171 -- AFTER INSERT OR UPDATE
172 -- ON IGS_PS_FEE_TRG
173 -- FOR EACH ROW
174
175 PROCEDURE AfterRowInsertUpdate2(
176 p_inserting IN BOOLEAN DEFAULT FALSE,
177 p_updating IN BOOLEAN DEFAULT FALSE,
178 p_deleting IN BOOLEAN DEFAULT FALSE
179 ) AS
180 v_message_name varchar2(30);
181 v_rowid_saved BOOLEAN := FALSE;
182 BEGIN
183 -- Validate for open IGS_PS_COURSE Fee Trig records.
184 IF (new_references.logical_delete_dt IS NULL) THEN
185 -- Cannot call finp_val_cft_open because trigger will be mutating.
186 -- Save the rowid of the current row for after statement processing.
187 v_rowid_saved := TRUE;
188 END IF;
189 -- Validate Fee Trigger Group.
190 IF (new_references.logical_delete_dt IS NULL AND
191 new_references.fee_trigger_group_number IS NOT NULL) THEN
192 -- Cannot call finp_val_cft_ftg because trigger will be mutating
193 IF (v_rowid_saved = FALSE) THEN
194 -- Save the rowid of the current row for after statement
195 -- processing.
196 v_rowid_saved := TRUE;
197 END IF;
198 END IF;
199
200 IF v_rowid_saved = TRUE Then
201
202 IF (p_inserting OR p_updating) THEN
203 -- Validate for open ended IGS_PS_FEE_TRG records.
204 IF new_references.logical_delete_dt IS NULL THEN
205 IF IGS_FI_VAL_CFT.finp_val_cft_open(new_references.fee_cat,
206 new_references.fee_cal_type,
207 new_references.fee_ci_sequence_number,
208 new_references.fee_type,
209 new_references.course_cd,
210 new_references.sequence_number,
211 new_references.version_number,
212 new_references.cal_type,
213 new_references.location_cd,
214 new_references.attendance_mode,
215 new_references.attendance_type,
216 new_references.create_dt,
217 new_references.fee_trigger_group_number,
218 v_message_name) = FALSE THEN
219 Fnd_Message.Set_Name('IGS',v_message_name);
220 IGS_GE_MSG_STACK.ADD;
221 App_Exception.Raise_Exception;
222 END IF;
223 END IF;
224 -- Validate IGS_PS_FEE_TRG fee trigger group.
225 IF new_references.fee_trigger_group_number IS NOT NULL THEN
226 IF IGS_FI_VAL_CFT.finp_val_cft_ftg(new_references.fee_cat,
227 new_references.fee_cal_type,
228 new_references.fee_ci_sequence_number,
229 new_references.fee_type,
230 new_references.course_cd,
231 new_references.fee_trigger_group_number,
232 v_message_name) = FALSE THEN
233 Fnd_Message.Set_Name('IGS',v_message_name);
234 IGS_GE_MSG_STACK.ADD;
235 App_Exception.Raise_Exception;
236 END IF;
237 END IF;
238 END IF;
239 END IF;
240
241
242 END AfterRowInsertUpdate2;
243
244 -- Trigger description :-
245 -- "OSS_TST".trg_cft_ar_u_hist
246 -- AFTER UPDATE
247 -- ON IGS_PS_FEE_TRG
248 -- FOR EACH ROW
249
250 PROCEDURE AfterRowUpdate3(
251 p_inserting IN BOOLEAN DEFAULT FALSE,
252 p_updating IN BOOLEAN DEFAULT FALSE,
253 p_deleting IN BOOLEAN DEFAULT FALSE
254 ) AS
255 BEGIN
256 -- create a history
257 IGS_FI_GEN_002.FINP_INS_CFT_HIST(old_references.fee_cat,
258 old_references.fee_cal_type,
259 old_references.fee_ci_sequence_number,
260 old_references.fee_type,
261 old_references.course_cd,
262 old_references.sequence_number,
263 new_references.version_number,
264 old_references.version_number,
265 new_references.cal_type,
266 old_references.cal_type,
267 new_references.location_cd,
268 old_references.location_cd,
269 new_references.attendance_mode,
270 old_references.attendance_mode,
271 new_references.attendance_type,
272 old_references.attendance_type,
273 new_references.create_dt,
274 old_references.create_dt,
275 new_references.fee_trigger_group_number,
276 old_references.fee_trigger_group_number,
277 new_references.last_updated_by,
278 old_references.last_updated_by,
279 new_references.last_update_date,
280 old_references.last_update_date);
281
282
283 END AfterRowUpdate3;
284
285 PROCEDURE Check_Constraints (
286 Column_Name IN VARCHAR2 DEFAULT NULL,
287 Column_Value IN VARCHAR2 DEFAULT NULL
288 ) AS
289 /*----------------------------------------------------------------------------
290 || Created By :
291 || Created On :
292 || Purpose :
293 || Known limitations, enhancements or remarks :
294 || Change History :
295 || Who When What
296 || (reverse chronological order - newest change first)
297 || vvutukur 19-May-2002 removed upper check constraint on fee_cat,fee_type columns.bug#2344826.
298 ----------------------------------------------------------------------------*/
299 BEGIN
300
301 IF column_name is null then
302 NULL;
303 ELSIF upper(Column_name) = 'ATTENDANCE_MODE' then
304 new_references.attendance_mode := column_value;
305 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' then
306 new_references.attendance_type := column_value;
307 ELSIF upper(Column_name) = 'CAL_TYPE' then
308 new_references.cal_type := column_value;
309 ELSIF upper(Column_name) = 'COURSE_CD' then
310 new_references.course_cd := column_value;
311 ELSIF upper(Column_name) = 'FEE_CAL_TYPE' then
312 new_references.fee_cal_type := column_value;
313 ELSIF upper(Column_name) = 'LOCATION_CD' then
314 new_references.location_cd := column_value;
315 ELSIF upper(Column_name) = 'FEE_TRIGGER_GROUP_NUMBER' then
316 new_references.fee_trigger_group_number := igs_ge_number.to_num(column_value);
317 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' then
318 new_references.sequence_number := igs_ge_number.to_num(column_value);
319 ELSIF upper(Column_name) = 'VERSION_NUMBER' then
320 new_references.version_number := igs_ge_number.to_num(column_value);
321 ELSIF upper(Column_name) = 'FEE_CI_SEQUENCE_NUMBER' then
322 new_references.fee_ci_sequence_number := igs_ge_number.to_num(column_value);
323 END IF;
324
325 IF upper(column_name) = 'ATTENDANCE_MODE' OR
326 column_name is null Then
327 IF ( new_references.attendance_mode <> UPPER(new_references.attendance_mode) ) Then
328 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
329 IGS_GE_MSG_STACK.ADD;
330 App_Exception.Raise_Exception;
331 END IF;
332 END IF;
333
334 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
335 column_name is null Then
336 IF ( new_references.attendance_type <> UPPER(new_references.attendance_type) ) Then
337 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
338 IGS_GE_MSG_STACK.ADD;
339 App_Exception.Raise_Exception;
340 END IF;
341 END IF;
342
343 IF upper(column_name) = 'CAL_TYPE' OR
344 column_name is null Then
345 IF ( new_references.cal_type <> UPPER(new_references.cal_type) ) Then
346 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
347 IGS_GE_MSG_STACK.ADD;
348 App_Exception.Raise_Exception;
349 END IF;
350 END IF;
351
352 IF upper(column_name) = 'COURSE_CD' OR
353 column_name is null Then
354 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) Then
355 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
356 IGS_GE_MSG_STACK.ADD;
357 App_Exception.Raise_Exception;
358 END IF;
359 END IF;
360
361 IF upper(column_name) = 'FEE_CAL_TYPE' OR
362 column_name is null Then
363 IF ( new_references.fee_cal_type <> UPPER(new_references.fee_cal_type) ) Then
364 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
365 IGS_GE_MSG_STACK.ADD;
366 App_Exception.Raise_Exception;
367 END IF;
368 END IF;
369
370 IF upper(column_name) = 'LOCATION_CD' OR
371 column_name is null Then
372 IF ( new_references.location_cd <> UPPER(new_references.location_cd) ) Then
373 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
374 IGS_GE_MSG_STACK.ADD;
375 App_Exception.Raise_Exception;
376 END IF;
377 END IF;
378
379 IF upper(column_name) = 'SEQUENCE_NUMBER' OR
380 column_name is null Then
381 IF ( new_references.sequence_number < 1 OR new_references.sequence_number > 999999 ) Then
382 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
383 IGS_GE_MSG_STACK.ADD;
384 App_Exception.Raise_Exception;
385 END IF;
386 END IF;
387
388 IF upper(column_name) = 'FEE_TRIGGER_GROUP_NUMBER' OR
389 column_name is null Then
390 IF ( new_references.fee_trigger_group_number < 1 OR new_references.fee_trigger_group_number > 999999 ) Then
391 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
392 IGS_GE_MSG_STACK.ADD;
393 App_Exception.Raise_Exception;
394 END IF;
395 END IF;
396
397 IF upper(column_name) = 'VERSION_NUMBER' OR
398 column_name is null Then
399 IF ( new_references.version_number < 1 OR new_references.version_number > 999 ) Then
400 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
401 IGS_GE_MSG_STACK.ADD;
402 App_Exception.Raise_Exception;
403 END IF;
404 END IF;
405
406 IF upper(column_name) = 'FEE_CI_SEQUENCE_NUMBER' OR
407 column_name is null Then
408 IF ( new_references.fee_ci_sequence_number < 1 OR new_references.fee_ci_sequence_number > 999999 ) Then
409 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
410 IGS_GE_MSG_STACK.ADD;
411 App_Exception.Raise_Exception;
412 END IF;
413 END IF;
414
415 END Check_Constraints;
416
417 PROCEDURE Check_Uniqueness AS
418 BEGIN
419
420 IF Get_UK_For_Validation (
421 new_references.fee_cat ,
422 new_references.fee_cal_type,
423 new_references.fee_ci_sequence_number,
424 new_references.fee_type,
425 new_references.course_cd,
426 new_references.version_number,
427 new_references.CAL_TYPE,
428 new_references.LOCATION_CD,
429 new_references.ATTENDANCE_MODE,
430 new_references.ATTENDANCE_TYPE,
431 new_references.CREATE_DT) THEN
432 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
433 IGS_GE_MSG_STACK.ADD;
434 App_Exception.Raise_Exception;
435 END IF;
436 END Check_Uniqueness ;
437
438 PROCEDURE Check_Parent_Existance AS
439 BEGIN
440
441 IF (((old_references.attendance_mode = new_references.attendance_mode)) OR
442 ((new_references.attendance_mode IS NULL))) THEN
443 NULL;
444 ELSE
445 IF NOT IGS_EN_ATD_MODE_PKG.Get_PK_For_Validation (
446 new_references.attendance_mode ) THEN
447 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
448 IGS_GE_MSG_STACK.ADD;
449 App_Exception.Raise_Exception;
450 END IF;
451 END IF;
452
453 IF (((old_references.attendance_type = new_references.attendance_type)) OR
454 ((new_references.attendance_type IS NULL))) THEN
455 NULL;
456 ELSE
457 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
458 new_references.attendance_type ) THEN
459 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
460 IGS_GE_MSG_STACK.ADD;
461 App_Exception.Raise_Exception;
462 END IF;
463 END IF;
464
465 IF (((old_references.cal_type = new_references.cal_type)) OR
466 ((new_references.cal_type IS NULL))) THEN
467 NULL;
468 ELSE
469 IF NOT IGS_CA_TYPE_PKG.Get_PK_For_Validation (
470 new_references.cal_type ) THEN
471 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END IF;
475 END IF;
476
477 IF (((old_references.course_cd = new_references.course_cd)) OR
478 ((new_references.course_cd IS NULL))) THEN
479 NULL;
480 ELSE
481 IF NOT IGS_PS_COURSE_PKG.Get_PK_For_Validation (
482 new_references.course_cd ) THEN
483 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
484 IGS_GE_MSG_STACK.ADD;
485 App_Exception.Raise_Exception;
486 END IF;
487 END IF;
488
489 IF (((old_references.fee_cat = new_references.fee_cat) AND
490 (old_references.fee_cal_type = new_references.fee_cal_type) AND
491 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
492 (old_references.fee_type = new_references.fee_type)) OR
493 ((new_references.fee_cat IS NULL) OR
494 (new_references.fee_cal_type IS NULL) OR
495 (new_references.fee_ci_sequence_number IS NULL) OR
496 (new_references.fee_type IS NULL))) THEN
497 NULL;
498 ELSE
499 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
500 new_references.fee_cat,
501 new_references.fee_cal_type,
502 new_references.fee_ci_sequence_number,
503 new_references.fee_type ) THEN
504 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
505 IGS_GE_MSG_STACK.ADD;
506 App_Exception.Raise_Exception;
507 END IF;
508 END IF;
509
510 IF (((old_references.fee_cat = new_references.fee_cat) AND
511 (old_references.fee_cal_type = new_references.fee_cal_type) AND
512 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
513 (old_references.fee_type = new_references.fee_type) AND
514 (old_references.fee_trigger_group_number = new_references.fee_trigger_group_number)) OR
515 ((new_references.fee_cat IS NULL) OR
516 (new_references.fee_cal_type IS NULL) OR
517 (new_references.fee_ci_sequence_number IS NULL) OR
518 (new_references.fee_type IS NULL) OR
519 (new_references.fee_trigger_group_number IS NULL))) THEN
520 NULL;
521 ELSE
522 IF NOT IGS_FI_FEE_TRG_GRP_PKG.Get_PK_For_Validation (
523 new_references.fee_cat,
524 new_references.fee_cal_type,
525 new_references.fee_ci_sequence_number,
526 new_references.fee_type,
527 new_references.fee_trigger_group_number ) THEN
528 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
529 IGS_GE_MSG_STACK.ADD;
530 App_Exception.Raise_Exception;
531 END IF;
532 END IF;
533
534 IF (((old_references.location_cd = new_references.location_cd)) OR
535 ((new_references.location_cd IS NULL))) THEN
536 NULL;
537 ELSE
538 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
539 new_references.location_cd,
540 'N' ) THEN
541 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
542 IGS_GE_MSG_STACK.ADD;
543 App_Exception.Raise_Exception;
544 END IF;
545 END IF;
546
547 END Check_Parent_Existance;
548
549 FUNCTION Get_PK_For_Validation (
550 x_fee_cat IN VARCHAR2,
551 x_fee_cal_type IN VARCHAR2,
552 x_fee_ci_sequence_number IN NUMBER,
553 x_fee_type IN VARCHAR2,
554 x_course_cd IN VARCHAR2,
555 x_sequence_number IN NUMBER
556 )
557 RETURN BOOLEAN AS
558
559 CURSOR cur_rowid IS
560 SELECT rowid
561 FROM IGS_PS_FEE_TRG
562 WHERE fee_cat = x_fee_cat
563 AND fee_cal_type = x_fee_cal_type
564 AND fee_ci_sequence_number = x_fee_ci_sequence_number
565 AND fee_type = x_fee_type
566 AND course_cd = x_course_cd
567 AND sequence_number = x_sequence_number
568 FOR UPDATE NOWAIT;
569
570 lv_rowid cur_rowid%RowType;
571
572 BEGIN
573
574 Open cur_rowid;
575 Fetch cur_rowid INTO lv_rowid;
576 IF (cur_rowid%FOUND) THEN
577 Close cur_rowid;
578 Return (TRUE);
579 ELSE
580 Close cur_rowid;
581 Return (FALSE);
582 END IF;
583
584 END Get_PK_For_Validation;
585
586 FUNCTION Get_UK_For_Validation (
587 x_fee_cat IN VARCHAR2,
588 x_fee_cal_type IN VARCHAR2,
589 x_fee_ci_sequence_number IN NUMBER,
590 x_fee_type IN VARCHAR2,
591 x_course_cd IN VARCHAR2,
592 x_version_number IN NUMBER,
593 X_CAL_TYPE in VARCHAR2,
594 X_LOCATION_CD in VARCHAR2,
595 X_ATTENDANCE_MODE in VARCHAR2,
596 X_ATTENDANCE_TYPE in VARCHAR2,
597 X_CREATE_DT in DATE )
598 RETURN BOOLEAN AS
599
600 CURSOR cur_rowid IS
601 SELECT rowid
602 FROM IGS_PS_FEE_TRG
603 WHERE fee_cat = x_fee_cat
604 AND fee_cal_type = x_fee_cal_type
605 AND fee_ci_sequence_number = x_fee_ci_sequence_number
606 AND fee_type = x_fee_type
607 AND course_cd = x_course_cd
608 AND version_number = x_version_number
609 AND cal_type = x_cal_type
610 AND location_cd = x_location_cd
611 AND attendance_mode = x_attendance_mode
612 AND attendance_type = x_attendance_type
613 AND create_dt = x_create_dt
614 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
615
616 FOR UPDATE NOWAIT;
617
618 lv_rowid cur_rowid%RowType;
619
620 BEGIN
621
622 Open cur_rowid;
623 Fetch cur_rowid INTO lv_rowid;
624 IF (cur_rowid%FOUND) THEN
625 Close cur_rowid;
626 Return (TRUE);
627 ELSE
628 Close cur_rowid;
629 Return (FALSE);
630 END IF;
631
632 END Get_UK_For_Validation;
633
634 PROCEDURE GET_FK_IGS_EN_ATD_MODE (
635 x_attendance_mode IN VARCHAR2
636 ) AS
637
638 CURSOR cur_rowid IS
639 SELECT rowid
640 FROM IGS_PS_FEE_TRG
641 WHERE attendance_mode = x_attendance_mode ;
642
643 lv_rowid cur_rowid%RowType;
644
645 BEGIN
646
647 Open cur_rowid;
648 Fetch cur_rowid INTO lv_rowid;
649 IF (cur_rowid%FOUND) THEN
650 Close cur_rowid;
651 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CFT_AM_FK');
652 IGS_GE_MSG_STACK.ADD;
653 App_Exception.Raise_Exception;
654 Return;
655 END IF;
656 Close cur_rowid;
657
658 END GET_FK_IGS_EN_ATD_MODE;
659
660 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
661 x_attendance_type IN VARCHAR2
662 ) AS
663
664 CURSOR cur_rowid IS
665 SELECT rowid
666 FROM IGS_PS_FEE_TRG
667 WHERE attendance_type = x_attendance_type ;
668
669 lv_rowid cur_rowid%RowType;
670
671 BEGIN
672
673 Open cur_rowid;
674 Fetch cur_rowid INTO lv_rowid;
675 IF (cur_rowid%FOUND) THEN
676 Close cur_rowid;
677 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CFT_ATT_FK');
678 IGS_GE_MSG_STACK.ADD;
679 App_Exception.Raise_Exception;
680 Return;
681 END IF;
682 Close cur_rowid;
683
684 END GET_FK_IGS_EN_ATD_TYPE;
685
686 PROCEDURE GET_FK_IGS_CA_TYPE (
687 x_cal_type IN VARCHAR2
688 ) AS
689
690 CURSOR cur_rowid IS
691 SELECT rowid
692 FROM IGS_PS_FEE_TRG
693 WHERE cal_type = x_cal_type ;
694
695 lv_rowid cur_rowid%RowType;
696
697 BEGIN
698
699 Open cur_rowid;
700 Fetch cur_rowid INTO lv_rowid;
701 IF (cur_rowid%FOUND) THEN
702 Close cur_rowid;
703 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CFT_CAT_FK');
704 IGS_GE_MSG_STACK.ADD;
705 App_Exception.Raise_Exception;
706 Return;
707 END IF;
708 Close cur_rowid;
709
710 END GET_FK_IGS_CA_TYPE;
711
712 PROCEDURE GET_FK_IGS_PS_COURSE (
713 x_course_cd IN VARCHAR2
714 ) AS
715
716 CURSOR cur_rowid IS
717 SELECT rowid
718 FROM IGS_PS_FEE_TRG
719 WHERE course_cd = x_course_cd ;
720
721 lv_rowid cur_rowid%RowType;
722
723 BEGIN
724
725 Open cur_rowid;
726 Fetch cur_rowid INTO lv_rowid;
727 IF (cur_rowid%FOUND) THEN
728 Close cur_rowid;
729 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CFT_CRS_FK');
730 IGS_GE_MSG_STACK.ADD;
731 App_Exception.Raise_Exception;
732 Return;
733 END IF;
734 Close cur_rowid;
735
736 END GET_FK_IGS_PS_COURSE;
737
738 PROCEDURE GET_FK_IGS_FI_FEE_TRG_GRP (
739 x_fee_cat IN VARCHAR2,
740 x_fee_cal_type IN VARCHAR2,
741 x_fee_ci_sequence_number IN NUMBER,
742 x_fee_type IN VARCHAR2,
743 x_fee_trigger_group_number IN NUMBER
744 ) AS
745
746 CURSOR cur_rowid IS
747 SELECT rowid
748 FROM IGS_PS_FEE_TRG
749 WHERE fee_cat = x_fee_cat
750 AND fee_cal_type = x_fee_cal_type
751 AND fee_ci_sequence_number = x_fee_ci_sequence_number
752 AND fee_type = x_fee_type
753 AND fee_trigger_group_number = x_fee_trigger_group_number ;
754
755
756 lv_rowid cur_rowid%RowType;
757
758 BEGIN
759
760 Open cur_rowid;
761 Fetch cur_rowid INTO lv_rowid;
762 IF (cur_rowid%FOUND) THEN
763 Close cur_rowid;
764 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CFT_FTG_FK');
765 IGS_GE_MSG_STACK.ADD;
766 App_Exception.Raise_Exception;
767 Return;
768 END IF;
769 Close cur_rowid;
770
771 END GET_FK_IGS_FI_FEE_TRG_GRP;
772
773 PROCEDURE GET_FK_IGS_AD_LOCATION (
774 x_location_cd IN VARCHAR2
775 ) AS
776
777 CURSOR cur_rowid IS
778 SELECT rowid
779 FROM IGS_PS_FEE_TRG
780 WHERE location_cd = x_location_cd ;
781
782 lv_rowid cur_rowid%RowType;
783
784 BEGIN
785
786 Open cur_rowid;
787 Fetch cur_rowid INTO lv_rowid;
788 IF (cur_rowid%FOUND) THEN
789 Close cur_rowid;
790 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CFT_LOC_FK');
791 IGS_GE_MSG_STACK.ADD;
792 App_Exception.Raise_Exception;
793 Return;
794 END IF;
795 Close cur_rowid;
796
797 END GET_FK_IGS_AD_LOCATION;
798
799 PROCEDURE Before_DML (
800 p_action IN VARCHAR2,
801 x_rowid IN VARCHAR2 DEFAULT NULL,
802 x_fee_cat IN VARCHAR2 DEFAULT NULL,
803 x_fee_cal_type IN VARCHAR2 DEFAULT NULL,
804 x_fee_ci_sequence_number IN NUMBER DEFAULT NULL,
805 x_fee_type IN VARCHAR2 DEFAULT NULL,
806 x_course_cd IN VARCHAR2 DEFAULT NULL,
807 x_sequence_number IN NUMBER DEFAULT NULL,
808 x_version_number IN NUMBER DEFAULT NULL,
809 x_cal_type IN VARCHAR2 DEFAULT NULL,
810 x_location_cd IN VARCHAR2 DEFAULT NULL,
811 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
812 x_attendance_type IN VARCHAR2 DEFAULT NULL,
813 x_create_dt IN DATE DEFAULT NULL,
814 x_fee_trigger_group_number IN NUMBER DEFAULT NULL,
815 x_logical_delete_dt IN DATE DEFAULT NULL,
816 x_creation_date IN DATE DEFAULT NULL,
817 x_created_by IN NUMBER DEFAULT NULL,
818 x_last_update_date IN DATE DEFAULT NULL,
819 x_last_updated_by IN NUMBER DEFAULT NULL,
820 x_last_update_login IN NUMBER DEFAULT NULL
821 ) AS
822 BEGIN
823
824 Set_Column_Values (
825 p_action,
826 x_rowid,
827 x_fee_cat,
828 x_fee_cal_type,
829 x_fee_ci_sequence_number,
830 x_fee_type,
831 x_course_cd,
832 x_sequence_number,
833 x_version_number,
834 x_cal_type,
835 x_location_cd,
836 x_attendance_mode,
837 x_attendance_type,
838 x_create_dt,
839 x_fee_trigger_group_number,
840 x_logical_delete_dt,
841 x_creation_date,
842 x_created_by,
843 x_last_update_date,
844 x_last_updated_by,
845 x_last_update_login
846 );
847
848 IF (p_action = 'INSERT') THEN
849 -- Call all the procedures related to Before Insert.
850 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
851 IF Get_PK_For_Validation (
852 new_references.fee_cat,
853 new_references.fee_cal_type,
854 new_references.fee_ci_sequence_number,
855 new_references.fee_type,
856 new_references.course_cd,
857 new_references.sequence_number) THEN
858 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
859 IGS_GE_MSG_STACK.ADD;
860 App_Exception.Raise_Exception;
861 END IF;
862 Check_Constraints;
863 Check_Parent_Existance;
864 Check_Uniqueness;
865 ELSIF (p_action = 'UPDATE') THEN
866 -- Call all the procedures related to Before Update.
867 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
868 Check_Constraints;
869 Check_Uniqueness;
870 Check_Parent_Existance;
871 ELSIF (p_action = 'DELETE') THEN
872 -- Call all the procedures related to Before Delete.
873 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
874 ELSIF (p_action = 'VALIDATE_INSERT') THEN
875 IF Get_PK_For_Validation (
876 new_references.fee_cat,
877 new_references.fee_cal_type,
878 new_references.fee_ci_sequence_number,
879 new_references.fee_type,
880 new_references.course_cd,
881 new_references.sequence_number) THEN
882 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
883 IGS_GE_MSG_STACK.ADD;
884 App_Exception.Raise_Exception;
885 END IF;
886 Check_Constraints;
887 Check_Uniqueness;
888 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
889 Check_Constraints;
890 Check_Uniqueness;
891 END IF;
892
893 END Before_DML;
894
895 PROCEDURE After_DML (
896 p_action IN VARCHAR2,
897 x_rowid IN VARCHAR2
898 ) AS
899 BEGIN
900
901 l_rowid := x_rowid;
902
903 IF (p_action = 'INSERT') THEN
904 -- Call all the procedures related to After Insert.
905 AfterRowInsertUpdate2 ( p_inserting => TRUE );
906 ELSIF (p_action = 'UPDATE') THEN
907 -- Call all the procedures related to After Update.
908 AfterRowInsertUpdate2 ( p_updating => TRUE );
909 AfterRowUpdate3 ( p_updating => TRUE );
910 END IF;
911 l_rowid := null;
912 END After_DML;
913
914 procedure INSERT_ROW (
915 X_ROWID in out NOCOPY VARCHAR2,
916 X_FEE_CAT in VARCHAR2,
917 X_FEE_TYPE in VARCHAR2,
918 X_COURSE_CD in VARCHAR2,
919 X_SEQUENCE_NUMBER in NUMBER,
920 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
921 X_FEE_CAL_TYPE in VARCHAR2,
922 X_VERSION_NUMBER in NUMBER,
923 X_CAL_TYPE in VARCHAR2,
924 X_LOCATION_CD in VARCHAR2,
925 X_ATTENDANCE_MODE in VARCHAR2,
926 X_ATTENDANCE_TYPE in VARCHAR2,
927 X_CREATE_DT in DATE,
928 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
929 X_LOGICAL_DELETE_DT in DATE,
930 X_MODE in VARCHAR2 default 'R'
931 ) AS
932 cursor C is select ROWID from IGS_PS_FEE_TRG
933 where FEE_CAT = X_FEE_CAT
934 and FEE_TYPE = X_FEE_TYPE
935 and COURSE_CD = X_COURSE_CD
936 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
937 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
938 and FEE_CAL_TYPE = X_FEE_CAL_TYPE;
939 X_LAST_UPDATE_DATE DATE;
940 X_LAST_UPDATED_BY NUMBER;
941 X_LAST_UPDATE_LOGIN NUMBER;
942 X_REQUEST_ID NUMBER;
943 X_PROGRAM_ID NUMBER;
944 X_PROGRAM_APPLICATION_ID NUMBER;
945 X_PROGRAM_UPDATE_DATE DATE;
946 begin
947 X_LAST_UPDATE_DATE := SYSDATE;
948 if(X_MODE = 'I') then
949 X_LAST_UPDATED_BY := 1;
950 X_LAST_UPDATE_LOGIN := 0;
951 elsif (X_MODE = 'R') then
952 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
953 if X_LAST_UPDATED_BY is NULL then
954 X_LAST_UPDATED_BY := -1;
955 end if;
956 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
957 if X_LAST_UPDATE_LOGIN is NULL then
958 X_LAST_UPDATE_LOGIN := -1;
959 end if;
960
961 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
962 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
963 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
964 IF (X_REQUEST_ID = -1) THEN
965 X_REQUEST_ID := NULL;
966 X_PROGRAM_ID := NULL;
967 X_PROGRAM_APPLICATION_ID := NULL;
968 X_PROGRAM_UPDATE_DATE := NULL;
969 ELSE
970 X_PROGRAM_UPDATE_DATE := SYSDATE;
971 END IF;
972
973 else
974 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
975 IGS_GE_MSG_STACK.ADD;
976 app_exception.raise_exception;
977 end if;
978
979 Before_DML (
980 p_action => 'INSERT',
981 x_rowid => X_ROWID,
982 x_fee_cat => X_FEE_CAT,
983 x_fee_cal_type => X_FEE_CAL_TYPE,
984 x_fee_ci_sequence_number => X_FEE_CI_SEQUENCE_NUMBER,
985 x_fee_type => X_FEE_TYPE,
986 x_course_cd => X_COURSE_CD,
987 x_sequence_number => X_SEQUENCE_NUMBER,
988 x_version_number => X_VERSION_NUMBER ,
989 x_cal_type => X_CAL_TYPE ,
990 x_location_cd => X_LOCATION_CD ,
991 x_attendance_mode => X_ATTENDANCE_MODE ,
992 x_attendance_type => X_ATTENDANCE_TYPE ,
993 x_create_dt => NVL(X_CREATE_DT,SYSDATE) ,
994 x_fee_trigger_group_number => X_FEE_TRIGGER_GROUP_NUMBER ,
995 x_logical_delete_dt => X_LOGICAL_DELETE_DT,
996 x_creation_date => X_LAST_UPDATE_DATE ,
997 x_created_by => X_LAST_UPDATED_BY ,
998 x_last_update_date => X_LAST_UPDATE_DATE ,
999 x_last_updated_by => X_LAST_UPDATED_BY ,
1000 x_last_update_login => X_LAST_UPDATE_LOGIN
1001 );
1002
1003 insert into IGS_PS_FEE_TRG (
1004 FEE_CAT,
1005 FEE_CAL_TYPE,
1006 FEE_CI_SEQUENCE_NUMBER,
1007 FEE_TYPE,
1008 COURSE_CD,
1009 SEQUENCE_NUMBER,
1010 VERSION_NUMBER,
1011 CAL_TYPE,
1012 LOCATION_CD,
1013 ATTENDANCE_MODE,
1014 ATTENDANCE_TYPE,
1015 CREATE_DT,
1016 FEE_TRIGGER_GROUP_NUMBER,
1017 LOGICAL_DELETE_DT,
1018 CREATION_DATE,
1019 CREATED_BY,
1020 LAST_UPDATE_DATE,
1021 LAST_UPDATED_BY,
1022 LAST_UPDATE_LOGIN,
1023 REQUEST_ID,
1024 PROGRAM_ID,
1025 PROGRAM_APPLICATION_ID,
1026 PROGRAM_UPDATE_DATE
1027 ) values (
1028 NEW_REFERENCES.FEE_CAT,
1029 NEW_REFERENCES.FEE_CAL_TYPE,
1030 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
1031 NEW_REFERENCES.FEE_TYPE,
1032 NEW_REFERENCES.COURSE_CD,
1033 NEW_REFERENCES.SEQUENCE_NUMBER,
1034 NEW_REFERENCES.VERSION_NUMBER,
1035 NEW_REFERENCES.CAL_TYPE,
1036 NEW_REFERENCES.LOCATION_CD,
1037 NEW_REFERENCES.ATTENDANCE_MODE,
1038 NEW_REFERENCES.ATTENDANCE_TYPE,
1039 NEW_REFERENCES.CREATE_DT,
1040 NEW_REFERENCES.FEE_TRIGGER_GROUP_NUMBER,
1041 NEW_REFERENCES.LOGICAL_DELETE_DT,
1042 X_LAST_UPDATE_DATE,
1043 X_LAST_UPDATED_BY,
1044 X_LAST_UPDATE_DATE,
1045 X_LAST_UPDATED_BY,
1046 X_LAST_UPDATE_LOGIN,
1047 X_REQUEST_ID,
1048 X_PROGRAM_ID,
1049 X_PROGRAM_APPLICATION_ID,
1050 X_PROGRAM_UPDATE_DATE
1051 );
1052
1053 open c;
1054 fetch c into X_ROWID;
1055 if (c%notfound) then
1056 close c;
1057 raise no_data_found;
1058 end if;
1059 After_DML (
1060 p_action => 'INSERT',
1061 x_rowid => X_ROWID
1062 );
1063
1064 close c;
1065
1066 end INSERT_ROW;
1067
1068 procedure LOCK_ROW (
1069 X_ROWID IN VARCHAR2,
1070 X_FEE_CAT in VARCHAR2,
1071 X_FEE_TYPE in VARCHAR2,
1072 X_COURSE_CD in VARCHAR2,
1073 X_SEQUENCE_NUMBER in NUMBER,
1074 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
1075 X_FEE_CAL_TYPE in VARCHAR2,
1076 X_VERSION_NUMBER in NUMBER,
1077 X_CAL_TYPE in VARCHAR2,
1078 X_LOCATION_CD in VARCHAR2,
1079 X_ATTENDANCE_MODE in VARCHAR2,
1080 X_ATTENDANCE_TYPE in VARCHAR2,
1081 X_CREATE_DT in DATE,
1082 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
1083 X_LOGICAL_DELETE_DT in DATE
1084 ) AS
1085 cursor c1 is select
1086 VERSION_NUMBER,
1087 CAL_TYPE,
1088 LOCATION_CD,
1089 ATTENDANCE_MODE,
1090 ATTENDANCE_TYPE,
1091 CREATE_DT,
1092 FEE_TRIGGER_GROUP_NUMBER,
1093 LOGICAL_DELETE_DT
1094 from IGS_PS_FEE_TRG
1095 where ROWID = X_ROWID
1096 for update nowait;
1097 tlinfo c1%rowtype;
1098
1099 begin
1100 open c1;
1101 fetch c1 into tlinfo;
1102 if (c1%notfound) then
1103 close c1;
1104 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1105 IGS_GE_MSG_STACK.ADD;
1106 app_exception.raise_exception;
1107 return;
1108 end if;
1109 close c1;
1110
1111 if ( ((tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
1112 OR ((tlinfo.VERSION_NUMBER is null)
1113 AND (X_VERSION_NUMBER is null)))
1114 AND ((tlinfo.CAL_TYPE = X_CAL_TYPE)
1115 OR ((tlinfo.CAL_TYPE is null)
1116 AND (X_CAL_TYPE is null)))
1117 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
1118 OR ((tlinfo.LOCATION_CD is null)
1119 AND (X_LOCATION_CD is null)))
1120 AND ((tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
1121 OR ((tlinfo.ATTENDANCE_MODE is null)
1122 AND (X_ATTENDANCE_MODE is null)))
1123 AND ((tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
1124 OR ((tlinfo.ATTENDANCE_TYPE is null)
1125 AND (X_ATTENDANCE_TYPE is null)))
1126 AND (tlinfo.CREATE_DT = X_CREATE_DT)
1127 AND ((tlinfo.FEE_TRIGGER_GROUP_NUMBER = X_FEE_TRIGGER_GROUP_NUMBER)
1128 OR ((tlinfo.FEE_TRIGGER_GROUP_NUMBER is null)
1129 AND (X_FEE_TRIGGER_GROUP_NUMBER is null)))
1130 AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
1131 OR ((tlinfo.LOGICAL_DELETE_DT is null)
1132 AND (X_LOGICAL_DELETE_DT is null)))
1133 ) then
1134 null;
1135 else
1136 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1137 IGS_GE_MSG_STACK.ADD;
1138 app_exception.raise_exception;
1139 end if;
1140 return;
1141 end LOCK_ROW;
1142
1143 procedure UPDATE_ROW (
1144 X_ROWID IN VARCHAR2,
1145 X_FEE_CAT in VARCHAR2,
1146 X_FEE_TYPE in VARCHAR2,
1147 X_COURSE_CD in VARCHAR2,
1148 X_SEQUENCE_NUMBER in NUMBER,
1149 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
1150 X_FEE_CAL_TYPE in VARCHAR2,
1151 X_VERSION_NUMBER in NUMBER,
1152 X_CAL_TYPE in VARCHAR2,
1153 X_LOCATION_CD in VARCHAR2,
1154 X_ATTENDANCE_MODE in VARCHAR2,
1155 X_ATTENDANCE_TYPE in VARCHAR2,
1156 X_CREATE_DT in DATE,
1157 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
1158 X_LOGICAL_DELETE_DT in DATE,
1159 X_MODE in VARCHAR2 default 'R'
1160 ) AS
1161 X_LAST_UPDATE_DATE DATE;
1162 X_LAST_UPDATED_BY NUMBER;
1163 X_LAST_UPDATE_LOGIN NUMBER;
1164 X_REQUEST_ID NUMBER;
1165 X_PROGRAM_ID NUMBER;
1166 X_PROGRAM_APPLICATION_ID NUMBER;
1167 X_PROGRAM_UPDATE_DATE DATE;
1168
1169 begin
1170 X_LAST_UPDATE_DATE := SYSDATE;
1171 if(X_MODE = 'I') then
1172 X_LAST_UPDATED_BY := 1;
1173 X_LAST_UPDATE_LOGIN := 0;
1174 elsif (X_MODE = 'R') then
1175 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1176 if X_LAST_UPDATED_BY is NULL then
1177 X_LAST_UPDATED_BY := -1;
1178 end if;
1179 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1180 if X_LAST_UPDATE_LOGIN is NULL then
1181 X_LAST_UPDATE_LOGIN := -1;
1182 end if;
1183 else
1184 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1185 IGS_GE_MSG_STACK.ADD;
1186 app_exception.raise_exception;
1187 end if;
1188
1189 Before_DML (
1190 p_action => 'UPDATE',
1191 x_rowid => X_ROWID,
1192 x_fee_cat => X_FEE_CAT,
1193 x_fee_cal_type => X_FEE_CAL_TYPE,
1194 x_fee_ci_sequence_number => X_FEE_CI_SEQUENCE_NUMBER,
1195 x_fee_type => X_FEE_TYPE,
1196 x_course_cd => X_COURSE_CD,
1197 x_sequence_number => X_SEQUENCE_NUMBER,
1198 x_version_number => X_VERSION_NUMBER ,
1199 x_cal_type => X_CAL_TYPE ,
1200 x_location_cd => X_LOCATION_CD ,
1201 x_attendance_mode => X_ATTENDANCE_MODE ,
1202 x_attendance_type => X_ATTENDANCE_TYPE ,
1203 x_create_dt => X_CREATE_DT ,
1204 x_fee_trigger_group_number => X_FEE_TRIGGER_GROUP_NUMBER ,
1205 x_logical_delete_dt => X_LOGICAL_DELETE_DT,
1206 x_creation_date => X_LAST_UPDATE_DATE ,
1207 x_created_by => X_LAST_UPDATED_BY ,
1208 x_last_update_date => X_LAST_UPDATE_DATE ,
1209 x_last_updated_by => X_LAST_UPDATED_BY ,
1210 x_last_update_login => X_LAST_UPDATE_LOGIN
1211 );
1212
1213 if (X_MODE = 'R') then
1214 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1215 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1216 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1217 if (X_REQUEST_ID = -1) then
1218 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1219 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1220 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1221 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1222 else
1223 X_PROGRAM_UPDATE_DATE := SYSDATE;
1224 end if;
1225 end if;
1226
1227 update IGS_PS_FEE_TRG set
1228 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
1229 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
1230 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
1231 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
1232 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
1233 CREATE_DT = NEW_REFERENCES.CREATE_DT,
1234 FEE_TRIGGER_GROUP_NUMBER = NEW_REFERENCES.FEE_TRIGGER_GROUP_NUMBER,
1235 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
1236 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1237 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1238 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1239 REQUEST_ID = X_REQUEST_ID,
1240 PROGRAM_ID = X_PROGRAM_ID,
1241 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1242 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1243 where ROWID = X_ROWID
1244 ;
1245 if (sql%notfound) then
1246 raise no_data_found;
1247 end if;
1248 After_DML (
1249 p_action => 'UPDATE',
1250 x_rowid => X_ROWID
1251 );
1252 end UPDATE_ROW;
1253
1254 procedure ADD_ROW (
1255 X_ROWID in out NOCOPY VARCHAR2,
1256 X_FEE_CAT in VARCHAR2,
1257 X_FEE_TYPE in VARCHAR2,
1258 X_COURSE_CD in VARCHAR2,
1259 X_SEQUENCE_NUMBER in NUMBER,
1260 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
1261 X_FEE_CAL_TYPE in VARCHAR2,
1262 X_VERSION_NUMBER in NUMBER,
1263 X_CAL_TYPE in VARCHAR2,
1264 X_LOCATION_CD in VARCHAR2,
1265 X_ATTENDANCE_MODE in VARCHAR2,
1266 X_ATTENDANCE_TYPE in VARCHAR2,
1267 X_CREATE_DT in DATE,
1268 X_FEE_TRIGGER_GROUP_NUMBER in NUMBER,
1269 X_LOGICAL_DELETE_DT in DATE,
1270 X_MODE in VARCHAR2 default 'R'
1271 ) AS
1272 cursor c1 is select rowid from IGS_PS_FEE_TRG
1273 where FEE_CAT = X_FEE_CAT
1274 and FEE_TYPE = X_FEE_TYPE
1275 and COURSE_CD = X_COURSE_CD
1276 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1277 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
1278 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
1279 ;
1280 begin
1281 open c1;
1282 fetch c1 into X_ROWID;
1283 if (c1%notfound) then
1284 close c1;
1285 INSERT_ROW (
1286 X_ROWID,
1287 X_FEE_CAT,
1288 X_FEE_TYPE,
1289 X_COURSE_CD,
1290 X_SEQUENCE_NUMBER,
1291 X_FEE_CI_SEQUENCE_NUMBER,
1292 X_FEE_CAL_TYPE,
1293 X_VERSION_NUMBER,
1294 X_CAL_TYPE,
1295 X_LOCATION_CD,
1296 X_ATTENDANCE_MODE,
1297 X_ATTENDANCE_TYPE,
1298 X_CREATE_DT,
1299 X_FEE_TRIGGER_GROUP_NUMBER,
1300 X_LOGICAL_DELETE_DT,
1301 X_MODE);
1302 return;
1303 end if;
1304 close c1;
1305 UPDATE_ROW (
1306 X_ROWID,
1307 X_FEE_CAT,
1308 X_FEE_TYPE,
1309 X_COURSE_CD,
1310 X_SEQUENCE_NUMBER,
1311 X_FEE_CI_SEQUENCE_NUMBER,
1312 X_FEE_CAL_TYPE,
1313 X_VERSION_NUMBER,
1314 X_CAL_TYPE,
1315 X_LOCATION_CD,
1316 X_ATTENDANCE_MODE,
1317 X_ATTENDANCE_TYPE,
1318 X_CREATE_DT,
1319 X_FEE_TRIGGER_GROUP_NUMBER,
1320 X_LOGICAL_DELETE_DT,
1321 X_MODE);
1322 end ADD_ROW;
1323
1324 procedure DELETE_ROW (
1325 X_ROWID in VARCHAR2
1326 ) AS
1327 begin
1328 Before_DML (
1329 p_action => 'DELETE',
1330 x_rowid => X_ROWID
1331 );
1332 delete from IGS_PS_FEE_TRG
1333 where ROWID = X_ROWID;
1334 if (sql%notfound) then
1335 raise no_data_found;
1336 end if;
1337 After_DML (
1338 p_action => 'DELETE',
1339 x_rowid => X_ROWID
1340 );
1341 end DELETE_ROW;
1342
1343 end IGS_PS_FEE_TRG_PKG;