[Home] [Help]
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
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;
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
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;
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
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;
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
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;
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
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;
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
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;
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
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;
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
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;
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; */
2819: THEN
2820: log ( 'index_owner is ' || l_index_owner );
2821: BEGIN
2822: idx_name := l_index_owner || '.hz_stage_party_sites_t1';
2823: ad_ctx_Ddl.Sync_Index (idx_name);
2824: log ('Successful in syncing hz_stage_party_sites_t1 ');
2825: EXCEPTION
2826: WHEN OTHERS THEN
2827: retcode := 2;
2830: END ;
2831:
2832: BEGIN
2833: idx_name := l_index_owner || '.hz_stage_contact_t1';
2834: ad_ctx_Ddl.Sync_Index (idx_name);
2835: log ('Successful in syncing hz_stage_contact_t1 ');
2836: EXCEPTION
2837: WHEN OTHERS THEN
2838: retcode := 2;
2841: END ;
2842:
2843: BEGIN
2844: idx_name := l_index_owner || '.hz_stage_cpt_t1';
2845: ad_ctx_Ddl.Sync_Index (idx_name);
2846: log ('Successful in syncing hz_stage_cpt_t1 ');
2847: EXCEPTION
2848: WHEN OTHERS THEN
2849: retcode := 2;
2852: END ;
2853:
2854: BEGIN
2855: idx_name := l_index_owner || '.hz_stage_parties_t1';
2856: ad_ctx_Ddl.Sync_Index (idx_name);
2857: log ('Successful in syncing hz_stage_parties_t1 ');
2858: EXCEPTION
2859: WHEN OTHERS THEN
2860: retcode := 2;
3024: BEGIN
3025: -- SYNC THE INDEX IF THERE IS ANY IN THE PENDING QUEUE
3026: IF is_index_pending( idx_name,owner_name) --bug 5929615
3027: THEN
3028: ad_ctx_Ddl.Sync_Index (p_index_name );
3029: END IF ;
3030:
3031: -- WHEN EXCEPTION HAPPENS GET THE HELL OUT OF HERE
3032: EXCEPTION