1 PACKAGE BODY igs_ad_imp_near_mtch_pkg AS
2 /* $Header: IGSAIB2B.pls 115.19 2003/05/22 13:17:56 npalanis ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ad_imp_near_mtch_all%RowType;
5 new_references igs_ad_imp_near_mtch_all%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_org_id IN NUMBER DEFAULT NULL,
11 x_near_mtch_id IN NUMBER DEFAULT NULL,
12 x_interface_id IN NUMBER DEFAULT NULL,
13 x_person_id IN NUMBER DEFAULT NULL,
14 x_match_ind IN VARCHAR2 DEFAULT NULL,
15 x_action IN VARCHAR2 DEFAULT NULL,
16 x_addr_type IN VARCHAR2 DEFAULT NULL,
17 x_person_id_type IN VARCHAR2 DEFAULT NULL,
18 x_match_set_id IN NUMBER DEFAULT NULL,
19 x_creation_date IN DATE DEFAULT NULL,
20 x_created_by IN NUMBER DEFAULT NULL,
21 x_last_update_date IN DATE DEFAULT NULL,
22 x_last_updated_by IN NUMBER DEFAULT NULL,
23 x_last_update_login IN NUMBER DEFAULT NULL,
24 x_party_site_id IN NUMBER DEFAULT NULL ,
25 X_INTERFACE_RELATIONS_ID IN NUMBER DEFAULT NULL
26 ) AS
27
28 /*************************************************************
29 Created By : amuthu
30 Date Created On : 15-May-2000
31 Purpose :
32 Know limitations, enhancements or remarks
33 Change History
34 Who When What
35
36 (reverse chronological order - newest change first)
37 ***************************************************************/
38
39 CURSOR cur_old_ref_values IS
40 SELECT *
41 FROM IGS_AD_IMP_NEAR_MTCH_ALL
42 WHERE rowid = x_rowid;
43
44 BEGIN
45
46 l_rowid := x_rowid;
47
48 -- Code for setting the Old and New Reference Values.
49 -- Populate Old Values.
50 Open cur_old_ref_values;
51 Fetch cur_old_ref_values INTO old_references;
52 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
53 Close cur_old_ref_values;
54 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
55 IGS_GE_MSG_STACK.ADD;
56 App_Exception.Raise_Exception;
57 Return;
58 END IF;
59 Close cur_old_ref_values;
60
61 -- Populate New Values.
62 new_references.near_mtch_id := x_near_mtch_id;
63 new_references.org_id := x_org_id;
64 new_references.interface_id := x_interface_id;
65 new_references.person_id := x_person_id;
66 new_references.match_ind := x_match_ind;
67 new_references.action := x_action;
68 new_references.addr_type := x_addr_type;
69 new_references.person_id_type := x_person_id_type;
70 new_references.match_set_id := x_match_set_id;
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78 new_references.last_update_date := x_last_update_date;
79 new_references.last_updated_by := x_last_updated_by;
80 new_references.last_update_login := x_last_update_login;
81 new_references.party_site_id := x_party_site_id;
82 new_references.interface_relations_id := x_interface_relations_id;
83
84 END Set_Column_Values;
85
86 PROCEDURE Check_Constraints (
87 Column_Name IN VARCHAR2 DEFAULT NULL,
88 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
89 /*************************************************************
90 Created By : amuthu
91 Date Created On : 15-May-2000
92 Purpose :
93 Know limitations, enhancements or remarks
94 Change History
95 Who When What
96
97 (reverse chronological order - newest change first)
98 ***************************************************************/
99
100 BEGIN
101
102 IF column_name IS NULL THEN
103 NULL;
104 ELSIF UPPER(column_name) = 'MATCH_IND' THEN
105 new_references.match_ind := column_value;
106 ELSIF UPPER(column_name) = 'ACTION' THEN
107 new_references.action := column_value;
108 END IF;
109
110
111
112 -- The following code checks for check constraints on the Columns.
113 IF Upper(Column_Name) = 'ACTION' OR
114 Column_Name IS NULL THEN
115 IF NOT (new_references.action IN ('I','D')) THEN
116 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
117 IGS_GE_MSG_STACK.ADD;
118 App_Exception.Raise_Exception;
119 END IF;
120 END IF;
121
122
123 END Check_Constraints;
124
125 PROCEDURE Check_Parent_Existance AS
126 /*************************************************************
127 Created By : amuthu
128 Date Created On : 15-May-2000
129 Purpose :
130 Know limitations, enhancements or remarks
131 Change History
132 Who When What
133
134 (reverse chronological order - newest change first)
135 ***************************************************************/
136
137 BEGIN
138
139 IF (((old_references.interface_id = new_references.interface_id)) OR
140 ((new_references.interface_id IS NULL))) THEN
141 NULL;
142 ELSIF NOT Igs_Ad_Interface_Pkg.Get_PK_For_Validation (
143 new_references.interface_id
144 ) THEN
145 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148 END IF;
149
150 IF (((old_references.person_id_type = new_references.person_id_type)) OR
151 ((new_references.person_id_type IS NULL))) THEN
152 NULL;
153 ELSIF NOT Igs_Pe_Person_Id_Typ_Pkg.Get_PK_For_Validation (
154 new_references.person_id_type
155 ) THEN
156 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END IF;
160
161 /* IF (((old_references.addr_type = new_references.addr_type)) OR
162 ((new_references.addr_type IS NULL))) THEN
163 NULL;
164 ELSIF NOT Igs_Co_Addr_Type_Pkg.Get_PK_For_Validation (
165 new_references.addr_type
166 ) THEN
167 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 END IF;
171 */
172 IF (((old_references.match_set_id = new_references.match_set_id)) OR
173 ((new_references.match_set_id IS NULL))) THEN
174 NULL;
175 ELSIF NOT Igs_Pe_Match_Sets_Pkg.Get_PK_For_Validation (
176 new_references.match_set_id
177 ) THEN
178 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
179 IGS_GE_MSG_STACK.ADD;
180 App_Exception.Raise_Exception;
181 END IF;
182
183 IF (((old_references.person_id = new_references.person_id)) OR
184 ((new_references.person_id IS NULL))) THEN
185 NULL;
186 ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
187 new_references.person_id
188 ) THEN
189 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
190 IGS_GE_MSG_STACK.ADD;
191 App_Exception.Raise_Exception;
192 END IF;
193
194 END Check_Parent_Existance;
195
196 FUNCTION Get_PK_For_Validation (
197 x_near_mtch_id IN NUMBER
198 ) RETURN BOOLEAN AS
199
200 /*************************************************************
201 Created By : amuthu
202 Date Created On : 15-May-2000
203 Purpose :
204 Know limitations, enhancements or remarks
205 Change History
206 Who When What
207
208 (reverse chronological order - newest change first)
209 ***************************************************************/
210
211 CURSOR cur_rowid IS
212 SELECT rowid
213 FROM igs_ad_imp_near_mtch_all
214 WHERE near_mtch_id = x_near_mtch_id
215 FOR UPDATE NOWAIT;
216
217 lv_rowid cur_rowid%RowType;
218
219 BEGIN
220 Open cur_rowid;
221 Fetch cur_rowid INTO lv_rowid;
222 IF (cur_rowid%FOUND) THEN
223 Close cur_rowid;
224 Return(TRUE);
225 ELSE
226 Close cur_rowid;
227 Return(FALSE);
228 END IF;
229 END Get_PK_For_Validation;
230
231
232 PROCEDURE Get_FK_Igs_Ad_Interface (
233 x_interface_id IN NUMBER
234 ) AS
235
236 /*************************************************************
237 Created By : amuthu
238 Date Created On : 15-May-2000
239 Purpose :
240 Know limitations, enhancements or remarks
241 Change History
242 Who When What
243
244 (reverse chronological order - newest change first)
245 ***************************************************************/
246
247 CURSOR cur_rowid IS
248 SELECT rowid
249 FROM igs_ad_imp_near_mtch_all
250 WHERE interface_id = x_interface_id ;
251
252 lv_rowid cur_rowid%RowType;
253
254 BEGIN
255
256 Open cur_rowid;
257 Fetch cur_rowid INTO lv_rowid;
258 IF (cur_rowid%FOUND) THEN
259 Close cur_rowid;
260 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AINM_AINT_FK');
261 IGS_GE_MSG_STACK.ADD;
262 App_Exception.Raise_Exception;
263 Return;
264 END IF;
265 Close cur_rowid;
266
267 END Get_FK_Igs_Ad_Interface;
268
269 PROCEDURE Get_FK_Igs_Co_Addr_Type (
270 x_addr_type IN VARCHAR2
271 ) AS
272
273 /*************************************************************
274 Created By : amuthu
275 Date Created On : 15-May-2000
276 Purpose :
277 Know limitations, enhancements or remarks
278 Change History
279 Who When What
280
281 (reverse chronological order - newest change first)
282 ***************************************************************/
283
284 CURSOR cur_rowid IS
285 SELECT rowid
286 FROM igs_ad_imp_near_mtch_all
287 WHERE addr_type = x_addr_type ;
288
289 lv_rowid cur_rowid%RowType;
290
291 BEGIN
292
293 Open cur_rowid;
294 Fetch cur_rowid INTO lv_rowid;
295 IF (cur_rowid%FOUND) THEN
296 Close cur_rowid;
297 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AINM_ADT_FK');
298 IGS_GE_MSG_STACK.ADD;
299 App_Exception.Raise_Exception;
300 Return;
301 END IF;
302 Close cur_rowid;
303
304 END Get_FK_Igs_Co_Addr_Type;
305
306 PROCEDURE Get_FK_Igs_Pe_Match_Sets (
307 x_match_set_id IN NUMBER
308 ) AS
309
310 /*************************************************************
311 Created By : amuthu
312 Date Created On : 15-May-2000
313 Purpose :
314 Know limitations, enhancements or remarks
315 Change History
316 Who When What
317
318 (reverse chronological order - newest change first)
319 ***************************************************************/
320
321 CURSOR cur_rowid IS
322 SELECT rowid
323 FROM igs_ad_imp_near_mtch_all
324 WHERE match_set_id = x_match_set_id ;
325
326 lv_rowid cur_rowid%RowType;
327
328 BEGIN
329
330 Open cur_rowid;
331 Fetch cur_rowid INTO lv_rowid;
332 IF (cur_rowid%FOUND) THEN
333 Close cur_rowid;
334 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AINM_PMS_FK');
335 IGS_GE_MSG_STACK.ADD;
336 App_Exception.Raise_Exception;
337 Return;
338 END IF;
339 Close cur_rowid;
340
341 END Get_FK_Igs_Pe_Match_Sets;
342
343 PROCEDURE Get_FK_Igs_Pe_Person (
344 x_person_id IN NUMBER
345 ) AS
346
347 /*************************************************************
348 Created By : amuthu
349 Date Created On : 15-May-2000
350 Purpose :
351 Know limitations, enhancements or remarks
352 Change History
353 Who When What
354
355 (reverse chronological order - newest change first)
356 ***************************************************************/
357
358 CURSOR cur_rowid IS
359 SELECT rowid
360 FROM igs_ad_imp_near_mtch_all
361 WHERE person_id = x_person_id ;
362
363 lv_rowid cur_rowid%RowType;
364
365 BEGIN
366
367 Open cur_rowid;
368 Fetch cur_rowid INTO lv_rowid;
369 IF (cur_rowid%FOUND) THEN
370 Close cur_rowid;
371 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AINM_PE_FK');
372 IGS_GE_MSG_STACK.ADD;
373 App_Exception.Raise_Exception;
374 Return;
375 END IF;
376 Close cur_rowid;
377
378 END Get_FK_Igs_Pe_Person;
379
380 PROCEDURE Before_DML (
381 p_action IN VARCHAR2,
382 x_rowid IN VARCHAR2 DEFAULT NULL,
383 x_org_id IN NUMBER DEFAULT NULL,
384 x_near_mtch_id IN NUMBER DEFAULT NULL,
385 x_interface_id IN NUMBER DEFAULT NULL,
386 x_person_id IN NUMBER DEFAULT NULL,
387 x_match_ind IN VARCHAR2 DEFAULT NULL,
388 x_action IN VARCHAR2 DEFAULT NULL,
389 x_addr_type IN VARCHAR2 DEFAULT NULL,
390 x_person_id_type IN VARCHAR2 DEFAULT NULL,
391 x_match_set_id IN NUMBER DEFAULT NULL,
392 x_creation_date IN DATE DEFAULT NULL,
393 x_created_by IN NUMBER DEFAULT NULL,
394 x_last_update_date IN DATE DEFAULT NULL,
395 x_last_updated_by IN NUMBER DEFAULT NULL,
396 x_last_update_login IN NUMBER DEFAULT NULL,
397 x_party_site_id IN NUMBER DEFAULT NULL,
398 X_INTERFACE_RELATIONS_ID IN NUMBER DEFAULT NULL
399 ) AS
400 /*************************************************************
401 Created By : amuthu
402 Date Created On : 15-May-2000
403 Purpose :
404 Know limitations, enhancements or remarks
405 Change History
406 Who When What
407
408 (reverse chronological order - newest change first)
412
409 ***************************************************************/
410
411 BEGIN
413 Set_Column_Values (
414 p_action,
415 x_rowid,
416 x_org_id,
417 x_near_mtch_id,
418 x_interface_id,
419 x_person_id,
420 x_match_ind,
421 x_action,
422 x_addr_type,
423 x_person_id_type,
424 x_match_set_id,
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_party_site_id,
431 x_interface_relations_id
432
433 );
434
435 IF (p_action = 'INSERT') THEN
436 -- Call all the procedures related to Before Insert.
437 IF Get_Pk_For_Validation(new_references.near_mtch_id) THEN
438 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
439 IGS_GE_MSG_STACK.ADD;
440 App_Exception.Raise_Exception;
441 END IF;
442 Check_Constraints;
443 Check_Parent_Existance;
444 ELSIF (p_action = 'UPDATE') THEN
445 -- Call all the procedures related to Before Update.
446 Null;
447 Check_Constraints;
448 Check_Parent_Existance;
449 ELSIF (p_action = 'DELETE') THEN
450 -- Call all the procedures related to Before Delete.
451 Null;
452 ELSIF (p_action = 'VALIDATE_INSERT') THEN
453 -- Call all the procedures related to Before Insert.
454 IF Get_PK_For_Validation (
455 new_references.near_mtch_id) THEN
456 Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
457 IGS_GE_MSG_STACK.ADD;
458 App_Exception.Raise_Exception;
459 END IF;
460 Check_Constraints;
461 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
462 Check_Constraints;
463 ELSIF (p_action = 'VALIDATE_DELETE') THEN
464 Null;
465 END IF;
466
467 END Before_DML;
468
469 PROCEDURE After_DML (
470 p_action IN VARCHAR2,
471 x_rowid IN VARCHAR2
472 ) IS
473 /*************************************************************
474 Created By : amuthu
475 Date Created On : 15-May-2000
476 Purpose :
477 Know limitations, enhancements or remarks
478 Change History
479 Who When What
480
481 (reverse chronological order - newest change first)
482 ***************************************************************/
483
484 BEGIN
485
486 l_rowid := x_rowid;
487
488 IF (p_action = 'INSERT') THEN
489 -- Call all the procedures related to After Insert.
490 Null;
491 ELSIF (p_action = 'UPDATE') THEN
492 -- Call all the procedures related to After Update.
493 Null;
494 ELSIF (p_action = 'DELETE') THEN
495 -- Call all the procedures related to After Delete.
496 Null;
497 END IF;
498
499 END After_DML;
500
501 procedure INSERT_ROW (
502 X_ROWID in out NOCOPY VARCHAR2,
503 X_ORG_ID in NUMBER,
504 x_NEAR_MTCH_ID IN OUT NOCOPY NUMBER,
505 x_INTERFACE_ID IN NUMBER,
506 x_PERSON_ID IN NUMBER,
507 x_MATCH_IND IN VARCHAR2,
508 x_ACTION IN VARCHAR2,
509 x_ADDR_TYPE IN VARCHAR2,
510 x_PERSON_ID_TYPE IN VARCHAR2,
511 x_MATCH_SET_ID IN NUMBER,
512 X_MODE in VARCHAR2 default 'R',
513 X_PARTY_SITE_ID IN NUMBER ,
514 X_INTERFACE_RELATIONS_ID IN NUMBER
515 ) AS
516 /*************************************************************
517 Created By : amuthu
518 Date Created On : 15-May-2000
519 Purpose :
520 Know limitations, enhancements or remarks
521 Change History
522 Who When What
523 sbaliga 12-feb-2002 Modified call to before_dml by assigning
524 igs_ge_gen_003.get_org_id to x_org_id as part of
525 SWCR006 build.
526
527 (reverse chronological order - newest change first)
528 ***************************************************************/
529
530 cursor C is select ROWID from IGS_AD_IMP_NEAR_MTCH_ALL
531 where NEAR_MTCH_ID= X_NEAR_MTCH_ID;
532 X_LAST_UPDATE_DATE DATE ;
533 X_LAST_UPDATED_BY NUMBER ;
534 X_LAST_UPDATE_LOGIN NUMBER ;
535 X_REQUEST_ID NUMBER;
536 X_PROGRAM_ID NUMBER;
537 X_PROGRAM_APPLICATION_ID NUMBER;
538 X_PROGRAM_UPDATE_DATE DATE;
539 begin
540 X_LAST_UPDATE_DATE := SYSDATE;
541 if(X_MODE = 'I') then
542 X_LAST_UPDATED_BY := 1;
543 X_LAST_UPDATE_LOGIN := 0;
544 elsif (X_MODE = 'R') then
545 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
546 if X_LAST_UPDATED_BY is NULL then
547 X_LAST_UPDATED_BY := -1;
548 end if;
549 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
550 if X_LAST_UPDATE_LOGIN is NULL then
551 X_LAST_UPDATE_LOGIN := -1;
552 end if;
553 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
554 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
555 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
556 if (X_REQUEST_ID = -1) then
557 X_REQUEST_ID := NULL;
558 X_PROGRAM_ID := NULL;
562 X_PROGRAM_UPDATE_DATE := SYSDATE;
559 X_PROGRAM_APPLICATION_ID := NULL;
560 X_PROGRAM_UPDATE_DATE := NULL;
561 else
563 end if;
564 else
565 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
566 IGS_GE_MSG_STACK.ADD;
567 app_exception.raise_exception;
568 end if;
569
570 X_NEAR_MTCH_ID := -1;
571 Before_DML(
572 p_action=>'INSERT',
573 x_org_id=>igs_ge_gen_003.get_org_id,
574 x_rowid=>X_ROWID,
575 x_near_mtch_id=>X_NEAR_MTCH_ID,
576 x_interface_id=>X_INTERFACE_ID,
577 x_person_id=>X_PERSON_ID,
578 x_match_ind=>X_MATCH_IND,
579 x_action=>X_ACTION,
580 x_addr_type=>X_ADDR_TYPE,
581 x_person_id_type=>X_PERSON_ID_TYPE,
582 x_match_set_id=>X_MATCH_SET_ID,
583 x_creation_date=>X_LAST_UPDATE_DATE,
584 x_created_by=>X_LAST_UPDATED_BY,
585 x_last_update_date=>X_LAST_UPDATE_DATE,
586 x_last_updated_by=>X_LAST_UPDATED_BY,
587 x_last_update_login=>X_LAST_UPDATE_LOGIN,
588 x_party_site_id => X_PARTY_SITE_ID,
589 x_interface_relations_id => X_INTERFACE_RELATIONS_ID);
590
591 insert into IGS_AD_IMP_NEAR_MTCH_ALL (
592 NEAR_MTCH_ID
593 ,INTERFACE_ID
594 ,PERSON_ID
595 ,ORG_ID
596 ,MATCH_IND
597 ,ACTION
598 ,ADDR_TYPE
599 ,PERSON_ID_TYPE
600 ,MATCH_SET_ID
601 ,CREATION_DATE
602 ,CREATED_BY
603 ,LAST_UPDATE_DATE
604 ,LAST_UPDATED_BY
605 ,LAST_UPDATE_LOGIN
606 ,REQUEST_ID
607 ,PROGRAM_ID
608 ,PROGRAM_APPLICATION_ID
609 ,PROGRAM_UPDATE_DATE
610 ,PARTY_SITE_ID
611 ,INTERFACE_RELATIONS_ID
612
613 ) values (
614 IGS_AD_IMP_NEAR_MTCH_S.NEXTVAL
615 ,NEW_REFERENCES.INTERFACE_ID
616 ,NEW_REFERENCES.PERSON_ID
617 ,NEW_REFERENCES.ORG_ID
618 ,NEW_REFERENCES.MATCH_IND
619 ,NEW_REFERENCES.ACTION
620 ,NEW_REFERENCES.ADDR_TYPE
621 ,NEW_REFERENCES.PERSON_ID_TYPE
622 ,NEW_REFERENCES.MATCH_SET_ID
623 ,X_LAST_UPDATE_DATE
624 ,X_LAST_UPDATED_BY
625 ,X_LAST_UPDATE_DATE
626 ,X_LAST_UPDATED_BY
627 ,X_LAST_UPDATE_LOGIN
628 ,X_REQUEST_ID
629 ,X_PROGRAM_ID
630 ,X_PROGRAM_APPLICATION_ID
631 ,X_PROGRAM_UPDATE_DATE
632 ,NEW_REFERENCES.PARTY_SITE_ID
633 ,NEW_REFERENCES.INTERFACE_RELATIONS_ID )RETURNING NEAR_MTCH_ID INTO X_NEAR_MTCH_ID;
634
635 open c;
636 fetch c into X_ROWID;
637 if (c%notfound) then
638 close c;
639 raise no_data_found;
640 end if;
641 close c;
642
643 After_DML (
644 p_action => 'INSERT' ,
645 x_rowid => X_ROWID );
646
647 end INSERT_ROW;
648
649
650 procedure LOCK_ROW (
651 X_ROWID in VARCHAR2,
652 x_NEAR_MTCH_ID IN NUMBER,
653 x_INTERFACE_ID IN NUMBER,
654 x_PERSON_ID IN NUMBER,
655 x_MATCH_IND IN VARCHAR2,
656 x_ACTION IN VARCHAR2,
657 x_ADDR_TYPE IN VARCHAR2,
658 x_PERSON_ID_TYPE IN VARCHAR2,
659 x_MATCH_SET_ID IN NUMBER,
660 X_PARTY_SITE_ID IN NUMBER,
661 X_INTERFACE_RELATIONS_ID IN NUMBER ) AS
662 /*************************************************************
663 Created By : amuthu
664 Date Created On : 15-May-2000
665 Purpose :
666 Know limitations, enhancements or remarks
667 Change History
668 Who When What
669
670 (reverse chronological order - newest change first)
671 ***************************************************************/
672
673 cursor c1 is select
674 INTERFACE_ID,
675 PERSON_ID,
676 MATCH_IND,
677 ACTION,
678 ADDR_TYPE,
679 PERSON_ID_TYPE,
680 MATCH_SET_ID,
681 PARTY_SITE_ID,
682 INTERFACE_RELATIONS_ID
683 from IGS_AD_IMP_NEAR_MTCH_ALL
684 where ROWID = X_ROWID
685 for update nowait;
686
687 tlinfo c1%rowtype;
688 begin
689 open c1;
690 fetch c1 into tlinfo;
691 if (c1%notfound) then
692 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
693 IGS_GE_MSG_STACK.ADD;
694 close c1;
695 app_exception.raise_exception;
696 return;
697 end if;
698 close c1;
699 if ( ( tlinfo.INTERFACE_ID = X_INTERFACE_ID)
700 AND (tlinfo.PERSON_ID = X_PERSON_ID)
701 AND ((tlinfo.MATCH_IND = X_MATCH_IND)
702 OR ((tlinfo.MATCH_IND is null)
703 AND (X_MATCH_IND is null)))
704 AND ((tlinfo.ACTION = X_ACTION)
705 OR ((tlinfo.ACTION is null)
706 AND (X_ACTION is null)))
707 AND (tlinfo.ADDR_TYPE = X_ADDR_TYPE OR (tlinfo.ADDR_TYPE IS NULL AND X_ADDR_TYPE IS NULL ))
708 AND (tlinfo.PERSON_ID_TYPE = X_PERSON_ID_TYPE OR (tlinfo.PERSON_ID_TYPE IS NULL AND X_PERSON_ID_TYPE IS NULL ) )
709 AND (tlinfo.MATCH_SET_ID = X_MATCH_SET_ID)
710 AND (tlinfo.PARTY_SITE_ID = X_PARTY_SITE_ID OR (tlinfo.PARTY_SITE_ID IS NULL AND X_PARTY_SITE_ID IS NULL ) )
714 else
711 AND (tlinfo.INTERFACE_RELATIONS_ID = X_INTERFACE_RELATIONS_ID OR (tlinfo.INTERFACE_RELATIONS_ID IS NULL AND X_INTERFACE_RELATIONS_ID IS NULL ) )
712 ) then
713 NULL;
715 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
716 IGS_GE_MSG_STACK.ADD;
717 app_exception.raise_exception;
718 end if;
719 return;
720
721 end LOCK_ROW;
722 Procedure UPDATE_ROW (
723 X_ROWID in VARCHAR2,
724 x_NEAR_MTCH_ID IN NUMBER,
725 x_INTERFACE_ID IN NUMBER,
726 x_PERSON_ID IN NUMBER,
727 x_MATCH_IND IN VARCHAR2,
728 x_ACTION IN VARCHAR2,
729 x_ADDR_TYPE IN VARCHAR2,
730 x_PERSON_ID_TYPE IN VARCHAR2,
731 x_MATCH_SET_ID IN NUMBER,
732 X_MODE in VARCHAR2 default 'R',
733 X_PARTY_SITE_ID IN NUMBER ,
734 X_INTERFACE_RELATIONS_ID IN NUMBER
735 ) AS
736 /*************************************************************
737 Created By : amuthu
738 Date Created On : 15-May-2000
739 Purpose :
740 Know limitations, enhancements or remarks
741 Change History
742 Who When What
743
744 (reverse chronological order - newest change first)
745 ***************************************************************/
746
747 X_LAST_UPDATE_DATE DATE ;
748 X_LAST_UPDATED_BY NUMBER ;
749 X_LAST_UPDATE_LOGIN NUMBER ;
750 X_REQUEST_ID NUMBER;
751 X_PROGRAM_ID NUMBER;
752 X_PROGRAM_APPLICATION_ID NUMBER;
753 X_PROGRAM_UPDATE_DATE DATE;
754 begin
755 X_LAST_UPDATE_DATE := SYSDATE;
756 if(X_MODE = 'I') then
757 X_LAST_UPDATED_BY := 1;
758 X_LAST_UPDATE_LOGIN := 0;
759 elsif (X_MODE = 'R') then
760 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
761 if X_LAST_UPDATED_BY is NULL then
762 X_LAST_UPDATED_BY := -1;
763 end if;
764 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
765 if X_LAST_UPDATE_LOGIN is NULL then
766 X_LAST_UPDATE_LOGIN := -1;
767 end if;
768 else
769 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
770 IGS_GE_MSG_STACK.ADD;
771 app_exception.raise_exception;
772 end if;
773
774 Before_DML(
775 p_action=>'UPDATE',
776 x_rowid=>X_ROWID,
777 x_near_mtch_id=>X_NEAR_MTCH_ID,
778 x_interface_id=>X_INTERFACE_ID,
779 x_person_id=>X_PERSON_ID,
780 x_match_ind=>X_MATCH_IND,
781 x_action=>X_ACTION,
782 x_addr_type=>X_ADDR_TYPE,
783 x_person_id_type=>X_PERSON_ID_TYPE,
784 x_match_set_id=>X_MATCH_SET_ID,
785 x_creation_date=>X_LAST_UPDATE_DATE,
786 x_created_by=>X_LAST_UPDATED_BY,
787 x_last_update_date=>X_LAST_UPDATE_DATE,
788 x_last_updated_by=>X_LAST_UPDATED_BY,
789 x_last_update_login=>X_LAST_UPDATE_LOGIN,
790 x_party_site_id => X_PARTY_SITE_ID,
791 x_interface_relations_id => X_INTERFACE_RELATIONS_ID);
792
793 if (X_MODE = 'R') then
794 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
795 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
796 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
797 if (X_REQUEST_ID = -1) then
798 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
799 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
800 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
801 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
802 else
803 X_PROGRAM_UPDATE_DATE := SYSDATE;
804 end if;
805 end if;
806
807 update IGS_AD_IMP_NEAR_MTCH_ALL set
808 INTERFACE_ID = NEW_REFERENCES.INTERFACE_ID,
809 PERSON_ID = NEW_REFERENCES.PERSON_ID,
810 MATCH_IND = NEW_REFERENCES.MATCH_IND,
811 ACTION = NEW_REFERENCES.ACTION,
812 ADDR_TYPE = NEW_REFERENCES.ADDR_TYPE,
813 PERSON_ID_TYPE = NEW_REFERENCES.PERSON_ID_TYPE,
814 MATCH_SET_ID = NEW_REFERENCES.MATCH_SET_ID,
815 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
816 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
817 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
818 REQUEST_ID = X_REQUEST_ID,
819 PROGRAM_ID = X_PROGRAM_ID,
820 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID,
821 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
822 PARTY_SITE_ID = X_PARTY_SITE_ID,
823 INTERFACE_RELATIONS_ID = X_INTERFACE_RELATIONS_ID
824 where ROWID = X_ROWID;
825
826 if (sql%notfound) then
827 raise no_data_found;
828 end if;
829
830 After_DML (
831 p_action => 'UPDATE' ,
832 x_rowid => X_ROWID
833 );
834 end UPDATE_ROW;
835
836
837 procedure ADD_ROW (
838 X_ROWID in out NOCOPY VARCHAR2,
839 X_ORG_ID in NUMBER,
840 x_NEAR_MTCH_ID IN OUT NOCOPY NUMBER,
841 x_INTERFACE_ID IN NUMBER,
842 x_PERSON_ID IN NUMBER,
843 x_MATCH_IND IN VARCHAR2,
844 x_ACTION IN VARCHAR2,
845 x_ADDR_TYPE IN VARCHAR2,
846 x_PERSON_ID_TYPE IN VARCHAR2,
847 x_MATCH_SET_ID IN NUMBER,
848 X_MODE in VARCHAR2 default 'R',
849 x_PARTY_SITE_ID IN NUMBER ,
850 X_INTERFACE_RELATIONS_ID IN NUMBER
854 Date Created On : 15-May-2000
851 ) AS
852 /*************************************************************
853 Created By : amuthu
855 Purpose :
856 Know limitations, enhancements or remarks
857 Change History
858 Who When What
859
860 (reverse chronological order - newest change first)
861 ***************************************************************/
862
863 cursor c1 is select ROWID from IGS_AD_IMP_NEAR_MTCH_ALL
864 where NEAR_MTCH_ID= X_NEAR_MTCH_ID;
865 begin
866 open c1;
867 fetch c1 into X_ROWID;
868 if (c1%notfound) then
869 close c1;
870 INSERT_ROW (
871 X_ROWID,
872 X_ORG_ID,
873 X_NEAR_MTCH_ID,
874 X_INTERFACE_ID,
875 X_PERSON_ID,
876 X_MATCH_IND,
877 X_ACTION,
878 X_ADDR_TYPE,
879 X_PERSON_ID_TYPE,
880 X_MATCH_SET_ID,
881 X_MODE,
882 X_PARTY_SITE_ID ,
883 X_INTERFACE_RELATIONS_ID);
884 return;
885 end if;
886 close c1;
887 UPDATE_ROW (
888 X_ROWID,
889 X_NEAR_MTCH_ID,
890 X_INTERFACE_ID,
891 X_PERSON_ID,
892 X_MATCH_IND,
893 X_ACTION,
894 X_ADDR_TYPE,
895 X_PERSON_ID_TYPE,
896 X_MATCH_SET_ID,
897 X_MODE,
898 X_PARTY_SITE_ID,
899 X_INTERFACE_RELATIONS_ID );
900
901 end ADD_ROW;
902
903
904 procedure DELETE_ROW (
905 X_ROWID in VARCHAR2
906 ) AS
907 /*************************************************************
908 Created By : amuthu
909 Date Created On : 15-May-2000
910 Purpose :
911 Know limitations, enhancements or remarks
912 Change History
913 Who When What
914
915 (reverse chronological order - newest change first)
916 ***************************************************************/
917
918 begin
919 Before_DML (
920 p_action => 'DELETE',
921 x_rowid => X_ROWID
922 );
923
924 delete from IGS_AD_IMP_NEAR_MTCH_ALL
925 where ROWID = X_ROWID;
926 if (sql%notfound) then
927 raise no_data_found;
928 end if;
929
930 After_DML (
931 p_action => 'DELETE',
932 x_rowid => X_ROWID
933 );
934
935 end DELETE_ROW;
936
937 END igs_ad_imp_near_mtch_pkg;
[Home] [Help]