1 PACKAGE BODY HZ_IMP_MATCH_RULE_52 AS
2 g_match_rule_id NUMBER := 52;
3 TYPE StageImpContactCurTyp IS REF CURSOR;
4 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 TYPE CharList2000 IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
6 TYPE CharList1000 IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
7 TYPE CharList30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
8 TYPE CharList60 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
9 TYPE CharList240 IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
10 TYPE CharList1 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
11 TYPE RowIdList IS TABLE OF rowid INDEX BY BINARY_INTEGER;
12 H_P_ROW_ID RowIdList;
13 H_P_N_PARTY CharList1;
14 H_P_PARTY_ID NumberList;
15 H_P_PARTY_OS CharList30;
16 H_P_PARTY_OSR CharList240;
17 H_P_PS_OS CharList30;
18 H_P_PS_OSR CharList240;
19 H_P_P_TYPE CharList30;
20 H_P_PARTY_SITE_ID NumberList;
21 H_P_CONTACT_POINT_ID NumberList;
22 H_P_CP_OS CharList30;
23 H_P_CP_OSR CharList240;
24 H_P_SUBJECT_OS CharList30;
25 H_P_SUBJECT_OSR CharList240;
26 H_P_CONTACT_OS CharList30;
27 H_P_CONTACT_OSR CharList240;
28 H_P_CP_TYPE CharList30;
29 H_TX0 CharList2000;
30 g_limit NUMBER := 1000;
31 H_CT_OBJ_ID NumberList;
32 H_TX10 CharList2000;
33 H_TX11 CharList2000;
34 H_TX14 CharList2000;
35 H_TX15 CharList2000;
36 H_TX156 CharList2000;
37 H_TX158 CharList2000;
38 H_TX19 CharList2000;
39 H_TX2 CharList2000;
40 H_TX22 CharList2000;
41 H_TX23 CharList2000;
42 H_TX24 CharList2000;
43 H_TX26 CharList2000;
44 H_TX27 CharList2000;
45 H_TX3 CharList2000;
46 H_TX36 CharList2000;
47 H_TX4 CharList2000;
48 H_TX41 CharList2000;
49 H_TX45 CharList2000;
50 H_TX46 CharList2000;
51 H_TX5 CharList2000;
52 H_TX59 CharList2000;
53 H_TX6 CharList2000;
54 H_TX60 CharList2000;
55 H_TX7 CharList2000;
56 H_TX8 CharList2000;
57 H_TX9 CharList2000;
58 H_8E CharList2000;
59 H_14E CharList2000;
60 H_16E CharList2000;
61 H_19E CharList2000;
62 H_20E CharList2000;
63 H_29E CharList2000;
64 H_30E CharList2000;
65 H_32E CharList2000;
66 H_36E CharList2000;
67 H_43E CharList2000;
68 H_48E CharList2000;
69 H_49E CharList2000;
70 H_CT_NAME CharList2000;
71 H_CT_CUST_TX2 CharList2000;
72 H_CT_CUST_TX23 CharList2000;
73 H_P_CP_R_PH_NO CharList60;
74 H_CP_CUST_TX10 CharList2000;
75 H_CP_CUST_TX158 CharList2000;
76 H_P_PS_ADD CharList1000;
77 H_PS_CUST_TX26 CharList2000;
78 PROCEDURE pop_parties (
79 p_batch_id IN NUMBER,
80 p_from_osr IN VARCHAR2,
81 p_to_osr IN VARCHAR2,
82 p_batch_mode_flag IN VARCHAR2
83 ) IS
84 l_last_fetch BOOLEAN := FALSE;
85 p_party_cur HZ_PARTY_STAGE.StageCurTyp;
86
87 count NUMBER := 0;
88 BEGIN
89 -- query for interface to TCA
90 open p_party_cur FOR
91 select decode(a.party_type, 'ORGANIZATION', a.organization_name, 'PERSON', a.person_first_name || ' ' || a.person_last_name) as PARTY_NAME , a.PARTY_TYPE, a.DUNS_NUMBER_C, a.JGZZ_FISCAL_CODE, a.party_orig_system, a.party_orig_system_reference, b.party_id, a.rowid, a.party_type
92 from hz_imp_parties_int a, hz_imp_parties_sg b
93 where b.action_flag = 'I'
94 and b.int_row_id = a.rowid
95 and a.batch_id = p_batch_id
96 and b.party_orig_system_reference >= p_from_osr
97 and b.party_orig_system_reference <= p_to_osr
98 and b.batch_mode_flag = p_batch_mode_flag
99 and interface_status is null ;
100 LOOP
101 FETCH p_party_cur BULK COLLECT INTO
102 H_8E, H_14E, H_16E, H_19E, H_P_PARTY_OS , H_P_PARTY_OSR, H_P_PARTY_ID, H_P_ROW_ID, H_P_P_TYPE
103 LIMIT g_limit;
104 IF (p_party_cur%NOTFOUND) THEN
105 l_last_fetch:=TRUE;
106 END IF;
107
108 IF H_P_PARTY_OS.COUNT=0 AND l_last_fetch THEN
109 EXIT;
110 END IF;
111
112 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
113 HZ_TRANS_PKG.set_party_type(H_P_P_TYPE(I));
114 H_TX2(I) := HZ_TRANS_PKG.EXACT_PADDED(H_8E(I), NULL, 'PARTY_NAME', 'PARTY');
115 H_TX36(I) := HZ_TRANS_PKG.EXACT(H_14E(I), NULL, 'PARTY_TYPE', 'PARTY');
116 H_TX41(I) := HZ_TRANS_PKG.EXACT(H_16E(I), NULL, 'DUNS_NUMBER_C', 'PARTY');
117 H_TX45(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_19E(I), NULL, 'JGZZ_FISCAL_CODE', 'PARTY', 'SEARCH' );
118 H_TX59(I) := HZ_TRANS_PKG.BASIC_WRNAMES(H_8E(I), NULL, 'PARTY_NAME', 'PARTY', 'SEARCH' );
119 END LOOP;
120 SAVEPOINT pop_parties;
121 BEGIN
122 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
123 INSERT INTO HZ_SRCH_PARTIES (
124 TX2, TX36, TX41, TX45, TX59, PARTY_OS, PARTY_OSR, PARTY_ID, BATCH_ID, INT_ROW_ID
125 ) VALUES (
126 H_TX2(I), H_TX36(I), H_TX41(I), H_TX45(I), H_TX59(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PARTY_ID(I), P_BATCH_ID, H_P_ROW_ID(I)
127 );
128 EXCEPTION
129 WHEN OTHERS THEN
130 ROLLBACK to pop_parties;
131 -- dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
132 RAISE;
133 END ;
134 IF l_last_fetch THEN
135 FND_CONCURRENT.AF_Commit;
136 EXIT;
137 END IF;
138 FND_CONCURRENT.AF_Commit;
139
140 END LOOP;
141 CLOSE p_party_cur;
142 END pop_parties;
143
144 PROCEDURE pop_party_sites (
145 p_batch_id IN NUMBER,
146 p_from_osr IN VARCHAR2,
147 p_to_osr IN VARCHAR2,
148 p_batch_mode_flag IN VARCHAR2
149 ) IS
150 l_last_fetch BOOLEAN := FALSE;
151 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
152
153 BEGIN
154 -- query for interface to tca
155 open l_party_site_cur for
156 select decode(accept_standardized_flag, 'Y', a.CITY_STD, a.CITY), decode(accept_standardized_flag, 'Y', a.POSTAL_CODE_STD, a.POSTAL_CODE), a.STATE, decode(accept_standardized_flag, 'Y', a.COUNTRY_STD, a.COUNTRY), a.party_orig_system, a.party_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, b.party_id, b.party_site_id, b.party_action_flag, a.rowid, decode(accept_standardized_flag, 'Y', a.ADDRESS1_STD, a.ADDRESS1) || ' ' || decode(accept_standardized_flag, 'Y', a.ADDRESS2_STD, a.ADDRESS2) || ' ' || decode(accept_standardized_flag, 'Y', a.ADDRESS3_STD, a.ADDRESS3) || ' ' || decode(accept_standardized_flag, 'Y', a.ADDRESS4_STD, a.ADDRESS4) as address
157 from hz_imp_addresses_int a, hz_imp_addresses_sg b
158 where a.batch_id = p_batch_id
159 and b.action_flag = 'I'
160 and b.int_row_id = a.rowid
161 and a.party_orig_system_reference >= p_from_osr
162 and a.party_orig_system_reference <= p_to_osr
163 and b.batch_mode_flag = p_batch_mode_flag
164 and interface_status is null ;
165 LOOP
166 FETCH l_party_site_cur BULK COLLECT INTO
167 H_29E, H_30E, H_32E, H_36E, H_P_PARTY_OS, H_P_PARTY_OSR, H_P_PS_OS, H_P_PS_OSR, H_P_PARTY_ID, H_P_PARTY_SITE_ID, H_P_N_PARTY, H_P_ROW_ID, H_P_PS_ADD
168 LIMIT g_limit;
169
170 IF (l_party_site_cur%NOTFOUND) THEN
171 l_last_fetch := TRUE;
172 END IF;
173
174 IF H_P_PS_OS.COUNT = 0 AND l_last_fetch THEN
175 EXIT;
176 END IF;
177
178 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
179 ----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY_SITES LEVEL ---------
180 HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (36,H_36E(I) );
181 H_TX11(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_30E(I), NULL, 'POSTAL_CODE', 'PARTY_SITES', 'SEARCH' );
182 H_TX14(I) := HZ_TRANS_PKG.WRSTATE_EXACT(H_32E(I), NULL, 'STATE', 'PARTY_SITES', 'SEARCH' );
183 H_TX22(I) := HZ_TRANS_PKG.EXACT(H_36E(I), NULL, 'COUNTRY', 'PARTY_SITES');
184 H_TX9(I) := HZ_TRANS_PKG.EXACT(H_29E(I), NULL, 'CITY', 'PARTY_SITES');
185 H_PS_CUST_TX26(I) := HZ_TRANS_PKG.BASIC_WRADDR(H_P_PS_ADD(I), NULL, 'ADDRESS', 'PARTY_SITES', 'SEARCH' );
186 END LOOP;
187 SAVEPOINT pop_party_sites;
188 BEGIN
189 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
190 INSERT INTO HZ_SRCH_PSITES (
191 TX11, TX14, TX22, TX9, PARTY_SITE_ID, PARTY_ID, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID , TX26
192 ) VALUES (
193 H_TX11(I), H_TX14(I), H_TX22(I), H_TX9(I), H_P_PARTY_SITE_ID(I), H_P_PARTY_ID(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I), H_P_N_PARTY(I), P_BATCH_ID, H_P_ROW_ID(I) , H_PS_CUST_TX26(I)
194 );
195 EXCEPTION
196 WHEN OTHERS THEN
197 ROLLBACK to pop_party_sites;
198 RAISE;
199 END;
200
201 IF l_last_fetch THEN
202 FND_CONCURRENT.AF_Commit;
203 EXIT;
204 END IF;
205 FND_CONCURRENT.AF_Commit;
206
207 END LOOP;
208 CLOSE l_party_site_cur;
209 END pop_party_sites;
210 PROCEDURE pop_cp (
211 p_batch_id IN NUMBER,
212 p_from_osr IN VARCHAR2,
213 p_to_osr IN VARCHAR2,
214 p_batch_mode_flag IN VARCHAR2
215 ) IS
216
217 l_last_fetch BOOLEAN := FALSE;
218 l_cp_cur HZ_PARTY_STAGE.StageCurTyp;
219 BEGIN
220 open l_cp_cur for
221 select a.EMAIL_ADDRESS, a.URL, a.party_orig_system, a.party_orig_system_reference, a.cp_orig_system, a.cp_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, b.party_site_id, b.contact_point_id, b.party_id, b.party_action_flag, a.rowid, a.contact_point_type, decode(a.raw_phone_number, null, a.PHONE_COUNTRY_CODE||a.PHONE_AREA_CODE ||a.phone_number, a.raw_phone_number) as raw_phone_number
222 from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b --
223 where a.batch_id = p_batch_id
224 and b.action_flag = 'I'
225 and b.int_row_id = a.rowid
226 and b.party_orig_system_reference >= p_from_osr
227 and b.party_orig_system_reference <= p_to_osr
228 and b.batch_mode_flag = p_batch_mode_flag
229 and interface_status is null ;
230 LOOP
231 FETCH l_cp_cur BULK COLLECT INTO
232 H_48E, H_49E, H_P_PARTY_OS, H_P_PARTY_OSR, H_P_CP_OS, H_P_CP_OSR, H_P_PS_OS, H_P_PS_OSR, H_P_PARTY_SITE_ID, H_P_CONTACT_POINT_ID, H_P_PARTY_ID, H_P_N_PARTY, H_P_ROW_ID, H_P_CP_TYPE, H_P_CP_R_PH_NO
233 LIMIT g_limit;
234 IF l_cp_cur%NOTFOUND THEN
235 l_last_fetch := TRUE;
236 END IF;
237
238 IF H_P_CP_OSR.COUNT = 0 AND l_last_fetch THEN
239 EXIT;
240 END IF;
241
242 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
243 H_TX5(I) := HZ_TRANS_PKG.EXACT_EMAIL(H_48E(I), NULL, 'EMAIL_ADDRESS', 'CONTACT_POINTS');
244 H_TX7(I) := HZ_TRANS_PKG.EXACT_URL(H_49E(I), NULL, 'URL', 'CONTACT_POINTS');
245 H_CP_CUST_TX10(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_P_CP_R_PH_NO(I), NULL, 'RAW_PHONE_NUMBER', 'CONTACT_POINTS', 'SEARCH' );
246 H_CP_CUST_TX158(I) := HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(H_P_CP_R_PH_NO(I), NULL, 'RAW_PHONE_NUMBER', 'CONTACT_POINTS');
247 END LOOP;
248 SAVEPOINT POP_CP;
249 BEGIN
250 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
251 INSERT INTO HZ_SRCH_CPTS (
252 TX5, TX7, PARTY_SITE_ID, PARTY_ID, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, CONTACT_POINT_ID, CONTACT_PT_OS, CONTACT_PT_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID, CONTACT_POINT_TYPE , TX10, TX158
253 ) VALUES (
254 H_TX5(I), H_TX7(I), H_P_PARTY_SITE_ID(I), H_P_PARTY_ID(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I) , H_P_CONTACT_POINT_ID(I), H_P_CP_OS(I), H_P_CP_OSR(I), H_P_N_PARTY(I), P_BATCH_ID, H_P_ROW_ID(I), H_P_CP_TYPE(I) , H_CP_CUST_TX10(I), H_CP_CUST_TX158(I)
255 );
256 EXCEPTION
257 WHEN OTHERS THEN
258 ROLLBACK to POP_CP;
259 RAISE;
260 END;
261
262 IF l_last_fetch THEN
263 FND_CONCURRENT.AF_Commit;
264 EXIT;
265 END IF;
266 FND_CONCURRENT.AF_Commit;
267
268 END LOOP;
269 CLOSE l_cp_cur;
270 END pop_cp;
271 PROCEDURE get_contact_cur(
272 p_batch_id IN NUMBER,
273 p_from_osr IN VARCHAR2,
274 p_to_osr IN VARCHAR2,
275 p_batch_mode_flag IN VARCHAR2,
276 x_contact_cur IN OUT NOCOPY StageImpContactCurTyp
277 ) IS
278 is_using_allow_cust_attr VARCHAR2(1);
279 CURSOR c1 is select 'Y'
280 from hz_trans_attributes_vl
281 where entity_name = 'CONTACTS'
282 and attribute_name = 'CONTACT_NAME'
283 and attribute_id in (
284 select attribute_id
285 from hz_match_rule_primary b
286 where match_rule_id = 52
287 union
288 select attribute_id
289 from hz_match_rule_secondary b
290 where match_rule_id = 52 ) and rownum = 1;
291
292 BEGIN
293 OPEN c1;
294 LOOP
295 FETCH c1 INTO is_using_allow_cust_attr;
296 EXIT when c1%NOTFOUND;
297 END LOOP;
298 CLOSE c1;
299 IF (is_using_allow_cust_attr = 'Y') THEN
300 OPEN x_contact_cur FOR
301 select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, b.party_action_flag, a.rowid, b.obj_id, c.person_first_name || ' ' || c.person_last_name as person_name
302 from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b, HZ_IMP_PARTIES_INT c
303 where a.batch_id = p_batch_id
304 and b.action_flag = 'I'
305 and b.int_row_id = a.rowid
306 and a.sub_orig_system_reference >= p_from_osr
307 and a.sub_orig_system_reference <= p_to_osr
308 and a.sub_orig_system = c.party_orig_system
309 and a.batch_id = c.batch_id
310 and b.sub_id = c.party_id
311 and b.batch_mode_flag = p_batch_mode_flag
312 and a.interface_status is null
313 union all
314 select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, b.party_action_flag, a.rowid, b.obj_id, c.party_name as person_name
315 from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b, hz_parties c
316 where a.batch_id = p_batch_id
317 and b.action_flag = 'I'
318 and b.int_row_id = a.rowid
319 and a.sub_orig_system_reference >= p_from_osr
320 and a.sub_orig_system_reference <= p_to_osr
321 and b.sub_id = c.party_id
322 and b.batch_mode_flag = p_batch_mode_flag
323 and a.interface_status is null
324 ;
325 ELSE
326 OPEN x_contact_cur FOR
327 select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, b.party_action_flag, a.rowid, b.obj_id
328 from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b
329 where a.batch_id = p_batch_id
330 and b.action_flag = 'I'
331 and b.int_row_id = a.rowid
332 and a.sub_orig_system_reference >= p_from_osr
333 and a.sub_orig_system_reference <= p_to_osr
334 and b.batch_mode_flag = p_batch_mode_flag
335 and a.interface_status is null ;
336 END IF;
337 END get_contact_cur;
338
339 PROCEDURE pop_contacts (
340 p_batch_id IN NUMBER,
341 p_from_osr IN VARCHAR2,
342 p_to_osr IN VARCHAR2,
343 p_batch_mode_flag IN VARCHAR2
344 ) IS
345 l_last_fetch BOOLEAN := FALSE;
346 l_contact_cur StageImpContactCurTyp;
347
348 BEGIN
349 get_contact_cur(p_batch_id, p_from_osr, p_to_osr, p_batch_mode_flag, l_contact_cur );
350 LOOP
351 FETCH l_contact_cur BULK COLLECT INTO
352 H_43E, H_P_SUBJECT_OS, H_P_SUBJECT_OSR, H_P_CONTACT_OS, H_P_CONTACT_OSR, H_P_N_PARTY, H_P_ROW_ID, H_CT_OBJ_ID , H_CT_NAME
353 LIMIT g_limit;
354
355 IF l_contact_cur%NOTFOUND THEN
356 l_last_fetch:=TRUE;
357 END IF;
358 IF H_P_CONTACT_OS.COUNT=0 AND l_last_fetch THEN
359 EXIT;
360 END IF;
361
362 FOR I in H_P_CONTACT_OSR.FIRST..H_P_CONTACT_OSR.LAST LOOP
363 H_TX22(I) := HZ_TRANS_PKG.EXACT(H_43E(I), NULL, 'JOB_TITLE', 'CONTACTS');
364 H_CT_CUST_TX2(I) := HZ_TRANS_PKG.EXACT_PADDED(H_CT_NAME(I), NULL, 'CONTACT_NAME', 'CONTACTS');
365 H_CT_CUST_TX23(I) := HZ_TRANS_PKG.BASIC_WRPERSON(H_CT_NAME(I), NULL, 'CONTACT_NAME', 'CONTACTS', 'SEARCH' );
366 END LOOP;
367 SAVEPOINT pop_contacts;
368 BEGIN
369 FORALL I in H_P_CONTACT_OSR.FIRST..H_P_CONTACT_OSR.LAST
370 INSERT INTO HZ_SRCH_CONTACTS (
371 TX22, PARTY_OS, PARTY_OSR, CONTACT_OS, CONTACT_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID, PARTY_ID , TX2, TX23
372 ) VALUES (
373 H_TX22(I), H_P_SUBJECT_OS(I), H_P_SUBJECT_OSR(I), H_P_CONTACT_OS(I), H_P_CONTACT_OSR(I), H_P_N_PARTY(I), P_BATCH_ID, H_P_ROW_ID(I), H_CT_OBJ_ID(I) , H_CT_CUST_TX2(I), H_CT_CUST_TX23(I)
374 );
375 EXCEPTION
376 WHEN OTHERS THEN
377 ROLLBACK to pop_contacts;
378 RAISE;
379 END;
380
381 IF l_last_fetch THEN
382 FND_CONCURRENT.AF_Commit;
383 EXIT;
384 END IF;
385 FND_CONCURRENT.AF_Commit;
386
387 END LOOP;
388 CLOSE l_contact_cur ;
389 END pop_contacts;
390
391 PROCEDURE pop_parties_int (
392 p_batch_id IN NUMBER,
393 p_from_osr IN VARCHAR2,
394 p_to_osr IN VARCHAR2
395 ) IS
396 l_last_fetch BOOLEAN := FALSE;
397 p_party_cur HZ_PARTY_STAGE.StageCurTyp;
398
399 count NUMBER := 0;
400 l_os VARCHAR2(30);
401 BEGIN
402 l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id);
403 open p_party_cur FOR
404 select decode(a.party_type, 'ORGANIZATION', a.organization_name, 'PERSON', a.person_first_name || ' ' || a.person_last_name) as PARTY_NAME , a.PARTY_TYPE, a.DUNS_NUMBER_C, a.JGZZ_FISCAL_CODE, a.party_orig_system, a.party_orig_system_reference, a.rowid, a.party_type , a.party_id
405 from hz_imp_parties_int a
406 where a.batch_id = p_batch_id
407 and a.party_orig_system_reference >= p_from_osr
408 and a.party_orig_system_reference <= p_to_osr
409 and a.party_orig_system = l_os;
410 LOOP
411 FETCH p_party_cur BULK COLLECT INTO
412 H_8E, H_14E, H_16E, H_19E, H_P_PARTY_OS , H_P_PARTY_OSR, H_P_ROW_ID, H_P_P_TYPE , H_P_PARTY_ID
413 LIMIT g_limit;
414 IF p_party_cur%NOTFOUND THEN
415 l_last_fetch:=TRUE;
416 END IF;
417
418 IF H_P_PARTY_OS.COUNT=0 AND l_last_fetch THEN
419 EXIT;
420 END IF;
421
422 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
423 HZ_TRANS_PKG.set_party_type(H_P_P_TYPE(I));
424 H_TX2(I) := HZ_TRANS_PKG.EXACT_PADDED(H_8E(I), NULL, 'PARTY_NAME', 'PARTY');
425 H_TX36(I) := HZ_TRANS_PKG.EXACT(H_14E(I), NULL, 'PARTY_TYPE', 'PARTY');
426 H_TX41(I) := HZ_TRANS_PKG.EXACT(H_16E(I), NULL, 'DUNS_NUMBER_C', 'PARTY');
427 H_TX45(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_19E(I), NULL, 'JGZZ_FISCAL_CODE', 'PARTY', 'SEARCH' );
428 H_TX59(I) := HZ_TRANS_PKG.BASIC_WRNAMES(H_8E(I), NULL, 'PARTY_NAME', 'PARTY', 'SEARCH' );
429 END LOOP;
430 SAVEPOINT pop_parties_int;
431 BEGIN
432 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
433 INSERT INTO HZ_SRCH_PARTIES (
434 TX2, TX36, TX41, TX45, TX59, PARTY_OS, PARTY_OSR, BATCH_ID, INT_ROW_ID , PARTY_ID
435 ) VALUES (
436 H_TX2(I), H_TX36(I), H_TX41(I), H_TX45(I), H_TX59(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), P_BATCH_ID, H_P_ROW_ID(I) , H_P_PARTY_ID(I)
437 );
438 EXCEPTION
439 WHEN OTHERS THEN
440 ROLLBACK to pop_parties_int;
441 -- dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
442 RAISE;
443 END ;
444 IF l_last_fetch THEN
445 FND_CONCURRENT.AF_Commit;
446 EXIT;
447 END IF;
448 FND_CONCURRENT.AF_Commit;
449
450 END LOOP;
451 CLOSE p_party_cur;
452 END pop_parties_int;
453
454 PROCEDURE pop_party_sites_int (
455 p_batch_id IN NUMBER,
456 p_from_osr IN VARCHAR2,
457 p_to_osr IN VARCHAR2
458 ) IS
459 l_last_fetch BOOLEAN := FALSE;
460 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
461
462 l_os VARCHAR2(30);
463 BEGIN
464 l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id);
465 open l_party_site_cur for
466 select a.CITY, a.POSTAL_CODE, a.STATE, a.COUNTRY, a.party_orig_system, a.party_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, a.rowid, a.address1 || ' ' || a.address2 || ' ' || a.address3 || ' ' || a.address4 as address , a.party_id
467 from hz_imp_addresses_int a
468 where a.batch_id = p_batch_id
469 and a.party_orig_system_reference >= p_from_osr
470 and a.party_orig_system_reference <= p_to_osr
471 and a.party_orig_system = l_os;
472 LOOP
473 FETCH l_party_site_cur BULK COLLECT INTO
474 H_29E, H_30E, H_32E, H_36E, H_P_PARTY_OS, H_P_PARTY_OSR, H_P_PS_OS, H_P_PS_OSR, H_P_ROW_ID, H_P_PS_ADD , H_P_PARTY_ID
475 LIMIT g_limit;
476
477 IF l_party_site_cur%NOTFOUND THEN
478 l_last_fetch:=TRUE;
479 END IF;
480 IF H_P_PS_OS.COUNT=0 AND l_last_fetch THEN
481 EXIT;
482 END IF;
483
484 FOR I in H_P_PS_OSR.FIRST..H_P_PS_OSR.LAST LOOP
485 ----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY_SITES LEVEL ---------
486 HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (36,H_36E(I) );
487 H_TX11(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_30E(I), NULL, 'POSTAL_CODE', 'PARTY_SITES', 'SEARCH' );
488 H_TX14(I) := HZ_TRANS_PKG.WRSTATE_EXACT(H_32E(I), NULL, 'STATE', 'PARTY_SITES', 'SEARCH' );
489 H_TX22(I) := HZ_TRANS_PKG.EXACT(H_36E(I), NULL, 'COUNTRY', 'PARTY_SITES');
490 H_TX9(I) := HZ_TRANS_PKG.EXACT(H_29E(I), NULL, 'CITY', 'PARTY_SITES');
491 H_PS_CUST_TX26(I) := HZ_TRANS_PKG.BASIC_WRADDR(H_P_PS_ADD(I), NULL, 'ADDRESS', 'PARTY_SITES', 'SEARCH' );
492 END LOOP;
493 SAVEPOINT pop_party_sites_int;
494 BEGIN
495 FORALL I in H_P_PS_OSR.FIRST..H_P_PS_OSR.LAST
496 INSERT INTO HZ_SRCH_PSITES (
497 TX11, TX14, TX22, TX9, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, BATCH_ID, INT_ROW_ID, TX26 , PARTY_ID
498 ) VALUES (
499 H_TX11(I), H_TX14(I), H_TX22(I), H_TX9(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I), P_BATCH_ID, H_P_ROW_ID(I), H_PS_CUST_TX26(I) , H_P_PARTY_ID(I)
500 );
501 EXCEPTION
502 WHEN OTHERS THEN
503 ROLLBACK to pop_party_sites_int;
504 RAISE;
505 END;
506
507 IF l_last_fetch THEN
508 FND_CONCURRENT.AF_Commit;
509 EXIT;
510 END IF;
511 FND_CONCURRENT.AF_Commit;
512
513 END LOOP;
514 CLOSE l_party_site_cur;
515 END pop_party_sites_int;
516
517 PROCEDURE pop_cp_int (
518 p_batch_id IN NUMBER,
519 p_from_osr IN VARCHAR2,
520 p_to_osr IN VARCHAR2
521 ) IS
522 l_last_fetch BOOLEAN := FALSE;
523 l_cp_cur HZ_PARTY_STAGE.StageCurTyp;
524 l_os VARCHAR2(30);
525 BEGIN
526 l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id);
527 open l_cp_cur for
528 select a.EMAIL_ADDRESS, a.URL, a.party_orig_system, a.party_orig_system_reference, a.cp_orig_system, a.cp_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, a.rowid, a.contact_point_type,decode(a.raw_phone_number, null, a.PHONE_COUNTRY_CODE||a.PHONE_AREA_CODE ||a.phone_number,a.raw_phone_number) as raw_phone_number , a.party_id
529 from HZ_IMP_CONTACTPTS_INT a
530 where a.batch_id = p_batch_id
531 and a.party_orig_system_reference >= p_from_osr
532 and a.party_orig_system_reference <= p_to_osr
533 and a.party_orig_system = l_os;
534
535 LOOP
536 FETCH l_cp_cur BULK COLLECT INTO
537 H_48E, H_49E, H_P_PARTY_OS, H_P_PARTY_OSR, H_P_CP_OS, H_P_CP_OSR, H_P_PS_OS, H_P_PS_OSR, H_P_ROW_ID, H_P_CP_TYPE, H_P_CP_R_PH_NO , H_P_PARTY_ID
538 LIMIT g_limit;
539 IF l_cp_cur%NOTFOUND THEN
540 l_last_fetch:=TRUE;
541 END IF;
542
543 IF H_P_CP_OS.COUNT=0 AND l_last_fetch THEN
544 EXIT;
545 END IF;
546
547 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
548 H_TX5(I) := HZ_TRANS_PKG.EXACT_EMAIL(H_48E(I), NULL, 'EMAIL_ADDRESS', 'CONTACT_POINTS');
549 H_TX7(I) := HZ_TRANS_PKG.EXACT_URL(H_49E(I), NULL, 'URL', 'CONTACT_POINTS');
550 H_CP_CUST_TX10(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_P_CP_R_PH_NO(I), NULL, 'RAW_PHONE_NUMBER', 'CONTACT_POINTS', 'SEARCH' );
551 H_CP_CUST_TX158(I) := HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(H_P_CP_R_PH_NO(I), NULL, 'RAW_PHONE_NUMBER', 'CONTACT_POINTS');
552 END LOOP;
553 SAVEPOINT pop_cp_int;
554 BEGIN
555 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
556 INSERT INTO HZ_SRCH_CPTS (
557 TX5, TX7, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, CONTACT_PT_OS, CONTACT_PT_OSR, BATCH_ID, INT_ROW_ID, CONTACT_POINT_TYPE, TX10, TX158 , PARTY_ID
558 ) VALUES (
559 H_TX5(I), H_TX7(I), H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I), H_P_CP_OS(I), H_P_CP_OSR(I), P_BATCH_ID, H_P_ROW_ID(I), H_P_CP_TYPE(I), H_CP_CUST_TX10(I), H_CP_CUST_TX158(I) ,H_P_PARTY_ID(I)
560 );
561 EXCEPTION
562 WHEN OTHERS THEN
563 ROLLBACK to pop_cp_int;
564 RAISE;
565 END;
566
567 IF l_last_fetch THEN
568 FND_CONCURRENT.AF_Commit;
569 EXIT;
570 END IF;
571 FND_CONCURRENT.AF_Commit;
572
573 END LOOP;
574 CLOSE l_cp_cur ;
575
576 END pop_cp_int;
577 PROCEDURE get_contact_cur_int(
578 p_batch_id IN NUMBER,
579 p_from_osr IN VARCHAR2,
580 p_to_osr IN VARCHAR2,
581 x_contact_cur IN OUT NOCOPY StageImpContactCurTyp
582 ) IS
583 is_using_allow_cust_attr VARCHAR2(1);
584 CURSOR c1 is select 'Y'
585 from hz_trans_attributes_vl
586 where entity_name = 'CONTACTS'
587 and attribute_name = 'CONTACT_NAME'
588 and attribute_id in (
589 select attribute_id
590 from hz_match_rule_primary b
591 where match_rule_id = 52
592 union
593 select attribute_id
594 from hz_match_rule_secondary b
595 where match_rule_id = 52 ) and rownum = 1;
596
597 l_os VARCHAR2(30);
598 BEGIN --
599 l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id);
600 OPEN c1;
601 LOOP
602 FETCH c1 into is_using_allow_cust_attr;
603 EXIT when c1%NOTFOUND;
604 END LOOP;
605 IF (is_using_allow_cust_attr = 'Y') THEN
606 OPEN x_contact_cur FOR
607 select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, a.rowid, c.person_first_name || ' ' || c.person_last_name as person_name
608 from HZ_IMP_CONTACTS_INT a, HZ_IMP_PARTIES_INT c
609 where a.batch_id = p_batch_id
610 and a.sub_orig_system_reference >= p_from_osr
611 and a.sub_orig_system_reference <= p_to_osr
612 and a.sub_orig_system_reference = c.party_orig_system_reference
613 and a.sub_orig_system = c.party_orig_system
614 and a.batch_id = c.batch_id
615 and a.sub_orig_system = l_os;
616 ELSE
617 OPEN x_contact_cur FOR
618 select a.JOB_TITLE, a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, a.rowid, null person_name
619 from HZ_IMP_CONTACTS_INT a
620 where a.batch_id = p_batch_id
621 and a.sub_orig_system_reference >= p_from_osr
622 and a.sub_orig_system_reference <= p_to_osr
623 and a.sub_orig_system = l_os;
624 END IF;
625 END get_contact_cur_int;
626
627 PROCEDURE pop_contacts_int (
628 p_batch_id IN NUMBER,
629 p_from_osr IN VARCHAR2,
630 p_to_osr IN VARCHAR2
631 ) IS
632 l_last_fetch BOOLEAN := FALSE;
633 l_contact_cur StageImpContactCurTyp;
634
635 BEGIN
636 get_contact_cur_int(p_batch_id, p_from_osr, p_to_osr, l_contact_cur );
637 LOOP
638 FETCH l_contact_cur BULK COLLECT INTO
639 H_43E, H_P_SUBJECT_OS, H_P_SUBJECT_OSR, H_P_CONTACT_OS, H_P_CONTACT_OSR, H_P_ROW_ID , H_CT_NAME
640 LIMIT g_limit;
641
642 IF l_contact_cur%NOTFOUND THEN
643 l_last_fetch:=TRUE;
644 END IF;
645 IF H_P_CONTACT_OS.COUNT=0 AND l_last_fetch THEN
646 EXIT;
647 END IF;
648 FOR I in H_P_CONTACT_OS.FIRST..H_P_CONTACT_OS.LAST LOOP
649 H_TX22(I) := HZ_TRANS_PKG.EXACT(H_43E(I), NULL, 'JOB_TITLE', 'CONTACTS');
650 H_CT_CUST_TX2(I) := HZ_TRANS_PKG.EXACT_PADDED(H_CT_NAME(I), NULL, 'CONTACT_NAME', 'CONTACTS');
651 H_CT_CUST_TX23(I) := HZ_TRANS_PKG.BASIC_WRPERSON(H_CT_NAME(I), NULL, 'CONTACT_NAME', 'CONTACTS', 'SEARCH' );
652 END LOOP;
653 SAVEPOINT pop_contacts_int;
654 BEGIN
655 FORALL I in H_P_CONTACT_OS.FIRST..H_P_CONTACT_OS.LAST
656 INSERT INTO HZ_SRCH_CONTACTS (
657 TX22, PARTY_OS, PARTY_OSR, CONTACT_OS, CONTACT_OSR, BATCH_ID, INT_ROW_ID , TX2, TX23
658 ) VALUES (
659 H_TX22(I), H_P_SUBJECT_OS(I), H_P_SUBJECT_OSR(I), H_P_CONTACT_OS(I), H_P_CONTACT_OSR(I), P_BATCH_ID, H_P_ROW_ID(I) , H_CT_CUST_TX2(I), H_CT_CUST_TX23(I)
660 );
661 EXCEPTION
662 WHEN OTHERS THEN
663 ROLLBACK to pop_contacts_int;
664 RAISE;
665 END;
666
667 IF l_last_fetch THEN
668 FND_CONCURRENT.AF_Commit;
669 EXIT;
670 END IF;
671 FND_CONCURRENT.AF_Commit;
672
673 END LOOP;
674 CLOSE l_contact_cur ;
675 END pop_contacts_int;
676
677
678
679
680 ---------------------------------------------------------------
681 -------------------- TCA JOIN BEGINS --------------------------
682 ---------------------------------------------------------------
683 PROCEDURE tca_join_entities(trap_explosion in varchar2, rows_in_chunk in number, inserted_duplicates out number)
684 IS
685 x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
686 x_insert_threshold number := 20;
687 l_party_limit NUMBER := 50000;
688 l_detail_limit NUMBER := 100000;
689 BEGIN
690 FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
691 insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score)
692 select f, t, least(f,t), greatest(f,t), sum(score) score from (
693 select /*+ ORDERED */ s1.party_id f, s2.party_id t,
694 -------PARTY ENTITY: SCORING SECTION ---------
695 decode(instrb(s2.TX2,s1.TX2),1,80,
696 decode(instrb(s2.TX59,s1.TX59),1,72,
697 0
698 )
699 )
700 +
701 decode(instrb(s2.TX41,s1.TX41),1,200,
702 0
703 )
704 +
705 decode(instrb(s2.TX45,s1.TX45),1,200,
706 0
707 )
708 score
709 from hz_dup_worker_chunk_gt p, HZ_STAGED_PARTIES s1, HZ_STAGED_PARTIES s2
710 where p.party_id = s1.party_id and s1.party_id<>s2.party_id
711 and nvl(s1.status,'A') = 'A' and nvl(s2.status,'A') = 'A'
712 and 1=decode(trap_explosion,'N',1,decode(rownum,l_party_limit,to_number('A'),1))
713 and (
714 -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES---------
715 -- do an or between all the transformations of an attribute --
716 (
717 (s1.TX45 is not null and s2.TX45 like s1.TX45 || decode(sign(lengthb(s1.TX45)-3),1,'%',''))
718 )
719 or
720 -- do an or between all the transformations of an attribute --
721 (
722 (s1.TX59 is not null and s2.TX59 like s1.TX59 || decode(sign(lengthb(s1.TX59)-3),1,'%',''))
723 )
724 or
725 -- do an or between all the transformations of an attribute --
726 (
727 (s1.TX41 is not null and s2.TX41 like s1.TX41 || decode(sign(lengthb(s1.TX41)-3),1,'%',''))
728 )
729 )
730 union all
731 select f, t, max(score) score from (
732 select /*+ ORDERED */ s1.party_id f, s2.party_id t,
733 -------CONTACT_POINTS ENTITY: SCORING SECTION ---------
734 decode(instrb(s2.TX5,s1.TX5),1,60,
735 0
736 )
737 +
738 decode(instrb(s2.TX7,s1.TX7),1,20,
739 0
740 )
741 +
742 decode(instrb(s2.TX10,s1.TX10),1,70,
743 decode(instrb(s2.TX158,s1.TX158),1,70,
744 0
745 )
746 )
747 score
748 from hz_dup_worker_chunk_gt p, HZ_STAGED_CONTACT_POINTS s1, HZ_STAGED_CONTACT_POINTS s2
749 where p.party_id = s1.party_id and s1.party_id<>s2.party_id
750 and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,'A') = 'A')
751 and 1=decode(trap_explosion,'N',1,decode(rownum,l_detail_limit,to_number('A'),1))
752 and (
753 -------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES---------
754 -- do an or between all the transformations of an attribute --
755 (
756 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
757 )
758 or
759 -- do an or between all the transformations of an attribute --
760 (
761 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
762 )
763 or
764 -- do an or between all the transformations of an attribute --
765 (
766 (s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-3),1,'%',''))
767 )
768 )
769 ) group by f, t
770 )
771 ------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
772 where EXISTS (
773 SELECT 1 FROM HZ_STAGED_PARTIES p1, HZ_STAGED_PARTIES p2
774 WHERE p1.party_id = f and p2.party_id = t
775 and
776 -- do an or between all the transformations of an attribute --
777 (
778 ((p1.TX36 is null and p2.TX36 is null) or p2.TX36 = p1.TX36)
779 )
780 and
781 -- do an or between all the transformations of an attribute --
782 (
783 ((p1.TX46 is null and p2.TX46 is null) or p2.TX46 = p1.TX46)
784 )
785 )
786 group by f, t
787 having sum(score) >= x_insert_threshold
788 ;
789 inserted_duplicates := (SQL%ROWCOUNT);
790 FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
791 FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
792 FND_CONCURRENT.AF_Commit;
793
794
795 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
796 FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
797 FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
798 open x_ent_cur for
799 select f, t, max(score) score from (
800 select /*+ ORDERED */ s1.party_id f, s2.party_id t,
801 decode(instrb(s2.TX26,s1.TX26),1,100,
802 0
803 )
804 +
805 decode(instrb(s2.TX9,s1.TX9),1,15,
806 0
807 )
808 +
809 decode(instrb(s2.TX14,s1.TX14),1,5,
810 0
811 )
812 +
813 decode(instrb(s2.TX22,s1.TX22),1,5,
814 0
815 )
816 score
817 from hz_dup_worker_chunk_gt p, hz_dup_results h1, HZ_STAGED_PARTY_SITES s1, HZ_STAGED_PARTY_SITES s2
818 where p.party_id=h1.fid and s1.party_id = h1.fid and s2.party_id = h1.tid
819 and (
820 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
821 -- do an or between all the transformations of an attribute --
822 (
823 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
824 )
825 )
826 ------------ FILTER ATTRIBUTES SECTION ------------------------
827 and
828 -- do an or between all the transformations of an attribute --
829 (
830 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
831 )
832 ) group by f,t ;
833 HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);
834 close x_ent_cur;
835 FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
836 FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
837 FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
838 FND_CONCURRENT.AF_Commit;
839
840
841 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
842 FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of CONTACTS');
843 FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
844 open x_ent_cur for
845 select f, t, max(score) score from (
846 select /*+ ORDERED */ s1.party_id f, s2.party_id t,
847 decode(instrb(s2.TX2,s1.TX2),1,20,
848 decode(instrb(s2.TX23,s1.TX23),1,18,
849 0
850 )
851 )
852 +
853 decode(instrb(s2.TX22,s1.TX22),1,10,
854 0
855 )
856 score
857 from hz_dup_worker_chunk_gt p, hz_dup_results h1, HZ_STAGED_CONTACTS s1, HZ_STAGED_CONTACTS s2
858 where p.party_id=h1.fid and s1.party_id = h1.fid and s2.party_id = h1.tid
859 and (
860 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
861 -- do an or between all the transformations of an attribute --
862 (
863 (s1.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
864 )
865 )
866 ) group by f,t ;
867 HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);
868 close x_ent_cur;
869 FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
870 FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
871 FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of CONTACTS');
872 FND_CONCURRENT.AF_Commit;
873
874
875 ---------- exception block ---------------
876 EXCEPTION
877 WHEN OTHERS THEN
878 IF sqlcode=-1722
879 THEN
880 inserted_duplicates := -1;
881 ELSE
882 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
883 FND_MESSAGE.SET_TOKEN('PROC','HZ_IMP_MATCH_RULE_52.tca_join_entities');
884 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
885 FND_MSG_PUB.ADD;
886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
887 END IF;
888 END tca_join_entities;
889
890
891
892
893 ---------------------------------------------------------------
894 -------------------- INTERFACE TCA JOIN BEGINS --------------------------
895 ---------------------------------------------------------------
896 PROCEDURE interface_tca_join_entities( p_batch_id in number, from_osr in varchar2, to_osr in varchar2,
897 p_threshold in number, p_auto_merge_threshold in number)
898 IS
899 x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
900 x_insert_threshold number := 20;
901 BEGIN
902 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
903 FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
904 FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
905 insert into hz_imp_dup_parties(party_id,dup_party_id, score, party_osr, party_os, batch_id, auto_merge_flag
906 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
907 select f, t, sum(score) sc, party_osr, party_os, p_batch_id, 'N'
908 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
909 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
910 from (
911 ------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
912 select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
913 -------PARTY ENTITY: SCORING SECTION ---------
914 decode(instrb(s2.TX2,s1.TX2),1,80,
915 decode(instrb(s2.TX59,s1.TX59),1,72,
916 0
917 )
918 )
919 +
920 decode(instrb(s2.TX41,s1.TX41),1,200,
921 0
922 )
923 +
924 decode(instrb(s2.TX45,s1.TX45),1,200,
925 0
926 )
927 score , s1.party_osr party_osr, s1.party_os party_os
928 from HZ_SRCH_PARTIES s1, HZ_STAGED_PARTIES s2
929 where s1.party_id is not null and s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
930 and nvl(s2.status,'A') = 'A'
931 and (
932 -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
933 -- do an or between all the transformations of an attribute --
934 (
935 (s1.TX45 is not null and s2.TX45 like s1.TX45 || decode(sign(lengthb(s1.TX45)-3),1,'%',''))
936 )
937 or
938 -- do an or between all the transformations of an attribute --
939 (
940 (s1.TX59 is not null and s2.TX59 like s1.TX59 || decode(sign(lengthb(s1.TX59)-3),1,'%',''))
941 )
942 or
943 -- do an or between all the transformations of an attribute --
944 (
945 (s1.TX41 is not null and s2.TX41 like s1.TX41 || decode(sign(lengthb(s1.TX41)-3),1,'%',''))
946 )
947 )
948 union all
949 select f, t, max(score) score, party_osr, party_os from (
950 select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
951 -------CONTACT_POINTS ENTITY: SCORING SECTION ---------
952 decode(instrb(s2.TX5,s1.TX5),1,60,
953 0
954 )
955 +
956 decode(instrb(s2.TX7,s1.TX7),1,20,
957 0
958 )
959 +
960 decode(instrb(s2.TX10,s1.TX10),1,70,
961 decode(instrb(s2.TX158,s1.TX158),1,70,
962 0
963 )
964 )
965 score , s1.party_osr party_osr, s1.party_os party_os
966 from HZ_SRCH_CPTS s1, HZ_STAGED_CONTACT_POINTS s2
967 where s1.party_id is not null and s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = 'I'
968 and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,'A') = 'A')
969 and (
970 -------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
971 -- do an or between all the transformations of an attribute --
972 (
973 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
974 )
975 or
976 -- do an or between all the transformations of an attribute --
977 (
978 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
979 )
980 or
981 -- do an or between all the transformations of an attribute --
982 (
983 (s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-3),1,'%',''))
984 )
985 )
986 )
987 group by f, t, party_osr, party_os
988 )
989 ------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
990 where EXISTS (
991 SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_STAGED_PARTIES p2
992 WHERE p1.batch_id = p_batch_id and p1.party_osr = party_osr and p1.party_os = party_os
993 and p2.party_id = t
994 and
995 -- do an or between all the transformations of an attribute --
996 (
997 ((p1.TX36 is null and p2.TX36 is null) or p2.TX36 = p1.TX36 || ' ' )
998 )
999 )
1000 group by f, t, party_osr, party_os
1001 having sum(score) >= x_insert_threshold
1002 ;
1003 FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
1004 FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1005
1006
1007 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1008 FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
1009 FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
1010 open x_ent_cur for
1011 select f,t,max(score) from (
1012 select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
1013 decode(instrb(s2.TX26,s1.TX26),1,100,
1014 0
1015 )
1016 +
1017 decode(instrb(s2.TX9,s1.TX9),1,15,
1018 0
1019 )
1020 +
1021 decode(instrb(s2.TX14,s1.TX14),1,5,
1022 0
1023 )
1024 +
1025 decode(instrb(s2.TX22,s1.TX22),1,5,
1026 0
1027 )
1028 score
1029 from hz_imp_dup_parties h1, HZ_SRCH_PSITES s1, HZ_STAGED_PARTY_SITES s2
1030 where h1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
1031 and s1.batch_id = h1.batch_id and s1.party_osr = h1.party_osr and s1.party_os = h1.party_os and s2.party_id = h1.dup_party_id
1032 and (
1033 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1034 -- do an or between all the transformations of an attribute --
1035 (
1036 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
1037 )
1038 )
1039 ------------ FILTER ATTRIBUTES SECTION ------------------------
1040 and
1041 -- do an or between all the transformations of an attribute --
1042 (
1043 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11 || ' ' )
1044 )
1045 ) group by f,t ;
1046 HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);
1047 close x_ent_cur;
1048 FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1049 FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1050 FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
1051
1052
1053 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1054 FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of CONTACTS');
1055 FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
1056 open x_ent_cur for
1057 select f,t,max(score) from (
1058 select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
1059 decode(instrb(s2.TX2,s1.TX2),1,20,
1060 decode(instrb(s2.TX23,s1.TX23),1,18,
1061 0
1062 )
1063 )
1064 +
1065 decode(instrb(s2.TX22,s1.TX22),1,10,
1066 0
1067 )
1068 score
1069 from hz_imp_dup_parties h1, HZ_SRCH_CONTACTS s1, HZ_STAGED_CONTACTS s2
1070 where h1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
1071 and s1.batch_id = h1.batch_id and s1.party_osr = h1.party_osr and s1.party_os = h1.party_os and s2.party_id = h1.dup_party_id
1072 and (
1073 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1074 -- do an or between all the transformations of an attribute --
1075 (
1076 (s1.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
1077 )
1078 )
1079 ) group by f,t ;
1080 HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);
1081 close x_ent_cur;
1082 FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1083 FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1084 FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of CONTACTS');
1085
1086 --------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------
1087
1088 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1089 FND_FILE.put_line(FND_FILE.log,'DELETE ON THRESHOLD AND INDIRECT TRANSITIVITY ');
1090 FND_FILE.put_line(FND_FILE.log,'Begin time to delete '||to_char(sysdate,'hh24:mi:ss'));
1091
1092 delete from hz_imp_dup_parties a
1093 where (a.party_osr >= from_osr and a.party_osr <= to_osr
1094 and a.batch_id = p_batch_id)
1095 and (
1096 a.score < p_threshold
1097 or
1098 -- delete the party id whose duplicate is a bigger number, when scores are same
1099 exists
1100 (Select 1 from hz_imp_dup_parties b
1101 where b.batch_id=p_batch_id and a.party_id=b.party_id and a.dup_party_id > b.dup_party_id and a.score = b.score)
1102 or
1103 -- delete the party id with least score, if scores are different
1104 exists
1105 (Select 1 from hz_imp_dup_parties b
1106 where b.batch_id=p_batch_id and a.party_id=b.party_id and a.score < b.score)
1107 );
1108
1109 FND_FILE.put_line(FND_FILE.log,'Number of records deleted from hz_imp_dup_parties '||SQL%ROWCOUNT);
1110 FND_FILE.put_line(FND_FILE.log,'End time to delete '||to_char(sysdate,'hh24:mi:ss'));
1111 --------UPDATE AUTO MERGE FLAG --------------
1112 update hz_imp_dup_parties a
1113 set a.auto_merge_flag = 'Y'
1114 where a.score >= p_auto_merge_threshold
1115 and a.party_osr >= from_osr and a.party_osr <= to_osr
1116 and a.batch_id = p_batch_id ;
1117 --------UPDATE DQM ACTION FLAG IN INTERFACE/STAGING TABLES --------------
1118
1119 open x_ent_cur for
1120 select a.party_osr, a.party_os, a.auto_merge_flag
1121 from hz_imp_dup_parties a
1122 where a.batch_id = p_batch_id
1123 and a.party_osr between from_osr and to_osr ;
1124 HZ_DQM_DUP_ID_PKG.update_party_dqm_action_flag(p_batch_id, x_ent_cur);
1125 ----------------------PARTY LEVEL DUPLICATE IDENTIFICATION ENDS --------------------
1126
1127
1128 -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1129 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1130 FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
1131 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1132 insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id
1133 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1134 select /*+ USE_CONCAT */ s1.party_id f,
1135 decode(instrb(s2.TX5,s1.TX5),1,60,
1136 0
1137 )
1138 +
1139 decode(instrb(s2.TX7,s1.TX7),1,20,
1140 0
1141 )
1142 +
1143 decode(instrb(s2.TX10,s1.TX10),1,70,
1144 decode(instrb(s2.TX158,s1.TX158),1,70,
1145 0
1146 )
1147 )
1148 score , s1.party_osr, s1.party_os, p_batch_id,'CONTACT_POINTS', s1.CONTACT_POINT_ID, s1.CONTACT_PT_OSR, s1.CONTACT_PT_OS,
1149 s2.CONTACT_POINT_ID
1150 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
1151 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
1152 from HZ_SRCH_CPTS s1, HZ_STAGED_CONTACT_POINTS s2
1153 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = 'U'
1154 and s1.party_id = s2.party_id
1155 and (
1156 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1157 -- do an or between all the transformations of an attribute --
1158 (
1159 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
1160 )
1161 or
1162 -- do an or between all the transformations of an attribute --
1163 (
1164 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
1165 )
1166 or
1167 -- do an or between all the transformations of an attribute --
1168 (
1169 (s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-3),1,'%',''))
1170 )
1171 )
1172 ;
1173
1174
1175 --------UPDATE DQM ACTION FLAG IN CONTACT_POINTS INTERFACE/STAGING TABLES --------------
1176 open x_ent_cur for
1177 select distinct a.record_osr, a.record_os
1178 from hz_imp_dup_details a
1179 where a.batch_id = p_batch_id
1180 and a.party_osr between from_osr and to_osr and a.entity ='CONTACT_POINTS';
1181 HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('CONTACT_POINTS',p_batch_id, x_ent_cur);
1182 -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
1183 FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
1184 FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1185 FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1186
1187
1188
1189 -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1190 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1191 FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
1192 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1193 insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id
1194 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1195 select /*+ USE_CONCAT */ s1.party_id f,
1196 decode(instrb(s2.TX26,s1.TX26),1,100,
1197 0
1198 )
1199 +
1200 decode(instrb(s2.TX9,s1.TX9),1,15,
1201 0
1202 )
1203 +
1204 decode(instrb(s2.TX14,s1.TX14),1,5,
1205 0
1206 )
1207 +
1208 decode(instrb(s2.TX22,s1.TX22),1,5,
1209 0
1210 )
1211 score , s1.party_osr, s1.party_os, p_batch_id,'PARTY_SITES', s1.PARTY_SITE_ID, s1.PARTY_SITE_OSR, s1.PARTY_SITE_OS,
1212 s2.PARTY_SITE_ID
1213 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
1214 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
1215 from HZ_SRCH_PSITES s1, HZ_STAGED_PARTY_SITES s2
1216 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = 'U'
1217 and s1.party_id = s2.party_id
1218 and (
1219 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1220 -- do an or between all the transformations of an attribute --
1221 (
1222 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
1223 )
1224 )
1225 ------------ FILTER ATTRIBUTES SECTION ------------------------
1226 and
1227 -- do an or between all the transformations of an attribute --
1228 (
1229 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11 || ' ' )
1230 )
1231 ;
1232
1233
1234 --------UPDATE DQM ACTION FLAG IN PARTY_SITES INTERFACE/STAGING TABLES --------------
1235 open x_ent_cur for
1236 select distinct a.record_osr, a.record_os
1237 from hz_imp_dup_details a
1238 where a.batch_id = p_batch_id
1239 and a.party_osr between from_osr and to_osr and a.entity ='PARTY_SITES';
1240 HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('PARTY_SITES',p_batch_id, x_ent_cur);
1241 -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
1242 FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
1243 FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1244 FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1245
1246
1247
1248 -------------CONTACTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1249 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1250 FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACTS');
1251 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1252 insert into hz_imp_dup_details(party_id, score, party_osr, party_os, batch_id, entity, record_id, record_osr, record_os, dup_record_id
1253 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1254 select /*+ USE_CONCAT */ s1.party_id f,
1255 decode(instrb(s2.TX2,s1.TX2),1,20,
1256 decode(instrb(s2.TX23,s1.TX23),1,18,
1257 0
1258 )
1259 )
1260 +
1261 decode(instrb(s2.TX22,s1.TX22),1,10,
1262 0
1263 )
1264 score , s1.party_osr, s1.party_os, p_batch_id,'CONTACTS', s1.ORG_CONTACT_ID, s1.CONTACT_OSR, s1.CONTACT_OS,
1265 s2.ORG_CONTACT_ID
1266 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
1267 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
1268 from HZ_SRCH_CONTACTS s1, HZ_STAGED_CONTACTS s2
1269 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = 'U'
1270 and s1.party_id = s2.party_id
1271 and (
1272 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1273 -- do an or between all the transformations of an attribute --
1274 (
1275 (s1.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
1276 )
1277 )
1278 ;
1279
1280
1281 --------UPDATE DQM ACTION FLAG IN CONTACTS INTERFACE/STAGING TABLES --------------
1282 open x_ent_cur for
1283 select distinct a.record_osr, a.record_os
1284 from hz_imp_dup_details a
1285 where a.batch_id = p_batch_id
1286 and a.party_osr between from_osr and to_osr and a.entity ='CONTACTS';
1287 HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('CONTACTS',p_batch_id, x_ent_cur);
1288 -------------CONTACTS LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
1289 FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACTS');
1290 FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1291 FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1292
1293
1294
1295 ---------- exception block ---------------
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1299 FND_MESSAGE.SET_TOKEN('PROC','HZ_IMP_MATCH_RULE_52.interface_tca_join_entities');
1300 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
1301 FND_MSG_PUB.ADD;
1302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1303 END interface_tca_join_entities;
1304
1305
1306
1307
1308 ---------------------------------------------------------------
1309 -------------------- INTERFACE JOIN BEGINS --------------------------
1310 ---------------------------------------------------------------
1311 PROCEDURE interface_join_entities(p_batch_id in number,
1312 from_osr in varchar2, to_osr in varchar2, p_threshold in number)
1313 IS
1314 x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
1315 x_insert_threshold number := 20;
1316 BEGIN
1317 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1318 FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
1319 FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
1320 insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)
1321 select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (
1322 ------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
1323 select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1324 -------PARTY ENTITY: SCORING SECTION ---------
1325 decode(instrb(s2.TX2,s1.TX2),1,80,
1326 decode(instrb(s2.TX59,s1.TX59),1,72,
1327 0
1328 )
1329 )
1330 +
1331 decode(instrb(s2.TX41,s1.TX41),1,200,
1332 0
1333 )
1334 +
1335 decode(instrb(s2.TX45,s1.TX45),1,200,
1336 0
1337 )
1338 score, s1.party_os fos, s2.party_os tos
1339 from HZ_SRCH_PARTIES s1, HZ_SRCH_PARTIES s2
1340 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1341 and s2.batch_id = p_batch_id and not exists (select 1 from HZ_INT_DUP_RESULTS WHERE t_osr = s1.party_osr and batch_id = p_batch_id)
1342 and (
1343 -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1344 -- do an or between all the transformations of an attribute --
1345 (
1346 (s1.TX45 is not null and s2.TX45 like s1.TX45 || decode(sign(lengthb(s1.TX45)-3),1,'%',''))
1347 )
1348 or
1349 -- do an or between all the transformations of an attribute --
1350 (
1351 (s1.TX59 is not null and s2.TX59 like s1.TX59 || decode(sign(lengthb(s1.TX59)-3),1,'%',''))
1352 )
1353 or
1354 -- do an or between all the transformations of an attribute --
1355 (
1356 (s1.TX41 is not null and s2.TX41 like s1.TX41 || decode(sign(lengthb(s1.TX41)-3),1,'%',''))
1357 )
1358 )
1359 union all
1360 select f, t, max(score) score, fos, tos from (
1361 select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1362 -------CONTACT_POINTS ENTITY: SCORING SECTION ---------
1363 decode(instrb(s2.TX5,s1.TX5),1,60,
1364 0
1365 )
1366 +
1367 decode(instrb(s2.TX7,s1.TX7),1,20,
1368 0
1369 )
1370 +
1371 decode(instrb(s2.TX10,s1.TX10),1,70,
1372 decode(instrb(s2.TX158,s1.TX158),1,70,
1373 0
1374 )
1375 )
1376 score, s1.party_os fos, s2.party_os tos
1377 from HZ_SRCH_CPTS s1, HZ_SRCH_CPTS s2
1378 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1379 and s2.batch_id = p_batch_id and not exists (select 1 from HZ_INT_DUP_RESULTS WHERE t_osr = s1.party_osr and batch_id = p_batch_id)
1380 and s1.contact_point_type = s2.contact_point_type
1381 and (
1382 -------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1383 -- do an or between all the transformations of an attribute --
1384 (
1385 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
1386 )
1387 or
1388 -- do an or between all the transformations of an attribute --
1389 (
1390 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
1391 )
1392 or
1393 -- do an or between all the transformations of an attribute --
1394 (
1395 (s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-3),1,'%',''))
1396 )
1397 )
1398 )
1399 group by f, t, fos, tos
1400 )
1401 ------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
1402 where EXISTS (
1403 SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_SRCH_PARTIES p2
1404 WHERE p1.batch_id = p_batch_id and p1.party_osr = f and p1.party_os = fos
1405 and p2.batch_id = p_batch_id and p2.party_osr = t and p2.party_os = tos
1406 and
1407 -- do an or between all the transformations of an attribute --
1408 (
1409 ((p1.TX36 is null and p2.TX36 is null) or p2.TX36 = p1.TX36)
1410 )
1411 )
1412 group by f, t, fos, tos
1413 having sum(score) >= x_insert_threshold
1414 ;
1415 FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
1416 FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1417 FND_CONCURRENT.AF_Commit;
1418
1419
1420 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1421 FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
1422 FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
1423 open x_ent_cur for
1424 select f,t,max(score) from (
1425 select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1426 decode(instrb(s2.TX26,s1.TX26),1,100,
1427 0
1428 )
1429 +
1430 decode(instrb(s2.TX9,s1.TX9),1,15,
1431 0
1432 )
1433 +
1434 decode(instrb(s2.TX14,s1.TX14),1,5,
1435 0
1436 )
1437 +
1438 decode(instrb(s2.TX22,s1.TX22),1,5,
1439 0
1440 )
1441 score
1442 from hz_int_dup_results h1, HZ_SRCH_PSITES s1, HZ_SRCH_PSITES s2
1443 where
1444 s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id
1445 and s1.party_osr between from_osr and to_osr
1446 and (
1447 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1448 -- do an or between all the transformations of an attribute --
1449 (
1450 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
1451 )
1452 )
1453 ------------ FILTER ATTRIBUTES SECTION ------------------------
1454 and
1455 -- do an or between all the transformations of an attribute --
1456 (
1457 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
1458 )
1459 ) group by f,t ;
1460 HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1461 close x_ent_cur;
1462 FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1463 FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1464 FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
1465 FND_CONCURRENT.AF_Commit;
1466
1467
1468 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1469 FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of CONTACTS');
1470 FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
1471 open x_ent_cur for
1472 select f,t,max(score) from (
1473 select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1474 decode(instrb(s2.TX2,s1.TX2),1,20,
1475 decode(instrb(s2.TX23,s1.TX23),1,18,
1476 0
1477 )
1478 )
1479 +
1480 decode(instrb(s2.TX22,s1.TX22),1,10,
1481 0
1482 )
1483 score
1484 from hz_int_dup_results h1, HZ_SRCH_CONTACTS s1, HZ_SRCH_CONTACTS s2
1485 where
1486 s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id
1487 and s1.party_osr between from_osr and to_osr
1488 and (
1489 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1490 -- do an or between all the transformations of an attribute --
1491 (
1492 (s1.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
1493 )
1494 )
1495 ) group by f,t ;
1496 HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1497 close x_ent_cur;
1498 FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1499 FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1500 FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of CONTACTS');
1501 FND_CONCURRENT.AF_Commit;
1502 -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1503 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1504 FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
1505 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1506 insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1507 dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1508 dup_creation_date,dup_last_update_date
1509 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1510 select /*+ USE_CONCAT */ p_batch_id, s1.CONTACT_PT_OSR, s1.CONTACT_PT_OS,
1511 s2.CONTACT_PT_OSR, s2.CONTACT_PT_OS,
1512 s1.party_osr, s2.party_os,'CONTACT_POINTS',
1513 decode(nvl(s1.TX5,'N1'),nvl(substrb(s2.TX5,1,length(s1.TX5)),'N2'),60,
1514 0
1515 )
1516 +
1517 decode(nvl(s1.TX7,'N1'),nvl(substrb(s2.TX7,1,length(s1.TX7)),'N2'),20,
1518 0
1519 )
1520 +
1521 decode(nvl(s1.TX10,'N1'),nvl(substrb(s2.TX10,1,length(s1.TX10)),'N2'),70,
1522 decode(nvl(s1.TX158,'N1'),nvl(substrb(s2.TX158,1,length(s1.TX158)),'N2'),70,
1523 0
1524 )
1525 )
1526 score ,hz_utility_v2pub.creation_date, hz_utility_v2pub.last_update_date
1527 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
1528 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
1529 from HZ_SRCH_CPTS s1, HZ_SRCH_CPTS s2
1530 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
1531 and ( ( (s1.party_osr = s2.party_osr) and ( nvl(s1.party_id, 1) = nvl(s2.party_id,1) ) ) OR ( s1.party_id = s2.party_id) )
1532 and s2.batch_id = p_batch_id and s1.CONTACT_PT_OSR < s2.CONTACT_PT_OSR
1533 and s1.contact_point_type = s2.contact_point_type
1534 and (
1535 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1536 -- do an or between all the transformations of an attribute --
1537 (
1538 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
1539 )
1540 or
1541 -- do an or between all the transformations of an attribute --
1542 (
1543 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
1544 )
1545 or
1546 -- do an or between all the transformations of an attribute --
1547 (
1548 (s1.TX7 is not null and s2.TX7 like s1.TX7 || decode(sign(lengthb(s1.TX7)-3),1,'%',''))
1549 )
1550 )
1551 ;
1552 FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
1553 FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1554 FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1555 FND_CONCURRENT.AF_Commit;
1556 -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1557 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1558 FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
1559 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1560 insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1561 dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1562 dup_creation_date,dup_last_update_date
1563 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1564 select /*+ USE_CONCAT */ p_batch_id, s1.PARTY_SITE_OSR, s1.PARTY_SITE_OS,
1565 s2.PARTY_SITE_OSR, s2.PARTY_SITE_OS,
1566 s1.party_osr, s2.party_os,'PARTY_SITES',
1567 decode(nvl(s1.TX26,'N1'),nvl(substrb(s2.TX26,1,length(s1.TX26)),'N2'),100,
1568 0
1569 )
1570 +
1571 decode(nvl(s1.TX9,'N1'),nvl(substrb(s2.TX9,1,length(s1.TX9)),'N2'),15,
1572 0
1573 )
1574 +
1575 decode(nvl(s1.TX14,'N1'),nvl(substrb(s2.TX14,1,length(s1.TX14)),'N2'),5,
1576 0
1577 )
1578 +
1579 decode(nvl(s1.TX22,'N1'),nvl(substrb(s2.TX22,1,length(s1.TX22)),'N2'),5,
1580 0
1581 )
1582 score ,hz_utility_v2pub.creation_date, hz_utility_v2pub.last_update_date
1583 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
1584 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
1585 from HZ_SRCH_PSITES s1, HZ_SRCH_PSITES s2
1586 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
1587 and ( ( (s1.party_osr = s2.party_osr) and ( nvl(s1.party_id, 1) = nvl(s2.party_id,1) ) ) OR ( s1.party_id = s2.party_id) )
1588 and s2.batch_id = p_batch_id and s1.PARTY_SITE_OSR < s2.PARTY_SITE_OSR
1589 and (
1590 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1591 -- do an or between all the transformations of an attribute --
1592 (
1593 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
1594 )
1595 )
1596 ------------ FILTER ATTRIBUTES SECTION ------------------------
1597 and
1598 -- do an or between all the transformations of an attribute --
1599 (
1600 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
1601 )
1602 ;
1603 FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
1604 FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1605 FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1606 FND_CONCURRENT.AF_Commit;
1607 -------------CONTACTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1608 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1609 FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACTS');
1610 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1611 insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1612 dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1613 dup_creation_date,dup_last_update_date
1614 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1615 select /*+ USE_CONCAT */ p_batch_id, s1.CONTACT_OSR, s1.CONTACT_OS,
1616 s2.CONTACT_OSR, s2.CONTACT_OS,
1617 s1.party_osr, s2.party_os,'CONTACTS',
1618 decode(nvl(s1.TX2,'N1'),nvl(substrb(s2.TX2,1,length(s1.TX2)),'N2'),20,
1619 decode(nvl(s1.TX23,'N1'),nvl(substrb(s2.TX23,1,length(s1.TX23)),'N2'),18,
1620 0
1621 )
1622 )
1623 +
1624 decode(nvl(s1.TX22,'N1'),nvl(substrb(s2.TX22,1,length(s1.TX22)),'N2'),10,
1625 0
1626 )
1627 score ,hz_utility_v2pub.creation_date, hz_utility_v2pub.last_update_date
1628 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
1629 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
1630 from HZ_SRCH_CONTACTS s1, HZ_SRCH_CONTACTS s2
1631 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
1632 and ( ( (s1.party_osr = s2.party_osr) and ( nvl(s1.party_id, 1) = nvl(s2.party_id,1) ) ) OR ( s1.party_id = s2.party_id) )
1633 and s2.batch_id = p_batch_id and s1.CONTACT_OSR < s2.CONTACT_OSR
1634 and (
1635 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1636 -- do an or between all the transformations of an attribute --
1637 (
1638 (s1.TX23 is not null and s2.TX23 like s1.TX23 || decode(sign(lengthb(s1.TX23)-3),1,'%',''))
1639 )
1640 )
1641 ;
1642 FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACTS');
1643 FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1644 FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1645 FND_CONCURRENT.AF_Commit;
1646
1647 ---------- exception block ---------------
1648 EXCEPTION
1649 WHEN OTHERS THEN
1650 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1651 FND_MESSAGE.SET_TOKEN('PROC','HZ_IMP_MATCH_RULE_52.interface_join_entities');
1652 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
1653 FND_MSG_PUB.ADD;
1654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1655 END interface_join_entities;
1656 END;
[Home] [Help]