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