1 PACKAGE BODY HZ_DQM_SYNC AS
2 /* $Header: ARHDQSNB.pls 120.56 2008/02/06 11:14:06 amstephe 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 SELECT r.object_id INTO l_party_id
723 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
724 WHERE oc.org_contact_id = p_org_contact_id
725 AND oc.party_relationship_id = r.relationship_id
726 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
727 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES'
728 AND subject_type ='PERSON'
729 AND DIRECTIONAL_FLAG= 'F'
730 AND (oc.status is null OR oc.status = 'A' or oc.status = 'I')
731 AND (r.status is null OR r.status = 'A' or r.status = 'I') ;
732
733 insert_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_create_upd);
734 -- REPURI. Bug 4884742. Added this to insert data into hz_dqm_sh_sync_interface table
735 -- if shadow staging conc prog completed successfully.
736 IF (is_shadow_staging_complete) THEN
737 insert_sh_interface_rec(l_party_id,p_org_contact_id,null,null,'CONTACTS',p_create_upd);
738 END IF;
739 END IF;
740 EXCEPTION
741 WHEN NO_DATA_FOUND THEN
742 NULL;
743 WHEN OTHERS THEN
744 hz_common_pub.enable_cont_source_security;
745 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
746 FND_MESSAGE.SET_TOKEN('PROC','sync_contact');
747 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
748 FND_MSG_PUB.ADD;
749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
750 END sync_contact;
751
752
753 PROCEDURE sync_contact_point (
754 p_contact_point_id IN NUMBER,
755 p_create_upd IN VARCHAR2
756 ) IS
757 l_party_id NUMBER := 0;
758 l_party_id1 NUMBER;
759 l_org_contact_id NUMBER;
760 l_party_site_id NUMBER;
761 l_pr_id NUMBER;
762 l_num_ocs NUMBER;
763 l_ot_id NUMBER;
764 l_ot_table VARCHAR2(60);
765 l_party_type VARCHAR2(60);
766 l_sql_err_message VARCHAR2(2000);
767 BEGIN
768 l_party_id := 0;
769 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
770 IF (L_REALTIME_SYNC_VALUE = 'Y') THEN
771 -- REPURI. SYNC Perf Improvements. Insert directly into staging tables
772 HZ_STAGE_MAP_TRANSFORM.sync_single_cpt_online(p_contact_point_id, p_create_upd);
773 --Check if sync went through successfully
774 IF (is_sync_success('CONTACT_POINTS',p_contact_point_id,null)) THEN
775 -- Call sync index serial concurrent program
776 call_sync_index_serial ;
777 END IF;
778 ELSIF ( L_REALTIME_SYNC_VALUE = 'N') THEN
779 SELECT owner_table_name,owner_table_id INTO l_ot_table, l_ot_id
780 FROM hz_contact_points
781 WHERE contact_point_id = p_contact_point_id;
782
783 IF l_ot_table = 'HZ_PARTY_SITES' THEN
784 SELECT p.party_id, ps.party_site_id, party_type
785 INTO l_party_id1, l_party_site_id, l_party_type
786 FROM HZ_PARTY_SITES ps, HZ_PARTIES p
787 WHERE party_site_id = l_ot_id
788 AND p.party_id = ps.party_id;
789
790 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
791 BEGIN
792 SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
793 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
794 WHERE r.party_id = l_party_id1
795 AND r.relationship_id = oc.party_relationship_id
796 AND r.directional_flag='F'
797 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
798 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
799 EXCEPTION
800 WHEN NO_DATA_FOUND THEN
801 RETURN;
802 END;
803 ELSE
804 l_party_id:=l_party_id1;
805 l_org_contact_id:=NULL;
806 END IF;
807 ELSIF l_ot_table = 'HZ_PARTIES' THEN
808 l_party_site_id := NULL;
809 SELECT party_type INTO l_party_type
810 FROM hz_parties
811 WHERE party_id = l_ot_id;
812
813 IF l_party_type <> 'PARTY_RELATIONSHIP' THEN
814 l_party_id := l_ot_id;
815 l_org_contact_id:=NULL;
816 ELSE
817 BEGIN
818 SELECT r.object_id, org_contact_id INTO l_party_id,l_org_contact_id
819 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r
820 WHERE r.party_id = l_ot_id
821 AND r.relationship_id = oc.party_relationship_id
822 AND r.directional_flag='F'
823 AND r.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
824 AND r.OBJECT_TABLE_NAME = 'HZ_PARTIES';
825 EXCEPTION
826 WHEN NO_DATA_FOUND THEN
827 RETURN;
828 END;
829 END IF;
830 END IF;
831
832 insert_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_create_upd);
833 -- REPURI. Bug 4884742. Added this to insert data into hz_dqm_sh_sync_interface table
834 -- if shadow staging conc prog completed successfully.
835 IF (is_shadow_staging_complete) THEN
836 insert_sh_interface_rec(l_party_id,p_contact_point_id,l_party_site_id, l_org_contact_id, 'CONTACT_POINTS',p_create_upd);
837 END IF;
838 END IF;
839 EXCEPTION
840 WHEN OTHERS THEN
841 hz_common_pub.enable_cont_source_security;
842 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
843 FND_MESSAGE.SET_TOKEN('PROC','sync_contact_point');
844 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
845 FND_MSG_PUB.ADD;
846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
847 END sync_contact_point;
848
849
850 PROCEDURE sync_relationship (
851 p_relationship_id IN NUMBER,
852 p_create_upd IN VARCHAR2
853 ) IS
854
855 CURSOR org_contacts IS
856 SELECT org_contact_id
857 FROM hz_org_contacts
858 WHERE party_relationship_id = p_relationship_id
859 AND status = 'A';
860
861 l_org_contact_id NUMBER;
862
863 BEGIN
864 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
865 IF ( L_REALTIME_SYNC_VALUE = 'Y' OR L_REALTIME_SYNC_VALUE = 'N')
866 THEN
867 OPEN org_contacts;
868 LOOP
869 FETCH org_contacts INTO l_org_contact_id;
870 EXIT WHEN org_contacts%NOTFOUND;
871 sync_contact(l_org_contact_id,'U');
872 END LOOP;
873 CLOSE org_contacts;
874 END IF;
875
876 EXCEPTION
877 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
878 hz_common_pub.enable_cont_source_security;
879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880 WHEN OTHERS THEN
881 hz_common_pub.enable_cont_source_security;
882 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
883 FND_MESSAGE.SET_TOKEN('PROC','sync_relationship');
884 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
885 FND_MSG_PUB.ADD;
886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
887 END;
888
889 --- VJN CHANGED THIS PROCEDURE TO INCLUDE THE CHECK OF 'I' FOR THE
890 -- party_sites CURSOR (Bug 3139325)
891 PROCEDURE sync_location (
892 p_location_id IN NUMBER,
893 p_create_upd IN VARCHAR2
894 ) IS
895
896 CURSOR party_sites IS
897 SELECT party_site_id
898 FROM hz_party_Sites
899 WHERE location_id = p_location_id
900 AND (status = 'A' or status = 'I') ;
901
902 l_party_site_id NUMBER;
903
904 BEGIN
905 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
906 IF ( L_REALTIME_SYNC_VALUE = 'Y' OR L_REALTIME_SYNC_VALUE = 'N')
907 THEN
908 OPEN party_sites;
909 LOOP
910 FETCH party_sites INTO l_party_site_id;
911 EXIT WHEN party_sites%NOTFOUND;
912 sync_party_site(l_party_site_id,'U');
913 END LOOP;
914 CLOSE party_sites;
915 END IF;
916
917 EXCEPTION
918 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
919 hz_common_pub.enable_cont_source_security;
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 WHEN OTHERS THEN
922 hz_common_pub.enable_cont_source_security;
923 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
924 FND_MESSAGE.SET_TOKEN('PROC','sync_location');
925 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
926 FND_MSG_PUB.ADD;
927 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
928 END;
929
930 PROCEDURE sync_cust_account (
931 p_cust_acct_id IN NUMBER,
932 p_create_upd IN VARCHAR2
933 ) IS
934
935 CURSOR c_cust_party IS
936 SELECT c.PARTY_ID, p.PARTY_TYPE
937 FROM HZ_CUST_ACCOUNTS c, HZ_PARTIES p
938 WHERE c.cust_account_id = p_cust_acct_id
939 AND p.party_id = c.party_id
940 AND NOT EXISTS (
941 SELECT d.PARTY_ID
942 FROM HZ_DQM_SYNC_INTERFACE d
943 WHERE d.ENTITY = 'PARTY'
944 AND d.PARTY_ID = c.PARTY_ID
945 AND d.STAGED_FLAG = 'N');
946
947 l_party_id NUMBER;
948 l_party_type VARCHAR2(200);
949
950 BEGIN
951 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
952 IF ( L_REALTIME_SYNC_VALUE = 'Y' OR L_REALTIME_SYNC_VALUE = 'N')
953 THEN
954 OPEN c_cust_party;
955 FETCH c_cust_party INTO l_party_id, l_party_type;
956 IF c_cust_party%FOUND THEN
957 IF l_party_type = 'ORGANIZATION' THEN
958 HZ_DQM_SYNC.sync_org(l_party_id,'U');
959 ELSIF l_party_type = 'PERSON' THEN
960 HZ_DQM_SYNC.sync_person(l_party_id,'U');
961 END IF;
962 END IF;
963 CLOSE c_cust_party;
964 END IF;
965 EXCEPTION
966 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
967 hz_common_pub.enable_cont_source_security;
968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
969 WHEN OTHERS THEN
970 hz_common_pub.enable_cont_source_security;
971 FND_MESSAGE.SET_NAME('AR', 'HZ_SYNC_SQL_EXCEP');
972 FND_MESSAGE.SET_TOKEN('PROC','sync_cust_account');
973 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
974 FND_MSG_PUB.ADD;
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976 END;
977
978 -- VJN added for Reporting errored records into HZ_DQM_SYNC_INTERFACE
979
980 PROCEDURE insert_error_rec (
981 p_party_id IN NUMBER,
982 p_record_id IN NUMBER,
983 p_party_site_id IN NUMBER,
984 p_org_contact_id IN NUMBER,
985 p_entity IN VARCHAR2,
986 p_operation IN VARCHAR2,
987 p_staged_flag IN VARCHAR2 DEFAULT 'E',
988 p_realtime_sync_flag IN VARCHAR2 DEFAULT 'Y',
989 p_error_data IN VARCHAR2
990 ) IS
991
992 BEGIN
993
994 INSERT INTO hz_dqm_sync_interface (
995 PARTY_ID,
996 RECORD_ID,
997 PARTY_SITE_ID,
998 ORG_CONTACT_ID,
999 ENTITY,
1000 OPERATION,
1001 STAGED_FLAG,
1002 REALTIME_SYNC_FLAG,
1003 ERROR_DATA,
1004 CREATED_BY,
1005 CREATION_DATE,
1006 LAST_UPDATE_LOGIN,
1007 LAST_UPDATE_DATE,
1008 LAST_UPDATED_BY,
1009 SYNC_INTERFACE_NUM
1010 ) VALUES (
1011 p_party_id,
1012 p_record_id,
1013 p_party_site_id,
1014 p_org_contact_id,
1015 p_entity,
1016 p_operation,
1017 p_staged_flag,
1018 p_realtime_sync_flag,
1019 p_error_data,
1020 hz_utility_pub.created_by,
1021 hz_utility_pub.creation_date,
1022 hz_utility_pub.last_update_login,
1023 hz_utility_pub.last_update_date,
1024 hz_utility_pub.user_id,
1025 HZ_DQM_SYNC_INTERFACE_S.nextval
1026 );
1027 END insert_error_rec;
1028
1029 -- REPURI. Bug 4884742. Added this procedure to insert data into hz_dqm_sh_sync_interface table.
1030 -- This is the interface table for Shadow Sync.
1031
1032 PROCEDURE insert_sh_interface_rec (
1033 p_party_id IN NUMBER,
1034 p_record_id IN NUMBER,
1035 p_party_site_id IN NUMBER,
1036 p_org_contact_id IN NUMBER,
1037 p_entity IN VARCHAR2,
1038 p_operation IN VARCHAR2,
1039 p_staged_flag IN VARCHAR2 DEFAULT 'N'
1040 ) IS
1041
1042 is_real_time VARCHAR2(1);
1043
1044 BEGIN
1045 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
1046 IF (L_REALTIME_SYNC_VALUE = 'Y') THEN
1047 is_real_time := 'Y';
1048 END IF;
1049
1050 -- REPURI. Bug 4968126.
1051 -- Using the Merge instead of Insert statement
1052 -- so that duplicate records dont get inserted.
1053
1054 MERGE INTO hz_dqm_sh_sync_interface S
1055 USING (
1056 SELECT
1057 p_entity AS entity
1058 ,p_party_id AS party_id
1059 ,p_record_id AS record_id
1060 ,p_party_site_id AS party_site_id
1061 ,p_org_contact_id AS org_contact_id
1062 FROM dual ) T
1063 ON (S.entity = T.entity AND
1064 S.party_id = T.party_id AND
1065 NVL(S.record_id,-99) = NVL(T.record_id,-99) AND
1066 NVL(S.party_site_id, -99) = NVL(T.party_site_id,-99) AND
1067 NVL(S.org_contact_id,-99) = NVL(T.org_contact_id,-99) AND
1068 S.staged_flag <> 'E')
1069 WHEN NOT MATCHED THEN
1070 INSERT (
1071 PARTY_ID,
1072 RECORD_ID,
1073 PARTY_SITE_ID,
1074 ORG_CONTACT_ID,
1075 ENTITY,
1076 OPERATION,
1077 STAGED_FLAG,
1078 REALTIME_SYNC_FLAG,
1079 CREATED_BY,
1080 CREATION_DATE,
1081 LAST_UPDATE_LOGIN,
1082 LAST_UPDATE_DATE,
1083 LAST_UPDATED_BY,
1084 SYNC_INTERFACE_NUM
1085 ) VALUES (
1086 p_party_id,
1087 p_record_id,
1088 p_party_site_id,
1089 p_org_contact_id,
1090 p_entity,
1091 p_operation,
1092 p_staged_flag,
1093 is_real_time,
1094 hz_utility_pub.created_by,
1095 hz_utility_pub.creation_date,
1096 hz_utility_pub.last_update_login,
1097 hz_utility_pub.last_update_date,
1098 hz_utility_pub.user_id,
1099 HZ_DQM_SH_SYNC_INTERFACE_S.nextval
1100 );
1101
1102 END insert_sh_interface_rec;
1103
1104
1105 PROCEDURE insert_interface_rec (
1106 p_party_id IN NUMBER,
1107 p_record_id IN NUMBER,
1108 p_party_site_id IN NUMBER,
1109 p_org_contact_id IN NUMBER,
1110 p_entity IN VARCHAR2,
1111 p_operation IN VARCHAR2,
1112 p_staged_flag IN VARCHAR2 DEFAULT 'N'
1113 ) IS
1114
1115 is_real_time VARCHAR2(1);
1116
1117 BEGIN
1118 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
1119 IF (L_REALTIME_SYNC_VALUE = 'Y') THEN
1120 is_real_time := 'Y';
1121 END IF;
1122
1123 -- REPURI. Bug 4968126.
1124 -- Using the Merge instead of Insert statement
1125 -- so that duplicate records dont get inserted.
1126
1127 MERGE INTO hz_dqm_sync_interface S
1128 USING (
1129 SELECT
1130 p_entity AS entity
1131 ,p_party_id AS party_id
1132 ,p_record_id AS record_id
1133 ,p_party_site_id AS party_site_id
1134 ,p_org_contact_id AS org_contact_id
1135 FROM dual ) T
1136 ON (S.entity = T.entity AND
1137 S.party_id = T.party_id AND
1138 NVL(S.record_id,-99) = NVL(T.record_id,-99) AND
1139 NVL(S.party_site_id, -99) = NVL(T.party_site_id,-99) AND
1140 NVL(S.org_contact_id,-99) = NVL(T.org_contact_id,-99) AND
1141 S.staged_flag <> 'E')
1142 WHEN NOT MATCHED THEN
1143 INSERT (
1144 PARTY_ID,
1145 RECORD_ID,
1146 PARTY_SITE_ID,
1147 ORG_CONTACT_ID,
1148 ENTITY,
1149 OPERATION,
1150 STAGED_FLAG,
1151 REALTIME_SYNC_FLAG,
1152 CREATED_BY,
1153 CREATION_DATE,
1154 LAST_UPDATE_LOGIN,
1155 LAST_UPDATE_DATE,
1156 LAST_UPDATED_BY,
1157 SYNC_INTERFACE_NUM
1158 ) VALUES (
1159 p_party_id,
1160 p_record_id,
1161 p_party_site_id,
1162 p_org_contact_id,
1163 p_entity,
1164 p_operation,
1165 p_staged_flag,
1166 is_real_time,
1167 hz_utility_pub.created_by,
1168 hz_utility_pub.creation_date,
1169 hz_utility_pub.last_update_login,
1170 hz_utility_pub.last_update_date,
1171 hz_utility_pub.user_id,
1172 HZ_DQM_SYNC_INTERFACE_S.nextval
1173 );
1174
1175 END insert_interface_rec;
1176
1177
1178 PROCEDURE optimize_indexes (
1179 errbuf OUT NOCOPY VARCHAR2,
1180 retcode OUT NOCOPY VARCHAR2
1181 ) IS
1182
1183 l_bool BOOLEAN;
1184 l_status VARCHAR2(255);
1185 l_index_owner VARCHAR2(255);
1186 l_tmp VARCHAR2(2000);
1187
1188 l_prof VARCHAR2(255);
1189 l_prof_val NUMBER;
1190 idx_retcode VARCHAR2(1);
1191 idx_err VARCHAR2(2000);
1192
1193 BEGIN
1194 retcode := 0;
1195
1196 outandlog('Starting Concurrent Program ''Synchronize Stage Schema''');
1197 outandlog('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
1198 outandlog('NEWLINE');
1199
1200 BEGIN
1201 l_prof := FND_PROFILE.VALUE('HZ_DQM_OPT_MAXTIME');
1202 IF upper(l_prof) = 'UNLIMITED' THEN
1203 l_prof_val := null;
1204 ELSE
1205 l_prof_val := TO_NUMBER(l_prof);
1206 END IF;
1207 EXCEPTION
1208 WHEN OTHERS THEN
1209 l_prof_val := null;
1210 END;
1211
1212 -- Initialize return status and message stack
1213 FND_MSG_PUB.initialize;
1214
1215 log('Optimizing Intermedia indexes');
1216 log('Optimizing party index .. ', TRUE);
1217
1218 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_index_owner);
1219 OPTIMIZE_INDEX(l_index_owner || '.hz_stage_parties_t1', 'FULL', l_prof_val, idx_retcode , idx_err);
1220 IF idx_retcode = 1 THEN
1221 RAISE FND_API.G_EXC_ERROR;
1222 END IF;
1223 log('Done');
1224
1225 log('Optimizing party site index .. ', TRUE);
1226 OPTIMIZE_INDEX(l_index_owner || '.hz_stage_party_sites_t1', 'FULL', l_prof_val, idx_retcode , idx_err);
1227 IF idx_retcode = 1 THEN
1228 RAISE FND_API.G_EXC_ERROR;
1229 END IF;
1230 log('Done');
1231
1232 log('Optimizing contact index .. ', TRUE);
1233 OPTIMIZE_INDEX(l_index_owner || '.hz_stage_contact_t1', 'FULL', l_prof_val, idx_retcode , idx_err);
1234 IF idx_retcode = 1 THEN
1235 RAISE FND_API.G_EXC_ERROR;
1236 END IF;
1237 log('Done');
1238
1239 log('Optimizing contact point index .. ', TRUE);
1240 OPTIMIZE_INDEX(l_index_owner || '.hz_stage_cpt_t1', 'FULL', l_prof_val, idx_retcode , idx_err);
1241 IF idx_retcode = 1 THEN
1242 RAISE FND_API.G_EXC_ERROR;
1243 END IF;
1244 log('Done');
1245
1246 outandlog('Concurrent Program Execution completed ');
1247 outandlog('End Time : '|| TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
1248
1249
1250 END;
1251
1252 /**
1253 * Procedure to write a message to the out NOCOPY file
1254 **/
1255 PROCEDURE out(
1256 message IN VARCHAR2,
1257 newline IN BOOLEAN DEFAULT TRUE) IS
1258 BEGIN
1259 IF message = 'NEWLINE' THEN
1260 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1261 ELSIF (newline) THEN
1262 FND_FILE.put_line(fnd_file.output,message);
1263 ELSE
1264 FND_FILE.put(fnd_file.output,message);
1265 END IF;
1266 END out;
1267
1268 /**
1269 * Procedure to write a message to the log file
1270 **/
1271 PROCEDURE log(
1272 message IN VARCHAR2,
1273 newline IN BOOLEAN DEFAULT TRUE
1274 ) IS
1275 BEGIN
1276 IF message = 'NEWLINE' THEN
1277 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
1278 ELSIF (newline) THEN
1279 FND_FILE.put_line(fnd_file.log,message);
1280 ELSE
1281 FND_FILE.put(fnd_file.log,message);
1282 END IF;
1283 END log;
1284
1285 /**
1286 * Procedure to write a message to the out NOCOPY and log files
1287 **/
1288 PROCEDURE outandlog(
1289 message IN VARCHAR2,
1290 newline IN BOOLEAN DEFAULT TRUE) IS
1291 BEGIN
1292 out(message, newline);
1293 log(message, newline);
1294 END outandlog;
1295
1296 /**
1297 * Function to fetch messages of the stack and log the error
1298 * Also returns the error
1299 **/
1300 FUNCTION logerror(SQLERRM VARCHAR2 DEFAULT NULL)
1301 RETURN VARCHAR2 IS
1302
1303 l_msg_data VARCHAR2(2000);
1304 BEGIN
1305 FND_MSG_PUB.Reset;
1306
1307 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
1308 l_msg_data := l_msg_data || FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE );
1309 END LOOP;
1310 IF (SQLERRM IS NOT NULL) THEN
1311 l_msg_data := l_msg_data || SQLERRM;
1312 END IF;
1313 log(l_msg_data);
1314 RETURN l_msg_data;
1315 END logerror;
1316
1317 PROCEDURE stage_party_merge(
1318 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1319 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1320 x_to_id IN OUT NOCOPY NUMBER,
1321 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1322 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1323 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1324 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
1325 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
1326 x_return_status OUT NOCOPY VARCHAR2
1327 ) IS
1328
1329 l_party_type VARCHAR2(30);
1330
1331 BEGIN
1332
1333 x_return_status := FND_API.G_RET_STS_SUCCESS;
1334
1335 SELECT party_type INTO l_party_type
1336 FROM HZ_PARTIES
1337 WHERE party_id = p_from_id;
1338
1339 IF l_party_type <> 'PARTY_RELATIONSHIP' THEN
1340 BEGIN
1341 UPDATE HZ_STAGED_PARTIES
1342 SET STATUS = 'M'
1343 WHERE party_id = p_from_id;
1344 EXCEPTION
1345 WHEN NO_DATA_FOUND THEN
1346 NULL;
1347 END;
1348 END IF;
1349
1350 EXCEPTION
1351 WHEN OTHERS THEN
1352 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1353 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1354 FND_MSG_PUB.ADD;
1355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1356 END stage_party_merge;
1357
1358 PROCEDURE stage_party_site_merge(
1359 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1360 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1361 x_to_id IN OUT NOCOPY NUMBER,
1362 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1363 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1364 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1365 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
1366 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
1367 x_return_status OUT NOCOPY VARCHAR2
1368 ) IS
1369
1370 l_party_id NUMBER;
1371 l_party_site_id NUMBER;
1372 l_party_site_search_rec HZ_PARTY_SEARCH.party_site_search_rec_type;
1373 l_party_site_stage_rec HZ_PARTY_STAGE.PARTY_SITE_STAGE_REC_TYPE;
1374
1375 BEGIN
1376 x_return_status := FND_API.G_RET_STS_SUCCESS;
1377
1378 BEGIN
1379 DELETE FROM HZ_STAGED_PARTY_SITES
1380 WHERE party_site_id = p_from_id;
1381 EXCEPTION
1382 WHEN NO_DATA_FOUND THEN
1383 NULL;
1384 END;
1385
1386 IF p_from_fk_id = p_to_fk_id THEN
1387 SELECT party_id INTO l_party_id
1388 FROM HZ_PARTY_SITES
1389 WHERE party_site_id = p_from_id;
1390
1391 l_party_site_id := p_from_id;
1392
1393 SAVEPOINT party_site_sync;
1394
1395 BEGIN
1396 sync_party_site (l_party_site_id,'C');
1397
1398 EXCEPTION
1399 WHEN OTHERS THEN
1400 ROLLBACK TO party_site_sync;
1401 sync_party_site (p_from_id, 'C');
1402 END;
1403 END IF;
1404
1405 EXCEPTION
1406 WHEN OTHERS THEN
1407 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1408 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1409 FND_MSG_PUB.ADD;
1410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1411 END stage_party_site_merge;
1412
1413 PROCEDURE stage_contact_point_merge(
1414 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1415 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1416 x_to_id IN OUT NOCOPY NUMBER,
1417 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1418 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1419 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1420 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
1421 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
1422 x_return_status OUT NOCOPY VARCHAR2
1423 ) IS
1424
1425 l_contact_point_id NUMBER;
1426 l_cpt_search_rec HZ_PARTY_SEARCH.contact_point_search_rec_type;
1427 l_contact_pt_stage_rec HZ_PARTY_STAGE.CONTACT_PT_STAGE_REC_TYPE;
1428 l_party_id NUMBER := 0;
1429 l_pr_id NUMBER;
1430 l_num_ocs NUMBER;
1431 l_ot_id NUMBER;
1432 l_ot_table VARCHAR2(60);
1433 l_party_type VARCHAR2(60);
1434
1435 CURSOR c_cp_party_site (cp_id NUMBER) IS
1436 SELECT owner_table_id
1437 FROM HZ_CONTACT_POINTS
1438 WHERE owner_table_name = 'HZ_PARTY_SITES'
1439 AND contact_point_id = cp_id;
1440
1441 CURSOR c_cp_org_contact (cp_id NUMBER) IS
1442 SELECT oc.org_contact_id
1443 FROM HZ_CONTACT_POINTS cp, HZ_RELATIONSHIPS rl, HZ_ORG_CONTACTS oc
1444 WHERE owner_table_name = 'HZ_PARTIES'
1445 AND contact_point_id = cp_id
1446 AND rl.PARTY_ID = cp.owner_table_id
1447 AND oc.party_relationship_id = rl.relationship_id
1448 AND rl.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1449 AND rl.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1450 AND rl.DIRECTIONAL_FLAG = 'F';
1451
1452 CURSOR c_ps_org_contact (ps_id NUMBER) IS
1453 SELECT oc.org_contact_id
1454 FROM HZ_PARTY_SITES ps, HZ_RELATIONSHIPS rl, HZ_ORG_CONTACTS oc
1455 WHERE ps.party_site_id = ps_id
1456 AND rl.PARTY_ID = ps.party_id
1457 AND oc.party_relationship_id = rl.relationship_id
1458 AND rl.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1459 AND rl.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1460 AND rl.DIRECTIONAL_FLAG = 'F';
1461
1462 l_cp_party_site_id NUMBER;
1463 l_cp_org_contact_id NUMBER;
1464
1465 BEGIN
1466
1467 x_return_status := FND_API.G_RET_STS_SUCCESS;
1468
1469 BEGIN
1470 DELETE FROM HZ_STAGED_CONTACT_POINTS
1471 WHERE contact_point_id = p_from_id;
1472 EXCEPTION
1473 WHEN NO_DATA_FOUND THEN
1474 NULL;
1475 END;
1476
1477 IF p_from_fk_id = p_to_fk_id THEN
1478
1479 l_contact_point_id := p_from_id;
1480 l_party_id := 0;
1481
1482 SELECT owner_table_name,owner_table_id INTO l_ot_table, l_ot_id
1483 FROM hz_contact_points
1484 WHERE contact_point_id = l_contact_point_id;
1485
1486 IF l_ot_table = 'HZ_PARTY_SITES' THEN
1487 SELECT party_id INTO l_party_id
1488 FROM HZ_PARTY_SITES
1489 WHERE party_site_id = l_ot_id;
1490
1491 ELSIF l_ot_table = 'HZ_PARTIES' THEN
1492 SELECT party_type INTO l_party_type
1493 FROM hz_parties
1494 WHERE party_id = l_ot_id;
1495
1496 IF l_party_type = 'ORGANIZATION' OR l_party_type = 'PERSON' THEN
1497 l_party_id := l_ot_id;
1498 ELSIF l_party_type = 'PARTY_RELATIONSHIP' THEN
1499 SELECT relationship_id, object_id INTO l_pr_id, l_party_id
1500 FROM hz_relationships --bug 4500011 replaced hz_party_relationships with hz_relationships
1501 WHERE party_id = l_ot_id
1502 AND subject_table_name = 'HZ_PARTIES'
1503 AND object_table_name = 'HZ_PARTIES'
1504 AND directional_flag = 'F';
1505
1506 SELECT count(1) INTO l_num_ocs
1507 FROM HZ_ORG_CONTACTS
1508 WHERE party_relationship_id = l_pr_id;
1509
1510 IF l_num_ocs = 0 THEN
1511 l_party_id := 0;
1512 END IF;
1513 END IF;
1514 END IF;
1515
1516 IF l_party_id <> 0 AND l_party_id IS NOT NULL THEN
1517 fnd_file.put_line(fnd_file.log,'pt 11');
1518 SAVEPOINT contact_point_sync;
1519 fnd_file.put_line(fnd_file.log,'pt 12');
1520 BEGIN
1521 sync_contact_point (l_contact_point_id,'C');
1522
1523
1524 EXCEPTION
1525 WHEN OTHERS THEN
1526 ROLLBACK TO contact_point_sync;
1527 sync_contact_point (p_from_id, 'C');
1528 END;
1529 END IF;
1530 END IF;
1531
1532 EXCEPTION
1533 WHEN OTHERS THEN
1534 fnd_file.put_line(fnd_file.log,'Error here1 '||SQLERRM);
1535 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1536 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1537 FND_MSG_PUB.ADD;
1538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1539 END stage_contact_point_merge;
1540
1541
1542 PROCEDURE stage_contact_merge(
1543 p_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1544 p_from_id IN NUMBER:=FND_API.G_MISS_NUM,
1545 x_to_id IN OUT NOCOPY NUMBER,
1546 p_from_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1547 p_to_fk_id IN NUMBER:=FND_API.G_MISS_NUM,
1548 p_par_entity_name IN VARCHAR2:=FND_API.G_MISS_CHAR,
1549 p_batch_id IN NUMBER:=FND_API.G_MISS_NUM,
1550 p_batch_party_id IN NUMBER:=FND_API.G_MISS_NUM,
1551 x_return_status OUT NOCOPY VARCHAR2
1552 ) IS
1553
1554 l_org_contact_id NUMBER;
1555 l_party_id NUMBER;
1556 l_contact_search_rec HZ_PARTY_SEARCH.contact_search_rec_type;
1557 l_contact_stage_rec HZ_PARTY_STAGE.CONTACT_STAGE_REC_TYPE;
1558 l_rel_party_id NUMBER;
1559 BEGIN
1560
1561 x_return_status := FND_API.G_RET_STS_SUCCESS;
1562
1563 BEGIN
1564 DELETE FROM HZ_STAGED_CONTACTS
1565 WHERE org_contact_id = p_from_id;
1566 EXCEPTION
1567 WHEN NO_DATA_FOUND THEN
1568 NULL;
1569 END;
1570
1571 IF p_from_fk_id = p_to_fk_id THEN
1572 l_org_contact_id := p_from_id;
1573
1574 SELECT pr.party_id, pr.object_id INTO l_rel_party_id, l_party_id
1575 FROM HZ_RELATIONSHIPS pr, HZ_ORG_CONTACTS oc --bug 4500011 replaced hz_party_relationships with hz_relationships
1576 WHERE oc.org_contact_id = l_org_contact_id
1577 AND pr.relationship_id = oc.party_relationship_id
1578 AND pr.subject_table_name = 'HZ_PARTIES'
1579 AND pr.object_table_name = 'HZ_PARTIES'
1580 AND pr.directional_flag = 'F';
1581
1582 SAVEPOINT contact_sync;
1583 BEGIN
1584 sync_contact(l_org_contact_id,'C');
1585
1586 UPDATE HZ_STAGED_PARTY_SITES
1587 SET party_id = l_party_id
1588 WHERE party_site_id in (
1589 SELECT party_site_id
1590 FROM HZ_PARTY_SITES
1591 WHERE party_id = l_rel_party_id
1592 AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'));
1593
1594 UPDATE HZ_STAGED_CONTACT_POINTS
1595 SET party_id = l_party_id
1596 WHERE contact_point_id in (
1597 SELECT contact_point_id
1598 FROM HZ_CONTACT_POINTS
1599 WHERE owner_table_name = 'HZ_PARTIES'
1600 AND owner_table_id = l_rel_party_id
1601 AND (nvl(STATUS,'A') = 'A' OR nvl(STATUS,'A')='I'));
1602
1603
1604 EXCEPTION
1605 WHEN OTHERS THEN
1606 ROLLBACK TO contact_sync;
1607 sync_contact(p_from_id, 'C');
1608 END;
1609 END IF;
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 FND_MESSAGE.SET_NAME('AR', 'HZ_MERGE_SQL_ERROR');
1614 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1615 FND_MSG_PUB.ADD;
1616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1617
1618 END stage_contact_merge;
1619
1620
1621 -- procedure to sync data realtime from hx_dqm_sync_interface table
1622 -- the program sets staged_flag = 'P' as an intermediate step during complete processing of rows in going on.
1623 FUNCTION realtime_sync (p_subscription_guid IN RAW,
1624 p_event IN OUT NOCOPY WF_EVENT_T) return VARCHAR2
1625 AS
1626
1627 TYPE PartyIdList IS TABLE OF NUMBER;
1628 TYPE OperationList IS TABLE OF VARCHAR2(1);
1629 TYPE EntityList IS TABLE OF VARCHAR2(30);
1630
1631 l_party_id PartyIdList;
1632 l_record_id PartyIdList;
1633 l_entity EntityList;
1634 l_operation OperationList;
1635 l_party_type VARCHAR2(30);
1636 l_sql_error_message VARCHAR2(2000);
1637 l_rowid EntityList;
1638
1639 errbuf VARCHAR2(1000);
1640 retcode VARCHAR2(10);
1641
1642 i_party boolean := false;
1643 i_party_sites boolean := false;
1644 i_contacts boolean := false;
1645 i_contact_points boolean := false;
1646 l_dqm_run VARCHAR2(1);
1647
1648
1649 BEGIN
1650
1651 select 'Y' into l_dqm_run
1652 from HZ_TRANS_FUNCTIONS_VL
1653 where STAGED_FLAG='Y'
1654 and nvl(ACTIVE_FLAG,'Y')='Y'
1655 and rownum = 1;
1656
1657 IF (l_dqm_run = 'Y') THEN
1658
1659 update HZ_DQM_SYNC_INTERFACE set STAGED_FLAG = 'P'
1660 where STAGED_FLAG = 'N' and REALTIME_SYNC_FLAG = 'Y'
1661 returning party_id, record_id, entity, operation, rowid BULK COLLECT into
1662 l_party_id, l_record_id, l_entity, l_operation, l_rowid;
1663
1664 COMMIT;
1665
1666 FOR i in 1..l_party_id.COUNT LOOP
1667 BEGIN
1668 IF (l_entity(i) = 'PARTY') THEN
1669 select party_type into l_party_type from hz_parties where party_id = l_party_id(i);
1670 hz_trans_pkg.set_party_type(l_party_type);
1671 HZ_STAGE_MAP_TRANSFORM.sync_single_party(l_party_id(i), l_party_type, l_operation(i));
1672 i_party := true;
1673 ELSIF (l_entity(i) = 'PARTY_SITES') THEN
1674 HZ_STAGE_MAP_TRANSFORM.sync_single_party_site(l_record_id(i), l_operation(i));
1675 i_party := true;
1676 i_party_sites := true;
1677 ELSIF (l_entity(i) = 'CONTACTS') THEN
1678 HZ_STAGE_MAP_TRANSFORM.sync_single_contact(l_record_id(i), l_operation(i));
1679 i_party := true;
1680 i_contacts := true;
1681 ELSIF (l_entity(i) = 'CONTACT_POINTS') THEN
1682 HZ_STAGE_MAP_TRANSFORM.sync_single_contact_point(l_record_id(i), l_operation(i));
1683 i_party := true;
1684 i_contact_points := true;
1685 END IF;
1686
1687 BEGIN
1688 IF (l_entity(i) <> 'PARTY') THEN
1689 insert_into_interface(l_party_id(i));
1690 END IF;
1691 IF l_operation(i) = 'C' THEN
1692 DELETE FROM hz_dqm_sync_interface WHERE rowid = l_rowid(i) ;
1693 ELSE
1694 UPDATE hz_dqm_sync_interface SET staged_flag = 'Y' WHERE rowid = l_rowid(i);
1695 END IF;
1696 EXCEPTION WHEN OTHERS THEN
1697 NULL;
1698 END;
1699
1700 EXCEPTION
1701 WHEN OTHERS THEN
1702 -- update staged_flag to 'E' if program generates an error.
1703 l_sql_error_message := SQLERRM;
1704 UPDATE hz_dqm_sync_interface SET error_data = l_sql_error_message, staged_flag = 'E' WHERE ROWID = l_rowid(i);
1705 END;
1706
1707 COMMIT;
1708 END LOOP ;
1709 COMMIT;
1710
1711 REALTIME_SYNC_INDEXES(i_party, i_party_sites, i_contacts, i_contact_points);
1712 RETURN 'SUCCESS';
1713 END IF;
1714 EXCEPTION
1715 when others then
1716 IF p_subscription_guid IS NOT NULL THEN
1717 WF_CORE.context('HZ_DQM_SYNC', 'REALTIME_SYNC', p_event.getEventName(), p_subscription_guid);
1718 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1719 END IF;
1720 return 'ERROR';
1721
1722 END REALTIME_SYNC;
1723
1724
1725
1726 -- procedure to sync indexes real time. Commented code will be useful later.
1727 PROCEDURE realtime_sync_indexes(i_party IN boolean,
1728 i_party_sites IN boolean,
1729 i_contacts IN boolean,
1730 i_contact_points IN boolean
1731 )
1732 AS
1733
1734 idx_retcode varchar2(1);
1735 idx_err varchar2(2000);
1736
1737 l_status VARCHAR2(255);
1738 l_index_owner VARCHAR2(255);
1739 l_tmp VARCHAR2(2000);
1740
1741 BEGIN
1742 IF(fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_index_owner)) THEN
1743 IF (i_party) THEN
1744 SYNC_INDEX_REALTIME(l_index_owner || '.hz_stage_parties_t1',
1745 idx_retcode , idx_err);
1746 IF idx_retcode = 1 THEN
1747 RAISE FND_API.G_EXC_ERROR;
1748 END IF;
1749
1750 END IF;
1751 IF (i_party_sites) THEN
1752 SYNC_INDEX_REALTIME(l_index_owner || '.hz_stage_party_sites_t1',
1753 idx_retcode , idx_err);
1754 IF idx_retcode = 1 THEN
1755 RAISE FND_API.G_EXC_ERROR;
1756 END IF;
1757
1758 END IF;
1759 IF (i_contacts) THEN
1760 SYNC_INDEX_REALTIME(l_index_owner || '.hz_stage_contact_t1',
1761 idx_retcode , idx_err);
1762 IF idx_retcode = 1 THEN
1763 RAISE FND_API.G_EXC_ERROR;
1764 END IF;
1765
1766 END IF;
1767 IF (i_contact_points) THEN
1768 SYNC_INDEX_REALTIME(l_index_owner || '.hz_stage_cpt_t1',
1769 idx_retcode , idx_err);
1770 IF idx_retcode = 1 THEN
1771 RAISE FND_API.G_EXC_ERROR;
1772 END IF;
1773 END IF;
1774 END IF;
1775 EXCEPTION
1776 WHEN FND_API.G_EXC_ERROR THEN
1777 outandlog('Error : Aborting Program');
1778 outandlog(idx_err);
1779 WHEN OTHERS THEN
1780 outandlog('Error : Aborting Program');
1781 outandlog(SQLERRM);
1782 END REALTIME_SYNC_INDEXES;
1783
1784 -- returns 'Y' if at least one row present in the global temporary table.
1785 -- This is done to reduce the number of events fired.
1786 FUNCTION check_for_transaction
1787 RETURN VARCHAR2 IS
1788
1789 bool varchar2(1) := 'N';
1790
1791 BEGIN
1792 SELECT 'Y' INTO bool
1793 FROM HZ_DQM_SYNC_GT
1794 WHERE ROWNUM = 1;
1795 IF bool <> 'Y' THEN
1796 bool := 'N';
1797 END IF;
1798 RETURN bool;
1799 EXCEPTION
1800 WHEN NO_DATA_FOUND THEN
1801 RETURN bool;
1802 END check_for_transaction;
1803
1804
1805 PROCEDURE sync_work_unit(
1806 retcode OUT NOCOPY VARCHAR2,
1807 err OUT NOCOPY VARCHAR2,
1808 p_from_rec IN VARCHAR2,
1809 p_to_rec IN VARCHAR2,
1810 p_sync_type IN VARCHAR2
1811 ) IS
1812
1813 -- REPURI - Removed all the variable declarations not being used,
1814 -- as part of code changes for Sync Performance Improvement Project.
1815
1816 l_sql_error_message VARCHAR2(2000);
1817 l_do_exec boolean := TRUE;
1818 l_sync_party_cur HZ_DQM_SYNC.SyncCurTyp;
1819 l_sync_party_site_cur HZ_DQM_SYNC.SyncCurTyp;
1820 l_sync_contact_cur HZ_DQM_SYNC.SyncCurTyp;
1821 l_sync_cpt_cur HZ_DQM_SYNC.SyncCurTyp;
1822
1823 BEGIN
1824
1825 log(' p_from_rec = '|| p_from_rec);
1826 log(' p_to_rec = '|| p_to_rec);
1827 log(' p_sync_type = ' || p_sync_type);
1828 LOOP
1829 BEGIN
1830 IF (l_do_exec = FALSE) THEN
1831 log('l_do_exec is false');
1832 EXIT;
1833 ELSE
1834 log('l_do_exec is true');
1835 l_do_exec := FALSE;
1836 END IF;
1837
1838 -- Part of DQM Sync Peformance Improvements Project (REPURI).
1839
1840 -- Instead of calling the sync_single_xxx in a LOOP for each row, we now call
1841 -- the new open_sync_xxx_cursor and pass the IN OUT CURSOR to sync_all_xxx APIs
1842 -- for all the 4 entities, to Fetch and Insert/Update data in Bulk Mode.
1843
1844 log ('-----------------------------');
1845 log ('Begin DQM SYNC');
1846 log ('-----------------------------');
1847
1848 -- Sync all organization parties
1849 -- For Create
1850 log ('-----------------------------');
1851 log ('For Create Organization Party');
1852 log ('-----------------------------');
1853 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor');
1854 HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor ('C','ORGANIZATION', p_from_rec, p_to_rec, l_sync_party_cur);
1855 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
1856 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('C', 'DQM_SYNC' ,l_sync_party_cur);
1857 -- For Update
1858 log ('-----------------------------');
1859 log ('For Update Organization Party');
1860 log ('-----------------------------');
1861 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor');
1862 HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor ('U','ORGANIZATION', p_from_rec, p_to_rec, l_sync_party_cur);
1863 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
1864 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('U', 'DQM_SYNC' , l_sync_party_cur);
1865
1866 -- Sync all person parties
1867 -- For Create
1868 log ('-----------------------');
1869 log ('For Create Person Party');
1870 log ('-----------------------');
1871 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor');
1872 HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor ('C','PERSON', p_from_rec, p_to_rec, l_sync_party_cur);
1873 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
1874 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('C', 'DQM_SYNC', l_sync_party_cur);
1875 -- For Update
1876 log ('-----------------------');
1877 log ('For Update Person Party');
1878 log ('-----------------------');
1879 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor');
1880 HZ_STAGE_MAP_TRANSFORM.open_sync_party_cursor ('U','PERSON', p_from_rec, p_to_rec, l_sync_party_cur);
1881 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
1882 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('U', 'DQM_SYNC', l_sync_party_cur);
1883
1884 -- Sync all party_sites
1885 -- For Create
1886 log ('----------------------');
1887 log ('For Create Party Sites');
1888 log ('----------------------');
1889 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_site_cursor');
1890 HZ_STAGE_MAP_TRANSFORM.open_sync_party_site_cursor ('C', p_from_rec, p_to_rec, l_sync_party_site_cur);
1891 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites');
1892 HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites ('C', 'DQM_SYNC', l_sync_party_site_cur);
1893 -- For Update
1894 log ('----------------------');
1895 log ('For Update Party Sites');
1896 log ('----------------------');
1897 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_party_site_cursor');
1898 HZ_STAGE_MAP_TRANSFORM.open_sync_party_site_cursor ('U', p_from_rec, p_to_rec, l_sync_party_site_cur);
1899 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites');
1900 HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites ('U', 'DQM_SYNC', l_sync_party_site_cur);
1901
1902 -- Sync all contacts
1903 -- For Create
1904 log ('-------------------');
1905 log ('For Create Contacts');
1906 log ('-------------------');
1907 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_contact_cursor');
1908 HZ_STAGE_MAP_TRANSFORM.open_sync_contact_cursor ('C', p_from_rec, p_to_rec, l_sync_contact_cur);
1909 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contacts');
1910 HZ_STAGE_MAP_TRANSFORM.sync_all_contacts ('C', 'DQM_SYNC', l_sync_contact_cur);
1911 -- For Update
1912 log ('-------------------');
1913 log ('For Update Contacts');
1914 log ('-------------------');
1915 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_contact_cursor');
1916 HZ_STAGE_MAP_TRANSFORM.open_sync_contact_cursor ('U', p_from_rec, p_to_rec, l_sync_contact_cur);
1917 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contacts');
1918 HZ_STAGE_MAP_TRANSFORM.sync_all_contacts ('U', 'DQM_SYNC', l_sync_contact_cur);
1919
1920 -- Sync all contact_points
1921 -- For Create
1922 log ('-------------------------');
1923 log ('For Create Contact Points');
1924 log ('-------------------------');
1925 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_cpt_cursor');
1926 HZ_STAGE_MAP_TRANSFORM.open_sync_cpt_cursor ('C', p_from_rec, p_to_rec, l_sync_cpt_cur);
1927 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points');
1928 HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points ('C', 'DQM_SYNC', l_sync_cpt_cur);
1929 -- For Update
1930 log ('-------------------------');
1931 log ('For Update Contact Points');
1932 log ('-------------------------');
1933 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_sync_cpt_cursor');
1934 HZ_STAGE_MAP_TRANSFORM.open_sync_cpt_cursor ('U', p_from_rec, p_to_rec, l_sync_cpt_cur);
1935 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points');
1936 HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points ('U', 'DQM_SYNC', l_sync_cpt_cur);
1937
1938 -- Delete from from hz_dqm_sync_interface table, all the range
1939 -- of records that are already processed
1940 DELETE FROM hz_dqm_sync_interface
1941 WHERE staged_flag = 'N'
1942 AND error_data IS NULL
1943 AND sync_interface_num BETWEEN p_from_rec AND p_to_rec;
1944
1945 EXCEPTION
1946 WHEN OTHERS THEN
1947 IF SQLCODE = -1555 THEN
1948 l_do_exec := true;
1949 log(' Snapshot too old exception raised and caught. Cursor re-executed. ');
1950 ELSE
1951 retcode := 2;
1952 err := SQLERRM;
1953 log(err);
1954 RAISE;
1955 EXIT;
1956 END IF;
1957 END;
1958
1959 END LOOP ;
1960
1961 EXCEPTION
1962 WHEN OTHERS THEN
1963 retcode := 2;
1964 err := SQLERRM;
1965 log(err);
1966
1967 END sync_work_unit;
1968
1969
1970 PROCEDURE sync_parties(retcode OUT NOCOPY VARCHAR2,
1971 err OUT NOCOPY VARCHAR2,
1972 p_num_of_workers IN VARCHAR2,
1973 p_indexes_only IN VARCHAR2
1974 ) IS
1975
1976 l_num_of_workers NUMBER;
1977 l_min_id NUMBER;
1978 l_max_id NUMBER;
1979 l_range NUMBER;
1980 l_count NUMBER;
1981 l_from_rec NUMBER;
1982 l_to_rec NUMBER;
1983 l_from_rec_v VARCHAR2(255);
1984 l_to_rec_v VARCHAR2(255);
1985 idx_name VARCHAR2(300); -- VJN Increased Size of this for P1 4096839, from 30 to 300
1986 l_index_owner VARCHAR2(255);
1987 l_status VARCHAR2(255);
1988 l_tmp VARCHAR2(2000);
1989 idx_retcode varchar2(1);
1990 idx_err varchar2(2000);
1991 TYPE nTable IS TABLE OF NUMBER index by binary_integer;
1992 l_sub_requests nTable;
1993
1994 l_range1 NUMBER;
1995 j number := 0;
1996 req_data varchar2(30);
1997 l_indexes_only VARCHAR2(30);
1998 l_workers_completed boolean ;
1999
2000
2001
2002 -- VJN Introduced
2003
2004 FIRST BOOLEAN ;
2005 l_index_conc_program_req_id NUMBER ;
2006 l_request_id NUMBER ;
2007 --Start of bug 4915282
2008 CURSOR c_non_indexed IS select 1 from hz_dqm_stage_log where operation= 'ALTER_INDEX'
2009 and start_flag = 'Y' and end_flag ='Y';
2010 l_index_count number := 0;
2011 --End of bug 4915282
2012 BEGIN
2013 log (' -------------------------------------------');
2014 log ('Entering procedure sync_parties');
2015 log ('p_num_of_workers = ' || p_num_of_workers);
2016 log('p_indexes_only = '|| p_indexes_only);
2017
2018 req_data := fnd_conc_global.request_data;
2019
2020 log(' req_data = ' || req_data);
2021
2022 l_indexes_only := nvl(p_indexes_only, 'N');
2023
2024 log (' l_indexes_only = ' || l_indexes_only);
2025
2026
2027
2028 /************* we don't have to support this ************/
2029 -- IF ( l_indexes_only = 'Y') AND (req_data IS NULL) THEN
2030 -- req_data := 'PAUSED_FOR_INDEX';
2031 -- END IF;
2032
2033 -- First Phase
2034 IF ( req_data IS NULL) THEN
2035
2036 --Start Bug:5407223---
2037 /* Delete records from HZ_DQM_SYNC_INTERFACE table if that record already exists in staged table*/
2038 log('Start Time for delete statements = ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
2039 delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='PARTY' and operation='C'
2040 and party_id in (select /*+ parallel_index(s) index_ffs(s) */ party_id from hz_staged_parties s );
2041 log (' After delete duplicate party entity records, time= '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
2042
2043 delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='PARTY_SITES' and operation='C'
2044 and record_id in (select /*+ parallel_index(s) index_ffs(s) */ party_site_id from hz_staged_party_sites s );
2045 log (' After delete duplicate party site entity records '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS') );
2046
2047 delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='CONTACTS' and operation='C'
2048 and record_id in (select /*+ parallel_index(s) index_ffs(s) */ org_contact_id from hz_staged_contacts s );
2049 log (' After delete duplicate contact entity records = '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS') );
2050
2051 delete /*+ parallel(i) */ from hz_dqm_sync_interface i where entity='CONTACT_POINTS' and operation='C'
2052 and record_id in (select /*+ parallel_index(s) index_ffs(s) */ contact_point_id from hz_staged_contact_points s );
2053 log (' After delete duplicate contact point entity records = '||TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS') );
2054 log('End Time for delete statements = ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
2055
2056 --End Bug:5407223---
2057
2058 --start bug 5460390: Add commit
2059 COMMIT;
2060 FND_CONCURRENT.AF_Commit;
2061 log('Committed Records');
2062 --end bug 5460390
2063 -- Get number of workers
2064 l_num_of_workers := to_number(nvl(p_num_of_workers, '1'));
2065
2066 -- If number of workers is 0, make it 1
2067 IF (l_num_of_workers <= 0) THEN
2068 l_num_of_workers := 1;
2069 END IF;
2070
2071 -- Get range if any possible optimization could be done on the number of workers to
2072 -- be deployed
2073 SELECT min(sync_interface_num), max(sync_interface_num) into l_min_id, l_max_id
2074 FROM hz_dqm_sync_interface
2075 WHERE staged_flag = 'N';
2076 l_range := l_max_id - l_min_id;
2077
2078 IF ( l_range <= l_num_of_workers) THEN
2079 l_num_of_workers := 1;
2080 log('Too less data to process hence reducing the number of workers. ');
2081 END IF;
2082
2083 l_from_rec := l_min_id;
2084 l_range1 := floor(l_range/l_num_of_workers);
2085
2086 log('Total number of Data Workers deployed = ' || l_num_of_workers );
2087 log ('Data Workers only create/update data');
2088
2089 --get request_id of this program
2090 l_request_id := FND_GLOBAL.conc_request_id ;
2091
2092 -- Deploy Data Workers
2093
2094 FIRST := TRUE ;
2095 FOR I in 1..l_num_of_workers LOOP
2096 j := j + 1;
2097 l_to_rec := l_from_rec + l_range1;
2098 IF (l_to_rec > l_max_id) THEN
2099 l_to_rec := l_max_id;
2100 END IF;
2101 log ( 'Calling the DQM Import Sync child program for Data Worker ' || i);
2102 log ( 'l_from_rec and l_to_rec are : ' || to_char(l_from_rec) || ' , ' || TO_CHAR(l_to_rec) );
2103
2104 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR', 'ARHDCH',
2105 'DQM Import Sync Child Program' || to_char(i),
2106 NULL,--Bug No:3941365
2107 true, to_char(l_from_rec), TO_CHAR(l_to_rec), 'BATCH');
2108
2109 -- Boolean used to track the submission of the parallel sync concurrent program
2110
2111 IF l_sub_requests(i) = 0 THEN
2112 log('Error submitting worker ' || i);
2113 log(fnd_message.get);
2114 ELSE
2115 log('Submitted request for Worker ' || TO_CHAR(I) );
2116 log('Request ID : ' || l_sub_requests(i));
2117
2118 IF FIRST
2119 THEN
2120 FIRST := FALSE ;
2121
2122 log('Calling Parallel Sync Index concurrent program');
2123 log('Request Id of the program to be waited on, that is being passed to this : ' || l_request_id );
2124 l_index_conc_program_req_id := FND_REQUEST.SUBMIT_REQUEST('AR',
2125 'ARHDQMPP',
2126 'DQM Parallel Sync Index Parent Program',
2127 NULL,
2128 FALSE,
2129 l_request_id
2130 );
2131 log('Request Id of Parallel Sync concurrent Program is : ' || l_index_conc_program_req_id );
2132 END IF ;
2133 END IF;
2134
2135 EXIT when l_sub_requests(i) = 0;
2136 l_from_rec := l_to_rec + 1;
2137 IF (l_to_rec >= l_max_id) THEN
2138 EXIT;
2139 END IF;
2140 END LOOP;
2141
2142 -- be in paused status until all the concurrent requests submitted have completed
2143 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
2144 request_data => 'END') ;
2145 err := 'Concurrent Workers submitted.';
2146 retcode := 0;
2147
2148 ELSIF( req_data = 'END') THEN
2149 -- This is for error handling, to make sure that after the
2150 -- control of this program returns here, that we catch any
2151 -- errored concurrent requests that were spawned by this program
2152 -- and if any of them errored out, we error out this program
2153 -- itself
2154 log ('checking completion status of child programs spawned by tis program' );
2155 l_workers_completed := TRUE;
2156
2157 -- program id is hard coded since any conc program submitted by this program is construed
2158 -- as its child in FND_CONCURRENT_REQUESTS, regardless of the TRUE/FALSE flag
2159 -- used in FND_CONCURRENT.SUBMIT_REQUEST
2160
2161 select request_id BULK COLLECT into l_sub_requests
2162 from fnd_concurrent_requests R
2163 where parent_request_id = l_request_id
2164 and concurrent_program_id = 46839
2165 and (phase_code<>'C' or status_code<>'C');
2166
2167 IF l_sub_requests.count > 0 THEN
2168 l_workers_completed := FALSE;
2169 FOR I in 1..l_sub_requests.COUNT LOOP
2170 outandlog('Worker with request id ' || l_sub_requests(I) );
2171 outandlog('Did not complete normally.');
2172 retcode := 2;
2173 log(' retcode = ' || retcode);
2174 RAISE FND_API.G_EXC_ERROR;
2175 END LOOP;
2176 END IF;
2177 --Start of Bug No : 4915282
2178 --Set the index transactional, if the search on non indexed records is enabled.
2179 L_REALTIME_SYNC_VALUE := nvl(FND_PROFILE.VALUE('HZ_DQM_ENABLE_REALTIME_SYNC'), 'Y');
2180 IF(L_REALTIME_SYNC_VALUE = 'Y') THEN
2181 OPEN c_non_indexed;
2182 FETCH c_non_indexed INTO l_index_count;
2183 if(c_non_indexed%FOUND) then
2184 HZ_DQM_SYNC.set_index_transactional(enabled=>'Y');
2185 end if;
2186 CLOSE c_non_indexed;
2187 END IF;
2188 --End of Bug No : 4915282
2189 END IF;
2190
2191 EXCEPTION
2192 WHEN FND_API.G_EXC_ERROR THEN
2193 log('DQM Synchronization Program Aborted');
2194 retcode := 2;
2195 err := err || logerror || SQLERRM;
2196 RAISE;
2197 WHEN OTHERS THEN
2198 log('DQM Synchronization Program Aborted');
2199 retcode := 2;
2200 err := err || logerror || SQLERRM;
2201 RAISE;
2202 END sync_parties;
2203
2204
2205 PROCEDURE sync_index_conc(
2206 retcode OUT NOCOPY VARCHAR2,
2207 err OUT NOCOPY VARCHAR2,
2208 p_index_name IN VARCHAR2 ) IS
2209 BEGIN
2210 log('Index being synched ... ' || p_index_name);
2211 sync_index( p_index_name, retcode, err);
2212 if (retcode=2) then
2213 RAISE FND_API.G_EXC_ERROR;
2214 end if;
2215 EXCEPTION
2216 WHEN OTHERS THEN
2217 retcode := 2;
2218 err := SQLERRM;
2219 RAISE FND_API.G_EXC_ERROR;--
2220 END sync_index_conc;
2221
2222 ----------------------------------------
2223 -- VJN Changes for SYNC in R12
2224 ---------------------------------------
2225
2226 -- VJN modified for R12 for Bulk Import
2227 -- This API would be called by the Bulk Import Post Processing Program, to directly insert
2228 -- data into the STAGING tables
2229
2230 -- REPURI, Modifying to procedure signature to process the records in bulk mode. Bug 4884735.
2231 PROCEDURE sync_work_unit_imp(
2232 p_batch_id IN NUMBER,
2233 p_batch_mode_flag IN VARCHAR2,
2234 p_from_osr IN VARCHAR2,
2235 p_to_osr IN VARCHAR2,
2236 p_os IN VARCHAR2,
2237 x_return_status OUT NOCOPY VARCHAR2,
2238 x_msg_count OUT NOCOPY NUMBER,
2239 x_msg_data OUT NOCOPY VARCHAR2
2240 )
2241 IS
2242
2243 l_sync_party_cur HZ_DQM_SYNC.SyncCurTyp;
2244 l_sync_party_site_cur HZ_DQM_SYNC.SyncCurTyp;
2245 l_sync_contact_cur HZ_DQM_SYNC.SyncCurTyp;
2246 l_sync_cpt_cur HZ_DQM_SYNC.SyncCurTyp;
2247
2248 BEGIN
2249 -- Part of DQM Bulk Import Sync Peformance Improvements Project (REPURI). Bug 4884735.
2250
2251 -- Instead of calling the sync_single_xxx_online in a LOOP for each row, we now call
2252 -- the new open_bulk_imp_sync_xxx_cur and pass the IN OUT CURSOR to sync_all_xxx APIs
2253 -- for all the 4 entities, to Fetch and Insert/Update data in Bulk Mode.
2254
2255 log ('-----------------------------');
2256 log ('Begin Bulk Import SYNC');
2257 log ('-----------------------------');
2258 log ('');
2259 log ('------------------------');
2260 log ('Incoming variable values');
2261 log ('------------------------');
2262 log ('p_batch_id - '|| p_batch_id);
2263 log ('p_batch_mode_flag - '||p_batch_mode_flag);
2264 log ('p_from_osr - '||p_from_osr);
2265 log ('p_to_osr - '||p_to_osr);
2266 log ('p_os - '||p_os);
2267 log ('------------------------');
2268
2269 -- Sync all organization parties
2270 -- For Create
2271 log ('-----------------------------');
2272 log ('For Create Organization Party');
2273 log ('-----------------------------');
2274
2275 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur');
2276 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur (
2277 p_batch_id => p_batch_id
2278 ,p_batch_mode_flag => p_batch_mode_flag
2279 ,p_from_osr => p_from_osr
2280 ,p_to_osr => p_to_osr
2281 ,p_os => p_os
2282 ,p_party_type => 'ORGANIZATION'
2283 ,p_operation => 'I'
2284 ,x_sync_party_cur => l_sync_party_cur
2285 );
2286
2287 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
2288 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('C', 'IMPORT_SYNC', l_sync_party_cur);
2289
2290 -- For Update
2291 log ('-----------------------------');
2292 log ('For Update Organization Party');
2293 log ('-----------------------------');
2294
2295 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur');
2296 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur (
2297 p_batch_id => p_batch_id
2298 ,p_batch_mode_flag => p_batch_mode_flag
2299 ,p_from_osr => p_from_osr
2300 ,p_to_osr => p_to_osr
2301 ,p_os => p_os
2302 ,p_party_type => 'ORGANIZATION'
2303 ,p_operation => 'U'
2304 ,x_sync_party_cur => l_sync_party_cur
2305 );
2306
2307 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
2308 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('U', 'IMPORT_SYNC', l_sync_party_cur);
2309
2310 -- Sync all person parties
2311 -- For Create
2312
2313 log ('-----------------------');
2314 log ('For Create Person Party');
2315 log ('-----------------------');
2316
2317 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur');
2318 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur (
2319 p_batch_id => p_batch_id
2320 ,p_batch_mode_flag => p_batch_mode_flag
2321 ,p_from_osr => p_from_osr
2322 ,p_to_osr => p_to_osr
2323 ,p_os => p_os
2324 ,p_party_type => 'PERSON'
2325 ,p_operation => 'I'
2326 ,x_sync_party_cur => l_sync_party_cur
2327 );
2328
2329 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
2330 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('C', 'IMPORT_SYNC', l_sync_party_cur);
2331
2332 -- For Update
2333
2334 log ('-----------------------');
2335 log ('For Update Person Party');
2336 log ('-----------------------');
2337
2338 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur');
2339 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_party_cur (
2340 p_batch_id => p_batch_id
2341 ,p_batch_mode_flag => p_batch_mode_flag
2342 ,p_from_osr => p_from_osr
2343 ,p_to_osr => p_to_osr
2344 ,p_os => p_os
2345 ,p_party_type => 'PERSON'
2346 ,p_operation => 'U'
2347 ,x_sync_party_cur => l_sync_party_cur
2348 );
2349
2350 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_parties');
2351 HZ_STAGE_MAP_TRANSFORM.sync_all_parties ('U', 'IMPORT_SYNC', l_sync_party_cur);
2352
2353 -- Sync all party_sites
2354 -- For Create
2355
2356 log ('----------------------');
2357 log ('For Create Party Sites');
2358 log ('----------------------');
2359
2360 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_psite_cur');
2361 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_psite_cur (
2362 p_batch_id => p_batch_id
2363 ,p_batch_mode_flag => p_batch_mode_flag
2364 ,p_from_osr => p_from_osr
2365 ,p_to_osr => p_to_osr
2366 ,p_os => p_os
2367 ,p_operation => 'I'
2368 ,x_sync_party_site_cur => l_sync_party_site_cur
2369 );
2370
2371 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites');
2372 HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites ('C', 'IMPORT_SYNC', l_sync_party_site_cur);
2373
2374 -- For Update
2375 log ('----------------------');
2376 log ('For Update Party Sites');
2377 log ('----------------------');
2378
2379 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_psite_cur');
2380 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_psite_cur (
2381 p_batch_id => p_batch_id
2382 ,p_batch_mode_flag => p_batch_mode_flag
2383 ,p_from_osr => p_from_osr
2384 ,p_to_osr => p_to_osr
2385 ,p_os => p_os
2386 ,p_operation => 'U'
2387 ,x_sync_party_site_cur => l_sync_party_site_cur
2388 );
2389
2390 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites');
2391 HZ_STAGE_MAP_TRANSFORM.sync_all_party_sites ('U', 'IMPORT_SYNC', l_sync_party_site_cur);
2392
2393 -- Sync all contacts
2394 -- For Create
2395 log ('-------------------');
2396 log ('For Create Contacts');
2397 log ('-------------------');
2398
2399 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_ct_cur');
2400 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_ct_cur (
2401 p_batch_id => p_batch_id
2402 ,p_batch_mode_flag => p_batch_mode_flag
2403 ,p_from_osr => p_from_osr
2404 ,p_to_osr => p_to_osr
2405 ,p_os => p_os
2406 ,p_operation => 'I'
2407 ,x_sync_contact_cur => l_sync_contact_cur
2408 );
2409
2410 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contacts');
2411 HZ_STAGE_MAP_TRANSFORM.sync_all_contacts ('C', 'IMPORT_SYNC', l_sync_contact_cur);
2412 -- For Update
2413 log ('-------------------');
2414 log ('For Update Contacts');
2415 log ('-------------------');
2416
2417 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_ct_cur');
2418 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_ct_cur (
2419 p_batch_id => p_batch_id
2420 ,p_batch_mode_flag => p_batch_mode_flag
2421 ,p_from_osr => p_from_osr
2422 ,p_to_osr => p_to_osr
2423 ,p_os => p_os
2424 ,p_operation => 'U'
2425 ,x_sync_contact_cur => l_sync_contact_cur
2426 );
2427
2428 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contacts');
2429 HZ_STAGE_MAP_TRANSFORM.sync_all_contacts ('U', 'IMPORT_SYNC', l_sync_contact_cur);
2430
2431 -- Sync all contact_points
2432 -- For Create
2433
2434 log ('-------------------------');
2435 log ('For Create Contact Points');
2436 log ('-------------------------');
2437
2438 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_cpt_cur');
2439 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_cpt_cur (
2440 p_batch_id => p_batch_id
2441 ,p_batch_mode_flag => p_batch_mode_flag
2442 ,p_from_osr => p_from_osr
2443 ,p_to_osr => p_to_osr
2444 ,p_os => p_os
2445 ,p_operation => 'I'
2446 ,x_sync_cpt_cur => l_sync_cpt_cur
2447 );
2448
2449 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points');
2450 HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points ('C', 'IMPORT_SYNC', l_sync_cpt_cur);
2451
2452 -- For Update
2453
2454 log ('-------------------------');
2455 log ('For Update Contact Points');
2456 log ('-------------------------');
2457
2458 log ('Calling HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_cpt_cur');
2459 HZ_STAGE_MAP_TRANSFORM.open_bulk_imp_sync_cpt_cur (
2460 p_batch_id => p_batch_id
2461 ,p_batch_mode_flag => p_batch_mode_flag
2462 ,p_from_osr => p_from_osr
2463 ,p_to_osr => p_to_osr
2464 ,p_os => p_os
2465 ,p_operation => 'U'
2466 ,x_sync_cpt_cur => l_sync_cpt_cur
2467 );
2468
2469 log ('Calling HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points');
2470 HZ_STAGE_MAP_TRANSFORM.sync_all_contact_points ('U', 'IMPORT_SYNC', l_sync_cpt_cur);
2471
2472 END ;
2473
2474 -- This would take a request_id , see if the corresponding conc program is complete
2475 -- and return TRUE or FALSE
2476 FUNCTION is_conc_complete ( p_request_id IN NUMBER) RETURN BOOLEAN
2477 IS
2478 req_id NUMBER ;
2479 rphase varchar2(30);
2480 rstatus varchar2(30);
2481 dphase varchar2(30);
2482 dstatus varchar2(30);
2483 message varchar2(240);
2484 status boolean ;
2485
2486 BEGIN
2487 -- set request id we want to query
2488 req_id := p_request_id ;
2489
2490 -- call FND procedure to find status of concurrent program
2491 status := FND_CONCURRENT.GET_REQUEST_STATUS(req_id, NULL, NULL, rphase,rstatus,dphase,dstatus,message);
2492
2493 -- Return true any time request is complete
2494 -- IF dphase = 'RUNNING' OR dphase = 'PENDING'
2495 IF dphase = 'COMPLETE'
2496 THEN
2497 RETURN TRUE ;
2498 ELSE
2499 RETURN FALSE ;
2500 END IF ;
2501
2502 END ;
2503
2504 -- This will return true if the passed in index has any rows to be synced
2505 -- else will return false
2506 FUNCTION is_index_pending( p_index_name IN VARCHAR2,p_owner_name IN VARCHAR2) RETURN BOOLEAN
2507 IS
2508 BEGIN
2509 --bug 5929615
2510 FOR cur in
2511 ( SELECT 'Y' FROM
2512 (
2513 select
2514 u.name pnd_index_owner,
2515 idx_name pnd_index_name,
2516 ixp_name pnd_partition_name,
2517 pnd_rowid,
2518 pnd_timestamp
2519 from ctxsys.dr$pending, ctxsys.dr$index i, ctxsys.dr$index_partition p,
2520 sys.user$ u
2521 where idx_owner# = u.user#
2522 and idx_id = ixp_idx_id
2523 and pnd_pid = ixp_id
2524 and pnd_pid <> 0
2525 and pnd_cid = idx_id
2526 UNION ALL
2527 select
2528 u.name pnd_index_owner,
2529 idx_name pnd_index_name,
2530 null pnd_partition_name,
2531 pnd_rowid,
2532 pnd_timestamp
2533 from ctxsys.dr$pending, ctxsys.dr$index i, sys.user$ u
2534 where idx_owner# = u.user#
2535 and pnd_pid = 0
2536 and pnd_cid = idx_id
2537 )
2538 WHERE PND_INDEX_NAME = p_index_name
2539 and pnd_index_owner=p_owner_name
2540 and rownum =1
2541
2542 )
2543 LOOP
2544 RETURN TRUE ;
2545 END LOOP ;
2546
2547 RETURN FALSE ;
2548 END ;
2549
2550 -- VJN Introduced for setting transactional property of Index, a new feature
2551 -- for text indexes, available as part of 10g.
2552 PROCEDURE set_index_transactional( enabled IN VARCHAR2 )
2553 IS
2554 l_bool boolean ;
2555
2556 l_status VARCHAR2(255);
2557 l_temp VARCHAR2(255);
2558 l_index_owner VARCHAR2(255);
2559 index_cnt NUMBER;
2560 BEGIN
2561 -- GET THE INDEX OWNER INSTEAD OF HARDCODING IT
2562 -- THIS FND FUNCTION, WILL TAKE THE APPLICATION SHORT NAME AND RETURN THE ORACLE_SCHEMA FOR THE USER
2563 -- AND THIS IS ESSENTIALLY THE INDEX OWNER
2564
2565 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_temp,l_index_owner);
2566
2567 -- Propagating the changes done for Bug:4706376
2568 SELECT COUNT(*) INTO index_cnt FROM HZ_DQM_STAGE_LOG WHERE OPERATION='ALTER_INDEX';
2569
2570 IF enabled = 'Y'
2571 THEN
2572 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2573 '.hz_stage_parties_t1 rebuild parameters(''replace metadata transactional'')';
2574 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2575 '.hz_stage_party_sites_t1 rebuild parameters(''replace metadata transactional'')';
2576 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2577 '.hz_stage_contact_t1 rebuild parameters(''replace metadata transactional'')';
2578 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2579 '.hz_stage_cpt_t1 rebuild parameters(''replace metadata transactional'')';
2580
2581 -- Propagating the changes done for Bug:4706376
2582 if(index_cnt=null OR index_cnt=0) then
2583 insert into hz_dqm_stage_log(OPERATION, NUMBER_OF_WORKERS,WORKER_NUMBER,STEP,START_FLAG,START_TIME,END_FLAG,
2584 END_TIME,LAST_UPDATE_DATE, CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN )
2585 values ('ALTER_INDEX',1,1,'STAGED_TABLES','Y',sysdate,'Y',null,sysdate,sysdate,
2586 fnd_global.user_id,fnd_global.user_id,fnd_global.login_id);
2587 else
2588 update hz_dqm_stage_log set start_flag='Y',end_flag='Y',start_time=sysdate,
2589 last_update_date=sysdate,last_update_login=fnd_global.login_id
2590 where operation='ALTER_INDEX';
2591 end if;
2592
2593 ELSE
2594 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2595 '.hz_stage_parties_t1 rebuild parameters(''replace metadata nontransactional'')';
2596 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2597 '.hz_stage_party_sites_t1 rebuild parameters(''replace metadata nontransactional'')';
2598 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2599 '.hz_stage_contact_t1 rebuild parameters(''replace metadata nontransactional'')';
2600 EXECUTE IMMEDIATE 'ALTER INDEX ' || l_index_owner ||
2601 '.hz_stage_cpt_t1 rebuild parameters(''replace metadata nontransactional'')';
2602
2603 -- Propagating the changes done for Bug:4706376
2604 if(index_cnt=null OR index_cnt=0) then
2605 insert into hz_dqm_stage_log(OPERATION, NUMBER_OF_WORKERS,WORKER_NUMBER,STEP,START_FLAG,START_TIME,END_FLAG,
2606 END_TIME,LAST_UPDATE_DATE, CREATION_DATE,CREATED_BY,LAST_UPDATED_BY,LAST_UPDATE_LOGIN)
2607 values ('ALTER_INDEX',1,1,'STAGED_TABLES','N',sysdate,'N',null,sysdate,sysdate,
2608 fnd_global.user_id,fnd_global.user_id,fnd_global.login_id);
2609 else
2610 update hz_dqm_stage_log set start_flag='N',end_flag='N',start_time=sysdate,
2611 last_update_date=sysdate,last_update_login=fnd_global.login_id
2612 where operation='ALTER_INDEX';
2613 end if;
2614
2615 END IF ;
2616 EXCEPTION WHEN OTHERS THEN
2617 NULL ;
2618
2619 END ;
2620
2621 -- conc program executable for Serial Sync Index Concurrent Program
2622 -- This will be used only by online (API) flows
2623 PROCEDURE sync_index_serial(
2624 retcode OUT NOCOPY VARCHAR2,
2625 err OUT NOCOPY VARCHAR2
2626 )
2627 IS
2628 idx_name VARCHAR2(300);
2629 l_index_owner VARCHAR2(255);
2630 l_status VARCHAR2(255);
2631 l_tmp VARCHAR2(2000);
2632 BEGIN
2633 retcode := 0;
2634 err := null;
2635
2636 -- Sequentially Sync all indexes one by one
2637 -- set OUT variables and write any messages to logs appropriately
2638 -- Fix for bug 5048604. Moved the call to Parties Sync Index to be the last, instead of being first.
2639 IF ( fnd_installation.GET_APP_INFO('AR', l_status, l_tmp, l_index_owner ))
2640 THEN
2641 log ( 'index_owner is ' || l_index_owner );
2642 BEGIN
2643 idx_name := l_index_owner || '.hz_stage_party_sites_t1';
2644 ad_ctx_Ddl.Sync_Index (idx_name);
2645 log ('Successful in syncing hz_stage_party_sites_t1 ');
2646 EXCEPTION
2647 WHEN OTHERS THEN
2648 retcode := 2;
2649 err := SQLERRM;
2650 log ('Error syncing hz_stage_party_sites_t1 :' || SQLERRM);
2651 END ;
2652
2653 BEGIN
2654 idx_name := l_index_owner || '.hz_stage_contact_t1';
2655 ad_ctx_Ddl.Sync_Index (idx_name);
2656 log ('Successful in syncing hz_stage_contact_t1 ');
2657 EXCEPTION
2658 WHEN OTHERS THEN
2659 retcode := 2;
2660 err := SQLERRM;
2661 log ('Error syncing hz_stage_contact_t1 :' || SQLERRM);
2662 END ;
2663
2664 BEGIN
2665 idx_name := l_index_owner || '.hz_stage_cpt_t1';
2666 ad_ctx_Ddl.Sync_Index (idx_name);
2667 log ('Successful in syncing hz_stage_cpt_t1 ');
2668 EXCEPTION
2669 WHEN OTHERS THEN
2670 retcode := 2;
2671 err := SQLERRM;
2672 log ('Error syncing hz_stage_cpt_t1 :' || SQLERRM);
2673 END ;
2674
2675 BEGIN
2676 idx_name := l_index_owner || '.hz_stage_parties_t1';
2677 ad_ctx_Ddl.Sync_Index (idx_name);
2678 log ('Successful in syncing hz_stage_parties_t1 ');
2679 EXCEPTION
2680 WHEN OTHERS THEN
2681 retcode := 2;
2682 err := SQLERRM;
2683 log ('Error syncing hz_stage_parties_t1 :' || SQLERRM);
2684 END ;
2685
2686 END IF ;
2687
2688 END ;
2689
2690 -- conc program executable for Parallel Sync Index Parent Concurrent Program
2691 -- This will be used by both Manual ( Batch) Synchronization and Bulk Import
2692
2693 PROCEDURE sync_index_parallel_parent (
2694 retcode OUT NOCOPY VARCHAR2,
2695 err OUT NOCOPY VARCHAR2,
2696 p_request_id IN NUMBER
2697 )
2698 IS
2699 req_data VARCHAR2(100);
2700 idx_name VARCHAR2(300);
2701 l_index_owner VARCHAR2(255);
2702 l_status VARCHAR2(255);
2703 l_tmp VARCHAR2(2000);
2704 idx_retcode varchar2(1);
2705 idx_err varchar2(2000);
2706 TYPE nTable IS TABLE OF NUMBER index by binary_integer;
2707 l_sub_requests nTable;
2708 l_workers_completed boolean ;
2709
2710 BEGIN
2711 -- req_data will be null the first time, by default
2712 req_data := fnd_conc_global.request_data;
2713
2714 -- First Phase
2715 -- Submit the Parallel Sync Index Child Concurrent Progarm for each one of the indexes
2716 IF (req_data IS NULL)
2717 THEN
2718 retcode := 0;
2719
2720 log('------------------------------');
2721 log('Starting DQM Parallel Sync Index Parent Program ');
2722
2723 FND_MSG_PUB.initialize;
2724
2725
2726 IF ( fnd_installation.GET_APP_INFO('AR', l_status, l_tmp, l_index_owner ))
2727 THEN
2728 log ( 'index_owner is ' || l_index_owner );
2729
2730 -- Submit requests for the Parallel Sync Index Child concurrent program
2731 -- for creating the four indexes
2732 FOR i in 1..4
2733 LOOP
2734 IF (i = 1) THEN
2735 idx_name := l_index_owner || '.hz_stage_parties_t1';
2736 ELSIF ( i = 2) THEN
2737 idx_name := l_index_owner || '.hz_stage_party_sites_t1';
2738 ELSIF ( i = 3) THEN
2739 idx_name := l_index_owner || '.hz_stage_contact_t1';
2740 ELSE
2741 idx_name :=l_index_owner || '.hz_stage_cpt_t1';
2742 END IF;
2743
2744 log('Calling the Parallel Sync Index Child program for index ' || idx_name );
2745 l_sub_requests(i) := FND_REQUEST.SUBMIT_REQUEST('AR',
2746 'ARHDQMPC',
2747 'DQM Parallel Sync Index Child Program' || to_char(i),
2748 NULL,
2749 TRUE,
2750 p_request_id,
2751 idx_name);
2752 IF l_sub_requests(i) = 0 THEN
2753 log('Error submitting index worker for ' || idx_name);
2754 log(fnd_message.get);
2755 ELSE
2756 log('Submitted request for index worker ' || idx_name );
2757 log('Request ID : ' || l_sub_requests(i));
2758 END IF;
2759 EXIT when l_sub_requests(i) = 0;
2760 END LOOP;
2761
2762 -- This will make sure that the parent is waits until the above requests complete
2763 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => 'END') ;
2764 return;
2765
2766
2767 END IF;
2768
2769 END IF ;
2770
2771 -- Second Phase
2772 -- After all workers have completed, see if they have completed normally
2773 IF req_data = 'END'
2774 THEN
2775
2776
2777 -- assume that all concurrent dup workers completed normally, unless found otherwise
2778 l_workers_completed := TRUE;
2779
2780 -- get request ids that did not complete
2781 Select request_id BULK COLLECT into l_sub_requests
2782 from Fnd_Concurrent_Requests R
2783 Where Parent_Request_Id = FND_GLOBAL.conc_request_id
2784 and (phase_code<>'C' or status_code<>'C');
2785
2786 -- log these request_ids and set the return code of the parent concurrent program
2787 -- to 2 ie., ERROR
2788 IF l_sub_requests.count>0 THEN
2789 l_workers_completed:=FALSE;
2790 FOR I in 1..l_sub_requests.COUNT LOOP
2791 log('Index worker with request id ' || l_sub_requests(I) );
2792 log('did not complete normally');
2793 retcode := 2;
2794 END LOOP;
2795 END IF;
2796
2797 -- If any worker has not completed just return
2798 IF (l_workers_completed = false)
2799 THEN
2800 return;
2801 END IF ;
2802
2803 -- This means success
2804 log('All the Child Index workers completed successfully');
2805
2806
2807 END IF ;
2808
2809 EXCEPTION
2810 WHEN OTHERS THEN
2811 log('Parallel Sync Index Parent Program Aborted');
2812 retcode := 2;
2813 err := err || logerror || SQLERRM;
2814
2815
2816 END ;
2817
2818 -- conc program executable for Parallel Sync Index Child Concurrent Program
2819 -- This will be used by both Manual ( Batch) Synchronization and Bulk Import
2820 PROCEDURE sync_index_parallel_child (
2821 retcode OUT NOCOPY VARCHAR2,
2822 err OUT NOCOPY VARCHAR2,
2823 p_request_id IN NUMBER,
2824 p_index_name IN VARCHAR2
2825 )
2826 IS
2827 idx_name varchar2(300);
2828 owner_name VARCHAR2(30); --bug 5929615
2829 BEGIN
2830 log ( ' Starting Sync Index Parallel Child concurrent Program for index ' || p_index_name );
2831
2832 idx_name := substrb( upper(p_index_name),instrb( upper(p_index_name),'.' ) + 1 ) ;
2833 owner_name := SubStrB(Upper(p_index_name),0,instrb( upper(p_index_name),'.' )-1); --bug 5929615
2834 log ( ' Schema name stripped index is ' || idx_name );
2835 log ( ' index owner is ' || owner_name );
2836
2837
2838 retcode := 0;
2839 err := null;
2840
2841 -- we make sure that we call the sync index atleast once, regardless of the status
2842 -- of the concurrent program to be waited on ( just to make sure, if the completion happens
2843 -- so fast that we have nothing to SYNC
2844 LOOP
2845 BEGIN
2846 -- SYNC THE INDEX IF THERE IS ANY IN THE PENDING QUEUE
2847 IF is_index_pending( idx_name,owner_name) --bug 5929615
2848 THEN
2849 ad_ctx_Ddl.Sync_Index (p_index_name );
2850 END IF ;
2851
2852 -- WHEN EXCEPTION HAPPENS GET THE HELL OUT OF HERE
2853 EXCEPTION
2854 WHEN OTHERS THEN
2855 retcode := 2;
2856 err := SQLERRM;
2857 log ('Error syncing index ' || p_index_name || ' :' || SQLERRM);
2858 return ;
2859 END ;
2860
2861 /********* will incorporate this in the future
2862 -- sleep for 2 minutes
2863 -- dbms_lock.sleep( 120 );
2864
2865 ************/
2866
2867 -- EXIT CONDITION
2868 -- GET THE HELL OUT OF HERE WHEN THE CONCURRENT PROGRAM WE ARE WAITING ON IS COMPLETE
2869 EXIT WHEN is_conc_complete(p_request_id) = TRUE ;
2870
2871 END LOOP;
2872
2873 END ;
2874
2875 END HZ_DQM_SYNC;
2876
[Home] [Help]