[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_DQM_STAGE
Source
1 PACKAGE BODY HZ_IMP_DQM_STAGE AS
2 /* $Header: ARHDISTB.pls 120.22.12020000.2 2012/11/28 09:09:39 amstephe ship $ */
3
4 /*
5 Developer Notes:
6
7 */
8 TYPE coltab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
9 l_trans_list coltab;
10
11 TYPE StageImpContactCurTyp IS REF CURSOR;
12 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_IMP_DQM_STAGE' ;
13
14
15 NO_STD_CHK CONSTANT INTEGER := 1;
16 DO_STD_CHK CONSTANT INTEGER := 2;
17 -- set to false during checkin.
18 is_test BOOLEAN := false;
19
20 PROCEDURE l(str VARCHAR2) IS
21 BEGIN
22 HZ_GEN_PLSQL.add_line(str);
23 END;
24
25 FUNCTION using_allow_cust(
26 p_match_rule_id IN NUMBER,
27 p_et_name IN VARCHAR2,
28 p_attr_name IN VARCHAR2
29 ) RETURN VARCHAR2 ;
30
31 PROCEDURE chk_et_req(p_entity_name IN VARCHAR2,
32 p_rule_id IN NUMBER,
33 x_bool IN OUT NOCOPY VARCHAR2);
34
35 FUNCTION chk_is_std(p_attribute_name IN VARCHAR2
36 ) RETURN VARCHAR2 ;
37
38 PROCEDURE get_table_name (
39 p_entity_name IN VARCHAR2,
40 p_table_name IN OUT NOCOPY VARCHAR2
41 );
42
43 PROCEDURE log(
44 message IN VARCHAR2,
45 newline IN BOOLEAN DEFAULT TRUE
46 );
47
48 /**
49 * Procedure to write a message to the out file
50 **/
51 PROCEDURE out(
52 message IN VARCHAR2,
53 newline IN BOOLEAN DEFAULT TRUE) IS
54 BEGIN
55 IF message = 'NEWLINE' THEN
56 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
57 ELSIF (newline) THEN
58 FND_FILE.put_line(fnd_file.output,message);
59 ELSE
60 FND_FILE.put(fnd_file.output,message);
61 END IF;
62 END out;
63
64
65 -----------------------------------------------------------------------
66 -- Function to fetch messages of the stack and log the error
67 -----------------------------------------------------------------------
68 PROCEDURE logerror(SQLERRM VARCHAR2 DEFAULT NULL)
69 IS
70 l_msg_data VARCHAR2(2000);
71 BEGIN
72 FND_MSG_PUB.Reset;
73 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
74 l_msg_data := substr(l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 2000) ;
75 END LOOP;
76 IF (SQLERRM IS NOT NULL) THEN
77 l_msg_data := substr(l_msg_data || SQLERRM, 1, 2000);
78 END IF;
79 log(l_msg_data);
80 END;
81
82
83 /**
84 * Procedure to write a message to the out and log files
85 **/
86 PROCEDURE outandlog(
87 message IN VARCHAR2,
88 newline IN BOOLEAN DEFAULT TRUE) IS
89 BEGIN
90 out(message, newline);
91 log(message);
92 END outandlog;
93
94 FUNCTION using_allow_cust(
95 p_match_rule_id IN NUMBER,
96 p_et_name IN VARCHAR2,
97 p_attr_name IN VARCHAR2
98 ) RETURN VARCHAR2
99 IS
100 using_allow_cust VARCHAR2(1) := 'N';
101 CURSOR c1 is select 'Y'
102 from hz_match_rule_primary a
103 where match_rule_id = p_match_rule_id
104 and a.attribute_id in (
105 select attribute_id
106 from hz_trans_attributes_b
107 where entity_name = p_et_name
108 and attribute_name = p_attr_name)
109 union
110 select 'Y'
111 from hz_match_rule_secondary a
112 where match_rule_id = p_match_rule_id
113 and a.attribute_id in (
114 select attribute_id
115 from hz_trans_attributes_b
116 where entity_name = p_et_name
117 and attribute_name = p_attr_name);
118
119 l_procedure_name VARCHAR2(30) := '.USING_ALLOW_CUST' ;
120 BEGIN
121 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
122 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
123 END IF;
124 OPEN c1;
125 LOOP
126 FETCH c1 into using_allow_cust;
127 EXIT WHEN c1%NOTFOUND;
128 END LOOP;
129 CLOSE c1;
130 RETURN using_allow_cust;
131 EXCEPTION WHEN OTHERS THEN
132 using_allow_cust := 'N';
133 END using_allow_cust;
134
135 FUNCTION get_os (p_batch_id IN NUMBER
136 ) RETURN VARCHAR2
137 IS
138 l_os VARCHAR2(30) ;
139 BEGIN
140 select original_system into l_os from hz_imp_batch_summary where batch_id = p_batch_id;
141 IF (l_os IS NULL) THEN
142 log(' hz_imp_batch_summary.original_system has return null. This indicates an error during batch setup.');
143 END IF;
144 RETURN l_os;
145 EXCEPTION WHEN others THEN
146 RAISE FND_API.G_EXC_ERROR;
147 END get_os;
148
149 PROCEDURE POP_INTERFACE_SEARCH_TAB (
150 p_batch_id IN NUMBER,
151 p_match_rule_id IN NUMBER,
152 p_from_osr IN VARCHAR2,
153 p_to_osr IN VARCHAR2,
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2
157 ) IS
158 l_sql_stmt VARCHAR2(255) ;
159 BEGIN
160 x_return_status := fnd_api.g_ret_sts_success;
161 hz_trans_pkg.set_bulk_dup_id ;
162 -- execute immediate ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_parties_int('|| p_batch_id ||',' || p_from_osr ||','|| p_to_osr||'); end;';
163 l_sql_stmt := ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_parties_int(:1, :2, :3); end;';
164 execute immediate l_sql_stmt using p_batch_id, p_from_osr, p_to_osr;
165 -- execute immediate ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_party_sites_int('|| p_batch_id ||',' || p_from_osr ||','|| p_to_osr||'); end;';
166 l_sql_stmt := ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_party_sites_int(:1, :2,:3); end;';
167 execute immediate l_sql_stmt using p_batch_id, p_from_osr, p_to_osr;
168 -- execute immediate ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_cp_int('|| p_batch_id ||',' || p_from_osr ||','|| p_to_osr||'); end;';
169 l_sql_stmt := ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_cp_int(:1, :2,:3); end;';
170 execute immediate l_sql_stmt using p_batch_id, p_from_osr, p_to_osr;
171 -- execute immediate ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_contacts_int('|| p_batch_id ||',' || p_from_osr ||','|| p_to_osr||'); end;';
172 l_sql_stmt := ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_contacts_int(:1, :2,:3); end;';
173 execute immediate l_sql_stmt using p_batch_id, p_from_osr, p_to_osr;
174
175 EXCEPTION WHEN others THEN
176 x_return_status := fnd_api.g_ret_sts_unexp_error;
177 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
178 FND_MESSAGE.SET_TOKEN('PROC' ,'POP_INTERFACE_SEARCH_TAB');
179 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
180 FND_MSG_PUB.ADD;
181 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
182 p_count => x_msg_count,
183 p_data => x_msg_data);
184 END POP_INTERFACE_SEARCH_TAB;
185
186 PROCEDURE POP_INT_TCA_SEARCH_TAB (
187 p_batch_id IN NUMBER,
188 p_match_rule_id IN NUMBER,
189 p_from_osr IN VARCHAR2,
190 p_to_osr IN VARCHAR2 ,
191 p_batch_mode_flag IN VARCHAR2,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2
195 ) IS
196
197 l_a VARCHAR2(1);
198 l_c NUMBER;
199 l_d NUMBER;
200 l_e VARCHAR2(1);
201 l_sql_stmt VARCHAR2(255); -- ????
202 BEGIN
203 log(' Start of staging for interface_tca POP_INT_TCA_SEARCH_TAB');
204 log(' p_batch_id = ' || p_batch_id);
205 log(' p_match_rule_id = '|| p_match_rule_id);
206 log(' p_from_osr = '|| p_from_osr);
207 log(' p_to_osr = ' || p_to_osr);
208 log(' p_batch_mode_flag = '|| p_batch_mode_flag);
209
210 x_return_status := fnd_api.g_ret_sts_success;
211 hz_trans_pkg.set_bulk_dup_id ;
212 select batch_dedup_flag, batch_dedup_match_rule_id, registry_dedup_match_rule_id, addr_val_flag
213 into l_a, l_c, l_d, l_e
214 from hz_imp_batch_summary
215 where batch_id = p_batch_id;
216 log ('l_a = '|| l_a);
217 log ('l_c = '|| l_c);
218 log ('l_d = '|| l_d);
219 log ('l_e = '|| l_e);
220 /*
221 l_a = was interface dedup run
222 l_b is interface_tca required
223 l_c = interface match rule id
224 l_d = interface_tca match rule id
225 l_e = address validation flag
226 */
227 IF ((l_a = 'Y') AND (l_c = l_d) ) THEN
228 IF (l_e = 'Y') THEN
229 log(' Restage party_sites entity due to address validation');
230 -- execute immediate ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_party_sites('|| p_batch_id ||',' || p_from_osr ||','|| p_to_osr||'); end;';
231 l_sql_stmt := ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_party_sites(:1 ,:2, :3, :4); end;';
232 execute immediate l_sql_stmt using p_batch_id, p_from_osr, p_to_osr, p_batch_mode_flag;
233 ELSE
234 -- - Update IDs on PARTY SITES
235 log( ' Update party_sites since address validation was not run');
236
237 update HZ_SRCH_PSITES c set party_site_id = ( select b.party_site_id
238 from hz_imp_addresses_int a, HZ_IMP_ADDRESSES_SG b
239 where a.rowid = b.int_row_id
240 and c.int_row_id = a.rowid
241 and a.batch_id = p_batch_id
242 and a.batch_id = c.batch_id
243 and b.action_flag = 'I' ),
244 party_id = ( select b.party_id
245 from hz_imp_addresses_int a, HZ_IMP_ADDRESSES_SG b
246 where a.rowid = b.int_row_id
247 and c.int_row_id = a.rowid
248 and a.batch_id = p_batch_id
249 and a.batch_id = c.batch_id
250 and b.action_flag = 'I'
251 ) where batch_id = p_batch_id;
252 END IF;
253 -- - Update IDs on PARTY, CONTACTS, CONTACT_POINTS
254 log( ' Update party as same match rule being used');
255 update hz_srch_parties c set party_id = ( select b.party_id
256 from hz_imp_parties_int a, hz_imp_parties_sg b
257 where a.rowid = b.int_row_id
258 and c.int_row_id = a.rowid
259 and a.batch_id = p_batch_id
260 and a.batch_id = c.batch_id
261 and b.action_flag = 'I' );
262 log( ' Update contacts as same match rule being used');
263 update HZ_SRCH_CONTACTS c set party_id = ( select b.sub_id
264 from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b
265 where a.rowid = b.int_row_id
266 and c.int_row_id = a.rowid
267 and a.batch_id = p_batch_id
268 and a.batch_id = c.batch_id
269 and b.action_flag = 'I' ),
270 org_contact_id = (select b.contact_id
271 from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b
272 where a.rowid = b.int_row_id
273 and a.batch_id = p_batch_id
274 and a.batch_id = c.batch_id
275 and b.action_flag = 'I' );
276 log( ' Update contact_points as same match rule being used');
277 update HZ_SRCH_CPTS c set party_id = ( select b.party_id
278 from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b
279 where a.rowid = b.int_row_id
280 and c.int_row_id = a.rowid
281 and a.batch_id = p_batch_id
282 and a.batch_id = c.batch_id
283 and b.action_flag = 'I' ),
284 party_site_id = ( select b.party_site_id
285 from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b
286 where a.rowid = b.int_row_id
287 and c.int_row_id = a.rowid
288 and a.batch_id = p_batch_id
289 and a.batch_id = c.batch_id
290 and b.action_flag = 'I' ),
291 contact_point_id = ( select b.contact_point_id
292 from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b
293 where a.rowid = b.int_row_id
294 and c.int_row_id = a.rowid
295 and a.batch_id = p_batch_id
296 and a.batch_id = c.batch_id
297 and b.action_flag = 'I' );
298 ELSE
299 log(' Restage all four entities ');
300 -- execute immediate ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_parties('|| p_batch_id ||',' || p_from_osr ||','|| p_to_osr||','||p_batch_mode_flag||'); end;';
301 l_sql_stmt := ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_parties(:1 ,:2, :3, :4); end;';
302 execute immediate l_sql_stmt using p_batch_id, p_from_osr, p_to_osr, p_batch_mode_flag;
303 -- execute immediate ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_party_sites('|| p_batch_id ||',' || p_from_osr ||','|| p_to_osr||'); end;';
304 l_sql_stmt := ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_party_sites(:1 ,:2, :3, :4); end;';
305 execute immediate l_sql_stmt using p_batch_id, p_from_osr, p_to_osr, p_batch_mode_flag;
306 -- execute immediate ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_cp('|| p_batch_id ||',' || p_from_osr ||','|| p_to_osr||'); end;';
307 l_sql_stmt := ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_cp(:1 ,:2, :3, :4); end;';
308 execute immediate l_sql_stmt using p_batch_id, p_from_osr, p_to_osr, p_batch_mode_flag;
309 -- execute immediate ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_contacts('|| p_batch_id ||',' || p_from_osr ||','|| p_to_osr||'); end;';
310 l_sql_stmt := ' begin HZ_IMP_MATCH_RULE_'||p_match_rule_id||'.pop_contacts(:1 ,:2, :3, :4); end;';
311 execute immediate l_sql_stmt using p_batch_id, p_from_osr, p_to_osr, p_batch_mode_flag;
312 END IF;
313 log('End of staging for interface_tca POP_INT_TCA_SEARCH_TAB');
314 EXCEPTION WHEN others THEN
315 x_return_status := fnd_api.g_ret_sts_unexp_error;
316 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
317 FND_MESSAGE.SET_TOKEN('PROC' ,'POP_INT_TCA_SEARCH_TAB');
318 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
319 FND_MSG_PUB.ADD;
320 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
321 p_count => x_msg_count,
322 p_data => x_msg_data);
323 END POP_INT_TCA_SEARCH_TAB;
324
325 FUNCTION has_trx_context(proc VARCHAR2) RETURN BOOLEAN IS
326
327 l_sql VARCHAR2(255);
328 l_entity VARCHAR2(255);
329 l_procedure VARCHAR2(255);
330 l_attribute VARCHAR2(255);
331 c NUMBER;
332 n NUMBER;
333 l_custom BOOLEAN;
334
335 BEGIN
336 c := dbms_sql.open_cursor;
337 l_sql := 'select ' || proc ||
338 '(:attrval,:lang,:attr,:entity,:ctx) from dual';
339 dbms_sql.parse(c,l_sql,2);
340 DBMS_SQL.BIND_VARIABLE(c,':attrval','x');
341 DBMS_SQL.BIND_VARIABLE(c,':lang','x');
342 DBMS_SQL.BIND_VARIABLE(c,':attr','x');
343 DBMS_SQL.BIND_VARIABLE(c,':entity','x');
344 DBMS_SQL.BIND_VARIABLE(c,':ctx','x');
345 n:=DBMS_SQL.execute(c);
346 dbms_sql.close_cursor(c);
347 RETURN TRUE;
348 EXCEPTION
349 WHEN OTHERS THEN
350 dbms_sql.close_cursor(c);
351 RETURN FALSE;
352 END;
353
354 FUNCTION chk_for_trunc (
355 p_table_name IN VARCHAR2,
356 p_batch_id IN NUMBER
357 ) RETURN VARCHAR2 IS
358 l_procedure_name VARCHAR2(30) := '.CHK_FOR_TRUNC' ;
359 l_bool VARCHAR2(1) := 'N';
360 l_sql_stmt VARCHAR2(255);
361 l_count NUMBER := 0;
362 BEGIN
363 l_sql_stmt := ' select /*+ INDEX(a) */ count(batch_id) from ' || p_table_name || ' a where batch_id <> :1 and rownum < 2 ';
364 execute immediate l_sql_stmt into l_count using p_batch_id;
365 IF (l_count <= 0) THEN
366 l_bool := 'Y';
367 ELSE
368 l_bool := 'N';
369 END IF;
370 RETURN l_bool;
371 EXCEPTION WHEN OTHERS THEN
372 l_bool := 'N';
373 RETURN l_bool;
374 END chk_for_trunc;
375
376 PROCEDURE chk_table (
377 p_table_name IN VARCHAR2,
378 p_batch_id IN NUMBER
379 ) IS
380 l_procedure_name VARCHAR2(30) := '.CHK_TABLE' ;
381 l_bool VARCHAR2(1) := 'N';
382 l_owner VARCHAR2(30);
383 l_sql_stmt VARCHAR2(255);
384 BEGIN
385 l_bool := chk_for_trunc(p_table_name, p_batch_id);
386 l_sql_stmt := ' delete from ' || p_table_name || ' where batch_id = :1 ' ;
387 IF (l_bool = 'Y') THEN
388 BEGIN
389 l_owner := get_owner_name(p_table_name, 'TABLE');
390 log ('Attempting to truncate table ' || p_table_name);
391 execute immediate ' truncate table ' || l_owner || '.' || p_table_name;
392 EXCEPTION WHEN OTHERS THEN
393 log(SQLERRM);
394 log('Exception thrown possibly due to lock on table. Unable to truncate hence deleting data for batch_id ' || p_batch_id ||'Deletion in progress...');
395 execute immediate l_sql_stmt using p_batch_id;
396 END;
397 ELSE
398 execute immediate l_sql_stmt using p_batch_id;
399 END IF;
400 EXCEPTION WHEN OTHERS THEN
401 log(SQLERRM);
402 RAISE FND_API.G_EXC_ERROR;
403 END chk_table;
404
405 PROCEDURE chk_srch_tab ( p_batch_id IN NUMBER
406 ) IS
407 l_owner VARCHAR2(255);
408 l_table_name VARCHAR2(30);
409 l_sql_stmt VARCHAR2(255);
410 BEGIN
411 chk_table('HZ_SRCH_PARTIES', p_batch_id);
412 chk_table('HZ_SRCH_PSITES', p_batch_id);
413 chk_table('HZ_SRCH_CONTACTS', p_batch_id);
414 chk_table('HZ_SRCH_CPTS', p_batch_id);
415 EXCEPTION WHEN OTHERS THEN
416 RAISE FND_API.G_EXC_ERROR;
417 END chk_srch_tab;
418
419 PROCEDURE dqm_post_imp_cleanup (
420 p_batch_id IN NUMBER,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_count OUT NOCOPY NUMBER,
423 x_msg_data OUT NOCOPY VARCHAR2
424 ) IS
425 l_procedure_name VARCHAR2(30) := '.DQM_POST_IMP_CLEANUP' ;
426 BEGIN
427 x_return_status := fnd_api.g_ret_sts_success;
428 log(' HZ_DQM_DUP_ID_PKG.final_process_int_tca_dup_id(' || p_batch_id || '); +');
429 HZ_DQM_DUP_ID_PKG.final_process_int_tca_dup_id(p_batch_id);
430 log(' HZ_DQM_DUP_ID_PKG.final_process_int_tca_dup_id(' || p_batch_id || '); -');
431 chk_srch_tab(p_batch_id);
432 delete from HZ_INT_DUP_RESULTS where batch_id = p_batch_id;
433 EXCEPTION WHEN OTHERS THEN
434 log(SQLERRM);
435 x_return_status := fnd_api.g_ret_sts_unexp_error;
436 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
437 FND_MESSAGE.SET_TOKEN('PROC' ,'DQM_POST_IMP_CLEANUP');
438 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
439 FND_MSG_PUB.ADD;
440 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
441 p_count => x_msg_count,
442 p_data => x_msg_data);
443 END dqm_post_imp_cleanup;
444
445 PROCEDURE del_from_table (p_table_name IN VARCHAR2,
446 p_batch_id IN NUMBER) IS
447
448 l_sql_stmt VARCHAR2(255);
449 l_owner VARCHAR2(30);
450 BEGIN
451 l_owner := get_owner_name(p_table_name, 'TABLE');
452 l_sql_stmt := ' delete from ' ||l_owner || '.' || p_table_name || ' where batch_id = :1 ';
453 execute immediate l_sql_stmt using p_batch_id;
454 EXCEPTION WHEN OTHERS THEN
455 log(SQLERRM);
456 RAISE FND_API.G_EXC_ERROR;
457 END del_from_table;
458
459
460
461 PROCEDURE dqm_pre_imp_cleanup (
462 p_batch_id IN NUMBER,
463 x_return_status OUT NOCOPY VARCHAR2,
464 x_msg_count OUT NOCOPY NUMBER,
465 x_msg_data OUT NOCOPY VARCHAR2
466 ) IS
467 l_procedure_name VARCHAR2(30) := '.DQM_PRE_IMP_CLEANUP' ;
468 l_owner VARCHAR2(255);
469 l_table_name VARCHAR2(30);
470 l_sql_stmt VARCHAR2(255);
471 BEGIN
472 x_return_status := fnd_api.g_ret_sts_success;
473 chk_srch_tab(p_batch_id);
474 delete from HZ_IMP_INT_DEDUP_RESULTS where batch_id = p_batch_id;
475 delete from HZ_IMP_DUP_PARTIES where batch_id = p_batch_id;
476 delete from HZ_IMP_DUP_DETAILS where batch_id = p_batch_id;
477 EXCEPTION WHEN OTHERS THEN
478 log(SQLERRM);
479 x_return_status := fnd_api.g_ret_sts_unexp_error;
480 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
481 FND_MESSAGE.SET_TOKEN('PROC' ,'DQM_PRE_IMP_CLEANUP');
482 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
483 FND_MSG_PUB.ADD;
484 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
485 p_count => x_msg_count,
486 p_data => x_msg_data);
487 END dqm_pre_imp_cleanup;
488
489 FUNCTION get_owner_name (
490 p_object_name IN VARCHAR2,
491 p_object_type IN VARCHAR2
492 ) RETURN VARCHAR2 IS
493 l_owner VARCHAR2(30);
494 l_status VARCHAR2(255);
495 l_owner1 VARCHAR2(255);
496 l_temp VARCHAR2(255);
497 BEGIN
498 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner1)) THEN
499 select owner into l_owner from sys.all_objects
500 where object_name = p_object_name and OBJECT_TYPE = p_object_type and owner = l_owner1;
501 END IF;
502 RETURN l_owner;
503 EXCEPTION WHEN OTHERS THEN
504 log(SQLERRM);
505 RAISE FND_API.G_EXC_ERROR;
506 END get_owner_name;
507
508 /*
509 PROCEDURE del_existing_rec(p_et_name IN VARCHAR,
510 p_batch_id IN NUMBER
511 ) IS
512
513 BEGIN
514 IF (p_et_name = 'PSITES') THEN
515 delete from hz_srch_psites a
516 where a.party_os || a.party_osr in (
517 select a.party_os || a.party_osr
518 from HZ_IMP_ADDRESSES_SG b
519 where a.party_os = b.party_orig_system
520 and a.party_osr = b.party_orig_system_reference
521 and b.action_flag = 'U'
522 and a.batch_id = p_batch_id);
523 ELSIF (p_et_name = 'CONTACTS') THEN
524 delete from HZ_SRCH_CONTACTS c where c.contact_os ||c.contact_osr || c.batch_id in(
525 select b.contact_orig_system || b.contact_orig_system_reference || b.batch_id
526 from HZ_IMP_CONTACTS_SG a, HZ_IMP_CONTACTS_INT b
527 where a.int_row_id = b.rowid
528 and a.action_flag = 'U'
529 and b.contact_orig_system = c.contact_os
530 and b.contact_orig_system_reference = c.contact_osr
531 and b.batch_id = p_batch_id);
532 ELSIF (p_et_name = 'CP') THEN
533 delete from HZ_SRCH_CPTS a
534 where a.party_os || a.party_osr in (
535 select a.party_os || a.party_osr
536 from HZ_IMP_CONTACTPTS_SG b
537 where a.party_os = b.party_orig_system
538 and a.party_osr = b.party_orig_system_reference
539 and b.action_flag = 'U'
540 and a.batch_id = p_batch_id);
541 END IF;
542 END del_existing_rec;
543 */
544
545 PROCEDURE dqm_inter_imp_cleanup (
546 p_batch_id IN NUMBER,
547 x_return_status OUT NOCOPY VARCHAR2,
548 x_msg_count OUT NOCOPY NUMBER,
549 x_msg_data OUT NOCOPY VARCHAR2
550 ) IS
551 l_procedure_name VARCHAR2(30) := '.DQM_INTER_IMP_CLEANUP' ;
552 /*
553 l_a = was interface dedup run
554 l_b is interface_tca required
555 l_c = interface match rule id
556 l_d = interface_tca match rule id
557 l_e = address validation flag
558 */
559 l_a VARCHAR2(1);
560 l_b VARCHAR2(1);
561 l_c NUMBER;
562 l_d NUMBER;
563 l_e VARCHAR2(1);
564 l_owner VARCHAR2(255);
565 l_table_name VARCHAR2(30);
566 l_status VARCHAR2(255);
567 l_owner1 VARCHAR2(255);
568 l_temp VARCHAR2(255);
569 l_sqlstr VARCHAR2(4000);
570
571 BEGIN
572 x_return_status := fnd_api.g_ret_sts_success;
573 select batch_dedup_flag, registry_dedup_flag, batch_dedup_match_rule_id, registry_dedup_match_rule_id, addr_val_flag
574 into l_a, l_b, l_c, l_d, l_e
575 from hz_imp_batch_summary
576 where batch_id = p_batch_id;
577
578
579 IF (l_b = 'Y') THEN
580 IF ((l_a = 'Y') AND (l_c = l_d) ) THEN
581 IF (l_e = 'Y') THEN
582 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner1)) THEN
583 /*select owner into l_owner from sys.all_objects
584 where object_name = 'HZ_SRCH_PSITES' and OBJECT_TYPE = 'TABLE' and owner = l_owner1 ;*/
585 --Bug:4956084
586 l_sqlstr := 'select owner from sys.all_tables
587 where table_name = ''HZ_SRCH_PSITES'' and owner = :p_owner ';
588 execute immediate l_sqlstr into l_owner USING l_owner1 ;
589 l_table_name := l_owner || '.HZ_SRCH_PSITES';
590 chk_table(l_table_name, p_batch_id);
591 END IF;
592 END IF;
593 ELSIF (l_a = 'Y') THEN
594 chk_srch_tab(p_batch_id);
595 -- delete from all three tables
596 /* del_existing_rec('CONTACTS', p_batch_id);
597 del_existing_rec('CP', p_batch_id);
598 del_existing_rec('PSITES', p_batch_id); */
599 END IF;
600 END IF;
601 EXCEPTION WHEN OTHERS THEN
602 log(SQLERRM); --
603 x_return_status := fnd_api.g_ret_sts_unexp_error;
604 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
605 FND_MESSAGE.SET_TOKEN('PROC' ,'DQM_INTER_IMP_CLEANUP');
606 FND_MESSAGE.SET_TOKEN('ERROR' , SQLERRM);
607 FND_MSG_PUB.ADD;
608 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
609 p_count => x_msg_count,
610 p_data => x_msg_data);
611 END dqm_inter_imp_cleanup;
612
613 /**
614 * Procedure to write a message to the log file
615 **/
616 PROCEDURE log(
617 message IN VARCHAR2,
618 newline IN BOOLEAN DEFAULT TRUE
619 ) IS
620 BEGIN
621 IF message = 'NEWLINE' THEN
622 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
623 ELSIF (newline) THEN
624 FND_FILE.put_line(fnd_file.log,message);
625 ELSE
626 FND_FILE.put(fnd_file.log,message);
627 END IF;
628 END log;
629
630
631 PROCEDURE interface_dup_id_worker (
632 retcode OUT NOCOPY VARCHAR2,
633 err OUT NOCOPY VARCHAR2,
634 p_batch_id IN VARCHAR2,
635 p_match_rule_id IN VARCHAR2,
636 p_worker_num IN VARCHAR2,
637 p_num_of_workers IN VARCHAR2,
638 p_phase IN OUT NOCOPY VARCHAR2
639 ) IS
640
641 l_to_osr VARCHAR2(30);
642 l_from_osr VARCHAR2(30);
643 x_return_status VARCHAR2(30);
644 x_msg_count NUMBER;
645 x_msg_data VARCHAR2(255);
646 l_row_id VARCHAR2(100);
647
648 BEGIN
649 x_return_status := fnd_api.g_ret_sts_success;
650 LOOP
651 log(' Processing worker ' || p_worker_num );
652 l_row_id := NULL;
653 IF (p_phase = 'PHASE_1') THEN
654 UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'P', BATCH_DEDUP_STAGE = 1
655 WHERE batch_id = p_batch_id and BATCH_DEDUP_STATUS is null and rownum = 1
656 RETURNING rowid, FROM_ORIG_SYSTEM_REF, TO_ORIG_SYSTEM_REF into l_row_id, l_from_osr, l_to_osr;
657 IF (l_row_id IS NULL) THEN
658 EXIT;
659 END IF;
660 COMMIT;
661 log('calling HZ_IMP_DQM_STAGE.POP_INTERFACE_SEARCH_TAB');
662 log ('p_batch_id = ' || p_batch_id);
663 log ('p_match_rule_id = ' || p_match_rule_id);
664 log ('l_from_osr = ' || l_from_osr);
665 log ('l_to_osr = ' || l_to_osr);
666 HZ_IMP_DQM_STAGE.POP_INTERFACE_SEARCH_TAB(to_number(p_batch_id),
667 to_number(p_match_rule_id), l_from_osr, l_to_osr,
668 x_return_status, x_msg_count, x_msg_data);
669
670 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
671 retcode := 2;
672 err := x_msg_data;
673 logerror;
674 UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'E' where rowid = l_row_id;
675 RAISE FND_API.G_EXC_ERROR;
676 ELSE
677 UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'C' where rowid = l_row_id;
678 END IF;
679 COMMIT;
680 ELSIF (p_phase = 'PHASE_2') THEN
681 UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'P', BATCH_DEDUP_STAGE = 2
682 WHERE batch_id = p_batch_id AND BATCH_DEDUP_STAGE = 1 and ROWNUM = 1
683 RETURNING rowid, FROM_ORIG_SYSTEM_REF, TO_ORIG_SYSTEM_REF into l_row_id, l_from_osr, l_to_osr;
684 IF (l_row_id IS NULL) THEN
685 EXIT;
686 END IF;
687 COMMIT;
688 HZ_DQM_DUP_ID_PKG.interface_dup_id_worker(p_batch_id, p_match_rule_id, l_from_osr, l_to_osr, x_return_status, x_msg_count, x_msg_data);
689 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
690 retcode := 2;
691 err := x_msg_data;
692 logerror;
693 UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'E' WHERE ROWID = l_row_id;
694 RAISE FND_API.G_EXC_ERROR;
695 ELSE
696 UPDATE hz_imp_work_units SET BATCH_DEDUP_STATUS = 'C' WHERE ROWID = l_row_id;
697 END IF;
698 COMMIT;
699 END IF;
700 END LOOP;
701 EXCEPTION WHEN OTHERS THEN
702 retcode := 2;
703 err := SQLERRM;
704 log ('Error ::'|| err);
705 RAISE FND_API.G_EXC_ERROR;
706 END interface_dup_id_worker;
707
708
709 PROCEDURE interface_dup_id(
710 retcode OUT NOCOPY VARCHAR2,
711 err OUT NOCOPY VARCHAR2,
712 p_batch_id IN VARCHAR2,
713 p_match_rule_id IN VARCHAR2,
714 p_num_of_workers IN VARCHAR2
715 ) IS
716 req_data VARCHAR2(30);
717 TYPE nTable IS TABLE OF NUMBER index by binary_integer;
718 l_sub_requests nTable;
719 x_return_status VARCHAR2(1) ;
720 x_msg_count NUMBER;
721 x_msg_data VARCHAR2(255);
722
723 x_dup_batch_id NUMBER;
724 l_state boolean := true;
725 l_sub FND_CONCURRENT.REQUESTS_TAB_TYPE;
726 call_status boolean;
727 l_conc_phase VARCHAR2(80);
728 l_conc_status VARCHAR2(80);
729 l_conc_dev_phase VARCHAR2(30);
730 l_conc_dev_status VARCHAR2(30);
731 l_message VARCHAR2(240);
732 l_workers_completed boolean ;
733 BEGIN
734 /* DQM - Batch De-duplication Program
735 Writes to - HZ_IMP_BATCH_SUMMARY.BATCH_DEDUP_STATUS
736 Values -
737 PROCESSING - When program starts the process
738 COMPLETED - When program ends successfully. Also write all the DQM Count columns in this case.
739 ERROR - When program encounters some error
740 */
741 req_data := fnd_conc_global.request_data;
742 log('req_data = '|| req_data);
743 IF ( req_data IS NULL) THEN
744 -- code for validation. needs to be completed ????
745 /*
746 l_bool := if_correct_batch(p_batch_id);
747 IF l_bool THEN
748 l_bool := if_correct_rule(p_match_rule_id);
749 IF (l_bool) THEN
750 l_bool := if_correct_worker_num( p_num_of_workers);
751 ELSE
752 log (' Please pass worker number between 0 and 100');
753 END IF;
754 ELSE
755 log ('Please check the match rule you passed');
756 END IF;
757 */
758
759 chk_table('HZ_INT_DUP_RESULTS', p_batch_id);
760 UPDATE HZ_IMP_BATCH_SUMMARY set BATCH_DEDUP_STATUS = 'PROCESSING' where batch_id = p_batch_id;
761 COMMIT;
762 -- HZ_IMP_DQM_STAGE.dqm_pre_imp_cleanup(p_batch_id, x_return_status, x_msg_count, x_msg_data);
763 FOR I in 1..to_number(p_num_of_workers) LOOP
764 log('I = ' || I);
765 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHDIDIW',
766 'Interface Duplicate Identification Worker' || to_char(i),
767 to_char(sysdate,'DD-MON-YY HH:MI:SS'),
768 true, p_batch_id, p_match_rule_id, to_char(I), p_num_of_workers, 'PHASE_1');
769 IF l_sub_requests(i) = 0 THEN
770 log('Error submitting worker ' || i);
771 log(fnd_message.get);
772 retcode := 2;
773 RAISE FND_API.G_EXC_ERROR;
774 ELSE
775 log('Submitted request for Worker ' || TO_CHAR(I) );
776 log('Request ID : ' || l_sub_requests(i));
777 END IF;
778 EXIT when l_sub_requests(i) = 0;
779 END LOOP;
780 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => 'STAGING') ;
781 err := 'Concurrent Workers submitted.';
782 retcode := 0;
783 ELSIF ( req_data = 'STAGING') THEN
784 -- adding below for better error handling, parent should denote the error status if any child fails.
785 -- AFTER ALL THE WORKERS ARE DONE, SEE IF THEY HAVE ALL COMPLETED NORMALLY
786 -- assume that all concurrent dup workers completed normally, unless found otherwise
787 l_workers_completed := TRUE;
788 select request_id BULK COLLECT into l_sub_requests
789 from fnd_concurrent_requests R
790 where parent_request_id = FND_GLOBAL.conc_request_id
791 and (phase_code<>'C' or status_code<>'C');
792 IF l_sub_requests.count > 0 THEN
793 l_workers_completed := FALSE;
794 FOR I in 1..l_sub_requests.COUNT LOOP
795 outandlog('Worker with request id ' || l_sub_requests(I) );
796 outandlog('Did not complete normally.');
797 retcode := 2;
798 log(' retcode = ' || retcode);
799 RAISE FND_API.G_EXC_ERROR;
800 END LOOP;
801 END IF;
802 log('p_rule_id '||p_match_rule_id);
803 IF l_workers_completed THEN
804 FOR I in 1..to_number(p_num_of_workers) LOOP
805 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHDIDIW',
806 'Interface Duplicate Identification Worker' || to_char(i),
807 to_char(sysdate,'DD-MON-YY HH:MI:SS'),
808 true, p_batch_id, p_match_rule_id, to_char(I), p_num_of_workers, 'PHASE_2');
809 IF l_sub_requests(i) = 0 THEN
810 log('Error submitting worker ' || i);
811 log(fnd_message.get);
812 ELSE
813 log('Submitted request for Worker ' || TO_CHAR(I) );
814 log('Request ID : ' || l_sub_requests(i));
815 END IF;
816 EXIT when l_sub_requests(i) = 0;
817 END LOOP;
818 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => 'EXIT') ;
819 err := 'Concurrent Workers submitted.';
820 retcode := 0;
821 END IF;
822 ELSIF ( req_data = 'EXIT') THEN
823 -- adding below for better error handling, parent should denote the error status if any child fails.
824 -- AFTER ALL THE WORKERS ARE DONE, SEE IF THEY HAVE ALL COMPLETED NORMALLY
825 -- assume that all concurrent dup workers completed normally, unless found otherwise
826 l_workers_completed := TRUE;
827 select request_id BULK COLLECT into l_sub_requests
828 from fnd_concurrent_requests R
829 where parent_request_id = FND_GLOBAL.conc_request_id
830 and (phase_code<>'C' or status_code<>'C');
831 IF l_sub_requests.count > 0 THEN
832 l_workers_completed := FALSE;
833 FOR I in 1..l_sub_requests.COUNT LOOP
834 outandlog('Worker with request id ' || l_sub_requests(I) );
835 outandlog('Did not complete normally');
836 retcode := 2;
837 log(' retcode = ' || retcode);
838 RAISE FND_API.G_EXC_ERROR;
839 END LOOP;
840 END IF;
841 log('p_rule_id '||p_match_rule_id);
842 IF l_workers_completed THEN
843 HZ_DQM_DUP_ID_PKG.interface_sanitize_report(p_batch_id,
844 p_match_rule_id, x_return_status, x_msg_count, x_msg_data);
845
846 -- update hz_imp_batch_summary set est_no_matches = $no where batch_id = p_batch_id;
847 UPDATE HZ_IMP_BATCH_SUMMARY set BATCH_DEDUP_STATUS = 'COMPLETED' where batch_id = p_batch_id;
848 COMMIT;
849 chk_table('HZ_INT_DUP_RESULTS', p_batch_id);
850 END IF;
851 END IF;
852 EXCEPTION WHEN OTHERS THEN
853 retcode := 2;
854 log(' retcode . = ' || retcode);
855 UPDATE HZ_IMP_BATCH_SUMMARY set BATCH_DEDUP_STATUS = 'ERROR' where batch_id = p_batch_id;
856 COMMIT;
857 RAISE FND_API.G_EXC_ERROR;
858 END interface_dup_id;
859
860 PROCEDURE gen_pkg_spec (
861 p_pkg_name IN VARCHAR2,
862 p_rule_id IN NUMBER
863 ) IS
864 l_procedure_name VARCHAR2(30) := '.GEN_PKG_SPEC' ;
865 BEGIN
866 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
867 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter p_pkg_name='||p_pkg_name || ' p_rule_id='|| p_rule_id);
868 END IF;
869 IF is_test THEN
870 l('CREATE or REPLACE PACKAGE ' || p_pkg_name || ' AUTHID CURRENT_USER AS'); -- this
871 END IF;
872 l(' PROCEDURE pop_parties( ');
873 l(' p_batch_id IN NUMBER, ');
874 l(' p_from_osr IN VARCHAR2, ');
875 l(' p_to_osr IN VARCHAR2, ');
876 l(' p_batch_mode_flag IN VARCHAR2 ); ');
877 l(' ');
878 l(' PROCEDURE pop_party_sites ( ');
879 l(' p_batch_id IN NUMBER, ');
880 l(' p_from_osr IN VARCHAR2, ');
881 l(' p_to_osr IN VARCHAR2, ');
882 l(' p_batch_mode_flag IN VARCHAR2 ); ');
883 l(' ');
884 l(' PROCEDURE pop_cp ( ');
885 l(' p_batch_id IN NUMBER, ');
886 l(' p_from_osr IN VARCHAR2, ');
887 l(' p_to_osr IN VARCHAR2, ');
888 l(' p_batch_mode_flag IN VARCHAR2 ); ');
889 l(' ');
890 l(' PROCEDURE pop_contacts ( ');
891 l(' p_batch_id IN NUMBER, ');
892 l(' p_from_osr IN VARCHAR2, ');
893 l(' p_to_osr IN VARCHAR2, ');
894 l(' p_batch_mode_flag IN VARCHAR2 ); ');
895 l(' ');
896 l(' PROCEDURE pop_parties_int ( ');
897 l(' p_batch_id IN NUMBER, ');
898 l(' p_from_osr IN VARCHAR2, ');
899 l(' p_to_osr IN VARCHAR2 );');
900 l(' ');
901 l(' PROCEDURE pop_party_sites_int ( ');
902 l(' p_batch_id IN NUMBER, ');
903 l(' p_from_osr IN VARCHAR2, ');
904 l(' p_to_osr IN VARCHAR2 );');
905 l(' ');
906 l(' PROCEDURE pop_cp_int ( ');
907 l(' p_batch_id IN NUMBER, ');
908 l(' p_from_osr IN VARCHAR2, ');
909 l(' p_to_osr IN VARCHAR2 );');
910 l(' ');
911 l(' PROCEDURE pop_contacts_int ( ');
912 l(' p_batch_id IN NUMBER, ');
913 l(' p_from_osr IN VARCHAR2, ');
914 l(' p_to_osr IN VARCHAR2 );');
915 l(' ');
916 IF is_test THEN
917 l('END ' || p_pkg_name || ';'); -- this
918 END IF;
919 EXCEPTION WHEN OTHERS THEN
920 RAISE FND_API.G_EXC_ERROR;
921 END gen_pkg_spec;
922
923
924 PROCEDURE get_table_name (
925 p_entity_name IN VARCHAR2,
926 p_table_name IN OUT NOCOPY VARCHAR2
927 ) IS
928 l_procedure_name VARCHAR2(30) := 'GET_TABLE_NAME';
929 BEGIN
930 IF ((p_entity_name = 'PARTY') OR (p_entity_name = 'HZ_STAGED_PARTIES')) THEN
931 p_table_name := 'HZ_IMP_PARTIES_INT';
932 ELSIF ((p_entity_name = 'PARTY_SITES') OR (p_entity_name = 'HZ_STAGED_PARTY_SITES')) THEN
933 p_table_name := 'HZ_IMP_ADDRESSES_INT';
934 ELSIF ((p_entity_name = 'CONTACTS') OR (p_entity_name = 'HZ_STAGED_CONTACTS')) THEN
935 p_table_name := 'HZ_IMP_CONTACTS_INT';
936 ELSIF ((p_entity_name = 'CONTACT_POINTS') OR (p_entity_name = 'HZ_STAGED_CONTACT_POINTS')) THEN
937 p_table_name := 'HZ_IMP_CONTACTPTS_INT';
938 ELSE
939 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
940 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_entity_name='||p_entity_name);
941 END IF;
942 END IF;
943 END get_table_name;
944
945 PROCEDURE get_select_str(
946 p_entity_name IN VARCHAR2,
947 p_rule_id IN NUMBER,
948 p_sql_str IN OUT NOCOPY VARCHAR2,
949 p_et_point IN VARCHAR2,
950 p_std_chk IN NUMBER
951 ) IS
952 is_first BOOLEAN := TRUE;
953 l_procedure_name VARCHAR2(30) := '.GET_SELECT_STR' ;
954 l_table_name VARCHAR2(30);
955 is_using_allow_cust_attr VARCHAR2(1) := 'N';
956
957 BEGIN
958 get_table_name(p_entity_name, l_table_name);
959 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
960 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
961 END IF;
962 -- FOR TX IN (select decode(attribute_name, 'PARTY_NAME', 'ORGANIZATION_NAME', attribute_name) as attribute_name
963 FOR TX IN (select attribute_name
964 from hz_trans_attributes_b
965 where entity_name = p_entity_name
966 and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
967 and custom_attribute_procedure is null
968 /* and attribute_name not in
969 ('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' ) */
970 and attribute_id in ( select attribute_id
971 from hz_match_rule_primary
972 where match_rule_id = p_rule_id
973 union
974 select attribute_id
975 from hz_match_rule_secondary
976 where match_rule_id = p_rule_id)
977 ) LOOP
978 IF is_first THEN
979 is_first := false;
980 IF (p_std_chk = DO_STD_CHK) THEN
981 p_sql_str := ' select ' || chk_is_std(TX.attribute_name) ;
982 ELSE
983 IF TX.attribute_name = 'PARTY_NAME' THEN
984 p_sql_str := ' select decode(a.party_type, ''ORGANIZATION'', a.organization_name, ''PERSON'', a.person_first_name || '' '' || a.person_last_name) as PARTY_NAME ';
985 ELSE
986 p_sql_str := ' select a.' || TX.attribute_name ;
987 END IF;
988 END IF;
989 ELSE
990 IF (p_std_chk = DO_STD_CHK) THEN
991 p_sql_str := p_sql_str || ', ' || chk_is_std(TX.attribute_name) ;
992 ELSE
993 IF TX.attribute_name = 'PARTY_NAME' THEN
994 p_sql_str := p_sql_str || ', decode(a.party_type, ''ORGANIZATION'', a.organization_name, ''PERSON'', a.person_first_name || '' '' || a.person_last_name) as PARTY_NAME ';
995 ELSE
996 p_sql_str := p_sql_str || ', a.' || TX.attribute_name ;
997 END IF;
998 END IF;
999 END IF;
1000 END LOOP;
1001 IF p_et_point = 'INT_INT' THEN
1002 IF p_entity_name = 'PARTY' /* and p_sql_str != null */ THEN
1003 p_sql_str := p_sql_str || ', a.party_orig_system, a.party_orig_system_reference, b.party_id, a.rowid, a.party_type' ;
1004 ELSIF p_entity_name = 'PARTY_SITES' /* and p_sql_str != null */ THEN
1005 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES', 'ADDRESS'); -- using_address(p_rule_id);
1006 -- IF (p_std_chk = NO_STD_CHK) THEN
1007 IF (p_sql_str is NULL) THEN
1008 p_sql_str := 'select a.party_orig_system';
1009 p_sql_str := p_sql_str || ', a.party_orig_system_reference, a.site_orig_system';
1010 p_sql_str := p_sql_str || ', a.site_orig_system_reference, b.party_id, b.party_site_id, b.party_action_flag, a.rowid';
1011 ELSE
1012 p_sql_str := p_sql_str || ', a.party_orig_system';
1013 p_sql_str := p_sql_str || ', a.party_orig_system_reference, a.site_orig_system';
1014 p_sql_str := p_sql_str || ', a.site_orig_system_reference, b.party_id, b.party_site_id, b.party_action_flag, a.rowid';
1015 END IF;
1016 IF (is_using_allow_cust_attr = 'Y') THEN
1017 -- p_sql_str := p_sql_str || ', a.address1 || a.address2 || a.address3 || a.address4 as address ';
1018 p_sql_str := p_sql_str || ','|| chk_is_std('ADDRESS1') || ' || '' '' '
1019 || '||' || chk_is_std('ADDRESS2') || ' || '' '' '
1020 || '||' || chk_is_std('ADDRESS3') || ' || '' '' '
1021 || '||' || chk_is_std('ADDRESS4')||' as address ';
1022 END IF;
1023 /* ELSE IF (p_std_chk = DO_STD_CHK) THEN
1024
1025 ELSE
1026 dbms_output.put_line('SOMETHING WRONG 1 ');
1027 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1028 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = ''INT_INT'',p_entity_name='||p_entity_name||' p_std_chk=' || p_std_chk);
1029 END IF;
1030 END IF;
1031 */
1032 ELSIF p_entity_name = 'CONTACT_POINTS' /* and p_sql_str != null */ THEN
1033 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS', 'RAW_PHONE_NUMBER'); -- using_raw_ph_no(p_rule_id);
1034 IF (p_sql_str IS NULL) THEN
1035 p_sql_str := 'select a.party_orig_system, a.party_orig_system_reference, a.cp_orig_system, a.cp_orig_system_reference, a.site_orig_system';
1036 p_sql_str := p_sql_str || ', 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';
1037 ELSE
1038 p_sql_str := p_sql_str || ', a.party_orig_system, a.party_orig_system_reference, a.cp_orig_system, a.cp_orig_system_reference, a.site_orig_system';
1039 p_sql_str := p_sql_str || ', 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';
1040 END IF;
1041 IF (is_using_allow_cust_attr = 'Y') THEN
1042 p_sql_str := p_sql_str || ', 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 ';
1043 END IF;
1044 ELSIF p_entity_name = 'CONTACTS' /* and p_sql_str != null */ THEN
1045 IF (p_sql_str IS NULL) THEN
1046 p_sql_str := 'select 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';
1047 ELSE
1048 p_sql_str := p_sql_str || ', 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';
1049 END IF;
1050 ELSE
1051 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1052 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = ''INT_INT'',p_entity_name='||p_entity_name);
1053 END IF;
1054 END IF;
1055 ELSIF p_et_point = 'INT_TCA' THEN
1056 IF p_entity_name = 'PARTY' /* and p_sql_str != null */ THEN
1057 p_sql_str := p_sql_str || ', a.party_orig_system, a.party_orig_system_reference, a.rowid, a.party_type ' ;
1058 ELSIF p_entity_name = 'PARTY_SITES' /* and p_sql_str != null */ THEN
1059 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES', 'ADDRESS');--using_address(p_rule_id);
1060 IF (p_sql_str is NULL) THEN
1061 p_sql_str := 'select a.party_orig_system';
1062 p_sql_str := p_sql_str || ', a.party_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, a.rowid';
1063 ELSE
1064 p_sql_str := p_sql_str || ', a.party_orig_system';
1065 p_sql_str := p_sql_str || ', a.party_orig_system_reference, a.site_orig_system, a.site_orig_system_reference, a.rowid';
1066 END IF;
1067 IF (is_using_allow_cust_attr = 'Y') THEN
1068 p_sql_str := p_sql_str || ', a.address1 || '' '' || a.address2 || '' '' || a.address3 || '' '' || a.address4 as address ';
1069 END IF;
1070 ELSIF p_entity_name = 'CONTACT_POINTS' /* and p_sql_str != null */ THEN
1071 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS','RAW_PHONE_NUMBER');--using_raw_ph_no(p_rule_id);
1072 IF (p_sql_str IS NULL) THEN
1073 p_sql_str := 'select 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';
1074 ELSE
1075 p_sql_str := p_sql_str || ', 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';
1076 END IF;
1077 IF (is_using_allow_cust_attr = 'Y') THEN
1078 p_sql_str := p_sql_str || ',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 ';
1079 END IF;
1080 ELSIF p_entity_name = 'CONTACTS' /* and p_sql_str != null */ THEN
1081 IF (p_sql_str IS NULL) THEN
1082 p_sql_str := 'select a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, a.rowid';
1083 ELSE
1084 p_sql_str := p_sql_str || ', a.obj_orig_system, a.obj_orig_system_reference, a.contact_orig_system, a.contact_orig_system_reference, a.rowid';
1085 END IF;
1086 ELSE
1087 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1088 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = ''INT_TCA'',p_entity_name='||p_entity_name);
1089 END IF;
1090 END IF;
1091 ELSE
1092 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1093 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = '||p_et_point);
1094 END IF;
1095 END IF;
1096 EXCEPTION WHEN OTHERS THEN
1097 RAISE FND_API.G_EXC_ERROR;
1098 END get_select_str;
1099
1100 PROCEDURE get_trans_str (
1101 p_entity_name IN VARCHAR2,
1102 p_rule_id IN NUMBER,
1103 p_sql_str IN OUT NOCOPY VARCHAR2,
1104 p_et_point IN VARCHAR2
1105 ) IS
1106 is_first BOOLEAN := TRUE;
1107 is_using_allow_cust_attr VARCHAR2(1) := 'N';
1108 l_procedure_name VARCHAR2(30) := '.GET_TRANS_STR' ;
1109 l_table_name VARCHAR2(30);
1110 BEGIN
1111 get_table_name(p_entity_name, l_table_name);
1112 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1113 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1114 END IF;
1115 FOR TX IN ( select attribute_id || 'E' as STAGED_ATTRIBUTE_COLUMN
1116 from hz_trans_attributes_b
1117 where attribute_id in (select attribute_id
1118 from hz_match_rule_primary
1119 where match_rule_id = p_rule_id
1120 union
1121 select attribute_id
1122 from hz_match_rule_secondary
1123 where match_rule_id = p_rule_id)
1124 and entity_name = p_entity_name
1125 and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
1126 and custom_attribute_procedure is null
1127 /* and attribute_name not in
1128 ('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' ) */
1129 ) LOOP
1130 IF is_first THEN
1131 is_first := false;
1132 p_sql_str := ' H_' || TX.STAGED_ATTRIBUTE_COLUMN ;
1133 ELSE
1134 p_sql_str := p_sql_str || ', H_' || TX.STAGED_ATTRIBUTE_COLUMN ;
1135 END IF;
1136 END LOOP;
1137 IF p_et_point = 'INT_INT' THEN
1138 IF p_entity_name = 'PARTY' THEN
1139 p_sql_str := p_sql_str || ', H_P_PARTY_OS , H_P_PARTY_OSR, H_P_PARTY_ID, H_P_ROW_ID, H_P_P_TYPE' ;
1140 ELSIF p_entity_name = 'PARTY_SITES' THEN
1141 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES','ADDRESS');--using_address(p_rule_id);
1142 IF (p_sql_str IS NULL) THEN
1143 p_sql_str := ' 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' ;
1144 ELSE
1145 p_sql_str := p_sql_str || ', 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' ;
1146 END IF;
1147 IF (is_using_allow_cust_attr = 'Y') THEN
1148 p_sql_str := p_sql_str || ', H_P_PS_ADD ';
1149 END IF;
1150 ELSIF p_entity_name = 'CONTACT_POINTS' THEN
1151 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS','RAW_PHONE_NUMBER');--using_raw_ph_no(p_rule_id);
1152 IF (p_sql_str IS NULL) THEN
1153 p_sql_str := ' 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';
1154 ELSE
1155 p_sql_str := p_sql_str || ', 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';
1156 END IF;
1157 IF (is_using_allow_cust_attr = 'Y') THEN
1158 p_sql_str := p_sql_str || ', H_P_CP_R_PH_NO ';
1159 END IF;
1160 ELSIF p_entity_name = 'CONTACTS' THEN
1161 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACTS','CONTACT_NAME');-- using_contact_name(p_rule_id);
1162 IF (p_sql_str IS NULL) THEN
1163 p_sql_str := ' 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 ';
1164 ELSE
1165 p_sql_str := p_sql_str || ', 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 ';
1166 END IF;
1167 IF (is_using_allow_cust_attr = 'Y') THEN
1168 p_sql_str := p_sql_str || ', H_CT_NAME ';
1169 END IF;
1170 ELSE
1171 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1172 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = ''INT_INT'', p_entity_name = '||p_entity_name);
1173 END IF;
1174 END IF;
1175 ELSIF p_et_point = 'INT_TCA' THEN
1176 IF p_entity_name = 'PARTY' THEN
1177 p_sql_str := p_sql_str || ', H_P_PARTY_OS , H_P_PARTY_OSR, H_P_ROW_ID, H_P_P_TYPE' ;
1178 ELSIF p_entity_name = 'PARTY_SITES' THEN
1179 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES','ADDRESS'); --using_address(p_rule_id);
1180 IF (p_sql_str IS NULL) THEN
1181 p_sql_str := ' H_P_PARTY_OS, H_P_PARTY_OSR, H_P_PS_OS, H_P_PS_OSR, H_P_ROW_ID' ;
1182 ELSE
1183 p_sql_str := p_sql_str || ', H_P_PARTY_OS, H_P_PARTY_OSR, H_P_PS_OS, H_P_PS_OSR, H_P_ROW_ID' ;
1184 END IF;
1185 IF (is_using_allow_cust_attr = 'Y') THEN
1186 p_sql_str := p_sql_str || ', H_P_PS_ADD ';
1187 END IF;
1188 ELSIF p_entity_name = 'CONTACT_POINTS' THEN
1189 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS','RAW_PHONE_NUMBER'); --using_raw_ph_no(p_rule_id);
1190 IF (p_sql_str IS NULL) THEN
1191 p_sql_str := ' 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';
1192 ELSE
1193 p_sql_str := p_sql_str || ', 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';
1194 END IF;
1195 IF (is_using_allow_cust_attr = 'Y') THEN
1196 p_sql_str := p_sql_str || ', H_P_CP_R_PH_NO ';
1197 END IF;
1198 ELSIF p_entity_name = 'CONTACTS' THEN
1199 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACTS','CONTACT_NAME');-- using_contact_name(p_rule_id);
1200 IF (p_sql_str IS NULL) THEN
1201 p_sql_str := ' H_P_SUBJECT_OS, H_P_SUBJECT_OSR, H_P_CONTACT_OS, H_P_CONTACT_OSR, H_P_ROW_ID ';
1202 ELSE
1203 p_sql_str := p_sql_str || ', H_P_SUBJECT_OS, H_P_SUBJECT_OSR, H_P_CONTACT_OS, H_P_CONTACT_OSR, H_P_ROW_ID ';
1204 END IF;
1205 IF (is_using_allow_cust_attr = 'Y') THEN
1206 p_sql_str := p_sql_str || ', H_CT_NAME ';
1207 END IF;
1208 ELSE
1209 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1210 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = ''INT_TCA'', p_entity_name = '||p_entity_name);
1211 END IF;
1212 END IF;
1213 ELSE
1214 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1215 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = '|| p_et_point);
1216 END IF;
1217 END IF;
1218 EXCEPTION WHEN OTHERS THEN
1219 RAISE FND_API.G_EXC_ERROR;
1220
1221 END get_trans_str;
1222
1223 PROCEDURE get_cust_insert_str (
1224 p_entity_name IN VARCHAR2,
1225 p_match_rule_id IN NUMBER,
1226 p_sql_str IN OUT NOCOPY VARCHAR2,
1227 p_et_point IN VARCHAR2,
1228 p_attr_name IN VARCHAR2,
1229 p_purpose VARCHAR2 -- can be taken out
1230 ) IS
1231 is_first BOOLEAN := TRUE;
1232 is_using_allow_cust_attr VARCHAR2(1) := 'N';
1233 l_procedure_name VARCHAR2(30) := '.GET_CUST_INSERT_STR' ;
1234 l_table_name VARCHAR2(30);
1235 BEGIN
1236 get_table_name(p_entity_name, l_table_name);
1237 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1238 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1239 END IF;
1240 FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
1241 from hz_trans_functions_b
1242 where attribute_id in (select attribute_id
1243 from hz_trans_attributes_b
1244 where attribute_name = p_attr_name
1245 and entity_name = p_entity_name)
1246 and function_id in (select function_id
1247 from hz_match_rule_primary e, hz_primary_trans d
1248 where match_rule_id = p_match_rule_id
1249 and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
1250 union
1251 select function_id
1252 from hz_match_rule_secondary g, hz_secondary_trans f
1253 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
1254 and match_rule_id = p_match_rule_id)
1255 order by STAGED_ATTRIBUTE_COLUMN
1256 ) LOOP
1257 IF is_first THEN
1258 is_first := false;
1259 p_sql_str := ', ' || TX.STAGED_ATTRIBUTE_COLUMN ;
1260 ELSE
1261 p_sql_str := p_sql_str || ', ' || TX.STAGED_ATTRIBUTE_COLUMN ;
1262 END IF;
1263 END LOOP;
1264 END get_cust_insert_str;
1265
1266
1267 PROCEDURE get_insert_str (
1268 p_entity_name IN VARCHAR2,
1269 p_rule_id IN NUMBER,
1270 p_sql_str IN OUT NOCOPY VARCHAR2,
1271 p_et_point IN VARCHAR2
1272 ) IS
1273 is_first BOOLEAN := TRUE;
1274 is_using_allow_cust_attr VARCHAR2(1) := 'N';
1275 l_procedure_name VARCHAR2(30) := '.GET_INSERT_STR' ;
1276 l_table_name VARCHAR2(30);
1277 l_sql_str VARCHAR2(255) ;
1278 BEGIN
1279 get_table_name(p_entity_name, l_table_name);
1280 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1281 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1282 END IF;
1283 FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
1284 from hz_trans_functions_b
1285 where function_id in
1286 (select function_id
1287 from hz_match_rule_primary e, hz_primary_trans d
1288 where match_rule_id = p_rule_id
1289 and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
1290 union
1291 select function_id
1292 from hz_match_rule_secondary g, hz_secondary_trans f
1293 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
1294 and match_rule_id = p_rule_id)
1295 and nvl(active_flag, 'Y') <> 'N'
1296 and staged_attribute_table = p_entity_name
1297 and attribute_id not in (
1298 select attribute_id
1299 from hz_trans_attributes_b
1300 where custom_attribute_procedure is not null
1301 or HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'N'
1302 /* or attribute_name in
1303 ('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' ) */
1304 ) order by STAGED_ATTRIBUTE_COLUMN
1305 ) LOOP
1306 IF is_first THEN
1307 is_first := false;
1308 p_sql_str := ' ' || TX.STAGED_ATTRIBUTE_COLUMN ;
1309 ELSE
1310 p_sql_str := p_sql_str || ', ' || TX.STAGED_ATTRIBUTE_COLUMN ;
1311 END IF;
1312 END LOOP;
1313 IF p_et_point = 'INT_INT' THEN
1314 IF p_entity_name = 'HZ_STAGED_PARTIES' THEN
1315 IF (p_sql_str IS NULL) THEN
1316 p_sql_str := ' PARTY_OS, PARTY_OSR, PARTY_ID, BATCH_ID, INT_ROW_ID' ;
1317 ELSE
1318 p_sql_str := p_sql_str || ', PARTY_OS, PARTY_OSR, PARTY_ID, BATCH_ID, INT_ROW_ID' ;
1319 END IF;
1320 ELSIF p_entity_name = 'HZ_STAGED_PARTY_SITES' THEN
1321 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES','ADDRESS'); --using_address(p_rule_id);
1322 IF (p_sql_str IS NULL) THEN
1323 p_sql_str := ' PARTY_SITE_ID, PARTY_ID, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID ';
1324 ELSE
1325 p_sql_str := p_sql_str || ', PARTY_SITE_ID, PARTY_ID, PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID ';
1326 END IF;
1327 IF (is_using_allow_cust_attr = 'Y') THEN
1328 -- p_sql_str := p_sql_str || ', TX3, TX4, TX26, TX27 ';
1329 get_cust_insert_str ('PARTY_SITES', p_rule_id, l_sql_str, 'PS', 'ADDRESS', null);
1330 p_sql_str := p_sql_str || l_sql_str;
1331 END IF;
1332 ELSIF p_entity_name = 'HZ_STAGED_CONTACT_POINTS' THEN
1333 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS','RAW_PHONE_NUMBER');-- using_raw_ph_no(p_rule_id);
1334 IF (p_sql_str IS NULL) THEN
1335 p_sql_str := ' 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 ';
1336 ELSE
1337 p_sql_str := p_sql_str || ', 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 ';
1338 END IF;
1339 IF (is_using_allow_cust_attr = 'Y') THEN
1340 -- p_sql_str := p_sql_str || ', TX10, TX158 ';
1341 get_cust_insert_str ('CONTACT_POINTS', p_rule_id, l_sql_str, 'CP', 'RAW_PHONE_NUMBER', null);
1342 p_sql_str := p_sql_str || l_sql_str;
1343 END IF;
1344 ELSIF p_entity_name = 'HZ_STAGED_CONTACTS' THEN
1345 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACTS','CONTACT_NAME');--using_contact_name(p_rule_id);
1346 IF (p_sql_str IS NULL) THEN
1347 p_sql_str := ' PARTY_OS, PARTY_OSR, CONTACT_OS, CONTACT_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID, PARTY_ID ';
1348 ELSE
1349 p_sql_str := p_sql_str || ', PARTY_OS, PARTY_OSR, CONTACT_OS, CONTACT_OSR, NEW_PARTY_FLAG, BATCH_ID, INT_ROW_ID, PARTY_ID ';
1350 END IF;
1351 IF (is_using_allow_cust_attr = 'Y') THEN
1352 -- p_sql_str := p_sql_str || ', TX2, TX5, TX6, TX156 ';
1353 get_cust_insert_str ('CONTACTS', p_rule_id, l_sql_str, NULL, 'CONTACT_NAME', null);
1354 p_sql_str := p_sql_str || l_sql_str;
1355 END IF;
1356 ELSE
1357 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1358 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = ''INT_INT'', p_entity_name = '||p_entity_name);
1359 END IF;
1360 END IF;
1361 ELSIF p_et_point = 'INT_TCA' THEN
1362 IF p_entity_name = 'HZ_STAGED_PARTIES' THEN
1363 IF (p_sql_str IS NULL) THEN
1364 p_sql_str := ' PARTY_OS, PARTY_OSR, BATCH_ID, INT_ROW_ID' ;
1365 ELSE
1366 p_sql_str := p_sql_str || ', PARTY_OS, PARTY_OSR, BATCH_ID, INT_ROW_ID' ;
1367 END IF;
1368 ELSIF p_entity_name = 'HZ_STAGED_PARTY_SITES' THEN
1369 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES','ADDRESS');--using_address(p_rule_id);
1370 IF (p_sql_str IS NULL) THEN
1371 p_sql_str := ' PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, BATCH_ID, INT_ROW_ID';
1372 ELSE
1373 p_sql_str := p_sql_str || ', PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, BATCH_ID, INT_ROW_ID';
1374 END IF;
1375 IF (is_using_allow_cust_attr = 'Y') THEN
1376 -- p_sql_str := p_sql_str || ', TX3, TX4, TX26, TX27 ';
1377 get_cust_insert_str ('PARTY_SITES', p_rule_id, l_sql_str, 'PS', 'ADDRESS', null);
1378 p_sql_str := p_sql_str || l_sql_str;
1379 END IF;
1380 ELSIF p_entity_name = 'HZ_STAGED_CONTACT_POINTS' THEN
1381 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS','RAW_PHONE_NUMBER');--using_raw_ph_no(p_rule_id);
1382 IF (p_sql_str IS NULL) THEN
1383 p_sql_str := ' PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, CONTACT_PT_OS, CONTACT_PT_OSR, BATCH_ID, INT_ROW_ID, CONTACT_POINT_TYPE';
1384 ELSE
1385 p_sql_str := p_sql_str || ', PARTY_OS, PARTY_OSR, PARTY_SITE_OS, PARTY_SITE_OSR, CONTACT_PT_OS, CONTACT_PT_OSR, BATCH_ID, INT_ROW_ID, CONTACT_POINT_TYPE';
1386 END IF;
1387 IF (is_using_allow_cust_attr = 'Y') THEN
1388 -- p_sql_str := p_sql_str || ', TX10, TX158 ';
1389 get_cust_insert_str ('CONTACT_POINTS', p_rule_id, l_sql_str, 'CP', 'RAW_PHONE_NUMBER', null);
1390 p_sql_str := p_sql_str || l_sql_str;
1391 END IF;
1392 ELSIF p_entity_name = 'HZ_STAGED_CONTACTS' THEN
1393 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACTS','CONTACT_NAME');--using_contact_name(p_rule_id);
1394 IF (p_sql_str is NULL) THEN
1395 p_sql_str := ' PARTY_OS, PARTY_OSR, CONTACT_OS, CONTACT_OSR, BATCH_ID, INT_ROW_ID ';
1396 ELSE
1397 p_sql_str := p_sql_str || ', PARTY_OS, PARTY_OSR, CONTACT_OS, CONTACT_OSR, BATCH_ID, INT_ROW_ID ';
1398 END IF;
1399 IF (is_using_allow_cust_attr = 'Y') THEN
1400 -- p_sql_str := p_sql_str || ', TX2, TX5, TX6, TX156 ';
1401 get_cust_insert_str ('CONTACTS', p_rule_id, l_sql_str, NULL, 'CONTACT_NAME', null);
1402 p_sql_str := p_sql_str || l_sql_str;
1403 END IF;
1404 ELSE
1405 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1406 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = ''INT_TCA'', p_entity_name = '||p_entity_name);
1407 END IF;
1408 END IF;
1409 ELSE
1410 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1411 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = '|| p_et_point);
1412 END IF;
1413 END IF;
1414 EXCEPTION WHEN OTHERS THEN
1415 RAISE FND_API.G_EXC_ERROR;
1416
1417 END get_insert_str;
1418
1419 PROCEDURE get_cust_insert_val_str (
1420 p_entity_name IN VARCHAR2,
1421 p_rule_id IN NUMBER,
1422 p_sql_str IN OUT NOCOPY VARCHAR2,
1423 p_et_point IN VARCHAR2,
1424 p_attr_name IN VARCHAR2
1425 ) IS
1426 is_first BOOLEAN := TRUE;
1427 is_using_allow_cust_attr VARCHAR2(1) := 'N';
1428 l_procedure_name VARCHAR2(30) := '.GET_CUST_INSERT_VAL_STR' ;
1429 l_table_name VARCHAR2(30);
1430 BEGIN
1431 get_table_name(p_entity_name, l_table_name);
1432 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1433 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1434 END IF;
1435 FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
1436 from hz_trans_functions_b
1437 where attribute_id in (select attribute_id
1438 from hz_trans_attributes_b
1439 where attribute_name = p_attr_name
1440 and entity_name = p_entity_name)
1441 and function_id in (select function_id
1442 from hz_match_rule_primary e, hz_primary_trans d
1443 where match_rule_id = p_rule_id
1444 and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
1445 union
1446 select function_id
1447 from hz_match_rule_secondary g, hz_secondary_trans f
1448 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
1449 and match_rule_id = p_rule_id)
1450 order by STAGED_ATTRIBUTE_COLUMN
1451 ) LOOP
1452 IF is_first THEN
1453 is_first := false;
1454 p_sql_str := ', H_'||p_et_point||'_CUST_' || TX.STAGED_ATTRIBUTE_COLUMN || '(I)' ;
1455 ELSE
1456 p_sql_str := p_sql_str || ', H_'||p_et_point||'_CUST_' || TX.STAGED_ATTRIBUTE_COLUMN || '(I)' ;
1457 END IF;
1458 END LOOP;
1459 END get_cust_insert_val_str;
1460
1461 PROCEDURE get_insert_val_str (
1462 p_entity_name IN VARCHAR2,
1463 p_rule_id IN NUMBER,
1464 p_sql_str IN OUT NOCOPY VARCHAR2,
1465 p_et_point IN VARCHAR2
1466 ) IS
1467 is_first BOOLEAN := TRUE;
1468 is_using_allow_cust_attr VARCHAR2(1) := 'N';
1469 l_procedure_name VARCHAR2(30) := '.GET_INSERT_VAL_STR' ;
1470 l_table_name VARCHAR2(30);
1471 l_sql_str VARCHAR2(255);
1472 BEGIN
1473 get_table_name(p_entity_name, l_table_name);
1474 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1475 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1476 END IF;
1477 FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
1478 from hz_trans_functions_b
1479 where function_id in (select function_id
1480 from hz_match_rule_primary e, hz_primary_trans d
1481 where e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
1482 and match_rule_id = p_rule_id
1483 union
1484 select function_id
1485 from hz_match_rule_secondary g, hz_secondary_trans f
1486 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
1487 and match_rule_id = p_rule_id)
1488 and nvl(active_flag, 'Y') <> 'N'
1489 and staged_attribute_table = p_entity_name
1490 and attribute_id not in (
1491 select attribute_id
1492 from hz_trans_attributes_b
1493 where custom_attribute_procedure is not null
1494 or HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'N'
1495
1496 ) order by STAGED_ATTRIBUTE_COLUMN
1497 ) LOOP
1498 IF is_first THEN
1499 is_first := false;
1500 p_sql_str := ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || '(I)' ;
1501 ELSE
1502 p_sql_str := p_sql_str || ', H_' || TX.STAGED_ATTRIBUTE_COLUMN || '(I)' ;
1503 END IF;
1504 END LOOP;
1505 IF (p_et_point = 'INT_INT') THEN
1506 IF p_entity_name = 'HZ_STAGED_PARTIES' THEN
1507 IF (p_sql_str IS NULL) THEN
1508 p_sql_str := ' H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PARTY_ID(I), P_BATCH_ID, H_P_ROW_ID(I)' ;
1509 ELSE
1510 p_sql_str := p_sql_str || ', H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PARTY_ID(I), P_BATCH_ID, H_P_ROW_ID(I)' ;
1511 END IF;
1512 ELSIF p_entity_name = 'HZ_STAGED_PARTY_SITES' THEN
1513 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES','ADDRESS');-- using_address(p_rule_id);
1514 IF (p_sql_str IS NULL) THEN
1515 p_sql_str := ' 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) ';
1516 ELSE
1517 p_sql_str := p_sql_str || ', 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) ';
1518 END IF;
1519 IF (is_using_allow_cust_attr = 'Y') THEN
1520 -- p_sql_str := p_sql_str || ', H_P_PS_CUST_TX3(I), H_P_PS_CUST_TX4(I), H_P_PS_CUST_TX26(I), H_P_PS_CUST_TX27(I) ';
1521 get_cust_insert_val_str ('PARTY_SITES', p_rule_id, l_sql_str, 'PS', 'ADDRESS');
1522 p_sql_str := p_sql_str || l_sql_str;
1523 END IF;
1524 ELSIF p_entity_name = 'HZ_STAGED_CONTACT_POINTS' THEN
1525 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS','RAW_PHONE_NUMBER');--using_raw_ph_no(p_rule_id);
1526 IF (p_sql_str IS NULL) THEN
1527 p_sql_str := ' 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) ';
1528 p_sql_str := p_sql_str || ', 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) ';
1529 ELSE
1530 p_sql_str := p_sql_str || ', 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) ';
1531 p_sql_str := p_sql_str || ', 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) ';
1532 END IF;
1533 IF (is_using_allow_cust_attr = 'Y') THEN
1534 -- p_sql_str := p_sql_str || ', H_P_CP_CUST_TX10(I), H_P_CP_CUST_TX158(I) ';
1535 get_cust_insert_val_str ('CONTACT_POINTS', p_rule_id, l_sql_str, 'CP', 'RAW_PHONE_NUMBER');
1536 p_sql_str := p_sql_str || l_sql_str;
1537 END IF;
1538 ELSIF p_entity_name = 'HZ_STAGED_CONTACTS' THEN
1539 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACTS','CONTACT_NAME');--using_contact_name(p_rule_id);
1540 IF (p_sql_str IS NULL) THEN
1541 p_sql_str := ' 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) ';
1542 ELSE
1543 p_sql_str := p_sql_str || ', 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) ';
1544 END IF;
1545 IF (is_using_allow_cust_attr = 'Y') THEN
1546 -- p_sql_str := p_sql_str || ', H_P_CT_CUST_2(I), H_P_CT_CUST_5(I), H_P_CT_CUST_6(I), H_P_CT_CUST_156(I) ';
1547 get_cust_insert_val_str ('CONTACTS', p_rule_id, l_sql_str, 'CT', 'CONTACT_NAME');
1548 p_sql_str := p_sql_str || l_sql_str;
1549 END IF;
1550 ELSE
1551 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1552 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = ''INT_INT'', p_entity_name = '||p_entity_name);
1553 END IF;
1554 END IF;
1555 ELSIF (p_et_point = 'INT_TCA') THEN
1556 IF p_entity_name = 'HZ_STAGED_PARTIES' THEN
1557 IF (p_sql_str IS NULL) THEN
1558 p_sql_str := ' H_P_PARTY_OS(I), H_P_PARTY_OSR(I), P_BATCH_ID, H_P_ROW_ID(I)' ;
1559 ELSE
1560 p_sql_str := p_sql_str || ', H_P_PARTY_OS(I), H_P_PARTY_OSR(I), P_BATCH_ID, H_P_ROW_ID(I)' ;
1561 END IF;
1562 ELSIF p_entity_name = 'HZ_STAGED_PARTY_SITES' THEN
1563 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES','ADDRESS');--using_address(p_rule_id);
1564 IF (p_sql_str IS NULL) THEN
1565 p_sql_str := ' 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)';
1566 ELSE
1567 p_sql_str := p_sql_str || ', 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)';
1568 END IF;
1569 IF (is_using_allow_cust_attr = 'Y') THEN
1570 -- p_sql_str := p_sql_str || ', H_P_PS_CUST_TX3(I), H_P_PS_CUST_TX4(I), H_P_PS_CUST_TX26(I), H_P_PS_CUST_TX27(I) ';
1571 get_cust_insert_val_str ('PARTY_SITES', p_rule_id, l_sql_str, 'PS', 'ADDRESS');
1572 p_sql_str := p_sql_str || l_sql_str;
1573 END IF;
1574 ELSIF p_entity_name = 'HZ_STAGED_CONTACT_POINTS' THEN
1575 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS','RAW_PHONE_NUMBER');--using_raw_ph_no(p_rule_id);
1576 IF (p_sql_str IS NULL) THEN
1577 p_sql_str := ' H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I)';
1578 p_sql_str := p_sql_str || ', H_P_CP_OS(I), H_P_CP_OSR(I), P_BATCH_ID, H_P_ROW_ID(I), H_P_CP_TYPE(I)';
1579 ELSE
1580 p_sql_str := p_sql_str || ', H_P_PARTY_OS(I), H_P_PARTY_OSR(I), H_P_PS_OS(I), H_P_PS_OSR(I)';
1581 p_sql_str := p_sql_str || ', H_P_CP_OS(I), H_P_CP_OSR(I), P_BATCH_ID, H_P_ROW_ID(I), H_P_CP_TYPE(I)';
1582 END IF;
1583 IF (is_using_allow_cust_attr = 'Y') THEN
1584 -- p_sql_str := p_sql_str || ', H_P_CP_CUST_TX10(I), H_P_CP_CUST_TX158(I) ';
1585 get_cust_insert_val_str ('CONTACT_POINTS', p_rule_id, l_sql_str, 'CP', 'RAW_PHONE_NUMBER');
1586 p_sql_str := p_sql_str || l_sql_str;
1587 END IF;
1588 ELSIF p_entity_name = 'HZ_STAGED_CONTACTS' THEN
1589 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACTS','CONTACT_NAME');--using_contact_name(p_rule_id);
1590 IF (p_sql_str IS NULL) THEN
1591 p_sql_str := ' 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) ';
1592 ELSE
1593 p_sql_str := p_sql_str || ', 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) ';
1594 END IF;
1595 IF (is_using_allow_cust_attr = 'Y') THEN
1596 -- p_sql_str := p_sql_str || ', H_P_CT_CUST_2(I), H_P_CT_CUST_5(I), H_P_CT_CUST_6(I), H_P_CT_CUST_156(I) ';
1597 get_cust_insert_val_str ('CONTACTS', p_rule_id, l_sql_str, 'CT', 'CONTACT_NAME');
1598 p_sql_str := p_sql_str || l_sql_str;
1599 END IF;
1600 ELSE
1601 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1602 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = ''INT_TCA'', p_entity_name = '||p_entity_name);
1603 END IF;
1604 END IF;
1605 ELSE
1606 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1607 fnd_log.string(fnd_log.LEVEL_ERROR,G_PKG_NAME||l_procedure_name,'p_et_point = '|| p_et_point);
1608 END IF;
1609 END IF;
1610 EXCEPTION WHEN OTHERS THEN
1611 RAISE FND_API.G_EXC_ERROR;
1612
1613 END get_insert_val_str;
1614
1615 /*
1616 FUNCTION using_address(
1617 p_rule_id IN NUMBER
1618 ) RETURN VARCHAR2 IS
1619
1620 using_address VARCHAR2(1) := 'N';
1621 CURSOR c1 is select 'Y'
1622 from hz_match_rule_primary a, hz_match_rule_secondary b
1623 where a.match_rule_id = b.match_rule_id
1624 and a.match_rule_id = p_rule_id
1625 -- check if one really needs the below condition
1626 -- and a.attribute_id = b.attribute_id
1627 and a.attribute_id in (
1628 select attribute_id
1629 from hz_trans_attributes_b
1630 where entity_name = 'PARTY_SITES'
1631 and attribute_name = 'ADDRESS');
1632 l_procedure_name VARCHAR2(30) := '.USING_RAW_PH_NO' ;
1633 BEGIN
1634 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1635 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1636 END IF;
1637 OPEN c1;
1638 LOOP
1639 FETCH c1 into using_address;
1640 EXIT WHEN c1%NOTFOUND;
1641 END LOOP;
1642 CLOSE c1;
1643 RETURN using_address;
1644 EXCEPTION WHEN OTHERS THEN
1645 using_address := 'N';
1646 END using_address;
1647
1648
1649 FUNCTION using_raw_ph_no(
1650 p_rule_id IN NUMBER
1651 ) RETURN VARCHAR2 IS
1652
1653 using_raw_ph_no VARCHAR2(1) := 'N';
1654 CURSOR c1 is select 'Y'
1655 from hz_match_rule_primary a, hz_match_rule_secondary b
1656 where a.match_rule_id = b.match_rule_id
1657 and a.match_rule_id = p_rule_id
1658 -- check if one really needs the below condition
1659 -- and a.attribute_id = b.attribute_id
1660 and a.attribute_id in (
1661 select attribute_id
1662 from hz_trans_attributes_b
1663 where entity_name = 'CONTACT_POINTS'
1664 and attribute_name = 'RAW_PHONE_NUMBER');
1665 l_procedure_name VARCHAR2(30) := '.USING_RAW_PH_NO' ;
1666 BEGIN
1667 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1668 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1669 END IF;
1670 OPEN c1;
1671 LOOP
1672 FETCH c1 into using_raw_ph_no;
1673 EXIT WHEN c1%NOTFOUND;
1674 END LOOP;
1675 CLOSE c1;
1676 RETURN using_raw_ph_no;
1677 EXCEPTION WHEN OTHERS THEN
1678 using_raw_ph_no := 'N';
1679 END using_raw_ph_no;
1680
1681
1682 FUNCTION using_contact_name(
1683 p_rule_id IN NUMBER
1684 ) RETURN VARCHAR2 IS
1685
1686 using_contact_name VARCHAR2(1) := 'N';
1687 CURSOR c1 is select 'Y'
1688 from hz_match_rule_primary a, hz_match_rule_secondary b
1689 where a.match_rule_id = b.match_rule_id
1690 and a.match_rule_id = p_rule_id
1691 -- check if one really needs the below condition
1692 -- and a.attribute_id = b.attribute_id
1693 and a.attribute_id in (
1694 select attribute_id
1695 from hz_trans_attributes_b
1696 where entity_name = 'CONTACTS'
1697 and attribute_name = 'CONTACT_NAME' );
1698 l_procedure_name VARCHAR2(30) := '.USING_CONTACT_NAME' ;
1699 BEGIN
1700 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1701 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1702 END IF;
1703 OPEN c1;
1704 LOOP
1705 FETCH c1 into using_contact_name;
1706 EXIT WHEN c1%NOTFOUND;
1707 END LOOP;
1708 CLOSE c1;
1709 RETURN using_contact_name;
1710 EXCEPTION WHEN OTHERS THEN
1711 using_contact_name := 'N';
1712 END using_contact_name;
1713
1714 FUNCTION using_org_name(
1715 p_rule_id IN NUMBER
1716 ) RETURN VARCHAR2 IS
1717
1718 using_contact_name VARCHAR2(1) := 'N';
1719 CURSOR c1 is select 'Y'
1720 from hz_match_rule_primary a, hz_match_rule_secondary b
1721 where a.match_rule_id = b.match_rule_id
1722 and a.match_rule_id = p_rule_id
1723 and a.attribute_id = b.attribute_id
1724 and a.attribute_id in (
1725 select attribute_id
1726 from hz_trans_attributes_b
1727 where entity_name = 'CONTACTS'
1728 and attribute_name = 'CONTACT_NAME' );
1729 l_procedure_name VARCHAR2(30) := '.USING_CONTACT_NAME' ;
1730 BEGIN
1731 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1732 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1733 END IF;
1734 OPEN c1;
1735 LOOP
1736 FETCH c1 into using_contact_name;
1737 EXIT WHEN c1%NOTFOUND;
1738 END LOOP;
1739 CLOSE c1;
1740 RETURN using_contact_name;
1741 EXCEPTION WHEN OTHERS THEN
1742 using_contact_name := 'N';
1743 END using_org_name;
1744 */
1745
1746 PROCEDURE get_custom_attr_cur(
1747 p_rule_id IN NUMBER,
1748 p_et_name IN VARCHAR,
1749 p_attr_name IN VARCHAR2,
1750 x_custom_cur IN OUT NOCOPY StageImpContactCurTyp
1751 ) IS
1752 BEGIN
1753 OPEN x_custom_cur FOR
1754 select ATTRIBUTE_NAME, USER_DEFINED_ATTRIBUTE_NAME, PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN, b.attribute_id
1755 from hz_trans_functions_b b, hz_trans_attributes_vl c
1756 where b.attribute_id = c.attribute_id
1757 --Fix for bug 4669257. Removing the hardcoded reference below.
1758 --and userenv('LANG') = 'US'
1759 and b.function_id in (select function_id
1760 from hz_match_rule_primary d, hz_primary_trans e
1761 where match_rule_id = p_rule_id
1762 and d.primary_attribute_id = e.primary_attribute_id
1763 union
1764 select function_id
1765 from hz_match_rule_secondary f, hz_secondary_trans g
1766 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID -- b.attribute_id = c.attribute_id
1767 and match_rule_id = p_rule_id)
1768 and nvl(active_flag, 'Y') <> 'N'
1769 and entity_name = p_et_name
1770 and attribute_name = p_attr_name;
1771 END get_custom_attr_cur;
1772
1773 PROCEDURE get_trans_proc (
1774 p_entity_name IN VARCHAR2,
1775 p_rule_id IN NUMBER,
1776 l_trans_list IN OUT NOCOPY coltab
1777 ) IS
1778
1779 l_str VARCHAR2(2000);
1780 is_using_allow_cust_attr VARCHAR(1) := 'N';
1781 i NUMBER := 0;
1782
1783 l_procedure_name VARCHAR2(30) := '.GET_TRANS_PROC' ;
1784 l_table_name VARCHAR2(30);
1785 l_custom_cur StageImpContactCurTyp;
1786 l_attribute_name VARCHAR2(255);
1787 l_user_defined_attribute_name VARCHAR2(255);
1788 l_proc_name VARCHAR2(255);
1789 l_staged_attribute_column VARCHAr2(255);
1790 l_attribute_id NUMBER;
1791
1792 -- VJN Introduced for Conditional Word Replacements
1793 NONE BOOLEAN := TRUE ;
1794 BEGIN
1795 get_table_name(p_entity_name, l_table_name);
1796 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1797 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1798 END IF;
1799
1800 -- VJN ADDED CODE FOR TRACKING CONDITION ATTRIBUTES AT THIS ENTITY LEVEL
1801 FOR TX IN ( ----------> CONDITIONAL REPLACEMENT CODE ENDS
1802 select STAGED_ATTRIBUTE_COLUMN, b.attribute_id
1803 from hz_trans_functions_b b, hz_trans_attributes_vl c
1804 where b.attribute_id = c.attribute_id
1805 --Fix for bug 4669257. Removing the hardcoded reference below.
1806 --and userenv('LANG') = 'US'
1807 and b.function_id in
1808 (select function_id
1809 from hz_match_rule_primary d, hz_primary_trans e
1810 where match_rule_id = p_rule_id
1811 and d.primary_attribute_id = e.primary_attribute_id
1812 union
1813 select function_id
1814 from hz_match_rule_secondary f, hz_secondary_trans g
1815 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID -- b.attribute_id = c.attribute_id
1816 and match_rule_id = p_rule_id)
1817 and nvl(active_flag, 'Y') <> 'N'
1818 and entity_name = p_entity_name
1819 and custom_attribute_procedure is null
1820 and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
1821 order by STAGED_ATTRIBUTE_COLUMN
1822 ) LOOP
1823 -- DO THIS IF AND ONLY IF THIS ATTRIBUTE IS A CONDITION ATTRIBUTE
1824 IF HZ_WORD_CONDITIONS_PKG.is_a_cond_attrib( TX.ATTRIBUTE_ID)
1825 THEN
1826 -- ONE TIME ONLY
1827 IF NONE
1828 THEN
1829 l_trans_list(i) := '----------- SETTING GLOBAL CONDITION RECORD AT THE ' || p_entity_name || ' LEVEL ---------';
1830 i := i + 1 ;
1831 NONE := FALSE ;
1832 END IF ;
1833
1834 l_trans_list(i) := ' HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (' ||
1835 TX.ATTRIBUTE_ID || ',' || 'H_' || TX.ATTRIBUTE_ID || 'E(I) );' ;
1836 i := i + 1;
1837 END IF;
1838
1839 END LOOP ; ----------> CONDITIONAL REPLACEMENT CODE ENDS
1840
1841
1842
1843 IF p_entity_name = 'PARTY' THEN
1844 l_trans_list(i) := ' HZ_TRANS_PKG.set_party_type(H_P_P_TYPE(I));';
1845 i := i + 1;
1846 END IF;
1847 FOR TX IN ( select ATTRIBUTE_NAME, USER_DEFINED_ATTRIBUTE_NAME, PROCEDURE_NAME, STAGED_ATTRIBUTE_COLUMN, b.attribute_id
1848 from hz_trans_functions_b b, hz_trans_attributes_vl c
1849 where b.attribute_id = c.attribute_id
1850 --Fix for bug 4669257. Removing the hardcoded reference below.
1851 --and userenv('LANG') = 'US'
1852 and b.function_id in
1853 (select function_id
1854 from hz_match_rule_primary d, hz_primary_trans e
1855 where match_rule_id = p_rule_id
1856 and d.primary_attribute_id = e.primary_attribute_id
1857 union
1858 select function_id
1859 from hz_match_rule_secondary f, hz_secondary_trans g
1860 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID -- b.attribute_id = c.attribute_id
1861 and match_rule_id = p_rule_id)
1862 and nvl(active_flag, 'Y') <> 'N'
1863 and entity_name = p_entity_name
1864 and custom_attribute_procedure is null
1865 and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
1866 -- and attribute_name not in
1867 -- ('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' )
1868
1869 order by STAGED_ATTRIBUTE_COLUMN
1870 ) LOOP
1871
1872 IF has_trx_context(TX.PROCEDURE_NAME) THEN
1873 l_str := ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || '(I) := ' || TX.PROCEDURE_NAME || '(H_' || TX.ATTRIBUTE_ID || 'E(I), NULL, '''|| TX.ATTRIBUTE_NAME || ''', '''|| p_entity_name || ''', ''SEARCH'' );' ;
1874 ELSE
1875 l_str := ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || '(I) := ' || TX.PROCEDURE_NAME || '(H_' || TX.ATTRIBUTE_ID || 'E(I), NULL, '''|| TX.ATTRIBUTE_NAME || ''', '''|| p_entity_name || ''');' ;
1876 END IF;
1877 l_trans_list(i) := l_str;
1878 l_str := '';
1879 i := i + 1;
1880 END LOOP;
1881 IF p_entity_name = 'CONTACTS' THEN
1882 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACTS','CONTACT_NAME');--using_contact_name(p_rule_id);
1883 IF (is_using_allow_cust_attr = 'Y') THEN
1884 -- for dynamic transformation for custom atribute
1885 get_custom_attr_cur(p_rule_id, p_entity_name, 'CONTACT_NAME', l_custom_cur);
1886 LOOP FETCH l_custom_cur into l_attribute_name, l_user_defined_attribute_name, l_proc_name, l_staged_attribute_column, l_attribute_id;
1887 EXIT when l_custom_cur%NOTFOUND;
1888 IF has_trx_context(l_proc_name) THEN
1889 l_str := ' H_CT_CUST_'||l_staged_attribute_column||'(I) := ' || l_proc_name || '(H_CT_NAME(I), NULL, '''|| l_attribute_name || ''', '''|| p_entity_name || ''', ''SEARCH'' );' ;
1890 ELSE
1891 l_str := ' H_CT_CUST_'||l_staged_attribute_column||'(I) := ' || l_proc_name || '(H_CT_NAME(I), NULL, '''|| l_attribute_name || ''', '''|| p_entity_name || '''); ' ;
1892 END IF;
1893 l_trans_list(i) := l_str;
1894 l_str := '';
1895 i := i + 1;
1896 END LOOP;
1897 /*
1898 l_trans_list(i + 2) := ' H_P_CT_CUST_2(I) := HZ_TRANS_PKG.EXACT_PADDED(H_CT_NAME(I), NULL, ''CONTACT_NAME'', ''CONTACTS'');';
1899 l_trans_list(i + 3) := ' H_P_CT_CUST_5(I) := HZ_TRANS_PKG.WRPERSON_EXACT(H_CT_NAME(I), NULL, ''CONTACT_NAME'', ''CONTACTS'');';
1900 l_trans_list(i + 1) := ' H_P_CT_CUST_6(I) := HZ_TRANS_PKG.WRPERSON_CLEANSE(H_CT_NAME(I), NULL, ''CONTACT_NAME'', ''CONTACTS'');';
1901 l_trans_list(i) := ' H_P_CT_CUST_156(I) := HZ_TRANS_PKG.SOUNDX(H_CT_NAME(I), NULL, ''CONTACT_NAME'', ''CONTACTS'');';
1902 */
1903 END IF;
1904 ELSIF p_entity_name = 'CONTACT_POINTS' THEN
1905 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS','RAW_PHONE_NUMBER');--using_raw_ph_no(p_rule_id);
1906 IF (is_using_allow_cust_attr = 'Y') THEN
1907 -- for dynamic transformation for custom atribute
1908 get_custom_attr_cur(p_rule_id, p_entity_name, 'RAW_PHONE_NUMBER', l_custom_cur);
1909 LOOP FETCH l_custom_cur into l_attribute_name, l_user_defined_attribute_name, l_proc_name, l_staged_attribute_column, l_attribute_id;
1910 EXIT when l_custom_cur%NOTFOUND;
1911 IF has_trx_context(l_proc_name) THEN
1912 l_str := ' H_CP_CUST_'||l_staged_attribute_column||'(I) := ' || l_proc_name || '(H_P_CP_R_PH_NO(I), NULL, '''|| l_attribute_name || ''', '''|| p_entity_name || ''', ''SEARCH'' );' ;
1913 ELSE
1914 l_str := ' H_CP_CUST_'||l_staged_attribute_column||'(I) := ' || l_proc_name || '(H_P_CP_R_PH_NO(I), NULL, '''|| l_attribute_name || ''', '''|| p_entity_name || '''); ' ;
1915 END IF;
1916 l_trans_list(i) := l_str;
1917 l_str := '';
1918 i := i + 1;
1919 END LOOP;
1920 /*
1921 l_trans_list(i + 1) := ' H_P_CP_CUST_TX10(I) := HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(H_P_CP_R_PH_NO(I), NULL, ''RAW_PHONE_NUMBER'', ''CONTACT_POINTS''); ';
1922 l_trans_list(i) := ' H_P_CP_CUST_TX158(I) := HZ_TRANS_PKG.RM_SPLCHAR(H_P_CP_R_PH_NO(I), NULL, ''RAW_PHONE_NUMBER'', ''CONTACT_POINTS''); '; */
1923 END IF;
1924 ELSIF p_entity_name = 'PARTY_SITES' THEN
1925 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES','ADDRESS');--using_address(p_rule_id);
1926 IF ( is_using_allow_cust_attr = 'Y') THEN
1927 -- for dynamic transformation for custom atribute
1928 get_custom_attr_cur(p_rule_id, p_entity_name, 'ADDRESS', l_custom_cur);
1929 LOOP FETCH l_custom_cur into l_attribute_name, l_user_defined_attribute_name, l_proc_name, l_staged_attribute_column, l_attribute_id;
1930 EXIT when l_custom_cur%NOTFOUND;
1931 IF has_trx_context(l_proc_name) THEN
1932 l_str := ' H_PS_CUST_'||l_staged_attribute_column||'(I) := ' || l_proc_name || '(H_P_PS_ADD(I), NULL, '''|| l_attribute_name || ''', '''|| p_entity_name || ''', ''SEARCH'' );' ;
1933 ELSE
1934 l_str := ' H_PS_CUST_'||l_staged_attribute_column||'(I) := ' || l_proc_name || '(H_P_PS_ADD(I), NULL, '''|| l_attribute_name || ''', '''|| p_entity_name || '''); ' ;
1935 END IF;
1936 l_trans_list(i) := l_str;
1937 l_str := '';
1938 i := i + 1;
1939 END LOOP;
1940 /*
1941 l_trans_list(i) := ' H_P_PS_CUST_TX3(I) := HZ_TRANS_PKG.WRADDRESS_EXACT(H_P_PS_ADD(I), NULL, ''ADDRESS'', ''PARTY_SITES''); ';
1942 l_trans_list(i + 1) := ' H_P_PS_CUST_TX4(I) := HZ_TRANS_PKG.WRADDRESS_CLEANSE(H_P_PS_ADD(I), NULL, ''ADDRESS'', ''PARTY_SITES''); ';
1943 l_trans_list(i + 2) := ' H_P_PS_CUST_TX26(I) := HZ_TRANS_PKG.BASIC_WRADDR(H_P_PS_ADD(I), NULL, ''ADDRESS'', ''PARTY_SITES'', ''SEARCH''); ';
1944 l_trans_list(i + 3) := ' H_P_PS_CUST_TX27(I) := HZ_TRANS_PKG.BASIC_CLEANSE_WRADDR(H_P_PS_ADD(I), NULL, ''ADDRESS'', ''PARTY_SITES'', ''SEARCH''); '; */
1945 END IF;
1946 END IF;
1947 EXCEPTION WHEN OTHERS THEN
1948 RAISE FND_API.G_EXC_ERROR;
1949 END get_trans_proc;
1950
1951 PROCEDURE gen_pop_parties (
1952 p_rule_id IN NUMBER
1953 )
1954 IS
1955 l_sel_str VARCHAR2(4000) := NULL;
1956 x_bool VARCHAR2(1);
1957 l_procedure_name VARCHAR2(30) := '.GEN_POP_PARTIES' ;
1958 BEGIN
1959 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1960 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
1961 END IF;
1962 l('PROCEDURE pop_parties (');
1963 l(' p_batch_id IN NUMBER,');
1964 l(' p_from_osr IN VARCHAR2,');
1965 l(' p_to_osr IN VARCHAR2,');
1966 l(' p_batch_mode_flag IN VARCHAR2 ');
1967 l(') IS ');
1968 l(' l_last_fetch BOOLEAN := FALSE;');
1969 l(' p_party_cur HZ_PARTY_STAGE.StageCurTyp;');
1970 l('');
1971 l(' count NUMBER := 0;');
1972 l(' BEGIN ');
1973 x_bool := 'N';
1974 chk_et_req('PARTY', p_rule_id, x_bool);
1975 IF (x_bool = 'Y') THEN
1976 l('-- query for interface to TCA');
1977 l(' open p_party_cur FOR ');
1978 get_select_str('PARTY', p_rule_id, l_sel_str, 'INT_INT',null);
1979 l( l_sel_str);
1980 -- dbms_output.put_line('l_sel_str get_select_str ' || l_sel_str);
1981 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1982 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
1983 END IF;
1984 l_sel_str := '';
1985 -- select a.organization_name, a.duns_number_c, a.tax_reference,
1986 -- a.party_orig_system, a.party_orig_system_reference, b.party_id
1987 l(' from hz_imp_parties_int a, hz_imp_parties_sg b ');
1988 l(' where b.action_flag = ''I''');
1989 l(' and b.int_row_id = a.rowid ');
1990 l(' and a.batch_id = p_batch_id ');
1991 l(' and b.party_orig_system_reference >= p_from_osr ');
1992 l(' and b.party_orig_system_reference <= p_to_osr ');
1993 l(' and b.batch_mode_flag = p_batch_mode_flag ');
1994 l(' and interface_status is null ; ');
1995 l(' LOOP ');
1996 l(' FETCH p_party_cur BULK COLLECT INTO ');
1997 get_trans_str('PARTY', p_rule_id, l_sel_str, 'INT_INT');
1998 l( l_sel_str);
1999 -- dbms_output.put_line('l_sel_str get_trans_str ' || l_sel_str);
2000 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2001 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2002 END IF;
2003 l_sel_str := '';
2004 /* H_TX4
2005 ,H_TX11
2006 ,H_TX41
2007 , H_P_PARTY_OS
2008 , H_P_PARTY_OSR
2009 , H_P_PARTY_ID */
2010 l(' LIMIT g_limit; ');
2011 l(' IF (p_party_cur%NOTFOUND) THEN ');
2012 l(' l_last_fetch:=TRUE;');
2013 l(' END IF;');
2014 l(' ');
2015 l(' IF H_P_PARTY_OS.COUNT=0 AND l_last_fetch THEN');
2016 l(' EXIT;');
2017 l(' END IF;');
2018 l(' ');
2019 l(' FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP');
2020 -- pass STAGE wherever p_context is required.
2021 get_trans_proc('PARTY', p_rule_id, l_trans_list);
2022 FOR I in l_trans_list.FIRST..l_trans_list.LAST LOOP
2023 -- dbms_output.put_line('l_trans_list get_trans_proc ' || l_trans_list(I));
2024 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2025 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_trans_list(I));
2026 END IF;
2027 l(l_trans_list(I));
2028 l_trans_list.DELETE(I);
2029 END LOOP ;
2030 /* H_TX4(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX4(I),NULL, 'ORGANIZATION_NAME','PARTY','STAGE');
2031 H_TX11(I):=HZ_TRANS_PKG.EXACT_PADDED(H_TX11(I),NULL, 'SIC_CODE','PARTY');
2032 H_TX41(I):=HZ_TRANS_PKG.EXACT(H_TX41(I),NULL, 'DUNS_NUMBER_C','PARTY');
2033 -- H_TX44(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX44(I),NULL, 'TAX_REFERENCE','PARTY');
2034 */
2035 l(' END LOOP;');
2036 l(' SAVEPOINT pop_parties;');
2037 l(' BEGIN ');
2038 l(' FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST');
2039 l(' INSERT INTO HZ_SRCH_PARTIES (');
2040 get_insert_str('HZ_STAGED_PARTIES', p_rule_id, l_sel_str, 'INT_INT');
2041 l( l_sel_str);
2042 -- dbms_output.put_line('l_sel_str get_insert_str ' || l_sel_str);
2043 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2044 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2045 END IF;
2046 l_sel_str := '';
2047
2048 /* PARTY_ID
2049 , PARTY_OS
2050 , PARTY_OSR
2051 , TX4
2052 , TX11
2053 , TX41
2054 -- , TX44 */
2055 l(' ) VALUES ( ');
2056 get_insert_val_str('HZ_STAGED_PARTIES', p_rule_id, l_sel_str, 'INT_INT');
2057 l( l_sel_str);
2058 -- dbms_output.put_line('l_sel_str get_insert_val_str ' || l_sel_str);
2059 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2060 fnd_log.string(fnd_log.LEVEL_PROCEDURE, G_PKG_NAME||l_procedure_name, l_sel_str);
2061 END IF;
2062 l_sel_str := '';
2063 /*
2064 H_P_PARTY_ID(I)
2065 , H_P_PARTY_OS(I)
2066 , H_P_PARTY_OSR(I)
2067 , H_TX4(I)
2068 , H_TX11(I)
2069 , H_TX41(I)
2070 */
2071 -- , H_TX44(I)
2072 l(' ); ');
2073 l(' EXCEPTION ');
2074 l(' WHEN OTHERS THEN');
2075 l(' ROLLBACK to pop_parties;');
2076 l(' -- dbms_output.put_line(SubStr(''Error ''||TO_CHAR(SQLCODE)||'': ''||SQLERRM, 1, 255));');
2077 l(' RAISE;');
2078 l(' END ;');
2079 l(' IF l_last_fetch THEN');
2080 l(' FND_CONCURRENT.AF_Commit;');
2081 l(' EXIT;');
2082 l(' END IF;');
2083 l(' FND_CONCURRENT.AF_Commit;');
2084 l(' ');
2085 l(' END LOOP; ');
2086 l(' CLOSE p_party_cur; ');
2087 ELSE
2088 l(' null;');
2089 END IF;
2090 l(' END pop_parties; ');
2091 EXCEPTION WHEN OTHERS THEN
2092 RAISE FND_API.G_EXC_ERROR;
2093 END gen_pop_parties;
2094
2095
2096 PROCEDURE gen_pop_parties_int (
2097 p_rule_id IN NUMBER
2098 )
2099 IS
2100 l_sel_str VARCHAR2(4000) := NULL;
2101 x_bool VARCHAR2(1);
2102 l_procedure_name VARCHAR2(30) := '.GEN_POP_PARTIES_INT' ;
2103 BEGIN
2104 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2105 fnd_log.string(fnd_log.LEVEL_STATEMENT, G_PKG_NAME||l_procedure_name, 'Enter');
2106 END IF;
2107 x_bool := 'N';
2108
2109 l(' PROCEDURE pop_parties_int ( ');
2110 l(' p_batch_id IN NUMBER, ');
2111 l(' p_from_osr IN VARCHAR2, ');
2112 l(' p_to_osr IN VARCHAR2 ');
2113 l(' ) IS ');
2114 l(' l_last_fetch BOOLEAN := FALSE; ');
2115 l(' p_party_cur HZ_PARTY_STAGE.StageCurTyp; ');
2116 l(' ');
2117 l(' count NUMBER := 0; ');
2118 l(' l_os VARCHAR2(30); ');
2119 l(' BEGIN ');
2120 l(' l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id); ');
2121 chk_et_req('PARTY', p_rule_id, x_bool);
2122 IF (x_bool = 'Y') THEN
2123 -- query for interface to TCA
2124 l(' open p_party_cur FOR ');
2125 get_select_str('PARTY', p_rule_id, l_sel_str, 'INT_TCA', NULL);
2126 l_sel_str := l_sel_str || ' , a.party_id '; --bug 5393826
2127 l( l_sel_str);
2128 -- dbms_output.put_line('get_select_str (PARTY -int tca) ' || l_sel_str);
2129 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2130 fnd_log.string(fnd_log.LEVEL_PROCEDURE, G_PKG_NAME||l_procedure_name, l_sel_str);
2131 END IF;
2132 l_sel_str := '';
2133 /* select a.organization_name, a.duns_number_c, a.tax_reference,
2134 a.party_orig_system, a.party_orig_system_reference
2135 */
2136 l(' from hz_imp_parties_int a ');
2137 l(' where a.batch_id = p_batch_id ');
2138 l(' and a.party_orig_system_reference >= p_from_osr ');
2139 l(' and a.party_orig_system_reference <= p_to_osr ');
2140 l(' and a.party_orig_system = l_os; ');
2141 l(' LOOP ');
2142 l(' FETCH p_party_cur BULK COLLECT INTO ');
2143 get_trans_str('PARTY', p_rule_id, l_sel_str, 'INT_TCA');
2144 l_sel_str := l_sel_str || ' , H_P_PARTY_ID '; -- bug 5393826
2145 l( l_sel_str);
2146 -- dbms_output.put_line('get_trans_str PARTY int tca ' || l_sel_str);
2147 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2148 fnd_log.string(fnd_log.LEVEL_PROCEDURE, G_PKG_NAME||l_procedure_name, l_sel_str);
2149 END IF;
2150 l_sel_str := '';
2151 /* H_TX4
2152 ,H_TX11
2153 ,H_TX41
2154 , H_P_PARTY_OS
2155 , H_P_PARTY_OSR */
2156 l(' LIMIT g_limit; ');
2157 l(' IF p_party_cur%NOTFOUND THEN ');
2158 l(' l_last_fetch:=TRUE; ');
2159 l(' END IF; ');
2160 l(' ');
2161 l(' IF H_P_PARTY_OS.COUNT=0 AND l_last_fetch THEN ');
2162 l(' EXIT; ');
2163 l(' END IF; ');
2164 l(' ');
2165 l(' FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP ');
2166 -- pass STAGE wherever p_context is required.
2167 get_trans_proc('PARTY', p_rule_id, l_trans_list);
2168 FOR I in l_trans_list.FIRST..l_trans_list.LAST LOOP
2169 -- dbms_output.put_line('get_trans_proc (PARTY int tca)' || l_trans_list(I));
2170 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2171 fnd_log.string(fnd_log.LEVEL_PROCEDURE, G_PKG_NAME||l_procedure_name, l_trans_list(I));
2172 END IF;
2173 l(l_trans_list(I));
2174 l_trans_list.DELETE(I);
2175 END LOOP ;
2176 /* H_TX4(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX4(I),NULL, 'ORGANIZATION_NAME','PARTY','STAGE');
2177 H_TX11(I):=HZ_TRANS_PKG.EXACT_PADDED(H_TX11(I),NULL, 'SIC_CODE','PARTY');
2178 H_TX41(I):=HZ_TRANS_PKG.EXACT(H_TX41(I),NULL, 'DUNS_NUMBER_C','PARTY');
2179 */
2180 l(' END LOOP; ');
2181 l(' SAVEPOINT pop_parties_int; ');
2182 l(' BEGIN ');
2183 l(' FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST ');
2184 l(' INSERT INTO HZ_SRCH_PARTIES ( ');
2185 get_insert_str('HZ_STAGED_PARTIES', p_rule_id, l_sel_str, 'INT_TCA');
2186 l_sel_str := l_sel_str || ' , PARTY_ID '; -- bug 5393826
2187 l( l_sel_str);
2188 -- dbms_output.put_line('get_insert_str PARTY int tca ' || l_sel_str);
2189 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2190 fnd_log.string(fnd_log.LEVEL_PROCEDURE, G_PKG_NAME||l_procedure_name, l_sel_str);
2191 END IF;
2192 l_sel_str := '';
2193 /* PARTY_OS
2194 , PARTY_OSR
2195 , TX4
2196 , TX11
2197 , TX41*/
2198 l(' ) VALUES ( ');
2199 get_insert_val_str('HZ_STAGED_PARTIES', p_rule_id, l_sel_str, 'INT_TCA');
2200 l_sel_str := l_sel_str || ' , H_P_PARTY_ID(I) '; -- bug 5393826
2201 l( l_sel_str);
2202 -- dbms_output.put_line('l_sel_str get_insert_val_str ' || l_sel_str);
2203 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2204 fnd_log.string(fnd_log.LEVEL_PROCEDURE, G_PKG_NAME||l_procedure_name, l_sel_str);
2205 END IF;
2206 l_sel_str := '';
2207 /* H_P_PARTY_OS(I)
2208 , H_P_PARTY_OSR(I)
2209 , H_TX4(I)
2210 , H_TX11(I)
2211 , H_TX41(I)
2212 */
2213 l(' ); ');
2214 l(' EXCEPTION ');
2215 l(' WHEN OTHERS THEN ');
2216 l(' ROLLBACK to pop_parties_int; ');
2217 l('-- dbms_output.put_line(SubStr(''Error ''||TO_CHAR(SQLCODE)||'': ''||SQLERRM, 1, 255)); ');
2218 l(' RAISE; ');
2219 l(' END ; ');
2220 l(' IF l_last_fetch THEN ');
2221 l(' FND_CONCURRENT.AF_Commit; ');
2222 l(' EXIT; ');
2223 l(' END IF; ');
2224 l(' FND_CONCURRENT.AF_Commit; ');
2225 l(' ');
2226 l(' END LOOP; ');
2227 l(' CLOSE p_party_cur; ');
2228 ELSE
2229 l(' null; ');
2230 END IF;
2231 l(' END pop_parties_int; ');
2232 EXCEPTION WHEN OTHERS THEN
2233 RAISE FND_API.G_EXC_ERROR;
2234 END gen_pop_parties_int ;
2235
2236
2237 PROCEDURE gen_pop_party_sites (
2238 p_rule_id IN NUMBER
2239 )
2240 IS
2241 l_sel_str VARCHAR2(4000) := NULL;
2242 x_bool VARCHAR2(1);
2243 l_procedure_name VARCHAR2(30) := '.GEN_POP_PARTY_SITES' ;
2244 BEGIN
2245 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2246 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
2247 END IF;
2248 l('');
2249 l(' PROCEDURE pop_party_sites ( ');
2250 l(' p_batch_id IN NUMBER, ');
2251 l(' p_from_osr IN VARCHAR2, ');
2252 l(' p_to_osr IN VARCHAR2, ');
2253 l(' p_batch_mode_flag IN VARCHAR2 ');
2254 l(' ) IS ');
2255 l(' l_last_fetch BOOLEAN := FALSE; ');
2256 l(' l_party_site_cur HZ_PARTY_STAGE.StageCurTyp; ');
2257 l(' ');
2258 l(' BEGIN ');
2259 x_bool := 'N';
2260 chk_et_req('PARTY_SITES', p_rule_id, x_bool);
2261 IF (x_bool = 'Y') THEN
2262 l('-- query for interface to tca ');
2263 l(' open l_party_site_cur for ');
2264 get_select_str('PARTY_SITES', p_rule_id, l_sel_str, 'INT_INT', DO_STD_CHK);
2265 l( l_sel_str);
2266 -- dbms_output.put_line('select_str (PARTY_SITES) = ' || l_sel_str);
2267 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2268 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2269 END IF;
2270 l_sel_str := '';
2271 /*
2272 select a.party_orig_system, a.party_orig_system_reference,
2273 a.site_orig_system, a.site_orig_system_reference,
2274 b.party_id, b.party_site_id, a.address1, a.postal_code
2275 */
2276 l(' from hz_imp_addresses_int a, hz_imp_addresses_sg b ');
2277 l(' where a.batch_id = p_batch_id ');
2278 l(' and b.action_flag = ''I'' ');
2279 l(' and b.int_row_id = a.rowid ');
2280 --l(' and nvl(a.accept_standardized_flag, ''N'') <> ''Y'' ');
2281 l(' and a.party_orig_system_reference >= p_from_osr ');
2282 l(' and a.party_orig_system_reference <= p_to_osr ');
2283 l(' and b.batch_mode_flag = p_batch_mode_flag ');
2284 l(' and interface_status is null ; ');
2285
2286 /*
2287 l(' union ');
2288 -- addition starts --
2289 get_select_str('PARTY_SITES', p_rule_id, l_sel_str, 'INT_INT', DO_STD_CHK);
2290 l( l_sel_str);
2291 dbms_output.put_line('select_str (PARTY_SITES) = ' || l_sel_str);
2292 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2293 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2294 END IF;
2295 l_sel_str := '';
2296 l(' from hz_imp_addresses_int a, hz_imp_addresses_sg b ');
2297 l(' where a.batch_id = p_batch_id ');
2298 l(' and b.action_flag = ''I'' ');
2299 l(' and b.int_row_id = a.rowid ');
2300 l(' and a.accept_standardized_flag = ''Y'' ');
2301 l(' and a.party_orig_system_reference >= p_from_osr ');
2302 l(' and a.party_orig_system_reference <= p_to_osr; ');
2303 */
2304 -- addition ends --
2305 l(' LOOP ');
2306 /* l(' IF l_party_site_cur%NOTFOUND THEN ');
2307 l(' l_last_fetch:=TRUE; ');
2308 l(' END IF; '); */
2309 l(' FETCH l_party_site_cur BULK COLLECT INTO ');
2310 get_trans_str('PARTY_SITES', p_rule_id, l_sel_str, 'INT_INT');
2311 l( l_sel_str);
2312 -- dbms_output.put_line('get_trans_str (PARTY_SITES) =' || l_sel_str);
2313 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2314 fnd_log.string(fnd_log.LEVEL_PROCEDURE, G_PKG_NAME||l_procedure_name, l_sel_str);
2315 END IF;
2316 l_sel_str := '';
2317 /* H_P_PARTY_OS,
2318 H_P_PARTY_OSR,
2319 H_P_PS_OS,
2320 H_P_PS_OSR,
2321 H_P_PARTY_ID,
2322 H_P_PARTY_SITE_ID,
2323 H_TX4,
2324 H_TX6
2325 */
2326 l(' LIMIT g_limit; ');
2327 l(' ');
2328 l(' IF (l_party_site_cur%NOTFOUND) THEN ');
2329 l(' l_last_fetch := TRUE;');
2330 l(' END IF;');
2331 l(' ');
2332 l(' IF H_P_PS_OS.COUNT = 0 AND l_last_fetch THEN');
2333 l(' EXIT;');
2334 l(' END IF;');
2335 l(' ');
2336 l(' FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP ');
2337 get_trans_proc('PARTY_SITES', p_rule_id, l_trans_list);
2338 FOR I in l_trans_list.FIRST..l_trans_list.LAST LOOP
2339 -- dbms_output.put_line('get_trans_proc (PARTY_SITES) = ' || l_trans_list(I));
2340 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2341 fnd_log.string(fnd_log.LEVEL_PROCEDURE, G_PKG_NAME||l_procedure_name, l_trans_list(I));
2342 END IF;
2343 l(l_trans_list(I));
2344 l_trans_list.DELETE(I);
2345 END LOOP ;
2346 /* H_TX4(I):= HZ_TRANS_PKG.WRADDRESS_CLEANSE (H_TX4(I),NULL, ''ADDRESS'',''PARTY_SITE'');
2347 H_TX6(I):= HZ_TRANS_PKG.CLEANSED_EMAIL (H_TX6(I),NULL, 'POSTAL_CODE','PARTY_SITE');
2348 H_TX11(I):= HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(I),NULL, 'POSTAL_CODE','PARTY_SITE');
2349 */
2350 l(' END LOOP; ');
2351 l(' SAVEPOINT pop_party_sites; ');
2352 l(' BEGIN ');
2353 l(' FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST ');
2354 l(' INSERT INTO HZ_SRCH_PSITES ( ');
2355 get_insert_str('HZ_STAGED_PARTY_SITES', p_rule_id, l_sel_str, 'INT_INT');
2356 l( l_sel_str);
2357 -- dbms_output.put_line('l_sel_str get_insert_str ' || l_sel_str);
2358 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2359 fnd_log.string(fnd_log.LEVEL_PROCEDURE, G_PKG_NAME||l_procedure_name, l_sel_str);
2360 END IF;
2361 l_sel_str := '';
2362 /* PARTY_SITE_ID,
2363 PARTY_ID,
2364 PARTY_OS,
2365 PARTY_OSR,
2366 PARTY_SITE_OS,
2367 PARTY_SITE_OSR
2368 , TX4
2369 , TX6
2370 , TX11
2371 */
2372 l(' ) VALUES ( ');
2373 get_insert_val_str('HZ_STAGED_PARTY_SITES', p_rule_id, l_sel_str, 'INT_INT');
2374 l( l_sel_str);
2375 -- dbms_output.put_line(' get_insert_val_str (PARTY_SITES) = ' || l_sel_str);
2376 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2377 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2378 END IF;
2379 l_sel_str := '';
2380 /* H_P_PARTY_SITE_ID(I),
2381 H_P_PARTY_ID(I),
2382 H_P_PARTY_OS(I),
2383 H_P_PARTY_OSR(I),
2384 H_P_PS_OS(I),
2385 H_P_PS_OSR(I)
2386 , H_TX4(I)
2387 , H_TX6(I)
2388 , H_TX11(I)
2389 */
2390 l(' ); ');
2391 l(' EXCEPTION ');
2392 l(' WHEN OTHERS THEN ');
2393 l(' ROLLBACK to pop_party_sites; ');
2394 l(' RAISE; ');
2395 l(' END; ');
2396 l(' ');
2397 l(' IF l_last_fetch THEN ');
2398 l(' FND_CONCURRENT.AF_Commit; ');
2399 l(' EXIT; ');
2400 l(' END IF; ');
2401 l(' FND_CONCURRENT.AF_Commit; ');
2402 l(' ');
2403 l(' END LOOP; ');
2404 l(' CLOSE l_party_site_cur; ');
2405 ELSE
2406 l(' null;');
2407 END IF;
2408 l(' END pop_party_sites; ');
2409 EXCEPTION WHEN OTHERS THEN
2410 RAISE FND_API.G_EXC_ERROR;
2411 END gen_pop_party_sites;
2412
2413
2414 PROCEDURE gen_pop_party_sites_int (
2415 p_rule_id IN NUMBER
2416 )
2417 IS
2418 l_sel_str VARCHAR2(4000) := NULL;
2419 x_bool VARCHAR2(1);
2420 l_procedure_name VARCHAR2(30) := '.GEN_POP_PARTY_SITES_INT' ;
2421 BEGIN
2422 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2423 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
2424 END IF;
2425 l(' ');
2426 l(' PROCEDURE pop_party_sites_int ( ');
2427 l(' p_batch_id IN NUMBER, ');
2428 l(' p_from_osr IN VARCHAR2, ');
2429 l(' p_to_osr IN VARCHAR2 ');
2430 l(' ) IS ');
2431
2432 l(' l_last_fetch BOOLEAN := FALSE; ');
2433 l(' l_party_site_cur HZ_PARTY_STAGE.StageCurTyp; ');
2434 l(' ');
2435 l(' l_os VARCHAR2(30); ');
2436 l(' BEGIN ');
2437 l(' l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id); ');
2438 x_bool := 'N';
2439 chk_et_req('PARTY_SITES', p_rule_id, x_bool);
2440 IF (x_bool = 'Y') THEN
2441
2442 -- query for interface to tca
2443 l(' open l_party_site_cur for ');
2444 get_select_str('PARTY_SITES', p_rule_id, l_sel_str, 'INT_TCA', null);
2445 l_sel_str := l_sel_str || ' , a.party_id '; --bug 5393826
2446 l( l_sel_str);
2447 -- dbms_output.put_line('select_str (PARTY_SITES int tca) = ' || l_sel_str);
2448 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2449 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2450 END IF;
2451 l_sel_str := '';
2452 /* select a.party_orig_system, a.party_orig_system_reference,
2453 a.site_orig_system, a.site_orig_system_reference,
2454 a.address1, a.postal_code */
2455 l(' from hz_imp_addresses_int a ');
2456 l(' where a.batch_id = p_batch_id ');
2457 l(' and a.party_orig_system_reference >= p_from_osr ');
2458 l(' and a.party_orig_system_reference <= p_to_osr ');
2459 l(' and a.party_orig_system = l_os; ');
2460 l(' LOOP ');
2461 l(' FETCH l_party_site_cur BULK COLLECT INTO ');
2462 get_trans_str('PARTY_SITES', p_rule_id, l_sel_str, 'INT_TCA');
2463 l_sel_str := l_sel_str || ' , H_P_PARTY_ID '; -- bug 5393826
2464 l( l_sel_str);
2465 -- dbms_output.put_line('get_trans_str (PARTY_SITES) =' || l_sel_str);
2466 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2467 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2468 END IF;
2469 l_sel_str := '';
2470 /* H_P_PARTY_OS,
2471 H_P_PARTY_OSR,
2472 H_P_PS_OS,
2473 H_P_PS_OSR,
2474 H_TX4,
2475 H_TX6 */
2476 l(' LIMIT g_limit; ');
2477 l(' ');
2478 l(' IF l_party_site_cur%NOTFOUND THEN ');
2479 l(' l_last_fetch:=TRUE; ');
2480 l(' END IF; ');
2481 l(' IF H_P_PS_OS.COUNT=0 AND l_last_fetch THEN ');
2482 l(' EXIT; ');
2483 l(' END IF; ');
2484 l(' ');
2485 l(' FOR I in H_P_PS_OSR.FIRST..H_P_PS_OSR.LAST LOOP ');
2486 get_trans_proc('PARTY_SITES', p_rule_id, l_trans_list);
2487 FOR I in l_trans_list.FIRST..l_trans_list.LAST LOOP
2488 -- dbms_output.put_line('get_trans_proc (PARTY_SITES) = ' || l_trans_list(I));
2489 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2490 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_trans_list(I));
2491 END IF;
2492 l(l_trans_list(I));
2493 l_trans_list.DELETE(I);
2494 END LOOP ;
2495 /*
2496 H_TX4(I):= HZ_TRANS_PKG.WRADDRESS_CLEANSE (H_TX4(I),NULL, 'ADDRESS','PARTY_SITE');
2497 H_TX6(I):= HZ_TRANS_PKG.CLEANSED_EMAIL (H_TX6(I),NULL, 'POSTAL_CODE','PARTY_SITE');
2498 H_TX11(I):= HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(I),NULL, 'POSTAL_CODE','PARTY_SITE');
2499 */
2500 l(' END LOOP; ');
2501 l(' SAVEPOINT pop_party_sites_int; ');
2502 l(' BEGIN ');
2503 l(' FORALL I in H_P_PS_OSR.FIRST..H_P_PS_OSR.LAST ');
2504 l(' INSERT INTO HZ_SRCH_PSITES ( ');
2505 get_insert_str('HZ_STAGED_PARTY_SITES', p_rule_id, l_sel_str, 'INT_TCA');
2506 l_sel_str := l_sel_str || ' , PARTY_ID ';-- bug 5393826
2507 l( l_sel_str);
2508 -- dbms_output.put_line('l_sel_str get_insert_str ' || l_sel_str);
2509 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2510 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2511 END IF;
2512 l_sel_str := '';
2513 /* PARTY_OS,
2514 PARTY_OSR,
2515 PARTY_SITE_OS,
2516 PARTY_SITE_OSR
2517 , TX4
2518 , TX6
2519 , TX11 */
2520 l(' ) VALUES ( ');
2521 get_insert_val_str('HZ_STAGED_PARTY_SITES', p_rule_id, l_sel_str, 'INT_TCA');
2522 l_sel_str := l_sel_str || ' , H_P_PARTY_ID(I) '; -- bug 5393826
2523 l( l_sel_str);
2524 -- dbms_output.put_line(' get_insert_val_str (PARTY_SITES) = ' || l_sel_str);
2525 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2526 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2527 END IF;
2528 l_sel_str := '';
2529 /* H_P_PARTY_OS(I),
2530 H_P_PARTY_OSR(I),
2531 H_P_PS_OS(I),
2532 H_P_PS_OSR(I)
2533 , H_TX4(I)
2534 , H_TX6(I)
2535 , H_TX11(I) */
2536 l(' ); ');
2537 l(' EXCEPTION ');
2538 l(' WHEN OTHERS THEN ');
2539 l(' ROLLBACK to pop_party_sites_int; ');
2540 l(' RAISE; ');
2541 l(' END; ');
2542 l(' ');
2543 l(' IF l_last_fetch THEN ');
2544 l(' FND_CONCURRENT.AF_Commit; ');
2545 l(' EXIT; ');
2546 l(' END IF; ');
2547 l(' FND_CONCURRENT.AF_Commit; ');
2548 l(' ');
2549 l(' END LOOP; ');
2550 l(' CLOSE l_party_site_cur; ');
2551 ELSE
2552 l(' null;');
2553 END IF;
2554 l(' END pop_party_sites_int; ');
2555 EXCEPTION WHEN OTHERS THEN
2556 RAISE FND_API.G_EXC_ERROR;
2557
2558 END gen_pop_party_sites_int;
2559
2560 PROCEDURE gen_static_text(
2561 p_rule_id IN NUMBER
2562 ) IS
2563 l_procedure_name VARCHAR2(30) := '.GEN_STATIC_TEXT' ;
2564 BEGIN
2565 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2566 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
2567 END IF;
2568 /*
2569 */
2570 l('PROCEDURE POP_INTERFACE_SEARCH_TAB (');
2571 l(' p_batch_id IN NUMBER,');
2572 l(' p_from_osr IN VARCHAR2,');
2573 l(' p_to_osr IN VARCHAR2,');
2574 l(' x_return_status OUT NOCOPY VARCHAR2,');
2575 l(' x_msg_count OUT NOCOPY NUMBER,');
2576 l(' x_msg_data OUT NOCOPY VARCHAR2');
2577 l(' ) IS');
2578 l(' ');
2579 l(' BEGIN');
2580 l(' ');
2581 l(' x_return_status := fnd_api.g_ret_sts_success; ');
2582 l(' pop_parties(p_batch_id, p_from_osr, p_to_osr);');
2583 l(' pop_party_sites(p_batch_id, p_from_osr, p_to_osr);');
2584 l(' pop_cp(p_batch_id, p_from_osr, p_to_osr);');
2585 l(' pop_contacts(p_batch_id, p_from_osr, p_to_osr);');
2586 l(' -- build_srch_indexes(); ');
2587 l(' EXCEPTION WHEN others THEN ');
2588 l(' x_return_status := fnd_api.g_ret_sts_unexp_error; ');
2589 l(' fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, ');
2590 l(' p_count => x_msg_count, ');
2591 l(' p_data => x_msg_data); ');
2592 l(' RAISE; ');
2593 l(' END POP_INTERFACE_SEARCH_TAB;');
2594 l(' ');
2595
2596 l(' PROCEDURE POP_INT_TCA_SEARCH_TAB ( ');
2597 l(' p_batch_id IN NUMBER, ');
2598 l(' p_from_osr IN VARCHAR2, ');
2599 l(' p_to_osr IN VARCHAR2 , ');
2600 l(' x_return_status OUT NOCOPY VARCHAR2, ');
2601 l(' x_msg_count OUT NOCOPY NUMBER, ');
2602 l(' x_msg_data OUT NOCOPY VARCHAR2 ');
2603 l(' ) IS ');
2604 l(' ');
2605 l(' l_a VARCHAR2(1); ');
2606 l(' l_b VARCHAR2(1); ');
2607 l(' l_c NUMBER; ');
2608 l(' l_d NUMBER; ');
2609 l(' l_e VARCHAR2(1); ');
2610 l(' ');
2611
2612 l(' BEGIN ');
2613 l(' x_return_status := fnd_api.g_ret_sts_success; ');
2614 l(' select batch_dedup_flag, registry_dedup_flag, batch_dedup_match_rule_id, registry_dedup_match_rule_id, addr_val_flag ');
2615 l(' into l_a, l_b, l_c, l_d, l_e ');
2616 l(' from hz_imp_batch_summary ');
2617 l(' where batch_id = p_batch_id; ');
2618 l(' ');
2619 l(' IF (l_b = ''Y'') THEN ');
2620 l(' IF ((l_a = ''Y'') AND (l_c = l_d) ) THEN ');
2621 l(' IF (l_e = ''Y'') THEN ');
2622 l(' pop_party_sites_int(p_batch_id, p_from_osr, p_to_osr); ');
2623 l(' END IF; ');
2624 l(' ELSIF (l_a = ''Y'') THEN ');
2625 l(' pop_parties_int(p_batch_id, p_from_osr, p_to_osr); ');
2626 l(' pop_party_sites_int(p_batch_id, p_from_osr, p_to_osr); ');
2627 l(' pop_cp_int(p_batch_id, p_from_osr, p_to_osr); ');
2628 l(' pop_contacts_int(p_batch_id, p_from_osr, p_to_osr); ');
2629 l(' END IF; ');
2630 l(' END IF; ');
2631
2632 l(' EXCEPTION WHEN others THEN ');
2633 l(' x_return_status := fnd_api.g_ret_sts_unexp_error; ');
2634 l(' fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, ');
2635 l(' p_count => x_msg_count, ');
2636 l(' p_data => x_msg_data); ');
2637 l(' RAISE; ');
2638 l(' END POP_INT_TCA_SEARCH_TAB; ');
2639
2640 l(' ');
2641 EXCEPTION WHEN OTHERS THEN
2642 RAISE FND_API.G_EXC_ERROR;
2643 END gen_static_text;
2644
2645 PROCEDURE gen_get_contact_cur (
2646 p_rule_id IN NUMBER
2647 )
2648 IS
2649 l_sel_str VARCHAR2(4000) := NULL;
2650 x_bool VARCHAR2(1) := NULL;
2651 l_procedure_name VARCHAR2(30) := '.GEN_GET_CONTACT_CUR' ;
2652 BEGIN
2653 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2654 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
2655 END IF;
2656 l(' PROCEDURE get_contact_cur( ');
2657 l(' p_batch_id IN NUMBER, ');
2658 l(' p_from_osr IN VARCHAR2, ');
2659 l(' p_to_osr IN VARCHAR2, ');
2660 l(' p_batch_mode_flag IN VARCHAR2, ');
2661 l(' x_contact_cur IN OUT NOCOPY StageImpContactCurTyp ');
2662 l(' ) IS ');
2663 l(' is_using_allow_cust_attr VARCHAR2(1); ');
2664 l(' CURSOR c1 is select ''Y'' ');
2665 l(' from hz_trans_attributes_vl ');
2666 l(' where entity_name = ''CONTACTS'' ');
2667 -- l(' and userenv(''LANG'') = ''US'' ');
2668 l(' and attribute_name = ''CONTACT_NAME'' ');
2669 l(' and attribute_id in ( ');
2670 l(' select attribute_id ');
2671 l(' from hz_match_rule_primary b ');
2672 l(' where match_rule_id = '|| p_rule_id);
2673 l(' union ');
2674 l(' select attribute_id ');
2675 l(' from hz_match_rule_secondary b ');
2676 l(' where match_rule_id = '|| p_rule_id ||' ) and rownum = 1; ');
2677 l(' ');
2678 l(' BEGIN ');
2679 x_bool := 'N';
2680 chk_et_req('CONTACTS', p_rule_id, x_bool);
2681 IF (x_bool = 'Y') THEN
2682 l(' OPEN c1; ');
2683 l(' LOOP ');
2684 l(' FETCH c1 INTO is_using_allow_cust_attr; ');
2685 l(' EXIT when c1%NOTFOUND; ');
2686 l(' END LOOP; ');
2687 l(' CLOSE c1; ');
2688 l(' IF (is_using_allow_cust_attr = ''Y'') THEN ');
2689 l(' OPEN x_contact_cur FOR ');
2690 -- cursor1 if match_rule has contact_name, person_first_name, person_last_name in it.
2691 get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_INT', NULL);
2692 l_sel_str := l_sel_str || ', c.person_first_name || '' '' || c.person_last_name as person_name' ;
2693 -- dbms_output.put_line('l_sel_str ' || l_sel_str);
2694 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2695 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2696 END IF;
2697 l(l_sel_str);
2698 l_sel_str := '';
2699
2700 /* l(' select a.sub_orig_system, a.sub_orig_system_reference, ');
2701 l(' a.site_orig_system, a.site_orig_system_reference, ');
2702 l(' a.contact_orig_system, a.contact_orig_system_reference, ');
2703 l(' a.contact_number, a.title, c.person_first_name || '' '' || c.person_last_name as person_name ');
2704 */
2705 l(' from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b, HZ_IMP_PARTIES_INT c ');
2706 l(' where a.batch_id = p_batch_id ');
2707 l(' and b.action_flag = ''I'' ');
2708 l(' and b.int_row_id = a.rowid ');
2709 l(' and a.sub_orig_system_reference >= p_from_osr ');
2710 l(' and a.sub_orig_system_reference <= p_to_osr ');
2711 -- for contact_name
2712 l(' and a.sub_orig_system = c.party_orig_system ');
2713 l(' and a.batch_id = c.batch_id ');
2714 -- l(' and a.sub_orig_system = c.party_orig_system ');
2715 l(' and b.sub_id = c.party_id ');
2716 l(' and b.batch_mode_flag = p_batch_mode_flag ');
2717 l(' and a.interface_status is null ');
2718 l(' union all ');
2719 get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_INT', NULL);
2720 l_sel_str := l_sel_str || ', c.party_name as person_name' ;
2721 l(l_sel_str);
2722 -- dbms_output.put_line('get_select_str (CONTACTS)' || l_sel_str);
2723 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2724 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2725 END IF;
2726 l_sel_str := '';
2727 /* l(' select a.sub_orig_system, a.sub_orig_system_reference, ');
2728 l(' a.site_orig_system, a.site_orig_system_reference, ');
2729 l(' a.contact_orig_system, a.contact_orig_system_reference, ');
2730 l(' a.contact_number, a.title, c.party_name as person_name ');
2731 */
2732 l(' from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b, hz_parties c ');
2733 l(' where a.batch_id = p_batch_id ');
2734 l(' and b.action_flag = ''I'' ');
2735 l(' and b.int_row_id = a.rowid ');
2736 l(' and a.sub_orig_system_reference >= p_from_osr ');
2737 l(' and a.sub_orig_system_reference <= p_to_osr ');
2738 -- for contact_name
2739 -- l(' and a.sub_orig_system = c.party_id ');
2740 l(' and b.sub_id = c.party_id ');
2741 l(' and b.batch_mode_flag = p_batch_mode_flag ');
2742 l(' and a.interface_status is null ');
2743 l(' ; ');
2744 l(' ELSE ');
2745 l(' OPEN x_contact_cur FOR ');
2746 -- cursor2 if specified match_rule does not have name associated with it.
2747 get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_INT', NULL);
2748 l_sel_str := l_sel_str || ' ' ; -- , null person_name
2749 l(l_sel_str);
2750 -- dbms_output.put_line('get_select_str (CONTACTS)' || l_sel_str);
2751 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2752 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2753 END IF;
2754 l_sel_str := '';
2755 /* l(' select a.sub_orig_system, a.sub_orig_system_reference, ');
2756 l(' a.site_orig_system, a.site_orig_system_reference, ');
2757 l(' a.contact_orig_system, a.contact_orig_system_reference, ');
2758 l(' a.contact_number, a.title, null person_name ');
2759 */
2760 l(' from HZ_IMP_CONTACTS_INT a, HZ_IMP_CONTACTS_SG b ');
2761 l(' where a.batch_id = p_batch_id ');
2762 l(' and b.action_flag = ''I'' ');
2763 l(' and b.int_row_id = a.rowid ');
2764 /* l(' and b.contact_orig_system = a.contact_orig_system ');
2765 l(' and b.contact_orig_system_reference = a.contact_orig_system_reference ');
2766 l(' and b.sub_orig_system = a.sub_orig_system ');
2767 l(' and b.sub_orig_system_reference = a.sub_orig_system_reference ');
2768 l(' and b.obj_orig_system = a.obj_orig_system ');
2769 l(' and b.obj_orig_system_reference = a.obj_orig_system_reference ');
2770 l(' '); */
2771 l(' and a.sub_orig_system_reference >= p_from_osr ');
2772 l(' and a.sub_orig_system_reference <= p_to_osr ');
2773 l(' and b.batch_mode_flag = p_batch_mode_flag ');
2774 l(' and a.interface_status is null ; ');
2775 l(' END IF; ');
2776 ELSE
2777 l(' null; --5');
2778 END IF;
2779 l(' END get_contact_cur; ');
2780 l(' ');
2781 EXCEPTION WHEN OTHERS THEN
2782 RAISE FND_API.G_EXC_ERROR;
2783 END gen_get_contact_cur;
2784
2785
2786 PROCEDURE gen_get_contact_cur_int (
2787 p_rule_id IN NUMBER
2788 )
2789 IS
2790 l_sel_str VARCHAR2(4000) := NULL;
2791 x_bool VARCHAR2(1) := NULL;
2792 l_procedure_name VARCHAR2(30) := '.GEN_GET_CONTACT_CUR_INT' ;
2793 BEGIN
2794 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2795 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
2796 END IF;
2797 l(' PROCEDURE get_contact_cur_int( ');
2798 l(' p_batch_id IN NUMBER, ');
2799 l(' p_from_osr IN VARCHAR2, ');
2800 l(' p_to_osr IN VARCHAR2, ');
2801 l(' x_contact_cur IN OUT NOCOPY StageImpContactCurTyp ');
2802 l(' ) IS ');
2803 l(' is_using_allow_cust_attr VARCHAR2(1); ');
2804 l(' CURSOR c1 is select ''Y'' ');
2805 l(' from hz_trans_attributes_vl ');
2806 l(' where entity_name = ''CONTACTS'' ');
2807 -- l(' and userenv(''LANG'') = ''US'' ');
2808 l(' and attribute_name = ''CONTACT_NAME'' ');
2809 l(' and attribute_id in ( ');
2810 l(' select attribute_id ');
2811 l(' from hz_match_rule_primary b ');
2812 l(' where match_rule_id = '|| p_rule_id);
2813 l(' union ');
2814 l(' select attribute_id ');
2815 l(' from hz_match_rule_secondary b ');
2816 l(' where match_rule_id = '|| p_rule_id ||' ) and rownum = 1; ');
2817 l(' ');
2818 l(' l_os VARCHAR2(30); ');
2819 l(' BEGIN --');
2820 l(' l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id); ');
2821 x_bool := 'N';
2822 chk_et_req('CONTACTS', p_rule_id, x_bool);
2823 IF (x_bool = 'Y') THEN
2824 l(' OPEN c1; ');
2825 l(' LOOP ');
2826 l(' FETCH c1 into is_using_allow_cust_attr; ');
2827 l(' EXIT when c1%NOTFOUND; ');
2828 l(' END LOOP; ');
2829 l(' IF (is_using_allow_cust_attr = ''Y'') THEN ');
2830 l(' OPEN x_contact_cur FOR ');
2831 -- cursor1 if match_rule has contact_name, person_first_name, person_last_name in it.
2832 get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_TCA', NULL);
2833 -- dbms_output.put_line('l_sel_str (CONTACTS int tca 1)' || l_sel_str);
2834 l_sel_str := l_sel_str || ', c.person_first_name || '' '' || c.person_last_name as person_name' ;
2835 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2836 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2837 END IF;
2838 -- dbms_output.put_line('l_sel_str (CONTACTS int tca)' || l_sel_str);
2839 l(l_sel_str);
2840 l_sel_str := '';
2841 /* select a.sub_orig_system, a.sub_orig_system_reference,
2842 a.site_orig_system, a.site_orig_system_reference,
2843 a.contact_orig_system, a.contact_orig_system_reference,
2844 a.contact_number, a.title, c.person_first_name || ' ' || c.person_last_name as person_name
2845 */
2846 l(' from HZ_IMP_CONTACTS_INT a, HZ_IMP_PARTIES_INT c ');
2847 l(' where a.batch_id = p_batch_id ');
2848 l(' and a.sub_orig_system_reference >= p_from_osr ');
2849 l(' and a.sub_orig_system_reference <= p_to_osr ');
2850 l(' and a.sub_orig_system_reference = c.party_orig_system_reference ');
2851 -- for contact_name
2852 l(' and a.sub_orig_system = c.party_orig_system ');
2853 l(' and a.batch_id = c.batch_id ');
2854 -- l(' and a.sub_orig_system = c.party_orig_system ');
2855 l(' and a.sub_orig_system = l_os; ');
2856 /* l(' union ');
2857 get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_TCA', NULL);
2858 l_sel_str := l_sel_str || ', c.party_name as person_name' ;
2859 l(l_sel_str);
2860 dbms_output.put_line('get_select_str (CONTACTS int_tca)' || l_sel_str);
2861 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2862 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2863 END IF;
2864
2865 l_sel_str := ''; */
2866 /* select a.sub_orig_system, a.sub_orig_system_reference,
2867 a.site_orig_system, a.site_orig_system_reference,
2868 a.contact_orig_system, a.contact_orig_system_reference,
2869 a.contact_number, a.title, c.party_name as person_name */
2870 /* l(' from HZ_IMP_CONTACTS_INT a, hz_parties c ');
2871 l(' where a.batch_id = p_batch_id ');
2872 l(' and a.sub_orig_system >= p_from_osr ');
2873 l(' and a.sub_orig_system <= p_to_osr ');
2874 -- for contact_name
2875 l(' -- and a.sub_orig_system = c.party_id; '); */
2876 l(' ELSE ');
2877 l(' OPEN x_contact_cur FOR ');
2878 -- cursor2 if specified match_rule does not have name associated with it.
2879 get_select_str('CONTACTS', p_rule_id, l_sel_str, 'INT_TCA', NULL);
2880 l_sel_str := l_sel_str || ', null person_name' ;
2881 l(l_sel_str);
2882 -- dbms_output.put_line('get_select_str (CONTACTS int_tca)' || l_sel_str);
2883 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2884 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2885 END IF;
2886
2887 l_sel_str := '';
2888 /* select a.sub_orig_system, a.sub_orig_system_reference,
2889 a.site_orig_system, a.site_orig_system_reference,
2890 a.contact_orig_system, a.contact_orig_system_reference,
2891 a.contact_number, a.title, null person_name */
2892 l(' from HZ_IMP_CONTACTS_INT a ');
2893 l(' where a.batch_id = p_batch_id ');
2894 l(' and a.sub_orig_system_reference >= p_from_osr ');
2895 l(' and a.sub_orig_system_reference <= p_to_osr ');
2896 l(' and a.sub_orig_system = l_os; ');
2897 l(' END IF; ');
2898 ELSE
2899 l(' null; ');
2900 END IF;
2901 l(' END get_contact_cur_int; ');
2902 l(' ');
2903 EXCEPTION WHEN OTHERS THEN
2904 RAISE FND_API.G_EXC_ERROR;
2905 END gen_get_contact_cur_int;
2906
2907 PROCEDURE gen_pop_contacts (
2908 p_rule_id IN NUMBER
2909 )
2910 IS
2911 l_sel_str VARCHAR2(4000) := NULL;
2912 x_bool VARCHAR2(1);
2913 l_procedure_name VARCHAR2(30) := '.GEN_POP_CONTACTS' ;
2914 BEGIN
2915 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2916 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
2917 END IF;
2918 l(' PROCEDURE pop_contacts ( ');
2919 l(' p_batch_id IN NUMBER, ');
2920 l(' p_from_osr IN VARCHAR2, ');
2921 l(' p_to_osr IN VARCHAR2, ');
2922 l(' p_batch_mode_flag IN VARCHAR2 ');
2923 l(' ) IS ');
2924 l(' l_last_fetch BOOLEAN := FALSE; ');
2925 l(' l_contact_cur StageImpContactCurTyp; ');
2926 l(' ');
2927 l(' BEGIN ');
2928 x_bool := 'N';
2929 chk_et_req('CONTACTS', p_rule_id, x_bool);
2930 IF (x_bool = 'Y') THEN
2931 -- query for interface to tca
2932 l(' get_contact_cur(p_batch_id, p_from_osr, p_to_osr, p_batch_mode_flag, l_contact_cur ); ');
2933 l(' LOOP ');
2934 l(' FETCH l_contact_cur BULK COLLECT INTO ');
2935 get_trans_str('CONTACTS', p_rule_id, l_sel_str, 'INT_INT');
2936 l( l_sel_str);
2937 -- dbms_output.put_line('get_trans_str (CONTACTS) ' || l_sel_str);
2938 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2939 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2940 END IF;
2941 l_sel_str := '';
2942 /* H_P_SUBJECT_OS,
2943 H_P_SUBJECT_OSR,
2944 H_P_PS_OS,
2945 H_P_PS_OSR,
2946 H_P_CONTACT_OS,
2947 H_P_CONTACT_OSR,
2948 H_TX4,
2949 H_TX6,
2950 H_TX45 */
2951 l(' LIMIT g_limit; ');
2952 l(' ');
2953 l(' IF l_contact_cur%NOTFOUND THEN ');
2954 l(' l_last_fetch:=TRUE; ');
2955 l(' END IF; ');
2956 l(' IF H_P_CONTACT_OS.COUNT=0 AND l_last_fetch THEN ');
2957 l(' EXIT; ');
2958 l(' END IF; ');
2959 l(' ');
2960 l(' FOR I in H_P_CONTACT_OSR.FIRST..H_P_CONTACT_OSR.LAST LOOP ');
2961 get_trans_proc('CONTACTS', p_rule_id, l_trans_list);
2962 FOR I in l_trans_list.FIRST..l_trans_list.LAST LOOP
2963 -- dbms_output.put_line('get_trans_proc (CONTACTS)' || l_trans_list(I));
2964 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2965 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_trans_list(I));
2966 END IF;
2967 l(l_trans_list(I));
2968 l_trans_list.DELETE(I);
2969 END LOOP ;
2970 /* H_TX4(I):= HZ_TRANS_PKG.WRADDRESS_CLEANSE (H_TX4(I),NULL, 'CONTACT_NUMBER','CONTACTS');
2971 H_TX6(I):= HZ_TRANS_PKG.CLEANSED_EMAIL (H_TX6(I),NULL, 'TITLE','CONTACTS');
2972 */
2973 l(' END LOOP; ');
2974 l(' SAVEPOINT pop_contacts; ');
2975 l(' BEGIN ');
2976 l(' FORALL I in H_P_CONTACT_OSR.FIRST..H_P_CONTACT_OSR.LAST ');
2977 l(' INSERT INTO HZ_SRCH_CONTACTS ( ');
2978 get_insert_str('HZ_STAGED_CONTACTS', p_rule_id, l_sel_str, 'INT_INT');
2979 l( l_sel_str);
2980 -- dbms_output.put_line(' get_insert_str (CONTACTS)' || l_sel_str);
2981 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2982 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2983 END IF;
2984 l_sel_str := '';
2985 /* PARTY_OS,
2986 PARTY_OSR,
2987 CONTACT_OS,
2988 CONTACT_OSR
2989 , TX4
2990 , TX6 */
2991 l(' ) VALUES ( ');
2992 get_insert_val_str('HZ_STAGED_CONTACTS', p_rule_id, l_sel_str, 'INT_INT');
2993 l( l_sel_str);
2994 -- dbms_output.put_line('get_insert_val_str (CONTACTS)' || l_sel_str);
2995 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2996 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
2997 END IF;
2998 l_sel_str := '';
2999 /* H_P_SUBJECT_OS(I),
3000 H_P_SUBJECT_OSR(I),
3001 H_P_CONTACT_OS(I),
3002 H_P_CONTACT_OSR(I)
3003 , H_TX4(I)
3004 , H_TX6(I) */
3005 l(' ); ');
3006 l(' EXCEPTION ');
3007 l(' WHEN OTHERS THEN ');
3008 l(' ROLLBACK to pop_contacts; ');
3009 l(' RAISE; ');
3010 l(' END; ');
3011 l(' ');
3012 l(' IF l_last_fetch THEN ');
3013 l(' FND_CONCURRENT.AF_Commit; ');
3014 l(' EXIT; ');
3015 l(' END IF; ');
3016 l(' FND_CONCURRENT.AF_Commit; ');
3017 l(' ');
3018 l(' END LOOP; ');
3019 l(' CLOSE l_contact_cur ; ');
3020 ELSE
3021 l(' null; ');
3022 END IF;
3023 l(' END pop_contacts; ');
3024 l(' ');
3025 EXCEPTION WHEN OTHERS THEN
3026 RAISE FND_API.G_EXC_ERROR;
3027 END gen_pop_contacts;
3028
3029
3030 PROCEDURE gen_pop_contacts_int (
3031 p_rule_id IN NUMBER
3032 )
3033 IS
3034 l_sel_str VARCHAR2(4000) := NULL;
3035 x_bool VARCHAR2(1);
3036 l_procedure_name VARCHAR2(30) := '.GEN_POP_CONTACTS_INT' ;
3037 BEGIN
3038 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3039 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
3040 END IF;
3041 l(' PROCEDURE pop_contacts_int ( ');
3042 l(' p_batch_id IN NUMBER, ');
3043 l(' p_from_osr IN VARCHAR2, ');
3044 l(' p_to_osr IN VARCHAR2 ');
3045 l(' ) IS ');
3046 l(' l_last_fetch BOOLEAN := FALSE; ');
3047 l(' l_contact_cur StageImpContactCurTyp; ');
3048 l(' ');
3049 l(' BEGIN ');
3050 x_bool := 'N';
3051 chk_et_req('CONTACTS', p_rule_id, x_bool);
3052 IF (x_bool = 'Y') THEN
3053
3054 -- query for interface to tca
3055 l(' get_contact_cur_int(p_batch_id, p_from_osr, p_to_osr, l_contact_cur ); ');
3056 l(' LOOP ');
3057 l(' FETCH l_contact_cur BULK COLLECT INTO ');
3058 get_trans_str('CONTACTS', p_rule_id, l_sel_str, 'INT_TCA');
3059 l( l_sel_str);
3060 -- dbms_output.put_line('get_trans_str (CONTACTS int_tca) ' || l_sel_str);
3061 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3062 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3063 END IF;
3064 l_sel_str := '';
3065 /* H_P_SUBJECT_OS,
3066 H_P_SUBJECT_OSR,
3067 H_P_PS_OS,
3068 H_P_PS_OSR,
3069 H_P_CONTACT_OS,
3070 H_P_CONTACT_OSR,
3071 H_TX4,
3072 H_TX6,
3073 H_TX45 */
3074 l(' LIMIT g_limit; ');
3075 l(' ');
3076 l(' IF l_contact_cur%NOTFOUND THEN ');
3077 l(' l_last_fetch:=TRUE; ');
3078 l(' END IF; ');
3079 l(' IF H_P_CONTACT_OS.COUNT=0 AND l_last_fetch THEN ');
3080 l(' EXIT; ');
3081 l(' END IF; ');
3082 l(' FOR I in H_P_CONTACT_OS.FIRST..H_P_CONTACT_OS.LAST LOOP ');
3083 get_trans_proc('CONTACTS', p_rule_id, l_trans_list);
3084 FOR I in l_trans_list.FIRST..l_trans_list.LAST LOOP
3085 -- dbms_output.put_line('get_trans_proc (CONTACTS int_tca)' || l_trans_list(I));
3086 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3087 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_trans_list(I));
3088 END IF;
3089 l(l_trans_list(I));
3090 l_trans_list.DELETE(I);
3091 END LOOP ;
3092 /* H_TX4(I):= HZ_TRANS_PKG.WRADDRESS_CLEANSE (H_TX4(I),NULL, 'CONTACT_NUMBER','CONTACTS');
3093 H_TX6(I):= HZ_TRANS_PKG.CLEANSED_EMAIL (H_TX6(I),NULL, 'TITLE','CONTACTS'); */
3094 l(' END LOOP; ');
3095 l(' SAVEPOINT pop_contacts_int; ');
3096 l(' BEGIN ');
3097 l(' FORALL I in H_P_CONTACT_OS.FIRST..H_P_CONTACT_OS.LAST ');
3098 l(' INSERT INTO HZ_SRCH_CONTACTS ( ');
3099 get_insert_str('HZ_STAGED_CONTACTS', p_rule_id, l_sel_str, 'INT_TCA');
3100 l( l_sel_str);
3101 -- dbms_output.put_line(' get_insert_str (CONTACTS int_tca)' || l_sel_str);
3102 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3103 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3104 END IF;
3105 l_sel_str := '';
3106 /* PARTY_OS,
3107 PARTY_OSR,
3108 CONTACT_OS,
3109 CONTACT_OSR
3110 , TX4
3111 , TX6 */
3112 l(' ) VALUES ( ');
3113 get_insert_val_str('HZ_STAGED_CONTACTS', p_rule_id, l_sel_str, 'INT_TCA');
3114 l( l_sel_str);
3115 -- dbms_output.put_line('get_insert_val_str (CONTACTS int_tca)' || l_sel_str);
3116 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3117 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3118 END IF;
3119 l_sel_str := '';
3120 /* H_P_SUBJECT_OS(I),
3121 H_P_SUBJECT_OSR(I),
3122 H_P_CONTACT_OS(I),
3123 H_P_CONTACT_OSR(I)
3124 , H_TX4(I)
3125 , H_TX6(I) */
3126 l(' ); ');
3127 l(' EXCEPTION ');
3128 l(' WHEN OTHERS THEN ');
3129 l(' ROLLBACK to pop_contacts_int; ');
3130 l(' RAISE; ');
3131 l(' END; ');
3132 l(' ');
3133 l(' IF l_last_fetch THEN ');
3134 l(' FND_CONCURRENT.AF_Commit; ');
3135 l(' EXIT; ');
3136 l(' END IF; ');
3137 l(' FND_CONCURRENT.AF_Commit; ');
3138 l(' ');
3139 l(' END LOOP; ');
3140 l(' CLOSE l_contact_cur ; ');
3141 ELSE
3142 l(' null; ');
3143 END IF;
3144 l(' END pop_contacts_int; ');
3145 EXCEPTION WHEN OTHERS THEN
3146 RAISE FND_API.G_EXC_ERROR;
3147 END gen_pop_contacts_int;
3148
3149 PROCEDURE gen_pop_cp (
3150 p_rule_id IN NUMBER
3151 )
3152 IS
3153 l_sel_str VARCHAR2(4000) := NULL;
3154 x_bool VARCHAR2(1);
3155 l_procedure_name VARCHAR2(30) := '.GEN_POP_CP' ;
3156 BEGIN
3157 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3158 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
3159 END IF;
3160 l(' PROCEDURE pop_cp ( ');
3161 l(' p_batch_id IN NUMBER, ');
3162 l(' p_from_osr IN VARCHAR2, ');
3163 l(' p_to_osr IN VARCHAR2, ');
3164 l(' p_batch_mode_flag IN VARCHAR2 ');
3165 l(' ) IS ');
3166 l(' ');
3167 l(' l_last_fetch BOOLEAN := FALSE; ');
3168 l(' l_cp_cur HZ_PARTY_STAGE.StageCurTyp;');
3169 l(' BEGIN ');
3170 x_bool := 'N';
3171 chk_et_req('CONTACT_POINTS', p_rule_id, x_bool);
3172 IF (x_bool = 'Y') THEN
3173 -- query for interface to tca
3174 l(' open l_cp_cur for ');
3175 get_select_str('CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_INT', NULL);
3176 l( l_sel_str);
3177 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3178 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3179 END IF;
3180
3181 -- dbms_output.put_line('get_select_str (CONTACT_POINTS) ' || l_sel_str);
3182 l_sel_str := '';
3183 /* select a.email_address, a.party_orig_system_reference,
3184 a.party_orig_system, a.cp_orig_system, a.cp_orig_system_reference,
3185 a.site_orig_system, a.site_orig_system_reference, b.party_site_id,
3186 b.contact_point_id, b.party_id */
3187 l(' from HZ_IMP_CONTACTPTS_INT a, HZ_IMP_CONTACTPTS_SG b --');
3188 l(' where a.batch_id = p_batch_id ');
3189 l(' and b.action_flag = ''I'' ');
3190 l(' and b.int_row_id = a.rowid ');
3191 l(' and b.party_orig_system_reference >= p_from_osr ');
3192 l(' and b.party_orig_system_reference <= p_to_osr ');
3193 l(' and b.batch_mode_flag = p_batch_mode_flag ');
3194 l(' and interface_status is null ; ');
3195
3196 l(' LOOP ');
3197 l(' FETCH l_cp_cur BULK COLLECT INTO ');
3198 get_trans_str('CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_INT');
3199 l( l_sel_str);
3200 -- dbms_output.put_line('get_trans_str (CONTACT_POINTS) ' || l_sel_str);
3201 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3202 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3203 END IF;
3204 l_sel_str := '';
3205 /* H_TX41
3206 , H_P_PARTY_ID
3207 , H_P_PARTY_OS
3208 , H_P_PARTY_OSR
3209 , H_P_CP_OS
3210 , H_P_CP_OSR
3211 , H_P_PS_OS
3212 , H_P_PS_OSR
3213 , H_P_PARTY_SITE_ID
3214 , H_P_CONTACT_POINT_ID
3215 */
3216 l(' LIMIT g_limit; ');
3217 l(' IF l_cp_cur%NOTFOUND THEN ');
3218 l(' l_last_fetch := TRUE; ');
3219 l(' END IF; ');
3220 l(' ');
3221 l(' IF H_P_CP_OSR.COUNT = 0 AND l_last_fetch THEN ');
3222 l(' EXIT; ');
3223 l(' END IF; ');
3224 l(' ');
3225 l(' FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP ');
3226 get_trans_proc('CONTACT_POINTS', p_rule_id, l_trans_list);
3227 FOR I in l_trans_list.FIRST..l_trans_list.LAST LOOP
3228 -- dbms_output.put_line(' get_trans_proc (CONTACT_POINTS) ' || l_trans_list(I));
3229 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3230 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_trans_list(I));
3231 END IF;
3232 l(l_trans_list(I));
3233 l_trans_list.DELETE(I);
3234 END LOOP ;
3235 /*
3236 H_TX41(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX41(I),NULL, 'EMAIL','CONTACT_POINTS','STAGE');
3237 */
3238 l(' END LOOP; ');
3239 l(' SAVEPOINT POP_CP; ');
3240 l(' BEGIN ');
3241 l(' FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST ');
3242 l(' INSERT INTO HZ_SRCH_CPTS ( ');
3243 get_insert_str('HZ_STAGED_CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_INT');
3244 l( l_sel_str);
3245 -- dbms_output.put_line(' get_insert_str (CONTACT_POINTS) ' || l_sel_str);
3246 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3247 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3248 END IF;
3249 l_sel_str := '';
3250 /* PARTY_SITE_ID,
3251 PARTY_ID,
3252 PARTY_OS,
3253 PARTY_OSR,
3254 PARTY_SITE_OS,
3255 PARTY_SITE_OSR,
3256 CONTACT_POINT_ID,
3257 CONTACT_PT_OS,
3258 CONTACT_PT_OSR
3259 , TX41
3260 */
3261 l(' ) VALUES ( ');
3262 get_insert_val_str('HZ_STAGED_CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_INT');
3263 l( l_sel_str);
3264 -- dbms_output.put_line('get_insert_val_str (CONTACT_POINTS) ' || l_sel_str);
3265 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3266 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3267 END IF;
3268 l_sel_str := '';
3269 /* H_P_PARTY_SITE_ID(I),
3270 H_P_PARTY_ID(I),
3271 H_P_PARTY_OS(I),
3272 H_P_PARTY_OSR(I),
3273 H_P_PS_OS(I),
3274 H_P_PS_OSR(I),
3275 H_P_CONTACT_POINT_ID(I),
3276 H_P_CP_OS(I),
3277 H_P_CP_OSR(I)
3278 , H_TX41(I)
3279 */
3280 l(' ); ');
3281 l(' EXCEPTION ');
3282 l(' WHEN OTHERS THEN ');
3283 l(' ROLLBACK to POP_CP; ');
3284 l(' RAISE; ');
3285 l(' END; ');
3286 l(' ');
3287 l(' IF l_last_fetch THEN ');
3288 l(' FND_CONCURRENT.AF_Commit; ');
3289 l(' EXIT; ');
3290 l(' END IF; ');
3291 l(' FND_CONCURRENT.AF_Commit; ');
3292 l(' ');
3293 l(' END LOOP; ');
3294 l(' CLOSE l_cp_cur; ');
3295 ELSE
3296 l(' null;');
3297 END IF;
3298 l(' END pop_cp; ');
3299 EXCEPTION WHEN OTHERS THEN
3300 RAISE FND_API.G_EXC_ERROR;
3301 END gen_pop_cp;
3302
3303
3304 PROCEDURE gen_pop_cp_int (
3305 p_rule_id IN NUMBER
3306 )
3307 IS
3308 l_sel_str VARCHAR2(4000) := NULL;
3309 x_bool VARCHAR2(1);
3310 l_procedure_name VARCHAR2(30) := '.GEN_POP_CP_INT' ;
3311 BEGIN
3312 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3313 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
3314 END IF;
3315 l(' ');
3316 l(' PROCEDURE pop_cp_int ( ');
3317 l(' p_batch_id IN NUMBER, ');
3318 l(' p_from_osr IN VARCHAR2, ');
3319 l(' p_to_osr IN VARCHAR2 ');
3320 l(' ) IS ');
3321 l(' l_last_fetch BOOLEAN := FALSE; ');
3322 l(' l_cp_cur HZ_PARTY_STAGE.StageCurTyp; ');
3323 l(' l_os VARCHAR2(30); ');
3324 l(' BEGIN ');
3325 l(' l_os := HZ_IMP_DQM_STAGE.get_os(p_batch_id); ');
3326 x_bool := 'N';
3327 chk_et_req('CONTACT_POINTS', p_rule_id, x_bool);
3328 IF (x_bool = 'Y') THEN
3329 -- query for interface to tca
3330 l(' open l_cp_cur for ');
3331 get_select_str('CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_TCA', NULL);
3332 l_sel_str := l_sel_str || ' , a.party_id '; --bug 5393826
3333 l( l_sel_str);
3334 -- dbms_output.put_line('get_select_str (CONTACT_POINTS) ' || l_sel_str);
3335 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3336 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3337 END IF;
3338 l_sel_str := '';
3339 /* select a.email_address, a.party_orig_system_reference,
3340 a.party_orig_system, a.cp_orig_system, a.cp_orig_system_reference,
3341 a.site_orig_system, a.site_orig_system_reference
3342 */
3343 l(' from HZ_IMP_CONTACTPTS_INT a ');
3344 l(' where a.batch_id = p_batch_id ');
3345 l(' and a.party_orig_system_reference >= p_from_osr ');
3346 l(' and a.party_orig_system_reference <= p_to_osr ');
3347 l(' and a.party_orig_system = l_os; ');
3348 l(' ');
3349 l(' LOOP ');
3350 l(' FETCH l_cp_cur BULK COLLECT INTO ');
3351 get_trans_str('CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_TCA');
3352 l_sel_str := l_sel_str || ' , H_P_PARTY_ID '; -- bug 5393826
3353 l( l_sel_str);
3354 -- dbms_output.put_line('get_trans_str (CONTACT_POINTS) ' || l_sel_str);
3355 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3356 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3357 END IF;
3358 l_sel_str := '';
3359 /* H_TX41
3360 , H_P_PARTY_OS
3361 , H_P_PARTY_OSR
3362 , H_P_CP_OS
3363 , H_P_CP_OSR
3364 , H_P_PS_OS
3365 , H_P_PS_OSR */
3366 l(' LIMIT g_limit; ');
3367 l(' IF l_cp_cur%NOTFOUND THEN ');
3368 l(' l_last_fetch:=TRUE; ');
3369 l(' END IF; ');
3370 l(' ');
3371 l(' IF H_P_CP_OS.COUNT=0 AND l_last_fetch THEN ');
3372 l(' EXIT; ');
3373 l(' END IF; ');
3374 l(' ');
3375 l(' FOR I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST LOOP ');
3376 get_trans_proc('CONTACT_POINTS', p_rule_id, l_trans_list);
3377 FOR I in l_trans_list.FIRST..l_trans_list.LAST LOOP
3378 -- dbms_output.put_line(' get_trans_proc (CONTACT_POINTS) ' || l_trans_list(I));
3379 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3380 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_trans_list(I));
3381 END IF;
3382 l(l_trans_list(I));
3383 l_trans_list.DELETE(I);
3384 END LOOP ;
3385 -- H_TX41(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX41(I),NULL, 'EMAIL','CONTACT_POINTS','STAGE');
3386 l(' END LOOP; ');
3387 l(' SAVEPOINT pop_cp_int; ');
3388 l(' BEGIN ');
3389 l(' FORALL I in H_P_PARTY_OSR.FIRST..H_P_PARTY_OSR.LAST ');
3390 l(' INSERT INTO HZ_SRCH_CPTS ( ');
3391 get_insert_str('HZ_STAGED_CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_TCA');
3392 l_sel_str := l_sel_str || ' , PARTY_ID '; -- bug 5393826
3393 l( l_sel_str);
3394 -- dbms_output.put_line(' get_insert_str (CONTACT_POINTS) ' || l_sel_str);
3395 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3396 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3397 END IF;
3398 l_sel_str := '';
3399 /* PARTY_OS,
3400 PARTY_OSR,
3401 PARTY_SITE_OS,
3402 PARTY_SITE_OSR,
3403 CONTACT_PT_OS,
3404 CONTACT_PT_OSR
3405 , TX41 */
3406 l(' ) VALUES ( ');
3407 get_insert_val_str('HZ_STAGED_CONTACT_POINTS', p_rule_id, l_sel_str, 'INT_TCA');
3408 l_sel_str := l_sel_str || ' ,H_P_PARTY_ID(I) '; -- bug 5393826
3409 l( l_sel_str);
3410 -- dbms_output.put_line('get_insert_val_str (CONTACT_POINTS) ' || l_sel_str);
3411 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3412 fnd_log.string(fnd_log.LEVEL_PROCEDURE,G_PKG_NAME||l_procedure_name,l_sel_str);
3413 END IF;
3414 l_sel_str := '';
3415 /* H_P_PARTY_OS(I),
3416 H_P_PARTY_OSR(I),
3417 H_P_PS_OS(I),
3418 H_P_PS_OSR(I),
3419 H_P_CP_OS(I),
3420 H_P_CP_OSR(I)
3421 , H_TX41(I) */
3422 l(' ); ');
3423 l(' EXCEPTION ');
3424 l(' WHEN OTHERS THEN ');
3425 l(' ROLLBACK to pop_cp_int; ');
3426 l(' RAISE; ');
3427 l(' END; ');
3428 l(' ');
3429 l(' IF l_last_fetch THEN ');
3430 l(' FND_CONCURRENT.AF_Commit; ');
3431 l(' EXIT; ');
3432 l(' END IF; ');
3433 l(' FND_CONCURRENT.AF_Commit; ');
3434 l(' ');
3435 l(' END LOOP; ');
3436 l(' CLOSE l_cp_cur ; ');
3437 l(' ');
3438 ELSE
3439 l(' null; ');
3440 END IF;
3441 l(' END pop_cp_int; ');
3442 EXCEPTION WHEN OTHERS THEN
3443 RAISE FND_API.G_EXC_ERROR;
3444 END gen_pop_cp_int;
3445
3446 PROCEDURE gen_declarations (
3447 p_rule_id IN NUMBER
3448 ) IS
3449 is_using_allow_cust_attr VARCHAR2(1) := 'N';
3450 l_procedure_name VARCHAR2(30) := '.GEN_DECLARATIONS' ;
3451 BEGIN
3452 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3453 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
3454 END IF;
3455 l(' g_match_rule_id NUMBER := ' ||p_rule_id || ';');
3456 l(' TYPE StageImpContactCurTyp IS REF CURSOR;');
3457 l(' TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;');
3458 l(' TYPE CharList2000 IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;');
3459 l(' TYPE CharList1000 IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;');
3460 l(' TYPE CharList30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;');
3461 l(' TYPE CharList60 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;');
3462 l(' TYPE CharList240 IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;');
3463 l(' TYPE CharList1 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;');
3464 l(' TYPE RowIdList IS TABLE OF rowid INDEX BY BINARY_INTEGER; ');
3465 l(' H_P_ROW_ID RowIdList; ');
3466 l(' H_P_N_PARTY CharList1; ');
3467 l(' H_P_PARTY_ID NumberList;');
3468 l(' H_P_PARTY_OS CharList30;');
3469 l(' H_P_PARTY_OSR CharList240;');
3470 l(' H_P_PS_OS CharList30;');
3471 l(' H_P_PS_OSR CharList240;');
3472 l(' H_P_P_TYPE CharList30; ');
3473 l(' H_P_PARTY_SITE_ID NumberList;');
3474 l(' H_P_CONTACT_POINT_ID NumberList;');
3475 l(' H_P_CP_OS CharList30;');
3476 l(' H_P_CP_OSR CharList240;');
3477 l(' H_P_SUBJECT_OS CharList30;');
3478 l(' H_P_SUBJECT_OSR CharList240;');
3479 l(' H_P_CONTACT_OS CharList30;');
3480 l(' H_P_CONTACT_OSR CharList240;');
3481 l(' H_P_CP_TYPE CharList30; ');
3482 l(' H_TX0 CharList2000;');
3483 l(' g_limit NUMBER := 1000;');
3484 l(' H_CT_OBJ_ID NumberList; ');
3485
3486 FOR TX IN ( select STAGED_ATTRIBUTE_COLUMN
3487 from hz_trans_functions_b
3488 where attribute_id in (select attribute_id
3489 from hz_match_rule_primary
3490 where match_rule_id = p_rule_id)
3491 union
3492 select STAGED_ATTRIBUTE_COLUMN
3493 from hz_trans_functions_b
3494 where attribute_id in (select attribute_id
3495 from hz_match_rule_secondary
3496 where match_rule_id = p_rule_id)
3497 order by STAGED_ATTRIBUTE_COLUMN
3498 ) LOOP
3499 l( ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;');
3500 END LOOP;
3501
3502 FOR TX2 IN ( select attribute_id
3503 from hz_trans_attributes_vl
3504 --Fix for bug 4669257. Removing the hardcoded reference below.
3505 -- where userenv('LANG') = 'US'
3506 where attribute_id in (select attribute_id
3507 from hz_match_rule_primary
3508 where match_rule_id = p_rule_id
3509 union
3510 select attribute_id
3511 from hz_match_rule_secondary
3512 where match_rule_id = p_rule_id)
3513 and custom_attribute_procedure is null
3514 ) LOOP
3515 l( ' H_' || TX2.attribute_id || 'E CharList2000;');
3516 END LOOP;
3517
3518 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACTS','CONTACT_NAME');--using_contact_name(p_rule_id);
3519 IF (is_using_allow_cust_attr = 'Y') THEN
3520 l(' H_CT_NAME CharList2000; ');
3521 -- convert to bind variable query. ????
3522 /* l_sql_stmt := ' select count(distinct batch_id) from ' || p_table_name || ' where batch_id <> :1';
3523 execute immediate l_sql_stmt into l_count using p_batch_id;
3524 */
3525 FOR TX5 IN ( select STAGED_ATTRIBUTE_COLUMN
3526 from hz_trans_functions_b
3527 where attribute_id in (select attribute_id
3528 from hz_trans_attributes_b
3529 where attribute_name = 'CONTACT_NAME'
3530 and entity_name = 'CONTACTS')
3531 and function_id in (select function_id
3532 from hz_match_rule_primary e, hz_primary_trans d
3533 where match_rule_id = p_rule_id
3534 and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
3535 union
3536 select function_id
3537 from hz_match_rule_secondary g, hz_secondary_trans f
3538 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
3539 and match_rule_id = p_rule_id)
3540 order by STAGED_ATTRIBUTE_COLUMN ) LOOP
3541 -- l( ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;');
3542 l(' H_CT_CUST_' || TX5.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;' );
3543 END LOOP;
3544
3545 /* l(' H_CT_CUST_TX2 CharList2000; ');
3546 l(' H_CT_CUST_TX5 CharList2000; ');
3547 l(' H_CT_CUST_TX6 CharList2000; ');
3548 l(' H_CT_CUST_TX156 CharList2000; ');
3549 l(' H_CT_CUST_TX23 CharList2000; '); -- added extra
3550 l(' H_CT_CUST_TX24 CharList2000; '); -- added extra
3551 */
3552 END IF;
3553
3554 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'CONTACT_POINTS','RAW_PHONE_NUMBER');--using_raw_ph_no(p_rule_id);
3555 IF (is_using_allow_cust_attr = 'Y') THEN
3556 l(' H_P_CP_R_PH_NO CharList60; ');
3557
3558 FOR TX3 IN ( select STAGED_ATTRIBUTE_COLUMN
3559 from hz_trans_functions_b
3560 where attribute_id in (select attribute_id
3561 from hz_trans_attributes_b
3562 where attribute_name = 'RAW_PHONE_NUMBER'
3563 and entity_name = 'CONTACT_POINTS')
3564 and function_id in (select function_id
3565 from hz_match_rule_primary e, hz_primary_trans d
3566 where match_rule_id = p_rule_id
3567 and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
3568 union
3569 select function_id
3570 from hz_match_rule_secondary g, hz_secondary_trans f
3571 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
3572 and match_rule_id = p_rule_id)
3573 order by STAGED_ATTRIBUTE_COLUMN ) LOOP
3574 -- l( ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;');
3575 l(' H_CP_CUST_' || TX3.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;' );
3576 END LOOP;
3577 /* l(' H_CP_CUST_TX10 CharList2000; ');
3578 l(' H_CP_CUST_TX158 CharList2000; ');
3579 */
3580 END IF;
3581 is_using_allow_cust_attr := using_allow_cust(p_rule_id, 'PARTY_SITES','ADDRESS');--using_address(p_rule_id);
3582 IF (is_using_allow_cust_attr = 'Y') THEN
3583 l(' H_P_PS_ADD CharList1000; ');
3584 FOR TX4 IN ( select STAGED_ATTRIBUTE_COLUMN
3585 from hz_trans_functions_b
3586 where attribute_id in (select attribute_id
3587 from hz_trans_attributes_b
3588 where attribute_name = 'ADDRESS'
3589 and entity_name = 'PARTY_SITES')
3590 and function_id in (select function_id
3591 from hz_match_rule_primary e, hz_primary_trans d
3592 where match_rule_id = p_rule_id
3593 and e.PRIMARY_ATTRIBUTE_ID = d.PRIMARY_ATTRIBUTE_ID
3594 union
3595 select function_id
3596 from hz_match_rule_secondary g, hz_secondary_trans f
3597 where f.SECONDARY_ATTRIBUTE_ID = g.SECONDARY_ATTRIBUTE_ID
3598 and match_rule_id = p_rule_id)
3599 order by STAGED_ATTRIBUTE_COLUMN ) LOOP
3600 -- l( ' H_' || TX.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;');
3601 l(' H_PS_CUST_' || TX4.STAGED_ATTRIBUTE_COLUMN || ' CharList2000;' );
3602 END LOOP;
3603 /* l(' H_PS_CUST_TX3 CharList240; ');
3604 l(' H_PS_CUST_TX4 CharList240; ');
3605 l(' H_PS_CUST_TX26 CharList240; ');
3606 l(' H_PS_CUST_TX27 CharList240; ');
3607 l(' H_P_PS_CUST_TX3 CharList240; ');
3608 l(' H_P_PS_CUST_TX4 CharList240; ');
3609 l(' H_P_PS_CUST_TX26 CharList240; ');
3610 l(' H_P_PS_CUST_TX27 CharList240; ');
3611 */
3612 END IF;
3613 EXCEPTION WHEN OTHERS THEN
3614 RAISE FND_API.G_EXC_ERROR;
3615 END gen_declarations;
3616
3617 FUNCTION chk_is_std(p_attribute_name IN VARCHAR2
3618 ) RETURN VARCHAR2
3619 IS
3620 l_procedure_name VARCHAR2(30) := '.CHK_IS_STD' ;
3621 l_attribute_name VARCHAR2(255) := null ;
3622 l_bool VARCHAR2(1) := 'N';
3623 l_status VARCHAR2(255);
3624 l_owner VARCHAR2(255);
3625 l_temp VARCHAR2(255);
3626
3627
3628 CURSOR c1 is select 'Y'
3629 from user_synonyms syn, dba_tab_columns col
3630 where syn.synonym_name = 'HZ_IMP_ADDRESSES_INT'
3631 and col.owner = syn.table_owner
3632 and col.table_name = syn.table_name
3633 and col.column_name = l_attribute_name
3634 and col.owner = l_owner;
3635 BEGIN
3636 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3637 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
3638 END IF;
3639 l_attribute_name := p_attribute_name || '_STD';
3640 IF (fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_owner)) THEN
3641 OPEN c1;
3642 FETCH c1 INTO l_bool;
3643 IF c1%NOTFOUND THEN
3644 l_attribute_name := ' a.' || p_attribute_name;
3645 ELSE
3646 l_attribute_name := ' decode(accept_standardized_flag, ''Y'', a.' || l_attribute_name || ', a.' || p_attribute_name || ')' ;
3647 END IF;
3648 RETURN l_attribute_name;
3649 END IF;
3650 EXCEPTION WHEN OTHERS THEN
3651 l_attribute_name := ' a.' || p_attribute_name;
3652 END chk_is_std;
3653
3654 PROCEDURE chk_et_req(p_entity_name IN VARCHAR2,
3655 p_rule_id IN NUMBER,
3656 x_bool IN OUT NOCOPY VARCHAR2
3657 ) IS
3658 l_procedure_name VARCHAR2(30) := '.CHK_RT_REQ' ;
3659 l_table_name VARCHAR2(30);
3660 BEGIN
3661 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3662 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
3663 END IF;
3664 get_table_name(p_entity_name, l_table_name);
3665 BEGIN
3666 IF (p_entity_name = 'CONTACTS') THEN
3667 x_bool := using_allow_cust(p_rule_id, 'CONTACTS','CONTACT_NAME');--using_contact_name(p_rule_id);
3668 END IF;
3669 IF (x_bool <> 'Y') THEN
3670 select distinct 'Y' into x_bool
3671 from hz_trans_attributes_vl
3672 --Fix for bug 4669257. Removing the hardcoded reference below.
3673 --where userenv('LANG') = 'US'
3674 where entity_name = p_entity_name
3675 and custom_attribute_procedure is null
3676 and HZ_IMP_DQM_STAGE.EXIST_COL(attribute_name, p_entity_name) = 'Y'
3677 -- and attribute_name not in ('SIC_CODE', 'SIC_CODE_TYPE', 'TAX_NAME', 'CATEGORY_CODE', 'IDENTIFYING_ADDRESS_FLAG', 'STATUS', 'PRIMARY_FLAG', 'REFERENCE_USE_FLAG' )
3678 and attribute_id in ( select attribute_id
3679 from hz_match_rule_primary
3680 where match_rule_id = p_rule_id
3681 union
3682 select attribute_id
3683 from hz_match_rule_secondary
3684 where match_rule_id = p_rule_id);
3685 END IF;
3686 EXCEPTION
3687 WHEN OTHERS THEN
3688 x_bool := 'N';
3689 END;
3690 END chk_et_req;
3691
3692
3693 PROCEDURE gen_pkg_body (
3694 p_pkg_name IN VARCHAR2,
3695 p_rule_id IN NUMBER
3696 ) IS
3697
3698 -- Local Variables
3699 FIRST boolean;
3700 FIRST1 boolean;
3701 UPSTMT boolean;
3702 l_match_str VARCHAR2(255);
3703 l_attrib_cnt NUMBER;
3704 l_party_filter VARCHAR2(1) := null;
3705 l_ps_filter VARCHAR2(1) := null;
3706 l_contact_filter VARCHAR2(1) := null;
3707 l_cpt_filter VARCHAR2(1) := null;
3708 l_num_primary NUMBER;
3709 l_num_secondary NUMBER;
3710 l_ent VARCHAR2(30);
3711 l_max_score NUMBER;
3712 l_match_threshold NUMBER;
3713 l_purpose VARCHAR2(30);
3714 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3715 TYPE CharList IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
3716 attribList NumberList;
3717 l_party_filter_str VARCHAR2(2000);
3718 l_dyn_party_filter_str VARCHAR2(2000);
3719 l_p_select_list VARCHAR2(1000);
3720 l_p_param_list VARCHAR2(1000);
3721 l_p_into_list VARCHAR2(1000);
3722 l_ps_select_list VARCHAR2(1000);
3723 l_ps_param_list VARCHAR2(1000);
3724 l_ps_into_list VARCHAR2(1000);
3725 l_c_select_list VARCHAR2(1000);
3726 l_c_param_list VARCHAR2(1000);
3727 l_c_into_list VARCHAR2(1000);
3728 l_cpt_select_list VARCHAR2(1000);
3729 l_cpt_param_list VARCHAR2(1000);
3730 l_cpt_into_list VARCHAR2(1000);
3731 cnt NUMBER;
3732 l_party_filt_bind CharList;
3733 l_cpt_type VARCHAR2(255);
3734 l_trans VARCHAR2(4000);
3735 l_auto_merge_score NUMBER;
3736 tmp VARCHAR2(30);
3737 l_procedure_name VARCHAR2(30) := '.GEN_PKG_BODY' ;
3738 BEGIN
3739 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3740 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter');
3741 END IF;
3742 IF is_test THEN
3743 l('CREATE or REPLACE PACKAGE BODY ' || p_pkg_name || ' IS'); -- this
3744 END IF;
3745 gen_declarations(p_rule_id);
3746 -- gen_static_text(p_rule_id);
3747 gen_pop_parties(p_rule_id);
3748 gen_pop_party_sites(p_rule_id);
3749 gen_pop_cp(p_rule_id);
3750 gen_get_contact_cur(p_rule_id);
3751 gen_pop_contacts(p_rule_id);
3752 gen_pop_parties_int(p_rule_id);
3753 gen_pop_party_sites_int(p_rule_id);
3754 gen_pop_cp_int(p_rule_id);
3755 gen_get_contact_cur_int(p_rule_id);
3756 gen_pop_contacts_int(p_rule_id);
3757 IF is_test THEN
3758 l('END ' || p_pkg_name || ';'); -- this
3759 END IF;
3760 EXCEPTION WHEN OTHERS THEN
3761 RAISE FND_API.G_EXC_ERROR;
3762
3763 END gen_pkg_body;
3764
3765 FUNCTION EXIST_COL (attr_name IN VARCHAR2,
3766 entity IN VARCHAR2) RETURN VARCHAR2 IS
3767 BEGIN
3768 IF entity = 'PARTY' or entity = 'HZ_STAGED_PARTIES' THEN
3769 IF attr_name IN ('PARTY_NAME') THEN
3770 RETURN 'Y';
3771 END IF;
3772 EXECUTE IMMEDIATE 'select '||attr_name||' from HZ_IMP_PARTIES_INT where rownum=1';
3773 ELSIF entity = 'PARTY_SITES' or entity = 'HZ_STAGED_PARTY_SITES' THEN
3774 IF attr_name IN ('ADDRESS') THEN
3775 RETURN 'Y';
3776 END IF;
3777 EXECUTE IMMEDIATE 'select '||attr_name||' from HZ_IMP_ADDRESSES_INT where rownum=1';
3778 ELSIF entity = 'CONTACTS' or entity = 'HZ_STAGED_CONTACTS' THEN
3779 IF attr_name IN ('CONTACT_NAME') THEN
3780 RETURN 'Y';
3781 END IF;
3782 BEGIN
3783 EXECUTE IMMEDIATE 'select '||attr_name||' from HZ_IMP_CONTACTS_INT where rownum=1';
3784 EXCEPTION
3785 WHEN OTHERS THEN
3786 EXECUTE IMMEDIATE 'select '||attr_name||' from hz_imp_parties_int where rownum=1';
3787 END;
3788 ELSIF entity = 'CONTACT_POINTS' or entity = 'HZ_STAGED_CONTACT_POINTS' THEN
3789 IF attr_name IN ('RAW_PHONE_NUMBER') THEN
3790 RETURN 'Y';
3791 END IF;
3792 EXECUTE IMMEDIATE 'select '||attr_name||' from HZ_IMP_CONTACTPTS_INT where rownum=1';
3793 END IF;
3794 RETURN 'Y';
3795 EXCEPTION
3796 WHEN others THEN
3797 RETURN 'N';
3798 END EXIST_COL;
3799
3800 /*
3801 PROCEDURE gen_hz_dqm_imp_debug(
3802 p_rule_id IN NUMBER,
3803 x_return_status OUT NOCOPY VARCHAR2,
3804 x_msg_count OUT NOCOPY NUMBER,
3805 x_msg_data OUT NOCOPY VARCHAR2
3806 ) IS
3807
3808 CURSOR check_inactive IS
3809 SELECT 1
3810 FROM hz_match_rule_primary p, hz_primary_trans pt, hz_trans_functions_vl f
3811 WHERE p.match_rule_id = p_rule_id
3812 AND pt.PRIMARY_ATTRIBUTE_ID = p.PRIMARY_ATTRIBUTE_ID
3813 AND f.function_id = pt.function_id
3814 --Fix for bug 4669257. Removing the hardcoded reference below.
3815 --AND userenv('LANG') = 'US'
3816 AND nvl(f.ACTIVE_FLAG,'Y') = 'N'
3817 UNION
3818 SELECT 1
3819 FROM hz_match_rule_secondary s, hz_secondary_trans pt, hz_trans_functions_vl f
3820 WHERE s.match_rule_id = p_rule_id
3821 AND pt.SECONDARY_ATTRIBUTE_ID = s.SECONDARY_ATTRIBUTE_ID
3822 AND f.function_id = pt.function_id
3823 --Fix for bug 4669257. Removing the hardcoded reference below.
3824 --AND userenv('LANG') = 'US'
3825 AND nvl(f.ACTIVE_FLAG,'Y') = 'N';
3826
3827 -- Local variable declarations
3828 l_tmp VARCHAR2(255);
3829 l_rule_id NUMBER;
3830 l_batch_flag VARCHAR2(1);
3831 l_package_name VARCHAR2(2000);
3832 l_procedure_name VARCHAR2(30) := '.GEN_HZ_DQM_IMP_DEBUG' ;
3833 BEGIN
3834 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3835 fnd_log_repository.init;
3836 fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Enter, p_rule_id='||p_rule_id);
3837 END IF;
3838 --Initialize API return status to success.
3839 x_return_status := FND_API.G_RET_STS_SUCCESS;
3840 -- Initialize the compiled package name
3841 l_rule_id := TO_NUMBER(p_rule_id);
3842 l_package_name := 'HZ_IMP_MATCH_RULE_'||p_rule_id;
3843 -- Initialize message stack
3844 FND_MSG_PUB.initialize;
3845
3846
3847 BEGIN
3848 -- Verify that the match rule exists
3849 SELECT 1 INTO l_batch_flag
3850 FROM HZ_MATCH_RULES_VL
3851 WHERE match_rule_id = l_rule_id;
3852
3853 EXCEPTION
3854 WHEN NO_DATA_FOUND THEN
3855 FND_MESSAGE.SET_NAME('AR', 'HZ_STAGE_NO_RULE');
3856 FND_MSG_PUB.ADD;
3857 RAISE FND_API.G_EXC_ERROR;
3858 END;
3859 -- Check if match rule has any inactive transformations
3860 OPEN check_inactive;
3861 FETCH check_inactive INTO l_tmp;
3862 IF check_inactive%FOUND THEN
3863 CLOSE check_inactive;
3864 BEGIN
3865 EXECUTE IMMEDIATE 'DROP PACKAGE HZ_MATCH_RULE_'||l_rule_id;
3866 EXCEPTION
3867 WHEN OTHERS THEN
3868 NULL;
3869 END;
3870
3871 fnd_message.set_name('AR','HZ_MR_HAS_INACTIVE_TX');
3872 FND_MSG_PUB.ADD;
3873 RAISE FND_API.G_EXC_ERROR;
3874 END IF;
3875 CLOSE check_inactive;
3876 -- Generate and compile match rule package spec
3877 HZ_GEN_PLSQL.new(l_package_name, 'PACKAGE');
3878 gen_pkg_spec(l_package_name, l_rule_id);
3879 HZ_GEN_PLSQL.compile_code;
3880 -- Generate and compile match rule package body
3881 HZ_GEN_PLSQL.new(l_package_name, 'PACKAGE BODY');
3882 gen_pkg_body(l_package_name, l_rule_id);
3883 HZ_GEN_PLSQL.compile_code;
3884
3885 --Standard call to get message count and if count is 1, get message info.
3886 FND_MSG_PUB.Count_And_Get(
3887 p_encoded => FND_API.G_FALSE,
3888 p_count => x_msg_count,
3889 p_data => x_msg_data);
3890
3891 UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'C' WHERE MATCH_RULE_ID = l_rule_id;
3892 COMMIT;
3893 EXCEPTION
3894 WHEN FND_API.G_EXC_ERROR THEN
3895 FND_MSG_PUB.Count_And_Get(
3896 p_encoded => FND_API.G_FALSE,
3897 p_count => x_msg_count,
3898 p_data => x_msg_data);
3899 x_return_status := FND_API.G_RET_STS_ERROR;
3900 UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_rule_id;
3901 COMMIT;
3902 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3903 FND_MSG_PUB.Count_And_Get(
3904 p_encoded => FND_API.G_FALSE,
3905 p_count => x_msg_count,
3906 p_data => x_msg_data);
3907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3908 UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_rule_id;
3909 COMMIT;
3910 WHEN OTHERS THEN
3911
3912 FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
3913 FND_MESSAGE.SET_TOKEN('PROC','compile_match_rule');
3914 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3915 FND_MSG_PUB.ADD;
3916
3917 FND_MSG_PUB.Count_And_Get(
3918 p_encoded => FND_API.G_FALSE,
3919 p_count => x_msg_count,
3920 p_data => x_msg_data);
3921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3922 UPDATE HZ_MATCH_RULES_B SET COMPILATION_FLAG = 'U' WHERE MATCH_RULE_ID = l_rule_id;
3923 COMMIT;
3924
3925 END gen_hz_dqm_imp_debug;
3926 */
3927 END HZ_IMP_DQM_STAGE;
3928