[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_TREE_PVT
Source
1 PACKAGE BODY AHL_MC_TREE_PVT AS
2 /* $Header: AHLVMCTB.pls 120.3.12020000.2 2012/12/10 13:56:04 shnatu ship $ */
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 -- SATHAPLI::Bug 8363349, 16-Apr-2009, MC tree perf issue
38 -- TYPE Nodes_for_parent_ref_csr IS REF CURSOR RETURN Nodes_for_parent_rec;
39 TYPE Nodes_for_parent_tbl IS TABLE OF Nodes_for_parent_rec INDEX BY BINARY_INTEGER;
40
41 ------------------------------
42 -- Declare local procedures --
43 ------------------------------
44 FUNCTION Decode_Pos_Path
45 (
46 p_encoded_path IN VARCHAR2
47 )
48 RETURN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
49
50 PROCEDURE Get_nodes_for_parent(
51 -- p_end_date IN DATE,
52 p_relationship_id IN NUMBER,
53 p_is_sub_config_node IN VARCHAR2,
54 p_is_top_config_node IN VARCHAR2,
55 p_mc_header_id IN NUMBER,
56 p_parent_rel_id IN NUMBER,
57 -- SATHAPLI::Bug 8363349, 16-Apr-2009, MC tree perf issue
58 -- p_Get_nodes_csr IN OUT NOCOPY Nodes_for_parent_ref_csr
59 x_Get_nodes_tbl OUT NOCOPY Nodes_for_parent_tbl
60 );
61
62
63
64 ----------------------------
65 -- Get_MasterConfig_Nodes --
66 ----------------------------
67 -- Key to call this API, input params to be passed
68 -- MC Root Node
69 -- p_mc_header_id = <header-id of the MC>
70 -- p_parent_rel_id = null
71 -- p_is_parent_subconfig = 'F'
72 -- p_parent_pos_path = null
73 -- p_is_top_config_node = 'T'
74 -- p_is_sub_config_node = 'F'
75 --
76 -- MC Node (except root node)
77 -- p_mc_header_id = <header-id of the MC>
78 -- p_parent_rel_id = <relationship-id of the MC Node>
79 -- p_is_parent_subconfig = 'F'
80 -- p_parent_pos_path = <position path of the parent-node>
81 -- p_is_top_config_node = 'F'
82 -- p_is_sub_config_node = 'F'
83 --
84 -- Subconfig Root Node (any level deep)
85 -- p_mc_header_id = <header-id of the subconfig>
86 -- p_parent_rel_id = <relationship-id of the MC node to which the subconfig is to be attached>
87 -- p_is_parent_subconfig = 'F' (if the MC node to which the subconfig is to be attached is not a subconfig itself, else 'T'>
88 -- p_parent_pos_path = <position path of the MC node to which the subconfig is to be attached>
89 -- p_is_top_config_node = 'T'
90 -- p_is_sub_config_node = 'T'
91 --
92 -- Subconfig Node (except root node, any level deep)
93 -- p_mc_header_id = <header-id of the subconfig>
94 -- p_parent_rel_id = <relationship-id of the subconfig node>
95 -- p_is_parent_subconfig = 'T'
96 -- p_parent_pos_path = <position path of the subconfig node>
97 -- p_is_top_config_node = 'F'
98 -- p_is_sub_config_node = 'T'
99 --
100
101
102
103 PROCEDURE Get_MasterConfig_Nodes
104 (
105 p_api_version IN NUMBER,
106 x_return_status OUT NOCOPY VARCHAR2,
107 x_msg_count OUT NOCOPY NUMBER,
108 x_msg_data OUT NOCOPY VARCHAR2,
109 p_mc_header_id IN NUMBER,
110 p_parent_rel_id IN NUMBER,
111 p_is_parent_subconfig IN VARCHAR2 := 'F',
112 p_parent_pos_path IN VARCHAR2,
113 p_is_top_config_node IN VARCHAR2 := 'F',
114 p_is_sub_config_node IN VARCHAR2 := 'F',
115 x_tree_node_tbl OUT NOCOPY Tree_Node_Tbl_Type
116 )
117 IS
118
119
120
121 -- Define cursor to get the number of children for a particular node
122 CURSOR get_num_children
123 (
124 p_rel_id IN NUMBER
125 )
126 IS
127 SELECT NVL(COUNT(*), 0) NUM_CHILDREN
128 FROM AHL_MC_RELATIONSHIPS
129 WHERE PARENT_RELATIONSHIP_ID = p_rel_id;
130
131 -- Define cursor to check whether the particular node has any subconfig_association
132 CURSOR check_subconfig_assos
133 (
134 p_relationship_id IN NUMBER
135 )
136 IS
137 SELECT 'x'
138 FROM AHL_MC_CONFIG_RELATIONS
139 WHERE RELATIONSHIP_ID = p_relationship_id;
140
141 -- Define cursor to get check whether MC exists, also retrieve relationship_id of the topnode
142 CURSOR check_mc_exists
143 (
144 p_mc_header_id IN NUMBER
145 )
146 IS
147 SELECT RELATIONSHIP_ID,
148 ACTIVE_END_DATE
149 FROM AHL_MC_RELATIONSHIPS
150 WHERE MC_HEADER_ID = p_mc_header_id AND
151 PARENT_RELATIONSHIP_ID IS NULL;
152
153 -- Define cursor to retrieve the position path id given a position path
154 CURSOR get_pos_path_id
155 (
156 p_position_path IN VARCHAR2
157 )
158 IS
159 SELECT NVL(path_position_id, 0)
160 FROM ahl_mc_path_positions
161 WHERE encoded_path_position = p_position_path;
162
163 l_api_name CONSTANT VARCHAR2(30) := 'Get_MasterConfig_Nodes';
164 l_api_version CONSTANT NUMBER := 1.0;
165
166 l_tree_node_rec AHL_MC_TREE_PVT.Tree_node_rec_type;
167 l_topnode_rec Nodes_for_parent_rec;
168 l_tree_index NUMBER := 0;
169
170 l_pos_ref_code VARCHAR2(30);
171 l_pos_ref_meaning VARCHAR2(80);
172 l_ret_val BOOLEAN;
173 l_active_end_date DATE;
174 l_pos_path_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
175
176 -- SATHAPLI::Bug 8363349, 16-Apr-2009, MC tree perf issue
177 -- Nodes_list_for_parent Nodes_for_parent_ref_csr;
178 l_Nodes_for_parent_tbl Nodes_for_parent_tbl;
179
180 BEGIN
181
182 -- Standard start of API savepoint
183 SAVEPOINT Get_MasterConfig_Nodes_SP;
184
185 -- Standard call to check for call compatibility
186 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
187 THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189 END IF;
190
191 -- Initialize message list if p_init_msg_list is set to TRUE
192 FND_MSG_PUB.Initialize;
193
194 -- Initialize API return status to success
195 x_return_status := FND_API.G_RET_STS_SUCCESS;
196
197 -- API body starts here
198 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
199 THEN
200 fnd_log.string
201 (
202 fnd_log.level_procedure,
203 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
204 'At the start of PLSQL procedure'
205 );
206 END IF;
207
208 -- Log all input params
209 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
210 THEN
211 fnd_log.string
212 (
213 fnd_log.level_statement,
214 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
215 '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||']'
216 );
217
218 fnd_log.string
219 (
220 fnd_log.level_statement,
221 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
222 '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||']'
223 );
224 END IF;
225
226 -- Verify MC exists, retrieve relationship_id of the topnode also
227 OPEN check_mc_exists ( p_mc_header_id);
228 FETCH check_mc_exists INTO l_dummy_number,l_active_end_date;
229 IF (check_mc_exists%NOTFOUND)
230 THEN
231 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NOT_FOUND');
232 FND_MSG_PUB.ADD;
233 RAISE FND_API.G_EXC_ERROR;
234 END IF;
235 CLOSE check_mc_exists;
236
237 -- SATHAPLI : Time-specific debugs
238 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
239 THEN
240 fnd_log.string
241 (
242 fnd_log.level_statement,
243 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
244 ' TSDL::About to call Get_nodes_for_parent ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY hh24:mi:ss')
245 );
246 END IF;
247
248 Get_nodes_for_parent (
249 -- p_end_date => l_active_end_date,
250 p_relationship_id => l_dummy_number,
251 p_is_sub_config_node => p_is_sub_config_node,
252 p_is_top_config_node => p_is_top_config_node,
253 p_mc_header_id => p_mc_header_id,
254 p_parent_rel_id => p_parent_rel_id,
255 -- SATHAPLI::Bug 8363349, 16-Apr-2009, MC tree perf issue
256 -- p_Get_nodes_csr => Nodes_list_for_parent
257 x_Get_nodes_tbl => l_Nodes_for_parent_tbl
258 );
259
260 -- SATHAPLI : Time-specific debugs
261 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
262 THEN
263 fnd_log.string
264 (
265 fnd_log.level_statement,
266 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
267 ' TSDL::Returned from Get_nodes_for_parent ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY hh24:mi:ss')
268 );
269 fnd_log.string
270 (
271 fnd_log.level_statement,
272 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
273 ' TSDL::Loop start ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY hh24:mi:ss')
274 );
275 END IF;
276
277 -- Iterate through all retrieved nodes for the particular query
278 -- SATHAPLI::Bug 8363349, 16-Apr-2009, MC tree perf issue
279 /*
280 LOOP
281 FETCH Nodes_list_for_parent INTO l_topnode_rec;
282 EXIT WHEN Nodes_list_for_parent%NOTFOUND;
283 */
284 FOR i IN l_Nodes_for_parent_tbl.FIRST..l_Nodes_for_parent_tbl.LAST
285 LOOP
286 l_topnode_rec := l_Nodes_for_parent_tbl(i);
287
288 -- Populate all output params
289
290 -- Populate MC node params
291 l_tree_node_rec.RELATIONSHIP_ID := l_topnode_rec.RELATIONSHIP_ID;
292 l_tree_node_rec.OBJECT_VERSION_NUMBER := l_topnode_rec.OBJECT_VERSION_NUMBER;
293 l_tree_node_rec.POSITION_KEY := l_topnode_rec.POSITION_KEY;
294 l_tree_node_rec.ITEM_GROUP_ID := l_topnode_rec.ITEM_GROUP_ID;
295 l_tree_node_rec.POSITION_NECESSITY_CODE := l_topnode_rec.POSITION_NECESSITY_CODE;
296 l_tree_node_rec.POSITION_NECESSITY_MEANING := l_topnode_rec.POSITION_NECESSITY_MEANING;
297 --R12
298 --priyan MEL-CDL
299 l_tree_node_rec.ATA_CODE := l_topnode_rec.ATA_CODE;
300 l_tree_node_rec.ATA_MEANING := l_topnode_rec.ATA_MEANING;
301
302 l_tree_node_rec.UOM_CODE := l_topnode_rec.UOM_CODE;
303 l_tree_node_rec.QUANTITY := l_topnode_rec.QUANTITY;
304 l_tree_node_rec.DISPLAY_ORDER := l_topnode_rec.DISPLAY_ORDER;
305 l_tree_node_rec.ACTIVE_START_DATE := l_topnode_rec.ACTIVE_START_DATE;
306 l_tree_node_rec.ACTIVE_END_DATE := l_topnode_rec.ACTIVE_END_DATE;
307
308 -- Populate MC specific pars (for position path)
309 l_tree_node_rec.MC_HEADER_ID := l_topnode_rec.MC_HEADER_ID;
310 l_tree_node_rec.MC_ID := l_topnode_rec.MC_ID;
311 l_tree_node_rec.VERSION_NUMBER := l_topnode_rec.VERSION_NUMBER;
312
313 -- Populate number of children
314 OPEN get_num_children (l_topnode_rec.RELATIONSHIP_ID);
315 FETCH get_num_children INTO l_tree_node_rec.NUM_CHILD_NODES;
316 CLOSE get_num_children;
317
318 -- Check whether node has any subconfiguration params, populate the same
319 OPEN check_subconfig_assos (l_topnode_rec.RELATIONSHIP_ID);
320 FETCH check_subconfig_assos INTO l_dummy_varchar;
321 IF (check_subconfig_assos%FOUND)
322 THEN
323 l_tree_node_rec.HAS_SUBCONFIGS := 'T';
324 ELSE
325 l_tree_node_rec.HAS_SUBCONFIGS := 'F';
326 END IF;
327 CLOSE check_subconfig_assos;
328
329 -- Populate output param flags
330
331 IF (p_is_top_config_node = 'T')
332 -- Implies that it is a rootnode, also for subconfig rootnode
333 THEN
334 l_tree_node_rec.IS_SUBCONFIG_NODE := 'F';
335 l_tree_node_rec.IS_PARENT_SUBCONFIG :='F';
336
337 -- If is is topnode, populate config_satus_code
338 l_tree_node_rec.CONFIG_STATUS_CODE := l_topnode_rec.CONFIG_STATUS_CODE;
339 END IF;
340
341 IF (p_is_sub_config_node = 'T')
342 -- Implies that it is a subconfig node, also for subconfig rootnode
343 THEN
344 l_tree_node_rec.IS_SUBCONFIG_NODE := 'T';
345 l_tree_node_rec.IS_PARENT_SUBCONFIG := p_is_parent_subconfig;
346 END IF ;
347
348 IF (p_is_sub_config_node = 'T' AND p_is_top_config_node = 'T')
349 -- Implies that it is the rootnode of a subconfig, will be true only in the case of a subconfig expand call
350 THEN
351 l_tree_node_rec.PARENT_RELATIONSHIP_ID := p_parent_rel_id;
352 -- l_tree_node_rec.IS_SUBCONFIG_TOPNODE := 'T';
353 l_tree_node_rec.IS_SUBCONFIG_TOPNODE := 'F';
354
355 -- Populate position path
356 l_tree_node_rec.POSITION_PATH := AHL_MC_PATH_POSITION_PVT.get_encoded_path
357 (
358 p_parent_pos_path,
359 l_tree_node_rec.MC_ID,
360 l_tree_node_rec.VERSION_NUMBER,
361 l_tree_node_rec.POSITION_KEY,
362 'T'
363 );
364 ELSE
365 l_tree_node_rec.PARENT_RELATIONSHIP_ID := l_topnode_rec.PARENT_RELATIONSHIP_ID;
366 l_tree_node_rec.IS_SUBCONFIG_TOPNODE := 'F';
367
368 IF (p_is_top_config_node = 'T' AND p_is_sub_config_node = 'F')
369 -- Implies that it is the rootnode of the MC, will be true only once, in the case of the first call to the API
370 THEN
371 -- Populate position path
372 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;
373 ELSE
374 -- Populate position path
375 l_tree_node_rec.POSITION_PATH := AHL_MC_PATH_POSITION_PVT.get_encoded_path
376 (
377 p_parent_pos_path,
378 l_tree_node_rec.MC_ID,
379 l_tree_node_rec.VERSION_NUMBER,
380 l_tree_node_rec.POSITION_KEY,
381 'F'
382 );
383 END IF;
384
385 END IF;
386
387 -- Retrieve position path id
388 OPEN get_pos_path_id(l_tree_node_rec.POSITION_PATH);
389 FETCH get_pos_path_id INTO l_tree_node_rec.POSITION_PATH_ID;
390 CLOSE get_pos_path_id;
391
392 -- Reset flags to read position path specific position ref codes...
393 l_pos_ref_code := NULL;
394 l_pos_ref_meaning := NULL;
395 l_ret_val := FALSE;
396
397 -- Only if it is a subconfig node, we should query for position path specific position reference code
398 IF (p_is_sub_config_node = 'T')
399 THEN
400 IF (l_pos_path_tbl.COUNT > 0)
401 THEN
402 FOR i IN l_pos_path_tbl.FIRST..l_pos_path_tbl.LAST
403 LOOP
404 l_pos_path_tbl(i).MC_ID := NULL;
405 l_pos_path_tbl(i).VERSION_NUMBER := NULL;
406 l_pos_path_tbl(i).POSITION_KEY := NULL;
407 END LOOP;
408 END IF;
409
410 -- Decode the retrieved position path to a position path table, since the input to get_posref_by_path is a position path table
411 l_pos_path_tbl := Decode_Pos_Path(l_tree_node_rec.POSITION_PATH);
412
413 -- Retrieve the position path specific position reference code, retrieves default position reference if no position path has already been created
414 l_pos_ref_code := AHL_MC_PATH_POSITION_PVT.get_posref_by_path(l_pos_path_tbl, FND_API.G_TRUE);
415
416 AHL_UTIL_MC_PKG.Convert_To_LookupMeaning
417 (
418 'AHL_POSITION_REFERENCE',
419 l_pos_ref_code,
420 l_pos_ref_meaning,
421 l_ret_val
422 );
423
424 IF (l_ret_val = TRUE)
425 THEN
426 l_tree_node_rec.POSITION_REF_CODE := l_pos_ref_code;
427 l_tree_node_rec.POSITION_REF_MEANING := l_pos_ref_meaning;
428 ELSE
429 l_tree_node_rec.POSITION_REF_CODE := l_topnode_rec.POSITION_REF_CODE;
430 l_tree_node_rec.POSITION_REF_MEANING := l_topnode_rec.POSITION_REF_MEANING;
431 END IF;
432 ELSE
433 l_tree_node_rec.POSITION_REF_CODE := l_topnode_rec.POSITION_REF_CODE;
434 l_tree_node_rec.POSITION_REF_MEANING := l_topnode_rec.POSITION_REF_MEANING;
435 END IF;
436
437 -- Log some output params
438 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
439 THEN
440 fnd_log.string
441 (
442 fnd_log.level_statement,
443 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
444 '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||']'
445 );
446
447 fnd_log.string
448 (
449 fnd_log.level_statement,
450 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
451 'OUT -- [subconfig_top = '||l_tree_node_rec.IS_SUBCONFIG_TOPNODE||'] [parent_subconfig = '||l_tree_node_rec.IS_PARENT_SUBCONFIG||']'
452 );
453
454 fnd_log.string
455 (
456 fnd_log.level_statement,
457 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
458 'OUT -- [position_path = '||l_tree_node_rec.POSITION_PATH||'] [position_path_id = '||l_tree_node_rec.POSITION_PATH_ID||']'
459 );
460 END IF;
461
462 -- Add the tree node record to the output table
463 l_tree_index := l_tree_index + 1;
464 x_tree_node_tbl(l_tree_index) := l_tree_node_rec;
465
466 END LOOP;
467 -- CLOSE Nodes_list_for_parent;
468 -- API body ends here
469
470 -- SATHAPLI : Time-specific debugs
471 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
472 THEN
473 fnd_log.string
474 (
475 fnd_log.level_statement,
476 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
477 ' TSDL::Loop end ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY hh24:mi:ss')
478 );
479 END IF;
480
481 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
482 THEN
483 fnd_log.string
484 (
485 fnd_log.level_procedure,
486 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
487 'At the end of PLSQL procedure'
488 );
489 END IF;
490 -- API body ends here
491
492 -- Check Error Message stack.
493 x_msg_count := FND_MSG_PUB.count_msg;
494 IF x_msg_count > 0
495 THEN
496 RAISE FND_API.G_EXC_ERROR;
497 END IF;
498
499 -- Standard call to get message count and if count is 1, get message info
500 FND_MSG_PUB.count_and_get
501 (
502 p_count => x_msg_count,
503 p_data => x_msg_data,
504 p_encoded => FND_API.G_FALSE
505 );
506
507 EXCEPTION
508 WHEN FND_API.G_EXC_ERROR THEN
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 Rollback to Get_MasterConfig_Nodes_SP;
511 FND_MSG_PUB.count_and_get
512 (
513 p_count => x_msg_count,
514 p_data => x_msg_data,
515 p_encoded => FND_API.G_FALSE
516 );
517
518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
520 Rollback to Get_MasterConfig_Nodes_SP;
521 FND_MSG_PUB.count_and_get
522 (
523 p_count => x_msg_count,
524 p_data => x_msg_data,
525 p_encoded => FND_API.G_FALSE
526 );
527
528 WHEN OTHERS THEN
529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
530 Rollback to Get_MasterConfig_Nodes_SP;
531 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
532 THEN
533 FND_MSG_PUB.add_exc_msg
534 (
535 p_pkg_name => G_PKG_NAME,
536 p_procedure_name => 'Get_MasterConfig_Nodes',
537 p_error_text => SUBSTR(SQLERRM,1,240)
538 );
539 END IF;
540 FND_MSG_PUB.count_and_get
541 (
542 p_count => x_msg_count,
543 p_data => x_msg_data,
544 p_encoded => FND_API.G_FALSE
545 );
546
547 END Get_MasterConfig_Nodes;
548
549 ---------------------
550 -- Decode_Pos_Path --
551 ---------------------
552 FUNCTION Decode_Pos_Path
553 (
554 p_encoded_path IN VARCHAR2
555 )
556 RETURN AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type
557 IS
558
559 G_NODE_SEP VARCHAR2(1) := '/';
560 G_ID_SEP VARCHAR2(1) := ':';
561
562 l_node_start NUMBER := 0;
563 l_node_end NUMBER := 0;
564 l_node_str VARCHAR2(32);
565
566 l_id_start NUMBER := 0;
567 l_id_end NUMBER := 0;
568 l_id_str VARCHAR2(10);
569
570 l_position_tbl AHL_MC_PATH_POSITION_PVT.Path_Position_Tbl_Type;
571 l_tbl_idx NUMBER := 0;
572
573 BEGIN
574
575 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
576 THEN
577 fnd_log.string
578 (
579 fnd_log.level_procedure,
580 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path.begin',
581 'At the start of PLSQL procedure'
582 );
583 END IF;
584
585 LOOP
586 -- Tokenize input encoded position path string using node separator
587 l_node_end := INSTR(p_encoded_path, G_NODE_SEP, l_node_start + 1);
588
589 IF (l_node_end <= 0)
590 THEN
591 l_node_end := LENGTH(p_encoded_path) + 1;
592 END IF;
593
594 -- Retrieve the node tokens
595 l_node_str := SUBSTR(p_encoded_path, l_node_start + 1, l_node_end - l_node_start - 1);
596 l_node_start := l_node_end;
597
598 -- Set the index for the output position path table
599 l_tbl_idx := l_tbl_idx + 1;
600
601 -- Retrieve the MC_ID from the node token
602 l_id_end := INSTR(l_node_str, G_ID_SEP, 1);
603 l_position_tbl(l_tbl_idx).MC_ID := TO_NUMBER(SUBSTR(l_node_str, 1, l_id_end - 1));
604 l_id_start := l_id_end;
605
606 -- Retrieve the VERSION_NUMBER from the node token
607 l_id_end := INSTR(l_node_str, G_ID_SEP, l_id_start + 1);
608 l_id_str := SUBSTR(l_node_str, l_id_start + 1, l_id_end - l_id_start - 1);
609 IF (l_id_str <> '%')
610 THEN
611 l_position_tbl(l_tbl_idx).VERSION_NUMBER := TO_NUMBER(l_id_str);
612 END IF;
613 l_id_start := l_id_end;
614
615 -- Retrieve the POSITION_KEY from the node token
616 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));
617
618 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
619 THEN
620 fnd_log.string
621 (
622 fnd_log.level_statement,
623 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path',
624 '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||']'
625 );
626 END IF;
627
628 EXIT WHEN l_node_end >= LENGTH(p_encoded_path);
629 END LOOP;
630
631 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
632 THEN
633 fnd_log.string
634 (
635 fnd_log.level_procedure,
636 'ahl.plsql.'||G_PKG_NAME||'.Decode_Pos_Path.end',
637 'At the end of PLSQL procedure'
638 );
639 END IF;
640
641 RETURN l_position_tbl;
642
643 END Decode_Pos_Path;
644
645 ------------------------------------------------------------------
646 -- Precedure to get the cursor at runtime depending on the data --
647 ------------------------------------------------------------------
648 -- SATHAPLI::Bug 8363349, 16-Apr-2009, MC tree perf issue
649 -- Modified Get_nodes_for_parent to return BULK COLLECTed table type.
650 -- Modified the cursor queries for performance tuning.
651 PROCEDURE Get_nodes_for_parent(
652 -- p_end_date IN DATE,
653 p_relationship_id IN NUMBER,
654 p_is_sub_config_node IN VARCHAR2,
655 p_is_top_config_node IN VARCHAR2,
656 p_mc_header_id IN NUMBER,
657 p_parent_rel_id IN NUMBER,
658 -- p_Get_nodes_csr IN OUT NOCOPY Nodes_for_parent_ref_csr
659 x_Get_nodes_tbl OUT NOCOPY Nodes_for_parent_tbl
660 )
661 IS
662
663 CURSOR get_sub_config_root_details (p_relationship_id NUMBER,
664 p_mc_header_id NUMBER) IS
665 SELECT REL.RELATIONSHIP_ID,
666 REL.OBJECT_VERSION_NUMBER,
667 REL.POSITION_KEY,
668 REL.PARENT_RELATIONSHIP_ID,
669 REL.ITEM_GROUP_ID,
670 REL.POSITION_REF_CODE,
671 FPRC.MEANING POSITION_REF_MEANING,
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 (
687 SELECT MCB.MC_HEADER_ID,
688 MCB.MC_ID,
689 MCB.VERSION_NUMBER,
690 DECODE (MCB.CONFIG_STATUS_CODE,
691 'CLOSED', MCB.CONFIG_STATUS_CODE,
692 DECODE (SIGN(TRUNC(NVL(MCR.ACTIVE_END_DATE, SYSDATE+1)) - TRUNC(SYSDATE)),
693 1, MCB.CONFIG_STATUS_CODE, 'EXPIRED')) CONFIG_STATUS_CODE
694 FROM AHL_MC_HEADERS_B MCB, AHL_MC_RELATIONSHIPS MCR
695 WHERE MCB.MC_HEADER_ID = MCR.MC_HEADER_ID
696 AND MCR.PARENT_RELATIONSHIP_ID IS NULL
697 ) HDR,
698 FND_LOOKUP_VALUES FPRC,
699 FND_LOOKUP_VALUES FPNC,
700 FND_LOOKUP_VALUES FATA
701 WHERE NVL(REL.PARENT_RELATIONSHIP_ID,0) = NVL(p_relationship_id,0)
702 AND REL.MC_HEADER_ID = p_mc_header_id
703 AND REL.MC_HEADER_ID = HDR.MC_HEADER_ID
704 AND TRUNC(NVL(REL.ACTIVE_END_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
705 AND FPRC.LOOKUP_CODE (+) = REL.POSITION_REF_CODE
706 AND FPRC.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
707 AND FPRC.LANGUAGE (+) = USERENV('LANG')
708 AND FPRC.VIEW_APPLICATION_ID (+) = 0
709 AND FPNC.LOOKUP_CODE (+) = REL.POSITION_NECESSITY_CODE
710 AND FPNC.LOOKUP_TYPE (+) = 'AHL_POSITION_NECESSITY'
711 AND FPNC.LANGUAGE (+) = USERENV('LANG')
712 AND FPNC.VIEW_APPLICATION_ID (+) = 0
713 AND FATA.LOOKUP_CODE (+) = REL.ATA_CODE
714 AND FATA.LOOKUP_TYPE (+) = 'AHL_ATA_CODE'
715 AND FATA.LANGUAGE (+) = USERENV('LANG')
716 AND FATA.VIEW_APPLICATION_ID (+) = 0
717 ORDER BY DISPLAY_ORDER;
718
719 CURSOR get_root_details (p_mc_header_id NUMBER) IS
720 SELECT REL.RELATIONSHIP_ID,
721 REL.OBJECT_VERSION_NUMBER,
722 REL.POSITION_KEY,
723 REL.PARENT_RELATIONSHIP_ID,
724 REL.ITEM_GROUP_ID,
725 REL.POSITION_REF_CODE,
726 FPRC.MEANING POSITION_REF_MEANING,
727 REL.ATA_CODE,
728 FATA.MEANING ATA_MEANING,
729 REL.POSITION_NECESSITY_CODE,
730 FPNC.MEANING POSITION_NECESSITY_MEANING,
731 REL.UOM_CODE,
732 REL.QUANTITY,
733 REL.DISPLAY_ORDER,
734 REL.ACTIVE_START_DATE,
735 REL.ACTIVE_END_DATE,
736 REL.MC_HEADER_ID,
737 HDR.MC_ID,
738 HDR.VERSION_NUMBER,
739 DECODE (HDR.CONFIG_STATUS_CODE,
740 'CLOSED', HDR.CONFIG_STATUS_CODE,
741 DECODE (SIGN(TRUNC(NVL(REL.ACTIVE_END_DATE, SYSDATE+1)) - TRUNC(SYSDATE)),
742 1, HDR.CONFIG_STATUS_CODE, 'EXPIRED')) CONFIG_STATUS_CODE
743 FROM AHL_MC_RELATIONSHIPS REL,
744 AHL_MC_HEADERS_B HDR,
745 FND_LOOKUP_VALUES FPRC,
746 FND_LOOKUP_VALUES FPNC,
747 FND_LOOKUP_VALUES FATA
748 WHERE REL.PARENT_RELATIONSHIP_ID IS NULL
749 AND REL.MC_HEADER_ID = p_mc_header_id
750 AND REL.MC_HEADER_ID = HDR.MC_HEADER_ID
751 AND FPRC.LOOKUP_CODE (+) = REL.POSITION_REF_CODE
752 AND FPRC.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
753 AND FPRC.LANGUAGE (+) = USERENV('LANG')
754 AND FPRC.VIEW_APPLICATION_ID (+) = 0
755 AND FPNC.LOOKUP_CODE (+) = REL.POSITION_NECESSITY_CODE
756 AND FPNC.LOOKUP_TYPE (+) = 'AHL_POSITION_NECESSITY'
757 AND FPNC.LANGUAGE (+) = USERENV('LANG')
758 AND FPNC.VIEW_APPLICATION_ID (+) = 0
759 AND FATA.LOOKUP_CODE (+) = REL.ATA_CODE
760 AND FATA.LOOKUP_TYPE (+) = 'AHL_ATA_CODE'
761 AND FATA.LANGUAGE (+) = USERENV('LANG')
762 AND FATA.VIEW_APPLICATION_ID (+) = 0
763 ORDER BY DISPLAY_ORDER;
764
765 CURSOR get_children_details (p_parent_rel_id NUMBER,
766 p_mc_header_id NUMBER) IS
767 SELECT REL.RELATIONSHIP_ID,
768 REL.OBJECT_VERSION_NUMBER,
769 REL.POSITION_KEY,
770 REL.PARENT_RELATIONSHIP_ID,
771 REL.ITEM_GROUP_ID,
772 REL.POSITION_REF_CODE,
773 FPRC.MEANING POSITION_REF_MEANING,
774 REL.ATA_CODE,
775 FATA.MEANING ATA_MEANING,
776 REL.POSITION_NECESSITY_CODE,
777 FPNC.MEANING POSITION_NECESSITY_MEANING,
778 REL.UOM_CODE,
779 REL.QUANTITY,
780 REL.DISPLAY_ORDER,
781 REL.ACTIVE_START_DATE,
782 REL.ACTIVE_END_DATE,
783 REL.MC_HEADER_ID,
784 HDR.MC_ID,
785 HDR.VERSION_NUMBER,
786 HDR.CONFIG_STATUS_CODE
787 FROM AHL_MC_RELATIONSHIPS REL,
788 (
789 SELECT MCB.MC_HEADER_ID,
790 MCB.MC_ID,
791 MCB.VERSION_NUMBER,
792 DECODE (MCB.CONFIG_STATUS_CODE,
793 'CLOSED', MCB.CONFIG_STATUS_CODE,
794 DECODE (SIGN(TRUNC(NVL(MCR.ACTIVE_END_DATE, SYSDATE+1)) - TRUNC(SYSDATE)),
795 1, MCB.CONFIG_STATUS_CODE, 'EXPIRED')) CONFIG_STATUS_CODE
796 FROM AHL_MC_HEADERS_B MCB, AHL_MC_RELATIONSHIPS MCR
797 WHERE MCB.MC_HEADER_ID = MCR.MC_HEADER_ID
798 AND MCR.PARENT_RELATIONSHIP_ID IS NULL
799 ) HDR,
800 FND_LOOKUP_VALUES FPRC,
801 FND_LOOKUP_VALUES FPNC,
802 FND_LOOKUP_VALUES FATA
803 WHERE NVL(REL.PARENT_RELATIONSHIP_ID,0) = NVL(p_parent_rel_id,0)
804 AND REL.MC_HEADER_ID = p_mc_header_id
805 AND REL.MC_HEADER_ID = HDR.MC_HEADER_ID
806 AND TRUNC(NVL(REL.ACTIVE_END_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
807 AND FPRC.LOOKUP_CODE (+) = REL.POSITION_REF_CODE
808 AND FPRC.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
809 AND FPRC.LANGUAGE (+) = USERENV('LANG')
810 AND FPRC.VIEW_APPLICATION_ID (+) = 0
811 AND FPNC.LOOKUP_CODE (+) = REL.POSITION_NECESSITY_CODE
812 AND FPNC.LOOKUP_TYPE (+) = 'AHL_POSITION_NECESSITY'
813 AND FPNC.LANGUAGE (+) = USERENV('LANG')
814 AND FPNC.VIEW_APPLICATION_ID (+) = 0
815 AND FATA.LOOKUP_CODE (+) = REL.ATA_CODE
816 AND FATA.LOOKUP_TYPE (+) = 'AHL_ATA_CODE'
817 AND FATA.LANGUAGE (+) = USERENV('LANG')
818 AND FATA.VIEW_APPLICATION_ID (+) = 0
819 ORDER BY DISPLAY_ORDER;
820
821 BEGIN
822 /*
823 IF (p_is_sub_config_node = 'T' and p_is_top_config_node = 'T')
824 THEN
825 OPEN p_Get_nodes_csr FOR
826
827 -- Modified Query Below for Performance Issue 1 in Bug 4913944
828 SELECT REL.RELATIONSHIP_ID,
829 REL.OBJECT_VERSION_NUMBER,
830 REL.POSITION_KEY,
831 REL.PARENT_RELATIONSHIP_ID,
832 REL.ITEM_GROUP_ID,
833 REL.POSITION_REF_CODE,
834 FPRC.MEANING POSITION_REF_MEANING,
835 --R12
836 --priyan MEL-CDL
837 REL.ATA_CODE,
838 FATA.MEANING ATA_MEANING,
839 REL.POSITION_NECESSITY_CODE,
840 FPNC.MEANING POSITION_NECESSITY_MEANING,
841 REL.UOM_CODE,
842 REL.QUANTITY,
843 REL.DISPLAY_ORDER,
844 REL.ACTIVE_START_DATE,
845 REL.ACTIVE_END_DATE,
846 REL.MC_HEADER_ID,
847 HDR.MC_ID,
848 HDR.VERSION_NUMBER,
849 HDR.CONFIG_STATUS_CODE
850 FROM AHL_MC_RELATIONSHIPS REL,
851 AHL_MC_HEADERS_V HDR,
852 FND_LOOKUP_VALUES_VL FPRC,
853 FND_LOOKUP_VALUES_VL FPNC,
854 FND_LOOKUP_VALUES_VL FATA
855 WHERE NVL(REL.PARENT_RELATIONSHIP_ID,0) = NVL(p_relationship_id,0)
856 AND REL.MC_HEADER_ID = p_mc_header_id
857 AND REL.MC_HEADER_ID = HDR.MC_HEADER_ID
858 AND TRUNC(NVL(REL.ACTIVE_END_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
859 AND FPRC.LOOKUP_CODE (+) = REL.POSITION_REF_CODE
860 AND FPRC.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
861 AND FPNC.LOOKUP_CODE (+) = REL.POSITION_NECESSITY_CODE
862 AND FPNC.LOOKUP_TYPE (+) = 'AHL_POSITION_NECESSITY'
863 AND FATA.LOOKUP_CODE (+) = REL.ATA_CODE
864 AND FATA.LOOKUP_TYPE (+) = 'AHL_ATA_CODE'
865 ORDER BY DISPLAY_ORDER;
866
867 ELSIF p_parent_rel_id IS NULL
868 THEN
869
870 -- This query is only for the top node(Header) to fetch even the expired Master Configuration.
871
872 OPEN p_Get_nodes_csr FOR
873
874 -- Modified Query Below for Performance Issue 2 in Bug 4913944
875 SELECT REL.RELATIONSHIP_ID,
876 REL.OBJECT_VERSION_NUMBER,
877 REL.POSITION_KEY,
878 REL.PARENT_RELATIONSHIP_ID,
879 REL.ITEM_GROUP_ID,
880 REL.POSITION_REF_CODE,
881 FPRC.MEANING POSITION_REF_MEANING,
882 --R12
883 --priyan MEL-CDL
884 REL.ATA_CODE,
885 FATA.MEANING ATA_MEANING,
886 REL.POSITION_NECESSITY_CODE,
887 FPNC.MEANING POSITION_NECESSITY_MEANING,
888 REL.UOM_CODE,
889 REL.QUANTITY,
890 REL.DISPLAY_ORDER,
891 REL.ACTIVE_START_DATE,
892 REL.ACTIVE_END_DATE,
893 REL.MC_HEADER_ID,
894 HDR.MC_ID,
895 HDR.VERSION_NUMBER,
896 HDR.CONFIG_STATUS_CODE
897 FROM AHL_MC_RELATIONSHIPS REL,
898 AHL_MC_HEADERS_V HDR,
899 FND_LOOKUP_VALUES_VL FPRC,
900 FND_LOOKUP_VALUES_VL FPNC,
901 FND_LOOKUP_VALUES_VL FATA
902 WHERE REL.PARENT_RELATIONSHIP_ID IS NULL
903 AND REL.MC_HEADER_ID = p_mc_header_id
904 AND REL.MC_HEADER_ID = HDR.MC_HEADER_ID
905 AND FPRC.LOOKUP_CODE (+) = REL.POSITION_REF_CODE
906 AND FPRC.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
907 AND FPNC.LOOKUP_CODE (+) = REL.POSITION_NECESSITY_CODE
908 AND FPNC.LOOKUP_TYPE (+) = 'AHL_POSITION_NECESSITY'
909 AND FATA.LOOKUP_CODE (+) = REL.ATA_CODE
910 AND FATA.LOOKUP_TYPE (+) = 'AHL_ATA_CODE'
911 ORDER BY DISPLAY_ORDER;
912
913 ELSE
914 OPEN p_Get_nodes_csr FOR
915
916 -- Modified Query Below for Performance Issue 3 in Bug 4913944
917 SELECT REL.RELATIONSHIP_ID,
918 REL.OBJECT_VERSION_NUMBER,
919 REL.POSITION_KEY,
920 REL.PARENT_RELATIONSHIP_ID,
921 REL.ITEM_GROUP_ID,
922 REL.POSITION_REF_CODE,
923 FPRC.MEANING POSITION_REF_MEANING,
924 --R12
925 --priyan MEL-CDL
926 REL.ATA_CODE,
927 FATA.MEANING ATA_MEANING,
928 REL.POSITION_NECESSITY_CODE,
929 FPNC.MEANING POSITION_NECESSITY_MEANING,
930 REL.UOM_CODE,
931 REL.QUANTITY,
932 REL.DISPLAY_ORDER,
933 REL.ACTIVE_START_DATE,
934 REL.ACTIVE_END_DATE,
935 REL.MC_HEADER_ID,
936 HDR.MC_ID,
937 HDR.VERSION_NUMBER,
938 HDR.CONFIG_STATUS_CODE
939 FROM AHL_MC_RELATIONSHIPS REL,
940 AHL_MC_HEADERS_V HDR,
941 FND_LOOKUP_VALUES_VL FPRC,
942 FND_LOOKUP_VALUES_VL FPNC,
943 FND_LOOKUP_VALUES_VL FATA
944 WHERE NVL(REL.PARENT_RELATIONSHIP_ID,0) = NVL(p_parent_rel_id,0)
945 AND REL.MC_HEADER_ID = p_mc_header_id
946 AND REL.MC_HEADER_ID = HDR.MC_HEADER_ID
947 AND TRUNC(NVL(REL.ACTIVE_END_DATE, SYSDATE + 1)) > TRUNC(SYSDATE)
948 AND FPRC.LOOKUP_CODE (+) = REL.POSITION_REF_CODE
949 AND FPRC.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
950 AND FPNC.LOOKUP_CODE (+) = REL.POSITION_NECESSITY_CODE
951 AND FPNC.LOOKUP_TYPE (+) = 'AHL_POSITION_NECESSITY'
952 AND FATA.LOOKUP_CODE (+) = REL.ATA_CODE
953 AND FATA.LOOKUP_TYPE (+) = 'AHL_ATA_CODE'
954 ORDER BY DISPLAY_ORDER;
955 END IF;
956 */
957
958 IF (p_is_sub_config_node = 'T' and p_is_top_config_node = 'T') THEN
959 OPEN get_sub_config_root_details(p_relationship_id, p_mc_header_id);
960 FETCH get_sub_config_root_details BULK COLLECT INTO x_Get_nodes_tbl;
961 CLOSE get_sub_config_root_details;
962 ELSIF (p_parent_rel_id IS NULL) THEN
963 -- This query is only for the top node (Header) to fetch even the expired Master Configuration.
964 OPEN get_root_details(p_mc_header_id);
965 FETCH get_root_details BULK COLLECT INTO x_Get_nodes_tbl;
966 CLOSE get_root_details;
967 ELSE
968 OPEN get_children_details(p_parent_rel_id, p_mc_header_id);
969 FETCH get_children_details BULK COLLECT INTO x_Get_nodes_tbl;
970 CLOSE get_children_details;
971 END IF;
972 END Get_nodes_for_parent;
973
974 End AHL_MC_TREE_PVT;