[Home] [Help]
PACKAGE BODY: APPS.IGI_DOS_TRX_DEST_PKG
Source
1 PACKAGE BODY igi_dos_trx_dest_pkg AS
2 /* $Header: igidosrb.pls 120.8.12000000.2 2007/06/14 05:57:08 pshivara 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_dest%ROWTYPE;
15 new_references igi_dos_trx_dest%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_dest_trx_id IN NUMBER ,
23 x_source_trx_id IN NUMBER ,
24 x_source_id IN NUMBER ,
25 x_destination_id IN NUMBER ,
26 x_code_combination_id IN NUMBER ,
27 x_profile_code IN VARCHAR2 ,
28 x_budget_name IN VARCHAR2 ,
29 x_budget_entity_id IN NUMBER ,
30 x_budget_amount IN NUMBER ,
31 x_funds_available IN NUMBER ,
32 x_new_balance IN NUMBER ,
33 x_currency_code IN VARCHAR2 ,
34 x_visible_segments IN VARCHAR2 ,
35 x_actual_segments IN VARCHAR2 ,
36 x_m_budget_amount IN NUMBER ,
37 x_m_budget_amt_exch_rate IN NUMBER ,
38 x_m_budget_amt_exch_rate_type IN VARCHAR2 ,
39 x_m_budget_amt_exch_date IN DATE ,
40 x_m_budget_amt_exch_status IN VARCHAR2 ,
41 x_m_funds_avail IN NUMBER ,
42 x_m_funds_avail_exch_rate IN NUMBER ,
43 x_m_funds_avail_exch_rate_type IN VARCHAR2 ,
44 x_m_funds_avail_exch_date IN DATE ,
45 x_m_funds_avail_exch_status IN VARCHAR2 ,
46 x_m_new_balance IN NUMBER ,
47 x_m_new_balance_exch_rate IN NUMBER ,
48 x_m_new_balance_exch_rate_type IN VARCHAR2 ,
49 x_m_new_balance_exch_date IN DATE ,
50 x_m_new_balance_exch_status IN VARCHAR2 ,
51 x_dossier_id IN NUMBER ,
52 x_budget_version_id IN NUMBER ,
53 x_period_name IN VARCHAR2 ,
54 x_percentage IN NUMBER ,
55 x_status IN VARCHAR2 ,
56 x_group_id IN NUMBER ,
57 x_quarter_num IN NUMBER ,
58 x_period_year IN NUMBER ,
59 x_period_num IN NUMBER ,
60 x_line_num IN NUMBER ,
61 x_segment1 IN VARCHAR2 ,
62 x_segment2 IN VARCHAR2 ,
63 x_segment3 IN VARCHAR2 ,
64 x_segment4 IN VARCHAR2 ,
65 x_segment5 IN VARCHAR2 ,
66 x_segment6 IN VARCHAR2 ,
67 x_segment7 IN VARCHAR2 ,
68 x_segment8 IN VARCHAR2 ,
69 x_segment9 IN VARCHAR2 ,
70 x_segment10 IN VARCHAR2 ,
71 x_segment11 IN VARCHAR2 ,
72 x_segment12 IN VARCHAR2 ,
73 x_segment13 IN VARCHAR2 ,
74 x_segment14 IN VARCHAR2 ,
75 x_segment15 IN VARCHAR2 ,
76 x_segment16 IN VARCHAR2 ,
77 x_segment17 IN VARCHAR2 ,
78 x_segment18 IN VARCHAR2 ,
79 x_segment19 IN VARCHAR2 ,
80 x_segment20 IN VARCHAR2 ,
81 x_segment21 IN VARCHAR2 ,
82 x_segment22 IN VARCHAR2 ,
83 x_segment23 IN VARCHAR2 ,
84 x_segment24 IN VARCHAR2 ,
85 x_segment25 IN VARCHAR2 ,
86 x_segment26 IN VARCHAR2 ,
87 x_segment27 IN VARCHAR2 ,
88 x_segment28 IN VARCHAR2 ,
89 x_segment29 IN VARCHAR2 ,
90 x_segment30 IN VARCHAR2 ,
91 x_creation_date IN DATE ,
92 x_created_by IN NUMBER ,
93 x_last_update_date IN DATE ,
94 x_last_updated_by IN NUMBER ,
95 x_last_update_login IN NUMBER
96 ) AS
97 /*
98 || Created By : [email protected]
99 || Created On : 02-MAY-2002
100 || Purpose : Initialises the Old and New references for the columns of the table.
101 || Known limitations, enhancements or remarks :
102 || Change History :
103 || Who When What
104 || (reverse chronological order - newest change first)
105 */
106
107 CURSOR cur_old_ref_values IS
108 SELECT *
109 FROM igi_dos_trx_dest
110 WHERE rowid = x_rowid;
111
112 BEGIN
113
114 l_rowid := x_rowid;
115
116 -- Code for setting the Old and New Reference Values.
117 -- Populate Old Values.
118 OPEN cur_old_ref_values;
119 FETCH cur_old_ref_values INTO old_references;
120 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
121 CLOSE cur_old_ref_values;
122 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
123 -- bug 3199481, start block
124 IF (l_unexp_level >= l_debug_level) THEN
125 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.set_column_values.Msg1',FALSE);
126 END IF;
127 -- bug 3199481, end block
128 app_exception.raise_exception;
129 RETURN;
130 END IF;
131 CLOSE cur_old_ref_values;
132
133 -- Populate New Values.
134 new_references.sob_id := x_sob_id;
135 new_references.trx_id := x_trx_id;
136 new_references.dest_trx_id := x_dest_trx_id;
137 new_references.source_trx_id := x_source_trx_id;
138 new_references.source_id := x_source_id;
139 new_references.destination_id := x_destination_id;
140 new_references.code_combination_id := x_code_combination_id;
141 new_references.profile_code := x_profile_code;
142 new_references.budget_name := x_budget_name;
143 new_references.budget_entity_id := x_budget_entity_id;
144 new_references.budget_amount := x_budget_amount;
145 new_references.funds_available := x_funds_available;
146 new_references.new_balance := x_new_balance;
147 new_references.currency_code := x_currency_code;
148 new_references.visible_segments := x_visible_segments;
149 new_references.actual_segments := x_actual_segments;
150 new_references.mrc_budget_amount := x_m_budget_amount;
151 new_references.mrc_budget_amt_exch_rate := x_m_budget_amt_exch_rate;
152 new_references.mrc_budget_amt_exch_rate_type := x_m_budget_amt_exch_rate_type;
153 new_references.mrc_budget_amt_exch_date := x_m_budget_amt_exch_date;
154 new_references.mrc_budget_amt_exch_status := x_m_budget_amt_exch_status;
155 new_references.mrc_funds_avail := x_m_funds_avail;
156 new_references.mrc_funds_avail_exch_rate := x_m_funds_avail_exch_rate;
157 new_references.mrc_funds_avail_exch_rate_type := x_m_funds_avail_exch_rate_type;
158 new_references.mrc_funds_avail_exch_date := x_m_funds_avail_exch_date;
159 new_references.mrc_funds_avail_exch_status := x_m_funds_avail_exch_status;
160 new_references.mrc_new_balance := x_m_new_balance;
161 new_references.mrc_new_balance_exch_rate := x_m_new_balance_exch_rate;
162 new_references.mrc_new_balance_exch_rate_type := x_m_new_balance_exch_rate_type;
163 new_references.mrc_new_balance_exch_date := x_m_new_balance_exch_date;
164 new_references.mrc_new_balance_exch_status := x_m_new_balance_exch_status;
168 new_references.percentage := x_percentage;
165 new_references.dossier_id := x_dossier_id;
166 new_references.budget_version_id := x_budget_version_id;
167 new_references.period_name := x_period_name;
169 new_references.status := x_status;
170 new_references.group_id := x_group_id;
171 new_references.quarter_num := x_quarter_num;
172 new_references.period_year := x_period_year;
173 new_references.period_num := x_period_num;
174 new_references.line_num := x_line_num;
175 new_references.segment1 := x_segment1;
176 new_references.segment2 := x_segment2;
177 new_references.segment3 := x_segment3;
178 new_references.segment4 := x_segment4;
179 new_references.segment5 := x_segment5;
180 new_references.segment6 := x_segment6;
181 new_references.segment7 := x_segment7;
182 new_references.segment8 := x_segment8;
183 new_references.segment9 := x_segment9;
184 new_references.segment10 := x_segment10;
185 new_references.segment11 := x_segment11;
186 new_references.segment12 := x_segment12;
187 new_references.segment13 := x_segment13;
188 new_references.segment14 := x_segment14;
189 new_references.segment15 := x_segment15;
190 new_references.segment16 := x_segment16;
191 new_references.segment17 := x_segment17;
192 new_references.segment18 := x_segment18;
193 new_references.segment19 := x_segment19;
194 new_references.segment20 := x_segment20;
195 new_references.segment21 := x_segment21;
196 new_references.segment22 := x_segment22;
197 new_references.segment23 := x_segment23;
198 new_references.segment24 := x_segment24;
199 new_references.segment25 := x_segment25;
200 new_references.segment26 := x_segment26;
201 new_references.segment27 := x_segment27;
202 new_references.segment28 := x_segment28;
203 new_references.segment29 := x_segment29;
204 new_references.segment30 := x_segment30;
205
206 IF (p_action = 'UPDATE') THEN
207 new_references.creation_date := old_references.creation_date;
208 new_references.created_by := old_references.created_by;
209 ELSE
210 new_references.creation_date := x_creation_date;
211 new_references.created_by := x_created_by;
212 END IF;
213
214 new_references.last_update_date := x_last_update_date;
215 new_references.last_updated_by := x_last_updated_by;
216 new_references.last_update_login := x_last_update_login;
217
218 END set_column_values;
219
220
221 PROCEDURE check_parent_existance AS
222 /*
223 || Created By : [email protected]
224 || Created On : 02-MAY-2002
225 || Purpose : Checks for the existance of Parent records.
226 || Known limitations, enhancements or remarks :
227 || Change History :
228 || Who When What
229 || (reverse chronological order - newest change first)
230 */
231 BEGIN
232
233 IF (((old_references.source_trx_id = new_references.source_trx_id)) OR
234 ((new_references.source_trx_id IS NULL))) THEN
235 NULL;
236 ELSIF NOT igi_dos_trx_sources_pkg.get_pk_for_validation (
237 new_references.source_trx_id
238 ) THEN
239 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
240 -- bug 3199481, start block
241 IF (l_unexp_level >= l_debug_level) THEN
242 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.check_parent_existance.Msg1',FALSE);
243 END IF;
244 -- bug 3199481, end block
245 app_exception.raise_exception;
246 END IF;
247
248 IF (((old_references.trx_id = new_references.trx_id)) OR
249 ((new_references.trx_id IS NULL))) THEN
250 NULL;
251 ELSIF NOT igi_dos_trx_headers_pkg.get_pk_for_validation (
252 new_references.trx_id
253 ) THEN
254 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
255 -- bug 3199481, start block
256 IF (l_unexp_level >= l_debug_level) THEN
257 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.check_parent_existance.Msg2',FALSE);
258 END IF;
259 -- bug 3199481, end block
260 app_exception.raise_exception;
261 END IF;
262
263 IF (((old_references.dossier_id = new_references.dossier_id)) OR
264 ((new_references.dossier_id IS NULL))) THEN
265 NULL;
266 ELSIF NOT igi_dos_doc_types_pkg.get_pk_for_validation (
267 new_references.dossier_id
268 ) THEN
269 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
270 -- bug 3199481, start block
271 IF (l_unexp_level >= l_debug_level) THEN
272 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.check_parent_existance.Msg3',FALSE);
273 END IF;
274 -- bug 3199481, end block
275 app_exception.raise_exception;
276 END IF;
277
278 END check_parent_existance;
282 /*
279
280
281 PROCEDURE check_child_existance AS
283 || Created By : [email protected]
284 || Created On : 02-MAY-2002
285 || Purpose : Checks for the existance of Child records.
286 || Known limitations, enhancements or remarks :
287 || Change History :
288 || Who When What
289 || (reverse chronological order - newest change first)
290 */
291 BEGIN
292
293 /* sowsubra 06-JUN-2002 start */
294 /* Commented the following code as this igi_dos_trx_dest_hist_pkg
295 is currently not present */
296
297 null;
298 /*igi_dos_trx_dest_hist_pkg.get_fk_igi_dos_trx_dest (
299 old_references.dest_trx_id );
300 /* sowsubra 06-JUN-2002 end */
301
302 END check_child_existance;
303
304
305 FUNCTION get_pk_for_validation (
306 x_dest_trx_id IN NUMBER
307 ) RETURN BOOLEAN AS
308 /*
309 || Created By : [email protected]
310 || Created On : 02-MAY-2002
311 || Purpose : Validates the Primary Key of the table.
312 || Known limitations, enhancements or remarks :
313 || Change History :
314 || Who When What
315 || (reverse chronological order - newest change first)
316 */
317 CURSOR cur_rowid IS
318 SELECT rowid
319 FROM igi_dos_trx_dest
320 WHERE dest_trx_id = x_dest_trx_id
321 FOR UPDATE NOWAIT;
322
323 lv_rowid cur_rowid%RowType;
324
325 BEGIN
326
327 OPEN cur_rowid;
328 FETCH cur_rowid INTO lv_rowid;
329 IF (cur_rowid%FOUND) THEN
330 CLOSE cur_rowid;
331 RETURN(TRUE);
332 ELSE
333 CLOSE cur_rowid;
334 RETURN(FALSE);
335 END IF;
336
337 END get_pk_for_validation;
338
339
340 PROCEDURE get_fk_igi_dos_trx_sources (
341 x_source_trx_id IN NUMBER
342 ) AS
343 /*
344 || Created By : [email protected]
345 || Created On : 02-MAY-2002
346 || Purpose : Validates the Foreign Keys for the table.
347 || Known limitations, enhancements or remarks :
348 || Change History :
349 || Who When What
350 || (reverse chronological order - newest change first)
351 */
352 CURSOR cur_rowid IS
353 SELECT rowid
354 FROM igi_dos_trx_dest
355 WHERE ((source_trx_id = x_source_trx_id));
356
357 lv_rowid cur_rowid%RowType;
358
359 BEGIN
360
361 OPEN cur_rowid;
362 FETCH cur_rowid INTO lv_rowid;
363 IF (cur_rowid%FOUND) THEN
364 CLOSE cur_rowid;
365 fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
366 -- bug 3199481, start block
367 IF (l_unexp_level >= l_debug_level) THEN
368 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.get_fk_igi_dos_trx_sources.Msg1',FALSE);
369 END IF;
370 -- bug 3199481, end block
371 app_exception.raise_exception;
372 RETURN;
373 END IF;
374 CLOSE cur_rowid;
375
376 END get_fk_igi_dos_trx_sources;
377
378
379 PROCEDURE get_fk_igi_dos_trx_headers (
380 x_trx_id IN NUMBER
381 ) AS
382 /*
383 || Created By : [email protected]
384 || Created On : 02-MAY-2002
385 || Purpose : Validates the Foreign Keys for the table.
386 || Known limitations, enhancements or remarks :
387 || Change History :
388 || Who When What
389 || (reverse chronological order - newest change first)
390 */
391 CURSOR cur_rowid IS
392 SELECT rowid
393 FROM igi_dos_trx_dest
394 WHERE ((trx_id = x_trx_id));
395
396 lv_rowid cur_rowid%RowType;
397
398 BEGIN
399
400 OPEN cur_rowid;
401 FETCH cur_rowid INTO lv_rowid;
402 IF (cur_rowid%FOUND) THEN
403 CLOSE cur_rowid;
404 fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
405 -- bug 3199481, start block
406 IF (l_unexp_level >= l_debug_level) THEN
407 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.get_fk_igi_dos_trx_headers.Msg1',FALSE);
408 END IF;
409 -- bug 3199481, end block
410 app_exception.raise_exception;
411 RETURN;
412 END IF;
413 CLOSE cur_rowid;
414
415 END get_fk_igi_dos_trx_headers;
416
417
418 PROCEDURE get_fk_igi_dos_doc_types (
419 x_dossier_id IN NUMBER
420 ) AS
421 /*
422 || Created By : [email protected]
423 || Created On : 02-MAY-2002
424 || Purpose : Validates the Foreign Keys for the table.
425 || Known limitations, enhancements or remarks :
426 || Change History :
427 || Who When What
428 || (reverse chronological order - newest change first)
429 */
430 CURSOR cur_rowid IS
431 SELECT rowid
432 FROM igi_dos_trx_dest
433 WHERE ((dossier_id = x_dossier_id));
434
435 lv_rowid cur_rowid%RowType;
436
437 BEGIN
438
439 OPEN cur_rowid;
440 FETCH cur_rowid INTO lv_rowid;
441 IF (cur_rowid%FOUND) THEN
442 CLOSE cur_rowid;
443 fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
444 -- bug 3199481, start block
445 IF (l_unexp_level >= l_debug_level) THEN
446 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.get_fk_igi_dos_doc_types.Msg1',FALSE);
447 END IF;
448 -- bug 3199481, end block
449 app_exception.raise_exception;
450 RETURN;
451 END IF;
452 CLOSE cur_rowid;
453
454 END get_fk_igi_dos_doc_types;
455
456
457 PROCEDURE before_dml (
458 p_action IN VARCHAR2,
459 x_rowid IN VARCHAR2 ,
460 x_sob_id IN NUMBER ,
461 x_trx_id IN NUMBER ,
462 x_dest_trx_id IN NUMBER ,
463 x_source_trx_id IN NUMBER ,
464 x_source_id IN NUMBER ,
465 x_destination_id IN NUMBER ,
466 x_code_combination_id IN NUMBER ,
467 x_profile_code IN VARCHAR2 ,
468 x_budget_name IN VARCHAR2 ,
469 x_budget_entity_id IN NUMBER ,
470 x_budget_amount IN NUMBER ,
471 x_funds_available IN NUMBER ,
472 x_new_balance IN NUMBER ,
473 x_currency_code IN VARCHAR2 ,
474 x_visible_segments IN VARCHAR2 ,
475 x_actual_segments IN VARCHAR2 ,
476 x_m_budget_amount IN NUMBER ,
477 x_m_budget_amt_exch_rate IN NUMBER ,
478 x_m_budget_amt_exch_rate_type IN VARCHAR2 ,
479 x_m_budget_amt_exch_date IN DATE ,
480 x_m_budget_amt_exch_status IN VARCHAR2 ,
481 x_m_funds_avail IN NUMBER ,
482 x_m_funds_avail_exch_rate IN NUMBER ,
483 x_m_funds_avail_exch_rate_type IN VARCHAR2 ,
484 x_m_funds_avail_exch_date IN DATE ,
485 x_m_funds_avail_exch_status IN VARCHAR2 ,
486 x_m_new_balance IN NUMBER ,
487 x_m_new_balance_exch_rate IN NUMBER ,
488 x_m_new_balance_exch_rate_type IN VARCHAR2 ,
489 x_m_new_balance_exch_date IN DATE ,
490 x_m_new_balance_exch_status IN VARCHAR2 ,
491 x_dossier_id IN NUMBER ,
492 x_budget_version_id IN NUMBER ,
493 x_period_name IN VARCHAR2 ,
494 x_percentage IN NUMBER ,
495 x_status IN VARCHAR2 ,
496 x_group_id IN NUMBER ,
497 x_quarter_num IN NUMBER ,
498 x_period_year IN NUMBER ,
499 x_period_num IN NUMBER ,
500 x_line_num IN NUMBER ,
501 x_segment1 IN VARCHAR2 ,
502 x_segment2 IN VARCHAR2 ,
503 x_segment3 IN VARCHAR2 ,
504 x_segment4 IN VARCHAR2 ,
505 x_segment5 IN VARCHAR2 ,
506 x_segment6 IN VARCHAR2 ,
507 x_segment7 IN VARCHAR2 ,
508 x_segment8 IN VARCHAR2 ,
509 x_segment9 IN VARCHAR2 ,
510 x_segment10 IN VARCHAR2 ,
511 x_segment11 IN VARCHAR2 ,
512 x_segment12 IN VARCHAR2 ,
513 x_segment13 IN VARCHAR2 ,
514 x_segment14 IN VARCHAR2 ,
515 x_segment15 IN VARCHAR2 ,
516 x_segment16 IN VARCHAR2 ,
517 x_segment17 IN VARCHAR2 ,
518 x_segment18 IN VARCHAR2 ,
519 x_segment19 IN VARCHAR2 ,
520 x_segment20 IN VARCHAR2 ,
521 x_segment21 IN VARCHAR2 ,
522 x_segment22 IN VARCHAR2 ,
523 x_segment23 IN VARCHAR2 ,
524 x_segment24 IN VARCHAR2 ,
525 x_segment25 IN VARCHAR2 ,
526 x_segment26 IN VARCHAR2 ,
527 x_segment27 IN VARCHAR2 ,
528 x_segment28 IN VARCHAR2 ,
529 x_segment29 IN VARCHAR2 ,
530 x_segment30 IN VARCHAR2 ,
531 x_creation_date IN DATE ,
532 x_created_by IN NUMBER ,
533 x_last_update_date IN DATE ,
534 x_last_updated_by IN NUMBER ,
535 x_last_update_login IN NUMBER
536 ) AS
537 /*
538 || Created By : [email protected]
539 || Created On : 02-MAY-2002
540 || Purpose : Initialises the columns, Checks Constraints, Calls the
541 || Trigger Handlers for the table, before any DML operation.
542 || Known limitations, enhancements or remarks :
543 || Change History :
544 || Who When What
545 || (reverse chronological order - newest change first)
546 */
547 BEGIN
548
549 set_column_values (
550 p_action,
551 x_rowid,
552 x_sob_id,
553 x_trx_id,
554 x_dest_trx_id,
555 x_source_trx_id,
556 x_source_id,
557 x_destination_id,
558 x_code_combination_id,
559 x_profile_code,
560 x_budget_name,
561 x_budget_entity_id,
562 x_budget_amount,
563 x_funds_available,
564 x_new_balance,
565 x_currency_code,
566 x_visible_segments,
567 x_actual_segments,
568 x_m_budget_amount,
569 x_m_budget_amt_exch_rate,
570 x_m_budget_amt_exch_rate_type,
571 x_m_budget_amt_exch_date,
572 x_m_budget_amt_exch_status,
573 x_m_funds_avail,
574 x_m_funds_avail_exch_rate,
575 x_m_funds_avail_exch_rate_type,
576 x_m_funds_avail_exch_date,
577 x_m_funds_avail_exch_status,
578 x_m_new_balance,
579 x_m_new_balance_exch_rate,
580 x_m_new_balance_exch_rate_type,
581 x_m_new_balance_exch_date,
582 x_m_new_balance_exch_status,
583 x_dossier_id,
584 x_budget_version_id,
585 x_period_name,
586 x_percentage,
587 x_status,
588 x_group_id,
589 x_quarter_num,
590 x_period_year,
591 x_period_num,
592 x_line_num,
593 x_segment1,
594 x_segment2,
595 x_segment3,
596 x_segment4,
597 x_segment5,
598 x_segment6,
599 x_segment7,
600 x_segment8,
601 x_segment9,
602 x_segment10,
603 x_segment11,
604 x_segment12,
605 x_segment13,
606 x_segment14,
607 x_segment15,
608 x_segment16,
609 x_segment17,
610 x_segment18,
611 x_segment19,
612 x_segment20,
613 x_segment21,
614 x_segment22,
615 x_segment23,
616 x_segment24,
617 x_segment25,
618 x_segment26,
619 x_segment27,
620 x_segment28,
621 x_segment29,
622 x_segment30,
623 x_creation_date,
624 x_created_by,
625 x_last_update_date,
626 x_last_updated_by,
627 x_last_update_login
628 );
629
630 IF (p_action = 'INSERT') THEN
631 -- Call all the procedures related to Before Insert.
632 IF ( get_pk_for_validation(
633 new_references.dest_trx_id
634 )
635 ) THEN
636 fnd_message.set_name('FND','FORM_DUPLICATE_KEY_IN_INDEX');
637 -- bug 3199481, start block
638 IF (l_unexp_level >= l_debug_level) THEN
639 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.before_dml.Msg1',FALSE);
640 END IF;
641 -- bug 3199481, end block
642 app_exception.raise_exception;
643 END IF;
644 check_parent_existance;
645 ELSIF (p_action = 'UPDATE') THEN
646 -- Call all the procedures related to Before Update.
647 check_parent_existance;
648 ELSIF (p_action = 'DELETE') THEN
649 -- Call all the procedures related to Before Delete.
650 --check_child_existance;
651 null;
652 ELSIF (p_action = 'VALIDATE_INSERT') THEN
653 -- Call all the procedures related to Before Insert.
654 IF ( get_pk_for_validation (
655 new_references.dest_trx_id
656 )
657 ) THEN
658 fnd_message.set_name('FND','FORM_DUPLICATE_KEY_IN_INDEX');
659 -- bug 3199481, start block
660 IF (l_unexp_level >= l_debug_level) THEN
661 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.before_dml.Msg2',FALSE);
662 END IF;
663 -- bug 3199481, end block
664 app_exception.raise_exception;
665 END IF;
666 ELSIF (p_action = 'VALIDATE_DELETE') THEN
667 check_child_existance;
668 END IF;
669
670 END before_dml;
671
672
673 PROCEDURE insert_row (
674 x_rowid IN OUT NOCOPY VARCHAR2,
675 x_sob_id IN NUMBER,
676 x_trx_id IN NUMBER,
677 x_dest_trx_id IN OUT NOCOPY NUMBER,
681 x_code_combination_id IN NUMBER,
678 x_source_trx_id IN NUMBER,
679 x_source_id IN NUMBER,
680 x_destination_id IN NUMBER,
682 x_profile_code IN VARCHAR2,
683 x_budget_name IN VARCHAR2,
684 x_budget_entity_id IN NUMBER,
685 x_budget_amount IN NUMBER,
686 x_funds_available IN NUMBER,
687 x_new_balance IN NUMBER,
688 x_currency_code IN VARCHAR2,
689 x_visible_segments IN VARCHAR2,
690 x_actual_segments IN VARCHAR2,
691 x_m_budget_amount IN NUMBER,
692 x_m_budget_amt_exch_rate IN NUMBER,
693 x_m_budget_amt_exch_rate_type IN VARCHAR2,
694 x_m_budget_amt_exch_date IN DATE,
695 x_m_budget_amt_exch_status IN VARCHAR2,
696 x_m_funds_avail IN NUMBER,
697 x_m_funds_avail_exch_rate IN NUMBER,
698 x_m_funds_avail_exch_rate_type IN VARCHAR2,
699 x_m_funds_avail_exch_date IN DATE,
700 x_m_funds_avail_exch_status IN VARCHAR2,
701 x_m_new_balance IN NUMBER,
702 x_m_new_balance_exch_rate IN NUMBER,
703 x_m_new_balance_exch_rate_type IN VARCHAR2,
704 x_m_new_balance_exch_date IN DATE,
705 x_m_new_balance_exch_status IN VARCHAR2,
706 x_dossier_id IN NUMBER,
707 x_budget_version_id IN NUMBER,
708 x_period_name IN VARCHAR2,
709 x_percentage IN NUMBER,
710 x_status IN VARCHAR2,
711 x_group_id IN NUMBER,
712 x_quarter_num IN NUMBER,
713 x_period_year IN NUMBER,
714 x_period_num IN NUMBER,
715 x_line_num IN NUMBER,
716 x_segment1 IN VARCHAR2,
717 x_segment2 IN VARCHAR2,
718 x_segment3 IN VARCHAR2,
719 x_segment4 IN VARCHAR2,
720 x_segment5 IN VARCHAR2,
721 x_segment6 IN VARCHAR2,
722 x_segment7 IN VARCHAR2,
723 x_segment8 IN VARCHAR2,
724 x_segment9 IN VARCHAR2,
725 x_segment10 IN VARCHAR2,
726 x_segment11 IN VARCHAR2,
727 x_segment12 IN VARCHAR2,
728 x_segment13 IN VARCHAR2,
729 x_segment14 IN VARCHAR2,
730 x_segment15 IN VARCHAR2,
731 x_segment16 IN VARCHAR2,
732 x_segment17 IN VARCHAR2,
733 x_segment18 IN VARCHAR2,
734 x_segment19 IN VARCHAR2,
735 x_segment20 IN VARCHAR2,
736 x_segment21 IN VARCHAR2,
737 x_segment22 IN VARCHAR2,
738 x_segment23 IN VARCHAR2,
739 x_segment24 IN VARCHAR2,
740 x_segment25 IN VARCHAR2,
741 x_segment26 IN VARCHAR2,
742 x_segment27 IN VARCHAR2,
743 x_segment28 IN VARCHAR2,
747 /*
744 x_segment29 IN VARCHAR2,
745 x_segment30 IN VARCHAR2,
746 x_mode IN VARCHAR2 ) AS
748 || Created By : [email protected]
749 || Created On : 02-MAY-2002
750 || Purpose : Handles the INSERT DML logic for the table.
751 || Known limitations, enhancements or remarks :
752 || Change History :
753 || Who When What
754 || (reverse chronological order - newest change first)
755 */
756 CURSOR c IS
757 SELECT rowid
758 FROM igi_dos_trx_dest
759 WHERE dest_trx_id = x_dest_trx_id;
760
761 x_last_update_date DATE;
762 x_last_updated_by NUMBER;
763 x_last_update_login NUMBER;
764
765 l_mode VARCHAR2(1);
766
767 BEGIN
768 IF X_mode is NULL then
769 l_mode :='R';
770 ELSE
771 l_mode := X_mode;
772 END IF;
773
774 x_last_update_date := SYSDATE;
775 IF (l_mode = 'I') THEN
776 x_last_updated_by := 1;
777 x_last_update_login := 0;
778 ELSIF (l_mode = 'R') THEN
779 x_last_updated_by := fnd_global.user_id;
780 IF (x_last_updated_by IS NULL) THEN
781 x_last_updated_by := -1;
782 END IF;
783 x_last_update_login := fnd_global.login_id;
784 IF (x_last_update_login IS NULL) THEN
785 x_last_update_login := -1;
786 END IF;
787 ELSE
788 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
789 -- bug 3199481, start block
790 IF (l_unexp_level >= l_debug_level) THEN
791 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.insert_row.Msg1',FALSE);
792 END IF;
793 -- bug 3199481, end block
794 app_exception.raise_exception;
795 END IF;
796
797 SELECT igi_dos_trx_dest_s.NEXTVAL
798 INTO x_dest_trx_id
799 FROM dual;
800
801 before_dml(
802 p_action => 'INSERT',
803 x_rowid => x_rowid,
804 x_sob_id => x_sob_id,
805 x_trx_id => x_trx_id,
806 x_dest_trx_id => x_dest_trx_id,
807 x_source_trx_id => x_source_trx_id,
808 x_source_id => x_source_id,
809 x_destination_id => x_destination_id,
810 x_code_combination_id => x_code_combination_id,
811 x_profile_code => x_profile_code,
812 x_budget_name => x_budget_name,
813 x_budget_entity_id => x_budget_entity_id,
814 x_budget_amount => x_budget_amount,
815 x_funds_available => x_funds_available,
816 x_new_balance => x_new_balance,
817 x_currency_code => x_currency_code,
818 x_visible_segments => x_visible_segments,
819 x_actual_segments => x_actual_segments,
820 x_m_budget_amount => x_m_budget_amount,
821 x_m_budget_amt_exch_rate => x_m_budget_amt_exch_rate,
822 x_m_budget_amt_exch_rate_type => x_m_budget_amt_exch_rate_type,
823 x_m_budget_amt_exch_date => x_m_budget_amt_exch_date,
824 x_m_budget_amt_exch_status => x_m_budget_amt_exch_status,
825 x_m_funds_avail => x_m_funds_avail,
826 x_m_funds_avail_exch_rate => x_m_funds_avail_exch_rate,
827 x_m_funds_avail_exch_rate_type => x_m_funds_avail_exch_rate_type,
828 x_m_funds_avail_exch_date => x_m_funds_avail_exch_date,
829 x_m_funds_avail_exch_status => x_m_funds_avail_exch_status,
830 x_m_new_balance => x_m_new_balance,
831 x_m_new_balance_exch_rate => x_m_new_balance_exch_rate,
832 x_m_new_balance_exch_rate_type => x_m_new_balance_exch_rate_type,
833 x_m_new_balance_exch_date => x_m_new_balance_exch_date,
834 x_m_new_balance_exch_status => x_m_new_balance_exch_status,
835 x_dossier_id => x_dossier_id,
836 x_budget_version_id => x_budget_version_id,
837 x_period_name => x_period_name,
838 x_percentage => x_percentage,
839 x_status => x_status,
840 x_group_id => x_group_id,
841 x_quarter_num => x_quarter_num,
842 x_period_year => x_period_year,
843 x_period_num => x_period_num,
844 x_line_num => x_line_num,
845 x_segment1 => x_segment1,
846 x_segment2 => x_segment2,
847 x_segment3 => x_segment3,
848 x_segment4 => x_segment4,
849 x_segment5 => x_segment5,
850 x_segment6 => x_segment6,
851 x_segment7 => x_segment7,
852 x_segment8 => x_segment8,
853 x_segment9 => x_segment9,
854 x_segment10 => x_segment10,
855 x_segment11 => x_segment11,
856 x_segment12 => x_segment12,
857 x_segment13 => x_segment13,
858 x_segment14 => x_segment14,
859 x_segment15 => x_segment15,
860 x_segment16 => x_segment16,
861 x_segment17 => x_segment17,
862 x_segment18 => x_segment18,
863 x_segment19 => x_segment19,
864 x_segment20 => x_segment20,
865 x_segment21 => x_segment21,
866 x_segment22 => x_segment22,
867 x_segment23 => x_segment23,
868 x_segment24 => x_segment24,
869 x_segment25 => x_segment25,
870 x_segment26 => x_segment26,
871 x_segment27 => x_segment27,
875 x_creation_date => x_last_update_date,
872 x_segment28 => x_segment28,
873 x_segment29 => x_segment29,
874 x_segment30 => x_segment30,
876 x_created_by => x_last_updated_by,
877 x_last_update_date => x_last_update_date,
878 x_last_updated_by => x_last_updated_by,
879 x_last_update_login => x_last_update_login
880 );
881
882 INSERT INTO igi_dos_trx_dest (
883 sob_id,
884 trx_id,
885 dest_trx_id,
886 source_trx_id,
887 source_id,
888 destination_id,
889 code_combination_id,
890 profile_code,
891 budget_name,
892 budget_entity_id,
893 budget_amount,
894 funds_available,
895 new_balance,
896 currency_code,
897 visible_segments,
898 actual_segments,
899 mrc_budget_amount,
900 mrc_budget_amt_exch_rate,
901 mrc_budget_amt_exch_rate_type,
902 mrc_budget_amt_exch_date,
903 mrc_budget_amt_exch_status,
904 mrc_funds_avail,
905 mrc_funds_avail_exch_rate,
906 mrc_funds_avail_exch_rate_type,
907 mrc_funds_avail_exch_date,
908 mrc_funds_avail_exch_status,
909 mrc_new_balance,
910 mrc_new_balance_exch_rate,
911 mrc_new_balance_exch_rate_type,
912 mrc_new_balance_exch_date,
913 mrc_new_balance_exch_status,
914 dossier_id,
915 budget_version_id,
916 period_name,
917 percentage,
918 status,
919 group_id,
920 quarter_num,
921 period_year,
922 period_num,
923 line_num,
924 segment1,
925 segment2,
926 segment3,
927 segment4,
928 segment5,
929 segment6,
930 segment7,
931 segment8,
932 segment9,
933 segment10,
934 segment11,
935 segment12,
936 segment13,
937 segment14,
938 segment15,
939 segment16,
940 segment17,
941 segment18,
942 segment19,
943 segment20,
944 segment21,
945 segment22,
946 segment23,
947 segment24,
948 segment25,
949 segment26,
950 segment27,
951 segment28,
952 segment29,
953 segment30,
954 creation_date,
955 created_by,
956 last_update_date,
957 last_updated_by,
958 last_update_login
959 ) VALUES (
960 new_references.sob_id,
961 new_references.trx_id,
962 new_references.dest_trx_id,
963 new_references.source_trx_id,
964 new_references.source_id,
965 new_references.destination_id,
966 new_references.code_combination_id,
967 new_references.profile_code,
968 new_references.budget_name,
969 new_references.budget_entity_id,
970 new_references.budget_amount,
971 new_references.funds_available,
972 new_references.new_balance,
973 new_references.currency_code,
974 new_references.visible_segments,
975 new_references.actual_segments,
976 new_references.mrc_budget_amount,
977 new_references.mrc_budget_amt_exch_rate,
978 new_references.mrc_budget_amt_exch_rate_type,
979 new_references.mrc_budget_amt_exch_date,
980 new_references.mrc_budget_amt_exch_status,
981 new_references.mrc_funds_avail,
982 new_references.mrc_funds_avail_exch_rate,
983 new_references.mrc_funds_avail_exch_rate_type,
984 new_references.mrc_funds_avail_exch_date,
985 new_references.mrc_funds_avail_exch_status,
986 new_references.mrc_new_balance,
987 new_references.mrc_new_balance_exch_rate,
988 new_references.mrc_new_balance_exch_rate_type,
989 new_references.mrc_new_balance_exch_date,
990 new_references.mrc_new_balance_exch_status,
991 new_references.dossier_id,
992 new_references.budget_version_id,
993 new_references.period_name,
994 new_references.percentage,
995 new_references.status,
996 new_references.group_id,
997 new_references.quarter_num,
998 new_references.period_year,
999 new_references.period_num,
1000 new_references.line_num,
1001 new_references.segment1,
1002 new_references.segment2,
1003 new_references.segment3,
1004 new_references.segment4,
1005 new_references.segment5,
1006 new_references.segment6,
1007 new_references.segment7,
1008 new_references.segment8,
1009 new_references.segment9,
1010 new_references.segment10,
1011 new_references.segment11,
1012 new_references.segment12,
1013 new_references.segment13,
1014 new_references.segment14,
1015 new_references.segment15,
1016 new_references.segment16,
1017 new_references.segment17,
1018 new_references.segment18,
1019 new_references.segment19,
1020 new_references.segment20,
1021 new_references.segment21,
1022 new_references.segment22,
1023 new_references.segment23,
1024 new_references.segment24,
1025 new_references.segment25,
1026 new_references.segment26,
1027 new_references.segment27,
1028 new_references.segment28,
1029 new_references.segment29,
1030 new_references.segment30,
1031 x_last_update_date,
1032 x_last_updated_by,
1033 x_last_update_date,
1034 x_last_updated_by,
1035 x_last_update_login
1036 );
1037
1038 OPEN c;
1039 FETCH c INTO x_rowid;
1040 IF (c%NOTFOUND) THEN
1041 CLOSE c;
1042 RAISE NO_DATA_FOUND;
1043 END IF;
1044 CLOSE c;
1045
1046 END insert_row;
1047
1048
1049 PROCEDURE lock_row (
1050 x_rowid IN VARCHAR2,
1051 x_sob_id IN NUMBER,
1052 x_trx_id IN NUMBER,
1053 x_dest_trx_id IN NUMBER,
1054 x_source_trx_id IN NUMBER,
1055 x_source_id IN NUMBER,
1056 x_destination_id IN NUMBER,
1057 x_code_combination_id IN NUMBER,
1058 x_profile_code IN VARCHAR2,
1059 x_budget_name IN VARCHAR2,
1060 x_budget_entity_id IN NUMBER,
1061 x_budget_amount IN NUMBER,
1062 x_funds_available IN NUMBER,
1063 x_new_balance IN NUMBER,
1064 x_currency_code IN VARCHAR2,
1065 x_visible_segments IN VARCHAR2,
1066 x_actual_segments IN VARCHAR2,
1067 x_m_budget_amount IN NUMBER,
1068 x_m_budget_amt_exch_rate IN NUMBER,
1069 x_m_budget_amt_exch_rate_type IN VARCHAR2,
1070 x_m_budget_amt_exch_date IN DATE,
1071 x_m_budget_amt_exch_status IN VARCHAR2,
1072 x_m_funds_avail IN NUMBER,
1073 x_m_funds_avail_exch_rate IN NUMBER,
1074 x_m_funds_avail_exch_rate_type IN VARCHAR2,
1075 x_m_funds_avail_exch_date IN DATE,
1076 x_m_funds_avail_exch_status IN VARCHAR2,
1077 x_m_new_balance IN NUMBER,
1078 x_m_new_balance_exch_rate IN NUMBER,
1079 x_m_new_balance_exch_rate_type IN VARCHAR2,
1080 x_m_new_balance_exch_date IN DATE,
1081 x_m_new_balance_exch_status IN VARCHAR2,
1082 x_dossier_id IN NUMBER,
1083 x_budget_version_id IN NUMBER,
1084 x_period_name IN VARCHAR2,
1085 x_percentage IN NUMBER,
1086 x_status IN VARCHAR2,
1087 x_group_id IN NUMBER,
1088 x_quarter_num IN NUMBER,
1089 x_period_year IN NUMBER,
1090 x_period_num IN NUMBER,
1091 x_line_num IN NUMBER,
1092 x_segment1 IN VARCHAR2,
1093 x_segment2 IN VARCHAR2,
1094 x_segment3 IN VARCHAR2,
1095 x_segment4 IN VARCHAR2,
1096 x_segment5 IN VARCHAR2,
1097 x_segment6 IN VARCHAR2,
1098 x_segment7 IN VARCHAR2,
1099 x_segment8 IN VARCHAR2,
1100 x_segment9 IN VARCHAR2,
1101 x_segment10 IN VARCHAR2,
1102 x_segment11 IN VARCHAR2,
1103 x_segment12 IN VARCHAR2,
1104 x_segment13 IN VARCHAR2,
1105 x_segment14 IN VARCHAR2,
1106 x_segment15 IN VARCHAR2,
1107 x_segment16 IN VARCHAR2,
1108 x_segment17 IN VARCHAR2,
1112 x_segment21 IN VARCHAR2,
1109 x_segment18 IN VARCHAR2,
1110 x_segment19 IN VARCHAR2,
1111 x_segment20 IN VARCHAR2,
1113 x_segment22 IN VARCHAR2,
1114 x_segment23 IN VARCHAR2,
1115 x_segment24 IN VARCHAR2,
1116 x_segment25 IN VARCHAR2,
1117 x_segment26 IN VARCHAR2,
1118 x_segment27 IN VARCHAR2,
1119 x_segment28 IN VARCHAR2,
1120 x_segment29 IN VARCHAR2,
1121 x_segment30 IN VARCHAR2
1122 ) AS
1123 /*
1124 || Created By : [email protected]
1125 || Created On : 02-MAY-2002
1126 || Purpose : Handles the LOCK mechanism for the table.
1127 || Known limitations, enhancements or remarks :
1128 || Change History :
1129 || Who When What
1130 || (reverse chronological order - newest change first)
1131 */
1132 CURSOR c1 IS
1133 SELECT
1134 sob_id,
1135 trx_id,
1136 source_trx_id,
1137 source_id,
1138 destination_id,
1139 code_combination_id,
1140 profile_code,
1141 budget_name,
1142 budget_entity_id,
1143 budget_amount,
1144 funds_available,
1145 new_balance,
1146 currency_code,
1147 visible_segments,
1148 actual_segments,
1149 mrc_budget_amount,
1150 mrc_budget_amt_exch_rate,
1151 mrc_budget_amt_exch_rate_type,
1152 mrc_budget_amt_exch_date,
1153 mrc_budget_amt_exch_status,
1154 mrc_funds_avail,
1155 mrc_funds_avail_exch_rate,
1156 mrc_funds_avail_exch_rate_type,
1157 mrc_funds_avail_exch_date,
1158 mrc_funds_avail_exch_status,
1159 mrc_new_balance,
1160 mrc_new_balance_exch_rate,
1161 mrc_new_balance_exch_rate_type,
1162 mrc_new_balance_exch_date,
1163 mrc_new_balance_exch_status,
1164 dossier_id,
1165 budget_version_id,
1166 period_name,
1167 percentage,
1168 status,
1169 group_id,
1170 quarter_num,
1171 period_year,
1172 period_num,
1173 line_num,
1174 segment1,
1175 segment2,
1176 segment3,
1177 segment4,
1178 segment5,
1179 segment6,
1180 segment7,
1181 segment8,
1182 segment9,
1183 segment10,
1184 segment11,
1185 segment12,
1186 segment13,
1187 segment14,
1188 segment15,
1189 segment16,
1190 segment17,
1191 segment18,
1192 segment19,
1193 segment20,
1194 segment21,
1195 segment22,
1196 segment23,
1197 segment24,
1198 segment25,
1199 segment26,
1200 segment27,
1201 segment28,
1202 segment29,
1203 segment30
1204 FROM igi_dos_trx_dest
1205 WHERE rowid = x_rowid
1206 FOR UPDATE NOWAIT;
1207
1208 tlinfo c1%ROWTYPE;
1209
1210 BEGIN
1211
1212 OPEN c1;
1213 FETCH c1 INTO tlinfo;
1214 IF (c1%notfound) THEN
1215 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1216 -- bug 3199481, start block
1217 IF (l_unexp_level >= l_debug_level) THEN
1218 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.lock_row.Msg1',FALSE);
1219 END IF;
1220 -- bug 3199481, end block
1221 CLOSE c1;
1222 app_exception.raise_exception;
1223 RETURN;
1224 END IF;
1225 CLOSE c1;
1226
1227 IF (
1228 ((tlinfo.sob_id = x_sob_id) OR ((tlinfo.sob_id IS NULL) AND (X_sob_id IS NULL)))
1229 AND ((tlinfo.trx_id = x_trx_id) OR ((tlinfo.trx_id IS NULL) AND (X_trx_id IS NULL)))
1230 AND ((tlinfo.source_trx_id = x_source_trx_id) OR ((tlinfo.source_trx_id IS NULL) AND (X_source_trx_id IS NULL)))
1231 AND ((tlinfo.source_id = x_source_id) OR ((tlinfo.source_id IS NULL) AND (X_source_id IS NULL)))
1232 AND ((tlinfo.destination_id = x_destination_id) OR ((tlinfo.destination_id IS NULL) AND (X_destination_id IS NULL)))
1233 AND ((tlinfo.code_combination_id = x_code_combination_id) OR ((tlinfo.code_combination_id IS NULL) AND (X_code_combination_id IS NULL)))
1234 AND ((tlinfo.profile_code = x_profile_code) OR ((tlinfo.profile_code IS NULL) AND (X_profile_code IS NULL)))
1235 AND ((tlinfo.budget_name = x_budget_name) OR ((tlinfo.budget_name IS NULL) AND (X_budget_name IS NULL)))
1236 AND ((tlinfo.budget_entity_id = x_budget_entity_id) OR ((tlinfo.budget_entity_id IS NULL) AND (X_budget_entity_id IS NULL)))
1237 AND ((tlinfo.budget_amount = x_budget_amount) OR ((tlinfo.budget_amount IS NULL) AND (X_budget_amount IS NULL)))
1238 AND ((tlinfo.funds_available = x_funds_available) OR ((tlinfo.funds_available IS NULL) AND (X_funds_available IS NULL)))
1239 AND ((tlinfo.new_balance = x_new_balance) OR ((tlinfo.new_balance IS NULL) AND (X_new_balance IS NULL)))
1240 AND ((tlinfo.currency_code = x_currency_code) OR ((tlinfo.currency_code IS NULL) AND (X_currency_code IS NULL)))
1241 AND ((tlinfo.visible_segments = x_visible_segments) OR ((tlinfo.visible_segments IS NULL) AND (X_visible_segments IS NULL)))
1242 AND ((tlinfo.actual_segments = x_actual_segments) OR ((tlinfo.actual_segments IS NULL) AND (X_actual_segments IS NULL)))
1243 AND ((tlinfo.mrc_budget_amount = x_m_budget_amount) OR ((tlinfo.mrc_budget_amount IS NULL) AND (x_m_budget_amount IS NULL)))
1244 AND ((tlinfo.mrc_budget_amt_exch_rate = x_m_budget_amt_exch_rate) OR ((tlinfo.mrc_budget_amt_exch_rate IS NULL) AND (x_m_budget_amt_exch_rate IS NULL)))
1245 AND ((tlinfo.mrc_budget_amt_exch_rate_type = x_m_budget_amt_exch_rate_type) OR ((tlinfo.mrc_budget_amt_exch_rate_type IS NULL) AND (x_m_budget_amt_exch_rate_type IS NULL)))
1246 AND ((tlinfo.mrc_budget_amt_exch_date = x_m_budget_amt_exch_date) OR ((tlinfo.mrc_budget_amt_exch_date IS NULL) AND (x_m_budget_amt_exch_date IS NULL)))
1247 AND ((tlinfo.mrc_budget_amt_exch_status = x_m_budget_amt_exch_status) OR ((tlinfo.mrc_budget_amt_exch_status IS NULL) AND (x_m_budget_amt_exch_status IS NULL)))
1248 AND ((tlinfo.mrc_funds_avail = x_m_funds_avail) OR ((tlinfo.mrc_funds_avail IS NULL) AND (x_m_funds_avail IS NULL)))
1249 AND ((tlinfo.mrc_funds_avail_exch_rate = x_m_funds_avail_exch_rate) OR ((tlinfo.mrc_funds_avail_exch_rate IS NULL) AND (x_m_funds_avail_exch_rate IS NULL)))
1250 AND ((tlinfo.mrc_funds_avail_exch_rate_type = x_m_funds_avail_exch_rate_type) OR ((tlinfo.mrc_funds_avail_exch_rate_type IS NULL) AND (x_m_funds_avail_exch_rate_type IS NULL)))
1251 AND ((tlinfo.mrc_funds_avail_exch_date = x_m_funds_avail_exch_date) OR ((tlinfo.mrc_funds_avail_exch_date IS NULL) AND (x_m_funds_avail_exch_date IS NULL)))
1255 AND ((tlinfo.mrc_new_balance_exch_rate_type = x_m_new_balance_exch_rate_type) OR ((tlinfo.mrc_new_balance_exch_rate_type IS NULL) AND (x_m_new_balance_exch_rate_type IS NULL)))
1252 AND ((tlinfo.mrc_funds_avail_exch_status = x_m_funds_avail_exch_status) OR ((tlinfo.mrc_funds_avail_exch_status IS NULL) AND (x_m_funds_avail_exch_status IS NULL)))
1253 AND ((tlinfo.mrc_new_balance = x_m_new_balance) OR ((tlinfo.mrc_new_balance IS NULL) AND (x_m_new_balance IS NULL)))
1254 AND ((tlinfo.mrc_new_balance_exch_rate = x_m_new_balance_exch_rate) OR ((tlinfo.mrc_new_balance_exch_rate IS NULL) AND (x_m_new_balance_exch_rate IS NULL)))
1256 AND ((tlinfo.mrc_new_balance_exch_date = x_m_new_balance_exch_date) OR ((tlinfo.mrc_new_balance_exch_date IS NULL) AND (x_m_new_balance_exch_date IS NULL)))
1257 AND ((tlinfo.mrc_new_balance_exch_status = x_m_new_balance_exch_status) OR ((tlinfo.mrc_new_balance_exch_status IS NULL) AND (x_m_new_balance_exch_status IS NULL)))
1258 AND ((tlinfo.dossier_id = x_dossier_id) OR ((tlinfo.dossier_id IS NULL) AND (X_dossier_id IS NULL)))
1259 AND ((tlinfo.budget_version_id = x_budget_version_id) OR ((tlinfo.budget_version_id IS NULL) AND (X_budget_version_id IS NULL)))
1260 AND ((tlinfo.period_name = x_period_name) OR ((tlinfo.period_name IS NULL) AND (X_period_name IS NULL)))
1261 AND ((tlinfo.percentage = x_percentage) OR ((tlinfo.percentage IS NULL) AND (X_percentage IS NULL)))
1262 AND ((tlinfo.status = x_status) OR ((tlinfo.status IS NULL) AND (X_status IS NULL)))
1263 AND ((tlinfo.group_id = x_group_id) OR ((tlinfo.group_id IS NULL) AND (X_group_id IS NULL)))
1264 AND ((tlinfo.quarter_num = x_quarter_num) OR ((tlinfo.quarter_num IS NULL) AND (X_quarter_num IS NULL)))
1265 AND ((tlinfo.period_year = x_period_year) OR ((tlinfo.period_year IS NULL) AND (X_period_year IS NULL)))
1266 AND ((tlinfo.period_num = x_period_num) OR ((tlinfo.period_num IS NULL) AND (X_period_num IS NULL)))
1267 AND ((tlinfo.line_num = x_line_num) OR ((tlinfo.line_num IS NULL) AND (X_line_num IS NULL)))
1268 AND ((tlinfo.segment1 = x_segment1) OR ((tlinfo.segment1 IS NULL) AND (X_segment1 IS NULL)))
1269 AND ((tlinfo.segment2 = x_segment2) OR ((tlinfo.segment2 IS NULL) AND (X_segment2 IS NULL)))
1270 AND ((tlinfo.segment3 = x_segment3) OR ((tlinfo.segment3 IS NULL) AND (X_segment3 IS NULL)))
1271 AND ((tlinfo.segment4 = x_segment4) OR ((tlinfo.segment4 IS NULL) AND (X_segment4 IS NULL)))
1272 AND ((tlinfo.segment5 = x_segment5) OR ((tlinfo.segment5 IS NULL) AND (X_segment5 IS NULL)))
1273 AND ((tlinfo.segment6 = x_segment6) OR ((tlinfo.segment6 IS NULL) AND (X_segment6 IS NULL)))
1274 AND ((tlinfo.segment7 = x_segment7) OR ((tlinfo.segment7 IS NULL) AND (X_segment7 IS NULL)))
1275 AND ((tlinfo.segment8 = x_segment8) OR ((tlinfo.segment8 IS NULL) AND (X_segment8 IS NULL)))
1276 AND ((tlinfo.segment9 = x_segment9) OR ((tlinfo.segment9 IS NULL) AND (X_segment9 IS NULL)))
1277 AND ((tlinfo.segment10 = x_segment10) OR ((tlinfo.segment10 IS NULL) AND (X_segment10 IS NULL)))
1278 AND ((tlinfo.segment11 = x_segment11) OR ((tlinfo.segment11 IS NULL) AND (X_segment11 IS NULL)))
1279 AND ((tlinfo.segment12 = x_segment12) OR ((tlinfo.segment12 IS NULL) AND (X_segment12 IS NULL)))
1280 AND ((tlinfo.segment13 = x_segment13) OR ((tlinfo.segment13 IS NULL) AND (X_segment13 IS NULL)))
1281 AND ((tlinfo.segment14 = x_segment14) OR ((tlinfo.segment14 IS NULL) AND (X_segment14 IS NULL)))
1282 AND ((tlinfo.segment15 = x_segment15) OR ((tlinfo.segment15 IS NULL) AND (X_segment15 IS NULL)))
1283 AND ((tlinfo.segment16 = x_segment16) OR ((tlinfo.segment16 IS NULL) AND (X_segment16 IS NULL)))
1284 AND ((tlinfo.segment17 = x_segment17) OR ((tlinfo.segment17 IS NULL) AND (X_segment17 IS NULL)))
1285 AND ((tlinfo.segment18 = x_segment18) OR ((tlinfo.segment18 IS NULL) AND (X_segment18 IS NULL)))
1286 AND ((tlinfo.segment19 = x_segment19) OR ((tlinfo.segment19 IS NULL) AND (X_segment19 IS NULL)))
1287 AND ((tlinfo.segment20 = x_segment20) OR ((tlinfo.segment20 IS NULL) AND (X_segment20 IS NULL)))
1288 AND ((tlinfo.segment21 = x_segment21) OR ((tlinfo.segment21 IS NULL) AND (X_segment21 IS NULL)))
1289 AND ((tlinfo.segment22 = x_segment22) OR ((tlinfo.segment22 IS NULL) AND (X_segment22 IS NULL)))
1290 AND ((tlinfo.segment23 = x_segment23) OR ((tlinfo.segment23 IS NULL) AND (X_segment23 IS NULL)))
1291 AND ((tlinfo.segment24 = x_segment24) OR ((tlinfo.segment24 IS NULL) AND (X_segment24 IS NULL)))
1292 AND ((tlinfo.segment25 = x_segment25) OR ((tlinfo.segment25 IS NULL) AND (X_segment25 IS NULL)))
1293 AND ((tlinfo.segment26 = x_segment26) OR ((tlinfo.segment26 IS NULL) AND (X_segment26 IS NULL)))
1294 AND ((tlinfo.segment27 = x_segment27) OR ((tlinfo.segment27 IS NULL) AND (X_segment27 IS NULL)))
1295 AND ((tlinfo.segment28 = x_segment28) OR ((tlinfo.segment28 IS NULL) AND (X_segment28 IS NULL)))
1296 AND ((tlinfo.segment29 = x_segment29) OR ((tlinfo.segment29 IS NULL) AND (X_segment29 IS NULL)))
1297 AND ((tlinfo.segment30 = x_segment30) OR ((tlinfo.segment30 IS NULL) AND (X_segment30 IS NULL)))
1298 ) THEN
1299 NULL;
1300 ELSE
1301 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1302 -- bug 3199481, start block
1303 IF (l_unexp_level >= l_debug_level) THEN
1304 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.lock_row.Msg2',FALSE);
1305 END IF;
1306 -- bug 3199481, end block
1307 app_exception.raise_exception;
1308 END IF;
1309
1310 RETURN;
1311
1312 END lock_row;
1313
1314
1315 PROCEDURE update_row (
1316 x_rowid IN VARCHAR2,
1317 x_sob_id IN NUMBER,
1318 x_trx_id IN NUMBER,
1319 x_dest_trx_id IN NUMBER,
1320 x_source_trx_id IN NUMBER,
1321 x_source_id IN NUMBER,
1322 x_destination_id IN NUMBER,
1323 x_code_combination_id IN NUMBER,
1324 x_profile_code IN VARCHAR2,
1325 x_budget_name IN VARCHAR2,
1326 x_budget_entity_id IN NUMBER,
1327 x_budget_amount IN NUMBER,
1328 x_funds_available IN NUMBER,
1329 x_new_balance IN NUMBER,
1330 x_currency_code IN VARCHAR2,
1331 x_visible_segments IN VARCHAR2,
1332 x_actual_segments IN VARCHAR2,
1336 x_m_budget_amt_exch_date IN DATE,
1333 x_m_budget_amount IN NUMBER,
1334 x_m_budget_amt_exch_rate IN NUMBER,
1335 x_m_budget_amt_exch_rate_type IN VARCHAR2,
1337 x_m_budget_amt_exch_status IN VARCHAR2,
1338 x_m_funds_avail IN NUMBER,
1339 x_m_funds_avail_exch_rate IN NUMBER,
1340 x_m_funds_avail_exch_rate_type IN VARCHAR2,
1341 x_m_funds_avail_exch_date IN DATE,
1342 x_m_funds_avail_exch_status IN VARCHAR2,
1343 x_m_new_balance IN NUMBER,
1344 x_m_new_balance_exch_rate IN NUMBER,
1345 x_m_new_balance_exch_rate_type IN VARCHAR2,
1346 x_m_new_balance_exch_date IN DATE,
1347 x_m_new_balance_exch_status IN VARCHAR2,
1348 x_dossier_id IN NUMBER,
1349 x_budget_version_id IN NUMBER,
1350 x_period_name IN VARCHAR2,
1351 x_percentage IN NUMBER,
1352 x_status IN VARCHAR2,
1353 x_group_id IN NUMBER,
1354 x_quarter_num IN NUMBER,
1355 x_period_year IN NUMBER,
1356 x_period_num IN NUMBER,
1357 x_line_num IN NUMBER,
1358 x_segment1 IN VARCHAR2,
1359 x_segment2 IN VARCHAR2,
1360 x_segment3 IN VARCHAR2,
1361 x_segment4 IN VARCHAR2,
1362 x_segment5 IN VARCHAR2,
1363 x_segment6 IN VARCHAR2,
1364 x_segment7 IN VARCHAR2,
1365 x_segment8 IN VARCHAR2,
1366 x_segment9 IN VARCHAR2,
1367 x_segment10 IN VARCHAR2,
1368 x_segment11 IN VARCHAR2,
1369 x_segment12 IN VARCHAR2,
1370 x_segment13 IN VARCHAR2,
1371 x_segment14 IN VARCHAR2,
1372 x_segment15 IN VARCHAR2,
1373 x_segment16 IN VARCHAR2,
1374 x_segment17 IN VARCHAR2,
1375 x_segment18 IN VARCHAR2,
1376 x_segment19 IN VARCHAR2,
1377 x_segment20 IN VARCHAR2,
1378 x_segment21 IN VARCHAR2,
1379 x_segment22 IN VARCHAR2,
1380 x_segment23 IN VARCHAR2,
1381 x_segment24 IN VARCHAR2,
1382 x_segment25 IN VARCHAR2,
1383 x_segment26 IN VARCHAR2,
1384 x_segment27 IN VARCHAR2,
1385 x_segment28 IN VARCHAR2,
1386 x_segment29 IN VARCHAR2,
1387 x_segment30 IN VARCHAR2,
1388 x_mode IN VARCHAR2 ) AS
1389 /*
1390 || Created By : [email protected]
1391 || Created On : 02-MAY-2002
1392 || Purpose : Handles the UPDATE DML logic for the table.
1393 || Known limitations, enhancements or remarks :
1394 || Change History :
1395 || Who When What
1396 || (reverse chronological order - newest change first)
1397 */
1398 x_last_update_date DATE ;
1399 x_last_updated_by NUMBER;
1400 x_last_update_login NUMBER;
1401
1402 l_mode VARCHAR2(1);
1403
1404 BEGIN
1405 IF X_mode is NULL then
1406 l_mode :='R';
1407 ELSE
1408 l_mode := X_mode;
1409 END IF;
1410
1411 x_last_update_date := SYSDATE;
1412 IF (l_MODE = 'I') THEN
1413 x_last_updated_by := 1;
1414 x_last_update_login := 0;
1415 ELSIF (l_mode = 'R') THEN
1416 x_last_updated_by := fnd_global.user_id;
1417 IF x_last_updated_by IS NULL THEN
1418 x_last_updated_by := -1;
1419 END IF;
1420 x_last_update_login := fnd_global.login_id;
1421 IF (x_last_update_login IS NULL) THEN
1422 x_last_update_login := -1;
1423 END IF;
1424 ELSE
1425 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1426 -- bug 3199481, start block
1427 IF (l_unexp_level >= l_debug_level) THEN
1428 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igi_dos_trx_dest_pkg.update_row.Msg1',FALSE);
1429 END IF;
1430 -- bug 3199481, end block
1431 app_exception.raise_exception;
1432 END IF;
1433
1434 before_dml(
1435 p_action => 'UPDATE',
1436 x_rowid => x_rowid,
1437 x_sob_id => x_sob_id,
1438 x_trx_id => x_trx_id,
1439 x_dest_trx_id => x_dest_trx_id,
1440 x_source_trx_id => x_source_trx_id,
1441 x_source_id => x_source_id,
1442 x_destination_id => x_destination_id,
1443 x_code_combination_id => x_code_combination_id,
1444 x_profile_code => x_profile_code,
1445 x_budget_name => x_budget_name,
1446 x_budget_entity_id => x_budget_entity_id,
1447 x_budget_amount => x_budget_amount,
1448 x_funds_available => x_funds_available,
1452 x_actual_segments => x_actual_segments,
1449 x_new_balance => x_new_balance,
1450 x_currency_code => x_currency_code,
1451 x_visible_segments => x_visible_segments,
1453 x_m_budget_amount => x_m_budget_amount,
1454 x_m_budget_amt_exch_rate => x_m_budget_amt_exch_rate,
1455 x_m_budget_amt_exch_rate_type => x_m_budget_amt_exch_rate_type,
1456 x_m_budget_amt_exch_date => x_m_budget_amt_exch_date,
1457 x_m_budget_amt_exch_status => x_m_budget_amt_exch_status,
1458 x_m_funds_avail => x_m_funds_avail,
1459 x_m_funds_avail_exch_rate => x_m_funds_avail_exch_rate,
1460 x_m_funds_avail_exch_rate_type => x_m_funds_avail_exch_rate_type,
1461 x_m_funds_avail_exch_date => x_m_funds_avail_exch_date,
1462 x_m_funds_avail_exch_status => x_m_funds_avail_exch_status,
1463 x_m_new_balance => x_m_new_balance,
1464 x_m_new_balance_exch_rate => x_m_new_balance_exch_rate,
1465 x_m_new_balance_exch_rate_type => x_m_new_balance_exch_rate_type,
1466 x_m_new_balance_exch_date => x_m_new_balance_exch_date,
1467 x_m_new_balance_exch_status => x_m_new_balance_exch_status,
1468 x_dossier_id => x_dossier_id,
1469 x_budget_version_id => x_budget_version_id,
1470 x_period_name => x_period_name,
1471 x_percentage => x_percentage,
1472 x_status => x_status,
1473 x_group_id => x_group_id,
1474 x_quarter_num => x_quarter_num,
1475 x_period_year => x_period_year,
1476 x_period_num => x_period_num,
1477 x_line_num => x_line_num,
1478 x_segment1 => x_segment1,
1479 x_segment2 => x_segment2,
1480 x_segment3 => x_segment3,
1481 x_segment4 => x_segment4,
1482 x_segment5 => x_segment5,
1483 x_segment6 => x_segment6,
1484 x_segment7 => x_segment7,
1485 x_segment8 => x_segment8,
1486 x_segment9 => x_segment9,
1487 x_segment10 => x_segment10,
1488 x_segment11 => x_segment11,
1489 x_segment12 => x_segment12,
1490 x_segment13 => x_segment13,
1491 x_segment14 => x_segment14,
1492 x_segment15 => x_segment15,
1493 x_segment16 => x_segment16,
1494 x_segment17 => x_segment17,
1495 x_segment18 => x_segment18,
1496 x_segment19 => x_segment19,
1497 x_segment20 => x_segment20,
1498 x_segment21 => x_segment21,
1499 x_segment22 => x_segment22,
1500 x_segment23 => x_segment23,
1501 x_segment24 => x_segment24,
1502 x_segment25 => x_segment25,
1503 x_segment26 => x_segment26,
1504 x_segment27 => x_segment27,
1505 x_segment28 => x_segment28,
1506 x_segment29 => x_segment29,
1507 x_segment30 => x_segment30,
1508 x_creation_date => x_last_update_date,
1509 x_created_by => x_last_updated_by,
1510 x_last_update_date => x_last_update_date,
1511 x_last_updated_by => x_last_updated_by,
1512 x_last_update_login => x_last_update_login
1513 );
1514
1515 UPDATE igi_dos_trx_dest
1516 SET
1517 sob_id = new_references.sob_id,
1518 trx_id = new_references.trx_id,
1519 source_trx_id = new_references.source_trx_id,
1520 source_id = new_references.source_id,
1521 destination_id = new_references.destination_id,
1522 code_combination_id = new_references.code_combination_id,
1523 profile_code = new_references.profile_code,
1524 budget_name = new_references.budget_name,
1525 budget_entity_id = new_references.budget_entity_id,
1526 budget_amount = new_references.budget_amount,
1527 funds_available = new_references.funds_available,
1528 new_balance = new_references.new_balance,
1529 currency_code = new_references.currency_code,
1530 visible_segments = new_references.visible_segments,
1531 actual_segments = new_references.actual_segments,
1532 mrc_budget_amount = new_references.mrc_budget_amount,
1533 mrc_budget_amt_exch_rate = new_references.mrc_budget_amt_exch_rate,
1534 mrc_budget_amt_exch_rate_type = new_references.mrc_budget_amt_exch_rate_type,
1535 mrc_budget_amt_exch_date = new_references.mrc_budget_amt_exch_date,
1536 mrc_budget_amt_exch_status = new_references.mrc_budget_amt_exch_status,
1537 mrc_funds_avail = new_references.mrc_funds_avail,
1538 mrc_funds_avail_exch_rate = new_references.mrc_funds_avail_exch_rate,
1539 mrc_funds_avail_exch_rate_type = new_references.mrc_funds_avail_exch_rate_type,
1540 mrc_funds_avail_exch_date = new_references.mrc_funds_avail_exch_date,
1541 mrc_funds_avail_exch_status = new_references.mrc_funds_avail_exch_status,
1542 mrc_new_balance = new_references.mrc_new_balance,
1543 mrc_new_balance_exch_rate = new_references.mrc_new_balance_exch_rate,
1544 mrc_new_balance_exch_rate_type = new_references.mrc_new_balance_exch_rate_type,
1545 mrc_new_balance_exch_date = new_references.mrc_new_balance_exch_date,
1546 mrc_new_balance_exch_status = new_references.mrc_new_balance_exch_status,
1547 dossier_id = new_references.dossier_id,
1548 budget_version_id = new_references.budget_version_id,
1549 period_name = new_references.period_name,
1550 percentage = new_references.percentage,
1551 status = new_references.status,
1552 group_id = new_references.group_id,
1553 quarter_num = new_references.quarter_num,
1554 period_year = new_references.period_year,
1555 period_num = new_references.period_num,
1556 line_num = new_references.line_num,
1557 segment1 = new_references.segment1,
1558 segment2 = new_references.segment2,
1559 segment3 = new_references.segment3,
1560 segment4 = new_references.segment4,
1561 segment5 = new_references.segment5,
1562 segment6 = new_references.segment6,
1563 segment7 = new_references.segment7,
1564 segment8 = new_references.segment8,
1565 segment9 = new_references.segment9,
1566 segment10 = new_references.segment10,
1567 segment11 = new_references.segment11,
1568 segment12 = new_references.segment12,
1569 segment13 = new_references.segment13,
1570 segment14 = new_references.segment14,
1571 segment15 = new_references.segment15,
1572 segment16 = new_references.segment16,
1573 segment17 = new_references.segment17,
1574 segment18 = new_references.segment18,
1575 segment19 = new_references.segment19,
1576 segment20 = new_references.segment20,
1577 segment21 = new_references.segment21,
1578 segment22 = new_references.segment22,
1579 segment23 = new_references.segment23,
1580 segment24 = new_references.segment24,
1581 segment25 = new_references.segment25,
1582 segment26 = new_references.segment26,
1583 segment27 = new_references.segment27,
1584 segment28 = new_references.segment28,
1585 segment29 = new_references.segment29,
1586 segment30 = new_references.segment30,
1587 last_update_date = x_last_update_date,
1588 last_updated_by = x_last_updated_by,
1589 last_update_login = x_last_update_login
1590 WHERE rowid = x_rowid;
1591
1592 IF (SQL%NOTFOUND) THEN
1593 RAISE NO_DATA_FOUND;
1594 END IF;
1595
1596 END update_row;
1597
1598
1599 PROCEDURE add_row (
1600 x_rowid IN OUT NOCOPY VARCHAR2,
1601 x_sob_id IN NUMBER,
1602 x_trx_id IN NUMBER,
1603 x_dest_trx_id IN OUT NOCOPY NUMBER,
1604 x_source_trx_id IN NUMBER,
1605 x_source_id IN NUMBER,
1606 x_destination_id IN NUMBER,
1607 x_code_combination_id IN NUMBER,
1608 x_profile_code IN VARCHAR2,
1609 x_budget_name IN VARCHAR2,
1610 x_budget_entity_id IN NUMBER,
1611 x_budget_amount IN NUMBER,
1612 x_funds_available IN NUMBER,
1613 x_new_balance IN NUMBER,
1614 x_currency_code IN VARCHAR2,
1615 x_visible_segments IN VARCHAR2,
1616 x_actual_segments IN VARCHAR2,
1617 x_m_budget_amount IN NUMBER,
1618 x_m_budget_amt_exch_rate IN NUMBER,
1619 x_m_budget_amt_exch_rate_type IN VARCHAR2,
1620 x_m_budget_amt_exch_date IN DATE,
1621 x_m_budget_amt_exch_status IN VARCHAR2,
1622 x_m_funds_avail IN NUMBER,
1623 x_m_funds_avail_exch_rate IN NUMBER,
1624 x_m_funds_avail_exch_rate_type IN VARCHAR2,
1625 x_m_funds_avail_exch_date IN DATE,
1626 x_m_funds_avail_exch_status IN VARCHAR2,
1627 x_m_new_balance IN NUMBER,
1628 x_m_new_balance_exch_rate IN NUMBER,
1629 x_m_new_balance_exch_rate_type IN VARCHAR2,
1630 x_m_new_balance_exch_date IN DATE,
1631 x_m_new_balance_exch_status IN VARCHAR2,
1632 x_dossier_id IN NUMBER,
1633 x_budget_version_id IN NUMBER,
1634 x_period_name IN VARCHAR2,
1635 x_percentage IN NUMBER,
1636 x_status IN VARCHAR2,
1637 x_group_id IN NUMBER,
1638 x_quarter_num IN NUMBER,
1639 x_period_year IN NUMBER,
1640 x_period_num IN NUMBER,
1641 x_line_num IN NUMBER,
1642 x_segment1 IN VARCHAR2,
1643 x_segment2 IN VARCHAR2,
1644 x_segment3 IN VARCHAR2,
1645 x_segment4 IN VARCHAR2,
1646 x_segment5 IN VARCHAR2,
1647 x_segment6 IN VARCHAR2,
1648 x_segment7 IN VARCHAR2,
1649 x_segment8 IN VARCHAR2,
1650 x_segment9 IN VARCHAR2,
1651 x_segment10 IN VARCHAR2,
1652 x_segment11 IN VARCHAR2,
1653 x_segment12 IN VARCHAR2,
1654 x_segment13 IN VARCHAR2,
1655 x_segment14 IN VARCHAR2,
1656 x_segment15 IN VARCHAR2,
1660 x_segment19 IN VARCHAR2,
1657 x_segment16 IN VARCHAR2,
1658 x_segment17 IN VARCHAR2,
1659 x_segment18 IN VARCHAR2,
1661 x_segment20 IN VARCHAR2,
1662 x_segment21 IN VARCHAR2,
1663 x_segment22 IN VARCHAR2,
1664 x_segment23 IN VARCHAR2,
1665 x_segment24 IN VARCHAR2,
1666 x_segment25 IN VARCHAR2,
1667 x_segment26 IN VARCHAR2,
1668 x_segment27 IN VARCHAR2,
1669 x_segment28 IN VARCHAR2,
1670 x_segment29 IN VARCHAR2,
1671 x_segment30 IN VARCHAR2,
1672 x_mode IN VARCHAR2 ) AS
1673 /*
1674 || Created By : [email protected]
1675 || Created On : 02-MAY-2002
1676 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1677 || Known limitations, enhancements or remarks :
1678 || Change History :
1679 || Who When What
1680 || (reverse chronological order - newest change first)
1681 */
1682 CURSOR c1 IS
1683 SELECT rowid
1684 FROM igi_dos_trx_dest
1685 WHERE dest_trx_id = x_dest_trx_id;
1686
1687 l_mode VARCHAR2(1);
1688 BEGIN
1689 IF X_mode is NULL then
1690 l_mode :='R';
1691 ELSE
1692 l_mode := X_mode;
1693 END IF;
1694
1695 OPEN c1;
1696 FETCH c1 INTO x_rowid;
1697 IF (c1%NOTFOUND) THEN
1698 CLOSE c1;
1699
1700 insert_row (
1701 x_rowid,
1702 x_sob_id,
1703 x_trx_id,
1704 x_dest_trx_id,
1705 x_source_trx_id,
1706 x_source_id,
1707 x_destination_id,
1708 x_code_combination_id,
1709 x_profile_code,
1710 x_budget_name,
1711 x_budget_entity_id,
1712 x_budget_amount,
1713 x_funds_available,
1714 x_new_balance,
1715 x_currency_code,
1716 x_visible_segments,
1717 x_actual_segments,
1718 x_m_budget_amount,
1719 x_m_budget_amt_exch_rate,
1720 x_m_budget_amt_exch_rate_type,
1721 x_m_budget_amt_exch_date,
1722 x_m_budget_amt_exch_status,
1723 x_m_funds_avail,
1724 x_m_funds_avail_exch_rate,
1725 x_m_funds_avail_exch_rate_type,
1726 x_m_funds_avail_exch_date,
1727 x_m_funds_avail_exch_status,
1728 x_m_new_balance,
1729 x_m_new_balance_exch_rate,
1730 x_m_new_balance_exch_rate_type,
1731 x_m_new_balance_exch_date,
1732 x_m_new_balance_exch_status,
1733 x_dossier_id,
1734 x_budget_version_id,
1735 x_period_name,
1736 x_percentage,
1737 x_status,
1738 x_group_id,
1739 x_quarter_num,
1740 x_period_year,
1741 x_period_num,
1742 x_line_num,
1743 x_segment1,
1744 x_segment2,
1745 x_segment3,
1746 x_segment4,
1747 x_segment5,
1748 x_segment6,
1749 x_segment7,
1750 x_segment8,
1751 x_segment9,
1752 x_segment10,
1753 x_segment11,
1754 x_segment12,
1755 x_segment13,
1756 x_segment14,
1757 x_segment15,
1758 x_segment16,
1759 x_segment17,
1760 x_segment18,
1761 x_segment19,
1762 x_segment20,
1763 x_segment21,
1764 x_segment22,
1765 x_segment23,
1766 x_segment24,
1767 x_segment25,
1768 x_segment26,
1769 x_segment27,
1770 x_segment28,
1771 x_segment29,
1772 x_segment30,
1773 l_mode
1774 );
1775 RETURN;
1776 END IF;
1777 CLOSE c1;
1778
1779 update_row (
1780 x_rowid,
1781 x_sob_id,
1782 x_trx_id,
1783 x_dest_trx_id,
1784 x_source_trx_id,
1785 x_source_id,
1786 x_destination_id,
1787 x_code_combination_id,
1788 x_profile_code,
1789 x_budget_name,
1790 x_budget_entity_id,
1791 x_budget_amount,
1792 x_funds_available,
1793 x_new_balance,
1794 x_currency_code,
1795 x_visible_segments,
1796 x_actual_segments,
1797 x_m_budget_amount,
1798 x_m_budget_amt_exch_rate,
1799 x_m_budget_amt_exch_rate_type,
1800 x_m_budget_amt_exch_date,
1801 x_m_budget_amt_exch_status,
1802 x_m_funds_avail,
1803 x_m_funds_avail_exch_rate,
1804 x_m_funds_avail_exch_rate_type,
1805 x_m_funds_avail_exch_date,
1806 x_m_funds_avail_exch_status,
1807 x_m_new_balance,
1808 x_m_new_balance_exch_rate,
1809 x_m_new_balance_exch_rate_type,
1810 x_m_new_balance_exch_date,
1811 x_m_new_balance_exch_status,
1812 x_dossier_id,
1813 x_budget_version_id,
1814 x_period_name,
1815 x_percentage,
1816 x_status,
1817 x_group_id,
1818 x_quarter_num,
1819 x_period_year,
1820 x_period_num,
1821 x_line_num,
1825 x_segment4,
1822 x_segment1,
1823 x_segment2,
1824 x_segment3,
1826 x_segment5,
1827 x_segment6,
1828 x_segment7,
1829 x_segment8,
1830 x_segment9,
1831 x_segment10,
1832 x_segment11,
1833 x_segment12,
1834 x_segment13,
1835 x_segment14,
1836 x_segment15,
1837 x_segment16,
1838 x_segment17,
1839 x_segment18,
1840 x_segment19,
1841 x_segment20,
1842 x_segment21,
1843 x_segment22,
1847 x_segment26,
1844 x_segment23,
1845 x_segment24,
1846 x_segment25,
1848 x_segment27,
1849 x_segment28,
1850 x_segment29,
1851 x_segment30,
1852 l_mode
1853 );
1854
1855 END add_row;
1856
1857
1858 PROCEDURE delete_row (
1859 x_rowid IN VARCHAR2
1860 ) AS
1861 /*
1862 || Created By : [email protected]
1863 || Created On : 02-MAY-2002
1864 || Purpose : Handles the DELETE DML logic for the table.
1865 || Known limitations, enhancements or remarks :
1866 || Change History :
1867 || Who When What
1868 || (reverse chronological order - newest change first)
1869 */
1870 BEGIN
1871
1872 before_dml(
1873 p_action => 'DELETE',
1874 x_rowid => x_rowid,
1875 x_sob_id => NULL,
1876 x_trx_id => NULL,
1877 x_dest_trx_id => NULL,
1878 x_source_trx_id => NULL,
1879 x_source_id => NULL,
1880 x_destination_id => NULL,
1881 x_code_combination_id => NULL,
1882 x_profile_code => NULL,
1883 x_budget_name => NULL,
1884 x_budget_entity_id => NULL,
1885 x_budget_amount => NULL,
1886 x_funds_available => NULL,
1887 x_new_balance => NULL,
1888 x_currency_code => NULL,
1889 x_visible_segments => NULL,
1890 x_actual_segments => NULL,
1891 x_m_budget_amount => NULL,
1892 x_m_budget_amt_exch_rate => NULL,
1893 x_m_budget_amt_exch_rate_type => NULL,
1894 x_m_budget_amt_exch_date => NULL,
1895 x_m_budget_amt_exch_status => NULL,
1896 x_m_funds_avail => NULL,
1897 x_m_funds_avail_exch_rate => NULL,
1898 x_m_funds_avail_exch_rate_type => NULL,
1899 x_m_funds_avail_exch_date => NULL,
1900 x_m_funds_avail_exch_status => NULL,
1901 x_m_new_balance => NULL,
1902 x_m_new_balance_exch_rate => NULL,
1903 x_m_new_balance_exch_rate_type => NULL,
1904 x_m_new_balance_exch_date => NULL,
1905 x_m_new_balance_exch_status => NULL,
1906 x_dossier_id => NULL,
1907 x_budget_version_id => NULL,
1908 x_period_name => NULL,
1909 x_percentage => NULL,
1910 x_status => NULL,
1911 x_group_id => NULL,
1912 x_quarter_num => NULL,
1913 x_period_year => NULL,
1914 x_period_num => NULL,
1915 x_line_num => NULL,
1916 x_segment1 => NULL,
1917 x_segment2 => NULL,
1918 x_segment3 => NULL,
1919 x_segment4 => NULL,
1920 x_segment5 => NULL,
1921 x_segment6 => NULL,
1922 x_segment7 => NULL,
1923 x_segment8 => NULL,
1924 x_segment9 => NULL,
1925 x_segment10 => NULL,
1926 x_segment11 => NULL,
1927 x_segment12 => NULL,
1928 x_segment13 => NULL,
1929 x_segment14 => NULL,
1930 x_segment15 => NULL,
1931 x_segment16 => NULL,
1932 x_segment17 => NULL,
1933 x_segment18 => NULL,
1934 x_segment19 => NULL,
1935 x_segment20 => NULL,
1936 x_segment21 => NULL,
1937 x_segment22 => NULL,
1938 x_segment23 => NULL,
1939 x_segment24 => NULL,
1940 x_segment25 => NULL,
1941 x_segment26 => NULL,
1942 x_segment27 => NULL,
1943 x_segment28 => NULL,
1944 x_segment29 => NULL,
1945 x_segment30 => NULL,
1946 x_creation_date => NULL,
1947 x_created_by => NULL,
1948 x_last_update_date => NULL,
1949 x_last_updated_by => NULL,
1950 x_last_update_login => NULL
1951 );
1952
1953
1954 DELETE FROM igi_dos_trx_dest
1955 WHERE rowid = x_rowid;
1956
1957 --IF (SQL%NOTFOUND) THEN
1958 -- RAISE NO_DATA_FOUND;
1959 --END IF;
1960
1961 END delete_row;
1962 Begin
1963 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
1964
1965 l_state_level := FND_LOG.LEVEL_STATEMENT ;
1966 l_proc_level := FND_LOG.LEVEL_PROCEDURE ;
1967 l_event_level := FND_LOG.LEVEL_EVENT ;
1968 l_excep_level := FND_LOG.LEVEL_EXCEPTION ;
1969 l_error_level := FND_LOG.LEVEL_ERROR ;
1970 l_unexp_level := FND_LOG.LEVEL_UNEXPECTED ;
1971
1972
1973
1974 END igi_dos_trx_dest_pkg;