1 PACKAGE BODY igs_en_disc_dl_cons_pkg AS
2 /* $Header: IGSEI44B.pls 115.6 2002/11/28 23:43:05 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_disc_dl_cons%ROWTYPE;
6 new_references igs_en_disc_dl_cons%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_disc_dl_cons_id IN NUMBER DEFAULT NULL,
12 x_offset_cons_type_cd IN VARCHAR2 DEFAULT NULL,
13 x_constraint_condition IN VARCHAR2 DEFAULT NULL,
14 x_constraint_resolution IN NUMBER DEFAULT NULL,
15 x_non_std_disc_dl_stp_id IN NUMBER 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 : [email protected]
24 || Created On : 30-MAR-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 IGS_EN_DISC_DL_CONS
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.disc_dl_cons_id := x_disc_dl_cons_id;
56 new_references.offset_cons_type_cd := x_offset_cons_type_cd;
57 new_references.constraint_condition := x_constraint_condition;
58 new_references.constraint_resolution := x_constraint_resolution;
59 new_references.non_std_disc_dl_stp_id := x_non_std_disc_dl_stp_id;
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 : [email protected]
79 || Created On : 30-MAR-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.offset_cons_type_cd,
90 new_references.non_std_disc_dl_stp_id
91 )
92 ) THEN
93 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
94 igs_ge_msg_stack.add;
95 app_exception.raise_exception;
96 END IF;
97
98 END check_uniqueness;
99
100
101 PROCEDURE check_parent_existance AS
102 /*
103 || Created By : [email protected]
104 || Created On : 30-MAR-2001
105 || Purpose : Checks for the existance of Parent records.
106 || Known limitations, enhancements or remarks :
107 || Change History :
108 || Who When What
109 || (reverse chronological order - newest change first)
110 */
111 BEGIN
112
113 IF (((old_references.non_std_disc_dl_stp_id = new_references.non_std_disc_dl_stp_id)) OR
114 ((new_references.non_std_disc_dl_stp_id IS NULL))) THEN
115 NULL;
116 ELSIF NOT igs_en_nsd_dlstp_pkg.get_pk_for_validation (
117 new_references.non_std_disc_dl_stp_id
118 ) THEN
119 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
120 igs_ge_msg_stack.add;
121 app_exception.raise_exception;
122 END IF;
123
124 END check_parent_existance;
125
126
127 FUNCTION get_pk_for_validation (
128 x_disc_dl_cons_id IN NUMBER
129 ) RETURN BOOLEAN AS
130 /*
131 || Created By : [email protected]
132 || Created On : 30-MAR-2001
133 || Purpose : Validates the Primary Key of the table.
134 || Known limitations, enhancements or remarks :
135 || Change History :
136 || Who When What
137 || (reverse chronological order - newest change first)
138 */
139 CURSOR cur_rowid IS
140 SELECT rowid
141 FROM igs_en_disc_dl_cons
142 WHERE disc_dl_cons_id = x_disc_dl_cons_id
143 FOR UPDATE NOWAIT;
144
145 lv_rowid cur_rowid%RowType;
146
147 BEGIN
148
149 OPEN cur_rowid;
150 FETCH cur_rowid INTO lv_rowid;
151 IF (cur_rowid%FOUND) THEN
152 CLOSE cur_rowid;
153 RETURN(TRUE);
154 ELSE
155 CLOSE cur_rowid;
156 RETURN(FALSE);
157 END IF;
158
159 END get_pk_for_validation;
160
161
162 FUNCTION get_uk_for_validation (
163 x_offset_cons_type_cd IN VARCHAR2,
164 x_non_std_disc_dl_stp_id IN NUMBER
165 ) RETURN BOOLEAN AS
166 /*
167 || Created By : [email protected]
168 || Created On : 30-MAR-2001
169 || Purpose : Validates the Unique Keys of the table.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 CURSOR cur_rowid IS
176 SELECT rowid
177 FROM igs_en_disc_dl_cons
178 WHERE offset_cons_type_cd = x_offset_cons_type_cd
179 AND non_std_disc_dl_stp_id = x_non_std_disc_dl_stp_id
180 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
181
182 lv_rowid cur_rowid%RowType;
183
184 BEGIN
185
186 OPEN cur_rowid;
187 FETCH cur_rowid INTO lv_rowid;
188 IF (cur_rowid%FOUND) THEN
189 CLOSE cur_rowid;
190 RETURN (true);
191 ELSE
192 CLOSE cur_rowid;
193 RETURN(FALSE);
194 END IF;
195
196 END get_uk_for_validation ;
197
198
199 PROCEDURE get_fk_igs_en_nsd_dlstp_all (
200 x_non_std_disc_dl_stp_id IN NUMBER
201 ) AS
202 /*
203 || Created By : [email protected]
204 || Created On : 30-MAR-2001
205 || Purpose : Validates the Foreign Keys for the table.
206 || Known limitations, enhancements or remarks :
207 || Change History :
208 || Who When What
209 || (reverse chronological order - newest change first)
210 */
211 CURSOR cur_rowid IS
212 SELECT rowid
213 FROM igs_en_disc_dl_cons
214 WHERE ((non_std_disc_dl_stp_id = x_non_std_disc_dl_stp_id));
215
216 lv_rowid cur_rowid%RowType;
217
218 BEGIN
219
220 OPEN cur_rowid;
221 FETCH cur_rowid INTO lv_rowid;
222 IF (cur_rowid%FOUND) THEN
223 CLOSE cur_rowid;
224 fnd_message.set_name ('IGS', 'IGS_EN_DDC_NDDS_FK');
225 igs_ge_msg_stack.add;
226 app_exception.raise_exception;
227 RETURN;
228 END IF;
229 CLOSE cur_rowid;
230
231 END get_fk_igs_en_nsd_dlstp_all;
232
233
234 PROCEDURE before_dml (
235 p_action IN VARCHAR2,
236 x_rowid IN VARCHAR2 DEFAULT NULL,
237 x_disc_dl_cons_id IN NUMBER DEFAULT NULL,
238 x_offset_cons_type_cd IN VARCHAR2 DEFAULT NULL,
239 x_constraint_condition IN VARCHAR2 DEFAULT NULL,
240 x_constraint_resolution IN NUMBER DEFAULT NULL,
241 x_non_std_disc_dl_stp_id IN NUMBER DEFAULT NULL,
242 x_creation_date IN DATE DEFAULT NULL,
243 x_created_by IN NUMBER DEFAULT NULL,
244 x_last_update_date IN DATE DEFAULT NULL,
245 x_last_updated_by IN NUMBER DEFAULT NULL,
246 x_last_update_login IN NUMBER DEFAULT NULL
247 ) AS
248 /*
249 || Created By : [email protected]
250 || Created On : 30-MAR-2001
251 || Purpose : Initialises the columns, Checks Constraints, Calls the
252 || Trigger Handlers for the table, before any DML operation.
253 || Known limitations, enhancements or remarks :
254 || Change History :
255 || Who When What
256 || (reverse chronological order - newest change first)
257 */
258 BEGIN
259
260 set_column_values (
261 p_action,
262 x_rowid,
263 x_disc_dl_cons_id,
264 x_offset_cons_type_cd,
268 x_creation_date,
265 x_constraint_condition,
266 x_constraint_resolution,
267 x_non_std_disc_dl_stp_id,
269 x_created_by,
270 x_last_update_date,
271 x_last_updated_by,
272 x_last_update_login
273 );
274
275 IF (p_action = 'INSERT') THEN
276 -- Call all the procedures related to Before Insert.
277 IF ( get_pk_for_validation(
278 new_references.disc_dl_cons_id
279 )
280 ) THEN
281 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
282 igs_ge_msg_stack.add;
283 app_exception.raise_exception;
284 END IF;
285 check_uniqueness;
286 check_parent_existance;
287 ELSIF (p_action = 'UPDATE') THEN
288 -- Call all the procedures related to Before Update.
289 check_uniqueness;
290 check_parent_existance;
291 ELSIF (p_action = 'VALIDATE_INSERT') THEN
292 -- Call all the procedures related to Before Insert.
293 IF ( get_pk_for_validation (
294 new_references.disc_dl_cons_id
295 )
296 ) THEN
297 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
298 igs_ge_msg_stack.add;
299 app_exception.raise_exception;
300 END IF;
301 check_uniqueness;
302 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
303 check_uniqueness;
304 END IF;
305
306 END before_dml;
307
308
309 PROCEDURE insert_row (
310 x_rowid IN OUT NOCOPY VARCHAR2,
311 x_disc_dl_cons_id IN OUT NOCOPY NUMBER,
312 x_offset_cons_type_cd IN VARCHAR2,
313 x_constraint_condition IN VARCHAR2,
314 x_constraint_resolution IN NUMBER,
315 x_non_std_disc_dl_stp_id IN NUMBER,
316 x_mode IN VARCHAR2 DEFAULT 'R'
317 ) AS
318 /*
319 || Created By : [email protected]
320 || Created On : 30-MAR-2001
321 || Purpose : Handles the INSERT DML logic for the table.
322 || Known limitations, enhancements or remarks :
323 || Change History :
324 || Who When What
325 || (reverse chronological order - newest change first)
326 */
327 CURSOR c IS
328 SELECT rowid
329 FROM igs_en_disc_dl_cons
330 WHERE disc_dl_cons_id = x_disc_dl_cons_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_en_disc_dl_cons_s.NEXTVAL
358 INTO x_disc_dl_cons_id
359 FROM dual;
360
361 before_dml(
362 p_action => 'INSERT',
363 x_rowid => x_rowid,
364 x_disc_dl_cons_id => x_disc_dl_cons_id,
365 x_offset_cons_type_cd => x_offset_cons_type_cd,
366 x_constraint_condition => x_constraint_condition,
367 x_constraint_resolution => x_constraint_resolution,
368 x_non_std_disc_dl_stp_id => x_non_std_disc_dl_stp_id,
369 x_creation_date => x_last_update_date,
370 x_created_by => x_last_updated_by,
371 x_last_update_date => x_last_update_date,
372 x_last_updated_by => x_last_updated_by,
373 x_last_update_login => x_last_update_login
374 );
375
376 INSERT INTO igs_en_disc_dl_cons (
377 disc_dl_cons_id,
378 offset_cons_type_cd,
379 constraint_condition,
380 constraint_resolution,
381 non_std_disc_dl_stp_id,
382 creation_date,
383 created_by,
384 last_update_date,
385 last_updated_by,
386 last_update_login
387 ) VALUES (
391 new_references.constraint_resolution,
388 new_references.disc_dl_cons_id,
389 new_references.offset_cons_type_cd,
390 new_references.constraint_condition,
392 new_references.non_std_disc_dl_stp_id,
393 x_last_update_date,
394 x_last_updated_by,
395 x_last_update_date,
396 x_last_updated_by,
397 x_last_update_login
398 );
399
400 OPEN c;
401 FETCH c INTO x_rowid;
402 IF (c%NOTFOUND) THEN
403 CLOSE c;
404 RAISE NO_DATA_FOUND;
405 END IF;
406 CLOSE c;
407
408 END insert_row;
409
410
411 PROCEDURE lock_row (
412 x_rowid IN VARCHAR2,
413 x_disc_dl_cons_id IN NUMBER,
414 x_offset_cons_type_cd IN VARCHAR2,
415 x_constraint_condition IN VARCHAR2,
416 x_constraint_resolution IN NUMBER,
417 x_non_std_disc_dl_stp_id IN NUMBER
418 ) AS
419 /*
420 || Created By : [email protected]
421 || Created On : 30-MAR-2001
422 || Purpose : Handles the LOCK mechanism for the table.
423 || Known limitations, enhancements or remarks :
424 || Change History :
425 || Who When What
426 || (reverse chronological order - newest change first)
427 */
428 CURSOR c1 IS
429 SELECT
430 offset_cons_type_cd,
431 constraint_condition,
432 constraint_resolution,
433 non_std_disc_dl_stp_id
434 FROM igs_en_disc_dl_cons
435 WHERE rowid = x_rowid
436 FOR UPDATE NOWAIT;
437
438 tlinfo c1%ROWTYPE;
439
440 BEGIN
441
442 OPEN c1;
443 FETCH c1 INTO tlinfo;
444 IF (c1%notfound) THEN
445 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
446 igs_ge_msg_stack.add;
447 CLOSE c1;
448 app_exception.raise_exception;
449 RETURN;
450 END IF;
451 CLOSE c1;
452
453 IF (
454 (tlinfo.offset_cons_type_cd = x_offset_cons_type_cd)
455 AND (tlinfo.constraint_condition = x_constraint_condition)
456 AND (tlinfo.constraint_resolution = x_constraint_resolution)
457 AND (tlinfo.non_std_disc_dl_stp_id = x_non_std_disc_dl_stp_id)
458 ) THEN
459 NULL;
460 ELSE
461 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
462 igs_ge_msg_stack.add;
463 app_exception.raise_exception;
464 END IF;
465
466 RETURN;
467
468 END lock_row;
469
470
471 PROCEDURE update_row (
472 x_rowid IN VARCHAR2,
473 x_disc_dl_cons_id IN NUMBER,
474 x_offset_cons_type_cd IN VARCHAR2,
475 x_constraint_condition IN VARCHAR2,
476 x_constraint_resolution IN NUMBER,
480 /*
477 x_non_std_disc_dl_stp_id IN NUMBER,
478 x_mode IN VARCHAR2 DEFAULT 'R'
479 ) AS
481 || Created By : [email protected]
482 || Created On : 30-MAR-2001
483 || Purpose : Handles the UPDATE DML logic for the table.
484 || Known limitations, enhancements or remarks :
485 || Change History :
486 || Who When What
487 || (reverse chronological order - newest change first)
488 */
489 x_last_update_date DATE ;
490 x_last_updated_by NUMBER;
491 x_last_update_login NUMBER;
492
493 BEGIN
494
495 x_last_update_date := SYSDATE;
496 IF (X_MODE = 'I') THEN
497 x_last_updated_by := 1;
498 x_last_update_login := 0;
499 ELSIF (x_mode = 'R') THEN
500 x_last_updated_by := fnd_global.user_id;
501 IF x_last_updated_by IS NULL THEN
502 x_last_updated_by := -1;
503 END IF;
504 x_last_update_login := fnd_global.login_id;
505 IF (x_last_update_login IS NULL) THEN
506 x_last_update_login := -1;
507 END IF;
508 ELSE
509 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
510 igs_ge_msg_stack.add;
511 app_exception.raise_exception;
512 END IF;
513
514 before_dml(
515 p_action => 'UPDATE',
516 x_rowid => x_rowid,
517 x_disc_dl_cons_id => x_disc_dl_cons_id,
518 x_offset_cons_type_cd => x_offset_cons_type_cd,
519 x_constraint_condition => x_constraint_condition,
520 x_constraint_resolution => x_constraint_resolution,
521 x_non_std_disc_dl_stp_id => x_non_std_disc_dl_stp_id,
522 x_creation_date => x_last_update_date,
523 x_created_by => x_last_updated_by,
524 x_last_update_date => x_last_update_date,
525 x_last_updated_by => x_last_updated_by,
526 x_last_update_login => x_last_update_login
527 );
528
529 UPDATE igs_en_disc_dl_cons
530 SET
531 offset_cons_type_cd = new_references.offset_cons_type_cd,
532 constraint_condition = new_references.constraint_condition,
533 constraint_resolution = new_references.constraint_resolution,
534 non_std_disc_dl_stp_id = new_references.non_std_disc_dl_stp_id,
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_disc_dl_cons_id IN OUT NOCOPY NUMBER,
550 x_offset_cons_type_cd IN VARCHAR2,
551 x_constraint_condition IN VARCHAR2,
552 x_constraint_resolution IN NUMBER,
553 x_non_std_disc_dl_stp_id IN NUMBER,
554 x_mode IN VARCHAR2 DEFAULT 'R'
555 ) AS
556 /*
557 || Created By : [email protected]
558 || Created On : 30-MAR-2001
559 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
560 || Known limitations, enhancements or remarks :
561 || Change History :
562 || Who When What
563 || (reverse chronological order - newest change first)
564 */
565 CURSOR c1 IS
566 SELECT rowid
567 FROM igs_en_disc_dl_cons
568 WHERE disc_dl_cons_id = x_disc_dl_cons_id;
569
570 BEGIN
571
572 OPEN c1;
573 FETCH c1 INTO x_rowid;
574 IF (c1%NOTFOUND) THEN
575 CLOSE c1;
576
577 insert_row (
578 x_rowid,
579 x_disc_dl_cons_id,
580 x_offset_cons_type_cd,
581 x_constraint_condition,
582 x_constraint_resolution,
583 x_non_std_disc_dl_stp_id,
584 x_mode
585 );
586 RETURN;
587 END IF;
588 CLOSE c1;
589
590 update_row (
591 x_rowid,
592 x_disc_dl_cons_id,
593 x_offset_cons_type_cd,
594 x_constraint_condition,
595 x_constraint_resolution,
596 x_non_std_disc_dl_stp_id,
597 x_mode
598 );
599
600 END add_row;
601
602
603 PROCEDURE delete_row (
604 x_rowid IN VARCHAR2
605 ) AS
606 /*
607 || Created By : [email protected]
608 || Created On : 30-MAR-2001
609 || Purpose : Handles the DELETE DML logic for the table.
610 || Known limitations, enhancements or remarks :
611 || Change History :
612 || Who When What
613 || (reverse chronological order - newest change first)
614 */
615 BEGIN
616
617 before_dml (
618 p_action => 'DELETE',
619 x_rowid => x_rowid
620 );
621
622 DELETE FROM igs_en_disc_dl_cons
623 WHERE rowid = x_rowid;
624
625 IF (SQL%NOTFOUND) THEN
626 RAISE NO_DATA_FOUND;
627 END IF;
628
629 END delete_row;
630
631
632 END igs_en_disc_dl_cons_pkg;
[Home] [Help]