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