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