[Home] [Help]
PACKAGE BODY: APPS.CZ_PUBL_SYNC_CRASH
Source
1 PACKAGE BODY CZ_PUBL_SYNC_CRASH AS
2 /* $Header: czpsynb.pls 120.1 2006/04/27 03:20:40 kdande ship $ */
3
4 pkg_name VARCHAR2(30) := 'CZ_PUBL_SYNC_CRASH';
5
6 PROCEDURE SET_DBMS_INFO(p_module_name IN VARCHAR2)
7 IS
8 BEGIN
9 CZ_ADMIN.SPX_SYNC_PUBLISHSESSIONS;
10 DBMS_APPLICATION_INFO.SET_MODULE(p_module_name,'');
11 END;
12 ------------------------------------------------------------------------------------------
13 /* clear session */
14 PROCEDURE RESET_DBMS_INFO
15 IS
16 BEGIN
17 DBMS_APPLICATION_INFO.SET_MODULE('', '');
18 END;
19 ------------------------------------------------------------------------------------------
20 /* Validate if the input server id matches the sid for that server and the db link is alive */
21
22 FUNCTION validateServer(p_server_id IN NUMBER)
23 RETURN BOOLEAN
24 IS
25 lServerName CZ_SERVERS.local_name%type;
26 lLinkName CZ_SERVERS.fndnam_link_name%type;
27 lHostName CZ_SERVERS.HOSTNAME%type;
28 lSid CZ_SERVERS.INSTANCE_NAME%type;
29 lHost CZ_SERVERS.HOSTNAME%type;
30 x_server_f BOOLEAN := FALSE;
31 CURSOR c_get_server IS
32 SELECT local_name, fndnam_link_name FROM CZ_SERVERS
33 WHERE server_local_id = p_server_id;
34 BEGIN
35
36 BEGIN
37 EXECUTE IMMEDIATE
38 ' SELECT fndnam_link_name,local_name, hostname FROM CZ_SERVERS WHERE server_local_id = :1'
39 INTO lLinkName, lServerName, lHostName
40 USING p_server_id ;
41 EXCEPTION
42 WHEN NO_DATA_FOUND THEN
43 RAISE SERVER_NOT_FOUND;
44 END;
45
46 IF (lLinkName <> NULL) THEN
47 lLinkName := '@' || LTRIM(RTRIM(lLinkName));
48 END IF;
49
50 IF (lServerName <> NULL) THEN
51 IF (lServerName = 'LOCAL') THEN
52 NULL;
53 ELSE
54 IF ((lLinkName <> NULL) AND (CZ_ORAAPPS_INTEGRATE.LINK_IS_DOWN = (cz_oraapps_integrate.isLinkAlive(lLinkName)))) THEN
55 RAISE DB_LINK_DOWN;
56 END IF;
57 END IF;
58
59 execute immediate 'select INSTANCE_NAME, HOST_NAME from v$instance@'|| lLinkName
60 INTO lSid, lHost;
61 IF ((upper(lSid) = upper(lServerName)) and (upper(lHost) = upper(lHostName))) THEN
62 return true;
63 ELSE
64 RAISE DB_TNS_INCORRECT;
65 END IF;
66 ELSE
67 /*
68 ERRNO := czError;
69 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_NOT_FOUND','SERVERNAME',lServerName);
70 xERROR := CZ_UTILS.REPORT(ERRBUF,1,'CZ_PUBL_SYNC_CRASH.VALIDATE_SERVER',11276);
71 return false;
72 */
73 RAISE SERVER_NOT_FOUND;
74 END IF;
75 EXCEPTION
76 WHEN OTHERS THEN
77 /*
78 ERRNO := czError;
79 ERRBUF := CZ_UTILS.GET_TEXT(SQLERRM);
80 xERROR := CZ_UTILS.REPORT(ERRBUF,1,'CZ_PUBL_SYNC_CRASH.VALIDATE_SERVER',11276);
81 return false;
82 */
83 RAISE VALIDATE_SERVER_ERROR;
84 END validateServer;
85
86 ------------------------------------------------------------------------------------------
87 ------------------------------------------------------------------------------------------
88 /* Validate if the input server id matches the sid for that server and the db link is alive */
89
90 FUNCTION verifyServer(p_link_name IN VARCHAR2)
91 RETURN BOOLEAN
92 IS
93 lServerName CZ_SERVERS.local_name%type;
94 lLinkName CZ_SERVERS.fndnam_link_name%type;
95 lHostName CZ_SERVERS.HOSTNAME%type;
96 lInstanceName CZ_SERVERS.INSTANCE_NAME%type;
97 lSid CZ_SERVERS.INSTANCE_NAME%type;
98 lHost CZ_SERVERS.HOSTNAME%type;
99 x_server_f BOOLEAN := FALSE;
100
101 BEGIN
102
103 BEGIN
104 EXECUTE IMMEDIATE
105 ' SELECT local_name, hostname, instance_name FROM CZ_SERVERS' || p_link_name ||
106 ' WHERE source_server_flag = ''1'''
107 INTO lServerName,lHostName, lInstanceName;
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 RAISE SERVER_NOT_FOUND;
111 END;
112
113 IF (lServerName is NOT NULL) THEN
114 IF (lServerName = 'LOCAL') THEN
115 NULL;
116 ELSE
117 execute immediate 'select INSTANCE_NAME, HOST_NAME from v$instance'
118 INTO lSid, lHost ;
119 IF ((upper(lSid) = upper(lServerName)) and (upper(lHost) = upper(lHostName))) THEN
120 return true;
121 ELSE
122 /* ERRNO := czError;
123 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_MISMATCH','SERVERNAME',lServerName,'DATABASE',lSid);
124 return false; */
125 RAISE DB_TNS_INCORRECT;
126 END IF;
127 END IF;
128 ELSE
129 /*
130 ERRNO := czError;
131 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_NOT_FOUND','SERVERNAME',lServerName);
132 xERROR := CZ_UTILS.REPORT(ERRBUF,1,'CZ_PUBL_SYNC_CRASH.VALIDATE_SERVER',11276);
133 return false;
134 */
135 RAISE SERVER_NOT_FOUND;
136 END IF;
137 EXCEPTION
138 WHEN OTHERS THEN
139 RAISE VALIDATE_SERVER_ERROR;
140 END verifyServer;
141
142 ------------------------------------------------------------------------------------------
143
144 -- Validate if this is the source server based on if the source_server_flag is set.
145 FUNCTION checkIfSource(p_server_id IN NUMBER)
146 RETURN BOOLEAN
147 IS
148
149 lServerId CZ_SERVERS.server_local_id%type;
150 rServerId CZ_SERVERS.server_local_id%type;
151 lServerName CZ_SERVERS.local_name%type;
152 lName CZ_SERVERS.local_name%type;
153 x_source_server_f BOOLEAN := FALSE;
154
155 CURSOR c_source_servers IS
156 SELECT local_name FROM CZ_SERVERS
157 WHERE source_server_flag = '1'
158 AND server_local_id = p_server_id;
159
160 str varchar2(255);
161 BEGIN
162 /* open c_source_servers;
163 FETCH c_source_servers INTO lServerName;
164 x_source_server_f:= c_source_servers%FOUND;
165 close c_source_servers;
166 */
167 select fndnam_link_name into lName from CZ_SERVERS
168 WHERE server_local_id = p_server_id;
169
170 IF (lName is not NULL) THEN
171 lName := '@' || lName;
172 EXECUTE IMMEDIATE 'SELECT local_name, server_local_id FROM CZ_SERVERS' || lName ||
173 ' WHERE source_server_flag = ''1'' '
174 INTO lServerName, rServerId;
175
176 -- if (NOT(x_source_server_f)) then
177 IF (NOT(verifyServer(lName) ) ) THEN
178 RAISE INCORRECT_SOURCE;
179 else
180 return true;
181 end if;
182 END IF;
183 EXCEPTION
184 WHEN OTHERS THEN
185 RAISE CZ_SYNC_ERROR;
186 END checkIfSource;
187
188 ------------------------------------------------------------------------------------------
189
190 /* Deletes publication data from the source */
191
192
193 PROCEDURE DELETE_PUBLICATION_DATA (p_target_server_id IN NUMBER)
194 IS
195 -- xERROR BOOLEAN := FALSE;
196 --TYPE tPublicationIds IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
197 --lPublicationIds tPublicationIds;
198 BEGIN
199 UPDATE cz_model_publications
200 SET deleted_flag = '1'
201 WHERE server_id = p_target_server_id;
202
203 DELETE FROM cz_pb_client_apps
204 WHERE publication_id in (SELECT publication_id from cz_model_publications
205 where server_id = p_target_server_id);
206
207 DELETE FROM cz_publication_usages
208 WHERE publication_id in (SELECT publication_id from cz_model_publications
209 where server_id = p_target_server_id);
210
211 DELETE FROM cz_pb_languages
212 WHERE publication_id in (SELECT publication_id from cz_model_publications
213 where server_id = p_target_server_id);
214
215 DELETE FROM cz_pb_model_exports
216 WHERE server_id = p_target_server_id;
217 COMMIT;
218 EXCEPTION
219 WHEN OTHERS THEN
220 ERRNO := czError;
221 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
222 cz_utils.log_report(pkg_name, 'DELETE_PUBLICATION_DATA', null, ERRBUF, fnd_log.LEVEL_ERROR);
223 RAISE DELETE_PUBLICATION_ERROR;
224 END DELETE_PUBLICATION_DATA ;
225
226 -----------------------------------------------------------------------------------------
227 /* Deletes publication data from the source */
228
229 /* not in use currently
230 PROCEDURE DELETE_PUBLICATION_DATA (p_target_server_id IN NUMBER,
231 p_date IN DATE default to_date('01/01/1970', 'mm/dd/yyyy') )
232 IS
233 -- xERROR BOOLEAN := FALSE;
234 --TYPE tPublicationIds IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
235 --lPublicationIds tPublicationIds;
236
237 BEGIN
238 UPDATE cz_model_publications
239 SET deleted_flag = '1'
240 WHERE server_id = p_target_server_id
241 and model_last_updated > p_date;
242
243 DELETE FROM cz_pb_client_apps
244 WHERE publication_id in (SELECT publication_id from cz_model_publications
245 where server_id = p_target_server_id
246 and model_last_updated > p_date);
247
248 DELETE FROM cz_publication_usages
249 WHERE publication_id in (SELECT publication_id from cz_model_publications
250 where server_id = p_target_server_id
251 and model_last_updated > p_date);
252
253 DELETE FROM cz_pb_languages
254 WHERE publication_id in (SELECT publication_id from cz_model_publications
255 where server_id = p_target_server_id
256 and model_last_updated > p_date);
257
258 DELETE FROM cz_pb_model_exports
259 WHERE server_id = p_target_server_id;
260 COMMIT;
261 EXCEPTION
262 WHEN OTHERS THEN
263 RAISE DELETE_PUBLICATION_ERROR;
264 END DELETE_PUBLICATION_DATA ;
265 */
266 ------------------------------------------------------------------------------------------
267 /* Deletes publication data from the source */
268
269 PROCEDURE DELETE_PUBLICATION ( p_publication_id IN NUMBER,
270 p_target_server_id IN NUMBER DEFAULT 0,
271 p_link_name IN VARCHAR2 DEFAULT NULL,
272 p_date IN DATE)
273 IS
274 -- xERROR BOOLEAN := FALSE;
275 --TYPE tPublicationIds IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
276 --lPublicationIds tPublicationIds;
277 lLinkName VARCHAR2(255);
278 BEGIN
279 /*
280 IF (p_link_name <> NULL) THEN
281 lLinkName = '@'||RTIM(LTRIM(p_link_name));
282 END IF;
283 */
284
285 EXECUTE IMMEDIATE
286 'UPDATE cz_model_publications' || p_link_name ||
287 ' SET deleted_flag = ''1'' WHERE publication_id = :1 AND last_update_date > :2'
288 USING p_publication_id, p_date;
289
290 EXECUTE IMMEDIATE
291 ' DELETE FROM cz_pb_client_apps' || p_link_name ||
292 ' WHERE publication_id = :1'
293 USING p_publication_id;
294
295 EXECUTE IMMEDIATE
296 ' DELETE FROM cz_publication_usages' || p_link_name ||
297 ' WHERE publication_id = :1'
298 USING p_publication_id;
299
300 EXECUTE IMMEDIATE
301 ' DELETE FROM cz_pb_languages' || p_link_name ||
302 ' WHERE publication_id publication_id = :1'
303 USING p_publication_id;
304
305 EXECUTE IMMEDIATE
306 ' DELETE FROM cz_pb_model_exports' || p_link_name ||
307 ' WHERE server_id = :1'
308 USING p_target_server_id;
309
310 COMMIT;
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 /*
315 ERRNO := czError;
316 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
317 xERROR := CZ_UTILS.REPORT(ERRBUF,1,'CZ_PUBL_SYNC_CRASH.SYNC_ALL_SOURCE_CP',11276);
318 */
319 RAISE DELETE_PUBLICATION_ERROR;
320 END DELETE_PUBLICATION;
321
322 ------------------------------------------------------------------------------------------
323
324 /* Deletes publication data from the source and target */
325
326 PROCEDURE DELETE_DELETED_PUBLICATIONS ( p_server_id IN NUMBER,
327 p_date IN DATE default to_date('01/01/1970', 'mm/dd/yyyy'))
328 IS
329 -- xERROR BOOLEAN := FALSE;
330 --TYPE tPublicationIds IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
331 --lPublicationIds tPublicationIds;
332 lServerName CZ_SERVERS.local_name%type;
333 lLinkName CZ_SERVERS.fndnam_link_name%type;
334 lTargetServerId CZ_SERVERS.server_local_id%type;
335 lSid CZ_SERVERS.instance_name%type;
336 lHostName CZ_SERVERS.hostname%type;
337 x_server_f BOOLEAN := FALSE;
338
339 TYPE t_publ_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
340 v_deleted_pub_tbl t_publ_tbl;
341 v_deleted_remote_pub_tbl t_publ_tbl;
342 v_pub_tbl t_publ_tbl;
343 v_remote_pub_tbl t_publ_tbl;
344 v_publication_id CZ_MODEL_PUBLICATIONS.publication_id%type;
345
346 BEGIN
347
348 SELECT fndnam_link_name, server_local_id INTO lLinkName, lServerName FROM CZ_SERVERS
349 WHERE server_local_id = p_server_id;
350
351 IF (lLinkName <> NULL) THEN
352 lLinkName := '@' || LTRIM(RTRIM(lLinkName));
353 END IF;
354
355 /*
356 EXECUTE IMMEDIATE
357 ' SELECT publication_id BULK COLLECT INTO ' || v_deleted_pub_tbl ||
358 ' from cz_model_publications' || lLinkName ||
359 ' where deleted_flag = ''1''';
360
361 IF (v_deleted_pub_tbl.COUNT > 0) THEN
362 FOR i IN v_deleted_pub_tbl.FIRST..v_deleted_pub_tbl.LAST
363 LOOP
364 DELETE_PUBLICATION(v_deleted_pub_tbl(i),p_server_id,lLinkName);
365 END LOOP;
366 END IF;
367 */
368
369 -- delete publications in source for which the target publication has been deleted
370 SELECT publication_id, remote_publication_id
371 BULK COLLECT INTO v_deleted_pub_tbl, v_deleted_remote_pub_tbl
372 from cz_model_publications
373 where deleted_flag = '1';
374
375 IF (v_deleted_pub_tbl.COUNT > 0) THEN
376 FOR i IN v_deleted_pub_tbl.FIRST..v_deleted_pub_tbl.LAST
377 LOOP
378 DELETE_PUBLICATION(v_deleted_remote_pub_tbl(i),p_server_id,lLinkName,p_date);
379 END LOOP;
380 END IF;
381
382 -- delete publications in target for which the source publication has been deleted
383 SELECT publication_id
384 BULK COLLECT INTO v_pub_tbl
385 from cz_model_publications
386 where deleted_flag = '0';
387
388 IF (v_pub_tbl.COUNT > 0) THEN
389 FOR i IN v_pub_tbl.FIRST..v_pub_tbl.LAST
390 LOOP
391 EXECUTE IMMEDIATE
392 ' SELECT publication_id INTO ' || v_publication_id ||
393 ' from cz_model_publications' || lLinkName ||
394 ' where remote_publication_id = :1 and deleted_flag = ''1'''
395 USING v_pub_tbl(i);
396
397 IF (v_publication_id <> NULL) THEN
398 DELETE_PUBLICATION(v_pub_tbl(i),0,NULL,p_date);
399 END IF;
400 END LOOP;
401 END IF;
402
403 -- Delete all history for this target on the source.
404
405 SELECT hostname, instance_name
406 INTO lHostName, lSid
407 FROM CZ_SERVERS
408 WHERE server_local_id = '0';
409
410 EXECUTE IMMEDIATE
411 'SELECT SERVER_LOCAL_ID FROM CZ_SERVERS' || lLinkName ||
412 ' INTO ' || lTargetServerId ||
413 ' WHERE hostname = ' || lHostName ||
414 ' AND instance_name = ' || lSid;
415
416 EXECUTE IMMEDIATE
417 ' DELETE FROM CZ_PB_MODEL_EXPORTS' || lLinkName ||
418 ' WHERE server_id = :1 AND last_update_date = :2'
419 USING lTargetServerId, p_date;
420
421 COMMIT;
422 EXCEPTION
423 WHEN OTHERS THEN
424 /*
425 ERRNO := czError;
426 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
427 xERROR := CZ_UTILS.REPORT(ERRBUF,1,'CZ_PUBL_SYNC_CRASH.SYNC_ALL_SOURCE_CP',11276);
428 */
429 RAISE DELETE_DEL_PUBLICATION_ERROR;
430 END DELETE_DELETED_PUBLICATIONS ;
431 ------------------------------------------------------------------------------------------
432 /* Check if the applicability parameters match for the publication and its remote publication */
433
434 FUNCTION checkApplicabilityParameters(publicationId IN NUMBER,
435 linkName IN VARCHAR2)
436 RETURN boolean
437 IS
438 TYPE tPublLangs IS TABLE OF cz_pb_languages.language%type INDEX BY BINARY_INTEGER;
439 lPublishedLanguages tPublLangs ;
440 rPublishedLanguages tPublLangs ;
441 TYPE tPublIds IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
442 lPublUsages tPublIds;
443 lPublApps tPublIds;
444 rPublUsages tPublIds;
445 rPublApps tPublIds;
446 i NUMBER;
447 j NUMBER;
448 found BOOLEAN := FALSE;
449
450 BEGIN
451
452 SELECT language BULK COLLECT INTO lPublishedLanguages
453 FROM CZ_PB_LANGUAGES
454 WHERE publication_id = publicationId;
455
456 SELECT usage_id BULK COLLECT INTO lPublUsages
457 FROM CZ_PUBLICATION_USAGES
458 WHERE publication_id = publicationId;
459
460 SELECT fnd_application_id BULK COLLECT INTO lPublApps
461 FROM CZ_PB_CLIENT_APPS
462 WHERE publication_id = publicationId;
463
464 EXECUTE IMMEDIATE 'Begin SELECT language bulk collect INTO rPublishedLanguages FROM CZ_PB_LANGUAGES' || linkName || ' WHERE publication_id = publicationId; End;';
465
466 EXECUTE IMMEDIATE 'Begin SELECT usage_id bulk collect INTO rPublUsages FROM CZ_PUBLICATION_USAGES' || linkName || ' WHERE publication_id = publicationId; End;';
467
468 EXECUTE IMMEDIATE 'Begin SELECT fnd_application_id bulk collect INTO rPublApps FROM CZ_PB_CLIENT_APPS' || linkName || ' WHERE publication_id = publicationId; End;';
469
470 IF (lPublishedLanguages.COUNT > 0) THEN
471 FOR I IN lPublishedLanguages.FIRST..lPublishedLanguages.LAST
472 LOOP
473 found := FALSE;
474 IF (rPublishedLanguages.COUNT > 0) THEN
475 FOR J IN rPublishedLanguages.FIRST..rPublishedLanguages.LAST
476 LOOP
477 IF (upper(lPublishedLanguages(i)) = upper(rPublishedLanguages(j))) THEN
478 found := TRUE;
479 ELSE
480 return false;
481 -- EXIT;
482 END IF;
483 END LOOP;
484 END IF;
485 END LOOP;
486 END IF;
487 found := FALSE;
488
489 IF (lPublUsages.COUNT > 0) THEN
490 FOR I IN lPublUsages.FIRST..lPublUsages.LAST
491 LOOP
492 found := FALSE;
493 IF (rPublUsages.COUNT > 0) THEN
494 FOR J IN rPublUsages.FIRST..rPublUsages.LAST
495 LOOP
496 IF (upper(lPublUsages(i)) = upper(rPublUsages(j))) THEN
497 found := TRUE;
498 ELSE
499 return false;
500 END IF;
501 END LOOP;
502 END IF;
503 END LOOP;
504 END IF;
505 found := FALSE;
506
507 IF (lPublApps.COUNT > 0) THEN
508 FOR I IN lPublApps.FIRST..lPublApps.LAST
509 LOOP
510 found := FALSE;
511 IF (rPublApps.COUNT > 0) THEN
512 FOR J IN rPublApps.FIRST..rPublApps.LAST
513 LOOP
514 IF (upper(lPublApps(i)) = upper(rPublApps(j))) THEN
515 found := TRUE;
516 ELSE
517 return false;
518 END IF;
519 END LOOP;
520 END IF;
521 END LOOP;
522 END IF;
523
524 return true;
525 END checkApplicabilityParameters;
526 ------------------------------------------------------------------------------------------
527 /* Create publication data on source */
528
529 PROCEDURE CREATE_PUBLICATION_DATA ( errBuf IN OUT NOCOPY VARCHAR,
530 p_target_server_id IN NUMBER,
531 x_count IN OUT NOCOPY NUMBER)
532 IS
533 -- xERROR BOOLEAN := FALSE;
534 --TYPE tPublicationIds IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
535 --lPublicationIds tPublicationIds;
536 lServerName CZ_SERVERS.local_name%type;
537 lLinkName CZ_SERVERS.fndnam_link_name%type;
538 x_server_f BOOLEAN := FALSE;
539
540 n_source_model_id cz_ps_nodes.ps_node_id%type;
541 n_source_ui_def_id cz_ui_nodes.ui_def_id%type;
542 lCount NUMBER;
543 lPublicationId cz_model_publications.publication_id%type;
544
545 publications_cur REF_CURSOR;
546 TYPE t_publ_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
547 v_pub_tbl t_publ_tbl;
548
549 BEGIN
550
551 SELECT fndnam_link_name INTO lLinkName FROM CZ_SERVERS
552 WHERE server_local_id = p_target_server_id;
553
554 IF (lLinkName is NOT NULL) THEN
555 lLinkName := '@' || LTRIM(RTRIM(lLinkName));
556 END IF;
557
558 -- insert into publication tables
559 EXECUTE IMMEDIATE
560 'SELECT count(*) from cz_model_publications' || lLinkName || ' where source_model_id is null'
561 INTO lCount;
562 x_count := lCount;
563
564 if (lCount > 0) then
565 begin
566 OPEN publications_cur FOR ' SELECT publication_id FROM cz_model_publications'||lLinkName ||
567 ' WHERE deleted_flag = ''0'' ';
568 LOOP
569 lPublicationId := NULL;
570 FETCH publications_cur INTO lPublicationId;
571 EXIT WHEN publications_cur%NOTFOUND;
572
573 BEGIN
574 -- Match applicability parameters. Call raparti's procedure here
575 IF (not(checkApplicabilityParameters(lPublicationId,lLinkName))) THEN
576 RAISE APPLICABILITY_PARAM_ERR;
577 END IF;
578
579 -- resolve source_model_id
580 EXECUTE IMMEDIATE 'select pb.model_id into ' || n_source_model_id ||
581 ' from cz_model_publications' || lLinkName || ' pb, cz_ps_nodes' || lLinkName || ' ps, ' ||
582 'cz_ps_nodes p ' ||
583 'where pb.model_id = ps.ps_node_id ' ||
584 'and pb.persistent_node_id = ps.persistent_node_id ' ||
585 'and pb_source_model_id is null ' ||
586 'and pb.persistent_node_id = p.persistent_node_id ' ||
587 'and ps.name = p.name' ;
588 exception
589 when no_data_found then
590 -- ERRNO := czError;
591 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_MODEL_NOT_FOUND', 'PUBID', lPublicationId);
592 cz_utils.log_report(pkg_name, 'CREATE_PUBLICATION_DATA', null, ERRBUF, fnd_log.LEVEL_ERROR);
593 -- RAISE SOURCE_MODEL_NOT_FOUND;
594 END;
595 -- resolve source_ui_def_id
596 BEGIN
597 EXECUTE IMMEDIATE 'select pb.ui_def_id into ' || n_source_ui_def_id || ' ' ||
598 'from cz_model_publications' || lLinkName || ' pb, cz_ui_nodes' || lLinkName || ' ui, ' ||
599 'cz_ui_nodes u ' ||
600 'where pb.ui_def_id = ui.ui_def_id ' ||
601 'and pb.source_ui_def_id is null ' ||
602 'and ui.persistent_node_id = u.persistent_ui_node_id ' ||
603 'and u.parent_id is null ' ||
604 'and ui.name = u.name' ;
605 exception
606 when no_data_found then
607 -- ERRNO := czError;
608 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_MODEL_NOT_FOUND', 'PUBID', lPublicationId);
609 cz_utils.log_report(pkg_name, 'CREATE_PUBLICATION_DATA', null, ERRBUF, fnd_log.LEVEL_ERROR);
610 -- RAISE SOURCE_UI_NOT_FOUND;
611 when others then
612 CLOSE publications_cur;
613 ERRNO := czError;
614 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
615 cz_utils.log_report(pkg_name, 'CREATE_PUBLICATION_DATA', null, ERRBUF, fnd_log.LEVEL_ERROR);
616 END;
617
618 -- insert
619 SELECT CZ_MODEL_PUBLICATIONS_S.NEXTVAL into lPublicationId from dual;
620 EXECUTE IMMEDIATE
621 ' INSERT INTO cz_model_publications (PUBLICATION_ID ' ||
622 ' ,MODEL_ID ' ||
623 ' ,SERVER_ID ' ||
624 ' ,ORGANIZATION_ID ' ||
625 ' ,TOP_ITEM_ID ' ||
626 ' ,PRODUCT_KEY ' ||
627 ' ,PUBLICATION_MODE ' ||
628 ' ,UI_DEF_ID ' ||
629 ' ,UI_STYLE ' ||
630 ' ,APPLICABLE_FROM ' ||
631 ' ,APPLICABLE_UNTIL ' ||
632 ' ,EXPORT_STATUS ' ||
633 ' ,MODEL_PERSISTENT_ID ' ||
634 ' ,DELETED_FLAG ' ||
635 ' ,MODEL_LAST_STRUCT_UPDATE ' ||
636 ' ,MODEL_LAST_LOGIC_UPDATE ' ||
637 ' ,MODEL_LAST_UPDATED ' ||
638 ' ,CREATION_DATE ' ||
639 ' ,LAST_UPDATE_DATE ' ||
640 ' ,CREATED_BY ' ||
641 ' ,LAST_UPDATED_BY ' ||
642 ' ,SOURCE_TARGET_FLAG ' ||
643 ' ,REMOTE_PUBLICATION_ID ' ||
644 ' ) ' ||
645 ' VALUES (SELECT lPublicationId ' ||
646 ' ,nvl(SOURCE_MODEL_ID,n_source_model_id) ' ||
647 ' ,p_target_server_id ' ||
648 ' ,ORGANIZATION_ID ' ||
649 ' ,TOP_ITEM_ID ' ||
650 ' ,PRODUCT_KEY ' ||
651 ' ,PUBLICATION_MODE ' ||
652 ' ,nvl(SOURCE_UI_DEF_ID,n_source_ui_def_id) ' ||
653 ' ,UI_STYLE ' ||
654 ' ,APPLICABLE_FROM ' ||
655 ' ,APPLICABLE_UNTIL ' ||
656 ' ,EXPORT_STATUS ' ||
657 ' ,MODEL_PERSISTENT_ID ' ||
658 ' ,DELETED_FLAG ' ||
659 ' ,MODEL_LAST_STRUCT_UPDATE ' ||
660 ' ,MODEL_LAST_LOGIC_UPDATE ' ||
661 ' ,MODEL_LAST_UPDATED ' ||
662 ' ,CREATION_DATE ' ||
663 ' ,LAST_UPDATE_DATE ' ||
664 ' ,CREATED_BY ' ||
665 ' ,LAST_UPDATED_BY ' ||
666 ' ,''S'' ' ||
667 ' ,PUBLICATION_ID ' ||
668 ' FROM CZ_MODEL_PUBLICATIONS' || lLinkName || ' remote ' ||
669 ' WHERE cz_model_publications.remote_publication_id = remote.publication_id ' ||
670 ' AND cz_model_publications.deleted_flag = ''1'' )';
671
672 -- insert into other publication request tables
673 EXECUTE IMMEDIATE
674 ' INSERT INTO CZ_PB_LANGUAGES( PUBLICATION_ID, LANGUAGE) VALUES (SELECT v_pub_tbl(i),language FROM CZ_PB_LANGUAGES'|| lLinkName || 'r ' ||
675 ' WHERE r.publication_id = lPublicationId';
676
677 EXECUTE IMMEDIATE
678 ' INSERT INTO CZ_PB_CLIENT_APPS( PUBLICATION_ID, FND_APPLICATION_ID, APPLICATION_SHORT_NAME, NOTES) VALUES ' ||
679 ' (SELECT v_pub_tbl(i),FND_APPLICATION_ID, APPLICATION_SHORT_NAME, NOTES FROM CZ_PB_CLIENT_APPS'|| lLinkName || 'r ' ||
680 ' WHERE r.publication_id = lPublicationId';
681
682 EXECUTE IMMEDIATE
683 ' INSERT INTO CZ_PUBLICATION_USAGES( PUBLICATION_ID, USAGE_ID) ' ||
684 ' VALUES (SELECT v_pub_tbl(i),usage_id FROM Z_PUBLICATION_USAGES'|| lLinkName || 'r ' ||
685 ' WHERE r.publication_id = lPublicationId';
686
687 END LOOP;
688 CLOSE publications_cur;
689 END;
690 END IF;
691
692 -- Update remote_publication_id on target
693 EXECUTE IMMEDIATE
694 ' update cz_model_publications'||lLinkName || ' t ' ||
695 ' set remote_publication_id = (select publication_id from cz_model_publications' ||
696 ' where remote_publication_id = t.publication_id' ||
697 ' and deleted_flag = ''0'')' ||
698 ' and deleted_flag = ''0'' ';
699
700 EXCEPTION
701 WHEN OTHERS THEN
702 RAISE CREATE_PUBLICATION_ERROR;
703 /*
704 ERRNO := czError;
705 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
706 xERROR := CZ_UTILS.REPORT(ERRBUF,1,'CZ_PUBL_SYNC_CRASH.CREATE_PUBLICATION_DATA',11276);
707 */
708 END CREATE_PUBLICATION_DATA ;
709 ------------------------------------------------------------------------------------------
710 /* Redo item/ property sequences */
711 PROCEDURE redo_sequences (p_target_server IN NUMBER)
712 IS
713
714 cursor c1 is
715 select value from CZ_DB_SETTINGS
716 where setting_id='OracleSequenceIncr' and section_name='SCHEMA';
717 r_incr number;
718 r_item_val number;
719 r_property_val number;
720 r_item_type_val number;
721 r_node_val number;
722 r_ui_node_val number;
723 incr_val number;
724 item_val number;
725 property_val number;
726 item_type_val number;
727 ps_node_val number;
728 ui_node_val number;
729
730 lLinkName CZ_SERVERS.fndnam_link_name%type;
731 WRONG_INCR exception;
732
733 BEGIN
734
735 EXECUTE IMMEDIATE
736 ' SELECT fndnam_link_name FROM CZ_SERVERS WHERE server_local_id = :1'
737 INTO lLinkName
738 USING p_target_server ;
739
740 IF (lLinkName <> NULL) THEN
741 lLinkName := '@' || LTRIM(RTRIM(lLinkName));
742 END IF;
743
744 OPEN c1;
745 FETCH c1 INTO incr_val;
746 IF (c1%notfound) THEN
747 incr_val := 20;
748 raise WRONG_INCR;
749 END IF;
750 CLOSE c1;
751
752 EXECUTE IMMEDIATE
753 'SELECT greatest (max(l.item_id),max(r.item_id)) INTO ' || item_val ||
754 ' FROM cz_item_masters' || lLinkName || ' r, cz_item_masters l';
755
756 EXECUTE IMMEDIATE
757 'SELECT greatest (max(l.item_type_id),max(r.item_type_id)) INTO ' || item_type_val ||
758 ' FROM cz_item_types' || lLinkName || ' r, cz_item_masters l' ;
759
760 EXECUTE IMMEDIATE
761 'SELECT greatest (max(l.property_id),max(r.property_id)) INTO ' || property_val ||
762 ' FROM cz_properties' || lLinkName || ' r, cz_item_masters l' ;
763
764 EXECUTE IMMEDIATE
765 'SELECT greatest (max(l.ps_node_id),max(r.ps_node_id)) INTO ' || ps_node_val ||
766 ' FROM cz_ps_nodes' || lLinkName || ' r, cz_item_masters l' ;
767
768 EXECUTE IMMEDIATE
769 'SELECT greatest (max(l.ui_node_id),max(r.ui_node_id)) INTO ' || ui_node_val ||
770 ' FROM cz_ui_nodes' || lLinkName || ' r, cz_item_masters l' ;
771
772 /* drop sequences */
773 EXECUTE IMMEDIATE 'DROP SEQUENCE cz_ps_nodes_s';
774 EXECUTE IMMEDIATE 'DROP SEQUENCE cz_ui_nodes_s';
775 EXECUTE IMMEDIATE 'DROP SEQUENCE cz_item_masters_s';
776 EXECUTE IMMEDIATE 'DROP SEQUENCE cz_item_types_s';
777 EXECUTE IMMEDIATE 'DROP SEQUENCE cz_properties_s';
778
779 -- create sequences
780 EXECUTE IMMEDIATE 'CREATE SEQUENCE cz_item_masters_s START WITH '|| item_val+incr_val ||
781 ' INCREMENT BY '|| incr_val || ' NOCACHE';
782 EXECUTE IMMEDIATE 'CREATE SEQUENCE cz_item_types_s START WITH '|| item_type_val+incr_val ||
783 ' INCREMENT BY '|| incr_val || ' NOCACHE';
784 EXECUTE IMMEDIATE 'CREATE SEQUENCE cz_ps_nodes_s START WITH '|| ps_node_val+incr_val ||
785 ' INCREMENT BY '|| incr_val || ' NOCACHE';
786 EXECUTE IMMEDIATE 'CREATE SEQUENCE cz_ui_nodes_s START WITH '|| ui_node_val+incr_val ||
787 ' INCREMENT BY '|| incr_val || ' NOCACHE';
788 EXECUTE IMMEDIATE 'CREATE SEQUENCE cz_properties_s START WITH '|| property_val+incr_val ||
789 ' INCREMENT BY '|| incr_val || ' NOCACHE';
790 EXCEPTION
791 WHEN OTHERS THEN
792 RAISE REDO_SEQUENCE_ERROR;
793 /*
794 ERRNO := czError;
795 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
796 xERROR := CZ_UTILS.REPORT(ERRBUF,1,'CZ_PUBL_SYNC.REDO_SEQUENCES',11276);
797 */
798 END;
799 ------------------------------------------------------------------------------------------
800 PROCEDURE republish_models (ERRNO IN OUT NOCOPY NUMBER,
801 ERRBUF IN OUT NOCOPY VARCHAR2,
802 p_source_server IN NUMBER,
803 okCount IN OUT NOCOPY NUMBER,
804 errCount IN OUT NOCOPY NUMBER,
805 commitYesNo IN NUMBER DEFAULT 0)
806 IS
807 TYPE t_publ_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
808 v_pub_tbl t_publ_tbl;
809 v_remote_pub_tbl t_publ_tbl;
810 v_publication_id CZ_MODEL_PUBLICATIONS.publication_id%type;
811
812 lLinkName CZ_SERVERS.fndnam_link_name%type;
813 x_run_id CZ_XFR_RUN_INFOS.run_id%type;
814 x_status CZ_MODEL_PUBLICATIONS.export_status%type;
815
816 ok_count NUMBER := 0;
817 err_count NUMBER := 0;
818 lStartDate DATE := sysdate;
819 lEndDate DATE := sysdate;
820 BEGIN
821
822 SELECT fndnam_link_name INTO lLinkName FROM CZ_SERVERS
823 WHERE server_local_id = p_source_server;
824
825 IF (lLinkName <> NULL) THEN
826 lLinkName := '@' || LTRIM(RTRIM(lLinkName));
827 END IF;
828
829 SELECT publication_id, remote_publication_id
830 BULK COLLECT INTO v_pub_tbl, v_remote_pub_tbl
831 from cz_model_publications
832 where deleted_flag = '0';
833
834 IF (v_pub_tbl.COUNT > 0) THEN
835 FOR i IN v_pub_tbl.FIRST..v_pub_tbl.LAST
836 LOOP
837 UPDATE CZ_MODEL_PUBLICATIONS SET export_status = PUBLICATION_PENDING
838 WHERE export_status = PUBLICATION_OK
839 AND deleted_flag = '0';
840
841 x_status := NULL;
842 IF (commitYesNo = 0 ) THEN
843 CZ_PB_MGR.REPUBLISH_MODEL(v_pub_tbl(i),lStartDate,lEndDate,x_run_id,x_status);
844 IF (x_status <> PUBLICATION_OK) THEN
845 err_count := err_count + 1;
846 ERRBUF := CZ_UTILS.GET_TEXT('CZ_REPUBLISH_ERROR','PUBL_ID',v_pub_tbl(i),'ERROR',SQLERRM);
847 cz_utils.log_report(pkg_name, 'REPUBLISH_MODELS', null, ERRBUF, fnd_log.LEVEL_ERROR);
848 ELSE
849 ok_count := ok_count + 1;
850 END IF;
851 ELSE
852 -- Republishing :Publication Id
853 ERRBUF := CZ_UTILS.GET_TEXT('CZ_REPUBLISH','PUBL_ID',v_pub_tbl(i));
854 cz_utils.log_report(pkg_name, 'REPUBLISH_MODELS', null, ERRBUF, fnd_log.LEVEL_ERROR);
855 END IF;
856 END LOOP;
857 END IF;
858 okCount := ok_count;
859 errCount := err_count;
860 EXCEPTION
861 WHEN OTHERS THEN
862 okCount := ok_count;
863 errCount := err_count;
864 RAISE REPUBLISH_ERROR;
865 END;
866 ------------------------------------------------------------------------------------------
867 PROCEDURE report_results (p_runId IN NUMBER,
868 p_programName IN VARCHAR2,
869 p_disposition IN VARCHAR2,
870 p_rec_status IN VARCHAR2,
871 p_rec_count IN NUMBER)
872 IS
873 BEGIN
874 INSERT INTO CZ_XFR_RUN_RESULTS (RUN_ID,IMP_TABLE,DISPOSITION,REC_STATUS,RECORDS)
875 VALUES(p_runId,p_programName,p_disposition,p_rec_status,p_rec_count);
876 COMMIT;
877 EXCEPTION
878 WHEN OTHERS THEN
879 RAISE REPORT_RESULTS_ERROR;
880 END;
881 ------------------------------------------------------------------------------------------
882
883 /* Validate if the serverId is the right source server */
884
885 FUNCTION ValidateSource(p_target_server_id IN NUMBER)
886 RETURN BOOLEAN
887 IS
888
889 lLinkName CZ_SERVERS.FNDNAM_LINK_NAME%TYPE;
890 BEGIN
891
892 if (checkIfSource(p_target_server_id)) then
893 -- if(validateServer(p_target_server_id)) then
894 return true;
895 -- else
896 -- return false;
897 -- end if;
898 else
899 return false;
900 end if;
901 EXCEPTION
902 WHEN OTHERS THEN
903 RAISE INCORRECT_SOURCE;
904 /*
905 ERRNO := czError;
906 ERRBUF := CZ_UTILS.GET_TEXT('CZ_INCORRECT_SOURCE');
907 xERROR := CZ_UTILS.REPORT(ERRBUF,1,'CZ_PUBL_SYNC_CRASH.ValidateSource',11276);
908 */
909 END ValidateSource;
910
911 ------------------------------------------------------------------------------------------
912
913 /* Sync source instance with a single target instance */
914
915 PROCEDURE SYNC_SINGLE_SOURCE_CP(ERRNO IN OUT NOCOPY NUMBER,
916 ERRBUF IN OUT NOCOPY VARCHAR2,
917 p_run_id IN OUT NOCOPY NUMBER, -- DEFAULT NULL
918 p_target_server_id IN NUMBER)
919 IS
920 -- xERROR BOOLEAN := FALSE;
921 runId CZ_XFR_RUN_INFOS.RUN_ID%type;
922 okCount NUMBER := 0;
923 errCount NUMBER := 0;
924 lServerName CZ_SERVERS.local_name%type;
925 lLinkName CZ_SERVERS.fndnam_link_name%type;
926
927 BEGIN
928 SET_DBMS_INFO(pbSourceCrash);
929
930 SELECT local_name,fndnam_link_name INTO lServerName, lLinkName
931 FROM CZ_SERVERS
932 WHERE server_local_id = p_target_server_id;
933
934 IF (ValidateSource(p_target_server_id)) THEN
935 BEGIN
936 -- get new run id if not there and insert record in cz_xfr_run_infos
937 IF (p_run_id = NULL) THEN
938 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO runId FROM DUAL;
939 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
940 SELECT runId,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
941 (SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID = runId);
942 COMMIT;
943 ELSE
944 -- should have been inserted by SYNC_ALL_SOURCE_CP
945 runId := p_run_id;
946 END IF;
947 -- delete and recreate publication data
948 DELETE_PUBLICATION_DATA (p_target_server_id);
949 CREATE_PUBLICATION_DATA (errBuf,p_target_server_id,okCount);
950 REDO_SEQUENCES(p_target_server_id);
951 REPORT_RESULTS(runId,pbSourceCrash,'I',czOk,okCount);
952 END;
953 ELSE
954 RAISE INCORRECT_SOURCE;
955 END IF;
956 RESET_DBMS_INFO;
957 EXCEPTION
958 WHEN NO_DATA_FOUND THEN
959 ROLLBACK;
960 ERRNO := czError;
961 ERRBUF := CZ_UTILS.GET_TEXT(sqlerrm);
962 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
963 RESET_DBMS_INFO;
964 WHEN INCORRECT_SOURCE THEN
965 ROLLBACK;
966 ERRNO := czError;
967 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SERVER_NOT_SOURCE','SERVERNAME',lServerName);
968 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
969 RESET_DBMS_INFO;
970 WHEN CZ_SYNC_ERROR THEN
971 ROLLBACK;
972 ERRNO := czError;
973 ERRBUF := CZ_UTILS.GET_TEXT('CZ_GENERAL_ERROR', 'ERRORTEXT', SQLERRM);
974 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
975 RESET_DBMS_INFO;
976 WHEN SERVER_NOT_FOUND THEN
977 ROLLBACK;
978 ERRNO := czError;
979 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_NOT_FOUND','SERVERNAME',lServerName);
980 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
981 RESET_DBMS_INFO;
982 WHEN DB_LINK_DOWN THEN
983 ROLLBACK;
984 ERRNO := czError;
985 ERRBUF := CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','LINK_NAME', lLinkName);
986 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
987 RESET_DBMS_INFO;
988 WHEN DB_TNS_INCORRECT THEN
989 ROLLBACK;
990 ERRNO := czError;
991 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_MISMATCH','SERVERNAME',lServerName);
992 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
993 RESET_DBMS_INFO;
994 WHEN VALIDATE_SERVER_ERROR THEN
995 ROLLBACK;
996 ERRNO := czError;
997 ERRBUF := CZ_UTILS.GET_TEXT(SQLERRM);
998 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
999 RESET_DBMS_INFO;
1000 WHEN DELETE_PUBLICATION_ERROR THEN
1001 ROLLBACK;
1002 ERRNO := czError;
1003 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
1004 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1005 RESET_DBMS_INFO;
1006 /* WHEN SOURCE_MODEL_NOT_FOUND THEN
1007 ROLLBACK;
1008 ERRNO := czError;
1009 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_MODEL_NOT_FOUND', 'MODELID', n_Source_Model_Id);
1010 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1011 RESET_DBMS_INFO;
1012 WHEN SOURCE_UI_NOT_FOUND THEN
1013 ROLLBACK;
1014 ERRNO := czError;
1015 ERRBUF := CZ_UTILS.GET_TEXT('CZ_UI_MODEL_NOT_FOUND', 'MODELID', n_Source_Model_Id);
1016 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1017 RESET_DBMS_INFO;
1018 */
1019 WHEN CREATE_PUBLICATION_ERROR THEN
1020 ROLLBACK;
1021 ERRNO := czError;
1022 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
1023 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1024 RESET_DBMS_INFO;
1025 WHEN REDO_SEQUENCE_ERROR THEN
1026 ROLLBACK;
1027 ERRNO := czError;
1028 ERRBUF := CZ_UTILS.GET_TEXT('CZ_GENERAL_ERROR','ERRORTEXT', SQLERRM);
1029 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1030 RESET_DBMS_INFO;
1031 /* WHEN WRONG_INCR THEN
1032 ERRNO := czWarning;
1033 ERRBUF := CZ_UTILS.GET_TEXT('CZ_NO_SEQ_INCREMENT_VAL');
1034 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1035 RESET_DBMS_INFO;
1036 */
1037 WHEN REPORT_RESULTS_ERROR THEN
1038 ERRNO := czWarning;
1039 ERRBUF := CZ_UTILS.GET_TEXT('CZ_GENERAL_ERROR','ERRORTEXT', SQLERRM);
1040 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1041 RESET_DBMS_INFO;
1042 WHEN OTHERS THEN
1043 --'Unable to continue because of %ERRORTEXT'
1044 ROLLBACK;
1045 ERRNO := czError;
1046 ERRBUF := CZ_UTILS.GET_TEXT('CZ_GENERAL_ERROR', 'ERRORTEXT', SQLERRM);
1047 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_UNEXPECTED);
1048 RESET_DBMS_INFO;
1049 END SYNC_SINGLE_SOURCE_CP;
1050 ------------------------------------------------------------------------------------------
1051
1052 /* Sync source with all target instances */
1053 PROCEDURE SYNC_ALL_SOURCE_CP (ERRNO IN OUT NOCOPY NUMBER,
1054 ERRBUF IN OUT NOCOPY VARCHAR2,
1055 p_run_id IN OUT NOCOPY NUMBER)
1056 IS
1057 l_server_id CZ_SERVERS.server_local_id%type;
1058 l_server_name CZ_SERVERS.local_name%type;
1059 -- xError boolean := false;
1060 CURSOR c_get_all_remote_servers IS
1061 SELECT server_local_id, local_name FROM CZ_SERVERS;
1062 BEGIN
1063 -- get new run id if not there and insert record in cz_xfr_run_infos
1064 IF (p_run_id = NULL) THEN
1065 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO p_run_id FROM DUAL;
1066 END IF;
1067 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
1068 SELECT p_run_id,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
1069 (SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=p_run_id);
1070 COMMIT;
1071 OPEN c_get_all_remote_servers;
1072 LOOP
1073 BEGIN
1074 FETCH c_get_all_remote_servers INTO l_server_id, l_server_name;
1075 EXIT WHEN c_get_all_remote_servers%NOTFOUND;
1076 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_SYNC','SERVERNAME',l_server_name);
1077 CZ_PUBL_SYNC_CRASH.SYNC_SINGLE_SOURCE_CP(ERRNO,ERRBUF,p_run_id,l_server_id);
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 ERRNO := 2;
1081 ERRBUF := SQLERRM;
1082 cz_utils.log_report(pkg_name, 'SYNC_ALL_SOURCE_CP', null, ERRBUF, fnd_log.LEVEL_UNEXPECTED);
1083 END;
1084 END LOOP;
1085 CLOSE c_get_all_remote_servers;
1086
1087 END SYNC_ALL_SOURCE_CP;
1088 ------------------------------------------------------------------------------------------
1089 PROCEDURE SYNC_TARGET_CP(ERRNO IN OUT NOCOPY NUMBER,
1090 ERRBUF IN OUT NOCOPY VARCHAR2,
1091 p_run_id IN OUT NOCOPY NUMBER,
1092 p_source_server_id IN NUMBER,
1093 p_date IN DATE,
1094 p_commitYesNo IN NUMBER DEFAULT 0)
1095 IS
1096 okCount NUMBER := 0;
1097 errCount NUMBER := 0;
1098 lServerName CZ_SERVERS.local_name%type;
1099 lLinkName CZ_SERVERS.fndnam_link_name%type;
1100 inSourceServerId CZ_SERVERS.server_local_id%type;
1101 inDate DATE;
1102 inCommitYesNo NUMBER;
1103 runId NUMBER;
1104 BEGIN
1105
1106
1107 SET_DBMS_INFO(pbTargetCrash);
1108
1109 -- inSourceServerId := p_source_server_id;
1110 inDate := p_date;
1111 inCommitYesNo := p_commitYesNo;
1112
1113 SELECT local_name,fndnam_link_name INTO lServerName, lLinkName
1114 FROM CZ_SERVERS
1115 WHERE server_local_id = p_source_server_id;
1116
1117 -- Check if source server flag is set correctly
1118 IF (ValidateSource(p_source_server_id)) THEN
1119 -- IF (ValidateTarget(p_source_server_id)) THEN
1120 BEGIN
1121 -- get new run id if not there and insert record in cz_xfr_run_infos
1122 IF (p_run_id = NULL) THEN
1123 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO runId FROM DUAL;
1124 ELSE
1125 runId := p_run_id;
1126 END IF;
1127 INSERT INTO CZ_XFR_RUN_INFOS (RUN_ID,STARTED,LAST_ACTIVITY,COMPLETED)
1128 SELECT runId,SYSDATE,SYSDATE,'0' FROM DUAL WHERE NOT EXISTS
1129 (SELECT 1 FROM CZ_XFR_RUN_INFOS WHERE RUN_ID=runId);
1130 IF (inCommitYesNo = 0) THEN
1131 COMMIT;
1132 END IF;
1133 -- Delete all deleted publications from source and target
1134 DELETE_DELETED_PUBLICATIONS(p_source_server_id,inDate);
1135 -- republish
1136 REPUBLISH_MODELS(Errno,ErrBuf,p_source_server_id, okCount, errCount,inCommitYesNo);
1137 -- log results -- not sure what to use for Disposition ??
1138 REPORT_RESULTS(runId,pbTargetCrash,'I',czOk,okCount);
1139 REPORT_RESULTS(runId,pbTargetCrash,'I',czError,errCount);
1140 END;
1141 /* ELSE
1142 ERRNO := czError;
1143 -- This instance is not the source for the selected Target server
1144 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SERVER_NOT_SOURCE');
1145 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1146 END IF;
1147 **/
1148 ELSE
1149 ERRNO := czError;
1150 -- The selected Target's tns details do not match with that in CZ_SERVERS
1151 ERRBUF := CZ_UTILS.GET_TEXT('CZ_INCORRECT_TARGET');
1152 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1153 END IF;
1154 IF (inCommitYesNo = 0) THEN
1155 COMMIT;
1156 END IF;
1157 RESET_DBMS_INFO;
1158 EXCEPTION
1159 WHEN NO_DATA_FOUND THEN
1160 ROLLBACK;
1161 ERRNO := czError;
1162 ERRBUF := CZ_UTILS.GET_TEXT(sqlerrm);
1163 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_ERROR);
1164 RESET_DBMS_INFO;
1165 WHEN INCORRECT_SOURCE THEN
1166 ROLLBACK;
1167 ERRNO := czError;
1168 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SERVER_NOT_SOURCE','SERVERNAME',lServerName);
1169 cz_utils.log_report(pkg_name, 'CHECK_IF_SOURCE', null, ERRBUF, fnd_log.LEVEL_ERROR);
1170 RESET_DBMS_INFO;
1171 WHEN CZ_SYNC_ERROR THEN
1172 ROLLBACK;
1173 ERRNO := czError;
1174 ERRBUF := CZ_UTILS.GET_TEXT('CZ_GENERAL_ERROR', 'ERRORTEXT', SQLERRM);
1175 cz_utils.log_report(pkg_name, 'CHECK_IF_SOURCE', null, ERRBUF, fnd_log.LEVEL_ERROR);
1176 RESET_DBMS_INFO;
1177 WHEN SERVER_NOT_FOUND THEN
1178 ROLLBACK;
1179 ERRNO := czError;
1180 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_NOT_FOUND','SERVERNAME',lServerName);
1181 cz_utils.log_report(pkg_name, 'VALIDATE_SERVER', null, ERRBUF, fnd_log.LEVEL_ERROR);
1182 RESET_DBMS_INFO;
1183 WHEN DB_LINK_DOWN THEN
1184 ROLLBACK;
1185 ERRNO := czError;
1186 ERRBUF := CZ_UTILS.GET_TEXT('CZ_DB_LINK_IS_DOWN','LINK_NAME', lLinkName);
1187 cz_utils.log_report(pkg_name, 'VALIDATE_SERVER', null, ERRBUF, fnd_log.LEVEL_ERROR);
1188 RESET_DBMS_INFO;
1189 WHEN DB_TNS_INCORRECT THEN
1190 ROLLBACK;
1191 ERRNO := czError;
1192 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_MISMATCH','SERVERNAME',lServerName);
1193 cz_utils.log_report(pkg_name, 'VALIDATE_SERVER', null, ERRBUF, fnd_log.LEVEL_ERROR);
1194 RESET_DBMS_INFO;
1195 WHEN VALIDATE_SERVER_ERROR THEN
1196 ROLLBACK;
1197 ERRNO := czError;
1198 ERRBUF := CZ_UTILS.GET_TEXT(SQLERRM);
1199 cz_utils.log_report(pkg_name, 'VALIDATE_SERVER', null, ERRBUF, fnd_log.LEVEL_ERROR);
1200 RESET_DBMS_INFO;
1201 WHEN TNS_INCORRECT THEN -- target validation
1202 ROLLBACK;
1203 ERRNO := czError;
1204 ERRBUF := CZ_UTILS.GET_TEXT('CZ_INCORRECT_TARGET','SERVERNAME',lServerName);
1205 cz_utils.log_report(pkg_name, 'VALIDATETARGET', null, ERRBUF, fnd_log.LEVEL_ERROR);
1206 RESET_DBMS_INFO;
1207 WHEN DELETE_DEL_PUBLICATION_ERROR THEN
1208 ROLLBACK;
1209 ERRNO := czError;
1210 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
1211 cz_utils.log_report(pkg_name, 'DELETE_DELETED_PUBLICATIONS', null, ERRBUF, fnd_log.LEVEL_ERROR);
1212 RESET_DBMS_INFO;
1213 WHEN DELETE_PUBLICATION_ERROR THEN
1214 ROLLBACK;
1215 ERRNO := czError;
1216 ERRBUF := CZ_UTILS.GET_TEXT('SQLERRM');
1217 cz_utils.log_report(pkg_name, 'DELETE_DELETED_PUBLICATIONS', null, ERRBUF, fnd_log.LEVEL_ERROR);
1218 RESET_DBMS_INFO;
1219 WHEN REPUBLISH_ERROR THEN
1220 ROLLBACK;
1221 ERRNO := czError;
1222 ERRBUF := CZ_UTILS.GET_TEXT('REPUBLISH_ERROR');
1223 cz_utils.log_report(pkg_name, 'REPUBLISH_MODELS', null, ERRBUF, fnd_log.LEVEL_ERROR);
1224 RESET_DBMS_INFO;
1225 WHEN REPORT_RESULTS_ERROR THEN
1226 ERRNO := czWarning;
1227 ERRBUF := CZ_UTILS.GET_TEXT('CZ_GENERAL_ERROR','ERRORTEXT', SQLERRM);
1228 cz_utils.log_report(pkg_name, 'REDO_SEQUENCES', null, ERRBUF, fnd_log.LEVEL_ERROR);
1229 RESET_DBMS_INFO;
1230 WHEN OTHERS THEN
1231 --'Unable to continue because of %ERRORTEXT'
1232 ROLLBACK;
1233 ERRNO := czError;
1234 ERRBUF := CZ_UTILS.GET_TEXT('CZ_GENERAL_ERROR', 'ERRORTEXT', SQLERRM);
1235 cz_utils.log_report(pkg_name, 'SYNC_TARGET_CP', null, ERRBUF, fnd_log.LEVEL_UNEXPECTED);
1236 RESET_DBMS_INFO;
1237 END;
1238 ------------------------------------------------------------------------------------------
1239
1240 /* Validate if the serverId is a valid target */
1241
1242 FUNCTION ValidateTarget(p_server_id IN NUMBER)
1243 RETURN BOOLEAN
1244 IS
1245 lHost CZ_SERVERS.hostname%type;
1246 lSid CZ_SERVERS.instance_name%type;
1247 lLinkName CZ_SERVERS.fndnam_link_name%type;
1248 lHostName CZ_SERVERS.hostname%type;
1249 lServerName CZ_SERVERS.instance_name%type;
1250 BEGIN
1251
1252 SELECT fndnam_link_name INTO lLinkName
1253 FROM CZ_SERVERS
1254 WHERE server_local_id = p_server_id;
1255
1256 IF (lLinkName <> NULL) THEN
1257 lLinkName := '@' || lLinkName;
1258 END IF;
1259
1260 EXECUTE IMMEDIATE
1261 ' SELECT hostname, instance_name INTO ' || lHost || ',' || lSid ||
1262 ' FROM CZ_SERVERS'|| lLinkName ||
1263 ' WHERE source_server_flag = ''1''';
1264
1265 SELECT INSTANCE_NAME, HOST_NAME INTO lServerName,lHostName from v$instance;
1266 IF ((upper(lSid) = upper(lServerName)) and (upper(lHost) = upper(lHostName))) THEN
1267 return true;
1268 ELSE
1269 /* ERRNO := czError;
1270 ERRBUF := CZ_UTILS.GET_TEXT('CZ_SOURCE_MISMATCH','SERVERNAME',lServerName,'DATABASE',lSid);
1271 return false; */
1272 RAISE TNS_INCORRECT;
1273 END IF;
1274
1275 EXCEPTION
1276 WHEN NO_DATA_FOUND THEN
1277 cz_utils.log_report(pkg_name, 'SYNC_ALL_SOURCE_CP', null, ERRBUF, fnd_log.LEVEL_UNEXPECTED);
1278 END;
1279 ------------------------------------------------------------------------------------------
1280 PROCEDURE SYNC_TARGET_LIST_CP(ERRNO IN OUT NOCOPY NUMBER,
1281 ERRBUF IN OUT NOCOPY VARCHAR2,
1282 p_run_id IN OUT NOCOPY NUMBER,
1283 p_source_server_id IN NUMBER,
1284 p_date IN DATE)
1285 IS
1286 okCount NUMBER := 0;
1287 errCount NUMBER := 0;
1288 lServerName CZ_SERVERS.local_name%type;
1289 BEGIN
1290 SYNC_TARGET_CP(ERRNO,ERRBUF,p_run_id,p_source_server_id,p_date,1);
1291 END;
1292 ------------------------------------------------------------------------------------------
1293 END CZ_PUBL_SYNC_CRASH;