[Home] [Help]
PACKAGE BODY: APPS.CSI_CZ_INT
Source
1 PACKAGE BODY csi_cz_int AS
2 /* $Header: csigczib.pls 120.5 2006/02/08 13:46:05 srramakr noship $ */
3
4 PROCEDURE debug(
5 p_message IN varchar2)
6 IS
7 BEGIN
8 csi_t_gen_utility_pvt.add(p_message);
9 END debug;
10
11 procedure api_log(
12 p_api_name IN varchar2)
13 IS
14 BEGIN
15 csi_t_gen_utility_pvt.dump_api_info(
16 p_pkg_name => 'csi_cz_int',
17 p_api_name => p_api_name);
18 END api_log;
19
20 PROCEDURE get_configuration_revision(
21 p_config_header_id IN number,
22 p_target_commitment_date IN date,
23 px_instance_level IN OUT NOCOPY varchar2,
24 x_install_config_rec OUT NOCOPY config_rec , -- Bug 4147624, item instance locking. The config keys in the rec
25 x_return_status OUT NOCOPY varchar2, -- would actually correspond to values of the Installed Root
26 x_return_message OUT NOCOPY varchar2)
27 IS
28
29 l_rev_found boolean := FALSE;
30 l_instance_level varchar2(30);
31
32 /* Commented this cursor and changed as below for bug 3502896
33 as suggested by CZ */
34
35 -- CURSOR installed_cur(p_inst_hdr_id in number) IS
36 -- SELECT cii.config_inst_rev_num
37 -- FROM csi_item_instances cii
38 -- WHERE cii.config_inst_hdr_id = p_inst_hdr_id
39 -- AND sysdate BETWEEN nvl(cii.active_start_date, sysdate-1)
40 -- AND nvl(cii.active_end_date, sysdate+1);
41 /* Changes for bug 3901123 . Commented this cursor to replace with a single select - Performance
42 CURSOR installed_cur(p_inst_hdr_id in number) IS
43 SELECT cii.config_inst_rev_num
44 FROM csi_item_instances cii,
45 cz_config_items czItems
46 WHERE cii.config_inst_hdr_id = p_inst_hdr_id
47 AND czItems.instance_hdr_id = p_inst_hdr_id
48 AND czItems.component_instance_type in ('I','R') -- I = Root instance
49 AND czItems.config_item_id = cii.config_inst_item_id
50 AND sysdate BETWEEN nvl(cii.active_start_date, sysdate-1)
51 AND nvl(cii.active_end_date, sysdate+1);
52 */
53
54 CURSOR proposed_cur(p_inst_hdr_id in number) IS
55 SELECT ctd.config_inst_rev_num
56 FROM csi_t_transaction_lines ctl,
57 csi_t_txn_line_details ctd
58 WHERE ctd.config_inst_hdr_id = p_inst_hdr_id
59 AND ctl.transaction_line_id = ctd.transaction_line_id
60 AND ctl.source_transaction_status = 'PROPOSED'
61 AND not exists (SELECT 'X' FROM csi_t_txn_line_details ctlx
62 WHERE ctlx.config_inst_hdr_id = ctd.config_inst_hdr_id
63 AND ctlx.config_inst_baseline_rev_num = ctd.config_inst_rev_num);
64
65 BEGIN
66
67 x_return_status := fnd_api.g_ret_sts_success;
68 --Initializing the lock status
69 x_install_config_rec.lock_status := 0;
70
71
72 l_instance_level := nvl(px_instance_level, 'INSTALLED');
73
74 IF l_instance_level = 'INSTALLED' THEN
75 -- Added for 3901123
76
77 Begin
78
79 SELECT cii.config_inst_hdr_id, -- changes made for MACD locking bug, 4147624
80 cii.config_inst_rev_num,
81 cii.config_inst_item_id
82 INTO x_install_config_rec.config_inst_hdr_id,
83 x_install_config_rec.config_inst_rev_num,
84 x_install_config_rec.config_inst_item_id
85 FROM csi_item_instances cii
86 WHERE cii.config_inst_hdr_id = p_config_header_id
87 AND sysdate BETWEEN nvl(cii.active_start_date, sysdate-1)
88 AND nvl(cii.active_end_date, sysdate+1)
89 AND EXISTS (SELECT 'Y' -- bug 3901123
90 FROM cz_config_items czItems
91 WHERE czItems.instance_hdr_id = p_config_header_id
92 AND czItems.instance_rev_nbr = cii.config_inst_rev_num
93 AND czItems.config_item_id = cii.config_inst_item_id
94 AND czItems.component_instance_type = 'I' -- I = Root instance
95 AND czItems.deleted_flag = '0');
96 l_rev_found := TRUE;
97 Exception when others then
98 l_rev_found := FALSE;
99 End;
100
101 IF (l_rev_found)
102 THEN
103 BEGIN
104 SELECT lock_source_appln_id, -- pass the locking details except the locked CZ keys
105 lock_source_header_ref,
106 lock_source_line_ref1,
107 lock_source_line_ref2,
108 lock_source_line_ref3,
109 lock_id,
110 lock_status
111 INTO x_install_config_rec.source_application_id,
112 x_install_config_rec.source_txn_header_ref,
113 x_install_config_rec.source_txn_line_ref1,
114 x_install_config_rec.source_txn_line_ref2,
115 x_install_config_rec.source_txn_line_ref3,
116 x_install_config_rec.lock_id,
117 x_install_config_rec.lock_status
118 FROM CSI_ITEM_INSTANCE_LOCKS
119 WHERE CONFIG_INST_HDR_ID = p_config_header_id
120 AND CONFIG_INST_ITEM_ID = x_install_config_rec.config_inst_item_id
121 AND LOCK_STATUS <> 0;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 NULL;
126 END;
127 END IF;
128
129 /* commented the loop for 3901123
130 FOR installed_rec IN installed_cur (p_config_header_id)
131 LOOP
132 l_rev_found := TRUE;
133 x_install_config_rec.config_inst_rev_num := installed_rec.config_inst_rev_num;
134 END LOOP;
135 */
136
137 /*
138
139 -- commenting as proposed and PENDING are not supported in the first release
140
141 ELSIF l_instance_level = 'PROPOSED' THEN
142
143 FOR proposed_rec IN proposed_cur(p_config_header_id)
144 LOOP
145 l_rev_found := TRUE;
146 x_install_config_rec.config_inst_rev_num := proposed_rec.config_inst_rev_num;
147 END LOOP;
148
149 IF NOT(l_rev_found) THEN
150 FOR installed_rec IN installed_cur (p_config_header_id)
151 LOOP
152 l_rev_found := TRUE;
153 x_install_config_rec.config_inst_rev_num := installed_rec.config_inst_rev_num;
154 px_instance_level := 'INSTALLED';
155 END LOOP;
156 END IF;
157 */
158
159 ELSE
160
161 fnd_message.set_name('CSI', 'CSI_UNSUPPORTED_INST_LEVEL');
162 fnd_message.set_token('INST_LVL', px_instance_level);
163 fnd_msg_pub.add;
164 RAISE fnd_api.g_exc_error;
165
166 END IF;
167
168 IF NOT(l_rev_found) THEN
169 x_install_config_rec.config_inst_rev_num := null;
170 /*
171 fnd_message.set_name('CSI','CSI_CONFIG_REV_NOT_FOUND');
172 fnd_message.set_token('LEVEL', l_instance_level);
173 fnd_message.set_token('INST_HDR_ID', p_config_header_id);
174 fnd_msg_pub.add;
175 RAISE fnd_api.g_exc_error;
176 */
177 END IF;
178
179 EXCEPTION
180 WHEN fnd_api.g_exc_error THEN
181 x_return_status := fnd_api.g_ret_sts_error;
182 x_return_message := csi_t_gen_utility_pvt.dump_error_stack;
183 END get_configuration_revision;
184
185 --
186 --
187 --
188 PROCEDURE get_connected_configurations(
189 p_config_query_table IN config_query_table,
190 p_instance_level IN varchar2,
191 x_config_pair_table OUT NOCOPY config_pair_table,
192 x_return_status OUT NOCOPY varchar2,
193 x_return_message OUT NOCOPY varchar2)
194 IS
195
196 l_o_ind binary_integer := 0;
197 l_instance_id number;
198
199 CURSOR pending_cur(p_inst_hdr_id in number, p_inst_rev_num in number) IS
200 SELECT cti.sub_config_inst_hdr_id,
201 cti.sub_config_inst_rev_num,
202 cti.sub_config_inst_item_id,
203 cti.obj_config_inst_hdr_id,
204 cti.obj_config_inst_rev_num,
205 cti.obj_config_inst_item_id
206 FROM csi_t_ii_relationships cti
207 WHERE cti.relationship_type_code = 'CONNECTED-TO'
208 AND ((
209 cti.sub_config_inst_hdr_id = p_inst_hdr_id
210 AND
211 cti.sub_config_inst_rev_num = p_inst_rev_num
212 )
213 OR
214 (
215 cti.obj_config_inst_hdr_id = p_inst_hdr_id
216 AND
217 cti.obj_config_inst_rev_num = p_inst_rev_num
218 )
219 );
220 /* replaced the cursor for 3892929
221 CURSOR installed_cur(p_inst_hdr_id in number, p_inst_rev_num in number) IS
222 SELECT subject_id,
223 object_id
224 FROM csi_ii_relationships cir,
225 csi_item_instances cii
226 WHERE cii.config_inst_hdr_id = p_inst_hdr_id
227 AND cii.config_inst_rev_num = p_inst_rev_num
228 AND cir.relationship_type_code = 'CONNECTED-TO'
229 AND ( cir.subject_id = cii.instance_id
230 OR
231 cir.object_id = cii.instance_id)
232 AND sysdate BETWEEN nvl(cir.active_start_date, sysdate-1)
233 AND nvl(cir.active_end_date, sysdate+1);
234 */
235
236 CURSOR installed_cur(p_inst_hdr_id in number, p_inst_rev_num in number) IS
237 SELECT subject_id ,
238 object_id ,
239 instance_id ,
240 decode (subject_id, instance_id, config_inst_hdr_id, -9999) sub_inst_hdr_id,
241 decode (object_id, instance_id, config_inst_hdr_id, -9999) obj_inst_hdr_id,
242 config_inst_item_id,
243 config_inst_rev_num
244 FROM csi_ii_relationships cir,
245 csi_item_instances cii
246 WHERE cii.config_inst_hdr_id = p_inst_hdr_id
247 AND cii.config_inst_rev_num = p_inst_rev_num
248 AND cir.relationship_type_code = 'CONNECTED-TO'
249 AND ( cir.subject_id = cii.instance_id
250 OR
251 cir.object_id = cii.instance_id)
252 AND sysdate BETWEEN nvl(cir.active_start_date, sysdate-1)
253 AND nvl(cir.active_end_date, sysdate+1);
254
255 l_root_hdr_id number;
256 l_root_rev_num number;
257 l_root_item_id number;
258 l_conn_hdr_id number;
259 l_found BOOLEAN;
260
261 l_sub_hdr_id number;
262 l_sub_rev_num number;
263 l_sub_item_id number;
264
265 l_obj_hdr_id number;
266 l_obj_rev_num number;
267 l_obj_item_id number;
268
269 BEGIN
270
271 x_return_status := fnd_api.g_ret_sts_success;
272
273 IF p_config_query_table.COUNT > 0 THEN
274
275 FOR l_ind IN p_config_query_table.FIRST .. p_config_query_table.LAST
276 LOOP
277 IF p_instance_level = 'INSTALLED' THEN
278
279 FOR installed_rec IN installed_cur (
280 p_config_query_table(l_ind).config_header_id,
281 p_config_query_table(l_ind).config_revision_number)
282 LOOP
283
284 l_sub_hdr_id := null;
285 l_sub_rev_num := null;
286 l_sub_item_id := null;
287 l_obj_hdr_id := null;
288 l_obj_rev_num := null;
289 l_obj_item_id := null;
290 l_root_hdr_id := null;
291 l_root_rev_num:= null;
292 l_root_item_id:= null;
293 l_conn_hdr_id := null;
294
295 DECLARE
296 do_not_build exception;
297 BEGIN
298
299 /* commented and replaced below for bug 3892929
300 BEGIN
301 SELECT config_inst_hdr_id ,
302 config_inst_rev_num,
303 config_inst_item_id
304 INTO l_sub_hdr_id,
305 l_sub_rev_num,
306 l_sub_item_id
307 FROM csi_item_instances
308 WHERE instance_id = installed_rec.subject_id
309 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
310 AND nvl(active_end_date, sysdate+1);
311 EXCEPTION
312 WHEN no_data_found THEN
313 RAISE do_not_build;
314 END;
315
316 BEGIN
317 SELECT config_inst_hdr_id ,
318 config_inst_rev_num,
319 config_inst_item_id
320 INTO l_obj_hdr_id,
321 l_obj_rev_num,
322 l_obj_item_id
323 FROM csi_item_instances
324 WHERE instance_id = installed_rec.object_id
325 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
326 AND nvl(active_end_date, sysdate+1);
327 EXCEPTION
328 WHEN no_data_found THEN
329 RAISE do_not_build;
330 END;
331
332 l_o_ind := l_o_ind + 1;
333
334 x_config_pair_table(l_o_ind).object_header_id := l_obj_hdr_id;
335 x_config_pair_table(l_o_ind).object_revision_number := l_obj_rev_num;
336 x_config_pair_table(l_o_ind).object_item_id := l_obj_item_id;
337
338 x_config_pair_table(l_o_ind).subject_header_id := l_sub_hdr_id;
339 x_config_pair_table(l_o_ind).subject_revision_number := l_sub_rev_num;
340 x_config_pair_table(l_o_ind).subject_item_id := l_sub_item_id;
341
342 EXCEPTION
343 WHEN do_not_build THEN
344 null;
345 END;
346 END LOOP;
347 bug 3892929 */
348
349 IF installed_rec.subject_id is not null
350 OR
351 installed_rec.object_id is not null THEN
352 IF nvl(installed_rec.sub_inst_hdr_id, -9999) = -9999 THEN
353 l_obj_hdr_id := installed_rec.obj_inst_hdr_id;
354 l_obj_item_id := installed_rec.config_inst_item_id;
355 l_obj_rev_num := installed_rec.config_inst_rev_num;
356 -- get the cz 3tuple
357 BEGIN
358 SELECT config_inst_hdr_id ,
359 config_inst_rev_num,
360 config_inst_item_id
361 INTO l_sub_hdr_id,
362 l_sub_rev_num,
363 l_sub_item_id
364 FROM csi_item_instances
365 WHERE instance_id = installed_rec.subject_id
366 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
367 AND nvl(active_end_date, sysdate+1);
371 RAISE do_not_build;
368 l_conn_hdr_id := l_sub_hdr_id; -- the connected instance hdr ID
369 EXCEPTION
370 WHEN no_data_found THEN
372 END;
373 ELSE
374 l_sub_hdr_id := installed_rec.sub_inst_hdr_id;
375 l_sub_item_id := installed_rec.config_inst_item_id;
376 l_sub_rev_num := installed_rec.config_inst_rev_num;
377 BEGIN
378 SELECT config_inst_hdr_id ,
379 config_inst_rev_num,
380 config_inst_item_id
381 INTO l_obj_hdr_id,
382 l_obj_rev_num,
383 l_obj_item_id
384 FROM csi_item_instances
385 WHERE instance_id = installed_rec.object_id
386 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
387 AND nvl(active_end_date, sysdate+1);
388 l_conn_hdr_id := l_obj_hdr_id; -- the connected instance hdr ID
389 EXCEPTION
390 WHEN no_data_found THEN
391 RAISE do_not_build;
392 END;
393 END IF;
394 -- now get the root of the connected instance
395
396 Begin
397
398 SELECT cii.config_inst_hdr_id ,
399 cii.config_inst_rev_num,
400 cii.config_inst_item_id
401 INTO l_root_hdr_id,
402 l_root_rev_num,
403 l_root_item_id
404 FROM csi_item_instances cii
405 WHERE cii.config_inst_hdr_id = l_conn_hdr_id
406 AND sysdate BETWEEN nvl(cii.active_start_date, sysdate-1)
407 AND nvl(cii.active_end_date, sysdate+1)
408 AND EXISTS (SELECT 'Y'
409 FROM cz_config_items czItems
410 WHERE czItems.instance_hdr_id = l_conn_hdr_id
411 AND czItems.instance_rev_nbr = cii.config_inst_rev_num
412 AND czItems.config_item_id = cii.config_inst_item_id
413 AND czItems.component_instance_type = 'I' -- I = Root instance
414 AND czItems.deleted_flag = '0');
415 Exception when others then
416 fnd_message.set_name('CSI','CSI_CONFIG_REV_NOT_FOUND');
417 fnd_message.set_token('LEVEL', 'INSTALLED');
418 fnd_message.set_token('INST_HDR_ID', l_conn_hdr_id);
419 fnd_msg_pub.add;
420 RAISE fnd_api.g_exc_error;
421 End;
422
423 l_found := FALSE;
424 IF x_config_pair_table.count > 0 THEN
425 FOR x_ind in x_config_pair_table.First .. x_config_pair_table.LAST LOOP
426 IF ( l_sub_hdr_id is not null OR l_obj_hdr_id is not null) THEN
427 IF ( (x_config_pair_table(x_ind).root_header_id = l_obj_hdr_id)
428 OR (x_config_pair_table(x_ind).root_header_id = l_sub_hdr_id) )
429 --only if a particular tree/root has not already been loaded/identified, build it
430 THEN
431 l_found := TRUE;
432 END IF;
433 END IF;
434 END LOOP;
435 END IF;
436
437 IF NOT l_found THEN
438 l_o_ind := l_o_ind + 1;
439 --Initializing the lock_status
440 x_config_pair_table(l_o_ind).lock_status := 0;
441
442 x_config_pair_table(l_o_ind).root_header_id := l_root_hdr_id;
443 x_config_pair_table(l_o_ind).root_revision_number := l_root_rev_num;
444 x_config_pair_table(l_o_ind).root_item_id := l_root_item_id;
445 x_config_pair_table(l_o_ind).object_header_id := l_obj_hdr_id;
446 x_config_pair_table(l_o_ind).object_revision_number := l_obj_rev_num;
447 x_config_pair_table(l_o_ind).object_item_id := l_obj_item_id;
448 x_config_pair_table(l_o_ind).subject_header_id := l_sub_hdr_id;
449 x_config_pair_table(l_o_ind).subject_revision_number := l_sub_rev_num;
450 x_config_pair_table(l_o_ind).subject_item_id := l_sub_item_id;
451
452 BEGIN
453 SELECT lock_source_appln_id,
454 lock_source_header_ref,
455 lock_source_line_ref1,
456 lock_source_line_ref2,
457 lock_source_line_ref3,
458 lock_id,
459 lock_status
460 INTO x_config_pair_table(l_o_ind).source_application_id,
461 x_config_pair_table(l_o_ind).source_txn_header_ref,
462 x_config_pair_table(l_o_ind).source_txn_line_ref1,
463 x_config_pair_table(l_o_ind).source_txn_line_ref2,
464 x_config_pair_table(l_o_ind).source_txn_line_ref3,
465 x_config_pair_table(l_o_ind).lock_id,
466 x_config_pair_table(l_o_ind).lock_status
470 AND LOCK_STATUS <> 0;
467 FROM CSI_ITEM_INSTANCE_LOCKS
468 WHERE config_inst_hdr_id = l_root_hdr_id
469 AND config_inst_item_id = l_root_item_id
471
472 EXCEPTION
473 WHEN OTHERS THEN
474 NULL;
475 END;
476
477 ELSE
478 RAISE do_not_build;
479 END IF;
480 END IF;
481 EXCEPTION
482 WHEN do_not_build THEN
483 null;
484 END;
485 END LOOP;
486
487 ELSIF p_instance_level = 'PENDING' THEN
488
489 FOR pending_rec IN pending_cur(p_config_query_table(l_ind).config_header_id,
490 p_config_query_table(l_ind).config_revision_number)
491 LOOP
492 l_o_ind := l_o_ind + 1;
493
494 x_config_pair_table(l_o_ind).subject_header_id
495 := pending_rec.sub_config_inst_hdr_id;
496 x_config_pair_table(l_o_ind).subject_revision_number
497 := pending_rec.sub_config_inst_rev_num;
498 x_config_pair_table(l_o_ind).subject_item_id
499 := pending_rec.sub_config_inst_item_id;
500 x_config_pair_table(l_o_ind).object_header_id
501 := pending_rec.obj_config_inst_hdr_id;
502 x_config_pair_table(l_o_ind).object_revision_number
503 := pending_rec.obj_config_inst_rev_num;
504 x_config_pair_table(l_o_ind).object_item_id
505 := pending_rec.obj_config_inst_item_id;
506
507 END LOOP;
508 END IF;
509 END LOOP;
510
511 END IF;
512
513 EXCEPTION
514 WHEN fnd_api.g_exc_error THEN
515 x_return_status := fnd_api.g_ret_sts_error;
516 END get_connected_configurations;
517
518 Function check_item_instance_lock (
519 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
520 p_config_rec IN config_rec,
521 x_return_status OUT NOCOPY VARCHAR2,
522 x_msg_count OUT NOCOPY NUMBER,
523 x_msg_data OUT NOCOPY VARCHAR2)
524 RETURN BOOLEAN is
525
526 l_locked BOOLEAN := FALSE;
527 Begin
528 x_return_status := fnd_api.g_ret_sts_success;
529
530 l_locked := csi_item_instance_pvt.check_item_instance_lock(
531 p_config_inst_hdr_id => p_config_rec.config_inst_hdr_id,
532 p_config_inst_item_id => p_config_rec.config_inst_item_id,
533 p_config_inst_rev_num => p_config_rec.config_inst_rev_num,
534 p_instance_id => p_config_rec.instance_id);
535 Return l_locked;
536
537 EXCEPTION
538 WHEN fnd_api.g_exc_error THEN
539 x_return_status := fnd_api.g_ret_sts_error;
540 Return TRUE;
541 END check_item_instance_lock;
542
543 PROCEDURE lock_item_instances(
544 p_api_version IN NUMBER,
545 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
546 p_commit IN VARCHAR2 := FND_API.g_false,
547 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
548 px_config_tbl IN OUT NOCOPY config_tbl,
549 x_return_status OUT NOCOPY varchar2,
550 x_msg_count OUT NOCOPY NUMBER,
551 x_msg_data OUT NOCOPY VARCHAR2 )
552 IS
553
554 l_lock BOOLEAN := FALSE;
555 l_config_rec config_rec;
556 l_config_tbl config_tbl;
557 l_parent_ind NUMBER;
558 l_child_ind NUMBER;
559 l_CONFIG_SESSION_HDR_ID NUMBER;
560 l_CONFIG_SESSION_REV_NUM NUMBER;
561 l_CONFIG_SESSION_ITEM_ID NUMBER;
562 l_txn_rec csi_datastructures_pub.transaction_rec;
563 l_flag NUMBER;
564 l_csi_debug_level NUMBER;
565 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
566 l_msg_count NUMBER;
567 l_msg_data VARCHAR2(2000);
568 l_return_message VARCHAR2(2000);
569
570 -- Cursor to populate all the child keys for the root keys passed from CZ.
571 CURSOR sess_cur(
572 p_config_inst_hdr_id IN NUMBER,
573 p_config_inst_rev_num IN NUMBER,
574 p_config_inst_item_id in NUMBER)
575 IS
576 SELECT ctl.CONFIG_SESSION_HDR_ID,
577 ctl.CONFIG_SESSION_REV_NUM,
578 ctl.CONFIG_SESSION_ITEM_ID,
579 ctld.CONFIG_INST_HDR_ID,
580 ctld.CONFIG_INST_REV_NUM,
581 ctld.CONFIG_INST_ITEM_ID,
582 ctld.instance_id
583 FROM csi_t_transaction_lines ctl,
584 csi_t_txn_line_details ctld
585 WHERE ctl.transaction_line_id = ctld.transaction_line_id
586 AND CONFIG_INST_HDR_ID = p_config_inst_hdr_id
587 AND CONFIG_INST_REV_NUM = p_config_inst_rev_num;
588 -- AND CONFIG_INST_ITEM_ID <> p_config_inst_item_id;
589
590 Begin
591
592 savepoint csi_cz_lock_item;
593
594 -- This routine checks if ib is active
595 csi_utility_grp.check_ib_active;
596
597 -- Initialize API return status to success
598 x_return_status := fnd_api.g_ret_sts_success;
599
600 csi_t_gen_utility_pvt.build_file_name(
601 p_file_segment1 => 'csilock',
602 p_file_segment2 => to_char(sysdate,'DDMONYYYY'));
603
604 api_log('lock_item_instance');
605
606 -- Check the profile option CSI_DEBUG_LEVEL for debug message reporting
607 l_csi_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
608
609 -- Building txn rec
610 -- l_txn_rec.transaction_id := fnd_api.g_miss_num;
611 l_txn_rec.transaction_date := sysdate;
612 l_txn_rec.source_transaction_date := sysdate;
613 l_txn_rec.transaction_type_id := 51;
614
615 -- Populating the txn details for the child keys taking root keys passed from CZ.
616 IF px_config_tbl.COUNT > 0 THEN
617 l_child_ind := 1;
618 -- For each root key populate the child keys
619 FOR l_key IN px_config_tbl.FIRST .. px_config_tbl.LAST
620 LOOP
621
622 debug('Processing root key ');
623 debug('config_hdr_id('||l_key||') :'||px_config_tbl(l_key).config_inst_hdr_id);
624 debug('config_itm_id('||l_key||') :'||px_config_tbl(l_key).config_inst_item_id);
625 debug('config_rev_num('||l_key||') :'||px_config_tbl(l_key).config_inst_rev_num);
626 debug('src Appln Id('||l_key||') :'||px_config_tbl(l_key).source_application_id);
627
628 IF ( px_config_tbl(l_key).source_application_id is null
629 OR
630 px_config_tbl(l_key).source_application_id = fnd_api.g_miss_num
631 )
632 OR
633 ( px_config_tbl(l_key).source_txn_header_ref is null
634 OR
635 px_config_tbl(l_key).source_txn_header_ref = fnd_api.g_miss_num
636 )
637 THEN
638 fnd_message.set_name('CSI','CSI_CZ_LOCK_DTLS_MISS');
639 fnd_message.set_token('APPLN_ID',px_config_tbl(l_key).source_application_id);
640 fnd_message.set_token('HEADER_REF',px_config_tbl(l_key).source_txn_header_ref);
641 fnd_msg_pub.add;
642 RAISE fnd_api.g_exc_error;
643 END IF;
644
645 FOR sess_rec IN sess_cur(px_config_tbl(l_key).config_inst_hdr_id,
646 px_config_tbl(l_key).config_inst_rev_num,
647 px_config_tbl(l_key).config_inst_item_id)
648 LOOP
649 -- Build the lock config table for all the child and parent config keys
650 l_config_tbl(l_child_ind).source_application_id := px_config_tbl(l_key).source_application_id;
651 l_config_tbl(l_child_ind).source_txn_header_ref := px_config_tbl(l_key).source_txn_header_ref;
652 l_config_tbl(l_child_ind).config_inst_hdr_id := sess_rec.CONFIG_INST_HDR_ID;
653 l_config_tbl(l_child_ind).config_inst_rev_num := sess_rec.CONFIG_INST_REV_NUM;
654 l_config_tbl(l_child_ind).config_inst_item_id := sess_rec.CONFIG_INST_ITEM_ID;
655 -- l_config_tbl(l_child_ind).instance_id := sess_rec.INSTANCE_ID;
656
657
658 l_CONFIG_SESSION_HDR_ID := sess_rec.CONFIG_SESSION_HDR_ID;
659 l_CONFIG_SESSION_REV_NUM := sess_rec.CONFIG_SESSION_REV_NUM;
660 l_CONFIG_SESSION_ITEM_ID := sess_rec.CONFIG_SESSION_ITEM_ID;
661
662 -- Building the record for the config keys to check for the Lock Status.
663 l_config_rec.config_inst_hdr_id := sess_rec.CONFIG_INST_HDR_ID;
667 -- checking for the config keys if they are locked alreday.
664 l_config_rec.config_inst_rev_num := sess_rec.CONFIG_INST_REV_NUM;
665 l_config_rec.config_inst_item_id := sess_rec.CONFIG_INST_ITEM_ID;
666
668 l_lock := check_item_instance_lock (
669 p_init_msg_list => fnd_api.g_true,
670 p_config_rec => l_config_rec,
671 x_return_status => x_return_status,
672 x_msg_count => x_msg_count,
673 x_msg_data => x_msg_data);
674
675 IF (l_lock)
676 THEN
677 fnd_message.set_name('CSI','CSI_CONFIG_KEYS_LOCKED');
678 fnd_message.set_token('CONFIG_INST_HDR_ID',l_config_rec.config_inst_hdr_id);
679 fnd_message.set_token('CONFIG_INST_ITEM_ID',l_config_rec.config_inst_item_id);
680 fnd_message.set_token('CONFIG_INST_REV_NUM',l_config_rec.config_inst_rev_num);
681 fnd_msg_pub.add;
682 RAISE fnd_api.g_exc_error;
683 Exit;
684 ELSE
685 /*
686 -- Populating the order line details onto the key rec
687 BEGIN
688 SELECT line_number||'.'||
689 shipment_number||'.'||
690 option_number
691 INTO l_config_rec.source_txn_line_ref1
692 --,l_config_rec.source_txn_line_ref2
693 --,l_config_rec.source_txn_line_ref3
694 FROM oe_order_lines_all oel,
695 oe_order_headers_all oeh
696 WHERE oeh.header_id = oel.header_id
697 AND oeh.order_number = px_config_tbl(l_key).source_txn_header_ref
698 AND oel.config_header_id = l_CONFIG_SESSION_HDR_ID
699 AND oel.config_rev_nbr = l_CONFIG_SESSION_REV_NUM
700 AND oel.configuration_id = l_CONFIG_SESSION_ITEM_ID;
701
702 EXCEPTION
703 WHEN NO_DATA_FOUND Then
704 fnd_message.set_name('CSI','CSI_CZ_KEY_INVAL_OREDER');
705 fnd_msg_pub.add;
706 RAISE fnd_api.g_exc_error;
707 Exit;
708 END;
709 */
710 -- Populating the instance_id onto the key rec
711 -- IF l_config_rec.instance_id is null
712 -- OR
713 -- l_config_rec.instance_id = fnd_api.g_miss_num
714 -- THEN
715 BEGIN
716 SELECT instance_id
717 INTO l_config_tbl(l_child_ind).instance_id
718 FROM CSI_ITEM_INSTANCES
719 WHERE CONFIG_INST_HDR_ID = l_config_rec.config_inst_hdr_id
720 -- AND CONFIG_INST_REV_NUM = l_config_rec.config_inst_rev_num
721 AND CONFIG_INST_ITEM_ID = l_config_rec.config_inst_item_id;
722
723 EXCEPTION
724 WHEN NO_DATA_FOUND Then
725 Null;
726 END;
727 -- END IF;
728 -- l_config_tbl(l_child_ind).source_txn_line_ref1 := l_config_rec.source_txn_line_ref1;
729 -- l_config_tbl(l_child_ind).source_txn_line_ref2 := l_config_rec.source_txn_line_ref2;
730 -- l_config_tbl(l_child_ind).source_txn_line_ref3 := l_config_rec.source_txn_line_ref3;
731 l_config_tbl(l_child_ind).lock_status := 2;
732 l_child_ind := l_child_ind + 1;
733 END IF; -- End If for Falg Check
734 END LOOP; -- End Loop for the cild keys
735 END LOOP; -- End Loop for Root Keys
736 END IF;
737
738 debug('Before call to csi_item_instance_pvt.lock_item_instances');
739 debug('Records count to be locked '||nvl(l_config_tbl.count,0));
740
741 csi_t_gen_utility_pvt.dump_api_info(
742 p_pkg_name => 'csi_item_instance_pvt',
743 p_api_name => 'lock_item_instance');
744
745 csi_t_gen_utility_pvt.dump_csi_config_tbl(
746 p_config_tbl => l_config_tbl);
747
748 -- Call to core API for Locking
749 csi_item_instance_pvt.lock_item_instances(
750 p_api_version => 1.0,
751 p_commit => fnd_api.g_false,
752 p_init_msg_list => fnd_api.g_true,
753 p_validation_level => fnd_api.g_valid_level_full,
754 px_config_tbl => l_config_tbl,
755 x_return_status => x_return_status,
756 x_msg_count => x_msg_count,
757 x_msg_data => x_msg_data);
758
759 IF x_return_status <> fnd_api.g_ret_sts_success THEN
760 debug('Failed csi_item_instance_pvt.lock_item_instance');
761 RAISE fnd_api.g_exc_error;
762 END IF;
763
764 -- Assigningrequired values to px_config keys
765 IF px_config_tbl.count > 0
766 THEN
767 FOR i IN px_config_tbl.FIRST .. px_config_tbl.LAST
768 LOOP
769 IF l_config_tbl.count > 0
770 THEN
771 FOR j in l_config_tbl.FIRST .. l_config_tbl.LAST
772 LOOP
773 IF px_config_tbl(i).config_inst_hdr_id = l_config_tbl(j).config_inst_hdr_id
774 AND
775 px_config_tbl(i).config_inst_rev_num = l_config_tbl(j).config_inst_rev_num
776 AND
777 px_config_tbl(i).config_inst_item_id = l_config_tbl(j).config_inst_item_id
778 THEN
779 px_config_tbl(i) := l_config_tbl(j);
780 debug('config_hdr_id :'||px_config_tbl(i).config_inst_hdr_id);
781 debug('config_itm_id :'||px_config_tbl(i).config_inst_item_id);
782 debug('config_rev_num :'||px_config_tbl(i).config_inst_rev_num);
786 END LOOP;
783 debug('lock_status :'||px_config_tbl(i).lock_status);
784 debug('lock_id :'||px_config_tbl(i).lock_id);
785 END IF;
787 END IF;
788 END LOOP;
789 END IF;
790
791 -- Standard call to get message count and if count is get message info.
792 FND_MSG_PUB.Count_And_Get
793 (p_count => x_msg_count ,
794 p_data => x_msg_data );
795
796 EXCEPTION
797 WHEN fnd_api.g_exc_error THEN
798 x_return_status := fnd_api.g_ret_sts_error;
799 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
800 FND_MSG_PUB.Count_And_Get
801 (p_count => x_msg_count ,
802 p_data => x_msg_data );
803 rollback to csi_cz_lock_item;
804 debug(l_return_message);
805 WHEN others THEN
806 fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');
807 fnd_message.set_token('MESSAGE', 'OTHERS Error :'||substr(sqlerrm, 1, 300));
808 fnd_msg_pub.add;
809 FND_MSG_PUB.Count_And_Get
810 (p_count => x_msg_count ,
811 p_data => x_msg_data );
812 x_return_status := fnd_api.g_ret_sts_error;
813 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
814 rollback to csi_cz_lock_item;
815 debug(l_return_message);
816 END lock_item_instances;
817
818 PROCEDURE get_lock_status(
819 p_config_inst_header_id IN NUMBER,
820 p_config_inst_rev_num IN NUMBER,
821 p_config_inst_item_id IN NUMBER,
822 x_lock_status OUT NOCOPY NUMBER,
823 x_lock_id OUT NOCOPY NUMBER)
824 IS
825 BEGIN
826 api_log('get_lock_status');
827
828 SELECT lock_status,
829 lock_id
830 INTO x_lock_status,
831 x_lock_id
832 FROM csi_item_instance_locks
833 WHERE config_inst_hdr_id = p_config_inst_header_id
834 AND config_inst_rev_num = p_config_inst_rev_num
835 AND config_inst_item_id = p_config_inst_item_id;
836
837 EXCEPTION
838 WHEN no_data_found THEN
839 x_lock_status := 0;
840 WHEN others THEN
841 x_lock_status := 0;
842 END get_lock_status;
843
844
845 PROCEDURE populate_connected_tbl(
846 p_config_inst_header_id IN NUMBER,
847 p_config_inst_rev_num IN NUMBER,
848 p_config_inst_item_id IN NUMBER,
849 p_config_rec IN config_rec,
850 x_conn_config_tbl OUT NOCOPY config_tbl,
851 x_return_status OUT NOCOPY varchar2)
852 IS
853 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
854 l_parent_hdr_id number;
855 l_parent_rev_num number;
856 l_parent_item_id number;
857 l_ind number := 0;
858
859 CURSOR sub_cur(l_hdr_id IN number, l_rev_num IN number, l_item_id IN NUMBER,
860 l_parent_hdr_id IN number, l_parent_rev_num IN number, l_parent_item_id IN number) IS
861 SELECT sub_config_inst_hdr_id,
862 sub_config_inst_rev_num,
863 sub_config_inst_item_id
864 FROM csi_t_ii_relationships
865 WHERE obj_config_inst_hdr_id = p_config_inst_header_id
866 AND obj_config_inst_rev_num = p_config_inst_rev_num
867 AND obj_config_inst_item_id = p_config_inst_item_id
868 AND sub_config_inst_hdr_id <> l_parent_hdr_id
869 -- AND sub_config_inst_rev_num <> l_parent_rev_num
870 AND sub_config_inst_item_id <> l_parent_item_id
871 AND relationship_type_code = 'CONNECTED-TO';
872
873 CURSOR obj_cur(l_hdr_id IN number, l_rev_num IN number, l_item_id IN NUMBER,
874 l_parent_hdr_id IN number, l_parent_rev_num IN number, l_parent_item_id IN number) IS
875 SELECT obj_config_inst_hdr_id,
876 obj_config_inst_rev_num,
877 obj_config_inst_item_id
878 FROM csi_t_ii_relationships
879 WHERE sub_config_inst_hdr_id = p_config_inst_header_id
880 AND sub_config_inst_rev_num = p_config_inst_rev_num
881 AND sub_config_inst_item_id = p_config_inst_item_id
882 AND obj_config_inst_hdr_id <> l_parent_hdr_id
883 -- AND obj_config_inst_rev_num <> l_parent_rev_num
884 AND obj_config_inst_item_id <> l_parent_item_id
885 AND relationship_type_code = 'CONNECTED-TO';
886
887 BEGIN
888
889 api_log('populate_connected_tbl');
890
891 x_return_status := fnd_api.g_ret_sts_success;
892 l_parent_hdr_id := p_config_rec.config_inst_hdr_id;
893 l_parent_rev_num := p_config_rec.config_inst_rev_num;
894 l_parent_item_id := p_config_rec.config_inst_item_id;
895
896 -- Building sub keys
897 FOR l_sub_key in sub_cur(p_config_inst_header_id,p_config_inst_rev_num,p_config_inst_item_id,
898 l_parent_hdr_id,l_parent_rev_num,l_parent_item_id)
899 LOOP
900 l_ind := l_ind + 1;
901 x_conn_config_tbl(l_ind).config_inst_hdr_id := l_sub_key.sub_config_inst_hdr_id;
902 x_conn_config_tbl(l_ind).config_inst_rev_num := l_sub_key.sub_config_inst_rev_num;
903 x_conn_config_tbl(l_ind).config_inst_item_id := l_sub_key.sub_config_inst_item_id;
904 x_conn_config_tbl(l_ind).source_txn_header_ref := p_config_rec.source_txn_header_ref;
905 x_conn_config_tbl(l_ind).source_txn_line_ref1 := p_config_rec.source_txn_line_ref1;
906 x_conn_config_tbl(l_ind).source_application_id := p_config_rec.source_application_id;
907
908 -- Populate the lock_status of each subject key
909 get_lock_status( p_config_inst_header_id => x_conn_config_tbl(l_ind).config_inst_hdr_id,
910 p_config_inst_rev_num => x_conn_config_tbl(l_ind).config_inst_rev_num,
914 END LOOP;
911 p_config_inst_item_id => x_conn_config_tbl(l_ind).config_inst_item_id,
912 x_lock_status => x_conn_config_tbl(l_ind).lock_status,
913 x_lock_id => x_conn_config_tbl(l_ind).lock_id);
915
916 -- Building obj keys
917 FOR l_obj_key in obj_cur(p_config_inst_header_id,p_config_inst_rev_num,p_config_inst_item_id,
918 l_parent_hdr_id,l_parent_rev_num,l_parent_item_id)
919 LOOP
920 l_ind := l_ind + 1;
921 x_conn_config_tbl(l_ind).config_inst_hdr_id := l_obj_key.obj_config_inst_hdr_id;
922 x_conn_config_tbl(l_ind).config_inst_rev_num := l_obj_key.obj_config_inst_rev_num;
923 x_conn_config_tbl(l_ind).config_inst_item_id := l_obj_key.obj_config_inst_item_id;
924 x_conn_config_tbl(l_ind).source_txn_header_ref := p_config_rec.source_txn_header_ref;
925 x_conn_config_tbl(l_ind).source_txn_line_ref1 := p_config_rec.source_txn_line_ref1;
926 x_conn_config_tbl(l_ind).source_application_id := p_config_rec.source_application_id;
927
928 -- Populate the lock_status of each object key
929 get_lock_status( p_config_inst_header_id => x_conn_config_tbl(l_ind).config_inst_hdr_id,
930 p_config_inst_rev_num => x_conn_config_tbl(l_ind).config_inst_rev_num,
931 p_config_inst_item_id => x_conn_config_tbl(l_ind).config_inst_item_id,
932 x_lock_status => x_conn_config_tbl(l_ind).lock_status,
933 x_lock_id => x_conn_config_tbl(l_ind).lock_id);
934 END LOOP;
935 END populate_connected_tbl;
936
937 PROCEDURE Unlock_Current_Node(
938 p_api_version IN NUMBER,
939 p_init_msg_list IN VARCHAR2,
940 p_commit IN VARCHAR2,
941 p_validation_level IN NUMBER,
942 p_config_rec IN config_rec,
943 x_conn_config_tbl OUT NOCOPY config_tbl,
944 x_return_status OUT NOCOPY varchar2,
945 x_msg_count OUT NOCOPY NUMBER,
946 x_msg_data OUT NOCOPY VARCHAR2 )
947 IS
948 l_config_tbl config_tbl;
949 l_comp_conn_config_tbl config_tbl;
950 l_config_rec config_rec;
951 l_child_config_rec config_rec;
952 l_return_message VARCHAR2(2000);
953 l_root_inst_hdr_id NUMBER;
954 l_root_inst_rev_num NUMBER;
955 l_root_inst_item_id NUMBER;
956 l_root BOOLEAN;
957
958 l_lock_status NUMBER := 0;
959 l_child_ind NUMBER := 0;
960 l_found_locked BOOLEAN;
961
962 CURSOR comp_cur(l_root_inst_hdr_id IN number, l_root_inst_rev_num IN number,
963 l_config_inst_hdr_id IN NUMBER,l_config_inst_rev_num IN NUMBER,
964 l_config_inst_item_id IN NUMBER) IS
965 SELECT *
966 FROM csi_item_instance_locks
967 WHERE root_config_inst_hdr_id = l_root_inst_hdr_id
968 AND root_config_inst_rev_num = l_root_inst_rev_num
969 AND NOT( config_inst_hdr_id = l_config_inst_hdr_id
970 AND config_inst_rev_num = l_config_inst_rev_num
971 AND config_inst_item_id = l_config_inst_item_id )
972 AND lock_status <> 0;
973
974 BEGIN
975
976 x_return_status := fnd_api.g_ret_sts_success;
977 api_log('unlock_current_node');
978
979 savepoint unlock_current_node;
980
981 -- This is called from csi_order_fulfillment proc.
982 debug('Processing unlock for config keys:'|| p_config_rec.config_inst_hdr_id||'-'|| p_config_rec.config_inst_rev_num||'-'||p_config_rec.config_inst_item_id);
983
984 l_config_rec := p_config_rec;
985
986 -- Populate the Subject and Object Config Key along with the lock status
987 -- for the fulfillable Item with Connected-To relationship.
988 debug('Populating the connected to keys for the passed key');
989
990 populate_connected_tbl(
991 p_config_inst_header_id => p_config_rec.config_inst_hdr_id,
992 p_config_inst_rev_num => p_config_rec.config_inst_rev_num,
993 p_config_inst_item_id => p_config_rec.config_inst_item_id,
994 p_config_rec => l_config_rec,
995 x_conn_config_tbl => x_conn_config_tbl,
996 x_return_status => x_return_status);
997
998 IF x_return_status <> fnd_api.g_ret_sts_success THEN
999 RAISE fnd_api.g_exc_error;
1000 END IF;
1001
1002
1003 -- If any of the neighbours of the fulfilled INstance is in Locked status
1004 -- then mark the fulfillable instance to be "To Be Unlocked i.e 1"
1005 --
1006 l_found_locked := FALSE;
1007 l_root := TRUE; -- Defaulting it to TRUE becos if neighbors are in locked state then
1008 -- the current node should be set to 1. Root will be checked only if
1009 -- all connected-to's are in unlocked state.
1010 IF x_conn_config_tbl.count > 0 THEN
1011 FOR i in x_conn_config_tbl.FIRST .. x_conn_config_tbl.LAST
1012 LOOP
1013 IF x_conn_config_tbl(i).lock_status = 2 THEN
1014 l_found_locked := TRUE;
1015 EXIT;
1016 END IF;
1017 END LOOP;
1018 END IF;
1019 --
1020 IF l_found_locked = FALSE THEN
1021 debug('None of the Connected-To are in Locked Status. So Checking Components..');
1022 -- Checking for component of relationships
1023 SELECT root_config_inst_hdr_id,
1024 root_config_inst_rev_num,
1025 root_config_inst_item_id
1026 INTO l_root_inst_hdr_id,
1027 l_root_inst_rev_num,
1028 l_root_inst_item_id
1029 FROM csi_item_instance_locks
1030 WHERE config_inst_hdr_id = p_config_rec.config_inst_hdr_id
1031 AND config_inst_rev_num = p_config_rec.config_inst_rev_num
1032 AND config_inst_item_id = p_config_rec.config_inst_item_id;
1033 --
1037 debug('Current Node qualifies as Root...');
1034 IF p_config_rec.config_inst_hdr_id = l_root_inst_hdr_id AND
1035 p_config_rec.config_inst_rev_num = l_root_inst_rev_num AND
1036 p_config_rec.config_inst_item_id = l_root_inst_item_id THEN
1038 l_root := TRUE;
1039 ELSE
1040 debug('Current Node is not the Root...');
1041 l_root := FALSE;
1042 END IF;
1043 --
1044 FOR comp_rec IN COMP_CUR(l_root_inst_hdr_id,l_root_inst_rev_num,
1045 p_config_rec.config_inst_hdr_id,
1046 p_config_rec.config_inst_rev_num,
1047 p_config_rec.config_inst_item_id ) LOOP
1048 IF comp_rec.lock_status = 2 THEN
1049 debug('One of the components is in Locked State. Cannot un-lock the Root..');
1050 l_found_locked := TRUE;
1051 l_config_tbl.DELETE; -- Deleting the children from the List
1052 EXIT;
1053 END IF;
1054 --
1055 -- Keep Adding the components to the list.
1056 -- Look for components connections
1057 l_child_config_rec.config_inst_hdr_id := comp_rec.config_inst_hdr_id;
1058 l_child_config_rec.config_inst_rev_num := comp_rec.config_inst_rev_num;
1059 l_child_config_rec.config_inst_item_id := comp_rec.config_inst_item_id;
1060 --
1061 -- Even though the components that are in 1 status are purely because of their
1062 -- connections havig status 2, we still call the populate_connected_tbl routine.
1063 -- This is because during re-configuring API will lock the components which are not
1064 -- there in the order with status 1. Obviously, such configurations won't be there
1065 -- in CSI_T_II_RELATIONSHIPS. Since we cannot distinguish between configuring and re-configuring
1066 -- we always call the below routine to look for component's connections.
1067 --
1068 populate_connected_tbl(
1069 p_config_inst_header_id => comp_rec.config_inst_hdr_id,
1070 p_config_inst_rev_num => comp_rec.config_inst_rev_num,
1071 p_config_inst_item_id => comp_rec.config_inst_item_id,
1072 p_config_rec => l_child_config_rec,
1073 x_conn_config_tbl => l_comp_conn_config_tbl,
1074 x_return_status => x_return_status);
1075
1076 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1077 RAISE fnd_api.g_exc_error;
1078 END IF;
1079 --
1080 IF l_comp_conn_config_tbl.count > 0 THEN
1081 FOR i in l_comp_conn_config_tbl.FIRST .. l_comp_conn_config_tbl.LAST
1082 LOOP
1083 IF l_comp_conn_config_tbl(i).lock_status = 2 THEN
1084 l_found_locked := TRUE;
1085 EXIT;
1086 END IF;
1087 END LOOP;
1088 END IF;
1089 --
1090 IF l_found_locked = TRUE THEN
1091 EXIT;
1092 END IF;
1093 --
1094 l_child_ind := l_config_tbl.count + 1;
1095 l_config_tbl(l_child_ind).config_inst_hdr_id := comp_rec.config_inst_hdr_id;
1096 l_config_tbl(l_child_ind).config_inst_rev_num := comp_rec.config_inst_rev_num;
1097 l_config_tbl(l_child_ind).config_inst_item_id := comp_rec.config_inst_item_id;
1098 l_config_tbl(l_child_ind).lock_id := comp_rec.lock_id;
1099 l_config_tbl(l_child_ind).lock_status := 0;
1100 l_config_tbl(l_child_ind).source_txn_header_ref := p_config_rec.source_txn_header_ref;
1101 l_config_tbl(l_child_ind).source_txn_line_ref1 := p_config_rec.source_txn_line_ref1;
1102 l_config_tbl(l_child_ind).source_application_id := p_config_rec.source_application_id;
1103 END LOOP;
1104 END IF; -- components check
1105 --
1106 IF l_found_locked THEN
1107 l_config_tbl.DELETE; -- Ignoring the previously loaded list
1108 IF l_root = TRUE THEN
1109 l_config_tbl(1) := p_config_rec;
1110 l_config_tbl(1).lock_status := 1;
1111 ELSE
1112 l_config_tbl(1) := p_config_rec;
1113 l_config_tbl(1).lock_status := 0;
1114 END IF;
1115 ELSE -- Along with the children, parent will get unlocked. Adding the parent
1116 l_child_ind := l_config_tbl.count + 1;
1117 l_config_tbl(l_child_ind) := p_config_rec;
1118 l_config_tbl(l_child_ind).lock_status := 0;
1119 END IF;
1120 --
1121 csi_t_gen_utility_pvt.dump_api_info(
1122 p_pkg_name => 'csi_item_instance_pvt',
1123 p_api_name => 'unlock_item_instance');
1124
1125 csi_t_gen_utility_pvt.dump_csi_config_tbl(
1126 p_config_tbl => l_config_tbl);
1127
1128 csi_item_instance_pvt.unlock_item_instances(
1129 p_api_version => 1.0,
1130 p_commit => fnd_api.g_false,
1131 p_init_msg_list => fnd_api.g_true,
1132 p_validation_level => fnd_api.g_valid_level_full,
1133 p_config_tbl => l_config_tbl,
1134 p_unlock_all => fnd_api.g_false,
1135 x_return_status => x_return_status,
1136 x_msg_count => x_msg_count,
1137 x_msg_data => x_msg_data);
1138
1139 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1140 debug('Failed csi_item_instance_pvt.unlock_item_instance');
1141 RAISE fnd_api.g_exc_error;
1142 END IF;
1143 EXCEPTION
1144 WHEN fnd_api.g_exc_error THEN
1145 x_return_status := fnd_api.g_ret_sts_error;
1146 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
1147 FND_MSG_PUB.Count_And_Get
1148 (p_count => x_msg_count ,
1149 p_data => x_msg_data );
1150 rollback to unlock_current_node;
1151 debug(l_return_message);
1152 WHEN others THEN
1153 fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');
1154 fnd_message.set_token('MESSAGE', 'OTHERS Error :'||substr(sqlerrm, 1, 300));
1155 fnd_msg_pub.add;
1156 FND_MSG_PUB.Count_And_Get
1157 (p_count => x_msg_count ,
1158 p_data => x_msg_data );
1159 x_return_status := fnd_api.g_ret_sts_error;
1160 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
1161 rollback to unlock_current_node;
1165 PROCEDURE unlock_item_instances(
1162 debug(l_return_message);
1163 END Unlock_Current_Node;
1164
1166 p_api_version IN NUMBER,
1167 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1168 p_commit IN VARCHAR2 := FND_API.g_false,
1169 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1170 p_config_tbl IN config_tbl,
1171 x_return_status OUT NOCOPY varchar2,
1172 x_msg_count OUT NOCOPY NUMBER,
1173 x_msg_data OUT NOCOPY VARCHAR2 )
1174 IS
1175 l_txn_rec csi_datastructures_pub.transaction_rec;
1176 l_config_tbl config_tbl;
1177 l_all_config_tbl config_tbl;
1178 l_config_rec config_rec;
1179 x_conn_config_tbl config_tbl;
1180 l_conn_config_tbl config_tbl;
1181 l_return_message VARCHAR2(2000);
1182 l_lock_status NUMBER := 0;
1183 l_from_cz VARCHAR2(10) := 'NO';
1184
1185 l_lock_config_rec config_rec;
1186 l_lock_id NUMBER;
1187
1188 BEGIN
1189
1190 x_return_status := fnd_api.g_ret_sts_success;
1191 api_log('unlock_item_instance');
1192
1193 savepoint csi_cz_unlock_item;
1194
1195 -- Building txn rec
1196 -- l_txn_rec.transaction_id := fnd_api.g_miss_num;
1197 l_txn_rec.transaction_date := sysdate;
1198 l_txn_rec.source_transaction_date := sysdate;
1199 l_txn_rec.transaction_type_id := 401;
1200
1201 -- Populate Lock_id for passed keys
1202 If p_config_tbl.count > 0 Then
1203 Begin
1204 Select config_inst_hdr_id,
1205 config_inst_item_id,
1206 config_inst_rev_num,
1207 lock_id,
1208 lock_source_appln_id,
1209 lock_source_header_ref
1210 Into l_lock_config_rec.config_inst_hdr_id,
1211 l_lock_config_rec.config_inst_item_id,
1212 l_lock_config_rec.config_inst_rev_num,
1213 l_lock_config_rec.lock_id,
1214 l_lock_config_rec.source_application_id,
1215 l_lock_config_rec.source_txn_header_ref
1216 From csi_item_instance_locks
1217 Where config_inst_hdr_id = p_config_tbl(1).config_inst_hdr_id
1218 And config_inst_item_id = p_config_tbl(1).config_inst_item_id
1219 And config_inst_rev_num = p_config_tbl(1).config_inst_rev_num;
1220
1221 Exception
1222 When OTHERS Then
1223 debug('Lock_Id not found for keys '|| p_config_tbl(1).config_inst_hdr_id||'-'||p_config_tbl(1).config_inst_item_id||'-'||p_config_tbl(1).config_inst_rev_num);
1224 Null;
1225 End;
1226 End If;
1227
1228 -- Validate the lock_id if this proc. is called from other callers.
1229 IF p_config_tbl.count > 0
1230 THEN
1231 FOR l_key in p_config_tbl.FIRST .. p_config_tbl.LAST
1232 LOOP
1233 IF ( p_config_tbl(l_key).source_application_id <> 542
1234 AND
1235 p_config_tbl(l_key).source_application_id <> fnd_api.g_miss_num
1236 )
1237 AND
1238 -- In future need to make sure that lock_id is passed from other callers.
1239 ( p_config_tbl(l_key).source_txn_header_ref is NULL--lock_id is NULL
1240 OR
1241 p_config_tbl(l_key).source_txn_header_ref = fnd_api.g_miss_char --lock_id = fnd_api.g_miss_num
1242 )
1243 THEN
1244 fnd_message.set_name('CSI','CSI_CZ_LOCK_ID_MISS');
1245 fnd_message.set_token('CONFIG_INST_HDR_ID',p_config_tbl(l_key).config_inst_hdr_id);
1246 fnd_message.set_token('CONFIG_INST_REV_NUM',p_config_tbl(l_key).config_inst_rev_num);
1247 fnd_message.set_token('CONFIG_INST_ITEM_ID',p_config_tbl(l_key).config_inst_item_id);
1248 fnd_msg_pub.add;
1249 RAISE fnd_api.g_exc_error;
1250 EXIT;
1251 END IF;
1252 END LOOP;
1253 END IF;
1254
1255 -- If this proc. is called from other callers this might be a cancellation, delete etc..
1256 -- So setting the lock status to "0" for all the passed keys
1257
1258 debug('Source Application id :'||p_config_tbl(1).source_application_id);
1259
1260 IF p_config_tbl(1).source_application_id <> 542
1261 THEN
1262 -- This call is made for Cancellation. Suppose we re-configure an existing configuration and cancelling the same,
1263 -- OM unlocks the configuration. When the same order is re-configured again CZ puts the same revision number
1264 -- for the new lines. This creates multiple records in CSI_ITEM_INSTANCE_LOCKS for the config keys.
1265 -- To avoid this, we are deleting the rows upon cancellation.
1266 --
1267 DELETE FROM CSI_ITEM_INSTANCE_LOCKS
1268 WHERE lock_id = l_lock_config_rec.lock_id;
1269 --
1270 /********* COMMENTED
1271 l_from_cz := 'YES';
1272 IF p_config_tbl.count > 0
1273 THEN
1274 FOR i in p_config_tbl.FIRST .. p_config_tbl.LAST
1275 LOOP
1276 l_all_config_tbl(i) := p_config_tbl(i);
1277 l_all_config_tbl(i).lock_id := l_lock_config_rec.lock_id;
1278 l_all_config_tbl(i).lock_status := 0;
1279 END LOOP;
1280
1281 debug('Before call to csi_item_instance_pvt.unlock_item_instances');
1282 debug('Record count passed to api '||nvl(l_all_config_tbl.count,0));
1283
1284
1285 csi_t_gen_utility_pvt.dump_api_info(
1286 p_pkg_name => 'csi_item_instance_pvt',
1287 p_api_name => 'unlock_item_instance');
1288
1289 csi_t_gen_utility_pvt.dump_csi_config_tbl(
1290 p_config_tbl => l_all_config_tbl);
1291
1292 csi_item_instance_pvt.unlock_item_instances(
1293 p_api_version => 1.0,
1297 p_config_tbl => l_all_config_tbl,
1294 p_commit => fnd_api.g_false,
1295 p_init_msg_list => fnd_api.g_true,
1296 p_validation_level => fnd_api.g_valid_level_full,
1298 p_unlock_all => fnd_api.g_true,
1299 x_return_status => x_return_status,
1300 x_msg_count => x_msg_count,
1301 x_msg_data => x_msg_data);
1302
1303 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1304 debug('Failed csi_item_instance_pvt.unlock_item_instance');
1305 RAISE fnd_api.g_exc_error;
1306 END IF;
1307 END IF;
1308 ******** END OF COMMENT *******/
1309 ELSE
1310 -- This is called from csi_order_fulfillment proc.
1311 IF p_config_tbl.count > 0 and l_from_cz = 'NO'
1312 THEN
1313 -- For each passed key if the Lock status is "0" then condtinue the algorithm
1314 FOR l_key in p_config_tbl.FIRST .. p_config_tbl.LAST
1315 LOOP
1316 get_lock_status(
1317 p_config_inst_header_id => p_config_tbl(l_key).config_inst_hdr_id,
1318 p_config_inst_rev_num => p_config_tbl(l_key).config_inst_rev_num,
1319 p_config_inst_item_id => p_config_tbl(l_key).config_inst_item_id,
1320 x_lock_status => l_lock_status,
1321 x_lock_id => l_lock_id
1322 );
1323 IF l_lock_status = 2 THEN
1324 l_config_rec := p_config_tbl(l_key);
1325 l_config_rec.lock_id := l_lock_id;
1326 --
1327 Unlock_Current_Node(
1328 p_api_version => 1.0,
1329 p_init_msg_list => fnd_api.g_true,
1330 p_commit => fnd_api.g_false,
1331 p_validation_level => fnd_api.g_valid_level_full,
1332 p_config_rec => l_config_rec,
1333 x_conn_config_tbl => x_conn_config_tbl,
1334 x_return_status => x_return_status,
1335 x_msg_count => x_msg_count,
1336 x_msg_data => x_msg_data);
1337
1338 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1339 debug('Failed unlock_current_node');
1340 RAISE fnd_api.g_exc_error;
1341 END IF;
1342 --
1343 -- Process IJs
1344 debug('Connected to key count :'||nvl(x_conn_config_tbl.count,0));
1345 --
1346 IF x_conn_config_tbl.count > 0 THEN
1347 debug('Process IJs...');
1348 FOR i in x_conn_config_tbl.FIRST .. x_conn_config_tbl.LAST
1349 LOOP
1350 get_lock_status(
1351 p_config_inst_header_id => x_conn_config_tbl(i).config_inst_hdr_id,
1352 p_config_inst_rev_num => x_conn_config_tbl(i).config_inst_rev_num,
1353 p_config_inst_item_id => x_conn_config_tbl(i).config_inst_item_id,
1354 x_lock_status => l_lock_status,
1355 x_lock_id => l_lock_id
1356 );
1357 IF l_lock_status = 1 THEN -- Lock Status should be 1 for IJs
1358 l_config_rec := x_conn_config_tbl(i);
1359 l_config_rec.lock_id := l_lock_id;
1360 --
1361 Unlock_Current_Node(
1362 p_api_version => 1.0,
1363 p_init_msg_list => fnd_api.g_true,
1364 p_commit => fnd_api.g_false,
1365 p_validation_level => fnd_api.g_valid_level_full,
1366 p_config_rec => l_config_rec,
1367 x_conn_config_tbl => l_conn_config_tbl, -- will not be used further
1368 x_return_status => x_return_status,
1369 x_msg_count => x_msg_count,
1370 x_msg_data => x_msg_data);
1371
1372 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1373 debug('Failed unlock_current_node for IJs...');
1374 RAISE fnd_api.g_exc_error;
1375 END IF;
1376 END IF;
1377 END LOOP;
1378 END IF;
1379 ELSE
1380 debug('Config keys are already in unlocked status');
1381 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LOCKS');
1382 FND_MSG_PUB.Add;
1383 END IF;
1384 END LOOP;
1385 END IF;
1386 END IF;
1387
1388 -- Standard call to get message count and if count is get message info.
1389 FND_MSG_PUB.Count_And_Get
1390 (p_count => x_msg_count ,
1391 p_data => x_msg_data );
1392
1393 EXCEPTION
1394 WHEN fnd_api.g_exc_error THEN
1395 x_return_status := fnd_api.g_ret_sts_error;
1396 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
1397 FND_MSG_PUB.Count_And_Get
1398 (p_count => x_msg_count ,
1399 p_data => x_msg_data );
1400 rollback to csi_cz_unlock_item;
1401 debug(l_return_message);
1402 WHEN others THEN
1403 fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');
1404 fnd_message.set_token('MESSAGE', 'OTHERS Error :'||substr(sqlerrm, 1, 300));
1405 fnd_msg_pub.add;
1406 FND_MSG_PUB.Count_And_Get
1407 (p_count => x_msg_count ,
1408 p_data => x_msg_data );
1409 x_return_status := fnd_api.g_ret_sts_error;
1410 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
1411 rollback to csi_cz_unlock_item;
1412 debug(l_return_message);
1413
1414
1415
1416 END unlock_item_instances;
1417
1418 PROCEDURE configure_from_html_ui(
1419 p_session_hdr_id IN number,
1420 p_instance_id IN number,
1421 -- Added the following 3 parameters fro bug 3711457
1422 p_session_rev_num_old IN number,
1423 p_session_rev_num_new IN number,
1424 p_action IN varchar2,
1425 x_error_message OUT NOCOPY varchar2,
1429 IS
1426 x_return_status OUT NOCOPY varchar2,
1427 x_msg_count OUT NOCOPY number,
1428 x_msg_data OUT NOCOPY varchar2)
1430
1431 -- Included new parameter for the cursor for Bug 3711457
1432 CURSOR td_cur(p_sess_hdr_id IN number, p_sess_rev_num IN number) IS
1433 SELECT config_session_hdr_id,
1434 config_session_rev_num,
1435 config_session_item_id
1436 FROM csi_t_transaction_lines
1437 WHERE config_session_hdr_id = p_sess_hdr_id
1438 -- Added the and condition for Bug 3711457
1439 AND config_session_rev_num = p_sess_rev_num
1440 ORDER BY config_session_item_id;
1441
1442 l_session_keys csi_utility_grp.config_session_keys;
1443 l_instance_tbl csi_datastructures_pub.instance_tbl;
1444 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1445
1446 -- Added for BUg 3711457
1447 l_config_keys td_cur%ROWTYPE;
1448 l_usage_exists number;
1449 l_return_value number;
1450 l_error_message varchar2(2000);
1451
1452 BEGIN
1453
1454 x_return_status := fnd_api.g_ret_sts_success;
1455
1456 csi_t_gen_utility_pvt.build_file_name(
1457 p_file_segment1 => 'csiczuii',
1458 p_file_segment2 => p_session_hdr_id);
1459
1460 debug('Re-Configure from Install Base HTML User Interface');
1461 api_log('configure_from_html_ui');
1462
1463 debug(' p_session_hdr_id :'||p_session_hdr_id );
1464 debug(' p_instance_id :'||p_instance_id );
1465 debug(' p_rev_num_old :'||p_session_rev_num_old );
1466 debug(' p_rev_num_new :'||p_session_rev_num_new );
1467 debug(' p_action :'||p_action );
1468
1469 savepoint configure_from_html_ui;
1470
1471 -- Begin Code fix for Bug 3711457
1472 IF p_session_rev_num_old is NOT NULL
1473 THEN
1474 OPEN td_cur(p_session_hdr_id,p_session_rev_num_old);
1475
1476 FETCH td_cur INTO l_config_keys;
1477
1478 -- Calleg CZ Delete API to delete all the details
1479 -- corresponding to old_session_rev_number
1480 CZ_CF_API.delete_configuration(
1481 config_hdr_id => p_session_hdr_id,
1482 config_rev_nbr => p_session_rev_num_old,
1483 usage_exists => l_usage_exists,
1484 Error_message => l_error_message,
1485 Return_value => l_return_value);
1486
1487 IF l_return_value <> 1
1488 AND
1489 td_cur%ROWCOUNT > 0
1490 THEN
1491 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1492 fnd_message.set_token('MESSAGE', l_error_message);
1493 fnd_msg_pub.add;
1494 raise fnd_api.g_exc_error;
1495 END IF;
1496 CLOSE td_cur;
1497 END IF;
1498
1499 IF p_action = 'SAVE'
1500 AND
1501 ( p_session_rev_num_new is not null
1502 AND
1503 p_session_rev_num_new <> fnd_api.g_miss_num
1504 )
1505 THEN
1506 -- End Code Fix for 3711457
1507 -- Included new parameter for the cursor for Bug 3711457
1508 FOR td_rec IN td_cur (p_session_hdr_id,p_session_rev_num_new)
1509 LOOP
1510
1511 l_session_keys(td_cur%rowcount).session_hdr_id := td_rec.config_session_hdr_id;
1512 l_session_keys(td_cur%rowcount).session_rev_num := td_rec.config_session_rev_num;
1513 l_session_keys(td_cur%rowcount).session_item_id := td_rec.config_session_item_id;
1514
1515 END LOOP;
1516
1517 csi_interface_pkg.process_cz_txn_details(
1518 p_config_session_keys => l_session_keys,
1519 p_instance_id => p_instance_id,
1520 x_instance_tbl => l_instance_tbl,
1521 x_return_status => l_return_status);
1522
1523 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1524 raise fnd_api.g_exc_error;
1525 END IF;
1526
1527 debug('Re-Configure from Install Base HTML User Interface successful.');
1528 END IF; -- Added for Bug 3711457
1529
1530 EXCEPTION
1531 WHEN fnd_api.g_exc_error THEN
1532
1533 rollback to configure_from_html_ui;
1534
1535 x_return_status := fnd_api.g_ret_sts_error;
1536 x_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1537 x_msg_data := x_error_message;
1538 x_msg_count := 1;
1539 WHEN others THEN
1540
1541 rollback to configure_from_html_ui;
1542
1543 x_return_status := fnd_api.g_ret_sts_error;
1544 x_error_message := substr(sqlerrm, 1, 500);
1545 x_msg_data := x_error_message;
1546 x_msg_count := 1;
1547
1548 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1549 fnd_message.set_token('MESSAGE', x_error_message);
1550 fnd_msg_pub.add;
1551
1552 END configure_from_html_ui;
1553
1554 Procedure CSI_CONFIG_LAUNCH_PRMS
1555 ( p_api_version IN NUMBER,
1556 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1557 p_commit IN VARCHAR2 := FND_API.g_false,
1558 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1559 x_return_status OUT NOCOPY VARCHAR2,
1560 x_msg_count OUT NOCOPY NUMBER,
1561 x_msg_data OUT NOCOPY VARCHAR2,
1562 x_configurable OUT NOCOPY VARCHAR2,
1563 x_icx_sessn_tkt OUT NOCOPY VARCHAR2,
1564 x_db_id OUT NOCOPY VARCHAR2,
1565 x_servlet_url OUT NOCOPY VARCHAR2,
1566 x_sysdate OUT NOCOPY VARCHAR2
1567 ) is
1568 l_api_name CONSTANT VARCHAR2(30) := 'CSI_CONFIG_LAUNCH_PRMS';
1569 l_api_version CONSTANT NUMBER := 1.0;
1570
1571 l_resp_id NUMBER;
1572 l_resp_appl_id NUMBER;
1573 l_log_enabled VARCHAR2(1) := 'N';
1574 l_user_id NUMBER;
1575
1576 BEGIN
1577 l_user_id := fnd_global.user_id;
1578
1579 SAVEPOINT CSI_CONFIG_LAUNCH_PRMS;
1583 l_api_name ,
1580 -- Standard call to check for call compatibility.
1581 /*IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1582 p_api_version ,
1584 G_PKG_NAME )
1585 THEN
1586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1587 END IF;*/
1588
1589 -- Initialize message list if p_init_msg_list is set to TRUE.
1590 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1591 FND_MSG_PUB.initialize;
1592 END IF;
1593
1594 -- Initialize API rturn status to success
1595 x_return_status := FND_API.g_ret_sts_success;
1596
1597
1598 l_resp_id := fnd_profile.value('RESP_ID');
1599 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
1600
1601 -- get icx session ticket
1602 x_icx_sessn_tkt := CZ_CF_API.ICX_SESSION_TICKET;
1603
1604 -- get the dbc file name
1605 x_db_id := FND_WEB_CONFIG.DATABASE_ID;
1606
1607 -- get the URL for servlet
1608 x_servlet_url := fnd_profile.value('CZ_UIMGR_URL');
1609
1610 -- get the SYSDATE
1611 x_sysdate := to_char(sysdate,'mm-dd-yyyy-hh24-mi-ss');
1612
1613
1614 IF FND_API.To_Boolean( p_commit ) THEN
1615 COMMIT WORK;
1616 END IF;
1617 FND_MSG_PUB.Count_And_Get
1618 ( p_encoded => FND_API.G_FALSE,
1619 p_count => x_msg_count,
1620 p_data => x_msg_data
1621 );
1622 EXCEPTION
1623 WHEN FND_API.G_EXC_ERROR THEN
1624 ROLLBACK TO CSI_CONFIG_LAUNCH_PRMS;
1625 x_return_status := FND_API.G_RET_STS_ERROR ;
1626 FND_MSG_PUB.Count_And_Get
1627 ( p_encoded => FND_API.G_FALSE,
1628 p_count => x_msg_count,
1629 p_data => x_msg_data
1630 );
1631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1632 csi_gen_utility_pvt.put_line('csi_cz_int.CSI_CONFIG_LAUNCH_PRMS: UNEXPECTED ERROR EXCEPTION ');
1633 ROLLBACK TO CSI_CONFIG_LAUNCH_PRMS;
1634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1635 FND_MSG_PUB.Count_And_Get
1636 ( p_encoded => FND_API.G_FALSE,
1637 p_count => x_msg_count,
1638 p_data => x_msg_data
1639 );
1640 WHEN OTHERS THEN
1641 csi_gen_utility_pvt.put_line('csi_cz_int.CSI_CONFIG_LAUNCH_PRMS: OTHER EXCEPTION ');
1642 ROLLBACK TO CSI_CONFIG_LAUNCH_PRMS;
1643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1644 /*IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1645 THEN
1646 FND_MSG_PUB.Add_Exc_Msg
1647 ( G_PKG_NAME,
1648 l_api_name
1649 );
1650 END IF;*/
1651 FND_MSG_PUB.Count_And_Get
1652 ( p_encoded => FND_API.G_FALSE,
1653 p_count => x_msg_count,
1654 p_data => x_msg_data
1655 );
1656 /*ibe_util.disable_debug;*/
1657 END CSI_CONFIG_LAUNCH_PRMS; -- Procedure CSI_CONFIG_LAUNCH_PRMS
1658
1659
1660
1661 PROCEDURE IS_CONFIGURABLE(p_api_version IN NUMBER
1662 ,p_config_hdr_id IN NUMBER
1663 ,p_config_rev_nbr IN NUMBER
1664 ,p_config_item_id IN NUMBER
1665 ,x_return_value OUT NOCOPY VARCHAR2
1666 ,x_return_status OUT NOCOPY VARCHAR2
1667 ,x_msg_count OUT NOCOPY NUMBER
1668 ,x_msg_data OUT NOCOPY VARCHAR2
1669 ) IS
1670 l_found NUMBER;
1671 BEGIN
1672 cz_network_api_pub.IS_CONFIGURABLE(p_api_version
1673 ,p_config_hdr_id
1674 ,p_config_rev_nbr
1675 ,p_config_item_id
1676 ,x_return_value
1677 ,x_return_status
1678 ,x_msg_count
1679 ,x_msg_data);
1680
1681 -- Begin of fix for Bug 2873845
1682 -- Checking whether the config keys has a Instance.
1683 IF x_return_value = FND_API.G_FALSE
1684 THEN
1685 Begin
1686 Select count(*)
1687 Into l_found
1688 From csi_item_instances i,
1689 cz_config_items_v c
1690 Where i.config_inst_hdr_id = c.instance_hdr_id
1691 and i.config_inst_rev_num = c.instance_rev_nbr
1692 and i.config_inst_item_id = c.config_item_id
1693 and c.config_hdr_id = p_config_hdr_id
1694 and c.config_rev_nbr = p_config_rev_nbr
1695 and c.config_item_id = p_config_item_id;
1696
1697 IF NVL(l_found,0) > 0 Then
1698 x_return_value := FND_API.G_TRUE;
1699 ELSE
1700 x_return_value := FND_API.G_FALSE;
1701 END IF;
1702
1703 End;
1704 END IF;
1705 -- End of fix for Bug 2873845.
1706
1707 /*EXCEPTION
1708 WHEN exception_name THEN
1709 statements ;*/
1710 END IS_CONFIGURABLE;
1711
1712
1713 PROCEDURE generate_config_trees(p_api_version IN NUMBER,
1714 p_config_query_table IN config_query_table,
1715 p_tree_copy_mode IN VARCHAR2,
1716 x_cfg_model_tbl OUT NOCOPY config_model_tbl_type,
1717 x_return_status OUT NOCOPY VARCHAR2,
1718 x_msg_count OUT NOCOPY NUMBER,
1719 x_msg_data OUT NOCOPY VARCHAR2
1720 ) IS
1721
1722 l_in_cfg_tbl CZ_API_PUB.config_tbl_type;
1723 l_tree_copy_mode VARCHAR2(4) := 'R';
1724 l_index integer := 0;
1725 l_config_model_tbl CZ_API_PUB.config_model_tbl_type;
1726 l_appl_param_rec CZ_API_PUB.appl_param_rec_type;
1727
1728 BEGIN
1732 l_appl_param_rec.config_creation_date := sysdate;
1729 IF 0 < p_config_query_table.count() THEN
1730 l_index := p_config_query_table.FIRST;
1731
1733 l_appl_param_rec.config_model_lookup_date := null;
1734 l_appl_param_rec.config_effective_date := null;
1735 l_appl_param_rec.usage_name := null;
1736 l_appl_param_rec.publication_mode := null;
1737 l_appl_param_rec.language := 'US';
1738 l_appl_param_rec.calling_application_id := 542;
1739
1740 LOOP
1741 l_in_cfg_tbl(l_index).config_hdr_id := p_config_query_table(l_index).config_header_id;
1742 l_in_cfg_tbl(l_index).config_rev_nbr := p_config_query_table(l_index).config_revision_number;
1743
1744 EXIT WHEN l_index = p_config_query_table.LAST;
1745 l_index := p_config_query_table.NEXT(l_index);
1746 END LOOP;
1747
1748 CZ_NETWORK_API_PUB.generate_config_trees(p_api_version => p_api_version,
1749 p_config_tbl =>l_in_cfg_tbl,
1750 p_tree_copy_mode => l_tree_copy_mode,
1751 p_appl_param_rec => l_appl_param_rec,
1752 p_validation_context => CZ_API_PUB.G_INSTALLED,
1753 x_config_model_tbl=> l_config_model_tbl,
1754 x_return_status =>x_return_status,
1755 x_msg_count => x_msg_count,
1756 x_msg_data => x_msg_data );
1757
1758 IF 0 < l_config_model_tbl.count() THEN
1759 l_index := l_config_model_tbl.FIRST;
1760
1761 LOOP
1762 x_cfg_model_tbl(l_index).inventory_item_id := l_config_model_tbl(l_index).inventory_item_id;
1763 x_cfg_model_tbl(l_index).organization_id := l_config_model_tbl(l_index).organization_id;
1764 x_cfg_model_tbl(l_index).config_hdr_id := l_config_model_tbl(l_index).config_hdr_id;
1765 x_cfg_model_tbl(l_index).config_rev_nbr := l_config_model_tbl(l_index).config_rev_nbr;
1766 x_cfg_model_tbl(l_index).config_item_id := l_config_model_tbl(l_index).config_item_id;
1767
1768 EXIT WHEN l_index = l_config_model_tbl.LAST;
1769 l_index := l_config_model_tbl.NEXT(l_index);
1770 END LOOP;
1771 END IF; --IF 0 < l_config_model_tbl.count() THEN
1772
1773 END IF; --IF 0 < p_config_query_table.count() THEN
1774
1775 END generate_config_trees;
1776
1777 END csi_cz_int;