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