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