[Home] [Help]
PACKAGE BODY: APPS.IGI_EXP_AP_TRANS_PKG
Source
1 PACKAGE BODY igi_exp_ap_trans_pkg AS
2 /* $Header: igiexpyb.pls 120.4.12000000.1 2007/09/13 04:25:23 mbremkum ship $ */
3
4 --following variables added for bug 3199481: fnd logging changes: sdixit
5 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 l_state_level number := FND_LOG.LEVEL_STATEMENT;
7 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
8 l_event_level number := FND_LOG.LEVEL_EVENT;
9 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
10 l_error_level number := FND_LOG.LEVEL_ERROR;
11 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
12
13 l_rowid VARCHAR2(25);
14 old_references igi_exp_ap_trans_all%ROWTYPE;
15 new_references igi_exp_ap_trans_all%ROWTYPE;
16
17 PROCEDURE set_column_values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2 ,
20 x_ap_tran_id IN NUMBER ,
21 x_invoice_id IN NUMBER ,
22 x_du_id IN NUMBER ,
23 x_org_id IN NUMBER ,
24 x_attribute_category IN VARCHAR2 ,
25 x_attribute1 IN VARCHAR2 ,
26 x_attribute2 IN VARCHAR2 ,
27 x_attribute3 IN VARCHAR2 ,
28 x_attribute4 IN VARCHAR2 ,
29 x_attribute5 IN VARCHAR2 ,
30 x_attribute6 IN VARCHAR2 ,
31 x_attribute7 IN VARCHAR2 ,
32 x_attribute8 IN VARCHAR2 ,
33 x_attribute9 IN VARCHAR2 ,
34 x_attribute10 IN VARCHAR2 ,
35 x_attribute11 IN VARCHAR2 ,
36 x_attribute12 IN VARCHAR2 ,
37 x_attribute13 IN VARCHAR2 ,
38 x_attribute14 IN VARCHAR2 ,
39 x_attribute15 IN VARCHAR2 ,
40 x_creation_date IN DATE ,
41 x_created_by IN NUMBER ,
42 x_last_update_date IN DATE ,
43 x_last_updated_by IN NUMBER ,
44 x_last_update_login IN NUMBER ) AS
45 /*
46 || Created By : [email protected]
47 || Created On : 01-NOV-2001
48 || Purpose : Initialises the Old and New references for the columns of the table.
49 || Known limitations, enhancements or remarks :
50 || Change History :
51 || Who When What
52 || (reverse chronological order - newest change first)
53 */
54
55 CURSOR cur_old_ref_values IS
56 SELECT *
57 FROM IGI_EXP_AP_TRANS_ALL
58 WHERE rowid = x_rowid;
59
60 BEGIN
61
62 l_rowid := x_rowid;
63
64 -- Code for setting the Old and New Reference Values.
65 -- Populate Old Values.
66 OPEN cur_old_ref_values;
67 FETCH cur_old_ref_values INTO old_references;
68 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
69 CLOSE cur_old_ref_values;
70 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
71 --bug 3199481 fnd logging changes: sdixit: start block
72 IF (l_error_level >= l_debug_level ) THEN
73 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpyb.IGI_EXP_AP_TRANS_PKG.set_column_values',FALSE);
74 END IF;
75 --bug 3199481 fnd logging changes: sdixit: end block
76
77 app_exception.raise_exception;
78 RETURN;
79 END IF;
80 CLOSE cur_old_ref_values;
81
82 -- Populate New Values.
83 new_references.ap_tran_id := x_ap_tran_id;
84 new_references.invoice_id := x_invoice_id;
85 new_references.du_id := x_du_id;
86 new_references.org_id := x_org_id;
87 new_references.attribute_category := x_attribute_category;
88 new_references.attribute1 := x_attribute1;
89 new_references.attribute2 := x_attribute2;
90 new_references.attribute3 := x_attribute3;
91 new_references.attribute4 := x_attribute4;
92 new_references.attribute5 := x_attribute5;
93 new_references.attribute6 := x_attribute6;
94 new_references.attribute7 := x_attribute7;
95 new_references.attribute8 := x_attribute8;
96 new_references.attribute9 := x_attribute9;
97 new_references.attribute10 := x_attribute10;
98 new_references.attribute11 := x_attribute11;
99 new_references.attribute12 := x_attribute12;
100 new_references.attribute13 := x_attribute13;
101 new_references.attribute14 := x_attribute14;
102 new_references.attribute15 := x_attribute15;
103
104 IF (p_action = 'UPDATE') THEN
105 new_references.creation_date := old_references.creation_date;
106 new_references.created_by := old_references.created_by;
107 ELSE
108 new_references.creation_date := x_creation_date;
109 new_references.created_by := x_created_by;
110 END IF;
111
112 new_references.last_update_date := x_last_update_date;
113 new_references.last_updated_by := x_last_updated_by;
114 new_references.last_update_login := x_last_update_login;
115
116 END set_column_values;
117
118
119 PROCEDURE check_parent_existance AS
120 /*
121 || Created By : [email protected]
122 || Created On : 01-NOV-2001
123 || Purpose : Checks for the existance of Parent records.
124 || Known limitations, enhancements or remarks :
125 || Change History :
126 || Who When What
127 || (reverse chronological order - newest change first)
128 */
129 BEGIN
130
131 IF (((old_references.du_id = new_references.du_id)) OR
132 ((new_references.du_id IS NULL))) THEN
133 NULL;
134 ELSIF NOT igi_exp_dus_pkg.get_pk_for_validation (
135 new_references.du_id
136 ) THEN
137 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
138 --bug 3199481 fnd logging changes: sdixit: start block
139 IF (l_error_level >= l_debug_level ) THEN
140 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpyb.IGI_EXP_AP_TRANS_PKG.check_parent_existance',FALSE);
141 END IF;
142 --bug 3199481 fnd logging changes: sdixit: end block
143
144 app_exception.raise_exception;
145 END IF;
146
147 END check_parent_existance;
148
149
150 FUNCTION get_pk_for_validation (
151 x_ap_tran_id IN NUMBER
152 ) RETURN BOOLEAN AS
153 /*
154 || Created By : [email protected]
155 || Created On : 01-NOV-2001
156 || Purpose : Validates the Primary Key of the table.
157 || Known limitations, enhancements or remarks :
158 || Change History :
159 || Who When What
160 || (reverse chronological order - newest change first)
161 */
162 CURSOR cur_rowid IS
163 SELECT rowid
164 FROM igi_exp_ap_trans_all
165 WHERE ap_tran_id = x_ap_tran_id
166 FOR UPDATE NOWAIT;
167
168 lv_rowid cur_rowid%RowType;
169
170 BEGIN
171
172 OPEN cur_rowid;
173 FETCH cur_rowid INTO lv_rowid;
174 IF (cur_rowid%FOUND) THEN
175 CLOSE cur_rowid;
176 RETURN(TRUE);
177 ELSE
178 CLOSE cur_rowid;
179 RETURN(FALSE);
180 END IF;
181
182 END get_pk_for_validation;
183
184
185 PROCEDURE get_fk_igi_exp_dus (
186 x_du_id IN NUMBER
187 ) AS
188 /*
189 || Created By : [email protected]
190 || Created On : 01-NOV-2001
191 || Purpose : Validates the Foreign Keys for the table.
192 || Known limitations, enhancements or remarks :
193 || Change History :
194 || Who When What
195 || (reverse chronological order - newest change first)
196 */
197 CURSOR cur_rowid IS
198 SELECT rowid
199 FROM igi_exp_ap_trans_all
200 WHERE ((du_id = x_du_id));
201
202 lv_rowid cur_rowid%RowType;
203
204 BEGIN
205
206 OPEN cur_rowid;
207 FETCH cur_rowid INTO lv_rowid;
208 IF (cur_rowid%FOUND) THEN
209 CLOSE cur_rowid;
210 fnd_message.set_name('FND' ,'FND-CANNOT DELETE MASTER');
211 --bug 3199481 fnd logging changes: sdixit: start block
212 IF (l_error_level >= l_debug_level ) THEN
213 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpyb.IGI_EXP_AP_TRANS_PKG.get_fk_igi_exp_dus',FALSE);
214 END IF;
215 --bug 3199481 fnd logging changes: sdixit: end block
216 app_exception.raise_exception;
217 RETURN;
218 END IF;
219 CLOSE cur_rowid;
220
221 END get_fk_igi_exp_dus;
222
223
224 PROCEDURE before_dml (
225 p_action IN VARCHAR2,
226 x_rowid IN VARCHAR2 ,
227 x_ap_tran_id IN NUMBER ,
228 x_invoice_id IN NUMBER ,
229 x_du_id IN NUMBER ,
230 x_org_id IN NUMBER ,
231 x_attribute_category IN VARCHAR2 ,
232 x_attribute1 IN VARCHAR2 ,
233 x_attribute2 IN VARCHAR2 ,
234 x_attribute3 IN VARCHAR2 ,
235 x_attribute4 IN VARCHAR2 ,
236 x_attribute5 IN VARCHAR2 ,
237 x_attribute6 IN VARCHAR2 ,
238 x_attribute7 IN VARCHAR2 ,
239 x_attribute8 IN VARCHAR2 ,
240 x_attribute9 IN VARCHAR2 ,
241 x_attribute10 IN VARCHAR2 ,
242 x_attribute11 IN VARCHAR2 ,
243 x_attribute12 IN VARCHAR2 ,
244 x_attribute13 IN VARCHAR2 ,
245 x_attribute14 IN VARCHAR2 ,
246 x_attribute15 IN VARCHAR2 ,
247 x_creation_date IN DATE ,
248 x_created_by IN NUMBER ,
249 x_last_update_date IN DATE ,
250 x_last_updated_by IN NUMBER ,
251 x_last_update_login IN NUMBER ) AS
252 /*
253 || Created By : [email protected]
254 || Created On : 01-NOV-2001
255 || Purpose : Initialises the columns, Checks Constraints, Calls the
256 || Trigger Handlers for the table, before any DML operation.
257 || Known limitations, enhancements or remarks :
258 || Change History :
259 || Who When What
260 || (reverse chronological order - newest change first)
261 */
262 BEGIN
263
264 set_column_values (
265 p_action,
266 x_rowid,
267 x_ap_tran_id,
268 x_invoice_id,
269 x_du_id,
270 x_org_id,
271 x_attribute_category,
272 x_attribute1,
273 x_attribute2,
274 x_attribute3,
275 x_attribute4,
276 x_attribute5,
277 x_attribute6,
278 x_attribute7,
279 x_attribute8,
280 x_attribute9,
281 x_attribute10,
282 x_attribute11,
283 x_attribute12,
284 x_attribute13,
285 x_attribute14,
286 x_attribute15,
287 x_creation_date,
288 x_created_by,
289 x_last_update_date,
290 x_last_updated_by,
291 x_last_update_login
292 );
293
294 IF (p_action = 'INSERT') THEN
295 -- Call all the procedures related to Before Insert.
296 IF ( get_pk_for_validation(
297 new_references.ap_tran_id
298 )
299 ) THEN
300 fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
301 --bug 3199481 fnd logging changes: sdixit: start block
302 IF (l_error_level >= l_debug_level ) THEN
303 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpyb.IGI_EXP_AP_TRANS_PKG.before_dml.msg1',FALSE);
304 END IF;
305 --bug 3199481 fnd logging changes: sdixit: end block
306
307 app_exception.raise_exception;
308 END IF;
309 check_parent_existance;
310 ELSIF (p_action = 'UPDATE') THEN
311 -- Call all the procedures related to Before Update.
312 check_parent_existance;
313 ELSIF (p_action = 'VALIDATE_INSERT') THEN
314 -- Call all the procedures related to Before Insert.
315 IF ( get_pk_for_validation (
316 new_references.ap_tran_id
317 )
318 ) THEN
319 fnd_message.set_name('IGI','IGI_EXP_DUP_ROW');
320 --bug 3199481 fnd logging changes: sdixit: start block
321 IF (l_error_level >= l_debug_level ) THEN
322 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpyb.IGI_EXP_AP_TRANS_PKG.before_dml.msg2',FALSE);
323 END IF;
324 --bug 3199481 fnd logging changes: sdixit: end block
325
326 app_exception.raise_exception;
327 END IF;
328 END IF;
329
330 END before_dml;
331
332
333 PROCEDURE insert_row (
334 x_rowid IN OUT NOCOPY VARCHAR2,
335 x_ap_tran_id IN OUT NOCOPY NUMBER,
336 x_invoice_id IN NUMBER,
337 x_du_id IN NUMBER,
338 x_org_id IN NUMBER,
339 x_attribute_category IN VARCHAR2,
340 x_attribute1 IN VARCHAR2,
341 x_attribute2 IN VARCHAR2,
342 x_attribute3 IN VARCHAR2,
343 x_attribute4 IN VARCHAR2,
344 x_attribute5 IN VARCHAR2,
345 x_attribute6 IN VARCHAR2,
346 x_attribute7 IN VARCHAR2,
347 x_attribute8 IN VARCHAR2,
348 x_attribute9 IN VARCHAR2,
349 x_attribute10 IN VARCHAR2,
350 x_attribute11 IN VARCHAR2,
351 x_attribute12 IN VARCHAR2,
352 x_attribute13 IN VARCHAR2,
353 x_attribute14 IN VARCHAR2,
354 x_attribute15 IN VARCHAR2,
355 x_mode IN VARCHAR2 ) AS
356 /*
357 || Created By : [email protected]
358 || Created On : 01-NOV-2001
359 || Purpose : Handles the INSERT DML logic for the table.
360 || Known limitations, enhancements or remarks :
361 || Change History :
362 || Who When What
363 || (reverse chronological order - newest change first)
364 */
365 CURSOR c IS
366 SELECT rowid
367 FROM igi_exp_ap_trans_all
368 WHERE ap_tran_id = x_ap_tran_id;
369
370 x_last_update_date DATE;
371 x_last_updated_by NUMBER;
372 x_last_update_login NUMBER;
373
374 BEGIN
375
376 x_last_update_date := SYSDATE;
377 IF (x_mode = 'I') THEN
378 x_last_updated_by := 1;
379 x_last_update_login := 0;
380 ELSIF (x_mode = 'R') THEN
381 x_last_updated_by := fnd_global.user_id;
382 IF (x_last_updated_by IS NULL) THEN
383 x_last_updated_by := -1;
384 END IF;
385 x_last_update_login := fnd_global.login_id;
386 IF (x_last_update_login IS NULL) THEN
387 x_last_update_login := -1;
388 END IF;
389 ELSE
390 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
391 --bug 3199481 fnd logging changes: sdixit: start block
392 IF (l_error_level >= l_debug_level ) THEN
393 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpyb.IGI_EXP_AP_TRANS_PKG.insert_row',FALSE);
394 END IF;
395 --bug 3199481 fnd logging changes: sdixit: end block
396
397 app_exception.raise_exception;
398 END IF;
399
400 SELECT igi_exp_ap_trans_s1.NEXTVAL
401 INTO x_ap_tran_id
402 FROM dual;
403
404 before_dml(
405 p_action => 'INSERT',
406 x_rowid => x_rowid,
407 x_ap_tran_id => x_ap_tran_id,
408 x_invoice_id => x_invoice_id,
409 x_du_id => x_du_id,
410 x_org_id => x_org_id,
411 x_attribute_category => x_attribute_category,
412 x_attribute1 => x_attribute1,
413 x_attribute2 => x_attribute2,
414 x_attribute3 => x_attribute3,
415 x_attribute4 => x_attribute4,
416 x_attribute5 => x_attribute5,
417 x_attribute6 => x_attribute6,
418 x_attribute7 => x_attribute7,
419 x_attribute8 => x_attribute8,
420 x_attribute9 => x_attribute9,
421 x_attribute10 => x_attribute10,
422 x_attribute11 => x_attribute11,
423 x_attribute12 => x_attribute12,
424 x_attribute13 => x_attribute13,
425 x_attribute14 => x_attribute14,
426 x_attribute15 => x_attribute15,
427 x_creation_date => x_last_update_date,
428 x_created_by => x_last_updated_by,
429 x_last_update_date => x_last_update_date,
430 x_last_updated_by => x_last_updated_by,
431 x_last_update_login => x_last_update_login
432 );
433
434 INSERT INTO igi_exp_ap_trans_all (
435 ap_tran_id,
436 invoice_id,
437 du_id,
438 org_id,
439 attribute_category,
440 attribute1,
441 attribute2,
442 attribute3,
443 attribute4,
444 attribute5,
445 attribute6,
446 attribute7,
447 attribute8,
448 attribute9,
449 attribute10,
450 attribute11,
451 attribute12,
452 attribute13,
453 attribute14,
454 attribute15,
455 creation_date,
456 created_by,
457 last_update_date,
458 last_updated_by,
459 last_update_login
460 ) VALUES (
461 new_references.ap_tran_id,
462 new_references.invoice_id,
463 new_references.du_id,
464 new_references.org_id,
465 new_references.attribute_category,
466 new_references.attribute1,
467 new_references.attribute2,
468 new_references.attribute3,
469 new_references.attribute4,
470 new_references.attribute5,
471 new_references.attribute6,
472 new_references.attribute7,
473 new_references.attribute8,
474 new_references.attribute9,
475 new_references.attribute10,
476 new_references.attribute11,
477 new_references.attribute12,
478 new_references.attribute13,
479 new_references.attribute14,
480 new_references.attribute15,
481 x_last_update_date,
482 x_last_updated_by,
483 x_last_update_date,
484 x_last_updated_by,
485 x_last_update_login
486 );
487
488 OPEN c;
489 FETCH c INTO x_rowid;
490 IF (c%NOTFOUND) THEN
491 CLOSE c;
492 RAISE NO_DATA_FOUND;
493 END IF;
494 CLOSE c;
495
496 END insert_row;
497
498
499 PROCEDURE lock_row (
500 x_rowid IN VARCHAR2,
501 x_ap_tran_id IN NUMBER,
502 x_invoice_id IN NUMBER,
503 x_du_id IN NUMBER,
504 x_org_id IN NUMBER,
505 x_attribute_category IN VARCHAR2,
506 x_attribute1 IN VARCHAR2,
507 x_attribute2 IN VARCHAR2,
508 x_attribute3 IN VARCHAR2,
509 x_attribute4 IN VARCHAR2,
510 x_attribute5 IN VARCHAR2,
511 x_attribute6 IN VARCHAR2,
512 x_attribute7 IN VARCHAR2,
513 x_attribute8 IN VARCHAR2,
514 x_attribute9 IN VARCHAR2,
515 x_attribute10 IN VARCHAR2,
516 x_attribute11 IN VARCHAR2,
517 x_attribute12 IN VARCHAR2,
518 x_attribute13 IN VARCHAR2,
519 x_attribute14 IN VARCHAR2,
520 x_attribute15 IN VARCHAR2
521 ) AS
522 /*
523 || Created By : [email protected]
524 || Created On : 01-NOV-2001
525 || Purpose : Handles the LOCK mechanism for the table.
526 || Known limitations, enhancements or remarks :
527 || Change History :
528 || Who When What
529 || (reverse chronological order - newest change first)
530 */
531 CURSOR c1 IS
532 SELECT
533 invoice_id,
534 du_id,
535 org_id,
536 attribute_category,
537 attribute1,
538 attribute2,
539 attribute3,
540 attribute4,
541 attribute5,
542 attribute6,
543 attribute7,
544 attribute8,
545 attribute9,
546 attribute10,
547 attribute11,
548 attribute12,
549 attribute13,
550 attribute14,
551 attribute15
552 FROM igi_exp_ap_trans_all
553 WHERE rowid = x_rowid
554 FOR UPDATE NOWAIT;
555
556 tlinfo c1%ROWTYPE;
557
558 BEGIN
559
560 OPEN c1;
561 FETCH c1 INTO tlinfo;
562 IF (c1%notfound) THEN
563 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
564 --bug 3199481 fnd logging changes: sdixit: start block
565 IF (l_error_level >= l_debug_level ) THEN
566 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpyb.IGI_EXP_AP_TRANS_PKG.lock_row',FALSE);
567 END IF;
568 --bug 3199481 fnd logging changes: sdixit: end block
569
570 CLOSE c1;
571 app_exception.raise_exception;
572 RETURN;
573 END IF;
574 CLOSE c1;
575
576 IF (
577 (tlinfo.invoice_id = x_invoice_id)
578 AND (tlinfo.du_id = x_du_id)
579 AND (tlinfo.org_id = x_org_id)
580 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
581 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
582 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
583 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
584 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
585 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
586 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
587 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
588 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
589 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
590 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
591 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
592 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
593 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
594 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
595 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
596 ) THEN
597 NULL;
598 ELSE
599 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
600 --bug 3199481 fnd logging changes: sdixit: start block
601 IF (l_error_level >= l_debug_level ) THEN
602 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpyb.IGI_EXP_AP_TRANS_PKG.lock_row',FALSE);
603 END IF;
604 --bug 3199481 fnd logging changes: sdixit: end block
605
606 app_exception.raise_exception;
607 END IF;
608
609 RETURN;
610
611 END lock_row;
612
613
614 PROCEDURE update_row (
615 x_rowid IN VARCHAR2,
616 x_ap_tran_id IN NUMBER,
617 x_invoice_id IN NUMBER,
618 x_du_id IN NUMBER,
619 x_org_id IN NUMBER,
620 x_attribute_category IN VARCHAR2,
621 x_attribute1 IN VARCHAR2,
622 x_attribute2 IN VARCHAR2,
623 x_attribute3 IN VARCHAR2,
624 x_attribute4 IN VARCHAR2,
625 x_attribute5 IN VARCHAR2,
626 x_attribute6 IN VARCHAR2,
627 x_attribute7 IN VARCHAR2,
628 x_attribute8 IN VARCHAR2,
629 x_attribute9 IN VARCHAR2,
630 x_attribute10 IN VARCHAR2,
631 x_attribute11 IN VARCHAR2,
632 x_attribute12 IN VARCHAR2,
633 x_attribute13 IN VARCHAR2,
634 x_attribute14 IN VARCHAR2,
635 x_attribute15 IN VARCHAR2,
636 x_mode IN VARCHAR2 ) AS
637 /*
638 || Created By : [email protected]
639 || Created On : 01-NOV-2001
640 || Purpose : Handles the UPDATE DML logic for the table.
641 || Known limitations, enhancements or remarks :
642 || Change History :
643 || Who When What
644 || (reverse chronological order - newest change first)
645 */
646 x_last_update_date DATE ;
647 x_last_updated_by NUMBER;
648 x_last_update_login NUMBER;
649
650 BEGIN
651
652 x_last_update_date := SYSDATE;
653 IF (X_MODE = 'I') THEN
654 x_last_updated_by := 1;
655 x_last_update_login := 0;
656 ELSIF (x_mode = 'R') THEN
657 x_last_updated_by := fnd_global.user_id;
658 IF x_last_updated_by IS NULL THEN
659 x_last_updated_by := -1;
660 END IF;
661 x_last_update_login := fnd_global.login_id;
662 IF (x_last_update_login IS NULL) THEN
663 x_last_update_login := -1;
664 END IF;
665 ELSE
666 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
667 --bug 3199481 fnd logging changes: sdixit: start block
668 IF (l_error_level >= l_debug_level ) THEN
669 FND_LOG.MESSAGE (l_error_level , 'igi.plsql.igiexpyb.IGI_EXP_AP_TRANS_PKG.update_row',FALSE);
670 END IF;
671 --bug 3199481 fnd logging changes: sdixit: end block
672
673 app_exception.raise_exception;
674 END IF;
675
676 before_dml(
677 p_action => 'UPDATE',
678 x_rowid => x_rowid,
679 x_ap_tran_id => x_ap_tran_id,
680 x_invoice_id => x_invoice_id,
681 x_du_id => x_du_id,
682 x_org_id => x_org_id,
683 x_attribute_category => x_attribute_category,
684 x_attribute1 => x_attribute1,
685 x_attribute2 => x_attribute2,
686 x_attribute3 => x_attribute3,
687 x_attribute4 => x_attribute4,
688 x_attribute5 => x_attribute5,
689 x_attribute6 => x_attribute6,
690 x_attribute7 => x_attribute7,
691 x_attribute8 => x_attribute8,
692 x_attribute9 => x_attribute9,
693 x_attribute10 => x_attribute10,
694 x_attribute11 => x_attribute11,
695 x_attribute12 => x_attribute12,
696 x_attribute13 => x_attribute13,
697 x_attribute14 => x_attribute14,
698 x_attribute15 => x_attribute15,
699 x_creation_date => x_last_update_date,
700 x_created_by => x_last_updated_by,
701 x_last_update_date => x_last_update_date,
702 x_last_updated_by => x_last_updated_by,
703 x_last_update_login => x_last_update_login
704 );
705
706 UPDATE igi_exp_ap_trans_all
707 SET
708 invoice_id = new_references.invoice_id,
709 du_id = new_references.du_id,
710 org_id = new_references.org_id,
711 attribute_category = new_references.attribute_category,
712 attribute1 = new_references.attribute1,
713 attribute2 = new_references.attribute2,
714 attribute3 = new_references.attribute3,
715 attribute4 = new_references.attribute4,
716 attribute5 = new_references.attribute5,
717 attribute6 = new_references.attribute6,
718 attribute7 = new_references.attribute7,
719 attribute8 = new_references.attribute8,
720 attribute9 = new_references.attribute9,
721 attribute10 = new_references.attribute10,
722 attribute11 = new_references.attribute11,
723 attribute12 = new_references.attribute12,
724 attribute13 = new_references.attribute13,
725 attribute14 = new_references.attribute14,
726 attribute15 = new_references.attribute15,
727 last_update_date = x_last_update_date,
728 last_updated_by = x_last_updated_by,
729 last_update_login = x_last_update_login
730 WHERE rowid = x_rowid;
731
732 IF (SQL%NOTFOUND) THEN
733 RAISE NO_DATA_FOUND;
734 END IF;
735
736 END update_row;
737
738
739 PROCEDURE add_row (
740 x_rowid IN OUT NOCOPY VARCHAR2,
741 x_ap_tran_id IN OUT NOCOPY NUMBER,
742 x_invoice_id IN NUMBER,
743 x_du_id IN NUMBER,
744 x_org_id IN NUMBER,
745 x_attribute_category IN VARCHAR2,
746 x_attribute1 IN VARCHAR2,
747 x_attribute2 IN VARCHAR2,
748 x_attribute3 IN VARCHAR2,
749 x_attribute4 IN VARCHAR2,
750 x_attribute5 IN VARCHAR2,
751 x_attribute6 IN VARCHAR2,
752 x_attribute7 IN VARCHAR2,
753 x_attribute8 IN VARCHAR2,
754 x_attribute9 IN VARCHAR2,
755 x_attribute10 IN VARCHAR2,
756 x_attribute11 IN VARCHAR2,
757 x_attribute12 IN VARCHAR2,
758 x_attribute13 IN VARCHAR2,
759 x_attribute14 IN VARCHAR2,
760 x_attribute15 IN VARCHAR2,
761 x_mode IN VARCHAR2 ) AS
762 /*
763 || Created By : [email protected]
764 || Created On : 01-NOV-2001
765 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
766 || Known limitations, enhancements or remarks :
767 || Change History :
768 || Who When What
769 || (reverse chronological order - newest change first)
770 */
771 CURSOR c1 IS
772 SELECT rowid
773 FROM igi_exp_ap_trans_all
774 WHERE ap_tran_id = x_ap_tran_id;
775
776 BEGIN
777
778 OPEN c1;
779 FETCH c1 INTO x_rowid;
780 IF (c1%NOTFOUND) THEN
781 CLOSE c1;
782
783 insert_row (
784 x_rowid,
785 x_ap_tran_id,
786 x_invoice_id,
787 x_du_id,
788 x_org_id,
789 x_attribute_category,
790 x_attribute1,
791 x_attribute2,
792 x_attribute3,
793 x_attribute4,
794 x_attribute5,
795 x_attribute6,
796 x_attribute7,
797 x_attribute8,
798 x_attribute9,
799 x_attribute10,
800 x_attribute11,
801 x_attribute12,
802 x_attribute13,
803 x_attribute14,
804 x_attribute15,
805 x_mode
806 );
807 RETURN;
808 END IF;
809 CLOSE c1;
810
811 update_row (
812 x_rowid,
813 x_ap_tran_id,
814 x_invoice_id,
815 x_du_id,
816 x_org_id,
817 x_attribute_category,
818 x_attribute1,
819 x_attribute2,
820 x_attribute3,
821 x_attribute4,
822 x_attribute5,
823 x_attribute6,
824 x_attribute7,
825 x_attribute8,
826 x_attribute9,
827 x_attribute10,
828 x_attribute11,
829 x_attribute12,
830 x_attribute13,
831 x_attribute14,
832 x_attribute15,
833 x_mode
834 );
835
836 END add_row;
837
838
839 PROCEDURE delete_row (
840 x_rowid IN VARCHAR2
841 ) AS
842 /*
843 || Created By : [email protected]
844 || Created On : 01-NOV-2001
845 || Purpose : Handles the DELETE DML logic for the table.
846 || Known limitations, enhancements or remarks :
847 || Change History :
848 || Who When What
849 || (reverse chronological order - newest change first)
850 */
851 BEGIN
852
853 before_dml (
854 p_action => 'DELETE',
855 x_rowid => x_rowid
856 );
857
858 DELETE FROM igi_exp_ap_trans_all
859 WHERE rowid = x_rowid;
860
861 IF (SQL%NOTFOUND) THEN
862 RAISE NO_DATA_FOUND;
863 END IF;
864
865 END delete_row;
866
867
868 END igi_exp_ap_trans_pkg;