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