1 PACKAGE BODY igs_fi_fee_as_rate_pkg AS
2 /* $Header: IGSSI68B.pls 120.4 2006/05/26 10:57:37 sapanigr ship $*/
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_FI_FEE_AS_RATE%RowType;
6 new_references IGS_FI_FEE_AS_RATE%RowType;
7 -- Forward declaring the private procedure to ensure compiling of the package
8 PROCEDURE beforerowupdate;
9
10 PROCEDURE Set_Column_Values (
11 p_action IN VARCHAR2,
12 x_rowid IN VARCHAR2 ,
13 x_far_id IN NUMBER ,
14 x_fee_type IN VARCHAR2 ,
15 x_fee_cal_type IN VARCHAR2 ,
16 x_fee_ci_sequence_number IN NUMBER ,
17 x_s_relation_type IN VARCHAR2 ,
18 x_rate_number IN NUMBER ,
19 x_fee_cat IN VARCHAR2 ,
20 x_location_cd IN VARCHAR2 ,
21 x_attendance_type IN VARCHAR2 ,
22 x_attendance_mode IN VARCHAR2 ,
23 x_order_of_precedence IN NUMBER ,
24 x_govt_hecs_payment_option IN VARCHAR2 ,
25 x_govt_hecs_cntrbtn_band IN NUMBER ,
26 x_chg_rate IN NUMBER ,
27 x_logical_delete_dt IN DATE ,
28 x_residency_status_cd IN VARCHAR2 ,
29 x_course_cd IN VARCHAR2 ,
30 x_version_number IN NUMBER ,
31 x_org_party_id IN NUMBER ,
32 x_class_standing IN VARCHAR2 ,
33 x_creation_date IN DATE ,
34 x_created_by IN NUMBER ,
35 x_last_update_date IN DATE ,
36 x_last_updated_by IN NUMBER ,
37 x_last_update_login IN NUMBER,
38 x_unit_set_cd IN VARCHAR2,
39 x_us_version_number IN NUMBER,
40 x_unit_cd IN VARCHAR2 ,
41 x_unit_version_number IN NUMBER ,
42 x_unit_level IN VARCHAR2 ,
43 x_unit_type_id IN NUMBER ,
44 x_unit_class IN VARCHAR2 ,
45 x_unit_mode IN VARCHAR2
46
47 ) AS
48 /*-----------------------------------------------------------------
49 || Created By :
50 || Created On :
51 || Purpose :
52 || Known limitations, enhancements or remarks :
53 || Change History :
54 || Who When What
55 || (reverse chronological order - newest change first)
56 || svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
57 || Unit Version and Unit Level
58 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
59 || Added 2 new columns unit_set_cd and us_version_number
60 --------------------------------------------------------------------*/
61 CURSOR cur_old_ref_values IS
62 SELECT *
63 FROM IGS_FI_FEE_AS_RATE
64 WHERE rowid = x_rowid;
65
66 BEGIN
67 l_rowid := x_rowid;
68 -- Code for setting the Old and New Reference Values.
69 -- Populate Old Values.
70 OPEN cur_old_ref_values;
71 FETCH cur_old_ref_values INTO old_references;
72 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
73 CLOSE cur_old_ref_values;
74 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
75 igs_ge_msg_stack.add;
76 app_exception.raise_exception;
77 RETURN;
78 END IF;
79 CLOSE cur_old_ref_values;
80
81 -- Populate New Values.
82 new_references.far_id := x_far_id;
83 new_references.fee_type := x_fee_type;
84 new_references.fee_cal_type := x_fee_cal_type;
85 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
86 new_references.s_relation_type := x_s_relation_type;
87 new_references.rate_number := x_rate_number;
88 new_references.fee_cat := x_fee_cat;
89 new_references.location_cd := x_location_cd;
90 new_references.attendance_type := x_attendance_type;
91 new_references.attendance_mode := x_attendance_mode;
92 new_references.order_of_precedence := x_order_of_precedence;
93 new_references.govt_hecs_payment_option := x_govt_hecs_payment_option;
94 new_references.govt_hecs_cntrbtn_band := x_govt_hecs_cntrbtn_band;
95 new_references.chg_rate := x_chg_rate;
96 new_references.logical_delete_dt := x_logical_delete_dt;
97 new_references.residency_status_cd := x_residency_status_cd;
98 new_references.course_cd := x_course_cd;
99 new_references.version_number := x_version_number;
100 new_references.org_party_id := x_org_party_id;
101 new_references.class_standing := x_class_standing;
102 new_references.unit_set_cd := x_unit_set_cd;
103 new_references.us_version_number := x_us_version_number;
104 new_references.unit_cd := x_unit_cd ;
105 new_references.unit_version_number := x_unit_version_number ;
106 new_references.unit_level := x_unit_level ;
107 new_references.unit_type_id := x_unit_type_id ;
108 new_references.unit_class := x_unit_class ;
109 new_references.unit_mode := x_unit_mode ;
110
111
112 IF (p_action = 'UPDATE') THEN
113 new_references.creation_date := old_references.creation_date;
114 new_references.created_by := old_references.created_by;
115 ELSE
116 new_references.creation_date := x_creation_date;
117 new_references.created_by := x_created_by;
118 END IF;
119 new_references.last_update_date := x_last_update_date;
120 new_references.last_updated_by := x_last_updated_by;
121 new_references.last_update_login := x_last_update_login;
122
123 END set_column_values;
124
125
126 -- Trigger description
127 -- before insert or delete or update on IGS_FI_FEE_AS_RATE for each row
128 PROCEDURE BeforeRowInsertUpdateDelete1(
129 p_inserting IN BOOLEAN ,
130 p_updating IN BOOLEAN ,
131 p_deleting IN BOOLEAN
132 ) AS
133 /*-----------------------------------------------------------------
134 || Created By :
135 || Created On :
136 || Purpose :
137 || Known limitations, enhancements or remarks :
138 || Change History :
139 || Who When What
140 || (reverse chronological order - newest change first)
141 --------------------------------------------------------------------*/
142 v_message_name varchar2(30);
143 BEGIN
144 -- Validate Fee Assessment Rate can be created.
145 IF p_inserting THEN
146 -- If IGS_FI_FEE_TYPE.s_fee_trigger_cat = 'INSTITUTN' or
147 -- IGS_FI_FEE_TYPE.s_fee_type = 'HECS', then schedules can only
148 -- be defined against FTCI's.
149 IF new_references.s_relation_type <> 'FTCI' THEN
150 IF IGS_FI_VAL_FAR.finp_val_far_ins (
151 new_references.fee_type,
152 v_message_name) = FALSE THEN
153 Fnd_Message.Set_Name('IGS',v_message_name);
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END IF;
157 END IF;
158 END IF;
159 -- Validate that appropriate fields are set depending on the fee type.
160 IF p_inserting OR p_updating THEN
161 IF IGS_FI_VAL_FAR.finp_val_far_defntn (
162 new_references.fee_type,
163 new_references.location_cd,
164 new_references.attendance_type,
165 new_references.attendance_mode,
166 new_references.govt_hecs_payment_option,
167 new_references.govt_hecs_cntrbtn_band,
168 v_message_name) = FALSE THEN
169 Fnd_Message.Set_Name('IGS',v_message_name);
170 IGS_GE_MSG_STACK.ADD;
171 App_Exception.Raise_Exception;
172 END IF;
173 END IF;
174 -- Validate fee category is only set when the relation type = 'FCFL'.
175 IF p_inserting OR p_updating THEN
176 IF IGS_FI_VAL_FAR.finp_val_far_rltn (
177 new_references.s_relation_type,
178 new_references.fee_cat,
179 v_message_name) = FALSE THEN
180 Fnd_Message.Set_Name('IGS',v_message_name);
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 END IF;
185 -- Validate closed indicators.
186 IF p_inserting OR p_updating THEN
187 -- Validate for closed location.
188 IF IGS_FI_VAL_FAR.crsp_val_loc_cd (
189 new_references.location_cd,
190 v_message_name) = FALSE THEN
191 Fnd_Message.Set_Name('IGS',v_message_name);
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception;
194 END IF;
195 -- Validate for closed attendance type.
196 -- change igs_fi_val_far.enrp_val_att_closed
197 -- to igs_en_val_pee.enrp_val_att_closed
198 --
199 IF IGS_EN_VAL_PEE.enrp_val_att_closed (
200 new_references.attendance_type,
201 v_message_name) = FALSE THEN
202 Fnd_Message.Set_Name('IGS',v_message_name);
203 IGS_GE_MSG_STACK.ADD;
204 App_Exception.Raise_Exception;
205 END IF;
206 -- Validate for closed attendance mode.
207 IF IGS_FI_VAL_FAR.enrp_val_am_closed (
208 new_references.attendance_mode,
209 v_message_name) = FALSE THEN
210 Fnd_Message.Set_Name('IGS',v_message_name);
211 IGS_GE_MSG_STACK.ADD;
212 App_Exception.Raise_Exception;
213 END IF;
214 END IF;
215 END BeforeRowInsertUpdateDelete1;
216
217
218 -- Trigger description :-
219 -- AFTER UPDATE ON IGS_FI_FEE_AS_RATE FOR EACH ROW
220 PROCEDURE AfterRowUpdate3(
221 p_inserting IN BOOLEAN ,
222 p_updating IN BOOLEAN ,
223 p_deleting IN BOOLEAN
224 ) AS
225 /*-----------------------------------------------------------------
226 || Created By :
227 || Created On :
228 || Purpose :
229 || Known limitations, enhancements or remarks :
230 || Change History :
231 || Who When What
232 || (reverse chronological order - newest change first)
233 || svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
234 || Unit Version and Unit Level
235 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
236 || Added 2 new columns unit_set_cd and us_version_number
237 || in call to finp_ins_far_hist
238 --------------------------------------------------------------------*/
239 BEGIN
240 -- create a history
241 igs_fi_gen_002.finp_ins_far_hist(old_references.fee_type,
242 old_references.fee_cal_type,
243 old_references.fee_ci_sequence_number,
244 old_references.s_relation_type,
245 old_references.rate_number,
246 new_references.fee_cat,
247 old_references.fee_cat,
248 new_references.location_cd,
249 old_references.location_cd,
250 new_references.attendance_type,
251 old_references.attendance_type,
252 new_references.attendance_mode,
253 old_references.attendance_mode,
254 new_references.order_of_precedence,
255 old_references.order_of_precedence,
256 new_references.govt_hecs_payment_option,
257 old_references.govt_hecs_payment_option,
258 new_references.govt_hecs_cntrbtn_band,
259 old_references.govt_hecs_cntrbtn_band,
260 new_references.chg_rate,
261 old_references.chg_rate,
262 new_references.unit_class ,
263 old_references.unit_class ,
264 new_references.residency_status_cd,
265 old_references.residency_status_cd,
266 new_references.course_cd,
267 old_references.course_cd,
268 new_references.version_number,
269 old_references.version_number,
270 new_references.org_party_id,
271 old_references.org_party_id,
272 new_references.class_standing,
273 old_references.class_standing,
274 new_references.last_updated_by,
275 old_references.last_updated_by,
276 new_references.last_update_date,
277 old_references.last_update_date,
278 new_references.unit_set_cd,
279 old_references.unit_set_cd,
280 new_references.us_version_number,
281 old_references.us_version_number,
282 new_references.unit_cd ,
283 old_references.unit_cd ,
284 new_references.unit_version_number,
285 old_references.unit_version_number,
286 new_references.unit_level ,
287 old_references.unit_level ,
288 new_references.unit_type_id,
289 old_references.unit_type_id,
290 new_references.unit_mode ,
291 old_references.unit_mode
292 );
293
294 END AfterRowUpdate3;
295
296
297 -- Trigger description :-
298 -- AFTER INSERT OR UPDATE ON IGS_FI_FEE_AS_RATE
299 PROCEDURE AfterStmtInsertUpdate4(
300 p_inserting IN BOOLEAN ,
301 p_updating IN BOOLEAN ,
302 p_deleting IN BOOLEAN
303 ) AS
304 /*-----------------------------------------------------------------
305 || Created By :
306 || Created On :
307 || Purpose :
308 || Known limitations, enhancements or remarks :
309 || Change History :
310 || Who When What
311 || (reverse chronological order - newest change first)
312 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
313 || Modified call to finp_val_far_unique - Added 2
314 || new columns unit_set_cd and us_version_number
315 --------------------------------------------------------------------*/
316 v_message_name varchar2(30);
317 v_message_string VARCHAR2(512);
318 BEGIN
319 -- Validate if fee_ass_rate can be created and if so, then
320 -- validate that it is unique and the order of precedence.
321 IF p_inserting OR p_updating THEN
322 IF IGS_FI_VAL_FAR.finp_val_far_create(new_references.fee_type,
323 new_references.fee_cal_type,
324 new_references.fee_ci_sequence_number,
325 new_references.s_relation_type,
326 v_message_name) = FALSE THEN
327 Fnd_Message.Set_Name('IGS',v_message_name);
328 IGS_GE_MSG_STACK.ADD;
329 App_Exception.Raise_Exception;
330 END IF;
331 IF igs_fi_val_far.finp_val_far_unique(new_references.fee_type,
332 new_references.fee_cal_type,
333 new_references.fee_ci_sequence_number,
334 new_references.s_relation_type,
335 new_references.rate_number,
336 new_references.fee_cat,
337 new_references.location_cd,
338 new_references.attendance_type,
339 new_references.attendance_mode,
340 new_references.govt_hecs_payment_option,
341 new_references.govt_hecs_cntrbtn_band,
342 new_references.chg_rate,
343 new_references.unit_class,
344 new_references.residency_status_cd,
345 new_references.course_cd,
346 new_references.version_number,
347 new_references.org_party_id,
348 new_references.class_standing,
349 v_message_name,
350 new_references.unit_set_cd,
351 new_references.us_version_number,
352 new_references.unit_cd ,
353 new_references.unit_version_number,
354 new_references.unit_level ,
355 new_references.unit_type_id,
356 new_references.unit_mode
357 ) = FALSE THEN
358 Fnd_Message.Set_Name('IGS',v_message_name);
359 IGS_GE_MSG_STACK.ADD;
360 App_Exception.Raise_Exception;
361 END IF;
362 IF IGS_FI_VAL_FAR.finp_val_far_order(new_references.fee_type,
363 new_references.fee_cal_type,
364 new_references.fee_ci_sequence_number,
365 new_references.s_relation_type,
366 new_references.rate_number,
367 new_references.fee_cat,
368 new_references.location_cd,
369 new_references.attendance_type,
370 new_references.attendance_mode,
371 new_references.govt_hecs_payment_option,
372 new_references.govt_hecs_cntrbtn_band,
373 new_references.order_of_precedence,
374 v_message_name) = FALSE THEN
375 Fnd_Message.Set_Name('IGS',v_message_name);
376 IGS_GE_MSG_STACK.ADD;
377 App_Exception.Raise_Exception;
378 END IF;
379 END IF;
380 END AfterStmtInsertUpdate4;
381
382
383 PROCEDURE Check_Constraints (
384 column_name IN VARCHAR2 ,
385 column_value IN VARCHAR2
386 ) AS
387 /*----------------------------------------------------------------------------
388 || Created By :
389 || Created On :
390 || Purpose :
391 || Known limitations, enhancements or remarks :
392 || Change History :
393 || Who When What
394 || (reverse chronological order - newest change first)
395 || sapanigr 26-May-2006 Enh 5217319. Removed highest value criteria for item 'CHG_RATE'
396 || svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
397 || Unit Version and Unit Level
398 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
399 || Added 2 new columns unit_set_cd and us_version_number
400 || vvutukur 21-Apr-2003 Bug#2885575.Changed the upper limit check to 999999999 for fields rate_number and order_of_precedence.
401 || SYKRISHn 10APR03 ORDER_OF_PRECEDENCE - Changes limit check to 9999
402 || vvutukur 12-May-2002 removed upper check constraint on fee category,fee type columns.bug#2344826.
403 ----------------------------------------------------------------------------*/
404 BEGIN
405 IF (column_name IS NULL) THEN
406 NULL;
407 ELSIF (UPPER (column_name) = 'ATTENDANCE_MODE') THEN
408 new_references.attendance_mode := column_value;
409 ELSIF (UPPER (column_name) = 'ATTENDANCE_TYPE') THEN
410 new_references.attendance_type := column_value;
411 ELSIF (UPPER (column_name) = 'FEE_CAL_TYPE') THEN
412 new_references.fee_cal_type := column_value;
413 ELSIF (UPPER (column_name) = 'GOVT_HECS_PAYMENT_OPTION') THEN
414 new_references.govt_hecs_payment_option := column_value;
415 ELSIF (UPPER (column_name) = 'LOCATION_CD') THEN
416 new_references.location_cd := column_value;
417 ELSIF (UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') THEN
418 new_references.fee_ci_sequence_number := igs_ge_number.To_Num (column_value);
419 ELSIF (UPPER (column_name) = 'S_RELATION_TYPE') THEN
420 new_references.s_relation_type := column_value;
421 ELSIF (UPPER (column_name) = 'RATE_NUMBER') THEN
422 new_references.rate_number := igs_ge_number.To_Num (column_value);
423 ELSIF (UPPER (column_name) = 'ORDER_OF_PRECEDENCE') THEN
424 new_references.order_of_precedence := igs_ge_number.To_Num (column_value);
425 ELSIF (UPPER (column_name) = 'CHG_RATE') THEN
426 new_references.chg_rate := igs_ge_number.to_num (column_value);
427 ELSIF (UPPER (column_name) = 'COURSE_CD') THEN
428 new_references.course_cd := column_value;
429 ELSIF (UPPER (column_name) = 'VERSION_NUMBER') THEN
430 new_references.version_number := igs_ge_number.to_num (column_value);
431 ELSIF (UPPER (column_name) = 'CLASS_STANDING') THEN
432 new_references.class_standing := column_value;
433 ELSIF (UPPER(column_name) = 'UNIT_SET_CD') THEN
434 new_references.unit_set_cd := column_value;
435 ELSIF (UPPER(column_name) = 'UNIT_VERSION_NUMBER') THEN
436 new_references.unit_version_number := igs_ge_number.to_num(column_value);
437 ELSIF (UPPER(column_name) = 'US_VERSION_NUMBER') THEN
438 new_references.us_version_number := igs_ge_number.to_num(column_value);
439 ELSIF (UPPER(column_name) = 'UNIT_TYPE_ID') THEN
440 new_references.unit_type_id := igs_ge_number.to_num(column_value);
441 ELSIF (UPPER (column_name) = 'UNIT_CD') THEN
442 new_references.unit_cd := column_value;
443 ELSIF (UPPER (column_name) = 'UNIT_LEVEL') THEN
444 new_references.unit_level := column_value;
445 ELSIF (UPPER (column_name) = 'UNIT_CLASS') THEN
446 new_references.unit_class := column_value;
447 ELSIF (UPPER(column_name) = 'UNIT_MODE') THEN
448 new_references.unit_mode := column_value;
449 END IF;
450
451 IF ((UPPER (column_name) = 'ATTENDANCE_MODE') OR (column_name IS NULL)) THEN
452 IF (new_references.attendance_mode <> UPPER (new_references.attendance_mode)) THEN
453 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
454 IGS_GE_MSG_STACK.ADD;
455 App_Exception.Raise_Exception;
456 END IF;
457 END IF;
458 IF ((UPPER (column_name) = 'ATTENDANCE_TYPE') OR (column_name IS NULL)) THEN
459 IF (new_references.attendance_type <> UPPER (new_references.attendance_type)) THEN
460 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
461 IGS_GE_MSG_STACK.ADD;
462 App_Exception.Raise_Exception;
463 END IF;
464 END IF;
465 IF ((UPPER (column_name) = 'FEE_CAL_TYPE') OR (column_name IS NULL)) THEN
466 IF (new_references.fee_cal_type <> UPPER (new_references.fee_cal_type)) THEN
467 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
468 IGS_GE_MSG_STACK.ADD;
469 App_Exception.Raise_Exception;
470 END IF;
471 END IF;
472 IF ((UPPER (column_name) = 'GOVT_HECS_PAYMENT_OPTION') OR (column_name IS NULL)) THEN
473 IF (new_references.govt_hecs_payment_option <> UPPER (new_references.govt_hecs_payment_option)) THEN
474 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
475 IGS_GE_MSG_STACK.ADD;
476 App_Exception.Raise_Exception;
477 END IF;
478 END IF;
479 IF ((UPPER (column_name) = 'LOCATION_CD') OR (column_name IS NULL)) THEN
480 IF (new_references.location_cd <> UPPER (new_references.location_cd)) THEN
481 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
482 IGS_GE_MSG_STACK.ADD;
483 App_Exception.Raise_Exception;
484 END IF;
485 END IF;
486 IF ((UPPER (column_name) = 'FEE_CI_SEQUENCE_NUMBER') OR (column_name IS NULL)) THEN
487 IF ((new_references.fee_ci_sequence_number < 1) OR (new_references.fee_ci_sequence_number > 999999)) THEN
488 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
489 IGS_GE_MSG_STACK.ADD;
490 App_Exception.Raise_Exception;
491 END IF;
492 END IF;
493 IF ((UPPER (column_name) = 'S_RELATION_TYPE') OR (column_name IS NULL)) THEN
494 IF (new_references.s_relation_type NOT IN ('FTCI', 'FCFL')) THEN
495 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
496 IGS_GE_MSG_STACK.ADD;
497 App_Exception.Raise_Exception;
498 END IF;
499 END IF;
500 IF ((UPPER (column_name) = 'RATE_NUMBER') OR (column_name IS NULL)) THEN
501 IF ((new_references.rate_number < 1) OR (new_references.rate_number > 999999999)) THEN
502 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
503 IGS_GE_MSG_STACK.ADD;
504 App_Exception.Raise_Exception;
505 END IF;
506 END IF;
507 IF ((UPPER (column_name) = 'ORDER_OF_PRECEDENCE') OR (column_name IS NULL)) THEN
508 IF ((new_references.order_of_precedence < 0) OR (new_references.order_of_precedence > 999999999)) THEN
509 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
510 IGS_GE_MSG_STACK.ADD;
511 App_Exception.Raise_Exception;
512 END IF;
513 END IF;
514 IF ((UPPER (column_name) = 'CHG_RATE') OR (column_name IS NULL)) THEN
515 IF (new_references.chg_rate < 0) THEN
516 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
517 IGS_GE_MSG_STACK.ADD;
518 App_Exception.Raise_Exception;
519 END IF;
520 END IF;
521 IF ((UPPER (column_name) = 'COURSE_CD') OR (column_name IS NULL)) THEN
522 IF (new_references.course_cd <> UPPER (new_references.course_cd)) THEN
523 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
524 IGS_GE_MSG_STACK.ADD;
525 App_Exception.Raise_Exception;
526 END IF;
527 END IF;
528 IF ((UPPER (column_name) = 'VERSION_NUMBER') OR (column_name IS NULL)) THEN
529 IF ((new_references.version_number < 0) OR (new_references.version_number > 999)) THEN
530 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
531 IGS_GE_MSG_STACK.ADD;
532 App_Exception.Raise_Exception;
533 END IF;
534 END IF;
535 IF ((UPPER (column_name) = 'CLASS_STANDING') OR (column_name IS NULL)) THEN
536 IF (new_references.class_standing <> UPPER (new_references.class_standing)) THEN
537 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
538 IGS_GE_MSG_STACK.ADD;
539 App_Exception.Raise_Exception;
540 END IF;
541 END IF;
542 IF ((UPPER(column_name) = 'UNIT_SET_CD') OR (column_name IS NULL)) THEN
543 IF (new_references.unit_set_cd <> UPPER(new_references.unit_set_cd)) THEN
544 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
545 igs_ge_msg_stack.add;
546 app_exception.raise_exception;
547 END IF;
548 END IF;
549 IF ((UPPER(column_name) = 'US_VERSION_NUMBER') OR (column_name IS NULL)) THEN
550 IF ((new_references.us_version_number < 0) OR (new_references.us_version_number > 999)) THEN
551 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
552 igs_ge_msg_stack.add;
553 app_exception.raise_exception;
554 END IF;
555 END IF;
556 IF ((UPPER(column_name) = 'UNIT_VERSION_NUMBER') OR (column_name IS NULL)) THEN
557 IF ((new_references.unit_version_number < 0) OR (new_references.unit_version_number > 999)) THEN
558 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
559 igs_ge_msg_stack.add;
560 app_exception.raise_exception;
561 END IF;
562 END IF;
563 IF ((UPPER(column_name) = 'UNIT_TYPE_ID') OR (column_name IS NULL)) THEN
564 IF (new_references.unit_type_id < 0) THEN
565 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
566 igs_ge_msg_stack.add;
567 app_exception.raise_exception;
568 END IF;
569 END IF;
570 IF ((UPPER (column_name) = 'UNIT_CD') OR (column_name IS NULL)) THEN
571 IF (new_references.unit_cd <> UPPER (new_references.unit_cd)) THEN
572 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
573 IGS_GE_MSG_STACK.ADD;
574 App_Exception.Raise_Exception;
575 END IF;
576 END IF;
577 IF ((UPPER(column_name) = 'UNIT_LEVEL') OR (column_name IS NULL)) THEN
578 IF (new_references.unit_level <> UPPER(new_references.unit_level)) THEN
579 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
580 igs_ge_msg_stack.add;
581 app_exception.raise_exception;
582 END IF;
583 END IF;
584 IF ((UPPER (column_name) = 'UNIT_CLASS') OR (column_name IS NULL)) THEN
585 IF (new_references.unit_class <> UPPER (new_references.unit_class)) THEN
586 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
587 IGS_GE_MSG_STACK.ADD;
588 App_Exception.Raise_Exception;
589 END IF;
590 END IF;
591 IF ((UPPER(column_name) = 'UNIT_MODE') OR (column_name IS NULL)) THEN
592 IF (new_references.unit_mode <> UPPER(new_references.unit_mode)) THEN
593 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
594 igs_ge_msg_stack.add;
595 app_exception.raise_exception;
596 END IF;
597 END IF;
598
599 END Check_Constraints;
600
601
602 PROCEDURE Check_Uniqueness AS
603 /*-----------------------------------------------------------------
604 || Created By :
605 || Created On :
606 || Purpose :
607 || Known limitations, enhancements or remarks :
608 || Change History :
609 || Who When What
610 || (reverse chronological order - newest change first)
611 --------------------------------------------------------------------*/
612 BEGIN
613 IF (Get_UK1_For_Validation (
614 new_references.fee_type,
615 new_references.fee_cal_type,
616 new_references.fee_ci_sequence_number,
617 new_references.rate_number,
618 new_references.fee_cat
619 )) THEN
620 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
621 IGS_GE_MSG_STACK.ADD;
622 App_Exception.Raise_Exception;
623 END IF;
624 IF (Get_UK2_For_Validation (
625 new_references.fee_type,
626 new_references.fee_cal_type,
627 new_references.fee_ci_sequence_number,
628 new_references.s_relation_type,
629 new_references.rate_number,
630 new_references.fee_cat
631 )) THEN
632 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
633 IGS_GE_MSG_STACK.ADD;
634 App_Exception.Raise_Exception;
635 END IF;
636 END Check_Uniqueness;
637
638
639 PROCEDURE Check_Child_Existance AS
640 /*-----------------------------------------------------------------
641 || Created By :
642 || Created On :
643 || Purpose :
644 || Known limitations, enhancements or remarks :
645 || Change History :
646 || Who When What
647 || (reverse chronological order - newest change first)
648 || svuppala 23-JUN-2005 Bug 3392088 Modifications as part of CPF build
649 --------------------------------------------------------------------*/
650 BEGIN
651 igs_fi_elm_range_rt_pkg.get_ufk_igs_fi_fee_as_rate (
652 new_references.fee_type,
653 new_references.fee_cal_type,
654 new_references.fee_ci_sequence_number,
655 new_references.s_relation_type,
656 new_references.rate_number,
657 new_references.fee_cat
658 );
659
660 -- checking child
661 igs_fi_sub_er_rt_pkg.get_fk_igs_fi_fee_as_rate (
662 new_references.far_id
663 );
664 END Check_Child_Existance;
665
666
667 PROCEDURE Check_UK_Child_Existance AS
668 /*-----------------------------------------------------------------
669 || Created By :
670 || Created On :
671 || Purpose :
672 || Known limitations, enhancements or remarks :
673 || Change History :
674 || Who When What
675 || (reverse chronological order - newest change first)
676 --------------------------------------------------------------------*/
677 BEGIN
678 IF (((old_references.fee_type = new_references.fee_type) AND
679 (old_references.fee_cal_type = new_references.fee_cal_type) AND
680 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
681 (old_references.s_relation_type = new_references.s_relation_type) AND
682 (old_references.rate_number = new_references.rate_number) AND
683 (old_references.fee_cat = new_references.fee_cat)) OR
684 ((old_references.fee_type = Null) AND
685 (old_references.fee_cal_type = Null) AND
686 (old_references.fee_ci_sequence_number = Null) AND
687 (old_references.s_relation_type = Null) AND
688 (old_references.rate_number = Null) AND
689 (old_references.fee_cat = Null))) THEN
690 NULL;
691 ELSE
692 igs_fi_elm_range_rt_pkg.get_ufk_igs_fi_fee_as_rate(
693 old_references.fee_type,
694 old_references.fee_cal_type,
695 old_references.fee_ci_sequence_number,
696 old_references.s_relation_type,
697 old_references.rate_number,
698 old_references.fee_cat
699 );
700 END IF;
701 END Check_UK_Child_Existance;
702
703
704 PROCEDURE Check_Parent_Existance AS
705 /*-----------------------------------------------------------------
706 || Created By :
707 || Created On :
708 || Purpose :
709 || Known limitations, enhancements or remarks :
710 || Change History :
711 || Who When What
712 || (reverse chronological order - newest change first)
713 || pathipat 17-Sep-2003 Enh 3108052 - Unit Sets in Rate Table build
714 || Added call to igs_en_unit_set_pkg.get_pk_for_validation
715 --------------------------------------------------------------------*/
716 CURSOR cur1(p_party_id IN NUMBER) IS
717 SELECT 'X'
718 FROM hz_parties HP,
719 igs_pe_hz_parties PHP
720 WHERE HP.party_id = p_party_id
721 AND PHP.inst_org_ind = 'O'
722 AND HP.party_id = PHP.party_id;
723 BEGIN
724 IF (((old_references.attendance_mode = new_references.attendance_mode)) OR
725 ((new_references.attendance_mode IS NULL))) THEN
726 NULL;
727 ELSE
728 IF NOT IGS_EN_ATD_MODE_PKG.Get_PK_For_Validation (
729 new_references.attendance_mode
730 ) THEN
731 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
732 IGS_GE_MSG_STACK.ADD;
733 App_Exception.Raise_Exception;
734 END IF;
735 END IF;
736 IF (((old_references.attendance_type = new_references.attendance_type)) OR
737 ((new_references.attendance_type IS NULL))) THEN
738 NULL;
739 ELSE
740 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
741 new_references.attendance_type
742 ) THEN
743 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
744 IGS_GE_MSG_STACK.ADD;
745 App_Exception.Raise_Exception;
746 END IF;
747 END IF;
748 IF (((old_references.fee_cat = new_references.fee_cat) AND
749 (old_references.fee_cal_type = new_references.fee_cal_type) AND
750 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
751 (old_references.fee_type = new_references.fee_type)) OR
752 ((new_references.fee_cat IS NULL) OR
753 (new_references.fee_cal_type IS NULL) OR
754 (new_references.fee_ci_sequence_number IS NULL) OR
755 (new_references.fee_type IS NULL))) THEN
756 NULL;
757 ELSE
758 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
759 new_references.fee_cat,
760 new_references.fee_cal_type,
761 new_references.fee_ci_sequence_number,
762 new_references.fee_type
763 ) THEN
764 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
765 IGS_GE_MSG_STACK.ADD;
766 App_Exception.Raise_Exception;
767 END IF;
768 END IF;
769 IF (((old_references.fee_type = new_references.fee_type) AND
770 (old_references.fee_cal_type = new_references.fee_cal_type) AND
771 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
772 ((new_references.fee_type IS NULL) OR
773 (new_references.fee_cal_type IS NULL) OR
774 (new_references.fee_ci_sequence_number IS NULL))) THEN
775 NULL;
776 ELSE
777 IF NOT IGS_FI_F_TYP_CA_INST_PKG.Get_PK_For_Validation (
778 new_references.fee_type,
779 new_references.fee_cal_type,
780 new_references.fee_ci_sequence_number
781 ) THEN
782 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
783 IGS_GE_MSG_STACK.ADD;
784 App_Exception.Raise_Exception;
785 END IF;
786 END IF;
787 IF (((old_references.govt_hecs_cntrbtn_band = new_references.govt_hecs_cntrbtn_band)) OR
788 ((new_references.govt_hecs_cntrbtn_band IS NULL))) THEN
789 NULL;
790 ELSE
791 IF NOT IGS_FI_GOVT_HEC_CNTB_PKG.Get_PK_For_Validation (
792 new_references.govt_hecs_cntrbtn_band
793 ) THEN
794 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
795 IGS_GE_MSG_STACK.ADD;
796 App_Exception.Raise_Exception;
797 END IF;
798 END IF;
799 IF (((old_references.govt_hecs_payment_option = new_references.govt_hecs_payment_option)) OR
800 ((new_references.govt_hecs_payment_option IS NULL))) THEN
801 NULL;
802 ELSE
803 IF NOT IGS_FI_GOV_HEC_PA_OP_PKG.Get_PK_For_Validation (
804 new_references.govt_hecs_payment_option
805 ) THEN
806 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
807 IGS_GE_MSG_STACK.ADD;
808 App_Exception.Raise_Exception;
809 END IF;
810 END IF;
811 IF (((old_references.location_cd = new_references.location_cd)) OR
812 ((new_references.location_cd IS NULL))) THEN
813 NULL;
814 ELSE
815 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
816 new_references.location_cd ,
817 'N'
818 ) THEN
819 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
820 IGS_GE_MSG_STACK.ADD;
821 App_Exception.Raise_Exception;
822 END IF;
823 END IF;
824
825 IF (((old_references.residency_status_cd = new_references.residency_status_cd)) OR
826 ((new_references.residency_status_cd IS NULL))) THEN
827 NULL;
828 ELSE
829 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
830 'PE_RES_STATUS',new_references.residency_status_cd
831 ) THEN
832 Fnd_Message.Set_Name ('FND' , 'FORM_RECORD_DELETED');
833 IGS_GE_MSG_STACK.ADD;
834 App_Exception.Raise_Exception;
835 END IF;
836 END IF;
837 IF (((old_references.course_cd = new_references.course_cd) AND
838 (old_references.version_number = new_references.version_number)) OR
839 ((new_references.course_cd IS NULL) OR
840 (new_references.version_number IS NULL))) THEN
841 NULL;
842 ELSE
843 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
844 new_references.course_cd,
845 new_references.version_number
846 ) THEN
847 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
848 IGS_GE_MSG_STACK.ADD;
849 App_Exception.Raise_Exception;
850 END IF;
851 END IF;
852 IF (((old_references.org_party_id = new_references.org_party_id) OR
853 (new_references.org_party_id IS NULL))) THEN
854 NULL;
855 ELSE
856 OPEN cur1(new_references.org_party_id);
857 IF (cur1%NOTFOUND) THEN
858 CLOSE cur1;
859 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
860 IGS_GE_MSG_STACK.ADD;
861 App_Exception.Raise_Exception;
862 END IF;
863 CLOSE cur1;
864 END IF;
865
866 IF (((old_references.class_standing = new_references.class_standing) OR
867 (new_references.class_standing IS NULL)))THEN
868 NULL;
869 ELSE
870 IF NOT IGS_PR_CLASS_STD_PKG.Get_UK_For_Validation (
871 new_references.class_standing
872 ) THEN
873 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
874 IGS_GE_MSG_STACK.ADD;
875 App_Exception.Raise_Exception;
876 END IF;
877 END IF;
878
879 IF (((old_references.unit_set_cd = new_references.unit_set_cd) AND
880 (old_references.us_version_number = new_references.us_version_number)) OR
881 ((new_references.unit_set_cd IS NULL) OR
882 (new_references.us_version_number IS NULL))) THEN
883 NULL;
884 ELSE
885 IF NOT igs_en_unit_set_pkg.get_pk_for_validation(
886 new_references.unit_set_cd,
887 new_references.us_version_number
888 ) THEN
889 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
890 igs_ge_msg_stack.add;
891 app_exception.raise_exception;
892 END IF;
893 END IF;
894
895
896 END Check_Parent_Existance;
897
898
899 FUNCTION Get_PK_For_Validation (
900 x_far_id NUMBER
901 ) RETURN BOOLEAN AS
902 /*-----------------------------------------------------------------
903 || Created By :
904 || Created On :
905 || Purpose :
906 || Known limitations, enhancements or remarks :
907 || Change History :
908 || Who When What
909 || (reverse chronological order - newest change first)
910 --------------------------------------------------------------------*/
911 CURSOR cur_rowid IS
912 SELECT rowid
913 FROM IGS_FI_FEE_AS_RATE
914 WHERE far_id = x_far_id
915 FOR UPDATE NOWAIT;
916
917 lv_rowid cur_rowid%RowType;
918
919 BEGIN
920 Open cur_rowid;
921 Fetch cur_rowid INTO lv_rowid;
922 IF (cur_rowid%FOUND) THEN
923 Close cur_rowid;
924 Return (TRUE);
925 ELSE
926 Close cur_rowid;
927 Return (FALSE);
928 END IF;
929 END Get_PK_For_Validation;
930
931
932 FUNCTION Get_UK1_For_Validation (
933 x_fee_type IN VARCHAR2,
934 x_fee_cal_type IN VARCHAR2,
935 x_fee_ci_sequence_number IN NUMBER,
936 x_rate_number IN NUMBER,
937 x_fee_cat IN VARCHAR2
938 ) RETURN BOOLEAN AS
939 /*-----------------------------------------------------------------
940 || Created By :
941 || Created On :
942 || Purpose :
943 || Known limitations, enhancements or remarks :
944 || Change History :
945 || Who When What
946 || (reverse chronological order - newest change first)
947 --------------------------------------------------------------------*/
948 CURSOR cur_rowid IS
949 SELECT rowid
950 FROM IGS_FI_FEE_AS_RATE
951 WHERE fee_type = x_fee_type
952 AND fee_cal_type = x_fee_cal_type
953 AND fee_ci_sequence_number = x_fee_ci_sequence_number
954 AND rate_number = x_rate_number
955 AND fee_cat = x_fee_cat
956 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
957 FOR UPDATE NOWAIT;
958 lv_rowid cur_rowid%RowType;
959 BEGIN
960 Open cur_rowid;
961 Fetch cur_rowid INTO lv_rowid;
962 IF (cur_rowid%FOUND) THEN
963 Close cur_rowid;
964 Return (TRUE);
965 ELSE
966 Close cur_rowid;
967 Return (FALSE);
968 END IF;
969 END Get_UK1_For_Validation;
970
971
972 FUNCTION Get_UK2_For_Validation (
973 x_fee_type IN VARCHAR2,
974 x_fee_cal_type IN VARCHAR2,
975 x_fee_ci_sequence_number IN NUMBER,
976 x_s_relation_type IN VARCHAR2,
977 x_rate_number IN NUMBER,
978 x_fee_cat IN VARCHAR2
979 ) RETURN BOOLEAN AS
980 /*-----------------------------------------------------------------
981 || Created By :
982 || Created On :
983 || Purpose :
984 || Known limitations, enhancements or remarks :
985 || Change History :
986 || Who When What
987 || (reverse chronological order - newest change first)
988 --------------------------------------------------------------------*/
989 CURSOR cur_rowid IS
990 SELECT rowid
991 FROM IGS_FI_FEE_AS_RATE
992 WHERE fee_type = x_fee_type
993 AND fee_cal_type = x_fee_cal_type
994 AND fee_ci_sequence_number = x_fee_ci_sequence_number
995 AND s_relation_type = x_s_relation_type
996 AND rate_number = x_rate_number
997 AND (fee_cat = x_fee_cat or fee_cat is null)
998 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
999 FOR UPDATE NOWAIT;
1000 lv_rowid cur_rowid%RowType;
1001 BEGIN
1002 Open cur_rowid;
1003 Fetch cur_rowid INTO lv_rowid;
1004 IF (cur_rowid%FOUND) THEN
1005 Close cur_rowid;
1006 Return (TRUE);
1007 ELSE
1008 Close cur_rowid;
1009 Return (FALSE);
1010 END IF;
1011 END Get_UK2_For_Validation;
1012
1013
1014 PROCEDURE get_fk_igs_as_unit_mode (
1015 x_unit_mode IN VARCHAR2
1016 ) AS
1017 /*-------------------------------------------------------
1018 || Created By : svuppala
1019 || Created On : 01-JUN-2005
1020 || Purpose : Validates the Foreign Keys for the table.
1021 || Known limitations, enhancements or remarks :
1022 || Change History :
1023 || Who When What
1024 || (reverse chronological order - newest change first)
1025 ------------------------------------------------------------
1026 */
1027 CURSOR cur_rowid IS
1028 SELECT rowid
1029 FROM igs_fi_fee_as_rate
1030 WHERE (unit_mode = x_unit_mode);
1031
1032 lv_rowid cur_rowid%RowType;
1033
1034 BEGIN
1035
1036 OPEN cur_rowid;
1037 FETCH cur_rowid INTO lv_rowid;
1038 IF (cur_rowid%FOUND) THEN
1039 CLOSE cur_rowid;
1040 fnd_message.set_name ('IGS', 'IGS_FI_FAR_UM_FK');
1041 igs_ge_msg_stack.add;
1042 app_exception.raise_exception;
1043 RETURN;
1044 END IF;
1045 CLOSE cur_rowid;
1046
1047 END get_fk_igs_as_unit_mode;
1048
1049 PROCEDURE GET_FK_IGS_EN_ATD_MODE (
1050 x_attendance_mode IN VARCHAR2
1051 ) AS
1052 /*-----------------------------------------------------------------
1053 || Created By :
1054 || Created On :
1055 || Purpose :
1056 || Known limitations, enhancements or remarks :
1057 || Change History :
1058 || Who When What
1059 || (reverse chronological order - newest change first)
1060 --------------------------------------------------------------------*/
1061 CURSOR cur_rowid IS
1062 SELECT rowid
1063 FROM IGS_FI_FEE_AS_RATE
1064 WHERE attendance_mode = x_attendance_mode ;
1065 lv_rowid cur_rowid%RowType;
1066 BEGIN
1067 Open cur_rowid;
1068 Fetch cur_rowid INTO lv_rowid;
1069 IF (cur_rowid%FOUND) THEN
1070 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FAR_AM_FK');
1071 IGS_GE_MSG_STACK.ADD;
1072 App_Exception.Raise_Exception;
1073 Close cur_rowid;
1074 Return;
1075 END IF;
1076 Close cur_rowid;
1077 END GET_FK_IGS_EN_ATD_MODE;
1078
1079
1080 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
1081 x_attendance_type IN VARCHAR2
1082 ) AS
1083 /*-----------------------------------------------------------------
1084 || Created By :
1085 || Created On :
1086 || Purpose :
1087 || Known limitations, enhancements or remarks :
1088 || Change History :
1089 || Who When What
1090 || (reverse chronological order - newest change first)
1091 --------------------------------------------------------------------*/
1092 CURSOR cur_rowid IS
1093 SELECT rowid
1094 FROM IGS_FI_FEE_AS_RATE
1095 WHERE attendance_type = x_attendance_type ;
1096 lv_rowid cur_rowid%RowType;
1097 BEGIN
1098 Open cur_rowid;
1099 Fetch cur_rowid INTO lv_rowid;
1100 IF (cur_rowid%FOUND) THEN
1101 Close cur_rowid;
1102 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FAR_ATT_FK');
1103 IGS_GE_MSG_STACK.ADD;
1104 App_Exception.Raise_Exception;
1105 Return;
1106 END IF;
1107 Close cur_rowid;
1108 END GET_FK_IGS_EN_ATD_TYPE;
1109
1110
1111 PROCEDURE GET_FK_IGS_FI_GOVT_HEC_CNTB (
1112 x_govt_hecs_cntrbtn_band IN NUMBER
1113 ) AS
1114 /*-----------------------------------------------------------------
1115 || Created By :
1116 || Created On :
1117 || Purpose :
1118 || Known limitations, enhancements or remarks :
1119 || Change History :
1120 || Who When What
1121 || (reverse chronological order - newest change first)
1122 --------------------------------------------------------------------*/
1123 CURSOR cur_rowid IS
1124 SELECT rowid
1125 FROM IGS_FI_FEE_AS_RATE
1126 WHERE govt_hecs_cntrbtn_band = x_govt_hecs_cntrbtn_band ;
1127 lv_rowid cur_rowid%RowType;
1128 BEGIN
1129 Open cur_rowid;
1130 Fetch cur_rowid INTO lv_rowid;
1131 IF (cur_rowid%FOUND) THEN
1132 Close cur_rowid;
1133 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FAR_GHC_FK');
1134 IGS_GE_MSG_STACK.ADD;
1135 App_Exception.Raise_Exception;
1136 Return;
1137 END IF;
1138 Close cur_rowid;
1139 END GET_FK_IGS_FI_GOVT_HEC_CNTB;
1140
1141
1142 PROCEDURE GET_FK_IGS_FI_GOV_HEC_PA_OP (
1143 x_govt_hecs_payment_option IN VARCHAR2
1144 ) AS
1145 /*-----------------------------------------------------------------
1146 || Created By :
1147 || Created On :
1148 || Purpose :
1149 || Known limitations, enhancements or remarks :
1150 || Change History :
1151 || Who When What
1152 || (reverse chronological order - newest change first)
1153 --------------------------------------------------------------------*/
1154 CURSOR cur_rowid IS
1155 SELECT rowid
1156 FROM IGS_FI_FEE_AS_RATE
1157 WHERE govt_hecs_payment_option = x_govt_hecs_payment_option ;
1158 lv_rowid cur_rowid%RowType;
1159 BEGIN
1160 Open cur_rowid;
1161 Fetch cur_rowid INTO lv_rowid;
1162 IF (cur_rowid%FOUND) THEN
1163 Close cur_rowid;
1164 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FAR_GHPO_FK');
1165 IGS_GE_MSG_STACK.ADD;
1166 App_Exception.Raise_Exception;
1167 Return;
1168 END IF;
1169 Close cur_rowid;
1170 END GET_FK_IGS_FI_GOV_HEC_PA_OP;
1171
1172
1173 PROCEDURE GET_FK_IGS_AD_LOCATION (
1174 x_location_cd IN VARCHAR2
1175 ) AS
1176 /*-----------------------------------------------------------------
1177 || Created By :
1178 || Created On :
1179 || Purpose :
1180 || Known limitations, enhancements or remarks :
1181 || Change History :
1182 || Who When What
1183 || (reverse chronological order - newest change first)
1184 --------------------------------------------------------------------*/
1185 CURSOR cur_rowid IS
1186 SELECT rowid
1187 FROM IGS_FI_FEE_AS_RATE
1188 WHERE location_cd = x_location_cd ;
1189 lv_rowid cur_rowid%RowType;
1190 BEGIN
1191 Open cur_rowid;
1192 Fetch cur_rowid INTO lv_rowid;
1193 IF (cur_rowid%FOUND) THEN
1194 Close cur_rowid;
1195 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FAR_LOC_FK');
1196 IGS_GE_MSG_STACK.ADD;
1197 App_Exception.Raise_Exception;
1198 Return;
1199 END IF;
1200 Close cur_rowid;
1201 END GET_FK_IGS_AD_LOCATION;
1202
1203
1204 PROCEDURE GET_FK_IGS_PS_VER(
1205 x_course_cd IN VARCHAR2,
1206 x_version_number IN NUMBER
1207 ) AS
1208 /*-----------------------------------------------------------------
1209 || Created By :
1210 || Created On :
1211 || Purpose :
1212 || Known limitations, enhancements or remarks :
1213 || Change History :
1214 || Who When What
1215 || (reverse chronological order - newest change first)
1216 --------------------------------------------------------------------*/
1217 CURSOR cur_rowid IS
1218 SELECT rowid
1219 FROM IGS_FI_FEE_AS_RATE
1220 WHERE course_cd = x_course_cd
1221 AND version_number = x_version_number;
1222 lv_rowid cur_rowid%RowType;
1223 BEGIN
1224 Open cur_rowid;
1225 Fetch cur_rowid INTO lv_rowid;
1226 IF(cur_rowid%FOUND) THEN
1227 Close cur_rowid;
1228 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FAR_CRV_FK');
1229 IGS_GE_MSG_STACK.ADD;
1230 App_Exception.Raise_Exception;
1231 Return;
1232 END IF;
1233 Close cur_rowid;
1234 END GET_FK_IGS_PS_VER;
1235
1236
1237 PROCEDURE GET_UFK_IGS_PR_CLASS_STD(
1238 x_class_standing IN VARCHAR2
1239 ) AS
1240 /*-----------------------------------------------------------------
1241 || Created By :
1242 || Created On :
1243 || Purpose :
1244 || Known limitations, enhancements or remarks :
1245 || Change History :
1246 || Who When What
1247 || (reverse chronological order - newest change first)
1248 --------------------------------------------------------------------*/
1249 -- Modified cur_rowid to fetch records from igs_fi_fee_as_rate instead of fetching from
1250 -- IGS_PR_CLASS_STD. This has been done as per Bug# 2637262.
1251 CURSOR cur_rowid IS
1252 SELECT rowid
1253 FROM IGS_FI_FEE_AS_RATE
1254 WHERE class_standing = x_class_standing;
1255 lv_rowid cur_rowid%RowType;
1256 BEGIN
1257 Open cur_rowid;
1258 Fetch cur_rowid INTO lv_rowid;
1259 IF(cur_rowid%FOUND) THEN
1260 Close cur_rowid;
1261 Fnd_Message.Set_Name ('IGS', 'IGS_FI_FAR_PCS_FK');
1262 IGS_GE_MSG_STACK.ADD;
1263 App_Exception.Raise_Exception;
1264 Return;
1265 END IF;
1266 Close cur_rowid;
1267 END GET_UFK_IGS_PR_CLASS_STD;
1268
1269
1270 PROCEDURE Before_DML (
1271 p_action IN VARCHAR2,
1272 x_rowid IN VARCHAR2 ,
1273 x_far_id IN NUMBER ,
1274 x_fee_type IN VARCHAR2 ,
1275 x_fee_cal_type IN VARCHAR2 ,
1276 x_fee_ci_sequence_number IN NUMBER ,
1277 x_s_relation_type IN VARCHAR2 ,
1278 x_rate_number IN NUMBER ,
1279 x_fee_cat IN VARCHAR2 ,
1280 x_location_cd IN VARCHAR2 ,
1281 x_attendance_type IN VARCHAR2 ,
1282 x_attendance_mode IN VARCHAR2 ,
1283 x_order_of_precedence IN NUMBER ,
1284 x_govt_hecs_payment_option IN VARCHAR2 ,
1285 x_govt_hecs_cntrbtn_band IN NUMBER ,
1286 x_chg_rate IN NUMBER ,
1287 x_logical_delete_dt IN DATE ,
1288 x_residency_status_cd IN VARCHAR2 ,
1289 x_course_cd IN VARCHAR2 ,
1290 x_version_number IN NUMBER ,
1291 x_org_party_id IN NUMBER ,
1292 x_class_standing IN VARCHAR2 ,
1293 x_creation_date IN DATE ,
1294 x_created_by IN NUMBER ,
1295 x_last_update_date IN DATE ,
1296 x_last_updated_by IN NUMBER ,
1297 x_last_update_login IN NUMBER,
1298 x_unit_set_cd IN VARCHAR2,
1299 x_us_version_number IN NUMBER,
1300 x_unit_cd IN VARCHAR2 ,
1301 x_unit_version_number IN NUMBER ,
1302 x_unit_level IN VARCHAR2 ,
1303 x_unit_type_id IN NUMBER ,
1304 x_unit_class IN VARCHAR2 ,
1305 x_unit_mode IN VARCHAR2
1306 ) AS
1307 /*-----------------------------------------------------------------
1308 || Created By :
1309 || Created On :
1310 || Purpose :
1311 || Known limitations, enhancements or remarks :
1312 || Change History :
1313 || Who When What
1314 || (reverse chronological order - newest change first)
1315 || svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
1316 || Unit Version and Unit Level
1317 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
1318 || Added 2 new columns unit_set_cd and us_version_number
1319 --------------------------------------------------------------------*/
1320 BEGIN
1321 Set_Column_Values (
1322 p_action,
1323 x_rowid,
1324 x_far_id,
1325 x_fee_type,
1326 x_fee_cal_type,
1327 x_fee_ci_sequence_number,
1328 x_s_relation_type,
1329 x_rate_number,
1330 x_fee_cat,
1331 x_location_cd,
1332 x_attendance_type,
1333 x_attendance_mode,
1334 x_order_of_precedence,
1335 x_govt_hecs_payment_option,
1336 x_govt_hecs_cntrbtn_band,
1337 x_chg_rate,
1338 x_logical_delete_dt,
1339 x_residency_status_cd,
1340 x_course_cd,
1341 x_version_number,
1342 x_org_party_id,
1343 x_class_standing,
1344 x_creation_date,
1345 x_created_by,
1346 x_last_update_date,
1347 x_last_updated_by,
1348 x_last_update_login,
1349 x_unit_set_cd,
1350 x_us_version_number,
1351 x_unit_cd ,
1352 x_unit_version_number,
1353 x_unit_level,
1354 x_unit_type_id,
1355 x_unit_class,
1356 x_unit_mode
1357
1358 );
1359
1360 IF (p_action = 'INSERT') THEN
1361 -- Call all the procedures related to Before Insert.
1362 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE ,p_updating => FALSE , p_deleting => FALSE );
1363 IF (Get_PK_For_Validation (
1364 new_references.far_id
1365 )) THEN
1366 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
1367 IGS_GE_MSG_STACK.ADD;
1368 App_Exception.Raise_Exception;
1369 END IF;
1370 Check_Uniqueness;
1371 Check_Constraints;
1372 Check_Parent_Existance;
1373 ELSIF (p_action = 'UPDATE') THEN
1374 -- Call all the procedures related to Before Update.
1375 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE , p_updating => TRUE ,p_deleting => FALSE);
1376 beforerowupdate;
1377 Check_Parent_Existance;
1378 Check_UK_Child_Existance;
1379
1380 ELSIF (p_action = 'DELETE') THEN
1381 -- Call all the procedures related to Before Delete.
1382 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating => FALSE , p_deleting => TRUE );
1383 Check_Child_Existance;
1384
1385 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1386 IF (Get_PK_For_Validation (
1387 new_references.far_id
1388 )) THEN
1389 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
1390 IGS_GE_MSG_STACK.ADD;
1391 App_Exception.Raise_Exception;
1392 END IF;
1393 Check_Uniqueness;
1394 Check_Constraints;
1395
1396 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1397 Check_Uniqueness;
1398 Check_Constraints;
1399 Check_UK_Child_Existance;
1400
1401 ELSIF (p_action = 'VALIDATE_DELETE') THEN
1402 Check_Child_Existance;
1403 END IF;
1404
1405 END Before_DML;
1406
1407
1408 PROCEDURE After_DML (
1409 p_action IN VARCHAR2,
1410 x_rowid IN VARCHAR2
1411 ) AS
1412 /*-----------------------------------------------------------------
1413 || Created By :
1414 || Created On :
1415 || Purpose :
1416 || Known limitations, enhancements or remarks :
1417 || Change History :
1418 || Who When What
1419 || (reverse chronological order - newest change first)
1420 --------------------------------------------------------------------*/
1421 BEGIN
1422 l_rowid := x_rowid;
1423 IF (p_action = 'INSERT') THEN
1424 -- Call all the procedures related to After Insert.
1425 AfterStmtInsertUpdate4 ( p_inserting => TRUE ,p_updating => FALSE , p_deleting => FALSE );
1426 ELSIF (p_action = 'UPDATE') THEN
1427 -- Call all the procedures related to After Update.
1428 AfterRowUpdate3 ( p_inserting => FALSE , p_updating => TRUE ,p_deleting => FALSE);
1429 AfterStmtInsertUpdate4 ( p_inserting => FALSE , p_updating => TRUE ,p_deleting => FALSE);
1430 END IF;
1431 l_rowid := NULL;
1432 END After_DML;
1433
1434
1435 PROCEDURE insert_row (
1436 x_rowid IN OUT NOCOPY VARCHAR2,
1437 x_far_id IN OUT NOCOPY NUMBER,
1438 x_fee_type IN VARCHAR2,
1439 x_fee_cal_type IN VARCHAR2,
1440 x_fee_ci_sequence_number IN NUMBER,
1441 x_s_relation_type IN VARCHAR2,
1442 x_rate_number IN NUMBER,
1443 x_fee_cat IN VARCHAR2,
1444 x_location_cd IN VARCHAR2,
1445 x_attendance_type IN VARCHAR2,
1446 x_attendance_mode IN VARCHAR2,
1447 x_order_of_precedence IN NUMBER,
1448 x_govt_hecs_payment_option IN VARCHAR2,
1449 x_govt_hecs_cntrbtn_band IN NUMBER,
1450 x_chg_rate IN NUMBER,
1451 x_logical_delete_dt IN DATE,
1452 x_residency_status_cd IN VARCHAR2 ,
1453 x_course_cd IN VARCHAR2 ,
1454 x_version_number IN NUMBER ,
1455 x_org_party_id IN NUMBER ,
1456 x_class_standing IN VARCHAR2 ,
1457 x_mode IN VARCHAR2,
1458 x_unit_set_cd IN VARCHAR2,
1459 x_us_version_number IN NUMBER,
1460 x_unit_cd IN VARCHAR2 ,
1461 x_unit_version_number IN NUMBER ,
1462 x_unit_level IN VARCHAR2 ,
1463 x_unit_type_id IN NUMBER ,
1464 x_unit_class IN VARCHAR2 ,
1465 x_unit_mode IN VARCHAR2
1466 ) AS
1467 /*-----------------------------------------------------------------
1468 || Created By :
1469 || Created On :
1470 || Purpose :
1471 || Known limitations, enhancements or remarks :
1472 || Change History :
1473 || Who When What
1474 || (reverse chronological order - newest change first)
1475 || svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
1476 || Unit Version and Unit Level
1477 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
1478 || Added 2 new columns unit_set_cd and us_version_number
1479 --------------------------------------------------------------------*/
1480 CURSOR C (cp_range_id IN NUMBER) is select ROWID from IGS_FI_FEE_AS_RATE
1481 where far_id = cp_range_id;
1482
1483 X_LAST_UPDATE_DATE DATE;
1484 X_LAST_UPDATED_BY NUMBER;
1485 X_LAST_UPDATE_LOGIN NUMBER;
1486 X_REQUEST_ID NUMBER;
1487 X_PROGRAM_ID NUMBER;
1488 X_PROGRAM_APPLICATION_ID NUMBER;
1489 X_PROGRAM_UPDATE_DATE DATE;
1490
1491 BEGIN
1492 x_last_update_date := SYSDATE;
1493 IF(x_mode = 'I') THEN
1494 x_last_updated_by := 1;
1495 x_last_update_login := 0;
1496 ELSIF (x_mode = 'R') THEN
1497 x_last_updated_by := fnd_global.USER_ID;
1498 IF x_last_updated_by IS NULL THEN
1499 x_last_updated_by := -1;
1500 END IF;
1501 x_last_update_login :=fnd_global.login_id;
1502 IF x_last_update_login IS NULL THEN
1503 x_last_update_login := -1;
1504 END IF;
1505 x_request_id := fnd_global.conc_request_id;
1506 x_program_id := fnd_global.conc_program_id;
1507 x_program_application_id := fnd_global.prog_appl_id;
1508 IF (x_request_id = -1 ) THEN
1509 x_request_id := NULL;
1510 x_program_id := NULL;
1511 x_program_application_id := NULL;
1512 x_program_update_date := NULL;
1513 ELSE
1514 x_program_update_date:=SYSDATE;
1515 END IF;
1516 ELSE
1517 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1518 igs_ge_msg_stack.add;
1519 app_exception.raise_exception;
1520 END IF;
1521
1522 SELECT igs_fi_fee_as_rate_far_id_s.nextval
1523 INTO x_far_id
1524 FROM dual;
1525
1526 Before_DML(
1527 p_action =>'INSERT',
1528 x_rowid => x_rowid,
1529 x_far_id => x_far_id,
1530 x_attendance_mode => x_attendance_mode,
1531 x_attendance_type => x_attendance_type,
1532 x_chg_rate => x_chg_rate,
1533 x_fee_cal_type => x_fee_cal_type,
1534 x_fee_cat => x_fee_cat,
1535 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
1536 x_fee_type => x_fee_type,
1537 x_govt_hecs_cntrbtn_band => x_govt_hecs_cntrbtn_band,
1538 x_govt_hecs_payment_option => x_govt_hecs_payment_option,
1539 x_location_cd => x_location_cd,
1540 x_logical_delete_dt => x_logical_delete_dt,
1541 x_order_of_precedence => x_order_of_precedence,
1542 x_rate_number => x_rate_number,
1543 x_s_relation_type => x_s_relation_type,
1544 x_residency_status_cd => x_residency_status_cd,
1545 x_course_cd => x_course_cd,
1546 x_version_number => x_version_number,
1547 x_org_party_id => x_org_party_id,
1548 x_class_standing => x_class_standing,
1549 x_creation_date => x_last_update_date,
1550 x_created_by => x_last_updated_by,
1551 x_last_update_date => x_last_update_date,
1552 x_last_updated_by => x_last_updated_by,
1553 x_last_update_login => x_last_update_login,
1554 x_unit_set_cd => x_unit_set_cd,
1555 x_us_version_number => x_us_version_number,
1556 x_unit_cd => x_unit_cd,
1557 x_unit_version_number => x_unit_version_number,
1558 x_unit_level => x_unit_level ,
1559 x_unit_type_id => x_unit_type_id,
1560 x_unit_class => x_unit_class ,
1561 x_unit_mode => x_unit_mode
1562 );
1563
1564 INSERT INTO igs_fi_fee_as_rate (
1565 far_id,
1566 fee_type,
1567 fee_cal_type,
1568 fee_ci_sequence_number,
1569 s_relation_type,
1570 rate_number,
1571 fee_cat,
1572 location_cd,
1573 attendance_type,
1574 attendance_mode,
1575 order_of_precedence,
1576 govt_hecs_payment_option,
1577 govt_hecs_cntrbtn_band,
1578 chg_rate,
1579 logical_delete_dt,
1580 residency_status_cd,
1581 course_cd,
1582 version_number,
1583 org_party_id,
1584 class_standing,
1585 creation_date,
1586 created_by,
1587 last_update_date,
1588 last_updated_by,
1589 last_update_login,
1590 request_id,
1591 program_id,
1592 program_application_id,
1593 program_update_date,
1594 unit_set_cd,
1595 us_version_number,
1596 unit_cd ,
1597 unit_version_number,
1598 unit_level ,
1599 unit_type_id,
1600 unit_class ,
1601 unit_mode
1602 ) VALUES (
1603 new_references.far_id,
1604 new_references.fee_type,
1605 new_references.fee_cal_type,
1606 new_references.fee_ci_sequence_number,
1607 new_references.s_relation_type,
1608 new_references.rate_number,
1609 new_references.fee_cat,
1610 new_references.location_cd,
1611 new_references.attendance_type,
1612 new_references.attendance_mode,
1613 new_references.order_of_precedence,
1614 new_references.govt_hecs_payment_option,
1615 new_references.govt_hecs_cntrbtn_band,
1616 new_references.chg_rate,
1617 new_references.logical_delete_dt,
1618 new_references.residency_status_cd,
1619 new_references.course_cd,
1620 new_references.version_number,
1621 new_references.org_party_id,
1622 new_references.class_standing,
1623 x_last_update_date,
1624 x_last_updated_by,
1625 x_last_update_date,
1626 x_last_updated_by,
1627 x_last_update_login,
1628 x_request_id,
1629 x_program_id,
1630 x_program_application_id,
1631 x_program_update_date,
1632 new_references.unit_set_cd,
1633 new_references.us_version_number,
1634 new_references.unit_cd,
1635 new_references.unit_version_number,
1636 new_references.unit_level ,
1637 new_references.unit_type_id ,
1638 new_references.unit_class,
1639 new_references.unit_mode
1640 );
1641
1642 OPEN c (x_far_id);
1643 FETCH c INTO x_rowid;
1644 IF (c%NOTFOUND) THEN
1645 CLOSE c;
1646 RAISE NO_DATA_FOUND;
1647 END IF;
1648 CLOSE c;
1649
1650 After_DML(
1651 p_action =>'INSERT',
1652 x_rowid => x_rowid
1653 );
1654
1655 END insert_row;
1656
1657
1658 PROCEDURE lock_row (
1659 x_rowid IN VARCHAR2,
1660 x_far_id IN NUMBER,
1661 x_fee_type IN VARCHAR2,
1662 x_fee_cal_type IN VARCHAR2,
1663 x_fee_ci_sequence_number IN NUMBER,
1664 x_s_relation_type IN VARCHAR2,
1665 x_rate_number IN NUMBER,
1666 x_fee_cat IN VARCHAR2,
1667 x_location_cd IN VARCHAR2,
1668 x_attendance_type IN VARCHAR2,
1669 x_attendance_mode IN VARCHAR2,
1670 x_order_of_precedence IN NUMBER,
1671 x_govt_hecs_payment_option IN VARCHAR2,
1672 x_govt_hecs_cntrbtn_band IN NUMBER,
1673 x_chg_rate IN NUMBER,
1674 x_logical_delete_dt IN DATE,
1675 x_residency_status_cd IN VARCHAR2 ,
1676 x_course_cd IN VARCHAR2 ,
1677 x_version_number IN NUMBER ,
1678 x_org_party_id IN NUMBER ,
1679 x_class_standing IN VARCHAR2,
1680 x_unit_set_cd IN VARCHAR2,
1681 x_us_version_number IN NUMBER,
1682 x_unit_cd IN VARCHAR2 ,
1683 x_unit_version_number IN NUMBER ,
1684 x_unit_level IN VARCHAR2 ,
1685 x_unit_type_id IN NUMBER ,
1686 x_unit_class IN VARCHAR2 ,
1687 x_unit_mode IN VARCHAR2
1688 ) AS
1689 /*-----------------------------------------------------------------
1690 || Created By :
1691 || Created On :
1692 || Purpose :
1693 || Known limitations, enhancements or remarks :
1694 || Change History :
1695 || Who When What
1696 || (reverse chronological order - newest change first)
1697 || svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
1698 || Unit Version and Unit Level
1699 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
1700 || Added 2 new columns unit_set_cd and us_version_number
1701 --------------------------------------------------------------------*/
1702 CURSOR c1 IS
1703 SELECT far_id,
1704 fee_cal_type,
1705 fee_ci_sequence_number,
1706 s_relation_type,
1707 rate_number,
1708 fee_cat,
1709 location_cd,
1710 attendance_type,
1711 attendance_mode,
1712 order_of_precedence,
1713 govt_hecs_payment_option,
1714 govt_hecs_cntrbtn_band,
1715 chg_rate,
1716 logical_delete_dt,
1717 residency_status_cd,
1718 course_cd,
1719 version_number,
1720 org_party_id,
1721 class_standing,
1722 unit_set_cd,
1723 us_version_number,
1724 unit_cd,
1725 unit_version_number,
1726 unit_level ,
1727 unit_type_id ,
1728 unit_class ,
1729 unit_mode
1730 FROM igs_fi_fee_as_rate
1731 WHERE rowid = x_rowid
1732 FOR UPDATE NOWAIT;
1733
1734 tlinfo c1%ROWTYPE;
1735
1736 BEGIN
1737
1738 OPEN c1;
1739 FETCH c1 INTO tlinfo;
1740 IF (c1%NOTFOUND) THEN
1741 CLOSE c1;
1742 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1743 igs_ge_msg_stack.add;
1744 app_exception.raise_exception;
1745 RETURN;
1746 END IF;
1747 CLOSE c1;
1748 IF ( (tlinfo.fee_cal_type = x_fee_cal_type)
1749 AND (tlinfo.far_id = x_far_id)
1750 AND (tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number)
1751 AND (tlinfo.s_relation_type = x_s_relation_type)
1752 AND (tlinfo.rate_number = x_rate_number)
1753 AND ((tlinfo.fee_cat = x_fee_cat) OR ((tlinfo.fee_cat IS NULL) AND (x_fee_cat IS NULL)))
1754 AND ((tlinfo.location_cd = x_location_cd) OR ((tlinfo.LOCATION_CD is null) AND (x_location_cd IS NULL)))
1755 AND ((tlinfo.attendance_type = x_attendance_type) OR ((tlinfo.attendance_type IS NULL) AND (x_attendance_type IS NULL)))
1756 AND ((tlinfo.attendance_mode = x_attendance_mode) OR ((tlinfo.attendance_mode IS NULL) AND (x_attendance_mode IS NULL)))
1757 AND ((tlinfo.order_of_precedence = x_order_of_precedence) OR ((tlinfo.order_of_precedence IS NULL) AND (x_order_of_precedence IS NULL)))
1758 AND ((tlinfo.govt_hecs_payment_option = x_govt_hecs_payment_option) OR ((tlinfo.govt_hecs_payment_option IS NULL) AND (x_govt_hecs_payment_option IS NULL)))
1759 AND ((tlinfo.govt_hecs_cntrbtn_band = x_govt_hecs_cntrbtn_band) OR ((tlinfo.govt_hecs_cntrbtn_band IS NULL) AND (x_govt_hecs_cntrbtn_band IS NULL)))
1760 AND (tlinfo.chg_rate = x_chg_rate)
1761 AND ((tlinfo.logical_delete_dt = x_logical_delete_dt) OR ((tlinfo.logical_delete_dt IS NULL) AND (x_logical_delete_dt IS NULL)))
1762 AND ((tlinfo.residency_status_cd = x_residency_status_cd) OR ((tlinfo.residency_status_cd IS NULL) AND (x_residency_status_cd IS NULL)))
1763 AND ((tlinfo.course_cd = x_course_cd) OR ((tlinfo.course_cd IS NULL) AND (x_course_cd IS NULL)))
1764 AND ((tlinfo.version_number = x_version_number) OR ((tlinfo.version_number IS NULL) AND (x_version_number IS NULL)))
1765 AND ((tlinfo.org_party_id = x_org_party_id) OR ((tlinfo.org_party_id IS NULL) AND (x_org_party_id IS NULL)))
1766 AND ((tlinfo.class_standing = x_class_standing) OR ((tlinfo.class_standing IS NULL) AND (x_class_standing IS NULL)))
1767 AND ((tlinfo.unit_set_cd = x_unit_set_cd) OR ((tlinfo.unit_set_cd IS NULL) AND (x_unit_set_cd IS NULL)))
1768 AND ((tlinfo.us_version_number = x_us_version_number) OR ((tlinfo.us_version_number IS NULL) AND (x_us_version_number IS NULL)))
1769 AND ((tlinfo.unit_cd = x_unit_cd) OR ((tlinfo.unit_cd IS NULL) AND (x_unit_cd IS NULL)))
1770 AND ((tlinfo.unit_version_number = x_unit_version_number) OR ((tlinfo.unit_version_number IS NULL) AND (x_unit_version_number IS NULL)))
1771 AND ((tlinfo.unit_level = x_unit_level) OR ((tlinfo.unit_level IS NULL) AND (x_unit_level IS NULL)))
1772 AND ((tlinfo.unit_type_id = x_unit_type_id) OR ((tlinfo.unit_type_id IS NULL) AND (x_unit_type_id IS NULL)))
1773 AND ((tlinfo.unit_class = x_unit_class) OR ((tlinfo.unit_class IS NULL) AND (x_unit_class IS NULL)))
1774 AND ((tlinfo.unit_mode = x_unit_mode) OR ((tlinfo.unit_mode IS NULL) AND (x_unit_mode IS NULL)))
1775 ) THEN
1776 NULL;
1777 ELSE
1778 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1779 igs_ge_msg_stack.add;
1780 app_exception.raise_exception;
1781 END IF;
1782
1783 RETURN;
1784
1785 END lock_row;
1786
1787
1788 PROCEDURE update_row (
1789 x_rowid IN VARCHAR2,
1790 x_far_id IN NUMBER,
1791 x_fee_type IN VARCHAR2,
1792 x_fee_cal_type IN VARCHAR2,
1793 x_fee_ci_sequence_number IN NUMBER,
1794 x_s_relation_type IN VARCHAR2,
1795 x_rate_number IN NUMBER,
1796 x_fee_cat IN VARCHAR2,
1797 x_location_cd IN VARCHAR2,
1798 x_attendance_type IN VARCHAR2,
1799 x_attendance_mode IN VARCHAR2,
1800 x_order_of_precedence IN NUMBER,
1801 x_govt_hecs_payment_option IN VARCHAR2,
1802 x_govt_hecs_cntrbtn_band IN NUMBER,
1803 x_chg_rate IN NUMBER,
1804 x_logical_delete_dt IN DATE,
1805 x_residency_status_cd IN VARCHAR2 ,
1806 x_course_cd IN VARCHAR2 ,
1807 x_version_number IN NUMBER ,
1808 x_org_party_id IN NUMBER ,
1809 x_class_standing IN VARCHAR2 ,
1810 x_mode IN VARCHAR2,
1811 x_unit_set_cd IN VARCHAR2,
1812 x_us_version_number IN NUMBER,
1813 x_unit_cd IN VARCHAR2 ,
1814 x_unit_version_number IN NUMBER ,
1815 x_unit_level IN VARCHAR2 ,
1816 x_unit_type_id IN NUMBER ,
1817 x_unit_class IN VARCHAR2 ,
1818 x_unit_mode IN VARCHAR2
1819 ) AS
1820 /*-----------------------------------------------------------------
1821 || Created By :
1822 || Created On :
1823 || Purpose :
1824 || Known limitations, enhancements or remarks :
1825 || Change History :
1826 || Who When What
1827 || (reverse chronological order - newest change first)
1828 || svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
1829 || Unit Version and Unit Level
1830 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
1831 || Added 2 new columns unit_set_cd and us_version_number
1832 --------------------------------------------------------------------*/
1833 x_last_update_date DATE;
1834 x_last_updated_by NUMBER;
1835 x_last_update_login NUMBER;
1836 x_request_id NUMBER;
1837 x_program_id NUMBER;
1838 x_program_application_id NUMBER;
1839 x_program_update_date DATE;
1840
1841 BEGIN
1842
1843 x_last_update_date := SYSDATE;
1844
1845 IF(x_mode = 'I') THEN
1846 x_last_updated_by := 1;
1847 x_last_update_login := 0;
1848 ELSIF (x_mode = 'R') THEN
1849 x_last_updated_by := fnd_global.user_id;
1850 IF x_last_updated_by IS NULL THEN
1851 x_last_updated_by := -1;
1852 END IF;
1853 x_last_update_login :=fnd_global.login_id;
1854 IF x_last_update_login IS NULL THEN
1855 x_last_update_login := -1;
1856 END IF;
1857 x_request_id := fnd_global.conc_request_id;
1858 x_program_id := fnd_global.conc_program_id;
1859 x_program_application_id := fnd_global.prog_appl_id;
1860 IF (x_request_id = -1 ) THEN
1861 x_request_id := old_references.request_id;
1862 x_program_id := old_references.program_id;
1863 x_program_application_id := old_references.program_application_id;
1864 x_program_update_date := old_references.program_update_date;
1865 ELSE
1866 x_program_update_date := SYSDATE;
1867 END IF;
1868 ELSE
1869 fnd_message.set_name('FND', 'SYSTEM-INVALID ARGS');
1870 igs_ge_msg_stack.add;
1871 app_exception.raise_exception;
1872 END IF;
1873
1874 Before_DML(
1875 p_action => 'UPDATE',
1876 x_rowid => x_rowid,
1877 x_far_id => x_far_id,
1878 x_attendance_mode => x_attendance_mode,
1879 x_attendance_type => x_attendance_type,
1880 x_chg_rate => x_chg_rate,
1881 x_fee_cal_type => x_fee_cal_type,
1882 x_fee_cat => x_fee_cat,
1883 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
1884 x_fee_type => x_fee_type,
1885 x_govt_hecs_cntrbtn_band => x_govt_hecs_cntrbtn_band,
1886 x_govt_hecs_payment_option => x_govt_hecs_payment_option,
1887 x_location_cd => x_location_cd,
1888 x_logical_delete_dt => x_logical_delete_dt,
1889 x_order_of_precedence => x_order_of_precedence,
1890 x_rate_number => x_rate_number,
1891 x_s_relation_type => x_s_relation_type,
1892 x_residency_status_cd => x_residency_status_cd,
1893 x_course_cd => x_course_cd,
1894 x_version_number => x_version_number,
1895 x_org_party_id => x_org_party_id,
1896 x_class_standing => x_class_standing,
1897 x_creation_date => x_last_update_date,
1898 x_created_by => x_last_updated_by,
1899 x_last_update_date => x_last_update_date,
1900 x_last_updated_by => x_last_updated_by,
1901 x_last_update_login => x_last_update_login,
1902 x_unit_set_cd => x_unit_set_cd,
1903 x_us_version_number => x_us_version_number,
1904 x_unit_cd => x_unit_cd,
1905 x_unit_version_number => x_unit_version_number,
1906 x_unit_level => x_unit_level ,
1907 x_unit_type_id => x_unit_type_id,
1908 x_unit_class => x_unit_class ,
1909 x_unit_mode => x_unit_mode
1910 );
1911
1912 UPDATE igs_fi_fee_as_rate
1913 SET
1914 far_id = far_id,
1915 fee_cal_type = new_references.fee_cal_type,
1916 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
1917 s_relation_type = new_references.s_relation_type,
1918 rate_number = new_references.rate_number,
1919 fee_cat = new_references.fee_cat,
1920 location_cd = new_references.location_cd,
1921 attendance_type = new_references.attendance_type,
1922 attendance_mode = new_references.attendance_mode,
1923 order_of_precedence = new_references.order_of_precedence,
1924 govt_hecs_payment_option = new_references.govt_hecs_payment_option,
1925 govt_hecs_cntrbtn_band = new_references.govt_hecs_cntrbtn_band,
1926 chg_rate = new_references.chg_rate,
1927 logical_delete_dt = new_references.logical_delete_dt,
1928 residency_status_cd = new_references.residency_status_cd,
1929 course_cd = new_references.course_cd,
1930 version_number = new_references.version_number,
1931 org_party_id = new_references.org_party_id,
1932 class_standing = new_references.class_standing,
1933 last_update_date = x_last_update_date,
1934 last_updated_by = x_last_updated_by,
1935 last_update_login = x_last_update_login,
1936 request_id = x_request_id,
1937 program_id = x_program_id,
1938 program_application_id = x_program_application_id,
1939 program_update_date = x_program_update_date,
1940 unit_set_cd = new_references.unit_set_cd,
1941 us_version_number = new_references.us_version_number,
1942 unit_cd = new_references.unit_cd,
1943 unit_version_number = new_references.unit_version_number,
1944 unit_level = new_references.unit_level ,
1945 unit_type_id = new_references.unit_type_id,
1946 unit_class = new_references.unit_class ,
1947 unit_mode = new_references.unit_mode
1948 WHERE rowid = x_rowid ;
1949
1950 IF (SQL%NOTFOUND) THEN
1951 RAISE NO_DATA_FOUND;
1952 END IF;
1953
1954 after_dml(
1955 p_action =>'UPDATE',
1956 x_rowid => x_rowid
1957 );
1958
1959 END update_row;
1960
1961
1962 PROCEDURE add_row (
1963 x_rowid IN OUT NOCOPY VARCHAR2,
1964 x_far_id IN OUT NOCOPY NUMBER,
1965 x_fee_type IN VARCHAR2,
1966 x_fee_cal_type IN VARCHAR2,
1967 x_fee_ci_sequence_number IN NUMBER,
1968 x_s_relation_type IN VARCHAR2,
1969 x_rate_number IN NUMBER,
1970 x_fee_cat IN VARCHAR2,
1971 x_location_cd IN VARCHAR2,
1972 x_attendance_type IN VARCHAR2,
1973 x_attendance_mode IN VARCHAR2,
1974 x_order_of_precedence IN NUMBER,
1975 x_govt_hecs_payment_option IN VARCHAR2,
1976 x_govt_hecs_cntrbtn_band IN NUMBER,
1977 x_chg_rate IN NUMBER,
1978 x_logical_delete_dt IN DATE,
1979 x_residency_status_cd IN VARCHAR2 ,
1980 x_course_cd IN VARCHAR2 ,
1981 x_version_number IN NUMBER ,
1982 x_org_party_id IN NUMBER ,
1983 x_class_standing IN VARCHAR2 ,
1984 x_mode IN VARCHAR2 ,
1985 x_unit_set_cd IN VARCHAR2,
1986 x_us_version_number IN NUMBER,
1987 x_unit_cd IN VARCHAR2 ,
1988 x_unit_version_number IN NUMBER ,
1989 x_unit_level IN VARCHAR2 ,
1990 x_unit_type_id IN NUMBER ,
1991 x_unit_class IN VARCHAR2 ,
1992 x_unit_mode IN VARCHAR2
1993 ) AS
1994 /*-----------------------------------------------------------------
1995 || Created By :
1996 || Created On :
1997 || Purpose :
1998 || Known limitations, enhancements or remarks :
1999 || Change History :
2000 || Who When What
2001 || (reverse chronological order - newest change first)
2002 || svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
2003 || Unit Version and Unit Level
2004 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
2005 || Added 2 new columns unit_set_cd and us_version_number
2006 --------------------------------------------------------------------*/
2007 CURSOR c1 IS
2008 SELECT rowid
2009 FROM igs_fi_fee_as_rate
2010 WHERE far_id = x_far_id ;
2011 BEGIN
2012 OPEN c1;
2013 FETCH c1 INTO x_rowid;
2014 IF (c1%NOTFOUND) THEN
2015 CLOSE c1;
2016 insert_row (
2017 x_rowid,
2018 x_far_id,
2019 x_fee_type,
2020 x_fee_cal_type,
2021 x_fee_ci_sequence_number,
2022 x_s_relation_type,
2023 x_rate_number,
2024 x_fee_cat,
2025 x_location_cd,
2026 x_attendance_type,
2027 x_attendance_mode,
2028 x_order_of_precedence,
2029 x_govt_hecs_payment_option,
2030 x_govt_hecs_cntrbtn_band,
2031 x_chg_rate,
2032 x_logical_delete_dt,
2033 x_residency_status_cd,
2034 x_course_cd,
2035 x_version_number,
2036 x_org_party_id,
2037 x_class_standing,
2038 x_mode,
2039 x_unit_set_cd,
2040 x_us_version_number,
2041 x_unit_cd,
2042 x_unit_version_number,
2043 x_unit_level,
2044 x_unit_type_id,
2045 x_unit_class,
2046 x_unit_mode
2047 );
2048 RETURN;
2049 END IF;
2050 CLOSE c1;
2051
2052 update_row (
2053 x_rowid,
2054 x_far_id,
2055 x_fee_type,
2056 x_fee_cal_type,
2057 x_fee_ci_sequence_number,
2058 x_s_relation_type,
2059 x_rate_number,
2060 x_fee_cat,
2061 x_location_cd,
2062 x_attendance_type,
2063 x_attendance_mode,
2064 x_order_of_precedence,
2065 x_govt_hecs_payment_option,
2066 x_govt_hecs_cntrbtn_band,
2067 x_chg_rate,
2068 x_logical_delete_dt,
2069 x_residency_status_cd,
2070 x_course_cd,
2071 x_version_number,
2072 x_org_party_id,
2073 x_class_standing,
2074 x_mode,
2075 x_unit_set_cd,
2076 x_us_version_number,
2077 x_unit_cd,
2078 x_unit_version_number,
2079 x_unit_level,
2080 x_unit_type_id,
2081 x_unit_class,
2082 x_unit_mode
2083 );
2084 END add_row;
2085
2086
2087 PROCEDURE delete_row (
2088 x_rowid IN VARCHAR2
2089 ) AS
2090 /*-----------------------------------------------------------------
2091 || Created By :
2092 || Created On :
2093 || Purpose :
2094 || Known limitations, enhancements or remarks :
2095 || Change History :
2096 || Who When What
2097 || (reverse chronological order - newest change first)
2098 --------------------------------------------------------------------*/
2099 BEGIN
2100 Before_DML( p_action =>'DELETE',
2101 x_rowid => X_ROWID
2102 );
2103 DELETE FROM igs_fi_fee_as_rate
2104 WHERE rowid = x_rowid;
2105 IF (SQL%NOTFOUND) THEN
2106 RAISE NO_DATA_FOUND;
2107 END IF;
2108 END delete_row;
2109
2110
2111 PROCEDURE beforerowupdate AS
2112 /*----------------------------------------------------------------------------
2113 || Created By : vchappid
2114 || Created On : 02-Jul-2002
2115 || Purpose : Will not allow any updation of attributes when the logical delete date is set.
2116 || Known limitations, enhancements or remarks :
2117 || Change History :
2118 || Who When What
2119 || (reverse chronological order - newest change first)
2120 ----------------------------------------------------------------------------*/
2121 BEGIN
2122 -- Bug#2409567, Will not allow any updation of attributes when the logical delete date is set.
2123 IF old_references.logical_delete_dt IS NOT NULL THEN
2124 fnd_message.set_name('IGS','IGS_FI_LOG_DEL_UPD_NOT_ALLOWED');
2125 IGS_GE_MSG_STACK.ADD;
2126 app_exception.raise_exception;
2127 END IF;
2128 END beforerowupdate;
2129
2130 PROCEDURE get_fk_igs_en_unit_set_all(
2131 x_unit_set_cd IN VARCHAR2,
2132 x_us_version_number IN NUMBER
2133 ) AS
2134 /*-----------------------------------------------------------------
2135 || Created By : Priya Athipatla
2136 || Created On : 17-Sep-2003
2137 || Purpose : To validate FK with igs_en_unit_set_all
2138 || Known limitations, enhancements or remarks :
2139 || Change History :
2140 || Who When What
2141 || (reverse chronological order - newest change first)
2142 --------------------------------------------------------------------*/
2143 CURSOR cur_rowid IS
2144 SELECT rowid
2145 FROM igs_fi_fee_as_rate
2146 WHERE unit_set_cd = x_unit_set_cd
2147 AND us_version_number = x_us_version_number;
2148
2149 lv_rowid cur_rowid%ROWTYPE;
2150
2151 BEGIN
2152
2153 OPEN cur_rowid;
2154 FETCH cur_rowid INTO lv_rowid;
2155 IF(cur_rowid%FOUND) THEN
2156 CLOSE cur_rowid;
2157 fnd_message.set_name('IGS', 'IGS_FI_FAR_EUS_FK');
2158 igs_ge_msg_stack.add;
2159 app_exception.raise_exception;
2160 RETURN;
2161 END IF;
2162 CLOSE cur_rowid;
2163 END get_fk_igs_en_unit_set_all;
2164
2165 END igs_fi_fee_as_rate_pkg;
[Home] [Help]