[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_PP_STD_ATTRS_PKG
Source
1 PACKAGE BODY igs_fi_pp_std_attrs_pkg AS
2 /* $Header: IGSSIE0B.pls 120.1 2005/08/10 03:37:43 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_pp_std_attrs%ROWTYPE;
6 new_references igs_fi_pp_std_attrs%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_student_plan_id IN NUMBER,
12 x_person_id IN NUMBER,
13 x_payment_plan_name IN VARCHAR2,
14 x_plan_start_date IN DATE,
15 x_plan_end_date IN DATE,
16 x_plan_status_code IN VARCHAR2,
17 x_processing_fee_amt IN NUMBER,
18 x_processing_fee_type IN VARCHAR2,
19 x_fee_cal_type IN VARCHAR2,
20 x_fee_ci_sequence_number IN NUMBER,
21 x_notes IN VARCHAR2,
22 x_invoice_id IN NUMBER,
23 x_attribute_category IN VARCHAR2,
24 x_attribute1 IN VARCHAR2,
25 x_attribute2 IN VARCHAR2,
26 x_attribute3 IN VARCHAR2,
27 x_attribute4 IN VARCHAR2,
28 x_attribute5 IN VARCHAR2,
29 x_attribute6 IN VARCHAR2,
30 x_attribute7 IN VARCHAR2,
31 x_attribute8 IN VARCHAR2,
32 x_attribute9 IN VARCHAR2,
33 x_attribute10 IN VARCHAR2,
34 x_attribute11 IN VARCHAR2,
35 x_attribute12 IN VARCHAR2,
36 x_attribute13 IN VARCHAR2,
37 x_attribute14 IN VARCHAR2,
38 x_attribute15 IN VARCHAR2,
39 x_attribute16 IN VARCHAR2,
40 x_attribute17 IN VARCHAR2,
41 x_attribute18 IN VARCHAR2,
42 x_attribute19 IN VARCHAR2,
43 x_attribute20 IN VARCHAR2,
44 x_creation_date IN DATE,
45 x_created_by IN NUMBER,
46 x_last_update_date IN DATE,
47 x_last_updated_by IN NUMBER,
48 x_last_update_login IN NUMBER
49 ) AS
50 /*
51 || Created By : smvk
52 || Created On : 24-AUG-2003
53 || Purpose : Initialises the Old and New references for the columns of the table.
54 || Known limitations, enhancements or remarks :
55 || Change History :
56 || Who When What
57 || (reverse chronological order - newest change first)
58 */
59
60 CURSOR cur_old_ref_values IS
61 SELECT *
62 FROM igs_fi_pp_std_attrs
63 WHERE rowid = x_rowid;
64
65 BEGIN
66
67 l_rowid := x_rowid;
68
69 -- Code for setting the Old and New Reference Values.
70 -- Populate Old Values.
71 OPEN cur_old_ref_values;
72 FETCH cur_old_ref_values INTO old_references;
73 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
74 CLOSE cur_old_ref_values;
75 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
76 igs_ge_msg_stack.add;
77 app_exception.raise_exception;
78 RETURN;
79 END IF;
80 CLOSE cur_old_ref_values;
81
82 -- Populate New Values.
83 new_references.student_plan_id := x_student_plan_id;
84 new_references.person_id := x_person_id;
85 new_references.payment_plan_name := x_payment_plan_name;
86 new_references.plan_start_date := x_plan_start_date;
87 new_references.plan_end_date := x_plan_end_date;
88 new_references.plan_status_code := x_plan_status_code;
89 new_references.processing_fee_amt := x_processing_fee_amt;
90 new_references.processing_fee_type := x_processing_fee_type;
91 new_references.fee_cal_type := x_fee_cal_type;
92 new_references.fee_ci_sequence_number := x_fee_ci_sequence_number;
93 new_references.notes := x_notes;
94 new_references.invoice_id := x_invoice_id;
95 new_references.attribute_category := x_attribute_category;
96 new_references.attribute1 := x_attribute1;
97 new_references.attribute2 := x_attribute2;
98 new_references.attribute3 := x_attribute3;
99 new_references.attribute4 := x_attribute4;
100 new_references.attribute5 := x_attribute5;
101 new_references.attribute6 := x_attribute6;
102 new_references.attribute7 := x_attribute7;
103 new_references.attribute8 := x_attribute8;
104 new_references.attribute9 := x_attribute9;
105 new_references.attribute10 := x_attribute10;
106 new_references.attribute11 := x_attribute11;
107 new_references.attribute12 := x_attribute12;
108 new_references.attribute13 := x_attribute13;
109 new_references.attribute14 := x_attribute14;
110 new_references.attribute15 := x_attribute15;
111 new_references.attribute16 := x_attribute16;
112 new_references.attribute17 := x_attribute17;
113 new_references.attribute18 := x_attribute18;
114 new_references.attribute19 := x_attribute19;
115 new_references.attribute20 := x_attribute20;
116
117 IF (p_action = 'UPDATE') THEN
118 new_references.creation_date := old_references.creation_date;
119 new_references.created_by := old_references.created_by;
120 ELSE
121 new_references.creation_date := x_creation_date;
122 new_references.created_by := x_created_by;
123 END IF;
124
125 new_references.last_update_date := x_last_update_date;
126 new_references.last_updated_by := x_last_updated_by;
127 new_references.last_update_login := x_last_update_login;
128
129 END set_column_values;
130
131
132 PROCEDURE check_uniqueness AS
133 /*
134 || Created By : smvk
135 || Created On : 24-AUG-2003
136 || Purpose : Handles the Unique Constraint logic defined for the columns.
137 || Known limitations, enhancements or remarks :
138 || Change History :
139 || Who When What
140 || (reverse chronological order - newest change first)
141 */
142 BEGIN
143
144 IF ( get_uk_for_validation (
145 new_references.person_id,
146 new_references.payment_plan_name,
147 new_references.plan_start_date
148 )
149 ) THEN
150 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
151 igs_ge_msg_stack.add;
152 app_exception.raise_exception;
153 END IF;
154
155 END check_uniqueness;
156
157
158 PROCEDURE check_parent_existance AS
159 /*
160 || Created By : smvk
161 || Created On : 24-AUG-2003
162 || Purpose : Checks for the existance of Parent records.
163 || Known limitations, enhancements or remarks :
164 || Change History :
165 || Who When What
166 || (reverse chronological order - newest change first)
167 */
168 CURSOR c_personid IS
169 SELECT rowid
170 FROM hz_parties
171 WHERE party_id = new_references.person_id
172 AND status = 'A';
173
174 rec_personid c_personid%ROWTYPE;
175
176 BEGIN
177 IF (((old_references.person_id = new_references.person_id)) OR
178 ((new_references.person_id IS NULL))) THEN
179 NULL;
180 ELSE
181 OPEN c_personid;
182 FETCH c_personid INTO rec_personid;
183 IF (c_personid%FOUND) THEN
184 CLOSE c_personid;
185 ELSE
186 CLOSE c_personid;
187 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
188 igs_ge_msg_stack.add;
189 app_exception.raise_exception;
190 END IF;
191 END IF;
192
193 IF (((old_references.payment_plan_name = new_references.payment_plan_name)) OR
194 ((new_references.payment_plan_name IS NULL))) THEN
195 NULL;
196 ELSIF NOT igs_fi_pp_templates_pkg.get_pk_for_validation (
197 new_references.payment_plan_name
198 ) THEN
199 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
200 igs_ge_msg_stack.add;
201 app_exception.raise_exception;
202 END IF;
203
204 IF (((old_references.fee_cal_type = new_references.fee_cal_type) AND
205 (old_references.fee_ci_sequence_number = new_references.fee_ci_sequence_number)) OR
206 ((new_references.fee_cal_type IS NULL) OR
207 (new_references.fee_ci_sequence_number IS NULL))) THEN
208 NULL;
209 ELSIF NOT igs_ca_inst_PKG.get_pk_for_validation (
210 new_references.fee_cal_type,
211 new_references.fee_ci_sequence_number
212 ) THEN
213 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
214 igs_ge_msg_stack.add;
215 app_exception.raise_exception;
216 END IF;
217
218 IF (((old_references.processing_fee_type = new_references.processing_fee_type)) OR
219 ((new_references.processing_fee_type IS NULL))) THEN
220 NULL;
221 ELSIF NOT igs_fi_fee_type_PKG.get_pk_for_validation (
222 new_references.processing_fee_type
223 ) THEN
224 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
225 igs_ge_msg_stack.add;
226 app_exception.raise_exception;
227 END IF;
228
229 END check_parent_existance;
230
231
232 PROCEDURE check_child_existance AS
233 /*
234 || Created By : smvk
235 || Created On : 24-AUG-2003
236 || Purpose : Checks for the existance of Child records.
237 || Known limitations, enhancements or remarks :
238 || Change History :
239 || Who When What
240 || (reverse chronological order - newest change first)
241 */
242 BEGIN
243
244 igs_fi_pp_instlmnts_pkg.get_fk_igs_fi_pp_std_attrs (
245 old_references.student_plan_id
246 );
247
248 END check_child_existance;
249
250 FUNCTION create_processing_charge( p_n_person_id igs_fi_pp_std_attrs.person_id%TYPE,
251 p_v_fee_type igs_fi_pp_std_attrs.processing_fee_type%TYPE,
252 p_v_fee_cal_type igs_fi_pp_std_attrs.fee_cal_type%TYPE,
253 p_n_fee_ci_sequence_number igs_fi_pp_std_attrs.fee_ci_sequence_number%TYPE,
254 p_n_amount igs_fi_pp_std_attrs.processing_fee_amt%TYPE,
255 p_v_plan_name igs_fi_pp_std_attrs.payment_plan_name%TYPE,
256 p_d_start_date igs_fi_pp_std_attrs.plan_start_date%TYPE ) RETURN NUMBER IS
257 /*
258 || Created By : shtatiko
259 || Created On : 02-SEP-2003
260 || Purpose : To create a charge for Processing Fee Amount.
261 || Known limitations, enhancements or remarks :
262 || Change History :
263 || Who When What
264 || svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
265 || Impact of Charges API version Number change
266 || Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
267 || (reverse chronological order - newest change first)
268 */
269
270 l_chg_rec igs_fi_charges_api_pvt.header_rec_type;
271 l_chg_line_tbl igs_fi_charges_api_pvt.line_tbl_type;
272 l_line_tbl igs_fi_charges_api_pvt.line_id_tbl_type;
273 l_n_invoice_id igs_fi_inv_int.invoice_id%TYPE;
274 l_v_message_name VARCHAR2(30);
275 l_v_curr_desc VARCHAR2(100);
276 l_msg_count NUMBER(5);
277 l_msg_data VARCHAR2(2000);
278 l_msg VARCHAR2(2000);
279 l_v_status VARCHAR2(2);
280
281 l_n_waiver_amount NUMBER;
282 BEGIN
283
284 l_chg_rec.p_person_id := p_n_person_id;
285 l_chg_rec.p_fee_type := p_v_fee_type;
286 l_chg_rec.p_fee_cat := NULL;
287 l_chg_rec.p_fee_cal_type := p_v_fee_cal_type;
288 l_chg_rec.p_fee_ci_sequence_number := p_n_fee_ci_sequence_number;
289 l_chg_rec.p_course_cd := NULL;
290 l_chg_rec.p_attendance_type := NULL;
291 l_chg_rec.p_attendance_mode := NULL;
292 l_chg_rec.p_invoice_amount := p_n_amount;
293 l_chg_rec.p_invoice_creation_date := TRUNC(SYSDATE);
294
295 -- Get Invoice Description from Message
296 fnd_message.set_name( 'IGS', 'IGS_FI_PP_PROCESSING_FEE' );
297 fnd_message.set_token( 'PLAN_NAME', p_v_plan_name );
298 fnd_message.set_token( 'START_DATE', p_d_start_date );
299 l_chg_rec.p_invoice_desc := fnd_message.get;
300
301 l_chg_rec.p_transaction_type := 'PAY_PLAN';
302
303 --Capture the default currency that is set up in System Options Form.
304 igs_fi_gen_gl.finp_get_cur( p_v_currency_cd => l_chg_rec.p_currency_cd,
305 p_v_curr_desc => l_v_curr_desc,
306 p_v_message_name => l_v_message_name
307 );
308 IF l_v_message_name IS NOT NULL THEN
309 fnd_message.set_name('IGS',l_v_message_name);
310 igs_ge_msg_stack.ADD;
311 app_exception.raise_exception;
312 END IF;
313
314 l_chg_rec.p_exchange_rate := 1;
315 l_chg_rec.p_effective_date := TRUNC(SYSDATE);
316 l_chg_rec.p_waiver_flag := NULL;
317 l_chg_rec.p_waiver_reason := NULL;
318 l_chg_rec.p_source_transaction_id := NULL;
319
320 l_chg_line_tbl(1).p_s_chg_method_type := NULL;
321 l_chg_line_tbl(1).p_description := l_chg_rec.p_invoice_desc;
322 l_chg_line_tbl(1).p_chg_elements := 1;
323 l_chg_line_tbl(1).p_amount := p_n_amount;
324 l_chg_line_tbl(1).p_unit_attempt_status := NULL;
325 l_chg_line_tbl(1).p_eftsu := NULL;
326 l_chg_line_tbl(1).p_credit_points := NULL;
327 l_chg_line_tbl(1).p_org_unit_cd := NULL;
328 l_chg_line_tbl(1).p_attribute_category := NULL;
329 l_chg_line_tbl(1).p_attribute1 := NULL;
330 l_chg_line_tbl(1).p_attribute2 := NULL;
331 l_chg_line_tbl(1).p_attribute3 := NULL;
332 l_chg_line_tbl(1).p_attribute4 := NULL;
333 l_chg_line_tbl(1).p_attribute5 := NULL;
334 l_chg_line_tbl(1).p_attribute6 := NULL;
335 l_chg_line_tbl(1).p_attribute7 := NULL;
336 l_chg_line_tbl(1).p_attribute8 := NULL;
337 l_chg_line_tbl(1).p_attribute9 := NULL;
338 l_chg_line_tbl(1).p_attribute10 := NULL;
339 l_chg_line_tbl(1).p_attribute11 := NULL;
340 l_chg_line_tbl(1).p_attribute12 := NULL;
341 l_chg_line_tbl(1).p_attribute13 := NULL;
342 l_chg_line_tbl(1).p_attribute14 := NULL;
343 l_chg_line_tbl(1).p_attribute15 := NULL;
344 l_chg_line_tbl(1).p_attribute16 := NULL;
345 l_chg_line_tbl(1).p_attribute17 := NULL;
346 l_chg_line_tbl(1).p_attribute18 := NULL;
347 l_chg_line_tbl(1).p_attribute19 := NULL;
348 l_chg_line_tbl(1).p_attribute20 := NULL;
349 l_chg_line_tbl(1).p_location_cd := NULL;
350 l_chg_line_tbl(1).p_uoo_id := NULL;
351 l_chg_line_tbl(1).p_d_gl_date := SYSDATE;
352
353 igs_fi_charges_api_pvt.create_charge(p_api_version => 2.0,
354 p_init_msg_list => 'T',
355 p_commit => 'F',
356 p_validation_level => NULL,
357 p_header_rec => l_chg_rec,
358 p_line_tbl => l_chg_line_tbl,
359 x_invoice_id => l_n_invoice_id,
360 x_line_id_tbl => l_line_tbl,
361 x_return_status => l_v_status,
362 x_msg_count => l_msg_count,
363 x_msg_data => l_msg_data,
364 x_waiver_amount => l_n_waiver_amount);
365
366 IF l_v_status <> 'S' THEN
367 IF l_msg_count = 1 THEN
368 fnd_message.set_encoded(l_msg_data);
369 l_msg := fnd_message.get;
370 fnd_message.set_name('IGS', 'IGS_FI_ERR_TXT');
371 fnd_message.set_token('TEXT', l_msg);
372 igs_ge_msg_stack.add;
373 app_exception.raise_exception;
374 ELSE
375 l_msg := '';
376 FOR l_count IN 1 .. l_msg_count LOOP
377 l_msg := l_msg||fnd_msg_pub.get(p_msg_index => l_count, p_encoded => 'T') || ' ';
378 END LOOP;
379 fnd_message.set_name('IGS', 'IGS_FI_ERR_TXT');
380 fnd_message.set_token('TEXT', l_msg);
381 app_exception.raise_exception;
382 END IF;
383 END IF;
384
385 RETURN l_n_invoice_id;
386
387 END create_processing_charge;
388
389 FUNCTION get_pk_for_validation (
390 x_student_plan_id IN NUMBER
391 ) RETURN BOOLEAN AS
392 /*
393 || Created By : smvk
394 || Created On : 24-AUG-2003
395 || Purpose : Validates the Primary Key of the table.
396 || Known limitations, enhancements or remarks :
397 || Change History :
398 || Who When What
399 || (reverse chronological order - newest change first)
400 */
401 CURSOR cur_rowid IS
402 SELECT rowid
403 FROM igs_fi_pp_std_attrs
404 WHERE student_plan_id = x_student_plan_id
405 FOR UPDATE NOWAIT;
406
407 lv_rowid cur_rowid%RowType;
408
409 BEGIN
410
411 OPEN cur_rowid;
412 FETCH cur_rowid INTO lv_rowid;
413 IF (cur_rowid%FOUND) THEN
414 CLOSE cur_rowid;
415 RETURN(TRUE);
416 ELSE
417 CLOSE cur_rowid;
418 RETURN(FALSE);
419 END IF;
420
421 END get_pk_for_validation;
422
423
424 FUNCTION get_uk_for_validation (
425 x_person_id IN NUMBER,
426 x_payment_plan_name IN VARCHAR2,
427 x_plan_start_date IN DATE
428 ) RETURN BOOLEAN AS
429 /*
430 || Created By : smvk
431 || Created On : 24-AUG-2003
432 || Purpose : Validates the Unique Keys of the table.
433 || Known limitations, enhancements or remarks :
434 || Change History :
435 || Who When What
436 || (reverse chronological order - newest change first)
437 */
438 CURSOR cur_rowid IS
439 SELECT rowid
440 FROM igs_fi_pp_std_attrs
441 WHERE person_id = x_person_id
442 AND payment_plan_name = x_payment_plan_name
443 AND plan_start_date = x_plan_start_date
444 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
445
446 lv_rowid cur_rowid%RowType;
447
448 BEGIN
449
450 OPEN cur_rowid;
451 FETCH cur_rowid INTO lv_rowid;
452 IF (cur_rowid%FOUND) THEN
453 CLOSE cur_rowid;
454 RETURN (true);
455 ELSE
456 CLOSE cur_rowid;
457 RETURN(FALSE);
458 END IF;
459
460 END get_uk_for_validation ;
461
462 PROCEDURE get_fk_igs_ca_inst (
463 x_cal_type IN VARCHAR2,
464 x_sequence_number IN NUMBER
465 ) AS
466 /*
467 || Created By : smvk
468 || Created On : 24-AUG-2003
469 || Purpose : Validates the Foreign Keys for the table.
470 || Known limitations, enhancements or remarks :
471 || Change History :
472 || Who When What
473 || (reverse chronological order - newest change first)
474 */
475 CURSOR cur_rowid IS
476 SELECT rowid
477 FROM igs_fi_pp_std_attrs
478 WHERE ((fee_cal_type = x_cal_type) AND
479 (fee_ci_sequence_number = x_sequence_number));
480
481 lv_rowid cur_rowid%RowType;
482
483 BEGIN
484
485 OPEN cur_rowid;
486 FETCH cur_rowid INTO lv_rowid;
487 IF (cur_rowid%FOUND) THEN
488 CLOSE cur_rowid;
489 fnd_message.set_name ('IGS', 'IGS_FI_PPSA_CI_FK');
490 igs_ge_msg_stack.add;
491 app_exception.raise_exception;
492 RETURN;
493 END IF;
494 CLOSE cur_rowid;
495
496 END get_fk_igs_ca_inst;
497
498 PROCEDURE BeforeRowInsertUpdateDelete( p_inserting IN BOOLEAN DEFAULT FALSE,
499 p_updating IN BOOLEAN DEFAULT FALSE,
500 p_deleting IN BOOLEAN DEFAULT FALSE ) AS
501 /*
502 || Created By : shtatiko
503 || Created On : 24-AUG-2003
504 || Purpose : To carryout actions to be done before inserting/updating/deleting
505 || Known limitations, enhancements or remarks :
506 || Change History :
507 || Who When What
508 || (reverse chronological order - newest change first)
509 */
510 l_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE;
511 BEGIN
512
513 IF (p_updating) THEN
514 -- If Plan Status is changed to ACTIVE, then create processing charge.
515 IF old_references.plan_status_code = 'PLANNED'
516 AND new_references.plan_status_code = 'ACTIVE' THEN
517 IF new_references.processing_fee_amt IS NOT NULL THEN
518 IF new_references.fee_cal_type IS NULL THEN
519 fnd_message.set_name('IGS',
520 'IGS_FI_PP_NO_FEE_PERIOD');
521 fnd_message.set_token( 'PLAN_NAME', new_references.payment_plan_name);
522 igs_ge_msg_stack.add;
523 app_exception.raise_exception;
524 END IF;
525
526 l_n_invoice_id := create_processing_charge( p_n_person_id => new_references.person_id,
527 p_v_fee_type => new_references.processing_fee_type,
528 p_v_fee_cal_type => new_references.fee_cal_type,
529 p_n_fee_ci_sequence_number => new_references.fee_ci_sequence_number,
530 p_n_amount => new_references.processing_fee_amt,
531 p_v_plan_name => new_references.payment_plan_name,
532 p_d_start_date => new_references.plan_start_date );
533 new_references.invoice_id := l_n_invoice_id;
534 END IF;
535 END IF;
536 END IF;
537
538 END BeforeRowInsertUpdateDelete;
539
540 PROCEDURE before_dml (
541 p_action IN VARCHAR2,
542 x_rowid IN VARCHAR2,
543 x_student_plan_id IN NUMBER,
544 x_person_id IN NUMBER,
545 x_payment_plan_name IN VARCHAR2,
546 x_plan_start_date IN DATE,
547 x_plan_end_date IN DATE,
548 x_plan_status_code IN VARCHAR2,
549 x_processing_fee_amt IN NUMBER,
550 x_processing_fee_type IN VARCHAR2,
551 x_fee_cal_type IN VARCHAR2,
552 x_fee_ci_sequence_number IN NUMBER,
553 x_notes IN VARCHAR2,
554 x_invoice_id IN NUMBER,
555 x_attribute_category IN VARCHAR2,
556 x_attribute1 IN VARCHAR2,
557 x_attribute2 IN VARCHAR2,
558 x_attribute3 IN VARCHAR2,
559 x_attribute4 IN VARCHAR2,
560 x_attribute5 IN VARCHAR2,
561 x_attribute6 IN VARCHAR2,
562 x_attribute7 IN VARCHAR2,
563 x_attribute8 IN VARCHAR2,
564 x_attribute9 IN VARCHAR2,
565 x_attribute10 IN VARCHAR2,
566 x_attribute11 IN VARCHAR2,
567 x_attribute12 IN VARCHAR2,
568 x_attribute13 IN VARCHAR2,
569 x_attribute14 IN VARCHAR2,
570 x_attribute15 IN VARCHAR2,
571 x_attribute16 IN VARCHAR2,
572 x_attribute17 IN VARCHAR2,
573 x_attribute18 IN VARCHAR2,
574 x_attribute19 IN VARCHAR2,
575 x_attribute20 IN VARCHAR2,
576 x_creation_date IN DATE,
577 x_created_by IN NUMBER,
578 x_last_update_date IN DATE,
579 x_last_updated_by IN NUMBER,
580 x_last_update_login IN NUMBER
581 ) AS
582 /*
583 || Created By : smvk
584 || Created On : 24-AUG-2003
585 || Purpose : Initialises the columns, Checks Constraints, Calls the
586 || Trigger Handlers for the table, before any DML operation.
587 || Known limitations, enhancements or remarks :
588 || Change History :
589 || Who When What
590 || (reverse chronological order - newest change first)
591 */
592 BEGIN
593
594 set_column_values (
595 p_action,
596 x_rowid,
597 x_student_plan_id,
598 x_person_id,
599 x_payment_plan_name,
600 x_plan_start_date,
601 x_plan_end_date,
602 x_plan_status_code,
603 x_processing_fee_amt,
604 x_processing_fee_type,
605 x_fee_cal_type,
606 x_fee_ci_sequence_number,
607 x_notes,
608 x_invoice_id,
609 x_attribute_category,
610 x_attribute1,
611 x_attribute2,
612 x_attribute3,
613 x_attribute4,
614 x_attribute5,
615 x_attribute6,
616 x_attribute7,
617 x_attribute8,
618 x_attribute9,
619 x_attribute10,
620 x_attribute11,
621 x_attribute12,
622 x_attribute13,
623 x_attribute14,
624 x_attribute15,
625 x_attribute16,
626 x_attribute17,
627 x_attribute18,
628 x_attribute19,
629 x_attribute20,
630 x_creation_date,
631 x_created_by,
632 x_last_update_date,
633 x_last_updated_by,
634 x_last_update_login
635 );
636
637 IF (p_action = 'INSERT') THEN
638 -- Call all the procedures related to Before Insert.
639 IF ( get_pk_for_validation(
640 new_references.student_plan_id
641 )
642 ) THEN
643 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
644 igs_ge_msg_stack.add;
645 app_exception.raise_exception;
646 END IF;
647 check_uniqueness;
648 check_parent_existance;
649 ELSIF (p_action = 'UPDATE') THEN
650 -- Call all the procedures related to Before Update.
651 check_uniqueness;
652 check_parent_existance;
653 BeforeRowInsertUpdateDelete(p_updating => TRUE);
654 ELSIF (p_action = 'DELETE') THEN
655 -- Call all the procedures related to Before Delete.
656 check_child_existance;
657 ELSIF (p_action = 'VALIDATE_INSERT') THEN
658 -- Call all the procedures related to Before Insert.
659 IF ( get_pk_for_validation (
660 new_references.student_plan_id
661 )
662 ) THEN
663 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
664 igs_ge_msg_stack.add;
665 app_exception.raise_exception;
666 END IF;
667 check_uniqueness;
668 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
669 check_uniqueness;
670 ELSIF (p_action = 'VALIDATE_DELETE') THEN
671 check_child_existance;
672 END IF;
673
674 END before_dml;
675
676
677 PROCEDURE insert_row (
678 x_rowid IN OUT NOCOPY VARCHAR2,
679 x_student_plan_id IN OUT NOCOPY NUMBER,
680 x_person_id IN NUMBER,
681 x_payment_plan_name IN VARCHAR2,
682 x_plan_start_date IN DATE,
683 x_plan_end_date IN DATE,
684 x_plan_status_code IN VARCHAR2,
685 x_processing_fee_amt IN NUMBER,
686 x_processing_fee_type IN VARCHAR2,
687 x_fee_cal_type IN VARCHAR2,
688 x_fee_ci_sequence_number IN NUMBER,
689 x_notes IN VARCHAR2,
690 x_invoice_id IN NUMBER,
691 x_attribute_category IN VARCHAR2,
692 x_attribute1 IN VARCHAR2,
693 x_attribute2 IN VARCHAR2,
694 x_attribute3 IN VARCHAR2,
695 x_attribute4 IN VARCHAR2,
696 x_attribute5 IN VARCHAR2,
697 x_attribute6 IN VARCHAR2,
698 x_attribute7 IN VARCHAR2,
699 x_attribute8 IN VARCHAR2,
700 x_attribute9 IN VARCHAR2,
701 x_attribute10 IN VARCHAR2,
702 x_attribute11 IN VARCHAR2,
703 x_attribute12 IN VARCHAR2,
704 x_attribute13 IN VARCHAR2,
705 x_attribute14 IN VARCHAR2,
706 x_attribute15 IN VARCHAR2,
707 x_attribute16 IN VARCHAR2,
708 x_attribute17 IN VARCHAR2,
709 x_attribute18 IN VARCHAR2,
710 x_attribute19 IN VARCHAR2,
711 x_attribute20 IN VARCHAR2,
712 x_mode IN VARCHAR2
713 ) AS
714 /*
715 || Created By : smvk
716 || Created On : 24-AUG-2003
717 || Purpose : Handles the INSERT DML logic for the table.
718 || Known limitations, enhancements or remarks :
719 || Change History :
720 || Who When What
721 || (reverse chronological order - newest change first)
722 */
723
724 x_last_update_date DATE;
725 x_last_updated_by NUMBER;
726 x_last_update_login NUMBER;
727 x_request_id NUMBER;
728 x_program_id NUMBER;
729 x_program_application_id NUMBER;
730 x_program_update_date DATE;
731
732 BEGIN
733
734 x_last_update_date := SYSDATE;
735 IF (x_mode = 'I') THEN
736 x_last_updated_by := 1;
737 x_last_update_login := 0;
738 ELSIF (x_mode = 'R') THEN
739 x_last_updated_by := fnd_global.user_id;
740 IF (x_last_updated_by IS NULL) THEN
741 x_last_updated_by := -1;
742 END IF;
743 x_last_update_login := fnd_global.login_id;
744 IF (x_last_update_login IS NULL) THEN
745 x_last_update_login := -1;
746 END IF;
747 x_request_id := fnd_global.conc_request_id;
748 x_program_id := fnd_global.conc_program_id;
749 x_program_application_id := fnd_global.prog_appl_id;
750
751 IF (x_request_id = -1) THEN
752 x_request_id := NULL;
753 x_program_id := NULL;
754 x_program_application_id := NULL;
755 x_program_update_date := NULL;
756 ELSE
757 x_program_update_date := SYSDATE;
758 END IF;
759 ELSE
760 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
761 fnd_message.set_token ('ROUTINE', 'IGS_FI_PP_STD_ATTRS_PKG.INSERT_ROW');
762 igs_ge_msg_stack.add;
763 app_exception.raise_exception;
764 END IF;
765
766 x_student_plan_id := NULL;
767
768 before_dml(
769 p_action => 'INSERT',
770 x_rowid => x_rowid,
771 x_student_plan_id => x_student_plan_id,
772 x_person_id => x_person_id,
773 x_payment_plan_name => x_payment_plan_name,
774 x_plan_start_date => x_plan_start_date,
775 x_plan_end_date => x_plan_end_date,
776 x_plan_status_code => x_plan_status_code,
777 x_processing_fee_amt => x_processing_fee_amt,
778 x_processing_fee_type => x_processing_fee_type,
779 x_fee_cal_type => x_fee_cal_type,
780 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
781 x_notes => x_notes,
782 x_invoice_id => x_invoice_id,
783 x_attribute_category => x_attribute_category,
784 x_attribute1 => x_attribute1,
785 x_attribute2 => x_attribute2,
786 x_attribute3 => x_attribute3,
787 x_attribute4 => x_attribute4,
788 x_attribute5 => x_attribute5,
789 x_attribute6 => x_attribute6,
790 x_attribute7 => x_attribute7,
791 x_attribute8 => x_attribute8,
792 x_attribute9 => x_attribute9,
793 x_attribute10 => x_attribute10,
794 x_attribute11 => x_attribute11,
795 x_attribute12 => x_attribute12,
796 x_attribute13 => x_attribute13,
797 x_attribute14 => x_attribute14,
798 x_attribute15 => x_attribute15,
799 x_attribute16 => x_attribute16,
800 x_attribute17 => x_attribute17,
801 x_attribute18 => x_attribute18,
802 x_attribute19 => x_attribute19,
803 x_attribute20 => x_attribute20,
804 x_creation_date => x_last_update_date,
805 x_created_by => x_last_updated_by,
806 x_last_update_date => x_last_update_date,
807 x_last_updated_by => x_last_updated_by,
808 x_last_update_login => x_last_update_login
809 );
810
811 INSERT INTO igs_fi_pp_std_attrs (
812 student_plan_id,
813 person_id,
814 payment_plan_name,
815 plan_start_date,
816 plan_end_date,
817 plan_status_code,
818 processing_fee_amt,
819 processing_fee_type,
820 fee_cal_type,
821 fee_ci_sequence_number,
822 notes,
823 invoice_id,
824 attribute_category,
825 attribute1,
826 attribute2,
827 attribute3,
828 attribute4,
829 attribute5,
830 attribute6,
831 attribute7,
832 attribute8,
833 attribute9,
834 attribute10,
835 attribute11,
836 attribute12,
837 attribute13,
838 attribute14,
839 attribute15,
840 attribute16,
841 attribute17,
842 attribute18,
843 attribute19,
844 attribute20,
845 creation_date,
846 created_by,
847 last_update_date,
848 last_updated_by,
849 last_update_login,
850 request_id,
851 program_id,
852 program_application_id,
853 program_update_date
854 ) VALUES (
855 igs_fi_pp_std_attrs_s.NEXTVAL,
856 new_references.person_id,
857 new_references.payment_plan_name,
858 new_references.plan_start_date,
859 new_references.plan_end_date,
860 new_references.plan_status_code,
861 new_references.processing_fee_amt,
862 new_references.processing_fee_type,
863 new_references.fee_cal_type,
864 new_references.fee_ci_sequence_number,
865 new_references.notes,
866 new_references.invoice_id,
867 new_references.attribute_category,
868 new_references.attribute1,
869 new_references.attribute2,
870 new_references.attribute3,
871 new_references.attribute4,
872 new_references.attribute5,
873 new_references.attribute6,
874 new_references.attribute7,
875 new_references.attribute8,
876 new_references.attribute9,
877 new_references.attribute10,
878 new_references.attribute11,
879 new_references.attribute12,
880 new_references.attribute13,
881 new_references.attribute14,
882 new_references.attribute15,
883 new_references.attribute16,
884 new_references.attribute17,
885 new_references.attribute18,
886 new_references.attribute19,
887 new_references.attribute20,
888 x_last_update_date,
889 x_last_updated_by,
890 x_last_update_date,
891 x_last_updated_by,
892 x_last_update_login ,
893 x_request_id,
894 x_program_id,
895 x_program_application_id,
896 x_program_update_date
897 ) RETURNING ROWID, student_plan_id INTO x_rowid, x_student_plan_id;
898
899 END insert_row;
900
901
902 PROCEDURE lock_row (
903 x_rowid IN VARCHAR2,
904 x_student_plan_id IN NUMBER,
905 x_person_id IN NUMBER,
906 x_payment_plan_name IN VARCHAR2,
907 x_plan_start_date IN DATE,
908 x_plan_end_date IN DATE,
909 x_plan_status_code IN VARCHAR2,
910 x_processing_fee_amt IN NUMBER,
911 x_processing_fee_type IN VARCHAR2,
912 x_fee_cal_type IN VARCHAR2,
913 x_fee_ci_sequence_number IN NUMBER,
914 x_notes IN VARCHAR2,
915 x_invoice_id IN NUMBER,
916 x_attribute_category IN VARCHAR2,
917 x_attribute1 IN VARCHAR2,
918 x_attribute2 IN VARCHAR2,
919 x_attribute3 IN VARCHAR2,
920 x_attribute4 IN VARCHAR2,
921 x_attribute5 IN VARCHAR2,
922 x_attribute6 IN VARCHAR2,
923 x_attribute7 IN VARCHAR2,
924 x_attribute8 IN VARCHAR2,
925 x_attribute9 IN VARCHAR2,
926 x_attribute10 IN VARCHAR2,
927 x_attribute11 IN VARCHAR2,
928 x_attribute12 IN VARCHAR2,
929 x_attribute13 IN VARCHAR2,
930 x_attribute14 IN VARCHAR2,
931 x_attribute15 IN VARCHAR2,
932 x_attribute16 IN VARCHAR2,
933 x_attribute17 IN VARCHAR2,
934 x_attribute18 IN VARCHAR2,
935 x_attribute19 IN VARCHAR2,
936 x_attribute20 IN VARCHAR2
937 ) AS
938 /*
939 || Created By : smvk
940 || Created On : 24-AUG-2003
941 || Purpose : Handles the LOCK mechanism for the table.
942 || Known limitations, enhancements or remarks :
943 || Change History :
944 || Who When What
945 || (reverse chronological order - newest change first)
946 */
947 CURSOR c1 IS
948 SELECT
949 person_id,
950 payment_plan_name,
951 plan_start_date,
952 plan_end_date,
953 plan_status_code,
954 processing_fee_amt,
955 processing_fee_type,
956 fee_cal_type,
957 fee_ci_sequence_number,
958 notes,
959 invoice_id,
960 attribute_category,
961 attribute1,
962 attribute2,
963 attribute3,
964 attribute4,
965 attribute5,
966 attribute6,
967 attribute7,
968 attribute8,
969 attribute9,
970 attribute10,
971 attribute11,
972 attribute12,
973 attribute13,
974 attribute14,
975 attribute15,
976 attribute16,
977 attribute17,
978 attribute18,
979 attribute19,
980 attribute20
981 FROM igs_fi_pp_std_attrs
982 WHERE rowid = x_rowid
983 FOR UPDATE NOWAIT;
984
985 tlinfo c1%ROWTYPE;
986
987 BEGIN
988
989 OPEN c1;
990 FETCH c1 INTO tlinfo;
991 IF (c1%notfound) THEN
992 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
993 igs_ge_msg_stack.add;
994 CLOSE c1;
995 app_exception.raise_exception;
996 RETURN;
997 END IF;
998 CLOSE c1;
999
1000 IF (
1001 (tlinfo.person_id = x_person_id)
1002 AND (tlinfo.payment_plan_name = x_payment_plan_name)
1003 AND (tlinfo.plan_start_date = x_plan_start_date)
1004 AND (tlinfo.plan_end_date = x_plan_end_date)
1005 AND (tlinfo.plan_status_code = x_plan_status_code)
1006 AND ((tlinfo.processing_fee_amt = x_processing_fee_amt) OR ((tlinfo.processing_fee_amt IS NULL) AND (X_processing_fee_amt IS NULL)))
1007 AND ((tlinfo.processing_fee_type = x_processing_fee_type) OR ((tlinfo.processing_fee_type IS NULL) AND (X_processing_fee_type IS NULL)))
1008 AND ((tlinfo.fee_cal_type = x_fee_cal_type) OR ((tlinfo.fee_cal_type IS NULL) AND (X_fee_cal_type IS NULL)))
1009 AND ((tlinfo.fee_ci_sequence_number = x_fee_ci_sequence_number) OR ((tlinfo.fee_ci_sequence_number IS NULL) AND (X_fee_ci_sequence_number IS NULL)))
1010 AND ((tlinfo.notes = x_notes) OR ((tlinfo.notes IS NULL) AND (X_notes IS NULL)))
1011 AND ((tlinfo.invoice_id = x_invoice_id) OR ((tlinfo.invoice_id IS NULL) AND (X_invoice_id IS NULL)))
1012 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1013 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1014 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1015 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1016 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1017 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1018 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1019 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1020 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1021 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1022 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1023 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1024 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1025 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1026 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1027 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1028 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1029 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1030 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1031 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1032 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1033 ) THEN
1034 NULL;
1035 ELSE
1036 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1037 igs_ge_msg_stack.add;
1038 app_exception.raise_exception;
1039 END IF;
1040
1041 RETURN;
1042
1043 END lock_row;
1044
1045
1046 PROCEDURE update_row (
1047 x_rowid IN VARCHAR2,
1048 x_student_plan_id IN NUMBER,
1049 x_person_id IN NUMBER,
1050 x_payment_plan_name IN VARCHAR2,
1051 x_plan_start_date IN DATE,
1052 x_plan_end_date IN DATE,
1053 x_plan_status_code IN VARCHAR2,
1054 x_processing_fee_amt IN NUMBER,
1055 x_processing_fee_type IN VARCHAR2,
1056 x_fee_cal_type IN VARCHAR2,
1057 x_fee_ci_sequence_number IN NUMBER,
1058 x_notes IN VARCHAR2,
1059 x_invoice_id IN NUMBER,
1060 x_attribute_category IN VARCHAR2,
1061 x_attribute1 IN VARCHAR2,
1062 x_attribute2 IN VARCHAR2,
1063 x_attribute3 IN VARCHAR2,
1064 x_attribute4 IN VARCHAR2,
1065 x_attribute5 IN VARCHAR2,
1066 x_attribute6 IN VARCHAR2,
1067 x_attribute7 IN VARCHAR2,
1068 x_attribute8 IN VARCHAR2,
1069 x_attribute9 IN VARCHAR2,
1070 x_attribute10 IN VARCHAR2,
1071 x_attribute11 IN VARCHAR2,
1072 x_attribute12 IN VARCHAR2,
1073 x_attribute13 IN VARCHAR2,
1074 x_attribute14 IN VARCHAR2,
1075 x_attribute15 IN VARCHAR2,
1076 x_attribute16 IN VARCHAR2,
1077 x_attribute17 IN VARCHAR2,
1078 x_attribute18 IN VARCHAR2,
1079 x_attribute19 IN VARCHAR2,
1080 x_attribute20 IN VARCHAR2,
1081 x_mode IN VARCHAR2
1082 ) AS
1083 /*
1084 || Created By : smvk
1085 || Created On : 24-AUG-2003
1086 || Purpose : Handles the UPDATE DML logic for the table.
1087 || Known limitations, enhancements or remarks :
1088 || Change History :
1089 || Who When What
1090 || (reverse chronological order - newest change first)
1091 */
1092 x_last_update_date DATE ;
1093 x_last_updated_by NUMBER;
1094 x_last_update_login NUMBER;
1095 x_request_id NUMBER;
1096 x_program_id NUMBER;
1097 x_program_application_id NUMBER;
1098 x_program_update_date DATE;
1099
1100 BEGIN
1101
1102 x_last_update_date := SYSDATE;
1103 IF (X_MODE = 'I') THEN
1104 x_last_updated_by := 1;
1105 x_last_update_login := 0;
1106 ELSIF (x_mode = 'R') THEN
1107 x_last_updated_by := fnd_global.user_id;
1108 IF x_last_updated_by IS NULL THEN
1109 x_last_updated_by := -1;
1110 END IF;
1111 x_last_update_login := fnd_global.login_id;
1112 IF (x_last_update_login IS NULL) THEN
1113 x_last_update_login := -1;
1114 END IF;
1115 ELSE
1116 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1117 fnd_message.set_token ('ROUTINE', 'IGS_FI_PP_STD_ATTRS_PKG.UPDATE_ROW');
1118 igs_ge_msg_stack.add;
1119 app_exception.raise_exception;
1120 END IF;
1121
1122 before_dml(
1123 p_action => 'UPDATE',
1124 x_rowid => x_rowid,
1125 x_student_plan_id => x_student_plan_id,
1126 x_person_id => x_person_id,
1127 x_payment_plan_name => x_payment_plan_name,
1128 x_plan_start_date => x_plan_start_date,
1129 x_plan_end_date => x_plan_end_date,
1130 x_plan_status_code => x_plan_status_code,
1131 x_processing_fee_amt => x_processing_fee_amt,
1132 x_processing_fee_type => x_processing_fee_type,
1133 x_fee_cal_type => x_fee_cal_type,
1134 x_fee_ci_sequence_number => x_fee_ci_sequence_number,
1135 x_notes => x_notes,
1136 x_invoice_id => x_invoice_id,
1137 x_attribute_category => x_attribute_category,
1138 x_attribute1 => x_attribute1,
1139 x_attribute2 => x_attribute2,
1140 x_attribute3 => x_attribute3,
1141 x_attribute4 => x_attribute4,
1142 x_attribute5 => x_attribute5,
1143 x_attribute6 => x_attribute6,
1144 x_attribute7 => x_attribute7,
1145 x_attribute8 => x_attribute8,
1146 x_attribute9 => x_attribute9,
1147 x_attribute10 => x_attribute10,
1148 x_attribute11 => x_attribute11,
1149 x_attribute12 => x_attribute12,
1150 x_attribute13 => x_attribute13,
1151 x_attribute14 => x_attribute14,
1152 x_attribute15 => x_attribute15,
1153 x_attribute16 => x_attribute16,
1154 x_attribute17 => x_attribute17,
1155 x_attribute18 => x_attribute18,
1156 x_attribute19 => x_attribute19,
1157 x_attribute20 => x_attribute20,
1158 x_creation_date => x_last_update_date,
1159 x_created_by => x_last_updated_by,
1160 x_last_update_date => x_last_update_date,
1161 x_last_updated_by => x_last_updated_by,
1162 x_last_update_login => x_last_update_login
1163 );
1164
1165 IF (x_mode = 'R') THEN
1166 x_request_id := fnd_global.conc_request_id;
1167 x_program_id := fnd_global.conc_program_id;
1168 x_program_application_id := fnd_global.prog_appl_id;
1169 IF (x_request_id = -1) THEN
1170 x_request_id := old_references.request_id;
1171 x_program_id := old_references.program_id;
1172 x_program_application_id := old_references.program_application_id;
1173 x_program_update_date := old_references.program_update_date;
1174 ELSE
1175 x_program_update_date := SYSDATE;
1176 END IF;
1177 END IF;
1178
1179 UPDATE igs_fi_pp_std_attrs
1180 SET
1181 person_id = new_references.person_id,
1182 payment_plan_name = new_references.payment_plan_name,
1183 plan_start_date = new_references.plan_start_date,
1184 plan_end_date = new_references.plan_end_date,
1185 plan_status_code = new_references.plan_status_code,
1186 processing_fee_amt = new_references.processing_fee_amt,
1187 processing_fee_type = new_references.processing_fee_type,
1188 fee_cal_type = new_references.fee_cal_type,
1189 fee_ci_sequence_number = new_references.fee_ci_sequence_number,
1190 notes = new_references.notes,
1191 invoice_id = new_references.invoice_id,
1192 attribute_category = new_references.attribute_category,
1193 attribute1 = new_references.attribute1,
1194 attribute2 = new_references.attribute2,
1195 attribute3 = new_references.attribute3,
1196 attribute4 = new_references.attribute4,
1197 attribute5 = new_references.attribute5,
1198 attribute6 = new_references.attribute6,
1199 attribute7 = new_references.attribute7,
1200 attribute8 = new_references.attribute8,
1201 attribute9 = new_references.attribute9,
1202 attribute10 = new_references.attribute10,
1203 attribute11 = new_references.attribute11,
1204 attribute12 = new_references.attribute12,
1205 attribute13 = new_references.attribute13,
1206 attribute14 = new_references.attribute14,
1207 attribute15 = new_references.attribute15,
1208 attribute16 = new_references.attribute16,
1209 attribute17 = new_references.attribute17,
1210 attribute18 = new_references.attribute18,
1211 attribute19 = new_references.attribute19,
1212 attribute20 = new_references.attribute20,
1213 last_update_date = x_last_update_date,
1214 last_updated_by = x_last_updated_by,
1215 last_update_login = x_last_update_login ,
1216 request_id = x_request_id,
1217 program_id = x_program_id,
1218 program_application_id = x_program_application_id,
1219 program_update_date = x_program_update_date
1220 WHERE rowid = x_rowid;
1221
1222 IF (SQL%NOTFOUND) THEN
1223 RAISE NO_DATA_FOUND;
1224 END IF;
1225
1226 END update_row;
1227
1228
1229 PROCEDURE add_row (
1230 x_rowid IN OUT NOCOPY VARCHAR2,
1231 x_student_plan_id IN OUT NOCOPY NUMBER,
1232 x_person_id IN NUMBER,
1233 x_payment_plan_name IN VARCHAR2,
1234 x_plan_start_date IN DATE,
1235 x_plan_end_date IN DATE,
1236 x_plan_status_code IN VARCHAR2,
1237 x_processing_fee_amt IN NUMBER,
1238 x_processing_fee_type IN VARCHAR2,
1239 x_fee_cal_type IN VARCHAR2,
1240 x_fee_ci_sequence_number IN NUMBER,
1241 x_notes IN VARCHAR2,
1242 x_invoice_id IN NUMBER,
1243 x_attribute_category IN VARCHAR2,
1244 x_attribute1 IN VARCHAR2,
1245 x_attribute2 IN VARCHAR2,
1246 x_attribute3 IN VARCHAR2,
1247 x_attribute4 IN VARCHAR2,
1248 x_attribute5 IN VARCHAR2,
1249 x_attribute6 IN VARCHAR2,
1250 x_attribute7 IN VARCHAR2,
1251 x_attribute8 IN VARCHAR2,
1252 x_attribute9 IN VARCHAR2,
1253 x_attribute10 IN VARCHAR2,
1254 x_attribute11 IN VARCHAR2,
1255 x_attribute12 IN VARCHAR2,
1256 x_attribute13 IN VARCHAR2,
1257 x_attribute14 IN VARCHAR2,
1258 x_attribute15 IN VARCHAR2,
1259 x_attribute16 IN VARCHAR2,
1260 x_attribute17 IN VARCHAR2,
1261 x_attribute18 IN VARCHAR2,
1262 x_attribute19 IN VARCHAR2,
1263 x_attribute20 IN VARCHAR2,
1264 x_mode IN VARCHAR2
1265 ) AS
1266 /*
1267 || Created By : smvk
1268 || Created On : 24-AUG-2003
1269 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1270 || Known limitations, enhancements or remarks :
1271 || Change History :
1272 || Who When What
1273 || (reverse chronological order - newest change first)
1274 */
1275 CURSOR c1 IS
1276 SELECT rowid
1277 FROM igs_fi_pp_std_attrs
1278 WHERE student_plan_id = x_student_plan_id;
1279
1280 BEGIN
1281
1282 OPEN c1;
1283 FETCH c1 INTO x_rowid;
1284 IF (c1%NOTFOUND) THEN
1285 CLOSE c1;
1286
1287 insert_row (
1288 x_rowid,
1289 x_student_plan_id,
1290 x_person_id,
1291 x_payment_plan_name,
1292 x_plan_start_date,
1293 x_plan_end_date,
1294 x_plan_status_code,
1295 x_processing_fee_amt,
1296 x_processing_fee_type,
1297 x_fee_cal_type,
1298 x_fee_ci_sequence_number,
1299 x_notes,
1300 x_invoice_id,
1301 x_attribute_category,
1302 x_attribute1,
1303 x_attribute2,
1304 x_attribute3,
1305 x_attribute4,
1306 x_attribute5,
1307 x_attribute6,
1308 x_attribute7,
1309 x_attribute8,
1310 x_attribute9,
1311 x_attribute10,
1312 x_attribute11,
1313 x_attribute12,
1314 x_attribute13,
1315 x_attribute14,
1316 x_attribute15,
1317 x_attribute16,
1318 x_attribute17,
1319 x_attribute18,
1320 x_attribute19,
1321 x_attribute20,
1322 x_mode
1323 );
1324 RETURN;
1325 END IF;
1326 CLOSE c1;
1327
1328 update_row (
1329 x_rowid,
1330 x_student_plan_id,
1331 x_person_id,
1332 x_payment_plan_name,
1333 x_plan_start_date,
1334 x_plan_end_date,
1335 x_plan_status_code,
1336 x_processing_fee_amt,
1337 x_processing_fee_type,
1338 x_fee_cal_type,
1339 x_fee_ci_sequence_number,
1340 x_notes,
1341 x_invoice_id,
1342 x_attribute_category,
1343 x_attribute1,
1344 x_attribute2,
1345 x_attribute3,
1346 x_attribute4,
1347 x_attribute5,
1348 x_attribute6,
1349 x_attribute7,
1350 x_attribute8,
1351 x_attribute9,
1352 x_attribute10,
1353 x_attribute11,
1354 x_attribute12,
1355 x_attribute13,
1356 x_attribute14,
1357 x_attribute15,
1358 x_attribute16,
1359 x_attribute17,
1360 x_attribute18,
1361 x_attribute19,
1362 x_attribute20,
1363 x_mode
1364 );
1365
1366 END add_row;
1367
1368
1369 PROCEDURE delete_row (
1370 x_rowid IN VARCHAR2
1371 ) AS
1372 /*
1373 || Created By : smvk
1374 || Created On : 24-AUG-2003
1375 || Purpose : Handles the DELETE DML logic for the table.
1376 || Known limitations, enhancements or remarks :
1377 || Change History :
1378 || Who When What
1379 || (reverse chronological order - newest change first)
1380 */
1381 BEGIN
1382
1383 before_dml (
1384 p_action => 'DELETE',
1385 x_rowid => x_rowid
1386 );
1387
1388 DELETE FROM igs_fi_pp_std_attrs
1389 WHERE rowid = x_rowid;
1390
1391 IF (SQL%NOTFOUND) THEN
1392 RAISE NO_DATA_FOUND;
1393 END IF;
1394
1395 END delete_row;
1396
1397
1398 END igs_fi_pp_std_attrs_pkg;