1 PACKAGE BODY igs_fi_fin_lt_plan_pkg AS
2 /* $Header: IGSSIB5B.pls 115.16 2003/09/16 10:22:14 vvutukur ship $ */
3 /* Change History
4 Who When What
5 vvutukur 07-Sep-2003 Enh#3045007.Payment Plans Build.Addition of 7 columns related to
6 payment plans.
7 pathipat 11-Feb-2003 Enh 2747325 - Locking Issues build
8 Removed proc get_fk_igs_fi_fee_type()
9 jbegum 4-dec-2001 As part of enh bug # 2124001
10 Added a local procedure check_charge_existence for the validations in form IGSFI067.fmb
11 Also added the call to this procedure from before_dml
12 jbegum 11-dec-2001 Added the check_constraints procedure and call to it in Before_dml procedure .
13 */
14
15
16 l_rowid VARCHAR2(25);
17 old_references igs_fi_fin_lt_plan%ROWTYPE;
18 new_references igs_fi_fin_lt_plan%ROWTYPE;
19
20 PROCEDURE set_column_values (
21 p_action IN VARCHAR2,
22 x_rowid IN VARCHAR2 ,
23 x_plan_name IN VARCHAR2 ,
24 x_plan_type IN VARCHAR2 ,
25 x_description IN VARCHAR2 ,
26 x_closed_ind IN VARCHAR2 ,
27 x_balance_type IN VARCHAR2 ,
28 x_fee_type IN VARCHAR2 ,
29 x_accrual_type IN VARCHAR2 ,
30 x_offset_days IN NUMBER ,
31 x_chg_rate IN NUMBER ,
32 x_flat_amount IN NUMBER ,
33 x_max_charge_amount IN NUMBER ,
34 x_min_charge_amount IN NUMBER ,
35 x_min_charge_amount_no_charge IN NUMBER ,
36 x_min_balance_amount IN NUMBER ,
37 x_attribute_category IN VARCHAR2 ,
38 x_attribute1 IN VARCHAR2 ,
39 x_attribute2 IN VARCHAR2 ,
40 x_attribute3 IN VARCHAR2 ,
41 x_attribute4 IN VARCHAR2 ,
42 x_attribute5 IN VARCHAR2 ,
43 x_attribute6 IN VARCHAR2 ,
44 x_attribute7 IN VARCHAR2 ,
45 x_attribute8 IN VARCHAR2 ,
46 x_attribute9 IN VARCHAR2 ,
47 x_attribute10 IN VARCHAR2 ,
48 x_attribute11 IN VARCHAR2 ,
49 x_attribute12 IN VARCHAR2 ,
50 x_attribute13 IN VARCHAR2 ,
51 x_attribute14 IN VARCHAR2 ,
52 x_attribute15 IN VARCHAR2 ,
53 x_attribute16 IN VARCHAR2 ,
54 x_attribute17 IN VARCHAR2 ,
55 x_attribute18 IN VARCHAR2 ,
56 x_attribute19 IN VARCHAR2 ,
57 x_attribute20 IN VARCHAR2 ,
58 x_creation_date IN DATE ,
59 x_created_by IN NUMBER ,
60 x_last_update_date IN DATE ,
61 x_last_updated_by IN NUMBER ,
62 x_last_update_login IN NUMBER ,
63 x_payment_plan_accrl_type_code IN VARCHAR2 ,
64 x_payment_plan_chg_rate IN NUMBER ,
65 x_payment_plan_flat_amt IN NUMBER ,
66 x_payment_plan_max_charge_amt IN NUMBER ,
67 x_payment_plan_min_charge_amt IN NUMBER ,
68 x_payment_plan_minchgamt_nochg IN NUMBER ,
69 x_payment_plan_min_balance_amt IN NUMBER) AS
70 /*
71 || Created By : [email protected]
72 || Created On : 28-NOV-2001
73 || Purpose : Initialises the Old and New references for the columns of the table.
74 || Known limitations, enhancements or remarks :
75 || Change History :
76 || Who When What
77 || (reverse chronological order - newest change first)
78 || vvutukur 07-Sep-2003 Enh#3045007.Payment Plans Build.Addition of 7 columns related to
79 || payment plans.
80 */
81
82 CURSOR cur_old_ref_values IS
83 SELECT *
84 FROM IGS_FI_FIN_LT_PLAN
85 WHERE rowid = x_rowid;
86
87 BEGIN
88
89 l_rowid := x_rowid;
90
91 -- Code for setting the Old and New Reference Values.
92 -- Populate Old Values.
93 OPEN cur_old_ref_values;
94 FETCH cur_old_ref_values INTO old_references;
95 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
96 CLOSE cur_old_ref_values;
97 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
98 igs_ge_msg_stack.add;
99 app_exception.raise_exception;
100 RETURN;
101 END IF;
102 CLOSE cur_old_ref_values;
103
104 -- Populate New Values.
105 new_references.plan_name := x_plan_name;
106 new_references.plan_type := x_plan_type;
107 new_references.description := x_description;
108 new_references.closed_ind := x_closed_ind;
109 new_references.balance_type := x_balance_type;
110 new_references.fee_type := x_fee_type;
111 new_references.accrual_type := x_accrual_type;
112 new_references.offset_days := x_offset_days;
113 new_references.chg_rate := x_chg_rate;
114 new_references.flat_amount := x_flat_amount;
115 new_references.max_charge_amount := x_max_charge_amount;
116 new_references.min_charge_amount := x_min_charge_amount;
117 new_references.min_charge_amount_no_charge := x_min_charge_amount_no_charge;
118 new_references.min_balance_amount := x_min_balance_amount;
119 new_references.attribute_category := x_attribute_category;
120 new_references.attribute1 := x_attribute1;
121 new_references.attribute2 := x_attribute2;
122 new_references.attribute3 := x_attribute3;
123 new_references.attribute4 := x_attribute4;
124 new_references.attribute5 := x_attribute5;
125 new_references.attribute6 := x_attribute6;
126 new_references.attribute7 := x_attribute7;
127 new_references.attribute8 := x_attribute8;
128 new_references.attribute9 := x_attribute9;
129 new_references.attribute10 := x_attribute10;
130 new_references.attribute11 := x_attribute11;
131 new_references.attribute12 := x_attribute12;
132 new_references.attribute13 := x_attribute13;
133 new_references.attribute14 := x_attribute14;
134 new_references.attribute15 := x_attribute15;
135 new_references.attribute16 := x_attribute16;
136 new_references.attribute17 := x_attribute17;
137 new_references.attribute18 := x_attribute18;
138 new_references.attribute19 := x_attribute19;
139 new_references.attribute20 := x_attribute20;
140 new_references.payment_plan_accrl_type_code := x_payment_plan_accrl_type_code;
141 new_references.payment_plan_chg_rate := x_payment_plan_chg_rate;
142 new_references.payment_plan_flat_amt := x_payment_plan_flat_amt;
143 new_references.payment_plan_max_charge_amt := x_payment_plan_max_charge_amt;
144 new_references.payment_plan_min_charge_amt := x_payment_plan_min_charge_amt;
145 new_references.payment_plan_minchgamt_nochg := x_payment_plan_minchgamt_nochg;
146 new_references.payment_plan_min_balance_amt := x_payment_plan_min_balance_amt;
147
148 IF (p_action = 'UPDATE') THEN
149 new_references.creation_date := old_references.creation_date;
150 new_references.created_by := old_references.created_by;
151 ELSE
152 new_references.creation_date := x_creation_date;
153 new_references.created_by := x_created_by;
154 END IF;
155
156 new_references.last_update_date := x_last_update_date;
157 new_references.last_updated_by := x_last_updated_by;
158 new_references.last_update_login := x_last_update_login;
159
160 END set_column_values;
161
162 PROCEDURE check_constraints (
163 column_name IN VARCHAR2 ,
164 column_value IN VARCHAR2 ) AS
165 /*************************************************************
166 Created By : jbegum
167 Date Created By : 11-dec_01
168 Purpose : To check whether the plan_name has been entered in upper case and closed_ind value in Y,N
169 Know limitations, enhancements or remarks
170 Change History
171 Who When What
172
173 (reverse chronological order - newest change first)
174 ***************************************************************/
175
176 BEGIN
177
178 IF column_name IS NULL THEN
179 NULL;
180 ELSIF UPPER(column_name) = 'CLOSED_IND' THEN
181 new_references.closed_ind := column_value;
182 END IF;
183
184 IF UPPER(column_name) = 'CLOSED_IND' OR
185 column_name IS NULL THEN
186 IF NOT (new_references.closed_ind IN ('Y', 'N')) THEN
187 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END IF;
191 END IF;
192
193 END check_constraints;
194
195 PROCEDURE check_parent_existance AS
196 /*
197 || Created By : [email protected]
198 || Created On : 28-NOV-2001
199 || Purpose : Checks for the existance of Parent records.
200 || Known limitations, enhancements or remarks :
201 || Change History :
202 || Who When What
203 || (reverse chronological order - newest change first)
204 || vvutukur 16-Sep-2003 Enh#3045007.Payment Plans Build.Added call to
205 || igs_lookups_view_pkg.get_pk_for_validation for the newly
206 || added column payment_plan_accrl_type_code.
207 */
208 BEGIN
209
210 IF (((old_references.fee_type = new_references.fee_type)) OR
211 ((new_references.fee_type IS NULL))) THEN
212 NULL;
213 ELSIF NOT igs_fi_fee_type_pkg.get_pk_for_validation (
214 new_references.fee_type
215 ) THEN
216 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
217 igs_ge_msg_stack.add;
218 app_exception.raise_exception;
219 END IF;
220
221 IF (((old_references.plan_type = new_references.plan_type)) OR
222 ((new_references.plan_type IS NULL))) THEN
223 NULL;
224 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation (
225 'IGS_FI_PLAN_TYPE',
226 new_references.plan_type
227 ) THEN
228 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
229 igs_ge_msg_stack.add;
230 app_exception.raise_exception;
231 END IF;
232
233 IF (((old_references.accrual_type = new_references.accrual_type)) OR
234 ((new_references.accrual_type IS NULL))) THEN
235 NULL;
236 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation (
237 'IGS_FI_ACCRUAL_TYPE',
238 new_references.accrual_type
239 ) THEN
240 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
241 igs_ge_msg_stack.add;
242 app_exception.raise_exception;
243 END IF;
244
245 IF (((old_references.balance_type = new_references.balance_type)) OR
246 ((new_references.balance_type IS NULL))) THEN
247 NULL;
248 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation (
249 'IGS_FI_BALANCE_TYPE',
250 new_references.balance_type
251 ) THEN
252 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
253 igs_ge_msg_stack.add;
254 app_exception.raise_exception;
255 END IF;
256
257 IF (((old_references.payment_plan_accrl_type_code = new_references.payment_plan_accrl_type_code)) OR
258 ((new_references.payment_plan_accrl_type_code IS NULL))) THEN
259 NULL;
260 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation (
261 'IGS_FI_ACCRUAL_TYPE',
262 new_references.payment_plan_accrl_type_code
263 ) THEN
264 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
265 igs_ge_msg_stack.add;
266 app_exception.raise_exception;
267 END IF;
268
269 END check_parent_existance;
270
271 PROCEDURE check_charge_existance (
272 p_action IN VARCHAR2,
273 x_fee_type IN VARCHAR2
274 ) AS
275 /*
276 || Created By : jbegum
277 || Created On : 04-dec-2001
278 || Purpose : Checks for the existance of Charge records for the given fee type in the Charges table.
279 || Known limitations, enhancements or remarks :
280 || Change History :
281 || Who When What
282 || (reverse chronological order - newest change first)
283 || vvutukur 07-Sep-2003 Enh#3045007.Payment Plans Build.Added check for the payment plan attributes
284 || not to get updated if a charge is created using the fee type on which the plan is based.
285 */
286 CURSOR cur_chg IS
287 SELECT rowid
288 FROM igs_fi_inv_int
289 WHERE fee_type = x_fee_type
290 AND transaction_type IN ('INTEREST','LATE')
291 FOR UPDATE NOWAIT;
292
293 lv_chg cur_chg%RowType;
294
295 BEGIN
296 IF (
297 p_action = 'VALIDATE_UPDATE' AND
298 (new_references.plan_type = old_references.plan_type) AND
299 ((new_references.description = old_references.description) OR ((old_references.description IS NULL) AND (new_references.description IS NULL))) AND
300 (new_references.balance_type = old_references.balance_type) AND
301 (new_references.fee_type = old_references.fee_type) AND
302 (new_references.accrual_type = old_references.accrual_type) AND
303 ((new_references.offset_days = old_references.offset_days) OR ((old_references.offset_days IS NULL) AND (new_references.offset_days IS NULL))) AND
304 ((new_references.chg_rate = old_references.chg_rate) OR ((old_references.chg_rate IS NULL) AND (new_references.chg_rate IS NULL))) AND
305 ((new_references.flat_amount = old_references.flat_amount) OR ((old_references.flat_amount IS NULL) AND (new_references.flat_amount IS NULL))) AND
306 ((new_references.min_balance_amount = old_references.min_balance_amount) OR ((old_references.min_balance_amount IS NULL) AND (new_references.min_balance_amount IS NULL))) AND
307 ((new_references.min_charge_amount = old_references.min_charge_amount) OR ((old_references.min_charge_amount IS NULL) AND (new_references.min_charge_amount IS NULL))) AND
308 ((new_references.max_charge_amount = old_references.max_charge_amount) OR ((old_references.max_charge_amount IS NULL) AND (new_references.max_charge_amount IS NULL))) AND
309 ((new_references.min_charge_amount_no_charge = old_references.min_charge_amount_no_charge) OR ((old_references.min_charge_amount_no_charge IS NULL) AND (new_references.min_charge_amount_no_charge IS NULL))) AND
310 ((new_references.payment_plan_accrl_type_code = old_references.payment_plan_accrl_type_code) OR ((old_references.payment_plan_accrl_type_code IS NULL) AND (new_references.payment_plan_accrl_type_code IS NULL))) AND
311 ((new_references.payment_plan_chg_rate = old_references.payment_plan_chg_rate) OR ((old_references.payment_plan_chg_rate IS NULL) AND (new_references.payment_plan_chg_rate IS NULL))) AND
312 ((new_references.payment_plan_flat_amt = old_references.payment_plan_flat_amt) OR ((old_references.payment_plan_flat_amt IS NULL) AND (new_references.payment_plan_flat_amt IS NULL))) AND
313 ((new_references.payment_plan_min_balance_amt = old_references.payment_plan_min_balance_amt) OR ((old_references.payment_plan_min_balance_amt IS NULL) AND (new_references.payment_plan_min_balance_amt IS NULL))) AND
314 ((new_references.payment_plan_min_charge_amt = old_references.payment_plan_min_charge_amt) OR ((old_references.payment_plan_min_charge_amt IS NULL) AND (new_references.payment_plan_min_charge_amt IS NULL))) AND
315 ((new_references.payment_plan_max_charge_amt = old_references.payment_plan_max_charge_amt) OR ((old_references.payment_plan_max_charge_amt IS NULL) AND (new_references.payment_plan_max_charge_amt IS NULL))) AND
316 ((new_references.payment_plan_minchgamt_nochg = old_references.payment_plan_minchgamt_nochg) OR ((old_references.payment_plan_minchgamt_nochg IS NULL) AND (new_references.payment_plan_minchgamt_nochg IS NULL)))
317 ) THEN
318 NULL;
319 ELSE
320 OPEN cur_chg;
321 FETCH cur_chg INTO lv_chg;
322 IF (cur_chg%FOUND) THEN
323 CLOSE cur_chg;
324 fnd_message.set_name ('IGS', 'IGS_FI_MOD_PLAN');
325 igs_ge_msg_stack.add;
326 app_exception.raise_exception;
327 RETURN;
328 END IF;
329 CLOSE cur_chg;
330 END IF;
331
332 END check_charge_existance;
333
334 FUNCTION get_pk_for_validation (
335 x_plan_name IN VARCHAR2
336 ) RETURN BOOLEAN AS
337 /*
338 || Created By : [email protected]
339 || Created On : 28-NOV-2001
340 || Purpose : Validates the Primary Key of the table.
341 || Known limitations, enhancements or remarks :
342 || Change History :
343 || Who When What
344 || (reverse chronological order - newest change first)
345 */
346 CURSOR cur_rowid IS
347 SELECT rowid
348 FROM igs_fi_fin_lt_plan
349 WHERE plan_name = x_plan_name
350 FOR UPDATE NOWAIT;
351
352 lv_rowid cur_rowid%RowType;
353
354 BEGIN
355
356 OPEN cur_rowid;
357 FETCH cur_rowid INTO lv_rowid;
358 IF (cur_rowid%FOUND) THEN
359 CLOSE cur_rowid;
360 RETURN(TRUE);
361 ELSE
362 CLOSE cur_rowid;
363 RETURN(FALSE);
364 END IF;
365
366 END get_pk_for_validation;
367
368
369 PROCEDURE before_dml (
370 p_action IN VARCHAR2,
371 x_rowid IN VARCHAR2 ,
372 x_plan_name IN VARCHAR2 ,
373 x_plan_type IN VARCHAR2 ,
374 x_description IN VARCHAR2 ,
375 x_closed_ind IN VARCHAR2 ,
379 x_offset_days IN NUMBER ,
376 x_balance_type IN VARCHAR2 ,
377 x_fee_type IN VARCHAR2 ,
378 x_accrual_type IN VARCHAR2 ,
380 x_chg_rate IN NUMBER ,
381 x_flat_amount IN NUMBER ,
382 x_max_charge_amount IN NUMBER ,
383 x_min_charge_amount IN NUMBER ,
384 x_min_charge_amount_no_charge IN NUMBER ,
385 x_min_balance_amount IN NUMBER ,
386 x_attribute_category IN VARCHAR2 ,
387 x_attribute1 IN VARCHAR2 ,
388 x_attribute2 IN VARCHAR2 ,
389 x_attribute3 IN VARCHAR2 ,
390 x_attribute4 IN VARCHAR2 ,
391 x_attribute5 IN VARCHAR2 ,
392 x_attribute6 IN VARCHAR2 ,
393 x_attribute7 IN VARCHAR2 ,
394 x_attribute8 IN VARCHAR2 ,
395 x_attribute9 IN VARCHAR2 ,
396 x_attribute10 IN VARCHAR2 ,
397 x_attribute11 IN VARCHAR2 ,
398 x_attribute12 IN VARCHAR2 ,
399 x_attribute13 IN VARCHAR2 ,
400 x_attribute14 IN VARCHAR2 ,
401 x_attribute15 IN VARCHAR2 ,
402 x_attribute16 IN VARCHAR2 ,
403 x_attribute17 IN VARCHAR2 ,
404 x_attribute18 IN VARCHAR2 ,
405 x_attribute19 IN VARCHAR2 ,
406 x_attribute20 IN VARCHAR2 ,
407 x_creation_date IN DATE ,
408 x_created_by IN NUMBER ,
409 x_last_update_date IN DATE ,
410 x_last_updated_by IN NUMBER ,
411 x_last_update_login IN NUMBER ,
412 x_payment_plan_accrl_type_code IN VARCHAR2 ,
413 x_payment_plan_chg_rate IN NUMBER ,
414 x_payment_plan_flat_amt IN NUMBER ,
415 x_payment_plan_max_charge_amt IN NUMBER ,
416 x_payment_plan_min_charge_amt IN NUMBER ,
417 x_payment_plan_minchgamt_nochg IN NUMBER ,
418 x_payment_plan_min_balance_amt IN NUMBER ) AS
419 /*
420 || Created By : [email protected]
421 || Created On : 28-NOV-2001
422 || Purpose : Initialises the columns, Checks Constraints, Calls the
423 || Trigger Handlers for the table, before any DML operation.
424 || Known limitations, enhancements or remarks :
425 || Change History :
426 || Who When What
427 || (reverse chronological order - newest change first)
428 || vvutukur 07-Sep-2003 Enh#3045007.Payment Plans Build.Addition of 7 columns related to
429 || payment plans.
430 */
431 BEGIN
432
433 set_column_values (
434 p_action,
435 x_rowid,
436 x_plan_name,
437 x_plan_type,
438 x_description,
439 x_closed_ind,
440 x_balance_type,
441 x_fee_type,
442 x_accrual_type,
443 x_offset_days,
444 x_chg_rate,
445 x_flat_amount,
446 x_max_charge_amount,
447 x_min_charge_amount,
448 x_min_charge_amount_no_charge,
449 x_min_balance_amount,
450 x_attribute_category,
451 x_attribute1,
452 x_attribute2,
453 x_attribute3,
454 x_attribute4,
455 x_attribute5,
456 x_attribute6,
457 x_attribute7,
458 x_attribute8,
459 x_attribute9,
460 x_attribute10,
461 x_attribute11,
462 x_attribute12,
463 x_attribute13,
464 x_attribute14,
465 x_attribute15,
466 x_attribute16,
467 x_attribute17,
468 x_attribute18,
469 x_attribute19,
470 x_attribute20,
471 x_creation_date,
472 x_created_by,
473 x_last_update_date,
474 x_last_updated_by,
475 x_last_update_login,
476 x_payment_plan_accrl_type_code,
477 x_payment_plan_chg_rate,
478 x_payment_plan_flat_amt,
479 x_payment_plan_max_charge_amt,
480 x_payment_plan_min_charge_amt,
481 x_payment_plan_minchgamt_nochg,
482 x_payment_plan_min_balance_amt
483 );
484
485 IF (p_action = 'INSERT') THEN
486 -- Call all the procedures related to Before Insert.
487 IF ( get_pk_for_validation(
488 new_references.plan_name
489 )
490 ) THEN
491 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
492 igs_ge_msg_stack.add;
493 app_exception.raise_exception;
494 END IF;
495 check_constraints;
496 check_parent_existance;
497 ELSIF (p_action = 'UPDATE') THEN
498 -- Call all the procedures related to Before Update.
499 check_constraints;
500 check_parent_existance;
501 ELSIF (p_action = 'VALIDATE_INSERT') THEN
502 -- Call all the procedures related to Before Insert.
506 ) THEN
503 IF ( get_pk_for_validation (
504 new_references.plan_name
505 )
507 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
508 igs_ge_msg_stack.add;
509 app_exception.raise_exception;
510 END IF;
511 check_constraints;
512 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
513 check_constraints;
514 check_charge_existance(p_action,
515 old_references.fee_type);
516 ELSIF (p_action = 'VALIDATE_DELETE') THEN
517 check_charge_existance(p_action,
518 old_references.fee_type);
519 END IF;
520
521 END before_dml;
522
523
524 PROCEDURE insert_row (
525 x_rowid IN OUT NOCOPY VARCHAR2,
526 x_plan_name IN VARCHAR2,
527 x_plan_type IN VARCHAR2,
528 x_description IN VARCHAR2,
529 x_closed_ind IN VARCHAR2,
530 x_balance_type IN VARCHAR2,
531 x_fee_type IN VARCHAR2,
532 x_accrual_type IN VARCHAR2,
533 x_offset_days IN NUMBER,
534 x_chg_rate IN NUMBER,
535 x_flat_amount IN NUMBER,
536 x_max_charge_amount IN NUMBER,
537 x_min_charge_amount IN NUMBER,
538 x_min_charge_amount_no_charge IN NUMBER,
539 x_min_balance_amount IN NUMBER,
540 x_attribute_category IN VARCHAR2,
541 x_attribute1 IN VARCHAR2,
542 x_attribute2 IN VARCHAR2,
543 x_attribute3 IN VARCHAR2,
544 x_attribute4 IN VARCHAR2,
545 x_attribute5 IN VARCHAR2,
546 x_attribute6 IN VARCHAR2,
547 x_attribute7 IN VARCHAR2,
548 x_attribute8 IN VARCHAR2,
549 x_attribute9 IN VARCHAR2,
550 x_attribute10 IN VARCHAR2,
551 x_attribute11 IN VARCHAR2,
552 x_attribute12 IN VARCHAR2,
553 x_attribute13 IN VARCHAR2,
554 x_attribute14 IN VARCHAR2,
555 x_attribute15 IN VARCHAR2,
556 x_attribute16 IN VARCHAR2,
557 x_attribute17 IN VARCHAR2,
558 x_attribute18 IN VARCHAR2,
559 x_attribute19 IN VARCHAR2,
560 x_attribute20 IN VARCHAR2,
561 x_mode IN VARCHAR2,
562 x_payment_plan_accrl_type_code IN VARCHAR2,
563 x_payment_plan_chg_rate IN NUMBER ,
564 x_payment_plan_flat_amt IN NUMBER ,
565 x_payment_plan_max_charge_amt IN NUMBER ,
566 x_payment_plan_min_charge_amt IN NUMBER ,
567 x_payment_plan_minchgamt_nochg IN NUMBER ,
568 x_payment_plan_min_balance_amt IN NUMBER
569 ) AS
570 /*
571 || Created By : [email protected]
572 || Created On : 28-NOV-2001
573 || Purpose : Handles the INSERT DML logic for the table.
574 || Known limitations, enhancements or remarks :
575 || Change History :
576 || Who When What
577 || (reverse chronological order - newest change first)
578 || vvutukur 07-Sep-2003 Enh#3045007.Payment Plans Build.Addition of 7 columns related to
579 || payment plans.
580 */
581 CURSOR c IS
582 SELECT rowid
583 FROM igs_fi_fin_lt_plan
584 WHERE plan_name = x_plan_name;
585
586 x_last_update_date DATE;
587 x_last_updated_by NUMBER;
588 x_last_update_login NUMBER;
589
590 BEGIN
591
592 x_last_update_date := SYSDATE;
593 IF (x_mode = 'I') THEN
594 x_last_updated_by := 1;
595 x_last_update_login := 0;
596 ELSIF (x_mode = 'R') THEN
597 x_last_updated_by := fnd_global.user_id;
598 IF (x_last_updated_by IS NULL) THEN
599 x_last_updated_by := -1;
600 END IF;
601 x_last_update_login := fnd_global.login_id;
602 IF (x_last_update_login IS NULL) THEN
603 x_last_update_login := -1;
604 END IF;
605 ELSE
606 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
607 igs_ge_msg_stack.add;
608 app_exception.raise_exception;
609 END IF;
610
611 before_dml(
612 p_action => 'INSERT',
613 x_rowid => x_rowid,
614 x_plan_name => x_plan_name,
615 x_plan_type => x_plan_type,
616 x_description => x_description,
617 x_closed_ind => x_closed_ind,
618 x_balance_type => x_balance_type,
619 x_fee_type => x_fee_type,
623 x_flat_amount => x_flat_amount,
620 x_accrual_type => x_accrual_type,
621 x_offset_days => x_offset_days,
622 x_chg_rate => x_chg_rate,
624 x_max_charge_amount => x_max_charge_amount,
625 x_min_charge_amount => x_min_charge_amount,
626 x_min_charge_amount_no_charge => x_min_charge_amount_no_charge,
627 x_min_balance_amount => x_min_balance_amount,
628 x_attribute_category => x_attribute_category,
629 x_attribute1 => x_attribute1,
630 x_attribute2 => x_attribute2,
631 x_attribute3 => x_attribute3,
632 x_attribute4 => x_attribute4,
633 x_attribute5 => x_attribute5,
634 x_attribute6 => x_attribute6,
635 x_attribute7 => x_attribute7,
636 x_attribute8 => x_attribute8,
637 x_attribute9 => x_attribute9,
638 x_attribute10 => x_attribute10,
639 x_attribute11 => x_attribute11,
640 x_attribute12 => x_attribute12,
641 x_attribute13 => x_attribute13,
642 x_attribute14 => x_attribute14,
643 x_attribute15 => x_attribute15,
644 x_attribute16 => x_attribute16,
645 x_attribute17 => x_attribute17,
646 x_attribute18 => x_attribute18,
647 x_attribute19 => x_attribute19,
648 x_attribute20 => x_attribute20,
649 x_creation_date => x_last_update_date,
650 x_created_by => x_last_updated_by,
651 x_last_update_date => x_last_update_date,
652 x_last_updated_by => x_last_updated_by,
653 x_last_update_login => x_last_update_login,
654 x_payment_plan_accrl_type_code => x_payment_plan_accrl_type_code,
655 x_payment_plan_chg_rate => x_payment_plan_chg_rate,
656 x_payment_plan_flat_amt => x_payment_plan_flat_amt,
657 x_payment_plan_max_charge_amt => x_payment_plan_max_charge_amt,
658 x_payment_plan_min_charge_amt => x_payment_plan_min_charge_amt,
659 x_payment_plan_minchgamt_nochg => x_payment_plan_minchgamt_nochg,
660 x_payment_plan_min_balance_amt => x_payment_plan_min_balance_amt
661 );
662
663 INSERT INTO igs_fi_fin_lt_plan (
664 plan_name,
665 plan_type,
666 description,
667 closed_ind,
668 balance_type,
669 fee_type,
670 accrual_type,
671 offset_days,
672 chg_rate,
673 flat_amount,
674 max_charge_amount,
675 min_charge_amount,
676 min_charge_amount_no_charge,
677 min_balance_amount,
678 attribute_category,
679 attribute1,
680 attribute2,
681 attribute3,
682 attribute4,
683 attribute5,
684 attribute6,
685 attribute7,
686 attribute8,
687 attribute9,
688 attribute10,
689 attribute11,
690 attribute12,
691 attribute13,
692 attribute14,
693 attribute15,
694 attribute16,
695 attribute17,
696 attribute18,
697 attribute19,
698 attribute20,
699 creation_date,
700 created_by,
701 last_update_date,
702 last_updated_by,
703 last_update_login,
704 payment_plan_accrl_type_code,
705 payment_plan_chg_rate,
706 payment_plan_flat_amt,
707 payment_plan_max_charge_amt,
708 payment_plan_min_charge_amt,
709 payment_plan_minchgamt_nochg,
710 payment_plan_min_balance_amt
711 ) VALUES (
712 new_references.plan_name,
713 new_references.plan_type,
714 new_references.description,
715 new_references.closed_ind,
716 new_references.balance_type,
717 new_references.fee_type,
718 new_references.accrual_type,
719 new_references.offset_days,
720 new_references.chg_rate,
721 new_references.flat_amount,
722 new_references.max_charge_amount,
723 new_references.min_charge_amount,
724 new_references.min_charge_amount_no_charge,
725 new_references.min_balance_amount,
726 new_references.attribute_category,
727 new_references.attribute1,
728 new_references.attribute2,
729 new_references.attribute3,
730 new_references.attribute4,
731 new_references.attribute5,
732 new_references.attribute6,
733 new_references.attribute7,
734 new_references.attribute8,
735 new_references.attribute9,
736 new_references.attribute10,
737 new_references.attribute11,
738 new_references.attribute12,
739 new_references.attribute13,
740 new_references.attribute14,
741 new_references.attribute15,
742 new_references.attribute16,
743 new_references.attribute17,
744 new_references.attribute18,
748 x_last_updated_by,
745 new_references.attribute19,
746 new_references.attribute20,
747 x_last_update_date,
749 x_last_update_date,
750 x_last_updated_by,
751 x_last_update_login,
752 new_references.payment_plan_accrl_type_code,
753 new_references.payment_plan_chg_rate,
754 new_references.payment_plan_flat_amt,
755 new_references.payment_plan_max_charge_amt,
756 new_references.payment_plan_min_charge_amt,
757 new_references.payment_plan_minchgamt_nochg,
758 new_references.payment_plan_min_balance_amt
759 );
760
761 OPEN c;
762 FETCH c INTO x_rowid;
763 IF (c%NOTFOUND) THEN
764 CLOSE c;
765 RAISE NO_DATA_FOUND;
766 END IF;
767 CLOSE c;
768
769 END insert_row;
770
771
772 PROCEDURE lock_row (
773 x_rowid IN VARCHAR2,
774 x_plan_name IN VARCHAR2,
775 x_plan_type IN VARCHAR2,
776 x_description IN VARCHAR2,
777 x_closed_ind IN VARCHAR2,
778 x_balance_type IN VARCHAR2,
779 x_fee_type IN VARCHAR2,
780 x_accrual_type IN VARCHAR2,
781 x_offset_days IN NUMBER,
782 x_chg_rate IN NUMBER,
783 x_flat_amount IN NUMBER,
784 x_max_charge_amount IN NUMBER,
785 x_min_charge_amount IN NUMBER,
786 x_min_charge_amount_no_charge IN NUMBER,
787 x_min_balance_amount IN NUMBER,
788 x_attribute_category IN VARCHAR2,
789 x_attribute1 IN VARCHAR2,
790 x_attribute2 IN VARCHAR2,
791 x_attribute3 IN VARCHAR2,
792 x_attribute4 IN VARCHAR2,
793 x_attribute5 IN VARCHAR2,
794 x_attribute6 IN VARCHAR2,
795 x_attribute7 IN VARCHAR2,
796 x_attribute8 IN VARCHAR2,
797 x_attribute9 IN VARCHAR2,
798 x_attribute10 IN VARCHAR2,
799 x_attribute11 IN VARCHAR2,
800 x_attribute12 IN VARCHAR2,
801 x_attribute13 IN VARCHAR2,
802 x_attribute14 IN VARCHAR2,
803 x_attribute15 IN VARCHAR2,
804 x_attribute16 IN VARCHAR2,
805 x_attribute17 IN VARCHAR2,
806 x_attribute18 IN VARCHAR2,
807 x_attribute19 IN VARCHAR2,
808 x_attribute20 IN VARCHAR2,
809 x_payment_plan_accrl_type_code IN VARCHAR2,
810 x_payment_plan_chg_rate IN NUMBER ,
811 x_payment_plan_flat_amt IN NUMBER ,
812 x_payment_plan_max_charge_amt IN NUMBER ,
813 x_payment_plan_min_charge_amt IN NUMBER ,
814 x_payment_plan_minchgamt_nochg IN NUMBER ,
815 x_payment_plan_min_balance_amt IN NUMBER
816 ) AS
817 /*
818 || Created By : [email protected]
819 || Created On : 28-NOV-2001
820 || Purpose : Handles the LOCK mechanism for the table.
821 || Known limitations, enhancements or remarks :
822 || Change History :
823 || Who When What
824 || (reverse chronological order - newest change first)
825 || vvutukur 07-Sep-2003 Enh#3045007.Payment Plans Build.Addition of 7 columns related to
826 || payment plans.
827 */
828 CURSOR c1 IS
829 SELECT
830 plan_type,
831 description,
832 closed_ind,
833 balance_type,
834 fee_type,
835 accrual_type,
836 offset_days,
837 chg_rate,
838 flat_amount,
839 max_charge_amount,
840 min_charge_amount,
841 min_charge_amount_no_charge,
842 min_balance_amount,
843 attribute_category,
844 attribute1,
845 attribute2,
846 attribute3,
847 attribute4,
848 attribute5,
849 attribute6,
850 attribute7,
851 attribute8,
852 attribute9,
853 attribute10,
854 attribute11,
855 attribute12,
856 attribute13,
857 attribute14,
858 attribute15,
859 attribute16,
860 attribute17,
861 attribute18,
862 attribute19,
863 attribute20,
864 payment_plan_accrl_type_code,
865 payment_plan_chg_rate,
866 payment_plan_flat_amt,
867 payment_plan_max_charge_amt,
868 payment_plan_min_charge_amt,
869 payment_plan_minchgamt_nochg,
870 payment_plan_min_balance_amt
871 FROM igs_fi_fin_lt_plan
872 WHERE rowid = x_rowid
873 FOR UPDATE NOWAIT;
874
875 tlinfo c1%ROWTYPE;
876
877 BEGIN
878
879 OPEN c1;
880 FETCH c1 INTO tlinfo;
881 IF (c1%notfound) THEN
885 app_exception.raise_exception;
882 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
883 igs_ge_msg_stack.add;
884 CLOSE c1;
886 RETURN;
887 END IF;
888 CLOSE c1;
889
890 IF (
891 (tlinfo.plan_type = x_plan_type)
892 AND ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
893 AND (tlinfo.closed_ind = x_closed_ind)
894 AND (tlinfo.balance_type = x_balance_type)
895 AND (tlinfo.fee_type = x_fee_type)
896 AND (tlinfo.accrual_type = x_accrual_type)
897 AND ((tlinfo.offset_days = x_offset_days) OR ((tlinfo.offset_days IS NULL) AND (X_offset_days IS NULL)))
898 AND ((tlinfo.chg_rate = x_chg_rate) OR ((tlinfo.chg_rate IS NULL) AND (X_chg_rate IS NULL)))
899 AND ((tlinfo.flat_amount = x_flat_amount) OR ((tlinfo.flat_amount IS NULL) AND (X_flat_amount IS NULL)))
900 AND ((tlinfo.max_charge_amount = x_max_charge_amount) OR ((tlinfo.max_charge_amount IS NULL) AND (X_max_charge_amount IS NULL)))
901 AND ((tlinfo.min_charge_amount = x_min_charge_amount) OR ((tlinfo.min_charge_amount IS NULL) AND (X_min_charge_amount IS NULL)))
902 AND ((tlinfo.min_charge_amount_no_charge = x_min_charge_amount_no_charge) OR ((tlinfo.min_charge_amount_no_charge IS NULL) AND (X_min_charge_amount_no_charge IS NULL)))
903 AND ((tlinfo.min_balance_amount = x_min_balance_amount) OR ((tlinfo.min_balance_amount IS NULL) AND (X_min_balance_amount IS NULL)))
904 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
905 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
906 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
907 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
908 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
909 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
910 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
911 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
912 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
913 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
914 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
915 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
916 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
917 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
918 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
919 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
920 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
921 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
922 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
923 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
924 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
925 AND ((tlinfo.payment_plan_accrl_type_code = x_payment_plan_accrl_type_code) OR ((tlinfo.payment_plan_accrl_type_code IS NULL) AND (x_payment_plan_accrl_type_code IS NULL)))
926 AND ((tlinfo.payment_plan_chg_rate = x_payment_plan_chg_rate) OR ((tlinfo.payment_plan_chg_rate IS NULL) AND (x_payment_plan_chg_rate IS NULL)))
927 AND ((tlinfo.payment_plan_flat_amt = x_payment_plan_flat_amt) OR ((tlinfo.payment_plan_flat_amt IS NULL) AND (x_payment_plan_flat_amt IS NULL)))
928 AND ((tlinfo.payment_plan_max_charge_amt = x_payment_plan_max_charge_amt) OR ((tlinfo.payment_plan_max_charge_amt IS NULL) AND (x_payment_plan_max_charge_amt IS NULL)))
929 AND ((tlinfo.payment_plan_min_charge_amt = x_payment_plan_min_charge_amt) OR ((tlinfo.payment_plan_min_charge_amt IS NULL) AND (x_payment_plan_min_charge_amt IS NULL)))
930 AND ((tlinfo.payment_plan_minchgamt_nochg = x_payment_plan_minchgamt_nochg) OR ((tlinfo.payment_plan_minchgamt_nochg IS NULL) AND (x_payment_plan_minchgamt_nochg IS NULL)))
931 AND ((tlinfo.payment_plan_min_balance_amt = x_payment_plan_min_balance_amt) OR ((tlinfo.payment_plan_min_balance_amt IS NULL) AND (x_payment_plan_min_balance_amt IS NULL)))
932 ) THEN
933 NULL;
934 ELSE
935 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
936 igs_ge_msg_stack.add;
937 app_exception.raise_exception;
938 END IF;
939
940 RETURN;
941
942 END lock_row;
943
944
945 PROCEDURE update_row (
946 x_rowid IN VARCHAR2,
947 x_plan_name IN VARCHAR2,
948 x_plan_type IN VARCHAR2,
949 x_description IN VARCHAR2,
950 x_closed_ind IN VARCHAR2,
951 x_balance_type IN VARCHAR2,
952 x_fee_type IN VARCHAR2,
953 x_accrual_type IN VARCHAR2,
957 x_max_charge_amount IN NUMBER,
954 x_offset_days IN NUMBER,
955 x_chg_rate IN NUMBER,
956 x_flat_amount IN NUMBER,
958 x_min_charge_amount IN NUMBER,
959 x_min_charge_amount_no_charge IN NUMBER,
960 x_min_balance_amount IN NUMBER,
961 x_attribute_category IN VARCHAR2,
962 x_attribute1 IN VARCHAR2,
963 x_attribute2 IN VARCHAR2,
964 x_attribute3 IN VARCHAR2,
965 x_attribute4 IN VARCHAR2,
966 x_attribute5 IN VARCHAR2,
967 x_attribute6 IN VARCHAR2,
968 x_attribute7 IN VARCHAR2,
969 x_attribute8 IN VARCHAR2,
970 x_attribute9 IN VARCHAR2,
971 x_attribute10 IN VARCHAR2,
972 x_attribute11 IN VARCHAR2,
973 x_attribute12 IN VARCHAR2,
974 x_attribute13 IN VARCHAR2,
975 x_attribute14 IN VARCHAR2,
976 x_attribute15 IN VARCHAR2,
977 x_attribute16 IN VARCHAR2,
978 x_attribute17 IN VARCHAR2,
979 x_attribute18 IN VARCHAR2,
980 x_attribute19 IN VARCHAR2,
981 x_attribute20 IN VARCHAR2,
982 x_mode IN VARCHAR2,
983 x_payment_plan_accrl_type_code IN VARCHAR2,
984 x_payment_plan_chg_rate IN NUMBER ,
985 x_payment_plan_flat_amt IN NUMBER ,
986 x_payment_plan_max_charge_amt IN NUMBER ,
987 x_payment_plan_min_charge_amt IN NUMBER ,
988 x_payment_plan_minchgamt_nochg IN NUMBER ,
989 x_payment_plan_min_balance_amt IN NUMBER
990 ) AS
991 /*
992 || Created By : [email protected]
993 || Created On : 28-NOV-2001
994 || Purpose : Handles the UPDATE DML logic for the table.
995 || Known limitations, enhancements or remarks :
996 || Change History :
997 || Who When What
998 || (reverse chronological order - newest change first)
999 || vvutukur 07-Sep-2003 Enh#3045007.Payment Plans Build.Addition of 7 columns related to
1000 || payment plans.
1001 */
1002 x_last_update_date DATE ;
1003 x_last_updated_by NUMBER;
1004 x_last_update_login NUMBER;
1005
1006 BEGIN
1007
1008 x_last_update_date := SYSDATE;
1009 IF (X_MODE = 'I') THEN
1010 x_last_updated_by := 1;
1011 x_last_update_login := 0;
1012 ELSIF (x_mode = 'R') THEN
1013 x_last_updated_by := fnd_global.user_id;
1014 IF x_last_updated_by IS NULL THEN
1015 x_last_updated_by := -1;
1016 END IF;
1017 x_last_update_login := fnd_global.login_id;
1018 IF (x_last_update_login IS NULL) THEN
1019 x_last_update_login := -1;
1020 END IF;
1021 ELSE
1022 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1023 igs_ge_msg_stack.add;
1024 app_exception.raise_exception;
1025 END IF;
1026
1027 before_dml(
1028 p_action => 'UPDATE',
1029 x_rowid => x_rowid,
1030 x_plan_name => x_plan_name,
1031 x_plan_type => x_plan_type,
1032 x_description => x_description,
1033 x_closed_ind => x_closed_ind,
1034 x_balance_type => x_balance_type,
1035 x_fee_type => x_fee_type,
1036 x_accrual_type => x_accrual_type,
1037 x_offset_days => x_offset_days,
1038 x_chg_rate => x_chg_rate,
1039 x_flat_amount => x_flat_amount,
1040 x_max_charge_amount => x_max_charge_amount,
1041 x_min_charge_amount => x_min_charge_amount,
1042 x_min_charge_amount_no_charge => x_min_charge_amount_no_charge,
1043 x_min_balance_amount => x_min_balance_amount,
1044 x_attribute_category => x_attribute_category,
1045 x_attribute1 => x_attribute1,
1046 x_attribute2 => x_attribute2,
1047 x_attribute3 => x_attribute3,
1048 x_attribute4 => x_attribute4,
1049 x_attribute5 => x_attribute5,
1050 x_attribute6 => x_attribute6,
1051 x_attribute7 => x_attribute7,
1052 x_attribute8 => x_attribute8,
1053 x_attribute9 => x_attribute9,
1054 x_attribute10 => x_attribute10,
1055 x_attribute11 => x_attribute11,
1056 x_attribute12 => x_attribute12,
1057 x_attribute13 => x_attribute13,
1058 x_attribute14 => x_attribute14,
1059 x_attribute15 => x_attribute15,
1060 x_attribute16 => x_attribute16,
1064 x_attribute20 => x_attribute20,
1061 x_attribute17 => x_attribute17,
1062 x_attribute18 => x_attribute18,
1063 x_attribute19 => x_attribute19,
1065 x_creation_date => x_last_update_date,
1066 x_created_by => x_last_updated_by,
1067 x_last_update_date => x_last_update_date,
1068 x_last_updated_by => x_last_updated_by,
1069 x_last_update_login => x_last_update_login,
1070 x_payment_plan_accrl_type_code => x_payment_plan_accrl_type_code,
1071 x_payment_plan_chg_rate => x_payment_plan_chg_rate,
1072 x_payment_plan_flat_amt => x_payment_plan_flat_amt,
1073 x_payment_plan_max_charge_amt => x_payment_plan_max_charge_amt,
1074 x_payment_plan_min_charge_amt => x_payment_plan_min_charge_amt,
1075 x_payment_plan_minchgamt_nochg => x_payment_plan_minchgamt_nochg,
1076 x_payment_plan_min_balance_amt => x_payment_plan_min_balance_amt
1077 );
1078
1079 UPDATE igs_fi_fin_lt_plan
1080 SET
1081 plan_type = new_references.plan_type,
1082 description = new_references.description,
1083 closed_ind = new_references.closed_ind,
1084 balance_type = new_references.balance_type,
1085 fee_type = new_references.fee_type,
1086 accrual_type = new_references.accrual_type,
1087 offset_days = new_references.offset_days,
1088 chg_rate = new_references.chg_rate,
1089 flat_amount = new_references.flat_amount,
1090 max_charge_amount = new_references.max_charge_amount,
1091 min_charge_amount = new_references.min_charge_amount,
1092 min_charge_amount_no_charge = new_references.min_charge_amount_no_charge,
1093 min_balance_amount = new_references.min_balance_amount,
1094 attribute_category = new_references.attribute_category,
1095 attribute1 = new_references.attribute1,
1096 attribute2 = new_references.attribute2,
1097 attribute3 = new_references.attribute3,
1098 attribute4 = new_references.attribute4,
1099 attribute5 = new_references.attribute5,
1100 attribute6 = new_references.attribute6,
1101 attribute7 = new_references.attribute7,
1102 attribute8 = new_references.attribute8,
1103 attribute9 = new_references.attribute9,
1104 attribute10 = new_references.attribute10,
1105 attribute11 = new_references.attribute11,
1106 attribute12 = new_references.attribute12,
1107 attribute13 = new_references.attribute13,
1108 attribute14 = new_references.attribute14,
1109 attribute15 = new_references.attribute15,
1110 attribute16 = new_references.attribute16,
1111 attribute17 = new_references.attribute17,
1112 attribute18 = new_references.attribute18,
1113 attribute19 = new_references.attribute19,
1114 attribute20 = new_references.attribute20,
1115 last_update_date = x_last_update_date,
1116 last_updated_by = x_last_updated_by,
1117 last_update_login = x_last_update_login,
1118 payment_plan_accrl_type_code = new_references.payment_plan_accrl_type_code,
1119 payment_plan_chg_rate = new_references.payment_plan_chg_rate,
1120 payment_plan_flat_amt = new_references.payment_plan_flat_amt,
1121 payment_plan_max_charge_amt = new_references.payment_plan_max_charge_amt,
1122 payment_plan_min_charge_amt = new_references.payment_plan_min_charge_amt,
1123 payment_plan_minchgamt_nochg = new_references.payment_plan_minchgamt_nochg,
1124 payment_plan_min_balance_amt = new_references.payment_plan_min_balance_amt
1125 WHERE rowid = x_rowid;
1126
1127 IF (SQL%NOTFOUND) THEN
1128 RAISE NO_DATA_FOUND;
1129 END IF;
1130
1131 END update_row;
1132
1133
1134 PROCEDURE add_row (
1135 x_rowid IN OUT NOCOPY VARCHAR2,
1136 x_plan_name IN VARCHAR2,
1137 x_plan_type IN VARCHAR2,
1138 x_description IN VARCHAR2,
1139 x_closed_ind IN VARCHAR2,
1140 x_balance_type IN VARCHAR2,
1141 x_fee_type IN VARCHAR2,
1142 x_accrual_type IN VARCHAR2,
1143 x_offset_days IN NUMBER,
1144 x_chg_rate IN NUMBER,
1145 x_flat_amount IN NUMBER,
1146 x_max_charge_amount IN NUMBER,
1147 x_min_charge_amount IN NUMBER,
1148 x_min_charge_amount_no_charge IN NUMBER,
1149 x_min_balance_amount IN NUMBER,
1150 x_attribute_category IN VARCHAR2,
1151 x_attribute1 IN VARCHAR2,
1155 x_attribute5 IN VARCHAR2,
1152 x_attribute2 IN VARCHAR2,
1153 x_attribute3 IN VARCHAR2,
1154 x_attribute4 IN VARCHAR2,
1156 x_attribute6 IN VARCHAR2,
1157 x_attribute7 IN VARCHAR2,
1158 x_attribute8 IN VARCHAR2,
1159 x_attribute9 IN VARCHAR2,
1160 x_attribute10 IN VARCHAR2,
1161 x_attribute11 IN VARCHAR2,
1162 x_attribute12 IN VARCHAR2,
1163 x_attribute13 IN VARCHAR2,
1164 x_attribute14 IN VARCHAR2,
1165 x_attribute15 IN VARCHAR2,
1166 x_attribute16 IN VARCHAR2,
1167 x_attribute17 IN VARCHAR2,
1168 x_attribute18 IN VARCHAR2,
1169 x_attribute19 IN VARCHAR2,
1170 x_attribute20 IN VARCHAR2,
1171 x_mode IN VARCHAR2,
1172 x_payment_plan_accrl_type_code IN VARCHAR2,
1173 x_payment_plan_chg_rate IN NUMBER ,
1174 x_payment_plan_flat_amt IN NUMBER ,
1175 x_payment_plan_max_charge_amt IN NUMBER ,
1176 x_payment_plan_min_charge_amt IN NUMBER ,
1177 x_payment_plan_minchgamt_nochg IN NUMBER ,
1178 x_payment_plan_min_balance_amt IN NUMBER
1179 ) AS
1180 /*
1181 || Created By : [email protected]
1182 || Created On : 28-NOV-2001
1183 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1184 || Known limitations, enhancements or remarks :
1185 || Change History :
1186 || Who When What
1187 || (reverse chronological order - newest change first)
1188 || vvutukur 07-Sep-2003 Enh#3045007.Payment Plans Build.Addition of 7 columns related to
1189 || payment plans.
1190 */
1191 CURSOR c1 IS
1192 SELECT rowid
1193 FROM igs_fi_fin_lt_plan
1194 WHERE plan_name = x_plan_name;
1195
1196 BEGIN
1197
1198 OPEN c1;
1199 FETCH c1 INTO x_rowid;
1200 IF (c1%NOTFOUND) THEN
1201 CLOSE c1;
1202
1203 insert_row (
1204 x_rowid,
1205 x_plan_name,
1206 x_plan_type,
1207 x_description,
1208 x_closed_ind,
1209 x_balance_type,
1210 x_fee_type,
1211 x_accrual_type,
1212 x_offset_days,
1213 x_chg_rate,
1214 x_flat_amount,
1215 x_max_charge_amount,
1216 x_min_charge_amount,
1217 x_min_charge_amount_no_charge,
1218 x_min_balance_amount,
1219 x_attribute_category,
1220 x_attribute1,
1221 x_attribute2,
1222 x_attribute3,
1223 x_attribute4,
1224 x_attribute5,
1225 x_attribute6,
1226 x_attribute7,
1227 x_attribute8,
1228 x_attribute9,
1229 x_attribute10,
1230 x_attribute11,
1231 x_attribute12,
1232 x_attribute13,
1233 x_attribute14,
1234 x_attribute15,
1235 x_attribute16,
1236 x_attribute17,
1237 x_attribute18,
1238 x_attribute19,
1239 x_attribute20,
1240 x_mode,
1241 x_payment_plan_accrl_type_code,
1242 x_payment_plan_chg_rate,
1243 x_payment_plan_flat_amt,
1244 x_payment_plan_max_charge_amt,
1245 x_payment_plan_min_charge_amt,
1246 x_payment_plan_minchgamt_nochg,
1247 x_payment_plan_min_balance_amt
1248 );
1249 RETURN;
1250 END IF;
1251 CLOSE c1;
1252
1253 update_row (
1254 x_rowid,
1255 x_plan_name,
1256 x_plan_type,
1257 x_description,
1258 x_closed_ind,
1259 x_balance_type,
1260 x_fee_type,
1261 x_accrual_type,
1262 x_offset_days,
1263 x_chg_rate,
1264 x_flat_amount,
1265 x_max_charge_amount,
1266 x_min_charge_amount,
1267 x_min_charge_amount_no_charge,
1268 x_min_balance_amount,
1269 x_attribute_category,
1270 x_attribute1,
1271 x_attribute2,
1272 x_attribute3,
1273 x_attribute4,
1274 x_attribute5,
1275 x_attribute6,
1276 x_attribute7,
1277 x_attribute8,
1278 x_attribute9,
1279 x_attribute10,
1280 x_attribute11,
1281 x_attribute12,
1282 x_attribute13,
1283 x_attribute14,
1284 x_attribute15,
1285 x_attribute16,
1286 x_attribute17,
1287 x_attribute18,
1288 x_attribute19,
1289 x_attribute20,
1290 x_mode,
1291 x_payment_plan_accrl_type_code,
1292 x_payment_plan_chg_rate,
1293 x_payment_plan_flat_amt,
1294 x_payment_plan_max_charge_amt,
1295 x_payment_plan_min_charge_amt,
1296 x_payment_plan_minchgamt_nochg,
1297 x_payment_plan_min_balance_amt
1298 );
1299
1300 END add_row;
1301
1302
1303 PROCEDURE delete_row (
1304 x_rowid IN VARCHAR2
1305 ) AS
1306 /*
1307 || Created By : [email protected]
1308 || Created On : 28-NOV-2001
1309 || Purpose : Handles the DELETE DML logic for the table.
1310 || Known limitations, enhancements or remarks :
1311 || Change History :
1312 || Who When What
1313 || (reverse chronological order - newest change first)
1314 */
1315 BEGIN
1316
1317 before_dml (
1318 p_action => 'DELETE',
1319 x_rowid => x_rowid
1320 );
1321
1322 DELETE FROM igs_fi_fin_lt_plan
1323 WHERE rowid = x_rowid;
1324
1325 IF (SQL%NOTFOUND) THEN
1326 RAISE NO_DATA_FOUND;
1327 END IF;
1328
1329 END delete_row;
1330
1331
1332 END igs_fi_fin_lt_plan_pkg;
[Home] [Help]