[Home] [Help]
PACKAGE BODY: APPS.IGI_DOS_DOC_TYPES_PKG
Source
1 PACKAGE BODY igi_dos_doc_types_pkg AS
2 /* $Header: igidosob.pls 120.6.12000000.2 2007/06/14 05:47:01 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_doc_types%ROWTYPE;
15 new_references igi_dos_doc_types%ROWTYPE;
16
17 PROCEDURE set_column_values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2 ,
20 x_amount_type IN VARCHAR2 ,
21 x_dossier_id IN NUMBER ,
22 x_dossier_name IN VARCHAR2 ,
23 x_dossier_numbering IN VARCHAR2 ,
24 x_coa_id IN NUMBER ,
25 x_sob_id IN NUMBER ,
26 x_hierarchy_id IN NUMBER ,
27 x_balanced IN VARCHAR2 ,
28 x_dossier_description IN VARCHAR2 ,
29 x_multi_annual IN VARCHAR2 ,
30 -- x_related_dossier IN VARCHAR2 ,
31 x_related_dossier_dsp IN VARCHAR2 ,
32 x_dossier_relationship IN VARCHAR2 ,
33 x_dossier_relationship_dsp IN VARCHAR2 ,
34 x_dossier_status IN VARCHAR2 ,
35 x_workflow_name IN VARCHAR2 ,
36 x_retired_flag IN VARCHAR2 ,
37 x_attribute_category IN VARCHAR2 ,
38 x_attribute1 IN VARCHAR2 ,
39 x_attribute2 IN VARCHAR2 ,
40 x_attribute3 IN VARCHAR2 ,
41 x_attribute4 IN VARCHAR2 ,
42 x_attribute5 IN VARCHAR2 ,
43 x_attribute6 IN VARCHAR2 ,
44 x_attribute7 IN VARCHAR2 ,
45 x_attribute8 IN VARCHAR2 ,
46 x_attribute9 IN VARCHAR2 ,
47 x_attribute10 IN VARCHAR2 ,
48 x_attribute11 IN VARCHAR2 ,
49 x_attribute12 IN VARCHAR2 ,
50 x_attribute13 IN VARCHAR2 ,
51 x_attribute14 IN VARCHAR2 ,
52 x_attribute15 IN VARCHAR2 ,
53 x_related_dossier_id IN NUMBER ,
54 x_creation_date IN DATE ,
55 x_created_by IN NUMBER ,
56 x_last_update_date IN DATE ,
57 x_last_updated_by IN NUMBER ,
58 x_last_update_login IN NUMBER ) AS
59 /*
60 || Created By : [email protected]
61 || Created On : 18-APR-2002
62 || Purpose : Initialises the Old and New references for the columns of the table.
63 || Known limitations, enhancements or remarks :
64 || Change History :
65 || Who When What
66 || (reverse chronological order - newest change first)
67 */
68
69 CURSOR cur_old_ref_values IS
70 SELECT *
71 FROM igi_dos_doc_types
72 WHERE rowid = x_rowid;
73
74 BEGIN
75
76 l_rowid := x_rowid;
77
78 -- Code for setting the Old and New Reference Values.
79 -- Populate Old Values.
80 OPEN cur_old_ref_values;
81 FETCH cur_old_ref_values INTO old_references;
82 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
83 CLOSE cur_old_ref_values;
84 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
85 -- bug 3199481, start block
86 IF (l_unexp_level >= l_debug_level) THEN
87 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.set_column_values.Msg1',FALSE);
88 END IF;
89 -- bug 3199481, end block
90 app_exception.raise_exception;
91 RETURN;
92 END IF;
93 CLOSE cur_old_ref_values;
94
95 -- Populate New Values.
96 new_references.amount_type := x_amount_type;
97 new_references.dossier_id := x_dossier_id;
98 new_references.dossier_name := x_dossier_name;
99 new_references.dossier_numbering := x_dossier_numbering;
100 new_references.coa_id := x_coa_id;
101 new_references.sob_id := x_sob_id;
102 new_references.hierarchy_id := x_hierarchy_id;
103 new_references.balanced := x_balanced;
104 new_references.dossier_description := x_dossier_description;
105 new_references.multi_annual := x_multi_annual;
106 -- new_references.related_dossier := x_related_dossier;
107 new_references.related_dossier_dsp := x_related_dossier_dsp;
108 new_references.dossier_relationship := x_dossier_relationship;
109 new_references.dossier_relationship_dsp := x_dossier_relationship_dsp;
110 new_references.dossier_status := x_dossier_status;
111 new_references.workflow_name := x_workflow_name;
112 new_references.retired_flag := x_retired_flag;
113 new_references.attribute_category := x_attribute_category;
114 new_references.attribute1 := x_attribute1;
115 new_references.attribute2 := x_attribute2;
116 new_references.attribute3 := x_attribute3;
117 new_references.attribute4 := x_attribute4;
118 new_references.attribute5 := x_attribute5;
119 new_references.attribute6 := x_attribute6;
120 new_references.attribute7 := x_attribute7;
121 new_references.attribute8 := x_attribute8;
122 new_references.attribute9 := x_attribute9;
123 new_references.attribute10 := x_attribute10;
124 new_references.attribute11 := x_attribute11;
125 new_references.attribute12 := x_attribute12;
126 new_references.attribute13 := x_attribute13;
127 new_references.attribute14 := x_attribute14;
128 new_references.attribute15 := x_attribute15;
129 new_references.related_dossier_id := x_related_dossier_id;
130
131 IF (p_action = 'UPDATE') THEN
132 new_references.creation_date := old_references.creation_date;
133 new_references.created_by := old_references.created_by;
134 ELSE
135 new_references.creation_date := x_creation_date;
136 new_references.created_by := x_created_by;
137 END IF;
138
139 new_references.last_update_date := x_last_update_date;
140 new_references.last_updated_by := x_last_updated_by;
141 new_references.last_update_login := x_last_update_login;
142
143 END set_column_values;
144
145
146 PROCEDURE check_parent_existance AS
147 /*
148 || Created By : [email protected]
149 || Created On : 18-APR-2002
150 || Purpose : Checks for the existance of Parent records.
151 || Known limitations, enhancements or remarks :
152 || Change History :
153 || Who When What
154 || (reverse chronological order - newest change first)
155 */
156 BEGIN
157
158 IF (((old_references.dossier_numbering = new_references.dossier_numbering)) OR
159 ((new_references.dossier_numbering IS NULL))) THEN
160 NULL;
161 /* for dossier numbering there is no TBH package
162 ELSIF NOT igi_dossier_numbering_pkg.get_pk_for_validation (
163 new_references.dossier_numbering
164 ) THEN
165 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
166 -- bug 3199481, start block
167 IF (l_unexp_level >= l_debug_level) THEN
168 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.check_parent_existance.Msg1',FALSE);
169 END IF;
170 -- bug 3199481, end block
171 app_exception.raise_exception; */
172 END IF;
173
174 END check_parent_existance;
175
176
177 PROCEDURE check_child_existance AS
178 /*
179 || Created By : [email protected]
180 || Created On : 18-APR-2002
181 || Purpose : Checks for the existance of Child records.
182 || Known limitations, enhancements or remarks :
183 || Change History :
184 || Who When What
185 || (reverse chronological order - newest change first)
186 */
187
188 cursor cur_rel_dos is
189 select count(*) from
190 igi_dos_doc_types
191 where related_dossier_id = old_references.dossier_id ;
192
193 cur_row_cnt number;
194
195 BEGIN
196 open cur_rel_dos ;
197 fetch cur_rel_dos into cur_row_cnt;
198 close cur_rel_dos;
199
200 if cur_row_cnt > 0 then
201 fnd_message.set_name('IGI', 'IGI_DOS_NO_DELETE_TYPE');
202 -- bug 3199481, start block
203 IF (l_unexp_level >= l_debug_level) THEN
204 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.check_child_existance.Msg1',FALSE);
205 END IF;
206 -- bug 3199481, end block
207 app_exception.raise_exception;
208 end if;
209
210 igi_dos_sources_pkg.get_fk_igi_dos_doc_types (
211 old_references.dossier_id
212 );
213 igi_dos_destinations_pkg.get_fk_igi_dos_doc_types (
214 old_references.dossier_id
215 );
216
217 END check_child_existance;
218
219
220 FUNCTION get_pk_for_validation (
221 x_dossier_id IN NUMBER
222 ) RETURN BOOLEAN AS
223 /*
224 || Created By : [email protected]
225 || Created On : 18-APR-2002
226 || Purpose : Validates the Primary Key of the table.
227 || Known limitations, enhancements or remarks :
228 || Change History :
229 || Who When What
230 || (reverse chronological order - newest change first)
231 */
232 CURSOR cur_rowid IS
233 SELECT rowid
234 FROM igi_dos_doc_types
235 WHERE dossier_id = x_dossier_id
236 FOR UPDATE NOWAIT;
237
238 lv_rowid cur_rowid%RowType;
239
240 BEGIN
241
242 OPEN cur_rowid;
243 FETCH cur_rowid INTO lv_rowid;
244 IF (cur_rowid%FOUND) THEN
245 CLOSE cur_rowid;
246 RETURN(TRUE);
247 ELSE
248 CLOSE cur_rowid;
249 RETURN(FALSE);
250 END IF;
251
252 END get_pk_for_validation;
253
254
255 PROCEDURE get_fk_igi_dossier_numbering (
256 x_numbering_scheme IN VARCHAR2
257 ) AS
258 /*
259 || Created By : [email protected]
260 || Created On : 18-APR-2002
261 || Purpose : Validates the Foreign Keys for the table.
262 || Known limitations, enhancements or remarks :
263 || Change History :
264 || Who When What
265 || (reverse chronological order - newest change first)
266 */
267 CURSOR cur_rowid IS
268 SELECT rowid
269 FROM igi_dos_doc_types
270 WHERE ((dossier_numbering = x_numbering_scheme));
271
272 lv_rowid cur_rowid%RowType;
273
274 BEGIN
275
276 OPEN cur_rowid;
277 FETCH cur_rowid INTO lv_rowid;
278 IF (cur_rowid%FOUND) THEN
279 CLOSE cur_rowid;
280 fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
281 -- bug 3199481, start block
282 IF (l_unexp_level >= l_debug_level) THEN
283 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.get_fk_igi_dossier_numbering.Msg1',FALSE);
284 END IF;
285 -- bug 3199481, end block
286 app_exception.raise_exception;
287 RETURN;
288 END IF;
289 CLOSE cur_rowid;
290
291 END get_fk_igi_dossier_numbering;
292
293
294 PROCEDURE before_dml (
295 p_action IN VARCHAR2,
296 x_rowid IN VARCHAR2 ,
297 x_amount_type IN VARCHAR2 ,
298 x_dossier_id IN NUMBER ,
299 x_dossier_name IN VARCHAR2 ,
300 x_dossier_numbering IN VARCHAR2 ,
301 x_coa_id IN NUMBER ,
302 x_sob_id IN NUMBER ,
303 x_hierarchy_id IN NUMBER ,
304 x_balanced IN VARCHAR2 ,
305 x_dossier_description IN VARCHAR2 ,
306 x_multi_annual IN VARCHAR2 ,
307 x_related_dossier IN VARCHAR2 ,
308 x_related_dossier_dsp IN VARCHAR2 ,
309 x_dossier_relationship IN VARCHAR2 ,
310 x_dossier_relationship_dsp IN VARCHAR2 ,
311 x_dossier_status IN VARCHAR2 ,
312 x_workflow_name IN VARCHAR2 ,
313 x_retired_flag IN VARCHAR2 ,
314 x_attribute_category IN VARCHAR2 ,
315 x_attribute1 IN VARCHAR2 ,
316 x_attribute2 IN VARCHAR2 ,
317 x_attribute3 IN VARCHAR2 ,
318 x_attribute4 IN VARCHAR2 ,
319 x_attribute5 IN VARCHAR2 ,
320 x_attribute6 IN VARCHAR2 ,
321 x_attribute7 IN VARCHAR2 ,
322 x_attribute8 IN VARCHAR2 ,
323 x_attribute9 IN VARCHAR2 ,
324 x_attribute10 IN VARCHAR2 ,
325 x_attribute11 IN VARCHAR2 ,
326 x_attribute12 IN VARCHAR2 ,
327 x_attribute13 IN VARCHAR2 ,
328 x_attribute14 IN VARCHAR2 ,
329 x_attribute15 IN VARCHAR2 ,
330 x_related_dossier_id IN NUMBER ,
331 x_creation_date IN DATE ,
332 x_created_by IN NUMBER ,
333 x_last_update_date IN DATE ,
334 x_last_updated_by IN NUMBER ,
335 x_last_update_login IN NUMBER
336 ) AS
337 /*
338 || Created By : [email protected]
339 || Created On : 18-APR-2002
340 || Purpose : Initialises the columns, Checks Constraints, Calls the
341 || Trigger Handlers for the table, before any DML operation.
342 || Known limitations, enhancements or remarks :
343 || Change History :
344 || Who When What
345 || (reverse chronological order - newest change first)
346 */
347 BEGIN
348
349 set_column_values (
350 p_action,
351 x_rowid,
352 x_amount_type,
353 x_dossier_id,
354 x_dossier_name,
355 x_dossier_numbering,
356 x_coa_id,
357 x_sob_id,
358 x_hierarchy_id,
359 x_balanced,
360 x_dossier_description,
361 x_multi_annual,
362 -- x_related_dossier,
363 x_related_dossier_dsp,
364 x_dossier_relationship,
365 x_dossier_relationship_dsp,
366 x_dossier_status,
367 x_workflow_name,
368 x_retired_flag,
369 x_attribute_category,
370 x_attribute1,
371 x_attribute2,
372 x_attribute3,
373 x_attribute4,
374 x_attribute5,
375 x_attribute6,
376 x_attribute7,
377 x_attribute8,
378 x_attribute9,
379 x_attribute10,
380 x_attribute11,
381 x_attribute12,
382 x_attribute13,
383 x_attribute14,
384 x_attribute15,
385 x_related_dossier_id,
386 x_creation_date,
387 x_created_by,
388 x_last_update_date,
389 x_last_updated_by,
390 x_last_update_login
391 );
392
393 IF (p_action = 'INSERT') THEN
394 -- Call all the procedures related to Before Insert.
395 IF ( get_pk_for_validation(
396 new_references.dossier_id
397 )
398 ) THEN
399 fnd_message.set_name('FND','FORM_DUPLICATE_KEY_IN_INDEX');
400 -- bug 3199481, start block
401 IF (l_unexp_level >= l_debug_level) THEN
402 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.before_dml.Msg1',FALSE);
403 END IF;
404 -- bug 3199481, end block
405 app_exception.raise_exception;
406 END IF;
407 check_parent_existance;
408 ELSIF (p_action = 'UPDATE') THEN
409 -- Call all the procedures related to Before Update.
410 check_parent_existance;
411 ELSIF (p_action = 'DELETE') THEN
412 -- Call all the procedures related to Before Delete.
413 check_child_existance;
414 ELSIF (p_action = 'VALIDATE_INSERT') THEN
415 -- Call all the procedures related to Before Insert.
416 IF ( get_pk_for_validation (
417 new_references.dossier_id
418 )
419 ) THEN
420 fnd_message.set_name('FND','FORM_DUPLICATE_KEY_IN_INDEX');
421 -- bug 3199481, start block
422 IF (l_unexp_level >= l_debug_level) THEN
423 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.before_dml.Msg2',FALSE);
424 END IF;
425 -- bug 3199481, end block
426 app_exception.raise_exception;
427 END IF;
428 ELSIF (p_action = 'VALIDATE_DELETE') THEN
429 check_child_existance;
430 END IF;
431
432 END before_dml;
433
434
435 PROCEDURE insert_row (
436 x_rowid IN OUT NOCOPY VARCHAR2,
437 x_amount_type IN VARCHAR2,
438 x_dossier_id IN OUT NOCOPY NUMBER,
439 x_dossier_name IN VARCHAR2,
440 x_dossier_numbering IN VARCHAR2,
441 x_coa_id IN NUMBER,
442 x_sob_id IN NUMBER,
443 x_hierarchy_id IN NUMBER,
444 x_balanced IN VARCHAR2,
445 x_dossier_description IN VARCHAR2,
446 x_multi_annual IN VARCHAR2,
447 x_related_dossier IN VARCHAR2,
448 x_related_dossier_dsp IN VARCHAR2,
449 x_dossier_relationship IN VARCHAR2,
450 x_dossier_relationship_dsp IN VARCHAR2,
451 x_dossier_status IN VARCHAR2,
452 x_workflow_name IN VARCHAR2,
453 x_retired_flag IN VARCHAR2,
454 x_attribute_category IN VARCHAR2,
455 x_attribute1 IN VARCHAR2,
456 x_attribute2 IN VARCHAR2,
457 x_attribute3 IN VARCHAR2,
458 x_attribute4 IN VARCHAR2,
459 x_attribute5 IN VARCHAR2,
460 x_attribute6 IN VARCHAR2,
461 x_attribute7 IN VARCHAR2,
462 x_attribute8 IN VARCHAR2,
463 x_attribute9 IN VARCHAR2,
464 x_attribute10 IN VARCHAR2,
465 x_attribute11 IN VARCHAR2,
466 x_attribute12 IN VARCHAR2,
467 x_attribute13 IN VARCHAR2,
468 x_attribute14 IN VARCHAR2,
469 x_attribute15 IN VARCHAR2,
470 x_related_dossier_id IN NUMBER,
471 x_mode IN VARCHAR2 ) AS
472 /*
473 || Created By : [email protected]
474 || Created On : 18-APR-2002
475 || Purpose : Handles the INSERT DML logic for the table.
476 || Known limitations, enhancements or remarks :
477 || Change History :
478 || Who When What
479 || (reverse chronological order - newest change first)
480 */
481 CURSOR c IS
482 SELECT rowid
483 FROM igi_dos_doc_types
484 WHERE dossier_id = x_dossier_id;
485
486 x_last_update_date DATE;
487 x_last_updated_by NUMBER;
488 x_last_update_login NUMBER;
489
490 BEGIN
491
492 x_last_update_date := SYSDATE;
493 IF (x_mode = 'I') THEN
494 x_last_updated_by := 1;
495 x_last_update_login := 0;
496 ELSIF (x_mode = 'R') THEN
497 x_last_updated_by := fnd_global.user_id;
498 IF (x_last_updated_by IS NULL) THEN
499 x_last_updated_by := -1;
500 END IF;
501 x_last_update_login := fnd_global.login_id;
502 IF (x_last_update_login IS NULL) THEN
503 x_last_update_login := -1;
504 END IF;
505 ELSE
506 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
507 -- bug 3199481, start block
508 IF (l_unexp_level >= l_debug_level) THEN
509 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.insert_row.Msg1',FALSE);
510 END IF;
511 -- bug 3199481, end block
512 app_exception.raise_exception;
513 END IF;
514
515 SELECT igi_dos_doc_types_s.NEXTVAL
516 INTO x_dossier_id
517 FROM dual;
518
519 before_dml(
520 p_action => 'INSERT',
521 x_rowid => x_rowid,
522 x_amount_type => x_amount_type,
523 x_dossier_id => x_dossier_id,
524 x_dossier_name => x_dossier_name,
525 x_dossier_numbering => x_dossier_numbering,
526 x_coa_id => x_coa_id,
527 x_sob_id => x_sob_id,
528 x_hierarchy_id => x_hierarchy_id,
529 x_balanced => x_balanced,
530 x_dossier_description => x_dossier_description,
531 x_multi_annual => x_multi_annual,
532 x_related_dossier => x_related_dossier,
533 x_related_dossier_dsp => x_related_dossier_dsp,
534 x_dossier_relationship => x_dossier_relationship,
535 x_dossier_relationship_dsp => x_dossier_relationship_dsp,
536 x_dossier_status => x_dossier_status,
537 x_workflow_name => x_workflow_name,
538 x_retired_flag => x_retired_flag,
539 x_attribute_category => x_attribute_category,
540 x_attribute1 => x_attribute1,
541 x_attribute2 => x_attribute2,
542 x_attribute3 => x_attribute3,
543 x_attribute4 => x_attribute4,
544 x_attribute5 => x_attribute5,
545 x_attribute6 => x_attribute6,
546 x_attribute7 => x_attribute7,
547 x_attribute8 => x_attribute8,
548 x_attribute9 => x_attribute9,
549 x_attribute10 => x_attribute10,
550 x_attribute11 => x_attribute11,
551 x_attribute12 => x_attribute12,
552 x_attribute13 => x_attribute13,
553 x_attribute14 => x_attribute14,
554 x_attribute15 => x_attribute15,
555 x_related_dossier_id => x_related_dossier_id,
556 x_creation_date => x_last_update_date,
557 x_created_by => x_last_updated_by,
558 x_last_update_date => x_last_update_date,
559 x_last_updated_by => x_last_updated_by,
560 x_last_update_login => x_last_update_login
561 );
562
563 INSERT INTO igi_dos_doc_types (
564 amount_type,
565 dossier_id,
566 dossier_name,
567 dossier_numbering,
568 coa_id,
569 sob_id,
570 hierarchy_id,
571 balanced,
572 dossier_description,
573 multi_annual,
574 -- related_dossier,
575 related_dossier_dsp,
576 dossier_relationship,
577 dossier_relationship_dsp,
578 dossier_status,
579 workflow_name,
580 retired_flag,
581 attribute_category,
582 attribute1,
583 attribute2,
584 attribute3,
585 attribute4,
586 attribute5,
587 attribute6,
588 attribute7,
589 attribute8,
590 attribute9,
591 attribute10,
592 attribute11,
593 attribute12,
594 attribute13,
595 attribute14,
596 attribute15,
597 related_dossier_id,
598 creation_date,
599 created_by,
600 last_update_date,
601 last_updated_by,
602 last_update_login
603 ) VALUES (
604 new_references.amount_type,
605 new_references.dossier_id,
606 new_references.dossier_name,
607 new_references.dossier_numbering,
608 new_references.coa_id,
609 new_references.sob_id,
610 new_references.hierarchy_id,
611 new_references.balanced,
612 new_references.dossier_description,
613 new_references.multi_annual,
614 -- new_references.related_dossier,
615 new_references.related_dossier_dsp,
616 new_references.dossier_relationship,
617 new_references.dossier_relationship_dsp,
618 new_references.dossier_status,
619 new_references.workflow_name,
620 new_references.retired_flag,
621 new_references.attribute_category,
622 new_references.attribute1,
623 new_references.attribute2,
624 new_references.attribute3,
625 new_references.attribute4,
626 new_references.attribute5,
627 new_references.attribute6,
628 new_references.attribute7,
629 new_references.attribute8,
630 new_references.attribute9,
631 new_references.attribute10,
632 new_references.attribute11,
633 new_references.attribute12,
634 new_references.attribute13,
635 new_references.attribute14,
636 new_references.attribute15,
637 new_references.related_dossier_id,
638 x_last_update_date,
639 x_last_updated_by,
640 x_last_update_date,
641 x_last_updated_by,
642 x_last_update_login
643 );
644
645 OPEN c;
646 FETCH c INTO x_rowid;
647 IF (c%NOTFOUND) THEN
648 CLOSE c;
649 RAISE NO_DATA_FOUND;
650 END IF;
651 CLOSE c;
652
653 END insert_row;
654
655
656 PROCEDURE lock_row (
657 x_rowid IN VARCHAR2,
658 x_amount_type IN VARCHAR2,
659 x_dossier_id IN NUMBER,
660 x_dossier_name IN VARCHAR2,
661 x_dossier_numbering IN VARCHAR2,
662 x_coa_id IN NUMBER,
663 x_sob_id IN NUMBER,
664 x_hierarchy_id IN NUMBER,
665 x_balanced IN VARCHAR2,
666 x_dossier_description IN VARCHAR2,
667 x_multi_annual IN VARCHAR2,
668 x_related_dossier IN VARCHAR2,
669 x_related_dossier_dsp IN VARCHAR2,
670 x_dossier_relationship IN VARCHAR2,
671 x_dossier_relationship_dsp IN VARCHAR2,
672 x_dossier_status IN VARCHAR2,
673 x_workflow_name IN VARCHAR2,
674 x_retired_flag IN VARCHAR2,
675 x_attribute_category IN VARCHAR2,
676 x_attribute1 IN VARCHAR2,
677 x_attribute2 IN VARCHAR2,
678 x_attribute3 IN VARCHAR2,
679 x_attribute4 IN VARCHAR2,
680 x_attribute5 IN VARCHAR2,
681 x_attribute6 IN VARCHAR2,
682 x_attribute7 IN VARCHAR2,
683 x_attribute8 IN VARCHAR2,
684 x_attribute9 IN VARCHAR2,
685 x_attribute10 IN VARCHAR2,
686 x_attribute11 IN VARCHAR2,
687 x_attribute12 IN VARCHAR2,
688 x_attribute13 IN VARCHAR2,
689 x_attribute14 IN VARCHAR2,
690 x_attribute15 IN VARCHAR2,
691 x_related_dossier_id IN NUMBER
692 ) AS
693 /*
694 || Created By : [email protected]
695 || Created On : 18-APR-2002
696 || Purpose : Handles the LOCK mechanism for the table.
697 || Known limitations, enhancements or remarks :
698 || Change History :
699 || Who When What
700 || (reverse chronological order - newest change first)
701 */
702 CURSOR c1 IS
703 SELECT
704 amount_type,
705 dossier_name,
706 dossier_numbering,
707 coa_id,
708 sob_id,
709 hierarchy_id,
710 balanced,
711 dossier_description,
712 multi_annual,
713 -- related_dossier,
714 related_dossier_dsp,
715 dossier_relationship,
716 dossier_relationship_dsp,
717 dossier_status,
718 workflow_name,
719 retired_flag,
720 attribute_category,
721 attribute1,
722 attribute2,
723 attribute3,
724 attribute4,
725 attribute5,
726 attribute6,
727 attribute7,
728 attribute8,
729 attribute9,
730 attribute10,
731 attribute11,
732 attribute12,
733 attribute13,
734 attribute14,
735 attribute15,
736 related_dossier_id
737 FROM igi_dos_doc_types
738 WHERE rowid = x_rowid
739 FOR UPDATE NOWAIT;
740
741 tlinfo c1%ROWTYPE;
742
743 BEGIN
744
745 OPEN c1;
746 FETCH c1 INTO tlinfo;
747 IF (c1%notfound) THEN
748 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
749 -- bug 3199481, start block
750 IF (l_unexp_level >= l_debug_level) THEN
751 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.lock_row.Msg1',FALSE);
752 END IF;
753 -- bug 3199481, end block
754 CLOSE c1;
755 app_exception.raise_exception;
756 RETURN;
757 END IF;
758 CLOSE c1;
759
760 IF (
761 ((tlinfo.amount_type = x_amount_type) OR ((tlinfo.amount_type IS NULL) AND (X_amount_type IS NULL)))
762 AND (tlinfo.dossier_name = x_dossier_name)
763 AND ((tlinfo.dossier_numbering = x_dossier_numbering) OR ((tlinfo.dossier_numbering IS NULL) AND (X_dossier_numbering IS NULL)))
764 AND (tlinfo.coa_id = x_coa_id)
765 AND (tlinfo.sob_id = x_sob_id)
766 AND ((tlinfo.hierarchy_id = x_hierarchy_id) OR ((tlinfo.hierarchy_id IS NULL) AND (X_hierarchy_id IS NULL)))
767 AND (tlinfo.balanced = x_balanced)
768 AND ((tlinfo.dossier_description = x_dossier_description) OR ((tlinfo.dossier_description IS NULL) AND (X_dossier_description IS NULL)))
769 AND (tlinfo.multi_annual = x_multi_annual)
770 -- AND ((tlinfo.related_dossier = x_related_dossier) OR ((tlinfo.related_dossier IS NULL) AND (X_related_dossier IS NULL)))
771 AND ((tlinfo.related_dossier_dsp = x_related_dossier_dsp) OR ((tlinfo.related_dossier_dsp IS NULL) AND (X_related_dossier_dsp IS NULL)))
772 AND ((tlinfo.dossier_relationship = x_dossier_relationship) OR ((tlinfo.dossier_relationship IS NULL) AND (X_dossier_relationship IS NULL)))
773 AND ((tlinfo.dossier_relationship_dsp = x_dossier_relationship_dsp) OR ((tlinfo.dossier_relationship_dsp IS NULL) AND (X_dossier_relationship_dsp IS NULL)))
774 AND (tlinfo.dossier_status = x_dossier_status)
775 AND ((tlinfo.workflow_name = x_workflow_name) OR ((tlinfo.workflow_name IS NULL) AND (X_workflow_name IS NULL)))
776 AND ((tlinfo.retired_flag = x_retired_flag) OR ((tlinfo.retired_flag IS NULL) AND (X_retired_flag IS NULL)))
777 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
778 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
779 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
780 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
781 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
782 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
783 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
784 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
785 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
786 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
787 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
788 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
789 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
790 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
791 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
792 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
793 AND ((tlinfo.related_dossier_id = x_related_dossier_id) OR ((tlinfo.related_dossier_id IS NULL) AND (X_related_dossier_id IS NULL)))
794 ) THEN
795 NULL;
796 ELSE
797 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
798 -- bug 3199481, start block
799 IF (l_unexp_level >= l_debug_level) THEN
800 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.lock_row.Msg2',FALSE);
801 END IF;
802 -- bug 3199481, end block
803 app_exception.raise_exception;
804 END IF;
805
806 RETURN;
807
808 END lock_row;
809
810
811 PROCEDURE update_row (
812 x_rowid IN VARCHAR2,
813 x_amount_type IN VARCHAR2,
814 x_dossier_id IN NUMBER,
815 x_dossier_name IN VARCHAR2,
816 x_dossier_numbering IN VARCHAR2,
817 x_coa_id IN NUMBER,
818 x_sob_id IN NUMBER,
819 x_hierarchy_id IN NUMBER,
820 x_balanced IN VARCHAR2,
821 x_dossier_description IN VARCHAR2,
822 x_multi_annual IN VARCHAR2,
823 x_related_dossier IN VARCHAR2,
824 x_related_dossier_dsp IN VARCHAR2,
825 x_dossier_relationship IN VARCHAR2,
826 x_dossier_relationship_dsp IN VARCHAR2,
827 x_dossier_status IN VARCHAR2,
828 x_workflow_name IN VARCHAR2,
829 x_retired_flag IN VARCHAR2,
830 x_attribute_category IN VARCHAR2,
831 x_attribute1 IN VARCHAR2,
832 x_attribute2 IN VARCHAR2,
833 x_attribute3 IN VARCHAR2,
834 x_attribute4 IN VARCHAR2,
835 x_attribute5 IN VARCHAR2,
836 x_attribute6 IN VARCHAR2,
837 x_attribute7 IN VARCHAR2,
838 x_attribute8 IN VARCHAR2,
839 x_attribute9 IN VARCHAR2,
840 x_attribute10 IN VARCHAR2,
841 x_attribute11 IN VARCHAR2,
842 x_attribute12 IN VARCHAR2,
843 x_attribute13 IN VARCHAR2,
844 x_attribute14 IN VARCHAR2,
845 x_attribute15 IN VARCHAR2,
846 x_related_dossier_id IN NUMBER,
847 x_mode IN VARCHAR2 ) AS
848 /*
849 || Created By : [email protected]
850 || Created On : 18-APR-2002
851 || Purpose : Handles the UPDATE DML logic for the table.
852 || Known limitations, enhancements or remarks :
853 || Change History :
854 || Who When What
855 || (reverse chronological order - newest change first)
856 */
857 x_last_update_date DATE ;
858 x_last_updated_by NUMBER;
859 x_last_update_login NUMBER;
860
861 BEGIN
862
863 x_last_update_date := SYSDATE;
864 IF (X_MODE = 'I') THEN
865 x_last_updated_by := 1;
866 x_last_update_login := 0;
867 ELSIF (x_mode = 'R') THEN
868 x_last_updated_by := fnd_global.user_id;
869 IF x_last_updated_by IS NULL THEN
870 x_last_updated_by := -1;
871 END IF;
872 x_last_update_login := fnd_global.login_id;
873 IF (x_last_update_login IS NULL) THEN
874 x_last_update_login := -1;
875 END IF;
876 ELSE
877 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
878 -- bug 3199481, start block
879 IF (l_unexp_level >= l_debug_level) THEN
880 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_doc_types_pkg.update_row.Msg1',FALSE);
881 END IF;
882 -- bug 3199481, end block
883 app_exception.raise_exception;
884 END IF;
885
886 before_dml(
887 p_action => 'UPDATE',
888 x_rowid => x_rowid,
889 x_amount_type => x_amount_type,
890 x_dossier_id => x_dossier_id,
891 x_dossier_name => x_dossier_name,
892 x_dossier_numbering => x_dossier_numbering,
893 x_coa_id => x_coa_id,
894 x_sob_id => x_sob_id,
895 x_hierarchy_id => x_hierarchy_id,
896 x_balanced => x_balanced,
897 x_dossier_description => x_dossier_description,
898 x_multi_annual => x_multi_annual,
899 x_related_dossier => x_related_dossier,
900 x_related_dossier_dsp => x_related_dossier_dsp,
901 x_dossier_relationship => x_dossier_relationship,
902 x_dossier_relationship_dsp => x_dossier_relationship_dsp,
903 x_dossier_status => x_dossier_status,
904 x_workflow_name => x_workflow_name,
905 x_retired_flag => x_retired_flag,
906 x_attribute_category => x_attribute_category,
907 x_attribute1 => x_attribute1,
908 x_attribute2 => x_attribute2,
909 x_attribute3 => x_attribute3,
910 x_attribute4 => x_attribute4,
911 x_attribute5 => x_attribute5,
912 x_attribute6 => x_attribute6,
913 x_attribute7 => x_attribute7,
914 x_attribute8 => x_attribute8,
915 x_attribute9 => x_attribute9,
916 x_attribute10 => x_attribute10,
917 x_attribute11 => x_attribute11,
918 x_attribute12 => x_attribute12,
919 x_attribute13 => x_attribute13,
920 x_attribute14 => x_attribute14,
921 x_attribute15 => x_attribute15,
922 x_related_dossier_id => x_related_dossier_id,
923 x_creation_date => x_last_update_date,
924 x_created_by => x_last_updated_by,
925 x_last_update_date => x_last_update_date,
926 x_last_updated_by => x_last_updated_by,
927 x_last_update_login => x_last_update_login
928 );
929
930 UPDATE igi_dos_doc_types
931 SET
932 amount_type = new_references.amount_type,
933 dossier_name = new_references.dossier_name,
934 dossier_numbering = new_references.dossier_numbering,
935 coa_id = new_references.coa_id,
936 sob_id = new_references.sob_id,
937 hierarchy_id = new_references.hierarchy_id,
938 balanced = new_references.balanced,
939 dossier_description = new_references.dossier_description,
940 multi_annual = new_references.multi_annual,
941 -- related_dossier = new_references.related_dossier,
942 related_dossier_dsp = new_references.related_dossier_dsp,
943 dossier_relationship = new_references.dossier_relationship,
944 dossier_relationship_dsp = new_references.dossier_relationship_dsp,
945 dossier_status = new_references.dossier_status,
946 workflow_name = new_references.workflow_name,
947 retired_flag = new_references.retired_flag,
948 attribute_category = new_references.attribute_category,
949 attribute1 = new_references.attribute1,
950 attribute2 = new_references.attribute2,
951 attribute3 = new_references.attribute3,
952 attribute4 = new_references.attribute4,
953 attribute5 = new_references.attribute5,
954 attribute6 = new_references.attribute6,
955 attribute7 = new_references.attribute7,
956 attribute8 = new_references.attribute8,
957 attribute9 = new_references.attribute9,
958 attribute10 = new_references.attribute10,
959 attribute11 = new_references.attribute11,
960 attribute12 = new_references.attribute12,
961 attribute13 = new_references.attribute13,
962 attribute14 = new_references.attribute14,
963 attribute15 = new_references.attribute15,
964 related_dossier_id = new_references.related_dossier_id,
965 last_update_date = x_last_update_date,
966 last_updated_by = x_last_updated_by,
967 last_update_login = x_last_update_login
968 WHERE rowid = x_rowid;
969
970 IF (SQL%NOTFOUND) THEN
971 RAISE NO_DATA_FOUND;
972 END IF;
973
974 END update_row;
975
976
977 PROCEDURE add_row (
978 x_rowid IN OUT NOCOPY VARCHAR2,
979 x_amount_type IN VARCHAR2,
980 x_dossier_id IN OUT NOCOPY NUMBER,
981 x_dossier_name IN VARCHAR2,
982 x_dossier_numbering IN VARCHAR2,
983 x_coa_id IN NUMBER,
984 x_sob_id IN NUMBER,
985 x_hierarchy_id IN NUMBER,
986 x_balanced IN VARCHAR2,
987 x_dossier_description IN VARCHAR2,
988 x_multi_annual IN VARCHAR2,
989 x_related_dossier IN VARCHAR2,
990 x_related_dossier_dsp IN VARCHAR2,
991 x_dossier_relationship IN VARCHAR2,
992 x_dossier_relationship_dsp IN VARCHAR2,
993 x_dossier_status IN VARCHAR2,
994 x_workflow_name IN VARCHAR2,
995 x_retired_flag IN VARCHAR2,
996 x_attribute_category IN VARCHAR2,
997 x_attribute1 IN VARCHAR2,
998 x_attribute2 IN VARCHAR2,
999 x_attribute3 IN VARCHAR2,
1000 x_attribute4 IN VARCHAR2,
1001 x_attribute5 IN VARCHAR2,
1002 x_attribute6 IN VARCHAR2,
1003 x_attribute7 IN VARCHAR2,
1004 x_attribute8 IN VARCHAR2,
1005 x_attribute9 IN VARCHAR2,
1006 x_attribute10 IN VARCHAR2,
1007 x_attribute11 IN VARCHAR2,
1008 x_attribute12 IN VARCHAR2,
1009 x_attribute13 IN VARCHAR2,
1010 x_attribute14 IN VARCHAR2,
1011 x_attribute15 IN VARCHAR2,
1012 x_related_dossier_id IN NUMBER,
1013 x_mode IN VARCHAR2 ) AS
1014 /*
1015 || Created By : [email protected]
1016 || Created On : 18-APR-2002
1017 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1018 || Known limitations, enhancements or remarks :
1019 || Change History :
1020 || Who When What
1021 || (reverse chronological order - newest change first)
1022 */
1023 CURSOR c1 IS
1024 SELECT rowid
1025 FROM igi_dos_doc_types
1026 WHERE dossier_id = x_dossier_id;
1027
1028 BEGIN
1029
1030 OPEN c1;
1031 FETCH c1 INTO x_rowid;
1032 IF (c1%NOTFOUND) THEN
1033 CLOSE c1;
1034
1035 insert_row (
1036 x_rowid,
1037 x_amount_type,
1038 x_dossier_id,
1039 x_dossier_name,
1040 x_dossier_numbering,
1041 x_coa_id,
1042 x_sob_id,
1043 x_hierarchy_id,
1044 x_balanced,
1045 x_dossier_description,
1046 x_multi_annual,
1047 x_related_dossier,
1048 x_related_dossier_dsp,
1049 x_dossier_relationship,
1050 x_dossier_relationship_dsp,
1051 x_dossier_status,
1052 x_workflow_name,
1053 x_retired_flag,
1054 x_attribute_category,
1055 x_attribute1,
1056 x_attribute2,
1057 x_attribute3,
1058 x_attribute4,
1059 x_attribute5,
1060 x_attribute6,
1061 x_attribute7,
1062 x_attribute8,
1063 x_attribute9,
1064 x_attribute10,
1065 x_attribute11,
1066 x_attribute12,
1067 x_attribute13,
1068 x_attribute14,
1069 x_attribute15,
1070 x_related_dossier_id,
1071 x_mode
1072 );
1073 RETURN;
1074 END IF;
1075 CLOSE c1;
1076
1077 update_row (
1078 x_rowid,
1079 x_amount_type,
1080 x_dossier_id,
1081 x_dossier_name,
1082 x_dossier_numbering,
1083 x_coa_id,
1084 x_sob_id,
1085 x_hierarchy_id,
1086 x_balanced,
1087 x_dossier_description,
1088 x_multi_annual,
1089 x_related_dossier,
1090 x_related_dossier_dsp,
1091 x_dossier_relationship,
1092 x_dossier_relationship_dsp,
1093 x_dossier_status,
1094 x_workflow_name,
1095 x_retired_flag,
1096 x_attribute_category,
1097 x_attribute1,
1098 x_attribute2,
1099 x_attribute3,
1100 x_attribute4,
1101 x_attribute5,
1102 x_attribute6,
1103 x_attribute7,
1104 x_attribute8,
1105 x_attribute9,
1106 x_attribute10,
1107 x_attribute11,
1108 x_attribute12,
1109 x_attribute13,
1110 x_attribute14,
1111 x_attribute15,
1112 x_related_dossier_id,
1113 x_mode
1114 );
1115
1116 END add_row;
1117
1118
1119 PROCEDURE delete_row (
1120 x_rowid IN VARCHAR2
1121 ) AS
1122 /*
1123 || Created By : [email protected]
1124 || Created On : 18-APR-2002
1125 || Purpose : Handles the DELETE DML logic for the table.
1126 || Known limitations, enhancements or remarks :
1127 || Change History :
1128 || Who When What
1129 || (reverse chronological order - newest change first)
1130 */
1131 BEGIN
1132
1133 before_dml (
1134 p_action => 'DELETE',
1135 x_rowid => x_rowid
1136 );
1137
1138
1139 DELETE FROM igi_dos_doc_types
1140 WHERE rowid = x_rowid;
1141
1142 IF (SQL%NOTFOUND) THEN
1143 RAISE NO_DATA_FOUND;
1144 END IF;
1145
1146 END delete_row;
1147
1148
1149 END igi_dos_doc_types_pkg;