[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_INTERFACE_PKG
Source
1 PACKAGE BODY igs_ad_interface_pkg AS
2 /* $Header: IGSAIB3B.pls 120.2 2005/09/22 23:52:29 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_interface_all%ROWTYPE;
6 new_references igs_ad_interface_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_person_number IN VARCHAR2 DEFAULT NULL,
12 x_org_id IN NUMBER DEFAULT NULL,
13 x_interface_id IN NUMBER DEFAULT NULL,
14 x_batch_id IN NUMBER DEFAULT NULL,
15 x_source_type_id IN NUMBER DEFAULT NULL,
16 x_surname IN VARCHAR2 DEFAULT NULL,
17 x_middle_name IN VARCHAR2 DEFAULT NULL,
18 x_given_names IN VARCHAR2 DEFAULT NULL,
19 x_preferred_given_name IN VARCHAR2 DEFAULT NULL,
20 x_sex IN VARCHAR2 DEFAULT NULL,
21 x_birth_dt IN DATE DEFAULT NULL,
22 x_title IN VARCHAR2 DEFAULT NULL,
23 x_suffix IN VARCHAR2 DEFAULT NULL,
24 x_pre_name_adjunct IN VARCHAR2 DEFAULT NULL,
25 x_level_of_qual IN NUMBER DEFAULT NULL,
26 x_proof_of_insurance IN VARCHAR2 DEFAULT NULL,
27 x_proof_of_immun IN VARCHAR2 DEFAULT NULL,
28 x_pref_alternate_id IN VARCHAR2 DEFAULT NULL,
29 x_person_id IN NUMBER DEFAULT NULL,
30 x_status IN VARCHAR2 DEFAULT NULL,
31 x_military_service_reg IN VARCHAR2 DEFAULT NULL,
32 x_veteran IN VARCHAR2 DEFAULT NULL,
33 x_match_ind IN VARCHAR2 DEFAULT NULL,
34 x_person_match_ind IN VARCHAR2 DEFAULT NULL,
35 x_error_code IN VARCHAR2 DEFAULT NULL,
36 x_record_status IN VARCHAR2 DEFAULT NULL,
37 x_interface_run_id IN NUMBER DEFAULT NULL,
38 x_attribute_category IN VARCHAR2 DEFAULT NULL,
39 x_attribute1 IN VARCHAR2 DEFAULT NULL,
40 x_attribute2 IN VARCHAR2 DEFAULT NULL,
41 x_attribute3 IN VARCHAR2 DEFAULT NULL,
42 x_attribute4 IN VARCHAR2 DEFAULT NULL,
43 x_attribute5 IN VARCHAR2 DEFAULT NULL,
44 x_attribute6 IN VARCHAR2 DEFAULT NULL,
45 x_attribute7 IN VARCHAR2 DEFAULT NULL,
46 x_attribute8 IN VARCHAR2 DEFAULT NULL,
47 x_attribute9 IN VARCHAR2 DEFAULT NULL,
48 x_attribute10 IN VARCHAR2 DEFAULT NULL,
49 x_attribute11 IN VARCHAR2 DEFAULT NULL,
50 x_attribute12 IN VARCHAR2 DEFAULT NULL,
51 x_attribute13 IN VARCHAR2 DEFAULT NULL,
52 x_attribute14 IN VARCHAR2 DEFAULT NULL,
53 x_attribute15 IN VARCHAR2 DEFAULT NULL,
54 x_attribute16 IN VARCHAR2 DEFAULT NULL,
55 x_attribute17 IN VARCHAR2 DEFAULT NULL,
56 x_attribute18 IN VARCHAR2 DEFAULT NULL,
57 x_attribute19 IN VARCHAR2 DEFAULT NULL,
58 x_attribute20 IN VARCHAR2 DEFAULT NULL,
59 x_creation_date IN DATE DEFAULT NULL,
60 x_created_by IN NUMBER DEFAULT NULL,
61 x_last_update_date IN DATE DEFAULT NULL,
62 x_last_updated_by IN NUMBER DEFAULT NULL,
63 x_last_update_login IN NUMBER DEFAULT NULL,
64 x_person_type_code IN VARCHAR2 DEFAULT NULL,
65 x_funnel_status IN VARCHAR2 DEFAULT NULL,
66 x_birth_city IN VARCHAR2 DEFAULT NULL,
67 x_birth_country IN VARCHAR2 DEFAULT NULL,
68 x_attribute21 IN VARCHAR2 DEFAULT NULL,
69 x_attribute22 IN VARCHAR2 DEFAULT NULL,
70 x_attribute23 IN VARCHAR2 DEFAULT NULL,
71 x_attribute24 IN VARCHAR2 DEFAULT NULL
72 ) AS
73 /*
74 || Created By : [email protected]
75 || Created On : 21-NOV-2000
76 || Purpose : Initialises the Old and New references for the columns of the table.
77 || Known limitations, enhancements or remarks :
78 || Change History :
79 || Who When What
80 || (reverse chronological order - newest change first)
81 */
82
83 CURSOR cur_old_ref_values IS
84 SELECT *
85 FROM IGS_AD_INTERFACE_ALL
86 WHERE rowid = x_rowid;
87
88 BEGIN
89
90 l_rowid := x_rowid;
91
92 -- Code for setting the Old and New Reference Values.
93 -- Populate Old Values.
94 OPEN cur_old_ref_values;
95 FETCH cur_old_ref_values INTO old_references;
96 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
97 CLOSE cur_old_ref_values;
98 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
99 igs_ge_msg_stack.add;
100 app_exception.raise_exception;
101 RETURN;
102 END IF;
103 CLOSE cur_old_ref_values;
104
105 -- Populate New Values.
106 new_references.person_number := x_person_number;
107 new_references.org_id := x_org_id;
108 new_references.interface_id := x_interface_id;
109 new_references.batch_id := x_batch_id;
110 new_references.source_type_id := x_source_type_id;
111 new_references.surname := x_surname;
112 new_references.middle_name := x_middle_name;
113 new_references.given_names := x_given_names;
114 new_references.preferred_given_name := x_preferred_given_name;
115 new_references.sex := x_sex;
116 new_references.birth_dt := x_birth_dt;
117 new_references.title := x_title;
118 new_references.suffix := x_suffix;
119 new_references.pre_name_adjunct := x_pre_name_adjunct;
120 new_references.level_of_qual := x_level_of_qual;
121 new_references.proof_of_insurance := x_proof_of_insurance;
122 new_references.proof_of_immun := x_proof_of_immun;
123 new_references.pref_alternate_id := x_pref_alternate_id;
124 new_references.person_id := x_person_id;
125 new_references.status := x_status;
126 new_references.military_service_reg := x_military_service_reg;
127 new_references.veteran := x_veteran;
128 new_references.match_ind := x_match_ind;
129 new_references.person_match_ind := x_person_match_ind;
130 new_references.error_code := x_error_code;
131 new_references.record_status := x_record_status;
132 new_references.interface_run_id := x_interface_run_id;
133 new_references.attribute_category := x_attribute_category;
134 new_references.attribute1 := x_attribute1;
135 new_references.attribute2 := x_attribute2;
136 new_references.attribute3 := x_attribute3;
137 new_references.attribute4 := x_attribute4;
138 new_references.attribute5 := x_attribute5;
139 new_references.attribute6 := x_attribute6;
140 new_references.attribute7 := x_attribute7;
141 new_references.attribute8 := x_attribute8;
142 new_references.attribute9 := x_attribute9;
143 new_references.attribute10 := x_attribute10;
144 new_references.attribute11 := x_attribute11;
145 new_references.attribute12 := x_attribute12;
146 new_references.attribute13 := x_attribute13;
147 new_references.attribute14 := x_attribute14;
148 new_references.attribute15 := x_attribute15;
149 new_references.attribute16 := x_attribute16;
150 new_references.attribute17 := x_attribute17;
151 new_references.attribute18 := x_attribute18;
152 new_references.attribute19 := x_attribute19;
153 new_references.attribute20 := x_attribute20;
154 new_references.person_type_code := x_person_type_code;
155 new_references.funnel_status := x_funnel_status;
156 new_references.birth_city := x_birth_city;
157 new_references.birth_country := x_birth_country;
158 new_references.attribute21 := x_attribute21;
159 new_references.attribute22 := x_attribute22;
160 new_references.attribute23 := x_attribute23;
161 new_references.attribute24 := x_attribute24;
162
163 IF (p_action = 'UPDATE') THEN
164 new_references.creation_date := old_references.creation_date;
165 new_references.created_by := old_references.created_by;
166 ELSE
167 new_references.creation_date := x_creation_date;
168 new_references.created_by := x_created_by;
169 END IF;
170
171 new_references.last_update_date := x_last_update_date;
172 new_references.last_updated_by := x_last_updated_by;
173 new_references.last_update_login := x_last_update_login;
174
175 END set_column_values;
176
177 PROCEDURE Check_Constraints (
178 Column_Name IN VARCHAR2 DEFAULT NULL,
179 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
180 /*************************************************************
181 Created By : [email protected]
182 Date Created On : 21-Nov-2000
183 Purpose :
184 Know limitations, enhancements or remarks
185 Change History
186 Who When What
187
188 (reverse chronological order - newest change first)
189 ***************************************************************/
190
191 BEGIN
192
193 IF column_name IS NULL THEN
194 NULL;
195 ELSIF UPPER(column_name) = 'STATUS' THEN
196 new_references.status := column_value;
197 ELSIF UPPER(column_name) = 'SEX' THEN
198 new_references.sex := column_value;
199 ELSIF UPPER(column_name) = 'RECORD_STATUS' THEN
200 new_references.record_status := column_value;
201 NULL;
202 END IF;
203
204
205
206 -- The following code checks for check constraints on the Columns.
207 IF Upper(Column_Name) = 'STATUS' OR
208 Column_Name IS NULL THEN
209 IF NOT (new_references.status IN ('1','2','3','4')) THEN
210 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
211 IGS_GE_MSG_STACK.ADD;
212 App_Exception.Raise_Exception;
213 END IF;
214 END IF;
215
216 -- The following code checks for check constraints on the Columns.
217 IF Upper(Column_Name) = 'SEX' OR
218 Column_Name IS NULL THEN
219 IF new_references.sex IS NOT NULL AND NOT (igs_pe_pers_imp_001.validate_lookup_type_code
220 ('HZ_GENDER', UPPER(new_references.sex),222)) THEN
221 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
222 IGS_GE_MSG_STACK.ADD;
223 App_Exception.Raise_Exception;
224 END IF;
225 END IF;
226
227 -- The following code checks for check constraints on the Columns.
228 IF Upper(Column_Name) = 'RECORD_STATUS' OR
229 Column_Name IS NULL THEN
230 IF NOT (new_references.record_status IN ('1', '2', '3')) THEN
231 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
232 IGS_GE_MSG_STACK.ADD;
233 App_Exception.Raise_Exception;
234 END IF;
235 END IF;
236
237
238 END Check_Constraints;
239
240 PROCEDURE Check_Child_Existance IS
241 /*************************************************************
242 Created By : [email protected]
243 Date Created On : 21-Nov-2000
244 Purpose :
245 Know limitations, enhancements or remarks
246 Change History
247 Who When What
248
249 (reverse chronological order - newest change first)
250 ***************************************************************/
251
252 BEGIN
253
254 Igs_Ad_Imp_Near_Mtch_Pkg.Get_FK_Igs_Ad_Interface (
255 old_references.interface_id
256 );
257
258 END Check_Child_Existance;
259
260 FUNCTION Get_PK_For_Validation (
261 x_interface_id IN NUMBER
262 ) RETURN BOOLEAN AS
263
264 /*************************************************************
265 Created By : [email protected]
266 Date Created On : 21-Nov-2000
267 Purpose :
268 Know limitations, enhancements or remarks
269 Change History
270 Who When What
271
272 (reverse chronological order - newest change first)
273 ***************************************************************/
274
275 CURSOR cur_rowid IS
276 SELECT rowid
277 FROM igs_ad_interface_all
278 WHERE interface_id = x_interface_id
279 FOR UPDATE NOWAIT;
280
281 lv_rowid cur_rowid%RowType;
282
283 BEGIN
284
285 Open cur_rowid;
286 Fetch cur_rowid INTO lv_rowid;
287 IF (cur_rowid%FOUND) THEN
288 Close cur_rowid;
289 Return(TRUE);
290 ELSE
291 Close cur_rowid;
292 Return(FALSE);
293 END IF;
294 END Get_PK_For_Validation;
295
296
297 PROCEDURE before_dml (
298 p_action IN VARCHAR2,
299 x_rowid IN VARCHAR2 DEFAULT NULL,
300 x_person_number IN VARCHAR2 DEFAULT NULL,
301 x_org_id IN NUMBER DEFAULT NULL,
302 x_interface_id IN NUMBER DEFAULT NULL,
303 x_batch_id IN NUMBER DEFAULT NULL,
304 x_source_type_id IN NUMBER DEFAULT NULL,
305 x_surname IN VARCHAR2 DEFAULT NULL,
306 x_middle_name IN VARCHAR2 DEFAULT NULL,
307 x_given_names IN VARCHAR2 DEFAULT NULL,
308 x_preferred_given_name IN VARCHAR2 DEFAULT NULL,
309 x_sex IN VARCHAR2 DEFAULT NULL,
310 x_birth_dt IN DATE DEFAULT NULL,
311 x_title IN VARCHAR2 DEFAULT NULL,
312 x_suffix IN VARCHAR2 DEFAULT NULL,
313 x_pre_name_adjunct IN VARCHAR2 DEFAULT NULL,
314 x_level_of_qual IN NUMBER DEFAULT NULL,
315 x_proof_of_insurance IN VARCHAR2 DEFAULT NULL,
316 x_proof_of_immun IN VARCHAR2 DEFAULT NULL,
317 x_pref_alternate_id IN VARCHAR2 DEFAULT NULL,
318 x_person_id IN NUMBER DEFAULT NULL,
319 x_status IN VARCHAR2 DEFAULT NULL,
320 x_military_service_reg IN VARCHAR2 DEFAULT NULL,
321 x_veteran IN VARCHAR2 DEFAULT NULL,
322 x_match_ind IN VARCHAR2 DEFAULT NULL,
323 x_person_match_ind IN VARCHAR2 DEFAULT NULL,
324 x_error_code IN VARCHAR2 DEFAULT NULL,
325 x_record_status IN VARCHAR2 DEFAULT NULL,
326 x_interface_run_id IN NUMBER DEFAULT NULL,
327 x_attribute_category IN VARCHAR2 DEFAULT NULL,
328 x_attribute1 IN VARCHAR2 DEFAULT NULL,
329 x_attribute2 IN VARCHAR2 DEFAULT NULL,
330 x_attribute3 IN VARCHAR2 DEFAULT NULL,
331 x_attribute4 IN VARCHAR2 DEFAULT NULL,
332 x_attribute5 IN VARCHAR2 DEFAULT NULL,
333 x_attribute6 IN VARCHAR2 DEFAULT NULL,
334 x_attribute7 IN VARCHAR2 DEFAULT NULL,
335 x_attribute8 IN VARCHAR2 DEFAULT NULL,
336 x_attribute9 IN VARCHAR2 DEFAULT NULL,
337 x_attribute10 IN VARCHAR2 DEFAULT NULL,
338 x_attribute11 IN VARCHAR2 DEFAULT NULL,
339 x_attribute12 IN VARCHAR2 DEFAULT NULL,
340 x_attribute13 IN VARCHAR2 DEFAULT NULL,
341 x_attribute14 IN VARCHAR2 DEFAULT NULL,
342 x_attribute15 IN VARCHAR2 DEFAULT NULL,
343 x_attribute16 IN VARCHAR2 DEFAULT NULL,
344 x_attribute17 IN VARCHAR2 DEFAULT NULL,
345 x_attribute18 IN VARCHAR2 DEFAULT NULL,
346 x_attribute19 IN VARCHAR2 DEFAULT NULL,
347 x_attribute20 IN VARCHAR2 DEFAULT NULL,
348 x_creation_date IN DATE DEFAULT NULL,
349 x_created_by IN NUMBER DEFAULT NULL,
350 x_last_update_date IN DATE DEFAULT NULL,
351 x_last_updated_by IN NUMBER DEFAULT NULL,
352 x_last_update_login IN NUMBER DEFAULT NULL,
353 x_person_type_code IN VARCHAR2 DEFAULT NULL,
354 x_funnel_status IN VARCHAR2 DEFAULT NULL,
355 x_birth_city IN VARCHAR2 DEFAULT NULL,
356 x_birth_country IN VARCHAR2 DEFAULT NULL,
357 x_attribute21 IN VARCHAR2 DEFAULT NULL,
358 x_attribute22 IN VARCHAR2 DEFAULT NULL,
359 x_attribute23 IN VARCHAR2 DEFAULT NULL,
360 x_attribute24 IN VARCHAR2 DEFAULT NULL
361 ) AS
362 /*
363 || Created By : [email protected]
364 || Created On : 21-NOV-2000
365 || Purpose : Initialises the columns, Checks Constraints, Calls the
366 || Trigger Handlers for the table, before any DML operation.
367 || Known limitations, enhancements or remarks :
368 || Change History :
369 || Who When What
370 || (reverse chronological order - newest change first)
371 */
372 BEGIN
373
374 set_column_values (
375 p_action,
376 x_rowid,
377 x_person_number,
378 x_org_id,
379 x_interface_id,
380 x_batch_id,
381 x_source_type_id,
382 x_surname,
383 x_middle_name,
384 x_given_names,
385 x_preferred_given_name,
386 x_sex,
387 x_birth_dt,
388 x_title,
389 x_suffix,
390 x_pre_name_adjunct,
391 x_level_of_qual,
392 x_proof_of_insurance,
393 x_proof_of_immun,
394 x_pref_alternate_id,
395 x_person_id,
396 x_status,
397 x_military_service_reg,
398 x_veteran,
399 x_match_ind,
400 x_person_match_ind,
401 x_error_code,
402 x_record_status,
403 x_interface_run_id,
404 x_attribute_category,
405 x_attribute1,
406 x_attribute2,
407 x_attribute3,
408 x_attribute4,
409 x_attribute5,
410 x_attribute6,
411 x_attribute7,
412 x_attribute8,
413 x_attribute9,
414 x_attribute10,
415 x_attribute11,
416 x_attribute12,
417 x_attribute13,
418 x_attribute14,
419 x_attribute15,
420 x_attribute16,
421 x_attribute17,
422 x_attribute18,
423 x_attribute19,
424 x_attribute20,
425 x_creation_date,
426 x_created_by,
427 x_last_update_date,
428 x_last_updated_by,
429 x_last_update_login,
430 x_person_type_code,
431 x_funnel_status,
432 x_birth_city,
433 x_birth_country,
434 x_attribute21,
435 x_attribute22,
436 x_attribute23,
437 x_attribute24
438 );
439
440 IF (p_action = 'INSERT') THEN
441 -- Call all the procedures related to Before Insert.
442 IF Get_Pk_For_Validation(
443 new_references.interface_id) THEN
444 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
445 IGS_GE_MSG_STACK.ADD;
446 App_Exception.Raise_Exception;
447 END IF;
448 Check_Constraints;
449 ELSIF (p_action = 'UPDATE') THEN
450 -- Call all the procedures related to Before Update.
451 Check_Constraints;
452 ELSIF (p_action = 'DELETE') THEN
453 -- Call all the procedures related to Before Delete.
454 Check_Child_Existance;
455 ELSIF (p_action = 'VALIDATE_INSERT') THEN
456 -- Call all the procedures related to Before Insert.
457 IF Get_PK_For_Validation (
458 new_references.interface_id) THEN
459 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
460 IGS_GE_MSG_STACK.ADD;
461 App_Exception.Raise_Exception;
462 END IF;
463 Check_Constraints;
464 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
465 Check_Constraints;
466 ELSIF (p_action = 'VALIDATE_DELETE') THEN
467 Check_Child_Existance;
468 END IF;
469
470 END before_dml;
471
472 PROCEDURE insert_row (
473 x_rowid IN OUT NOCOPY VARCHAR2,
474 x_person_number IN VARCHAR2,
475 x_org_id IN NUMBER DEFAULT NULL,
476 x_interface_id IN NUMBER,
477 x_batch_id IN NUMBER,
478 x_source_type_id IN NUMBER,
479 x_surname IN VARCHAR2,
480 x_middle_name IN VARCHAR2,
481 x_given_names IN VARCHAR2,
482 x_preferred_given_name IN VARCHAR2,
483 x_sex IN VARCHAR2,
484 x_birth_dt IN DATE,
485 x_title IN VARCHAR2,
486 x_suffix IN VARCHAR2,
487 x_pre_name_adjunct IN VARCHAR2,
488 x_level_of_qual IN NUMBER,
489 x_proof_of_insurance IN VARCHAR2,
490 x_proof_of_immun IN VARCHAR2,
491 x_pref_alternate_id IN VARCHAR2,
492 x_person_id IN NUMBER,
493 x_status IN VARCHAR2,
494 x_military_service_reg IN VARCHAR2,
495 x_veteran IN VARCHAR2,
496 x_match_ind IN VARCHAR2,
497 x_person_match_ind IN VARCHAR2,
498 x_error_code IN VARCHAR2,
499 x_record_status IN VARCHAR2,
500 x_interface_run_id IN NUMBER,
501 x_attribute_category IN VARCHAR2,
502 x_attribute1 IN VARCHAR2,
503 x_attribute2 IN VARCHAR2,
504 x_attribute3 IN VARCHAR2,
505 x_attribute4 IN VARCHAR2,
506 x_attribute5 IN VARCHAR2,
507 x_attribute6 IN VARCHAR2,
508 x_attribute7 IN VARCHAR2,
509 x_attribute8 IN VARCHAR2,
510 x_attribute9 IN VARCHAR2,
511 x_attribute10 IN VARCHAR2,
512 x_attribute11 IN VARCHAR2,
513 x_attribute12 IN VARCHAR2,
514 x_attribute13 IN VARCHAR2,
515 x_attribute14 IN VARCHAR2,
516 x_attribute15 IN VARCHAR2,
517 x_attribute16 IN VARCHAR2,
518 x_attribute17 IN VARCHAR2,
519 x_attribute18 IN VARCHAR2,
520 x_attribute19 IN VARCHAR2,
521 x_attribute20 IN VARCHAR2,
522 x_mode IN VARCHAR2 DEFAULT NULL,
523 x_person_type_code IN VARCHAR2 DEFAULT NULL,
524 x_funnel_status IN VARCHAR2 DEFAULT NULL,
525 x_birth_city IN VARCHAR2 DEFAULT NULL,
526 x_birth_country IN VARCHAR2 DEFAULT NULL,
527 x_attribute21 IN VARCHAR2,
528 x_attribute22 IN VARCHAR2,
529 x_attribute23 IN VARCHAR2,
530 x_attribute24 IN VARCHAR2
531 ) AS
532 /*
533 || Created By : [email protected]
534 || Created On : 21-NOV-2000
535 || Purpose : Handles the INSERT DML logic for the table.
536 || Known limitations, enhancements or remarks :
537 || Change History :
538 || Who When What
539 || smvk 13-Feb-2002 Call to igs_ge_gen_003.get_org_id
540 || w.r.t. SWCR006
541 || (reverse chronological order - newest change first)
542 */
543 CURSOR c IS
544 SELECT rowid
545 FROM igs_ad_interface_all
546 WHERE interface_id = x_interface_id;
547
548 x_last_update_date DATE;
549 x_last_updated_by NUMBER;
550 x_last_update_login NUMBER;
551 x_request_id NUMBER;
552 x_program_id NUMBER;
553 x_program_application_id NUMBER;
554 x_program_update_date DATE;
555
556 BEGIN
557
558 x_last_update_date := SYSDATE;
559 IF (NVL(x_mode,'R') = 'I') THEN
560 x_last_updated_by := 1;
561 x_last_update_login := 0;
562 ELSIF (NVL(x_mode,'R') = 'R') THEN
563 x_last_updated_by := fnd_global.user_id;
564 IF (x_last_updated_by IS NULL) THEN
565 x_last_updated_by := -1;
566 END IF;
567 x_last_update_login := fnd_global.login_id;
568 IF (x_last_update_login IS NULL) THEN
569 x_last_update_login := -1;
570 END IF;
571 x_request_id := fnd_global.conc_request_id;
572 x_program_id := fnd_global.conc_program_id;
573 x_program_application_id := fnd_global.prog_appl_id;
574
575 IF (x_request_id = -1) THEN
576 x_request_id := NULL;
577 x_program_id := NULL;
578 x_program_application_id := NULL;
579 x_program_update_date := NULL;
580 ELSE
581 x_program_update_date := SYSDATE;
582 END IF;
583 ELSE
584 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
585 igs_ge_msg_stack.add;
586 app_exception.raise_exception;
587 END IF;
588
589 before_dml(
590 p_action => 'INSERT',
591 x_rowid => x_rowid,
592 x_person_number => x_person_number,
593 x_org_id => igs_ge_gen_003.get_org_id,
594 x_interface_id => x_interface_id,
595 x_batch_id => x_batch_id,
596 x_source_type_id => x_source_type_id,
597 x_surname => x_surname,
598 x_middle_name => x_middle_name,
599 x_given_names => x_given_names,
600 x_preferred_given_name => x_preferred_given_name,
601 x_sex => x_sex,
602 x_birth_dt => x_birth_dt,
603 x_title => x_title,
604 x_suffix => x_suffix,
605 x_pre_name_adjunct => x_pre_name_adjunct,
606 x_level_of_qual => x_level_of_qual,
607 x_proof_of_insurance => x_proof_of_insurance,
608 x_proof_of_immun => x_proof_of_immun,
609 x_pref_alternate_id => x_pref_alternate_id,
610 x_person_id => x_person_id,
611 x_status => x_status,
612 x_military_service_reg => x_military_service_reg,
613 x_veteran => x_veteran,
614 x_match_ind => x_match_ind,
615 x_person_match_ind => x_person_match_ind,
616 x_error_code => x_error_code,
617 x_record_status => x_record_status,
618 x_interface_run_id => x_interface_run_id,
619 x_attribute_category => x_attribute_category,
620 x_attribute1 => x_attribute1,
621 x_attribute2 => x_attribute2,
622 x_attribute3 => x_attribute3,
623 x_attribute4 => x_attribute4,
624 x_attribute5 => x_attribute5,
625 x_attribute6 => x_attribute6,
626 x_attribute7 => x_attribute7,
627 x_attribute8 => x_attribute8,
628 x_attribute9 => x_attribute9,
629 x_attribute10 => x_attribute10,
630 x_attribute11 => x_attribute11,
631 x_attribute12 => x_attribute12,
632 x_attribute13 => x_attribute13,
633 x_attribute14 => x_attribute14,
634 x_attribute15 => x_attribute15,
635 x_attribute16 => x_attribute16,
636 x_attribute17 => x_attribute17,
637 x_attribute18 => x_attribute18,
638 x_attribute19 => x_attribute19,
639 x_attribute20 => x_attribute20,
640 x_creation_date => x_last_update_date,
641 x_created_by => x_last_updated_by,
642 x_last_update_date => x_last_update_date,
643 x_last_updated_by => x_last_updated_by,
644 x_last_update_login => x_last_update_login,
645 x_person_type_code => x_person_type_code,
646 x_funnel_status => x_funnel_status,
647 x_birth_city => x_birth_city,
648 x_birth_country => x_birth_country,
649 x_attribute21 => x_attribute21,
650 x_attribute22 => x_attribute22,
651 x_attribute23 => x_attribute23,
652 x_attribute24 => x_attribute24
653 );
654
655 INSERT INTO igs_ad_interface_all (
656 person_number,
657 org_id,
658 interface_id,
659 batch_id,
660 source_type_id,
661 surname,
662 middle_name,
663 given_names,
664 preferred_given_name,
665 sex,
666 birth_dt,
667 title,
668 suffix,
669 pre_name_adjunct,
670 level_of_qual,
671 proof_of_insurance,
672 proof_of_immun,
673 pref_alternate_id,
674 person_id,
675 status,
676 military_service_reg,
677 veteran,
678 match_ind,
679 person_match_ind,
680 error_code,
681 record_status,
682 interface_run_id,
683 attribute_category,
684 attribute1,
685 attribute2,
686 attribute3,
687 attribute4,
688 attribute5,
689 attribute6,
690 attribute7,
691 attribute8,
692 attribute9,
693 attribute10,
694 attribute11,
695 attribute12,
696 attribute13,
697 attribute14,
698 attribute15,
699 attribute16,
700 attribute17,
701 attribute18,
702 attribute19,
703 attribute20,
704 person_type_code,
705 funnel_status,
706 creation_date,
707 created_by,
708 last_update_date,
709 last_updated_by,
710 last_update_login,
711 request_id,
712 program_id,
713 program_application_id,
714 program_update_date,
715 birth_city,
716 birth_country,
717 attribute21,
718 attribute22,
719 attribute23,
720 attribute24
721 ) VALUES (
722 new_references.person_number,
723 new_references.org_id,
724 new_references.interface_id,
725 new_references.batch_id,
726 new_references.source_type_id,
727 new_references.surname,
728 new_references.middle_name,
729 new_references.given_names,
730 new_references.preferred_given_name,
731 new_references.sex,
732 new_references.birth_dt,
733 new_references.title,
734 new_references.suffix,
735 new_references.pre_name_adjunct,
736 new_references.level_of_qual,
737 new_references.proof_of_insurance,
738 new_references.proof_of_immun,
739 new_references.pref_alternate_id,
740 new_references.person_id,
741 new_references.status,
742 new_references.military_service_reg,
743 new_references.veteran,
744 new_references.match_ind,
745 new_references.person_match_ind,
746 new_references.error_code,
747 new_references.record_status,
748 new_references.interface_run_id,
749 new_references.attribute_category,
750 new_references.attribute1,
751 new_references.attribute2,
752 new_references.attribute3,
753 new_references.attribute4,
754 new_references.attribute5,
755 new_references.attribute6,
756 new_references.attribute7,
757 new_references.attribute8,
758 new_references.attribute9,
759 new_references.attribute10,
760 new_references.attribute11,
761 new_references.attribute12,
762 new_references.attribute13,
763 new_references.attribute14,
764 new_references.attribute15,
765 new_references.attribute16,
766 new_references.attribute17,
767 new_references.attribute18,
768 new_references.attribute19,
769 new_references.attribute20,
770 new_references.person_type_code,
771 new_references.funnel_status,
772 x_last_update_date,
773 x_last_updated_by,
774 x_last_update_date,
775 x_last_updated_by,
776 x_last_update_login ,
777 x_request_id,
778 x_program_id,
779 x_program_application_id,
780 x_program_update_date,
781 new_references.birth_city,
782 new_references.birth_country,
783 new_references.attribute21,
784 new_references.attribute22,
785 new_references.attribute23,
786 new_references.attribute24
787 );
788
789 OPEN c;
790 FETCH c INTO x_rowid;
791 IF (c%NOTFOUND) THEN
792 CLOSE c;
793 RAISE NO_DATA_FOUND;
794 END IF;
795 CLOSE c;
796
797 END insert_row;
798
799
800 PROCEDURE lock_row (
801 x_rowid IN VARCHAR2,
802 x_person_number IN VARCHAR2,
803 x_org_id IN NUMBER DEFAULT NULL,
804 x_interface_id IN NUMBER,
805 x_batch_id IN NUMBER,
806 x_source_type_id IN NUMBER,
807 x_surname IN VARCHAR2,
808 x_middle_name IN VARCHAR2,
809 x_given_names IN VARCHAR2,
810 x_preferred_given_name IN VARCHAR2,
811 x_sex IN VARCHAR2,
812 x_birth_dt IN DATE,
813 x_title IN VARCHAR2,
814 x_suffix IN VARCHAR2,
815 x_pre_name_adjunct IN VARCHAR2,
816 x_level_of_qual IN NUMBER,
817 x_proof_of_insurance IN VARCHAR2,
818 x_proof_of_immun IN VARCHAR2,
819 x_pref_alternate_id IN VARCHAR2,
820 x_person_id IN NUMBER,
821 x_status IN VARCHAR2,
822 x_military_service_reg IN VARCHAR2,
823 x_veteran IN VARCHAR2,
824 x_match_ind IN VARCHAR2,
825 x_person_match_ind IN VARCHAR2,
826 x_error_code IN VARCHAR2,
827 x_record_status IN VARCHAR2,
828 x_interface_run_id IN NUMBER,
829 x_attribute_category IN VARCHAR2,
830 x_attribute1 IN VARCHAR2,
831 x_attribute2 IN VARCHAR2,
832 x_attribute3 IN VARCHAR2,
833 x_attribute4 IN VARCHAR2,
834 x_attribute5 IN VARCHAR2,
835 x_attribute6 IN VARCHAR2,
836 x_attribute7 IN VARCHAR2,
837 x_attribute8 IN VARCHAR2,
838 x_attribute9 IN VARCHAR2,
839 x_attribute10 IN VARCHAR2,
840 x_attribute11 IN VARCHAR2,
841 x_attribute12 IN VARCHAR2,
842 x_attribute13 IN VARCHAR2,
843 x_attribute14 IN VARCHAR2,
844 x_attribute15 IN VARCHAR2,
845 x_attribute16 IN VARCHAR2,
846 x_attribute17 IN VARCHAR2,
847 x_attribute18 IN VARCHAR2,
848 x_attribute19 IN VARCHAR2,
849 x_attribute20 IN VARCHAR2,
850 x_person_type_code IN VARCHAR2 DEFAULT NULL,
851 x_funnel_status IN VARCHAR2 DEFAULT NULL,
852 x_birth_city IN VARCHAR2 DEFAULT NULL,
853 x_birth_country IN VARCHAR2 DEFAULT NULL,
854 x_attribute21 IN VARCHAR2,
855 x_attribute22 IN VARCHAR2,
856 x_attribute23 IN VARCHAR2,
857 x_attribute24 IN VARCHAR2
858 ) AS
859 /*
860 || Created By : [email protected]
861 || Created On : 21-NOV-2000
862 || Purpose : Handles the LOCK mechanism for the table.
863 || Known limitations, enhancements or remarks :
864 || Change History :
865 || Who When What
866 || smvk 13-Feb-2002 Removed org_id from cursor declaration
867 || and conditional checking. w.r.t. SWCR006
868 || (reverse chronological order - newest change first)
869 */
870 CURSOR c1 IS
871 SELECT
872 person_number,
873 interface_id,
874 batch_id,
875 source_type_id,
876 surname,
877 middle_name,
878 given_names,
879 preferred_given_name,
880 sex,
881 birth_dt,
882 title,
883 suffix,
884 pre_name_adjunct,
885 level_of_qual,
886 proof_of_insurance,
887 proof_of_immun,
888 pref_alternate_id,
889 person_id,
890 status,
891 military_service_reg,
892 veteran,
893 match_ind,
894 person_match_ind,
895 error_code,
896 record_status,
897 interface_run_id,
898 attribute_category,
899 attribute1,
900 attribute2,
901 attribute3,
902 attribute4,
903 attribute5,
904 attribute6,
905 attribute7,
906 attribute8,
907 attribute9,
908 attribute10,
909 attribute11,
910 attribute12,
911 attribute13,
912 attribute14,
913 attribute15,
914 attribute16,
915 attribute17,
916 attribute18,
917 attribute19,
918 attribute20,
919 person_type_code,
920 funnel_status,
921 birth_city,
922 birth_country,
923 attribute21,
924 attribute22,
925 attribute23,
926 attribute24
927 FROM igs_ad_interface_all
928 WHERE rowid = x_rowid
929 FOR UPDATE NOWAIT;
930
931 tlinfo c1%ROWTYPE;
932
933 BEGIN
934 OPEN c1;
935 FETCH c1 INTO tlinfo;
936 IF (c1%notfound) THEN
937 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
938 igs_ge_msg_stack.add;
939 CLOSE c1;
940 app_exception.raise_exception;
941 RETURN;
942 END IF;
943 CLOSE c1;
944
945 IF (
946 ((tlinfo.person_number = x_person_number) OR ((tlinfo.person_number IS NULL) AND (X_person_number IS NULL)))
947 AND (tlinfo.interface_id = x_interface_id)
948 AND (tlinfo.batch_id = x_batch_id)
949 AND (tlinfo.source_type_id = x_source_type_id)
950 AND (tlinfo.surname = x_surname)
951 AND ((tlinfo.middle_name = x_middle_name) OR ((tlinfo.middle_name IS NULL) AND (X_middle_name IS NULL)))
952 AND (tlinfo.given_names = x_given_names)
953 AND ((tlinfo.preferred_given_name = x_preferred_given_name) OR ((tlinfo.preferred_given_name IS NULL) AND (X_preferred_given_name IS NULL)))
954 AND ((tlinfo.sex = x_sex) OR ((tlinfo.sex IS NULL) AND (X_sex IS NULL)))
955 AND ((tlinfo.birth_dt = x_birth_dt) OR ((tlinfo.birth_dt IS NULL) AND (X_birth_dt IS NULL)))
956 AND ((tlinfo.title = x_title) OR ((tlinfo.title IS NULL) AND (X_title IS NULL)))
957 AND ((tlinfo.suffix = x_suffix) OR ((tlinfo.suffix IS NULL) AND (X_suffix IS NULL)))
958 AND ((tlinfo.pre_name_adjunct = x_pre_name_adjunct) OR ((tlinfo.pre_name_adjunct IS NULL) AND (X_pre_name_adjunct IS NULL)))
959 AND ((tlinfo.level_of_qual = x_level_of_qual) OR ((tlinfo.level_of_qual IS NULL) AND (X_level_of_qual IS NULL)))
960 AND ((tlinfo.proof_of_insurance = x_proof_of_insurance) OR ((tlinfo.proof_of_insurance IS NULL) AND (X_proof_of_insurance IS NULL)))
961 AND ((tlinfo.proof_of_immun = x_proof_of_immun) OR ((tlinfo.proof_of_immun IS NULL) AND (X_proof_of_immun IS NULL)))
962 AND ((tlinfo.pref_alternate_id = x_pref_alternate_id) OR ((tlinfo.pref_alternate_id IS NULL) AND (X_pref_alternate_id IS NULL)))
963 AND ((tlinfo.person_id = x_person_id) OR ((tlinfo.person_id IS NULL) AND (X_person_id IS NULL)))
964 AND (tlinfo.status = x_status)
965 AND ((tlinfo.military_service_reg = x_military_service_reg) OR ((tlinfo.military_service_reg IS NULL) AND (X_military_service_reg IS NULL)))
966 AND ((tlinfo.veteran = x_veteran) OR ((tlinfo.veteran IS NULL) AND (X_veteran IS NULL)))
967 AND ((tlinfo.match_ind = x_match_ind) OR ((tlinfo.match_ind IS NULL) AND (X_match_ind IS NULL)))
968 AND ((tlinfo.person_match_ind = x_person_match_ind) OR ((tlinfo.person_match_ind IS NULL) AND (X_person_match_ind IS NULL)))
969 AND ((tlinfo.error_code = x_error_code) OR ((tlinfo.error_code IS NULL) AND (X_error_code IS NULL)))
970 AND (tlinfo.record_status = x_record_status)
971 AND ((tlinfo.interface_run_id = x_interface_run_id) OR ((tlinfo.interface_run_id IS NULL) AND (X_interface_run_id IS NULL)))
972 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
973 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
974 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
975 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
976 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
977 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
978 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
979 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
980 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
981 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
982 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
983 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
984 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
985 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
986 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
987 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
988 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
989 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
990 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
991 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
992 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
993 AND ((tlinfo.person_type_code = x_person_type_code) OR ((tlinfo.person_type_code IS NULL) AND (X_person_type_code IS NULL)))
994 AND ((tlinfo.funnel_status = x_funnel_status) OR ((tlinfo.funnel_status IS NULL) AND (X_funnel_status IS NULL)))
995 AND ((tlinfo.birth_city = x_birth_city) OR ((tlinfo.birth_city IS NULL) AND (X_birth_city IS NULL)))
996 AND ((tlinfo.birth_country = x_birth_country) OR ((tlinfo.birth_country IS NULL) AND (X_birth_country IS NULL)))
997 AND ((tlinfo.attribute21 = x_attribute21) OR ((tlinfo.attribute21 IS NULL) AND (X_attribute21 IS NULL)))
998 AND ((tlinfo.attribute22 = x_attribute22) OR ((tlinfo.attribute22 IS NULL) AND (X_attribute22 IS NULL)))
999 AND ((tlinfo.attribute23 = x_attribute23) OR ((tlinfo.attribute23 IS NULL) AND (X_attribute23 IS NULL)))
1000 AND ((tlinfo.attribute24 = x_attribute24) OR ((tlinfo.attribute24 IS NULL) AND (X_attribute24 IS NULL)))
1001 ) THEN
1002 NULL;
1003 ELSE
1004 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1005 igs_ge_msg_stack.add;
1006 app_exception.raise_exception;
1007 END IF;
1008
1009 RETURN;
1010
1011 END lock_row;
1012
1013
1014 PROCEDURE update_row (
1015 x_rowid IN VARCHAR2,
1016 x_person_number IN VARCHAR2,
1017 x_org_id IN NUMBER DEFAULT NULL,
1018 x_interface_id IN NUMBER,
1019 x_batch_id IN NUMBER,
1020 x_source_type_id IN NUMBER,
1021 x_surname IN VARCHAR2,
1022 x_middle_name IN VARCHAR2,
1023 x_given_names IN VARCHAR2,
1024 x_preferred_given_name IN VARCHAR2,
1025 x_sex IN VARCHAR2,
1026 x_birth_dt IN DATE,
1027 x_title IN VARCHAR2,
1028 x_suffix IN VARCHAR2,
1029 x_pre_name_adjunct IN VARCHAR2,
1030 x_level_of_qual IN NUMBER,
1031 x_proof_of_insurance IN VARCHAR2,
1032 x_proof_of_immun IN VARCHAR2,
1033 x_pref_alternate_id IN VARCHAR2,
1034 x_person_id IN NUMBER,
1035 x_status IN VARCHAR2,
1036 x_military_service_reg IN VARCHAR2,
1037 x_veteran IN VARCHAR2,
1038 x_match_ind IN VARCHAR2,
1039 x_person_match_ind IN VARCHAR2,
1040 x_error_code IN VARCHAR2,
1041 x_record_status IN VARCHAR2,
1042 x_interface_run_id IN NUMBER,
1043 x_attribute_category IN VARCHAR2,
1044 x_attribute1 IN VARCHAR2,
1045 x_attribute2 IN VARCHAR2,
1046 x_attribute3 IN VARCHAR2,
1047 x_attribute4 IN VARCHAR2,
1048 x_attribute5 IN VARCHAR2,
1049 x_attribute6 IN VARCHAR2,
1050 x_attribute7 IN VARCHAR2,
1051 x_attribute8 IN VARCHAR2,
1052 x_attribute9 IN VARCHAR2,
1053 x_attribute10 IN VARCHAR2,
1054 x_attribute11 IN VARCHAR2,
1055 x_attribute12 IN VARCHAR2,
1056 x_attribute13 IN VARCHAR2,
1057 x_attribute14 IN VARCHAR2,
1058 x_attribute15 IN VARCHAR2,
1059 x_attribute16 IN VARCHAR2,
1060 x_attribute17 IN VARCHAR2,
1061 x_attribute18 IN VARCHAR2,
1062 x_attribute19 IN VARCHAR2,
1063 x_attribute20 IN VARCHAR2,
1064 x_mode IN VARCHAR2 DEFAULT NULL,
1065 x_person_type_code IN VARCHAR2 DEFAULT NULL,
1066 x_funnel_status IN VARCHAR2 DEFAULT NULL,
1067 x_birth_city IN VARCHAR2 DEFAULT NULL,
1068 x_birth_country IN VARCHAR2 DEFAULT NULL,
1069 x_attribute21 IN VARCHAR2,
1070 x_attribute22 IN VARCHAR2,
1071 x_attribute23 IN VARCHAR2,
1072 x_attribute24 IN VARCHAR2
1073 ) AS
1074 /*
1075 || Created By : [email protected]
1076 || Created On : 21-NOV-2000
1077 || Purpose : Handles the UPDATE DML logic for the table.
1078 || Known limitations, enhancements or remarks :
1079 || Change History :
1080 || Who When What
1081 || smvk 13-Feb-2002 Call to igs_ge_gen_003.get_org_id
1082 || w.r.t. SWCR006
1083 || (reverse chronological order - newest change first)
1084 */
1085 x_last_update_date DATE ;
1086 x_last_updated_by NUMBER;
1087 x_last_update_login NUMBER;
1088 x_request_id NUMBER;
1089 x_program_id NUMBER;
1090 x_program_application_id NUMBER;
1091 x_program_update_date DATE;
1092
1093 BEGIN
1094
1095 x_last_update_date := SYSDATE;
1096 IF (NVL(x_mode,'R') = 'I') THEN
1097 x_last_updated_by := 1;
1098 x_last_update_login := 0;
1099 ELSIF (NVL(x_mode,'R') = 'R') THEN
1100 x_last_updated_by := fnd_global.user_id;
1101 IF x_last_updated_by IS NULL THEN
1102 x_last_updated_by := -1;
1103 END IF;
1104 x_last_update_login := fnd_global.login_id;
1105 IF (x_last_update_login IS NULL) THEN
1106 x_last_update_login := -1;
1107 END IF;
1108 ELSE
1109 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1110 igs_ge_msg_stack.add;
1111 app_exception.raise_exception;
1112 END IF;
1113
1114 before_dml(
1115 p_action => 'UPDATE',
1116 x_rowid => x_rowid,
1117 x_person_number => x_person_number,
1118 x_org_id => igs_ge_gen_003.get_org_id,
1119 x_interface_id => x_interface_id,
1120 x_batch_id => x_batch_id,
1121 x_source_type_id => x_source_type_id,
1122 x_surname => x_surname,
1123 x_middle_name => x_middle_name,
1124 x_given_names => x_given_names,
1125 x_preferred_given_name => x_preferred_given_name,
1126 x_sex => x_sex,
1127 x_birth_dt => x_birth_dt,
1128 x_title => x_title,
1129 x_suffix => x_suffix,
1130 x_pre_name_adjunct => x_pre_name_adjunct,
1131 x_level_of_qual => x_level_of_qual,
1132 x_proof_of_insurance => x_proof_of_insurance,
1133 x_proof_of_immun => x_proof_of_immun,
1134 x_pref_alternate_id => x_pref_alternate_id,
1135 x_person_id => x_person_id,
1136 x_status => x_status,
1137 x_military_service_reg => x_military_service_reg,
1138 x_veteran => x_veteran,
1139 x_match_ind => x_match_ind,
1140 x_person_match_ind => x_person_match_ind,
1141 x_error_code => x_error_code,
1142 x_record_status => x_record_status,
1143 x_interface_run_id => x_interface_run_id,
1144 x_attribute_category => x_attribute_category,
1145 x_attribute1 => x_attribute1,
1146 x_attribute2 => x_attribute2,
1147 x_attribute3 => x_attribute3,
1148 x_attribute4 => x_attribute4,
1149 x_attribute5 => x_attribute5,
1150 x_attribute6 => x_attribute6,
1151 x_attribute7 => x_attribute7,
1152 x_attribute8 => x_attribute8,
1153 x_attribute9 => x_attribute9,
1154 x_attribute10 => x_attribute10,
1155 x_attribute11 => x_attribute11,
1156 x_attribute12 => x_attribute12,
1157 x_attribute13 => x_attribute13,
1158 x_attribute14 => x_attribute14,
1159 x_attribute15 => x_attribute15,
1160 x_attribute16 => x_attribute16,
1161 x_attribute17 => x_attribute17,
1162 x_attribute18 => x_attribute18,
1163 x_attribute19 => x_attribute19,
1164 x_attribute20 => x_attribute20,
1165 x_creation_date => x_last_update_date,
1166 x_created_by => x_last_updated_by,
1167 x_last_update_date => x_last_update_date,
1168 x_last_updated_by => x_last_updated_by,
1169 x_last_update_login => x_last_update_login,
1170 x_person_type_code => x_person_type_code,
1171 x_funnel_status => x_funnel_status,
1172 x_birth_city => x_birth_city,
1173 x_birth_country => x_birth_country,
1174 x_attribute21 => x_attribute21,
1175 x_attribute22 => x_attribute22,
1176 x_attribute23 => x_attribute23,
1177 x_attribute24 => x_attribute24
1178 );
1179
1180 IF (NVL(x_mode,'R') = 'R') THEN
1181 x_request_id := fnd_global.conc_request_id;
1182 x_program_id := fnd_global.conc_program_id;
1183 x_program_application_id := fnd_global.prog_appl_id;
1184 IF (x_request_id = -1) THEN
1185 x_request_id := old_references.request_id;
1186 x_program_id := old_references.program_id;
1187 x_program_application_id := old_references.program_application_id;
1188 x_program_update_date := old_references.program_update_date;
1189 ELSE
1190 x_program_update_date := SYSDATE;
1191 END IF;
1192 END IF;
1193
1194 UPDATE igs_ad_interface_all
1195 SET
1196 person_number = new_references.person_number,
1197 interface_id = new_references.interface_id,
1198 batch_id = new_references.batch_id,
1199 source_type_id = new_references.source_type_id,
1200 surname = new_references.surname,
1201 middle_name = new_references.middle_name,
1202 given_names = new_references.given_names,
1203 preferred_given_name = new_references.preferred_given_name,
1204 sex = new_references.sex,
1205 birth_dt = new_references.birth_dt,
1206 title = new_references.title,
1207 suffix = new_references.suffix,
1208 pre_name_adjunct = new_references.pre_name_adjunct,
1209 level_of_qual = new_references.level_of_qual,
1210 proof_of_insurance = new_references.proof_of_insurance,
1211 proof_of_immun = new_references.proof_of_immun,
1212 pref_alternate_id = new_references.pref_alternate_id,
1213 person_id = new_references.person_id,
1214 status = new_references.status,
1215 military_service_reg = new_references.military_service_reg,
1216 veteran = new_references.veteran,
1217 match_ind = new_references.match_ind,
1218 person_match_ind = new_references.person_match_ind,
1219 error_code = new_references.error_code,
1220 record_status = new_references.record_status,
1221 interface_run_id = new_references.interface_run_id,
1222 attribute_category = new_references.attribute_category,
1223 attribute1 = new_references.attribute1,
1224 attribute2 = new_references.attribute2,
1225 attribute3 = new_references.attribute3,
1226 attribute4 = new_references.attribute4,
1227 attribute5 = new_references.attribute5,
1228 attribute6 = new_references.attribute6,
1229 attribute7 = new_references.attribute7,
1230 attribute8 = new_references.attribute8,
1231 attribute9 = new_references.attribute9,
1232 attribute10 = new_references.attribute10,
1233 attribute11 = new_references.attribute11,
1234 attribute12 = new_references.attribute12,
1235 attribute13 = new_references.attribute13,
1236 attribute14 = new_references.attribute14,
1237 attribute15 = new_references.attribute15,
1238 attribute16 = new_references.attribute16,
1239 attribute17 = new_references.attribute17,
1240 attribute18 = new_references.attribute18,
1241 attribute19 = new_references.attribute19,
1242 attribute20 = new_references.attribute20,
1243 person_type_code = new_references.person_type_code,
1244 funnel_status = new_references.funnel_status,
1245 last_update_date = x_last_update_date,
1246 last_updated_by = x_last_updated_by,
1247 last_update_login = x_last_update_login ,
1248 request_id = x_request_id,
1249 program_id = x_program_id,
1250 program_application_id = x_program_application_id,
1251 program_update_date = x_program_update_date,
1252 birth_city = new_references.birth_city,
1253 birth_country = new_references.birth_country,
1254 felony_convicted_flag = old_references.felony_convicted_flag,
1255 attribute21 = new_references.attribute21,
1256 attribute22 = new_references.attribute22,
1257 attribute23 = new_references.attribute23,
1258 attribute24 = new_references.attribute24
1259 WHERE rowid = x_rowid;
1260
1261 IF (SQL%NOTFOUND) THEN
1262 RAISE NO_DATA_FOUND;
1263 END IF;
1264
1265 END update_row;
1266
1267
1268 PROCEDURE add_row (
1269 x_rowid IN OUT NOCOPY VARCHAR2,
1270 x_person_number IN VARCHAR2,
1271 x_org_id IN NUMBER DEFAULT NULL,
1272 x_interface_id IN NUMBER,
1273 x_batch_id IN NUMBER,
1274 x_source_type_id IN NUMBER,
1275 x_surname IN VARCHAR2,
1276 x_middle_name IN VARCHAR2,
1277 x_given_names IN VARCHAR2,
1278 x_preferred_given_name IN VARCHAR2,
1279 x_sex IN VARCHAR2,
1280 x_birth_dt IN DATE,
1281 x_title IN VARCHAR2,
1282 x_suffix IN VARCHAR2,
1283 x_pre_name_adjunct IN VARCHAR2,
1284 x_level_of_qual IN NUMBER,
1285 x_proof_of_insurance IN VARCHAR2,
1286 x_proof_of_immun IN VARCHAR2,
1287 x_pref_alternate_id IN VARCHAR2,
1288 x_person_id IN NUMBER,
1289 x_status IN VARCHAR2,
1290 x_military_service_reg IN VARCHAR2,
1291 x_veteran IN VARCHAR2,
1292 x_match_ind IN VARCHAR2,
1293 x_person_match_ind IN VARCHAR2,
1294 x_error_code IN VARCHAR2,
1295 x_record_status IN VARCHAR2,
1296 x_interface_run_id IN NUMBER,
1297 x_attribute_category IN VARCHAR2,
1298 x_attribute1 IN VARCHAR2,
1299 x_attribute2 IN VARCHAR2,
1300 x_attribute3 IN VARCHAR2,
1301 x_attribute4 IN VARCHAR2,
1302 x_attribute5 IN VARCHAR2,
1303 x_attribute6 IN VARCHAR2,
1304 x_attribute7 IN VARCHAR2,
1305 x_attribute8 IN VARCHAR2,
1306 x_attribute9 IN VARCHAR2,
1307 x_attribute10 IN VARCHAR2,
1308 x_attribute11 IN VARCHAR2,
1309 x_attribute12 IN VARCHAR2,
1310 x_attribute13 IN VARCHAR2,
1311 x_attribute14 IN VARCHAR2,
1312 x_attribute15 IN VARCHAR2,
1313 x_attribute16 IN VARCHAR2,
1314 x_attribute17 IN VARCHAR2,
1315 x_attribute18 IN VARCHAR2,
1316 x_attribute19 IN VARCHAR2,
1317 x_attribute20 IN VARCHAR2,
1318 x_mode IN VARCHAR2 DEFAULT NULL,
1319 x_person_type_code IN VARCHAR2 DEFAULT NULL,
1320 x_funnel_status IN VARCHAR2 DEFAULT NULL,
1321 x_birth_city IN VARCHAR2 DEFAULT NULL,
1322 x_birth_country IN VARCHAR2 DEFAULT NULL,
1323 x_attribute21 IN VARCHAR2,
1324 x_attribute22 IN VARCHAR2,
1325 x_attribute23 IN VARCHAR2,
1326 x_attribute24 IN VARCHAR2
1327 ) AS
1328 /*
1329 || Created By : [email protected]
1330 || Created On : 21-NOV-2000
1331 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1332 || Known limitations, enhancements or remarks :
1333 || Change History :
1334 || Who When What
1335 || (reverse chronological order - newest change first)
1336 */
1337 CURSOR c1 IS
1338 SELECT rowid
1339 FROM igs_ad_interface_all
1340 WHERE interface_id = x_interface_id;
1341
1342 BEGIN
1343
1344 OPEN c1;
1345 FETCH c1 INTO x_rowid;
1346 IF (c1%NOTFOUND) THEN
1347 CLOSE c1;
1348
1349 insert_row (
1350 x_rowid,
1351 x_person_number,
1352 x_org_id,
1353 x_interface_id,
1354 x_batch_id,
1355 x_source_type_id,
1356 x_surname,
1357 x_middle_name,
1358 x_given_names,
1359 x_preferred_given_name,
1360 x_sex,
1361 x_birth_dt,
1362 x_title,
1363 x_suffix,
1364 x_pre_name_adjunct,
1365 x_level_of_qual,
1366 x_proof_of_insurance,
1367 x_proof_of_immun,
1368 x_pref_alternate_id,
1369 x_person_id,
1370 x_status,
1371 x_military_service_reg,
1372 x_veteran,
1373 x_match_ind,
1374 x_person_match_ind,
1375 x_error_code,
1376 x_record_status,
1377 x_interface_run_id,
1378 x_attribute_category,
1379 x_attribute1,
1380 x_attribute2,
1381 x_attribute3,
1382 x_attribute4,
1383 x_attribute5,
1384 x_attribute6,
1385 x_attribute7,
1386 x_attribute8,
1387 x_attribute9,
1388 x_attribute10,
1389 x_attribute11,
1390 x_attribute12,
1391 x_attribute13,
1392 x_attribute14,
1393 x_attribute15,
1394 x_attribute16,
1395 x_attribute17,
1396 x_attribute18,
1397 x_attribute19,
1398 x_attribute20,
1399 NVL(x_mode,'R'),
1400 x_person_type_code,
1401 x_funnel_status,
1402 x_birth_city,
1403 x_birth_country,
1404 x_attribute21,
1405 x_attribute22,
1406 x_attribute23,
1407 x_attribute24
1408 );
1409 RETURN;
1410 END IF;
1411 CLOSE c1;
1412
1413 update_row (
1414 x_rowid,
1415 x_person_number,
1416 x_org_id,
1417 x_interface_id,
1418 x_batch_id,
1419 x_source_type_id,
1420 x_surname,
1421 x_middle_name,
1422 x_given_names,
1423 x_preferred_given_name,
1424 x_sex,
1425 x_birth_dt,
1426 x_title,
1427 x_suffix,
1428 x_pre_name_adjunct,
1429 x_level_of_qual,
1430 x_proof_of_insurance,
1431 x_proof_of_immun,
1432 x_pref_alternate_id,
1433 x_person_id,
1434 x_status,
1435 x_military_service_reg,
1436 x_veteran,
1437 x_match_ind,
1438 x_person_match_ind,
1439 x_error_code,
1440 x_record_status,
1441 x_interface_run_id,
1442 x_attribute_category,
1443 x_attribute1,
1444 x_attribute2,
1445 x_attribute3,
1446 x_attribute4,
1447 x_attribute5,
1448 x_attribute6,
1449 x_attribute7,
1450 x_attribute8,
1451 x_attribute9,
1452 x_attribute10,
1453 x_attribute11,
1454 x_attribute12,
1455 x_attribute13,
1456 x_attribute14,
1457 x_attribute15,
1458 x_attribute16,
1459 x_attribute17,
1460 x_attribute18,
1461 x_attribute19,
1462 x_attribute20,
1463 NVL(x_mode,'R'),
1464 x_person_type_code,
1465 x_funnel_status,
1466 x_birth_city,
1467 x_birth_country,
1468 x_attribute21,
1469 x_attribute22,
1470 x_attribute23,
1471 x_attribute24
1472 );
1473
1474 END add_row;
1475
1476
1477 PROCEDURE delete_row (
1478 x_rowid IN VARCHAR2
1479 ) AS
1480 /*
1481 || Created By : [email protected]
1482 || Created On : 21-NOV-2000
1483 || Purpose : Handles the DELETE DML logic for the table.
1484 || Known limitations, enhancements or remarks :
1485 || Change History :
1486 || Who When What
1487 || (reverse chronological order - newest change first)
1488 */
1489 BEGIN
1490
1491 before_dml (
1492 p_action => 'DELETE',
1493 x_rowid => x_rowid
1494 );
1495
1496 DELETE FROM igs_ad_interface_all
1497 WHERE rowid = x_rowid;
1498
1499 IF (SQL%NOTFOUND) THEN
1500 RAISE NO_DATA_FOUND;
1501 END IF;
1502
1503 END delete_row;
1504
1505
1506 END igs_ad_interface_pkg;