1 PACKAGE BODY cz_pb_sync AS
2 /* $Header: czpbsynb.pls 120.4.12010000.3 2008/10/29 19:50:29 lamrute ship $ */
3
4 --------package variable declaration
5 TYPE t_ref IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 TYPE t_name IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
7
8 m_msg_tbl cz_pb_sync_util.message_list;
9 m_err_message VARCHAR2(2000);
10
11 SRC_SERVER_FLAG cz_servers.source_server_flag%TYPE := '1';
12
13 OBJECT_TYPE_PROJECT CONSTANT cz_model_publications.object_type%TYPE := 'PRJ';
14 OBJECT_TYPE_UITEMPL CONSTANT cz_model_publications.object_type%TYPE := 'UIT';
15 GLOBAL_UI_DEF_PUB CONSTANT NUMBER := 1;
16 GLOBAL_UI_DEF_SRC CONSTANT NUMBER := 0;
17
18 ---------------------------------------
19 ----procedure that logs err messages to m_msg_tbl
20
21 PROCEDURE error_msg_populate(p_msg VARCHAR2,
22 p_caller VARCHAR2,
23 p_code NUMBER
24 )
25 AS
26 record_count PLS_INTEGER := 0;
27 BEGIN
28 record_count := m_msg_tbl.COUNT + 1;
29 m_msg_tbl(record_count).msg_text := LTRIM(RTRIM(substr(p_msg,1,2000)));
30 m_msg_tbl(record_count).called_proc := p_caller;
31 m_msg_tbl(record_count).SQL_CODE := p_code;
32 EXCEPTION
33 WHEN OTHERS THEN
34 RAISE;
35 END error_msg_populate;
36
37 ------------------------------------------------------
38 -----verifies source server entry on the target instance
39 FUNCTION verify_src_server_entry( p_link_name cz_servers.fndnam_link_name%TYPE
40 ,p_source_server cz_servers.local_name%TYPE)
41 RETURN BOOLEAN
42 IS
43
44 gl_ref_cursor REF_CURSOR;
45 l_source_server_flag cz_servers.source_server_flag%TYPE;
46
47 BEGIN
48 OPEN gl_ref_cursor FOR 'SELECT source_server_flag
49 FROM cz_servers'||p_link_name||' t
50 WHERE UPPER(t.local_name) = UPPER(:1)' USING p_source_server;
51 LOOP
52 FETCH gl_ref_cursor INTO l_source_server_flag;
53 EXIT WHEN gl_ref_cursor%NOTFOUND;
54 END LOOP;
55 CLOSE gl_ref_cursor ;
56
57 IF ( (l_source_server_flag IS NULL)
58 OR (l_source_server_flag <> SRC_SERVER_FLAG) ) THEN
59 RETURN FALSE;
60 ELSE
61 RETURN TRUE;
62 END IF;
63 EXCEPTION
64 WHEN OTHERS THEN
65 CLOSE gl_ref_cursor;
66 RETURN FALSE;
67 END verify_src_server_entry;
68
69 ---------------------------------------------------------
70 -----procedure that verifies that the clone proc is executed on the source db
71 FUNCTION verify_source_instance(p_target_instance IN VARCHAR2)
72 RETURN BOOLEAN
73 IS
74
75 v_flag BOOLEAN := TRUE;
76 x_src_verification NUMBER := 0;
77 gl_ref_cursor REF_CURSOR;
78
79 l_hostname cz_servers.hostname%TYPE;
80 l_instance_name cz_servers.instance_name%TYPE;
81 l_src_server_flg cz_servers.source_server_flag%TYPE;
82 l_src_count NUMBER;
83
84 v_target_server_id NUMBER := 0;
85 v_link_name cz_servers.fndnam_link_name%TYPE;
86
87 CURSOR src_server_info IS SELECT hostname, instance_name, source_server_flag
88 FROM cz_servers
89 WHERE UPPER(local_name) = 'LOCAL';
90
91 BEGIN
92 --------compare instance information
93 OPEN src_server_info;
94 LOOP
95 FETCH src_server_info INTO l_hostname,l_instance_name,l_src_server_flg;
96 EXIT WHEN src_server_info%NOTFOUND;
97 END LOOP;
98 CLOSE src_server_info;
99
100 SELECT COUNT(*)
101 INTO l_src_count
102 FROM cz_servers
103 WHERE cz_servers.source_server_flag = SRC_SERVER_FLAG;
104
105 IF (l_src_count <> 0) THEN
106 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_NOT_SRC_INSTANCE', 'INSTANCE', l_instance_name);
107 error_msg_populate(m_err_message ,'CZ_PB_SYNC.VERIFYSRCINST',21061);
108 RETURN FALSE;
109 END IF;
110
111 SELECT COUNT(*)
112 INTO x_src_verification
113 FROM v$instance
114 WHERE UPPER(host_name) = UPPER(l_hostname)
115 AND UPPER(instance_name) = UPPER(l_instance_name);
116
117 IF (x_src_verification = 0) THEN
118 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_NOT_SRC_INSTANCE','SRCINSTANCE',l_instance_name);
119 error_msg_populate(m_err_message ,'CZ_PB_SYNC.VERIFYSRCINST',21061);
120 RETURN FALSE;
121 END IF;
122
123 v_target_server_id := cz_pb_sync_util.get_target_instance_id(p_target_instance);
124 v_link_name := cz_pb_sync_util.retrieve_link_name(v_target_server_id);
125 IF (NOT verify_src_server_entry(v_link_name,l_instance_name) ) THEN
126 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_NOT_TGT_INSTANCE',
127 'TGTINSTANCE', p_target_instance,
128 'SRCINSTANCE', l_instance_name);
129 error_msg_populate(m_err_message ,'CZ_PB_SYNC.VERIFYSRCENTRY',21061);
130 RETURN FALSE;
131 END IF;
132
133 RETURN v_flag;
134 EXCEPTION
135 WHEN OTHERS THEN
136 RETURN FALSE;
137 END verify_source_instance;
138
139 -------------------------------------------------------------
140 -----function that checks if the target instance is a clone
141 FUNCTION check_target_instance(p_link_name IN cz_servers.fndnam_link_name%TYPE)
142 RETURN BOOLEAN
143 IS
144
145 gl_ref_cursor REF_CURSOR;
146 l_instance_name cz_servers.instance_name%TYPE;
147 l_host_name cz_servers.hostname%TYPE;
148 v_instance_name cz_servers.instance_name%TYPE;
149 v_host_name cz_servers.hostname%TYPE;
150 v_notes VARCHAR2(2000);
151 v_return_flg BOOLEAN := FALSE;
152
153 BEGIN
154 OPEN gl_ref_cursor FOR 'SELECT instance_name,host_name
155 FROM v$instance'||p_link_name;
156 LOOP
157 FETCH gl_ref_cursor INTO l_instance_name,l_host_name;
158 EXIT WHEN gl_ref_cursor%NOTFOUND;
159 END LOOP;
160 CLOSE gl_ref_cursor;
161
162 OPEN gl_ref_cursor FOR 'SELECT instance_name,hostname,notes
163 FROM cz_servers'||p_link_name||' t
164 WHERE UPPER(local_name) = ''LOCAL'' ';
165 LOOP
166 FETCH gl_ref_cursor INTO v_instance_name,v_host_name,v_notes;
167 EXIT WHEN gl_ref_cursor%NOTFOUND;
168 END LOOP;
169 CLOSE gl_ref_cursor;
170
171 IF ( (UPPER(l_instance_name) <> UPPER(v_instance_name))
172 OR (UPPER(l_host_name) <> UPPER(v_host_name)) ) THEN
173 v_return_flg := FALSE;
174 ELSE
175 IF (v_notes IS NULL) THEN
176 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_NOT_A_CLONE', 'INSTANCENAME',v_instance_name);
177 error_msg_populate(m_err_message ,'CZ_PB_SYNC.GETLINKNAME',21011);
178 v_return_flg := TRUE;
179 ELSE
180 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_ALREADY_SYNCED','INSTANCENAME',v_instance_name, 'SYNCDATE',v_notes);
181 error_msg_populate(m_err_message ,'CZ_PB_SYNC.GETLINKNAME',21011);
182 v_return_flg := TRUE;
183 END IF;
184 END IF;
185 RETURN v_return_flg;
186 EXCEPTION
187 WHEN OTHERS THEN
188 CLOSE gl_ref_cursor;
189 RETURN TRUE;
190 END check_target_instance;
191
192 -------------------------------------------------
193 -----function that returns the server id of the cloned instance
194 FUNCTION get_tgt_server_id(p_link_name cz_servers.fndnam_link_name%TYPE)
195 RETURN NUMBER
196 IS
197
198 gl_ref_cursor REF_CURSOR;
199 v_tgt_server_id cz_servers.server_local_id%TYPE := 0;
200
201 BEGIN
202 OPEN gl_ref_cursor FOR 'SELECT distinct server_id
203 FROM cz_model_publications'||p_link_name||' t
204 WHERE t.source_target_flag = ''T''
205 AND t.deleted_flag = ''0'' ';
206 LOOP
207 FETCH gl_ref_cursor INTO v_tgt_server_id;
208 EXIT WHEN gl_ref_cursor%NOTFOUND;
209 END LOOP;
210 CLOSE gl_ref_cursor ;
211 RETURN v_tgt_server_id;
212 EXCEPTION
213 WHEN OTHERS THEN
214 CLOSE gl_ref_cursor;
215 RETURN v_tgt_server_id;
216 END get_tgt_server_id;
217
218 ---------------------------------------------------
219 ------function that verifies that the source and target server ids match
220 FUNCTION verify_src_tgt_instances(p_link_name IN cz_servers.fndnam_link_name%TYPE)
221 RETURN BOOLEAN
222 IS
223
224 v_tgt_pb_server_id cz_servers.server_local_id%TYPE;
225 v_validate_flag BOOLEAN := TRUE;
226 v_src_server_count NUMBER := 0;
227
228 NO_TGT_ID EXCEPTION;
229
230 BEGIN
231 v_src_server_count := 0;
232 v_tgt_pb_server_id := get_tgt_server_id(p_link_name);
233 IF (v_tgt_pb_server_id = 0) THEN
234 RAISE NO_TGT_ID;
235 END IF;
236
237 ----compare source and target publications
238 ----check not required
239 IF (v_tgt_pb_server_id <> 0) THEN
240 SELECT 1
241 INTO v_src_server_count
242 FROM cz_model_publications
243 WHERE cz_model_publications.server_id = v_tgt_pb_server_id
244 AND cz_model_publications.deleted_flag = '0'
245 AND ROWNUM < 2;
246 END IF;
247
248 IF (v_src_server_count <> 1) THEN
249 v_validate_flag := FALSE;
250 END IF;
251
252 RETURN v_validate_flag ;
253 EXCEPTION
254 WHEN NO_TGT_ID THEN
255 v_validate_flag := FALSE;
256 RETURN v_validate_flag ;
257 WHEN OTHERS THEN
258 v_validate_flag := FALSE;
259 RETURN v_validate_flag ;
260 END verify_src_tgt_instances;
261
262 -------------------------------------------------------
263 ------function that verifies if model ids on source and target instances are the same
264 FUNCTION verify_src_tgt_models(p_link_name IN cz_servers.fndnam_link_name%TYPE)
265 RETURN BOOLEAN
266 IS
267
268 gl_ref_cursor REF_CURSOR;
269 v_validate_model_count NUMBER := 0;
270 v_validate_flag BOOLEAN := TRUE;
271
272 BEGIN
273 ----compare model persistent ids on of source and target models
274 OPEN gl_ref_cursor FOR 'SELECT COUNT(*) FROM cz_model_publications'||p_link_name||' t
275 WHERE t.deleted_flag = ''0''
276 AND t.model_persistent_id IN (SELECT model_persistent_id
277 FROM cz_model_publications x
278 WHERE x.deleted_flag = ''0''
279 AND x.source_target_flag = ''S'')';
280 LOOP
281 FETCH gl_ref_cursor INTO v_validate_model_count;
282 EXIT WHEN gl_ref_cursor%NOTFOUND;
283 END LOOP;
284 CLOSE gl_ref_cursor;
285
286 IF (v_validate_model_count = 0) THEN
287 v_validate_flag := FALSE;
288 END IF;
289 RETURN v_validate_flag;
290 EXCEPTION
291 WHEN OTHERS THEN
292 CLOSE gl_ref_cursor;
293 v_validate_flag := FALSE;
294 RETURN v_validate_flag;
295 END verify_src_tgt_models;
296
297 ---------------------------------------------------
298 ---------function that validates publication ids on the tgt instance
299 FUNCTION verify_src_tgt_pb_ids(p_link_name IN cz_servers.fndnam_link_name%TYPE)
300 RETURN BOOLEAN
301 IS
302
303 gl_ref_cursor REF_CURSOR;
304 v_validate_flg BOOLEAN := TRUE;
305 v_rem_pb_count NUMBER := 0;
306
307 BEGIN
308
309 ----verify that the remote publication ids on the source exist on the target
310 OPEN gl_ref_cursor FOR 'SELECT COUNT(*) FROM cz_model_publications'||p_link_name||' t
311 WHERE t.deleted_flag = ''0''
312 AND t.export_status = ''OK''
313 AND t.source_target_flag = ''T''
314 AND t.publication_id IN (SELECT remote_publication_id
315 FROM cz_model_publications x
316 WHERE x.export_status = ''OK''
317 AND x.deleted_flag = ''0'')';
318 LOOP
319 FETCH gl_ref_cursor INTO v_rem_pb_count;
320 EXIT WHEN gl_ref_cursor%NOTFOUND;
321 END LOOP;
322 CLOSE gl_ref_cursor;
323
324 IF (v_rem_pb_count = 0) THEN
325 v_validate_flg := FALSE;
326 END IF;
327 RETURN v_validate_flg;
328 EXCEPTION
329 WHEN OTHERS THEN
330 CLOSE gl_ref_cursor;
331 v_validate_flg := FALSE;
332 RETURN v_validate_flg;
333 END verify_src_tgt_pb_ids;
334
335 ----------------------------------------------------
336 -----procedure that checks if the target instance has to be synchronized
337 -----x_sync_flag : TRUE if it has to be synced
338 FUNCTION has_to_be_synced(p_tgt_server_id IN cz_servers.server_local_id%TYPE)
339 RETURN BOOLEAN
340 IS
341
342 v_link_name cz_servers.fndnam_link_name%TYPE;
343 v_sync_flag BOOLEAN := TRUE;
344
345 BEGIN
346 FOR I IN 1..1
347 LOOP
348 ------get db link name for tgt instance
349 v_link_name := cz_pb_sync_util.retrieve_link_name(p_tgt_server_id);
350 IF ( NOT cz_pb_sync_util.check_db_link(v_link_name) ) THEN
351 v_sync_flag := FALSE;
352 EXIT;
353 END IF;
354
355 ------verify that server ids on the source and target are the same
356 v_sync_flag := verify_src_tgt_instances(v_link_name);
357 IF (NOT v_sync_flag) THEN
358 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_NO_TGT_ID_FOUND', 'INSTANCENAME',v_link_name);
359 error_msg_populate(m_err_message ,'CZ_PB_SYNC.NOTGTIDFOUND',21011);
360 EXIT;
361 END IF;
362
363 ----verify persistent model ids on source and target instances
364 v_sync_flag := verify_src_tgt_models(v_link_name);
365 IF (NOT v_sync_flag) THEN
366 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_MODELS_ERR');
367 error_msg_populate(m_err_message ,'CZ_PB_SYNC.SRCTGTMODELS',21009);
368 v_sync_flag := FALSE;
369 EXIT;
370 END IF;
371
372 ----verify remote publication ids
373 v_sync_flag := verify_src_tgt_pb_ids(v_link_name);
374 IF (NOT v_sync_flag) THEN
375 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_REM_PB_ID_ERR', 'TGTINSTANCE',v_link_name );
376 error_msg_populate(m_err_message ,'CZ_PB_SYNC.VERIFYSRCTGTPBIDS',21010);
377 v_sync_flag := FALSE;
378 EXIT;
379 END IF;
380 END LOOP;
381 RETURN v_sync_flag;
382 EXCEPTION
383 WHEN OTHERS THEN
384 v_sync_flag := FALSE;
385 RETURN v_sync_flag;
386 END has_to_be_synced;
387
388 ----------------------------------------------------
389 ---procedure that deletes the publication (on the source instance) record pointing to the cloned database
390 PROCEDURE clear_source_pb_record(p_publication_id IN cz_model_publications.publication_id%TYPE)
391 IS
392
393 BEGIN
394 UPDATE cz_model_publications
395 SET deleted_flag = '1'
396 WHERE publication_id = p_publication_id;
397
398 /* DELETE FROM cz_model_publications
399 WHERE publication_id = p_publication_id; */
400 EXCEPTION
401 WHEN OTHERS THEN
402 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_DEL_PB_REC_ERR', 'PUBID', p_publication_id, 'SQLERRM', SQLERRM);
403 error_msg_populate(m_err_message ,'CZ_PB_SYNC.CLRSRCPBRECORD',SQLCODE);
404 RAISE;
405 END clear_source_pb_record;
406
407 ----------------------------------------------------
408 ---procedure that deletes data from cz_pb_client_apps for a given publication
409 PROCEDURE clear_pb_clients(p_publication_id IN cz_model_publications.publication_id%TYPE)
410 IS
411
412 BEGIN
413 DELETE FROM cz_pb_client_apps
414 WHERE publication_id = p_publication_id;
415 EXCEPTION
416 WHEN OTHERS THEN
417 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_DEL_PB_CLIENT_ERR', 'PUBID',p_publication_id, 'SQLERRM', SQLERRM);
418 error_msg_populate(m_err_message ,'CZ_PB_SYNC.CLRPBCLIENTS',SQLCODE);
419 RAISE;
420 END clear_pb_clients;
421
422 -----------------------------------------------------
423 ---procedure that deletes data from cz_pb_languages for a given publication
424 PROCEDURE clear_pb_lang(p_publication_id IN cz_model_publications.publication_id%TYPE)
425 IS
426
427 BEGIN
428 DELETE FROM cz_pb_languages
429 WHERE publication_id = p_publication_id;
430 EXCEPTION
431 WHEN OTHERS THEN
432 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_DEL_LANG_ERR','PUBID',p_publication_id, 'SQLERRM', SQLERRM);
433 error_msg_populate(m_err_message ,'CZ_PB_SYNC.CLRPBCLIENTS',SQLCODE);
434 RAISE;
438 ---procedure that deletes data from cz_pb_client_apps for a given publication
435 END clear_pb_lang;
436
437 -----------------------------------------------------
439 PROCEDURE clear_pb_usages(p_publication_id IN cz_model_publications.publication_id%TYPE)
440 IS
441
442 BEGIN
443 DELETE FROM cz_publication_usages
444 WHERE publication_id = p_publication_id;
445 EXCEPTION
446 WHEN OTHERS THEN
447 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_DEL_PB_USAGE_ERR','PUBID',p_publication_id, 'SQLERRM', SQLERRM);
448 error_msg_populate(m_err_message ,'CZ_PB_SYNC.CLRPBUSAGES',SQLCODE);
449 RAISE;
450 END clear_pb_usages;
451
452 --------------------------------------------------------
453 ----procedure that truncates publication history
454 PROCEDURE clear_pb_exports
455 IS
456
457 BEGIN
458 delete from cz_pb_model_exports;
459 EXCEPTION
460 WHEN OTHERS THEN
461 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_DEL_PB_EXPORTS_ERR', 'SQLERRM', SQLERRM);
462 error_msg_populate(m_err_message ,'CZ_PB_SYNC.CLRPBEXPORTS',SQLCODE);
463 RAISE;
464 END clear_pb_exports;
465
466 ---------------------------------------------------------
467 -----procedure that updates the server_id of the target publication record
468 PROCEDURE update_tgt_server_id(p_target_server_id IN cz_servers.server_local_id%TYPE,
469 p_link_name In cz_servers.fndnam_link_name%TYPE)
470 IS
471
472 BEGIN
473 EXECUTE IMMEDIATE
474 'UPDATE cz_model_publications'||p_link_name||' t SET t.server_id = '||p_target_server_id||' WHERE t.deleted_flag = ''0'' ';
475 EXCEPTION
476 WHEN OTHERS THEN
477 RAISE;
478 END update_tgt_server_id;
479
480 ------------------------------------------------------
481 --jhanda
482 -- procedure that returns the product_id and organisation_id in the target corresponding
483 -- to the product_id and organisation_id in the source for a given source publication
484 PROCEDURE get_local_ids(p_publication_id IN cz_model_publications.publication_id%TYPE,
485 p_sync_tgt_link_name IN cz_servers.fndnam_link_name%TYPE,
486 p_import_link_name IN cz_servers.fndnam_link_name%TYPE,
487 p_item_id IN OUT NOCOPY cz_item_masters.item_id%TYPE,
488 p_org_id IN OUT NOCOPY cz_model_publications.organization_id%TYPE,
489 p_product_key IN OUT NOCOPY cz_model_publications.product_key%TYPE
490 )
491 IS
492 l_concatenated_segments MTL_SYSTEM_ITEMS_VL.concatenated_segments%TYPE;
493 l_remote_item_cursor ref_cursor;
494 c_local_item_id ref_cursor;
495 l_org_id cz_model_publications.organization_id%TYPE;
496
497 BEGIN
498
499
500 IF p_product_key IS NULL THEN
501 p_item_id:=NULL;
502 p_org_id:=NULL;
503 RETURN;
504 ELSIF instr(p_product_key, ':')=0 THEN -- product key not in BOM Format then exit
505 p_item_id:=NULL;
506 p_org_id:=NULL;
507 RETURN;
508 END IF;
509
510 l_org_id:=p_org_id;
511
512 BEGIN
513 -- Product key is propogated for Non BOM parent
514 IF p_org_id IS NULL
515 AND p_product_key IS NOT NULL THEN
516 p_org_id := SUBSTR(p_product_key, 1, instr(p_product_key, ':') -1);
517 END IF;
518
519 -- Extract item id
520
521 IF p_item_id IS NULL
522 AND p_product_key IS NOT NULL THEN
523 p_item_id:=SUBSTR(p_product_key, instr(p_product_key, ':') +1);
524 END IF;
525
526 EXCEPTION WHEN OTHERS THEN
527 -- This will happen if product key has non numeric components
528 p_item_id:=NULL;
529 p_org_id:=NULL;
530 RETURN;
531 END;
532
533
534 -- Transform Organization ID
535 BEGIN
536 EXECUTE IMMEDIATE 'SELECT organization_id FROM org_organization_definitions'||p_import_link_name||
537 ' WHERE UPPER(organization_name) = ' ||
538 ' (SELECT UPPER(organization_name) FROM org_organization_definitions' || p_sync_tgt_link_name ||
539 ' WHERE organization_id = :1)'
540 INTO p_org_id USING l_org_id;
541
542 EXCEPTION WHEN NO_DATA_FOUND THEN
543 m_err_message := 'Unable to find organization definition in import source';
544 error_msg_populate(m_err_message ,'CZ_PB_SYNC.GETLOCALIDS',SQLCODE);
545 RAISE;
546 END;
547
548 -- Transform Item ID
549
550
551 OPEN l_remote_item_cursor FOR ' SELECT concatenated_segments FROM MTL_SYSTEM_ITEMS_VL'
552 || p_sync_tgt_link_name || ' t ' || ' WHERE t.inventory_item_id = ' || p_item_id
553 || ' AND organization_id = '||l_org_id;
554 LOOP
555 FETCH l_remote_item_cursor
556 INTO l_concatenated_segments;
557 EXIT
558 WHEN l_remote_item_cursor%NOTFOUND;
559 END LOOP;
560
561 CLOSE l_remote_item_cursor;
562
563
564 -- here if p_import_link_name is NULL then we are effectively querying the local BOM data
565
566 BEGIN
567 OPEN c_local_item_id FOR ' SELECT inventory_item_id FROM MTL_SYSTEM_ITEMS_VL'
568 || p_import_link_name||' WHERE concatenated_segments = '''||l_concatenated_segments||''' AND organization_id = '||p_org_id;
569 FETCH c_local_item_id
570 INTO p_item_id;
571 CLOSE c_local_item_id;
572
573 EXCEPTION WHEN NO_DATA_FOUND THEN
574 m_err_message := 'Unable to find item definition in import source .';
578 END IF;
575 error_msg_populate(m_err_message ,'CZ_PB_SYNC.GETLOCALIDS',SQLCODE);
576 IF c_local_item_id%ISOPEN THEN
577 CLOSE c_local_item_id;
579 RAISE;
580 END;
581
582 IF p_org_id IS NOT NULL AND p_item_id IS NOT NULL THEN
583 p_product_key:=p_org_id||':'||p_item_id;
584 END IF;
585
586
587 EXCEPTION WHEN OTHERS THEN
588 IF (c_local_item_id%ISOPEN)
589 THEN
590 CLOSE c_local_item_id;
591 END IF;
592
593 IF (l_remote_item_cursor%ISOPEN)
594 THEN
595 CLOSE l_remote_item_cursor;
596 END IF;
597 RAISE;
598 END get_local_ids;
599
600
601 ------------------------------------------------------
602 ------procedure that creates a single publication record on the source for a
603 ------target publication. This proc is called from create_src_pb_records.
604 PROCEDURE create_src_publication(p_publication_id IN cz_model_publications.publication_id%TYPE,
605 p_link_name IN cz_servers.fndnam_link_name%TYPE,
606 p_tgt_server_id IN cz_servers.server_local_id%TYPE,
607 p_src_object_id IN cz_model_publications.object_id%TYPE,
608 -- p_src_object_type IN cz_model_publications.object_type%TYPE,
609 p_src_ui_def_id IN cz_model_publications.ui_def_id%TYPE)
610 IS
611 l_new_pb_id cz_model_publications.publication_id%TYPE;
612 l_ui_def_id VARCHAR2(100);
613 pub_cursor ref_cursor;
614 l_pub_cursor cz_model_publications%rowtype;
615 v_item_id cz_item_masters.item_id%TYPE;
616 v_org_id cz_model_publications.organization_id%TYPE;
617 v_product_key cz_model_publications.product_key%TYPE;
618
619 linkName cz_servers.fndnam_link_name%TYPE;
620
621 BEGIN
622 ----get new publication id
623 SELECT cz_model_publications_s.nextval
624 INTO l_new_pb_id
625 FROM dual;
626
627 IF (p_src_ui_def_id IS NULL) THEN
628 l_ui_def_id := 'NULL';
629 ELSE
630 l_ui_def_id := to_char(p_src_ui_def_id);
631 END IF;
632
633 -- Get Import Server link name
634 SELECT fndnam_link_name INTO linkName
635 FROM cz_servers
636 WHERE import_enabled='1';
637
638
639 IF(linkName IS NOT NULL)THEN
640 linkName := '@' || linkName;
641 END IF;
642
643 -- model_id, source_model_id, source_ui_def_id are null
644 OPEN pub_cursor for
645 ' SELECT *'||
646 ' FROM cz_model_publications'||p_link_name||' t ' ||
647 ' WHERE t.publication_id = '||p_publication_id ;
648
649 LOOP
650 FETCH pub_cursor
651 INTO l_pub_cursor;
652 EXIT WHEN pub_cursor % NOTFOUND;
653 v_item_id:=l_pub_cursor.top_item_id;
654 v_org_id:=l_pub_cursor.organization_id;
655 v_product_key:=l_pub_cursor.product_key;
656 IF p_link_name <> linkName THEN
657 get_local_ids(l_pub_cursor.PUBLICATION_ID,p_link_name,linkName ,v_item_id,v_org_id,v_product_key);
658 END IF;
659
660 INSERT INTO cz_model_publications(
661 PUBLICATION_ID
662 ,OBJECT_ID
663 ,OBJECT_TYPE
664 ,SERVER_ID
665 ,ORGANIZATION_ID
666 ,TOP_ITEM_ID
667 ,PRODUCT_KEY
668 ,PUBLICATION_MODE
669 ,UI_DEF_ID
670 ,UI_STYLE
671 ,APPLICABLE_FROM
672 ,APPLICABLE_UNTIL
673 ,EXPORT_STATUS
674 ,DELETED_FLAG
675 ,MODEL_LAST_STRUCT_UPDATE
676 ,MODEL_LAST_LOGIC_UPDATE
677 ,MODEL_LAST_UPDATED
678 ,CREATED_BY
679 ,CREATION_DATE
680 ,LAST_UPDATED_BY
681 ,LAST_UPDATE_DATE
682 ,USER_STR01
683 ,USER_STR02
684 ,USER_STR03
685 ,USER_STR04
686 ,USER_NUM01
687 ,USER_NUM02
688 ,USER_NUM03
689 ,USER_NUM04
690 ,MODEL_PERSISTENT_ID
691 ,SOURCE_TARGET_FLAG
692 ,REMOTE_PUBLICATION_ID
693 ,PAGE_LAYOUT
694 ,CONTAINER
695 ,DISABLED_FLAG
696 ,PUBLISHED
697 ) VALUES
698 (
699 l_new_pb_id
700 ,p_src_object_id
701 ,l_pub_cursor.OBJECT_TYPE
702 ,p_tgt_server_id
703 ,DECODE(l_pub_cursor.ORGANIZATION_ID,NULL,NULL, v_org_id)
704 ,DECODE(l_pub_cursor.TOP_ITEM_ID ,NULL, NULL, v_item_id)
705 ,DECODE(l_pub_cursor.PRODUCT_KEY , NULL,NULL, v_product_key )
706 ,l_pub_cursor.PUBLICATION_MODE
707 ,l_ui_def_id
708 ,l_pub_cursor.UI_STYLE
709 ,l_pub_cursor.APPLICABLE_FROM
710 ,l_pub_cursor.APPLICABLE_UNTIL
711 ,l_pub_cursor.EXPORT_STATUS
712 ,l_pub_cursor.DELETED_FLAG
713 ,l_pub_cursor.MODEL_LAST_STRUCT_UPDATE
714 ,l_pub_cursor.MODEL_LAST_LOGIC_UPDATE
715 ,l_pub_cursor.MODEL_LAST_UPDATED
716 ,l_pub_cursor.CREATED_BY
717 ,l_pub_cursor.CREATION_DATE
718 ,l_pub_cursor.LAST_UPDATED_BY
719 ,l_pub_cursor.LAST_UPDATE_DATE
720 ,l_pub_cursor.USER_STR01
721 ,l_pub_cursor.USER_STR02
722 ,l_pub_cursor.USER_STR03
723 ,l_pub_cursor.USER_STR04
724 ,l_pub_cursor.USER_NUM01
725 ,l_pub_cursor.USER_NUM02
726 ,l_pub_cursor.USER_NUM03
727 ,l_pub_cursor.USER_NUM04
728 ,l_pub_cursor.MODEL_PERSISTENT_ID
729 ,'S'
733 ,l_pub_cursor.DISABLED_FLAG
730 ,p_publication_id
731 ,l_pub_cursor.PAGE_LAYOUT
732 ,l_pub_cursor.CONTAINER
734 ,l_pub_cursor.PUBLISHED
735 );
736 END LOOP;
737 CLOSE pub_cursor;
738
739 ----update remote publication id on the clone instance
740 EXECUTE IMMEDIATE
741 'UPDATE cz_model_publications'||p_link_name||' t ' ||
742 ' SET t.remote_publication_id = '||l_new_pb_id||
743 ' WHERE t.publication_id = '||p_publication_id ;
744
745 ----insert into cz_pb_client_apps
746 EXECUTE IMMEDIATE
747 'INSERT INTO cz_pb_client_apps(publication_id,fnd_application_id,application_short_name,notes)' ||
748 ' SELECT '||l_new_pb_id||',' ||
749 ' s.application_id,' ||
750 ' x.application_short_name,' ||
751 ' x.notes' ||
752 ' FROM cz_pb_client_apps'||p_link_name||' x, fnd_applications s' ||
753 ' WHERE x.publication_id = '||p_publication_id ||
754 ' AND x.application_short_name = s.application_short_name' ;
755
756 ----insert into cz_publication_usages
757 EXECUTE IMMEDIATE
758 'INSERT INTO cz_publication_usages(publication_id,usage_id)' ||
759 ' SELECT '||l_new_pb_id||',usage_id' ||
760 ' FROM cz_publication_usages'||p_link_name||' z' ||
761 ' WHERE z.publication_id = '||p_publication_id ;
762
763 ----insert into cz_pb_languages
764 EXECUTE IMMEDIATE
765 'INSERT INTO cz_pb_languages(publication_id,language)' ||
766 ' SELECT '||l_new_pb_id||', language' ||
767 ' FROM cz_pb_languages'||p_link_name||' y' ||
768 ' WHERE y.publication_id = '||p_publication_id;
769
770 m_err_message := 'source pb: '||l_new_pb_id||' created for target pb: '||p_publication_id;
771 error_msg_populate(m_err_message ,'CZ_PB_MGR.CREATESRCPBRECORDS',SQLCODE);
772 EXCEPTION
773 WHEN OTHERS THEN
774 IF pub_cursor%ISOPEN THEN
775 CLOSE pub_cursor;
776 END IF;
777 ROLLBACK;
778 RAISE;
779 END create_src_publication;
780
781 --------------------------------------------------------
782 ----procedure that deletes a publication record on the target
783 ----instance
784 PROCEDURE delete_tgt_publication(p_publication_id IN cz_model_publications.publication_id%TYPE,
785 p_link_name IN cz_servers.fndnam_link_name%TYPE)
786 IS
787
788 BEGIN
789 EXECUTE IMMEDIATE
790 'delete from cz_model_publications'||p_link_name||' t' ||
791 ' where t.publication_id = '||p_publication_id ;
792 EXCEPTION
793 WHEN OTHERS THEN
794 RAISE;
795 END;
796
797 ---------------------------------------------------------
798 ------procedure that retrieves the src and tgt information
799 PROCEDURE get_src_tgt_info(p_tgt_server_id IN cz_servers.server_local_id%TYPE,
800 x_src_instance OUT NOCOPY cz_servers.local_name%TYPE,
801 x_tgt_instance OUT NOCOPY cz_servers.local_name%TYPE)
802 IS
803
804 BEGIN
805 SELECT instance_name
806 INTO x_src_instance
807 FROM cz_servers
808 WHERE cz_servers.server_local_id = 0;
809
810 SELECT instance_name
811 INTO x_tgt_instance
812 FROM cz_servers
813 WHERE cz_servers.server_local_id = p_tgt_server_id ;
814 EXCEPTION
815 WHEN OTHERS THEN
816 RAISE;
817 END;
818 -------------------------------------------------------
819 PROCEDURE clear_publication_data(p_target_server_id IN cz_model_publications.server_id%TYPE)
820 IS
821
822 l_src_pub_tbl t_ref;
823
824 BEGIN
825 SELECT publication_id
826 BULK
827 COLLECT
828 INTO l_src_pub_tbl
829 FROM cz_model_publications
830 WHERE server_id = p_target_server_id ;
831
832 IF (l_src_pub_tbl.COUNT > 0) THEN
833 FOR I IN l_src_pub_tbl.FIRST..l_src_pub_tbl.LAST
834 LOOP
835 ----clear source pb record of the tgt clone
836 clear_source_pb_record(l_src_pub_tbl(i));
837
838 ---clear pb clients
839 clear_pb_clients(l_src_pub_tbl(i));
840
841 ----clear pb usages
842 clear_pb_usages(l_src_pub_tbl(i));
843
844 ----clear pb lang
845 clear_pb_lang(l_src_pub_tbl(i));
846 END LOOP;
847 END IF;
848 EXCEPTION
849 WHEN NO_DATA_FOUND THEN
850 ----do nothing
851 NULL;
852 WHEN OTHERS THEN
853 RAISE;
854 END;
855
856 ----------------------------------------------------------
857 ----procedure that creates the source publication records for each valid publication
858 ----on the clone
859 PROCEDURE create_src_pb_records(p_tgt_server_id IN cz_servers.server_local_id%TYPE)
860 IS
861
862 TYPE object_type_tbl_type IS TABLE OF cz_model_publications.object_type%TYPE
863 INDEX BY BINARY_INTEGER;
864
865 l_ref_cursor REF_CURSOR;
866 l_model_cursor REF_CURSOR;
867 l_templ_cursor REF_CURSOR;
868 l_ui_cursor REF_CURSOR;
869
870 l_publication_id cz_model_publications.publication_id%TYPE;
871 l_src_pb_id cz_model_publications.publication_id%TYPE;
872 l_rem_pb_id cz_model_publications.publication_id%TYPE;
873 l_src_object_id cz_model_publications.object_id%TYPE;
874 l_tgt_object_id cz_model_publications.object_id%TYPE;
878 l_src_ui_def_id cz_model_publications.ui_def_id%TYPE;
875 l_src_object_type cz_model_publications.object_type%TYPE;
876 l_tgt_object_type cz_model_publications.object_type%TYPE;
877 l_src_model_id cz_model_publications.model_id%TYPE;
879 l_src_object_name cz_devl_projects.name%TYPE;
880 l_tgt_object_name cz_devl_projects.name%TYPE;
881
882 l_link_name cz_servers.fndnam_link_name%TYPE;
883
884 l_tgt_src_pub_tbl t_ref;
885 l_tgt_rem_pub_tbl t_ref;
886 l_tgt_object_id_tbl t_ref;
887 l_tgt_object_type_tbl object_type_tbl_type;
888 l_tgt_src_model_tbl t_ref;
889 l_tgt_src_ui_def_tbl t_ref;
890 l_tgt_object_name_tbl t_name;
891 l_tgt_pb_count NUMBER;
892
893 l_src_instance cz_servers.local_name%TYPE;
894 l_tgt_instance cz_servers.local_name%TYPE;
895 l_tgt_ui_def_id cz_ui_defs.ui_def_id%TYPE;
896
897 OBJ_TYPE_MISMATCH_ERR EXCEPTION;
898 MODEL_MISMATCH_ERR EXCEPTION;
899 UI_MISMATCH_ERR EXCEPTION;
900 MODELNAME_MISMATCH_ERR EXCEPTION;
901 TEMPLNAME_MISMATCH_ERR EXCEPTION;
902 OBJ_TYPE_ERR EXCEPTION;
903
904 BEGIN
905 clear_publication_data(p_tgt_server_id);
906 l_link_name := cz_pb_sync_util.retrieve_link_name(p_tgt_server_id);
907
908 l_tgt_src_pub_tbl.DELETE;
909 l_tgt_rem_pub_tbl.DELETE;
910 l_tgt_object_id_tbl.DELETE;
911 l_tgt_object_type_tbl.DELETE;
912 l_tgt_src_model_tbl.DELETE;
913 l_tgt_src_ui_def_tbl.DELETE;
914 l_tgt_object_name_tbl.DELETE;
915
916 l_tgt_pb_count := 1;
917 OPEN l_ref_cursor FOR
918 'SELECT publication_id,
919 remote_publication_id,
920 object_id,
921 object_type,
922 source_model_id,
923 source_ui_def_id
924 FROM cz_model_publications'||l_link_name||' t
925 WHERE t.deleted_flag = ''0''
926 AND t.source_target_flag = ''T''
927 AND t.export_status = ''OK'' ';
928 LOOP
929 FETCH l_ref_cursor INTO l_publication_id,
930 l_rem_pb_id,
931 l_tgt_object_id,
932 l_tgt_object_type,
933 l_src_model_id,
934 l_src_ui_def_id;
935 EXIT WHEN l_ref_cursor%NOTFOUND;
936 l_tgt_src_pub_tbl(l_tgt_pb_count) := l_publication_id;
937 l_tgt_rem_pub_tbl(l_tgt_pb_count) := l_rem_pb_id;
938 l_tgt_object_id_tbl(l_tgt_pb_count) := l_tgt_object_id;
939 l_tgt_object_type_tbl(l_tgt_pb_count) := l_tgt_object_type;
940 l_tgt_src_model_tbl(l_tgt_pb_count) := nvl(l_src_model_id,0);
941 l_tgt_src_ui_def_tbl(l_tgt_pb_count) := nvl(l_src_ui_def_id,0);
942 l_tgt_pb_count := l_tgt_pb_count + 1;
943 END LOOP;
944 CLOSE l_ref_cursor;
945
946 IF (l_tgt_object_id_tbl.COUNT > 0) THEN
947 FOR i IN l_tgt_object_id_tbl.FIRST..l_tgt_object_id_tbl.LAST
948 LOOP
949 l_tgt_object_id := l_tgt_object_id_tbl(i);
950 IF l_tgt_object_type_tbl(i) = OBJECT_TYPE_PROJECT THEN
951 OPEN l_model_cursor FOR
952 'SELECT name
953 FROM cz_devl_projects'||l_link_name||' t
954 WHERE t.devl_project_id = '||l_tgt_object_id||'
955 AND t.deleted_flag = ''0'' ';
956 LOOP
957 FETCH l_model_cursor INTO l_tgt_object_name;
958 EXIT WHEN l_model_cursor%NOTFOUND;
959 l_tgt_object_name_tbl(i) := l_tgt_object_name;
960 END LOOP;
961 CLOSE l_model_cursor;
962 ELSE
963 OPEN l_templ_cursor FOR
964 'SELECT template_name
965 FROM cz_ui_templates'||l_link_name||' t
966 WHERE t.template_id = '||l_tgt_object_id||'
967 AND t.ui_def_id = '||GLOBAL_UI_DEF_PUB||'
968 AND t.deleted_flag = ''0'' ';
969 LOOP
970 FETCH l_templ_cursor INTO l_tgt_object_name;
971 EXIT WHEN l_templ_cursor%NOTFOUND;
972 l_tgt_object_name_tbl(i) := l_tgt_object_name;
973 END LOOP;
974 CLOSE l_templ_cursor;
975 END IF;
976 END LOOP;
977 END IF;
978
979 IF (l_tgt_rem_pub_tbl.COUNT > 0) THEN
980 FOR I IN 1..l_tgt_rem_pub_tbl.COUNT
981 LOOP
982 l_src_pb_id := 0;
983 l_src_object_id := 0;
984 l_src_ui_def_id := 0;
985 BEGIN
986 SELECT publication_id,object_id,object_type,ui_def_id
987 INTO l_src_pb_id,l_src_object_id,l_src_object_type,l_src_ui_def_id
988 FROM cz_model_publications
989 WHERE cz_model_publications.publication_id = l_tgt_rem_pub_tbl(i);
990 EXCEPTION
991 WHEN NO_DATA_FOUND THEN
992 ----delete tgt_publication
993 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_DEL_TGT_PB',
994 'TGTPBID',l_tgt_rem_pub_tbl(i));
995 error_msg_populate(m_err_message ,'CZ_PB_MGR.DELTGTPBID',SQLCODE);
996 delete_tgt_publication(l_tgt_src_pub_tbl(i),l_link_name);
997 WHEN OTHERS THEN
998 RAISE;
999 END;
1000
1001 IF (l_src_object_id > 0) THEN
1002 IF UPPER(l_src_object_type) <> UPPER(l_tgt_object_type_tbl(i)) THEN
1006 END IF;
1003 l_publication_id := l_tgt_src_pub_tbl(i);
1004 l_tgt_object_type := l_tgt_object_type_tbl(i);
1005 RAISE OBJ_TYPE_MISMATCH_ERR;
1007
1008 IF l_src_object_type = OBJECT_TYPE_PROJECT THEN
1009 -----match models on the source and the target
1010 IF (l_tgt_src_model_tbl(i) <> 0) THEN
1011 IF (l_src_object_id <> l_tgt_src_model_tbl(i)) THEN
1012 l_tgt_object_id := l_tgt_src_model_tbl(i);
1013 RAISE MODEL_MISMATCH_ERR;
1014 END IF;
1015 END IF;
1016
1017 IF (l_tgt_src_ui_def_tbl(i) <> 0) THEN
1018 IF ( (l_src_ui_def_id IS NOT NULL) AND
1019 (l_src_ui_def_id <> l_tgt_src_ui_def_tbl(i)) ) THEN
1020 l_tgt_ui_def_id := l_tgt_src_ui_def_tbl(i);
1021 RAISE UI_MISMATCH_ERR;
1022 END IF;
1023 END IF;
1024
1025 SELECT name INTO l_src_object_name
1026 FROM cz_devl_projects
1027 WHERE cz_devl_projects.devl_project_id = l_src_object_id;
1028
1029 IF (l_src_object_name <> l_tgt_object_name_tbl(i)) THEN
1030 l_tgt_object_name := l_tgt_object_name_tbl(i);
1031 RAISE MODELNAME_MISMATCH_ERR;
1032 END IF;
1033 ELSIF l_src_object_type = OBJECT_TYPE_UITEMPL THEN
1034 SELECT template_name INTO l_src_object_name
1035 FROM cz_ui_templates
1036 WHERE template_id = l_src_object_id AND ui_def_id = GLOBAL_UI_DEF_SRC;
1037
1038 IF l_src_object_name <> l_tgt_object_name_tbl(i) THEN
1039 l_tgt_object_name := l_tgt_object_name_tbl(i);
1040 RAISE TEMPLNAME_MISMATCH_ERR;
1041 END IF;
1042 ELSE
1043 RAISE OBJ_TYPE_ERR;
1044 END IF;
1045
1046 ----publication data on the source is created for each publication id
1047 ----if error occurs during an insert, the whole sync is terminated
1048 create_src_publication(l_tgt_src_pub_tbl(i),
1049 l_link_name,
1050 p_tgt_server_id,
1051 l_src_object_id,
1052 -- l_src_object_type,
1053 l_src_ui_def_id);
1054 END IF;
1055 END LOOP;
1056 END IF;
1057 EXCEPTION
1058 WHEN MODEL_MISMATCH_ERR THEN
1059 ----get source and target info
1060 get_src_tgt_info(p_tgt_server_id, l_src_instance,l_tgt_instance);
1061 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_MODELS_ERR',
1062 'SRCMODEL',l_src_object_id,
1063 'SRCINSTANCE',l_src_instance,
1064 'TGTMODEL',l_tgt_object_id,
1065 'TGTINSTANCE',l_tgt_instance);
1066 error_msg_populate(m_err_message ,'CZ_PB_MGR.CREATESRCPBRECORDS',SQLCODE);
1067 IF (l_ref_cursor%ISOPEN) THEN
1068 CLOSE l_ref_cursor;
1069 END IF;
1070
1071 IF (l_model_cursor%ISOPEN) THEN
1072 CLOSE l_model_cursor;
1073 END IF;
1074 IF (l_templ_cursor%ISOPEN) THEN
1075 CLOSE l_templ_cursor;
1076 END IF;
1077 RAISE;
1078 WHEN UI_MISMATCH_ERR THEN
1079 ----get source and target info
1080 get_src_tgt_info(p_tgt_server_id, l_src_instance,l_tgt_instance);
1081 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_UIS_ERR',
1082 'SRCUIDEF',l_src_ui_def_id,
1083 'SRCINSTANCE',l_src_instance,
1084 'TGTUIDEF',l_tgt_ui_def_id,
1085 'TGTINSTANCE',l_tgt_instance);
1086 error_msg_populate(m_err_message ,'CZ_PB_MGR.CREATESRCPBRECORDS',SQLCODE);
1087 IF (l_ref_cursor%ISOPEN) THEN
1088 CLOSE l_ref_cursor;
1089 END IF;
1090 IF (l_model_cursor%ISOPEN) THEN
1091 CLOSE l_model_cursor;
1092 END IF;
1093 IF (l_templ_cursor%ISOPEN) THEN
1094 CLOSE l_templ_cursor;
1095 END IF;
1096 RAISE;
1097 WHEN MODELNAME_MISMATCH_ERR THEN
1098 ----get source and target info
1099 get_src_tgt_info(p_tgt_server_id, l_src_instance,l_tgt_instance);
1100 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_MODELNAME_ERR',
1101 'SRCMODEL',l_src_object_name,
1102 'SRCINSTANCE',l_src_instance,
1103 'TGTMODEL',l_tgt_object_name,
1104 'TGTINSTANCE',l_tgt_instance);
1105 error_msg_populate(m_err_message ,'CZ_PB_MGR.CREATESRCPBRECORDS',SQLCODE);
1106 IF (l_ref_cursor%ISOPEN) THEN
1107 CLOSE l_ref_cursor;
1108 END IF;
1109 IF (l_model_cursor%ISOPEN) THEN
1110 CLOSE l_model_cursor;
1111 END IF;
1112 IF (l_templ_cursor%ISOPEN) THEN
1113 CLOSE l_templ_cursor;
1114 END IF;
1115 RAISE;
1116 WHEN TEMPLNAME_MISMATCH_ERR THEN
1117 ----get source and target info
1118 get_src_tgt_info(p_tgt_server_id, l_src_instance,l_tgt_instance);
1119 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_TEMPLNAME_ERR',
1120 'SRCTEMPL',l_src_object_name,
1121 'SRCINSTANCE',l_src_instance,
1125 IF (l_ref_cursor%ISOPEN) THEN
1122 'TGTTEMPL',l_tgt_object_name,
1123 'TGTINSTANCE',l_tgt_instance);
1124 error_msg_populate(m_err_message ,'CZ_PB_MGR.CREATESRCPBRECORDS',SQLCODE);
1126 CLOSE l_ref_cursor;
1127 END IF;
1128
1129 IF (l_templ_cursor%ISOPEN) THEN
1130 CLOSE l_templ_cursor;
1131 END IF;
1132
1133 IF (l_model_cursor%ISOPEN) THEN
1134 CLOSE l_model_cursor;
1135 END IF;
1136 RAISE;
1137 WHEN OBJ_TYPE_MISMATCH_ERR THEN
1138 ----get source and target info
1139 get_src_tgt_info(p_tgt_server_id, l_src_instance,l_tgt_instance);
1140 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_TYPE_ERR',
1141 'TGTTYPE', l_tgt_object_type,
1142 'TGTPUBID',l_publication_id,
1143 'TGTINSTANCE',l_tgt_instance,
1144 'SRCTYPE', l_src_object_type,
1145 'SRCPUBID',l_src_pb_id,
1146 'SRCINSTANCE',l_src_instance);
1147 error_msg_populate(m_err_message ,'CZ_PB_MGR.CREATESRCPBRECORDS',SQLCODE);
1148 IF (l_ref_cursor%ISOPEN) THEN
1149 CLOSE l_ref_cursor;
1150 END IF;
1151
1152 IF (l_model_cursor%ISOPEN) THEN
1153 CLOSE l_model_cursor;
1154 END IF;
1155 RAISE;
1156
1157 IF (l_templ_cursor%ISOPEN) THEN
1158 CLOSE l_templ_cursor;
1159 END IF;
1160 RAISE;
1161
1162 WHEN OBJ_TYPE_ERR THEN
1163 ----get source and target info
1164 get_src_tgt_info(p_tgt_server_id, l_src_instance,l_tgt_instance);
1165 m_err_message := 'The object_type ' || l_src_object_type || ' of publication ' ||
1166 l_src_pb_id || ' on instance ' || l_src_instance || ' is invalid';
1167 error_msg_populate(m_err_message ,'CZ_PB_MGR.CREATESRCPBRECORDS',SQLCODE);
1168 IF (l_ref_cursor%ISOPEN) THEN
1169 CLOSE l_ref_cursor;
1170 END IF;
1171
1172 IF (l_model_cursor%ISOPEN) THEN
1173 CLOSE l_model_cursor;
1174 END IF;
1175 RAISE;
1176
1177 IF (l_templ_cursor%ISOPEN) THEN
1178 CLOSE l_templ_cursor;
1179 END IF;
1180 RAISE;
1181
1182 WHEN OTHERS THEN
1183 IF (l_ref_cursor%ISOPEN) THEN
1184 CLOSE l_ref_cursor;
1185 END IF;
1186
1187 IF (l_model_cursor%ISOPEN) THEN
1188 CLOSE l_model_cursor;
1189 END IF;
1190 IF (l_templ_cursor%ISOPEN) THEN
1191 CLOSE l_templ_cursor;
1192 END IF;
1193 RAISE;
1194 END create_src_pb_records;
1195
1196 ------------------------------------------------------
1197 -----function that validates input parameters
1198 FUNCTION verify_input_parameters(p_target_instance IN VARCHAR2)
1199 RETURN BOOLEAN
1200 IS
1201
1202 l_instance_name cz_servers.instance_name%TYPE;
1203 l_host_name cz_servers.hostname%TYPE;
1204 l_listener_port cz_servers.db_listener_port%TYPE;
1205 l_fndnam_link_name cz_servers.fndnam_link_name%TYPE;
1206 v_ret_flag BOOLEAN := TRUE;
1207
1208 BEGIN
1209 IF (p_target_instance IS NULL) THEN
1210 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_INPUT_INST_NULL');
1211 error_msg_populate(m_err_message ,'CZ_PB_SYNC.INPUTPARAMS',21004);
1212 v_ret_flag := FALSE;
1213 ELSE
1214 SELECT instance_name,hostname,db_listener_port,fndnam_link_name
1215 INTO l_instance_name,l_host_name,l_listener_port,l_fndnam_link_name
1216 FROM cz_servers
1217 WHERE UPPER(cz_servers.local_name) = UPPER(p_target_instance);
1218
1219 IF (l_instance_name IS NULL) THEN
1220 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_INSTANCE_IS_NULL', 'TGTINSTANCE',p_target_instance);
1221 error_msg_populate(m_err_message ,'CZ_PB_SYNC.INPUTPARAMS',21004);
1222 v_ret_flag := FALSE;
1223 END IF;
1224
1225 IF (l_host_name IS NULL) THEN
1226 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_HOST_IS_NULL','TGTINSTANCE',p_target_instance);
1227 error_msg_populate(m_err_message ,'CZ_PB_SYNC.INPUTPARAMS',21004);
1228 v_ret_flag := FALSE;
1229 END IF;
1230
1231 IF (l_listener_port IS NULL) THEN
1232 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_PORT_IS_NULL','TGTINSTANCE',p_target_instance);
1233 error_msg_populate(m_err_message ,'CZ_PB_SYNC.INPUTPARAMS',21004);
1234 v_ret_flag := FALSE;
1235 END IF;
1236
1237 IF (l_fndnam_link_name IS NULL) THEN
1238 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_LINK_IS_NULL','TGTINSTANCE',p_target_instance);
1239 error_msg_populate(m_err_message ,'CZ_PB_SYNC.INPUTPARAMS',21004);
1240 v_ret_flag := FALSE;
1241 END IF;
1242 END IF;
1243
1244 RETURN v_ret_flag;
1245 EXCEPTION
1246 WHEN NO_DATA_FOUND THEN
1247 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_NO_SVR_ENTRY','TGTINSTANCE',p_target_instance);
1248 error_msg_populate(m_err_message ,'CZ_PB_SYNC.INPUTPARAMS',21004);
1249 v_ret_flag := FALSE;
1250 RETURN v_ret_flag;
1251 WHEN OTHERS THEN
1252 v_ret_flag := FALSE;
1253 RETURN v_ret_flag;
1254 END verify_input_parameters;
1255
1256 -------------------------------------------------------
1257 -----procedure that updates server information on the target
1261
1258 PROCEDURE update_server_info(p_target_server_id IN cz_servers.server_local_id%TYPE,
1259 p_link_name IN cz_servers.fndnam_link_name%TYPE)
1260 IS
1262 l_instance_name cz_servers.instance_name%TYPE;
1263 l_host_name cz_servers.hostname%TYPE;
1264 l_listener_port cz_servers.db_listener_port%TYPE;
1265 l_sync_date VARCHAR2(50);
1266
1267 BEGIN
1268 l_sync_date := TO_CHAR(sysdate, 'mm-dd-yyyy hh24:mi:ss');
1269
1270 SELECT instance_name,hostname,db_listener_port
1271 INTO l_instance_name,l_host_name,l_listener_port
1272 FROM cz_servers
1273 WHERE cz_servers.server_local_id = p_target_server_id;
1274
1275 EXECUTE IMMEDIATE
1276 'UPDATE cz_servers'||p_link_name||' t ' ||
1277 ' SET t.instance_name = '''||l_instance_name||''', ' ||
1278 ' t.hostname = '''||l_host_name||''', ' ||
1279 ' t.db_listener_port = '||l_listener_port||', ' ||
1280 ' t.notes = '''||l_sync_date||''' ' ||
1281 ' WHERE UPPER(t.local_name) = ''LOCAL'' ';
1282 EXCEPTION
1283 WHEN NO_DATA_FOUND THEN
1284 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_UPD_SVR_ERR','SERVERID',p_target_server_id, 'SQLERRM',SQLERRM);
1285 error_msg_populate(m_err_message ,'CZ_PB_SYNC.GETTGTINSTANCE',21004);
1286 RAISE;
1287 WHEN OTHERS THEN
1288 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_UPD_SVR_ERR','SERVERID',p_target_server_id, 'SQLERRM',SQLERRM);
1289 error_msg_populate(m_err_message ,'CZ_PB_SYNC.GETTGTINSTANCE',21004);
1290 RAISE;
1291 END;
1292
1293 --------------------------------------------------------
1294 -----procedure that syncs publication data on the source
1295 -----and target servers
1296 ------@p_target_server_id --- server id of the target server
1297 ------@x_pb_clone_flg --- TRUE if sync was successful
1298
1299 PROCEDURE sync_publication_clone(p_target_server_id IN cz_servers.server_local_id%TYPE,
1300 x_pb_clone_flg OUT NOCOPY BOOLEAN)
1301 IS
1302
1303 v_link_name cz_servers.fndnam_link_name%TYPE;
1304
1305 BEGIN
1306 x_pb_clone_flg := TRUE;
1307
1308 ----for each tgt publication record create source publication record
1309 create_src_pb_records(p_target_server_id);
1310
1311 ----clear pb exports
1312 clear_pb_exports;
1313
1314 ----update server id in tgt publication record
1315 v_link_name := cz_pb_sync_util.retrieve_link_name(p_target_server_id);
1316 update_tgt_server_id(p_target_server_id,v_link_name);
1317
1318 ----update target server information
1319 update_server_info(p_target_server_id,v_link_name);
1320
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_INSERT_ERR', 'SQLERRM', SQLERRM);
1324 error_msg_populate(m_err_message ,'CZ_PB_SYNC.SYNCPBCLONE',21015);
1325 x_pb_clone_flg := FALSE;
1326 END sync_publication_clone;
1327
1328 -----------------------------------------------------
1329 --- procedure that synchronizes the publication data on the source and
1330 --- target servers
1331 PROCEDURE sync_cloned_tgt_pub_data(p_target_instance IN VARCHAR2,
1332 x_run_id OUT NOCOPY NUMBER,
1333 x_status OUT NOCOPY VARCHAR2)
1334 IS
1335
1336 l_src_verification BOOLEAN;
1337 l_target_server_id NUMBER := 0;
1338 l_validate_flg BOOLEAN := TRUE;
1339 l_has_to_be_synced_flg BOOLEAN := TRUE;
1340 l_pb_clone_flg BOOLEAN ;
1341 l_run_id NUMBER := 0;
1342 l_link_name cz_servers.fndnam_link_name%TYPE;
1343 l_proc_name v$session.module%TYPE;
1344
1345 BEGIN
1346
1347 ----initialize OUT NOCOPY variables
1348 x_status := FND_API.G_RET_STS_SUCCESS;
1349 m_msg_tbl.DELETE;
1350
1351 FOR I IN 1..1
1352 LOOP
1353 ----get run id
1354 l_run_id := cz_pb_sync_util.get_run_id;
1355 x_run_id := l_run_id;
1356
1357 IF (l_run_id = 0) THEN
1358 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SEQ_XFR');
1359 error_msg_populate(m_err_message ,'CZ_PB_SYNC.XFRINFO',21001);
1360 x_status := FND_API.G_RET_STS_ERROR;
1361 EXIT;
1362 END IF;
1363
1364 ----check if another sync is in progress
1365 l_proc_name := cz_pb_sync_util.check_process;
1366 IF l_proc_name IS NOT NULL THEN
1367 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_PROCESS_EXISTS');
1368 error_msg_populate(m_err_message ,'CZ_PB_SYNC.CHKPROCESS',21001);
1369 x_status := FND_API.G_RET_STS_ERROR;
1370 EXIT;
1371 ELSE
1372 ----register application
1373 cz_pb_sync_util.set_dbms_info('CZ_PB_SYNC');
1374 END IF;
1375
1376 ----validate input parameters
1377 IF (NOT verify_input_parameters(p_target_instance) ) THEN
1378 x_status := FND_API.G_RET_STS_ERROR;
1379 EXIT;
1380 END IF;
1381
1382 ---verify schema versions on the source and target instances
1383 l_target_server_id := cz_pb_sync_util.get_target_instance_id(p_target_instance);
1384
1385 ------check for active links
1386 l_link_name := cz_pb_sync_util.retrieve_link_name(l_target_server_id);
1387 IF ( NOT cz_pb_sync_util.check_db_link(l_link_name) ) THEN
1388 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_NO_LINK_EXISTS','LINKNAME',l_link_name);
1389 error_msg_populate(m_err_message ,'CHECKDBLINK',21025);
1393
1390 x_status := FND_API.G_RET_STS_ERROR;
1391 EXIT;
1392 END IF;
1394 ---verify that the above proc is called from the source
1395 l_src_verification := verify_source_instance(p_target_instance);
1396 IF (NOT l_src_verification) THEN
1397 x_status := FND_API.G_RET_STS_ERROR;
1398 EXIT;
1399 END IF;
1400
1401 l_validate_flg := cz_pb_sync_util.validate_schema(l_target_server_id);
1402 IF (NOT l_validate_flg ) THEN
1403 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SCHEMA_COMPAT_ERR');
1404 error_msg_populate(m_err_message ,'VALIDATESCHEMA',21005);
1405 x_status := FND_API.G_RET_STS_ERROR;
1406 EXIT;
1407 END IF;
1408
1409 -----verify that the target instance is a clone
1410 IF (check_target_instance(l_link_name) ) THEN
1411 x_status := FND_API.G_RET_STS_ERROR;
1412 EXIT;
1413 END IF;
1414
1415
1416 ----check if the pb data has to be synchronized
1417 l_has_to_be_synced_flg := has_to_be_synced(l_target_server_id);
1418 IF (NOT l_has_to_be_synced_flg) THEN
1419 x_status := FND_API.G_RET_STS_ERROR;
1420 EXIT;
1421 ELSE
1422 ---sync publication data for a single publication
1423 sync_publication_clone(l_target_server_id,l_pb_clone_flg);
1424
1425 IF (NOT l_pb_clone_flg) THEN
1426 x_status := FND_API.G_RET_STS_ERROR;
1427 ROLLBACK;
1428 EXIT;
1429 ELSE
1430 COMMIT;
1431 END IF;
1432 END IF;
1433 END LOOP;
1434
1435 ---log errors to cz_db_logs
1436 IF (x_status = FND_API.G_RET_STS_ERROR) THEN
1437 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_FAILURE', 'INSTANCENAME',p_target_instance);
1438 error_msg_populate(m_err_message ,'SYNCCLOTGTPBDATA',21014);
1439 END IF;
1440 cz_pb_sync_util.log_pb_sync_errors(m_msg_tbl,l_run_id);
1441
1442 -----unregister application
1443 cz_pb_sync_util.reset_dbms_info;
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 x_status := FND_API.G_RET_STS_UNEXP_ERROR;
1447 END sync_cloned_tgt_pub_data;
1448
1449 ------------------------------------------------------------
1450 -----concurrent manager program for cloned tgt instance
1451 PROCEDURE sync_cloned_tgt_pub_data_cp(Errbuf IN OUT NOCOPY VARCHAR2,
1452 Retcode IN OUT NOCOPY PLS_INTEGER,
1453 p_target_instance IN VARCHAR2)
1454
1455 IS
1456
1457 v_run_id NUMBER := 0.0;
1458 v_sync_status VARCHAR2(1);
1459
1460 BEGIN
1461 Retcode:=0;
1462 sync_cloned_tgt_pub_data(p_target_instance,v_run_id,v_sync_status);
1463
1464 Errbuf := NULL;
1465 IF (v_sync_status = FND_API.G_RET_STS_ERROR) THEN
1466 Errbuf := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_FAILURE', 'INSTANCENAME',p_target_instance);
1467 Retcode:= 2;
1468 END IF;
1469
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472 Retcode := 2;
1473 Errbuf := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_FAILURE', 'TGTINSTANCE',p_target_instance);
1474 END sync_cloned_tgt_pub_data_cp;
1475
1476 ---------------------------------------------------------
1477 ----get local server information
1478 PROCEDURE get_local_server_info (x_hostname OUT NOCOPY cz_servers.hostname%TYPE,
1479 x_instance_name OUT NOCOPY cz_servers.instance_name%TYPE)
1480 IS
1481
1482 BEGIN
1483 SELECT host_name,instance_name
1484 INTO x_hostname,x_instance_name
1485 FROM v$instance;
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 RAISE;
1489 END get_local_server_info;
1490
1491 --------------------------------------------------------
1492 PROCEDURE modify_target_server(p_link_name IN VARCHAR2,
1493 p_hostname IN cz_servers.hostname%TYPE,
1494 p_instance_name IN cz_servers.instance_name%TYPE,
1495 p_local_name IN cz_servers.local_name%TYPE)
1496 IS
1497
1498 v_str VARCHAR2(2000);
1499
1500 BEGIN
1501 v_str := ' UPDATE cz_servers'||p_link_name||' SET hostname = '''||p_hostname||''', ' ||
1502 ' instance_name = '''||p_instance_name||''', ' ||
1503 ' local_name = :1 ' ||
1504 ' WHERE source_server_flag = ''1'' ';
1505
1506 EXECUTE IMMEDIATE v_str USING p_local_name ;
1507 COMMIT;
1508 EXCEPTION
1509 WHEN OTHERS THEN
1510 RAISE;
1511 END;
1512
1513 -----procedure that retrieves the link name
1514 FUNCTION get_link_name(p_local_name IN cz_servers.local_name%TYPE)
1515 RETURN VARCHAR2
1516 IS
1517
1518 l_link_name cz_servers.fndnam_link_name%TYPE := NULL;
1519
1520 BEGIN
1521 SELECT fndnam_link_name
1522 INTO l_link_name
1523 FROM cz_servers
1524 WHERE UPPER(local_name) = UPPER(p_local_name);
1525
1526 RETURN l_link_name;
1527 EXCEPTION
1528 WHEN OTHERS THEN
1529 RETURN l_link_name;
1530 END get_link_name;
1531 ----------------------------------------------------------
1532 --- procedure that synchronizes the publication data on the source and
1533 --- target servers after the source server has been cloned
1534 PROCEDURE sync_cloned_src_pub_data(p_decomm_flag IN VARCHAR2,
1535 x_run_id OUT NOCOPY NUMBER,
1536 x_status OUT NOCOPY VARCHAR2)
1537 IS
1538
1539 v_src_verification BOOLEAN;
1540 v_target_server_id NUMBER := 0;
1541 v_validate_flg BOOLEAN := TRUE;
1542 v_has_to_be_synced_flg BOOLEAN := TRUE;
1543 v_pb_clone_flg BOOLEAN ;
1544 v_run_id NUMBER := 0;
1545 v_link_name cz_servers.fndnam_link_name%TYPE;
1546
1547 l_hostname cz_servers.hostname%TYPE;
1548 l_instance_name cz_servers.instance_name%TYPE;
1549 l_local_name_tbl t_name ;
1550 l_message VARCHAr2(2000);
1551 l_proc_name v$session.module%TYPE;
1552
1553 BEGIN
1554
1555 ----initialize OUT NOCOPY variables
1556 x_status := FND_API.G_RET_STS_SUCCESS;
1557 m_msg_tbl.DELETE;
1558
1559 FOR I IN 1..1
1560 LOOP
1561 ----get run id for message
1562 v_run_id := cz_pb_sync_util.get_run_id;
1563 x_run_id := v_run_id;
1564
1565 IF (v_run_id = 0) THEN
1566 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SEQ_XFR');
1567 error_msg_populate(m_err_message ,'CZ_PB_SYNC.XFRINFO',21001);
1568 x_status := FND_API.G_RET_STS_ERROR;
1569 EXIT;
1570 END IF;
1571
1572 ----check if another sync is in progress
1573 l_proc_name := cz_pb_sync_util.check_process;
1574 IF l_proc_name IS NOT NULL THEN
1575 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_PROCESS_EXISTS');
1576 error_msg_populate(m_err_message ,'CZ_PB_SYNC.CHKPROCESS',21001);
1577 x_status := FND_API.G_RET_STS_ERROR;
1578 EXIT;
1579 ELSE
1580 ----register application
1581 cz_pb_sync_util.set_dbms_info('CZ_PB_SYNC');
1582 END IF;
1583
1584 ----validate input parameter
1585 IF (UPPER(p_decomm_flag) NOT IN ('YES','NO') ) THEN
1586 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_INPUT_FLG_INVALID', 'FLAG', p_decomm_flag);
1587 error_msg_populate(m_err_message ,'CZ_PB_SYNC.DECOMMFLG',21004);
1588 x_status := FND_API.G_RET_STS_ERROR;
1589 EXIT;
1590 END IF;
1591
1592 -----update local server entry
1593 get_local_server_info (l_hostname,l_instance_name);
1594
1595 UPDATE cz_servers
1596 set hostname = l_hostname,
1597 instance_name = l_instance_name
1598 WHERE UPPER(cz_servers.local_name) = 'LOCAL';
1599
1600 -----recreate database links
1601 BEGIN
1602 SELECT local_name
1603 BULK
1604 COLLECT
1605 INTO l_local_name_tbl
1606 FROM cz_servers
1607 WHERE UPPER(cz_servers.local_name) <> 'LOCAL';
1608 EXCEPTION
1609 WHEN NO_DATA_FOUND THEN
1610 -----do not care
1611 NULL;
1612 END;
1613
1614
1615 IF (l_local_name_tbl.COUNT > 0) THEN
1616 FOR localName IN l_local_name_tbl.FIRST..l_local_name_tbl.LAST
1617 LOOP
1618 v_link_name := get_link_name(l_local_name_tbl(localName));
1619 IF (v_link_name IS NOT NULL) THEN
1620 v_link_name := '@'||v_link_name;
1621 IF ( NOT cz_pb_sync_util.check_db_link(v_link_name) ) THEN
1622 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_NO_LINK_EXISTS','LINKNAME',v_link_name);
1623 error_msg_populate(m_err_message ,'CHECKDBLINK',21025);
1624 x_status := FND_API.G_RET_STS_ERROR;
1625 EXIT;
1626 END IF;
1627 END IF;
1628
1629 IF (UPPER(p_decomm_flag) = 'YES') THEN
1630 modify_target_server(v_link_name,l_hostname,l_instance_name,l_instance_name);
1631 ELSE
1632 UPDATE cz_model_publications set deleted_flag = '1';
1633 COMMIT;
1634 END IF;
1635 END LOOP;
1636 END IF;
1637 END LOOP;
1638
1639 ---log errors to cz_db_logs
1640 IF (x_status = FND_API.G_RET_STS_ERROR) THEN
1641 m_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_FAILURE', 'SRCINSTANCE',l_instance_name);
1642 error_msg_populate(m_err_message ,'SYNCCLOTGTPBDATA',21014);
1643 x_status := FND_API.G_RET_STS_ERROR;
1644 END IF;
1645 cz_pb_sync_util.log_pb_sync_errors(m_msg_tbl,v_run_id);
1646
1647 -----unregister application
1648 cz_pb_sync_util.reset_dbms_info;
1649 COMMIT;
1650 EXCEPTION
1651 WHEN OTHERS THEN
1652 x_status := FND_API.G_RET_STS_UNEXP_ERROR;
1653 END sync_cloned_src_pub_data;
1654
1655 -------------------------------------------------------------------
1656 -----concurrent manager program for cloned src instance
1657 PROCEDURE sync_cloned_src_pub_data_cp(Errbuf IN OUT NOCOPY VARCHAR2,
1658 Retcode IN OUT NOCOPY PLS_INTEGER,
1659 p_decomm_flag IN VARCHAR2)
1660
1661 IS
1662
1663 v_run_id NUMBER := 0.0;
1664 v_sync_status VARCHAR2(1);
1665 l_src_instance cz_servers.instance_name%TYPE;
1666
1667 BEGIN
1668 Retcode:=0;
1669 Errbuf := NULL;
1670
1671 SELECT name INTO l_src_instance from v$database;
1672 sync_cloned_src_pub_data(p_decomm_flag,v_run_id,v_sync_status);
1673 IF (v_sync_status = FND_API.G_RET_STS_ERROR) THEN
1674 Errbuf := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_FAILURE', 'INSTANCENAME',l_src_instance );
1675 Retcode:= 2;
1676 END IF;
1677 EXCEPTION
1678 WHEN OTHERS THEN
1679 Retcode := 2;
1680 Errbuf := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_FAILURE', 'TGTINSTANCE',l_src_instance );
1681 END sync_cloned_src_pub_data_cp;
1682
1683
1684 ---------------------------------------------------------------------
1685
1686 END cz_pb_sync; /* end of package */
[Home] [Help]