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