1 PACKAGE Igs_Pe_Identify_Dups AS
2 /* $Header: IGSPE03S.pls 120.0 2005/06/01 18:01:08 appldev noship $ */
3
4
5 -- Main procedure called from the Identify Duplicates process.
6 PROCEDURE find_dup_main(
7 ERRBUF OUT NOCOPY VARCHAR2,
8 RETCODE OUT NOCOPY VARCHAR2,
9 x_match_set_id IN NUMBER,
10 x_report_requested IN VARCHAR2);
11 -- procedure called from the find_dup_main procedure.
12 PROCEDURE find_duplicates (
13 x_errbuf IN OUT NOCOPY VARCHAR2,
14 x_retcode IN OUT NOCOPY VARCHAR2,
15 x_match_set_id IN NUMBER);
16 -- Procedure to find the duplicate records using the person related info only.
17 PROCEDURE find_duplicates_p(
18 x_errbuf IN OUT NOCOPY VARCHAR2,
19 x_retcode IN OUT NOCOPY VARCHAR2,
20 x_match_set_id IN NUMBER,
21 x_batch_id IN NUMBER);
22 -- Procedure to find the duplicate records using the person and person id type related info only.
23 PROCEDURE find_duplicates_pp(
24 x_errbuf IN OUT NOCOPY VARCHAR2,
25 x_retcode IN OUT NOCOPY VARCHAR2,
26 x_match_set_id IN NUMBER,
27 x_batch_id IN NUMBER,
28 x_person_id_type IN VARCHAR2);
29 -- Procedure to find the duplicate records using the person and address related info only.
30 PROCEDURE find_duplicates_pa(
31 x_errbuf IN OUT NOCOPY VARCHAR2,
32 x_retcode IN OUT NOCOPY VARCHAR2,
33 x_match_set_id IN NUMBER,
34 x_batch_id IN NUMBER,
35 x_addr_type IN VARCHAR2);
36 -- Procedure to find the duplicate records using the person, address and person id type related info.
37 PROCEDURE find_duplicates_ppa(
38 x_errbuf IN OUT NOCOPY VARCHAR2,
39 x_retcode IN OUT NOCOPY VARCHAR2,
40 x_match_set_id IN NUMBER,
41 x_batch_id IN NUMBER,
42 x_addr_type IN VARCHAR2,
43 x_person_id_type IN VARCHAR2);
44 -- Procedure to form the select clause which returns the person duplicate records in the system.
45 PROCEDURE form_dup_whereclause(
46 x_errbuf IN OUT NOCOPY VARCHAR2 ,
47 x_retcode IN OUT NOCOPY VARCHAR2 ,
48 x_match_set_id IN NUMBER DEFAULT NULL,
49 x_match_category IN VARCHAR2 DEFAULT NULL,
50 x_view_name IN VARCHAR2 DEFAULT NULL,
51 x_person_id IN NUMBER DEFAULT NULL,
52 x_surname IN VARCHAR2 DEFAULT NULL,
53 x_given_names IN VARCHAR2 DEFAULT NULL,
54 x_api_person_id IN VARCHAR2 DEFAULT NULL,
55 x_pref_alternate_id IN VARCHAR2 DEFAULT NULL,
56 x_person_id_type IN VARCHAR2 DEFAULT NULL,
57 x_birth_dt IN DATE DEFAULT NULL,
58 x_sex IN VARCHAR2 DEFAULT NULL,
59 x_ethnic_origin IN VARCHAR2 DEFAULT NULL,
60 x_addr_type IN VARCHAR2 DEFAULT NULL,
61 x_addr_line_1 IN VARCHAR2 DEFAULT NULL,
62 x_addr_line_2 IN VARCHAR2 DEFAULT NULL,
63 x_addr_line_3 IN VARCHAR2 DEFAULT NULL,
64 x_addr_line_4 IN VARCHAR2 DEFAULT NULL,
65 x_city IN VARCHAR2 DEFAULT NULL,
66 x_state IN VARCHAR2 DEFAULT NULL,
67 x_province IN VARCHAR2 DEFAULT NULL,
68 x_county IN VARCHAR2 DEFAULT NULL,
69 x_country IN VARCHAR2 DEFAULT NULL,
70 x_postcode IN VARCHAR2 DEFAULT NULL,
71 x_select_clause IN OUT NOCOPY VARCHAR2
72 );
73 -- Procedure to find duplicate match for Related Person during import
74 TYPE r_record_dup_rel IS RECORD(
75 INTERFACE_ID igs_ad_interface.interface_id%TYPE,
76 INTERFACE_RELATIONS_ID igs_ad_relations_int.interface_relations_id%TYPE,
77 SURNAME igs_ad_relations_int.surname%TYPE,
78 FIRST_NAME igs_Ad_relations_int.given_names%TYPE,
79 GENDER igs_ad_relations_int.sex%TYPE,
80 BIRTH_DATE igs_ad_relations_int.birth_dt%TYPE,
81 BATCH_ID igs_ad_interface.batch_id%TYPE,
82 MATCH_SET_ID igs_pe_match_sets.match_set_id%TYPE);
83
84 r_record_dup_rel_rec r_record_dup_rel;
85
86 TYPE r_matchset_partial IS RECORD(
87 data_element igs_pe_mtch_set_data_all.data_element%TYPE,
88 drop_if_null igs_pe_mtch_set_data_all.drop_if_null%TYPE);
89
90 TYPE t_matchset IS TABLE OF r_matchset_partial INDEX BY BINARY_INTEGER;
91
92 -- global variables added as part of Bug 3130316, Duplicate Person Matching Performance Improvements
93 g_partial_if_null igs_pe_match_sets.partial_if_null%TYPE;
94 g_exclude_inactive_ind igs_pe_match_sets.exclude_inactive_ind%TYPE;
95 g_match_set_id igs_pe_match_sets.match_set_id%TYPE;
96 g_source_type_id NUMBER; -- nsidana Bug 3633341
97 g_addr_type_din igs_pe_mtch_set_data_all.drop_if_null%TYPE;
98 g_person_id_type_din igs_pe_mtch_set_data_all.drop_if_null%TYPE;
99 g_matchset_exact t_matchset;
100 g_matchset_partial t_matchset;
101
102 PROCEDURE Find_dup_rel_per(
103 P_REL_DUP_REC IN r_record_dup_rel,
104 P_MATCH_FOUND OUT NOCOPY VARCHAR2
105 );
106
107 -- global variable added as part of Bug 3146324, Match Criteria Sets Enhancements
108 g_primary_addr_flag VARCHAR2(1);
109
110 END Igs_Pe_Identify_Dups;