[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_FTCI_ACCTS_PKG
Source
1 PACKAGE BODY igs_fi_ftci_accts_pkg AS
2 /* $Header: IGSSID0B.pls 120.3 2005/06/05 23:42:09 appldev $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_ftci_accts%ROWTYPE;
6 new_references igs_fi_ftci_accts%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_acct_id IN NUMBER,
12 x_fee_type IN VARCHAR2,
13 x_fee_cal_type IN VARCHAR2,
14 x_fee_ci_sequence_number IN NUMBER,
15 x_order_sequence IN NUMBER,
16 x_natural_account_segment IN VARCHAR2,
17 x_rev_account_cd IN VARCHAR2,
18 x_location_cd IN VARCHAR2,
19 x_attendance_type IN VARCHAR2,
20 x_attendance_mode IN VARCHAR2,
21 x_course_cd IN VARCHAR2,
22 x_crs_version_number IN NUMBER,
23 x_unit_cd IN VARCHAR2,
24 x_unit_version_number IN NUMBER,
25 x_org_unit_cd IN VARCHAR2,
26 x_residency_status_cd IN VARCHAR2,
27 x_uoo_id IN NUMBER,
28 x_creation_date IN DATE,
29 x_created_by IN NUMBER,
30 x_last_update_date IN DATE,
31 x_last_updated_by IN NUMBER,
32 x_last_update_login IN NUMBER,
33 x_unit_level IN VARCHAR2,
34 x_unit_type_id IN NUMBER,
35 x_unit_mode IN VARCHAR2,
36 x_unit_class IN VARCHAR2
37 ) AS
38 /*
39 || Created By : [email protected]
40 || Created On : 15-MAY-2003
41 || Purpose : Initialises the Old and New references for the columns of the table.
42 || Known limitations, enhancements or remarks :
43 || Change History :
44 || Who When What
45 || gurprsin 02-Jun-2005 Enh 3442712, Added 4 new columns.
46 || (reverse chronological order - newest change first)
47 */
48
49 CURSOR cur_old_ref_values IS
50 SELECT *
51 FROM igs_fi_ftci_accts
52 WHERE rowid = x_rowid;
53
54 BEGIN
55
56 l_rowid := x_rowid;
57
58 -- Code for setting the Old and New Reference Values.
59 -- Populate Old Values.
60 OPEN cur_old_ref_values;
61 FETCH cur_old_ref_values INTO old_references;
62 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
63 CLOSE cur_old_ref_values;
64 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
65 igs_ge_msg_stack.add;
66 app_exception.raise_exception;
67 RETURN;
68 END IF;
69 CLOSE cur_old_ref_values;
70
71 -- Populate New Values.
72 new_references.acct_id := x_acct_id;
73 new_references.fee_type := x_fee_type;
74 new_references.fee_cal_type := x_fee_cal_type;
75 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
76 new_references.order_sequence := x_order_sequence;
77 new_references.natural_account_segment := x_natural_account_segment;
78 new_references.rev_account_cd := x_rev_account_cd;
79 new_references.location_cd := x_location_cd;
80 new_references.attendance_type := x_attendance_type;
81 new_references.attendance_mode := x_attendance_mode;
82 new_references.course_cd := x_course_cd;
83 new_references.crs_version_number := x_crs_version_number;
84 new_references.unit_cd := x_unit_cd;
85 new_references.unit_version_number := x_unit_version_number;
86 new_references.org_unit_cd := x_org_unit_cd;
87 new_references.residency_status_cd := x_residency_status_cd;
88 new_references.uoo_id := x_uoo_id;
89 new_references.unit_level := x_unit_level;
90 new_references.unit_type_id := x_unit_type_id;
91 new_references.unit_mode := x_unit_mode;
92 new_references.unit_class := x_unit_class;
93
94 IF (p_action = 'UPDATE') THEN
95 new_references.creation_date := old_references.creation_date;
96 new_references.created_by := old_references.created_by;
97 ELSE
98 new_references.creation_date := x_creation_date;
99 new_references.created_by := x_created_by;
100 END IF;
101
102 new_references.last_update_date := x_last_update_date;
103 new_references.last_updated_by := x_last_updated_by;
104 new_references.last_update_login := x_last_update_login;
105
106 END set_column_values;
107
108
109 PROCEDURE check_uniqueness AS
110 /*
111 || Created By : [email protected]
112 || Created On : 15-MAY-2003
113 || Purpose : Handles the Unique Constraint logic defined for the columns.
114 || Known limitations, enhancements or remarks :
115 || Change History :
116 || Who When What
117 || gurprsin 02-Jun-2005 Enh 3442712, Added 4 new columns. in get_uk2_for_validation method
118 || (reverse chronological order - newest change first)
119 */
120 BEGIN
121
122 IF ( get_uk1_for_validation (
123 new_references.fee_type,
124 new_references.fee_cal_type,
125 new_references.fee_ci_sequence_number,
126 new_references.order_sequence
127 )
128 ) THEN
129 fnd_message.set_name ('IGS','IGS_FI_ACCT_DUP_SEQ');
130 igs_ge_msg_stack.add;
131 app_exception.raise_exception;
132 END IF;
133
134 IF ( get_uk2_for_validation (
135 new_references.fee_type,
136 new_references.fee_cal_type,
137 new_references.fee_ci_sequence_number,
138 new_references.location_cd,
139 new_references.attendance_type,
140 new_references.attendance_mode,
141 new_references.course_cd,
142 new_references.crs_version_number,
143 new_references.unit_cd,
144 new_references.unit_version_number,
145 new_references.org_unit_cd,
146 new_references.residency_status_cd,
147 new_references.uoo_id,
148 new_references.unit_level,
149 new_references.unit_type_id,
150 new_references.unit_mode,
151 new_references.unit_class
152 )
153 ) THEN
154 fnd_message.set_name ('IGS', 'IGS_FI_ACCT_ATTR_COMB_EXISTS');
155 igs_ge_msg_stack.add;
156 app_exception.raise_exception;
157 END IF;
158
159
160 END check_uniqueness;
161
162
163 PROCEDURE check_parent_existance AS
164 /*
165 || Created By : [email protected]
166 || Created On : 15-MAY-2003
167 || Purpose : Checks for the existance of Parent records.
168 || Known limitations, enhancements or remarks :
169 || Change History :
170 || Who When What
171 || gurprsin 02-Jun-2005 Enh 3442712, Added 4 new methods to check unit_level,unit_type_id,
172 || unit_clss,unit_mode existence in respective master table
173 || (reverse chronological order - newest change first)
174 */
175 BEGIN
176
177 IF (((old_references.fee_type = new_references.fee_type) AND
178 (old_references.fee_cal_type = new_references.fee_cal_type) AND
179 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
180 ((new_references.fee_type IS NULL) OR
181 (new_references.fee_cal_type IS NULL) OR
182 (new_references.fee_ci_sequence_number IS NULL))) THEN
183 NULL;
184 ELSIF NOT igs_fi_f_typ_ca_inst_pkg.get_pk_for_validation (
185 new_references.fee_type,
186 new_references.fee_cal_type,
187 new_references.fee_ci_sequence_number
188 ) THEN
189 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
190 igs_ge_msg_stack.add;
191 app_exception.raise_exception;
192 END IF;
193
194 IF (((old_references.rev_account_cd = new_references.rev_account_cd)) OR
195 ((new_references.rev_account_cd IS NULL))) THEN
196 NULL;
197 ELSIF NOT igs_fi_acc_pkg.get_pk_for_validation (
198 new_references.rev_account_cd
199 ) THEN
200 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
201 igs_ge_msg_stack.add;
202 app_exception.raise_exception;
203 END IF;
204
205 IF (((old_references.location_cd = new_references.location_cd)) OR
206 ((new_references.location_cd IS NULL))) THEN
207 NULL;
208 ELSIF NOT igs_ad_location_pkg.get_pk_for_validation (
209 new_references.location_cd,
210 'N'
211 ) THEN
212 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
213 igs_ge_msg_stack.add;
214 app_exception.raise_exception;
215 END IF;
216
217 IF (((old_references.attendance_type = new_references.attendance_type)) OR
218 ((new_references.attendance_type IS NULL))) THEN
219 NULL;
220 ELSIF NOT igs_en_atd_type_pkg.get_pk_for_validation (
221 new_references.attendance_type
222 ) THEN
223 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
224 igs_ge_msg_stack.add;
225 app_exception.raise_exception;
226 END IF;
227
228 IF (((old_references.attendance_mode = new_references.attendance_mode)) OR
229 ((new_references.attendance_mode IS NULL))) THEN
230 NULL;
231 ELSIF NOT igs_en_atd_mode_pkg.get_pk_for_validation (
232 new_references.attendance_mode
233 ) THEN
234 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
235 igs_ge_msg_stack.add;
236 app_exception.raise_exception;
237 END IF;
238
239 IF (((old_references.course_cd = new_references.course_cd) AND
240 (old_references.crs_version_number = new_references.crs_version_number)) OR
241 ((new_references.course_cd IS NULL) OR
242 (new_references.crs_version_number IS NULL))) THEN
243 NULL;
244 ELSIF NOT igs_ps_ver_pkg.get_pk_for_validation (
245 new_references.course_cd,
246 new_references.crs_version_number
247 ) THEN
248 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
249 igs_ge_msg_stack.add;
250 app_exception.raise_exception;
251 END IF;
252
253 IF (((old_references.unit_cd = new_references.unit_cd) AND
254 (old_references.unit_version_number = new_references.unit_version_number)) OR
255 ((new_references.unit_cd IS NULL) OR
256 (new_references.unit_version_number IS NULL))) THEN
257 NULL;
258 ELSIF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (
259 new_references.unit_cd,
260 new_references.unit_version_number
261 ) THEN
262 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
263 igs_ge_msg_stack.add;
264 app_exception.raise_exception;
265 END IF;
266
267 IF (((old_references.uoo_id = new_references.uoo_id)) OR
268 ((new_references.uoo_id IS NULL))) THEN
269 NULL;
270 ELSIF NOT igs_ps_unit_ofr_opt_pkg.get_uk_for_validation (
271 new_references.uoo_id
272 ) THEN
273 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
274 igs_ge_msg_stack.add;
275 app_exception.raise_exception;
276 END IF;
277
278 IF (((old_references.unit_level = new_references.unit_level)) OR
279 ((new_references.unit_level IS NULL))) THEN
280 NULL;
281 ELSIF NOT igs_ps_unit_level_pkg.get_pk_for_validation (
282 new_references.unit_level
283 ) THEN
284 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
285 igs_ge_msg_stack.add;
286 app_exception.raise_exception;
287 END IF;
288
289 IF (((old_references.unit_type_id = new_references.unit_type_id)) OR
290 ((new_references.unit_type_id IS NULL))) THEN
291 NULL;
292 ELSIF NOT igs_ps_unit_type_lvl_pkg.get_pk_for_validation (
293 new_references.unit_type_id
294 ) THEN
295 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
296 igs_ge_msg_stack.add;
297 app_exception.raise_exception;
298 END IF;
299
300 IF (((old_references.unit_mode = new_references.unit_mode)) OR
301 ((new_references.unit_mode IS NULL))) THEN
302 NULL;
303 ELSIF NOT igs_as_unit_mode_pkg.get_pk_for_validation (
304 new_references.unit_mode
305 ) THEN
306 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
307 igs_ge_msg_stack.add;
308 app_exception.raise_exception;
309 END IF;
310
311 IF (((old_references.unit_class = new_references.unit_class)) OR
312 ((new_references.unit_class IS NULL))) THEN
313 NULL;
314 ELSIF NOT igs_as_unit_class_pkg.get_pk_for_validation (
315 new_references.unit_class
316 ) THEN
317 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
318 igs_ge_msg_stack.add;
319 app_exception.raise_exception;
320 END IF;
321
322 END check_parent_existance;
323
324
325 FUNCTION get_pk_for_validation (
326 x_acct_id IN NUMBER
327 ) RETURN BOOLEAN AS
328 /*
329 || Created By : [email protected]
330 || Created On : 15-MAY-2003
331 || Purpose : Validates the Primary Key of the table.
332 || Known limitations, enhancements or remarks :
333 || Change History :
334 || Who When What
335 || (reverse chronological order - newest change first)
336 */
337 CURSOR cur_rowid IS
338 SELECT rowid
339 FROM igs_fi_ftci_accts
340 WHERE acct_id = x_acct_id
341 FOR UPDATE NOWAIT;
342
343 lv_rowid cur_rowid%ROWTYPE;
344
345 BEGIN
346
347 OPEN cur_rowid;
348 FETCH cur_rowid INTO lv_rowid;
349 IF (cur_rowid%FOUND) THEN
350 CLOSE cur_rowid;
351 RETURN(TRUE);
352 ELSE
353 CLOSE cur_rowid;
354 RETURN(FALSE);
355 END IF;
356
357 END get_pk_for_validation;
358
359
360 FUNCTION get_uk1_for_validation (
361 x_fee_type IN VARCHAR2,
362 x_fee_cal_type IN VARCHAR2,
363 x_fee_ci_sequence_number IN NUMBER,
364 x_order_sequence IN NUMBER
365 ) RETURN BOOLEAN AS
366 /*
367 || Created By : [email protected]
368 || Created On : 15-MAY-2003
369 || Purpose : Validates the Unique Keys of the table.
370 || Known limitations, enhancements or remarks :
371 || Change History :
372 || Who When What
373 || (reverse chronological order - newest change first)
374 */
375 CURSOR cur_rowid IS
376 SELECT rowid
377 FROM igs_fi_ftci_accts
378 WHERE fee_type = x_fee_type
379 AND fee_cal_type = x_fee_cal_type
380 AND fee_ci_sequence_number = x_fee_ci_sequence_number
381 AND order_sequence = x_order_sequence
382 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
383
384 lv_rowid cur_rowid%RowType;
385
386 BEGIN
387
388 OPEN cur_rowid;
389 FETCH cur_rowid INTO lv_rowid;
390 IF (cur_rowid%FOUND) THEN
391 CLOSE cur_rowid;
392 RETURN (TRUE);
393 ELSE
394 CLOSE cur_rowid;
395 RETURN(FALSE);
396 END IF;
397
398 END get_uk1_for_validation;
399
400 FUNCTION get_uk2_for_validation (
401 x_fee_type IN VARCHAR2,
402 x_fee_cal_type IN VARCHAR2,
403 x_fee_ci_sequence_number IN NUMBER,
404 x_location_cd IN VARCHAR2,
405 x_attendance_type IN VARCHAR2,
406 x_attendance_mode IN VARCHAR2,
407 x_course_cd IN VARCHAR2,
408 x_crs_version_number IN NUMBER,
409 x_unit_cd IN VARCHAR2,
410 x_unit_version_number IN NUMBER,
411 x_org_unit_cd IN VARCHAR2,
412 x_residency_status_cd IN VARCHAR2,
413 x_uoo_id IN NUMBER,
414 x_unit_level IN VARCHAR2,
415 x_unit_type_id IN NUMBER,
416 x_unit_mode IN VARCHAR2,
417 x_unit_class IN VARCHAR2
418 )RETURN BOOLEAN AS
419 /*
420 || Created By : [email protected]
421 || Created On : 15-MAY-2003
422 || Purpose : Validates the Unique Keys of the table.
423 || Known limitations, enhancements or remarks :
424 || Change History :
425 || Who When What
426 || gurprsin 02-Jun-2005 Enh 3442712, Added 4 new columns.
427 || (reverse chronological order - newest change first)
428 */
429 CURSOR cur_rowid IS
430 SELECT rowid
431 FROM igs_fi_ftci_accts
432 WHERE fee_type = x_fee_type
433 AND fee_cal_type = x_fee_cal_type
434 AND fee_ci_sequence_number = x_fee_ci_sequence_number
435 AND ((location_cd = x_location_cd) OR (x_location_cd IS NULL AND location_cd IS NULL))
436 AND ((attendance_type = x_attendance_type) OR (x_attendance_type IS NULL AND attendance_type IS NULL))
437 AND ((attendance_mode = x_attendance_mode) OR (x_attendance_mode IS NULL AND attendance_mode IS NULL))
438 AND ((course_cd = x_course_cd) OR (x_course_cd IS NULL AND course_cd IS NULL))
439 AND ((crs_version_number = x_crs_version_number) OR (x_crs_version_number IS NULL AND crs_version_number IS NULL))
440 AND ((unit_cd = x_unit_cd) OR (x_unit_cd IS NULL OR unit_cd IS NULL))
441 AND ((unit_version_number = x_unit_version_number) OR (x_unit_version_number IS NULL AND unit_version_number IS NULL))
442 AND ((org_unit_cd = x_org_unit_cd) OR (x_org_unit_cd IS NULL AND org_unit_cd IS NULL))
443 AND ((residency_status_cd = x_residency_status_cd) OR (x_residency_status_cd IS NULL AND residency_status_cd IS NULL))
444 AND ((uoo_id = x_uoo_id) OR (x_uoo_id IS NULL AND uoo_id IS NULL))
445 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
446 AND ((unit_level = x_unit_level) OR (x_unit_level IS NULL AND unit_level IS NULL))
447 AND ((unit_type_id = x_unit_type_id) OR (x_unit_type_id IS NULL AND unit_type_id IS NULL))
448 AND ((unit_mode = x_unit_mode) OR (x_unit_mode IS NULL AND unit_mode IS NULL))
449 AND ((unit_class = x_unit_class) OR (x_unit_class IS NULL AND unit_class IS NULL));
450
451 lv_rowid cur_rowid%ROWTYPE;
452
453 BEGIN
454
455 OPEN cur_rowid;
456 FETCH cur_rowid INTO lv_rowid;
457 IF (cur_rowid%FOUND) THEN
458 CLOSE cur_rowid;
459 RETURN (TRUE);
460 ELSE
461 CLOSE cur_rowid;
462 RETURN(FALSE);
463 END IF;
464 END get_uk2_for_validation;
465
466 PROCEDURE get_fk_igs_fi_f_typ_ca_inst (
467 x_fee_type IN VARCHAR2,
468 x_fee_cal_type IN VARCHAR2,
469 x_fee_ci_sequence_number IN NUMBER
470 ) AS
471 /*
472 || Created By : [email protected]
473 || Created On : 15-MAY-2003
474 || Purpose : Validates the Foreign Keys for the table.
475 || Known limitations, enhancements or remarks :
476 || Change History :
477 || Who When What
478 || (reverse chronological order - newest change first)
479 */
480 CURSOR cur_rowid IS
481 SELECT rowid
482 FROM igs_fi_ftci_accts
483 WHERE ((fee_cal_type = x_fee_cal_type) AND
484 (fee_ci_sequence_number = x_fee_ci_sequence_number) AND
485 (fee_type = x_fee_type));
486
487 lv_rowid cur_rowid%ROWTYPE;
488
489 BEGIN
490
491 OPEN cur_rowid;
492 FETCH cur_rowid INTO lv_rowid;
493 IF (cur_rowid%FOUND) THEN
494 CLOSE cur_rowid;
495 fnd_message.set_name ('IGS', 'IGS_FI_FICA_FTCI_FK');
496 igs_ge_msg_stack.add;
497 app_exception.raise_exception;
498 RETURN;
499 END IF;
500 CLOSE cur_rowid;
501
502 END get_fk_igs_fi_f_typ_ca_inst;
503
504
505 PROCEDURE get_fk_igs_fi_acc (
506 x_account_cd IN VARCHAR2
507 ) AS
508 /*
509 || Created By : [email protected]
510 || Created On : 15-MAY-2003
511 || Purpose : Validates the Foreign Keys for the table.
512 || Known limitations, enhancements or remarks :
513 || Change History :
514 || Who When What
515 || (reverse chronological order - newest change first)
516 */
517 CURSOR cur_rowid IS
518 SELECT rowid
519 FROM igs_fi_ftci_accts
520 WHERE ((rev_account_cd = x_account_cd));
521
522 lv_rowid cur_rowid%ROWTYPE;
523
524 BEGIN
525
526 OPEN cur_rowid;
527 FETCH cur_rowid INTO lv_rowid;
528 IF (cur_rowid%FOUND) THEN
529 CLOSE cur_rowid;
530 fnd_message.set_name ('IGS', 'IGS_FI_FICA_ACC_FK');
531 igs_ge_msg_stack.add;
532 app_exception.raise_exception;
533 RETURN;
534 END IF;
535 CLOSE cur_rowid;
536
537 END get_fk_igs_fi_acc;
538
539
540 PROCEDURE get_fk_igs_ad_location (
541 x_location_cd IN VARCHAR2
542 ) AS
543 /*
544 || Created By : [email protected]
545 || Created On : 15-MAY-2003
546 || Purpose : Validates the Foreign Keys for the table.
547 || Known limitations, enhancements or remarks :
548 || Change History :
549 || Who When What
550 || (reverse chronological order - newest change first)
551 */
552 CURSOR cur_rowid IS
553 SELECT rowid
554 FROM igs_fi_ftci_accts
555 WHERE ((location_cd = x_location_cd));
556
557 lv_rowid cur_rowid%ROWTYPE;
558
559 BEGIN
560
561 OPEN cur_rowid;
562 FETCH cur_rowid INTO lv_rowid;
563 IF (cur_rowid%FOUND) THEN
564 CLOSE cur_rowid;
565 fnd_message.set_name ('IGS', 'IGS_FI_FICA_LOC_FK');
566 igs_ge_msg_stack.add;
567 app_exception.raise_exception;
568 RETURN;
569 END IF;
570 CLOSE cur_rowid;
571
572 END get_fk_igs_ad_location;
573
574
575 PROCEDURE get_fk_igs_en_atd_type (
576 x_attendance_type IN VARCHAR2
577 ) AS
578 /*
579 || Created By : [email protected]
580 || Created On : 15-MAY-2003
581 || Purpose : Validates the Foreign Keys for the table.
582 || Known limitations, enhancements or remarks :
583 || Change History :
584 || Who When What
585 || (reverse chronological order - newest change first)
586 */
587 CURSOR cur_rowid IS
588 SELECT rowid
589 FROM igs_fi_ftci_accts
590 WHERE ((attendance_type = x_attendance_type));
591
592 lv_rowid cur_rowid%ROWTYPE;
593
594 BEGIN
595
596 OPEN cur_rowid;
597 FETCH cur_rowid INTO lv_rowid;
598 IF (cur_rowid%FOUND) THEN
599 CLOSE cur_rowid;
600 fnd_message.set_name ('IGS', 'IGS_FI_FICA_ATT_FK');
601 igs_ge_msg_stack.add;
602 app_exception.raise_exception;
603 RETURN;
604 END IF;
605 CLOSE cur_rowid;
606
607 END get_fk_igs_en_atd_type;
608
609
610 PROCEDURE get_fk_igs_en_atd_mode (
611 x_attendance_mode IN VARCHAR2
612 ) AS
613 /*
614 || Created By : [email protected]
615 || Created On : 15-MAY-2003
616 || Purpose : Validates the Foreign Keys for the table.
617 || Known limitations, enhancements or remarks :
618 || Change History :
619 || Who When What
620 || (reverse chronological order - newest change first)
621 */
622 CURSOR cur_rowid IS
623 SELECT rowid
624 FROM igs_fi_ftci_accts
625 WHERE ((attendance_mode = x_attendance_mode));
626
627 lv_rowid cur_rowid%ROWTYPE;
628
629 BEGIN
630
631 OPEN cur_rowid;
632 FETCH cur_rowid INTO lv_rowid;
633 IF (cur_rowid%FOUND) THEN
634 CLOSE cur_rowid;
635 fnd_message.set_name ('IGS', 'IGS_FI_FICA_AM_FK');
636 igs_ge_msg_stack.add;
637 app_exception.raise_exception;
638 RETURN;
639 END IF;
640 CLOSE cur_rowid;
641
642 END get_fk_igs_en_atd_mode;
643
644
645 PROCEDURE get_fk_igs_ps_ver (
646 x_course_cd IN VARCHAR2,
647 x_version_number IN NUMBER
648 ) AS
649 /*
650 || Created By : [email protected]
651 || Created On : 15-MAY-2003
652 || Purpose : Validates the Foreign Keys for the table.
653 || Known limitations, enhancements or remarks :
654 || Change History :
655 || Who When What
656 || (reverse chronological order - newest change first)
657 */
658 CURSOR cur_rowid IS
659 SELECT rowid
660 FROM igs_fi_ftci_accts
661 WHERE ((course_cd = x_course_cd) AND
662 (crs_version_number = x_version_number));
663
664 lv_rowid cur_rowid%ROWTYPE;
665
666 BEGIN
667
668 OPEN cur_rowid;
669 FETCH cur_rowid INTO lv_rowid;
670 IF (cur_rowid%FOUND) THEN
671 CLOSE cur_rowid;
672 fnd_message.set_name ('IGS', 'IGS_FI_FICA_SCA_FK');
673 igs_ge_msg_stack.add;
674 app_exception.raise_exception;
675 RETURN;
676 END IF;
677 CLOSE cur_rowid;
678
679 END get_fk_igs_ps_ver;
680
681 PROCEDURE get_fk_igs_ps_unit_ver (
682 x_unit_cd IN VARCHAR2,
683 x_version_number IN NUMBER
684 ) AS
685 /*
686 || Created By : [email protected]
687 || Created On : 15-MAY-2003
688 || Purpose : Validates the Foreign Keys for the table.
689 || Known limitations, enhancements or remarks :
690 || Change History :
691 || Who When What
692 || (reverse chronological order - newest change first)
693 */
694 CURSOR cur_rowid IS
695 SELECT rowid
696 FROM igs_fi_ftci_accts
697 WHERE ((unit_cd = x_unit_cd) AND
698 (unit_version_number = x_version_number));
699
700 lv_rowid cur_rowid%ROWTYPE;
701
702 BEGIN
703
704 OPEN cur_rowid;
705 FETCH cur_rowid INTO lv_rowid;
706 IF (cur_rowid%FOUND) THEN
707 CLOSE cur_rowid;
708 fnd_message.set_name ('IGS', 'IGS_FI_FICA_UN_FK');
709 igs_ge_msg_stack.add;
710 app_exception.raise_exception;
711 RETURN;
712 END IF;
713 CLOSE cur_rowid;
714
715 END get_fk_igs_ps_unit_ver;
716
717
718
719
720 PROCEDURE get_ufk_igs_ps_unit_ofr_opt (
721 x_uoo_id IN NUMBER
722 ) AS
723 /*
724 || Created By : [email protected]
725 || Created On : 15-MAY-2003
726 || Purpose : Validates the Foreign Keys for the table.
727 || Known limitations, enhancements or remarks :
728 || Change History :
729 || Who When What
730 || (reverse chronological order - newest change first)
731 */
732 CURSOR cur_rowid IS
733 SELECT rowid
734 FROM igs_fi_ftci_accts
735 WHERE ((uoo_id = x_uoo_id));
736
737 lv_rowid cur_rowid%ROWTYPE;
738
739 BEGIN
740
741 OPEN cur_rowid;
742 FETCH cur_rowid INTO lv_rowid;
743 IF (cur_rowid%FOUND) THEN
744 CLOSE cur_rowid;
745 fnd_message.set_name ('IGS', 'IGS_FI_FICA_UOO_FK');
746 igs_ge_msg_stack.add;
747 app_exception.raise_exception;
748 RETURN;
749 END IF;
750 CLOSE cur_rowid;
751
752 END get_ufk_igs_ps_unit_ofr_opt;
753
754 PROCEDURE get_fk_igs_as_unit_mode (
755 x_unit_mode IN VARCHAR2
756 ) AS
757 /*
758 || Created By : [email protected]
759 || Created On : 03-MAY-2005
760 || Purpose : Validates the Foreign Keys for the table.
761 || Known limitations, enhancements or remarks :
762 || Change History :
763 || Who When What
764 || (reverse chronological order - newest change first)
765 */
766 CURSOR cur_rowid IS
767 SELECT rowid
768 FROM igs_fi_ftci_accts
769 WHERE ((unit_mode = x_unit_mode));
770
771 lv_rowid cur_rowid%ROWTYPE;
772
773 BEGIN
774
775 OPEN cur_rowid;
776 FETCH cur_rowid INTO lv_rowid;
777 IF (cur_rowid%FOUND) THEN
778 CLOSE cur_rowid;
779 fnd_message.set_name ('IGS', 'IGS_FI_ACCTS_UM_FK');
780 igs_ge_msg_stack.add;
781 app_exception.raise_exception;
782 RETURN;
783 END IF;
784 CLOSE cur_rowid;
785
786 END get_fk_igs_as_unit_mode;
787
788
789
790 PROCEDURE before_dml (
791 p_action IN VARCHAR2,
792 x_rowid IN VARCHAR2,
793 x_acct_id IN NUMBER,
794 x_fee_type IN VARCHAR2,
795 x_fee_cal_type IN VARCHAR2,
796 x_fee_ci_sequence_number IN NUMBER,
797 x_order_sequence IN NUMBER,
798 x_natural_account_segment IN VARCHAR2,
799 x_rev_account_cd IN VARCHAR2,
800 x_location_cd IN VARCHAR2,
801 x_attendance_type IN VARCHAR2,
802 x_attendance_mode IN VARCHAR2,
803 x_course_cd IN VARCHAR2,
804 x_crs_version_number IN NUMBER,
805 x_unit_cd IN VARCHAR2,
806 x_unit_version_number IN NUMBER,
807 x_org_unit_cd IN VARCHAR2,
808 x_residency_status_cd IN VARCHAR2,
809 x_uoo_id IN NUMBER,
810 x_creation_date IN DATE,
811 x_created_by IN NUMBER,
812 x_last_update_date IN DATE,
813 x_last_updated_by IN NUMBER,
814 x_last_update_login IN NUMBER,
815 x_unit_level IN VARCHAR2,
816 x_unit_type_id IN NUMBER,
817 x_unit_mode IN VARCHAR2,
818 x_unit_class IN VARCHAR2
819 ) AS
820 /*
821 || Created By : [email protected]
822 || Created On : 15-MAY-2003
823 || Purpose : Initialises the columns, Checks Constraints, Calls the
824 || Trigger Handlers for the table, before any DML operation.
825 || Known limitations, enhancements or remarks :
826 || Change History :
827 || Who When What
828 || gurprsin 02-Jun-2005 Enh 3442712, Added 4 new columns.
829 || (reverse chronological order - newest change first)
830 */
831 BEGIN
832
833 set_column_values (
834 p_action,
835 x_rowid,
836 x_acct_id,
837 x_fee_type,
838 x_fee_cal_type,
839 x_fee_ci_sequence_number,
840 x_order_sequence,
841 x_natural_account_segment,
842 x_rev_account_cd,
843 x_location_cd,
844 x_attendance_type,
845 x_attendance_mode,
846 x_course_cd,
847 x_crs_version_number,
848 x_unit_cd,
849 x_unit_version_number,
850 x_org_unit_cd,
851 x_residency_status_cd,
852 x_uoo_id,
853 x_creation_date,
854 x_created_by,
855 x_last_update_date,
856 x_last_updated_by,
857 x_last_update_login,
858 x_unit_level,
859 x_unit_type_id,
860 x_unit_mode,
861 x_unit_class
862 );
863
864 IF (p_action = 'INSERT') THEN
865 -- Call all the procedures related to Before Insert.
866 IF ( get_pk_for_validation(
867 new_references.acct_id
868 )
869 ) THEN
870 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
871 igs_ge_msg_stack.add;
872 app_exception.raise_exception;
873 END IF;
874 check_uniqueness;
875 check_parent_existance;
876 ELSIF (p_action = 'UPDATE') THEN
877 -- Call all the procedures related to Before Update.
878 check_uniqueness;
879 check_parent_existance;
880 ELSIF (p_action = 'VALIDATE_INSERT') THEN
881 -- Call all the procedures related to Before Insert.
882 IF ( get_pk_for_validation (
883 new_references.acct_id
884 )
885 ) THEN
886 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
887 igs_ge_msg_stack.add;
888 app_exception.raise_exception;
889 END IF;
890 check_uniqueness;
891 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
892 check_uniqueness;
893 END IF;
894
895 l_rowid := NULL;
896
897 END before_dml;
898
899
900 PROCEDURE insert_row (
901 x_rowid IN OUT NOCOPY VARCHAR2,
902 x_acct_id IN OUT NOCOPY NUMBER,
903 x_fee_type IN VARCHAR2,
904 x_fee_cal_type IN VARCHAR2,
905 x_fee_ci_sequence_number IN NUMBER,
906 x_order_sequence IN NUMBER,
907 x_natural_account_segment IN VARCHAR2,
908 x_rev_account_cd IN VARCHAR2,
909 x_location_cd IN VARCHAR2,
910 x_attendance_type IN VARCHAR2,
911 x_attendance_mode IN VARCHAR2,
912 x_course_cd IN VARCHAR2,
913 x_crs_version_number IN NUMBER,
914 x_unit_cd IN VARCHAR2,
915 x_unit_version_number IN NUMBER,
916 x_org_unit_cd IN VARCHAR2,
917 x_residency_status_cd IN VARCHAR2,
918 x_uoo_id IN NUMBER,
919 x_mode IN VARCHAR2,
920 x_unit_level IN VARCHAR2,
921 x_unit_type_id IN NUMBER,
922 x_unit_mode IN VARCHAR2,
923 x_unit_class IN VARCHAR2
924 ) AS
925 /*
926 || Created By : [email protected]
927 || Created On : 15-MAY-2003
928 || Purpose : Handles the INSERT DML logic for the table.
929 || Known limitations, enhancements or remarks :
930 || Change History :
931 || Who When What
932 || gurprsin 02-Jun-2005 Enh 3442712, Added 4 new columns.
933 || (reverse chronological order - newest change first)
934 */
935
936 x_last_update_date DATE;
937 x_last_updated_by NUMBER;
938 x_last_update_login NUMBER;
939 x_request_id NUMBER;
940 x_program_id NUMBER;
941 x_program_application_id NUMBER;
942 x_program_update_date DATE;
943
944 BEGIN
945
946 x_last_update_date := SYSDATE;
947 IF (x_mode = 'I') THEN
948 x_last_updated_by := 1;
949 x_last_update_login := 0;
950 ELSIF (x_mode = 'R') THEN
951 x_last_updated_by := fnd_global.user_id;
952 IF (x_last_updated_by IS NULL) THEN
953 x_last_updated_by := -1;
954 END IF;
955 x_last_update_login := fnd_global.login_id;
956 IF (x_last_update_login IS NULL) THEN
957 x_last_update_login := -1;
958 END IF;
959 x_request_id := fnd_global.conc_request_id;
960 x_program_id := fnd_global.conc_program_id;
961 x_program_application_id := fnd_global.prog_appl_id;
962
963 IF (x_request_id = -1) THEN
964 x_request_id := NULL;
965 x_program_id := NULL;
966 x_program_application_id := NULL;
967 x_program_update_date := NULL;
968 ELSE
969 x_program_update_date := SYSDATE;
970 END IF;
971 ELSE
972 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
973 fnd_message.set_token ('ROUTINE', 'IGS_FI_FTCI_ACCTS_PKG.INSERT_ROW');
974 igs_ge_msg_stack.add;
975 app_exception.raise_exception;
976 END IF;
977
978 SELECT igs_fi_ftci_accts_s.NEXTVAL INTO x_acct_id FROM dual;
979
980 before_dml(
981 p_action => 'INSERT',
982 x_rowid => x_rowid,
983 x_acct_id => x_acct_id,
984 x_fee_type => x_fee_type,
985 x_fee_cal_type => x_fee_cal_type,
986 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
987 x_order_sequence => x_order_sequence,
988 x_natural_account_segment => x_natural_account_segment,
989 x_rev_account_cd => x_rev_account_cd,
990 x_location_cd => x_location_cd,
991 x_attendance_type => x_attendance_type,
992 x_attendance_mode => x_attendance_mode,
993 x_course_cd => x_course_cd,
994 x_crs_version_number => x_crs_version_number,
995 x_unit_cd => x_unit_cd,
996 x_unit_version_number => x_unit_version_number,
997 x_org_unit_cd => x_org_unit_cd,
998 x_residency_status_cd => x_residency_status_cd,
999 x_uoo_id => x_uoo_id,
1000 x_creation_date => x_last_update_date,
1001 x_created_by => x_last_updated_by,
1002 x_last_update_date => x_last_update_date,
1003 x_last_updated_by => x_last_updated_by,
1004 x_last_update_login => x_last_update_login,
1005 x_unit_level => x_unit_level,
1006 x_unit_type_id => x_unit_type_id,
1007 x_unit_mode => x_unit_mode,
1008 x_unit_class => x_unit_class
1009 );
1010
1011 INSERT INTO igs_fi_ftci_accts (
1012 acct_id,
1013 fee_type,
1014 fee_cal_type,
1015 fee_ci_sequence_number,
1016 order_sequence,
1017 natural_account_segment,
1018 rev_account_cd,
1019 location_cd,
1020 attendance_type,
1021 attendance_mode,
1022 course_cd,
1023 crs_version_number,
1024 unit_cd,
1025 unit_version_number,
1026 org_unit_cd,
1027 residency_status_cd,
1028 uoo_id,
1029 creation_date,
1030 created_by,
1031 last_update_date,
1032 last_updated_by,
1033 last_update_login,
1034 request_id,
1035 program_id,
1036 program_application_id,
1037 program_update_date,
1038 unit_level,
1039 unit_type_id,
1040 unit_mode,
1041 unit_class
1042 ) VALUES (
1043 new_references.acct_id,
1044 new_references.fee_type,
1045 new_references.fee_cal_type,
1046 new_references.fee_ci_sequence_number,
1047 new_references.order_sequence,
1048 new_references.natural_account_segment,
1049 new_references.rev_account_cd,
1050 new_references.location_cd,
1051 new_references.attendance_type,
1052 new_references.attendance_mode,
1053 new_references.course_cd,
1054 new_references.crs_version_number,
1055 new_references.unit_cd,
1056 new_references.unit_version_number,
1057 new_references.org_unit_cd,
1058 new_references.residency_status_cd,
1059 new_references.uoo_id,
1060 x_last_update_date,
1061 x_last_updated_by,
1062 x_last_update_date,
1063 x_last_updated_by,
1064 x_last_update_login ,
1065 x_request_id,
1066 x_program_id,
1067 x_program_application_id,
1068 x_program_update_date,
1069 new_references.unit_level,
1070 new_references.unit_type_id,
1071 new_references.unit_mode,
1072 new_references.unit_class
1073 ) RETURNING ROWID,ACCT_ID INTO x_rowid,x_acct_id;
1074
1075 END insert_row;
1076
1077
1078 PROCEDURE lock_row (
1079 x_rowid IN VARCHAR2,
1080 x_acct_id IN NUMBER,
1081 x_fee_type IN VARCHAR2,
1082 x_fee_cal_type IN VARCHAR2,
1083 x_fee_ci_sequence_number IN NUMBER,
1084 x_order_sequence IN NUMBER,
1085 x_natural_account_segment IN VARCHAR2,
1086 x_rev_account_cd IN VARCHAR2,
1087 x_location_cd IN VARCHAR2,
1088 x_attendance_type IN VARCHAR2,
1089 x_attendance_mode IN VARCHAR2,
1090 x_course_cd IN VARCHAR2,
1091 x_crs_version_number IN NUMBER,
1092 x_unit_cd IN VARCHAR2,
1093 x_unit_version_number IN NUMBER,
1094 x_org_unit_cd IN VARCHAR2,
1095 x_residency_status_cd IN VARCHAR2,
1096 x_uoo_id IN NUMBER,
1097 x_unit_level IN VARCHAR2,
1098 x_unit_type_id IN NUMBER,
1099 x_unit_mode IN VARCHAR2,
1100 x_unit_class IN VARCHAR2
1101 ) AS
1102 /*
1103 || Created By : [email protected]
1104 || Created On : 15-MAY-2003
1105 || Purpose : Handles the LOCK mechanism for the table.
1106 || Known limitations, enhancements or remarks :
1107 || Change History :
1108 || Who When What
1109 || gurprsin 02-Jun-2005 Enh 3442712, Added 4 new columns.
1110 || (reverse chronological order - newest change first)
1111 */
1112 CURSOR c1 IS
1113 SELECT
1114 fee_type,
1115 fee_cal_type,
1116 fee_ci_sequence_number,
1117 order_sequence,
1118 natural_account_segment,
1119 rev_account_cd,
1120 location_cd,
1121 attendance_type,
1122 attendance_mode,
1123 course_cd,
1124 crs_version_number,
1125 unit_cd,
1126 unit_version_number,
1127 org_unit_cd,
1128 residency_status_cd,
1129 uoo_id,
1130 unit_level,
1131 unit_type_id,
1132 unit_mode,
1133 unit_class
1134 FROM igs_fi_ftci_accts
1135 WHERE rowid = x_rowid
1136 FOR UPDATE NOWAIT;
1137
1138 tlinfo c1%ROWTYPE;
1139
1140 BEGIN
1141
1142 OPEN c1;
1143 FETCH c1 INTO tlinfo;
1144 IF (c1%notfound) THEN
1145 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1146 igs_ge_msg_stack.add;
1147 CLOSE c1;
1148 app_exception.raise_exception;
1149 RETURN;
1150 END IF;
1151 CLOSE c1;
1152
1153 IF (
1154 (tlinfo.fee_type = x_fee_type)
1155 AND (tlinfo.fee_cal_type = x_fee_cal_type)
1156 AND (tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number)
1157 AND (tlinfo.order_sequence = x_order_sequence)
1158 AND ((tlinfo.natural_account_segment = x_natural_account_segment) OR ((tlinfo.natural_account_segment IS NULL) AND (x_natural_account_segment IS NULL)))
1159 AND ((tlinfo.rev_account_cd = x_rev_account_cd) OR ((tlinfo.rev_account_cd IS NULL) AND (X_rev_account_cd IS NULL)))
1160 AND ((tlinfo.location_cd = x_location_cd) OR ((tlinfo.location_cd IS NULL) AND (X_location_cd IS NULL)))
1161 AND ((tlinfo.attendance_type = x_attendance_type) OR ((tlinfo.attendance_type IS NULL) AND (X_attendance_type IS NULL)))
1162 AND ((tlinfo.attendance_mode = x_attendance_mode) OR ((tlinfo.attendance_mode IS NULL) AND (X_attendance_mode IS NULL)))
1163 AND ((tlinfo.course_cd = x_course_cd) OR ((tlinfo.course_cd IS NULL) AND (X_course_cd IS NULL)))
1164 AND ((tlinfo.crs_version_number = x_crs_version_number) OR ((tlinfo.crs_version_number IS NULL) AND (X_crs_version_number IS NULL)))
1165 AND ((tlinfo.unit_cd = x_unit_cd) OR ((tlinfo.unit_cd IS NULL) AND (X_unit_cd IS NULL)))
1166 AND ((tlinfo.unit_version_number = x_unit_version_number) OR ((tlinfo.unit_version_number IS NULL) AND (X_unit_version_number IS NULL)))
1167 AND ((tlinfo.org_unit_cd = x_org_unit_cd) OR ((tlinfo.org_unit_cd IS NULL) AND (X_org_unit_cd IS NULL)))
1168 AND ((tlinfo.residency_status_cd = x_residency_status_cd) OR ((tlinfo.residency_status_cd IS NULL) AND (X_residency_status_cd IS NULL)))
1169 AND ((tlinfo.uoo_id = x_uoo_id) OR ((tlinfo.uoo_id IS NULL) AND (X_uoo_id IS NULL)))
1170 AND ((tlinfo.unit_level = x_unit_level) OR ((tlinfo.unit_level IS NULL) AND (X_unit_level IS NULL)))
1171 AND ((tlinfo.unit_type_id = x_unit_type_id) OR ((tlinfo.unit_type_id IS NULL) AND (X_unit_type_id IS NULL)))
1172 AND ((tlinfo.unit_mode = x_unit_mode) OR ((tlinfo.unit_mode IS NULL) AND (X_unit_mode IS NULL)))
1173 AND ((tlinfo.unit_class = x_unit_class) OR ((tlinfo.unit_class IS NULL) AND (X_unit_class IS NULL)))
1174 ) THEN
1175 NULL;
1176 ELSE
1177 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1178 igs_ge_msg_stack.add;
1179 app_exception.raise_exception;
1180 END IF;
1181
1182 RETURN;
1183
1184 END lock_row;
1185
1186
1187 PROCEDURE update_row (
1188 x_rowid IN VARCHAR2,
1189 x_acct_id IN NUMBER,
1190 x_fee_type IN VARCHAR2,
1191 x_fee_cal_type IN VARCHAR2,
1192 x_fee_ci_sequence_number IN NUMBER,
1193 x_order_sequence IN NUMBER,
1194 x_natural_account_segment IN VARCHAR2,
1195 x_rev_account_cd IN VARCHAR2,
1196 x_location_cd IN VARCHAR2,
1197 x_attendance_type IN VARCHAR2,
1198 x_attendance_mode IN VARCHAR2,
1199 x_course_cd IN VARCHAR2,
1200 x_crs_version_number IN NUMBER,
1201 x_unit_cd IN VARCHAR2,
1202 x_unit_version_number IN NUMBER,
1203 x_org_unit_cd IN VARCHAR2,
1204 x_residency_status_cd IN VARCHAR2,
1205 x_uoo_id IN NUMBER,
1206 x_mode IN VARCHAR2,
1207 x_unit_level IN VARCHAR2,
1208 x_unit_type_id IN NUMBER,
1209 x_unit_mode IN VARCHAR2,
1210 x_unit_class IN VARCHAR2
1211 ) AS
1212 /*
1213 || Created By : [email protected]
1214 || Created On : 15-MAY-2003
1215 || Purpose : Handles the UPDATE DML logic for the table.
1216 || Known limitations, enhancements or remarks :
1217 || Change History :
1218 || Who When What
1219 || gurprsin 02-Jun-2005 Enh 3442712, Added 4 new columns.
1220 || (reverse chronological order - newest change first)
1221 */
1222 x_last_update_date DATE ;
1223 x_last_updated_by NUMBER;
1224 x_last_update_login NUMBER;
1225 x_request_id NUMBER;
1226 x_program_id NUMBER;
1227 x_program_application_id NUMBER;
1228 x_program_update_date DATE;
1229
1230 BEGIN
1231
1232 x_last_update_date := SYSDATE;
1233 IF (X_MODE = 'I') THEN
1234 x_last_updated_by := 1;
1235 x_last_update_login := 0;
1236 ELSIF (x_mode = 'R') THEN
1237 x_last_updated_by := fnd_global.user_id;
1238 IF x_last_updated_by IS NULL THEN
1239 x_last_updated_by := -1;
1240 END IF;
1241 x_last_update_login := fnd_global.login_id;
1242 IF (x_last_update_login IS NULL) THEN
1243 x_last_update_login := -1;
1244 END IF;
1245 ELSE
1246 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1247 fnd_message.set_token ('ROUTINE', 'IGS_FI_FTCI_ACCTS_PKG.UPDATE_ROW');
1248 igs_ge_msg_stack.add;
1249 app_exception.raise_exception;
1250 END IF;
1251
1252 before_dml(
1253 p_action => 'UPDATE',
1254 x_rowid => x_rowid,
1255 x_acct_id => x_acct_id,
1256 x_fee_type => x_fee_type,
1257 x_fee_cal_type => x_fee_cal_type,
1258 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
1259 x_order_sequence => x_order_sequence,
1260 x_natural_account_segment => x_natural_account_segment,
1261 x_rev_account_cd => x_rev_account_cd,
1262 x_location_cd => x_location_cd,
1263 x_attendance_type => x_attendance_type,
1264 x_attendance_mode => x_attendance_mode,
1265 x_course_cd => x_course_cd,
1266 x_crs_version_number => x_crs_version_number,
1267 x_unit_cd => x_unit_cd,
1268 x_unit_version_number => x_unit_version_number,
1269 x_org_unit_cd => x_org_unit_cd,
1270 x_residency_status_cd => x_residency_status_cd,
1271 x_uoo_id => x_uoo_id,
1272 x_creation_date => x_last_update_date,
1273 x_created_by => x_last_updated_by,
1274 x_last_update_date => x_last_update_date,
1275 x_last_updated_by => x_last_updated_by,
1276 x_last_update_login => x_last_update_login,
1277 x_unit_level => x_unit_level,
1278 x_unit_type_id => x_unit_type_id,
1279 x_unit_mode => x_unit_mode,
1280 x_unit_class => x_unit_class
1281 );
1282
1283 IF (x_mode = 'R') THEN
1284 x_request_id := fnd_global.conc_request_id;
1285 x_program_id := fnd_global.conc_program_id;
1286 x_program_application_id := fnd_global.prog_appl_id;
1287 IF (x_request_id = -1) THEN
1288 x_request_id := old_references.request_id;
1289 x_program_id := old_references.program_id;
1290 x_program_application_id := old_references.program_application_id;
1291 x_program_update_date := old_references.program_update_date;
1292 ELSE
1293 x_program_update_date := SYSDATE;
1294 END IF;
1295 END IF;
1296
1297 UPDATE igs_fi_ftci_accts
1298 SET
1299 fee_type = new_references.fee_type,
1300 fee_cal_type = new_references.fee_cal_type,
1301 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
1302 order_sequence = new_references.order_sequence,
1303 natural_account_segment = new_references.natural_account_segment,
1304 rev_account_cd = new_references.rev_account_cd,
1305 location_cd = new_references.location_cd,
1306 attendance_type = new_references.attendance_type,
1307 attendance_mode = new_references.attendance_mode,
1308 course_cd = new_references.course_cd,
1309 crs_version_number = new_references.crs_version_number,
1310 unit_cd = new_references.unit_cd,
1311 unit_version_number = new_references.unit_version_number,
1312 org_unit_cd = new_references.org_unit_cd,
1313 residency_status_cd = new_references.residency_status_cd,
1314 uoo_id = new_references.uoo_id,
1315 last_update_date = x_last_update_date,
1316 last_updated_by = x_last_updated_by,
1317 last_update_login = x_last_update_login ,
1318 request_id = x_request_id,
1319 program_id = x_program_id,
1320 program_application_id = x_program_application_id,
1321 program_update_date = x_program_update_date,
1322 unit_level = new_references.unit_level,
1323 unit_type_id = new_references.unit_type_id,
1324 unit_mode = new_references.unit_mode,
1325 unit_class = new_references.unit_class
1326 WHERE rowid = x_rowid;
1327
1328 IF (SQL%NOTFOUND) THEN
1329 RAISE NO_DATA_FOUND;
1330 END IF;
1331
1332 END update_row;
1333
1334
1335 PROCEDURE add_row (
1336 x_rowid IN OUT NOCOPY VARCHAR2,
1337 x_acct_id IN OUT NOCOPY NUMBER,
1338 x_fee_type IN VARCHAR2,
1339 x_fee_cal_type IN VARCHAR2,
1340 x_fee_ci_sequence_number IN NUMBER,
1341 x_order_sequence IN NUMBER,
1342 x_natural_account_segment IN VARCHAR2,
1343 x_rev_account_cd IN VARCHAR2,
1344 x_location_cd IN VARCHAR2,
1345 x_attendance_type IN VARCHAR2,
1346 x_attendance_mode IN VARCHAR2,
1347 x_course_cd IN VARCHAR2,
1348 x_crs_version_number IN NUMBER,
1349 x_unit_cd IN VARCHAR2,
1350 x_unit_version_number IN NUMBER,
1351 x_org_unit_cd IN VARCHAR2,
1352 x_residency_status_cd IN VARCHAR2,
1353 x_uoo_id IN NUMBER,
1354 x_mode IN VARCHAR2,
1355 x_unit_level IN VARCHAR2,
1356 x_unit_type_id IN NUMBER,
1357 x_unit_mode IN VARCHAR2,
1358 x_unit_class IN VARCHAR2
1359 ) AS
1360 /*
1361 || Created By : [email protected]
1362 || Created On : 15-MAY-2003
1363 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1364 || Known limitations, enhancements or remarks :
1365 || Change History :
1366 || Who When What
1367 || gurprsin 02-Jun-2005 Enh 3442712, Added 4 new columns.
1368 || (reverse chronological order - newest change first)
1369 */
1370 CURSOR c1 IS
1371 SELECT rowid
1372 FROM igs_fi_ftci_accts
1373 WHERE acct_id = x_acct_id;
1374
1375 BEGIN
1376
1377 OPEN c1;
1378 FETCH c1 INTO x_rowid;
1379 IF (c1%NOTFOUND) THEN
1380 CLOSE c1;
1381
1382 insert_row (
1383 x_rowid,
1384 x_acct_id,
1385 x_fee_type,
1386 x_fee_cal_type,
1387 x_fee_ci_sequence_number,
1388 x_order_sequence,
1389 x_natural_account_segment,
1390 x_rev_account_cd,
1391 x_location_cd,
1392 x_attendance_type,
1393 x_attendance_mode,
1394 x_course_cd,
1395 x_crs_version_number,
1396 x_unit_cd,
1397 x_unit_version_number,
1398 x_org_unit_cd,
1399 x_residency_status_cd,
1400 x_uoo_id,
1401 x_mode,
1402 x_unit_level,
1403 x_unit_type_id,
1404 x_unit_mode,
1405 x_unit_class
1406 );
1407 RETURN;
1408 END IF;
1409 CLOSE c1;
1410
1411 update_row (
1412 x_rowid,
1413 x_acct_id,
1414 x_fee_type,
1415 x_fee_cal_type,
1416 x_fee_ci_sequence_number,
1417 x_order_sequence,
1418 x_natural_account_segment,
1419 x_rev_account_cd,
1420 x_location_cd,
1421 x_attendance_type,
1422 x_attendance_mode,
1423 x_course_cd,
1424 x_crs_version_number,
1425 x_unit_cd,
1426 x_unit_version_number,
1427 x_org_unit_cd,
1428 x_residency_status_cd,
1429 x_uoo_id,
1430 x_mode,
1431 x_unit_level,
1432 x_unit_type_id,
1433 x_unit_mode,
1434 x_unit_class
1435 );
1436
1437 END add_row;
1438
1439 PROCEDURE delete_row (
1440 x_rowid IN VARCHAR2
1441 ) AS
1442 /*
1443 || Created By : [email protected]
1444 || Created On : 15-MAY-2003
1445 || Purpose : Handles the DELETE DML logic for the table.
1446 || Known limitations, enhancements or remarks :
1447 || Change History :
1448 || Who When What
1449 || (reverse chronological order - newest change first)
1450 */
1451 BEGIN
1452
1453 before_dml (
1454 p_action => 'DELETE',
1455 x_rowid => x_rowid
1456 );
1457
1458 DELETE FROM igs_fi_ftci_accts
1459 WHERE rowid = x_rowid;
1460
1461 IF (SQL%NOTFOUND) THEN
1462 RAISE NO_DATA_FOUND;
1463 END IF;
1464
1465 END delete_row;
1466
1467
1468 END igs_fi_ftci_accts_pkg;