1 PACKAGE BODY AHL_MC_MasterConfig_PUB AS
2 /* $Header: AHLPMCXB.pls 120.2.12010000.2 2008/11/06 09:58:38 sathapli ship $ */
3
4 ---------------------
5 -- Spec Procedures --
6 ---------------------
7 PROCEDURE Process_Master_Config
8 (
9 p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE,
12 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
13 p_module_type IN VARCHAR2 := 'JSP',
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17 p_x_mc_header_rec IN OUT NOCOPY AHL_MC_MasterConfig_PVT.Header_Rec_Type,
18 p_x_node_rec IN OUT NOCOPY AHL_MC_Node_PVT.Node_Rec_Type
19 )
20 IS
21 -- Define validation cursors
22 CURSOR get_item_group_id
23 (
24 p_ig_name in VARCHAR2
25 )
26 IS
27 SELECT item_group_id
28 FROM ahl_item_groups_b
29 WHERE upper(name) = upper (p_ig_name) AND
30 source_item_group_id IS NULL;
31
32 -- Declare local variables
33 l_api_name CONSTANT VARCHAR2(30) := 'Process_Master_Config';
34 l_api_version CONSTANT NUMBER := 1.0;
35 l_return_status VARCHAR2(1);
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(2000);
38
39 l_ret_val BOOLEAN;
40 -- Fix for Bug #3523435
41 l_lookup_code VARCHAR2(30);
42 l_resolved_id NUMBER;
43 -- Fix for Bug #3523435
44
45 BEGIN
46
47 -- Standard start of API savepoint
48 SAVEPOINT Process_Master_Config_SP;
49
50 -- Standard call to check for call compatibility
51 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
52 THEN
53 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
54 END IF;
55
56 -- Initialize message list if p_init_msg_list is set to TRUE
57 IF FND_API.TO_BOOLEAN(p_init_msg_list)
58 THEN
59 FND_MSG_PUB.Initialize;
60 END IF;
61
62 -- Initialize API return status to success
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64
65 -- API body starts here
66 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
67 THEN
68 fnd_log.string
69 (
70 fnd_log.level_procedure,
71 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.begin',
72 'At the start of PLSQL procedure'
73 );
74 END IF;
75
76 -- Fix for Bug #3523435
77 -- IF (p_module_type = 'JSP' AND p_x_mc_header_rec.operation_flag <> G_DML_DELETE)
78 IF (p_x_mc_header_rec.operation_flag <> G_DML_DELETE)
79 THEN
80 -- Validate header status code
81 -- This field is represented in UI with a dropdown, hence need to check for validity of the code only
82 IF (p_x_mc_header_rec.config_status_code IS NULL)
83 THEN
84 -- This is a mandatory field, hence throw error
85 FND_MESSAGE.Set_Name('AHL','AHL_MC_STATUS_NULL');
86 FND_MSG_PUB.ADD;
87 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
88 THEN
89 fnd_log.message
90 (
91 fnd_log.level_exception,
92 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
93 false
94 );
95 END IF;
96 ELSIF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_CONFIG_STATUS', p_x_mc_header_rec.config_status_code))
97 THEN
98 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_STATUS_INVALID');
99 FND_MESSAGE.Set_Token('STATUS', p_x_mc_header_rec.config_status_code);
100 FND_MSG_PUB.ADD;
101 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
102 THEN
103 fnd_log.message
104 (
105 fnd_log.level_exception,
106 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
107 false
108 );
109 END IF;
110 END IF;
111
112 -- Validate and Convert node position reference meaning to code
113 p_x_node_rec.position_ref_meaning := RTRIM(p_x_node_rec.position_ref_meaning);
114
115 -- This field is represented in UI with an LOV, hence need to check for validity of the meaning only
116 IF (p_x_node_rec.position_ref_meaning IS NULL)
117 THEN
118 -- This is a mandatory field, hence throw error
119 FND_MESSAGE.Set_Name('AHL','AHL_MC_POSREF_NULL');
120 FND_MSG_PUB.ADD;
121 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
122 THEN
123 fnd_log.message
124 (
125 fnd_log.level_exception,
126 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
127 false
128 );
129 END IF;
130 ELSE
131 AHL_UTIL_MC_PKG.Convert_To_LookupCode
132 (
133 'AHL_POSITION_REFERENCE',
134 p_x_node_rec.position_ref_meaning,
135 l_lookup_code,
136 l_ret_val
137 );
138
139 -- Fix for Bug #3523435
140 -- If p_module_type <> 'JSP' then lookup code and meaning should match, else resolve on meaning
141 -- IF NOT (l_ret_val)
142 IF (NOT l_ret_val OR (NOT (p_module_type IS NOT NULL AND p_module_type = 'JSP') AND l_lookup_code <> p_x_node_rec.position_ref_code))
143 THEN
144 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_POSREF_INVALID');
145 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
146 FND_MSG_PUB.ADD;
147 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
148 THEN
149 fnd_log.message
150 (
151 fnd_log.level_exception,
152 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
153 false
154 );
155 END IF;
156 -- Fix for Bug #3523435
157 ELSE
158 p_x_node_rec.position_ref_code := l_lookup_code;
159 -- Fix for Bug #3523435
160 END IF;
161 END IF;
162
163 -----R12
164 ---- priyan MEL-CDL
165 ---- Validate and Convert node ata meaning to code
166 p_x_node_rec.ata_meaning := RTRIM(p_x_node_rec.ata_meaning);
167
168 ---- This field is represented in UI with an LOV, hence need to check for validity of the meaning only
169 IF (p_x_node_rec.ata_meaning IS NOT NULL AND p_x_node_rec.ata_meaning <> FND_API.G_MISS_CHAR)
170 THEN
171 AHL_UTIL_MC_PKG.Convert_To_LookupCode
172 (
173 'AHL_ATA_CODE',
174 p_x_node_rec.ata_meaning,
175 p_x_node_rec.ata_code,
176 l_ret_val
177 );
178
179 IF NOT (l_ret_val)
180 THEN
181 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ATASEQ_INVALID');
182 FND_MESSAGE.Set_Token('ATAMEANING', p_x_node_rec.ata_meaning);
183 FND_MSG_PUB.ADD;
184 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
185 THEN
186 fnd_log.message
187 (
188 fnd_log.level_exception,
189 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
190 false
191 );
192 END IF;
193 END IF;
194 ELSE
195 -- Not a mandatory field, hence ensure no ID is passed to PVT if there is no name
196 p_x_node_rec.ata_code := null;
197 END IF;
198
199
200
201 -- Validate node position necessity code
202 -- This field is represented in UI with a dropdown, hence need to check for validity of the code only
203 IF (p_x_node_rec.position_necessity_code IS NULL)
204 THEN
205 -- This is a mandatory field, hence throw error
206 FND_MESSAGE.Set_Name('AHL','AHL_MC_NECESSITY_NULL');
207 FND_MSG_PUB.ADD;
208 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
209 THEN
210 fnd_log.message
211 (
212 fnd_log.level_exception,
213 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
214 false
215 );
216 END IF;
217 ELSIF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_POSITION_NECESSITY', p_x_node_rec.position_necessity_code))
218 THEN
219 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_NECESSITY_INVALID');
220 FND_MESSAGE.Set_Token('POSREF', p_x_node_rec.position_ref_meaning);
221 FND_MESSAGE.Set_Token('CODE', p_x_node_rec.position_necessity_code);
222 FND_MSG_PUB.ADD;
223 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
224 THEN
225 fnd_log.message
226 (
227 fnd_log.level_exception,
228 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
229 false
230 );
231 END IF;
232 END IF;
233
234 -- Validate and Convert node item group name to id
235 p_x_node_rec.item_group_name := RTRIM(p_x_node_rec.item_group_name);
236
237 -- This field is represented in UI with an LOV, hence need to check for validity of the name only
238 -- This is not a mandatory field
239 IF (p_x_node_rec.item_group_name IS NOT NULL)
240 THEN
241 OPEN get_item_group_id (p_x_node_rec.item_group_name);
242 FETCH get_item_group_id INTO l_resolved_id;
243 -- Fix for Bug #3523435
244 -- If p_module_type <> 'JSP' then id and name should match, else resolve on name
245 -- IF (get_item_group_id%NOTFOUND)
246 IF (get_item_group_id%NOTFOUND OR (NOT (p_module_type IS NOT NULL AND p_module_type = 'JSP') AND l_resolved_id <> p_x_node_rec.item_group_id))
247 THEN
248 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_ITEMGRP_INVALID');
249 FND_MESSAGE.Set_Token('ITEM_GRP', p_x_node_rec.item_group_name);
250 FND_MSG_PUB.ADD;
251 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
252 THEN
253 fnd_log.message
254 (
255 fnd_log.level_exception,
256 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
257 false
258 );
259 END IF;
260 ELSE
261 p_x_node_rec.item_group_id := l_resolved_id;
262 END IF;
263 CLOSE get_item_group_id;
264 ELSE
265 -- Not a mandatory field, hence ensure no ID is passed to PVT if there is no name
266 p_x_node_rec.item_group_id := null;
267 END IF;
268
269
270
271 -- SATHAPLI::Enigma code changes, 26-Aug-2008
272 -- This field is represented in UI with an LOV, hence need to check for validity of the meaning only
273 IF (p_x_mc_header_rec.model_meaning IS NOT NULL AND p_x_mc_header_rec.model_meaning <> FND_API.G_MISS_CHAR)
274 THEN
275 AHL_UTIL_MC_PKG.Convert_To_LookupCode
276 (
277 'AHL_ENIGMA_MODEL_CODE',
278 p_x_mc_header_rec.model_meaning,
279 p_x_mc_header_rec.model_code,
280 l_ret_val
281 );
282
283 IF NOT (l_ret_val)
284 THEN
285 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_MODEL_INVALID');
286 FND_MESSAGE.Set_Token('MODEL_MEANING', p_x_mc_header_rec.model_meaning);
287 FND_MSG_PUB.ADD;
288 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
289 THEN
290 fnd_log.message
291 (
292 fnd_log.level_exception,
293 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
294 false
295 );
296 END IF;
297 END IF;
298 ELSE
299 -- Not a mandatory field, hence ensure no ID is passed to PVT if there is no name
300 p_x_mc_header_rec.model_code := null;
301 END IF;
302
303 -- Check Error Message stack.
304 x_msg_count := FND_MSG_PUB.count_msg;
305 IF x_msg_count > 0 THEN
306 RAISE FND_API.G_EXC_ERROR;
307 END IF;
308 END IF;
309
310 -- Call PVT APIs accordingly
311 IF (p_x_mc_header_rec.operation_flag = G_DML_CREATE)
312 THEN
313 -- Fix for Bug #3523435
314 -- Invalid version number...
315 IF (NVL(p_x_mc_header_rec.version_number, 1) <> 1)
316 THEN
317 FND_MESSAGE.Set_Name('AHL', 'AHL_MC_INV_VERNUM');
318 FND_MESSAGE.Set_Token('MC_NAME', p_x_mc_header_rec.name);
319 FND_MSG_PUB.ADD;
320 END IF;
321 -- Fix for Bug #3523435
322
323 AHL_MC_MasterConfig_PVT.Create_Master_Config
324 (
325 p_api_version => 1.0,
326 p_init_msg_list => FND_API.G_FALSE,
327 p_commit => FND_API.G_FALSE,
328 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
329 x_return_status => l_return_status,
330 x_msg_count => l_msg_count,
331 x_msg_data => l_msg_data,
332 p_x_mc_header_rec => p_x_mc_header_rec,
333 p_x_node_rec => p_x_node_rec
334 );
335 ELSIF (p_x_mc_header_rec.operation_flag = G_DML_DELETE)
336 THEN
337 AHL_MC_MasterConfig_PVT.Delete_Master_Config
338 (
339 p_api_version => 1.0,
340 p_init_msg_list => FND_API.G_FALSE,
341 p_commit => FND_API.G_FALSE,
342 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
343 x_return_status => l_return_status,
344 x_msg_count => l_msg_count,
345 x_msg_data => l_msg_data,
346 p_mc_header_id => p_x_mc_header_rec.mc_header_id,
347 p_object_ver_num => p_x_mc_header_rec.object_version_number
348 );
349 -- Fix for Bug #3523435
350 -- Invalid operation flag...
351 -- ELSE
352 ELSIF (p_x_mc_header_rec.operation_flag = G_DML_UPDATE)
353 THEN
354
355 AHL_MC_MasterConfig_PVT.Modify_Master_Config
356 (
357 p_api_version => 1.0,
358 p_init_msg_list => FND_API.G_FALSE,
359 p_commit => FND_API.G_FALSE,
360 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
361 x_return_status => l_return_status,
362 x_msg_count => l_msg_count,
363 x_msg_data => l_msg_data,
364 p_x_mc_header_rec => p_x_mc_header_rec,
365 p_x_node_rec => p_x_node_rec
366 );
367
368 -- Fix for Bug #3523435
369 ELSE
370 FND_MESSAGE.Set_Name('AHL', 'AHL_COM_INVALID_DML');
371 FND_MESSAGE.Set_Token('FIELD', p_x_mc_header_rec.operation_flag);
372 IF (p_x_mc_header_rec.name IS NOT NULL)
373 THEN
374 FND_MESSAGE.Set_Token('RECORD', p_x_mc_header_rec.name);
375 ELSE
376 FND_MESSAGE.Set_Token('RECORD', p_x_mc_header_rec.mc_header_id);
377 END IF;
378 FND_MSG_PUB.ADD;
379 RAISE FND_API.G_EXC_ERROR;
380 -- Fix for Bug #3523435
381 END IF;
382
383 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
384 THEN
385 fnd_log.string
386 (
387 fnd_log.level_procedure,
388 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||'.end',
389 'At the end of PLSQL procedure'
390 );
391 END IF;
392 -- API body ends heres
393
394 -- Check Error Message stack.
395 x_msg_count := FND_MSG_PUB.count_msg;
396
397 --priyan
398 --R12 MEL/CDL
399 IF ( x_msg_count > 0 AND l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
400 RAISE FND_API.G_EXC_ERROR;
401 END IF;
402
403 -- Standard check for p_commit
404 IF FND_API.TO_BOOLEAN (p_commit)
405 THEN
406 COMMIT WORK;
407 END IF;
408
409 -- Standard call to get message count and if count is 1, get message info
410 FND_MSG_PUB.count_and_get
411 (
412 p_count => x_msg_count,
413 p_data => x_msg_data,
414 p_encoded => FND_API.G_FALSE
415 );
416
417 EXCEPTION
418 WHEN FND_API.G_EXC_ERROR THEN
419 x_return_status := FND_API.G_RET_STS_ERROR;
420 Rollback to Process_Master_Config_SP;
421 FND_MSG_PUB.count_and_get
422 (
423 p_count => x_msg_count,
424 p_data => x_msg_data,
425 p_encoded => FND_API.G_FALSE
426 );
427
428 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
430 Rollback to Process_Master_Config_SP;
431 FND_MSG_PUB.count_and_get
432 (
433 p_count => x_msg_count,
434 p_data => x_msg_data,
435 p_encoded => FND_API.G_FALSE
436 );
437
438 WHEN OTHERS THEN
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
440 Rollback to Process_Master_Config_SP;
441 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
442 THEN
443 FND_MSG_PUB.add_exc_msg
444 (
445 p_pkg_name => G_PKG_NAME,
446 p_procedure_name => 'Process_Master_Config',
447 p_error_text => SUBSTR(SQLERRM,1,240)
448 );
449 END IF;
450 FND_MSG_PUB.count_and_get
451 (
452 p_count => x_msg_count,
453 p_data => x_msg_data,
454 p_encoded => FND_API.G_FALSE
455 );
456
457 END Process_Master_Config;
458
459 End AHL_MC_MasterConfig_PUB;