1 Package body ASO_Def_Util AS
2 /* $Header: asodutlb.pls 120.1 2005/07/01 10:19:48 appldev noship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Def_Util';
7
8 -- parameters
9 TYPE Parameter_Rec_Type IS RECORD
10 (
11 parameter_name varchar2(30),
12 parameter_value varchar2(255)
13 );
14
15 TYPE Parameter_Tbl_Type IS TABLE OF Parameter_Rec_Type
16 INDEX BY BINARY_INTEGER;
17
18 g_parameter_tbl Parameter_Tbl_Type;
19 ---------------------------------------
20
21 ---------------------------------------------
22 Procedure Set_Parameter_Value(
23 p_param_name IN varchar2,
24 p_value in varchar2
25 )
26 IS
27 l_add_parameter boolean := TRUE;
28 l_count number := g_parameter_tbl.COUNT;
29 BEGIN
30
31 -- if the parameter name already exists in the
32 -- table, assign assign the value to that index. otherwise add
33 -- a new element
34 for i in 1..l_count loop
35 if (g_parameter_tbl(i).parameter_name = p_param_name) then
36 g_parameter_tbl(i).parameter_value := p_value;
37 l_add_parameter := FALSE;
38 exit;
39 end if;
40 end loop;
41
42 if (l_add_parameter = TRUE) then
43 g_parameter_tbl(l_count+1).parameter_name := p_param_name;
44 g_parameter_tbl(l_count+1).parameter_value := p_value;
45 end if;
46
47 END Set_Parameter_Value;
48 --------------------------------
49
50 Function Get_Parameter_Value(
51 p_param_name in varchar2
52 )
53 Return Varchar2
54 IS
55 BEGIN
56 for i in 1..g_parameter_tbl.COUNT loop
57 if (g_parameter_tbl(i).parameter_name = p_param_name) then
58 return g_parameter_tbl(i).parameter_value;
59 end if;
60 end loop;
61 return null;
62 END Get_Parameter_Value;
63 -----------------------------------
64
65 Function Get_Database_Default_Varchar2(
66 p_column_name in varchar2,
67 p_table_name in varchar2
68 )
69 Return Varchar2
70 IS
71 l_default_value long;
72 l_status varchar2(30);
73 l_schema varchar2(30);
74 l_industry varchar2(30);
75 l_return_status boolean;
76
77 CURSOR C(l_schema varchar2)
78 IS
79 SELECT data_default
80 FROM all_tab_columns
81 WHERE table_name = p_table_name
82 AND column_name = p_column_name
83 AND owner = l_schema;
84
85 BEGIN
86 l_return_status := FND_INSTALLATION.get_app_info('ONT',l_status,l_industry,l_schema);
87 Open C (l_schema);
88 Fetch C into l_default_value;
89 close C;
90 return l_default_value;
91
92 END Get_Database_Default_Varchar2;
93 --------------------------------------
94
95 Function Get_Attr_Default_Varchar2(
96 p_attribute_code in varchar2,
97 p_application_id in varchar2
98 )
99 Return Varchar2
100 IS
101 l_value_varchar2 AK_ATTRIBUTES.DEFAULT_VALUE_VARCHAR2%TYPE;
102 l_value_number number;
103 l_value_date date;
104
105 CURSOR C IS
106 SELECT default_value_varchar2, default_value_number, default_value_date
107 FROM ak_Attributes
108 WHERE attribute_application_id = p_application_id
109 AND attribute_code = p_attribute_code;
110
111 BEGIN
112
113 open C;
114 fetch C into l_value_varchar2, l_value_number, l_value_date;
115 CLOSE C;
116
117 if (l_value_date is not null) then
118 l_value_varchar2 := l_value_date;
119 elsif (l_value_number is not null) then
120 l_value_varchar2 := l_value_number;
121 end if;
122 return l_value_varchar2;
123
124 END Get_Attr_Default_Varchar2;
125 ---------------------------------------
126
127 Function Get_ObjAttr_Default_Varchar2(
128 p_attribute_code in varchar2,
129 p_database_object_name in varchar2,
130 p_application_id in varchar2
131 ) Return Varchar2
132 IS
133 l_value_varchar2 AK_OBJECT_ATTRIBUTES.DEFAULT_VALUE_VARCHAR2%TYPE;
134 l_value_number number;
135 l_value_date date;
136
137 CURSOR C IS
138 SELECT default_value_varchar2, default_value_number, default_value_date
139 FROM ak_OBJECT_Attributes
140 WHERE attribute_application_id = p_application_id
141 AND database_object_name = p_database_object_name
142 AND attribute_code = p_attribute_code;
143
144 BEGIN
145
146 open C;
147 fetch C into l_value_varchar2, l_value_number, l_value_date;
148 Close C;
149
150 if (l_value_date is not null) then
151 l_value_varchar2 := l_value_date;
152 elsif (l_value_number is not null) then
153 l_value_varchar2 := l_value_number;
154 end if;
155 return l_value_varchar2;
156
157 END Get_ObjAttr_Default_Varchar2;
158 -----------------------------------
159
160 -- resolve system variable/expression
161 Function Get_Expression_Value_Varchar2(
162 p_expression_string in varchar2
163 ) Return Varchar2
164 IS
165 l_sql_String long;
166 l_expression_value varchar2(255);
167 BEGIN
168
169 l_sql_string := 'SELECT '||p_expression_string||' FROM SYS.DUAL';
170 EXECUTE IMMEDIATE l_sql_string INTO l_expression_value;
171
172 RETURN l_expression_value;
173
174 END Get_Expression_Value_Varchar2;
175
176 ---------------------------------------------------------------------
177 -- resolve system variable/expression for DATE attributes
178 ---------------------------------------------------------------------
179 Function Get_Expression_Value_Date(
180 p_expression_string in varchar2
181 ) Return Date
182 IS
183 l_sql_string VARCHAR2(500);
184 l_return_value DATE;
185 BEGIN
186
187 IF lower(p_expression_string) = 'sysdate' THEN
188 l_return_value := sysdate;
189 ELSE
190 l_sql_string := 'SELECT '||p_expression_string||' FROM DUAL';
191 EXECUTE IMMEDIATE l_sql_string INTO l_return_value;
192 END IF;
193
194 RETURN l_return_value;
195
196 END Get_Expression_Value_Date;
197 -------------------------------------
198
199
200 Function Validate_Value(p_required_value in varchar2,
201 p_validation_op in varchar2,
202 p_actual_value in varchar2
203 ) Return Boolean
204 IS
205 BEGIN
206 If (p_validation_op = 'IS NULL') then
207 If (p_actual_value IS NULL) then
208 return TRUE;
209 Else
210 return FALSE;
211 End If;
212 Elsif (p_validation_op = 'IS NOT NULL') then
213 If (p_actual_value IS NOT NULL) then
214 return TRUE;
215 Else
216 return FALSE;
217 End If;
218 Elsif (p_validation_op = '=') then
219
220 If (p_actual_value = p_required_value) then
221 return TRUE;
222 Else
223
224 return FALSE;
225 End If;
226 Elsif (p_validation_op = '!=') then
227 If (p_actual_value <> p_required_value) then
228 return TRUE;
229 Else
230 return FALSE;
231 End If;
232 Elsif (p_validation_op = '<') then
233 If (p_actual_value < p_required_value) then
234 return TRUE;
235 Else
236 return FALSE;
237 End If;
238 Elsif (p_validation_op = '<=') then
239 If (p_actual_value <= p_required_value) then
240
241 return TRUE;
242 Else
243 return FALSE;
244 End If;
245 Elsif (p_validation_op = '>') then
246 If (p_actual_value > p_required_value) then
247 return TRUE;
248 Else
249 return FALSE;
250 End If;
251 Elsif (p_validation_op = '>=') then
252 If (p_actual_value >= p_required_value) then
253 return TRUE;
254 Else
255 return FALSE;
256 End If;
257 End If;
258 return FALSE;
259 END Validate_Value;
260
261
262 FUNCTION Get_API_Value_Varchar2 (
263 p_api_name in varchar2,
264 p_database_object_name in varchar2,
265 p_attribute_code in varchar2)
266 RETURN VARCHAR2
267 IS
268 l_func_block VARCHAR2(4000);
269 l_return_value VARCHAR2(2000);
270 begin
271
272 l_func_block :=
273 'declare '||
274 'begin '||
275 ':return_value := '||
276 p_api_name||'(p_database_object_name => :p_database_object_name
277 ,p_attribute_code => :p_attribute_code);'|| ' end;' ;
278
279 EXECUTE IMMEDIATE l_func_block USING OUT l_return_value,
280 IN p_database_object_name, IN p_attribute_code;
281
282 RETURN l_return_value;
283
284 END Get_API_Value_Varchar2;
285
286 FUNCTION Get_API_Value_Date (
287 p_api_name in varchar2,
288 p_database_object_name in varchar2,
289 p_attribute_code in varchar2)
290 RETURN DATE
291 IS
292 l_func_block VARCHAR2(4000);
293 l_return_value DATE;
294 begin
295
296 l_func_block :=
297 'declare '||
298 'begin '||
299 ':return_value := '||
300 p_api_name||'(p_database_object_name => :p_database_object_name
301 ,p_attribute_code => :p_attribute_code);'|| ' end;' ;
302
303 EXECUTE IMMEDIATE l_func_block USING OUT l_return_value,
304 IN p_database_object_name, IN p_attribute_code;
305
306 RETURN l_return_value;
307
308 END Get_API_Value_Date;
309
310
311 FUNCTION Get_Sequence_Value
312 ( p_sequence_name IN VARCHAR2)
313 RETURN VARCHAR2
314 IS
315 l_next_val NUMBER;
316 l_select_stmt VARCHAR2(200);
317 BEGIN
318
319 l_select_stmt := 'SELECT '||p_sequence_name||'.NEXTVAL FROM DUAL';
320 EXECUTE IMMEDIATE l_select_stmt INTO l_next_val;
321
322 RETURN to_char(l_next_val);
323
324 END Get_Sequence_Value;
325
326
327 ---------------------------------------------------------------------
328 -- PROCEDURE Add_Invalid_Rule_Message
329 -- Fix bug#1063896
330 -- This procedure is called from the generated attribute handlers
331 -- packages when there is a runtime error when evaluating a defaulting
332 -- rule.
333 -- This procedure adds an error message (OE_DEF_INVALID_RULE)
334 -- to the stack after resolving the tokens for the attribute being
335 -- defaulted and the default source type, default source/value and
336 -- also prints out the SQL error message.
337 ---------------------------------------------------------------------
338 PROCEDURE Add_Invalid_Rule_Message
339 (p_attribute_code IN VARCHAR2
340 ,p_rule_id IN NUMBER DEFAULT NULL
341 ,p_src_type IN VARCHAR2 DEFAULT NULL
342 ,p_src_api_name IN VARCHAR2 DEFAULT NULL
343 ,p_src_database_object_name IN VARCHAR2 DEFAULT NULL
344 ,p_src_attribute_code IN VARCHAR2 DEFAULT NULL
345 ,p_src_constant_value IN VARCHAR2 DEFAULT NULL
346 ,p_src_profile_option IN VARCHAR2 DEFAULT NULL
347 ,p_src_system_variable_expr IN VARCHAR2 DEFAULT NULL
348 ,p_src_sequence_name IN VARCHAR2 DEFAULT NULL
349 )
350 IS
351 l_default_source VARCHAR2(240);
352 l_attribute VARCHAR2(80);
353 l_src_attribute VARCHAR2(80);
354 l_src_object VARCHAR2(30);
355 l_src_type VARCHAR2(80);
356 l_src_type_value VARCHAR2(80);
357 l_error VARCHAR2(500);
358 l_src_api_name VARCHAR2(2031);
359 l_src_database_object_name VARCHAR2(30);
360 l_src_attribute_code VARCHAR2(30);
361 l_src_constant_value VARCHAR2(240);
362 l_src_profile_option VARCHAR2(240);
363 l_src_system_variable_expr VARCHAR2(255);
364 l_src_sequence_name VARCHAR2(30);
365 BEGIN
366
367 l_error := substr(sqlerrm,1,500);
368
369 if p_rule_id is not null then
370 select src_type, src_api_pkg||'.'||src_api_fn
371 ,src_database_object_name, src_attribute_code
372 ,src_constant_value, src_profile_option
373 ,src_system_variable_expr, src_sequence_name
374 into l_src_type, l_src_api_name
375 ,l_src_database_object_name, l_src_attribute_code
376 ,l_src_constant_value, l_src_profile_option
377 ,l_src_system_variable_expr, l_src_sequence_name
378 from oe_def_attr_def_rules
379 where attr_def_rule_id = p_rule_id;
380 else
381 l_src_type := p_src_type;
382 l_src_api_name := p_src_api_name;
383 l_src_database_object_name := p_src_database_object_name;
384 l_src_attribute_code := p_src_attribute_code;
385 l_src_constant_value := p_src_constant_value;
386 l_src_profile_option := p_src_profile_option;
387 l_src_system_variable_expr := p_src_system_variable_expr;
388 l_src_sequence_name := p_src_sequence_name;
389 end if;
390
391 -- If src_type_code is same as the attribute_code, then it is
392 -- a CONSTANT value rule
393 select meaning
394 into l_src_type_value
395 from oe_lookups
396 where lookup_type = 'DEFAULTING_SOURCE_TYPE'
397 and lookup_code = decode(l_src_type,p_attribute_code,'CONSTANT'
398 ,l_src_type);
399
400 l_attribute := OE_Order_Util.Get_Attribute_Name(p_attribute_code);
401
402 if l_src_attribute_code is not null then
403 l_src_attribute := OE_Order_Util.Get_Attribute_Name(l_src_attribute_code);
404 end if;
405
406 if l_src_database_object_name is not null then
407 select name
408 into l_src_object
409 from ak_objects_vl
410 where database_object_name = l_src_database_object_name;
411 end if;
412
413 -- Set up the token for the default source/value based on the
414 -- source type
415
416 if l_src_type = 'SAME_RECORD' then
417
418 l_default_source := l_src_attribute;
419
420 elsif l_src_type = 'RELATED_RECORD' then
421
422 l_default_source := l_src_object||'.'||l_src_attribute;
423
424 elsif l_src_type = 'API' then
425
426 l_default_source := substr(l_src_api_name,1,240);
427 if l_src_database_object_name is not null then
428 l_default_source := substr(l_default_source
429 ||'.'||l_src_database_object_name,1,240);
430 end if;
431 if l_src_attribute_code is not null then
432 l_default_source := substr(l_default_source
433 ||'.'||l_src_attribute_code,1,240);
434 end if;
435
436 elsif l_src_type = 'PROFILE_OPTION' then
437
438 select user_profile_option_name
439 into l_default_source
440 from fnd_profile_options_vl
441 where profile_option_name = l_src_profile_option;
442
443 elsif l_src_type = 'CONSTANT' then
444
445 l_default_source := l_src_constant_value;
446
447 elsif l_src_type = 'SYSTEM' then
448
449 l_default_source := l_src_system_variable_expr;
450
451 elsif l_src_type = 'SEQUENCE' then
452
453 l_default_source := l_src_sequence_name;
454
455 elsif l_src_type = p_attribute_code then
456
457 fnd_flex_descval.set_context_value(p_attribute_code);
458 fnd_flex_descval.set_column_value('SRC_CONSTANT_VALUE'
459 ,l_src_constant_value);
460 if fnd_flex_descval.validate_desccols
461 ('ONT','Defaulting Rules Flexfield') then
462 l_default_source := fnd_flex_descval.concatenated_values;
463 else
464 raise_application_error(-20000,fnd_flex_descval.error_message);
465 end if;
466
467 end if;
468
469 FND_MESSAGE.SET_NAME('ONT','OE_DEF_INVALID_RULE');
470 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
471 FND_MESSAGE.SET_TOKEN('SOURCE_TYPE',l_src_type_value);
472 FND_MESSAGE.SET_TOKEN('DEFAULT_SOURCE',l_default_source);
473 FND_MESSAGE.SET_TOKEN('ERROR',l_error);
474 OE_MSG_PUB.ADD;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 OE_MSG_PUB.Add_Exc_Msg
479 ( G_PKG_NAME
480 , 'Add_Invalid_Rule_Message');
481 END Add_Invalid_Rule_Message;
482
483 End ASO_Def_Util;
[Home] [Help]