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