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