[Home] [Help]
PACKAGE BODY: APPS.HZ_STAGE_MAP_TRANSFORM
Source
1 PACKAGE BODY HZ_STAGE_MAP_TRANSFORM AS
2 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 TYPE Char1List IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
4 TYPE Char2List IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
5 TYPE CharList IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
6 TYPE RowIdList IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
7
8 H_ROWID RowIdList;
9 H_P_PARTY_ID NumberList;
10 H_PS_DEN CharList;
11 H_CT_DEN CharList;
12 H_CPT_DEN CharList;
13 H_PARTY_INDEX NumberList;
14 H_PARTY_ID NumberList;
15 H_C_PARTY_ID NumberList;
16 H_PS_PARTY_ID NumberList;
17 H_CPT_PARTY_ID NumberList;
18 H_R_PARTY_ID NumberList;
19 H_STATUS Char1List;
20 H_PARTY_SITE_ID NumberList;
21 H_CPT_PARTY_SITE_ID NumberList;
22 H_ORG_CONTACT_ID NumberList;
23 H_PS_ORG_CONTACT_ID NumberList;
24 H_CPT_ORG_CONTACT_ID NumberList;
25 H_CONTACT_POINT_ID NumberList;
26 H_CONTACT_POINT_TYPE Char2List;
27 H_TX1 CharList;
28 H_TX2 CharList;
29 H_TX3 CharList;
30 H_TX4 CharList;
31 H_TX5 CharList;
32 H_TX6 CharList;
33 H_TX7 CharList;
34 H_TX8 CharList;
35 H_TX9 CharList;
36 H_TX10 CharList;
37 H_TX11 CharList;
38 H_TX12 CharList;
39 H_TX13 CharList;
40 H_TX14 CharList;
41 H_TX15 CharList;
42 H_TX16 CharList;
43 H_TX17 CharList;
44 H_TX18 CharList;
45 H_TX19 CharList;
46 H_TX20 CharList;
47 H_TX21 CharList;
48 H_TX22 CharList;
49 H_TX23 CharList;
50 H_TX24 CharList;
51 H_TX25 CharList;
52 H_TX26 CharList;
53 H_TX27 CharList;
54 H_TX28 CharList;
55 H_TX29 CharList;
56 H_TX30 CharList;
57 H_TX31 CharList;
58 H_TX32 CharList;
59 H_TX33 CharList;
60 H_TX34 CharList;
61 H_TX35 CharList;
62 H_TX36 CharList;
63 H_TX37 CharList;
64 H_TX38 CharList;
65 H_TX39 CharList;
66 H_TX40 CharList;
67 H_TX41 CharList;
68 H_TX42 CharList;
69 H_TX43 CharList;
70 H_TX44 CharList;
71 H_TX45 CharList;
72 H_TX46 CharList;
73 H_TX47 CharList;
74 H_TX48 CharList;
75 H_TX49 CharList;
76 H_TX50 CharList;
77 H_TX51 CharList;
78 H_TX52 CharList;
79 H_TX53 CharList;
80 H_TX54 CharList;
81 H_TX55 CharList;
82 H_TX56 CharList;
83 H_TX57 CharList;
84 H_TX58 CharList;
85 H_TX59 CharList;
86 H_TX60 CharList;
87 H_TX61 CharList;
88 H_TX62 CharList;
89 H_TX63 CharList;
90 H_TX64 CharList;
91 H_TX65 CharList;
92 H_TX66 CharList;
93 H_TX67 CharList;
94 H_TX68 CharList;
95 H_TX69 CharList;
96 H_TX70 CharList;
97 H_TX71 CharList;
98 H_TX72 CharList;
99 H_TX73 CharList;
100 H_TX74 CharList;
101 H_TX75 CharList;
102 H_TX76 CharList;
103 H_TX77 CharList;
104 H_TX78 CharList;
105 H_TX79 CharList;
106 H_TX80 CharList;
107 H_TX81 CharList;
108 H_TX82 CharList;
109 H_TX83 CharList;
110 H_TX84 CharList;
111 H_TX85 CharList;
112 H_TX86 CharList;
113 H_TX87 CharList;
114 H_TX88 CharList;
115 H_TX89 CharList;
116 H_TX90 CharList;
117 H_TX91 CharList;
118 H_TX92 CharList;
119 H_TX93 CharList;
120 H_TX94 CharList;
121 H_TX95 CharList;
122 H_TX96 CharList;
123 H_TX97 CharList;
124 H_TX98 CharList;
125 H_TX99 CharList;
126 H_TX100 CharList;
127 H_TX101 CharList;
128 H_TX102 CharList;
129 H_TX103 CharList;
130 H_TX104 CharList;
131 H_TX105 CharList;
132 H_TX106 CharList;
133 H_TX107 CharList;
134 H_TX108 CharList;
135 H_TX109 CharList;
136 H_TX110 CharList;
137 H_TX111 CharList;
138 H_TX112 CharList;
139 H_TX113 CharList;
140 H_TX114 CharList;
141 H_TX115 CharList;
142 H_TX116 CharList;
143 H_TX117 CharList;
144 H_TX118 CharList;
145 H_TX119 CharList;
146 H_TX120 CharList;
147 H_TX121 CharList;
148 H_TX122 CharList;
149 H_TX123 CharList;
150 H_TX124 CharList;
151 H_TX125 CharList;
152 H_TX126 CharList;
153 H_TX127 CharList;
154 H_TX128 CharList;
155 H_TX129 CharList;
156 H_TX130 CharList;
157 H_TX131 CharList;
158 H_TX132 CharList;
159 H_TX133 CharList;
160 H_TX134 CharList;
161 H_TX135 CharList;
162 H_TX136 CharList;
163 H_TX137 CharList;
164 H_TX138 CharList;
165 H_TX139 CharList;
166 H_TX140 CharList;
167 H_TX141 CharList;
168 H_TX142 CharList;
169 H_TX143 CharList;
170 H_TX144 CharList;
171 H_TX145 CharList;
172 H_TX146 CharList;
173 H_TX147 CharList;
174 H_TX148 CharList;
175 H_TX149 CharList;
176 H_TX150 CharList;
177 H_TX151 CharList;
178 H_TX152 CharList;
179 H_TX153 CharList;
180 H_TX154 CharList;
181 H_TX155 CharList;
182 H_TX156 CharList;
183 H_TX157 CharList;
184 H_TX158 CharList;
185 H_TX159 CharList;
186 H_TX160 CharList;
187 H_TX161 CharList;
188 H_TX162 CharList;
189 H_TX163 CharList;
190 H_TX164 CharList;
191 H_TX165 CharList;
192 H_TX166 CharList;
193 H_TX167 CharList;
194 H_TX168 CharList;
195 H_TX169 CharList;
196 H_TX170 CharList;
197 H_TX171 CharList;
198 H_TX172 CharList;
199 H_TX173 CharList;
200 H_TX174 CharList;
201 H_TX175 CharList;
202 H_TX176 CharList;
203 H_TX177 CharList;
204 H_TX178 CharList;
205 H_TX179 CharList;
206 H_TX180 CharList;
207 H_TX181 CharList;
208 H_TX182 CharList;
209 H_TX183 CharList;
210 H_TX184 CharList;
211 H_TX185 CharList;
212 H_TX186 CharList;
213 H_TX187 CharList;
214 H_TX188 CharList;
215 H_TX189 CharList;
216 H_TX190 CharList;
217 H_TX191 CharList;
218 H_TX192 CharList;
219 H_TX193 CharList;
220 H_TX194 CharList;
221 H_TX195 CharList;
222 H_TX196 CharList;
223 H_TX197 CharList;
224 H_TX198 CharList;
225 H_TX199 CharList;
226 H_TX200 CharList;
227 H_TX201 CharList;
228 H_TX202 CharList;
229 H_TX203 CharList;
230 H_TX204 CharList;
231 H_TX205 CharList;
232 H_TX206 CharList;
233 H_TX207 CharList;
234 H_TX208 CharList;
235 H_TX209 CharList;
236 H_TX210 CharList;
237 H_TX211 CharList;
238 H_TX212 CharList;
239 H_TX213 CharList;
240 H_TX214 CharList;
241 H_TX215 CharList;
242 H_TX216 CharList;
243 H_TX217 CharList;
244 H_TX218 CharList;
245 H_TX219 CharList;
246 H_TX220 CharList;
247 H_TX221 CharList;
248 H_TX222 CharList;
249 H_TX223 CharList;
250 H_TX224 CharList;
251 H_TX225 CharList;
252 H_TX226 CharList;
253 H_TX227 CharList;
254 H_TX228 CharList;
255 H_TX229 CharList;
256 H_TX230 CharList;
257 H_TX231 CharList;
258 H_TX232 CharList;
259 H_TX233 CharList;
260 H_TX234 CharList;
261 H_TX235 CharList;
262 H_TX236 CharList;
263 H_TX237 CharList;
264 H_TX238 CharList;
265 H_TX239 CharList;
266 H_TX240 CharList;
267 H_TX241 CharList;
268 H_TX242 CharList;
269 H_TX243 CharList;
270 H_TX244 CharList;
271 H_TX245 CharList;
272 H_TX246 CharList;
273 H_TX247 CharList;
274 H_TX248 CharList;
275 H_TX249 CharList;
276 H_TX250 CharList;
277 H_TX251 CharList;
278 H_TX252 CharList;
279 H_TX253 CharList;
280 H_TX254 CharList;
281 H_TX255 CharList;
282 FUNCTION miscp (rid IN ROWID) RETURN CLOB IS
283 BEGIN
284 RETURN NULL;
285 END;
286 FUNCTION miscps (rid IN ROWID) RETURN CLOB IS
287 BEGIN
288 RETURN NULL;
289 END;
290 FUNCTION miscct (rid IN ROWID) RETURN CLOB IS
291 BEGIN
292 RETURN NULL;
293 END;
294 FUNCTION misccpt (rid IN ROWID) RETURN CLOB IS
295 BEGIN
296 RETURN NULL;
297 END;
298 FUNCTION den_ps (party_id NUMBER) RETURN VARCHAR2 IS
299 CURSOR party_site_denorm (cp_party_id NUMBER) IS
300 SELECT distinct
301 TX9||' '||
302 TX10||' '||
303 TX11||' '||
304 TX12||' '||
305 TX13||' '||
306 TX14||' '||
307 TX15||' '||
308 TX20||' '||
309 TX21||' '||
310 TX22||' '||
311 ' '
312 FROM APPS.HZ_STAGED_PARTY_SITES
313 WHERE party_id = cp_party_id;
314 l_buffer VARCHAR2(4000);
315 l_den_ps VARCHAR2(2000);
316 BEGIN
317 OPEN party_site_denorm(party_id);
318 LOOP
319 FETCH party_site_denorm INTO l_den_ps;
320 EXIT WHEN party_site_denorm%NOTFOUND;
321 l_buffer := l_buffer||' '||l_den_ps;
322 END LOOP;
323 CLOSE party_site_denorm;
324 RETURN l_buffer;
325 EXCEPTION
326 WHEN OTHERS THEN
327 RETURN l_buffer;
328 END;
329 FUNCTION den_ct (party_id NUMBER) RETURN VARCHAR2 IS
330 CURSOR contact_denorm (cp_party_id NUMBER) IS
331 SELECT distinct
332 TX22||' '||
333 ' '
334 FROM APPS.HZ_STAGED_CONTACTS
335 WHERE party_id = cp_party_id;
336 l_buffer VARCHAR2(4000);
337 l_den_ct VARCHAR2(2000);
338 BEGIN
339 OPEN contact_denorm(party_id);
340 LOOP
341 FETCH contact_denorm INTO l_den_ct;
342 EXIT WHEN contact_denorm%NOTFOUND;
343 l_buffer := l_buffer||' '||l_den_ct;
344 END LOOP;
345 CLOSE contact_denorm;
346 RETURN l_buffer;
347 EXCEPTION
348 WHEN OTHERS THEN
349 RETURN l_buffer;
350 END;
351 FUNCTION den_cpt (party_id NUMBER) RETURN VARCHAR2 IS
352 CURSOR contact_pt_denorm (cp_party_id NUMBER) IS
353 SELECT distinct
354 TX3||' '||
355 TX4||' '||
356 ' '
357 FROM APPS.HZ_STAGED_CONTACT_POINTS
358 WHERE party_id = cp_party_id;
359 l_buffer VARCHAR2(4000);
360 l_den_cpt VARCHAR2(2000);
361 BEGIN
362 OPEN contact_pt_denorm(party_id);
363 LOOP
364 FETCH contact_pt_denorm INTO l_den_cpt;
365 EXIT WHEN contact_pt_denorm%NOTFOUND;
366 l_buffer := l_buffer||' '||l_den_cpt;
367 END LOOP;
368 CLOSE contact_pt_denorm;
369 RETURN l_buffer;
370 EXCEPTION
371 WHEN OTHERS THEN
372 RETURN l_buffer;
373 END;
374
375
376 PROCEDURE log(
377 message IN VARCHAR2,
378 newline IN BOOLEAN DEFAULT TRUE) IS
379 BEGIN
380 IF message = 'NEWLINE' THEN
381 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
382 ELSIF (newline) THEN
383 FND_FILE.put_line(fnd_file.log,message);
384 ELSE
385 FND_FILE.put(fnd_file.log,message);
386 END IF;
387 END log;
388
389
390 PROCEDURE insert_dqm_sync_error_rec (
391 p_party_id IN NUMBER,
392 p_record_id IN NUMBER,
393 p_party_site_id IN NUMBER,
394 p_org_contact_id IN NUMBER,
395 p_entity IN VARCHAR2,
396 p_operation IN VARCHAR2,
397 p_staged_flag IN VARCHAR2 DEFAULT 'E',
398 p_realtime_sync_flag IN VARCHAR2 DEFAULT 'Y',
399 p_error_data IN VARCHAR2
400 ) IS
401 BEGIN
402 INSERT INTO hz_dqm_sync_interface (
403 PARTY_ID,
404 RECORD_ID,
405 PARTY_SITE_ID,
406 ORG_CONTACT_ID,
407 ENTITY,
408 OPERATION,
409 STAGED_FLAG,
410 REALTIME_SYNC_FLAG,
411 ERROR_DATA,
412 CREATED_BY,
413 CREATION_DATE,
414 LAST_UPDATE_LOGIN,
415 LAST_UPDATE_DATE,
416 LAST_UPDATED_BY,
417 SYNC_INTERFACE_NUM
418 ) VALUES (
419 p_party_id,
420 p_record_id,
421 p_party_site_id,
422 p_org_contact_id,
423 p_entity,
424 p_operation,
425 p_staged_flag,
426 p_realtime_sync_flag,
427 p_error_data,
428 hz_utility_pub.created_by,
429 hz_utility_pub.creation_date,
430 hz_utility_pub.last_update_login,
431 hz_utility_pub.last_update_date,
432 hz_utility_pub.user_id,
433 HZ_DQM_SYNC_INTERFACE_S.nextval
434 );
435 END insert_dqm_sync_error_rec;
436
437
438 PROCEDURE open_party_cursor(
439 p_select_type IN VARCHAR2,
440 p_party_type IN VARCHAR2,
441 p_worker_number IN NUMBER,
442 p_num_workers IN NUMBER,
443 p_party_id IN NUMBER,
444 p_continue IN VARCHAR2,
445 x_party_cur IN OUT HZ_PARTY_STAGE.StageCurTyp) IS
446
447 l_party_type VARCHAR2(255);
448 BEGIN
449 IF p_select_type = 'SINGLE_PARTY' THEN
450 NULL;
451 ELSIF p_select_type = 'ALL_PARTIES' THEN
452 IF p_continue IS NULL OR p_continue<>'Y' THEN
453 IF p_party_type = 'ORGANIZATION' THEN
454 open x_party_cur FOR
455 SELECT p.PARTY_ID, p.STATUS
456 ,p.PARTY_NAME
457 ,p.PARTY_NUMBER
458 ,p.PARTY_TYPE
459 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
460 ,op.DUNS_NUMBER_C
461 ,op.TAX_NAME
462 ,op.TAX_REFERENCE
463 ,op.JGZZ_FISCAL_CODE
464 ,op.SIC_CODE
465 ,op.SIC_CODE_TYPE
466 ,p.CATEGORY_CODE
467 ,p.REFERENCE_USE_FLAG
468 ,op.CORPORATION_CLASS
469 FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
470 WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number
471 AND p.party_id = op.party_id
472 AND op.effective_end_date is NULL
473 AND p.PARTY_TYPE ='ORGANIZATION';
474 ELSIF p_party_type = 'PERSON' THEN
475 open x_party_cur FOR
476 SELECT p.PARTY_ID, p.STATUS
477 ,p.PARTY_NAME
478 ,p.PARTY_NUMBER
479 ,p.PARTY_TYPE
480 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
481 ,NULL
482 ,pe.TAX_NAME
483 ,pe.TAX_REFERENCE
484 ,pe.JGZZ_FISCAL_CODE
485 ,NULL
486 ,NULL
487 ,p.CATEGORY_CODE
488 ,p.REFERENCE_USE_FLAG
489 ,NULL
490 FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe
491 WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number
492 AND p.party_id = pe.party_id
493 AND pe.effective_end_date is NULL
494 AND p.PARTY_TYPE ='PERSON';
495 ELSE
496 open x_party_cur FOR
497 SELECT p.PARTY_ID, p.STATUS
498 ,p.PARTY_NAME
499 ,p.PARTY_NUMBER
500 ,p.PARTY_TYPE
501 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
502 ,NULL
503 ,NULL
504 ,NULL
505 ,NULL
506 ,NULL
507 ,NULL
508 ,p.CATEGORY_CODE
509 ,p.REFERENCE_USE_FLAG
510 ,NULL
511 FROM HZ_PARTIES p
512 WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number
513 AND p.party_type <> 'PERSON'
514 AND p.party_type <> 'ORGANIZATION'
515 AND p.party_type <> 'PARTY_RELATIONSHIP';
516 END IF;
517 ELSE
518 IF p_party_type = 'ORGANIZATION' THEN
519 open x_party_cur FOR
520 SELECT p.PARTY_ID, p.STATUS
521 ,p.PARTY_NAME
522 ,p.PARTY_NUMBER
523 ,p.PARTY_TYPE
524 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
525 ,op.DUNS_NUMBER_C
526 ,op.TAX_NAME
527 ,op.TAX_REFERENCE
528 ,op.JGZZ_FISCAL_CODE
529 ,op.SIC_CODE
530 ,op.SIC_CODE_TYPE
531 ,p.CATEGORY_CODE
532 ,p.REFERENCE_USE_FLAG
533 ,op.CORPORATION_CLASS
534 FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
535 WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number
536 AND NOT EXISTS (select 1 FROM HZ_STAGED_PARTIES sp
537 WHERE sp.party_id = p.party_id)
538 AND p.party_id = op.party_id
539 AND op.effective_end_date is NULL
540 AND p.PARTY_TYPE ='ORGANIZATION';
541 ELSIF p_party_type = 'PERSON' THEN
542 open x_party_cur FOR
543 SELECT p.PARTY_ID, p.STATUS
544 ,p.PARTY_NAME
545 ,p.PARTY_NUMBER
546 ,p.PARTY_TYPE
547 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
548 ,NULL
549 ,pe.TAX_NAME
550 ,pe.TAX_REFERENCE
551 ,pe.JGZZ_FISCAL_CODE
552 ,NULL
553 ,NULL
554 ,p.CATEGORY_CODE
555 ,p.REFERENCE_USE_FLAG
556 ,NULL
557 FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe
558 WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number
559 AND NOT EXISTS (select 1 FROM HZ_STAGED_PARTIES sp
560 WHERE sp.party_id = p.party_id)
561 AND p.party_id = pe.party_id
562 AND pe.effective_end_date is NULL
563 AND p.PARTY_TYPE ='PERSON';
564 ELSE
565 open x_party_cur FOR
566 SELECT p.PARTY_ID, p.STATUS
567 ,p.PARTY_NAME
568 ,p.PARTY_NUMBER
569 ,p.PARTY_TYPE
570 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
571 ,NULL
572 ,NULL
573 ,NULL
574 ,NULL
575 ,NULL
576 ,NULL
577 ,p.CATEGORY_CODE
578 ,p.REFERENCE_USE_FLAG
579 ,NULL
580 FROM HZ_PARTIES p
581 WHERE mod(p.PARTY_ID, p_num_workers) = p_worker_number
582 AND NOT EXISTS (select 1 FROM HZ_STAGED_PARTIES sp
583 WHERE sp.party_id = p.party_id)
584 AND p.party_type <> 'PERSON'
585 AND p.party_type <> 'ORGANIZATION'
586 AND p.party_type <> 'PARTY_RELATIONSHIP';
587 END IF;
588 END IF;
589 END IF;
590 END;
591
592 PROCEDURE insert_stage_parties (
593 p_continue IN VARCHAR2,
594 p_party_cur IN HZ_PARTY_STAGE.StageCurTyp) IS
595 l_limit NUMBER := 200;
596 l_contact_cur HZ_PARTY_STAGE.StageCurTyp;
597 l_cpt_cur HZ_PARTY_STAGE.StageCurTyp;
598 l_party_site_cur HZ_PARTY_STAGE.StageCurTyp;
599 l_last_fetch BOOLEAN := FALSE;
600 call_status BOOLEAN;
601 rphase varchar2(255);
602 rstatus varchar2(255);
603 dphase varchar2(255);
604 dstatus varchar2(255);
605 message varchar2(255);
606 req_id NUMBER;
607 l_st number;
608 l_en number;
609 USER_TERMINATE EXCEPTION;
610
611 BEGIN
612 req_id := FND_GLOBAL.CONC_REQUEST_ID;
613 LOOP
614 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(
615 req_id, null,null,rphase,rstatus,dphase,dstatus,message);
616 IF dstatus = 'TERMINATING' THEN
617 FND_FILE.put_line(FND_FILE.log,'Aborted by User');
618 RAISE USER_TERMINATE;
619 END IF;
620 FETCH p_party_cur BULK COLLECT INTO
621 H_P_PARTY_ID
622 , H_STATUS
623 ,H_TX2
624 ,H_TX34
625 ,H_TX36
626 ,H_TX39
627 ,H_TX41
628 ,H_TX42
629 ,H_TX44
630 ,H_TX45
631 ,H_TX46
632 ,H_TX47
633 ,H_TX48
634 ,H_TX156
635 ,H_TX157
636 LIMIT l_limit;
637
638 IF p_party_cur%NOTFOUND THEN
639 l_last_fetch:=TRUE;
640 END IF;
641 IF H_P_PARTY_ID.COUNT=0 AND l_last_fetch THEN
642 EXIT;
643 END IF;
644 FOR I in H_P_PARTY_ID.FIRST..H_P_PARTY_ID.LAST LOOP
645
646 H_TX32(I):=HZ_PARTY_ACQUIRE.get_account_info(H_P_PARTY_ID(I),'PARTY','ALL_ACCOUNT_NAMES', 'STAGE');
647 H_TX35(I):=HZ_PARTY_ACQUIRE.get_account_info(H_P_PARTY_ID(I),'PARTY','ALL_ACCOUNT_NUMBERS', 'STAGE');
648 H_TX61(I):=HZ_EMAIL_DOMAINS_V2PUB.get_email_domains(H_P_PARTY_ID(I),'PARTY','DOMAIN_NAME', 'STAGE');
649 H_TX63(I):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_P_PARTY_ID(I),'PARTY','PARTY_SOURCE_SYSTEM_REF', 'STAGE');
650 H_TX4(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX2(I),NULL, 'PARTY_NAME','PARTY','STAGE');
651 H_TX8(I):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX2(I),NULL, 'PARTY_NAME','PARTY','STAGE');
652 H_TX19(I):=HZ_TRANS_PKG.SOUNDX(H_TX2(I),NULL, 'PARTY_NAME','PARTY');
653 H_TX33(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX32(I),NULL, 'ALL_ACCOUNT_NAMES','PARTY','STAGE');
654 H_TX40(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX39(I),NULL, 'PARTY_ALL_NAMES','PARTY','STAGE');
655 H_TX43(I):=HZ_TRANS_PKG.CLEANSE(H_TX42(I),NULL, 'TAX_NAME','PARTY');
656 H_TX59(I):=HZ_TRANS_PKG.BASIC_WRNAMES(H_TX2(I),NULL, 'PARTY_NAME','PARTY','STAGE');
657 H_TX60(I):=HZ_TRANS_PKG.BASIC_CLEANSE_WRNAMES(H_TX2(I),NULL, 'PARTY_NAME','PARTY','STAGE');
658 H_TX62(I):=HZ_EMAIL_DOMAINS_V2PUB.FULL_DOMAIN(H_TX61(I),NULL, 'DOMAIN_NAME','PARTY');
659 H_TX158(I):=HZ_TRANS_PKG.SOUNDX(H_TX39(I),NULL, 'PARTY_ALL_NAMES','PARTY');
660 H_TX2(I):=HZ_TRANS_PKG.EXACT_PADDED(H_TX2(I),NULL, 'PARTY_NAME','PARTY');
661 H_TX32(I):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX32(I),NULL, 'ALL_ACCOUNT_NAMES','PARTY','STAGE');
662 H_TX34(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX34(I),NULL, 'PARTY_NUMBER','PARTY','STAGE');
663 H_TX35(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX35(I),NULL, 'ALL_ACCOUNT_NUMBERS','PARTY','STAGE');
664 H_TX36(I):=HZ_TRANS_PKG.EXACT(H_TX36(I),NULL, 'PARTY_TYPE','PARTY');
665 H_TX39(I):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX39(I),NULL, 'PARTY_ALL_NAMES','PARTY','STAGE');
666 H_TX41(I):=HZ_TRANS_PKG.EXACT(H_TX41(I),NULL, 'DUNS_NUMBER_C','PARTY');
667 H_TX42(I):=HZ_TRANS_PKG.EXACT(H_TX42(I),NULL, 'TAX_NAME','PARTY');
668 H_TX44(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX44(I),NULL, 'TAX_REFERENCE','PARTY','STAGE');
669 H_TX45(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX45(I),NULL, 'JGZZ_FISCAL_CODE','PARTY','STAGE');
670 H_TX46(I):=HZ_TRANS_PKG.EXACT(H_TX46(I),NULL, 'SIC_CODE','PARTY');
671 H_TX47(I):=HZ_TRANS_PKG.EXACT(H_TX47(I),NULL, 'SIC_CODE_TYPE','PARTY');
672 H_TX48(I):=HZ_TRANS_PKG.EXACT(H_TX48(I),NULL, 'CATEGORY_CODE','PARTY');
673 H_TX61(I):=HZ_EMAIL_DOMAINS_V2PUB.CORE_DOMAIN(H_TX61(I),NULL, 'DOMAIN_NAME','PARTY');
674 H_TX63(I):=HZ_TRANS_PKG.EXACT(H_TX63(I),NULL, 'PARTY_SOURCE_SYSTEM_REF','PARTY');
675 H_TX156(I):=HZ_TRANS_PKG.EXACT(H_TX156(I),NULL, 'REFERENCE_USE_FLAG','PARTY');
676 H_TX157(I):=HZ_TRANS_PKG.EXACT(H_TX157(I),NULL, 'CORPORATION_CLASS','PARTY');
677 H_PARTY_INDEX(I) := I;
678 H_PS_DEN(I) := ' ';
679 H_CT_DEN(I) := ' ';
680 H_CPT_DEN(I) := ' ';
681 END LOOP;
682 SAVEPOINT party_batch;
683 BEGIN
684 l_st := 1;
685 l_en := H_P_PARTY_ID.COUNT;
686 LOOP
687 BEGIN
688 FORALL I in l_st..l_en
689 INSERT INTO HZ_STAGED_PARTIES (
690 PARTY_ID
691 ,STATUS
692 , TX2
693 , TX4
694 , TX8
695 , TX19
696 , TX32
697 , TX33
698 , TX34
699 , TX35
700 , TX36
701 , TX39
702 , TX40
703 , TX41
704 , TX42
705 , TX43
706 , TX44
707 , TX45
708 , TX46
709 , TX47
710 , TX48
711 , TX59
712 , TX60
713 , TX61
714 , TX62
715 , TX63
716 , TX156
717 , TX157
718 , TX158
719 ) VALUES (
720 H_P_PARTY_ID(I)
721 ,H_STATUS(I)
722 , decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
723 , decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
724 , decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
725 , decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
726 , decode(H_TX32(I),null,H_TX32(I),H_TX32(I)||' ')
727 , decode(H_TX33(I),null,H_TX33(I),H_TX33(I)||' ')
728 , decode(H_TX34(I),null,H_TX34(I),H_TX34(I)||' ')
729 , decode(H_TX35(I),null,H_TX35(I),H_TX35(I)||' ')
730 , decode(H_TX36(I),null,H_TX36(I),H_TX36(I)||' ')
731 , decode(H_TX39(I),null,H_TX39(I),H_TX39(I)||' ')
732 , decode(H_TX40(I),null,H_TX40(I),H_TX40(I)||' ')
733 , decode(H_TX41(I),null,H_TX41(I),H_TX41(I)||' ')
734 , decode(H_TX42(I),null,H_TX42(I),H_TX42(I)||' ')
735 , decode(H_TX43(I),null,H_TX43(I),H_TX43(I)||' ')
736 , decode(H_TX44(I),null,H_TX44(I),H_TX44(I)||' ')
737 , decode(H_TX45(I),null,H_TX45(I),H_TX45(I)||' ')
738 , decode(H_TX46(I),null,H_TX46(I),H_TX46(I)||' ')
739 , decode(H_TX47(I),null,H_TX47(I),H_TX47(I)||' ')
740 , decode(H_TX48(I),null,H_TX48(I),H_TX48(I)||' ')
741 , decode(H_TX59(I),null,H_TX59(I),H_TX59(I)||' ')
742 , decode(H_TX60(I),null,H_TX60(I),H_TX60(I)||' ')
743 , decode(H_TX61(I),null,H_TX61(I),H_TX61(I)||' ')
744 , decode(H_TX62(I),null,H_TX62(I),H_TX62(I)||' ')
745 , decode(H_TX63(I),null,H_TX63(I),H_TX63(I)||' ')
746 , decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
747 , decode(H_TX157(I),null,H_TX157(I),H_TX157(I)||' ')
748 , decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
749 );
750 EXIT;
751 EXCEPTION WHEN OTHERS THEN
752 l_st:= l_st+SQL%ROWCOUNT+1;
753 END;
754 END LOOP;
755 FORALL I in H_P_PARTY_ID.FIRST..H_P_PARTY_ID.LAST
756 INSERT INTO HZ_DQM_STAGE_GT ( PARTY_ID, OWNER_ID, PARTY_INDEX) VALUES (
757 H_P_PARTY_ID(I),H_P_PARTY_ID(I),H_PARTY_INDEX(I));
758 insert_stage_contacts;
759 insert_stage_party_sites;
760 insert_stage_contact_pts;
761 FORALL I in H_P_PARTY_ID.FIRST..H_P_PARTY_ID.LAST
762 UPDATE HZ_STAGED_PARTIES SET
763 D_PS = H_PS_DEN(I),
764 D_CT = H_CT_DEN(I),
765 D_CPT = H_CPT_DEN(I)
766 WHERE PARTY_ID = H_P_PARTY_ID(I);
767 EXCEPTION
768 WHEN OTHERS THEN
769 ROLLBACK to party_batch;
770 RAISE;
771 END;
772 IF l_last_fetch THEN
773 FND_CONCURRENT.AF_Commit;
774 EXIT;
775 END IF;
776 FND_CONCURRENT.AF_Commit;
777 END LOOP;
778 END;
779
780 PROCEDURE sync_single_party (
781 p_party_id NUMBER,
782 p_party_type VARCHAR2,
783 p_operation VARCHAR2) IS
784
785 l_tryins BOOLEAN;
786 l_tryupd BOOLEAN;
787 BEGIN
788 IF p_party_type = 'ORGANIZATION' THEN
789 SELECT p.PARTY_ID, p.STATUS
790 ,p.PARTY_NAME
791 ,p.PARTY_NUMBER
792 ,p.PARTY_TYPE
793 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
794 ,op.DUNS_NUMBER_C
795 ,op.TAX_NAME
796 ,op.TAX_REFERENCE
797 ,op.JGZZ_FISCAL_CODE
798 ,op.SIC_CODE
799 ,op.SIC_CODE_TYPE
800 ,p.CATEGORY_CODE
801 ,p.REFERENCE_USE_FLAG
802 ,op.CORPORATION_CLASS
803 INTO H_P_PARTY_ID(1), H_STATUS(1)
804 , H_TX2(1)
805 , H_TX34(1)
806 , H_TX36(1)
807 , H_TX39(1)
808 , H_TX41(1)
809 , H_TX42(1)
810 , H_TX44(1)
811 , H_TX45(1)
812 , H_TX46(1)
813 , H_TX47(1)
814 , H_TX48(1)
815 , H_TX156(1)
816 , H_TX157(1)
817 FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
818 WHERE p.party_id = p_party_id
819 AND p.party_id = op.party_id
820 AND (p.status = 'M' or op.effective_end_date is NULL) AND ROWNUM=1;
821 ELSIF p_party_type = 'PERSON' THEN
822 SELECT p.PARTY_ID, p.STATUS
823 ,p.PARTY_NAME
824 ,p.PARTY_NUMBER
825 ,p.PARTY_TYPE
826 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
827 ,NULL
828 ,pe.TAX_NAME
829 ,pe.TAX_REFERENCE
830 ,pe.JGZZ_FISCAL_CODE
831 ,NULL
832 ,NULL
833 ,p.CATEGORY_CODE
834 ,p.REFERENCE_USE_FLAG
835 ,NULL
836 INTO H_P_PARTY_ID(1), H_STATUS(1)
837 , H_TX2(1)
838 , H_TX34(1)
839 , H_TX36(1)
840 , H_TX39(1)
841 , H_TX41(1)
842 , H_TX42(1)
843 , H_TX44(1)
844 , H_TX45(1)
845 , H_TX46(1)
846 , H_TX47(1)
847 , H_TX48(1)
848 , H_TX156(1)
849 , H_TX157(1)
850 FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe
851 WHERE p.party_id = p_party_id
852 AND p.party_id = pe.party_id
853 AND (p.status = 'M' or pe.effective_end_date is NULL) AND ROWNUM=1;
854 ELSE
855 SELECT p.PARTY_ID, p.STATUS
856 ,p.PARTY_NAME
857 ,p.PARTY_NUMBER
858 ,p.PARTY_TYPE
859 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
860 ,NULL
861 ,NULL
862 ,NULL
863 ,NULL
864 ,NULL
865 ,NULL
866 ,p.CATEGORY_CODE
867 ,p.REFERENCE_USE_FLAG
868 ,NULL
869 INTO H_P_PARTY_ID(1), H_STATUS(1)
870 , H_TX2(1)
871 , H_TX34(1)
872 , H_TX36(1)
873 , H_TX39(1)
874 , H_TX41(1)
875 , H_TX42(1)
876 , H_TX44(1)
877 , H_TX45(1)
878 , H_TX46(1)
879 , H_TX47(1)
880 , H_TX48(1)
881 , H_TX156(1)
882 , H_TX157(1)
883 FROM HZ_PARTIES p
884 WHERE p.party_id = p_party_id;
885 END IF;
886 H_TX32(1):=HZ_PARTY_ACQUIRE.get_account_info(H_P_PARTY_ID(1),'PARTY','ALL_ACCOUNT_NAMES', 'STAGE');
887 H_TX35(1):=HZ_PARTY_ACQUIRE.get_account_info(H_P_PARTY_ID(1),'PARTY','ALL_ACCOUNT_NUMBERS', 'STAGE');
888 H_TX61(1):=HZ_EMAIL_DOMAINS_V2PUB.get_email_domains(H_P_PARTY_ID(1),'PARTY','DOMAIN_NAME', 'STAGE');
889 H_TX63(1):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_P_PARTY_ID(1),'PARTY','PARTY_SOURCE_SYSTEM_REF', 'STAGE');
890 H_TX4(1):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX2(1),NULL, 'PARTY_NAME','PARTY','STAGE');
891 H_TX8(1):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX2(1),NULL, 'PARTY_NAME','PARTY','STAGE');
892 H_TX19(1):=HZ_TRANS_PKG.SOUNDX(H_TX2(1),NULL, 'PARTY_NAME','PARTY');
893 H_TX33(1):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX32(1),NULL, 'ALL_ACCOUNT_NAMES','PARTY','STAGE');
894 H_TX40(1):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX39(1),NULL, 'PARTY_ALL_NAMES','PARTY','STAGE');
895 H_TX43(1):=HZ_TRANS_PKG.CLEANSE(H_TX42(1),NULL, 'TAX_NAME','PARTY');
896 H_TX59(1):=HZ_TRANS_PKG.BASIC_WRNAMES(H_TX2(1),NULL, 'PARTY_NAME','PARTY','STAGE');
897 H_TX60(1):=HZ_TRANS_PKG.BASIC_CLEANSE_WRNAMES(H_TX2(1),NULL, 'PARTY_NAME','PARTY','STAGE');
898 H_TX62(1):=HZ_EMAIL_DOMAINS_V2PUB.FULL_DOMAIN(H_TX61(1),NULL, 'DOMAIN_NAME','PARTY');
899 H_TX158(1):=HZ_TRANS_PKG.SOUNDX(H_TX39(1),NULL, 'PARTY_ALL_NAMES','PARTY');
900 H_TX2(1):=HZ_TRANS_PKG.EXACT_PADDED(H_TX2(1),NULL, 'PARTY_NAME','PARTY');
901 H_TX32(1):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX32(1),NULL, 'ALL_ACCOUNT_NAMES','PARTY','STAGE');
902 H_TX34(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX34(1),NULL, 'PARTY_NUMBER','PARTY','STAGE');
903 H_TX35(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX35(1),NULL, 'ALL_ACCOUNT_NUMBERS','PARTY','STAGE');
904 H_TX36(1):=HZ_TRANS_PKG.EXACT(H_TX36(1),NULL, 'PARTY_TYPE','PARTY');
905 H_TX39(1):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX39(1),NULL, 'PARTY_ALL_NAMES','PARTY','STAGE');
906 H_TX41(1):=HZ_TRANS_PKG.EXACT(H_TX41(1),NULL, 'DUNS_NUMBER_C','PARTY');
907 H_TX42(1):=HZ_TRANS_PKG.EXACT(H_TX42(1),NULL, 'TAX_NAME','PARTY');
908 H_TX44(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX44(1),NULL, 'TAX_REFERENCE','PARTY','STAGE');
909 H_TX45(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX45(1),NULL, 'JGZZ_FISCAL_CODE','PARTY','STAGE');
910 H_TX46(1):=HZ_TRANS_PKG.EXACT(H_TX46(1),NULL, 'SIC_CODE','PARTY');
911 H_TX47(1):=HZ_TRANS_PKG.EXACT(H_TX47(1),NULL, 'SIC_CODE_TYPE','PARTY');
912 H_TX48(1):=HZ_TRANS_PKG.EXACT(H_TX48(1),NULL, 'CATEGORY_CODE','PARTY');
913 H_TX61(1):=HZ_EMAIL_DOMAINS_V2PUB.CORE_DOMAIN(H_TX61(1),NULL, 'DOMAIN_NAME','PARTY');
914 H_TX63(1):=HZ_TRANS_PKG.EXACT(H_TX63(1),NULL, 'PARTY_SOURCE_SYSTEM_REF','PARTY');
915 H_TX156(1):=HZ_TRANS_PKG.EXACT(H_TX156(1),NULL, 'REFERENCE_USE_FLAG','PARTY');
916 H_TX157(1):=HZ_TRANS_PKG.EXACT(H_TX157(1),NULL, 'CORPORATION_CLASS','PARTY');
917 l_tryins := FALSE;
918 l_tryupd := FALSE;
919 IF p_operation='C' THEN
920 l_tryins:=TRUE;
921 ELSE
922 l_tryupd:=TRUE;
923 END IF;
924 WHILE (l_tryins OR l_tryupd) LOOP
925 IF l_tryins THEN
926 BEGIN
927 l_tryins:=FALSE;
928 INSERT INTO HZ_STAGED_PARTIES (
929 PARTY_ID
930 ,STATUS
931 ,D_PS
932 ,D_CT
933 ,D_CPT
934 , TX2
935 , TX4
936 , TX8
937 , TX19
938 , TX32
939 , TX33
940 , TX34
941 , TX35
942 , TX36
943 , TX39
944 , TX40
945 , TX41
946 , TX42
947 , TX43
948 , TX44
949 , TX45
950 , TX46
951 , TX47
952 , TX48
953 , TX59
954 , TX60
955 , TX61
956 , TX62
957 , TX63
958 , TX156
959 , TX157
960 , TX158
961 ) VALUES (
962 H_P_PARTY_ID(1)
963 ,H_STATUS(1)
964 ,'SYNC'
965 ,'SYNC'
966 ,'SYNC'
967 , decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
968 , decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
969 , decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
970 , decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
971 , decode(H_TX32(1),null,H_TX32(1),H_TX32(1)||' ')
972 , decode(H_TX33(1),null,H_TX33(1),H_TX33(1)||' ')
973 , decode(H_TX34(1),null,H_TX34(1),H_TX34(1)||' ')
974 , decode(H_TX35(1),null,H_TX35(1),H_TX35(1)||' ')
975 , decode(H_TX36(1),null,H_TX36(1),H_TX36(1)||' ')
976 , decode(H_TX39(1),null,H_TX39(1),H_TX39(1)||' ')
977 , decode(H_TX40(1),null,H_TX40(1),H_TX40(1)||' ')
978 , decode(H_TX41(1),null,H_TX41(1),H_TX41(1)||' ')
979 , decode(H_TX42(1),null,H_TX42(1),H_TX42(1)||' ')
980 , decode(H_TX43(1),null,H_TX43(1),H_TX43(1)||' ')
981 , decode(H_TX44(1),null,H_TX44(1),H_TX44(1)||' ')
982 , decode(H_TX45(1),null,H_TX45(1),H_TX45(1)||' ')
983 , decode(H_TX46(1),null,H_TX46(1),H_TX46(1)||' ')
984 , decode(H_TX47(1),null,H_TX47(1),H_TX47(1)||' ')
985 , decode(H_TX48(1),null,H_TX48(1),H_TX48(1)||' ')
986 , decode(H_TX59(1),null,H_TX59(1),H_TX59(1)||' ')
987 , decode(H_TX60(1),null,H_TX60(1),H_TX60(1)||' ')
988 , decode(H_TX61(1),null,H_TX61(1),H_TX61(1)||' ')
989 , decode(H_TX62(1),null,H_TX62(1),H_TX62(1)||' ')
990 , decode(H_TX63(1),null,H_TX63(1),H_TX63(1)||' ')
991 , decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
992 , decode(H_TX157(1),null,H_TX157(1),H_TX157(1)||' ')
993 , decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
994 );
995 EXCEPTION
996 WHEN DUP_VAL_ON_INDEX THEN
997 IF p_operation='C' THEN
998 l_tryupd:=TRUE;
999 END IF;
1000 END;
1001 END IF;
1002 IF l_tryupd THEN
1003 BEGIN
1004 l_tryupd:=FALSE;
1005 UPDATE HZ_STAGED_PARTIES SET
1006 status =H_STATUS(1)
1007 ,concat_col = concat_col
1008 ,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
1009 ,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
1010 ,TX8=decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
1011 ,TX19=decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
1012 ,TX32=decode(H_TX32(1),null,H_TX32(1),H_TX32(1)||' ')
1013 ,TX33=decode(H_TX33(1),null,H_TX33(1),H_TX33(1)||' ')
1014 ,TX34=decode(H_TX34(1),null,H_TX34(1),H_TX34(1)||' ')
1015 ,TX35=decode(H_TX35(1),null,H_TX35(1),H_TX35(1)||' ')
1016 ,TX36=decode(H_TX36(1),null,H_TX36(1),H_TX36(1)||' ')
1017 ,TX39=decode(H_TX39(1),null,H_TX39(1),H_TX39(1)||' ')
1018 ,TX40=decode(H_TX40(1),null,H_TX40(1),H_TX40(1)||' ')
1019 ,TX41=decode(H_TX41(1),null,H_TX41(1),H_TX41(1)||' ')
1020 ,TX42=decode(H_TX42(1),null,H_TX42(1),H_TX42(1)||' ')
1021 ,TX43=decode(H_TX43(1),null,H_TX43(1),H_TX43(1)||' ')
1022 ,TX44=decode(H_TX44(1),null,H_TX44(1),H_TX44(1)||' ')
1023 ,TX45=decode(H_TX45(1),null,H_TX45(1),H_TX45(1)||' ')
1024 ,TX46=decode(H_TX46(1),null,H_TX46(1),H_TX46(1)||' ')
1025 ,TX47=decode(H_TX47(1),null,H_TX47(1),H_TX47(1)||' ')
1026 ,TX48=decode(H_TX48(1),null,H_TX48(1),H_TX48(1)||' ')
1027 ,TX59=decode(H_TX59(1),null,H_TX59(1),H_TX59(1)||' ')
1028 ,TX60=decode(H_TX60(1),null,H_TX60(1),H_TX60(1)||' ')
1029 ,TX61=decode(H_TX61(1),null,H_TX61(1),H_TX61(1)||' ')
1030 ,TX62=decode(H_TX62(1),null,H_TX62(1),H_TX62(1)||' ')
1031 ,TX63=decode(H_TX63(1),null,H_TX63(1),H_TX63(1)||' ')
1032 ,TX156=decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
1033 ,TX157=decode(H_TX157(1),null,H_TX157(1),H_TX157(1)||' ')
1034 ,TX158=decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
1035 WHERE PARTY_ID=H_P_PARTY_ID(1);
1036 IF SQL%ROWCOUNT=0 AND p_operation='U' THEN
1037 l_tryins := TRUE;
1038 END IF;
1039 EXCEPTION
1040 WHEN NO_DATA_FOUND THEN
1041 IF p_operation='U' THEN
1042 l_tryins := TRUE;
1043 END IF;
1044 END;
1045 END IF;
1046 END LOOP;
1047 END;
1048
1049 PROCEDURE sync_single_party_online (
1050 p_party_id NUMBER,
1051 p_operation VARCHAR2) IS
1052
1053 l_tryins BOOLEAN;
1054 l_tryupd BOOLEAN;
1055 l_party_type VARCHAR2(30);
1056 l_org_contact_id NUMBER;
1057 l_sql_err_message VARCHAR2(2000);
1058
1059 --bug 4500011 replaced hz_party_relationships with hz_relationships
1060 CURSOR c_contact IS
1061 SELECT oc.org_contact_id
1062 FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc
1063 WHERE pr.relationship_id = oc.party_relationship_id
1064 AND pr.subject_id = p_party_id
1065 AND pr.subject_table_name = 'HZ_PARTIES'
1066 AND pr.object_table_name = 'HZ_PARTIES'
1067 AND pr.directional_flag = 'F';
1068
1069 BEGIN
1070
1071 -- Get party_type
1072 SELECT party_type INTO l_party_type
1073 FROM hz_parties WHERE party_id = p_party_id;
1074
1075 -- Set global G_PARTY_TYPE variable value
1076 hz_trans_pkg.set_party_type(l_party_type);
1077
1078 IF l_party_type = 'PERSON' THEN
1079 ----------------------------------
1080 -- Take care of CONTACT INFORMATION
1081 -- When the operation is an update
1082 ----------------------------------
1083 IF p_operation = 'U' THEN
1084 OPEN c_contact;
1085 LOOP
1086 FETCH c_contact INTO l_org_contact_id;
1087 EXIT WHEN c_contact%NOTFOUND;
1088 BEGIN
1089 sync_single_contact_online(l_org_contact_id, p_operation);
1090 EXCEPTION WHEN OTHERS THEN
1091 -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
1092 -- FOR ONLINE FLOWS
1093 l_sql_err_message := SQLERRM;
1094 insert_dqm_sync_error_rec(p_party_id,l_org_contact_id,null,null,'CONTACTS','U','E','Y', l_sql_err_message);
1095 END ;
1096 END LOOP;
1097 END IF ;
1098 END IF;
1099
1100 IF l_party_type = 'ORGANIZATION' THEN
1101 SELECT p.PARTY_ID, p.STATUS
1102 ,p.PARTY_NAME
1103 ,p.PARTY_NUMBER
1104 ,p.PARTY_TYPE
1105 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
1106 ,op.DUNS_NUMBER_C
1107 ,op.TAX_NAME
1108 ,op.TAX_REFERENCE
1109 ,op.JGZZ_FISCAL_CODE
1110 ,op.SIC_CODE
1111 ,op.SIC_CODE_TYPE
1112 ,p.CATEGORY_CODE
1113 ,p.REFERENCE_USE_FLAG
1114 ,op.CORPORATION_CLASS
1115 INTO H_P_PARTY_ID(1), H_STATUS(1)
1116 , H_TX2(1)
1117 , H_TX34(1)
1118 , H_TX36(1)
1119 , H_TX39(1)
1120 , H_TX41(1)
1121 , H_TX42(1)
1122 , H_TX44(1)
1123 , H_TX45(1)
1124 , H_TX46(1)
1125 , H_TX47(1)
1126 , H_TX48(1)
1127 , H_TX156(1)
1128 , H_TX157(1)
1129 FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
1130 WHERE p.party_id = p_party_id
1131 AND p.party_id = op.party_id
1132 AND (p.status = 'M' or op.effective_end_date is NULL) AND ROWNUM=1;
1133 ELSIF l_party_type = 'PERSON' THEN
1134 SELECT p.PARTY_ID, p.STATUS
1135 ,p.PARTY_NAME
1136 ,p.PARTY_NUMBER
1137 ,p.PARTY_TYPE
1138 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
1139 ,NULL
1140 ,pe.TAX_NAME
1141 ,pe.TAX_REFERENCE
1142 ,pe.JGZZ_FISCAL_CODE
1143 ,NULL
1144 ,NULL
1145 ,p.CATEGORY_CODE
1146 ,p.REFERENCE_USE_FLAG
1147 ,NULL
1148 INTO H_P_PARTY_ID(1), H_STATUS(1)
1149 , H_TX2(1)
1150 , H_TX34(1)
1151 , H_TX36(1)
1152 , H_TX39(1)
1153 , H_TX41(1)
1154 , H_TX42(1)
1155 , H_TX44(1)
1156 , H_TX45(1)
1157 , H_TX46(1)
1158 , H_TX47(1)
1159 , H_TX48(1)
1160 , H_TX156(1)
1161 , H_TX157(1)
1162 FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe
1163 WHERE p.party_id = p_party_id
1164 AND p.party_id = pe.party_id
1165 AND (p.status = 'M' or pe.effective_end_date is NULL) AND ROWNUM=1;
1166 ELSE
1167 SELECT p.PARTY_ID, p.STATUS
1168 ,p.PARTY_NAME
1169 ,p.PARTY_NUMBER
1170 ,p.PARTY_TYPE
1171 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
1172 ,NULL
1173 ,NULL
1174 ,NULL
1175 ,NULL
1176 ,NULL
1177 ,NULL
1178 ,p.CATEGORY_CODE
1179 ,p.REFERENCE_USE_FLAG
1180 ,NULL
1181 INTO H_P_PARTY_ID(1), H_STATUS(1)
1182 , H_TX2(1)
1183 , H_TX34(1)
1184 , H_TX36(1)
1185 , H_TX39(1)
1186 , H_TX41(1)
1187 , H_TX42(1)
1188 , H_TX44(1)
1189 , H_TX45(1)
1190 , H_TX46(1)
1191 , H_TX47(1)
1192 , H_TX48(1)
1193 , H_TX156(1)
1194 , H_TX157(1)
1195 FROM HZ_PARTIES p
1196 WHERE p.party_id = p_party_id;
1197 END IF;
1198
1199 H_TX32(1):=HZ_PARTY_ACQUIRE.get_account_info(H_P_PARTY_ID(1),'PARTY','ALL_ACCOUNT_NAMES', 'STAGE');
1200 H_TX35(1):=HZ_PARTY_ACQUIRE.get_account_info(H_P_PARTY_ID(1),'PARTY','ALL_ACCOUNT_NUMBERS', 'STAGE');
1201 H_TX61(1):=HZ_EMAIL_DOMAINS_V2PUB.get_email_domains(H_P_PARTY_ID(1),'PARTY','DOMAIN_NAME', 'STAGE');
1202 H_TX63(1):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_P_PARTY_ID(1),'PARTY','PARTY_SOURCE_SYSTEM_REF', 'STAGE');
1203 H_TX4(1):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX2(1),NULL, 'PARTY_NAME','PARTY','STAGE');
1204 H_TX8(1):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX2(1),NULL, 'PARTY_NAME','PARTY','STAGE');
1205 H_TX19(1):=HZ_TRANS_PKG.SOUNDX(H_TX2(1),NULL, 'PARTY_NAME','PARTY');
1206 H_TX33(1):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX32(1),NULL, 'ALL_ACCOUNT_NAMES','PARTY','STAGE');
1207 H_TX40(1):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX39(1),NULL, 'PARTY_ALL_NAMES','PARTY','STAGE');
1208 H_TX43(1):=HZ_TRANS_PKG.CLEANSE(H_TX42(1),NULL, 'TAX_NAME','PARTY');
1209 H_TX59(1):=HZ_TRANS_PKG.BASIC_WRNAMES(H_TX2(1),NULL, 'PARTY_NAME','PARTY','STAGE');
1210 H_TX60(1):=HZ_TRANS_PKG.BASIC_CLEANSE_WRNAMES(H_TX2(1),NULL, 'PARTY_NAME','PARTY','STAGE');
1211 H_TX62(1):=HZ_EMAIL_DOMAINS_V2PUB.FULL_DOMAIN(H_TX61(1),NULL, 'DOMAIN_NAME','PARTY');
1212 H_TX158(1):=HZ_TRANS_PKG.SOUNDX(H_TX39(1),NULL, 'PARTY_ALL_NAMES','PARTY');
1213 H_TX2(1):=HZ_TRANS_PKG.EXACT_PADDED(H_TX2(1),NULL, 'PARTY_NAME','PARTY');
1214 H_TX32(1):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX32(1),NULL, 'ALL_ACCOUNT_NAMES','PARTY','STAGE');
1215 H_TX34(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX34(1),NULL, 'PARTY_NUMBER','PARTY','STAGE');
1216 H_TX35(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX35(1),NULL, 'ALL_ACCOUNT_NUMBERS','PARTY','STAGE');
1217 H_TX36(1):=HZ_TRANS_PKG.EXACT(H_TX36(1),NULL, 'PARTY_TYPE','PARTY');
1218 H_TX39(1):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX39(1),NULL, 'PARTY_ALL_NAMES','PARTY','STAGE');
1219 H_TX41(1):=HZ_TRANS_PKG.EXACT(H_TX41(1),NULL, 'DUNS_NUMBER_C','PARTY');
1220 H_TX42(1):=HZ_TRANS_PKG.EXACT(H_TX42(1),NULL, 'TAX_NAME','PARTY');
1221 H_TX44(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX44(1),NULL, 'TAX_REFERENCE','PARTY','STAGE');
1222 H_TX45(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX45(1),NULL, 'JGZZ_FISCAL_CODE','PARTY','STAGE');
1223 H_TX46(1):=HZ_TRANS_PKG.EXACT(H_TX46(1),NULL, 'SIC_CODE','PARTY');
1224 H_TX47(1):=HZ_TRANS_PKG.EXACT(H_TX47(1),NULL, 'SIC_CODE_TYPE','PARTY');
1225 H_TX48(1):=HZ_TRANS_PKG.EXACT(H_TX48(1),NULL, 'CATEGORY_CODE','PARTY');
1226 H_TX61(1):=HZ_EMAIL_DOMAINS_V2PUB.CORE_DOMAIN(H_TX61(1),NULL, 'DOMAIN_NAME','PARTY');
1227 H_TX63(1):=HZ_TRANS_PKG.EXACT(H_TX63(1),NULL, 'PARTY_SOURCE_SYSTEM_REF','PARTY');
1228 H_TX156(1):=HZ_TRANS_PKG.EXACT(H_TX156(1),NULL, 'REFERENCE_USE_FLAG','PARTY');
1229 H_TX157(1):=HZ_TRANS_PKG.EXACT(H_TX157(1),NULL, 'CORPORATION_CLASS','PARTY');
1230
1231 l_tryins := FALSE;
1232 l_tryupd := FALSE;
1233
1234 IF p_operation='C' THEN
1235 l_tryins:=TRUE;
1236 ELSE
1237 l_tryupd:=TRUE;
1238 END IF;
1239
1240 WHILE (l_tryins OR l_tryupd) LOOP
1241 IF l_tryins THEN
1242 BEGIN
1243 l_tryins:=FALSE;
1244 INSERT INTO HZ_STAGED_PARTIES (
1245 PARTY_ID
1246 ,STATUS
1247 ,D_PS
1248 ,D_CT
1249 ,D_CPT
1250 ,TX2
1251 ,TX4
1252 ,TX8
1253 ,TX19
1254 ,TX32
1255 ,TX33
1256 ,TX34
1257 ,TX35
1258 ,TX36
1259 ,TX39
1260 ,TX40
1261 ,TX41
1262 ,TX42
1263 ,TX43
1264 ,TX44
1265 ,TX45
1266 ,TX46
1267 ,TX47
1268 ,TX48
1269 ,TX59
1270 ,TX60
1271 ,TX61
1272 ,TX62
1273 ,TX63
1274 ,TX156
1275 ,TX157
1276 ,TX158
1277 ) VALUES (
1278 H_P_PARTY_ID(1)
1279 ,H_STATUS(1)
1280 ,'SYNC'
1281 ,'SYNC'
1282 ,'SYNC'
1283 ,decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
1284 ,decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
1285 ,decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
1286 ,decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
1287 ,decode(H_TX32(1),null,H_TX32(1),H_TX32(1)||' ')
1288 ,decode(H_TX33(1),null,H_TX33(1),H_TX33(1)||' ')
1289 ,decode(H_TX34(1),null,H_TX34(1),H_TX34(1)||' ')
1290 ,decode(H_TX35(1),null,H_TX35(1),H_TX35(1)||' ')
1291 ,decode(H_TX36(1),null,H_TX36(1),H_TX36(1)||' ')
1292 ,decode(H_TX39(1),null,H_TX39(1),H_TX39(1)||' ')
1293 ,decode(H_TX40(1),null,H_TX40(1),H_TX40(1)||' ')
1294 ,decode(H_TX41(1),null,H_TX41(1),H_TX41(1)||' ')
1295 ,decode(H_TX42(1),null,H_TX42(1),H_TX42(1)||' ')
1296 ,decode(H_TX43(1),null,H_TX43(1),H_TX43(1)||' ')
1297 ,decode(H_TX44(1),null,H_TX44(1),H_TX44(1)||' ')
1298 ,decode(H_TX45(1),null,H_TX45(1),H_TX45(1)||' ')
1299 ,decode(H_TX46(1),null,H_TX46(1),H_TX46(1)||' ')
1300 ,decode(H_TX47(1),null,H_TX47(1),H_TX47(1)||' ')
1301 ,decode(H_TX48(1),null,H_TX48(1),H_TX48(1)||' ')
1302 ,decode(H_TX59(1),null,H_TX59(1),H_TX59(1)||' ')
1303 ,decode(H_TX60(1),null,H_TX60(1),H_TX60(1)||' ')
1304 ,decode(H_TX61(1),null,H_TX61(1),H_TX61(1)||' ')
1305 ,decode(H_TX62(1),null,H_TX62(1),H_TX62(1)||' ')
1306 ,decode(H_TX63(1),null,H_TX63(1),H_TX63(1)||' ')
1307 ,decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
1308 ,decode(H_TX157(1),null,H_TX157(1),H_TX157(1)||' ')
1309 ,decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
1310 );
1311 EXCEPTION
1312 WHEN DUP_VAL_ON_INDEX THEN
1313 IF p_operation='C' THEN
1314 l_tryupd:=TRUE;
1315 END IF;
1316 END;
1317 END IF;
1318
1319 IF l_tryupd THEN
1320 BEGIN
1321 l_tryupd:=FALSE;
1322 UPDATE HZ_STAGED_PARTIES SET
1323 concat_col = concat_col
1324 ,status =H_STATUS(1)
1325 ,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
1326 ,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
1327 ,TX8=decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
1328 ,TX19=decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
1329 ,TX32=decode(H_TX32(1),null,H_TX32(1),H_TX32(1)||' ')
1330 ,TX33=decode(H_TX33(1),null,H_TX33(1),H_TX33(1)||' ')
1331 ,TX34=decode(H_TX34(1),null,H_TX34(1),H_TX34(1)||' ')
1332 ,TX35=decode(H_TX35(1),null,H_TX35(1),H_TX35(1)||' ')
1333 ,TX36=decode(H_TX36(1),null,H_TX36(1),H_TX36(1)||' ')
1334 ,TX39=decode(H_TX39(1),null,H_TX39(1),H_TX39(1)||' ')
1335 ,TX40=decode(H_TX40(1),null,H_TX40(1),H_TX40(1)||' ')
1336 ,TX41=decode(H_TX41(1),null,H_TX41(1),H_TX41(1)||' ')
1337 ,TX42=decode(H_TX42(1),null,H_TX42(1),H_TX42(1)||' ')
1338 ,TX43=decode(H_TX43(1),null,H_TX43(1),H_TX43(1)||' ')
1339 ,TX44=decode(H_TX44(1),null,H_TX44(1),H_TX44(1)||' ')
1340 ,TX45=decode(H_TX45(1),null,H_TX45(1),H_TX45(1)||' ')
1341 ,TX46=decode(H_TX46(1),null,H_TX46(1),H_TX46(1)||' ')
1342 ,TX47=decode(H_TX47(1),null,H_TX47(1),H_TX47(1)||' ')
1343 ,TX48=decode(H_TX48(1),null,H_TX48(1),H_TX48(1)||' ')
1344 ,TX59=decode(H_TX59(1),null,H_TX59(1),H_TX59(1)||' ')
1345 ,TX60=decode(H_TX60(1),null,H_TX60(1),H_TX60(1)||' ')
1346 ,TX61=decode(H_TX61(1),null,H_TX61(1),H_TX61(1)||' ')
1347 ,TX62=decode(H_TX62(1),null,H_TX62(1),H_TX62(1)||' ')
1348 ,TX63=decode(H_TX63(1),null,H_TX63(1),H_TX63(1)||' ')
1349 ,TX156=decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
1350 ,TX157=decode(H_TX157(1),null,H_TX157(1),H_TX157(1)||' ')
1351 ,TX158=decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
1352 WHERE PARTY_ID=H_P_PARTY_ID(1);
1353 IF SQL%ROWCOUNT=0 AND p_operation='U' THEN
1354 l_tryins := TRUE;
1355 END IF;
1356 EXCEPTION
1357 WHEN NO_DATA_FOUND THEN
1358 IF p_operation='U' THEN
1359 l_tryins := TRUE;
1360 END IF;
1361 END;
1362 END IF;
1363 END LOOP;
1364
1365 -- REPURI. Bug 4884742. If shadow staging is completely successfully
1366 -- insert a record into hz_dqm_sh_sync_interface table for each record
1367 IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1368 BEGIN
1369 HZ_DQM_SYNC.insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_operation);
1370 EXCEPTION WHEN OTHERS THEN
1371 NULL;
1372 END;
1373 END IF;
1374
1375 EXCEPTION WHEN OTHERS THEN
1376 -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
1377 -- FOR ONLINE FLOWS
1378 l_sql_err_message := SQLERRM;
1379 insert_dqm_sync_error_rec(p_party_id, NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'Y', l_sql_err_message);
1380 END;
1381
1382 PROCEDURE insert_stage_contacts IS
1383 l_limit NUMBER := 200;
1384 l_last_fetch BOOLEAN := FALSE;
1385 l_denorm VARCHAR2(2000);
1386 l_st number;
1387 l_en number;
1388 CURSOR contact_cur IS
1389 SELECT
1390 /*+ ORDERED USE_NL(R OC PP)*/
1391 oc.ORG_CONTACT_ID , r.OBJECT_ID, r.PARTY_ID, g.PARTY_INDEX, r.STATUS
1392 ,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
1393 ,oc.CONTACT_NUMBER
1394 ,oc.JOB_TITLE
1395 FROM HZ_DQM_STAGE_GT g, HZ_RELATIONSHIPS r,
1396 HZ_ORG_CONTACTS oc, HZ_PERSON_PROFILES pp
1397 WHERE oc.party_relationship_id = r.relationship_id
1398 AND r.object_id = g.party_id
1399 AND r.subject_id = pp.party_id
1400 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1401 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1402 AND DIRECTIONAL_FLAG= 'F'
1403 AND pp.effective_end_date is NULL
1404 AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
1405 AND (r.status is null OR r.status = 'A' or r.status = 'I');
1406
1407 BEGIN
1408 OPEN contact_cur;
1409 LOOP
1410 FETCH contact_cur BULK COLLECT INTO
1411 H_ORG_CONTACT_ID
1412 ,H_C_PARTY_ID
1413 ,H_R_PARTY_ID
1414 ,H_PARTY_INDEX
1415 ,H_STATUS
1416 ,H_TX2
1417 ,H_TX11
1418 ,H_TX22
1419 LIMIT l_limit;
1420
1421 IF contact_cur%NOTFOUND THEN
1422 l_last_fetch:=TRUE;
1423 END IF;
1424 IF H_C_PARTY_ID.COUNT=0 AND l_last_fetch THEN
1425 EXIT;
1426 END IF;
1427 FOR I in H_C_PARTY_ID.FIRST..H_C_PARTY_ID.LAST LOOP
1428
1429 H_TX25(I):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_ORG_CONTACT_ID(I),'CONTACTS','CONTACT_SOURCE_SYSTEM_REF', 'STAGE');
1430 H_TX5(I):=HZ_TRANS_PKG.WRPERSON_EXACT(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1431 H_TX6(I):=HZ_TRANS_PKG.WRPERSON_CLEANSE(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1432 H_TX23(I):=HZ_TRANS_PKG.BASIC_WRPERSON(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1433 H_TX24(I):=HZ_TRANS_PKG.BASIC_CLEANSE_WRPERSON(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1434 H_TX156(I):=HZ_TRANS_PKG.SOUNDX(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS');
1435 H_TX2(I):=HZ_TRANS_PKG.EXACT_PADDED(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS');
1436 H_TX11(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(I),NULL, 'CONTACT_NUMBER','CONTACTS','STAGE');
1437 H_TX22(I):=HZ_TRANS_PKG.EXACT(H_TX22(I),NULL, 'JOB_TITLE','CONTACTS');
1438 H_TX25(I):=HZ_TRANS_PKG.EXACT(H_TX25(I),NULL, 'CONTACT_SOURCE_SYSTEM_REF','CONTACTS');
1439 BEGIN
1440 l_denorm := H_TX22(I)
1441 ;
1442 IF H_CT_DEN(H_PARTY_INDEX(I)) = 'SYNC' THEN
1443 NULL;
1444 ELSIF lengthb(H_CT_DEN(H_PARTY_INDEX(I)))+lengthb(l_denorm)<2000 THEN
1445 IF H_CT_DEN(H_PARTY_INDEX(I)) IS NULL OR instrb(H_CT_DEN(H_PARTY_INDEX(I)),l_denorm)= 0 THEN
1446 H_CT_DEN(H_PARTY_INDEX(I)) := H_CT_DEN(H_PARTY_INDEX(I)) || ' ' || l_denorm;
1447 END IF;
1448 ELSE
1449 H_CT_DEN(H_PARTY_INDEX(I)) := 'SYNC';
1450 END IF;
1451 EXCEPTION WHEN OTHERS THEN
1452 IF SQLCODE=-6502 THEN
1453 H_CT_DEN(H_PARTY_INDEX(I)) := 'SYNC';
1454 END IF;
1455 END;
1456 END LOOP;
1457 l_st := 1;
1458 l_en := H_C_PARTY_ID.COUNT;
1459 LOOP
1460 BEGIN
1461 FORALL I in l_st..l_en
1462 INSERT INTO HZ_STAGED_CONTACTS (
1463 ORG_CONTACT_ID
1464 ,PARTY_ID
1465 ,STATUS_FLAG
1466 , TX2
1467 , TX5
1468 , TX6
1469 , TX11
1470 , TX22
1471 , TX23
1472 , TX24
1473 , TX25
1474 , TX156
1475 ) VALUES (
1476 H_ORG_CONTACT_ID(I)
1477 ,H_C_PARTY_ID(I)
1478 ,H_STATUS(I)
1479 , decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
1480 , decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
1481 , decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
1482 , decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
1483 , decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
1484 , decode(H_TX23(I),null,H_TX23(I),H_TX23(I)||' ')
1485 , decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
1486 , decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
1487 , decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
1488 );
1489 EXIT;
1490 EXCEPTION WHEN OTHERS THEN
1491 l_st:= l_st+SQL%ROWCOUNT+1;
1492 END;
1493 END LOOP;
1494 FORALL I in H_C_PARTY_ID.FIRST..H_C_PARTY_ID.LAST
1495 INSERT INTO HZ_DQM_STAGE_GT(PARTY_ID,OWNER_ID,ORG_CONTACT_ID,PARTY_INDEX)
1496 SELECT H_C_PARTY_ID(I), H_R_PARTY_ID(I), H_ORG_CONTACT_ID(I), H_PARTY_INDEX(I)
1497 FROM DUAL WHERE H_R_PARTY_ID(I) IS NOT NULL;
1498 IF l_last_fetch THEN
1499 EXIT;
1500 END IF;
1501 END LOOP;
1502 CLOSE contact_cur;
1503 END;
1504
1505 PROCEDURE sync_single_contact (
1506 p_org_contact_id NUMBER,
1507 p_operation VARCHAR2) IS
1508
1509 l_tryins BOOLEAN;
1510 l_tryupd BOOLEAN;
1511 BEGIN
1512 SELECT oc.ORG_CONTACT_ID, d.PARTY_ID, r.STATUS
1513 ,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
1514 ,oc.CONTACT_NUMBER
1515 ,oc.JOB_TITLE
1516 INTO H_ORG_CONTACT_ID(1), H_PARTY_ID(1), H_STATUS(1)
1517 , H_TX2(1)
1518 , H_TX11(1)
1519 , H_TX22(1)
1520 FROM HZ_ORG_CONTACTS oc, HZ_DQM_SYNC_INTERFACE d,
1521 HZ_RELATIONSHIPS r, HZ_PERSON_PROFILES pp
1522 WHERE d.ENTITY = 'CONTACTS'
1523 AND oc.org_contact_id = p_org_contact_id
1524 AND oc.org_contact_id = d.RECORD_ID
1525 AND oc.party_relationship_id = r.relationship_id
1526 AND r.subject_id = pp.party_id
1527 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1528 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1529 AND DIRECTIONAL_FLAG= 'F'
1530 AND pp.effective_end_date is NULL
1531 AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
1532 AND (r.status is null OR r.status = 'A' or r.status = 'I')
1533 AND ROWNUM=1;
1534 H_TX25(1):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_ORG_CONTACT_ID(1),'CONTACTS','CONTACT_SOURCE_SYSTEM_REF', 'STAGE');
1535 H_TX5(1):=HZ_TRANS_PKG.WRPERSON_EXACT(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1536 H_TX6(1):=HZ_TRANS_PKG.WRPERSON_CLEANSE(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1537 H_TX23(1):=HZ_TRANS_PKG.BASIC_WRPERSON(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1538 H_TX24(1):=HZ_TRANS_PKG.BASIC_CLEANSE_WRPERSON(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1539 H_TX156(1):=HZ_TRANS_PKG.SOUNDX(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS');
1540 H_TX2(1):=HZ_TRANS_PKG.EXACT_PADDED(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS');
1541 H_TX11(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(1),NULL, 'CONTACT_NUMBER','CONTACTS','STAGE');
1542 H_TX22(1):=HZ_TRANS_PKG.EXACT(H_TX22(1),NULL, 'JOB_TITLE','CONTACTS');
1543 H_TX25(1):=HZ_TRANS_PKG.EXACT(H_TX25(1),NULL, 'CONTACT_SOURCE_SYSTEM_REF','CONTACTS');
1544 l_tryins := FALSE;
1545 l_tryupd := FALSE;
1546 IF p_operation='C' THEN
1547 l_tryins:=TRUE;
1548 ELSE
1549 l_tryupd:=TRUE;
1550 END IF;
1551 WHILE (l_tryins OR l_tryupd) LOOP
1552 IF l_tryins THEN
1553 BEGIN
1554 l_tryins:=FALSE;
1555 INSERT INTO HZ_STAGED_CONTACTS (
1556 ORG_CONTACT_ID
1557 ,PARTY_ID
1558 ,STATUS_FLAG
1559 , TX2
1560 , TX5
1561 , TX6
1562 , TX11
1563 , TX22
1564 , TX23
1565 , TX24
1566 , TX25
1567 , TX156
1568 ) VALUES (
1569 H_ORG_CONTACT_ID(1)
1570 , H_PARTY_ID(1)
1571 , H_STATUS(1)
1572 , decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
1573 , decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
1574 , decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
1575 , decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
1576 , decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
1577 , decode(H_TX23(1),null,H_TX23(1),H_TX23(1)||' ')
1578 , decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
1579 , decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
1580 , decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
1581 );
1582 EXCEPTION
1583 WHEN DUP_VAL_ON_INDEX THEN
1584 IF p_operation='C' THEN
1585 l_tryupd:=TRUE;
1586 END IF;
1587 END;
1588 END IF;
1589 IF l_tryupd THEN
1590 BEGIN
1591 l_tryupd:=FALSE;
1592 UPDATE HZ_STAGED_CONTACTS SET
1593 concat_col = concat_col
1594 ,status_flag = H_STATUS(1)
1595 ,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
1596 ,TX5=decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
1597 ,TX6=decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
1598 ,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
1599 ,TX22=decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
1600 ,TX23=decode(H_TX23(1),null,H_TX23(1),H_TX23(1)||' ')
1601 ,TX24=decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
1602 ,TX25=decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
1603 ,TX156=decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
1604 WHERE ORG_CONTACT_ID=H_ORG_CONTACT_ID(1);
1605 IF SQL%ROWCOUNT=0 AND p_operation='U' THEN
1606 l_tryins := TRUE;
1607 END IF;
1608 EXCEPTION
1609 WHEN NO_DATA_FOUND THEN
1610 IF p_operation='U' THEN
1611 l_tryins := TRUE;
1612 END IF;
1613 END;
1614 END IF;
1615 END LOOP;
1616 --Fix for bug 5048604, to update concat_col during update of denorm column
1617 UPDATE HZ_STAGED_PARTIES set
1618 D_CT = 'SYNC'
1619 ,CONCAT_COL = CONCAT_COL
1620 WHERE PARTY_ID = H_PARTY_ID(1);
1621 END;
1622
1623 PROCEDURE sync_single_contact_online (
1624 p_org_contact_id NUMBER,
1625 p_operation VARCHAR2) IS
1626
1627 l_tryins BOOLEAN;
1628 l_tryupd BOOLEAN;
1629 l_party_id NUMBER;
1630 l_sql_err_message VARCHAR2(2000);
1631
1632 BEGIN
1633
1634 l_party_id := -1;
1635
1636 SELECT r.object_id INTO l_party_id
1637 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
1638 WHERE oc.org_contact_id = p_org_contact_id
1639 AND oc.party_relationship_id = r.relationship_id
1640 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1641 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1642 AND subject_type = 'PERSON'
1643 AND DIRECTIONAL_FLAG = 'F'
1644 AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
1645 AND (r.status is null OR r.status = 'A' or r.status = 'I') ;
1646
1647 SELECT oc.ORG_CONTACT_ID, l_party_id, r.status
1648 ,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
1649 ,oc.CONTACT_NUMBER
1650 ,oc.JOB_TITLE
1651 INTO H_ORG_CONTACT_ID(1), H_PARTY_ID(1), H_STATUS(1)
1652 ,H_TX2(1)
1653 ,H_TX11(1)
1654 ,H_TX22(1)
1655 FROM HZ_ORG_CONTACTS oc,
1656 HZ_RELATIONSHIPS r, HZ_PERSON_PROFILES pp
1657 WHERE
1658 oc.org_contact_id = p_org_contact_id
1659 AND oc.party_relationship_id = r.relationship_id
1660 AND r.subject_id = pp.party_id
1661 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1662 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1663 AND DIRECTIONAL_FLAG = 'F'
1664 AND pp.effective_end_date is NULL
1665 AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
1666 AND (r.status is null OR r.status = 'A' or r.status = 'I')
1667 AND ROWNUM=1;
1668
1669 H_TX25(1):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_ORG_CONTACT_ID(1),'CONTACTS','CONTACT_SOURCE_SYSTEM_REF', 'STAGE');
1670 H_TX5(1):=HZ_TRANS_PKG.WRPERSON_EXACT(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1671 H_TX6(1):=HZ_TRANS_PKG.WRPERSON_CLEANSE(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1672 H_TX23(1):=HZ_TRANS_PKG.BASIC_WRPERSON(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1673 H_TX24(1):=HZ_TRANS_PKG.BASIC_CLEANSE_WRPERSON(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
1674 H_TX156(1):=HZ_TRANS_PKG.SOUNDX(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS');
1675 H_TX2(1):=HZ_TRANS_PKG.EXACT_PADDED(H_TX2(1),NULL, 'CONTACT_NAME','CONTACTS');
1676 H_TX11(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(1),NULL, 'CONTACT_NUMBER','CONTACTS','STAGE');
1677 H_TX22(1):=HZ_TRANS_PKG.EXACT(H_TX22(1),NULL, 'JOB_TITLE','CONTACTS');
1678 H_TX25(1):=HZ_TRANS_PKG.EXACT(H_TX25(1),NULL, 'CONTACT_SOURCE_SYSTEM_REF','CONTACTS');
1679
1680 l_tryins := FALSE;
1681 l_tryupd := FALSE;
1682
1683 IF p_operation='C' THEN
1684 l_tryins:=TRUE;
1685 ELSE
1686 l_tryupd:=TRUE;
1687 END IF;
1688
1689 WHILE (l_tryins OR l_tryupd) LOOP
1690 IF l_tryins THEN
1691 BEGIN
1692 l_tryins:=FALSE;
1693 INSERT INTO HZ_STAGED_CONTACTS (
1694 ORG_CONTACT_ID
1695 ,PARTY_ID
1696 ,STATUS_FLAG
1697 ,TX2
1698 ,TX5
1699 ,TX6
1700 ,TX11
1701 ,TX22
1702 ,TX23
1703 ,TX24
1704 ,TX25
1705 ,TX156
1706 ) VALUES (
1707 H_ORG_CONTACT_ID(1)
1708 ,H_PARTY_ID(1)
1709 ,H_STATUS(1)
1710 ,decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
1711 ,decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
1712 ,decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
1713 ,decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
1714 ,decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
1715 ,decode(H_TX23(1),null,H_TX23(1),H_TX23(1)||' ')
1716 ,decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
1717 ,decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
1718 ,decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
1719 );
1720 EXCEPTION
1721 WHEN DUP_VAL_ON_INDEX THEN
1722 IF p_operation='C' THEN
1723 l_tryupd:=TRUE;
1724 END IF;
1725 END;
1726 END IF;
1727
1728 IF l_tryupd THEN
1729 BEGIN
1730 l_tryupd:=FALSE;
1731 UPDATE HZ_STAGED_CONTACTS SET
1732 concat_col = concat_col
1733 ,status_flag = H_STATUS(1)
1734 ,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
1735 ,TX5=decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
1736 ,TX6=decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
1737 ,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
1738 ,TX22=decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
1739 ,TX23=decode(H_TX23(1),null,H_TX23(1),H_TX23(1)||' ')
1740 ,TX24=decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
1741 ,TX25=decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
1742 ,TX156=decode(H_TX156(1),null,H_TX156(1),H_TX156(1)||' ')
1743 WHERE ORG_CONTACT_ID=H_ORG_CONTACT_ID(1);
1744 IF SQL%ROWCOUNT=0 AND p_operation='U' THEN
1745 l_tryins := TRUE;
1746 END IF;
1747 EXCEPTION
1748 WHEN NO_DATA_FOUND THEN
1749 IF p_operation='U' THEN
1750 l_tryins := TRUE;
1751 END IF;
1752 END;
1753 END IF;
1754 END LOOP;
1755
1756 --Fix for bug 5048604, to update concat_col during update of denorm column
1757 UPDATE HZ_STAGED_PARTIES set
1758 D_CT = 'SYNC'
1759 ,CONCAT_COL = CONCAT_COL
1760 WHERE PARTY_ID = H_PARTY_ID(1);
1761
1762 -- REPURI. Bug 4884742. If shadow staging is completely successfully
1763 -- insert a record into hz_dqm_sh_sync_interface table for each record
1764 IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
1765 BEGIN
1766 HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_operation);
1767 EXCEPTION WHEN OTHERS THEN
1768 NULL;
1769 END;
1770 END IF;
1771
1772 EXCEPTION WHEN OTHERS THEN
1773 -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
1774 -- FOR ONLINE FLOWS
1775 l_sql_err_message := SQLERRM;
1776 insert_dqm_sync_error_rec(l_party_id, p_org_contact_id, NULL, NULL, 'CONTACTS', p_operation, 'E', 'Y', l_sql_err_message);
1777 END;
1778
1779 PROCEDURE insert_stage_contact_pts IS
1780 l_limit NUMBER := 200;
1781 l_last_fetch BOOLEAN := FALSE;
1782 l_denorm VARCHAR2(2000);
1783 l_st number;
1784 l_en number;
1785
1786 CURSOR contact_pt_cur IS
1787 SELECT /*+ ORDERED USE_NL(cp) */ cp.CONTACT_POINT_ID, g.party_id, g.party_site_id, g.org_contact_id, cp.CONTACT_POINT_TYPE, PARTY_INDEX, cp.STATUS
1788 ,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
1789 ,cp.PHONE_NUMBER
1790 ,cp.PHONE_AREA_CODE
1791 ,cp.PHONE_COUNTRY_CODE
1792 ,cp.EMAIL_ADDRESS
1793 ,cp.URL
1794 ,cp.PRIMARY_FLAG
1795 ,translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
1796 ,cp.PHONE_LINE_TYPE
1797 ,cp.STATUS
1798 ,cp.CONTACT_POINT_PURPOSE
1799 FROM HZ_DQM_STAGE_GT g,HZ_CONTACT_POINTS cp
1800 WHERE cp.owner_table_id = g.owner_id
1801 AND cp.OWNER_TABLE_NAME = nvl(g.owner_table,'HZ_PARTIES')
1802 AND (cp.status is null OR cp.status = 'A' or cp.status = 'I');
1803
1804 BEGIN
1805 OPEN contact_pt_cur;
1806 LOOP
1807 FETCH contact_pt_cur BULK COLLECT INTO
1808 H_CONTACT_POINT_ID
1809 ,H_CPT_PARTY_ID
1810 ,H_CPT_PARTY_SITE_ID
1811 ,H_CPT_ORG_CONTACT_ID
1812 ,H_CONTACT_POINT_TYPE
1813 ,H_PARTY_INDEX
1814 ,H_STATUS
1815 ,H_TX1
1816 ,H_TX2
1817 ,H_TX3
1818 ,H_TX4
1819 ,H_TX5
1820 ,H_TX7
1821 ,H_TX9
1822 ,H_TX10
1823 ,H_TX11
1824 ,H_TX12
1825 ,H_TX13
1826 LIMIT l_limit;
1827
1828 IF contact_pt_cur%NOTFOUND THEN
1829 l_last_fetch:=TRUE;
1830 END IF;
1831 IF H_CPT_PARTY_ID.COUNT=0 AND l_last_fetch THEN
1832 EXIT;
1833 END IF;
1834 FOR I in H_CPT_PARTY_ID.FIRST..H_CPT_PARTY_ID.LAST LOOP
1835
1836 H_TX14(I):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_CONTACT_POINT_ID(I),'CONTACT_POINTS','CPT_SOURCE_SYSTEM_REF', 'STAGE');
1837 H_TX6(I):=HZ_TRANS_PKG.CLEANSED_EMAIL(H_TX5(I),NULL, 'EMAIL_ADDRESS','CONTACT_POINTS','STAGE');
1838 H_TX8(I):=HZ_TRANS_PKG.CLEANSED_URL(H_TX7(I),NULL, 'URL','CONTACT_POINTS','STAGE');
1839 H_TX158(I):=HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(H_TX10(I),NULL, 'RAW_PHONE_NUMBER','CONTACT_POINTS');
1840 H_TX1(I):=HZ_TRANS_PKG.RM_SPLCHAR_CTX(H_TX1(I),NULL, 'FLEX_FORMAT_PHONE_NUMBER','CONTACT_POINTS','STAGE');
1841 H_TX2(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX2(I),NULL, 'PHONE_NUMBER','CONTACT_POINTS','STAGE');
1842 H_TX3(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX3(I),NULL, 'PHONE_AREA_CODE','CONTACT_POINTS','STAGE');
1843 H_TX4(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX4(I),NULL, 'PHONE_COUNTRY_CODE','CONTACT_POINTS','STAGE');
1844 H_TX5(I):=HZ_TRANS_PKG.EXACT_EMAIL(H_TX5(I),NULL, 'EMAIL_ADDRESS','CONTACT_POINTS');
1845 H_TX7(I):=HZ_TRANS_PKG.EXACT_URL(H_TX7(I),NULL, 'URL','CONTACT_POINTS');
1846 H_TX9(I):=HZ_TRANS_PKG.EXACT(H_TX9(I),NULL, 'PRIMARY_FLAG','CONTACT_POINTS');
1847 H_TX10(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX10(I),NULL, 'RAW_PHONE_NUMBER','CONTACT_POINTS','STAGE');
1848 H_TX11(I):=HZ_TRANS_PKG.EXACT(H_TX11(I),NULL, 'PHONE_LINE_TYPE','CONTACT_POINTS');
1849 H_TX12(I):=HZ_TRANS_PKG.EXACT(H_TX12(I),NULL, 'STATUS','CONTACT_POINTS');
1850 H_TX13(I):=HZ_TRANS_PKG.EXACT(H_TX13(I),NULL, 'CONTACT_POINT_PURPOSE','CONTACT_POINTS');
1851 H_TX14(I):=HZ_TRANS_PKG.EXACT(H_TX14(I),NULL, 'CPT_SOURCE_SYSTEM_REF','CONTACT_POINTS');
1852 BEGIN
1853 l_denorm := H_TX3(I)
1854 || ' ' || H_TX4(I)
1855 ;
1856 IF H_CPT_DEN(H_PARTY_INDEX(I)) = 'SYNC' THEN
1857 NULL;
1858 ELSIF lengthb(H_CPT_DEN(H_PARTY_INDEX(I)))+lengthb(l_denorm)<2000 THEN
1859 IF H_CPT_DEN(H_PARTY_INDEX(I)) IS NULL OR instrb(H_CPT_DEN(H_PARTY_INDEX(I)),l_denorm)= 0 THEN
1860 H_CPT_DEN(H_PARTY_INDEX(I)) := H_CPT_DEN(H_PARTY_INDEX(I)) || ' ' || l_denorm;
1861 END IF;
1862 ELSE
1863 H_CPT_DEN(H_PARTY_INDEX(I)) := 'SYNC';
1864 END IF;
1865 EXCEPTION WHEN OTHERS THEN
1866 IF SQLCODE=-6502 THEN
1867 H_CPT_DEN(H_PARTY_INDEX(I)) := 'SYNC';
1868 END IF;
1869 END;
1870 END LOOP;
1871 l_st := 1;
1872 l_en := H_CPT_PARTY_ID.COUNT;
1873 LOOP
1874 BEGIN
1875 FORALL I in l_st..l_en
1876 INSERT INTO HZ_STAGED_CONTACT_POINTS (
1877 CONTACT_POINT_ID
1878 ,PARTY_ID
1879 ,PARTY_SITE_ID
1880 ,ORG_CONTACT_ID
1881 ,CONTACT_POINT_TYPE
1882 ,STATUS_FLAG
1883 , TX1
1884 , TX2
1885 , TX3
1886 , TX4
1887 , TX5
1888 , TX6
1889 , TX7
1890 , TX8
1891 , TX9
1892 , TX10
1893 , TX11
1894 , TX12
1895 , TX13
1896 , TX14
1897 , TX158
1898 ) VALUES (
1899 H_CONTACT_POINT_ID(I)
1900 ,H_CPT_PARTY_ID(I)
1901 ,H_CPT_PARTY_SITE_ID(I)
1902 ,H_CPT_ORG_CONTACT_ID(I)
1903 ,H_CONTACT_POINT_TYPE(I)
1904 ,H_STATUS(I)
1905 , decode(H_TX1(I),null,H_TX1(I),H_TX1(I)||' ')
1906 , decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
1907 , decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
1908 , decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
1909 , decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
1910 , decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
1911 , decode(H_TX7(I),null,H_TX7(I),H_TX7(I)||' ')
1912 , decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
1913 , decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
1914 , decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
1915 , decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
1916 , decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
1917 , decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
1918 , decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
1919 , decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
1920 );
1921 EXIT;
1922 EXCEPTION WHEN OTHERS THEN
1923 l_st:= l_st+SQL%ROWCOUNT+1;
1924 END;
1925 END LOOP;
1926 IF l_last_fetch THEN
1927 EXIT;
1928 END IF;
1929 END LOOP;
1930 CLOSE contact_pt_cur;
1931 END;
1932
1933 PROCEDURE sync_single_contact_point (
1934 p_contact_point_id NUMBER,
1935 p_operation VARCHAR2) IS
1936
1937 l_tryins BOOLEAN;
1938 l_tryupd BOOLEAN;
1939 BEGIN
1940 SELECT cp.CONTACT_POINT_ID, d.PARTY_ID, d.PARTY_SITE_ID, d.ORG_CONTACT_ID, cp.CONTACT_POINT_TYPE, cp.STATUS
1941 ,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
1942 ,cp.PHONE_NUMBER
1943 ,cp.PHONE_AREA_CODE
1944 ,cp.PHONE_COUNTRY_CODE
1945 ,cp.EMAIL_ADDRESS
1946 ,cp.URL
1947 ,cp.PRIMARY_FLAG
1948 ,translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
1949 ,cp.PHONE_LINE_TYPE
1950 ,cp.STATUS
1951 ,cp.CONTACT_POINT_PURPOSE
1952 INTO H_CONTACT_POINT_ID(1),H_PARTY_ID(1), H_PARTY_SITE_ID(1),H_ORG_CONTACT_ID(1),H_CONTACT_POINT_TYPE(1), H_STATUS(1)
1953 , H_TX1(1)
1954 , H_TX2(1)
1955 , H_TX3(1)
1956 , H_TX4(1)
1957 , H_TX5(1)
1958 , H_TX7(1)
1959 , H_TX9(1)
1960 , H_TX10(1)
1961 , H_TX11(1)
1962 , H_TX12(1)
1963 , H_TX13(1)
1964 FROM HZ_CONTACT_POINTS cp, HZ_DQM_SYNC_INTERFACE d
1965 WHERE d.ENTITY = 'CONTACT_POINTS'
1966 AND cp.contact_point_id = p_contact_point_id
1967 AND cp.contact_point_id = d.RECORD_ID
1968 AND (cp.status is null OR cp.status = 'A' or cp.status = 'I') and rownum = 1 ;
1969 H_TX14(1):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_CONTACT_POINT_ID(1),'CONTACT_POINTS','CPT_SOURCE_SYSTEM_REF', 'STAGE');
1970 H_TX6(1):=HZ_TRANS_PKG.CLEANSED_EMAIL(H_TX5(1),NULL, 'EMAIL_ADDRESS','CONTACT_POINTS','STAGE');
1971 H_TX8(1):=HZ_TRANS_PKG.CLEANSED_URL(H_TX7(1),NULL, 'URL','CONTACT_POINTS','STAGE');
1972 H_TX158(1):=HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(H_TX10(1),NULL, 'RAW_PHONE_NUMBER','CONTACT_POINTS');
1973 H_TX1(1):=HZ_TRANS_PKG.RM_SPLCHAR_CTX(H_TX1(1),NULL, 'FLEX_FORMAT_PHONE_NUMBER','CONTACT_POINTS','STAGE');
1974 H_TX2(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX2(1),NULL, 'PHONE_NUMBER','CONTACT_POINTS','STAGE');
1975 H_TX3(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX3(1),NULL, 'PHONE_AREA_CODE','CONTACT_POINTS','STAGE');
1976 H_TX4(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX4(1),NULL, 'PHONE_COUNTRY_CODE','CONTACT_POINTS','STAGE');
1977 H_TX5(1):=HZ_TRANS_PKG.EXACT_EMAIL(H_TX5(1),NULL, 'EMAIL_ADDRESS','CONTACT_POINTS');
1978 H_TX7(1):=HZ_TRANS_PKG.EXACT_URL(H_TX7(1),NULL, 'URL','CONTACT_POINTS');
1979 H_TX9(1):=HZ_TRANS_PKG.EXACT(H_TX9(1),NULL, 'PRIMARY_FLAG','CONTACT_POINTS');
1980 H_TX10(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX10(1),NULL, 'RAW_PHONE_NUMBER','CONTACT_POINTS','STAGE');
1981 H_TX11(1):=HZ_TRANS_PKG.EXACT(H_TX11(1),NULL, 'PHONE_LINE_TYPE','CONTACT_POINTS');
1982 H_TX12(1):=HZ_TRANS_PKG.EXACT(H_TX12(1),NULL, 'STATUS','CONTACT_POINTS');
1983 H_TX13(1):=HZ_TRANS_PKG.EXACT(H_TX13(1),NULL, 'CONTACT_POINT_PURPOSE','CONTACT_POINTS');
1984 H_TX14(1):=HZ_TRANS_PKG.EXACT(H_TX14(1),NULL, 'CPT_SOURCE_SYSTEM_REF','CONTACT_POINTS');
1985 l_tryins := FALSE;
1986 l_tryupd := FALSE;
1987 IF p_operation='C' THEN
1988 l_tryins:=TRUE;
1989 ELSE
1990 l_tryupd:=TRUE;
1991 END IF;
1992 WHILE (l_tryins OR l_tryupd) LOOP
1993 IF l_tryins THEN
1994 BEGIN
1995 l_tryins:=FALSE;
1996 INSERT INTO HZ_STAGED_CONTACT_POINTS (
1997 CONTACT_POINT_ID
1998 ,PARTY_ID
1999 ,PARTY_SITE_ID
2000 ,ORG_CONTACT_ID
2001 ,CONTACT_POINT_TYPE
2002 ,STATUS_FLAG
2003 , TX1
2004 , TX2
2005 , TX3
2006 , TX4
2007 , TX5
2008 , TX6
2009 , TX7
2010 , TX8
2011 , TX9
2012 , TX10
2013 , TX11
2014 , TX12
2015 , TX13
2016 , TX14
2017 , TX158
2018 ) VALUES (
2019 H_CONTACT_POINT_ID(1)
2020 ,H_PARTY_ID(1)
2021 ,H_PARTY_SITE_ID(1)
2022 ,H_ORG_CONTACT_ID(1)
2023 ,H_CONTACT_POINT_TYPE(1)
2024 ,H_STATUS(1)
2025 , decode(H_TX1(1),null,H_TX1(1),H_TX1(1)||' ')
2026 , decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
2027 , decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
2028 , decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
2029 , decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
2030 , decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
2031 , decode(H_TX7(1),null,H_TX7(1),H_TX7(1)||' ')
2032 , decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
2033 , decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
2034 , decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
2035 , decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
2036 , decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
2037 , decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
2038 , decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
2039 , decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
2040 );
2041 EXCEPTION
2042 WHEN DUP_VAL_ON_INDEX THEN
2043 IF p_operation='C' THEN
2044 l_tryupd:=TRUE;
2045 END IF;
2046 END;
2047 END IF;
2048 IF l_tryupd THEN
2049 BEGIN
2050 l_tryupd:=FALSE;
2051 UPDATE HZ_STAGED_CONTACT_POINTS SET
2052 concat_col = concat_col
2053 ,status_flag = H_STATUS(1)
2054 ,TX1=decode(H_TX1(1),null,H_TX1(1),H_TX1(1)||' ')
2055 ,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
2056 ,TX3=decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
2057 ,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
2058 ,TX5=decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
2059 ,TX6=decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
2060 ,TX7=decode(H_TX7(1),null,H_TX7(1),H_TX7(1)||' ')
2061 ,TX8=decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
2062 ,TX9=decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
2063 ,TX10=decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
2064 ,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
2065 ,TX12=decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
2066 ,TX13=decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
2067 ,TX14=decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
2068 ,TX158=decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
2069 WHERE CONTACT_POINT_ID=H_CONTACT_POINT_ID(1);
2070 IF SQL%ROWCOUNT=0 AND p_operation='U' THEN
2071 l_tryins := TRUE;
2072 END IF;
2073 EXCEPTION
2074 WHEN NO_DATA_FOUND THEN
2075 IF p_operation='U' THEN
2076 l_tryins := TRUE;
2077 END IF;
2078 END;
2079 END IF;
2080 END LOOP;
2081 --Fix for bug 5048604, to update concat_col during update of denorm column
2082 UPDATE HZ_STAGED_PARTIES set
2083 D_CPT = 'SYNC'
2084 ,CONCAT_COL = CONCAT_COL
2085 WHERE PARTY_ID = H_PARTY_ID(1);
2086 END;
2087
2088 PROCEDURE sync_single_cpt_online (
2089 p_contact_point_id NUMBER,
2090 p_operation VARCHAR2) IS
2091
2092 l_tryins BOOLEAN;
2093 l_tryupd BOOLEAN;
2094 l_party_id NUMBER := 0;
2095 l_party_id1 NUMBER;
2096 l_org_contact_id NUMBER;
2097 l_party_site_id NUMBER;
2098 l_pr_id NUMBER;
2099 l_num_ocs NUMBER;
2100 l_ot_id NUMBER;
2101 l_ot_table VARCHAR2(60);
2102 l_party_type VARCHAR2(60);
2103 l_sql_err_message VARCHAR2(2000);
2104
2105 BEGIN
2106
2107 l_org_contact_id := -1;
2108 l_party_site_id := -1;
2109
2110 SELECT owner_table_name,owner_table_id INTO l_ot_table, l_ot_id
2111 FROM hz_contact_points
2112 WHERE contact_point_id = p_contact_point_id;
2113
2114 IF l_ot_table = 'HZ_PARTY_SITES' THEN
2115 SELECT p.party_id, ps.party_site_id, party_type
2116 INTO l_party_id1, l_party_site_id, l_party_type
2117 FROM HZ_PARTY_SITES ps, HZ_PARTIES p
2118 WHERE party_site_id = l_ot_id
2119 AND p.party_id = ps.party_id;
2120
2121 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
2122 BEGIN
2123 SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
2124 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
2125 WHERE r.party_id = l_party_id1
2126 AND r.relationship_id = oc.party_relationship_id
2127 AND r.directional_flag = 'F'
2128 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2129 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
2130 EXCEPTION
2131 WHEN NO_DATA_FOUND THEN
2132 RETURN;
2133 END;
2134 ELSE
2135 l_party_id:=l_party_id1;
2136 l_org_contact_id:=NULL;
2137 END IF;
2138
2139 ELSIF l_ot_table = 'HZ_PARTIES' THEN
2140 l_party_site_id := NULL;
2141 SELECT party_type INTO l_party_type
2142 FROM hz_parties
2143 WHERE party_id = l_ot_id;
2144
2145 IF l_party_type <> 'PARTY_RELATIONSHIP' THEN
2146 l_party_id := l_ot_id;
2147 l_org_contact_id:=NULL;
2148 ELSE
2149 BEGIN
2150 SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
2151 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
2152 WHERE r.party_id = l_ot_id
2153 AND r.relationship_id = oc.party_relationship_id
2154 AND r.directional_flag = 'F'
2155 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2156 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
2157 EXCEPTION
2158 WHEN NO_DATA_FOUND THEN
2159 RETURN;
2160 END;
2161 END IF;
2162 END IF;
2163
2164 SELECT cp.CONTACT_POINT_ID, l_party_id, l_party_site_id, l_org_contact_id, cp.CONTACT_POINT_TYPE, cp.STATUS
2165 ,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
2166 ,cp.PHONE_NUMBER
2167 ,cp.PHONE_AREA_CODE
2168 ,cp.PHONE_COUNTRY_CODE
2169 ,cp.EMAIL_ADDRESS
2170 ,cp.URL
2171 ,cp.PRIMARY_FLAG
2172 ,translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
2173 ,cp.PHONE_LINE_TYPE
2174 ,cp.STATUS
2175 ,cp.CONTACT_POINT_PURPOSE
2176 INTO H_CONTACT_POINT_ID(1),H_PARTY_ID(1), H_PARTY_SITE_ID(1),H_ORG_CONTACT_ID(1),H_CONTACT_POINT_TYPE(1), H_STATUS(1)
2177 ,H_TX1(1)
2178 ,H_TX2(1)
2179 ,H_TX3(1)
2180 ,H_TX4(1)
2181 ,H_TX5(1)
2182 ,H_TX7(1)
2183 ,H_TX9(1)
2184 ,H_TX10(1)
2185 ,H_TX11(1)
2186 ,H_TX12(1)
2187 ,H_TX13(1)
2188 FROM HZ_CONTACT_POINTS cp
2189 WHERE
2190 cp.contact_point_id = p_contact_point_id
2191 AND (cp.status is null OR cp.status = 'A' or cp.status = 'I') and rownum = 1 ;
2192
2193 H_TX14(1):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_CONTACT_POINT_ID(1),'CONTACT_POINTS','CPT_SOURCE_SYSTEM_REF', 'STAGE');
2194 H_TX6(1):=HZ_TRANS_PKG.CLEANSED_EMAIL(H_TX5(1),NULL, 'EMAIL_ADDRESS','CONTACT_POINTS','STAGE');
2195 H_TX8(1):=HZ_TRANS_PKG.CLEANSED_URL(H_TX7(1),NULL, 'URL','CONTACT_POINTS','STAGE');
2196 H_TX158(1):=HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(H_TX10(1),NULL, 'RAW_PHONE_NUMBER','CONTACT_POINTS');
2197 H_TX1(1):=HZ_TRANS_PKG.RM_SPLCHAR_CTX(H_TX1(1),NULL, 'FLEX_FORMAT_PHONE_NUMBER','CONTACT_POINTS','STAGE');
2198 H_TX2(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX2(1),NULL, 'PHONE_NUMBER','CONTACT_POINTS','STAGE');
2199 H_TX3(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX3(1),NULL, 'PHONE_AREA_CODE','CONTACT_POINTS','STAGE');
2200 H_TX4(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX4(1),NULL, 'PHONE_COUNTRY_CODE','CONTACT_POINTS','STAGE');
2201 H_TX5(1):=HZ_TRANS_PKG.EXACT_EMAIL(H_TX5(1),NULL, 'EMAIL_ADDRESS','CONTACT_POINTS');
2202 H_TX7(1):=HZ_TRANS_PKG.EXACT_URL(H_TX7(1),NULL, 'URL','CONTACT_POINTS');
2203 H_TX9(1):=HZ_TRANS_PKG.EXACT(H_TX9(1),NULL, 'PRIMARY_FLAG','CONTACT_POINTS');
2204 H_TX10(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX10(1),NULL, 'RAW_PHONE_NUMBER','CONTACT_POINTS','STAGE');
2205 H_TX11(1):=HZ_TRANS_PKG.EXACT(H_TX11(1),NULL, 'PHONE_LINE_TYPE','CONTACT_POINTS');
2206 H_TX12(1):=HZ_TRANS_PKG.EXACT(H_TX12(1),NULL, 'STATUS','CONTACT_POINTS');
2207 H_TX13(1):=HZ_TRANS_PKG.EXACT(H_TX13(1),NULL, 'CONTACT_POINT_PURPOSE','CONTACT_POINTS');
2208 H_TX14(1):=HZ_TRANS_PKG.EXACT(H_TX14(1),NULL, 'CPT_SOURCE_SYSTEM_REF','CONTACT_POINTS');
2209
2210 l_tryins := FALSE;
2211 l_tryupd := FALSE;
2212
2213 IF p_operation='C' THEN
2214 l_tryins:=TRUE;
2215 ELSE
2216 l_tryupd:=TRUE;
2217 END IF;
2218
2219 WHILE (l_tryins OR l_tryupd) LOOP
2220 IF l_tryins THEN
2221 BEGIN
2222 l_tryins:=FALSE;
2223 INSERT INTO HZ_STAGED_CONTACT_POINTS (
2224 CONTACT_POINT_ID
2225 ,PARTY_ID
2226 ,PARTY_SITE_ID
2227 ,ORG_CONTACT_ID
2228 ,CONTACT_POINT_TYPE
2229 ,STATUS_FLAG
2230 ,TX1
2231 ,TX2
2232 ,TX3
2233 ,TX4
2234 ,TX5
2235 ,TX6
2236 ,TX7
2237 ,TX8
2238 ,TX9
2239 ,TX10
2240 ,TX11
2241 ,TX12
2242 ,TX13
2243 ,TX14
2244 ,TX158
2245 ) VALUES (
2246 H_CONTACT_POINT_ID(1)
2247 ,H_PARTY_ID(1)
2248 ,H_PARTY_SITE_ID(1)
2249 ,H_ORG_CONTACT_ID(1)
2250 ,H_CONTACT_POINT_TYPE(1)
2251 ,H_STATUS(1)
2252 ,decode(H_TX1(1),null,H_TX1(1),H_TX1(1)||' ')
2253 ,decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
2254 ,decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
2255 ,decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
2256 ,decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
2257 ,decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
2258 ,decode(H_TX7(1),null,H_TX7(1),H_TX7(1)||' ')
2259 ,decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
2260 ,decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
2261 ,decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
2262 ,decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
2263 ,decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
2264 ,decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
2265 ,decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
2266 ,decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
2267 );
2268 EXCEPTION
2269 WHEN DUP_VAL_ON_INDEX THEN
2270 IF p_operation='C' THEN
2271 l_tryupd:=TRUE;
2272 END IF;
2273 END;
2274 END IF;
2275
2276 IF l_tryupd THEN
2277 BEGIN
2278 l_tryupd:=FALSE;
2279 UPDATE HZ_STAGED_CONTACT_POINTS SET
2280 concat_col = concat_col
2281 ,status_flag = H_STATUS(1)
2282 ,TX1=decode(H_TX1(1),null,H_TX1(1),H_TX1(1)||' ')
2283 ,TX2=decode(H_TX2(1),null,H_TX2(1),H_TX2(1)||' ')
2284 ,TX3=decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
2285 ,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
2286 ,TX5=decode(H_TX5(1),null,H_TX5(1),H_TX5(1)||' ')
2287 ,TX6=decode(H_TX6(1),null,H_TX6(1),H_TX6(1)||' ')
2288 ,TX7=decode(H_TX7(1),null,H_TX7(1),H_TX7(1)||' ')
2289 ,TX8=decode(H_TX8(1),null,H_TX8(1),H_TX8(1)||' ')
2290 ,TX9=decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
2291 ,TX10=decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
2292 ,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
2293 ,TX12=decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
2294 ,TX13=decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
2295 ,TX14=decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
2296 ,TX158=decode(H_TX158(1),null,H_TX158(1),H_TX158(1)||' ')
2297 WHERE CONTACT_POINT_ID=H_CONTACT_POINT_ID(1);
2298 IF SQL%ROWCOUNT=0 AND p_operation='U' THEN
2299 l_tryins := TRUE;
2300 END IF;
2301 EXCEPTION
2302 WHEN NO_DATA_FOUND THEN
2303 IF p_operation='U' THEN
2304 l_tryins := TRUE;
2305 END IF;
2306 END;
2307 END IF;
2308 END LOOP;
2309
2310 --Fix for bug 5048604, to update concat_col during update of denorm column
2311 UPDATE HZ_STAGED_PARTIES set
2312 D_CPT = 'SYNC'
2313 ,CONCAT_COL = CONCAT_COL
2314 WHERE PARTY_ID = H_PARTY_ID(1);
2315
2316 -- REPURI. Bug 4884742. If shadow staging is completely successfully
2317 -- insert a record into hz_dqm_sh_sync_interface table for each record
2318 IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2319 BEGIN
2320 HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_operation);
2321 EXCEPTION WHEN OTHERS THEN
2322 NULL;
2323 END;
2324 END IF;
2325
2326 EXCEPTION WHEN OTHERS THEN
2327 -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
2328 -- FOR ONLINE FLOWS
2329 l_sql_err_message := SQLERRM;
2330 insert_dqm_sync_error_rec(l_party_id, p_contact_point_id, l_party_site_id, l_org_contact_id, 'CONTACT_POINTS', p_operation, 'E', 'Y', l_sql_err_message);
2331 END;
2332
2333 PROCEDURE insert_stage_party_sites IS
2334 l_limit NUMBER := 200;
2335 l_last_fetch BOOLEAN := FALSE;
2336 l_denorm VARCHAR2(2000);
2337 l_st number;
2338 l_en number;
2339
2340 CURSOR party_site_cur IS
2341 SELECT /*+ ORDERED USE_NL(ps l) */ ps.PARTY_SITE_ID, g.party_id, g.org_contact_id, g.PARTY_INDEX, ps.status
2342 ,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
2343 ,l.CITY
2344 ,l.POSTAL_CODE
2345 ,l.PROVINCE
2346 ,l.STATE
2347 ,ps.PARTY_SITE_NUMBER
2348 ,ps.PARTY_SITE_NAME
2349 ,l.COUNTY
2350 ,l.COUNTRY
2351 ,ps.IDENTIFYING_ADDRESS_FLAG
2352 ,ps.STATUS
2353 ,l.ADDRESS1
2354 FROM HZ_DQM_STAGE_GT g, HZ_PARTY_SITES ps, HZ_LOCATIONS l
2355 WHERE ps.PARTY_ID = g.owner_id
2356 AND (ps.status is null OR ps.status = 'A' OR ps.status = 'I')
2357 AND ps.location_id = l.location_id;
2358 BEGIN
2359 OPEN party_site_cur;
2360 LOOP
2361 FETCH party_site_cur BULK COLLECT INTO
2362 H_PARTY_SITE_ID
2363 ,H_PS_PARTY_ID
2364 ,H_PS_ORG_CONTACT_ID
2365 ,H_PARTY_INDEX
2366 ,H_STATUS
2367 ,H_TX3
2368 ,H_TX9
2369 ,H_TX11
2370 ,H_TX12
2371 ,H_TX14
2372 ,H_TX17
2373 ,H_TX18
2374 ,H_TX20
2375 ,H_TX22
2376 ,H_TX24
2377 ,H_TX25
2378 ,H_TX28
2379 LIMIT l_limit;
2380
2381 IF party_site_cur%NOTFOUND THEN
2382 l_last_fetch:=TRUE;
2383 END IF;
2384 IF H_PS_PARTY_ID.COUNT=0 AND l_last_fetch THEN
2385 EXIT;
2386 END IF;
2387 FOR I in H_PS_PARTY_ID.FIRST..H_PS_PARTY_ID.LAST LOOP
2388 ----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY SITE LEVEL ---------
2389
2390 HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (36,H_TX22(I));
2391 H_TX30(I):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_PARTY_SITE_ID(I),'PARTY_SITES','ADDR_SOURCE_SYSTEM_REF', 'STAGE');
2392 H_TX4(I):=HZ_TRANS_PKG.WRADDRESS_CLEANSE(H_TX3(I),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2393 H_TX10(I):=HZ_TRANS_PKG.CLEANSE(H_TX9(I),NULL, 'CITY','PARTY_SITES');
2394 H_TX13(I):=HZ_TRANS_PKG.CLEANSE(H_TX12(I),NULL, 'PROVINCE','PARTY_SITES');
2395 H_TX15(I):=HZ_TRANS_PKG.WRSTATE_CLEANSE(H_TX14(I),NULL, 'STATE','PARTY_SITES','STAGE');
2396 H_TX19(I):=HZ_TRANS_PKG.CLEANSE(H_TX18(I),NULL, 'PARTY_SITE_NAME','PARTY_SITES');
2397 H_TX21(I):=HZ_TRANS_PKG.CLEANSE(H_TX20(I),NULL, 'COUNTY','PARTY_SITES');
2398 H_TX26(I):=HZ_TRANS_PKG.BASIC_WRADDR(H_TX3(I),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2399 H_TX27(I):=HZ_TRANS_PKG.BASIC_CLEANSE_WRADDR(H_TX3(I),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2400 H_TX29(I):=HZ_TRANS_PKG.BASIC_CLEANSE_WRADDR(H_TX28(I),NULL, 'ADDRESS1','PARTY_SITES','STAGE');
2401 H_TX3(I):=HZ_TRANS_PKG.WRADDRESS_EXACT(H_TX3(I),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2402 H_TX9(I):=HZ_TRANS_PKG.EXACT(H_TX9(I),NULL, 'CITY','PARTY_SITES');
2403 H_TX11(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(I),NULL, 'POSTAL_CODE','PARTY_SITES','STAGE');
2404 H_TX12(I):=HZ_TRANS_PKG.EXACT(H_TX12(I),NULL, 'PROVINCE','PARTY_SITES');
2405 H_TX14(I):=HZ_TRANS_PKG.WRSTATE_EXACT(H_TX14(I),NULL, 'STATE','PARTY_SITES','STAGE');
2406 H_TX17(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX17(I),NULL, 'PARTY_SITE_NUMBER','PARTY_SITES','STAGE');
2407 H_TX18(I):=HZ_TRANS_PKG.EXACT(H_TX18(I),NULL, 'PARTY_SITE_NAME','PARTY_SITES');
2408 H_TX20(I):=HZ_TRANS_PKG.EXACT(H_TX20(I),NULL, 'COUNTY','PARTY_SITES');
2409 H_TX22(I):=HZ_TRANS_PKG.EXACT(H_TX22(I),NULL, 'COUNTRY','PARTY_SITES');
2410 H_TX24(I):=HZ_TRANS_PKG.EXACT(H_TX24(I),NULL, 'IDENTIFYING_ADDRESS_FLAG','PARTY_SITES');
2411 H_TX25(I):=HZ_TRANS_PKG.EXACT(H_TX25(I),NULL, 'STATUS','PARTY_SITES');
2412 H_TX28(I):=HZ_TRANS_PKG.BASIC_WRADDR(H_TX28(I),NULL, 'ADDRESS1','PARTY_SITES','STAGE');
2413 H_TX30(I):=HZ_TRANS_PKG.EXACT(H_TX30(I),NULL, 'ADDR_SOURCE_SYSTEM_REF','PARTY_SITES');
2414 BEGIN
2415 l_denorm := H_TX9(I)
2416 || ' ' || H_TX10(I)
2417 || ' ' || H_TX11(I)
2418 || ' ' || H_TX12(I)
2419 || ' ' || H_TX13(I)
2420 || ' ' || H_TX14(I)
2421 || ' ' || H_TX15(I)
2422 || ' ' || H_TX20(I)
2423 || ' ' || H_TX21(I)
2424 || ' ' || H_TX22(I)
2425 ;
2426 IF H_PS_DEN(H_PARTY_INDEX(I)) = 'SYNC' THEN
2427 NULL;
2428 ELSIF lengthb(H_PS_DEN(H_PARTY_INDEX(I)))+lengthb(l_denorm)<2000 THEN
2429 IF H_PS_DEN(H_PARTY_INDEX(I)) IS NULL OR instrb(H_PS_DEN(H_PARTY_INDEX(I)),l_denorm)=0 THEN
2430 H_PS_DEN(H_PARTY_INDEX(I)) := H_PS_DEN(H_PARTY_INDEX(I)) || ' ' || l_denorm;
2431 END IF;
2432 ELSE
2433 H_PS_DEN(H_PARTY_INDEX(I)) := 'SYNC';
2434 END IF;
2435 EXCEPTION WHEN OTHERS THEN
2436 IF SQLCODE=-6502 THEN
2437 H_PS_DEN(H_PARTY_INDEX(I)) := 'SYNC';
2438 END IF;
2439 END;
2440 END LOOP;
2441 l_st := 1;
2442 l_en := H_PS_PARTY_ID.COUNT;
2443 LOOP
2444 BEGIN
2445 FORALL I in l_st..l_en
2446 INSERT INTO HZ_STAGED_PARTY_SITES (
2447 PARTY_SITE_ID
2448 ,PARTY_ID
2449 ,ORG_CONTACT_ID
2450 ,STATUS_FLAG
2451 , TX3
2452 , TX4
2453 , TX9
2454 , TX10
2455 , TX11
2456 , TX12
2457 , TX13
2458 , TX14
2459 , TX15
2460 , TX17
2461 , TX18
2462 , TX19
2463 , TX20
2464 , TX21
2465 , TX22
2466 , TX24
2467 , TX25
2468 , TX26
2469 , TX27
2470 , TX28
2471 , TX29
2472 , TX30
2473 ) VALUES (
2474 H_PARTY_SITE_ID(I)
2475 ,H_PS_PARTY_ID(I)
2476 ,H_PS_ORG_CONTACT_ID(I)
2477 ,H_STATUS(I)
2478 , decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
2479 , decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
2480 , decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
2481 , decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
2482 , decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
2483 , decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
2484 , decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
2485 , decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
2486 , decode(H_TX15(I),null,H_TX15(I),H_TX15(I)||' ')
2487 , decode(H_TX17(I),null,H_TX17(I),H_TX17(I)||' ')
2488 , decode(H_TX18(I),null,H_TX18(I),H_TX18(I)||' ')
2489 , decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
2490 , decode(H_TX20(I),null,H_TX20(I),H_TX20(I)||' ')
2491 , decode(H_TX21(I),null,H_TX21(I),H_TX21(I)||' ')
2492 , decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
2493 , decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
2494 , decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
2495 , decode(H_TX26(I),null,H_TX26(I),H_TX26(I)||' ')
2496 , decode(H_TX27(I),null,H_TX27(I),H_TX27(I)||' ')
2497 , decode(H_TX28(I),null,H_TX28(I),H_TX28(I)||' ')
2498 , decode(H_TX29(I),null,H_TX29(I),H_TX29(I)||' ')
2499 , decode(H_TX30(I),null,H_TX30(I),H_TX30(I)||' ')
2500 );
2501 EXIT;
2502 EXCEPTION WHEN OTHERS THEN
2503 l_st:= l_st+SQL%ROWCOUNT+1;
2504 END;
2505 END LOOP;
2506 FORALL I in H_PS_PARTY_ID.FIRST..H_PS_PARTY_ID.LAST
2507 INSERT INTO HZ_DQM_STAGE_GT (PARTY_ID, OWNER_ID, OWNER_TABLE, PARTY_SITE_ID,
2508 ORG_CONTACT_ID,PARTY_INDEX) VALUES (
2509 H_PS_PARTY_ID(I),H_PARTY_SITE_ID(I),'HZ_PARTY_SITES',H_PARTY_SITE_ID(I),
2510 H_PS_ORG_CONTACT_ID(I),H_PARTY_INDEX(I));
2511 IF l_last_fetch THEN
2512 EXIT;
2513 END IF;
2514 END LOOP;
2515 CLOSE party_site_cur;
2516 END;
2517
2518 PROCEDURE sync_single_party_site (
2519 p_party_site_id NUMBER,
2520 p_operation VARCHAR2) IS
2521
2522 l_tryins BOOLEAN;
2523 l_tryupd BOOLEAN;
2524 BEGIN
2525 SELECT ps.PARTY_SITE_ID, d.party_id, d.org_contact_id, ps.STATUS
2526 ,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
2527 ,l.CITY
2528 ,l.POSTAL_CODE
2529 ,l.PROVINCE
2530 ,l.STATE
2531 ,ps.PARTY_SITE_NUMBER
2532 ,ps.PARTY_SITE_NAME
2533 ,l.COUNTY
2534 ,l.COUNTRY
2535 ,ps.IDENTIFYING_ADDRESS_FLAG
2536 ,ps.STATUS
2537 ,l.ADDRESS1
2538 INTO H_PARTY_SITE_ID(1), H_PARTY_ID(1), H_ORG_CONTACT_ID(1), H_STATUS(1)
2539 , H_TX3(1)
2540 , H_TX9(1)
2541 , H_TX11(1)
2542 , H_TX12(1)
2543 , H_TX14(1)
2544 , H_TX17(1)
2545 , H_TX18(1)
2546 , H_TX20(1)
2547 , H_TX22(1)
2548 , H_TX24(1)
2549 , H_TX25(1)
2550 , H_TX28(1)
2551 FROM HZ_PARTY_SITES ps, HZ_DQM_SYNC_INTERFACE d, HZ_LOCATIONS l
2552 WHERE d.ENTITY='PARTY_SITES'
2553 AND ps.party_site_id = p_party_site_id
2554 AND d.record_id = ps.party_site_id
2555 AND ps.location_id = l.location_id
2556 AND (ps.status is null OR ps.status = 'A' OR ps.status = 'I')
2557 AND ROWNUM=1;
2558 ----------- SETTING GLOBAL CONDITION RECORD AT THE PARTY SITE LEVEL ---------
2559 HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (36,H_TX22(1));
2560 H_TX30(1):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_PARTY_SITE_ID(1),'PARTY_SITES','ADDR_SOURCE_SYSTEM_REF', 'STAGE');
2561 H_TX4(1):=HZ_TRANS_PKG.WRADDRESS_CLEANSE(H_TX3(1),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2562 H_TX10(1):=HZ_TRANS_PKG.CLEANSE(H_TX9(1),NULL, 'CITY','PARTY_SITES');
2563 H_TX13(1):=HZ_TRANS_PKG.CLEANSE(H_TX12(1),NULL, 'PROVINCE','PARTY_SITES');
2564 H_TX15(1):=HZ_TRANS_PKG.WRSTATE_CLEANSE(H_TX14(1),NULL, 'STATE','PARTY_SITES','STAGE');
2565 H_TX19(1):=HZ_TRANS_PKG.CLEANSE(H_TX18(1),NULL, 'PARTY_SITE_NAME','PARTY_SITES');
2566 H_TX21(1):=HZ_TRANS_PKG.CLEANSE(H_TX20(1),NULL, 'COUNTY','PARTY_SITES');
2567 H_TX26(1):=HZ_TRANS_PKG.BASIC_WRADDR(H_TX3(1),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2568 H_TX27(1):=HZ_TRANS_PKG.BASIC_CLEANSE_WRADDR(H_TX3(1),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2569 H_TX29(1):=HZ_TRANS_PKG.BASIC_CLEANSE_WRADDR(H_TX28(1),NULL, 'ADDRESS1','PARTY_SITES','STAGE');
2570 H_TX3(1):=HZ_TRANS_PKG.WRADDRESS_EXACT(H_TX3(1),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2571 H_TX9(1):=HZ_TRANS_PKG.EXACT(H_TX9(1),NULL, 'CITY','PARTY_SITES');
2572 H_TX11(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(1),NULL, 'POSTAL_CODE','PARTY_SITES','STAGE');
2573 H_TX12(1):=HZ_TRANS_PKG.EXACT(H_TX12(1),NULL, 'PROVINCE','PARTY_SITES');
2574 H_TX14(1):=HZ_TRANS_PKG.WRSTATE_EXACT(H_TX14(1),NULL, 'STATE','PARTY_SITES','STAGE');
2575 H_TX17(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX17(1),NULL, 'PARTY_SITE_NUMBER','PARTY_SITES','STAGE');
2576 H_TX18(1):=HZ_TRANS_PKG.EXACT(H_TX18(1),NULL, 'PARTY_SITE_NAME','PARTY_SITES');
2577 H_TX20(1):=HZ_TRANS_PKG.EXACT(H_TX20(1),NULL, 'COUNTY','PARTY_SITES');
2578 H_TX22(1):=HZ_TRANS_PKG.EXACT(H_TX22(1),NULL, 'COUNTRY','PARTY_SITES');
2579 H_TX24(1):=HZ_TRANS_PKG.EXACT(H_TX24(1),NULL, 'IDENTIFYING_ADDRESS_FLAG','PARTY_SITES');
2580 H_TX25(1):=HZ_TRANS_PKG.EXACT(H_TX25(1),NULL, 'STATUS','PARTY_SITES');
2581 H_TX28(1):=HZ_TRANS_PKG.BASIC_WRADDR(H_TX28(1),NULL, 'ADDRESS1','PARTY_SITES','STAGE');
2582 H_TX30(1):=HZ_TRANS_PKG.EXACT(H_TX30(1),NULL, 'ADDR_SOURCE_SYSTEM_REF','PARTY_SITES');
2583 l_tryins := FALSE;
2584 l_tryupd := FALSE;
2585 IF p_operation='C' THEN
2586 l_tryins:=TRUE;
2587 ELSE
2588 l_tryupd:=TRUE;
2589 END IF;
2590 WHILE (l_tryins OR l_tryupd) LOOP
2591 IF l_tryins THEN
2592 BEGIN
2593 l_tryins:=FALSE;
2594 INSERT INTO HZ_STAGED_PARTY_SITES (
2595 PARTY_SITE_ID
2596 ,PARTY_ID
2597 ,ORG_CONTACT_ID
2598 ,STATUS_FLAG
2599 , TX3
2600 , TX4
2601 , TX9
2602 , TX10
2603 , TX11
2604 , TX12
2605 , TX13
2606 , TX14
2607 , TX15
2608 , TX17
2609 , TX18
2610 , TX19
2611 , TX20
2612 , TX21
2613 , TX22
2614 , TX24
2615 , TX25
2616 , TX26
2617 , TX27
2618 , TX28
2619 , TX29
2620 , TX30
2621 ) VALUES (
2622 H_PARTY_SITE_ID(1)
2623 ,H_PARTY_ID(1)
2624 ,H_ORG_CONTACT_ID(1)
2625 ,H_STATUS(1)
2626 , decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
2627 , decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
2628 , decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
2629 , decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
2630 , decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
2631 , decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
2632 , decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
2633 , decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
2634 , decode(H_TX15(1),null,H_TX15(1),H_TX15(1)||' ')
2635 , decode(H_TX17(1),null,H_TX17(1),H_TX17(1)||' ')
2636 , decode(H_TX18(1),null,H_TX18(1),H_TX18(1)||' ')
2637 , decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
2638 , decode(H_TX20(1),null,H_TX20(1),H_TX20(1)||' ')
2639 , decode(H_TX21(1),null,H_TX21(1),H_TX21(1)||' ')
2640 , decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
2641 , decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
2642 , decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
2643 , decode(H_TX26(1),null,H_TX26(1),H_TX26(1)||' ')
2644 , decode(H_TX27(1),null,H_TX27(1),H_TX27(1)||' ')
2645 , decode(H_TX28(1),null,H_TX28(1),H_TX28(1)||' ')
2646 , decode(H_TX29(1),null,H_TX29(1),H_TX29(1)||' ')
2647 , decode(H_TX30(1),null,H_TX30(1),H_TX30(1)||' ')
2648 );
2649 EXCEPTION
2650 WHEN DUP_VAL_ON_INDEX THEN
2651 IF p_operation='C' THEN
2652 l_tryupd:=TRUE;
2653 END IF;
2654 END;
2655 END IF;
2656 IF l_tryupd THEN
2657 BEGIN
2658 l_tryupd:=FALSE;
2659 UPDATE HZ_STAGED_PARTY_SITES SET
2660 concat_col = concat_col
2661 ,status_flag = H_STATUS(1)
2662 ,TX3=decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
2663 ,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
2664 ,TX9=decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
2665 ,TX10=decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
2666 ,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
2667 ,TX12=decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
2668 ,TX13=decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
2669 ,TX14=decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
2670 ,TX15=decode(H_TX15(1),null,H_TX15(1),H_TX15(1)||' ')
2671 ,TX17=decode(H_TX17(1),null,H_TX17(1),H_TX17(1)||' ')
2672 ,TX18=decode(H_TX18(1),null,H_TX18(1),H_TX18(1)||' ')
2673 ,TX19=decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
2674 ,TX20=decode(H_TX20(1),null,H_TX20(1),H_TX20(1)||' ')
2675 ,TX21=decode(H_TX21(1),null,H_TX21(1),H_TX21(1)||' ')
2676 ,TX22=decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
2677 ,TX24=decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
2678 ,TX25=decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
2679 ,TX26=decode(H_TX26(1),null,H_TX26(1),H_TX26(1)||' ')
2680 ,TX27=decode(H_TX27(1),null,H_TX27(1),H_TX27(1)||' ')
2681 ,TX28=decode(H_TX28(1),null,H_TX28(1),H_TX28(1)||' ')
2682 ,TX29=decode(H_TX29(1),null,H_TX29(1),H_TX29(1)||' ')
2683 ,TX30=decode(H_TX30(1),null,H_TX30(1),H_TX30(1)||' ')
2684 WHERE PARTY_SITE_ID=H_PARTY_SITE_ID(1);
2685 IF SQL%ROWCOUNT=0 AND p_operation='U' THEN
2686 l_tryins := TRUE;
2687 END IF;
2688 EXCEPTION
2689 WHEN NO_DATA_FOUND THEN
2690 IF p_operation='U' THEN
2691 l_tryins := TRUE;
2692 END IF;
2693 END;
2694 END IF;
2695 END LOOP;
2696 --Fix for bug 5048604, to update concat_col during update of denorm column
2697 UPDATE HZ_STAGED_PARTIES set
2698 D_PS = 'SYNC'
2699 ,CONCAT_COL = CONCAT_COL
2700 WHERE PARTY_ID = H_PARTY_ID(1);
2701 END;
2702
2703 PROCEDURE sync_single_party_site_online (
2704 p_party_site_id NUMBER,
2705 p_operation VARCHAR2) IS
2706
2707 l_tryins BOOLEAN;
2708 l_tryupd BOOLEAN;
2709 l_party_id NUMBER;
2710 l_party_id1 NUMBER;
2711 l_org_contact_id NUMBER;
2712 l_party_type VARCHAR2(255);
2713 l_sql_err_message VARCHAR2(2000);
2714
2715 BEGIN
2716
2717 l_party_id := -1;
2718 l_org_contact_id := -1;
2719
2720 BEGIN
2721 SELECT ps.party_id,p.party_type INTO l_party_id1, l_party_type
2722 FROM HZ_PARTY_SITES ps, HZ_PARTIES p
2723 WHERE party_site_id = p_party_site_id
2724 AND p.PARTY_ID = ps.PARTY_ID;
2725 -- take care of invalid party ids
2726 EXCEPTION
2727 WHEN NO_DATA_FOUND THEN
2728 -- dbms_output.put_line ( 'Exception caught in party_site ');
2729 RETURN;
2730 END;
2731
2732 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
2733 BEGIN
2734 SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
2735 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
2736 WHERE r.party_id = l_party_id1
2737 AND r.relationship_id = oc.party_relationship_id
2738 AND r.directional_flag = 'F'
2739 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
2740 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
2741 -- take care of invalid identifiers
2742 EXCEPTION
2743 WHEN NO_DATA_FOUND THEN
2744 -- dbms_output.put_line ( 'Exception caught in party_rel ');
2745 RETURN;
2746 END;
2747 ELSE
2748 l_party_id :=l_party_id1;
2749 l_org_contact_id:=NULL;
2750 END IF;
2751
2752 SELECT ps.PARTY_SITE_ID, l_party_id, l_org_contact_id, ps.STATUS
2753 ,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
2754 ,l.CITY
2755 ,l.POSTAL_CODE
2756 ,l.PROVINCE
2757 ,l.STATE
2758 ,ps.PARTY_SITE_NUMBER
2759 ,ps.PARTY_SITE_NAME
2760 ,l.COUNTY
2761 ,l.COUNTRY
2762 ,ps.IDENTIFYING_ADDRESS_FLAG
2763 ,ps.STATUS
2764 ,l.ADDRESS1
2765 INTO H_PARTY_SITE_ID(1), H_PARTY_ID(1), H_ORG_CONTACT_ID(1), H_STATUS(1)
2766 ,H_TX3(1)
2767 ,H_TX9(1)
2768 ,H_TX11(1)
2769 ,H_TX12(1)
2770 ,H_TX14(1)
2771 ,H_TX17(1)
2772 ,H_TX18(1)
2773 ,H_TX20(1)
2774 ,H_TX22(1)
2775 ,H_TX24(1)
2776 ,H_TX25(1)
2777 ,H_TX28(1)
2778 FROM HZ_PARTY_SITES ps, HZ_LOCATIONS l
2779 WHERE
2780 ps.party_site_id = p_party_site_id
2781 AND ps.location_id = l.location_id
2782 AND (ps.status is null OR ps.status = 'A' OR ps.status = 'I')
2783 AND ROWNUM=1;
2784
2785 ---- SETTING GLOBAL CONDITION RECORD AT THE PARTY SITE LEVEL ----
2786 HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (36,H_TX22(1));
2787 H_TX30(1):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_PARTY_SITE_ID(1),'PARTY_SITES','ADDR_SOURCE_SYSTEM_REF', 'STAGE');
2788 H_TX4(1):=HZ_TRANS_PKG.WRADDRESS_CLEANSE(H_TX3(1),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2789 H_TX10(1):=HZ_TRANS_PKG.CLEANSE(H_TX9(1),NULL, 'CITY','PARTY_SITES');
2790 H_TX13(1):=HZ_TRANS_PKG.CLEANSE(H_TX12(1),NULL, 'PROVINCE','PARTY_SITES');
2791 H_TX15(1):=HZ_TRANS_PKG.WRSTATE_CLEANSE(H_TX14(1),NULL, 'STATE','PARTY_SITES','STAGE');
2792 H_TX19(1):=HZ_TRANS_PKG.CLEANSE(H_TX18(1),NULL, 'PARTY_SITE_NAME','PARTY_SITES');
2793 H_TX21(1):=HZ_TRANS_PKG.CLEANSE(H_TX20(1),NULL, 'COUNTY','PARTY_SITES');
2794 H_TX26(1):=HZ_TRANS_PKG.BASIC_WRADDR(H_TX3(1),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2795 H_TX27(1):=HZ_TRANS_PKG.BASIC_CLEANSE_WRADDR(H_TX3(1),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2796 H_TX29(1):=HZ_TRANS_PKG.BASIC_CLEANSE_WRADDR(H_TX28(1),NULL, 'ADDRESS1','PARTY_SITES','STAGE');
2797 H_TX3(1):=HZ_TRANS_PKG.WRADDRESS_EXACT(H_TX3(1),NULL, 'ADDRESS','PARTY_SITES','STAGE');
2798 H_TX9(1):=HZ_TRANS_PKG.EXACT(H_TX9(1),NULL, 'CITY','PARTY_SITES');
2799 H_TX11(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(1),NULL, 'POSTAL_CODE','PARTY_SITES','STAGE');
2800 H_TX12(1):=HZ_TRANS_PKG.EXACT(H_TX12(1),NULL, 'PROVINCE','PARTY_SITES');
2801 H_TX14(1):=HZ_TRANS_PKG.WRSTATE_EXACT(H_TX14(1),NULL, 'STATE','PARTY_SITES','STAGE');
2802 H_TX17(1):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX17(1),NULL, 'PARTY_SITE_NUMBER','PARTY_SITES','STAGE');
2803 H_TX18(1):=HZ_TRANS_PKG.EXACT(H_TX18(1),NULL, 'PARTY_SITE_NAME','PARTY_SITES');
2804 H_TX20(1):=HZ_TRANS_PKG.EXACT(H_TX20(1),NULL, 'COUNTY','PARTY_SITES');
2805 H_TX22(1):=HZ_TRANS_PKG.EXACT(H_TX22(1),NULL, 'COUNTRY','PARTY_SITES');
2806 H_TX24(1):=HZ_TRANS_PKG.EXACT(H_TX24(1),NULL, 'IDENTIFYING_ADDRESS_FLAG','PARTY_SITES');
2807 H_TX25(1):=HZ_TRANS_PKG.EXACT(H_TX25(1),NULL, 'STATUS','PARTY_SITES');
2808 H_TX28(1):=HZ_TRANS_PKG.BASIC_WRADDR(H_TX28(1),NULL, 'ADDRESS1','PARTY_SITES','STAGE');
2809 H_TX30(1):=HZ_TRANS_PKG.EXACT(H_TX30(1),NULL, 'ADDR_SOURCE_SYSTEM_REF','PARTY_SITES');
2810
2811 l_tryins := FALSE;
2812 l_tryupd := FALSE;
2813
2814 IF p_operation='C' THEN
2815 l_tryins:=TRUE;
2816 ELSE
2817 l_tryupd:=TRUE;
2818 END IF;
2819
2820 WHILE (l_tryins OR l_tryupd) LOOP
2821 IF l_tryins THEN
2822 BEGIN
2823 l_tryins:=FALSE;
2824 INSERT INTO HZ_STAGED_PARTY_SITES (
2825 PARTY_SITE_ID
2826 ,PARTY_ID
2827 ,ORG_CONTACT_ID
2828 ,STATUS_FLAG
2829 ,TX3
2830 ,TX4
2831 ,TX9
2832 ,TX10
2833 ,TX11
2834 ,TX12
2835 ,TX13
2836 ,TX14
2837 ,TX15
2838 ,TX17
2839 ,TX18
2840 ,TX19
2841 ,TX20
2842 ,TX21
2843 ,TX22
2844 ,TX24
2845 ,TX25
2846 ,TX26
2847 ,TX27
2848 ,TX28
2849 ,TX29
2850 ,TX30
2851 ) VALUES (
2852 H_PARTY_SITE_ID(1)
2853 ,H_PARTY_ID(1)
2854 ,H_ORG_CONTACT_ID(1)
2855 ,H_STATUS(1)
2856 ,decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
2857 ,decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
2858 ,decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
2859 ,decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
2860 ,decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
2861 ,decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
2862 ,decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
2863 ,decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
2864 ,decode(H_TX15(1),null,H_TX15(1),H_TX15(1)||' ')
2865 ,decode(H_TX17(1),null,H_TX17(1),H_TX17(1)||' ')
2866 ,decode(H_TX18(1),null,H_TX18(1),H_TX18(1)||' ')
2867 ,decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
2868 ,decode(H_TX20(1),null,H_TX20(1),H_TX20(1)||' ')
2869 ,decode(H_TX21(1),null,H_TX21(1),H_TX21(1)||' ')
2870 ,decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
2871 ,decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
2872 ,decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
2873 ,decode(H_TX26(1),null,H_TX26(1),H_TX26(1)||' ')
2874 ,decode(H_TX27(1),null,H_TX27(1),H_TX27(1)||' ')
2875 ,decode(H_TX28(1),null,H_TX28(1),H_TX28(1)||' ')
2876 ,decode(H_TX29(1),null,H_TX29(1),H_TX29(1)||' ')
2877 ,decode(H_TX30(1),null,H_TX30(1),H_TX30(1)||' ')
2878 );
2879 EXCEPTION
2880 WHEN DUP_VAL_ON_INDEX THEN
2881 IF p_operation='C' THEN
2882 l_tryupd:=TRUE;
2883 END IF;
2884 END;
2885 END IF;
2886
2887 IF l_tryupd THEN
2888 BEGIN
2889 l_tryupd:=FALSE;
2890 UPDATE HZ_STAGED_PARTY_SITES SET
2891 concat_col = concat_col
2892 ,status_flag = H_STATUS(1)
2893 ,TX3=decode(H_TX3(1),null,H_TX3(1),H_TX3(1)||' ')
2894 ,TX4=decode(H_TX4(1),null,H_TX4(1),H_TX4(1)||' ')
2895 ,TX9=decode(H_TX9(1),null,H_TX9(1),H_TX9(1)||' ')
2896 ,TX10=decode(H_TX10(1),null,H_TX10(1),H_TX10(1)||' ')
2897 ,TX11=decode(H_TX11(1),null,H_TX11(1),H_TX11(1)||' ')
2898 ,TX12=decode(H_TX12(1),null,H_TX12(1),H_TX12(1)||' ')
2899 ,TX13=decode(H_TX13(1),null,H_TX13(1),H_TX13(1)||' ')
2900 ,TX14=decode(H_TX14(1),null,H_TX14(1),H_TX14(1)||' ')
2901 ,TX15=decode(H_TX15(1),null,H_TX15(1),H_TX15(1)||' ')
2902 ,TX17=decode(H_TX17(1),null,H_TX17(1),H_TX17(1)||' ')
2903 ,TX18=decode(H_TX18(1),null,H_TX18(1),H_TX18(1)||' ')
2904 ,TX19=decode(H_TX19(1),null,H_TX19(1),H_TX19(1)||' ')
2905 ,TX20=decode(H_TX20(1),null,H_TX20(1),H_TX20(1)||' ')
2906 ,TX21=decode(H_TX21(1),null,H_TX21(1),H_TX21(1)||' ')
2907 ,TX22=decode(H_TX22(1),null,H_TX22(1),H_TX22(1)||' ')
2908 ,TX24=decode(H_TX24(1),null,H_TX24(1),H_TX24(1)||' ')
2909 ,TX25=decode(H_TX25(1),null,H_TX25(1),H_TX25(1)||' ')
2910 ,TX26=decode(H_TX26(1),null,H_TX26(1),H_TX26(1)||' ')
2911 ,TX27=decode(H_TX27(1),null,H_TX27(1),H_TX27(1)||' ')
2912 ,TX28=decode(H_TX28(1),null,H_TX28(1),H_TX28(1)||' ')
2913 ,TX29=decode(H_TX29(1),null,H_TX29(1),H_TX29(1)||' ')
2914 ,TX30=decode(H_TX30(1),null,H_TX30(1),H_TX30(1)||' ')
2915 WHERE PARTY_SITE_ID=H_PARTY_SITE_ID(1);
2916 IF SQL%ROWCOUNT=0 AND p_operation='U' THEN
2917 l_tryins := TRUE;
2918 END IF;
2919 EXCEPTION
2920 WHEN NO_DATA_FOUND THEN
2921 IF p_operation='U' THEN
2922 l_tryins := TRUE;
2923 END IF;
2924 END;
2925 END IF;
2926 END LOOP;
2927
2928 --Fix for bug 5048604, to update concat_col during update of denorm column
2929 UPDATE HZ_STAGED_PARTIES set
2930 D_PS = 'SYNC'
2931 ,CONCAT_COL = CONCAT_COL
2932 WHERE PARTY_ID = H_PARTY_ID(1);
2933
2934 -- REPURI. Bug 4884742. If shadow staging is completely successfully
2935 -- insert a record into hz_dqm_sh_sync_interface table for each record
2936 IF (HZ_DQM_SYNC.is_shadow_staging_complete) THEN
2937 BEGIN
2938 HZ_DQM_SYNC.insert_sh_interface_rec(l_party_id,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_operation);
2939 EXCEPTION WHEN OTHERS THEN
2940 NULL;
2941 END;
2942 END IF;
2943
2944 EXCEPTION WHEN OTHERS THEN
2945 -- FAILOVER : REPORT RECORD TO HZ_DQM_SYNC_INTERFACE
2946 -- FOR ONLINE FLOWS
2947 l_sql_err_message := SQLERRM;
2948 insert_dqm_sync_error_rec(l_party_id, p_party_site_id, NULL, l_org_contact_id, 'PARTY_SITES', p_operation, 'E', 'Y', l_sql_err_message);
2949 END;
2950
2951 PROCEDURE open_sync_party_cursor(
2952 p_operation IN VARCHAR2,
2953 p_party_type IN VARCHAR2,
2954 p_from_rec IN VARCHAR2,
2955 p_to_rec IN VARCHAR2,
2956 x_sync_party_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
2957
2958 BEGIN
2959
2960 IF p_party_type = 'ORGANIZATION' THEN
2961 open x_sync_party_cur FOR
2962 SELECT p.PARTY_ID, p.STATUS, dsi.ROWID
2963 ,p.PARTY_NAME
2964 ,p.PARTY_NUMBER
2965 ,p.PARTY_TYPE
2966 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
2967 ,op.DUNS_NUMBER_C
2968 ,op.TAX_NAME
2969 ,op.TAX_REFERENCE
2970 ,op.JGZZ_FISCAL_CODE
2971 ,op.SIC_CODE
2972 ,op.SIC_CODE_TYPE
2973 ,p.CATEGORY_CODE
2974 ,p.REFERENCE_USE_FLAG
2975 ,op.CORPORATION_CLASS
2976 FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op, HZ_DQM_SYNC_INTERFACE dsi
2977 WHERE p.party_id = op.party_id
2978 AND p.party_id = dsi.party_id
2979 AND p.PARTY_TYPE = 'ORGANIZATION'
2980 AND dsi.entity = 'PARTY'
2981 AND dsi.staged_flag = 'N'
2982 AND dsi.operation = p_operation
2983 AND dsi.sync_interface_num >= p_from_rec
2984 AND dsi.sync_interface_num <= p_to_rec
2985 AND (p.status = 'M' or op.effective_end_date is NULL);
2986 ELSIF p_party_type = 'PERSON' THEN
2987 open x_sync_party_cur FOR
2988 SELECT p.PARTY_ID, p.STATUS, dsi.ROWID
2989 ,p.PARTY_NAME
2990 ,p.PARTY_NUMBER
2991 ,p.PARTY_TYPE
2992 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
2993 ,NULL
2994 ,pe.TAX_NAME
2995 ,pe.TAX_REFERENCE
2996 ,pe.JGZZ_FISCAL_CODE
2997 ,NULL
2998 ,NULL
2999 ,p.CATEGORY_CODE
3000 ,p.REFERENCE_USE_FLAG
3001 ,NULL
3002 FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe, HZ_DQM_SYNC_INTERFACE dsi
3003 WHERE p.party_id = pe.party_id
3004 AND p.party_id = dsi.party_id
3005 AND p.PARTY_TYPE = 'PERSON'
3006 AND dsi.entity = 'PARTY'
3007 AND dsi.staged_flag = 'N'
3008 AND dsi.operation = p_operation
3009 AND dsi.sync_interface_num >= p_from_rec
3010 AND dsi.sync_interface_num <= p_to_rec
3011 AND (p.status = 'M' or pe.effective_end_date is NULL);
3012 ELSE
3013 open x_sync_party_cur FOR
3014 SELECT p.PARTY_ID, p.STATUS, dsi.ROWID
3015 ,p.PARTY_NAME
3016 ,p.PARTY_NUMBER
3017 ,p.PARTY_TYPE
3018 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
3019 ,NULL
3020 ,NULL
3021 ,NULL
3022 ,NULL
3023 ,NULL
3024 ,NULL
3025 ,p.CATEGORY_CODE
3026 ,p.REFERENCE_USE_FLAG
3027 ,NULL
3028 FROM HZ_PARTIES p, HZ_DQM_SYNC_INTERFACE dsi
3029 WHERE p.party_id = dsi.party_id
3030 AND dsi.entity = 'PARTY'
3031 AND dsi.staged_flag = 'N'
3032 AND dsi.operation = p_operation
3033 AND dsi.sync_interface_num >= p_from_rec
3034 AND dsi.sync_interface_num <= p_to_rec
3035 AND p.party_type <> 'PERSON'
3036 AND p.party_type <> 'ORGANIZATION'
3037 AND p.party_type <> 'PARTY_RELATIONSHIP';
3038 END IF;
3039 hz_trans_pkg.set_party_type(p_party_type);
3040 END;
3041
3042 PROCEDURE sync_all_parties (
3043 p_operation IN VARCHAR2,
3044 p_bulk_sync_type IN VARCHAR2,
3045 p_sync_all_party_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
3046
3047 l_limit NUMBER := 200;
3048 l_last_fetch BOOLEAN := FALSE;
3049 l_sql_errm VARCHAR2(2000);
3050 l_st NUMBER;
3051 l_en NUMBER;
3052 l_err_index NUMBER;
3053 l_err_count NUMBER;
3054
3055 bulk_errors EXCEPTION;
3056 PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
3057
3058 BEGIN
3059 log ('Begin Synchronizing Parties');
3060 LOOP
3061 log ('Bulk Collecting Parties Data...',FALSE);
3062 FETCH p_sync_all_party_cur BULK COLLECT INTO
3063 H_P_PARTY_ID
3064 ,H_STATUS
3065 ,H_ROWID
3066 ,H_TX2
3067 ,H_TX34
3068 ,H_TX36
3069 ,H_TX39
3070 ,H_TX41
3071 ,H_TX42
3072 ,H_TX44
3073 ,H_TX45
3074 ,H_TX46
3075 ,H_TX47
3076 ,H_TX48
3077 ,H_TX156
3078 ,H_TX157
3079 LIMIT l_limit;
3080 log ('Done');
3081
3082 IF p_sync_all_party_cur%NOTFOUND THEN
3083 l_last_fetch:=TRUE;
3084 END IF;
3085
3086 IF H_P_PARTY_ID.COUNT=0 AND l_last_fetch THEN
3087 EXIT;
3088 END IF;
3089
3090 log ('Synchronizing for '||H_P_PARTY_ID.COUNT||' Parties');
3091 log ('Populating Party Transformation Functions into Arrays...',FALSE);
3092 FOR I in H_P_PARTY_ID.FIRST..H_P_PARTY_ID.LAST LOOP
3093
3094 H_TX32(I):=HZ_PARTY_ACQUIRE.get_account_info(H_P_PARTY_ID(I),'PARTY','ALL_ACCOUNT_NAMES', 'STAGE');
3095 H_TX35(I):=HZ_PARTY_ACQUIRE.get_account_info(H_P_PARTY_ID(I),'PARTY','ALL_ACCOUNT_NUMBERS', 'STAGE');
3096 H_TX61(I):=HZ_EMAIL_DOMAINS_V2PUB.get_email_domains(H_P_PARTY_ID(I),'PARTY','DOMAIN_NAME', 'STAGE');
3097 H_TX63(I):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_P_PARTY_ID(I),'PARTY','PARTY_SOURCE_SYSTEM_REF', 'STAGE');
3098 H_TX4(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX2(I),NULL, 'PARTY_NAME','PARTY','STAGE');
3099 H_TX8(I):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX2(I),NULL, 'PARTY_NAME','PARTY','STAGE');
3100 H_TX19(I):=HZ_TRANS_PKG.SOUNDX(H_TX2(I),NULL, 'PARTY_NAME','PARTY');
3101 H_TX33(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX32(I),NULL, 'ALL_ACCOUNT_NAMES','PARTY','STAGE');
3102 H_TX40(I):=HZ_TRANS_PKG.WRNAMES_CLEANSE(H_TX39(I),NULL, 'PARTY_ALL_NAMES','PARTY','STAGE');
3103 H_TX43(I):=HZ_TRANS_PKG.CLEANSE(H_TX42(I),NULL, 'TAX_NAME','PARTY');
3104 H_TX59(I):=HZ_TRANS_PKG.BASIC_WRNAMES(H_TX2(I),NULL, 'PARTY_NAME','PARTY','STAGE');
3105 H_TX60(I):=HZ_TRANS_PKG.BASIC_CLEANSE_WRNAMES(H_TX2(I),NULL, 'PARTY_NAME','PARTY','STAGE');
3106 H_TX62(I):=HZ_EMAIL_DOMAINS_V2PUB.FULL_DOMAIN(H_TX61(I),NULL, 'DOMAIN_NAME','PARTY');
3107 H_TX158(I):=HZ_TRANS_PKG.SOUNDX(H_TX39(I),NULL, 'PARTY_ALL_NAMES','PARTY');
3108 H_TX2(I):=HZ_TRANS_PKG.EXACT_PADDED(H_TX2(I),NULL, 'PARTY_NAME','PARTY');
3109 H_TX32(I):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX32(I),NULL, 'ALL_ACCOUNT_NAMES','PARTY','STAGE');
3110 H_TX34(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX34(I),NULL, 'PARTY_NUMBER','PARTY','STAGE');
3111 H_TX35(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX35(I),NULL, 'ALL_ACCOUNT_NUMBERS','PARTY','STAGE');
3112 H_TX36(I):=HZ_TRANS_PKG.EXACT(H_TX36(I),NULL, 'PARTY_TYPE','PARTY');
3113 H_TX39(I):=HZ_TRANS_PKG.WRNAMES_EXACT(H_TX39(I),NULL, 'PARTY_ALL_NAMES','PARTY','STAGE');
3114 H_TX41(I):=HZ_TRANS_PKG.EXACT(H_TX41(I),NULL, 'DUNS_NUMBER_C','PARTY');
3115 H_TX42(I):=HZ_TRANS_PKG.EXACT(H_TX42(I),NULL, 'TAX_NAME','PARTY');
3116 H_TX44(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX44(I),NULL, 'TAX_REFERENCE','PARTY','STAGE');
3117 H_TX45(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX45(I),NULL, 'JGZZ_FISCAL_CODE','PARTY','STAGE');
3118 H_TX46(I):=HZ_TRANS_PKG.EXACT(H_TX46(I),NULL, 'SIC_CODE','PARTY');
3119 H_TX47(I):=HZ_TRANS_PKG.EXACT(H_TX47(I),NULL, 'SIC_CODE_TYPE','PARTY');
3120 H_TX48(I):=HZ_TRANS_PKG.EXACT(H_TX48(I),NULL, 'CATEGORY_CODE','PARTY');
3121 H_TX61(I):=HZ_EMAIL_DOMAINS_V2PUB.CORE_DOMAIN(H_TX61(I),NULL, 'DOMAIN_NAME','PARTY');
3122 H_TX63(I):=HZ_TRANS_PKG.EXACT(H_TX63(I),NULL, 'PARTY_SOURCE_SYSTEM_REF','PARTY');
3123 H_TX156(I):=HZ_TRANS_PKG.EXACT(H_TX156(I),NULL, 'REFERENCE_USE_FLAG','PARTY');
3124 H_TX157(I):=HZ_TRANS_PKG.EXACT(H_TX157(I),NULL, 'CORPORATION_CLASS','PARTY');
3125 END LOOP;
3126 log ('Done');
3127
3128 l_st := 1;
3129 l_en := H_P_PARTY_ID.COUNT;
3130
3131 IF p_operation = 'C' THEN
3132 BEGIN
3133 log ('Inserting Data into HZ_STAGED_PARTIES...',FALSE);
3134 FORALL I in l_st..l_en SAVE EXCEPTIONS
3135 INSERT INTO HZ_STAGED_PARTIES (
3136 PARTY_ID
3137 ,STATUS
3138 ,D_PS
3139 ,D_CT
3140 ,D_CPT
3141 ,TX2
3142 ,TX4
3143 ,TX8
3144 ,TX19
3145 ,TX32
3146 ,TX33
3147 ,TX34
3148 ,TX35
3149 ,TX36
3150 ,TX39
3151 ,TX40
3152 ,TX41
3153 ,TX42
3154 ,TX43
3155 ,TX44
3156 ,TX45
3157 ,TX46
3158 ,TX47
3159 ,TX48
3160 ,TX59
3161 ,TX60
3162 ,TX61
3163 ,TX62
3164 ,TX63
3165 ,TX156
3166 ,TX157
3167 ,TX158
3168 ) VALUES (
3169 H_P_PARTY_ID(I)
3170 ,H_STATUS(I)
3171 ,'SYNC'
3172 ,'SYNC'
3173 ,'SYNC'
3174 ,decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
3175 ,decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
3176 ,decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
3177 ,decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
3178 ,decode(H_TX32(I),null,H_TX32(I),H_TX32(I)||' ')
3179 ,decode(H_TX33(I),null,H_TX33(I),H_TX33(I)||' ')
3180 ,decode(H_TX34(I),null,H_TX34(I),H_TX34(I)||' ')
3181 ,decode(H_TX35(I),null,H_TX35(I),H_TX35(I)||' ')
3182 ,decode(H_TX36(I),null,H_TX36(I),H_TX36(I)||' ')
3183 ,decode(H_TX39(I),null,H_TX39(I),H_TX39(I)||' ')
3184 ,decode(H_TX40(I),null,H_TX40(I),H_TX40(I)||' ')
3185 ,decode(H_TX41(I),null,H_TX41(I),H_TX41(I)||' ')
3186 ,decode(H_TX42(I),null,H_TX42(I),H_TX42(I)||' ')
3187 ,decode(H_TX43(I),null,H_TX43(I),H_TX43(I)||' ')
3188 ,decode(H_TX44(I),null,H_TX44(I),H_TX44(I)||' ')
3189 ,decode(H_TX45(I),null,H_TX45(I),H_TX45(I)||' ')
3190 ,decode(H_TX46(I),null,H_TX46(I),H_TX46(I)||' ')
3191 ,decode(H_TX47(I),null,H_TX47(I),H_TX47(I)||' ')
3192 ,decode(H_TX48(I),null,H_TX48(I),H_TX48(I)||' ')
3193 ,decode(H_TX59(I),null,H_TX59(I),H_TX59(I)||' ')
3194 ,decode(H_TX60(I),null,H_TX60(I),H_TX60(I)||' ')
3195 ,decode(H_TX61(I),null,H_TX61(I),H_TX61(I)||' ')
3196 ,decode(H_TX62(I),null,H_TX62(I),H_TX62(I)||' ')
3197 ,decode(H_TX63(I),null,H_TX63(I),H_TX63(I)||' ')
3198 ,decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
3199 ,decode(H_TX157(I),null,H_TX157(I),H_TX157(I)||' ')
3200 ,decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
3201 );
3202 log ('Done');
3203 EXCEPTION WHEN bulk_errors THEN
3204 l_err_count := SQL%BULK_EXCEPTIONS.COUNT;
3205 FOR indx IN 1..l_err_count LOOP
3206 l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3207 l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3208 IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3209 log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3210 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='C' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3211 ELSE
3212 IF p_bulk_sync_type = 'DQM_SYNC' THEN
3213 UPDATE hz_dqm_sync_interface
3214 SET error_data = l_sql_errm
3215 ,staged_flag = decode (error_data, NULL, 'N', 'E')
3216 WHERE rowid = H_ROWID(l_err_index);
3217 ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3218 -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3219 insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'N', l_sql_errm);
3220 END IF;
3221 END IF;
3222 END LOOP;
3223 END;
3224 ELSIF p_operation = 'U' THEN
3225 BEGIN
3226 log ('Updating Data in HZ_STAGED_PARTIES...',FALSE);
3227 FORALL I in l_st..l_en SAVE EXCEPTIONS
3228 UPDATE HZ_STAGED_PARTIES SET
3229 status =H_STATUS(I)
3230 ,concat_col = concat_col
3231 ,TX2=decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
3232 ,TX4=decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
3233 ,TX8=decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
3234 ,TX19=decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
3235 ,TX32=decode(H_TX32(I),null,H_TX32(I),H_TX32(I)||' ')
3236 ,TX33=decode(H_TX33(I),null,H_TX33(I),H_TX33(I)||' ')
3237 ,TX34=decode(H_TX34(I),null,H_TX34(I),H_TX34(I)||' ')
3238 ,TX35=decode(H_TX35(I),null,H_TX35(I),H_TX35(I)||' ')
3239 ,TX36=decode(H_TX36(I),null,H_TX36(I),H_TX36(I)||' ')
3240 ,TX39=decode(H_TX39(I),null,H_TX39(I),H_TX39(I)||' ')
3241 ,TX40=decode(H_TX40(I),null,H_TX40(I),H_TX40(I)||' ')
3242 ,TX41=decode(H_TX41(I),null,H_TX41(I),H_TX41(I)||' ')
3243 ,TX42=decode(H_TX42(I),null,H_TX42(I),H_TX42(I)||' ')
3244 ,TX43=decode(H_TX43(I),null,H_TX43(I),H_TX43(I)||' ')
3245 ,TX44=decode(H_TX44(I),null,H_TX44(I),H_TX44(I)||' ')
3246 ,TX45=decode(H_TX45(I),null,H_TX45(I),H_TX45(I)||' ')
3247 ,TX46=decode(H_TX46(I),null,H_TX46(I),H_TX46(I)||' ')
3248 ,TX47=decode(H_TX47(I),null,H_TX47(I),H_TX47(I)||' ')
3249 ,TX48=decode(H_TX48(I),null,H_TX48(I),H_TX48(I)||' ')
3250 ,TX59=decode(H_TX59(I),null,H_TX59(I),H_TX59(I)||' ')
3251 ,TX60=decode(H_TX60(I),null,H_TX60(I),H_TX60(I)||' ')
3252 ,TX61=decode(H_TX61(I),null,H_TX61(I),H_TX61(I)||' ')
3253 ,TX62=decode(H_TX62(I),null,H_TX62(I),H_TX62(I)||' ')
3254 ,TX63=decode(H_TX63(I),null,H_TX63(I),H_TX63(I)||' ')
3255 ,TX156=decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
3256 ,TX157=decode(H_TX157(I),null,H_TX157(I),H_TX157(I)||' ')
3257 ,TX158=decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
3258 WHERE PARTY_ID = H_P_PARTY_ID(I);
3259 log ('Done');
3260 EXCEPTION WHEN bulk_errors THEN
3261 l_err_count := SQL%BULK_EXCEPTIONS.COUNT;
3262 FOR indx IN 1..l_err_count LOOP
3263 l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3264 l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3265 IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3266 log ('Exception DUP_VAL_ON_INDEX occured while inserting Party with PARTY_ID - '||H_P_PARTY_ID(l_err_index));
3267 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY' AND OPERATION='U' AND PARTY_ID=H_P_PARTY_ID(l_err_index);
3268 ELSE
3269 IF p_bulk_sync_type = 'DQM_SYNC' THEN
3270 UPDATE hz_dqm_sync_interface
3271 SET error_data = l_sql_errm
3272 ,staged_flag = decode (error_data, NULL, 'N', 'E')
3273 WHERE rowid = H_ROWID(l_err_index);
3274 ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3275 -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3276 insert_dqm_sync_error_rec(H_P_PARTY_ID(l_err_index), NULL, NULL, NULL, 'PARTY', p_operation, 'E', 'N', l_sql_errm);
3277 END IF;
3278 END IF;
3279 END LOOP;
3280 END;
3281 END IF;
3282
3283 -- REPURI. Bug 4884742.
3284 -- Bulk Insert the Import Parties into Shadow Sync Interface table
3285 -- if Shadow Staging has already run and completed successfully
3286 IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
3287 (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
3288 BEGIN
3289 -- REPURI. Bug 4968126.
3290 -- Using the Merge instead of Insert statement
3291 -- so that duplicate records dont get inserted.
3292 log ('Merging data into HZ_DQM_SH_SYNC_INTERFACE...',FALSE);
3293 FORALL I in l_st..l_en
3294 MERGE INTO hz_dqm_sh_sync_interface S
3295 USING (
3296 SELECT
3297 H_P_PARTY_ID(I) AS party_id
3298 FROM dual ) T
3299 ON (S.entity = 'PARTY' AND
3300 S.party_id = T.party_id AND
3301 S.staged_flag <> 'E')
3302 WHEN NOT MATCHED THEN
3303 INSERT (
3304 PARTY_ID,
3305 RECORD_ID,
3306 PARTY_SITE_ID,
3307 ORG_CONTACT_ID,
3308 ENTITY,
3309 OPERATION,
3310 STAGED_FLAG,
3311 REALTIME_SYNC_FLAG,
3312 CREATED_BY,
3313 CREATION_DATE,
3314 LAST_UPDATE_LOGIN,
3315 LAST_UPDATE_DATE,
3316 LAST_UPDATED_BY,
3317 SYNC_INTERFACE_NUM
3318 ) VALUES (
3319 H_P_PARTY_ID(I),
3320 NULL,
3321 NULL,
3322 NULL,
3323 'PARTY',
3324 p_operation,
3325 'N',
3326 'N',
3327 hz_utility_pub.created_by,
3328 hz_utility_pub.creation_date,
3329 hz_utility_pub.last_update_login,
3330 hz_utility_pub.last_update_date,
3331 hz_utility_pub.user_id,
3332 HZ_DQM_SH_SYNC_INTERFACE_S.nextval
3333 );
3334 log ('Done');
3335 EXCEPTION WHEN OTHERS THEN
3336 log ('Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table');
3337 log ('Eror Message is - '|| sqlerrm);
3338 END;
3339 END IF;
3340
3341 IF l_last_fetch THEN
3342 FND_CONCURRENT.AF_Commit;
3343 EXIT;
3344 END IF;
3345
3346 FND_CONCURRENT.AF_Commit;
3347
3348 END LOOP;
3349 log ('End Synchronizing Parties');
3350 END;
3351
3352 PROCEDURE open_sync_party_site_cursor (
3353 p_operation IN VARCHAR2,
3354 p_from_rec IN VARCHAR2,
3355 p_to_rec IN VARCHAR2,
3356 x_sync_party_site_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
3357 BEGIN
3358 OPEN x_sync_party_site_cur FOR
3359 SELECT /*+ ORDERED USE_NL(ps l) */
3360 ps.PARTY_SITE_ID
3361 ,dsi.party_id
3362 ,dsi.org_contact_id
3363 ,ps.status
3364 ,dsi.ROWID
3365 ,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
3366 ,l.CITY
3367 ,l.POSTAL_CODE
3368 ,l.PROVINCE
3369 ,l.STATE
3370 ,ps.PARTY_SITE_NUMBER
3371 ,ps.PARTY_SITE_NAME
3372 ,l.COUNTY
3373 ,l.COUNTRY
3374 ,ps.IDENTIFYING_ADDRESS_FLAG
3375 ,ps.STATUS
3376 ,l.ADDRESS1
3377 FROM HZ_DQM_SYNC_INTERFACE dsi, HZ_PARTY_SITES ps, HZ_LOCATIONS l
3378 WHERE dsi.record_id = ps.party_site_id
3379 AND dsi.entity = 'PARTY_SITES'
3380 AND dsi.operation = p_operation
3381 AND dsi.staged_flag = 'N'
3382 AND dsi.sync_interface_num >= p_from_rec
3383 AND dsi.sync_interface_num <= p_to_rec
3384 AND (ps.status is null OR ps.status = 'A' OR ps.status = 'I')
3385 AND ps.location_id = l.location_id;
3386 END;
3387
3388 PROCEDURE sync_all_party_sites (
3389 p_operation IN VARCHAR2,
3390 p_bulk_sync_type IN VARCHAR2,
3391 p_sync_all_party_site_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
3392
3393 l_limit NUMBER := 200;
3394 l_last_fetch BOOLEAN := FALSE;
3395 l_sql_errm VARCHAR2(2000);
3396 l_st NUMBER;
3397 l_en NUMBER;
3398 l_err_index NUMBER;
3399 l_err_count NUMBER;
3400
3401 bulk_errors EXCEPTION;
3402 PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
3403
3404 BEGIN
3405 log ('Begin Synchronizing Party Sites');
3406 LOOP
3407 log ('Bulk Collecting Party Sites Data...',FALSE);
3408 FETCH p_sync_all_party_site_cur BULK COLLECT INTO
3409 H_PARTY_SITE_ID
3410 ,H_PS_PARTY_ID
3411 ,H_PS_ORG_CONTACT_ID
3412 ,H_STATUS
3413 ,H_ROWID
3414 ,H_TX3
3415 ,H_TX9
3416 ,H_TX11
3417 ,H_TX12
3418 ,H_TX14
3419 ,H_TX17
3420 ,H_TX18
3421 ,H_TX20
3422 ,H_TX22
3423 ,H_TX24
3424 ,H_TX25
3425 ,H_TX28
3426 LIMIT l_limit;
3427 log ('Done');
3428
3429 IF p_sync_all_party_site_cur%NOTFOUND THEN
3430 l_last_fetch:=TRUE;
3431 END IF;
3432
3433 IF H_PARTY_SITE_ID.COUNT=0 AND l_last_fetch THEN
3434 EXIT;
3435 END IF;
3436
3437 log ('Synchronizing for '||H_PARTY_SITE_ID.COUNT||' Party Sites');
3438 log ('Populating Party Sites Transformation Functions into Arrays...',FALSE);
3439 FOR I in H_PARTY_SITE_ID.FIRST..H_PARTY_SITE_ID.LAST LOOP
3440 ---- SETTING GLOBAL CONDITION RECORD AT THE PARTY SITE LEVEL ----
3441
3442 HZ_WORD_CONDITIONS_PKG.set_gbl_condition_rec (36,H_TX22(I));
3443
3444 H_TX30(I):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_PARTY_SITE_ID(I),'PARTY_SITES','ADDR_SOURCE_SYSTEM_REF', 'STAGE');
3445 H_TX4(I):=HZ_TRANS_PKG.WRADDRESS_CLEANSE(H_TX3(I),NULL, 'ADDRESS','PARTY_SITES','STAGE');
3446 H_TX10(I):=HZ_TRANS_PKG.CLEANSE(H_TX9(I),NULL, 'CITY','PARTY_SITES');
3447 H_TX13(I):=HZ_TRANS_PKG.CLEANSE(H_TX12(I),NULL, 'PROVINCE','PARTY_SITES');
3448 H_TX15(I):=HZ_TRANS_PKG.WRSTATE_CLEANSE(H_TX14(I),NULL, 'STATE','PARTY_SITES','STAGE');
3449 H_TX19(I):=HZ_TRANS_PKG.CLEANSE(H_TX18(I),NULL, 'PARTY_SITE_NAME','PARTY_SITES');
3450 H_TX21(I):=HZ_TRANS_PKG.CLEANSE(H_TX20(I),NULL, 'COUNTY','PARTY_SITES');
3451 H_TX26(I):=HZ_TRANS_PKG.BASIC_WRADDR(H_TX3(I),NULL, 'ADDRESS','PARTY_SITES','STAGE');
3452 H_TX27(I):=HZ_TRANS_PKG.BASIC_CLEANSE_WRADDR(H_TX3(I),NULL, 'ADDRESS','PARTY_SITES','STAGE');
3453 H_TX29(I):=HZ_TRANS_PKG.BASIC_CLEANSE_WRADDR(H_TX28(I),NULL, 'ADDRESS1','PARTY_SITES','STAGE');
3454 H_TX3(I):=HZ_TRANS_PKG.WRADDRESS_EXACT(H_TX3(I),NULL, 'ADDRESS','PARTY_SITES','STAGE');
3455 H_TX9(I):=HZ_TRANS_PKG.EXACT(H_TX9(I),NULL, 'CITY','PARTY_SITES');
3456 H_TX11(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(I),NULL, 'POSTAL_CODE','PARTY_SITES','STAGE');
3457 H_TX12(I):=HZ_TRANS_PKG.EXACT(H_TX12(I),NULL, 'PROVINCE','PARTY_SITES');
3458 H_TX14(I):=HZ_TRANS_PKG.WRSTATE_EXACT(H_TX14(I),NULL, 'STATE','PARTY_SITES','STAGE');
3459 H_TX17(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX17(I),NULL, 'PARTY_SITE_NUMBER','PARTY_SITES','STAGE');
3460 H_TX18(I):=HZ_TRANS_PKG.EXACT(H_TX18(I),NULL, 'PARTY_SITE_NAME','PARTY_SITES');
3461 H_TX20(I):=HZ_TRANS_PKG.EXACT(H_TX20(I),NULL, 'COUNTY','PARTY_SITES');
3462 H_TX22(I):=HZ_TRANS_PKG.EXACT(H_TX22(I),NULL, 'COUNTRY','PARTY_SITES');
3463 H_TX24(I):=HZ_TRANS_PKG.EXACT(H_TX24(I),NULL, 'IDENTIFYING_ADDRESS_FLAG','PARTY_SITES');
3464 H_TX25(I):=HZ_TRANS_PKG.EXACT(H_TX25(I),NULL, 'STATUS','PARTY_SITES');
3465 H_TX28(I):=HZ_TRANS_PKG.BASIC_WRADDR(H_TX28(I),NULL, 'ADDRESS1','PARTY_SITES','STAGE');
3466 H_TX30(I):=HZ_TRANS_PKG.EXACT(H_TX30(I),NULL, 'ADDR_SOURCE_SYSTEM_REF','PARTY_SITES');
3467 END LOOP;
3468 log ('Done');
3469
3470 l_st := 1;
3471 l_en := H_PARTY_SITE_ID.COUNT;
3472
3473 IF p_operation = 'C' THEN
3474 BEGIN
3475 log ('Inserting Data into HZ_STAGED_PARTY_SITES...',FALSE);
3476 FORALL I in l_st..l_en SAVE EXCEPTIONS
3477 INSERT INTO HZ_STAGED_PARTY_SITES (
3478 PARTY_SITE_ID
3479 ,PARTY_ID
3480 ,ORG_CONTACT_ID
3481 ,STATUS_FLAG
3482 ,TX3
3483 ,TX4
3484 ,TX9
3485 ,TX10
3486 ,TX11
3487 ,TX12
3488 ,TX13
3489 ,TX14
3490 ,TX15
3491 ,TX17
3492 ,TX18
3493 ,TX19
3494 ,TX20
3495 ,TX21
3496 ,TX22
3497 ,TX24
3498 ,TX25
3499 ,TX26
3500 ,TX27
3501 ,TX28
3502 ,TX29
3503 ,TX30
3504 ) VALUES (
3505 H_PARTY_SITE_ID(I)
3506 ,H_PS_PARTY_ID(I)
3507 ,H_PS_ORG_CONTACT_ID(I)
3508 ,H_STATUS(I)
3509 ,decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
3510 ,decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
3511 ,decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
3512 ,decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
3513 ,decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
3514 ,decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
3515 ,decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
3516 ,decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
3517 ,decode(H_TX15(I),null,H_TX15(I),H_TX15(I)||' ')
3518 ,decode(H_TX17(I),null,H_TX17(I),H_TX17(I)||' ')
3519 ,decode(H_TX18(I),null,H_TX18(I),H_TX18(I)||' ')
3520 ,decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
3521 ,decode(H_TX20(I),null,H_TX20(I),H_TX20(I)||' ')
3522 ,decode(H_TX21(I),null,H_TX21(I),H_TX21(I)||' ')
3523 ,decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
3524 ,decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
3525 ,decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
3526 ,decode(H_TX26(I),null,H_TX26(I),H_TX26(I)||' ')
3527 ,decode(H_TX27(I),null,H_TX27(I),H_TX27(I)||' ')
3528 ,decode(H_TX28(I),null,H_TX28(I),H_TX28(I)||' ')
3529 ,decode(H_TX29(I),null,H_TX29(I),H_TX29(I)||' ')
3530 ,decode(H_TX30(I),null,H_TX30(I),H_TX30(I)||' ')
3531 );
3532 log ('Done');
3533 EXCEPTION WHEN bulk_errors THEN
3534 l_err_count := SQL%BULK_EXCEPTIONS.COUNT;
3535 FOR indx IN 1..l_err_count LOOP
3536 l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3537 l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3538 IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3539 log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3540 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='C' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3541 ELSE
3542 IF p_bulk_sync_type = 'DQM_SYNC' THEN
3543 UPDATE hz_dqm_sync_interface
3544 SET error_data = l_sql_errm
3545 ,staged_flag = decode (error_data, NULL, 'N', 'E')
3546 WHERE rowid = H_ROWID(l_err_index);
3547 ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3548 -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3549 insert_dqm_sync_error_rec(H_PS_PARTY_ID(l_err_index), H_PARTY_SITE_ID(l_err_index), NULL, H_PS_ORG_CONTACT_ID(l_err_index), 'PARTY_SITES', p_operation, 'E', 'N', l_sql_errm);
3550 END IF;
3551 END IF;
3552 END LOOP;
3553 END;
3554 ELSIF p_operation = 'U' THEN
3555 BEGIN
3556 log ('Updating Data in HZ_STAGED_PARTY_SITES...',FALSE);
3557 FORALL I in l_st..l_en SAVE EXCEPTIONS
3558 UPDATE HZ_STAGED_PARTY_SITES SET
3559 concat_col = concat_col
3560 ,status_flag = H_STATUS(I)
3561 ,TX3=decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
3562 ,TX4=decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
3563 ,TX9=decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
3564 ,TX10=decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
3565 ,TX11=decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
3566 ,TX12=decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
3567 ,TX13=decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
3568 ,TX14=decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
3569 ,TX15=decode(H_TX15(I),null,H_TX15(I),H_TX15(I)||' ')
3570 ,TX17=decode(H_TX17(I),null,H_TX17(I),H_TX17(I)||' ')
3571 ,TX18=decode(H_TX18(I),null,H_TX18(I),H_TX18(I)||' ')
3572 ,TX19=decode(H_TX19(I),null,H_TX19(I),H_TX19(I)||' ')
3573 ,TX20=decode(H_TX20(I),null,H_TX20(I),H_TX20(I)||' ')
3574 ,TX21=decode(H_TX21(I),null,H_TX21(I),H_TX21(I)||' ')
3575 ,TX22=decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
3576 ,TX24=decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
3577 ,TX25=decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
3578 ,TX26=decode(H_TX26(I),null,H_TX26(I),H_TX26(I)||' ')
3579 ,TX27=decode(H_TX27(I),null,H_TX27(I),H_TX27(I)||' ')
3580 ,TX28=decode(H_TX28(I),null,H_TX28(I),H_TX28(I)||' ')
3581 ,TX29=decode(H_TX29(I),null,H_TX29(I),H_TX29(I)||' ')
3582 ,TX30=decode(H_TX30(I),null,H_TX30(I),H_TX30(I)||' ')
3583 WHERE PARTY_SITE_ID=H_PARTY_SITE_ID(I);
3584 log ('Done');
3585 EXCEPTION WHEN bulk_errors THEN
3586 l_err_count := SQL%BULK_EXCEPTIONS.COUNT;
3587 FOR indx IN 1..l_err_count LOOP
3588 l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3589 l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3590 IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3591 log ('Exception DUP_VAL_ON_INDEX occured while inserting Party Site with PARTY_SITE_ID - '||H_PARTY_SITE_ID(l_err_index));
3592 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='PARTY_SITES' AND OPERATION='U' AND RECORD_ID=H_PARTY_SITE_ID(l_err_index);
3593 ELSE
3594 IF p_bulk_sync_type = 'DQM_SYNC' THEN
3595 UPDATE hz_dqm_sync_interface
3596 SET error_data = l_sql_errm
3597 ,staged_flag = decode (error_data, NULL, 'N', 'E')
3598 WHERE rowid = H_ROWID(l_err_index);
3599 ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3600 -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3601 insert_dqm_sync_error_rec(H_PS_PARTY_ID(l_err_index), H_PARTY_SITE_ID(l_err_index), NULL, H_PS_ORG_CONTACT_ID(l_err_index), 'PARTY_SITES', p_operation, 'E', 'N', l_sql_errm);
3602 END IF;
3603 END IF;
3604 END LOOP;
3605 END;
3606 END IF;
3607
3608 IF l_last_fetch THEN
3609 -- Update HZ_STAGED_PARTIES, if corresponding child entity records
3610 -- PARTY_SITES (in this case), have been inserted/updated
3611
3612 log ('Updating D_PS column to SYNC in HZ_STAGED_PARTIES table for all related records...',FALSE);
3613 --Fix for bug 5048604, to update concat_col during update of denorm column
3614 FORALL I IN H_PARTY_SITE_ID.FIRST..H_PARTY_SITE_ID.LAST
3615 UPDATE HZ_STAGED_PARTIES set
3616 D_PS = 'SYNC'
3617 ,CONCAT_COL = CONCAT_COL
3618 WHERE PARTY_ID = H_PS_PARTY_ID(I);
3619 log ('Done');
3620
3621 -- REPURI. Bug 4884742.
3622 -- Bulk Insert of Import Party Sites into Shadow Sync Interface table
3623 -- if Shadow Staging has already run and completed successfully
3624 IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
3625 (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
3626 BEGIN
3627 -- REPURI. Bug 4968126.
3628 -- Using the Merge instead of Insert statement
3629 -- so that duplicate records dont get inserted.
3630 log ('Merging data into HZ_DQM_SH_SYNC_INTERFACE...',FALSE);
3631 FORALL I in l_st..l_en
3632 MERGE INTO hz_dqm_sh_sync_interface S
3633 USING (
3634 SELECT
3635 H_PS_PARTY_ID(I) AS party_id
3636 ,H_PARTY_SITE_ID(I) AS record_id
3637 ,H_PS_ORG_CONTACT_ID(I) AS org_contact_id
3638 FROM dual ) T
3639 ON (S.entity = 'PARTY_SITES' AND
3640 S.party_id = T.party_id AND
3641 S.record_id = T.record_id AND
3642 NVL(S.org_contact_id, -99) = NVL(T.org_contact_id, -99) AND
3643 S.staged_flag <> 'E')
3644 WHEN NOT MATCHED THEN
3645 INSERT (
3646 PARTY_ID,
3647 RECORD_ID,
3648 PARTY_SITE_ID,
3649 ORG_CONTACT_ID,
3650 ENTITY,
3651 OPERATION,
3652 STAGED_FLAG,
3653 REALTIME_SYNC_FLAG,
3654 CREATED_BY,
3655 CREATION_DATE,
3656 LAST_UPDATE_LOGIN,
3657 LAST_UPDATE_DATE,
3658 LAST_UPDATED_BY,
3659 SYNC_INTERFACE_NUM
3660 ) VALUES (
3661 H_PS_PARTY_ID(I),
3662 H_PARTY_SITE_ID(I),
3663 NULL,
3664 H_PS_ORG_CONTACT_ID(I),
3665 'PARTY_SITES',
3666 p_operation,
3667 'N',
3668 'N',
3669 hz_utility_pub.created_by,
3670 hz_utility_pub.creation_date,
3671 hz_utility_pub.last_update_login,
3672 hz_utility_pub.last_update_date,
3673 hz_utility_pub.user_id,
3674 HZ_DQM_SH_SYNC_INTERFACE_S.nextval
3675 );
3676 log ('Done');
3677 EXCEPTION WHEN OTHERS THEN
3678 log ('Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table');
3679 log ('Eror Message is - '|| sqlerrm);
3680 END;
3681 END IF;
3682
3683 FND_CONCURRENT.AF_Commit;
3684 EXIT;
3685 END IF;
3686
3687 FND_CONCURRENT.AF_Commit;
3688
3689 END LOOP;
3690 log ('End Synchronizing Party Sites');
3691 END;
3692
3693 PROCEDURE open_sync_contact_cursor (
3694 p_operation IN VARCHAR2,
3695 p_from_rec IN VARCHAR2,
3696 p_to_rec IN VARCHAR2,
3697 x_sync_contact_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
3698 BEGIN
3699 OPEN x_sync_contact_cur FOR
3700 SELECT
3701 /*+ leading(dsi) USE_NL(OC R PP) */
3702 oc.ORG_CONTACT_ID
3703 ,r.OBJECT_ID
3704 ,r.PARTY_ID
3705 ,r.STATUS
3706 ,dsi.ROWID
3707 ,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
3708 ,oc.CONTACT_NUMBER
3709 ,oc.JOB_TITLE
3710 FROM HZ_DQM_SYNC_INTERFACE dsi, HZ_RELATIONSHIPS r,
3711 HZ_ORG_CONTACTS oc, HZ_PERSON_PROFILES pp
3712 WHERE oc.party_relationship_id = r.relationship_id
3713 AND dsi.record_id = oc.org_contact_id
3714 AND r.subject_id = pp.party_id
3715 AND r.subject_type = 'PERSON'
3716 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3717 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3718 AND DIRECTIONAL_FLAG = 'F'
3719 AND pp.effective_end_date is NULL
3720 AND dsi.entity = 'CONTACTS'
3721 AND dsi.operation = p_operation
3722 AND dsi.staged_flag = 'N'
3723 AND dsi.sync_interface_num >= p_from_rec
3724 AND dsi.sync_interface_num <= p_to_rec
3725 AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
3726 AND (r.status is null OR r.status = 'A' or r.status = 'I');
3727 END;
3728
3729 PROCEDURE sync_all_contacts (
3730 p_operation IN VARCHAR2,
3731 p_bulk_sync_type IN VARCHAR2,
3732 p_sync_all_contact_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
3733
3734 l_limit NUMBER := 200;
3735 l_last_fetch BOOLEAN := FALSE;
3736 l_sql_errm VARCHAR2(2000);
3737 l_st NUMBER;
3738 l_en NUMBER;
3739 l_err_index NUMBER;
3740 l_err_count NUMBER;
3741
3742 bulk_errors EXCEPTION;
3743 PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
3744
3745 BEGIN
3746 log ('Begin Synchronizing Contacts');
3747 LOOP
3748 log ('Bulk Collecting Contacts Data...',FALSE);
3749 FETCH p_sync_all_contact_cur BULK COLLECT INTO
3750 H_ORG_CONTACT_ID
3751 ,H_C_PARTY_ID
3752 ,H_R_PARTY_ID
3753 ,H_STATUS
3754 ,H_ROWID
3755 ,H_TX2
3756 ,H_TX11
3757 ,H_TX22
3758 LIMIT l_limit;
3759 log ('Done');
3760
3761 IF p_sync_all_contact_cur%NOTFOUND THEN
3762 l_last_fetch:=TRUE;
3763 END IF;
3764
3765 IF H_ORG_CONTACT_ID.COUNT=0 AND l_last_fetch THEN
3766 EXIT;
3767 END IF;
3768
3769 log ('Synchronizing for '||H_ORG_CONTACT_ID.COUNT||' Contacts');
3770 log ('Populating Contacts Transformation Functions into Arrays...',FALSE);
3771
3772 FOR I in H_ORG_CONTACT_ID.FIRST..H_ORG_CONTACT_ID.LAST LOOP
3773
3774 H_TX25(I):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_ORG_CONTACT_ID(I),'CONTACTS','CONTACT_SOURCE_SYSTEM_REF', 'STAGE');
3775 H_TX5(I):=HZ_TRANS_PKG.WRPERSON_EXACT(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
3776 H_TX6(I):=HZ_TRANS_PKG.WRPERSON_CLEANSE(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
3777 H_TX23(I):=HZ_TRANS_PKG.BASIC_WRPERSON(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
3778 H_TX24(I):=HZ_TRANS_PKG.BASIC_CLEANSE_WRPERSON(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS','STAGE');
3779 H_TX156(I):=HZ_TRANS_PKG.SOUNDX(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS');
3780 H_TX2(I):=HZ_TRANS_PKG.EXACT_PADDED(H_TX2(I),NULL, 'CONTACT_NAME','CONTACTS');
3781 H_TX11(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX11(I),NULL, 'CONTACT_NUMBER','CONTACTS','STAGE');
3782 H_TX22(I):=HZ_TRANS_PKG.EXACT(H_TX22(I),NULL, 'JOB_TITLE','CONTACTS');
3783 H_TX25(I):=HZ_TRANS_PKG.EXACT(H_TX25(I),NULL, 'CONTACT_SOURCE_SYSTEM_REF','CONTACTS');
3784 END LOOP;
3785 log ('Done');
3786
3787 l_st := 1;
3788 l_en := H_ORG_CONTACT_ID.COUNT;
3789
3790 IF p_operation = 'C' THEN
3791 BEGIN
3792 log ('Inserting Data into HZ_STAGED_CONTACTS...',FALSE);
3793 FORALL I in l_st..l_en SAVE EXCEPTIONS
3794 INSERT INTO HZ_STAGED_CONTACTS (
3795 ORG_CONTACT_ID
3796 ,PARTY_ID
3797 ,STATUS_FLAG
3798 ,TX2
3799 ,TX5
3800 ,TX6
3801 ,TX11
3802 ,TX22
3803 ,TX23
3804 ,TX24
3805 ,TX25
3806 ,TX156
3807 ) VALUES (
3808 H_ORG_CONTACT_ID(I)
3809 ,H_C_PARTY_ID(I)
3810 ,H_STATUS(I)
3811 ,decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
3812 ,decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
3813 ,decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
3814 ,decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
3815 ,decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
3816 ,decode(H_TX23(I),null,H_TX23(I),H_TX23(I)||' ')
3817 ,decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
3818 ,decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
3819 ,decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
3820 );
3821 log ('Done');
3822 EXCEPTION WHEN bulk_errors THEN
3823 l_err_count := SQL%BULK_EXCEPTIONS.COUNT;
3824 FOR indx IN 1..l_err_count LOOP
3825 l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3826 l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3827 IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3828 log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3829 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='C' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3830 ELSE
3831 IF p_bulk_sync_type = 'DQM_SYNC' THEN
3832 UPDATE hz_dqm_sync_interface
3833 SET error_data = l_sql_errm
3834 ,staged_flag = decode (error_data, NULL, 'N', 'E')
3835 WHERE rowid = H_ROWID(l_err_index);
3836 ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3837 -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3838 insert_dqm_sync_error_rec(H_C_PARTY_ID(l_err_index), H_ORG_CONTACT_ID(l_err_index), NULL, NULL, 'CONTACTS', p_operation, 'E', 'N', l_sql_errm);
3839 END IF;
3840 END IF;
3841 END LOOP;
3842 END;
3843 ELSIF p_operation = 'U' THEN
3844 BEGIN
3845 log ('Updating Data in HZ_STAGED_CONTACTS...',FALSE);
3846 FORALL I in l_st..l_en SAVE EXCEPTIONS
3847 UPDATE HZ_STAGED_CONTACTS SET
3848 concat_col = concat_col
3849 ,status_flag = H_STATUS(I)
3850 ,TX2=decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
3851 ,TX5=decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
3852 ,TX6=decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
3853 ,TX11=decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
3854 ,TX22=decode(H_TX22(I),null,H_TX22(I),H_TX22(I)||' ')
3855 ,TX23=decode(H_TX23(I),null,H_TX23(I),H_TX23(I)||' ')
3856 ,TX24=decode(H_TX24(I),null,H_TX24(I),H_TX24(I)||' ')
3857 ,TX25=decode(H_TX25(I),null,H_TX25(I),H_TX25(I)||' ')
3858 ,TX156=decode(H_TX156(I),null,H_TX156(I),H_TX156(I)||' ')
3859 WHERE ORG_CONTACT_ID=H_ORG_CONTACT_ID(I);
3860 log ('Done');
3861 EXCEPTION WHEN bulk_errors THEN
3862 l_err_count := SQL%BULK_EXCEPTIONS.COUNT;
3863 FOR indx IN 1..l_err_count LOOP
3864 l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
3865 l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
3866 IF (instr(l_sql_errm,'ORA-00001')>0) THEN
3867 log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact with ORG_CONTACT_ID - '||H_ORG_CONTACT_ID(l_err_index));
3868 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACTS' AND OPERATION='U' AND RECORD_ID=H_ORG_CONTACT_ID(l_err_index);
3869 ELSE
3870 IF p_bulk_sync_type = 'DQM_SYNC' THEN
3871 UPDATE hz_dqm_sync_interface
3872 SET error_data = l_sql_errm
3873 ,staged_flag = decode (error_data, NULL, 'N', 'E')
3874 WHERE rowid = H_ROWID(l_err_index);
3875 ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
3876 -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
3877 insert_dqm_sync_error_rec(H_C_PARTY_ID(l_err_index), H_ORG_CONTACT_ID(l_err_index), NULL, NULL, 'CONTACTS', p_operation, 'E', 'N', l_sql_errm);
3878 END IF;
3879 END IF;
3880 END LOOP;
3881 END;
3882 END IF;
3883
3884 IF l_last_fetch THEN
3885 -- Update HZ_STAGED_PARTIES, if corresponding child entity records
3886 -- CONTACTS (in this case), have been inserted/updated
3887
3888 log ('Updating D_CT column to SYNC in HZ_STAGED_PARTIES table for all related records...',FALSE);
3889 --Fix for bug 5048604, to update concat_col during update of denorm column
3890 FORALL I IN H_ORG_CONTACT_ID.FIRST..H_ORG_CONTACT_ID.LAST
3891 UPDATE HZ_STAGED_PARTIES set
3892 D_CT = 'SYNC'
3893 ,CONCAT_COL = CONCAT_COL
3894 WHERE PARTY_ID = H_C_PARTY_ID(I);
3895 log ('Done');
3896
3897 -- REPURI. Bug 4884742.
3898 -- Bulk Insert of Import Contacts into Shadow Sync Interface table
3899 -- if Shadow Staging has already run and completed successfully
3900 IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
3901 (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
3902 BEGIN
3903 -- REPURI. Bug 4968126.
3904 -- Using the Merge instead of Insert statement
3905 -- so that duplicate records dont get inserted.
3906 log ('Merging data into HZ_DQM_SH_SYNC_INTERFACE...',FALSE);
3907 FORALL I in l_st..l_en
3908 MERGE INTO hz_dqm_sh_sync_interface S
3909 USING (
3910 SELECT
3911 H_C_PARTY_ID(I) AS party_id
3912 ,H_ORG_CONTACT_ID(I) AS record_id
3913 FROM dual ) T
3914 ON (S.entity = 'CONTACTS' AND
3915 S.party_id = T.party_id AND
3916 S.record_id = T.record_id AND
3917 S.staged_flag <> 'E')
3918 WHEN NOT MATCHED THEN
3919 INSERT (
3920 PARTY_ID,
3921 RECORD_ID,
3922 PARTY_SITE_ID,
3923 ORG_CONTACT_ID,
3924 ENTITY,
3925 OPERATION,
3926 STAGED_FLAG,
3927 REALTIME_SYNC_FLAG,
3928 CREATED_BY,
3929 CREATION_DATE,
3930 LAST_UPDATE_LOGIN,
3931 LAST_UPDATE_DATE,
3932 LAST_UPDATED_BY,
3933 SYNC_INTERFACE_NUM
3934 ) VALUES (
3935 H_C_PARTY_ID(I),
3936 H_ORG_CONTACT_ID(I),
3937 NULL,
3938 NULL,
3939 'CONTACTS',
3940 p_operation,
3941 'N',
3942 'N',
3943 hz_utility_pub.created_by,
3944 hz_utility_pub.creation_date,
3945 hz_utility_pub.last_update_login,
3946 hz_utility_pub.last_update_date,
3947 hz_utility_pub.user_id,
3948 HZ_DQM_SH_SYNC_INTERFACE_S.nextval
3949 );
3950 log ('Done');
3951 EXCEPTION WHEN OTHERS THEN
3952 log ('Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table');
3953 log ('Eror Message is - '|| sqlerrm);
3954 END;
3955 END IF;
3956
3957 FND_CONCURRENT.AF_Commit;
3958 EXIT;
3959 END IF;
3960
3961 FND_CONCURRENT.AF_Commit;
3962
3963 END LOOP;
3964 log ('End Synchronizing Contacts');
3965 END;
3966
3967 PROCEDURE open_sync_cpt_cursor (
3968 p_operation IN VARCHAR2,
3969 p_from_rec IN VARCHAR2,
3970 p_to_rec IN VARCHAR2,
3971 x_sync_cpt_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
3972 BEGIN
3973 OPEN x_sync_cpt_cur FOR
3974 SELECT
3975 /*+ ORDERED USE_NL(cp) */
3976 cp.CONTACT_POINT_ID
3977 ,dsi.party_id
3978 ,dsi.party_site_id
3979 ,dsi.org_contact_id
3980 ,cp.CONTACT_POINT_TYPE
3981 ,cp.STATUS
3982 ,dsi.ROWID
3983 ,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
3984 ,cp.PHONE_NUMBER
3985 ,cp.PHONE_AREA_CODE
3986 ,cp.PHONE_COUNTRY_CODE
3987 ,cp.EMAIL_ADDRESS
3988 ,cp.URL
3989 ,cp.PRIMARY_FLAG
3990 ,translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
3991 ,cp.PHONE_LINE_TYPE
3992 ,cp.STATUS
3993 ,cp.CONTACT_POINT_PURPOSE
3994 FROM HZ_DQM_SYNC_INTERFACE dsi,HZ_CONTACT_POINTS cp
3995 WHERE dsi.record_id = cp.contact_point_id
3996 AND dsi.entity = 'CONTACT_POINTS'
3997 AND dsi.operation = p_operation
3998 AND dsi.staged_flag = 'N'
3999 AND dsi.sync_interface_num >= p_from_rec
4000 AND dsi.sync_interface_num <= p_to_rec
4001 AND (cp.status is null OR cp.status = 'A' or cp.status = 'I');
4002 END;
4003
4004 PROCEDURE sync_all_contact_points (
4005 p_operation IN VARCHAR2,
4006 p_bulk_sync_type IN VARCHAR2,
4007 p_sync_all_cpt_cur IN HZ_DQM_SYNC.SyncCurTyp) IS
4008
4009 l_limit NUMBER := 200;
4010 l_last_fetch BOOLEAN := FALSE;
4011 l_sql_errm VARCHAR2(2000);
4012 l_st NUMBER;
4013 l_en NUMBER;
4014 l_err_index NUMBER;
4015 l_err_count NUMBER;
4016
4017 bulk_errors EXCEPTION;
4018 PRAGMA EXCEPTION_INIT(bulk_errors, -24381);
4019
4020 BEGIN
4021 log ('Begin Synchronizing Contact Points');
4022 LOOP
4023 log ('Bulk Collecting Contact Points Data...',FALSE);
4024 FETCH p_sync_all_cpt_cur BULK COLLECT INTO
4025 H_CONTACT_POINT_ID
4026 ,H_CPT_PARTY_ID
4027 ,H_CPT_PARTY_SITE_ID
4028 ,H_CPT_ORG_CONTACT_ID
4029 ,H_CONTACT_POINT_TYPE
4030 ,H_STATUS
4031 ,H_ROWID
4032 ,H_TX1
4033 ,H_TX2
4034 ,H_TX3
4035 ,H_TX4
4036 ,H_TX5
4037 ,H_TX7
4038 ,H_TX9
4039 ,H_TX10
4040 ,H_TX11
4041 ,H_TX12
4042 ,H_TX13
4043 LIMIT l_limit;
4044 log ('Done');
4045
4046 IF p_sync_all_cpt_cur%NOTFOUND THEN
4047 l_last_fetch:=TRUE;
4048 END IF;
4049
4050 IF H_CONTACT_POINT_ID.COUNT=0 AND l_last_fetch THEN
4051 EXIT;
4052 END IF;
4053
4054 log ('Synchronizing for '||H_CONTACT_POINT_ID.COUNT||' Contact Points');
4055 log ('Populating Contact Points Transformation Functions into Arrays...',FALSE);
4056
4057 FOR I in H_CONTACT_POINT_ID.FIRST..H_CONTACT_POINT_ID.LAST LOOP
4058
4059 H_TX14(I):=HZ_PARTY_ACQUIRE.get_ssm_mappings(H_CONTACT_POINT_ID(I),'CONTACT_POINTS','CPT_SOURCE_SYSTEM_REF', 'STAGE');
4060 H_TX6(I):=HZ_TRANS_PKG.CLEANSED_EMAIL(H_TX5(I),NULL, 'EMAIL_ADDRESS','CONTACT_POINTS','STAGE');
4061 H_TX8(I):=HZ_TRANS_PKG.CLEANSED_URL(H_TX7(I),NULL, 'URL','CONTACT_POINTS','STAGE');
4062 H_TX158(I):=HZ_TRANS_PKG.REVERSE_PHONE_NUMBER(H_TX10(I),NULL, 'RAW_PHONE_NUMBER','CONTACT_POINTS');
4063 H_TX1(I):=HZ_TRANS_PKG.RM_SPLCHAR_CTX(H_TX1(I),NULL, 'FLEX_FORMAT_PHONE_NUMBER','CONTACT_POINTS','STAGE');
4064 H_TX2(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX2(I),NULL, 'PHONE_NUMBER','CONTACT_POINTS','STAGE');
4065 H_TX3(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX3(I),NULL, 'PHONE_AREA_CODE','CONTACT_POINTS','STAGE');
4066 H_TX4(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX4(I),NULL, 'PHONE_COUNTRY_CODE','CONTACT_POINTS','STAGE');
4067 H_TX5(I):=HZ_TRANS_PKG.EXACT_EMAIL(H_TX5(I),NULL, 'EMAIL_ADDRESS','CONTACT_POINTS');
4068 H_TX7(I):=HZ_TRANS_PKG.EXACT_URL(H_TX7(I),NULL, 'URL','CONTACT_POINTS');
4069 H_TX9(I):=HZ_TRANS_PKG.EXACT(H_TX9(I),NULL, 'PRIMARY_FLAG','CONTACT_POINTS');
4070 H_TX10(I):=HZ_TRANS_PKG.RM_SPLCHAR(H_TX10(I),NULL, 'RAW_PHONE_NUMBER','CONTACT_POINTS','STAGE');
4071 H_TX11(I):=HZ_TRANS_PKG.EXACT(H_TX11(I),NULL, 'PHONE_LINE_TYPE','CONTACT_POINTS');
4072 H_TX12(I):=HZ_TRANS_PKG.EXACT(H_TX12(I),NULL, 'STATUS','CONTACT_POINTS');
4073 H_TX13(I):=HZ_TRANS_PKG.EXACT(H_TX13(I),NULL, 'CONTACT_POINT_PURPOSE','CONTACT_POINTS');
4074 H_TX14(I):=HZ_TRANS_PKG.EXACT(H_TX14(I),NULL, 'CPT_SOURCE_SYSTEM_REF','CONTACT_POINTS');
4075 END LOOP;
4076 log ('Done');
4077
4078 l_st := 1;
4079 l_en := H_CONTACT_POINT_ID.COUNT;
4080
4081 IF p_operation = 'C' THEN
4082 BEGIN
4083 log ('Inserting Data into HZ_STAGED_CONTACT_POINTS...',FALSE);
4084 FORALL I in l_st..l_en SAVE EXCEPTIONS
4085 INSERT INTO HZ_STAGED_CONTACT_POINTS (
4086 CONTACT_POINT_ID
4087 ,PARTY_ID
4088 ,PARTY_SITE_ID
4089 ,ORG_CONTACT_ID
4090 ,CONTACT_POINT_TYPE
4091 ,STATUS_FLAG
4092 ,TX1
4093 ,TX2
4094 ,TX3
4095 ,TX4
4096 ,TX5
4097 ,TX6
4098 ,TX7
4099 ,TX8
4100 ,TX9
4101 ,TX10
4102 ,TX11
4103 ,TX12
4104 ,TX13
4105 ,TX14
4106 ,TX158
4107 ) VALUES (
4108 H_CONTACT_POINT_ID(I)
4109 ,H_CPT_PARTY_ID(I)
4110 ,H_CPT_PARTY_SITE_ID(I)
4111 ,H_CPT_ORG_CONTACT_ID(I)
4112 ,H_CONTACT_POINT_TYPE(I)
4113 ,H_STATUS(I)
4114 ,decode(H_TX1(I),null,H_TX1(I),H_TX1(I)||' ')
4115 ,decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
4116 ,decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
4117 ,decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
4118 ,decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
4119 ,decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
4120 ,decode(H_TX7(I),null,H_TX7(I),H_TX7(I)||' ')
4121 ,decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
4122 ,decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
4123 ,decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
4124 ,decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
4125 ,decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
4126 ,decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
4127 ,decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
4128 ,decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
4129 );
4130 log ('Done');
4131 EXCEPTION WHEN bulk_errors THEN
4132 l_err_count := SQL%BULK_EXCEPTIONS.COUNT;
4133 FOR indx IN 1..l_err_count LOOP
4134 l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
4135 l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
4136 IF (instr(l_sql_errm,'ORA-00001')>0) THEN
4137 log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4138 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='C' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4139 ELSE
4140 IF p_bulk_sync_type = 'DQM_SYNC' THEN
4141 UPDATE hz_dqm_sync_interface
4142 SET error_data = l_sql_errm
4143 ,staged_flag = decode (error_data, NULL, 'N', 'E')
4144 WHERE rowid = H_ROWID(l_err_index);
4145 ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4146 -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
4147 insert_dqm_sync_error_rec(H_CPT_PARTY_ID(l_err_index), H_CONTACT_POINT_ID(l_err_index), H_CPT_PARTY_SITE_ID(l_err_index), H_CPT_ORG_CONTACT_ID(l_err_index), 'CONTACT_POINTS', p_operation, 'E', 'N', l_sql_errm);
4148 END IF;
4149 END IF;
4150 END LOOP;
4151 END;
4152 ELSIF p_operation = 'U' THEN
4153 BEGIN
4154 log ('Updating Data in HZ_STAGED_CONTACT_POINTS...',FALSE);
4155 FORALL I in l_st..l_en SAVE EXCEPTIONS
4156 UPDATE HZ_STAGED_CONTACT_POINTS SET
4157 concat_col = concat_col
4158 ,status_flag = H_STATUS(I)
4159 ,TX1=decode(H_TX1(I),null,H_TX1(I),H_TX1(I)||' ')
4160 ,TX2=decode(H_TX2(I),null,H_TX2(I),H_TX2(I)||' ')
4161 ,TX3=decode(H_TX3(I),null,H_TX3(I),H_TX3(I)||' ')
4162 ,TX4=decode(H_TX4(I),null,H_TX4(I),H_TX4(I)||' ')
4163 ,TX5=decode(H_TX5(I),null,H_TX5(I),H_TX5(I)||' ')
4164 ,TX6=decode(H_TX6(I),null,H_TX6(I),H_TX6(I)||' ')
4165 ,TX7=decode(H_TX7(I),null,H_TX7(I),H_TX7(I)||' ')
4166 ,TX8=decode(H_TX8(I),null,H_TX8(I),H_TX8(I)||' ')
4167 ,TX9=decode(H_TX9(I),null,H_TX9(I),H_TX9(I)||' ')
4168 ,TX10=decode(H_TX10(I),null,H_TX10(I),H_TX10(I)||' ')
4169 ,TX11=decode(H_TX11(I),null,H_TX11(I),H_TX11(I)||' ')
4170 ,TX12=decode(H_TX12(I),null,H_TX12(I),H_TX12(I)||' ')
4171 ,TX13=decode(H_TX13(I),null,H_TX13(I),H_TX13(I)||' ')
4172 ,TX14=decode(H_TX14(I),null,H_TX14(I),H_TX14(I)||' ')
4173 ,TX158=decode(H_TX158(I),null,H_TX158(I),H_TX158(I)||' ')
4174 WHERE CONTACT_POINT_ID=H_CONTACT_POINT_ID(I);
4175 log ('Done');
4176 EXCEPTION WHEN bulk_errors THEN
4177 l_err_count := SQL%BULK_EXCEPTIONS.COUNT;
4178 FOR indx IN 1..l_err_count LOOP
4179 l_err_index := SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX;
4180 l_sql_errm := sqlerrm(-SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
4181 IF (instr(l_sql_errm,'ORA-00001')>0) THEN
4182 log ('Exception DUP_VAL_ON_INDEX occured while inserting a Contact Point with CONTACT_POINT_ID - '||H_CONTACT_POINT_ID(l_err_index));
4183 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE ENTITY='CONTACT_POINTS' AND OPERATION='U' AND RECORD_ID=H_CONTACT_POINT_ID(l_err_index);
4184 ELSE
4185 IF p_bulk_sync_type = 'DQM_SYNC' THEN
4186 UPDATE hz_dqm_sync_interface
4187 SET error_data = l_sql_errm
4188 ,staged_flag = decode (error_data, NULL, 'N', 'E')
4189 WHERE rowid = H_ROWID(l_err_index);
4190 ELSIF p_bulk_sync_type = 'IMPORT_SYNC' THEN
4191 -- Insert the Error Record into HZ_DQM_SYNC_INTERFACE table
4192 insert_dqm_sync_error_rec(H_CPT_PARTY_ID(l_err_index), H_CONTACT_POINT_ID(l_err_index), H_CPT_PARTY_SITE_ID(l_err_index), H_CPT_ORG_CONTACT_ID(l_err_index), 'CONTACT_POINTS', p_operation, 'E', 'N', l_sql_errm);
4193 END IF;
4194 END IF;
4195 END LOOP;
4196 END;
4197 END IF;
4198
4199 IF l_last_fetch THEN
4200 -- Update HZ_STAGED_PARTIES, if corresponding child entity records
4201 -- CONTACT_POINTS (in this case), have been inserted/updated
4202
4203 log ('Updating D_CPT column to SYNC in HZ_STAGED_PARTIES table for all related records...',FALSE);
4204 --Fix for bug 5048604, to update concat_col during update of denorm column
4205 FORALL I IN H_CONTACT_POINT_ID.FIRST..H_CONTACT_POINT_ID.LAST
4206 UPDATE HZ_STAGED_PARTIES set
4207 D_CPT = 'SYNC'
4208 ,CONCAT_COL = CONCAT_COL
4209 WHERE PARTY_ID = H_CPT_PARTY_ID(I);
4210 log ('Done');
4211
4212 -- REPURI. Bug 4884742.
4213 -- Bulk Insert the Import of Contact Points into Shadow Sync Interface table
4214 -- if Shadow Staging has already run and completed successfully
4215 IF ((p_bulk_sync_type = 'IMPORT_SYNC') AND
4216 (HZ_DQM_SYNC.is_shadow_staging_complete)) THEN
4217 BEGIN
4218 -- REPURI. Bug 4968126.
4219 -- Using the Merge instead of Insert statement
4220 -- so that duplicate records dont get inserted.
4221 log ('Merging data into HZ_DQM_SH_SYNC_INTERFACE...',FALSE);
4222 FORALL I in l_st..l_en
4223 MERGE INTO hz_dqm_sh_sync_interface S
4224 USING (
4225 SELECT
4226 H_CPT_PARTY_ID(I) AS party_id
4227 ,H_CONTACT_POINT_ID(I) AS record_id
4228 ,H_CPT_PARTY_SITE_ID(I) AS party_site_id
4229 ,H_CPT_ORG_CONTACT_ID(I) AS org_contact_id
4230 FROM dual ) T
4231 ON (S.entity = 'CONTACT_POINTS' AND
4232 S.party_id = T.party_id AND
4233 S.record_id = T.record_id AND
4234 NVL(S.party_site_id, -99) = NVL(T.party_site_id, -99) AND
4235 NVL(S.org_contact_id, -99) = NVL(T.org_contact_id, -99) AND
4236 S.staged_flag <> 'E')
4237 WHEN NOT MATCHED THEN
4238 INSERT (
4239 PARTY_ID,
4240 RECORD_ID,
4241 PARTY_SITE_ID,
4242 ORG_CONTACT_ID,
4243 ENTITY,
4244 OPERATION,
4245 STAGED_FLAG,
4246 REALTIME_SYNC_FLAG,
4247 CREATED_BY,
4248 CREATION_DATE,
4249 LAST_UPDATE_LOGIN,
4250 LAST_UPDATE_DATE,
4251 LAST_UPDATED_BY,
4252 SYNC_INTERFACE_NUM
4253 ) VALUES (
4254 H_CPT_PARTY_ID(I),
4255 H_CONTACT_POINT_ID(I),
4256 H_CPT_PARTY_SITE_ID(I),
4257 H_CPT_ORG_CONTACT_ID(I),
4258 'CONTACT_POINTS',
4259 p_operation,
4260 'N',
4261 'N',
4262 hz_utility_pub.created_by,
4263 hz_utility_pub.creation_date,
4264 hz_utility_pub.last_update_login,
4265 hz_utility_pub.last_update_date,
4266 hz_utility_pub.user_id,
4267 HZ_DQM_SH_SYNC_INTERFACE_S.nextval
4268 );
4269 log ('Done');
4270 EXCEPTION WHEN OTHERS THEN
4271 log ('Exception occured while inserting data into HZ_DQM_SH_SYNC_INTERFACE Table');
4272 log ('Eror Message is - '|| sqlerrm);
4273 END;
4274 END IF;
4275
4276 FND_CONCURRENT.AF_Commit;
4277 EXIT;
4278 END IF;
4279
4280 FND_CONCURRENT.AF_Commit;
4281
4282 END LOOP;
4283 log ('End Synchronizing Contact Points');
4284 END;
4285
4286 PROCEDURE open_bulk_imp_sync_party_cur(
4287 p_batch_id IN NUMBER,
4288 p_batch_mode_flag IN VARCHAR2,
4289 p_from_osr IN VARCHAR2,
4290 p_to_osr IN VARCHAR2,
4291 p_os IN VARCHAR2,
4292 p_party_type IN VARCHAR2,
4293 p_operation IN VARCHAR2,
4294 x_sync_party_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4295 BEGIN
4296 IF p_party_type = 'ORGANIZATION' THEN
4297 open x_sync_party_cur FOR
4298 SELECT p.PARTY_ID, p.STATUS, p.ROWID
4299 ,p.PARTY_NAME
4300 ,p.PARTY_NUMBER
4301 ,p.PARTY_TYPE
4302 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
4303 ,op.DUNS_NUMBER_C
4304 ,op.TAX_NAME
4305 ,op.TAX_REFERENCE
4306 ,op.JGZZ_FISCAL_CODE
4307 ,op.SIC_CODE
4308 ,op.SIC_CODE_TYPE
4309 ,p.CATEGORY_CODE
4310 ,p.REFERENCE_USE_FLAG
4311 ,op.CORPORATION_CLASS
4312 FROM HZ_PARTIES p, HZ_IMP_PARTIES_SG ps, HZ_IMP_BATCH_DETAILS bd
4313 ,HZ_ORGANIZATION_PROFILES op
4314 WHERE p.request_id = bd.main_conc_req_id
4315 AND bd.batch_id = ps.batch_id
4316 AND p.PARTY_TYPE = 'ORGANIZATION'
4317 AND p.party_id = ps.party_id
4318 AND ps.batch_id = p_batch_id
4319 AND ps.party_orig_system = p_os
4320 AND ps.batch_mode_flag = p_batch_mode_flag
4321 AND ps.action_flag = p_operation
4322 AND p.party_id = op.party_id
4323 AND ps.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
4324 AND (p.status = 'M' OR op.effective_end_date IS NULL);
4325 ELSIF p_party_type = 'PERSON' THEN
4326 open x_sync_party_cur FOR
4327 SELECT p.PARTY_ID, p.STATUS, p.ROWID
4328 ,p.PARTY_NAME
4329 ,p.PARTY_NUMBER
4330 ,p.PARTY_TYPE
4331 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
4332 ,NULL
4333 ,pe.TAX_NAME
4334 ,pe.TAX_REFERENCE
4335 ,pe.JGZZ_FISCAL_CODE
4336 ,NULL
4337 ,NULL
4338 ,p.CATEGORY_CODE
4339 ,p.REFERENCE_USE_FLAG
4340 ,NULL
4341 FROM HZ_PARTIES p, HZ_IMP_PARTIES_SG ps, HZ_IMP_BATCH_DETAILS bd
4342 ,HZ_PERSON_PROFILES pe
4343 WHERE p.request_id = bd.main_conc_req_id
4344 AND bd.batch_id = ps.batch_id
4345 AND p.PARTY_TYPE = 'PERSON'
4346 AND p.party_id = ps.party_id
4347 AND ps.batch_id = p_batch_id
4348 AND ps.party_orig_system = p_os
4349 AND ps.batch_mode_flag = p_batch_mode_flag
4350 AND ps.action_flag = p_operation
4351 AND p.party_id = pe.party_id
4352 AND ps.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
4353 AND (p.status = 'M' OR pe.effective_end_date IS NULL);
4354 ELSE
4355 open x_sync_party_cur FOR
4356 SELECT p.PARTY_ID, p.STATUS, p.ROWID
4357 ,p.PARTY_NAME
4358 ,p.PARTY_NUMBER
4359 ,p.PARTY_TYPE
4360 ,p.PARTY_NAME || ' ' || p.KNOWN_AS || ' ' || p.KNOWN_AS2 || ' ' || p.KNOWN_AS3 || ' '|| p.KNOWN_AS4 || ' '|| p.KNOWN_AS5
4361 ,NULL
4362 ,NULL
4363 ,NULL
4364 ,NULL
4365 ,NULL
4366 ,NULL
4367 ,p.CATEGORY_CODE
4368 ,p.REFERENCE_USE_FLAG
4369 ,NULL
4370 FROM HZ_PARTIES p, HZ_IMP_PARTIES_SG ps, HZ_IMP_BATCH_DETAILS bd
4371 WHERE p.request_id = bd.main_conc_req_id
4372 AND bd.batch_id = ps.batch_id
4373 AND p.party_id = ps.party_id
4374 AND ps.batch_id = p_batch_id
4375 AND ps.party_orig_system = p_os
4376 AND ps.batch_mode_flag = p_batch_mode_flag
4377 AND ps.action_flag = p_operation
4378 AND p.party_type <> 'PERSON'
4379 AND p.party_type <> 'ORGANIZATION'
4380 AND p.party_type <> 'PARTY_RELATIONSHIP'
4381 AND ps.party_orig_system_reference between p_from_osr and p_to_osr;
4382 END IF;
4383
4384 hz_trans_pkg.set_party_type(p_party_type);
4385
4386 END open_bulk_imp_sync_party_cur;
4387
4388
4389 PROCEDURE open_bulk_imp_sync_psite_cur (
4390 p_batch_id IN NUMBER,
4391 p_batch_mode_flag IN VARCHAR2,
4392 p_from_osr IN VARCHAR2,
4393 p_to_osr IN VARCHAR2,
4394 p_os IN VARCHAR2,
4395 p_operation IN VARCHAR2,
4396 x_sync_party_site_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4397 BEGIN
4398 OPEN x_sync_party_site_cur FOR
4399 SELECT /*+ ORDERED USE_NL(ps l) */
4400 ps.PARTY_SITE_ID
4401 ,ps.PARTY_ID
4402 ,NULL
4403 ,ps.STATUS
4404 ,ps.ROWID
4405 ,rtrim(l.address1 || ' ' || l.address2 || ' ' || l.address3 || ' ' || l.address4)
4406 ,l.CITY
4407 ,l.POSTAL_CODE
4408 ,l.PROVINCE
4409 ,l.STATE
4410 ,ps.PARTY_SITE_NUMBER
4411 ,ps.PARTY_SITE_NAME
4412 ,l.COUNTY
4413 ,l.COUNTRY
4414 ,ps.IDENTIFYING_ADDRESS_FLAG
4415 ,ps.STATUS
4416 ,l.ADDRESS1
4417 FROM hz_locations l, hz_party_sites ps,
4418 hz_imp_addresses_sg addr_sg, hz_imp_batch_details bd
4419 WHERE l.request_id = bd.main_conc_req_id
4420 AND bd.batch_id = addr_sg.batch_id
4421 AND l.location_id = ps.location_id
4422 AND addr_sg.batch_id = p_batch_id
4423 AND addr_sg.batch_mode_flag = p_batch_mode_flag
4424 AND addr_sg.party_orig_system = p_os
4425 AND addr_sg.party_site_id = ps.party_site_id
4426 AND addr_sg.action_flag = p_operation
4427 AND addr_sg.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
4428 AND (ps.status IS NULL OR ps.status = 'A' OR ps.status = 'I');
4429
4430 END open_bulk_imp_sync_psite_cur;
4431
4432
4433 PROCEDURE open_bulk_imp_sync_ct_cur (
4434 p_batch_id IN NUMBER,
4435 p_batch_mode_flag IN VARCHAR2,
4436 p_from_osr IN VARCHAR2,
4437 p_to_osr IN VARCHAR2,
4438 p_os IN VARCHAR2,
4439 p_operation IN VARCHAR2,
4440 x_sync_contact_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4441 BEGIN
4442 OPEN x_sync_contact_cur FOR
4443 SELECT
4444 /*+ ORDERED USE_NL(R OC PP)*/
4445 oc.ORG_CONTACT_ID
4446 ,r.OBJECT_ID
4447 ,r.PARTY_ID
4448 ,r.STATUS
4449 ,oc.ROWID
4450 ,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
4451 ,oc.CONTACT_NUMBER
4452 ,oc.JOB_TITLE
4453 FROM hz_org_contacts oc, hz_imp_contacts_sg ocsg, hz_imp_batch_details bd,
4454 hz_relationships r, hz_person_profiles pp
4455 WHERE ocsg.batch_mode_flag = p_batch_mode_flag
4456 AND oc.party_relationship_id = r.relationship_id
4457 AND ocsg.batch_id = p_batch_id
4458 AND ocsg.sub_orig_system = p_os
4459 AND ocsg.contact_id = oc.org_contact_id
4460 AND oc.request_id = bd.main_conc_req_id
4461 AND bd.batch_id = ocsg.batch_id
4462 AND r.subject_id = pp.party_id
4463 AND r.subject_type = 'PERSON'
4464 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4465 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4466 AND DIRECTIONAL_FLAG = 'F'
4467 AND ocsg.action_flag = p_operation
4468 AND pp.effective_end_date IS NULL
4469 AND ocsg.sub_orig_system_reference BETWEEN p_from_osr AND p_to_osr
4470 AND (oc.status IS NULL OR oc.status = 'A' OR oc.status = 'I')
4471 AND (r.status IS NULL OR r.status = 'A' OR r.status = 'I')
4472 UNION
4473 SELECT
4474 /*+ ORDERED USE_NL(R OC PP)*/
4475 oc.ORG_CONTACT_ID
4476 ,r.OBJECT_ID
4477 ,r.PARTY_ID
4478 ,r.STATUS
4479 ,oc.ROWID
4480 ,rtrim(pp.person_first_name || ' ' || pp.person_last_name)
4481 ,oc.CONTACT_NUMBER
4482 ,oc.JOB_TITLE
4483 FROM hz_org_contacts oc, hz_imp_relships_sg rsg, hz_imp_batch_details bd
4484 ,hz_relationships r, hz_person_profiles pp
4485 WHERE rsg.batch_mode_flag = p_batch_mode_flag
4486 AND rsg.batch_id = p_batch_id
4487 AND rsg.sub_orig_system = p_os
4488 AND rsg.relationship_id = oc.party_relationship_id
4489 AND oc.request_id = bd.main_conc_req_id
4490 AND bd.batch_id = rsg.batch_id
4491 AND rsg.relationship_id = r.relationship_id
4492 AND r.directional_flag = 'F'
4493 AND r.subject_id = pp.party_id
4494 AND r.subject_type = 'PERSON'
4495 AND r.object_type = 'ORGANIZATION'
4496 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4497 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4498 AND rsg.action_flag = p_operation
4499 AND pp.effective_end_date IS NULL
4500 AND rsg.sub_orig_system_reference BETWEEN p_from_osr AND p_to_osr
4501 AND (oc.status IS NULL OR oc.status = 'A' OR oc.status = 'I')
4502 AND (r.status IS NULL OR r.status = 'A' OR r.status = 'I');
4503 END open_bulk_imp_sync_ct_cur;
4504
4505 PROCEDURE open_bulk_imp_sync_cpt_cur (
4506 p_batch_id IN NUMBER,
4507 p_batch_mode_flag IN VARCHAR2,
4508 p_from_osr IN VARCHAR2,
4509 p_to_osr IN VARCHAR2,
4510 p_os IN VARCHAR2,
4511 p_operation IN VARCHAR2,
4512 x_sync_cpt_cur IN OUT HZ_DQM_SYNC.SyncCurTyp) IS
4513 BEGIN
4514 OPEN x_sync_cpt_cur FOR
4515 SELECT
4516 /*+ ORDERED USE_NL(cp) */
4517 cp.CONTACT_POINT_ID
4518 ,cps.party_id
4519 ,decode (cp.owner_table_name, 'HZ_PARTY_SITES', cp.owner_table_id, NULL) party_site_id
4520 ,NULL
4521 ,cp.CONTACT_POINT_TYPE
4522 ,cp.STATUS
4523 ,cp.ROWID
4524 ,translate(phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ') || ' ' || translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
4525 ,cp.PHONE_NUMBER
4526 ,cp.PHONE_AREA_CODE
4527 ,cp.PHONE_COUNTRY_CODE
4528 ,cp.EMAIL_ADDRESS
4529 ,cp.URL
4530 ,cp.PRIMARY_FLAG
4531 ,translate(phone_country_code|| ' ' || phone_area_code||' ' || phone_number,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')
4532 ,cp.PHONE_LINE_TYPE
4533 ,cp.STATUS
4534 ,cp.CONTACT_POINT_PURPOSE
4535 FROM hz_contact_points cp, hz_imp_contactpts_sg cps, hz_imp_batch_details bd
4536 WHERE cp.request_id = bd.main_conc_req_id
4537 AND bd.batch_id = cps.batch_id
4538 AND cp.contact_point_id = cps.contact_point_id
4539 AND cps.batch_id = p_batch_id
4540 AND cps.party_orig_system = p_os
4541 AND cps.batch_mode_flag = p_batch_mode_flag
4542 AND cps.action_flag = p_operation
4543 AND cps.party_orig_system_reference BETWEEN p_from_osr AND p_to_osr
4544 AND (cp.status IS NULL OR cp.status = 'A' OR cp.status = 'I');
4545
4546 END open_bulk_imp_sync_cpt_cur;
4547
4548 END;