1 PACKAGE BODY igs_fi_fee_as_rt_h_pkg AS
2 /* $Header: IGSSI21B.pls 120.2 2006/05/26 13:42:37 skharida noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_FI_FEE_AS_RT_H_ALL%RowType;
6 new_references IGS_FI_FEE_AS_RT_H_ALL%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_chg_rate IN NUMBER ,
11 x_fee_type IN VARCHAR2 ,
12 x_fee_cal_type IN VARCHAR2 ,
13 x_fee_ci_sequence_number IN NUMBER ,
14 x_s_relation_type IN VARCHAR2 ,
15 x_rate_number IN NUMBER ,
16 x_hist_start_dt IN DATE ,
17 x_hist_end_dt IN DATE ,
18 x_hist_who IN VARCHAR2 ,
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_unit_class IN VARCHAR2 ,
27 x_residency_status_cd IN VARCHAR2 ,
28 x_course_cd IN VARCHAR2 ,
29 x_version_number IN NUMBER ,
30 x_org_party_id IN NUMBER ,
31 x_class_standing IN VARCHAR2 ,
32 x_org_id IN NUMBER ,
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_mode IN VARCHAR2
45
46 ) AS
47 /************************************************************************************
48 | HISTORY
49 | Who When What
50 | svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
51 | Unit Version and Unit Level
52 | pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
53 | Added 2 new columns unit_set_cd and us_version_number
54 **************************************************************************************/
55 CURSOR cur_old_ref_values IS
56 SELECT *
57 FROM IGS_FI_FEE_AS_RT_H_ALL
58 WHERE rowid = x_rowid;
59 BEGIN
60 l_rowid := x_rowid;
61 -- Code for setting the Old and New Reference Values.
62 -- Populate Old Values.
63 Open cur_old_ref_values;
64 Fetch cur_old_ref_values INTO old_references;
65 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
66 Close cur_old_ref_values;
67 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
68 IGS_GE_MSG_STACK.ADD;
69 App_Exception.Raise_Exception;
70 Return;
71 END IF;
72 Close cur_old_ref_values;
73 -- Populate New Values.
74 new_references.chg_rate := x_chg_rate;
75 new_references.fee_type := x_fee_type;
76 new_references.fee_cal_type := x_fee_cal_type;
77 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
78 new_references.s_relation_type := x_s_relation_type;
79 new_references.rate_number := x_rate_number;
80 new_references.hist_start_dt := x_hist_start_dt;
81 new_references.hist_end_dt := x_hist_end_dt;
82 new_references.hist_who := x_hist_who;
83 new_references.fee_cat := x_fee_cat;
84 new_references.location_cd := x_location_cd;
85 new_references.attendance_type := x_attendance_type;
86 new_references.attendance_mode := x_attendance_mode;
87 new_references.order_of_precedence := x_order_of_precedence;
88 new_references.govt_hecs_payment_option := x_govt_hecs_payment_option;
89 new_references.govt_hecs_cntrbtn_band := x_govt_hecs_cntrbtn_band;
90 new_references.unit_class := x_unit_class;
91 new_references.residency_status_cd := x_residency_status_cd;
92 new_references.course_cd := x_course_cd;
93 new_references.version_number := x_version_number;
94 new_references.org_party_id := x_org_party_id;
95 new_references.class_standing := x_class_standing;
96 new_references.org_id := x_org_id;
97 new_references.unit_set_cd := x_unit_set_cd;
98 new_references.us_version_number := x_us_version_number;
99 new_references.unit_cd := x_unit_cd ;
100 new_references.unit_version_number := x_unit_version_number ;
101 new_references.unit_level := x_unit_level ;
102 new_references.unit_type_id := x_unit_type_id ;
103 new_references.unit_mode := x_unit_mode ;
104
105
106 IF (p_action = 'UPDATE') THEN
107 new_references.creation_date := old_references.creation_date;
108 new_references.created_by := old_references.created_by;
109 ELSE
110 new_references.creation_date := x_creation_date;
111 new_references.created_by := x_created_by;
112 END IF;
113 new_references.last_update_date := x_last_update_date;
114 new_references.last_updated_by := x_last_updated_by;
115 new_references.last_update_login := x_last_update_login;
116 END Set_Column_Values;
117
118 PROCEDURE Check_Uniqueness AS
119 Begin
120 IF Get_UK_For_Validation (
121 new_references.fee_type ,
122 new_references.fee_cal_type ,
123 new_references.fee_ci_sequence_number ,
124 new_references.rate_number ,
125 new_references.hist_start_dt ,
126 new_references.fee_cat
127 ) THEN
128 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
129 IGS_GE_MSG_STACK.ADD;
130 App_Exception.Raise_Exception;
131 END IF;
132 End Check_Uniqueness;
133
134
135 PROCEDURE Check_Constraints (
136 Column_Name IN VARCHAR2 ,
137 Column_Value IN VARCHAR2
138 ) AS
139 /*----------------------------------------------------------------------------
140 || Created By :
141 || Created On :
142 || Purpose :
143 || Known limitations, enhancements or remarks :
144 || Change History :
145 || Who When What
146 || (reverse chronological order - newest change first)
147 || skharida 26-May-2006 Bug 5217319 Removed the hardcoded precision check
148 || svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
149 || Unit Version and Unit Level
150 || pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
151 || Added 2 new columns unit_set_cd and us_version_number
152 || vvutukur 21-Apr-2003 Bug#2885575.Modified the upper limit check to 999999999 for fields rate_number and order_of_precedence.
153 || SYKRISHn 10APR03 ORDER_OF_PRECEDENCE - Changes limit check to 9999
154 || vvutukur 17-May-2002 removed upper check on fee_type,fee_cat columns.bug#2344826.
155 ----------------------------------------------------------------------------*/
156 BEGIN
157 IF column_name is null then
158 NULL;
159 ELSIF upper(Column_name) = 'CHG_RATE' then
160 new_references.chg_rate := igs_ge_number.to_num(column_value);
161 ELSIF upper(Column_name) = 'FEE_CI_SEQUENCE_NUMBER' then
162 new_references.fee_ci_sequence_number := igs_ge_number.to_num(column_value);
163 ELSIF upper(Column_name) = 'ORDER_OF_PRECEDENCE' then
164 new_references.order_of_precedence := igs_ge_number.to_num(column_value);
165 ELSIF upper(Column_name) = 'ATTENDANCE_MODE' then
166 new_references.attendance_mode := column_value;
167 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' then
168 new_references.attendance_type := column_value;
169 ELSIF upper(Column_name) = 'FEE_CAL_TYPE' then
170 new_references.fee_cal_type := column_value;
171 ELSIF upper(Column_name) = 'GOVT_HECS_PAYMENT_OPTION' then
172 new_references.govt_hecs_payment_option := column_value;
173 ELSIF upper(Column_name) = 'LOCATION_CD' then
174 new_references.location_cd := column_value;
175 ELSIF upper(Column_name) = 'S_RELATION_TYPE' then
176 new_references.s_relation_type := column_value;
177 ELSIF upper(Column_name) = 'GOVT_HECS_CNTRBTN_BAND' then
178 new_references.govt_hecs_cntrbtn_band := igs_ge_number.to_num(column_value);
179 ELSIF upper(Column_name) = 'RATE_NUMBER' then
180 new_references.rate_number := igs_ge_number.to_num(column_value);
181 ELSIF upper(Column_name) = 'COURSE_CD' then
182 new_references.course_cd := column_value;
183 ELSIF upper(Column_name) = 'VERSION_NUMBER' then
184 new_references.version_number := igs_ge_number.to_num(column_value);
185 ELSIF upper(Column_name) = 'CLASS_STANDING' then
186 new_references.class_standing := column_value;
187 ELSIF upper(Column_name) = 'UNIT_SET_CD' then
188 new_references.unit_set_cd := column_value;
189 ELSIF upper(Column_name) = 'US_VERSION_NUMBER' then
190 new_references.us_version_number := igs_ge_number.to_num(column_value);
191 ELSIF (UPPER(column_name) = 'UNIT_VERSION_NUMBER') THEN
192 new_references.unit_version_number := igs_ge_number.to_num(column_value);
193 ELSIF (UPPER(column_name) = 'UNIT_TYPE_ID') THEN
194 new_references.unit_type_id := igs_ge_number.to_num(column_value);
195 ELSIF (UPPER (column_name) = 'UNIT_CD') THEN
196 new_references.unit_cd := column_value;
197 ELSIF (UPPER (column_name) = 'UNIT_LEVEL') THEN
198 new_references.unit_level := column_value;
199 ELSIF (UPPER (column_name) = 'UNIT_CLASS') THEN
200 new_references.unit_class := column_value;
201 ELSIF (UPPER(column_name) = 'UNIT_MODE') THEN
202 new_references.unit_mode := column_value;
203 END IF;
204
205 IF upper(column_name) = 'CHG_RATE' OR
206 column_name is null Then
207 IF new_references.chg_rate < 0 Then
208 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF;
212 END IF;
213 IF upper(column_name) = 'FEE_CI_SEQUENCE_NUMBER' OR
214 column_name is null Then
215 IF new_references.fee_ci_sequence_number < 1 OR
216 new_references.fee_ci_sequence_number > 999999 Then
217 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
218 IGS_GE_MSG_STACK.ADD;
219 App_Exception.Raise_Exception;
220 END IF;
221 END IF;
222 IF upper(column_name) = 'ORDER_OF_PRECEDENCE' OR
223 column_name is null Then
224 IF new_references.order_of_precedence < 1 OR
225 new_references.order_of_precedence > 999999999 Then
226 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
227 IGS_GE_MSG_STACK.ADD;
228 App_Exception.Raise_Exception;
229 END IF;
230 END IF;
231 IF upper(column_name) = 'GOVT_HECS_CNTRBTN_BAND' OR
232 column_name is null Then
233 IF new_references.govt_hecs_cntrbtn_band < 1 OR
234 new_references.govt_hecs_cntrbtn_band > 99 Then
235 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
236 IGS_GE_MSG_STACK.ADD;
237 App_Exception.Raise_Exception;
238 END IF;
239 END IF;
240 IF upper(column_name) = 'RATE_NUMBER' OR
241 column_name is null Then
242 IF new_references.rate_number < 1 OR
243 new_references.rate_number > 999999999 Then
244 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
245 IGS_GE_MSG_STACK.ADD;
246 App_Exception.Raise_Exception;
247 END IF;
248 END IF;
249
250 IF upper(column_name) = 'ATTENDANCE_MODE' OR
251 column_name is null Then
252 IF new_references.attendance_mode <>
253 UPPER(new_references.attendance_mode) Then
254 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
255 IGS_GE_MSG_STACK.ADD;
256 App_Exception.Raise_Exception;
257 END IF;
258 END IF;
259 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
260 column_name is null Then
261 IF new_references.attendance_type <>
262 UPPER(new_references.attendance_type) Then
263 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
264 IGS_GE_MSG_STACK.ADD;
265 App_Exception.Raise_Exception;
266 END IF;
267 END IF;
268 IF upper(column_name) = 'FEE_CAL_TYPE' OR
269 column_name is null Then
270 IF new_references.fee_cal_type <>
271 UPPER(new_references.fee_cal_type) Then
272 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
273 IGS_GE_MSG_STACK.ADD;
274 App_Exception.Raise_Exception;
275 END IF;
276 END IF;
277 IF upper(column_name) = 'GOVT_HECS_PAYMENT_OPTION' OR
278 column_name is null Then
279 IF new_references.govt_hecs_payment_option <>
280 UPPER(new_references.govt_hecs_payment_option) Then
281 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 END IF;
285 END IF;
286 IF upper(column_name) = 'LOCATION_CD' OR
287 column_name is null Then
288 IF new_references.location_cd <>
289 UPPER(new_references.location_cd) Then
290 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
291 IGS_GE_MSG_STACK.ADD;
292 App_Exception.Raise_Exception;
293 END IF;
294 END IF;
295 IF upper(column_name) = 'S_RELATION_TYPE' OR
296 column_name is null Then
297 IF new_references.s_relation_type <>
298 UPPER(new_references.s_relation_type) Then
299 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
300 IGS_GE_MSG_STACK.ADD;
301 App_Exception.Raise_Exception;
302 END IF;
303 END IF;
304 IF upper(column_name) = 'COURSE_CD' OR
305 column_name is null Then
306 IF new_references.course_cd <>
307 UPPER(new_references.course_cd) Then
308 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 END IF;
312 END IF;
313 IF upper(column_name) = 'VERSION_NUMBER' OR
314 column_name is null Then
315 IF new_references.version_number < 1 OR
316 new_references.version_number > 999 Then
317 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
318 IGS_GE_MSG_STACK.ADD;
319 App_Exception.Raise_Exception;
320 END IF;
321 END IF;
322 IF upper(column_name) = 'CLASS_STANDING' OR
323 column_name is null Then
324 IF new_references.class_standing <>
325 UPPER(new_references.class_standing) Then
326 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
327 IGS_GE_MSG_STACK.ADD;
328 App_Exception.Raise_Exception;
329 END IF;
330 END IF;
331 IF UPPER(column_name) = 'UNIT_SET_CD' OR column_name IS NULL THEN
332 IF new_references.unit_set_cd <> UPPER(new_references.unit_set_cd) THEN
333 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
334 igs_ge_msg_stack.add;
335 app_exception.raise_exception;
336 END IF;
337 END IF;
338 IF UPPER(column_name) = 'US_VERSION_NUMBER' OR column_name IS NULL THEN
339 IF new_references.us_version_number < 1 OR new_references.us_version_number > 999 Then
340 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
341 igs_ge_msg_stack.add;
342 app_exception.raise_exception;
343 END IF;
344 END IF;
345
346 IF ((UPPER(column_name) = 'UNIT_VERSION_NUMBER') OR (column_name IS NULL)) THEN
347 IF ((new_references.unit_version_number < 0) OR (new_references.unit_version_number > 999)) THEN
348 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
349 igs_ge_msg_stack.add;
350 app_exception.raise_exception;
351 END IF;
352 END IF;
353 IF ((UPPER(column_name) = 'UNIT_TYPE_ID') OR (column_name IS NULL)) THEN
354 IF (new_references.unit_type_id < 0) THEN
355 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
359 END IF;
356 igs_ge_msg_stack.add;
357 app_exception.raise_exception;
358 END IF;
360 IF ((UPPER (column_name) = 'UNIT_CD') OR (column_name IS NULL)) THEN
361 IF (new_references.unit_cd <> UPPER (new_references.unit_cd)) THEN
362 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
363 IGS_GE_MSG_STACK.ADD;
364 App_Exception.Raise_Exception;
365 END IF;
366 END IF;
367 IF ((UPPER(column_name) = 'UNIT_LEVEL') OR (column_name IS NULL)) THEN
368 IF (new_references.unit_level <> UPPER(new_references.unit_level)) THEN
369 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
370 igs_ge_msg_stack.add;
371 app_exception.raise_exception;
372 END IF;
373 END IF;
374 IF ((UPPER (column_name) = 'UNIT_CLASS') OR (column_name IS NULL)) THEN
375 IF (new_references.unit_class <> UPPER (new_references.unit_class)) THEN
376 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
377 IGS_GE_MSG_STACK.ADD;
378 App_Exception.Raise_Exception;
379 END IF;
380 END IF;
381 IF ((UPPER(column_name) = 'UNIT_MODE') OR (column_name IS NULL)) THEN
382 IF (new_references.unit_mode <> UPPER(new_references.unit_mode)) THEN
383 fnd_message.set_name('IGS', 'IGS_GE_INVALID_VALUE');
384 igs_ge_msg_stack.add;
385 app_exception.raise_exception;
386 END IF;
387 END IF;
388
389 END Check_Constraints;
390
391
392 PROCEDURE Check_Parent_Existance AS
393 BEGIN
394 IF (((old_references.fee_cat = new_references.fee_cat) AND
395 (old_references.fee_cal_type = new_references.fee_cal_type) AND
396 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number) AND
397 (old_references.fee_type = new_references.fee_type)) OR
398 ((new_references.fee_cat IS NULL) OR
399 (new_references.fee_cal_type IS NULL) OR
400 (new_references.fee_ci_sequence_number IS NULL) OR
401 (new_references.fee_type IS NULL))) THEN
402 NULL;
403 ELSE
404 IF NOT IGS_FI_F_CAT_FEE_LBL_PKG.Get_PK_For_Validation (
405 new_references.fee_cat,
406 new_references.fee_cal_type,
407 new_references.fee_ci_sequence_number,
408 new_references.fee_type
409 ) THEN
410 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
411 IGS_GE_MSG_STACK.ADD;
412 App_Exception.Raise_Exception;
413 END IF;
414 END IF;
415 IF (((old_references.fee_type = new_references.fee_type) AND
416 (old_references.fee_cal_type = new_references.fee_cal_type) AND
417 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
418 ((new_references.fee_type IS NULL) OR
419 (new_references.fee_cal_type IS NULL) OR
420 (new_references.fee_ci_sequence_number IS NULL))) THEN
421 NULL;
422 ELSE
423 IF NOT IGS_FI_F_TYP_CA_INST_PKG.Get_PK_For_Validation (
424 new_references.fee_type,
425 new_references.fee_cal_type,
426 new_references.fee_ci_sequence_number
427 ) THEN
428 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
429 IGS_GE_MSG_STACK.ADD;
430 App_Exception.Raise_Exception;
431 END IF;
432 END IF;
433 END Check_Parent_Existance;
434
435
436 FUNCTION Get_PK_For_Validation (
437 x_fee_type IN VARCHAR2,
438 x_fee_cal_type IN VARCHAR2,
439 x_fee_ci_sequence_number IN NUMBER,
440 x_s_relation_type IN VARCHAR2,
441 x_rate_number IN NUMBER,
442 x_hist_start_dt IN DATE
443 ) Return Boolean
444 AS
445 CURSOR cur_rowid IS
446 SELECT rowid
447 FROM IGS_FI_FEE_AS_RT_H_ALL
448 WHERE fee_type = x_fee_type
449 AND fee_cal_type = x_fee_cal_type
450 AND fee_ci_sequence_number = x_fee_ci_sequence_number
451 AND s_relation_type = x_s_relation_type
452 AND rate_number = x_rate_number
453 AND hist_start_dt = x_hist_start_dt
454 FOR UPDATE NOWAIT;
455 lv_rowid cur_rowid%RowType;
456 BEGIN
457 Open cur_rowid;
458 Fetch cur_rowid INTO lv_rowid;
459 IF (cur_rowid%FOUND) THEN
460 Close cur_rowid;
461 Return (TRUE);
462 ELSE
463 Close cur_rowid;
464 Return (FALSE);
465 END IF;
466 END Get_PK_For_Validation;
467
468
469 FUNCTION Get_UK_For_Validation (
470 x_fee_type IN VARCHAR2 ,
471 x_fee_cal_type IN VARCHAR2 ,
472 x_fee_ci_sequence_number IN NUMBER ,
473 x_rate_number IN NUMBER ,
474 x_hist_start_dt IN DATE ,
475 x_fee_cat IN VARCHAR2
476 ) Return Boolean
477 AS
478 CURSOR cur_rowid IS
479 SELECT rowid
480 FROM IGS_FI_FEE_AS_RT_H_ALL
481 WHERE fee_type = x_fee_type
482 AND fee_cal_type = x_fee_cal_type
483 AND fee_ci_sequence_number = x_fee_ci_sequence_number
484 AND rate_number = x_rate_number
485 AND hist_start_dt = x_hist_start_dt
486 AND fee_cat = x_fee_cat
490 BEGIN
487 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
488
489 lv_rowid cur_rowid%RowType;
491 Open cur_rowid;
492 Fetch cur_rowid INTO lv_rowid;
493 IF (cur_rowid%FOUND) THEN
494 Close cur_rowid;
495 Return (TRUE);
496 ELSE
497 Close cur_rowid;
498 Return (FALSE);
499 END IF;
500 END Get_UK_For_Validation;
501
502
503 PROCEDURE Before_DML (
504 p_action IN VARCHAR2,
505 x_rowid IN VARCHAR2 ,
506 x_chg_rate IN NUMBER ,
507 x_fee_type IN VARCHAR2 ,
508 x_fee_cal_type IN VARCHAR2 ,
509 x_fee_ci_sequence_number IN NUMBER ,
510 x_s_relation_type IN VARCHAR2 ,
511 x_rate_number IN NUMBER ,
512 x_hist_start_dt IN DATE ,
513 x_hist_end_dt IN DATE ,
514 x_hist_who IN VARCHAR2 ,
515 x_fee_cat IN VARCHAR2 ,
516 x_location_cd IN VARCHAR2 ,
517 x_attendance_type IN VARCHAR2 ,
518 x_attendance_mode IN VARCHAR2 ,
519 x_order_of_precedence IN NUMBER ,
520 x_govt_hecs_payment_option IN VARCHAR2 ,
521 x_govt_hecs_cntrbtn_band IN NUMBER ,
522 x_unit_class IN VARCHAR2 ,
523 x_residency_status_cd IN VARCHAR2 ,
524 x_course_cd IN VARCHAR2 ,
525 x_version_number IN NUMBER ,
526 x_org_party_id IN NUMBER ,
527 x_class_standing IN VARCHAR2 ,
528 x_org_id IN NUMBER ,
529 x_creation_date IN DATE ,
530 x_created_by IN NUMBER ,
531 x_last_update_date IN DATE ,
532 x_last_updated_by IN NUMBER ,
533 x_last_update_login IN NUMBER,
534 x_unit_set_cd IN VARCHAR2,
535 x_us_version_number IN NUMBER,
536 x_unit_cd IN VARCHAR2 ,
537 x_unit_version_number IN NUMBER ,
538 x_unit_level IN VARCHAR2 ,
539 x_unit_type_id IN NUMBER ,
540 x_unit_mode IN VARCHAR2
541 ) AS
542 /************************************************************************************
543 | HISTORY
544 | Who When What
545 | svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
546 | Unit Version and Unit Level
547 | pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
548 | Added 2 new columns unit_set_cd and us_version_number
549 **************************************************************************************/
550 BEGIN
551 Set_Column_Values (
552 p_action,
553 x_rowid,
554 x_chg_rate,
555 x_fee_type,
556 x_fee_cal_type,
557 x_fee_ci_sequence_number,
558 x_s_relation_type,
559 x_rate_number,
560 x_hist_start_dt,
561 x_hist_end_dt,
562 x_hist_who,
563 x_fee_cat,
564 x_location_cd,
565 x_attendance_type,
566 x_attendance_mode,
567 x_order_of_precedence,
568 x_govt_hecs_payment_option,
569 x_govt_hecs_cntrbtn_band,
570 x_unit_class,
571 x_residency_status_cd,
572 x_course_cd,
573 x_version_number,
574 x_org_party_id,
575 x_class_standing,
576 x_org_id,
577 x_creation_date,
578 x_created_by,
579 x_last_update_date,
580 x_last_updated_by,
581 x_last_update_login,
582 x_unit_set_cd,
583 x_us_version_number,
584 x_unit_cd ,
585 x_unit_version_number,
586 x_unit_level,
587 x_unit_type_id,
588 x_unit_mode
589 );
590 IF (p_action = 'INSERT') THEN
591 -- Call all the procedures related to Before Insert.
592 Null;
593 IF Get_PK_For_Validation (
594 new_references.fee_type ,
595 new_references.fee_cal_type ,
596 new_references.fee_ci_sequence_number ,
597 new_references.s_relation_type ,
598 new_references.rate_number ,
599 new_references.hist_start_dt
600 ) THEN
601 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
602 IGS_GE_MSG_STACK.ADD;
603 App_Exception.Raise_Exception;
604 END IF;
605 Check_Constraints;
606 Check_Uniqueness;
607 Check_Parent_Existance;
608 ELSIF (p_action = 'UPDATE') THEN
609 -- Call all the procedures related to Before Update.
610 Null;
611 Check_Constraints;
612 Check_Uniqueness;
613 Check_Parent_Existance;
614 ELSIF (p_action = 'DELETE') THEN
615 -- Call all the procedures related to Before Delete.
616 Null;
617 ELSIF (p_action = 'VALIDATE_INSERT') THEN
618 IF Get_PK_For_Validation (
619 new_references.fee_type ,
620 new_references.fee_cal_type ,
621 new_references.fee_ci_sequence_number ,
622 new_references.s_relation_type ,
623 new_references.rate_number ,
624 new_references.hist_start_dt
625 ) THEN
629 END IF;
626 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
627 IGS_GE_MSG_STACK.ADD;
628 App_Exception.Raise_Exception;
630 Check_Constraints;
631 Check_Uniqueness;
632 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
633 Check_Constraints;
634 Check_Uniqueness;
635 ELSIF (p_action = 'VALIDATE_DELETE') THEN
636 Null;
637 END IF;
638 END Before_DML;
639
640
641 procedure INSERT_ROW (
642 X_ROWID in out NOCOPY VARCHAR2,
643 X_FEE_TYPE in VARCHAR2,
644 X_FEE_CAL_TYPE in VARCHAR2,
645 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
646 X_S_RELATION_TYPE in VARCHAR2,
647 X_HIST_START_DT in DATE,
648 X_RATE_NUMBER in NUMBER,
649 X_HIST_END_DT in DATE,
650 X_HIST_WHO in NUMBER,
651 X_FEE_CAT in VARCHAR2,
652 X_LOCATION_CD in VARCHAR2,
653 X_ATTENDANCE_TYPE in VARCHAR2,
654 X_ATTENDANCE_MODE in VARCHAR2,
655 X_ORDER_OF_PRECEDENCE in NUMBER,
656 X_GOVT_HECS_PAYMENT_OPTION in VARCHAR2,
657 X_GOVT_HECS_CNTRBTN_BAND in NUMBER,
658 X_CHG_RATE in NUMBER,
659 X_UNIT_CLASS IN VARCHAR2,
660 X_RESIDENCY_STATUS_CD in VARCHAR2 ,
661 X_COURSE_CD in VARCHAR2 ,
662 X_VERSION_NUMBER in NUMBER ,
663 X_ORG_PARTY_ID in NUMBER ,
664 X_CLASS_STANDING in VARCHAR2 ,
665 X_ORG_ID in NUMBER,
666 X_MODE in VARCHAR2,
667 x_unit_set_cd IN VARCHAR2,
668 x_us_version_number IN NUMBER,
669 x_unit_cd IN VARCHAR2 ,
670 x_unit_version_number IN NUMBER ,
671 x_unit_level IN VARCHAR2 ,
672 x_unit_type_id IN NUMBER ,
673 x_unit_mode IN VARCHAR2
674 ) AS
675 /************************************************************************************
676 | HISTORY
677 | Who When What
678 | svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
679 | Unit Version and Unit Level
680 | pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
681 | Added 2 new columns unit_set_cd and us_version_number
682 **************************************************************************************/
683 cursor C is select ROWID from IGS_FI_FEE_AS_RT_H_ALL
684 where FEE_TYPE = X_FEE_TYPE
685 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
686 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
687 and S_RELATION_TYPE = X_S_RELATION_TYPE
688 and HIST_START_DT = X_HIST_START_DT
689 and RATE_NUMBER = X_RATE_NUMBER;
690 X_LAST_UPDATE_DATE DATE;
691 X_LAST_UPDATED_BY NUMBER;
692 X_LAST_UPDATE_LOGIN NUMBER;
693 begin
694 X_LAST_UPDATE_DATE := SYSDATE;
695 if(X_MODE = 'I') then
696 X_LAST_UPDATED_BY := 1;
697 X_LAST_UPDATE_LOGIN := 0;
698 elsif (X_MODE = 'R') then
699 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
700 if X_LAST_UPDATED_BY is NULL then
701 X_LAST_UPDATED_BY := -1;
702 end if;
703 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
704 if X_LAST_UPDATE_LOGIN is NULL then
705 X_LAST_UPDATE_LOGIN := -1;
706 end if;
707 else
708 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
709 IGS_GE_MSG_STACK.ADD;
710 app_exception.raise_exception;
711 end if;
712 Before_DML(
713 p_action=>'INSERT',
714 x_rowid=>X_ROWID,
715 x_attendance_mode=>X_ATTENDANCE_MODE,
716 x_attendance_type=>X_ATTENDANCE_TYPE,
717 x_chg_rate=>X_CHG_RATE,
718 x_fee_cal_type=>X_FEE_CAL_TYPE,
719 x_fee_cat=>X_FEE_CAT,
720 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
721 x_fee_type=>X_FEE_TYPE,
722 x_govt_hecs_cntrbtn_band=>X_GOVT_HECS_CNTRBTN_BAND,
723 x_govt_hecs_payment_option=>X_GOVT_HECS_PAYMENT_OPTION,
724 x_hist_end_dt=>X_HIST_END_DT,
725 x_hist_start_dt=>X_HIST_START_DT,
726 x_hist_who=>X_HIST_WHO,
727 x_location_cd=>X_LOCATION_CD,
728 x_order_of_precedence=>X_ORDER_OF_PRECEDENCE,
729 x_rate_number=>X_RATE_NUMBER,
730 x_s_relation_type=>X_S_RELATION_TYPE,
731 x_unit_class => X_UNIT_CLASS,
732 x_residency_status_cd => X_RESIDENCY_STATUS_CD,
733 x_course_cd => X_COURSE_CD,
734 x_version_number => X_VERSION_NUMBER,
735 x_org_party_id => X_ORG_PARTY_ID,
736 x_class_standing => X_CLASS_STANDING,
737 x_org_id => igs_ge_gen_003.get_org_id,
738 x_creation_date => X_LAST_UPDATE_DATE,
739 x_created_by => X_LAST_UPDATED_BY,
740 x_last_update_date => X_LAST_UPDATE_DATE,
741 x_last_updated_by => X_LAST_UPDATED_BY,
742 x_last_update_login => X_LAST_UPDATE_LOGIN,
743 x_unit_set_cd => x_unit_set_cd,
744 x_us_version_number => x_us_version_number,
745 x_unit_cd => x_unit_cd,
746 x_unit_version_number => x_unit_version_number,
747 x_unit_level => x_unit_level ,
748 x_unit_type_id => x_unit_type_id,
749 x_unit_mode => x_unit_mode
750 );
751
752 insert into IGS_FI_FEE_AS_RT_H_ALL (
753 FEE_TYPE,
754 FEE_CAL_TYPE,
755 FEE_CI_SEQUENCE_NUMBER,
756 S_RELATION_TYPE,
757 RATE_NUMBER,
758 HIST_START_DT,
759 HIST_END_DT,
760 HIST_WHO,
761 FEE_CAT,
762 LOCATION_CD,
763 ATTENDANCE_TYPE,
767 GOVT_HECS_CNTRBTN_BAND,
764 ATTENDANCE_MODE,
765 ORDER_OF_PRECEDENCE,
766 GOVT_HECS_PAYMENT_OPTION,
768 CHG_RATE,
769 UNIT_CLASS,
770 RESIDENCY_STATUS_CD,
771 COURSE_CD,
772 VERSION_NUMBER,
773 ORG_PARTY_ID,
774 CLASS_STANDING,
775 ORG_ID,
776 CREATION_DATE,
777 CREATED_BY,
778 LAST_UPDATE_DATE,
779 LAST_UPDATED_BY,
780 LAST_UPDATE_LOGIN,
781 unit_set_cd,
782 us_version_number,
783 unit_cd ,
784 unit_version_number,
785 unit_level ,
786 unit_type_id,
787 unit_mode
788 ) values (
789 NEW_REFERENCES.FEE_TYPE,
790 NEW_REFERENCES.FEE_CAL_TYPE,
791 NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
792 NEW_REFERENCES.S_RELATION_TYPE,
793 NEW_REFERENCES.RATE_NUMBER,
794 NEW_REFERENCES.HIST_START_DT,
795 NEW_REFERENCES.HIST_END_DT,
796 NEW_REFERENCES.HIST_WHO,
797 NEW_REFERENCES.FEE_CAT,
798 NEW_REFERENCES.LOCATION_CD,
799 NEW_REFERENCES.ATTENDANCE_TYPE,
800 NEW_REFERENCES.ATTENDANCE_MODE,
801 NEW_REFERENCES.ORDER_OF_PRECEDENCE,
802 NEW_REFERENCES.GOVT_HECS_PAYMENT_OPTION,
803 NEW_REFERENCES.GOVT_HECS_CNTRBTN_BAND,
804 NEW_REFERENCES.CHG_RATE,
805 NEW_REFERENCES.UNIT_CLASS,
806 NEW_REFERENCES.RESIDENCY_STATUS_CD,
807 NEW_REFERENCES.COURSE_CD,
808 NEW_REFERENCES.VERSION_NUMBER,
809 NEW_REFERENCES.ORG_PARTY_ID,
810 NEW_REFERENCES.CLASS_STANDING,
811 NEW_REFERENCES.ORG_ID,
812 X_LAST_UPDATE_DATE,
813 X_LAST_UPDATED_BY,
814 X_LAST_UPDATE_DATE,
815 X_LAST_UPDATED_BY,
816 X_LAST_UPDATE_LOGIN,
817 new_references.unit_set_cd,
818 new_references.us_version_number,
819 new_references.unit_cd,
820 new_references.unit_version_number,
821 new_references.unit_level ,
822 new_references.unit_type_id ,
823 new_references.unit_mode
824 );
825
826 open c;
827 fetch c into X_ROWID;
828 if (c%notfound) then
829 close c;
830 raise no_data_found;
831 end if;
832 close c;
833 end INSERT_ROW;
834
835
836 procedure LOCK_ROW (
837 X_ROWID in VARCHAR2,
838 X_FEE_TYPE in VARCHAR2,
839 X_FEE_CAL_TYPE in VARCHAR2,
840 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
841 X_S_RELATION_TYPE in VARCHAR2,
842 X_HIST_START_DT in DATE,
843 X_RATE_NUMBER in NUMBER,
844 X_HIST_END_DT in DATE,
845 X_HIST_WHO in NUMBER,
846 X_FEE_CAT in VARCHAR2,
847 X_LOCATION_CD in VARCHAR2,
848 X_ATTENDANCE_TYPE in VARCHAR2,
849 X_ATTENDANCE_MODE in VARCHAR2,
850 X_ORDER_OF_PRECEDENCE in NUMBER,
851 X_GOVT_HECS_PAYMENT_OPTION in VARCHAR2,
852 X_GOVT_HECS_CNTRBTN_BAND in NUMBER,
853 X_CHG_RATE in NUMBER,
854 X_UNIT_CLASS IN VARCHAR2,
855 X_RESIDENCY_STATUS_CD in VARCHAR2 ,
856 X_COURSE_CD in VARCHAR2 ,
857 X_VERSION_NUMBER in NUMBER ,
858 X_ORG_PARTY_ID in NUMBER ,
859 X_CLASS_STANDING in VARCHAR2,
860 x_unit_set_cd IN VARCHAR2,
861 x_us_version_number IN NUMBER,
862 x_unit_cd IN VARCHAR2 ,
863 x_unit_version_number IN NUMBER ,
864 x_unit_level IN VARCHAR2 ,
865 x_unit_type_id IN NUMBER ,
866 x_unit_mode IN VARCHAR2
867 ) AS
868 /************************************************************************************
869 | HISTORY
870 | Who When What
871 | svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
872 | Unit Version and Unit Level
873 | pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
874 | Added 2 new columns unit_set_cd and us_version_number
875 **************************************************************************************/
876 cursor c1 is select
877 HIST_END_DT,
878 HIST_WHO,
879 FEE_CAT,
880 LOCATION_CD,
881 ATTENDANCE_TYPE,
882 ATTENDANCE_MODE,
883 ORDER_OF_PRECEDENCE,
884 GOVT_HECS_PAYMENT_OPTION,
885 GOVT_HECS_CNTRBTN_BAND,
886 CHG_RATE,
887 UNIT_CLASS,
888 RESIDENCY_STATUS_CD,
889 COURSE_CD,
890 VERSION_NUMBER,
891 ORG_PARTY_ID,
892 CLASS_STANDING,
893 unit_set_cd,
894 us_version_number,
895 unit_cd,
896 unit_version_number,
897 unit_level ,
898 unit_type_id ,
899 unit_mode
900 from IGS_FI_FEE_AS_RT_H_ALL
901 where ROWID = X_ROWID
902 for update nowait;
903 tlinfo c1%rowtype;
904 begin
905 open c1;
906 fetch c1 into tlinfo;
907 if (c1%notfound) then
908 close c1;
909 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
910 IGS_GE_MSG_STACK.ADD;
911 app_exception.raise_exception;
912 return;
913 end if;
914 close c1;
915 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
916 AND (tlinfo.HIST_WHO = X_HIST_WHO)
917 AND ((tlinfo.FEE_CAT = X_FEE_CAT)
918 OR ((tlinfo.FEE_CAT is null)
919 AND (X_FEE_CAT is null)))
920 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
921 OR ((tlinfo.LOCATION_CD is null)
922 AND (X_LOCATION_CD is null)))
923 AND ((tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
924 OR ((tlinfo.ATTENDANCE_TYPE is null)
925 AND (X_ATTENDANCE_TYPE is null)))
926 AND ((tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
927 OR ((tlinfo.ATTENDANCE_MODE is null)
928 AND (X_ATTENDANCE_MODE is null)))
929 AND ((tlinfo.ORDER_OF_PRECEDENCE = X_ORDER_OF_PRECEDENCE)
930 OR ((tlinfo.ORDER_OF_PRECEDENCE is null)
931 AND (X_ORDER_OF_PRECEDENCE is null)))
932 AND ((tlinfo.GOVT_HECS_PAYMENT_OPTION = X_GOVT_HECS_PAYMENT_OPTION)
933 OR ((tlinfo.GOVT_HECS_PAYMENT_OPTION is null)
934 AND (X_GOVT_HECS_PAYMENT_OPTION is null)))
935 AND ((tlinfo.GOVT_HECS_CNTRBTN_BAND = X_GOVT_HECS_CNTRBTN_BAND)
936 OR ((tlinfo.GOVT_HECS_CNTRBTN_BAND is null)
937 AND (X_GOVT_HECS_CNTRBTN_BAND is null)))
938 AND ((tlinfo.CHG_RATE = X_CHG_RATE)
939 OR ((tlinfo.CHG_RATE is null)
940 AND (X_CHG_RATE is null)))
941 AND (tlinfo.UNIT_CLASS = X_UNIT_CLASS
942 OR (tlinfo.UNIT_CLASS is null
943 AND X_UNIT_CLASS is null))
944 AND (tlinfo.RESIDENCY_STATUS_CD = X_RESIDENCY_STATUS_CD
945 OR (tlinfo.RESIDENCY_STATUS_CD is null
946 AND X_RESIDENCY_STATUS_CD is null))
947 AND (tlinfo.COURSE_CD = X_COURSE_CD
948 OR (tlinfo.COURSE_CD is null
949 AND X_COURSE_CD is null))
950 AND (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER
951 OR (tlinfo.VERSION_NUMBER is null
952 AND X_VERSION_NUMBER is null))
953 AND (tlinfo.ORG_PARTY_ID = X_ORG_PARTY_ID OR (tlinfo.ORG_PARTY_ID is null AND X_ORG_PARTY_ID is null))
954 AND (tlinfo.CLASS_STANDING = X_CLASS_STANDING OR (tlinfo.CLASS_STANDING is null AND X_CLASS_STANDING is null))
955 AND (tlinfo.unit_set_cd = x_unit_set_cd OR (tlinfo.unit_set_cd IS NULL AND x_unit_set_cd IS NULL))
956 AND (tlinfo.us_version_number = x_us_version_number OR (tlinfo.us_version_number IS NULL AND x_us_version_number IS NULL))
957 AND ((tlinfo.unit_cd = x_unit_cd) OR ((tlinfo.unit_cd IS NULL) AND (x_unit_cd IS NULL)))
958 AND ((tlinfo.unit_version_number = x_unit_version_number) OR ((tlinfo.unit_version_number IS NULL) AND (x_unit_version_number IS NULL)))
959 AND ((tlinfo.unit_level = x_unit_level) OR ((tlinfo.unit_level IS NULL) AND (x_unit_level IS NULL)))
960 AND ((tlinfo.unit_type_id = x_unit_type_id) OR ((tlinfo.unit_type_id IS NULL) AND (x_unit_type_id IS NULL)))
961 AND ((tlinfo.unit_mode = x_unit_mode) OR ((tlinfo.unit_mode IS NULL) AND (x_unit_mode IS NULL)))
962 ) then
963 null;
964 else
965 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
966 IGS_GE_MSG_STACK.ADD;
967 app_exception.raise_exception;
968 end if;
969 return;
970 end LOCK_ROW;
971
972
973 procedure UPDATE_ROW (
974 X_ROWID in VARCHAR2,
975 X_FEE_TYPE in VARCHAR2,
976 X_FEE_CAL_TYPE in VARCHAR2,
977 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
978 X_S_RELATION_TYPE in VARCHAR2,
979 X_HIST_START_DT in DATE,
980 X_RATE_NUMBER in NUMBER,
981 X_HIST_END_DT in DATE,
982 X_HIST_WHO in NUMBER,
983 X_FEE_CAT in VARCHAR2,
984 X_LOCATION_CD in VARCHAR2,
985 X_ATTENDANCE_TYPE in VARCHAR2,
986 X_ATTENDANCE_MODE in VARCHAR2,
987 X_ORDER_OF_PRECEDENCE in NUMBER,
988 X_GOVT_HECS_PAYMENT_OPTION in VARCHAR2,
989 X_GOVT_HECS_CNTRBTN_BAND in NUMBER,
990 X_CHG_RATE in NUMBER,
991 X_UNIT_CLASS in VARCHAR2,
992 X_RESIDENCY_STATUS_CD in VARCHAR2 ,
993 X_COURSE_CD in VARCHAR2 ,
994 X_VERSION_NUMBER in NUMBER ,
995 X_ORG_PARTY_ID in NUMBER ,
996 X_CLASS_STANDING in VARCHAR2 ,
997 X_MODE in VARCHAR2,
1001 x_unit_version_number IN NUMBER ,
998 x_unit_set_cd IN VARCHAR2,
999 x_us_version_number IN NUMBER,
1000 x_unit_cd IN VARCHAR2 ,
1002 x_unit_level IN VARCHAR2 ,
1003 x_unit_type_id IN NUMBER ,
1004 x_unit_mode IN VARCHAR2
1005 ) AS
1006 /************************************************************************************
1007 | HISTORY
1008 | Who When What
1009 | svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
1010 | Unit Version and Unit Level
1011 | pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
1012 | Added 2 new columns unit_set_cd and us_version_number
1013 **************************************************************************************/
1014 X_LAST_UPDATE_DATE DATE;
1015 X_LAST_UPDATED_BY NUMBER;
1016 X_LAST_UPDATE_LOGIN NUMBER;
1017 begin
1018 X_LAST_UPDATE_DATE := SYSDATE;
1019 if(X_MODE = 'I') then
1020 X_LAST_UPDATED_BY := 1;
1021 X_LAST_UPDATE_LOGIN := 0;
1022 elsif (X_MODE = 'R') then
1023 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1024 if X_LAST_UPDATED_BY is NULL then
1025 X_LAST_UPDATED_BY := -1;
1026 end if;
1027 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1028 if X_LAST_UPDATE_LOGIN is NULL then
1029 X_LAST_UPDATE_LOGIN := -1;
1030 end if;
1031 else
1032 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1033 IGS_GE_MSG_STACK.ADD;
1034 app_exception.raise_exception;
1035 end if;
1036 Before_DML(
1037 p_action=>'UPDATE',
1038 x_rowid=>X_ROWID,
1039 x_attendance_mode=>X_ATTENDANCE_MODE,
1040 x_attendance_type=>X_ATTENDANCE_TYPE,
1041 x_chg_rate=>X_CHG_RATE,
1042 x_fee_cal_type=>X_FEE_CAL_TYPE,
1043 x_fee_cat=>X_FEE_CAT,
1044 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
1045 x_fee_type=>X_FEE_TYPE,
1046 x_govt_hecs_cntrbtn_band=>X_GOVT_HECS_CNTRBTN_BAND,
1047 x_govt_hecs_payment_option=>X_GOVT_HECS_PAYMENT_OPTION,
1048 x_hist_end_dt=>X_HIST_END_DT,
1049 x_hist_start_dt=>X_HIST_START_DT,
1050 x_hist_who=>X_HIST_WHO,
1051 x_location_cd=>X_LOCATION_CD,
1052 x_order_of_precedence=>X_ORDER_OF_PRECEDENCE,
1053 x_rate_number=>X_RATE_NUMBER,
1054 x_s_relation_type=>X_S_RELATION_TYPE,
1055 x_unit_class => X_UNIT_CLASS,
1056 x_residency_status_cd => X_RESIDENCY_STATUS_CD,
1057 x_course_cd => X_COURSE_CD,
1058 x_version_number => X_VERSION_NUMBER,
1059 x_org_party_id => X_ORG_PARTY_ID,
1060 x_class_standing => X_CLASS_STANDING,
1061 x_creation_date => X_LAST_UPDATE_DATE,
1062 x_created_by => X_LAST_UPDATED_BY,
1063 x_last_update_date => X_LAST_UPDATE_DATE,
1064 x_last_updated_by => X_LAST_UPDATED_BY,
1065 x_last_update_login => X_LAST_UPDATE_LOGIN,
1066 x_unit_set_cd => x_unit_set_cd,
1067 x_us_version_number => x_us_version_number,
1068 x_unit_cd => x_unit_cd,
1069 x_unit_version_number => x_unit_version_number,
1070 x_unit_level => x_unit_level ,
1071 x_unit_type_id => x_unit_type_id,
1072 x_unit_mode => x_unit_mode
1073 );
1074 update IGS_FI_FEE_AS_RT_H_ALL set
1075 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
1076 HIST_WHO = NEW_REFERENCES.HIST_WHO,
1077 FEE_CAT = NEW_REFERENCES.FEE_CAT,
1078 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
1079 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
1080 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
1081 ORDER_OF_PRECEDENCE = NEW_REFERENCES.ORDER_OF_PRECEDENCE,
1082 GOVT_HECS_PAYMENT_OPTION = NEW_REFERENCES.GOVT_HECS_PAYMENT_OPTION,
1083 GOVT_HECS_CNTRBTN_BAND = NEW_REFERENCES.GOVT_HECS_CNTRBTN_BAND,
1084 CHG_RATE = NEW_REFERENCES.CHG_RATE,
1085 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
1086 RESIDENCY_STATUS_CD = NEW_REFERENCES.RESIDENCY_STATUS_CD,
1087 COURSE_CD = NEW_REFERENCES.COURSE_CD,
1088 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
1089 ORG_PARTY_ID = NEW_REFERENCES.ORG_PARTY_ID,
1090 CLASS_STANDING = NEW_REFERENCES.CLASS_STANDING,
1091 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1092 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1093 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1094 unit_set_cd = new_references.unit_set_cd,
1095 us_version_number = new_references.us_version_number,
1096 unit_cd = new_references.unit_cd,
1097 unit_version_number = new_references.unit_version_number,
1098 unit_level = new_references.unit_level ,
1099 unit_type_id = new_references.unit_type_id,
1100 unit_mode = new_references.unit_mode
1101 where ROWID = X_ROWID;
1102 if (sql%notfound) then
1103 raise no_data_found;
1104 end if;
1105 end UPDATE_ROW;
1106
1107
1108 procedure ADD_ROW (
1109 X_ROWID in out NOCOPY VARCHAR2,
1110 X_FEE_TYPE in VARCHAR2,
1111 X_FEE_CAL_TYPE in VARCHAR2,
1112 X_FEE_CI_SEQUENCE_NUMBER in NUMBER,
1113 X_S_RELATION_TYPE in VARCHAR2,
1114 X_HIST_START_DT in DATE,
1115 X_RATE_NUMBER in NUMBER,
1116 X_HIST_END_DT in DATE,
1117 X_HIST_WHO in NUMBER,
1118 X_FEE_CAT in VARCHAR2,
1119 X_LOCATION_CD in VARCHAR2,
1120 X_ATTENDANCE_TYPE in VARCHAR2,
1121 X_ATTENDANCE_MODE in VARCHAR2,
1122 X_ORDER_OF_PRECEDENCE in NUMBER,
1123 X_GOVT_HECS_PAYMENT_OPTION in VARCHAR2,
1124 X_GOVT_HECS_CNTRBTN_BAND in NUMBER,
1125 X_CHG_RATE in NUMBER,
1126 X_UNIT_CLASS in VARCHAR2,
1127 X_RESIDENCY_STATUS_CD in VARCHAR2 ,
1128 X_COURSE_CD in VARCHAR2 ,
1129 X_VERSION_NUMBER in NUMBER ,
1130 X_ORG_PARTY_ID in NUMBER ,
1131 X_CLASS_STANDING in VARCHAR2 ,
1132 X_ORG_ID in NUMBER,
1133 X_MODE in VARCHAR2,
1134 x_unit_set_cd IN VARCHAR2,
1135 x_us_version_number IN NUMBER,
1136 x_unit_cd IN VARCHAR2 ,
1137 x_unit_version_number IN NUMBER ,
1138 x_unit_level IN VARCHAR2 ,
1139 x_unit_type_id IN NUMBER ,
1140 x_unit_mode IN VARCHAR2
1141 ) AS
1142 /************************************************************************************
1143 | HISTORY
1144 | Who When What
1145 | svuppala 31-MAY-2005 Enh 3442712: Added Unit Program Type Level, Unit Mode, Unit Class, Unit Code,
1146 | Unit Version and Unit Level
1147 | pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
1148 | Added 2 new columns unit_set_cd and us_version_number
1149 **************************************************************************************/
1150 cursor c1 is select rowid from IGS_FI_FEE_AS_RT_H_ALL
1151 where FEE_TYPE = X_FEE_TYPE
1152 and FEE_CAL_TYPE = X_FEE_CAL_TYPE
1153 and FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER
1154 and S_RELATION_TYPE = X_S_RELATION_TYPE
1155 and HIST_START_DT = X_HIST_START_DT
1156 and RATE_NUMBER = X_RATE_NUMBER ;
1157 begin
1158 open c1;
1159 fetch c1 into X_ROWID;
1160 if (c1%notfound) then
1161 close c1;
1162 INSERT_ROW (
1163 X_ROWID,
1164 X_FEE_TYPE,
1165 X_FEE_CAL_TYPE,
1166 X_FEE_CI_SEQUENCE_NUMBER,
1167 X_S_RELATION_TYPE,
1168 X_HIST_START_DT,
1169 X_RATE_NUMBER,
1170 X_HIST_END_DT,
1171 X_HIST_WHO,
1172 X_FEE_CAT,
1173 X_LOCATION_CD,
1174 X_ATTENDANCE_TYPE,
1175 X_ATTENDANCE_MODE,
1176 X_ORDER_OF_PRECEDENCE,
1177 X_GOVT_HECS_PAYMENT_OPTION,
1178 X_GOVT_HECS_CNTRBTN_BAND,
1179 X_CHG_RATE,
1180 X_UNIT_CLASS,
1181 X_RESIDENCY_STATUS_CD,
1182 X_COURSE_CD,
1183 X_VERSION_NUMBER,
1184 X_ORG_PARTY_ID,
1185 X_CLASS_STANDING,
1186 X_ORG_ID,
1187 X_MODE,
1188 x_unit_set_cd,
1189 x_us_version_number,
1190 x_unit_cd,
1191 x_unit_version_number,
1192 x_unit_level,
1193 x_unit_type_id,
1194 x_unit_mode
1195 );
1196 return;
1197 end if;
1198
1199 close c1;
1200
1201 UPDATE_ROW (
1202 X_ROWID,
1203 X_FEE_TYPE,
1204 X_FEE_CAL_TYPE,
1205 X_FEE_CI_SEQUENCE_NUMBER,
1206 X_S_RELATION_TYPE,
1207 X_HIST_START_DT,
1208 X_RATE_NUMBER,
1209 X_HIST_END_DT,
1210 X_HIST_WHO,
1211 X_FEE_CAT,
1212 X_LOCATION_CD,
1213 X_ATTENDANCE_TYPE,
1214 X_ATTENDANCE_MODE,
1215 X_ORDER_OF_PRECEDENCE,
1216 X_GOVT_HECS_PAYMENT_OPTION,
1217 X_GOVT_HECS_CNTRBTN_BAND,
1218 X_CHG_RATE,
1219 X_UNIT_CLASS,
1220 X_RESIDENCY_STATUS_CD,
1221 X_COURSE_CD,
1222 X_VERSION_NUMBER,
1223 X_ORG_PARTY_ID,
1224 X_CLASS_STANDING,
1225 X_MODE,
1226 x_unit_set_cd,
1227 x_us_version_number,
1228 x_unit_cd,
1229 x_unit_version_number,
1230 x_unit_level,
1231 x_unit_type_id,
1232 x_unit_mode
1233 );
1234 END add_row;
1235
1236
1237 PROCEDURE delete_row (
1238 X_ROWID in VARCHAR2
1239 ) AS
1240 BEGIN
1241 Before_DML(
1242 p_action => 'DELETE',
1243 x_rowid => X_ROWID
1244 );
1245 DELETE FROM igs_fi_fee_as_rt_h_all
1246 WHERE rowid = x_rowid;
1247 IF (SQL%NOTFOUND) THEN
1248 RAISE NO_DATA_FOUND;
1249 END IF;
1250 END delete_row;
1251
1252 END igs_fi_fee_as_rt_h_pkg;
[Home] [Help]