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