1 PACKAGE BODY igs_fi_fee_as_items_pkg AS
2 /* $Header: IGSSI76B.pls 120.5 2005/10/05 16:48:38 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_fi_fee_as_items%RowType;
5 new_references igs_fi_fee_as_items%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_fee_ass_item_id IN NUMBER ,
11 x_TRANSACTION_ID IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_status IN VARCHAR2 ,
14 x_fee_type IN VARCHAR2 ,
15 x_fee_cat IN VARCHAR2 ,
16 x_fee_cal_type IN VARCHAR2 ,
17 x_fee_ci_sequence_number IN NUMBER ,
18 x_RUL_SEQUENCE_NUMBER IN NUMBER ,
19 x_s_chg_method_type IN VARCHAR2 ,
20 x_description IN VARCHAR2 ,
21 x_chg_elements IN NUMBER ,
22 x_amount IN NUMBER ,
23 x_fee_effective_dt IN DATE ,
24 x_course_cd IN VARCHAR2 ,
25 x_crs_version_number IN NUMBER ,
26 x_course_attempt_status IN VARCHAR2 ,
27 x_attendance_mode IN VARCHAR2 ,
28 x_attendance_type IN VARCHAR2 ,
29 x_unit_attempt_status IN VARCHAR2 ,
30 x_location_cd IN VARCHAR2 ,
31 x_eftsu IN NUMBER ,
32 x_credit_points IN NUMBER ,
33 x_logical_delete_date IN DATE ,
34 x_invoice_id IN NUMBER ,
35 X_ORG_UNIT_CD IN VARCHAR2,
36 X_CLASS_STANDING IN VARCHAR2,
37 X_RESIDENCY_STATUS_CD IN VARCHAR2,
38 x_creation_date IN DATE ,
39 x_created_by IN NUMBER ,
40 x_last_update_date IN DATE ,
41 x_last_updated_by IN NUMBER ,
42 x_last_update_login IN NUMBER,
43 x_uoo_id IN NUMBER,
44 x_chg_rate IN VARCHAR,
45 x_unit_set_cd IN VARCHAR2,
46 x_us_version_number IN NUMBER,
47 x_unit_type_id IN NUMBER ,
48 x_unit_class IN VARCHAR2 ,
49 x_unit_mode IN VARCHAR2 ,
50 x_unit_level IN VARCHAR2,
51 x_scope_rul_sequence_num IN NUMBER,
52 x_elm_rng_order_name IN VARCHAR2,
53 x_max_chg_elements IN NUMBER
54 ) AS
55
56 /*************************************************************
57 Created By :syam.krishnan
58 Date Created By :6-jul-2000
59 Purpose :
60 Know limitations, enhancements or remarks
61 Change History
62 Who When What
63 pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
64 Added 2 new columns unit_set_cd and us_version_number
65 (reverse chronological order - newest change first)
66 ***************************************************************/
67
68 CURSOR cur_old_ref_values IS
69 SELECT *
70 FROM IGS_FI_FEE_AS_ITEMS
71 WHERE rowid = x_rowid;
72
73 BEGIN
74
75 l_rowid := x_rowid;
76
77 -- Code for setting the Old and New Reference Values.
78 -- Populate Old Values.
79 Open cur_old_ref_values;
80 Fetch cur_old_ref_values INTO old_references;
81 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
82 Close cur_old_ref_values;
83 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
84 IGS_GE_MSG_STACK.ADD;
85 App_Exception.Raise_Exception;
86 Return;
87 END IF;
88 Close cur_old_ref_values;
89
90 -- Populate New Values.
91 new_references.fee_ass_item_id := x_fee_ass_item_id;
92 new_references.transaction_id := x_transaction_id;
93 new_references.person_id := x_person_id;
94 new_references.status := x_status;
95 new_references.fee_type := x_fee_type;
96 new_references.fee_cat := x_fee_cat;
97 new_references.fee_cal_type := x_fee_cal_type;
98 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
99 new_references.rul_sequence_number := x_rul_sequence_number;
100 new_references.s_chg_method_type := x_s_chg_method_type;
101 new_references.description := x_description;
102 new_references.chg_elements := x_chg_elements;
103 new_references.amount := x_amount;
104 new_references.fee_effective_dt := x_fee_effective_dt;
105 new_references.course_cd := x_course_cd;
106 new_references.crs_version_number := x_crs_version_number;
107 new_references.course_attempt_status := x_course_attempt_status;
108 new_references.attendance_mode := x_attendance_mode;
109 new_references.attendance_type := x_attendance_type;
110 new_references.unit_attempt_status := x_unit_attempt_status;
111 new_references.location_cd := x_location_cd;
112 new_references.eftsu := x_eftsu;
113 new_references.credit_points := x_credit_points;
114 new_references.logical_delete_date := x_logical_delete_date;
115 new_references.invoice_id := x_invoice_id;
116 new_references.org_unit_cd := x_org_unit_cd;
117 new_references.class_standing := x_class_standing;
118 new_references.residency_status_cd := x_residency_status_cd;
119 new_references.uoo_id := x_uoo_id;
120 new_references.chg_rate := x_chg_rate;
121 new_references.unit_set_cd := x_unit_set_cd;
122 new_references.us_version_number := x_us_version_number;
123 new_references.unit_type_id := x_unit_type_id;
124 new_references.unit_class := x_unit_class;
125 new_references.unit_mode := x_unit_mode;
126 new_references.unit_level := x_unit_level;
127 new_references.scope_rul_sequence_num := x_scope_rul_sequence_num;
128 new_references.elm_rng_order_name := x_elm_rng_order_name;
129 new_references.max_chg_elements := x_max_chg_elements;
130
131
132 IF (p_action = 'UPDATE') THEN
133 new_references.creation_date := old_references.creation_date;
134 new_references.created_by := old_references.created_by;
135 ELSE
136 new_references.creation_date := x_creation_date;
137 new_references.created_by := x_created_by;
138 END IF;
139 new_references.last_update_date := x_last_update_date;
140 new_references.last_updated_by := x_last_updated_by;
141 new_references.last_update_login := x_last_update_login;
142
143 END Set_Column_Values;
144
145 PROCEDURE Check_Constraints (
146 Column_Name IN VARCHAR2 ,
147 Column_Value IN VARCHAR2 ) AS
148 /*************************************************************
149 Created By :syam.krishnan
150 Date Created By :6-jul-2000
151 Purpose :
152 Know limitations, enhancements or remarks
153 Change History
154 Who When What
155 bannamal 08-Jul-2005 Enh#3392088 Campus Privilege Fee.
156 Added 'I' while checking the status.
157 (reverse chronological order - newest change first)
158 ***************************************************************/
159
160 BEGIN
161
162 IF column_name IS NULL THEN
163 NULL;
164 ELSIF UPPER(column_name) = 'STATUS' THEN
165 new_references.status := column_value;
166 NULL;
167 END IF;
168
169 -- The following code checks for check constraints on the Columns.
170 IF Upper(Column_Name) = 'STATUS' OR
171 Column_Name IS NULL THEN
172 IF NOT (new_references.status IN ('O', 'E', 'I')) THEN
173 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END IF;
177 END IF;
178
179 END Check_Constraints;
180
181 PROCEDURE Check_Uniqueness AS
182 /*************************************************************
183 Created By :syam.krishnan
184 Date Created By :6-jul-2000
185 Purpose :
186 Know limitations, enhancements or remarks
187 Change History
188 Who When What
189
190 (reverse chronological order - newest change first)
191 ***************************************************************/
192
193 begin
194 IF Get_Uk_For_Validation (
195 new_references.transaction_id
196 ,new_references.person_id
197 ,new_references.location_cd
198 ,new_references.course_cd
199 ,new_references.crs_version_number
200 ,new_references.fee_cal_type
201 ,new_references.fee_cat
202 ,new_references.fee_ci_sequence_number
203 ,new_references.fee_type
204 ,new_references.uoo_id,
205 new_references.org_unit_cd
206 ) THEN
207 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
208 IGS_GE_MSG_STACK.ADD;
209 app_exception.raise_exception;
210 END IF;
211 END Check_Uniqueness ;
212
213 PROCEDURE Check_Parent_Existance AS
214 /*************************************************************
215 Created By :syam.krishnan
216 Date Created By :6-jul-2000
217 Purpose :
218 Know limitations, enhancements or remarks
219 Change History
220 Who When What
221 pathipat 18-Sep-2003 Enh 3108052 - Unit Sets in Rate Table build
222 Added call to igs_en_unit_set_pkg.get_pk_for_validation
223 (reverse chronological order - newest change first)
224 ***************************************************************/
225
226 BEGIN
227
228 IF (( (old_references.s_chg_method_type = new_references.s_chg_method_type)) OR
229 ((new_references.s_chg_method_type IS NULL))) THEN
230 NULL;
231 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation('CHG_METHOD',
232 new_references.s_chg_method_type) THEN
233 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
234 IGS_GE_MSG_STACK.ADD;
235 App_Exception.Raise_Exception;
236 END IF;
237
238 IF ((old_references.person_id = new_references.person_id) AND
239 (old_references.transaction_id = new_references.transaction_id)) THEN
240 NULL;
241 ELSE
242 IF NOT IGS_FI_FEE_AS_PKG.Get_PK_For_Validation (
243 new_references.person_id,
244 new_references.transaction_id) THEN
245 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
246 IGS_GE_MSG_STACK.ADD;
247 App_Exception.Raise_Exception;
248 END IF;
249 END IF;
250
251 IF ( ((old_references.unit_set_cd = new_references.unit_set_cd)
252 AND (old_references.us_version_number = new_references.us_version_number))
253 OR ((new_references.unit_set_cd IS NULL) OR (new_references.us_version_number IS NULL))
254 ) THEN
255 NULL;
256 ELSE
257 IF NOT igs_en_unit_set_pkg.Get_PK_For_Validation (
258 new_references.unit_set_cd,
259 new_references.us_version_number) THEN
260 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 END IF;
264 END IF;
265
266 END Check_Parent_Existance;
267
268
269 FUNCTION Get_PK_For_Validation (
270 x_fee_ass_item_id IN NUMBER
271 ) RETURN BOOLEAN AS
272
273 /*************************************************************
274 Created By :syam.krishnan
275 Date Created By :6-jul-2000
276 Purpose :
277 Know limitations, enhancements or remarks
278 Change History
279 Who When What
280
281 (reverse chronological order - newest change first)
282 ***************************************************************/
283
284 CURSOR cur_rowid IS
285 SELECT rowid
286 FROM igs_fi_fee_as_items
287 WHERE fee_ass_item_id = x_fee_ass_item_id
288 FOR UPDATE NOWAIT;
289
290 lv_rowid cur_rowid%RowType;
291
292 BEGIN
293
294 Open cur_rowid;
295 Fetch cur_rowid INTO lv_rowid;
296 IF (cur_rowid%FOUND) THEN
297 Close cur_rowid;
298 Return(TRUE);
299 ELSE
300 Close cur_rowid;
301 Return(FALSE);
302 END IF;
303 END Get_PK_For_Validation;
304
305
306 ---added by syam
307 PROCEDURE GET_FK_IGS_FI_FEE_AS (
308 x_person_id IN NUMBER,
309 x_transaction_id IN NUMBER
310 ) AS
311
312 CURSOR cur_rowid IS
313 SELECT rowid
314 FROM IGS_FI_FEE_AS_ITEMS
315 WHERE person_id = x_person_id
316 AND transaction_id = x_transaction_id;
317
318 lv_rowid cur_rowid%RowType;
319
320 BEGIN
321
322 Open cur_rowid;
323 Fetch cur_rowid INTO lv_rowid;
324 IF (cur_rowid%FOUND) THEN
325 Close cur_rowid;
326 Fnd_Message.Set_Name ('IGS', 'IGS_FI_AITM_FAS_FK');
327 IGS_GE_MSG_STACK.ADD;
328 App_Exception.Raise_Exception;
329 Return;
330 END IF;
331 Close cur_rowid;
332
333 END GET_FK_IGS_FI_FEE_AS;
334
335 --added by syam
336
337 FUNCTION Get_UK_For_Validation (
338 x_TRANSACTION_ID IN NUMBER,
339 x_person_id IN NUMBER,
340 x_location_cd IN VARCHAR2,
341 x_course_cd IN VARCHAR2,
342 x_crs_version_number IN NUMBER,
343 x_fee_cal_type IN VARCHAR2,
344 x_fee_cat IN VARCHAR2,
345 x_fee_ci_sequence_number IN NUMBER,
346 x_fee_type IN VARCHAR2,
347 x_uoo_id IN NUMBER,
348 x_org_unit_cd IN VARCHAR2
349 ) RETURN BOOLEAN AS
350
351 /*************************************************************
352 Created By :syam.krishnan
353 Date Created By :6-jul-2000
354 Purpose :
355 Know limitations, enhancements or remarks
356 Change History
357 Who When What
358 pathipat 05-Oct-2005 Bug 4615626 - Unhandled exception when org unit is changed
359 Added ORG_UNIT_CD as part of UK
360 (reverse chronological order - newest change first)
361 ***************************************************************/
362 --to take care of unique keys which are nullable -syam
363 CURSOR cur_rowid IS
364 SELECT rowid
365 FROM igs_fi_fee_as_items
366 WHERE person_id = x_person_id
367 AND transaction_id = x_TRANSACTION_ID
368 AND ((LOCATION_CD = X_LOCATION_CD) OR ((LOCATION_CD is null) AND (X_LOCATION_CD is null)))
369 AND ((COURSE_CD = X_COURSE_CD) OR ((COURSE_CD is null) AND (X_COURSE_CD is null)))
370 AND ((CRS_VERSION_NUMBER = X_CRS_VERSION_NUMBER) OR ((CRS_VERSION_NUMBER is null) AND (X_CRS_VERSION_NUMBER is null)))
371 AND fee_cal_type = x_fee_cal_type
372 AND ((FEE_CAT = X_FEE_CAT) OR ((FEE_CAT is null) AND (X_FEE_CAT is null)))
373 AND fee_ci_sequence_number = x_fee_ci_sequence_number
374 AND fee_type = x_fee_type and ((l_rowid is null) or (rowid <> l_rowid))
375 AND ((UOO_ID = X_UOO_ID) OR ((UOO_ID is null) AND (X_UOO_ID is null)))
376 AND ((org_unit_cd = x_org_unit_cd) OR ((org_unit_cd IS NULL) AND (org_unit_cd IS NULL)));
377
378 lv_rowid cur_rowid%RowType;
379
380
381 BEGIN
382
383 Open cur_rowid;
384 Fetch cur_rowid INTO lv_rowid;
385 IF (cur_rowid%FOUND) THEN
386 Close cur_rowid;
387 return (true);
388 ELSE
389 close cur_rowid;
390 return(false);
391 END IF;
392 END Get_UK_For_Validation ;
393
394
395 PROCEDURE Before_DML (
396 p_action IN VARCHAR2,
397 x_rowid IN VARCHAR2 ,
398 x_fee_ass_item_id IN NUMBER ,
399 x_TRANSACTION_ID IN NUMBER ,
400 x_person_id IN NUMBER ,
401 x_status IN VARCHAR2 ,
402 x_fee_type IN VARCHAR2 ,
403 x_fee_cat IN VARCHAR2 ,
404 x_fee_cal_type IN VARCHAR2 ,
405 x_fee_ci_sequence_number IN NUMBER ,
406 x_RUL_SEQUENCE_NUMBER IN NUMBER ,
407 x_s_chg_method_type IN VARCHAR2 ,
408 x_description IN VARCHAR2 ,
409 x_chg_elements IN NUMBER ,
410 x_amount IN NUMBER ,
411 x_fee_effective_dt IN DATE ,
412 x_course_cd IN VARCHAR2 ,
413 x_crs_version_number IN NUMBER ,
414 x_course_attempt_status IN VARCHAR2 ,
415 x_attendance_mode IN VARCHAR2 ,
416 x_attendance_type IN VARCHAR2 ,
417 x_unit_attempt_status IN VARCHAR2 ,
418 x_location_cd IN VARCHAR2 ,
419 x_eftsu IN NUMBER ,
420 x_credit_points IN NUMBER ,
421 x_logical_delete_date IN DATE ,
422 x_invoice_id IN NUMBER,
423 X_ORG_UNIT_CD IN VARCHAR2,
424 X_CLASS_STANDING IN VARCHAR2,
425 X_RESIDENCY_STATUS_CD IN VARCHAR2,
426 x_creation_date IN DATE ,
427 x_created_by IN NUMBER ,
428 x_last_update_date IN DATE ,
429 x_last_updated_by IN NUMBER ,
430 x_last_update_login IN NUMBER,
431 x_uoo_id IN NUMBER,
432 X_CHG_RATE IN VARCHAR2,
433 x_unit_set_cd IN VARCHAR2,
434 x_us_version_number IN NUMBER,
435 x_unit_type_id IN NUMBER,
436 x_unit_class IN VARCHAR2,
437 x_unit_mode IN VARCHAR2,
438 x_unit_level IN VARCHAR2,
439 x_scope_rul_sequence_num IN NUMBER,
440 x_elm_rng_order_name IN VARCHAR2,
441 x_max_chg_elements IN NUMBER
442 ) AS
443 /*************************************************************
444 Created By :syam.krishnan
445 Date Created By :6-jul-2000
446 Purpose :
447 Know limitations, enhancements or remarks
448 Change History
449 Who When What
450 pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
451 Added 2 new columns unit_set_cd and us_version_number
452 (reverse chronological order - newest change first)
453 ***************************************************************/
454
455 BEGIN
456
457 Set_Column_Values (
458 p_action,
459 x_rowid,
460 x_fee_ass_item_id,
461 x_transaction_id,
462 x_person_id,
463 x_status,
464 x_fee_type,
465 x_fee_cat,
466 x_fee_cal_type,
467 x_fee_ci_sequence_number,
468 x_rul_sequence_number,
469 x_s_chg_method_type,
470 x_description,
471 x_chg_elements,
472 x_amount,
473 x_fee_effective_dt,
474 x_course_cd,
475 x_crs_version_number,
476 x_course_attempt_status,
477 x_attendance_mode,
478 x_attendance_type,
479 x_unit_attempt_status,
480 x_location_cd,
481 x_eftsu,
482 x_credit_points,
483 x_logical_delete_date,
484 x_invoice_id,
485 X_ORG_UNIT_CD,
486 X_CLASS_STANDING,
487 X_RESIDENCY_STATUS_CD,
488 x_creation_date,
489 x_created_by,
490 x_last_update_date,
491 x_last_updated_by,
492 x_last_update_login,
493 x_uoo_id,
494 x_chg_rate,
495 x_unit_set_cd,
496 x_us_version_number,
497 x_unit_type_id,
498 x_unit_class,
499 x_unit_mode,
500 x_unit_level,
501 x_scope_rul_sequence_num,
502 x_elm_rng_order_name,
503 x_max_chg_elements
504 );
505
506 IF (p_action = 'INSERT') THEN
507 -- Call all the procedures related to Before Insert.
508 Null;
509 IF Get_Pk_For_Validation(
510 new_references.fee_ass_item_id) THEN
511 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
512 IGS_GE_MSG_STACK.ADD;
513 App_Exception.Raise_Exception;
514 END IF;
515 Check_Uniqueness;
516 Check_Constraints;
517 Check_Parent_Existance;
518 ELSIF (p_action = 'UPDATE') THEN
519 -- Call all the procedures related to Before Update.
520 Null;
521 Check_Uniqueness;
522 Check_Constraints;
523
524 Check_Parent_Existance;
525 ELSIF (p_action = 'DELETE') THEN
526 -- Call all the procedures related to Before Delete.
527 Null;
528 ELSIF (p_action = 'VALIDATE_INSERT') THEN
529 -- Call all the procedures related to Before Insert.
530 IF Get_PK_For_Validation (
531 new_references.fee_ass_item_id) THEN
532 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
533 IGS_GE_MSG_STACK.ADD;
534 App_Exception.Raise_Exception;
535 END IF;
536 Check_Uniqueness;
537 Check_Constraints;
538 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
539 Check_Uniqueness;
540 Check_Constraints;
541 ELSIF (p_action = 'VALIDATE_DELETE') THEN
542 Null;
543 END IF;
544
545 END Before_DML;
546
547 PROCEDURE After_DML (
548 p_action IN VARCHAR2,
549 x_rowid IN VARCHAR2
550 ) IS
551 /*************************************************************
552 Created By :syam.krishnan
553 Date Created By :6-jul-2000
554 Purpose :
555 Know limitations, enhancements or remarks
556 Change History
557 Who When What
558
559 (reverse chronological order - newest change first)
560 ***************************************************************/
561
562 BEGIN
563
564 l_rowid := x_rowid;
565
566 IF (p_action = 'INSERT') THEN
567 -- Call all the procedures related to After Insert.
568 Null;
569 ELSIF (p_action = 'UPDATE') THEN
570 -- Call all the procedures related to After Update.
571 Null;
572 ELSIF (p_action = 'DELETE') THEN
573 -- Call all the procedures related to After Delete.
574 Null;
575 END IF;
576
577 END After_DML;
578
579 procedure INSERT_ROW (
580 X_ROWID in out NOCOPY VARCHAR2,
581 x_FEE_ASS_ITEM_ID IN OUT NOCOPY NUMBER,
582 x_TRANSACTION_ID IN NUMBER,
583 x_PERSON_ID IN NUMBER,
584 x_STATUS IN VARCHAR2,
585 x_FEE_TYPE IN VARCHAR2,
586 x_FEE_CAT IN VARCHAR2,
587 x_FEE_CAL_TYPE IN VARCHAR2,
588 x_FEE_CI_SEQUENCE_NUMBER IN NUMBER,
589 x_RUL_SEQUENCE_NUMBER IN NUMBER,
590 x_S_CHG_METHOD_TYPE IN VARCHAR2,
591 x_DESCRIPTION IN VARCHAR2,
592 x_CHG_ELEMENTS IN NUMBER,
593 x_AMOUNT IN NUMBER,
594 x_FEE_EFFECTIVE_DT IN DATE,
595 x_COURSE_CD IN VARCHAR2,
596 x_CRS_VERSION_NUMBER IN NUMBER,
597 x_COURSE_ATTEMPT_STATUS IN VARCHAR2,
598 x_ATTENDANCE_MODE IN VARCHAR2,
599 x_ATTENDANCE_TYPE IN VARCHAR2,
600 x_UNIT_ATTEMPT_STATUS IN VARCHAR2,
601 x_LOCATION_CD IN VARCHAR2,
602 x_EFTSU IN NUMBER,
603 x_CREDIT_POINTS IN NUMBER,
604 x_LOGICAL_DELETE_DATE IN DATE,
605 X_INVOICE_ID IN NUMBER ,
606 X_ORG_UNIT_CD IN VARCHAR2,
607 X_CLASS_STANDING IN VARCHAR2,
608 X_RESIDENCY_STATUS_CD IN VARCHAR2,
609 X_MODE in VARCHAR2,
610 x_uoo_id IN NUMBER ,
611 x_chg_rate IN VARCHAR2 ,
612 x_unit_set_cd IN VARCHAR2,
613 x_us_version_number IN NUMBER,
614 x_unit_type_id IN NUMBER,
615 x_unit_class IN VARCHAR2,
616 x_unit_mode IN VARCHAR2,
617 x_unit_level IN VARCHAR2,
618 x_scope_rul_sequence_num IN NUMBER,
619 x_elm_rng_order_name IN VARCHAR2,
620 x_max_chg_elements IN NUMBER
621 ) AS
622 /*************************************************************
623 Created By :syam.krishnan
624 Date Created By :6-jul-2000
625 Purpose :
626 Know limitations, enhancements or remarks
627 Change History
628 Who When What
629 pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
630 Added 2 new columns unit_set_cd and us_version_number
631 (reverse chronological order - newest change first)
632 ***************************************************************/
633
634 cursor C is select ROWID from IGS_FI_FEE_AS_ITEMS
635 where FEE_ASS_ITEM_ID= X_FEE_ASS_ITEM_ID;
636 X_LAST_UPDATE_DATE DATE ;
637 X_LAST_UPDATED_BY NUMBER ;
638 X_LAST_UPDATE_LOGIN NUMBER ;
639 begin
640 X_LAST_UPDATE_DATE := SYSDATE;
641 if(X_MODE = 'I') then
642 X_LAST_UPDATED_BY := 1;
643 X_LAST_UPDATE_LOGIN := 0;
644 elsif (X_MODE = 'R') then
645 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
646 if X_LAST_UPDATED_BY is NULL then
647 X_LAST_UPDATED_BY := -1;
648 end if;
649 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
650 if X_LAST_UPDATE_LOGIN is NULL then
651 X_LAST_UPDATE_LOGIN := -1;
652 end if;
653 else
654 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
655 IGS_GE_MSG_STACK.ADD;
656 app_exception.raise_exception;
657 end if;
658 ----------------------------------
659 SELECT igs_fi_fee_as_items_s.nextval
660 INTO X_FEE_ASS_ITEM_ID
661 FROM dual;
662 --------------------------------------
663
664 Before_DML(
665 p_action=>'INSERT',
666 x_rowid=>X_ROWID,
667 x_fee_ass_item_id=>X_FEE_ASS_ITEM_ID,
668 x_transaction_id=>X_TRANSACTION_ID,
669 x_person_id=>X_PERSON_ID,
670 x_status=>X_STATUS,
671 x_fee_type=>X_FEE_TYPE,
672 x_fee_cat=>X_FEE_CAT,
673 x_fee_cal_type=>X_FEE_CAL_TYPE,
674 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
675 x_rul_sequence_number=>X_RUL_SEQUENCE_NUMBER,
676 x_s_chg_method_type=>X_S_CHG_METHOD_TYPE,
677 x_description=>X_DESCRIPTION,
678 x_chg_elements=>X_CHG_ELEMENTS,
679 x_amount=>X_AMOUNT,
680 x_fee_effective_dt=>X_FEE_EFFECTIVE_DT,
681 x_course_cd=>X_COURSE_CD,
682 x_crs_version_number=>X_CRS_VERSION_NUMBER,
683 x_course_attempt_status=>X_COURSE_ATTEMPT_STATUS,
684 x_attendance_mode=>X_ATTENDANCE_MODE,
685 x_attendance_type=>X_ATTENDANCE_TYPE,
686 x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
687 x_location_cd=>X_LOCATION_CD,
688 x_eftsu=>X_EFTSU,
689 x_credit_points=>X_CREDIT_POINTS,
690 x_logical_delete_date=>X_LOGICAL_DELETE_DATE,
691 x_invoice_id=>X_INVOICE_ID,
692 X_ORG_UNIT_CD => x_org_unit_cd,
693 X_CLASS_STANDING => x_class_standing,
694 X_RESIDENCY_STATUS_CD => x_residency_status_cd,
695 x_creation_date=>X_LAST_UPDATE_DATE,
696 x_created_by=>X_LAST_UPDATED_BY,
697 x_last_update_date=>X_LAST_UPDATE_DATE,
698 x_last_updated_by=>X_LAST_UPDATED_BY,
699 x_last_update_login=>X_LAST_UPDATE_LOGIN,
700 x_uoo_id => x_uoo_id,
701 x_chg_rate => x_chg_rate,
702 x_unit_set_cd => x_unit_set_cd,
703 x_us_version_number => x_us_version_number,
704 x_unit_type_id => x_unit_type_id,
705 x_unit_class => x_unit_class,
706 x_unit_mode => x_unit_mode,
707 x_unit_level => x_unit_level,
708 x_scope_rul_sequence_num => x_scope_rul_sequence_num,
709 x_elm_rng_order_name => x_elm_rng_order_name,
710 x_max_chg_elements => x_max_chg_elements
711 );
712
713 insert into IGS_FI_FEE_AS_ITEMS (
714 FEE_ASS_ITEM_ID
715 ,TRANSACTION_ID
716 ,PERSON_ID
717 ,STATUS
718 ,FEE_TYPE
719 ,FEE_CAT
720 ,FEE_CAL_TYPE
721 ,FEE_CI_SEQUENCE_NUMBER
722 ,RUL_SEQUENCE_NUMBER
723 ,S_CHG_METHOD_TYPE
724 ,DESCRIPTION
725 ,CHG_ELEMENTS
726 ,AMOUNT
727 ,FEE_EFFECTIVE_DT
728 ,COURSE_CD
729 ,CRS_VERSION_NUMBER
730 ,COURSE_ATTEMPT_STATUS
731 ,ATTENDANCE_MODE
732 ,ATTENDANCE_TYPE
733 ,UNIT_ATTEMPT_STATUS
734 ,LOCATION_CD
735 ,EFTSU
736 ,CREDIT_POINTS
737 ,LOGICAL_DELETE_DATE
738 ,CREATION_DATE
739 ,CREATED_BY
740 ,LAST_UPDATE_DATE
741 ,LAST_UPDATED_BY
742 ,LAST_UPDATE_LOGIN
743 ,INVOICE_ID
744 ,ORG_UNIT_CD
745 ,CLASS_STANDING
746 ,RESIDENCY_STATUS_CD
747 ,uoo_id
748 ,chg_rate,
749 unit_set_cd,
750 us_version_number,
751 unit_type_id,
752 unit_class,
753 unit_mode,
754 unit_level,
755 scope_rul_sequence_num,
756 elm_rng_order_name,
757 max_chg_elements
758 )
759 values (
760 NEW_REFERENCES.FEE_ASS_ITEM_ID
761 ,NEW_REFERENCES.TRANSACTION_ID
762 ,NEW_REFERENCES.PERSON_ID
763 ,NEW_REFERENCES.STATUS
764 ,NEW_REFERENCES.FEE_TYPE
765 ,NEW_REFERENCES.FEE_CAT
766 ,NEW_REFERENCES.FEE_CAL_TYPE
767 ,NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER
768 ,NEW_REFERENCES.RUL_SEQUENCE_NUMBER
769 ,NEW_REFERENCES.S_CHG_METHOD_TYPE
770 ,NEW_REFERENCES.DESCRIPTION
771 ,NEW_REFERENCES.CHG_ELEMENTS
772 ,NEW_REFERENCES.AMOUNT
773 ,NEW_REFERENCES.FEE_EFFECTIVE_DT
774 ,NEW_REFERENCES.COURSE_CD
775 ,NEW_REFERENCES.CRS_VERSION_NUMBER
776 ,NEW_REFERENCES.COURSE_ATTEMPT_STATUS
777 ,NEW_REFERENCES.ATTENDANCE_MODE
778 ,NEW_REFERENCES.ATTENDANCE_TYPE
779 ,NEW_REFERENCES.UNIT_ATTEMPT_STATUS
780 ,NEW_REFERENCES.LOCATION_CD
781 ,NEW_REFERENCES.EFTSU
782 ,NEW_REFERENCES.CREDIT_POINTS
783 ,NEW_REFERENCES.LOGICAL_DELETE_DATE
784 ,X_LAST_UPDATE_DATE
785 ,X_LAST_UPDATED_BY
786 ,X_LAST_UPDATE_DATE
787 ,X_LAST_UPDATED_BY
788 ,X_LAST_UPDATE_LOGIN
789 ,NEW_REFERENCES.INVOICE_ID
790 ,NEW_REFERENCES.ORG_UNIT_CD
791 ,NEW_REFERENCES.CLASS_STANDING
792 ,NEW_REFERENCES.RESIDENCY_STATUS_CD
793 ,NEW_REFERENCES.UOO_ID
794 ,NEW_REFERENCES.CHG_RATE,
795 new_references.unit_set_cd,
796 new_references.us_version_number,
797 new_references.unit_type_id,
798 new_references.unit_class,
799 new_references.unit_mode,
800 new_references.unit_level,
801 new_references.scope_rul_sequence_num,
802 new_references.elm_rng_order_name,
803 new_references.max_chg_elements
804 );
805 open c;
806 fetch c into X_ROWID;
807 if (c%notfound) then
808 close c;
809 raise no_data_found;
810 end if;
811 close c;
812 After_DML (
813 p_action => 'INSERT' ,
814 x_rowid => X_ROWID );
815 end INSERT_ROW;
816
817
818 procedure LOCK_ROW (
819 X_ROWID in VARCHAR2,
820 x_FEE_ASS_ITEM_ID IN NUMBER,
821 x_TRANSACTION_ID IN NUMBER,
822 x_PERSON_ID IN NUMBER,
823 x_STATUS IN VARCHAR2,
824 x_FEE_TYPE IN VARCHAR2,
825 x_FEE_CAT IN VARCHAR2,
826 x_FEE_CAL_TYPE IN VARCHAR2,
827 x_FEE_CI_SEQUENCE_NUMBER IN NUMBER,
828 x_RUL_SEQUENCE_NUMBER IN NUMBER,
829 x_S_CHG_METHOD_TYPE IN VARCHAR2,
830 x_DESCRIPTION IN VARCHAR2,
831 x_CHG_ELEMENTS IN NUMBER,
832 x_AMOUNT IN NUMBER,
833 x_FEE_EFFECTIVE_DT IN DATE,
834 x_COURSE_CD IN VARCHAR2,
835 x_CRS_VERSION_NUMBER IN NUMBER,
836 x_COURSE_ATTEMPT_STATUS IN VARCHAR2,
837 x_ATTENDANCE_MODE IN VARCHAR2,
838 x_ATTENDANCE_TYPE IN VARCHAR2,
839 x_UNIT_ATTEMPT_STATUS IN VARCHAR2,
840 x_LOCATION_CD IN VARCHAR2,
841 x_EFTSU IN NUMBER,
842 x_CREDIT_POINTS IN NUMBER,
843 x_LOGICAL_DELETE_DATE IN DATE,
844 X_INVOICE_ID IN NUMBER,
845 X_ORG_UNIT_CD IN VARCHAR2,
846 X_CLASS_STANDING IN VARCHAR2,
847 X_RESIDENCY_STATUS_CD IN VARCHAR2,
848 X_UOO_ID IN NUMBER,
849 x_chg_rate IN VARCHAR2,
850 x_unit_set_cd IN VARCHAR2,
851 x_us_version_number IN NUMBER,
852 x_unit_type_id IN NUMBER,
853 x_unit_class IN VARCHAR2,
854 x_unit_mode IN VARCHAR2,
855 x_unit_level IN VARCHAR2,
856 x_scope_rul_sequence_num IN NUMBER,
857 x_elm_rng_order_name IN VARCHAR2,
858 x_max_chg_elements IN NUMBER
859 )AS
860 /*************************************************************
861 Created By :syam.krishnan
862 Date Created By :6-jul-2000
863 Purpose :
864 Know limitations, enhancements or remarks
865 Change History
866 Who When What
867 pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
868 Added 2 new columns unit_set_cd and us_version_number
869 (reverse chronological order - newest change first)
870 ***************************************************************/
871
872 cursor c1 is select
873 transaction_id ,
874 person_id,
875 status,
876 fee_type,
877 fee_cat,
878 fee_cal_type,
879 fee_ci_sequence_number,
880 rul_sequence_number,
881 s_chg_method_type,
882 description,
883 chg_elements,
884 amount,
885 fee_effective_dt,
886 course_cd,
887 crs_version_number,
888 course_attempt_status,
889 attendance_mode,
890 attendance_type,
891 unit_attempt_status,
892 location_cd,
893 eftsu,
894 credit_points,
895 logical_delete_date,
896 invoice_id,
897 org_unit_cd,
898 class_standing,
899 residency_status_cd,
900 uoo_id,
901 chg_rate,
902 unit_set_cd,
903 us_version_number,
904 unit_type_id,
905 unit_class,
906 unit_mode,
907 unit_level,
908 scope_rul_sequence_num,
909 elm_rng_order_name,
910 max_chg_elements
911 FROM igs_fi_fee_as_items
912 WHERE rowid = x_rowid
913 FOR UPDATE NOWAIT;
914 tlinfo c1%rowtype;
915 begin
916 open c1;
917 fetch c1 into tlinfo;
918 if (c1%notfound) then
919 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
920 IGS_GE_MSG_STACK.ADD;
921 close c1;
922 app_exception.raise_exception;
923 return;
924 end if;
925 close c1;
926 if ( (tlinfo.TRANSACTION_ID = X_TRANSACTION_ID)
927 AND (tlinfo.PERSON_ID = X_PERSON_ID)
928 AND (tlinfo.STATUS = X_STATUS)
929 AND (tlinfo.FEE_TYPE = X_FEE_TYPE)
930 AND ((tlinfo.FEE_CAT = X_FEE_CAT)
931 OR ((tlinfo.FEE_CAT is null)
932 AND (X_FEE_CAT is null)))
933 AND (tlinfo.FEE_CAL_TYPE = X_FEE_CAL_TYPE)
934 AND (tlinfo.FEE_CI_SEQUENCE_NUMBER = X_FEE_CI_SEQUENCE_NUMBER)
935 AND ((tlinfo.RUL_SEQUENCE_NUMBER = X_RUL_SEQUENCE_NUMBER)
936 OR ((tlinfo.RUL_SEQUENCE_NUMBER is null)
937 AND (X_RUL_SEQUENCE_NUMBER is null)))
938 AND (tlinfo.S_CHG_METHOD_TYPE = X_S_CHG_METHOD_TYPE)
939 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
940 OR ((tlinfo.DESCRIPTION is null)
941 AND (X_DESCRIPTION is null)))
942 AND ((tlinfo.CHG_ELEMENTS = X_CHG_ELEMENTS)
943 OR ((tlinfo.CHG_ELEMENTS is null)
944 AND (X_CHG_ELEMENTS is null)))
945 AND ((tlinfo.AMOUNT = X_AMOUNT)
946 OR ((tlinfo.AMOUNT is null)
947 AND (X_AMOUNT is null)))
948 AND ((tlinfo.FEE_EFFECTIVE_DT = X_FEE_EFFECTIVE_DT)
949 OR ((tlinfo.FEE_EFFECTIVE_DT is null)
950 AND (X_FEE_EFFECTIVE_DT is null)))
951 AND ((tlinfo.COURSE_CD = X_COURSE_CD)
952 OR ((tlinfo.COURSE_CD is null)
953 AND (X_COURSE_CD is null)))
954 AND ((tlinfo.CRS_VERSION_NUMBER = X_CRS_VERSION_NUMBER)
955 OR ((tlinfo.CRS_VERSION_NUMBER is null)
956 AND (X_CRS_VERSION_NUMBER is null)))
957 AND ((tlinfo.COURSE_ATTEMPT_STATUS = X_COURSE_ATTEMPT_STATUS)
958 OR ((tlinfo.COURSE_ATTEMPT_STATUS is null)
959 AND (X_COURSE_ATTEMPT_STATUS is null)))
960 AND ((tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
961 OR ((tlinfo.ATTENDANCE_MODE is null)
962 AND (X_ATTENDANCE_MODE is null)))
963 AND ((tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
964 OR ((tlinfo.ATTENDANCE_TYPE is null)
965 AND (X_ATTENDANCE_TYPE is null)))
966 AND ((tlinfo.UNIT_ATTEMPT_STATUS = X_UNIT_ATTEMPT_STATUS)
967 OR ((tlinfo.UNIT_ATTEMPT_STATUS is null)
968 AND (X_UNIT_ATTEMPT_STATUS is null)))
969 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
970 OR ((tlinfo.LOCATION_CD is null)
971 AND (X_LOCATION_CD is null)))
972 AND ((tlinfo.EFTSU = X_EFTSU)
973 OR ((tlinfo.EFTSU is null)
974 AND (X_EFTSU is null)))
975 AND ((tlinfo.CREDIT_POINTS = X_CREDIT_POINTS)
976 OR ((tlinfo.CREDIT_POINTS is null)
977 AND (X_CREDIT_POINTS is null)))
978 AND ((tlinfo.LOGICAL_DELETE_DATE = X_LOGICAL_DELETE_DATE)
979 OR ((tlinfo.LOGICAL_DELETE_DATE is null)
980 AND (X_LOGICAL_DELETE_DATE is null)))
981 AND ((tlinfo.INVOICE_ID = X_INVOICE_ID)
982 OR ((tlinfo.INVOICE_ID is null)
983 AND (X_INVOICE_ID is null)))
984 AND ((tlinfo.ORG_UNIT_CD = X_ORG_UNIT_CD)
985 OR ((tlinfo.ORG_UNIT_CD is null)
986 AND (X_ORG_UNIT_CD is null)))
987 AND ((tlinfo.CLASS_STANDING = X_CLASS_STANDING)
988 OR ((tlinfo.CLASS_STANDING is null)
989 AND (X_CLASS_STANDING is null)))
990 AND ((tlinfo.RESIDENCY_STATUS_CD = X_RESIDENCY_STATUS_CD)
991 OR ((tlinfo.RESIDENCY_STATUS_CD is null)
992 AND (X_RESIDENCY_STATUS_CD is null)))
993 AND ((tlinfo.UOO_ID = X_UOO_ID)
994 OR ((tlinfo.UOO_ID is null)
995 AND (X_UOO_ID is null)))
996 AND ((tlinfo.CHG_RATE = X_CHG_RATE)
997 OR ((tlinfo.CHG_RATE is null)
998 AND (X_CHG_RATE is null)))
999 AND ((tlinfo.unit_set_cd = x_unit_set_cd) OR ((tlinfo.unit_set_cd IS NULL) AND (x_unit_set_cd IS NULL)))
1000 AND ((tlinfo.us_version_number = x_us_version_number) OR ((tlinfo.us_version_number IS NULL) AND (x_us_version_number IS NULL)))
1001 AND ((tlinfo.unit_type_id = x_unit_type_id) OR ((tlinfo.unit_type_id IS NULL) AND (x_unit_type_id IS NULL)))
1002 AND ((tlinfo.unit_class = x_unit_class) OR ((tlinfo.unit_class IS NULL) AND (x_unit_class IS NULL)))
1003 AND ((tlinfo.unit_mode = x_unit_mode) OR ((tlinfo.unit_mode IS NULL) AND (x_unit_mode IS NULL)))
1004 AND ((tlinfo.unit_level = x_unit_level) OR ((tlinfo.unit_level IS NULL) AND (x_unit_level IS NULL)))
1005 AND ((tlinfo.scope_rul_sequence_num = x_scope_rul_sequence_num) OR ((tlinfo.scope_rul_sequence_num IS NULL) AND (x_scope_rul_sequence_num IS NULL)))
1006 AND ((tlinfo.elm_rng_order_name = x_elm_rng_order_name) OR ((tlinfo.elm_rng_order_name IS NULL) AND (x_elm_rng_order_name IS NULL)))
1007 AND ((tlinfo.max_chg_elements = x_max_chg_elements) OR ((tlinfo.max_chg_elements IS NULL) AND (x_max_chg_elements IS NULL)))
1008 ) THEN
1009 NULL;
1010 ELSE
1011 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1012 igs_ge_msg_stack.add;
1013 app_exception.raise_exception;
1014 END IF;
1015 RETURN;
1016 END lock_row;
1017
1018
1019 Procedure UPDATE_ROW (
1020 X_ROWID in VARCHAR2,
1021 x_FEE_ASS_ITEM_ID IN NUMBER,
1022 x_TRANSACTION_ID IN NUMBER,
1023 x_PERSON_ID IN NUMBER,
1024 x_STATUS IN VARCHAR2,
1025 x_FEE_TYPE IN VARCHAR2,
1026 x_FEE_CAT IN VARCHAR2,
1027 x_FEE_CAL_TYPE IN VARCHAR2,
1028 x_FEE_CI_SEQUENCE_NUMBER IN NUMBER,
1029 x_RUL_SEQUENCE_NUMBER IN NUMBER,
1030 x_S_CHG_METHOD_TYPE IN VARCHAR2,
1031 x_DESCRIPTION IN VARCHAR2,
1032 x_CHG_ELEMENTS IN NUMBER,
1033 x_AMOUNT IN NUMBER,
1034 x_FEE_EFFECTIVE_DT IN DATE,
1035 x_COURSE_CD IN VARCHAR2,
1036 x_CRS_VERSION_NUMBER IN NUMBER,
1037 x_COURSE_ATTEMPT_STATUS IN VARCHAR2,
1038 x_ATTENDANCE_MODE IN VARCHAR2,
1039 x_ATTENDANCE_TYPE IN VARCHAR2,
1040 x_UNIT_ATTEMPT_STATUS IN VARCHAR2,
1041 x_LOCATION_CD IN VARCHAR2,
1042 x_EFTSU IN NUMBER,
1043 x_CREDIT_POINTS IN NUMBER,
1044 x_LOGICAL_DELETE_DATE IN DATE,
1045 X_INVOICE_ID IN NUMBER,
1046 X_ORG_UNIT_CD IN VARCHAR2,
1047 X_CLASS_STANDING IN VARCHAR2,
1048 X_RESIDENCY_STATUS_CD IN VARCHAR2,
1049 X_MODE in VARCHAR2 ,
1050 X_UOO_ID IN NUMBER,
1051 X_CHG_RATE IN VARCHAR2,
1052 x_unit_set_cd IN VARCHAR2,
1053 x_us_version_number IN NUMBER,
1054 x_unit_type_id IN NUMBER,
1055 x_unit_class IN VARCHAR2,
1056 x_unit_mode IN VARCHAR2,
1057 x_unit_level IN VARCHAR2,
1058 x_scope_rul_sequence_num IN NUMBER,
1059 x_elm_rng_order_name IN VARCHAR2,
1060 x_max_chg_elements IN NUMBER
1061 ) AS
1062 /*************************************************************
1063 Created By :syam.krishnan
1064 Date Created By :6-jul-2000
1065 Purpose :
1066 Know limitations, enhancements or remarks
1067 Change History
1068 Who When What
1069 pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
1070 Added 2 new columns unit_set_cd and us_version_number
1071 (reverse chronological order - newest change first)
1072 ***************************************************************/
1073
1074 X_LAST_UPDATE_DATE DATE ;
1075 X_LAST_UPDATED_BY NUMBER ;
1076 X_LAST_UPDATE_LOGIN NUMBER ;
1077 begin
1078 X_LAST_UPDATE_DATE := SYSDATE;
1079 if(X_MODE = 'I') then
1080 X_LAST_UPDATED_BY := 1;
1081 X_LAST_UPDATE_LOGIN := 0;
1082 elsif (X_MODE = 'R') then
1083 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1084 if X_LAST_UPDATED_BY is NULL then
1085 X_LAST_UPDATED_BY := -1;
1086 end if;
1087 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1088 if X_LAST_UPDATE_LOGIN is NULL then
1089 X_LAST_UPDATE_LOGIN := -1;
1090 end if;
1091 else
1092 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1093 IGS_GE_MSG_STACK.ADD;
1094 app_exception.raise_exception;
1095 end if;
1096 Before_DML(
1097 p_action=>'UPDATE',
1098 x_rowid=>X_ROWID,
1099 x_fee_ass_item_id=>X_FEE_ASS_ITEM_ID,
1100 x_transaction_id=>X_TRANSACTION_ID,
1101 x_person_id=>X_PERSON_ID,
1102 x_status=>X_STATUS,
1103 x_fee_type=>X_FEE_TYPE,
1104 x_fee_cat=>X_FEE_CAT,
1105 x_fee_cal_type=>X_FEE_CAL_TYPE,
1106 x_fee_ci_sequence_number=>X_FEE_CI_SEQUENCE_NUMBER,
1107 x_rul_sequence_number=>X_RUL_SEQUENCE_NUMBER,
1108 x_s_chg_method_type=>X_S_CHG_METHOD_TYPE,
1109 x_description=>X_DESCRIPTION,
1110 x_chg_elements=>X_CHG_ELEMENTS,
1111 x_amount=>X_AMOUNT,
1112 x_fee_effective_dt=>X_FEE_EFFECTIVE_DT,
1113 x_course_cd=>X_COURSE_CD,
1114 x_crs_version_number=>X_CRS_VERSION_NUMBER,
1115 x_course_attempt_status=>X_COURSE_ATTEMPT_STATUS,
1116 x_attendance_mode=>X_ATTENDANCE_MODE,
1117 x_attendance_type=>X_ATTENDANCE_TYPE,
1118 x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
1119 x_location_cd=>X_LOCATION_CD,
1120 x_eftsu=>X_EFTSU,
1121 x_credit_points=>X_CREDIT_POINTS,
1122 x_logical_delete_date=>X_LOGICAL_DELETE_DATE,
1123 x_invoice_id=>x_invoice_id,
1124 x_org_unit_cd =>x_org_unit_cd,
1125 x_class_standing =>x_class_standing,
1126 x_residency_status_cd =>x_residency_status_cd,
1127 x_creation_date=>X_LAST_UPDATE_DATE,
1128 x_created_by=>X_LAST_UPDATED_BY,
1129 x_last_update_date=>X_LAST_UPDATE_DATE,
1130 x_last_updated_by=>X_LAST_UPDATED_BY,
1131 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1132 x_uoo_id => x_uoo_id,
1133 x_chg_rate =>x_chg_rate,
1134 x_unit_set_cd => x_unit_set_cd,
1135 x_us_version_number => x_us_version_number,
1136 x_unit_type_id => x_unit_type_id,
1137 x_unit_class => x_unit_class,
1138 x_unit_mode => x_unit_mode,
1139 x_unit_level => x_unit_level,
1140 x_scope_rul_sequence_num => x_scope_rul_sequence_num,
1141 x_elm_rng_order_name => x_elm_rng_order_name,
1142 x_max_chg_elements => x_max_chg_elements
1143 );
1144
1145 update IGS_FI_FEE_AS_ITEMS set
1146 TRANSACTION_ID = NEW_REFERENCES.TRANSACTION_ID,
1147 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1148 STATUS = NEW_REFERENCES.STATUS,
1149 FEE_TYPE = NEW_REFERENCES.FEE_TYPE,
1150 FEE_CAT = NEW_REFERENCES.FEE_CAT,
1151 FEE_CAL_TYPE = NEW_REFERENCES.FEE_CAL_TYPE,
1152 FEE_CI_SEQUENCE_NUMBER = NEW_REFERENCES.FEE_CI_SEQUENCE_NUMBER,
1153 RUL_SEQUENCE_NUMBER = NEW_REFERENCES.RUL_SEQUENCE_NUMBER,
1154 S_CHG_METHOD_TYPE = NEW_REFERENCES.S_CHG_METHOD_TYPE,
1155 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
1156 CHG_ELEMENTS = NEW_REFERENCES.CHG_ELEMENTS,
1157 AMOUNT = NEW_REFERENCES.AMOUNT,
1158 FEE_EFFECTIVE_DT = NEW_REFERENCES.FEE_EFFECTIVE_DT,
1159 COURSE_CD = NEW_REFERENCES.COURSE_CD,
1160 CRS_VERSION_NUMBER = NEW_REFERENCES.CRS_VERSION_NUMBER,
1161 COURSE_ATTEMPT_STATUS = NEW_REFERENCES.COURSE_ATTEMPT_STATUS,
1162 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
1163 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
1164 UNIT_ATTEMPT_STATUS = NEW_REFERENCES.UNIT_ATTEMPT_STATUS,
1165 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
1166 EFTSU = NEW_REFERENCES.EFTSU,
1167 CREDIT_POINTS = NEW_REFERENCES.CREDIT_POINTS,
1168 LOGICAL_DELETE_DATE = NEW_REFERENCES.LOGICAL_DELETE_DATE,
1169 INVOICE_ID = NEW_REFERENCES.INVOICE_ID,
1170 ORG_UNIT_CD = NEW_REFERENCES.ORG_UNIT_CD,
1171 CLASS_STANDING = NEW_REFERENCES.CLASS_STANDING,
1172 RESIDENCY_STATUS_CD = NEW_REFERENCES.RESIDENCY_STATUS_CD,
1173 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1174 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1175 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1176 UOO_ID = NEW_REFERENCES.UOO_ID,
1177 CHG_RATE = NEW_REFERENCES.CHG_RATE,
1178 unit_set_cd = new_references.unit_set_cd,
1179 us_version_number = new_references.us_version_number,
1180 unit_type_id = new_references.unit_type_id,
1181 unit_class = new_references.unit_class,
1182 unit_mode = new_references.unit_mode,
1183 unit_level = new_references.unit_level,
1184 scope_rul_sequence_num = new_references.scope_rul_sequence_num,
1185 elm_rng_order_name = new_references.elm_rng_order_name,
1186 max_chg_elements = new_references.max_chg_elements
1187 where ROWID = X_ROWID;
1188 if (sql%notfound) then
1189 raise no_data_found;
1190 end if;
1191
1192 After_DML (
1193 p_action => 'UPDATE' ,
1194 x_rowid => X_ROWID
1195 );
1196 END update_row;
1197
1198
1199 procedure ADD_ROW (
1200 X_ROWID in out NOCOPY VARCHAR2,
1201 x_FEE_ASS_ITEM_ID IN OUT NOCOPY NUMBER,
1202 x_TRANSACTION_ID IN NUMBER,
1203 x_PERSON_ID IN NUMBER,
1204 x_STATUS IN VARCHAR2,
1205 x_FEE_TYPE IN VARCHAR2,
1206 x_FEE_CAT IN VARCHAR2,
1207 x_FEE_CAL_TYPE IN VARCHAR2,
1208 x_FEE_CI_SEQUENCE_NUMBER IN NUMBER,
1209 x_RUL_SEQUENCE_NUMBER IN NUMBER,
1210 x_S_CHG_METHOD_TYPE IN VARCHAR2,
1211 x_DESCRIPTION IN VARCHAR2,
1212 x_CHG_ELEMENTS IN NUMBER,
1213 x_AMOUNT IN NUMBER,
1214 x_FEE_EFFECTIVE_DT IN DATE,
1215 x_COURSE_CD IN VARCHAR2,
1216 x_CRS_VERSION_NUMBER IN NUMBER,
1217 x_COURSE_ATTEMPT_STATUS IN VARCHAR2,
1218 x_ATTENDANCE_MODE IN VARCHAR2,
1219 x_ATTENDANCE_TYPE IN VARCHAR2,
1220 x_UNIT_ATTEMPT_STATUS IN VARCHAR2,
1221 x_LOCATION_CD IN VARCHAR2,
1222 x_EFTSU IN NUMBER,
1223 x_CREDIT_POINTS IN NUMBER,
1224 x_LOGICAL_DELETE_DATE IN DATE,
1225 X_INVOICE_ID IN NUMBER,
1226 X_ORG_UNIT_CD IN VARCHAR2,
1227 X_CLASS_STANDING IN VARCHAR2,
1228 X_RESIDENCY_STATUS_CD IN VARCHAR2,
1229 X_MODE in VARCHAR2,
1230 X_UOO_ID IN NUMBER,
1231 X_CHG_RATE in VARCHAR2 ,
1232 x_unit_set_cd IN VARCHAR2,
1233 x_us_version_number IN NUMBER,
1234 x_unit_type_id IN NUMBER,
1235 x_unit_class IN VARCHAR2,
1236 x_unit_mode IN VARCHAR2,
1237 x_unit_level IN VARCHAR2,
1238 x_scope_rul_sequence_num IN NUMBER,
1239 x_elm_rng_order_name IN VARCHAR2,
1240 x_max_chg_elements IN NUMBER
1241 ) AS
1242 /*************************************************************
1243 Created By :syam.krishnan
1244 Date Created By :06-jul-2000
1245 Purpose :
1246 Know limitations, enhancements or remarks
1247 Change History
1248 Who When What
1249 pathipat 10-Sep-2003 Enh 3108052 - Add Unit Sets to Rate Table
1250 Added 2 new columns unit_set_cd and us_version_number
1251 (reverse chronological order - newest change first)
1252 ***************************************************************/
1253 CURSOR c1 IS
1254 SELECT rowid FROM igs_fi_fee_as_items
1255 WHERE fee_ass_item_id = x_fee_ass_item_id;
1256 BEGIN
1257 OPEN c1;
1258 FETCH c1 INTO x_rowid;
1259 IF (C1%NOTFOUND) THEN
1260 CLOSE c1;
1261 insert_row (
1262 x_rowid,
1263 x_fee_ass_item_id,
1264 x_transaction_id,
1265 x_person_id,
1266 x_status,
1267 x_fee_type,
1268 x_fee_cat,
1269 x_fee_cal_type,
1270 x_fee_ci_sequence_number,
1271 x_rul_sequence_number,
1272 x_s_chg_method_type,
1273 x_description,
1274 x_chg_elements,
1275 x_amount,
1276 x_fee_effective_dt,
1277 x_course_cd,
1278 x_crs_version_number,
1279 x_course_attempt_status,
1280 x_attendance_mode,
1281 x_attendance_type,
1282 x_unit_attempt_status,
1283 x_location_cd,
1284 x_eftsu,
1285 x_credit_points,
1286 x_logical_delete_date,
1287 x_invoice_id,
1288 x_org_unit_cd,
1289 x_class_standing,
1290 x_residency_status_cd,
1291 x_mode,
1292 x_uoo_id,
1293 x_chg_rate,
1294 x_unit_set_cd,
1295 x_us_version_number,
1296 x_unit_type_id,
1297 x_unit_class,
1298 x_unit_mode,
1299 x_unit_level,
1300 x_scope_rul_sequence_num,
1301 x_elm_rng_order_name,
1302 x_max_chg_elements
1303 );
1304 RETURN;
1305 END IF;
1306 CLOSE c1;
1307
1308 update_row (
1309 x_rowid,
1310 x_fee_ass_item_id,
1311 x_transaction_id,
1312 x_person_id,
1313 x_status,
1314 x_fee_type,
1315 x_fee_cat,
1316 x_fee_cal_type,
1317 x_fee_ci_sequence_number,
1318 x_rul_sequence_number,
1319 x_s_chg_method_type,
1320 x_description,
1321 x_chg_elements,
1322 x_amount,
1323 x_fee_effective_dt,
1324 x_course_cd,
1325 x_crs_version_number,
1326 x_course_attempt_status,
1327 x_attendance_mode,
1328 x_attendance_type,
1329 x_unit_attempt_status,
1330 x_location_cd,
1331 x_eftsu,
1332 x_credit_points,
1333 x_logical_delete_date,
1334 x_invoice_id,
1335 x_org_unit_cd,
1336 x_class_standing,
1337 x_residency_status_cd,
1338 x_mode,
1339 x_uoo_id,
1340 x_chg_rate ,
1341 x_unit_set_cd,
1342 x_us_version_number,
1343 x_unit_type_id,
1344 x_unit_class,
1345 x_unit_mode,
1346 x_unit_level,
1347 x_scope_rul_sequence_num,
1348 x_elm_rng_order_name,
1349 x_max_chg_elements
1350 );
1351 END add_row;
1352
1353 PROCEDURE delete_row ( x_rowid IN VARCHAR2 ) AS
1354 /*************************************************************
1355 Created By :syam.krishnan
1356 Date Created By :6-jul-2000
1357 Purpose :
1358 Know limitations, enhancements or remarks
1359 Change History
1360 Who When What
1361
1362 (reverse chronological order - newest change first)
1363 ***************************************************************/
1364 BEGIN
1365 before_dml (
1366 p_action => 'DELETE',
1367 x_rowid => x_rowid
1368 );
1369
1370 DELETE FROM igs_fi_fee_as_items
1371 WHERE rowid = x_rowid;
1372
1373 IF (SQL%NOTFOUND) THEN
1374 RAISE NO_DATA_FOUND;
1375 END IF;
1376
1377 after_dml (
1378 p_action => 'DELETE',
1379 x_rowid => x_rowid
1380 );
1381 END delete_row;
1382
1383 PROCEDURE get_fk_igs_en_unit_set_all (
1384 x_unit_set_cd IN VARCHAR2,
1385 x_us_version_number IN NUMBER ) AS
1386 /*************************************************************
1387 Created By : Priya Athipatla
1388 Date Created By : 18-Sep-2003
1389 Purpose : FK relation with igs_en_unit_set_all
1390 Know limitations, enhancements or remarks
1391 Change History
1392 Who When What
1393 (reverse chronological order - newest change first)
1394 ***************************************************************/
1395 CURSOR cur_rowid IS
1396 SELECT rowid
1397 FROM igs_fi_fee_as_items
1398 WHERE unit_set_cd = x_unit_set_cd
1399 AND us_version_number = x_us_version_number;
1400
1401 lv_rowid cur_rowid%ROWTYPE;
1402
1403 BEGIN
1404 OPEN cur_rowid;
1405 FETCH cur_rowid INTO lv_rowid;
1406 IF (cur_rowid%FOUND) THEN
1407 CLOSE cur_rowid;
1408 fnd_message.set_name('IGS', 'IGS_FI_FAI_EUS_FK');
1409 igs_ge_msg_stack.add;
1410 app_exception.raise_exception;
1411 RETURN;
1412 END IF;
1413 CLOSE cur_rowid;
1414 END get_fk_igs_en_unit_set_all;
1415
1416 END igs_fi_fee_as_items_pkg;
[Home] [Help]