[Home] [Help]
PACKAGE BODY: APPS.IGI_DOS_TRX_HEADERS_PKG
Source
1 PACKAGE BODY igi_dos_trx_headers_pkg AS
2 /* $Header: igidossb.pls 120.11.12010000.2 2008/08/04 13:00:56 sasukuma ship $ */
3
4 l_debug_level number;
5
6 l_state_level number;
7 l_proc_level number;
8 l_event_level number;
9 l_excep_level number;
10 l_error_level number;
11 l_unexp_level number;
12
13 l_rowid VARCHAR2(25);
14 old_references igi_dos_trx_headers%ROWTYPE;
15 new_references igi_dos_trx_headers%ROWTYPE;
16
17 PROCEDURE set_column_values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2 ,
20 x_sob_id IN NUMBER ,
21 x_trx_id IN NUMBER ,
22 x_dossier_name IN VARCHAR2 ,
23 x_trx_number IN VARCHAR2 ,
24 x_packet_id IN NUMBER ,
25 x_trx_status IN VARCHAR2 ,
26 x_dossier_id IN NUMBER ,
27 x_dossier_transaction_name IN VARCHAR2 ,
28 x_description IN VARCHAR2 ,
29 x_funds_status IN VARCHAR2 ,
30 x_attribute_category IN VARCHAR2 ,
31 x_attribute1 IN VARCHAR2 ,
32 x_attribute2 IN VARCHAR2 ,
33 x_attribute3 IN VARCHAR2 ,
34 x_attribute4 IN VARCHAR2 ,
35 x_attribute5 IN VARCHAR2 ,
36 x_attribute6 IN VARCHAR2 ,
37 x_attribute7 IN VARCHAR2 ,
38 x_attribute8 IN VARCHAR2 ,
39 x_attribute9 IN VARCHAR2 ,
40 x_attribute10 IN VARCHAR2 ,
41 x_attribute11 IN VARCHAR2 ,
42 x_attribute12 IN VARCHAR2 ,
43 x_attribute13 IN VARCHAR2 ,
44 x_attribute14 IN VARCHAR2 ,
45 x_attribute15 IN VARCHAR2 ,
46 x_attribute16 IN VARCHAR2 ,
47 x_attribute17 IN VARCHAR2 ,
48 x_attribute18 IN VARCHAR2 ,
49 x_attribute19 IN VARCHAR2 ,
50 x_attribute20 IN VARCHAR2 ,
51 x_attribute21 IN VARCHAR2 ,
52 x_attribute22 IN VARCHAR2 ,
53 x_attribute23 IN VARCHAR2 ,
54 x_attribute24 IN VARCHAR2 ,
55 x_attribute25 IN VARCHAR2 ,
56 x_attribute26 IN VARCHAR2 ,
57 x_attribute27 IN VARCHAR2 ,
58 x_attribute28 IN VARCHAR2 ,
59 x_attribute29 IN VARCHAR2 ,
60 x_attribute30 IN VARCHAR2 ,
61 x_parent_trx_id IN NUMBER ,
62 x_parent_trx_number IN VARCHAR2 ,
63 x_creation_date IN DATE ,
64 x_created_by IN NUMBER ,
65 x_last_update_date IN DATE ,
66 x_last_updated_by IN NUMBER ,
67 x_last_update_login IN NUMBER
68 ) AS
69 /*
70 || Created By : [email protected]
71 || Created On : 02-MAY-2002
72 || Purpose : Initialises the Old and New references for the columns of the table.
73 || Known limitations, enhancements or remarks :
74 || Change History :
75 || Who When What
76 || (reverse chronological order - newest change first)
77 */
78
79 CURSOR cur_old_ref_values IS
80 SELECT *
81 FROM igi_dos_trx_headers
82 WHERE rowid = x_rowid;
83
84 BEGIN
85
86 l_rowid := x_rowid;
87
88 -- Code for setting the Old and New Reference Values.
89 -- Populate Old Values.
90 OPEN cur_old_ref_values;
91 FETCH cur_old_ref_values INTO old_references;
92 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
93 CLOSE cur_old_ref_values;
94 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
95 -- bug 3199481, start block
96 IF (l_unexp_level >= l_debug_level) THEN
97 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_headers_pkg.set_column_values.Msg1',FALSE);
98 END IF;
99 -- bug 3199481, end block
100 app_exception.raise_exception;
101 RETURN;
102 END IF;
103 CLOSE cur_old_ref_values;
104
105 -- Populate New Values.
106 new_references.sob_id := x_sob_id;
107 new_references.trx_id := x_trx_id;
108 new_references.dossier_name := x_dossier_name;
109 new_references.trx_number := x_trx_number;
110 new_references.packet_id := x_packet_id;
111 new_references.trx_status := x_trx_status;
112 new_references.dossier_id := x_dossier_id;
113 new_references.dossier_transaction_name := x_dossier_transaction_name;
114 new_references.description := x_description;
115 new_references.funds_status := x_funds_status;
116 new_references.attribute_category := x_attribute_category;
117 new_references.attribute1 := x_attribute1;
118 new_references.attribute2 := x_attribute2;
119 new_references.attribute3 := x_attribute3;
120 new_references.attribute4 := x_attribute4;
121 new_references.attribute5 := x_attribute5;
122 new_references.attribute6 := x_attribute6;
123 new_references.attribute7 := x_attribute7;
124 new_references.attribute8 := x_attribute8;
125 new_references.attribute9 := x_attribute9;
126 new_references.attribute10 := x_attribute10;
127 new_references.attribute11 := x_attribute11;
128 new_references.attribute12 := x_attribute12;
129 new_references.attribute13 := x_attribute13;
130 new_references.attribute14 := x_attribute14;
131 new_references.attribute15 := x_attribute15;
132 new_references.attribute16 := x_attribute16;
133 new_references.attribute17 := x_attribute17;
134 new_references.attribute18 := x_attribute18;
135 new_references.attribute19 := x_attribute19;
136 new_references.attribute20 := x_attribute20;
137 new_references.attribute21 := x_attribute21;
138 new_references.attribute22 := x_attribute22;
139 new_references.attribute23 := x_attribute23;
140 new_references.attribute24 := x_attribute24;
141 new_references.attribute25 := x_attribute25;
142 new_references.attribute26 := x_attribute26;
143 new_references.attribute27 := x_attribute27;
144 new_references.attribute28 := x_attribute28;
145 new_references.attribute29 := x_attribute29;
146 new_references.attribute30 := x_attribute30;
147 new_references.parent_trx_id := x_parent_trx_id;
148 new_references.parent_trx_number := x_parent_trx_number;
149
150 IF (p_action = 'UPDATE') THEN
151 new_references.creation_date := old_references.creation_date;
152 new_references.created_by := old_references.created_by;
153 ELSE
154 new_references.creation_date := x_creation_date;
155 new_references.created_by := x_created_by;
156 END IF;
157
158 new_references.last_update_date := x_last_update_date;
159 new_references.last_updated_by := x_last_updated_by;
160 new_references.last_update_login := x_last_update_login;
161
162 END set_column_values;
163
164
165 -- Trigger description :-
166 -- IGI_DOS_TRX_HEADERS_T1
167 -- BEFORE UPDATE OF TRX_STATUS
168 -- ON IGI_DOS_TRX_HEADERS
169 -- REFERENCING OLD AS OLD NEW AS NEW
170 -- FOR EACH ROW
171 -- -- PL/SQL Block
172
173 PROCEDURE BeforeRowUpdate1(
174 p_updating IN BOOLEAN
175 ) AS
176 x_history_trx_id number;
177
178 BEGIN
179
180 --
181 -- Bug 2897525 Start(1)
182 --
183 -- select igi_dos_trx_history_s.nextval
184 SELECT igi_dos_trx_headers_hist_s.NEXTVAL
185 --
186 -- Bug 2897525 End(1)
187 --
188 into x_history_trx_id
189 from dual;
190
191 insert into igi_dos_trx_headers_hist
192 (
193 trx_id ,
194 dossier_name ,
195 trx_number ,
196 packet_id ,
197 trx_status ,
198 dossier_id ,
199 dossier_transaction_name ,
200 description ,
201 last_update_date ,
202 history_trx_id
203 )
204 values (
205 new_references.trx_id ,
206 new_references.dossier_name ,
207 new_references.trx_number ,
208 new_references.packet_id ,
209 new_references.trx_status ,
210 new_references.dossier_id ,
211 new_references.dossier_transaction_name ,
212 new_references.description ,
213 new_references.last_update_date ,
214 x_history_trx_id
215 );
216
217
218
219
220 INSERT INTO igi_dos_trx_sources_hist
221 (
222 sob_id ,
223 trx_id ,
224 source_trx_id ,
225 source_id ,
226 code_combination_id ,
227 profile_code ,
228 budget_org_id ,
229 budget_entity_id ,
230 budget_amount ,
231 funds_available ,
232 new_balance ,
233 currency_code ,
234 visible_segments ,
235 actual_segments ,
236 mrc_budget_amount ,
237 mrc_budget_amt_exch_rate ,
238 mrc_budget_amt_exch_rate_type ,
239 mrc_budget_amt_exch_date ,
240 mrc_budget_amt_exch_status ,
241 mrc_funds_avail ,
242 mrc_funds_avail_exch_rate ,
243 mrc_funds_avail_exch_rate_type ,
244 mrc_funds_avail_exch_date ,
245 mrc_funds_avail_exch_status ,
246 mrc_new_balance ,
247 mrc_new_balance_exch_rate ,
248 mrc_new_balance_exch_rate_type ,
249 mrc_new_balance_exch_date ,
250 mrc_new_balance_exch_status ,
251 budget_name ,
252 dossier_id ,
253 budget_version_id ,
254 period_name ,
255 status ,
256 group_id ,
257 quarter_num ,
258 period_year ,
259 period_num ,
260 history_trx_id)
261 SELECT
262 sob_id ,
263 trx_id ,
264 source_trx_id ,
265 source_id ,
266 code_combination_id ,
267 profile_code ,
268 budget_org_id ,
269 budget_entity_id ,
270 budget_amount ,
271 funds_available ,
272 new_balance ,
273 currency_code ,
274 visible_segments ,
275 actual_segments ,
276 mrc_budget_amount ,
277 mrc_budget_amt_exch_rate ,
278 mrc_budget_amt_exch_rate_type ,
279 mrc_budget_amt_exch_date ,
280 mrc_budget_amt_exch_status ,
281 mrc_funds_avail ,
282 mrc_funds_avail_exch_rate ,
283 mrc_funds_avail_exch_rate_type ,
284 mrc_funds_avail_exch_date ,
285 mrc_funds_avail_exch_status ,
286 mrc_new_balance ,
287 mrc_new_balance_exch_rate
288 ,
289 mrc_new_balance_exch_rate_type ,
290 mrc_new_balance_exch_date ,
291 mrc_new_balance_exch_status ,
292 budget_name ,
293 dossier_id ,
294 budget_version_id ,
295 period_name ,
296 status ,
297 group_id ,
298 quarter_num ,
299 period_year ,
300 period_num ,
301 x_history_trx_id
302 from igi_dos_trx_sources
303 where trx_id = new_references.trx_id;
304
305 insert into igi_dos_trx_dest_hist
306 (
307 sob_id ,
308 trx_id ,
309 dest_trx_id ,
310 source_id ,
311 destination_id ,
312 code_combination_id ,
313 profile_code ,
314 budget_name ,
315 budget_entity_id ,
316 budget_amount ,
317 funds_available ,
318 new_balance ,
319 currency_code ,
320 visible_segments ,
321 actual_segments ,
322 mrc_budget_amount ,
323 mrc_budget_amt_exch_rate ,
324 mrc_budget_amt_exch_rate_type ,
325 mrc_budget_amt_exch_date ,
326 mrc_budget_amt_exch_status ,
327 mrc_funds_avail ,
328 mrc_funds_avail_exch_rate ,
329 mrc_funds_avail_exch_rate_type ,
330 mrc_funds_avail_exch_date ,
331 mrc_funds_avail_exch_status ,
332 mrc_new_balance ,
333 mrc_new_balance_exch_rate ,
334 mrc_new_balance_exch_rate_type ,
335 mrc_new_balance_exch_date ,
336 mrc_new_balance_exch_status ,
337 dossier_id ,
338 budget_version_id ,
339 period_name ,
340 percentage ,
341 status ,
342 group_id ,
343 quarter_num ,
344 period_year ,
345 period_num ,
346 history_trx_id
347 )
348 select
349 sob_id ,
350 trx_id ,
351 dest_trx_id ,
352 source_id ,
353 destination_id ,
354 code_combination_id ,
355 profile_code ,
356 budget_name ,
357 budget_entity_id ,
358 budget_amount ,
359 funds_available ,
360 new_balance ,
361 currency_code ,
362 visible_segments ,
363 actual_segments ,
364 mrc_budget_amount ,
365 mrc_budget_amt_exch_rate ,
366 mrc_budget_amt_exch_rate_type ,
367 mrc_budget_amt_exch_date ,
368 mrc_budget_amt_exch_status ,
369 mrc_funds_avail ,
370 mrc_funds_avail_exch_rate ,
371
372 mrc_funds_avail_exch_rate_type ,
373 mrc_funds_avail_exch_date ,
374 mrc_funds_avail_exch_status ,
375 mrc_new_balance ,
376 mrc_new_balance_exch_rate ,
377 mrc_new_balance_exch_rate_type ,
378 mrc_new_balance_exch_date ,
379 mrc_new_balance_exch_status ,
380 dossier_id ,
381 budget_version_id ,
382 period_name ,
383 percentage ,
384 status ,
385 group_id ,
386 quarter_num ,
387 period_year ,
388 period_num ,
389 x_history_trx_id
390 from igi_dos_trx_dest
391 where trx_id = new_references.trx_id;
392
393
394 END BeforeRowUpdate1;
395
396
397 PROCEDURE check_parent_existance AS
398 /*
399 || Created By : [email protected]
400 || Created On : 02-MAY-2002
401 || Purpose : Checks for the existance of Parent records.
402 || Known limitations, enhancements or remarks :
403 || Change History :
404 || Who When What
405 || (reverse chronological order - newest change first)
406 */
407 BEGIN
408
409 IF (((old_references.dossier_id = new_references.dossier_id)) OR
410 ((new_references.dossier_id IS NULL))) THEN
411 NULL;
412 ELSIF NOT igi_dos_doc_types_pkg.get_pk_for_validation (
413 new_references.dossier_id
414 ) THEN
415 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
416 -- bug 3199481, start block
417 IF (l_unexp_level >= l_debug_level) THEN
418 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_headers_pkg.check_parent_existance.Msg1',FALSE);
419 END IF;
420 -- bug 3199481, end block
421 app_exception.raise_exception;
422 END IF;
423
424 END check_parent_existance;
425
426
427 PROCEDURE check_child_existance AS
428 /*
429 || Created By : [email protected]
430 || Created On : 02-MAY-2002
431 || Purpose : Checks for the existance of Child records.
432 || Known limitations, enhancements or remarks :
433 || Change History :
434 || Who When What
435 || (reverse chronological order - newest change first)
436 */
437 BEGIN
438
439 igi_dos_trx_dest_pkg.get_fk_igi_dos_trx_headers (
440 old_references.trx_id
441 );
442 /* sowsubra 06-JUN-2002 start */
443 /* Commented the following code as this igi_dos_headers_dest_hist_pkg
444 is currently not present */
445
446 /*igi_dos_trx_headers_hist_pkg.get_fk_igi_dos_trx_headers (
447 old_references.trx_id
448 );*/
449 /* sowsubra 06-JUN-2002 end */
450
451
452 igi_dos_trx_sources_pkg.get_fk_igi_dos_trx_headers (
453 old_references.trx_id
454 );
455
456 END check_child_existance;
457
458
459 FUNCTION get_pk_for_validation (
460 x_trx_id IN NUMBER
461 ) RETURN BOOLEAN AS
462 /*
463 || Created By : [email protected]
464 || Created On : 02-MAY-2002
465 || Purpose : Validates the Primary Key of the table.
466 || Known limitations, enhancements or remarks :
467 || Change History :
468 || Who When What
469 || (reverse chronological order - newest change first)
470 */
471 CURSOR cur_rowid IS
472 SELECT rowid
473 FROM igi_dos_trx_headers
474 WHERE trx_id = x_trx_id
475 FOR UPDATE NOWAIT;
476
477 lv_rowid cur_rowid%RowType;
478
479 BEGIN
480
481 OPEN cur_rowid;
482 FETCH cur_rowid INTO lv_rowid;
483 IF (cur_rowid%FOUND) THEN
484 CLOSE cur_rowid;
485 RETURN(TRUE);
486 ELSE
487 CLOSE cur_rowid;
488 RETURN(FALSE);
489 END IF;
490
491 END get_pk_for_validation;
492
493
494 PROCEDURE get_fk_igi_dos_doc_types (
495 x_dossier_id IN NUMBER
496 ) AS
497 /*
498 || Created By : [email protected]
499 || Created On : 02-MAY-2002
500 || Purpose : Validates the Foreign Keys for the table.
501 || Known limitations, enhancements or remarks :
502 || Change History :
503 || Who When What
504 || (reverse chronological order - newest change first)
505 */
506 CURSOR cur_rowid IS
507 SELECT rowid
508 FROM igi_dos_trx_headers
509 WHERE ((dossier_id = x_dossier_id));
510
511 lv_rowid cur_rowid%RowType;
512
513 BEGIN
514
515 OPEN cur_rowid;
516 FETCH cur_rowid INTO lv_rowid;
517 IF (cur_rowid%FOUND) THEN
518 CLOSE cur_rowid;
519 fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
520 -- bug 3199481, start block
521 IF (l_unexp_level >= l_debug_level) THEN
522 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_headers_pkg.get_fk_igi_dos_doc_types.Msg1',FALSE);
523 END IF;
524 -- bug 3199481, end block
525 app_exception.raise_exception;
526 RETURN;
527 END IF;
528 CLOSE cur_rowid;
529
530 END get_fk_igi_dos_doc_types;
531
532
533 PROCEDURE before_dml (
534 p_action IN VARCHAR2,
535 x_rowid IN VARCHAR2 ,
536 x_sob_id IN NUMBER ,
537 x_trx_id IN NUMBER ,
538 x_dossier_name IN VARCHAR2 ,
539 x_trx_number IN VARCHAR2 ,
540 x_packet_id IN NUMBER ,
541 x_trx_status IN VARCHAR2 ,
542 x_dossier_id IN NUMBER ,
543 x_dossier_transaction_name IN VARCHAR2 ,
544 x_description IN VARCHAR2 ,
545 x_funds_status IN VARCHAR2 ,
546 x_attribute_category IN VARCHAR2 ,
547 x_attribute1 IN VARCHAR2 ,
548 x_attribute2 IN VARCHAR2 ,
549 x_attribute3 IN VARCHAR2 ,
550 x_attribute4 IN VARCHAR2 ,
551 x_attribute5 IN VARCHAR2 ,
552 x_attribute6 IN VARCHAR2 ,
553 x_attribute7 IN VARCHAR2 ,
554 x_attribute8 IN VARCHAR2 ,
555 x_attribute9 IN VARCHAR2 ,
556 x_attribute10 IN VARCHAR2 ,
557 x_attribute11 IN VARCHAR2 ,
558 x_attribute12 IN VARCHAR2 ,
559 x_attribute13 IN VARCHAR2 ,
560 x_attribute14 IN VARCHAR2 ,
561 x_attribute15 IN VARCHAR2 ,
562 x_attribute16 IN VARCHAR2 ,
563 x_attribute17 IN VARCHAR2 ,
564 x_attribute18 IN VARCHAR2 ,
565 x_attribute19 IN VARCHAR2 ,
566 x_attribute20 IN VARCHAR2 ,
567 x_attribute21 IN VARCHAR2 ,
568 x_attribute22 IN VARCHAR2 ,
569 x_attribute23 IN VARCHAR2 ,
570 x_attribute24 IN VARCHAR2 ,
571 x_attribute25 IN VARCHAR2 ,
572 x_attribute26 IN VARCHAR2 ,
573 x_attribute27 IN VARCHAR2 ,
574 x_attribute28 IN VARCHAR2 ,
575 x_attribute29 IN VARCHAR2 ,
576 x_attribute30 IN VARCHAR2 ,
577 x_parent_trx_id IN NUMBER ,
578 x_parent_trx_number IN VARCHAR2 ,
579 x_creation_date IN DATE ,
580 x_created_by IN NUMBER ,
581 x_last_update_date IN DATE ,
582 x_last_updated_by IN NUMBER ,
583 x_last_update_login IN NUMBER
584 ) AS
585 /*
586 || Created By : [email protected]
587 || Created On : 02-MAY-2002
588 || Purpose : Initialises the columns, Checks Constraints, Calls the
589 || Trigger Handlers for the table, before any DML operation.
590 || Known limitations, enhancements or remarks :
591 || Change History :
592 || Who When What
593 || (reverse chronological order - newest change first)
594 */
595 BEGIN
596
597 set_column_values (
598 p_action,
599 x_rowid,
600 x_sob_id,
601 x_trx_id,
602 x_dossier_name,
603 x_trx_number,
604 x_packet_id,
605 x_trx_status,
606 x_dossier_id,
607 x_dossier_transaction_name,
608 x_description,
609 x_funds_status,
610 x_attribute_category,
611 x_attribute1,
612 x_attribute2,
613 x_attribute3,
614 x_attribute4,
615 x_attribute5,
616 x_attribute6,
617 x_attribute7,
618 x_attribute8,
619 x_attribute9,
620 x_attribute10,
621 x_attribute11,
622 x_attribute12,
623 x_attribute13,
624 x_attribute14,
625 x_attribute15,
626 x_attribute16,
627 x_attribute17,
628 x_attribute18,
629 x_attribute19,
630 x_attribute20,
631 x_attribute21,
632 x_attribute22,
633 x_attribute23,
634 x_attribute24,
635 x_attribute25,
636 x_attribute26,
637 x_attribute27,
638 x_attribute28,
639 x_attribute29,
640 x_attribute30,
641 x_parent_trx_id,
642 x_parent_trx_number,
643 x_creation_date,
644 x_created_by,
645 x_last_update_date,
646 x_last_updated_by,
647 x_last_update_login
648 );
649
650 IF (p_action = 'INSERT') THEN
651 -- Call all the procedures related to Before Insert.
652 IF ( get_pk_for_validation(
653 new_references.trx_id
654 )
655 ) THEN
656 fnd_message.set_name('FND','FORM_DUPLICATE_KEY_IN_INDEX');
657 -- bug 3199481, start block
658 IF (l_unexp_level >= l_debug_level) THEN
659 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_headers_pkg.before_dml.Msg1',FALSE);
660 END IF;
661 -- bug 3199481, end block
662 app_exception.raise_exception;
663 END IF;
664 check_parent_existance;
665 ELSIF (p_action = 'UPDATE') THEN
666 -- Call all the procedures related to Before Update.
667 BeforeRowUpdate1 ( p_updating => TRUE );
668 check_parent_existance;
669 ELSIF (p_action = 'DELETE') THEN
670 -- Call all the procedures related to Before Delete.
671 check_child_existance;
672 ELSIF (p_action = 'VALIDATE_INSERT') THEN
673 -- Call all the procedures related to Before Insert.
674 IF ( get_pk_for_validation (
675 new_references.trx_id
676 )
677 ) THEN
678 fnd_message.set_name('FND','FORM_DUPLICATE_KEY_IN_INDEX');
679 -- bug 3199481, start block
680 IF (l_unexp_level >= l_debug_level) THEN
681 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_headers_pkg.before_dml.Msg2',FALSE);
682 END IF;
683 -- bug 3199481, end block
684 app_exception.raise_exception;
685 END IF;
686 ELSIF (p_action = 'VALIDATE_DELETE') THEN
687 check_child_existance;
688 END IF;
689
690 END before_dml;
691
692
693 PROCEDURE insert_row (
694 x_rowid IN OUT NOCOPY VARCHAR2,
695 x_sob_id IN NUMBER,
696 x_trx_id IN OUT NOCOPY NUMBER,
697 x_dossier_name IN VARCHAR2,
698 x_trx_number IN VARCHAR2,
699 x_packet_id IN NUMBER,
700 x_trx_status IN VARCHAR2,
701 x_dossier_id IN NUMBER,
702 x_dossier_transaction_name IN VARCHAR2,
703 x_description IN VARCHAR2,
704 x_funds_status IN VARCHAR2,
705 x_attribute_category IN VARCHAR2,
706 x_attribute1 IN VARCHAR2,
707 x_attribute2 IN VARCHAR2,
708 x_attribute3 IN VARCHAR2,
709 x_attribute4 IN VARCHAR2,
710 x_attribute5 IN VARCHAR2,
711 x_attribute6 IN VARCHAR2,
712 x_attribute7 IN VARCHAR2,
713 x_attribute8 IN VARCHAR2,
714 x_attribute9 IN VARCHAR2,
715 x_attribute10 IN VARCHAR2,
716 x_attribute11 IN VARCHAR2,
717 x_attribute12 IN VARCHAR2,
718 x_attribute13 IN VARCHAR2,
719 x_attribute14 IN VARCHAR2,
720 x_attribute15 IN VARCHAR2,
721 x_attribute16 IN VARCHAR2,
722 x_attribute17 IN VARCHAR2,
723 x_attribute18 IN VARCHAR2,
724 x_attribute19 IN VARCHAR2,
725 x_attribute20 IN VARCHAR2,
726 x_attribute21 IN VARCHAR2,
727 x_attribute22 IN VARCHAR2,
728 x_attribute23 IN VARCHAR2,
729 x_attribute24 IN VARCHAR2,
730 x_attribute25 IN VARCHAR2,
731 x_attribute26 IN VARCHAR2,
732 x_attribute27 IN VARCHAR2,
733 x_attribute28 IN VARCHAR2,
734 x_attribute29 IN VARCHAR2,
735 x_attribute30 IN VARCHAR2,
736 x_parent_trx_id IN NUMBER,
737 x_parent_trx_number IN VARCHAR2,
738 x_mode IN VARCHAR2 ) AS
739 /*
740 || Created By : [email protected]
741 || Created On : 02-MAY-2002
742 || Purpose : Handles the INSERT DML logic for the table.
743 || Known limitations, enhancements or remarks :
744 || Change History :
745 || Who When What
746 || (reverse chronological order - newest change first)
747 */
748 CURSOR c IS
749 SELECT rowid
750 FROM igi_dos_trx_headers
751 WHERE trx_id = x_trx_id;
752
753 x_last_update_date DATE;
754 x_last_updated_by NUMBER;
755 x_last_update_login NUMBER;
756
757 l_mode VARCHAR2(1);
758
759 BEGIN
760 IF X_mode is NULL then
761 l_mode :='R';
762 ELSE
763 l_mode := X_mode;
764 END IF;
765
766 x_last_update_date := SYSDATE;
767 IF (l_mode = 'I') THEN
768 x_last_updated_by := 1;
769 x_last_update_login := 0;
770 ELSIF (l_mode = 'R') THEN
771 x_last_updated_by := fnd_global.user_id;
772 IF (x_last_updated_by IS NULL) THEN
773 x_last_updated_by := -1;
774 END IF;
775 x_last_update_login := fnd_global.login_id;
776 IF (x_last_update_login IS NULL) THEN
777 x_last_update_login := -1;
778 END IF;
779 ELSE
780 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
781 -- bug 3199481, start block
782 IF (l_unexp_level >= l_debug_level) THEN
783 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_headers_pkg.insert_row.Msg1',FALSE);
784 END IF;
785 -- bug 3199481, end block
786 app_exception.raise_exception;
787 END IF;
788
789 SELECT igi_dos_trx_headers_s.NEXTVAL
790 INTO x_trx_id
791 FROM dual;
792
793 before_dml(
794 p_action => 'INSERT',
795 x_rowid => x_rowid,
796 x_sob_id => x_sob_id,
797 x_trx_id => x_trx_id,
798 x_dossier_name => x_dossier_name,
799 x_trx_number => x_trx_number,
800 x_packet_id => x_packet_id,
801 x_trx_status => x_trx_status,
802 x_dossier_id => x_dossier_id,
803 x_dossier_transaction_name => x_dossier_transaction_name,
804 x_description => x_description,
805 x_funds_status => x_funds_status,
806 x_attribute_category => x_attribute_category,
807 x_attribute1 => x_attribute1,
808 x_attribute2 => x_attribute2,
809 x_attribute3 => x_attribute3,
810 x_attribute4 => x_attribute4,
811 x_attribute5 => x_attribute5,
812 x_attribute6 => x_attribute6,
813 x_attribute7 => x_attribute7,
814 x_attribute8 => x_attribute8,
815 x_attribute9 => x_attribute9,
816 x_attribute10 => x_attribute10,
817 x_attribute11 => x_attribute11,
818 x_attribute12 => x_attribute12,
819 x_attribute13 => x_attribute13,
820 x_attribute14 => x_attribute14,
821 x_attribute15 => x_attribute15,
822 x_attribute16 => x_attribute16,
823 x_attribute17 => x_attribute17,
824 x_attribute18 => x_attribute18,
825 x_attribute19 => x_attribute19,
826 x_attribute20 => x_attribute20,
827 x_attribute21 => x_attribute21,
828 x_attribute22 => x_attribute22,
829 x_attribute23 => x_attribute23,
830 x_attribute24 => x_attribute24,
831 x_attribute25 => x_attribute25,
832 x_attribute26 => x_attribute26,
833 x_attribute27 => x_attribute27,
834 x_attribute28 => x_attribute28,
835 x_attribute29 => x_attribute29,
836 x_attribute30 => x_attribute30,
837 x_parent_trx_id => x_parent_trx_id,
838 x_parent_trx_number => x_parent_trx_number,
839 x_creation_date => x_last_update_date,
840 x_created_by => x_last_updated_by,
841 x_last_update_date => x_last_update_date,
842 x_last_updated_by => x_last_updated_by,
843 x_last_update_login => x_last_update_login
844 );
845
846 INSERT INTO igi_dos_trx_headers (
847 sob_id,
848 trx_id,
849 dossier_name,
850 trx_number,
851 packet_id,
852 trx_status,
853 dossier_id,
854 dossier_transaction_name,
855 description,
856 funds_status,
857 attribute_category,
858 attribute1,
859 attribute2,
860 attribute3,
861 attribute4,
862 attribute5,
863 attribute6,
864 attribute7,
865 attribute8,
866 attribute9,
867 attribute10,
868 attribute11,
869 attribute12,
870 attribute13,
871 attribute14,
872 attribute15,
873 attribute16,
874 attribute17,
875 attribute18,
876 attribute19,
877 attribute20,
878 attribute21,
879 attribute22,
880 attribute23,
881 attribute24,
882 attribute25,
883 attribute26,
884 attribute27,
885 attribute28,
886 attribute29,
887 attribute30,
888 parent_trx_id,
889 parent_trx_number,
890 creation_date,
891 created_by,
892 last_update_date,
893 last_updated_by,
894 last_update_login
895 ) VALUES (
896 new_references.sob_id,
897 new_references.trx_id,
898 new_references.dossier_name,
899 new_references.trx_number,
900 new_references.packet_id,
901 new_references.trx_status,
902 new_references.dossier_id,
903 new_references.dossier_transaction_name,
904 new_references.description,
905 new_references.funds_status,
906 new_references.attribute_category,
907 new_references.attribute1,
908 new_references.attribute2,
909 new_references.attribute3,
910 new_references.attribute4,
911 new_references.attribute5,
912 new_references.attribute6,
913 new_references.attribute7,
914 new_references.attribute8,
915 new_references.attribute9,
916 new_references.attribute10,
917 new_references.attribute11,
918 new_references.attribute12,
919 new_references.attribute13,
920 new_references.attribute14,
921 new_references.attribute15,
922 new_references.attribute16,
923 new_references.attribute17,
924 new_references.attribute18,
925 new_references.attribute19,
926 new_references.attribute20,
927 new_references.attribute21,
928 new_references.attribute22,
929 new_references.attribute23,
930 new_references.attribute24,
931 new_references.attribute25,
932 new_references.attribute26,
933 new_references.attribute27,
934 new_references.attribute28,
935 new_references.attribute29,
936 new_references.attribute30,
937 new_references.parent_trx_id,
938 new_references.parent_trx_number,
939 x_last_update_date,
940 x_last_updated_by,
941 x_last_update_date,
942 x_last_updated_by,
943 x_last_update_login
944 );
945
946 OPEN c;
947 FETCH c INTO x_rowid;
948 IF (c%NOTFOUND) THEN
949 CLOSE c;
950 RAISE NO_DATA_FOUND;
951 END IF;
952 CLOSE c;
953
954 END insert_row;
955
956
957 PROCEDURE lock_row (
958 x_rowid IN VARCHAR2,
959 x_sob_id IN NUMBER,
960 x_trx_id IN NUMBER,
961 x_dossier_name IN VARCHAR2,
962 x_trx_number IN VARCHAR2,
963 x_packet_id IN NUMBER,
964 x_trx_status IN VARCHAR2,
965 x_dossier_id IN NUMBER,
966 x_dossier_transaction_name IN VARCHAR2,
967 x_description IN VARCHAR2,
968 x_funds_status IN VARCHAR2,
969 x_attribute_category IN VARCHAR2,
970 x_attribute1 IN VARCHAR2,
971 x_attribute2 IN VARCHAR2,
972 x_attribute3 IN VARCHAR2,
973 x_attribute4 IN VARCHAR2,
974 x_attribute5 IN VARCHAR2,
975 x_attribute6 IN VARCHAR2,
976 x_attribute7 IN VARCHAR2,
977 x_attribute8 IN VARCHAR2,
978 x_attribute9 IN VARCHAR2,
979 x_attribute10 IN VARCHAR2,
980 x_attribute11 IN VARCHAR2,
981 x_attribute12 IN VARCHAR2,
982 x_attribute13 IN VARCHAR2,
983 x_attribute14 IN VARCHAR2,
984 x_attribute15 IN VARCHAR2,
985 x_attribute16 IN VARCHAR2,
986 x_attribute17 IN VARCHAR2,
987 x_attribute18 IN VARCHAR2,
988 x_attribute19 IN VARCHAR2,
989 x_attribute20 IN VARCHAR2,
990 x_attribute21 IN VARCHAR2,
991 x_attribute22 IN VARCHAR2,
992 x_attribute23 IN VARCHAR2,
993 x_attribute24 IN VARCHAR2,
994 x_attribute25 IN VARCHAR2,
995 x_attribute26 IN VARCHAR2,
996 x_attribute27 IN VARCHAR2,
997 x_attribute28 IN VARCHAR2,
998 x_attribute29 IN VARCHAR2,
999 x_attribute30 IN VARCHAR2,
1000 x_parent_trx_id IN NUMBER,
1001 x_parent_trx_number IN VARCHAR2
1002 ) AS
1003 /*
1004 || Created By : [email protected]
1005 || Created On : 02-MAY-2002
1006 || Purpose : Handles the LOCK mechanism for the table.
1007 || Known limitations, enhancements or remarks :
1008 || Change History :
1009 || Who When What
1010 || (reverse chronological order - newest change first)
1011 */
1012 CURSOR c1 IS
1013 SELECT
1014 sob_id,
1015 dossier_name,
1016 trx_number,
1017 packet_id,
1018 trx_status,
1019 dossier_id,
1020 dossier_transaction_name,
1021 description,
1022 funds_status,
1023 attribute_category,
1024 attribute1,
1025 attribute2,
1026 attribute3,
1027 attribute4,
1028 attribute5,
1029 attribute6,
1030 attribute7,
1031 attribute8,
1032 attribute9,
1033 attribute10,
1034 attribute11,
1035 attribute12,
1036 attribute13,
1037 attribute14,
1038 attribute15,
1039 attribute16,
1040 attribute17,
1041 attribute18,
1042 attribute19,
1043 attribute20,
1044 attribute21,
1045 attribute22,
1046 attribute23,
1047 attribute24,
1048 attribute25,
1049 attribute26,
1050 attribute27,
1051 attribute28,
1052 attribute29,
1053 attribute30,
1054 parent_trx_id,
1055 parent_trx_number
1056 FROM igi_dos_trx_headers
1057 WHERE rowid = x_rowid
1058 FOR UPDATE NOWAIT;
1059
1060 tlinfo c1%ROWTYPE;
1061
1062 BEGIN
1063
1064 OPEN c1;
1065 FETCH c1 INTO tlinfo;
1066 IF (c1%notfound) THEN
1067 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1068 -- bug 3199481, start block
1069 IF (l_unexp_level >= l_debug_level) THEN
1070 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_headers_pkg.lock_row.Msg1',FALSE);
1071 END IF;
1072 -- bug 3199481, end block
1073 CLOSE c1;
1074 app_exception.raise_exception;
1075 RETURN;
1076 END IF;
1077 CLOSE c1;
1078
1079 IF (
1080 ((tlinfo.sob_id = x_sob_id) OR ((tlinfo.sob_id IS NULL) AND (X_sob_id IS NULL)))
1081 AND (tlinfo.dossier_name = x_dossier_name)
1082 AND ((tlinfo.trx_number = x_trx_number) OR ((tlinfo.trx_number IS NULL) AND (X_trx_number IS NULL)))
1083 AND ((tlinfo.packet_id = x_packet_id) OR ((tlinfo.packet_id IS NULL) AND (X_packet_id IS NULL)))
1084 AND ((tlinfo.trx_status = x_trx_status) OR ((tlinfo.trx_status IS NULL) AND (X_trx_status IS NULL)))
1085 AND ((tlinfo.dossier_id = x_dossier_id) OR ((tlinfo.dossier_id IS NULL) AND (X_dossier_id IS NULL)))
1086 AND ((tlinfo.dossier_transaction_name = x_dossier_transaction_name) OR ((tlinfo.dossier_transaction_name IS NULL) AND (X_dossier_transaction_name IS NULL)))
1087 AND ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
1088 AND ((tlinfo.funds_status = x_funds_status) OR ((tlinfo.funds_status IS NULL) AND (X_funds_status IS NULL)))
1089 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1090 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1091 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1092 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1093 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1094 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1095 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1096 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1097 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1098 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1099 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1100 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1101 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1102 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1103 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1104 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1105 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1106 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1107 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1108 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1109 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1110 AND ((tlinfo.attribute21 = x_attribute21) OR ((tlinfo.attribute21 IS NULL) AND (X_attribute21 IS NULL)))
1111 AND ((tlinfo.attribute22 = x_attribute22) OR ((tlinfo.attribute22 IS NULL) AND (X_attribute22 IS NULL)))
1112 AND ((tlinfo.attribute23 = x_attribute23) OR ((tlinfo.attribute23 IS NULL) AND (X_attribute23 IS NULL)))
1113 AND ((tlinfo.attribute24 = x_attribute24) OR ((tlinfo.attribute24 IS NULL) AND (X_attribute24 IS NULL)))
1114 AND ((tlinfo.attribute25 = x_attribute25) OR ((tlinfo.attribute25 IS NULL) AND (X_attribute25 IS NULL)))
1115 AND ((tlinfo.attribute26 = x_attribute26) OR ((tlinfo.attribute26 IS NULL) AND (X_attribute26 IS NULL)))
1116 AND ((tlinfo.attribute27 = x_attribute27) OR ((tlinfo.attribute27 IS NULL) AND (X_attribute27 IS NULL)))
1117 AND ((tlinfo.attribute28 = x_attribute28) OR ((tlinfo.attribute28 IS NULL) AND (X_attribute28 IS NULL)))
1118 AND ((tlinfo.attribute29 = x_attribute29) OR ((tlinfo.attribute29 IS NULL) AND (X_attribute29 IS NULL)))
1119 AND ((tlinfo.attribute30 = x_attribute30) OR ((tlinfo.attribute30 IS NULL) AND (X_attribute30 IS NULL)))
1120 AND ((tlinfo.parent_trx_id = x_parent_trx_id) OR ((tlinfo.parent_trx_id IS NULL) AND (X_parent_trx_id IS NULL)))
1121 AND ((tlinfo.parent_trx_number = x_parent_trx_number) OR ((tlinfo.parent_trx_number IS NULL) AND (X_parent_trx_number IS NULL)))
1122 ) THEN
1123 NULL;
1124 ELSE
1125 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1126 -- bug 3199481, start block
1127 IF (l_unexp_level >= l_debug_level) THEN
1128 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_headers_pkg.lock_row.Msg1',FALSE);
1129 END IF;
1130 -- bug 3199481, end block
1131 app_exception.raise_exception;
1132 END IF;
1133
1134 RETURN;
1135
1136 END lock_row;
1137
1138
1139 PROCEDURE update_row (
1140 x_rowid IN VARCHAR2,
1141 x_sob_id IN NUMBER,
1142 x_trx_id IN NUMBER,
1143 x_dossier_name IN VARCHAR2,
1144 x_trx_number IN VARCHAR2,
1145 x_packet_id IN NUMBER,
1146 x_trx_status IN VARCHAR2,
1147 x_dossier_id IN NUMBER,
1148 x_dossier_transaction_name IN VARCHAR2,
1149 x_description IN VARCHAR2,
1150 x_funds_status IN VARCHAR2,
1151 x_attribute_category IN VARCHAR2,
1152 x_attribute1 IN VARCHAR2,
1153 x_attribute2 IN VARCHAR2,
1154 x_attribute3 IN VARCHAR2,
1155 x_attribute4 IN VARCHAR2,
1156 x_attribute5 IN VARCHAR2,
1157 x_attribute6 IN VARCHAR2,
1158 x_attribute7 IN VARCHAR2,
1159 x_attribute8 IN VARCHAR2,
1160 x_attribute9 IN VARCHAR2,
1161 x_attribute10 IN VARCHAR2,
1162 x_attribute11 IN VARCHAR2,
1163 x_attribute12 IN VARCHAR2,
1164 x_attribute13 IN VARCHAR2,
1165 x_attribute14 IN VARCHAR2,
1166 x_attribute15 IN VARCHAR2,
1167 x_attribute16 IN VARCHAR2,
1168 x_attribute17 IN VARCHAR2,
1169 x_attribute18 IN VARCHAR2,
1170 x_attribute19 IN VARCHAR2,
1171 x_attribute20 IN VARCHAR2,
1172 x_attribute21 IN VARCHAR2,
1173 x_attribute22 IN VARCHAR2,
1174 x_attribute23 IN VARCHAR2,
1175 x_attribute24 IN VARCHAR2,
1176 x_attribute25 IN VARCHAR2,
1177 x_attribute26 IN VARCHAR2,
1178 x_attribute27 IN VARCHAR2,
1179 x_attribute28 IN VARCHAR2,
1180 x_attribute29 IN VARCHAR2,
1181 x_attribute30 IN VARCHAR2,
1182 x_parent_trx_id IN NUMBER,
1183 x_parent_trx_number IN VARCHAR2,
1184 x_mode IN VARCHAR2 ) AS
1185 /*
1186 || Created By : [email protected]
1187 || Created On : 02-MAY-2002
1188 || Purpose : Handles the UPDATE DML logic for the table.
1189 || Known limitations, enhancements or remarks :
1190 || Change History :
1191 || Who When What
1192 || (reverse chronological order - newest change first)
1193 */
1194 x_last_update_date DATE ;
1195 x_last_updated_by NUMBER;
1196 x_last_update_login NUMBER;
1197
1198 l_mode VARCHAR2(1);
1199
1200 BEGIN
1201
1202 IF X_mode is NULL then
1203 l_mode :='R';
1204 ELSE
1205 l_mode := X_mode;
1206 END IF;
1207
1208 x_last_update_date := SYSDATE;
1209 IF (l_MODE = 'I') THEN
1210 x_last_updated_by := 1;
1211 x_last_update_login := 0;
1212 ELSIF (l_mode = 'R') THEN
1213 x_last_updated_by := fnd_global.user_id;
1214 IF x_last_updated_by IS NULL THEN
1215 x_last_updated_by := -1;
1216 END IF;
1217 x_last_update_login := fnd_global.login_id;
1218 IF (x_last_update_login IS NULL) THEN
1219 x_last_update_login := -1;
1220 END IF;
1221 ELSE
1222 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1223 -- bug 3199481, start block
1224 IF (l_unexp_level >= l_debug_level) THEN
1225 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_headers_pkg.update_row.Msg1',FALSE);
1226 END IF;
1227 -- bug 3199481, end block
1228 app_exception.raise_exception;
1229 END IF;
1230
1231 before_dml(
1232 p_action => 'UPDATE',
1233 x_rowid => x_rowid,
1234 x_sob_id => x_sob_id,
1235 x_trx_id => x_trx_id,
1236 x_dossier_name => x_dossier_name,
1237 x_trx_number => x_trx_number,
1238 x_packet_id => x_packet_id,
1239 x_trx_status => x_trx_status,
1240 x_dossier_id => x_dossier_id,
1241 x_dossier_transaction_name => x_dossier_transaction_name,
1242 x_description => x_description,
1243 x_funds_status => x_funds_status,
1244 x_attribute_category => x_attribute_category,
1245 x_attribute1 => x_attribute1,
1246 x_attribute2 => x_attribute2,
1247 x_attribute3 => x_attribute3,
1248 x_attribute4 => x_attribute4,
1249 x_attribute5 => x_attribute5,
1250 x_attribute6 => x_attribute6,
1251 x_attribute7 => x_attribute7,
1252 x_attribute8 => x_attribute8,
1253 x_attribute9 => x_attribute9,
1254 x_attribute10 => x_attribute10,
1255 x_attribute11 => x_attribute11,
1256 x_attribute12 => x_attribute12,
1257 x_attribute13 => x_attribute13,
1258 x_attribute14 => x_attribute14,
1259 x_attribute15 => x_attribute15,
1260 x_attribute16 => x_attribute16,
1261 x_attribute17 => x_attribute17,
1262 x_attribute18 => x_attribute18,
1263 x_attribute19 => x_attribute19,
1264 x_attribute20 => x_attribute20,
1265 x_attribute21 => x_attribute21,
1266 x_attribute22 => x_attribute22,
1267 x_attribute23 => x_attribute23,
1268 x_attribute24 => x_attribute24,
1269 x_attribute25 => x_attribute25,
1270 x_attribute26 => x_attribute26,
1271 x_attribute27 => x_attribute27,
1272 x_attribute28 => x_attribute28,
1273 x_attribute29 => x_attribute29,
1274 x_attribute30 => x_attribute30,
1275 x_parent_trx_id => x_parent_trx_id,
1276 x_parent_trx_number => x_parent_trx_number,
1277 x_creation_date => x_last_update_date,
1278 x_created_by => x_last_updated_by,
1279 x_last_update_date => x_last_update_date,
1280 x_last_updated_by => x_last_updated_by,
1281 x_last_update_login => x_last_update_login
1282 );
1283
1284 UPDATE igi_dos_trx_headers
1285 SET
1286 sob_id = new_references.sob_id,
1287 dossier_name = new_references.dossier_name,
1288 trx_number = new_references.trx_number,
1289 packet_id = new_references.packet_id,
1290 trx_status = new_references.trx_status,
1291 dossier_id = new_references.dossier_id,
1292 dossier_transaction_name = new_references.dossier_transaction_name,
1293 description = new_references.description,
1294 funds_status = new_references.funds_status,
1295 attribute_category = new_references.attribute_category,
1296 attribute1 = new_references.attribute1,
1297 attribute2 = new_references.attribute2,
1298 attribute3 = new_references.attribute3,
1299 attribute4 = new_references.attribute4,
1300 attribute5 = new_references.attribute5,
1301 attribute6 = new_references.attribute6,
1302 attribute7 = new_references.attribute7,
1303 attribute8 = new_references.attribute8,
1304 attribute9 = new_references.attribute9,
1305 attribute10 = new_references.attribute10,
1306 attribute11 = new_references.attribute11,
1307 attribute12 = new_references.attribute12,
1308 attribute13 = new_references.attribute13,
1309 attribute14 = new_references.attribute14,
1310 attribute15 = new_references.attribute15,
1311 attribute16 = new_references.attribute16,
1312 attribute17 = new_references.attribute17,
1313 attribute18 = new_references.attribute18,
1314 attribute19 = new_references.attribute19,
1315 attribute20 = new_references.attribute20,
1316 attribute21 = new_references.attribute21,
1317 attribute22 = new_references.attribute22,
1318 attribute23 = new_references.attribute23,
1319 attribute24 = new_references.attribute24,
1320 attribute25 = new_references.attribute25,
1321 attribute26 = new_references.attribute26,
1322 attribute27 = new_references.attribute27,
1323 attribute28 = new_references.attribute28,
1324 attribute29 = new_references.attribute29,
1325 attribute30 = new_references.attribute30,
1326 parent_trx_id = new_references.parent_trx_id,
1327 parent_trx_number = new_references.parent_trx_number,
1328 last_update_date = x_last_update_date,
1329 last_updated_by = x_last_updated_by,
1330 last_update_login = x_last_update_login
1331 WHERE rowid = x_rowid;
1332
1333 IF (SQL%NOTFOUND) THEN
1334 RAISE NO_DATA_FOUND;
1335 END IF;
1336
1337 END update_row;
1338
1339
1340 PROCEDURE add_row (
1341 x_rowid IN OUT NOCOPY VARCHAR2,
1342 x_sob_id IN NUMBER,
1343 x_trx_id IN OUT NOCOPY NUMBER,
1344 x_dossier_name IN VARCHAR2,
1345 x_trx_number IN VARCHAR2,
1346 x_packet_id IN NUMBER,
1347 x_trx_status IN VARCHAR2,
1348 x_dossier_id IN NUMBER,
1349 x_dossier_transaction_name IN VARCHAR2,
1350 x_description IN VARCHAR2,
1351 x_funds_status IN VARCHAR2,
1352 x_attribute_category IN VARCHAR2,
1353 x_attribute1 IN VARCHAR2,
1354 x_attribute2 IN VARCHAR2,
1355 x_attribute3 IN VARCHAR2,
1356 x_attribute4 IN VARCHAR2,
1357 x_attribute5 IN VARCHAR2,
1358 x_attribute6 IN VARCHAR2,
1359 x_attribute7 IN VARCHAR2,
1360 x_attribute8 IN VARCHAR2,
1361 x_attribute9 IN VARCHAR2,
1362 x_attribute10 IN VARCHAR2,
1363 x_attribute11 IN VARCHAR2,
1364 x_attribute12 IN VARCHAR2,
1365 x_attribute13 IN VARCHAR2,
1366 x_attribute14 IN VARCHAR2,
1367 x_attribute15 IN VARCHAR2,
1368 x_attribute16 IN VARCHAR2,
1369 x_attribute17 IN VARCHAR2,
1370 x_attribute18 IN VARCHAR2,
1371 x_attribute19 IN VARCHAR2,
1372 x_attribute20 IN VARCHAR2,
1373 x_attribute21 IN VARCHAR2,
1374 x_attribute22 IN VARCHAR2,
1375 x_attribute23 IN VARCHAR2,
1376 x_attribute24 IN VARCHAR2,
1377 x_attribute25 IN VARCHAR2,
1378 x_attribute26 IN VARCHAR2,
1379 x_attribute27 IN VARCHAR2,
1380 x_attribute28 IN VARCHAR2,
1381 x_attribute29 IN VARCHAR2,
1382 x_attribute30 IN VARCHAR2,
1383 x_parent_trx_id IN NUMBER,
1384 x_parent_trx_number IN VARCHAR2,
1385 x_mode IN VARCHAR2 ) AS
1386 /*
1387 || Created By : [email protected]
1388 || Created On : 02-MAY-2002
1389 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1390 || Known limitations, enhancements or remarks :
1391 || Change History :
1392 || Who When What
1393 || (reverse chronological order - newest change first)
1394 */
1395 CURSOR c1 IS
1396 SELECT rowid
1397 FROM igi_dos_trx_headers
1398 WHERE trx_id = x_trx_id;
1399
1400 l_mode VARCHAR2(1);
1401
1402 BEGIN
1403 IF X_mode is NULL then
1404 l_mode :='R';
1405 ELSE
1406 l_mode := X_mode;
1407 END IF;
1408
1409 OPEN c1;
1410 FETCH c1 INTO x_rowid;
1411 IF (c1%NOTFOUND) THEN
1412 CLOSE c1;
1413
1414 insert_row (
1415 x_rowid,
1416 x_sob_id,
1417 x_trx_id,
1418 x_dossier_name,
1419 x_trx_number,
1420 x_packet_id,
1421 x_trx_status,
1422 x_dossier_id,
1423 x_dossier_transaction_name,
1424 x_description,
1425 x_funds_status,
1426 x_attribute_category,
1427 x_attribute1,
1428 x_attribute2,
1429 x_attribute3,
1430 x_attribute4,
1431 x_attribute5,
1432 x_attribute6,
1433 x_attribute7,
1434 x_attribute8,
1435 x_attribute9,
1436 x_attribute10,
1437 x_attribute11,
1438 x_attribute12,
1439 x_attribute13,
1440 x_attribute14,
1441 x_attribute15,
1442 x_attribute16,
1443 x_attribute17,
1444 x_attribute18,
1445 x_attribute19,
1446 x_attribute20,
1447 x_attribute21,
1448 x_attribute22,
1449 x_attribute23,
1450 x_attribute24,
1451 x_attribute25,
1452 x_attribute26,
1453 x_attribute27,
1454 x_attribute28,
1455 x_attribute29,
1456 x_attribute30,
1457 x_parent_trx_id,
1458 x_parent_trx_number,
1459 l_mode
1460 );
1461 RETURN;
1462 END IF;
1463 CLOSE c1;
1464
1465 update_row (
1466 x_rowid,
1467 x_sob_id,
1468 x_trx_id,
1469 x_dossier_name,
1470 x_trx_number,
1471 x_packet_id,
1472 x_trx_status,
1473 x_dossier_id,
1474 x_dossier_transaction_name,
1475 x_description,
1476 x_funds_status,
1477 x_attribute_category,
1478 x_attribute1,
1479 x_attribute2,
1480 x_attribute3,
1481 x_attribute4,
1482 x_attribute5,
1483 x_attribute6,
1484 x_attribute7,
1485 x_attribute8,
1486 x_attribute9,
1487 x_attribute10,
1488 x_attribute11,
1489 x_attribute12,
1490 x_attribute13,
1491 x_attribute14,
1492 x_attribute15,
1493 x_attribute16,
1494 x_attribute17,
1495 x_attribute18,
1496 x_attribute19,
1497 x_attribute20,
1498 x_attribute21,
1499 x_attribute22,
1500 x_attribute23,
1501 x_attribute24,
1502 x_attribute25,
1503 x_attribute26,
1504 x_attribute27,
1505 x_attribute28,
1506 x_attribute29,
1507 x_attribute30,
1508 x_parent_trx_id,
1509 x_parent_trx_number,
1510 l_mode
1511 );
1512
1513 END add_row;
1514
1515
1516 PROCEDURE delete_row (
1517 x_rowid IN VARCHAR2
1518 ) AS
1519 /*
1520 || Created By : [email protected]
1521 || Created On : 02-MAY-2002
1522 || Purpose : Handles the DELETE DML logic for the table.
1523 || Known limitations, enhancements or remarks :
1524 || Change History :
1525 || Who When What
1526 || (reverse chronological order - newest change first)
1527 */
1528 BEGIN
1529
1530 before_dml (
1531 p_action => 'DELETE',
1532 x_rowid => x_rowid,
1533 x_sob_id=>NULL,
1534 x_trx_id=>NULL,
1535 x_dossier_name=>NULL,
1536 x_trx_number =>NULL,
1537 x_packet_id=>NULL,
1538 x_trx_status =>NULL,
1539 x_dossier_id=>NULL,
1540 x_dossier_transaction_name=>NULL,
1541 x_description =>NULL,
1542 x_funds_status=>NULL,
1543 x_attribute_category=>NULL,
1544 x_attribute1 =>NULL,
1545 x_attribute2 =>NULL,
1546 x_attribute3 =>NULL,
1547 x_attribute4 =>NULL,
1548 x_attribute5 =>NULL,
1549 x_attribute6 =>NULL,
1550 x_attribute7 =>NULL,
1551 x_attribute8 =>NULL,
1552 x_attribute9 =>NULL,
1553 x_attribute10 =>NULL,
1554 x_attribute11 =>NULL,
1555 x_attribute12 =>NULL,
1556 x_attribute13 =>NULL,
1557 x_attribute14 =>NULL,
1558 x_attribute15 =>NULL,
1559 x_attribute16 =>NULL,
1560 x_attribute17 =>NULL,
1561 x_attribute18 =>NULL,
1562 x_attribute19 =>NULL,
1563 x_attribute20 =>NULL,
1564 x_attribute21 =>NULL,
1565 x_attribute22 =>NULL,
1566 x_attribute23 =>NULL,
1567 x_attribute24 =>NULL,
1568 x_attribute25 =>NULL,
1569 x_attribute26 =>NULL,
1570 x_attribute27 =>NULL,
1571 x_attribute28 =>NULL,
1572 x_attribute29 =>NULL,
1573 x_attribute30 =>NULL,
1574 x_parent_trx_id=>NULL,
1575 x_parent_trx_number=>NULL,
1576 x_creation_date=>NULL,
1577 x_created_by=>NULL,
1578 x_last_update_date=>NULL,
1579 x_last_updated_by=>NULL,
1580 x_last_update_login=>NULL
1581
1582 );
1583
1584 DELETE FROM igi_dos_trx_headers
1585 WHERE rowid = x_rowid;
1586
1587 IF (SQL%NOTFOUND) THEN
1588 RAISE NO_DATA_FOUND;
1589 END IF;
1590
1591 END delete_row;
1592 BEGIN
1593 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
1594
1595 l_state_level := FND_LOG.LEVEL_STATEMENT ;
1596 l_proc_level := FND_LOG.LEVEL_PROCEDURE ;
1597 l_event_level := FND_LOG.LEVEL_EVENT ;
1598 l_excep_level := FND_LOG.LEVEL_EXCEPTION ;
1599 l_error_level := FND_LOG.LEVEL_ERROR ;
1600 l_unexp_level := FND_LOG.LEVEL_UNEXPECTED ;
1601
1602 END igi_dos_trx_headers_pkg;