[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_RULE_STMT_PVT
Source
1 PACKAGE BODY AHL_MC_RULE_STMT_PVT AS
2 /* $Header: AHLVRSTB.pls 120.1 2007/12/21 13:34:54 sathapli ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Ahl_MC_Rule_Stmt_Pvt';
4 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME;
5
6 --
7 --Local function used to fetch the rule operator
8 --
9 FUNCTION get_rule_oper(p_rule_oper IN VARCHAR2,
10 p_neg_flag IN VARCHAR2)
11 RETURN VARCHAR2;
12
13 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 03-Dec-2007
14 -- New APIs declared: -
15 FUNCTION get_fnd_lkup_meaning(p_lookup_type IN VARCHAR2, p_lookup_code IN VARCHAR2)
16 RETURN VARCHAR2;
17
18 PROCEDURE validate_pos_quantity_rule(p_rule_stmt_rec IN AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type);
19
20 --
21 ------------------------
22 -- Declare Procedures --
23 ------------------------
24 --------------------------------
25 -- Start of Comments --
26 -- Procedure name : Validate_Rule_Stmt
27 -- Type : Private
28 -- Function : Validates the rule statement for statement errors.
29 -- Pre-reqs :
30 -- Parameters :
31 --
32 -- Validate_Rule_Stmt Parameters:
33 -- p_rule_stmt_rec IN AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type Required
34 --
35 -- End of Comments.
36
37 PROCEDURE Validate_Rule_Stmt (
38 p_api_version IN NUMBER,
39 p_init_msg_list 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_stmt_rec IN AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type)
45 IS
46 --
47 CURSOR Check_rule_type_csr (p_rule_id IN NUMBER) IS
48 SELECT rule_type_code FROM ahl_mc_rules_b
49 WHERE rule_id = p_rule_id;
50 --
51 CURSOR Check_rule_statement_csr (p_rule_stmt_id IN NUMBER) IS
52 SELECT 'X'
53 FROM ahl_mc_rule_statements
54 WHERE rule_statement_id = p_rule_stmt_id;
55
56 --Checks that position_id is valid and that position_id maps to the rule's mc
57 -- by checking mc_id and version are the same.
58 CURSOR Check_position_id_csr (p_rule_id IN NUMBER, p_position_id IN NUMBER) IS
59 SELECT 'X'
60 FROM AHL_MC_HEADERS_B header,
61 AHL_MC_PATH_POSITION_NODES pnodes, AHL_MC_RULES_B rule
62 WHERE pnodes.path_position_id = p_position_id
63 AND rule.rule_id = p_rule_id
64 AND rule.mc_header_id = header.mc_header_id
65 AND pnodes.sequence = 0
66 AND pnodes.mc_id = header.mc_id
67 AND nvl(pnodes.version_number,header.version_number) = header.version_number;
68
69 --Checks the inventory_item_id. Ignoring all org_id
70 CURSOR Check_item_id_csr (p_inventory_item_id IN NUMBER) IS
71 SELECT 'X'
72 FROM MTL_SYSTEM_ITEMS_KFV
73 WHERE inventory_item_id = p_inventory_item_id;
74 --
75 --Check that mc_id plus version number is valid configuration
76 CURSOR Check_mc_id_csr (p_mc_id IN NUMBER,
77 p_ver_number IN NUMBER) IS
78 SELECT 'X'
79 FROM AHL_MC_HEADERS_B
80 WHERE mc_id = p_mc_id
81 AND version_number = nvl(p_ver_number, version_number);
82
83 --
84 l_api_version CONSTANT NUMBER := 1.0;
85 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Rule_Stmt';
86 l_junk VARCHAR2(1);
87 l_rule_type AHL_MC_RULES_B.RULE_TYPE_CODE%TYPE;
88 --
89 BEGIN
90 -- Initialize Procedure return status to success
91 x_return_status := FND_API.G_RET_STS_SUCCESS;
92 -- Standard call to check for call compatibility
93 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
94 G_PKG_NAME) THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97 -- Initialize message list if p_init_msg_list is set to TRUE
98 IF FND_API.To_Boolean(p_init_msg_list) THEN
99 FND_MSG_PUB.Initialize;
100 END IF;
101
102 --Check Rule subject id is not null
103 IF (p_rule_stmt_rec.subject_id IS NULL) THEN
104 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_SUBJ_NULL');
105 FND_MSG_PUB.ADD;
106 END IF;
107
108 --Check Rule operator is not null
109 IF (p_rule_stmt_rec.operator IS NULL) THEN
110 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_OPER_NULL');
111 FND_MSG_PUB.ADD;
112 END IF;
113
114 --Validate join operators
115 IF ( (p_rule_stmt_rec.operator = 'OR') OR
116 (p_rule_stmt_rec.operator = 'AND') OR
117 (p_rule_stmt_rec.operator = 'IMPLIES') OR
118 (p_rule_stmt_rec.operator = 'REQUIRES'))
119 THEN
120 --Check negation flag and subj/obj types
121 IF ((p_rule_stmt_rec.subject_type <> 'RULE_STMT') OR
122 (p_rule_stmt_rec.object_type <> 'RULE_STMT') OR
123 (p_rule_stmt_rec.negation_flag IS NOT NULL))
124 THEN
125 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_INV');
126 FND_MESSAGE.Set_Token('OPERATOR',get_rule_oper(p_rule_stmt_rec.operator, p_rule_stmt_rec.negation_flag));
127 FND_MSG_PUB.ADD;
128 END IF;
129
130 --Check both rule_ids are valid rule statements.
131 OPEN check_rule_statement_csr(p_rule_stmt_rec.subject_id);
132 FETCH check_rule_statement_csr INTO l_junk;
133 IF (check_rule_statement_csr%NOTFOUND) THEN
134 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_ID_INV');
135 FND_MESSAGE.Set_Token('RULE_STMT_ID',p_rule_stmt_rec.subject_id);
136 FND_MSG_PUB.ADD;
137 END IF;
138 CLOSE check_rule_statement_csr;
139
140 OPEN check_rule_statement_csr(p_rule_stmt_rec.object_id);
141 FETCH check_rule_statement_csr INTO l_junk;
142 IF (check_rule_statement_csr%NOTFOUND) THEN
143 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_ID_INV');
144 FND_MESSAGE.Set_Token('RULE_STMT_ID',p_rule_stmt_rec.object_id);
145 FND_MSG_PUB.ADD;
146 END IF;
147 CLOSE check_rule_statement_csr;
148 END IF;
149
150 --Check Mandatory type and position is valid
151 IF ((p_rule_stmt_rec.operator = 'INSTALLED') OR
152 (p_rule_stmt_rec.operator = 'HAVE') OR
153 (p_rule_stmt_rec.operator = 'MUST_HAVE') OR
154 (p_rule_stmt_rec.operator = 'SAME') )
155 THEN
156 --Check MANDATORY Type
157 OPEN check_rule_type_csr(p_rule_stmt_rec.rule_id);
158 FETCH check_rule_type_csr INTO l_rule_type;
159 IF (l_rule_type <> 'MANDATORY' AND l_rule_type <> 'FLEET') THEN
160 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_TYPE_INV');
161 FND_MESSAGE.Set_Token('RULE_TYPE',l_rule_type);
162 FND_MSG_PUB.ADD;
163 END IF;
164 CLOSE check_rule_type_csr;
165
166 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 03-Dec-2007
167 -- Check that the fleet type rules are not created with the object type "total quantity of children"
168 IF(p_rule_stmt_rec.object_type = 'TOT_CHILD_QUANTITY' AND l_rule_type = 'FLEET') THEN
169 FND_MESSAGE.Set_Name('AHL','AHL_MC_QRUL_TYP_OBJTY_INV');
170 FND_MESSAGE.Set_Token('OPERATOR',get_rule_oper(p_rule_stmt_rec.operator, p_rule_stmt_rec.negation_flag));
171 FND_MESSAGE.Set_Token('RULE_TYPE',get_fnd_lkup_meaning('AHL_MC_RULE_TYPES', l_rule_type));
172 FND_MESSAGE.Set_Token('OBJ_TYPE',get_fnd_lkup_meaning('AHL_MC_RULE_OBJECT_TYPES', p_rule_stmt_rec.object_type));
173 FND_MSG_PUB.ADD;
174 END IF;
175
176 --Check Valid Position
177 OPEN check_position_id_csr(p_rule_stmt_rec.rule_id, p_rule_stmt_rec.subject_id);
178 FETCH check_position_id_csr INTO l_junk;
179 IF (check_position_id_csr%NOTFOUND) THEN
180 FND_MESSAGE.Set_Name('AHL','AHL_MC_PATH_POS_ID_INV');
181 FND_MESSAGE.Set_Token('POSITION_ID',p_rule_stmt_rec.subject_id);
182 FND_MSG_PUB.ADD;
183 END IF;
184 CLOSE check_position_id_csr;
185 END IF;
186
187 -----------Install statement type--------------
188 IF (p_rule_stmt_rec.operator = 'INSTALLED')
189 THEN
190 --Check subj_type is position and object info are null
191 IF ((p_rule_stmt_rec.subject_type <> 'POSITION') OR
192 (p_rule_stmt_rec.object_type IS NOT NULL) OR
193 (p_rule_stmt_rec.object_id IS NOT NULL))
194 THEN
195 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_INV');
196 FND_MESSAGE.Set_Token('OPERATOR',get_rule_oper(p_rule_stmt_rec.operator, p_rule_stmt_rec.negation_flag));
197 FND_MSG_PUB.ADD;
198 END IF;
199
200 -----------Have statement type--------------
201 ELSIF ((p_rule_stmt_rec.operator = 'HAVE') OR
202 (p_rule_stmt_rec.operator = 'MUST_HAVE'))
203 THEN
204 --Check subj_type is position and object info are null
205 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 03-Dec-2007
206 -- Modified the following check as the new rule is compatible only for the operator - object_type
207 -- combination of MUST_HAVE - TOT_CHILD_QUANTITY
208
209 -- Therefore for operator as HAVE or MUST_HAVE, the invalid rule statement error should be thrown if: -
210 -- 1) subject_type is not POSITION
211 -- OR
212 -- 2) object_type is not among ITEM, CONFIGURATION and TOT_CHILD_QUANTITY
213 -- OR
214 -- 3) (operator - object_type) combination is (HAVE - TOT_CHILD_QUANTITY) or (Must Not Have - TOT_CHILD_QUANTITY)
215 IF ((p_rule_stmt_rec.subject_type <> 'POSITION')
216 OR
217 ((p_rule_stmt_rec.object_type <> 'ITEM') AND
218 (p_rule_stmt_rec.object_type <> 'CONFIGURATION') AND
219 (p_rule_stmt_rec.object_type <> 'TOT_CHILD_QUANTITY'))
220 OR
221 ((p_rule_stmt_rec.operator <> 'MUST_HAVE' OR (p_rule_stmt_rec.operator = 'MUST_HAVE' AND NVL(p_rule_stmt_rec.negation_flag,'F') = 'T')) AND
222 (p_rule_stmt_rec.object_type = 'TOT_CHILD_QUANTITY'))
223 )
224 THEN
225 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_INV');
226 FND_MESSAGE.Set_Token('OPERATOR',get_rule_oper(p_rule_stmt_rec.operator, p_rule_stmt_rec.negation_flag));
227 FND_MSG_PUB.ADD;
228 END IF;
229
230 IF (p_rule_stmt_rec.object_type = 'ITEM') THEN
231 OPEN check_item_id_csr(p_rule_stmt_rec.object_id);
232 FETCH check_item_id_csr INTO l_junk;
233 IF (check_item_id_csr%NOTFOUND) THEN
234 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
235 FND_MESSAGE.Set_Token('INV_ITEM',p_rule_stmt_rec.object_id);
236 FND_MSG_PUB.ADD;
237 END IF;
238 CLOSE check_item_id_csr;
239 ELSIF (p_rule_stmt_rec.object_type = 'CONFIGURATION') THEN
240 OPEN check_mc_id_csr(p_rule_stmt_rec.object_id, p_rule_stmt_rec.object_attribute1);
241 FETCH check_mc_id_csr INTO l_junk;
242 IF (check_mc_id_csr%NOTFOUND) THEN
243 FND_MESSAGE.Set_Name('AHL','AHL_MC_MC_ID_INV');
244 FND_MESSAGE.Set_Token('MC_ID',p_rule_stmt_rec.object_id);
245 FND_MESSAGE.Set_Token('VER',p_rule_stmt_rec.object_attribute1);
246 FND_MSG_PUB.ADD;
247 END IF;
248 CLOSE check_mc_id_csr;
249
250 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 03-Dec-2007
251 -- If the new rule is being created, then validate the various parameters by calling the
252 -- new API validate_pos_quantity_rule.
253 ELSIF((p_rule_stmt_rec.operator = 'MUST_HAVE') AND
254 (NVL(p_rule_stmt_rec.negation_flag,'F') = 'F') AND
255 (p_rule_stmt_rec.object_type = 'TOT_CHILD_QUANTITY')) THEN
256 validate_pos_quantity_rule(p_rule_stmt_rec);
257 END IF;
258
259 -------SAME Statements-------------
260 ELSIF (p_rule_stmt_rec.operator = 'SAME')
261 THEN
262 --Check subj_type is position and object info are null
263 IF ((p_rule_stmt_rec.subject_type <> 'POSITION') OR
264 ((p_rule_stmt_rec.object_type <> 'ITEM_AS_POSITION') AND
265 (p_rule_stmt_rec.object_type <> 'CONFIG_AS_POSITION')))
266 THEN
267 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_INV');
268 FND_MESSAGE.Set_Token('OPERATOR',get_rule_oper(p_rule_stmt_rec.operator, p_rule_stmt_rec.negation_flag));
269 FND_MSG_PUB.ADD;
270 END IF;
271
272 --Check Valid Position for object_id
273 OPEN check_position_id_csr(p_rule_stmt_rec.rule_id, p_rule_stmt_rec.object_id);
274 FETCH check_position_id_csr INTO l_junk;
275 IF (check_position_id_csr%NOTFOUND) THEN
276 FND_MESSAGE.Set_Name('AHL','AHL_MC_PATH_POS_ID_INV');
277 FND_MESSAGE.Set_Token('POSITION_ID',p_rule_stmt_rec.object_id);
278 FND_MSG_PUB.ADD;
279 END IF;
280 CLOSE check_position_id_csr;
281
282 END IF;
283
284 --Check Fleet Rule Statements
285 IF ((p_rule_stmt_rec.operator = 'FLEET_QTY_GT') OR
286 (p_rule_stmt_rec.operator = 'FLEET_QTY_EQ') OR
287 (p_rule_stmt_rec.operator = 'FLEET_QTY_LT') OR
288 (p_rule_stmt_rec.operator = 'FLEET_PCTG_GT') OR
289 (p_rule_stmt_rec.operator = 'FLEET_PCTG_LT') OR
290 (p_rule_stmt_rec.operator = 'FLEET_PCTG_EQ') )
291 THEN
292 --Check FLEET Type
293 OPEN check_rule_type_csr(p_rule_stmt_rec.rule_id);
294 FETCH check_rule_type_csr INTO l_rule_type;
295 IF (l_rule_type <> 'FLEET') THEN
296 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_TYPE_INV');
297 FND_MESSAGE.Set_Token('RULE_TYPE',l_rule_type);
298 FND_MSG_PUB.ADD;
299 END IF;
300 CLOSE check_rule_type_csr;
301
302 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 03-Dec-2007
303 -- Check that the fleet type rules are not created with the object type "total quantity of children"
304 IF(p_rule_stmt_rec.object_type = 'TOT_CHILD_QUANTITY') THEN
305 FND_MESSAGE.Set_Name('AHL','AHL_MC_QRUL_TYP_OBJTY_INV');
306 FND_MESSAGE.Set_Token('OPERATOR',get_rule_oper(p_rule_stmt_rec.operator, p_rule_stmt_rec.negation_flag));
307 FND_MESSAGE.Set_Token('RULE_TYPE',get_fnd_lkup_meaning('AHL_MC_RULE_TYPES', l_rule_type));
308 FND_MESSAGE.Set_Token('OBJ_TYPE',get_fnd_lkup_meaning('AHL_MC_RULE_OBJECT_TYPES', p_rule_stmt_rec.object_type));
309 FND_MSG_PUB.ADD;
310 END IF;
311
312 --Check Valid Position
313 OPEN check_position_id_csr(p_rule_stmt_rec.rule_id, p_rule_stmt_rec.subject_id);
314 FETCH check_position_id_csr INTO l_junk;
315 IF (check_position_id_csr%NOTFOUND) THEN
316 FND_MESSAGE.Set_Name('AHL','AHL_MC_PATH_POS_ID_INV');
317 FND_MESSAGE.Set_Token('POSITION_ID',p_rule_stmt_rec.subject_id);
318 FND_MSG_PUB.ADD;
319 END IF;
320 CLOSE check_position_id_csr;
321
322 --Make sure object type is item
323 IF (p_rule_stmt_rec.object_type <> 'ITEM') THEN
324 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_INV');
325 FND_MESSAGE.Set_Token('OPERATOR',get_rule_oper(p_rule_stmt_rec.operator, p_rule_stmt_rec.negation_flag));
326 FND_MSG_PUB.ADD;
327 END IF;
328
329 --Make sure item id is valid
330 OPEN check_item_id_csr(p_rule_stmt_rec.object_id);
331 FETCH check_item_id_csr INTO l_junk;
332 IF (check_item_id_csr%NOTFOUND) THEN
333 FND_MESSAGE.Set_Name('AHL','AHL_MC_INV_INVALID');
334 FND_MESSAGE.Set_Token('INV_ITEM',p_rule_stmt_rec.object_id);
335 FND_MSG_PUB.ADD;
336 END IF;
337 CLOSE check_item_id_csr;
338
339 --Check quantity >= 0
340 IF ((p_rule_stmt_rec.operator = 'FLEET_QTY_GT') OR
341 (p_rule_stmt_rec.operator = 'FLEET_QTY_EQ') OR
342 (p_rule_stmt_rec.operator = 'FLEET_QTY_LT')) THEN
343 IF (TO_NUMBER(p_rule_stmt_rec.object_attribute1) < 0) THEN
344 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_FLT_ATTR_INV');
345 FND_MESSAGE.Set_Token('ATTR_VAL',p_rule_stmt_rec.object_attribute1);
346 FND_MSG_PUB.ADD;
347 END IF;
348 END IF;
349
350 IF ((p_rule_stmt_rec.operator = 'FLEET_PCTG_GT') OR
351 (p_rule_stmt_rec.operator = 'FLEET_PCTG_EQ') OR
352 (p_rule_stmt_rec.operator = 'FLEET_PCTG_LT')) THEN
353 IF (TO_NUMBER(p_rule_stmt_rec.object_attribute1) < 0 OR
354 TO_NUMBER(p_rule_stmt_rec.object_attribute1) > 100) THEN
355 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_FLT_ATTR_INV');
356 FND_MESSAGE.Set_Token('ATTR_VAL',p_rule_stmt_rec.object_attribute1);
357 FND_MSG_PUB.ADD;
358 END IF;
359 END IF;
360 END IF;
361
362 -- Check Error Message stack.
363 x_msg_count := FND_MSG_PUB.count_msg;
364 IF x_msg_count > 0 THEN
365 RAISE FND_API.G_EXC_ERROR;
366 END IF;
367 -- Standard call to get message count and if count is 1, get message info
368 FND_MSG_PUB.Count_And_Get
369 ( p_count => x_msg_count,
370 p_data => x_msg_data,
371 p_encoded => fnd_api.g_false
372 );
373
374 EXCEPTION
375 WHEN FND_API.G_EXC_ERROR THEN
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
378 p_data => x_msg_data,
379 p_encoded => fnd_api.g_false);
380 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
383 p_data => x_msg_data,
384 p_encoded => fnd_api.g_false);
385 WHEN OTHERS THEN
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
388 p_procedure_name => l_api_name,
389 p_error_text => SQLERRM);
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
394 END Validate_Rule_Stmt;
395
396 --------------------------------
397 -- Start of Comments --
398 -- Procedure name : Insert_Rule_Stmt
399 -- Type : Private
400 -- Function : Writes to DB the rule stmt
401 -- Pre-reqs :
402 -- Parameters :
403 --
404 -- Insert_Rule_Stmt Parameters:
405 -- p_x_rule_stmt_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type Required
406 --
407 -- End of Comments.
408
409 PROCEDURE Insert_Rule_Stmt (
410 p_api_version IN NUMBER,
411 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
412 p_commit IN VARCHAR2 := FND_API.G_FALSE,
413 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_count OUT NOCOPY NUMBER,
416 x_msg_data OUT NOCOPY VARCHAR2,
417 p_module IN VARCHAR2 := 'JSP',
418 p_x_rule_stmt_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type)
419 IS
420 --
421 CURSOR next_rule_stmt_id_csr IS
422 SELECT ahl_mc_rule_statements_s.nextval FROM DUAL;
423 --
424 l_api_version CONSTANT NUMBER := 1.0;
425 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Rule_Stmt';
426 --
427 BEGIN
428 -- Standard start of API savepoint
429 SAVEPOINT Insert_Rule_Stmt_pvt;
430 -- Initialize Procedure return status to success
431 x_return_status := FND_API.G_RET_STS_SUCCESS;
432 -- Standard call to check for call compatibility
433 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
434 G_PKG_NAME) THEN
435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
436 END IF;
437 -- Initialize message list if p_init_msg_list is set to TRUE
438 IF FND_API.To_Boolean(p_init_msg_list) THEN
439 FND_MSG_PUB.Initialize;
440 END IF;
441
442 --Check Status of MC allows for editing
443 /*IF NOT(check_mc_status(p_x_rule_stmt_rec.rule_id)) THEN
444 FND_MESSAGE.Set_Name('AHL','AHL_INVALID_MC_STATUS');
445 FND_MSG_PUB.ADD;
446 Raise FND_API.G_EXC_ERROR;
447 END IF; */
448
449 IF (p_module = 'JSP') THEN
450 IF (p_x_rule_stmt_rec.OBJECT_ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
451 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE1 := null;
452 END IF;
453 IF (p_x_rule_stmt_rec.OBJECT_ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
454 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE2 := null;
455 END IF;
456 IF (p_x_rule_stmt_rec.OBJECT_ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
457 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE3 := null;
458 END IF;
459 IF (p_x_rule_stmt_rec.OBJECT_ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
460 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE4 := null;
461 END IF;
462 IF (p_x_rule_stmt_rec.OBJECT_ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
463 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE5 := null;
464 END IF;
465 IF (p_x_rule_stmt_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
466 p_x_rule_stmt_rec.ATTRIBUTE_CATEGORY := null;
467 END IF;
468 IF (p_x_rule_stmt_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
469 p_x_rule_stmt_rec.ATTRIBUTE1 := null;
470 END IF;
471 IF (p_x_rule_stmt_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
472 p_x_rule_stmt_rec.ATTRIBUTE2 := null;
473 END IF;
474 IF (p_x_rule_stmt_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
475 p_x_rule_stmt_rec.ATTRIBUTE3 := null;
476 END IF;
477 IF (p_x_rule_stmt_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
478 p_x_rule_stmt_rec.ATTRIBUTE4 := null;
479 END IF;
480 IF (p_x_rule_stmt_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
481 p_x_rule_stmt_rec.ATTRIBUTE5 := null;
482 END IF;
483 IF (p_x_rule_stmt_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
484 p_x_rule_stmt_rec.ATTRIBUTE6 := null;
485 END IF;
486 IF (p_x_rule_stmt_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
487 p_x_rule_stmt_rec.ATTRIBUTE7 := null;
488 END IF;
489 IF (p_x_rule_stmt_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
490 p_x_rule_stmt_rec.ATTRIBUTE8 := null;
491 END IF;
492 IF (p_x_rule_stmt_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
493 p_x_rule_stmt_rec.ATTRIBUTE9 := null;
494 END IF;
495 IF (p_x_rule_stmt_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
496 p_x_rule_stmt_rec.ATTRIBUTE10 := null;
497 END IF;
498 IF (p_x_rule_stmt_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
499 p_x_rule_stmt_rec.ATTRIBUTE11 := null;
500 END IF;
501 IF (p_x_rule_stmt_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
502 p_x_rule_stmt_rec.ATTRIBUTE12 := null;
503 END IF;
504 IF (p_x_rule_stmt_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
505 p_x_rule_stmt_rec.ATTRIBUTE13 := null;
506 END IF;
507 IF (p_x_rule_stmt_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
508 p_x_rule_stmt_rec.ATTRIBUTE14 := null;
509 END IF;
510 IF (p_x_rule_stmt_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
511 p_x_rule_stmt_rec.ATTRIBUTE15 := null;
512 END IF;
513 END IF;
514
515 --Validate Rule Statement;
516 Validate_Rule_Stmt( p_api_version => 1.0,
517 p_rule_stmt_rec => p_x_rule_stmt_rec,
518 x_return_status => x_return_status,
519 x_msg_count => x_msg_count,
520 x_msg_data => x_msg_data);
521
522 -- Check return status.
523 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
524 RAISE FND_API.G_EXC_ERROR;
525 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
527 END IF;
528
529 IF (p_x_rule_stmt_rec.rule_statement_id IS NULL) THEN
530 OPEN next_rule_stmt_id_csr;
531 FETCH next_rule_stmt_id_csr INTO p_x_rule_stmt_rec.rule_statement_id;
532 CLOSE next_rule_stmt_id_csr;
533 END IF;
534
535 INSERT INTO ahl_mc_rule_statements(
536 RULE_STATEMENT_ID,
537 OBJECT_VERSION_NUMBER,
538 LAST_UPDATE_DATE,
539 LAST_UPDATED_BY,
540 CREATION_DATE,
541 CREATED_BY,
542 LAST_UPDATE_LOGIN,
543 RULE_ID ,
544 TOP_RULE_STMT_FLAG ,
545 NEGATION_FLAG ,
546 SUBJECT_ID ,
547 SUBJECT_TYPE ,
548 OPERATOR ,
549 OBJECT_ID ,
550 OBJECT_TYPE ,
551 OBJECT_ATTRIBUTE1 ,
552 OBJECT_ATTRIBUTE2 ,
553 OBJECT_ATTRIBUTE3 ,
554 OBJECT_ATTRIBUTE4 ,
555 OBJECT_ATTRIBUTE5 ,
556 ATTRIBUTE_CATEGORY,
557 ATTRIBUTE1,
558 ATTRIBUTE2,
559 ATTRIBUTE3,
560 ATTRIBUTE4,
561 ATTRIBUTE5,
562 ATTRIBUTE6,
563 ATTRIBUTE7,
564 ATTRIBUTE8,
565 ATTRIBUTE9,
566 ATTRIBUTE10,
567 ATTRIBUTE11,
568 ATTRIBUTE12,
569 ATTRIBUTE13,
570 ATTRIBUTE14,
571 ATTRIBUTE15
572 )
573 VALUES (
574 p_x_rule_stmt_rec.rule_statement_id,
575 1,
576 sysdate,
577 fnd_global.user_id,
578 sysdate,
579 fnd_global.user_id,
580 fnd_global.login_id,
581 p_x_rule_stmt_rec.RULE_ID ,
582 p_x_rule_stmt_rec.TOP_RULE_STMT_FLAG ,
583 p_x_rule_stmt_rec.NEGATION_FLAG ,
584 p_x_rule_stmt_rec.SUBJECT_ID ,
585 p_x_rule_stmt_rec.SUBJECT_TYPE ,
586 p_x_rule_stmt_rec.OPERATOR ,
587 p_x_rule_stmt_rec.OBJECT_ID ,
588 p_x_rule_stmt_rec.OBJECT_TYPE ,
589 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE1 ,
590 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE2 ,
591 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE3 ,
592 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE4 ,
593 p_x_rule_stmt_rec.OBJECT_ATTRIBUTE5 ,
594 p_x_rule_stmt_rec.ATTRIBUTE_CATEGORY,
595 p_x_rule_stmt_rec.ATTRIBUTE1,
596 p_x_rule_stmt_rec.ATTRIBUTE2,
597 p_x_rule_stmt_rec.ATTRIBUTE3,
598 p_x_rule_stmt_rec.ATTRIBUTE4,
599 p_x_rule_stmt_rec.ATTRIBUTE5,
600 p_x_rule_stmt_rec.ATTRIBUTE6,
601 p_x_rule_stmt_rec.ATTRIBUTE7,
602 p_x_rule_stmt_rec.ATTRIBUTE8,
603 p_x_rule_stmt_rec.ATTRIBUTE9,
604 p_x_rule_stmt_rec.ATTRIBUTE10,
605 p_x_rule_stmt_rec.ATTRIBUTE11,
606 p_x_rule_stmt_rec.ATTRIBUTE12,
607 p_x_rule_stmt_rec.ATTRIBUTE13,
608 p_x_rule_stmt_rec.ATTRIBUTE14,
609 p_x_rule_stmt_rec.ATTRIBUTE15
610 )
611 RETURNING object_version_number INTO p_x_rule_stmt_rec.object_version_number;
612
613 -- Standard check of p_commit
614 IF FND_API.TO_BOOLEAN(p_commit) THEN
615 COMMIT WORK;
616 END IF;
617 -- Standard call to get message count and if count is 1, get message info
618 FND_MSG_PUB.Count_And_Get
619 ( p_count => x_msg_count,
620 p_data => x_msg_data,
621 p_encoded => fnd_api.g_false
622 );
623 EXCEPTION
624 WHEN FND_API.G_EXC_ERROR THEN
625 Rollback to Insert_Rule_Stmt_pvt;
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
628 p_data => x_msg_data,
629 p_encoded => fnd_api.g_false);
630 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
631 Rollback to Insert_Rule_Stmt_pvt;
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
633 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
634 p_data => x_msg_data,
635 p_encoded => fnd_api.g_false);
636 WHEN OTHERS THEN
637 Rollback to Insert_Rule_Stmt_pvt;
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
640 p_procedure_name => l_api_name,
641 p_error_text => SQLERRM);
642 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
643 p_data => x_msg_data,
644 p_encoded => fnd_api.g_false);
645 END Insert_Rule_Stmt;
646
647
648 --------------------------------
649 -- Start of Comments --
650 -- Procedure name : Update_Rule_Stmt
651 -- Type : Private
652 -- Function : Writes to DB the rule stmt
653 -- Pre-reqs :
654 -- Parameters :
655 --
656 -- Update_Rule_Stmt Parameters:
657 -- p_x_rule_stmt_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type Required
658 --
659 -- End of Comments.
660
661 PROCEDURE Update_Rule_Stmt (
662 p_api_version IN NUMBER,
663 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
664 p_commit IN VARCHAR2 := FND_API.G_FALSE,
665 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_msg_count OUT NOCOPY NUMBER,
668 x_msg_data OUT NOCOPY VARCHAR2,
669 p_module IN VARCHAR2 := 'JSP',
670 p_rule_stmt_rec IN AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type)
671 IS
672 --
673 CURSOR ahl_mc_rule_stmt_csr (p_rstmt_id IN NUMBER) IS
674 SELECT *
675 FROM AHL_MC_RULE_STATEMENTS
676 WHERE rule_statement_id = p_rstmt_id;
677 --
678 l_api_version CONSTANT NUMBER := 1.0;
679 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rule_Stmt';
680 l_old_rstmt_rec ahl_mc_rule_stmt_csr%ROWTYPE;
681 l_rule_stmt_rec AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type;
682 --
683 BEGIN
684 -- Standard start of API savepoint
685 SAVEPOINT Update_Rule_Stmt_pvt;
686 -- Initialize Procedure return status to success
687 x_return_status := FND_API.G_RET_STS_SUCCESS;
688 -- Standard call to check for call compatibility
689 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
690 G_PKG_NAME) THEN
691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
692 END IF;
693 -- Initialize message list if p_init_msg_list is set to TRUE
694 IF FND_API.To_Boolean(p_init_msg_list) THEN
695 FND_MSG_PUB.Initialize;
696 END IF;
697
698 --Check that rule_statement_id is valid
699 OPEN ahl_mc_rule_stmt_csr(p_rule_stmt_rec.rule_statement_id);
700 FETCH ahl_mc_rule_stmt_csr INTO l_old_rstmt_rec;
701 IF (ahl_mc_rule_stmt_csr%NOTFOUND) THEN
702 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_ID_INV');
703 FND_MESSAGE.Set_Token('RULE_STMT_ID',p_rule_stmt_rec.rule_statement_id);
704 FND_MSG_PUB.ADD;
705 CLOSE ahl_mc_rule_stmt_csr;
706 Raise FND_API.G_EXC_ERROR;
707 END IF;
708 CLOSE ahl_mc_rule_stmt_csr;
709
710 --Check Status of MC allows for editing
711 /*IF NOT(check_mc_status(p_rule_stmt_rec.rule_id)) THEN
712 FND_MESSAGE.Set_Name('AHL','AHL_INVALID_MC_STATUS');
713 FND_MSG_PUB.ADD;
714 Raise FND_API.G_EXC_ERROR;
715 END IF; */
716
717 l_rule_stmt_rec := p_rule_stmt_rec;
718
719 -- Check Object version number.
720 IF (l_old_rstmt_rec.object_version_number <> l_rule_stmt_rec.object_version_number) THEN
721 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
722 FND_MSG_PUB.ADD;
723 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
724 END IF;
725
726 --Do NULL/G_MISS conversion
727 IF (p_module = 'JSP') THEN
728
729 IF (l_rule_stmt_rec.OBJECT_ATTRIBUTE1 IS NULL) THEN
730 l_rule_stmt_rec.OBJECT_ATTRIBUTE1 := l_old_rstmt_rec.OBJECT_ATTRIBUTE1;
731 ELSIF (l_rule_stmt_rec.OBJECT_ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
732 l_rule_stmt_rec.OBJECT_ATTRIBUTE1 := NULL;
733 END IF;
734 IF (l_rule_stmt_rec.OBJECT_ATTRIBUTE2 IS NULL) THEN
735 l_rule_stmt_rec.OBJECT_ATTRIBUTE2 := l_old_rstmt_rec.OBJECT_ATTRIBUTE2;
736 ELSIF (l_rule_stmt_rec.OBJECT_ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
737 l_rule_stmt_rec.OBJECT_ATTRIBUTE2 := NULL;
738 END IF;
739 IF (l_rule_stmt_rec.OBJECT_ATTRIBUTE3 IS NULL) THEN
740 l_rule_stmt_rec.OBJECT_ATTRIBUTE3 := l_old_rstmt_rec.OBJECT_ATTRIBUTE3;
741 ELSIF (l_rule_stmt_rec.OBJECT_ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
742 l_rule_stmt_rec.OBJECT_ATTRIBUTE3 := NULL;
743 END IF;
744 IF (l_rule_stmt_rec.OBJECT_ATTRIBUTE4 IS NULL) THEN
745 l_rule_stmt_rec.OBJECT_ATTRIBUTE4 := l_old_rstmt_rec.OBJECT_ATTRIBUTE4;
746 ELSIF (l_rule_stmt_rec.OBJECT_ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
747 l_rule_stmt_rec.OBJECT_ATTRIBUTE4 := NULL;
748 END IF;
749 IF (l_rule_stmt_rec.OBJECT_ATTRIBUTE5 IS NULL) THEN
750 l_rule_stmt_rec.OBJECT_ATTRIBUTE5 := l_old_rstmt_rec.OBJECT_ATTRIBUTE5;
751 ELSIF (l_rule_stmt_rec.OBJECT_ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
752 l_rule_stmt_rec.OBJECT_ATTRIBUTE5 := NULL;
753 END IF;
754
755 IF (l_rule_stmt_rec.ATTRIBUTE_CATEGORY IS NULL) THEN
756 l_rule_stmt_rec.ATTRIBUTE_CATEGORY := l_old_rstmt_rec.ATTRIBUTE_CATEGORY;
757 ELSIF (l_rule_stmt_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR) THEN
758 l_rule_stmt_rec.ATTRIBUTE_CATEGORY := NULL;
759 END IF;
760 IF (l_rule_stmt_rec.ATTRIBUTE1 IS NULL) THEN
761 l_rule_stmt_rec.ATTRIBUTE1 := l_old_rstmt_rec.ATTRIBUTE1;
762 ELSIF (l_rule_stmt_rec.ATTRIBUTE1 = FND_API.G_MISS_CHAR) THEN
763 l_rule_stmt_rec.ATTRIBUTE1 := NULL;
764 END IF;
765 IF (l_rule_stmt_rec.ATTRIBUTE2 IS NULL) THEN
766 l_rule_stmt_rec.ATTRIBUTE2 := l_old_rstmt_rec.ATTRIBUTE2;
767 ELSIF (l_rule_stmt_rec.ATTRIBUTE2 = FND_API.G_MISS_CHAR) THEN
768 l_rule_stmt_rec.ATTRIBUTE2 := NULL;
769 END IF;
770 IF (l_rule_stmt_rec.ATTRIBUTE3 IS NULL) THEN
771 l_rule_stmt_rec.ATTRIBUTE3 := l_old_rstmt_rec.ATTRIBUTE3;
772 ELSIF (l_rule_stmt_rec.ATTRIBUTE3 = FND_API.G_MISS_CHAR) THEN
773 l_rule_stmt_rec.ATTRIBUTE3 := NULL;
774 END IF;
775 IF (l_rule_stmt_rec.ATTRIBUTE4 IS NULL) THEN
776 l_rule_stmt_rec.ATTRIBUTE4 := l_old_rstmt_rec.ATTRIBUTE4;
777 ELSIF (l_rule_stmt_rec.ATTRIBUTE4 = FND_API.G_MISS_CHAR) THEN
778 l_rule_stmt_rec.ATTRIBUTE4 := NULL;
779 END IF;
780 IF (l_rule_stmt_rec.ATTRIBUTE5 IS NULL) THEN
781 l_rule_stmt_rec.ATTRIBUTE5 := l_old_rstmt_rec.ATTRIBUTE5;
782 ELSIF (l_rule_stmt_rec.ATTRIBUTE5 = FND_API.G_MISS_CHAR) THEN
783 l_rule_stmt_rec.ATTRIBUTE5 := NULL;
784 END IF;
785 IF (l_rule_stmt_rec.ATTRIBUTE6 IS NULL) THEN
786 l_rule_stmt_rec.ATTRIBUTE6 := l_old_rstmt_rec.ATTRIBUTE6;
787 ELSIF (l_rule_stmt_rec.ATTRIBUTE6 = FND_API.G_MISS_CHAR) THEN
788 l_rule_stmt_rec.ATTRIBUTE6 := NULL;
789 END IF;
790 IF (l_rule_stmt_rec.ATTRIBUTE7 IS NULL) THEN
791 l_rule_stmt_rec.ATTRIBUTE7 := l_old_rstmt_rec.ATTRIBUTE7;
792 ELSIF (l_rule_stmt_rec.ATTRIBUTE7 = FND_API.G_MISS_CHAR) THEN
793 l_rule_stmt_rec.ATTRIBUTE7 := NULL;
794 END IF;
795 IF (l_rule_stmt_rec.ATTRIBUTE8 IS NULL) THEN
796 l_rule_stmt_rec.ATTRIBUTE8 := l_old_rstmt_rec.ATTRIBUTE8;
797 ELSIF (l_rule_stmt_rec.ATTRIBUTE8 = FND_API.G_MISS_CHAR) THEN
798 l_rule_stmt_rec.ATTRIBUTE8 := NULL;
799 END IF;
800 IF (l_rule_stmt_rec.ATTRIBUTE9 IS NULL) THEN
801 l_rule_stmt_rec.ATTRIBUTE9 := l_old_rstmt_rec.ATTRIBUTE9;
802 ELSIF (l_rule_stmt_rec.ATTRIBUTE9 = FND_API.G_MISS_CHAR) THEN
803 l_rule_stmt_rec.ATTRIBUTE9 := NULL;
804 END IF;
805 IF (l_rule_stmt_rec.ATTRIBUTE10 IS NULL) THEN
806 l_rule_stmt_rec.ATTRIBUTE10 := l_old_rstmt_rec.ATTRIBUTE10;
807 ELSIF (l_rule_stmt_rec.ATTRIBUTE10 = FND_API.G_MISS_CHAR) THEN
808 l_rule_stmt_rec.ATTRIBUTE10 := NULL;
809 END IF;
810 IF (l_rule_stmt_rec.ATTRIBUTE11 IS NULL) THEN
811 l_rule_stmt_rec.ATTRIBUTE11 := l_old_rstmt_rec.ATTRIBUTE11;
812 ELSIF (l_rule_stmt_rec.ATTRIBUTE11 = FND_API.G_MISS_CHAR) THEN
813 l_rule_stmt_rec.ATTRIBUTE11 := NULL;
814 END IF;
815 IF (l_rule_stmt_rec.ATTRIBUTE12 IS NULL) THEN
816 l_rule_stmt_rec.ATTRIBUTE12 := l_old_rstmt_rec.ATTRIBUTE12;
817 ELSIF (l_rule_stmt_rec.ATTRIBUTE12 = FND_API.G_MISS_CHAR) THEN
818 l_rule_stmt_rec.ATTRIBUTE12 := NULL;
819 END IF;
820 IF (l_rule_stmt_rec.ATTRIBUTE13 IS NULL) THEN
821 l_rule_stmt_rec.ATTRIBUTE13 := l_old_rstmt_rec.ATTRIBUTE13;
822 ELSIF (l_rule_stmt_rec.ATTRIBUTE13 = FND_API.G_MISS_CHAR) THEN
823 l_rule_stmt_rec.ATTRIBUTE13 := NULL;
824 END IF;
825 IF (l_rule_stmt_rec.ATTRIBUTE14 IS NULL) THEN
826 l_rule_stmt_rec.ATTRIBUTE14 := l_old_rstmt_rec.ATTRIBUTE14;
827 ELSIF (l_rule_stmt_rec.ATTRIBUTE14 = FND_API.G_MISS_CHAR) THEN
828 l_rule_stmt_rec.ATTRIBUTE14 := NULL;
829 END IF;
830 IF (l_rule_stmt_rec.ATTRIBUTE15 IS NULL) THEN
831 l_rule_stmt_rec.ATTRIBUTE15 := l_old_rstmt_rec.ATTRIBUTE15;
832 ELSIF (l_rule_stmt_rec.ATTRIBUTE15 = FND_API.G_MISS_CHAR) THEN
833 l_rule_stmt_rec.ATTRIBUTE15 := NULL;
834 END IF;
835
836 END IF;
837
838 --Validate Rule Statement;
839 Validate_Rule_Stmt( p_api_version => 1.0,
840 p_rule_stmt_rec => l_rule_stmt_rec,
841 x_return_status => x_return_status,
842 x_msg_count => x_msg_count,
843 x_msg_data => x_msg_data);
844
845 -- Check return status.
846 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
847 RAISE FND_API.G_EXC_ERROR;
848 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
850 END IF;
851
852 UPDATE ahl_mc_rule_statements SET
853 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
854 LAST_UPDATE_DATE = sysdate,
855 LAST_UPDATED_BY = fnd_global.USER_ID,
856 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
857 RULE_ID = l_rule_stmt_rec.RULE_ID,
858 TOP_RULE_STMT_FLAG = l_rule_stmt_rec.TOP_RULE_STMT_FLAG,
859 NEGATION_FLAG =l_rule_stmt_rec.NEGATION_FLAG ,
860 SUBJECT_ID =l_rule_stmt_rec.SUBJECT_ID ,
861 SUBJECT_TYPE =l_rule_stmt_rec.SUBJECT_TYPE,
862 OPERATOR =l_rule_stmt_rec.OPERATOR ,
863 OBJECT_ID =l_rule_stmt_rec.OBJECT_ID ,
864 OBJECT_TYPE =l_rule_stmt_rec.OBJECT_TYPE,
865 OBJECT_ATTRIBUTE1 =l_rule_stmt_rec.OBJECT_ATTRIBUTE1,
866 OBJECT_ATTRIBUTE2 =l_rule_stmt_rec.OBJECT_ATTRIBUTE2,
867 OBJECT_ATTRIBUTE3 =l_rule_stmt_rec.OBJECT_ATTRIBUTE3,
868 OBJECT_ATTRIBUTE4 =l_rule_stmt_rec.OBJECT_ATTRIBUTE4,
869 OBJECT_ATTRIBUTE5 =l_rule_stmt_rec.OBJECT_ATTRIBUTE5,
870 ATTRIBUTE_CATEGORY = l_rule_stmt_rec.ATTRIBUTE_CATEGORY,
871 ATTRIBUTE1 = l_rule_stmt_rec.ATTRIBUTE1,
872 ATTRIBUTE2 = l_rule_stmt_rec.ATTRIBUTE2,
873 ATTRIBUTE3 = l_rule_stmt_rec.ATTRIBUTE3,
874 ATTRIBUTE4 = l_rule_stmt_rec.ATTRIBUTE4,
875 ATTRIBUTE5 = l_rule_stmt_rec.ATTRIBUTE5,
876 ATTRIBUTE6 = l_rule_stmt_rec.ATTRIBUTE6,
877 ATTRIBUTE7 = l_rule_stmt_rec.ATTRIBUTE7,
878 ATTRIBUTE8 = l_rule_stmt_rec.ATTRIBUTE8,
879 ATTRIBUTE9 = l_rule_stmt_rec.ATTRIBUTE9,
880 ATTRIBUTE10 = l_rule_stmt_rec.ATTRIBUTE10,
881 ATTRIBUTE11 = l_rule_stmt_rec.ATTRIBUTE11,
882 ATTRIBUTE12 = l_rule_stmt_rec.ATTRIBUTE12,
883 ATTRIBUTE13 = l_rule_stmt_rec.ATTRIBUTE13,
884 ATTRIBUTE14 = l_rule_stmt_rec.ATTRIBUTE14,
885 ATTRIBUTE15 = l_rule_stmt_rec.ATTRIBUTE15
886 WHERE RULE_STATEMENT_ID = l_rule_stmt_rec.rule_statement_id;
887
888 -- Standard check of p_commit
889 IF FND_API.TO_BOOLEAN(p_commit) THEN
890 COMMIT WORK;
891 END IF;
892 -- Standard call to get message count and if count is 1, get message info
893 FND_MSG_PUB.Count_And_Get
894 ( p_count => x_msg_count,
895 p_data => x_msg_data,
896 p_encoded => fnd_api.g_false);
897
898 EXCEPTION
899 WHEN FND_API.G_EXC_ERROR THEN
900 Rollback to Update_Rule_Stmt_pvt;
901 x_return_status := FND_API.G_RET_STS_ERROR;
902 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
903 p_data => x_msg_data,
904 p_encoded => fnd_api.g_false);
905 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
906 Rollback to Update_Rule_Stmt_pvt;
907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
908 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
909 p_data => x_msg_data,
910 p_encoded => fnd_api.g_false);
911 WHEN OTHERS THEN
912 Rollback to Update_Rule_Stmt_pvt;
913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
914 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
915 p_procedure_name => l_api_name,
916 p_error_text => SQLERRM);
917 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
918 p_data => x_msg_data,
919 p_encoded => fnd_api.g_false);
920 END Update_Rule_Stmt;
921
922 --------------------------------
923 -- Start of Comments --
924 -- Procedure name : Copy_Rule_Stmt
925 -- Type : Private
926 -- Function : Writes to DB the rule stmt by copying the rule stmt
927 -- Pre-reqs :
928 -- Parameters :
929 --
930 -- Copy_Rule_Stmt Parameters:
931 -- p_rule_stmt_id IN NUMBER Required. rule stmt to copy
932 -- p_to_rule_id IN NUMBER Required rule_id for insert purpose
933 -- p_to_mc_header_id IN NUMBER Requred. mc_header_id to copy to
934 -- x_rule_stmt_id OUT NOCOPY NUMBER the new rule_stmt_id
935 --
936 -- End of Comments.
937
938 PROCEDURE Copy_Rule_Stmt (
939 p_api_version IN NUMBER,
940 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
941 p_commit IN VARCHAR2 := FND_API.G_FALSE,
942 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
943 x_return_status OUT NOCOPY VARCHAR2,
944 x_msg_count OUT NOCOPY NUMBER,
945 x_msg_data OUT NOCOPY VARCHAR2,
946 p_rule_stmt_id IN NUMBER,
947 p_to_rule_id IN NUMBER,
948 p_to_mc_header_id IN NUMBER,
949 x_rule_stmt_id OUT NOCOPY NUMBER) IS
950 --
951 CURSOR get_rule_stmt_csr (p_rulestmt_id IN NUMBER) IS
952 SELECT *
953 FROM ahl_mc_rule_statements rs
954 WHERE rs.rule_statement_id = p_rulestmt_id;
955 --
956 l_api_version CONSTANT NUMBER := 1.0;
957 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Rule_Stmt';
958 l_stmt_rec get_rule_stmt_csr%ROWTYPE;
959 l_rstmt_rec AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type;
960 l_new_subject_id NUMBER;
961 l_new_object_id NUMBER;
962 --
963 BEGIN
964 -- Standard start of API savepoint
965 SAVEPOINT Copy_Rule_Stmt_pvt;
966 -- Initialize Procedure return status to success
967 x_return_status := FND_API.G_RET_STS_SUCCESS;
968 -- Standard call to check for call compatibility
969 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
970 G_PKG_NAME) THEN
971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972 END IF;
973 -- Initialize message list if p_init_msg_list is set to TRUE
974 IF FND_API.To_Boolean(p_init_msg_list) THEN
975 FND_MSG_PUB.Initialize;
976 END IF;
977
978 OPEN get_rule_stmt_csr(p_rule_stmt_id);
979 FETCH get_rule_stmt_csr INTO l_stmt_rec;
980
981 IF((l_stmt_rec.operator = 'OR') OR
982 (l_stmt_rec.operator = 'AND') OR
983 (l_stmt_rec.operator = 'IMPLIES') OR
984 (l_stmt_rec.operator = 'REQUIRES'))
985 THEN
986 --1) Copy the subject table
987 Copy_Rule_Stmt (p_api_version => 1.0,
988 p_commit => FND_API.G_FALSE,
989 p_rule_stmt_id => l_stmt_rec.subject_id,
990 p_to_rule_id => p_to_rule_id,
991 p_to_mc_header_id => p_to_mc_header_id,
992 x_rule_stmt_id => l_new_subject_id,
993 x_return_status => x_return_status,
994 x_msg_count => x_msg_count,
995 x_msg_data => x_msg_data);
996
997 --Verify that the rule stmt is null
998 IF (l_new_subject_id is NULL AND
999 x_return_Status = fnd_api.g_ret_sts_success) THEN
1000 x_rule_stmt_id := null;
1001 RETURN;
1002 END IF;
1003
1004 -- Check return status.
1005 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1006 RAISE FND_API.G_EXC_ERROR;
1007 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1009 END IF;
1010
1011
1012 --2) Build the object table
1013 Copy_Rule_Stmt (p_api_version => 1.0,
1014 p_commit => FND_API.G_FALSE,
1015 p_rule_stmt_id => l_stmt_rec.object_id,
1016 p_to_rule_id => p_to_rule_id,
1017 p_to_mc_header_id => p_to_mc_header_id,
1018 x_rule_stmt_id => l_new_object_id,
1019 x_return_status => x_return_status,
1020 x_msg_count => x_msg_count,
1021 x_msg_data => x_msg_data);
1022
1023 IF (l_new_object_id is NULL AND
1024 x_return_Status = fnd_api.g_ret_sts_success) THEN
1025 x_rule_stmt_id := null;
1026 RETURN;
1027 END IF;
1028
1029 -- Check return status.
1030 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1031 RAISE FND_API.G_EXC_ERROR;
1032 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1034 END IF;
1035
1036 --3) Change the rule stmt record and insert
1037 l_rstmt_rec.subject_id := l_new_subject_id;
1038 l_rstmt_rec.object_id := l_new_object_id;
1039 l_rstmt_rec.rule_id := p_to_rule_id;
1040
1041 l_rstmt_rec.top_rule_stmt_flag := l_stmt_rec.top_rule_stmt_flag;
1042 l_rstmt_rec.negation_flag := l_stmt_rec.negation_flag;
1043 l_rstmt_rec.subject_type := l_stmt_rec.subject_type;
1044 l_rstmt_rec.operator := l_stmt_rec.operator;
1045 l_rstmt_rec.object_type := l_stmt_rec.object_type;
1046 l_rstmt_rec.OBJECT_ATTRIBUTE1:=l_stmt_rec.OBJECT_ATTRIBUTE1;
1047 l_rstmt_rec.OBJECT_ATTRIBUTE2:=l_stmt_rec.OBJECT_ATTRIBUTE2;
1048 l_rstmt_rec.OBJECT_ATTRIBUTE3:=l_stmt_rec.OBJECT_ATTRIBUTE3;
1049 l_rstmt_rec.OBJECT_ATTRIBUTE4:=l_stmt_rec.OBJECT_ATTRIBUTE4;
1050 l_rstmt_rec.OBJECT_ATTRIBUTE5:=l_stmt_rec.OBJECT_ATTRIBUTE5;
1051 l_rstmt_rec.ATTRIBUTE_CATEGORY := l_stmt_rec.ATTRIBUTE_CATEGORY;
1052 l_rstmt_rec.ATTRIBUTE1:= l_stmt_rec.ATTRIBUTE1;
1053 l_rstmt_rec.ATTRIBUTE2:= l_stmt_rec.ATTRIBUTE2;
1054 l_rstmt_rec.ATTRIBUTE3:= l_stmt_rec.ATTRIBUTE3;
1055 l_rstmt_rec.ATTRIBUTE4:= l_stmt_rec.ATTRIBUTE4;
1056 l_rstmt_rec.ATTRIBUTE5:= l_stmt_rec.ATTRIBUTE5;
1057 l_rstmt_rec.ATTRIBUTE6:= l_stmt_rec.ATTRIBUTE6;
1058 l_rstmt_rec.ATTRIBUTE7:= l_stmt_rec.ATTRIBUTE7;
1059 l_rstmt_rec.ATTRIBUTE8:= l_stmt_rec.ATTRIBUTE8;
1060 l_rstmt_rec.ATTRIBUTE9 := l_stmt_rec.ATTRIBUTE9;
1061 l_rstmt_rec.ATTRIBUTE10:= l_stmt_rec.ATTRIBUTE10 ;
1062 l_rstmt_rec.ATTRIBUTE11:= l_stmt_rec.ATTRIBUTE11 ;
1063 l_rstmt_rec.ATTRIBUTE12:= l_stmt_rec.ATTRIBUTE12 ;
1064 l_rstmt_rec.ATTRIBUTE13:= l_stmt_rec.ATTRIBUTE13;
1065 l_rstmt_rec.ATTRIBUTE14:= l_stmt_rec.ATTRIBUTE14;
1066 l_rstmt_rec.ATTRIBUTE15:= l_stmt_rec.ATTRIBUTE15;
1067
1068 Insert_Rule_Stmt (p_api_version => 1.0,
1069 p_commit => FND_API.G_FALSE,
1070 p_module => 'PLSQL',
1071 p_x_rule_stmt_rec => l_rstmt_rec,
1072 x_return_status => x_return_status,
1073 x_msg_count => x_msg_count,
1074 x_msg_data => x_msg_data);
1075 x_rule_stmt_id := l_rstmt_rec.rule_statement_id;
1076
1077 ELSE
1078 --Convert for positions
1079 IF (l_stmt_rec.subject_type = 'POSITION') THEN
1080 AHL_MC_PATH_POSITION_PVT.Copy_Position (
1081 p_api_version => 1.0,
1082 p_commit => FND_API.G_FALSE,
1083 p_position_id => l_stmt_rec.subject_id,
1084 p_to_mc_header_id => p_to_mc_header_id,
1085 x_position_id => l_rstmt_rec.subject_id,
1086 x_return_status => x_return_status,
1087 x_msg_count => x_msg_count,
1088 x_msg_data => x_msg_data);
1089
1090 --If position can not be copied, return and not copy the rule.
1091 IF (l_rstmt_rec.subject_id is NULL AND
1092 x_return_Status = fnd_api.g_ret_sts_success) THEN
1093 x_rule_stmt_id := null;
1094 RETURN;
1095 END IF;
1096
1097 -- Check return status.
1098 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1099 RAISE FND_API.G_EXC_ERROR;
1100 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1102 END IF;
1103
1104 ELSE
1105 l_rstmt_rec.subject_id := l_stmt_rec.subject_id;
1106 END IF;
1107
1108 IF (l_stmt_rec.object_type = 'ITEM_AS_POSITION' OR
1109 l_stmt_rec.object_type = 'CONFIG_AS_POSITION') THEN
1110 AHL_MC_PATH_POSITION_PVT.Copy_Position (
1111 p_api_version => 1.0,
1112 p_commit => FND_API.G_FALSE,
1113 p_position_id => l_stmt_rec.object_id,
1114 p_to_mc_header_id => p_to_mc_header_id,
1115 x_position_id => l_rstmt_rec.object_id,
1116 x_return_status => x_return_status,
1117 x_msg_count => x_msg_count,
1118 x_msg_data => x_msg_data);
1119
1120 --If position can not be copied, return and not copy the rule.
1121 IF (l_rstmt_rec.object_id is NULL AND
1122 x_return_Status = fnd_api.g_ret_sts_success) THEN
1123 x_rule_stmt_id := null;
1124 RETURN;
1125 END IF;
1126
1127 -- Check return status.
1128 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1129 RAISE FND_API.G_EXC_ERROR;
1130 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1132 END IF;
1133
1134 ELSE
1135 l_rstmt_rec.object_id := l_stmt_rec.object_id;
1136 END IF;
1137
1138 l_rstmt_rec.rule_id := p_to_rule_id;
1139 l_rstmt_rec.top_rule_stmt_flag := l_stmt_rec.top_rule_stmt_flag;
1140 l_rstmt_rec.negation_flag := l_stmt_rec.negation_flag;
1141 l_rstmt_rec.subject_type := l_stmt_rec.subject_type;
1142 l_rstmt_rec.operator := l_stmt_rec.operator;
1143 l_rstmt_rec.object_type := l_stmt_rec.object_type;
1144 l_rstmt_rec.OBJECT_ATTRIBUTE1:=l_stmt_rec.OBJECT_ATTRIBUTE1;
1145 l_rstmt_rec.OBJECT_ATTRIBUTE2:=l_stmt_rec.OBJECT_ATTRIBUTE2;
1146 l_rstmt_rec.OBJECT_ATTRIBUTE3:=l_stmt_rec.OBJECT_ATTRIBUTE3;
1147 l_rstmt_rec.OBJECT_ATTRIBUTE4:=l_stmt_rec.OBJECT_ATTRIBUTE4;
1148 l_rstmt_rec.OBJECT_ATTRIBUTE5:=l_stmt_rec.OBJECT_ATTRIBUTE5;
1149 l_rstmt_rec.ATTRIBUTE_CATEGORY := l_stmt_rec.ATTRIBUTE_CATEGORY;
1150 l_rstmt_rec.ATTRIBUTE1:= l_stmt_rec.ATTRIBUTE1;
1151 l_rstmt_rec.ATTRIBUTE2:= l_stmt_rec.ATTRIBUTE2;
1152 l_rstmt_rec.ATTRIBUTE3:= l_stmt_rec.ATTRIBUTE3;
1153 l_rstmt_rec.ATTRIBUTE4:= l_stmt_rec.ATTRIBUTE4;
1154 l_rstmt_rec.ATTRIBUTE5:= l_stmt_rec.ATTRIBUTE5;
1155 l_rstmt_rec.ATTRIBUTE6:= l_stmt_rec.ATTRIBUTE6;
1156 l_rstmt_rec.ATTRIBUTE7:= l_stmt_rec.ATTRIBUTE7;
1157 l_rstmt_rec.ATTRIBUTE8:= l_stmt_rec.ATTRIBUTE8;
1158 l_rstmt_rec.ATTRIBUTE9 := l_stmt_rec.ATTRIBUTE9;
1159 l_rstmt_rec.ATTRIBUTE10:= l_stmt_rec.ATTRIBUTE10 ;
1160 l_rstmt_rec.ATTRIBUTE11:= l_stmt_rec.ATTRIBUTE11 ;
1161 l_rstmt_rec.ATTRIBUTE12:= l_stmt_rec.ATTRIBUTE12 ;
1162 l_rstmt_rec.ATTRIBUTE13:= l_stmt_rec.ATTRIBUTE13;
1163 l_rstmt_rec.ATTRIBUTE14:= l_stmt_rec.ATTRIBUTE14;
1164 l_rstmt_rec.ATTRIBUTE15:= l_stmt_rec.ATTRIBUTE15;
1165
1166 Insert_Rule_Stmt (p_api_version => 1.0,
1167 p_commit => FND_API.G_FALSE,
1168 p_module => 'PLSQL',
1169 p_x_rule_stmt_rec => l_rstmt_rec,
1170 x_return_status => x_return_status,
1171 x_msg_count => x_msg_count,
1172 x_msg_data => x_msg_data);
1173
1174 x_rule_stmt_id := l_rstmt_rec.rule_statement_id;
1175 END IF;
1176 CLOSE get_rule_stmt_csr;
1177
1178 -- Standard check of p_commit
1179 IF FND_API.TO_BOOLEAN(p_commit) THEN
1180 COMMIT WORK;
1181 END IF;
1182 -- Standard call to get message count and if count is 1, get message info
1183 FND_MSG_PUB.Count_And_Get
1184 ( p_count => x_msg_count,
1185 p_data => x_msg_data,
1186 p_encoded => fnd_api.g_false
1187 );
1188 EXCEPTION
1189 WHEN FND_API.G_EXC_ERROR THEN
1190 Rollback to Copy_Rule_Stmt_pvt;
1191 x_return_status := FND_API.G_RET_STS_ERROR;
1192 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1193 p_data => x_msg_data,
1194 p_encoded => fnd_api.g_false);
1195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1196 Rollback to Copy_Rule_Stmt_pvt;
1197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1198 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1199 p_data => x_msg_data,
1200 p_encoded => fnd_api.g_false);
1201 WHEN OTHERS THEN
1202 Rollback to Copy_Rule_Stmt_pvt;
1203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1204 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1205 p_procedure_name => l_api_name,
1206 p_error_text => SQLERRM);
1207 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1208 p_data => x_msg_data,
1209 p_encoded => fnd_api.g_false);
1210 END Copy_Rule_Stmt;
1211
1212 -----------------------------
1213 -- Start of Comments --
1214 -- Procedure name : Delete_Rule_Stmts
1215 -- Type : Private
1216 -- Function : Deletes all the Rule statements corresponding to a rule
1217 -- Pre-reqs :
1218 -- Parameters :
1219 --
1220 -- Delete_Rule Parameters:
1221 -- p_rule_id IN NUMBER Required
1222 --
1223 -- End of Comments.
1224
1225 PROCEDURE Delete_Rule_Stmts (
1226 p_api_version IN NUMBER,
1227 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1228 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1229 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1230 x_return_status OUT NOCOPY VARCHAR2,
1231 x_msg_count OUT NOCOPY NUMBER,
1232 x_msg_data OUT NOCOPY VARCHAR2,
1233 p_rule_id IN NUMBER)
1234 IS
1235 --
1236 CURSOR ahl_rule_stmts_csr (p_rule_id IN NUMBER) IS
1237 SELECT rule_statement_id
1238 FROM ahl_mc_rule_statements stmt
1239 WHERE rule_id = p_rule_id;
1240 --
1241 l_api_version CONSTANT NUMBER := 1.0;
1242 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rule_Stmts';
1243 l_stmt_id NUMBER;
1244 --
1245 BEGIN
1246 -- Standard start of API savepoint
1247 SAVEPOINT Delete_Rule_Stmts_pvt;
1248 -- Initialize Procedure return status to success
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250 -- Standard call to check for call compatibility
1251 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1252 G_PKG_NAME) THEN
1253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1254 END IF;
1255 -- Initialize message list if p_init_msg_list is set to TRUE
1256 IF FND_API.To_Boolean(p_init_msg_list) THEN
1257 FND_MSG_PUB.Initialize;
1258 END IF;
1259
1260 --Check Status of MC allows for editing
1261 /*IF NOT(check_mc_status(p_rule_id)) THEN
1262 FND_MESSAGE.Set_Name('AHL','AHL_INVALID_MC_STATUS');
1263 FND_MSG_PUB.ADD;
1264 Raise FND_API.G_EXC_ERROR;
1265 END IF; */
1266
1267 --Delete the rule statments corresponding to rule
1268 OPEN ahl_rule_stmts_csr(p_rule_id);
1269 LOOP
1270 FETCH ahl_rule_stmts_csr INTO l_stmt_id;
1271 EXIT WHEN ahl_rule_stmts_csr%NOTFOUND;
1272
1273 IF (ahl_rule_stmts_csr%FOUND) THEN
1274 DELETE FROM AHL_MC_RULE_STATEMENTS
1275 WHERE rule_statement_id = l_stmt_id;
1276 END IF;
1277
1278 END LOOP;
1279 CLOSE ahl_rule_stmts_csr;
1280
1281 -- Standard check of p_commit
1282 IF FND_API.TO_BOOLEAN(p_commit) THEN
1283 COMMIT WORK;
1284 END IF;
1285 -- Standard call to get message count and if count is 1, get message info
1286 FND_MSG_PUB.Count_And_Get
1287 ( p_count => x_msg_count,
1288 p_data => x_msg_data,
1289 p_encoded => fnd_api.g_false
1290 );
1291 EXCEPTION
1292 WHEN FND_API.G_EXC_ERROR THEN
1293 Rollback to Delete_Rule_Stmts_pvt;
1294 x_return_status := FND_API.G_RET_STS_ERROR;
1295 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1296 p_data => x_msg_data,
1297 p_encoded => fnd_api.g_false);
1298 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1299 Rollback to Delete_Rule_Stmts_pvt;
1300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1301 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1302 p_data => x_msg_data,
1303 p_encoded => fnd_api.g_false);
1304 WHEN OTHERS THEN
1305 Rollback to Delete_Rule_Stmts_pvt;
1306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1307 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1308 p_procedure_name => l_api_name,
1309 p_error_text => SQLERRM);
1310 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1311 p_data => x_msg_data,
1312 p_encoded => fnd_api.g_false);
1313 End Delete_Rule_Stmts;
1314
1315 -----------------------------
1316 -- Start of Comments --
1317 -- Procedure name : validate_pos_quantity_rule
1318 -- Type : Private
1319 -- Function : Validates the position quantity related rule
1320 -- Pre-reqs :
1321 -- Parameters :
1322 --
1323 -- validate_pos_quantity_rule Parameters:
1324 -- p_rule_stmt_rec IN AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type Required
1325 --
1326 -- API added for FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 03-Dec-2007
1327 --
1328 -- End of Comments.
1329
1330 PROCEDURE validate_pos_quantity_rule (p_rule_stmt_rec IN AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type)
1331 IS
1332 --
1333 /*
1334 The following cursor retrieves all the relationships, corresponding to the path_position_id for which the rule is defined.
1335 If the version number is populated, which would mean that the rule is version dependent, we will get only one record,
1336 else we will get as many records as the versions of the MC, unless the user deleted the relation in one of the versions.
1337 */
1338 CURSOR get_relationships_csr (p_position_id IN NUMBER) IS
1339 SELECT header.mc_header_id, header.name, header.revision, rels.position_key, rels.relationship_id
1340 FROM AHL_MC_HEADERS_B header,
1341 AHL_MC_PATH_POSITION_NODES pnodes,
1342 AHL_MC_RELATIONSHIPS rels
1343 WHERE pnodes.path_position_id = p_position_id
1344 AND pnodes.sequence = (select max(sequence) from AHL_MC_PATH_POSITION_NODES where path_position_id = p_position_id)
1345 AND pnodes.mc_id = header.mc_id
1346 AND nvl(pnodes.version_number,header.version_number) = header.version_number
1347 AND rels.mc_header_id = header.mc_header_id
1348 AND rels.position_key = pnodes.position_key;
1349
1350 CURSOR get_child_rels_csr(p_relationship_id IN NUMBER) IS
1351 SELECT relationship_id
1352 FROM AHL_MC_RELATIONSHIPS
1353 WHERE parent_relationship_id = p_relationship_id;
1354
1355 CURSOR is_rel_nonleaf_node(p_relationship_id IN NUMBER) IS
1356 SELECT 1
1357 FROM AHL_MC_RELATIONSHIPS
1358 WHERE parent_relationship_id = p_relationship_id
1359 AND rownum = 1;
1360
1361 CURSOR is_rel_subconfig_csr(p_relationship_id IN NUMBER) IS
1362 SELECT relationship_id
1363 FROM ahl_mc_config_relations
1364 WHERE relationship_id = p_relationship_id
1365 AND rownum = 1;
1366
1367 --
1368 l_api_version CONSTANT NUMBER := 1.0;
1369 l_api_name CONSTANT VARCHAR2(30) := 'validate_pos_quantity_rule';
1370 l_stmt_id NUMBER;
1371 l_rels_rec get_relationships_csr%ROWTYPE;
1372 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.validate_pos_quantity_rule';
1373 l_child_rel_id NUMBER;
1374 l_child_count NUMBER;
1375 l_dummy NUMBER;
1376 l_oper_meaning FND_LOOKUPS.MEANING%TYPE;
1377 l_obj_typ_meaning FND_LOOKUPS.MEANING%TYPE;
1378 --
1379 BEGIN
1380
1381 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)THEN
1382 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,L_DEBUG_KEY||'.begin','At the start of PLSQL procedure');
1383 END IF;
1384
1385 l_oper_meaning := get_rule_oper(p_rule_stmt_rec.operator, p_rule_stmt_rec.negation_flag);
1386 l_obj_typ_meaning := get_fnd_lkup_meaning('AHL_MC_RULE_OBJECT_TYPES', p_rule_stmt_rec.object_type);
1387
1388 OPEN get_relationships_csr(p_rule_stmt_rec.subject_id);
1389 LOOP
1390 FETCH get_relationships_csr INTO l_rels_rec;
1391 EXIT WHEN get_relationships_csr%NOTFOUND;
1392 --get the child relationship ids. At least one should be present
1393 --Initialize the child count to zero for every relationship (node).
1394 l_child_count := 0;
1395 OPEN get_child_rels_csr(l_rels_rec.relationship_id);
1396 LOOP
1397 FETCH get_child_rels_csr INTO l_child_rel_id;
1398 EXIT WHEN get_child_rels_csr%NOTFOUND;
1399 l_child_count := l_child_count + 1;
1400
1401 --If we reached here, it means that there is atleast one child for the node corr. to the passed path position.
1402 --Now each of the children should be a leaf node.
1403 OPEN is_rel_nonleaf_node(l_child_rel_id);
1404 FETCH is_rel_nonleaf_node INTO l_dummy;
1405 IF(is_rel_nonleaf_node%FOUND) THEN
1406 --This would mean that the child node is not a leaf node. Throw an error.
1407 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QRUL_CHD_LF_NOD');
1408 FND_MESSAGE.Set_Token('OPERATOR',l_oper_meaning);
1409 FND_MESSAGE.Set_Token('OBJ_TYPE',l_obj_typ_meaning);
1410 FND_MESSAGE.Set_Token('MC_NAME',l_rels_rec.name);
1411 FND_MESSAGE.Set_Token('MC_REV',l_rels_rec.revision);
1412 FND_MSG_PUB.ADD;
1413 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1414 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_child_rel_id: ' || l_child_rel_id || 'has children');
1415 END IF;
1416 END IF;--IF(is_rel_nonleaf_node%FOUND) THEN
1417 CLOSE is_rel_nonleaf_node;
1418
1419 --validate that none of the child nodes are sub-configs
1420 OPEN is_rel_subconfig_csr(l_child_rel_id);
1421 FETCH is_rel_subconfig_csr INTO l_dummy;
1422 IF(is_rel_subconfig_csr%FOUND) THEN
1423 --This would mean that the child node is a sub-config. Throw an error.
1424 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QRUL_CHD_SBC_NOD');
1425 FND_MESSAGE.Set_Token('OPERATOR',l_oper_meaning);
1426 FND_MESSAGE.Set_Token('OBJ_TYPE',l_obj_typ_meaning);
1427 FND_MESSAGE.Set_Token('MC_NAME',l_rels_rec.name);
1428 FND_MESSAGE.Set_Token('MC_REV',l_rels_rec.revision);
1429 FND_MSG_PUB.ADD;
1430 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1431 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'l_child_rel_id: ' || l_child_rel_id || 'is a sub-configuration');
1432 END IF;
1433 END IF;--IF(is_rel_subconfig_csr%FOUND) THEN
1434 CLOSE is_rel_subconfig_csr;
1435
1436 END LOOP;--Loop for get_child_rels_csr(l_rels_rec.relationship_id)
1437 CLOSE get_child_rels_csr;
1438
1439 IF(l_child_count = 0) THEN
1440 --This would mean that the relationship node is a leaf node. We cannot define the position quantity rule for a leaf node.
1441 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QRUL_CN_LF_NOD');
1442 FND_MESSAGE.Set_Token('OPERATOR',l_oper_meaning);
1443 FND_MESSAGE.Set_Token('OBJ_TYPE',l_obj_typ_meaning);
1444 FND_MESSAGE.Set_Token('MC_NAME',l_rels_rec.name);
1445 FND_MESSAGE.Set_Token('MC_REV',l_rels_rec.revision);
1446 FND_MSG_PUB.ADD;
1447 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1448 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1449 'l_rels_rec.relationship_id: ' || l_rels_rec.relationship_id || 'does not have children');
1450 END IF;
1451 END IF;
1452 END LOOP;--Loop for get_relationships_csr(p_rule_stmt_rec.subject_id);
1453 CLOSE get_relationships_csr;
1454
1455 --object_id has to be NULL
1456 IF(p_rule_stmt_rec.object_id is not null) THEN
1457 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QRUL_OBJ_NNLL');
1458 FND_MESSAGE.Set_Token('OPERATOR',l_oper_meaning);
1459 FND_MESSAGE.Set_Token('OBJ_TYPE',l_obj_typ_meaning);
1460 FND_MSG_PUB.ADD;
1461 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1462 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1463 'p_rule_stmt_rec.object_id: ' || p_rule_stmt_rec.object_id || 'is not null');
1464 END IF;
1465 END IF;
1466
1467 --object_attribute1 is to be non null and should be a positive integer.
1468 IF(p_rule_stmt_rec.object_attribute1 is NULL) THEN
1469 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QRUL_QUANT_NLL');
1470 FND_MESSAGE.Set_Token('OPERATOR',l_oper_meaning);
1471 FND_MESSAGE.Set_Token('OBJ_TYPE',l_obj_typ_meaning);
1472 FND_MSG_PUB.ADD;
1473 ELSE
1474 BEGIN
1475 IF(MOD(TO_NUMBER(p_rule_stmt_rec.object_attribute1),1) <> 0 OR TO_NUMBER(p_rule_stmt_rec.object_attribute1) <= 0) THEN
1476 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QRUL_QUANT_NPOSI');
1477 FND_MESSAGE.Set_Token('OPERATOR',l_oper_meaning);
1478 FND_MESSAGE.Set_Token('OBJ_TYPE',l_obj_typ_meaning);
1479 FND_MSG_PUB.ADD;
1480 END IF;
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_QRUL_QUANT_NPOSI');
1484 FND_MESSAGE.Set_Token('OPERATOR',l_oper_meaning);
1485 FND_MESSAGE.Set_Token('OBJ_TYPE',l_obj_typ_meaning);
1486 FND_MSG_PUB.ADD;
1487 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1488 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1489 'p_rule_stmt_rec.object_attribute1: ' ||p_rule_stmt_rec.object_attribute1 || 'is not a number');
1490 END IF;
1491 END;
1492 END IF;
1493
1494 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)THEN
1495 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,L_DEBUG_KEY||'.end','At the end of PLSQL procedure');
1496 END IF;
1497
1498 End validate_pos_quantity_rule;
1499
1500 -----------------------------
1501 -- Start of Comments --
1502 -- Procedure name : validate_quantity_rules_for_mc
1503 -- Type : Private
1504 -- Function : Validates the position quantity related rules for MC
1505 -- Pre-reqs :
1506 -- Parameters :
1507 --
1508 -- validate_quantity_rules_for_mc Parameters:
1509 -- p_mc_header_id IN NUMBER Required
1510 --
1511 -- API added for FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 03-Dec-2007
1512 -- It is called from AHL_MC_MasterConfig_PVT.Check_MC_Complete
1513 --
1514 -- End of Comments.
1515 PROCEDURE validate_quantity_rules_for_mc(
1516 p_api_version IN NUMBER,
1517 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1518 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1519 p_mc_header_id IN NUMBER,
1520 x_return_status OUT NOCOPY VARCHAR2,
1521 x_msg_count OUT NOCOPY NUMBER,
1522 x_msg_data OUT NOCOPY VARCHAR2)
1523
1524 IS
1525 --
1526 --Get the rule statements that are quantity based.
1527 CURSOR get_quantity_rule_stmt_csr (c_mc_header_id IN NUMBER) IS
1528 SELECT ruls.subject_id,
1529 ruls.object_id,
1530 ruls.rule_statement_id,
1531 ruls.rule_id,
1532 ruls.top_rule_stmt_flag,
1533 ruls.negation_flag,
1534 ruls.subject_type,
1535 ruls.operator,
1536 ruls.object_type,
1537 ruls.OBJECT_ATTRIBUTE1
1538 FROM ahl_mc_rules_b rul, ahl_mc_rule_statements ruls
1539 WHERE rul.rule_id = ruls.rule_id
1540 AND rul.mc_header_id = p_mc_header_id
1541 AND ruls.object_type = 'TOT_CHILD_QUANTITY'
1542 AND TRUNC(nvl(rul.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
1543 AND TRUNC(nvl(rul.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
1544
1545 --
1546 l_api_version CONSTANT NUMBER := 1.0;
1547 l_api_name CONSTANT VARCHAR2(30) := 'validate_quantity_rules_for_mc';
1548 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.validate_quantity_rules_for_mc';
1549 l_quant_stmt_rec get_quantity_rule_stmt_csr%ROWTYPE;
1550 l_rstmt_rec AHL_MC_RULE_PVT.Rule_Stmt_Rec_Type;
1551
1552 --
1553 BEGIN
1554 -- Initialize Procedure return status to success
1555 x_return_status := FND_API.G_RET_STS_SUCCESS;
1556 -- Standard call to check for call compatibility
1557 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1558 G_PKG_NAME) THEN
1559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1560 END IF;
1561 -- Initialize message list if p_init_msg_list is set to TRUE
1562 IF FND_API.To_Boolean(p_init_msg_list) THEN
1563 FND_MSG_PUB.Initialize;
1564 END IF;
1565
1566 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)THEN
1567 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,L_DEBUG_KEY||'.begin','At the start of PLSQL procedure');
1568 END IF;
1569
1570 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1571 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'p_mc_header_id: ' || p_mc_header_id );
1572 END IF;
1573
1574 IF(p_mc_header_id is NULL) THEN
1575 FND_MESSAGE.Set_Name('AHL','AHL_COM_REQD_PARAM_MISSING');
1576 FND_MSG_PUB.ADD;
1577 RAISE FND_API.G_EXC_ERROR;
1578 END IF;
1579
1580 --Validate the position quantity rules.
1581 OPEN get_quantity_rule_stmt_csr (p_mc_header_id);
1582 LOOP
1583 FETCH get_quantity_rule_stmt_csr INTO l_quant_stmt_rec;
1584 EXIT WHEN get_quantity_rule_stmt_csr%NOTFOUND;
1585
1586 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1587 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY,
1588 ' l_quant_stmt_rec.rule_statement_id: ' || l_quant_stmt_rec.rule_statement_id ||
1589 ', l_quant_stmt_rec.subject_id: ' || l_quant_stmt_rec.subject_id ||
1590 ', l_quant_stmt_rec.object_id: ' || l_quant_stmt_rec.object_id ||
1591 ', l_quant_stmt_rec.operator: ' || l_quant_stmt_rec.operator ||
1592 ', l_quant_stmt_rec.subject_type: ' || l_quant_stmt_rec.subject_type ||
1593 ', l_quant_stmt_rec.object_type: ' || l_quant_stmt_rec.object_type ||
1594 ', l_quant_stmt_rec.OBJECT_ATTRIBUTE1: ' || l_quant_stmt_rec.OBJECT_ATTRIBUTE1 );
1595 END IF;
1596
1597 l_rstmt_rec.subject_id := l_quant_stmt_rec.subject_id;
1598 l_rstmt_rec.object_id := l_quant_stmt_rec.object_id;
1599 l_rstmt_rec.rule_id := l_quant_stmt_rec.rule_id;
1600 l_rstmt_rec.negation_flag := l_quant_stmt_rec.negation_flag;
1601 l_rstmt_rec.subject_type := l_quant_stmt_rec.subject_type;
1602 l_rstmt_rec.operator := l_quant_stmt_rec.operator;
1603 l_rstmt_rec.object_type := l_quant_stmt_rec.object_type;
1604 l_rstmt_rec.OBJECT_ATTRIBUTE1:=l_quant_stmt_rec.OBJECT_ATTRIBUTE1;
1605
1606 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1607 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before calling the validate_pos_quantity_rule ' );
1608 END IF;
1609 --calling validate_pos_quantity_rule to validate the rule statement.
1610 validate_pos_quantity_rule(l_rstmt_rec);
1611
1612 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1613 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After calling the validate_pos_quantity_rule ' );
1614 END IF;
1615
1616 END LOOP;
1617 CLOSE get_quantity_rule_stmt_csr;
1618
1619 -- Check Error Message stack.
1620 x_msg_count := FND_MSG_PUB.count_msg;
1621
1622 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1623 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'x_msg_count: ' ||x_msg_count);
1624 END IF;
1625
1626 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)THEN
1627 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,L_DEBUG_KEY||'.end','At the end of PLSQL procedure');
1628 END IF;
1629
1630 -- Standard call to get message count and if count is 1, get message info
1631 FND_MSG_PUB.Count_And_Get
1632 ( p_count => x_msg_count,
1633 p_data => x_msg_data,
1634 p_encoded => fnd_api.g_false
1635 );
1636
1637 EXCEPTION
1638 WHEN FND_API.G_EXC_ERROR THEN
1639 x_return_status := FND_API.G_RET_STS_ERROR;
1640 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1641 p_data => x_msg_data,
1642 p_encoded => fnd_api.g_false);
1643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1645 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1646 p_data => x_msg_data,
1647 p_encoded => fnd_api.g_false);
1648 WHEN OTHERS THEN
1649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1650 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1651 p_procedure_name => l_api_name,
1652 p_error_text => SQLERRM);
1653 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1654 p_data => x_msg_data,
1655 p_encoded => fnd_api.g_false);
1656
1657 END validate_quantity_rules_for_mc;
1658
1659 --
1660 --Simple function that fetches the translated operator name
1661 --
1662 FUNCTION get_rule_oper(p_rule_oper IN VARCHAR2,
1663 p_neg_flag IN VARCHAR2)
1664 RETURN VARCHAR2
1665 IS
1666 --
1667 CURSOR get_rule_oper_csr(p_oper IN VARCHAR2, p_neg IN VARCHAR2) IS
1668 SELECT fnd.meaning
1669 FROM fnd_lookups fnd
1670 WHERE fnd.lookup_code = decode (p_neg, 'T', p_oper||'_NOT', p_oper)
1671 AND fnd.lookup_type = 'AHL_MC_RULE_ALL_OPERATORS';
1672 --
1673 l_operator FND_LOOKUPS.MEANING%TYPE;
1674 --
1675 BEGIN
1676 OPEN get_rule_oper_csr(p_rule_oper, p_neg_flag);
1677 FETCH get_rule_oper_csr INTO l_operator;
1678 CLOSE get_rule_oper_csr;
1679 RETURN l_operator;
1680 END get_rule_oper;
1681
1682 --
1683 -- API added for FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 03-Dec-2007
1684 --
1685 -- Simple function that fetches the translated lookup meaning, given a lookup type and code.
1686 --
1687 FUNCTION get_fnd_lkup_meaning(p_lookup_type IN VARCHAR2, p_lookup_code IN VARCHAR2)
1688 RETURN VARCHAR2
1689 IS
1690 --
1691 CURSOR get_fnd_lkup_meaning_csr(p_lookup_type IN VARCHAR2, p_lookup_code IN VARCHAR2) IS
1692 SELECT fnd.meaning
1693 FROM fnd_lookups fnd
1694 WHERE fnd.lookup_code = p_lookup_code
1695 AND fnd.lookup_type = p_lookup_type;
1696 --
1697 l_lkup_meaning FND_LOOKUPS.MEANING%TYPE;
1698 --
1699 BEGIN
1700 OPEN get_fnd_lkup_meaning_csr(p_lookup_type, p_lookup_code );
1701 FETCH get_fnd_lkup_meaning_csr INTO l_lkup_meaning;
1702 CLOSE get_fnd_lkup_meaning_csr;
1703 RETURN l_lkup_meaning;
1704
1705 END get_fnd_lkup_meaning;
1706
1707 -----------------
1708 End AHL_MC_RULE_STMT_PVT;