[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_TREE_PVT
Source
1 PACKAGE BODY AHL_MC_TREE_PVT AS
2 /* $Header: AHLVMCTB.pls 120.2 2006/01/09 05:25:37 sagarwal noship $ */
3
4 -------------------
5 -- Common variables
6 -------------------
7 l_dummy_varchar VARCHAR2(1);
8 l_dummy_number NUMBER;
9
10 -------------------
11 -- Private type --
12 -------------------
13 TYPE Nodes_for_parent_rec IS RECORD (
14 RELATIONSHIP_ID NUMBER,
15 OBJECT_VERSION_NUMBER NUMBER,
16 POSITION_KEY NUMBER,
17 PARENT_RELATIONSHIP_ID NUMBER,
18 ITEM_GROUP_ID NUMBER,
19 POSITION_REF_CODE VARCHAR2(30),
20 POSITION_REF_MEANING VARCHAR2(80),
21 --R12
22 --priyan MEL-CDL
23 ATA_CODE VARCHAR2(30),
24 ATA_MEANING VARCHAR2(80),
25 POSITION_NECESSITY_CODE VARCHAR2(30),
26 POSITION_NECESSITY_MEANING VARCHAR2(80),
27 UOM_CODE VARCHAR2(3),
28 QUANTITY NUMBER,
29 DISPLAY_ORDER NUMBER,
30 ACTIVE_START_DATE DATE,
31 ACTIVE_END_DATE DATE,
32 MC_HEADER_ID NUMBER,
33 MC_ID NUMBER,
34 VERSION_NUMBER NUMBER,
35 CONFIG_STATUS_CODE VARCHAR2(30));
36
37 TYPE Nodes_for_parent_ref_csr IS REF CURSOR RETURN Nodes_for_parent_rec;
38
39
40 ------------------------------
41 -- Declare local procedures --
42 ------------------------------
43 FUNCTION Decode_Pos_Path
44 (
45 p_encoded_path IN VARCHAR2
46 )
47 RETURN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
48
49 PROCEDURE Get_nodes_for_parent(
50 p_end_date IN DATE,
51 p_relationship_id IN NUMBER,
52 p_is_sub_config_node IN VARCHAR2,
53 p_is_top_config_node IN VARCHAR2,
54 p_mc_header_id IN NUMBER,
55 p_parent_rel_id IN NUMBER,
56 p_Get_nodes_csr IN OUT NOCOPY Nodes_for_parent_ref_csr
57 );
58
59
60
61 ----------------------------
62 -- Get_MasterConfig_Nodes --
63 ----------------------------
64 -- Key to call this API, input params to be passed
65 -- MC Root Node
66 -- p_mc_header_id = <header-id of the MC>
67 -- p_parent_rel_id = null
68 -- p_is_parent_subconfig = 'F'
69 -- p_parent_pos_path = null
70 -- p_is_top_config_node = 'T'
71 -- p_is_sub_config_node = 'F'
72 --
73 -- MC Node (except root node)
74 -- p_mc_header_id = <header-id of the MC>
75 -- p_parent_rel_id = <relationship-id of the MC Node>
76 -- p_is_parent_subconfig = 'F'
77 -- p_parent_pos_path = <position path of the parent-node>
78 -- p_is_top_config_node = 'F'
79 -- p_is_sub_config_node = 'F'
80 --
81 -- Subconfig Root Node (any level deep)
82 -- p_mc_header_id = <header-id of the subconfig>
83 -- p_parent_rel_id = <relationship-id of the MC node to which the subconfig is to be attached>
84 -- p_is_parent_subconfig = 'F' (if the MC node to which the subconfig is to be attached is not a subconfig itself, else 'T'>
85 -- p_parent_pos_path = <position path of the MC node to which the subconfig is to be attached>
86 -- p_is_top_config_node = 'T'
87 -- p_is_sub_config_node = 'T'
88 --
89 -- Subconfig Node (except root node, any level deep)
90 -- p_mc_header_id = <header-id of the subconfig>
91 -- p_parent_rel_id = <relationship-id of the subconfig node>
92 -- p_is_parent_subconfig = 'T'
93 -- p_parent_pos_path = <position path of the subconfig node>
94 -- p_is_top_config_node = 'F'
95 -- p_is_sub_config_node = 'T'
96 --
97
98
99
100 PROCEDURE Get_MasterConfig_Nodes
101 (
102 p_api_version IN NUMBER,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2,
106 p_mc_header_id IN NUMBER,
107 p_parent_rel_id IN NUMBER,
108 p_is_parent_subconfig IN VARCHAR2 := 'F',
109 p_parent_pos_path IN VARCHAR2,
110 p_is_top_config_node IN VARCHAR2 := 'F',
111 p_is_sub_config_node IN VARCHAR2 := 'F',
112 x_tree_node_tbl OUT NOCOPY Tree_Node_Tbl_Type
113 )
114 IS
115
116
117
118 -- Define cursor to get the number of children for a particular node
119 CURSOR get_num_children
120 (
121 p_rel_id IN NUMBER
122 )
123 IS
124 SELECT NVL(COUNT(*), 0) NUM_CHILDREN
125 FROM AHL_MC_RELATIONSHIPS
126 WHERE PARENT_RELATIONSHIP_ID = p_rel_id;
127
128 -- Define cursor to check whether the particular node has any subconfig_association
129 CURSOR check_subconfig_assos
130 (
131 p_relationship_id IN NUMBER
132 )
133 IS
134 SELECT 'x'
135 FROM AHL_MC_CONFIG_RELATIONS
136 WHERE RELATIONSHIP_ID = p_relationship_id;
137
138 -- Define cursor to get check whether MC exists, also retrieve relationship_id of the topnode
139 CURSOR check_mc_exists
140 (
141 p_mc_header_id IN NUMBER
142 )
143 IS
144 SELECT RELATIONSHIP_ID,
145 ACTIVE_END_DATE
146 FROM AHL_MC_RELATIONSHIPS
147 WHERE MC_HEADER_ID = p_mc_header_id AND
148 PARENT_RELATIONSHIP_ID IS NULL;
149
150 -- Define cursor to retrieve the position path id given a position path
151 CURSOR get_pos_path_id
152 (
153 p_position_path IN VARCHAR2
154 )
155 IS
156 SELECT NVL(path_position_id, 0)
157 FROM ahl_mc_path_positions
158 WHERE encoded_path_position = p_position_path;
159
160 l_api_name CONSTANT VARCHAR2(30) := 'Get_MasterConfig_Nodes';
161 l_api_version CONSTANT NUMBER := 1.0;
162
163 l_tree_node_rec AHL_MC_TREE_PVT.Tree_node_rec_type;
164 l_topnode_rec Nodes_for_parent_rec;
165 l_tree_index NUMBER := 0;
166
167 l_pos_ref_code VARCHAR2(30);
168 l_pos_ref_meaning VARCHAR2(80);
169 l_ret_val BOOLEAN;
170 l_active_end_date DATE;
171 l_pos_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
172 Nodes_list_for_parent Nodes_for_parent_ref_csr;
173
174 BEGIN
175
176 -- Standard start of API savepoint
177 SAVEPOINT Get_MasterConfig_Nodes_SP;
178
179 -- Standard call to check for call compatibility
180 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
181 THEN
182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
183 END IF;
184
185 -- Initialize message list if p_init_msg_list is set to TRUE
186 FND_MSG_PUB.Initialize;
187
188 -- Initialize API return status to success
189 x_return_status := FND_API.G_RET_STS_SUCCESS;
190
191 -- API body starts here
192 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
193 THEN
194 fnd_log.string
195 (
196 fnd_log.level_procedure,
197 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
198 'At the start of PLSQL procedure'
199 );
200 END IF;
201
202 -- Log all input params
203 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
204 THEN
205 fnd_log.string
206 (
207 fnd_log.level_statement,
208 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
209 'IN -- [p_mc_header_id = '||p_mc_header_id||'] [p_parent_rel_id = '||p_parent_rel_id||'] [p_is_parent_subconfig = '||p_is_parent_subconfig||']'
210 );
211
212 fnd_log.string
213 (
214 fnd_log.level_statement,
215 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
216 'IN -- [p_parent_pos_path = '||p_parent_pos_path||'] [p_is_top_config_node = '||p_is_top_config_node||'] [p_is_sub_config_node = '||p_is_sub_config_node||']'
217 );
218 END IF;
219
220 -- Verify MC exists, retrieve relationship_id of the topnode also
221 OPEN check_mc_exists ( p_mc_header_id);
222 FETCH check_mc_exists INTO l_dummy_number,l_active_end_date;
223 IF (check_mc_exists%NOTFOUND)
224 THEN
225 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_FOUND');
226 FND_MSG_PUB.ADD;
227 RAISE FND_API.G_EXC_ERROR;
228 END IF;
229 CLOSE check_mc_exists;
230
231
232
233 Get_nodes_for_parent (
234 p_end_date => l_active_end_date,
235 p_relationship_id => l_dummy_number,
236 p_is_sub_config_node => p_is_sub_config_node,
237 p_is_top_config_node => p_is_top_config_node,
238 p_mc_header_id => p_mc_header_id,
239 p_parent_rel_id => p_parent_rel_id,
240 p_Get_nodes_csr => Nodes_list_for_parent
241 );
242 -- Iterate through all retrieved nodes for the particular query
243 LOOP
244 FETCH Nodes_list_for_parent INTO l_topnode_rec;
245 EXIT WHEN Nodes_list_for_parent%NOTFOUND;
246
247
248 -- Populate all output params
249
250 -- Populate MC node params
251 l_tree_node_rec.RELATIONSHIP_ID := l_topnode_rec.RELATIONSHIP_ID;
252 l_tree_node_rec.OBJECT_VERSION_NUMBER := l_topnode_rec.OBJECT_VERSION_NUMBER;
253 l_tree_node_rec.POSITION_KEY := l_topnode_rec.POSITION_KEY;
254 l_tree_node_rec.ITEM_GROUP_ID := l_topnode_rec.ITEM_GROUP_ID;
255 l_tree_node_rec.POSITION_NECESSITY_CODE := l_topnode_rec.POSITION_NECESSITY_CODE;
256 l_tree_node_rec.POSITION_NECESSITY_MEANING := l_topnode_rec.POSITION_NECESSITY_MEANING;
257 --R12
258 --priyan MEL-CDL
259 l_tree_node_rec.ATA_CODE := l_topnode_rec.ATA_CODE;
260 l_tree_node_rec.ATA_MEANING := l_topnode_rec.ATA_MEANING;
261
262 l_tree_node_rec.UOM_CODE := l_topnode_rec.UOM_CODE;
263 l_tree_node_rec.QUANTITY := l_topnode_rec.QUANTITY;
264 l_tree_node_rec.DISPLAY_ORDER := l_topnode_rec.DISPLAY_ORDER;
265 l_tree_node_rec.ACTIVE_START_DATE := l_topnode_rec.ACTIVE_START_DATE;
266 l_tree_node_rec.ACTIVE_END_DATE := l_topnode_rec.ACTIVE_END_DATE;
267
268 -- Populate MC specific pars (for position path)
269 l_tree_node_rec.MC_HEADER_ID := l_topnode_rec.MC_HEADER_ID;
270 l_tree_node_rec.MC_ID := l_topnode_rec.MC_ID;
271 l_tree_node_rec.VERSION_NUMBER := l_topnode_rec.VERSION_NUMBER;
272
273 -- Populate number of children
274 OPEN get_num_children (l_topnode_rec.RELATIONSHIP_ID);
275 FETCH get_num_children INTO l_tree_node_rec.NUM_CHILD_NODES;
276 CLOSE get_num_children;
277
278 -- Check whether node has any subconfiguration params, populate the same
279 OPEN check_subconfig_assos (l_topnode_rec.RELATIONSHIP_ID);
280 FETCH check_subconfig_assos INTO l_dummy_varchar;
281 IF (check_subconfig_assos%FOUND)
282 THEN
283 l_tree_node_rec.HAS_SUBCONFIGS := 'T';
284 ELSE
285 l_tree_node_rec.HAS_SUBCONFIGS := 'F';
286 END IF;
287 CLOSE check_subconfig_assos;
288
289 -- Populate output param flags
290
291 IF (p_is_top_config_node = 'T')
292 -- Implies that it is a rootnode, also for subconfig rootnode
293 THEN
294 l_tree_node_rec.IS_SUBCONFIG_NODE := 'F';
295 l_tree_node_rec.IS_PARENT_SUBCONFIG :='F';
296
297 -- If is is topnode, populate config_satus_code
298 l_tree_node_rec.CONFIG_STATUS_CODE := l_topnode_rec.CONFIG_STATUS_CODE;
299 END IF;
300
301 IF (p_is_sub_config_node = 'T')
302 -- Implies that it is a subconfig node, also for subconfig rootnode
303 THEN
304 l_tree_node_rec.IS_SUBCONFIG_NODE := 'T';
305 l_tree_node_rec.IS_PARENT_SUBCONFIG := p_is_parent_subconfig;
306 END IF ;
307
308 IF (p_is_sub_config_node = 'T' AND p_is_top_config_node = 'T')
309 -- Implies that it is the rootnode of a subconfig, will be true only in the case of a subconfig expand call
310 THEN
311 l_tree_node_rec.PARENT_RELATIONSHIP_ID := p_parent_rel_id;
312 -- l_tree_node_rec.IS_SUBCONFIG_TOPNODE := 'T';
313 l_tree_node_rec.IS_SUBCONFIG_TOPNODE := 'F';
314
315 -- Populate position path
316 l_tree_node_rec.POSITION_PATH := AHL_MC_PATH_POSITION_PVT.get_encoded_path
317 (
318 p_parent_pos_path,
319 l_tree_node_rec.MC_ID,
320 l_tree_node_rec.VERSION_NUMBER,
321 l_tree_node_rec.POSITION_KEY,
322 'T'
323 );
324 ELSE
325 l_tree_node_rec.PARENT_RELATIONSHIP_ID := l_topnode_rec.PARENT_RELATIONSHIP_ID;
326 l_tree_node_rec.IS_SUBCONFIG_TOPNODE := 'F';
327
328 IF (p_is_top_config_node = 'T' AND p_is_sub_config_node = 'F')
329 -- Implies that it is the rootnode of the MC, will be true only once, in the case of the first call to the API
330 THEN
331 -- Populate position path
332 l_tree_node_rec.POSITION_PATH := to_char(l_tree_node_rec.MC_ID)||':'||to_char(l_tree_node_rec.VERSION_NUMBER)||':'||l_tree_node_rec.POSITION_KEY;
333 ELSE
334 -- Populate position path
335 l_tree_node_rec.POSITION_PATH := AHL_MC_PATH_POSITION_PVT.get_encoded_path
336 (
337 p_parent_pos_path,
338 l_tree_node_rec.MC_ID,
339 l_tree_node_rec.VERSION_NUMBER,
340 l_tree_node_rec.POSITION_KEY,
341 'F'
342 );
343 END IF;
344
345 END IF;
346
347 -- Retrieve position path id
348 OPEN get_pos_path_id(l_tree_node_rec.POSITION_PATH);
349 FETCH get_pos_path_id INTO l_tree_node_rec.POSITION_PATH_ID;
350 CLOSE get_pos_path_id;
351
352 -- Reset flags to read position path specific position ref codes...
353 l_pos_ref_code := NULL;
354 l_pos_ref_meaning := NULL;
355 l_ret_val := FALSE;
356
357 -- Only if it is a subconfig node, we should query for position path specific position reference code
358 IF (p_is_sub_config_node = 'T')
359 THEN
360 IF (l_pos_path_tbl.COUNT > 0)
361 THEN
362 FOR i IN l_pos_path_tbl.FIRST..l_pos_path_tbl.LAST
363 LOOP
364 l_pos_path_tbl(i).MC_ID := NULL;
365 l_pos_path_tbl(i).VERSION_NUMBER := NULL;
366 l_pos_path_tbl(i).POSITION_KEY := NULL;
367 END LOOP;
368 END IF;
369
370 -- Decode the retrieved position path to a position path table, since the input to get_posref_by_path is a position path table
371 l_pos_path_tbl := Decode_Pos_Path(l_tree_node_rec.POSITION_PATH);
372
373 -- Retrieve the position path specific position reference code, retrieves default position reference if no position path has already been created
374 l_pos_ref_code := AHL_MC_PATH_POSITION_PVT.get_posref_by_path(l_pos_path_tbl, FND_API.G_TRUE);
375
376 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning
377 (
378 'AHL_POSITION_REFERENCE',
379 l_pos_ref_code,
380 l_pos_ref_meaning,
381 l_ret_val
382 );
383
384 IF (l_ret_val = TRUE)
385 THEN
386 l_tree_node_rec.POSITION_REF_CODE := l_pos_ref_code;
387 l_tree_node_rec.POSITION_REF_MEANING := l_pos_ref_meaning;
388 ELSE
389 l_tree_node_rec.POSITION_REF_CODE := l_topnode_rec.POSITION_REF_CODE;
390 l_tree_node_rec.POSITION_REF_MEANING := l_topnode_rec.POSITION_REF_MEANING;
391 END IF;
392 ELSE
393 l_tree_node_rec.POSITION_REF_CODE := l_topnode_rec.POSITION_REF_CODE;
394 l_tree_node_rec.POSITION_REF_MEANING := l_topnode_rec.POSITION_REF_MEANING;
395 END IF;
396
397 -- Log some output params
398 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
399 THEN
400 fnd_log.string
401 (
402 fnd_log.level_statement,
403 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
404 'OUT -- [num_chilren = '||l_tree_node_rec.NUM_CHILD_NODES||'] [has_subconfigs = '||l_tree_node_rec.HAS_SUBCONFIGS||'] [is_subconfig = '||l_tree_node_rec.IS_SUBCONFIG_NODE||']'
405 );
406
407 fnd_log.string
408 (
409 fnd_log.level_statement,
410 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
411 'OUT -- [subconfig_top = '||l_tree_node_rec.IS_SUBCONFIG_TOPNODE||'] [parent_subconfig = '||l_tree_node_rec.IS_PARENT_SUBCONFIG||']'
412 );
413
414 fnd_log.string
415 (
416 fnd_log.level_statement,
417 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
418 'OUT -- [position_path = '||l_tree_node_rec.POSITION_PATH||'] [position_path_id = '||l_tree_node_rec.POSITION_PATH_ID||']'
419 );
420 END IF;
421
422 -- Add the tree node record to the output table
423 l_tree_index := l_tree_index + 1;
424 x_tree_node_tbl(l_tree_index) := l_tree_node_rec;
425
426 END LOOP;
427 CLOSE Nodes_list_for_parent;
428 -- API body ends here
429
430 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
431 THEN
432 fnd_log.string
433 (
434 fnd_log.level_procedure,
435 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
436 'At the end of PLSQL procedure'
437 );
438 END IF;
439 -- API body ends here
440
441 -- Check Error Message stack.
442 x_msg_count := FND_MSG_PUB.count_msg;
443 IF x_msg_count > 0
444 THEN
445 RAISE FND_API.G_EXC_ERROR;
446 END IF;
447
448 -- Standard call to get message count and if count is 1, get message info
449 FND_MSG_PUB.count_and_get
450 (
451 p_count => x_msg_count,
452 p_data => x_msg_data,
453 p_encoded => FND_API.G_FALSE
454 );
455
456 EXCEPTION
457 WHEN FND_API.G_EXC_ERROR THEN
458 x_return_status := FND_API.G_RET_STS_ERROR;
459 Rollback to Get_MasterConfig_Nodes_SP;
460 FND_MSG_PUB.count_and_get
461 (
462 p_count => x_msg_count,
463 p_data => x_msg_data,
464 p_encoded => FND_API.G_FALSE
465 );
466
467 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
469 Rollback to Get_MasterConfig_Nodes_SP;
470 FND_MSG_PUB.count_and_get
471 (
472 p_count => x_msg_count,
473 p_data => x_msg_data,
474 p_encoded => FND_API.G_FALSE
475 );
476
477 WHEN OTHERS THEN
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 Rollback to Get_MasterConfig_Nodes_SP;
480 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
481 THEN
482 FND_MSG_PUB.add_exc_msg
483 (
484 p_pkg_name => G_PKG_NAME,
485 p_procedure_name => 'Get_MasterConfig_Nodes',
486 p_error_text => SUBSTR(SQLERRM,1,240)
487 );
488 END IF;
489 FND_MSG_PUB.count_and_get
490 (
491 p_count => x_msg_count,
492 p_data => x_msg_data,
493 p_encoded => FND_API.G_FALSE
494 );
495
496 END Get_MasterConfig_Nodes;
497
498 ---------------------
499 -- Decode_Pos_Path --
500 ---------------------
501 FUNCTION Decode_Pos_Path
502 (
503 p_encoded_path IN VARCHAR2
504 )
505 RETURN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type
506 IS
507
508 G_NODE_SEP VARCHAR2(1) := '/';
509 G_ID_SEP VARCHAR2(1) := ':';
510
511 l_node_start NUMBER := 0;
512 l_node_end NUMBER := 0;
513 l_node_str VARCHAR2(32);
514
515 l_id_start NUMBER := 0;
516 l_id_end NUMBER := 0;
517 l_id_str VARCHAR2(10);
518
519 l_position_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
520 l_tbl_idx NUMBER := 0;
521
522 BEGIN
523
524 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
525 THEN
526 fnd_log.string
527 (
528 fnd_log.level_procedure,
529 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path.begin',
530 'At the start of PLSQL procedure'
531 );
532 END IF;
533
534 LOOP
535 -- Tokenize input encoded position path string using node separator
536 l_node_end := INSTR(p_encoded_path, G_NODE_SEP, l_node_start + 1);
537
538 IF (l_node_end <= 0)
539 THEN
540 l_node_end := LENGTH(p_encoded_path) + 1;
541 END IF;
542
543 -- Retrieve the node tokens
544 l_node_str := SUBSTR(p_encoded_path, l_node_start + 1, l_node_end - l_node_start - 1);
545 l_node_start := l_node_end;
546
547 -- Set the index for the output position path table
548 l_tbl_idx := l_tbl_idx + 1;
549
550 -- Retrieve the MC_ID from the node token
551 l_id_end := INSTR(l_node_str, G_ID_SEP, 1);
552 l_position_tbl(l_tbl_idx).MC_ID := TO_NUMBER(SUBSTR(l_node_str, 1, l_id_end - 1));
553 l_id_start := l_id_end;
554
555 -- Retrieve the VERSION_NUMBER from the node token
556 l_id_end := INSTR(l_node_str, G_ID_SEP, l_id_start + 1);
557 l_id_str := SUBSTR(l_node_str, l_id_start + 1, l_id_end - l_id_start - 1);
558 IF (l_id_str <> '%')
559 THEN
560 l_position_tbl(l_tbl_idx).VERSION_NUMBER := TO_NUMBER(l_id_str);
561 END IF;
562 l_id_start := l_id_end;
563
564 -- Retrieve the POSITION_KEY from the node token
565 l_position_tbl(l_tbl_idx).POSITION_KEY := TO_NUMBER(SUBSTR(l_node_str, l_id_start + 1, l_node_end - l_id_start - 1));
566
567 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
568 THEN
569 fnd_log.string
570 (
571 fnd_log.level_statement,
572 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
573 'l_position_tbl -- ['||l_tbl_idx||'] ['||l_position_tbl(l_tbl_idx).MC_ID||'] ['||l_position_tbl(l_tbl_idx).VERSION_NUMBER||'] ['||l_position_tbl(l_tbl_idx).POSITION_KEY||']'
574 );
575 END IF;
576
577 EXIT WHEN l_node_end >= LENGTH(p_encoded_path);
578 END LOOP;
579
580 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
581 THEN
582 fnd_log.string
583 (
584 fnd_log.level_procedure,
585 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path.end',
586 'At the end of PLSQL procedure'
587 );
588 END IF;
589
590 RETURN l_position_tbl;
591
592 END Decode_Pos_Path;
593
594 ------------------------------------------------------------------
595 -- Precedure to get the cursor at runtime depending on the data --
596 ------------------------------------------------------------------
597 PROCEDURE Get_nodes_for_parent(
598 p_end_date IN DATE,
599 p_relationship_id IN NUMBER,
600 p_is_sub_config_node IN VARCHAR2,
601 p_is_top_config_node IN VARCHAR2,
602 p_mc_header_id IN NUMBER,
603 p_parent_rel_id IN NUMBER,
604 p_Get_nodes_csr IN OUT NOCOPY Nodes_for_parent_ref_csr
605 )
606 IS
607
608
609 BEGIN
610
611 IF (p_is_sub_config_node = 'T' and p_is_top_config_node = 'T')
612 THEN
613 OPEN p_Get_nodes_csr FOR
614
615 -- Modified Query Below for Performance Issue 1 in Bug 4913944
616 SELECT REL.RELATIONSHIP_ID,
617 REL.OBJECT_VERSION_NUMBER,
618 REL.POSITION_KEY,
619 REL.PARENT_RELATIONSHIP_ID,
620 REL.ITEM_GROUP_ID,
621 REL.POSITION_REF_CODE,
622 FPRC.MEANING POSITION_REF_MEANING,
623 --R12
624 --priyan MEL-CDL
625 REL.ATA_CODE,
626 FATA.MEANING ATA_MEANING,
627 REL.POSITION_NECESSITY_CODE,
628 FPNC.MEANING POSITION_NECESSITY_MEANING,
629 REL.UOM_CODE,
630 REL.QUANTITY,
631 REL.DISPLAY_ORDER,
632 REL.ACTIVE_START_DATE,
633 REL.ACTIVE_END_DATE,
634 REL.MC_HEADER_ID,
635 HDR.MC_ID,
636 HDR.VERSION_NUMBER,
637 HDR.CONFIG_STATUS_CODE
638 FROM AHL_MC_RELATIONSHIPS REL,
639 AHL_MC_HEADERS_V HDR,
640 FND_LOOKUP_VALUES_VL FPRC,
641 FND_LOOKUP_VALUES_VL FPNC,
642 FND_LOOKUP_VALUES_VL FATA
643 WHERE NVL(REL.PARENT_RELATIONSHIP_ID,0) = NVL(p_relationship_id,0)
644 AND REL.MC_HEADER_ID = p_mc_header_id
645 AND REL.MC_HEADER_ID = HDR.MC_HEADER_ID
646 AND TRUNC(NVL(REL.ACTIVE_END_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
647 AND FPRC.LOOKUP_CODE (+) = REL.POSITION_REF_CODE
648 AND FPRC.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
649 AND FPNC.LOOKUP_CODE (+) = REL.POSITION_NECESSITY_CODE
650 AND FPNC.LOOKUP_TYPE (+) = 'AHL_POSITION_NECESSITY'
651 AND FATA.LOOKUP_CODE (+) = REL.ATA_CODE
652 AND FATA.LOOKUP_TYPE (+) = 'AHL_ATA_CODE'
653 ORDER BY DISPLAY_ORDER;
654
655 ELSIF p_parent_rel_id IS NULL
656 THEN
657
658 -- This query is only for the top node(Header) to fetch even the expired Master Configuration.
659
660 OPEN p_Get_nodes_csr FOR
661
662 -- Modified Query Below for Performance Issue 2 in Bug 4913944
663 SELECT REL.RELATIONSHIP_ID,
664 REL.OBJECT_VERSION_NUMBER,
665 REL.POSITION_KEY,
666 REL.PARENT_RELATIONSHIP_ID,
667 REL.ITEM_GROUP_ID,
668 REL.POSITION_REF_CODE,
669 FPRC.MEANING POSITION_REF_MEANING,
670 --R12
671 --priyan MEL-CDL
672 REL.ATA_CODE,
673 FATA.MEANING ATA_MEANING,
674 REL.POSITION_NECESSITY_CODE,
675 FPNC.MEANING POSITION_NECESSITY_MEANING,
676 REL.UOM_CODE,
677 REL.QUANTITY,
678 REL.DISPLAY_ORDER,
679 REL.ACTIVE_START_DATE,
680 REL.ACTIVE_END_DATE,
681 REL.MC_HEADER_ID,
682 HDR.MC_ID,
683 HDR.VERSION_NUMBER,
684 HDR.CONFIG_STATUS_CODE
685 FROM AHL_MC_RELATIONSHIPS REL,
686 AHL_MC_HEADERS_V HDR,
687 FND_LOOKUP_VALUES_VL FPRC,
688 FND_LOOKUP_VALUES_VL FPNC,
689 FND_LOOKUP_VALUES_VL FATA
690 WHERE REL.PARENT_RELATIONSHIP_ID IS NULL
691 AND REL.MC_HEADER_ID = p_mc_header_id
692 AND REL.MC_HEADER_ID = HDR.MC_HEADER_ID
693 AND FPRC.LOOKUP_CODE (+) = REL.POSITION_REF_CODE
694 AND FPRC.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
695 AND FPNC.LOOKUP_CODE (+) = REL.POSITION_NECESSITY_CODE
696 AND FPNC.LOOKUP_TYPE (+) = 'AHL_POSITION_NECESSITY'
697 AND FATA.LOOKUP_CODE (+) = REL.ATA_CODE
698 AND FATA.LOOKUP_TYPE (+) = 'AHL_ATA_CODE'
699 ORDER BY DISPLAY_ORDER;
700
701 ELSE
702 OPEN p_Get_nodes_csr FOR
703
704 -- Modified Query Below for Performance Issue 3 in Bug 4913944
705 SELECT REL.RELATIONSHIP_ID,
706 REL.OBJECT_VERSION_NUMBER,
707 REL.POSITION_KEY,
708 REL.PARENT_RELATIONSHIP_ID,
709 REL.ITEM_GROUP_ID,
710 REL.POSITION_REF_CODE,
711 FPRC.MEANING POSITION_REF_MEANING,
712 --R12
713 --priyan MEL-CDL
714 REL.ATA_CODE,
715 FATA.MEANING ATA_MEANING,
716 REL.POSITION_NECESSITY_CODE,
717 FPNC.MEANING POSITION_NECESSITY_MEANING,
718 REL.UOM_CODE,
719 REL.QUANTITY,
720 REL.DISPLAY_ORDER,
721 REL.ACTIVE_START_DATE,
722 REL.ACTIVE_END_DATE,
723 REL.MC_HEADER_ID,
724 HDR.MC_ID,
725 HDR.VERSION_NUMBER,
726 HDR.CONFIG_STATUS_CODE
727 FROM AHL_MC_RELATIONSHIPS REL,
728 AHL_MC_HEADERS_V HDR,
729 FND_LOOKUP_VALUES_VL FPRC,
730 FND_LOOKUP_VALUES_VL FPNC,
731 FND_LOOKUP_VALUES_VL FATA
732 WHERE NVL(REL.PARENT_RELATIONSHIP_ID,0) = NVL(p_parent_rel_id,0)
733 AND REL.MC_HEADER_ID = p_mc_header_id
734 AND REL.MC_HEADER_ID = HDR.MC_HEADER_ID
735 AND TRUNC(NVL(REL.ACTIVE_END_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
736 AND FPRC.LOOKUP_CODE (+) = REL.POSITION_REF_CODE
737 AND FPRC.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
741 AND FATA.LOOKUP_TYPE (+) = 'AHL_ATA_CODE'
738 AND FPNC.LOOKUP_CODE (+) = REL.POSITION_NECESSITY_CODE
739 AND FPNC.LOOKUP_TYPE (+) = 'AHL_POSITION_NECESSITY'
740 AND FATA.LOOKUP_CODE (+) = REL.ATA_CODE
742 ORDER BY DISPLAY_ORDER;
743 END IF;
744
745
746 END Get_nodes_for_parent;
747
748
749 End AHL_MC_TREE_PVT;