[Home] [Help]
PACKAGE BODY: APPS.CZ_NETWORK_API_PUB
Source
1 PACKAGE BODY cz_network_api_pub AS
2 /* $Header: czntapib.pls 120.10 2012/01/24 04:28:08 spitre ship $ */
3 ------------------------------------------------------------------------------------------
4 -- model_type/model_instantiation_type
5 NETWORK CONSTANT VARCHAR2(1) := 'N';
6
7 -- mtl_system_items.config_model_type
8 NETWORK_CONTAINER_MODEL CONSTANT VARCHAR2(1) := 'N';
9
10 -- component_instance_type
11 ROOT CONSTANT VARCHAR2(1) := 'R';
12 INSTANCE_ROOT CONSTANT VARCHAR2(1) := 'I';
13 INCLUDED CONSTANT VARCHAR2(1) := 'T';
14
15
16 NO_PARENT_VALUE CONSTANT PLS_INTEGER := -1;
17 INSTANTIABLE CONSTANT PLS_INTEGER := 4;
18 PS_NODE_TYPE_REFERENCE CONSTANT PLS_INTEGER := 263;
19 NO_FLAG CONSTANT VARCHAR2(1) := '0';
20 YES_FLAG CONSTANT VARCHAR2(1) := '1';
21
22 type VARCHAR2_TBL_TYPE is table of VARCHAR2(1200);
23
24 ----declaration used for grouping instances and keyed by inv item id
25 TYPE container_model_rec_type IS RECORD
26 (
27 inventory_item_id NUMBER,
28 organization_id NUMBER,
29 config_item_id NUMBER
30 );
31 TYPE container_model_tbl_type IS TABLE OF container_model_rec_type INDEX BY BINARY_INTEGER;
32
33 TYPE container_config_rec_type IS RECORD
34 (
35 inventory_item_id NUMBER,
36 config_hdr_id NUMBER,
37 config_rev_nbr NUMBER
38 );
39 TYPE container_config_tbl_type IS TABLE OF container_config_rec_type INDEX BY BINARY_INTEGER;
40
41 G_INCOMPATIBLE_API EXCEPTION;
42
43 -----declaration for debug message table
44 v_msg_tbl CZ_DEBUG_PUB.msg_text_list ;
45
46 -------function that extracts the config err msg from xml
47 FUNCTION get_terminate_msg(p_str IN LONG) RETURN VARCHAR2
48 IS
49 l_start_tag VARCHAR2(20) := '<message_text>';
50 l_end_tag VARCHAR2(20) := '</message_text>';
51 l_start_msg VARCHAR2(2000);
52 l_start_instr NUMBER;
53 l_end_instr NUMBER;
54 l_str_len NUMBER;
55
56 BEGIN
57 l_start_instr := INSTR(p_str,l_start_tag);
58 l_str_len := LENGTH(p_str);
59 l_start_msg := SUBSTR(p_str,l_start_instr);
60 l_start_msg := SUBSTR(l_start_msg,15,l_str_len);
61 l_end_instr := INSTR(l_start_msg,l_end_tag);
62 l_start_msg := SUBSTR(l_start_msg,1,l_end_instr-1);
63 l_start_msg := SUBSTR(l_start_msg,1,2000);
64 RETURN l_start_msg;
65 EXCEPTION
66 WHEN OTHERS THEN
67 l_start_msg := SUBSTR(p_str,1,2000);
68 RETURN l_start_msg;
69 END get_terminate_msg;
70
71 ----procedure that populates debug messages
72 PROCEDURE populate_debug_message(p_msg IN VARCHAR2,
73 p_caller IN VARCHAR2,
74 p_sqlcode IN NUMBER)
75 IS
76 l_caller cz_db_logs.caller%TYPE;
77
78 BEGIN
79 IF (p_caller IS NULL) THEN
80 l_caller := 'CZ_NETWORK_API';
81 ELSE
82 l_caller := p_caller;
83 END IF;
84 CZ_DEBUG_PUB.populate_debug_message(p_msg,p_caller,NVL(p_sqlcode,0),v_msg_tbl);
85 END populate_debug_message;
86 ------------------------------------------------------------------------
87 -----procedure that writes messages to the FND stack
88 PROCEDURE set_fnd_message(inMessageName IN VARCHAR2,
89 inToken1 IN VARCHAR2 DEFAULT NULL, inValue1 IN VARCHAR2 DEFAULT NULL,
90 inToken2 IN VARCHAR2 DEFAULT NULL, inValue2 IN VARCHAR2 DEFAULT NULL,
91 inToken3 IN VARCHAR2 DEFAULT NULL, inValue3 IN VARCHAR2 DEFAULT NULL,
92 inToken4 IN VARCHAR2 DEFAULT NULL, inValue4 IN VARCHAR2 DEFAULT NULL,
93 inToken5 IN VARCHAR2 DEFAULT NULL, inValue5 IN VARCHAR2 DEFAULT NULL,
94 inToken6 IN VARCHAR2 DEFAULT NULL, inValue6 IN VARCHAR2 DEFAULT NULL
95 )
96 IS
97
98 BEGIN
99 FND_MESSAGE.SET_NAME('CZ', inMessageName);
100 IF (inToken1 IS NOT NULL) THEN
101 FND_MESSAGE.SET_TOKEN(inToken1, inValue1);
102 END IF;
103
104 IF (inToken2 IS NOT NULL) THEN
105 FND_MESSAGE.SET_TOKEN(inToken2, inValue2);
106 END IF;
107
108 IF (inToken3 IS NOT NULL) THEN
109 FND_MESSAGE.SET_TOKEN(inToken3, inValue3);
110 END IF;
111
112 IF (inToken4 IS NOT NULL) THEN
113 FND_MESSAGE.SET_TOKEN(inToken4, inValue4);
114 END IF;
115
116 IF (inToken5 IS NOT NULL) THEN
117 FND_MESSAGE.SET_TOKEN(inToken5, inValue5);
118 END IF;
119
120 IF (inToken6 IS NOT NULL) THEN
121 FND_MESSAGE.SET_TOKEN(inToken6, inValue6);
122 END IF;
123
124 FND_MSG_PUB.ADD;
125 END set_fnd_message;
126
127 -------------------------------------------------------------------------
128 -------calling applications default publication parameters to G_MISS_DATE, G_MISS_CHAR etc
129 -------In such cases default the parameter to NULL.
130 ----default pb applicability parameters to NULL if no values are passed in
131 PROCEDURE default_pb_parameters(p_appl_param_rec IN OUT NOCOPY CZ_API_PUB.appl_param_rec_type,
132 x_return_status IN OUT NOCOPY VARCHAR2)
133 IS
134 APPLID_NOT_FOUND EXCEPTION;
135
136 BEGIN
137
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139
140 IF (p_appl_param_rec.config_creation_date = FND_API.G_MISS_DATE) THEN
141 p_appl_param_rec.config_creation_date := NULL;
142 END IF;
143
144 IF (p_appl_param_rec.config_model_lookup_date = FND_API.G_MISS_DATE) THEN
145 p_appl_param_rec.config_model_lookup_date := NULL;
146 END IF;
147
148 IF (p_appl_param_rec.config_effective_date = FND_API.G_MISS_DATE) THEN
149 p_appl_param_rec.config_effective_date := NULL;
150 END IF;
151
152 IF (p_appl_param_rec.usage_name = FND_API.G_MISS_CHAR) THEN
153 p_appl_param_rec.usage_name := NULL;
154 END IF;
155
156 IF (p_appl_param_rec.publication_mode = FND_API.G_MISS_CHAR) THEN
157 p_appl_param_rec.publication_mode := NULL;
158 END IF;
159
160 IF (p_appl_param_rec.language = FND_API.G_MISS_CHAR) THEN
161 p_appl_param_rec.language := NULL;
162 END IF;
163
164 IF ( (p_appl_param_rec.calling_application_id = FND_API.G_MISS_NUM) OR (p_appl_param_rec.calling_application_id IS NULL) ) THEN
165 RAISE APPLID_NOT_FOUND;
166 END IF;
167 EXCEPTION
168 WHEN APPLID_NOT_FOUND THEN
169 x_return_status := FND_API.G_RET_STS_ERROR;
170 set_fnd_message('CZ_NET_APPL_ID_ISNULL',null,null,null,null);
171 WHEN OTHERS THEN
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 END default_pb_parameters;
174 -----------------------------------------------------------------------
175 ----procedure that sets the the return status to error
176 ----and populates the error message stack
177 PROCEDURE set_error_message(p_err_code IN OUT NOCOPY VARCHAR2,
178 p_msg_count IN OUT NOCOPY NUMBER,
179 p_msg_data IN OUT NOCOPY VARCHAR2,
180 p_err_msg IN VARCHAR2)
181 IS
182
183 BEGIN
184 p_err_code := FND_API.G_RET_STS_ERROR;
185 fnd_msg_pub.count_and_get(p_count => p_msg_count,p_data => p_msg_data);
186 populate_debug_message(p_err_msg,NULL,NULL);
187 CZ_DEBUG_PUB.insert_into_logs(v_msg_tbl);
188 END set_error_message;
189
190 ---------------------------------------------------------------------------
191 ----procedure that deletes the saved configurations
192 PROCEDURE delete_configuration(p_config_model_tbl IN cz_api_pub.config_model_tbl_type)
193 IS
194 PRAGMA AUTONOMOUS_TRANSACTION;
195 l_err_buf VARCHAR2(2000);
196 l_msg_data VARCHAR2(2000);
197 l_msg_count NUMBER;
198 l_err_code VARCHAR2(30);
199 l_usage_exists NUMBER;
200 l_error_message VARCHAR2(2000);
201 l_Return_value NUMBER;
202
203 BEGIN
204 IF (p_config_model_tbl.COUNT > 0) THEN
205 FOR I IN p_config_model_tbl.FIRST..p_config_model_tbl.LAST
206 LOOP
207 -- this needs to change to call cz_cf_api.delete_configuration
208 cz_cf_api.delete_configuration(p_config_model_tbl(i).config_hdr_id,
209 p_config_model_tbl(i).config_rev_nbr,
210 l_usage_exists,
211 l_error_message,
212 l_Return_value);
213 IF (l_Return_value <> 1) THEN
214 l_err_buf := CZ_UTILS.GET_TEXT('CZ_NET_API_DEL_CFG_ERR','ConfigHdrId',
215 p_config_model_tbl(i).config_hdr_id,'ConfigRevNbr',p_config_model_tbl(i).config_rev_nbr);
216 END IF;
217 END LOOP;
218 END IF;
219 EXCEPTION
220 WHEN OTHERS THEN
221 l_err_buf := CZ_UTILS.GET_TEXT('CZ_NET_API_DEL_CONFIG_ERR');
222 set_error_message(l_err_code,l_msg_count,l_msg_data,l_err_buf);
223 set_error_message(l_err_code,l_msg_count,l_msg_data,'delete config err');
224 END delete_configuration;
225 ---------------------------------------------------------------------------
226 ----procedure that retrieves the model instantiation type and
227 ----component instance type for a config hdr and rev nbr
228 PROCEDURE get_header_types(p_config_hdr_id IN NUMBER,
229 p_config_rev_nbr IN NUMBER,
230 x_model_instantiation_type OUT NOCOPY VARCHAR2,
231 x_component_instance_type OUT NOCOPY VARCHAR2)
232 IS
233
234 BEGIN
235 SELECT model_instantiation_type,
236 component_instance_type
237 INTO x_model_instantiation_type,
238 x_component_instance_type
239 FROM cz_config_hdrs
240 WHERE cz_config_hdrs.config_hdr_id = p_config_hdr_id
241 AND cz_config_hdrs.config_rev_nbr = p_config_rev_nbr;
242 EXCEPTION
243 WHEN OTHERS THEN
244 x_model_instantiation_type := '0';
245 x_component_instance_type := '0';
246 END get_header_types;
247
248 --------------------------------------------------------------------------
249 ----procedure that retrieves the root inventory item id and organization id for a
250 ----given config_hdr_id and rev nbr
251 procedure get_root_bom_config_item(p_config_hdr_id IN NUMBER,
252 p_config_rev_nbr IN NUMBER,
253 x_inventory_item_id OUT NOCOPY NUMBER,
254 x_organization_id OUT NOCOPY NUMBER,
255 x_config_item_id OUT NOCOPY NUMBER)
256 IS
257
258 BEGIN
259 SELECT config_item_id, inventory_item_id, organization_id
260 INTO x_config_item_id, x_inventory_item_id, x_organization_id
261 FROM cz_config_items
262 WHERE config_hdr_id = p_config_hdr_id
263 AND config_rev_nbr = p_config_rev_nbr
264 AND deleted_flag = '0'
265 AND inventory_item_id IS NOT NULL
266 START WITH (parent_config_item_id IS NULL OR parent_config_item_id = -1)
267 AND config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
268 CONNECT BY PRIOR inventory_item_id IS NULL
269 AND parent_config_item_id = PRIOR config_item_id
270 AND config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr;
271 EXCEPTION
272 WHEN OTHERS THEN
273 x_inventory_item_id := -1;
274 x_organization_id := -1;
275 END get_root_bom_config_item;
276
277 --------------------------------------------------------------------------
278 PROCEDURE get_container_header(p_config_hdr_id NUMBER,
279 p_config_rev_nbr NUMBER,
280 x_container_hdr_id OUT NOCOPY NUMBER,
281 x_container_rev_nbr OUT NOCOPY NUMBER)
282 IS
283
284 BEGIN
285 ----get container header
286 SELECT config_hdr_id, config_rev_nbr
287 INTO x_container_hdr_id, x_container_rev_nbr
288 FROM cz_config_items
289 WHERE cz_config_items.instance_hdr_id = p_config_hdr_id
290 AND cz_config_items.instance_rev_nbr = p_config_rev_nbr
291 AND cz_config_items.deleted_flag = '0'
292 AND ROWNUM < 2;
293 EXCEPTION
294 WHEN OTHERS THEN
295 x_container_hdr_id := -1;
296 x_container_rev_nbr := -1;
297 END get_container_header;
298
299 --------------------------------------------------------------------------
300 PROCEDURE check_if_item_exists(p_inv_item_id IN NUMBER,
301 p_org_id IN NUMBER,
302 container_model_tbl IN container_model_tbl_type,
303 x_out_index OUT NOCOPY NUMBER,
304 x_flag OUT NOCOPY BOOLEAN)
305 IS
306
307 BEGIN
308 x_flag := FALSE;
309 x_out_index := 0;
310 IF (container_model_tbl.COUNT > 0) THEN
311 FOR I IN container_model_tbl.FIRST..container_model_tbl.LAST
312 LOOP
313 IF ( (container_model_tbl(i).inventory_item_id = p_inv_item_id) AND
314 (container_model_tbl(i).organization_id = p_org_id) ) THEN
315 x_flag := TRUE;
316 x_out_index := i;
317 END IF;
318 END LOOP;
319 END IF;
320 EXCEPTION
321 WHEN OTHERS THEN
322 x_flag := FALSE;
323 END check_if_item_exists;
324
325 -------------------------------------------------------------------------
326 PROCEDURE validate_org_id(container_model_tbl IN container_model_tbl_type,
327 x_flag OUT NOCOPY BOOLEAN)
328 IS
329 prev_org_id NUMBER;
330
331 BEGIN
332 x_flag := TRUE;
333 IF (container_model_tbl.COUNT > 0) THEN
334 FOR I IN container_model_tbl.FIRST..container_model_tbl.LAST
335 LOOP
336 prev_org_id := container_model_tbl(i).organization_id;
337 IF (i <> container_model_tbl.COUNT) THEN
338 IF (prev_org_id <> container_model_tbl(i+1).organization_id) THEN
339 x_flag := FALSE;
340 EXIT;
341 END IF;
342 END IF;
343 END LOOP;
344 END IF;
345 EXCEPTION
346 WHEN OTHERS THEN
347 x_flag := FALSE;
348 END validate_org_id;
349
350 -------------------------------------------------------------------------
351 ------procedure that sets the deltas to 0 for the new configuration
352 ------after a call is made to copy configuration
353 ------deltas for nodes above trackable root are not set to 0, otherwise
354 ------the container model will be filtered from config_details_v
355 PROCEDURE reset_config_delta (p_config_hdr_id IN NUMBER,
356 p_config_rev_nbr IN NUMBER)
357 IS
358
359 BEGIN
360 UPDATE cz_config_items
361 SET config_delta = 0
362 WHERE config_hdr_id = p_config_hdr_id
363 AND config_rev_nbr = p_config_rev_nbr
364 /* Need to verify */
365 AND instance_hdr_id <> p_config_hdr_id;
366 END reset_config_delta ;
367
368 -- Sungard ER Bug7673721
369 -------------------------------------------------------------------------
370 ------procedure sets the line type to 0 and ext_activated_flag to '1' for
371 ------the new configuration after a call is made to copy configuration in
372 ------generate_config_trees
373 PROCEDURE reset_config_flags (p_config_hdr_id IN NUMBER,
374 p_config_rev_nbr IN NUMBER)
375 IS
376
377 l_prof_val VARCHAR2(10);
378
379 BEGIN
380 l_prof_val := fnd_profile.value('CZ_OUTPUT_IB_UNCHD_CHILD_ITEMS');
381
382 -- if unchanged items not output, only set ext_activated_flag to '1' on instance root
383 -- otherwise set to '1' on all items in trackable instances
384 UPDATE cz_config_items
385 SET line_type = NULL,
386 ext_activated_flag = Decode(l_prof_val, 'N', (Decode (component_instance_type, 'I', '1', '0')), '1')
387 WHERE config_hdr_id = p_config_hdr_id
388 AND config_rev_nbr = p_config_rev_nbr
389 AND instance_hdr_id <> p_config_hdr_id;
390 END reset_config_flags ;
391 --------------------------------------------------------------------------
392 PROCEDURE write_dummy_config(p_inventory_id IN NUMBER,
393 p_inst_hdr_id_tbl IN container_config_tbl_type,
394 x_dummy_hdr_id OUT NOCOPY NUMBER)
395 IS
396
397 --These values do not really matter. We only provide them because corresponding columns
398 --are not null.
399
400 ANY_USAGE_ID CONSTANT NUMBER := -1;
401 NETWORK CONSTANT VARCHAR2(1) := 'N';
402 ROOT CONSTANT VARCHAR2(1) := 'R';
403 INPUTTTYPECODE CONSTANT NUMBER := 7;
404
405 TYPE t_local_number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
406
407 l_inst_header_id_tbl t_local_number_tbl;
408 l_input_id_tbl t_local_number_tbl;
409
410 BEGIN
411
412 SELECT cz_config_hdrs_s.NEXTVAL INTO x_dummy_hdr_id FROM DUAL;
413
414 FOR i IN 1..p_inst_hdr_id_tbl.COUNT LOOP
415
416 IF(p_inst_hdr_id_tbl(i).inventory_item_id = p_inventory_id)THEN
417
418 l_inst_header_id_tbl(l_inst_header_id_tbl.COUNT + 1) := p_inst_hdr_id_tbl(i).config_hdr_id;
419 l_input_id_tbl(l_inst_header_id_tbl.COUNT) := l_inst_header_id_tbl.COUNT;
420 END IF;
421 END LOOP;
422
423 INSERT INTO cz_config_hdrs ( config_hdr_id
424 , config_rev_nbr
425 , name
426 , desc_text
427 , effective_usage_id
428 , deleted_flag
429 , config_delta_spec
430 , component_instance_type
431 , model_instantiation_type
432 , has_failures
433 )
434 VALUES ( x_dummy_hdr_id
435 , 1
436 , 'generate/add_to_config_tree'
437 , 'generate/add_to_config_tree'
438 , ANY_USAGE_ID
439 , '0'
440 , 0
441 , ROOT
442 , NETWORK
443 , 0
444 );
445
446 FORALL i IN 1..l_inst_header_id_tbl.COUNT
447 INSERT INTO cz_config_contents_v ( config_hdr_id
448 , config_rev_nbr
449 , config_input_id
450 , input_seq
451 , input_type_code
452 , input_num_val
453 , item_num_val
454 , config_item_id
455 , item_type_code
456 , instance_hdr_id
457 , instance_rev_nbr
458 , component_instance_type
459 , config_delta
460 )
461 VALUES ( x_dummy_hdr_id
462 , 1
463 , l_input_id_tbl(i)
464 , l_input_id_tbl(i)
465 , INPUTTTYPECODE
466 , l_inst_header_id_tbl(i)
467 , l_inst_header_id_tbl(i)
468 , -1 * l_input_id_tbl(i)
469 , -1
470 , x_dummy_hdr_id
471 , 1
472 , 'R'
473 , 0
474 );
475 END write_dummy_config;
476
477 --------------------------------------------------------------------------
478 PROCEDURE delete_dummy_config(p_dummy_hdr_id IN NUMBER)
479 IS
480
481 v_cfg_delete cz_db_settings.value%TYPE;
482
483 BEGIN
484
485 -----delete based on setting in cz_db_settings
486 BEGIN
487 SELECT value INTO v_cfg_delete
488 FROM cz_db_settings
489 WHERE setting_id = 'BatchValConfigInputDelete';
490 EXCEPTION
491 WHEN OTHERS THEN
492 v_cfg_delete := 'NO';
493 END;
494
495 IF(v_cfg_delete <> 'YES')THEN
496
497 DELETE FROM cz_config_contents_v WHERE config_hdr_id = p_dummy_hdr_id
498 AND config_rev_nbr = 1;
499
500 DELETE FROM cz_config_hdrs WHERE config_hdr_id = p_dummy_hdr_id
501 AND config_rev_nbr = 1;
502 END IF;
503 END delete_dummy_config;
504
505 --------------------------------------------------------------------------
506 ------procedure that creates the init msg
507 PROCEDURE create_hdr_xml( p_inventory_id IN NUMBER,
508 p_organization_id IN NUMBER,
509 p_config_hdr_id IN NUMBER,
510 p_config_rev_nbr IN NUMBER,
511 p_dummy_header_id IN NUMBER,
512 p_appl_params IN CZ_API_PUB.appl_param_rec_type,
513 p_tree_copy_mode IN VARCHAR2,
514 p_valid_context IN VARCHAR2,
515 x_xml_hdr OUT NOCOPY VARCHAR2 )
516 IS
517
518 TYPE param_name_type IS TABLE OF VARCHAR2(30)INDEX BY BINARY_INTEGER;
519 TYPE param_value_type IS TABLE OF VARCHAR2(200)INDEX BY BINARY_INTEGER;
520 param_name param_name_type;
521 param_value param_value_type;
522
523 l_rec_index BINARY_INTEGER;
524
525 l_database_id VARCHAR2(100);
526 l_save_config_behavior VARCHAR2(30):= CZ_API_PUB.G_NEW_REVISION_COPY_MODE;
527 l_ui_type VARCHAR2(30):= null;
528 l_msg_behavior VARCHAR2(30):= 'brief';
529 l_context_org_id VARCHAR2(80);
530 l_inventory_item_id VARCHAR2(80);
531 l_config_header_id VARCHAR2(80);
532 l_config_rev_nbr VARCHAR2(80);
533 l_model_quantity VARCHAR2(80);
534 l_count NUMBER;
535
536 -- message related
537 l_xml_hdr VARCHAR2(32767):= '<initialize>';
538 l_dummy VARCHAR2(500) := NULL;
539 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
540
541 BEGIN
542 ---- now set the values from model_rec and org_id
543 l_context_org_id := to_char(p_organization_id);
544 l_inventory_item_id := to_char(p_inventory_id);
545 l_config_header_id := to_char(p_config_hdr_id);
546 l_config_rev_nbr := to_char(p_config_rev_nbr);
547
548 -- profiles and env. variables.
549 l_database_id := fnd_web_config.database_id;
550
551 -- set param_names
552 param_name(1) := 'database_id';
553 param_name(2) := 'context_org_id';
554 param_name(3) := 'config_creation_date';
555 param_name(4) := 'calling_application_id';
556 param_name(5) := 'responsibility_id';
557 param_name(6) := 'model_id';
558 param_name(7) := 'config_header_id';
559 param_name(8) := 'config_rev_nbr';
560 param_name(9) := 'config_effective_date';
561 param_name(10) := 'save_config_behavior';
562 param_name(11) := 'ui_type';
563 param_name(12) := 'language';
564 param_name(13) := 'terminate_msg_behavior';
565 param_name(14) := 'model_quantity';
566 param_name(15) := 'icx_session_ticket';
567 param_name(16) := 'publication_mode';
568 param_name(17) := 'usage_name';
569 param_name(18) := 'sbm_flag';
570 param_name(19) := 'validation_context';
571 param_name(20) := 'suppress_baseline_errors';
572
573 l_count := 20;
574
575 -- set param values
576 param_value(1) := l_database_id;
577 param_value(2) := l_context_org_id;
578 param_value(3) := to_char(sysdate,'MM-DD-YYYY-HH24-MI-SS');
579 param_value(4) := p_appl_params.calling_application_id;
580 param_value(5) := fnd_profile.value('RESP_ID');
581 param_value(6) := l_inventory_item_id;
582 param_value(7) := l_config_header_id;
583 param_value(8) := l_config_rev_nbr;
584
585 -- config_effective_date should have LONG DATE FORMAT --
586 param_value(9) := to_char(p_appl_params.config_effective_date,'MM-DD-YYYY-HH24-MI-SS');
587
588 IF (p_tree_copy_mode = CZ_API_PUB.G_NEW_HEADER_COPY_MODE) THEN
589 l_save_config_behavior := 'new_config';
590 ELSIF (p_tree_copy_mode = CZ_API_PUB.G_NEW_REVISION_COPY_MODE) THEN
591 l_save_config_behavior := 'new_revision';
592 END IF;
593
594 param_value(10) := l_save_config_behavior;
595 param_value(11) := l_ui_type;
596 param_value(12) := p_appl_params.language;
597 param_value(13) := l_msg_behavior;
598 param_value(14) := l_model_quantity;
599 param_value(15) := cz_cf_api.icx_session_ticket;
600 param_value(16) := p_appl_params.publication_mode;
601 param_value(17) := p_appl_params.usage_name;
602 param_value(18) := 'TRUE';
603
604 IF (p_valid_context = 'I') THEN
605 param_value(19) := 'INSTALLED';
606 ELSIF (p_valid_context = 'P') THEN
607 param_value(19) := 'PENDING_OR_INSTALLED';
608 ELSE
609 param_value(19) := 'I';
610 END IF;
611 param_value(20) := 'TRUE';
612
613 l_rec_index := 1;
614 LOOP
615 ----- ex : <param name="config_header_id">1890</param>
616 IF (param_value(l_rec_index) IS NOT NULL) THEN
617 l_dummy := '<param name='||'"'||param_name(l_rec_index)||'"'||'>'|| param_value(l_rec_index)||'</param>';
618 l_xml_hdr := l_xml_hdr || l_dummy;
619 END IF;
620 l_dummy := NULL;
621 l_rec_index := l_rec_index + 1;
622 EXIT WHEN l_rec_index > l_count;
623 END LOOP;
624
625 -- add termination tags
626 l_xml_hdr := l_xml_hdr || '</initialize>';
627
628 --add the config instance section
629 l_xml_hdr := l_xml_hdr ||
630 '<config_instance>' ||
631 '<config_header_id>' || TO_CHAR(p_dummy_header_id) || '</config_header_id>' ||
632 '<config_rev_nbr>1</config_rev_nbr>' ||
633 '</config_instance>';
634
635 l_xml_hdr := REPLACE(l_xml_hdr, ' ' , '+');
636 x_xml_hdr := l_xml_hdr;
637
638 ----added for debugging
639 populate_debug_message(l_xml_hdr,'CREATE_HDR_XML',NULL);
640
641 EXCEPTION
642 WHEN OTHERS THEN
643 ----is error to be logged ? if so what ?
644 RAISE;
645 END create_hdr_xml;
646
647 --------------------------------------------------------------------------
648 PROCEDURE parse_output_xml(p_xml IN LONG,
649 x_config_header_id OUT NOCOPY NUMBER,
650 x_config_rev_nbr OUT NOCOPY NUMBER,
651 x_return_status OUT NOCOPY VARCHAR2)
652 IS
653
654 CURSOR messages(p_config_hdr_id NUMBER, p_config_rev_nbr NUMBER) IS
655 SELECT constraint_type , message
656 FROM cz_config_messages
657 WHERE config_hdr_id = p_config_hdr_id
658 AND config_rev_nbr = p_config_rev_nbr;
659 l_exit_start_tag VARCHAR2(20) := '<EXIT>';
660 l_exit_end_tag VARCHAR2(20) := '</EXIT>';
661 l_exit_start_pos NUMBER;
662 l_exit_end_pos NUMBER;
663 l_config_header_id_start_tag VARCHAR2(20) := '<CONFIG_HEADER_ID>';
664 l_config_header_id_end_tag VARCHAR2(20) := '</CONFIG_HEADER_ID>';
665 l_config_header_id_start_pos NUMBER;
666 l_config_header_id_end_pos NUMBER;
667 l_config_rev_nbr_start_tag VARCHAR2(20) := '<CONFIG_REV_NBR>';
668 l_config_rev_nbr_end_tag VARCHAR2(20) := '</CONFIG_REV_NBR>';
669 l_config_rev_nbr_start_pos NUMBER;
670 l_config_rev_nbr_end_pos NUMBER;
671 l_message_text_start_tag VARCHAR2(20) := '<MESSAGE_TEXT>';
672 l_message_text_end_tag VARCHAR2(20) := '</MESSAGE_TEXT>';
673 l_message_text_start_pos NUMBER;
674 l_message_text_end_pos NUMBER;
675 l_message_type_start_tag VARCHAR2(20) := '<MESSAGE_TYPE>';
676 l_message_type_end_tag VARCHAR2(20) := '</MESSAGE_TYPE>';
677 l_message_type_start_pos NUMBER;
678 l_message_type_end_pos NUMBER;
679 l_exit VARCHAR(20);
680 l_config_header_id NUMBER;
681 l_config_rev_nbr NUMBER;
682 l_message_text VARCHAR2(2000);
683 l_message_type VARCHAR2(200);
684 l_list_price NUMBER;
685 l_selection_line_id NUMBER;
686 l_valid_config VARCHAR2(10);
687 l_complete_config VARCHAR2(10);
688 l_header_id NUMBER;
689 l_line_id NUMBER ;
690 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
691 l_return_status_del VARCHAR2(1);
692 l_msg VARCHAR2(2000);
693 l_msg_data VARCHAR2(2000);
694 l_msg_count NUMBER;
695 l_constraint VARCHAR2(16);
696 l_flag VARCHAR2(1) := 'N';
697
698 BEGIN
699 l_exit_start_pos := INSTR(UPPER(p_xml), l_exit_start_tag,1, 1) + length(l_exit_start_tag);
700 l_exit_end_pos := INSTR(UPPER(p_xml), l_exit_end_tag,1, 1) - 1;
701 l_exit := SUBSTR(p_xml,l_exit_start_pos,l_exit_end_pos - l_exit_start_pos + 1);
702
703 -- get the latest config_header_id, and rev_nbr to get
704 -- messages if any.
705 l_config_header_id_start_pos := INSTR(UPPER(p_xml), l_config_header_id_start_tag, 1, 1)+ length(l_config_header_id_start_tag);
706 l_config_header_id_end_pos := INSTR(UPPER(p_xml), l_config_header_id_end_tag, 1, 1) - 1;
707 l_config_header_id := to_number(SUBSTR(p_xml,l_config_header_id_start_pos,l_config_header_id_end_pos - l_config_header_id_start_pos + 1));
708
709 l_config_rev_nbr_start_pos := INSTR(UPPER(p_xml), l_config_rev_nbr_start_tag, 1, 1)+ length(l_config_rev_nbr_start_tag);
710 l_config_rev_nbr_end_pos := INSTR(UPPER(p_xml), l_config_rev_nbr_end_tag, 1, 1) - 1;
711 l_config_rev_nbr := to_number(SUBSTR(p_xml,l_config_rev_nbr_start_pos,l_config_rev_nbr_end_pos - l_config_rev_nbr_start_pos + 1));
712
713 -----no need to check if valid_config or complete_config
714 ----fix for bug# 2937753
715 IF ( (l_exit is NULL) OR (UPPER(l_exit) = 'ERROR') ) THEN
716 RAISE FND_API.G_EXC_ERROR;
717 ELSE
718 -- if everything ok, set return values
719 x_return_status := l_return_status;
720 x_config_header_id := l_config_header_id;
721 x_config_rev_nbr := l_config_rev_nbr;
722 END IF;
723 EXCEPTION
724 WHEN FND_API.G_EXC_ERROR THEN
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 WHEN OTHERS THEN
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
728 END Parse_output_xml;
729
730 --------------------------------------------------------------------------
731 ----procedure that logs trace for network API
732 PROCEDURE trace_gen_config_trees (p_api_version IN NUMBER,
733 p_config_tbl IN CZ_API_PUB.config_tbl_type,
734 p_tree_copy_mode IN VARCHAR2,
735 p_appl_param_rec IN CZ_API_PUB.appl_param_rec_type,
736 p_validation_context IN VARCHAR2,
737 p_config_model_tbl IN OUT NOCOPY CZ_API_PUB.config_model_tbl_type,
738 p_return_status IN OUT NOCOPY VARCHAR2)
739 IS
740 PRAGMA AUTONOMOUS_TRANSACTION;
741 TYPE trace_tbl IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
742 l_trace_tbl trace_tbl ;
743 l_trace_count NUMBER := 0;
744 l_trace_value cz_db_settings.value%type;
745
746 BEGIN
747 BEGIN
748 SELECT value
749 INTO l_trace_value
750 FROM cz_db_settings
751 WHERE cz_db_settings.setting_id = 'NetworkApiTrace';
752 EXCEPTION
753 WHEN OTHERS THEN
754 l_trace_value := 'NO';
755 END;
756
757 IF (l_trace_value = 'YES') THEN
758 l_trace_count := 1;
759 l_trace_tbl(l_trace_count) := 'Api version : '||to_char(p_api_version);
760 l_trace_count := l_trace_count + 1;
761 l_trace_tbl(l_trace_count) := 'Tree copy mode : '||p_tree_copy_mode;
762 l_trace_count := l_trace_count + 1;
763 l_trace_tbl(l_trace_count) := 'Publication applicability parameters';
764 l_trace_count := l_trace_count + 1;
765 l_trace_tbl(l_trace_count) := 'config_effective_date : '||to_char(p_appl_param_rec.config_effective_date,'mm-dd-yyyy hh24:mi:ss');
766 l_trace_count := l_trace_count + 1;
767 l_trace_tbl(l_trace_count) := 'calling_application_id : '||to_char(p_appl_param_rec.calling_application_id);
768 l_trace_count := l_trace_count + 1;
769 l_trace_tbl(l_trace_count) := 'usage_name : '||p_appl_param_rec.usage_name;
770 l_trace_count := l_trace_count + 1;
771 l_trace_tbl(l_trace_count) := 'publication_mode : '||p_appl_param_rec.publication_mode;
772 l_trace_count := l_trace_count + 1;
773 l_trace_tbl(l_trace_count) := 'language : '||p_appl_param_rec.language;
774 l_trace_count := l_trace_count + 1;
775 l_trace_tbl(l_trace_count) := 'Validation context : '||p_validation_context;
776 l_trace_count := l_trace_count + 1;
777 l_trace_tbl(l_trace_count) := 'Input configs from calling application';
778
779 IF (p_config_tbl.COUNT > 0) THEN
780 FOR I IN p_config_tbl.FIRST..p_config_tbl.LAST
781 LOOP
782 l_trace_count := l_trace_count + 1;
783 l_trace_tbl(l_trace_count) := 'Input config hdr id : '||to_char(p_config_tbl(i).config_hdr_id);
784 l_trace_count := l_trace_count + 1;
785 l_trace_tbl(l_trace_count) := 'Input config rev nbr: '||to_char(p_config_tbl(i).config_rev_nbr);
786 END LOOP;
787 END IF;
788
789 l_trace_count := l_trace_count + 1;
790 l_trace_tbl(l_trace_count) := 'Output configs sent to calling application';
791
792 IF (p_config_model_tbl.COUNT > 0) THEN
793 FOR I IN p_config_model_tbl.FIRST..p_config_model_tbl.LAST
794 LOOP
795 l_trace_count := l_trace_count + 1;
796 l_trace_tbl(l_trace_count) := 'Ouput config hdr id : '||to_char(p_config_model_tbl(i).config_hdr_id);
797 l_trace_count := l_trace_count + 1;
798 l_trace_tbl(l_trace_count) := 'Output config rev nbr : '||to_char(p_config_model_tbl(i).config_rev_nbr);
799 l_trace_count := l_trace_count + 1;
800 l_trace_tbl(l_trace_count) := 'Ouput inv item id : '||to_char(p_config_model_tbl(i).inventory_item_id);
801 l_trace_count := l_trace_count + 1;
802 l_trace_tbl(l_trace_count) := 'Output org id : '||to_char(p_config_model_tbl(i).organization_id);
803 l_trace_count := l_trace_count + 1;
804 l_trace_tbl(l_trace_count) := 'Output config item id : '||to_char(p_config_model_tbl(i).config_item_id);
805 l_trace_count := l_trace_count + 1;
806 END LOOP;
807 END IF;
808
809 l_trace_count := l_trace_count + 1;
810 l_trace_tbl(l_trace_count) := 'STATUS : '||p_return_status;
811
812 IF (l_trace_tbl.COUNT > 0) THEN
813 FOR I IN l_trace_tbl.FIRST..l_trace_tbl.LAST
814 LOOP
815 insert into cz_db_logs (message,caller,logtime) values (l_trace_tbl(i),'CZNETAPI',sysdate);
816 END LOOP;
817 l_trace_tbl.DELETE;
818 END IF;
819 END IF;
820 COMMIT;
821 EXCEPTION
822 WHEN OTHERS THEN
823 ROLLBACK;
824 END trace_gen_config_trees;
825
826 -------------------------------------------------------------------------
827 ----procedure that logs trace for network API
828 PROCEDURE trace_add_to_config_trees (p_api_version IN NUMBER,
829 p_inventory_item_id IN NUMBER,
830 p_organization_id IN NUMBER,
831 p_config_hdr_id IN NUMBER,
832 p_config_rev_nbr IN NUMBER,
833 p_instance_tbl IN CZ_API_PUB.config_tbl_type,
834 p_tree_copy_mode IN VARCHAR2,
835 p_appl_param_rec IN CZ_API_PUB.appl_param_rec_type,
836 p_validation_context IN VARCHAR2,
837 x_config_model_rec IN CZ_API_PUB.config_model_rec_type,
838 x_return_status IN VARCHAR2,
839 x_msg_count IN NUMBER,
840 x_msg_data IN VARCHAR2)
841 IS
842 PRAGMA AUTONOMOUS_TRANSACTION;
843 TYPE trace_tbl IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
844 l_trace_tbl trace_tbl ;
845 l_trace_count NUMBER := 0;
846 l_trace_value cz_db_settings.value%type;
847
848 BEGIN
849 BEGIN
850 SELECT value
851 INTO l_trace_value
852 FROM cz_db_settings
853 WHERE cz_db_settings.setting_id = 'NetworkApiTrace';
854 EXCEPTION
855 WHEN OTHERS THEN
856 l_trace_value := 'NO';
857 END;
858
859 IF (l_trace_value = 'YES') THEN
860 l_trace_count := 1;
861 l_trace_tbl(l_trace_count) := 'Api version : '||to_char(p_api_version);
862 l_trace_count := l_trace_count + 1;
863 l_trace_tbl(l_trace_count) := 'Tree copy mode : '||p_tree_copy_mode;
864 l_trace_count := l_trace_count + 1;
865 l_trace_tbl(l_trace_count) := 'Publication applicability parameters';
866 l_trace_count := l_trace_count + 1;
867 l_trace_tbl(l_trace_count) := 'config_effective_date : '||to_char(p_appl_param_rec.config_effective_date,'mm-dd-yyyy hh24:mi:ss');
868 l_trace_count := l_trace_count + 1;
869 l_trace_tbl(l_trace_count) := 'calling_application_id : '||to_char(p_appl_param_rec.calling_application_id);
870 l_trace_count := l_trace_count + 1;
871 l_trace_tbl(l_trace_count) := 'usage_name : '||p_appl_param_rec.usage_name;
872 l_trace_count := l_trace_count + 1;
873 l_trace_tbl(l_trace_count) := 'publication_mode : '||p_appl_param_rec.publication_mode;
874 l_trace_count := l_trace_count + 1;
875 l_trace_tbl(l_trace_count) := 'language : '||p_appl_param_rec.language;
876 l_trace_count := l_trace_count + 1;
877 l_trace_tbl(l_trace_count) := 'Validation context : '||p_validation_context;
878 l_trace_count := l_trace_count + 1;
879 l_trace_tbl(l_trace_count) := 'Input configs from calling application';
880
881 IF (p_instance_tbl.COUNT > 0) THEN
882 FOR I IN p_instance_tbl.FIRST..p_instance_tbl.LAST
883 LOOP
884 l_trace_count := l_trace_count + 1;
885 l_trace_tbl(l_trace_count) := 'Input config hdr id : '||to_char(p_instance_tbl(i).config_hdr_id);
886 l_trace_count := l_trace_count + 1;
887 l_trace_tbl(l_trace_count) := 'Input config rev nbr: '||to_char(p_instance_tbl(i).config_rev_nbr);
888 END LOOP;
889 END IF;
890
891 l_trace_count := l_trace_count + 1;
892 l_trace_tbl(l_trace_count) := 'Output configs sent to calling application';
893
894 l_trace_count := l_trace_count + 1;
895 l_trace_tbl(l_trace_count) := 'Ouput config hdr id : '||to_char(nvl(x_config_model_rec.config_hdr_id,0));
896 l_trace_count := l_trace_count + 1;
897 l_trace_tbl(l_trace_count) := 'Output config rev nbr : '||to_char(nvl(x_config_model_rec.config_rev_nbr,0));
898 l_trace_count := l_trace_count + 1;
899 l_trace_tbl(l_trace_count) := 'Ouput inv item id : '||to_char(nvl(x_config_model_rec.inventory_item_id,0));
900 l_trace_count := l_trace_count + 1;
901 l_trace_tbl(l_trace_count) := 'Output org id : '||to_char(nvl(x_config_model_rec.organization_id,0));
902 l_trace_count := l_trace_count + 1;
903 l_trace_tbl(l_trace_count) := 'Output config item id : '||to_char(nvl(x_config_model_rec.config_item_id,0));
904 l_trace_count := l_trace_count + 1;
905
906 l_trace_count := l_trace_count + 1;
907 l_trace_tbl(l_trace_count) := 'STATUS : '||x_return_status;
908
909 IF (l_trace_tbl.COUNT > 0) THEN
910 FOR I IN l_trace_tbl.FIRST..l_trace_tbl.LAST
911 LOOP
912 insert into cz_db_logs (message,caller,logtime) values (l_trace_tbl(i),'CZNETAPI',sysdate);
913 END LOOP;
914 l_trace_tbl.DELETE;
915 END IF;
916 END IF;
917 COMMIT;
918 EXCEPTION
919 WHEN OTHERS THEN
920 ROLLBACK;
921 END trace_add_to_config_trees;
922
923
924 ---------------------------------------------------------------------------
925 ---Start of comments
926 ---API name : generate_config_trees
927 ---Type : Public
928 ---Pre-reqs : None
929 ---Function : generates config trees for a given set of config hdr ids and rev nbrs
930 ---Parameters :
931 ---IN : p_api_version IN NUMBER Required
932 --- p_config_tbl IN config_tbl_type Required
933 --- p_tree_copy_mode IN VARCHAR2 Required
934 --- p_appl_param_rec IN appl_param_rec_type Required
935 --- p_validation_context IN VARCHAR2 Required
936 --- p_validation_type IN VARCHAR2 valid values are :
937 --- CZ_API_PUB.INTERACTIVE, CZ_API_PUB.VALIDATE_RETURN
938 ---OUT : x_return_status OUT NOCOPY VARCHAR2
939 --- x_msg_count OUT NOCOPY NUMBER
940 --- x_msg_data OUT NOCOPY VARCHAR2
941 ---Version: Current version :1.0
942 ---End of comments
943
944 PROCEDURE generate_config_trees(p_api_version IN NUMBER,
945 p_config_tbl IN CZ_API_PUB.config_tbl_type,
946 p_tree_copy_mode IN VARCHAR2,
947 p_appl_param_rec IN CZ_API_PUB.appl_param_rec_type,
948 p_validation_context IN VARCHAR2,
949 p_validation_type IN VARCHAR2,
950 x_config_model_tbl OUT NOCOPY CZ_API_PUB.config_model_tbl_type,
951 x_return_status OUT NOCOPY VARCHAR2,
952 x_msg_count OUT NOCOPY NUMBER,
953 x_msg_data OUT NOCOPY VARCHAR2
954 )
955 IS
956 PRAGMA AUTONOMOUS_TRANSACTION;
957
958 l_api_name CONSTANT VARCHAR2(30) := 'generate_config_trees';
959 l_api_version CONSTANT NUMBER := 1.0;
960 l_config_hdr_id cz_config_hdrs.config_hdr_id%TYPE;
961 l_config_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE;
962 l_main_config_hdr_id cz_config_hdrs.config_hdr_id%TYPE;
963 l_main_config_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE;
964 l_inventory_item_id cz_config_items.inventory_item_id%TYPE;
965 l_organization_id cz_config_items.organization_id%TYPE;
966 l_config_item_id cz_config_items.config_item_id%TYPE;
967 l_model_instantiation_type VARCHAR2(1);
968 l_component_instance_type VARCHAR2(1);
969 container_model_tbl container_model_tbl_type;
970 container_config_tbl container_config_tbl_type;
971 exists_flag BOOLEAN;
972 ORG_ID_FLAG BOOLEAN;
973 inst_hdr_count NUMBER := 0;
974 l_xml_hdr VARCHAR2(32767);
975 l_batch_validate_msg VARCHAR2(100);
976 v_config_hdr_id NUMBER := 0;
977 v_config_rev_nbr NUMBER := 0;
978 v_output_cfg_hdr_id NUMBER := 0;
979 v_output_cfg_rev_nbr NUMBER := 0;
980 v_valid_config VARCHAR2(30);
981 v_complete_config VARCHAR2(30);
982 l_copy_config_msg VARCHAR2(2000);
983 config_input_list cz_cf_api.cfg_input_list;
984 config_messages cz_cf_api.cfg_output_pieces;
985 v_ouput_config_count NUMBER := 0;
986 v_xml_str LONG;
987 l_idx NUMBER;
988 inv_count NUMBER := 0;
989 new_config_flag VARCHAR2(1);
990 l_msg_count NUMBER;
991 l_cp_msg_count NUMBER;
992 l_msg_data VARCHAR2(10000);
993 l_errbuf VARCHAR2(2000);
994 l_copy_config_status VARCHAR2(1);
995 validation_status NUMBER;
996 v_parse_status VARCHAR2(1);
997 l_validation_context VARCHAR2(1);
998 l_url VARCHAR2(255);
999 l_orig_item_tbl cz_api_pub.number_tbl_type;
1000 l_new_item_tbl cz_api_pub.number_tbl_type;
1001 l_config_err_msg VARCHAR2(2000);
1002 l_dummy_config_hdr_id NUMBER;
1003
1004 --Sungard ER Bug7673721
1005 l_instance_number_tbl cz_api_pub.number_tbl_type;
1006 l_value cz_db_settings.value%TYPE;
1007
1008
1009 NO_INPUT_RECORDS EXCEPTION;
1010 INPUT_TREE_MODE_NULL EXCEPTION;
1011 ORG_ID_EXCEP EXCEPTION;
1012 BATCH_VALID_FAILURE EXCEPTION;
1013 INVALID_CONTAINER_HDR EXCEPTION;
1014 INVALID_INV_ORG_ID EXCEPTION;
1015 INVALID_OUT_INV_ORG_ID EXCEPTION;
1016 INVALID_HEADER_TYPE EXCEPTION;
1017 INVALID_TREE_MODE_ERR EXCEPTION;
1018 INVALID_VALIDATION_TYPE EXCEPTION;
1019 COPY_CONFIG_FAILURE_EXCEP EXCEPTION;
1020 INVALID_HEADER_EXCEP EXCEPTION;
1021 BATCH_VALID_ERR EXCEPTION;
1022 PARSE_XML_ERROR EXCEPTION;
1023 INVALID_CONTEXT EXCEPTION;
1024 NO_VALIDATION_CONTEXT EXCEPTION;
1025
1026 BEGIN
1027
1028 IF p_validation_type NOT IN(CZ_API_PUB.INTERACTIVE, CZ_API_PUB.VALIDATE_RETURN) THEN
1029 RAISE INVALID_VALIDATION_TYPE;
1030 END IF;
1031
1032 l_orig_item_tbl := cz_api_pub.NUMBER_TBL_TYPE();
1033 l_new_item_tbl := cz_api_pub.NUMBER_TBL_TYPE();
1034
1035 ----initialize return status to success
1036 x_return_status := FND_API.G_RET_STS_SUCCESS;
1037 fnd_msg_pub.initialize;
1038
1039 ---check api version
1040 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1041 RAISE G_INCOMPATIBLE_API;
1042 END IF;
1043
1044 ----verify that input array contains records
1045 IF (p_config_tbl.COUNT = 0) THEN
1046 RAISE NO_INPUT_RECORDS;
1047 END IF;
1048
1049 ---verify that the input parameters are not null
1050 IF ( (p_tree_copy_mode IS NULL) OR (p_tree_copy_mode = FND_API.G_MISS_CHAR)) THEN
1051 RAISE INPUT_TREE_MODE_NULL;
1052 ELSIF (p_tree_copy_mode = CZ_API_PUB.G_NEW_HEADER_COPY_MODE) THEN
1053 new_config_flag := '0';
1054 ELSIF (p_tree_copy_mode = CZ_API_PUB.G_NEW_REVISION_COPY_MODE) THEN
1055 new_config_flag := '1';
1056 ELSE
1057 RAISE INVALID_TREE_MODE_ERR;
1058 END IF;
1059
1060 ----verify validation context
1061 IF (p_validation_context IS NULL) THEN
1062 l_validation_context := NULL;
1063 ELSIF (p_validation_context = CZ_API_PUB.G_INSTALLED) THEN
1064 l_validation_context := CZ_API_PUB.G_INSTALLED;
1065 ELSIF (p_validation_context = CZ_API_PUB.G_PENDING_OR_INSTALLED) THEN
1066 l_validation_context := CZ_API_PUB.G_PENDING_OR_INSTALLED;
1067 ELSE
1068 RAISE INVALID_CONTEXT;
1069 END IF;
1070
1071 container_model_tbl.DELETE;
1072
1073 --Sungard ER Bug7673721
1074
1075 BEGIN
1076 SELECT UPPER(value) INTO l_value FROM cz_db_settings WHERE UPPER(section_name) = 'CONFIG'
1077 AND UPPER(setting_id) = 'OPTIMIZECONFIGTREE';
1078 EXCEPTION
1079 WHEN OTHERS THEN
1080 l_value := 'N';
1081 END;
1082
1083 --optimization for Sungard
1084 IF (l_value = 'Y' AND p_config_tbl.COUNT = 1) THEN
1085 -- call copy config
1086 l_idx := p_config_tbl.FIRST;
1087 l_config_hdr_id := p_config_tbl(l_idx).config_hdr_id;
1088 l_config_rev_nbr := p_config_tbl(l_idx).config_rev_nbr;
1089
1090 ----get container header
1091 get_container_header(l_config_hdr_id,l_config_rev_nbr,l_main_config_hdr_id,l_main_config_rev_nbr);
1092
1093 ----if no container header is found raise exception
1094 IF ( (l_main_config_hdr_id = -1) OR (l_main_config_rev_nbr = -1) ) THEN
1095 RAISE INVALID_CONTAINER_HDR;
1096 END IF;
1097
1098 --Check the number of instances in the container - the optimization is valid only if there is only one.
1099 SELECT 1 BULK COLLECT INTO l_instance_number_tbl FROM cz_config_items
1100 WHERE component_instance_type = 'I'
1101 AND deleted_flag = '0'
1102 AND config_hdr_id = l_main_config_hdr_id
1103 AND config_rev_nbr = l_main_config_rev_nbr;
1104
1105 IF(l_instance_number_tbl.COUNT = 1)THEN
1106 v_config_hdr_id := 0;
1107 v_config_rev_nbr := 0;
1108
1109 -- debug
1110 INSERT INTO cz_db_logs (message,caller,logtime)
1111 VALUES ('copy Config Called :' || l_main_config_hdr_id || '>>'|| l_main_config_rev_nbr ,'CZNETAPI',sysdate);
1112
1113 CZ_CONFIG_API_PVT.copy_configuration(1.0,
1114 l_main_config_hdr_id,
1115 l_main_config_rev_nbr,
1116 CZ_API_PUB.G_NEW_HEADER_COPY_MODE,
1117 v_config_hdr_id,
1118 v_config_rev_nbr,
1119 l_orig_item_tbl,
1120 l_new_item_tbl,
1121 l_copy_config_status,
1122 l_cp_msg_count,
1123 l_copy_config_msg,
1124 NULL,
1125 NULL,
1126 '1');
1127
1128 IF (l_copy_config_status <> FND_API.G_RET_STS_SUCCESS) THEN
1129 RAISE COPY_CONFIG_FAILURE_EXCEP;
1130 ELSE
1131 reset_config_delta (v_config_hdr_id, v_config_rev_nbr);
1132 reset_config_flags (v_config_hdr_id, v_config_rev_nbr);
1133
1134 -- set effective_date of new config to sysdate - bug#8417357
1135 UPDATE cz_config_hdrs SET effective_date = SYSDATE WHERE config_hdr_id = v_config_hdr_id
1136 AND config_rev_nbr = v_config_rev_nbr;
1137
1138 -- set baseline rev for the new instance header
1139 UPDATE cz_config_hdrs SET baseline_rev_nbr = l_config_rev_nbr WHERE config_hdr_id = l_config_hdr_id
1140 AND config_rev_nbr = (SELECT instance_rev_nbr FROM cz_config_items WHERE config_hdr_id =
1141 v_config_hdr_id AND config_rev_nbr = v_config_rev_nbr AND
1142 component_instance_type = 'I');
1143
1144 -- need to delete discontinued items
1145 DELETE FROM cz_config_items a WHERE discontinued_flag = '1'
1146 AND config_hdr_id = v_config_hdr_id
1147 AND config_rev_nbr = v_config_rev_nbr
1148 AND not exists (SELECT 1 from cz_config_items
1149 WHERE parent_config_item_id = a.config_item_id
1150 AND config_hdr_id = v_config_hdr_id
1151 AND config_rev_nbr = v_config_rev_nbr
1152 AND discontinued_flag = '0');
1153
1154 ------identify container model config item ID
1155 get_root_bom_config_item(v_config_hdr_id,v_config_rev_nbr,l_inventory_item_id,l_organization_id,l_config_item_id);
1156
1157 -----if no inv item id or org id is retrieved raise exception
1158 IF ( (l_inventory_item_id = -1) OR (l_organization_id = -1) ) THEN
1159 RAISE INVALID_INV_ORG_ID;
1160 END IF;
1161
1162 v_ouput_config_count := x_config_model_tbl.COUNT + 1;
1163 x_config_model_tbl(v_ouput_config_count).config_hdr_id := v_config_hdr_id;
1164 x_config_model_tbl(v_ouput_config_count).config_rev_nbr := v_config_rev_nbr;
1165 x_config_model_tbl(v_ouput_config_count).inventory_item_id := l_inventory_item_id;
1166 x_config_model_tbl(v_ouput_config_count).organization_id := l_organization_id;
1167 x_config_model_tbl(v_ouput_config_count).config_item_id := l_config_item_id;
1168
1169 END IF;
1170
1171 COMMIT;
1172 RETURN;
1173 END IF;
1174 END IF;
1175
1176 FOR configInstance IN p_config_tbl.FIRST..p_config_tbl.LAST
1177 LOOP
1178 IF (p_config_tbl(configInstance).config_hdr_id > 0) THEN
1179 l_config_hdr_id := p_config_tbl(configInstance).config_hdr_id;
1180 l_config_rev_nbr := p_config_tbl(configInstance).config_rev_nbr;
1181 ----get header types
1182 get_header_types(l_config_hdr_id,l_config_rev_nbr,l_model_instantiation_type,l_component_instance_type);
1183
1184 IF (l_model_instantiation_type = NETWORK) THEN
1185 IF (l_component_instance_type = INSTANCE_ROOT) THEN
1186 ----get container header
1187 get_container_header(l_config_hdr_id,l_config_rev_nbr,l_main_config_hdr_id,l_main_config_rev_nbr);
1188
1189 ----if no container header is found raise exception
1190 IF ( (l_main_config_hdr_id = -1) OR (l_main_config_rev_nbr = -1) ) THEN
1191 RAISE INVALID_CONTAINER_HDR;
1192 END IF;
1193
1194 ------get top inv item and org id
1195 get_root_bom_config_item(l_main_config_hdr_id,l_main_config_rev_nbr,l_inventory_item_id,l_organization_id,l_config_item_id);
1196
1197 -----if no inv item id or org id is retrieved raise exception
1198 IF ( (l_inventory_item_id = -1) OR (l_organization_id = -1) ) THEN
1199 RAISE INVALID_INV_ORG_ID;
1200 END IF;
1201
1202 ----check if the combination of inv item id and org id exists
1203 check_if_item_exists(l_inventory_item_id,l_organization_id,container_model_tbl,l_idx,exists_flag);
1204
1205
1206 ---if inv item and org id exists in the array then append instances else create a new rec
1207 inst_hdr_count := container_config_tbl.COUNT + 1;
1208 container_config_tbl(inst_hdr_count).inventory_item_id := l_inventory_item_id;
1209 container_config_tbl(inst_hdr_count).config_hdr_id := l_config_hdr_id;
1210 container_config_tbl(inst_hdr_count).config_rev_nbr := l_config_rev_nbr;
1211 IF (NOT exists_flag) THEN
1212 inv_count := container_model_tbl.COUNT + 1;
1213 inst_hdr_count := container_config_tbl.COUNT + 1;
1214 container_model_tbl(inv_count).inventory_item_id := l_inventory_item_id;
1215 container_model_tbl(inv_count).organization_id := l_organization_id;
1216 container_model_tbl(inv_count).config_item_id := l_config_item_id;
1217 END IF;
1218
1219 ELSIF (l_component_instance_type = ROOT) THEN
1220 get_root_bom_config_item(l_config_hdr_id,l_config_rev_nbr,l_inventory_item_id,l_organization_id,l_config_item_id);
1221 v_config_hdr_id := 0;
1222 v_config_rev_nbr := 0;
1223
1224 CZ_CONFIG_API_PUB.copy_configuration(1.0,
1225 l_config_hdr_id,
1226 l_config_rev_nbr,
1227 p_tree_copy_mode,
1228 v_config_hdr_id,
1229 v_config_rev_nbr,
1230 l_orig_item_tbl,
1231 l_new_item_tbl,
1232 l_copy_config_status,
1233 l_cp_msg_count,
1234 l_copy_config_msg,
1235 NULL,
1236 NULL);
1237
1238 IF (l_copy_config_status <> FND_API.G_RET_STS_SUCCESS) THEN
1239 RAISE COPY_CONFIG_FAILURE_EXCEP;
1240 ELSE
1241 -- Sungard ER Bug7673721
1242 -- This causes problems because the container model is filtered out of config_details_v
1243 --reset_config_delta (v_config_hdr_id,v_config_hdr_id);
1244 v_ouput_config_count := x_config_model_tbl.COUNT + 1;
1245 x_config_model_tbl(v_ouput_config_count).config_hdr_id := v_config_hdr_id;
1246 x_config_model_tbl(v_ouput_config_count).config_rev_nbr := v_config_rev_nbr;
1247 x_config_model_tbl(v_ouput_config_count).inventory_item_id := l_inventory_item_id;
1248 x_config_model_tbl(v_ouput_config_count).organization_id := l_organization_id;
1249 x_config_model_tbl(v_ouput_config_count).config_item_id := l_config_item_id;
1250 END IF;
1251 ELSE
1252 RAISE INVALID_HEADER_TYPE;
1253 END IF; /* end if of l_component_instance_type = INSTANCE_ROOT */
1254 ELSE
1255 RAISE INVALID_HEADER_EXCEP;
1256 END IF; /* end if of l_model_instantiation_type = NETWORK */
1257 END IF;
1258 END LOOP;
1259
1260 ---validate organization id in container_model_tbl
1261 ---if org ids are not the same then raise exception
1262 validate_org_id(container_model_tbl, org_id_flag);
1263 IF (NOT org_id_flag) THEN
1264 RAISE ORG_ID_EXCEP;
1265 END IF;
1266
1267 -----batch validation for grouped network instances.
1268 IF (container_model_tbl.COUNT > 0) THEN
1269 FOR I IN container_model_tbl.FIRST..container_model_tbl.LAST
1270 LOOP
1271
1272
1273 BEGIN
1274 write_dummy_config ( container_model_tbl(i).inventory_item_id
1275 , container_config_tbl
1276 , l_dummy_config_hdr_id );
1277 COMMIT;
1278
1279 create_hdr_xml( container_model_tbl(i).inventory_item_id,
1280 container_model_tbl(i).organization_id,
1281 null,
1282 null,
1283 l_dummy_config_hdr_id,
1284 p_appl_param_rec,
1285 p_tree_copy_mode,
1286 l_validation_context,
1287 l_xml_hdr);
1288
1289 l_url := FND_PROFILE.VALUE('CZ_UIMGR_URL');
1290 config_messages.DELETE;
1291 cz_cf_api.validate(config_input_list,l_xml_hdr,config_messages,validation_status,l_url,p_validation_type);
1292
1293 EXCEPTION
1294 WHEN OTHERS THEN
1295 delete_dummy_config ( l_dummy_config_hdr_id );
1296 COMMIT;
1297 RAISE;
1298 END;
1299
1300 delete_dummy_config ( l_dummy_config_hdr_id );
1301 COMMIT;
1302
1303 cz_debug_pub.get_batch_validate_message(validation_status,l_batch_validate_msg);
1304
1305 IF (validation_status <> CZ_CF_API.CONFIG_PROCESSED) THEN
1306 RAISE BATCH_VALID_ERR;
1307 ELSE
1308 ----get config hdr id and config rev nbr from xml string
1309 IF (config_messages.COUNT > 0) THEN
1310 v_output_cfg_hdr_id := 0;
1311 v_xml_str := NULL;
1312
1313 FOR xmlStr IN config_messages.FIRST..config_messages.LAST
1314 LOOP
1315 v_xml_str := v_xml_str||config_messages(xmlStr);
1316 populate_debug_message(config_messages(xmlStr),NULL,NULL);
1317 END LOOP;
1318 END IF;
1319
1320 parse_output_xml (v_xml_str,
1321 v_output_cfg_hdr_id,
1322 v_output_cfg_rev_nbr,
1323 v_parse_status);
1324
1325 ----if error in parsing xml raise an exception
1326 IF (v_parse_status <> FND_API.G_RET_STS_SUCCESS) THEN
1327 RAISE PARSE_XML_ERROR;
1328 END IF;
1329
1330 ---add the config hdr and rev to OUT NOCOPY put tbl
1331 IF (v_output_cfg_hdr_id > 0) THEN
1332 v_ouput_config_count := x_config_model_tbl.COUNT + 1;
1333 x_config_model_tbl(v_ouput_config_count).config_hdr_id := v_output_cfg_hdr_id;
1334 x_config_model_tbl(v_ouput_config_count).config_rev_nbr := v_output_cfg_rev_nbr;
1335
1336 ------get top inv item and org id
1337 get_root_bom_config_item(v_output_cfg_hdr_id,v_output_cfg_rev_nbr,l_inventory_item_id,l_organization_id,l_config_item_id);
1338
1339 -----if no inv item id or org id is retrieved raise exception
1340 IF p_validation_type<>CZ_API_PUB.VALIDATE_RETURN AND
1341 ( l_inventory_item_id = -1 OR l_organization_id = -1 ) THEN
1342 RAISE INVALID_OUT_INV_ORG_ID;
1343 END IF;
1344
1345 x_config_model_tbl(v_ouput_config_count).inventory_item_id := l_inventory_item_id;
1346 x_config_model_tbl(v_ouput_config_count).organization_id := l_organization_id;
1347 x_config_model_tbl(v_ouput_config_count).config_item_id := l_config_item_id;
1348 END IF;
1349 END IF;
1350 END LOOP;
1351 END IF;
1352
1353 -----log messages for debugging
1354 populate_debug_message('Generation of config tree successful','CZ_NETWORK_API: '||to_char(sysdate, 'mm-dd-yyyy hh24:mi:ss'), 0);
1355 CZ_DEBUG_PUB.insert_into_logs(v_msg_tbl);
1356 trace_gen_config_trees (p_api_version,p_config_tbl,
1357 p_tree_copy_mode,p_appl_param_rec,
1358 p_validation_context,
1359 x_config_model_tbl,
1360 x_return_status);
1361 COMMIT;
1362 EXCEPTION
1363 WHEN G_INCOMPATIBLE_API THEN
1364 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_VERSION_ERR', 'CODEVERSION', l_api_version, 'VERSION', p_api_version);
1365 set_fnd_message('CZ_NET_API_VERSION_ERR','CODEVERSION',l_api_version, 'VERSION', p_api_version);
1366 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1367 COMMIT;
1368 WHEN INVALID_VALIDATION_TYPE THEN
1369 l_errbuf := CZ_UTILS.GET_TEXT('CZ_BV_INVALID_TYPE','TYPE',p_validation_type);
1370 set_fnd_message('CZ_BV_INVALID_TYPE','TYPE',p_validation_type);
1371 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1372 COMMIT;
1373 WHEN NO_INPUT_RECORDS THEN
1374 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_NO_INPUT_HDRS','TABLE','p_config_tbl','PROC',l_api_name );
1375 set_fnd_message('CZ_NET_API_NO_INPUT_HDRS','TABLE','p_config_tbl','PROC',l_api_name);
1376 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1377 COMMIT;
1378 WHEN INPUT_TREE_MODE_NULL THEN
1379 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_INVALID_TREE_MODE','MODE',p_tree_copy_mode,'PROC',l_api_name);
1380 set_fnd_message('CZ_NET_API_INVALID_TREE_MODE','MODE',p_tree_copy_mode,'PROC',l_api_name);
1381 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1382 COMMIT;
1383 WHEN NO_VALIDATION_CONTEXT THEN
1384 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_INVALID_VAL_CTX', 'CTX', p_validation_context);
1385 set_fnd_message('CZ_NET_API_INVALID_VAL_CTX','CTX', p_validation_context, null, null);
1386 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1387 COMMIT;
1388 WHEN INVALID_CONTEXT THEN
1389 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_INVALID_VAL_CTX', 'CTX', p_validation_context);
1390 set_fnd_message('CZ_NET_API_INVALID_VAL_CTX','CTX', p_validation_context, null, null);
1391 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1392 COMMIT;
1393 WHEN INVALID_CONTAINER_HDR THEN
1394 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_NO_SESSION_HDR','HDRID',l_config_hdr_id,'REV',l_config_rev_nbr);
1395 set_fnd_message('CZ_NET_API_NO_SESSION_HDR','HDRID',l_config_hdr_id,'REV',l_config_rev_nbr);
1396 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1397 delete_configuration(x_config_model_tbl);
1398 x_config_model_tbl.DELETE;
1399 COMMIT;
1400 WHEN INVALID_HEADER_TYPE THEN
1401 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_INVALID_INST_HDR',
1402 'modelInstType', l_model_instantiation_type,
1403 'compInstType' , l_component_instance_type,
1404 'Hdr',l_config_hdr_id,
1405 'Rev',l_config_rev_nbr);
1406 set_fnd_message('CZ_NET_API_INVALID_INST_HDR',
1407 'modelInstType', l_model_instantiation_type,
1408 'compInstType' , l_component_instance_type,
1409 'Hdr',l_config_hdr_id,
1410 'Rev',l_config_rev_nbr);
1411 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1412 delete_configuration(x_config_model_tbl);
1413 x_config_model_tbl.DELETE;
1414 COMMIT;
1415 WHEN INVALID_INV_ORG_ID THEN
1416 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_NO_INV_FOR_CFG_HDR','HDR',l_main_config_hdr_id,'REV',l_main_config_rev_nbr);
1417 set_fnd_message('CZ_NET_API_NO_INV_FOR_CFG_HDR','HDR',l_main_config_hdr_id,'REV',l_main_config_rev_nbr);
1418 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1419 delete_configuration(x_config_model_tbl);
1420 x_config_model_tbl.DELETE;
1421 COMMIT;
1422 WHEN INVALID_OUT_INV_ORG_ID THEN
1423 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_NO_INV_FOR_CFG_HDR','HDR',v_output_cfg_hdr_id,'REV',v_output_cfg_rev_nbr);
1424 set_fnd_message('CZ_NET_API_NO_INV_FOR_CFG_HDR','HDR',v_output_cfg_hdr_id,'REV',v_output_cfg_rev_nbr);
1425 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1426 IF x_config_model_tbl.COUNT>0 AND p_validation_type<>CZ_API_PUB.VALIDATE_RETURN THEN
1427 delete_configuration(x_config_model_tbl);
1428 x_config_model_tbl.DELETE;
1429 END IF;
1430 COMMIT;
1431 WHEN INVALID_TREE_MODE_ERR THEN
1432 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_INVALID_TREE_MODE','MODE',p_tree_copy_mode,'PROC',l_api_name);
1433 set_fnd_message('CZ_NET_API_INVALID_TREE_MODE','MODE',p_tree_copy_mode,'PROC',l_api_name);
1434 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1435 delete_configuration(x_config_model_tbl);
1436 x_config_model_tbl.DELETE;
1437 COMMIT;
1438 WHEN ORG_ID_EXCEP THEN
1439 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_DIFF_ORGS');
1440 set_fnd_message('CZ_NET_API_DIFF_ORGS',null,null, null, null);
1441 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1442 delete_configuration(x_config_model_tbl);
1443 x_config_model_tbl.DELETE;
1444 COMMIT;
1445 WHEN COPY_CONFIG_FAILURE_EXCEP THEN
1446 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_COPY_CONFIG_ERR', 'HDR',l_main_config_hdr_id,'REV',l_main_config_rev_nbr,'ERR',l_copy_config_msg);
1447 set_fnd_message('CZ_NET_API_COPY_CONFIG_ERR','HDR',l_main_config_hdr_id,'REV',l_main_config_rev_nbr,'ERR',l_copy_config_msg,null,null);
1448 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1449 delete_configuration(x_config_model_tbl);
1450 x_config_model_tbl.DELETE;
1451 COMMIT;
1452 WHEN INVALID_HEADER_EXCEP THEN
1453 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_NO_SESSION_HDR', 'HDR',l_config_hdr_id,'REV',l_config_rev_nbr);
1454 set_fnd_message('CZ_NET_API_NO_SESSION_HDR', 'HDR',l_config_hdr_id,'REV',l_config_rev_nbr);
1455 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1456 delete_configuration(x_config_model_tbl);
1457 x_config_model_tbl.DELETE;
1458 COMMIT;
1459 WHEN BATCH_VALID_ERR THEN
1460 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_BV_ERR', 'STATUS', l_batch_validate_msg);
1461 set_fnd_message('CZ_NET_API_BV_ERR', 'STATUS', l_batch_validate_msg,null,null);
1462 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1463 delete_configuration(x_config_model_tbl);
1464 x_config_model_tbl.DELETE;
1465 COMMIT;
1466 WHEN PARSE_XML_ERROR THEN
1467 l_config_err_msg := get_terminate_msg(v_xml_str);
1468 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_PARSE_BV_XML_ERR','Err',l_config_err_msg,1,2000);
1469 set_fnd_message('CZ_NET_API_PARSE_BV_XML_ERR', 'ERR', substr(v_xml_str,1,2000),null,null);
1470 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1471 delete_configuration(x_config_model_tbl);
1472 x_config_model_tbl.DELETE;
1473 COMMIT;
1474 WHEN OTHERS THEN
1475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1476 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1477 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name);
1478 END IF;
1479 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
1480 delete_configuration(x_config_model_tbl);
1481 x_config_model_tbl.DELETE;
1482 COMMIT;
1483 END generate_config_trees;
1484
1485 ---------------------------------------------------------------------------
1486 ---Start of comments
1487 ---API name : generate_config_trees
1488 ---Type : Public
1489 ---Pre-reqs : None
1490 ---Function : generates config trees for a given set of config hdr ids and rev nbrs
1491 ---Parameters :
1492 ---IN : p_api_version IN NUMBER Required
1493 --- p_config_tbl IN config_tbl_type Required
1494 --- p_tree_copy_mode IN VARCHAR2 Required
1495 --- p_appl_param_rec IN appl_param_rec_type Required
1496 --- p_validation_context IN VARCHAR2 Required
1497 ---OUT : x_return_status OUT NOCOPY VARCHAR2
1498 --- x_msg_count OUT NOCOPY NUMBER
1499 --- x_msg_data OUT NOCOPY VARCHAR2
1500 ---Version: Current version :1.0
1501 ---End of comments
1502
1503 PROCEDURE generate_config_trees(p_api_version IN NUMBER,
1504 p_config_tbl IN CZ_API_PUB.config_tbl_type,
1505 p_tree_copy_mode IN VARCHAR2,
1506 p_appl_param_rec IN CZ_API_PUB.appl_param_rec_type,
1507 p_validation_context IN VARCHAR2,
1508 x_config_model_tbl OUT NOCOPY CZ_API_PUB.config_model_tbl_type,
1509 x_return_status OUT NOCOPY VARCHAR2,
1510 x_msg_count OUT NOCOPY NUMBER,
1511 x_msg_data OUT NOCOPY VARCHAR2
1512 )
1513 IS
1514 BEGIN
1515
1516 generate_config_trees(p_api_version => p_api_version,
1517 p_config_tbl => p_config_tbl,
1518 p_tree_copy_mode => p_tree_copy_mode,
1519 p_appl_param_rec => p_appl_param_rec,
1520 p_validation_context => p_validation_context,
1521 p_validation_type => CZ_API_PUB.INTERACTIVE,
1522 x_config_model_tbl => x_config_model_tbl,
1523 x_return_status => x_return_status,
1524 x_msg_count => x_msg_count,
1525 x_msg_data => x_msg_data
1526 );
1527
1528 END generate_config_trees;
1529
1530 ------------------------------------------------------------------------------------------------
1531 -----procedure adds instances to the saved configuration of a container model
1532 PROCEDURE add_to_config_tree (p_api_version IN NUMBER,
1533 p_inventory_item_id IN NUMBER,
1534 p_organization_id IN NUMBER,
1535 p_config_hdr_id IN NUMBER,
1536 p_config_rev_nbr IN NUMBER,
1537 p_instance_tbl IN CZ_API_PUB.config_tbl_type,
1538 p_tree_copy_mode IN VARCHAR2,
1539 p_appl_param_rec IN CZ_API_PUB.appl_param_rec_type,
1540 p_validation_context IN VARCHAR2,
1541 x_config_model_rec OUT NOCOPY CZ_API_PUB.config_model_rec_type,
1542 x_return_status OUT NOCOPY VARCHAR2,
1543 x_msg_count OUT NOCOPY NUMBER,
1544 x_msg_data OUT NOCOPY VARCHAR2)
1545 IS
1546 PRAGMA AUTONOMOUS_TRANSACTION;
1547
1548 l_api_name CONSTANT VARCHAR2(30) := 'add_to_config_tree';
1549 l_api_version CONSTANT NUMBER := 1.0;
1550
1551 l_config_hdr_id cz_config_hdrs.config_hdr_id%TYPE;
1552 l_config_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE;
1553
1554 l_inventory_item_id cz_config_items.inventory_item_id%TYPE;
1555 l_organization_id cz_config_items.organization_id%TYPE;
1556 l_config_item_id cz_config_items.config_item_id%TYPE;
1557
1558 l_main_config_hdr_id cz_config_hdrs.config_hdr_id%TYPE;
1559 l_main_config_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE;
1560
1561 l_config_model_tbl CZ_API_PUB.config_model_tbl_type;
1562 l_con_config_tbl container_config_tbl_type;
1563
1564 l_model_instantiation_type VARCHAR2(1);
1565 l_component_instance_type VARCHAR2(1);
1566
1567 config_input_list cz_cf_api.cfg_input_list;
1568 config_messages cz_cf_api.cfg_output_pieces;
1569 validation_status NUMBER;
1570 l_batch_validate_msg VARCHAR2(100);
1571 v_output_cfg_hdr_id NUMBER := 0;
1572 v_output_cfg_rev_nbr NUMBER := 0;
1573 v_valid_config VARCHAR2(30);
1574 v_complete_config VARCHAR2(30);
1575
1576 l_xml_hdr VARCHAR2(32767);
1577
1578 v_config_hdr_id NUMBER;
1579 v_config_rev_nbr NUMBER;
1580 l_copy_config_msg VARCHAR2(2000);
1581
1582 v_ouput_config_count NUMBER := 0;
1583 v_xml_str LONG;
1584 l_idx NUMBER;
1585 new_config_flag VARCHAR2(1);
1586 instance_hdr_count NUMBER;
1587
1588 l_msg_count NUMBER;
1589 l_msg_data VARCHAR2(10000);
1590 l_errbuf VARCHAR2(2000);
1591 l_copy_config_status NUMBER;
1592 v_parse_status VARCHAR2(1);
1593 l_validation_context VARCHAR2(1);
1594 l_url VARCHAR2(255);
1595 l_config_err_msg VARCHAR2(2000);
1596 l_dummy_config_hdr_id NUMBER;
1597
1598 INPUT_TREE_MODE_NULL EXCEPTION;
1599 BATCH_VALID_FAILURE EXCEPTION;
1600 INVALID_CONTAINER_HDR EXCEPTION;
1601 INVALID_INV_ORG_ID EXCEPTION;
1602 GEN_CONFIG_TREE_ERR EXCEPTION;
1603 INVALID_HEADER_TYPE EXCEPTION;
1604 MODEL_ORG_EXCEP EXCEPTION;
1605 BATCH_VALID_ERR EXCEPTION;
1606 PARSE_XML_ERROR EXCEPTION;
1607 NO_INPUT_RECORDS EXCEPTION;
1608 INVALID_CONTEXT EXCEPTION;
1609 INVALID_TREE_MODE_ERR EXCEPTION;
1610 NO_VALIDATION_CONTEXT EXCEPTION;
1611
1612 BEGIN
1613 ----initialize return status to success
1614 x_return_status := FND_API.G_RET_STS_SUCCESS;
1615 fnd_msg_pub.initialize;
1616
1617 ---check api version
1618 IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
1619 RAISE G_INCOMPATIBLE_API;
1620 END IF;
1621
1622 ---check if instance records are passed
1623 IF (p_instance_tbl.COUNT = 0) THEN
1624 RAISE NO_INPUT_RECORDS;
1625 END IF;
1626
1627 ---verify that the input parameters are not null
1628 IF ( (p_tree_copy_mode IS NULL) OR (p_tree_copy_mode = FND_API.G_MISS_CHAR)) THEN
1629 RAISE INPUT_TREE_MODE_NULL;
1630 ELSIF (p_tree_copy_mode = CZ_API_PUB.G_NEW_HEADER_COPY_MODE) THEN
1631 new_config_flag := '0';
1632 ELSIF (p_tree_copy_mode = CZ_API_PUB.G_NEW_REVISION_COPY_MODE) THEN
1633 new_config_flag := '1';
1634 ELSE
1635 RAISE INVALID_TREE_MODE_ERR;
1636 END IF;
1637
1638 ----verify validation context
1639 IF (p_validation_context IS NULL) THEN
1640 l_validation_context := NULL;
1641 ELSIF (p_validation_context = CZ_API_PUB.G_INSTALLED) THEN
1642 l_validation_context := CZ_API_PUB.G_INSTALLED;
1643 ELSIF (p_validation_context = CZ_API_PUB.G_PENDING_OR_INSTALLED) THEN
1644 l_validation_context := CZ_API_PUB.G_PENDING_OR_INSTALLED;
1645 ELSE
1646 RAISE INVALID_CONTEXT;
1647 END IF;
1648
1649 ---validate instance headers
1650 ---this is not required as adding of a non-network instance is allowed
1651 IF (p_instance_tbl.COUNT > 0) THEN
1652 FOR configInstance IN p_instance_tbl.FIRST..p_instance_tbl.LAST
1653 LOOP
1654 l_config_hdr_id := p_instance_tbl(configInstance).config_hdr_id;
1655 l_config_rev_nbr := p_instance_tbl(configInstance).config_rev_nbr;
1656
1657 ----get header types
1658 get_header_types(l_config_hdr_id,l_config_rev_nbr,l_model_instantiation_type,l_component_instance_type);
1659
1660 IF ( (l_model_instantiation_type <> NETWORK) OR (l_component_instance_type <> INSTANCE_ROOT) ) THEN
1661 RAISE INVALID_HEADER_TYPE;
1662 END IF;
1663 END LOOP;
1664 END IF;
1665
1666 IF ( (p_config_hdr_id IS NOT NULL) AND (p_config_rev_nbr IS NOT NULL) ) THEN
1667 get_header_types(p_config_hdr_id,p_config_rev_nbr,l_model_instantiation_type,l_component_instance_type);
1668 IF ( (l_model_instantiation_type <> NETWORK) OR (l_component_instance_type <> ROOT) ) THEN
1669 RAISE INVALID_HEADER_TYPE;
1670 END IF;
1671
1672 IF ( (p_inventory_item_id IS NOT NULL) AND (p_organization_id IS NOT NULL) ) THEN
1673 ------get top inv item and org id
1674 get_root_bom_config_item(p_config_hdr_id,p_config_rev_nbr,l_inventory_item_id,l_organization_id,l_config_item_id);
1675
1676 -----if no inv item id or org id is retrieved raise exception
1677 IF ( (l_inventory_item_id = -1) OR (l_organization_id = -1) ) THEN
1678 RAISE INVALID_INV_ORG_ID;
1679 END IF;
1680
1681 -----if OUT NOCOPY put inv id and org id are not equal to input model id and org id raise exception
1682 IF ( (l_inventory_item_id <> p_inventory_item_id) OR (l_organization_id <> p_organization_id) ) THEN
1683 RAISE MODEL_ORG_EXCEP;
1684 END IF;
1685 END IF; /* end if of (p_inventory_item_id IS NOT NULL) */
1686
1687 ----initialize config array for creating xml hdr
1688 l_con_config_tbl.DELETE;
1689 IF (p_instance_tbl.COUNT > 0) THEN
1690 FOR inst IN p_instance_tbl.FIRST..p_instance_tbl.LAST
1691 LOOP
1692 instance_hdr_count := l_con_config_tbl.COUNT + 1;
1693 l_con_config_tbl(instance_hdr_count).inventory_item_id := p_inventory_item_id;
1694 l_con_config_tbl(instance_hdr_count).config_hdr_id := p_instance_tbl(inst).config_hdr_id;
1695 l_con_config_tbl(instance_hdr_count).config_rev_nbr := p_instance_tbl(inst).config_rev_nbr;
1696 END LOOP;
1697 END IF;
1698
1699 BEGIN
1700 write_dummy_config ( p_inventory_item_id
1701 , l_con_config_tbl
1702 , l_dummy_config_hdr_id );
1703 COMMIT;
1704
1705 -----create init message
1706 create_hdr_xml( p_inventory_item_id,
1707 p_organization_id,
1708 p_config_hdr_id,
1709 p_config_rev_nbr,
1710 l_dummy_config_hdr_id,
1711 p_appl_param_rec,
1712 p_tree_copy_mode,
1713 l_validation_context,
1714 l_xml_hdr);
1715
1716 config_input_list.DELETE;
1717 l_url := FND_PROFILE.VALUE('CZ_UIMGR_URL');
1718 config_messages.DELETE;
1719 cz_cf_api.validate(config_input_list,l_xml_hdr,config_messages,validation_status,l_url,CZ_API_PUB.INTERACTIVE);
1720
1721 EXCEPTION
1722 WHEN OTHERS THEN
1723 delete_dummy_config ( l_dummy_config_hdr_id );
1724 COMMIT;
1725 RAISE;
1726 END;
1727
1728 delete_dummy_config ( l_dummy_config_hdr_id );
1729 COMMIT;
1730
1731 cz_debug_pub.get_batch_validate_message(validation_status,l_batch_validate_msg);
1732
1733 IF (validation_status <> CZ_CF_API.CONFIG_PROCESSED) THEN
1734 RAISE BATCH_VALID_ERR;
1735 ELSE
1736 ----get config hdr id and config rev nbr from xml string
1737 IF (config_messages.COUNT > 0) THEN
1738 v_output_cfg_hdr_id := 0;
1739 v_xml_str := NULL;
1740
1741 FOR xmlStr IN config_messages.FIRST..config_messages.LAST
1742 LOOP
1743 v_xml_str := v_xml_str||config_messages(xmlStr);
1744 END LOOP;
1745 END IF;
1746
1747 parse_output_xml (v_xml_str,
1748 v_output_cfg_hdr_id,
1749 v_output_cfg_rev_nbr,
1750 v_parse_status);
1751
1752 ----if error in parsing xml raise an exception
1753 IF (v_parse_status <> FND_API.G_RET_STS_SUCCESS) THEN
1754 RAISE PARSE_XML_ERROR;
1755 END IF;
1756
1757 ---add the config hdr and rev to OUT NOCOPY put tbl
1758 IF (v_output_cfg_hdr_id > 0) THEN
1759 x_config_model_rec.config_hdr_id := v_output_cfg_hdr_id;
1760 x_config_model_rec.config_rev_nbr := v_output_cfg_rev_nbr;
1761 x_config_model_rec.inventory_item_id := p_inventory_item_id;
1762 x_config_model_rec.organization_id := p_organization_id;
1763 x_config_model_rec.config_item_id := l_config_item_id;
1764 END IF;
1765 END IF; /* end if of validation_status <> CZ_CF_API.CONFIG_PROCESSED */
1766
1767 ELSE /* if p_config_hdr_id is NULL */
1768 ----generate config tree
1769 generate_config_trees(l_api_version,
1770 p_instance_tbl,
1771 p_tree_copy_mode,
1772 p_appl_param_rec,
1773 l_validation_context,
1774 l_config_model_tbl,
1775 x_return_status,
1776 x_msg_count,
1777 x_msg_data
1778 );
1779
1780 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1781 RAISE GEN_CONFIG_TREE_ERR;
1782 END IF;
1783
1784 IF (l_config_model_tbl.COUNT > 1) THEN
1785 RAISE GEN_CONFIG_TREE_ERR;
1786 END IF;
1787
1788 IF ( (p_inventory_item_id IS NOT NULL) AND (p_organization_id IS NOT NULL) ) THEN
1789 IF ( (p_inventory_item_id <> l_config_model_tbl(1).inventory_item_id)
1790 OR (p_organization_id <> l_config_model_tbl(1).organization_id) ) THEN
1791 RAISE MODEL_ORG_EXCEP;
1792 END IF;
1793 END IF;
1794
1795 x_config_model_rec.config_hdr_id := l_config_model_tbl(1).config_hdr_id;
1796 x_config_model_rec.config_rev_nbr := l_config_model_tbl(1).config_rev_nbr;
1797 x_config_model_rec.inventory_item_id := l_config_model_tbl(1).inventory_item_id;
1798 x_config_model_rec.organization_id := l_config_model_tbl(1).organization_id;
1799 x_config_model_rec.config_item_id := l_config_model_tbl(1).config_item_id;
1800
1801 END IF; /* end if of (p_config_hdr_id IS NOT NULL) */
1802 trace_add_to_config_trees (p_api_version,p_inventory_item_id,p_organization_id,p_config_hdr_id,p_config_rev_nbr
1803 ,p_instance_tbl,p_tree_copy_mode,p_appl_param_rec,p_validation_context,x_config_model_rec
1804 ,x_return_status,x_msg_count,x_msg_data);
1805 COMMIT;
1806 EXCEPTION
1807 WHEN G_INCOMPATIBLE_API THEN
1808 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_VERSION_ERR', 'CODEVERSION', l_api_version, 'VERSION', p_api_version);
1809 set_fnd_message('CZ_NET_API_VERSION_ERR','CODEVERSION',l_api_version, 'VERSION', p_api_version);
1810 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1811 COMMIT;
1812 WHEN NO_INPUT_RECORDS THEN
1813 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_NO_INPUT_HDRS','TABLE','p_instance_tbl','PROC',l_api_name );
1814 set_fnd_message('CZ_NET_API_NO_INPUT_HDRS','TABLE','p_instance_tbl','PROC',l_api_name);
1815 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1816 COMMIT;
1817 WHEN INVALID_HEADER_TYPE THEN
1818 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_INVALID_INST_HDR','modelInstType', l_model_instantiation_type,
1819 'compInstType',l_component_instance_type,'Hdr',l_config_hdr_id,
1820 'Rev',l_config_rev_nbr);
1821 set_fnd_message('CZ_NET_API_INVALID_INST_HDR','modelInstType', l_model_instantiation_type,
1822 'compInstType',l_component_instance_type,'Hdr',l_config_hdr_id,
1823 'Rev',l_config_rev_nbr);
1824 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1825 COMMIT;
1826 WHEN INVALID_CONTEXT THEN
1827 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_INVALID_VAL_CTX', 'CTX', p_validation_context);
1828 set_fnd_message('CZ_NET_API_INVALID_VAL_CTX','CTX',p_validation_context, null, null);
1829 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1830 COMMIT;
1831 WHEN NO_VALIDATION_CONTEXT THEN
1832 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_INVALID_VAL_CTX','CTX', p_validation_context);
1833 set_fnd_message('CZ_NET_API_INVALID_VAL_CTX','CTX', p_validation_context, null, null);
1834 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1835 COMMIT;
1836 WHEN INVALID_INV_ORG_ID THEN
1837 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_NO_INV_FOR_CFG_HDR', 'HDR',l_config_hdr_id,'REV',l_config_rev_nbr);
1838 set_fnd_message('CZ_NET_API_NO_INV_FOR_CFG_HDR','HDR',l_config_hdr_id,'REV',l_config_rev_nbr);
1839 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1840 COMMIT;
1841 WHEN MODEL_ORG_EXCEP THEN
1842 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_MODELID_ORGID_ERR',
1843 'ITEM1',l_inventory_item_id,
1844 'ORGID1',l_organization_id,
1845 'HDRID',p_config_hdr_id,
1846 'REVNBR',p_config_rev_nbr,
1847 'ITEM2',p_inventory_item_id,
1848 'ORGID2',p_organization_id);
1849 set_fnd_message('CZ_NET_API_MODELID_ORGID_ERR',
1850 'ITEM1', l_inventory_item_id,
1851 'ORGID1',l_organization_id,
1852 'HDRID', p_config_hdr_id,
1853 'REVNBR',p_config_rev_nbr,
1854 'ITEM2', p_inventory_item_id,
1855 'ORGID2',p_organization_id);
1856 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1857 COMMIT;
1858 WHEN BATCH_VALID_ERR THEN
1859 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_BV_ERR', 'STATUS', l_batch_validate_msg);
1860 set_fnd_message('CZ_NET_API_BV_ERR', 'STATUS', l_batch_validate_msg, null, null);
1861 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1862 COMMIT;
1863 WHEN PARSE_XML_ERROR THEN
1864 l_config_err_msg := get_terminate_msg(v_xml_str);
1865 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_PARSE_BV_XML_ERR','ERR',l_config_err_msg);
1866 set_fnd_message('CZ_NET_API_NO_CFG_HDR', 'ERR', substr(v_xml_str,1,2000),null,null);
1867 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1868 COMMIT;
1869 WHEN GEN_CONFIG_TREE_ERR THEN
1870 l_errbuf := CZ_UTILS.GET_TEXT('CZ_NET_API_TREE_GEN_ERR','HDR',l_config_hdr_id,'REV',l_config_rev_nbr);
1871 set_fnd_message('CZ_NET_API_TREE_GEN_ERR', 'HDR',l_config_hdr_id,'REV',l_config_rev_nbr);
1872 set_error_message(x_return_status,x_msg_count,x_msg_data,l_errbuf);
1873 COMMIT;
1874 WHEN OTHERS THEN
1875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1876 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1877 FND_MSG_PUB.add_exc_msg(G_PKG_NAME,l_api_name);
1878 END IF;
1879 FND_MSG_PUB.count_and_get(NULL,x_msg_count,x_msg_data);
1880 COMMIT;
1881 END add_to_config_tree;
1882
1883 -----------------------------------------------------------------------------
1884 -- API name: is_container_pvt
1885 -- API type: private
1886 -- Function: Checks if a given model is a network container model
1887 -- Pre-Reqs: input model is in cz schema
1888
1889 FUNCTION is_container_pvt(p_model_id IN NUMBER)
1890 RETURN BOOLEAN
1891
1892 IS
1893 l_model_type cz_devl_projects.model_type%TYPE;
1894
1895 BEGIN
1896
1897 SELECT model_type INTO l_model_type
1898 FROM cz_devl_projects
1899 WHERE devl_project_id = p_model_id
1900 AND deleted_flag = NO_FLAG;
1901
1902 RETURN (upper(l_model_type) = NETWORK);
1903
1904 EXCEPTION
1905 WHEN OTHERS THEN
1906 RAISE;
1907 END is_container_pvt;
1908
1909 -----------------------------------------------------------------------------
1910 -- API name: get_contained_models_pvt
1911 -- API type: private
1912 -- Function: Retrieves all trackable and instantiable child models for a
1913 -- given network container model.
1914
1915 procedure get_contained_models_pvt(p_model_id IN NUMBER
1916 ,p_inventory_item_id_tbl IN OUT NOCOPY CZ_API_PUB.number_tbl_type
1917 )
1918 IS
1919 l_model_id NUMBER := p_model_id;
1920 l_reference_id NUMBER;
1921 l_inventory_item_id NUMBER := NULL;
1922 l_index NUMBER := 0;
1923 l_count NUMBER;
1924
1925 CURSOR child_model_csr IS
1926 SELECT reference_id
1927 FROM cz_ps_nodes
1928 WHERE devl_project_id = l_model_id
1929 AND ps_node_type = PS_NODE_TYPE_REFERENCE
1930 AND deleted_flag = NO_FLAG;
1931
1932 BEGIN
1933 FOR child_model_rec IN child_model_csr
1934 LOOP
1935 l_reference_id := child_model_rec.reference_id;
1936 BEGIN
1937 SELECT to_number(substr(orig_sys_ref, instr(orig_sys_ref, ':', -1, 1)+1))
1938 INTO l_inventory_item_id
1939 FROM cz_ps_nodes
1940 WHERE ps_node_id = l_reference_id
1941 AND ib_trackable = YES_FLAG
1942 AND deleted_flag = NO_FLAG;
1943
1944 -- check if the model is already in array
1945 -- Note: if arrray size is big, use hashing insead of iteration
1946 l_index := 1;
1947 l_count := p_inventory_item_id_tbl.COUNT;
1948
1949 WHILE (l_index <= l_count AND l_inventory_item_id <> p_inventory_item_id_tbl(l_index))
1950 LOOP
1951 l_index := l_index + 1;
1952 END LOOP;
1953
1954 -- new entry
1955 IF (l_index > l_count) THEN
1956 p_inventory_item_id_tbl.extend(1);
1957 p_inventory_item_id_tbl(l_index) := l_inventory_item_id;
1958 END IF;
1959 l_inventory_item_id := NULL;
1960 EXCEPTION
1961 WHEN NO_DATA_FOUND THEN -- not trackable, lookup further down the branch
1962 get_contained_models_pvt(l_reference_id, p_inventory_item_id_tbl);
1963 END;
1964 END LOOP;
1965 EXCEPTION
1966 WHEN OTHERS THEN
1967 RAISE;
1968 END get_contained_models_pvt;
1969
1970 -----------------------------------------------------------------------------
1971 -- API name: get_contained_models
1972 -- API type: public
1973 -- Function: Retrieves all possible enclosed trackable child models for the network
1974 -- container model specified by the input inventory_item_id and
1975 -- organization_id
1976
1977 procedure get_contained_models(p_api_version IN NUMBER
1978 ,p_inventory_item_id IN NUMBER
1979 ,p_organization_id IN NUMBER
1980 ,p_appl_param_rec IN CZ_API_PUB.appl_param_rec_type
1981 ,x_model_tbl OUT NOCOPY CZ_API_PUB.number_tbl_type
1982 ,x_return_status OUT NOCOPY VARCHAR2
1983 ,x_msg_count OUT NOCOPY NUMBER
1984 ,x_msg_data OUT NOCOPY VARCHAR2
1985 )
1986 IS
1987 l_api_version CONSTANT NUMBER := 1.0;
1988 l_api_name CONSTANT VARCHAR2(30) := 'get_contained_models';
1989
1990 l_config_creation_date DATE := p_appl_param_rec.config_creation_date;
1991 l_config_model_lookup_date DATE := p_appl_param_rec.config_model_lookup_date;
1992 l_config_effective_date DATE := p_appl_param_rec.config_effective_date;
1993
1994 l_model_id NUMBER;
1995 l_inventory_item_id_tbl CZ_API_PUB.number_tbl_type := cz_api_pub.NUMBER_TBL_TYPE();
1996 l_msg_data VARCHAR2(2000);
1997 l_appl_param_rec CZ_API_PUB.appl_param_rec_type;
1998
1999 BEGIN
2000 x_return_status := FND_API.G_RET_STS_SUCCESS;
2001 fnd_msg_pub.initialize;
2002 x_model_tbl := cz_api_pub.NUMBER_TBL_TYPE();
2003 -- standard call to check for call compatibility
2004 IF (NOT FND_API.compatible_api_call(l_api_version
2005 ,p_api_version
2006 ,l_api_name
2007 ,G_PKG_NAME
2008 )) THEN
2009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2010 END IF;
2011
2012 ----default pb applicability parameters to NULL if no values are passed in
2013 l_appl_param_rec.config_creation_date := p_appl_param_rec.config_creation_date;
2014 l_appl_param_rec.config_model_lookup_date := p_appl_param_rec.config_model_lookup_date;
2015 l_appl_param_rec.config_effective_date := p_appl_param_rec.config_effective_date;
2016 l_appl_param_rec.calling_application_id := p_appl_param_rec.calling_application_id;
2017 l_appl_param_rec.usage_name := p_appl_param_rec.usage_name;
2018 l_appl_param_rec.publication_mode := p_appl_param_rec.publication_mode;
2019 l_appl_param_rec.language := p_appl_param_rec.language;
2020
2021 default_pb_parameters(l_appl_param_rec,x_return_status);
2022 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2023 RAISE FND_API.G_EXC_ERROR;
2024 END IF;
2025
2026 -- defaulting date values if not passed in
2027 cz_cf_api.default_new_cfg_dates(l_appl_param_rec.config_creation_date
2028 ,l_appl_param_rec.config_model_lookup_date
2029 ,l_appl_param_rec.config_effective_date
2030 );
2031
2032 -- publication look up
2033 l_model_id := cz_cf_api.config_model_for_item
2034 (p_inventory_item_id
2035 ,p_organization_id
2036 ,l_appl_param_rec.config_model_lookup_date
2037 ,l_appl_param_rec.calling_application_id
2038 ,l_appl_param_rec.usage_name
2039 ,l_appl_param_rec.publication_mode
2040 ,l_appl_param_rec.language
2041 );
2042
2043 -- in case of publication look up failure
2044 IF (l_model_id IS NULL) THEN
2045 fnd_message.set_name('CZ', 'CZ_NO_PUB_MODEL');
2046 fnd_message.set_token('inventory_item_id', p_inventory_item_id);
2047 fnd_message.set_token('organization', p_organization_id);
2048 fnd_msg_pub.add;
2049 RAISE FND_API.G_EXC_ERROR;
2050 END IF;
2051
2052 -- must be a network container model
2053 IF (NOT is_container_pvt(l_model_id)) THEN
2054 fnd_message.set_name('CZ', 'CZ_NOT_CONTAINER_MODEL');
2055 fnd_message.set_token('model', l_model_id);
2056 fnd_msg_pub.add;
2057
2058 RAISE FND_API.G_EXC_ERROR;
2059 END IF;
2060
2061 get_contained_models_pvt(l_model_id, l_inventory_item_id_tbl);
2062 x_model_tbl := l_inventory_item_id_tbl;
2063
2064 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2065 p_data => x_msg_data);
2066
2067 EXCEPTION
2068 WHEN FND_API.G_EXC_ERROR THEN
2069 x_return_status := FND_API.G_RET_STS_ERROR;
2070 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2071 p_data => x_msg_data);
2072
2073 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2075 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2076 p_data => x_msg_data);
2077
2078 WHEN OTHERS THEN
2079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2080 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
2081 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
2082 END IF;
2083 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2084 p_data => x_msg_data);
2085
2086 END get_contained_models;
2087
2088 -----------------------------------------------------------------------------
2089 -- API name: is_container
2090 -- API type: public
2091 -- Function: Checks if a model specified by the top inventory_item_id and
2092 -- organization_id is a network container model.
2093
2094 procedure is_container(p_api_version IN NUMBER
2095 ,p_inventory_item_id IN NUMBER
2096 ,p_organization_id IN NUMBER
2097 ,p_appl_param_rec IN CZ_API_PUB.appl_param_rec_type
2098 ,x_return_value OUT NOCOPY VARCHAR2
2099 ,x_return_status OUT NOCOPY VARCHAR2
2100 ,x_msg_count OUT NOCOPY NUMBER
2101 ,x_msg_data OUT NOCOPY VARCHAR2
2102 )
2103 IS
2104 l_api_version CONSTANT NUMBER := 1.0;
2105 l_api_name CONSTANT VARCHAR2(30) := 'is_container';
2106 BEGIN
2107 fnd_msg_pub.initialize;
2108
2109 -- standard call to check for call compatibility
2110 IF (NOT FND_API.compatible_api_call(l_api_version
2111 ,p_api_version
2112 ,l_api_name
2113 ,G_PKG_NAME
2114 )) THEN
2115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2116 END IF;
2117
2118 SELECT DECODE(config_model_type, NETWORK_CONTAINER_MODEL, FND_API.G_TRUE, FND_API.G_FALSE)
2119 INTO x_return_value
2120 FROM mtl_system_items_b
2121 WHERE inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id;
2122
2123 x_return_status := FND_API.G_RET_STS_SUCCESS;
2124
2125 EXCEPTION
2126 WHEN NO_DATA_FOUND THEN
2127 x_return_value := NULL;
2128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2129 x_msg_count := 1;
2130 x_msg_data := 'The input inventory item ' || p_inventory_item_id || ' with organization ' ||
2131 p_organization_id || ' does not exist';
2132
2133 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2134 x_return_value := NULL;
2135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2136 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2137 p_data => x_msg_data);
2138 END is_container;
2139
2140 ---------------------------------------------------------------------------
2141 -- API name: is_configurable
2142 -- API type: public
2143 -- Function: Checks whether a config item is independently configurable
2144
2145 procedure is_configurable(p_api_version IN NUMBER
2146 ,p_config_hdr_id IN NUMBER
2147 ,p_config_rev_nbr IN NUMBER
2148 ,p_config_item_id IN NUMBER
2149 ,x_return_value OUT NOCOPY VARCHAR2
2150 ,x_return_status OUT NOCOPY VARCHAR2
2151 ,x_msg_count OUT NOCOPY NUMBER
2152 ,x_msg_data OUT NOCOPY VARCHAR2
2153 )
2154 IS
2155 l_api_version CONSTANT NUMBER := 1.0;
2156 l_api_name CONSTANT VARCHAR2(30) := 'is_configurable';
2157
2158 l_component_instance_type cz_config_items.component_instance_type%TYPE;
2159
2160 BEGIN
2161 IF (NOT FND_API.compatible_api_call(l_api_version
2162 ,p_api_version
2163 ,l_api_name
2164 ,G_PKG_NAME
2165 )) THEN
2166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2167 END IF;
2168
2169 BEGIN
2170 SELECT component_instance_type INTO l_component_instance_type
2171 FROM cz_config_items
2172 WHERE config_hdr_id = p_config_hdr_id
2173 AND config_rev_nbr = p_config_rev_nbr
2174 AND config_item_id = p_config_item_id
2175 AND deleted_flag = NO_FLAG;
2176
2177 EXCEPTION
2178 WHEN NO_DATA_FOUND THEN
2179 fnd_message.set_name('CZ','CZ_NO_CFG_ITEM');
2180 fnd_message.set_token('hdr_id','p_config_hdr_id');
2181 fnd_message.set_token('revision','p_config_hdr_id');
2182 fnd_message.set_token('item', p_config_item_id);
2183 fnd_msg_pub.add;
2184 RAISE FND_API.G_EXC_ERROR;
2185 END;
2186
2187 IF (l_component_instance_type = INSTANCE_ROOT) THEN
2188 x_return_value := FND_API.G_TRUE;
2189 ELSE
2190 x_return_value := FND_API.G_FALSE;
2191 END IF;
2192
2193 x_return_status := FND_API.G_RET_STS_SUCCESS;
2194 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2195 p_data => x_msg_data);
2196
2197 EXCEPTION
2198 WHEN FND_API.G_EXC_ERROR THEN
2199 x_return_status := FND_API.G_RET_STS_ERROR;
2200 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2201 p_data => x_msg_data);
2202
2203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2205 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2206 p_data => x_msg_data);
2207
2208 WHEN OTHERS THEN
2209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2210 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
2211 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
2212 END IF;
2213
2214 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2215 p_data => x_msg_data);
2216
2217 END is_configurable;
2218
2219 ------------------------------------------------------------------------
2220 -- API name: is_rma_allowed
2221 -- API type: public
2222 -- Function: Checks if a configurable item instance can be split
2223
2224 procedure is_rma_allowed(p_api_version IN NUMBER
2225 ,p_config_hdr_id IN NUMBER
2226 ,p_config_rev_nbr IN NUMBER
2227 ,p_config_item_id IN NUMBER
2228 ,x_return_value OUT NOCOPY VARCHAR2
2229 ,x_return_status OUT NOCOPY VARCHAR2
2230 ,x_msg_count OUT NOCOPY NUMBER
2231 ,x_msg_data OUT NOCOPY VARCHAR2
2232 )
2233 IS
2234 l_api_version CONSTANT NUMBER := 1.0;
2235 l_api_name CONSTANT VARCHAR2(30) := 'is_rma_allowed';
2236
2237 l_model_instantiation_type cz_config_hdrs.model_instantiation_type%TYPE;
2238 l_component_instance_type cz_config_hdrs.component_instance_type%TYPE;
2239
2240 BEGIN
2241 IF (NOT FND_API.compatible_api_call(l_api_version
2242 ,p_api_version
2243 ,l_api_name
2244 ,G_PKG_NAME
2245 )) THEN
2246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2247 END IF;
2248
2249 get_header_types(p_config_hdr_id
2250 ,p_config_rev_nbr
2251 ,l_model_instantiation_type
2252 ,l_component_instance_type
2253 );
2254 IF (l_model_instantiation_type = NO_FLAG) THEN
2255 fnd_message.set_name('CZ','CZ_NO_CFG');
2256 fnd_message.set_token('hdr_id','p_config_hdr_id');
2257 fnd_message.set_token('revision','p_config_hdr_id');
2258 fnd_msg_pub.add;
2259 RAISE FND_API.G_EXC_ERROR;
2260 END IF;
2261
2262 IF (l_model_instantiation_type <> NETWORK) THEN
2263 x_return_value := FND_API.G_TRUE;
2264 ELSE
2265 x_return_value := FND_API.G_FALSE;
2266 END IF;
2267
2268 x_return_status := FND_API.G_RET_STS_SUCCESS;
2269 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2270 p_data => x_msg_data);
2271
2272 EXCEPTION
2273 WHEN FND_API.G_EXC_ERROR THEN
2274 x_return_status := FND_API.G_RET_STS_ERROR;
2275 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2276 p_data => x_msg_data);
2277
2278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2280 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2281 p_data => x_msg_data);
2282
2283 WHEN OTHERS THEN
2284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2285 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
2286 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
2287 END IF;
2288
2289 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2290 p_data => x_msg_data);
2291
2292 END is_rma_allowed;
2293
2294 ---------------------------------------------------------------------
2295 ------procedure that gets the root instance , component_instance_type = 'I'
2296 ------for the config_item_id that is passed in
2297 PROCEDURE get_root_instance(p_config_hdr_id IN NUMBER,
2298 p_config_rev_nbr IN NUMBER,
2299 p_config_item_id IN NUMBER,
2300 x_root_inst_changed OUT NOCOPY BOOLEAN)
2301 IS
2302
2303 CURSOR config_sub_tree_cur IS
2304 SELECT component_instance_type,config_delta,ext_activated_flag
2305 FROM cz_config_items
2306 WHERE config_hdr_id = p_config_hdr_id
2307 AND config_rev_nbr = p_config_rev_nbr
2308 AND deleted_flag = NO_FLAG
2309 START WITH config_item_id = p_config_item_id
2310 AND config_hdr_id = p_config_hdr_id
2311 AND config_rev_nbr = p_config_rev_nbr
2312 AND deleted_flag = NO_FLAG
2313 CONNECT BY PRIOR parent_config_item_id = config_item_id
2314 AND config_hdr_id = p_config_hdr_id
2315 AND config_rev_nbr = p_config_rev_nbr
2316 AND deleted_flag = NO_FLAG;
2317
2318 l_component_instance_type cz_config_items.component_instance_type%TYPE;
2319 l_config_delta cz_config_items.config_delta%TYPE;
2320 l_ext_activated_flag cz_config_items.ext_activated_flag%TYPE;
2321
2322 BEGIN
2323 x_root_inst_changed := FALSE;
2324 OPEN config_sub_tree_cur;
2325 LOOP
2326 FETCH config_sub_tree_cur
2327 INTO l_component_instance_type, l_config_delta, l_ext_activated_flag;
2328 EXIT WHEN config_sub_tree_cur%NOTFOUND;
2329 IF (l_component_instance_type = 'I') THEN
2330 EXIT;
2331 END IF;
2332 END LOOP;
2333 CLOSE config_sub_tree_cur;
2334
2335 IF (l_component_instance_type = 'I') THEN
2336 IF ((l_config_delta <> 0) AND (l_ext_activated_flag <> '1')) THEN
2337 x_root_inst_changed := TRUE;
2338 END IF;
2339 ELSE
2340 x_root_inst_changed := FALSE;
2341 END IF;
2342 EXCEPTION
2343 WHEN OTHERS THEN
2344 IF (config_sub_tree_cur%ISOPEN) THEN
2345 CLOSE config_sub_tree_cur;
2346 END IF;
2347 END get_root_instance;
2348 -------------------------------------------
2349 -----procedure that retrieves the children of the config item id
2350 -----that has been passed in (for a config hdr and config rev)
2351 PROCEDURE get_child_items(p_config_hdr_id IN NUMBER,
2352 p_config_rev_nbr IN NUMBER,
2353 p_config_item_id IN NUMBER,
2354 x_child_config_tbl OUT NOCOPY CZ_API_PUB.number_tbl_type)
2355
2356 IS
2357
2358 BEGIN
2359 SELECT config_item_id BULK COLLECT INTO x_child_config_tbl
2360 FROM cz_config_items
2361 WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
2362 AND deleted_flag = NO_FLAG
2363 START WITH config_item_id = p_config_item_id
2364 AND config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
2365 AND deleted_flag = NO_FLAG
2366 CONNECT BY PRIOR config_item_id = parent_config_item_id
2367 AND config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
2368 AND deleted_flag = NO_FLAG;
2369 EXCEPTION
2370 WHEN NO_DATA_FOUND THEN
2371 ---the query returns no rows so ignore
2372 NULL;
2373 WHEN OTHERS THEN
2374 RAISE;
2375 END get_child_items;
2376 -----------------------------------------------------------------------------
2377 -- API name: ext_deactivate_item
2378 -- API type: public
2379 -- Function: Externally deactivates an instance from CZ_CONFIG_DETAILS_V
2380 -- Note : Marks the specified config item and all of its child config items as
2381 -- externally deactivated. If any of those items has no other changes,
2382 -- they will be filtered OUT NOCOPY from the view CZ_CONFIG_DETAILS_V.
2383
2384 procedure ext_deactivate_item(p_api_version IN NUMBER
2385 ,p_config_hdr_id IN NUMBER
2386 ,p_config_rev_nbr IN NUMBER
2387 ,p_config_item_id IN NUMBER
2388 ,x_return_status OUT NOCOPY VARCHAR2
2389 ,x_msg_count OUT NOCOPY NUMBER
2390 ,x_msg_data OUT NOCOPY VARCHAR2
2391 )
2392 IS
2393 l_api_version CONSTANT NUMBER := 1.0;
2394 l_api_name VARCHAR2(30) := 'ext_deactivate_item';
2395 l_ext_activated_flag VARCHAR2(1);
2396 l_root_inst_changed BOOLEAN;
2397 v_child_config_tbl CZ_API_PUB.number_tbl_type;
2398 l_run_id NUMBER;
2399 l_config_item_id NUMBER;
2400 REMOVE_IB_ERR EXCEPTION;
2401
2402 BEGIN
2403 fnd_msg_pub.initialize;
2404 IF (NOT FND_API.compatible_api_call(l_api_version
2405 ,p_api_version
2406 ,l_api_name
2407 ,G_PKG_NAME
2408 )) THEN
2409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2410 END IF;
2411
2412 -----get root instance for the config_item_id
2413 get_root_instance(p_config_hdr_id,
2414 p_config_rev_nbr,
2415 p_config_item_id,
2416 l_root_inst_changed);
2417
2418 IF (l_root_inst_changed) THEN
2419 -----get all the children of the config item id that is passed in
2420 get_child_items (p_config_hdr_id,p_config_rev_nbr,p_config_item_id,v_child_config_tbl);
2421
2422 IF (v_child_config_tbl.COUNT > 0) THEN
2423 FOR childItem IN v_child_config_tbl.FIRST..v_child_config_tbl.LAST
2424 LOOP
2425 l_config_item_id := v_child_config_tbl(childItem);
2426 cz_ib_transactions.remove_ib_config(p_session_config_hdr_id => p_config_hdr_id,
2427 p_session_config_rev_nbr => p_config_rev_nbr,
2428 p_instance_item_id => l_config_item_id,
2429 x_run_id => l_run_id
2430 );
2431 IF (l_run_id > 0) THEN
2432 RAISE REMOVE_IB_ERR;
2433 END IF;
2434 END LOOP;
2435 END IF;
2436 END IF;
2437
2438 l_ext_activated_flag := '1';
2439 UPDATE cz_config_items
2440 SET ext_activated_flag = NO_FLAG
2441 WHERE ext_activated_flag = l_ext_activated_flag
2442 AND config_hdr_id = p_config_hdr_id
2443 AND config_rev_nbr = p_config_rev_nbr
2444 AND config_item_id IN
2445 (SELECT config_item_id
2446 FROM cz_config_items
2447 WHERE config_hdr_id = p_config_hdr_id
2448 AND config_rev_nbr = p_config_rev_nbr
2449 AND deleted_flag = NO_FLAG
2450 START WITH config_item_id = p_config_item_id
2451 AND config_hdr_id = p_config_hdr_id
2452 AND config_rev_nbr = p_config_rev_nbr
2453 AND deleted_flag = NO_FLAG
2454 CONNECT BY PRIOR config_item_id = parent_config_item_id
2455 AND config_hdr_id = p_config_hdr_id
2456 AND config_rev_nbr = p_config_rev_nbr
2457 AND deleted_flag = NO_FLAG);
2458
2459 IF (SQL%ROWCOUNT > 0) THEN
2460 x_return_status := FND_API.G_RET_STS_SUCCESS;
2461 ELSE
2462 x_return_status := FND_API.G_RET_STS_ERROR;
2463 set_fnd_message('CZ_IB_API_NO_DEACTIVATE','ConfigHdrId',p_config_hdr_id, 'ConfigRevNbr', p_config_rev_nbr);
2464 END IF;
2465 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
2466
2467 EXCEPTION
2468 WHEN REMOVE_IB_ERR THEN
2469 x_return_status := FND_API.G_RET_STS_ERROR;
2470 set_fnd_message('CZ_IB_DEACT_REMOVE_IB_ERR',
2471 'ConfigHdrId', p_config_hdr_id,
2472 'ConfigRevNbr', p_config_rev_nbr,
2473 'ConfigItemId', l_config_item_id,
2474 'RunId', l_run_id);
2475 fnd_msg_pub.count_and_get(p_count => x_msg_count,p_data => x_msg_data);
2476 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2478 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2479 p_data => x_msg_data);
2480 WHEN OTHERS THEN
2481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2482 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
2483 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
2484 END IF;
2485
2486 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2487 p_data => x_msg_data);
2488 END ext_deactivate_item;
2489
2490 ------------------------------------------------------------------------------------------
2491 /* The CZ stub patch for use as a prereq by other teams will contain CZ_NETWORK_API_PUB.validate
2492 but not CZ_CF_API.validate. Existing customers have CZ_CF_API.validate,
2493 but without the new p_validation_type parameter. If the CZ stub patch is applied
2494 with your product's MACD changes but without CZ's MACD changes,
2495 your code will not compile if it calls CZ_CF_API.validate with p_validation_type. */
2496
2497 PROCEDURE VALIDATE(config_input_list IN CZ_CF_API.CFG_INPUT_LIST, -- input selections
2498 init_message IN VARCHAR2, -- additional XML
2499 config_messages IN OUT NOCOPY CZ_CF_API.CFG_OUTPUT_PIECES, -- table of output XML messages
2500 validation_status IN OUT NOCOPY NUMBER, -- status return
2501 URL IN VARCHAR2 DEFAULT FND_PROFILE.Value('CZ_UIMGR_URL'),
2502 p_validation_type IN VARCHAR2 DEFAULT CZ_API_PUB.VALIDATE_ORDER
2503 )
2504
2505 IS
2506
2507 BEGIN
2508 cz_cf_api.validate( config_input_list,
2509 init_message,
2510 config_messages,
2511 validation_status,
2512 URL,
2513 p_validation_type);
2514 END VALIDATE;
2515
2516
2517 -- The "is_item_added" function returns 1 if the config item has an "add" delta, 0 if not.
2518 -- Note that p_config_hdr_id and p_config_rev_nbr are for the session header, not the instance header.
2519
2520 FUNCTION is_item_added (p_config_hdr_id IN NUMBER,
2521 p_config_rev_nbr IN NUMBER,
2522 p_config_item_id IN NUMBER) RETURN pls_integer IS
2523 l_delta pls_integer;
2524 l_hex_format varchar2(20) := 'FM0000X';
2525 -- add bit is bit 1, which has a value of 2 when turned on
2526 l_add_bit pls_integer := 2;
2527 BEGIN
2528 SELECT config_delta INTO l_delta
2529 FROM cz_config_items
2530 WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
2531 AND config_item_id = p_config_item_id;
2532
2533 IF to_number(utl_raw.bit_and(to_char(l_delta,l_hex_format), to_char(l_add_bit,l_hex_format))) > 0 THEN
2534 RETURN 1;
2535 ELSE
2536 RETURN 0;
2537 END IF;
2538 END is_item_added;
2539
2540 ----------------------------------------------------------------------
2541 END CZ_NETWORK_API_PUB;