[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_RULE_PVT
Source
1 PACKAGE BODY AHL_MC_RULE_PVT AS
2 /* $Header: AHLVMCRB.pls 120.0 2005/05/26 01:16:13 appldev noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Ahl_MC_Rule_Pvt';
4
5
6 ------------------------
7 -- Declare Procedures --
8 ------------------------
9
10 --Helper Procedure used to build the rules table
11 PROCEDURE Build_UI_Rule_Stmt_Tbl (
12 p_rule_stmt_id IN NUMBER,
13 x_rule_stmt_tbl OUT NOCOPY AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type);
14
15 PROCEDURE Build_Rule_Stmt_Tbl (
16 p_start_index IN NUMBER,
17 p_end_index IN NUMBER,
18 p_rule_id IN NUMBER,
19 p_ui_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type,
20 x_rule_stmt_tbl OUT NOCOPY AHL_MC_RULE_PVT.Rule_Stmt_Tbl_Type,
21 x_rule_stmt_id OUT NOCOPY NUMBER);
22
23 -----------------
24 -- Start of Comments --
25 -- Procedure name : Load_Rule
26 -- Type : Private
27 -- Function : Builds the rule record and ui rule table for display purposes
28 -- Pre-reqs :
29 -- Parameters :
30 --
31 -- Load_Rule Parameters:
32 -- p_rule_id IN NUMBER Required
33 --
34 -- End of Comments.
35
36 PROCEDURE Load_Rule (
37 p_api_version IN NUMBER,
38 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
39 p_commit IN VARCHAR2 := FND_API.G_FALSE,
40 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2,
44 p_rule_id IN NUMBER,
45 x_rule_stmt_tbl OUT NOCOPY AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type)
46 IS
47 --
48 CURSOR ahl_rule_stmt_csr (p_rule_id IN NUMBER) IS
49 SELECT rule_statement_id
50 FROM ahl_mc_rule_statements stmt
51 WHERE rule_id = p_rule_id
52 AND top_rule_stmt_flag = 'T';
53 --
54 l_api_version CONSTANT NUMBER := 1.0;
55 l_api_name CONSTANT VARCHAR2(30) := 'Load_Rule';
56 l_rule_stmt_id NUMBER;
57 --
58 BEGIN
59 -- Standard start of API savepoint
60 SAVEPOINT Load_Rule_pvt;
61
62 -- Initialize Procedure return status to success
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64 -- Standard call to check for call compatibility
65 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
66 G_PKG_NAME) THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69
70 -- Initialize message list if p_init_msg_list is set to TRUE
71 IF FND_API.To_Boolean(p_init_msg_list) THEN
72 FND_MSG_PUB.Initialize;
73 END IF;
74
75 OPEN ahl_rule_stmt_csr(p_rule_id);
76 FETCH ahl_rule_stmt_csr INTO l_rule_stmt_id;
77
78 IF (ahl_rule_stmt_csr%NOTFOUND) THEN
79 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_ID_INV');
80 FND_MESSAGE.Set_Token('RULE_STMT_ID',l_rule_stmt_id);
81 FND_MSG_PUB.ADD;
82 CLOSE ahl_rule_stmt_csr;
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85 CLOSE ahl_rule_stmt_csr;
86
87 --Call the API to build the rule table
88 Build_UI_Rule_Stmt_Tbl (
89 p_rule_stmt_id => l_rule_stmt_id,
90 x_rule_stmt_tbl => x_rule_stmt_tbl);
91
92 --Set the sequence number
93 FOR i IN x_rule_stmt_tbl.FIRST..x_rule_stmt_tbl.LAST LOOP
94 x_rule_stmt_tbl(i).sequence_num := i*10;
95 END LOOP;
96
97 -- Standard call to get message count and if count is 1, get message info
98 FND_MSG_PUB.Count_And_Get
99 ( p_count => x_msg_count,
100 p_data => x_msg_data,
101 p_encoded => fnd_api.g_false
102 );
103 EXCEPTION
104 WHEN FND_API.G_EXC_ERROR THEN
105 Rollback to Load_Rule_pvt;
106 x_return_status := FND_API.G_RET_STS_ERROR;
107 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
108 p_data => x_msg_data,
109 p_encoded => fnd_api.g_false);
110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
111 Rollback to Load_Rule_pvt;
112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
113 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
114 p_data => x_msg_data,
115 p_encoded => fnd_api.g_false);
116 WHEN OTHERS THEN
117 Rollback to Load_Rule_pvt;
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
120 p_procedure_name => l_api_name,
121 p_error_text => SQLERRM);
122 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
123 p_data => x_msg_data,
124 p_encoded => fnd_api.g_false);
125
126 END Load_Rule;
127
128 ---
129
130 -------------------------------
131 -- Start of Comments --
132 -- Procedure name : Build_UI_Rule_Stmt_Tbl
133 -- Type : Private
134 -- Function : Helper method which builds the rule_stmt table based on rule_stmts.
135 -- Pre-reqs :
136 -- Parameters :
137 --
138 -- End of Comments.
139
140 PROCEDURE Build_UI_Rule_Stmt_Tbl (
141 p_rule_stmt_id IN NUMBER,
142 x_rule_stmt_tbl OUT NOCOPY AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type)
143 IS
144 --
145 CURSOR Check_rule_operator_csr (p_rulestmt_id IN NUMBER) IS
146 SELECT rs.operator, rs.subject_id, rs.object_id,
147 fnd.meaning, rs.object_version_number
148 FROM ahl_mc_rule_statements rs, fnd_lookups fnd
149 WHERE rs.rule_statement_id = p_rulestmt_id
150 AND rs.operator = fnd.lookup_code
151 AND fnd.lookup_type = 'AHL_MC_RULE_ALL_OPERATORS';
152 --
153 CURSOR Rule_stmt_csr (p_rulestmt_id IN NUMBER) IS
154 SELECT rs.RULE_STATEMENT_ID,
155 rs.subject_ID POSITION_ID,
156 AHL_MC_PATH_POSITION_PVT.get_posref_by_id(rs.subject_id, FND_API.G_FALSE) POSITION_MEANING,
157 rs.negation_flag,
158 rs.operator,
159 rs.object_version_number,
160 fnd.meaning operator_meaning,
161 rs.OBJECT_ID ,
162 rs.OBJECT_TYPE ,
163 rs.OBJECT_ATTRIBUTE1 ,
164 rs.OBJECT_ATTRIBUTE2 ,
165 rs.OBJECT_ATTRIBUTE3 ,
166 rs.OBJECT_ATTRIBUTE4 ,
167 rs.OBJECT_ATTRIBUTE5 ,
168 rs.ATTRIBUTE_CATEGORY ,
169 rs.ATTRIBUTE1 ,
170 rs.ATTRIBUTE2 ,
171 rs.ATTRIBUTE3 ,
172 rs.ATTRIBUTE4 ,
173 rs.ATTRIBUTE5 ,
174 rs.ATTRIBUTE6 ,
175 rs.ATTRIBUTE7 ,
176 rs.ATTRIBUTE8 ,
177 rs.ATTRIBUTE9 ,
178 rs.ATTRIBUTE10 ,
179 rs.ATTRIBUTE11 ,
180 rs.ATTRIBUTE12 ,
181 rs.ATTRIBUTE13 ,
182 rs.ATTRIBUTE14 ,
183 rs.ATTRIBUTE15
184 FROM ahl_mc_rule_statements rs, fnd_lookups fnd
185 WHERE rs.rule_statement_id = p_rulestmt_id
186 AND fnd.lookup_code = decode (rs.negation_flag, 'T', rs.operator||'_NOT', rs.operator)
187 AND fnd.lookup_type = 'AHL_MC_RULE_ALL_OPERATORS';
188 --
189 CURSOR get_object_type_csr (p_object_type IN VARCHAR2) IS
190 SELECT meaning
191 FROM fnd_lookups
192 WHERE lookup_code = p_object_type
193 AND lookup_type = 'AHL_MC_RULE_OBJECT_TYPES';
194 --
195 CURSOR get_part_number_csr(p_inv_item_id IN NUMBER) IS
196 SELECT distinct concatenated_segments
197 FROM MTL_SYSTEM_ITEMS_KFV
198 WHERE INVENTORY_ITEM_ID = p_inv_item_id;
199 --
200 CURSOR get_position_ref_meaning_csr(p_position_id IN NUMBER) IS
201 SELECT AHL_MC_PATH_POSITION_PVT.get_posref_by_id(p_position_id)
202 FROM DUAL;
203 --
204 CURSOR get_mc_name_csr (p_mc_id IN NUMBER) IS
205 SELECT distinct name
206 FROM ahl_mc_headers_b
207 WHERE mc_id = p_mc_id;
208 --
209 l_operator AHL_MC_RULE_STATEMENTS.OPERATOR%TYPE;
210 l_obj_ver_num NUMBER;
211 l_oper_meaning VARCHAR2(80);
212 l_subject_id AHL_MC_RULE_STATEMENTS.SUBJECT_ID%TYPE;
213 l_object_id AHL_MC_RULE_STATEMENTS.OBJECT_ID%TYPE;
214 l_subject_tbl AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type;
215 l_object_tbl AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type;
216 l_max NUMBER;
217
218 l_stmt_rec Rule_Stmt_Csr%ROWTYPE;
219 l_ui_stmt_rec AHL_MC_RULE_PVT.UI_Rule_Stmt_Rec_Type;
220 --
221 BEGIN
222
223 OPEN check_rule_operator_csr(p_rule_stmt_id);
224 FETCH check_rule_operator_csr INTO l_operator, l_subject_id,
225 l_object_id, l_oper_meaning, l_obj_ver_num;
226
227 --The rule statement has to exist
228 IF (check_rule_operator_csr%NOTFOUND) THEN
229 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_ID_INV');
230 FND_MESSAGE.Set_Token('RULE_STMT_ID',p_rule_stmt_id);
231 FND_MSG_PUB.ADD;
232 CLOSE check_rule_operator_csr;
233 RAISE FND_API.G_EXC_ERROR;
234 ELSE
235
236 CLOSE check_rule_operator_csr;
237 --Evaluate the subject and object ids
238 IF((l_operator = 'OR') OR
239 (l_operator = 'AND') OR
240 (l_operator = 'IMPLIES') OR
241 (l_operator = 'REQUIRES'))
242 THEN
243 --1) Build the subject table
244 Build_UI_Rule_Stmt_Tbl (
245 p_rule_stmt_id => l_subject_id,
246 x_rule_stmt_tbl => l_subject_tbl);
247 --Update the parens.
248 IF (l_subject_tbl.COUNT >1) THEN
249 l_subject_tbl(l_subject_tbl.FIRST).left_paren :=
250 l_subject_tbl(l_subject_tbl.FIRST).left_paren || '(';
251 l_subject_tbl(l_subject_tbl.LAST).right_paren :=
252 l_subject_tbl(l_subject_tbl.LAST).right_paren || ')';
253 END IF;
254 --Copy into x_rule_stmt table and update the operator
255 FOR i IN l_subject_tbl.FIRST..l_subject_tbl.LAST LOOP
256 x_rule_stmt_tbl(i) := l_subject_tbl(i);
257 END LOOP;
258
259 --2) Set the operator
260 l_max := x_rule_stmt_tbl.LAST;
261 x_rule_stmt_tbl(l_max).rule_operator := l_operator;
262 x_rule_stmt_tbl(l_max).rule_operator_meaning := l_oper_meaning;
263 x_rule_stmt_tbl(l_max).rule_oper_stmt_id := p_rule_stmt_id;
264 x_rule_stmt_tbl(l_max).rule_oper_stmt_obj_ver_num := l_obj_ver_num;
265
266 --3) Build the object table
267 Build_UI_Rule_Stmt_Tbl (
268 p_rule_stmt_id => l_object_id,
269 x_rule_stmt_tbl => l_object_tbl);
270 --Update parens and copy for the object table
271 IF (l_object_tbl.COUNT >1) THEN
272 l_object_tbl(l_object_tbl.FIRST).left_paren :=
273 l_object_tbl(l_object_tbl.FIRST).left_paren || '(';
277
274 l_object_tbl(l_object_tbl.LAST).right_paren :=
275 l_object_tbl(l_object_tbl.LAST).right_paren || ')';
276 END IF;
278 --Copy into x_rule_stmt table
279 FOR i IN l_object_tbl.FIRST..l_object_tbl.LAST LOOP
280 x_rule_stmt_tbl(l_max+i) := l_object_tbl(i);
281 END LOOP;
282
283 ELSE
284 --Regular statement query.
285 OPEN rule_stmt_csr(p_rule_stmt_id);
286 FETCH rule_stmt_csr INTO l_stmt_rec;
287
288 l_ui_stmt_rec.rule_statement_id:= l_stmt_rec.rule_statement_id;
289 l_ui_stmt_rec.position_id := l_stmt_rec.position_id;
290 l_ui_stmt_rec.position_meaning := l_stmt_rec.position_meaning;
291
292 --Fetch the operator
293 IF (l_stmt_rec.negation_flag IS NOT NULL AND
294 l_stmt_rec.negation_flag = 'T') THEN
295 l_ui_stmt_rec.operator := l_stmt_rec.operator || '_NOT';
296 ELSE
297 l_ui_stmt_rec.operator := l_stmt_rec.operator;
298 END IF;
299 l_ui_stmt_rec.operator_meaning := l_stmt_rec.operator_meaning;
300
301 l_ui_stmt_rec.object_id := l_stmt_rec.object_id;
302 l_ui_stmt_rec.object_type := l_stmt_rec.object_type;
303
304 IF (l_stmt_rec.object_type IS NOT NULL) THEN
305 OPEN get_object_type_csr(l_stmt_rec.object_type);
306 FETCH get_object_type_csr INTO l_ui_stmt_rec.object_type_meaning;
307 CLOSE get_object_type_csr;
308 IF (l_stmt_rec.object_type = 'ITEM') THEN
309 OPEN get_part_number_csr(l_stmt_rec.object_id);
310 FETCH get_part_number_csr INTO l_ui_stmt_rec.object_meaning;
311 CLOSE get_part_number_csr;
312 ELSIF (l_stmt_rec.object_type = 'CONFIGURATION') THEN
313 OPEN get_mc_name_csr(l_stmt_rec.object_id);
314 FETCH get_mc_name_csr INTO l_ui_stmt_rec.object_meaning;
315 CLOSE get_mc_name_csr;
316 ELSIF (l_stmt_rec.object_type = 'ITEM_AS_POSITION' OR
317 l_stmt_rec.object_type = 'CONFIG_AS_POSITION') THEN
318 OPEN get_position_ref_meaning_csr(l_stmt_rec.object_id);
319 FETCH get_position_ref_meaning_csr INTO l_ui_stmt_rec.object_meaning;
320 CLOSE get_position_ref_meaning_csr;
321 END IF;
322 END IF;
323 l_ui_stmt_rec.rule_stmt_obj_ver_num :=l_stmt_rec.OBJECT_VERSION_NUMBER;
324 l_ui_stmt_rec.OBJECT_ATTRIBUTE1:=l_stmt_rec.OBJECT_ATTRIBUTE1;
325 l_ui_stmt_rec.OBJECT_ATTRIBUTE2 :=l_stmt_rec.OBJECT_ATTRIBUTE2;
326 l_ui_stmt_rec.OBJECT_ATTRIBUTE3 :=l_stmt_rec.OBJECT_ATTRIBUTE3;
327 l_ui_stmt_rec.OBJECT_ATTRIBUTE4 :=l_stmt_rec.OBJECT_ATTRIBUTE4;
328 l_ui_stmt_rec.OBJECT_ATTRIBUTE5 :=l_stmt_rec.OBJECT_ATTRIBUTE5;
329 l_ui_stmt_rec.ATTRIBUTE_CATEGORY := l_stmt_rec.ATTRIBUTE_CATEGORY;
330 l_ui_stmt_rec.ATTRIBUTE1:= l_stmt_rec.ATTRIBUTE1;
331 l_ui_stmt_rec.ATTRIBUTE2:= l_stmt_rec.ATTRIBUTE2;
332 l_ui_stmt_rec.ATTRIBUTE3:= l_stmt_rec.ATTRIBUTE3;
333 l_ui_stmt_rec.ATTRIBUTE4:= l_stmt_rec.ATTRIBUTE4;
334 l_ui_stmt_rec.ATTRIBUTE5:= l_stmt_rec.ATTRIBUTE5;
335 l_ui_stmt_rec.ATTRIBUTE6:= l_stmt_rec.ATTRIBUTE6;
336 l_ui_stmt_rec.ATTRIBUTE7:= l_stmt_rec.ATTRIBUTE7;
337 l_ui_stmt_rec.ATTRIBUTE8:= l_stmt_rec.ATTRIBUTE8;
338 l_ui_stmt_rec.ATTRIBUTE9 := l_stmt_rec.ATTRIBUTE9;
339 l_ui_stmt_rec.ATTRIBUTE10:= l_stmt_rec.ATTRIBUTE10 ;
343 l_ui_stmt_rec.ATTRIBUTE14:= l_stmt_rec.ATTRIBUTE14;
340 l_ui_stmt_rec.ATTRIBUTE11:= l_stmt_rec.ATTRIBUTE11 ;
341 l_ui_stmt_rec.ATTRIBUTE12:= l_stmt_rec.ATTRIBUTE12 ;
342 l_ui_stmt_rec.ATTRIBUTE13:= l_stmt_rec.ATTRIBUTE13;
344 l_ui_stmt_rec.ATTRIBUTE15:= l_stmt_rec.ATTRIBUTE15;
345
346 x_rule_stmt_tbl(1) :=l_ui_stmt_rec;
347
348 CLOSE rule_stmt_csr;
349 END IF;
350
351 END IF;
352
353 END Build_UI_Rule_Stmt_Tbl;
354
355
356 -------------------------------
357 -- Start of Comments --
358 -- Procedure name : Build_Rule_Tree
359 -- Type : Private
360 -- Function : Helper method which builds the rule tree from the ui
361 -- rule table.
362 -- Assumptions : 1) Table is densely populated. 2) Table is sorted by sequence.
363 -- Pre-reqs :
364 -- Parameters :
365 --
366 -- End of Comments.
367
368 PROCEDURE Build_Rule_Tree (
369 p_rule_id IN NUMBER,
370 p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type,
371 x_rule_stmt_tbl OUT NOCOPY AHL_MC_RULE_PVT.Rule_Stmt_Tbl_Type)
372 IS
373 --
374 l_ui_stmt_tbl AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type;
375 l_ui_stmt_rec AHL_MC_RULE_PVT.UI_Rule_Stmt_Rec_Type;
376 l_depth_count NUMBER;
377 l_rule_stmt_id NUMBER;
378 l_msg_count NUMBER;
379 --
380 BEGIN
381 IF (p_rule_stmt_tbl.COUNT<1) THEN
382 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_NULL');
383 FND_MSG_PUB.ADD;
384 RAISE FND_API.G_EXC_ERROR;
385 END IF;
386
387 l_depth_count :=0;
388
389 --Do initial preprocess
390 FOR i IN p_rule_stmt_tbl.FIRST..p_rule_stmt_tbl.LAST LOOP
391 l_ui_stmt_rec := p_rule_stmt_tbl(i);
392
393 --Check that the parens are only valid characters
394 IF (ltrim(l_ui_stmt_rec.left_paren, '(' ) IS NOT NULL OR
395 ltrim(l_ui_stmt_rec.right_paren, ')' ) IS NOT NULL) THEN
396 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_PAREN_INV');
397 FND_MSG_PUB.ADD;
398 END IF;
399
400 --Check that the left parens>0 and right_paren>0 is an error
401 IF (length(l_ui_stmt_rec.left_paren) > 0 AND
402 length(l_ui_stmt_rec.right_paren) > 0 ) THEN
403 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_PAREN_DUP');
404 FND_MSG_PUB.ADD;
405 END IF;
406
407 --Calculate the depth and assign to statement
408 l_depth_count:=l_depth_count+nvl(length(l_ui_stmt_rec.left_paren),0);
409 l_ui_stmt_rec.rule_stmt_depth := l_depth_count;
410 l_depth_count:=l_depth_count-nvl(length(l_ui_stmt_rec.right_paren),0);
411 l_ui_stmt_rec.rule_oper_stmt_depth := l_depth_count;
412
413 IF (l_depth_count < 0 ) THEN
414 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_PAREN_UNMATCH');
415 FND_MSG_PUB.ADD;
416 END IF;
417
418 l_ui_stmt_tbl(i) := l_ui_stmt_rec;
419 END LOOP;
420
421
422 --Check that the left parens>1 and right_paren>1
423 IF (l_depth_count <>0) THEN
424 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_PAREN_UNMATCH');
425 FND_MSG_PUB.ADD;
426 END IF;
427
428 -- Check Error Message stack.
429 l_msg_count := FND_MSG_PUB.count_msg;
430 IF l_msg_count > 0 THEN
431 RAISE FND_API.G_EXC_ERROR;
432 END IF;
433
434 Build_Rule_Stmt_Tbl(
435 p_start_index => l_ui_stmt_tbl.FIRST,
436 p_end_index => l_ui_stmt_tbl.LAST,
437 p_rule_id => p_rule_id,
438 p_ui_stmt_tbl => l_ui_stmt_tbl,
439 x_rule_stmt_tbl => x_rule_stmt_tbl,
440 x_rule_stmt_id => l_rule_stmt_id);
441
442 --Set the last rule as the top rule statement
443 x_rule_stmt_tbl(x_rule_stmt_tbl.LAST).top_rule_stmt_flag := 'T';
444
445 END Build_Rule_Tree;
446 -------------------------------
447 -- Start of Comments --
448 -- Procedure name : Build_Rule_Stmt_Tbl
449 -- Type : Private
450 -- Function : Helper method which builds the rule_stmt tree based on ui_rule_stmts.
451 -- Pre-reqs :
452 -- Parameters :
453 --
454 -- End of Comments.
455
456 PROCEDURE Build_Rule_Stmt_Tbl (
460 p_ui_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type,
457 p_start_index IN NUMBER,
458 p_end_index IN NUMBER,
459 p_rule_id IN NUMBER,
461 x_rule_stmt_tbl OUT NOCOPY AHL_MC_RULE_PVT.Rule_Stmt_Tbl_Type,
462 x_rule_stmt_id OUT NOCOPY NUMBER)
463 IS
464 --
465 CURSOR next_rule_stmt_id_csr IS
466 SELECT ahl_mc_rule_statements_s.nextval FROM DUAL;
467
468 --
469 l_stmt_rec AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type;
470 l_ui_stmt_rec AHL_MC_RULE_PVT.UI_Rule_Stmt_Rec_Type;
471 l_subject_stmt_tbl AHL_MC_RULE_PVT.Rule_Stmt_Tbl_Type;
472 l_object_stmt_tbl AHL_MC_RULE_PVT.Rule_Stmt_Tbl_Type;
473 l_subject_id NUMBER; --subject stmt id
474 l_object_id NUMBER; --object stmt id
475 l_operator VARCHAR2(30); --rule operator
476 l_operation_flag VARCHAR2(1);
477 l_max NUMBER;
478 l_min_depth_index NUMBER;
479 l_min_depth NUMBER;
480 l_rstmt_obj_ver_num NUMBER;
481 --
482 BEGIN
483
484 --Convert if only 1 record
485 IF (p_start_index = p_end_index)
486 THEN
487 l_ui_stmt_rec := p_ui_stmt_tbl(p_start_index);
488 IF (l_ui_stmt_rec.rule_statement_id IS NOT NULL)
489 THEN
490 x_rule_stmt_id:=l_ui_stmt_rec.rule_statement_id;
491 l_rstmt_obj_ver_num :=l_ui_stmt_rec.rule_stmt_obj_ver_num;
492 l_operation_flag := 'U';
493 ELSE
494 OPEN next_rule_stmt_id_csr;
495 FETCH next_rule_stmt_id_csr INTO x_rule_stmt_id;
496 CLOSE next_rule_stmt_id_csr;
497 l_rstmt_obj_ver_num :=1;
498 l_operation_flag := 'I';
499 END IF;
500
501 --Convert operator to negation_flag
502 IF (INSTR(l_ui_stmt_rec.operator, '_NOT') >0)
503 THEN
504 l_stmt_rec.NEGATION_FLAG := 'T';
505 l_stmt_rec.OPERATOR := RTRIM(l_ui_stmt_rec.operator,'_NOT');
506 ELSE
507 l_stmt_rec.NEGATION_FLAG := NULL;
508 l_stmt_rec.OPERATOR := l_ui_stmt_rec.operator;
509 END IF;
510
511 l_stmt_rec.rule_statement_id:= x_rule_stmt_id;
512 l_stmt_rec.RULE_ID := p_rule_id;
513 l_stmt_rec.TOP_RULE_STMT_FLAG := NULL;
514 l_stmt_rec.OBJECT_VERSION_NUMBER := l_rstmt_obj_ver_num;
515 l_stmt_rec.SUBJECT_ID := l_ui_stmt_rec.POSITION_ID;
516 l_stmt_rec.SUBJECT_TYPE := 'POSITION';
517 l_stmt_rec.OBJECT_TYPE := l_ui_stmt_rec.OBJECT_TYPE;
518 IF (l_ui_stmt_rec.OBJECT_TYPE IS NULL) THEN
519 l_stmt_rec.OBJECT_ID := null;
520 ELSE
521 l_stmt_rec.OBJECT_ID := l_ui_stmt_rec.OBJECT_ID;
522 END IF;
523 l_stmt_rec.OBJECT_ATTRIBUTE1:=l_ui_stmt_rec.OBJECT_ATTRIBUTE1;
524 l_stmt_rec.OBJECT_ATTRIBUTE2:=l_ui_stmt_rec.OBJECT_ATTRIBUTE2;
525 l_stmt_rec.OBJECT_ATTRIBUTE3:=l_ui_stmt_rec.OBJECT_ATTRIBUTE3;
526 l_stmt_rec.OBJECT_ATTRIBUTE4:=l_ui_stmt_rec.OBJECT_ATTRIBUTE4;
527 l_stmt_rec.OBJECT_ATTRIBUTE5:=l_ui_stmt_rec.OBJECT_ATTRIBUTE5;
528 l_stmt_rec.ATTRIBUTE_CATEGORY:= l_ui_stmt_rec.ATTRIBUTE_CATEGORY;
529 l_stmt_rec.ATTRIBUTE1:= l_ui_stmt_rec.ATTRIBUTE1;
530 l_stmt_rec.ATTRIBUTE2:= l_ui_stmt_rec.ATTRIBUTE2;
531 l_stmt_rec.ATTRIBUTE3:= l_ui_stmt_rec.ATTRIBUTE3;
532 l_stmt_rec.ATTRIBUTE4:= l_ui_stmt_rec.ATTRIBUTE4;
533 l_stmt_rec.ATTRIBUTE5:= l_ui_stmt_rec.ATTRIBUTE5;
534 l_stmt_rec.ATTRIBUTE6:= l_ui_stmt_rec.ATTRIBUTE6;
535 l_stmt_rec.ATTRIBUTE7:= l_ui_stmt_rec.ATTRIBUTE7;
536 l_stmt_rec.ATTRIBUTE8:= l_ui_stmt_rec.ATTRIBUTE8;
537 l_stmt_rec.ATTRIBUTE9 := l_ui_stmt_rec.ATTRIBUTE9;
538 l_stmt_rec.ATTRIBUTE10:= l_ui_stmt_rec.ATTRIBUTE10 ;
539 l_stmt_rec.ATTRIBUTE11:= l_ui_stmt_rec.ATTRIBUTE11 ;
540 l_stmt_rec.ATTRIBUTE12:= l_ui_stmt_rec.ATTRIBUTE12 ;
541 l_stmt_rec.ATTRIBUTE13:= l_ui_stmt_rec.ATTRIBUTE13;
542 l_stmt_rec.ATTRIBUTE14:= l_ui_stmt_rec.ATTRIBUTE14;
546
543 l_stmt_rec.ATTRIBUTE15:= l_ui_stmt_rec.ATTRIBUTE15;
544 l_stmt_rec.operation_flag := l_operation_flag;
545 x_rule_stmt_tbl(1) := l_stmt_rec;
547 ELSE
548 --Recursive rule parsing portion
549 l_min_depth := p_ui_stmt_tbl.COUNT;
550 l_min_depth_index := -1;
551
552 --Find minimum depth index
553 FOR i IN p_start_index..p_end_index-1 LOOP
554 IF (p_ui_stmt_tbl(i).rule_oper_stmt_depth < l_min_depth)
555 THEN
556 l_min_depth := p_ui_stmt_tbl(i).rule_oper_stmt_depth;
557 l_min_depth_index := i;
558 END IF;
559 END LOOP;
560
561 --If we did not find the min depth, then throw an error.
562 IF (l_min_depth_index = -1) THEN
563 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_MIN_INV');
564 FND_MSG_PUB.ADD;
565 RAISE FND_API.G_EXC_ERROR;
566 END IF;
567
568 --Determine the operator and the operations flag
569 l_operator := p_ui_stmt_tbl(l_min_depth_index).rule_operator;
570 IF (p_ui_stmt_tbl(l_min_depth_index).rule_oper_stmt_id IS NOT NULL)
571 THEN
572 x_rule_stmt_id:=p_ui_stmt_tbl(l_min_depth_index).rule_oper_stmt_id;
573 l_rstmt_obj_ver_num :=l_ui_stmt_rec.rule_oper_stmt_obj_ver_num;
574 l_operation_flag := 'U';
575 ELSE
576 OPEN next_rule_stmt_id_csr;
577 FETCH next_rule_stmt_id_csr INTO x_rule_stmt_id;
578 CLOSE next_rule_stmt_id_csr;
579 l_rstmt_obj_ver_num :=1;
580 l_operation_flag := 'I';
581 END IF;
582
583 --If the operator is null, then throw error.
584 IF (l_operator IS NULL) THEN
585 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_OPER_NULL');
586 FND_MSG_PUB.ADD;
587 RAISE FND_API.G_EXC_ERROR;
588 END IF;
589
590
591 --1) Build the subject table
592 Build_Rule_Stmt_Tbl(
593 p_start_index => p_start_index,
594 p_end_index => l_min_depth_index,
595 p_rule_id => p_rule_id,
596 p_ui_stmt_tbl => p_ui_stmt_tbl,
597 x_rule_stmt_tbl => l_subject_stmt_tbl,
598 x_rule_stmt_id => l_subject_id);
599
600 --2) Build the object table
601 Build_Rule_Stmt_Tbl(
602 p_start_index => l_min_depth_index+1,
603 p_end_index => p_end_index,
604 p_rule_id => p_rule_id,
605 p_ui_stmt_tbl => p_ui_stmt_tbl,
606 x_rule_stmt_tbl => l_object_stmt_tbl,
607 x_rule_stmt_id => l_object_id
608 );
609
610 --3)Copy into x_rule_stmt table and update the operator
611 FOR i IN l_subject_stmt_tbl.FIRST..l_subject_stmt_tbl.LAST LOOP
612 x_rule_stmt_tbl(i) := l_subject_stmt_tbl(i);
613 END LOOP;
614
615 --4) Copy the object stmt table into x_rule_stmt table
616 l_max := x_rule_stmt_tbl.LAST;
617 FOR i IN l_object_stmt_tbl.FIRST..l_object_stmt_tbl.LAST LOOP
618 x_rule_stmt_tbl(l_max+i) := l_object_stmt_tbl(i);
619 END LOOP;
620
621 --5) Insert the join statement
622 l_max := x_rule_stmt_tbl.LAST;
623 l_stmt_rec.rule_statement_id:= x_rule_stmt_id;
624 l_stmt_rec.RULE_ID := p_rule_id;
625 l_stmt_rec.OBJECT_VERSION_NUMBER :=l_rstmt_obj_ver_num;
626 l_stmt_rec.NEGATION_FLAG := NULL;
627 l_stmt_rec.TOP_RULE_STMT_FLAG := NULL;
628 l_stmt_rec.SUBJECT_ID := l_subject_id;
629 l_stmt_rec.SUBJECT_TYPE := 'RULE_STMT';
630 l_stmt_rec.OPERATOR := l_operator;
631 l_stmt_rec.OBJECT_ID := l_object_id;
632 l_stmt_rec.OBJECT_TYPE := 'RULE_STMT';
633 l_stmt_rec.OBJECT_ATTRIBUTE1 := NULL;
634 l_stmt_rec.OBJECT_ATTRIBUTE2 := NULL;
635 l_stmt_rec.OBJECT_ATTRIBUTE3 := NULL;
636 l_stmt_rec.OBJECT_ATTRIBUTE4 := NULL;
637 l_stmt_rec.OBJECT_ATTRIBUTE5 := NULL;
638 l_stmt_rec.ATTRIBUTE_CATEGORY := NULL;
639 l_stmt_rec.ATTRIBUTE1:= NULL;
640 l_stmt_rec.ATTRIBUTE2:= NULL;
644 l_stmt_rec.ATTRIBUTE6:= NULL;
641 l_stmt_rec.ATTRIBUTE3:= NULL;
642 l_stmt_rec.ATTRIBUTE4:= NULL;
643 l_stmt_rec.ATTRIBUTE5:= NULL;
645 l_stmt_rec.ATTRIBUTE7:= NULL;
646 l_stmt_rec.ATTRIBUTE8:= NULL;
647 l_stmt_rec.ATTRIBUTE9 := NULL;
648 l_stmt_rec.ATTRIBUTE10:= NULL;
649 l_stmt_rec.ATTRIBUTE11:= NULL;
650 l_stmt_rec.ATTRIBUTE12:= NULL;
651 l_stmt_rec.ATTRIBUTE13:= NULL;
652 l_stmt_rec.ATTRIBUTE14:= NULL;
653 l_stmt_rec.ATTRIBUTE15:= NULL;
654 l_stmt_rec.operation_flag := l_operation_flag;
655
656 x_rule_stmt_tbl(l_max+1) :=l_stmt_rec;
657
658 END IF;
659
660
661 END Build_Rule_Stmt_Tbl;
662
663
664 --------------------------------
665 -- Start of Comments --
666 -- Procedure name : Insert_Rule
667 -- Type : Private
668 -- Function : Writes to DB the rule record and ui rule table
669 -- Pre-reqs :
670 -- Parameters :
671 --
672 -- Insert_Rule Parameters:
673 -- p_x_rule_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Rec_Type Required
674 -- p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type Required
675 --
676 -- End of Comments.
677
678 PROCEDURE Insert_Rule (
679 p_api_version IN NUMBER,
680 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
681 p_commit IN VARCHAR2 := FND_API.G_FALSE,
682 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
683 x_return_status OUT NOCOPY VARCHAR2,
684 x_msg_count OUT NOCOPY NUMBER,
685 x_msg_data OUT NOCOPY VARCHAR2,
686 p_module IN VARCHAR2 := 'JSP',
687 p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type,
688 p_x_rule_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Rec_Type)
689 IS
690 --
691 CURSOR check_mc_status_csr (p_header_id IN NUMBER) IS
692 SELECT config_status_code, config_status_meaning
693 FROM ahl_mc_headers_v header
694 WHERE header.mc_header_id = p_header_id;
695 --
696 CURSOR Check_rule_type_csr (p_type IN VARCHAR2) IS
697 SELECT 'X'
698 FROM FND_LOOKUPS
699 WHERE lookup_code = p_type
700 AND lookup_type = 'AHL_MC_RULE_TYPES';
701 --
702 CURSOR Check_rule_name_csr (p_name IN VARCHAR2, p_mc_header_id IN NUMBER) IS
703 SELECT 'X'
704 FROM AHL_MC_RULES_B
705 WHERE mc_header_id = p_mc_header_id
706 AND rule_name = p_name;
707 --
708 l_api_version CONSTANT NUMBER := 1.0;
709 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Rule';
710 l_junk VARCHAR2(1);
711 l_status_code VARCHAR2(30);
712 l_status VARCHAR2(80);
713 l_rule_stmt_tbl AHL_MC_RULE_PVT.Rule_Stmt_Tbl_Type;
714 l_row_id VARCHAR2(30);
715 --
716 BEGIN
717
718 -- Standard start of API savepoint
719 SAVEPOINT Insert_Rule_pvt;
720 -- Initialize Procedure return status to success
721 x_return_status := FND_API.G_RET_STS_SUCCESS;
722 -- Standard call to check for call compatibility
723 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
724 G_PKG_NAME) THEN
725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
726 END IF;
727
728 -- Initialize message list if p_init_msg_list is set to TRUE
729 IF FND_API.To_Boolean(p_init_msg_list) THEN
730 FND_MSG_PUB.Initialize;
731 END IF;
732
733 --Check Status of MC allows for editing
734 OPEN check_mc_status_csr(p_x_rule_rec.mc_header_id);
735 FETCH check_mc_status_csr INTO l_status_code, l_status;
736 IF (check_mc_status_csr%NOTFOUND) THEN
737 FND_MESSAGE.Set_Name('AHL','AHL_MC_HEADER_ID_INVALID');
738 FND_MESSAGE.Set_Token('NAME','');
739 FND_MESSAGE.Set_Token('MC_HEADER_ID',p_x_rule_rec.mc_header_id);
740 FND_MSG_PUB.ADD;
741 CLOSE check_mc_status_csr;
742 RAISE FND_API.G_EXC_ERROR;
743 ELSIF ( l_status_code <> 'DRAFT' AND
744 l_status_code <> 'APPROVAL_REJECTED') THEN
745 FND_MESSAGE.Set_Name('AHL','AHL_MC_EDIT_INV_MC');
746 FND_MESSAGE.Set_Token('STATUS', l_status);
747 FND_MSG_PUB.ADD;
748 CLOSE check_mc_status_csr;
749 RAISE FND_API.G_EXC_ERROR;
750 END IF;
751 CLOSE check_mc_status_csr;
752
753 IF (p_module = 'JSP') THEN
754
755 IF (p_x_rule_rec.DESCRIPTION = FND_API.G_MISS_CHAR) THEN
756 p_x_rule_rec.DESCRIPTION := null;
760 p_x_rule_rec.RULE_NAME := null;
757 END IF;
758
759 IF (p_x_rule_rec.RULE_NAME = FND_API.G_MISS_CHAR) THEN
761 END IF;
762 IF (p_x_rule_rec.RULE_TYPE_CODE = FND_API.G_MISS_CHAR) THEN
763 p_x_rule_rec.RULE_TYPE_CODE := null;
764 END IF;
765 IF (p_x_rule_rec.ACTIVE_START_DATE = FND_API.G_MISS_DATE) THEN
766 p_x_rule_rec.ACTIVE_START_DATE := null;
767 END IF;
768 IF (p_x_rule_rec.ACTIVE_END_DATE = FND_API.G_MISS_DATE) THEN
769 p_x_rule_rec.ACTIVE_END_DATE := null;
770 END IF;
771 IF (p_x_rule_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
772 p_x_rule_rec.ATTRIBUTE_CATEGORY := null;
773 END IF;
774 IF (p_x_rule_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
775 p_x_rule_rec.ATTRIBUTE1 := null;
776 END IF;
777 IF (p_x_rule_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
778 p_x_rule_rec.ATTRIBUTE2 := null;
779 END IF;
780
781 IF (p_x_rule_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
782 p_x_rule_rec.ATTRIBUTE3 := null;
783 END IF;
784 IF (p_x_rule_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
785 p_x_rule_rec.ATTRIBUTE4 := null;
786 END IF;
787 IF (p_x_rule_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
788 p_x_rule_rec.ATTRIBUTE5 := null;
789 END IF;
790 IF (p_x_rule_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
791 p_x_rule_rec.ATTRIBUTE6 := null;
792 END IF;
793
794 IF (p_x_rule_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
795 p_x_rule_rec.ATTRIBUTE7 := null;
796 END IF;
797 IF (p_x_rule_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
798 p_x_rule_rec.ATTRIBUTE8 := null;
799 END IF;
800 IF (p_x_rule_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
801 p_x_rule_rec.ATTRIBUTE9 := null;
802 END IF;
803 IF (p_x_rule_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
804 p_x_rule_rec.ATTRIBUTE10 := null;
805 END IF;
806 IF (p_x_rule_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
807 p_x_rule_rec.ATTRIBUTE11 := null;
808 END IF;
809 IF (p_x_rule_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
810 p_x_rule_rec.ATTRIBUTE12 := null;
811 END IF;
812 IF (p_x_rule_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
813 p_x_rule_rec.ATTRIBUTE13 := null;
814 END IF;
815 IF (p_x_rule_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
816 p_x_rule_rec.ATTRIBUTE14 := null;
817 END IF;
818 IF (p_x_rule_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
819 p_x_rule_rec.ATTRIBUTE15 := null;
820 END IF;
821 END IF;
822
823 --Check Rule Name is not null
824 IF (p_x_rule_rec.rule_name IS NULL) THEN
825 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_NAME_NULL');
826 FND_MSG_PUB.ADD;
827 END IF;
828
829 --Check Rule Type is valid
830 OPEN check_rule_type_csr(p_x_rule_rec.rule_type_code);
831 FETCH check_rule_type_csr INTO l_junk;
832 IF (check_rule_type_csr%NOTFOUND) THEN
833 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_TYPE_INV');
834 FND_MESSAGE.Set_Token('RULE_TYPE',p_x_rule_rec.rule_type_code);
835 FND_MSG_PUB.ADD;
836 END IF;
837 CLOSE check_rule_type_csr;
838
839 --Check start date is less than end date
840 IF (p_x_rule_rec.active_start_date IS NOT NULL AND
841 p_x_rule_rec.active_end_date IS NOT NULL AND
842 p_x_rule_rec.active_start_date >= p_x_rule_rec.active_end_date) THEN
843 FND_MESSAGE.Set_Name('AHL','AHL_MC_DATE_INVALID');
844 FND_MESSAGE.Set_Token('SDATE',p_x_rule_rec.active_start_date);
845 FND_MESSAGE.Set_Token('EDATE',p_x_rule_rec.active_end_date);
846 FND_MSG_PUB.ADD;
847 END IF;
848
849 --Check Rule name is unique
850 OPEN check_rule_name_csr(p_x_rule_rec.rule_name, p_x_rule_rec.mc_header_id);
851 FETCH check_rule_name_csr INTO l_junk;
852 IF (check_rule_name_csr%FOUND) THEN
853 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_NAME_INV');
854 FND_MESSAGE.Set_Token('RULE_NAME', p_x_rule_rec.rule_name);
855 FND_MSG_PUB.ADD;
856 END IF;
857 CLOSE check_rule_name_csr;
858
859 -- Check Error Message stack.
860 x_msg_count := FND_MSG_PUB.count_msg;
861 IF x_msg_count > 0 THEN
862 RAISE FND_API.G_EXC_ERROR;
863 END IF;
864
865 SELECT AHL_MC_RULES_B_S.nextval
866 INTO p_x_rule_rec.rule_id
867 FROM dual;
868
869 --Convert the flat structure into a rule tree.
870 Build_rule_tree( p_rule_id => p_x_rule_rec.rule_id,
871 p_rule_stmt_tbl=> p_rule_stmt_tbl,
872 x_rule_stmt_tbl=> l_rule_stmt_tbl);
873
874 --Insert the Rule Record
875 AHL_MC_RULES_PKG.INSERT_ROW (
876 X_ROWID => l_row_id,
877 X_RULE_ID => p_x_rule_rec.rule_id,
878 X_OBJECT_VERSION_NUMBER => 1,
879 X_RULE_NAME => p_x_rule_rec.rule_name,
880 X_MC_HEADER_ID => p_x_rule_rec.mc_header_id,
881 X_RULE_TYPE_CODE => p_x_rule_rec.rule_type_code,
882 X_ACTIVE_START_DATE => p_x_rule_rec.active_start_date,
883 X_ACTIVE_END_DATE => p_x_rule_rec.active_end_date,
884 X_ATTRIBUTE_CATEGORY => p_x_rule_rec.ATTRIBUTE_CATEGORY,
885 X_ATTRIBUTE1 => p_x_rule_rec.ATTRIBUTE1,
886 X_ATTRIBUTE2 => p_x_rule_rec.ATTRIBUTE2,
887 X_ATTRIBUTE3 => p_x_rule_rec.ATTRIBUTE3,
888 X_ATTRIBUTE4 => p_x_rule_rec.ATTRIBUTE4,
892 X_ATTRIBUTE8 => p_x_rule_rec.ATTRIBUTE8,
889 X_ATTRIBUTE5 => p_x_rule_rec.ATTRIBUTE5,
890 X_ATTRIBUTE6 => p_x_rule_rec.ATTRIBUTE6,
891 X_ATTRIBUTE7 => p_x_rule_rec.ATTRIBUTE7,
893 X_ATTRIBUTE9 => p_x_rule_rec.ATTRIBUTE9,
894 X_ATTRIBUTE10 => p_x_rule_rec.ATTRIBUTE10,
895 X_ATTRIBUTE11 => p_x_rule_rec.ATTRIBUTE11,
896 X_ATTRIBUTE12 => p_x_rule_rec.ATTRIBUTE12,
897 X_ATTRIBUTE13 => p_x_rule_rec.ATTRIBUTE13,
898 X_ATTRIBUTE14 => p_x_rule_rec.ATTRIBUTE14,
899 X_ATTRIBUTE15 => p_x_rule_rec.ATTRIBUTE15,
900 X_DESCRIPTION => p_x_rule_rec.description,
901 X_CREATION_DATE => SYSDATE,
902 X_CREATED_BY => Fnd_Global.USER_ID,
903 X_LAST_UPDATE_DATE => SYSDATE,
904 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
905 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
906
907 --Insert the Rule statement Records
908 FOR i IN l_rule_stmt_tbl.FIRST..l_rule_stmt_tbl.LAST LOOP
909 AHL_MC_RULE_STMT_PVT.Insert_Rule_Stmt (
910 p_api_version => 1.0,
911 p_commit => FND_API.G_FALSE,
912 p_module => p_module,
913 p_x_rule_stmt_rec => l_rule_stmt_tbl(i),
914 x_return_status => x_return_status,
915 x_msg_count => x_msg_count,
916 x_msg_data => x_msg_data);
917
918 -- Check Error Message stack.
919 x_msg_count := FND_MSG_PUB.count_msg;
920 IF x_msg_count > 0 THEN
921 RAISE FND_API.G_EXC_ERROR;
922 END IF;
923 END LOOP;
924
925 --Update the status to Draft if approval rejected
926 IF (l_status_code = 'APPROVAL_REJECTED')
927 THEN
928 UPDATE ahl_mc_headers_b
929 SET config_status_code = 'DRAFT'
930 WHERE mc_header_id = p_x_rule_rec.mc_header_id;
931 END IF;
932
933 -- Standard check of p_commit
934 IF FND_API.TO_BOOLEAN(p_commit) THEN
935 COMMIT WORK;
936 END IF;
937
938 -- Standard call to get message count and if count is 1, get message info
939 FND_MSG_PUB.Count_And_Get
940 ( p_count => x_msg_count,
941 p_data => x_msg_data,
942 p_encoded => fnd_api.g_false
943 );
944 EXCEPTION
945 WHEN FND_API.G_EXC_ERROR THEN
946 Rollback to Insert_Rule_pvt;
947 x_return_status := FND_API.G_RET_STS_ERROR;
948 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
949 p_data => x_msg_data,
950 p_encoded => fnd_api.g_false);
951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
952 Rollback to Insert_Rule_pvt;
953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
954 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
955 p_data => x_msg_data,
956 p_encoded => fnd_api.g_false);
957 WHEN OTHERS THEN
958 Rollback to Insert_Rule_pvt;
959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
960 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
961 p_procedure_name => l_api_name,
962 p_error_text => SQLERRM);
963 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
964 p_data => x_msg_data,
965 p_encoded => fnd_api.g_false);
966
967 END Insert_Rule;
968
969 --------------------------------
970 -- Start of Comments --
971 -- Procedure name : Update_Rule
972 -- Type : Private
973 -- Function : Writes to DB the rule record and ui rule table
974 -- Pre-reqs :
975 -- Parameters :
976 --
977 -- Update_Rule Parameters:
978 -- p_rule_rec IN AHL_MC_RULE_PVT.Rule_Rec_Type Required
979 -- p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type Required
980 --
981 -- End of Comments.
982
983 PROCEDURE Update_Rule (
984 p_api_version IN NUMBER,
985 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
986 p_commit IN VARCHAR2 := FND_API.G_FALSE,
987 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
988 x_return_status OUT NOCOPY VARCHAR2,
989 x_msg_count OUT NOCOPY NUMBER,
990 x_msg_data OUT NOCOPY VARCHAR2,
991 p_module IN VARCHAR2 := 'JSP',
992 p_rule_rec IN AHL_MC_RULE_PVT.Rule_Rec_Type,
993 p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type)
994 IS
995 --
996 CURSOR get_rule_rec_csr (p_rule_id IN NUMBER) IS
997 SELECT *
998 FROM AHL_MC_RULES_VL
999 WHERE rule_id = p_rule_id;
1000 --
1001 CURSOR Check_rule_type_csr (p_type IN VARCHAR2) IS
1002 SELECT 'X'
1003 FROM FND_LOOKUPS
1004 WHERE lookup_code = p_type
1005 AND lookup_type = 'AHL_MC_RULE_TYPES';
1006 --
1007 CURSOR Check_rule_name_csr (p_name IN VARCHAR2,
1008 p_mc_header_id IN NUMBER,
1012 WHERE mc_header_id = p_mc_header_id
1009 p_rule_id IN NUMBER) IS
1010 SELECT 'X'
1011 FROM AHL_MC_RULES_B
1013 AND rule_name = p_name
1014 AND rule_id <> p_rule_id;
1015 --
1016 --This cursor fetches all rule statements for given rule_id
1017 CURSOR get_rule_stmt_ids_csr (p_rule_id IN NUMBER) IS
1018 SELECT rule_statement_id
1019 FROM ahl_mc_rule_statements
1020 WHERE rule_id = p_rule_id;
1021 --
1022 CURSOR check_mc_status_csr (p_rule_id IN NUMBER) IS
1023 SELECT config_status_code, config_status_meaning
1024 FROM ahl_mc_rules_b rule, ahl_mc_headers_v header
1025 WHERE rule.mc_header_id = header.mc_header_id
1026 AND rule.rule_id = p_rule_id;
1027 --
1028 l_api_version CONSTANT NUMBER := 1.0;
1029 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rule';
1030 l_junk VARCHAR2(1);
1031 l_status_code VARCHAR2(30);
1032 l_status VARCHAR2(80);
1033 l_rule_stmt_tbl AHL_MC_RULE_PVT.Rule_Stmt_Tbl_Type;
1034 l_rule_rec AHL_MC_RULE_PVT.Rule_Rec_Type;
1035 l_old_rule_rec Get_rule_rec_csr%ROWTYPE;
1036 l_rule_stmt_id NUMBER;
1037 l_match_flag BOOLEAN;
1038 --
1039 BEGIN
1040 -- Standard start of API savepoint
1041 SAVEPOINT Update_Rule_pvt;
1042 -- Initialize Procedure return status to success
1043 x_return_status := FND_API.G_RET_STS_SUCCESS;
1044 -- Standard call to check for call compatibility
1045 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1046 G_PKG_NAME) THEN
1047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1048 END IF;
1049
1050 -- Initialize message list if p_init_msg_list is set to TRUE
1051 IF FND_API.To_Boolean(p_init_msg_list) THEN
1052 FND_MSG_PUB.Initialize;
1053 END IF;
1054
1055 --Check Status of MC allows for editing
1056 OPEN check_mc_status_csr(p_rule_rec.rule_id);
1057 FETCH check_mc_status_csr INTO l_status_code, l_status;
1058 IF (check_mc_status_csr%NOTFOUND) THEN
1059 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_ID_INV');
1060 FND_MESSAGE.Set_Token('RULE_ID',p_rule_rec.rule_id);
1061 FND_MSG_PUB.ADD;
1062 CLOSE check_mc_status_csr;
1063 RAISE FND_API.G_EXC_ERROR;
1064 ELSIF ( l_status_code <> 'DRAFT' AND
1065 l_status_code <> 'APPROVAL_REJECTED') THEN
1066 FND_MESSAGE.Set_Name('AHL','AHL_MC_EDIT_INV_MC');
1067 FND_MESSAGE.Set_Token('STATUS', l_status);
1068 FND_MSG_PUB.ADD;
1069 CLOSE check_mc_status_csr;
1070 RAISE FND_API.G_EXC_ERROR;
1071 END IF;
1072 CLOSE check_mc_status_csr;
1073
1074
1075 --Check Rule ID is valid
1076 OPEN get_rule_rec_csr(p_rule_rec.rule_id);
1077 FETCH get_rule_rec_csr INTO l_old_rule_rec;
1078 IF (get_rule_rec_csr%NOTFOUND) THEN
1079 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_ID_INV');
1080 FND_MESSAGE.Set_Token('RULE_ID', p_rule_rec.rule_id);
1081 FND_MSG_PUB.ADD;
1082 END IF;
1083 CLOSE get_rule_rec_csr;
1084
1085 --Assign to local Var
1086 l_rule_rec := p_rule_rec;
1087 -- Check Object version number.
1088 IF (l_rule_rec.object_version_number IS NOT NULL AND
1089 l_old_rule_rec.object_version_number<>l_rule_rec.object_version_number)
1090 THEN
1091 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1092 FND_MSG_PUB.ADD;
1093 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1094 END IF;
1095
1096 --Convert NULL/G_MISS types
1097 IF (p_module = 'JSP') THEN
1098 IF (l_rule_rec.DESCRIPTION IS NULL) THEN
1099 l_rule_rec.DESCRIPTION := l_old_rule_rec.DESCRIPTION;
1100 ELSIF (l_rule_rec.DESCRIPTION = FND_API.G_MISS_CHAR) THEN
1101 l_rule_rec.DESCRIPTION := NULL;
1102 END IF;
1103 IF (l_rule_rec.RULE_NAME IS NULL) THEN
1104 l_rule_rec.RULE_NAME := l_old_rule_rec.RULE_NAME;
1105 ELSIF (l_rule_rec.RULE_NAME = FND_API.G_MISS_CHAR) THEN
1106 l_rule_rec.RULE_NAME := NULL;
1107 END IF;
1108 IF (l_rule_rec.RULE_TYPE_CODE IS NULL) THEN
1109 l_rule_rec.RULE_TYPE_CODE := l_old_rule_rec.RULE_TYPE_CODE;
1110 ELSIF (l_rule_rec.RULE_TYPE_CODE = FND_API.G_MISS_CHAR) THEN
1111 l_rule_rec.RULE_TYPE_CODE := NULL;
1112 END IF;
1113 IF (l_rule_rec.ACTIVE_START_DATE IS NULL) THEN
1114 l_rule_rec.ACTIVE_START_DATE := l_old_rule_rec.ACTIVE_START_DATE;
1118 IF (l_rule_rec.ACTIVE_END_DATE IS NULL) THEN
1115 ELSIF (l_rule_rec.ACTIVE_START_DATE = FND_API.G_MISS_DATE) THEN
1116 l_rule_rec.ACTIVE_START_DATE := NULL;
1117 END IF;
1119 l_rule_rec.ACTIVE_END_DATE := l_old_rule_rec.ACTIVE_END_DATE;
1120 ELSIF (l_rule_rec.ACTIVE_END_DATE = FND_API.G_MISS_DATE) THEN
1121 l_rule_rec.ACTIVE_END_DATE := NULL;
1122 END IF;
1123 IF (l_rule_rec.ATTRIBUTE_CATEGORY IS NULL) THEN
1124 l_rule_rec.ATTRIBUTE_CATEGORY := l_old_rule_rec.ATTRIBUTE_CATEGORY;
1125 ELSIF (l_rule_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
1126 l_rule_rec.ATTRIBUTE_CATEGORY := NULL;
1127 END IF;
1128 IF (l_rule_rec.ATTRIBUTE1 IS NULL) THEN
1129 l_rule_rec.ATTRIBUTE1 := l_old_rule_rec.ATTRIBUTE1;
1130 ELSIF (l_rule_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
1131 l_rule_rec.ATTRIBUTE1 := NULL;
1132 END IF;
1133 IF (l_rule_rec.ATTRIBUTE2 IS NULL) THEN
1134 l_rule_rec.ATTRIBUTE2 := l_old_rule_rec.ATTRIBUTE2;
1135 ELSIF (l_rule_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
1136 l_rule_rec.ATTRIBUTE2 := NULL;
1137 END IF;
1138 IF (l_rule_rec.ATTRIBUTE3 IS NULL) THEN
1139 l_rule_rec.ATTRIBUTE3 := l_old_rule_rec.ATTRIBUTE3;
1140 ELSIF (l_rule_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
1141 l_rule_rec.ATTRIBUTE3 := NULL;
1142 END IF;
1143 IF (l_rule_rec.ATTRIBUTE4 IS NULL) THEN
1144 l_rule_rec.ATTRIBUTE4 := l_old_rule_rec.ATTRIBUTE4;
1145 ELSIF (l_rule_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
1146 l_rule_rec.ATTRIBUTE4 := NULL;
1147 END IF;
1148 IF (l_rule_rec.ATTRIBUTE5 IS NULL) THEN
1149 l_rule_rec.ATTRIBUTE5 := l_old_rule_rec.ATTRIBUTE5;
1150 ELSIF (l_rule_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
1151 l_rule_rec.ATTRIBUTE5 := NULL;
1152 END IF;
1153 IF (l_rule_rec.ATTRIBUTE6 IS NULL) THEN
1154 l_rule_rec.ATTRIBUTE6 := l_old_rule_rec.ATTRIBUTE6;
1155 ELSIF (l_rule_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
1156 l_rule_rec.ATTRIBUTE6 := NULL;
1157 END IF;
1158 IF (l_rule_rec.ATTRIBUTE7 IS NULL) THEN
1159 l_rule_rec.ATTRIBUTE7 := l_old_rule_rec.ATTRIBUTE7;
1160 ELSIF (l_rule_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
1161 l_rule_rec.ATTRIBUTE7 := NULL;
1162 END IF;
1163 IF (l_rule_rec.ATTRIBUTE8 IS NULL) THEN
1164 l_rule_rec.ATTRIBUTE8 := l_old_rule_rec.ATTRIBUTE8;
1165 ELSIF (l_rule_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
1166 l_rule_rec.ATTRIBUTE8 := NULL;
1167 END IF;
1168 IF (l_rule_rec.ATTRIBUTE9 IS NULL) THEN
1169 l_rule_rec.ATTRIBUTE9 := l_old_rule_rec.ATTRIBUTE9;
1170 ELSIF (l_rule_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
1171 l_rule_rec.ATTRIBUTE9 := NULL;
1172 END IF;
1173 IF (l_rule_rec.ATTRIBUTE10 IS NULL) THEN
1174 l_rule_rec.ATTRIBUTE10 := l_old_rule_rec.ATTRIBUTE10;
1175 ELSIF (l_rule_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
1176 l_rule_rec.ATTRIBUTE10 := NULL;
1177 END IF;
1178 IF (l_rule_rec.ATTRIBUTE11 IS NULL) THEN
1179 l_rule_rec.ATTRIBUTE11 := l_old_rule_rec.ATTRIBUTE11;
1180 ELSIF (l_rule_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
1181 l_rule_rec.ATTRIBUTE11 := NULL;
1182 END IF;
1183 IF (l_rule_rec.ATTRIBUTE12 IS NULL) THEN
1184 l_rule_rec.ATTRIBUTE12 := l_old_rule_rec.ATTRIBUTE12;
1185 ELSIF (l_rule_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
1186 l_rule_rec.ATTRIBUTE12 := NULL;
1187 END IF;
1188 IF (l_rule_rec.ATTRIBUTE13 IS NULL) THEN
1189 l_rule_rec.ATTRIBUTE13 := l_old_rule_rec.ATTRIBUTE13;
1190 ELSIF (l_rule_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
1191 l_rule_rec.ATTRIBUTE13 := NULL;
1192 END IF;
1193 IF (l_rule_rec.ATTRIBUTE14 IS NULL) THEN
1194 l_rule_rec.ATTRIBUTE14 := l_old_rule_rec.ATTRIBUTE14;
1195 ELSIF (l_rule_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
1196 l_rule_rec.ATTRIBUTE14 := NULL;
1197 END IF;
1198 IF (l_rule_rec.ATTRIBUTE15 IS NULL) THEN
1199 l_rule_rec.ATTRIBUTE15 := l_old_rule_rec.ATTRIBUTE15;
1200 ELSIF (l_rule_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
1201 l_rule_rec.ATTRIBUTE15 := NULL;
1202 END IF;
1203
1204 END IF;
1205
1206
1207 --Check Rule Name is not null
1208 IF (l_rule_rec.rule_name IS NULL) THEN
1209 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_NAME_NULL');
1210 FND_MSG_PUB.ADD;
1211 END IF;
1212
1213 --Check Rule Type is valid
1214 OPEN check_rule_type_csr(l_rule_rec.rule_type_code);
1215 FETCH check_rule_type_csr INTO l_junk;
1216 IF (check_rule_type_csr%NOTFOUND) THEN
1217 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_TYPE_INV');
1218 FND_MESSAGE.Set_Token('RULE_TYPE',l_rule_rec.rule_type_code);
1219 FND_MSG_PUB.ADD;
1220 END IF;
1221 CLOSE check_rule_type_csr;
1222
1223 --Check Rule name is unique
1224 OPEN check_rule_name_csr(l_rule_rec.rule_name,
1225 l_rule_rec.mc_header_id,
1226 l_rule_rec.rule_id);
1227 FETCH check_rule_name_csr INTO l_junk;
1228 IF (check_rule_name_csr%FOUND) THEN
1229 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_NAME_INV');
1230 FND_MESSAGE.Set_Token('RULE_NAME',l_rule_rec.rule_name);
1231 FND_MSG_PUB.ADD;
1232 END IF;
1233 CLOSE check_rule_name_csr;
1234
1235 --Check start date is less than end date
1239 FND_MESSAGE.Set_Name('AHL','AHL_MC_DATE_INVALID');
1236 IF (l_rule_rec.active_start_date IS NOT NULL AND
1237 l_rule_rec.active_end_date IS NOT NULL AND
1238 l_rule_rec.active_start_date >= l_rule_rec.active_end_date) THEN
1240 FND_MESSAGE.Set_Token('SDATE',l_rule_rec.active_start_date);
1241 FND_MESSAGE.Set_Token('EDATE',l_rule_rec.active_end_date);
1242 FND_MSG_PUB.ADD;
1243 END IF;
1244
1245 -- Check Error Message stack.
1246 x_msg_count := FND_MSG_PUB.count_msg;
1247 IF x_msg_count > 0 THEN
1248 RAISE FND_API.G_EXC_ERROR;
1249 END IF;
1250
1251 --Convert the flat structure into a rule tree.
1252 Build_rule_tree( p_rule_id => l_rule_rec.rule_id,
1253 p_rule_stmt_tbl=> p_rule_stmt_tbl,
1254 x_rule_stmt_tbl=> l_rule_stmt_tbl);
1255
1256 --Update the Rule Record
1257 AHL_MC_RULES_PKG.UPDATE_ROW (
1258 X_RULE_ID => l_rule_rec.rule_id,
1259 X_OBJECT_VERSION_NUMBER => l_rule_rec.object_version_number +1,
1260 X_MC_HEADER_ID => l_rule_rec.mc_header_id,
1261 X_RULE_NAME => l_rule_rec.rule_name,
1262 X_RULE_TYPE_CODE => l_rule_rec.rule_type_code,
1263 X_ACTIVE_START_DATE => l_rule_rec.active_start_date,
1264 X_ACTIVE_END_DATE => l_rule_rec.active_end_date,
1265 X_ATTRIBUTE_CATEGORY => l_rule_rec.ATTRIBUTE_CATEGORY,
1266 X_ATTRIBUTE1 => l_rule_rec.ATTRIBUTE1,
1267 X_ATTRIBUTE2 => l_rule_rec.ATTRIBUTE2,
1268 X_ATTRIBUTE3 => l_rule_rec.ATTRIBUTE3,
1269 X_ATTRIBUTE4 => l_rule_rec.ATTRIBUTE4,
1270 X_ATTRIBUTE5 => l_rule_rec.ATTRIBUTE5,
1271 X_ATTRIBUTE6 => l_rule_rec.ATTRIBUTE6,
1272 X_ATTRIBUTE7 => l_rule_rec.ATTRIBUTE7,
1273 X_ATTRIBUTE8 => l_rule_rec.ATTRIBUTE8,
1274 X_ATTRIBUTE9 => l_rule_rec.ATTRIBUTE9,
1275 X_ATTRIBUTE10 => l_rule_rec.ATTRIBUTE10,
1276 X_ATTRIBUTE11 => l_rule_rec.ATTRIBUTE11,
1277 X_ATTRIBUTE12 => l_rule_rec.ATTRIBUTE12,
1278 X_ATTRIBUTE13 => l_rule_rec.ATTRIBUTE13,
1279 X_ATTRIBUTE14 => l_rule_rec.ATTRIBUTE14,
1280 X_ATTRIBUTE15 => l_rule_rec.ATTRIBUTE15,
1281 X_DESCRIPTION => l_rule_rec.description,
1282 X_LAST_UPDATE_DATE => SYSDATE,
1283 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1284 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1285
1286 --Delete all the extra Rule statement Records
1287 OPEN get_rule_stmt_ids_csr(l_rule_rec.rule_id);
1288 LOOP
1289 FETCH get_rule_stmt_ids_csr INTO l_rule_stmt_id;
1290 EXIT WHEN get_rule_stmt_ids_csr%NOTFOUND;
1291
1292 l_match_flag := FALSE;
1293
1294 <<l_match_loop>>
1295 --Check for any which matching rule statements
1296 FOR i IN l_rule_stmt_tbl.FIRST..l_rule_stmt_tbl.LAST LOOP
1297 IF (l_rule_stmt_tbl(i).rule_statement_id = l_rule_stmt_id) THEN
1298 l_match_flag := TRUE;
1299 EXIT l_match_loop;
1300 END IF;
1301 END LOOP;
1302
1303 --If no match, then delete the rule statement
1304 IF (NOT(l_match_flag)) THEN
1305 DELETE FROM AHL_MC_RULE_STATEMENTS
1306 WHERE rule_statement_id = l_rule_stmt_id;
1307 END IF;
1308 END LOOP;
1309
1310 --Now insert or update all the existing rule statements
1311 FOR i IN l_rule_stmt_tbl.FIRST..l_rule_stmt_tbl.LAST LOOP
1312 IF (l_rule_stmt_tbl(i).operation_flag = 'I') THEN
1313 AHL_MC_RULE_STMT_PVT.Insert_Rule_Stmt (
1314 p_api_version => 1.0,
1315 p_commit => FND_API.G_FALSE,
1316 p_x_rule_stmt_rec => l_rule_stmt_tbl(i),
1317 x_return_status => x_return_status,
1318 x_msg_count => x_msg_count,
1319 x_msg_data => x_msg_data);
1320
1321 ELSIF (l_rule_stmt_tbl(i).operation_flag = 'U') THEN
1322 AHL_MC_RULE_STMT_PVT.Update_Rule_Stmt (
1323 p_api_version => 1.0,
1324 p_commit => FND_API.G_FALSE,
1325 p_rule_stmt_rec => l_rule_stmt_tbl(i),
1326 x_return_status => x_return_status,
1327 x_msg_count => x_msg_count,
1328 x_msg_data => x_msg_data);
1329 END IF;
1330
1331 -- Check Error Message stack.
1332 x_msg_count := FND_MSG_PUB.count_msg;
1333 IF x_msg_count > 0 THEN
1334 RAISE FND_API.G_EXC_ERROR;
1335 END IF;
1336 END LOOP;
1337
1338 --Update the status to Draft if approval rejected
1339 IF (l_status_code = 'APPROVAL_REJECTED')
1340 THEN
1341 UPDATE ahl_mc_headers_b
1342 SET config_status_code = 'DRAFT'
1343 WHERE mc_header_id = l_rule_rec.mc_header_id;
1344 END IF;
1345
1346 -- Standard check of p_commit
1347 IF FND_API.TO_BOOLEAN(p_commit) THEN
1348 COMMIT WORK;
1349 END IF;
1350
1351 -- Standard call to get message count and if count is 1, get message info
1352 FND_MSG_PUB.Count_And_Get
1353 ( p_count => x_msg_count,
1354 p_data => x_msg_data,
1355 p_encoded => fnd_api.g_false
1356 );
1357 EXCEPTION
1358 WHEN FND_API.G_EXC_ERROR THEN
1359 Rollback to Update_Rule_pvt;
1363 p_encoded => fnd_api.g_false);
1360 x_return_status := FND_API.G_RET_STS_ERROR;
1361 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1362 p_data => x_msg_data,
1364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1365 Rollback to Update_Rule_pvt;
1366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1367 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1368 p_data => x_msg_data,
1369 p_encoded => fnd_api.g_false);
1370 WHEN OTHERS THEN
1371 Rollback to Update_Rule_pvt;
1372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1373 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1374 p_procedure_name => l_api_name,
1375 p_error_text => SQLERRM);
1376 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1377 p_data => x_msg_data,
1378 p_encoded => fnd_api.g_false);
1379 END Update_Rule;
1380
1381 -----------------------------
1382 -- Start of Comments --
1383 -- Procedure name : Delete_Rule
1384 -- Type : Private
1385 -- Function : Deletes the Rule corresponding to p_rule_rec
1386 -- Pre-reqs :
1387 -- Parameters :
1388 --
1389 -- Delete_Rule Parameters:
1390 -- p_rule_rec.rule_id IN NUMBER Required
1391 -- p_rule_rec.object_version_number IN NUMBER Required
1392 --
1393 -- End of Comments.
1394
1395 PROCEDURE Delete_Rule (
1396 p_api_version IN NUMBER,
1397 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1398 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1399 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1400 x_return_status OUT NOCOPY VARCHAR2,
1401 x_msg_count OUT NOCOPY NUMBER,
1402 x_msg_data OUT NOCOPY VARCHAR2,
1403 p_rule_rec IN RULE_REC_TYPE)
1404 IS
1405 --
1406 CURSOR check_obj_ver_csr (p_rule_id IN NUMBER, p_obj_ver IN NUMBER) IS
1407 SELECT 'X'
1408 FROM ahl_mc_rules_b rule
1409 WHERE rule.object_version_number = p_obj_ver
1410 AND rule.rule_id = p_rule_id;
1411 --
1412 CURSOR check_mc_status_csr (p_rule_id IN NUMBER) IS
1413 SELECT config_status_code, config_status_meaning, header.mc_header_id
1414 FROM ahl_mc_rules_b rule, ahl_mc_headers_v header
1415 WHERE rule.mc_header_id = header.mc_header_id
1416 AND rule.rule_id = p_rule_id;
1417 --
1418 l_api_version CONSTANT NUMBER := 1.0;
1419 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rule';
1420 l_mc_header_id NUMBER;
1421 l_junk VARCHAR2(1);
1422 l_status_code VARCHAR2(30);
1423 l_status VARCHAR2(80);
1424 --
1425 BEGIN
1426 -- Standard start of API savepoint
1427 SAVEPOINT Delete_Rule_pvt;
1428 -- Initialize Procedure return status to success
1429 x_return_status := FND_API.G_RET_STS_SUCCESS;
1430 -- Standard call to check for call compatibility
1431 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1432 G_PKG_NAME) THEN
1433 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1434 END IF;
1435 -- Initialize message list if p_init_msg_list is set to TRUE
1436 IF FND_API.To_Boolean(p_init_msg_list) THEN
1437 FND_MSG_PUB.Initialize;
1438 END IF;
1439
1440 -- Check Object version number.
1441 OPEN check_obj_ver_csr(p_rule_rec.rule_id, p_rule_rec.object_version_number);
1442 FETCH check_obj_ver_csr INTO l_junk;
1443 IF (check_obj_ver_csr%NOTFOUND) THEN
1444 CLOSE check_obj_ver_csr;
1445 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1446 FND_MSG_PUB.ADD;
1447 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1448 END IF;
1449 CLOSE check_obj_ver_csr;
1450
1451 --Check Status of MC allows for editing
1452 OPEN check_mc_status_csr(p_rule_rec.rule_id);
1453 FETCH check_mc_status_csr INTO l_status_code, l_status, l_mc_header_id;
1454 IF (check_mc_status_csr%NOTFOUND) THEN
1455 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_ID_INV');
1456 FND_MESSAGE.Set_Token('RULE_ID',p_rule_rec.rule_id);
1457 FND_MSG_PUB.ADD;
1458 CLOSE check_mc_status_csr;
1459 RAISE FND_API.G_EXC_ERROR;
1460 ELSIF ( l_status_code <> 'DRAFT' AND
1461 l_status_code <> 'APPROVAL_REJECTED') THEN
1462 FND_MESSAGE.Set_Name('AHL','AHL_MC_EDIT_INV_MC');
1463 FND_MESSAGE.Set_Token('STATUS', l_status);
1464 FND_MSG_PUB.ADD;
1465 CLOSE check_mc_status_csr;
1466 RAISE FND_API.G_EXC_ERROR;
1470 --Delete the rule statements first.
1467 END IF;
1468 CLOSE check_mc_status_csr;
1469
1471 AHL_MC_RULE_STMT_PVT.Delete_Rule_Stmts (
1472 p_api_version => 1.0,
1473 p_commit => FND_API.G_FALSE,
1474 p_rule_id => p_rule_rec.rule_id,
1475 x_return_status => x_return_status,
1476 x_msg_count => x_msg_count,
1477 x_msg_data => x_msg_data);
1478
1479 -- Check Error Message stack.
1480 x_msg_count := FND_MSG_PUB.count_msg;
1481 IF x_msg_count > 0 THEN
1482 RAISE FND_API.G_EXC_ERROR;
1483 END IF;
1484
1485 --Delete the row record
1486 AHL_MC_RULES_PKG.DELETE_ROW ( X_RULE_ID => p_rule_rec.rule_id);
1487
1488
1489 --Update the status to Draft if approval rejected
1490 IF (l_status_code = 'APPROVAL_REJECTED')
1491 THEN
1492 UPDATE ahl_mc_headers_b
1493 SET config_status_code = 'DRAFT'
1494 WHERE mc_header_id = l_mc_header_id;
1495 END IF;
1496
1497 -- Standard check of p_commit
1498 IF FND_API.TO_BOOLEAN(p_commit) THEN
1499 COMMIT WORK;
1500 END IF;
1501 -- Standard call to get message count and if count is 1, get message info
1502 FND_MSG_PUB.Count_And_Get
1503 ( p_count => x_msg_count,
1504 p_data => x_msg_data,
1505 p_encoded => fnd_api.g_false
1506 );
1507 EXCEPTION
1508 WHEN FND_API.G_EXC_ERROR THEN
1509 Rollback to Delete_Rule_pvt;
1510 x_return_status := FND_API.G_RET_STS_ERROR;
1511 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1512 p_data => x_msg_data,
1513 p_encoded => fnd_api.g_false);
1514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515 Rollback to Delete_Rule_pvt;
1516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1517 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1518 p_data => x_msg_data,
1519 p_encoded => fnd_api.g_false);
1520 WHEN OTHERS THEN
1521 Rollback to Delete_Rule_pvt;
1522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1523 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1524 p_procedure_name => l_api_name,
1525 p_error_text => SQLERRM);
1526 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1527 p_data => x_msg_data,
1528 p_encoded => fnd_api.g_false);
1529 END Delete_Rule;
1530
1531 -----------------------------
1532 -- Start of Comments --
1533 -- Procedure name : Copy_Rules_For_MC
1534 -- Type : Private
1535 -- Function : Copies all Rules for 1 MC to another MC
1536 -- Pre-reqs :
1537 -- Parameters :
1538 --
1539 -- Copy_Rule_For_MC Parameters:
1540 -- p_from_mc_header_id IN NUMBER Required
1541 -- p_to_mc_header_id IN NUMBER Required
1542 --
1543 -- End of Comments.
1544
1545 PROCEDURE Copy_Rules_For_MC (
1546 p_api_version IN NUMBER,
1547 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1548 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1549 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1550 x_return_status OUT NOCOPY VARCHAR2,
1551 x_msg_count OUT NOCOPY NUMBER,
1552 x_msg_data OUT NOCOPY VARCHAR2,
1553 p_from_mc_header_id IN NUMBER,
1554 p_to_mc_header_id IN NUMBER )
1555 IS
1556 --
1557 CURSOR get_rule_rec_csr (p_mc_header_id IN NUMBER) IS
1558 SELECT *
1559 FROM AHL_MC_RULES_VL
1560 WHERE MC_HEADER_ID = p_mc_header_id;
1561 --
1562 CURSOR get_rule_stmt_id_csr (p_rule_id IN NUMBER) IS
1563 SELECT rule_statement_id
1564 FROM AHL_MC_RULE_STATEMENTS
1565 WHERE rule_id = p_rule_id
1566 AND top_rule_stmt_flag = 'T';
1567 --
1568 l_api_version CONSTANT NUMBER := 1.0;
1569 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Rules_For_Mc';
1570 l_rule_rec get_rule_rec_csr%ROWTYPE;
1571 l_row_id VARCHAR2(30);
1572 l_new_rule_id NUMBER;
1573 l_stmt_id NUMBER;
1574 l_new_stmt_id NUMBER;
1575 --
1576 BEGIN
1577 -- Standard start of API savepoint
1578 SAVEPOINT Copy_Rules_For_Mc_pvt;
1579
1580 -- Initialize Procedure return status to success
1584 G_PKG_NAME) THEN
1581 x_return_status := FND_API.G_RET_STS_SUCCESS;
1582 -- Standard call to check for call compatibility
1583 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1586 END IF;
1587
1588 -- Initialize message list if p_init_msg_list is set to TRUE
1589 IF FND_API.To_Boolean(p_init_msg_list) THEN
1590 FND_MSG_PUB.Initialize;
1591 END IF;
1592
1593 --For each rule for given mc, copy the rule
1594 OPEN get_rule_rec_csr(p_from_mc_header_id);
1595 LOOP
1596 FETCH get_rule_rec_csr INTO l_rule_rec;
1597 EXIT WHEN get_rule_rec_csr%NOTFOUND;
1598
1599 SAVEPOINT copy_rule_pvt;
1600
1601 SELECT AHL_MC_RULES_B_S.nextval
1602 INTO l_new_rule_id
1603 FROM dual;
1604
1605 --Copy the Rule Record
1606 AHL_MC_RULES_PKG.INSERT_ROW (
1607 X_ROWID => l_row_id,
1608 X_RULE_ID => l_new_rule_id,
1609 X_OBJECT_VERSION_NUMBER => 1,
1610 X_RULE_NAME => l_rule_rec.rule_name,
1611 X_MC_HEADER_ID => p_to_mc_header_id,
1612 X_RULE_TYPE_CODE => l_rule_rec.rule_type_code,
1613 X_ACTIVE_START_DATE => l_rule_rec.active_start_date,
1614 X_ACTIVE_END_DATE => l_rule_rec.active_end_date,
1615 X_ATTRIBUTE_CATEGORY => l_rule_rec.ATTRIBUTE_CATEGORY,
1616 X_ATTRIBUTE1 => l_rule_rec.ATTRIBUTE1,
1617 X_ATTRIBUTE2 => l_rule_rec.ATTRIBUTE2,
1618 X_ATTRIBUTE3 => l_rule_rec.ATTRIBUTE3,
1619 X_ATTRIBUTE4 => l_rule_rec.ATTRIBUTE4,
1620 X_ATTRIBUTE5 => l_rule_rec.ATTRIBUTE5,
1621 X_ATTRIBUTE6 => l_rule_rec.ATTRIBUTE6,
1622 X_ATTRIBUTE7 => l_rule_rec.ATTRIBUTE7,
1623 X_ATTRIBUTE8 => l_rule_rec.ATTRIBUTE8,
1624 X_ATTRIBUTE9 => l_rule_rec.ATTRIBUTE9,
1625 X_ATTRIBUTE10 => l_rule_rec.ATTRIBUTE10,
1626 X_ATTRIBUTE11 => l_rule_rec.ATTRIBUTE11,
1627 X_ATTRIBUTE12 => l_rule_rec.ATTRIBUTE12,
1628 X_ATTRIBUTE13 => l_rule_rec.ATTRIBUTE13,
1629 X_ATTRIBUTE14 => l_rule_rec.ATTRIBUTE14,
1630 X_ATTRIBUTE15 => l_rule_rec.ATTRIBUTE15,
1631 X_DESCRIPTION => l_rule_rec.description,
1632 X_CREATION_DATE => SYSDATE,
1633 X_CREATED_BY => Fnd_Global.USER_ID,
1634 X_LAST_UPDATE_DATE => SYSDATE,
1635 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1636 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
1637
1638 OPEN get_rule_stmt_id_csr(l_rule_rec.rule_id);
1639 FETCH get_rule_stmt_id_csr INTO l_stmt_id;
1640 CLOSE get_rule_stmt_id_csr;
1641
1642 --Calls copy rule statement with top stmt, which will recursively
1643 -- copy the rule statements.
1644 AHL_MC_RULE_STMT_PVT.Copy_Rule_Stmt (
1645 p_api_version => 1.0,
1646 p_commit => FND_API.G_FALSE,
1647 p_rule_stmt_id => l_stmt_id,
1648 p_to_rule_id => l_new_rule_id,
1649 p_to_mc_header_id => p_to_mc_header_id,
1650 x_rule_stmt_id => l_new_stmt_id,
1651 x_return_status => x_return_status,
1652 x_msg_count => x_msg_count,
1653 x_msg_data => x_msg_data);
1657 IF (l_new_stmt_id is NULL AND
1654
1655
1656 --Verify that the rule stmt if null, means can not be copied
1658 x_return_Status = fnd_api.g_ret_sts_success) THEN
1659 ROLLBACK TO copy_rule_pvt;
1660 ELSE
1661 --No errors in creating rule
1662 -- Check Error Message stack.
1663 x_msg_count := FND_MSG_PUB.count_msg;
1664 IF x_msg_count > 0 THEN
1665 CLOSE get_rule_rec_csr;
1666 RAISE FND_API.G_EXC_ERROR;
1667 END IF;
1668
1669 --Set the rule id for the new top statement. Special case
1670 -- can not be included in recursive code.
1671 UPDATE AHL_MC_RULE_STATEMENTS
1672 SET top_rule_stmt_flag = 'T'
1673 WHERE rule_statement_id = l_new_stmt_id;
1674
1675 END IF;
1676 END LOOP;
1677 CLOSE get_rule_rec_csr;
1678
1679 -- Standard check of p_commit
1680 IF FND_API.TO_BOOLEAN(p_commit) THEN
1681 COMMIT WORK;
1682 END IF;
1683 -- Standard call to get message count and if count is 1, get message info
1684 FND_MSG_PUB.Count_And_Get
1685 ( p_count => x_msg_count,
1686 p_data => x_msg_data,
1687 p_encoded => fnd_api.g_false
1688 );
1689 EXCEPTION
1690 WHEN FND_API.G_EXC_ERROR THEN
1691 Rollback to Copy_Rules_For_Mc_pvt;
1692 x_return_status := FND_API.G_RET_STS_ERROR;
1693 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1694 p_data => x_msg_data,
1695 p_encoded => fnd_api.g_false);
1696 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1697 Rollback to Copy_Rules_For_Mc_pvt;
1698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1699 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1700 p_data => x_msg_data,
1701 p_encoded => fnd_api.g_false);
1702 WHEN OTHERS THEN
1703 Rollback to Copy_Rules_For_Mc_pvt;
1704 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1705 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1706 p_procedure_name => l_api_name,
1707 p_error_text => SQLERRM);
1708 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1709 p_data => x_msg_data,
1710 p_encoded => fnd_api.g_false);
1711 END Copy_Rules_For_MC;
1712
1713 -----------------------------
1714 -- Start of Comments --
1715 -- Procedure name : Delete_Rules_For_MC
1716 -- Type : Private
1717 -- Function : Deletes the Rule corresponding to 1 MC
1718 -- Pre-reqs :
1719 -- Parameters :
1720 --
1721 -- Delete_Rules_For_MC Parameters:
1722 -- p_mc_header_id IN NUMBER Required
1723 --
1724 -- End of Comments.
1725
1726 PROCEDURE Delete_Rules_For_MC (
1727 p_api_version IN NUMBER,
1728 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1729 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1730 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1731 x_return_status OUT NOCOPY VARCHAR2,
1732 x_msg_count OUT NOCOPY NUMBER,
1733 x_msg_data OUT NOCOPY VARCHAR2,
1734 p_mc_header_id IN NUMBER)
1735 IS
1736 --
1737 CURSOR get_rule_ids_csr (p_mc_header_id IN NUMBER) IS
1738 SELECT rule_id, object_version_number
1739 FROM AHL_MC_RULES_B
1740 WHERE MC_HEADER_ID = p_mc_header_id;
1741 --
1742 l_api_version CONSTANT NUMBER := 1.0;
1743 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rules_For_Mc';
1744 l_rule_rec RULE_REC_TYPE;
1745 --
1746 BEGIN
1747 -- Standard start of API savepoint
1748 SAVEPOINT Delete_Rules_For_Mc_pvt;
1749
1750 -- Initialize Procedure return status to success
1751 x_return_status := FND_API.G_RET_STS_SUCCESS;
1752 -- Standard call to check for call compatibility
1753 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1754 G_PKG_NAME) THEN
1758 -- Initialize message list if p_init_msg_list is set to TRUE
1755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1756 END IF;
1757
1759 IF FND_API.To_Boolean(p_init_msg_list) THEN
1760 FND_MSG_PUB.Initialize;
1761 END IF;
1762
1763 --For each rule for given mc, delete the rule
1764 OPEN get_rule_ids_csr(p_mc_header_id);
1765 LOOP
1766 FETCH get_rule_ids_csr INTO l_rule_rec.rule_id,
1767 l_rule_rec.object_version_number;
1768 EXIT WHEN get_rule_ids_csr%NOTFOUND;
1769
1770 AHL_MC_RULE_PVT.Delete_Rule (
1771 p_api_version => 1.0,
1772 p_commit => FND_API.G_FALSE,
1773 p_rule_rec => l_rule_rec,
1774 x_return_status => x_return_status,
1775 x_msg_count => x_msg_count,
1776 x_msg_data => x_msg_data);
1777
1778 -- Check Error Message stack.
1779 x_msg_count := FND_MSG_PUB.count_msg;
1780 IF x_msg_count > 0 THEN
1781 CLOSE get_rule_ids_csr;
1782 RAISE FND_API.G_EXC_ERROR;
1783 END IF;
1784 END LOOP;
1785 CLOSE get_rule_ids_csr;
1786
1787 -- Standard check of p_commit
1788 IF FND_API.TO_BOOLEAN(p_commit) THEN
1789 COMMIT WORK;
1790 END IF;
1791 -- Standard call to get message count and if count is 1, get message info
1792 FND_MSG_PUB.Count_And_Get
1793 ( p_count => x_msg_count,
1794 p_data => x_msg_data,
1795 p_encoded => fnd_api.g_false
1796 );
1797 EXCEPTION
1798 WHEN FND_API.G_EXC_ERROR THEN
1799 Rollback to Delete_Rules_For_Mc_pvt;
1800 x_return_status := FND_API.G_RET_STS_ERROR;
1801 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1802 p_data => x_msg_data,
1803 p_encoded => fnd_api.g_false);
1804 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1805 Rollback to Delete_Rules_For_Mc_pvt;
1806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1807 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1808 p_data => x_msg_data,
1809 p_encoded => fnd_api.g_false);
1810 WHEN OTHERS THEN
1811 Rollback to Delete_Rules_For_Mc_pvt;
1812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1813 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1814 p_procedure_name => l_api_name,
1815 p_error_text => SQLERRM);
1816 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1817 p_data => x_msg_data,
1818 p_encoded => fnd_api.g_false);
1819 END Delete_Rules_For_MC;
1820
1821
1822 -----------------------------
1823 -- Start of Comments --
1824 -- Procedure name : Get_Rules_For_Position
1825 -- Type : Private
1826 -- Function : Returns all the rules that belong to a position
1827 -- Pre-reqs :
1828 -- Parameters :
1829 -- p_encoded_path is the position path of the node.
1830 -- x_rule_tbl is a list of all applicable rules for position path
1831 --
1832 -- End of Comments.
1833
1834 PROCEDURE Get_Rules_For_Position (
1835 p_api_version IN NUMBER,
1836 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1837 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1838 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1839 x_return_status OUT NOCOPY VARCHAR2,
1840 x_msg_count OUT NOCOPY NUMBER,
1841 x_msg_data OUT NOCOPY VARCHAR2,
1842 p_mc_header_id IN NUMBER,
1843 p_encoded_path IN VARCHAR2,
1844 x_rule_tbl OUT NOCOPY Rule_Tbl_Type)
1845 IS
1846 --
1847 --Fetches the relevant fields for the rule vo
1848 CURSOR get_rule_vo_csr (p_mc_header_id IN NUMBER,
1852 rul.object_version_number,
1849 p_encoded_path IN VARCHAR2,
1850 p_size IN NUMBER) IS
1851 SELECT rul.rule_id,
1853 rul.mc_header_id,
1854 rul.rule_name,
1855 rul.rule_type_code,
1856 lookup.meaning rule_type_meaning,
1857 rul.active_start_date,
1858 rul.active_end_date,
1859 rul.description,
1860 rul.attribute_category,
1861 rul.attribute1,
1862 rul.attribute2,
1863 rul.attribute3,
1864 rul.attribute4,
1865 rul.attribute5,
1866 rul.attribute6,
1867 rul.attribute7,
1868 rul.attribute8,
1869 rul.attribute9,
1870 rul.attribute10,
1871 rul.attribute11,
1872 rul.attribute12,
1873 rul.attribute13,
1874 rul.attribute14,
1875 rul.attribute15
1876 FROM AHL_MC_RULES_VL rul, FND_LOOKUPS lookup
1877 WHERE rul.rule_type_code = lookup.lookup_code
1878 AND lookup.lookup_type = 'AHL_MC_RULE_TYPES'
1879 AND rul.mc_header_id = p_mc_header_id
1880 AND rul.rule_id IN (
1881 SELECT rst.rule_id
1882 FROM AHL_MC_RULE_STATEMENTS rst, AHL_MC_PATH_POSITIONS pst
1883 WHERE rst.subject_type = 'POSITION'
1884 AND rst.subject_id = pst.path_position_id
1885 AND p_encoded_path LIKE pst.encoded_path_position
1886 AND p_size = (select COUNT(path_position_node_id) FROM
1887 AHL_MC_PATH_POSITION_NODES where path_position_id=pst.path_position_id)
1888 UNION ALL
1889 SELECT rst.rule_id
1890 FROM AHL_MC_RULE_STATEMENTS rst, AHL_MC_PATH_POSITIONS pst
1891 WHERE (rst.object_type = 'ITEM_AS_POSITION'
1892 OR rst.object_type = 'CONFIG_AS_POSITION')
1893 AND rst.object_id = pst.path_position_id
1894 AND p_encoded_path LIKE pst.encoded_path_position
1895 AND p_size = (select COUNT(path_position_node_id) FROM
1896 AHL_MC_PATH_POSITION_NODES where path_position_id=pst.path_position_id));
1897 --
1898 l_api_version CONSTANT NUMBER := 1.0;
1899 l_api_name CONSTANT VARCHAR2(30) := 'Get_Rules_For_Position';
1900 l_get_rule_rec get_rule_vo_csr%ROWTYPE;
1901 l_index NUMBER;
1902 l_rule_rec RULE_REC_TYPE;
1903 --
1904 BEGIN
1905 -- Standard start of API savepoint
1906 SAVEPOINT Get_Rules_For_Position_pvt;
1907
1908 -- Initialize Procedure return status to success
1909 x_return_status := FND_API.G_RET_STS_SUCCESS;
1910 -- Standard call to check for call compatibility
1911 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1912 G_PKG_NAME) THEN
1913 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1914 END IF;
1915
1916 -- Initialize message list if p_init_msg_list is set to TRUE
1917 IF FND_API.To_Boolean(p_init_msg_list) THEN
1918 FND_MSG_PUB.Initialize;
1919 END IF;
1920
1921
1922 l_index :=0;
1923
1924 --For each rule for given position, fetch the rule
1925 --Calculates the depth of the path by # of / + 1
1926 OPEN get_rule_vo_csr(p_mc_header_id, p_encoded_path,
1927 length(p_encoded_path) - length(replace(p_encoded_path,'/'))+1);
1928 LOOP
1929 FETCH get_rule_vo_csr INTO l_get_rule_rec;
1930 EXIT WHEN get_rule_vo_csr%NOTFOUND;
1931
1932 l_rule_rec.rule_id :=l_get_rule_rec.rule_id;
1933 l_rule_rec.rule_name :=l_get_rule_rec.rule_name;
1934 l_rule_rec.mc_header_id :=l_get_rule_rec.mc_header_id;
1935 l_rule_rec.rule_type_code :=l_get_rule_rec.rule_type_code;
1936 l_rule_rec.rule_type_meaning :=l_get_rule_rec.rule_type_meaning;
1937 l_rule_rec.active_start_date :=l_get_rule_rec.active_start_date;
1938 l_rule_rec.active_end_date :=l_get_rule_rec.active_end_date;
1939 l_rule_rec.object_version_number :=l_get_rule_rec.object_version_number;
1940 l_rule_rec.description :=l_get_rule_rec.description;
1941 l_rule_rec.ATTRIBUTE_CATEGORY := l_get_rule_rec.ATTRIBUTE_CATEGORY;
1942 l_rule_rec.ATTRIBUTE1:= l_get_rule_rec.ATTRIBUTE1;
1943 l_rule_rec.ATTRIBUTE2:= l_get_rule_rec.ATTRIBUTE2;
1944 l_rule_rec.ATTRIBUTE3:= l_get_rule_rec.ATTRIBUTE3;
1945 l_rule_rec.ATTRIBUTE4:= l_get_rule_rec.ATTRIBUTE4;
1946 l_rule_rec.ATTRIBUTE5:= l_get_rule_rec.ATTRIBUTE5;
1947 l_rule_rec.ATTRIBUTE6:= l_get_rule_rec.ATTRIBUTE6;
1948 l_rule_rec.ATTRIBUTE7:= l_get_rule_rec.ATTRIBUTE7;
1949 l_rule_rec.ATTRIBUTE8:= l_get_rule_rec.ATTRIBUTE8;
1950 l_rule_rec.ATTRIBUTE9 := l_get_rule_rec.ATTRIBUTE9;
1951 l_rule_rec.ATTRIBUTE10:= l_get_rule_rec.ATTRIBUTE10 ;
1952 l_rule_rec.ATTRIBUTE11:= l_get_rule_rec.ATTRIBUTE11 ;
1953 l_rule_rec.ATTRIBUTE12:= l_get_rule_rec.ATTRIBUTE12 ;
1954 l_rule_rec.ATTRIBUTE13:= l_get_rule_rec.ATTRIBUTE13;
1955 l_rule_rec.ATTRIBUTE14:= l_get_rule_rec.ATTRIBUTE14;
1956 l_rule_rec.ATTRIBUTE15:= l_get_rule_rec.ATTRIBUTE15;
1957 x_rule_tbl(l_index) := l_rule_rec;
1958 l_index := l_index +1;
1959 END LOOP;
1960 CLOSE get_rule_vo_csr;
1961
1965 p_data => x_msg_data,
1962 -- Standard call to get message count and if count is 1, get message info
1963 FND_MSG_PUB.Count_And_Get
1964 ( p_count => x_msg_count,
1966 p_encoded => fnd_api.g_false
1967 );
1968 EXCEPTION
1969 WHEN FND_API.G_EXC_ERROR THEN
1970 Rollback to Get_Rules_For_Position_pvt;
1971 x_return_status := FND_API.G_RET_STS_ERROR;
1972 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1973 p_data => x_msg_data,
1974 p_encoded => fnd_api.g_false);
1975 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1976 Rollback to Get_Rules_For_Position_pvt;
1977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1978 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1979 p_data => x_msg_data,
1980 p_encoded => fnd_api.g_false);
1981 WHEN OTHERS THEN
1982 Rollback to Get_Rules_For_Position_pvt;
1983 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1984 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1985 p_procedure_name => l_api_name,
1986 p_error_text => SQLERRM);
1987 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1988 p_data => x_msg_data,
1989 p_encoded => fnd_api.g_false);
1990 --
1991 END Get_Rules_For_Position;
1992
1993 End AHL_MC_RULE_PVT;