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