1 PACKAGE BODY cz_pb_sync_util AS
2 /* $Header: czcloutb.pls 120.4 2006/04/27 02:59:37 kdande ship $ */
3
4
5 -----variable declarations
6 TYPE ref_cursor IS REF CURSOR;
7
8 -------------------------------------------------
9 ---function that returns the run id
10 FUNCTION get_run_id
11 RETURN NUMBER
12 IS
13
14 v_run_id NUMBER := 0;
15
16 BEGIN
17 SELECT cz_xfr_run_infos_s.NEXTVAL
18 INTO v_run_id
19 FROM DUAL;
20 RETURN v_run_id;
21 EXCEPTION
22 WHEN OTHERS THEN
23 RETURN v_run_id;
24 END;
25
26 ---------------------------------------------------
27 --------------------procedure to log errors during the sync process
28 PROCEDURE log_pb_sync_errors(p_msg_tbl IN message_list,p_run_id IN NUMBER)
29 AS
30
31 v_message VARCHAR2(2000) := NULL;
32
33 BEGIN
34 IF (p_msg_tbl.COUNT > 0) THEN
35 FOR I IN p_msg_tbl.FIRST..p_msg_tbl.LAST
36 LOOP
37 cz_utils.log_report(p_msg_tbl(i).called_proc, null, p_msg_tbl(i).sql_code,
38 p_msg_tbl(i).msg_text, fnd_log.LEVEL_ERROR);
39 END LOOP;
40 END IF;
41 EXCEPTION
42 WHEN OTHERS THEN
43 RAISE;
44 END log_pb_sync_errors;
45
46 ---------------------------------------------------
47 ------function that retrieves the db link name from cz_servers
48 FUNCTION retrieve_link_name(p_tgt_server_id cz_servers.server_local_id%TYPE)
49 RETURN VARCHAR2
50 IS
51
52 v_db_link_name cz_servers.fndnam_link_name%TYPE := NULL;
53
54 BEGIN
55 IF (p_tgt_server_id IS NOT NULL) THEN
56 SELECT fndnam_link_name
57 INTO v_db_link_name
58 FROM cz_servers
59 WHERE cz_servers.server_local_id = p_tgt_server_id;
60 v_db_link_name := '@'||v_db_link_name;
61 END IF;
62 RETURN v_db_link_name;
63 EXCEPTION
64 WHEN NO_DATA_FOUND THEN
65 RETURN v_db_link_name;
66 END retrieve_link_name;
67
68 ---------------------------------------------------------
69 ----procedure that retrieves the target server id from cz_servers
70 FUNCTION get_target_instance_id(p_target_instance IN VARCHAR2)
71 RETURN NUMBER
72 IS
73
74 v_tgt_server_id cz_servers.server_local_id%TYPE;
75
76 BEGIN
77 v_tgt_server_id := 0;
78 SELECT server_local_id
79 INTO v_tgt_server_id
80 FROM cz_servers
81 WHERE UPPER(cz_servers.local_name) = UPPER(LTRIM(RTRIM(p_target_instance)));
82 RETURN v_tgt_server_id ;
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 RETURN v_tgt_server_id ;
86 WHEN OTHERS THEN
87 RETURN v_tgt_server_id ;
88 END get_target_instance_id;
89
90 ------------------------------------------------------------
91 -----function that checks if the database lnk is active
92 FUNCTION check_db_link(p_db_link_name IN cz_servers.fndnam_link_name%TYPE)
93 RETURN BOOLEAN
94 IS
95
96 gl_ref_cursor REF_CURSOR;
97 l_active_count NUMBER := 0;
98 v_err_message VARCHAR2(2000);
99
100 BEGIN
101 IF ( (p_db_link_name IS NULL) OR (p_db_link_name = '@') ) THEN
102 v_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_LINK_NAME_IS_NULL');
103 RETURN FALSE;
104 ELSE
105 ----check if the link is up and active
106 OPEN gl_ref_cursor FOR 'SELECT COUNT(*) FROM cz_db_settings'||p_db_link_name ;
107 LOOP
108 FETCH gl_ref_cursor INTO l_active_count;
109 EXIT WHEN gl_ref_cursor%NOTFOUND;
110 END LOOP;
111 CLOSE gl_ref_cursor;
112
113 IF (l_active_count = 0) THEN
114 v_err_message := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_LINK_NOT_ACTIVE');
115 RETURN FALSE;
116 END IF;
117 END IF;
118 RETURN TRUE;
119 EXCEPTION
120 WHEN OTHERS THEN
121 RETURN FALSE;
122 END check_db_link;
123
124 -------------------------------------------------------------
125 ------function that validates the schema versions on the source
126 ------and the cloned instances
127 ------
128 FUNCTION validate_schema(target_server_id cz_servers.server_local_id%TYPE)
129 RETURN BOOLEAN
130 IS
131
132 v_source_major_version cz_db_settings.value%TYPE;
133 v_source_minor_version cz_db_settings.value%TYPE;
134 v_target_major_version cz_db_settings.value%TYPE;
135 v_target_minor_version cz_db_settings.value%TYPE;
136 db_schema_compare_cur ref_cursor ;
137 v_db_link cz_servers.fndnam_link_name%TYPE;
138 v_validate_schema_flg BOOLEAN := TRUE;
139 v_count PLS_INTEGER := 0;
140
141 BEGIN
142 v_db_link := retrieve_link_name(target_server_id);
143
144 IF ( NOT cz_pb_sync_util.check_db_link(v_db_link) ) THEN
145 RETURN FALSE;
146 END IF;
147
148 IF LTRIM(RTRIM(v_db_link)) = '@' THEN
149 v_validate_schema_flg := FALSE;
150 ELSE
151 OPEN db_schema_compare_cur FOR ' select count(*)
152 from cz_db_settings,
153 cz_db_settings'||v_db_link||' tgt
154 where cz_db_settings.setting_id = tgt.setting_id
155 and cz_db_settings.value = tgt.value
156 and cz_db_settings.setting_id = ''MAJOR_VERSION''
157 INTERSECT
158 select count(*)
159 from cz_db_settings,
160 cz_db_settings'||v_db_link||' tgt
161 where cz_db_settings.setting_id = tgt.setting_id
162 and cz_db_settings.value = tgt.value
163 and cz_db_settings.setting_id = ''MINOR_VERSION'' ';
164 LOOP
165 FETCH db_schema_compare_cur INTO v_count;
166 EXIT WHEN db_schema_compare_cur%NOTFOUND;
167 END LOOP;
168 CLOSE db_schema_compare_cur;
169
170 IF (v_count <> 1) THEN
171 v_validate_schema_flg := FALSE;
172 END IF;
173 END IF;
174 RETURN v_validate_schema_flg ;
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 RETURN FALSE;
179 END validate_schema;
180 -------------------------------------------------------
181 ----procedure to register application
182 PROCEDURE set_dbms_info(p_module_name IN VARCHAR2)
183 IS
184
185 BEGIN
186 dbms_application_info.set_module(p_module_name,'');
187 END;
188
189 PROCEDURE reset_dbms_info
190 IS
191
192 BEGIN
193 dbms_application_info.set_module('','');
194 END;
195
196 ------------------------------------------------------
197 -- Verifies if there is another sync or a publishing session running
198 FUNCTION check_process RETURN VARCHAR2
199 IS
200 l_module_name v$session.module%TYPE;
201 BEGIN
202 SELECT module INTO l_module_name
203 FROM v$session
204 WHERE module IN ('CZ_PB_SYNC', 'CZ_PB_MGR');
205 RETURN l_module_name;
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 RETURN NULL;
209 END check_process;
210
211 -----------------------------------------------------
212 ------------------------------------------------------
213 ----procedure used by publishing to check if the target server is
214 ----actually a target and not a source.
215 ----if target then a value of 0 is returned
216 PROCEDURE verify_tgt_server(p_link_name IN cz_servers.fndnam_link_name%TYPE,
217 x_status OUT NOCOPY VARCHAR2,
218 x_msg OUT NOCOPY VARCHAR2)
219 IS
220
221 l_instance_name cz_servers.instance_name%TYPE;
222 l_hostname cz_servers.hostname%TYPE;
223 l_listener_port cz_servers.db_listener_port%TYPE;
224 l_source_server_flag cz_servers.source_server_flag%TYPE;
225 l_tgt_instance_name cz_servers.instance_name%TYPE;
226 gl_ref_cursor REF_CURSOR;
227 seq_ref_cursor REF_CURSOR;
228 l_server_local_id cz_servers.server_local_id%TYPE;
229
230 l_msg VARCHAR2(2000);
231
232 BEGIN
233 x_status := '0';
234
235 SELECT instance_name,hostname,db_listener_port
236 INTO l_instance_name,l_hostname,l_listener_port
237 FROM cz_servers
238 WHERE UPPER(cz_servers.local_name) = 'LOCAL';
239
240 OPEN gl_ref_cursor FOR 'SELECT instance_name, source_server_flag
241 FROM cz_servers'||p_link_name||' t
242 WHERE UPPER(t.local_name) = UPPER('''||l_instance_name||''')';
243 LOOP
244 FETCH gl_ref_cursor INTO l_tgt_instance_name, l_source_server_flag;
245 EXIT WHEN gl_ref_cursor%NOTFOUND;
246 END LOOP;
247 CLOSE gl_ref_cursor ;
248
249 IF (l_tgt_instance_name IS NOT NULL) THEN
250 IF (l_source_server_flag IS NULL) THEN
251 x_status := '1';
252 x_msg := CZ_UTILS.GET_TEXT('CZ_PB_MGR_NOT_PRD_INSTANCE');
253
254 ELSIF ( (l_source_server_flag IS NOT NULL)
255 AND (l_source_server_flag <> '1') ) THEN
256 x_status := '1';
257 x_msg := CZ_UTILS.GET_TEXT('CZ_PB_MGR_NOT_PRD_INSTANCE');
258 END IF;
259 ELSE
260 l_msg := 'SELECT cz_servers_s.nextval from dual'||p_link_name;
261 OPEN seq_ref_cursor FOR 'SELECT cz_servers_s.nextval from dual'||p_link_name;
262 LOOP
263 EXIT WHEN seq_ref_cursor%NOTFOUND;
264 FETCH seq_ref_cursor INTO l_server_local_id;
265 END LOOP;
266 CLOSE seq_ref_cursor;
267
268 l_msg := 'INSERT INTO cz_servers'||p_link_name||'
269 (server_local_id,local_name,hostname,db_listener_port,instance_name,import_enabled,source_server_flag)
270 SELECT '||l_server_local_id||',local_name,hostname,db_listener_port,instance_name,import_enabled,''1''
271 FROM cz_servers where UPPER(cz_servers.local_name) = ''LOCAL'' ';
272
273 EXECUTE IMMEDIATE
274 'INSERT INTO cz_servers'||p_link_name||
275 ' (server_local_id,local_name,hostname,db_listener_port,instance_name,import_enabled,source_server_flag) ' ||
276 ' SELECT '||l_server_local_id||',instance_name,hostname,db_listener_port,instance_name,import_enabled,''1'' ' ||
277 ' FROM cz_servers where UPPER(cz_servers.local_name) = ''LOCAL'' ';
278 COMMIT;
279 x_status := '0';
280 END IF;
281 EXCEPTION
282 WHEN NO_DATA_FOUND THEN
283 IF (gl_ref_cursor%ISOPEN) THEN
284 CLOSE gl_ref_cursor;
285 END IF;
286 IF (seq_ref_cursor%ISOPEN) THEN
287 CLOSE seq_ref_cursor;
288 END IF;
289 x_msg := CZ_UTILS.GET_TEXT('CZ_PB_SYNC_INSTANCE_IS_NULL','TGTINSTANCE','LOCAL');
290 x_status := '1';
291 WHEN OTHERS THEN
292 IF (gl_ref_cursor%ISOPEN) THEN
293 CLOSE gl_ref_cursor;
294 END IF;
295 IF (seq_ref_cursor%ISOPEN) THEN
296 CLOSE seq_ref_cursor;
297 END IF;
298 x_msg := SQLERRM;
299 x_status := '1';
300 END verify_tgt_server;
301 ----------------------------------------------------------------
302 ----procedure used by migration to check if the target server is
303 ----a Development instance and models can indeed migrate
304 -----------------------------------------------------------------
305 PROCEDURE verify_mig_tgt_server(p_link_name IN cz_servers.fndnam_link_name%TYPE,
306 x_status OUT NOCOPY VARCHAR2,
307 x_msg OUT NOCOPY VARCHAR2)
308 IS
309
310 l_converted_target VARCHAR2(1);
311 l_msg VARCHAR2(2000);
312 p_link_name_trim cz_servers.fndnam_link_name%TYPE;
313
314 BEGIN
315
316 --the local name is being passed as null because this is a migration
317 --call and it really does not matter. The local parameter is
318 --only passed to check local publication.
319
320 p_link_name_trim := REPLACE(p_link_name, '@', '');
321 l_converted_target := CZ_MODEL_MIGRATION_PVT.target_open_for ('M', p_link_name_trim, '');
322 IF (l_converted_target = '1') THEN
323 x_status :='0';
324 ELSE
325 x_status :='1';
326 x_msg := CZ_UTILS.GET_TEXT('CZ_CANNOT_MIGRATE');
327 END IF;
328 EXCEPTION
329 WHEN OTHERS THEN
330 x_msg := SQLERRM;
331 x_status := '0';
332 END verify_mig_tgt_server;
333
334 -------------------------------------------------------
335
336 END cz_pb_sync_util;
[Home] [Help]