[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_POST_PROCESS_PKG
Source
1 PACKAGE BODY HZ_IMP_LOAD_POST_PROCESS_PKG AS
2 /*$Header: ARHLPPLB.pls 120.32 2006/10/12 17:35:21 achung noship $ */
3
4 TYPE INDEXIDList IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
5 TYPE RefCurType IS REF CURSOR;
6
7 l_party_id PARTY_ID;
8 l_party_site_id PARTY_SITE_ID;
9 l_party_type PARTY_TYPE;
10 l_insert_update_flag INSERT_UPDATE_FLAG;
11 l_location_id LOCATION_ID;
12 l_org_contact_id ORG_CONTACT_ID;
13 l_relationship_code RELATIONSHIP_CODE;
14 l_relationship_id RELATIONSHIP_ID;
15 l_contact_point_id CONTACT_POINT_ID;
16 l_person_title PERSON_TITLE;
17 l_person_first_name PERSON_FIRST_NAME;
18 l_person_middle_name PERSON_MIDDLE_NAME;
19 l_person_last_name PERSON_LAST_NAME;
20 l_person_name_suffix PERSON_NAME_SUFFIX;
21 l_known_as KNOWN_AS;
22 l_person_first_name_phonetic PERSON_FIRST_NAME_PHONETIC;
23 l_middle_name_phonetic MIDDLE_NAME_PHONETIC;
24 l_person_last_name_phonetic PERSON_LAST_NAME_PHONETIC;
25 l_party_name PARTY_NAME;
26 l_address1 ADDRESS1;
27 l_address2 ADDRESS2;
28 l_address3 ADDRESS3;
29 l_address4 ADDRESS4;
30 l_postal_code POSTAL_CODE;
31 l_city CITY;
32 l_state STATE;
33 l_country COUNTRY;
34 l_rel_party_id PARTY_ID;
35 l_subject_name PARTY_NAME;
36 l_object_name PARTY_NAME;
37 l_rel_party_number PARTY_NUMBER;
38 l_raw_phone_number RAW_PHONE_NUMBER;
39 l_country_code COUNTRY_CODE;
40 l_phone_area_code PHONE_AREA_CODE;
41 l_phone_number PHONE_NUMBER;
42 l_owner_table_name OWNER_TABLE_NAME;
43 l_owner_table_id OWNER_TABLE_ID;
44 l_primary_flag PRIMARY_FLAG;
45 l_primary_by_purpose PRIMARY_BY_PURPOSE;
46 l_phone_line_type PHONE_LINE_TYPE;
47 l_phone_extension PHONE_EXTENSION;
48 l_title TITLE;
49 l_subject_id SUBJECT_ID;
50 l_object_id OBJECT_ID;
51 l_comp_flag COMP_FLAG;
52 l_ref_flag REF_FLAG;
53 l_par_flag PAR_FLAG;
54 l_created_by_module CREATED_BY_MODULE;
55 l_site_orig_system_reference SITE_ORIG_SYSTEM_REFERENCE;
56 l_return_status VARCHAR2(1);
57 l_key VARCHAR2(2000);
58 l_msg_count NUMBER;
59 l_formatted_phone_number VARCHAR2(2000);
60 l_msg_data VARCHAR2(2000);
61 l_formatted_lines_cnt NUMBER;
62 l_formatted_name_tbl HZ_FORMAT_PUB.STRING_TBL_TYPE;
63 x_return_status VARCHAR2(1);
64
65 -- Data Type for DQM Sync
66 l_record_id PARTY_ID;
67 l_entity EntityList;
68 l_operation INSERT_UPDATE_FLAG;
69 l_contact_point_type CONTACT_POINT_TYPE;
70
71 /* *
72 *=======================================================================*
73 * PROCEDURENAME *
74 * pp_denorm_rel *
75 * *
76 * DESCRIPTION *
77 * *
78 * NOTES *
79 * *
80 * MODIFICATION HISTORY *
81 * *
82 *=======================================================================*/
83
84 PROCEDURE pp_denorm_rel (
85 p_batch_mode_flag IN VARCHAR2,
86 p_batch_id IN NUMBER,
87 p_os IN VARCHAR2,
88 p_from_osr IN VARCHAR2,
89 p_to_osr IN VARCHAR2,
90 p_request_id IN NUMBER,
91 p_postprocess_status IN VARCHAR2
92 ) IS
93 CURSOR c_denorm_rel IS select sub_id,
94 decode(rs.relationship_code, 'COMPETITOR_OF','Y','N') comp_flag,
95 decode(rs.relationship_code, 'REFERENCE_FOR' ,'Y','N') ref_flag,
96 decode(rs.relationship_code, 'PARTNER_OF' ,'Y','N') par_flag
97 from hz_imp_relships_sg rs
98 where rs.batch_mode_flag = p_batch_mode_flag
99 and rs.batch_id = p_batch_id
100 and rs.action_flag = 'I'
101 and rs.sub_orig_system = p_os
102 and rs.sub_orig_system_reference between p_from_osr and p_to_osr
103 and rs.relationship_code IN ('COMPETITOR_OF','REFERENCE_FOR','PARTNER_OF');
104
105 BEGIN
106
107 fnd_file.put_line(fnd_file.log, ' pp_denorm_rel+');
108 OPEN c_denorm_rel ;
109 FETCH c_denorm_rel BULK COLLECT INTO
110 l_subject_id,l_comp_flag,l_ref_flag,l_par_flag;
111 CLOSE c_denorm_rel ;
112
113 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_denorm_rel = ' || l_subject_id.COUNT);
114
115 IF l_subject_id.COUNT = 0 THEN
116 RETURN;
117 END IF;
118
119 BEGIN
120 IF p_postprocess_status IS NULL THEN
121 FORALL i in 1..l_subject_id.count
122 UPDATE hz_parties set
123 competitor_flag = decode(l_comp_flag(i),'Y','Y',competitor_flag),
124 reference_use_flag = decode(l_ref_flag(i) ,'Y','Y',reference_use_flag),
125 third_party_flag = decode(l_par_flag(i) ,'Y','Y',third_party_flag)
126 WHERE party_id = l_subject_id(i)
127 AND request_id = p_request_id;
128 ELSIF p_postprocess_status = 'U' THEN
129 FORALL i in 1..l_subject_id.count
130 UPDATE hz_parties set
131 competitor_flag = decode(l_comp_flag(i),'Y','Y',competitor_flag),
132 reference_use_flag = decode(l_ref_flag(i) ,'Y','Y',reference_use_flag),
133 third_party_flag = decode(l_par_flag(i) ,'Y','Y',third_party_flag)
134 WHERE party_id = l_subject_id(i)
135 AND request_id IN (SELECT main_conc_req_id FROM hz_imp_batch_details
136 WHERE batch_id = p_batch_id);
137 END IF;
138 EXCEPTION
139 WHEN OTHERS THEN
140 fnd_file.put_line(fnd_file.log,' Unexpected error occured in the post processing
141 relationship denormalization program ');
142 RAISE;
143 END;
144
145 fnd_file.put_line(fnd_file.log, ' pp_denorm_rel-');
146 END pp_denorm_rel;
147
148 /* *
149 *=======================================================================*
150 * PROCEDURENAME *
151 * pp_format_person_name *
152 * *
153 * DESCRIPTION *
154 * *
155 * NOTES *
156 * *
157 * MODIFICATION HISTORY *
158 * *
159 *=======================================================================*/
160
161 PROCEDURE pp_format_person_name (
162 p_batch_mode_flag IN VARCHAR2,
163 p_batch_id IN NUMBER,
164 p_os IN VARCHAR2,
165 p_from_osr IN VARCHAR2,
166 p_to_osr IN VARCHAR2,
167 p_request_id IN NUMBER,
168 p_postprocess_status IN VARCHAR2
169 ) IS
170 l_new_sql varchar2(1300) := 'SELECT p.party_id
171 ,p.person_title
172 ,p.person_first_name
173 ,p.person_middle_name
174 ,p.person_last_name
175 ,p.person_name_suffix
176 ,p.known_as
177 ,p.person_first_name_phonetic
178 ,pf.middle_name_phonetic
179 ,p.person_last_name_phonetic
180 FROM HZ_PARTIES p, HZ_PERSON_PROFILES pf, HZ_IMP_PARTIES_SG ps
181 WHERE p.request_id = :p_request_id
182 AND p.party_type = ''PERSON''
183 AND p.party_id = pf.party_id
184 AND pf.effective_end_date is NULL
185 AND p.party_id = ps.party_id
186 AND ps.batch_id = :p_batch_id
187 AND ps.party_orig_system = :p_os
188 AND ps.party_orig_system_reference between :p_from_osr and :p_to_osr
189 AND ps.batch_mode_flag = :p_batch_mode_flag';
190
191 l_rerun_sql varchar2(1300) := 'SELECT p.party_id
192 ,p.person_title
193 ,p.person_first_name
194 ,p.person_middle_name
195 ,p.person_last_name
196 ,p.person_name_suffix
197 ,p.known_as
198 ,p.person_first_name_phonetic
199 ,pf.middle_name_phonetic
200 ,p.person_last_name_phonetic
201 FROM HZ_PARTIES p, HZ_PERSON_PROFILES pf, HZ_IMP_PARTIES_SG ps, hz_imp_batch_details bd
202 WHERE p.request_id = bd.main_conc_req_id
203 AND bd.batch_id = ps.batch_id
204 AND p.party_type = ''PERSON''
205 AND p.party_id = pf.party_id
206 AND pf.effective_end_date is NULL
207 AND p.party_id = ps.party_id
208 AND ps.batch_id = :p_batch_id
209 AND ps.party_orig_system = :p_os
210 AND ps.party_orig_system_reference between :p_from_osr and :p_to_osr
211 AND ps.batch_mode_flag = :p_batch_mode_flag';
212
213 l_person_name HZ_PARTIES.PARTY_NAME%TYPE;
214 c_person_name RefCurType;
215
216 BEGIN
217
218 fnd_file.put_line(fnd_file.log, ' pp_format_person_name+');
219 IF p_postprocess_status IS NULL THEN
220 OPEN c_person_name FOR l_new_sql
221 USING p_request_id, p_batch_id, p_os, p_from_osr, p_to_osr, p_batch_mode_flag;
222 ELSIF p_postprocess_status = 'U' THEN
223 OPEN c_person_name FOR l_rerun_sql
224 USING p_batch_id, p_os, p_from_osr, p_to_osr, p_batch_mode_flag;
225 END IF;
226
227 FETCH c_person_name BULK COLLECT INTO
228 l_party_id, l_person_title,l_person_first_name,l_person_middle_name,l_person_last_name,
229 l_person_name_suffix,l_known_as,l_person_first_name_phonetic,
230 l_middle_name_phonetic,l_person_last_name_phonetic;
231 CLOSE c_person_name;
232
233 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_format_person_name = ' || l_party_id.COUNT);
234 IF l_party_id.COUNT = 0 THEN
235 RETURN;
236 END IF;
237
238 FOR i in 1..l_party_id.count
239 LOOP
240 BEGIN
241 hz_format_pub.format_name (
242 -- input parameters
243 p_person_title => l_person_title(i),
244 p_person_first_name => l_person_first_name(i),
245 p_person_middle_name => l_person_middle_name(i),
246 p_person_last_name => l_person_last_name(i),
247 p_person_name_suffix => l_person_name_suffix(i),
248 p_person_known_as => l_known_as(i),
249 p_first_name_phonetic => l_person_first_name_phonetic(i),
250 p_middle_name_phonetic => l_middle_name_phonetic(i),
251 p_last_name_phonetic => l_person_last_name_phonetic(i),
252 -- output parameters
253 x_return_status => l_return_status,
254 x_msg_count => l_msg_count,
255 x_msg_data => l_msg_data,
256 x_formatted_name => l_person_name,
257 x_formatted_lines_cnt => l_formatted_lines_cnt,
258 x_formatted_name_tbl => l_formatted_name_tbl
259 );
260
261 IF l_return_status <> fnd_api.g_ret_sts_success THEN
262 l_person_name := NULL;
263 END IF;
264 UPDATE hz_person_profiles
265 SET person_name = l_person_name
266 WHERE party_id = l_party_id(i)
267 AND effective_end_date is NULL;
268
269 UPDATE hz_parties
270 SET party_name = substrb(l_person_name,1,360)
271 WHERE party_id = l_party_id(i);
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 fnd_file.put_line(fnd_file.log,' Unexpected error occured in the post processing person name format program
276 for Party : ' || l_party_id(i));
277 RAISE;
278 END;
279
280 END LOOP;
281 fnd_file.put_line(fnd_file.log, ' pp_format_person_name-');
282
283 END pp_format_person_name;
284
285 /* *
286 *=======================================================================*
287 * PROCEDURENAME *
288 * pp_generate_cust_key *
289 * *
290 * DESCRIPTION *
291 * *
292 * NOTES *
293 * *
294 * MODIFICATION HISTORY *
295 * *
296 *=======================================================================*/
297
298 PROCEDURE pp_generate_cust_key (
299 p_batch_mode_flag IN VARCHAR2,
300 p_batch_id IN NUMBER,
301 p_os IN VARCHAR2,
302 p_from_osr IN VARCHAR2,
303 p_to_osr IN VARCHAR2,
304 p_request_id IN NUMBER,
305 p_generate_fuzzy_key IN VARCHAR2,
306 p_enable_dqm_sync IN VARCHAR2,
307 p_postprocess_status IN VARCHAR2
308 ) IS
309 l_new_sql varchar2(1100) := 'SELECT p.party_id
310 ,p.party_type
311 ,p.party_name
312 ,p.person_first_name
313 ,p.person_last_name
314 ,null record_id
315 ,''PARTY'' entity
316 ,decode(ps.action_flag, ''I'', ''C'', ps.action_flag) operation_flag
317 FROM hz_parties p, HZ_IMP_PARTIES_SG ps
318 WHERE p.request_id = :p_request_id
319 AND p.party_type IN (''ORGANIZATION'',''PERSON'',''GROUP'')
320 AND p.party_id = ps.party_id
321 AND ps.batch_id = :p_batch_id
322 AND ps.party_orig_system = :p_os
323 AND ps.party_orig_system_reference between :p_from_osr and :p_to_osr
324 AND ps.batch_mode_flag = :p_batch_mode_flag';
325
326 l_rerun_sql varchar2(1100) := 'SELECT p.party_id
327 ,p.party_type
328 ,p.party_name
329 ,p.person_first_name
330 ,p.person_last_name
331 ,null record_id
332 ,''PARTY'' entity
333 ,decode(ps.action_flag, ''I'', ''C'', ps.action_flag) operation_flag
334 FROM hz_parties p, HZ_IMP_PARTIES_SG ps, hz_imp_batch_details bd
335 WHERE p.request_id = bd.main_conc_req_id
336 AND bd.batch_id = ps.batch_id
337 AND p.party_type IN (''ORGANIZATION'',''PERSON'',''GROUP'')
338 AND p.party_id = ps.party_id
339 AND ps.batch_id = :p_batch_id
340 AND ps.party_orig_system = :p_os
341 AND ps.party_orig_system_reference between :p_from_osr and :p_to_osr
342 AND ps.batch_mode_flag = :p_batch_mode_flag';
343
344 l_return_status VARCHAR2(1);
345 l_msg_count NUMBER;
346 l_msg_data VARCHAR2(2000);
347 c_gen_cust_key RefCurType;
348
349 BEGIN
350
351 fnd_file.put_line(fnd_file.log, ' pp_generate_cust_key+');
352
353 IF p_postprocess_status IS NULL THEN
354 fnd_file.put_line(fnd_file.log, 'p_postprocess_status IS NULL');
355
356 OPEN c_gen_cust_key FOR l_new_sql
357 USING p_request_id, p_batch_id, p_os, p_from_osr, p_to_osr, p_batch_mode_flag;
358 ELSIF p_postprocess_status = 'U' THEN
359 fnd_file.put_line(fnd_file.log, 'p_postprocess_status IS -NOT- NULL');
360 OPEN c_gen_cust_key FOR l_rerun_sql
361 USING p_batch_id, p_os, p_from_osr, p_to_osr, p_batch_mode_flag;
362 END IF;
363
364 FETCH c_gen_cust_key BULK COLLECT INTO
365 l_party_id,l_party_type,l_party_name,l_person_first_name,l_person_last_name,
366 l_record_id, l_entity, l_operation;
367 CLOSE c_gen_cust_key ;
368
369 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_generate_cust_key = ' || l_party_id.COUNT);
370 IF l_party_id.COUNT = 0 THEN
371 RETURN;
372 END IF;
373
374 -- Bug 4925023 : call HZ_DQM_SYNC once with new spec
375 /*
376 IF (p_enable_dqm_sync <> 'DISABLE') THEN
377 fnd_file.put_line(fnd_file.log, ' sync PARTY');
378 HZ_DQM_SYNC.sync_work_unit_imp(l_party_id, l_record_id, l_entity, l_operation, l_party_type,
379 l_return_status, l_msg_count, l_msg_data);
380 END IF;
381 */
382
383 IF (p_generate_fuzzy_key = 'Y') THEN
384 FOR i in 1..l_party_id.count
385 LOOP
386 BEGIN
387 l_key := HZ_FUZZY_PUB.Generate_Key (
388 p_key_type => l_party_type(i),
389 p_party_name => l_party_name(i),
390 p_address1 => NULL,
391 p_address2 => NULL,
392 p_address3 => NULL,
393 p_address4 => NULL,
394 p_postal_code => NULL,
395 p_first_name => l_person_first_name(i),
396 p_last_name => l_person_last_name(i));
397
398 UPDATE HZ_PARTIES
399 SET customer_key = l_key
400 WHERE party_id = l_party_id(i);
401 EXCEPTION
402 WHEN OTHERS THEN
403 fnd_file.put_line(fnd_file.log,' Unexpected error occured in the post processing customer key generation program
404 for Party : ' || l_party_id(i));
405 RAISE;
406 END;
407 END LOOP;
408
409 END IF;
410
411 fnd_file.put_line(fnd_file.log, ' pp_generate_cust_key-');
412
413 END pp_generate_cust_key;
414
415 /* *
416 *=======================================================================*
417 * PROCEDURENAME *
418 * pp_generate_addr_key *
419 * *
420 * DESCRIPTION *
421 * *
422 * NOTES *
423 * *
424 * MODIFICATION HISTORY *
425 * *
426 *=======================================================================*/
427
428 PROCEDURE pp_generate_addr_key (
429 p_batch_mode_flag IN VARCHAR2,
430 p_batch_id IN NUMBER,
431 p_os IN VARCHAR2,
432 p_from_osr IN VARCHAR2,
433 p_to_osr IN VARCHAR2,
434 p_request_id IN NUMBER,
435 p_generate_fuzzy_key IN VARCHAR2,
436 p_enable_dqm_sync IN VARCHAR2,
437 p_postprocess_status IN VARCHAR2
438 ) IS
439 l_new_sql varchar2(1300) := 'SELECT l.location_id
440 ,l.address1
441 ,l.address2
442 ,l.address3
443 ,l.address4
444 ,l.postal_code
445 ,null party_id
446 ,ps.party_site_id record_id
447 ,''PARTY_SITES'' entity
448 ,decode(addr_sg.action_flag, ''I'', ''C'', addr_sg.action_flag) operation_flag
449 ,null party_type
450 FROM hz_locations l, hz_party_sites ps,
451 hz_imp_addresses_sg addr_sg
452 WHERE l.request_id = :p_request_id
453 and l.location_id = ps.location_id
454 and addr_sg.batch_id = :p_batch_id
455 and addr_sg.batch_mode_flag = :p_batch_mode_flag
456 and addr_sg.party_orig_system = :p_os
457 and addr_sg.party_orig_system_reference between :p_from_osr and :p_to_osr
458 and addr_sg.party_site_id = ps.party_site_id';
459
460 l_rerun_sql varchar2(1300) := 'SELECT l.location_id
461 ,l.address1
462 ,l.address2
463 ,l.address3
464 ,l.address4
465 ,l.postal_code
466 ,null party_id
467 ,ps.party_site_id record_id
468 ,''PARTY_SITES'' entity
469 ,decode(addr_sg.action_flag, ''I'', ''C'', addr_sg.action_flag) operation_flag
470 ,null party_type
471 FROM hz_locations l, hz_party_sites ps,
472 hz_imp_addresses_sg addr_sg, hz_imp_batch_details bd
473 WHERE l.request_id = bd.main_conc_req_id
474 and bd.batch_id = addr_sg.batch_id
475 and l.location_id = ps.location_id
476 and addr_sg.batch_id = :p_batch_id
477 and addr_sg.batch_mode_flag = :p_batch_mode_flag
478 and addr_sg.party_orig_system = :p_os
479 and addr_sg.party_orig_system_reference between :p_from_osr and :p_to_osr
480 and addr_sg.party_site_id = ps.party_site_id';
481
482 l_return_status VARCHAR2(1);
483 l_msg_count NUMBER;
484 l_msg_data VARCHAR2(2000);
485 c_gen_addr_key RefCurType;
486
487 BEGIN
488
489 fnd_file.put_line(fnd_file.log, ' pp_generate_addr_key+');
490 IF p_postprocess_status IS NULL THEN
491 OPEN c_gen_addr_key FOR l_new_sql
492 USING p_request_id, p_batch_id, p_batch_mode_flag, p_os, p_from_osr, p_to_osr;
493 ELSIF p_postprocess_status = 'U' THEN
494 OPEN c_gen_addr_key FOR l_rerun_sql
495 USING p_batch_id, p_batch_mode_flag, p_os, p_from_osr, p_to_osr;
496 END IF;
497
498 FETCH c_gen_addr_key BULK COLLECT INTO
499 l_location_id,l_address1,l_address2,l_address3,l_address4,l_postal_code,
500 l_party_id, l_record_id, l_entity, l_operation, l_party_type;
501 CLOSE c_gen_addr_key ;
502
503 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_generate_addr_key = ' || l_location_id.COUNT);
504
505 IF l_location_id.COUNT = 0 THEN
506 RETURN;
507 END IF;
508
509 -- Bug 4925023 : call HZ_DQM_SYNC once with new spec
510 /*
511 IF (p_enable_dqm_sync <> 'DISABLE') THEN
512 fnd_file.put_line(fnd_file.log, ' sync PARTY_SITES');
513 HZ_DQM_SYNC.sync_work_unit_imp(l_party_id, l_record_id, l_entity, l_operation, l_party_type,
514 l_return_status, l_msg_count, l_msg_data);
515 END IF;
516 */
517
518 IF (p_generate_fuzzy_key = 'Y') THEN
519 FOR i in 1..l_location_id.count
520 LOOP
521 BEGIN
522 l_key := HZ_FUZZY_PUB.Generate_Key (
523 p_key_type => 'ADDRESS',
524 p_party_name => NULL,
525 p_address1 => l_address1(i),
526 p_address2 => l_address2(i),
527 p_address3 => l_address3(i),
528 p_address4 => l_address4(i),
529 p_postal_code => l_postal_code(i),
530 p_first_name => NULL,
531 p_last_name => NULL);
532
533 UPDATE HZ_LOCATIONS
534 SET address_key = l_key
535 WHERE location_id = l_location_id(i);
536 EXCEPTION
537 WHEN OTHERS THEN
538 fnd_file.put_line(fnd_file.log,' Unexpected error occured in the post processing address key generation program
539 for location : ' || l_location_id(i) );
540 RAISE;
541 END;
542 END LOOP;
543 END IF;
544
545 fnd_file.put_line(fnd_file.log, ' pp_generate_addr_key-');
546
547 END pp_generate_addr_key;
548
549 /* *
550 *=======================================================================*
551 * PROCEDURENAME *
552 * pp_dnb_hierarchy *
553 * *
554 * DESCRIPTION *
555 * *
556 * NOTES *
557 * *
558 * MODIFICATION HISTORY *
559 * *
560 *=======================================================================*/
561
562 PROCEDURE pp_dnb_hierarchy (
563 p_batch_mode_flag IN VARCHAR2,
564 p_batch_id IN NUMBER,
565 p_os IN VARCHAR2,
566 p_from_osr IN VARCHAR2,
567 p_to_osr IN VARCHAR2
568 ) IS
569
570 CURSOR c_party IS
571 SELECT r.object_id,r.subject_id, r.relationship_code
572 FROM hz_imp_parties_sg ps,hz_imp_relships_sg rs,hz_relationships r
573 WHERE ps.batch_id = p_batch_id
574 AND ps.batch_mode_flag = p_batch_mode_flag
575 AND ps.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
576 AND ps.party_orig_system = 'DNB'
577 AND ps.party_id = rs.obj_id
578 AND rs.batch_mode_flag = p_batch_mode_flag
579 AND rs.action_flag = 'I'
580 AND rs.batch_id = p_batch_id
581 AND r.subject_id = rs.sub_id
582 AND r.relationship_type IN ('HEADQUARTERS/DIVISION','PARENT/SUBSIDIARY',
583 'DOMESTIC_ULTIMATE','GLOBAL_ULTIMATE')
584 AND r.relationship_code IN ('PARENT_OF','HEADQUARTERS_OF',
585 'DOMESTIC_ULTIMATE_OF','GLOBAL_ULTIMATE_OF')
586 AND r.object_table_name = 'HZ_PARTIES'
587 AND r.relationship_id = rs.relationship_id
588 AND r.directional_flag = 'F'
589 ORDER BY r.object_id;
590
591 l_dup_party_id NUMBER;
592 l_gup_party_id NUMBER;
593 l_parent_party_id NUMBER;
594 l_parent_type_flg VARCHAR2(1);
595 l_prev_obj_id NUMBER;
596 p_init_msg_list VARCHAR2(1);
597 l_msg_data VARCHAR2(2000);
598 l_msg_count NUMBER;
599
600 BEGIN
601
602 fnd_file.put_line(fnd_file.log, ' pp_dnb_hierarchy+');
603 OPEN c_party;
604 FETCH c_party BULK COLLECT INTO
605 l_object_id,l_subject_id,l_relationship_code;
606 CLOSE c_party;
607
608 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_dnb_hierarchy = ' || l_object_id.COUNT);
609
610 IF l_object_id.COUNT = 0 THEN
611 RETURN;
612 END IF;
613
614 BEGIN
615 FOR j in 1..l_object_id.count LOOP
616
617 IF l_prev_obj_id IS NOT NULL AND l_prev_obj_id <> l_object_id(j) THEN
618 fnd_file.put_line(fnd_file.log, ' create dnb hierarchy for : ' || l_prev_obj_id ||
619 ' parent ' || l_parent_party_id || ' type ' || l_parent_type_flg ||
620 ' dup ' || l_dup_party_id || ' gup ' || l_gup_party_id );
621
622 HZ_DNB_HIERARCHY_PVT.conform_party_to_dnb_hierarchy( p_init_msg_list,
623 l_prev_obj_id,
624 l_parent_party_id,
625 l_dup_party_id,
626 l_gup_party_id,
627 l_parent_type_flg,
628 l_return_status,
629 l_msg_count,
630 l_msg_data
631 );
632
633 -- Bug 5264069/5437427 : re-initialize local variables for each record in bulk collection
634 l_dup_party_id := null;
635 l_gup_party_id := null;
636 l_parent_party_id := null;
637 l_parent_type_flg := null;
638 END IF;
639
640 IF l_relationship_code(j) = 'HEADQUARTERS_OF' THEN
641 l_parent_party_id := l_subject_id(j);
642 l_parent_type_flg := 'H';
643 fnd_file.put_line(fnd_file.log, ' obj ' || l_object_id(j) || ' HQ ' || l_parent_party_id);
644 ELSIF l_relationship_code(j) = 'PARENT_OF' THEN
645 l_parent_party_id := l_subject_id(j);
646 l_parent_type_flg := 'P';
647 fnd_file.put_line(fnd_file.log, ' obj ' || l_object_id(j) || ' par ' || l_parent_party_id);
648 ELSIF l_relationship_code(j) = 'DOMESTIC_ULTIMATE_OF' THEN
649 l_dup_party_id := l_subject_id(j);
650 fnd_file.put_line(fnd_file.log, ' obj ' || l_object_id(j) || ' dup ' || l_dup_party_id);
651 ELSIF l_relationship_code(j) = 'GLOBAL_ULTIMATE_OF' THEN
652 l_gup_party_id := l_subject_id(j);
653 fnd_file.put_line(fnd_file.log, ' obj ' || l_object_id(j) || ' gup ' || l_gup_party_id);
654 END IF;
655
656 l_prev_obj_id := l_object_id(j);
657
658 END LOOP;
659
660 -- Call HZ_DNB_HIERARCHY_PVT for last set of data
661 fnd_file.put_line(fnd_file.log, ' create dnb hierarchy for : ' || l_prev_obj_id ||
662 ' parent ' || l_parent_party_id || ' type ' || l_parent_type_flg ||
663 ' dup ' || l_dup_party_id || ' gup ' || l_gup_party_id );
664
665 HZ_DNB_HIERARCHY_PVT.conform_party_to_dnb_hierarchy( p_init_msg_list,
666 l_prev_obj_id,
667 l_parent_party_id,
668 l_dup_party_id,
669 l_gup_party_id,
670 l_parent_type_flg,
671 l_return_status,
672 l_msg_count,
673 l_msg_data
674 );
675 EXCEPTION
676 WHEN OTHERS THEN
677 fnd_file.put_line(fnd_file.log,
678 ' Unexpected error occured in the post processing dnb hierarchy program for party : ' || l_prev_obj_id);
679 RAISE;
680 END;
681
682 fnd_file.put_line(fnd_file.log, ' pp_dnb_hierarchy-');
683
684 END pp_dnb_hierarchy;
685
686 /* *
687 *=======================================================================*
688 * PROCEDURENAME *
689 * pp_generate_loc_assignments *
690 * *
691 * DESCRIPTION *
692 * *
693 * NOTES *
694 * *
695 * MODIFICATION HISTORY *
696 * *
697 *=======================================================================*/
698
699 PROCEDURE pp_generate_loc_assignments (
700 p_batch_mode_flag IN VARCHAR2,
701 p_batch_id IN NUMBER,
702 p_os IN VARCHAR2,
703 p_from_osr IN VARCHAR2,
704 p_to_osr IN VARCHAR2
705 ) IS
706 CURSOR c_loc IS SELECT ps.location_id,ps.created_by_module,site_sg.site_orig_system_reference
707 FROM hz_imp_addresses_sg site_sg,
708 hz_imp_addresses_int site_int,
709 hz_party_sites ps
710 WHERE site_sg.batch_id = p_batch_id
711 AND site_sg.batch_mode_flag = p_batch_mode_flag
712 AND site_sg.site_orig_system = p_os
713 AND site_sg.site_orig_system_reference between p_from_osr and p_to_osr
714 AND site_sg.action_flag = 'U'
715 AND site_sg.int_row_id = site_int.rowid
716 AND site_int.correct_move_indicator = 'C'
717 AND site_int.interface_status IS NULL /* check if any validation error */
718 AND site_sg.party_site_id = ps.party_site_id
719 AND exists (select 1 from hz_geo_name_references gnr
720 where gnr.location_id = ps.location_id
721 and gnr.location_table_name = 'HZ_LOCATIONS');
722
723 l_msg_count NUMBER;
724 l_msg_data VARCHAR2(2000);
725 l_loc_id NUMBER;
726 l_org_id VARCHAR2(2000);
727 msg VARCHAR2(2000);
728
729 BEGIN
730
731 fnd_file.put_line(fnd_file.log, ' pp_generate_loc_assignments+');
732 OPEN c_loc;
733 FETCH c_loc BULK COLLECT INTO l_location_id,l_created_by_module,l_site_orig_system_reference;
734 CLOSE c_loc;
735 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_generate_loc_assignments = ' || l_location_id.COUNT);
736
737 IF l_location_id.COUNT = 0 THEN
738 RETURN;
739 END IF;
740
741 BEGIN
742 FOR i in 1..l_location_id.count
743 LOOP
744 BEGIN
745 HZ_TAX_ASSIGNMENT_V2PUB.update_loc_assignment (
746 p_location_id => l_location_id(i),
747 p_created_by_module => l_created_by_module(i),
748 p_application_id => 222,
749 x_return_status => x_return_status,
750 x_msg_count => l_msg_count,
751 x_msg_data => l_msg_data,
752 x_loc_id => l_loc_id,
753 x_org_id => l_org_id );
754 IF l_org_id IS NOT NULL THEN
755 FND_MESSAGE.SET_NAME('AR', 'HZ_LOC_ASSIGN_ERROR');
756 FND_MESSAGE.SET_TOKEN('LOCATION_ID', l_location_id(i));
757 FND_MESSAGE.SET_TOKEN('SITE_OSR', l_site_orig_system_reference(i));
758 FND_MESSAGE.SET_TOKEN('ORG_ID',l_org_id );
759 msg := FND_MESSAGE.GET;
760 fnd_file.put_line(' ' || fnd_file.log,msg);
761 END IF;
762 EXCEPTION
763 WHEN OTHERS THEN
764 fnd_file.put_line(fnd_file.log,' Unexpected error occured in the post processing loc assignment generation program
765 for Location : ' || l_location_id(i) );
766 RAISE;
767 END;
768 END LOOP;
769 END;
770 fnd_file.put_line(fnd_file.log, ' pp_generate_loc_assignments-');
771
772 END pp_generate_loc_assignments;
773
774 PROCEDURE pp_generate_loc_timezone (
775 p_batch_mode_flag IN VARCHAR2,
776 p_batch_id IN NUMBER,
777 p_os IN VARCHAR2,
778 p_from_osr IN VARCHAR2,
779 p_to_osr IN VARCHAR2,
780 p_request_id IN NUMBER,
781 p_g_miss_char IN VARCHAR2
782 ) IS
783 l_timezone_id NUMBER;
784 l_msg_count NUMBER;
785 l_msg_data VARCHAR2(2000);
786
787 cursor c_timezone_loc is
788 select l.location_id, l.country, l.state, l.city, l.postal_code
789 from hz_locations l,hz_imp_addresses_int addr_int, hz_imp_addresses_sg addr_sg,
790 hz_party_sites ps
791 where l.location_id = ps.location_id
792 and addr_sg.batch_id = p_batch_id
793 and addr_sg.batch_mode_flag = p_batch_mode_flag
794 and addr_sg.party_orig_system = p_os
795 and addr_sg.party_orig_system_reference between p_from_osr and p_to_osr
796 and addr_sg.int_row_id = addr_int.rowid
797 and addr_sg.party_site_id = ps.party_site_id
798 AND addr_int.timezone_code IS NULL
799 AND addr_int.interface_status IS NULL
800 AND (decode(addr_int.COUNTRY,p_g_miss_char,NULL,addr_int.COUNTRY) IS NOT NULL OR
801 decode(addr_int.STATE,p_g_miss_char,NULL,addr_int.STATE) IS NOT NULL OR
802 decode(addr_int.CITY,p_g_miss_char,NULL,addr_int.CITY) IS NOT NULL OR
803 decode(addr_int.POSTAL_CODE,p_g_miss_char,NULL,addr_int.POSTAL_CODE) IS NOT NULL);
804
805 BEGIN
806
807 fnd_file.put_line(fnd_file.log, ' pp_generate_loc_timezone+');
808 OPEN c_timezone_loc;
809 FETCH c_timezone_loc BULK COLLECT INTO l_location_id,l_country,l_state,l_city,l_postal_code;
810 CLOSE c_timezone_loc;
811 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_generate_loc_timezone = ' || l_location_id.COUNT);
812
813 IF l_location_id.COUNT = 0 THEN
814 RETURN;
815 END IF;
816
817 BEGIN
818 FOR i in 1..l_location_id.count
819 LOOP
820 BEGIN
821 hz_timezone_pub.get_timezone_id(
822 p_api_version => 1.0,
823 p_init_msg_list => FND_API.G_FALSE,
824 p_postal_code => l_postal_code(i),
825 p_city => l_city(i),
826 p_state => l_state(i),
827 p_country => l_country(i),
828 x_timezone_id => l_timezone_id,
829 x_return_status => l_return_status ,
830 x_msg_count => l_msg_count ,
831 x_msg_data => l_msg_data);
832
833 IF l_return_status <> fnd_api.g_ret_sts_success THEN -- we don't raise error
834 l_timezone_id := null;
835 END IF;
836
837 UPDATE hz_locations
838 SET timezone_id = l_timezone_id
839 WHERE location_id = l_location_id(i);
840 EXCEPTION
841 WHEN OTHERS THEN
842 fnd_file.put_line(fnd_file.log,' Unexpected error occured in the post processing location timezone generation program
843 for Location : ' || l_location_id(i));
844 RAISE;
845 END;
846 END LOOP;
847 END;
848 fnd_file.put_line(fnd_file.log, ' pp_generate_loc_timezone-');
849
850 END pp_generate_loc_timezone;
851
852 PROCEDURE pp_generate_cp_timezone (
853 p_batch_mode_flag IN VARCHAR2,
854 p_batch_id IN NUMBER,
855 p_os IN VARCHAR2,
856 p_from_osr IN VARCHAR2,
857 p_to_osr IN VARCHAR2,
858 p_request_id IN NUMBER,
859 p_g_miss_char IN VARCHAR2,
860 p_postprocess_status IN VARCHAR2
861 ) IS
862 l_timezone_id NUMBER;
863 l_msg_count NUMBER;
864 l_msg_data VARCHAR2(2000);
865
866 l_new_sql varchar2(1000) := 'SELECT cp.contact_point_id, cp.phone_country_code, cp.phone_area_code
867 FROM hz_contact_points cp,hz_imp_contactpts_int cpint,hz_imp_contactpts_sg cpsg
868 WHERE cpsg.batch_id = :p_batch_id
869 AND cpsg.party_orig_system = :p_os
870 AND cpsg.party_orig_system_reference between :p_from_osr and :p_to_osr
871 AND cpsg.batch_mode_flag = :p_batch_mode_flag
872 AND cp.contact_point_id = cpsg.contact_point_id
873 AND cp.request_id = :p_request_id
874 AND cpsg.contact_point_type = ''PHONE''
875 AND cpsg.int_row_id = cpint.rowid
876 AND cpint.timezone_code is NULL
877 AND (decode(cpint.phone_country_code,:p_g_miss_char,NULL,cpint.phone_country_code) IS NOT NULL OR decode(cpint.phone_area_code,:p_g_miss_char,NULL,cpint.phone_area_code) IS NOT NULL)';
878
879 l_rerun_sql varchar2(1000) := 'SELECT cp.contact_point_id, cp.phone_country_code, cp.phone_area_code
880 FROM hz_contact_points cp,hz_imp_contactpts_int cpint,hz_imp_contactpts_sg cpsg, hz_imp_batch_details bd
881 WHERE cpsg.batch_id = :p_batch_id
882 AND cpsg.party_orig_system = :p_os
883 AND cpsg.party_orig_system_reference between :p_from_osr and :p_to_osr
884 AND cpsg.batch_mode_flag = :p_batch_mode_flag
885 AND cp.contact_point_id = cpsg.contact_point_id
886 AND cp.request_id = bd.main_conc_req_id
887 AND bd.batch_id = cpsg.batch_id
888 AND cpsg.contact_point_type = ''PHONE''
889 AND cpsg.int_row_id = cpint.rowid
890 AND cpint.timezone_code is NULL
891 AND (decode(cpint.phone_country_code,:p_g_miss_char,NULL,cpint.phone_country_code) IS NOT NULL OR decode(cpint.phone_area_code,:p_g_miss_char,NULL,cpint.phone_area_code) IS NOT NULL)';
892
893 c_timezone_cp RefCurType;
894
895 BEGIN
896
897 fnd_file.put_line(fnd_file.log, ' pp_generate_cp_timezone+');
898 IF p_postprocess_status IS NULL THEN
899 OPEN c_timezone_cp FOR l_new_sql
900 USING p_batch_id, p_os, p_from_osr, p_to_osr, p_batch_mode_flag, p_request_id, p_g_miss_char, p_g_miss_char;
901 ELSIF p_postprocess_status = 'U' THEN
902 OPEN c_timezone_cp FOR l_rerun_sql
903 USING p_batch_id, p_os, p_from_osr, p_to_osr, p_batch_mode_flag, p_g_miss_char, p_g_miss_char;
904 END IF;
905
906 FETCH c_timezone_cp BULK COLLECT INTO l_contact_point_id,l_country_code,l_phone_area_code;
907 CLOSE c_timezone_cp;
908 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_generate_cp_timezone = ' || l_contact_point_id.COUNT);
909
910 IF l_contact_point_id.COUNT = 0 THEN
911 RETURN;
912 END IF;
913
914 BEGIN
915 FOR i in 1..l_contact_point_id.count
916 LOOP
917 BEGIN
918 hz_timezone_pub.get_phone_timezone_id(
919 p_api_version => 1.0,
920 p_init_msg_list => FND_API.G_FALSE,
921 p_phone_country_code => l_country_code(i),
922 p_area_code => l_phone_area_code(i),
923 p_phone_prefix => null,
924 p_country_code => null,-- don't need to pass in this
925 x_timezone_id => l_timezone_id,
926 x_return_status => l_return_status ,
927 x_msg_count =>l_msg_count ,
928 x_msg_data => l_msg_data);
929 if l_return_status <> fnd_api.g_ret_sts_success
930 then -- we don't raise error
931 l_timezone_id := null;
932 end if;
933
934 UPDATE hz_contact_points
935 SET timezone_id = l_timezone_id
936 WHERE contact_point_id = l_contact_point_id(i);
937 EXCEPTION
938 WHEN OTHERS THEN
939 fnd_file.put_line(fnd_file.log,' Unexpected error occured in the post processing contact point timezone generation program
940 for Contact Point : ' || l_contact_point_id(i));
941 RAISE;
942 END;
943 END LOOP;
944 END;
945 fnd_file.put_line(fnd_file.log, ' pp_generate_cp_timezone-');
946
947 END pp_generate_cp_timezone ;
948
949 /* *
950 *=======================================================================*
951 * PROCEDURENAME *
952 * pp_phone_format *
953 * *
954 * DESCRIPTION *
955 * *
956 * NOTES *
957 * *
958 * MODIFICATION HISTORY *
959 * *
960 *=======================================================================*/
961
962
963 PROCEDURE pp_phone_format (
964 p_batch_mode_flag IN VARCHAR2,
965 p_batch_id IN NUMBER,
966 p_os IN VARCHAR2,
967 p_from_osr IN VARCHAR2,
968 p_to_osr IN VARCHAR2,
969 p_request_id IN NUMBER,
970 p_enable_dqm_sync IN VARCHAR2,
971 p_postprocess_status IN VARCHAR2
972 ) IS
973 l_new_sql varchar2(1500) := 'SELECT cp.contact_point_id,cp.raw_phone_number,
974 cp.phone_country_code,cp.phone_area_code,
975 cp.phone_number,cp.owner_table_name,cp.owner_table_id,
976 cp.primary_flag,cp.primary_by_purpose,cp.phone_line_type,
977 cp.phone_extension,
978 cps.contact_point_type,
979 null party_id,
980 cp.contact_point_id record_id,
981 ''CONTACT_POINTS'' entity,
982 decode(cps.action_flag, ''I'', ''C'', cps.action_flag) operation_flag,
983 null party_type
984 FROM hz_contact_points cp, hz_imp_contactpts_sg cps
985 WHERE cp.request_id = :p_request_id
986 AND cp.contact_point_id = cps.contact_point_id
987 and cps.batch_id = :p_batch_id
988 and cps.party_orig_system = :p_os
989 and cps.party_orig_system_reference between :p_from_osr and :p_to_osr
990 and cps.batch_mode_flag = :p_batch_mode_flag
991 and cps.action_flag is not null';
992 -- AND cps.contact_point_type = 'PHONE';
993
994 l_rerun_sql varchar2(1500) := 'SELECT cp.contact_point_id,cp.raw_phone_number,
995 cp.phone_country_code,cp.phone_area_code,
996 cp.phone_number,cp.owner_table_name,cp.owner_table_id,
997 cp.primary_flag,cp.primary_by_purpose,cp.phone_line_type,
998 cp.phone_extension,
999 cps.contact_point_type,
1000 null party_id,
1001 cp.contact_point_id record_id,
1002 ''CONTACT_POINTS'' entity,
1003 decode(cps.action_flag, ''I'', ''C'', cps.action_flag) operation_flag,
1004 null party_type
1005 FROM hz_contact_points cp, hz_imp_contactpts_sg cps, hz_imp_batch_details bd
1006 WHERE cp.request_id = bd.main_conc_req_id
1007 AND bd.batch_id = cps.batch_id
1008 AND cp.contact_point_id = cps.contact_point_id
1009 and cps.batch_id = :p_batch_id
1010 and cps.party_orig_system = :p_os
1011 and cps.party_orig_system_reference between :p_from_osr and :p_to_osr
1012 and cps.batch_mode_flag = :p_batch_mode_flag
1013 and cps.action_flag is not null';
1014
1015 CURSOR c_country (p_site_id IN NUMBER) IS
1016 SELECT country
1017 FROM hz_locations
1018 WHERE location_id = (SELECT location_id
1019 FROM hz_party_sites
1020 WHERE party_site_id = p_site_id);
1021
1022 l1_country_code hz_phone_country_codes.territory_code%type;
1023 c_phone_format RefCurType;
1024
1025 BEGIN
1026
1027 fnd_file.put_line(fnd_file.log, ' pp_phone_format+');
1028 IF p_postprocess_status IS NULL THEN
1029 OPEN c_phone_format FOR l_new_sql
1030 USING p_request_id, p_batch_id, p_os, p_from_osr, p_to_osr, p_batch_mode_flag;
1031 ELSIF p_postprocess_status = 'U' THEN
1032 OPEN c_phone_format FOR l_rerun_sql
1033 USING p_batch_id, p_os, p_from_osr, p_to_osr, p_batch_mode_flag;
1034 END IF;
1035
1036 FETCH c_phone_format BULK COLLECT INTO
1037 l_contact_point_id, l_raw_phone_number,l_country_code,l_phone_area_code,
1038 l_phone_number,l_owner_table_name,l_owner_table_id,l_primary_flag,
1039 l_primary_by_purpose,l_phone_line_type,l_phone_extension,
1040 l_contact_point_type,l_party_id, l_record_id, l_entity, l_operation, l_party_type;
1041 CLOSE c_phone_format;
1042
1043 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_phone_format = ' || l_contact_point_id.COUNT);
1044
1045 IF l_contact_point_id.COUNT = 0 THEN
1046 RETURN;
1047 END IF;
1048
1049 -- Bug 4925023 : call HZ_DQM_SYNC once with new spec
1050 /*
1051 IF (p_enable_dqm_sync <> 'DISABLE') THEN
1052 fnd_file.put_line(fnd_file.log, ' sync CONTACT_POINTS');
1053 HZ_DQM_SYNC.sync_work_unit_imp(l_party_id, l_record_id, l_entity, l_operation, l_party_type,
1054 l_return_status, l_msg_count, l_msg_data);
1055 END IF;
1056 */
1057
1058 FOR i in 1..l_contact_point_id.count
1059 LOOP
1060 BEGIN
1061 IF l_contact_point_type(i) = 'PHONE' THEN
1062 -- IF RAW_PHONE_NUMBER IS PASSED CALL PHONE_FORMAT API
1063 IF l_phone_number(i) IS NULL THEN
1064 IF l_country_code(i) IS NOT NULL THEN
1065 BEGIN
1066 select territory_code into l1_country_code
1067 from hz_phone_country_codes
1068 where phone_country_code = l_country_code(i)
1069 and rownum = 1;
1070 EXCEPTION
1071 WHEN NO_DATA_FOUND THEN
1072 NULL;
1073 END;
1074 ELSIF l_owner_table_name(i) = 'HZ_PARTY_SITES' AND
1075 l_country_code(i) IS NULL
1076 THEN
1077 OPEN c_country(l_owner_table_id(i));
1078 FETCH c_country INTO l1_country_code;
1079 IF c_country%NOTFOUND THEN
1080 CLOSE c_country;
1081 RAISE NO_DATA_FOUND;
1082 END IF;
1083 CLOSE c_country;
1084 ELSE
1085 l1_country_code := NULL;
1086 END IF;
1087
1088 l_phone_area_code(i) := NULL;
1089 hz_contact_point_v2pub.phone_format (
1090 p_raw_phone_number => l_raw_phone_number(i),
1091 p_territory_code => l1_country_code,
1092 x_formatted_phone_number => l_formatted_phone_number,
1093 x_phone_country_code => l_country_code(i),
1094 x_phone_area_code => l_phone_area_code(i),
1095 x_phone_number => l_phone_number(i),
1096 x_return_status => x_return_status,
1097 x_msg_count => l_msg_count,
1098 x_msg_data => l_msg_data);
1099
1100 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1101 fnd_file.put_line(fnd_file.log,' The phone format API failed for contact point id ' || l_contact_point_id(i));
1102 -- l_formatted_phone_number := NULL;
1103 END IF;
1104
1105 Update hz_contact_points SET
1106 phone_number = l_phone_number(i),
1107 phone_area_code = l_phone_area_code(i),
1108 phone_country_code = l_country_code(i),
1109 transposed_phone_number = hz_phone_number_pkg.transpose(
1110 l_country_code(i)||l_phone_area_code(i)||l_phone_number(i))
1111 Where contact_point_id = l_contact_point_id(i);
1112
1113 ELSIF l_raw_phone_number(i) IS NULL THEN /* Phone Number is passed */
1114 Update hz_contact_points SET
1115 raw_phone_number = l_phone_area_code(i) || '-' || l_phone_number(i),
1116 transposed_phone_number = hz_phone_number_pkg.transpose(
1117 l_country_code(i)||l_phone_area_code(i)||l_phone_number(i))
1118 Where contact_point_id = l_contact_point_id(i);
1119 END IF;
1120
1121 /* Denormalize the primary contact point type in hz_parties table */
1122 IF l_primary_flag(i) = 'Y' AND l_owner_table_name(i) = 'HZ_PARTIES' THEN
1123 UPDATE hz_parties set
1124 primary_phone_contact_pt_id = l_contact_point_id(i),
1125 primary_phone_purpose = l_primary_by_purpose(i),
1126 primary_phone_line_type = l_phone_line_type(i),
1127 primary_phone_country_code = l_country_code(i),
1128 primary_phone_area_code = l_phone_area_code(i),
1129 primary_phone_number = l_phone_number(i),
1130 primary_phone_extension = l_phone_extension(i)
1131 WHERE party_id = l_owner_table_id(i);
1132 END IF;
1133
1134 END IF; -- end IF l_contact_point_type(i) = 'PHONE'
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 fnd_file.put_line(fnd_file.log,' Unexpected error occured in the post processing phone format program
1138 for Contact Point : ' || l_contact_point_id(i));
1139 RAISE;
1140 END;
1141 END LOOP;
1142 fnd_file.put_line(fnd_file.log, ' pp_phone_format-');
1143
1144 END pp_phone_format;
1145
1146 -- Bug 4925023 : call HZ_DQM_SYNC once with new spec
1147 -- PROCEDURE pp_dqm_sync obsoleted
1148 /*
1149 PROCEDURE pp_dqm_sync (
1150 p_batch_mode_flag IN VARCHAR2,
1151 p_batch_id IN NUMBER,
1152 p_os IN VARCHAR2,
1153 p_from_osr IN VARCHAR2,
1154 p_to_osr IN VARCHAR2,
1155 p_request_id IN NUMBER,
1156 p_postprocess_status IN VARCHAR2
1157 ) IS
1158 l_return_status VARCHAR2(1);
1159 l_msg_count NUMBER;
1160 l_msg_data VARCHAR2(2000);
1161
1162 l_new_sql varchar2(1100) := 'SELECT null party_id,
1163 ocsg.contact_id p_record_id,
1164 ''CONTACTS'' entity,
1165 decode(ocsg.action_flag, ''I'', ''C'', ocsg.action_flag) operation_flag,
1166 null party_type
1167 FROM hz_org_contacts oc, hz_imp_contacts_sg ocsg
1168 WHERE ocsg.batch_mode_flag = :p_batch_mode_flag
1169 and ocsg.batch_id = :p_batch_id
1170 and ocsg.sub_orig_system = :p_os
1171 and ocsg.sub_orig_system_reference between :p_from_osr and :p_to_osr
1172 and ocsg.contact_id = oc.org_contact_id
1173 and oc.request_id = :p_request_id';
1174
1175 l_rerun_sql varchar2(1100) := 'SELECT null party_id,
1176 ocsg.contact_id p_record_id,
1177 ''CONTACTS'' entity,
1178 decode(ocsg.action_flag, ''I'', ''C'', ocsg.action_flag) operation_flag,
1179 null party_type
1180 FROM hz_org_contacts oc, hz_imp_contacts_sg ocsg, hz_imp_batch_details bd
1181 WHERE ocsg.batch_mode_flag = :p_batch_mode_flag
1182 and ocsg.batch_id = :p_batch_id
1183 and ocsg.sub_orig_system = :p_os
1184 and ocsg.sub_orig_system_reference between :p_from_osr and :p_to_osr
1185 and ocsg.contact_id = oc.org_contact_id
1186 and oc.request_id = bd.main_conc_req_id
1187 and bd.batch_id = ocsg.batch_id';
1188
1189 c_contacts RefCurType;
1190
1191 BEGIN
1192
1193 fnd_file.put_line(fnd_file.log, ' pp_dqm_sync+');
1194
1195 -- Only contacts imported from hz_imp_org_contacts_int are sync'ed.
1196 -- Contacts loaded through relationship loading do not contain any
1197 -- meaningful attributes (only primary key, foreign keys, OSR, and
1198 -- WHO columns). They will not affect search in any way.
1199
1200 IF p_postprocess_status IS NULL THEN
1201 OPEN c_contacts FOR l_new_sql
1202 USING p_batch_mode_flag, p_batch_id, p_os, p_from_osr, p_to_osr, p_request_id;
1203 ELSIF p_postprocess_status = 'U' THEN
1204 OPEN c_contacts FOR l_rerun_sql
1205 USING p_batch_mode_flag, p_batch_id, p_os, p_from_osr, p_to_osr;
1206 END IF;
1207
1208 FETCH c_contacts BULK COLLECT INTO
1209 l_party_id, l_record_id, l_entity, l_operation, l_party_type;
1210 CLOSE c_contacts;
1211
1212 fnd_file.put_line(fnd_file.log, ' # of records to process in pp_dqm_sync = ' || l_record_id.COUNT);
1213
1214 IF l_record_id.COUNT = 0 THEN
1215 RETURN;
1216 END IF;
1217
1218 fnd_file.put_line(fnd_file.log, ' sync CONTACTS');
1219
1220 HZ_DQM_SYNC.sync_work_unit_imp(l_party_id, l_record_id, l_entity, l_operation, l_party_type,
1221 l_return_status, l_msg_count, l_msg_data);
1222
1223 fnd_file.put_line(fnd_file.log, ' pp_dqm_sync-');
1224
1225 END pp_dqm_sync ;
1226 */
1227
1228 PROCEDURE WORKER_PROCESS (
1229 Errbuf OUT NOCOPY VARCHAR2,
1230 Retcode OUT NOCOPY VARCHAR2,
1231 P_BATCH_ID IN NUMBER,
1232 P_ACTUAL_CONTENT_SRC IN VARCHAR2,
1233 P_BATCH_MODE_FLAG IN VARCHAR2,
1234 P_REQUEST_ID IN NUMBER,
1235 P_GENERATE_FUZZY_KEY IN VARCHAR2 := 'Y'
1236 ) IS
1237
1238 START_TIME DATE := sysdate;
1239 P_OS VARCHAR2(30);
1240 P_FROM_OSR VARCHAR2(255);
1241 P_TO_OSR VARCHAR2(255);
1242 l_rerun VARCHAR2(1) := 'Y';
1243 l_g_miss_char VARCHAR2(1);
1244 l_hwm_stage NUMBER := 0;
1245 l_run_format_person_name_flag boolean;
1246 l_enable_dqm_sync_flag VARCHAR2(50);
1247
1248 l_return_status VARCHAR2(1);
1249 l_msg_count NUMBER;
1250 l_msg_data VARCHAR2(4000);
1251
1252 BEGIN
1253
1254 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Post-processing WORKER_PROCESS+');
1255
1256 /* Check profile if need to format person name */
1257 l_run_format_person_name_flag := (nvl(fnd_profile.value('HZ_FMT_BKWD_COMPATIBLE'),'Y') = 'N');
1258 l_enable_dqm_sync_flag := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
1259 l_g_miss_char := NVL(FND_PROFILE.value('HZ_IMP_G_MISS_CHAR'), '!');
1260
1261 /* Process records with current request_id */
1262 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Process records with current request_id');
1263 LOOP
1264 P_OS := NULL;
1265
1266 /* Pick up work units that have NULL postprocess_status */
1267 HZ_IMP_LOAD_WRAPPER.RETRIEVE_PP_WORK_UNIT(P_BATCH_ID, NULL, P_OS, P_FROM_OSR, P_TO_OSR);
1268
1269 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_OS = ' || P_OS);
1270 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_FROM_OSR = ' || P_FROM_OSR);
1271 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_TO_OSR = ' || P_TO_OSR);
1272
1273 IF (P_OS IS NULL) Then
1274 EXIT;
1275 END IF;
1276
1277 /* HZ_PARTIES */
1278 fnd_file.put_line(fnd_file.log,' Post Processing for HZ_PARTIES entity ');
1279
1280 if (l_run_format_person_name_flag) then
1281 fnd_file.put_line(fnd_file.log,' Format Person Name (+) ');
1282 pp_format_person_name(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID, NULL);
1283 fnd_file.put_line(fnd_file.log,' Format Person Name (-) ');
1284 end if;
1285
1286 IF (P_GENERATE_FUZZY_KEY = 'Y' OR l_enable_dqm_sync_flag <> 'DISABLE') THEN
1287 fnd_file.put_line(fnd_file.log,' Generate Customer Key (+) ');
1288 pp_generate_cust_key(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,
1289 P_GENERATE_FUZZY_KEY,l_enable_dqm_sync_flag, NULL);
1290
1291
1292 fnd_file.put_line(fnd_file.log,' Generate Customer Key (-) ');
1293 END IF;
1294
1295 /* HZ_RELATIONSHIPS */
1296 fnd_file.put_line(fnd_file.log,' Post Processing for HZ_RELATIONSHIPS entity ');
1297 fnd_file.put_line(fnd_file.log,' Relationship Denormalization (+) ');
1298 pp_denorm_rel(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID, NULL);
1299 fnd_file.put_line(fnd_file.log,' Relationship Denormalization (-) ');
1300
1301 IF P_OS = 'DNB' THEN
1302 fnd_file.put_line(fnd_file.log,' DNB Hierarchy (+) ');
1303 pp_dnb_hierarchy(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR);
1304 fnd_file.put_line(fnd_file.log,' DNB Hierarchy (-) ');
1305 END IF;
1306
1307 /* HZ_CONTACT_POINTS */
1308 fnd_file.put_line(fnd_file.log,' Post Processing for HZ_CONTACT_POINTS entity ');
1309 fnd_file.put_line(fnd_file.log,' Phone Format (+) ');
1310 pp_phone_format(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,
1311 l_enable_dqm_sync_flag, NULL);
1312 fnd_file.put_line(fnd_file.log,' Phone Format (-) ');
1313
1314 fnd_file.put_line(fnd_file.log,' Generate Contact point Timezone (+) ');
1315 pp_generate_cp_timezone(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,l_g_miss_char, NULL);
1316 fnd_file.put_line(fnd_file.log,' Generate Contact point Timezone (-) ');
1317
1318 /* HZ_LOCATIONS */
1319 fnd_file.put_line(fnd_file.log,' Post Processing for HZ_LOCATIONS entity ');
1320
1321 IF (P_GENERATE_FUZZY_KEY = 'Y' OR l_enable_dqm_sync_flag <> 'DISABLE') THEN
1322 fnd_file.put_line(fnd_file.log,' Generate Address Key (+) ');
1323 pp_generate_addr_key(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,
1324 P_GENERATE_FUZZY_KEY,l_enable_dqm_sync_flag, NULL);
1325 fnd_file.put_line(fnd_file.log,' Generate Address Key (-) ');
1326 END IF;
1327
1328 fnd_file.put_line(fnd_file.log,' Generate Loc Assignment (+) ');
1329 pp_generate_loc_assignments(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR);
1330 fnd_file.put_line(fnd_file.log,' Generate Loc Assignment (-) ');
1331
1332 fnd_file.put_line(fnd_file.log,' Generate Loctaion Timezone (+) ');
1333 pp_generate_loc_timezone(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,l_g_miss_char);
1334 fnd_file.put_line(fnd_file.log,' Generate Loctaion Timezone (-) ');
1335
1336 fnd_file.put_line(fnd_file.log,' DQM Sync (+) ');
1337 IF (l_enable_dqm_sync_flag <> 'DISABLE') THEN
1338 fnd_file.put_line(fnd_file.log,' Calling HZ_DQM_SYNC.sync_work_unit_imp.');
1339 -- Bug 4925023 : call HZ_DQM_SYNC once with new spec
1340 -- pp_dqm_sync(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID, NULL);
1341 HZ_DQM_SYNC.sync_work_unit_imp(
1342 p_batch_id => P_BATCH_ID,
1343 p_batch_mode_flag => P_BATCH_MODE_FLAG,
1344 p_from_osr => P_FROM_OSR,
1345 p_to_osr => P_TO_OSR,
1346 p_os => P_OS,
1347 x_return_status => l_return_status,
1348 x_msg_count => l_msg_count,
1349 x_msg_data => l_msg_data
1350 );
1351
1352 ELSE
1353 fnd_file.put_line(fnd_file.log,' HZ_DQM_ENABLE_REALTIME_SYNC not enabled.');
1354 END IF;
1355 fnd_file.put_line(fnd_file.log,' DQM Sync (-) ');
1356
1357 /* Update status to Complete for the work unit that just finished */
1358 UPDATE HZ_IMP_WORK_UNITS
1359 SET POSTPROCESS_STATUS = 'C'
1360 WHERE BATCH_ID = P_BATCH_ID
1361 AND FROM_ORIG_SYSTEM_REF = P_FROM_OSR;
1362
1363 COMMIT;
1364
1365 END LOOP;
1366
1367
1368 /* Process work units with current and all previous request_ids */
1369 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Process records with current and all previous request_id');
1370 LOOP
1371 P_OS := NULL;
1372
1373 /* Pick up work units that have NULL postprocess_status */
1374 HZ_IMP_LOAD_WRAPPER.RETRIEVE_PP_WORK_UNIT(P_BATCH_ID, 'U', P_OS, P_FROM_OSR, P_TO_OSR);
1375
1376 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_OS = ' || P_OS);
1377 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_FROM_OSR = ' || P_FROM_OSR);
1378 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_TO_OSR = ' || P_TO_OSR);
1379
1380 IF (P_OS IS NULL) Then
1381 EXIT;
1382 END IF;
1383
1384 /* HZ_PARTIES */
1385 fnd_file.put_line(fnd_file.log,' Post Processing for HZ_PARTIES entity ');
1386
1387 if (l_run_format_person_name_flag) then
1388 fnd_file.put_line(fnd_file.log,' Format Person Name (+) ');
1389 pp_format_person_name(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID, 'U');
1390 fnd_file.put_line(fnd_file.log,' Format Person Name (-) ');
1391 end if;
1392
1393 IF (P_GENERATE_FUZZY_KEY = 'Y' OR l_enable_dqm_sync_flag <> 'DISABLE') THEN
1394 fnd_file.put_line(fnd_file.log,' Generate Customer Key (+) ');
1395 pp_generate_cust_key(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,
1396 P_GENERATE_FUZZY_KEY,l_enable_dqm_sync_flag, 'U');
1397
1398
1399 fnd_file.put_line(fnd_file.log,' Generate Customer Key (-) ');
1400 END IF;
1401
1402 /* HZ_RELATIONSHIPS */
1403 fnd_file.put_line(fnd_file.log,' Post Processing for HZ_RELATIONSHIPS entity ');
1404 fnd_file.put_line(fnd_file.log,' Relationship Denormalization (+) ');
1405 pp_denorm_rel(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID, 'U');
1406 fnd_file.put_line(fnd_file.log,' Relationship Denormalization (-) ');
1407
1408 IF P_OS = 'DNB' THEN
1409 fnd_file.put_line(fnd_file.log,' DNB Hierarchy (+) ');
1410 pp_dnb_hierarchy(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR);
1411 fnd_file.put_line(fnd_file.log,' DNB Hierarchy (-) ');
1412 END IF;
1413
1414 /* HZ_CONTACT_POINTS */
1415 fnd_file.put_line(fnd_file.log,' Post Processing for HZ_CONTACT_POINTS entity ');
1416 fnd_file.put_line(fnd_file.log,' Phone Format (+) ');
1417 pp_phone_format(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,
1418 l_enable_dqm_sync_flag, 'U');
1419 fnd_file.put_line(fnd_file.log,' Phone Format (-) ');
1420
1421 fnd_file.put_line(fnd_file.log,' Generate Contact point Timezone (+) ');
1422 pp_generate_cp_timezone(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,l_g_miss_char, 'U');
1423 fnd_file.put_line(fnd_file.log,' Generate Contact point Timezone (-) ');
1424
1425 /* HZ_LOCATIONS */
1426 fnd_file.put_line(fnd_file.log,' Post Processing for HZ_LOCATIONS entity ');
1427
1428 IF (P_GENERATE_FUZZY_KEY = 'Y' OR l_enable_dqm_sync_flag <> 'DISABLE') THEN
1429 fnd_file.put_line(fnd_file.log,' Generate Address Key (+) ');
1430 pp_generate_addr_key(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,
1431 P_GENERATE_FUZZY_KEY,l_enable_dqm_sync_flag, 'U');
1432 fnd_file.put_line(fnd_file.log,' Generate Address Key (-) ');
1433 END IF;
1434
1435 fnd_file.put_line(fnd_file.log,' Generate Loc Assignment (+) ');
1436 pp_generate_loc_assignments(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR);
1437 fnd_file.put_line(fnd_file.log,' Generate Loc Assignment (-) ');
1438
1439 fnd_file.put_line(fnd_file.log,' Generate Loctaion Timezone (+) ');
1440 pp_generate_loc_timezone(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID,l_g_miss_char);
1441 fnd_file.put_line(fnd_file.log,' Generate Loctaion Timezone (-) ');
1442
1443 fnd_file.put_line(fnd_file.log,' DQM Sync (+) ');
1444 IF (l_enable_dqm_sync_flag <> 'DISABLE') THEN
1445 fnd_file.put_line(fnd_file.log,' Calling pp_dqm_sync.');
1446 -- Bug 4925023 : call HZ_DQM_SYNC once with new spec
1447 -- pp_dqm_sync(P_BATCH_MODE_FLAG,P_BATCH_ID,P_OS,P_FROM_OSR,P_TO_OSR,P_REQUEST_ID, 'U');
1448 HZ_DQM_SYNC.sync_work_unit_imp(
1449 p_batch_id => P_BATCH_ID,
1450 p_batch_mode_flag => P_BATCH_MODE_FLAG,
1451 p_from_osr => P_FROM_OSR,
1452 p_to_osr => P_TO_OSR,
1453 p_os => P_OS,
1454 x_return_status => l_return_status,
1455 x_msg_count => l_msg_count,
1456 x_msg_data => l_msg_data
1457 );
1458
1459 ELSE
1460 fnd_file.put_line(fnd_file.log,' HZ_DQM_ENABLE_REALTIME_SYNC not enabled.');
1461 END IF;
1462 fnd_file.put_line(fnd_file.log,' DQM Sync (-) ');
1463
1464 /* Update status to Complete for the work unit that just finished */
1465 UPDATE HZ_IMP_WORK_UNITS
1466 SET POSTPROCESS_STATUS = 'C'
1467 WHERE BATCH_ID = P_BATCH_ID
1468 AND FROM_ORIG_SYSTEM_REF = P_FROM_OSR;
1469
1470 COMMIT;
1471
1472 END LOOP;
1473
1474 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Post-processing WORKER_PROCESS-');
1475
1476 RETURN;
1477
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480 ROLLBACK;
1481 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1482 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
1483
1484 errbuf := FND_MESSAGE.get;
1485 retcode := 2;
1486
1487 UPDATE hz_imp_batch_summary
1488 SET import_status = 'ERROR'
1489 WHERE batch_id = P_BATCH_ID;
1490
1491 UPDATE hz_imp_batch_details
1492 SET import_status = 'ERROR'
1493 WHERE batch_id = P_BATCH_ID
1494 AND run_number = (SELECT max(run_number)
1495 FROM hz_imp_batch_details
1496 WHERE batch_id = P_BATCH_ID);
1497
1498 COMMIT;
1499
1500 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in Post-processing worker: ' || SQLERRM);
1501
1502 END WORKER_PROCESS;
1503
1504 END HZ_IMP_LOAD_POST_PROCESS_PKG;