1 PACKAGE BODY AHL_RA_SETUPS_PVT AS
2 /* $Header: AHLVRASB.pls 120.13 2006/02/16 01:59 sagarwal noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_RA_SETUPS_PVT';
5 G_DML_CREATE CONSTANT VARCHAR2(1) := 'C';
6 G_DML_UPDATE CONSTANT VARCHAR2(1) := 'U';
7 G_DML_DELETE CONSTANT VARCHAR2(1) := 'D';
8
9 -- Start of Comments --
10 --
11 -- Procedure name : CREATE_SETUP_DATA
12 -- Type : Private
13 -- Function : This API would create the setup data for Reliability Framework in AHL_RA_SETUPS table
14 -- Pre-reqs :
15 --
16 -- Standard IN Parameters :
17 -- p_api_version IN NUMBER Required
18 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
19 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
20 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
21 --
22 -- Standard OUT Parameters :
23 -- x_return_status OUT VARCHAR2 Required
24 -- x_msg_count OUT NUMBER Required
25 -- x_msg_data OUT VARCHAR2 Required
26 --
27 -- CREATE_SETUP_DATA Parameters :
28 -- p_x_setup_data_rec IN OUT RA_SETUP_DATA_REC_TYPE Required
29 --
30 -- Version :
31 -- Initial Version 1.0
32 --
33 -- End of Comments --
34 PROCEDURE CREATE_SETUP_DATA (
35 p_api_version IN NUMBER,
36 p_init_msg_list IN VARCHAR2,
37 p_commit IN VARCHAR2,
38 p_validation_level IN NUMBER,
39 p_module_type IN VARCHAR2,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2,
43 p_x_setup_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_SETUP_DATA_REC_TYPE) IS
44
45 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SETUP_DATA';
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||g_pkg_name || '.' || l_api_name;
48
49 l_setup_data_rec AHL_RA_SETUPS_PVT.RA_SETUP_DATA_REC_TYPE DEFAULT p_x_setup_data_rec;
50 l_dummy VARCHAR2(1);
51 l_code VARCHAR2(30); -- dummy variable used for token storage.
52
53 BEGIN
54
55 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
56 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
57 END IF;
58
59 -- Standard start of API savepoint
60 SAVEPOINT CREATE_SETUP_DATA_SP;
61
62 -- Standard call to check for call compatibility
63 IF NOT FND_API.Compatible_API_Call(l_api_version,p_api_version, l_api_name, G_PKG_NAME) THEN
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66
67 -- Initialize message list if p_init_msg_list is set to TRUE
68 IF FND_API.To_Boolean(p_init_msg_list) THEN
69 FND_MSG_PUB.Initialize;
70 END IF;
71
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
75 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_SETUP_DATA -------BEGIN-----------');
76 END IF;
77
78 -- Validate input setup data in p_x_setup_data_rec
79 -- Note :: l_setup_data_rec has been defaulted to p_x_setup_data_rec in declaration
80 -- A. SETUP_CODE Cannot be NULL
81 -- B. SETUP_CODE should be either ITEM_STATUS or REMOVAL_CODE
82 -- B. If SETUP_CODE = ITEM_STATUS then STATUS_ID must be passed
83 -- C. If SETUP_CODE = REMOVAL_CODE then REMOVAL_CODE must be passed
84 -- D. OPERATIONS_FLAG should be C
85 IF ((l_setup_data_rec.SETUP_CODE IS NULL) OR
86 (l_setup_data_rec.SETUP_CODE NOT IN ('ITEM_STATUS','REMOVAL_CODE')) OR
87 (l_setup_data_rec.SETUP_CODE = 'ITEM_STATUS' AND l_setup_data_rec.STATUS_ID IS NULL) OR
88 (l_setup_data_rec.SETUP_CODE = 'REMOVAL_CODE' AND l_setup_data_rec.REMOVAL_CODE IS NULL) OR
89 ((l_setup_data_rec.OPERATION_FLAG IS NULL) OR (l_setup_data_rec.OPERATION_FLAG <> G_DML_CREATE))) THEN
90
91 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
92 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
93 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_setup_data_rec.SETUP_CODE -- '||l_setup_data_rec.SETUP_CODE);
94 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_setup_data_rec.STATUS_ID -- '||l_setup_data_rec.STATUS_ID);
95 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_setup_data_rec.REMOVAL_CODE -- '||l_setup_data_rec.REMOVAL_CODE);
96 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_setup_data_rec.OPERATION_FLAG -- '||l_setup_data_rec.OPERATION_FLAG);
97 END IF;
98
99 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
100 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_SETUP_DATA');
101 FND_MSG_PUB.ADD;
102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
103
104 END IF;
105
106 -- Check for duplicate Setup Data in AHL_RA_SETUPS
107 BEGIN
108 SELECT 'X'
109 INTO l_dummy
110 FROM DUAL
111 WHERE EXISTS (SELECT 1
112 FROM AHL_RA_SETUPS
113 WHERE SETUP_CODE = l_setup_data_rec.SETUP_CODE
114 AND nvl(STATUS_ID,'-1') = nvl(DECODE(l_setup_data_rec.SETUP_CODE,'ITEM_STATUS',l_setup_data_rec.STATUS_ID,STATUS_ID),'-1')
115 AND nvl(REMOVAL_CODE,'-1') = nvl(DECODE(l_setup_data_rec.SETUP_CODE,'REMOVAL_CODE',l_setup_data_rec.REMOVAL_CODE,REMOVAL_CODE),'-1'));
116
117 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
118 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Duplicate Data exists -- ERROR ... ');
119 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Fetch Code for Message Token ... ');
120 END IF;
121
122 IF l_setup_data_rec.SETUP_CODE = 'ITEM_STATUS' THEN
123 BEGIN
124 SELECT STATUS_CODE
125 INTO l_code
126 FROM MTL_MATERIAL_STATUSES_VL
127 WHERE STATUS_ID = l_setup_data_rec.STATUS_ID;
128
129 FND_MESSAGE.Set_Name('AHL','AHL_RA_DUP_STATUS_CODE');
130 FND_MESSAGE.Set_Token('ITEM_STATUS',l_code);
131 FND_MSG_PUB.ADD;
132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
136 fnd_log.string(fnd_log.level_statement,l_full_name,'-- MTL STATUS Data Corruption -- ERROR ... ');
137 END IF;
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END;
140
141 ELSIF l_setup_data_rec.SETUP_CODE = 'REMOVAL_CODE' THEN
142 BEGIN
143 SELECT MEANING
144 INTO l_code
145 FROM FND_LOOKUPS
146 WHERE LOOKUP_TYPE = 'AHL_REMOVAL_CODE'
147 AND LOOKUP_CODE = l_setup_data_rec.REMOVAL_CODE;
148
149 FND_MESSAGE.Set_Name('AHL','AHL_RA_DUP_REMOVAL_CODE');
150 FND_MESSAGE.Set_Token('REMOVAL_CODE',l_code);
151 FND_MSG_PUB.ADD;
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
156 fnd_log.string(fnd_log.level_statement,l_full_name,'-- AHL LOOKUP Data Corruption -- ERROR ... ');
157 END IF;
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END;
160 END IF;
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
165 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Duplicate Data does not exists -- CONTINUE ... ');
166 END IF;
167 NULL;
168 END;
169
170 IF l_setup_data_rec.SETUP_CODE = 'ITEM_STATUS' THEN
171 -- Initialise removal_code to NULL .. to avoid any data corruption
172 l_setup_data_rec.REMOVAL_CODE := NULL;
173 -- Validate l_setup_data_rec.STATUS_ID passed to be a valid value
174 BEGIN
175 SELECT 'X'
176 INTO l_dummy
177 FROM DUAL
178 WHERE EXISTS (SELECT 1
179 FROM MTL_MATERIAL_STATUSES_VL
180 WHERE STATUS_ID = l_setup_data_rec.STATUS_ID);
181
182 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
183 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Valid Status Id Passed. -- CONTINUE ... ' || l_setup_data_rec.STATUS_ID);
184 END IF;
185
186 EXCEPTION
187 WHEN NO_DATA_FOUND THEN
188 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
189 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Status Id Passed -- ERROR ... ' || l_setup_data_rec.STATUS_ID);
190 END IF;
191 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
192 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_SETUP_DATA');
193 FND_MSG_PUB.ADD;
194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
195 END;
196
197 ELSIF l_setup_data_rec.SETUP_CODE = 'REMOVAL_CODE' THEN
198
199 -- Initialise status_id to NULL .. to avoid any data corruption
200 l_setup_data_rec.STATUS_ID := NULL;
201
202 -- Validate l_setup_data_rec.REMOVAL_CODE passed to be a valid value
203 BEGIN
204 SELECT 'X'
205 INTO l_dummy
206 FROM DUAL
207 WHERE EXISTS (SELECT 1
208 FROM FND_LOOKUPS
209 WHERE LOOKUP_TYPE = 'AHL_REMOVAL_CODE'
210 AND LOOKUP_CODE = l_setup_data_rec.REMOVAL_CODE);
211
212 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
213 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Valid Removal Code Passed. -- CONTINUE ... ' || l_setup_data_rec.REMOVAL_CODE);
214 END IF;
215
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
219 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Removal Code Passed -- ERROR ... ' || l_setup_data_rec.REMOVAL_CODE);
220 END IF;
221 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
222 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_SETUP_DATA');
223 FND_MSG_PUB.ADD;
224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225 END;
226
227 END IF;
228
229 -- Initialize RA_SETUP_ID to sequence next val for insert
230 SELECT AHL_RA_SETUPS_S.NEXTVAL INTO l_setup_data_rec.RA_SETUP_ID FROM DUAL;
231
232 -- Initialize object version number to 1
233 l_setup_data_rec.OBJECT_VERSION_NUMBER := 1;
234
235 -- Intialize who column info
236 l_setup_data_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
237 l_setup_data_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
238 l_setup_data_rec.CREATED_BY := fnd_global.user_id;
239 l_setup_data_rec.CREATION_DATE := sysdate;
240 l_setup_data_rec.LAST_UPDATE_DATE := sysdate;
241
242 -- Initialize security group id
243 l_setup_data_rec.SECURITY_GROUP_ID := null;
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,'-- Derived ra_setup_id -- ' || l_setup_data_rec.RA_SETUP_ID);
247 END IF;
248
249 -- INSERT Setup data in AHL_RA_SETUPS
250 INSERT INTO AHL_RA_SETUPS(RA_SETUP_ID,SETUP_CODE,STATUS_ID,REMOVAL_CODE,OBJECT_VERSION_NUMBER,SECURITY_GROUP_ID,
251 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,ATTRIBUTE_CATEGORY,
252 ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,
253 ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15)
254 VALUES(
255 l_setup_data_rec.RA_SETUP_ID -- RA_SETUP_ID
256 ,l_setup_data_rec.SETUP_CODE -- SETUP_CODE
257 ,l_setup_data_rec.STATUS_ID -- STATUS_ID
258 ,l_setup_data_rec.REMOVAL_CODE -- REMOVAL_CODE
259 ,l_setup_data_rec.OBJECT_VERSION_NUMBER-- OBJECT_VERSION_NUMBER
260 ,l_setup_data_rec.SECURITY_GROUP_ID -- SECURITY_GROUP_ID
261 ,l_setup_data_rec.CREATION_DATE -- CREATION_DATE
262 ,l_setup_data_rec.CREATED_BY -- CREATED_BY
263 ,l_setup_data_rec.LAST_UPDATE_DATE -- LAST_UPDATE_DATE
264 ,l_setup_data_rec.LAST_UPDATED_BY -- LAST_UPDATED_BY
265 ,l_setup_data_rec.LAST_UPDATE_LOGIN -- LAST_UPDATE_LOGIN
266 ,l_setup_data_rec.ATTRIBUTE_CATEGORY -- ATTRIBUTE_CATEGORY
267 ,l_setup_data_rec.ATTRIBUTE1 -- ATTRIBUTE1
268 ,l_setup_data_rec.ATTRIBUTE2 -- ATTRIBUTE2
269 ,l_setup_data_rec.ATTRIBUTE3 -- ATTRIBUTE3
270 ,l_setup_data_rec.ATTRIBUTE4 -- ATTRIBUTE4
271 ,l_setup_data_rec.ATTRIBUTE5 -- ATTRIBUTE5
272 ,l_setup_data_rec.ATTRIBUTE6 -- ATTRIBUTE6
273 ,l_setup_data_rec.ATTRIBUTE7 -- ATTRIBUTE7
274 ,l_setup_data_rec.ATTRIBUTE8 -- ATTRIBUTE8
275 ,l_setup_data_rec.ATTRIBUTE9 -- ATTRIBUTE9
276 ,l_setup_data_rec.ATTRIBUTE10 -- ATTRIBUTE10
277 ,l_setup_data_rec.ATTRIBUTE11 -- ATTRIBUTE11
278 ,l_setup_data_rec.ATTRIBUTE12 -- ATTRIBUTE12
279 ,l_setup_data_rec.ATTRIBUTE13 -- ATTRIBUTE13
280 ,l_setup_data_rec.ATTRIBUTE14 -- ATTRIBUTE14
281 ,l_setup_data_rec.ATTRIBUTE15 -- ATTRIBUTE15
282 );
283
284 -- Set the Out Param
285 p_x_setup_data_rec := l_setup_data_rec;
286
287 -- Standard check for p_commit
288 IF FND_API.To_Boolean (p_commit) THEN
289 COMMIT;
290 END IF;
291
292 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
293 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_SETUP_DATA -------END-----------');
294 END IF;
295
296 -- Standard call to get message count and if count is 1, get message
297 FND_MSG_PUB.Count_And_Get
298 ( p_count => x_msg_count,
299 p_data => x_msg_data,
300 p_encoded => fnd_api.g_false);
301
302 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
303 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
304 END IF;
305
306 EXCEPTION
307 WHEN FND_API.G_EXC_ERROR THEN
308 x_return_status := FND_API.G_RET_STS_ERROR;
309 ROLLBACK TO CREATE_SETUP_DATA_SP;
310 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
311 p_data => x_msg_data,
312 p_encoded => fnd_api.g_false);
313
314 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
316 ROLLBACK TO CREATE_SETUP_DATA_SP;
317 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
318 p_data => x_msg_data,
319 p_encoded => fnd_api.g_false);
320
321 WHEN OTHERS THEN
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
323 ROLLBACK TO CREATE_SETUP_DATA_SP;
324 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
325 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
326 p_procedure_name => 'CREATE_SETUP_DATA',
327 p_error_text => SUBSTR(SQLERRM,1,240));
328 END IF;
329 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
330 p_data => x_msg_data,
331 p_encoded => fnd_api.g_false);
332
333 END CREATE_SETUP_DATA;
334
335 -- Start of Comments --
336 --
337 -- Procedure name : DELETE_SETUP_DATA
338 -- Type : Private
339 -- Function : This API would dalete the setup data for Reliability Framework in AHL_RA_SETUPS table
340 -- Pre-reqs :
341 --
342 -- Standard IN Parameters :
343 -- p_api_version IN NUMBER Required
344 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
345 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
346 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
347 --
348 -- Standard OUT Parameters :
349 -- x_return_status OUT VARCHAR2 Required
350 -- x_msg_count OUT NUMBER Required
351 -- x_msg_data OUT VARCHAR2 Required
352 --
353 -- DELETE_SETUP_DATA Parameters :
354 -- p_setup_data_rec IN RA_SETUP_DATA_REC_TYPE Required
355 --
356 -- Version :
357 -- Initial Version 1.0
358 --
359 -- End of Comments --
360 PROCEDURE DELETE_SETUP_DATA (
361 p_api_version IN NUMBER,
362 p_init_msg_list IN VARCHAR2,
363 p_commit IN VARCHAR2,
364 p_validation_level IN NUMBER,
365 p_module_type IN VARCHAR2,
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_msg_count OUT NOCOPY NUMBER,
368 x_msg_data OUT NOCOPY VARCHAR2,
369 p_setup_data_rec IN AHL_RA_SETUPS_PVT.RA_SETUP_DATA_REC_TYPE) IS
370
371 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SETUP_DATA';
372 l_api_version CONSTANT NUMBER := 1.0;
373 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
374
375 l_dummy VARCHAR2(1);
376 l_obj_version_num AHL_RA_SETUPS.OBJECT_VERSION_NUMBER%TYPE;
377
378 BEGIN
379
380 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
381 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
382 END IF;
383
384 -- Standard start of API savepoint
385 SAVEPOINT DELETE_SETUP_DATA_SP;
386
387 -- Standard call to check for call compatibility
388 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
390 END IF;
391
392 -- Initialize message list if p_init_msg_list is set to TRUE
393 IF FND_API.To_Boolean(p_init_msg_list) THEN
394 FND_MSG_PUB.Initialize;
395 END IF;
396
397 x_return_status := FND_API.G_RET_STS_SUCCESS;
398
399 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
400 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_SETUP_DATA -------BEGIN-----------');
401 END IF;
402
403
404 -- Validate input setup data in p_setup_data_rec
405 -- A. p_setup_data_rec.RA_SETUP_ID Cannot be NULL
406 -- B. OPERATIONS_FLAG should be D
407 -- C. Object Version Number should not be NULL
408 IF ((p_setup_data_rec.RA_SETUP_ID IS NULL) OR
409 ((p_setup_data_rec.OPERATION_FLAG IS NULL) OR (p_setup_data_rec.OPERATION_FLAG <> G_DML_DELETE)) OR
410 (p_setup_data_rec.OBJECT_VERSION_NUMBER IS NULL))THEN
411 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
412 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- RA SETUP ID :' || p_setup_data_rec.RA_SETUP_ID);
413 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- OP FLAG :' || p_setup_data_rec.OPERATION_FLAG);
414 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- OVN :' || p_setup_data_rec.OBJECT_VERSION_NUMBER);
415 END IF;
416 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
417 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.D_SETUP_DATA');
418 FND_MSG_PUB.ADD;
419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
420 END IF;
421
422 BEGIN
423 SELECT OBJECT_VERSION_NUMBER
424 INTO l_obj_version_num
425 FROM AHL_RA_SETUPS
426 WHERE RA_SETUP_ID = p_setup_data_rec.RA_SETUP_ID
427 FOR UPDATE OF object_version_number NOWAIT;
428
429 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
430 fnd_log.string(fnd_log.level_statement,l_full_name,'-- OBJECT VERSION NUMBER IN DB : ' || l_obj_version_num);
431 END IF;
432
433 EXCEPTION
434 WHEN NO_DATA_FOUND THEN
435 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
436 fnd_log.string(fnd_log.level_statement,l_full_name,'-- DATA DOES NOT EXISTS -- ERROR ... ' || p_setup_data_rec.RA_SETUP_ID);
437 END IF;
438 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
439 FND_MSG_PUB.ADD;
440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
441 END;
442
443 -- Validate input setup data in p_setup_data_rec
444 -- A. RECORD MUST NOT HAVE CHANGED. i.e. object_version_number should not change.
445 -- Note that currently(30/05/2005) UPDATE feature is NOT AVAILABLE to the user from the Self Service pages for RA setup Data.
446 -- However this might be taken up later.
447 IF p_setup_data_rec.OBJECT_VERSION_NUMBER <> l_obj_version_num THEN
448 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
449 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN passed : ' || p_setup_data_rec.OBJECT_VERSION_NUMBER);
450 END IF;
451 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
452 FND_MSG_PUB.ADD;
453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
454 END IF;
455
456 -- Delete Record from AHL_RA_SETUPS
457 DELETE AHL_RA_SETUPS
458 WHERE RA_SETUP_ID = p_setup_data_rec.RA_SETUP_ID;
459
460 -- Standard check for p_commit
461 IF FND_API.To_Boolean (p_commit) THEN
462 COMMIT;
463 END IF;
464
465 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
466 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_SETUP_DATA -------END-----------');
467 END IF;
468
469 -- Standard call to get message count and if count is 1, get message
470 FND_MSG_PUB.Count_And_Get
471 ( p_count => x_msg_count,
472 p_data => x_msg_data,
473 p_encoded => fnd_api.g_false);
474
475 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
476 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
477 END IF;
478
479 EXCEPTION
480 WHEN FND_API.G_EXC_ERROR THEN
481 x_return_status := FND_API.G_RET_STS_ERROR;
482 ROLLBACK TO DELETE_SETUP_DATA_SP;
483 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
484 p_data => x_msg_data,
485 p_encoded => fnd_api.g_false);
486
487 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
489 ROLLBACK TO DELETE_SETUP_DATA_SP;
490 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
491 p_data => x_msg_data,
492 p_encoded => fnd_api.g_false);
493
494 WHEN OTHERS THEN
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 ROLLBACK TO DELETE_SETUP_DATA_SP;
497 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
498 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
499 p_procedure_name => 'DELETE_SETUP_DATA',
500 p_error_text => SUBSTR(SQLERRM,1,240));
501 END IF;
502 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
503 p_data => x_msg_data,
504 p_encoded => fnd_api.g_false);
505
506 END DELETE_SETUP_DATA;
507
508 -- Start of Comments --
509 --
510 -- Procedure name : CREATE_RELIABILITY_DATA
511 -- Type : Private
512 -- Function : This API would create the setup data for Reliability Framework in AHL_RA_DEFINITION_HDR
513 -- Pre-reqs :
514 --
515 -- Standard IN Parameters :
516 -- p_api_version IN NUMBER Required
517 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
518 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
519 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
520 --
521 -- Standard OUT Parameters :
522 -- x_return_status OUT VARCHAR2 Required
523 -- x_msg_count OUT NUMBER Required
524 -- x_msg_data OUT VARCHAR2 Required
525 --
526 -- CREATE_RELIABILITY_DATA Parameters :
527 -- p_x_reliability_data_rec IN OUT RA_DEFINITION_HDR_REC_TYPE Required
528 --
529 -- Version :
530 -- Initial Version 1.0
531 --
532 -- End of Comments --
533 PROCEDURE CREATE_RELIABILITY_DATA (
534 p_api_version IN NUMBER,
535 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
536 p_commit IN VARCHAR2 := FND_API.G_FALSE,
537 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
538 p_module_type IN VARCHAR2,
539 x_return_status OUT NOCOPY VARCHAR2,
540 x_msg_count OUT NOCOPY NUMBER,
541 x_msg_data OUT NOCOPY VARCHAR2,
542 p_x_reliability_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE)
543 IS
544
545 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RELIABILITY_DATA';
546 l_api_version CONSTANT NUMBER := 1.0;
547 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
548 l_ra_def_hdr_rec AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE DEFAULT p_x_reliability_data_rec;
549 l_dummy VARCHAR2(1);
550
551 BEGIN
552
553 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
554 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
555 END IF;
556
557 -- Standard start of API savepoint
558 SAVEPOINT CREATE_RELIABILITY_DATA_SP;
559
560 -- Standard call to check for call compatibility
561 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
563 END IF;
564
565 -- Initialize message list if p_init_msg_list is set to TRUE
566 IF FND_API.To_Boolean(p_init_msg_list) THEN
567 FND_MSG_PUB.Initialize;
568 END IF;
569
570 x_return_status := FND_API.G_RET_STS_SUCCESS;
571
572 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
573 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_RELIABILITY_DATA -------BEGIN-----------');
574 END IF;
575
576 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
577 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Parameters Passed --');
578 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_hdr_rec.OPERATION_FLAG -- '||l_ra_def_hdr_rec.OPERATION_FLAG);
579 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_hdr_rec.MC_HEADER_ID -- '||l_ra_def_hdr_rec.MC_HEADER_ID);
580 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_hdr_rec.INVENTORY_ITEM_ID -- '||l_ra_def_hdr_rec.INVENTORY_ITEM_ID);
581 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_hdr_rec.ITEM_REVISION -- '||l_ra_def_hdr_rec.ITEM_REVISION);
582 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_hdr_rec.INVENTORY_ORG_ID -- '||l_ra_def_hdr_rec.INVENTORY_ORG_ID);
583 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_hdr_rec.RELATIONSHIP_ID -- '||l_ra_def_hdr_rec.RELATIONSHIP_ID);
584 END IF;
585
586 IF ((l_ra_def_hdr_rec.OPERATION_FLAG IS NULL) OR (l_ra_def_hdr_rec.OPERATION_FLAG <> G_DML_CREATE)) THEN
587 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
588 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - OP Flag-');
589 END IF;
590 FND_MESSAGE.Set_Name('AHL','AHL_COM_INVALID_DML_REC');
591 FND_MESSAGE.Set_Token('FIELD',l_ra_def_hdr_rec.OPERATION_FLAG);
592 FND_MSG_PUB.ADD;
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 END IF;
595
596 -- Validate input reliability data in l_ra_def_hdr_rec
597 /*
598 A. MC_HEADER_ID Cannot be NULL
599 B. INVENTORY_ITEM_ID cannot be NULL
600 C. INVENTORY_ORG_ID cannot be NULL
601 D. RELATIONSHIP_ID cannot be NULL
602 */
603 IF((l_ra_def_hdr_rec.MC_HEADER_ID IS NULL) OR
604 (l_ra_def_hdr_rec.INVENTORY_ITEM_ID IS NULL) OR
605 (l_ra_def_hdr_rec.INVENTORY_ORG_ID IS NULL) OR
606 (l_ra_def_hdr_rec.RELATIONSHIP_ID IS NULL)) THEN
607
608 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
609 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
610 END IF;
611
612 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
613 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_RA_DEF_DATA');
614 FND_MSG_PUB.ADD;
615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
616 END IF;
617
618 --Validate MC_HEADER_ID
619 BEGIN
620 SELECT 'Y'
621 INTO l_dummy
622 FROM dual
623 WHERE EXISTS( SELECT 'X'
624 FROM ahl_mc_headers_b
625 WHERE mc_header_id = l_ra_def_hdr_rec.MC_HEADER_ID);
626
627 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
628 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation successful: MR_HEADER_ID --');
629 END IF;
630
631 EXCEPTION
632 WHEN NO_DATA_FOUND THEN
633 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
634 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation Failed: MR_HEADER_ID --');
635 END IF;
636
637 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
638 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_RA_DEF_DATA');
639 FND_MSG_PUB.ADD;
640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
641 END;
642
643 --Validate RELATIONSHIP_ID
644 BEGIN
645 SELECT 'Y'
646 INTO l_dummy
647 FROM dual
648 WHERE EXISTS(SELECT 'X'
649 FROM ahl_mc_relationships
650 WHERE mc_header_id = l_ra_def_hdr_rec.MC_HEADER_ID
651 AND relationship_id = l_ra_def_hdr_rec.RELATIONSHIP_ID);
652
653 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
654 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation successful: RELATIONSHIP_ID --');
655 END IF;
656
657 EXCEPTION
658 WHEN NO_DATA_FOUND THEN
659 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
660 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation Failed: RELATIONSHIP_ID --');
661 END IF;
662
663 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
664 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_RA_DEF_DATA');
665 FND_MSG_PUB.ADD;
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667 END;
668
669 --Validate INVENTORY_ITEM_ID, INVENTORY_ORG_ID, ITEM_REVISION from AHL_ITEM_ASSOCIATIONS_B
670 BEGIN
671 SELECT 'Y'
672 INTO l_dummy
673 FROM dual
674 WHERE EXISTS(SELECT 'X'
675 FROM ahl_mc_relationships mcr, ahl_item_associations_b ia
676 WHERE mcr.relationship_id = l_ra_def_hdr_rec.RELATIONSHIP_ID
677 AND mcr.item_group_id = ia.item_group_id
678 AND ia.inventory_item_id = l_ra_def_hdr_rec.INVENTORY_ITEM_ID
679 AND ia.inventory_org_id = l_ra_def_hdr_rec.INVENTORY_ORG_ID
680 AND nvl(ia.revision,FND_API.G_MISS_CHAR) = nvl(l_ra_def_hdr_rec.ITEM_REVISION,FND_API.G_MISS_CHAR));
681
682
683 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
684 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation successful: Item --');
685 END IF;
686
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
690 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation Failed: Item --');
691 END IF;
692
693 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
694 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_RA_DEF_DATA');
695 FND_MSG_PUB.ADD;
696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
697 END;
698
699 --Uniquness Check for MC_HEADER_ID, RELATIONSHIP_ID, INVENTORY_ITEM_ID, INVENTORY_ORG_ID, ITEM_REVISION
700 BEGIN
701 SELECT 'Y'
702 INTO l_dummy
703 FROM dual
704 WHERE EXISTS(SELECT 'X'
705 FROM ahl_ra_definition_hdr
706 WHERE mc_header_id = l_ra_def_hdr_rec.MC_HEADER_ID
707 AND relationship_id = l_ra_def_hdr_rec.RELATIONSHIP_ID
708 AND inventory_item_id = l_ra_def_hdr_rec.INVENTORY_ITEM_ID
709 AND inventory_org_id = l_ra_def_hdr_rec.INVENTORY_ORG_ID
710 AND nvl(item_revision,FND_API.G_MISS_CHAR) = nvl(l_ra_def_hdr_rec.ITEM_REVISION,FND_API.G_MISS_CHAR));
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,'-- Validation Failed: Uniqnuess Check --');
714 END IF;
715
716 FND_MESSAGE.Set_Name('AHL','AHL_RA_REL_DEF_EXISTS');
717 FND_MSG_PUB.ADD;
718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
719
720 EXCEPTION
721 WHEN NO_DATA_FOUND THEN
722 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
723 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation successful: Uniqnuess Check --');
724 END IF;
725 END;
726
727 -- Initialize RA_DEFINITION_HDR_ID to sequence next val for insert
728 SELECT AHL_RA_DEFINITION_HDR_S.NEXTVAL into l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID from dual;
729
730 -- Initialize object version number to 1
731 l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER := 1;
732
733 -- Intialize who column info
734 l_ra_def_hdr_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
735 l_ra_def_hdr_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
736 l_ra_def_hdr_rec.CREATED_BY := fnd_global.user_id;
737 l_ra_def_hdr_rec.CREATION_DATE := sysdate;
738 l_ra_def_hdr_rec.LAST_UPDATE_DATE := sysdate;
739
740 -- Initialize security group id
741 l_ra_def_hdr_rec.SECURITY_GROUP_ID := null;
742
743 INSERT INTO AHL_RA_DEFINITION_HDR
744 (
745 RA_DEFINITION_HDR_ID,
746 MC_HEADER_ID,
747 INVENTORY_ITEM_ID,
748 INVENTORY_ORG_ID,
749 ITEM_REVISION,
750 RELATIONSHIP_ID,
751 OBJECT_VERSION_NUMBER,
752 SECURITY_GROUP_ID,
753 ATTRIBUTE_CATEGORY,
754 ATTRIBUTE1,
755 ATTRIBUTE2,
756 ATTRIBUTE3,
757 ATTRIBUTE4,
758 ATTRIBUTE5,
759 ATTRIBUTE6,
760 ATTRIBUTE7,
761 ATTRIBUTE8,
762 ATTRIBUTE9,
763 ATTRIBUTE10,
764 ATTRIBUTE11,
765 ATTRIBUTE12,
766 ATTRIBUTE13,
767 ATTRIBUTE14,
768 ATTRIBUTE15,
769 CREATION_DATE,
770 CREATED_BY,
771 LAST_UPDATE_DATE,
772 LAST_UPDATED_BY,
773 LAST_UPDATE_LOGIN
774 )
775 VALUES
776 (
777 l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID, -- RA_DEFINITION_HDR_ID
778 l_ra_def_hdr_rec.MC_HEADER_ID, -- MC_HEADER_ID
779 l_ra_def_hdr_rec.INVENTORY_ITEM_ID, -- INVENTORY_ITEM_ID
780 l_ra_def_hdr_rec.INVENTORY_ORG_ID, -- INVENTORY_ORG_ID
781 l_ra_def_hdr_rec.ITEM_REVISION, -- ITEM_REVISION
782 l_ra_def_hdr_rec.RELATIONSHIP_ID, -- RELATIONSHIP_ID
783 l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER, -- OBJECT_VERSION_NUMBER
784 l_ra_def_hdr_rec.SECURITY_GROUP_ID, -- SECURITY_GROUP_ID
785 l_ra_def_hdr_rec.ATTRIBUTE_CATEGORY, -- ATTRIBUTE_CATEGORY
786 l_ra_def_hdr_rec.ATTRIBUTE1, -- ATTRIBUTE1
787 l_ra_def_hdr_rec.ATTRIBUTE2, -- ATTRIBUTE2
788 l_ra_def_hdr_rec.ATTRIBUTE3, -- ATTRIBUTE3
789 l_ra_def_hdr_rec.ATTRIBUTE4, -- ATTRIBUTE4
790 l_ra_def_hdr_rec.ATTRIBUTE5, -- ATTRIBUTE5
791 l_ra_def_hdr_rec.ATTRIBUTE6, -- ATTRIBUTE6
792 l_ra_def_hdr_rec.ATTRIBUTE7, -- ATTRIBUTE7
793 l_ra_def_hdr_rec.ATTRIBUTE8, -- ATTRIBUTE8
794 l_ra_def_hdr_rec.ATTRIBUTE9, -- ATTRIBUTE9
795 l_ra_def_hdr_rec.ATTRIBUTE10, -- ATTRIBUTE10
796 l_ra_def_hdr_rec.ATTRIBUTE11, -- ATTRIBUTE11
797 l_ra_def_hdr_rec.ATTRIBUTE12, -- ATTRIBUTE12
798 l_ra_def_hdr_rec.ATTRIBUTE13, -- ATTRIBUTE13
799 l_ra_def_hdr_rec.ATTRIBUTE14, -- ATTRIBUTE14
800 l_ra_def_hdr_rec.ATTRIBUTE15, -- ATTRIBUTE15
801 l_ra_def_hdr_rec.CREATION_DATE, -- CREATION_DATE
802 l_ra_def_hdr_rec.CREATED_BY, -- CREATED_BY
803 l_ra_def_hdr_rec.LAST_UPDATE_DATE, -- LAST_UPDATE_DATE
804 l_ra_def_hdr_rec.LAST_UPDATED_BY, -- LAST_UPDATED_BY
805 l_ra_def_hdr_rec.LAST_UPDATE_LOGIN -- LAST_UPDATE_LOGIN
806 );
807
808 p_x_reliability_data_rec := l_ra_def_hdr_rec;
809 -- Standard check for p_commit
810 IF FND_API.To_Boolean (p_commit) THEN
811 COMMIT;
812 END IF;
813
814 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
815 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_RELIABILITY_DATA -------END-----------');
816 END IF;
817
818 -- Standard call to get message count and if count is 1, get message
819 FND_MSG_PUB.Count_And_Get
820 ( p_count => x_msg_count,
821 p_data => x_msg_data,
822 p_encoded => fnd_api.g_false);
823
824 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
825 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
826 END IF;
827
828 EXCEPTION
829 WHEN FND_API.G_EXC_ERROR THEN
830 x_return_status := FND_API.G_RET_STS_ERROR;
831 ROLLBACK TO CREATE_RELIABILITY_DATA_SP;
832 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
833 p_data => x_msg_data,
834 p_encoded => fnd_api.g_false);
835
836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838 ROLLBACK TO CREATE_RELIABILITY_DATA_SP;
839 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
840 p_data => x_msg_data,
841 p_encoded => fnd_api.g_false);
842
843 WHEN OTHERS THEN
844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
845 ROLLBACK TO CREATE_RELIABILITY_DATA_SP;
846 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
847 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
848 p_procedure_name => 'CREATE_RELIABILITY_DATA',
849 p_error_text => SUBSTR(SQLERRM,1,240));
850 END IF;
851 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
852 p_data => x_msg_data,
853 p_encoded => fnd_api.g_false);
854
855 END CREATE_RELIABILITY_DATA;
856
857 -- Start of Comments --
858 --
859 -- Procedure name : DELETE_RELIABILITY_DATA
860 -- Type : Private
861 -- Function : This API would delete the setup data for Reliability Framework in AHL_RA_DEFINITION_HDR
862 -- Pre-reqs :
863 --
864 -- Standard IN Parameters :
865 -- p_api_version IN NUMBER Required
866 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
867 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
868 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
869 --
870 -- Standard OUT Parameters :
871 -- x_return_status OUT VARCHAR2 Required
872 -- x_msg_count OUT NUMBER Required
873 -- x_msg_data OUT VARCHAR2 Required
874 --
875 -- DELETE_RELIABILITY_DATA Parameters :
876 -- p_reliability_data_rec IN OUT RA_DEFINITION_HDR_REC_TYPE Required
877 --
878 -- Version :
879 -- Initial Version 1.0
880 --
881 -- End of Comments --
882 PROCEDURE DELETE_RELIABILITY_DATA (
883 p_api_version IN NUMBER,
884 p_init_msg_list IN VARCHAR2,
885 p_commit IN VARCHAR2,
886 p_validation_level IN NUMBER,
887 p_module_type IN VARCHAR2,
888 x_return_status OUT NOCOPY VARCHAR2,
889 x_msg_count OUT NOCOPY NUMBER,
890 x_msg_data OUT NOCOPY VARCHAR2,
891 p_reliability_data_rec IN AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE) IS
892
893 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RELIABILITY_DATA';
894 l_api_version CONSTANT NUMBER := 1.0;
895 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
896
897 l_obj_version_num AHL_RA_DEFINITION_HDR.OBJECT_VERSION_NUMBER%TYPE;
898
899 BEGIN
900
901 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
902 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
903 END IF;
904
905 -- Standard start of API savepoint
906 SAVEPOINT DELETE_RELIABILITY_DATA_SP;
907
908 -- Standard call to check for call compatibility
909 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END IF;
912
913 -- Initialize message list if p_init_msg_list is set to TRUE
914 IF FND_API.To_Boolean(p_init_msg_list) THEN
915 FND_MSG_PUB.Initialize;
916 END IF;
917
918 x_return_status := FND_API.G_RET_STS_SUCCESS;
919
920 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
921 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_RELIABILITY_DATA -------BEGIN-----------');
922 END IF;
923
924 IF ((p_reliability_data_rec.OPERATION_FLAG IS NULL) OR (p_reliability_data_rec.OPERATION_FLAG <> G_DML_DELETE)) THEN
925 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
926 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
927 END IF;
928 FND_MESSAGE.Set_Name('AHL','AHL_COM_INVALID_DML_REC');
929 FND_MESSAGE.Set_Token('FIELD', p_reliability_data_rec.OPERATION_FLAG);
930 FND_MSG_PUB.ADD;
931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
932 END IF;
933
934 -- Validate input data in p_reliability_data_rec
935 -- A. p_reliability_data_rec. RA_DEFINITION_HDR_ID Cannot be NULL
936 -- C. Object Version Number should not be NULL
937 IF ((p_reliability_data_rec.RA_DEFINITION_HDR_ID IS NULL) OR
938 (p_reliability_data_rec.OBJECT_VERSION_NUMBER IS NULL)) THEN
939 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
940 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- RA_DEFINITION_HDR_ID :' || p_reliability_data_rec.RA_DEFINITION_HDR_ID);
941 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- OVN :' || p_reliability_data_rec.OBJECT_VERSION_NUMBER);
942 END IF;
943 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
944 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.D_RA_DEF_DATA');
945 FND_MSG_PUB.ADD;
946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
947 END IF;
948
949 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
950 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Delete counter-MTBF records for RA_DEFINITION_HDR_ID -- ' || p_reliability_data_rec.RA_DEFINITION_HDR_ID);
951 END IF;
952
953 -- Check for existence of record and fetch OVN for change record validation
954 BEGIN
955 SELECT OBJECT_VERSION_NUMBER
956 INTO l_obj_version_num
957 FROM AHL_RA_DEFINITION_HDR
958 WHERE RA_DEFINITION_HDR_ID = p_reliability_data_rec.RA_DEFINITION_HDR_ID
959 FOR UPDATE OF object_version_number NOWAIT;
960
961 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
962 fnd_log.string(fnd_log.level_statement,l_full_name,'-- OBJECT VERSION NUMBER IN DB : ' || l_obj_version_num);
963 END IF;
964
965 EXCEPTION
966 WHEN NO_DATA_FOUND THEN
967 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
968 fnd_log.string(fnd_log.level_statement,l_full_name,'-- DATA DOES NOT EXISTS -- ERROR ... ' || p_reliability_data_rec.RA_DEFINITION_HDR_ID);
969 END IF;
970 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
971 FND_MSG_PUB.ADD;
972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
973 END;
974
975 -- Validate input data in p_reliability_data_rec
976 -- A. RECORD SHOULD NOT HAVE CHANGED. i.e. object_version_number should not change.
977 -- Child Locking Check is implemented for Setup Data - When Child Is edited/inserted/deleted OVN of Master is bumped up
978 IF l_obj_version_num <> p_reliability_data_rec.OBJECT_VERSION_NUMBER THEN
979 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
980 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN passed : ' || p_reliability_data_rec.OBJECT_VERSION_NUMBER);
981 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN in db : ' || l_obj_version_num);
982 END IF;
983 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
984 FND_MSG_PUB.ADD;
985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
986 END IF;
987
988 --Delete the child records before deleting the master record.
989 DELETE AHL_RA_DEFINITION_DTLS
990 WHERE RA_DEFINITION_HDR_ID = p_reliability_data_rec.RA_DEFINITION_HDR_ID;
991
992 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
993 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Child Records have been deleted --');
994 END IF;
995
996 --Delete the master record.
997 DELETE AHL_RA_DEFINITION_HDR
998 WHERE RA_DEFINITION_HDR_ID = p_reliability_data_rec.RA_DEFINITION_HDR_ID;
999
1000 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1001 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Master Record has been deleted --');
1002 END IF;
1003
1004 -- Standard check for p_commit
1005 IF FND_API.To_Boolean (p_commit) THEN
1006 COMMIT;
1007 END IF;
1008
1009 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1010 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_RELIABILITY_DATA -------END-----------');
1011 END IF;
1012
1013 -- Standard call to get message count and if count is 1, get message
1014 FND_MSG_PUB.Count_And_Get
1015 ( p_count => x_msg_count,
1016 p_data => x_msg_data,
1017 p_encoded => fnd_api.g_false);
1018
1019 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1020 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
1021 END IF;
1022
1023 EXCEPTION
1024 WHEN FND_API.G_EXC_ERROR THEN
1025 x_return_status := FND_API.G_RET_STS_ERROR;
1026 ROLLBACK TO DELETE_RELIABILITY_DATA_SP;
1027 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1028 p_data => x_msg_data,
1029 p_encoded => fnd_api.g_false);
1030
1031 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1033 ROLLBACK TO DELETE_RELIABILITY_DATA_SP;
1034 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1035 p_data => x_msg_data,
1036 p_encoded => fnd_api.g_false);
1037
1038 WHEN OTHERS THEN
1039 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1040 ROLLBACK TO DELETE_RELIABILITY_DATA_SP;
1041 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1042 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1043 p_procedure_name => 'DELETE_RELIABILITY_DATA',
1044 p_error_text => SUBSTR(SQLERRM,1,240));
1045 END IF;
1046 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1047 p_data => x_msg_data,
1048 p_encoded => fnd_api.g_false);
1049
1050 END DELETE_RELIABILITY_DATA;
1051
1052
1053 -- Start of Comments --
1054 --
1055 -- Procedure name : CREATE_MTBF_DATA
1056 -- Type : Private
1057 -- Function : This API would create the setup data for Reliability Framework in AHL_RA_DEFINITION_DTLS
1058 -- Pre-reqs :
1059 --
1060 -- Standard IN Parameters :
1061 -- p_api_version IN NUMBER Required
1062 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1063 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1064 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1065 --
1066 -- Standard OUT Parameters :
1067 -- x_return_status OUT VARCHAR2 Required
1068 -- x_msg_count OUT NUMBER Required
1069 -- x_msg_data OUT VARCHAR2 Required
1070 --
1071 -- CREATE_MTBF_DATA Parameters :
1072 -- p_x_mtbf_data_rec IN OUT RA_DEFINITION_DTLS_REC_TYPE Required
1073 --
1074 -- Version :
1075 -- Initial Version 1.0
1076 --
1077 -- End of Comments --
1078 PROCEDURE CREATE_MTBF_DATA (
1079 p_api_version IN NUMBER,
1080 p_init_msg_list IN VARCHAR2,
1081 p_commit IN VARCHAR2,
1082 p_validation_level IN NUMBER,
1083 p_module_type IN VARCHAR2,
1084 x_return_status OUT NOCOPY VARCHAR2,
1085 x_msg_count OUT NOCOPY NUMBER,
1086 x_msg_data OUT NOCOPY VARCHAR2,
1087 p_x_reliability_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE,
1088 p_x_mtbf_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_DEFINITION_DTLS_REC_TYPE) IS
1089
1090 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MTBF_DATA';
1091 l_api_version CONSTANT NUMBER := 1.0;
1092 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
1093
1094 l_ra_def_hdr_rec AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE DEFAULT p_x_reliability_data_rec;
1095 l_ra_def_dtl_rec AHL_RA_SETUPS_PVT.RA_DEFINITION_DTLS_REC_TYPE DEFAULT p_x_mtbf_data_rec;
1096 l_m_obj_version_num AHL_RA_DEFINITION_HDR.OBJECT_VERSION_NUMBER%TYPE;
1097 l_cou_name CSI_COUNTER_TEMPLATE_VL.NAME%TYPE;
1098 l_dummy VARCHAR2(1);
1099
1100 BEGIN
1101
1102 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1103 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
1104 END IF;
1105
1106 -- Standard start of API savepoint
1107 SAVEPOINT CREATE_MTBF_DATA_SP;
1108
1109 -- Standard call to check for call compatibility
1110 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1112 END IF;
1113
1114 -- Initialize message list if p_init_msg_list is set to TRUE
1115 IF FND_API.To_Boolean(p_init_msg_list) THEN
1116 FND_MSG_PUB.Initialize;
1117 END IF;
1118
1119 x_return_status := FND_API.G_RET_STS_SUCCESS;
1120
1121 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1122 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_MTBF_DATA -------BEGIN-----------');
1123 END IF;
1124
1125 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1126 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Parameters Passed --');
1127 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID -- '||l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1128 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_dtl_rec.COUNTER_ID -- '||l_ra_def_dtl_rec.COUNTER_ID);
1129 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_dtl_rec.MTBF_VALUE -- '||l_ra_def_dtl_rec.MTBF_VALUE);
1130 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_dtl_rec.OPERATION_FLAG -- '||l_ra_def_dtl_rec.OPERATION_FLAG);
1131 END IF;
1132
1133 IF ((l_ra_def_dtl_rec.OPERATION_FLAG IS NULL) OR (l_ra_def_dtl_rec.OPERATION_FLAG <> G_DML_CREATE)) THEN
1134 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1135 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
1136 END IF;
1137 FND_MESSAGE.Set_Name('AHL','AHL_COM_INVALID_DML_REC');
1138 FND_MESSAGE.Set_Token('FIELD',l_ra_def_dtl_rec.OPERATION_FLAG);
1139 FND_MSG_PUB.ADD;
1140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1141 END IF;
1142
1143 --Mandatory validations for CounterId and RA_DEFINITION_HDR_ID
1144 IF ((l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID IS NULL AND l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID IS NULL) OR
1145 (l_ra_def_dtl_rec.COUNTER_ID IS NULL) OR
1146 (l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER IS NULL)) THEN
1147 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1148 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - RA_DEFINITION_HDR_ID -'||l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1149 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - COUNTER_ID -'||l_ra_def_dtl_rec.COUNTER_ID);
1150 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - RA_DEFINITION_HDR_ID -'||l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID);
1151 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - OBJECT_VERSION_NUMBER -'||l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER);
1152 END IF;
1153 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
1154 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_RA_DEF_DTL_DATA');
1155 FND_MSG_PUB.ADD;
1156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1157 END IF;
1158
1159 IF ((l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID IS NOT NULL AND l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID IS NOT NULL) AND
1160 (l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID <> l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID)) THEN
1161 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1162 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - RA_DEFINITION_HDR_ID MASTER-'||l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID);
1163 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - RA_DEFINITION_HDR_ID CHILD-'||l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1164 END IF;
1165 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
1166 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_RA_DEF_DTL_DATA');
1167 FND_MSG_PUB.ADD;
1168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1169 END IF;
1170
1171 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1172 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Default RA_DEFINITION_HDR_ID in child rec from header rec and vice-versa- if passed as null. -- ');
1173 END IF;
1174
1175 -- Default RA_DEFINITION_HDR_ID in child rec from header rec - if passed as null.
1176 IF l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID IS NULL THEN
1177 l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID := l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID;
1178 ELSE
1179 l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID := l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID;
1180 END IF;
1181
1182 --Validate CounterId passed
1183 BEGIN
1184 -- Bug 4913954 : Perf Fixes.
1185 -- Since we are using reference to cs_counter_groups with a join condition TEMPLATE_FLAG = 'Y'
1186 -- i.e only counter templates, direct reference to cs_csi_counter_groups can be used here.
1187 SELECT templates.name
1188 INTO l_cou_name
1189 FROM cs_csi_counter_groups cg,
1190 csi_counter_template_vl templates,
1191 csi_ctr_item_associations csia,
1192 ahl_ra_definition_hdr rdh
1193 WHERE templates.counter_id = l_ra_def_dtl_rec.COUNTER_ID
1194 AND cg.template_flag = 'Y'
1195 AND templates.group_id = cg.counter_group_id
1196 AND csia.group_id = cg.counter_group_id
1197 AND csia.inventory_item_id = rdh.inventory_item_id
1198 --Added the following on 13-sep-2005 after the feedback from csi team
1199 AND nvl(csia.associated_to_group,'N') = 'Y'
1200 AND csia.counter_id is null
1201 --mpothuku end
1202 AND rdh.RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
1203 AND trunc(sysdate) < trunc(nvl(templates.end_date_active,sysdate+1))
1204 AND trunc(sysdate) < trunc(nvl(csia.end_date_active,sysdate+1));
1205 --Did not add the start date check above as this is setup and we might want to
1206 --define associations for counters that might get activated in future.
1207
1208 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1209 fnd_log.string(fnd_log.level_statement,l_full_name,'-- COUNTER_ID Validated Successfully--');
1210 END IF;
1211
1212 EXCEPTION
1213 WHEN NO_DATA_FOUND THEN
1214 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1215 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - COUNTER_ID -' || l_ra_def_dtl_rec.COUNTER_ID);
1216 END IF;
1217 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
1218 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_RA_DEF_DTL_DATA');
1219 FND_MSG_PUB.ADD;
1220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1221 END;
1222
1223 --MTBF should be >= 0 if not null
1224 IF(l_ra_def_dtl_rec.MTBF_VALUE IS NOT NULL and l_ra_def_dtl_rec.MTBF_VALUE < 0) THEN
1225 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1226 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - MTBF_VALUE -'||l_ra_def_dtl_rec.MTBF_VALUE);
1227 END IF;
1228 FND_MESSAGE.Set_Name('AHL','AHL_RA_MTBF_INV');
1229 FND_MESSAGE.Set_Token('COUNTER',l_cou_name);
1230 FND_MESSAGE.Set_Token('MTBF', l_ra_def_dtl_rec.MTBF_VALUE);
1231 FND_MSG_PUB.ADD;
1232 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1233 END IF;
1234
1235 --Uniqueness Check for the counterId
1236 BEGIN
1237 SELECT 'Y'
1238 INTO l_dummy
1239 FROM dual
1240 WHERE EXISTS(SELECT 'X'
1241 FROM AHL_RA_DEFINITION_DTLS
1242 WHERE COUNTER_ID = l_ra_def_dtl_rec.COUNTER_ID
1243 AND RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1244
1245 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1246 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Duplicate exists - COUNTER_ID -' || l_ra_def_dtl_rec.COUNTER_ID);
1247 END IF;
1248
1249 FND_MESSAGE.Set_Name('AHL','AHL_RA_REL_DTL_EXISTS');
1250 FND_MESSAGE.Set_Token('COUNTER',l_cou_name);
1251 FND_MSG_PUB.ADD;
1252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1253
1254 EXCEPTION
1255 WHEN NO_DATA_FOUND THEN
1256 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1257 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Duplicate does not exist-- COUNTER_ID Validated Successfully--');
1258 END IF;
1259 END;
1260
1261 -- Check for existence of record and fetch OVN of Master Rec for change record validation and bump up
1262 BEGIN
1263 SELECT OBJECT_VERSION_NUMBER
1264 INTO l_m_obj_version_num
1265 FROM AHL_RA_DEFINITION_HDR
1266 WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
1267 FOR UPDATE OF object_version_number NOWAIT;
1268
1269 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1270 fnd_log.string(fnd_log.level_statement,l_full_name,'-- OBJECT VERSION NUMBER IN DB OF MASTER: ' || l_m_obj_version_num);
1271 END IF;
1272
1273 EXCEPTION
1274 WHEN NO_DATA_FOUND THEN
1275 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1276 fnd_log.string(fnd_log.level_statement,l_full_name,'-- DATA DOES NOT EXISTS -- ERROR ... ' || l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1277 END IF;
1278 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
1279 FND_MSG_PUB.ADD;
1280 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1281 END;
1282
1283 -- Validate input data in p_x_reliability_data_rec
1284 -- A. RECORD SHOULD NOT HAVE CHANGED. i.e. object_version_number should not change.
1285 -- Child Locking Check is implemented for Setup Data - When Child Is edited/inserted/deleted OVN of Master is bumped up
1286 IF l_m_obj_version_num <> l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER THEN
1287 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1288 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN passed : ' || l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER);
1289 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN in db : ' || l_m_obj_version_num);
1290 END IF;
1291 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1292 FND_MSG_PUB.ADD;
1293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1294 END IF;
1295
1296 -- Initialize RA_DEFINITION_DTL_ID to sequence next val for insert
1297 SELECT AHL_RA_DEFINITION_DTLS_S.NEXTVAL into l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID from dual;
1298
1299 -- Initialize object version number to 1
1300 l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER := 1;
1301
1302 -- Intialize who column info
1303 l_ra_def_dtl_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
1304 l_ra_def_dtl_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
1305 l_ra_def_dtl_rec.CREATED_BY := fnd_global.user_id;
1306 l_ra_def_dtl_rec.CREATION_DATE := sysdate;
1307 l_ra_def_dtl_rec.LAST_UPDATE_DATE := sysdate;
1308
1309 -- Initialize security group id
1310 l_ra_def_dtl_rec.SECURITY_GROUP_ID := null;
1311
1312 --Insert the record into AHL_RA_DEFINITION_DTLS
1313 INSERT INTO AHL_RA_DEFINITION_DTLS
1314 (
1315 RA_DEFINITION_DTL_ID,
1316 RA_DEFINITION_HDR_ID,
1317 COUNTER_ID,
1318 MTBF_VALUE,
1319 OBJECT_VERSION_NUMBER,
1320 SECURITY_GROUP_ID,
1321 ATTRIBUTE_CATEGORY,
1322 ATTRIBUTE1,
1323 ATTRIBUTE2,
1324 ATTRIBUTE3,
1325 ATTRIBUTE4,
1326 ATTRIBUTE5,
1327 ATTRIBUTE6,
1328 ATTRIBUTE7,
1329 ATTRIBUTE8,
1330 ATTRIBUTE9,
1331 ATTRIBUTE10,
1332 ATTRIBUTE11,
1333 ATTRIBUTE12,
1334 ATTRIBUTE13,
1335 ATTRIBUTE14,
1336 ATTRIBUTE15,
1337 CREATION_DATE,
1338 CREATED_BY,
1339 LAST_UPDATE_DATE,
1340 LAST_UPDATED_BY,
1341 LAST_UPDATE_LOGIN
1342 )
1343 VALUES
1344 (
1345 l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID,
1346 l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID,
1347 l_ra_def_dtl_rec.COUNTER_ID,
1348 l_ra_def_dtl_rec.MTBF_VALUE,
1349 l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER,
1350 l_ra_def_dtl_rec.SECURITY_GROUP_ID,
1351 l_ra_def_dtl_rec.ATTRIBUTE_CATEGORY,
1352 l_ra_def_dtl_rec.ATTRIBUTE1,
1353 l_ra_def_dtl_rec.ATTRIBUTE2,
1354 l_ra_def_dtl_rec.ATTRIBUTE3,
1355 l_ra_def_dtl_rec.ATTRIBUTE4,
1356 l_ra_def_dtl_rec.ATTRIBUTE5,
1357 l_ra_def_dtl_rec.ATTRIBUTE6,
1358 l_ra_def_dtl_rec.ATTRIBUTE7,
1359 l_ra_def_dtl_rec.ATTRIBUTE8,
1360 l_ra_def_dtl_rec.ATTRIBUTE9,
1361 l_ra_def_dtl_rec.ATTRIBUTE10,
1362 l_ra_def_dtl_rec.ATTRIBUTE11,
1363 l_ra_def_dtl_rec.ATTRIBUTE12,
1364 l_ra_def_dtl_rec.ATTRIBUTE13,
1365 l_ra_def_dtl_rec.ATTRIBUTE14,
1366 l_ra_def_dtl_rec.ATTRIBUTE15,
1367 l_ra_def_dtl_rec.CREATION_DATE,
1368 l_ra_def_dtl_rec.CREATED_BY,
1369 l_ra_def_dtl_rec.LAST_UPDATE_DATE,
1370 l_ra_def_dtl_rec.LAST_UPDATED_BY,
1371 l_ra_def_dtl_rec.LAST_UPDATE_LOGIN
1372 );
1373
1374 p_x_mtbf_data_rec := l_ra_def_dtl_rec;
1375
1376 l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER := l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER + 1;
1377
1378 -- Update Object Version Number of Master Record are Inserting child record.
1379 UPDATE AHL_RA_DEFINITION_HDR
1380 SET OBJECT_VERSION_NUMBER = l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER
1381 WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID;
1382
1383 p_x_reliability_data_rec := l_ra_def_hdr_rec;
1384
1385 -- Standard check for p_commit
1386 IF FND_API.To_Boolean (p_commit) THEN
1387 COMMIT;
1388 END IF;
1389
1390 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1391 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_MTBF_DATA -------END-----------');
1392 END IF;
1393
1394 -- Standard call to get message count and if count is 1, get message
1395 FND_MSG_PUB.Count_And_Get
1396 ( p_count => x_msg_count,
1397 p_data => x_msg_data,
1398 p_encoded => fnd_api.g_false);
1399
1400 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1401 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','At the start of PLSQL procedure');
1402 END IF;
1403
1404 EXCEPTION
1405 WHEN FND_API.G_EXC_ERROR THEN
1406 x_return_status := FND_API.G_RET_STS_ERROR;
1407 ROLLBACK TO CREATE_MTBF_DATA_SP;
1408 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1409 p_data => x_msg_data,
1410 p_encoded => fnd_api.g_false);
1411
1412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414 ROLLBACK TO CREATE_MTBF_DATA_SP;
1415 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1416 p_data => x_msg_data,
1417 p_encoded => fnd_api.g_false);
1418
1419 WHEN OTHERS THEN
1420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1421 ROLLBACK TO CREATE_MTBF_DATA_SP;
1422 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1423 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1424 p_procedure_name => 'CREATE_MTBF_DATA',
1425 p_error_text => SUBSTR(SQLERRM,1,240));
1426 END IF;
1427 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1428 p_data => x_msg_data,
1429 p_encoded => fnd_api.g_false);
1430
1431 END CREATE_MTBF_DATA;
1432
1433
1434 -- Start of Comments --
1435 --
1436 -- Procedure name : UPDATE_MTBF_DATA
1437 -- Type : Private
1438 -- Function : This API would update the setup data for Reliability Framework in AHL_RA_DEFINITION_DTLS
1439 -- Pre-reqs :
1440 --
1441 -- Standard IN Parameters :
1442 -- p_api_version IN NUMBER Required
1443 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1444 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1445 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1446 --
1447 -- Standard OUT Parameters :
1448 -- x_return_status OUT VARCHAR2 Required
1449 -- x_msg_count OUT NUMBER Required
1450 -- x_msg_data OUT VARCHAR2 Required
1451 --
1452 -- UPDATE_MTBF_DATA Parameters :
1453 -- p_x_mtbf_data_rec IN OUT RA_DEFINITION_DTLS_REC_TYPE Required
1454 --
1455 -- Version :
1456 -- Initial Version 1.0
1457 --
1458 -- End of Comments --
1459 PROCEDURE UPDATE_MTBF_DATA (
1460 p_api_version IN NUMBER,
1461 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1462 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1463 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1464 p_module_type IN VARCHAR2,
1465 x_return_status OUT NOCOPY VARCHAR2,
1466 x_msg_count OUT NOCOPY NUMBER,
1467 x_msg_data OUT NOCOPY VARCHAR2,
1468 p_x_reliability_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE,
1469 p_x_mtbf_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_DEFINITION_DTLS_REC_TYPE) IS
1470
1471 CURSOR get_mtbf_data_cur(p_ra_definition_dtl_id IN NUMBER) IS
1472 SELECT RA_DEFINITION_DTL_ID,
1473 RA_DEFINITION_HDR_ID,
1474 COUNTER_ID,
1475 MTBF_VALUE,
1476 OBJECT_VERSION_NUMBER
1477 FROM AHL_RA_DEFINITION_DTLS
1478 WHERE RA_DEFINITION_DTL_ID = p_ra_definition_dtl_id
1479 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1480
1481 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MTBF_DATA';
1482 l_api_version CONSTANT NUMBER := 1.0;
1483 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME || ' : ';
1484 l_mtbf_data_old_rec get_mtbf_data_cur%ROWTYPE;
1485 l_ra_def_dtl_rec AHL_RA_SETUPS_PVT.RA_DEFINITION_DTLS_REC_TYPE DEFAULT p_x_mtbf_data_rec;
1486 l_ra_def_hdr_rec AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE DEFAULT p_x_reliability_data_rec;
1487 l_m_obj_version_num AHL_RA_DEFINITION_HDR.OBJECT_VERSION_NUMBER%TYPE;
1488 l_cou_name CSI_COUNTER_TEMPLATE_VL.NAME%TYPE;
1489
1490 BEGIN
1491
1492 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1493 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
1494 END IF;
1495
1496 -- Standard start of API savepoint
1497 SAVEPOINT UPDATE_MTBF_DATA_SP;
1498
1499 -- Standard call to check for call compatibility
1500 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1502 END IF;
1503
1504 -- Initialize message list if p_init_msg_list is set to TRUE
1505 IF FND_API.To_Boolean(p_init_msg_list) THEN
1506 FND_MSG_PUB.Initialize;
1507 END IF;
1508
1509 x_return_status := FND_API.G_RET_STS_SUCCESS;
1510
1511 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1512 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- UPDATE_MTBF_DATA -------BEGIN-----------');
1513 END IF;
1514
1515 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1516 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Parameters Passed --');
1517 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID -- '||l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID);
1518 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID -- '||l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1519 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_dtl_rec.COUNTER_ID -- '||l_ra_def_dtl_rec.COUNTER_ID);
1520 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_dtl_rec.MTBF_VALUE -- '||l_ra_def_dtl_rec.MTBF_VALUE);
1521 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_ra_def_dtl_rec.OPERATION_FLAG -- '||l_ra_def_dtl_rec.OPERATION_FLAG);
1522 END IF;
1523
1524 IF ((l_ra_def_dtl_rec.OPERATION_FLAG IS NULL) OR (l_ra_def_dtl_rec.OPERATION_FLAG <> G_DML_UPDATE)) THEN
1525 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1526 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
1527 FND_MESSAGE.Set_Name('AHL','AHL_COM_INVALID_DML_REC');
1528 FND_MESSAGE.Set_Token('FIELD',l_ra_def_dtl_rec.OPERATION_FLAG);
1529 FND_MSG_PUB.ADD;
1530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1531 END IF;
1532 END IF;
1533
1534 --RA_DEFINITION_DTL_ID and OBJECT_VERSION_NUMBER are mandatory
1535 IF( l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID IS NULL OR
1536 l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID = FND_API.G_MISS_NUM OR
1537 l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER IS NULL OR
1538 l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM OR
1539 l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID = FND_API.G_MISS_NUM OR
1540 l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID = FND_API.G_MISS_NUM OR
1541 (l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID IS NULL AND l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID IS NULL)) THEN
1542 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
1543 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.U_RA_DEF_DTL_DATA');
1544 FND_MSG_PUB.ADD;
1545 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1546 END IF;
1547
1548 --Check that both the HDR_ID is same in both the master and child records.
1549 IF ((l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID IS NOT NULL AND l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID IS NOT NULL) AND
1550 (l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID <> l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID)) THEN
1551 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1552 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - RA_DEFINITION_HDR_ID MASTER-'||l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID);
1553 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - RA_DEFINITION_HDR_ID CHILD-'||l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1554 END IF;
1555 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
1556 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.U_RA_DEF_DTL_DATA');
1557 FND_MSG_PUB.ADD;
1558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1559 END IF;
1560
1561 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1562 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Default RA_DEFINITION_HDR_ID in child rec from header rec and vice-versa- if passed as null. -- ');
1563 END IF;
1564
1565 -- Default RA_DEFINITION_HDR_ID in child rec from header rec - if passed as null.
1566 IF l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID IS NULL THEN
1567 l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID := l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID;
1568 ELSE
1569 l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID := l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID;
1570 END IF;
1571
1572
1573 --get current mtbf record
1574 OPEN get_mtbf_data_cur(l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID);
1575 FETCH get_mtbf_data_cur INTO l_mtbf_data_old_rec;
1576 IF(get_mtbf_data_cur%NOTFOUND) THEN
1577 CLOSE get_mtbf_data_cur;
1578 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
1579 FND_MSG_PUB.ADD;
1580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1581 END IF;
1582 CLOSE get_mtbf_data_cur;
1583
1584 IF(l_mtbf_data_old_rec.OBJECT_VERSION_NUMBER <> l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER) THEN
1585 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1586 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN passed : ' || l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER);
1587 END IF;
1588 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1589 FND_MSG_PUB.ADD;
1590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1591 END IF;
1592
1593 -- Default missing and unchanged attributes.
1594 IF (p_module_type <> 'OAF') THEN
1595
1596 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1597 fnd_log.string(fnd_log.level_statement, l_full_name, 'default_unchanged_attributes for update operation. Module type is '||p_module_type);
1598 END IF;
1599
1600 -- Default RA_DEFINITION_HDR_ID
1601 /* RA_DEFINITION_HDR_ID cannot be defaulted to null as it is the forigen key reference hence cannot be passed as G_MISS_NUM */
1602
1603 IF (l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID IS NULL) THEN
1604 l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID := l_mtbf_data_old_rec.RA_DEFINITION_HDR_ID;
1605 END IF;
1606
1607 -- Default COUNTER_ID
1608 IF (l_ra_def_dtl_rec.COUNTER_ID IS NULL) THEN
1609 l_ra_def_dtl_rec.COUNTER_ID := l_mtbf_data_old_rec.COUNTER_ID;
1610 ELSIF l_ra_def_dtl_rec.COUNTER_ID = FND_API.G_MISS_NUM THEN
1611 l_ra_def_dtl_rec.COUNTER_ID := NULL;
1612 END IF;
1613
1614 -- Default MTBF_VALUE
1615 IF (l_ra_def_dtl_rec.MTBF_VALUE IS NULL) THEN
1616 l_ra_def_dtl_rec.MTBF_VALUE := l_mtbf_data_old_rec.MTBF_VALUE;
1617 ELSIF l_ra_def_dtl_rec.MTBF_VALUE = FND_API.G_MISS_NUM THEN
1618 l_ra_def_dtl_rec.MTBF_VALUE := NULL;
1619 END IF;
1620
1621 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1622 fnd_log.string(fnd_log.level_statement, l_full_name, 'defaulting completed successfully');
1623 END IF;
1624
1625 END IF; --( p_module_type <> 'OAF' )
1626
1627 --Validate CounterId passed
1628 BEGIN
1629 -- Bug 4913954 : Perf Fixes.
1630 -- Since we are using reference to cs_counter_groups with a join condition TEMPLATE_FLAG = 'Y'
1631 -- i.e only counter templates, direct reference to cs_csi_counter_groups can be used here.
1632 SELECT templates.name
1633 INTO l_cou_name
1634 FROM cs_csi_counter_groups cg,
1635 csi_counter_template_vl templates,
1636 csi_ctr_item_associations csia,
1637 ahl_ra_definition_hdr rdh
1638 WHERE templates.counter_id = l_ra_def_dtl_rec.COUNTER_ID
1639 AND cg.template_flag = 'Y'
1640 AND templates.group_id = cg.counter_group_id
1641 AND csia.group_id = cg.counter_group_id
1642 AND csia.inventory_item_id = rdh.inventory_item_id
1643 --Added the following on 13-sep-2005 after the feedback from csi team
1644 AND nvl(csia.associated_to_group,'N') = 'Y'
1645 AND csia.counter_id is null
1646 --mpothuku end
1647 AND rdh.RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
1648 AND trunc(sysdate) < trunc(nvl(templates.end_date_active,sysdate+1))
1649 AND trunc(sysdate) < trunc(nvl(csia.end_date_active,sysdate+1));
1650 --Did not add the start date check above as this is setup and we might want to
1651 --define associations for counters that might get activated in future.
1652
1653 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1654 fnd_log.string(fnd_log.level_statement,l_full_name,'-- COUNTER_ID Validated Successfully--');
1655 END IF;
1656
1657 EXCEPTION
1658 WHEN NO_DATA_FOUND THEN
1659 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1660 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - COUNTER_ID -' || l_ra_def_dtl_rec.COUNTER_ID);
1661 END IF;
1662 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
1663 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.U_RA_DEF_DTL_DATA');
1664 FND_MSG_PUB.ADD;
1665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1666 END;
1667
1668 --MTBF should be >= 0 if not null
1669 IF(l_ra_def_dtl_rec.MTBF_VALUE IS NOT NULL AND l_ra_def_dtl_rec.MTBF_VALUE < 0) THEN
1670 FND_MESSAGE.Set_Name('AHL','AHL_RA_MTBF_INV');
1671 FND_MESSAGE.Set_Token('COUNTER',l_cou_name);
1672 FND_MESSAGE.Set_Token('MTBF', l_ra_def_dtl_rec.MTBF_VALUE);
1673 FND_MSG_PUB.ADD;
1674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1675 END IF;
1676
1677 -- Check for existence of record and fetch OVN of Master Rec for change record validation and bump up
1678 BEGIN
1679 SELECT OBJECT_VERSION_NUMBER
1680 INTO l_m_obj_version_num
1681 FROM AHL_RA_DEFINITION_HDR
1682 WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
1683 FOR UPDATE OF object_version_number NOWAIT;
1684
1685 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1686 fnd_log.string(fnd_log.level_statement,l_full_name,'-- OBJECT VERSION NUMBER IN DB OF MASTER: ' || l_m_obj_version_num);
1687 END IF;
1688
1689 EXCEPTION
1690 WHEN NO_DATA_FOUND THEN
1691 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1692 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Master DATA DOES NOT EXISTS -- ERROR ... ' || l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1693 END IF;
1694 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
1695 FND_MSG_PUB.ADD;
1696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697 END;
1698
1699 -- Validate input data in p_x_reliability_data_rec
1700 -- A. RECORD SHOULD NOT HAVE CHANGED. i.e. object_version_number should not change.
1701 -- Child Locking Check is implemented for Setup Data - When Child Is edited/inserted/deleted OVN of Master is bumped up
1702 IF l_m_obj_version_num <> l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER THEN
1703 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1704 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Master Record has changed : OVN passed : ' || l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER);
1705 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Master Record has changed : OVN in db : ' || l_m_obj_version_num);
1706 END IF;
1707 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1708 FND_MSG_PUB.ADD;
1709 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1710 END IF;
1711
1712 -- Increment object version number
1713 l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER := l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER + 1;
1714
1715 -- Intialize who column info
1716 l_ra_def_dtl_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
1717 l_ra_def_dtl_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
1718 l_ra_def_dtl_rec.LAST_UPDATE_DATE := sysdate;
1719 l_ra_def_dtl_rec.CREATED_BY := fnd_global.user_id;
1720 l_ra_def_dtl_rec.CREATION_DATE := sysdate;
1721
1722 UPDATE AHL_RA_DEFINITION_DTLS
1723 SET
1724 RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID,
1725 COUNTER_ID = l_ra_def_dtl_rec.COUNTER_ID,
1726 MTBF_VALUE = l_ra_def_dtl_rec.MTBF_VALUE,
1727 OBJECT_VERSION_NUMBER = l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER,
1728 SECURITY_GROUP_ID = l_ra_def_dtl_rec.SECURITY_GROUP_ID,
1729 ATTRIBUTE_CATEGORY = l_ra_def_dtl_rec.ATTRIBUTE_CATEGORY,
1730 ATTRIBUTE1 = l_ra_def_dtl_rec.ATTRIBUTE1,
1731 ATTRIBUTE2 = l_ra_def_dtl_rec.ATTRIBUTE2,
1732 ATTRIBUTE3 = l_ra_def_dtl_rec.ATTRIBUTE3,
1733 ATTRIBUTE4 = l_ra_def_dtl_rec.ATTRIBUTE4,
1734 ATTRIBUTE5 = l_ra_def_dtl_rec.ATTRIBUTE5,
1735 ATTRIBUTE6 = l_ra_def_dtl_rec.ATTRIBUTE6,
1736 ATTRIBUTE7 = l_ra_def_dtl_rec.ATTRIBUTE7,
1737 ATTRIBUTE8 = l_ra_def_dtl_rec.ATTRIBUTE8,
1738 ATTRIBUTE9 = l_ra_def_dtl_rec.ATTRIBUTE9,
1739 ATTRIBUTE10 = l_ra_def_dtl_rec.ATTRIBUTE10,
1740 ATTRIBUTE11 = l_ra_def_dtl_rec.ATTRIBUTE11,
1741 ATTRIBUTE12 = l_ra_def_dtl_rec.ATTRIBUTE12,
1742 ATTRIBUTE13 = l_ra_def_dtl_rec.ATTRIBUTE13,
1743 ATTRIBUTE14 = l_ra_def_dtl_rec.ATTRIBUTE14,
1744 ATTRIBUTE15 = l_ra_def_dtl_rec.ATTRIBUTE15,
1745 CREATION_DATE = l_ra_def_dtl_rec.CREATION_DATE,
1746 CREATED_BY = l_ra_def_dtl_rec.CREATED_BY,
1747 LAST_UPDATE_DATE = l_ra_def_dtl_rec.LAST_UPDATE_DATE,
1748 LAST_UPDATED_BY = l_ra_def_dtl_rec.LAST_UPDATED_BY,
1749 LAST_UPDATE_LOGIN = l_ra_def_dtl_rec.LAST_UPDATE_LOGIN
1750 WHERE
1751 RA_DEFINITION_DTL_ID = l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID;
1752
1753 -- Set the Out Param
1754 p_x_mtbf_data_rec := l_ra_def_dtl_rec;
1755
1756 l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER := l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER + 1;
1757
1758 -- Update Object Version Number of Master Record are Inserting child record.
1759 UPDATE AHL_RA_DEFINITION_HDR
1760 SET OBJECT_VERSION_NUMBER = l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER
1761 WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID;
1762
1763 p_x_reliability_data_rec := l_ra_def_hdr_rec;
1764
1765 -- Standard check for p_commit
1766 IF FND_API.To_Boolean (p_commit) THEN
1767 COMMIT;
1768 END IF;
1769
1770 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1771 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- UPDATE_MTBF_DATA -------END-----------');
1772 END IF;
1773
1774 -- Standard call to get message count and if count is 1, get message
1775 FND_MSG_PUB.Count_And_Get
1776 ( p_count => x_msg_count,
1777 p_data => x_msg_data,
1778 p_encoded => fnd_api.g_false);
1779
1780 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1781 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
1782 END IF;
1783
1784 EXCEPTION
1785 WHEN FND_API.G_EXC_ERROR THEN
1786 x_return_status := FND_API.G_RET_STS_ERROR;
1787 Rollback to UPDATE_MTBF_DATA_SP;
1788 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1789 p_data => x_msg_data,
1790 p_encoded => fnd_api.g_false);
1791
1792 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1794 Rollback to UPDATE_MTBF_DATA_SP;
1795 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1796 p_data => x_msg_data,
1797 p_encoded => fnd_api.g_false);
1798
1799 WHEN OTHERS THEN
1800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1801 Rollback to UPDATE_MTBF_DATA_SP;
1802 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1803 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1804 p_procedure_name => 'UPDATE_MTBF_DATA',
1805 p_error_text => SUBSTR(SQLERRM,1,240));
1806 END IF;
1807 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1808 p_data => x_msg_data,
1809 p_encoded => fnd_api.g_false);
1810
1811 END UPDATE_MTBF_DATA;
1812
1813
1814 -- Start of Comments --
1815 --
1816 -- Procedure name : DELETE_MTBF_DATA
1817 -- Type : Private
1818 -- Function : This API would delete the setup data for Reliability Framework in AHL_RA_DEFINITION_DTLS
1819 -- Pre-reqs :
1820 --
1821 -- Standard IN Parameters :
1822 -- p_api_version IN NUMBER Required
1823 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1824 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1825 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1826 --
1827 -- Standard OUT Parameters :
1828 -- x_return_status OUT VARCHAR2 Required
1829 -- x_msg_count OUT NUMBER Required
1830 -- x_msg_data OUT VARCHAR2 Required
1831 --
1832 -- DELETE_MTBF_DATA Parameters :
1833 -- p_mtbf_data_rec IN OUT RA_DEFINITION_DTLS_REC_TYPE Required
1834 --
1835 -- Version :
1836 -- Initial Version 1.0
1837 --
1838 -- End of Comments --
1839 PROCEDURE DELETE_MTBF_DATA (
1840 p_api_version IN NUMBER,
1841 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1842 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1843 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1844 p_module_type IN VARCHAR2,
1845 x_return_status OUT NOCOPY VARCHAR2,
1846 x_msg_count OUT NOCOPY NUMBER,
1847 x_msg_data OUT NOCOPY VARCHAR2,
1848 p_x_reliability_data_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE,
1849 p_mtbf_data_rec IN AHL_RA_SETUPS_PVT.RA_DEFINITION_DTLS_REC_TYPE) IS
1850
1851 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_MTBF_DATA';
1852 l_api_version CONSTANT NUMBER := 1.0;
1853 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
1854
1855 l_obj_version_num AHL_RA_CTR_ASSOCIATIONS.OBJECT_VERSION_NUMBER%TYPE;
1856 l_ra_def_hdr_rec AHL_RA_SETUPS_PVT.RA_DEFINITION_HDR_REC_TYPE DEFAULT p_x_reliability_data_rec;
1857 l_ra_def_dtl_rec AHL_RA_SETUPS_PVT.RA_DEFINITION_DTLS_REC_TYPE DEFAULT p_mtbf_data_rec;
1858 l_m_obj_version_num AHL_RA_DEFINITION_HDR.OBJECT_VERSION_NUMBER%TYPE;
1859
1860 BEGIN
1861
1862 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
1863 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
1864 END IF;
1865
1866 -- Standard start of API savepoint
1867 SAVEPOINT DELETE_MTBF_DATA_SP;
1868
1869 -- Standard call to check for call compatibility
1870 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1872 END IF;
1873
1874 -- Initialize message list if p_init_msg_list is set to TRUE
1875 IF FND_API.To_Boolean(p_init_msg_list) THEN
1876 FND_MSG_PUB.Initialize;
1877 END IF;
1878
1879 x_return_status := FND_API.G_RET_STS_SUCCESS;
1880
1881 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1882 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_MTBF_DATA -------BEGIN-----------');
1883 END IF;
1884
1885 --Validate the Operation Flag
1886 IF ((l_ra_def_dtl_rec.OPERATION_FLAG IS NULL) OR (l_ra_def_dtl_rec.OPERATION_FLAG <> G_DML_DELETE)) THEN
1887 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1888 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
1889 FND_MESSAGE.Set_Name('AHL','AHL_COM_INVALID_DML_REC');
1890 FND_MESSAGE.Set_Token('FIELD',l_ra_def_dtl_rec.OPERATION_FLAG);
1891 FND_MSG_PUB.ADD;
1892 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1893 END IF;
1894 END IF;
1895
1896 -- Validate input data in l_ra_def_dtl_rec
1897 -- A. l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID Cannot be NULL
1898 -- C. Object Version Number should not be NULL
1899 IF ((l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID IS NULL) OR
1900 (l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER IS NULL) OR
1901 (l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID is null AND l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID is null))THEN
1902 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1903 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- RA_DEFINITION_DTL_ID :' || l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID);
1904 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- OVN :' || l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER);
1905 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- Master RA_DEFINITION_HDR_ID :' || l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID);
1906 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- Detail RA_DEFINITION_HDR_ID:' || l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1907 END IF;
1908 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
1909 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.D_RA_DEF_DET_DATA');
1910 FND_MSG_PUB.ADD;
1911 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1912 END IF;
1913
1914 --Check that both the HDR_ID is same in both the master and child records.
1915 IF ((l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID is not null AND l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID IS NOT NULL) AND
1916 (l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID <> l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID)) THEN
1917 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1918 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - RA_DEFINITION_HDR_ID MASTER-'||l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID);
1919 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - RA_DEFINITION_HDR_ID CHILD-'||l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1920 END IF;
1921 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
1922 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.D_RA_DEF_DTL_DATA');
1923 FND_MSG_PUB.ADD;
1924 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1925 END IF;
1926
1927 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1928 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Default RA_DEFINITION_HDR_ID in child rec from header rec and vice-versa- if passed as null. -- ');
1929 END IF;
1930
1931 -- Default RA_DEFINITION_HDR_ID in child rec from header rec - if passed as null.
1932 IF l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID IS NULL THEN
1933 l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID := l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID;
1934 ELSE
1935 l_ra_def_hdr_rec.RA_DEFINITION_HDR_ID := l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID;
1936 END IF;
1937
1938 -- Check for existence of record and fetch OVN for change record validation
1939 BEGIN
1940 SELECT OBJECT_VERSION_NUMBER
1941 INTO l_obj_version_num
1942 FROM AHL_RA_DEFINITION_DTLS
1943 WHERE RA_DEFINITION_DTL_ID = l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID
1944 FOR UPDATE OF object_version_number NOWAIT;
1945
1946 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1947 fnd_log.string(fnd_log.level_statement,l_full_name,'-- OBJECT VERSION NUMBER IN DB : ' || l_obj_version_num);
1948 END IF;
1949
1950 EXCEPTION
1951 WHEN NO_DATA_FOUND THEN
1952 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1953 fnd_log.string(fnd_log.level_statement,l_full_name,'-- DATA DOES NOT EXISTS -- ERROR ... ' || l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID);
1954 END IF;
1955 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
1956 FND_MSG_PUB.ADD;
1957 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1958 END;
1959
1960 -- Validate input data in l_ra_def_dtl_rec
1961 -- A. RECORD MUST NOT HAVE CHANGED. i.e. object_version_number should not change.
1962 IF l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER <> l_obj_version_num THEN
1963 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1964 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN passed : ' || l_ra_def_dtl_rec.OBJECT_VERSION_NUMBER);
1965 END IF;
1966 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1967 FND_MSG_PUB.ADD;
1968 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1969 END IF;
1970
1971 -- Check for existence of record and fetch OVN of Master Rec for change record validation and bump up
1972 BEGIN
1973 SELECT OBJECT_VERSION_NUMBER
1974 INTO l_m_obj_version_num
1975 FROM AHL_RA_DEFINITION_HDR
1976 WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID
1977 FOR UPDATE OF object_version_number nowait;
1978
1979 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1980 fnd_log.string(fnd_log.level_statement,l_full_name,'-- OBJECT VERSION NUMBER IN DB OF MASTER: ' || l_m_obj_version_num);
1981 END IF;
1982
1983 EXCEPTION
1984 WHEN NO_DATA_FOUND THEN
1985 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1986 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Master DATA DOES NOT EXISTS -- ERROR ... ' || l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID);
1987 END IF;
1988 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
1989 FND_MSG_PUB.ADD;
1990 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1991 END;
1992
1993 -- Validate input data in p_x_reliability_data_rec
1994 -- A. RECORD SHOULD NOT HAVE CHANGED. i.e. object_version_number should not change.
1995 -- Child Locking Check is implemented for Setup Data - When Child Is edited/inserted/deleted OVN of Master is bumped up
1996 IF l_m_obj_version_num <> l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER THEN
1997 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1998 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Master Record has changed : OVN passed : ' || l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER);
1999 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Master Record has changed : OVN in db : ' || l_m_obj_version_num);
2000 END IF;
2001 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
2002 FND_MSG_PUB.ADD;
2003 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2004 END IF;
2005
2006 -- Delete Record from AHL_RA_DEFINITION_DTLS
2007 DELETE AHL_RA_DEFINITION_DTLS
2008 WHERE RA_DEFINITION_DTL_ID = l_ra_def_dtl_rec.RA_DEFINITION_DTL_ID;
2009
2010 l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER := l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER + 1;
2011
2012 -- Update Object Version Number of Master Record are Inserting child record.
2013 UPDATE AHL_RA_DEFINITION_HDR
2014 SET OBJECT_VERSION_NUMBER = l_ra_def_hdr_rec.OBJECT_VERSION_NUMBER
2015 WHERE RA_DEFINITION_HDR_ID = l_ra_def_dtl_rec.RA_DEFINITION_HDR_ID;
2016
2017 p_x_reliability_data_rec := l_ra_def_hdr_rec;
2018
2019 -- Standard check for p_commit
2020 IF FND_API.To_Boolean (p_commit) THEN
2021 COMMIT;
2022 END IF;
2023
2024 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2025 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_MTBF_DATA -------END-----------');
2026 END IF;
2027
2028 -- Standard call to get message count and if count is 1, get message
2029 FND_MSG_PUB.Count_And_Get
2030 ( p_count => x_msg_count,
2031 p_data => x_msg_data,
2032 p_encoded => fnd_api.g_false);
2033
2034 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2035 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
2036 END IF;
2037
2038 EXCEPTION
2039 WHEN FND_API.G_EXC_ERROR THEN
2040 x_return_status := FND_API.G_RET_STS_ERROR;
2041 Rollback to DELETE_MTBF_DATA_SP;
2042 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2043 p_data => x_msg_data,
2044 p_encoded => fnd_api.g_false);
2045
2046 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2047 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2048 Rollback to DELETE_MTBF_DATA_SP;
2049 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2050 p_data => x_msg_data,
2051 p_encoded => fnd_api.g_false);
2052
2053 WHEN OTHERS THEN
2054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2055 Rollback to DELETE_MTBF_DATA_SP;
2056 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2057 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2058 p_procedure_name => 'DELETE_MTBF_DATA',
2059 p_error_text => SUBSTR(SQLERRM,1,240));
2060 END IF;
2061 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2062 p_data => x_msg_data,
2063 p_encoded => fnd_api.g_false);
2064
2065 END DELETE_MTBF_DATA;
2066
2067
2068
2069 -- Start of Comments --
2070 --
2071 -- Procedure name : CREATE_COUNTER_ASSOC
2072 -- Type : Private
2073 -- Function : This API would create the setup data for Reliability Framework in AHL_RA_CTR_ASSOCIATIONS
2074 -- Pre-reqs :
2075 --
2076 -- Standard IN Parameters :
2077 -- p_api_version IN NUMBER Required
2078 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2079 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2080 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2081 --
2082 -- Standard OUT Parameters :
2083 -- x_return_status OUT VARCHAR2 Required
2084 -- x_msg_count OUT NUMBER Required
2085 -- x_msg_data OUT VARCHAR2 Required
2086 --
2087 -- CREATE_COUNTER_ASSOC Parameters :
2088 -- p_x_counter_assoc_rec IN OUT RA_COUNTER_ASSOC_REC_TYPE Required
2089 --
2090 -- Version :
2091 -- Initial Version 1.0
2092 --
2093 -- End of Comments --
2094 PROCEDURE CREATE_COUNTER_ASSOC (
2095 p_api_version IN NUMBER,
2096 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2097 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2098 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2099 p_module_type IN VARCHAR2,
2100 x_return_status OUT NOCOPY VARCHAR2,
2101 x_msg_count OUT NOCOPY NUMBER,
2102 x_msg_data OUT NOCOPY VARCHAR2,
2103 p_x_counter_assoc_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_COUNTER_ASSOC_REC_TYPE) IS
2104
2105 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_COUNTER_ASSOC';
2106 l_api_version CONSTANT NUMBER := 1.0;
2107 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
2108
2109 l_counter_assoc_rec AHL_RA_SETUPS_PVT.RA_COUNTER_ASSOC_REC_TYPE DEFAULT p_x_counter_assoc_rec;
2110 l_dummy VARCHAR2(1);
2111 l_new_cou_name CSI_COUNTER_TEMPLATE_VL.NAME%TYPE := NULL;
2112 l_overhaul_cou_name CSI_COUNTER_TEMPLATE_VL.NAME%TYPE := NULL;
2113 l_new_cou_uom CSI_COUNTER_TEMPLATE_VL.UOM_CODE%TYPE := NULL;
2114 l_overhaul_cou_uom CSI_COUNTER_TEMPLATE_VL.UOM_CODE%TYPE := NULL;
2115
2116 BEGIN
2117
2118 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2119 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
2120 END IF;
2121
2122 -- Standard start of API savepoint
2123 SAVEPOINT CREATE_COUNTER_ASSOC_SP;
2124
2125 -- Standard call to check for call compatibility
2126 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2128 END IF;
2129
2130 -- Initialize message list if p_init_msg_list is set to TRUE
2131 IF FND_API.To_Boolean(p_init_msg_list) THEN
2132 FND_MSG_PUB.Initialize;
2133 END IF;
2134
2135 x_return_status := FND_API.G_RET_STS_SUCCESS;
2136
2137 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2138 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_COUNTER_ASSOC -------BEGIN-----------');
2139 END IF;
2140
2141
2142 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2143 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Parameters Passed --');
2144 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_counter_assoc_rec.OPERATION_FLAG -- '||l_counter_assoc_rec.OPERATION_FLAG);
2145 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_counter_assoc_rec.SINCE_NEW_COUNTER_ID -- '||l_counter_assoc_rec.SINCE_NEW_COUNTER_ID);
2146 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID -- '||l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID);
2147 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_counter_assoc_rec.DESCRIPTION -- '||l_counter_assoc_rec.DESCRIPTION);
2148 END IF;
2149
2150 IF ((l_counter_assoc_rec.OPERATION_FLAG IS NULL) OR (l_counter_assoc_rec.OPERATION_FLAG <> G_DML_CREATE)) THEN
2151 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2152 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
2153 END IF;
2154 FND_MESSAGE.Set_Name('AHL','AHL_COM_INVALID_DML_REC');
2155 FND_MESSAGE.Set_Token('FIELD',l_counter_assoc_rec.OPERATION_FLAG);
2156 FND_MSG_PUB.ADD;
2157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2158 END IF;
2159
2160 --Mandatory validation for the Since New Counter
2161 IF(l_counter_assoc_rec.SINCE_NEW_COUNTER_ID is null) THEN
2162 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
2163 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_COU_DATA');
2164 FND_MSG_PUB.ADD;
2165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2166 END IF;
2167
2168
2169 --Validate value of the since new counter passed
2170 BEGIN
2171 -- Bug 4913954 : Perf Fixes.
2172 -- Since we are using reference to cs_counter_groups with a join condition TEMPLATE_FLAG = 'Y'
2173 -- i.e only counter templates, direct reference to cs_csi_counter_groups can be used here.
2174 SELECT templates.name,
2175 templates.uom_code
2176 INTO l_new_cou_name,
2177 l_new_cou_uom
2178 FROM cs_csi_counter_groups cg,
2179 csi_counter_template_vl templates
2180 WHERE templates.counter_id = l_counter_assoc_rec.SINCE_NEW_COUNTER_ID
2181 AND cg.template_flag = 'Y'
2182 AND templates.group_id = cg.counter_group_id
2183 AND trunc(sysdate) < trunc(nvl(templates.end_date_active,sysdate+1));
2184 --Did not add the start date check above as this is setup and we might want to
2185 --define associations for counters that might get activated in future.
2186
2187 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2188 fnd_log.string(fnd_log.level_statement,l_full_name,'-- SINCE_NEW_COUNTER_ID Validated Successfully--');
2189 END IF;
2190
2191 EXCEPTION
2192 WHEN NO_DATA_FOUND THEN
2193 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2194 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - SINCE_NEW_COUNTER_ID -' || l_counter_assoc_rec.SINCE_NEW_COUNTER_ID);
2195 END IF;
2196 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
2197 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_COU_DATA');
2198 FND_MSG_PUB.ADD;
2199 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2200 END;
2201
2202 --Validate Since overhaul counter id passed
2203 IF(l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID IS NOT NULL) THEN
2204 BEGIN
2205 -- Bug 4913954 : Perf Fixes.
2206 -- Since we are using reference to cs_counter_groups with a join condition TEMPLATE_FLAG = 'Y'
2207 -- i.e only counter templates, direct reference to cs_csi_counter_groups can be used here.
2208 SELECT templates.name,
2209 templates.uom_code
2210 INTO l_overhaul_cou_name,
2211 l_overhaul_cou_uom
2212 FROM cs_csi_counter_groups cg,
2213 csi_counter_template_vl templates
2214 WHERE templates.counter_id = l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID
2215 AND cg.template_flag = 'Y'
2216 AND templates.group_id = cg.counter_group_id
2217 AND trunc(sysdate) < trunc(nvl(templates.end_date_active,sysdate+1));
2218 --Did not add the start date check above as this is setup and we might want to
2219 --define associations for counters that might get activated in future.
2220
2221 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2222 fnd_log.string(fnd_log.level_statement,l_full_name,'-- SINCE_OVERHAUL_COUNTER_ID Validated Successfully--');
2223 END IF;
2224
2225 EXCEPTION
2226 WHEN NO_DATA_FOUND THEN
2227 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2228 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - SINCE_OVERHAUL_COUNTER_ID -' || l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID);
2229 END IF;
2230 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
2231 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_COU_DATA');
2232 FND_MSG_PUB.ADD;
2233 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2234 END;
2235 END IF;
2236
2237
2238 --A since counter cannot be declared new and overhaul at the same time
2239 IF(l_counter_assoc_rec.SINCE_NEW_COUNTER_ID IS NOT NULL AND l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID IS NOT NULL) THEN
2240 IF(l_counter_assoc_rec.SINCE_NEW_COUNTER_ID = l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID) THEN
2241 FND_MESSAGE.Set_Name('AHL','AHL_RA_NEW_OHAUL_COU_SAME');
2242 FND_MESSAGE.Set_Token('COUNTER', l_new_cou_name);
2243 FND_MSG_PUB.ADD;
2244 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2245 END IF;
2246 END IF;
2247
2248 --UOM of Since New and Since Overhaul Counters should be same
2249 --The check below makes sense only when there is a overhaul counter defined in the association.
2250 IF(l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID IS NOT NULL) THEN
2251 IF(l_new_cou_uom IS NOT NULL and l_overhaul_cou_uom IS NOT NULL) THEN
2252 IF(l_new_cou_uom <> l_overhaul_cou_uom) THEN
2253 FND_MESSAGE.Set_Name('AHL','AHL_RA_NEW_OHAUL_UOM_DIFF');
2254 FND_MESSAGE.Set_Token('NEW_COUNTER', l_new_cou_name);
2255 FND_MESSAGE.Set_Token('OHAUL_COUNTER', l_overhaul_cou_name);
2256 FND_MESSAGE.Set_Token('NEW_COU_UOM', l_new_cou_uom);
2257 FND_MESSAGE.Set_Token('OHAUL_COU_UOM', l_overhaul_cou_uom);
2258 FND_MSG_PUB.ADD;
2259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2260 END IF;
2261 END IF;
2262 END IF;
2263
2264 --A since New Counter should not have been previously declared as overhaul counter.
2265 BEGIN
2266 SELECT 'Y'
2267 INTO l_dummy
2268 FROM dual
2269 WHERE EXISTS(SELECT 'X'
2270 FROM AHL_RA_CTR_ASSOCIATIONS
2271 WHERE since_overhaul_counter_id = l_counter_assoc_rec.SINCE_NEW_COUNTER_ID);
2272
2273 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2274 fnd_log.string(fnd_log.level_statement,l_full_name,'Validation Failure: This since new counter is already declared as overhaul - SINCE_NEW_COUNTER_ID -' || l_counter_assoc_rec.SINCE_NEW_COUNTER_ID);
2275 END IF;
2276
2277 FND_MESSAGE.Set_Name('AHL','AHL_RA_NEW_COU_DEF_OHAUL');
2278 FND_MESSAGE.Set_Token('COUNTER', l_new_cou_name);
2279 FND_MSG_PUB.ADD;
2280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2281
2282 EXCEPTION
2283 WHEN NO_DATA_FOUND THEN
2284 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2285 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation Sucess: This since new counter is not declared as an overhaul counter already --' || l_counter_assoc_rec.SINCE_NEW_COUNTER_ID);
2286 END IF;
2287 END;
2288
2289 --A since Overhaul Counter should not have been declared previously as a new counter.
2290 IF(l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID IS NOT NULL) THEN
2291 BEGIN
2292 SELECT 'Y'
2293 INTO l_dummy
2294 FROM dual
2295 WHERE EXISTS(SELECT 'X'
2296 FROM AHL_RA_CTR_ASSOCIATIONS
2297 WHERE since_new_counter_id = l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID);
2298
2299 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2300 fnd_log.string(fnd_log.level_statement,l_full_name,'--Validation Failure: This since new counter is already declared as overhaul - SINCE_OVERHAUL_COUNTER_ID -' || l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID);
2301 END IF;
2302
2303 FND_MESSAGE.Set_Name('AHL','AHL_RA_OHAUL_COU_DEF_NEW');
2304 FND_MESSAGE.Set_Token('COUNTER', l_overhaul_cou_name);
2305 FND_MSG_PUB.ADD;
2306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2307
2308 EXCEPTION
2309 WHEN NO_DATA_FOUND THEN
2310 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2311 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation Sucess: This since ohaul counter is not declared as a new counter previously --' || l_counter_assoc_rec.SINCE_NEW_COUNTER_ID);
2312 END IF;
2313 END;
2314 END IF;
2315
2316
2317 --Duplicate record validation
2318 BEGIN
2319 SELECT 'Y'
2320 INTO l_dummy
2321 FROM dual
2322 WHERE EXISTS(SELECT 'X'
2323 FROM AHL_RA_CTR_ASSOCIATIONS counters
2324 WHERE SINCE_NEW_COUNTER_ID = l_counter_assoc_rec.SINCE_NEW_COUNTER_ID
2325 AND nvl(SINCE_OVERHAUL_COUNTER_ID,-1) = nvl(l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID,-1));
2326
2327 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2328 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation Failuure: Duplicate Counter Association exists-- SINCE_NEW_COUNTER_NAME - '|| l_new_cou_name ||'- SINCE_OVERHAUL_COUNTER_NAME -' || l_overhaul_cou_name);
2329 END IF;
2330
2331 FND_MESSAGE.Set_Name('AHL','AHL_RA_DUP_COU_ASSOC');
2332 FND_MESSAGE.Set_Token('NEW_COUNTER',l_new_cou_name );
2333 FND_MESSAGE.Set_Token('OHAUL_COUNTER', l_overhaul_cou_name);
2334 FND_MSG_PUB.ADD;
2335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2336
2337 EXCEPTION
2338 WHEN NO_DATA_FOUND THEN
2339 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2340 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Validation Successful: No Duplicate Counter Associations--');
2341 END IF;
2342 END;
2343
2344 -- Initialize RA_COUNTER_ASSOCIATION_ID to sequence next val for insert
2345 SELECT AHL_RA_CTR_ASSOCIATIONS_S.NEXTVAL into l_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID from dual;
2346
2347 -- Initialize object version number to 1
2348 l_counter_assoc_rec.OBJECT_VERSION_NUMBER := 1;
2349
2350 -- Intialize who column info
2351 l_counter_assoc_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
2352 l_counter_assoc_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
2353 l_counter_assoc_rec.CREATED_BY := fnd_global.user_id;
2354 l_counter_assoc_rec.CREATION_DATE := sysdate;
2355 l_counter_assoc_rec.LAST_UPDATE_DATE := sysdate;
2356
2357 -- Initialize security group id
2358 l_counter_assoc_rec.SECURITY_GROUP_ID := null;
2359
2360 --Insert the record into AHL_RA_CTR_ASSOCIATIONS
2361 INSERT INTO AHL_RA_CTR_ASSOCIATIONS
2362 (
2363 RA_COUNTER_ASSOCIATION_ID,
2364 SINCE_NEW_COUNTER_ID,
2365 SINCE_OVERHAUL_COUNTER_ID,
2366 DESCRIPTION,
2367 OBJECT_VERSION_NUMBER,
2368 SECURITY_GROUP_ID,
2369 CREATION_DATE,
2370 CREATED_BY,
2371 LAST_UPDATE_DATE,
2372 LAST_UPDATED_BY,
2373 LAST_UPDATE_LOGIN,
2374 ATTRIBUTE_CATEGORY,
2375 ATTRIBUTE1,
2376 ATTRIBUTE2,
2377 ATTRIBUTE3,
2378 ATTRIBUTE4,
2379 ATTRIBUTE5,
2380 ATTRIBUTE6,
2381 ATTRIBUTE7,
2382 ATTRIBUTE8,
2383 ATTRIBUTE9,
2384 ATTRIBUTE10,
2385 ATTRIBUTE11,
2386 ATTRIBUTE12,
2387 ATTRIBUTE13,
2388 ATTRIBUTE14,
2389 ATTRIBUTE15
2390 )
2391 VALUES
2392 (
2393 l_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID,
2394 l_counter_assoc_rec.SINCE_NEW_COUNTER_ID,
2395 l_counter_assoc_rec.SINCE_OVERHAUL_COUNTER_ID,
2396 l_counter_assoc_rec.DESCRIPTION,
2397 l_counter_assoc_rec.OBJECT_VERSION_NUMBER,
2398 l_counter_assoc_rec.SECURITY_GROUP_ID,
2399 l_counter_assoc_rec.CREATION_DATE,
2400 l_counter_assoc_rec.CREATED_BY,
2401 l_counter_assoc_rec.LAST_UPDATE_DATE,
2402 l_counter_assoc_rec.LAST_UPDATED_BY,
2403 l_counter_assoc_rec.LAST_UPDATE_LOGIN,
2404 l_counter_assoc_rec.ATTRIBUTE_CATEGORY,
2405 l_counter_assoc_rec.ATTRIBUTE1,
2406 l_counter_assoc_rec.ATTRIBUTE2,
2407 l_counter_assoc_rec.ATTRIBUTE3,
2408 l_counter_assoc_rec.ATTRIBUTE4,
2409 l_counter_assoc_rec.ATTRIBUTE5,
2410 l_counter_assoc_rec.ATTRIBUTE6,
2411 l_counter_assoc_rec.ATTRIBUTE7,
2412 l_counter_assoc_rec.ATTRIBUTE8,
2413 l_counter_assoc_rec.ATTRIBUTE9,
2414 l_counter_assoc_rec.ATTRIBUTE10,
2415 l_counter_assoc_rec.ATTRIBUTE11,
2416 l_counter_assoc_rec.ATTRIBUTE12,
2417 l_counter_assoc_rec.ATTRIBUTE13,
2418 l_counter_assoc_rec.ATTRIBUTE14,
2419 l_counter_assoc_rec.ATTRIBUTE15
2420 );
2421
2422 -- Set the Out Param
2423 p_x_counter_assoc_rec := l_counter_assoc_rec;
2424
2425 -- Standard check for p_commit
2426 IF FND_API.To_Boolean (p_commit) THEN
2427 COMMIT;
2428 END IF;
2429
2430
2431 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2432 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_COUNTER_ASSOC --END--');
2433 END IF;
2434
2435
2436 -- Standard call to get message count and if count is 1, get message
2437 FND_MSG_PUB.Count_And_Get
2438 ( p_count => x_msg_count,
2439 p_data => x_msg_data,
2440 p_encoded => fnd_api.g_false);
2441
2442 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2443 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
2444 END IF;
2445
2446 EXCEPTION
2447 WHEN FND_API.G_EXC_ERROR THEN
2448 x_return_status := FND_API.G_RET_STS_ERROR;
2449 Rollback to CREATE_COUNTER_ASSOC_SP;
2450 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2451 p_data => x_msg_data,
2452 p_encoded => fnd_api.g_false);
2453
2454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2456 Rollback to CREATE_COUNTER_ASSOC_SP;
2457 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2458 p_data => x_msg_data,
2459 p_encoded => fnd_api.g_false);
2460
2461 WHEN OTHERS THEN
2462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2463 Rollback to CREATE_COUNTER_ASSOC_SP;
2464 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2465 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2466 p_procedure_name => 'CREATE_COUNTER_ASSOC',
2467 p_error_text => SUBSTR(SQLERRM,1,240));
2468 END IF;
2469 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2470 p_data => x_msg_data,
2471 p_encoded => fnd_api.g_false);
2472
2473 END CREATE_COUNTER_ASSOC;
2474
2475 -- Start of Comments --
2476 --
2477 -- Procedure name : DELETE_COUNTER_ASSOC
2478 -- Type : Private
2479 -- Function : This API would delete the setup data for Reliability Framework in AHL_RA_CTR_ASSOCIATIONS
2480 -- Pre-reqs :
2481 --
2482 -- Standard IN Parameters :
2483 -- p_api_version IN NUMBER Required
2484 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2485 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2486 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2487 --
2488 -- Standard OUT Parameters :
2489 -- x_return_status OUT VARCHAR2 Required
2490 -- x_msg_count OUT NUMBER Required
2491 -- x_msg_data OUT VARCHAR2 Required
2492 --
2493 -- DELETE_COUNTER_ASSOC Parameters :
2494 -- p_counter_assoc_rec IN OUT RA_COUNTER_ASSOC_REC_TYPE Required
2495 --
2496 -- Version :
2497 -- Initial Version 1.0
2498 --
2499 -- End of Comments --
2500 PROCEDURE DELETE_COUNTER_ASSOC (
2501 p_api_version IN NUMBER,
2502 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2503 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2504 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2505 p_module_type IN VARCHAR2,
2506 x_return_status OUT NOCOPY VARCHAR2,
2507 x_msg_count OUT NOCOPY NUMBER,
2508 x_msg_data OUT NOCOPY VARCHAR2,
2509 p_counter_assoc_rec IN AHL_RA_SETUPS_PVT.RA_COUNTER_ASSOC_REC_TYPE) IS
2510
2511 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_COUNTER_ASSOC';
2512 l_api_version CONSTANT NUMBER := 1.0;
2513 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
2514
2515 l_obj_version_num AHL_RA_CTR_ASSOCIATIONS.OBJECT_VERSION_NUMBER%TYPE;
2516
2517 BEGIN
2518
2519 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2520 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
2521 END IF;
2522
2523 -- Standard start of API savepoint
2524 SAVEPOINT DELETE_COUNTER_ASSOC_SP;
2525
2526 -- Standard call to check for call compatibility
2527 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2529 END IF;
2530
2531 -- Initialize message list if p_init_msg_list is set to TRUE
2532 IF FND_API.To_Boolean(p_init_msg_list) THEN
2533 FND_MSG_PUB.Initialize;
2534 END IF;
2535
2536 x_return_status := FND_API.G_RET_STS_SUCCESS;
2537
2538 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2539 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_COUNTER_ASSOC -------BEGIN-----------');
2540 END IF;
2541
2542 IF ((p_counter_assoc_rec.OPERATION_FLAG IS NULL) OR (p_counter_assoc_rec.OPERATION_FLAG <> G_DML_DELETE)) THEN
2543 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2544 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
2545 END IF;
2546 FND_MESSAGE.Set_Name('AHL','AHL_COM_INVALID_DML_REC');
2547 FND_MESSAGE.Set_Token('FIELD',p_counter_assoc_rec.OPERATION_FLAG);
2548 FND_MSG_PUB.ADD;
2549 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2550 END IF;
2551
2552 -- Validate input data in p_counter_assoc_rec
2553 -- A. p_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID Cannot be NULL
2554 -- C. Object Version Number should not be NULL
2555 IF ((p_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID IS NULL) OR
2556 (p_counter_assoc_rec.OBJECT_VERSION_NUMBER IS NULL))THEN
2557 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2558 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- RA_COUNTER_ASSOCIATION_ID :' || p_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID);
2559 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- OVN :' || p_counter_assoc_rec.OBJECT_VERSION_NUMBER);
2560 END IF;
2561 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
2562 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.D_COU_DATA');
2563 FND_MSG_PUB.ADD;
2564 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2565 END IF;
2566
2567 -- Check for existence of record and fetch OVN for change record validation
2568 BEGIN
2569 SELECT OBJECT_VERSION_NUMBER
2570 INTO l_obj_version_num
2571 FROM AHL_RA_CTR_ASSOCIATIONS
2572 WHERE RA_COUNTER_ASSOCIATION_ID = p_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID
2573 FOR UPDATE OF object_version_number NOWAIT;
2574
2575 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2576 fnd_log.string(fnd_log.level_statement,l_full_name,'-- OBJECT VERSION NUMBER IN DB : ' || l_obj_version_num);
2577 END IF;
2578
2579 EXCEPTION
2580 WHEN NO_DATA_FOUND THEN
2581 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2582 fnd_log.string(fnd_log.level_statement,l_full_name,'-- DATA DOES NOT EXISTS -- ERROR ... ' || p_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID);
2583 END IF;
2584 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
2585 FND_MSG_PUB.ADD;
2586 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2587 END;
2588
2589 -- Validate input data in p_counter_assoc_rec
2590 -- A. RECORD MUST NOT HAVE CHANGED. i.e. object_version_number should not change.
2591 IF p_counter_assoc_rec.OBJECT_VERSION_NUMBER <> l_obj_version_num THEN
2592 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2593 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN passed : ' || p_counter_assoc_rec.OBJECT_VERSION_NUMBER);
2594 END IF;
2595 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
2596 FND_MSG_PUB.ADD;
2597 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2598 END IF;
2599
2600 -- Delete Record from AHL_RA_CTR_ASSOCIATIONS
2601 DELETE AHL_RA_CTR_ASSOCIATIONS
2602 WHERE RA_COUNTER_ASSOCIATION_ID = p_counter_assoc_rec.RA_COUNTER_ASSOCIATION_ID;
2603
2604 -- Standard check for p_commit
2605 IF FND_API.To_Boolean (p_commit) THEN
2606 COMMIT;
2607 END IF;
2608
2609 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2610 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_COUNTER_ASSOC -------END-----------');
2611 END IF;
2612
2613 -- Standard call to get message count and if count is 1, get message
2614 FND_MSG_PUB.Count_And_Get
2615 ( p_count => x_msg_count,
2616 p_data => x_msg_data,
2617 p_encoded => fnd_api.g_false);
2618
2619 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2620 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','At the start of PLSQL procedure');
2621 END IF;
2622
2623 EXCEPTION
2624 WHEN FND_API.G_EXC_ERROR THEN
2625 x_return_status := FND_API.G_RET_STS_ERROR;
2626 Rollback to DELETE_COUNTER_ASSOC_SP;
2627 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2628 p_data => x_msg_data,
2629 p_encoded => fnd_api.g_false);
2630
2631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2633 Rollback to DELETE_COUNTER_ASSOC_SP;
2634 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2635 p_data => x_msg_data,
2636 p_encoded => fnd_api.g_false);
2637
2638 WHEN OTHERS THEN
2639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2640 Rollback to DELETE_COUNTER_ASSOC_SP;
2641 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2642 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2643 p_procedure_name => 'DELETE_COUNTER_ASSOC',
2644 p_error_text => SUBSTR(SQLERRM,1,240));
2645 END IF;
2646 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2647 p_data => x_msg_data,
2648 p_encoded => fnd_api.g_false);
2649
2650 END DELETE_COUNTER_ASSOC;
2651
2652 -- Start of Comments --
2653 --
2654 -- Procedure name : CREATE_FCT_ASSOC_DATA
2655 -- Type : Private
2656 -- Function : This API would create the setup data for Reliability Framework in AHL_RA_FCT_ASSOCIATIONS
2657 -- Pre-reqs :
2658 --
2659 -- Standard IN Parameters :
2660 -- p_api_version IN NUMBER Required
2661 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2662 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2663 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2664 --
2665 -- Standard OUT Parameters :
2666 -- x_return_status OUT VARCHAR2 Required
2667 -- x_msg_count OUT NUMBER Required
2668 -- x_msg_data OUT VARCHAR2 Required
2669 --
2670 -- CREATE_FCT_ASSOC_DATA Parameters :
2671 -- p_x_fct_assoc_rec IN OUT RA_FCT_ASSOC_REC_TYPE Required
2672 --
2673 -- Version :
2674 -- Initial Version 1.0
2675 --
2676 -- End of Comments --
2677 PROCEDURE CREATE_FCT_ASSOC_DATA (
2678 p_api_version IN NUMBER,
2679 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2680 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2681 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2682 p_module_type IN VARCHAR2,
2683 x_return_status OUT NOCOPY VARCHAR2,
2684 x_msg_count OUT NOCOPY NUMBER,
2685 x_msg_data OUT NOCOPY VARCHAR2,
2686 p_x_fct_assoc_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_FCT_ASSOC_REC_TYPE) IS
2687
2688 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_FCT_ASSOC_DATA';
2689 l_api_version CONSTANT NUMBER := 1.0;
2690 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
2691
2692 l_fct_assoc_rec AHL_RA_SETUPS_PVT.RA_FCT_ASSOC_REC_TYPE DEFAULT p_x_fct_assoc_rec;
2693 l_dummy VARCHAR2(1);
2694
2695 BEGIN
2696
2697 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2698 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
2699 END IF;
2700
2701 -- Standard start of API savepoint
2702 SAVEPOINT CREATE_FCT_ASSOC_DATA_SP;
2703
2704 -- Standard call to check for call compatibility
2705 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2707 END IF;
2708
2709 -- Initialize message list if p_init_msg_list is set to TRUE
2710 IF FND_API.To_Boolean(p_init_msg_list) THEN
2711 FND_MSG_PUB.Initialize;
2712 END IF;
2713
2714 x_return_status := FND_API.G_RET_STS_SUCCESS;
2715
2716 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2717 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_FCT_ASSOC_DATA -------BEGIN-----------');
2718 END IF;
2719
2720 -- Validate input fct association data in p_x_fct_assoc_rec
2721 -- Note :: l_fct_assoc_rec has been defaulted to p_x_fct_assoc_rec in declaration
2722 -- A. ASSOCIATION_TYPE_CODE Cannot be NULL
2723 -- B. ASSOCIATION_TYPE_CODE should be in ('ASSOC_HISTORICAL','ASSOC_MTBF')
2724 -- C. ORGANIZATION_ID cannot be NULL
2725 -- D. FORECAST_DESIGNATOR cannot be NULL
2726 -- E. If ASSOCIATION_TYPE_CODE = ASSOC_HISTORICAL then PROBABILITY_FROM and PROBABILITY_TO are mandatory.
2727 -- F. OPERATIONS_FLAG should be C
2728
2729 IF ((l_fct_assoc_rec.ASSOCIATION_TYPE_CODE IS NULL) OR
2730 (l_fct_assoc_rec.ASSOCIATION_TYPE_CODE NOT IN ('ASSOC_HISTORICAL','ASSOC_MTBF')) OR
2731 (l_fct_assoc_rec.ORGANIZATION_ID IS NULL) OR
2732 (l_fct_assoc_rec.FORECAST_DESIGNATOR IS NULL) OR
2733 ((l_fct_assoc_rec.OPERATION_FLAG IS NULL) OR (l_fct_assoc_rec.OPERATION_FLAG <> G_DML_CREATE))) THEN
2734
2735 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2736 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
2737 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.FORECAST_DESIGNATOR ---- '||l_fct_assoc_rec.FORECAST_DESIGNATOR);
2738 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.ORGANIZATION_ID -------- '||l_fct_assoc_rec.ORGANIZATION_ID);
2739 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.ASSOCIATION_TYPE_CODE -- '||l_fct_assoc_rec.ASSOCIATION_TYPE_CODE);
2740 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.OPERATION_FLAG --------- '||l_fct_assoc_rec.OPERATION_FLAG);
2741 END IF;
2742
2743 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
2744 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_FCT_DATA');
2745 FND_MSG_PUB.ADD;
2746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2747
2748 END IF;
2749
2750 IF (l_fct_assoc_rec.ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL' AND (l_fct_assoc_rec.PROBABILITY_FROM IS NULL OR
2751 l_fct_assoc_rec.PROBABILITY_TO IS NULL)) THEN
2752 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2753 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
2754 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_FROM ------- '||l_fct_assoc_rec.PROBABILITY_FROM);
2755 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_TO --------- '||l_fct_assoc_rec.PROBABILITY_TO);
2756 END IF;
2757
2758 FND_MESSAGE.Set_Name('AHL','AHL_RA_HIST_PROB_NULL');
2759 FND_MSG_PUB.ADD;
2760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2761
2762 END IF;
2763
2764
2765 -- Validate value of ORGANIZATION_ID passed
2766 -- Bug 4913954 : Perf Fix
2767 -- Removed non-required reference to ORG_ORGANIZATION_DEFINTIONS below
2768 -- See earlier Query in 120.11
2769 BEGIN
2770 SELECT 'Y'
2771 INTO l_dummy
2772 FROM DUAL
2773 WHERE EXISTS(SELECT 'X'
2774 FROM MTL_PARAMETERS MP
2775 WHERE MP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
2776 AND MP.EAM_ENABLED_FLAG='Y');
2777
2778 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2779 fnd_log.string(fnd_log.level_statement,l_full_name,'-- ORGANIZATION_ID Validated Successfully--');
2780 END IF;
2781
2782
2783 EXCEPTION
2784 WHEN NO_DATA_FOUND THEN
2785 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2786 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - ORGANIZATION_ID -' || l_fct_assoc_rec.ORGANIZATION_ID);
2787 END IF;
2788 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
2789 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_FCT_DATA');
2790 FND_MSG_PUB.ADD;
2791 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
2792 END;
2793
2794 -- Validate value of FORECAST_DESIGNATOR passed
2795 BEGIN
2796 SELECT 'Y'
2797 INTO l_dummy
2798 FROM DUAL
2799 WHERE EXISTS(SELECT 'X'
2800 FROM mrp_forecast_designators_v MRP
2801 WHERE MRP.FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
2802 AND MRP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
2803 AND MRP.FORECAST_SET IS NOT NULL);
2804
2805 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2806 fnd_log.string(fnd_log.level_statement,l_full_name,'-- FORECAST_DESIGNATOR Validated Successfully--');
2807 END IF;
2808
2809
2810 EXCEPTION
2811 WHEN NO_DATA_FOUND THEN
2812 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2813 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - FORECAST_DESIGNATOR -' || l_fct_assoc_rec.FORECAST_DESIGNATOR);
2814 END IF;
2815 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
2816 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.C_FCT_DATA');
2817 FND_MSG_PUB.ADD;
2818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2819 END;
2820
2821
2822 -- When ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL'
2823 -- Validate PROBABILITY_FROM and PROBABILITY_TO passed
2824 -- 1. Neither PROBABILITY_FROM nor PROBABILITY_TO can be passed less than zero or greater than 100
2825 -- 2. PROBABILITY_TO should be >= PROBABILITY_FROM
2826 -- 3. records with Overlapping values of probablities should not exist for the Org Id and forecast designator in AHL_RA_FCT_ASSOCIATIONS
2827 IF (l_fct_assoc_rec.ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL') THEN
2828
2829 -- 1. Neither PROBABILITY_FROM nor PROBABILITY_TO can be passed less than zero or greater than 100
2830 IF (l_fct_assoc_rec.PROBABILITY_FROM < 0 OR
2831 l_fct_assoc_rec.PROBABILITY_FROM > 100 OR
2832 l_fct_assoc_rec.PROBABILITY_TO < 0 OR
2833 l_fct_assoc_rec.PROBABILITY_TO > 100) THEN
2834
2835 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2836 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - less than zero or greater than 100 -');
2837 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_FROM ------- '||l_fct_assoc_rec.PROBABILITY_FROM);
2838 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_TO --------- '||l_fct_assoc_rec.PROBABILITY_TO);
2839 END IF;
2840
2841 FND_MESSAGE.Set_Name('AHL','AHL_RA_PROB_VALID_RANGE');
2842 FND_MSG_PUB.ADD;
2843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2844 END IF;
2845
2846 -- 2. PROBABILITY_TO should be >= PROBABILITY_FROM
2847 IF (NOT(l_fct_assoc_rec.PROBABILITY_FROM <= l_fct_assoc_rec.PROBABILITY_TO)) THEN
2848
2849 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2850 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - PROBABILITY_TO should be >= PROBABILITY_FROM -');
2851 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_FROM ------- '||l_fct_assoc_rec.PROBABILITY_FROM);
2852 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_TO --------- '||l_fct_assoc_rec.PROBABILITY_TO);
2853 END IF;
2854
2855 FND_MESSAGE.Set_Name('AHL','AHL_RA_PROB_RELATIONSHIP');
2856 FND_MSG_PUB.ADD;
2857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2858 END IF;
2859
2860 -- 3. records with Overlapping values of probablities should not exist for the Org Id in AHL_RA_FCT_ASSOCIATIONS
2861 BEGIN
2862 SELECT 'Y'
2863 INTO l_dummy
2864 FROM DUAL
2865 WHERE EXISTS (SELECT 'X'
2866 FROM AHL_RA_FCT_ASSOCIATIONS
2867 WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
2868 -- Bug 4998568 :: Probability Values Should not overlap irrespective of the Fct Designator
2869 -- AND FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
2870 AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE
2871 AND ((PROBABILITY_FROM = l_fct_assoc_rec.PROBABILITY_FROM) OR
2872 (PROBABILITY_FROM > l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_FROM < l_fct_assoc_rec.PROBABILITY_TO) OR
2873 (PROBABILITY_FROM <= l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_TO >= l_fct_assoc_rec.PROBABILITY_TO) OR
2874 (PROBABILITY_TO > l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_TO < l_fct_assoc_rec.PROBABILITY_TO) OR
2875 (PROBABILITY_FROM = 100 AND l_fct_assoc_rec.PROBABILITY_TO = 100) OR -- if <> to 100 is defined .. then 100 to 100 is not allowed
2876 (PROBABILITY_TO = 100 AND l_fct_assoc_rec.PROBABILITY_FROM = 100))); -- if 100 to 100 is defined .. then <> to 100 is not allowed
2877
2878
2879 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2880 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - Probability Overlap -');
2881 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_FROM ------- '||l_fct_assoc_rec.PROBABILITY_FROM);
2882 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_TO --------- '||l_fct_assoc_rec.PROBABILITY_TO);
2883 END IF;
2884
2885 FND_MESSAGE.Set_Name('AHL','AHL_RA_PROB_OVERLAP');
2886 FND_MSG_PUB.ADD;
2887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2888
2889 EXCEPTION
2890 WHEN NO_DATA_FOUND THEN
2891 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2892 fnd_log.string(fnd_log.level_statement,l_full_name,'- HISTORICAL - No OverLap -- SUCCESS -- ');
2893 END IF;
2894 END;
2895
2896 ELSIF (l_fct_assoc_rec.ASSOCIATION_TYPE_CODE = 'ASSOC_MTBF') THEN
2897 -- Elsif When ASSOCIATION_TYPE_CODE = 'ASSOC_MTBF'
2898 -- Validate for Duplicate records in AHL_RA_FCT_ASSOCIATIONS since only one MTBF Association
2899 -- Record can be created for each Organization - irrespective of the Forecast Selected.
2900 BEGIN
2901 SELECT 'Y'
2902 INTO l_dummy
2903 FROM DUAL
2904 WHERE EXISTS (SELECT 'X'
2905 FROM AHL_RA_FCT_ASSOCIATIONS
2906 WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
2907 AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE);
2908
2909 IF (fnd_log.leVEL_STATEMENT >= fnd_log.g_current_runtime_level)THEN
2910 fnd_log.stRING(FND_LOG.Level_statement,l_full_name,'-- Invalid Param Passed - DUPLICATE FOUNT - MTBF -');
2911 fnd_log.stRING(FND_LOG.Level_statement,l_full_name,'-- l_fct_assoc_rec.ORGANIZATION_ID ------- '||l_fct_assoc_rec.ORGANIZATION_ID);
2912 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.FORECAST_DESIGNATOR --------- '||l_fct_assoc_rec.FORECAST_DESIGNATOR);
2913 END IF;
2914
2915 FND_MESSAGE.Set_Name('AHL','AHL_RA_FCT_ASSOC_MTBF_DUP');
2916 FND_MSG_PUB.ADD;
2917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2918
2919 EXCEPTION
2920 WHEN No_Data_Found THEN
2921 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2922 fnd_log.string(fnd_log.level_statement,l_full_name,'- MTBF - No Duplicate -- SUCCESS -- ');
2923 END IF;
2924 END;
2925
2926 -- Explicitly Null out PROBABILITY_FROM and PROBABILITY_TO
2927 l_fct_assoc_rec.PROBABILITY_FROM := NULL;
2928 l_fct_assoc_rec.PROBABILITY_TO := NULL;
2929
2930 END IF;
2931
2932 -- Initialize RA_FCT_ASSOCIATION_ID to sequence next val for insert
2933 SELECT AHL_RA_FCT_ASSOCIATIONS_S.NEXTVAL INTO l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID FROM DUAL;
2934
2935 -- Initialize object version number to 1
2936 l_fct_assoc_rec.OBJECT_VERSION_NUMBER := 1;
2937
2938 -- Intialize who column info
2939 l_fct_assoc_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
2940 l_fct_assoc_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
2941 l_fct_assoc_rec.CREATED_BY := fnd_global.user_id;
2942 l_fct_assoc_rec.CREATION_DATE := sysdate;
2943 l_fct_assoc_rec.LAST_UPDATE_DATE := sysdate;
2944
2945 -- Initialize security group id
2946 l_fct_assoc_rec.SECURITY_GROUP_ID := NULL;
2947
2948 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2949 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Derived RA_FCT_ASSOCIATION_ID -- ' || l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID);
2950 END IF;
2951
2952 -- INSERT Forecast Associations Data in AHL_RA_FCT_ASSOCIATIONS
2953 INSERT INTO AHL_RA_FCT_ASSOCIATIONS(RA_FCT_ASSOCIATION_ID,FORECAST_DESIGNATOR,ASSOCIATION_TYPE_CODE,ORGANIZATION_ID,PROBABILITY_FROM, PROBABILITY_TO,
2954 OBJECT_VERSION_NUMBER,SECURITY_GROUP_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,
2955 LAST_UPDATE_LOGIN,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,
2956 ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15)
2957 VALUES(
2958 l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID -- RA_FCT_ASSOCIATION_ID
2959 ,l_fct_assoc_rec.FORECAST_DESIGNATOR -- FORECAST_DESIGNATOR
2960 ,l_fct_assoc_rec.ASSOCIATION_TYPE_CODE -- ASSOCIATION_TYPE_CODE
2961 ,l_fct_assoc_rec.ORGANIZATION_ID -- ORGANIZATION_ID
2962 ,l_fct_assoc_rec.PROBABILITY_FROM -- PROBABILITY_FROM
2963 ,l_fct_assoc_rec.PROBABILITY_TO -- PROBABILITY_TO
2964 ,l_fct_assoc_rec.OBJECT_VERSION_NUMBER -- OBJECT_VERSION_NUMBER
2965 ,l_fct_assoc_rec.SECURITY_GROUP_ID -- SECURITY_GROUP_ID
2966 ,l_fct_assoc_rec.CREATION_DATE -- CREATION_DATE
2967 ,l_fct_assoc_rec.CREATED_BY -- CREATED_BY
2968 ,l_fct_assoc_rec.LAST_UPDATE_DATE -- LAST_UPDATE_DATE
2969 ,l_fct_assoc_rec.LAST_UPDATED_BY -- LAST_UPDATED_BY
2970 ,l_fct_assoc_rec.LAST_UPDATE_LOGIN -- LAST_UPDATE_LOGIN
2971 ,l_fct_assoc_rec.ATTRIBUTE_CATEGORY -- ATTRIBUTE_CATEGORY
2972 ,l_fct_assoc_rec.ATTRIBUTE1 -- ATTRIBUTE1
2973 ,l_fct_assoc_rec.ATTRIBUTE2 -- ATTRIBUTE2
2974 ,l_fct_assoc_rec.ATTRIBUTE3 -- ATTRIBUTE3
2975 ,l_fct_assoc_rec.ATTRIBUTE4 -- ATTRIBUTE4
2976 ,l_fct_assoc_rec.ATTRIBUTE5 -- ATTRIBUTE5
2977 ,l_fct_assoc_rec.ATTRIBUTE6 -- ATTRIBUTE6
2978 ,l_fct_assoc_rec.ATTRIBUTE7 -- ATTRIBUTE7
2979 ,l_fct_assoc_rec.ATTRIBUTE8 -- ATTRIBUTE8
2980 ,l_fct_assoc_rec.ATTRIBUTE9 -- ATTRIBUTE9
2981 ,l_fct_assoc_rec.ATTRIBUTE10 -- ATTRIBUTE10
2982 ,l_fct_assoc_rec.ATTRIBUTE11 -- ATTRIBUTE11
2983 ,l_fct_assoc_rec.ATTRIBUTE12 -- ATTRIBUTE12
2984 ,l_fct_assoc_rec.ATTRIBUTE13 -- ATTRIBUTE13
2985 ,l_fct_assoc_rec.ATTRIBUTE14 -- ATTRIBUTE14
2986 ,l_fct_assoc_rec.ATTRIBUTE15 -- ATTRIBUTE15
2987 );
2988
2989 -- Set the Out Param
2990 p_x_fct_assoc_rec := l_fct_assoc_rec;
2991
2992 -- Standard check for p_commit
2993 IF FND_API.To_Boolean (p_commit) THEN
2994 COMMIT;
2995 END IF;
2996
2997 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2998 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- CREATE_FCT_ASSOC_DATA -------END-----------');
2999 END IF;
3000
3001 -- Standard call to get message count and if count is 1, get message
3002 FND_MSG_PUB.Count_And_Get
3003 ( p_count => x_msg_count,
3004 p_data => x_msg_data,
3005 p_encoded => fnd_api.g_false);
3006
3007 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3008 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
3009 END IF;
3010
3011 EXCEPTION
3012 WHEN FND_API.G_EXC_ERROR THEN
3013 x_return_status := FND_API.G_RET_STS_ERROR;
3014 ROLLBACK TO CREATE_FCT_ASSOC_DATA_SP;
3015 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3016 p_data => x_msg_data,
3017 p_encoded => fnd_api.g_false);
3018
3019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3021 ROLLBACK TO CREATE_FCT_ASSOC_DATA_SP;
3022 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3023 p_data => x_msg_data,
3024 p_encoded => fnd_api.g_false);
3025
3026 WHEN OTHERS THEN
3027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3028 ROLLBACK TO CREATE_FCT_ASSOC_DATA_SP;
3029 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3030 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3031 p_procedure_name => 'CREATE_FCT_ASSOC_DATA',
3032 p_error_text => SUBSTR(SQLERRM,1,240));
3033 END IF;
3034 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3035 p_data => x_msg_data,
3036 p_encoded => fnd_api.g_false);
3037
3038 END CREATE_FCT_ASSOC_DATA;
3039
3040
3041
3042 -- Start of Comments --
3043 --
3044 -- Procedure name : UPDATE_FCT_ASSOC_DATA
3045 -- Type : Private
3046 -- Function : This API would update the setup data for Reliability Framework in AHL_RA_FCT_ASSOCIATIONS
3047 -- Update Logic to be used - NULL : Do not update
3048 -- G_MISS_XXXX : Nullify
3049 -- Valid Values : Update
3050 -- Pre-reqs :
3051 --
3052 -- Standard IN Parameters :
3053 -- p_api_version IN NUMBER Required
3054 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3055 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3056 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3057 --
3058 -- Standard OUT Parameters :
3059 -- x_return_status OUT VARCHAR2 Required
3060 -- x_msg_count OUT NUMBER Required
3061 -- x_msg_data OUT VARCHAR2 Required
3062 --
3063 -- UPDATE_FCT_ASSOC_DATA Parameters :
3064 -- p_x_fct_assoc_rec IN OUT RA_FCT_ASSOC_REC_TYPE Required
3065 --
3066 -- Version :
3067 -- Initial Version 1.0
3068 --
3069 -- End of Comments --
3070 PROCEDURE UPDATE_FCT_ASSOC_DATA (
3071 p_api_version IN NUMBER,
3072 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3073 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3074 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3075 p_module_type IN VARCHAR2,
3076 x_return_status OUT NOCOPY VARCHAR2,
3077 x_msg_count OUT NOCOPY NUMBER,
3078 x_msg_data OUT NOCOPY VARCHAR2,
3079 p_x_fct_assoc_rec IN OUT NOCOPY AHL_RA_SETUPS_PVT.RA_FCT_ASSOC_REC_TYPE) IS
3080
3081 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FCT_ASSOC_DATA';
3082 l_api_version CONSTANT NUMBER := 1.0;
3083 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
3084
3085 l_fct_assoc_rec AHL_RA_SETUPS_PVT.RA_FCT_ASSOC_REC_TYPE DEFAULT p_x_fct_assoc_rec;
3086 l_obj_version_num AHL_RA_FCT_ASSOCIATIONS.OBJECT_VERSION_NUMBER%TYPE;
3087 l_dummy varchar2(1);
3088
3089 BEGIN
3090
3091 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3092 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
3093 END IF;
3094
3095 -- Standard start of API savepoint
3096 SAVEPOINT UPDATE_FCT_ASSOC_DATA_SP;
3097
3098 -- Standard call to check for call compatibility
3099 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3101 END IF;
3102
3103 -- Initialize message list if p_init_msg_list is set to TRUE
3104 IF FND_API.To_Boolean(p_init_msg_list) THEN
3105 FND_MSG_PUB.Initialize;
3106 END IF;
3107
3108 x_return_status := FND_API.G_RET_STS_SUCCESS;
3109
3110 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3111 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- UPDATE_FCT_ASSOC_DATA -------BEGIN-----------');
3112 END IF;
3113
3114 -- Instead of FND_API.G_MISS_NUM, "-1001" is being passed from UI to indicate nullifying of Probability from
3115 -- and Probability To Columns. Translation being done below.
3116 IF (l_fct_assoc_rec.PROBABILITY_FROM = -1001) THEN
3117 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3118 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Translating -1001 to G_MISS_NUM - FROM');
3119 END IF;
3120 l_fct_assoc_rec.PROBABILITY_FROM := FND_API.G_MISS_NUM;
3121 END IF;
3122
3123 IF (l_fct_assoc_rec.PROBABILITY_TO = -1001) THEN
3124 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3125 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Translating -1001 to G_MISS_NUM - TO');
3126 END IF;
3127 l_fct_assoc_rec.PROBABILITY_TO := FND_API.G_MISS_NUM;
3128 END IF;
3129
3130 -- Validate input data in l_fct_assoc_rec
3131 -- A. l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID Cannot be NULL
3132 -- B. OPERATIONS_FLAG should be U
3133 -- C. Object Version Number should not be NULL
3134 IF ((l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID IS NULL) OR
3135 ((l_fct_assoc_rec.OPERATION_FLAG IS NULL) OR (l_fct_assoc_rec.OPERATION_FLAG <> G_DML_UPDATE)) OR
3136 (l_fct_assoc_rec.OBJECT_VERSION_NUMBER IS NULL OR l_fct_assoc_rec.OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM))THEN
3137 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3138 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- RA_FCT_ASSOCIATION_ID :' || l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID);
3139 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- OP FLAG :' || l_fct_assoc_rec.OPERATION_FLAG);
3140 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- OVN :' || l_fct_assoc_rec.OBJECT_VERSION_NUMBER);
3141 END IF;
3142 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
3143 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.U_FCST_DATA');
3144 FND_MSG_PUB.ADD;
3145 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3146 END IF;
3147
3148 -- Check for existence of record and fetch details for change record validation and data defaulting
3149 BEGIN
3150 SELECT OBJECT_VERSION_NUMBER
3151 ,DECODE(l_fct_assoc_rec.FORECAST_DESIGNATOR,FND_API.G_MISS_CHAR,NULL
3152 ,NULL,FORECAST_DESIGNATOR
3153 ,l_fct_assoc_rec.FORECAST_DESIGNATOR)
3154 ,DECODE(l_fct_assoc_rec.ASSOCIATION_TYPE_CODE,FND_API.G_MISS_CHAR,NULL
3155 ,NULL,ASSOCIATION_TYPE_CODE
3156 ,l_fct_assoc_rec.ASSOCIATION_TYPE_CODE)
3157 ,DECODE(l_fct_assoc_rec.ORGANIZATION_ID,FND_API.G_MISS_NUM,NULL
3158 ,NULL,ORGANIZATION_ID
3159 ,l_fct_assoc_rec.ORGANIZATION_ID)
3160 ,DECODE(l_fct_assoc_rec.PROBABILITY_FROM,FND_API.G_MISS_NUM,NULL
3161 ,NULL,PROBABILITY_FROM
3162 ,l_fct_assoc_rec.PROBABILITY_FROM)
3163 ,DECODE(l_fct_assoc_rec.PROBABILITY_TO,FND_API.G_MISS_NUM,NULL
3164 ,NULL,PROBABILITY_TO
3165 ,l_fct_assoc_rec.PROBABILITY_TO)
3166 INTO l_obj_version_num
3167 ,l_fct_assoc_rec.FORECAST_DESIGNATOR
3168 ,l_fct_assoc_rec.ASSOCIATION_TYPE_CODE
3169 ,l_fct_assoc_rec.ORGANIZATION_ID
3170 ,l_fct_assoc_rec.PROBABILITY_FROM
3171 ,l_fct_assoc_rec.PROBABILITY_TO
3172 FROM AHL_RA_FCT_ASSOCIATIONS
3173 WHERE RA_FCT_ASSOCIATION_ID = l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID
3174 FOR UPDATE OF object_version_number NOWAIT;
3175
3176 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3177 fnd_log.string(fnd_log.level_statement,l_full_name,'-- OBJECT VERSION NUMBER IN DB : ' || l_obj_version_num);
3178 END IF;
3179
3180 EXCEPTION
3181 WHEN NO_DATA_FOUND THEN
3182 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3183 fnd_log.string(fnd_log.level_statement,l_full_name,'-- DATA DOES NOT EXISTS -- ERROR ... ' || l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID);
3184 END IF;
3185 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
3186 FND_MSG_PUB.ADD;
3187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3188 END;
3189
3190
3191 -- Validate input data in p_x_fct_assoc_rec
3192 -- A. RECORD MUST NOT HAVE CHANGED. i.e. object_version_number should not change.
3193 IF l_fct_assoc_rec.OBJECT_VERSION_NUMBER <> l_obj_version_num THEN
3194 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3195 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN passed : ' || l_fct_assoc_rec.OBJECT_VERSION_NUMBER);
3196 END IF;
3197 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
3198 FND_MSG_PUB.ADD;
3199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3200 END IF;
3201
3202 -- Due to the decode statements used above the l_fct_assoc_rec record will not contain the final state to data that will exist in the db.
3203 -- Validate input fct association data in l_fct_assoc_rec
3204 -- A. ASSOCIATION_TYPE_CODE Cannot be NULL
3205 -- B. If ASSOCIATION_TYPE_CODE should be in ('ASSOC_HISTORICAL','ASSOC_MTBF')
3206 -- C. ORGANIZATION_ID cannot be NULL
3207 -- D. FORECAST_DESIGNATOR cannot be NULL
3208 -- E. If ASSOCIATION_TYPE_CODE = ASSOC_HISTORICAL then PROBABILITY_FROM and PROBABILITY_TO are mandatory.
3209 -- F. OPERATIONS_FLAG should be U
3210
3211 IF (l_fct_assoc_rec.ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL' AND (l_fct_assoc_rec.PROBABILITY_FROM IS NULL OR
3212 l_fct_assoc_rec.PROBABILITY_TO IS NULL)) THEN
3213 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3214 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
3215 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_FROM ------- '||l_fct_assoc_rec.PROBABILITY_FROM);
3216 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_TO --------- '||l_fct_assoc_rec.PROBABILITY_TO);
3217 END IF;
3218
3219 FND_MESSAGE.Set_Name('AHL','AHL_RA_HIST_PROB_NULL');
3220 FND_MSG_PUB.ADD;
3221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3222
3223 END IF;
3224
3225 IF ((l_fct_assoc_rec.ASSOCIATION_TYPE_CODE IS NULL) OR
3226 (l_fct_assoc_rec.ASSOCIATION_TYPE_CODE NOT IN ('ASSOC_HISTORICAL','ASSOC_MTBF')) OR
3227 (l_fct_assoc_rec.ORGANIZATION_ID IS NULL) OR
3228 (l_fct_assoc_rec.FORECAST_DESIGNATOR IS NULL) OR
3229 ((l_fct_assoc_rec.OPERATION_FLAG IS NULL) OR (l_fct_assoc_rec.OPERATION_FLAG <> G_DML_UPDATE))) THEN
3230
3231 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3232 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
3233 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_FROM ------- '||l_fct_assoc_rec.PROBABILITY_FROM);
3234 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_TO --------- '||l_fct_assoc_rec.PROBABILITY_TO);
3235 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.FORECAST_DESIGNATOR ---- '||l_fct_assoc_rec.FORECAST_DESIGNATOR);
3236 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.ORGANIZATION_ID -------- '||l_fct_assoc_rec.ORGANIZATION_ID);
3237 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.ASSOCIATION_TYPE_CODE -- '||l_fct_assoc_rec.ASSOCIATION_TYPE_CODE);
3238 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.OPERATION_FLAG --------- '||l_fct_assoc_rec.OPERATION_FLAG);
3239 END IF;
3240
3241 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
3242 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.U_FCT_DATA');
3243 FND_MSG_PUB.ADD;
3244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3245
3246 END IF;
3247
3248 -- Validate value of ORGANIZATION_ID passed
3249 BEGIN
3250 -- Bug 4913954 : Perf Fix
3251 -- Removed non-required reference to ORG_ORGANIZATION_DEFINTIONS below
3252 -- See earlier Query in 120.11
3253 SELECT 'Y'
3254 INTO l_dummy
3255 FROM DUAL
3256 WHERE EXISTS(SELECT 'X'
3257 FROM MTL_PARAMETERS MP
3258 WHERE MP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
3259 AND MP.EAM_ENABLED_FLAG='Y');
3260
3261 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3262 fnd_log.string(fnd_log.level_statement,l_full_name,'-- ORGANIZATION_ID Validated Successfully--');
3263 END IF;
3264
3265
3266 EXCEPTION
3267 WHEN NO_DATA_FOUND THEN
3268 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3269 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - ORGANIZATION_ID -' || l_fct_assoc_rec.ORGANIZATION_ID);
3270 END IF;
3271 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
3272 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.U_FCT_DATA');
3273 FND_MSG_PUB.ADD;
3274 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3275 END;
3276
3277 -- Validate value of FORECAST_DESIGNATOR passed
3278 BEGIN
3279 SELECT 'Y'
3280 INTO l_dummy
3281 FROM DUAL
3282 WHERE EXISTS(SELECT 'X'
3283 FROM mrp_forecast_designators_v MRP
3284 WHERE MRP.FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
3285 AND MRP.ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
3286 AND MRP.FORECAST_SET IS NOT NULL);
3287
3288 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3289 fnd_log.string(fnd_log.level_statement,l_full_name,'-- FORECAST_DESIGNATOR Validated Successfully--');
3290 END IF;
3291
3292
3293 EXCEPTION
3294 WHEN NO_DATA_FOUND THEN
3295 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3296 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - FORECAST_DESIGNATOR -' || l_fct_assoc_rec.FORECAST_DESIGNATOR);
3297 END IF;
3298 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
3299 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.U_FCT_DATA');
3300 FND_MSG_PUB.ADD;
3301 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3302 END;
3303
3304
3305 -- When ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL'
3306 -- Validate PROBABILITY_FROM and PROBABILITY_TO passed
3307 -- 1. Neither PROBABILITY_FROM nor PROBABILITY_TO can be passed less than zero or greater than 100
3308 -- 2. PROBABILITY_TO should be >= PROBABILITY_FROM
3309 -- 3. records with Overlapping values of probablities should not exist for the Org Id in AHL_RA_FCT_ASSOCIATIONS
3310 IF (l_fct_assoc_rec.ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL') THEN
3311
3312 -- 1. Neither PROBABILITY_FROM nor PROBABILITY_TO can be passed less than zero or greater than 100
3313 IF (l_fct_assoc_rec.PROBABILITY_FROM < 0 OR
3314 l_fct_assoc_rec.PROBABILITY_FROM > 100 OR
3315 l_fct_assoc_rec.PROBABILITY_TO < 0 OR
3316 l_fct_assoc_rec.PROBABILITY_TO > 100) THEN
3317
3318 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3319 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - less than zero or greater than 100 -');
3320 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_FROM ------- '||l_fct_assoc_rec.PROBABILITY_FROM);
3321 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_TO --------- '||l_fct_assoc_rec.PROBABILITY_TO);
3322 END IF;
3323
3324 FND_MESSAGE.Set_Name('AHL','AHL_RA_PROB_VALID_RANGE');
3325 FND_MSG_PUB.ADD;
3326 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3327 END IF;
3328
3329 -- 2. PROBABILITY_TO should be >= PROBABILITY_FROM
3330 IF (NOT(l_fct_assoc_rec.PROBABILITY_FROM <= l_fct_assoc_rec.PROBABILITY_TO)) THEN
3331
3332 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3333 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - PROBABILITY_TO should be >= PROBABILITY_FROM -');
3334 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_FROM ------- '||l_fct_assoc_rec.PROBABILITY_FROM);
3335 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_TO --------- '||l_fct_assoc_rec.PROBABILITY_TO);
3336 END IF;
3337
3338 FND_MESSAGE.Set_Name('AHL','AHL_RA_PROB_RELATIONSHIP');
3339 FND_MSG_PUB.ADD;
3340 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3341 END IF;
3342
3343 -- 3. records with Overlapping values of probablities should not exist for the Org Id and forecast designator in AHL_RA_FCT_ASSOCIATIONS
3344 BEGIN
3345 SELECT 'Y'
3346 INTO l_dummy
3347 FROM DUAL
3348 WHERE EXISTS (SELECT 'X'
3349 FROM AHL_RA_FCT_ASSOCIATIONS
3350 WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
3351 -- Bug 4998568 :: Probability Values Should not overlap irrespective of the Fct Designator
3352 -- AND FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR
3353 AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE
3354 AND ((PROBABILITY_FROM = l_fct_assoc_rec.PROBABILITY_FROM) OR
3355 (PROBABILITY_FROM > l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_FROM < l_fct_assoc_rec.PROBABILITY_TO) OR
3356 (PROBABILITY_FROM <= l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_TO >= l_fct_assoc_rec.PROBABILITY_TO) OR
3357 (PROBABILITY_TO > l_fct_assoc_rec.PROBABILITY_FROM AND PROBABILITY_TO < l_fct_assoc_rec.PROBABILITY_TO) OR
3358 (PROBABILITY_FROM = 100 AND l_fct_assoc_rec.PROBABILITY_TO = 100) OR-- if <> to 100 is defined .. then 100 to 100 is not allowed
3359 (PROBABILITY_TO = 100 AND l_fct_assoc_rec.PROBABILITY_FROM = 100)) -- if 100 to 100 is defined .. then <> to 100 is not allowed
3360 AND RA_FCT_ASSOCIATION_ID <> l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID); -- Update of the ame record to bump OVN is allowed
3361
3362
3363 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3364 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed - Probability Overlap -');
3365 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_FROM ------- '||l_fct_assoc_rec.PROBABILITY_FROM);
3366 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.PROBABILITY_TO --------- '||l_fct_assoc_rec.PROBABILITY_TO);
3367 END IF;
3368
3369 FND_MESSAGE.Set_Name('AHL','AHL_RA_PROB_OVERLAP');
3370 FND_MSG_PUB.ADD;
3371 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3372
3373 EXCEPTION
3374 WHEN NO_DATA_FOUND THEN
3375 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3376 fnd_log.string(fnd_log.level_statement,l_full_name,'- HISTORICAL - No OverLap -- SUCCESS -- ');
3377 END IF;
3378 END;
3379
3380 ELSIF (l_fct_assoc_rec.ASSOCIATION_TYPE_CODE = 'ASSOC_MTBF') THEN
3381 -- Elsif When ASSOCIATION_TYPE_CODE = 'ASSOC_MTBF'
3382 -- Validate for Duplicate records in AHL_RA_FCT_ASSOCIATIONS since only one MTBF Association
3383 -- Record can be created for each Organization - irrespective of the Forecast Selected.
3384 BEGIN
3385 SELECT 'Y'
3386 INTO l_dummy
3387 FROM DUAL
3388 WHERE EXISTS (SELECT 'X'
3389 FROM AHL_RA_FCT_ASSOCIATIONS
3390 WHERE ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID
3391 AND ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE
3392 AND RA_FCT_ASSOCIATION_ID <> l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID); -- Update of the ame record to bump OVN is allowed
3393
3394
3395 IF (fnd_log.leVEL_STATEMENT >= fnd_log.g_current_runtime_level)THEN
3396 fnd_log.stRING(FND_LOG.Level_statement,l_full_name,'-- Invalid Param Passed - DUPLICATE FOUNT - MTBF -');
3397 fnd_log.stRING(FND_LOG.Level_statement,l_full_name,'-- l_fct_assoc_rec.ORGANIZATION_ID ------- '||l_fct_assoc_rec.ORGANIZATION_ID);
3398 fnd_log.string(fnd_log.level_statement,l_full_name,'-- l_fct_assoc_rec.FORECAST_DESIGNATOR --------- '||l_fct_assoc_rec.FORECAST_DESIGNATOR);
3399 END IF;
3400
3401 FND_MESSAGE.Set_Name('AHL','AHL_RA_FCT_ASSOC_MTBF_DUP');
3402 FND_MSG_PUB.ADD;
3403 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3404
3405 EXCEPTION
3406 When No_Data_Found then
3407 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3408 fnd_log.string(fnd_log.level_statement,l_full_name,'- MTBF - No Duplicate -- SUCCESS -- ');
3409 END IF;
3410 END;
3411
3412 -- Explicitly Null out PROBABILITY_FROM and PROBABILITY_TO
3413 l_fct_assoc_rec.PROBABILITY_FROM := NULL;
3414 l_fct_assoc_rec.PROBABILITY_TO := NULL;
3415
3416 END IF;
3417
3418 -- Increment object version number
3419 l_fct_assoc_rec.OBJECT_VERSION_NUMBER := l_fct_assoc_rec.OBJECT_VERSION_NUMBER + 1;
3420
3421 -- Intialize who column info
3422 l_fct_assoc_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
3423 l_fct_assoc_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
3424 l_fct_assoc_rec.LAST_UPDATE_DATE := sysdate;
3425 l_fct_assoc_rec.CREATED_BY := fnd_global.user_id;
3426 l_fct_assoc_rec.CREATION_DATE := sysdate;
3427
3428 -- INSERT Forecast Associations Data in AHL_RA_FCT_ASSOCIATIONS
3429 UPDATE AHL_RA_FCT_ASSOCIATIONS
3430 SET FORECAST_DESIGNATOR = l_fct_assoc_rec.FORECAST_DESIGNATOR -- FORECAST_DESIGNATOR
3431 ,ASSOCIATION_TYPE_CODE = l_fct_assoc_rec.ASSOCIATION_TYPE_CODE -- ASSOCIATION_TYPE_CODE
3432 ,ORGANIZATION_ID = l_fct_assoc_rec.ORGANIZATION_ID -- ORGANIZATION_ID
3433 ,PROBABILITY_FROM = l_fct_assoc_rec.PROBABILITY_FROM -- PROBABILITY_FROM
3434 ,PROBABILITY_TO = l_fct_assoc_rec.PROBABILITY_TO -- PROBABILITY_TO
3435 ,OBJECT_VERSION_NUMBER = l_fct_assoc_rec.OBJECT_VERSION_NUMBER -- OBJECT_VERSION_NUMBER
3436 ,SECURITY_GROUP_ID = l_fct_assoc_rec.SECURITY_GROUP_ID -- SECURITY_GROUP_ID
3437 ,LAST_UPDATE_DATE = l_fct_assoc_rec.LAST_UPDATE_DATE -- LAST_UPDATE_DATE
3438 ,LAST_UPDATED_BY = l_fct_assoc_rec.LAST_UPDATED_BY -- LAST_UPDATED_BY
3439 ,LAST_UPDATE_LOGIN = l_fct_assoc_rec.LAST_UPDATE_LOGIN -- LAST_UPDATE_LOGIN
3440 ,ATTRIBUTE_CATEGORY = l_fct_assoc_rec.ATTRIBUTE_CATEGORY -- ATTRIBUTE_CATEGORY
3441 ,ATTRIBUTE1 = l_fct_assoc_rec.ATTRIBUTE1 -- ATTRIBUTE1
3442 ,ATTRIBUTE2 = l_fct_assoc_rec.ATTRIBUTE2 -- ATTRIBUTE2
3443 ,ATTRIBUTE3 = l_fct_assoc_rec.ATTRIBUTE3 -- ATTRIBUTE3
3444 ,ATTRIBUTE4 = l_fct_assoc_rec.ATTRIBUTE4 -- ATTRIBUTE4
3445 ,ATTRIBUTE5 = l_fct_assoc_rec.ATTRIBUTE5 -- ATTRIBUTE5
3446 ,ATTRIBUTE6 = l_fct_assoc_rec.ATTRIBUTE6 -- ATTRIBUTE6
3447 ,ATTRIBUTE7 = l_fct_assoc_rec.ATTRIBUTE7 -- ATTRIBUTE7
3448 ,ATTRIBUTE8 = l_fct_assoc_rec.ATTRIBUTE8 -- ATTRIBUTE8
3449 ,ATTRIBUTE9 = l_fct_assoc_rec.ATTRIBUTE9 -- ATTRIBUTE9
3450 ,ATTRIBUTE10 = l_fct_assoc_rec.ATTRIBUTE10 -- ATTRIBUTE10
3451 ,ATTRIBUTE11 = l_fct_assoc_rec.ATTRIBUTE11 -- ATTRIBUTE11
3452 ,ATTRIBUTE12 = l_fct_assoc_rec.ATTRIBUTE12 -- ATTRIBUTE12
3453 ,ATTRIBUTE13 = l_fct_assoc_rec.ATTRIBUTE13 -- ATTRIBUTE13
3454 ,ATTRIBUTE14 = l_fct_assoc_rec.ATTRIBUTE14 -- ATTRIBUTE14
3455 ,ATTRIBUTE15 = l_fct_assoc_rec.ATTRIBUTE15 -- ATTRIBUTE15
3456 WHERE RA_FCT_ASSOCIATION_ID = l_fct_assoc_rec.RA_FCT_ASSOCIATION_ID;
3457
3458 -- Set the Out Param
3459 p_x_fct_assoc_rec := l_fct_assoc_rec;
3460
3461 -- Standard check for p_commit
3462 IF FND_API.To_Boolean (p_commit) THEN
3463 COMMIT;
3464 END IF;
3465
3466 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3467 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- UPDATE_FCT_ASSOC_DATA -------END-----------');
3468 END IF;
3469
3470 -- Standard call to get message count and if count is 1, get message
3471 FND_MSG_PUB.Count_And_Get
3472 ( p_count => x_msg_count,
3473 p_data => x_msg_data,
3474 p_encoded => fnd_api.g_false);
3475
3476 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3477 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
3478 END IF;
3479
3480 EXCEPTION
3481 WHEN FND_API.G_EXC_ERROR THEN
3482 x_return_status := FND_API.G_RET_STS_ERROR;
3483 Rollback to UPDATE_FCT_ASSOC_DATA_SP;
3484 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3485 p_data => x_msg_data,
3486 p_encoded => fnd_api.g_false);
3487
3488 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3490 Rollback to UPDATE_FCT_ASSOC_DATA_SP;
3491 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3492 p_data => x_msg_data,
3493 p_encoded => fnd_api.g_false);
3494
3495 WHEN OTHERS THEN
3496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3497 Rollback to UPDATE_FCT_ASSOC_DATA_SP;
3498 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3499 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3500 p_procedure_name => 'UPDATE_FCT_ASSOC_DATA',
3501 p_error_text => SUBSTR(SQLERRM,1,240));
3502 END IF;
3503 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3504 p_data => x_msg_data,
3505 p_encoded => fnd_api.g_false);
3506
3507 END UPDATE_FCT_ASSOC_DATA;
3508
3509
3510
3511 -- Start of Comments --
3512 --
3513 -- Procedure name : DELETE_FCT_ASSOC_DATA
3514 -- Type : Private
3515 -- Function : This API would delete the setup data for Reliability Framework in AHL_RA_FCT_ASSOCIATIONS
3516 -- Pre-reqs :
3517 --
3518 -- Standard IN Parameters :
3519 -- p_api_version IN NUMBER Required
3520 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3521 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3522 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3523 --
3524 -- Standard OUT Parameters :
3525 -- x_return_status OUT VARCHAR2 Required
3526 -- x_msg_count OUT NUMBER Required
3527 -- x_msg_data OUT VARCHAR2 Required
3528 --
3529 -- DELETE_FCT_ASSOC_DATA Parameters :
3530 -- p_fct_assoc_rec IN OUT RA_FCT_ASSOC_REC_TYPE Required
3531 --
3532 -- Version :
3533 -- Initial Version 1.0
3534 --
3535 -- End of Comments --
3536 PROCEDURE DELETE_FCT_ASSOC_DATA (
3537 p_api_version IN NUMBER,
3538 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3539 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3540 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3541 p_module_type IN VARCHAR2,
3542 x_return_status OUT NOCOPY VARCHAR2,
3543 x_msg_count OUT NOCOPY NUMBER,
3544 x_msg_data OUT NOCOPY VARCHAR2,
3545 p_fct_assoc_rec IN AHL_RA_SETUPS_PVT.RA_FCT_ASSOC_REC_TYPE) IS
3546
3547 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_FCT_ASSOC_DATA';
3548 l_api_version CONSTANT NUMBER := 1.0;
3549 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
3550
3551 l_obj_version_num AHL_RA_FCT_ASSOCIATIONS.OBJECT_VERSION_NUMBER%TYPE;
3552
3553 BEGIN
3554
3555 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3556 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
3557 END IF;
3558
3559 -- Standard start of API savepoint
3560 SAVEPOINT DELETE_FCT_ASSOC_DATA_SP;
3561
3562 -- Standard call to check for call compatibility
3563 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3565 END IF;
3566
3567 -- Initialize message list if p_init_msg_list is set to TRUE
3568 IF FND_API.To_Boolean(p_init_msg_list) THEN
3569 FND_MSG_PUB.Initialize;
3570 END IF;
3571
3572 x_return_status := FND_API.G_RET_STS_SUCCESS;
3573
3574 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3575 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_FCT_ASSOC_DATA -------BEGIN-----------');
3576 END IF;
3577
3578 -- Validate input data in p_fct_assoc_rec
3579 -- A. p_fct_assoc_rec.RA_FCT_ASSOCIATION_ID Cannot be NULL
3580 -- B. OPERATIONS_FLAG should be D
3581 -- C. Object Version Number should not be NULL
3582 IF ((p_fct_assoc_rec.RA_FCT_ASSOCIATION_ID IS NULL) OR
3583 ((p_fct_assoc_rec.OPERATION_FLAG IS NULL) OR (p_fct_assoc_rec.OPERATION_FLAG <> G_DML_DELETE)) OR
3584 (p_fct_assoc_rec.OBJECT_VERSION_NUMBER IS NULL))THEN
3585 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3586 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- RA_FCT_ASSOCIATION_ID :' || p_fct_assoc_rec.RA_FCT_ASSOCIATION_ID);
3587 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- OP FLAG :' || p_fct_assoc_rec.OPERATION_FLAG);
3588 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed -- OVN :' || p_fct_assoc_rec.OBJECT_VERSION_NUMBER);
3589 END IF;
3590 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
3591 FND_MESSAGE.Set_Token('NAME','SETUP_PVT.D_FCST_DATA');
3592 FND_MSG_PUB.ADD;
3593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3594 END IF;
3595
3596 -- Check for existence of record and fetch OVN for change record validation
3597 BEGIN
3598 SELECT OBJECT_VERSION_NUMBER
3599 INTO l_obj_version_num
3600 FROM AHL_RA_FCT_ASSOCIATIONS
3601 WHERE RA_FCT_ASSOCIATION_ID = p_fct_assoc_rec.RA_FCT_ASSOCIATION_ID
3602 FOR UPDATE OF object_version_number NOWAIT;
3603
3604 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3605 fnd_log.string(fnd_log.level_statement,l_full_name,'-- OBJECT VERSION NUMBER IN DB : ' || l_obj_version_num);
3606 END IF;
3607
3608 EXCEPTION
3609 WHEN NO_DATA_FOUND THEN
3610 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3611 fnd_log.string(fnd_log.level_statement,l_full_name,'-- DATA DOES NOT EXISTS -- ERROR ... ' || p_fct_assoc_rec.RA_FCT_ASSOCIATION_ID);
3612 END IF;
3613 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_DELETED');
3614 FND_MSG_PUB.ADD;
3615 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
3616 END;
3617
3618 -- Validate input data in p_fct_assoc_rec
3619 -- A. RECORD MUST NOT HAVE CHANGED. i.e. object_version_number should not change.
3620 IF p_fct_assoc_rec.OBJECT_VERSION_NUMBER <> l_obj_version_num THEN
3621 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3622 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Record has changed : OVN passed : ' || p_fct_assoc_rec.OBJECT_VERSION_NUMBER);
3623 END IF;
3624 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
3625 FND_MSG_PUB.ADD;
3626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3627 END IF;
3628
3629 -- Delete Record from AHL_RA_FCT_ASSOCIATIONS
3630 DELETE AHL_RA_FCT_ASSOCIATIONS
3631 WHERE RA_FCT_ASSOCIATION_ID = p_fct_assoc_rec.RA_FCT_ASSOCIATION_ID;
3632
3633 -- Standard check for p_commit
3634 IF FND_API.To_Boolean (p_commit) THEN
3635 COMMIT;
3636 END IF;
3637
3638 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
3639 fnd_log.string(fnd_log.level_statement,l_full_name,'DS -- PVT -- DELETE_FCT_ASSOC_DATA -------END-----------');
3640 END IF;
3641
3642 -- Standard call to get message count and if count is 1, get message
3643 FND_MSG_PUB.Count_And_Get
3644 ( p_count => x_msg_count,
3645 p_data => x_msg_data,
3646 p_encoded => fnd_api.g_false);
3647
3648 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
3649 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
3650 END IF;
3651
3652 EXCEPTION
3653 WHEN FND_API.G_EXC_ERROR THEN
3654 x_return_status := FND_API.G_RET_STS_ERROR;
3655 ROLLBACK TO DELETE_FCT_ASSOC_DATA_SP;
3656 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3657 p_data => x_msg_data,
3658 p_encoded => fnd_api.g_false);
3659
3660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3662 ROLLBACK TO DELETE_FCT_ASSOC_DATA_SP;
3663 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3664 p_data => x_msg_data,
3665 p_encoded => fnd_api.g_false);
3666
3667 WHEN OTHERS THEN
3668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3669 ROLLBACK TO DELETE_FCT_ASSOC_DATA_SP;
3670 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3671 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3672 p_procedure_name => 'DELETE_FCT_ASSOC_DATA',
3673 p_error_text => SUBSTR(SQLERRM,1,240));
3674 END IF;
3675 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3676 p_data => x_msg_data,
3677 p_encoded => fnd_api.g_false);
3678
3679 END DELETE_FCT_ASSOC_DATA;
3680
3681 END AHL_RA_SETUPS_PVT;
[Home] [Help]