[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_PP_TEMPLATES_PKG
Source
1 PACKAGE BODY igs_fi_pp_templates_pkg AS
2 /* $Header: IGSSID8B.pls 115.1 2003/09/08 16:55:31 smvk noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_pp_templates%ROWTYPE;
6 new_references igs_fi_pp_templates%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_payment_plan_name IN VARCHAR2,
12 x_payment_plan_desc IN VARCHAR2,
13 x_closed_flag IN VARCHAR2,
14 x_installment_period_code IN VARCHAR2,
15 x_due_day_of_month IN NUMBER,
16 x_due_end_of_month_flag IN VARCHAR2,
17 x_due_cutoff_day IN NUMBER,
18 x_processing_fee_type IN VARCHAR2,
19 x_processing_fee_amt IN NUMBER,
20 x_installment_method_flag IN VARCHAR2,
21 x_base_amt IN NUMBER,
22 x_creation_date IN DATE,
23 x_created_by IN NUMBER,
24 x_last_update_date IN DATE,
25 x_last_updated_by IN NUMBER,
26 x_last_update_login IN NUMBER
27 ) AS
28 /*
29 || Created By : smvk
30 || Created On : 24-AUG-2003
31 || Purpose : Initialises the Old and New references for the columns of the table.
32 || Known limitations, enhancements or remarks :
33 || Change History :
34 || Who When What
35 || (reverse chronological order - newest change first)
36 */
37
38 CURSOR cur_old_ref_values IS
39 SELECT *
40 FROM igs_fi_pp_templates
41 WHERE rowid = x_rowid;
42
43 BEGIN
44
45 l_rowid := x_rowid;
46
47 -- Code for setting the Old and New Reference Values.
48 -- Populate Old Values.
49 OPEN cur_old_ref_values;
50 FETCH cur_old_ref_values INTO old_references;
51 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
52 CLOSE cur_old_ref_values;
53 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
54 igs_ge_msg_stack.add;
55 app_exception.raise_exception;
56 RETURN;
57 END IF;
58 CLOSE cur_old_ref_values;
59
60 -- Populate New Values.
61 new_references.payment_plan_name := x_payment_plan_name;
62 new_references.payment_plan_desc := x_payment_plan_desc;
63 new_references.closed_flag := x_closed_flag;
64 new_references.installment_period_code := x_installment_period_code;
65 new_references.due_day_of_month := x_due_day_of_month;
66 new_references.due_end_of_month_flag := x_due_end_of_month_flag;
67 new_references.due_cutoff_day := x_due_cutoff_day;
68 new_references.processing_fee_type := x_processing_fee_type;
69 new_references.processing_fee_amt := x_processing_fee_amt;
70 new_references.installment_method_flag := x_installment_method_flag;
71 new_references.base_amt := x_base_amt;
72
73 IF (p_action = 'UPDATE') THEN
74 new_references.creation_date := old_references.creation_date;
75 new_references.created_by := old_references.created_by;
76 ELSE
77 new_references.creation_date := x_creation_date;
78 new_references.created_by := x_created_by;
79 END IF;
80
81 new_references.last_update_date := x_last_update_date;
82 new_references.last_updated_by := x_last_updated_by;
83 new_references.last_update_login := x_last_update_login;
84
85 END set_column_values;
86
87
88 PROCEDURE check_parent_existance AS
89 /*
90 || Created By : smvk
91 || Created On : 24-AUG-2003
92 || Purpose : Checks for the existance of Parent records.
93 || Known limitations, enhancements or remarks :
94 || Change History :
95 || Who When What
96 || smvk 06-Sep-2003 Added the check for the Fk with igs_lookups_view.
97 || (reverse chronological order - newest change first)
98 */
99 BEGIN
100
101 IF (((old_references.processing_fee_type = new_references.processing_fee_type)) OR
102 ((new_references.processing_fee_type IS NULL))) THEN
103 NULL;
104 ELSIF NOT igs_fi_fee_type_pkg.get_pk_for_validation (
105 new_references.processing_fee_type
106 ) THEN
107 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
108 igs_ge_msg_stack.add;
109 app_exception.raise_exception;
110 END IF;
111
112 IF (((old_references.installment_period_code = new_references.installment_period_code )) OR
113 ((new_references.installment_period_code IS NULL))) THEN
114 NULL;
115 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation ('IGS_FI_INSTALLMENT_PERIOD', new_references.installment_period_code
116 ) THEN
117 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
118 igs_ge_msg_stack.add;
119 app_exception.raise_exception;
120 END IF;
121
122 END check_parent_existance;
123
124 FUNCTION get_pk_for_validation (
125 x_payment_plan_name IN VARCHAR2
126 ) RETURN BOOLEAN AS
127 /*
128 || Created By : smvk
129 || Created On : 24-AUG-2003
130 || Purpose : Validates the Primary Key of the table.
131 || Known limitations, enhancements or remarks :
132 || Change History :
133 || Who When What
134 || (reverse chronological order - newest change first)
135 */
136 CURSOR cur_rowid IS
137 SELECT rowid
138 FROM igs_fi_pp_templates
139 WHERE payment_plan_name = x_payment_plan_name;
140
141 lv_rowid cur_rowid%RowType;
142
143 BEGIN
144
145 OPEN cur_rowid;
146 FETCH cur_rowid INTO lv_rowid;
147 IF (cur_rowid%FOUND) THEN
148 CLOSE cur_rowid;
149 RETURN(TRUE);
150 ELSE
151 CLOSE cur_rowid;
152 RETURN(FALSE);
153 END IF;
154
155 END get_pk_for_validation;
156
157 PROCEDURE before_dml (
158 p_action IN VARCHAR2,
159 x_rowid IN VARCHAR2,
160 x_payment_plan_name IN VARCHAR2,
161 x_payment_plan_desc IN VARCHAR2,
162 x_closed_flag IN VARCHAR2,
163 x_installment_period_code IN VARCHAR2,
164 x_due_day_of_month IN NUMBER,
165 x_due_end_of_month_flag IN VARCHAR2,
166 x_due_cutoff_day IN NUMBER,
167 x_processing_fee_type IN VARCHAR2,
168 x_processing_fee_amt IN NUMBER,
169 x_installment_method_flag IN VARCHAR2,
170 x_base_amt IN NUMBER,
171 x_creation_date IN DATE,
172 x_created_by IN NUMBER,
173 x_last_update_date IN DATE,
174 x_last_updated_by IN NUMBER,
175 x_last_update_login IN NUMBER
176 ) AS
177 /*
178 || Created By : smvk
179 || Created On : 24-AUG-2003
180 || Purpose : Initialises the columns, Checks Constraints, Calls the
181 || Trigger Handlers for the table, before any DML operation.
182 || Known limitations, enhancements or remarks :
183 || Change History :
184 || Who When What
185 || smvk 06-Sep-2003 l_rowid is initialized to null.
186 || (reverse chronological order - newest change first)
187 */
188 BEGIN
189
190 set_column_values (
191 p_action,
192 x_rowid,
193 x_payment_plan_name,
194 x_payment_plan_desc,
195 x_closed_flag,
196 x_installment_period_code,
197 x_due_day_of_month,
198 x_due_end_of_month_flag,
199 x_due_cutoff_day,
200 x_processing_fee_type,
201 x_processing_fee_amt,
202 x_installment_method_flag,
203 x_base_amt,
204 x_creation_date,
205 x_created_by,
206 x_last_update_date,
207 x_last_updated_by,
208 x_last_update_login
209 );
210
211 IF (p_action = 'INSERT') THEN
212 -- Call all the procedures related to Before Insert.
213 IF ( get_pk_for_validation(
214 new_references.payment_plan_name
215 )
216 ) THEN
217 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
218 igs_ge_msg_stack.add;
219 app_exception.raise_exception;
220 END IF;
221 check_parent_existance;
222 ELSIF (p_action = 'UPDATE') THEN
223 -- Call all the procedures related to Before Update.
224 check_parent_existance;
225 ELSIF (p_action = 'VALIDATE_INSERT') THEN
226 -- Call all the procedures related to Before Insert.
227 IF ( get_pk_for_validation (
228 new_references.payment_plan_name
229 )
230 ) THEN
231 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
232 igs_ge_msg_stack.add;
233 app_exception.raise_exception;
234 END IF;
235 END IF;
236
237 l_rowid := NULL;
238 END before_dml;
239
240
241 PROCEDURE insert_row (
242 x_rowid IN OUT NOCOPY VARCHAR2,
243 x_payment_plan_name IN VARCHAR2,
244 x_payment_plan_desc IN VARCHAR2,
245 x_closed_flag IN VARCHAR2,
246 x_installment_period_code IN VARCHAR2,
247 x_due_day_of_month IN NUMBER,
248 x_due_end_of_month_flag IN VARCHAR2,
249 x_due_cutoff_day IN NUMBER,
250 x_processing_fee_type IN VARCHAR2,
251 x_processing_fee_amt IN NUMBER,
252 x_installment_method_flag IN VARCHAR2,
253 x_base_amt IN NUMBER,
254 x_mode IN VARCHAR2
255 ) AS
256 /*
257 || Created By : smvk
258 || Created On : 24-AUG-2003
259 || Purpose : Handles the INSERT DML logic for the table.
260 || Known limitations, enhancements or remarks :
261 || Change History :
262 || Who When What
263 || (reverse chronological order - newest change first)
264 */
265
266 x_last_update_date DATE;
267 x_last_updated_by NUMBER;
268 x_last_update_login NUMBER;
269
270 BEGIN
271
272 x_last_update_date := SYSDATE;
273 IF (x_mode = 'I') THEN
274 x_last_updated_by := 1;
275 x_last_update_login := 0;
276 ELSIF (x_mode = 'R') THEN
277 x_last_updated_by := fnd_global.user_id;
278 IF (x_last_updated_by IS NULL) THEN
279 x_last_updated_by := -1;
280 END IF;
281 x_last_update_login := fnd_global.login_id;
282 IF (x_last_update_login IS NULL) THEN
283 x_last_update_login := -1;
284 END IF;
285 ELSE
286 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
287 fnd_message.set_token ('ROUTINE', 'IGS_FI_PP_TEMPLATES_PKG.INSERT_ROW');
288 igs_ge_msg_stack.add;
289 app_exception.raise_exception;
290 END IF;
291
292 before_dml(
293 p_action => 'INSERT',
294 x_rowid => x_rowid,
295 x_payment_plan_name => x_payment_plan_name,
296 x_payment_plan_desc => x_payment_plan_desc,
297 x_closed_flag => x_closed_flag,
298 x_installment_period_code => x_installment_period_code,
299 x_due_day_of_month => x_due_day_of_month,
300 x_due_end_of_month_flag => x_due_end_of_month_flag,
301 x_due_cutoff_day => x_due_cutoff_day,
302 x_processing_fee_type => x_processing_fee_type,
303 x_processing_fee_amt => x_processing_fee_amt,
304 x_installment_method_flag => x_installment_method_flag,
305 x_base_amt => x_base_amt,
306 x_creation_date => x_last_update_date,
307 x_created_by => x_last_updated_by,
308 x_last_update_date => x_last_update_date,
309 x_last_updated_by => x_last_updated_by,
310 x_last_update_login => x_last_update_login
311 );
312
313 INSERT INTO igs_fi_pp_templates (
314 payment_plan_name,
315 payment_plan_desc,
316 closed_flag,
317 installment_period_code,
318 due_day_of_month,
319 due_end_of_month_flag,
320 due_cutoff_day,
321 processing_fee_type,
322 processing_fee_amt,
323 installment_method_flag,
324 base_amt,
325 creation_date,
326 created_by,
327 last_update_date,
328 last_updated_by,
329 last_update_login
330 ) VALUES (
331 new_references.payment_plan_name,
332 new_references.payment_plan_desc,
333 new_references.closed_flag,
334 new_references.installment_period_code,
338 new_references.processing_fee_type,
335 new_references.due_day_of_month,
336 new_references.due_end_of_month_flag,
337 new_references.due_cutoff_day,
339 new_references.processing_fee_amt,
340 new_references.installment_method_flag,
341 new_references.base_amt,
342 x_last_update_date,
343 x_last_updated_by,
344 x_last_update_date,
345 x_last_updated_by,
346 x_last_update_login
347 ) RETURNING ROWID INTO x_rowid;
348
349 END insert_row;
350
351
352 PROCEDURE lock_row (
353 x_rowid IN VARCHAR2,
354 x_payment_plan_name IN VARCHAR2,
355 x_payment_plan_desc IN VARCHAR2,
356 x_closed_flag IN VARCHAR2,
357 x_installment_period_code IN VARCHAR2,
358 x_due_day_of_month IN NUMBER,
359 x_due_end_of_month_flag IN VARCHAR2,
360 x_due_cutoff_day IN NUMBER,
361 x_processing_fee_type IN VARCHAR2,
362 x_processing_fee_amt IN NUMBER,
363 x_installment_method_flag IN VARCHAR2,
364 x_base_amt IN NUMBER
365 ) AS
366 /*
367 || Created By : smvk
368 || Created On : 24-AUG-2003
369 || Purpose : Handles the LOCK mechanism for 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 c1 IS
376 SELECT
377 payment_plan_desc,
378 closed_flag,
379 installment_period_code,
380 due_day_of_month,
381 due_end_of_month_flag,
382 due_cutoff_day,
383 processing_fee_type,
384 processing_fee_amt,
385 installment_method_flag,
386 base_amt
387 FROM igs_fi_pp_templates
388 WHERE rowid = x_rowid
389 FOR UPDATE NOWAIT;
390
391 tlinfo c1%ROWTYPE;
392
393 BEGIN
394
395 OPEN c1;
396 FETCH c1 INTO tlinfo;
397 IF (c1%notfound) THEN
398 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
399 igs_ge_msg_stack.add;
400 CLOSE c1;
401 app_exception.raise_exception;
402 RETURN;
403 END IF;
404 CLOSE c1;
405
406 IF (
407 (tlinfo.payment_plan_desc = x_payment_plan_desc)
408 AND (tlinfo.closed_flag = x_closed_flag)
409 AND (tlinfo.installment_period_code = x_installment_period_code)
410 AND ((tlinfo.due_day_of_month = x_due_day_of_month) OR ((tlinfo.due_day_of_month IS NULL) AND (X_due_day_of_month IS NULL)))
411 AND ((tlinfo.due_end_of_month_flag = x_due_end_of_month_flag) OR ((tlinfo.due_end_of_month_flag IS NULL) AND (X_due_end_of_month_flag IS NULL)))
412 AND ((tlinfo.due_cutoff_day = x_due_cutoff_day) OR ((tlinfo.due_cutoff_day IS NULL) AND (X_due_cutoff_day IS NULL)))
413 AND ((tlinfo.processing_fee_type = x_processing_fee_type) OR ((tlinfo.processing_fee_type IS NULL) AND (X_processing_fee_type IS NULL)))
414 AND ((tlinfo.processing_fee_amt = x_processing_fee_amt) OR ((tlinfo.processing_fee_amt IS NULL) AND (X_processing_fee_amt IS NULL)))
415 AND (tlinfo.installment_method_flag = x_installment_method_flag)
416 AND ((tlinfo.base_amt = x_base_amt) OR ((tlinfo.base_amt IS NULL) AND (X_base_amt IS NULL)))
417 ) THEN
418 NULL;
419 ELSE
420 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
421 igs_ge_msg_stack.add;
422 app_exception.raise_exception;
423 END IF;
424
425 RETURN;
426
427 END lock_row;
428
429
430 PROCEDURE update_row (
431 x_rowid IN VARCHAR2,
432 x_payment_plan_name IN VARCHAR2,
433 x_payment_plan_desc IN VARCHAR2,
434 x_closed_flag IN VARCHAR2,
435 x_installment_period_code IN VARCHAR2,
436 x_due_day_of_month IN NUMBER,
437 x_due_end_of_month_flag IN VARCHAR2,
438 x_due_cutoff_day IN NUMBER,
439 x_processing_fee_type IN VARCHAR2,
440 x_processing_fee_amt IN NUMBER,
441 x_installment_method_flag IN VARCHAR2,
442 x_base_amt IN NUMBER,
443 x_mode IN VARCHAR2
444 ) AS
445 /*
446 || Created By : smvk
447 || Created On : 24-AUG-2003
448 || Purpose : Handles the UPDATE DML logic for the table.
449 || Known limitations, enhancements or remarks :
450 || Change History :
451 || Who When What
452 || (reverse chronological order - newest change first)
453 */
454 x_last_update_date DATE ;
455 x_last_updated_by NUMBER;
456 x_last_update_login NUMBER;
457
458 BEGIN
459
460 x_last_update_date := SYSDATE;
461 IF (X_MODE = 'I') THEN
462 x_last_updated_by := 1;
463 x_last_update_login := 0;
464 ELSIF (x_mode = 'R') THEN
465 x_last_updated_by := fnd_global.user_id;
466 IF x_last_updated_by IS NULL THEN
467 x_last_updated_by := -1;
468 END IF;
469 x_last_update_login := fnd_global.login_id;
470 IF (x_last_update_login IS NULL) THEN
471 x_last_update_login := -1;
472 END IF;
473 ELSE
474 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
475 fnd_message.set_token ('ROUTINE', 'IGS_FI_PP_TEMPLATES_PKG.UPDATE_ROW');
476 igs_ge_msg_stack.add;
477 app_exception.raise_exception;
478 END IF;
479
480 before_dml(
481 p_action => 'UPDATE',
482 x_rowid => x_rowid,
483 x_payment_plan_name => x_payment_plan_name,
484 x_payment_plan_desc => x_payment_plan_desc,
485 x_closed_flag => x_closed_flag,
486 x_installment_period_code => x_installment_period_code,
487 x_due_day_of_month => x_due_day_of_month,
488 x_due_end_of_month_flag => x_due_end_of_month_flag,
489 x_due_cutoff_day => x_due_cutoff_day,
490 x_processing_fee_type => x_processing_fee_type,
491 x_processing_fee_amt => x_processing_fee_amt,
492 x_installment_method_flag => x_installment_method_flag,
493 x_base_amt => x_base_amt,
494 x_creation_date => x_last_update_date,
495 x_created_by => x_last_updated_by,
496 x_last_update_date => x_last_update_date,
497 x_last_updated_by => x_last_updated_by,
498 x_last_update_login => x_last_update_login
499 );
500
501 UPDATE igs_fi_pp_templates
502 SET
503 payment_plan_desc = new_references.payment_plan_desc,
504 closed_flag = new_references.closed_flag,
505 installment_period_code = new_references.installment_period_code,
506 due_day_of_month = new_references.due_day_of_month,
507 due_end_of_month_flag = new_references.due_end_of_month_flag,
508 due_cutoff_day = new_references.due_cutoff_day,
509 processing_fee_type = new_references.processing_fee_type,
510 processing_fee_amt = new_references.processing_fee_amt,
511 installment_method_flag = new_references.installment_method_flag,
512 base_amt = new_references.base_amt,
513 last_update_date = x_last_update_date,
514 last_updated_by = x_last_updated_by,
515 last_update_login = x_last_update_login
516 WHERE rowid = x_rowid;
517
518 IF (SQL%NOTFOUND) THEN
519 RAISE NO_DATA_FOUND;
520 END IF;
521
522 END update_row;
523
524
525 PROCEDURE add_row (
526 x_rowid IN OUT NOCOPY VARCHAR2,
527 x_payment_plan_name IN VARCHAR2,
528 x_payment_plan_desc IN VARCHAR2,
529 x_closed_flag IN VARCHAR2,
530 x_installment_period_code IN VARCHAR2,
531 x_due_day_of_month IN NUMBER,
532 x_due_end_of_month_flag IN VARCHAR2,
533 x_due_cutoff_day IN NUMBER,
534 x_processing_fee_type IN VARCHAR2,
535 x_processing_fee_amt IN NUMBER,
536 x_installment_method_flag IN VARCHAR2,
537 x_base_amt IN NUMBER,
538 x_mode IN VARCHAR2
539 ) AS
540 /*
541 || Created By : smvk
542 || Created On : 24-AUG-2003
543 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
544 || Known limitations, enhancements or remarks :
545 || Change History :
546 || Who When What
547 || (reverse chronological order - newest change first)
548 */
549 CURSOR c1 IS
550 SELECT rowid
551 FROM igs_fi_pp_templates
552 WHERE payment_plan_name = x_payment_plan_name;
553
554 BEGIN
555
556 OPEN c1;
557 FETCH c1 INTO x_rowid;
558 IF (c1%NOTFOUND) THEN
559 CLOSE c1;
560
561 insert_row (
562 x_rowid,
563 x_payment_plan_name,
564 x_payment_plan_desc,
565 x_closed_flag,
566 x_installment_period_code,
567 x_due_day_of_month,
568 x_due_end_of_month_flag,
569 x_due_cutoff_day,
570 x_processing_fee_type,
571 x_processing_fee_amt,
572 x_installment_method_flag,
573 x_base_amt,
574 x_mode
575 );
576 RETURN;
577 END IF;
578 CLOSE c1;
579
580 update_row (
581 x_rowid,
582 x_payment_plan_name,
583 x_payment_plan_desc,
584 x_closed_flag,
585 x_installment_period_code,
586 x_due_day_of_month,
587 x_due_end_of_month_flag,
588 x_due_cutoff_day,
589 x_processing_fee_type,
590 x_processing_fee_amt,
591 x_installment_method_flag,
592 x_base_amt,
593 x_mode
594 );
595
596 END add_row;
597
598 END igs_fi_pp_templates_pkg;