PACKAGE BODY: APPS.EDW_MISC_UTIL

Source

1 package body edw_misc_util AS
2 /* $Header: EDWMISCB.pls 115.7 2002/12/05 22:16:07 arsantha ship $ */
3 
4 Procedure globalNamesOff IS
5 l_cid number;
6 l_dummy number;
7 BEGIN
8 
9         l_cid := DBMS_SQL.OPEN_CURSOR;
10         DBMS_SQL.PARSE(l_cid,  ' ALTER SESSION SET global_names = false', DBMS_SQL.NATIVE);
11         l_dummy := DBMS_SQL.EXECUTE(l_cid);
12         DBMS_SQL.CLOSE_CURSOR(l_cid);
13 
14 
15 END;
16 
17 FUNCTION formatNumber(p_input in number, sep in varchar2) return varchar2 is
18 
19 hours number := 0;
20 minutes number := 0;
21 seconds number := 0;
22 l_ret varchar2(200) ;
23 l_input number := 0;
24 begin
25 
26 if (p_input is null) then /* in cases where end date is null, due to error etc */
27 	return '00'||sep||'00'||sep||'00';
28 end if;
29 
30 l_input := ceil(p_input);
31 
32 if (l_input > 3600 ) then -- more than an hour
33 	hours := (l_input - mod(l_input, 3600)) /3600;
34 	l_input := l_input - hours * 3600;
35 	l_ret := hours;
36 else
37 	l_ret := l_ret||'00';
38 end if;
39 
40 l_ret := l_ret ||sep;
41 
42 
43 if (l_input > 60) then -- more than one min
44 	minutes := (l_input - mod (l_input, 60)) / 60;
45 	l_input := l_input - minutes * 60;
46 	if (minutes > 10) then
47         	l_ret := l_ret || minutes;
48 	else
49         	l_ret := l_ret ||'0'||to_char(minutes, '' );
50 	end if;
51 
52 else
53 	l_ret:= l_ret ||'00';
54 end if;
55 
56 
57 l_ret := l_ret ||sep;
58 
59 seconds := l_input;
60 
61 
62 if (seconds > 10) then
63         l_ret := l_ret || seconds;
64 else
65         l_ret := l_ret ||'0'||seconds;
66 end if;
67 
68 return l_ret;
69 end;
70 
71 function get_item_default(l_db_link varchar2) return varchar2 is
72 l_stmt 		varchar2(1000);
73 result		varchar2(60);
74 Type CurTyp is Ref Cursor;
75 cv 		CurTyp;
76 begin
77 
78    edw_misc_util.globalnamesoff;
79    l_stmt:='select mts.category_set_name from mtl_category_sets@'||l_db_link
80       ||' mts, mtl_default_category_sets@'||l_db_link ||' mtd '||
81 	'where mts.category_set_id=mtd.category_set_id
82 	and mtd.functional_area_id=2
83 	and mts.control_level=1';
84 
85     open cv for l_stmt;
86     fetch cv into result;
87     close cv;
88     return result;
89 
90 end get_item_default;
91 
92 function get_itemorg_default(l_db_link varchar2) return varchar2 is
93 l_stmt 		varchar2(1000);
94 result		varchar2(60);
95 Type CurTyp is Ref Cursor;
96 cv 		CurTyp;
97 begin
98 
99    edw_misc_util.globalnamesoff;
100    l_stmt:='select mts.category_set_name from mtl_category_sets@'||l_db_link
101       ||' mts, mtl_default_category_sets@'||l_db_link ||' mtd '||
102 	'where  mts.category_set_id=mtd.category_set_id
103 	and mtd.functional_area_id=1';
104     open cv for l_stmt;
105     fetch cv into result;
106     close cv;
107     return result;
108 
109 end get_itemorg_default;
110 
111 
112 end edw_misc_util;