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