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