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