1 PACKAGE BODY HZ_IMP_MATCH_RULE_51 AS
2 g_match_rule_id NUMBER := 51;
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_TX158 CharList2000;
37 H_TX19 CharList2000;
38 H_TX2 CharList2000;
39 H_TX22 CharList2000;
40 H_TX26 CharList2000;
41 H_TX27 CharList2000;
42 H_TX3 CharList2000;
43 H_TX36 CharList2000;
44 H_TX4 CharList2000;
45 H_TX45 CharList2000;
46 H_TX5 CharList2000;
47 H_TX59 CharList2000;
48 H_TX6 CharList2000;
49 H_TX60 CharList2000;
50 H_TX8 CharList2000;
51 H_TX9 CharList2000;
52 H_8E CharList2000;
53 H_14E CharList2000;
54 H_19E CharList2000;
55 H_29E CharList2000;
56 H_30E CharList2000;
57 H_32E CharList2000;
58 H_36E CharList2000;
59 H_48E CharList2000;
60 H_P_CP_R_PH_NO CharList60;
61 H_CP_CUST_TX10 CharList2000;
62 H_CP_CUST_TX158 CharList2000;
63 H_P_PS_ADD CharList1000;
64 H_PS_CUST_TX26 CharList2000;
65 PROCEDURE pop_parties (
66 p_batch_id IN NUMBER,
67 p_from_osr IN VARCHAR2,
68 p_to_osr IN VARCHAR2,
69 p_batch_mode_flag IN VARCHAR2
70 ) IS
71 l_last_fetch BOOLEAN := FALSE;
72 p_party_cur HZ_PARTY_STAGE.StageCurTyp;
73
74 count NUMBER := 0;
75 BEGIN
76 -- query for interface to TCA
77 open p_party_cur FOR
78 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.JGZZ_FISCAL_CODE, a.party_orig_system, a.party_orig_system_reference, b.party_id, a.rowid, a.party_type
79 from hz_imp_parties_int a, hz_imp_parties_sg b
80 where b.action_flag = 'I'
81 and b.int_row_id = a.rowid
82 and a.batch_id = p_batch_id
83 and b.party_orig_system_reference >= p_from_osr
84 and b.party_orig_system_reference <= p_to_osr
85 and b.batch_mode_flag = p_batch_mode_flag
86 and interface_status is null ;
87 LOOP
88 FETCH p_party_cur BULK COLLECT INTO
89 H_8E, H_14E, H_19E, H_P_PARTY_OS , H_P_PARTY_OSR, H_P_PARTY_ID, H_P_ROW_ID, H_P_P_TYPE
90 LIMIT g_limit;
91 IF (p_party_cur%NOTFOUND) THEN
92 l_last_fetch:=TRUE;
93 END IF;
94
95 IF H_P_PARTY_OS.COUNT=0 AND l_last_fetch THEN
96 EXIT;
97 END IF;
98
99 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
100 HZ_TRANS_PKG.set_party_type(H_P_P_TYPE(I));
101 H_TX2(I) := HZ_TRANS_PKG.EXACT_PADDED(H_8E(I), NULL, 'PARTY_NAME', 'PARTY');
102 H_TX36(I) := HZ_TRANS_PKG.EXACT(H_14E(I), NULL, 'PARTY_TYPE', 'PARTY');
103 H_TX45(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_19E(I), NULL, 'JGZZ_FISCAL_CODE', 'PARTY', 'SEARCH' );
104 H_TX59(I) := HZ_TRANS_PKG.BASIC_WRNAMES(H_8E(I), NULL, 'PARTY_NAME', 'PARTY', 'SEARCH' );
105 END LOOP;
106 SAVEPOINT pop_parties;
107 BEGIN
108 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
109 INSERT INTO HZ_SRCH_PARTIES (
110 TX2, TX36, TX45, TX59, PARTY_OS, PARTY_OSR, PARTY_ID, BATCH_ID, INT_ROW_ID
111 ) VALUES (
112 H_TX2(I), H_TX36(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)
113 );
114 EXCEPTION
115 WHEN OTHERS THEN
116 ROLLBACK to pop_parties;
117 -- dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
118 RAISE;
119 END ;
120 IF l_last_fetch THEN
121 FND_CONCURRENT.AF_Commit;
122 EXIT;
123 END IF;
124 FND_CONCURRENT.AF_Commit;
125
126 END LOOP;
127 CLOSE p_party_cur;
128 END pop_parties;
129
130 PROCEDURE pop_party_sites (
131 p_batch_id IN NUMBER,
132 p_from_osr IN VARCHAR2,
133 p_to_osr IN VARCHAR2,
134 p_batch_mode_flag IN VARCHAR2
135 ) IS
136 l_last_fetch BOOLEAN := FALSE;
137 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
138
139 BEGIN
140 -- query for interface to tca
141 open l_party_site_cur for
142 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
143 from hz_imp_addresses_int a, hz_imp_addresses_sg b
144 where a.batch_id = p_batch_id
145 and b.action_flag = 'I'
146 and b.int_row_id = a.rowid
147 and a.party_orig_system_reference >= p_from_osr
148 and a.party_orig_system_reference <= p_to_osr
149 and b.batch_mode_flag = p_batch_mode_flag
150 and interface_status is null ;
151 LOOP
152 FETCH l_party_site_cur BULK COLLECT INTO
153 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
154 LIMIT g_limit;
155
156 IF (l_party_site_cur%NOTFOUND) THEN
157 l_last_fetch := TRUE;
158 END IF;
159
160 IF H_P_PS_OS.COUNT = 0 AND l_last_fetch THEN
161 EXIT;
162 END IF;
163
164 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
165 ----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY_SITES LEVEL ---------
166 HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (36,H_36E(I) );
167 H_TX11(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_30E(I), NULL, 'POSTAL_CODE', 'PARTY_SITES', 'SEARCH' );
168 H_TX14(I) := HZ_TRANS_PKG.WRSTATE_EXACT(H_32E(I), NULL, 'STATE', 'PARTY_SITES', 'SEARCH' );
169 H_TX22(I) := HZ_TRANS_PKG.EXACT(H_36E(I), NULL, 'COUNTRY', 'PARTY_SITES');
170 H_TX9(I) := HZ_TRANS_PKG.EXACT(H_29E(I), NULL, 'CITY', 'PARTY_SITES');
171 H_PS_CUST_TX26(I) := HZ_TRANS_PKG.BASIC_WRADDR(H_P_PS_ADD(I), NULL, 'ADDRESS', 'PARTY_SITES', 'SEARCH' );
172 END LOOP;
173 SAVEPOINT pop_party_sites;
174 BEGIN
175 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
176 INSERT INTO HZ_SRCH_PSITES (
177 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
178 ) VALUES (
179 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)
180 );
181 EXCEPTION
182 WHEN OTHERS THEN
183 ROLLBACK to pop_party_sites;
184 RAISE;
185 END;
186
187 IF l_last_fetch THEN
188 FND_CONCURRENT.AF_Commit;
189 EXIT;
190 END IF;
191 FND_CONCURRENT.AF_Commit;
192
193 END LOOP;
194 CLOSE l_party_site_cur;
195 END pop_party_sites;
196 PROCEDURE pop_cp (
197 p_batch_id IN NUMBER,
198 p_from_osr IN VARCHAR2,
199 p_to_osr IN VARCHAR2,
200 p_batch_mode_flag IN VARCHAR2
201 ) IS
202
203 l_last_fetch BOOLEAN := FALSE;
204 l_cp_cur HZ_PARTY_STAGE.StageCurTyp;
205 BEGIN
206 open l_cp_cur for
207 select a.EMAIL_ADDRESS, 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
208 from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b --
209 where a.batch_id = p_batch_id
210 and b.action_flag = 'I'
211 and b.int_row_id = a.rowid
212 and b.party_orig_system_reference >= p_from_osr
213 and b.party_orig_system_reference <= p_to_osr
214 and b.batch_mode_flag = p_batch_mode_flag
215 and interface_status is null ;
216 LOOP
217 FETCH l_cp_cur BULK COLLECT INTO
218 H_48E, 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
219 LIMIT g_limit;
220 IF l_cp_cur%NOTFOUND THEN
221 l_last_fetch := TRUE;
222 END IF;
223
224 IF H_P_CP_OSR.COUNT = 0 AND l_last_fetch THEN
225 EXIT;
226 END IF;
227
228 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
229 H_TX5(I) := HZ_TRANS_PKG.EXACT_EMAIL(H_48E(I), NULL, 'EMAIL_ADDRESS', 'CONTACT_POINTS');
230 H_CP_CUST_TX10(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_P_CP_R_PH_NO(I), NULL, 'RAW_PHONE_NUMBER', 'CONTACT_POINTS', 'SEARCH' );
231 H_CP_CUST_TX158(I) := HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(H_P_CP_R_PH_NO(I), NULL, 'RAW_PHONE_NUMBER', 'CONTACT_POINTS');
232 END LOOP;
233 SAVEPOINT POP_CP;
234 BEGIN
235 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
236 INSERT INTO HZ_SRCH_CPTS (
237 TX5, 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
238 ) VALUES (
239 H_TX5(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)
240 );
241 EXCEPTION
242 WHEN OTHERS THEN
243 ROLLBACK to POP_CP;
244 RAISE;
245 END;
246
247 IF l_last_fetch THEN
248 FND_CONCURRENT.AF_Commit;
249 EXIT;
250 END IF;
251 FND_CONCURRENT.AF_Commit;
252
253 END LOOP;
254 CLOSE l_cp_cur;
255 END pop_cp;
256 PROCEDURE get_contact_cur(
257 p_batch_id IN NUMBER,
258 p_from_osr IN VARCHAR2,
259 p_to_osr IN VARCHAR2,
260 p_batch_mode_flag IN VARCHAR2,
261 x_contact_cur IN OUT NOCOPY StageImpContactCurTyp
262 ) IS
263 is_using_allow_cust_attr VARCHAR2(1);
264 CURSOR c1 is select 'Y'
265 from hz_trans_attributes_vl
266 where entity_name = 'CONTACTS'
267 and attribute_name = 'CONTACT_NAME'
268 and attribute_id in (
269 select attribute_id
270 from hz_match_rule_primary b
271 where match_rule_id = 51
272 union
273 select attribute_id
274 from hz_match_rule_secondary b
275 where match_rule_id = 51 ) and rownum = 1;
276
277 BEGIN
278 null; --5
279 END get_contact_cur;
280
281 PROCEDURE pop_contacts (
282 p_batch_id IN NUMBER,
283 p_from_osr IN VARCHAR2,
284 p_to_osr IN VARCHAR2,
285 p_batch_mode_flag IN VARCHAR2
286 ) IS
287 l_last_fetch BOOLEAN := FALSE;
288 l_contact_cur StageImpContactCurTyp;
289
290 BEGIN
291 null;
292 END pop_contacts;
293
294 PROCEDURE pop_parties_int (
295 p_batch_id IN NUMBER,
296 p_from_osr IN VARCHAR2,
297 p_to_osr IN VARCHAR2
298 ) IS
299 l_last_fetch BOOLEAN := FALSE;
300 p_party_cur HZ_PARTY_STAGE.StageCurTyp;
301
302 count NUMBER := 0;
303 l_os VARCHAR2(30);
304 BEGIN
305 l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id);
306 open p_party_cur FOR
307 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.JGZZ_FISCAL_CODE, a.party_orig_system, a.party_orig_system_reference, a.rowid, a.party_type , a.party_id
308 from hz_imp_parties_int a
309 where a.batch_id = p_batch_id
310 and a.party_orig_system_reference >= p_from_osr
311 and a.party_orig_system_reference <= p_to_osr
312 and a.party_orig_system = l_os;
313 LOOP
314 FETCH p_party_cur BULK COLLECT INTO
315 H_8E, H_14E, H_19E, H_P_PARTY_OS , H_P_PARTY_OSR, H_P_ROW_ID, H_P_P_TYPE , H_P_PARTY_ID
316 LIMIT g_limit;
317 IF p_party_cur%NOTFOUND THEN
318 l_last_fetch:=TRUE;
319 END IF;
320
321 IF H_P_PARTY_OS.COUNT=0 AND l_last_fetch THEN
322 EXIT;
323 END IF;
324
325 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
326 HZ_TRANS_PKG.set_party_type(H_P_P_TYPE(I));
327 H_TX2(I) := HZ_TRANS_PKG.EXACT_PADDED(H_8E(I), NULL, 'PARTY_NAME', 'PARTY');
328 H_TX36(I) := HZ_TRANS_PKG.EXACT(H_14E(I), NULL, 'PARTY_TYPE', 'PARTY');
329 H_TX45(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_19E(I), NULL, 'JGZZ_FISCAL_CODE', 'PARTY', 'SEARCH' );
330 H_TX59(I) := HZ_TRANS_PKG.BASIC_WRNAMES(H_8E(I), NULL, 'PARTY_NAME', 'PARTY', 'SEARCH' );
331 END LOOP;
332 SAVEPOINT pop_parties_int;
333 BEGIN
334 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
335 INSERT INTO HZ_SRCH_PARTIES (
336 TX2, TX36, TX45, TX59, PARTY_OS, PARTY_OSR, BATCH_ID, INT_ROW_ID , PARTY_ID
337 ) VALUES (
338 H_TX2(I), H_TX36(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)
339 );
340 EXCEPTION
341 WHEN OTHERS THEN
342 ROLLBACK to pop_parties_int;
343 -- dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
344 RAISE;
345 END ;
346 IF l_last_fetch THEN
347 FND_CONCURRENT.AF_Commit;
348 EXIT;
349 END IF;
350 FND_CONCURRENT.AF_Commit;
351
352 END LOOP;
353 CLOSE p_party_cur;
354 END pop_parties_int;
355
356 PROCEDURE pop_party_sites_int (
357 p_batch_id IN NUMBER,
358 p_from_osr IN VARCHAR2,
359 p_to_osr IN VARCHAR2
360 ) IS
361 l_last_fetch BOOLEAN := FALSE;
362 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
363
364 l_os VARCHAR2(30);
365 BEGIN
366 l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id);
367 open l_party_site_cur for
368 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
369 from hz_imp_addresses_int a
370 where a.batch_id = p_batch_id
371 and a.party_orig_system_reference >= p_from_osr
372 and a.party_orig_system_reference <= p_to_osr
373 and a.party_orig_system = l_os;
374 LOOP
375 FETCH l_party_site_cur BULK COLLECT INTO
376 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
377 LIMIT g_limit;
378
379 IF l_party_site_cur%NOTFOUND THEN
380 l_last_fetch:=TRUE;
381 END IF;
382 IF H_P_PS_OS.COUNT=0 AND l_last_fetch THEN
383 EXIT;
384 END IF;
385
386 FOR I in H_P_PS_OSR.FIRST..H_P_PS_OSR.LAST LOOP
387 ----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY_SITES LEVEL ---------
388 HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (36,H_36E(I) );
389 H_TX11(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_30E(I), NULL, 'POSTAL_CODE', 'PARTY_SITES', 'SEARCH' );
390 H_TX14(I) := HZ_TRANS_PKG.WRSTATE_EXACT(H_32E(I), NULL, 'STATE', 'PARTY_SITES', 'SEARCH' );
391 H_TX22(I) := HZ_TRANS_PKG.EXACT(H_36E(I), NULL, 'COUNTRY', 'PARTY_SITES');
392 H_TX9(I) := HZ_TRANS_PKG.EXACT(H_29E(I), NULL, 'CITY', 'PARTY_SITES');
393 H_PS_CUST_TX26(I) := HZ_TRANS_PKG.BASIC_WRADDR(H_P_PS_ADD(I), NULL, 'ADDRESS', 'PARTY_SITES', 'SEARCH' );
394 END LOOP;
395 SAVEPOINT pop_party_sites_int;
396 BEGIN
397 FORALL I in H_P_PS_OSR.FIRST..H_P_PS_OSR.LAST
398 INSERT INTO HZ_SRCH_PSITES (
399 TX11, TX14, TX22, TX9, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, BATCH_ID, INT_ROW_ID, TX26 , PARTY_ID
400 ) VALUES (
401 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)
402 );
403 EXCEPTION
404 WHEN OTHERS THEN
405 ROLLBACK to pop_party_sites_int;
406 RAISE;
407 END;
408
409 IF l_last_fetch THEN
410 FND_CONCURRENT.AF_Commit;
411 EXIT;
412 END IF;
413 FND_CONCURRENT.AF_Commit;
414
415 END LOOP;
416 CLOSE l_party_site_cur;
417 END pop_party_sites_int;
418
419 PROCEDURE pop_cp_int (
420 p_batch_id IN NUMBER,
421 p_from_osr IN VARCHAR2,
422 p_to_osr IN VARCHAR2
423 ) IS
424 l_last_fetch BOOLEAN := FALSE;
425 l_cp_cur HZ_PARTY_STAGE.StageCurTyp;
426 l_os VARCHAR2(30);
427 BEGIN
428 l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id);
429 open l_cp_cur for
430 select a.EMAIL_ADDRESS, 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
431 from HZ_IMP_CONTACTPTS_INT a
432 where a.batch_id = p_batch_id
433 and a.party_orig_system_reference >= p_from_osr
434 and a.party_orig_system_reference <= p_to_osr
435 and a.party_orig_system = l_os;
436
437 LOOP
438 FETCH l_cp_cur BULK COLLECT INTO
439 H_48E, 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
440 LIMIT g_limit;
441 IF l_cp_cur%NOTFOUND THEN
442 l_last_fetch:=TRUE;
443 END IF;
444
445 IF H_P_CP_OS.COUNT=0 AND l_last_fetch THEN
446 EXIT;
447 END IF;
448
449 FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP
450 H_TX5(I) := HZ_TRANS_PKG.EXACT_EMAIL(H_48E(I), NULL, 'EMAIL_ADDRESS', 'CONTACT_POINTS');
451 H_CP_CUST_TX10(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_P_CP_R_PH_NO(I), NULL, 'RAW_PHONE_NUMBER', 'CONTACT_POINTS', 'SEARCH' );
452 H_CP_CUST_TX158(I) := HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(H_P_CP_R_PH_NO(I), NULL, 'RAW_PHONE_NUMBER', 'CONTACT_POINTS');
453 END LOOP;
454 SAVEPOINT pop_cp_int;
455 BEGIN
456 FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST
457 INSERT INTO HZ_SRCH_CPTS (
458 TX5, 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
459 ) VALUES (
460 H_TX5(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)
461 );
462 EXCEPTION
463 WHEN OTHERS THEN
464 ROLLBACK to pop_cp_int;
465 RAISE;
466 END;
467
468 IF l_last_fetch THEN
469 FND_CONCURRENT.AF_Commit;
470 EXIT;
471 END IF;
472 FND_CONCURRENT.AF_Commit;
473
474 END LOOP;
475 CLOSE l_cp_cur ;
476
477 END pop_cp_int;
478 PROCEDURE get_contact_cur_int(
479 p_batch_id IN NUMBER,
480 p_from_osr IN VARCHAR2,
481 p_to_osr IN VARCHAR2,
482 x_contact_cur IN OUT NOCOPY StageImpContactCurTyp
483 ) IS
484 is_using_allow_cust_attr VARCHAR2(1);
485 CURSOR c1 is select 'Y'
486 from hz_trans_attributes_vl
487 where entity_name = 'CONTACTS'
488 and attribute_name = 'CONTACT_NAME'
489 and attribute_id in (
490 select attribute_id
491 from hz_match_rule_primary b
492 where match_rule_id = 51
493 union
494 select attribute_id
495 from hz_match_rule_secondary b
496 where match_rule_id = 51 ) and rownum = 1;
497
498 l_os VARCHAR2(30);
499 BEGIN --
500 l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id);
501 null;
502 END get_contact_cur_int;
503
504 PROCEDURE pop_contacts_int (
505 p_batch_id IN NUMBER,
506 p_from_osr IN VARCHAR2,
507 p_to_osr IN VARCHAR2
508 ) IS
509 l_last_fetch BOOLEAN := FALSE;
510 l_contact_cur StageImpContactCurTyp;
511
512 BEGIN
513 null;
514 END pop_contacts_int;
515
516
517
518
519 ---------------------------------------------------------------
520 -------------------- TCA JOIN BEGINS --------------------------
521 ---------------------------------------------------------------
522 PROCEDURE tca_join_entities(trap_explosion in varchar2, rows_in_chunk in number, inserted_duplicates out number)
523 IS
524 x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
525 x_insert_threshold number := 60;
526 l_party_limit NUMBER := 50000;
527 l_detail_limit NUMBER := 100000;
528 BEGIN
529 FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
530 insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score)
531 select f, t, least(f,t), greatest(f,t), sum(score) score from (
532 select /*+ ORDERED */ s1.party_id f, s2.party_id t,
533 -------PARTY ENTITY: SCORING SECTION ---------
534 decode(instrb(s2.TX2,s1.TX2),1,80,
535 decode(instrb(s2.TX59,s1.TX59),1,72,
536 0
537 )
538 )
539 +
540 decode(instrb(s2.TX45,s1.TX45),1,200,
541 0
542 )
543 score
544 from hz_dup_worker_chunk_gt p, HZ_STAGED_PARTIES s1, HZ_STAGED_PARTIES s2
545 where p.party_id = s1.party_id and s1.party_id<>s2.party_id
546 and nvl(s1.status,'A') = 'A' and nvl(s2.status,'A') = 'A'
547 and 1=decode(trap_explosion,'N',1,decode(rownum,l_party_limit,to_number('A'),1))
548 and (
549 -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES---------
550 -- do an or between all the transformations of an attribute --
551 (
552 (s1.TX45 is not null and s2.TX45 like s1.TX45 || decode(sign(lengthb(s1.TX45)-3),1,'%',''))
553 )
554 or
555 -- do an or between all the transformations of an attribute --
556 (
557 (s1.TX59 is not null and s2.TX59 like s1.TX59 || decode(sign(lengthb(s1.TX59)-3),1,'%',''))
558 )
559 )
560 union all
561 select f, t, max(score) score from (
562 select /*+ ORDERED */ s1.party_id f, s2.party_id t,
563 -------CONTACT_POINTS ENTITY: SCORING SECTION ---------
564 decode(instrb(s2.TX5,s1.TX5),1,60,
565 0
566 )
567 +
568 decode(instrb(s2.TX158,s1.TX158),1,70,
569 decode(instrb(s2.TX10,s1.TX10),1,70,
570 0
571 )
572 )
573 score
574 from hz_dup_worker_chunk_gt p, HZ_STAGED_CONTACT_POINTS s1, HZ_STAGED_CONTACT_POINTS s2
575 where p.party_id = s1.party_id and s1.party_id<>s2.party_id
576 and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,'A') = 'A')
577 and 1=decode(trap_explosion,'N',1,decode(rownum,l_detail_limit,to_number('A'),1))
578 and (
579 -------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES---------
580 -- do an or between all the transformations of an attribute --
581 (
582 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
583 )
584 or
585 -- do an or between all the transformations of an attribute --
586 (
587 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
588 )
589 )
590 ) group by f, t
591 )
592 ------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
593 where EXISTS (
594 SELECT 1 FROM HZ_STAGED_PARTIES p1, HZ_STAGED_PARTIES p2
595 WHERE p1.party_id = f and p2.party_id = t
596 and
597 -- do an or between all the transformations of an attribute --
598 (
599 ((p1.TX36 is null and p2.TX36 is null) or p2.TX36 = p1.TX36)
600 )
601 )
602 group by f, t
603 having sum(score) >= x_insert_threshold
604 ;
605 inserted_duplicates := (SQL%ROWCOUNT);
606 FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
607 FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
608 FND_CONCURRENT.AF_Commit;
609
610
611 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
612 FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
613 FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
614 open x_ent_cur for
615 select f, t, max(score) score from (
616 select /*+ ORDERED */ s1.party_id f, s2.party_id t,
617 decode(instrb(s2.TX26,s1.TX26),1,100,
618 0
619 )
620 +
621 decode(instrb(s2.TX9,s1.TX9),1,5,
622 0
623 )
624 +
625 decode(instrb(s2.TX14,s1.TX14),1,5,
626 0
627 )
628 +
629 decode(instrb(s2.TX22,s1.TX22),1,5,
630 0
631 )
632 score
633 from hz_dup_worker_chunk_gt p, hz_dup_results h1, HZ_STAGED_PARTY_SITES s1, HZ_STAGED_PARTY_SITES s2
634 where p.party_id=h1.fid and s1.party_id = h1.fid and s2.party_id = h1.tid
635 and (
636 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
637 -- do an or between all the transformations of an attribute --
638 (
639 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
640 )
641 )
642 ------------ FILTER ATTRIBUTES SECTION ------------------------
643 and
644 -- do an or between all the transformations of an attribute --
645 (
646 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
647 )
648 ) group by f,t ;
649 HZ_DQM_DUP_ID_PKG.update_hz_dup_results(x_ent_cur);
650 close x_ent_cur;
651 FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
652 FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
653 FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
654 FND_CONCURRENT.AF_Commit;
655
656
657 ---------- exception block ---------------
658 EXCEPTION
659 WHEN OTHERS THEN
660 IF sqlcode=-1722
661 THEN
662 inserted_duplicates := -1;
663 ELSE
664 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
665 FND_MESSAGE.SET_TOKEN('PROC','HZ_IMP_MATCH_RULE_51.tca_join_entities');
666 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
667 FND_MSG_PUB.ADD;
668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
669 END IF;
670 END tca_join_entities;
671
672
673
674
675 ---------------------------------------------------------------
676 -------------------- INTERFACE TCA JOIN BEGINS --------------------------
677 ---------------------------------------------------------------
678 PROCEDURE interface_tca_join_entities( p_batch_id in number, from_osr in varchar2, to_osr in varchar2,
679 p_threshold in number, p_auto_merge_threshold in number)
680 IS
681 x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
682 x_insert_threshold number := 60;
683 BEGIN
684 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
685 FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
686 FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
687 insert into hz_imp_dup_parties(party_id,dup_party_id, score, party_osr, party_os, batch_id, auto_merge_flag
688 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
689 select f, t, sum(score) sc, party_osr, party_os, p_batch_id, 'N'
690 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
691 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
692 from (
693 ------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
694 select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
695 -------PARTY ENTITY: SCORING SECTION ---------
696 decode(instrb(s2.TX2,s1.TX2),1,80,
697 decode(instrb(s2.TX59,s1.TX59),1,72,
698 0
699 )
700 )
701 +
702 decode(instrb(s2.TX45,s1.TX45),1,200,
703 0
704 )
705 score , s1.party_osr party_osr, s1.party_os party_os
706 from HZ_SRCH_PARTIES s1, HZ_STAGED_PARTIES s2
707 where s1.party_id is not null and s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
708 and nvl(s2.status,'A') = 'A'
709 and (
710 -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
711 -- do an or between all the transformations of an attribute --
712 (
713 (s1.TX45 is not null and s2.TX45 like s1.TX45 || decode(sign(lengthb(s1.TX45)-3),1,'%',''))
714 )
715 or
716 -- do an or between all the transformations of an attribute --
717 (
718 (s1.TX59 is not null and s2.TX59 like s1.TX59 || decode(sign(lengthb(s1.TX59)-3),1,'%',''))
719 )
720 )
721 union all
722 select f, t, max(score) score, party_osr, party_os from (
723 select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
724 -------CONTACT_POINTS ENTITY: SCORING SECTION ---------
725 decode(instrb(s2.TX5,s1.TX5),1,60,
726 0
727 )
728 +
729 decode(instrb(s2.TX158,s1.TX158),1,70,
730 decode(instrb(s2.TX10,s1.TX10),1,70,
731 0
732 )
733 )
734 score , s1.party_osr party_osr, s1.party_os party_os
735 from HZ_SRCH_CPTS s1, HZ_STAGED_CONTACT_POINTS s2
736 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'
737 and exists(SELECT 1 from hz_staged_parties q where q.party_id = s2.party_id and nvl(q.status,'A') = 'A')
738 and (
739 -------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
740 -- do an or between all the transformations of an attribute --
741 (
742 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
743 )
744 or
745 -- do an or between all the transformations of an attribute --
746 (
747 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
748 )
749 )
750 )
751 group by f, t, party_osr, party_os
752 )
753 ------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
754 where EXISTS (
755 SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_STAGED_PARTIES p2
756 WHERE p1.batch_id = p_batch_id and p1.party_osr = party_osr and p1.party_os = party_os
757 and p2.party_id = t
758 and
759 -- do an or between all the transformations of an attribute --
760 (
761 ((p1.TX36 is null and p2.TX36 is null) or p2.TX36 = p1.TX36 || ' ' )
762 )
763 )
764 group by f, t, party_osr, party_os
765 having sum(score) >= x_insert_threshold
766 ;
767 FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
768 FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
769
770
771 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
772 FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
773 FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
774 open x_ent_cur for
775 select f,t,max(score) from (
776 select /*+ USE_CONCAT */ s1.party_id f, s2.party_id t,
777 decode(instrb(s2.TX26,s1.TX26),1,100,
778 0
779 )
780 +
781 decode(instrb(s2.TX9,s1.TX9),1,5,
782 0
783 )
784 +
785 decode(instrb(s2.TX14,s1.TX14),1,5,
786 0
787 )
788 +
789 decode(instrb(s2.TX22,s1.TX22),1,5,
790 0
791 )
792 score
793 from hz_imp_dup_parties h1, HZ_SRCH_PSITES s1, HZ_STAGED_PARTY_SITES s2
794 where h1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
795 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
796 and (
797 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
798 -- do an or between all the transformations of an attribute --
799 (
800 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
801 )
802 )
803 ------------ FILTER ATTRIBUTES SECTION ------------------------
804 and
805 -- do an or between all the transformations of an attribute --
806 (
807 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11 || ' ' )
808 )
809 ) group by f,t ;
810 HZ_DQM_DUP_ID_PKG.update_hz_imp_dup_parties(p_batch_id, x_ent_cur);
811 close x_ent_cur;
812 FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
813 FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
814 FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
815
816 --------DELETE ON THRESHOLD AND REMOVE INDIRECT TRANSITIVITY ---------------------
817
818 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
819 FND_FILE.put_line(FND_FILE.log,'DELETE ON THRESHOLD AND INDIRECT TRANSITIVITY ');
820 FND_FILE.put_line(FND_FILE.log,'Begin time to delete '||to_char(sysdate,'hh24:mi:ss'));
821
822 delete from hz_imp_dup_parties a
823 where (a.party_osr >= from_osr and a.party_osr <= to_osr
824 and a.batch_id = p_batch_id)
825 and (
826 a.score < p_threshold
827 or
828 -- delete the party id whose duplicate is a bigger number, when scores are same
829 exists
830 (Select 1 from hz_imp_dup_parties b
831 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)
832 or
833 -- delete the party id with least score, if scores are different
834 exists
835 (Select 1 from hz_imp_dup_parties b
836 where b.batch_id=p_batch_id and a.party_id=b.party_id and a.score < b.score)
837 );
838
839 FND_FILE.put_line(FND_FILE.log,'Number of records deleted from hz_imp_dup_parties '||SQL%ROWCOUNT);
840 FND_FILE.put_line(FND_FILE.log,'End time to delete '||to_char(sysdate,'hh24:mi:ss'));
841 --------UPDATE AUTO MERGE FLAG --------------
842 update hz_imp_dup_parties a
843 set a.auto_merge_flag = 'Y'
844 where a.score >= p_auto_merge_threshold
845 and a.party_osr >= from_osr and a.party_osr <= to_osr
846 and a.batch_id = p_batch_id ;
847 --------UPDATE DQM ACTION FLAG IN INTERFACE/STAGING TABLES --------------
848
849 open x_ent_cur for
850 select a.party_osr, a.party_os, a.auto_merge_flag
851 from hz_imp_dup_parties a
852 where a.batch_id = p_batch_id
853 and a.party_osr between from_osr and to_osr ;
854 HZ_DQM_DUP_ID_PKG.update_party_dqm_action_flag(p_batch_id, x_ent_cur);
855 ----------------------PARTY LEVEL DUPLICATE IDENTIFICATION ENDS --------------------
856
857
858 -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
859 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
860 FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
861 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
862 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
863 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
864 select /*+ USE_CONCAT */ s1.party_id f,
865 decode(instrb(s2.TX5,s1.TX5),1,60,
866 0
867 )
868 +
869 decode(instrb(s2.TX158,s1.TX158),1,70,
870 decode(instrb(s2.TX10,s1.TX10),1,70,
871 0
872 )
873 )
874 score , s1.party_osr, s1.party_os, p_batch_id,'CONTACT_POINTS', s1.CONTACT_POINT_ID, s1.CONTACT_PT_OSR, s1.CONTACT_PT_OS,
875 s2.CONTACT_POINT_ID
876 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
877 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
878 from HZ_SRCH_CPTS s1, HZ_STAGED_CONTACT_POINTS s2
879 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = 'U'
880 and s1.party_id = s2.party_id
881 and (
882 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
883 -- do an or between all the transformations of an attribute --
884 (
885 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
886 )
887 or
888 -- do an or between all the transformations of an attribute --
889 (
890 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
891 )
892 )
893 ;
894
895
896 --------UPDATE DQM ACTION FLAG IN CONTACT_POINTS INTERFACE/STAGING TABLES --------------
897 open x_ent_cur for
898 select distinct a.record_osr, a.record_os
899 from hz_imp_dup_details a
900 where a.batch_id = p_batch_id
901 and a.party_osr between from_osr and to_osr and a.entity ='CONTACT_POINTS';
902 HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('CONTACT_POINTS',p_batch_id, x_ent_cur);
903 -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
904 FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
905 FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
906 FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
907
908
909
910 -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
911 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
912 FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
913 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
914 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
915 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
916 select /*+ USE_CONCAT */ s1.party_id f,
917 decode(instrb(s2.TX26,s1.TX26),1,100,
918 0
919 )
920 +
921 decode(instrb(s2.TX9,s1.TX9),1,5,
922 0
923 )
924 +
925 decode(instrb(s2.TX14,s1.TX14),1,5,
926 0
927 )
928 +
929 decode(instrb(s2.TX22,s1.TX22),1,5,
930 0
931 )
932 score , s1.party_osr, s1.party_os, p_batch_id,'PARTY_SITES', s1.PARTY_SITE_ID, s1.PARTY_SITE_OSR, s1.PARTY_SITE_OS,
933 s2.PARTY_SITE_ID
934 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
935 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
936 from HZ_SRCH_PSITES s1, HZ_STAGED_PARTY_SITES s2
937 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.new_party_flag = 'U'
938 and s1.party_id = s2.party_id
939 and (
940 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
941 -- do an or between all the transformations of an attribute --
942 (
943 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
944 )
945 )
946 ------------ FILTER ATTRIBUTES SECTION ------------------------
947 and
948 -- do an or between all the transformations of an attribute --
949 (
950 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11 || ' ' )
951 )
952 ;
953
954
955 --------UPDATE DQM ACTION FLAG IN PARTY_SITES INTERFACE/STAGING TABLES --------------
956 open x_ent_cur for
957 select distinct a.record_osr, a.record_os
958 from hz_imp_dup_details a
959 where a.batch_id = p_batch_id
960 and a.party_osr between from_osr and to_osr and a.entity ='PARTY_SITES';
961 HZ_DQM_DUP_ID_PKG.update_detail_dqm_action_flag('PARTY_SITES',p_batch_id, x_ent_cur);
962 -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION ENDS ------------------------
963 FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
964 FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
965 FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
966
967
968
969 ---------- exception block ---------------
970 EXCEPTION
971 WHEN OTHERS THEN
972 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
973 FND_MESSAGE.SET_TOKEN('PROC','HZ_IMP_MATCH_RULE_51.interface_tca_join_entities');
974 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
975 FND_MSG_PUB.ADD;
976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
977 END interface_tca_join_entities;
978
979
980
981
982 ---------------------------------------------------------------
983 -------------------- INTERFACE JOIN BEGINS --------------------------
984 ---------------------------------------------------------------
985 PROCEDURE interface_join_entities(p_batch_id in number,
986 from_osr in varchar2, to_osr in varchar2, p_threshold in number)
987 IS
988 x_ent_cur HZ_DQM_DUP_ID_PKG.EntityCur;
989 x_insert_threshold number := 60;
990 BEGIN
991 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
992 FND_FILE.put_line(FND_FILE.log,'WU: '||from_osr||' to '||to_osr);
993 FND_FILE.put_line(FND_FILE.log,'Start time of insert of Parties '||to_char(sysdate,'hh24:mi:ss'));
994 insert into hz_int_dup_results(batch_id, f_osr,t_osr,ord_f_osr,ord_t_osr,score,f_os, t_os)
995 select p_batch_id, f, t, least(f,t), greatest(f,t), sum(score) score, fos, tos from (
996 ------------------ PARTY LEVEL DUPLICATE IDENTIFICATION BEGINS --------------------
997 select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
998 -------PARTY ENTITY: SCORING SECTION ---------
999 decode(instrb(s2.TX2,s1.TX2),1,80,
1000 decode(instrb(s2.TX59,s1.TX59),1,72,
1001 0
1002 )
1003 )
1004 +
1005 decode(instrb(s2.TX45,s1.TX45),1,200,
1006 0
1007 )
1008 score, s1.party_os fos, s2.party_os tos
1009 from HZ_SRCH_PARTIES s1, HZ_SRCH_PARTIES s2
1010 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1011 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)
1012 and (
1013 -------PARTY ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1014 -- do an or between all the transformations of an attribute --
1015 (
1016 (s1.TX45 is not null and s2.TX45 like s1.TX45 || decode(sign(lengthb(s1.TX45)-3),1,'%',''))
1017 )
1018 or
1019 -- do an or between all the transformations of an attribute --
1020 (
1021 (s1.TX59 is not null and s2.TX59 like s1.TX59 || decode(sign(lengthb(s1.TX59)-3),1,'%',''))
1022 )
1023 )
1024 union all
1025 select f, t, max(score) score, fos, tos from (
1026 select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1027 -------CONTACT_POINTS ENTITY: SCORING SECTION ---------
1028 decode(instrb(s2.TX5,s1.TX5),1,60,
1029 0
1030 )
1031 +
1032 decode(instrb(s2.TX158,s1.TX158),1,70,
1033 decode(instrb(s2.TX10,s1.TX10),1,70,
1034 0
1035 )
1036 )
1037 score, s1.party_os fos, s2.party_os tos
1038 from HZ_SRCH_CPTS s1, HZ_SRCH_CPTS s2
1039 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr and s1.party_osr <> s2.party_osr
1040 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)
1041 and s1.contact_point_type = s2.contact_point_type
1042 and (
1043 -------CONTACT_POINTS ENTITY: ACQUISITION ON NON-FILTER ATTRIBUTES ---------
1044 -- do an or between all the transformations of an attribute --
1045 (
1046 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
1047 )
1048 or
1049 -- do an or between all the transformations of an attribute --
1050 (
1051 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
1052 )
1053 )
1054 )
1055 group by f, t, fos, tos
1056 )
1057 ------- ONE TIME CHECK FOR PARTY LEVEL FILTER ATTRIBUTES---------
1058 where EXISTS (
1059 SELECT 1 FROM HZ_SRCH_PARTIES p1, HZ_SRCH_PARTIES p2
1060 WHERE p1.batch_id = p_batch_id and p1.party_osr = f and p1.party_os = fos
1061 and p2.batch_id = p_batch_id and p2.party_osr = t and p2.party_os = tos
1062 and
1063 -- do an or between all the transformations of an attribute --
1064 (
1065 ((p1.TX36 is null and p2.TX36 is null) or p2.TX36 = p1.TX36)
1066 )
1067 )
1068 group by f, t, fos, tos
1069 having sum(score) >= x_insert_threshold
1070 ;
1071 FND_FILE.put_line(FND_FILE.log,'Number of parties inserted '||SQL%ROWCOUNT);
1072 FND_FILE.put_line(FND_FILE.log,'End time of insert '||to_char(sysdate,'hh24:mi:ss'));
1073 FND_CONCURRENT.AF_Commit;
1074
1075
1076 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1077 FND_FILE.put_line(FND_FILE.log,'Beginning update of Parties on the basis of PARTY_SITES');
1078 FND_FILE.put_line(FND_FILE.log,'Start time of update '||to_char(sysdate,'hh24:mi:ss'));
1079 open x_ent_cur for
1080 select f,t,max(score) from (
1081 select /*+ USE_CONCAT */ s1.party_osr f, s2.party_osr t,
1082 decode(instrb(s2.TX26,s1.TX26),1,100,
1083 0
1084 )
1085 +
1086 decode(instrb(s2.TX9,s1.TX9),1,5,
1087 0
1088 )
1089 +
1090 decode(instrb(s2.TX14,s1.TX14),1,5,
1091 0
1092 )
1093 +
1094 decode(instrb(s2.TX22,s1.TX22),1,5,
1095 0
1096 )
1097 score
1098 from hz_int_dup_results h1, HZ_SRCH_PSITES s1, HZ_SRCH_PSITES s2
1099 where
1100 s1.party_osr = h1.f_osr and s2.party_osr = h1.t_osr and h1.batch_id = p_batch_id
1101 and s1.party_osr between from_osr and to_osr
1102 and (
1103 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1104 -- do an or between all the transformations of an attribute --
1105 (
1106 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
1107 )
1108 )
1109 ------------ FILTER ATTRIBUTES SECTION ------------------------
1110 and
1111 -- do an or between all the transformations of an attribute --
1112 (
1113 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
1114 )
1115 ) group by f,t ;
1116 HZ_DQM_DUP_ID_PKG.update_hz_int_dup_results(p_batch_id,x_ent_cur);
1117 close x_ent_cur;
1118 FND_FILE.put_line(FND_FILE.log,'Number of parties updated '||SQL%ROWCOUNT);
1119 FND_FILE.put_line(FND_FILE.log,'End time to update '||to_char(sysdate,'hh24:mi:ss'));
1120 FND_FILE.put_line(FND_FILE.log,'Ending update of Parties on the basis of PARTY_SITES');
1121 FND_CONCURRENT.AF_Commit;
1122 -------------CONTACT_POINTS LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1123 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1124 FND_FILE.put_line(FND_FILE.log,'Beginning insert of CONTACT_POINTS');
1125 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1126 insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1127 dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1128 dup_creation_date,dup_last_update_date
1129 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1130 select /*+ USE_CONCAT */ p_batch_id, s1.CONTACT_PT_OSR, s1.CONTACT_PT_OS,
1131 s2.CONTACT_PT_OSR, s2.CONTACT_PT_OS,
1132 s1.party_osr, s2.party_os,'CONTACT_POINTS',
1133 decode(nvl(s1.TX5,'N1'),nvl(substrb(s2.TX5,1,length(s1.TX5)),'N2'),60,
1134 0
1135 )
1136 +
1137 decode(nvl(s1.TX158,'N1'),nvl(substrb(s2.TX158,1,length(s1.TX158)),'N2'),70,
1138 decode(nvl(s1.TX10,'N1'),nvl(substrb(s2.TX10,1,length(s1.TX10)),'N2'),70,
1139 0
1140 )
1141 )
1142 score ,hz_utility_v2pub.creation_date, hz_utility_v2pub.last_update_date
1143 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
1144 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
1145 from HZ_SRCH_CPTS s1, HZ_SRCH_CPTS s2
1146 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
1147 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) )
1148 and s2.batch_id = p_batch_id and s1.CONTACT_PT_OSR < s2.CONTACT_PT_OSR
1149 and s1.contact_point_type = s2.contact_point_type
1150 and (
1151 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1152 -- do an or between all the transformations of an attribute --
1153 (
1154 (s1.TX158 is not null and s2.TX158 like s1.TX158 || decode(sign(lengthb(s1.TX158)-3),1,'%',''))
1155 )
1156 or
1157 -- do an or between all the transformations of an attribute --
1158 (
1159 (s1.TX5 is not null and s2.TX5 like s1.TX5 || decode(sign(lengthb(s1.TX5)-3),1,'%',''))
1160 )
1161 )
1162 ;
1163 FND_FILE.put_line(FND_FILE.log,'Ending insert of CONTACT_POINTS');
1164 FND_FILE.put_line(FND_FILE.log,'Number of records inserted '||SQL%ROWCOUNT);
1165 FND_FILE.put_line(FND_FILE.log,'End time to insert '||to_char(sysdate,'hh24:mi:ss'));
1166 FND_CONCURRENT.AF_Commit;
1167 -------------PARTY_SITES LEVEL DUPLICATE IDENTIFICATION BEGINS ------------------------
1168 FND_FILE.put_line(FND_FILE.log,'------------------------------------------------');
1169 FND_FILE.put_line(FND_FILE.log,'Beginning insert of PARTY_SITES');
1170 FND_FILE.put_line(FND_FILE.log,'Start time of insert '||to_char(sysdate,'hh24:mi:ss'));
1171 insert into hz_imp_int_dedup_results(batch_id, winner_record_osr, winner_record_os,
1172 dup_record_osr, dup_record_os, detail_party_osr, detail_party_os, entity, score,
1173 dup_creation_date,dup_last_update_date
1174 ,created_by,creation_date,last_update_login,last_update_date,last_updated_by)
1175 select /*+ USE_CONCAT */ p_batch_id, s1.PARTY_SITE_OSR, s1.PARTY_SITE_OS,
1176 s2.PARTY_SITE_OSR, s2.PARTY_SITE_OS,
1177 s1.party_osr, s2.party_os,'PARTY_SITES',
1178 decode(nvl(s1.TX26,'N1'),nvl(substrb(s2.TX26,1,length(s1.TX26)),'N2'),100,
1179 0
1180 )
1181 +
1182 decode(nvl(s1.TX9,'N1'),nvl(substrb(s2.TX9,1,length(s1.TX9)),'N2'),5,
1183 0
1184 )
1185 +
1186 decode(nvl(s1.TX14,'N1'),nvl(substrb(s2.TX14,1,length(s1.TX14)),'N2'),5,
1187 0
1188 )
1189 +
1190 decode(nvl(s1.TX22,'N1'),nvl(substrb(s2.TX22,1,length(s1.TX22)),'N2'),5,
1191 0
1192 )
1193 score ,hz_utility_v2pub.creation_date, hz_utility_v2pub.last_update_date
1194 ,hz_utility_v2pub.created_by,hz_utility_v2pub.creation_date,hz_utility_v2pub.last_update_login
1195 ,hz_utility_v2pub.last_update_date,hz_utility_v2pub.last_updated_by
1196 from HZ_SRCH_PSITES s1, HZ_SRCH_PSITES s2
1197 where s1.batch_id = p_batch_id and s1.party_osr between from_osr and to_osr
1198 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) )
1199 and s2.batch_id = p_batch_id and s1.PARTY_SITE_OSR < s2.PARTY_SITE_OSR
1200 and (
1201 ------------ NON FILTER ATTRIBUTES SECTION ------------------------
1202 -- do an or between all the transformations of an attribute --
1203 (
1204 (s1.TX26 is not null and s2.TX26 like s1.TX26 || decode(sign(lengthb(s1.TX26)-3),1,'%',''))
1205 )
1206 )
1207 ------------ FILTER ATTRIBUTES SECTION ------------------------
1208 and
1209 -- do an or between all the transformations of an attribute --
1210 (
1211 ((s1.TX11 is null and s2.TX11 is null) or s2.TX11 = s1.TX11)
1212 )
1213 ;
1214 FND_FILE.put_line(FND_FILE.log,'Ending insert of PARTY_SITES');
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 FND_CONCURRENT.AF_Commit;
1218
1219 ---------- exception block ---------------
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
1223 FND_MESSAGE.SET_TOKEN('PROC','HZ_IMP_MATCH_RULE_51.interface_join_entities');
1224 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
1225 FND_MSG_PUB.ADD;
1226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1227 END interface_join_entities;
1228 END;
[Home] [Help]