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