[Home] [Help]
PACKAGE BODY: APPS.AHL_MC_RULE_PUB
Source
1 PACKAGE BODY AHL_MC_RULE_PUB AS
2 /* $Header: AHLPMCRB.pls 120.0.12020000.2 2012/12/07 00:17:55 sareepar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_MC_RULE_PUB';
5
6 ------------------------------------------------------------------------------------
7 -- Local API Declaration
8 ------------------------------------------------------------------------------------
9 PROCEDURE Convert_Rule_Values_to_Id (
10 p_x_rule_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Rec_Type,
11 p_operation_flag IN VARCHAR2
12 );
13
14 PROCEDURE Convert_Rule_Stmt_Values_to_Id (
15 p_x_ui_rule_stmt_tbl IN OUT NOCOPY AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type,
16 p_operation_flag IN VARCHAR2
17 );
18
19 ------------------------------------------------------------------------------------
20 -- Start of Comments
21 -- Procedure name : Insert_Rule
22 -- Type : Public
23 -- Function : Does user input validation and calls private API Insert_Rule
24 -- Pre-reqs :
25 -- Parameters :
26 --
27 -- Insert_Rule Parameters:
28 -- p_x_rule_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Rec_Type Required
29 -- p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type Required
30 --
31 -- End of Comments
32
33 PROCEDURE Insert_Rule (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38 p_module IN VARCHAR2 := 'JSP',
39 p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type,
40 p_x_rule_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Rec_Type,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2
44 ) IS
45
46 --
47 l_api_version CONSTANT NUMBER := 1.0;
48 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Rule';
49 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
50
51 l_rule_stmt_tbl AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type DEFAULT p_rule_stmt_tbl;
52 --
53
54 BEGIN
55 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
56 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
57 END IF;
58
59 -- Standard start of API savepoint
60 SAVEPOINT Insert_Rule_Pub;
61
62 -- Initialize Procedure return status to success
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64
65 -- Standard call to check for call compatibility
66 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
67 l_api_name, G_PKG_NAME) THEN
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 END IF;
70
71 -- Initialize message list if p_init_msg_list is set to TRUE
72 IF FND_API.To_Boolean(p_init_msg_list) THEN
73 FND_MSG_PUB.Initialize;
74 END IF;
75
76 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
77 FND_LOG.string(FND_LOG.level_statement,l_full_name,
78 ' p_x_rule_rec.mc_header_id => '||p_x_rule_rec.mc_header_id||
79 ' p_x_rule_rec.mc_name => '||p_x_rule_rec.mc_name||
80 ' p_x_rule_rec.mc_revision => '||p_x_rule_rec.mc_revision||
81 ' p_x_rule_rec.rule_name => '||p_x_rule_rec.rule_name||
82 ' p_x_rule_rec.rule_type_code => '||p_x_rule_rec.rule_type_code||
83 ' p_x_rule_rec.rule_type_meaning => '||p_x_rule_rec.rule_type_meaning||
84 ' p_rule_stmt_tbl.COUNT => '||p_rule_stmt_tbl.COUNT);
85 END IF;
86
87 -- call Convert_Rule_Values_to_Id
88 Convert_Rule_Values_to_Id(p_x_rule_rec, 'I');
89
90 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
91 FND_LOG.string(FND_LOG.level_statement,l_full_name,
92 ' p_x_rule_rec.mc_header_id => '||p_x_rule_rec.mc_header_id||
93 ' p_rule_rec.mc_name => '||p_x_rule_rec.mc_name||
94 ' p_rule_rec.mc_revision => '||p_x_rule_rec.mc_revision||
95 ' p_x_rule_rec.rule_name => '||p_x_rule_rec.rule_name||
96 ' p_x_rule_rec.rule_type_code => '||p_x_rule_rec.rule_type_code||
97 ' p_x_rule_rec.rule_type_meaning => '||p_x_rule_rec.rule_type_meaning||
98 ' p_rule_stmt_tbl.COUNT => '||p_rule_stmt_tbl.COUNT);
99 END IF;
100
101 -- check for UI rule stmt table
102 IF (p_rule_stmt_tbl.COUNT < 1) THEN
103 -- input is NULL
104 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_NULL');
105 FND_MSG_PUB.ADD;
106 RAISE FND_API.G_EXC_ERROR;
107 END IF;
108
109 -- call Convert_Rule_Stmt_Values_to_Id
110 Convert_Rule_Stmt_Values_to_Id(l_rule_stmt_tbl, 'I');
111
112 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
113 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Calling private API.');
114 END IF;
115
116 -- call the private API
117 AHL_MC_RULE_PVT.Insert_Rule(
118 p_api_version => p_api_version,
119 p_init_msg_list => p_init_msg_list,
120 p_commit => p_commit,
121 p_validation_level => p_validation_level,
122 p_module => p_module,
123 p_rule_stmt_tbl => l_rule_stmt_tbl,
124 p_x_rule_rec => p_x_rule_rec,
125 x_return_status => x_return_status,
126 x_msg_count => x_msg_count,
127 x_msg_data => x_msg_data
128 );
129
130 -- check for the return status
131 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
132 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
133 FND_LOG.string(FND_LOG.level_statement,l_full_name,
134 'Raising exception with x_return_status => '||x_return_status);
135 END IF;
136
137 RAISE FND_API.G_EXC_ERROR;
138 END IF;
139
140 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
141 FND_LOG.string(FND_LOG.level_statement,l_full_name,'After call to private API.');
142 END IF;
143
144 -- Standard check of p_commit
145 IF FND_API.TO_BOOLEAN(p_commit) THEN
146 COMMIT WORK;
147 END IF;
148
149 -- Standard call to get message count and if count is 1, get message info
150 FND_MSG_PUB.Count_And_Get
151 ( p_count => x_msg_count,
152 p_data => x_msg_data,
153 p_encoded => FND_API.G_FALSE
154 );
155
156 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
157 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
158 END IF;
159
160 EXCEPTION
161 WHEN FND_API.G_EXC_ERROR THEN
162 Rollback to Insert_Rule_Pub;
163 x_return_status := FND_API.G_RET_STS_ERROR;
164 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
165 p_data => x_msg_data,
166 p_encoded => fnd_api.g_false);
167
168 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
169 Rollback to Insert_Rule_Pub;
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
172 p_data => x_msg_data,
173 p_encoded => fnd_api.g_false);
174
175 WHEN OTHERS THEN
176 Rollback to Insert_Rule_Pub;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
179 p_procedure_name => l_api_name,
180 p_error_text => SQLERRM);
181 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
182 p_data => x_msg_data,
183 p_encoded => FND_API.G_FALSE);
184
185 END Insert_Rule;
186
187 ------------------------------------------------------------------------------------
188 -- Start of Comments
189 -- Procedure name : Update_Rule
190 -- Type : Public
191 -- Function : Does user input validation and calls private API Update_Rule
192 -- Pre-reqs :
193 -- Parameters :
194 --
195 -- Update_Rule Parameters:
196 -- p_rule_rec IN AHL_MC_RULE_PVT.Rule_Rec_Type Required
197 -- p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type Required
198 --
199 -- End of Comments
200
201 PROCEDURE Update_Rule (
202 p_api_version IN NUMBER,
203 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
204 p_commit IN VARCHAR2 := FND_API.G_FALSE,
205 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
206 p_module IN VARCHAR2 := 'JSP',
207 p_rule_rec IN AHL_MC_RULE_PVT.Rule_Rec_Type,
208 p_rule_stmt_tbl IN AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2
212 ) IS
213
214 --
215 l_api_version CONSTANT NUMBER := 1.0;
216 l_api_name CONSTANT VARCHAR2(30) := 'Update_Rule';
217 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
218
219 l_rule_rec AHL_MC_RULE_PVT.Rule_Rec_Type DEFAULT p_rule_rec;
220 l_rule_stmt_tbl AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type DEFAULT p_rule_stmt_tbl;
221 --
222
223 BEGIN
224 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
225 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
226 END IF;
227
228 -- Standard start of API savepoint
229 SAVEPOINT Update_Rule_Pub;
230
231 -- Initialize Procedure return status to success
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 -- Standard call to check for call compatibility
235 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
236 l_api_name, G_PKG_NAME) THEN
237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
238 END IF;
239
240 -- Initialize message list if p_init_msg_list is set to TRUE
241 IF FND_API.To_Boolean(p_init_msg_list) THEN
242 FND_MSG_PUB.Initialize;
243 END IF;
244
245 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
246 FND_LOG.string(FND_LOG.level_statement,l_full_name,
247 ' p_rule_rec.rule_id => '||p_rule_rec.rule_id||
248 ' p_rule_rec.rule_name => '||p_rule_rec.rule_name||
249 ' p_rule_rec.object_version_number => '||p_rule_rec.object_version_number||
250 ' p_rule_rec.mc_header_id => '||p_rule_rec.mc_header_id||
251 ' p_rule_rec.mc_name => '||p_rule_rec.mc_name||
252 ' p_rule_rec.mc_revision => '||p_rule_rec.mc_revision||
253 ' p_rule_stmt_tbl.COUNT => '||p_rule_stmt_tbl.COUNT);
254 END IF;
255
256 -- check input parameters
257 IF (p_rule_rec.object_version_number IS NULL) THEN
258 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_OBJ_VER_NULL');
259 FND_MSG_PUB.ADD;
260 RAISE FND_API.G_EXC_ERROR;
261 END IF;
262
263 -- call Convert_Rule_Values_to_Id
264 Convert_Rule_Values_to_Id(l_rule_rec, 'U');
265
266 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
267 FND_LOG.string(FND_LOG.level_statement,l_full_name,
268 ' p_rule_rec.rule_id => '||l_rule_rec.rule_id||
269 ' p_rule_rec.rule_name => '||l_rule_rec.rule_name||
270 ' p_rule_rec.object_version_number => '||l_rule_rec.object_version_number||
271 ' p_rule_rec.mc_header_id => '||l_rule_rec.mc_header_id||
272 ' p_rule_rec.mc_name => '||l_rule_rec.mc_name||
273 ' p_rule_rec.mc_revision => '||l_rule_rec.mc_revision||
274 ' p_rule_stmt_tbl.COUNT => '||p_rule_stmt_tbl.COUNT);
275 END IF;
276
277 -- check for UI rule stmt table
278 IF (p_rule_stmt_tbl.COUNT < 1) THEN
279 -- input is NULL
280 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_NULL');
281 FND_MSG_PUB.ADD;
282 RAISE FND_API.G_EXC_ERROR;
283 END IF;
284
285 -- call Convert_Rule_Stmt_Values_to_Id
286 Convert_Rule_Stmt_Values_to_Id(l_rule_stmt_tbl, 'U');
287
288 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
289 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Calling private API.');
290 END IF;
291
292 -- call the private API
293 AHL_MC_RULE_PVT.Update_Rule(
294 p_api_version => p_api_version,
295 p_init_msg_list => p_init_msg_list,
296 p_commit => p_commit,
297 p_validation_level => p_validation_level,
298 p_module => p_module,
299 p_rule_rec => l_rule_rec,
300 p_rule_stmt_tbl => l_rule_stmt_tbl,
301 x_return_status => x_return_status,
302 x_msg_count => x_msg_count,
303 x_msg_data => x_msg_data
304 );
305
306 -- check for the return status
307 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
308 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
309 FND_LOG.string(FND_LOG.level_statement,l_full_name,
310 'Raising exception with x_return_status => '||x_return_status);
311 END IF;
312
313 RAISE FND_API.G_EXC_ERROR;
314 END IF;
315
316 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
317 FND_LOG.string(FND_LOG.level_statement,l_full_name,'After call to private API.');
318 END IF;
319
320 -- Standard check of p_commit
321 IF FND_API.TO_BOOLEAN(p_commit) THEN
322 COMMIT WORK;
323 END IF;
324
325 -- Standard call to get message count and if count is 1, get message info
326 FND_MSG_PUB.Count_And_Get
327 ( p_count => x_msg_count,
328 p_data => x_msg_data,
329 p_encoded => FND_API.G_FALSE
330 );
331
332 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
333 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
334 END IF;
335
336 EXCEPTION
337 WHEN FND_API.G_EXC_ERROR THEN
338 Rollback to Update_Rule_Pub;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
341 p_data => x_msg_data,
342 p_encoded => fnd_api.g_false);
343
344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
345 Rollback to Update_Rule_Pub;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
348 p_data => x_msg_data,
349 p_encoded => fnd_api.g_false);
350
351 WHEN OTHERS THEN
352 Rollback to Update_Rule_Pub;
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
355 p_procedure_name => l_api_name,
356 p_error_text => SQLERRM);
357 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
358 p_data => x_msg_data,
359 p_encoded => FND_API.G_FALSE);
360
361 END Update_Rule;
362
363 ------------------------------------------------------------------------------------
364 -- Start of Comments
365 -- Procedure name : Delete_Rule
366 -- Type : Public
367 -- Function : Does user input validation and calls private API Delete_Rule
368 -- Pre-reqs :
369 -- Parameters :
370 --
371 -- Delete_Rule Parameters:
372 -- p_rule_rec.rule_id IN NUMBER Required
373 -- or
374 -- p_rule_rec.rule_name IN VARCHAR2 Required
375 -- p_rule_rec.mc_header_id IN NUMBER Required
376 -- ( or
377 -- p_rule_rec.mc_name IN VARCHAR2 Required
378 -- p_rule_rec.mc_revision IN NUMBER Required)
379 --
380 -- p_rule_rec.object_version_number IN NUMBER Required
381 --
382 -- End of Comments
383
384 PROCEDURE Delete_Rule (
385 p_api_version IN NUMBER,
386 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
387 p_commit IN VARCHAR2 := FND_API.G_FALSE,
388 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
389 p_rule_rec IN AHL_MC_RULE_PVT.Rule_Rec_Type,
390 x_return_status OUT NOCOPY VARCHAR2,
391 x_msg_count OUT NOCOPY NUMBER,
392 x_msg_data OUT NOCOPY VARCHAR2
393 ) IS
394
395 --
396 l_api_version CONSTANT NUMBER := 1.0;
397 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rule';
398 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
399
400 l_rule_rec AHL_MC_RULE_PVT.Rule_Rec_Type DEFAULT p_rule_rec;
401 --
402
403 BEGIN
404 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
405 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
406 END IF;
407
408 -- Standard start of API savepoint
409 SAVEPOINT Delete_Rule_Pub;
410
411 -- Initialize Procedure return status to success
412 x_return_status := FND_API.G_RET_STS_SUCCESS;
413
414 -- Standard call to check for call compatibility
415 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
416 l_api_name, G_PKG_NAME) THEN
417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
418 END IF;
419
420 -- Initialize message list if p_init_msg_list is set to TRUE
421 IF FND_API.To_Boolean(p_init_msg_list) THEN
422 FND_MSG_PUB.Initialize;
423 END IF;
424
425 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
426 FND_LOG.string(FND_LOG.level_statement,l_full_name,
427 ' p_rule_rec.rule_id => '||p_rule_rec.rule_id||
428 ' p_rule_rec.object_version_number => '||p_rule_rec.object_version_number||
429 ' p_rule_rec.rule_name => '||p_rule_rec.rule_name||
430 ' p_rule_rec.mc_header_id => '||p_rule_rec.mc_header_id||
431 ' p_rule_rec.mc_name => '||p_rule_rec.mc_name||
432 ' p_rule_rec.mc_revision => '||p_rule_rec.mc_revision);
433 END IF;
434
435 -- check input parameters
436 IF (p_rule_rec.object_version_number IS NULL) THEN
437 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_OBJ_VER_NULL');
438 FND_MSG_PUB.ADD;
439 RAISE FND_API.G_EXC_ERROR;
440 END IF;
441
442 -- call Convert_Rule_Values_to_Id
443 Convert_Rule_Values_to_Id(l_rule_rec, 'D');
444
445 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
446 FND_LOG.string(FND_LOG.level_statement,l_full_name,
447 ' p_rule_rec.rule_id => '||l_rule_rec.rule_id||
448 ' p_rule_rec.object_version_number => '||l_rule_rec.object_version_number||
449 ' p_rule_rec.rule_name => '||l_rule_rec.rule_name||
450 ' p_rule_rec.mc_header_id => '||l_rule_rec.mc_header_id||
451 ' p_rule_rec.mc_name => '||l_rule_rec.mc_name||
452 ' p_rule_rec.mc_revision => '||l_rule_rec.mc_revision);
453 END IF;
454
455 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
456 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Calling private API.');
457 END IF;
458
459 -- call the private API
460 AHL_MC_RULE_PVT.Delete_Rule(
461 p_api_version => p_api_version,
462 p_init_msg_list => p_init_msg_list,
463 p_commit => p_commit,
464 p_validation_level => p_validation_level,
465 p_rule_rec => l_rule_rec,
466 x_return_status => x_return_status,
467 x_msg_count => x_msg_count,
468 x_msg_data => x_msg_data
469 );
470
471 -- check for the return status
472 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
473 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
474 FND_LOG.string(FND_LOG.level_statement,l_full_name,
475 'Raising exception with x_return_status => '||x_return_status);
476 END IF;
477
478 RAISE FND_API.G_EXC_ERROR;
479 END IF;
480
481 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
482 FND_LOG.string(FND_LOG.level_statement,l_full_name,'After call to private API.');
483 END IF;
484
485 -- Standard check of p_commit
486 IF FND_API.TO_BOOLEAN(p_commit) THEN
487 COMMIT WORK;
488 END IF;
489
490 -- Standard call to get message count and if count is 1, get message info
491 FND_MSG_PUB.Count_And_Get
492 ( p_count => x_msg_count,
493 p_data => x_msg_data,
494 p_encoded => FND_API.G_FALSE
495 );
496
497 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
498 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
499 END IF;
500
501 EXCEPTION
502 WHEN FND_API.G_EXC_ERROR THEN
503 Rollback to Delete_Rule_Pub;
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
506 p_data => x_msg_data,
507 p_encoded => fnd_api.g_false);
508
509 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
510 Rollback to Delete_Rule_Pub;
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
512 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
513 p_data => x_msg_data,
514 p_encoded => fnd_api.g_false);
515
516 WHEN OTHERS THEN
517 Rollback to Delete_Rule_Pub;
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
520 p_procedure_name => l_api_name,
521 p_error_text => SQLERRM);
522 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
523 p_data => x_msg_data,
524 p_encoded => FND_API.G_FALSE);
525
526 END Delete_Rule;
527
528 ------------------------------------------------------------------------------------------
529 -- Start of Comments
530 -- Procedure name : Copy_Rules_For_MC
531 -- Type : Public
532 -- Function : Does user input validation and calls private API Copy_Rules_For_MC
533 -- Pre-reqs :
534 -- Parameters :
535 --
536 -- Copy_Rules_For_MC Parameters:
537 -- p_from_mc_header_id IN NUMBER Required
538 -- or
539 -- p_to_mc_name IN VARCHAR2 Required
540 -- p_to_revision IN NUMBER Required
541 --
542 -- p_to_mc_header_id IN NUMBER Required
543 -- or
544 -- p_from_mc_name IN VARCHAR2 Required
545 -- p_from_revision IN NUMBER Required
546 --
547 -- End of Comments
548
549 PROCEDURE Copy_Rules_For_MC (
550 p_api_version IN NUMBER,
551 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
552 p_commit IN VARCHAR2 := FND_API.G_FALSE,
553 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
554 p_from_mc_header_id IN NUMBER,
555 p_to_mc_header_id IN NUMBER,
556 p_from_mc_name IN VARCHAR2,
557 p_from_revision IN VARCHAR2,
558 p_to_mc_name IN VARCHAR2,
559 p_to_revision IN VARCHAR2,
560 x_return_status OUT NOCOPY VARCHAR2,
561 x_msg_count OUT NOCOPY NUMBER,
562 x_msg_data OUT NOCOPY VARCHAR2
563 ) IS
564
565 CURSOR chk_mc_header_id_csr (p_mc_header_id NUMBER) IS
566 SELECT 'X'
567 FROM AHL_MC_HEADERS_B
568 WHERE mc_header_id = p_mc_header_id;
569
570 CURSOR chk_mc_name_csr (p_mc_name VARCHAR2, p_revision VARCHAR2) IS
571 SELECT mc_header_id
572 FROM AHL_MC_HEADERS_B
573 WHERE name = p_mc_name
574 AND revision = p_revision;
575 --
576 l_api_version CONSTANT NUMBER := 1.0;
577 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Rules_For_MC';
578 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
579
580 l_from_mc_header_id NUMBER;
581 l_to_mc_header_id NUMBER;
582 l_dummy VARCHAR2(1);
583 --
584
585 BEGIN
586 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
587 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
588 END IF;
589
590 -- Standard start of API savepoint
591 SAVEPOINT Copy_Rules_For_MC_Pub;
592
593 -- Initialize Procedure return status to success
594 x_return_status := FND_API.G_RET_STS_SUCCESS;
595
596 -- Standard call to check for call compatibility
597 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
598 l_api_name, G_PKG_NAME) THEN
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
600 END IF;
601
602 -- Initialize message list if p_init_msg_list is set to TRUE
603 IF FND_API.To_Boolean(p_init_msg_list) THEN
604 FND_MSG_PUB.Initialize;
605 END IF;
606
607 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
608 FND_LOG.string(FND_LOG.level_statement,l_full_name,
609 ' p_from_mc_header_id => '||p_from_mc_header_id||
610 ' p_to_mc_header_id => '||p_to_mc_header_id||
611 ' p_from_mc_name => '||p_from_mc_name||
612 ' p_to_mc_name => '||p_to_mc_name||
613 ' p_from_revision => '||p_from_revision||
614 ' p_to_revision => '||p_to_revision);
615 END IF;
616
617 -- check input parameters
618 -- checking for from MC
619 IF (p_from_mc_header_id IS NULL) THEN
620 -- check with mc_name and revision
621 IF (p_from_mc_name IS NULL) OR (p_from_revision IS NULL) THEN
622 -- input is NULL
623 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NULL');
624 FND_MSG_PUB.ADD;
625 RAISE FND_API.G_EXC_ERROR;
626 ELSE
627 OPEN chk_mc_name_csr(p_from_mc_name, p_from_revision);
628 FETCH chk_mc_name_csr INTO l_from_mc_header_id;
629
630 IF (chk_mc_name_csr%NOTFOUND) THEN
631 CLOSE chk_mc_name_csr;
632 -- input is invalid
633 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NAME_INV');
634 FND_MESSAGE.Set_Token('MC_NAME',p_from_mc_name);
635 FND_MESSAGE.Set_Token('MC_REV',p_from_revision);
636 FND_MSG_PUB.ADD;
637 RAISE FND_API.G_EXC_ERROR;
638 END IF;
639
640 CLOSE chk_mc_name_csr;
641 END IF;
642 ELSE
643 -- check with mc_header_id
644 OPEN chk_mc_header_id_csr(p_from_mc_header_id);
645 FETCH chk_mc_header_id_csr INTO l_dummy;
646
647 IF (chk_mc_header_id_csr%NOTFOUND) THEN
648 CLOSE chk_mc_header_id_csr;
649 -- input is invalid
650 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_HDR_ID_INV');
651 FND_MESSAGE.Set_Token('MC_ID',p_from_mc_header_id);
652 FND_MSG_PUB.ADD;
653 RAISE FND_API.G_EXC_ERROR;
654 END IF;
655
656 l_from_mc_header_id := p_from_mc_header_id;
657 CLOSE chk_mc_header_id_csr;
658 END IF;
659
660 -- checking for to MC
661 IF (p_to_mc_header_id IS NULL) THEN
662 -- check with mc_name and revision
663 IF (p_to_mc_name IS NULL) OR (p_to_revision IS NULL) THEN
664 -- input is NULL
665 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NULL');
666 FND_MSG_PUB.ADD;
667 RAISE FND_API.G_EXC_ERROR;
668 ELSE
669 OPEN chk_mc_name_csr(p_to_mc_name, p_to_revision);
670 FETCH chk_mc_name_csr INTO l_to_mc_header_id;
671
672 IF (chk_mc_name_csr%NOTFOUND) THEN
673 CLOSE chk_mc_name_csr;
674 -- input is invalid
675 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NAME_INV');
676 FND_MESSAGE.Set_Token('MC_NAME',p_to_mc_name);
677 FND_MESSAGE.Set_Token('MC_REV',p_to_revision);
678 FND_MSG_PUB.ADD;
679 RAISE FND_API.G_EXC_ERROR;
680 END IF;
681
682 CLOSE chk_mc_name_csr;
683 END IF;
684 ELSE
685 -- check with mc_header_id
686 OPEN chk_mc_header_id_csr(p_to_mc_header_id);
687 FETCH chk_mc_header_id_csr INTO l_dummy;
688
689 IF (chk_mc_header_id_csr%NOTFOUND) THEN
690 CLOSE chk_mc_header_id_csr;
691 -- input is invalid
692 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_HDR_ID_INV');
693 FND_MESSAGE.Set_Token('MC_ID',p_to_mc_header_id);
694 FND_MSG_PUB.ADD;
695 RAISE FND_API.G_EXC_ERROR;
696 END IF;
697
698 l_to_mc_header_id := p_to_mc_header_id;
699 CLOSE chk_mc_header_id_csr;
700 END IF;
701
702 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
703 FND_LOG.string(FND_LOG.level_statement,l_full_name,
704 ' p_from_mc_header_id => '||l_from_mc_header_id||
705 ' p_to_mc_header_id => '||l_to_mc_header_id||
706 ' p_from_mc_name => '||p_from_mc_name||
707 ' p_to_mc_name => '||p_to_mc_name||
708 ' p_from_revision => '||p_from_revision||
709 ' p_to_revision => '||p_to_revision);
710 END IF;
711
712 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
713 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Calling private API.');
714 END IF;
715
716 -- call the private API
717 AHL_MC_RULE_PVT.Copy_Rules_For_MC(
718 p_api_version => p_api_version,
719 p_init_msg_list => p_init_msg_list,
720 p_commit => p_commit,
721 p_validation_level => p_validation_level,
722 p_from_mc_header_id => l_from_mc_header_id,
723 p_to_mc_header_id => l_to_mc_header_id,
724 x_return_status => x_return_status,
725 x_msg_count => x_msg_count,
726 x_msg_data => x_msg_data
727 );
728
729 -- check for the return status
730 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
731 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
732 FND_LOG.string(FND_LOG.level_statement,l_full_name,
733 'Raising exception with x_return_status => '||x_return_status);
734 END IF;
735
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738
739 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
740 FND_LOG.string(FND_LOG.level_statement,l_full_name,'After call to private API.');
741 END IF;
742
743 -- Standard check of p_commit
744 IF FND_API.TO_BOOLEAN(p_commit) THEN
745 COMMIT WORK;
746 END IF;
747
748 -- Standard call to get message count and if count is 1, get message info
749 FND_MSG_PUB.Count_And_Get
750 ( p_count => x_msg_count,
751 p_data => x_msg_data,
752 p_encoded => FND_API.G_FALSE
753 );
754
755 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
756 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
757 END IF;
758
759 EXCEPTION
760 WHEN FND_API.G_EXC_ERROR THEN
761 Rollback to Copy_Rules_For_MC_Pub;
762 x_return_status := FND_API.G_RET_STS_ERROR;
763 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
764 p_data => x_msg_data,
765 p_encoded => fnd_api.g_false);
766
767 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
768 Rollback to Copy_Rules_For_MC_Pub;
769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
770 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
771 p_data => x_msg_data,
772 p_encoded => fnd_api.g_false);
773
774 WHEN OTHERS THEN
775 Rollback to Copy_Rules_For_MC_Pub;
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
777 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
778 p_procedure_name => l_api_name,
779 p_error_text => SQLERRM);
780 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
781 p_data => x_msg_data,
782 p_encoded => FND_API.G_FALSE);
783
784 END Copy_Rules_For_MC;
785
786 --------------------------------------------------------------------------------------------
787 -- Start of Comments
788 -- Procedure name : Delete_Rules_For_MC
789 -- Type : Public
790 -- Function : Does user input validation and calls private API Delete_Rules_For_MC
791 -- Pre-reqs :
792 -- Parameters :
793 --
794 -- Delete_Rules_For_MC Parameters:
795 -- p_mc_header_id IN NUMBER Required
796 -- or
797 -- p_mc_name IN VARCHAR2 Required
798 -- p_revision IN NUMBER Required
799 --
800 -- End of Comments
801
802 PROCEDURE Delete_Rules_For_MC (
803 p_api_version IN NUMBER,
804 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
805 p_commit IN VARCHAR2 := FND_API.G_FALSE,
806 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
807 p_mc_header_id IN NUMBER,
808 p_mc_name IN VARCHAR2,
809 p_revision IN VARCHAR2,
810 x_return_status OUT NOCOPY VARCHAR2,
811 x_msg_count OUT NOCOPY NUMBER,
812 x_msg_data OUT NOCOPY VARCHAR2
813 ) IS
814
815 CURSOR chk_mc_header_id_csr (p_mc_header_id NUMBER) IS
816 SELECT 'X'
817 FROM AHL_MC_HEADERS_B
818 WHERE mc_header_id = p_mc_header_id;
819
820 CURSOR chk_mc_name_csr (p_mc_name VARCHAR2, p_revision VARCHAR2) IS
821 SELECT mc_header_id
822 FROM AHL_MC_HEADERS_B
823 WHERE name = p_mc_name
824 AND revision = p_revision;
825 --
826 l_api_version CONSTANT NUMBER := 1.0;
827 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Rules_For_MC';
828 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
829
830 l_mc_header_id NUMBER;
831 l_dummy VARCHAR2(1);
832 --
833
834 BEGIN
835 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
836 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
837 END IF;
838
839 -- Standard start of API savepoint
840 SAVEPOINT Delete_Rules_For_MC_Pub;
841
842 -- Initialize Procedure return status to success
843 x_return_status := FND_API.G_RET_STS_SUCCESS;
844
845 -- Standard call to check for call compatibility
846 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
847 l_api_name, G_PKG_NAME) THEN
848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
849 END IF;
850
851 -- Initialize message list if p_init_msg_list is set to TRUE
852 IF FND_API.To_Boolean(p_init_msg_list) THEN
853 FND_MSG_PUB.Initialize;
854 END IF;
855
856 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
857 FND_LOG.string(FND_LOG.level_statement,l_full_name,
858 ' p_mc_header_id => '||p_mc_header_id||
859 ' p_mc_name => '||p_mc_name||
860 ' p_revision => '||p_revision);
861 END IF;
862
863 -- check input parameters
864 IF (p_mc_header_id IS NULL) THEN
865 -- check with mc_name and revision
866 IF (p_mc_name IS NULL) OR (p_revision IS NULL) THEN
867 -- input is NULL
868 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NULL');
869 FND_MSG_PUB.ADD;
870 RAISE FND_API.G_EXC_ERROR;
871 ELSE
872 OPEN chk_mc_name_csr(p_mc_name, p_revision);
873 FETCH chk_mc_name_csr INTO l_mc_header_id;
874
875 IF (chk_mc_name_csr%NOTFOUND) THEN
876 CLOSE chk_mc_name_csr;
877 -- input is invalid
878 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NAME_INV');
879 FND_MESSAGE.Set_Token('MC_NAME',p_mc_name);
880 FND_MESSAGE.Set_Token('MC_REV',p_revision);
881 FND_MSG_PUB.ADD;
882 RAISE FND_API.G_EXC_ERROR;
883 END IF;
884
885 CLOSE chk_mc_name_csr;
886 END IF;
887 ELSE
888 -- check with mc_header_id
889 OPEN chk_mc_header_id_csr(p_mc_header_id);
890 FETCH chk_mc_header_id_csr INTO l_dummy;
891
892 IF (chk_mc_header_id_csr%NOTFOUND) THEN
893 CLOSE chk_mc_header_id_csr;
894 -- input is invalid
895 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_HDR_ID_INV');
896 FND_MESSAGE.Set_Token('MC_ID',p_mc_header_id);
897 FND_MSG_PUB.ADD;
898 RAISE FND_API.G_EXC_ERROR;
899 END IF;
900
901 l_mc_header_id := p_mc_header_id;
902 CLOSE chk_mc_header_id_csr;
903 END IF;
904
905 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
906 FND_LOG.string(FND_LOG.level_statement,l_full_name,
907 ' p_mc_header_id => '||l_mc_header_id||
908 ' p_mc_name => '||p_mc_name||
909 ' p_revision => '||p_revision);
910 END IF;
911
912 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
913 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Calling private API.');
914 END IF;
915
916 -- call the private API
917 AHL_MC_RULE_PVT.Delete_Rules_For_MC(
918 p_api_version => p_api_version,
919 p_init_msg_list => p_init_msg_list,
920 p_commit => p_commit,
921 p_validation_level => p_validation_level,
922 p_mc_header_id => l_mc_header_id,
923 x_return_status => x_return_status,
924 x_msg_count => x_msg_count,
925 x_msg_data => x_msg_data
926 );
927
928 -- check for the return status
929 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
930 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
931 FND_LOG.string(FND_LOG.level_statement,l_full_name,
932 'Raising exception with x_return_status => '||x_return_status);
933 END IF;
934
935 RAISE FND_API.G_EXC_ERROR;
936 END IF;
937
938 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
939 FND_LOG.string(FND_LOG.level_statement,l_full_name,'After call to private API.');
940 END IF;
941
942 -- Standard check of p_commit
943 IF FND_API.TO_BOOLEAN(p_commit) THEN
944 COMMIT WORK;
945 END IF;
946
947 -- Standard call to get message count and if count is 1, get message info
948 FND_MSG_PUB.Count_And_Get
949 ( p_count => x_msg_count,
950 p_data => x_msg_data,
951 p_encoded => FND_API.G_FALSE
952 );
953
954 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
955 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
956 END IF;
957
958 EXCEPTION
959 WHEN FND_API.G_EXC_ERROR THEN
960 Rollback to Delete_Rules_For_MC_Pub;
961 x_return_status := FND_API.G_RET_STS_ERROR;
962 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
963 p_data => x_msg_data,
964 p_encoded => fnd_api.g_false);
965
966 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
967 Rollback to Delete_Rules_For_MC_Pub;
968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
969 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
970 p_data => x_msg_data,
971 p_encoded => fnd_api.g_false);
972
973 WHEN OTHERS THEN
974 Rollback to Delete_Rules_For_MC_Pub;
975 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
976 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
977 p_procedure_name => l_api_name,
978 p_error_text => SQLERRM);
979 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
980 p_data => x_msg_data,
981 p_encoded => FND_API.G_FALSE);
982
983 END Delete_Rules_For_MC;
984
985 -----------------------------------------------------------------------------------------------
986 -- Start of Comments
987 -- Procedure name : Get_Rules_For_Position
988 -- Type : Public
989 -- Function : Does user input validation and calls private API Get_Rules_For_Position
990 -- Pre-reqs :
991 -- Parameters :
992 --
993 -- Get_Rules_For_Position Parameters:
994 -- p_encoded_path IN VARCHAR2 Required
995 --
996 -- p_mc_header_id IN NUMBER Required
997 -- or
998 -- p_mc_name IN VARCHAR2 Required
999 -- p_revision IN NUMBER Required
1000 --
1001 -- x_rule_tbl OUT AHL_MC_RULE_PVT.Rule_Tbl_Type Required
1002 --
1003 -- End of Comments
1004
1005 PROCEDURE Get_Rules_For_Position (
1006 p_api_version IN NUMBER,
1007 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1008 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1009 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1010 p_mc_header_id IN NUMBER,
1011 p_encoded_path IN VARCHAR2,
1012 p_mc_name IN VARCHAR2,
1013 p_revision IN VARCHAR2,
1014 x_rule_tbl OUT NOCOPY AHL_MC_RULE_PVT.Rule_Tbl_Type,
1015 x_return_status OUT NOCOPY VARCHAR2,
1016 x_msg_count OUT NOCOPY NUMBER,
1017 x_msg_data OUT NOCOPY VARCHAR2
1018 ) IS
1019
1020 CURSOR chk_mc_header_id_csr (p_mc_header_id NUMBER) IS
1021 SELECT 'X'
1022 FROM AHL_MC_HEADERS_B
1023 WHERE mc_header_id = p_mc_header_id;
1024
1025 CURSOR chk_mc_name_csr (p_mc_name VARCHAR2, p_revision VARCHAR2) IS
1026 SELECT mc_header_id
1027 FROM AHL_MC_HEADERS_B
1028 WHERE name = p_mc_name
1029 AND revision = p_revision;
1030 --
1031 l_api_version CONSTANT NUMBER := 1.0;
1032 l_api_name CONSTANT VARCHAR2(30) := 'Get_Rules_For_Position';
1033 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1034
1035 l_mc_header_id NUMBER;
1036 l_dummy VARCHAR2(1);
1037 --
1038
1039 BEGIN
1040 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1041 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
1042 END IF;
1043
1044 -- Standard start of API savepoint
1045 SAVEPOINT Get_Rules_For_Position_Pub;
1046
1047 -- Initialize Procedure return status to success
1048 x_return_status := FND_API.G_RET_STS_SUCCESS;
1049
1050 -- Standard call to check for call compatibility
1051 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1052 l_api_name, G_PKG_NAME) THEN
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054 END IF;
1055
1056 -- Initialize message list if p_init_msg_list is set to TRUE
1057 IF FND_API.To_Boolean(p_init_msg_list) THEN
1058 FND_MSG_PUB.Initialize;
1059 END IF;
1060
1061 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1062 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1063 ' p_mc_header_id => '||p_mc_header_id||
1064 ' p_encoded_path => '||p_encoded_path||
1065 ' p_mc_name => '||p_mc_name||
1066 ' p_revision => '||p_revision);
1067 END IF;
1068
1069 -- check input parameters
1070 IF (p_encoded_path IS NULL) THEN
1071 -- encoded path cant be NULL
1072 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_PATH_NULL');
1073 FND_MSG_PUB.ADD;
1074 RAISE FND_API.G_EXC_ERROR;
1075 ELSIF (p_mc_header_id IS NULL) THEN
1076 -- check with mc_name and revision
1077 IF (p_mc_name IS NULL) OR (p_revision IS NULL) THEN
1078 -- input is NULL
1079 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NULL');
1080 FND_MSG_PUB.ADD;
1081 RAISE FND_API.G_EXC_ERROR;
1082 ELSE
1083 OPEN chk_mc_name_csr(p_mc_name, p_revision);
1084 FETCH chk_mc_name_csr INTO l_mc_header_id;
1085
1086 IF (chk_mc_name_csr%NOTFOUND) THEN
1087 CLOSE chk_mc_name_csr;
1088 -- input is invalid
1089 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NAME_INV');
1090 FND_MESSAGE.Set_Token('MC_NAME',p_mc_name);
1091 FND_MESSAGE.Set_Token('MC_REV',p_revision);
1092 FND_MSG_PUB.ADD;
1093 RAISE FND_API.G_EXC_ERROR;
1094 END IF;
1095
1096 CLOSE chk_mc_name_csr;
1097 END IF;
1098 ELSE
1099 -- check with mc_header_id
1100 OPEN chk_mc_header_id_csr(p_mc_header_id);
1101 FETCH chk_mc_header_id_csr INTO l_dummy;
1102
1103 IF (chk_mc_header_id_csr%NOTFOUND) THEN
1104 CLOSE chk_mc_header_id_csr;
1105 -- input is invalid
1106 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_HDR_ID_INV');
1107 FND_MESSAGE.Set_Token('MC_ID',p_mc_header_id);
1108 FND_MSG_PUB.ADD;
1109 RAISE FND_API.G_EXC_ERROR;
1110 END IF;
1111
1112 l_mc_header_id := p_mc_header_id;
1113 CLOSE chk_mc_header_id_csr;
1114 END IF;
1115
1116 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1117 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1118 ' p_mc_header_id => '||l_mc_header_id||
1119 ' p_encoded_path => '||p_encoded_path||
1120 ' p_mc_name => '||p_mc_name||
1121 ' p_revision => '||p_revision);
1122 END IF;
1123
1124 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1125 FND_LOG.string(FND_LOG.level_statement,l_full_name,'Calling private API.');
1126 END IF;
1127
1128 -- call the private API
1129 AHL_MC_RULE_PVT.Get_Rules_For_Position(
1130 p_api_version => p_api_version,
1131 p_init_msg_list => p_init_msg_list,
1132 p_commit => p_commit,
1133 p_validation_level => p_validation_level,
1134 p_mc_header_id => l_mc_header_id,
1135 p_encoded_path => p_encoded_path,
1136 x_rule_tbl => x_rule_tbl,
1137 x_return_status => x_return_status,
1138 x_msg_count => x_msg_count,
1139 x_msg_data => x_msg_data
1140 );
1141
1142 -- check for the return status
1143 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1144 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1145 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1146 'Raising exception with x_return_status => '||x_return_status);
1147 END IF;
1148
1149 RAISE FND_API.G_EXC_ERROR;
1150 END IF;
1151
1152 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1153 FND_LOG.string(FND_LOG.level_statement,l_full_name,'After call to private API.');
1154 END IF;
1155
1156 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1157 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1158 ' x_rule_tbl.COUNT => '||x_rule_tbl.COUNT);
1159 END IF;
1160
1161 -- Standard check of p_commit
1162 IF FND_API.TO_BOOLEAN(p_commit) THEN
1163 COMMIT WORK;
1164 END IF;
1165
1166 -- Standard call to get message count and if count is 1, get message info
1167 FND_MSG_PUB.Count_And_Get
1168 ( p_count => x_msg_count,
1169 p_data => x_msg_data,
1170 p_encoded => FND_API.G_FALSE
1171 );
1172
1173 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1174 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
1175 END IF;
1176
1177 EXCEPTION
1178 WHEN FND_API.G_EXC_ERROR THEN
1179 Rollback to Get_Rules_For_Position_Pub;
1180 x_return_status := FND_API.G_RET_STS_ERROR;
1181 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1182 p_data => x_msg_data,
1183 p_encoded => fnd_api.g_false);
1184
1185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1186 Rollback to Get_Rules_For_Position_Pub;
1187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1188 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1189 p_data => x_msg_data,
1190 p_encoded => fnd_api.g_false);
1191
1192 WHEN OTHERS THEN
1193 Rollback to Get_Rules_For_Position_Pub;
1194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1195 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
1196 p_procedure_name => l_api_name,
1197 p_error_text => SQLERRM);
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
1202 END Get_Rules_For_Position;
1203
1204 -----------------------------------------------------------------------------------------------
1205 -- Start of Comments
1206 -- Procedure name : Convert_Rule_Values_to_Id
1207 -- Type : Local
1208 -- Function : Does user input validation and value to id conversion
1209 -- Pre-reqs :
1210 -- Parameters :
1211 --
1212 -- Convert_Rule_Values_to_Id Parameters:
1213 -- p_x_rule_rec IN OUT AHL_MC_RULE_PVT.Rule_Rec_Type
1214 --
1215 -- End of Comments
1216
1217 PROCEDURE Convert_Rule_Values_to_Id (
1218 p_x_rule_rec IN OUT NOCOPY AHL_MC_RULE_PVT.Rule_Rec_Type,
1219 p_operation_flag IN VARCHAR2
1220 ) IS
1221
1222 CURSOR chk_mc_header_id_csr (p_mc_header_id NUMBER) IS
1223 SELECT 'X'
1224 FROM AHL_MC_HEADERS_B
1225 WHERE mc_header_id = p_mc_header_id;
1226
1227 CURSOR chk_mc_name_csr (p_mc_name VARCHAR2, p_revision VARCHAR2) IS
1228 SELECT mc_header_id
1229 FROM AHL_MC_HEADERS_B
1230 WHERE name = p_mc_name
1231 AND revision = p_revision;
1232
1233 CURSOR chk_rule_id_csr (p_rule_id NUMBER) IS
1234 SELECT 'X'
1235 FROM AHL_MC_RULES_B
1236 WHERE rule_id = p_rule_id;
1237
1238 CURSOR chk_rule_name_csr (p_rule_name VARCHAR2, p_mc_header_id NUMBER) IS
1239 SELECT rule_id
1240 FROM AHL_MC_RULES_B
1241 WHERE rule_name = p_rule_name
1242 AND mc_header_id = p_mc_header_id;
1243
1244 CURSOR chk_lookup_code_csr (p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
1245 SELECT 'X'
1246 FROM FND_LOOKUPS
1247 WHERE lookup_type = p_lookup_type
1248 AND lookup_code = p_lookup_code;
1249 --
1250 l_api_name CONSTANT VARCHAR2(30) := 'Convert_Rule_Values_to_Id';
1251 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1252
1253 l_rule_rec AHL_MC_RULE_PVT.Rule_Rec_Type DEFAULT p_x_rule_rec;
1254 l_rule_id NUMBER;
1255 l_mc_header_id NUMBER;
1256 l_rule_type_code FND_LOOKUPS.LOOKUP_CODE%TYPE;
1257 l_return_val BOOLEAN;
1258 l_dummy VARCHAR2(1);
1259 --
1260
1261 BEGIN
1262 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1263 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
1264 END IF;
1265
1266 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1267 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1268 ' p_operation_flag => '||p_operation_flag);
1269 END IF;
1270
1271 -- for insertion
1272 IF (p_operation_flag = 'I')THEN
1273 -- check for mc_header_id
1274 IF (l_rule_rec.mc_header_id IS NULL) THEN
1275 -- check with mc_name and revision
1276 IF (l_rule_rec.mc_name IS NULL) OR (l_rule_rec.mc_revision IS NULL) THEN
1277 -- input is NULL
1278 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NULL');
1279 FND_MSG_PUB.ADD;
1280 RAISE FND_API.G_EXC_ERROR;
1281 ELSE
1282 OPEN chk_mc_name_csr(l_rule_rec.mc_name, l_rule_rec.mc_revision);
1283 FETCH chk_mc_name_csr INTO l_mc_header_id;
1284
1285 IF (chk_mc_name_csr%NOTFOUND) THEN
1286 CLOSE chk_mc_name_csr;
1287 -- input is invalid
1288 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NAME_INV');
1289 FND_MESSAGE.Set_Token('MC_NAME',l_rule_rec.mc_name);
1290 FND_MESSAGE.Set_Token('MC_REV',l_rule_rec.mc_revision);
1291 FND_MSG_PUB.ADD;
1292 RAISE FND_API.G_EXC_ERROR;
1293 END IF;
1294
1295 -- set the mc_header_id
1296 l_rule_rec.mc_header_id := l_mc_header_id;
1297 CLOSE chk_mc_name_csr;
1298 END IF;
1299 ELSE
1300 -- check with mc_header_id
1301 OPEN chk_mc_header_id_csr(l_rule_rec.mc_header_id);
1302 FETCH chk_mc_header_id_csr INTO l_dummy;
1303
1304 IF (chk_mc_header_id_csr%NOTFOUND) THEN
1305 CLOSE chk_mc_header_id_csr;
1306 -- input is invalid
1307 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_HDR_ID_INV');
1308 FND_MESSAGE.Set_Token('MC_ID',l_rule_rec.mc_header_id);
1309 FND_MSG_PUB.ADD;
1310 RAISE FND_API.G_EXC_ERROR;
1311 END IF;
1312
1313 CLOSE chk_mc_header_id_csr;
1314 END IF;
1315
1316 -- check for rule_type_code
1317 IF (l_rule_rec.rule_type_code IS NULL) THEN
1318 -- check for rule_type_meaning
1319 IF (l_rule_rec.rule_type_meaning IS NULL) THEN
1320 -- input is NULL
1321 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_TYPE_NULL');
1322 FND_MSG_PUB.ADD;
1323 RAISE FND_API.G_EXC_ERROR;
1324 ELSE
1325 -- convert the meaning into code
1326 AHL_UTIL_MC_PKG.Convert_To_LookupCode(
1327 p_lookup_type => 'AHL_MC_RULE_TYPES',
1328 p_lookup_meaning => l_rule_rec.rule_type_meaning,
1329 x_lookup_code => l_rule_type_code,
1330 x_return_val => l_return_val
1331 );
1332
1333 IF (l_return_val) THEN
1334 -- set the rule_type_code
1335 l_rule_rec.rule_type_code := l_rule_type_code;
1336 ELSE
1337 -- input is invalid
1338 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_TYPE_INV');
1339 FND_MESSAGE.Set_Token('RULE_TYPE',l_rule_rec.rule_type_meaning);
1340 FND_MSG_PUB.ADD;
1341 RAISE FND_API.G_EXC_ERROR;
1342 END IF;
1343 END IF;
1344 ELSE
1345 -- check with rule_type_code
1346 OPEN chk_lookup_code_csr('AHL_MC_RULE_TYPES', l_rule_rec.rule_type_code);
1347 FETCH chk_lookup_code_csr INTO l_dummy;
1348
1349 IF (chk_lookup_code_csr%NOTFOUND) THEN
1350 CLOSE chk_lookup_code_csr;
1351 -- input is invalid
1352 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_TYPE_INV');
1353 FND_MESSAGE.Set_Token('RULE_TYPE',l_rule_rec.rule_type_code);
1354 FND_MSG_PUB.ADD;
1355 RAISE FND_API.G_EXC_ERROR;
1356 END IF;
1357
1358 CLOSE chk_lookup_code_csr;
1359 END IF;
1360 END IF;
1361
1362 -- for updation and deletion
1363 IF (p_operation_flag = 'D') OR (p_operation_flag = 'U') THEN
1364 -- check for rule_id
1365 IF (l_rule_rec.rule_id IS NULL) THEN
1366 -- check with rule_name and mc_header_id
1367 IF (l_rule_rec.rule_name IS NOT NULL) THEN
1368 -- check for mc_header_id
1369 IF (l_rule_rec.mc_header_id IS NOT NULL) THEN
1370 OPEN chk_rule_name_csr(l_rule_rec.rule_name, l_rule_rec.mc_header_id);
1371 FETCH chk_rule_name_csr INTO l_rule_id;
1372
1373 IF (chk_rule_name_csr%NOTFOUND) THEN
1374 CLOSE chk_rule_name_csr;
1375 -- input is invalid
1376 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_RL_NAME_INV');
1377 FND_MESSAGE.Set_Token('RULE_NAME',l_rule_rec.rule_name);
1378 FND_MESSAGE.Set_Token('MC_ID',l_rule_rec.mc_header_id);
1379 FND_MSG_PUB.ADD;
1380 RAISE FND_API.G_EXC_ERROR;
1381 END IF;
1382
1383 -- set the rule_id
1384 l_rule_rec.rule_id := l_rule_id;
1385 CLOSE chk_rule_name_csr;
1386 ELSE
1387 -- check with mc_name and revision
1388 IF (l_rule_rec.mc_name IS NULL) OR (l_rule_rec.mc_revision IS NULL) THEN
1389 -- input is NULL
1390 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NULL');
1391 FND_MSG_PUB.ADD;
1392 RAISE FND_API.G_EXC_ERROR;
1393 ELSE
1394 OPEN chk_mc_name_csr(l_rule_rec.mc_name, l_rule_rec.mc_revision);
1395 FETCH chk_mc_name_csr INTO l_mc_header_id;
1396
1397 IF (chk_mc_name_csr%NOTFOUND) THEN
1398 CLOSE chk_mc_name_csr;
1399 -- input is invalid
1400 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_NAME_INV');
1401 FND_MESSAGE.Set_Token('MC_NAME',l_rule_rec.mc_name);
1402 FND_MESSAGE.Set_Token('MC_REV',l_rule_rec.mc_revision);
1403 FND_MSG_PUB.ADD;
1404 RAISE FND_API.G_EXC_ERROR;
1405 END IF;
1406
1407 -- set the mc_header_id
1408 l_rule_rec.mc_header_id := l_mc_header_id;
1409 CLOSE chk_mc_name_csr;
1410
1411 -- get the rule_id from rule_name and mc_header_id
1412 OPEN chk_rule_name_csr(l_rule_rec.rule_name, l_rule_rec.mc_header_id);
1413 FETCH chk_rule_name_csr INTO l_rule_id;
1414
1415 IF (chk_rule_name_csr%NOTFOUND) THEN
1416 CLOSE chk_rule_name_csr;
1417 -- input is invalid
1418 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_RL_NAME_INV');
1419 FND_MESSAGE.Set_Token('RULE_NAME',l_rule_rec.rule_name);
1420 FND_MESSAGE.Set_Token('MC_ID',l_rule_rec.mc_header_id);
1421 FND_MSG_PUB.ADD;
1422 RAISE FND_API.G_EXC_ERROR;
1423 END IF;
1424
1425 -- set the rule_id
1426 l_rule_rec.rule_id := l_rule_id;
1427 CLOSE chk_rule_name_csr;
1428 END IF;
1429 END IF;
1430 ELSE
1431 -- input is NULL
1432 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_RL_NULL');
1433 FND_MSG_PUB.ADD;
1434 RAISE FND_API.G_EXC_ERROR;
1435 END IF;
1436 ELSE
1437 -- check with rule_id
1438 OPEN chk_rule_id_csr(l_rule_rec.rule_id);
1439 FETCH chk_rule_id_csr INTO l_dummy;
1440
1441 IF (chk_rule_id_csr%NOTFOUND) THEN
1442 CLOSE chk_rule_id_csr;
1443 -- input is invalid
1444 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_RL_ID_INV');
1445 FND_MESSAGE.Set_Token('RULE_ID',l_rule_rec.rule_id);
1446 FND_MSG_PUB.ADD;
1447 RAISE FND_API.G_EXC_ERROR;
1448 END IF;
1449
1450 CLOSE chk_rule_id_csr;
1451 END IF;
1452 END IF;
1453
1454 -- return changed record
1455 p_x_rule_rec := l_rule_rec;
1456
1457 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1458 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
1459 END IF;
1460
1461 END Convert_Rule_Values_to_Id;
1462
1463 -----------------------------------------------------------------------------------------------
1464 -- Start of Comments
1465 -- Procedure name : Convert_Rule_Stmt_Values_to_Id
1466 -- Type : Local
1467 -- Function : Does user input validation and value to id conversion
1468 -- Pre-reqs :
1469 -- Parameters :
1470 --
1471 -- Convert_Rule_Stmt_Values_to_Id Parameters:
1472 -- p_x_ui_rule_stmt_tbl IN OUT AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type
1473 --
1474 -- End of Comments
1475
1476 PROCEDURE Convert_Rule_Stmt_Values_to_Id (
1477 p_x_ui_rule_stmt_tbl IN OUT NOCOPY AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type,
1478 p_operation_flag IN VARCHAR2
1479 ) IS
1480
1481 CURSOR chk_item_id_csr (p_item_id NUMBER) IS
1482 SELECT 'X'
1483 FROM MTL_SYSTEM_ITEMS_B KFV
1484 WHERE inventory_item_id = p_item_id
1485 AND EXISTS
1486 (SELECT 'X'
1487 FROM MTL_PARAMETERS MP
1488 WHERE MP.master_organization_id = KFV.organization_id
1489 AND MP.eam_enabled_flag = 'Y');
1490
1491 CURSOR chk_item_name_csr (p_item_name VARCHAR2) IS
1492 SELECT inventory_item_id
1493 FROM MTL_SYSTEM_ITEMS_KFV KFV
1494 WHERE concatenated_segments = p_item_name
1495 AND EXISTS
1496 (SELECT 'X'
1497 FROM MTL_PARAMETERS MP
1498 WHERE MP.master_organization_id = KFV.organization_id
1499 AND MP.eam_enabled_flag = 'Y');
1500
1501 CURSOR chk_mc_id_csr (p_mc_id NUMBER) IS
1502 SELECT 'X'
1503 FROM AHL_MC_HEADERS_B
1504 WHERE mc_id = p_mc_id;
1505
1506 CURSOR chk_mc_name_csr (p_mc_name VARCHAR2) IS
1507 SELECT mc_id
1508 FROM AHL_MC_HEADERS_B
1509 WHERE name = p_mc_name;
1510
1511 CURSOR chk_rule_stmt_id_csr (p_rule_stmt_id NUMBER) IS
1512 SELECT 'X'
1513 FROM AHL_MC_RULE_STATEMENTS
1514 WHERE rule_statement_id = p_rule_stmt_id;
1515
1516 CURSOR chk_lookup_code_csr (p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
1517 SELECT 'X'
1518 FROM FND_LOOKUPS
1519 WHERE lookup_type = p_lookup_type
1520 AND lookup_code = p_lookup_code;
1521 --
1522 l_api_name CONSTANT VARCHAR2(30) := 'Convert_Rule_Stmt_Values_to_Id';
1523 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1524
1525 l_item_id NUMBER;
1526 l_mc_id NUMBER;
1527 l_ui_rule_stmt_tbl AHL_MC_RULE_PVT.UI_Rule_Stmt_Tbl_Type DEFAULT p_x_ui_rule_stmt_tbl;
1528 l_rule_operator FND_LOOKUPS.LOOKUP_CODE%TYPE;
1529 l_rule_rule_operator FND_LOOKUPS.LOOKUP_CODE%TYPE;
1530 l_rule_object_type FND_LOOKUPS.LOOKUP_CODE%TYPE;
1531 l_return_val BOOLEAN;
1532 l_dummy VARCHAR2(1);
1533 l_flag VARCHAR2(1) := 'N';
1534 --
1535
1536 BEGIN
1537 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1538 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
1539 END IF;
1540
1541 FOR i IN l_ui_rule_stmt_tbl.FIRST..l_ui_rule_stmt_tbl.LAST LOOP
1542 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1543 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1544 ' i => '||i||
1545 ' l_ui_rule_stmt_tbl(i).rule_statement_id => '||l_ui_rule_stmt_tbl(i).rule_statement_id||
1546 ' l_ui_rule_stmt_tbl(i).rule_stmt_obj_ver_num => '||l_ui_rule_stmt_tbl(i).rule_stmt_obj_ver_num||
1547 ' l_ui_rule_stmt_tbl(i).operator => '||l_ui_rule_stmt_tbl(i).operator||
1548 ' l_ui_rule_stmt_tbl(i).operator_meaning => '||l_ui_rule_stmt_tbl(i).operator_meaning||
1549 ' l_ui_rule_stmt_tbl(i).rule_operator => '||l_ui_rule_stmt_tbl(i).rule_operator||
1550 ' l_ui_rule_stmt_tbl(i).rule_operator_meaning => '||l_ui_rule_stmt_tbl(i).rule_operator_meaning||
1551 ' l_ui_rule_stmt_tbl(i).object_type => '||l_ui_rule_stmt_tbl(i).object_type||
1552 ' l_ui_rule_stmt_tbl(i).object_type_meaning => '||l_ui_rule_stmt_tbl(i).object_type_meaning||
1553 ' l_ui_rule_stmt_tbl(i).object_id => '||l_ui_rule_stmt_tbl(i).object_id||
1554 ' l_ui_rule_stmt_tbl(i).object_meaning => '||l_ui_rule_stmt_tbl(i).object_meaning);
1555 END IF;
1556
1557 -- for updation, check for rule stmt id and object version number
1558 IF (p_operation_flag = 'U')THEN
1559 IF (l_ui_rule_stmt_tbl(i).rule_statement_id IS NULL) THEN
1560 -- input is NULL
1561 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_ID_NULL');
1562 FND_MSG_PUB.ADD;
1563 l_flag := 'Y';
1564 ELSE
1565 -- check with the rule stmt id
1566 OPEN chk_rule_stmt_id_csr(l_ui_rule_stmt_tbl(i).rule_statement_id);
1567 FETCH chk_rule_stmt_id_csr INTO l_dummy;
1568
1569 IF (chk_rule_stmt_id_csr%NOTFOUND) THEN
1570 -- input is invalid
1571 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_ID_INV');
1572 FND_MESSAGE.Set_Token('RULE_STMT_ID',l_ui_rule_stmt_tbl(i).rule_statement_id);
1573 FND_MSG_PUB.ADD;
1574 l_flag := 'Y';
1575 END IF;
1576
1577 CLOSE chk_rule_stmt_id_csr;
1578 END IF;
1579
1580 IF (l_ui_rule_stmt_tbl(i).rule_stmt_obj_ver_num IS NULL) THEN
1581 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_OBJ_VER_NULL');
1582 FND_MSG_PUB.ADD;
1583 l_flag := 'Y';
1584 END IF;
1585 END IF;
1586
1587 -- check for operator
1588 IF (l_ui_rule_stmt_tbl(i).operator IS NULL) THEN
1589 -- check for operator_meaning
1590 IF (l_ui_rule_stmt_tbl(i).operator_meaning IS NULL) THEN
1591 -- input is NULL
1592 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_STMT_OPER_NULL');
1593 FND_MSG_PUB.ADD;
1594 l_flag := 'Y';
1595 ELSE
1596 -- convert the meaning into code
1597 AHL_UTIL_MC_PKG.Convert_To_LookupCode(
1598 p_lookup_type => 'AHL_MC_RULE_ALL_OPERATORS',
1599 p_lookup_meaning => l_ui_rule_stmt_tbl(i).operator_meaning,
1600 x_lookup_code => l_rule_operator,
1601 x_return_val => l_return_val
1602 );
1603
1604 IF (l_return_val) THEN
1605 -- set the operator
1606 l_ui_rule_stmt_tbl(i).operator := l_rule_operator;
1607 ELSE
1608 -- input is invalid
1609 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_OPR_INV');
1610 FND_MESSAGE.Set_Token('OPR',l_ui_rule_stmt_tbl(i).operator_meaning);
1611 FND_MSG_PUB.ADD;
1612 l_flag := 'Y';
1613 END IF;
1614 END IF;
1615 ELSE
1616 -- check with operator
1617 OPEN chk_lookup_code_csr('AHL_MC_RULE_ALL_OPERATORS', l_ui_rule_stmt_tbl(i).operator);
1618 FETCH chk_lookup_code_csr INTO l_dummy;
1619
1620 IF (chk_lookup_code_csr%NOTFOUND) THEN
1621 -- input is invalid
1622 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_OPR_INV');
1623 FND_MESSAGE.Set_Token('OPR',l_ui_rule_stmt_tbl(i).operator);
1624 FND_MSG_PUB.ADD;
1625 l_flag := 'Y';
1626 END IF;
1627
1628 CLOSE chk_lookup_code_csr;
1629 END IF;
1630
1631 -- check for rule_operator
1632 IF (l_ui_rule_stmt_tbl(i).rule_operator IS NULL) THEN
1633 -- check for rule_operator_meaning
1634 IF (l_ui_rule_stmt_tbl(i).rule_operator_meaning IS NOT NULL) THEN
1635 -- convert the meaning into code
1636 AHL_UTIL_MC_PKG.Convert_To_LookupCode(
1637 p_lookup_type => 'AHL_MC_RULE_OPERATORS',
1638 p_lookup_meaning => l_ui_rule_stmt_tbl(i).rule_operator_meaning,
1639 x_lookup_code => l_rule_rule_operator,
1640 x_return_val => l_return_val
1641 );
1642
1643 IF (l_return_val) THEN
1644 -- set the rule_operator
1645 l_ui_rule_stmt_tbl(i).rule_operator := l_rule_rule_operator;
1646 ELSE
1647 -- input is invalid
1648 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_RL_OPR_INV');
1649 FND_MESSAGE.Set_Token('RL_OPR',l_ui_rule_stmt_tbl(i).rule_operator_meaning);
1650 FND_MSG_PUB.ADD;
1651 l_flag := 'Y';
1652 END IF;
1653 END IF;
1654 ELSE
1655 -- check with rule_operator
1656 OPEN chk_lookup_code_csr('AHL_MC_RULE_OPERATORS', l_ui_rule_stmt_tbl(i).rule_operator);
1657 FETCH chk_lookup_code_csr INTO l_dummy;
1658
1659 IF (chk_lookup_code_csr%NOTFOUND) THEN
1660 -- input is invalid
1661 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_RL_OPR_INV');
1662 FND_MESSAGE.Set_Token('RL_OPR',l_ui_rule_stmt_tbl(i).rule_operator);
1663 FND_MSG_PUB.ADD;
1664 l_flag := 'Y';
1665 END IF;
1666
1667 CLOSE chk_lookup_code_csr;
1668 END IF;
1669
1670 -- check for object_type
1671 IF (l_ui_rule_stmt_tbl(i).object_type IS NULL) THEN
1672 -- check for object_type_meaning
1673 IF (l_ui_rule_stmt_tbl(i).object_type_meaning IS NOT NULL) THEN
1674 -- convert the meaning into code
1675 AHL_UTIL_MC_PKG.Convert_To_LookupCode(
1676 p_lookup_type => 'AHL_MC_RULE_OBJECT_TYPES',
1677 p_lookup_meaning => l_ui_rule_stmt_tbl(i).object_type_meaning,
1678 x_lookup_code => l_rule_object_type,
1679 x_return_val => l_return_val
1680 );
1681
1682 IF (l_return_val) THEN
1683 -- set the object_type
1684 l_ui_rule_stmt_tbl(i).object_type := l_rule_object_type;
1685 ELSE
1686 -- input is invalid
1687 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_OBJ_TYPE_INV');
1688 FND_MESSAGE.Set_Token('OBJ_TYPE',l_ui_rule_stmt_tbl(i).object_type_meaning);
1689 FND_MSG_PUB.ADD;
1690 l_flag := 'Y';
1691 END IF;
1692 END IF;
1693 ELSE
1694 -- check with object_type
1695 OPEN chk_lookup_code_csr('AHL_MC_RULE_OBJECT_TYPES', l_ui_rule_stmt_tbl(i).object_type);
1696 FETCH chk_lookup_code_csr INTO l_dummy;
1697
1698 IF (chk_lookup_code_csr%NOTFOUND) THEN
1699 -- input is invalid
1700 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_OBJ_TYPE_INV');
1701 FND_MESSAGE.Set_Token('OBJ_TYPE',l_ui_rule_stmt_tbl(i).object_type);
1702 FND_MSG_PUB.ADD;
1703 l_flag := 'Y';
1704 END IF;
1705
1706 CLOSE chk_lookup_code_csr;
1707 END IF;
1708
1709 -- if object_type is 'ITEM'
1710 -- check for object_id
1711 IF (l_ui_rule_stmt_tbl(i).object_type = 'ITEM') THEN
1712 IF (l_ui_rule_stmt_tbl(i).object_id IS NULL) THEN
1713 -- check for object_meaning
1714 IF (l_ui_rule_stmt_tbl(i).object_meaning IS NULL) THEN
1715 -- input is NULL
1716 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_ITM_NULL');
1717 FND_MSG_PUB.ADD;
1718 l_flag := 'Y';
1719 ELSE
1720 OPEN chk_item_name_csr(l_ui_rule_stmt_tbl(i).object_meaning);
1721 FETCH chk_item_name_csr INTO l_item_id;
1722
1723 IF (chk_item_name_csr%NOTFOUND) THEN
1724 -- input is invalid
1725 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_ITM_NAME_INV');
1726 FND_MESSAGE.Set_Token('ITM_NAME',l_ui_rule_stmt_tbl(i).object_meaning);
1727 FND_MSG_PUB.ADD;
1728 l_flag := 'Y';
1729 END IF;
1730
1731 -- set the object_id
1732 l_ui_rule_stmt_tbl(i).object_id := l_item_id;
1733 CLOSE chk_item_name_csr;
1734 END IF;
1735 ELSE
1736 -- check with object_id
1737 OPEN chk_item_id_csr (l_ui_rule_stmt_tbl(i).object_id);
1738 FETCH chk_item_id_csr INTO l_dummy;
1739
1740 IF (chk_item_id_csr%NOTFOUND) THEN
1741 -- input is invalid
1742 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_ITM_ID_INV');
1743 FND_MESSAGE.Set_Token('ITM_ID',l_ui_rule_stmt_tbl(i).object_id);
1744 FND_MSG_PUB.ADD;
1745 l_flag := 'Y';
1746 END IF;
1747
1748 CLOSE chk_item_id_csr;
1749 END IF;
1750 END IF;
1751
1752 -- if object_type is 'CONFIGURATION'
1753 -- check for object_id
1754 IF (l_ui_rule_stmt_tbl(i).object_type = 'CONFIGURATION') THEN
1755 IF (l_ui_rule_stmt_tbl(i).object_id IS NULL) THEN
1756 -- check for object_meaning
1757 IF (l_ui_rule_stmt_tbl(i).object_meaning IS NULL) THEN
1758 -- input is NULL
1759 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_ID_NULL');
1760 FND_MSG_PUB.ADD;
1761 l_flag := 'Y';
1762 ELSE
1763 OPEN chk_mc_name_csr(l_ui_rule_stmt_tbl(i).object_meaning);
1764 FETCH chk_mc_name_csr INTO l_mc_id;
1765
1766 IF (chk_mc_name_csr%NOTFOUND) THEN
1767 -- input is invalid
1768 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_ID_NAME_INV');
1769 FND_MESSAGE.Set_Token('MC_NAME',l_ui_rule_stmt_tbl(i).object_meaning);
1770 FND_MSG_PUB.ADD;
1771 l_flag := 'Y';
1772 END IF;
1773
1774 -- set the object_id
1775 l_ui_rule_stmt_tbl(i).object_id := l_mc_id;
1776 CLOSE chk_mc_name_csr;
1777 END IF;
1778 ELSE
1779 -- check with object_id
1780 OPEN chk_mc_id_csr (l_ui_rule_stmt_tbl(i).object_id);
1781 FETCH chk_mc_id_csr INTO l_dummy;
1782
1783 IF (chk_mc_id_csr%NOTFOUND) THEN
1784 -- input is invalid
1785 FND_MESSAGE.Set_Name('AHL','AHL_MC_RULE_MC_ID_INV');
1786 FND_MESSAGE.Set_Token('MC_ID',l_ui_rule_stmt_tbl(i).object_id);
1787 FND_MSG_PUB.ADD;
1788 l_flag := 'Y';
1789 END IF;
1790
1791 CLOSE chk_mc_id_csr;
1792 END IF;
1793 END IF;
1794
1795 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
1796 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1797 ' i => '||i||
1798 ' l_ui_rule_stmt_tbl(i).rule_statement_id => '||l_ui_rule_stmt_tbl(i).rule_statement_id||
1799 ' l_ui_rule_stmt_tbl(i).rule_stmt_obj_ver_num => '||l_ui_rule_stmt_tbl(i).rule_stmt_obj_ver_num||
1800 ' l_ui_rule_stmt_tbl(i).operator => '||l_ui_rule_stmt_tbl(i).operator||
1801 ' l_ui_rule_stmt_tbl(i).operator_meaning => '||l_ui_rule_stmt_tbl(i).operator_meaning||
1802 ' l_ui_rule_stmt_tbl(i).rule_operator => '||l_ui_rule_stmt_tbl(i).rule_operator||
1803 ' l_ui_rule_stmt_tbl(i).rule_operator_meaning => '||l_ui_rule_stmt_tbl(i).rule_operator_meaning||
1804 ' l_ui_rule_stmt_tbl(i).object_type => '||l_ui_rule_stmt_tbl(i).object_type||
1805 ' l_ui_rule_stmt_tbl(i).object_type_meaning => '||l_ui_rule_stmt_tbl(i).object_type_meaning||
1806 ' l_ui_rule_stmt_tbl(i).object_id => '||l_ui_rule_stmt_tbl(i).object_id||
1807 ' l_ui_rule_stmt_tbl(i).object_meaning => '||l_ui_rule_stmt_tbl(i).object_meaning);
1808 END IF;
1809
1810 -- raise the exception if some error occurred
1811 IF (l_flag = 'Y') THEN
1812 RAISE FND_API.G_EXC_ERROR;
1813 END IF;
1814 END LOOP;
1815
1816 -- return changed record
1817 p_x_ui_rule_stmt_tbl := l_ui_rule_stmt_tbl;
1818
1819 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1820 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
1821 END IF;
1822
1823 END Convert_Rule_Stmt_Values_to_Id;
1824
1825 End AHL_MC_RULE_PUB;