[Home] [Help]
PACKAGE BODY: APPS.CZ_CONFIG_API_PUB
Source
1 PACKAGE BODY CZ_CONFIG_API_PUB AS
2 /* $Header: czcfgapb.pls 120.1.12010000.3 2009/02/13 14:49:52 asiaston ship $ */
3
4 -- model_instantiation_type
5 NETWORK CONSTANT VARCHAR2(1) := 'N';
6
7 -- component_instance_type
8 ROOT CONSTANT VARCHAR2(1) := 'R';
9 GENERIC_INSTANCE_ROOT CONSTANT VARCHAR2(1) := 'C';
10 NETWORK_INSTANCE_ROOT CONSTANT VARCHAR2(1) := 'I';
11
12 CONFIG_STATUS_COMPLETE CONSTANT VARCHAR2(1) := '2';
13
14 BATCH_COPY_SIZE CONSTANT INTEGER := 500;
15
16 NEW_HDR_NAME_PREFIX CONSTANT VARCHAR2(25) := 'Copy of ';
17 NEW_REV_NAME_PREFIX CONSTANT VARCHAR2(25) := 'New revision of ';
18
19 last_hdr_allocated NUMBER := NULL;
20 next_hdr_to_use NUMBER := 0;
21 last_item_allocated NUMBER := NULL;
22 next_item_to_use NUMBER := 0;
23 last_msg_seq_allocated NUMBER := NULL;
24 next_msg_seq_to_use NUMBER := 0;
25 id_increment NUMBER;
26 DEFAULT_INCR CONSTANT PLS_INTEGER := 20;
27
28 TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(15);-- Bug 6892148;
29
30 TYPE instance_rec_type IS RECORD
31 ( instance_hdr_id cz_config_hdrs.config_hdr_id%TYPE
32 ,instance_rev_nbr cz_config_hdrs.config_rev_nbr%TYPE
33 ,txn_flag VARCHAR2(1)
34 );
35
36 TYPE instance_tbl_type IS TABLE of instance_rec_type INDEX BY PLS_INTEGER;
37
38 TYPE config_hdr_tbl_type IS TABLE OF cz_config_hdrs%ROWTYPE INDEX BY PLS_INTEGER;
39 TYPE config_item_tbl_type IS TABLE OF cz_config_items%ROWTYPE INDEX BY PLS_INTEGER;
40 TYPE config_input_tbl_type IS TABLE OF cz_config_inputs%ROWTYPE INDEX BY PLS_INTEGER;
41 TYPE config_attr_tbl_type IS TABLE OF cz_config_attributes%ROWTYPE INDEX BY PLS_INTEGER;
42 TYPE config_extattr_tbl_type IS TABLE OF cz_config_ext_attributes%ROWTYPE INDEX BY PLS_INTEGER;
43
44 --------------------------------------------------------------------------------
45 FUNCTION get_next_hdr_id RETURN NUMBER
46 IS
47 l_config_hdr_id NUMBER;
48
49 BEGIN
50 IF ((last_hdr_allocated IS NULL) OR
51 (next_hdr_to_use = last_hdr_allocated + id_increment)) THEN
52 SELECT cz_config_hdrs_s.NEXTVAL
53 INTO last_hdr_allocated
54 FROM dual;
55 next_hdr_to_use := last_hdr_allocated;
56 END IF;
57
58 l_config_hdr_id := next_hdr_to_use;
59 next_hdr_to_use := next_hdr_to_use + 1;
60 RETURN l_config_hdr_id;
61 END get_next_hdr_id;
62
63 --------------------------------------------------------------------------------
64 FUNCTION get_next_revision(p_config_hdr_id IN NUMBER)
65 RETURN NUMBER
66 IS
67 l_config_rev_nbr NUMBER;
68
69 BEGIN
70 SELECT MAX(CONFIG_REV_NBR) + 1 INTO l_config_rev_nbr
71 FROM CZ_CONFIG_HDRS
72 WHERE CONFIG_HDR_ID = p_config_hdr_id;
73
74 RETURN l_config_rev_nbr;
75
76 EXCEPTION
77 WHEN OTHERS THEN
78 RAISE;
79
80 END get_next_revision;
81
82 --------------------------------------------------------------------------------
83 FUNCTION get_next_item_id RETURN NUMBER
84 IS
85 l_config_item_id NUMBER;
86 BEGIN
87 IF ((last_item_allocated IS NULL) OR
88 (next_item_to_use = last_item_allocated + id_increment)) THEN
89 SELECT cz_config_items_s.NEXTVAL
90 INTO last_item_allocated
91 FROM dual;
92 next_item_to_use := last_item_allocated;
93 END IF;
94 l_config_item_id := next_item_to_use;
95 next_item_to_use := next_item_to_use + 1;
96 RETURN l_config_item_id;
97 END get_next_item_id;
98
99 --------------------------------------------------------------------------------
100 FUNCTION get_next_msg_seq RETURN NUMBER
101 IS
102 l_msg_seq NUMBER;
103
104 BEGIN
105 IF ((last_msg_seq_allocated IS NULL) OR
106 (next_msg_seq_to_use = last_msg_seq_allocated + id_increment)) THEN
107 SELECT cz_config_messages_s.NEXTVAL INTO last_msg_seq_allocated FROM dual;
108 next_msg_seq_to_use := last_msg_seq_allocated;
109 END IF;
110
111 l_msg_seq := next_msg_seq_to_use;
112 next_msg_seq_to_use := next_msg_seq_to_use + 1;
113 RETURN l_msg_seq;
114 END get_next_msg_seq;
115
116 --------------------------------------------------------------------------------
117 PROCEDURE copy_config_header(p_old_config_hdr_id IN NUMBER
118 ,p_old_config_rev_nbr IN NUMBER
119 ,p_new_config_hdr_id IN NUMBER
120 ,p_new_config_rev_nbr IN NUMBER
121 ,p_new_name IN VARCHAR2
122 ,p_copy_mode IN VARCHAR2
123 )
124 IS
125 BEGIN
126 INSERT INTO CZ_CONFIG_HDRS
127 (CONFIG_HDR_ID
128 ,NAME
129 ,CONFIG_REV_NBR
130 ,COMPONENT_ID
131 ,PERSISTENT_COMPONENT_ID
132 ,DESC_TEXT
133 ,UI_DEF_ID
134 ,OPPORTUNITY_HDR_ID
135 ,CONFIG_STATUS
136 ,CONFIG_DATE_CREATED
137 ,CONFIG_NOTE
138 ,USER_ID_CREATED
139 ,USER_ID_FOR_WHOM_CREATED
140 ,NUMBER_QUOTES_USED_IN
141 ,USER_NUM01
142 ,USER_NUM02
143 ,USER_NUM03
144 ,USER_NUM04
145 ,USER_STR01
146 ,USER_STR02
147 ,USER_STR03
148 ,USER_STR04
149 ,DELETED_FLAG
150 ,SECURITY_MASK
151 ,CHECKOUT_USER
152 ,LAST_UPDATE_LOGIN
153 ,MODEL_IDENTIFIER
154 ,EFFECTIVE_DATE
155 ,EFFECTIVE_USAGE_ID
156 ,CONFIG_MODEL_LOOKUP_DATE
157 ,CONFIG_DELTA_SPEC
158 ,MODEL_INSTANTIATION_TYPE
159 ,COMPONENT_INSTANCE_TYPE
160 ,BASELINE_REV_NBR
161 ,HAS_FAILURES
162 ,MODEL_POST_MIGR_CHG_FLAG
163 ,TO_BE_DELETED_FLAG
164 ,AUTO_COMPLETION_FLAG
165 ,CONFIG_ENGINE_TYPE
166 )
167 SELECT
168 p_new_config_hdr_id -- new or old value
169 ,SUBSTR(DECODE(p_new_name, NULL, DECODE(NAME,NULL,NAME,DECODE(p_copy_mode,CZ_API_PUB.G_NEW_HEADER_COPY_MODE,
170 NEW_HDR_NAME_PREFIX || NAME, NEW_REV_NAME_PREFIX || NAME)), p_new_name), 1, 240)
171 ,p_new_config_rev_nbr -- new value
172 ,COMPONENT_ID
173 ,PERSISTENT_COMPONENT_ID
174 ,DESC_TEXT
175 ,UI_DEF_ID
176 ,OPPORTUNITY_HDR_ID
177 ,CONFIG_STATUS
178 ,CONFIG_DATE_CREATED
179 ,CONFIG_NOTE
180 ,USER_ID_CREATED
181 ,USER_ID_FOR_WHOM_CREATED
182 ,NUMBER_QUOTES_USED_IN
183 ,USER_NUM01
184 ,USER_NUM02
185 ,USER_NUM03
186 ,USER_NUM04
187 ,USER_STR01
188 ,USER_STR02
189 ,USER_STR03
190 ,USER_STR04
191 ,DELETED_FLAG
192 ,SECURITY_MASK
193 ,CHECKOUT_USER
194 ,LAST_UPDATE_LOGIN
195 ,MODEL_IDENTIFIER
196 ,EFFECTIVE_DATE
197 ,EFFECTIVE_USAGE_ID
198 ,CONFIG_MODEL_LOOKUP_DATE
199 ,CONFIG_DELTA_SPEC
200 ,MODEL_INSTANTIATION_TYPE
201 ,COMPONENT_INSTANCE_TYPE
202 ,BASELINE_REV_NBR
203 ,HAS_FAILURES
204 ,MODEL_POST_MIGR_CHG_FLAG
205 ,TO_BE_DELETED_FLAG
206 ,AUTO_COMPLETION_FLAG
207 ,CONFIG_ENGINE_TYPE
208 FROM CZ_CONFIG_HDRS
209 WHERE CONFIG_HDR_ID = p_old_config_hdr_id
210 AND CONFIG_REV_NBR = p_old_config_rev_nbr;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 RAISE;
215
216 END copy_config_header;
217
218 --------------------------------------------------------------------------------
219 -- Copies all instance header records associated with the input session header
220 -- Returns an instance header lookup map: key, old instance header id; value,
221 -- record of new instance header id and revision as well as a flag indicating
222 -- if transaction needs to be written for network instance
223 PROCEDURE copy_instance_headers(p_session_hdr_id IN NUMBER
224 ,p_session_rev_nbr IN NUMBER
225 ,p_copy_mode IN VARCHAR2
226 ,x_instance_hdr_map OUT NOCOPY instance_tbl_type
227 )
228 IS
229 l_instance_hdr_id NUMBER;
230 l_instance_hdr_tbl config_hdr_tbl_type;
231
232 BEGIN
233 SELECT * BULK COLLECT INTO l_instance_hdr_tbl
234 FROM cz_config_hdrs
235 WHERE deleted_flag = '0' AND (config_hdr_id, config_rev_nbr) IN
236 (SELECT instance_hdr_id, instance_rev_nbr
237 FROM cz_config_items
238 WHERE config_hdr_id = p_session_hdr_id AND config_rev_nbr = p_session_rev_nbr
239 AND component_instance_type IN (GENERIC_INSTANCE_ROOT, NETWORK_INSTANCE_ROOT)
240 AND deleted_flag = '0');
241
242 IF l_instance_hdr_tbl.COUNT = 0 THEN RETURN; END IF;
243
244 -- generic: all depends on copy mode
245 -- network: depends on copy mode and baseline rev
246 -- new rev: copy all fields except config_rev_nbr (new rev)
247 -- new config: copy mode and baseline rev
248 -- 1. baseline_rev_nbr is not null: copy all fields except config_rev_nbr (new rev)
249 -- 2. baseline_rev_nbr is null: copy all fields except config_hdr_id and config_rev_nbr (new hdr)
250 FOR i IN l_instance_hdr_tbl.FIRST .. l_instance_hdr_tbl.LAST LOOP
251 l_instance_hdr_id := l_instance_hdr_tbl(i).config_hdr_id;
252
253 IF (p_copy_mode = CZ_API_PUB.G_NEW_REVISION_COPY_MODE OR
254 l_instance_hdr_tbl(i).component_instance_type = NETWORK_INSTANCE_ROOT AND
255 l_instance_hdr_tbl(i).baseline_rev_nbr IS NOT NULL) THEN
256 l_instance_hdr_tbl(i).config_rev_nbr := get_next_revision(l_instance_hdr_id);
257 ELSE
258 l_instance_hdr_tbl(i).config_hdr_id := get_next_hdr_id;
259 l_instance_hdr_tbl(i).config_rev_nbr := 1;
260 END IF;
261
262 IF l_instance_hdr_tbl(i).name IS NOT NULL THEN
263 IF l_instance_hdr_tbl(i).config_rev_nbr = 1 THEN
264 l_instance_hdr_tbl(i).name := SUBSTR(NEW_HDR_NAME_PREFIX||l_instance_hdr_tbl(i).name,1,240);
265 ELSE
266 l_instance_hdr_tbl(i).name := SUBSTR(NEW_REV_NAME_PREFIX||l_instance_hdr_tbl(i).name,1,240);
267 END IF;
268 END IF;
269
270 x_instance_hdr_map(l_instance_hdr_id).instance_hdr_id := l_instance_hdr_tbl(i).config_hdr_id;
271 x_instance_hdr_map(l_instance_hdr_id).instance_rev_nbr := l_instance_hdr_tbl(i).config_rev_nbr;
272 IF l_instance_hdr_tbl(i).component_instance_type = NETWORK_INSTANCE_ROOT AND
273 p_copy_mode = CZ_API_PUB.G_NEW_HEADER_COPY_MODE AND l_instance_hdr_tbl(i).baseline_rev_nbr IS NULL THEN
274 -- transaction writing needed for this instance
275 x_instance_hdr_map(l_instance_hdr_id).txn_flag := '1';
276 END IF;
277 END LOOP;
278
279 FORALL i IN 1..l_instance_hdr_tbl.COUNT
280 INSERT INTO cz_config_hdrs VALUES l_instance_hdr_tbl(i);
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 RAISE;
285
286 END copy_instance_headers;
287
288 --------------------------------------------------------------------------------
289 -- copy config containing no instance
290 PROCEDURE copy_config_item(p_old_config_hdr_id IN NUMBER,
291 p_old_config_rev_nbr IN NUMBER,
292 p_new_config_hdr_id IN NUMBER,
293 p_new_config_rev_nbr IN NUMBER)
294 IS
295
296 BEGIN
297 INSERT INTO CZ_CONFIG_ITEMS
298 (LAST_UPDATE_LOGIN,
299 CONFIG_HDR_ID,
300 CONFIG_REV_NBR,
301 CONFIG_ITEM_ID,
302 PARENT_CONFIG_ITEM_ID,
303 PS_NODE_ID,
304 ITEM_VAL,
305 ITEM_NUM_VAL,
306 INSTANCE_NBR,
307 ROOT_BOM_CONFIG_ITEM_ID,
308 SEQUENCE_NBR,
309 VALUE_TYPE_CODE,
310 DELETED_FLAG,
311 SECURITY_MASK,
312 CHECKOUT_USER,
313 NODE_IDENTIFIER,
314 INVENTORY_ITEM_ID,
315 ORGANIZATION_ID,
316 COMPONENT_SEQUENCE_ID,
317 UOM_CODE,
318 BOM_SORT_ORDER,
319 QUOTEABLE_FLAG,
320 BOM_ITEM_TYPE,
321 TARGET_CONFIG_ITEM_ID,
322 INSTANCE_CONFIG_ITEM_ID,
323 NAME,
324 ATO_CONFIG_ITEM_ID
325 ,INSTANCE_HDR_ID
326 ,INSTANCE_REV_NBR
327 ,LINE_TYPE
328 ,COMPONENT_INSTANCE_TYPE
329 ,TARGET_HDR_ID
330 ,TARGET_REV_NBR
331 ,CONFIG_DELTA
332 ,LOCATION_ID
333 ,LOCATION_TYPE_CODE
334 ,IB_TRACKABLE
335 ,EXT_ACTIVATED_FLAG
336 ,DISCONTINUED_FLAG
337 ,ORIG_SYS_REF
338 ,ITEM_SRC_APPL_ID
339 ,PS_NODE_NAME
340 ,TANGIBLE_ITEM_FLAG
341 ,RETURNED_FLAG
342 ,VALUE_SOURCE
343 ,ORDERABLE_FLAG
344 )
345 SELECT
346 LAST_UPDATE_LOGIN,
347 p_new_config_hdr_id,
348 p_new_config_rev_nbr,
349 CONFIG_ITEM_ID,
350 PARENT_CONFIG_ITEM_ID,
351 PS_NODE_ID,
352 ITEM_VAL,
353 ITEM_NUM_VAL,
354 INSTANCE_NBR,
355 ROOT_BOM_CONFIG_ITEM_ID,
356 SEQUENCE_NBR,
357 VALUE_TYPE_CODE,
358 DELETED_FLAG,
359 SECURITY_MASK,
360 CHECKOUT_USER,
361 NODE_IDENTIFIER,
362 INVENTORY_ITEM_ID,
363 ORGANIZATION_ID,
364 COMPONENT_SEQUENCE_ID,
365 UOM_CODE,
366 BOM_SORT_ORDER,
367 QUOTEABLE_FLAG,
368 BOM_ITEM_TYPE,
369 TARGET_CONFIG_ITEM_ID,
370 INSTANCE_CONFIG_ITEM_ID,
371 NAME,
372 ATO_CONFIG_ITEM_ID
373 ,p_new_config_hdr_id -- instance_hdr_id
374 ,p_new_config_rev_nbr -- instance_rev_nbr
375 ,LINE_TYPE
376 ,COMPONENT_INSTANCE_TYPE
377 ,DECODE(TARGET_HDR_ID, NULL, NULL, p_new_config_hdr_id)
378 ,DECODE(TARGET_REV_NBR, NULL, NULL, p_new_config_rev_nbr)
379 ,CONFIG_DELTA
380 ,LOCATION_ID
381 ,LOCATION_TYPE_CODE
382 ,IB_TRACKABLE
383 ,EXT_ACTIVATED_FLAG
384 ,DISCONTINUED_FLAG
385 ,ORIG_SYS_REF
386 ,ITEM_SRC_APPL_ID
387 ,PS_NODE_NAME
388 ,TANGIBLE_ITEM_FLAG
389 ,RETURNED_FLAG
390 ,VALUE_SOURCE
391 ,ORDERABLE_FLAG
392 FROM CZ_CONFIG_ITEMS
393 WHERE CONFIG_HDR_ID = p_old_config_hdr_id
394 AND CONFIG_REV_NBR = p_old_config_rev_nbr AND deleted_flag = '0';
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 RAISE;
399 END;
400
401 --------------------------------------------------------------------------------
402 PROCEDURE copy_config_item(p_old_config_hdr_id IN NUMBER
403 ,p_old_config_rev_nbr IN NUMBER
404 ,p_new_config_hdr_id IN NUMBER
405 ,p_new_config_rev_nbr IN NUMBER
406 ,p_copy_mode IN VARCHAR2
407 ,p_network_config IN BOOLEAN
408 ,p_instance_hdr_map IN instance_tbl_type
409 ,x_item_id_map OUT NOCOPY number_tbl_type
410 )
411 IS
412 l_config_item_id NUMBER;
413 l_parent_config_item_id NUMBER;
414 l_ato_config_item_id NUMBER;
415 l_instance_config_item_id NUMBER;
416 l_target_config_item_id NUMBER;
417 l_instance_hdr_id NUMBER;
418 l_instance_rev_nbr NUMBER;
419 l_target_hdr_id NUMBER;
420 l_target_rev_nbr NUMBER;
421 l_index PLS_INTEGER;
422 l_config_item_tbl config_item_tbl_type;
423
424 CURSOR item_cursor IS SELECT * FROM cz_config_items
425 WHERE config_hdr_id = p_old_config_hdr_id
426 AND config_rev_nbr = p_old_config_rev_nbr
427 AND deleted_flag = '0';
428
429 BEGIN
430 -- config containing no instance: plain copy
431 IF (p_instance_hdr_map.COUNT = 0) THEN
432 copy_config_item(p_old_config_hdr_id
433 ,p_old_config_rev_nbr
434 ,p_new_config_hdr_id
435 ,p_new_config_rev_nbr
436 );
437
438 ELSE
439 -- For network config, if copy mode is new_config and transaction needs to be written
440 -- for an instance, new instance config will be created and all config_item_ids of
441 -- the new instance items will be regenerated.
442 IF (p_network_config AND p_copy_mode = CZ_API_PUB.G_NEW_HEADER_COPY_MODE) THEN
443 l_index := p_instance_hdr_map.FIRST;
444 WHILE (l_index IS NOT NULL) LOOP
445 IF p_instance_hdr_map(l_index).txn_flag = '1' THEN
446 FOR id_rec IN (SELECT config_item_id FROM cz_config_items
447 WHERE config_hdr_id = p_old_config_hdr_id
448 AND config_rev_nbr = p_old_config_rev_nbr
449 AND deleted_flag = '0' AND instance_hdr_id = l_index)
450 LOOP
451 x_item_id_map(id_rec.config_item_id) := get_next_item_id;
452 END LOOP;
453 END IF;
454 l_index := p_instance_hdr_map.NEXT(l_index);
455 END LOOP;
456 END IF; -- generating new item ids
457
458 OPEN item_cursor;
459 LOOP
460 l_config_item_tbl.DELETE;
461 FETCH item_cursor BULK COLLECT INTO l_config_item_tbl LIMIT BATCH_COPY_SIZE;
462 EXIT WHEN item_cursor%NOTFOUND AND l_config_item_tbl.COUNT = 0;
463
464 FOR i IN l_config_item_tbl.FIRST .. l_config_item_tbl.LAST LOOP
465 IF (x_item_id_map.FIRST IS NOT NULL) THEN
466 l_config_item_id := l_config_item_tbl(i).config_item_id;
467 l_parent_config_item_id := l_config_item_tbl(i).parent_config_item_id;
468 l_ato_config_item_id := l_config_item_tbl(i).ato_config_item_id;
469 l_instance_config_item_id := l_config_item_tbl(i).instance_config_item_id;
470 l_target_config_item_id := l_config_item_tbl(i).target_config_item_id;
471
472 IF (x_item_id_map.EXISTS(l_config_item_id)) THEN
473 l_config_item_id := x_item_id_map(l_config_item_id);
474 END IF;
475
476 IF (l_parent_config_item_id IS NOT NULL AND
477 x_item_id_map.EXISTS(l_parent_config_item_id)) THEN
478 l_parent_config_item_id := x_item_id_map(l_parent_config_item_id);
479 END IF;
480
481 IF (l_ato_config_item_id IS NOT NULL AND
482 x_item_id_map.EXISTS(l_ato_config_item_id)) THEN
483 l_ato_config_item_id := x_item_id_map(l_ato_config_item_id);
484 END IF;
485
486 IF (l_instance_config_item_id IS NOT NULL AND
487 x_item_id_map.EXISTS(l_instance_config_item_id)) THEN
488 l_instance_config_item_id := x_item_id_map(l_instance_config_item_id);
489 END IF;
490
491 IF (l_target_config_item_id IS NOT NULL AND
492 x_item_id_map.EXISTS(l_target_config_item_id)) THEN
493 l_target_config_item_id := x_item_id_map(l_target_config_item_id);
494 END IF;
495
496 l_config_item_tbl(i).config_item_id := l_config_item_id;
497 l_config_item_tbl(i).parent_config_item_id := l_parent_config_item_id;
498 l_config_item_tbl(i).ato_config_item_id := l_ato_config_item_id;
499 l_config_item_tbl(i).instance_config_item_id := l_instance_config_item_id;
500 l_config_item_tbl(i).target_config_item_id := l_target_config_item_id;
501 END IF;
502
503 IF (p_instance_hdr_map.EXISTS(l_config_item_tbl(i).instance_hdr_id)) THEN
504 l_instance_hdr_id := p_instance_hdr_map(l_config_item_tbl(i).instance_hdr_id).instance_hdr_id;
505 l_instance_rev_nbr := p_instance_hdr_map(l_config_item_tbl(i).instance_hdr_id).instance_rev_nbr;
506 ELSE
507 l_instance_hdr_id := p_new_config_hdr_id;
508 l_instance_rev_nbr := p_new_config_rev_nbr;
509 END IF;
510 l_config_item_tbl(i).instance_hdr_id := l_instance_hdr_id;
511 l_config_item_tbl(i).instance_rev_nbr := l_instance_rev_nbr;
512
513 -- Target hdr could be out of the session config scope, e.g., a connector in an
514 -- active instance could target a node in a passive instance (bug 2533239 fix)
515 IF l_config_item_tbl(i).target_hdr_id IS NOT NULL THEN
516 l_target_hdr_id := l_config_item_tbl(i).target_hdr_id;
517 l_target_rev_nbr := l_config_item_tbl(i).target_rev_nbr;
518 IF p_instance_hdr_map.EXISTS(l_config_item_tbl(i).target_hdr_id) THEN
519 l_target_rev_nbr := p_instance_hdr_map(l_target_hdr_id).instance_rev_nbr;
520 l_target_hdr_id := p_instance_hdr_map(l_target_hdr_id).instance_hdr_id;
521 ELSIF l_target_hdr_id = p_old_config_hdr_id AND l_target_rev_nbr = p_old_config_rev_nbr THEN
522 l_target_hdr_id := p_new_config_hdr_id;
523 l_target_rev_nbr := p_new_config_rev_nbr;
524 END IF;
525
526 l_config_item_tbl(i).target_hdr_id := l_target_hdr_id;
527 l_config_item_tbl(i).target_rev_nbr := l_target_rev_nbr;
528 END IF;
529
530 l_config_item_tbl(i).config_hdr_id := p_new_config_hdr_id;
531 l_config_item_tbl(i).config_rev_nbr := p_new_config_rev_nbr;
532 END LOOP;
533
534 FORALL i IN 1..l_config_item_tbl.COUNT
535 INSERT INTO cz_config_items VALUES l_config_item_tbl(i);
536
537 END LOOP;
538
539 CLOSE item_cursor;
540 END IF;
541
542 EXCEPTION
543 WHEN OTHERS THEN
544 IF item_cursor%ISOPEN THEN CLOSE item_cursor; END IF;
545 RAISE;
546 END copy_config_item;
547
548 --------------------------------------------------------------------------------
549 PROCEDURE copy_config_input(p_old_config_hdr_id IN NUMBER
550 ,p_old_config_rev_nbr IN NUMBER
551 ,p_new_config_hdr_id IN NUMBER
552 ,p_new_config_rev_nbr IN NUMBER
553 ,p_instance_hdr_map IN instance_tbl_type
554 )
555 IS
556
557 l_config_input_tbl config_input_tbl_type;
558
559 CURSOR input_cursor IS SELECT *
560 FROM cz_config_inputs
561 WHERE config_hdr_id = p_old_config_hdr_id
562 AND config_rev_nbr = p_old_config_rev_nbr
563 AND deleted_flag = '0';
564
565 BEGIN
566
567 IF ( p_instance_hdr_map.COUNT = 0 ) THEN
568
569 INSERT INTO CZ_CONFIG_INPUTS
570 (LAST_UPDATE_LOGIN,
571 CONFIG_HDR_ID,
572 CONFIG_REV_NBR,
573 CONFIG_INPUT_ID,
574 INPUT_SEQ,
575 PARENT_INPUT_ID,
576 PS_NODE_ID,
577 INPUT_VAL,
578 INPUT_NUM_VAL,
579 INSTANCE_NBR,
580 INPUT_TYPE_CODE,
581 DELETED_FLAG,
582 SECURITY_MASK,
583 CHECKOUT_USER,
584 NODE_IDENTIFIER,
585 INSTANCE_ACTION_TYPE,
586 CONFIG_ITEM_ID,
587 TARGET_CONFIG_ITEM_ID,
588 TARGET_CONFIG_INPUT_ID,
589 FLOAT_TYPE,
590 INPUT_SOURCE)
591 SELECT
592 LAST_UPDATE_LOGIN,
593 p_new_config_hdr_id,
594 p_new_config_rev_nbr,
595 CONFIG_INPUT_ID,
596 INPUT_SEQ,
597 PARENT_INPUT_ID,
598 PS_NODE_ID,
599 INPUT_VAL,
600 INPUT_NUM_VAL,
601 INSTANCE_NBR,
602 INPUT_TYPE_CODE,
603 DELETED_FLAG,
604 SECURITY_MASK,
605 CHECKOUT_USER,
606 NODE_IDENTIFIER,
607 INSTANCE_ACTION_TYPE,
608 CONFIG_ITEM_ID,
609 TARGET_CONFIG_ITEM_ID,
610 TARGET_CONFIG_INPUT_ID,
611 FLOAT_TYPE,
612 INPUT_SOURCE
613 FROM CZ_CONFIG_INPUTS
614 WHERE CONFIG_HDR_ID = p_old_config_hdr_id
615 AND CONFIG_REV_NBR = p_old_config_rev_nbr
616 AND deleted_flag = '0';
617
618 ELSE
619
620 --Bug #8198519. There are instance headers, need to resolve containment_instance_hdr_id values
621 --when they are specified.
622 --All validations are done by DIO.
623
624 OPEN input_cursor;
625 LOOP
626
627 l_config_input_tbl.DELETE;
628
629 FETCH input_cursor BULK COLLECT INTO l_config_input_tbl LIMIT BATCH_COPY_SIZE;
630 EXIT WHEN input_cursor%NOTFOUND AND l_config_input_tbl.COUNT = 0;
631
632 FOR i IN l_config_input_tbl.FIRST .. l_config_input_tbl.LAST LOOP
633
634 l_config_input_tbl(i).config_hdr_id := p_new_config_hdr_id;
635 l_config_input_tbl(i).config_rev_nbr := p_new_config_rev_nbr;
636
637 IF ( l_config_input_tbl(i).containment_instance_hdr_id IS NOT NULL ) THEN
638 l_config_input_tbl(i).containment_instance_hdr_id := p_instance_hdr_map(l_config_input_tbl(i).containment_instance_hdr_id).instance_hdr_id;
639 END IF;
640 END LOOP;
641
642 FORALL i IN 1..l_config_input_tbl.COUNT
643 INSERT INTO cz_config_inputs VALUES l_config_input_tbl(i);
644
645 END LOOP;
646
647 CLOSE input_cursor;
648
649 END IF;
650
651 EXCEPTION
652 WHEN OTHERS THEN
653 IF input_cursor%ISOPEN THEN CLOSE input_cursor; END IF;
654 RAISE;
655 END copy_config_input;
656
657 --------------------------------------------------------------------------------
658 PROCEDURE copy_config_input(p_old_config_hdr_id IN NUMBER
659 ,p_old_config_rev_nbr IN NUMBER
660 ,p_new_config_hdr_id IN NUMBER
661 ,p_new_config_rev_nbr IN NUMBER
662 ,p_item_id_map IN number_tbl_type
663 ,p_instance_hdr_map IN instance_tbl_type
664 )
665 IS
666 l_config_input_tbl config_input_tbl_type;
667
668 CURSOR input_cursor IS SELECT *
669 FROM cz_config_inputs
670 WHERE config_hdr_id = p_old_config_hdr_id
671 AND config_rev_nbr = p_old_config_rev_nbr
672 AND deleted_flag = '0';
673
674 BEGIN
675 IF (p_item_id_map.COUNT = 0) THEN
676 copy_config_input(p_old_config_hdr_id
677 ,p_old_config_rev_nbr
678 ,p_new_config_hdr_id
679 ,p_new_config_rev_nbr
680 ,p_instance_hdr_map
681 );
682 ELSE
683 OPEN input_cursor;
684 LOOP
685 l_config_input_tbl.DELETE;
686 FETCH input_cursor BULK COLLECT INTO l_config_input_tbl LIMIT BATCH_COPY_SIZE;
687 EXIT WHEN input_cursor%NOTFOUND AND l_config_input_tbl.COUNT = 0;
688
689 FOR i IN l_config_input_tbl.FIRST .. l_config_input_tbl.LAST LOOP
690 IF p_item_id_map.EXISTS(l_config_input_tbl(i).config_input_id) THEN
691 l_config_input_tbl(i).config_input_id := p_item_id_map(l_config_input_tbl(i).config_input_id);
692 l_config_input_tbl(i).config_item_id := l_config_input_tbl(i).config_input_id;
693 END IF;
694
695 IF l_config_input_tbl(i).parent_input_id IS NOT NULL AND
696 p_item_id_map.EXISTS(l_config_input_tbl(i).parent_input_id) THEN
697 l_config_input_tbl(i).parent_input_id := p_item_id_map(l_config_input_tbl(i).parent_input_id);
698 END IF;
699
700 IF l_config_input_tbl(i).target_config_input_id IS NOT NULL AND
701 p_item_id_map.EXISTS(l_config_input_tbl(i).target_config_input_id) THEN
702 l_config_input_tbl(i).target_config_input_id := p_item_id_map(l_config_input_tbl(i).target_config_input_id);
703 l_config_input_tbl(i).target_config_item_id := l_config_input_tbl(i).target_config_input_id;
704 END IF;
705
706 l_config_input_tbl(i).config_hdr_id := p_new_config_hdr_id;
707 l_config_input_tbl(i).config_rev_nbr := p_new_config_rev_nbr;
708
709 --Bug #8198519. Resolve containment_instance_hdr_id values. All validations are done by DIO.
710
711 IF l_config_input_tbl(i).containment_instance_hdr_id IS NOT NULL THEN
712 l_config_input_tbl(i).containment_instance_hdr_id := p_instance_hdr_map(l_config_input_tbl(i).containment_instance_hdr_id).instance_hdr_id;
713 END IF;
714 END LOOP;
715
716 FORALL i IN 1..l_config_input_tbl.COUNT
717 INSERT INTO cz_config_inputs VALUES l_config_input_tbl(i);
718
719 END LOOP;
720
721 CLOSE input_cursor;
722 END IF;
723
724 EXCEPTION
725 WHEN OTHERS THEN
726 IF input_cursor%ISOPEN THEN CLOSE input_cursor; END IF;
727 RAISE;
728 END copy_config_input;
729
730 --------------------------------------------------------------------------------
731 PROCEDURE copy_config_attributes(p_old_config_hdr_id IN NUMBER,
732 p_old_config_rev_nbr IN NUMBER,
733 p_new_config_hdr_id IN NUMBER,
734 p_new_config_rev_nbr IN NUMBER,
735 p_item_id_map IN number_tbl_type)
736 IS
737 l_config_attr_tbl config_attr_tbl_type;
738
739 CURSOR attr_cursor IS SELECT *
740 FROM cz_config_attributes
741 WHERE config_hdr_id = p_old_config_hdr_id
742 AND config_rev_nbr = p_old_config_rev_nbr
743 AND deleted_flag = '0';
744
745 BEGIN
746 IF p_item_id_map.COUNT = 0 THEN
747 INSERT INTO CZ_CONFIG_ATTRIBUTES
748 (CONFIG_HDR_ID,
749 CONFIG_REV_NBR,
750 CONFIG_ITEM_ID,
751 ATTRIBUTE_CATEGORY,
752 ATTRIBUTE1,
753 ATTRIBUTE2,
754 ATTRIBUTE3,
755 ATTRIBUTE4,
756 ATTRIBUTE5,
757 ATTRIBUTE6,
758 ATTRIBUTE7,
759 ATTRIBUTE8,
760 ATTRIBUTE9,
761 ATTRIBUTE10,
762 ATTRIBUTE11,
763 ATTRIBUTE12,
764 ATTRIBUTE13,
765 ATTRIBUTE14,
766 ATTRIBUTE15,
767 ATTRIBUTE16,
768 ATTRIBUTE17,
769 ATTRIBUTE18,
770 ATTRIBUTE19,
771 ATTRIBUTE20,
772 ATTRIBUTE21,
773 ATTRIBUTE22,
774 ATTRIBUTE23,
775 ATTRIBUTE24,
776 ATTRIBUTE25,
777 ATTRIBUTE26,
778 ATTRIBUTE27,
779 ATTRIBUTE28,
780 ATTRIBUTE29,
781 ATTRIBUTE30,
782 LAST_UPDATE_LOGIN )
783 SELECT
784 p_new_config_hdr_id,
785 p_new_config_rev_nbr,
786 CONFIG_ITEM_ID,
787 ATTRIBUTE_CATEGORY,
788 ATTRIBUTE1,
789 ATTRIBUTE2,
790 ATTRIBUTE3,
791 ATTRIBUTE4,
792 ATTRIBUTE5,
793 ATTRIBUTE6,
794 ATTRIBUTE7,
795 ATTRIBUTE8,
796 ATTRIBUTE9,
797 ATTRIBUTE10,
798 ATTRIBUTE11,
799 ATTRIBUTE12,
800 ATTRIBUTE13,
801 ATTRIBUTE14,
802 ATTRIBUTE15,
803 ATTRIBUTE16,
804 ATTRIBUTE17,
805 ATTRIBUTE18,
806 ATTRIBUTE19,
807 ATTRIBUTE20,
808 ATTRIBUTE21,
809 ATTRIBUTE22,
810 ATTRIBUTE23,
811 ATTRIBUTE24,
812 ATTRIBUTE25,
813 ATTRIBUTE26,
814 ATTRIBUTE27,
815 ATTRIBUTE28,
816 ATTRIBUTE29,
817 ATTRIBUTE30,
818 LAST_UPDATE_LOGIN
819 FROM CZ_CONFIG_ATTRIBUTES
820 WHERE CONFIG_HDR_ID=p_old_config_hdr_id AND CONFIG_REV_NBR=p_old_config_rev_nbr
821 AND deleted_flag = '0';
822
823 ELSE
824 OPEN attr_cursor;
825 LOOP
826 l_config_attr_tbl.DELETE;
827 FETCH attr_cursor BULK COLLECT INTO l_config_attr_tbl LIMIT BATCH_COPY_SIZE;
828 EXIT WHEN attr_cursor%NOTFOUND AND l_config_attr_tbl.COUNT = 0;
829
830 FOR i IN l_config_attr_tbl.FIRST .. l_config_attr_tbl.LAST LOOP
831 IF (p_item_id_map.EXISTS(l_config_attr_tbl(i).config_item_id)) THEN
832 l_config_attr_tbl(i).config_item_id := p_item_id_map(l_config_attr_tbl(i).config_item_id);
833 END IF;
834
835 l_config_attr_tbl(i).config_hdr_id := p_new_config_hdr_id;
836 l_config_attr_tbl(i).config_rev_nbr := p_new_config_rev_nbr;
837 END LOOP;
838
839 FORALL i IN l_config_attr_tbl.FIRST .. l_config_attr_tbl.LAST
840 INSERT INTO CZ_CONFIG_ATTRIBUTES VALUES l_config_attr_tbl(i);
841 END LOOP;
842
843 CLOSE attr_cursor;
844 END IF;
845 EXCEPTION
846 WHEN OTHERS THEN
847 IF attr_cursor%ISOPEN THEN CLOSE attr_cursor; END IF;
848 RAISE;
849 END copy_config_attributes;
850
851 --------------------------------------------------------------------------------
852 PROCEDURE copy_config_ext_attributes(p_old_sess_config_hdr_id IN NUMBER
853 ,p_old_sess_config_rev_nbr IN NUMBER
854 ,p_instance_hdr_map IN instance_tbl_type
855 ,p_item_id_map IN number_tbl_type
856 )
857 IS
858 l_ext_attr_tbl config_extattr_tbl_type;
859
860 CURSOR attr_cursor IS
861 SELECT *
862 FROM cz_config_ext_attributes
863 WHERE deleted_flag = '0' AND (config_hdr_id, config_rev_nbr) IN
864 (SELECT instance_hdr_id, instance_rev_nbr
865 FROM cz_config_items
866 WHERE config_hdr_id = p_old_sess_config_hdr_id
867 AND config_rev_nbr = p_old_sess_config_rev_nbr
868 AND component_instance_type = NETWORK_INSTANCE_ROOT
869 AND deleted_flag = '0');
870
871 BEGIN
872 OPEN attr_cursor;
873 LOOP
874 l_ext_attr_tbl.DELETE;
875 FETCH attr_cursor BULK COLLECT INTO l_ext_attr_tbl LIMIT BATCH_COPY_SIZE;
876 EXIT WHEN attr_cursor%NOTFOUND AND l_ext_attr_tbl.COUNT = 0;
877
878 FOR i IN l_ext_attr_tbl.FIRST .. l_ext_attr_tbl.LAST LOOP
879 IF (p_item_id_map.EXISTS(l_ext_attr_tbl(i).config_item_id)) THEN
880 l_ext_attr_tbl(i).config_item_id := p_item_id_map(l_ext_attr_tbl(i).config_item_id);
881 END IF;
882
883 l_ext_attr_tbl(i).config_rev_nbr := p_instance_hdr_map(l_ext_attr_tbl(i).config_hdr_id).instance_rev_nbr;
884 l_ext_attr_tbl(i).config_hdr_id := p_instance_hdr_map(l_ext_attr_tbl(i).config_hdr_id).instance_hdr_id;
885 END LOOP;
886
887 FORALL i IN l_ext_attr_tbl.FIRST .. l_ext_attr_tbl.LAST
888 INSERT INTO CZ_CONFIG_EXT_ATTRIBUTES VALUES l_ext_attr_tbl(i);
889 END LOOP;
890
891 CLOSE attr_cursor;
892
893 EXCEPTION
894 WHEN OTHERS THEN
895 CLOSE attr_cursor;
896 RAISE;
897
898 END copy_config_ext_attributes;
899
900 --------------------------------------------------------------------------------
901 PROCEDURE copy_config_messages(p_old_config_hdr_id IN NUMBER
902 ,p_old_config_rev_nbr IN NUMBER
903 ,p_new_config_hdr_id IN NUMBER
904 ,p_new_config_rev_nbr IN NUMBER
905 ,p_instance_hdr_map IN instance_tbl_type
906 ,p_item_id_map IN number_tbl_type)
907 IS
908 l_instance_hdr_id NUMBER;
909 l_instance_rev_nbr NUMBER;
910 l_config_item_id NUMBER;
911 l_message_seq NUMBER;
912
913 BEGIN
914 FOR msg_rec IN (SELECT * FROM cz_config_messages
915 WHERE config_hdr_id = p_old_config_hdr_id
916 AND config_rev_nbr = p_old_config_rev_nbr
917 AND deleted_flag = '0'
918 ORDER BY MESSAGE_SEQ)
919 LOOP
920 l_instance_hdr_id := msg_rec.instance_hdr_id;
921 l_instance_rev_nbr := msg_rec.instance_rev_nbr;
922 l_config_item_id := msg_rec.config_item_id;
923
924 IF (l_instance_hdr_id IS NOT NULL) THEN
925 IF (p_instance_hdr_map.EXISTS(l_instance_hdr_id)) THEN
926 l_instance_rev_nbr := p_instance_hdr_map(l_instance_hdr_id).instance_rev_nbr;
927 l_instance_hdr_id := p_instance_hdr_map(l_instance_hdr_id).instance_hdr_id;
928 ELSE
929 l_instance_hdr_id := p_new_config_hdr_id;
930 l_instance_rev_nbr := p_new_config_rev_nbr;
931 END IF;
932 END IF;
933
934 IF (l_config_item_id IS NOT NULL AND p_item_id_map.EXISTS(l_config_item_id)) THEN
935 l_config_item_id := p_item_id_map(l_config_item_id);
936 END IF;
937
938 l_message_seq := get_next_msg_seq;
939
940 INSERT INTO CZ_CONFIG_MESSAGES
941 (MESSAGE_SEQ,
942 CONFIG_HDR_ID,
943 CONFIG_REV_NBR,
944 CONSTRAINT_TYPE,
945 MESSAGE,
946 OVERRIDE,
947 RULE_ID,
948 PS_NODE_ID,
949 LAST_UPDATE_LOGIN,
950 SECURITY_MASK,
951 CHECKOUT_USER,
952 DELETED_FLAG,
953 EFF_FROM,
954 EFF_TO,
955 EFF_MASK
956 ,config_item_id
957 ,instance_hdr_id
958 ,instance_rev_nbr
959 )
960 VALUES
961 (l_message_seq,
962 p_new_config_hdr_id,
963 p_new_config_rev_nbr,
964 msg_rec.CONSTRAINT_TYPE,
965 msg_rec.MESSAGE,
966 msg_rec.OVERRIDE,
967 msg_rec.RULE_ID,
968 msg_rec.PS_NODE_ID,
969 msg_rec.LAST_UPDATE_LOGIN,
970 msg_rec.SECURITY_MASK,
971 msg_rec.CHECKOUT_USER,
972 msg_rec.DELETED_FLAG,
973 msg_rec.EFF_FROM,
974 msg_rec.EFF_TO,
975 msg_rec.EFF_MASK
976 ,l_config_item_id
977 ,l_instance_hdr_id
978 ,l_instance_rev_nbr
979 );
980 END LOOP;
981
982 EXCEPTION
983 WHEN OTHERS THEN
984 RAISE;
985
986 END copy_config_messages;
987
988 --------------------------------------------------------------------------------
989 PROCEDURE copy_configuration(p_api_version IN NUMBER
990 ,p_config_hdr_id IN NUMBER
991 ,p_config_rev_nbr IN NUMBER
992 ,p_copy_mode IN VARCHAR2
993 ,x_config_hdr_id OUT NOCOPY NUMBER
994 ,x_config_rev_nbr OUT NOCOPY NUMBER
995 ,x_orig_item_id_tbl OUT NOCOPY CZ_API_PUB.number_tbl_type
996 ,x_new_item_id_tbl OUT NOCOPY CZ_API_PUB.number_tbl_type
997 ,x_return_status OUT NOCOPY VARCHAR2
998 ,x_msg_count OUT NOCOPY NUMBER
999 ,x_msg_data OUT NOCOPY VARCHAR2
1000 ,p_handle_deleted_flag IN VARCHAR2 := NULL
1001 ,p_new_name IN VARCHAR2 := NULL
1002 )
1003 IS
1004 l_api_version CONSTANT NUMBER := 1.0;
1005 l_api_name CONSTANT VARCHAR2(30) := 'copy_configuration';
1006
1007 l_model_instantiation_type cz_config_hdrs.model_instantiation_type%TYPE;
1008 l_component_instance_type cz_config_hdrs.component_instance_type%TYPE;
1009 l_deleted_flag cz_config_hdrs.DELETED_FLAG%TYPE;
1010
1011 -- lookup map of new instance hdr recs (id, rev), keyed by old instance_hdr_id
1012 l_instance_hdr_map instance_tbl_type;
1013
1014 -- config_item_id lookup map: key, old config_item_id; value, new config_item_id
1015 l_item_id_map number_tbl_type;
1016
1017 l_new_config_hdr_id NUMBER;
1018 l_new_config_rev_nbr NUMBER;
1019
1020 l_index NUMBER;
1021 l_run_id NUMBER;
1022 l_instance_count NUMBER;
1023 l_new_item_count NUMBER;
1024
1025 BEGIN
1026 SAVEPOINT start_transaction;
1027
1028 -- standard call to check for call compatibility
1029 IF (NOT FND_API.compatible_api_call(l_api_version
1030 ,p_api_version
1031 ,l_api_name
1032 ,G_PKG_NAME
1033 )) THEN
1034 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1035 END IF;
1036
1037 IF ((p_copy_mode IS NULL) OR (p_copy_mode <> CZ_API_PUB.G_NEW_HEADER_COPY_MODE AND
1038 p_copy_mode <> CZ_API_PUB.G_NEW_REVISION_COPY_MODE)) THEN
1039 FND_MESSAGE.SET_NAME('CZ', 'CZ_NET_API_INVALID_TREE_MODE');
1040 FND_MESSAGE.SET_TOKEN('MODE', p_copy_mode);
1041 FND_MESSAGE.SET_TOKEN('PROC', l_api_name);
1042 FND_MSG_PUB.ADD;
1043 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1044 p_data => x_msg_data);
1045 RAISE FND_API.G_EXC_ERROR;
1046 END IF;
1047
1048 BEGIN
1049 SELECT model_instantiation_type, component_instance_type, deleted_flag
1050 INTO l_model_instantiation_type, l_component_instance_type, l_deleted_flag
1051 FROM cz_config_hdrs
1052 WHERE config_hdr_id = p_config_hdr_id
1053 AND config_rev_nbr = p_config_rev_nbr;
1054
1055 EXCEPTION
1056 WHEN NO_DATA_FOUND THEN
1057 fnd_message.set_name('CZ', 'CZ_CFG_COPY_NO_CONFIG');
1058 fnd_msg_pub.add;
1059 RAISE FND_API.G_EXC_ERROR;
1060 END;
1061
1062 IF( l_deleted_flag = '1') THEN
1063 IF (p_handle_deleted_flag IS NULL) THEN
1064 fnd_message.set_name('CZ', 'CZ_CFG_COPY_DELETED_CONFIG');
1065 fnd_msg_pub.add;
1066 RAISE FND_API.G_EXC_ERROR;
1067 ELSIF (p_handle_deleted_flag = '0') THEN
1068 UPDATE CZ_CONFIG_HDRS SET DELETED_FLAG = '0'
1069 WHERE CONFIG_HDR_ID = p_config_hdr_id
1070 AND CONFIG_REV_NBR = p_config_rev_nbr;
1071 END IF;
1072 END IF;
1073
1074 -- input config must be session config
1075 -- i.e., component_instance_type must be 'R'
1076 IF (l_component_instance_type <> ROOT) THEN
1077 fnd_message.set_name('CZ', 'CZ_CFG_COPY_HDR_TYPE');
1078 fnd_message.set_token('id', p_config_hdr_id);
1079 fnd_message.set_token('revision', p_config_rev_nbr);
1080 fnd_message.set_token('type', l_component_instance_type);
1081 fnd_msg_pub.add;
1082 RAISE FND_API.G_EXC_ERROR;
1083 END IF;
1084
1085 IF (p_copy_mode = CZ_API_PUB.G_NEW_REVISION_COPY_MODE) THEN
1086 l_new_config_hdr_id := p_config_hdr_id;
1087 l_new_config_rev_nbr := get_next_revision(p_config_hdr_id);
1088 ELSE
1089 l_new_config_hdr_id := get_next_hdr_id;
1090 l_new_config_rev_nbr := 1;
1091 END IF;
1092
1093 copy_config_header(p_config_hdr_id
1094 ,p_config_rev_nbr
1095 ,l_new_config_hdr_id
1096 ,l_new_config_rev_nbr
1097 ,p_new_name
1098 ,p_copy_mode
1099 );
1100
1101 copy_instance_headers(p_config_hdr_id
1102 ,p_config_rev_nbr
1103 ,p_copy_mode
1104 ,l_instance_hdr_map
1105 );
1106
1107 copy_config_item(p_config_hdr_id
1108 ,p_config_rev_nbr
1109 ,l_new_config_hdr_id
1110 ,l_new_config_rev_nbr
1111 ,p_copy_mode
1112 ,(l_model_instantiation_type = NETWORK)
1113 ,l_instance_hdr_map
1114 ,l_item_id_map
1115 );
1116
1117 copy_config_input(p_config_hdr_id
1118 ,p_config_rev_nbr
1119 ,l_new_config_hdr_id
1120 ,l_new_config_rev_nbr
1121 ,l_item_id_map
1122 ,l_instance_hdr_map
1123 );
1124
1125
1126 copy_config_attributes(p_config_hdr_id
1127 ,p_config_rev_nbr
1128 ,l_new_config_hdr_id
1129 ,l_new_config_rev_nbr
1130 ,l_item_id_map
1131 );
1132
1133 l_instance_count := l_instance_hdr_map.COUNT;
1134 IF (l_model_instantiation_type = NETWORK AND l_instance_count > 0) THEN
1135 copy_config_ext_attributes(p_config_hdr_id
1136 ,p_config_rev_nbr
1137 ,l_instance_hdr_map
1138 ,l_item_id_map
1139 );
1140 END IF;
1141
1142 copy_config_messages(p_config_hdr_id
1143 ,p_config_rev_nbr
1144 ,l_new_config_hdr_id
1145 ,l_new_config_rev_nbr
1146 ,l_instance_hdr_map
1147 ,l_item_id_map
1148 );
1149
1150 -- copy ib data if necessary
1151 IF (l_model_instantiation_type = NETWORK AND l_instance_count > 0) THEN
1152 cz_ib_transactions.clone_ib_data(l_new_config_hdr_id
1153 ,l_new_config_rev_nbr
1154 ,l_run_id
1155 );
1156 IF (l_run_id <> 0) THEN
1157 fnd_message.set_name('CZ', 'CZ_CFG_COPY_IB');
1158 fnd_message.set_token('id', l_new_config_hdr_id);
1159 fnd_message.set_token('revision', l_new_config_rev_nbr);
1160 fnd_msg_pub.add;
1161 RAISE FND_API.G_EXC_ERROR;
1162 END IF;
1163 END IF;
1164
1165 x_config_hdr_id := l_new_config_hdr_id;
1166 x_config_rev_nbr := l_new_config_rev_nbr;
1167
1168 x_orig_item_id_tbl := CZ_API_PUB.NUMBER_TBL_TYPE();
1169 x_new_item_id_tbl := CZ_API_PUB.NUMBER_TBL_TYPE();
1170 l_new_item_count := l_item_id_map.COUNT;
1171 IF (l_new_item_count > 0) THEN
1172 x_orig_item_id_tbl.EXTEND(l_new_item_count);
1173 x_new_item_id_tbl.EXTEND(l_new_item_count);
1174 l_index := l_item_id_map.FIRST;
1175 WHILE (l_index IS NOT NULL) LOOP
1176 x_orig_item_id_tbl(l_new_item_count) := l_index;
1177 x_new_item_id_tbl(l_new_item_count) := l_item_id_map(l_index);
1178 l_new_item_count := l_new_item_count - 1;
1179 l_index := l_item_id_map.NEXT(l_index);
1180 END LOOP;
1181 END IF;
1182
1183 x_return_status := FND_API.G_RET_STS_SUCCESS;
1184 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1185 p_data => x_msg_data);
1186
1187 EXCEPTION
1188 WHEN FND_API.G_EXC_ERROR THEN
1189 x_return_status := FND_API.G_RET_STS_ERROR;
1190 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1191 p_data => x_msg_data);
1192 ROLLBACK TO start_transaction;
1193
1194 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1196 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1197 p_data => x_msg_data);
1198 ROLLBACK TO start_transaction;
1199
1200 WHEN OTHERS THEN
1201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1202 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1203 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1204 END IF;
1205
1206 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1207 p_data => x_msg_data);
1208 ROLLBACK TO start_transaction;
1209 END copy_configuration;
1210
1211 --------------------------------------------------------------------------------
1212 PROCEDURE copy_configuration_auto
1213 (p_api_version IN NUMBER
1214 ,p_config_hdr_id IN NUMBER
1215 ,p_config_rev_nbr IN NUMBER
1216 ,p_copy_mode IN VARCHAR2
1217 ,x_config_hdr_id OUT NOCOPY NUMBER
1218 ,x_config_rev_nbr OUT NOCOPY NUMBER
1219 ,x_orig_item_id_tbl OUT NOCOPY CZ_API_PUB.number_tbl_type
1220 ,x_new_item_id_tbl OUT NOCOPY CZ_API_PUB.number_tbl_type
1221 ,x_return_status OUT NOCOPY VARCHAR2
1222 ,x_msg_count OUT NOCOPY NUMBER
1223 ,x_msg_data OUT NOCOPY VARCHAR2
1224 ,p_handle_deleted_flag IN VARCHAR2 := NULL
1225 ,p_new_name IN VARCHAR2 := NULL
1226 )
1227 IS
1228 PRAGMA AUTONOMOUS_TRANSACTION;
1229 l_api_version CONSTANT NUMBER := 1.0;
1230 l_api_name CONSTANT VARCHAR2(30) := 'copy_configuration_auto';
1231
1232 BEGIN
1233 copy_configuration(p_api_version
1234 ,p_config_hdr_id
1235 ,p_config_rev_nbr
1236 ,p_copy_mode
1237 ,x_config_hdr_id
1238 ,x_config_rev_nbr
1239 ,x_orig_item_id_tbl
1240 ,x_new_item_id_tbl
1241 ,x_return_status
1242 ,x_msg_count
1243 ,x_msg_data
1244 ,p_handle_deleted_flag
1245 ,p_new_name
1246 );
1247 COMMIT;
1248
1249 EXCEPTION
1250 WHEN OTHERS THEN
1251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1252 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1253 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1254 END IF;
1255 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1256 p_data => x_msg_data);
1257 ROLLBACK;
1258 END copy_configuration_auto;
1259
1260 --------------------------------------------------------------------------------
1261 PROCEDURE verify_configuration(p_api_version IN NUMBER
1262 ,p_config_hdr_id IN NUMBER
1263 ,p_config_rev_nbr IN NUMBER
1264 ,x_exists_flag OUT NOCOPY VARCHAR2
1265 ,x_valid_flag OUT NOCOPY VARCHAR2
1266 ,x_complete_flag OUT NOCOPY VARCHAR2
1267 ,x_return_status OUT NOCOPY VARCHAR2
1268 ,x_msg_count OUT NOCOPY NUMBER
1269 ,x_msg_data OUT NOCOPY VARCHAR2
1270 )
1271 IS
1272 l_api_version CONSTANT NUMBER := 1.0;
1273 l_api_name CONSTANT VARCHAR2(30) := 'verify_configuration';
1274
1275 l_component_instance_type cz_config_hdrs.component_instance_type%TYPE;
1276 l_config_status cz_config_hdrs.config_status%TYPE;
1277 l_dummy INTEGER;
1278
1279 BEGIN
1280 IF (NOT FND_API.compatible_api_call(l_api_version
1281 ,p_api_version
1282 ,l_api_name
1283 ,G_PKG_NAME
1284 )) THEN
1285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1286 END IF;
1287
1288 BEGIN
1289 SELECT component_instance_type, config_status
1290 INTO l_component_instance_type, l_config_status
1291 FROM cz_config_hdrs
1292 WHERE config_hdr_id = p_config_hdr_id
1293 AND config_rev_nbr = p_config_rev_nbr
1294 AND deleted_flag = '0';
1295
1296 -- input config must be a session config
1297 IF (l_component_instance_type <> ROOT) THEN
1298 fnd_message.set_name('CZ', 'CZ_CFG_VERIFY_HDR_TYPE');
1299 fnd_message.set_token('id', p_config_hdr_id);
1300 fnd_message.set_token('revision', p_config_rev_nbr);
1301 fnd_message.set_token('type', l_component_instance_type);
1302 fnd_msg_pub.add;
1303 RAISE FND_API.G_EXC_ERROR;
1304 END IF;
1305
1306 x_exists_flag := FND_API.G_TRUE;
1307 x_complete_flag := FND_API.G_TRUE;
1308 IF (l_config_status <> CONFIG_STATUS_COMPLETE) THEN
1309 x_complete_flag := FND_API.G_FALSE;
1310 END IF;
1311
1312 BEGIN
1313 SELECT 1 INTO l_dummy
1314 FROM cz_config_messages
1315 WHERE config_hdr_id = p_config_hdr_id
1316 AND config_rev_nbr = p_config_rev_nbr
1317 AND deleted_flag = '0'
1318 AND ROWNUM < 2;
1319
1320 x_valid_flag := FND_API.G_FALSE;
1321
1322 EXCEPTION
1323 WHEN NO_DATA_FOUND THEN
1324 x_valid_flag := FND_API.G_TRUE;
1325 END;
1326
1327 EXCEPTION
1328 WHEN NO_DATA_FOUND THEN
1329 x_exists_flag := FND_API.G_FALSE;
1330
1331 END;
1332
1333 x_return_status := FND_API.G_RET_STS_SUCCESS;
1334
1335 EXCEPTION
1336 WHEN FND_API.G_EXC_ERROR THEN
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338
1339 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1340 p_data => x_msg_data);
1341
1342 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1344 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1345 p_data => x_msg_data);
1346
1347 WHEN OTHERS THEN
1348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1349 IF fnd_msg_pub.check_msg_Level(fnd_msg_pub.G_MSG_LVL_UNEXP_ERROR) THEN
1350 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name);
1351 END IF;
1352
1353 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1354 p_data => x_msg_data);
1355 END verify_configuration;
1356
1357 --------------------------------------------------------------------------------
1358 BEGIN
1359 SELECT NVL(cz_utils.conv_num(value),DEFAULT_INCR) INTO id_increment
1360 FROM CZ_DB_SETTINGS
1361 WHERE section_name='SCHEMA' AND setting_id='OracleSequenceIncr';
1362
1363 END CZ_CONFIG_API_PUB;