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