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