[Home] [Help]
PACKAGE BODY: APPS.FLM_UTIL
Source
1 PACKAGE BODY flm_util AS
2 /* $Header: FLMUTILB.pls 115.6 2003/05/02 01:28:50 yulin ship $ */
3
4 xmin NUMBER;
5 xmax NUMBER;
6 ymin NUMBER;
7 ymax NUMBER;
8 component NUMBER;
9 XY_MAX NUMBER := 99999;
10 nodes NODE_LIST;
11 links LINK_LIST;
12 TYPE FLAG_LIST IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
13 states FLAG_LIST;
14
15 FUNCTION get_key_flex_category(cat_id IN NUMBER) RETURN VARCHAR2 IS
16 l_result VARCHAR2(2000);
17 BEGIN
18 SELECT concatenated_segments
19 INTO l_result
20 FROM mtl_categories_kfv
21 WHERE category_id = cat_id;
22 RETURN l_result;
23 EXCEPTION
24 WHEN OTHERS THEN
25 RETURN '';
26 END get_key_flex_category;
27
28 FUNCTION get_key_flex_item(item_id IN NUMBER, org_id IN NUMBER) RETURN VARCHAR2 IS
29 l_result VARCHAR2(2000);
30 BEGIN
31 SELECT concatenated_segments
32 INTO l_result
33 FROM mtl_system_items_kfv
34 WHERE inventory_item_id = item_id
35 AND organization_id = org_id;
36 RETURN l_result;
37 EXCEPTION
38 WHEN OTHERS THEN
39 RETURN '';
40 END get_key_flex_item;
41
42 FUNCTION get_key_flex_location(loc_id IN NUMBER, org_id IN NUMBER) RETURN VARCHAR2 IS
43 l_result VARCHAR2(2000);
44 BEGIN
45 SELECT concatenated_segments
46 INTO l_result
47 FROM mtl_item_locations_kfv
48 WHERE inventory_location_id = loc_id
49 AND organization_id = org_id;
50 RETURN l_result;
51 EXCEPTION
52 WHEN OTHERS THEN
53 RETURN '';
54 END get_key_flex_location;
55
56
57 FUNCTION find_unset_node RETURN NUMBER IS
58 i NUMBER;
59 last NUMBER := nodes.LAST;
60 Begin
61 if (nodes.COUNT <= 0) then
62 return -1;
63 end if;
64
65 i := nodes.FIRST;
66 LOOP
67 if nodes(i).x = XY_MAX then
68 return i;
69 end if;
70 exit when (i = last);
71 i := nodes.NEXT(i);
72 END LOOP;
73
74 return -1;
75
76 End find_unset_node;
77
78 /* Recursively find a connected sub-graph and flatten it out
79 * by setting their x-coordinate linearly.
80 */
81 PROCEDURE find_component( n NUMBER,
82 x NUMBER) IS
83 i NUMBER;
84 j NUMBER ;
85 nn NUMBER;
86 Begin
87 if links.COUNT <= 0 then
88 return;
89 end if;
90
91 i := links.FIRST;
92 j := links.LAST;
93 LOOP
94 if (not states.exists(i)) then
95 if (links(i).n1 = n) then
96 nn := links(i).n2;
97 if (nodes(nn).x = XY_MAX) then
98 nodes(nn).x := x + 1;
99 nodes(nn).sub := component;
100 if xmax < x + 1 then
101 xmax := x + 1;
102 end if;
103 -- links.DELETE(i);
104 states(i) := TRUE;
105 find_component(nn, x + 1);
106 end if;
107 elsif (links(i).n2 = n) then
108 nn := links(i).n1;
109 if (nodes(nn).x = XY_MAX) then
110 nodes(nn).x := x - 1;
111 nodes(nn).sub := component;
112 if xmin > x - 1 then
113 xmin := x - 1;
114 end if;
115 -- links.DELETE(i);
116 states(i) := TRUE;
117 find_component(nn, x - 1);
118 end if;
119 end if;
120 end if;
121
122 exit when i = j;
123 i := links.NEXT(i);
124
125 END LOOP;
126
127 End find_component;
128
129 /******************************************************************************
130 * set_graph_coordinates, when passed in a graph, will position the nodes
131 * automatically.
132 * First we position all notes in a logical x-coordinate system;
133 * After this step, all nodes are divided into one or more connected components
134 * of a directed graph. X-position of a node is actually the relative position
135 * to its neighbors; Then for each component, at each x-position, we put nodes
136 * there into different logical y-positions (also relative position);
137 * Finally, we move those nodes into world coordinate system one component
138 * above another (vertically).
139 ******************************************************************************/
140 PROCEDURE set_graph_coordinates(llist LINK_LIST, nlist IN OUT NOCOPY NODE_LIST) IS
141 i NUMBER;
142 last NUMBER;
143
144 TYPE Range_t IS RECORD (
145 xmin NUMBER,
146 xmax NUMBER,
147 ymin NUMBER,
148 ymax NUMBER);
149
150 TYPE Range_List IS TABLE OF Range_t INDEX BY BINARY_INTEGER;
151
152 ranges Range_List;
153 dist NUMBER;
154 empty_states FLAG_LIST;
155
156 Begin
157 nodes := nlist;
158 links := llist;
159 states := empty_states;
160
161 if (nodes.COUNT <= 0) then
162 return;
163 end if;
164
165 -- initialize coordinates to XY_MAX
166
167 i := nodes.FIRST;
168 last := nodes.LAST;
169 LOOP
170 nodes(i).x := XY_MAX;
171 nodes(i).y := XY_MAX;
172 exit when (i = last);
173 i := nodes.NEXT(i);
174 END LOOP;
175
176 -- find all connected components, set X coordinates
177 component := 0;
178
179 LOOP
180 i := find_unset_node;
181 exit when i = -1;
182 component := component + 1;
183 xmin := 0;
184 xmax := 0;
185 nodes(i).x := 0;
186 nodes(i).sub := component;
187 find_component(i, 0);
188 ranges(component).xmin := xmin;
189 ranges(component).xmax := xmax;
190 END LOOP;
191
192 -- set Y coordinates
193
194 for c in 1..component loop
195 ymin := 0;
196 ymax := 0;
197 for x in ranges(c).xmin..ranges(c).xmax loop
198 dist := 0;
199 i := nodes.FIRST;
200 loop
201 if (nodes(i).sub = c and nodes(i).x = x) then
202 if (mod(dist,2) = 1) then
203 nodes(i).y := 0 - (floor(dist / 2) + 1);
204 if (ymin > nodes(i).y) then
205 ymin := nodes(i).y;
206 end if;
207 else
208 nodes(i).y := dist / 2;
209 if (ymax < nodes(i).y) then
210 ymax := nodes(i).y;
211 end if;
212 end if;
213 dist := dist + 1;
214 end if;
215 exit when i = last;
216 i := nodes.NEXT(i);
217 end loop;
218 end loop;
219 ranges(c).ymin := ymin;
220 ranges(c).ymax := ymax;
221 end loop;
222
223 -- move x, y to positive area
224
225 dist := 0;
226 ymax := 0;
227 for c in 1..component loop
228 ymax := ranges(c).ymax - ranges(c).ymin;
229 ranges(c).ymax := dist;
230 dist := dist + ymax + 1;
231 end loop;
232
233 i := nodes.FIRST;
234 loop
235 nodes(i).x := nodes(i).x - ranges(nodes(i).sub).xmin;
236 nodes(i).y := nodes(i).y - ranges(nodes(i).sub).ymin + ranges(nodes(i).sub).ymax;
237 exit when i = last;
238 i := nodes.NEXT(i);
239 end loop;
240
241 nlist := nodes;
242
243 End set_graph_coordinates;
244
245 FUNCTION Get_Install_Status RETURN VARCHAR2
246 IS
247 l_retval BOOLEAN;
248 l_status VARCHAR2(1);
249 l_industry VARCHAR2(1);
250
251 BEGIN
252
253 l_retval := fnd_installation.get(FLM_APPLICATION_ID,
254 FLM_APPLICATION_ID,
255 l_status,
256 l_industry);
257
258 IF (l_status IN ('I', 'S', 'N')) THEN
259 RETURN (l_status);
260 ELSE
261 RETURN ('N');
262 END IF;
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 return('N');
267 END Get_Install_Status;
268
269
270
271 -- dyanmic sql binding
272 PROCEDURE init_bind
273 IS
274 BEGIN
275
276 g_bind_table.DELETE;
277
278 END init_bind;
279
280 FUNCTION get_next_bind_seq RETURN NUMBER
281 IS
282 BEGIN
283
284 return g_bind_table.COUNT + 1;
285
286 END get_next_bind_seq;
287
288 PROCEDURE add_bind( p_name IN VARCHAR2, p_string IN VARCHAR2)
289 IS
290 l_cnt NUMBER;
291 BEGIN
292
293 l_cnt := g_bind_table.COUNT + 1;
294
295 g_bind_table(l_cnt).name := p_name;
296 g_bind_table(l_cnt).data_type := 1;
297 g_bind_table(l_cnt).value_string := p_string;
298
299 END add_bind;
300
301 PROCEDURE add_bind( p_name IN VARCHAR2, p_number IN NUMBER)
302 IS
303 l_cnt NUMBER;
304 BEGIN
305
306 l_cnt := g_bind_table.COUNT + 1;
307
308 g_bind_table(l_cnt).name := p_name;
309 g_bind_table(l_cnt).data_type := 2;
310 g_bind_table(l_cnt).value_number:= p_number;
311
312 END add_bind;
313
314 PROCEDURE add_bind ( p_name IN VARCHAR2, p_date IN DATE)
315 IS
316 l_cnt NUMBER;
317 BEGIN
318
319 l_cnt := g_bind_table.COUNT + 1;
320
321 g_bind_table(l_cnt).name := p_name;
322 g_bind_table(l_cnt).data_type := 3;
323 g_bind_table(l_cnt).value_date:= p_date;
324
325 END add_bind;
326
327
328 PROCEDURE do_binds( p_cursor IN INTEGER )
329 IS
330 l_cur NUMBER;
331 l_msg VARCHAR2(1024);
332 BEGIN
333 IF g_bind_table.COUNT > 0 THEN
334
335 l_cur := g_bind_table.first;
336
337 LOOP
338
339 l_msg := '#' || l_cur || ' ' || g_bind_table(l_cur).name || ' = ';
340
341 IF g_bind_table(l_cur).data_type = 1 THEN -- string
342 dbms_sql.bind_variable(p_cursor,
343 g_bind_table(l_cur).name, g_bind_table(l_cur).value_string);
344 l_msg := l_msg || '''' || g_bind_table(l_cur).value_string || '''';
345 ELSIF g_bind_table(l_cur).data_type = 2 THEN -- number
346 dbms_sql.bind_variable(p_cursor,
347 g_bind_table(l_cur).name, g_bind_table(l_cur).value_number);
348 l_msg := l_msg || g_bind_table(l_cur).value_number;
349 ELSIF g_bind_table(l_cur).data_type = 3 THEN -- date
350 dbms_sql.bind_variable(p_cursor,
351 g_bind_table(l_cur).name, g_bind_table(l_cur).value_date);
352 l_msg := l_msg || to_char(g_bind_table(l_cur).value_date,
353 'YYYY-MM-DD HH24:MI:SS');
354 END IF;
355
356 -- dbms_output.put_line(l_msg);
357 EXIT WHEN l_cur = g_bind_table.last;
358
359 l_cur := g_bind_table.next(l_cur);
360
361 END LOOP;
362
363 END IF;
364
365 END do_binds;
366
367
368 FUNCTION Category_Where_Clause ( p_cat_lo IN VARCHAR2,
369 p_cat_hi IN VARCHAR2,
370 p_table_name IN VARCHAR2,
371 p_cat_struct_id IN NUMBER,
372 p_where OUT NOCOPY VARCHAR2,
376 l_delim varchar2(1);
373 x_err_buf OUT NOCOPY VARCHAR2 )
374 RETURN BOOLEAN IS
375 l_num NUMBER;
377 l_append varchar2(1000) := NULL;
378 l_where varchar2(2000) := NULL;
379 l_cnt Number := 0;
380 l_ctr Number;
381 l_flex_num Number := Null;
382 l_quote varchar2(1) := '''';
383
384 l_bind_name VARCHAR2(256);
385
386 -- fnd_flex_ext.SegmentArray is a table of type varchar2(150)
387 -- we need two local instances
388 l_seg_low fnd_flex_ext.SegmentArray;
389 l_seg_high fnd_flex_ext.SegmentArray;
390
391 -- cursor to get all the application column names for this flexfield
392 CURSOR cur_columns (l_struct_num Number) IS
393 SELECT fs.application_column_name,
394 vs.format_type
395 FROM FND_FLEX_VALUE_SETS vs,
396 FND_ID_FLEX_SEGMENTS_VL fs
397 WHERE fs.application_id = 401
398 AND fs.id_flex_code = 'MCAT'
399 AND fs.id_flex_num = l_struct_num
400 AND fs.enabled_flag = 'Y'
401 AND fs.display_flag = 'Y'
402 AND vs.flex_value_set_id = fs.flex_value_set_id
403 ORDER BY
404 fs.segment_num;
405
406 BEGIN
407 -- get the flex id number
408 SELECT id_flex_num
409 INTO l_flex_num
410 FROM fnd_id_flex_structures fs
411 WHERE fs.id_flex_code = 'MCAT'
412 AND fs.id_flex_num = p_cat_struct_id;
413 l_delim := fnd_flex_ext.get_delimiter('INV','MCAT',l_flex_num);
414 l_num := fnd_flex_ext.breakup_segments( p_cat_lo, l_delim, l_seg_low);
415 l_num := fnd_flex_ext.breakup_segments( p_cat_hi, l_delim, l_seg_high);
416
417 -- go ahead and build the where clause
418
419 FOR curtemp IN cur_columns(l_flex_num) LOOP
420 l_cnt := l_cnt + 1;
421 IF ( (l_seg_low(l_cnt) IS NOT NULL) OR
422 (l_seg_high(l_cnt) IS NOT NULL) ) THEN
423 IF l_where IS NOT NULL THEN
424 l_append := l_where||' AND ';
425 ELSE
426 l_append := NULL;
427 END IF;
428
429 IF l_seg_low(l_cnt) IS NOT NULL THEN
430 l_bind_name := ':cat_seg_low_' || l_cnt;
431 IF curtemp.FORMAT_TYPE = 'N' THEN
432 l_where := l_append||' to_number('|| p_table_name || '.' ||
433 curtemp.APPLICATION_COLUMN_NAME||
434 ')' || ' >= ' || l_bind_name;
435 add_bind(l_bind_name, to_number(l_seg_low(l_cnt)));
436 ELSE
437 l_where := l_append||' '|| p_table_name || '.' ||
438 curtemp.APPLICATION_COLUMN_NAME||
439 ' >= ' || l_bind_name;
440 add_bind(l_bind_name, l_seg_low(l_cnt) );
441 END IF;
442 END IF;
443
444 IF l_where IS NOT NULL THEN
445 l_append := l_where||' AND ';
446 ELSE
447 l_append := null;
448 END IF;
449
450 IF l_seg_high(l_cnt) IS NOT NULL THEN
451 l_bind_name := ':cat_seg_high_' || l_cnt;
452 IF curtemp.FORMAT_TYPE = 'N' THEN
453 l_where := l_append||' to_number('|| p_table_name || '.' ||
454 curtemp.APPLICATION_COLUMN_NAME||
455 ')' || ' <= ' || l_bind_name;
456 add_bind(l_bind_name, to_number(l_seg_high(l_cnt)));
457 ELSE
458 l_where := l_append||' '|| p_table_name || '.' ||
459 curtemp.APPLICATION_COLUMN_NAME||
460 ' <= ' || l_bind_name;
461 add_bind(l_bind_name, l_seg_high(l_cnt));
462 END IF;
463 END IF;
464 END IF;
465 END LOOP;
466 p_where := l_where || ' ';
467
468 RETURN TRUE;
469
470 --exception handling
471 EXCEPTION
472 WHEN NO_DATA_FOUND THEN
473 p_where := l_where;
474 RETURN TRUE;
475 WHEN OTHERS THEN
476 x_err_buf := 'Unexpected SQL Error: '||sqlerrm;
477 RETURN FALSE;
478
479 END Category_Where_Clause;
480
481 FUNCTION Item_Where_Clause( p_item_lo IN VARCHAR2,
482 p_item_hi IN VARCHAR2,
483 p_table_name IN VARCHAR2,
484 x_where OUT NOCOPY VARCHAR2,
485 x_err_buf OUT NOCOPY VARCHAR2)
486 RETURN BOOLEAN IS
487
488 l_num NUMBER;
489 l_delim varchar2(1);
490 l_append varchar2(1000) := NULL;
491 l_where varchar2(2000) := NULL;
492 l_cnt Number := 0;
493 l_no_of_segs Number := 0;
494 l_flex_num Number := Null;
495 l_quote varchar2(1) := '''';
496
497 -- we need two local instances
498 l_seg_low fnd_flex_server1.stringarray;
499 l_seg_high fnd_flex_server1.stringarray;
500
501
502 l_bind_name VARCHAR2(255);
503
504 -- cursor to get all the application column names for this flexfield
505 CURSOR cur_columns (l_struct_num Number) IS
506 SELECT fs.application_column_name,
507 vs.format_type
508 FROM FND_FLEX_VALUE_SETS vs,
509 FND_ID_FLEX_SEGMENTS_VL fs
510 WHERE fs.application_id = 401
511 AND fs.id_flex_code = 'MSTK'
512 AND fs.id_flex_num = l_struct_num
513 AND fs.enabled_flag = 'Y'
514 AND fs.display_flag = 'Y'
515 AND vs.flex_value_set_id = fs.flex_value_set_id
516 ORDER BY
517 fs.segment_num;
518
519 BEGIN
520 -- get the flex id number
521 SELECT id_flex_num
522 INTO l_flex_num
523 FROM fnd_id_flex_structures
527 INTO l_no_of_segs
524 WHERE id_flex_code = 'MSTK';
525
526 SELECT count(*)
528 FROM FND_ID_FLEX_SEGMENTS_VL fs
529 WHERE fs.application_id = 401
530 AND fs.id_flex_code = 'MSTK'
531 AND fs.id_flex_num = l_flex_num
532 AND fs.enabled_flag = 'Y'
533 AND fs.display_flag = 'Y';
534
535 l_delim := fnd_flex_ext.get_delimiter('INV','MSTK',l_flex_num);
536 fnd_flex_server.parse_flex_values(p_item_lo,
537 l_delim,
538 l_no_of_segs,
539 l_seg_low,
540 l_num);
541
542 fnd_flex_server.parse_flex_values(p_item_hi,
543 l_delim,
544 l_no_of_segs,
545 l_seg_high,
546 l_num);
547
548 -- go ahead and build the where clause
549
550 FOR curtemp IN cur_columns(l_flex_num) LOOP
551 l_cnt := l_cnt + 1;
552 IF ( (l_seg_low(l_cnt) IS NOT NULL) OR
553 (l_seg_high(l_cnt) IS NOT NULL) ) THEN
554 IF l_where IS NOT NULL THEN
555 l_append := l_where||' AND ';
556 ELSE
557 l_append := NULL;
558 END IF;
559
560 IF l_seg_low(l_cnt) IS NOT NULL THEN
561 l_bind_name := ':item_seg_low_' || l_cnt;
562
563 IF curtemp.FORMAT_TYPE = 'N'THEN
564 l_where := l_append||' to_number('|| p_table_name || '.' ||
565 curtemp.APPLICATION_COLUMN_NAME||
566 ')'||' >= ' || l_bind_name ;
567
568 add_bind(l_bind_name, to_number(l_seg_low(l_cnt)) );
569 ELSE
570 l_where := l_append||' '|| p_table_name || '.' ||
571 curtemp.APPLICATION_COLUMN_NAME||
572 ' >= '|| l_bind_name ;
573
574 add_bind(l_bind_name, l_seg_low(l_cnt));
575 END IF;
576
577 END IF;
578
579 IF l_where IS NOT NULL THEN
580 l_append := l_where||' AND ';
581 ELSE
582 l_append := null;
583 END IF;
584
585 IF l_seg_high(l_cnt) IS NOT NULL THEN
586 l_bind_name := ':item_seg_high_' || l_cnt;
587
588 IF curtemp.FORMAT_TYPE = 'N' THEN
589 l_where := l_append||' to_number('|| p_table_name || '.' ||
590 curtemp.APPLICATION_COLUMN_NAME||
591 ')'||' <= ' || l_bind_name;
592
593 add_bind(l_bind_name, to_number(l_seg_high(l_cnt)) );
594 ELSE
595 l_where := l_append||' '|| p_table_name || '.' ||
596 curtemp.APPLICATION_COLUMN_NAME||
597 ' <= ' || l_bind_name ;
598
599 add_bind(l_bind_name, l_seg_high(l_cnt));
600 END IF;
601 END IF;
602 END IF;
603 END LOOP;
604
605 x_where := l_where || ' ';
606
607 RETURN TRUE;
608
609 --exception handling
610 EXCEPTION
611 WHEN OTHERS THEN
612 x_err_buf := 'Unexpected SQL Error: '||sqlerrm;
613 RETURN FALSE;
614
615 END Item_Where_Clause;
616
617
618 END flm_util;