1 PACKAGE BODY cz_model_migration_pvt AS
2 /* $Header: czmdlmgb.pls 120.19 2007/11/29 12:58:12 kdande ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'cz_model_migration_pvt';
5
6 BOM_ITEM_TYPE_MODEL CONSTANT NUMBER := 1;
7 PS_NODE_TYPE_REFERENCE CONSTANT NUMBER := 263;
8
9 MODEL_TYPE_NORMAL CONSTANT INTEGER := 0;
10 MODEL_TYPE_ABNORMAL CONSTANT INTEGER := 1;
11 MODEL_TYPE_NAME_ERR CONSTANT INTEGER := 2;
12
13 CP_RETCODE_SUCCESS CONSTANT INTEGER := 0;
14 CP_RETCODE_WARNING CONSTANT INTEGER := 1;
15 CP_RETCODE_FAILURE CONSTANT INTEGER := 2;
16
17 m_commit_size INTEGER;
18
19 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
20 TYPE num_tbl_type_index_vc2 IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
21
22 procedure log_msg(p_caller IN VARCHAR2
23 ,p_ndebug IN NUMBER
24 ,p_msg IN VARCHAR2
25 ,p_level IN NUMBER);
26
27 ---------------------------------------------------------------------------------------
28
29 /*
30 * Public API for Model Migration.
31 * @param p_request_id This is the CZ_MODEL_PUBLICATIONS, MIGRATION_GROUP_ID of the migration request.
32 * Migration request is created by Developer and contains the list of all models selected
33 * for Migration from the source's Configurator Repository, target Instance name and
34 * target Repository Folder.
35 * @param p_userid Standard parameters required for locking. Represent calling user.
36 * @param p_respid Standard parameters required for locking. Represent calling responsibility.
37 * @param p_applid Standard parameters required for locking. Represent calling application.
38 * @param p_run_id Number identifying the session. If left NULL, the API will generate the number and
39 * return it in x_run_id.
40 * @param x_run_id Output parameter containing internally generated session identifier if p_run_id
41 * was NULL, otherwise equal to p_run_id.
42 */
43
44 PROCEDURE migrate_models(p_request_id IN NUMBER,
45 p_user_id IN NUMBER,
46 p_resp_id IN NUMBER,
47 p_appl_id IN NUMBER,
48 p_run_id IN NUMBER,
49 x_run_id OUT NOCOPY NUMBER,
50 x_status OUT NOCOPY VARCHAR2
51 ) IS
52
53 l_api_name CONSTANT VARCHAR2(30) := 'migrate_models';
54 l_status VARCHAR2(3);
55 l_errbuf VARCHAR2(4000);
56 l_publication_id NUMBER;
57 BEGIN
58
59 fnd_global.apps_initialize(p_user_id, p_resp_id, p_appl_id);
60 x_run_id := p_run_id;
61
62 IF(x_run_id IS NULL)THEN
63 SELECT cz_xfr_run_infos_s.NEXTVAL INTO x_run_id FROM DUAL;
64 END IF;
65
66 x_status := FND_API.G_RET_STS_SUCCESS;
67
68 FOR c_pub IN (SELECT publication_id FROM cz_model_publications
69 WHERE migration_group_id = p_request_id AND deleted_flag = '0')LOOP
70
71 cz_pb_mgr.publish_model(c_pub.publication_id, x_run_id, l_status);
72
73 IF l_status <> 'OK' THEN x_status := FND_API.G_RET_STS_ERROR; END IF;
74 END LOOP;
75 EXCEPTION
76 WHEN OTHERS THEN
77 x_status := FND_API.G_RET_STS_ERROR;
78 l_errbuf := SQLERRM;
79 cz_utils.log_report(G_PKG_NAME, l_api_name, null, l_errbuf, fnd_log.LEVEL_UNEXPECTED);
80 END;
81 ---------------------------------------------------------------------------------------
82 /*
83 * Migrate Models concurrent procedure.
84 * @param errbuf Standard Oracle Concurrent Program output parameters.
85 * @param retcode Standard Oracle Concurrent Program output parameters.
86 * @param p_request_id This is the CZ_MODEL_PUBLICATIONS, MIGRATION_GROUP_ID of the migration request.
87 * Migration request is created by Developer and contains the list of all models selected
88 * for Migration from the source's Configurator Repository, target Instance name and
89 * target Repository Folder.
90 */
91
92 PROCEDURE migrate_models_cp(errbuf OUT NOCOPY VARCHAR2,
93 retcode OUT NOCOPY NUMBER,
94 p_request_id IN NUMBER
95 ) IS
96 l_status VARCHAR2(3);
97 l_publication_id NUMBER;
98 l_run_id NUMBER := 0;
99 l_mig_group_found BOOLEAN :=FALSE;
100 l_api_name CONSTANT VARCHAR2(30) := 'migrate_models_cp';
101 l_ndebug PLS_INTEGER:=1;
102
103 BEGIN
104
105 retcode:=0;
106 cz_pb_mgr.GLOBAL_EXPORT_RETCODE := 0;
107
108 FOR c_pub IN (SELECT publication_id FROM cz_model_publications
109 WHERE migration_group_id = p_request_id AND deleted_flag = '0')LOOP
110
111 l_mig_group_found :=TRUE;
112 cz_pb_mgr.publish_model(c_pub.publication_id, l_run_id, l_status);
113
114 errbuf := NULL;
115 IF(cz_pb_mgr.GLOBAL_EXPORT_RETCODE = 1)THEN
116
117 errbuf := CZ_UTILS.GET_TEXT('CZ_MM_WARNING');
118
119 ELSIF(cz_pb_mgr.GLOBAL_EXPORT_RETCODE = 2) THEN
120
121 errbuf := CZ_UTILS.GET_TEXT('CZ_MM_FAILURE');
122 END IF;
123 END LOOP;
124
125 IF NOT l_mig_group_found THEN
126 errbuf := cz_utils.get_text('CZ_INVALID_MIGR_GROUP_NUMBER', 'MIGRGRP', p_request_id);
127 log_msg(l_api_name, l_ndebug, errbuf , FND_LOG.LEVEL_PROCEDURE);
128 raise_application_error('-20020', 'INVALID_MIGRATION_GROUP');
129 END IF;
130
131 retcode := cz_pb_mgr.GLOBAL_EXPORT_RETCODE;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 retcode := 2;
136 errbuf := CZ_UTILS.GET_TEXT('CZ_MM_UNEXPECTED');
137 END;
138 ---------------------------------------------------------------------------------------
139 /*
140 * Procedure for persistent id(s) allocation in migrated models.
141 * @param p_model_id devl_project_id of the model.
142 * @param x_new_record_id Candidate for the new id.
143 */
144
145 PROCEDURE allocate_persistent_id(p_model_id IN NUMBER,
146 x_new_record_id IN OUT NOCOPY NUMBER
147 ) IS
148 PRAGMA AUTONOMOUS_TRANSACTION;
149 BEGIN
150 UPDATE CZ_PERSISTENT_REC_IDS SET max_persistent_rec_id = max_persistent_rec_id + 1
151 WHERE devl_project_id= (select devl_project_id from cz_devl_projects
152 where deleted_flag = '0' and devl_project_id=p_model_id
153 AND post_migr_change_flag <> 'L') and deleted_flag=0
154 --If no record returned, the value of the variable will not change.
155 RETURNING max_persistent_rec_id INTO x_new_record_id;
156 COMMIT;
157 END;
158 --------------------------------------------------------------------------------
159 --------------------------- Configuration Upgrade ----------------------------
160 --------------------------------------------------------------------------------
161 procedure log_msg(p_caller IN VARCHAR2
162 ,p_ndebug IN NUMBER
163 ,p_msg IN VARCHAR2
164 ,p_level IN NUMBER)
165 IS
166 BEGIN
167 IF FND_GLOBAL.CONC_REQUEST_ID > 0 THEN
168 FND_FILE.PUT_LINE(FND_FILE.LOG, p_msg);
169 END IF;
170 cz_utils.log_report(G_PKG_NAME, p_caller, p_ndebug, p_msg, p_level);
171 END log_msg;
172
173 --------------------------------------------------------------------------------
174 PROCEDURE get_commit_size_setting IS
175 BEGIN
176 SELECT NVL(TO_NUMBER(value), 50000) INTO m_commit_size
177 FROM cz_db_settings
178 WHERE upper(SECTION_NAME) = 'SCHEMA' AND upper(SETTING_ID) = 'BATCHSIZE';
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 m_commit_size := 50000;
182 END get_commit_size_setting;
183
184 --------------------------------------------------------------------------------
185
186 FUNCTION check_model(p_model_id IN NUMBER) RETURN NUMBER
187 IS
188 l_model_type INTEGER;
189 BEGIN
190 -- each node has name ?
191 BEGIN
192 SELECT MODEL_TYPE_NAME_ERR INTO l_model_type FROM cz_ps_nodes
193 WHERE deleted_flag = '0' AND devl_project_id IN
194 (SELECT component_id FROM cz_model_ref_expls
195 WHERE deleted_flag = '0' AND model_id = p_model_id
196 AND (ps_node_type = PS_NODE_TYPE_REFERENCE OR parent_expl_node_id IS NULL))
197 AND name IS NULL AND rownum < 2;
198 EXCEPTION
199 WHEN NO_DATA_FOUND THEN
200 l_model_type := MODEL_TYPE_NORMAL;
201 END;
202
203 -- 1 pid maps exactly with 1 name ?
204 IF l_model_type = MODEL_TYPE_NORMAL THEN
205 FOR i IN (SELECT persistent_node_id, COUNT(distinct name)
206 FROM cz_ps_nodes
207 WHERE deleted_flag = '0' AND devl_project_id IN
208 (SELECT component_id FROM cz_model_ref_expls
209 WHERE deleted_flag = '0' AND model_id = p_model_id
210 AND (ps_node_type = PS_NODE_TYPE_REFERENCE OR
211 parent_expl_node_id IS NULL))
212 GROUP BY persistent_node_id
213 HAVING COUNT(DISTINCT name) > 1)
214 LOOP
215 l_model_type := MODEL_TYPE_ABNORMAL;
216 EXIT;
217 END LOOP;
218 END IF;
219
220 RETURN l_model_type;
221 END check_model;
222
223 --------------------------------------------------------------------------------
224 -- to do: 1. collect upgraded hdrs, then check missed items using bulk bind?
225 -- 2. check if child model already in map when building map?
226 PROCEDURE upgrade_configs_by_model(p_model_id IN NUMBER
227 ,p_begin_date IN DATE
228 ,p_end_date IN DATE
229 ,x_retcode OUT NOCOPY NUMBER
230 -- ,x_msg OUT NOCOPY VARCHAR2
231 )
232 IS
233 l_api_name CONSTANT VARCHAR2(30) := 'upgrade_configs_by_model';
234 l_cmt_rec_count PLS_INTEGER;
235 l_ndebug PLS_INTEGER;
236 l_msg VARCHAR2(1000);
237 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
238 TYPE name_tbl_type IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
239 TYPE name_tbl_type_idx_vc2 IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY VARCHAR2(15);
240 TYPE complex_num_tbl_type IS TABLE OF num_tbl_type_index_vc2 INDEX BY VARCHAR2(15);
241 TYPE complex_name_tbl_type IS TABLE OF name_tbl_type_idx_vc2 INDEX BY VARCHAR2(15);
242
243 l_hdr_tbl num_tbl_type;
244 l_rev_tbl num_tbl_type;
245 l_mdl_tbl num_tbl_type;
246 l_model_type_map num_tbl_type_index_vc2;
247 l_prj_pid_name_map complex_name_tbl_type;
248 l_ref_node_map complex_num_tbl_type;
249
250 l_model_id NUMBER;
251 l_model_type INTEGER;
252 l_model_name cz_devl_projects.name%TYPE;
253 l_item_prj_map num_tbl_type_index_vc2;
254 l_item_tbl num_tbl_type;
255 l_name_tbl name_tbl_type;
256 l_prj_id NUMBER;
257
258 l_upd_item_count PLS_INTEGER;
259 l_miss_item_count PLS_INTEGER;
260 l_fail_hdr_count PLS_INTEGER;
261 l_miss_item_tbl num_tbl_type;
262 l_no_miss_item BOOLEAN;
263
264 PROCEDURE create_maps(p_model_id IN NUMBER)
265 IS
266 BEGIN
267 FOR i IN (SELECT devl_project_id, persistent_node_id, name, ps_node_type, reference_id
268 FROM cz_ps_nodes
269 WHERE deleted_flag = '0' AND devl_project_id in
270 (SELECT component_id FROM cz_model_ref_expls
271 WHERE deleted_flag = '0' AND model_id = p_model_id
272 AND (ps_node_type = PS_NODE_TYPE_REFERENCE OR parent_expl_node_id IS NULL)))
273 LOOP
274 l_prj_pid_name_map(i.devl_project_id)(i.persistent_node_id) := i.name;
275 IF i.ps_node_type = PS_NODE_TYPE_REFERENCE THEN
276 l_ref_node_map(i.persistent_node_id)(i.devl_project_id) := i.reference_id;
277 END IF;
278 END LOOP;
279 END create_maps;
280
281 BEGIN
282 l_ndebug := 1;
283 log_msg(l_api_name, l_ndebug, 'model_id=' || p_model_id, FND_LOG.LEVEL_PROCEDURE);
284
285 SELECT config_hdr_id, config_rev_nbr, component_id
286 BULK COLLECT INTO l_hdr_tbl, l_rev_tbl, l_mdl_tbl
287 FROM cz_config_hdrs hdr
288 WHERE deleted_flag = '0'
289 AND creation_date >= NVL(p_begin_date, cz_utils.EPOCH_BEGIN_)
290 AND creation_date <= NVL(p_end_date, SYSDATE)
291 AND persistent_component_id = (SELECT persistent_project_id
292 FROM cz_devl_projects
293 WHERE devl_project_id = p_model_id)
294 AND EXISTS (SELECT NULL FROM cz_config_items
295 WHERE config_hdr_id = hdr.config_hdr_id
296 AND config_rev_nbr = hdr.config_rev_nbr
297 AND (parent_config_item_id IS NULL OR parent_config_item_id = -1)
298 AND ps_node_name IS NULL);
299
300 IF l_hdr_tbl.COUNT = 0 THEN
301 SELECT name INTO l_model_name
302 FROM cz_devl_projects
303 WHERE devl_project_id = p_model_id;
304 l_msg := cz_utils.get_text('CZ_UPGCFG_NO_CFG', 'MODELNAME', l_model_name);
305 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_STATEMENT);
306 x_retcode := CP_RETCODE_SUCCESS;
307 RETURN;
308 ELSE
309 l_msg := 'Number of configs found which need to be upgraded for model '
310 || p_model_id|| ': ' || l_hdr_tbl.COUNT;
311 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_STATEMENT);
312 END IF;
313
314 l_ndebug := 2;
315 l_cmt_rec_count := 0;
316 l_fail_hdr_count := 0;
317 l_no_miss_item := TRUE;
318 FOR i IN l_hdr_tbl.FIRST .. l_hdr_tbl.LAST LOOP
319 -- Use the config src model if the model and all its referred models still exist
320 -- Use the common model otherwise
321 l_model_id := p_model_id;
322 BEGIN
323 SELECT devl_project_id INTO l_model_id
324 FROM cz_devl_projects
325 WHERE devl_project_id = l_mdl_tbl(i) AND deleted_flag = '0';
326
327 SELECT p_model_id INTO l_model_id
328 FROM cz_model_ref_expls re
329 WHERE deleted_flag = '0' AND model_id = l_mdl_tbl(i)
330 AND ps_node_type = PS_NODE_TYPE_REFERENCE
331 AND NOT EXISTS (SELECT 1 FROM cz_devl_projects
332 WHERE deleted_flag = '0' AND devl_project_id = re.component_id)
333 AND ROWNUM < 2;
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 NULL;
337 END;
338
339 l_ndebug := 3;
340 IF l_model_type_map.EXISTS(l_model_id) THEN
341 l_model_type := l_model_type_map(l_model_id);
342 ELSE
343 l_model_type := check_model(l_model_id);
344 IF l_model_type = MODEL_TYPE_ABNORMAL THEN
345 create_maps(l_model_id);
346 ELSIF l_model_type = MODEL_TYPE_NAME_ERR THEN
347 -- should never happen, otherwise need an fnd msg
348 l_msg := 'Model ' || l_model_id || ' cannot be used in config upgrade ' ||
349 'because some nodes have no name';
350 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_ERROR);
351 END IF;
352 l_model_type_map(l_model_id) := l_model_type;
353 END IF;
354
355 -- print_maps(l_prj_pid_name_map, l_ref_node_map, l_model_type_map);
356 IF l_model_type <> MODEL_TYPE_NAME_ERR THEN
357 l_msg := 'Processing config (' || l_hdr_tbl(i) || ',' || l_rev_tbl(i) ||
358 ') vs. type ' || l_model_type || ' model ' || l_model_id;
359 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_STATEMENT);
360 END IF;
361
362 l_miss_item_tbl.DELETE;
363 IF l_model_type = MODEL_TYPE_NORMAL THEN
364 l_ndebug := 4;
365 UPDATE cz_config_items item
366 SET ps_node_name =
367 (SELECT name FROM cz_ps_nodes psn
368 WHERE deleted_flag = '0' AND persistent_node_id = item.ps_node_id
369 AND EXISTS (SELECT 1 FROM cz_model_ref_expls
370 WHERE deleted_flag = '0' AND model_id = l_model_id
371 AND (ps_node_type = PS_NODE_TYPE_REFERENCE OR parent_expl_node_id IS NULL)
372 AND component_id = psn.devl_project_id)
373 AND rownum < 2)
374 WHERE config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i);
375 l_cmt_rec_count := l_cmt_rec_count + SQL%ROWCOUNT;
376
377 SELECT config_item_id BULK COLLECT INTO l_miss_item_tbl
378 FROM cz_config_items
379 WHERE config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i)
380 AND deleted_flag = '0' AND ps_node_name IS NULL;
381
382 ELSIF l_model_type = MODEL_TYPE_ABNORMAL THEN
383 l_ndebug := 5;
384 l_upd_item_count := 0;
385 l_miss_item_count := 0;
386 l_item_tbl.DELETE;
387 l_name_tbl.DELETE;
388 l_item_prj_map.DELETE;
389 FOR j IN (SELECT config_item_id, ps_node_id, parent_config_item_id
390 FROM cz_config_items
391 WHERE deleted_flag = '0'
392 START WITH (parent_config_item_id IS NULL OR parent_config_item_id = -1)
393 AND config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i)
394 CONNECT BY PRIOR config_item_id = parent_config_item_id
395 AND config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i)
396 AND deleted_flag = '0')
397 LOOP
398 IF j.parent_config_item_id IS NULL OR j.parent_config_item_id = -1 THEN
399 l_prj_id := l_model_id;
400 ELSE
401 l_prj_id := l_item_prj_map(j.parent_config_item_id);
402 END IF;
403
404 IF l_prj_pid_name_map.EXISTS(l_prj_id) AND l_prj_pid_name_map(l_prj_id).EXISTS(j.ps_node_id) THEN
405 l_upd_item_count := l_upd_item_count + 1;
406 l_item_tbl(l_upd_item_count) := j.config_item_id;
407 l_name_tbl(l_upd_item_count) := l_prj_pid_name_map(l_prj_id)(j.ps_node_id);
408 ELSE
409 l_miss_item_count := l_miss_item_count + 1;
410 l_miss_item_tbl(l_miss_item_count) := j.config_item_id;
411 END IF;
412
413 IF l_ref_node_map.EXISTS(j.ps_node_id) AND l_ref_node_map(j.ps_node_id).EXISTS(l_prj_id) THEN
414 l_item_prj_map(j.config_item_id) := l_ref_node_map(j.ps_node_id)(l_prj_id);
415 ELSE
416 l_item_prj_map(j.config_item_id) := l_prj_id;
417 END IF;
418 END LOOP;
419
420 -- print_maps(l_item_prj_map, l_item_tbl, l_name_tbl, l_miss_item_tbl);
421 l_ndebug := 6;
422 FORALL j IN l_item_tbl.FIRST .. l_item_tbl.LAST
423 UPDATE cz_config_items
424 SET ps_node_name = l_name_tbl(j)
425 WHERE config_hdr_id = l_hdr_tbl(i) AND config_rev_nbr = l_rev_tbl(i)
426 AND config_item_id = l_item_tbl(j);
427
428 l_cmt_rec_count := l_cmt_rec_count + l_upd_item_count;
429
430 ELSE
431 -- ? try to use p_model_id if l_model_id <> p_model_id
432 l_ndebug := 7;
433 l_fail_hdr_count := l_fail_hdr_count + 1;
434 END IF;
435
436 l_ndebug := 8;
437 IF l_cmt_rec_count >= m_commit_size THEN
438 COMMIT;
439 l_cmt_rec_count := 0;
440 END IF;
441
442 IF l_miss_item_tbl.COUNT > 0 THEN
443 l_no_miss_item := FALSE;
444 FOR j IN l_miss_item_tbl.FIRST ..l_miss_item_tbl.LAST LOOP
445 l_msg := cz_utils.get_text('CZ_UPGCFG_ITEM_NO_NODE', 'ID', to_char(l_miss_item_tbl(j)),
446 'HDR', to_char(l_hdr_tbl(i)), 'REV', to_char(l_rev_tbl(i)));
447 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_STATEMENT);
448 END LOOP;
449 END IF;
450
451 IF l_model_type = MODEL_TYPE_NAME_ERR THEN
452 l_msg := 'config (' || l_hdr_tbl(i) || ',' || l_rev_tbl(i) || ') not upgraded';
453 ELSE
454 l_msg := 'config (' || l_hdr_tbl(i) || ',' || l_rev_tbl(i) || ') upgraded';
455 END IF;
456 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_STATEMENT);
457 END LOOP;
458
459 l_ndebug := 10;
460 IF l_cmt_rec_count > 0 THEN
461 COMMIT;
462 END IF;
463
464 IF l_fail_hdr_count = 0 AND l_no_miss_item THEN
465 x_retcode := CP_RETCODE_SUCCESS;
466 -- x_msg := 'All configs with model ' || p_model_id || ' upgraded';
467 ELSIF l_fail_hdr_count = l_hdr_tbl.COUNT THEN
468 x_retcode := CP_RETCODE_FAILURE;
469 -- x_msg := 'All configs with model ' || p_model_id || ' failed in upgrade';
470 ELSE
471 x_retcode := CP_RETCODE_WARNING;
472 -- x_msg := 'Not all configs with model ' || p_model_id || ' upgraded: ' ||
473 -- 'some configs either not processed at all or just partially upgraded';
474 END IF;
475 -- log_msg(l_api_name, l_ndebug, x_msg, FND_LOG.LEVEL_PROCEDURE);
476 EXCEPTION
477 WHEN OTHERS THEN
478 x_retcode := CP_RETCODE_FAILURE;
479 l_msg := 'Fatal error in ' || l_api_name || '.' || l_ndebug || ': ' ||
480 substr(SQLERRM,1,900);
481 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_UNEXPECTED);
482 END upgrade_configs_by_model;
483 --------------------------------------------------------------------------------
484
485 PROCEDURE upgrade_configs_by_item(p_organization_id IN NUMBER
486 ,p_inventory_item_id IN NUMBER
487 ,p_application_id IN NUMBER
488 ,p_begin_date IN DATE
489 ,p_end_date IN DATE
490 ,x_retcode OUT NOCOPY NUMBER
491 -- ,x_msg OUT NOCOPY VARCHAR2
492 )
493 IS
494 l_api_name CONSTANT VARCHAR2(30) := 'upgrade_configs_by_item';
495 l_model_id cz_devl_projects.devl_project_id%TYPE;
496 l_ndebug INTEGER;
497 l_msg VARCHAR2(1000);
498 l_msg_name VARCHAR2(30);
499 BEGIN
500 l_ndebug := 1;
501 l_msg := 'BEGIN: org=' || p_organization_id || ', inv item=' || p_inventory_item_id;
502 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_PROCEDURE);
503 l_model_id := cz_cf_api.config_model_for_item(p_inventory_item_id,
504 p_organization_id, SYSDATE, p_application_id, NULL);
505 IF l_model_id IS NULL THEN
506 x_retcode := CP_RETCODE_FAILURE;
507 l_msg := cz_utils.get_text('CZ_UPGCFG_NO_PUB_ITEM', 'ITEM', to_char(p_inventory_item_id),
508 'ORG', to_char(p_organization_id));
509 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_ERROR);
510 RETURN;
511 END IF;
512
513 l_ndebug := 2;
514 IF check_model(l_model_id) = MODEL_TYPE_NAME_ERR THEN
515 -- should never happen, otherwise need an fnd msg
516 x_retcode := CP_RETCODE_FAILURE;
517 l_msg := 'Not all nodes in the published model ' || l_model_id || ' have names';
518 ELSE
519 l_ndebug := 3;
520 upgrade_configs_by_model(l_model_id
521 ,p_begin_date
522 ,p_end_date
523 ,x_retcode
524 -- ,x_msg
525 );
526 END IF;
527 IF x_retcode = CP_RETCODE_SUCCESS THEN
528 l_msg_name := 'CZ_UPGCFG_ITEM_SUCC';
529 ELSIF x_retcode = CP_RETCODE_WARNING THEN
530 l_msg_name := 'CZ_UPGCFG_ITEM_WARN';
531 ELSE
532 l_msg_name := 'CZ_UPGCFG_ITEM_FAIL';
533 END IF;
534 l_msg := cz_utils.get_text(l_msg_name, 'ITEM', to_char(p_inventory_item_id),
535 'ORG', to_char(p_organization_id));
536 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_PROCEDURE);
537 EXCEPTION
538 WHEN OTHERS THEN
539 x_retcode := CP_RETCODE_FAILURE;
540 l_msg := 'Fatal error in ' || l_api_name || '.' || l_ndebug || ': ' ||
541 substr(SQLERRM,1,900);
542 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_UNEXPECTED);
543 END upgrade_configs_by_item;
544 --------------------------------------------------------------------------------
545
546 /*
547 * Concurrent procedure for Configuration Upgrade by Item(s).
548 * @param errbuf Standard Oracle Concurrent Program output parameters.
549 * @param retcode Standard Oracle Concurrent Program output parameters.
550 * @param p_organization_id Used to search configurations by organization ID, required.
551 * @param p_top_inv_item_from Used to search configurations by top item, optional
552 * @param p_top_inv_item_to Used to search configurations by top item, optional
553 * @param p_application_id Used to refine search for equivalent published
554 * models to use as baselines for upgrade, optional.
555 * @param p_config_begin_date Optional, if present, indicates the date of the oldest
556 * configuration to be updated.
557 * @param p_config_end_date Optional, if present, indicates the date of the newest
558 * configuration to be updated.
559 */
560
561 PROCEDURE upgrade_configs_by_items_cp
562 (errbuf OUT NOCOPY VARCHAR2
563 ,retcode OUT NOCOPY NUMBER
564 ,p_organization_code IN VARCHAR2
565 ,p_organization_id IN NUMBER
566 ,p_top_inv_item_from IN VARCHAR2
567 ,p_top_inv_item_to IN VARCHAR2
568 ,p_application_id IN NUMBER
569 ,p_config_begin_date IN VARCHAR2
570 ,p_config_end_date IN VARCHAR2
571 )
572 IS
573 l_api_name CONSTANT VARCHAR2(30) := 'upgrade_configs_by_items';
574 l_item_tbl num_tbl_type;
575 l_org_tbl num_tbl_type;
576 l_org_id NUMBER;
577 l_one_org BOOLEAN;
578 l_begin_date DATE;
579 l_end_date DATE;
580 l_num_invitem INTEGER;
581 l_num_success PLS_INTEGER;
582 l_num_warning PLS_INTEGER;
583 l_num_failure PLS_INTEGER;
584 l_ndebug PLS_INTEGER;
585 l_retcode NUMBER;
586 l_msg VARCHAR2(1000);
587
588 BEGIN
589 l_ndebug := 1;
590 l_msg := 'BEGIN: org=' || nvl(to_char(p_organization_id), 'null') || ',' ||
591 'from_item=' || nvl(p_top_inv_item_from, 'null') || ',' ||
592 'to_item=' || nvl(p_top_inv_item_to, 'null') || ',' ||
593 'application=' || nvl(to_char(p_application_id), 'null') || ',' ||
594 'begin_date=' || nvl(p_config_begin_date, 'null') || ',' ||
595 'end_date=' || nvl(p_config_end_date, 'null');
596 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_PROCEDURE);
597
598 l_num_success := 0;
599 l_num_warning := 0;
600 l_num_failure := 0;
601
602 l_begin_date := fnd_date.canonical_to_date(p_config_begin_date);
603 l_end_date := fnd_date.canonical_to_date(p_config_end_date);
604
605 l_ndebug := 2;
606 IF p_organization_id IS NULL THEN
607 l_one_org := FALSE;
608 SELECT DISTINCT top_item_id, organization_id
609 BULK COLLECT INTO l_item_tbl, l_org_tbl
610 FROM cz_model_publications
611 WHERE deleted_flag = '0' AND object_type = 'PRJ'
612 AND source_target_flag = 'T' AND export_status = 'OK'
613 AND top_item_id IS NOT NULL AND organization_id IS NOT NULL;
614 ELSE
615 l_one_org := TRUE;
616 IF p_top_inv_item_from IS NULL AND p_top_inv_item_to IS NULL THEN
617 SELECT DISTINCT top_item_id BULK COLLECT INTO l_item_tbl
618 FROM cz_model_publications
619 WHERE deleted_flag = '0' AND object_type = 'PRJ'
620 AND source_target_flag = 'T' AND export_status = 'OK'
621 AND organization_id = p_organization_id;
622 ELSE
623 SELECT inventory_item_id BULK COLLECT INTO l_item_tbl
624 FROM mtl_system_items_vl item
625 WHERE organization_id = p_organization_id
626 AND concatenated_segments BETWEEN NVL(p_top_inv_item_from, p_top_inv_item_to)
627 AND NVL(p_top_inv_item_to, p_top_inv_item_from)
628 AND bom_item_type = BOM_ITEM_TYPE_MODEL
629 AND exists (SELECT NULL FROM cz_model_publications
630 WHERE deleted_flag = '0' AND object_type = 'PRJ'
631 AND source_target_flag = 'T' AND export_status = 'OK'
632 AND top_item_id = item.inventory_item_id
633 AND organization_id = p_organization_id);
634 END IF;
635 END IF;
636
637 l_num_invitem := l_item_tbl.COUNT;
638 log_msg(l_api_name, l_ndebug, 'Number of items: ' || l_num_invitem, FND_LOG.LEVEL_ERROR);
639 IF l_num_invitem = 0 THEN
640 retcode := CP_RETCODE_SUCCESS;
641 RETURN;
642 END IF;
643
644 l_ndebug := 3;
645 l_org_id := p_organization_id;
646 get_commit_size_setting;
647 FOR i IN l_item_tbl.FIRST .. l_item_tbl.LAST LOOP
648 IF NOT l_one_org THEN
649 l_org_id := l_org_tbl(i);
650 END IF;
651 upgrade_configs_by_item(l_org_id
652 ,l_item_tbl(i)
653 ,p_application_id
654 ,l_begin_date
655 ,l_end_date
656 ,l_retcode
657 -- ,l_msg
658 );
659 l_ndebug := l_ndebug + 1;
660 IF l_retcode = CP_RETCODE_SUCCESS THEN
661 l_num_success := l_num_success + 1;
662 ELSIF l_retcode = CP_RETCODE_WARNING THEN
663 l_num_warning := l_num_warning + 1;
664 ELSE
665 l_num_failure := l_num_failure + 1;
666 END IF;
667 END LOOP;
668
669 IF l_num_success = l_num_invitem THEN
670 retcode := CP_RETCODE_SUCCESS;
671 ELSIF l_num_failure = l_num_invitem THEN
672 retcode := CP_RETCODE_FAILURE;
673 ELSE
674 retcode := CP_RETCODE_WARNING;
675 END IF;
676 l_msg := cz_utils.get_text('CZ_UPGCFG_ITEMS', 'NSUCC', to_char(l_num_success),
677 'NWARN', to_char(l_num_warning), 'NFAIL', to_char(l_num_failure));
678 -- log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_ERROR);
679 errbuf := l_msg;
680 EXCEPTION
681 WHEN OTHERS THEN
682 retcode := CP_RETCODE_FAILURE;
683 errbuf := 'Fatal error in ' || l_api_name || '.' || l_ndebug || ': ' ||
684 substr(SQLERRM,1,900);
685 END upgrade_configs_by_items_cp;
686
687 ---------------------------------------------------------------------------------------
688 /*
689 * Concurrent procedure for Configuration Upgrade by Product key.
690 * @param errbuf Standard Oracle Concurrent Program output parameters.
691 * @param retcode Standard Oracle Concurrent Program output parameters.
692 * @param p_product_key Used to search configurations, required.
693 * @param p_application_id Used to refine search for equivalent published models
694 * to use as baselines for upgrade, optional.
695 * @param p_config_begin_date Optional, if present, indicates the date of the oldest
696 * configuration to be updated.
697 * @param p_config_end_date Optional, if present, indicates the date of the newest
698 * configuration to be updated.
699 */
700
701 PROCEDURE upgrade_configs_by_product_cp
702 (errbuf OUT NOCOPY VARCHAR2
703 ,retcode OUT NOCOPY NUMBER
704 ,p_product_key IN VARCHAR2
705 ,p_application_id IN NUMBER
706 ,p_config_begin_date IN VARCHAR2
707 ,p_config_end_date IN VARCHAR2
708 )
709 IS
710 l_api_name CONSTANT VARCHAR2(30) := 'upgrade_configs_by_product';
711 l_begin_date DATE;
712 l_end_date DATE;
713 l_model_id cz_devl_projects.devl_project_id%TYPE;
714 l_ndebug INTEGER;
715 l_msg VARCHAR2(1000);
716 l_msg_name VARCHAR2(30);
717 v_publication_id NUMBER;
718 BEGIN
719 l_ndebug := 1;
720 l_msg := 'BEGIN: product_key=' || p_product_key || ',' ||
721 'application=' || nvl(to_char(p_application_id), 'null') || ',' ||
722 'begin_date=' || nvl(p_config_begin_date, 'null') || ',' ||
723 'end_date=' || nvl(p_config_end_date, 'null');
724 log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_PROCEDURE);
725
726 l_begin_date := fnd_date.canonical_to_date(p_config_begin_date);
727 l_end_date := fnd_date.canonical_to_date(p_config_end_date);
728
729 get_commit_size_setting;
730
731 l_ndebug := 2;
732 l_model_id := cz_cf_api.config_model_for_product(p_product_key,SYSDATE,p_application_id,NULL);
733 IF l_model_id IS NULL THEN
734 errbuf := cz_utils.get_text('CZ_UPGCFG_NO_PUB_PRD', 'PRD', p_product_key);
735 retcode := CP_RETCODE_FAILURE;
736 log_msg(l_api_name, l_ndebug, errbuf, FND_LOG.LEVEL_ERROR);
737 RETURN;
738 ELSE
739 -- Bug 5496507; 14-Sep-2006; kdande; See if the model derived from publication is migrated and error out if it is, else proceed with upgrade.
740 DECLARE
741 -- Check if the model is a migrated one
742 CURSOR cur_model_migrated IS
743 SELECT 'Y' migrated
744 FROM cz_devl_projects dp
745 WHERE dp.devl_project_id = l_model_id
746 AND dp.post_migr_change_flag IS NOT NULL
747 AND dp.post_migr_change_flag <> 'L';
748 rec_model_migrated cur_model_migrated%ROWTYPE;
749 BEGIN
750 OPEN cur_model_migrated;
751 FETCH cur_model_migrated INTO rec_model_migrated;
752 IF (cur_model_migrated%FOUND) THEN
753 CLOSE cur_model_migrated;
754 v_publication_id := cz_cf_api.publication_for_product (
755 product_key => p_product_key,
756 config_lookup_date => SYSDATE,
757 calling_application_id => p_application_id,
758 usage_name => NULL,
759 publication_mode => NULL,
760 language => NULL
761 );
762 errbuf := cz_utils.get_text ('CZ_UPGCFG_WRONG_PUB4PRODKEY', 'PUBID1', v_publication_id, 'PUBID2', v_publication_id);
763 retcode := CP_RETCODE_FAILURE;
764 log_msg (l_api_name, l_ndebug, errbuf, FND_LOG.LEVEL_ERROR);
765 RETURN;
766 ELSE
767 CLOSE cur_model_migrated;
768 END IF;
769 END;
770 -- End of fix for bug 5496507
771 END IF;
772
773 l_ndebug := 3;
774 IF check_model(l_model_id) = MODEL_TYPE_NAME_ERR THEN
775 errbuf := 'Not all model nodes in model ' || l_model_id || ' have names';
776 retcode := CP_RETCODE_FAILURE;
777 ELSE
778 l_ndebug := 4;
779 upgrade_configs_by_model(l_model_id
780 ,l_begin_date
781 ,l_end_date
782 ,retcode
783 -- ,errbuf
784 );
785 END IF;
786 IF retcode = CP_RETCODE_SUCCESS THEN
787 l_msg_name := 'CZ_UPGCFG_PROD_SUCC';
788 ELSIF retcode = CP_RETCODE_WARNING THEN
789 l_msg_name := 'CZ_UPGCFG_PROD_WARN';
790 ELSE
791 l_msg_name := 'CZ_UPGCFG_PROD_FAIL';
792 END IF;
793 errbuf := cz_utils.get_text(l_msg_name, 'PRD', p_product_key);
794 -- l_msg := 'retcode=' || retcode || ',msg=' || errbuf;
795 -- log_msg(l_api_name, l_ndebug, l_msg, FND_LOG.LEVEL_PROCEDURE);
796 EXCEPTION
797 WHEN OTHERS THEN
798 retcode := CP_RETCODE_FAILURE;
799 errbuf := 'Fatal error in ' || l_api_name || '.' || l_ndebug || ': ' ||
800 substr(SQLERRM,1,900);
801 END upgrade_configs_by_product_cp;
802
803 ---------------------------------------------------------------------------------------
804 --------------------------- End of Configuration Upgrade ----------------------------
805 ---------------------------------------------------------------------------------------
806
807 /*
808 * This procedure converts a publication target to a Development Instance.
809 * @param errbuf Standard Oracle Concurrent Program output parameters.
810 * @param retcode Standard Oracle Concurrent Program output parameters.
811 */
812
813 PROCEDURE convert_instance_cp(errbuf OUT NOCOPY VARCHAR2,
814 retcode OUT NOCOPY NUMBER
815 ) IS
816
817 ALREADY_MIGRATED_EXCEPTION EXCEPTION;
818
819 l_return_status VARCHAR2(1);
820 l_msg_count NUMBER;
821 l_msg_data VARCHAR2(2000);
822
823 p_run_id NUMBER;
824 l_return BOOLEAN;
825
826 l_new_pb_id t_num_array_tbl_type;
827 l_old_pb_id t_num_array_tbl_type;
828
829 l_inst_str VARCHAR2(8000);
830 l_converted_target_flag VARCHAR2(1) ;
831
832 v_errorString VARCHAR2(1024) :='convert_instance: ';
833
834 BEGIN
835
836 --get the current value for converted flag
837 SELECT converted_target_flag
838 INTO l_converted_target_flag
839 FROM cz_servers
840 WHERE local_name = 'LOCAL';
841
842 IF l_converted_target_flag = '1' THEN
843 RAISE ALREADY_MIGRATED_EXCEPTION;
844 END IF;
845
846 --update the target server flag
847 update cz_servers
848 set converted_target_flag = '1' where
849 LOCAL_NAME = 'LOCAL';
850
851 --update the source server flag
852 --this will ensure that this server will be presented as a
853 --migration target my target_open_for function.
854 update cz_servers
855 set source_server_flag = '0' where
856 source_server_flag = '1';
857 --create a pseudo record for all the target publications
858 SELECT cz_model_publications_s.NEXTVAL, publication_id
859 BULK COLLECT
860 INTO l_new_pb_id, l_old_pb_id
861 FROM cz_model_publications
862 WHERE SOURCE_TARGET_FLAG = 'T'
863 AND cz_model_publications.deleted_flag = '0';
864
865 l_inst_str:= 'INSERT INTO cz_model_publications ' ||
866 ' (PUBLICATION_ID ' ||
867 ' ,MODEL_ID ' ||
868 ' ,OBJECT_ID ' ||
869 ' ,OBJECT_TYPE ' ||
870 ' ,SERVER_ID ' ||
871 ' ,ORGANIZATION_ID ' ||
872 ' ,TOP_ITEM_ID ' ||
873 ' ,PRODUCT_KEY ' ||
874 ' ,PUBLICATION_MODE ' ||
875 ' ,UI_DEF_ID ' ||
876 ' ,UI_STYLE ' ||
877 ' ,APPLICABLE_FROM ' ||
878 ' ,APPLICABLE_UNTIL ' ||
879 ' ,EXPORT_STATUS ' ||
880 ' ,MODEL_PERSISTENT_ID ' ||
881 ' ,DELETED_FLAG ' ||
882 ' ,MODEL_LAST_STRUCT_UPDATE ' ||
883 ' ,MODEL_LAST_LOGIC_UPDATE ' ||
884 ' ,MODEL_LAST_UPDATED ' ||
885 ' ,SOURCE_TARGET_FLAG ' ||
886 ' ,REMOTE_PUBLICATION_ID ' ||
887 ' ,CONTAINER ' ||
888 ' ,PAGE_LAYOUT ' ||
889 ' ,disabled_flag ' ||
890 ' ,converted_target_flag ' ||
891 ' ) ' ||
892 ' SELECT :1 ' ||
893 ' ,MODEL_ID ' ||
894 ' ,OBJECT_ID ' ||
895 ' ,OBJECT_TYPE ' ||
896 ' ,SERVER_ID ' ||
897 ' ,ORGANIZATION_ID ' ||
898 ' ,TOP_ITEM_ID ' ||
899 ' ,PRODUCT_KEY ' ||
900 ' ,PUBLICATION_MODE ' ||
901 ' ,UI_DEF_ID ' ||
902 ' ,UI_STYLE ' ||
903 ' ,APPLICABLE_FROM ' ||
904 ' ,APPLICABLE_UNTIL ' ||
905 ' ,EXPORT_STATUS ' ||
906 ' ,MODEL_PERSISTENT_ID ' ||
907 ' ,DELETED_FLAG ' ||
908 ' ,MODEL_LAST_STRUCT_UPDATE ' ||
909 ' ,MODEL_LAST_LOGIC_UPDATE ' ||
910 ' ,MODEL_LAST_UPDATED ' ||
911 ' ,''S'' ' ||
912 ' ,PUBLICATION_ID ' ||
913 ' ,CONTAINER ' ||
914 ' ,PAGE_LAYOUT ' ||
915 ' ,disabled_flag ' ||
916 ' ,''1'' ' ||
917 ' FROM cz_model_publications ' ||
918 ' WHERE publication_id = :2 ';
919
920 --insert the pseudo record
921 FORALL i IN 1..l_new_pb_id.COUNT
922 EXECUTE IMMEDIATE l_inst_str USING l_new_pb_id(i), l_old_pb_id(i);
923
924 COMMIT;
925
926 EXCEPTION
927 WHEN ALREADY_MIGRATED_EXCEPTION THEN
928 retcode:=2;
929 errbuf:=CZ_UTILS.Get_Text('CZ_ALREADY_CONVERTED_MESSAGE');
930 l_return := cz_utils.log_report(Msg => errbuf,
931 Urgency => 1,
932 ByCaller => 'CZ_MODEL_MIGRATION',
933 StatusCode => 11276,
934 RunId => p_run_id);
935 WHEN OTHERS THEN
936 rollback;
937 retcode:=2;
938 errbuf:=SQLERRM;
939 l_return := cz_utils.log_report(Msg => errbuf,
940 Urgency => 1,
941 ByCaller => 'CZ_MODEL_MIGRATION',
942 StatusCode => 11276,
943 RunId => p_run_id);
944 END;
945 ---------------------------------------------------------------------------------------
946 /*
947 * Once the target has been converted to a development instance,
948 * obselete all the source publications to that target.
949 */
950
951 PROCEDURE obsolete_nonpublishable(
952 p_commit_flag IN VARCHAR2,
953 x_return_status OUT NOCOPY VARCHAR2,
954 x_msg_count OUT NOCOPY NUMBER,
955 x_msg_data OUT NOCOPY VARCHAR2
956 )
957 IS
958 l_server_id NUMBER ;
959 r_instance_name_tbl t_varchar40_array_tbl_type;
960 obselete_exists VARCHAR2(1) := '0';
961 r_instance_name VARCHAR2(40);
962
963
964
965 BEGIN
966
967
968 FND_MSG_PUB.initialize;
969 x_return_status := FND_API.G_RET_STS_SUCCESS;
970
971 --get the instance name(s) that has been converted
972 r_instance_name_tbl := get_target_name_if_converted();
973
974
975 --we need to display a message in Developer
976 --if there are obseleted records.
977 --for each of the instances that have converted, check if there
978 --are obseleted records
979 IF ( r_instance_name_tbl.COUNT > 0) THEN
980
981 FOR i IN r_instance_name_tbl.FIRST..r_instance_name_tbl.LAST LOOP
982 r_instance_name := r_instance_name_tbl(i);
983
984 IF (r_instance_name IS NULL) THEN
985 RETURN;
986 END IF;
987
988 --get the local server id of the instance name
989 SELECT server_local_id
990 INTO l_server_id
991 FROM cz_servers
992 WHERE UPPER (local_name) = UPPER (r_instance_name);
993
994
995 obselete_exists := '0';
996
997 -- check if obselete exists
998 BEGIN
999
1000 SELECT '1'
1001 INTO obselete_exists
1002 FROM DUAL
1003 WHERE EXISTS (
1004 SELECT publication_id
1005 FROM cz_model_publications
1006 WHERE source_target_flag = 'S'
1007 AND deleted_flag = '0'
1008 AND export_status <> MODEL_PUBLICATION_OBSELETE
1009 AND server_id = l_server_id);
1010
1011 EXCEPTION
1012 WHEN NO_DATA_FOUND THEN
1013 obselete_exists := '0';
1014 END;
1015
1016 --if obselete exists
1017
1018 IF (obselete_exists = '1') THEN
1019
1020 x_return_status := FND_API.G_RET_STS_ERROR;
1021
1022 --add message to the error stack
1023 CZ_UTILS.add_error_message_to_stack(p_message_name => 'CZ_OBSELETE_RECORDS_EXISTS',
1024 p_token_name1 => 'INSTANCE_NAME',
1025 p_token_value1 => r_instance_name,
1026 x_msg_count => x_msg_count,
1027 x_msg_data => x_msg_data);
1028
1029
1030 --update all publications for local server id
1031 UPDATE cz_model_publications
1032 set export_status = MODEL_PUBLICATION_OBSELETE
1033 WHERE SOURCE_TARGET_FLAG = 'S'
1034 AND deleted_flag = '0'
1035 AND export_status <> MODEL_PUBLICATION_OBSELETE
1036 AND server_id = l_server_id;
1037
1038 END IF;
1039
1040 END LOOP;
1041
1042 END IF;
1043
1044 IF (p_commit_flag = FND_API.G_TRUE) THEN
1045 COMMIT;
1046 END IF;
1047
1048 EXCEPTION
1049 WHEN others THEN
1050 CZ_UTILS.add_exc_msg_to_fndstack(
1051 p_package_name => 'CZ_MODEL_MIGRATION',
1052 p_procedure_name => 'obselete_nonpublishable',
1053 p_error_message => SQLERRM);
1054
1055
1056 END;
1057 ---------------------------------------------------------------------------------------
1058 /* The target machine may have been convrted into
1059 * a Developer enabled, migratable machine.
1060 * If so, the target is publishable no more.
1061
1062 * This method will also be called from Developer
1063 * when a drop down of eligible targets is loaded
1064 * at the time of creating or editing a publication
1065 */
1066
1067 FUNCTION target_open_for (
1068 p_migration_or_publishing IN VARCHAR2,
1069 p_link_name IN VARCHAR2,
1070 p_local_name IN VARCHAR2
1071 ) RETURN VARCHAR2
1072 IS
1073 target_open VARCHAR2 (1) := '1';
1074 target_not_open VARCHAR2 (1) := '0';
1075 l_source_server_flag VARCHAR2 (1);
1076 l_source_server_flag_for_pub VARCHAR2 (1);
1077 l_sql_str VARCHAR2 (2000);
1078 l_sql_str_for_pub VARCHAR2 (2000);
1079 target_instance_for_pub VARCHAR2(2000);
1080 local_instance_name VARCHAR2(2000);
1081
1082 BEGIN
1083
1084 IF (p_migration_or_publishing = MODE_PUBLICATION) THEN
1085 IF (p_local_name='LOCAL') THEN
1086 RETURN target_open;
1087 END IF;
1088 END IF;
1089
1090 IF p_link_name IS NULL THEN
1091 RETURN target_not_open;
1092 END IF;
1093
1094 l_sql_str :=
1095 'SELECT NVL(source_server_flag,''0''), local_name FROM cz_servers@' || p_link_name || ' ' ||
1096 'WHERE source_server_flag = ''1'' ';
1097
1098 EXECUTE IMMEDIATE l_sql_str
1099 INTO l_source_server_flag, target_instance_for_pub;
1100
1101
1102 IF (p_migration_or_publishing = MODE_PUBLICATION) THEN
1103
1104 SELECT instance_name
1105 INTO local_instance_name
1106 FROM cz_servers
1107 WHERE local_name = 'LOCAL';
1108
1109 IF (local_instance_name=target_instance_for_pub) THEN
1110 RETURN target_open;
1111 END IF;
1112 END IF;
1113
1114 RETURN target_not_open;
1115
1116 EXCEPTION
1117 WHEN NO_DATA_FOUND THEN
1118 RETURN target_open;
1119 WHEN OTHERS THEN
1120 RETURN target_not_open;
1121 END;
1122 ---------------------------------------------------------------------------------------
1123 /*
1124 * For a given link name, get the converted target name.
1125 * The target machine may have been convrted into
1126 * a Developer enabled, migratable machine.
1127 * This method will get the converted target instance name
1128 */
1129
1130 FUNCTION get_converted_target(p_link_name IN VARCHAR2, p_instance_name IN VARCHAR2) RETURN VARCHAR2
1131 is
1132
1133
1134 l_instance_name VARCHAR(40);
1135 r_converted_target_flag VARCHAR(1);
1136 r_instance_name VARCHAR(40);
1137
1138 TYPE ref_cursor IS REF CURSOR;
1139 gl_ref_cursor ref_cursor;
1140
1141 BEGIN
1142
1143 --get the local name in the source server,
1144 --we will look for this instance in the remote target to check
1145 --if publication is still allowed.
1146
1147 IF p_link_name IS NULL then
1148 RETURN NULL;
1149 END IF;
1150
1151 -- check if publishable, this query will using the link on the remote machine
1152 OPEN gl_ref_cursor FOR 'SELECT converted_target_flag
1153 FROM cz_servers@'||p_link_name || ' where converted_target_flag = ''1''
1154 and local_name = ''LOCAL'' ';
1155 LOOP
1156 FETCH gl_ref_cursor INTO r_converted_target_flag;
1157 IF (r_converted_target_flag = TARGET_SERVER_PUBLISH_NOTALLOW) THEN
1158 CLOSE gl_ref_cursor ;
1159 RETURN p_instance_name;
1160 EXIT;
1161 END if;
1162 EXIT WHEN gl_ref_cursor%NOTFOUND;
1163 END LOOP;
1164 CLOSE gl_ref_cursor ;
1165
1166 RETURN NULL;
1167
1168 -- there may be exceptions where the remote link may not
1169 -- be available, the target may be at a different schema level
1170 -- just return the converted_target_flag as being allowed to
1171 -- publish in this case
1172
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 RETURN r_converted_target_flag;
1176
1177 END;
1178 ---------------------------------------------------------------------------------------
1179
1180 /* The target machine may have been convrted into
1181 * a Developer enabled, migratable machine.
1182 * If so, the target is publishable no more
1183 */
1184
1185 FUNCTION get_target_name_if_converted RETURN t_varchar40_array_tbl_type
1186 is
1187
1188 l_instance_name_tbl t_varchar40_array_tbl_type;
1189 l_link_name_tbl t_varchar40_array_tbl_type ;
1190
1191 r_converted_target VARCHAR2(40);
1192 r_converted_target_tbl t_varchar40_array_tbl_type;
1193 r_converted_target_count PLS_INTEGER :=1;
1194
1195 TYPE ref_cursor IS REF CURSOR;
1196 gl_ref_cursor ref_cursor;
1197
1198 BEGIN
1199
1200 -- collect the link name of the publishing target
1201 SELECT fndnam_link_name, instance_name
1202 BULK COLLECT
1203 INTO l_link_name_tbl, l_instance_name_tbl
1204 FROM cz_servers
1205 WHERE local_name <> 'LOCAL' AND FNDNAM_LINK_NAME IS NOT NULL ;
1206
1207 -- for each of the link_names collected, check if the
1208 -- remote target is still publishable
1209
1210 FOR i IN l_link_name_tbl.FIRST..l_link_name_tbl.LAST LOOP
1211 r_converted_target := get_converted_target(l_link_name_tbl(i), l_instance_name_tbl(i));
1212 IF (r_converted_target IS NOT NULL) THEN
1213 r_converted_target_tbl(r_converted_target_count) := r_converted_target;
1214 r_converted_target_count := r_converted_target_count + 1;
1215 END IF;
1216 END LOOP;
1217
1218 RETURN r_converted_target_tbl;
1219
1220 END;
1221 ---------------------------------------------------------------------------------------
1222 END;
[Home] [Help]