1 PACKAGE BODY igs_fi_bal_ex_c_typs_pkg AS
2 /* $Header: IGSSI98B.pls 115.9 2003/03/19 08:36:16 smadathi ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_bal_ex_c_typs%ROWTYPE;
6 new_references igs_fi_bal_ex_c_typs%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_bal_exc_credit_type_id IN NUMBER DEFAULT NULL,
12 x_balance_rule_id IN NUMBER DEFAULT NULL,
13 x_credit_type_id IN NUMBER 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 || (reverse chronological order - newest change first)
28 || smadathi 10-Apr-2002 Bug 2289191. Enabled_flag column reference removed.
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_FI_BAL_EX_C_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_credit_type_id := x_bal_exc_credit_type_id;
55 new_references.balance_rule_id := x_balance_rule_id;
56 new_references.credit_type_id := x_credit_type_id;
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.credit_type_id = new_references.credit_type_id)) OR
97 ((new_references.credit_type_id IS NULL))) THEN
98 NULL;
99 ELSIF NOT igs_fi_cr_types_pkg.get_pk_for_validation (
100 new_references.credit_type_id
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 : vchappid
112 || Created On : 25-Mar-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.credit_type_id
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_credit_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_c_typs
148 WHERE bal_exc_credit_type_id = x_bal_exc_credit_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_credit_type_id IN NUMBER
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_c_typs
182 WHERE balance_rule_id = x_balance_rule_id
183 AND credit_type_id = x_credit_type_id
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
203 PROCEDURE get_fk_igs_fi_cr_types_all (
204 x_credit_type_id IN NUMBER
205 ) AS
206 /*
207 || Created By : BDEVARAK
208 || Created On : 26-APR-2001
209 || Purpose : Validates the Foreign Keys for the table.
210 || Known limitations, enhancements or remarks :
211 || Change History :
212 || Who When What
213 || (reverse chronological order - newest change first)
214 */
215 CURSOR cur_rowid IS
216 SELECT rowid
217 FROM igs_fi_bal_ex_c_typs
218 WHERE ((credit_type_id = x_credit_type_id));
219
220 lv_rowid cur_rowid%RowType;
221
222 BEGIN
223
224 OPEN cur_rowid;
225 FETCH cur_rowid INTO lv_rowid;
226 IF (cur_rowid%FOUND) THEN
227 CLOSE cur_rowid;
228 fnd_message.set_name ('IGS', 'IGS_FI_EXCT_CRTY_FK');
229 igs_ge_msg_stack.add;
230 app_exception.raise_exception;
231 RETURN;
232 END IF;
233 CLOSE cur_rowid;
234
235 END get_fk_igs_fi_cr_types_all;
236
237
238 PROCEDURE before_dml (
239 p_action IN VARCHAR2,
240 x_rowid IN VARCHAR2 DEFAULT NULL,
241 x_bal_exc_credit_type_id IN NUMBER DEFAULT NULL,
242 x_balance_rule_id IN NUMBER DEFAULT NULL,
243 x_credit_type_id IN NUMBER DEFAULT NULL,
244 x_creation_date IN DATE DEFAULT NULL,
245 x_created_by IN NUMBER DEFAULT NULL,
246 x_last_update_date IN DATE DEFAULT NULL,
247 x_last_updated_by IN NUMBER DEFAULT NULL,
248 x_last_update_login IN NUMBER DEFAULT NULL
249 ) AS
250 /*
251 || Created By : BDEVARAK
252 || Created On : 26-APR-2001
253 || Purpose : Initialises the columns, Checks Constraints, Calls the
254 || Trigger Handlers for the table, before any DML operation.
255 || Known limitations, enhancements or remarks :
256 || Change History :
257 || Who When What
258 || (reverse chronological order - newest change first)
259 || smadathi 18-FEB-2003 Bug 2473845. Added logic to re initialize l_rowid to null.
260 || smadathi 10-Apr-2002 Bug 2289191. Enabled_flag column reference removed.
261 || vchappid 25-mar-2002 Added call to procedure check_uniqueness
262 */
263 BEGIN
264
265 set_column_values (
266 p_action,
267 x_rowid,
268 x_bal_exc_credit_type_id,
269 x_balance_rule_id,
270 x_credit_type_id,
271 x_creation_date,
272 x_created_by,
273 x_last_update_date,
274 x_last_updated_by,
275 x_last_update_login
276 );
277
278 IF (p_action = 'INSERT') THEN
279 -- Call all the procedures related to Before Insert.
280 IF ( get_pk_for_validation(
281 new_references.bal_exc_credit_type_id
282 )
283 ) THEN
284 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
285 igs_ge_msg_stack.add;
286 app_exception.raise_exception;
287 END IF;
288 check_uniqueness;
289 check_parent_existance;
290 ELSIF (p_action = 'UPDATE') THEN
291 -- Call all the procedures related to Before Update.
292 check_uniqueness;
293 check_parent_existance;
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_credit_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 END IF;
306 l_rowid := NULL;
307 END before_dml;
308
309
310 PROCEDURE insert_row (
311 x_rowid IN OUT NOCOPY VARCHAR2,
312 x_bal_exc_credit_type_id IN OUT NOCOPY NUMBER,
313 x_balance_rule_id IN NUMBER,
314 x_credit_type_id IN NUMBER,
315 x_mode IN VARCHAR2 DEFAULT 'R'
316 ) AS
317 /*
318 || Created By : BDEVARAK
319 || Created On : 26-APR-2001
320 || Purpose : Handles the INSERT DML logic for the table.
321 || Known limitations, enhancements or remarks :
322 || Change History :
323 || Who When What
324 || (reverse chronological order - newest change first)
325 || smadathi 10-Apr-2002 Bug 2289191. Enabled_flag column reference removed.
326 */
327 CURSOR c IS
328 SELECT rowid
329 FROM igs_fi_bal_ex_c_typs
330 WHERE bal_exc_credit_type_id = x_bal_exc_credit_type_id;
331
332 x_last_update_date DATE;
333 x_last_updated_by NUMBER;
334 x_last_update_login NUMBER;
335
336 BEGIN
337
338 x_last_update_date := SYSDATE;
339 IF (x_mode = 'I') THEN
340 x_last_updated_by := 1;
341 x_last_update_login := 0;
342 ELSIF (x_mode = 'R') THEN
343 x_last_updated_by := fnd_global.user_id;
344 IF (x_last_updated_by IS NULL) THEN
345 x_last_updated_by := -1;
346 END IF;
347 x_last_update_login := fnd_global.login_id;
348 IF (x_last_update_login IS NULL) THEN
349 x_last_update_login := -1;
350 END IF;
351 ELSE
352 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
353 igs_ge_msg_stack.add;
354 app_exception.raise_exception;
355 END IF;
356
357 SELECT igs_fi_bal_ex_c_typs_s.NEXTVAL
358 INTO x_bal_exc_credit_type_id
359 FROM dual;
360
361 before_dml(
362 p_action => 'INSERT',
363 x_rowid => x_rowid,
364 x_bal_exc_credit_type_id => x_bal_exc_credit_type_id,
365 x_balance_rule_id => x_balance_rule_id,
366 x_credit_type_id => x_credit_type_id,
367 x_creation_date => x_last_update_date,
368 x_created_by => x_last_updated_by,
369 x_last_update_date => x_last_update_date,
370 x_last_updated_by => x_last_updated_by,
371 x_last_update_login => x_last_update_login
372 );
373
374 INSERT INTO igs_fi_bal_ex_c_typs (
375 bal_exc_credit_type_id,
376 balance_rule_id,
377 credit_type_id,
378 creation_date,
379 created_by,
380 last_update_date,
381 last_updated_by,
382 last_update_login
383 ) VALUES (
384 new_references.bal_exc_credit_type_id,
385 new_references.balance_rule_id,
386 new_references.credit_type_id,
387 x_last_update_date,
388 x_last_updated_by,
389 x_last_update_date,
390 x_last_updated_by,
391 x_last_update_login
392 );
393
394 OPEN c;
395 FETCH c INTO x_rowid;
396 IF (c%NOTFOUND) THEN
397 CLOSE c;
398 RAISE NO_DATA_FOUND;
399 END IF;
400 CLOSE c;
401
402 END insert_row;
403
404
405 PROCEDURE lock_row (
406 x_rowid IN VARCHAR2,
407 x_bal_exc_credit_type_id IN NUMBER,
408 x_balance_rule_id IN NUMBER,
409 x_credit_type_id IN NUMBER
410 ) AS
411 /*
412 || Created By : BDEVARAK
413 || Created On : 26-APR-2001
414 || Purpose : Handles the LOCK mechanism for the table.
415 || Known limitations, enhancements or remarks :
416 || Change History :
417 || Who When What
418 || (reverse chronological order - newest change first)
419 || smadathi 10-Apr-2002 Bug 2289191. Enabled_flag column reference removed.
420 */
421 CURSOR c1 IS
422 SELECT
423 balance_rule_id,
424 credit_type_id
425 FROM igs_fi_bal_ex_c_typs
426 WHERE rowid = x_rowid
427 FOR UPDATE NOWAIT;
428
429 tlinfo c1%ROWTYPE;
430
431 BEGIN
432
433 OPEN c1;
434 FETCH c1 INTO tlinfo;
435 IF (c1%notfound) THEN
436 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
437 igs_ge_msg_stack.add;
438 CLOSE c1;
439 app_exception.raise_exception;
440 RETURN;
441 END IF;
442 CLOSE c1;
443
444 IF (
445 (tlinfo.balance_rule_id = x_balance_rule_id)
446 AND (tlinfo.credit_type_id = x_credit_type_id)
447 ) THEN
448 NULL;
449 ELSE
450 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
451 igs_ge_msg_stack.add;
452 app_exception.raise_exception;
453 END IF;
454
455 RETURN;
456
457 END lock_row;
458
459
460 PROCEDURE update_row (
461 x_rowid IN VARCHAR2,
462 x_bal_exc_credit_type_id IN NUMBER,
463 x_balance_rule_id IN NUMBER,
464 x_credit_type_id IN NUMBER,
465 x_mode IN VARCHAR2 DEFAULT 'R'
466 ) AS
467 /*
468 || Created By : BDEVARAK
469 || Created On : 26-APR-2001
470 || Purpose : Handles the UPDATE DML logic for the table.
471 || Known limitations, enhancements or remarks :
472 || Change History :
473 || Who When What
474 || (reverse chronological order - newest change first)
475 || smadathi 10-Apr-2002 Bug 2289191. Enabled_flag column reference removed.
476 */
477 x_last_update_date DATE ;
478 x_last_updated_by NUMBER;
479 x_last_update_login NUMBER;
480
481 BEGIN
482
483 x_last_update_date := SYSDATE;
484 IF (X_MODE = 'I') THEN
485 x_last_updated_by := 1;
486 x_last_update_login := 0;
487 ELSIF (x_mode = 'R') THEN
488 x_last_updated_by := fnd_global.user_id;
489 IF x_last_updated_by IS NULL THEN
490 x_last_updated_by := -1;
491 END IF;
492 x_last_update_login := fnd_global.login_id;
493 IF (x_last_update_login IS NULL) THEN
494 x_last_update_login := -1;
495 END IF;
496 ELSE
497 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
498 igs_ge_msg_stack.add;
499 app_exception.raise_exception;
500 END IF;
501
502 before_dml(
503 p_action => 'UPDATE',
504 x_rowid => x_rowid,
505 x_bal_exc_credit_type_id => x_bal_exc_credit_type_id,
506 x_balance_rule_id => x_balance_rule_id,
507 x_credit_type_id => x_credit_type_id,
508 x_creation_date => x_last_update_date,
509 x_created_by => x_last_updated_by,
510 x_last_update_date => x_last_update_date,
511 x_last_updated_by => x_last_updated_by,
512 x_last_update_login => x_last_update_login
513 );
514
515 UPDATE igs_fi_bal_ex_c_typs
516 SET
517 balance_rule_id = new_references.balance_rule_id,
518 credit_type_id = new_references.credit_type_id,
519 last_update_date = x_last_update_date,
520 last_updated_by = x_last_updated_by,
521 last_update_login = x_last_update_login
522 WHERE rowid = x_rowid;
523
524 IF (SQL%NOTFOUND) THEN
525 RAISE NO_DATA_FOUND;
526 END IF;
527
528 END update_row;
529
530
531 PROCEDURE add_row (
532 x_rowid IN OUT NOCOPY VARCHAR2,
533 x_bal_exc_credit_type_id IN OUT NOCOPY NUMBER,
534 x_balance_rule_id IN NUMBER,
535 x_credit_type_id IN NUMBER,
536 x_mode IN VARCHAR2 DEFAULT 'R'
537 ) AS
538 /*
539 || Created By : BDEVARAK
540 || Created On : 26-APR-2001
541 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
542 || Known limitations, enhancements or remarks :
543 || Change History :
544 || Who When What
545 || (reverse chronological order - newest change first)
546 || smadathi 10-Apr-2002 Bug 2289191. Enabled_flag column reference removed.
547 */
548 CURSOR c1 IS
549 SELECT rowid
550 FROM igs_fi_bal_ex_c_typs
551 WHERE bal_exc_credit_type_id = x_bal_exc_credit_type_id;
552
553 BEGIN
554
555 OPEN c1;
556 FETCH c1 INTO x_rowid;
557 IF (c1%NOTFOUND) THEN
558 CLOSE c1;
559
560 insert_row (
561 x_rowid,
562 x_bal_exc_credit_type_id,
563 x_balance_rule_id,
564 x_credit_type_id,
565 x_mode
566 );
567 RETURN;
568 END IF;
569 CLOSE c1;
570
571 update_row (
572 x_rowid,
573 x_bal_exc_credit_type_id,
574 x_balance_rule_id,
575 x_credit_type_id,
576 x_mode
577 );
578
579 END add_row;
580
581
582 PROCEDURE delete_row (
583 x_rowid IN VARCHAR2
584 ) AS
585 /*
586 || Created By : BDEVARAK
587 || Created On : 26-APR-2001
588 || Purpose : Handles the DELETE DML logic for the table.
589 || Known limitations, enhancements or remarks :
590 || Change History :
591 || Who When What
592 || (reverse chronological order - newest change first)
593 */
594 BEGIN
595
596 before_dml (
597 p_action => 'DELETE',
598 x_rowid => x_rowid
599 );
600
601 DELETE FROM igs_fi_bal_ex_c_typs
602 WHERE rowid = x_rowid;
603
604 IF (SQL%NOTFOUND) THEN
605 RAISE NO_DATA_FOUND;
606 END IF;
607
608 END delete_row;
609
610
611 END igs_fi_bal_ex_c_typs_pkg;
[Home] [Help]