[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_DUP_PAIRS_PKG
Source
1 PACKAGE BODY igs_pe_dup_pairs_pkg AS
2 /* $Header: IGSNI68B.pls 115.9 2002/11/29 01:29:17 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PE_DUP_PAIRS_ALL%RowType;
5 new_references IGS_PE_DUP_PAIRS_ALL%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_duplicate_pair_id IN NUMBER DEFAULT NULL,
11 x_batch_id IN NUMBER DEFAULT NULL,
12 x_match_set_id IN NUMBER DEFAULT NULL,
13 x_actual_person_id IN NUMBER DEFAULT NULL,
14 x_duplicate_person_id IN NUMBER DEFAULT NULL,
15 x_obsolete_id IN NUMBER DEFAULT NULL,
16 x_match_category IN VARCHAR2 DEFAULT NULL,
17 x_dup_status IN VARCHAR2 DEFAULT NULL,
18 x_address_type IN VARCHAR2 DEFAULT NULL,
19 x_location_id IN NUMBER DEFAULT NULL,
20 x_person_id_type IN VARCHAR2 DEFAULT NULL,
21 X_ORG_ID in NUMBER DEFAULT NULL,
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL
27 ) AS
28
29 /*************************************************************
30 Created By :SVISWEAS
31
32 Date Created By :11-MAY-2000
33
34 Purpose :
35
36 Know limitations, enhancements or remarks
37
38 Change History
39
40 Who When What
41
42 (reverse chronological order - newest change first)
43 ***************************************************************/
44
45 CURSOR cur_old_ref_values IS
46 SELECT *
47 FROM IGS_PE_DUP_PAIRS_ALL
48 WHERE rowid = x_rowid;
49
50 BEGIN
51
52 l_rowid := x_rowid;
53
54 -- Code for setting the Old and New Reference Values.
55 -- Populate Old Values.
56 Open cur_old_ref_values;
57 Fetch cur_old_ref_values INTO old_references;
58 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
59 Close cur_old_ref_values;
60 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
61 IGS_GE_MSG_STACK.ADD;
62 App_Exception.Raise_Exception;
63 Return;
64 END IF;
65 Close cur_old_ref_values;
66
67 -- Populate New Values.
68 new_references.duplicate_pair_id := x_duplicate_pair_id;
69 new_references.batch_id := x_batch_id;
70 new_references.match_set_id := x_match_set_id;
71 new_references.actual_person_id := x_actual_person_id;
72 new_references.duplicate_person_id := x_duplicate_person_id;
73 new_references.obsolete_id := x_obsolete_id;
74 new_references.match_category := x_match_category;
75 new_references.dup_status := x_dup_status;
76 new_references.address_type := x_address_type;
77 new_references.location_id := x_location_id;
78 new_references.person_id_type := x_person_id_type;
79 new_references.org_id := x_org_id;
80 IF (p_action = 'UPDATE') THEN
81 new_references.creation_date := old_references.creation_date;
82 new_references.created_by := old_references.created_by;
83 ELSE
84 new_references.creation_date := x_creation_date;
85 new_references.created_by := x_created_by;
86 END IF;
87 new_references.last_update_date := x_last_update_date;
88 new_references.last_updated_by := x_last_updated_by;
89 new_references.last_update_login := x_last_update_login;
90
91 END Set_Column_Values;
92
93 PROCEDURE Check_Constraints (
94 Column_Name IN VARCHAR2 DEFAULT NULL,
95 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
96 /*************************************************************
97 Created By :SVISWEAS
98
99 Date Created By :11-MAY-2000
100
101 Purpose :
102
103 Know limitations, enhancements or remarks
104
105 Change History
106
107 Who When What
108
109 (reverse chronological order - newest change first)
110 ***************************************************************/
111
112 BEGIN
113
114 IF column_name IS NULL THEN
115 NULL;
116 ELSIF UPPER(column_name) = 'MATCH_CATEGORY' THEN
117 new_references.match_category := column_value;
118 NULL;
119 END IF;
120
121
122
123 -- The following code checks for check constraints on the Columns.
124 IF Upper(Column_Name) = 'MATCH_CATEGORY' OR
125 Column_Name IS NULL THEN
126 IF NOT (new_references.match_category IN ('M', 'P')) THEN
127 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
130 END IF;
131 END IF;
132
133
134 END Check_Constraints;
135
136 FUNCTION Get_PK_For_Validation (
137 x_duplicate_pair_id IN NUMBER
138 ) RETURN BOOLEAN AS
139
140 /*************************************************************
141 Created By :SVISWEAS
142
143 Date Created By :11-MAY-2000
144
145 Purpose :
146
147 Know limitations, enhancements or remarks
148
149 Change History
150
151 Who When What
152
153 (reverse chronological order - newest change first)
154 ***************************************************************/
155
156 CURSOR cur_rowid IS
157 SELECT rowid
158 FROM IGS_PE_DUP_PAIRS_ALL
159 WHERE duplicate_pair_id = x_duplicate_pair_id
160 FOR UPDATE NOWAIT;
161
162 lv_rowid cur_rowid%RowType;
163
164 BEGIN
165
166 Open cur_rowid;
167 Fetch cur_rowid INTO lv_rowid;
168 IF (cur_rowid%FOUND) THEN
169 Close cur_rowid;
170 Return(TRUE);
171 ELSE
172 Close cur_rowid;
173 Return(FALSE);
174 END IF;
175 END Get_PK_For_Validation;
176
177 PROCEDURE Before_DML (
178 p_action IN VARCHAR2,
179 x_rowid IN VARCHAR2 DEFAULT NULL,
180 x_duplicate_pair_id IN NUMBER DEFAULT NULL,
181 x_batch_id IN NUMBER DEFAULT NULL,
182 x_match_set_id IN NUMBER DEFAULT NULL,
183 x_actual_person_id IN NUMBER DEFAULT NULL,
184 x_duplicate_person_id IN NUMBER DEFAULT NULL,
185 x_obsolete_id IN NUMBER DEFAULT NULL,
186 x_match_category IN VARCHAR2 DEFAULT NULL,
187 x_dup_status IN VARCHAR2 DEFAULT NULL,
188 x_address_type IN VARCHAR2 DEFAULT NULL,
189 x_location_id IN NUMBER DEFAULT NULL,
190 x_person_id_type IN VARCHAR2 DEFAULT NULL,
191 X_ORG_ID in NUMBER DEFAULT NULL,
192 x_creation_date IN DATE DEFAULT NULL,
193 x_created_by IN NUMBER DEFAULT NULL,
194 x_last_update_date IN DATE DEFAULT NULL,
195 x_last_updated_by IN NUMBER DEFAULT NULL,
196 x_last_update_login IN NUMBER DEFAULT NULL
197 ) AS
198 /*************************************************************
199 Created By :SVISWEAS
200
201 Date Created By :11-MAY-2000
202
203 Purpose :
204
205 Know limitations, enhancements or remarks
206
207 Change History
208
209 Who When What
210
211 (reverse chronological order - newest change first)
212 ***************************************************************/
213
214 BEGIN
215
216 Set_Column_Values (
217 p_action,
218 x_rowid,
219 x_duplicate_pair_id,
220 x_batch_id,
221 x_match_set_id,
222 x_actual_person_id,
223 x_duplicate_person_id,
224 x_obsolete_id,
225 x_match_category,
226 x_dup_status,
227 x_address_type,
228 x_location_id,
229 x_person_id_type,
230 x_org_id,
231 x_creation_date,
232 x_created_by,
233 x_last_update_date,
234 x_last_updated_by,
235 x_last_update_login
236 );
237
238 IF (p_action = 'INSERT') THEN
239 -- Call all the procedures related to Before Insert.
240 Null;
241 IF Get_Pk_For_Validation(
242 new_references.duplicate_pair_id) THEN
243 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
244 IGS_GE_MSG_STACK.ADD;
245 App_Exception.Raise_Exception;
246 END IF;
247 Check_Constraints;
248 ELSIF (p_action = 'UPDATE') THEN
249 -- Call all the procedures related to Before Update.
250 Null;
251 Check_Constraints;
252 ELSIF (p_action = 'DELETE') THEN
253 -- Call all the procedures related to Before Delete.
254 Null;
255 ELSIF (p_action = 'VALIDATE_INSERT') THEN
256 -- Call all the procedures related to Before Insert.
257 IF Get_PK_For_Validation (
258 new_references.duplicate_pair_id) THEN
259 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
260 IGS_GE_MSG_STACK.ADD;
261 App_Exception.Raise_Exception;
262 END IF;
263 Check_Constraints;
264 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
265 Check_Constraints;
266 ELSIF (p_action = 'VALIDATE_DELETE') THEN
267 Null;
268 END IF;
269
270 END Before_DML;
271
272 PROCEDURE After_DML (
273 p_action IN VARCHAR2,
274 x_rowid IN VARCHAR2
275 ) IS
276 /*************************************************************
277 Created By :SVISWEAS
278
279 Date Created By :11-MAY-2000
280
281 Purpose :
282
283 Know limitations, enhancements or remarks
284
285 Change History
286
287 Who When What
288
289 (reverse chronological order - newest change first)
290 ***************************************************************/
291
292 BEGIN
293
294 l_rowid := x_rowid;
295
296 IF (p_action = 'INSERT') THEN
297 -- Call all the procedures related to After Insert.
298 Null;
299 ELSIF (p_action = 'UPDATE') THEN
300 -- Call all the procedures related to After Update.
301 Null;
302 ELSIF (p_action = 'DELETE') THEN
303 -- Call all the procedures related to After Delete.
304 Null;
305 END IF;
306
307 END After_DML;
308
309 procedure INSERT_ROW (
310 X_ROWID in out NOCOPY VARCHAR2,
311 x_DUPLICATE_PAIR_ID IN OUT NOCOPY NUMBER,
312 x_BATCH_ID IN NUMBER,
313 x_MATCH_SET_ID IN NUMBER,
314 x_ACTUAL_PERSON_ID IN NUMBER,
315 x_DUPLICATE_PERSON_ID IN NUMBER,
316 x_OBSOLETE_ID IN NUMBER,
317 x_MATCH_CATEGORY IN VARCHAR2,
318 x_DUP_STATUS IN VARCHAR2,
319 x_ADDRESS_TYPE IN VARCHAR2,
320 x_LOCATION_ID IN NUMBER,
321 x_PERSON_ID_TYPE IN VARCHAR2,
322 X_ORG_ID in NUMBER,
323 X_MODE in VARCHAR2 default 'R'
324 ) AS
325 /*************************************************************
326 Created By :SVISWEAS
327
328 Date Created By :11-MAY-2000
329
330 Purpose :
331
332 Know limitations, enhancements or remarks
333
334 Change History
335
336 Who When What
337
338 (reverse chronological order - newest change first)
339 ***************************************************************/
340
341 cursor C is select ROWID from IGS_PE_DUP_PAIRS_ALL
342 where DUPLICATE_PAIR_ID= X_DUPLICATE_PAIR_ID
343 ;
344 X_LAST_UPDATE_DATE DATE ;
345 X_LAST_UPDATED_BY NUMBER ;
346 X_LAST_UPDATE_LOGIN NUMBER ;
347 X_REQUEST_ID NUMBER;
348 X_PROGRAM_ID NUMBER;
349 X_PROGRAM_APPLICATION_ID NUMBER;
350 X_PROGRAM_UPDATE_DATE DATE;
351 begin
352 X_LAST_UPDATE_DATE := SYSDATE;
353 if (X_MODE = 'I') then
354 X_LAST_UPDATED_BY := 1;
355 X_LAST_UPDATE_LOGIN := 0;
356 elsif (X_MODE = 'R') then
357 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
358 if X_LAST_UPDATED_BY is NULL then
359 X_LAST_UPDATED_BY := -1;
360 end if;
361 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
362 if X_LAST_UPDATE_LOGIN is NULL then
363 X_LAST_UPDATE_LOGIN := -1;
364 end if;
365 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
366 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
367 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
368 if (X_REQUEST_ID = -1) then
369 X_REQUEST_ID := NULL;
370 X_PROGRAM_ID := NULL;
371 X_PROGRAM_APPLICATION_ID := NULL;
372 X_PROGRAM_UPDATE_DATE := NULL;
373 else
374 X_PROGRAM_UPDATE_DATE := SYSDATE;
375 end if;
376 else
377 FND_MESSAGE.SET_NAME('FND','SYSTEM-INVALID ARGS');
378 IGS_GE_MSG_STACK.ADD;
379 app_exception.raise_exception;
380 end if;
381
382 SELECT IGS_PE_DUP_PAIRS_S.NEXTVAL
383 INTO X_DUPLICATE_PAIR_ID
384 FROM DUAL;
385
386 Before_DML(
387 p_action=>'INSERT',
388 x_rowid=>X_ROWID,
389 x_duplicate_pair_id=>X_DUPLICATE_PAIR_ID,
390 x_batch_id=>X_BATCH_ID,
391 x_match_set_id=>X_MATCH_SET_ID,
392 x_actual_person_id=>X_ACTUAL_PERSON_ID,
393 x_duplicate_person_id=>X_DUPLICATE_PERSON_ID,
394 x_obsolete_id=>X_OBSOLETE_ID,
395 x_match_category=>X_MATCH_CATEGORY,
396 x_dup_status=>X_DUP_STATUS,
397 x_address_type=>X_ADDRESS_TYPE,
398 x_location_id=>X_LOCATION_ID,
399 x_person_id_type=>X_PERSON_ID_TYPE,
400 x_org_id => igs_ge_gen_003.get_org_id,
401 x_creation_date=>X_LAST_UPDATE_DATE,
402 x_created_by=>X_LAST_UPDATED_BY,
403 x_last_update_date=>X_LAST_UPDATE_DATE,
404 x_last_updated_by=>X_LAST_UPDATED_BY,
405 x_last_update_login=>X_LAST_UPDATE_LOGIN);
406 insert into IGS_PE_DUP_PAIRS_ALL (
407 DUPLICATE_PAIR_ID
408 ,BATCH_ID
409 ,MATCH_SET_ID
410 ,ACTUAL_PERSON_ID
411 ,DUPLICATE_PERSON_ID
412 ,OBSOLETE_ID
413 ,MATCH_CATEGORY
414 ,DUP_STATUS
415 ,ADDRESS_TYPE
416 ,LOCATION_ID
417 ,PERSON_ID_TYPE
418 ,ORG_ID
419 ,CREATION_DATE
420 ,CREATED_BY
421 ,LAST_UPDATE_DATE
422 ,LAST_UPDATED_BY
423 ,LAST_UPDATE_LOGIN
424 ,REQUEST_ID
425 ,PROGRAM_ID
426 ,PROGRAM_APPLICATION_ID
427 ,PROGRAM_UPDATE_DATE
428 ) values (
429 NEW_REFERENCES.DUPLICATE_PAIR_ID
430 ,NEW_REFERENCES.BATCH_ID
431 ,NEW_REFERENCES.MATCH_SET_ID
432 ,NEW_REFERENCES.ACTUAL_PERSON_ID
433 ,NEW_REFERENCES.DUPLICATE_PERSON_ID
434 ,NEW_REFERENCES.OBSOLETE_ID
435 ,NEW_REFERENCES.MATCH_CATEGORY
436 ,NEW_REFERENCES.DUP_STATUS
437 ,NEW_REFERENCES.ADDRESS_TYPE
438 ,NEW_REFERENCES.LOCATION_ID
439 ,NEW_REFERENCES.PERSON_ID_TYPE
440 ,NEW_REFERENCES.ORG_ID
441 ,X_LAST_UPDATE_DATE
442 ,X_LAST_UPDATED_BY
443 ,X_LAST_UPDATE_DATE
444 ,X_LAST_UPDATED_BY
445 ,X_LAST_UPDATE_LOGIN
446 ,X_REQUEST_ID
447 ,X_PROGRAM_ID
448 ,X_PROGRAM_APPLICATION_ID
449 ,X_PROGRAM_UPDATE_DATE
450 );
451 open c;
452 fetch c into X_ROWID;
453 if (c%notfound) then
454 close c;
455 raise no_data_found;
456 end if;
457 close c;
458 After_DML (
459 p_action => 'INSERT' ,
460 x_rowid => X_ROWID );
461 end INSERT_ROW;
462
463 procedure LOCK_ROW (
464 X_ROWID in VARCHAR2,
465 x_DUPLICATE_PAIR_ID IN NUMBER,
466 x_BATCH_ID IN NUMBER,
467 x_MATCH_SET_ID IN NUMBER,
468 x_ACTUAL_PERSON_ID IN NUMBER,
469 x_DUPLICATE_PERSON_ID IN NUMBER,
470 x_OBSOLETE_ID IN NUMBER,
471 x_MATCH_CATEGORY IN VARCHAR2,
472 x_DUP_STATUS IN VARCHAR2,
473 x_ADDRESS_TYPE IN VARCHAR2,
474 x_LOCATION_ID IN NUMBER,
475 x_PERSON_ID_TYPE IN VARCHAR2
476 ) AS
477 /*************************************************************
478 Created By :SVISWEAS
479
480 Date Created By :11-MAY-2000
481
482 Purpose :
483
484 Know limitations, enhancements or remarks
485
486 Change History
487
488 Who When What
489
490 (reverse chronological order - newest change first)
491 ***************************************************************/
492
493 cursor c1 is select
494 BATCH_ID
495 , MATCH_SET_ID
496 , ACTUAL_PERSON_ID
497 , DUPLICATE_PERSON_ID
498 , OBSOLETE_ID
499 , MATCH_CATEGORY
500 , DUP_STATUS
501 , ADDRESS_TYPE
502 , LOCATION_ID
503 , PERSON_ID_TYPE
504 from IGS_PE_DUP_PAIRS_ALL
505 where ROWID = X_ROWID
506 for update nowait;
507 tlinfo c1%rowtype;
508 begin
509 open c1;
510 fetch c1 into tlinfo;
511 if (c1%notfound) then
512 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
513 IGS_GE_MSG_STACK.ADD;
514 close c1;
515 app_exception.raise_exception;
516 return;
517 end if;
518 close c1;
519 if (tlinfo.BATCH_ID = X_BATCH_ID)
520 AND (tlinfo.MATCH_SET_ID = X_MATCH_SET_ID)
521 AND (tlinfo.ACTUAL_PERSON_ID = X_ACTUAL_PERSON_ID)
522 AND (tlinfo.DUPLICATE_PERSON_ID = X_DUPLICATE_PERSON_ID)
523 AND (tlinfo.OBSOLETE_ID = X_OBSOLETE_ID)
524 OR (tlinfo.OBSOLETE_ID is null)
525 AND (X_OBSOLETE_ID is null)
526 AND (tlinfo.MATCH_CATEGORY = X_MATCH_CATEGORY)
527 AND (tlinfo.DUP_STATUS = X_DUP_STATUS)
528 AND (tlinfo.ADDRESS_TYPE = X_ADDRESS_TYPE)
529 OR (tlinfo.ADDRESS_TYPE is null)
530 AND (X_ADDRESS_TYPE is null)
531 AND (tlinfo.LOCATION_ID = X_LOCATION_ID)
532 OR (tlinfo.LOCATION_ID is null)
533 AND (X_LOCATION_ID is null)
534 AND (tlinfo.PERSON_ID_TYPE = X_PERSON_ID_TYPE)
535 OR (tlinfo.PERSON_ID_TYPE is null)
536 AND (X_PERSON_ID_TYPE is null)
537 then
538 null;
539 else
540 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
541 IGS_GE_MSG_STACK.ADD;
542 app_exception.raise_exception;
543 end if;
544 return;
545 end LOCK_ROW;
546
547 Procedure UPDATE_ROW (
548 X_ROWID in VARCHAR2,
549 x_DUPLICATE_PAIR_ID IN NUMBER,
550 x_BATCH_ID IN NUMBER,
551 x_MATCH_SET_ID IN NUMBER,
552 x_ACTUAL_PERSON_ID IN NUMBER,
553 x_DUPLICATE_PERSON_ID IN NUMBER,
554 x_OBSOLETE_ID IN NUMBER,
555 x_MATCH_CATEGORY IN VARCHAR2,
556 x_DUP_STATUS IN VARCHAR2,
557 x_ADDRESS_TYPE IN VARCHAR2,
558 x_LOCATION_ID IN NUMBER,
559 x_PERSON_ID_TYPE IN VARCHAR2,
560 X_MODE in VARCHAR2 default 'R'
561 ) AS
562 /*************************************************************
563 Created By :SVISWEAS
564
565 Date Created By :11-MAY-2000
566
567 Purpose :
568
569 Know limitations, enhancements or remarks
570
571 Change History
572
573 Who When What
574
575 (reverse chronological order - newest change first)
576 ***************************************************************/
577
578 X_LAST_UPDATE_DATE DATE ;
579 X_LAST_UPDATED_BY NUMBER ;
580 X_LAST_UPDATE_LOGIN NUMBER ;
581 X_REQUEST_ID NUMBER;
582 X_PROGRAM_ID NUMBER;
583 X_PROGRAM_APPLICATION_ID NUMBER;
584 X_PROGRAM_UPDATE_DATE DATE;
585 begin
586 X_LAST_UPDATE_DATE := SYSDATE;
587 if(X_MODE = 'I') then
588 X_LAST_UPDATED_BY := 1;
589 X_LAST_UPDATE_LOGIN := 0;
590 elsif (X_MODE = 'R') then
591 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
592 if X_LAST_UPDATED_BY is NULL then
593 X_LAST_UPDATED_BY := -1;
594 end if;
595 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
596 if X_LAST_UPDATE_LOGIN is NULL then
597 X_LAST_UPDATE_LOGIN := -1;
598 end if;
599 else
600 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
601 IGS_GE_MSG_STACK.ADD;
602 app_exception.raise_exception;
603 end if;
604 Before_DML(
605 p_action=>'UPDATE',
606 x_rowid=>X_ROWID,
607 x_duplicate_pair_id=>X_DUPLICATE_PAIR_ID,
608 x_batch_id=>X_BATCH_ID,
609 x_match_set_id=>X_MATCH_SET_ID,
610 x_actual_person_id=>X_ACTUAL_PERSON_ID,
611 x_duplicate_person_id=>X_DUPLICATE_PERSON_ID,
612 x_obsolete_id=>X_OBSOLETE_ID,
613 x_match_category=>X_MATCH_CATEGORY,
614 x_dup_status=>X_DUP_STATUS,
615 x_address_type=>X_ADDRESS_TYPE,
616 x_location_id=>X_LOCATION_ID,
617 x_person_id_type=>X_PERSON_ID_TYPE,
618 x_creation_date=>X_LAST_UPDATE_DATE,
619 x_created_by=>X_LAST_UPDATED_BY,
620 x_last_update_date=>X_LAST_UPDATE_DATE,
621 x_last_updated_by=>X_LAST_UPDATED_BY,
622 x_last_update_login=>X_LAST_UPDATE_LOGIN);
623
624 if (X_MODE = 'R') then
625 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
626 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
627 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
628 if (X_REQUEST_ID = -1) then
629 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
630 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
631 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
632 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
633 else
634 X_PROGRAM_UPDATE_DATE := SYSDATE;
635 end if;
636 end if;
637
638
639 update IGS_PE_DUP_PAIRS_ALL set
640 BATCH_ID = NEW_REFERENCES.BATCH_ID,
641 MATCH_SET_ID = NEW_REFERENCES.MATCH_SET_ID,
642 ACTUAL_PERSON_ID = NEW_REFERENCES.ACTUAL_PERSON_ID,
643 DUPLICATE_PERSON_ID = NEW_REFERENCES.DUPLICATE_PERSON_ID,
644 OBSOLETE_ID = NEW_REFERENCES.OBSOLETE_ID,
645 MATCH_CATEGORY = NEW_REFERENCES.MATCH_CATEGORY,
646 DUP_STATUS = NEW_REFERENCES.DUP_STATUS,
647 ADDRESS_TYPE = NEW_REFERENCES.ADDRESS_TYPE,
648 LOCATION_ID = NEW_REFERENCES.LOCATION_ID,
649 PERSON_ID_TYPE = NEW_REFERENCES.PERSON_ID_TYPE,
650 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
651 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
652 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
653 REQUEST_ID = X_REQUEST_ID,
654 PROGRAM_ID = X_PROGRAM_ID,
655 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
656 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
657 where ROWID = X_ROWID;
658 if (sql%notfound) then
659 raise no_data_found;
660 end if;
661
662 After_DML (
663 p_action => 'UPDATE' ,
664 x_rowid => X_ROWID
665 );
666 end UPDATE_ROW;
667
668 procedure ADD_ROW (
669 X_ROWID in out NOCOPY VARCHAR2,
670 x_DUPLICATE_PAIR_ID IN OUT NOCOPY NUMBER,
671 x_BATCH_ID IN NUMBER,
672 x_MATCH_SET_ID IN NUMBER,
673 x_ACTUAL_PERSON_ID IN NUMBER,
674 x_DUPLICATE_PERSON_ID IN NUMBER,
675 x_OBSOLETE_ID IN NUMBER,
676 x_MATCH_CATEGORY IN VARCHAR2,
677 x_DUP_STATUS IN VARCHAR2,
678 x_ADDRESS_TYPE IN VARCHAR2,
679 x_LOCATION_ID IN NUMBER,
680 x_PERSON_ID_TYPE IN VARCHAR2,
681 X_ORG_ID in NUMBER,
682 X_MODE in VARCHAR2 default 'R'
683 ) AS
684 /*************************************************************
685 Created By :SVISWEAS
686
687 Date Created By :11-MAY-2000
688
689 Purpose :
690
691 Know limitations, enhancements or remarks
692
693 Change History
694
695 Who When What
696
697 (reverse chronological order - newest change first)
698 ***************************************************************/
699
700 cursor c1 is select ROWID from IGS_PE_DUP_PAIRS_ALL
701 where DUPLICATE_PAIR_ID= X_DUPLICATE_PAIR_ID
702 ;
703 begin
704 open c1;
705 fetch c1 into X_ROWID;
706 if (c1%notfound) then
707 close c1;
708 INSERT_ROW (
709 X_ROWID,
710 X_DUPLICATE_PAIR_ID,
711 X_BATCH_ID,
712 X_MATCH_SET_ID,
713 X_ACTUAL_PERSON_ID,
714 X_DUPLICATE_PERSON_ID,
715 X_OBSOLETE_ID,
716 X_MATCH_CATEGORY,
717 X_DUP_STATUS,
718 X_ADDRESS_TYPE,
719 X_LOCATION_ID,
720 X_PERSON_ID_TYPE,
721 X_ORG_ID,
722 X_MODE );
723 return;
724 end if;
725 close c1;
726 UPDATE_ROW (
727 X_ROWID,
728 X_DUPLICATE_PAIR_ID,
729 X_BATCH_ID,
730 X_MATCH_SET_ID,
731 X_ACTUAL_PERSON_ID,
732 X_DUPLICATE_PERSON_ID,
733 X_OBSOLETE_ID,
734 X_MATCH_CATEGORY,
735 X_DUP_STATUS,
736 X_ADDRESS_TYPE,
737 X_LOCATION_ID,
738 X_PERSON_ID_TYPE,
739 X_MODE );
740 end ADD_ROW;
741 procedure DELETE_ROW (
742 X_ROWID in VARCHAR2
743 ) AS
744 /*************************************************************
745 Created By :SVISWEAS
746
747 Date Created By :11-MAY-2000
748
749 Purpose :
750
751 Know limitations, enhancements or remarks
752
753 Change History
754
755 Who When What
756
757 (reverse chronological order - newest change first)
758 ***************************************************************/
759
760 begin
761 Before_DML (
762 p_action => 'DELETE',
763 x_rowid => X_ROWID
764 );
765 delete from IGS_PE_DUP_PAIRS_ALL
766 where ROWID = X_ROWID;
767 if (sql%notfound) then
768 raise no_data_found;
769 end if;
770 After_DML (
771 p_action => 'DELETE',
772 x_rowid => X_ROWID
773 );
774 end DELETE_ROW;
775 END igs_pe_dup_pairs_pkg;