1 PACKAGE BODY FEM_Engines_Pkg AS
2 -- $Header: fem_engs_body.pls 120.2 2006/02/24 15:06:15 ghall ship $
3
4 /***************************************************************************
5 Copyright (c) 2003 Oracle Corporation
6 Redwood Shores, CA, USA
7 All rights reserved.
8 ***************************************************************************
9 FILENAME
10 fem_engs_body.pls
11
12 DESCRIPTION
13
14 HISTORY
15 Tim Moore 20-Dec-2002 Original script
16 Greg Hall 21-Jun-2005 Bug# 4445212: Added procedure Get_PB_Param_Value,
17 for retrieving a process behavior parameter value
18 from the database. Also added procedure
19 Validate_OA_Params as an internal procedure, for
20 use by OA-compliant procedures.
21 G Cheng 20-Feb-2006 Bug 5040902: FND_MESSAGE.Tech_Message should not
22 v115.7 perform any action if the debug log level from
23 the caller is less than the debug level profile
24 option value set by the user.
25 **************************************************************************/
26
27 ---------------------------------------
28 -- Declare Private Package Variables --
29 ---------------------------------------
30
31 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
32 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
33 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
34 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
35 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
36 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
37
38 v_msg_mode NUMBER := 0;
39
40 /***************************************************************************
41 ===========================================================================
42 Private Procedures
43 ===========================================================================
44 ***************************************************************************/
45
46 /****************************************************************************/
47 PROCEDURE Validate_OA_Params (p_api_version IN NUMBER,
48 p_init_msg_list IN VARCHAR2,
49 p_commit IN VARCHAR2,
50 p_encoded IN VARCHAR2,
51 x_return_status OUT NOCOPY VARCHAR2) IS
52 -- ==========================================================================
53 -- DESCRIPTION
54 -- Validates the OA input parameters for other procedures in this package.
55 -- Parameters:
56 -- See description of other OA-compliant procedures having these same
57 -- parameters for a description of the IN parameters.
58 -- x_return_status:
59 -- Returns the value from FND_API.G_RET_STS_ERROR ('E') if there are
60 -- any parameter validation errors.
61 -- HISTORY
62 -- Greg Hall 23-May-2005 Bug# 4301983: copied from
63 -- FEM_DIMENSION_UTIL_PKG.
64 -- ==========================================================================
65
66 e_bad_p_api_ver EXCEPTION;
67 e_bad_p_init_msg_list EXCEPTION;
68 e_bad_p_commit EXCEPTION;
69 e_bad_p_encoded EXCEPTION;
70
71 BEGIN
72
73 x_return_status := c_success;
74
75 CASE p_api_version
76 WHEN c_api_version THEN NULL;
77 ELSE RAISE e_bad_p_api_ver;
78 END CASE;
79
80 CASE p_init_msg_list
81 WHEN c_false THEN NULL;
82 WHEN c_true THEN
83 FND_MSG_PUB.Initialize;
84 ELSE RAISE e_bad_p_init_msg_list;
85 END CASE;
86
87 CASE p_encoded
88 WHEN c_false THEN NULL;
89 WHEN c_true THEN NULL;
90 ELSE RAISE e_bad_p_encoded;
91 END CASE;
92
93 CASE p_commit
94 WHEN c_false THEN NULL;
95 WHEN c_true THEN NULL;
96 ELSE RAISE e_bad_p_commit;
97 END CASE;
98
99 EXCEPTION
100 WHEN e_bad_p_api_ver THEN
101 FEM_ENGINES_PKG.Put_Message(
102 p_app_name => 'FEM',
103 p_msg_name => 'FEM_BAD_P_API_VER_ERR',
104 p_token1 => 'VALUE',
105 p_value1 => p_api_version);
106 x_return_status := c_error;
107
108 WHEN e_bad_p_init_msg_list THEN
109 FEM_ENGINES_PKG.Put_Message(
110 p_app_name => 'FEM',
111 p_msg_name => 'FEM_BAD_P_INIT_MSG_LIST_ERR');
112 x_return_status := c_error;
113
114 WHEN e_bad_p_encoded THEN
115 FEM_ENGINES_PKG.Put_Message(
116 p_app_name => 'FEM',
117 p_msg_name => 'FEM_BAD_P_ENCODED_ERR');
118 x_return_status := c_error;
119
120 WHEN e_bad_p_commit THEN
121 FEM_ENGINES_PKG.Put_Message(
122 p_app_name => 'FEM',
123 p_msg_name => 'FEM_BAD_P_COMMIT_ERR');
124 x_return_status := c_error;
125
126 END Validate_OA_Params;
127
128
129 /***************************************************************************
130 ===========================================================================
131 Public Procedures
132 ===========================================================================
133 ***************************************************************************/
134
135 PROCEDURE Put_Message
136 (p_app_name IN VARCHAR2,
137 p_msg_name IN VARCHAR2,
138 p_token1 IN VARCHAR2 DEFAULT NULL,
139 p_value1 IN VARCHAR2 DEFAULT NULL,
140 p_trans1 IN VARCHAR2 DEFAULT NULL,
141 p_token2 IN VARCHAR2 DEFAULT NULL,
142 p_value2 IN VARCHAR2 DEFAULT NULL,
143 p_trans2 IN VARCHAR2 DEFAULT NULL,
144 p_token3 IN VARCHAR2 DEFAULT NULL,
145 p_value3 IN VARCHAR2 DEFAULT NULL,
146 p_trans3 IN VARCHAR2 DEFAULT NULL,
147 p_token4 IN VARCHAR2 DEFAULT NULL,
148 p_value4 IN VARCHAR2 DEFAULT NULL,
149 p_trans4 IN VARCHAR2 DEFAULT NULL,
150 p_token5 IN VARCHAR2 DEFAULT NULL,
151 p_value5 IN VARCHAR2 DEFAULT NULL,
152 p_trans5 IN VARCHAR2 DEFAULT NULL,
153 p_token6 IN VARCHAR2 DEFAULT NULL,
154 p_value6 IN VARCHAR2 DEFAULT NULL,
155 p_trans6 IN VARCHAR2 DEFAULT NULL,
156 p_token7 IN VARCHAR2 DEFAULT NULL,
157 p_value7 IN VARCHAR2 DEFAULT NULL,
158 p_trans7 IN VARCHAR2 DEFAULT NULL,
159 p_token8 IN VARCHAR2 DEFAULT NULL,
160 p_value8 IN VARCHAR2 DEFAULT NULL,
161 p_trans8 IN VARCHAR2 DEFAULT NULL,
162 p_token9 IN VARCHAR2 DEFAULT NULL,
163 p_value9 IN VARCHAR2 DEFAULT NULL,
164 p_trans9 IN VARCHAR2 DEFAULT NULL)
165 IS
166 v_token VARCHAR2(30);
167 v_value VARCHAR2(4000);
168 v_trans BOOLEAN;
169
170 TYPE msg_array IS VARRAY(27) OF VARCHAR2(4000);
171 tokens_values msg_array;
172
173 BEGIN
174
175 IF (p_msg_name IS NOT NULL) AND
176 (p_app_name IS NOT NULL)
177 THEN
178
179 ---------------------------------
180 -- Get message from dictionary --
181 ---------------------------------
182 fnd_message.set_name(p_app_name,p_msg_name);
183
184 ----------------------------
185 -- Load token/value array --
186 ----------------------------
187 tokens_values := msg_array
188 (p_token1,p_value1,p_trans1,
189 p_token2,p_value2,p_trans2,
190 p_token3,p_value3,p_trans3,
191 p_token4,p_value4,p_trans4,
192 p_token5,p_value5,p_trans5,
193 p_token6,p_value6,p_trans6,
194 p_token7,p_value7,p_trans7,
195 p_token8,p_value8,p_trans8,
196 p_token9,p_value9,p_trans9);
197
198 ----------------------------------
199 -- Substitute values for tokens --
200 ----------------------------------
201 FOR i IN 1..27 LOOP
202 IF (MOD(i,3) = 1)
203 THEN
204 v_token := tokens_values(i);
205 IF (v_token IS NOT NULL)
206 THEN
207 v_value := tokens_values(i+1);
208 IF (tokens_values(i+2) = 'Y')
209 THEN
210 v_trans := TRUE;
211 ELSE
212 v_trans := FALSE;
213 END IF;
214 fnd_message.set_token(v_token,v_value,v_trans);
215 ELSE
216 EXIT;
217 END IF;
218 END IF;
219 END LOOP;
220
221 IF (v_msg_mode = 0)
222 THEN
223 fnd_msg_pub.add;
224 END IF;
225 v_msg_mode := 0;
226
227 END IF;
228
229 END Put_Message;
230
231
232 PROCEDURE Tech_Message
233 (p_severity IN NUMBER,
234 p_module IN VARCHAR2,
235 p_msg_text IN VARCHAR2 DEFAULT NULL,
236 p_app_name IN VARCHAR2 DEFAULT NULL,
237 p_msg_name IN VARCHAR2 DEFAULT NULL,
238 p_token1 IN VARCHAR2 DEFAULT NULL,
239 p_value1 IN VARCHAR2 DEFAULT NULL,
240 p_trans1 IN VARCHAR2 DEFAULT NULL,
241 p_token2 IN VARCHAR2 DEFAULT NULL,
242 p_value2 IN VARCHAR2 DEFAULT NULL,
243 p_trans2 IN VARCHAR2 DEFAULT NULL,
244 p_token3 IN VARCHAR2 DEFAULT NULL,
245 p_value3 IN VARCHAR2 DEFAULT NULL,
246 p_trans3 IN VARCHAR2 DEFAULT NULL,
247 p_token4 IN VARCHAR2 DEFAULT NULL,
248 p_value4 IN VARCHAR2 DEFAULT NULL,
249 p_trans4 IN VARCHAR2 DEFAULT NULL,
250 p_token5 IN VARCHAR2 DEFAULT NULL,
251 p_value5 IN VARCHAR2 DEFAULT NULL,
252 p_trans5 IN VARCHAR2 DEFAULT NULL,
253 p_token6 IN VARCHAR2 DEFAULT NULL,
254 p_value6 IN VARCHAR2 DEFAULT NULL,
255 p_trans6 IN VARCHAR2 DEFAULT NULL,
256 p_token7 IN VARCHAR2 DEFAULT NULL,
257 p_value7 IN VARCHAR2 DEFAULT NULL,
258 p_trans7 IN VARCHAR2 DEFAULT NULL,
259 p_token8 IN VARCHAR2 DEFAULT NULL,
260 p_value8 IN VARCHAR2 DEFAULT NULL,
261 p_trans8 IN VARCHAR2 DEFAULT NULL,
262 p_token9 IN VARCHAR2 DEFAULT NULL,
263 p_value9 IN VARCHAR2 DEFAULT NULL,
264 p_trans9 IN VARCHAR2 DEFAULT NULL)
265
266 IS
267 v_msg_text VARCHAR2(4000);
268 BEGIN
269
270 IF (p_severity IS NOT NULL) AND
271 (p_module IS NOT NULL)
272 THEN
273 -- Only log the message if the caller debug level is greater than
274 -- the FND profile debug level set by the user.
275 IF (p_severity >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
276 IF (p_app_name IS NOT NULL) AND
277 (p_msg_name IS NOT NULL)
278 THEN
279 v_msg_mode := 1;
280 Put_Message
281 (p_app_name,p_msg_name,
282 p_token1,p_value1,p_trans1,
283 p_token2,p_value2,p_trans2,
284 p_token3,p_value3,p_trans3,
285 p_token4,p_value4,p_trans4,
286 p_token5,p_value5,p_trans5,
287 p_token6,p_value6,p_trans6,
288 p_token7,p_value7,p_trans7,
289 p_token8,p_value8,p_trans8,
290 p_token9,p_value9,p_trans9);
291
292 v_msg_text := SUBSTR(FND_Message.Get,1,4000);
293 ELSE
294 v_msg_text := SUBSTR(p_msg_text,1,4000);
295 END IF;
296
297 fnd_log.string(p_severity,p_module,NVL(v_msg_text,'NULL'));
298 END IF;
299
300 END IF;
301
302 END Tech_Message;
303
304
305 PROCEDURE User_Message
306 (p_msg_text IN VARCHAR2 DEFAULT NULL,
307 p_app_name IN VARCHAR2 DEFAULT NULL,
308 p_msg_name IN VARCHAR2 DEFAULT NULL,
309 p_token1 IN VARCHAR2 DEFAULT NULL,
310 p_value1 IN VARCHAR2 DEFAULT NULL,
311 p_trans1 IN VARCHAR2 DEFAULT NULL,
312 p_token2 IN VARCHAR2 DEFAULT NULL,
313 p_value2 IN VARCHAR2 DEFAULT NULL,
314 p_trans2 IN VARCHAR2 DEFAULT NULL,
315 p_token3 IN VARCHAR2 DEFAULT NULL,
316 p_value3 IN VARCHAR2 DEFAULT NULL,
317 p_trans3 IN VARCHAR2 DEFAULT NULL,
318 p_token4 IN VARCHAR2 DEFAULT NULL,
319 p_value4 IN VARCHAR2 DEFAULT NULL,
320 p_trans4 IN VARCHAR2 DEFAULT NULL,
321 p_token5 IN VARCHAR2 DEFAULT NULL,
322 p_value5 IN VARCHAR2 DEFAULT NULL,
323 p_trans5 IN VARCHAR2 DEFAULT NULL,
324 p_token6 IN VARCHAR2 DEFAULT NULL,
325 p_value6 IN VARCHAR2 DEFAULT NULL,
326 p_trans6 IN VARCHAR2 DEFAULT NULL,
327 p_token7 IN VARCHAR2 DEFAULT NULL,
328 p_value7 IN VARCHAR2 DEFAULT NULL,
329 p_trans7 IN VARCHAR2 DEFAULT NULL,
330 p_token8 IN VARCHAR2 DEFAULT NULL,
331 p_value8 IN VARCHAR2 DEFAULT NULL,
332 p_trans8 IN VARCHAR2 DEFAULT NULL,
333 p_token9 IN VARCHAR2 DEFAULT NULL,
334 p_value9 IN VARCHAR2 DEFAULT NULL,
335 p_trans9 IN VARCHAR2 DEFAULT NULL)
336
337 IS
338 v_msg_text VARCHAR2(4000);
339 BEGIN
340
341 IF (p_app_name IS NOT NULL) AND
342 (p_msg_name IS NOT NULL)
343 THEN
344 v_msg_mode := 1;
345 Put_Message
346 (p_app_name,p_msg_name,
347 p_token1,p_value1,p_trans1,
348 p_token2,p_value2,p_trans2,
349 p_token3,p_value3,p_trans3,
350 p_token4,p_value4,p_trans4,
351 p_token5,p_value5,p_trans5,
352 p_token6,p_value6,p_trans6,
353 p_token7,p_value7,p_trans7,
354 p_token8,p_value8,p_trans8,
355 p_token9,p_value9,p_trans9);
356
357 v_msg_text := SUBSTR(FND_Message.Get,1,4000);
358 ELSE
359 v_msg_text := SUBSTR(p_msg_text,1,4000);
360 END IF;
361
362 fnd_file.put_line(fnd_file.log,
363 NVL(v_msg_text,'No message found'));
364
365 END User_Message;
366
367 /***************************************************************************/
368 PROCEDURE Get_PB_Param_Value
369 (p_api_version IN NUMBER DEFAULT c_api_version,
370 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
371 p_commit IN VARCHAR2 DEFAULT c_false,
372 p_encoded IN VARCHAR2 DEFAULT c_true,
373 x_return_status OUT NOCOPY VARCHAR2,
374 x_msg_count OUT NOCOPY NUMBER,
375 x_msg_data OUT NOCOPY VARCHAR2,
376 p_parameter_name IN VARCHAR2,
377 p_object_type_code IN VARCHAR2,
378 p_step_name IN VARCHAR2,
379 p_object_id IN NUMBER,
380 x_pb_param_data_type OUT NOCOPY VARCHAR2,
381 x_pb_param_value OUT NOCOPY VARCHAR2) IS
382 -- ===========================================================================
383 -- DESCRIPTION
384 -- Returns the process behavior parameter value for the specified
385 -- parameter for the specified object type, step name, and object ID.
386 -- Selection is made from the most specific assignment level to the most
387 -- general one until a matching assignment level is found. The assignment
388 -- level precedence is as follows, from most specific to most general
389 -- (consistent with how the multiprocessing parameter values are selected):
390 -- 1. Match on
391 -- OBJECT_TYPE_CODE = p_object_type_code AND
392 -- STEP_NAME = p_step_name AND
393 -- OBJECT_ID = p_object_id.
394 -- 2. Match on
395 -- OBJECT_TYPE_CODE = p_object_type_code AND
396 -- STEP_NAME = 'ALL' AND
397 -- OBJECT_ID = p_object_id.
398 -- 3. Match on
399 -- OBJECT_TYPE_CODE = p_object_type_code AND
400 -- STEP_NAME = p_step_name AND
401 -- OBJECT_ID IS NULL.
402 -- 4. Match on
403 -- OBJECT_TYPE_CODE = p_object_type_code AND
404 -- STEP_NAME = 'ALL' AND
405 -- OBJECT_ID IS NULL.
406 -- Note that level 3 and level 4 are the same for engines that don't use
407 -- distinct steps, as they will always pass in 'ALL' for p_step_name.
408 -- Parameters:
409 -- p_api_version:
410 -- Optional OA-compliance parameter.
411 -- Default is 1.0 (and this is currently the only acceptable value).
412 -- p_init_msg_list
413 -- Optional OA-compliance flag parameter.
414 -- Tells whether or not to initialize the FND_MSG_PUB message stack by
415 -- calling FND_MSG_PUB.Initialize.
416 -- Valid values are 'T' and 'F'; default is 'F'.
417 -- p_commit:
418 -- Optional OA-compliance flag parameter. Valid values are 'T' and 'F'.
419 -- Note that this procedure is read-only, there is nothing to commit,
420 -- so this parameter is not used.
421 -- p_encoded:
422 -- Optional OA-compliance flag parameter.
423 -- Passed to FND_MSG_PUB.Count_and_Get to determine the format of any
424 -- message passed back in x_msg_data.
425 -- Valid values are 'T' and 'F'; default is 'T'.
426 -- x_return_status:
427 -- OA-compliance OUT parameter.
428 -- Returns 'S' for success if an assignment level is found;
429 -- Returns 'E' for error if no assignment level is found.
430 -- x_msg_count:
431 -- OA-compliance OUT parameter. Tells how many messages are waiting
432 -- on the FND_MSG_PUB message stack. If x_msg_count = 1, the message
433 -- has already been fetched from the stack and is found in x_msg_data.
434 -- x_msg_data:
435 -- OA-compliance OUT parameter. If x_msg_count = 1, the message
436 -- has already been fetched from the stack and is found in x_msg_data.
437 -- p_parameter_name:
438 -- The PARAMETER_NAME of the parameter value to be retrieved.
439 -- p_object_type_code:
440 -- The OBJECT_TYPE_CODE of the object being processed, and for which
441 -- the Process Behavior parameter value is to be retrieved.
442 -- p_step_name:
443 -- The STEP_NAME identifying the engine processing step for which the
444 -- Process Behavior parameter is to be retrieved. If there is no
445 -- STEP_NAME-level assignment, then the value for the 'ALL' step
446 -- assignment level is returned.
447 -- p_object_id:
448 -- The FEM OBJECT_ID identifying the executable rule for which the
449 -- Process Behavior parameter is to be retrieved. If there is no
450 -- Object ID-level assignment, then the value for a more general
451 -- assignment level is returned.
452 -- x_pb_param_data_type:
453 -- Returns 'NUMBER', 'CHAR', or 'DATE' according to the data type of
454 -- the parameter for the applicable assignment level. Returns NULL
455 -- if no assignment is found for the given parameter and object type.
456 -- x_pb_param_value:
457 -- Returns the alphanumeric value of the process behavior parameter from
458 -- the applicable assignment level. Returns NULL if no assignment levels
459 -- are found for the specified parameter and object type. Number and Date
460 -- parameters are returned as alphanumeric, and will need to be converted
461 -- by the calling program.
462 -- HISTORY
463 -- Greg Hall 21-Jun-2005 Bug# 4445212: created.
464 -- ===========================================================================
465
466 v_param_numeric_value NUMBER;
467 v_param_char_value VARCHAR2(255);
468 v_param_date_value DATE;
469
470 v_param_data_type VARCHAR2(6);
471 v_param_default_value VARCHAR2(255);
472
473 BEGIN
474
475 -- Validate OA parameters
476
477 x_return_status := c_success;
478
479 Validate_OA_Params (
480 p_api_version => p_api_version,
481 p_init_msg_list => p_init_msg_list,
482 p_commit => p_commit,
483 p_encoded => p_encoded,
484 x_return_status => x_return_status);
485
486 IF (x_return_status <> c_success)
487 THEN
488 FND_MSG_PUB.Count_and_Get(
489 p_encoded => c_false,
490 p_count => x_msg_count,
491 p_data => x_msg_data);
492 RETURN;
493 END IF;
494
495 BEGIN
496
497 -- Assignment Level 1 (most specific):
498
499 SELECT parameter_numeric_value,
500 parameter_char_value,
501 parameter_date_value
502 INTO v_param_numeric_value,
503 v_param_char_value,
504 v_param_date_value
505 FROM fem_pb_parameters
506 WHERE parameter_name = p_parameter_name
507 AND object_type_code = p_object_type_code
508 AND step_name = p_step_name
509 AND object_id = p_object_id;
510
511 SELECT param_data_type_code, param_default_value
512 INTO v_param_data_type, v_param_default_value
513 FROM fem_pb_param_assignmt
514 WHERE parameter_name = p_parameter_name
515 AND object_type_code = p_object_type_code
516 AND step_name = p_step_name
517 AND object_id_level_flag = 'Y';
518
519 FEM_ENGINES_PKG.TECH_MESSAGE
520 (p_severity => c_log_level_1,
521 p_module => 'fem.plsql.fem_database_util_pkg.get_pb_param_value',
522 p_msg_text => 'Value found for PB parameter ' || p_parameter_name ||
523 ' at assignment level 1: Object Type, specific Step, specific Object ID');
524 EXCEPTION
525 WHEN no_data_found THEN
526
527 BEGIN
528
529 -- Assignment Level 2
530
531 SELECT parameter_numeric_value,
532 parameter_char_value,
533 parameter_date_value
534 INTO v_param_numeric_value,
535 v_param_char_value,
536 v_param_date_value
537 FROM fem_pb_parameters
538 WHERE parameter_name = p_parameter_name
539 AND object_type_code = p_object_type_code
540 AND step_name = 'ALL'
541 AND object_id = p_object_id;
542
543 SELECT param_data_type_code, param_default_value
544 INTO v_param_data_type, v_param_default_value
545 FROM fem_pb_param_assignmt
546 WHERE parameter_name = p_parameter_name
547 AND object_type_code = p_object_type_code
548 AND step_name = 'ALL'
549 AND object_id_level_flag = 'Y';
550
551 FEM_ENGINES_PKG.TECH_MESSAGE
552 (p_severity => c_log_level_1,
553 p_module => 'fem.plsql.fem_database_util_pkg.get_pb_param_value',
554 p_msg_text => 'Value found for PB parameter ' || p_parameter_name ||
555 ' at assignment level 2: Object Type, Step=ALL, specific Object ID');
556 EXCEPTION
557 WHEN no_data_found THEN
558
559 BEGIN
560
561 -- Assignment Level 3
562
563 SELECT parameter_numeric_value,
564 parameter_char_value,
565 parameter_date_value
566 INTO v_param_numeric_value,
567 v_param_char_value,
568 v_param_date_value
569 FROM fem_pb_parameters
570 WHERE parameter_name = p_parameter_name
571 AND object_type_code = p_object_type_code
572 AND step_name = p_step_name
573 AND object_id IS NULL;
574
575 SELECT param_data_type_code, param_default_value
576 INTO v_param_data_type, v_param_default_value
577 FROM fem_pb_param_assignmt
578 WHERE parameter_name = p_parameter_name
579 AND object_type_code = p_object_type_code
580 AND step_name = p_step_name
581 AND object_id_level_flag = 'N';
582
583 FEM_ENGINES_PKG.TECH_MESSAGE
584 (p_severity => c_log_level_1,
585 p_module => 'fem.plsql.fem_database_util_pkg.get_pb_param_value',
586 p_msg_text => 'Value found for PB parameter ' || p_parameter_name ||
587 ' at assignment level 3: Object Type, specific Step, Object ID IS NULL');
588 EXCEPTION
589 WHEN no_data_found THEN
590
591 BEGIN
592
593 -- Assignment Level 4 (most general)
594
595 SELECT parameter_numeric_value,
596 parameter_char_value,
597 parameter_date_value
598 INTO v_param_numeric_value,
599 v_param_char_value,
600 v_param_date_value
601 FROM fem_pb_parameters
602 WHERE parameter_name = p_parameter_name
603 AND object_type_code = p_object_type_code
604 AND step_name = 'ALL'
605 AND object_id IS NULL;
606
607 SELECT param_data_type_code, param_default_value
608 INTO v_param_data_type, v_param_default_value
609 FROM fem_pb_param_assignmt
610 WHERE parameter_name = p_parameter_name
611 AND object_type_code = p_object_type_code
612 AND step_name = 'ALL'
613 AND object_id_level_flag = 'N';
614
615 FEM_ENGINES_PKG.TECH_MESSAGE
616 (p_severity => c_log_level_1,
617 p_module => 'fem.plsql.fem_database_util_pkg.get_pb_param_value',
618 p_msg_text => 'Value found for PB parameter ' || p_parameter_name ||
619 ' at assignment level 4: Object Type, Step=ALL, Object ID IS NULL');
620 EXCEPTION
621 WHEN no_data_found THEN
622
623 -- Put user error message on the FND_MSG_PUB stack and to the debug log:
624 -- "No parameter assignment found. Parameter Name: PARAMETER_NAME. Object Type: OBJECT_TYPE"
625 FEM_ENGINES_PKG.PUT_MESSAGE
626 (p_app_name => 'FEM',
627 p_msg_name => 'FEM_PB_NO_ASSIGNMENTS_ERROR',
628 p_token1 => 'PARAMETER_NAME',
629 p_value1 => p_parameter_name,
630 p_token2 => 'OBJECT_TYPE',
631 p_value2 => p_object_type_code);
632
633 FEM_ENGINES_PKG.TECH_MESSAGE
634 (p_severity => c_log_level_5,
635 p_module => 'fem.plsql.fem_database_util_pkg.get_pb_param_value',
636 p_app_name => 'FEM',
637 p_msg_name => 'FEM_PB_NO_ASSIGNMENTS_ERROR',
638 p_token1 => 'PARAMETER_NAME',
639 p_value1 => p_parameter_name,
640 p_token2 => 'OBJECT_TYPE',
641 p_value2 => p_object_type_code);
642
643 FND_MSG_PUB.Count_and_Get(
644 p_encoded => p_encoded,
645 p_count => x_msg_count,
646 p_data => x_msg_data);
647
648 x_return_status := c_error;
649
650 RETURN;
651 END;
652 END;
653 END;
654 END;
655
656 IF v_param_data_type = 'NUMBER' THEN
657
658 x_pb_param_value := NVL(TO_CHAR(v_param_numeric_value), v_param_default_value);
659
660 ELSIF v_param_data_type = 'CHAR' THEN
661
662 x_pb_param_value := NVL(v_param_char_value, v_param_default_value);
663
664 ELSE
665 -- v_param_data_type = 'DATE'
666
667 x_pb_param_value := NVL(TO_CHAR(v_param_date_value), v_param_default_value);
668
669 END IF;
670
671 FEM_ENGINES_PKG.TECH_MESSAGE
672 (p_severity => c_log_level_1,
673 p_module => 'fem.plsql.fem_database_util_pkg.get_pb_param_value',
674 p_msg_text => 'Returning ' || v_param_data_type || ' parameter value: ' || x_pb_param_value);
675
676 x_pb_param_data_type := v_param_data_type;
677
678 FND_MSG_PUB.Count_and_Get(
679 p_encoded => p_encoded,
680 p_count => x_msg_count,
681 p_data => x_msg_data);
682
683 END Get_PB_Param_Value;
684
685
686 END FEM_Engines_Pkg;