[Home] [Help]
PACKAGE BODY: APPS.INL_RULE_PVT
Source
1 PACKAGE BODY INL_RULE_PVT AS
2 /* $Header: INLVRULB.pls 120.0.12020000.9 2013/05/07 18:09:44 acferrei noship $ */
3
4 L_FND_EXC_ERROR EXCEPTION;
5 L_FND_EXC_UNEXPECTED_ERROR EXCEPTION;
6
7 L_FND_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_success;
8 L_FND_RET_STS_ERROR CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_error;
9 L_FND_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := fnd_api.g_ret_sts_unexp_error;
10
11 line_number NUMBER := 0;
12
13 -- Utility name : initbuildpackage
14 -- Type : Private
15 -- Function : Initializes the global variables needed
16 -- to dynamically build the rule package.
17 --
18 -- Pre-reqs : None
19 -- Notes :
20 PROCEDURE initbuildpackage IS
21 BEGIN
22 g_build_package_row := 0;
23 g_build_package_tbl.DELETE;
24 END initbuildpackage;
25
26 -- Utility name : buildpackage
27 -- Type : Private
28 -- Function : This API takes a VARCHAR of undetermined length
29 -- and breaks it up into varchars of length 255. These
30 -- smaller strings are stored in the g_build_package_tbl,
31 -- and compose the sql statement to create the Rules package.
32 --
33 -- Pre-reqs : None
34 -- Parameters :
35 -- IN : p_package_string IN VARCHAR2
36 -- Notes :
37 PROCEDURE buildpackage(p_package_string IN VARCHAR2) IS
38 l_cur_start NUMBER;
39 l_package_length NUMBER;
40 l_num_chars NUMBER := 255;
41 l_num_chars_1 NUMBER;
42 l_row NUMBER;
43 BEGIN
44 l_cur_start := 1;
45 -- get last filled row of table
46 l_row := g_build_package_row;
47 l_package_length := LENGTH(p_package_string);
48
49 -- return if string is null
50 IF l_package_length IS NULL OR l_package_length = 0 THEN
51 RETURN;
52 END IF;
53 --Loop through string, reading off l_num_chars bytes at a time
54 LOOP
55 --When at end of varchar, exit loop;
56 EXIT WHEN l_cur_start > l_package_length;
57 l_row := l_row + 1;
58
59 IF l_package_length -l_cur_start > 255 THEN
60 l_num_chars :=INSTR(SUBSTR(p_package_string, l_cur_start, l_num_chars), ' ', -1);
61 ELSE
62 l_num_chars := 255;
63 END IF;
64
65 IF l_num_chars = 0 THEN
66 l_num_chars := 255;
67 END IF;
68
69 --Get substring from package_string
70 g_build_package_tbl(l_row) := SUBSTR(p_package_string, l_cur_start, l_num_chars);
71 --increment pointers
72 l_cur_start := l_cur_start + l_num_chars;
73
74 IF l_cur_start + l_num_chars > l_package_length THEN
75 l_num_chars := l_package_length - l_cur_start + 1;
76 END IF;
77 END LOOP;
78 g_build_package_row := l_row;
79 END buildpackage;
80
81 -- Utility name : Build_Condition
82 -- Type : Private
83 -- Function : Build where clause to a given rule using conditions entered in UI
84 -- Pre-reqs : None
85 -- Parameters :
86 -- IN : p_rule_id IN NUMBER
87 --
88 -- OUT : x_return_status OUT NOCOPY VARCHAR2
89 -- x_msg_count OUT NOCOPY NUMBER
90 -- x_msg_data OUT NOCOPY VARCHAR2
91 --
92 -- Version : Current version 1.0
93 --
94 -- Notes :
95 FUNCTION Build_Condition(p_rule_id IN NUMBER,
96 x_return_status IN OUT NOCOPY VARCHAR2,
97 x_msg_count OUT NOCOPY NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2)RETURN VARCHAR2 IS
99
100 l_func_name CONSTANT VARCHAR2(30) := 'Build_Condition';
101 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
102 l_msg_count NUMBER;
103 l_msg_data VARCHAR2(2000);
104 l_debug_info VARCHAR2(200);
105 l_var_name VARCHAR2(15);
106
107 CURSOR c_conditions(p_rule_id IN NUMBER) IS
108 SELECT c.condition_id, c.user_sequence, c.left_parenthesis_code, c.left_entity_code, LeftEnt.table_name left_tbl_name,
109 LeftAttr.attribute_code left_column_name, c.right_entity_code, RightEnt.table_name right_tbl_name,
110 RightAttr.attribute_code right_column_name, c.attribute_constant_number, c.attribute_constant_character,
111 c.attribute_constant_date, c.attribute_expression, c.line_operator_code, c.right_parenthesis_code,
112 c.logical_operator_code
113 FROM inl_conditions c,
114 inl_entities_vl LeftEnt,
115 inl_attributes_vl LeftAttr,
116 inl_entities_vl RightEnt,
117 inl_attributes_vl RightAttr
118 WHERE RightAttr.attribute_code (+) = c.right_attribute_code
119 AND RightAttr.entity_code (+) = c.right_entity_code
120 AND RightEnt.entity_code (+) = c.right_entity_code
121 AND LeftEnt.entity_code = c.left_entity_code
122 AND LeftAttr.attribute_code = c.left_attribute_code
123 AND LeftAttr.entity_code = c.left_entity_code
124 AND c.rule_id = p_rule_id
125 ORDER BY c.user_sequence;
126
127 TYPE conditions IS
128 TABLE OF c_conditions%ROWTYPE INDEX BY BINARY_INTEGER;
129 l_conditions conditions;
130
131 l_condition_dsp VARCHAR2(2000) := NULL;
132 l_line_operator_dsp VARCHAR2(80) := NULL;
133 l_value_dsp VARCHAR2(1000) := NULL;
134 l_concat_id VARCHAR2(200);
135
136 BEGIN
137
138 -- Initialize return status to success
139 x_return_status := FND_API.G_RET_STS_SUCCESS;
140
141 -- Standard Beginning of Procedure/Function Logging
142 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
143 p_procedure_name => l_func_name) ;
144 -- Logging variables
145 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
146 p_procedure_name => l_func_name,
147 p_var_name => 'p_rule_id',
148 p_var_value => p_rule_id);
149
150 OPEN c_conditions(p_rule_id);
151 FETCH c_conditions BULK COLLECT INTO l_conditions;
152 CLOSE c_conditions;
153
154 IF NVL(l_conditions.LAST, 0) > 0 THEN
155
156 FOR i IN NVL(l_conditions.FIRST, 0)..NVL(l_conditions.LAST, 0)
157 LOOP
158 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
159 p_procedure_name => l_func_name,
160 p_var_name => 'c_conditions(' || i ||').condition_id',
161 p_var_value => l_conditions(i).condition_id);
162
163 IF l_conditions(i).left_parenthesis_code IS NOT NULL THEN
164 l_condition_dsp := RTRIM(l_condition_dsp)||' '|| l_conditions(i).left_parenthesis_code;
165 END IF;
166
167 --
168 -- Build left side
169 --
170 IF l_conditions(i).left_entity_code IS NOT NULl AND l_conditions(i).left_column_name IS NOT NULL THEN
171
172 l_condition_dsp := RTRIM(l_condition_dsp)||' '|| l_conditions(i).left_entity_code || '.' || l_conditions(i).left_column_name;
173
174 IF l_concat_id IS NULL THEN
175 l_concat_id := l_conditions(i).left_entity_code || '.SHIP_HEADER_ID = :p_ship_header_id';
176 END IF;
177
178 END IF;
179
180 --
181 -- Get line_operator
182 --
183 IF l_conditions(i).line_operator_code IS NOT NULL THEN
184 l_line_operator_dsp := INL_UTILITIES_PKG.Get_LookupMeaning(
185 p_lookup_type => 'INL_CON_LINE_OPERATOR',
186 p_lookup_code => l_conditions(i).line_operator_code,
187 x_return_status => l_return_status,
188 x_msg_count => l_msg_count,
189 x_msg_data => l_msg_data);
190
191 -- If any errors happen abort API.
192 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
193 RAISE L_FND_EXC_ERROR;
194 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
195 RAISE L_FND_EXC_UNEXPECTED_ERROR;
196 END IF;
197
198 l_condition_dsp := RTRIM(l_condition_dsp)||' '|| l_line_operator_dsp;
199
200 END IF;
201
202 --
203 -- Build right side
204 --
205 l_value_dsp := NULL;
206
207 IF l_conditions(i).right_entity_code IS NOT NULL THEN
208 IF l_conditions(i).right_entity_code = 'CONS_CHAR' THEN
209 l_value_dsp := l_value_dsp || ':' || l_conditions(i).user_sequence;
210 ELSIF l_conditions(i).right_entity_code = 'CONS_DATE' THEN
211 l_value_dsp := l_value_dsp || ':' || l_conditions(i).user_sequence;
212 ELSIF l_conditions(i).right_entity_code = 'CONS_NUMBER' THEN
213 l_value_dsp := l_value_dsp || ':' || l_conditions(i).user_sequence;
214 ELSIF l_conditions(i).right_entity_code = 'EXPR' THEN
215 l_value_dsp := l_conditions(i).ATTRIBUTE_EXPRESSION;
216 ELSE
217 l_value_dsp := l_conditions(i).right_entity_code || '.' || l_conditions(i).right_column_name;
218 END IF;
219 l_condition_dsp := RTRIM(l_condition_dsp)||' '|| l_value_dsp;
220 END IF;
221
222 --
223 -- Right parenthesis
224 --
225 IF l_conditions(i).right_parenthesis_code IS NOT NULL THEN
226 l_condition_dsp := RTRIM(l_condition_dsp)||' '||
227 l_conditions(i).right_parenthesis_code;
228 END IF;
229
230 --
231 -- Get logical_operator
232 --
233 IF l_conditions(i).logical_operator_code IS NOT NULL THEN
234 l_condition_dsp := RTRIM(l_condition_dsp)|| ' ' ||
235 INL_UTILITIES_PKG.Get_LookupMeaning(
236 p_lookup_type => 'INL_CON_LOGICAL_OPERATOR',
237 p_lookup_code => l_conditions(i).logical_operator_code,
238 x_return_status => l_return_status,
239 x_msg_count => l_msg_count,
240 x_msg_data => l_msg_data);
241
242 -- If any errors happen abort API.
243 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
244 RAISE L_FND_EXC_ERROR;
245 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
246 RAISE L_FND_EXC_UNEXPECTED_ERROR;
247 END IF;
248 END IF;
249 END LOOP;
250 END IF;
251
252 IF l_condition_dsp IS NOT NULL THEN
253 l_condition_dsp := '(' || LTRIM(RTRIM(l_condition_dsp)) || ')';
254 END IF;
255
256 l_condition_dsp := l_condition_dsp || ' AND ' || l_concat_id;
257
258 -- Standard call to get message count and if count is 1, get message info.
259 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
260 p_count => x_msg_count,
261 p_data => x_msg_data);
262
263 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
264 p_procedure_name => l_func_name);
265
266 RETURN l_condition_dsp;
267
268 EXCEPTION
269 WHEN VALUE_ERROR THEN
270 -- Standard Expected Error Logging
271 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
272 p_procedure_name => l_func_name);
273 x_return_status := FND_API.G_RET_STS_ERROR;
274 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_COND_TOO_LONG');
275 FND_MSG_PUB.ADD;
276 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
277 p_count => x_msg_count,
278 p_data => x_msg_data);
279 RETURN NULL;
280 WHEN L_FND_EXC_ERROR THEN
281 -- Standard Expected Error Logging
282 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
283 p_procedure_name => l_func_name);
284 x_return_status := L_FND_RET_STS_ERROR;
285 RETURN NULL;
286 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
287 -- Standard Unexpected Error Logging
288 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
289 p_procedure_name => l_func_name) ;
290 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
291 RETURN NULL;
292 WHEN OTHERS THEN
293 -- Standard Unexpected Error Logging
294 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
295 p_procedure_name => l_func_name) ;
296 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
297 IF FND_MSG_PUB.Check_Msg_Level (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
298 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
299 p_procedure_name => l_func_name) ;
300 END IF;
301 RETURN NULL;
302 END Build_Condition;
303
304 -- Utility name : Build_FromClause
305 -- Type : Private
306 -- Function : Build FROM clause + join conditions
307 -- Pre-reqs : None
308 -- Parameters :
309 -- IN : p_rule_id IN NUMBER
310 --
311 -- OUT : x_return_status OUT NOCOPY VARCHAR2
312 -- x_msg_count OUT NOCOPY NUMBER
313 -- x_msg_data OUT NOCOPY VARCHAR2
314 --
315 -- Version : Current version 1.0
316 --
317 -- Notes :
318 FUNCTION Build_FromClause(p_rule_id IN NUMBER,
319 x_return_status IN OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2)RETURN VARCHAR2 IS
322
323 l_func_name CONSTANT VARCHAR2(30) := 'Build_FromClause';
324 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
325 l_msg_count NUMBER;
326 l_msg_data VARCHAR2(2000);
327 l_debug_info VARCHAR2(200);
328
329 l_from_clause VARCHAR2(500);
330 l_where_clause VARCHAR2(2000);
331 l_tbl_1 VARCHAR2(30);
332 l_tbl_2 VARCHAR2(30);
333 l_usage_code VARCHAR2(30);
334
335 CURSOR c_cond_tables(p_rule_id IN NUMBER) IS
336 SELECT DISTINCT e.entity_code, e.table_name || ' ' || e.entity_code AS from_table_name
337 FROM inl_entities_vl e,
338 inl_conditions c
339 WHERE e.entity_code = c.left_entity_code
340 AND e.table_name IS NOT NULL
341 AND c.rule_id = p_rule_id
342 UNION
343 SELECT DISTINCT e.entity_code, e.table_name || ' ' || e.entity_code AS from_table_name
344 FROM inl_entities_vl e,
345 inl_conditions c
346 WHERE e.entity_code = c.right_entity_code
347 AND e.table_name IS NOT NULL
348 AND c.rule_id = p_rule_id;
349
350 TYPE cond_tables IS
351 TABLE OF c_cond_tables%ROWTYPE INDEX BY BINARY_INTEGER;
352 l_cond_tables cond_tables;
353
354 BEGIN
355
356 -- Initialize return status to success
357 x_return_status := FND_API.G_RET_STS_SUCCESS;
358
359 -- Standard Beginning of Procedure/Function Logging
360 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
361 p_procedure_name => l_func_name) ;
362 -- Logging variables
363 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
364 p_procedure_name => l_func_name,
365 p_var_name => 'p_rule_id',
366 p_var_value => p_rule_id);
367
368 OPEN c_cond_tables(p_rule_id);
369 FETCH c_cond_tables BULK COLLECT INTO l_cond_tables;
370 CLOSE c_cond_tables;
371
372 SELECT usage_code
373 INTO l_usage_code
374 FROM inl_rules_vl
375 WHERE rule_id = p_rule_id;
376
377 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
378 p_procedure_name => l_func_name,
379 p_var_name => 'l_usage_code',
380 p_var_value => l_usage_code);
381
382 IF NVL(l_cond_tables.LAST, 0) > 0 AND l_usage_code = 'MANAGE_CHARGES' THEN
383 l_from_clause := ' FROM ';
384 FOR i IN NVL(l_cond_tables.FIRST, 0)..NVL(l_cond_tables.LAST, 0)
385 LOOP
386
387 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
388 p_procedure_name => l_func_name,
389 p_var_name => 'l_cond_tables(' || i ||').source_table_name',
390 p_var_value => l_cond_tables(i).from_table_name);
391
392 l_tbl_1 := l_cond_tables(i).entity_code;
393
394 -- Build FROM clause
395 l_from_clause := l_from_clause || l_cond_tables(i).from_table_name;
396
397 IF i <> NVL(l_cond_tables.LAST, 0) THEN
398 l_from_clause := l_from_clause || ',';
399 END IF;
400
401 -- Build WHERE clause if exists more than one table
402 IF NVL(l_cond_tables.LAST, 0) > 1 THEN
403 FOR j IN NVL(l_cond_tables.FIRST, 0)..NVL(l_cond_tables.LAST, 0)
404 LOOP
405 IF i <> j AND j > i THEN
406 l_tbl_2 := l_cond_tables(j).entity_code;
407
408 IF l_where_clause IS NOT NULL THEN
409 l_where_clause := l_where_clause || ' AND ';
410 ELSE
411 l_where_clause := ' WHERE ';
412 END IF;
413
414 -- Check all combinations for 3 tables: INL_SHIP_HEADERS, INL_SHIP_LINE_GROUPS and INL_SHIP_LINES
415 IF(l_tbl_1 = 'ISH' OR l_tbl_2 = 'ISH') AND
416 (l_tbl_1 = 'ISLG' OR l_tbl_2 = 'ISLG') THEN
417 l_where_clause := l_where_clause || l_tbl_1 || '.SHIP_HEADER_ID = ' || l_tbl_2 || '.SHIP_HEADER_ID';
418 ELSIF(l_tbl_1 = 'ISH' OR l_tbl_2 = 'ISH') AND
419 (l_tbl_1 = 'ISL' OR l_tbl_2 = 'ISL') THEN
420 l_where_clause := l_where_clause || l_tbl_1 || '.SHIP_HEADER_ID = ' || l_tbl_2 || '.SHIP_HEADER_ID';
421 ELSIF(l_tbl_1 = 'ISLG' OR l_tbl_2 = 'ISLG') AND
422 (l_tbl_1 = 'ISL' OR l_tbl_2 = 'ISL') THEN
423 l_where_clause := l_where_clause || l_tbl_1 || '.SHIP_LINE_GROUP_ID =' || l_tbl_2 || '.SHIP_LINE_GROUP_ID';
424 END IF;
425 END IF;
426 END LOOP;
427 END IF;
428 END LOOP;
429 END IF;
430
431 l_from_clause := l_from_clause || l_where_clause;
432
433 -- Standard call to get message count and if count is 1, get message info.
434 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
435 p_count => x_msg_count,
436 p_data => x_msg_data);
437
438 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
439 p_procedure_name => l_func_name);
440
441 RETURN l_from_clause;
442
443 EXCEPTION
444 WHEN VALUE_ERROR THEN
445 -- Standard Expected Error Logging
446 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
447 p_procedure_name => l_func_name);
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_COND_TOO_LONG');
450 FND_MSG_PUB.ADD;
451 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
452 p_count => x_msg_count,
453 p_data => x_msg_data);
454 RETURN NULL;
455 WHEN L_FND_EXC_ERROR THEN
456 -- Standard Expected Error Logging
457 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
458 p_procedure_name => l_func_name);
459 x_return_status := L_FND_RET_STS_ERROR;
460 RETURN NULL;
461 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
462 -- Standard Unexpected Error Logging
463 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
464 p_procedure_name => l_func_name) ;
465 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
466 RETURN NULL;
467 WHEN OTHERS THEN
468 -- Standard Unexpected Error Logging
469 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
470 p_procedure_name => l_func_name) ;
471 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
472 IF FND_MSG_PUB.Check_Msg_Level (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
473 FND_MSG_PUB.Add_Exc_Msg (p_pkg_name => g_pkg_name,
474 p_procedure_name => l_func_name) ;
475 END IF;
476 RETURN NULL;
477 END Build_FromClause;
478
479 -- API name : Build_Query
480 -- Type : Private
481 -- Function : Build select statement from the setup user entered in rules/conditions
482 --
483 -- Pre-reqs : None
484 -- Parameters :
485 -- IN : p_api_version IN NUMBER,
486 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
487 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
488 -- p_rule_id IN NUMBER
489 --
490 -- OUT x_return_status OUT NOCOPY VARCHAR2
491 -- x_msg_count OUT NOCOPY NUMBER
492 -- x_msg_data OUT NOCOPY VARCHAR2
493 --
494 -- Version : Current version 1.0
495 --
496 -- Notes :
497 FUNCTION Build_Query(p_api_version IN NUMBER,
498 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
499 p_commit IN VARCHAR2 := FND_API.G_FALSE,
500 p_rule_id IN NUMBER,
501 x_return_status OUT NOCOPY VARCHAR2,
502 x_msg_count OUT NOCOPY NUMBER,
503 x_msg_data OUT NOCOPY VARCHAR2)RETURN VARCHAR2
504 IS
505 l_api_name CONSTANT VARCHAR2(30) := 'Build_Query';
506 l_api_version CONSTANT NUMBER := 1.0;
507 l_return_status VARCHAR2(1);
508 l_msg_count NUMBER;
509 l_msg_data VARCHAR2(2000);
510 l_debug_info VARCHAR2(200);
511 l_from_clause VARCHAR2(2000);
512 l_query VARCHAR2(2000);
513 l_where_clause_cond VARCHAR2(2000);
514 l_condition_count NUMBER;
515
516 BEGIN
517
518 -- Standard Beginning of Procedure/Function Logging
519 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
520 p_procedure_name => l_api_name) ;
521
522 -- Initialize message list if p_init_msg_list is set to TRUE.
523 IF FND_API.to_Boolean(p_init_msg_list) THEN
524 FND_MSG_PUB.initialize;
525 END IF;
526
527 -- Standard call to check for call compatibility
528 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
529 p_caller_version_number => p_api_version,
530 p_api_name => l_api_name,
531 p_pkg_name => g_pkg_name ) THEN
532 RAISE L_FND_EXC_UNEXPECTED_ERROR;
533 END IF;
534
535 -- Initialize API return status to success
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 -- Check whether conditions exist for a given rule.
539 -- In case there is no condition created, skip the
540 -- build query execution
541 SELECT COUNT(1)
542 INTO l_condition_count
543 FROM inl_conditions
544 WHERE rule_id = p_rule_id;
545
546 IF NVL(l_condition_count,0) = 0 THEN
547 RETURN NULL;
548 END IF;
549
550 -- Logging variables
551 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
552 p_procedure_name => l_api_name,
553 p_var_name => 'p_rule_id',
554 p_var_value => p_rule_id);
555
556 l_from_clause := INL_RULE_PVT.Build_FromClause(
557 p_rule_id => p_rule_id,
558 x_return_status => l_return_status,
559 x_msg_count => l_msg_count,
560 x_msg_data => l_msg_data);
561
562 -- If any errors happen abort API.
563 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
564 RAISE L_FND_EXC_ERROR;
565 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
566 RAISE L_FND_EXC_UNEXPECTED_ERROR;
567 END IF;
568
569 l_debug_info := 'Call Build_Condition)';
570 INL_LOGGING_PVT.Log_Statement (
571 p_module_name => g_module_name,
572 p_procedure_name => l_api_name,
573 p_debug_info => l_debug_info);
574
575 -- Concat WHERE clause defined in conditions
576 l_where_clause_cond := l_where_clause_cond ||
577 Build_Condition(
578 p_rule_id => p_rule_id,
579 x_return_status => l_return_status,
580 x_msg_count => l_msg_count,
581 x_msg_data => l_msg_data);
582
583 -- If any errors happen abort API.
584 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
585 RAISE L_FND_EXC_ERROR;
586 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
587 RAISE L_FND_EXC_UNEXPECTED_ERROR;
588 END IF;
589
590 IF INSTR(UPPER(l_from_clause), 'WHERE') = 0 AND l_where_clause_cond IS NOT NULL THEN
591 l_where_clause_cond := ' WHERE ' || l_where_clause_cond;
592 ELSE
593 l_where_clause_cond := ' AND ' || l_where_clause_cond;
594 END IF;
595
596 l_query := 'SELECT COUNT(1) ' || l_from_clause || l_where_clause_cond;
597
598 -- Standard call to get message count and if count is 1, get message info.
599 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
600 p_count => x_msg_count,
601 p_data => x_msg_data);
602
603 -- Standard End of Procedure/Function Logging
604 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
605 p_procedure_name => l_api_name);
606
607 RETURN l_query;
608
609 EXCEPTION
610 WHEN VALUE_ERROR THEN
611 -- Standard Expected Error Logging
612 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
613 p_procedure_name => l_api_name);
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_COND_TOO_LONG');
616 FND_MSG_PUB.ADD;
617 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
618 p_count => x_msg_count,
619 p_data => x_msg_data);
620 RETURN NULL;
621 WHEN FND_API.G_EXC_ERROR THEN
622 -- Standard Expected Error Logging
623 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
624 p_procedure_name => l_api_name);
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
627 p_count => x_msg_count,
628 p_data => x_msg_data);
629 RETURN NULL;
630 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
631 -- Standard Unexpected Error Logging
632 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
633 p_procedure_name => l_api_name);
634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
636 p_count => x_msg_count,
637 p_data => x_msg_data);
638 RETURN NULL;
639 WHEN OTHERS THEN
640 -- Standard Unexpected Error Logging
641 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
642 p_procedure_name => l_api_name);
643
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
645 IF FND_MSG_PUB.Check_Msg_Level(
646 p_message_level =>FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
647 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
648 p_procedure_name => l_api_name);
649 END IF;
650 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
651 p_count => x_msg_count,
652 p_data => x_msg_data);
653 RETURN NULL;
654 END Build_Query;
655
656 -- Utility name : Build_SpecPackage
657 -- Type : Private
658 -- Function : Builds the INL_RULE_#### package specification
659 -- with function corresponding to a given rule.
660 --
661 -- Pre-reqs : None
662 -- IN : p_rule_id IN NUMBER
663 --
664 -- OUT : x_return_status OUT NOCOPY VARCHAR2
665 -- x_msg_count OUT NOCOPY NUMBER
666 -- x_msg_data OUT NOCOPY VARCHAR2
667 --
668 -- Version : Current version 1.0
669 --
670 -- Notes :
671 PROCEDURE Build_SpecPackage(p_rule_id IN NUMBER,
672 x_message_name IN OUT NOCOPY VARCHAR2,
673 x_return_status OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER,
675 x_msg_data OUT NOCOPY VARCHAR2) IS
676
677 l_func_name CONSTANT VARCHAR2(30) := 'Build_SpecPackage';
678 l_applsys_schema varchar2(30);
679 l_count NUMBER := 1;
680 l_package_name varchar2(30);
681 l_error VARCHAR2(10);
682 l_rule_name VARCHAR2(30);
683
684 CURSOR c_package_status(p_package_name VARCHAR2)IS
685 SELECT status
686 FROM user_objects
687 WHERE object_name = UPPER(p_package_name)
688 AND object_type = 'PACKAGE'
689 AND status <> 'VALID'
690 AND rownum = 1;
691
692 BEGIN
693
694 -- Initialize return status to success
695 x_return_status := FND_API.G_RET_STS_SUCCESS;
696
697 -- Standard Beginning of Procedure/Function Logging
698 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
699 p_procedure_name => l_func_name) ;
700 -- Logging variables
701 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
702 p_procedure_name => l_func_name,
703 p_var_name => 'p_rule_id',
704 p_var_value => p_rule_id);
705
706 -- Get applsys schema name
707 SELECT user
708 INTO l_applsys_schema
709 FROM dual;
710
711 SELECT r.package_name, r.rule_name
712 INTO l_package_name, l_rule_name
713 FROM inl_rules_vl r
714 WHERE r.package_name IS NOT NULL
715 AND r.rule_id = p_rule_id;
716
717 -- First line to create package specification
718 ad_ddl.build_package('CREATE OR REPLACE PACKAGE ' || l_package_name || ' AS ', l_count);
719
720 IF l_package_name IS NOT NULL THEN
721 l_count := l_count+1;
722 ad_ddl.build_package('FUNCTION Get_Value(p_ship_header_id IN NUMBER)', l_count);
723 l_count := l_count+1;
724 ad_ddl.build_package('RETURN VARCHAR2; ', l_count);
725 l_count := l_count+1;
726 ad_ddl.build_package(' ', l_count);
727 END IF;
728 l_count := l_count+1;
729 ad_ddl.build_package('END ' || l_package_name || ';', l_count);
730 ad_ddl.create_package(l_applsys_schema, 'INL', l_package_name, 'FALSE', 1, l_count);
731
732 -- Check status, return error if the package that was created is invalid
733 OPEN c_package_status(l_package_name);
734 FETCH c_package_status INTO l_error;
735
736 IF c_package_status%FOUND THEN
737 x_message_name := 'INL_ERR_RULE_PACKAGE_FAILED' ;
738 END IF;
739
740 -- Standard call to get message count and if count is 1, get message info.
741 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
742 p_count => x_msg_count,
743 p_data => x_msg_data);
744
745 -- Standard End of Procedure/Function Logging
746 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
747 p_procedure_name => l_func_name);
748
749 EXCEPTION
750 WHEN OTHERS THEN
751 x_return_status := fnd_api.g_ret_sts_unexp_error;
752 END Build_SpecPackage;
753
754 -- Utility name : Build_BodyPackage
755 -- Type : Private
756 -- Function : Builds the INL_RULE_#### package body with function
757 -- corresponding to a given rule.
758 --
759 -- Pre-reqs : None
760 -- Parameters :
761 -- IN : p_rule_id IN NUMBER
762 --
763 -- OUT : x_message_name IN OUT NOCOPY VARCHAR2
764 -- x_return_status OUT NOCOPY VARCHAR2
765 -- x_msg_count OUT NOCOPY NUMBER
766 -- x_msg_data OUT NOCOPY VARCHAR2
767 --
768 -- Version : Current version 1.0
769 --
770 -- Notes :
771 PROCEDURE Build_BodyPackage(p_rule_id IN NUMBER,
772 x_message_name IN OUT NOCOPY VARCHAR2,
773 x_return_status OUT NOCOPY VARCHAR2,
774 x_msg_count OUT NOCOPY NUMBER,
775 x_msg_data OUT NOCOPY VARCHAR2) IS
776
777 l_func_name CONSTANT VARCHAR2(30) := 'Build_BodyPackage';
778 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
779 l_msg_count NUMBER;
780 l_msg_data VARCHAR2(2000);
781 l_debug_info VARCHAR2(200);
782
783 l_query VARCHAR2(2000);
784 l_applsys_schema VARCHAR2(30);
785 l_count NUMBER := 1;
786 l_package_name VARCHAR(30);
787 l_error VARCHAR2(10);
788 l_rule_name VARCHAR2(30);
789 l_count_condition_lines NUMBER;
790 l_using_param VARCHAR2(1000);
791
792 l_check_submit VARCHAR2(1); -- Bug 16758966
793
794 CURSOR c_package_body_status(p_package_name VARCHAR2)IS
795 SELECT status
796 FROM user_objects
797 WHERE object_name = UPPER(p_package_name)
798 AND object_type = 'PACKAGE BODY'
799 AND status <> 'VALID'
800 AND rownum = 1;
801
802 CURSOR c_conditions(p_rule_id IN NUMBER) IS
803 SELECT c.user_sequence, c.right_entity_code, RAttr.datatype_code,
804 DECODE(c.right_entity_code, 'CONS_CHAR', 'VARCHAR2(240)',
805 DECODE(c.right_entity_code,'CONS_NUMBER', 'NUMBER',
806 DECODE(c.right_entity_code,'CONS_DATE', 'DATE', NULL))) AS datatype,
807 c.attribute_expression, c.attribute_constant_character,
808 c.attribute_constant_number, c.attribute_constant_date
809 FROM inl_conditions c,
810 inl_attributes_vl RAttr
811 WHERE RAttr.attribute_code (+) = c.right_attribute_code
812 AND c.rule_id = p_rule_id
813 AND c.right_entity_code IN ('CONS_CHAR', 'CONS_NUMBER', 'CONS_DATE')
814 ORDER BY c.user_sequence;
815
816 TYPE conditions IS
817 TABLE OF c_conditions%ROWTYPE INDEX BY BINARY_INTEGER;
818 l_cond conditions;
819
820 BEGIN
821
822 -- Initialize return status to success
823 x_return_status := FND_API.G_RET_STS_SUCCESS;
824
825 -- Standard Beginning of Procedure/Function Logging
826 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
827 p_procedure_name => l_func_name) ;
828 -- Logging variables
829 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
830 p_procedure_name => l_func_name,
831 p_var_name => 'p_rule_id',
832 p_var_value => p_rule_id);
833
834 -- Get applsys schema name
835 SELECT user
836 INTO l_applsys_schema
837 FROM dual;
838
839 SELECT irb.package_name, irb.rule_name
840 INTO l_package_name, l_rule_name
841 FROM inl_rules_vl irb
842 WHERE irb.package_name IS NOT NULL
843 AND rule_id = p_rule_id;
844
845 -- First line to create package specification
846 ad_ddl.build_package('CREATE OR REPLACE PACKAGE BODY ' || l_package_name || ' AS ', l_count);
847
848 IF l_package_name IS NOT NULL THEN
849 -- This function will return the query which is built
850 -- based on the conditions from a given rule
851 l_query := Build_Query(p_api_version => 1.0,
852 p_init_msg_list => FND_API.G_FALSE,
853 p_commit => FND_API.G_FALSE,
854 p_rule_id => p_rule_id,
855 x_return_status => l_return_status,
856 x_msg_count => l_msg_count,
857 x_msg_data => l_msg_data);
858
859 -- Init global variables
860 initbuildpackage;
861
862 -- Store query result in a table broken in
863 -- pieces of VARCHAR2 255 to avoid data overflow
864 buildpackage(l_query);
865
866 -- Build function
867 l_count := l_count+1;
868 ad_ddl.build_package('FUNCTION Get_Value(p_ship_header_id IN NUMBER)' || ' RETURN VARCHAR2 IS ', l_count);
869 l_count := l_count+1;
870 ad_ddl.build_package('l_count NUMBER; ', l_count);
871 l_count := l_count+1;
872 ad_ddl.build_package('sql_stmt VARCHAR2(4000); ', l_count);
873 l_count := l_count+1;
874
875 OPEN c_conditions(p_rule_id);
876 FETCH c_conditions BULK COLLECT INTO l_cond;
877 CLOSE c_conditions;
878
879 -- Declare Value for right attribute
880 IF NVL(l_cond.LAST, 0) > 0 THEN
881 FOR i IN NVL(l_cond.FIRST, 0)..NVL(l_cond.LAST, 0)
882 LOOP
883 IF l_cond(i).right_entity_code = 'CONS_CHAR' THEN
884 ad_ddl.build_package('l_right_attr' || l_cond(i).user_sequence || ' ' ||
885 l_cond(i).datatype || ' := ''' || l_cond(i).attribute_constant_character ||''';', l_count);
886 ELSIF l_cond(i).right_entity_code = 'CONS_NUMBER' THEN
887 ad_ddl.build_package('l_right_attr' || l_cond(i).user_sequence || ' ' ||
888 l_cond(i).datatype || ' := ' || l_cond(i).attribute_constant_number || ';', l_count);
889 ELSIF l_cond(i).right_entity_code = 'CONS_DATE' THEN
890 ad_ddl.build_package('l_right_attr' || l_cond(i).user_sequence || ' ' ||
891 l_cond(i).datatype || ' := ''' || l_cond(i).attribute_constant_date ||''';', l_count);
892 END IF;
893
894 IF l_cond(i).right_entity_code IN ('CONS_CHAR', 'CONS_NUMBER', 'CONS_DATE') THEN
895 IF l_using_param IS NOT NULL THEN
896 l_using_param := l_using_param || ',';
897 END IF;
898 l_using_param := l_using_param || 'l_right_attr' || l_cond(i).user_sequence;
899 END IF;
900 l_count := l_count+1;
901 END LOOP;
902
903 l_using_param := l_using_param || ', p_ship_header_id';
904
905 END IF;
906
907 IF l_using_param IS NULL THEN
908 l_using_param := ' p_ship_header_id';
909 END IF;
910
911 ad_ddl.build_package('l_complete_ship VARCHAR2(1) := ''Y''; ', l_count);
912 l_count := l_count+1;
913
914 ad_ddl.build_package('BEGIN ', l_count);
915 l_count := l_count+1;
916
917 ad_ddl.build_package('sql_stmt := '' ', l_count);
918 l_count := l_count+1;
919
920 -- Parse the global variable to concatenate
921 -- all the mounted pieces of the query
922 IF NVL(g_build_package_tbl.COUNT,0) > 0 THEN
923 FOR j IN 1 .. g_build_package_tbl.COUNT
924 LOOP
925 ad_ddl.build_package(TO_CHAR(g_build_package_tbl(j)), l_count);
926 l_count := l_count+1;
927 END LOOP;
928
929 ad_ddl.build_package('''; ', l_count);
930 END IF;
931
932 l_count := l_count+1;
933
934 ad_ddl.build_package('EXECUTE IMMEDIATE sql_stmt INTO l_count USING ' || l_using_param || ';', l_count);
935 l_count := l_count+1;
936
937 ad_ddl.build_package(' IF NVL(l_count,0) > 0 THEN ', l_count);
938 l_count := l_count+1;
939 ad_ddl.build_package(' l_complete_ship := ''N''; ', l_count);
940 l_count := l_count+1;
941 ad_ddl.build_package(' END IF; ', l_count);
942 l_count := l_count+1;
943 ad_ddl.build_package('RETURN l_complete_ship; ', l_count);
944 l_count := l_count+1;
945 ad_ddl.build_package('END Get_Value; ', l_count);
946 END IF;
947
948 l_count := l_count+1;
949 ad_ddl.build_package('END ' || l_package_name || '; ', l_count);
950 ad_ddl.create_package(l_applsys_schema, 'INL', l_package_name, 'TRUE', 1, l_count);
951
952 -- Check status, return error if the package that was created is invalid
953 OPEN c_package_body_status(l_package_name);
954 FETCH c_package_body_status INTO l_error;
955
956 IF c_package_body_status%FOUND THEN
957 x_message_name := 'INL_ERR_RULE_PACKAGE_FAILED' ;
958 END IF;
959
960 -- Bug 16758966
961 l_debug_info := 'Package is compiled successfully, it will validate in runtime';
962 INL_LOGGING_PVT.Log_Statement (
963 p_module_name => g_module_name,
964 p_procedure_name => l_func_name,
965 p_debug_info => l_debug_info);
966
967 IF x_message_name IS NULL THEN
968 l_debug_info := 'Validate Condition. Call INL_RULE_GRP.Check_Condition.';
969 INL_LOGGING_PVT.Log_Statement(p_module_name => g_module_name,
970 p_procedure_name => l_func_name,
971 p_debug_info => l_debug_info);
972
973 BEGIN
974 l_check_submit := INL_RULE_GRP.Check_Condition(p_api_version => 1.0,
975 p_init_msg_list => fnd_api.g_false,
976 p_commit => fnd_api.g_false,
977 p_ship_header_id => 1,
978 p_rule_package_name => l_package_name,
979 x_return_status => l_return_status,
980 x_msg_count => l_msg_count,
981 x_msg_data => l_msg_data);
982 EXCEPTION
983 WHEN OTHERS THEN
984 x_message_name := 'INL_ERR_RULE_CONDITION_FAILED';
985 END;
986 END IF;
987 -- End Bug 16758966
988
989 -- Standard call to get message count and if count is 1, get message info.
990 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
991 p_count => x_msg_count,
992 p_data => x_msg_data);
993
994 -- Standard End of Procedure/Function Logging
995 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
996 p_procedure_name => l_func_name);
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 x_return_status := fnd_api.g_ret_sts_unexp_error;
1001 END Build_BodyPackage;
1002
1003 -- Utility name : Build_ConditionPackage
1004 -- Type : Private
1005 -- Function : This routine is called by the UI when rule is enabled.
1006 -- It builds the package for Rule
1007 -- Pre-reqs : None
1008 -- Parameters :
1009 -- IN : p_api_version IN NUMBER,
1010 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1011 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1012 -- p_rule_id IN NUMBER
1013 --
1014 -- OUT x_message_name IN OUT NOCOPY VARCHAR2,
1015 -- x_return_status OUT NOCOPY VARCHAR2
1016 -- x_msg_count OUT NOCOPY NUMBER
1017 -- x_msg_data OUT NOCOPY VARCHAR2
1018 --
1019 -- Version : Current version 1.0
1020 --
1021 -- Notes :
1022 PROCEDURE Build_ConditionPackage(p_api_version IN NUMBER,
1023 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1024 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1025 p_rule_id IN NUMBER,
1026 x_message_name IN OUT NOCOPY VARCHAR2,
1027 x_return_status OUT NOCOPY VARCHAR2,
1028 x_msg_count OUT NOCOPY NUMBER,
1029 x_msg_data OUT NOCOPY VARCHAR2)IS
1030
1031 l_api_name CONSTANT VARCHAR2(30) := 'Build_ConditionPackage';
1032 l_api_version CONSTANT NUMBER := 1.0;
1033 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1034 l_msg_count NUMBER;
1035 l_msg_data VARCHAR2(2000);
1036 l_debug_info VARCHAR2(200);
1037
1038 l_rule_name VARCHAR2(30);
1039 l_usage_code VARCHAR2(30);
1040
1041 BEGIN
1042
1043 -- Standard Beginning of Procedure/Function Logging
1044 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
1045 p_procedure_name => l_api_name) ;
1046
1047 -- Initialize message list if p_init_msg_list is set to TRUE.
1048 IF FND_API.to_Boolean(p_init_msg_list) THEN
1049 FND_MSG_PUB.initialize;
1050 END IF;
1051
1052 -- Standard call to check for call compatibility
1053 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
1054 p_caller_version_number => p_api_version,
1055 p_api_name => l_api_name,
1056 p_pkg_name => g_pkg_name ) THEN
1057 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1058 END IF;
1059
1060 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1061 p_procedure_name => l_api_name,
1062 p_var_name => 'p_rule_id: ',
1063 p_var_value => p_rule_id);
1064
1065 -- Initialize API return status to success
1066 x_return_status := FND_API.G_RET_STS_SUCCESS;
1067
1068 SELECT r.rule_name, r.usage_code
1069 INTO l_rule_name, l_usage_code
1070 FROM inl_rules_vl r
1071 WHERE r.rule_id = p_rule_id;
1072
1073 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1074 p_procedure_name => l_api_name,
1075 p_var_name => 'l_usage_code: ',
1076 p_var_value => l_usage_code);
1077
1078 IF l_usage_code = 'MANAGE_CHARGES' THEN
1079 IF l_rule_name IS NOT NULL THEN
1080 UPDATE inl_rules_b
1081 SET package_name = 'INL_RULE_' || p_rule_id
1082 WHERE package_name IS NULL
1083 AND rule_id = p_rule_id;
1084 END IF;
1085
1086 INL_LOGGING_PVT.Log_Statement(p_module_name => g_module_name,
1087 p_procedure_name => l_api_name,
1088 p_debug_info => 'Building INL_RULE_' || p_rule_id ||'_PVT - Package Specification');
1089
1090 Build_SpecPackage(p_rule_id,
1091 x_message_name => x_message_name,
1092 x_return_status => l_return_status,
1093 x_msg_count => l_msg_count,
1094 x_msg_data => l_msg_data);
1095
1096 -- If any errors happen abort API.
1097 IF l_return_status = fnd_api.g_ret_sts_error THEN
1098 RAISE L_FND_EXC_ERROR;
1099 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1100 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1101 END IF;
1102
1103 INL_LOGGING_PVT.Log_Statement(p_module_name => g_module_name,
1104 p_procedure_name => l_api_name,
1105 p_debug_info => 'Building INL_RULE_' || p_rule_id ||'_PVT - Package Body');
1106
1107 Build_BodyPackage(p_rule_id => p_rule_id,
1108 x_message_name => x_message_name,
1109 x_return_status => l_return_status,
1110 x_msg_count => l_msg_count,
1111 x_msg_data => l_msg_data);
1112
1113 -- If any errors happen abort API.
1114 IF l_return_status = fnd_api.g_ret_sts_error THEN
1115 RAISE L_FND_EXC_ERROR;
1116 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1117 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1118 END IF;
1119 END IF;
1120
1121 -- Standard call to get message count and if count is 1, get message info.
1122 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1123 p_count => x_msg_count,
1124 p_data => x_msg_data);
1125
1126 -- Standard End of Procedure/Function Logging
1127 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
1128 p_procedure_name => l_api_name);
1129
1130 EXCEPTION
1131 WHEN L_FND_EXC_ERROR THEN
1132 -- Standard Expected Error Logging
1133 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
1134 p_procedure_name => l_api_name);
1135 x_return_status := L_FND_RET_STS_ERROR;
1136 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1137 p_count => x_msg_count,
1138 p_data => x_msg_data);
1139 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
1140 -- Standard Unexpected Error Logging
1141 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
1142 p_procedure_name => l_api_name) ;
1143 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1144 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1145 p_count => x_msg_count,
1146 p_data => x_msg_data);
1147 WHEN OTHERS THEN
1148 -- Standard Unexpected Error Logging
1149 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
1150 p_procedure_name => l_api_name) ;
1151 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1152 IF FND_MSG_PUB.Check_Msg_Level (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1153 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
1154 p_procedure_name => l_api_name) ;
1155 END IF;
1156 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1157 p_count => x_msg_count,
1158 p_data => x_msg_data);
1159
1160 END Build_ConditionPackage;
1161
1162 -- Utility name : Validate_Rules
1163 -- Type : Private
1164 -- Function : Validate conditions of a given rule.
1165 -- Returns TRUE or FALSE depending on the validation status
1166 -- Pre-reqs : None
1167 -- Parameters :
1168 -- IN : p_api_version IN NUMBER
1169 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1170 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1171 -- p_rule_id IN NUMBER
1172 --
1173 -- OUT : x_message_name IN OUT NOCOPY VARCHAR2
1174 -- x_return_status OUT NOCOPY VARCHAR2
1175 -- x_msg_count OUT NOCOPY NUMBER
1176 -- x_msg_data OUT NOCOPY VARCHAR2
1177 --
1178 -- Version : Current version 1.0
1179 --
1180 -- Notes :
1181 FUNCTION Validate_Rules(p_api_version IN NUMBER,
1182 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1183 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1184 p_rule_id IN NUMBER,
1185 x_message_name IN OUT NOCOPY VARCHAR2,
1186 x_return_status OUT NOCOPY VARCHAR2,
1187 x_msg_count OUT NOCOPY NUMBER,
1188 x_msg_data OUT NOCOPY VARCHAR2)RETURN VARCHAR2
1189 IS
1190 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Rules';
1191 l_debug_info VARCHAR2(200);
1192 l_api_version CONSTANT NUMBER := 1.0;
1193
1194 l_exist VARCHAR2(1);
1195 l_return VARCHAR2(5) := 'TRUE';
1196 l_count_condition NUMBER;
1197 l_max_left_seq NUMBER;
1198 l_max_right_seq NUMBER;
1199 l_min_left_seq NUMBER;
1200 l_min_right_seq NUMBER;
1201 l_desc_user_sequence NUMBER;
1202 l_last_row_with_logical_oper NUMBER;
1203 l_not_last_row_no_logical_oper NUMBER;
1204 l_precedence_left VARCHAR2(1) := 'N';
1205 l_attr_exp VARCHAR2(240);
1206 l_instr_left_parenthesis NUMBER;
1207 l_validate_expr VARCHAR2(240);
1208 l_db_object VARCHAR2(240);
1209 l_parameters VARCHAR(240);
1210 l_db_function VARCHAR2(128);
1211 l_valid_function NUMBER;
1212 l_valid_aspas NUMBER;
1213 l_right_attr_exp VARCHAR2(240);
1214
1215 CURSOR c_desc_parenthesis(p_rule_id IN NUMBER)IS
1216 SELECT count(1)
1217 FROM inl_conditions c
1218 WHERE c.rule_id = p_rule_id
1219 AND c.left_parenthesis_code IS NOT NULL
1220 MINUS
1221 SELECT count(1)
1222 FROM inl_conditions c1
1223 WHERE c1.rule_id = p_rule_id
1224 AND c1.right_parenthesis_code IS NOT NULL;
1225 TYPE desc_parenthesis IS
1226 TABLE OF c_desc_parenthesis%ROWTYPE INDEX BY BINARY_INTEGER;
1227 l_desc_parenthesis desc_parenthesis;
1228
1229 CURSOR c_precedence_parenthesis(p_rule_id IN NUMBER) IS
1230 SELECT c.user_sequence, c.left_parenthesis_code,
1231 c.right_parenthesis_code, NULL AS right_parenthesis_close
1232 FROM inl_conditions c
1233 WHERE rule_id = p_rule_id
1234 ORDER BY c.user_sequence;
1235
1236 TYPE precedence_parenthesis IS
1237 TABLE OF c_precedence_parenthesis%ROWTYPE INDEX BY BINARY_INTEGER;
1238 l_precedence_parenthesis precedence_parenthesis;
1239
1240 -- Get all rows which are not the last row or rows with just left parenthesis
1241 -- and have no logical operator
1242 CURSOR c_no_logical_operator(p_sequence NUMBER) IS
1243 SELECT c.user_sequence
1244 FROM inl_conditions c
1245 WHERE c.rule_id = p_rule_id
1246 AND c.logical_operator_code IS NULL
1247 AND c.user_sequence <> p_sequence
1248 AND NOT EXISTS (SELECT condition_id
1249 FROM inl_conditions c2
1250 WHERE c2.rule_id = p_rule_id
1251 AND c2.left_parenthesis_code IS NOT NULL
1252 AND c2.left_entity_code IS NULL
1253 AND c2.condition_id = c.condition_id);
1254
1255 l_no_logical_operator c_no_logical_operator%ROWTYPE;
1256
1257 -- Check if next row has only right parentheses
1258 -- and have no logical operator
1259 CURSOR c_only_right_parenthesis IS
1260 SELECT 'x'
1261 FROM inl_conditions c
1262 WHERE c.rule_id = p_rule_id
1263 AND c.left_entity_code IS NULL
1264 AND c.right_parenthesis_code IS NOT NULL
1265 AND c.user_sequence = (SELECT a.user_sequence
1266 FROM inl_conditions a
1267 WHERE a.rule_id = c.rule_id
1268 AND a.user_sequence > l_no_logical_operator.user_sequence
1269 AND rownum = 1);
1270
1271 -- Validate Right Attribute
1272 CURSOR c_right_attrs(p_rule_id IN NUMBER) IS
1273 SELECT c.user_sequence, c.right_entity_code, RAttr.datatype_code,
1274 c.attribute_expression, c.attribute_constant_character,
1275 c.attribute_constant_number, c.attribute_constant_date
1276 FROM inl_conditions c,
1277 inl_attributes_vl RAttr
1278 WHERE RAttr.attribute_code (+) = c.right_attribute_code
1279 AND c.rule_id = p_rule_id
1280 AND c.right_entity_code IN ( 'EXPR', 'CONS_CHAR')
1281 ORDER BY c.user_sequence;
1282
1283 TYPE right_attrs IS
1284 TABLE OF c_right_attrs%ROWTYPE INDEX BY BINARY_INTEGER;
1285 l_right_attrs right_attrs;
1286
1287 BEGIN
1288
1289 -- Standard Beginning of Procedure/Function Logging
1290 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
1291 p_procedure_name => l_api_name) ;
1292
1293 -- Initialize message list if p_init_msg_list is set to TRUE.
1294 IF FND_API.to_Boolean(p_init_msg_list) THEN
1295 FND_MSG_PUB.initialize;
1296 END IF;
1297
1298 -- Standard call to check for call compatibility
1299 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
1300 p_caller_version_number => p_api_version,
1301 p_api_name => l_api_name,
1302 p_pkg_name => g_pkg_name ) THEN
1303 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1304 END IF;
1305
1306 -- Initialize API return status to success
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308
1309 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1310 p_procedure_name => l_api_name,
1311 p_var_name => 'p_rule_id: ',
1312 p_var_value => p_rule_id);
1313
1314 -- Verify if Rule has condition line(s)
1315 SELECT count(1)
1316 INTO l_count_condition
1317 FROM inl_conditions c
1318 WHERE c.rule_id = p_rule_id;
1319
1320 IF(NVL(l_count_condition,0) = 0) THEN
1321 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_COND_NOT_COMPLETED') ;
1322 FND_MSG_PUB.ADD;
1323 x_message_name := 'INL_ERR_COND_NOT_COMPLETED';
1324 l_return := 'FALSE';
1325 END IF;
1326
1327 IF l_return = 'TRUE' THEN
1328 l_debug_info := 'Validate parentheses.';
1329 INL_LOGGING_PVT.Log_Statement(p_module_name => g_module_name,
1330 p_procedure_name => l_api_name,
1331 p_debug_info => l_debug_info);
1332
1333 -- Validate parentheses
1334 OPEN c_desc_parenthesis(p_rule_id);
1335 FETCH c_desc_parenthesis BULK COLLECT INTO l_desc_parenthesis;
1336 CLOSE c_desc_parenthesis;
1337
1338 IF NVL(l_desc_parenthesis.LAST, 0) > 0 THEN
1339 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_CON_UNEQUAL_PAR') ;
1340 FND_MSG_PUB.ADD;
1341 x_message_name := 'INL_ERR_CON_UNEQUAL_PAR';
1342 l_return := 'FALSE';
1343 END IF;
1344 END IF;
1345
1346 IF l_return = 'TRUE' THEN
1347 SELECT NVL(max(user_sequence),0)
1348 INTO l_max_left_seq
1349 FROM inl_conditions c
1350 WHERE c.rule_id = p_rule_id
1351 AND c.left_parenthesis_code IS NOT NULL;
1352
1353 SELECT NVL(max(user_sequence),0)
1354 INTO l_max_right_seq
1355 FROM inl_conditions c
1356 WHERE c.rule_id = p_rule_id
1357 AND c.right_parenthesis_code IS NOT NULL;
1358
1359 IF l_max_right_seq < l_max_left_seq then
1360 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_CON_UNEQUAL_PAR') ;
1361 FND_MSG_PUB.ADD;
1362 x_message_name := 'INL_ERR_CON_UNEQUAL_PAR';
1363 l_return := 'FALSE';
1364 END IF;
1365 END IF;
1366
1367 IF l_return = 'TRUE' THEN
1368 SELECT NVL(min(user_sequence),0)
1369 INTO l_min_left_seq
1370 FROM inl_conditions c
1371 WHERE c.rule_id = p_rule_id
1372 AND c.left_parenthesis_code IS NOT NULL;
1373
1374 SELECT NVL(min(user_sequence),0)
1375 INTO l_min_right_seq
1376 FROM inl_conditions c
1377 WHERE c.rule_id = p_rule_id
1378 AND c.right_parenthesis_code IS NOT NULL;
1379
1380 IF l_min_right_seq < l_min_left_seq then
1381 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_CON_UNEQUAL_PAR') ;
1382 FND_MSG_PUB.ADD;
1383 x_message_name := 'INL_ERR_CON_UNEQUAL_PAR';
1384 l_return := 'FALSE';
1385 END IF;
1386 END IF;
1387
1388 l_debug_info := 'Validate incorrect positioning of left and right parentheses.';
1389 INL_LOGGING_PVT.Log_Statement(p_module_name => g_module_name,
1390 p_procedure_name => l_api_name,
1391 p_debug_info => l_debug_info);
1392
1393 IF l_return = 'TRUE' THEN
1394 OPEN c_precedence_parenthesis(p_rule_id);
1395 FETCH c_precedence_parenthesis BULK COLLECT INTO l_precedence_parenthesis;
1396 CLOSE c_precedence_parenthesis;
1397
1398 IF NVL(l_precedence_parenthesis.LAST, 0) > 0 THEN
1399 FOR i IN NVL(l_precedence_parenthesis.FIRST, 0)..NVL(l_precedence_parenthesis.LAST, 0)
1400 LOOP
1401 IF l_precedence_parenthesis(i).right_parenthesis_code IS NOT NULL THEN
1402 l_precedence_left := 'N';
1403 FOR j IN NVL(l_precedence_parenthesis.FIRST, 0)..i
1404 LOOP
1405 IF l_precedence_parenthesis(j).left_parenthesis_code IS NOT NULL AND
1406 l_precedence_parenthesis(j).right_parenthesis_close IS NULL THEN
1407 l_precedence_parenthesis(j).right_parenthesis_close := i;
1408 l_precedence_left := 'Y';
1409 EXIT;
1410 END IF;
1411 END LOOP;
1412 IF l_precedence_left = 'N' THEN
1413 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_INCORRECT_PAR_POSN') ;
1414 FND_MSG_PUB.ADD;
1415 x_message_name := 'INL_ERR_INCORRECT_PAR_POSN';
1416 l_return := 'FALSE';
1417 EXIT;
1418 END IF;
1419 END IF;
1420 END LOOP;
1421 END IF;
1422 END IF;
1423
1424 l_debug_info := 'Validate logical operators.';
1425 INL_LOGGING_PVT.Log_Statement (p_module_name => g_module_name,
1426 p_procedure_name => l_api_name,
1427 p_debug_info => l_debug_info);
1428
1429 -- Check if condition has wrong number of logical operators
1430 IF l_return = 'TRUE' THEN
1431 SELECT max(user_sequence)
1432 INTO l_desc_user_sequence
1433 FROM inl_conditions c
1434 WHERE c.rule_id = p_rule_id;
1435
1436 -- Check if last row has logical operator
1437 SELECT count(1)
1438 INTO l_last_row_with_logical_oper
1439 FROM inl_conditions c
1440 WHERE c.rule_id = p_rule_id
1441 AND c.user_sequence = l_desc_user_sequence
1442 AND c.logical_operator_code IS NOT NULL;
1443
1444 IF NVL(l_last_row_with_logical_oper,0) > 0 THEN
1445 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_CON_UNEQUAL_OPRTR') ;
1446 FND_MSG_PUB.ADD;
1447 x_message_name := 'INL_ERR_CON_UNEQUAL_OPRTR';
1448 l_return := 'FALSE';
1449 END IF;
1450 END IF;
1451
1452 -- Get all rows which are not the last row or rows with just left parenthesis
1453 -- and have no logical operator
1454 IF l_return = 'TRUE' THEN
1455 --
1456 -- Get all rows which are not the last row or rows with just left parenthesis
1457 -- and have no logical operator
1458 --
1459 OPEN c_no_logical_operator(l_desc_user_sequence);
1460 LOOP
1461 FETCH c_no_logical_operator
1462 INTO l_no_logical_operator;
1463 EXIT WHEN c_no_logical_operator%notfound OR l_return = 'FALSE';
1464
1465 -- Check if next row has only right parenthesis
1466 OPEN c_only_right_parenthesis;
1467 FETCH c_only_right_parenthesis
1468 INTO l_exist;
1469
1470 IF c_only_right_parenthesis%found THEN
1471 x_message_name := NULL;
1472 l_return := 'TRUE';
1473 ELSE
1474 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_CON_UNEQUAL_OPRTR') ;
1475 FND_MSG_PUB.ADD;
1476 x_message_name := 'INL_ERR_CON_UNEQUAL_OPRTR';
1477 l_return := 'FALSE';
1478 END IF;
1479 CLOSE c_only_right_parenthesis;
1480 END LOOP;
1481 CLOSE c_no_logical_operator;
1482 END IF;
1483
1484
1485 -- Validate Constant character/Expression
1486 OPEN c_right_attrs(p_rule_id);
1487 FETCH c_right_attrs BULK COLLECT INTO l_right_attrs;
1488 CLOSE c_right_attrs;
1489
1490 IF NVL(l_right_attrs.LAST, 0) > 0 THEN
1491 FOR i IN NVL(l_right_attrs.FIRST, 0)..NVL(l_right_attrs.LAST, 0)
1492 LOOP
1493 IF l_right_attrs(i).right_entity_code IN ('CONS_CHAR', 'EXPR') THEN
1494 IF l_right_attrs(i).right_entity_code = 'CONS_CHAR' THEN
1495 l_right_attr_exp := l_right_attrs(i).attribute_constant_character;
1496 ELSIF l_right_attrs(i).right_entity_code = 'EXPR' THEN
1497 l_right_attr_exp := l_right_attrs(i).attribute_expression;
1498 END IF;
1499
1500 IF l_return = 'TRUE' AND l_right_attrs(i).right_entity_code = 'EXPR' THEN -- Validate db function
1501 -- Validate if the function is valid
1502 l_db_function := NVL(SUBSTR(l_right_attr_exp,1,INSTR(l_right_attr_exp, '(')-1),l_right_attr_exp);
1503
1504 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1505 p_procedure_name => l_api_name,
1506 p_var_name => 'l_db_function: ',
1507 p_var_value => l_db_function);
1508
1509 SELECT COUNT(1)
1510 INTO l_valid_function
1511 FROM user_objects
1512 WHERE object_name = l_db_function
1513 AND object_type = 'FUNCTION';
1514
1515 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1516 p_procedure_name => l_api_name,
1517 p_var_name => 'l_valid_function: ',
1518 p_var_value => l_valid_function);
1519
1520 IF l_return = 'TRUE' AND NVL(l_valid_function,0) = 0 THEN
1521 l_return := 'FALSE';
1522 FND_MESSAGE.SET_NAME('INL', 'INL_ERR_EXPRESSION') ;
1523 FND_MESSAGE.SET_TOKEN('SEQ_NUM', l_right_attrs(i).user_sequence);
1524 FND_MSG_PUB.ADD;
1525 x_message_name := 'INL_ERR_EXPRESSION';
1526 END IF;
1527 END IF; -- End Validate db function
1528
1529 IF l_return = 'TRUE' THEN
1530 FOR i IN 0..NVL(LENGTH(l_right_attr_exp),0) LOOP
1531 IF SUBSTR(l_right_attr_exp, i,1) = '''' THEN
1532 l_valid_aspas := NVL(l_valid_aspas,0) +1;
1533 END IF;
1534 END LOOP;
1535 END IF;
1536
1537 IF l_return = 'TRUE' THEN -- Validate char ''
1538 IF MOD(NVL(l_valid_aspas,0),2) <> 0 THEN
1539 l_return := 'FALSE';
1540 FND_MESSAGE.SET_NAME('INL', 'INL_INVALID_CHAR') ;
1541 FND_MESSAGE.SET_TOKEN('SEQ_NUM', l_right_attrs(i).user_sequence);
1542 FND_MSG_PUB.ADD;
1543 x_message_name := 'INL_INVALID_CHAR';
1544 END IF;
1545 END IF;
1546 END IF;
1547 END LOOP;
1548 END IF;
1549
1550 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1551 p_procedure_name => l_api_name,
1552 p_var_name => 'l_return',
1553 p_var_value => l_return);
1554
1555 -- Standard call to get message count and if count is 1, get message info.
1556 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1557 p_count => x_msg_count,
1558 p_data => x_msg_data);
1559
1560 -- Standard End of Procedure/Function Logging
1561 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
1562 p_procedure_name => l_api_name);
1563
1564 RETURN l_return;
1565
1566 EXCEPTION
1567 WHEN L_FND_EXC_ERROR THEN
1568 -- Standard Expected Error Logging
1569 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
1570 p_procedure_name => l_api_name);
1571 x_return_status := L_FND_RET_STS_ERROR;
1572 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1573 p_count => x_msg_count,
1574 p_data => x_msg_data);
1575 RETURN 'FALSE';
1576 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
1577 -- Standard Unexpected Error Logging
1578 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
1579 p_procedure_name => l_api_name) ;
1580 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1581 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1582 p_count => x_msg_count,
1583 p_data => x_msg_data);
1584 RETURN 'FALSE';
1585 WHEN OTHERS THEN
1586 -- Standard Unexpected Error Logging
1587 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
1588 p_procedure_name => l_api_name) ;
1589 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1590 IF FND_MSG_PUB.Check_Msg_Level (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1591 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
1592 p_procedure_name => l_api_name) ;
1593 END IF;
1594 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1595 p_count => x_msg_count,
1596 p_data => x_msg_data);
1597 RETURN 'FALSE';
1598 END Validate_Rules;
1599
1600 -- Utility name : Drop_ConditionPackage
1601 -- Type : Private
1602 -- Function : This routine is called by the UI when rule is deleted.
1603 -- It drops the package created for Rule
1604 -- Pre-reqs : None
1605 -- Parameters :
1606 -- IN : p_api_version IN NUMBER,
1607 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1608 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1609 -- p_rule_id IN NUMBER
1610 --
1611 -- OUT x_return_status OUT NOCOPY VARCHAR2
1612 -- x_msg_count OUT NOCOPY NUMBER
1613 -- x_msg_data OUT NOCOPY VARCHAR2
1614 --
1615 -- Version : Current version 1.0
1616 --
1617 -- Notes :
1618 PROCEDURE Drop_ConditionPackage(p_api_version IN NUMBER,
1619 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1620 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1621 p_rule_id IN NUMBER,
1622 x_return_status OUT NOCOPY VARCHAR2,
1623 x_msg_count OUT NOCOPY NUMBER,
1624 x_msg_data OUT NOCOPY VARCHAR2)IS
1625
1626 l_api_name CONSTANT VARCHAR2(30) := 'Drop_ConditionPackage';
1627 l_api_version CONSTANT NUMBER := 1.0;
1628 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1629
1630 l_exist_package_spec NUMBER;
1631 l_exist_package_body NUMBER;
1632 l_statement VARCHAR2(200);
1633 l_package_name VARCHAR2(100);
1634
1635 BEGIN
1636 -- Standard Beginning of Procedure/Function Logging
1637 INL_LOGGING_PVT.Log_BeginProc(p_module_name => g_module_name,
1638 p_procedure_name => l_api_name) ;
1639
1640 -- Initialize message list if p_init_msg_list is set to TRUE.
1641 IF FND_API.to_Boolean(p_init_msg_list) THEN
1642 FND_MSG_PUB.initialize;
1643 END IF;
1644
1645 -- Standard call to check for call compatibility
1646 IF NOT FND_API.Compatible_API_Call(p_current_version_number => l_api_version,
1647 p_caller_version_number => p_api_version,
1648 p_api_name => l_api_name,
1649 p_pkg_name => g_pkg_name ) THEN
1650 RAISE L_FND_EXC_UNEXPECTED_ERROR;
1651 END IF;
1652
1653 INL_LOGGING_PVT.Log_Variable(p_module_name => g_module_name,
1654 p_procedure_name => l_api_name,
1655 p_var_name => 'p_rule_id: ',
1656 p_var_value => p_rule_id);
1657
1658 -- Initialize API return status to success
1659 x_return_status := FND_API.G_RET_STS_SUCCESS;
1660
1661 SELECT package_name
1662 INTO l_package_name
1663 FROM inl_rules_vl
1664 WHERE rule_id = p_rule_id;
1665
1666 IF l_package_name IS NOT NULL AND l_package_name LIKE 'INL_RULE_%' || p_rule_id THEN
1667
1668 SELECT COUNT(1)
1669 INTO l_exist_package_spec
1670 FROM user_objects
1671 WHERE object_name = UPPER(l_package_name)
1672 AND object_type = 'PACKAGE'
1673 AND rownum = 1;
1674
1675 IF NVL(l_exist_package_spec,0) >0 THEN
1676 -- drop package specification
1677 --
1678 l_statement := 'DROP PACKAGE '||l_package_name;
1679
1680 EXECUTE IMMEDIATE l_statement;
1681 END IF;
1682
1683 SELECT COUNT(1)
1684 INTO l_exist_package_body
1685 FROM user_objects
1686 WHERE object_name = UPPER(l_package_name)
1687 AND object_type = 'PACKAGE BODY'
1688 AND rownum = 1;
1689
1690 IF NVL(l_exist_package_body,0) >0 THEN
1691 -- drop package body
1692 --
1693 l_statement := 'DROP PACKAGE BODY '||l_package_name ;
1694
1695 EXECUTE IMMEDIATE l_statement;
1696 END IF;
1697 END IF;
1698
1699 -- Standard call to get message count and if count is 1, get message info.
1700 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1701 p_count => x_msg_count,
1702 p_data => x_msg_data);
1703
1704 -- Standard End of Procedure/Function Logging
1705 INL_LOGGING_PVT.Log_EndProc(p_module_name => g_module_name,
1706 p_procedure_name => l_api_name);
1707
1708 EXCEPTION
1709 WHEN L_FND_EXC_ERROR THEN
1710 -- Standard Expected Error Logging
1711 INL_LOGGING_PVT.Log_ExpecError(p_module_name => g_module_name,
1712 p_procedure_name => l_api_name);
1713 x_return_status := L_FND_RET_STS_ERROR;
1714 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1715 p_count => x_msg_count,
1716 p_data => x_msg_data);
1717 WHEN L_FND_EXC_UNEXPECTED_ERROR THEN
1718 -- Standard Unexpected Error Logging
1719 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
1720 p_procedure_name => l_api_name) ;
1721 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1722 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1723 p_count => x_msg_count,
1724 p_data => x_msg_data);
1725 WHEN OTHERS THEN
1726 -- Standard Unexpected Error Logging
1727 INL_LOGGING_PVT.Log_UnexpecError(p_module_name => g_module_name,
1728 p_procedure_name => l_api_name) ;
1729 x_return_status := L_FND_RET_STS_UNEXP_ERROR;
1730 IF FND_MSG_PUB.Check_Msg_Level (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1731 FND_MSG_PUB.Add_Exc_Msg(p_pkg_name => g_pkg_name,
1732 p_procedure_name => l_api_name) ;
1733 END IF;
1734 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1735 p_count => x_msg_count,
1736 p_data => x_msg_data);
1737 END Drop_ConditionPackage;
1738
1739 END INL_RULE_PVT;