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