1 PACKAGE BODY AHL_UC_TREE_PVT AS
2 /* $Header: AHLVUCTB.pls 120.2.12010000.2 2008/11/06 10:55:52 sathapli ship $ */
3
4 -- Define global internal variables and cursors
5 G_PKG_NAME VARCHAR2(30) := 'AHL_UC_TREE_PVT';
6
7 -- Added by rbhavsar on July 25, 2007 to remap IB Tree Nodes to fix FP bug 6276991
8 TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
9
10 PROCEDURE Remap_IB_Tree(p_instance_id IN NUMBER,
11 p_relationship_id IN NUMBER);
12
13 PROCEDURE Process_instances(p_x_extra_instances_tbl IN OUT NOCOPY T_ID_TBL,
14 p_x_relations_tbl IN OUT NOCOPY T_ID_TBL);
15
16 -- Define procedure get_immediate_children
17 -- This API is used to draw the UC tree. For a given node, it will list all of its
18 -- immediate children nodes.
19 /*
20 PROCEDURE get_immediate_children(
21 p_api_version IN NUMBER := 1.0,
22 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
23 --p_commit IN VARCHAR2 := FND_API.G_FALSE,
24 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2,
28 p_uc_parent_rec IN uc_child_rec_type,
29 x_uc_child_tbl OUT NOCOPY uc_child_tbl_type)
30 IS
31 l_api_name CONSTANT VARCHAR2(30) := 'get_immediate_children';
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_return_status VARCHAR2(1);
34 l_msg_count NUMBER;
35 l_relationship_id NUMBER;
36 l_children_no NUMBER;
37 l_dummy VARCHAR2(1);
38 i NUMBER;
39 CURSOR check_installed_instance IS
40 SELECT 'x'
41 FROM ahl_unit_config_headers U,
42 ahl_mc_relationships R
43 WHERE U.master_config_id = R.mc_header_id
44 AND R.parent_relationship_id IS NULL
45 AND U.csi_item_instance_id = p_uc_parent_rec.instance_id
46 AND R.relationship_id = p_uc_parent_rec.relationship_id
47 AND nvl(trunc(U.active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE)
48 UNION
49 SELECT 'x'
50 FROM csi_ii_relationships
51 WHERE subject_id = p_uc_parent_rec.instance_id
52 AND position_reference = to_char(p_uc_parent_rec.relationship_id)
53 AND nvl(trunc(active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE);
54 CURSOR check_relationship IS
55 SELECT 'x'
56 FROM ahl_mc_relationships
57 WHERE relationship_id = p_uc_parent_rec.relationship_id;
58 CURSOR check_instance IS
59 SELECT 'x'
60 FROM csi_item_instances
61 WHERE instance_id = p_uc_parent_rec.instance_id;
62 CURSOR get_top_position(c_instance_id number) IS
63 SELECT B.relationship_id
64 FROM ahl_unit_config_headers A,
65 ahl_mc_relationships B
66 WHERE A.master_config_id = B.mc_header_id
67 AND B.parent_relationship_id IS NULL
68 AND A.csi_item_instance_id = c_instance_id;
69 -- get immediate children for installed node
70 CURSOR get_child_nodes_i(c_instance_id number, c_relationship_id number) IS
71 SELECT 'X' node_type, subject_id instance_id, NULL relationship_id
72 FROM csi_ii_relationships
73 WHERE object_id = c_instance_id
74 AND position_reference IS NULL
75 UNION
76 SELECT 'I' node_type, subject_id instance_id, to_number(position_reference) relationship_id
77 FROM csi_ii_relationships
78 WHERE object_id = c_instance_id
79 AND position_reference IS NOT NULL
80 AND nvl(trunc(active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE)
81 UNION
82 SELECT 'E' node_type, NULL instance_id, relationship_id
83 FROM ahl_mc_relationships
84 WHERE parent_relationship_id = c_relationship_id
85 AND relationship_id NOT IN (SELECT position_reference
86 FROM csi_ii_relationships
87 WHERE object_id = c_instance_id
88 AND position_reference IS NOT NULL
89 AND nvl(trunc(active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE));
90 l_get_child_nodes_i get_child_nodes_i%ROWTYPE;
91 -- get immediate children for empty node
92 CURSOR get_child_nodes_m(c_relationship_id number) IS
93 SELECT 'E' node_type, NULL instance_id, relationship_id
94 FROM ahl_mc_relationships
95 WHERE parent_relationship_id = c_relationship_id;
96 -- get immediate children for extra node
97 CURSOR get_child_nodes_x(c_instance_id number) IS
98 SELECT 'X' node_type, subject_id instance_id, to_number(position_reference) relationship_id
99 FROM csi_ii_relationships
100 WHERE object_id = c_instance_id
101 AND nvl(trunc(active_end_date), trunc(SYSDATE)+1) > trunc(SYSDATE);
102 BEGIN
103 -- Initialize API return status to success
104 x_return_status := FND_API.G_RET_STS_SUCCESS;
105
106 -- Standard call to check for call compatibility.
107 IF NOT FND_API.compatible_api_call(
108 l_api_version,
109 p_api_version,
110 l_api_name,
111 G_PKG_NAME)
112 THEN
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114 END IF;
115
116 -- Initialize message list if p_init_msg_list is set to TRUE.
117 IF FND_API.to_boolean( p_init_msg_list ) THEN
118 FND_MSG_PUB.initialize;
119 END IF;
120
121 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
122 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
123 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
124 'At the start of the procedure');
125 END IF;
126
127 -- Validate the input parameter
128 IF (p_uc_parent_rec.node_type IS NULL OR p_uc_parent_rec.node_type NOT IN ('I', 'E', 'X') OR
129 (p_uc_parent_rec.instance_id IS NULL AND p_uc_parent_rec.relationship_id IS NULL))THEN
130 FND_MESSAGE.set_name( 'AHL','AHL_UC_NODE_INVALID' );
131 FND_MSG_PUB.add;
132 END IF;
133 IF p_uc_parent_rec.node_type = 'I' THEN
134 IF p_uc_parent_rec.instance_id IS NULL OR p_uc_parent_rec.relationship_id IS NULL THEN
135 FND_MESSAGE.set_name( 'AHL','AHL_UC_NODE_INVALID' );
136 FND_MSG_PUB.add;
137 ELSE
138 OPEN check_installed_instance;
139 FETCH check_installed_instance INTO l_dummy;
140 IF check_installed_instance%NOTFOUND THEN
141 FND_MESSAGE.set_name( 'AHL','AHL_UC_NODE_INVALID' );
142 FND_MSG_PUB.add;
143 END IF;
144 CLOSE check_installed_instance;
145 END IF;
146 ELSIF p_uc_parent_rec.node_type = 'M' THEN
147 IF p_uc_parent_rec.instance_id IS NOT NULL OR p_uc_parent_rec.relationship_id IS NULL THEN
148 FND_MESSAGE.set_name( 'AHL','AHL_UC_NODE_INVALID' );
149 FND_MSG_PUB.add;
150 ELSE
151 OPEN check_relationship;
152 FETCH check_relationship INTO l_dummy;
153 IF check_relationship%NOTFOUND THEN
154 FND_MESSAGE.set_name( 'AHL','AHL_UC_NODE_INVALID' );
155 FND_MSG_PUB.add;
156 END IF;
157 CLOSE check_relationship;
158 END IF;
159 ELSIF p_uc_parent_rec.node_type = 'X' THEN
160 IF p_uc_parent_rec.instance_id IS NULL THEN
161 FND_MESSAGE.set_name( 'AHL','AHL_UC_NODE_INVALID' );
162 FND_MSG_PUB.add;
163 ELSE
164 OPEN check_instance;
165 FETCH check_instance INTO l_dummy;
166 IF check_instance%NOTFOUND THEN
167 FND_MESSAGE.set_name( 'AHL','AHL_UC_NODE_INVALID' );
168 FND_MSG_PUB.add;
169 END IF;
170 CLOSE check_instance;
171 END IF;
172 END IF;
173 -- If the node is a sub-UC's top node, then replace the relationship_id (leaf node
174 -- of its parent UC) with its own relationship_id (sub-UC's top node)
175 OPEN get_top_position(p_uc_parent_rec.instance_id);
176 FETCH get_top_position INTO l_relationship_id;
177 IF get_top_position%NOTFOUND THEN
178 l_relationship_id := p_uc_parent_rec.relationship_id;
179 END IF;
180 CLOSE get_top_position;
181 i := 1;
182 IF p_uc_parent_rec.node_type = 'I' THEN
183 FOR l_get_child_nodes IN get_child_nodes_i(p_uc_parent_rec.instance_id, l_relationship_id) LOOP
184 x_uc_child_tbl(i).node_type := l_get_child_nodes.node_type;
185 x_uc_child_tbl(i).instance_id := l_get_child_nodes.instance_id;
186 x_uc_child_tbl(i).relationship_id := l_get_child_nodes.relationship_id;
187 i := i+1;
188 END LOOP;
189 ELSIF p_uc_parent_rec.node_type = 'M' THEN
190 FOR l_get_child_nodes IN get_child_nodes_m(l_relationship_id) LOOP
191 x_uc_child_tbl(i).node_type := l_get_child_nodes.node_type;
192 x_uc_child_tbl(i).instance_id := l_get_child_nodes.instance_id;
193 x_uc_child_tbl(i).relationship_id := l_get_child_nodes.relationship_id;
194 i := i+1;
195 END LOOP;
196 ELSIF p_uc_parent_rec.node_type = 'X' THEN
197 FOR l_get_child_nodes IN get_child_nodes_x(p_uc_parent_rec.instance_id) LOOP
198 x_uc_child_tbl(i).node_type := l_get_child_nodes.node_type;
199 x_uc_child_tbl(i).instance_id := l_get_child_nodes.instance_id;
200 x_uc_child_tbl(i).relationship_id := l_get_child_nodes.relationship_id;
201 i := i+1;
202 END LOOP;
203 END IF;
204 IF x_uc_child_tbl.COUNT >0 THEN
205 FOR j IN x_uc_child_tbl.FIRST .. x_uc_child_tbl.LAST LOOP
206 IF x_uc_child_tbl(j).node_type = 'I' THEN
207 x_uc_child_tbl(j).has_subconfig_flag := 'N';
208 OPEN get_top_position(x_uc_child_tbl(j).instance_id);
209 FETCH get_top_position INTO l_relationship_id;
210 IF get_top_position%NOTFOUND THEN
211 l_relationship_id := x_uc_child_tbl(j).relationship_id;
212 END IF;
213 CLOSE get_top_position;
214 OPEN get_child_nodes_i(x_uc_child_tbl(j).instance_id, l_relationship_id);
215 FETCH get_child_nodes_i INTO l_get_child_nodes_i;
216 IF get_child_nodes_i%FOUND THEN
217 x_uc_child_tbl(j).leaf_node_flag := 'Y';
218 ELSE
219 x_uc_child_tbl(j).leaf_node_flag := 'N';
220 END IF;
221 ELSIF x_uc_child_tbl(j).node_type = 'M' THEN
222 SELECT count(relationship_id) INTO l_children_no
223 FROM ahl_mc_relationships
224 WHERE parent_relationship_id = x_uc_child_tbl(j).relationship_id;
225 IF l_children_no > 0 THEN
226 x_uc_child_tbl(j).has_subconfig_flag := 'N';
227 x_uc_child_tbl(j).leaf_node_flag := 'N';
228 ELSE
229 x_uc_child_tbl(j).leaf_node_flag := 'Y';
230 SELECT count(mc_header_id) INTO l_children_no
231 FROM ahl_mc_config_relations
232 WHERE relationship_id = x_uc_child_tbl(j).relationship_id;
233 IF l_children_no > 0 THEN
234 x_uc_child_tbl(j).has_subconfig_flag := 'Y';
235 ELSE
236 x_uc_child_tbl(j).has_subconfig_flag := 'N';
237 END IF;
238 END IF;
239 ELSIF x_uc_child_tbl(j).node_type = 'X' THEN
240 x_uc_child_tbl(j).has_subconfig_flag := 'N';
241 SELECT count(subject_id) INTO l_children_no
242 FROM csi_ii_relationships
243 WHERE object_id = x_uc_child_tbl(j).instance_id;
244 IF l_children_no > 0 THEN
245 x_uc_child_tbl(j).leaf_node_flag := 'Y';
246 ELSE
247 x_uc_child_tbl(j).leaf_node_flag := 'N';
248 END IF;
249 END IF;
250 END LOOP;
251 END IF;
252
253 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
254 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
255 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution',
256 'At the end of the procedure');
257 END IF;
258
259 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
260 l_msg_count := FND_MSG_PUB.count_msg;
261 IF l_msg_count > 0 THEN
262 x_msg_count := l_msg_count;
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265 -- Count and Get messages (optional)
266 FND_MSG_PUB.count_and_get(
267 p_encoded => FND_API.G_FALSE,
268 p_count => x_msg_count,
269 p_data => x_msg_data);
270
271 EXCEPTION
272 WHEN FND_API.G_EXC_ERROR THEN
273 x_return_status := FND_API.G_RET_STS_ERROR ;
274 FND_MSG_PUB.count_and_get(
275 p_encoded => FND_API.G_FALSE,
276 p_count => x_msg_count,
277 p_data => x_msg_data);
278
279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
281 FND_MSG_PUB.count_and_get(
282 p_encoded => FND_API.G_FALSE,
283 p_count => x_msg_count,
284 p_data => x_msg_data);
285
286 WHEN OTHERS THEN
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
288 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
289 THEN
290 FND_MSG_PUB.add_exc_msg(
291 p_pkg_name => G_PKG_NAME,
292 p_procedure_name => l_api_name,
293 p_error_text => SUBSTRB(SQLERRM,1,240));
294 END IF;
295 FND_MSG_PUB.count_and_get(
296 p_encoded => FND_API.G_FALSE,
297 p_count => x_msg_count,
298 p_data => x_msg_data);
299
300 END get_immediate_children;
301 */
302
303 PROCEDURE get_whole_uc_tree(
304 p_api_version IN NUMBER := 1.0,
305 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
306 --p_commit IN VARCHAR2 := FND_API.G_FALSE,
307 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
308 x_return_status OUT NOCOPY VARCHAR2,
309 x_msg_count OUT NOCOPY NUMBER,
310 x_msg_data OUT NOCOPY VARCHAR2,
311 p_uc_header_id IN NUMBER,
312 x_uc_descendant_tbl OUT NOCOPY uc_descendant_tbl_type)
313 IS
314 -- rbhavsar::FP Bug# 6268202, performance tuning
315 -- use PL/SQL tables for BULK COLLECT instead of looping through the cursor records
316 /*
317 TYPE l_uc_children_rec_type IS RECORD (
318 instance_id NUMBER,
319 relationship_id NUMBER,
320 matched_flag VARCHAR2(1));
321 TYPE l_uc_children_tbl_type IS TABLE OF l_uc_children_rec_type INDEX BY BINARY_INTEGER;*/
322
323 TYPE t_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
324 TYPE t_flag_tbl IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
325 TYPE t_partinfo_tbl IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
326
327 l_api_name CONSTANT VARCHAR2(30) := 'get_whole_uc_tree';
328 l_api_version CONSTANT NUMBER := 1.0;
329 l_return_status VARCHAR2(1);
330 l_msg_count NUMBER;
331 l_relationship_id NUMBER;
332 l_mc_header_id NUMBER;
333 l_root_instance_id NUMBER;
334 l_root_relationship_id NUMBER;
335 l_dummy VARCHAR2(1);
336 l_dummy_num NUMBER;
337 i NUMBER;
338 -- l_uc_children_tbl l_uc_children_tbl_type;
339 l_child_inst_tbl t_id_tbl;
340 l_child_rel_tbl t_id_tbl;
341 l_child_matchflag_tbl t_flag_tbl;
342 l_child_partinfo_tbl t_partinfo_tbl;
343 l_pos_ref FND_LOOKUPS.meaning%TYPE;
344 l_root_mc_hdr_id NUMBER;
345 l_root_mc_part BOOLEAN;
346 l_root_ata_code AHL_MC_RELATIONSHIPS.ATA_CODE%TYPE; -- SATHAPLI::Enigma code changes, 02-Sep-2008
347
348 l_matched boolean;
349 total_i NUMBER; --index of output table
350 i_uc_child NUMBER; --index of MC siblings
351 l_uc_header_id NUMBER;
352
353 --Check the given uc_heder_id is valid and if yes, get the UC top node's instance_id
354 --and relationship_id
355 -- rbhavsar::FP Bug# 6268202, performance tuning
356 -- fetch the root UC's mc_header_id too
357 -- SATHAPLI::Enigma code changes, 02-Sep-2008 - fetch the ata_code as well
358 CURSOR get_uc_header_attr(c_uc_header_id NUMBER) IS
359 SELECT A.csi_item_instance_id instance_id,
360 A.master_config_id,
361 B.relationship_id,
362 B.ata_code
363 FROM ahl_unit_config_headers A,
364 ahl_mc_relationships B
365 WHERE A.unit_config_header_id = c_uc_header_id
366 AND A.master_config_id = B.mc_header_id
367 AND B.parent_relationship_id IS NULL;
368
369 --Given an instance_id, get all of its immediate children from csi_ii_relationships
370 -- rbhavsar::FP Bug# 6268202, performance tuning
371 -- Object_id is not used in the code, so removing it.
372 -- Also, no need for ORDER BY for extra nodes.
373 CURSOR get_csi_children(c_instance_id NUMBER) IS
374 SELECT R.subject_id instance_id,
375 to_number(R.position_reference) relationship_id,
376 M.concatenated_segments||'-'||NVL(C.serial_number, C.instance_number) part_info,
377 'N'
378 FROM csi_ii_relationships R,
379 csi_item_instances C,
380 mtl_system_items_kfv M
381 WHERE R.object_id = c_instance_id
382 AND R.subject_id = C.instance_id
383 AND C.inventory_item_id = M.inventory_item_id
384 AND C.inv_master_organization_id = M.organization_id
385 AND R.relationship_type_code = 'COMPONENT-OF'
386 AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
387 AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
388
389 -- rbhavsar::FP Bug# 6268202, performance tuning
390 -- cursor to check if the instance is leaf node or not
391 CURSOR chk_csi_leaf_node_csr(c_instance_id NUMBER) IS
392 SELECT 'X'
393 FROM csi_ii_relationships
394 WHERE object_id = c_instance_id
395 AND relationship_type_code = 'COMPONENT-OF'
396 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
397 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
398
399 --Given an relationship_id, get all of its immediate children from MC
400 --display_order will determine the sequence of installed and empty sibling nodes
401 -- rbhavsar::FP Bug# 6268202, performance tuning
402 -- Fetch the mc_header_id.
403 -- Fetch the position reference meaning and necessity from fnd_lookups too.
404 -- SATHAPLI::Enigma code changes, 02-Sep-2008 - fetch the ata_code as well
405 CURSOR get_mc_children(c_relationship_id NUMBER) IS
406 SELECT rel.mc_header_id,
407 rel.parent_relationship_id parent_rel_id,
408 rel.relationship_id,
409 rel.display_order,
410 rel.ata_code,
411 (SELECT fnd.meaning
412 FROM fnd_lookups fnd
413 WHERE fnd.lookup_type = 'AHL_POSITION_REFERENCE'
414 AND fnd.lookup_code = rel.position_ref_code
415 AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
416 AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
417 ) pos_ref_meaning,
418 (SELECT fnd.meaning
419 FROM fnd_lookups fnd
420 WHERE fnd.lookup_type = 'AHL_POSITION_NECESSITY'
421 AND fnd.lookup_code = rel.position_necessity_code
422 AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
423 AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
424 ) pos_necessity
425 FROM ahl_mc_relationships rel
426 WHERE rel.parent_relationship_id = c_relationship_id
427 AND trunc(nvl(rel.active_start_date,SYSDATE)) <= trunc(SYSDATE)
428 AND trunc(nvl(rel.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
429 ORDER BY display_order;
430
431 -- rbhavsar::FP Bug# 6268202, performance tuning
432 -- cursor to check if the position is leaf node or not
433 CURSOR chk_mc_leaf_node_csr(c_relationship_id NUMBER) IS
434 SELECT 'X'
435 FROM ahl_mc_relationships
436 WHERE parent_relationship_id = c_relationship_id
437 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
438 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
439
440 --Given an relationship_id, get all of its descendants from MC
441 -- rbhavsar::FP Bug# 6268202, performance tuning
442 -- fetch the position reference meaning and necessity from fnd_lookups too
443 -- SATHAPLI::Enigma code changes, 02-Sep-2008 - fetch the ata_code as well
444 CURSOR get_mc_descendants(c_relationship_id NUMBER) IS
445 SELECT rel.parent_relationship_id parent_rel_id,
446 rel.relationship_id,
447 rel.display_order,
448 rel.ata_code,
449 (SELECT fnd.meaning
450 FROM fnd_lookups fnd
451 WHERE fnd.lookup_type = 'AHL_POSITION_REFERENCE'
452 AND fnd.lookup_code = rel.position_ref_code
453 AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
454 AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
455 ) pos_ref_meaning,
456 (SELECT fnd.meaning
457 FROM fnd_lookups fnd
458 WHERE fnd.lookup_type = 'AHL_POSITION_NECESSITY'
459 AND fnd.lookup_code = rel.position_necessity_code
460 AND trunc(nvl(fnd.start_date_active, SYSDATE)) <= trunc(SYSDATE)
461 AND trunc(nvl(fnd.end_date_active, SYSDATE+1)) > trunc(SYSDATE)
462 ) pos_necessity
463 FROM ahl_mc_relationships rel
464 START WITH rel.parent_relationship_id = c_relationship_id
465 AND trunc(nvl(rel.active_start_date,SYSDATE)) <= trunc(SYSDATE)
466 AND trunc(nvl(rel.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
467 CONNECT BY rel.parent_relationship_id = PRIOR rel.relationship_id
468 AND trunc(nvl(rel.active_start_date,SYSDATE)) <= trunc(SYSDATE)
469 AND trunc(nvl(rel.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
470 ORDER BY LEVEL, display_order;
471 -- keyword SIBLINGS is not supported in 8i, so we replace it with level
472 -- ORDER SIBLINGS BY display_order;
473
474
475 --Check whether an instance in UC is the top node of a sub UC, if yes, then get the
476 --relationship of the sub UC's top node
477 CURSOR check_sub_uc(c_instance_id NUMBER) IS
478 SELECT A.unit_config_header_id,
479 A.master_config_id,
480 B.relationship_id
481 FROM ahl_unit_config_headers A,
482 ahl_mc_relationships B
483 WHERE A.csi_item_instance_id = c_instance_id
484 AND trunc(nvl(A.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
485 AND B.mc_header_id = A.master_config_id
486 AND B.parent_relationship_id IS NULL
487 AND trunc(nvl(B.active_start_date,SYSDATE)) <= trunc(SYSDATE)
488 AND trunc(nvl(B.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
489
490 -- rbhavsar:: FP Bug# 6268202, performance tuning
491 -- check if the MC has sub MCs or not
492 CURSOR has_sub_mc_csr(c_relationship_id NUMBER) IS
493 SELECT 'X'
494 FROM ahl_mc_config_relations
495 WHERE relationship_id = c_relationship_id
496 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
497 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
498 AND rownum = 1;
499
500 -- rbhavsar:: FP Bug# 6268202, performance tuning
501 -- Cursor to get the position reference for a given relationship id.
502 CURSOR get_pos_ref_csr(c_relationship_id NUMBER) IS
503 SELECT fnd.meaning
504 FROM ahl_mc_relationships rel, fnd_lookups fnd
505 WHERE rel.relationship_id = c_relationship_id AND
506 fnd.lookup_code = rel.position_ref_code AND
507 fnd.lookup_type = 'AHL_POSITION_REFERENCE' AND
508 TRUNC(NVL(fnd.start_date_active, SYSDATE)) <= TRUNC(SYSDATE) AND
509 TRUNC(NVL(fnd.end_date_active, SYSDATE+1)) > TRUNC(SYSDATE);
510
511 --Based on the UC, list all of the non leaf nodes(including the root node) and the leaf nodes
512 --in UC but are non-leaf nodes in MC
513 -- rbhavsar::FP Bug# 6268202, performance tuning
514 CURSOR get_non_leaf_nodes(c_instance_id NUMBER) IS
515 SELECT TO_NUMBER(NULL) PARENT_INSTANCE_ID,
516 B.CSI_ITEM_INSTANCE_ID INSTANCE_ID,
517 A.RELATIONSHIP_ID,
518 'I' NODE_TYPE,
519 0 OWN_LEVEL
520 FROM AHL_UNIT_CONFIG_HEADERS B,
521 AHL_MC_RELATIONSHIPS A
522 WHERE B.UNIT_CONFIG_HEADER_ID = p_uc_header_id /*UC header id*/
523 AND B.CSI_ITEM_INSTANCE_ID = c_instance_id /*root instance id*/
524 AND A.MC_HEADER_ID = B.MASTER_CONFIG_ID
525 AND A.PARENT_RELATIONSHIP_ID IS NULL
526 UNION ALL
527 SELECT OBJECT_ID PARENT_INSTANCE_ID,
528 SUBJECT_ID INSTANCE_ID,
529 TO_NUMBER(POSITION_REFERENCE) RELATIONSHIP_ID,
530 DECODE(POSITION_REFERENCE, NULL, 'X', 'I') NODE_TYPE,
531 LEVEL OWN_LEVEL
532 FROM CSI_II_RELATIONSHIPS A
533 WHERE (
534 EXISTS (SELECT 'x'
535 FROM CSI_II_RELATIONSHIPS B
536 WHERE B.OBJECT_ID = A.SUBJECT_ID
537 AND B.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
538 AND TRUNC(NVL(B.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
539 AND TRUNC(NVL(B.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
540 )
541 )
542 OR
543 (
544 EXISTS (SELECT 'x'
545 FROM AHL_MC_RELATIONSHIPS D
546 WHERE D.PARENT_RELATIONSHIP_ID = TO_NUMBER(A.POSITION_REFERENCE)
547 AND TRUNC(NVL(D.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
548 AND TRUNC(NVL(D.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
549 )
550 OR
551 EXISTS (SELECT 'x'
552 FROM AHL_MC_RELATIONSHIPS D
553 WHERE D.RELATIONSHIP_ID = TO_NUMBER(A.POSITION_REFERENCE)
554 AND EXISTS (SELECT 'x'
555 FROM AHL_UNIT_CONFIG_HEADERS E
556 WHERE CSI_ITEM_INSTANCE_ID = A.SUBJECT_ID
557 AND TRUNC(NVL(E.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
558 )
559 AND TRUNC(NVL(D.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
560 AND TRUNC(NVL(D.ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
561 )
562 )
563 START WITH OBJECT_ID = c_instance_id /*root instance id*/
564 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
565 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
566 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
567 CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
568 AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
569 AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
570 AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
571 ORDER BY OWN_LEVEL, NODE_TYPE;
572
573 l_get_csi_child get_csi_children%ROWTYPE;
574 l_get_mc_child get_mc_children%ROWTYPE;
575
576 --Function to get position_necessity meaning for a given relationship_id
577 FUNCTION position_necessity(p_relationship_id NUMBER) RETURN VARCHAR2 IS
578 l_pos_necessity_meaning fnd_lookups.meaning%TYPE;
579 CURSOR get_position_necessity(c_relationship_id NUMBER) IS
580 SELECT F.meaning
581 FROM ahl_mc_relationships A,
582 fnd_lookup_values_vl F
583 WHERE A.relationship_id = c_relationship_id
584 AND A.position_necessity_code = F.lookup_code (+)
585 AND F.lookup_type (+) = 'AHL_POSITION_NECESSITY';
586 BEGIN
587 IF p_relationship_id IS NULL THEN
588 l_pos_necessity_meaning := NULL;
589 ELSE
590 OPEN get_position_necessity(p_relationship_id);
591 FETCH get_position_necessity INTO l_pos_necessity_meaning;
592 IF get_position_necessity%NOTFOUND THEN
593 FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_INVALID' );
594 FND_MESSAGE.set_token('POSITION', p_relationship_id);
595 FND_MSG_PUB.add;
596 CLOSE get_position_necessity;
597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
598 ELSE
599 CLOSE get_position_necessity;
600 END IF;
601 END IF;
602 RETURN l_pos_necessity_meaning;
603 END;
604
605 --Function to get instance information(part number plus serial number or
606 --instance number for a given instance_id
607 FUNCTION part_info(p_instance_id NUMBER) RETURN VARCHAR2 IS
608 l_part_info VARCHAR2(80);
609 CURSOR get_part_info(c_instance_id NUMBER) IS
610 SELECT M.concatenated_segments||'-'||NVL(C.serial_number, C.instance_number) part_info
611 FROM mtl_system_items_kfv M,
612 csi_item_instances C
613 WHERE C.instance_id = c_instance_id
614 AND C.inventory_item_id = M.inventory_item_id
615 AND C.inv_master_organization_id = M.organization_id;
616 BEGIN
617 OPEN get_part_info(p_instance_id);
618 FETCH get_part_info INTO l_part_info;
619 IF get_part_info%NOTFOUND THEN
620 FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_INVALID' );
621 FND_MESSAGE.set_token('INSTANCE', p_instance_id);
622 FND_MSG_PUB.add;
623 CLOSE get_part_info;
624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
625 ELSE
626 CLOSE get_part_info;
627 END IF;
628 RETURN l_part_info;
629 END;
630
631
632 BEGIN
633 --Initialize API return status to success
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636 --Standard call to check for call compatibility.
637 IF NOT FND_API.compatible_api_call(
638 l_api_version,
639 p_api_version,
640 l_api_name,
641 G_PKG_NAME)
642 THEN
643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
644 END IF;
645 --Initialize message list if p_init_msg_list is set to TRUE.
646 IF FND_API.to_boolean( p_init_msg_list ) THEN
647 FND_MSG_PUB.initialize;
648 END IF;
649
650 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
651 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
652 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
653 'At the start of the procedure');
654 END IF;
655 --Validate the input parameter and get the root instance_id and relationship_id
656 -- SATHAPLI::Enigma code changes, 02-Sep-2008 - fetch the ata_code as well
657 OPEN get_uc_header_attr(p_uc_header_id);
658 FETCH get_uc_header_attr INTO l_root_instance_id, l_root_mc_hdr_id, l_root_relationship_id, l_root_ata_code;
659 IF get_uc_header_attr%NOTFOUND THEN
660 FND_MESSAGE.set_name( 'AHL','AHL_UC_HEADER_ID_INVALID' );
661 FND_MESSAGE.set_token('UC_HEADER_ID', p_uc_header_id);
662 FND_MSG_PUB.add;
663 CLOSE get_uc_header_attr;
664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
665 ELSE
666 CLOSE get_uc_header_attr;
667 END IF;
668
669 --Initialize l_uc_header_id
670 l_uc_header_id := p_uc_header_id;
671
672 --add the root node to the output table
673 total_i := 1; --index of the output table
674 x_uc_descendant_tbl(total_i).instance_id := l_root_instance_id;
675 x_uc_descendant_tbl(total_i).relationship_id := l_root_relationship_id;
676 x_uc_descendant_tbl(total_i).parent_instance_id := NULL;
677 x_uc_descendant_tbl(total_i).parent_rel_id := NULL;
678 x_uc_descendant_tbl(total_i).node_type := 'I';
679 x_uc_descendant_tbl(total_i).leaf_node_flag := 'N'; --might be 'Y' very seldomly?
680 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'N'; --not applicable for root node
681
682 -- SATHAPLI::Enigma code changes, 02-Sep-2008 - populate the ata_code for the root
683 x_uc_descendant_tbl(total_i).ata_code := l_root_ata_code;
684
685 -- rbhavsar::FP Bug# 6268202, performance tuning
686 -- API ahl_mc_path_position_pvt.get_posref_for_uc need not be called for root
687 -- x_uc_descendant_tbl(total_i).position_reference := ahl_mc_path_position_pvt.get_posref_for_uc(l_uc_header_id, l_root_relationship_id);
688 OPEN get_pos_ref_csr(l_root_relationship_id);
689 FETCH get_pos_ref_csr INTO l_pos_ref;
690 CLOSE get_pos_ref_csr;
691 x_uc_descendant_tbl(total_i).position_reference := l_pos_ref;
692
693 --Even if p_uc_header_id is a subunit(installed or extra), when displaying its own tree, we pass its own
694 --p_uc_header_id instead of its parent_uc_header_id
695 x_uc_descendant_tbl(total_i).position_necessity := position_necessity(l_root_relationship_id);
696 x_uc_descendant_tbl(total_i).part_info := part_info(l_root_instance_id);
697 total_i := total_i + 1;
698
699 --Loop through all of the non leaf nodes of the UC tree, including the root node and extra node
700 FOR l_non_leaf_node IN get_non_leaf_nodes(l_root_instance_id) LOOP
701
702 --dbms_output.put_line('rel_id='||l_non_leaf_node.relationship_id||' uc_instance='||l_root_instance_id);
703 IF (l_non_leaf_node.relationship_id IS NOT NULL AND
704 NOT ahl_util_uc_pkg.extra_node(l_non_leaf_node.instance_id, l_root_instance_id)) THEN
705
706 --If the UC instance happens to be the top node of a sub UC, then when getting the
707 --MC descendants, the sub UC top node's relationship_id(got from uc headers table)
708 --instead of the relationship_id(got from position_reference in csi_ii_relationships)
709 --should be used. For UC root instance, the following cursor is also applicable.
710 OPEN check_sub_uc(l_non_leaf_node.instance_id);
711 FETCH check_sub_uc INTO l_uc_header_id, l_mc_header_id, l_relationship_id;
712
713 IF check_sub_uc%NOTFOUND THEN --not a sub UC's top node
714 l_relationship_id := l_non_leaf_node.relationship_id;
715 ahl_util_uc_pkg.get_parent_uc_header(l_non_leaf_node.instance_id, l_uc_header_id, l_dummy_num);
716
717 IF l_uc_header_id IS NULL THEN
718 l_uc_header_id := p_uc_header_id;
719 END IF;
720 END IF;
721 CLOSE check_sub_uc;
722
723 --Get all the immediate children of the corresponding UC node
724
725 --This is used to reset the l_uc_children_tbl and it is required, otherwise the elements
726 --left in the previous loop especially when the previous loop has more elements than the
727 --current loop, then these elements belonging to the previous loop will be inherited to
728 --the current loop.
729 -- rbhavsar::FP Bug# 6268202, performance tuning
730 -- use PL/SQL tables for BULK COLLECT instead of looping through the cursor records
731 /*
732 IF l_uc_children_tbl.COUNT > 0 THEN
733 l_uc_children_tbl.DELETE;
734 END IF;
735
736
737 i_uc_child := 0;
738 FOR l_get_csi_child IN get_csi_children(l_non_leaf_node.instance_id) LOOP
739 i_uc_child := i_uc_child + 1;
740 l_uc_children_tbl(i_uc_child).instance_id := l_get_csi_child.instance_id;
741 l_uc_children_tbl(i_uc_child).relationship_id := l_get_csi_child.relationship_id;
742 l_uc_children_tbl(i_uc_child).matched_flag := 'N'; -- default to 'N'
743 END LOOP;
744 */
745
746 IF l_child_inst_tbl.COUNT > 0 THEN
747 l_child_inst_tbl.DELETE;
748 l_child_rel_tbl.DELETE;
749 l_child_matchflag_tbl.DELETE;
750 l_child_partinfo_tbl.DELETE;
751 END IF;
752
753 OPEN get_csi_children(l_non_leaf_node.instance_id);
754 FETCH get_csi_children BULK COLLECT INTO l_child_inst_tbl,
755 l_child_rel_tbl,
756 l_child_partinfo_tbl,
757 l_child_matchflag_tbl;
758 CLOSE get_csi_children;
759
760 --Loop through all the immediate children of the MC node
761 FOR l_get_mc_child IN get_mc_children(l_relationship_id) LOOP
762 l_matched := FALSE;
763 IF l_child_inst_tbl.COUNT > 0 THEN
764 FOR i IN l_child_inst_tbl.FIRST..l_child_inst_tbl.LAST LOOP
765 IF (l_child_matchflag_tbl(i) = 'N' AND
766 l_get_mc_child.relationship_id = l_child_rel_tbl(i)) THEN
767 --Add these installed nodes to the output table
768 x_uc_descendant_tbl(total_i).instance_id := l_child_inst_tbl(i);
769 x_uc_descendant_tbl(total_i).relationship_id := l_child_rel_tbl(i);
770 x_uc_descendant_tbl(total_i).parent_instance_id := l_non_leaf_node.instance_id;
771 x_uc_descendant_tbl(total_i).parent_rel_id := l_non_leaf_node.relationship_id;
772 x_uc_descendant_tbl(total_i).node_type := 'I';
773
774 -- SATHAPLI::Enigma code changes, 02-Sep-2008 - populate the ata_code for the non-extra node
775 x_uc_descendant_tbl(total_i).ata_code := l_get_mc_child.ata_code;
776
777 -- rbhavsar::FP Bug# 6268202, performance tuning
778 -- API ahl_mc_path_position_pvt.get_posref_for_uc need not be called if
779 -- this relationship belongs to the root MC.
780 IF (l_get_mc_child.mc_header_id = l_root_mc_hdr_id) THEN
781 x_uc_descendant_tbl(total_i).position_reference := l_get_mc_child.pos_ref_meaning;
782 ELSE
783 x_uc_descendant_tbl(total_i).position_reference :=
784 ahl_mc_path_position_pvt.get_posref_for_uc(l_uc_header_id, l_child_rel_tbl(i));
785 END IF;
786
787 x_uc_descendant_tbl(total_i).position_necessity := l_get_mc_child.pos_necessity;
788 x_uc_descendant_tbl(total_i).part_info := l_child_partinfo_tbl(i);
789
790 --Leaf_node_flag is for front end display purpose, so we have to
791 --First check to see wether this installed node is a leaf node in CSI, if it is
792 --then we need to check whether the corresponding relationship_id is a leaf node
793 --in MC. Again we need to check whether this installed node happens to be a sub UC top node.
794 OPEN check_sub_uc(x_uc_descendant_tbl(total_i).instance_id);
795 FETCH check_sub_uc INTO l_dummy_num, l_mc_header_id, l_relationship_id;
796 IF check_sub_uc%NOTFOUND THEN --not a sub UC's top node
797 l_relationship_id := x_uc_descendant_tbl(total_i).relationship_id;
798 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'N';
799 ELSE
800 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'Y';
801 END IF;
802 CLOSE check_sub_uc;
803
804 -- rbhavsar::FP Bug# 6268202, performance tuning
805 OPEN chk_csi_leaf_node_csr(x_uc_descendant_tbl(total_i).instance_id);
806 FETCH chk_csi_leaf_node_csr INTO l_dummy;
807 IF chk_csi_leaf_node_csr%FOUND THEN
808 x_uc_descendant_tbl(total_i).leaf_node_flag := 'N';
809 ELSE
810 -- check from the MC
811 OPEN chk_mc_leaf_node_csr(l_relationship_id);
812 FETCH chk_mc_leaf_node_csr INTO l_dummy;
813 IF chk_mc_leaf_node_csr%FOUND THEN
814 x_uc_descendant_tbl(total_i).leaf_node_flag := 'N';
815 ELSE
816 x_uc_descendant_tbl(total_i).leaf_node_flag := 'Y';
817 END IF;
818 CLOSE chk_mc_leaf_node_csr;
819 END IF;
820 CLOSE chk_csi_leaf_node_csr;
821
822 total_i := total_i + 1;
823 l_matched := TRUE;
824 l_child_matchflag_tbl(i) := 'Y';
825 EXIT;
826 END IF; --whether relationship_id match condition
827 END LOOP; --l_uc_children_tbl
828 END IF; --whether table l_uc_children_tbl is empty
829
830 --dbms_output.put_line('Not installed node...');
831 IF NOT l_matched THEN -- empty node
832 --Add this empty node and all of its descendants to the output table
833 --dbms_output.put_line('i='||to_char(i)||':l_mc_children_table(i).installed_flag='||l_mc_children_tbl(i).installed_flag);
834 x_uc_descendant_tbl(total_i).instance_id := NULL;
835 x_uc_descendant_tbl(total_i).relationship_id := l_get_mc_child.relationship_id;
836 x_uc_descendant_tbl(total_i).parent_instance_id := l_non_leaf_node.instance_id;
837 x_uc_descendant_tbl(total_i).parent_rel_id := l_non_leaf_node.relationship_id;
838 x_uc_descendant_tbl(total_i).node_type := 'E';
839
840 -- SATHAPLI::Enigma code changes, 02-Sep-2008 - populate the ata_code for the non-extra node
841 x_uc_descendant_tbl(total_i).ata_code := l_get_mc_child.ata_code;
842
843 -- rbhavsar::FP Bug# 6268202, performance tuning
844 -- API ahl_mc_path_position_pvt.get_posref_for_uc need not be called if
845 -- this relationship belongs to the root MC.
846 IF (l_get_mc_child.mc_header_id = l_root_mc_hdr_id) THEN
847 l_root_mc_part := TRUE;
848 x_uc_descendant_tbl(total_i).position_reference := l_get_mc_child.pos_ref_meaning;
849 ELSE
850 l_root_mc_part := FALSE;
851 x_uc_descendant_tbl(total_i).position_reference :=
852 ahl_mc_path_position_pvt.get_posref_for_uc(l_uc_header_id, l_get_mc_child.relationship_id);
853 END IF;
854
855 x_uc_descendant_tbl(total_i).position_necessity := l_get_mc_child.pos_necessity;
856 x_uc_descendant_tbl(total_i).part_info := NULL;
857
858 -- rbhavsar::FP Bug# 6268202, performance tuning
859 OPEN chk_mc_leaf_node_csr(x_uc_descendant_tbl(total_i).relationship_id);
860 FETCH chk_mc_leaf_node_csr INTO l_dummy;
861 IF chk_mc_leaf_node_csr%FOUND THEN
862 x_uc_descendant_tbl(total_i).leaf_node_flag := 'N';
863 ELSE
864 x_uc_descendant_tbl(total_i).leaf_node_flag := 'Y';
865 END IF;
866 CLOSE chk_mc_leaf_node_csr;
867
868 OPEN has_sub_mc_csr(x_uc_descendant_tbl(total_i).relationship_id);
869 FETCH has_sub_mc_csr INTO l_dummy;
870 IF has_sub_mc_csr%FOUND THEN
871 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'Y';
872 ELSE
873 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'N';
874 END IF;
875 CLOSE has_sub_mc_csr;
876
877 total_i := total_i + 1;
878 FOR l_get_mc_descendant IN get_mc_descendants(l_get_mc_child.relationship_id) LOOP
879 x_uc_descendant_tbl(total_i).instance_id := NULL;
880 x_uc_descendant_tbl(total_i).relationship_id := l_get_mc_descendant.relationship_id;
881
882 --Changed on 02/04/2004 per Cheng's requirement. Actually using parent_instance_id to
883 --represent the lowest ancestor instance for the empty node
884 x_uc_descendant_tbl(total_i).parent_instance_id := l_non_leaf_node.instance_id;
885 --x_uc_descendant_tbl(total_i).parent_instance_id := NULL;
886
887 x_uc_descendant_tbl(total_i).parent_rel_id := l_get_mc_descendant.parent_rel_id;
888 x_uc_descendant_tbl(total_i).node_type := 'E';
889
890 -- SATHAPLI::Enigma code changes, 02-Sep-2008 - populate the ata_code for the non-extra node
891 x_uc_descendant_tbl(total_i).ata_code := l_get_mc_descendant.ata_code;
892
893 -- rbhavsar::FP Bug# 6268202, performance tuning
894 -- API ahl_mc_path_position_pvt.get_posref_for_uc need not be called if
895 -- the ancestor relationship belongs to the root MC.
896 IF (l_root_mc_part) THEN
897 x_uc_descendant_tbl(total_i).position_reference := l_get_mc_descendant.pos_ref_meaning;
898 ELSE
899 x_uc_descendant_tbl(total_i).position_reference :=
900 ahl_mc_path_position_pvt.get_posref_for_uc(l_uc_header_id, l_get_mc_descendant.relationship_id);
901 END IF;
902
903 x_uc_descendant_tbl(total_i).position_necessity := l_get_mc_descendant.pos_necessity;
904 x_uc_descendant_tbl(total_i).part_info := NULL;
905
906 -- rbhavsar::FP Bug# 6268202, performance tuning
907 OPEN chk_mc_leaf_node_csr(x_uc_descendant_tbl(total_i).relationship_id);
908 FETCH chk_mc_leaf_node_csr INTO l_dummy;
909 IF chk_mc_leaf_node_csr%FOUND THEN
910 x_uc_descendant_tbl(total_i).leaf_node_flag := 'N';
911 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'N';
912 ELSE
913 x_uc_descendant_tbl(total_i).leaf_node_flag := 'Y';
914 --Only if it is empty leaf node in MC then it is necessary to check whether there
915 --are some sub MCs can be associated to this node
916 OPEN has_sub_mc_csr(x_uc_descendant_tbl(total_i).relationship_id);
917 FETCH has_sub_mc_csr INTO l_dummy;
918 IF has_sub_mc_csr%FOUND THEN
919 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'Y';
920 ELSE
921 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'N';
922 END IF;
923 CLOSE has_sub_mc_csr;
924 END IF;
925 CLOSE chk_mc_leaf_node_csr;
926
927 total_i := total_i + 1;
928 END LOOP; --loop of get_mc_descendants
929 END IF; --l_matched condition
930 END LOOP; --loop of get_mc_children
931
932 --dbms_output.put_line('After empty node...');
933 --Check each instance in the temporary UC children table, if it is not matched,
934 --then it is an extra node. Add this extra node's immediate children to the output table
935 IF l_child_inst_tbl.COUNT > 0 THEN
936 FOR i IN l_child_inst_tbl.FIRST..l_child_inst_tbl.LAST LOOP
937 IF l_child_matchflag_tbl(i) = 'N' THEN
938 x_uc_descendant_tbl(total_i).instance_id := l_child_inst_tbl(i);
939 x_uc_descendant_tbl(total_i).relationship_id := l_child_rel_tbl(i);
940 x_uc_descendant_tbl(total_i).parent_instance_id := l_non_leaf_node.instance_id;
941 x_uc_descendant_tbl(total_i).parent_rel_id := l_non_leaf_node.relationship_id;
942 x_uc_descendant_tbl(total_i).node_type := 'X';
943 x_uc_descendant_tbl(total_i).position_reference := NULL;
944 -- ahl_mc_path_position_pvt.get_posref_for_uc(l_uc_header_id, l_uc_children_tbl(i).relationship_id);
945 x_uc_descendant_tbl(total_i).position_necessity := NULL;
946 x_uc_descendant_tbl(total_i).part_info := l_child_partinfo_tbl(i);
947
948 -- rbhavsar::FP Bug# 6268202, performance tuning
949 OPEN chk_csi_leaf_node_csr(x_uc_descendant_tbl(total_i).instance_id);
950 FETCH chk_csi_leaf_node_csr INTO l_dummy;
951 IF chk_csi_leaf_node_csr%FOUND THEN
952 x_uc_descendant_tbl(total_i).leaf_node_flag := 'N';
953 ELSE
954 x_uc_descendant_tbl(total_i).leaf_node_flag := 'Y';
955 END IF;
956 CLOSE chk_csi_leaf_node_csr;
957
958 OPEN check_sub_uc(x_uc_descendant_tbl(total_i).instance_id);
959 FETCH check_sub_uc INTO l_uc_header_id, l_mc_header_id, l_relationship_id;
960 IF check_sub_uc%NOTFOUND THEN --not a sub UC's top node
961 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'N';
962 ELSE
963 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'Y';
964 END IF;
965 CLOSE check_sub_uc;
966
967 total_i := total_i + 1;
968 END IF; --matched_flag condition
969 END LOOP; --loop of l_uc_children_tbl
970 END IF; --whether table l_uc_children_tbl is empty
971
972 ELSE --an extra node in all non leaf nodes
973 --add this node's immediate children instead of itself into the output table,
974 --because this node has already been added into the output table when processing its
975 --parent node.
976 FOR l_get_csi_child IN get_csi_children(l_non_leaf_node.instance_id) LOOP
977 x_uc_descendant_tbl(total_i).node_type := 'X';
978 x_uc_descendant_tbl(total_i).instance_id := l_get_csi_child.instance_id;
979 x_uc_descendant_tbl(total_i).relationship_id := l_get_csi_child.relationship_id;
980 x_uc_descendant_tbl(total_i).parent_instance_id := l_non_leaf_node.instance_id;
981 x_uc_descendant_tbl(total_i).parent_rel_id := NULL;
982 x_uc_descendant_tbl(total_i).position_reference := NULL;
983 -- ahl_mc_path_position_pvt.get_posref_for_uc(l_uc_header_id, l_get_csi_child.relationship_id);
984 x_uc_descendant_tbl(total_i).position_necessity := NULL;
985 x_uc_descendant_tbl(total_i).part_info := l_get_csi_child.part_info;
986
987 -- rbhavsar::FP Bug# 6268202, performance tuning
988 OPEN chk_csi_leaf_node_csr(x_uc_descendant_tbl(total_i).instance_id);
989 FETCH chk_csi_leaf_node_csr INTO l_dummy;
990 IF chk_csi_leaf_node_csr%FOUND THEN
991 x_uc_descendant_tbl(total_i).leaf_node_flag := 'N';
992 ELSE
993 x_uc_descendant_tbl(total_i).leaf_node_flag := 'Y';
994 END IF;
995 CLOSE chk_csi_leaf_node_csr;
996
997 OPEN check_sub_uc(x_uc_descendant_tbl(total_i).instance_id);
998 FETCH check_sub_uc INTO l_uc_header_id, l_mc_header_id, l_relationship_id;
999 IF check_sub_uc%NOTFOUND THEN --not a sub UC's top node
1000 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'N';
1001 ELSE
1002 x_uc_descendant_tbl(total_i).has_subconfig_flag := 'Y';
1003 END IF;
1004 CLOSE check_sub_uc;
1005
1006 total_i := total_i + 1;
1007 END LOOP;
1008 END IF; --whether l_non_leaf_node is extra node
1009 END LOOP; --loop of get_non_leaf_nodes
1010
1011 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1012 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1013 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution',
1014 'At the end of the procedure');
1015 END IF;
1016 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1017 l_msg_count := FND_MSG_PUB.count_msg;
1018 IF l_msg_count > 0 THEN
1019 x_msg_count := l_msg_count;
1020 RAISE FND_API.G_EXC_ERROR;
1021 END IF;
1022 -- Count and Get messages (optional)
1023 FND_MSG_PUB.count_and_get(
1024 p_encoded => FND_API.G_FALSE,
1025 p_count => x_msg_count,
1026 p_data => x_msg_data);
1027
1028 EXCEPTION
1029 WHEN FND_API.G_EXC_ERROR THEN
1030 x_return_status := FND_API.G_RET_STS_ERROR ;
1031 FND_MSG_PUB.count_and_get(
1032 p_encoded => FND_API.G_FALSE,
1033 p_count => x_msg_count,
1034 p_data => x_msg_data);
1035
1036 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1038 FND_MSG_PUB.count_and_get(
1039 p_encoded => FND_API.G_FALSE,
1040 p_count => x_msg_count,
1041 p_data => x_msg_data);
1042
1043 WHEN OTHERS THEN
1044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1045 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1046 THEN
1047 FND_MSG_PUB.add_exc_msg(
1048 p_pkg_name => G_PKG_NAME,
1049 p_procedure_name => l_api_name,
1050 p_error_text => SUBSTRB(SQLERRM,1,240));
1051 END IF;
1052 FND_MSG_PUB.count_and_get(
1053 p_encoded => FND_API.G_FALSE,
1054 p_count => x_msg_count,
1055 p_data => x_msg_data);
1056 END;
1057
1058 -- Define Procedure migrate_uc_tree --
1059 -- This API is used to migrate a UC tree to a new MC revision or copy
1060 PROCEDURE migrate_uc_tree(
1061 p_api_version IN NUMBER,
1062 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1063 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1064 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1065 x_return_status OUT NOCOPY VARCHAR2,
1066 x_msg_count OUT NOCOPY NUMBER,
1067 x_msg_data OUT NOCOPY VARCHAR2,
1068 p_uc_header_id IN NUMBER,
1069 p_mc_header_id IN NUMBER)
1070 IS
1071 TYPE l_mc_children_rec_type IS RECORD(
1072 relationship_id NUMBER,
1073 position_key NUMBER,
1074 position_ref_code VARCHAR2(30),
1075 item_group_id NUMBER,
1076 matched_flag VARCHAR2(1));
1077 TYPE l_mc_children_tbl_type IS TABLE OF l_mc_children_rec_type INDEX BY BINARY_INTEGER;
1078 l_mc_children_tbl l_mc_children_tbl_type;
1079 l_api_name CONSTANT VARCHAR2(30) := 'migrate_uc_tree';
1080 l_api_version CONSTANT NUMBER := 1.0;
1081 l_return_status VARCHAR2(1);
1082 l_evaluation_status VARCHAR2(1);
1083 l_msg_count NUMBER;
1084 l_msg_data VARCHAR2(2000);
1085 l_relationship_id NUMBER;
1086 l_mc_header_id NUMBER;
1087 l_mc_name ahl_mc_headers_b.name%TYPE;
1088 l_mc_revision ahl_mc_headers_b.revision%TYPE;
1089 l_mc_status_code FND_LOOKUP_VALUES.lookup_code%TYPE;
1090 l_root_instance_id NUMBER;
1091 l_root_relationship_id NUMBER;
1092 l_mc_top_rel_id NUMBER;
1093 l_parent_uc_header_id NUMBER;
1094 l_parent_instance_id NUMBER;
1095 l_sub_top_rel_id NUMBER;
1096 l_sub_mc_header_id NUMBER;
1097 l_boolean_var BOOLEAN;
1098 l_dummy NUMBER;
1099 i NUMBER;
1100 i_mc_child NUMBER;
1101 l_item_match BOOLEAN;
1102 l_matched BOOLEAN;
1103 l_return_value BOOLEAN;
1104 l_transaction_type_id NUMBER;
1105 l_version_no NUMBER;
1106 l_uc_header_all ahl_unit_config_headers%ROWTYPE;
1107 l_csi_relationship_rec csi_datastructures_pub.ii_relationship_rec;
1108 l_csi_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
1109 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
1110 l_root_uc_header_id NUMBER;
1111 l_root_uc_status_code FND_LOOKUP_VALUES.lookup_code%TYPE;
1112 l_root_active_uc_status_code FND_LOOKUP_VALUES.lookup_code%TYPE;
1113 l_root_uc_ovn NUMBER;
1114 l_new_relationship_id NUMBER;
1115 --In order to check whether the instance belongs to a sub unit or an extra node branch
1116 CURSOR check_subuc_extra(c_instance_id NUMBER, c_top_instance_id NUMBER) IS
1117 SELECT object_id, subject_id, position_reference
1118 FROM csi_ii_relationships
1119 WHERE subject_id IN (SELECT csi_item_instance_id
1120 FROM ahl_unit_config_headers
1121 WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
1122 OR position_reference IS NULL
1123 START WITH subject_id = c_instance_id
1124 AND relationship_type_code = 'COMPONENT-OF'
1125 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1126 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1127 CONNECT BY subject_id = PRIOR object_id
1128 AND relationship_type_code = 'COMPONENT-OF'
1129 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1130 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1131 AND subject_id <> c_top_instance_id;
1132 l_check_subuc_extra check_subuc_extra%ROWTYPE;
1133 --Check the given uc_heder_id is existing and if yes, get its status and other information
1134 CURSOR check_uc_header IS
1135 SELECT U.unit_config_status_code uc_status_code,
1136 U.active_uc_status_code,
1137 U.csi_item_instance_id instance_id,
1138 U.master_config_id mc_header_id,
1139 U.object_version_number,
1140 C.inventory_item_id inventory_item_id,
1141 C.inv_master_organization_id inventory_org_id,
1142 C.inventory_revision,
1143 C.quantity,
1144 C.unit_of_measure
1145 FROM ahl_unit_config_headers U,
1146 csi_item_instances C
1147 WHERE U.unit_config_header_id = p_uc_header_id
1148 AND trunc(nvl(U.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1149 AND U.csi_item_instance_id = C.instance_id;
1150 l_uc_header_attr check_uc_header%ROWTYPE;
1151 CURSOR check_mc_header IS
1152 SELECT H.mc_header_id,
1153 H.config_status_code,
1154 R.relationship_id,
1155 H.name,
1156 H.revision
1157 FROM ahl_mc_headers_b H,
1158 ahl_mc_relationships R
1159 WHERE H.mc_header_id = p_mc_header_id
1160 AND R.mc_header_id = H.mc_header_id
1161 AND R.parent_relationship_id IS NULL
1162 AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
1163 AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1164 CURSOR get_mc_header_items(c_mc_header_id NUMBER) IS
1165 SELECT M.relationship_id,
1166 I.inventory_item_id,
1167 I.inventory_org_id
1168 FROM ahl_mc_relationships M,
1169 ahl_item_associations_b I
1170 WHERE M.mc_header_id = c_mc_header_id
1171 AND M.parent_relationship_id IS NULL
1172 AND M.item_group_id = I.item_group_id;
1173 /*
1174 CURSOR get_associated_items(c_item_group_id NUMBER) IS
1175 SELECT inventory_item_id,
1176 inventory_org_id
1177 FROM ahl_item_associations_b
1178 WHERE item_group_id = c_item_group_id;
1179 l_get_associated_item get_associated_items%ROWTYPE;
1180 */
1181
1182 --Given an instance_id, get all of its immediate children from csi_ii_relationships
1183 CURSOR get_csi_children(c_instance_id NUMBER) IS
1184 SELECT C.relationship_id csi_ii_relationship_id,
1185 C.object_version_number csi_ii_relationship_ovn,
1186 C.object_id parent_instance_id,
1187 C.subject_id instance_id,
1188 to_number(C.position_reference) relationship_id,
1189 M.position_key,
1190 M.position_ref_code,
1191 I.inventory_item_id,
1192 I.inv_master_organization_id inventory_org_id,
1193 I.inventory_revision,
1194 I.quantity,
1195 I.unit_of_measure
1196 FROM csi_ii_relationships C,
1197 ahl_mc_relationships M,
1198 csi_item_instances I
1199 WHERE to_number(C.position_reference) = M.relationship_id (+)
1200 AND C.subject_id = I.instance_id
1201 AND C.object_id = c_instance_id
1202 AND C.relationship_type_code = 'COMPONENT-OF'
1203 AND trunc(nvl(C.active_start_date,SYSDATE)) <= trunc(SYSDATE)
1204 AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1205 --Given an relationship_id, get all of its immediate children from MC
1206 CURSOR get_mc_children(c_relationship_id NUMBER) IS
1207 SELECT parent_relationship_id parent_rel_id,
1208 relationship_id,
1209 position_key,
1210 position_ref_code,
1211 item_group_id
1212 FROM ahl_mc_relationships
1213 WHERE parent_relationship_id = c_relationship_id
1214 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1215 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1216 --Check whether an instance in UC is the top node of a sub UC, if yes, then get the
1217 --relationship of the sub UC's top node
1218 CURSOR check_sub_uc(c_instance_id NUMBER) IS
1219 SELECT A.master_config_id mc_header_id,
1220 B.relationship_id
1221 FROM ahl_unit_config_headers A,
1222 ahl_mc_relationships B
1223 WHERE A.csi_item_instance_id = c_instance_id
1224 AND trunc(nvl(A.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1225 AND B.mc_header_id = A.master_config_id
1226 AND B.parent_relationship_id IS NULL
1227 AND trunc(nvl(B.active_start_date,SYSDATE)) <= trunc(SYSDATE)
1228 AND trunc(nvl(B.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1229 l_check_sub_uc check_sub_uc%ROWTYPE;
1230 --Given a position(leaf) in MC, get all the sub MCs which can be associated to this position
1231 CURSOR get_sub_mcs(c_relationship_id NUMBER) IS
1232 SELECT mc_header_id
1233 FROM ahl_mc_config_relations
1234 WHERE relationship_id = c_relationship_id
1235 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1236 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1237 --Based on the UC, list all of the non leaf nodes including the root node
1238 CURSOR get_non_leaf_nodes(c_instance_id NUMBER) IS
1239 SELECT TO_NUMBER(NULL) parent_instance_id, --just include the root uc node
1240 B.csi_item_instance_id instance_id,
1241 A.relationship_id
1242 FROM ahl_unit_config_headers B,
1243 ahl_mc_relationships A
1244 WHERE B.csi_item_instance_id = c_instance_id
1245 AND trunc(nvl(B.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1246 AND A.mc_header_id = B.master_config_id
1247 AND A.parent_relationship_id IS NULL
1248 UNION ALL
1249 SELECT object_id parent_instance_id,
1250 subject_id instance_id,
1251 to_number(position_reference) relationship_id
1252 FROM csi_ii_relationships A
1253 --remove all of the leaf node after finishing the hierarchical query
1254 WHERE EXISTS (SELECT 'x'
1255 FROM csi_ii_relationships B
1256 WHERE B.object_id = A.subject_id
1257 AND relationship_type_code = 'COMPONENT-OF'
1258 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1259 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
1260 START WITH object_id = c_instance_id
1261 AND relationship_type_code = 'COMPONENT-OF'
1262 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1263 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1264 CONNECT BY object_id = PRIOR subject_id
1265 AND relationship_type_code = 'COMPONENT-OF'
1266 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1267 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1268
1269 CURSOR get_position_reference(c_object_id NUMBER, c_subject_id NUMBER) IS
1270 SELECT relationship_id csi_ii_relationship_id,
1271 object_version_number csi_ii_relationship_ovn,
1272 to_number(position_reference) relationship_id
1273 FROM csi_ii_relationships
1274 WHERE object_id = c_object_id
1275 AND subject_id = c_subject_id
1276 AND relationship_type_code = 'COMPONENT-OF'
1277 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1278 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1279 l_position_info get_position_reference%ROWTYPE;
1280
1281 BEGIN
1282 --Initialize API return status to success
1283 x_return_status := FND_API.G_RET_STS_SUCCESS;
1284
1285 -- Standard Start of API savepoint
1286 SAVEPOINT migrate_uc_tree;
1287
1288 --Standard call to check for call compatibility.
1289 IF NOT FND_API.compatible_api_call(
1290 l_api_version,
1291 p_api_version,
1292 l_api_name,
1293 G_PKG_NAME)
1294 THEN
1295 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1296 END IF;
1297
1298 --Initialize message list if p_init_msg_list is set to TRUE.
1299 IF FND_API.to_boolean( p_init_msg_list ) THEN
1300 FND_MSG_PUB.initialize;
1301 END IF;
1302
1303 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1304 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1305 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1306 'At the start of the procedure');
1307 END IF;
1308
1309 --Validate the input parameter p_uc_header_id and its status
1310 --Validate input parameter p_uc_header_id, its two status
1311 OPEN check_uc_header;
1312 FETCH check_uc_header INTO l_uc_header_attr;
1313 IF check_uc_header%NOTFOUND THEN
1314 FND_MESSAGE.set_name( 'AHL','AHL_UC_API_PARAMETER_INVALID');
1315 FND_MESSAGE.set_token('NAME', 'uc_header_id');
1316 FND_MESSAGE.set_token('VALUE', p_uc_header_id);
1317 FND_MSG_PUB.add;
1318 CLOSE check_uc_header;
1319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1320 ELSE
1321
1322 -- ACL :: Changes for R12
1323 IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
1324 FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
1325 FND_MSG_PUB.add;
1326 CLOSE check_uc_header;
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328 END IF;
1329
1330 ahl_util_uc_pkg.get_root_uc_attr(p_uc_header_id,
1331 l_root_uc_header_id,
1332 l_root_instance_id,
1333 l_root_uc_status_code,
1334 l_root_active_uc_status_code,
1335 l_root_uc_ovn);
1336 IF (l_root_uc_status_code = 'APPROVAL_PENDING' OR
1337 l_root_active_uc_status_code = 'APPROVAL_PENDING') THEN
1338 FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
1339 FND_MESSAGE.set_token('UC_HEADER_ID', l_root_uc_header_id);
1340 FND_MSG_PUB.add;
1341 CLOSE check_uc_header;
1342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1343 ELSE
1344 CLOSE check_uc_header;
1345 END IF;
1346 END IF;
1347
1348 --Validate the input parameter p_mc_header_id and get its status
1349 OPEN check_mc_header;
1350 FETCH check_mc_header INTO l_mc_header_id,
1351 l_mc_status_code,
1352 l_mc_top_rel_id,
1353 l_mc_name,
1354 l_mc_revision;
1355 IF check_mc_header%NOTFOUND THEN
1356 FND_MESSAGE.set_name( 'AHL','AHL_UC_API_PARAMETER_INVALID');
1357 FND_MESSAGE.set_token('NAME', 'mc_header_id');
1358 FND_MESSAGE.set_token('VALUE', p_mc_header_id);
1359 FND_MSG_PUB.add;
1360 CLOSE check_mc_header;
1361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1362 ELSE
1363 IF l_mc_status_code <> 'COMPLETE' THEN
1364 FND_MESSAGE.set_name( 'AHL','AHL_UC_MC_NOT_COMPLETE' );
1365 FND_MESSAGE.set_token('NAME', l_mc_name);
1366 FND_MESSAGE.set_token('REVISION', l_mc_revision);
1367 FND_MSG_PUB.add;
1368 CLOSE check_mc_header;
1369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1370 ELSIF l_mc_header_id = l_uc_header_attr.mc_header_id THEN
1371 FND_MESSAGE.set_name( 'AHL','AHL_UC_MC_MIGRATE_SAME' );
1372 FND_MESSAGE.set_token('NAME', l_mc_name);
1373 FND_MESSAGE.set_token('REVISION', l_mc_revision);
1374 FND_MSG_PUB.add;
1375 CLOSE check_mc_header;
1376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1377 ELSE
1378 CLOSE check_mc_header;
1379 END IF;
1380 END IF;
1381
1382 --The following lines are used to update the position_reference column in csi_ii_relationships
1383 --First, get transaction_type_id .
1384 AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
1385 IF NOT l_return_value THEN
1386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1387 END IF;
1388
1389 --For UC root node, only need to ensure item matches, no need to check position key
1390 --and position_reference
1391 IF p_uc_header_id = l_root_uc_header_id THEN
1392 --Standalone UC or UC installed in a CSI instance, only need to check whether
1393 --item match for the top node
1394 l_item_match := AHL_UTIL_UC_PKG.item_match(l_mc_top_rel_id,
1395 l_uc_header_attr.inventory_item_id,
1396 l_uc_header_attr.inventory_org_id,
1397 l_uc_header_attr.inventory_revision,
1398 l_uc_header_attr.quantity,
1399 l_uc_header_attr.unit_of_measure);
1400 IF NOT l_item_match THEN
1401 --dbms_output.put_line('l_root_uc_header_id='||l_root_uc_header_id||' l_mc_top_rel_id='||l_mc_top_rel_id);
1402 --dbms_output.put_line('inventory_item_id='||l_uc_header_attr.inventory_item_id||' inventory_org_id='||l_uc_header_attr.inventory_org_id);
1403 --dbms_output.put_line('revision='||l_uc_header_attr.inventory_revision||' quantity='||l_uc_header_attr.quantity);
1404 --dbms_output.put_line('uom='||l_uc_header_attr.unit_of_measure);
1405 FND_MESSAGE.set_name( 'AHL','AHL_UC_HEADER_NOT_MATCH' );
1406 FND_MSG_PUB.add;
1407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1408 ELSE
1409 --Item matches, can be migrated and thus change the mc_header_id of the UC header
1410 NULL;
1411 END IF;
1412 ELSIF (p_uc_header_id <> l_root_uc_header_id) THEN
1413 --Sub unit installed on another unit, either extra node or non extra node
1414 ahl_util_uc_pkg.get_parent_uc_header(l_uc_header_attr.instance_id,
1415 l_parent_uc_header_id,
1416 l_parent_instance_id);
1417 --Get its parent_instance_id in order to check whether it is an extra node within
1418 --the context of its parent UC
1419 IF NOT ahl_util_uc_pkg.extra_node(l_uc_header_attr.instance_id, l_parent_instance_id) THEN
1420 --Non extra sub unit within the context of its parent unit
1421 --Get its position_reference
1422 OPEN get_position_reference(l_parent_instance_id, l_uc_header_attr.instance_id);
1423 FETCH get_position_reference INTO l_position_info;
1424 IF get_position_reference%NOTFOUND THEN
1425 FND_MESSAGE.set_name( 'AHL','AHL_UC_UNIT_UNINSTALLED' );
1426 FND_MESSAGE.set_token('CHILD',l_uc_header_attr.instance_id);
1427 FND_MESSAGE.set_token('PARENT',l_parent_instance_id);
1428 FND_MSG_PUB.add;
1429 CLOSE get_position_reference;
1430 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1431 END IF;
1432 CLOSE get_position_reference;
1433 --Check whether the new mc_header_id to which to be migrated could be an alternate sub MC
1434 --in position
1435 l_boolean_var := FALSE;
1436 FOR l_get_sub_mcs IN get_sub_mcs(l_position_info.relationship_id) LOOP
1437 l_sub_mc_header_id := l_get_sub_mcs.mc_header_id;
1438 IF l_sub_mc_header_id = p_mc_header_id THEN
1439 l_boolean_var := TRUE;
1440 EXIT;
1441 END IF;
1442 END LOOP;
1443 IF NOT l_boolean_var THEN
1444 --Again check whether item match
1445 l_item_match := AHL_UTIL_UC_PKG.item_match(l_mc_top_rel_id,
1446 l_uc_header_attr.inventory_item_id,
1447 l_uc_header_attr.inventory_org_id,
1448 l_uc_header_attr.inventory_revision,
1449 l_uc_header_attr.quantity,
1450 l_uc_header_attr.unit_of_measure);
1451 IF NOT l_item_match THEN
1452 FND_MESSAGE.set_name( 'AHL','AHL_UC_HEADER_NOT_MATCH' );
1453 FND_MSG_PUB.add;
1454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1455 ELSE
1456 --Make the sub unit an extra node in its parent unit context and
1457 --update its own mc_header_id to the new p_mc_header_id
1458
1459 --Set the CSI transaction record
1460 l_csi_transaction_rec.source_transaction_date := SYSDATE;
1461 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
1462 --Set CSI relationship record
1463 l_csi_relationship_rec.relationship_id := l_position_info.csi_ii_relationship_id;
1464 l_csi_relationship_rec.object_version_number := l_position_info.csi_ii_relationship_ovn;
1465 l_csi_relationship_rec.position_reference := NULL;
1466 l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
1467 l_csi_relationship_rec.object_id := l_parent_instance_id;
1468 l_csi_relationship_rec.subject_id := l_uc_header_attr.instance_id;
1469 l_csi_relationship_tbl(1) := l_csi_relationship_rec;
1470 CSI_II_RELATIONSHIPS_PUB.update_relationship(
1471 p_api_version => 1.0,
1472 p_relationship_tbl => l_csi_relationship_tbl,
1473 p_txn_rec => l_csi_transaction_rec,
1474 x_return_status => l_return_status,
1475 x_msg_count => l_msg_count,
1476 x_msg_data => l_msg_data);
1477 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1478 RAISE FND_API.G_EXC_ERROR;
1479 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1481 END IF;
1482 --Could be migrated and thus change the mc_header_id of the UC header
1483 NULL;
1484 END IF;
1485 ELSE
1486 --The sub unit is still an alternate sub unit so only need to update
1487 --its own mc_header_id to the new p_mc_header_id
1488 NULL;
1489 END IF;
1490 ELSE
1491 --Extra node and check whether item match
1492 l_item_match := AHL_UTIL_UC_PKG.item_match(l_mc_top_rel_id,
1493 l_uc_header_attr.inventory_item_id,
1494 l_uc_header_attr.inventory_org_id,
1495 l_uc_header_attr.inventory_revision,
1496 l_uc_header_attr.quantity,
1497 l_uc_header_attr.unit_of_measure);
1498 IF NOT l_item_match THEN
1499 FND_MESSAGE.set_name( 'AHL','AHL_UC_HEADER_NOT_MATCH' );
1500 FND_MSG_PUB.add;
1501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1502 ELSE
1503 --Could be migrated but will be still extra node to its parent unit
1504 --Not necessary to update position_reference to NULL because it is already an
1505 --extra node to its parent unit
1506 NULL;
1507 END IF;
1508 END IF;
1509 END IF;
1510 --Update the master_config_id of p_uc_header_id to the new p_mc_header_id
1511 IF p_uc_header_id = l_root_uc_header_id THEN
1512 --For standalone no object_version_number change here because we have to update the
1513 --status for the same record after migration
1514 UPDATE ahl_unit_config_headers
1515 SET master_config_id = p_mc_header_id,
1516 last_updated_by = fnd_global.user_id,
1517 last_update_date = SYSDATE,
1518 last_update_login = fnd_global.login_id
1519 WHERE unit_config_header_id = p_uc_header_id
1520 AND object_version_number = l_uc_header_attr.object_version_number;
1521 ELSE
1522 UPDATE ahl_unit_config_headers
1523 SET master_config_id = p_mc_header_id,
1524 object_version_number = object_version_number + 1,
1525 last_updated_by = fnd_global.user_id,
1526 last_update_date = SYSDATE,
1527 last_update_login = fnd_global.login_id
1528 WHERE unit_config_header_id = p_uc_header_id
1529 AND object_version_number = l_uc_header_attr.object_version_number;
1530 END IF;
1531 IF SQL%ROWCOUNT = 0 THEN
1532 FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
1533 FND_MSG_PUB.add;
1534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1535 END IF;
1536
1537 --Copy the change to UC history table
1538 ahl_util_uc_pkg.copy_uc_header_to_history(p_uc_header_id, l_return_status);
1539 --IF history copy failed, then don't raise exception, just add the messageto the message stack
1540 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1541 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1542 FND_MSG_PUB.add;
1543 END IF;
1544
1545 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1546 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1547 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||':Within API',
1548 'After updating the master_config_id');
1549 END IF;
1550
1551 --Loop through all the non leaf nodes including the root node
1552 FOR l_get_non_leaf_node IN get_non_leaf_nodes(l_uc_header_attr.instance_id) LOOP
1553 --Make sure the node doesn't belong to a sub unit or extra node branch, because for
1554 --all the nodes in the sub unit or extra node branch including the top most extra node
1555 --it is not necessary to process them and the sub unit top node is processed in the loop
1556 --of its parent node
1557 OPEN check_subuc_extra(l_get_non_leaf_node.instance_id, l_uc_header_attr.instance_id);
1558 FETCH check_subuc_extra INTO l_check_subuc_extra;
1559 IF check_subuc_extra%NOTFOUND THEN
1560 --Get all the immediate children of the corresponding MC node
1561 --First get the non leaf node's new relationship_id to which it has been migrated
1562 --This is a bug fix found by Barry on Nov 6, 2003
1563 IF l_uc_header_attr.instance_id = l_get_non_leaf_node.instance_id THEN
1564 l_new_relationship_id := l_get_non_leaf_node.relationship_id;
1565 ELSE
1566 SELECT to_number(position_reference) into l_new_relationship_id
1567 FROM csi_ii_relationships
1568 WHERE subject_id = l_get_non_leaf_node.instance_id
1569 AND relationship_type_code='COMPONENT-OF'
1570 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
1571 AND trunc(nvl(active_end_date, sysdate+1)) > trunc(sysdate);
1572 END IF;
1573
1574 i_mc_child := 1;
1575 FOR l_get_mc_child IN get_mc_children(l_new_relationship_id) LOOP
1576 l_mc_children_tbl(i_mc_child).relationship_id := l_get_mc_child.relationship_id;
1577 l_mc_children_tbl(i_mc_child).position_key := l_get_mc_child.position_key;
1578 l_mc_children_tbl(i_mc_child).position_ref_code := l_get_mc_child.position_ref_code;
1579 l_mc_children_tbl(i_mc_child).item_group_id := l_get_mc_child.item_group_id;
1580 l_mc_children_tbl(i_mc_child).matched_flag := 'N'; -- default to 'N'
1581 i_mc_child := i_mc_child + 1;
1582 END LOOP;
1583 FOR l_get_csi_child IN get_csi_children(l_get_non_leaf_node.instance_id) LOOP
1584 --No processing for the extra node
1585 IF l_get_csi_child.relationship_id IS NOT NULL THEN
1586 l_matched := FALSE;
1587 l_relationship_id := NULL;
1588 IF l_mc_children_tbl.COUNT > 0 THEN
1589 <<OUTER>>
1590 FOR i IN l_mc_children_tbl.FIRST..l_mc_children_tbl.LAST LOOP
1591 IF l_mc_children_tbl(i).matched_flag <> 'Y' THEN
1592 IF l_get_csi_child.position_key = l_mc_children_tbl(i).position_key OR
1593 l_get_csi_child.position_ref_code = l_mc_children_tbl(i).position_ref_code THEN
1594 --Check whether this node is a sub unit top node, if YES compare the sub
1595 --mc_header_id, otherwise compare item
1596 OPEN check_sub_uc(l_get_csi_child.instance_id);
1597 FETCH check_sub_uc INTO l_check_sub_uc;
1598 IF check_sub_uc%FOUND THEN
1599 FOR l_get_sub_mc IN get_sub_mcs(l_mc_children_tbl(i).relationship_id) LOOP
1600 IF l_check_sub_uc.mc_header_id = l_get_sub_mc.mc_header_id THEN
1601 l_matched := TRUE;
1602 l_mc_children_tbl(i).matched_flag := 'Y';
1603 l_relationship_id := l_mc_children_tbl(i).relationship_id;
1604 CLOSE check_sub_uc;
1605 EXIT OUTER;
1606 END IF;
1607 END LOOP;
1608 ELSE
1609 l_matched := AHL_UTIL_UC_PKG.item_match(l_mc_children_tbl(i).relationship_id,
1610 l_get_csi_child.inventory_item_id,
1611 l_get_csi_child.inventory_org_id,
1612 l_get_csi_child.inventory_revision,
1613 l_get_csi_child.quantity,
1614 l_get_csi_child.unit_of_measure);
1615 IF l_matched THEN
1616 l_mc_children_tbl(i).matched_flag := 'Y';
1617 l_relationship_id := l_mc_children_tbl(i).relationship_id;
1618 CLOSE check_sub_uc;
1619 EXIT OUTER;
1620 END IF;
1621 /*
1622 FOR l_get_associated_item IN get_associated_items(l_mc_children_tbl(i).item_group_id) LOOP
1623 IF l_get_csi_child.inventory_item_id = l_get_associated_item.inventory_item_id AND
1624 l_get_csi_child.inventory_org_id = l_get_associated_item.inventory_org_id THEN
1625 l_matched := TRUE;
1626 l_relationship_id := l_mc_children_tbl(i).relationship_id;
1627 EXIT OUTER;
1628 END IF;
1629 END LOOP;
1630 */
1631
1632 END IF;
1633 CLOSE check_sub_uc;
1634 END IF; --whether position_key or position_ref_code matches
1635 END IF; --whether the MC chilren node has already been matched
1636 END LOOP; --of all the MC children
1637 END IF;
1638
1639 --Set the CSI transaction record
1640 l_csi_transaction_rec.source_transaction_date := SYSDATE;
1641 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
1642 --Set CSI relationship record
1643 l_csi_relationship_rec.relationship_id := l_get_csi_child.csi_ii_relationship_id;
1644 l_csi_relationship_rec.object_version_number := l_get_csi_child.csi_ii_relationship_ovn;
1645 l_csi_relationship_rec.position_reference := to_char(l_relationship_id);
1646 l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
1647 l_csi_relationship_rec.object_id := l_get_csi_child.parent_instance_id;
1648 l_csi_relationship_rec.subject_id := l_get_csi_child.instance_id;
1649 l_csi_relationship_tbl(1) := l_csi_relationship_rec;
1650 CSI_II_RELATIONSHIPS_PUB.update_relationship(
1651 p_api_version => 1.0,
1652 p_relationship_tbl => l_csi_relationship_tbl,
1653 p_txn_rec => l_csi_transaction_rec,
1654 x_return_status => l_return_status,
1655 x_msg_count => l_msg_count,
1656 x_msg_data => l_msg_data);
1657 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1658 RAISE FND_API.G_EXC_ERROR;
1659 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1661 END IF;
1662 END IF; --whether the UC node is an extra node
1663 END LOOP; --of all the UC children
1664 END IF; --whether it is a node belonging to subuc or extra node branch
1665 CLOSE check_subuc_extra;
1666 END LOOP; --of all non leaf nodes
1667
1668 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1669 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1670 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||':Within API',
1671 'After migration the whole UC tree');
1672 END IF;
1673
1674 --Call check_completeness API to check the completeness of the root UC
1675 IF (l_root_uc_status_code IN ('COMPLETE', 'INCOMPLETE')) THEN
1676 AHL_UC_VALIDATION_PUB.check_completeness(
1677 p_api_version => 1.0,
1678 p_init_msg_list => FND_API.G_FALSE,
1679 p_commit => FND_API.G_FALSE,
1680 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1681 x_return_status => l_return_status,
1682 x_msg_count => l_msg_count,
1683 x_msg_data => l_msg_data,
1684 p_unit_header_id => l_root_uc_header_id,
1685 x_evaluation_status => l_evaluation_status);
1686 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1687 RAISE FND_API.G_EXC_ERROR;
1688 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1690 END IF;
1691 END IF;
1692 --dbms_output.put_line('l_root_uc_ovn(a)='||l_root_uc_ovn);
1693 select object_version_number into l_root_uc_ovn
1694 from ahl_unit_config_headers
1695 where unit_config_header_id = l_root_uc_header_id;
1696 --dbms_output.put_line('l_root_uc_ovn(b)='||l_root_uc_ovn);
1697
1698 --After migration, UC(root) status change should be made.
1699 IF (l_evaluation_status = 'T' AND l_root_uc_status_code = 'INCOMPLETE') THEN
1700 UPDATE ahl_unit_config_headers
1701 SET unit_config_status_code = 'COMPLETE',
1702 active_uc_status_code = 'UNAPPROVED',
1703 object_version_number = object_version_number + 1,
1704 last_updated_by = fnd_global.user_id,
1705 last_update_date = SYSDATE,
1706 last_update_login = fnd_global.login_id
1707 WHERE unit_config_header_id = l_root_uc_header_id
1708 AND object_version_number = l_root_uc_ovn;
1709 IF SQL%ROWCOUNT = 0 THEN
1710 FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
1711 FND_MSG_PUB.add;
1712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1713 END IF;
1714
1715 --Copy the change to UC history table
1716 ahl_util_uc_pkg.copy_uc_header_to_history(l_root_uc_header_id, l_return_status);
1717 --IF history copy failed, then don't raise exception, just add the messageto the message stack
1718 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1719 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1720 FND_MSG_PUB.add;
1721 END IF;
1722 ELSIF (l_evaluation_status = 'F' AND l_root_uc_status_code = 'COMPLETE') THEN
1723 UPDATE ahl_unit_config_headers
1724 SET unit_config_status_code = 'INCOMPLETE',
1725 active_uc_status_code = 'UNAPPROVED',
1726 object_version_number = object_version_number + 1,
1727 last_updated_by = fnd_global.user_id,
1728 last_update_date = SYSDATE,
1729 last_update_login = fnd_global.login_id
1730 WHERE unit_config_header_id = l_root_uc_header_id
1731 AND object_version_number = l_root_uc_ovn;
1732 IF SQL%ROWCOUNT = 0 THEN
1733 FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
1734 FND_MSG_PUB.add;
1735 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1736 END IF;
1737
1738 --Copy the change to UC history table
1739 ahl_util_uc_pkg.copy_uc_header_to_history(l_root_uc_header_id, l_return_status);
1740 --IF history copy failed, then don't raise exception, just add the messageto the message stack
1741 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1742 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1743 FND_MSG_PUB.add;
1744 END IF;
1745 ELSIF (l_root_uc_status_code IN ('COMPLETE', 'INCOMPLETE') AND
1746 (l_root_active_uc_status_code IS NULL OR
1747 l_root_active_uc_status_code <> 'UNAPPROVED')) THEN
1748 UPDATE ahl_unit_config_headers
1749 SET active_uc_status_code = 'UNAPPROVED',
1750 object_version_number = object_version_number + 1,
1751 last_updated_by = fnd_global.user_id,
1752 last_update_date = SYSDATE,
1753 last_update_login = fnd_global.login_id
1754 WHERE unit_config_header_id = l_root_uc_header_id
1755 AND object_version_number = l_root_uc_ovn;
1756 IF SQL%ROWCOUNT = 0 THEN
1757 FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
1758 FND_MSG_PUB.add;
1759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1760 END IF;
1761
1762 --Copy the change to UC history table
1763 ahl_util_uc_pkg.copy_uc_header_to_history(l_root_uc_header_id, l_return_status);
1764 --IF history copy failed, then don't raise exception, just add the messageto the message stack
1765 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1766 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1767 FND_MSG_PUB.add;
1768 END IF;
1769 END IF;
1770
1771 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1772 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1773 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||':After normal execution',
1774 'Status changed and at the end of the procedure');
1775 END IF;
1776
1777 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1778 l_msg_count := FND_MSG_PUB.count_msg;
1779 IF l_msg_count > 0 THEN
1780 x_msg_count := l_msg_count;
1781 RAISE FND_API.G_EXC_ERROR;
1782 END IF;
1783
1784 -- Perform the Commit (if requested)
1785 IF FND_API.to_boolean(p_commit) THEN
1786 COMMIT;
1787 END IF;
1788
1789 -- Count and Get messages (optional)
1790 FND_MSG_PUB.count_and_get(
1791 p_encoded => FND_API.G_FALSE,
1792 p_count => x_msg_count,
1793 p_data => x_msg_data);
1794
1795 EXCEPTION
1796 WHEN FND_API.G_EXC_ERROR THEN
1797 ROLLBACK TO migrate_uc_tree;
1798 x_return_status := FND_API.G_RET_STS_ERROR ;
1799 FND_MSG_PUB.count_and_get(
1800 p_encoded => FND_API.G_FALSE,
1801 p_count => x_msg_count,
1802 p_data => x_msg_data);
1803 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1804 ROLLBACK TO migrate_uc_tree;
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1806 FND_MSG_PUB.count_and_get(
1807 p_encoded => FND_API.G_FALSE,
1808 p_count => x_msg_count,
1809 p_data => x_msg_data);
1810 WHEN OTHERS THEN
1811 ROLLBACK TO migrate_uc_tree;
1812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1813 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1814 THEN
1815 FND_MSG_PUB.add_exc_msg(
1816 p_pkg_name => G_PKG_NAME,
1817 p_procedure_name => l_api_name,
1818 p_error_text => SUBSTRB(SQLERRM,1,240));
1819 END IF;
1820 FND_MSG_PUB.count_and_get(
1821 p_encoded => FND_API.G_FALSE,
1822 p_count => x_msg_count,
1823 p_data => x_msg_data);
1824 END;
1825
1826 -- Define Procedure remap_uc_subtree --
1827 -- This API is used to remap a UC subtree (not a sub-unit) to a MC branch. It is called
1828 -- by ahl_uc_instnace_pvt.install_existing_instance.
1829 PROCEDURE remap_uc_subtree (
1830 p_api_version IN NUMBER,
1831 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1832 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1833 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1834 x_return_status OUT NOCOPY VARCHAR2,
1835 x_msg_count OUT NOCOPY NUMBER,
1836 x_msg_data OUT NOCOPY VARCHAR2,
1837 p_instance_id IN NUMBER,
1838 p_relationship_id IN NUMBER)
1839 IS
1840 TYPE l_mc_children_rec_type IS RECORD(
1841 relationship_id NUMBER,
1842 position_key NUMBER,
1843 position_ref_code VARCHAR2(30),
1844 item_group_id NUMBER,
1845 matched_flag VARCHAR2(1));
1846 TYPE l_mc_children_tbl_type IS TABLE OF l_mc_children_rec_type INDEX BY BINARY_INTEGER;
1847 l_mc_children_tbl l_mc_children_tbl_type;
1848 l_api_name CONSTANT VARCHAR2(30) := 'remap_uc_subtree';
1849 l_api_version CONSTANT NUMBER := 1.0;
1850 l_return_status VARCHAR2(1);
1851 l_msg_count NUMBER;
1852 l_msg_data VARCHAR2(2000);
1853 l_relationship_id NUMBER;
1854 l_mc_header_id NUMBER;
1855 l_root_instance_id NUMBER;
1856 l_root_relationship_id NUMBER;
1857 l_dummy NUMBER;
1858 i NUMBER;
1859 i_mc_child NUMBER;
1860 l_item_match BOOLEAN;
1861 l_matched BOOLEAN;
1862 l_return_value BOOLEAN;
1863 l_transaction_type_id NUMBER;
1864 l_version_no NUMBER;
1865 l_csi_relationship_rec csi_datastructures_pub.ii_relationship_rec;
1866 l_csi_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
1867 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
1868 l_new_relationship_id NUMBER;
1869 --In order to check whether the instance belongs to a sub unit or an extra node branch
1870 CURSOR check_subuc_extra(c_instance_id NUMBER, c_top_instance_id NUMBER) IS
1871 SELECT object_id, subject_id, position_reference
1872 FROM csi_ii_relationships
1873 WHERE subject_id IN (SELECT csi_item_instance_id
1874 FROM ahl_unit_config_headers
1875 WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
1876 OR position_reference IS NULL
1877 START WITH subject_id = c_instance_id
1878 AND relationship_type_code = 'COMPONENT-OF'
1879 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1880 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1881 CONNECT BY subject_id = PRIOR object_id
1882 AND subject_id <> c_top_instance_id
1883 --This hierarchy query is from bottom up and the top node is not a UC root node, so we
1884 --have to discontinue the hierarchy query when it comes to the given node
1885 AND relationship_type_code = 'COMPONENT-OF'
1886 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1887 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1888 l_check_subuc_extra check_subuc_extra%ROWTYPE;
1889 --Check the given instance_id is existing, with children instances and not a unit's or sub-unit's top node. But doesn't check whether this instance is installed or not, which is checked in get_available_instances and install_existing_instance.
1890 CURSOR check_uc_instance IS
1891 SELECT R.object_id instance_id,
1892 to_number(R.position_reference) relationship_id,
1893 C.inventory_item_id inventory_item_id,
1894 C.inv_master_organization_id inventory_org_id,
1895 C.inventory_revision,
1896 C.quantity,
1897 C.unit_of_measure
1898 FROM csi_ii_relationships R,
1899 csi_item_instances C
1900 WHERE R.object_id = p_instance_id
1901 AND R.object_id = C.instance_id
1902 AND R.relationship_type_code = 'COMPONENT-OF'
1903 AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
1904 AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1905 AND NOT EXISTS (SELECT 'X'
1906 FROM ahl_unit_config_headers
1907 WHERE csi_item_instance_id = R.object_id
1908 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE));
1909 l_check_uc_instance check_uc_instance%ROWTYPE;
1910 --Check the given relationship_id exists and also get its item
1911 CURSOR check_mc_relationship IS
1912 SELECT M.relationship_id,
1913 I.inventory_item_id,
1914 I.inventory_org_id
1915 FROM ahl_mc_relationships M,
1916 ahl_item_associations_b I
1917 WHERE M.relationship_id = p_relationship_id
1918 AND M.item_group_id = I.item_group_id
1919 AND trunc(nvl(M.active_start_date,SYSDATE)) <= trunc(SYSDATE)
1920 AND trunc(nvl(M.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1921 l_check_mc_relationship check_mc_relationship%ROWTYPE;
1922 --Get the associated items for a given item_group_id
1923 /*
1924 CURSOR get_associated_items(c_item_group_id NUMBER) IS
1925 SELECT inventory_item_id,
1926 inventory_org_id
1927 FROM ahl_item_associations_b
1928 WHERE item_group_id = c_item_group_id;
1929 l_get_associated_item get_associated_items%ROWTYPE;
1930 */
1931 --Given an instance_id, get all of its immediate children from csi_ii_relationships
1932 CURSOR get_csi_children(c_instance_id NUMBER) IS
1933 SELECT C.relationship_id csi_ii_relationship_id,
1934 C.object_version_number csi_ii_relationship_ovn,
1935 C.object_id parent_instance_id,
1936 C.subject_id instance_id,
1937 to_number(C.position_reference) relationship_id,
1938 M.position_key,
1939 M.position_ref_code,
1940 I.inventory_item_id,
1941 I.inv_master_organization_id inventory_org_id,
1942 I.inventory_revision,
1943 I.quantity,
1944 I.unit_of_measure
1945 FROM csi_ii_relationships C,
1946 ahl_mc_relationships M,
1947 csi_item_instances I
1948 WHERE to_number(C.position_reference) = M.relationship_id (+)
1949 AND C.subject_id = I.instance_id
1950 AND C.object_id = c_instance_id
1951 AND C.relationship_type_code = 'COMPONENT-OF'
1952 AND trunc(nvl(C.active_start_date,SYSDATE)) <= trunc(SYSDATE)
1953 AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1954 --Given an relationship_id, get all of its immediate children from MC
1955 CURSOR get_mc_children(c_relationship_id NUMBER) IS
1956 SELECT parent_relationship_id parent_rel_id,
1957 relationship_id,
1958 position_key,
1959 position_ref_code,
1960 item_group_id
1961 FROM ahl_mc_relationships
1962 WHERE parent_relationship_id = c_relationship_id
1963 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1964 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1965 --Check whether an instance in UC is the top node of a sub UC, if yes, then get the
1966 --relationship of the sub UC's top node
1967 CURSOR check_sub_uc(c_instance_id NUMBER) IS
1968 SELECT A.master_config_id mc_header_id,
1969 B.relationship_id
1970 FROM ahl_unit_config_headers A,
1971 ahl_mc_relationships B
1972 WHERE A.csi_item_instance_id = c_instance_id
1973 AND trunc(nvl(A.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1974 AND B.mc_header_id = A.master_config_id
1975 AND B.parent_relationship_id IS NULL
1976 AND trunc(nvl(B.active_start_date,SYSDATE)) <= trunc(SYSDATE)
1977 AND trunc(nvl(B.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1978 l_check_sub_uc check_sub_uc%ROWTYPE;
1979 --Given a position(leaf) in MC, get all the sub MCs which can be associated to this position
1980 CURSOR get_sub_mcs(c_relationship_id NUMBER) IS
1981 SELECT mc_header_id
1982 FROM ahl_mc_config_relations
1983 WHERE relationship_id = c_relationship_id
1984 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1985 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1986 --Based on the UC, list all of the non leaf nodes including the root node
1987 CURSOR get_non_leaf_nodes(c_instance_id NUMBER) IS
1988 SELECT object_id parent_instance_id,
1989 subject_id instance_id,
1990 to_number(position_reference) relationship_id
1991 FROM csi_ii_relationships A
1992 --remove all of the leaf node after finishing the hierarchical query
1993 WHERE EXISTS (SELECT 'X'
1994 FROM csi_ii_relationships B
1995 WHERE B.object_id = A.subject_id
1996 AND relationship_type_code = 'COMPONENT-OF'
1997 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1998 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
1999 START WITH subject_id = c_instance_id
2000 AND relationship_type_code = 'COMPONENT-OF'
2001 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
2002 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2003 CONNECT BY object_id = PRIOR subject_id
2004 AND relationship_type_code = 'COMPONENT-OF'
2005 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
2006 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2007 BEGIN
2008 --Initialize API return status to success
2009 x_return_status := FND_API.G_RET_STS_SUCCESS;
2010
2011 -- Standard Start of API savepoint
2012 SAVEPOINT remap_uc_subtree;
2013
2014 --Standard call to check for call compatibility.
2015 IF NOT FND_API.compatible_api_call(
2016 l_api_version,
2017 p_api_version,
2018 l_api_name,
2019 G_PKG_NAME)
2020 THEN
2021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2022 END IF;
2023 --Initialize message list if p_init_msg_list is set to TRUE.
2024 IF FND_API.to_boolean( p_init_msg_list ) THEN
2025 FND_MSG_PUB.initialize;
2026 END IF;
2027 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2028 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2029 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
2030 'At the start of the procedure, p_instance_id = ' || p_instance_id || ', p_relationship_id = ' || p_relationship_id);
2031 END IF;
2032 --Validate the input parameter p_instance_id
2033 OPEN check_uc_instance;
2034 FETCH check_uc_instance INTO l_check_uc_instance;
2035 IF check_uc_instance%NOTFOUND THEN
2036 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
2037 FND_MESSAGE.set_token('NAME', 'instance_id');
2038 FND_MESSAGE.set_token('VALUE', p_instance_id);
2039 FND_MSG_PUB.add;
2040 CLOSE check_uc_instance;
2041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2042 ELSE
2043 CLOSE check_uc_instance;
2044 END IF;
2045
2046 --Validate the input parameter p_relationship_id
2047 OPEN check_mc_relationship;
2048 FETCH check_mc_relationship INTO l_check_mc_relationship;
2049 IF check_mc_relationship%NOTFOUND THEN
2050 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
2051 FND_MESSAGE.set_token('NAME', 'relationship_id');
2052 FND_MESSAGE.set_token('VALUE', p_relationship_id);
2053 FND_MSG_PUB.add;
2054 CLOSE check_mc_relationship;
2055 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2056 ELSE
2057 CLOSE check_mc_relationship;
2058 END IF;
2059
2060 --For UC branch top node, only need to ensure item matches, no need to check position key
2061 --and position_reference
2062 l_item_match := AHL_UTIL_UC_PKG.item_match(p_relationship_id,
2063 l_check_uc_instance.inventory_item_id,
2064 l_check_uc_instance.inventory_org_id,
2065 l_check_uc_instance.inventory_revision,
2066 l_check_uc_instance.quantity,
2067 l_check_uc_instance.unit_of_measure);
2068 /*
2069 FOR l_check_mc_relationship IN check_mc_relationship LOOP
2070 IF l_check_uc_instance.inventory_item_id = l_check_mc_relationship.inventory_item_id AND
2071 l_check_uc_instance.inventory_org_id = l_check_mc_relationship.inventory_org_id THEN
2072 l_item_match := TRUE;
2073 EXIT;
2074 END IF;
2075 END LOOP;
2076 */
2077 IF NOT l_item_match THEN
2078 FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_NOT_MATCH' );
2079 FND_MESSAGE.set_token('INSTANCE', p_instance_id);
2080 FND_MSG_PUB.add;
2081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2082 END IF;
2083
2084 --Loop through all the non leaf nodes including the top node
2085 FOR l_get_non_leaf_node IN get_non_leaf_nodes(p_instance_id) LOOP
2086 --Make sure the node doesn't belong to a sub unit or extra node branch, because for
2087 --all the nodes in the sub unit or extra node branch including the top most extra node
2088 --it is not necessary to process them and the sub unit top node itself has been processed in the loop
2089 --of its parent node
2090 OPEN check_subuc_extra(l_get_non_leaf_node.instance_id, p_instance_id);
2091 FETCH check_subuc_extra INTO l_check_subuc_extra;
2092 IF check_subuc_extra%NOTFOUND THEN
2093 --Get all the immediate children of the corresponding MC node
2094 --First get the non leaf node's new relationship_id to which it has been migrated
2095 --This is a bug fix found by Barry on Nov 6, 2003
2096 IF p_instance_id = l_get_non_leaf_node.instance_id THEN
2097 l_new_relationship_id := p_relationship_id;
2098 ELSE
2099 SELECT to_number(position_reference) into l_new_relationship_id
2100 FROM csi_ii_relationships
2101 WHERE subject_id = l_get_non_leaf_node.instance_id
2102 AND relationship_type_code='COMPONENT-OF'
2103 AND trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate)
2104 AND trunc(nvl(active_end_date, sysdate+1)) > trunc(sysdate);
2105 END IF;
2106
2107 i_mc_child := 1;
2108 FOR l_get_mc_child IN get_mc_children(l_new_relationship_id) LOOP
2109 l_mc_children_tbl(i_mc_child).relationship_id := l_get_mc_child.relationship_id;
2110 l_mc_children_tbl(i_mc_child).position_key := l_get_mc_child.position_key;
2111 l_mc_children_tbl(i_mc_child).position_ref_code := l_get_mc_child.position_ref_code;
2112 l_mc_children_tbl(i_mc_child).item_group_id := l_get_mc_child.item_group_id;
2113 l_mc_children_tbl(i_mc_child).matched_flag := 'N'; -- default to 'N'
2114 i_mc_child := i_mc_child + 1;
2115 END LOOP;
2116 FOR l_get_csi_child IN get_csi_children(l_get_non_leaf_node.instance_id) LOOP
2117 --No processing for the extra node
2118 IF l_get_csi_child.relationship_id IS NOT NULL THEN
2119 l_matched := FALSE;
2120 l_relationship_id := NULL;
2121 <<OUTER>>
2122 FOR i IN l_mc_children_tbl.FIRST..l_mc_children_tbl.LAST LOOP
2123 IF l_mc_children_tbl(i).matched_flag <> 'Y' THEN
2124 IF l_get_csi_child.position_key = l_mc_children_tbl(i).position_key OR
2125 l_get_csi_child.position_ref_code = l_mc_children_tbl(i).position_ref_code THEN
2126 --Check whether this node is a sub unit top node, if YES compare the sub
2127 --mc_header_id(not necessary to compare the top node's item), otherwise compare item
2128 OPEN check_sub_uc(l_get_csi_child.instance_id);
2129 FETCH check_sub_uc INTO l_check_sub_uc;
2130 IF check_sub_uc%FOUND THEN
2131 FOR l_get_sub_mc IN get_sub_mcs(l_mc_children_tbl(i).relationship_id) LOOP
2132 IF l_check_sub_uc.mc_header_id = l_get_sub_mc.mc_header_id THEN
2133 l_matched := TRUE;
2134 l_mc_children_tbl(i).matched_flag := 'Y';
2135 l_relationship_id := l_mc_children_tbl(i).relationship_id;
2136 CLOSE check_sub_uc;
2137 EXIT OUTER;
2138 END IF;
2139 END LOOP;
2140 ELSE
2141 l_matched := AHL_UTIL_UC_PKG.item_match(l_mc_children_tbl(i).relationship_id,
2142 l_get_csi_child.inventory_item_id,
2143 l_get_csi_child.inventory_org_id,
2144 l_get_csi_child.inventory_revision,
2145 l_get_csi_child.quantity,
2146 l_get_csi_child.unit_of_measure);
2147 /*
2148 FOR l_get_associated_item IN get_associated_items(l_mc_children_tbl(i).item_group_id) LOOP
2149 IF l_get_csi_child.inventory_item_id = l_get_associated_item.inventory_item_id AND
2150 l_get_csi_child.inventory_org_id = l_get_associated_item.inventory_org_id THEN
2151 l_matched := TRUE;
2152 l_relationship_id := l_mc_children_tbl(i).relationship_id;
2153 EXIT OUTER;
2154 END IF;
2155 END LOOP;
2156 */
2157 IF l_matched THEN
2158 l_mc_children_tbl(i).matched_flag := 'Y';
2159 l_relationship_id := l_mc_children_tbl(i).relationship_id;
2160 CLOSE check_sub_uc;
2161 EXIT OUTER;
2162 END IF;
2163 END IF;
2164 CLOSE check_sub_uc;
2165 --Positions match but items or sub-configs don't match, then we define it as a hard failure
2166 --in Part Change but still an extra node in migration. Both position_key and position_ref_code
2167 --are unique across siblings.
2168 IF NOT l_matched THEN
2169 FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_HARD_FAIL' );
2170 FND_MESSAGE.set_token('INSTANCE', l_get_csi_child.instance_id);
2171 FND_MSG_PUB.add;
2172 RAISE FND_API.G_EXC_ERROR;
2173 END IF;
2174 END IF; --whether position_key or position_ref_code matches
2175 END IF; --whether the MC chilren node has already been matched
2176 END LOOP; --of all the MC children
2177 --The following lines are used to update the position_reference column in csi_ii_relationships
2178 --First, get transaction_type_id .
2179 AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
2180 IF NOT l_return_value THEN
2181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2182 END IF;
2183 --Set the CSI transaction record
2184 l_csi_transaction_rec.source_transaction_date := SYSDATE;
2185 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
2186 --Set CSI relationship record
2187 l_csi_relationship_rec.relationship_id := l_get_csi_child.csi_ii_relationship_id;
2188 l_csi_relationship_rec.object_version_number := l_get_csi_child.csi_ii_relationship_ovn;
2189 l_csi_relationship_rec.position_reference := to_char(l_relationship_id);
2190 l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
2191 l_csi_relationship_rec.object_id := l_get_csi_child.parent_instance_id;
2192 l_csi_relationship_rec.subject_id := l_get_csi_child.instance_id;
2193 l_csi_relationship_tbl(1) := l_csi_relationship_rec;
2194 CSI_II_RELATIONSHIPS_PUB.update_relationship(
2195 p_api_version => 1.0,
2196 p_relationship_tbl => l_csi_relationship_tbl,
2197 p_txn_rec => l_csi_transaction_rec,
2198 x_return_status => l_return_status,
2199 x_msg_count => l_msg_count,
2200 x_msg_data => l_msg_data);
2201 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2202 RAISE FND_API.G_EXC_ERROR;
2203 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2205 END IF;
2206 END IF; --whether the UC node is an extra node
2207 END LOOP; --of all the UC children
2208 END IF; --whether it is a node belonging to subuc or extra node branch
2209 CLOSE check_subuc_extra;
2210 END LOOP; --of all non leaf nodes
2211
2212 -- Added by rbhavsar on July 25, 2007 to remap IB Tree Nodes to fix FP bug 6276991
2213 Remap_IB_Tree(p_instance_id, p_relationship_id);
2214
2215 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2216 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2217 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
2218 'At the end of the procedure');
2219 END IF;
2220 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2221 l_msg_count := FND_MSG_PUB.count_msg;
2222 IF l_msg_count > 0 THEN
2223 x_msg_count := l_msg_count;
2224 RAISE FND_API.G_EXC_ERROR;
2225 END IF;
2226 -- Perform the Commit (if requested)
2227 IF FND_API.to_boolean(p_commit) THEN
2228 COMMIT;
2229 END IF;
2230 -- Count and Get messages (optional)
2231 FND_MSG_PUB.count_and_get(
2232 p_encoded => FND_API.G_FALSE,
2233 p_count => x_msg_count,
2234 p_data => x_msg_data);
2235
2236 EXCEPTION
2237 WHEN FND_API.G_EXC_ERROR THEN
2238 ROLLBACK TO remap_uc_subtree;
2239 x_return_status := FND_API.G_RET_STS_ERROR ;
2240 FND_MSG_PUB.count_and_get(
2241 p_encoded => FND_API.G_FALSE,
2242 p_count => x_msg_count,
2243 p_data => x_msg_data);
2244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2245 ROLLBACK TO remap_uc_subtree;
2246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2247 FND_MSG_PUB.count_and_get(
2248 p_encoded => FND_API.G_FALSE,
2249 p_count => x_msg_count,
2250 p_data => x_msg_data);
2251 WHEN OTHERS THEN
2252 ROLLBACK TO remap_uc_subtree;
2253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2254 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2255 THEN
2256 FND_MSG_PUB.add_exc_msg(
2257 p_pkg_name => G_PKG_NAME,
2258 p_procedure_name => l_api_name,
2259 p_error_text => SUBSTRB(SQLERRM,1,240));
2260 END IF;
2261 FND_MSG_PUB.count_and_get(
2262 p_encoded => FND_API.G_FALSE,
2263 p_count => x_msg_count,
2264 p_data => x_msg_data);
2265 END;
2266
2267 -- Following two procedures added by rbhavsar on July 25, 2007
2268 -- to remap IB Tree Nodes to fix FP bug 6276991
2269 PROCEDURE Remap_IB_Tree(p_instance_id IN NUMBER,
2270 p_relationship_id IN NUMBER)
2271 IS
2272
2273 -- Get all the children of the current instance that are extra nodes
2274 CURSOR get_extra_children_csr IS
2275 SELECT subject_id
2276 FROM csi_ii_relationships
2277 WHERE object_id = p_instance_id
2278 AND relationship_type_code = 'COMPONENT-OF'
2279 AND position_reference IS NULL
2280 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
2281 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2282 AND NOT EXISTS (SELECT 'X'
2283 FROM ahl_unit_config_headers
2284 WHERE csi_item_instance_id = subject_id
2285 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE));
2286
2287 -- Get all the children of the current instance that are NOT extra nodes
2288 CURSOR get_premapped_instances_csr IS
2289 SELECT subject_id, position_reference
2290 FROM csi_ii_relationships
2291 WHERE object_id = p_instance_id
2292 AND relationship_type_code = 'COMPONENT-OF'
2293 AND position_reference IS NOT NULL
2294 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
2295 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2296 AND NOT EXISTS (SELECT 'X'
2297 FROM ahl_unit_config_headers
2298 WHERE csi_item_instance_id = subject_id
2299 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE));
2300
2301 -- Validate that the child position is still valid under the parent position
2302 CURSOR validate_position_csr(c_child_relationship_id IN NUMBER) IS
2303 SELECT 1 from ahl_mc_relationships
2304 where relationship_id = c_child_relationship_id
2305 AND parent_relationship_id = p_relationship_id
2306 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
2307 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2308
2309 -- Get all children of current position that are empty positions
2310 CURSOR get_all_empty_positions_csr IS
2311 SELECT relationship_id
2312 FROM ahl_mc_relationships
2313 WHERE parent_relationship_id = p_relationship_id
2314 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
2315 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
2316 MINUS
2317 SELECT to_number(position_reference) relationship_id
2318 FROM csi_ii_relationships relationship_id
2319 WHERE object_id = p_instance_id
2320 AND relationship_type_code = 'COMPONENT-OF'
2321 AND position_reference IS NOT NULL
2322 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
2323 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2324
2325 CURSOR get_ii_rel_dtls_csr(c_instance_id NUMBER) IS
2326 SELECT C.relationship_id,
2327 C.object_version_number,
2328 C.object_id,
2329 C.subject_id
2330 FROM csi_ii_relationships C
2331 WHERE C.subject_id = c_instance_id
2332 AND C.relationship_type_code = 'COMPONENT-OF'
2333 AND trunc(nvl(C.active_start_date,SYSDATE)) <= trunc(SYSDATE)
2334 AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2335
2336 L_DEBUG_KEY CONSTANT VARCHAR2(150) := 'ahl.plsql.AHL_UC_TREE_PVT.Remap_IB_Tree';
2337 l_extra_instances_tbl T_ID_TBL;
2338 l_relations_tbl T_ID_TBL;
2339 l_premapped_instances_tbl T_ID_TBL;
2340 l_temp NUMBER;
2341 l_csi_relationship_rec csi_datastructures_pub.ii_relationship_rec;
2342 l_csi_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
2343 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
2344 l_ii_rel_dtls get_ii_rel_dtls_csr%ROWTYPE;
2345 l_return_status VARCHAR2(1);
2346 l_msg_count NUMBER;
2347 l_msg_data VARCHAR2(2000);
2348 l_return_value BOOLEAN;
2349 l_transaction_type_id NUMBER;
2350
2351 BEGIN
2352 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2353 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin',
2354 'At the start of the procedure, p_instance_id = ' || p_instance_id || ', p_relationship_id = ' || p_relationship_id);
2355 END IF;
2356
2357 -- Get the transaction_type_id for use later by the CSI_II_RELATIONSHIPS_PUB.update_relationship API
2358 AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE', l_transaction_type_id, l_return_value);
2359 IF NOT l_return_value THEN
2360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2361 END IF;
2362 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
2363 l_csi_transaction_rec.source_transaction_date := SYSDATE;
2364 l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
2365
2366 -- Get all nodes that are non-extra nodes
2367 FOR l_nonextra_instances IN get_premapped_instances_csr LOOP
2368 -- Validate if the instance still matches the position
2369 -- Check if l_nonextra_instances.position_reference is under the position p_relationship_id
2370 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2371 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2372 'Non Extra Node: l_nonextra_instances.subject_id = ' || l_nonextra_instances.subject_id ||
2373 ', l_nonextra_instances.position_reference = ' || l_nonextra_instances.position_reference);
2374 END IF;
2375 OPEN validate_position_csr(TO_NUMBER(l_nonextra_instances.position_reference));
2376 FETCH validate_position_csr INTO l_temp;
2377 IF (validate_position_csr%NOTFOUND) THEN
2378 -- Reset the position reference so that the instance becomes an extra node node
2379 OPEN get_ii_rel_dtls_csr( l_nonextra_instances.subject_id);
2380 FETCH get_ii_rel_dtls_csr INTO l_ii_rel_dtls;
2381 CLOSE get_ii_rel_dtls_csr;
2382 --Set CSI relationship record
2383 l_csi_relationship_rec.relationship_id := l_ii_rel_dtls.relationship_id;
2384 l_csi_relationship_rec.object_version_number := l_ii_rel_dtls.object_version_number;
2385 l_csi_relationship_rec.position_reference := null; -- Nullify the relationship
2386 l_csi_relationship_rec.object_id := l_ii_rel_dtls.object_id;
2387 l_csi_relationship_rec.subject_id := l_ii_rel_dtls.subject_id;
2388 l_csi_relationship_tbl(1) := l_csi_relationship_rec;
2389 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2390 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2391 'About to call CSI_II_RELATIONSHIPS_PUB.update_relationship to update CSI II relationship with id ' ||
2392 l_ii_rel_dtls.relationship_id || ' between ' ||
2393 l_ii_rel_dtls.object_id || ' (object) and ' ||
2394 l_ii_rel_dtls.subject_id || '(subject) with NULL position_reference ');
2395 END IF;
2396 CSI_II_RELATIONSHIPS_PUB.update_relationship(
2397 p_api_version => 1.0,
2398 p_relationship_tbl => l_csi_relationship_tbl,
2399 p_txn_rec => l_csi_transaction_rec,
2400 x_return_status => l_return_status,
2401 x_msg_count => l_msg_count,
2402 x_msg_data => l_msg_data);
2403 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2404 RAISE FND_API.G_EXC_ERROR;
2405 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2407 END IF;
2408 ELSE
2409 -- Position Matches at current level: Drill down by calling Remap_IB_Tree recursively
2410 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2411 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2412 'About to recursively call Remap_IB_Tree with p_instance_id = ' || l_nonextra_instances.subject_id ||
2413 ', p_relationship_id =' || l_nonextra_instances.position_reference);
2414 END IF;
2415 Remap_IB_Tree(p_instance_id => l_nonextra_instances.subject_id,
2416 p_relationship_id => TO_NUMBER(l_nonextra_instances.position_reference));
2417 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2418 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2419 'Returned from recursive call to Remap_IB_Tree.');
2420 END IF;
2421 END IF;
2422 CLOSE validate_position_csr;
2423 END LOOP; -- All non-extra nodes
2424
2425 -- Get all the children of the current instance that are extra nodes
2426 OPEN get_extra_children_csr;
2427 FETCH get_extra_children_csr BULK COLLECT INTO l_extra_instances_tbl;
2428 CLOSE get_extra_children_csr;
2429
2430 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2431 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2432 'Number of extra nodes: ' || l_extra_instances_tbl.COUNT ||
2433 ', Number of non-extra nodes: ' || l_premapped_instances_tbl.COUNT);
2434 END IF;
2435 IF (l_extra_instances_tbl.COUNT > 0) THEN
2436 -- There are child nodes present
2437 -- Get all children of current position that are empty positions
2438 OPEN get_all_empty_positions_csr;
2439 FETCH get_all_empty_positions_csr BULK COLLECT INTO l_relations_tbl;
2440 CLOSE get_all_empty_positions_csr;
2441
2442 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2443 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2444 'Number of empty positions: ' || l_relations_tbl.COUNT);
2445 END IF;
2446 IF (l_relations_tbl.COUNT > 0) THEN
2447 -- Analyse and process instances with matching positions
2448 Process_Instances(p_x_extra_instances_tbl => l_extra_instances_tbl,
2449 p_x_relations_tbl => l_relations_tbl);
2450 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2451 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2452 'Number of extra nodes after processing: ' || l_extra_instances_tbl.COUNT ||
2453 ', Number of empty positions after processing: ' || l_relations_tbl.COUNT);
2454 END IF;
2455 END IF;
2456 END IF; -- There are child nodes
2457
2458 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2459 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end',
2460 'At the end of the procedure.');
2461 END IF;
2462 END Remap_IB_Tree;
2463
2464 -------
2465 -- This procedure is called by Remap_IB_Tree and checks if an instance matches an unique
2466 -- relationship and if so, sets the position_reference. All relations are searched and
2467 -- if multiple are matching, position_reference is not set.
2468 -- This procedure also calls Remap_IB_Tree (mutual recursion) to process children.
2469 PROCEDURE Process_instances(p_x_extra_instances_tbl IN OUT NOCOPY T_ID_TBL,
2470 p_x_relations_tbl IN OUT NOCOPY T_ID_TBL) IS
2471
2472 CURSOR get_instance_dtls_csr(c_instance_id IN NUMBER) IS
2473 SELECT inventory_item_id,
2474 inv_master_organization_id,
2475 inventory_revision,
2476 quantity,
2477 unit_of_measure
2478 FROM csi_item_instances
2479 WHERE instance_id = c_instance_id;
2480
2481 CURSOR get_ii_rel_dtls_csr(c_instance_id NUMBER) IS
2482 SELECT C.relationship_id,
2483 C.object_version_number,
2484 C.object_id,
2485 C.subject_id
2486 FROM csi_ii_relationships C
2487 WHERE C.subject_id = c_instance_id
2488 AND C.relationship_type_code = 'COMPONENT-OF'
2489 AND trunc(nvl(C.active_start_date,SYSDATE)) <= trunc(SYSDATE)
2490 AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
2491
2492 l_instance_dtls get_instance_dtls_csr%ROWTYPE;
2493 l_ii_rel_dtls get_ii_rel_dtls_csr%ROWTYPE;
2494 L_DEBUG_KEY CONSTANT VARCHAR2(150) := 'ahl.plsql.AHL_UC_TREE_PVT.Process_instances';
2495 l_map_tbl T_ID_TBL;
2496 l_item_match BOOLEAN;
2497 l_csi_relationship_rec csi_datastructures_pub.ii_relationship_rec;
2498 l_csi_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
2499 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
2500 l_return_value BOOLEAN;
2501 l_transaction_type_id NUMBER;
2502 l_return_status VARCHAR2(1);
2503 l_msg_count NUMBER;
2504 l_msg_data VARCHAR2(2000);
2505 i NUMBER;
2506 j NUMBER;
2507 k NUMBER;
2508 l_matched_flag BOOLEAN;
2509 l_current_position NUMBER;
2510
2511 BEGIN
2512 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2513 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin',
2514 'At the start of the procedure, p_x_extra_instances_tbl.count = ' || p_x_extra_instances_tbl.count || ',p_x_relations_tbl.count = ' ||p_x_relations_tbl.count);
2515 END IF;
2516
2517 -- Get the transaction_type_id for use later by the CSI_II_RELATIONSHIPS_PUB.update_relationship API
2518 AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE', l_transaction_type_id, l_return_value);
2519 IF NOT l_return_value THEN
2520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2521 END IF;
2522 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
2523 l_csi_transaction_rec.source_transaction_date := SYSDATE;
2524 l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
2525
2526 -- There are extra nodes present
2527 i := p_x_extra_instances_tbl.FIRST;
2528 WHILE (i IS NOT NULL) LOOP
2529 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2530 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2531 'i = ' || i || ', p_x_extra_instances_tbl(i) = ' || p_x_extra_instances_tbl(i));
2532 END IF;
2533 -- Initialize associative array
2534 l_map_tbl(p_x_extra_instances_tbl(i)) := 0;
2535 OPEN get_instance_dtls_csr(p_x_extra_instances_tbl(i));
2536 FETCH get_instance_dtls_csr INTO l_instance_dtls;
2537 CLOSE get_instance_dtls_csr;
2538 j := p_x_relations_tbl.FIRST;
2539 WHILE (j IS NOT NULL) LOOP
2540 -- See if instance i matches position j
2541 l_item_match := AHL_UTIL_UC_PKG.item_match(p_x_relations_tbl(j),
2542 l_instance_dtls.inventory_item_id,
2543 l_instance_dtls.inv_master_organization_id,
2544 l_instance_dtls.inventory_revision,
2545 l_instance_dtls.quantity,
2546 l_instance_dtls.unit_of_measure);
2547 IF (l_item_match) THEN
2548 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2549 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2550 'Instance ' || p_x_extra_instances_tbl(i) || ' fits position ' || p_x_relations_tbl(j));
2551 END IF;
2552 IF (l_map_tbl(p_x_extra_instances_tbl(i))) <> 0 THEN
2553 -- More than one match
2554 l_map_tbl(p_x_extra_instances_tbl(i)) := -1;
2555 EXIT; -- No need to check more positions: go to the next instance
2556 ELSE
2557 -- First Match
2558 l_map_tbl(p_x_extra_instances_tbl(i)) := p_x_relations_tbl(j);
2559 END IF;
2560 END IF; -- l_item_match is true
2561 j := p_x_relations_tbl.NEXT(j);
2562 END LOOP; -- For all Positions
2563 i := p_x_extra_instances_tbl.NEXT(i);
2564 END LOOP; -- For all instances (Extra Nodes)
2565
2566 -- Now that analysis is done, process the instances
2567 i := p_x_extra_instances_tbl.FIRST;
2568 WHILE (i IS NOT NULL) LOOP
2569 IF (l_map_tbl(p_x_extra_instances_tbl(i))) = 0 THEN
2570 -- No Matching position found: Delete and Ignore this instance
2571 -- Instance will continue to remain an extra node.
2572 p_x_extra_instances_tbl.DELETE(i);
2573 ELSIF (l_map_tbl(p_x_extra_instances_tbl(i))) > 0 THEN
2574 -- Exactly one matching position has been found: Check if this matched relationship
2575 -- is also a match for any other instance
2576 l_current_position := l_map_tbl(p_x_extra_instances_tbl(i));
2577 k := p_x_extra_instances_tbl.NEXT(i);
2578 l_matched_flag := FALSE;
2579 WHILE (k IS NOT NULL) LOOP
2580 IF (l_map_tbl(p_x_extra_instances_tbl(k)) = l_current_position) THEN
2581 -- Another instance also has been matched to this position: Ignore that instance
2582 l_map_tbl(p_x_extra_instances_tbl(k)) := -1;
2583 l_matched_flag := TRUE;
2584 END IF;
2585 k := p_x_extra_instances_tbl.NEXT(k);
2586 END LOOP;
2587 IF (l_matched_flag = TRUE) THEN
2588 -- One or more instance also has been matched to this instance's position:
2589 -- Ignore the current instance. Let the user associate manually.
2590 l_map_tbl(p_x_extra_instances_tbl(i)) := -1;
2591 ELSE
2592 -- Exactly one matching position has been found
2593 -- Set the Position reference for the csi_ii_relationship
2594 OPEN get_ii_rel_dtls_csr(p_x_extra_instances_tbl(i));
2595 FETCH get_ii_rel_dtls_csr INTO l_ii_rel_dtls;
2596 CLOSE get_ii_rel_dtls_csr;
2597 --Set CSI relationship record
2598 l_csi_relationship_rec.relationship_id := l_ii_rel_dtls.relationship_id;
2599 l_csi_relationship_rec.object_version_number := l_ii_rel_dtls.object_version_number;
2600 l_csi_relationship_rec.position_reference := to_char(l_current_position);
2601 l_csi_relationship_rec.object_id := l_ii_rel_dtls.object_id;
2602 l_csi_relationship_rec.subject_id := l_ii_rel_dtls.subject_id;
2603 l_csi_relationship_tbl(1) := l_csi_relationship_rec;
2604 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2605 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2606 'About to call CSI_II_RELATIONSHIPS_PUB.update_relationship to update CSI II relationship with id ' ||
2607 l_ii_rel_dtls.relationship_id || ' between ' ||
2608 l_ii_rel_dtls.object_id || ' (object) and ' ||
2609 l_ii_rel_dtls.subject_id || '(subject) with position_reference ' ||
2610 to_char(l_current_position));
2611 END IF;
2612 CSI_II_RELATIONSHIPS_PUB.update_relationship(
2613 p_api_version => 1.0,
2614 p_relationship_tbl => l_csi_relationship_tbl,
2615 p_txn_rec => l_csi_transaction_rec,
2616 x_return_status => l_return_status,
2617 x_msg_count => l_msg_count,
2618 x_msg_data => l_msg_data);
2619 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2620 RAISE FND_API.G_EXC_ERROR;
2621 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2623 END IF;
2624 -- Now recursively match all the children of the current instance/position
2625 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2626 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2627 'About to recursively call Remap_IB_Tree');
2628 END IF;
2629 Remap_IB_Tree(p_instance_id => p_x_extra_instances_tbl(i),
2630 p_relationship_id => l_current_position);
2631 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2632 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
2633 'Returned from recursive call to Remap_IB_Tree');
2634 END IF;
2635 -- Now delete the matched relationship
2636 j := p_x_relations_tbl.FIRST;
2637 WHILE (j IS NOT NULL) LOOP
2638 IF p_x_relations_tbl(j) = l_current_position THEN
2639 p_x_relations_tbl.DELETE(j);
2640 EXIT;
2641 END IF;
2642 j := p_x_relations_tbl.NEXT(j);
2643 END LOOP;
2644 -- Now delete the matched instance
2645 p_x_extra_instances_tbl.DELETE(i);
2646 END IF; -- l_matched_flag is TRUE or FALSE
2647 ELSE
2648 -- Multiple matching positions found: Do nothing and let the user associate manually
2649 null;
2650 END IF;
2651 i := p_x_extra_instances_tbl.NEXT(i);
2652 END LOOP;
2653 l_map_tbl.DELETE; -- Clear up the temporary Associative Array
2654
2655 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2656 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, L_DEBUG_KEY || '.end',
2657 'At the end of the procedure. Remaining Instances: ' || p_x_extra_instances_tbl.count);
2658 END IF;
2659 END Process_instances;
2660
2661
2662 END AHL_UC_TREE_PVT; -- Package body
[Home] [Help]