[Home] [Help]
PACKAGE BODY: APPS.FEM_HELPER_RULE_UTIL_PKG
Source
1 PACKAGE BODY FEM_HELPER_RULE_UTIL_PKG AS
2 --$Header: fem_helper_rule_utl.plb 120.6 2008/02/20 06:58:04 jcliving noship $
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 +==========================================================================+
7 | FILENAME
8 |
9 | fem_helper_rule_utl.plb
10 |
11 | NAME FEM_HELPER_RULE_UTIL_PKG
12 |
13 | DESCRIPTION
14 |
15 | Package Body for the FEM Helper Rule Utility Package
16 |
17 | HISTORY
18 |
19 | 19-JAN-06 RFlippo initial creation
20 | 10-MAY-07 RFlippo added p_commit logic
21 | 22-MAY-07 RFlippo get_helper needs to find helper for the Object_ID
22 | and register it if it exists
23 | 24-MAY-07 RFlippo fixed issue where get_helper_rule not working
24 | for cr_new_ver_from_defaults
25 | 05-JUL-07 Rflippo remove savepoint on get_helper_rule because
26 | needs to be callable from functions (i.e.
27 | get_rule_dirty_flag)
28 +=========================================================================*/
29
30 -----------------------
31 -- Package Constants --
32 -----------------------
33 c_resp_app_id CONSTANT NUMBER := FND_GLOBAL.RESP_APPL_ID;
34
35 c_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
36 c_login_id NUMBER := FND_GLOBAL.Login_Id;
37
38 c_module_pkg CONSTANT VARCHAR2(80) := 'fem.plsql.fem_helper_rule_util_pkg';
39 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FEM_HELPER_RULE_UTIL_PKG';
40
41 f_set_status BOOLEAN;
42
43 c_log_level_1 CONSTANT NUMBER := fnd_log.level_statement;
44 c_log_level_2 CONSTANT NUMBER := fnd_log.level_procedure;
45 c_log_level_3 CONSTANT NUMBER := fnd_log.level_event;
46 c_log_level_4 CONSTANT NUMBER := fnd_log.level_exception;
47 c_log_level_5 CONSTANT NUMBER := fnd_log.level_error;
48 c_log_level_6 CONSTANT NUMBER := fnd_log.level_unexpected;
49
50 -----------------------
51 -- Package Variables --
52 -----------------------
53 v_module_log VARCHAR2(255);
54
55
56 v_token_value VARCHAR2(150);
57 v_token_trans VARCHAR2(1);
58
59 v_msg_text VARCHAR2(4000);
60
61 gv_prg_msg VARCHAR2(2000);
62 gv_callstack VARCHAR2(2000);
63
64
65 -----------------------
66 -- Private Procedures --
67 -----------------------
68 PROCEDURE Validate_OA_Params (
69 p_api_version IN NUMBER,
70 p_init_msg_list IN VARCHAR2,
71 p_commit IN VARCHAR2,
72 p_encoded IN VARCHAR2,
73 x_return_status OUT NOCOPY VARCHAR2
74 );
75
76
77
78 /*************************************************************************
79
80 register_helper_rule
81
82 PURPOSE: Registers the association between a true rule and a helper rule.
83
84 *************************************************************************/
85
86 PROCEDURE register_helper_rule (
87 p_rule_obj_def_id IN NUMBER,
88 p_helper_obj_def_id IN NUMBER,
89 p_helper_object_type_code IN VARCHAR2,
90 p_api_version IN NUMBER DEFAULT c_api_version,
91 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
92 p_commit IN VARCHAR2 DEFAULT c_false,
93 p_encoded IN VARCHAR2 DEFAULT c_true,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2)
97 IS
98
99 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
100 'fem.plsql.fem_helper_rule_util_pkg.register_helper_rule';
101 C_API_NAME CONSTANT VARCHAR2(30) := 'Register_helper_rule';
102
103 v_rule_object_id NUMBER;
104 v_helper_object_id NUMBER;
105 v_object_type_code VARCHAR2(30);
106
107 e_unexp EXCEPTION;
108
109 BEGIN
110
111 -- Standard Start of API savepoint
112 SAVEPOINT register_helper_rule_pub;
113
114
115 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
116 FEM_ENGINES_PKG.TECH_MESSAGE(
117 p_severity => FND_LOG.level_procedure,
118 p_module => C_MODULE,
119 p_msg_text => 'Begin Procedure');
120 END IF;
121
122
123 -- Initialize return status to unexpected error
124 x_return_status := c_unexp;
125
126 -- Check for call compatibility.
127 IF NOT FND_API.Compatible_API_Call (c_api_version,
128 p_api_version,
129 C_API_NAME,
130 G_PKG_NAME)
131 THEN
132 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
133 FEM_ENGINES_PKG.TECH_MESSAGE(
134 p_severity => FND_LOG.level_statement,
135 p_module => C_MODULE,
136 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
137 ||'passed in version ('||p_api_version||')');
138 END IF;
139 RAISE e_unexp;
140 END IF;
141
142
143 Validate_OA_Params (
144 p_api_version => p_api_version,
145 p_init_msg_list => p_init_msg_list,
146 p_commit => p_commit,
147 p_encoded => p_encoded,
148 x_return_status => x_return_status);
149
150 IF (x_return_status <> c_success) THEN
151 RAISE e_unexp;
152 END IF;
153
154
155 BEGIN
156
157 SELECT object_id
158 INTO v_rule_object_id
159 FROM fem_object_definition_b
160 WHERE object_definition_id = p_rule_obj_def_id;
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
165 FEM_ENGINES_PKG.TECH_MESSAGE(
166 p_severity => FND_LOG.level_statement,
167 p_module => C_MODULE,
168 p_msg_text => 'p_rule_obj_def_id does not exist');
169 END IF;
170 RAISE e_unexp;
171
172 END;
173
174 BEGIN
175
176 SELECT object_id
177 INTO v_helper_object_id
178 FROM fem_object_definition_b
179 WHERE object_definition_id = p_helper_obj_def_id;
180
181 EXCEPTION
182 WHEN NO_DATA_FOUND THEN
183 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
184 FEM_ENGINES_PKG.TECH_MESSAGE(
185 p_severity => FND_LOG.level_statement,
186 p_module => C_MODULE,
187 p_msg_text => 'p_helper_obj_def_id does not exist');
188 END IF;
189 RAISE e_unexp;
190
191 END;
192
193 /* Verify that the object_type_code is valid */
194 BEGIN
195
196 SELECT object_type_code
197 INTO v_object_type_code
198 FROM fem_object_types_vl
199 WHERE object_type_code = p_helper_object_type_code;
200
201 EXCEPTION
202 WHEN NO_DATA_FOUND THEN
203 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
204 FEM_ENGINES_PKG.TECH_MESSAGE(
205 p_severity => FND_LOG.level_statement,
206 p_module => C_MODULE,
207 p_msg_text => 'p_object_type_code does not exist');
208 END IF;
209 RAISE e_unexp;
210
211
212 END;
213
214
215 insert into fem_objdef_helper_rules (OBJECT_DEFINITION_ID,
216 HELPER_OBJECT_TYPE_CODE,
217 OBJECT_ID,
218 HELPER_OBJ_DEF_ID,
219 HELPER_OBJECT_ID,
220 CREATION_DATE,
221 CREATED_BY,
222 LAST_UPDATED_BY,
223 LAST_UPDATE_DATE,
224 LAST_UPDATE_LOGIN
225 )
226 values (p_rule_obj_def_id
227 ,p_helper_object_type_code
228 ,v_rule_object_id
229 ,p_helper_obj_def_id
230 ,v_helper_object_id
231 ,sysdate
232 ,c_user_id
233 ,c_user_id
234 ,sysdate
235 ,c_login_id);
236
237
238 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
239 FEM_ENGINES_PKG.TECH_MESSAGE(
240 p_severity => FND_LOG.level_procedure,
241 p_module => C_MODULE,
242 p_msg_text => 'End Procedure');
243 END IF;
244
245
246 IF FND_API.To_Boolean( p_commit ) THEN
247 COMMIT WORK;
248 END IF;
249
250
251 EXCEPTION
252
253 WHEN e_unexp THEN
254 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
255 FEM_ENGINES_PKG.TECH_MESSAGE(
256 p_severity => FND_LOG.level_statement,
257 p_module => C_MODULE,
258 p_msg_text => 'Unexpected error.');
259 END IF;
260 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
261 p_count => x_msg_count,
262 p_data => x_msg_data);
263 ROLLBACK TO register_helper_rule_pub;
264 x_return_status := c_unexp;
265
266
267 WHEN others THEN
268 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
269 FEM_ENGINES_PKG.TECH_MESSAGE(
270 p_severity => FND_LOG.level_statement,
271 p_module => C_MODULE,
272 p_msg_text => 'Unexpected error.');
273 FEM_ENGINES_PKG.TECH_MESSAGE(
274 p_severity => FND_LOG.level_statement,
275 p_module => C_MODULE,
276 p_msg_text => SQLERRM);
277 END IF;
278 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
279 p_count => x_msg_count,
280 p_data => x_msg_data);
281 ROLLBACK TO register_helper_rule_pub;
282 x_return_status := c_unexp;
283
284 END register_helper_rule;
285
286
287
288 /*************************************************************************
289
290 get_helper_rule
291
292 PURPOSE: Identifies the helper rule of a specified helper object type
293 for a given true rule.
294
295 *************************************************************************/
296
297 PROCEDURE get_helper_rule (
298 p_rule_obj_def_id IN NUMBER,
299 p_helper_object_type_code IN VARCHAR2,
300 p_api_version IN NUMBER DEFAULT c_api_version,
301 p_init_msg_list IN VARCHAR2 DEFAULT c_false,
302 p_commit IN VARCHAR2 DEFAULT c_false,
303 p_encoded IN VARCHAR2 DEFAULT c_true,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2,
307 x_helper_obj_def_id OUT NOCOPY NUMBER )
308 IS
309
310 C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
311 'fem.plsql.fem_helper_rule_util_pkg.get_helper_rule';
312 C_API_NAME CONSTANT VARCHAR2(30) := 'get_helper_rule';
313
314 v_rule_object_id NUMBER;
315 v_helper_object_id NUMBER;
316 v_object_type_code VARCHAR2(30);
317 v_msg_count NUMBER;
318 v_msg_data VARCHAR2(4000);
319 v_return_status VARCHAR2(4000);
320
321 e_unexp EXCEPTION;
322
323 BEGIN
324
325 /* comment out so can be called from function
326 -- Standard Start of API savepoint
327 SAVEPOINT get_helper_rule_pub; */
328
329
330 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
331 FEM_ENGINES_PKG.TECH_MESSAGE(
332 p_severity => FND_LOG.level_procedure,
333 p_module => C_MODULE,
334 p_msg_text => 'Begin Procedure');
335 END IF;
336
337
338 -- Initialize return status to unexpected error
339 x_return_status := c_unexp;
340
341 -- Check for call compatibility.
342 IF NOT FND_API.Compatible_API_Call (c_api_version,
343 p_api_version,
344 C_API_NAME,
345 G_PKG_NAME)
346 THEN
347 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
348 FEM_ENGINES_PKG.TECH_MESSAGE(
349 p_severity => FND_LOG.level_statement,
350 p_module => C_MODULE,
351 p_msg_text => 'API Version ('||C_API_VERSION||') not compatible with '
352 ||'passed in version ('||p_api_version||')');
353 END IF;
354 RAISE e_unexp;
355 END IF;
356
357
358 Validate_OA_Params (
359 p_api_version => p_api_version,
360 p_init_msg_list => p_init_msg_list,
361 p_commit => p_commit,
362 p_encoded => p_encoded,
363 x_return_status => x_return_status);
364
365 IF (x_return_status <> c_success) THEN
366 RAISE e_unexp;
367 END IF;
368
369
370 /* Verify that the object def id exists*/
371 BEGIN
372
373 SELECT object_id
374 INTO v_rule_object_id
375 FROM fem_object_definition_b
376 WHERE object_definition_id = p_rule_obj_def_id;
377
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
381 FEM_ENGINES_PKG.TECH_MESSAGE(
382 p_severity => FND_LOG.level_statement,
383 p_module => C_MODULE,
384 p_msg_text => 'p_rule_obj_def_id ='||p_rule_obj_def_id||' does not exist');
385 END IF;
386 RAISE e_unexp;
387
388 END;
389
390
391 /* Verify that the object_type_code is valid */
392 BEGIN
393
394 SELECT object_type_code
395 INTO v_object_type_code
396 FROM fem_object_types_vl
397 WHERE object_type_code = p_helper_object_type_code;
398
399 EXCEPTION
400 WHEN NO_DATA_FOUND THEN
401 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
402 FEM_ENGINES_PKG.TECH_MESSAGE(
403 p_severity => FND_LOG.level_statement,
404 p_module => C_MODULE,
405 p_msg_text => 'p_object_type_code ='||p_helper_object_type_code||'does not exist');
406 END IF;
407 RAISE e_unexp;
408
409
410 END;
411
412
413 BEGIN
414
415 SELECT helper_obj_def_id
416 INTO x_helper_obj_def_id
417 FROM fem_objdef_helper_rules
418 WHERE object_definition_id = p_rule_obj_def_id
419 AND helper_object_type_code = p_helper_object_type_code;
420
421 EXCEPTION
422 WHEN NO_DATA_FOUND THEN
423 /* Does helper exist for this Object ID */
424 BEGIN
425 SELECT min(helper_obj_def_id)
426 INTO x_helper_obj_def_id
427 FROM fem_objdef_helper_rules H, fem_object_definition_b D
428 WHERE D.object_id = H.object_id
429 AND D.object_definition_id = p_rule_obj_def_id
430 AND helper_object_type_code = p_helper_object_type_code;
431
432 IF x_helper_obj_def_id IS NULL THEN
433 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
434 FEM_ENGINES_PKG.TECH_MESSAGE(
435 p_severity => FND_LOG.level_statement,
436 p_module => C_MODULE,
437 p_msg_text => ' no helper rule for rule_obj_def_id ='||p_rule_obj_def_id||' and object_type_code = '||p_helper_object_type_code);
438 END IF;
439 RAISE e_unexp;
440
441 END IF;
442
443 /* Register the helper for this mapping rule object def */
444 register_helper_rule (
445 p_rule_obj_def_id => p_rule_obj_def_id,
446 p_helper_obj_def_id => x_helper_obj_def_id,
447 p_helper_object_type_code => p_helper_object_type_code,
448 x_return_status => v_return_status,
449 x_msg_count => v_msg_count,
450 x_msg_data => v_msg_data);
451
452
453 END;
454
455 END;
456
457
458 IF FND_LOG.level_procedure >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
459 FEM_ENGINES_PKG.TECH_MESSAGE(
460 p_severity => FND_LOG.level_procedure,
461 p_module => C_MODULE,
462 p_msg_text => 'End Procedure');
463 END IF;
464
465
466
467
468 EXCEPTION
469
470 WHEN e_unexp THEN
471 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
472 FEM_ENGINES_PKG.TECH_MESSAGE(
473 p_severity => FND_LOG.level_statement,
474 p_module => C_MODULE,
475 p_msg_text => 'Unexpected error.');
476 END IF;
477 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
478 p_count => x_msg_count,
479 p_data => x_msg_data);
480 --ROLLBACK TO get_helper_rule_pub;
481 x_return_status := c_unexp;
482 x_helper_obj_def_id := -1;
483
484
485 WHEN others THEN
486 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
487 FEM_ENGINES_PKG.TECH_MESSAGE(
488 p_severity => FND_LOG.level_statement,
489 p_module => C_MODULE,
490 p_msg_text => 'Unexpected error.');
491 FEM_ENGINES_PKG.TECH_MESSAGE(
492 p_severity => FND_LOG.level_statement,
493 p_module => C_MODULE,
494 p_msg_text => SQLERRM);
495 END IF;
496 FND_MSG_PUB.Count_And_Get(p_encoded => p_encoded,
497 p_count => x_msg_count,
498 p_data => x_msg_data);
499 --ROLLBACK TO get_helper_rule_pub;
500 x_return_status := c_unexp;
501 x_helper_obj_def_id := -1;
502
503 END get_helper_rule;
504
505
506
507
508
509 /*************************************************************************
510
511 OA Exception Handler
512
513 *************************************************************************/
514
515 PROCEDURE Validate_OA_Params (
516 p_api_version IN NUMBER,
517 p_init_msg_list IN VARCHAR2,
518 p_commit IN VARCHAR2,
519 p_encoded IN VARCHAR2,
520 x_return_status OUT NOCOPY VARCHAR2
521 )
522 IS
523 e_bad_p_api_ver EXCEPTION;
524 e_bad_p_init_msg_list EXCEPTION;
525 e_bad_p_commit EXCEPTION;
526 e_bad_p_encoded EXCEPTION;
527 BEGIN
528
529 x_return_status := c_success;
530
531 CASE p_api_version
532 WHEN c_api_version THEN NULL;
533 ELSE RAISE e_bad_p_api_ver;
534 END CASE;
535
536 CASE p_init_msg_list
537 WHEN c_false THEN NULL;
538 WHEN c_true THEN
539 FND_MSG_PUB.Initialize;
540 ELSE RAISE e_bad_p_init_msg_list;
541 END CASE;
542
543 CASE p_encoded
544 WHEN c_false THEN NULL;
545 WHEN c_true THEN NULL;
546 ELSE RAISE e_bad_p_encoded;
547 END CASE;
548
549 CASE p_commit
550 WHEN c_false THEN NULL;
551 WHEN c_true THEN NULL;
552 ELSE RAISE e_bad_p_commit;
553 END CASE;
554
555 EXCEPTION
556 WHEN e_bad_p_api_ver THEN
557 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
558 FEM_ENGINES_PKG.Put_Message(
559 p_app_name => 'FEM',
560 p_msg_name => 'FEM_BAD_P_API_VER_ERR',
561 p_token1 => 'VALUE',
562 p_value1 => p_api_version);
563 END IF;
564 x_return_status := c_error;
565
566
567 WHEN e_bad_p_init_msg_list THEN
568 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
569 FEM_ENGINES_PKG.Put_Message(
570 p_app_name => 'FEM',
571 p_msg_name => 'FEM_BAD_P_INIT_MSG_LIST_ERR');
572 END IF;
573 x_return_status := c_error;
574
575
576 WHEN e_bad_p_encoded THEN
577 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
578 FEM_ENGINES_PKG.Put_Message(
579 p_app_name => 'FEM',
580 p_msg_name => 'FEM_BAD_P_ENCODED_ERR');
581 END IF;
582 x_return_status := c_error;
583
584 WHEN e_bad_p_commit THEN
585 IF FND_LOG.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
586 FEM_ENGINES_PKG.Put_Message(
587 p_app_name => 'FEM',
588 p_msg_name => 'FEM_BAD_P_COMMIT_ERR');
589 END IF;
590 x_return_status := c_error;
591
592 END Validate_OA_Params;
593
594
595 END FEM_helper_rule_util_Pkg;