1 PACKAGE BODY OE_SYS_PARAMETERS_UTIL AS
2 /* $Header: OEXUSPMB.pls 120.2 2006/02/22 01:26:19 rmoharan noship $ */
3
4 -- Start of comments
5 -- API name : Get_Value_from_Table
6 -- Type : Public
7 -- Description : This api will get the value from the table based on table information and the code
8 -- Parameters :
9 -- IN : p_table_r IN fnd_vset.table_r required
10 -- Table information
11 -- p_code IN VARCHAR2 required
12 -- Code/id for which value to be retrived
13 -- OUT x_value OUT VARCHAR2
14 -- value for the code
15 -- End of Comments
16 PROCEDURE Get_Value_from_Table(p_table_r IN fnd_vset.table_r,
17 p_code IN VARCHAR2,
18 x_value OUT NOCOPY VARCHAR2)
19 IS
20 l_selectstmt VARCHAR2(3000) ;
21 l_meaning VARCHAR2(240);
22 l_id VARCHAR2(240);
23 l_value VARCHAR2(240);
24 l_cursor_id INTEGER;
25 l_where_clause
26 fnd_flex_validation_tables.additional_where_clause%type;
27 l_pos1 NUMBER;
28 l_where_length NUMBER;
29 l_cols VARCHAR2(1000);
30 l_retval INTEGER;
31 --
32 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
33 --
34 BEGIN
35 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
36 l_where_clause := p_table_r.where_clause;
37
38 IF instr(upper(l_where_clause),'WHERE ') > 0 then
39 --to include the id column name in the query
40 l_where_clause:= rtrim(ltrim(l_where_clause));
41 l_pos1 := instr(upper(l_where_clause),'WHERE');
42 l_where_length :=LENGTHB('WHERE');
43 l_where_clause:=
44 substr(l_where_clause,l_pos1+l_where_length);
45 IF (p_table_r.id_column_name IS NOT NULL) THEN
46 l_where_clause := 'WHERE '||p_table_r.id_column_name
47 ||' = :p1 AND '||l_where_clause;
48 ELSE
49 l_where_clause := 'WHERE '||p_table_r.value_column_name
50 ||' = :p1 AND '||l_where_clause;
51 END IF;
52 ELSE
53 IF (p_table_r.id_column_name IS NOT NULL) THEN
54 l_where_clause := 'WHERE '||p_table_r.id_column_name
55 ||' = :p1 '||l_where_clause;
56 ELSE
57 l_where_clause := 'WHERE '||p_table_r.value_column_name
58 ||' = :p1 '||l_where_clause;
59 END IF;
60 END IF;
61 l_cols :=p_table_r.value_column_name;
62
63 IF p_table_r.meaning_column_name IS NOT NULL THEN
64 l_cols := l_cols||','||p_table_r.meaning_column_name;
65 ELSE
66 --null;
67 l_cols := l_cols || ', NULL ';
68 END IF;
69
70 IF (p_table_r.id_column_name IS NOT NULL) THEN
71 IF (p_table_r.id_column_type IN ('D', 'N')) THEN
72 l_cols := l_cols || ' , To_char(' ||
73 p_table_r.id_column_name || ')';
74 ELSE
75 l_cols := l_cols || ' , ' ||
76 p_table_r.id_column_name;
77 END IF;
78 ELSE
79 l_cols := l_cols || ', NULL ';
80 END IF;
81
82 l_selectstmt := 'SELECT '||l_cols||' FROM '||p_table_r.table_name||' '||l_where_clause;
83 DBMS_SQL.PARSE(l_cursor_id,l_selectstmt,DBMS_SQL.NATIVE);
84
85 -- Bind variable
86 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':p1',p_code);
87 -- Bind the input variables
88 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_value,240);
89 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,2,l_meaning,240);
90 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,3,l_id,240);
91
92 l_retval := DBMS_SQL.EXECUTE(l_cursor_id);
93
94 LOOP
95 -- Fetch rows in to buffer and check the exit condition from the loop
96 IF( DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0) THEN
97 EXIT;
98 END IF;
99
100 -- Retrieve the rows from buffer into PLSQL variables
101 DBMS_SQL.COLUMN_VALUE(l_cursor_id,1,l_value);
102 DBMS_SQL.COLUMN_VALUE(l_cursor_id,2,l_meaning);
103 DBMS_SQL.COLUMN_VALUE(l_cursor_id,3,l_id);
104 IF l_id IS NOT NULL AND (p_code = l_id) THEN
105 --DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
106 x_value := l_value;
107 ELSIF (p_code = l_value) THEN
108 --DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
109 IF l_meaning IS NOT NULL THEN
110 x_value := l_meaning;
111 ELSE
112 x_value := l_value;
113 END IF;
114 ELSE
115 Null;
116 --value does notmatch, continue search
117 END IF;
118
119 END LOOP;
120 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
121 EXCEPTION
122 WHEN OTHERS THEN
123 oe_debug_pub.add('Get_value_from_table exception');
124 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
125
126 END Get_Value_from_Table;
127
128 -- Start of comments
129 -- API name : Get_num_date_from_canonical
130 -- Type : Public
131 -- Description : This api will convert the value based on format type
132 -- Parameters :
133 -- IN : p_format_type IN fnd_vset.table_r required
134 -- Format type information
135 -- p_value_code IN VARCHAR2 required
136 -- End of Comments
137 FUNCTION Get_num_date_from_canonical(p_format_type IN VARCHAR2,
138 p_value_code IN VARCHAR2)
139 RETURN VARCHAR2
140 IS
141 l_varchar_out varchar2(2000);
142 INVALID_DATA_TYPE EXCEPTION;
143 --
144 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
145 --
146 BEGIN
147 IF p_format_type = 'N' THEN
148 l_varchar_out :=
149 to_char(fnd_number.canonical_to_number(p_value_code));
150 ELSIF p_format_type = 'X' THEN
151 l_varchar_out :=
152 fnd_date.canonical_to_date(p_value_code);
153 ELSIF p_format_type = 'Y' THEN
154 l_varchar_out :=
155 fnd_date.canonical_to_date(p_value_code);
156 ELSIF p_format_type = 'C' THEN
157 l_varchar_out := p_value_code;
158 ELSE
159 l_varchar_out := p_value_code;
160
161 END IF;
162 RETURN l_varchar_out;
163 EXCEPTION
164 When Others Then
165 l_varchar_out := p_value_code;
166 RETURN l_varchar_out;
167
168 END Get_num_date_from_canonical;
169
170 -- Start of comments
171 -- API name : Get_Value
172 -- Type : Public
173 -- Description : This api will get value for the code based on format type and validation type
174 -- rerieved from value_set information
175 -- Parameters :
176 -- IN : p_value_set_id IN NUMBER required
177 -- p_value_code IN VARCHAR2 required
178 -- End of Comments
179 FUNCTION Get_Value(p_value_set_id IN NUMBER,
180 p_value_code IN VARCHAR2)
181 RETURN VARCHAR2
182 IS
183 l_vset_rec fnd_vset.valueset_r;
184 l_format_rec fnd_vset.valueset_dr;
185 l_found BOOLEAN;
186 l_row NUMBER;
187 l_value_rec fnd_vset.value_dr;
188 l_attr_code VARCHAR2(240);
189 l_attr_value VARCHAR2(240);
190 l_value VARCHAR2(240);
191 --
192 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
193 --
194
195 BEGIN
196 fnd_vset.get_valueset(p_value_set_id,l_vset_rec, l_format_rec);
197 l_attr_code := get_num_date_from_canonical
198 (l_format_rec.format_type,p_value_code);
199 IF l_vset_rec.validation_type = 'I' THEN
200 fnd_vset.get_value_init(l_vset_rec, TRUE);
201 fnd_vset.get_value(l_vset_rec, l_row, l_found, l_value_rec);
202
203 IF l_format_rec.Has_Id THEN --id is defined.Hence compare for id
204 WHILE(l_Found) LOOP
205 -- 4284156
206 l_value_rec.id := get_num_date_from_canonical
207 (l_format_rec.format_type,l_value_rec.id);
208 IF l_attr_code = l_value_rec.id THEN
209 l_attr_value := l_value_rec.value;
210 EXIT;
211 END IF;
212 FND_VSET.get_value(l_Vset_rec,l_Row,l_Found,l_Value_rec);
213 END LOOP;
214 ELSE -- id not defined.Hence compare for value
215 WHILE(l_Found) LOOP
216 -- bug 4284156
217 l_value_rec.value := get_num_date_from_canonical
218 (l_format_rec.format_type,l_value_rec.value);
219 IF l_attr_code = l_value_rec.value THEN
220 --5048979
221 l_attr_value := l_value_rec.value; -- l_attr_code;
222 EXIT;
223 END IF;
224 FND_VSET.get_value(l_Vset_rec,l_Row,l_Found,l_Value_rec);
225 END LOOP;
226 END IF;
227 fnd_vset.get_value_end(l_vset_rec);
228
229 ELSIF l_vset_rec.validation_type = 'F' THEN
230 Get_value_from_table(l_vset_rec.table_info,
231 l_attr_code,
232 l_value);
233 l_attr_value := l_value;
234
235 /* IF l_Format_rec.Has_Id Then --id is defined.Hence compare for id
236 IF l_attr_code = l_id THEN
237 l_attr_value := l_value;
238 END IF;
239 ELSIF l_attr_code = l_value THEN
240 l_attr_value := l_value;
241 END IF;
242 */
243 ELSE -- if validation type is not F or I or valueset id is null (not defined)
244 l_attr_value := l_attr_code;
245 END IF;
246 RETURN l_attr_value;
247
248 END Get_Value;
249
250 END OE_SYS_PARAMETERS_UTIL;
[Home] [Help]