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