1 PACKAGE HZ_DUP_PVT AUTHID CURRENT_USER AS
2 /*$Header: ARHDUPBS.pls 120.16.12010000.3 2009/10/28 18:00:19 awu ship $*/
3
4 -- all candidates, except master party_id in a dup set
5 TYPE dup_party_rec_type IS RECORD (
6 party_id NUMBER
7 ,score NUMBER
8 ,merge_flag VARCHAR2(1)
9 ,not_dup VARCHAR2(1)
10 ,merge_seq_id NUMBER
11 ,merge_batch_id NUMBER
12 ,merge_batch_name VARCHAR2(30) );
13
14 TYPE dup_party_tbl_type IS TABLE OF dup_party_rec_type INDEX BY BINARY_INTEGER;
15
16 -- dup batch record
17 TYPE dup_batch_rec_type IS RECORD (
18 dup_batch_name VARCHAR2(255)
19 ,match_rule_id NUMBER
20 ,application_id NUMBER
21 ,request_type VARCHAR2(30) );
22
23 -- dup set and dup set parties record
24 TYPE dup_set_rec_type IS RECORD (
25 dup_batch_id NUMBER
26 ,winner_party_id NUMBER
27 ,status VARCHAR2(60)
28 ,assigned_to_user_id NUMBER
29 ,merge_type VARCHAR2(30) );
30
31 -- This procedure create one dup batch, one dup set and one dup set party record
32 PROCEDURE create_dup (
33 dup_batch_name IN VARCHAR2
34 ,match_rule_id IN NUMBER
35 ,application_id IN NUMBER
36 ,request_type IN VARCHAR2
37 ,winner_party_id IN NUMBER
38 ,status IN VARCHAR2
39 ,assigned_to_user_id IN NUMBER DEFAULT NULL
40 ,merge_type IN VARCHAR2
41 ,party_id IN NUMBER DEFAULT NULL
42 ,score IN NUMBER DEFAULT 0
43 ,merge_flag IN VARCHAR2 DEFAULT 'Y'
44 ,not_dup IN VARCHAR2 DEFAULT NULL
45 ,merge_seq_id IN NUMBER DEFAULT NULL
46 ,merge_batch_id IN NUMBER DEFAULT NULL
47 ,merge_batch_name IN VARCHAR2 DEFAULT NULL
48 ,x_dup_batch_id OUT NOCOPY NUMBER
49 ,x_dup_set_id OUT NOCOPY NUMBER
50 ,x_return_status OUT NOCOPY VARCHAR2
51 ,x_msg_count OUT NOCOPY NUMBER
52 ,x_msg_data OUT NOCOPY VARCHAR2 );
53
54 -- This procedure create only dup set party record
55 PROCEDURE create_dup_set_party (
56 p_dup_set_id IN NUMBER
57 ,p_dup_set_party_id IN NUMBER
58 ,p_score IN NUMBER DEFAULT 0
59 ,p_merge_flag IN VARCHAR2 DEFAULT 'Y'
60 ,p_not_dup IN VARCHAR2 DEFAULT NULL
61 ,p_merge_seq_id IN NUMBER DEFAULT NULL
62 ,p_merge_batch_id IN NUMBER DEFAULT NULL
63 ,p_merge_batch_name IN VARCHAR2 DEFAULT NULL
64 ,x_return_status OUT NOCOPY VARCHAR2
65 ,x_msg_count OUT NOCOPY NUMBER
66 ,x_msg_data OUT NOCOPY VARCHAR2 );
67
68 -- create records in dup set and dup set parties based on existing dup_batch
69 PROCEDURE create_dup_set (
70 p_dup_set_rec IN DUP_SET_REC_TYPE
71 ,p_dup_party_tbl IN DUP_PARTY_TBL_TYPE
72 ,x_dup_set_id OUT NOCOPY NUMBER
73 ,x_return_status OUT NOCOPY VARCHAR2
74 ,x_msg_count OUT NOCOPY NUMBER
75 ,x_msg_data OUT NOCOPY VARCHAR2 );
76
77 -- create records in dup batch, dup_set and dup_set_parties
78 PROCEDURE create_dup_batch (
79 p_dup_batch_rec IN DUP_BATCH_REC_TYPE
80 ,p_dup_set_rec IN DUP_SET_REC_TYPE
81 ,p_dup_party_tbl IN DUP_PARTY_TBL_TYPE
82 ,x_dup_batch_id OUT NOCOPY NUMBER
83 ,x_dup_set_id OUT NOCOPY NUMBER
84 ,x_return_status OUT NOCOPY VARCHAR2
85 ,x_msg_count OUT NOCOPY NUMBER
86 ,x_msg_data OUT NOCOPY VARCHAR2 );
87
88 -- update winner_party_id in HZ_DUP_SETS table
89 PROCEDURE update_winner_party (
90 p_dup_set_id IN NUMBER
91 ,p_winner_party_id IN NUMBER
92 ,px_set_obj_version_number IN OUT NOCOPY NUMBER
93 ,x_return_status OUT NOCOPY VARCHAR2
94 ,x_msg_count OUT NOCOPY NUMBER
95 ,x_msg_data OUT NOCOPY VARCHAR2 );
96
97 -- remove candidate in HZ_DUP_SETS table
98 PROCEDURE delete_dup_party (
99 p_dup_set_id IN NUMBER
100 ,p_dup_party_id IN NUMBER
101 ,p_new_winner_party_id IN NUMBER
102 ,px_set_obj_version_number IN OUT NOCOPY NUMBER
103 ,x_return_status OUT NOCOPY VARCHAR2
104 ,x_msg_count OUT NOCOPY NUMBER
105 ,x_msg_data OUT NOCOPY VARCHAR2 );
106
107 -- restamp merge_type and hard delete candidates in HZ_DUP_SET_PARTIES table
108 PROCEDURE reset_merge_type (
109 p_dup_set_id IN NUMBER
110 ,px_set_obj_version_number IN OUT NOCOPY NUMBER
111 ,x_return_status OUT NOCOPY VARCHAR2
112 ,x_msg_count OUT NOCOPY NUMBER
113 ,x_msg_data OUT NOCOPY VARCHAR2 );
114
115 -- reject merge request
116 PROCEDURE reject_merge (
117 p_dup_set_id IN NUMBER
118 ,px_set_obj_version_number IN OUT NOCOPY NUMBER
119 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
120 ,x_return_status OUT NOCOPY VARCHAR2
121 ,x_msg_count OUT NOCOPY NUMBER
122 ,x_msg_data OUT NOCOPY VARCHAR2 );
123
124 -- call concurrent program - create_merge
125 PROCEDURE submit_dup (
126 p_dup_set_id IN NUMBER
127 ,x_request_id OUT NOCOPY NUMBER
128 ,x_return_status OUT NOCOPY VARCHAR2
129 ,x_msg_count OUT NOCOPY NUMBER
130 ,x_msg_data OUT NOCOPY VARCHAR2 );
131
132 -- concurrent program to create merge base on dup_set_id
133 PROCEDURE create_merge (
134 Errbuf OUT NOCOPY VARCHAR2
135 ,Retcode OUT NOCOPY VARCHAR2
136 ,p_dup_set_id IN NUMBER );
137
138 -- called by Data Librarian UI. Default master based on the profile.
139 -- Dup set has to be created first with random winner party, then change
140 -- winner party id to the one based on defaulting rule.
141
142 procedure default_master(
143 p_dup_set_id IN NUMBER,
144 x_master_party_id OUT NOCOPY NUMBER,
145 x_master_party_name OUT NOCOPY VARCHAR2,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_count OUT NOCOPY NUMBER,
148 x_msg_data OUT NOCOPY VARCHAR2 );
149
150
151 PROCEDURE validate_party_modeling( p_party_ids IN VARCHAR2,
152 x_cert_warn OUT NOCOPY VARCHAR2,
153 x_reln_warn OUT NOCOPY VARCHAR2,
154 x_reln_token OUT NOCOPY VARCHAR2
155 );
156 FUNCTION get_automerge_candidate(p_party_score NUMBER, p_automerge_score NUMBER)
157 RETURN VARCHAR2;
158
159 procedure party_merge_dss_check(p_merge_batch_id in number,
160 x_dss_update_flag out nocopy varchar2,
161 x_return_status OUT NOCOPY VARCHAR2,
162 x_msg_count OUT NOCOPY NUMBER,
163 x_msg_data OUT NOCOPY VARCHAR2 );
164
165 function show_dss_lock(p_dup_set_id in number) return varchar2;
166 FUNCTION get_update_flag(x_dup_set_id NUMBER) RETURN VARCHAR2;
167
168 PROCEDURE reprocess_merge_request (
169 p_dup_set_id IN NUMBER
170 ,x_request_id OUT NOCOPY NUMBER
171 ,x_return_status OUT NOCOPY VARCHAR2
172 ,x_msg_count OUT NOCOPY NUMBER
173 ,x_msg_data OUT NOCOPY VARCHAR2 );
174
175 procedure reset_dup_set_status;
176
177 procedure get_match_rule_thresholds(p_match_rule_id in number,
178 x_match_threshold out nocopy number,
179 x_automerge_threshold out nocopy number);
180
181 procedure get_most_matching_party(p_search_ctx_id in number,
182 p_new_party_id in number,
183 x_party_id out nocopy number,
184 x_match_score out nocopy number,
185 x_party_name out nocopy varchar2);
186 procedure validate_master_party_id(px_party_id in out nocopy number,
187 x_overlap_merge_req_id out nocopy number);
188
189 END HZ_DUP_PVT;