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