[Home] [Help]
PACKAGE BODY: APPS.CSI_CZ_INT
Source
1 PACKAGE BODY csi_cz_int AS
2 /* $Header: csigczib.pls 120.5.12020000.2 2012/07/04 10:20:26 sjawaji ship $ */
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);
368 l_conn_hdr_id := l_sub_hdr_id; -- the connected instance hdr ID
369 EXCEPTION
370 WHEN no_data_found THEN
371 RAISE do_not_build;
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
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
470 AND LOCK_STATUS <> 0;
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,
576 SELECT ctl.CONFIG_SESSION_HDR_ID,
573 p_config_inst_rev_num IN NUMBER,
574 p_config_inst_item_id in NUMBER)
575 IS
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;
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
667 -- checking for the config keys if they are locked alreday.
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||'.'||
692 --,l_config_rec.source_txn_line_ref2
689 shipment_number||'.'||
690 option_number
691 INTO l_config_rec.source_txn_line_ref1
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);
783 debug('lock_status :'||px_config_tbl(i).lock_status);
784 debug('lock_id :'||px_config_tbl(i).lock_id);
785 END IF;
786 END LOOP;
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 );
815 debug(l_return_message);
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;
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,
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);
914 END LOOP;
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
932 x_lock_status => x_conn_config_tbl(l_ind).lock_status,
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,
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 l_comp_exists BOOLEAN; -- Added for bug 13401321
962 l_root_rec config_rec; -- Added for bug 13401321
963
964 CURSOR comp_cur(l_root_inst_hdr_id IN number, l_root_inst_rev_num IN number,
965 l_config_inst_hdr_id IN NUMBER,l_config_inst_rev_num IN NUMBER,
966 l_config_inst_item_id IN NUMBER) IS
967 SELECT *
968 FROM csi_item_instance_locks
969 WHERE root_config_inst_hdr_id = l_root_inst_hdr_id
970 AND root_config_inst_rev_num = l_root_inst_rev_num
971 AND NOT( config_inst_hdr_id = l_config_inst_hdr_id
972 AND config_inst_rev_num = l_config_inst_rev_num
973 AND config_inst_item_id = l_config_inst_item_id )
974 AND lock_status <> 0;
975
976 BEGIN
977
978 x_return_status := fnd_api.g_ret_sts_success;
979 api_log('unlock_current_node');
980
981 savepoint unlock_current_node;
982
983 -- This is called from csi_order_fulfillment proc.
984 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);
985
986 l_config_rec := p_config_rec;
987
988 -- Populate the Subject and Object Config Key along with the lock status
989 -- for the fulfillable Item with Connected-To relationship.
990 debug('Populating the connected to keys for the passed key');
991
992 populate_connected_tbl(
993 p_config_inst_header_id => p_config_rec.config_inst_hdr_id,
994 p_config_inst_rev_num => p_config_rec.config_inst_rev_num,
995 p_config_inst_item_id => p_config_rec.config_inst_item_id,
996 p_config_rec => l_config_rec,
997 x_conn_config_tbl => x_conn_config_tbl,
998 x_return_status => x_return_status);
999
1000 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1001 RAISE fnd_api.g_exc_error;
1002 END IF;
1003
1004
1005 -- If any of the neighbours of the fulfilled INstance is in Locked status
1006 -- then mark the fulfillable instance to be "To Be Unlocked i.e 1"
1007 --
1008 l_found_locked := FALSE;
1009 l_root := TRUE; -- Defaulting it to TRUE becos if neighbors are in locked state then
1010 -- the current node should be set to 1. Root will be checked only if
1011 -- all connected-to's are in unlocked state.
1012 IF x_conn_config_tbl.count > 0 THEN
1013 FOR i in x_conn_config_tbl.FIRST .. x_conn_config_tbl.LAST
1014 LOOP
1015 IF x_conn_config_tbl(i).lock_status = 2 THEN
1016 l_found_locked := TRUE;
1017 EXIT;
1018 END IF;
1019 END LOOP;
1020 END IF;
1021 --
1022 IF l_found_locked = FALSE THEN
1023 debug('None of the Connected-To are in Locked Status. So Checking Components..');
1024 -- Checking for component of relationships
1025 SELECT root_config_inst_hdr_id,
1026 root_config_inst_rev_num,
1027 root_config_inst_item_id
1028 INTO l_root_inst_hdr_id,
1029 l_root_inst_rev_num,
1030 l_root_inst_item_id
1031 FROM csi_item_instance_locks
1032 WHERE config_inst_hdr_id = p_config_rec.config_inst_hdr_id
1033 AND config_inst_rev_num = p_config_rec.config_inst_rev_num
1034 AND config_inst_item_id = p_config_rec.config_inst_item_id;
1035 --
1036 IF p_config_rec.config_inst_hdr_id = l_root_inst_hdr_id AND
1037 p_config_rec.config_inst_rev_num = l_root_inst_rev_num AND
1038 p_config_rec.config_inst_item_id = l_root_inst_item_id THEN
1039 debug('Current Node qualifies as Root...');
1040 l_root := TRUE;
1041 ELSE
1042 debug('Current Node is not the Root...');
1043 l_root := FALSE;
1044 END IF;
1045 --
1046 l_comp_exists := FALSE; -- Added for bug 13401321
1047 FOR comp_rec IN COMP_CUR(l_root_inst_hdr_id,l_root_inst_rev_num,
1048 p_config_rec.config_inst_hdr_id,
1049 p_config_rec.config_inst_rev_num,
1050 p_config_rec.config_inst_item_id ) LOOP
1051 l_comp_exists := TRUE; -- Added for bug 13401321
1052 IF comp_rec.lock_status = 2 THEN
1053 debug('One of the components is in Locked State. Cannot un-lock the Root..');
1057 END IF;
1054 l_found_locked := TRUE;
1055 l_config_tbl.DELETE; -- Deleting the children from the List
1056 EXIT;
1058 --
1059 -- Keep Adding the components to the list.
1060 -- Look for components connections
1061 l_child_config_rec.config_inst_hdr_id := comp_rec.config_inst_hdr_id;
1062 l_child_config_rec.config_inst_rev_num := comp_rec.config_inst_rev_num;
1063 l_child_config_rec.config_inst_item_id := comp_rec.config_inst_item_id;
1064 --
1065 -- Even though the components that are in 1 status are purely because of their
1066 -- connections havig status 2, we still call the populate_connected_tbl routine.
1067 -- This is because during re-configuring API will lock the components which are not
1068 -- there in the order with status 1. Obviously, such configurations won't be there
1069 -- in CSI_T_II_RELATIONSHIPS. Since we cannot distinguish between configuring and re-configuring
1070 -- we always call the below routine to look for component's connections.
1071 --
1072 populate_connected_tbl(
1073 p_config_inst_header_id => comp_rec.config_inst_hdr_id,
1074 p_config_inst_rev_num => comp_rec.config_inst_rev_num,
1075 p_config_inst_item_id => comp_rec.config_inst_item_id,
1076 p_config_rec => l_child_config_rec,
1077 x_conn_config_tbl => l_comp_conn_config_tbl,
1078 x_return_status => x_return_status);
1079
1080 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1081 RAISE fnd_api.g_exc_error;
1082 END IF;
1083 --
1084 IF l_comp_conn_config_tbl.count > 0 THEN
1085 FOR i in l_comp_conn_config_tbl.FIRST .. l_comp_conn_config_tbl.LAST
1086 LOOP
1087 IF l_comp_conn_config_tbl(i).lock_status = 2 THEN
1088 l_found_locked := TRUE;
1089 EXIT;
1090 END IF;
1091 END LOOP;
1092 END IF;
1093 --
1094 IF l_found_locked = TRUE THEN
1095 EXIT;
1096 END IF;
1097 --
1098 l_child_ind := l_config_tbl.count + 1;
1099 l_config_tbl(l_child_ind).config_inst_hdr_id := comp_rec.config_inst_hdr_id;
1100 l_config_tbl(l_child_ind).config_inst_rev_num := comp_rec.config_inst_rev_num;
1101 l_config_tbl(l_child_ind).config_inst_item_id := comp_rec.config_inst_item_id;
1102 l_config_tbl(l_child_ind).lock_id := comp_rec.lock_id;
1103 l_config_tbl(l_child_ind).lock_status := 0;
1104 l_config_tbl(l_child_ind).source_txn_header_ref := p_config_rec.source_txn_header_ref;
1105 l_config_tbl(l_child_ind).source_txn_line_ref1 := p_config_rec.source_txn_line_ref1;
1106 l_config_tbl(l_child_ind).source_application_id := p_config_rec.source_application_id;
1107 END LOOP;
1108 END IF; -- components check
1109 --
1110 IF l_found_locked THEN
1111 l_config_tbl.DELETE; -- Ignoring the previously loaded list
1112 IF l_root = TRUE THEN
1113 l_config_tbl(1) := p_config_rec;
1114 l_config_tbl(1).lock_status := 1;
1115 ELSE
1116 l_config_tbl(1) := p_config_rec;
1117 l_config_tbl(1).lock_status := 0;
1118 -- Added for bug 13401321 Start
1119 IF l_comp_exists = FALSE THEN
1120 debug('l_comp_exists : FALSE');
1121 BEGIN
1122 SELECT config_inst_hdr_id,
1123 config_inst_rev_num,
1124 config_inst_item_id,
1125 lock_id,
1126 lock_status,
1127 p_config_rec.source_txn_header_ref,
1128 p_config_rec.source_txn_line_ref1,
1129 p_config_rec.source_application_id
1130 INTO l_root_rec.config_inst_hdr_id,
1131 l_root_rec.config_inst_rev_num,
1132 l_root_rec.config_inst_item_id,
1133 l_root_rec.lock_id,
1134 l_root_rec.lock_status,
1135 l_root_rec.source_txn_header_ref,
1136 l_root_rec.source_txn_line_ref1,
1137 l_root_rec.source_application_id
1138 FROM csi_item_instance_locks
1139 WHERE config_inst_hdr_id = root_config_inst_hdr_id
1140 AND config_inst_rev_num = root_config_inst_rev_num
1141 AND config_inst_item_id = root_config_inst_item_id;
1142
1143 debug('l_root_rec.lock_status : ' || l_root_rec.lock_status);
1144 IF l_root_rec.lock_status = 1 THEN
1145 l_config_tbl(2) := l_root_rec;
1146 l_config_tbl(2).lock_status := 0;
1147 END IF;
1148 EXCEPTION
1149 WHEN OTHERS THEN
1150 debug('OTHERS Error :'||substr(sqlerrm, 1, 300));
1151 END;
1152 ELSE
1153 debug('l_comp_exists : TRUE');
1154 END IF; -- Added for bug 13401321 End
1155 END IF;
1156 ELSE -- Along with the children, parent will get unlocked. Adding the parent
1157 l_child_ind := l_config_tbl.count + 1;
1158 l_config_tbl(l_child_ind) := p_config_rec;
1159 l_config_tbl(l_child_ind).lock_status := 0;
1160 END IF;
1161 --
1162 csi_t_gen_utility_pvt.dump_api_info(
1163 p_pkg_name => 'csi_item_instance_pvt',
1164 p_api_name => 'unlock_item_instance');
1165
1166 csi_t_gen_utility_pvt.dump_csi_config_tbl(
1167 p_config_tbl => l_config_tbl);
1168
1169 csi_item_instance_pvt.unlock_item_instances(
1170 p_api_version => 1.0,
1171 p_commit => fnd_api.g_false,
1172 p_init_msg_list => fnd_api.g_true,
1173 p_validation_level => fnd_api.g_valid_level_full,
1174 p_config_tbl => l_config_tbl,
1175 p_unlock_all => fnd_api.g_false,
1176 x_return_status => x_return_status,
1177 x_msg_count => x_msg_count,
1178 x_msg_data => x_msg_data);
1179
1183 END IF;
1180 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1181 debug('Failed csi_item_instance_pvt.unlock_item_instance');
1182 RAISE fnd_api.g_exc_error;
1184 EXCEPTION
1185 WHEN fnd_api.g_exc_error THEN
1186 x_return_status := fnd_api.g_ret_sts_error;
1187 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
1188 FND_MSG_PUB.Count_And_Get
1189 (p_count => x_msg_count ,
1190 p_data => x_msg_data );
1191 rollback to unlock_current_node;
1192 debug(l_return_message);
1193 WHEN others THEN
1194 fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');
1195 fnd_message.set_token('MESSAGE', 'OTHERS Error :'||substr(sqlerrm, 1, 300));
1196 fnd_msg_pub.add;
1197 FND_MSG_PUB.Count_And_Get
1198 (p_count => x_msg_count ,
1199 p_data => x_msg_data );
1200 x_return_status := fnd_api.g_ret_sts_error;
1201 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
1202 rollback to unlock_current_node;
1203 debug(l_return_message);
1204 END Unlock_Current_Node;
1205
1206 PROCEDURE unlock_item_instances(
1207 p_api_version IN NUMBER,
1208 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1209 p_commit IN VARCHAR2 := FND_API.g_false,
1210 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1211 p_config_tbl IN config_tbl,
1212 x_return_status OUT NOCOPY varchar2,
1213 x_msg_count OUT NOCOPY NUMBER,
1214 x_msg_data OUT NOCOPY VARCHAR2 )
1215 IS
1216 l_txn_rec csi_datastructures_pub.transaction_rec;
1217 l_config_tbl config_tbl;
1218 l_all_config_tbl config_tbl;
1219 l_config_rec config_rec;
1220 x_conn_config_tbl config_tbl;
1221 l_conn_config_tbl config_tbl;
1222 l_return_message VARCHAR2(2000);
1223 l_lock_status NUMBER := 0;
1224 l_from_cz VARCHAR2(10) := 'NO';
1225
1226 l_lock_config_rec config_rec;
1227 l_lock_id NUMBER;
1228
1229 BEGIN
1230
1231 x_return_status := fnd_api.g_ret_sts_success;
1232 api_log('unlock_item_instance');
1233
1234 savepoint csi_cz_unlock_item;
1235
1236 -- Building txn rec
1237 -- l_txn_rec.transaction_id := fnd_api.g_miss_num;
1238 l_txn_rec.transaction_date := sysdate;
1239 l_txn_rec.source_transaction_date := sysdate;
1240 l_txn_rec.transaction_type_id := 401;
1241
1242 -- Populate Lock_id for passed keys
1243 If p_config_tbl.count > 0 Then
1244 Begin
1245 Select config_inst_hdr_id,
1246 config_inst_item_id,
1247 config_inst_rev_num,
1248 lock_id,
1249 lock_source_appln_id,
1250 lock_source_header_ref
1251 Into l_lock_config_rec.config_inst_hdr_id,
1252 l_lock_config_rec.config_inst_item_id,
1253 l_lock_config_rec.config_inst_rev_num,
1254 l_lock_config_rec.lock_id,
1255 l_lock_config_rec.source_application_id,
1256 l_lock_config_rec.source_txn_header_ref
1257 From csi_item_instance_locks
1258 Where config_inst_hdr_id = p_config_tbl(1).config_inst_hdr_id
1259 And config_inst_item_id = p_config_tbl(1).config_inst_item_id
1260 And config_inst_rev_num = p_config_tbl(1).config_inst_rev_num;
1261
1262 Exception
1263 When OTHERS Then
1264 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);
1265 Null;
1266 End;
1267 End If;
1268
1269 -- Validate the lock_id if this proc. is called from other callers.
1270 IF p_config_tbl.count > 0
1271 THEN
1272 FOR l_key in p_config_tbl.FIRST .. p_config_tbl.LAST
1273 LOOP
1274 IF ( p_config_tbl(l_key).source_application_id <> 542
1275 AND
1276 p_config_tbl(l_key).source_application_id <> fnd_api.g_miss_num
1277 )
1278 AND
1279 -- In future need to make sure that lock_id is passed from other callers.
1280 ( p_config_tbl(l_key).source_txn_header_ref is NULL--lock_id is NULL
1281 OR
1282 p_config_tbl(l_key).source_txn_header_ref = fnd_api.g_miss_char --lock_id = fnd_api.g_miss_num
1283 )
1284 THEN
1285 fnd_message.set_name('CSI','CSI_CZ_LOCK_ID_MISS');
1286 fnd_message.set_token('CONFIG_INST_HDR_ID',p_config_tbl(l_key).config_inst_hdr_id);
1287 fnd_message.set_token('CONFIG_INST_REV_NUM',p_config_tbl(l_key).config_inst_rev_num);
1288 fnd_message.set_token('CONFIG_INST_ITEM_ID',p_config_tbl(l_key).config_inst_item_id);
1289 fnd_msg_pub.add;
1290 RAISE fnd_api.g_exc_error;
1291 EXIT;
1292 END IF;
1293 END LOOP;
1294 END IF;
1295
1296 -- If this proc. is called from other callers this might be a cancellation, delete etc..
1297 -- So setting the lock status to "0" for all the passed keys
1298
1299 debug('Source Application id :'||p_config_tbl(1).source_application_id);
1300
1301 IF p_config_tbl(1).source_application_id <> 542
1302 THEN
1303 -- This call is made for Cancellation. Suppose we re-configure an existing configuration and cancelling the same,
1304 -- OM unlocks the configuration. When the same order is re-configured again CZ puts the same revision number
1305 -- for the new lines. This creates multiple records in CSI_ITEM_INSTANCE_LOCKS for the config keys.
1306 -- To avoid this, we are deleting the rows upon cancellation.
1307 --
1308 DELETE FROM CSI_ITEM_INSTANCE_LOCKS
1312 l_from_cz := 'YES';
1309 WHERE lock_id = l_lock_config_rec.lock_id;
1310 --
1311 /********* COMMENTED
1313 IF p_config_tbl.count > 0
1314 THEN
1315 FOR i in p_config_tbl.FIRST .. p_config_tbl.LAST
1316 LOOP
1317 l_all_config_tbl(i) := p_config_tbl(i);
1318 l_all_config_tbl(i).lock_id := l_lock_config_rec.lock_id;
1319 l_all_config_tbl(i).lock_status := 0;
1320 END LOOP;
1321
1322 debug('Before call to csi_item_instance_pvt.unlock_item_instances');
1323 debug('Record count passed to api '||nvl(l_all_config_tbl.count,0));
1324
1325
1326 csi_t_gen_utility_pvt.dump_api_info(
1327 p_pkg_name => 'csi_item_instance_pvt',
1328 p_api_name => 'unlock_item_instance');
1329
1330 csi_t_gen_utility_pvt.dump_csi_config_tbl(
1331 p_config_tbl => l_all_config_tbl);
1332
1333 csi_item_instance_pvt.unlock_item_instances(
1334 p_api_version => 1.0,
1335 p_commit => fnd_api.g_false,
1336 p_init_msg_list => fnd_api.g_true,
1337 p_validation_level => fnd_api.g_valid_level_full,
1338 p_config_tbl => l_all_config_tbl,
1339 p_unlock_all => fnd_api.g_true,
1340 x_return_status => x_return_status,
1341 x_msg_count => x_msg_count,
1342 x_msg_data => x_msg_data);
1343
1344 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1345 debug('Failed csi_item_instance_pvt.unlock_item_instance');
1346 RAISE fnd_api.g_exc_error;
1347 END IF;
1348 END IF;
1349 ******** END OF COMMENT *******/
1350 ELSE
1351 -- This is called from csi_order_fulfillment proc.
1352 IF p_config_tbl.count > 0 and l_from_cz = 'NO'
1353 THEN
1354 -- For each passed key if the Lock status is "0" then condtinue the algorithm
1355 FOR l_key in p_config_tbl.FIRST .. p_config_tbl.LAST
1356 LOOP
1357 get_lock_status(
1358 p_config_inst_header_id => p_config_tbl(l_key).config_inst_hdr_id,
1359 p_config_inst_rev_num => p_config_tbl(l_key).config_inst_rev_num,
1360 p_config_inst_item_id => p_config_tbl(l_key).config_inst_item_id,
1361 x_lock_status => l_lock_status,
1362 x_lock_id => l_lock_id
1363 );
1364 IF l_lock_status = 2 THEN
1365 l_config_rec := p_config_tbl(l_key);
1366 l_config_rec.lock_id := l_lock_id;
1367 --
1368 Unlock_Current_Node(
1369 p_api_version => 1.0,
1370 p_init_msg_list => fnd_api.g_true,
1371 p_commit => fnd_api.g_false,
1372 p_validation_level => fnd_api.g_valid_level_full,
1373 p_config_rec => l_config_rec,
1374 x_conn_config_tbl => x_conn_config_tbl,
1375 x_return_status => x_return_status,
1376 x_msg_count => x_msg_count,
1377 x_msg_data => x_msg_data);
1378
1379 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1380 debug('Failed unlock_current_node');
1381 RAISE fnd_api.g_exc_error;
1382 END IF;
1383 --
1384 -- Process IJs
1385 debug('Connected to key count :'||nvl(x_conn_config_tbl.count,0));
1386 --
1387 IF x_conn_config_tbl.count > 0 THEN
1388 debug('Process IJs...');
1389 FOR i in x_conn_config_tbl.FIRST .. x_conn_config_tbl.LAST
1390 LOOP
1391 get_lock_status(
1392 p_config_inst_header_id => x_conn_config_tbl(i).config_inst_hdr_id,
1393 p_config_inst_rev_num => x_conn_config_tbl(i).config_inst_rev_num,
1394 p_config_inst_item_id => x_conn_config_tbl(i).config_inst_item_id,
1395 x_lock_status => l_lock_status,
1396 x_lock_id => l_lock_id
1397 );
1398 IF l_lock_status = 1 THEN -- Lock Status should be 1 for IJs
1399 l_config_rec := x_conn_config_tbl(i);
1400 l_config_rec.lock_id := l_lock_id;
1401 --
1402 Unlock_Current_Node(
1403 p_api_version => 1.0,
1404 p_init_msg_list => fnd_api.g_true,
1405 p_commit => fnd_api.g_false,
1406 p_validation_level => fnd_api.g_valid_level_full,
1407 p_config_rec => l_config_rec,
1408 x_conn_config_tbl => l_conn_config_tbl, -- will not be used further
1409 x_return_status => x_return_status,
1410 x_msg_count => x_msg_count,
1411 x_msg_data => x_msg_data);
1412
1413 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1414 debug('Failed unlock_current_node for IJs...');
1415 RAISE fnd_api.g_exc_error;
1416 END IF;
1417 END IF;
1418 END LOOP;
1419 END IF;
1420 ELSE
1421 debug('Config keys are already in unlocked status');
1422 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_LOCKS');
1423 FND_MSG_PUB.Add;
1424 END IF;
1425 END LOOP;
1426 END IF;
1427 END IF;
1428
1429 -- Standard call to get message count and if count is get message info.
1430 FND_MSG_PUB.Count_And_Get
1431 (p_count => x_msg_count ,
1432 p_data => x_msg_data );
1433
1434 EXCEPTION
1435 WHEN fnd_api.g_exc_error THEN
1436 x_return_status := fnd_api.g_ret_sts_error;
1437 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
1438 FND_MSG_PUB.Count_And_Get
1439 (p_count => x_msg_count ,
1440 p_data => x_msg_data );
1441 rollback to csi_cz_unlock_item;
1442 debug(l_return_message);
1443 WHEN others THEN
1444 fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');
1445 fnd_message.set_token('MESSAGE', 'OTHERS Error :'||substr(sqlerrm, 1, 300));
1446 fnd_msg_pub.add;
1447 FND_MSG_PUB.Count_And_Get
1448 (p_count => x_msg_count ,
1449 p_data => x_msg_data );
1450 x_return_status := fnd_api.g_ret_sts_error;
1451 l_return_message := csi_t_gen_utility_pvt.dump_error_stack;
1452 rollback to csi_cz_unlock_item;
1453 debug(l_return_message);
1454
1455
1456
1457 END unlock_item_instances;
1458
1459 PROCEDURE configure_from_html_ui(
1460 p_session_hdr_id IN number,
1461 p_instance_id IN number,
1462 -- Added the following 3 parameters fro bug 3711457
1463 p_session_rev_num_old IN number,
1464 p_session_rev_num_new IN number,
1465 p_action IN varchar2,
1466 x_error_message OUT NOCOPY varchar2,
1467 x_return_status OUT NOCOPY varchar2,
1468 x_msg_count OUT NOCOPY number,
1469 x_msg_data OUT NOCOPY varchar2)
1470 IS
1471
1472 -- Included new parameter for the cursor for Bug 3711457
1473 CURSOR td_cur(p_sess_hdr_id IN number, p_sess_rev_num IN number) IS
1474 SELECT config_session_hdr_id,
1475 config_session_rev_num,
1476 config_session_item_id
1477 FROM csi_t_transaction_lines
1478 WHERE config_session_hdr_id = p_sess_hdr_id
1479 -- Added the and condition for Bug 3711457
1480 AND config_session_rev_num = p_sess_rev_num
1481 ORDER BY config_session_item_id;
1482
1483 l_session_keys csi_utility_grp.config_session_keys;
1484 l_instance_tbl csi_datastructures_pub.instance_tbl;
1485 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1486
1487 -- Added for BUg 3711457
1488 l_config_keys td_cur%ROWTYPE;
1489 l_usage_exists number;
1490 l_return_value number;
1491 l_error_message varchar2(2000);
1492
1493 BEGIN
1494
1495 x_return_status := fnd_api.g_ret_sts_success;
1496
1497 csi_t_gen_utility_pvt.build_file_name(
1498 p_file_segment1 => 'csiczuii',
1499 p_file_segment2 => p_session_hdr_id);
1500
1501 debug('Re-Configure from Install Base HTML User Interface');
1502 api_log('configure_from_html_ui');
1503
1504 debug(' p_session_hdr_id :'||p_session_hdr_id );
1505 debug(' p_instance_id :'||p_instance_id );
1506 debug(' p_rev_num_old :'||p_session_rev_num_old );
1507 debug(' p_rev_num_new :'||p_session_rev_num_new );
1508 debug(' p_action :'||p_action );
1509
1510 savepoint configure_from_html_ui;
1511
1512 -- Begin Code fix for Bug 3711457
1513 IF p_session_rev_num_old is NOT NULL
1514 THEN
1515 OPEN td_cur(p_session_hdr_id,p_session_rev_num_old);
1516
1517 FETCH td_cur INTO l_config_keys;
1518
1519 -- Calleg CZ Delete API to delete all the details
1520 -- corresponding to old_session_rev_number
1521 CZ_CF_API.delete_configuration(
1522 config_hdr_id => p_session_hdr_id,
1523 config_rev_nbr => p_session_rev_num_old,
1524 usage_exists => l_usage_exists,
1525 Error_message => l_error_message,
1526 Return_value => l_return_value);
1527
1528 IF l_return_value <> 1
1529 AND
1530 td_cur%ROWCOUNT > 0
1531 THEN
1532 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1533 fnd_message.set_token('MESSAGE', l_error_message);
1534 fnd_msg_pub.add;
1535 raise fnd_api.g_exc_error;
1536 END IF;
1537 CLOSE td_cur;
1538 END IF;
1539
1540 IF p_action = 'SAVE'
1541 AND
1542 ( p_session_rev_num_new is not null
1543 AND
1544 p_session_rev_num_new <> fnd_api.g_miss_num
1545 )
1546 THEN
1547 -- End Code Fix for 3711457
1548 -- Included new parameter for the cursor for Bug 3711457
1549 FOR td_rec IN td_cur (p_session_hdr_id,p_session_rev_num_new)
1550 LOOP
1551
1552 l_session_keys(td_cur%rowcount).session_hdr_id := td_rec.config_session_hdr_id;
1553 l_session_keys(td_cur%rowcount).session_rev_num := td_rec.config_session_rev_num;
1554 l_session_keys(td_cur%rowcount).session_item_id := td_rec.config_session_item_id;
1555
1556 END LOOP;
1557
1558 csi_interface_pkg.process_cz_txn_details(
1559 p_config_session_keys => l_session_keys,
1560 p_instance_id => p_instance_id,
1561 x_instance_tbl => l_instance_tbl,
1562 x_return_status => l_return_status);
1563
1564 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1565 raise fnd_api.g_exc_error;
1566 END IF;
1567
1568 debug('Re-Configure from Install Base HTML User Interface successful.');
1569 END IF; -- Added for Bug 3711457
1570
1571 EXCEPTION
1572 WHEN fnd_api.g_exc_error THEN
1573
1574 rollback to configure_from_html_ui;
1575
1576 x_return_status := fnd_api.g_ret_sts_error;
1577 x_error_message := csi_t_gen_utility_pvt.dump_error_stack;
1578 x_msg_data := x_error_message;
1579 x_msg_count := 1;
1580 WHEN others THEN
1581
1582 rollback to configure_from_html_ui;
1583
1584 x_return_status := fnd_api.g_ret_sts_error;
1585 x_error_message := substr(sqlerrm, 1, 500);
1586 x_msg_data := x_error_message;
1587 x_msg_count := 1;
1588
1589 fnd_message.set_name('FND', 'FND_GENERIC_MESSAGE');
1590 fnd_message.set_token('MESSAGE', x_error_message);
1591 fnd_msg_pub.add;
1592
1593 END configure_from_html_ui;
1594
1595 Procedure CSI_CONFIG_LAUNCH_PRMS
1596 ( p_api_version IN NUMBER,
1597 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1598 p_commit IN VARCHAR2 := FND_API.g_false,
1599 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1600 x_return_status OUT NOCOPY VARCHAR2,
1601 x_msg_count OUT NOCOPY NUMBER,
1602 x_msg_data OUT NOCOPY VARCHAR2,
1603 x_configurable OUT NOCOPY VARCHAR2,
1604 x_icx_sessn_tkt OUT NOCOPY VARCHAR2,
1605 x_db_id OUT NOCOPY VARCHAR2,
1606 x_servlet_url OUT NOCOPY VARCHAR2,
1607 x_sysdate OUT NOCOPY VARCHAR2
1608 ) is
1609 l_api_name CONSTANT VARCHAR2(30) := 'CSI_CONFIG_LAUNCH_PRMS';
1610 l_api_version CONSTANT NUMBER := 1.0;
1611
1612 l_resp_id NUMBER;
1613 l_resp_appl_id NUMBER;
1614 l_log_enabled VARCHAR2(1) := 'N';
1615 l_user_id NUMBER;
1616
1617 BEGIN
1618 l_user_id := fnd_global.user_id;
1619
1620 SAVEPOINT CSI_CONFIG_LAUNCH_PRMS;
1621 -- Standard call to check for call compatibility.
1622 /*IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1623 p_api_version ,
1624 l_api_name ,
1625 G_PKG_NAME )
1626 THEN
1627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1628 END IF;*/
1629
1630 -- Initialize message list if p_init_msg_list is set to TRUE.
1631 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1632 FND_MSG_PUB.initialize;
1633 END IF;
1634
1635 -- Initialize API rturn status to success
1636 x_return_status := FND_API.g_ret_sts_success;
1637
1638
1639 l_resp_id := fnd_profile.value('RESP_ID');
1640 l_resp_appl_id := fnd_profile.value('RESP_APPL_ID');
1641
1642 -- get icx session ticket
1643 x_icx_sessn_tkt := CZ_CF_API.ICX_SESSION_TICKET;
1644
1645 -- get the dbc file name
1646 x_db_id := FND_WEB_CONFIG.DATABASE_ID;
1647
1648 -- get the URL for servlet
1649 x_servlet_url := fnd_profile.value('CZ_UIMGR_URL');
1650
1651 -- get the SYSDATE
1652 x_sysdate := to_char(sysdate,'mm-dd-yyyy-hh24-mi-ss');
1653
1654
1655 IF FND_API.To_Boolean( p_commit ) THEN
1656 COMMIT WORK;
1657 END IF;
1658 FND_MSG_PUB.Count_And_Get
1659 ( p_encoded => FND_API.G_FALSE,
1660 p_count => x_msg_count,
1661 p_data => x_msg_data
1662 );
1663 EXCEPTION
1664 WHEN FND_API.G_EXC_ERROR THEN
1665 ROLLBACK TO CSI_CONFIG_LAUNCH_PRMS;
1666 x_return_status := FND_API.G_RET_STS_ERROR ;
1667 FND_MSG_PUB.Count_And_Get
1668 ( p_encoded => FND_API.G_FALSE,
1669 p_count => x_msg_count,
1670 p_data => x_msg_data
1671 );
1672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1673 csi_gen_utility_pvt.put_line('csi_cz_int.CSI_CONFIG_LAUNCH_PRMS: UNEXPECTED ERROR EXCEPTION ');
1674 ROLLBACK TO CSI_CONFIG_LAUNCH_PRMS;
1675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1676 FND_MSG_PUB.Count_And_Get
1677 ( p_encoded => FND_API.G_FALSE,
1678 p_count => x_msg_count,
1679 p_data => x_msg_data
1680 );
1681 WHEN OTHERS THEN
1682 csi_gen_utility_pvt.put_line('csi_cz_int.CSI_CONFIG_LAUNCH_PRMS: OTHER EXCEPTION ');
1686 THEN
1683 ROLLBACK TO CSI_CONFIG_LAUNCH_PRMS;
1684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1685 /*IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1687 FND_MSG_PUB.Add_Exc_Msg
1688 ( G_PKG_NAME,
1689 l_api_name
1690 );
1691 END IF;*/
1692 FND_MSG_PUB.Count_And_Get
1693 ( p_encoded => FND_API.G_FALSE,
1694 p_count => x_msg_count,
1695 p_data => x_msg_data
1696 );
1697 /*ibe_util.disable_debug;*/
1698 END CSI_CONFIG_LAUNCH_PRMS; -- Procedure CSI_CONFIG_LAUNCH_PRMS
1699
1700
1701
1702 PROCEDURE IS_CONFIGURABLE(p_api_version IN NUMBER
1703 ,p_config_hdr_id IN NUMBER
1704 ,p_config_rev_nbr IN NUMBER
1705 ,p_config_item_id IN NUMBER
1706 ,x_return_value OUT NOCOPY VARCHAR2
1707 ,x_return_status OUT NOCOPY VARCHAR2
1708 ,x_msg_count OUT NOCOPY NUMBER
1709 ,x_msg_data OUT NOCOPY VARCHAR2
1710 ) IS
1711 l_found NUMBER;
1712 BEGIN
1713 cz_network_api_pub.IS_CONFIGURABLE(p_api_version
1714 ,p_config_hdr_id
1715 ,p_config_rev_nbr
1716 ,p_config_item_id
1717 ,x_return_value
1718 ,x_return_status
1719 ,x_msg_count
1720 ,x_msg_data);
1721
1722 -- Begin of fix for Bug 2873845
1723 -- Checking whether the config keys has a Instance.
1724 IF x_return_value = FND_API.G_FALSE
1725 THEN
1726 Begin
1727 Select count(*)
1728 Into l_found
1729 From csi_item_instances i,
1730 cz_config_items_v c
1731 Where i.config_inst_hdr_id = c.instance_hdr_id
1732 and i.config_inst_rev_num = c.instance_rev_nbr
1733 and i.config_inst_item_id = c.config_item_id
1734 and c.config_hdr_id = p_config_hdr_id
1735 and c.config_rev_nbr = p_config_rev_nbr
1736 and c.config_item_id = p_config_item_id;
1737
1738 IF NVL(l_found,0) > 0 Then
1739 x_return_value := FND_API.G_TRUE;
1740 ELSE
1741 x_return_value := FND_API.G_FALSE;
1742 END IF;
1743
1744 End;
1745 END IF;
1746 -- End of fix for Bug 2873845.
1747
1748 /*EXCEPTION
1749 WHEN exception_name THEN
1750 statements ;*/
1751 END IS_CONFIGURABLE;
1752
1753
1754 PROCEDURE generate_config_trees(p_api_version IN NUMBER,
1755 p_config_query_table IN config_query_table,
1756 p_tree_copy_mode IN VARCHAR2,
1757 x_cfg_model_tbl OUT NOCOPY config_model_tbl_type,
1758 x_return_status OUT NOCOPY VARCHAR2,
1759 x_msg_count OUT NOCOPY NUMBER,
1760 x_msg_data OUT NOCOPY VARCHAR2
1761 ) IS
1762
1763 l_in_cfg_tbl CZ_API_PUB.config_tbl_type;
1764 l_tree_copy_mode VARCHAR2(4) := 'R';
1765 l_index integer := 0;
1766 l_config_model_tbl CZ_API_PUB.config_model_tbl_type;
1767 l_appl_param_rec CZ_API_PUB.appl_param_rec_type;
1768
1769 BEGIN
1770 IF 0 < p_config_query_table.count() THEN
1771 l_index := p_config_query_table.FIRST;
1772
1773 l_appl_param_rec.config_creation_date := sysdate;
1774 l_appl_param_rec.config_model_lookup_date := null;
1775 l_appl_param_rec.config_effective_date := null;
1776 l_appl_param_rec.usage_name := null;
1777 l_appl_param_rec.publication_mode := null;
1778 l_appl_param_rec.language := 'US';
1779 l_appl_param_rec.calling_application_id := 542;
1780
1781 LOOP
1782 l_in_cfg_tbl(l_index).config_hdr_id := p_config_query_table(l_index).config_header_id;
1783 l_in_cfg_tbl(l_index).config_rev_nbr := p_config_query_table(l_index).config_revision_number;
1784
1785 EXIT WHEN l_index = p_config_query_table.LAST;
1786 l_index := p_config_query_table.NEXT(l_index);
1787 END LOOP;
1788
1789 CZ_NETWORK_API_PUB.generate_config_trees(p_api_version => p_api_version,
1790 p_config_tbl =>l_in_cfg_tbl,
1791 p_tree_copy_mode => l_tree_copy_mode,
1792 p_appl_param_rec => l_appl_param_rec,
1793 p_validation_context => CZ_API_PUB.G_INSTALLED,
1794 x_config_model_tbl=> l_config_model_tbl,
1795 x_return_status =>x_return_status,
1796 x_msg_count => x_msg_count,
1797 x_msg_data => x_msg_data );
1798
1799 IF 0 < l_config_model_tbl.count() THEN
1800 l_index := l_config_model_tbl.FIRST;
1801
1802 LOOP
1803 x_cfg_model_tbl(l_index).inventory_item_id := l_config_model_tbl(l_index).inventory_item_id;
1804 x_cfg_model_tbl(l_index).organization_id := l_config_model_tbl(l_index).organization_id;
1805 x_cfg_model_tbl(l_index).config_hdr_id := l_config_model_tbl(l_index).config_hdr_id;
1806 x_cfg_model_tbl(l_index).config_rev_nbr := l_config_model_tbl(l_index).config_rev_nbr;
1807 x_cfg_model_tbl(l_index).config_item_id := l_config_model_tbl(l_index).config_item_id;
1808
1809 EXIT WHEN l_index = l_config_model_tbl.LAST;
1810 l_index := l_config_model_tbl.NEXT(l_index);
1811 END LOOP;
1812 END IF; --IF 0 < l_config_model_tbl.count() THEN
1813
1814 END IF; --IF 0 < p_config_query_table.count() THEN
1815
1816 END generate_config_trees;
1817
1818 END csi_cz_int;