[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_RULE_ENGINE_PVT
Source
1 PACKAGE BODY AHL_MC_RULE_ENGINE_PVT AS
2 /* $Header: AHLVRUEB.pls 120.4 2007/12/21 13:37:18 sathapli ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Ahl_MC_Rule_Engine_Pvt';
4
5 --
6 PROCEDURE Evaluate_Rule_Stmt (
7 p_item_instance_id IN NUMBER,
8 p_rule_stmt_id IN NUMBER,
9 x_eval_result OUT NOCOPY VARCHAR2,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_msg_data OUT NOCOPY VARCHAR2);
13
14 ------------------------
15 -- Declare Procedures --
16 ------------------------
17 -- Start of Comments --
18 -- Procedure name : Check_Rules_For_Unit
19 -- Type : Private
20 -- Function : Checks rule completeness for unit
21 -- Pre-reqs :
22 -- Parameters :
23 --
24 -- Check_Rules_For_Unit Parameters:
25 -- p_unit_header_id IN NUMBER Required.
26 -- p_check_subconfig_flag IN VARCHAR2, T/F whether to check
27 -- subconfig rules
28 --
29 -- End of Comments.
30
31 PROCEDURE Check_Rules_For_Unit (
32 p_api_version IN NUMBER,
33 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
34 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38 p_unit_header_id IN NUMBER,
39 p_rule_type IN VARCHAR2,
40 p_check_subconfig_flag IN VARCHAR2 := FND_API.G_TRUE,
41 x_evaluation_status OUT NOCOPY VARCHAR2)
42 IS
43 --
44 CURSOR get_rules_for_unit_csr(p_uc_header_id IN NUMBER, p_rtype IN VARCHAR2) IS
45 SELECT DISTINCT rules.rule_id, uc.csi_item_instance_id
46 FROM AHL_MC_RULES_B rules, AHL_UNIT_CONFIG_HEADERS uc
47 WHERE rules.mc_header_id = uc.master_config_id
48 AND uc.unit_config_header_id = p_uc_header_id
49 AND TRUNC(nvl(uc.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
50 AND TRUNC(nvl(uc.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
51 AND rules.rule_type_code = p_rtype
52 AND TRUNC(nvl(rules.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
53 AND TRUNC(nvl(rules.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
54 --
55 CURSOR get_all_subunits_csr(p_uc_header_id IN NUMBER) IS
56 SELECT uc.unit_config_header_id
57 FROM AHL_UNIT_CONFIG_HEADERS uc
58 START WITH uc.unit_config_header_id = p_uc_header_id
59 AND TRUNC(nvl(uc.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
60 AND TRUNC(nvl(uc.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
61 CONNECT BY PRIOR uc.unit_config_header_id = uc.PARENT_UC_HEADER_ID
62 AND TRUNC(nvl(uc.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
63 AND TRUNC(nvl(uc.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
64 --
65 l_api_version CONSTANT NUMBER := 1.0;
66 l_api_name CONSTANT VARCHAR2(30) := 'Check_Rules_For_Unit';
67 l_csi_ii_id NUMBER;
68 l_rule_id NUMBER;
69 l_eval_result VARCHAR2(1);
70 l_all_true_flag BOOLEAN;
71 l_uc_header_id NUMBER;
72
73 --
74 BEGIN
75
76 -- Initialize Procedure return status to success
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78 -- Standard call to check for call compatibility
79 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
80 G_PKG_NAME) THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END IF;
83 -- Initialize message list if p_init_msg_list is set to TRUE
84 IF FND_API.To_Boolean(p_init_msg_list) THEN
85 FND_MSG_PUB.Initialize;
86 END IF;
87
88 x_evaluation_status := 'U';
89 l_all_true_flag := True;
90
91 --If NOT check sub configs
92 IF (p_check_subconfig_flag <> FND_API.G_TRUE) THEN
93 OPEN get_rules_for_unit_csr(p_unit_header_id, p_rule_type);
94 LOOP
95 FETCH get_rules_for_unit_csr into l_rule_id, l_csi_ii_id;
96 EXIT WHEN get_rules_for_unit_csr%NOTFOUND;
97
98 --Call rule evaluation
99 Evaluate_Rule (p_api_version => 1.0,
100 p_init_msg_list => p_init_msg_list,
101 p_validation_level => p_validation_level,
102 p_item_instance_id => l_csi_ii_id,
103 p_rule_id => l_rule_id,
104 x_eval_result => l_eval_result,
105 x_return_status => x_return_status,
106 x_msg_count => x_msg_count,
107 x_msg_data => x_msg_data);
108
109 IF (l_eval_result <> 'T') THEN
110 l_all_true_flag := False;
111 END IF;
112 IF (l_eval_result ='F') THEN
113 x_evaluation_status := 'F';
114 EXIT; --Quit after the 1st false
115 END IF;
116 END LOOP;
117 CLOSE get_rules_for_unit_csr;
118
119 ELSE --Evaluate subconfig rules as well.
120
121 OPEN get_all_subunits_csr(p_unit_header_id);
122 LOOP
123 FETCH get_all_subunits_csr into l_uc_header_id;
124 EXIT WHEN get_all_subunits_csr%NOTFOUND;
125 EXIT WHEN x_evaluation_status = 'F';
126
127 OPEN get_rules_for_unit_csr(l_uc_header_id, p_rule_type);
128 LOOP
129 FETCH get_rules_for_unit_csr into l_rule_id, l_csi_ii_id;
130 EXIT WHEN get_rules_for_unit_csr%NOTFOUND;
131
132 --Call rule evaluation
133 Evaluate_Rule (p_api_version => 1.0,
134 p_init_msg_list => p_init_msg_list,
135 p_validation_level => p_validation_level,
136 p_item_instance_id => l_csi_ii_id,
137 p_rule_id => l_rule_id,
138 x_eval_result => l_eval_result,
139 x_return_status => x_return_status,
140 x_msg_count => x_msg_count,
141 x_msg_data => x_msg_data);
142
143 IF (l_eval_result <> 'T') THEN
144 l_all_true_flag := False;
145 END IF;
146 IF (l_eval_result ='F') THEN
147 x_evaluation_status := 'F';
148 EXIT;
149 END IF;
150 END LOOP;
151 CLOSE get_rules_for_unit_csr;
152 END LOOP;
153 CLOSE get_all_subunits_csr;
154 END IF;
155
156 --If every rule evaluates to True.
157 IF (l_all_true_flag ) THEN
158 x_evaluation_status := 'T';
159 END IF;
160
161 -- Standard call to get message count and if count is 1, get message info
162 FND_MSG_PUB.Count_And_Get
163 ( p_count => x_msg_count,
164 p_data => x_msg_data,
165 p_encoded => fnd_api.g_false
166 );
167 EXCEPTION
168 WHEN FND_API.G_EXC_ERROR THEN
169 x_return_status := FND_API.G_RET_STS_ERROR;
170 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
171 p_data => x_msg_data,
172 p_encoded => fnd_api.g_false);
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
176 p_data => x_msg_data,
177 p_encoded => fnd_api.g_false);
178 WHEN OTHERS THEN
179 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
180 p_data => x_msg_data,
181 p_encoded => fnd_api.g_false);
182 END Check_Rules_For_Unit;
183
184 ------------------------
185 -- Start of Comments --
186 -- Procedure name : Validate_Rules_For_Unit
187 -- Type : Private
188 -- Function : Validate all rule completeness for unit
189 -- Pre-reqs :
190 -- Parameters :
191 --
192 -- Validate_Rules_For_Unit Parameters:
193 -- p_unit_header_id IN NUMBER Required.
194 -- p_check_subconfig_flag IN VARCHAR2, T/F whether to check
195 -- subconfig rules
196 --
197 -- End of Comments.
198
199 PROCEDURE Validate_Rules_For_Unit (
200 p_api_version IN NUMBER,
201 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
202 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2,
206 p_unit_header_id IN NUMBER,
207 p_rule_type IN VARCHAR2,
208 p_check_subconfig_flag IN VARCHAR2 := FND_API.G_TRUE,
209 p_x_error_tbl IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type,
210 x_evaluation_status OUT NOCOPY VARCHAR2)
211 IS
212 --
213 CURSOR get_rules_for_unit_csr(p_uc_header_id IN NUMBER, p_rtype IN VARCHAR2) IS
214 SELECT DISTINCT rules.rule_id, rules.rule_name, uc.csi_item_instance_id, uc.name
215 FROM AHL_MC_RULES_B rules, AHL_UNIT_CONFIG_HEADERS uc
216 WHERE rules.mc_header_id = uc.master_config_id
217 AND uc.unit_config_header_id = p_uc_header_id
218 AND TRUNC(nvl(uc.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
219 AND TRUNC(nvl(uc.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
220 AND rules.rule_type_code = p_rtype
221 AND TRUNC(nvl(rules.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
222 AND TRUNC(nvl(rules.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
223 --
224 CURSOR get_all_subunits_csr(p_uc_header_id IN NUMBER) IS
225 SELECT uc.unit_config_header_id
226 FROM AHL_UNIT_CONFIG_HEADERS uc
227 START WITH uc.unit_config_header_id = p_uc_header_id
228 AND TRUNC(nvl(uc.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
229 AND TRUNC(nvl(uc.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
230 CONNECT BY PRIOR uc.unit_config_header_id = uc.PARENT_UC_HEADER_ID
231 AND TRUNC(nvl(uc.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
232 AND TRUNC(nvl(uc.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
233 --
234 l_api_version CONSTANT NUMBER := 1.0;
235 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Rules_For_Unit';
236 l_csi_ii_id NUMBER;
237 l_rule_id NUMBER;
238 l_rule_name AHL_MC_RULES_B.RULE_NAME%TYPE;
239 l_uc_name AHL_UNIT_CONFIG_HEADERS.NAME%TYPE;
240 l_eval_result VARCHAR2(1);
241 l_all_true_flag BOOLEAN;
242 l_uc_header_id NUMBER;
243 --
244 BEGIN
245
246 -- Initialize Procedure return status to success
247 x_return_status := FND_API.G_RET_STS_SUCCESS;
248 -- Standard call to check for call compatibility
249 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
250 G_PKG_NAME) THEN
251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252 END IF;
253 -- Initialize message list if p_init_msg_list is set to TRUE
254 IF FND_API.To_Boolean(p_init_msg_list) THEN
255 FND_MSG_PUB.Initialize;
256 END IF;
257
258 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
259 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
260 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
261 'At the start of the procedure');
262 END IF;
263
264 x_evaluation_status := 'U';
265 l_all_true_flag := True;
266
267 --If NOT check sub configs
268 IF (p_check_subconfig_flag <> FND_API.G_TRUE) THEN
269 OPEN get_rules_for_unit_csr(p_unit_header_id, p_rule_type);
270 LOOP
271 FETCH get_rules_for_unit_csr into l_rule_id, l_rule_name, l_csi_ii_id, l_uc_name;
272 EXIT WHEN get_rules_for_unit_csr%NOTFOUND;
273
274 --Call rule evaluation
275 Evaluate_Rule (p_api_version => 1.0,
276 p_init_msg_list => p_init_msg_list,
277 p_validation_level => p_validation_level,
278 p_item_instance_id => l_csi_ii_id,
279 p_rule_id => l_rule_id,
280 x_eval_result => l_eval_result,
281 x_return_status => x_return_status,
282 x_msg_count => x_msg_count,
283 x_msg_data => x_msg_data);
284
285 --Rule failed, add error message
286 IF (l_eval_result ='F') THEN
287 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_FAILED');
288 FND_MESSAGE.Set_Token('RULE_NAME',l_rule_name);
289 FND_MESSAGE.Set_Token('UNIT_NAME',l_uc_name);
290 IF (p_x_error_tbl.COUNT >0) THEN
291 p_x_error_tbl(p_x_error_tbl.LAST+1) := FND_MESSAGE.get;
292 ELSE
293 p_x_error_tbl(0) := FND_MESSAGE.get;
294 END IF;
295 x_evaluation_status := 'F';
296 END IF;
297 IF (l_eval_result <> 'T') THEN
298 l_all_true_flag := False;
299 END IF;
300 END LOOP;
301 CLOSE get_rules_for_unit_csr;
302
303 ELSE --Evaluate subconfig rules as well.
304
305 OPEN get_all_subunits_csr(p_unit_header_id);
306 LOOP
307 FETCH get_all_subunits_csr into l_uc_header_id;
308 EXIT WHEN get_all_subunits_csr%NOTFOUND;
309 OPEN get_rules_for_unit_csr(l_uc_header_id, p_rule_type);
310 LOOP
311 FETCH get_rules_for_unit_csr into l_rule_id, l_rule_name, l_csi_ii_id, l_uc_name;
312 EXIT WHEN get_rules_for_unit_csr%NOTFOUND;
313
314 --Call rule evaluation
315 Evaluate_Rule (p_api_version => 1.0,
316 p_init_msg_list => p_init_msg_list,
317 p_validation_level => p_validation_level,
318 p_item_instance_id => l_csi_ii_id,
319 p_rule_id => l_rule_id,
320 x_eval_result => l_eval_result,
321 x_return_status => x_return_status,
322 x_msg_count => x_msg_count,
323 x_msg_data => x_msg_data);
324
325 IF (l_eval_result ='F') THEN
326 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_FAILED');
327 FND_MESSAGE.Set_Token('RULE_NAME',l_rule_name);
328 FND_MESSAGE.Set_Token('UNIT_NAME',l_uc_name);
329 IF (p_x_error_tbl.COUNT >0) THEN
330 p_x_error_tbl(p_x_error_tbl.LAST+1) := FND_MESSAGE.get;
331 ELSE
332 p_x_error_tbl(0) := FND_MESSAGE.get;
333 END IF;
334 x_evaluation_status := 'F';
335 END IF;
336 IF (l_eval_result <> 'T') THEN
337 l_all_true_flag := False;
338 END IF;
339 END LOOP;
340 CLOSE get_rules_for_unit_csr;
341 END LOOP;
342 CLOSE get_all_subunits_csr;
343 END IF;
344
345 --If every rule evaluates to True.
346 IF (l_all_true_flag ) THEN
347 x_evaluation_status := 'T';
348 END IF;
349
350 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
351 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
352 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
353 ' p_x_error_tbl.COUNT => '||p_x_error_tbl.COUNT);
354 END IF;
355
356 IF (p_x_error_tbl.COUNT > 0) THEN
357 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
358 FOR i IN p_x_error_tbl.FIRST..p_x_error_tbl.LAST LOOP
359 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
360 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
361 ' p_x_error_tbl(i) => '||p_x_error_tbl(i));
362 END LOOP;
363 END IF;
364 END IF;
365
366 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
367 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
368 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
369 'At the end of the procedure');
370 END IF;
371
372 -- Standard call to get message count and if count is 1, get message info
373 FND_MSG_PUB.Count_And_Get
374 ( p_count => x_msg_count,
375 p_data => x_msg_data,
376 p_encoded => fnd_api.g_false
377 );
378 EXCEPTION
379 WHEN FND_API.G_EXC_ERROR THEN
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
382 p_data => x_msg_data,
383 p_encoded => fnd_api.g_false);
384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
387 p_data => x_msg_data,
388 p_encoded => fnd_api.g_false);
389 WHEN OTHERS THEN
390 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
391 p_data => x_msg_data,
392 p_encoded => fnd_api.g_false);
393 END Validate_Rules_For_Unit;
394
395 ------------------------
396 -- Start of Comments --
397 -- Procedure name : Validate_Rules_For_Position
398 -- Type : Private
399 -- Function : Validate rules for one position
400 -- Pre-reqs :
401 -- Parameters :
402 --
403 -- Validate_Rules_For_Position Parameters:
404 -- p_item_instance_id IN NUMBER Required.
405 --
406 -- End of Comments.
407
408 PROCEDURE Validate_Rules_For_Position (
409 p_api_version IN NUMBER,
410 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
411 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2,
415 p_item_instance_id IN NUMBER,
416 p_rule_type IN VARCHAR2,
417 p_x_error_tbl IN OUT NOCOPY AHL_UC_VALIDATION_PUB.Error_Tbl_Type,
418 x_evaluation_status OUT NOCOPY VARCHAR2)
419 IS
420 --
421 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
422 -- Relationship start date check should include SYSDATE too.
423 CURSOR get_csi_ids_csr (p_csi_instance_id IN NUMBER) IS
424 SELECT csi_ii.subject_id
425 FROM csi_ii_relationships csi_ii
426 START WITH csi_ii.object_id = p_csi_instance_id
427 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
428 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate-1)) <= TRUNC(sysdate)
429 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
430 CONNECT BY PRIOR csi_ii.subject_id = csi_ii.object_id
431 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
432 AND trunc(nvl(CSI_II.ACTIVE_START_DATE, sysdate-1)) <= trunc(sysdate)
433 AND trunc(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > trunc(sysdate);
434
435 --
436 CURSOR get_rules_for_position_csr(p_rule_type IN VARCHAR2) IS
437 SELECT distinct rul.rule_id, rul.rule_name, rul.mc_header_id
438 FROM AHL_MC_RULES_VL rul, AHL_MC_RULE_STATEMENTS rst,
439 AHL_APPLICABLE_INSTANCES ap
440 WHERE rst.rule_id = rul.rule_id
441 AND rul.rule_type_code = p_rule_type
442 AND ((rst.subject_type = 'POSITION'
443 AND rst.subject_id = ap.position_id )
444 OR ((rst.object_type = 'ITEM_AS_POSITION' OR
445 rst.object_type = 'CONFIG_AS_POSITION')
446 AND rst.object_id = ap.position_id));
447 --
448 --Find matching instances given instance id and header_id
449 --instance is either top node or a subnode, matching mc_header_id
450 CURSOR get_uc_header_csr (p_csi_instance_id IN NUMBER,
451 p_mc_header_id IN NUMBER) IS
452 SELECT uch.csi_item_instance_id
453 FROM ahl_unit_config_headers uch
454 WHERE uch.master_config_id = p_mc_header_id
455 AND uch.csi_item_instance_id = p_csi_instance_id
456 UNION ALL
457 SELECT csi_ii.object_id
458 FROM csi_ii_relationships csi_ii
459 WHERE csi_ii.object_id IN
460 (SELECT csi_item_instance_id
461 FROM ahl_unit_config_headers
462 --mpothuku added '='
463 WHERE trunc(nvl(active_start_date,sysdate-1)) <= trunc(sysdate)
464 --mpothuku End
465 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
466 AND master_config_id = p_mc_header_id)
467
468 -- SATHAPLI::Bug# 6351371, 21-Aug-2007
469 -- relationship start date check should include SYSDATE too
470 START WITH csi_ii.subject_id = p_csi_instance_id
471 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
472 -- AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
473 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
474 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
475 CONNECT BY csi_ii.subject_id = PRIOR csi_ii.object_id
476 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
477 -- AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
478 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
479 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
480 --
481 CURSOR get_uc_header_det_csr(p_csi_instance_id IN NUMBER) IS
482 SELECT uch.name
483 FROM ahl_unit_config_headers uch
484 WHERE uch.csi_item_instance_id = p_csi_instance_id;
485
486 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
487 -- Cursor to fetch the parent instance id for a given instance id.
488 CURSOR get_parent_instance_csr(p_csi_instance_id IN NUMBER) IS
489 SELECT object_id
490 FROM csi_ii_relationships
491 WHERE subject_id = p_csi_instance_id
492 AND TRUNC(nvl(ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
493 AND TRUNC(nvl(ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
494
495 --
496 l_api_version CONSTANT NUMBER := 1.0;
497 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Rules_For_Position';
498 l_rule_id NUMBER;
499 l_rule_name AHL_MC_RULES_B.RULE_NAME%TYPE;
500 l_uc_name AHL_UNIT_CONFIG_HEADERS.NAME%TYPE;
501 l_mc_header_id NUMBER;
502 l_csi_ii_id NUMBER;
503 l_eval_result VARCHAR2(1);
504 l_all_true_flag BOOLEAN;
505 l_csi_id NUMBER;
506 l_msg_count NUMBER;
507
508 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
509 l_parent_instance_id NUMBER;
510 l_item_instance_id NUMBER;
511
512 --
513 BEGIN
514
515
516 -- Initialize Procedure return status to success
517 x_return_status := FND_API.G_RET_STS_SUCCESS;
518 -- Standard call to check for call compatibility
519 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
520 G_PKG_NAME) THEN
521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
522 END IF;
523 -- Initialize message list if p_init_msg_list is set to TRUE
524 IF FND_API.To_Boolean(p_init_msg_list) THEN
525 FND_MSG_PUB.Initialize;
526 END IF;
527
528 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
529 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
530 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
531 'At the start of the procedure');
532 END IF;
533
534 x_evaluation_status := 'U';
535 l_all_true_flag := True;
536
537 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
538 -- Since the Position Quantity Rule is defined only at the parent level for a given position, executing the rules
539 -- at the position and below it will not lead to the evaluation of the quantity rule, even if defined.
540 -- So its decided on executing the rules from the parent position, instead of the position where installation is being done.
541 -- For this, fetch the parent instance id for p_item_instance_id, and then use it instead for further validations.
542 OPEN get_parent_instance_csr(p_item_instance_id);
543 FETCH get_parent_instance_csr INTO l_parent_instance_id;
544 CLOSE get_parent_instance_csr;
545
546 IF(l_parent_instance_id is not null) THEN
547 l_item_instance_id := l_parent_instance_id;
548 ELSE
549 l_item_instance_id := p_item_instance_id;
550 END IF;
551
552 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
553 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
554 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
555 ' l_item_instance_id => '||l_item_instance_id||', l_parent_instance_id => '||l_parent_instance_id);
556 END IF;
557
558 EXECUTE IMMEDIATE 'DELETE FROM AHL_APPLICABLE_INSTANCES';
559
560 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
561 -- Modify the reference of p_item_instance_id to l_item_instance_id.
562 AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Positions
563 (p_api_version => 1.0,
564 p_init_msg_list => p_init_msg_list,
565 p_validation_level => p_validation_level,
566 p_csi_item_instance_id => l_item_instance_id,
567 x_return_status => x_return_status,
568 x_msg_count => x_msg_count,
569 x_msg_data => x_msg_data);
570
571 -- Check return status.
572 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
573 RAISE FND_API.G_EXC_ERROR;
574 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
575 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
576 END IF;
577
578 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
579 -- Modify the reference of p_item_instance_id to l_item_instance_id.
580 OPEN get_csi_ids_csr(l_item_instance_id);
581 LOOP
582 FETCH get_csi_ids_csr into l_csi_id;
583 EXIT WHEN get_csi_ids_csr%NOTFOUND;
584 AHL_MC_PATH_POSITION_PVT.Map_Instance_To_Positions
585 (p_api_version => 1.0,
586 p_init_msg_list => p_init_msg_list,
587 p_validation_level => p_validation_level,
588 p_csi_item_instance_id => l_csi_id,
589 x_return_status => x_return_status,
590 x_msg_count => x_msg_count,
591 x_msg_data => x_msg_data);
592 -- Check return status.
593 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
594 RAISE FND_API.G_EXC_ERROR;
595 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
596 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
597 END IF;
598
599
600 END LOOP;
601 CLOSE get_csi_ids_csr;
602
603 --Fetch rules matching given position
604 OPEN get_rules_for_position_csr(p_rule_type);
605 LOOP
606 FETCH get_rules_for_position_csr into l_rule_id, l_rule_name, l_mc_header_id;
607 EXIT WHEN get_rules_for_position_csr%NOTFOUND;
608
609 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
610 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
611 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
612 ' p_item_instance_id => '||p_item_instance_id||
613 ' l_mc_header_id => '||l_mc_header_id);
614 END IF;
615
616 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
617 -- Modify the reference of p_item_instance_id to l_item_instance_id.
618 OPEN get_uc_header_csr(l_item_instance_id, l_mc_header_id);
619 FETCH get_uc_header_csr INTO l_csi_ii_id;
620
621 IF (get_uc_header_csr%FOUND) THEN
622
623 --Call rule evaluation
624 Evaluate_Rule (p_api_version => 1.0,
625 p_init_msg_list => p_init_msg_list,
626 p_validation_level => p_validation_level,
627 p_item_instance_id => l_csi_ii_id,
628 p_rule_id => l_rule_id,
629 x_eval_result => l_eval_result,
630 x_return_status => x_return_status,
631 x_msg_count => x_msg_count,
632 x_msg_data => x_msg_data);
633
634 --Rule failed, add error message
635 IF (l_eval_result ='F') THEN
636
637 OPEN get_uc_header_det_csr (l_csi_ii_id);
638 FETCH get_uc_header_det_csr INTO l_uc_name;
639 CLOSE get_uc_header_det_csr;
640 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_FAILED');
641 FND_MESSAGE.Set_Token('RULE_NAME',l_rule_name);
642 FND_MESSAGE.Set_Token('UNIT_NAME',l_uc_name);
643 IF (p_x_error_tbl.COUNT >0) THEN
644 p_x_error_tbl(p_x_error_tbl.LAST+1) := FND_MESSAGE.get;
645 ELSE
646 p_x_error_tbl(0) := FND_MESSAGE.get;
647 END IF;
648 x_evaluation_status := 'F';
649 END IF;
650 IF (l_eval_result <> 'T') THEN
651 l_all_true_flag := False;
652 END IF;
653
654 END IF; --get_uc_header_csr%FOUND
655 CLOSE get_uc_header_csr;
656
657 END LOOP;
658 CLOSE get_rules_for_position_csr;
659
660 --If every rule evaluates to True.
661 IF (l_all_true_flag) THEN
662 x_evaluation_status := 'T';
663 END IF;
664
665 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
666 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
667 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
668 ' p_x_error_tbl.COUNT => '||p_x_error_tbl.COUNT);
669 END IF;
670
671 IF (p_x_error_tbl.COUNT > 0) THEN
672 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
673 FOR i IN p_x_error_tbl.FIRST..p_x_error_tbl.LAST LOOP
674 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
675 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
676 ' p_x_error_tbl(i) => '||p_x_error_tbl(i));
677 END LOOP;
678 END IF;
679 END IF;
680
681 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
682 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
683 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
684 ' At the end of the procedure');
685 END IF;
686
687 -- Standard call to get message count and if count is 1, get message info
688 FND_MSG_PUB.Count_And_Get
689 ( p_count => x_msg_count,
690 p_data => x_msg_data,
691 p_encoded => fnd_api.g_false
692 );
693 EXCEPTION
694 WHEN FND_API.G_EXC_ERROR THEN
695 x_return_status := FND_API.G_RET_STS_ERROR;
696 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
697 p_data => x_msg_data,
698 p_encoded => fnd_api.g_false);
699 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
700 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
701 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
702 p_data => x_msg_data,
703 p_encoded => fnd_api.g_false);
704 WHEN OTHERS THEN
705 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
706 p_data => x_msg_data,
707 p_encoded => fnd_api.g_false);
708 END Validate_Rules_For_Position;
709
710 ------------------------
711 -- Start of Comments --
712 -- Procedure name : Evaluate_Rule
713 -- Type : Private
714 -- Function : Evaluate 1 rule against 1 starting position
715 -- Pre-reqs :
716 -- Parameters :
717 --
718 -- Evaludate_Rule Parameters:
719 -- p_item_instance_id IN NUMBER Required.
720 -- p_rule_id IN NUMBER Required. Rule to eval.
721 -- End of Comments.
722
723 PROCEDURE Evaluate_Rule (
724 p_api_version IN NUMBER,
725 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
726 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
727 x_return_status OUT NOCOPY VARCHAR2,
728 x_msg_count OUT NOCOPY NUMBER,
729 x_msg_data OUT NOCOPY VARCHAR2,
730 p_item_instance_id IN NUMBER,
731 p_rule_id IN NUMBER,
732 x_eval_result OUT NOCOPY VARCHAR2)
733 IS
734 --
735 CURSOR get_rule_stmt_id_csr(p_rule_id IN NUMBER) IS
736 SELECT rule_statement_id
737 FROM AHL_MC_RULE_STATEMENTS
738 WHERE rule_id = p_rule_id
739 AND top_rule_stmt_flag = 'T';
740 --
741 l_rule_stmt_id NUMBER;
742 l_api_version CONSTANT NUMBER := 1.0;
743 l_api_name CONSTANT VARCHAR2(30) := 'Evaluate_Rule';
744 --
745 BEGIN
746
747 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
748 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
749 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
750 ' At the start of the procedure');
751 END IF;
752
753 -- Initialize Procedure return status to success
754 x_return_status := FND_API.G_RET_STS_SUCCESS;
755 -- Standard call to check for call compatibility
756 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
757 G_PKG_NAME) THEN
758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759 END IF;
760 -- Initialize message list if p_init_msg_list is set to TRUE
761 IF FND_API.To_Boolean(p_init_msg_list) THEN
762 FND_MSG_PUB.Initialize;
763 END IF;
764
765 OPEN get_rule_stmt_id_csr(p_rule_id);
766 FETCH get_rule_stmt_id_csr into l_rule_stmt_id;
767 IF (get_rule_stmt_id_csr%NOTFOUND) THEN
768 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_ID_INV');
769 FND_MESSAGE.Set_Token('RULE_ID',p_rule_id);
770 FND_MSG_PUB.ADD;
771 CLOSE get_rule_stmt_id_csr;
772 Raise FND_API.G_EXC_ERROR;
773 END IF;
774 CLOSE get_rule_stmt_id_csr;
775
776 --Call recursive rule evaluation
777 Evaluate_Rule_Stmt(p_item_instance_id => p_item_instance_id,
778 p_rule_stmt_id => l_rule_stmt_id,
779 x_eval_result => x_eval_result,
780 x_return_status => x_return_status,
781 x_msg_count => x_msg_count,
782 x_msg_data => x_msg_data);
783
784 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
785 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
786 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
787 ' At the end of the procedure');
788 END IF;
789
790 -- Standard call to get message count and if count is 1, get message info
791 FND_MSG_PUB.Count_And_Get
792 ( p_count => x_msg_count,
793 p_data => x_msg_data,
794 p_encoded => fnd_api.g_false
795 );
796 EXCEPTION
797 WHEN FND_API.G_EXC_ERROR THEN
798 x_return_status := FND_API.G_RET_STS_ERROR;
799 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
800 p_data => x_msg_data,
801 p_encoded => fnd_api.g_false);
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
805 p_data => x_msg_data,
806 p_encoded => fnd_api.g_false);
807 WHEN OTHERS THEN
808 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
809 p_data => x_msg_data,
810 p_encoded => fnd_api.g_false);
811 END Evaluate_Rule;
812
813
814 PROCEDURE Evaluate_Rule_Stmt (
815 p_item_instance_id IN NUMBER,
816 p_rule_stmt_id IN NUMBER,
817 x_eval_result OUT NOCOPY VARCHAR2,
818 x_return_status OUT NOCOPY VARCHAR2,
819 x_msg_count OUT NOCOPY NUMBER,
820 x_msg_data OUT NOCOPY VARCHAR2)
821 IS
822 --
823 CURSOR get_rule_stmt_csr(p_rulestmt_id IN NUMBER) IS
824 SELECT *
825 FROM ahl_mc_rule_statements
826 WHERE rule_statement_id = p_rulestmt_id;
827 --
828 CURSOR get_inventory_item_csr (p_csi_ii_id IN NUMBER) IS
829 SELECT inventory_item_id
830 FROM CSI_ITEM_INSTANCES
831 WHERE instance_id = p_csi_ii_id;
832 --
833 CURSOR get_mc_ids_csr (p_csi_ii_id IN NUMBER) IS
834 SELECT hd.mc_id, hd.version_number
835 FROM AHL_MC_HEADERS_B hd, AHL_UNIT_CONFIG_HEADERS uc
836 WHERE hd.mc_header_id = uc.master_config_id
837 AND uc.csi_item_instance_id = p_csi_ii_id;
838 --
839 CURSOR check_same_inventory_item_csr (p_csi_ii_id1 IN NUMBER,
840 p_csi_ii_id2 IN NUMBER) IS
841 SELECT csi1.inventory_item_id
842 FROM CSI_ITEM_INSTANCES csi1, CSI_ITEM_INSTANCES csi2
843 WHERE csi1.instance_id = p_csi_ii_id1
844 AND csi2.instance_id = p_csi_ii_id2
845 AND csi1.inventory_item_id = csi2.inventory_item_id;
846 --
847 CURSOR check_same_mc_header_csr (p_csi_ii_id1 IN NUMBER,
848 p_csi_ii_id2 IN NUMBER) IS
849 SELECT uc1.master_config_id
850 FROM AHL_UNIT_CONFIG_HEADERS uc1, AHL_UNIT_CONFIG_HEADERS uc2
851 WHERE uc1.csi_item_instance_id = p_csi_ii_id1
852 AND uc2.csi_item_instance_id = p_csi_ii_id2
853 AND uc1.master_config_id = uc2.master_config_id;
854
855 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
856 -- Defined the following new cursors: -
857
858 -- Cursor to get the distinct UOM count of all the non-extra children of an instance.
859 CURSOR check_child_inst_uoms_csr(p_csi_instance_id IN NUMBER) IS
860 SELECT count(distinct unit_of_measure)
861 FROM csi_ii_relationships csi_ii,
862 csi_item_instances csi
863 WHERE csi_ii.object_id = p_csi_instance_id
864 AND csi_ii.subject_id = csi.instance_id
865 AND csi_ii.position_reference is not null
866 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
867 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
868 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
869
870 -- Cursor to get the sum of quantities of all the non-extra children of an instance.
871 CURSOR get_tot_child_quant_csr(p_csi_instance_id IN NUMBER) IS
872 SELECT nvl(sum(quantity),0) quantity
873 FROM csi_ii_relationships csi_ii,
874 csi_item_instances csi
875 WHERE csi_ii.object_id = p_csi_instance_id
876 AND csi_ii.subject_id = csi.instance_id
877 AND csi_ii.position_reference is not null
878 AND CSI_II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
879 AND TRUNC(nvl(CSI_II.ACTIVE_START_DATE, sysdate)) <= TRUNC(sysdate)
880 AND TRUNC(nvl(CSI_II.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
881
882 -- Cursor to get the instance number for a given instance id.
883 CURSOR get_instance_number_csr(p_csi_instance_id IN NUMBER) IS
884 SELECT instance_number from csi_item_instances
885 WHERE instance_id = p_csi_instance_id;
886
887 --
888 l_rstmt_rec get_rule_stmt_csr%ROWTYPE;
889 l_subj_result VARCHAR2(1);
890 l_obj_result VARCHAR2(1);
891 l_mapping_status VARCHAR2(30);
892 l_dummy_id NUMBER;
893 l_instance_id NUMBER;
894 l_msg_count NUMBER;
895 l_inv_item_id NUMBER;
896 l_mc_id NUMBER;
897 l_version_number NUMBER;
898 l_subj_instance_id NUMBER;
899 l_obj_instance_id NUMBER;
900 l_junk NUMBER;
901
902 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
903 l_tot_child_quant NUMBER;
904 l_uom_count NUMBER;
905 l_instance_number CSI_ITEM_INSTANCES.INSTANCE_NUMBER%TYPE;
906 --
907 BEGIN
908
909 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
910 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
911 'ahl.plsql.'||G_PKG_NAME||'.Evaluate_Rule_Stmt',
912 ' At the start of the procedure');
913 END IF;
914
915 --Fetch rule statement information
916 OPEN get_rule_stmt_csr(p_rule_stmt_id);
917 FETCH get_rule_stmt_csr into l_rstmt_rec;
918 IF (get_rule_stmt_csr%NOTFOUND) THEN
919 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_ID_INV');
920 FND_MESSAGE.Set_Token('RULE_STMT_ID',p_rule_stmt_id);
921 FND_MSG_PUB.ADD;
922 CLOSE get_rule_stmt_csr;
923 RAISE FND_API.G_EXC_ERROR;
924 END IF;
925 CLOSE get_rule_stmt_csr;
926
927 --Check the join operators
928 IF ((l_rstmt_rec.operator = 'OR') OR
929 (l_rstmt_rec.operator = 'AND') OR
930 (l_rstmt_rec.operator = 'IMPLIES') OR
931 (l_rstmt_rec.operator = 'REQUIRES'))
932 THEN
933 --Evaluate the subject_id rule statements
934 Evaluate_Rule_Stmt(p_item_instance_id => p_item_instance_id,
935 p_rule_stmt_id => l_rstmt_rec.subject_id,
936 x_eval_result => l_subj_result,
937 x_return_status => x_return_status,
938 x_msg_count => x_msg_count,
939 x_msg_data => x_msg_data);
940
941 --Saves object_id evaluation in certain situations
942 IF (l_rstmt_rec.operator = 'OR'
943 AND l_subj_result = 'T') THEN
944 x_eval_result := 'T';
945 ELSIF (l_rstmt_rec.operator = 'AND'
946 AND l_subj_result = 'F') THEN
947 x_eval_result := 'F';
948 ELSIF (l_rstmt_rec.operator = 'REQUIRES'
949 AND l_subj_result = 'U') THEN
950 x_eval_result := 'U';
951 ELSIF (l_rstmt_rec.operator = 'IMPLIES'
952 AND l_subj_result = 'F') THEN
953 x_eval_result := 'T';
954 ELSE
955
956 --Evaluate the object_id rule statements
957 Evaluate_Rule_Stmt(p_item_instance_id => p_item_instance_id,
958 p_rule_stmt_id => l_rstmt_rec.object_id,
959 x_eval_result => l_obj_result,
960 x_return_status => x_return_status,
961 x_msg_count => x_msg_count,
962 x_msg_data => x_msg_data);
963
964 --logical eval results
965 IF (l_rstmt_rec.operator = 'OR') THEN
966 IF (l_subj_result = 'T' OR l_obj_result ='T') THEN
967 x_eval_result := 'T';
968 ELSIF (l_subj_result = 'F' AND l_obj_result='F') THEN
969 x_eval_result := 'F';
970 ELSE
971 x_eval_result := 'U';
972 END IF;
973 ELSIF (l_rstmt_rec.operator = 'AND') THEN
974 IF (l_subj_result = 'F' OR l_obj_result ='F') THEN
975 x_eval_result := 'F';
976 ELSIF (l_subj_result = 'T' AND l_obj_result='T') THEN
977 x_eval_result := 'T';
978 ELSE
979 x_eval_result := 'U';
980 END IF;
981 ELSIF (l_rstmt_rec.operator = 'REQUIRES') THEN
982 IF (l_subj_result = 'U' OR l_obj_result ='U') THEN
983 x_eval_result := 'U';
984 ELSIF (l_subj_result = l_obj_result) THEN
985 x_eval_result := 'T';
986 ELSE
987 x_eval_result := 'F';
988 END IF;
989 ELSIF (l_rstmt_rec.operator = 'IMPLIES') THEN
990 IF (l_subj_result = 'F' OR l_obj_result = 'T' ) THEN
991 x_eval_result := 'T';
992 ELSIF (l_subj_result='T' AND l_obj_result ='F') THEN
993 x_eval_result := 'F';
994 ELSE
995 x_eval_result := 'U';
996 END IF;
997 END IF;
998 END IF; --Did not have to evaluate object code
999 --Now for the leaf rule statements
1000 ELSE
1001 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1002 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1003 'ahl.plsql.'||G_PKG_NAME||'.Evaluate_Rule_Stmt',
1004 ' evaluating leaf rule');
1005 END IF;
1006
1007 AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance (
1008 p_api_version => 1.0,
1009 p_position_id => l_rstmt_rec.subject_id,
1010 p_csi_item_instance_id => p_item_instance_id,
1011 x_item_instance_id => l_instance_id,
1012 x_lowest_uc_csi_id => l_dummy_id,
1013 x_mapping_status => l_mapping_status,
1014 x_return_status => x_return_status,
1015 x_msg_count => x_msg_count,
1016 x_msg_data => x_msg_data);
1017
1018 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1020 'ahl.plsql.'||G_PKG_NAME||'.Evaluate_Rule_Stmt',
1021 ' After calling AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance: '||
1022 ' p_position_id => '||l_rstmt_rec.subject_id||', p_item_instance_id => '||p_item_instance_id||
1023 ', x_item_instance_id => '||l_instance_id||', x_lowest_uc_csi_id => '||l_dummy_id);
1024 END IF;
1025
1026 -- Check return status.
1027 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1028 RAISE FND_API.G_EXC_ERROR;
1029 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1030 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1031 END IF;
1032
1033 --Reset the instance id when there is not a matched instance.
1034 IF (l_mapping_status <> 'MATCH') THEN
1035 l_instance_id:=null;
1036 END IF;
1037
1038 --If Installed, Either exists or not
1039 IF (l_rstmt_rec.operator = 'INSTALLED') THEN
1040 IF (l_instance_id IS NOT NULL) THEN
1041 x_eval_result := 'T';
1042 ELSE
1043 x_eval_result := 'F';
1044 END IF;
1045
1046 --If Have, must check existance of item or config for instance.
1047 ELSIF (l_rstmt_rec.operator = 'HAVE' OR
1048 l_rstmt_rec.operator = 'MUST_HAVE' ) THEN
1049 --If no installed instances
1050 IF (l_instance_id IS NULL) THEN
1051 IF (l_rstmt_rec.operator = 'MUST_HAVE') THEN
1052 x_eval_result := 'F';
1053 ELSE
1054 x_eval_result := 'U';
1055 END IF;
1056 ELSE
1057 l_subj_instance_id :=l_instance_id;
1058 --1 installed instance
1059 IF (l_rstmt_rec.object_type ='ITEM') THEN
1060 OPEN get_inventory_item_csr(l_subj_instance_id);
1061 FETCH get_inventory_item_csr into l_inv_item_id;
1062 CLOSE get_inventory_item_csr;
1063 IF (l_inv_item_id = l_rstmt_rec.object_id) THEN
1064 x_eval_result := 'T';
1065 ELSE
1066 x_eval_result := 'F';
1067 END IF;
1068 ELSIF (l_rstmt_rec.object_type = 'CONFIGURATION') THEN
1069 OPEN get_mc_ids_csr(l_subj_instance_id);
1070 FETCH get_mc_ids_csr into l_mc_id, l_version_number;
1071 CLOSE get_mc_ids_csr;
1072 IF (l_rstmt_rec.object_attribute1 IS NULL) THEN
1073 IF (l_mc_id = l_rstmt_rec.object_id) THEN
1074 x_eval_result := 'T';
1075 ELSE
1076 x_eval_result := 'F';
1077 END IF;
1078 ELSE
1079 IF (l_mc_id = l_rstmt_rec.object_id AND
1080 l_version_number=TO_NUMBER(l_rstmt_rec.object_attribute1))
1081 THEN
1082 x_eval_result := 'T';
1083 ELSE
1084 x_eval_result := 'F';
1085 END IF;
1086 END IF; --IF object_attribute1
1087
1088 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 04-Dec-2007
1089 -- Evaluate for the object_type TOT_CHILD_QUANTITY
1090 ELSIF (l_rstmt_rec.object_type = 'TOT_CHILD_QUANTITY') THEN
1091 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1092 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1093 'ahl.plsql.'||G_PKG_NAME||'.Evaluate_Rule_Stmt',
1094 ' evaluating position quantity rule for the instance: ' || l_subj_instance_id);
1095 END IF;
1096
1097 -- Check that all the child instances have the same UOM
1098 l_uom_count := 0;
1099 OPEN check_child_inst_uoms_csr(l_subj_instance_id);
1100 FETCH check_child_inst_uoms_csr into l_uom_count;
1101 IF (l_uom_count > 1) THEN
1102 -- This would mean there are more than one UOM corresponding to the child instances
1103 x_eval_result := 'F';
1104 OPEN get_instance_number_csr(l_subj_instance_id);
1105 FETCH get_instance_number_csr into l_instance_number;
1106 CLOSE get_instance_number_csr;
1107
1108 FND_MESSAGE.Set_Name('AHL', 'AHL_UC_QRUL_INST_UOM_DIF');
1109 FND_MESSAGE.Set_Token('INST_NUM',l_instance_number);
1110 FND_MSG_PUB.ADD;
1111 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1112 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1113 'ahl.plsql.'||G_PKG_NAME||'.Evaluate_Rule_Stmt',
1114 ' UOMs of the children of the instance: ' ||l_subj_instance_id || ' do not match');
1115 END IF;
1116 CLOSE check_child_inst_uoms_csr;
1117 x_return_status := FND_API.G_RET_STS_ERROR;
1118 RAISE FND_API.G_EXC_ERROR;
1119
1120 END IF;
1121 CLOSE check_child_inst_uoms_csr;
1122
1123 -- Validate the rule quantity against the total child quantity
1124 l_tot_child_quant := 0;
1125 OPEN get_tot_child_quant_csr(l_subj_instance_id);
1126 FETCH get_tot_child_quant_csr into l_tot_child_quant;
1127 CLOSE get_tot_child_quant_csr;
1128 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1129 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1130 'ahl.plsql.'||G_PKG_NAME||'.Evaluate_Rule_Stmt',
1131 ' computed total quantity of children ->' ||l_tot_child_quant || ' and rule quantity is ->' ||
1132 l_rstmt_rec.object_attribute1);
1133 END IF;
1134
1135 IF(l_tot_child_quant = NVL(TO_NUMBER(l_rstmt_rec.object_attribute1),-1)) THEN
1136 x_eval_result := 'T';
1137 ELSE
1138 x_eval_result := 'F';
1139 END IF;
1140 END IF; -- If ITEM or CONFIGURATION or TOT_CHILD_QUANTITY
1141 END IF; --If instance id is not null
1142
1143 --Same operator. Must store instance id as subject id
1144 --Evaluate object path position and compare the 2 instance ids
1145 ELSIF (l_rstmt_rec.operator = 'SAME') THEN
1146
1147 IF (l_instance_id IS NULL) THEN
1148 x_eval_result := 'U';
1149 ELSE
1150 l_subj_instance_id :=l_instance_id;
1151
1152 AHL_MC_PATH_POSITION_PVT.Get_Pos_Instance (
1153 p_api_version => 1.0,
1154 p_position_id => l_rstmt_rec.object_id,
1155 p_csi_item_instance_id => p_item_instance_id,
1156 x_item_instance_id => l_instance_id,
1157 x_lowest_uc_csi_id => l_dummy_id,
1158 x_mapping_status => l_mapping_status,
1159 x_return_status => x_return_status,
1160 x_msg_count => x_msg_count,
1161 x_msg_data => x_msg_data);
1162
1163 -- Check return status.
1164 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1165 RAISE FND_API.G_EXC_ERROR;
1166 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1168 END IF;
1169
1170 --Reset the instance id when there is not a matched instance.
1171 IF (l_mapping_status <> 'MATCH') THEN
1172 l_instance_id:=null;
1173 END IF;
1174 --Check object instance id
1175 IF (l_instance_id IS NULL) THEN
1176 x_eval_result := 'U';
1177 ELSE
1178 l_obj_instance_id :=l_instance_id;
1179
1180 IF (l_rstmt_rec.object_type = 'ITEM_AS_POSITION') THEN
1181 OPEN check_same_inventory_item_csr(l_subj_instance_id,
1182 l_obj_instance_id);
1183 FETCH check_same_inventory_item_csr into l_junk;
1184 IF (check_same_inventory_item_csr%NOTFOUND) THEN
1185 x_eval_result := 'F';
1186 ELSE
1187 x_eval_result := 'T';
1188 END IF;
1189 CLOSE check_same_inventory_item_csr;
1190 ELSIF (l_rstmt_rec.object_type = 'CONFIG_AS_POSITION') THEN
1191 OPEN check_same_mc_header_csr(l_subj_instance_id,
1192 l_obj_instance_id);
1193 FETCH check_same_mc_header_csr into l_junk;
1194 IF (check_same_mc_header_csr%NOTFOUND) THEN
1195 x_eval_result := 'F';
1196 ELSE
1197 x_eval_result := 'T';
1198 END IF;
1199 CLOSE check_same_mc_header_csr;
1200 END IF;
1201 END IF; --obj count =1
1202 END IF; --subj count =1
1203 END IF; --Leaf Operators
1204
1205 --Invert logical status based on negation_flag
1206 IF (l_rstmt_rec.negation_flag = 'T') THEN
1207 IF (x_eval_result = 'T') THEN
1208 x_eval_result := 'F';
1209 ELSIF (x_eval_result = 'F') THEN
1210 x_eval_result := 'T';
1211 END IF;
1212 END IF;
1213
1214 END IF; --Leaf/non-leafoperators
1215 --dbms_output.put_line('rule_id' || to_char(p_rule_stmt_id) ||'->'||x_eval_result);
1216
1217 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1218 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1219 'ahl.plsql.'||G_PKG_NAME||'.Evaluate_Rule_Stmt',
1220 ' At the end of the procedure');
1221 END IF;
1222
1223 END Evaluate_Rule_Stmt;
1224
1225
1226 End AHL_MC_RULE_ENGINE_PVT;