1 PACKAGE BODY hz_dqm_sync AS
2 /* $Header: ARHDQSNB.pls 120.57.12020000.2 2012/07/19 10:06:22 ajaising ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Note that when this profile option is not set, the default value is Y
6 -- i.e., SYNC is assumed to be in REALTIME.
7 -- Need to look into the impact of this, for customers who do not need DQM out of the box.
8 ---------------------------------------------------------------------------------
9 L_REALTIME_SYNC_VALUE VARCHAR2(15) := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
10
11
12 PROCEDURE REALTIME_SYNC_INDEXES(i_party IN boolean,
13 i_party_sites IN boolean,
14 i_contacts IN boolean,
15 i_contact_points IN boolean
16 ) ;
17
18
19 PROCEDURE insert_interface_rec (
20 p_party_id IN NUMBER,
21 p_record_id IN NUMBER,
22 p_party_site_id IN NUMBER,
23 p_org_contact_id IN NUMBER,
24 p_entity IN VARCHAR2,
25 p_operation IN VARCHAR2,
26 p_staged_flag IN VARCHAR2 DEFAULT 'N'
27 );
28 PROCEDURE out(
29 message IN VARCHAR2,
30 newline IN BOOLEAN DEFAULT TRUE);
31
32 PROCEDURE log(
33 message IN VARCHAR2,
34 newline IN BOOLEAN DEFAULT TRUE);
35
36 PROCEDURE outandlog(
37 message IN VARCHAR2,
38 newline IN BOOLEAN DEFAULT TRUE);
39
40 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
41 RETURN VARCHAR2;
42
43 FUNCTION check_for_transaction RETURN VARCHAR2;
44
45 g_commit_counter NUMBER := 1;
46
47 -- VJN INTRODUCED FOR SYNC FUNCTIONALITY IN R12
48
49 -- This will take a request id and return TRUE if the concurrent program
50 -- is either Running or Pending
51 FUNCTION is_conc_complete ( p_request_id IN NUMBER) RETURN BOOLEAN ;
52
53 FUNCTION is_sync_success
54 (p_entity IN VARCHAR2
55 ,p_record_id IN NUMBER
56 ,p_party_id IN NUMBER
57 ) RETURN BOOLEAN ;
58
59 -- Will submit a concurrent request to call the Serial Sync Index Concurrent Program
60 PROCEDURE call_sync_index_serial IS
61 l_sub_request NUMBER ;
62 l_ignore_conc_limits VARCHAR2(80);
63 l_conc_req_limit VARCHAR2(80);
64 BEGIN
65 -- Fix for bug 5061761.
66 -- Check for Profile Value to decide if conc req limit per user can be ignored.
67 l_ignore_conc_limits := nvl(FND_PROFILE.VALUE('HZ_DQM_IGNORE_CONC_LIMITS'), 'N');
68 IF l_ignore_conc_limits = 'Y' THEN
69 l_conc_req_limit := nvl(FND_PROFILE.VALUE('CONC_REQUEST_LIMIT'), '0');
70 IF l_conc_req_limit <> '0' THEN
71 FND_PROFILE.PUT (
72 NAME => 'CONC_REQUEST_LIMIT',
73 VAL => NULL
74 );
75 END IF;
76 l_sub_request := FND_REQUEST.SUBMIT_REQUEST(
77 'AR',
78 'ARHDQMSS',
79 'DQM Serial Sync Index Program',
80 NULL,
81 FALSE
82 );
83 FND_PROFILE.PUT (
84 NAME => 'CONC_REQUEST_LIMIT',
85 VAL => l_conc_req_limit
86 );
87 ELSE
88 l_sub_request := FND_REQUEST.SUBMIT_REQUEST(
89 'AR',
90 'ARHDQMSS',
91 'DQM Serial Sync Index Program',
92 NULL,
93 FALSE
94 );
95 END IF;
96
97 EXCEPTION WHEN OTHERS THEN
98 NULL ;
99 END ;
100
101 PROCEDURE insert_into_interface(p_party_id IN NUMBER
102 ) IS
103 l_char NUMBER;
104 BEGIN
105 -- check if record already exists in HZ_DQM_SYNC_INTERFACE
106 BEGIN
107 select 'Y' into l_char
108 from hz_dqm_sync_interface
109 where party_id = p_party_id
110 and entity = 'PARTY'
111 and staged_flag in ('N', 'Y')
112 and rownum = 1;
113 EXCEPTION WHEN NO_DATA_FOUND THEN
114 insert_interface_rec (p_party_id, null, null, null, 'PARTY', 'U', 'Y');
115 END;
116 EXCEPTION WHEN others THEN
117 NULL;
118 END insert_into_interface;
119
120 -- REPURI. Bug 4884742. Introduced this function to check if Shadow Staging completely succesfully.
121 -- This function would return TRUE if the shadow staging program has run successfully
122
123 FUNCTION is_shadow_staging_complete RETURN BOOLEAN
124 IS
125 l_num NUMBER;
126 func_ret_status BOOLEAN := FALSE;
127
128 CURSOR c_sh_stage_log_count IS
129 SELECT 1
130 FROM hz_dqm_stage_log
131 WHERE operation = 'SHADOW_STAGING'
132 AND STEP = 'COMPLETE';
133
134 BEGIN
135 OPEN c_sh_stage_log_count;
136 FETCH c_sh_stage_log_count INTO l_num;
137 IF c_sh_stage_log_count%FOUND THEN
138 func_ret_status := TRUE;
139 END IF;
140 CLOSE c_sh_stage_log_count;
141
142 RETURN func_ret_status;
143 EXCEPTION WHEN OTHERS THEN
144 RETURN func_ret_status;
145 END is_shadow_staging_complete;
146
147
148 PROCEDURE set_to_batch_sync
149 IS
150 BEGIN
151 L_REALTIME_SYNC_VALUE := 'N' ;
152 END set_to_batch_sync;
153
154 FUNCTION is_prof_enable_for_sync
155 RETURN BOOLEAN
156 IS
157 l_prof VARCHAR2(1);
158 BEGIN
159 l_prof := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
160 IF (l_prof = 'Y') THEN
161 RETURN TRUE;
162 ELSE
163 RETURN FALSE;
164 END IF;
165 EXCEPTION WHEN OTHERS THEN
166 RETURN FALSE;
167 END is_prof_enable_for_sync;
168
169 PROCEDURE sync_index_realtime(
170 p_index_name IN VARCHAR2,
171 retcode OUT NOCOPY VARCHAR2,
172 err OUT NOCOPY VARCHAR2) IS
173
174 cursor l_party_cur is select rowid, party_id, record_id
175 from hz_dqm_sync_interface a
176 where a.staged_flag = 'Y'
177 and a.entity = 'PARTY' AND REALTIME_SYNC_FLAG='Y';
178 cursor l_ps_cur is select rowid, party_id, record_id
179 from hz_dqm_sync_interface a
180 where a.staged_flag = 'Y'
181 and a.entity = 'PARTY_SITES' AND REALTIME_SYNC_FLAG='Y';
182 cursor l_ct_cur is select rowid, party_id, record_id
183 from hz_dqm_sync_interface a
184 where a.staged_flag = 'Y'
185 and entity = 'CONTACTS' AND REALTIME_SYNC_FLAG='Y';
186 cursor l_cp_cur is select rowid, party_id, record_id
187 from hz_dqm_sync_interface a
188 where a.staged_flag = 'Y'
189 and entity = 'CONTACT_POINTS' AND REALTIME_SYNC_FLAG='Y';
190
191 l_limit NUMBER := 1000;
192 TYPE RowList IS TABLE OF VARCHAR2(255);
193 L_ROWID RowList;
194 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
195 L_PARTY_ID NumberList;
196 L_RECORD_ID NumberList;
197 l_last_fetch BOOLEAN := FALSE;
198 l_index_name VARCHAR2(100);
199
200 BEGIN
201 retcode := 0;
202 err := null;
203 l_index_name := lower(p_index_name);
204 IF (INSTRB(l_index_name,'hz_stage_parties_t1') > 0) THEN
205 ad_ctx_Ddl.Sync_Index ( p_index_name );
206 OPEN l_party_cur;
207 LOOP
208 FETCH l_party_cur BULK COLLECT INTO
209 L_ROWID
210 , L_PARTY_ID
211 , L_RECORD_ID LIMIT l_limit;
212 IF l_party_cur%NOTFOUND THEN
213 l_last_fetch:=TRUE;
214 END IF;
215 IF L_PARTY_ID.COUNT=0 AND l_last_fetch THEN
216 EXIT;
217 END IF;
218 FORALL I in L_PARTY_ID.FIRST..L_PARTY_ID.LAST
219 update hz_staged_parties a set concat_col = concat_col
220 where a.party_id = L_PARTY_ID(I);
221 FORALL I in L_PARTY_ID.FIRST..L_PARTY_ID.LAST
222 delete from hz_dqm_sync_interface
223 where rowid = L_ROWID(I);
224 ad_ctx_Ddl.Sync_Index ( p_index_name );
225 IF l_last_fetch THEN
226 EXIT;
227 END IF;
228 FND_CONCURRENT.AF_Commit;
229 END LOOP;
230 CLOSE l_party_cur;
231 ELSIF (INSTRB(l_index_name,'hz_stage_party_sites_t1') > 0) THEN
232 ad_ctx_Ddl.Sync_Index ( p_index_name );
233 OPEN l_ps_cur;
234 LOOP
235 FETCH l_ps_cur BULK COLLECT INTO
236 L_ROWID
237 , L_PARTY_ID
238 , L_RECORD_ID LIMIT l_limit;
239 IF l_ps_cur%NOTFOUND THEN
240 l_last_fetch:=TRUE;
241 END IF;
242 IF L_RECORD_ID.COUNT=0 AND l_last_fetch THEN
243 EXIT;
244 END IF;
245 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
246 update hz_staged_party_sites a set concat_col = concat_col
247 where a.party_site_id = L_RECORD_ID(I);
248 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
249 delete from hz_dqm_sync_interface
250 where rowid = L_ROWID(I);
251 ad_ctx_Ddl.Sync_Index ( p_index_name );
252 IF l_last_fetch THEN
253 EXIT;
254 END IF;
255 FND_CONCURRENT.AF_Commit;
256 END LOOP;
257 CLOSE l_ps_cur;
258 ELSIF (INSTRB(l_index_name,'hz_stage_contact_t1') > 0) THEN
259 ad_ctx_Ddl.Sync_Index ( p_index_name );
260 OPEN l_ct_cur;
261 LOOP
262 FETCH l_ct_cur BULK COLLECT INTO
263 L_ROWID
264 , L_PARTY_ID
265 , L_RECORD_ID LIMIT l_limit;
266 IF l_ct_cur%NOTFOUND THEN
267 l_last_fetch:=TRUE;
268 END IF;
269 IF L_RECORD_ID.COUNT=0 AND l_last_fetch THEN
270 EXIT;
271 END IF;
272 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
273 update hz_staged_contacts a set concat_col = concat_col
274 where a.org_contact_id = L_RECORD_ID(I);
275 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
276 delete from hz_dqm_sync_interface
277 where rowid = L_ROWID(I);
278 ad_ctx_Ddl.Sync_Index ( p_index_name );
279 IF l_last_fetch THEN
280 EXIT;
281 END IF;
282 FND_CONCURRENT.AF_Commit;
283 END LOOP;
284 CLOSE l_ct_cur;
285 ELSIF (INSTRB(l_index_name,'hz_stage_cpt_t1') > 0) THEN
286 ad_ctx_Ddl.Sync_Index ( p_index_name );
287 OPEN l_cp_cur;
288 LOOP
289 FETCH l_cp_cur BULK COLLECT INTO
290 L_ROWID
291 , L_PARTY_ID
292 , L_RECORD_ID LIMIT l_limit;
293 IF l_cp_cur%NOTFOUND THEN
294 l_last_fetch:=TRUE;
295 END IF;
296 IF L_RECORD_ID.COUNT=0 AND l_last_fetch THEN
297 EXIT;
298 END IF;
299 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
300 update hz_staged_contact_points a set concat_col = concat_col
301 where a.contact_point_id = L_RECORD_ID(I);
302 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
303 delete from hz_dqm_sync_interface
304 where rowid = L_ROWID(I);
305 ad_ctx_Ddl.Sync_Index ( p_index_name );
306 IF l_last_fetch THEN
307 EXIT;
308 END IF;
309 FND_CONCURRENT.AF_Commit;
310 END LOOP;
311 CLOSE l_cp_cur;
312 END IF;
313 --Call to sync index
314 END sync_index_realtime;
315
316
317
318 PROCEDURE sync_index(
319 p_index_name IN VARCHAR2,
320 retcode OUT NOCOPY VARCHAR2,
321 err OUT NOCOPY VARCHAR2) IS
322
323 cursor l_party_cur is select rowid, party_id, record_id
324 from hz_dqm_sync_interface a
325 where a.staged_flag = 'Y'
326 and a.entity = 'PARTY';
327 cursor l_ps_cur is select rowid, party_id, record_id
328 from hz_dqm_sync_interface a
329 where a.staged_flag = 'Y'
330 and a.entity = 'PARTY_SITES';
331 cursor l_ct_cur is select rowid, party_id, record_id
332 from hz_dqm_sync_interface a
333 where a.staged_flag = 'Y'
334 and entity = 'CONTACTS' ;
335 cursor l_cp_cur is select rowid, party_id, record_id
336 from hz_dqm_sync_interface a
337 where a.staged_flag = 'Y'
338 and entity = 'CONTACT_POINTS';
339
340 l_limit NUMBER := 1000;
341 TYPE RowList IS TABLE OF VARCHAR2(255);
342 L_ROWID RowList;
343 TYPE NumberList IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
344 L_PARTY_ID NumberList;
345 L_RECORD_ID NumberList;
346 l_last_fetch BOOLEAN := FALSE;
347 l_index_name VARCHAR2(100);
348
349 BEGIN
350 retcode := 0;
351 err := null;
352 l_index_name := lower(p_index_name);
353 IF (INSTRB(l_index_name,'hz_stage_parties_t1') > 0) THEN
354 ad_ctx_Ddl.Sync_Index ( p_index_name );
355 OPEN l_party_cur;
356 LOOP
357 FETCH l_party_cur BULK COLLECT INTO
358 L_ROWID
359 , L_PARTY_ID
360 , L_RECORD_ID LIMIT l_limit;
361 IF l_party_cur%NOTFOUND THEN
362 l_last_fetch:=TRUE;
363 END IF;
364 IF L_PARTY_ID.COUNT=0 AND l_last_fetch THEN
365 EXIT;
366 END IF;
367 FORALL I in L_PARTY_ID.FIRST..L_PARTY_ID.LAST
368 update hz_staged_parties a set concat_col = concat_col
369 where a.party_id = L_PARTY_ID(I);
370 FORALL I in L_PARTY_ID.FIRST..L_PARTY_ID.LAST
371 delete from hz_dqm_sync_interface
372 where rowid = L_ROWID(I);
373 ad_ctx_Ddl.Sync_Index ( p_index_name );
374 IF l_last_fetch THEN
375 EXIT;
376 END IF;
377 FND_CONCURRENT.AF_Commit;
378 END LOOP;
379 CLOSE l_party_cur;
380 ELSIF (INSTRB(l_index_name,'hz_stage_party_sites_t1') > 0) THEN
381 ad_ctx_Ddl.Sync_Index ( p_index_name );
382 OPEN l_ps_cur;
383 LOOP
384 FETCH l_ps_cur BULK COLLECT INTO
385 L_ROWID
386 , L_PARTY_ID
387 , L_RECORD_ID LIMIT l_limit;
388 IF l_ps_cur%NOTFOUND THEN
389 l_last_fetch:=TRUE;
390 END IF;
391 IF L_RECORD_ID.COUNT=0 AND l_last_fetch THEN
392 EXIT;
393 END IF;
394 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
395 update hz_staged_party_sites a set concat_col = concat_col
396 where a.party_site_id = L_RECORD_ID(I);
397 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
398 delete from hz_dqm_sync_interface
399 where rowid = L_ROWID(I);
400 ad_ctx_Ddl.Sync_Index ( p_index_name );
401 IF l_last_fetch THEN
402 EXIT;
403 END IF;
404 FND_CONCURRENT.AF_Commit;
405 END LOOP;
406 CLOSE l_ps_cur;
407 ELSIF (INSTRB(l_index_name,'hz_stage_contact_t1') > 0) THEN
408 ad_ctx_Ddl.Sync_Index ( p_index_name );
409 OPEN l_ct_cur;
410 LOOP
411 FETCH l_ct_cur BULK COLLECT INTO
412 L_ROWID
413 , L_PARTY_ID
414 , L_RECORD_ID LIMIT l_limit;
415 IF l_ct_cur%NOTFOUND THEN
416 l_last_fetch:=TRUE;
417 END IF;
418 IF L_RECORD_ID.COUNT=0 AND l_last_fetch THEN
419 EXIT;
420 END IF;
421 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
422 update hz_staged_contacts a set concat_col = concat_col
423 where a.org_contact_id = L_RECORD_ID(I);
424 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
425 delete from hz_dqm_sync_interface
426 where rowid = L_ROWID(I);
427 ad_ctx_Ddl.Sync_Index ( p_index_name );
428 IF l_last_fetch THEN
429 EXIT;
430 END IF;
431 FND_CONCURRENT.AF_Commit;
432 END LOOP;
433 CLOSE l_ct_cur;
434 ELSIF (INSTRB(l_index_name,'hz_stage_cpt_t1') > 0) THEN
435 ad_ctx_Ddl.Sync_Index ( p_index_name );
436 OPEN l_cp_cur;
437 LOOP
438 FETCH l_cp_cur BULK COLLECT INTO
439 L_ROWID
440 , L_PARTY_ID
441 , L_RECORD_ID LIMIT l_limit;
442 IF l_cp_cur%NOTFOUND THEN
443 l_last_fetch:=TRUE;
444 END IF;
445 IF L_RECORD_ID.COUNT=0 AND l_last_fetch THEN
446 EXIT;
447 END IF;
448 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
449 update hz_staged_contact_points a set concat_col = concat_col
450 where a.contact_point_id = L_RECORD_ID(I);
451 FORALL I in L_RECORD_ID.FIRST..L_RECORD_ID.LAST
452 delete from hz_dqm_sync_interface
453 where rowid = L_ROWID(I);
454 ad_ctx_Ddl.Sync_Index ( p_index_name );
455 IF l_last_fetch THEN
456 EXIT;
457 END IF;
458 FND_CONCURRENT.AF_Commit;
459 END LOOP;
460 CLOSE l_cp_cur;
461 END IF;
462 --Call to sync index
463 EXCEPTION
464 WHEN OTHERS THEN
465 retcode := 2;
466 err := SQLERRM;
467 log ('Error:' || SQLERRM);
468 END sync_index;
469
470
471 PROCEDURE optimize_index(
472 p_index_name IN VARCHAR2,
473 p_level IN VARCHAR2,
474 p_max_time IN NUMBER,
475 retcode OUT NOCOPY VARCHAR2,
476 err OUT NOCOPY VARCHAR2) IS
477 l_max_time NUMBER ;
478 BEGIN
479
480 retcode := 0;
481 err := null;
482
483 IF (p_max_time <> null) THEN
484 l_max_time := to_number(p_max_time);
485 ELSE
486 l_max_time := null;
487 END IF;
488
489 --Call to optimize index
490 ad_ctx_Ddl.Optimize_Index( p_index_name, p_level, l_max_time, null);
491 EXCEPTION
492 WHEN OTHERS THEN
493 /* retcode := 1;
494 err := SQLERRM; */
495 null;
496 END optimize_index;
497
498
499 FUNCTION is_sync_success (
500 p_entity IN VARCHAR2
501 ,p_record_id IN NUMBER
502 ,p_party_id IN NUMBER
503 ) RETURN BOOLEAN IS
504 CURSOR c_entity_sync_err (p_entity IN VARCHAR2, p_record_id IN NUMBER) IS
505 SELECT 1 from hz_dqm_sync_interface
506 WHERE entity = p_entity
507 AND record_id = p_record_id
508 AND staged_flag = 'E';
509
510 CURSOR c_party_sync_err (p_entity IN VARCHAR2, p_party_id IN NUMBER) IS
511 SELECT 1 from hz_dqm_sync_interface
512 WHERE entity = p_entity
513 AND party_id = p_party_id
514 AND staged_flag = 'E';
515
516 l_num NUMBER;
517 x_status BOOLEAN := TRUE;
518 BEGIN
519 IF p_entity = 'PARTY' THEN
520 OPEN c_party_sync_err (p_entity, p_party_id);
521 FETCH c_party_sync_err INTO l_num;
522 IF c_party_sync_err%FOUND THEN
523 x_status := FALSE;
524 END IF;
525 CLOSE c_party_sync_err;
526 ELSE
527 OPEN c_entity_sync_err (p_entity, p_record_id);
528 FETCH c_entity_sync_err INTO l_num;
529 IF c_entity_sync_err%FOUND THEN
530 x_status := FALSE;
531 END IF;
532 CLOSE c_entity_sync_err;
533 END IF;
534 RETURN x_status;
535 EXCEPTION
536 WHEN OTHERS THEN
537 IF c_entity_sync_err%ISOPEN THEN
538 CLOSE c_entity_sync_err;
539 END IF;
540 IF c_party_sync_err%ISOPEN THEN
541 CLOSE c_party_sync_err;
542 END IF;
543 x_status := TRUE;
544 RETURN x_status;
545
546 END is_sync_success;
547
548
549 PROCEDURE sync_org (
550 p_party_id IN NUMBER,
551 p_create_upd IN VARCHAR2
552 ) IS
553 l_sql_err_message VARCHAR2(2000);
554 BEGIN
555 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
556 IF ( L_REALTIME_SYNC_VALUE = 'Y') THEN
557 -- REPURI. SYNC Perf Improvements. Insert directly into staging tables
558 HZ_STAGE_MAP_TRANSFORM.sync_single_party_online(p_party_id, p_create_upd);
559 --Check if sync went through successfully
560 IF (is_sync_success('PARTY',null,p_party_id)) THEN
561 -- Call sync index serial concurrent program
562 call_sync_index_serial ;
563 END IF;
564 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN
565 insert_interface_rec(p_party_id,null,null,null,'PARTY',p_create_upd);
566 -- REPURI. Bug 4884742. Added this to insert data into hz_dqm_sh_sync_interface table
567 -- if shadow staging conc prog completed successfully.
568 IF (is_shadow_staging_complete) THEN
569 insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_create_upd);
570 END IF;
571 END IF;
572 EXCEPTION
573 WHEN OTHERS THEN
574 hz_common_pub.enable_cont_source_security;
575 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
576 FND_MESSAGE.SET_TOKEN('PROC','sync_org');
577 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
578 FND_MSG_PUB.ADD;
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
580 END sync_org;
581
582
583 PROCEDURE sync_person (
584 p_party_id IN NUMBER,
585 p_create_upd IN VARCHAR2
586 ) IS
587 l_org_contact_id NUMBER;
588 l_sql_err_message VARCHAR2(2000);
589
590 CURSOR c_contact IS
591 SELECT oc.org_contact_id
592 FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc
593 WHERE pr.relationship_id = oc.party_relationship_id
594 AND pr.subject_id = p_party_id
595 AND pr.subject_table_name = 'HZ_PARTIES'
596 AND pr.object_table_name = 'HZ_PARTIES'
597 AND pr.directional_flag = 'F';
598 BEGIN
599 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
600 IF (L_REALTIME_SYNC_VALUE = 'Y') THEN
601 -- REPURI. SYNC Perf Improvements. Insert directly into staging tables
602 HZ_STAGE_MAP_TRANSFORM.sync_single_party_online(p_party_id, p_create_upd);
603 --Check if sync went through successfully
604 IF (is_sync_success('PARTY',null,p_party_id)) THEN
605 -- Call sync index serial concurrent program
606 call_sync_index_serial ;
607 END IF;
608 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN
609 insert_interface_rec(p_party_id,null,null,null,'PARTY',p_create_upd);
610 -- REPURI. Bug 4884742. Added this to insert data into hz_dqm_sh_sync_interface table
611 -- if shadow staging conc prog completed successfully.
612 IF (is_shadow_staging_complete) THEN
613 insert_sh_interface_rec(p_party_id,null,null,null,'PARTY',p_create_upd);
614 END IF;
615 IF p_create_upd = 'U' THEN
616 OPEN c_contact;
617 LOOP
618 FETCH c_contact INTO l_org_contact_id;
619 EXIT WHEN c_contact%NOTFOUND;
620 insert_interface_rec(p_party_id,l_org_contact_id,null,null,'CONTACTS','U');
621 -- REPURI. Bug 4884742. Added this to insert data into hz_dqm_sh_sync_interface table
622 -- if shadow staging conc prog completed successfully.
623 IF (is_shadow_staging_complete) THEN
624 insert_sh_interface_rec(p_party_id,l_org_contact_id,null,null,'CONTACTS','U');
625 END IF;
626 END LOOP;
627 END IF ;
628 END IF;
629 EXCEPTION
630 WHEN OTHERS THEN
631 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
632 FND_MESSAGE.SET_TOKEN('PROC','sync_person');
633 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
634 FND_MSG_PUB.ADD;
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
636 END sync_person;
637
638
639 PROCEDURE sync_party_site (
640 p_party_site_id IN NUMBER,
641 p_create_upd IN VARCHAR2
642 ) IS
643 l_party_id NUMBER;
644 l_party_id1 NUMBER;
645 l_org_contact_id NUMBER;
646 l_party_type VARCHAR2(255);
647 l_sql_err_message VARCHAR2(2000) ;
648 BEGIN
649 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
650 IF (L_REALTIME_SYNC_VALUE = 'Y') THEN
651 -- REPURI. SYNC Perf Improvements. Insert directly into staging tables
652 HZ_STAGE_MAP_TRANSFORM.sync_single_party_site_online(p_party_site_id, p_create_upd);
653 --Check if sync went through successfully
654 IF (is_sync_success('PARTY_SITES',p_party_site_id,null)) THEN
655 -- Call sync index serial concurrent program
656 call_sync_index_serial ;
657 END IF;
658 ELSIF (L_REALTIME_SYNC_VALUE = 'N') THEN
659 BEGIN
660 SELECT ps.party_id,p.party_type INTO l_party_id, l_party_type
661 FROM HZ_PARTY_SITES ps, HZ_PARTIES p
662 WHERE party_site_id = p_party_site_id
663 AND p.PARTY_ID = ps.PARTY_ID;
664 EXCEPTION
665 /* Bug No: 2707873. Added this exception because the above sql
666 will not retrive any record for party_id -1. */
667 WHEN NO_DATA_FOUND THEN
668 RETURN;
669 END;
670 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
671 BEGIN
672 SELECT r.object_id, org_contact_id INTO l_party_id1,l_org_contact_id
673 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
674 WHERE r.party_id = l_party_id
675 AND r.relationship_id = oc.party_relationship_id
676 AND r.directional_flag='F'
677 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
678 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
679 EXCEPTION
680 WHEN NO_DATA_FOUND THEN
681 RETURN;
682 END;
683 ELSE
684 l_party_id1:=l_party_id;
685 l_org_contact_id:=NULL;
686 END IF;
687 insert_interface_rec(l_party_id1,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_create_upd);
688 -- REPURI. Bug 4884742. Added this to insert data into hz_dqm_sh_sync_interface table
689 -- if shadow staging conc prog completed successfully.
690 IF (is_shadow_staging_complete) THEN
691 insert_sh_interface_rec(l_party_id1,p_party_site_id,null,l_org_contact_id,'PARTY_SITES',p_create_upd);
692 END IF;
693 END IF;
694 EXCEPTION
695 WHEN OTHERS THEN
696 hz_common_pub.enable_cont_source_security;
697 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
698 FND_MESSAGE.SET_TOKEN('PROC','sync_party_site');
699 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
700 FND_MSG_PUB.ADD;
701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
702 END sync_party_site;
703
704
705 PROCEDURE sync_contact (
706 p_org_contact_id IN NUMBER,
707 p_create_upd IN VARCHAR2
708 ) IS
709 l_party_id NUMBER;
710 l_sql_err_message VARCHAR2(2000);
711 BEGIN
712 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
713 IF (L_REALTIME_SYNC_VALUE = 'Y') THEN
714 -- REPURI. SYNC Perf Improvements. Insert directly into staging tables
715 HZ_STAGE_MAP_TRANSFORM.sync_single_contact_online(p_org_contact_id, p_create_upd);
716 --Check if sync went through successfully
717 IF (is_sync_success('CONTACTS',p_org_contact_id,null)) THEN
718 -- Call sync index serial concurrent program
719 call_sync_index_serial ;
720 END IF;
721 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN
722 IF (p_create_upd <> 'D') THEN
723 SELECT r.object_id INTO l_party_id
724 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
725 WHERE oc.org_contact_id = p_org_contact_id
726 AND oc.party_relationship_id = r.relationship_id
727 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
728 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
729 AND subject_type ='PERSON'
730 AND DIRECTIONAL_FLAG= 'F'
731 AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
732 AND (r.status is null OR r.status = 'A' or r.status = 'I') ;
733 ELSIF (p_create_upd = 'D') THEN
734 SELECT r.object_id INTO l_party_id
735 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
736 WHERE oc.org_contact_id = p_org_contact_id
737 AND oc.party_relationship_id = r.relationship_id
738 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
739 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
740 AND subject_type ='PERSON'
741 AND DIRECTIONAL_FLAG= 'F';
742 END IF;
743
744 insert_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_create_upd);
745 -- REPURI. Bug 4884742. Added this to insert data into hz_dqm_sh_sync_interface table
746 -- if shadow staging conc prog completed successfully.
747 IF (is_shadow_staging_complete) THEN
748 insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_create_upd);
749 END IF;
750 END IF;
751 EXCEPTION
752 WHEN NO_DATA_FOUND THEN
753 NULL;
754 WHEN OTHERS THEN
755 hz_common_pub.enable_cont_source_security;
756 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
757 FND_MESSAGE.SET_TOKEN('PROC','sync_contact');
758 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
759 FND_MSG_PUB.ADD;
760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
761 END sync_contact;
762
763
764 PROCEDURE sync_contact_point (
765 p_contact_point_id IN NUMBER,
766 p_create_upd IN VARCHAR2
767 ) IS
768 l_party_id NUMBER := 0;
769 l_party_id1 NUMBER;
770 l_org_contact_id NUMBER;
771 l_party_site_id NUMBER;
772 l_pr_id NUMBER;
773 l_num_ocs NUMBER;
774 l_ot_id NUMBER;
775 l_ot_table VARCHAR2(60);
776 l_party_type VARCHAR2(60);
777 l_sql_err_message VARCHAR2(2000);
778 BEGIN
779 l_party_id := 0;
780 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
781 IF (L_REALTIME_SYNC_VALUE = 'Y') THEN
782 -- REPURI. SYNC Perf Improvements. Insert directly into staging tables
783 HZ_STAGE_MAP_TRANSFORM.sync_single_cpt_online(p_contact_point_id, p_create_upd);
784 --Check if sync went through successfully
785 IF (is_sync_success('CONTACT_POINTS',p_contact_point_id,null)) THEN
786 -- Call sync index serial concurrent program
787 call_sync_index_serial ;
788 END IF;
789 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN
790 SELECT owner_table_name,owner_table_id INTO l_ot_table, l_ot_id
791 FROM hz_contact_points
792 WHERE contact_point_id = p_contact_point_id;
793
794 IF l_ot_table = 'HZ_PARTY_SITES' THEN
795 SELECT p.party_id, ps.party_site_id, party_type
796 INTO l_party_id1, l_party_site_id, l_party_type
797 FROM HZ_PARTY_SITES ps, HZ_PARTIES p
798 WHERE party_site_id = l_ot_id
799 AND p.party_id = ps.party_id;
800
801 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
802 BEGIN
803 SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
804 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
805 WHERE r.party_id = l_party_id1
806 AND r.relationship_id = oc.party_relationship_id
807 AND r.directional_flag='F'
808 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
809 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
810 EXCEPTION
811 WHEN NO_DATA_FOUND THEN
812 RETURN;
813 END;
814 ELSE
815 l_party_id:=l_party_id1;
816 l_org_contact_id:=NULL;
817 END IF;
818 ELSIF l_ot_table = 'HZ_PARTIES' THEN
819 l_party_site_id := NULL;
820 SELECT party_type INTO l_party_type
821 FROM hz_parties
822 WHERE party_id = l_ot_id;
823
824 IF l_party_type <> 'PARTY_RELATIONSHIP' THEN
825 l_party_id := l_ot_id;
826 l_org_contact_id:=NULL;
827 ELSE
828 BEGIN
829 SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
830 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
831 WHERE r.party_id = l_ot_id
832 AND r.relationship_id = oc.party_relationship_id
833 AND r.directional_flag='F'
834 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
835 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
836 EXCEPTION
837 WHEN NO_DATA_FOUND THEN
838 RETURN;
839 END;
840 END IF;
841 END IF;
842
843 insert_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_create_upd);
844 -- REPURI. Bug 4884742. Added this to insert data into hz_dqm_sh_sync_interface table
845 -- if shadow staging conc prog completed successfully.
846 IF (is_shadow_staging_complete) THEN
847 insert_sh_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_create_upd);
848 END IF;
849 END IF;
850 EXCEPTION
851 WHEN OTHERS THEN
852 hz_common_pub.enable_cont_source_security;
853 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
854 FND_MESSAGE.SET_TOKEN('PROC','sync_contact_point');
855 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
856 FND_MSG_PUB.ADD;
857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
858 END sync_contact_point;
859
860
861 PROCEDURE sync_relationship (
862 p_relationship_id IN NUMBER,
863 p_create_upd IN VARCHAR2
864 ) IS
865
866 CURSOR org_contacts IS
867 SELECT org_contact_id
868 FROM hz_org_contacts
869 WHERE party_relationship_id = p_relationship_id
870 AND status = 'A';
871
872 l_org_contact_id NUMBER;
873
874 BEGIN
875 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
876 IF ( L_REALTIME_SYNC_VALUE = 'Y' OR L_REALTIME_SYNC_VALUE = 'N')
877 THEN
878 OPEN org_contacts;
879 LOOP
880 FETCH org_contacts INTO l_org_contact_id;
881 EXIT WHEN org_contacts%NOTFOUND;
882 sync_contact(l_org_contact_id,'U');
883 END LOOP;
884 CLOSE org_contacts;
885 END IF;
886
887 EXCEPTION
888 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
889 hz_common_pub.enable_cont_source_security;
890 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
891 WHEN OTHERS THEN
892 hz_common_pub.enable_cont_source_security;
893 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
894 FND_MESSAGE.SET_TOKEN('PROC','sync_relationship');
895 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
896 FND_MSG_PUB.ADD;
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 END;
899
900 --- VJN CHANGED THIS PROCEDURE TO INCLUDE THE CHECK OF 'I' FOR THE
901 -- party_sites CURSOR (Bug 3139325)
902 PROCEDURE sync_location (
903 p_location_id IN NUMBER,
904 p_create_upd IN VARCHAR2
905 ) IS
906
907 CURSOR party_sites IS
908 SELECT party_site_id
909 FROM hz_party_Sites
910 WHERE location_id = p_location_id
911 AND (status = 'A' or status = 'I') ;
912
913 l_party_site_id NUMBER;
914
915 BEGIN
916 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
917 IF ( L_REALTIME_SYNC_VALUE = 'Y' OR L_REALTIME_SYNC_VALUE = 'N')
918 THEN
919 OPEN party_sites;
920 LOOP
921 FETCH party_sites INTO l_party_site_id;
922 EXIT WHEN party_sites%NOTFOUND;
923 sync_party_site(l_party_site_id,'U');
924 END LOOP;
925 CLOSE party_sites;
926 END IF;
927
928 EXCEPTION
929 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
930 hz_common_pub.enable_cont_source_security;
931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
932 WHEN OTHERS THEN
933 hz_common_pub.enable_cont_source_security;
934 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
935 FND_MESSAGE.SET_TOKEN('PROC','sync_location');
936 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
937 FND_MSG_PUB.ADD;
938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
939 END;
940
941 PROCEDURE sync_cust_account (
942 p_cust_acct_id IN NUMBER,
943 p_create_upd IN VARCHAR2
944 ) IS
945
946 CURSOR c_cust_party IS
947 SELECT c.PARTY_ID, p.PARTY_TYPE
948 FROM HZ_CUST_ACCOUNTS c, HZ_PARTIES p
949 WHERE c.cust_account_id = p_cust_acct_id
950 AND p.party_id = c.party_id
951 AND NOT EXISTS (
952 SELECT d.PARTY_ID
953 FROM HZ_DQM_SYNC_INTERFACE d
954 WHERE d.ENTITY = 'PARTY'
955 AND d.PARTY_ID = c.PARTY_ID
956 AND d.STAGED_FLAG = 'N');
957
958 l_party_id NUMBER;
959 l_party_type VARCHAR2(200);
960
961 BEGIN
962 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
963 IF ( L_REALTIME_SYNC_VALUE = 'Y' OR L_REALTIME_SYNC_VALUE = 'N')
964 THEN
965 OPEN c_cust_party;
966 FETCH c_cust_party INTO l_party_id, l_party_type;
967 IF c_cust_party%FOUND THEN
968 IF l_party_type = 'ORGANIZATION' THEN
969 HZ_DQM_SYNC.sync_org(l_party_id,'U');
970 ELSIF l_party_type = 'PERSON' THEN
971 HZ_DQM_SYNC.sync_person(l_party_id,'U');
972 END IF;
973 END IF;
974 CLOSE c_cust_party;
975 END IF;
976 EXCEPTION
977 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
978 hz_common_pub.enable_cont_source_security;
979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
980 WHEN OTHERS THEN
981 hz_common_pub.enable_cont_source_security;
982 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
983 FND_MESSAGE.SET_TOKEN('PROC','sync_cust_account');
984 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
985 FND_MSG_PUB.ADD;
986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
987 END;
988
989 -- VJN added for Reporting errored records into HZ_DQM_SYNC_INTERFACE
990
991 PROCEDURE insert_error_rec (
992 p_party_id IN NUMBER,
993 p_record_id IN NUMBER,
994 p_party_site_id IN NUMBER,
995 p_org_contact_id IN NUMBER,
996 p_entity IN VARCHAR2,
997 p_operation IN VARCHAR2,
998 p_staged_flag IN VARCHAR2 DEFAULT 'E',
999 p_realtime_sync_flag IN VARCHAR2 DEFAULT 'Y',
1000 p_error_data IN VARCHAR2
1001 ) IS
1002
1003 BEGIN
1004
1005 INSERT INTO hz_dqm_sync_interface (
1006 PARTY_ID,
1007 RECORD_ID,
1008 PARTY_SITE_ID,
1009 ORG_CONTACT_ID,
1010 ENTITY,
1011 OPERATION,
1012 STAGED_FLAG,
1013 REALTIME_SYNC_FLAG,
1014 ERROR_DATA,
1015 CREATED_BY,
1016 CREATION_DATE,
1017 LAST_UPDATE_LOGIN,
1018 LAST_UPDATE_DATE,
1019 LAST_UPDATED_BY,
1020 SYNC_INTERFACE_NUM
1021 ) VALUES (
1022 p_party_id,
1023 p_record_id,
1024 p_party_site_id,
1025 p_org_contact_id,
1026 p_entity,
1027 p_operation,
1028 p_staged_flag,
1029 p_realtime_sync_flag,
1030 p_error_data,
1031 hz_utility_pub.created_by,
1032 hz_utility_pub.creation_date,
1033 hz_utility_pub.last_update_login,
1034 hz_utility_pub.last_update_date,
1035 hz_utility_pub.user_id,
1036 HZ_DQM_SYNC_INTERFACE_S.nextval
1037 );
1038 END insert_error_rec;
1039
1040 -- REPURI. Bug 4884742. Added this procedure to insert data into hz_dqm_sh_sync_interface table.
1041 -- This is the interface table for Shadow Sync.
1042
1043 PROCEDURE insert_sh_interface_rec (
1044 p_party_id IN NUMBER,
1045 p_record_id IN NUMBER,
1046 p_party_site_id IN NUMBER,
1047 p_org_contact_id IN NUMBER,
1048 p_entity IN VARCHAR2,
1049 p_operation IN VARCHAR2,
1050 p_staged_flag IN VARCHAR2 DEFAULT 'N'
1051 ) IS
1052
1053 is_real_time VARCHAR2(1);
1054
1055 BEGIN
1056 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
1057 IF (L_REALTIME_SYNC_VALUE = 'Y') THEN
1058 is_real_time := 'Y';
1059 END IF;
1060
1061 -- REPURI. Bug 4968126.
1062 -- Using the Merge instead of Insert statement
1063 -- so that duplicate records dont get inserted.
1064
1065 MERGE INTO hz_dqm_sh_sync_interface S
1066 USING (
1067 SELECT
1068 p_entity AS entity
1069 ,p_party_id AS party_id
1070 ,p_record_id AS record_id
1071 ,p_party_site_id AS party_site_id
1072 ,p_org_contact_id AS org_contact_id
1073 FROM dual ) T
1074 ON (S.entity = T.entity AND
1075 S.party_id = T.party_id AND
1076 NVL(S.record_id,-99) = NVL(T.record_id,-99) AND
1077 NVL(S.party_site_id, -99) = NVL(T.party_site_id,-99) AND
1078 NVL(S.org_contact_id,-99) = NVL(T.org_contact_id,-99) AND
1079 S.staged_flag <> 'E')
1080 WHEN NOT MATCHED THEN
1081 INSERT (
1082 PARTY_ID,
1083 RECORD_ID,
1084 PARTY_SITE_ID,
1085 ORG_CONTACT_ID,
1086 ENTITY,
1087 OPERATION,
1088 STAGED_FLAG,
1089 REALTIME_SYNC_FLAG,
1090 CREATED_BY,
1091 CREATION_DATE,
1092 LAST_UPDATE_LOGIN,
1093 LAST_UPDATE_DATE,
1094 LAST_UPDATED_BY,
1095 SYNC_INTERFACE_NUM
1096 ) VALUES (
1097 p_party_id,
1098 p_record_id,
1099 p_party_site_id,
1100 p_org_contact_id,
1101 p_entity,
1102 p_operation,
1103 p_staged_flag,
1104 is_real_time,
1105 hz_utility_pub.created_by,
1106 hz_utility_pub.creation_date,
1107 hz_utility_pub.last_update_login,
1108 hz_utility_pub.last_update_date,
1109 hz_utility_pub.user_id,
1110 HZ_DQM_SH_SYNC_INTERFACE_S.nextval
1111 );
1112
1113 END insert_sh_interface_rec;
1114
1115
1116 PROCEDURE insert_interface_rec (
1117 p_party_id IN NUMBER,
1118 p_record_id IN NUMBER,
1119 p_party_site_id IN NUMBER,
1120 p_org_contact_id IN NUMBER,
1121 p_entity IN VARCHAR2,
1122 p_operation IN VARCHAR2,
1123 p_staged_flag IN VARCHAR2 DEFAULT 'N'
1124 ) IS
1125
1126 is_real_time VARCHAR2(1);
1127
1128 BEGIN
1129 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
1130 IF (L_REALTIME_SYNC_VALUE = 'Y') THEN
1131 is_real_time := 'Y';
1132 END IF;
1133
1134 --Bug 13810656 Logic for handling the Delete Operation
1135 IF (p_operation = 'D') THEN
1136
1137 INSERT INTO HZ_DQM_SYNC_INTERFACE(
1138 PARTY_ID,
1139 RECORD_ID,
1140 PARTY_SITE_ID,
1141 ORG_CONTACT_ID,
1142 ENTITY,
1143 OPERATION,
1144 STAGED_FLAG,
1145 REALTIME_SYNC_FLAG,
1146 CREATED_BY,
1147 CREATION_DATE,
1148 LAST_UPDATE_LOGIN,
1149 LAST_UPDATE_DATE,
1150 LAST_UPDATED_BY,
1151 SYNC_INTERFACE_NUM
1152 ) VALUES (
1153 p_party_id,
1154 p_record_id,
1155 p_party_site_id,
1156 p_org_contact_id,
1157 p_entity,
1158 p_operation,
1159 p_staged_flag,
1160 is_real_time,
1161 hz_utility_pub.created_by,
1162 hz_utility_pub.creation_date,
1163 hz_utility_pub.last_update_login,
1164 hz_utility_pub.last_update_date,
1165 hz_utility_pub.user_id,
1166 HZ_DQM_SYNC_INTERFACE_S.nextval
1167 );
1168
1169 ELSE
1170
1171 -- REPURI. Bug 4968126.
1172 -- Using the Merge instead of Insert statement
1173 -- so that duplicate records dont get inserted.
1174 MERGE INTO hz_dqm_sync_interface S
1175 USING (
1176 SELECT
1177 p_entity AS entity
1178 ,p_party_id AS party_id
1179 ,p_record_id AS record_id
1180 ,p_party_site_id AS party_site_id
1181 ,p_org_contact_id AS org_contact_id
1182 FROM dual ) T
1183 ON (S.entity = T.entity AND
1184 S.party_id = T.party_id AND
1185 NVL(S.record_id,-99) = NVL(T.record_id,-99) AND
1186 NVL(S.party_site_id, -99) = NVL(T.party_site_id,-99) AND
1187 NVL(S.org_contact_id,-99) = NVL(T.org_contact_id,-99) AND
1188 S.staged_flag <> 'E' AND
1189 S.operation <> 'D')
1190 WHEN NOT MATCHED THEN
1191 INSERT (
1192 PARTY_ID,
1196 ENTITY,
1193 RECORD_ID,
1194 PARTY_SITE_ID,
1195 ORG_CONTACT_ID,
1197 OPERATION,
1198 STAGED_FLAG,
1199 REALTIME_SYNC_FLAG,
1200 CREATED_BY,
1201 CREATION_DATE,
1202 LAST_UPDATE_LOGIN,
1203 LAST_UPDATE_DATE,
1204 LAST_UPDATED_BY,
1205 SYNC_INTERFACE_NUM
1206 ) VALUES (
1207 p_party_id,
1208 p_record_id,
1209 p_party_site_id,
1210 p_org_contact_id,
1211 p_entity,
1212 p_operation,
1213 p_staged_flag,
1214 is_real_time,
1215 hz_utility_pub.created_by,
1216 hz_utility_pub.creation_date,
1217 hz_utility_pub.last_update_login,
1218 hz_utility_pub.last_update_date,
1219 hz_utility_pub.user_id,
1220 HZ_DQM_SYNC_INTERFACE_S.nextval
1221 );
1222
1223 END IF;
1224
1225 END insert_interface_rec;
1226
1227
1228 PROCEDURE optimize_indexes (
1229 errbuf OUT NOCOPY VARCHAR2,
1230 retcode OUT NOCOPY VARCHAR2
1231 ) IS
1232
1233 l_bool BOOLEAN;
1234 l_status VARCHAR2(255);
1235 l_index_owner VARCHAR2(255);
1236 l_tmp VARCHAR2(2000);
1237
1238 l_prof VARCHAR2(255);
1239 l_prof_val NUMBER;
1240 idx_retcode VARCHAR2(1);
1241 idx_err VARCHAR2(2000);
1242
1243 BEGIN
1244 retcode := 0;
1245
1246 outandlog('Starting Concurrent Program ''Synchronize Stage Schema''');
1247 outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
1248 outandlog('NEWLINE');
1249
1250 BEGIN
1251 l_prof := FND_PROFILE.VALUE('HZ_DQM_OPT_MAXTIME');
1252 IF upper(l_prof) = 'UNLIMITED' THEN
1253 l_prof_val := null;
1254 ELSE
1255 l_prof_val := TO_NUMBER(l_prof);
1256 END IF;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 l_prof_val := null;
1260 END;
1261
1262 -- Initialize return status and message stack
1263 FND_MSG_PUB.initialize;
1264
1265 log('Optimizing Intermedia indexes');
1266 log('Optimizing party index .. ', TRUE);
1267
1268 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_index_owner);
1269 OPTIMIZE_INDEX(l_index_owner || '.hz_stage_parties_t1', 'FULL', l_prof_val, idx_retcode , idx_err);
1270 IF idx_retcode = 1 THEN
1271 RAISE FND_API.G_EXC_ERROR;
1272 END IF;
1273 log('Done');
1274
1275 log('Optimizing party site index .. ', TRUE);
1276 OPTIMIZE_INDEX(l_index_owner || '.hz_stage_party_sites_t1', 'FULL', l_prof_val, idx_retcode , idx_err);
1277 IF idx_retcode = 1 THEN
1278 RAISE FND_API.G_EXC_ERROR;
1279 END IF;
1280 log('Done');
1281
1282 log('Optimizing contact index .. ', TRUE);
1283 OPTIMIZE_INDEX(l_index_owner || '.hz_stage_contact_t1', 'FULL', l_prof_val, idx_retcode , idx_err);
1284 IF idx_retcode = 1 THEN
1285 RAISE FND_API.G_EXC_ERROR;
1286 END IF;
1287 log('Done');
1288
1289 log('Optimizing contact point index .. ', TRUE);
1290 OPTIMIZE_INDEX(l_index_owner || '.hz_stage_cpt_t1', 'FULL', l_prof_val, idx_retcode , idx_err);
1291 IF idx_retcode = 1 THEN
1292 RAISE FND_API.G_EXC_ERROR;
1293 END IF;
1294 log('Done');
1295
1296 outandlog('Concurrent Program Execution completed ');
1297 outandlog('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
1298
1299
1300 END;
1301
1302 /**
1303 * Procedure to write a message to the out NOCOPY file
1304 **/
1305 PROCEDURE out(
1306 message IN VARCHAR2,
1307 newline IN BOOLEAN DEFAULT TRUE) IS
1308 BEGIN
1309 IF message = 'NEWLINE' THEN
1310 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1311 ELSIF (newline) THEN
1312 FND_FILE.put_line(fnd_file.output,message);
1313 ELSE
1314 FND_FILE.put(fnd_file.output,message);
1315 END IF;
1316 END out;
1317
1318 /**
1319 * Procedure to write a message to the log file
1320 **/
1321 PROCEDURE log(
1322 message IN VARCHAR2,
1323 newline IN BOOLEAN DEFAULT TRUE
1324 ) IS
1325 BEGIN
1326 IF message = 'NEWLINE' THEN
1327 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1328 ELSIF (newline) THEN
1329 FND_FILE.put_line(fnd_file.log,message);
1330 ELSE
1331 FND_FILE.put(fnd_file.log,message);
1332 END IF;
1333 END log;
1334
1335 /**
1336 * Procedure to write a message to the out NOCOPY and log files
1337 **/
1338 PROCEDURE outandlog(
1339 message IN VARCHAR2,
1340 newline IN BOOLEAN DEFAULT TRUE) IS
1341 BEGIN
1342 out(message, newline);
1343 log(message, newline);
1344 END outandlog;
1345
1346 /**
1347 * Function to fetch messages of the stack and log the error
1348 * Also returns the error
1349 **/
1350 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
1351 RETURN VARCHAR2 IS
1352
1353 l_msg_data VARCHAR2(2000);
1354 BEGIN
1355 FND_MSG_PUB.Reset;
1356
1357 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1358 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
1359 END LOOP;
1360 IF (SQLERRM IS NOT NULL) THEN
1361 l_msg_data := l_msg_data || SQLERRM;
1362 END IF;
1363 log(l_msg_data);
1364 RETURN l_msg_data;
1365 END logerror;
1366
1367 PROCEDURE stage_party_merge(
1368 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1369 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1370 x_to_id IN OUT NOCOPY NUMBER,
1371 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1372 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1373 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1374 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
1375 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
1376 x_return_status OUT NOCOPY VARCHAR2
1377 ) IS
1378
1379 l_party_type VARCHAR2(30);
1380
1381 BEGIN
1382
1383 x_return_status := FND_API.G_RET_STS_SUCCESS;
1384 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
1385
1386 SELECT party_type INTO l_party_type
1387 FROM HZ_PARTIES
1388 WHERE party_id = p_from_id;
1389
1390 --Bug 9249643
1391 IF l_party_type='ORGANIZATION' THEN
1392 sync_org(p_to_fk_id, 'U');
1393 ELSIF l_party_type='PERSON' THEN
1394 sync_person(p_to_fk_id, 'U');
1395 END IF;
1396
1397 IF l_party_type <> 'PARTY_RELATIONSHIP' THEN
1398 IF ( L_REALTIME_SYNC_VALUE = 'Y') THEN
1399 BEGIN
1400 UPDATE HZ_STAGED_PARTIES
1401 SET STATUS = 'M'
1402 WHERE party_id = p_from_id;
1403 EXCEPTION
1404 WHEN NO_DATA_FOUND THEN
1405 NULL;
1406 END;
1407 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN--Bug 13810656 Deferring the update of 'M' Status operation of Merge flow
1408 IF l_party_type='ORGANIZATION' THEN
1409 sync_org(p_from_id, 'D');
1410 ELSIF l_party_type='PERSON' THEN
1411 sync_person(p_from_id, 'D');
1412 END IF;
1413 END IF;
1414 END IF;
1415
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1419 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1420 FND_MSG_PUB.ADD;
1421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422 END stage_party_merge;
1423
1424 PROCEDURE stage_party_site_merge(
1425 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1426 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1427 x_to_id IN OUT NOCOPY NUMBER,
1428 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1429 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1430 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1431 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
1432 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
1433 x_return_status OUT NOCOPY VARCHAR2
1434 ) IS
1435
1436 l_party_id NUMBER;
1437 l_party_site_id NUMBER;
1438 l_party_site_search_rec HZ_PARTY_SEARCH.party_site_search_rec_type;
1439 l_party_site_stage_rec HZ_PARTY_STAGE.PARTY_SITE_STAGE_REC_TYPE;
1440
1441 BEGIN
1442 x_return_status := FND_API.G_RET_STS_SUCCESS;
1443 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
1444
1445 IF ( L_REALTIME_SYNC_VALUE = 'Y') THEN
1446 BEGIN
1447 DELETE FROM HZ_STAGED_PARTY_SITES
1448 WHERE party_site_id = p_from_id;
1449 EXCEPTION
1450 WHEN NO_DATA_FOUND THEN
1451 NULL;
1452 END;
1453 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN--Bug 13810656 Deferring the delete of from party_site_id for Merge flow
1454 sync_party_site (p_from_id,'D');
1455 END IF;
1456
1457 IF p_from_fk_id = p_to_fk_id THEN--Not Sure this will ever occur as Merge always deletes and creates a new PS for Merge/Transfer Case.
1458 SELECT party_id INTO l_party_id
1459 FROM HZ_PARTY_SITES
1460 WHERE party_site_id = p_from_id;
1461
1462 l_party_site_id := p_from_id;
1463
1464 SAVEPOINT party_site_sync;
1465
1466 BEGIN
1467 sync_party_site (l_party_site_id,'C');
1468
1469 EXCEPTION
1470 WHEN OTHERS THEN
1471 ROLLBACK TO party_site_sync;
1472 sync_party_site (p_from_id, 'C');
1473 END;
1474 END IF;
1475
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1479 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1480 FND_MSG_PUB.ADD;
1481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482 END stage_party_site_merge;
1483
1484 PROCEDURE stage_contact_point_merge(
1485 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1486 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1487 x_to_id IN OUT NOCOPY NUMBER,
1488 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1489 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1490 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1491 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
1492 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
1493 x_return_status OUT NOCOPY VARCHAR2
1494 ) IS
1495
1496 l_contact_point_id NUMBER;
1497 l_cpt_search_rec HZ_PARTY_SEARCH.contact_point_search_rec_type;
1498 l_contact_pt_stage_rec HZ_PARTY_STAGE.CONTACT_PT_STAGE_REC_TYPE;
1499 l_party_id NUMBER := 0;
1500 l_pr_id NUMBER;
1501 l_num_ocs NUMBER;
1502 l_ot_id NUMBER;
1503 l_ot_table VARCHAR2(60);
1504 l_party_type VARCHAR2(60);
1505
1506 CURSOR c_cp_party_site (cp_id NUMBER) IS
1507 SELECT owner_table_id
1508 FROM HZ_CONTACT_POINTS
1509 WHERE owner_table_name = 'HZ_PARTY_SITES'
1510 AND contact_point_id = cp_id;
1511
1512 CURSOR c_cp_org_contact (cp_id NUMBER) IS
1513 SELECT oc.org_contact_id
1514 FROM HZ_CONTACT_POINTS cp, HZ_RELATIONSHIPS rl, HZ_ORG_CONTACTS oc
1515 WHERE owner_table_name = 'HZ_PARTIES'
1516 AND contact_point_id = cp_id
1517 AND rl.PARTY_ID = cp.owner_table_id
1518 AND oc.party_relationship_id = rl.relationship_id
1519 AND rl.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1520 AND rl.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1521 AND rl.DIRECTIONAL_FLAG = 'F';
1522
1523 CURSOR c_ps_org_contact (ps_id NUMBER) IS
1524 SELECT oc.org_contact_id
1525 FROM HZ_PARTY_SITES ps, HZ_RELATIONSHIPS rl, HZ_ORG_CONTACTS oc
1526 WHERE ps.party_site_id = ps_id
1527 AND rl.PARTY_ID = ps.party_id
1528 AND oc.party_relationship_id = rl.relationship_id
1529 AND rl.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1530 AND rl.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1531 AND rl.DIRECTIONAL_FLAG = 'F';
1532
1533 l_cp_party_site_id NUMBER;
1534 l_cp_org_contact_id NUMBER;
1535
1536 BEGIN
1537
1538 x_return_status := FND_API.G_RET_STS_SUCCESS;
1539
1540 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
1541
1542 IF ( L_REALTIME_SYNC_VALUE = 'Y') THEN
1543 BEGIN
1544 DELETE FROM HZ_STAGED_CONTACT_POINTS
1545 WHERE contact_point_id = p_from_id;
1546 EXCEPTION
1547 WHEN NO_DATA_FOUND THEN
1548 NULL;
1549 END;
1550 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN--Bug 13810656 Deferring the delete of from contact_point_id for Merge flow
1551 BEGIN
1552 DELETE FROM HZ_DQM_SYNC_INTERFACE WHERE STAGED_FLAG='N'
1553 AND RECORD_ID=p_from_id AND ENTITY='CONTACT_POINTS';
1554 EXCEPTION
1555 WHEN NO_DATA_FOUND THEN
1556 NULL;
1557 END;
1558 sync_contact_point (p_from_id,'D');
1559 END IF;
1560
1561 IF p_from_fk_id = p_to_fk_id THEN
1562
1563 l_contact_point_id := p_from_id;
1564 l_party_id := 0;
1565
1566 SELECT owner_table_name,owner_table_id INTO l_ot_table, l_ot_id
1567 FROM hz_contact_points
1568 WHERE contact_point_id = l_contact_point_id;
1569
1570 IF l_ot_table = 'HZ_PARTY_SITES' THEN
1571 SELECT party_id INTO l_party_id
1572 FROM HZ_PARTY_SITES
1573 WHERE party_site_id = l_ot_id;
1574
1575 ELSIF l_ot_table = 'HZ_PARTIES' THEN
1576 SELECT party_type INTO l_party_type
1577 FROM hz_parties
1578 WHERE party_id = l_ot_id;
1579
1580 IF l_party_type = 'ORGANIZATION' OR l_party_type = 'PERSON' THEN
1581 l_party_id := l_ot_id;
1582 ELSIF l_party_type = 'PARTY_RELATIONSHIP' THEN
1583 SELECT relationship_id, object_id INTO l_pr_id, l_party_id
1584 FROM hz_relationships --bug 4500011 replaced hz_party_relationships with hz_relationships
1585 WHERE party_id = l_ot_id
1586 AND subject_table_name = 'HZ_PARTIES'
1587 AND object_table_name = 'HZ_PARTIES'
1588 AND directional_flag = 'F';
1589
1590 SELECT count(1) INTO l_num_ocs
1591 FROM HZ_ORG_CONTACTS
1592 WHERE party_relationship_id = l_pr_id;
1593
1594 IF l_num_ocs = 0 THEN
1595 l_party_id := 0;
1596 END IF;
1597 END IF;
1598 END IF;
1599
1600 IF l_party_id <> 0 AND l_party_id IS NOT NULL THEN
1601 fnd_file.put_line(fnd_file.log,'pt 11');
1602 SAVEPOINT contact_point_sync;
1603 fnd_file.put_line(fnd_file.log,'pt 12');
1604 BEGIN
1605 sync_contact_point (l_contact_point_id,'C');
1606
1607
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610 ROLLBACK TO contact_point_sync;
1611 sync_contact_point (p_from_id, 'C');
1612 END;
1613 END IF;
1614 END IF;
1615
1616 EXCEPTION
1617 WHEN OTHERS THEN
1618 fnd_file.put_line(fnd_file.log,'Error here1 '||SQLERRM);
1619 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1620 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1621 FND_MSG_PUB.ADD;
1622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1623 END stage_contact_point_merge;
1624
1625
1626 PROCEDURE stage_contact_merge(
1627 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1628 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1629 x_to_id IN OUT NOCOPY NUMBER,
1630 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1631 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1632 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1633 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
1634 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
1635 x_return_status OUT NOCOPY VARCHAR2
1636 ) IS
1637
1638 l_org_contact_id NUMBER;
1639 l_party_id NUMBER;
1640 l_contact_search_rec HZ_PARTY_SEARCH.contact_search_rec_type;
1641 l_contact_stage_rec HZ_PARTY_STAGE.CONTACT_STAGE_REC_TYPE;
1642 l_rel_party_id NUMBER;
1643 BEGIN
1644
1645 x_return_status := FND_API.G_RET_STS_SUCCESS;
1646 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
1647
1648 IF ( L_REALTIME_SYNC_VALUE = 'Y') THEN
1649 BEGIN
1650 DELETE FROM HZ_STAGED_CONTACTS
1651 WHERE org_contact_id = p_from_id;
1652 EXCEPTION
1653 WHEN NO_DATA_FOUND THEN
1654 NULL;
1655 END;
1656 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN----Bug 13810656 Deferring the delete of from contacts for Merge flow
1657 sync_contact(p_from_id,'D');
1658 END IF;
1659
1660
1661 IF p_from_fk_id = p_to_fk_id THEN
1662 l_org_contact_id := p_from_id;
1663
1664 SELECT pr.party_id, pr.object_id INTO l_rel_party_id, l_party_id
1665 FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc --bug 4500011 replaced hz_party_relationships with hz_relationships
1666 WHERE oc.org_contact_id = l_org_contact_id
1667 AND pr.relationship_id = oc.party_relationship_id
1668 AND pr.subject_table_name = 'HZ_PARTIES'
1669 AND pr.object_table_name = 'HZ_PARTIES'
1670 AND pr.directional_flag = 'F';
1671
1672 SAVEPOINT contact_sync;
1673 IF ( L_REALTIME_SYNC_VALUE = 'Y') THEN
1674 BEGIN
1675 sync_contact(l_org_contact_id,'C');
1676
1677 UPDATE HZ_STAGED_PARTY_SITES
1678 SET party_id = l_party_id
1679 WHERE party_site_id in (
1680 SELECT party_site_id
1681 FROM HZ_PARTY_SITES
1682 WHERE party_id = l_rel_party_id
1683 AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'));
1684
1685 UPDATE HZ_STAGED_CONTACT_POINTS
1686 SET party_id = l_party_id
1687 WHERE contact_point_id in (
1688 SELECT contact_point_id
1689 FROM HZ_CONTACT_POINTS
1690 WHERE owner_table_name = 'HZ_PARTIES'
1691 AND owner_table_id = l_rel_party_id
1692 AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'));
1693
1694
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 ROLLBACK TO contact_sync;
1698 sync_contact(p_from_id, 'C');
1699 END;
1700 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN----Bug 13810656 Deferring the update of relationships for Merge flow
1701 BEGIN
1702 FOR I IN (SELECT party_site_id
1703 FROM HZ_PARTY_SITES
1704 WHERE party_id = l_rel_party_id
1705 AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'))
1706 LOOP
1707 sync_party_site (I.party_site_id, 'U');
1708 END LOOP;
1709
1710 FOR I IN (SELECT contact_point_id
1711 FROM HZ_CONTACT_POINTS
1712 WHERE owner_table_name = 'HZ_PARTIES'
1713 AND owner_table_id = l_rel_party_id
1714 AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'))
1715 LOOP
1716 sync_contact_point (I.contact_point_id, 'U');
1717 END LOOP;
1718
1719 sync_contact(l_org_contact_id,'C');
1720 EXCEPTION
1721 WHEN OTHERS THEN
1722 ROLLBACK TO contact_sync;
1723 sync_contact(p_from_id, 'C');
1724 END;
1725
1726 END IF;
1727
1728 END IF;
1729
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1733 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1734 FND_MSG_PUB.ADD;
1735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1736
1737 END stage_contact_merge;
1738
1739
1740 -- procedure to sync data realtime from hx_dqm_sync_interface table
1741 -- the program sets staged_flag = 'P' as an intermediate step during complete processing of rows in going on.
1742 FUNCTION realtime_sync (p_subscription_guid IN RAW,
1743 p_event IN OUT NOCOPY WF_EVENT_T) return VARCHAR2
1744 AS
1745
1746 TYPE PartyIdList IS TABLE OF NUMBER;
1747 TYPE OperationList IS TABLE OF VARCHAR2(1);
1748 TYPE EntityList IS TABLE OF VARCHAR2(30);
1749
1750 l_party_id PartyIdList;
1751 l_record_id PartyIdList;
1752 l_entity EntityList;
1753 l_operation OperationList;
1754 l_party_type VARCHAR2(30);
1755 l_sql_error_message VARCHAR2(2000);
1756 l_rowid EntityList;
1757
1758 errbuf VARCHAR2(1000);
1759 retcode VARCHAR2(10);
1760
1761 i_party boolean := false;
1762 i_party_sites boolean := false;
1763 i_contacts boolean := false;
1764 i_contact_points boolean := false;
1765 l_dqm_run VARCHAR2(1);
1766
1767
1768 BEGIN
1769
1770 select 'Y' into l_dqm_run
1771 from HZ_TRANS_FUNCTIONS_VL
1772 where STAGED_FLAG='Y'
1773 and nvl(ACTIVE_FLAG,'Y')='Y'
1774 and rownum = 1;
1775
1776 IF (l_dqm_run = 'Y') THEN
1777
1778 update HZ_DQM_SYNC_INTERFACE set STAGED_FLAG = 'P'
1779 where STAGED_FLAG = 'N' and REALTIME_SYNC_FLAG = 'Y'
1780 returning party_id, record_id, entity, operation, rowid BULK COLLECT into
1781 l_party_id, l_record_id, l_entity, l_operation, l_rowid;
1782
1783 COMMIT;
1784
1785 FOR i in 1..l_party_id.COUNT LOOP
1786 BEGIN
1787 IF (l_entity(i) = 'PARTY') THEN
1788 select party_type into l_party_type from hz_parties where party_id = l_party_id(i);
1789 hz_trans_pkg.set_party_type(l_party_type);
1790 HZ_STAGE_MAP_TRANSFORM.sync_single_party(l_party_id(i), l_party_type, l_operation(i));
1791 i_party := true;
1792 ELSIF (l_entity(i) = 'PARTY_SITES') THEN
1793 HZ_STAGE_MAP_TRANSFORM.sync_single_party_site(l_record_id(i), l_operation(i));
1794 i_party := true;
1795 i_party_sites := true;
1796 ELSIF (l_entity(i) = 'CONTACTS') THEN
1797 HZ_STAGE_MAP_TRANSFORM.sync_single_contact(l_record_id(i), l_operation(i));
1798 i_party := true;
1799 i_contacts := true;
1800 ELSIF (l_entity(i) = 'CONTACT_POINTS') THEN
1801 HZ_STAGE_MAP_TRANSFORM.sync_single_contact_point(l_record_id(i), l_operation(i));
1802 i_party := true;
1803 i_contact_points := true;
1804 END IF;
1805
1806 BEGIN
1807 IF (l_entity(i) <> 'PARTY') THEN
1808 insert_into_interface(l_party_id(i));
1809 END IF;
1810 IF l_operation(i) = 'C' THEN
1811 DELETE FROM hz_dqm_sync_interface WHERE rowid = l_rowid(i) ;
1812 ELSE
1813 UPDATE hz_dqm_sync_interface SET staged_flag = 'Y' WHERE rowid = l_rowid(i);
1814 END IF;
1815 EXCEPTION WHEN OTHERS THEN
1816 NULL;
1817 END;
1818
1819 EXCEPTION
1820 WHEN OTHERS THEN
1821 -- update staged_flag to 'E' if program generates an error.
1822 l_sql_error_message := SQLERRM;
1823 UPDATE hz_dqm_sync_interface SET error_data = l_sql_error_message, staged_flag = 'E' WHERE ROWID = l_rowid(i);
1824 END;
1825
1826 COMMIT;
1827 END LOOP ;
1828 COMMIT;
1829
1830 REALTIME_SYNC_INDEXES(i_party, i_party_sites, i_contacts, i_contact_points);
1831 RETURN 'SUCCESS';
1832 END IF;
1833 EXCEPTION
1834 when others then
1835 IF p_subscription_guid IS NOT NULL THEN
1836 WF_CORE.context('HZ_DQM_SYNC', 'REALTIME_SYNC', p_event.getEventName(), p_subscription_guid);
1837 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1838 END IF;
1839 return 'ERROR';
1840
1841 END REALTIME_SYNC;
1842
1843
1844
1845 -- procedure to sync indexes real time. Commented code will be useful later.
1846 PROCEDURE realtime_sync_indexes(i_party IN boolean,
1847 i_party_sites IN boolean,
1848 i_contacts IN boolean,
1849 i_contact_points IN boolean
1850 )
1851 AS
1852
1853 idx_retcode varchar2(1);
1854 idx_err varchar2(2000);
1855
1856 l_status VARCHAR2(255);
1857 l_index_owner VARCHAR2(255);
1858 l_tmp VARCHAR2(2000);
1859
1860 BEGIN
1861 IF(fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_index_owner)) THEN
1862 IF (i_party) THEN
1863 SYNC_INDEX_REALTIME(l_index_owner || '.hz_stage_parties_t1',
1864 idx_retcode , idx_err);
1865 IF idx_retcode = 1 THEN
1866 RAISE FND_API.G_EXC_ERROR;
1867 END IF;
1868
1869 END IF;
1870 IF (i_party_sites) THEN
1871 SYNC_INDEX_REALTIME(l_index_owner || '.hz_stage_party_sites_t1',
1872 idx_retcode , idx_err);
1873 IF idx_retcode = 1 THEN
1874 RAISE FND_API.G_EXC_ERROR;
1875 END IF;
1876
1877 END IF;
1878 IF (i_contacts) THEN
1879 SYNC_INDEX_REALTIME(l_index_owner || '.hz_stage_contact_t1',
1880 idx_retcode , idx_err);
1881 IF idx_retcode = 1 THEN
1882 RAISE FND_API.G_EXC_ERROR;
1883 END IF;
1884
1885 END IF;
1886 IF (i_contact_points) THEN
1887 SYNC_INDEX_REALTIME(l_index_owner || '.hz_stage_cpt_t1',
1888 idx_retcode , idx_err);
1889 IF idx_retcode = 1 THEN
1890 RAISE FND_API.G_EXC_ERROR;
1891 END IF;
1892 END IF;
1893 END IF;
1894 EXCEPTION
1895 WHEN FND_API.G_EXC_ERROR THEN
1896 outandlog('Error : Aborting Program');
1897 outandlog(idx_err);
1898 WHEN OTHERS THEN
1899 outandlog('Error : Aborting Program');
1900 outandlog(SQLERRM);
1901 END REALTIME_SYNC_INDEXES;
1902
1903 -- returns 'Y' if at least one row present in the global temporary table.
1904 -- This is done to reduce the number of events fired.
1905 FUNCTION check_for_transaction
1906 RETURN VARCHAR2 IS
1907
1908 bool varchar2(1) := 'N';
1909
1910 BEGIN
1911 SELECT 'Y' INTO bool
1912 FROM HZ_DQM_SYNC_GT
1913 WHERE ROWNUM = 1;
1914 IF bool <> 'Y' THEN
1915 bool := 'N';
1916 END IF;
1917 RETURN bool;
1918 EXCEPTION
1919 WHEN NO_DATA_FOUND THEN
1920 RETURN bool;
1921 END check_for_transaction;
1922
1923
1924 PROCEDURE sync_work_unit(
1925 retcode OUT NOCOPY VARCHAR2,
1926 err OUT NOCOPY VARCHAR2,
1927 p_from_rec IN VARCHAR2,
1928 p_to_rec IN VARCHAR2,
1929 p_sync_type IN VARCHAR2
1930 ) IS
1931
1932 -- REPURI - Removed all the variable declarations not being used,
1933 -- as part of code changes for Sync Performance Improvement Project.
1934
1935 l_sql_error_message VARCHAR2(2000);
1936 l_do_exec boolean := TRUE;
1937 l_sync_party_cur HZ_DQM_SYNC.SyncCurTyp;
1938 l_sync_party_site_cur HZ_DQM_SYNC.SyncCurTyp;
1939 l_sync_contact_cur HZ_DQM_SYNC.SyncCurTyp;
1940 l_sync_cpt_cur HZ_DQM_SYNC.SyncCurTyp;
1941
1942 BEGIN
1943
1944 log(' p_from_rec = '|| p_from_rec);
1945 log(' p_to_rec = '|| p_to_rec);
1946 log(' p_sync_type = ' || p_sync_type);
1947 LOOP
1948 BEGIN
1949 IF (l_do_exec = FALSE) THEN
1950 log('l_do_exec is false');
1951 EXIT;
1952 ELSE
1953 log('l_do_exec is true');
1954 l_do_exec := FALSE;
1955 END IF;
1956
1957 -- Part of DQM Sync Peformance Improvements Project (REPURI).
1958
1959 -- Instead of calling the sync_single_xxx in a LOOP for each row, we now call
1960 -- the new open_sync_xxx_cursor and pass the IN OUT CURSOR to sync_all_xxx APIs
1961 -- for all the 4 entities, to Fetch and Insert/Update data in Bulk Mode.
1962
1963 log ('-----------------------------');
1964 log ('Begin DQM SYNC');
1965 log ('-----------------------------');
1966
1967 -- Sync all organization parties
1968 -- For Create
1969 log ('-----------------------------');
1970 log ('For Create Organization Party');
1971 log ('-----------------------------');
1972 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor');
1973 HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor ('C','ORGANIZATION', p_from_rec, p_to_rec, l_sync_party_cur);
1974 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
1975 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('C', 'DQM_SYNC' ,l_sync_party_cur);
1976 -- For Update
1977 log ('-----------------------------');
1978 log ('For Update Organization Party');
1979 log ('-----------------------------');
1980 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor');
1981 HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor ('U','ORGANIZATION', p_from_rec, p_to_rec, l_sync_party_cur);
1982 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
1983 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('U', 'DQM_SYNC' , l_sync_party_cur);
1984
1985 -- Sync all person parties
1986 -- For Create
1987 log ('-----------------------');
1988 log ('For Create Person Party');
1989 log ('-----------------------');
1990 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor');
1991 HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor ('C','PERSON', p_from_rec, p_to_rec, l_sync_party_cur);
1992 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
1993 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('C', 'DQM_SYNC', l_sync_party_cur);
1994 -- For Update
1995 log ('-----------------------');
1996 log ('For Update Person Party');
1997 log ('-----------------------');
1998 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor');
1999 HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor ('U','PERSON', p_from_rec, p_to_rec, l_sync_party_cur);
2000 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
2001 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('U', 'DQM_SYNC', l_sync_party_cur);
2002
2003 -- Sync all party_sites
2004 -- For Create
2005 log ('----------------------');
2006 log ('For Create Party Sites');
2007 log ('----------------------');
2008 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_site_cursor');
2009 HZ_STAGE_MAP_TRANSFORM.open_sync_party_site_cursor ('C', p_from_rec, p_to_rec, l_sync_party_site_cur);
2010 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites');
2011 HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites ('C', 'DQM_SYNC', l_sync_party_site_cur);
2012 -- For Update
2013 log ('----------------------');
2014 log ('For Update Party Sites');
2015 log ('----------------------');
2016 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_site_cursor');
2017 HZ_STAGE_MAP_TRANSFORM.open_sync_party_site_cursor ('U', p_from_rec, p_to_rec, l_sync_party_site_cur);
2018 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites');
2019 HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites ('U', 'DQM_SYNC', l_sync_party_site_cur);
2020
2021 -- Sync all contacts
2022 -- For Create
2023 log ('-------------------');
2024 log ('For Create Contacts');
2025 log ('-------------------');
2026 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_contact_cursor');
2027 HZ_STAGE_MAP_TRANSFORM.open_sync_contact_cursor ('C', p_from_rec, p_to_rec, l_sync_contact_cur);
2028 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contacts');
2029 HZ_STAGE_MAP_TRANSFORM.sync_all_contacts ('C', 'DQM_SYNC', l_sync_contact_cur);
2030 -- For Update
2031 log ('-------------------');
2032 log ('For Update Contacts');
2033 log ('-------------------');
2034 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_contact_cursor');
2035 HZ_STAGE_MAP_TRANSFORM.open_sync_contact_cursor ('U', p_from_rec, p_to_rec, l_sync_contact_cur);
2036 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contacts');
2037 HZ_STAGE_MAP_TRANSFORM.sync_all_contacts ('U', 'DQM_SYNC', l_sync_contact_cur);
2038
2039 -- Sync all contact_points
2040 -- For Create
2041 log ('-------------------------');
2042 log ('For Create Contact Points');
2043 log ('-------------------------');
2044 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_cpt_cursor');
2045 HZ_STAGE_MAP_TRANSFORM.open_sync_cpt_cursor ('C', p_from_rec, p_to_rec, l_sync_cpt_cur);
2046 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points');
2047 HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points ('C', 'DQM_SYNC', l_sync_cpt_cur);
2048 -- For Update
2049 log ('-------------------------');
2050 log ('For Update Contact Points');
2051 log ('-------------------------');
2052 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_cpt_cursor');
2053 HZ_STAGE_MAP_TRANSFORM.open_sync_cpt_cursor ('U', p_from_rec, p_to_rec, l_sync_cpt_cur);
2054 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points');
2055 HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points ('U', 'DQM_SYNC', l_sync_cpt_cur);
2056
2057 -- Delete from from hz_dqm_sync_interface table, all the range
2058 -- of records that are already processed
2059 DELETE FROM hz_dqm_sync_interface
2060 WHERE staged_flag = 'N'
2061 AND error_data IS NULL
2062 AND sync_interface_num BETWEEN p_from_rec AND p_to_rec;
2063
2064 EXCEPTION
2065 WHEN OTHERS THEN
2066 IF SQLCODE = -1555 THEN
2067 l_do_exec := true;
2068 log(' Snapshot too old exception raised and caught. Cursor re-executed. ');
2069 ELSE
2070 retcode := 2;
2071 err := SQLERRM;
2072 log(err);
2073 RAISE;
2074 EXIT;
2075 END IF;
2076 END;
2077
2078 END LOOP ;
2079
2080 EXCEPTION
2081 WHEN OTHERS THEN
2082 retcode := 2;
2083 err := SQLERRM;
2084 log(err);
2085
2086 END sync_work_unit;
2087
2088
2089 PROCEDURE sync_parties(retcode OUT NOCOPY VARCHAR2,
2090 err OUT NOCOPY VARCHAR2,
2091 p_num_of_workers IN VARCHAR2,
2092 p_indexes_only IN VARCHAR2
2093 ) IS
2094
2095 l_num_of_workers NUMBER;
2096 l_min_id NUMBER;
2097 l_max_id NUMBER;
2098 l_range NUMBER;
2099 l_count NUMBER;
2100 l_from_rec NUMBER;
2101 l_to_rec NUMBER;
2102 l_from_rec_v VARCHAR2(255);
2103 l_to_rec_v VARCHAR2(255);
2104 idx_name VARCHAR2(300); -- VJN Increased Size of this for P1 4096839, from 30 to 300
2105 l_index_owner VARCHAR2(255);
2106 l_status VARCHAR2(255);
2107 l_tmp VARCHAR2(2000);
2108 idx_retcode varchar2(1);
2109 idx_err varchar2(2000);
2110 TYPE nTable IS TABLE OF NUMBER index by binary_integer;
2111 l_sub_requests nTable;
2112
2113 l_range1 NUMBER;
2114 j number := 0;
2115 req_data varchar2(30);
2116 l_indexes_only VARCHAR2(30);
2117 l_workers_completed boolean ;
2118
2119
2120
2121 -- VJN Introduced
2122
2123 FIRST BOOLEAN ;
2124 l_index_conc_program_req_id NUMBER ;
2125 l_request_id NUMBER ;
2126 --Start of bug 4915282
2127 CURSOR c_non_indexed IS select 1 from hz_dqm_stage_log where operation= 'ALTER_INDEX'
2128 and start_flag = 'Y' and end_flag ='Y';
2129 l_index_count number := 0;
2130
2131
2132 --End of bug 4915282
2133 BEGIN
2134 log (' -------------------------------------------');
2135 log ('Entering procedure sync_parties');
2136 log ('p_num_of_workers = ' || p_num_of_workers);
2137 log('p_indexes_only = '|| p_indexes_only);
2138
2139 req_data := fnd_conc_global.request_data;
2140
2141 log(' req_data = ' || req_data);
2142
2143 l_indexes_only := nvl(p_indexes_only, 'N');
2144
2145 log (' l_indexes_only = ' || l_indexes_only);
2146
2147
2148
2149 /************* we don't have to support this ************/
2150 -- IF ( l_indexes_only = 'Y') AND (req_data IS NULL) THEN
2151 -- req_data := 'PAUSED_FOR_INDEX';
2152 -- END IF;
2153
2154 -- First Phase
2155 IF ( req_data IS NULL) THEN
2156
2157
2158 /* Code for handling the records with operation 'D' Flag*/
2159
2160 FOR I in (SELECT PARTY_ID,RECORD_ID,ENTITY FROM HZ_DQM_SYNC_INTERFACE WHERE OPERATION='D' AND STAGED_FLAG='N')
2161 LOOP
2162
2163 IF (I.ENTITY='PARTY')
2164 THEN
2165 BEGIN
2166 UPDATE HZ_STAGED_PARTIES
2167 SET STATUS = 'M'
2168 WHERE party_id = I.PARTY_ID;
2169 EXCEPTION
2170 WHEN NO_DATA_FOUND THEN
2171 NULL;
2172 END;
2173 END IF;
2174
2175 IF (I.ENTITY='PARTY_SITES')
2176 THEN
2177 BEGIN
2178 DELETE FROM HZ_STAGED_PARTY_SITES
2179 WHERE party_site_id = I.RECORD_ID;
2180 EXCEPTION
2181 WHEN NO_DATA_FOUND THEN
2182 NULL;
2183 END;
2184 END IF;
2185
2186 IF (I.ENTITY='CONTACTS')
2187 THEN
2188 BEGIN
2189 DELETE FROM HZ_STAGED_CONTACTS
2190 WHERE org_contact_id = I.RECORD_ID;
2191 EXCEPTION
2192 WHEN NO_DATA_FOUND THEN
2193 NULL;
2194 END;
2195 END IF;
2196
2197 IF (I.ENTITY='CONTACT_POINTS')
2198 THEN
2199 BEGIN
2200 DELETE FROM HZ_STAGED_CONTACT_POINTS
2201 WHERE contact_point_id = I.RECORD_ID;
2202 EXCEPTION
2203 WHEN NO_DATA_FOUND THEN
2204 NULL;
2205 END;
2206 END IF;
2207
2208
2209 END LOOP;
2210
2211 /* Code for handling the records with operation 'D' Flag*/
2212
2213 --Start Bug:5407223---
2214 /* Delete records from HZ_DQM_SYNC_INTERFACE table if that record already exists in staged table*/
2215 log('Start Time for delete statements = ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
2216 delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='PARTY' and operation='C'
2217 and party_id in (select /*+ parallel_index(s) index_ffs(s) */ party_id from hz_staged_parties s );
2218 log (' After delete duplicate party entity records, time= '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
2219
2220 delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='PARTY_SITES' and operation='C'
2221 and record_id in (select /*+ parallel_index(s) index_ffs(s) */ party_site_id from hz_staged_party_sites s );
2222 log (' After delete duplicate party site entity records '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS') );
2223
2224 delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='CONTACTS' and operation='C'
2225 and record_id in (select /*+ parallel_index(s) index_ffs(s) */ org_contact_id from hz_staged_contacts s );
2226 log (' After delete duplicate contact entity records = '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS') );
2227
2228 delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='CONTACT_POINTS' and operation='C'
2229 and record_id in (select /*+ parallel_index(s) index_ffs(s) */ contact_point_id from hz_staged_contact_points s );
2230 log (' After delete duplicate contact point entity records = '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS') );
2231 log('End Time for delete statements = ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
2232
2233
2234
2235 --End Bug:5407223---
2236
2237 --start bug 5460390: Add commit
2238 COMMIT;
2239 FND_CONCURRENT.AF_Commit;
2240 log('Committed Records');
2241 --end bug 5460390
2242 -- Get number of workers
2243 l_num_of_workers := to_number(nvl(p_num_of_workers, '1'));
2244
2245 -- If number of workers is 0, make it 1
2246 IF (l_num_of_workers <= 0) THEN
2247 l_num_of_workers := 1;
2248 END IF;
2249
2250 -- Get range if any possible optimization could be done on the number of workers to
2251 -- be deployed
2252 SELECT min(sync_interface_num), max(sync_interface_num) into l_min_id, l_max_id
2253 FROM hz_dqm_sync_interface
2254 WHERE staged_flag = 'N';
2255 l_range := l_max_id - l_min_id;
2256
2257 IF ( l_range <= l_num_of_workers) THEN
2258 l_num_of_workers := 1;
2259 log('Too less data to process hence reducing the number of workers. ');
2260 END IF;
2261
2262 l_from_rec := l_min_id;
2266 log ('Data Workers only create/update data');
2263 l_range1 := floor(l_range/l_num_of_workers);
2264
2265 log('Total number of Data Workers deployed = ' || l_num_of_workers );
2267
2268 --get request_id of this program
2269 l_request_id := FND_GLOBAL.conc_request_id ;
2270
2271 -- Deploy Data Workers
2272
2273 FIRST := TRUE ;
2274 FOR I in 1..l_num_of_workers LOOP
2275 j := j + 1;
2276 l_to_rec := l_from_rec + l_range1;
2277 IF (l_to_rec > l_max_id) THEN
2278 l_to_rec := l_max_id;
2279 END IF;
2280 log ( 'Calling the DQM Import Sync child program for Data Worker ' || i);
2281 log ( 'l_from_rec and l_to_rec are : ' || to_char(l_from_rec) || ' , ' || TO_CHAR(l_to_rec) );
2282
2283 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHDCH',
2284 'DQM Import Sync Child Program' || to_char(i),
2285 NULL,--Bug No:3941365
2286 true, to_char(l_from_rec), TO_CHAR(l_to_rec), 'BATCH');
2287
2288 -- Boolean used to track the submission of the parallel sync concurrent program
2289
2290 IF l_sub_requests(i) = 0 THEN
2291 log('Error submitting worker ' || i);
2292 log(fnd_message.get);
2293 ELSE
2294 log('Submitted request for Worker ' || TO_CHAR(I) );
2295 log('Request ID : ' || l_sub_requests(i));
2296
2297 IF FIRST
2298 THEN
2299 FIRST := FALSE ;
2300
2301 log('Calling Parallel Sync Index concurrent program');
2302 log('Request Id of the program to be waited on, that is being passed to this : ' || l_request_id );
2303 l_index_conc_program_req_id := FND_REQUEST.SUBMIT_REQUEST('AR',
2304 'ARHDQMPP',
2305 'DQM Parallel Sync Index Parent Program',
2306 NULL,
2307 FALSE,
2308 l_request_id
2309 );
2310 log('Request Id of Parallel Sync concurrent Program is : ' || l_index_conc_program_req_id );
2311 END IF ;
2312 END IF;
2313
2314 EXIT when l_sub_requests(i) = 0;
2315 l_from_rec := l_to_rec + 1;
2316 IF (l_to_rec >= l_max_id) THEN
2317 EXIT;
2318 END IF;
2319 END LOOP;
2320
2321 -- be in paused status until all the concurrent requests submitted have completed
2322 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
2323 request_data => 'END') ;
2324 err := 'Concurrent Workers submitted.';
2325 retcode := 0;
2326
2327 ELSIF( req_data = 'END') THEN
2328 -- This is for error handling, to make sure that after the
2329 -- control of this program returns here, that we catch any
2330 -- errored concurrent requests that were spawned by this program
2331 -- and if any of them errored out, we error out this program
2332 -- itself
2333 log ('checking completion status of child programs spawned by tis program' );
2334 l_workers_completed := TRUE;
2335
2336 -- program id is hard coded since any conc program submitted by this program is construed
2337 -- as its child in FND_CONCURRENT_REQUESTS, regardless of the TRUE/FALSE flag
2338 -- used in FND_CONCURRENT.SUBMIT_REQUEST
2339
2340 select request_id BULK COLLECT into l_sub_requests
2341 from fnd_concurrent_requests R
2342 where parent_request_id = l_request_id
2343 and concurrent_program_id = 46839
2344 and (phase_code<>'C' or status_code<>'C');
2345
2346 IF l_sub_requests.count > 0 THEN
2347 l_workers_completed := FALSE;
2348 FOR I in 1..l_sub_requests.COUNT LOOP
2349 outandlog('Worker with request id ' || l_sub_requests(I) );
2350 outandlog('Did not complete normally.');
2351 retcode := 2;
2352 log(' retcode = ' || retcode);
2353 RAISE FND_API.G_EXC_ERROR;
2354 END LOOP;
2355 END IF;
2356 --Start of Bug No : 4915282
2357 --Set the index transactional, if the search on non indexed records is enabled.
2358 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
2359 IF(L_REALTIME_SYNC_VALUE = 'Y') THEN
2360 OPEN c_non_indexed;
2361 FETCH c_non_indexed INTO l_index_count;
2362 if(c_non_indexed%FOUND) then
2363 HZ_DQM_SYNC.set_index_transactional(enabled=>'Y');
2364 end if;
2365 CLOSE c_non_indexed;
2366 END IF;
2367 --End of Bug No : 4915282
2368 END IF;
2369
2370 EXCEPTION
2371 WHEN FND_API.G_EXC_ERROR THEN
2372 log('DQM Synchronization Program Aborted');
2373 retcode := 2;
2374 err := err || logerror || SQLERRM;
2375 RAISE;
2376 WHEN OTHERS THEN
2377 log('DQM Synchronization Program Aborted');
2378 retcode := 2;
2379 err := err || logerror || SQLERRM;
2380 RAISE;
2381 END sync_parties;
2382
2383
2384 PROCEDURE sync_index_conc(
2385 retcode OUT NOCOPY VARCHAR2,
2386 err OUT NOCOPY VARCHAR2,
2387 p_index_name IN VARCHAR2 ) IS
2388 BEGIN
2389 log('Index being synched ... ' || p_index_name);
2390 sync_index( p_index_name, retcode, err);
2391 if (retcode=2) then
2392 RAISE FND_API.G_EXC_ERROR;
2393 end if;
2394 EXCEPTION
2395 WHEN OTHERS THEN
2396 retcode := 2;
2397 err := SQLERRM;
2398 RAISE FND_API.G_EXC_ERROR;--
2399 END sync_index_conc;
2400
2401 ----------------------------------------
2402 -- VJN Changes for SYNC in R12
2403 ---------------------------------------
2404
2405 -- VJN modified for R12 for Bulk Import
2406 -- This API would be called by the Bulk Import Post Processing Program, to directly insert
2407 -- data into the STAGING tables
2408
2409 -- REPURI, Modifying to procedure signature to process the records in bulk mode. Bug 4884735.
2410 PROCEDURE sync_work_unit_imp(
2411 p_batch_id IN NUMBER,
2412 p_batch_mode_flag IN VARCHAR2,
2413 p_from_osr IN VARCHAR2,
2414 p_to_osr IN VARCHAR2,
2415 p_os IN VARCHAR2,
2416 x_return_status OUT NOCOPY VARCHAR2,
2417 x_msg_count OUT NOCOPY NUMBER,
2418 x_msg_data OUT NOCOPY VARCHAR2
2419 )
2420 IS
2421
2422 l_sync_party_cur HZ_DQM_SYNC.SyncCurTyp;
2423 l_sync_party_site_cur HZ_DQM_SYNC.SyncCurTyp;
2424 l_sync_contact_cur HZ_DQM_SYNC.SyncCurTyp;
2425 l_sync_cpt_cur HZ_DQM_SYNC.SyncCurTyp;
2426
2427 BEGIN
2428 -- Part of DQM Bulk Import Sync Peformance Improvements Project (REPURI). Bug 4884735.
2429
2430 -- Instead of calling the sync_single_xxx_online in a LOOP for each row, we now call
2431 -- the new open_bulk_imp_sync_xxx_cur and pass the IN OUT CURSOR to sync_all_xxx APIs
2432 -- for all the 4 entities, to Fetch and Insert/Update data in Bulk Mode.
2433
2434 log ('-----------------------------');
2435 log ('Begin Bulk Import SYNC');
2436 log ('-----------------------------');
2437 log ('');
2438 log ('------------------------');
2439 log ('Incoming variable values');
2440 log ('------------------------');
2441 log ('p_batch_id - '|| p_batch_id);
2442 log ('p_batch_mode_flag - '||p_batch_mode_flag);
2443 log ('p_from_osr - '||p_from_osr);
2444 log ('p_to_osr - '||p_to_osr);
2445 log ('p_os - '||p_os);
2446 log ('------------------------');
2447
2448 -- Sync all organization parties
2449 -- For Create
2450 log ('-----------------------------');
2451 log ('For Create Organization Party');
2452 log ('-----------------------------');
2453
2454 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur');
2455 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur (
2456 p_batch_id => p_batch_id
2457 ,p_batch_mode_flag => p_batch_mode_flag
2458 ,p_from_osr => p_from_osr
2459 ,p_to_osr => p_to_osr
2460 ,p_os => p_os
2461 ,p_party_type => 'ORGANIZATION'
2462 ,p_operation => 'I'
2463 ,x_sync_party_cur => l_sync_party_cur
2464 );
2465
2466 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
2467 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('C', 'IMPORT_SYNC', l_sync_party_cur);
2468
2469 -- For Update
2470 log ('-----------------------------');
2471 log ('For Update Organization Party');
2472 log ('-----------------------------');
2473
2474 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur');
2475 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur (
2476 p_batch_id => p_batch_id
2477 ,p_batch_mode_flag => p_batch_mode_flag
2478 ,p_from_osr => p_from_osr
2479 ,p_to_osr => p_to_osr
2480 ,p_os => p_os
2481 ,p_party_type => 'ORGANIZATION'
2482 ,p_operation => 'U'
2483 ,x_sync_party_cur => l_sync_party_cur
2484 );
2485
2486 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
2487 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('U', 'IMPORT_SYNC', l_sync_party_cur);
2488
2489 -- Sync all person parties
2490 -- For Create
2491
2492 log ('-----------------------');
2493 log ('For Create Person Party');
2494 log ('-----------------------');
2495
2496 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur');
2497 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur (
2498 p_batch_id => p_batch_id
2499 ,p_batch_mode_flag => p_batch_mode_flag
2500 ,p_from_osr => p_from_osr
2501 ,p_to_osr => p_to_osr
2502 ,p_os => p_os
2503 ,p_party_type => 'PERSON'
2504 ,p_operation => 'I'
2505 ,x_sync_party_cur => l_sync_party_cur
2506 );
2507
2508 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
2509 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('C', 'IMPORT_SYNC', l_sync_party_cur);
2510
2511 -- For Update
2512
2513 log ('-----------------------');
2514 log ('For Update Person Party');
2515 log ('-----------------------');
2516
2517 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur');
2518 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur (
2519 p_batch_id => p_batch_id
2520 ,p_batch_mode_flag => p_batch_mode_flag
2521 ,p_from_osr => p_from_osr
2522 ,p_to_osr => p_to_osr
2523 ,p_os => p_os
2524 ,p_party_type => 'PERSON'
2525 ,p_operation => 'U'
2526 ,x_sync_party_cur => l_sync_party_cur
2527 );
2528
2529 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
2530 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('U', 'IMPORT_SYNC', l_sync_party_cur);
2531
2532 -- Sync all party_sites
2533 -- For Create
2534
2535 log ('----------------------');
2536 log ('For Create Party Sites');
2537 log ('----------------------');
2538
2539 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_psite_cur');
2543 ,p_from_osr => p_from_osr
2540 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_psite_cur (
2541 p_batch_id => p_batch_id
2542 ,p_batch_mode_flag => p_batch_mode_flag
2544 ,p_to_osr => p_to_osr
2545 ,p_os => p_os
2546 ,p_operation => 'I'
2547 ,x_sync_party_site_cur => l_sync_party_site_cur
2548 );
2549
2550 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites');
2551 HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites ('C', 'IMPORT_SYNC', l_sync_party_site_cur);
2552
2553 -- For Update
2554 log ('----------------------');
2555 log ('For Update Party Sites');
2556 log ('----------------------');
2557
2558 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_psite_cur');
2559 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_psite_cur (
2560 p_batch_id => p_batch_id
2561 ,p_batch_mode_flag => p_batch_mode_flag
2562 ,p_from_osr => p_from_osr
2563 ,p_to_osr => p_to_osr
2564 ,p_os => p_os
2565 ,p_operation => 'U'
2566 ,x_sync_party_site_cur => l_sync_party_site_cur
2567 );
2568
2569 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites');
2570 HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites ('U', 'IMPORT_SYNC', l_sync_party_site_cur);
2571
2572 -- Sync all contacts
2573 -- For Create
2574 log ('-------------------');
2575 log ('For Create Contacts');
2576 log ('-------------------');
2577
2578 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_ct_cur');
2579 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_ct_cur (
2580 p_batch_id => p_batch_id
2581 ,p_batch_mode_flag => p_batch_mode_flag
2582 ,p_from_osr => p_from_osr
2583 ,p_to_osr => p_to_osr
2584 ,p_os => p_os
2585 ,p_operation => 'I'
2586 ,x_sync_contact_cur => l_sync_contact_cur
2587 );
2588
2589 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contacts');
2590 HZ_STAGE_MAP_TRANSFORM.sync_all_contacts ('C', 'IMPORT_SYNC', l_sync_contact_cur);
2591 -- For Update
2592 log ('-------------------');
2593 log ('For Update Contacts');
2594 log ('-------------------');
2595
2596 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_ct_cur');
2597 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_ct_cur (
2598 p_batch_id => p_batch_id
2599 ,p_batch_mode_flag => p_batch_mode_flag
2600 ,p_from_osr => p_from_osr
2601 ,p_to_osr => p_to_osr
2602 ,p_os => p_os
2603 ,p_operation => 'U'
2604 ,x_sync_contact_cur => l_sync_contact_cur
2605 );
2606
2607 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contacts');
2608 HZ_STAGE_MAP_TRANSFORM.sync_all_contacts ('U', 'IMPORT_SYNC', l_sync_contact_cur);
2609
2610 -- Sync all contact_points
2611 -- For Create
2612
2613 log ('-------------------------');
2614 log ('For Create Contact Points');
2615 log ('-------------------------');
2616
2617 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_cpt_cur');
2618 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_cpt_cur (
2619 p_batch_id => p_batch_id
2620 ,p_batch_mode_flag => p_batch_mode_flag
2621 ,p_from_osr => p_from_osr
2622 ,p_to_osr => p_to_osr
2623 ,p_os => p_os
2624 ,p_operation => 'I'
2625 ,x_sync_cpt_cur => l_sync_cpt_cur
2626 );
2627
2628 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points');
2629 HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points ('C', 'IMPORT_SYNC', l_sync_cpt_cur);
2630
2631 -- For Update
2632
2633 log ('-------------------------');
2634 log ('For Update Contact Points');
2635 log ('-------------------------');
2636
2637 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_cpt_cur');
2638 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_cpt_cur (
2639 p_batch_id => p_batch_id
2640 ,p_batch_mode_flag => p_batch_mode_flag
2641 ,p_from_osr => p_from_osr
2642 ,p_to_osr => p_to_osr
2643 ,p_os => p_os
2644 ,p_operation => 'U'
2645 ,x_sync_cpt_cur => l_sync_cpt_cur
2646 );
2647
2648 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points');
2649 HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points ('U', 'IMPORT_SYNC', l_sync_cpt_cur);
2650
2651 END ;
2652
2653 -- This would take a request_id , see if the corresponding conc program is complete
2654 -- and return TRUE or FALSE
2655 FUNCTION is_conc_complete ( p_request_id IN NUMBER) RETURN BOOLEAN
2656 IS
2657 req_id NUMBER ;
2658 rphase varchar2(30);
2659 rstatus varchar2(30);
2660 dphase varchar2(30);
2661 dstatus varchar2(30);
2662 message varchar2(240);
2663 status boolean ;
2664
2665 BEGIN
2666 -- set request id we want to query
2667 req_id := p_request_id ;
2668
2669 -- call FND procedure to find status of concurrent program
2670 status := FND_CONCURRENT.GET_REQUEST_STATUS(req_id, NULL, NULL, rphase,rstatus,dphase,dstatus,message);
2671
2672 -- Return true any time request is complete
2673 -- IF dphase = 'RUNNING' OR dphase = 'PENDING'
2674 IF dphase = 'COMPLETE'
2675 THEN
2676 RETURN TRUE ;
2677 ELSE
2678 RETURN FALSE ;
2679 END IF ;
2680
2681 END ;
2682
2683 -- This will return true if the passed in index has any rows to be synced
2684 -- else will return false
2685 FUNCTION is_index_pending( p_index_name IN VARCHAR2,p_owner_name IN VARCHAR2) RETURN BOOLEAN
2686 IS
2687 BEGIN
2688 --bug 5929615
2689 FOR cur in
2690 ( SELECT 'Y' FROM
2691 (
2692 select
2693 u.name pnd_index_owner,
2694 idx_name pnd_index_name,
2695 ixp_name pnd_partition_name,
2696 pnd_rowid,
2697 pnd_timestamp
2698 from ctxsys.dr$pending, ctxsys.dr$index i, ctxsys.dr$index_partition p,
2699 sys.user$ u
2700 where idx_owner# = u.user#
2701 and idx_id = ixp_idx_id
2702 and pnd_pid = ixp_id
2703 and pnd_pid <> 0
2704 and pnd_cid = idx_id
2705 UNION ALL
2706 select
2707 u.name pnd_index_owner,
2708 idx_name pnd_index_name,
2709 null pnd_partition_name,
2710 pnd_rowid,
2711 pnd_timestamp
2712 from ctxsys.dr$pending, ctxsys.dr$index i, sys.user$ u
2713 where idx_owner# = u.user#
2714 and pnd_pid = 0
2715 and pnd_cid = idx_id
2716 )
2717 WHERE PND_INDEX_NAME = p_index_name
2718 and pnd_index_owner=p_owner_name
2719 and rownum =1
2720
2721 )
2722 LOOP
2723 RETURN TRUE ;
2724 END LOOP ;
2725
2726 RETURN FALSE ;
2727 END ;
2728
2729 -- VJN Introduced for setting transactional property of Index, a new feature
2730 -- for text indexes, available as part of 10g.
2731 PROCEDURE set_index_transactional( enabled IN VARCHAR2 )
2732 IS
2733 l_bool boolean ;
2734
2735 l_status VARCHAR2(255);
2736 l_temp VARCHAR2(255);
2737 l_index_owner VARCHAR2(255);
2738 index_cnt NUMBER;
2739 BEGIN
2740 -- GET THE INDEX OWNER INSTEAD OF HARDCODING IT
2741 -- THIS FND FUNCTION, WILL TAKE THE APPLICATION SHORT NAME AND RETURN THE ORACLE_SCHEMA FOR THE USER
2742 -- AND THIS IS ESSENTIALLY THE INDEX OWNER
2743
2744 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_index_owner);
2745
2746 -- Propagating the changes done for Bug:4706376
2747 SELECT COUNT(*) INTO index_cnt FROM HZ_DQM_STAGE_LOG WHERE OPERATION='ALTER_INDEX';
2748
2749 IF enabled = 'Y'
2750 THEN
2751 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2752 '.hz_stage_parties_t1 rebuild parameters(''replace metadata transactional'')';
2753 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2754 '.hz_stage_party_sites_t1 rebuild parameters(''replace metadata transactional'')';
2755 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2756 '.hz_stage_contact_t1 rebuild parameters(''replace metadata transactional'')';
2757 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2758 '.hz_stage_cpt_t1 rebuild parameters(''replace metadata transactional'')';
2759
2760 -- Propagating the changes done for Bug:4706376
2761 if(index_cnt=null OR index_cnt=0) then
2762 insert into hz_dqm_stage_log(OPERATION, NUMBER_OF_WORKERS,WORKER_NUMBER,STEP,START_FLAG,START_TIME,END_FLAG,
2763 END_TIME,LAST_UPDATE_DATE, CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN )
2764 values ('ALTER_INDEX',1,1,'STAGED_TABLES','Y',sysdate,'Y',null,sysdate,sysdate,
2765 fnd_global.user_id,fnd_global.user_id,fnd_global.login_id);
2766 else
2767 update hz_dqm_stage_log set start_flag='Y',end_flag='Y',start_time=sysdate,
2768 last_update_date=sysdate,last_update_login=fnd_global.login_id
2769 where operation='ALTER_INDEX';
2770 end if;
2771
2772 ELSE
2773 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2774 '.hz_stage_parties_t1 rebuild parameters(''replace metadata nontransactional'')';
2775 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2776 '.hz_stage_party_sites_t1 rebuild parameters(''replace metadata nontransactional'')';
2777 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2778 '.hz_stage_contact_t1 rebuild parameters(''replace metadata nontransactional'')';
2779 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2780 '.hz_stage_cpt_t1 rebuild parameters(''replace metadata nontransactional'')';
2781
2782 -- Propagating the changes done for Bug:4706376
2783 if(index_cnt=null OR index_cnt=0) then
2784 insert into hz_dqm_stage_log(OPERATION, NUMBER_OF_WORKERS,WORKER_NUMBER,STEP,START_FLAG,START_TIME,END_FLAG,
2785 END_TIME,LAST_UPDATE_DATE, CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
2786 values ('ALTER_INDEX',1,1,'STAGED_TABLES','N',sysdate,'N',null,sysdate,sysdate,
2787 fnd_global.user_id,fnd_global.user_id,fnd_global.login_id);
2788 else
2789 update hz_dqm_stage_log set start_flag='N',end_flag='N',start_time=sysdate,
2790 last_update_date=sysdate,last_update_login=fnd_global.login_id
2791 where operation='ALTER_INDEX';
2792 end if;
2793
2794 END IF ;
2795 EXCEPTION WHEN OTHERS THEN
2796 NULL ;
2797
2798 END ;
2799
2800 -- conc program executable for Serial Sync Index Concurrent Program
2801 -- This will be used only by online (API) flows
2802 PROCEDURE sync_index_serial(
2803 retcode OUT NOCOPY VARCHAR2,
2804 err OUT NOCOPY VARCHAR2
2805 )
2806 IS
2807 idx_name VARCHAR2(300);
2808 l_index_owner VARCHAR2(255);
2809 l_status VARCHAR2(255);
2810 l_tmp VARCHAR2(2000);
2811 BEGIN
2812 retcode := 0;
2813 err := null;
2814
2815 -- Sequentially Sync all indexes one by one
2816 -- set OUT variables and write any messages to logs appropriately
2817 -- Fix for bug 5048604. Moved the call to Parties Sync Index to be the last, instead of being first.
2818 IF ( fnd_installation.GET_APP_INFO('AR', l_status, l_tmp, l_index_owner ))
2819 THEN
2820 log ( 'index_owner is ' || l_index_owner );
2821 BEGIN
2822 idx_name := l_index_owner || '.hz_stage_party_sites_t1';
2823 ad_ctx_Ddl.Sync_Index (idx_name);
2824 log ('Successful in syncing hz_stage_party_sites_t1 ');
2825 EXCEPTION
2826 WHEN OTHERS THEN
2827 retcode := 2;
2828 err := SQLERRM;
2829 log ('Error syncing hz_stage_party_sites_t1 :' || SQLERRM);
2830 END ;
2831
2832 BEGIN
2833 idx_name := l_index_owner || '.hz_stage_contact_t1';
2834 ad_ctx_Ddl.Sync_Index (idx_name);
2835 log ('Successful in syncing hz_stage_contact_t1 ');
2836 EXCEPTION
2837 WHEN OTHERS THEN
2838 retcode := 2;
2839 err := SQLERRM;
2840 log ('Error syncing hz_stage_contact_t1 :' || SQLERRM);
2841 END ;
2842
2843 BEGIN
2844 idx_name := l_index_owner || '.hz_stage_cpt_t1';
2845 ad_ctx_Ddl.Sync_Index (idx_name);
2846 log ('Successful in syncing hz_stage_cpt_t1 ');
2847 EXCEPTION
2848 WHEN OTHERS THEN
2849 retcode := 2;
2850 err := SQLERRM;
2851 log ('Error syncing hz_stage_cpt_t1 :' || SQLERRM);
2852 END ;
2853
2854 BEGIN
2855 idx_name := l_index_owner || '.hz_stage_parties_t1';
2856 ad_ctx_Ddl.Sync_Index (idx_name);
2857 log ('Successful in syncing hz_stage_parties_t1 ');
2858 EXCEPTION
2859 WHEN OTHERS THEN
2860 retcode := 2;
2861 err := SQLERRM;
2862 log ('Error syncing hz_stage_parties_t1 :' || SQLERRM);
2863 END ;
2864
2865 END IF ;
2866
2867 END ;
2868
2869 -- conc program executable for Parallel Sync Index Parent Concurrent Program
2870 -- This will be used by both Manual ( Batch) Synchronization and Bulk Import
2871
2872 PROCEDURE sync_index_parallel_parent (
2873 retcode OUT NOCOPY VARCHAR2,
2874 err OUT NOCOPY VARCHAR2,
2875 p_request_id IN NUMBER
2876 )
2877 IS
2878 req_data VARCHAR2(100);
2879 idx_name VARCHAR2(300);
2880 l_index_owner VARCHAR2(255);
2881 l_status VARCHAR2(255);
2882 l_tmp VARCHAR2(2000);
2883 idx_retcode varchar2(1);
2884 idx_err varchar2(2000);
2885 TYPE nTable IS TABLE OF NUMBER index by binary_integer;
2886 l_sub_requests nTable;
2887 l_workers_completed boolean ;
2888
2889 BEGIN
2890 -- req_data will be null the first time, by default
2891 req_data := fnd_conc_global.request_data;
2892
2893 -- First Phase
2894 -- Submit the Parallel Sync Index Child Concurrent Progarm for each one of the indexes
2895 IF (req_data IS NULL)
2896 THEN
2897 retcode := 0;
2898
2899 log('------------------------------');
2900 log('Starting DQM Parallel Sync Index Parent Program ');
2901
2902 FND_MSG_PUB.initialize;
2903
2904
2905 IF ( fnd_installation.GET_APP_INFO('AR', l_status, l_tmp, l_index_owner ))
2906 THEN
2907 log ( 'index_owner is ' || l_index_owner );
2908
2909 -- Submit requests for the Parallel Sync Index Child concurrent program
2910 -- for creating the four indexes
2911 FOR i in 1..4
2912 LOOP
2913 IF (i = 1) THEN
2914 idx_name := l_index_owner || '.hz_stage_parties_t1';
2915 ELSIF ( i = 2) THEN
2916 idx_name := l_index_owner || '.hz_stage_party_sites_t1';
2917 ELSIF ( i = 3) THEN
2918 idx_name := l_index_owner || '.hz_stage_contact_t1';
2919 ELSE
2920 idx_name :=l_index_owner || '.hz_stage_cpt_t1';
2921 END IF;
2922
2923 log('Calling the Parallel Sync Index Child program for index ' || idx_name );
2924 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR',
2925 'ARHDQMPC',
2926 'DQM Parallel Sync Index Child Program' || to_char(i),
2927 NULL,
2928 TRUE,
2929 p_request_id,
2930 idx_name);
2931 IF l_sub_requests(i) = 0 THEN
2932 log('Error submitting index worker for ' || idx_name);
2933 log(fnd_message.get);
2934 ELSE
2935 log('Submitted request for index worker ' || idx_name );
2936 log('Request ID : ' || l_sub_requests(i));
2937 END IF;
2938 EXIT when l_sub_requests(i) = 0;
2939 END LOOP;
2940
2941 -- This will make sure that the parent is waits until the above requests complete
2942 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => 'END') ;
2943 return;
2944
2945
2946 END IF;
2947
2948 END IF ;
2949
2950 -- Second Phase
2951 -- After all workers have completed, see if they have completed normally
2952 IF req_data = 'END'
2953 THEN
2954
2955
2956 -- assume that all concurrent dup workers completed normally, unless found otherwise
2957 l_workers_completed := TRUE;
2958
2959 -- get request ids that did not complete
2960 Select request_id BULK COLLECT into l_sub_requests
2961 from Fnd_Concurrent_Requests R
2962 Where Parent_Request_Id = FND_GLOBAL.conc_request_id
2963 and (phase_code<>'C' or status_code<>'C');
2964
2965 -- log these request_ids and set the return code of the parent concurrent program
2966 -- to 2 ie., ERROR
2967 IF l_sub_requests.count>0 THEN
2968 l_workers_completed:=FALSE;
2969 FOR I in 1..l_sub_requests.COUNT LOOP
2970 log('Index worker with request id ' || l_sub_requests(I) );
2971 log('did not complete normally');
2972 retcode := 2;
2973 END LOOP;
2974 END IF;
2975
2976 -- If any worker has not completed just return
2977 IF (l_workers_completed = false)
2978 THEN
2979 return;
2980 END IF ;
2981
2982 -- This means success
2983 log('All the Child Index workers completed successfully');
2984
2985
2986 END IF ;
2987
2988 EXCEPTION
2989 WHEN OTHERS THEN
2990 log('Parallel Sync Index Parent Program Aborted');
2991 retcode := 2;
2992 err := err || logerror || SQLERRM;
2993
2994
2995 END ;
2996
2997 -- conc program executable for Parallel Sync Index Child Concurrent Program
2998 -- This will be used by both Manual ( Batch) Synchronization and Bulk Import
2999 PROCEDURE sync_index_parallel_child (
3000 retcode OUT NOCOPY VARCHAR2,
3001 err OUT NOCOPY VARCHAR2,
3002 p_request_id IN NUMBER,
3003 p_index_name IN VARCHAR2
3004 )
3005 IS
3006 idx_name varchar2(300);
3007 owner_name VARCHAR2(30); --bug 5929615
3008 BEGIN
3009 log ( ' Starting Sync Index Parallel Child concurrent Program for index ' || p_index_name );
3010
3011 idx_name := substrb( upper(p_index_name),instrb( upper(p_index_name),'.' ) + 1 ) ;
3012 owner_name := SubStrB(Upper(p_index_name),0,instrb( upper(p_index_name),'.' )-1); --bug 5929615
3013 log ( ' Schema name stripped index is ' || idx_name );
3014 log ( ' index owner is ' || owner_name );
3015
3016
3017 retcode := 0;
3018 err := null;
3019
3020 -- we make sure that we call the sync index atleast once, regardless of the status
3021 -- of the concurrent program to be waited on ( just to make sure, if the completion happens
3022 -- so fast that we have nothing to SYNC
3023 LOOP
3024 BEGIN
3025 -- SYNC THE INDEX IF THERE IS ANY IN THE PENDING QUEUE
3026 IF is_index_pending( idx_name,owner_name) --bug 5929615
3027 THEN
3028 ad_ctx_Ddl.Sync_Index (p_index_name );
3029 END IF ;
3030
3031 -- WHEN EXCEPTION HAPPENS GET THE HELL OUT OF HERE
3032 EXCEPTION
3033 WHEN OTHERS THEN
3034 retcode := 2;
3035 err := SQLERRM;
3036 log ('Error syncing index ' || p_index_name || ' :' || SQLERRM);
3037 return ;
3038 END ;
3039
3040 /********* will incorporate this in the future
3041 -- sleep for 2 minutes
3042 -- dbms_lock.sleep( 120 );
3043
3044 ************/
3045
3046 -- EXIT CONDITION
3047 -- GET THE HELL OUT OF HERE WHEN THE CONCURRENT PROGRAM WE ARE WAITING ON IS COMPLETE
3048 EXIT WHEN is_conc_complete(p_request_id) = TRUE ;
3049
3050 END LOOP;
3051
3052 END ;
3053
3054 END HZ_DQM_SYNC;
3055
[Home] [Help]