[Home] [Help]
PACKAGE BODY: APPS.IGI_RPI_LINE_AUDIT_DET_ALL_PKG
Source
1 PACKAGE BODY igi_rpi_line_audit_det_all_pkg AS
2 /* $Header: igirladb.pls 120.4.12010000.2 2010/02/08 23:21:26 gaprasad ship $ */
3
4 l_debug_level number:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5
6 l_state_level number:=FND_LOG.LEVEL_STATEMENT;
7 l_proc_level number:=FND_LOG.LEVEL_PROCEDURE;
8 l_event_level number:=FND_LOG.LEVEL_EVENT;
9 l_excep_level number:=FND_LOG.LEVEL_EXCEPTION;
10 l_error_level number:=FND_LOG.LEVEL_ERROR;
11 l_unexp_level number:=FND_LOG.LEVEL_UNEXPECTED;
12
13 l_rowid VARCHAR2(25);
14 old_references igi_rpi_line_audit_det_all%ROWTYPE;
15 new_references igi_rpi_line_audit_det_all%ROWTYPE;
16
17 PROCEDURE set_column_values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2,
20 x_standing_charge_id IN NUMBER,
21 x_line_item_id IN NUMBER,
22 x_charge_item_number IN NUMBER,
23 x_item_id IN NUMBER,
24 x_price IN NUMBER,
25 x_effective_date IN DATE,
26 x_revised_price IN NUMBER,
27 x_revised_effective_date IN DATE,
28 x_run_id IN NUMBER,
29 x_org_id IN NUMBER,
30 x_previous_price IN NUMBER,
31 x_previous_effective_date IN DATE,
32 x_creation_date IN DATE,
33 x_created_by IN NUMBER,
34 x_last_update_date IN DATE,
35 x_last_updated_by IN NUMBER,
36 x_last_update_login IN NUMBER ) AS
37 /*
38 || Created By : panaraya
39 || Created On : 22-MAR-2002
40 || Purpose : Initialises the Old and New references for the columns of the table.
41 || Known limitations, enhancements or remarks :
42 || Change History :
43 || Who When What
44 || (reverse chronological order - newest change first)
45 */
46
47 CURSOR cur_old_ref_values IS
48 SELECT *
49 FROM IGI_RPI_LINE_AUDIT_DET_ALL
50 WHERE rowid = x_rowid;
51
52 BEGIN
53
54 l_rowid := x_rowid;
55
56 -- Code for setting the Old and New Reference Values.
57 -- Populate Old Values.
58 OPEN cur_old_ref_values;
59 FETCH cur_old_ref_values INTO old_references;
60 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
61 CLOSE cur_old_ref_values;
62 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
63 --igs_ge_msg_stack.add;
64
65 --Bug 3199481 (start)
66 If (l_unexp_level >= l_debug_level) then
67 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_line_audit_det_all_pkg.set_column_values.Msg1',FALSE);
68 End if;
69 --Bug 3199481 (end)
70 app_exception.raise_exception;
71 RETURN;
72 END IF;
73 CLOSE cur_old_ref_values;
74
75 -- Populate New Values.
76 new_references.standing_charge_id := x_standing_charge_id;
77 new_references.line_item_id := x_line_item_id;
78 new_references.charge_item_number := x_charge_item_number;
79 new_references.item_id := x_item_id;
80 new_references.price := x_price;
81 new_references.effective_date := x_effective_date;
82 new_references.revised_price := x_revised_price;
83 new_references.revised_effective_date := x_revised_effective_date;
84 new_references.run_id := x_run_id;
85 new_references.org_id := x_org_id;
86 new_references.previous_price := x_previous_price;
87 new_references.previous_effective_date := x_previous_effective_date;
88
89 IF (p_action = 'UPDATE') THEN
90 new_references.creation_date := old_references.creation_date;
91 new_references.created_by := old_references.created_by;
92 ELSE
93 new_references.creation_date := x_creation_date;
94 new_references.created_by := x_created_by;
95 END IF;
96
97 new_references.last_update_date := x_last_update_date;
98 new_references.last_updated_by := x_last_updated_by;
99 new_references.last_update_login := x_last_update_login;
100
101 END set_column_values;
102
103 PROCEDURE check_parent_existance(p_item_id in number, p_line_item_id in number) AS
104 /*
105 || Created By : panaraya
106 || Created On : 22-MAR-2002
107 || Purpose : Checks for the existance of Parent records.
108 || Known limitations, enhancements or remarks :
109 || Change History :
110 || Who When What
111 || (reverse chronological order - newest change first)
112 */
113
114 CURSOR c_item_id is select item_id from igi_rpi_items_all where item_id=p_item_id;
115
116 CURSOR c_line_item_id is select line_item_id from igi_rpi_line_details_all
117 where line_item_id=p_line_item_id;
118
119 l_item_id c_item_id%rowtype;
120 l_line_item_id c_line_item_id%rowtype;
121 BEGIN
122
123 open c_item_id;
124 fetch c_item_id into l_item_id;
125
126 open c_line_item_id;
127 fetch c_line_item_id into l_line_item_id;
128
129 IF (((old_references.line_item_id = new_references.line_item_id)) OR
130 ((new_references.line_item_id IS NULL))) THEN
131 NULL;
132 ELSIF c_item_id%NOTFOUND THEN
133 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
134 --igs_ge_msg_stack.add;
135
136 --Bug 3199481 (start)
137 If (l_unexp_level >= l_debug_level) then
138 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_line_audit_det_all_pkg.check_parent_existence.Msg1',FALSE);
139 End if;
140 --Bug 3199481 (end)
141 app_exception.raise_exception;
142 END IF;
143
144 IF (((old_references.item_id = new_references.item_id)) OR
145 ((new_references.item_id IS NULL))) THEN
146 NULL;
147 ELSIF c_line_item_id%NOTFOUND THEN
148
149 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
150 --igs_ge_msg_stack.add;
151 --Bug 3199481 (start)
152 If (l_unexp_level >= l_debug_level) then
153 FND_LOG.MESSAGE(l_unexp_level,'igi.rpi_line_audit_det_all_pkg.check_parent_existence.Msg2',FALSE);
154 End if;
155 --Bug 3199481 (end)
156 app_exception.raise_exception;
157 END IF;
158 close c_item_id;
159 close c_line_item_id;
160
161
162 END check_parent_existance;
163
164
165 PROCEDURE get_fk_igi_rpi_line_det_all (
166 x_line_item_id IN NUMBER
167 ) AS
168 /*
169 || Created By : panaraya
170 || Created On : 22-MAR-2002
171 || Purpose : Validates the Foreign Keys for the table.
172 || Known limitations, enhancements or remarks :
173 || Change History :
174 || Who When What
175 || (reverse chronological order - newest change first)
176 */
177 CURSOR cur_rowid IS
178 SELECT rowid
179 FROM igi_rpi_line_audit_det_all
180 WHERE ((line_item_id = x_line_item_id));
181
182 lv_rowid cur_rowid%RowType;
183
184 BEGIN
185
186 OPEN cur_rowid;
187 FETCH cur_rowid INTO lv_rowid;
188 IF (cur_rowid%FOUND) THEN
189 CLOSE cur_rowid;
190 -- fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
191 --igs_ge_msg_stack.add;
192 --Bug 3199481 (start)
193 If (l_unexp_level >= l_debug_level) then
194 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_line_audit_det_all_pkg.get_fk_igi_rpi_line_det_all.Msg1',FALSE);
195 End if;
196 --Bug 3199481 (end)
197 app_exception.raise_exception;
198 RETURN;
199 END IF;
200 CLOSE cur_rowid;
201
202 END get_fk_igi_rpi_line_det_all;
203
204
205 PROCEDURE get_fk_igi_rpi_items_all (
206 x_item_id IN NUMBER
207 ) AS
208 /*
209 || Created By : panaraya
210 || Created On : 22-MAR-2002
211 || Purpose : Validates the Foreign Keys for the table.
212 || Known limitations, enhancements or remarks :
213 || Change History :
214 || Who When What
215 || (reverse chronological order - newest change first)
216 */
217 CURSOR cur_rowid IS
218 SELECT rowid
219 FROM igi_rpi_line_audit_det_all
220 WHERE ((item_id = x_item_id));
221
222 lv_rowid cur_rowid%RowType;
223
224 BEGIN
225
226 OPEN cur_rowid;
227 FETCH cur_rowid INTO lv_rowid;
228 IF (cur_rowid%FOUND) THEN
229 CLOSE cur_rowid;
230 -- fnd_message.set_name ('IGS', 'IGS_FOREIGN_KEY_REFERENCE');
231 --igs_ge_msg_stack.add;
232 app_exception.raise_exception;
233 RETURN;
234 END IF;
235 CLOSE cur_rowid;
236
237 END get_fk_igi_rpi_items_all;
238
239
240 PROCEDURE before_dml (
241 p_action IN VARCHAR2,
242 x_rowid IN VARCHAR2,
243 x_standing_charge_id IN NUMBER,
244 x_line_item_id IN NUMBER,
245 x_charge_item_number IN NUMBER,
246 x_item_id IN NUMBER,
247 x_price IN NUMBER,
248 x_effective_date IN DATE,
249 x_revised_price IN NUMBER,
250 x_revised_effective_date IN DATE,
251 x_run_id IN NUMBER,
252 x_org_id IN NUMBER,
253 x_previous_price IN NUMBER,
254 x_previous_effective_date IN DATE,
255 x_creation_date IN DATE,
256 x_created_by IN NUMBER,
257 x_last_update_date IN DATE,
258 x_last_updated_by IN NUMBER,
259 x_last_update_login IN NUMBER ) AS
260 /*
261 || Created By : panaraya
262 || Created On : 22-MAR-2002
263 || Purpose : Initialises the columns, Checks Constraints, Calls the
264 || Trigger Handlers for the table, before any DML operation.
265 || Known limitations, enhancements or remarks :
266 || Change History :
267 || Who When What
268 || (reverse chronological order - newest change first)
269 */
270 BEGIN
271
272 set_column_values (
273 p_action,
274 x_rowid,
275 x_standing_charge_id,
276 x_line_item_id,
277 x_charge_item_number,
278 x_item_id,
279 x_price,
280 x_effective_date,
281 x_revised_price,
282 x_revised_effective_date,
283 x_run_id,
284 x_org_id,
285 x_previous_price,
286 x_previous_effective_date,
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
298 )
299 ) THEN
300 -- fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
301 --igs_ge_msg_stack.add;
302 app_exception.raise_exception;
303 END IF; */
304 check_parent_existance(x_item_id,x_line_item_id);
305 ELSIF (p_action = 'UPDATE') THEN
306 -- Call all the procedures related to Before Update.
307 check_parent_existance(x_item_id,x_line_item_id);
308 ELSIF (p_action = 'VALIDATE_INSERT') THEN
309 -- Call all the procedures related to Before Insert.
310 /* IF ( get_pk_for_validation (
311
312 )
313 ) THEN
314 -- fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
315 --igs_ge_msg_stack.add;
316 app_exception.raise_exception;
317 END IF;*/
318 null;
319 END IF;
320
321 END before_dml;
322
323
324 PROCEDURE insert_row (
325 x_rowid IN OUT NOCOPY VARCHAR2,
326 x_standing_charge_id IN NUMBER,
327 x_line_item_id IN NUMBER,
328 x_charge_item_number IN NUMBER,
329 x_item_id IN NUMBER,
330 x_price IN NUMBER,
331 x_effective_date IN DATE,
332 x_revised_price IN NUMBER,
333 x_revised_effective_date IN DATE,
334 x_run_id IN NUMBER,
335 x_org_id IN NUMBER,
336 x_previous_price IN NUMBER,
337 x_previous_effective_date IN DATE,
338 x_mode IN VARCHAR2,
339 x_old_vat_id IN NUMBER,
340 x_new_vat_id IN NUMBER,
341 x_request_id IN NUMBER ) AS
342 /*
343 || Created By : panaraya
344 || Created On : 22-MAR-2002
345 || Purpose : Handles the INSERT DML logic for the table.
346 || Known limitations, enhancements or remarks :
347 || Change History :
348 || Who When What
349 || (reverse chronological order - newest change first)
350 */
351 CURSOR c IS
352 SELECT rowid
353 FROM igi_rpi_line_audit_det_all
354 WHERE item_id=x_item_id and line_item_id=x_line_item_id;
355
356 x_last_update_date DATE;
357 x_last_updated_by NUMBER;
358 x_last_update_login NUMBER;
359
360 BEGIN
361
362 x_last_update_date := SYSDATE;
363 IF (x_mode = 'I') THEN
364 x_last_updated_by := 1;
365 x_last_update_login := 0;
366 ELSIF (x_mode = 'R') THEN
367 x_last_updated_by := fnd_global.user_id;
368 IF (x_last_updated_by IS NULL) THEN
369 x_last_updated_by := -1;
370 END IF;
371 x_last_update_login := fnd_global.login_id;
372 IF (x_last_update_login IS NULL) THEN
373 x_last_update_login := -1;
374 END IF;
375 ELSE
376 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
377 --igs_ge_msg_stack.add;
378 --Bug 3199481 (start)
379 If (l_unexp_level >= l_debug_level) then
380 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_line_audit_det_all_pkg.insert_row.Msg1',FALSE);
381 End if;
382 --Bug 3199481 (end)
383 app_exception.raise_exception;
384 END IF;
385
386 before_dml(
387 p_action => 'INSERT',
388 x_rowid => x_rowid,
389 x_standing_charge_id => x_standing_charge_id,
390 x_line_item_id => x_line_item_id,
391 x_charge_item_number => x_charge_item_number,
392 x_item_id => x_item_id,
393 x_price => x_price,
394 x_effective_date => x_effective_date,
395 x_revised_price => x_revised_price,
396 x_revised_effective_date => x_revised_effective_date,
397 x_run_id => x_run_id,
398 x_org_id => NVL (x_org_id,0),
399 x_previous_price => x_previous_price,
400 x_previous_effective_date => x_previous_effective_date,
401 x_creation_date => x_last_update_date,
402 x_created_by => x_last_updated_by,
403 x_last_update_date => x_last_update_date,
404 x_last_updated_by => x_last_updated_by,
405 x_last_update_login => x_last_update_login
406 );
407
408 INSERT INTO igi_rpi_line_audit_det_all (
409 standing_charge_id,
410 line_item_id,
411 charge_item_number,
412 item_id,
413 price,
414 effective_date,
415 revised_price,
416 revised_effective_date,
417 run_id,
418 org_id,
419 previous_price,
420 previous_effective_date,
421 creation_date,
422 created_by,
423 last_update_date,
424 last_updated_by,
425 last_update_login,
426 old_vat_id,
427 new_vat_id,
428 request_id
429 ) VALUES (
430 new_references.standing_charge_id,
431 new_references.line_item_id,
432 new_references.charge_item_number,
433 new_references.item_id,
434 new_references.price,
435 new_references.effective_date,
436 new_references.revised_price,
437 new_references.revised_effective_date,
438 new_references.run_id,
439 new_references.org_id,
440 new_references.previous_price,
441 new_references.previous_effective_date,
442 x_last_update_date,
443 x_last_updated_by,
444 x_last_update_date,
445 x_last_updated_by,
446 x_last_update_login,
447 x_old_vat_id,
448 x_new_vat_id,
449 x_request_id
450 );
451
452 OPEN c;
453 FETCH c INTO x_rowid;
454 IF (c%NOTFOUND) THEN
455 CLOSE c;
456 RAISE NO_DATA_FOUND;
457 END IF;
458 CLOSE c;
459
460 END insert_row;
461
462
463 PROCEDURE lock_row (
464 x_rowid IN VARCHAR2,
465 x_standing_charge_id IN NUMBER,
466 x_line_item_id IN NUMBER,
467 x_charge_item_number IN NUMBER,
468 x_item_id IN NUMBER,
469 x_price IN NUMBER,
470 x_effective_date IN DATE,
471 x_revised_price IN NUMBER,
472 x_revised_effective_date IN DATE,
473 x_run_id IN NUMBER,
474 x_org_id IN NUMBER,
475 x_previous_price IN NUMBER,
476 x_previous_effective_date IN DATE
477 ) AS
478 /*
479 || Created By : panaraya
480 || Created On : 22-MAR-2002
481 || Purpose : Handles the LOCK mechanism for the table.
482 || Known limitations, enhancements or remarks :
483 || Change History :
484 || Who When What
485 || (reverse chronological order - newest change first)
486 */
487 CURSOR c1 IS
488 SELECT
489 standing_charge_id,
490 line_item_id,
491 charge_item_number,
492 item_id,
493 price,
494 effective_date,
495 revised_price,
496 revised_effective_date,
497 run_id,
498 org_id,
499 previous_price,
500 previous_effective_date
501 FROM igi_rpi_line_audit_det_all
502 WHERE rowid = x_rowid
503 FOR UPDATE NOWAIT;
504
505 tlinfo c1%ROWTYPE;
506
507 BEGIN
508
509 OPEN c1;
510 FETCH c1 INTO tlinfo;
511 IF (c1%notfound) THEN
512 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
513 --igs_ge_msg_stack.add;
514
515 --Bug 3199481 (start)
516 If (l_unexp_level >= l_debug_level) then
517 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_line_audit_det_all_pkg.lock_row.Msg1',FALSE);
518 End if;
519 --Bug 3199481 (end)
520 CLOSE c1;
521 app_exception.raise_exception;
522 RETURN;
523 END IF;
524 CLOSE c1;
525
526 IF (
527 (tlinfo.standing_charge_id = x_standing_charge_id)
528 AND (tlinfo.line_item_id = x_line_item_id)
529 AND (tlinfo.charge_item_number = x_charge_item_number)
530 AND (tlinfo.item_id = x_item_id)
531 AND (tlinfo.price = x_price)
532 AND (tlinfo.effective_date = x_effective_date)
533 AND ((tlinfo.revised_price = x_revised_price) OR ((tlinfo.revised_price IS NULL) AND (X_revised_price IS NULL)))
534 AND ((tlinfo.revised_effective_date = x_revised_effective_date) OR ((tlinfo.revised_effective_date IS NULL) AND (X_revised_effective_date IS NULL)))
535 AND ((tlinfo.run_id = x_run_id) OR ((tlinfo.run_id IS NULL) AND (X_run_id IS NULL)))
536 AND ((tlinfo.org_id = x_org_id) OR ((tlinfo.org_id IS NULL) AND (X_org_id IS NULL)))
537 AND ((tlinfo.previous_price = x_previous_price) OR ((tlinfo.previous_price IS NULL) AND (X_previous_price IS NULL)))
538 AND ((tlinfo.previous_effective_date = x_previous_effective_date) OR ((tlinfo.previous_effective_date IS NULL) AND (X_previous_effective_date IS NULL)))
539 ) THEN
540 NULL;
541 ELSE
542 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
543 --igs_ge_msg_stack.add;
544
545 --Bug 3199481 (start)
546 If (l_unexp_level >= l_debug_level) then
547 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_line_audit_det_all_pkg.lock_row.Msg1',FALSE);
548 End if;
549 --Bug 3199481 (end)
550 app_exception.raise_exception;
551 END IF;
552
553 RETURN;
554
555 END lock_row;
556
557
558 PROCEDURE update_row (
559 x_rowid IN VARCHAR2,
560 x_standing_charge_id IN NUMBER,
561 x_line_item_id IN NUMBER,
562 x_charge_item_number IN NUMBER,
563 x_item_id IN NUMBER,
564 x_price IN NUMBER,
565 x_effective_date IN DATE,
566 x_revised_price IN NUMBER,
567 x_revised_effective_date IN DATE,
568 x_run_id IN NUMBER,
569 x_org_id IN NUMBER,
570 x_previous_price IN NUMBER,
571 x_previous_effective_date IN DATE,
572 x_mode IN VARCHAR2 ) AS
573 /*
574 || Created By : panaraya
575 || Created On : 22-MAR-2002
576 || Purpose : Handles the UPDATE DML logic for the table.
577 || Known limitations, enhancements or remarks :
578 || Change History :
579 || Who When What
580 || (reverse chronological order - newest change first)
581 */
582 x_last_update_date DATE ;
583 x_last_updated_by NUMBER;
584 x_last_update_login NUMBER;
585
586 BEGIN
587
588 x_last_update_date := SYSDATE;
589 IF (X_MODE = 'I') THEN
590 x_last_updated_by := 1;
591 x_last_update_login := 0;
592 ELSIF (x_mode = 'R') THEN
593 x_last_updated_by := fnd_global.user_id;
594 IF x_last_updated_by IS NULL THEN
595 x_last_updated_by := -1;
596 END IF;
597 x_last_update_login := fnd_global.login_id;
598 IF (x_last_update_login IS NULL) THEN
599 x_last_update_login := -1;
600 END IF;
601 ELSE
602 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
603 --igs_ge_msg_stack.add;
604 --Bug 3199481 (start)
605 If (l_unexp_level >= l_debug_level) then
606 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_line_audit_det_all_pkg.update_row.Msg1',FALSE);
607 End if;
608 --Bug 3199481 (end)
609 app_exception.raise_exception;
610 END IF;
611
612 before_dml(
613 p_action => 'UPDATE',
614 x_rowid => x_rowid,
615 x_standing_charge_id => x_standing_charge_id,
616 x_line_item_id => x_line_item_id,
617 x_charge_item_number => x_charge_item_number,
618 x_item_id => x_item_id,
619 x_price => x_price,
620 x_effective_date => x_effective_date,
621 x_revised_price => x_revised_price,
622 x_revised_effective_date => x_revised_effective_date,
623 x_run_id => x_run_id,
624 x_org_id => NVL (x_org_id,0),
625 x_previous_price => x_previous_price,
626 x_previous_effective_date => x_previous_effective_date,
627 x_creation_date => x_last_update_date,
628 x_created_by => x_last_updated_by,
629 x_last_update_date => x_last_update_date,
630 x_last_updated_by => x_last_updated_by,
631 x_last_update_login => x_last_update_login
632 );
633
634 UPDATE igi_rpi_line_audit_det_all
635 SET
636 standing_charge_id = new_references.standing_charge_id,
637 line_item_id = new_references.line_item_id,
638 charge_item_number = new_references.charge_item_number,
639 item_id = new_references.item_id,
640 price = new_references.price,
641 effective_date = new_references.effective_date,
642 revised_price = new_references.revised_price,
643 revised_effective_date = new_references.revised_effective_date,
644 run_id = new_references.run_id,
645 org_id = new_references.org_id,
646 previous_price = new_references.previous_price,
647 previous_effective_date = new_references.previous_effective_date,
648 last_update_date = x_last_update_date,
649 last_updated_by = x_last_updated_by,
650 last_update_login = x_last_update_login
651 WHERE rowid = x_rowid;
652
653 IF (SQL%NOTFOUND) THEN
654 RAISE NO_DATA_FOUND;
655 END IF;
656
657 END update_row;
658
659
660 PROCEDURE add_row (
661 x_rowid IN OUT NOCOPY VARCHAR2,
662 x_standing_charge_id IN NUMBER,
663 x_line_item_id IN NUMBER,
664 x_charge_item_number IN NUMBER,
665 x_item_id IN NUMBER,
666 x_price IN NUMBER,
667 x_effective_date IN DATE,
668 x_revised_price IN NUMBER,
669 x_revised_effective_date IN DATE,
670 x_run_id IN NUMBER,
671 x_org_id IN NUMBER,
672 x_previous_price IN NUMBER,
673 x_previous_effective_date IN DATE,
674 x_mode IN VARCHAR2 ) AS
675 /*
676 || Created By : panaraya
677 || Created On : 22-MAR-2002
678 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
679 || Known limitations, enhancements or remarks :
680 || Change History :
681 || Who When What
682 || (reverse chronological order - newest change first)
683 */
684 CURSOR c1 IS
685 SELECT rowid
686 FROM igi_rpi_line_audit_det_all
687 WHERE item_id=x_item_id and line_item_id=x_line_item_id;
688
689 BEGIN
690
691 OPEN c1;
692 FETCH c1 INTO x_rowid;
693 IF (c1%NOTFOUND) THEN
694 CLOSE c1;
695
696 insert_row (
697 x_rowid,
698 x_standing_charge_id,
699 x_line_item_id,
700 x_charge_item_number,
701 x_item_id,
702 x_price,
703 x_effective_date,
704 x_revised_price,
705 x_revised_effective_date,
706 x_run_id,
707 x_org_id,
708 x_previous_price,
709 x_previous_effective_date,
710 x_mode
711 );
712 RETURN;
713 END IF;
714 CLOSE c1;
715
716 update_row (
717 x_rowid,
718 x_standing_charge_id,
719 x_line_item_id,
720 x_charge_item_number,
721 x_item_id,
722 x_price,
723 x_effective_date,
724 x_revised_price,
725 x_revised_effective_date,
726 x_run_id,
727 x_org_id,
728 x_previous_price,
729 x_previous_effective_date,
730 x_mode
731 );
732
733 END add_row;
734
735
736 PROCEDURE delete_row (
737 x_rowid IN VARCHAR2
738 ) AS
739 /*
740 || Created By : panaraya
741 || Created On : 22-MAR-2002
742 || Purpose : Handles the DELETE DML logic for the table.
743 || Known limitations, enhancements or remarks :
744 || Change History :
745 || Who When What
746 || (reverse chronological order - newest change first)
747 */
748 BEGIN
749
750 before_dml (
751 p_action => 'DELETE',
752 x_rowid => x_rowid
753 );
754
755 DELETE FROM igi_rpi_line_audit_det_all
756 WHERE rowid = x_rowid;
757
758 IF (SQL%NOTFOUND) THEN
759 RAISE NO_DATA_FOUND;
760 END IF;
761
762 END delete_row;
763
764
765 END igi_rpi_line_audit_det_all_pkg;