1 Package body ONT_Def_Util AS
2 /* $Header: OEXDUTLB.pls 120.2 2005/08/28 22:57:03 shewgupt 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 -- reverted fix for FP bug 4286055 to fix bug 4567396
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 END Get_Expression_Value_Varchar2;
174
175 ---------------------------------------------------------------------
176 -- resolve system variable/expression for DATE attributes
177 ---------------------------------------------------------------------
178 Function Get_Expression_Value_Date(
179 p_expression_string in varchar2
180 ) Return Date
181 IS
182 l_sql_string VARCHAR2(500);
183 l_return_value DATE;
184 BEGIN
185 -- reverted fix for FP bug 4286055 to fix bug 4567396
186 IF lower(p_expression_string) = 'sysdate' THEN
187 l_return_value := sysdate;
188 ELSE
189 l_sql_string := 'SELECT '||p_expression_string||' FROM DUAL';
190 EXECUTE IMMEDIATE l_sql_string INTO l_return_value;
191 END IF;
192 RETURN l_return_value;
193
194 END Get_Expression_Value_Date;
195 -------------------------------------
196
197
198 Function Validate_Value(p_required_value in varchar2,
199 p_validation_op in varchar2,
200 p_actual_value in varchar2
201 ) Return Boolean
202 IS
203 BEGIN
204 If (p_validation_op = 'IS NULL') then
205 If (p_actual_value IS NULL) then
206 return TRUE;
207 Else
208 return FALSE;
209 End If;
210 Elsif (p_validation_op = 'IS NOT NULL') then
211 If (p_actual_value IS NOT NULL) then
212 return TRUE;
213 Else
214 return FALSE;
215 End If;
216 Elsif (p_validation_op = '=') then
217
218 If (p_actual_value = p_required_value) then
219 return TRUE;
220 Else
221
222 return FALSE;
223 End If;
224 Elsif (p_validation_op = '!=') then
225 If (p_actual_value <> p_required_value) then
226 return TRUE;
227 Else
228 return FALSE;
229 End If;
230 Elsif (p_validation_op = '<') then
231 If (p_actual_value < p_required_value) then
232 return TRUE;
233 Else
234 return FALSE;
235 End If;
236 Elsif (p_validation_op = '<=') then
237 If (p_actual_value <= p_required_value) then
238
239 return TRUE;
240 Else
241 return FALSE;
242 End If;
243 Elsif (p_validation_op = '>') then
244 If (p_actual_value > p_required_value) then
245 return TRUE;
246 Else
247 return FALSE;
248 End If;
249 Elsif (p_validation_op = '>=') then
250 If (p_actual_value >= p_required_value) then
251 return TRUE;
252 Else
253 return FALSE;
254 End If;
255 End If;
256 return FALSE;
257 END Validate_Value;
258
259
260 FUNCTION Get_API_Value_Varchar2 (
261 p_api_name in varchar2,
262 p_database_object_name in varchar2,
263 p_attribute_code in varchar2)
264 RETURN VARCHAR2
265 IS
266 l_func_block VARCHAR2(4000);
267 l_return_value VARCHAR2(2000);
268 begin
269
270 l_func_block :=
271 'declare '||
272 'begin '||
273 ':return_value := '||
274 p_api_name||'(p_database_object_name => :p_database_object_name
275 ,p_attribute_code => :p_attribute_code);'|| ' end;' ;
276
277 EXECUTE IMMEDIATE l_func_block USING OUT l_return_value,
278 IN p_database_object_name, IN p_attribute_code;
279
280 RETURN l_return_value;
281
282 END Get_API_Value_Varchar2;
283
284 FUNCTION Get_API_Value_Date (
285 p_api_name in varchar2,
286 p_database_object_name in varchar2,
287 p_attribute_code in varchar2)
288 RETURN DATE
289 IS
290 l_func_block VARCHAR2(4000);
291 l_return_value DATE;
292 begin
293
294 l_func_block :=
295 'declare '||
296 'begin '||
297 ':return_value := '||
298 p_api_name||'(p_database_object_name => :p_database_object_name
299 ,p_attribute_code => :p_attribute_code);'|| ' end;' ;
300
301 EXECUTE IMMEDIATE l_func_block USING OUT l_return_value,
302 IN p_database_object_name, IN p_attribute_code;
303
304 RETURN l_return_value;
305
306 END Get_API_Value_Date;
307
308
309 FUNCTION Get_Sequence_Value
310 ( p_sequence_name IN VARCHAR2)
311 RETURN VARCHAR2
312 IS
313 l_next_val NUMBER;
314 l_select_stmt VARCHAR2(200);
315 BEGIN
316
317 l_select_stmt := 'SELECT '||p_sequence_name||'.NEXTVAL FROM DUAL';
318 EXECUTE IMMEDIATE l_select_stmt INTO l_next_val;
319
320 RETURN to_char(l_next_val);
321
322 END Get_Sequence_Value;
323
324
325 ---------------------------------------------------------------------
326 -- PROCEDURE Add_Invalid_Rule_Message
327 -- Fix bug#1063896
328 -- This procedure is called from the generated attribute handlers
329 -- packages when there is a runtime error when evaluating a defaulting
330 -- rule.
331 -- This procedure adds an error message (OE_DEF_INVALID_RULE)
332 -- to the stack after resolving the tokens for the attribute being
333 -- defaulted and the default source type, default source/value and
334 -- also prints out the SQL error message.
335 ---------------------------------------------------------------------
336 PROCEDURE Add_Invalid_Rule_Message
337 (p_attribute_code IN VARCHAR2
338 ,p_rule_id IN NUMBER DEFAULT NULL
339 ,p_src_type IN VARCHAR2 DEFAULT NULL
340 ,p_src_api_name IN VARCHAR2 DEFAULT NULL
341 ,p_src_database_object_name IN VARCHAR2 DEFAULT NULL
342 ,p_src_attribute_code IN VARCHAR2 DEFAULT NULL
343 ,p_src_constant_value IN VARCHAR2 DEFAULT NULL
344 ,p_src_profile_option IN VARCHAR2 DEFAULT NULL
345 ,p_src_system_variable_expr IN VARCHAR2 DEFAULT NULL
346 ,p_src_sequence_name IN VARCHAR2 DEFAULT NULL
347 )
348 IS
349 l_default_source VARCHAR2(240);
350 l_attribute VARCHAR2(80);
351 l_src_attribute VARCHAR2(80);
352 l_src_object VARCHAR2(30);
353 l_src_type VARCHAR2(80);
354 l_src_type_value VARCHAR2(80);
355 l_error VARCHAR2(500);
356 l_src_api_name VARCHAR2(2031);
357 l_src_database_object_name VARCHAR2(30);
358 l_src_attribute_code VARCHAR2(30);
359 l_src_constant_value VARCHAR2(240);
360 l_src_profile_option VARCHAR2(240);
361 l_src_system_variable_expr VARCHAR2(255);
362 l_src_sequence_name VARCHAR2(30);
363 BEGIN
364
365 l_error := substr(sqlerrm,1,500);
366
367 if p_rule_id is not null then
368 select src_type, src_api_pkg||'.'||src_api_fn
369 ,src_database_object_name, src_attribute_code
370 ,src_constant_value, src_profile_option
371 ,src_system_variable_expr, src_sequence_name
372 into l_src_type, l_src_api_name
373 ,l_src_database_object_name, l_src_attribute_code
374 ,l_src_constant_value, l_src_profile_option
375 ,l_src_system_variable_expr, l_src_sequence_name
376 from oe_def_attr_def_rules
377 where attr_def_rule_id = p_rule_id;
378 else
379 l_src_type := p_src_type;
380 l_src_api_name := p_src_api_name;
381 l_src_database_object_name := p_src_database_object_name;
382 l_src_attribute_code := p_src_attribute_code;
383 l_src_constant_value := p_src_constant_value;
384 l_src_profile_option := p_src_profile_option;
385 l_src_system_variable_expr := p_src_system_variable_expr;
386 l_src_sequence_name := p_src_sequence_name;
387 end if;
388
389 -- If src_type_code is same as the attribute_code, then it is
390 -- a CONSTANT value rule
391 select meaning
392 into l_src_type_value
393 from oe_lookups
394 where lookup_type = 'DEFAULTING_SOURCE_TYPE'
395 and lookup_code = decode(l_src_type,p_attribute_code,'CONSTANT'
396 ,l_src_type);
397
398 l_attribute := OE_Order_Util.Get_Attribute_Name(p_attribute_code);
399
400 if l_src_attribute_code is not null then
401 l_src_attribute := OE_Order_Util.Get_Attribute_Name(l_src_attribute_code);
402 end if;
403
404 if l_src_database_object_name is not null then
405 select name
406 into l_src_object
407 from ak_objects_vl
408 where database_object_name = l_src_database_object_name;
409 end if;
410
411 -- Set up the token for the default source/value based on the
412 -- source type
413
414 if l_src_type = 'SAME_RECORD' then
415
416 l_default_source := l_src_attribute;
417
418 elsif l_src_type = 'RELATED_RECORD' then
419
420 l_default_source := l_src_object||'.'||l_src_attribute;
421
422 elsif l_src_type = 'API' then
423
424 l_default_source := substr(l_src_api_name,1,240);
425 if l_src_database_object_name is not null then
426 l_default_source := substr(l_default_source
427 ||'.'||l_src_database_object_name,1,240);
428 end if;
429 if l_src_attribute_code is not null then
430 l_default_source := substr(l_default_source
431 ||'.'||l_src_attribute_code,1,240);
432 end if;
433
434 elsif l_src_type = 'PROFILE_OPTION' then
435
436 select user_profile_option_name
437 into l_default_source
438 from fnd_profile_options_vl
439 where profile_option_name = l_src_profile_option;
440
441 elsif l_src_type = 'CONSTANT' then
442
443 l_default_source := l_src_constant_value;
444
445 elsif l_src_type = 'SYSTEM' then
446
447 l_default_source := l_src_system_variable_expr;
448
449 elsif l_src_type = 'SEQUENCE' then
450
451 l_default_source := l_src_sequence_name;
452
453 elsif l_src_type = p_attribute_code then
454
455 fnd_flex_descval.set_context_value(p_attribute_code);
456 fnd_flex_descval.set_column_value('SRC_CONSTANT_VALUE'
457 ,l_src_constant_value);
458 if fnd_flex_descval.validate_desccols
459 ('ONT','Defaulting Rules Flexfield') then
460 l_default_source := fnd_flex_descval.concatenated_values;
461 else
462 raise_application_error(-20000,fnd_flex_descval.error_message);
463 end if;
464
465 end if;
466
467 FND_MESSAGE.SET_NAME('ONT','OE_DEF_INVALID_RULE');
468 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',l_attribute);
469 FND_MESSAGE.SET_TOKEN('SOURCE_TYPE',l_src_type_value);
470 FND_MESSAGE.SET_TOKEN('DEFAULT_SOURCE',l_default_source);
471 FND_MESSAGE.SET_TOKEN('ERROR',l_error);
472 OE_MSG_PUB.ADD;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 OE_MSG_PUB.Add_Exc_Msg
477 ( G_PKG_NAME
478 , 'Add_Invalid_Rule_Message');
479 END Add_Invalid_Rule_Message;
480
481 End ONT_Def_Util;
[Home] [Help]