[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_BALANCE_RULES_PKG
Source
1 PACKAGE BODY igs_fi_balance_rules_pkg AS
2 /* $Header: IGSSI95B.pls 115.9 2003/02/14 05:34:10 pathipat ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_balance_rules%ROWTYPE;
6 new_references igs_fi_balance_rules%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_balance_rule_id IN NUMBER ,
12 x_balance_name IN VARCHAR2,
13 x_version_number IN NUMBER ,
14 x_last_conversion_date IN DATE ,
15 x_creation_date IN DATE ,
16 x_created_by IN NUMBER ,
17 x_last_update_date IN DATE ,
18 x_last_updated_by IN NUMBER ,
19 x_last_update_login IN NUMBER
20 ) AS
21 /*
22 || Created By : BDEVARAK
23 || Created On : 26-APR-2001
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 || smadathi 30-sep-2002 Bug 2562745. All references to columns effective_start_date,effective_end_date,
30 || exclude_txn_date_low,exclude_txn_date_high,exclude_eff_date_low,exclude_eff_date_high
31 || removed. Column last_conversion_date added newly
32 || vvutukur 24-Sep-2002 Enh#2564643.Removed DEFAULT clause from parameters as gscc fix.
33 */
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_FI_BALANCE_RULES
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 OPEN cur_old_ref_values;
47 FETCH cur_old_ref_values INTO old_references;
48 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
49 CLOSE cur_old_ref_values;
50 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
51 igs_ge_msg_stack.add;
52 app_exception.raise_exception;
53 RETURN;
54 END IF;
55 CLOSE cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.balance_rule_id := x_balance_rule_id;
59 new_references.balance_name := x_balance_name;
60 new_references.version_number := x_version_number;
61 new_references.last_conversion_date := x_last_conversion_date;
62
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END set_column_values;
76
77 PROCEDURE check_constraints (
78 column_name IN VARCHAR2,
79 column_value IN VARCHAR2
80 )AS
81 /*----------------------------------------------------------------------------
82 || Created By : vvutukur
83 || Created On : 05/05/2002
84 || Purpose : To prevent defining exclusion rules for Standard Balances for
85 || bug:2329042
86 || Known limitations, enhancements or remarks :
87 || Change History :
88 || Who When What
89 || (reverse chronological order - newest change first)
90 || smadathi 30-sep-2002 Bug 2562745. Included constraint conditions for
91 || OTHER and INSTALLMENT balance types
92 || vvutukur 24-Sep-2002 Enh#2564643.Removed DEFAULT clause from parameters as gscc fix.
93 ----------------------------------------------------------------------------*/
94 BEGIN
95 IF column_name is NULL THEN
96 NULL;
97 ELSIF upper(column_name) = 'BALANCE_NAME' THEN
98 new_references.balance_name := column_value;
99 END IF;
100
101 IF (UPPER(column_name) = 'BALANCE_NAME' OR
102 column_name is NULL) THEN
103 IF new_references.balance_name IN ('STANDARD','OTHER','INSTALLMENT') THEN
104 fnd_message.set_name ('IGS', 'IGS_GE_INVALID_VALUE');
105 IGS_GE_MSG_STACK.ADD;
106 APP_EXCEPTION.RAISE_EXCEPTION;
107 END IF;
108 END IF;
109
110 END check_constraints;
111
112 PROCEDURE check_parent_existance AS
113 /*
114 || Created By : BDEVARAK
115 || Created On : 26-APR-2001
116 || Purpose : Checks for the existance of Parent records.
117 || Known limitations, enhancements or remarks :
118 || Change History :
119 || Who When What
120 || (reverse chronological order - newest change first)
121 */
122 BEGIN
123
124 IF (((old_references.balance_name = new_references.balance_name)) OR
125 ((new_references.balance_name IS NULL))) THEN
126 NULL;
127 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
128 'IGS_FI_BALANCE_TYPE',
129 new_references.balance_name
130 )THEN
131 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 END IF;
135
136 END check_parent_existance;
137
138 FUNCTION get_pk_for_validation (
139 x_balance_rule_id IN NUMBER
140 ) RETURN BOOLEAN AS
141 /*
142 || Created By : BDEVARAK
143 || Created On : 26-APR-2001
144 || Purpose : Validates the Primary Key of the table.
145 || Known limitations, enhancements or remarks :
146 || Change History :
147 || Who When What
148 || pathipat 14-Feb-2003 Enh 2747325 - Locking Issues build
149 || Removed FOR UPDATE NOWAIT clause
150 || (reverse chronological order - newest change first)
151 */
152
153 CURSOR cur_rowid IS
154 SELECT rowid
155 FROM igs_fi_balance_rules
156 WHERE balance_rule_id = x_balance_rule_id ;
157
158 lv_rowid cur_rowid%RowType;
159
160 BEGIN
161
162 OPEN cur_rowid;
163 FETCH cur_rowid INTO lv_rowid;
164 IF (cur_rowid%FOUND) THEN
165 CLOSE cur_rowid;
166 RETURN(TRUE);
167 ELSE
168 CLOSE cur_rowid;
169 RETURN(FALSE);
170 END IF;
171
172 END get_pk_for_validation;
173
174
175 PROCEDURE before_dml (
176 p_action IN VARCHAR2,
177 x_rowid IN VARCHAR2,
178 x_balance_rule_id IN NUMBER ,
179 x_balance_name IN VARCHAR2,
180 x_version_number IN NUMBER ,
181 x_last_conversion_date IN DATE ,
182 x_creation_date IN DATE ,
183 x_created_by IN NUMBER ,
184 x_last_update_date IN DATE ,
185 x_last_updated_by IN NUMBER ,
186 x_last_update_login IN NUMBER
187 ) AS
188 /*
189 || Created By : BDEVARAK
190 || Created On : 26-APR-2001
191 || Purpose : Initialises the columns, Checks Constraints, Calls the
192 || Trigger Handlers for the table, before any DML operation.
193 || Known limitations, enhancements or remarks :
194 || Change History :
195 || Who When What
196 || pathipat 14-Feb-2003 Enh 2747325 - Locking Issues Build
197 || Removed code for p_action = DELETE and VALIDATE_DELETE
198 || vvutukur 03-may-2002 called check_constrainsts for bug:2329042.
199 || (reverse chronological order - newest change first)
200 || smadathi 30-sep-2002 Bug 2562745. All references to columns effective_start_date,effective_end_date,
201 || exclude_txn_date_low,exclude_txn_date_high,exclude_eff_date_low,exclude_eff_date_high
202 || removed. Column last_conversion_date added newly
203 || vvutukur 24-Sep-2002 Enh#2564643.Removed DEFAULT clause from parameters as gscc fix.
204 */
205 BEGIN
206
207 set_column_values (
208 p_action,
209 x_rowid,
210 x_balance_rule_id,
211 x_balance_name,
212 x_version_number,
213 x_last_conversion_date,
214 x_creation_date,
215 x_created_by,
216 x_last_update_date,
217 x_last_updated_by,
218 x_last_update_login
219 );
220
221 IF (p_action = 'INSERT') THEN
222 -- Call all the procedures related to Before Insert.
223 IF ( get_pk_for_validation(
224 new_references.balance_rule_id
225 )
226 ) THEN
227 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
228 igs_ge_msg_stack.add;
229 app_exception.raise_exception;
230 END IF;
231 check_parent_existance;
232 check_constraints;
233 ELSIF (p_action = 'UPDATE') THEN
234 -- Call all the procedures related to Before Update.
235 check_parent_existance;
236 check_constraints;
237 ELSIF (p_action = 'VALIDATE_INSERT') THEN
238 -- Call all the procedures related to Before Insert.
239 IF ( get_pk_for_validation (
240 new_references.balance_rule_id
241 )
242 ) THEN
243 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
244 igs_ge_msg_stack.add;
245 app_exception.raise_exception;
246 END IF;
247 check_constraints;
248 END IF;
249
250 END before_dml;
251
252
253 PROCEDURE insert_row (
254 x_rowid IN OUT NOCOPY VARCHAR2,
255 x_balance_rule_id IN OUT NOCOPY NUMBER,
256 x_balance_name IN VARCHAR2,
257 x_version_number IN NUMBER,
258 x_last_conversion_date IN DATE,
259 x_mode IN VARCHAR2
260 ) AS
261 /*
262 || Created By : BDEVARAK
263 || Created On : 26-APR-2001
264 || Purpose : Handles the INSERT DML logic for the table.
265 || Known limitations, enhancements or remarks :
266 || Change History :
267 || Who When What
268 || (reverse chronological order - newest change first)
269 || smadathi 30-sep-2002 Bug 2562745. All references to columns effective_start_date,effective_end_date,
270 || exclude_txn_date_low,exclude_txn_date_high,exclude_eff_date_low,exclude_eff_date_high
271 || removed. Column last_conversion_date added newly
272 || vvutukur 24-Sep-2002 Enh#2564643.Removed DEFAULT clause from parameters as gscc fix.
273 */
274 CURSOR c IS
275 SELECT rowid
276 FROM igs_fi_balance_rules
277 WHERE balance_rule_id = x_balance_rule_id;
278
279 x_last_update_date DATE;
280 x_last_updated_by NUMBER;
281 x_last_update_login NUMBER;
282
283 BEGIN
284
285 x_last_update_date := SYSDATE;
286 IF (x_mode = 'I') THEN
287 x_last_updated_by := 1;
288 x_last_update_login := 0;
289 ELSIF (x_mode = 'R') THEN
290 x_last_updated_by := fnd_global.user_id;
291 IF (x_last_updated_by IS NULL) THEN
292 x_last_updated_by := -1;
293 END IF;
294 x_last_update_login := fnd_global.login_id;
295 IF (x_last_update_login IS NULL) THEN
296 x_last_update_login := -1;
297 END IF;
298 ELSE
299 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
300 igs_ge_msg_stack.add;
301 app_exception.raise_exception;
302 END IF;
303
304 SELECT igs_fi_balance_rules_s.NEXTVAL
305 INTO x_balance_rule_id
306 FROM dual;
307
308 before_dml(
309 p_action => 'INSERT',
310 x_rowid => x_rowid,
311 x_balance_rule_id => x_balance_rule_id,
312 x_balance_name => x_balance_name,
313 x_version_number => x_version_number,
314 x_last_conversion_date => x_last_conversion_date,
315 x_creation_date => x_last_update_date,
316 x_created_by => x_last_updated_by,
317 x_last_update_date => x_last_update_date,
318 x_last_updated_by => x_last_updated_by,
319 x_last_update_login => x_last_update_login
320 );
321
322 INSERT INTO igs_fi_balance_rules (
323 balance_rule_id,
324 balance_name,
325 version_number,
326 last_conversion_date,
327 creation_date,
328 created_by,
329 last_update_date,
330 last_updated_by,
331 last_update_login
332 ) VALUES (
333 new_references.balance_rule_id,
334 new_references.balance_name,
335 new_references.version_number,
336 new_references.last_conversion_date,
337 x_last_update_date,
338 x_last_updated_by,
339 x_last_update_date,
340 x_last_updated_by,
341 x_last_update_login
342 );
343
344 OPEN c;
345 FETCH c INTO x_rowid;
346 IF (c%NOTFOUND) THEN
347 CLOSE c;
348 RAISE NO_DATA_FOUND;
349 END IF;
350 CLOSE c;
351
352 END insert_row;
353
354
355 PROCEDURE lock_row (
356 x_rowid IN VARCHAR2,
357 x_balance_rule_id IN NUMBER,
358 x_balance_name IN VARCHAR2,
359 x_version_number IN NUMBER,
360 x_last_conversion_date IN DATE
361 ) AS
362 /*
363 || Created By : BDEVARAK
364 || Created On : 26-APR-2001
365 || Purpose : Handles the LOCK mechanism for the table.
366 || Known limitations, enhancements or remarks :
367 || Change History :
368 || Who When What
369 || smadathi 30-sep-2002 Bug 2562745. All references to columns effective_start_date,effective_end_date,
370 || exclude_txn_date_low,exclude_txn_date_high,exclude_eff_date_low,exclude_eff_date_high
371 || removed. Column last_conversion_date added newly
372 || (reverse chronological order - newest change first)
373 */
374 CURSOR c1 IS
375 SELECT
376 balance_name,
377 version_number,
378 last_conversion_date
379 FROM igs_fi_balance_rules
380 WHERE rowid = x_rowid
381 FOR UPDATE NOWAIT;
382
383 tlinfo c1%ROWTYPE;
384
385 BEGIN
386
387 OPEN c1;
388 FETCH c1 INTO tlinfo;
389 IF (c1%notfound) THEN
390 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
391 igs_ge_msg_stack.add;
392 CLOSE c1;
393 app_exception.raise_exception;
394 RETURN;
395 END IF;
399 (tlinfo.balance_name = x_balance_name)
396 CLOSE c1;
397
398 IF (
400 AND (tlinfo.version_number = x_version_number)
401 AND ((tlinfo.last_conversion_date = x_last_conversion_date) OR ((tlinfo.last_conversion_date IS NULL) AND (X_last_conversion_date IS NULL)))
402 ) THEN
403 NULL;
404 ELSE
405 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
406 igs_ge_msg_stack.add;
407 app_exception.raise_exception;
408 END IF;
409
410 RETURN;
411
412 END lock_row;
413
414
415 PROCEDURE update_row (
416 x_rowid IN VARCHAR2,
417 x_balance_rule_id IN NUMBER,
418 x_balance_name IN VARCHAR2,
419 x_version_number IN NUMBER,
420 x_last_conversion_date IN DATE,
421 x_mode IN VARCHAR2
422 ) AS
423 /*
424 || Created By : BDEVARAK
425 || Created On : 26-APR-2001
426 || Purpose : Handles the UPDATE DML logic for the table.
427 || Known limitations, enhancements or remarks :
428 || Change History :
429 || Who When What
430 || (reverse chronological order - newest change first)
431 || smadathi 30-sep-2002 Bug 2562745. All references to columns effective_start_date,effective_end_date,
432 || exclude_txn_date_low,exclude_txn_date_high,exclude_eff_date_low,exclude_eff_date_high
433 || removed. Column last_conversion_date added newly
434 || vvutukur 24-Sep-2002 Enh#2564643.Removed DEFAULT clause from parameters as gscc fix.
435 */
436 x_last_update_date DATE ;
437 x_last_updated_by NUMBER;
438 x_last_update_login NUMBER;
439
440 BEGIN
441
442 x_last_update_date := SYSDATE;
443 IF (X_MODE = 'I') THEN
444 x_last_updated_by := 1;
445 x_last_update_login := 0;
446 ELSIF (x_mode = 'R') THEN
447 x_last_updated_by := fnd_global.user_id;
448 IF x_last_updated_by IS NULL THEN
449 x_last_updated_by := -1;
450 END IF;
451 x_last_update_login := fnd_global.login_id;
452 IF (x_last_update_login IS NULL) THEN
453 x_last_update_login := -1;
454 END IF;
455 ELSE
456 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
457 igs_ge_msg_stack.add;
458 app_exception.raise_exception;
459 END IF;
460
461 before_dml(
462 p_action => 'UPDATE',
463 x_rowid => x_rowid,
464 x_balance_rule_id => x_balance_rule_id,
465 x_balance_name => x_balance_name,
466 x_version_number => x_version_number,
467 x_last_conversion_date => x_last_conversion_date,
468 x_creation_date => x_last_update_date,
469 x_created_by => x_last_updated_by,
470 x_last_update_date => x_last_update_date,
471 x_last_updated_by => x_last_updated_by,
472 x_last_update_login => x_last_update_login
473 );
474
475 UPDATE igs_fi_balance_rules
476 SET
477 balance_name = new_references.balance_name,
478 version_number = new_references.version_number,
479 last_conversion_date = new_references.last_conversion_date,
480 last_update_date = x_last_update_date,
481 last_updated_by = x_last_updated_by,
482 last_update_login = x_last_update_login
483 WHERE rowid = x_rowid;
484
485 IF (SQL%NOTFOUND) THEN
486 RAISE NO_DATA_FOUND;
487 END IF;
488
489 END update_row;
490
491
492 PROCEDURE add_row (
493 x_rowid IN OUT NOCOPY VARCHAR2,
494 x_balance_rule_id IN OUT NOCOPY NUMBER,
495 x_balance_name IN VARCHAR2,
496 x_version_number IN NUMBER,
497 x_last_conversion_date IN DATE,
498 x_mode IN VARCHAR2
499 ) AS
500 /*
501 || Created By : BDEVARAK
502 || Created On : 26-APR-2001
503 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
504 || Known limitations, enhancements or remarks :
505 || Change History :
506 || Who When What
507 || (reverse chronological order - newest change first)
508 || smadathi 30-sep-2002 Bug 2562745. All references to columns effective_start_date,effective_end_date,
509 || exclude_txn_date_low,exclude_txn_date_high,exclude_eff_date_low,exclude_eff_date_high
510 || removed. Column last_conversion_date added newly
511 || vvutukur 24-Sep-2002 Enh#2564643.Removed DEFAULT clause from parameters as gscc fix.
515 FROM igs_fi_balance_rules
512 */
513 CURSOR c1 IS
514 SELECT rowid
516 WHERE balance_rule_id = x_balance_rule_id;
517
518 BEGIN
519
520 OPEN c1;
521 FETCH c1 INTO x_rowid;
522 IF (c1%NOTFOUND) THEN
523 CLOSE c1;
524
525 insert_row (
526 x_rowid,
527 x_balance_rule_id,
528 x_balance_name,
529 x_version_number,
530 x_last_conversion_date,
531 x_mode
532 );
533 RETURN;
534 END IF;
535 CLOSE c1;
536
537 update_row (
538 x_rowid,
539 x_balance_rule_id,
540 x_balance_name,
541 x_version_number,
542 x_last_conversion_date,
543 x_mode
544 );
545
546 END add_row;
547
548 END igs_fi_balance_rules_pkg;