[Home] [Help]
PACKAGE BODY: APPS.AHL_UMP_UNPLANNED_PVT
Source
1 PACKAGE BODY AHL_UMP_UNPLANNED_PVT AS
2 /* $Header: AHLVUUNB.pls 120.7.12020000.2 2012/12/12 07:23:08 prakkum ship $ */
3
4 -----------
5 -- Common variables
6 -----------
7 l_dummy_varchar VARCHAR2(30);
8
9 G_USER_ID CONSTANT NUMBER := TO_NUMBER(FND_GLOBAL.USER_ID);
10 G_LOGIN_ID CONSTANT NUMBER := TO_NUMBER(FND_GLOBAL.LOGIN_ID);
11
12 -- FND Logging Constants
13 G_DEBUG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 G_DEBUG_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
18
15 G_DEBUG_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
16 G_DEBUG_UEXP CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
17 G_DEBUG_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
19 -------------------
20 -- Spec Procedure Signatures --
21 -------------------
22
23 PROCEDURE Create_Unit_Effectivity
24 (
25 p_api_version IN NUMBER,
26 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
27 p_commit IN VARCHAR2 := FND_API.G_FALSE,
28 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 p_mr_header_id IN NUMBER,
33 p_instance_id IN NUMBER,
34 x_orig_ue_id OUT NOCOPY NUMBER
35 )
36 IS
37
38
39 -- Cursors
40 --pdoki added for ER 9583373
41 Cursor Chk_UE_Init_Due (c_mr_header_id NUMBER, c_instance_id NUMBER)
42 IS
43 SELECT 'x'
44 FROM AHL_UNIT_EFFECTIVITIES_B UE,
45 AHL_MR_HEADERS_B MRH
46 WHERE UE.mr_header_id = c_mr_header_id
47 AND MRH.mr_header_id = UE.mr_header_id
48 AND MRH.IMPLEMENT_STATUS_CODE IN ('MANDATORY','OPTIONAL_IMPLEMENT')
49 AND UE.csi_item_instance_id = c_instance_id
50 AND UE.status_code = 'INIT-DUE';
51
52 -- cursor to fetch all applicable MRs for the instance number
53 Cursor Get_all_appl_mrs ( c_mr_header_id NUMBER , c_instance_id NUMBER )
54 IS
55 SELECT CSI_ITEM_INSTANCE_ID, MR_HEADER_ID, DESCENDENT_COUNT
56 from
57 AHL_APPLICABLE_MRS
58 where
59 MR_HEADER_ID = c_mr_header_id and
60 CSI_ITEM_INSTANCE_ID = c_instance_id;
61
62 -- cursor to fetch all applicable MRs and their related MRs for the instance number
63 Cursor Get_Appl_Mr_Relns
64 IS
65 SELECT CSI_ITEM_INSTANCE_ID, MR_HEADER_ID, RELATED_CSI_ITEM_INSTANCE_ID, RELATED_MR_HEADER_ID, UE_ID
66 FROM AHL_APPLICABLE_MR_RELNS
67 ORDER BY TREE_DEPTH_LEVEL;
68
69 Cursor get_rel_mr_ue_id( c_mr_header_id NUMBER,c_item_instance_id NUMBER)
70 IS
71 SELECT UE_ID FROM AHL_APPLICABLE_MR_RELNS
72 where RELATED_MR_HEADER_ID = c_mr_header_id
73 and RELATED_CSI_ITEM_INSTANCE_ID = c_item_instance_id; --amsriniv
74
75 -- cursor to validate the Mr Header Id
76 CURSOR check_mr_exists (c_mr_header_id NUMBER)
77 IS
78 SELECT 'X'
79 FROM
80 AHL_MR_HEADERS_APP_V
81 where
82 MR_HEADER_ID = c_mr_header_id;
83
84 -- cursor to validate the Instance Id of the Item
85 CURSOR check_instance_exists (c_instance_id NUMBER)
86 IS
87 SELECT 'X'
88 FROM
89 CSI_ITEM_INSTANCES
90 where
91 INSTANCE_ID = c_instance_id;
92
93 -- check for group MR.
94 CURSOR is_grp_mr_check(p_mr_header_id IN NUMBER) IS
95 SELECT 'x'
96 FROM DUAL
97 WHERE EXISTS (SELECT r.related_mr_header_id
98 FROM ahl_mr_relationships r
99 WHERE r.mr_header_id = p_mr_header_id
100 AND EXISTS (SELECT m.mr_header_id
101 FROM ahl_mr_headers_b M -- perf bug 6266738
102 WHERE m.mr_header_id = r.related_mr_header_id
103 AND (m.version_number) in (SELECT max(M1.version_number)
104 from ahl_mr_headers_b M1
105 where M1.title = m.title -- perf bug 6266738
106 AND m1.mr_status_code = 'COMPLETE'
107 AND SYSDATE between trunc(m1.effective_from)
108 and trunc(nvl(m1.effective_to,SYSDATE+1))
109 )
110 )
111 );
112
113
114 -- Declare local variables
115
116 l_api_name CONSTANT VARCHAR2(30) := 'Create_Unit_Effectivity';
117 l_api_version CONSTANT NUMBER := 1.0;
118 l_return_status VARCHAR2(1);
119 l_msg_count NUMBER;
120 l_msg_data VARCHAR2(2000);
121 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
122 l_all_appl_mrs Get_all_appl_mrs%rowtype;
123 l_appl_mrs_relns Get_Appl_Mr_Relns %rowtype;
124
125 -- Procedure Returned Variables
126
127 l_appln_code VARCHAR2(30) ;
128 l_rowid ROWID;
129 l_unit_effectivity_id NUMBER;
130 l_ue_relationship_id NUMBER;
131
132 l_mr_applicable_flag BOOLEAN;
133 l_applicable_mr_tbl AHL_FMP_PVT.applicable_mr_tbl_type;
134 k NUMBER;
135 l_mr_title VARCHAR2(80);
136
137 -- BACHANDR - NR project start
138 l_fleet_header_id NUMBER;
139 -- BACHANDR - NR project end
140
141 BEGIN
142
143 -- Log API entry point
144 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)THEN
145 fnd_log.string
146 (
147 G_DEBUG_PROC,
148 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
149 'At the start of PLSQL procedure'
150 );
151 END IF;
152
153 SAVEPOINT sp_create_unit_effectivity;
154
155 -- Initialize return status to success initially
156 x_return_status:=FND_API.G_RET_STS_SUCCESS;
157
158 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
159 THEN
160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
161 END IF;
162
163 --Call the procedure AHL_UTIL_PKG.Get_Appln_Usage
164 AHL_UTIL_PKG.Get_Appln_Usage
165 (
166 x_appln_code => l_appln_code,
167 x_return_status => l_return_status
168 );
169
170 l_msg_count := FND_MSG_PUB.COUNT_MSG;
171 IF (l_msg_count > 0 OR l_return_status <> FND_API.G_RET_STS_SUCCESS)
172 THEN
173 RAISE FND_API.G_EXC_ERROR;
174 END IF;
175
176 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL ) THEN
177 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' After Calling AHL_UTIL_PKG.Get_Appln_Usage successfully' );
178 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' l_appln_code: ' || l_appln_code);
179 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' l_return_status: ' || l_return_status);
180 END IF;
184 FND_MSG_PUB.Initialize;
181
182
183 IF (p_init_msg_list = FND_API.G_TRUE) THEN
185 END IF;
186
187 -- API body starts here
188 -- If (p_mr_header_id is null or p_instance_id is null), then display error
189 IF
190 (
191 p_mr_header_id IS NULL OR p_mr_header_id = FND_API.G_MISS_NUM OR
192 p_instance_id IS NULL OR p_instance_id = FND_API.G_MISS_NUM
193 )
194 THEN
195 FND_MESSAGE.SET_NAME('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
196 FND_MESSAGE.SET_TOKEN('PROCEDURE', l_api_name);
197 FND_MSG_PUB.ADD;
198 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL )THEN
199 fnd_log.string
200 (
201 G_DEBUG_ERROR,
202 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
203 'Invalid Procedure Call'
204 );
205 END IF;
206 RAISE FND_API.G_EXC_ERROR;
207 END IF;
208
209 -- validate the mr header id
210 OPEN check_mr_exists (p_mr_header_id);
211 FETCH check_mr_exists INTO l_dummy_varchar;
212 IF (check_mr_exists%NOTFOUND)
213 THEN
214 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_MR_NOT_FOUND');
215 FND_MSG_PUB.ADD;
216 CLOSE check_mr_exists;
217 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL )THEN
218 fnd_log.string
219 (
220 G_DEBUG_ERROR,
221 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
222 'MR is not found'
223 );
224 END IF;
225 RAISE FND_API.G_EXC_ERROR;
226 END IF;
227 CLOSE check_mr_exists;
228
229 -- validate the instance id
230 OPEN check_instance_exists (p_instance_id);
231 FETCH check_instance_exists INTO l_dummy_varchar;
232 IF (check_instance_exists%NOTFOUND)
233 THEN
234 FND_MESSAGE.SET_NAME('AHL', 'AHL_UMP_INVALID_CSI_INSTANCE'); --message reused
235 FND_MSG_PUB.ADD;
236 CLOSE check_instance_exists;
237 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL )THEN
238 fnd_log.string
239 (
240 G_DEBUG_ERROR,
241 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
242 'Instance Number is invalid'
243 );
244 END IF;
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247 CLOSE check_instance_exists;
248
249 --Set the return status to an error and raise an error message if the application code returned is not AHL
250 IF (l_appln_code <> 'AHL') THEN
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 FND_MESSAGE.set_name('AHL', 'AHL_COM_APPL_USG_MODE_INVALID');
253 FND_MSG_PUB.add;
254 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL)THEN
255 fnd_log.string
256 (
257 G_DEBUG_ERROR,
258 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
259 'Application Usage Mode is not defined or is invalid'
260 );
261 END IF;
262 RAISE FND_API.G_EXC_ERROR;
263 END IF;
264
265 --pdoki added for ER 9583373
266 OPEN Chk_UE_Init_Due (p_mr_header_id, p_instance_id);
267 FETCH Chk_UE_Init_Due INTO l_dummy_varchar;
268 IF (Chk_UE_Init_Due%FOUND) THEN
269 --pick the MR Title
270 SELECT title
271 INTO l_mr_title
272 FROM AHL_MR_HEADERS_B
273 WHERE mr_header_id = p_mr_header_id ;
274
275 FND_MESSAGE.SET_NAME('AHL', 'AHL_UMP_CANNOT_ADD_MRS');
276 FND_MESSAGE.SET_TOKEN('MR_TITLE',l_mr_title);
277 FND_MSG_PUB.ADD;
278 CLOSE Chk_UE_Init_Due;
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281 CLOSE Chk_UE_Init_Due;
282
283
284 OPEN is_grp_mr_check(p_mr_header_id);
285 FETCH is_grp_mr_check INTO l_dummy_varchar;
286 IF (is_grp_mr_check%FOUND) THEN
287
288 CLOSE is_grp_mr_check;
289
290 -- call AHL_FMP_COMMON_PVT.Populate_Appl_MRs to populate the AHL_APPLICABLE_MRS temporary table
291 -- call the API with input p_include_doNotImplmt = 'Y', otherwise descendent_count is not populated.
292
293 AHL_FMP_COMMON_PVT.Populate_Appl_MRs (
294 p_csi_ii_id => p_instance_id,
295 p_include_doNotImplmt => 'Y',
296 x_return_status => x_return_status,
297 x_msg_count => x_msg_count,
298 x_msg_data => x_msg_data);
299
300 x_msg_count := FND_MSG_PUB.count_msg;
301 IF ( x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS )
302 THEN
303 RAISE FND_API.G_EXC_ERROR;
304 END IF;
305
306 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
307 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , 'AFTER Calling AHL_FMP_COMMON_PVT.Populate_Appl_MRs to populate AHL_APPLICABLE_MRS');
308 END IF;
309
310 --loop not need as only one record.The Top Group MR is returned.
311 OPEN Get_all_appl_mrs ( p_mr_header_id , p_instance_id );
312 FETCH Get_all_appl_mrs INTO l_all_appl_mrs;
313 IF (Get_all_appl_mrs%NOTFOUND)
314 THEN
315 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UE_NOT_FOUND');
316 FND_MSG_PUB.ADD;
317 CLOSE Get_all_appl_mrs;
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320
321 CLOSE Get_all_appl_mrs;
322
323 ELSE
324 CLOSE is_grp_mr_check;
325
326 -- call only for instance and mr combination.
327 -- Check MR - Instance Applicability by calling FMP API
328 AHL_FMP_PVT.GET_APPLICABLE_MRS(p_api_version => 1.0,
329 x_return_status => x_return_status,
330 x_msg_count => x_msg_count,
331 x_msg_data => x_msg_data,
332 p_item_instance_id => p_instance_id,
333 p_mr_header_id => p_mr_header_id,
334 p_components_flag => 'N', -- get applicability for only this instance.
335 x_applicable_mr_tbl => l_applicable_mr_tbl);
336
337 x_msg_count := FND_MSG_PUB.count_msg;
338 IF ( x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS )
339 THEN
340 RAISE FND_API.G_EXC_ERROR;
341 END IF;
342
343 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
344 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , 'AFTER Calling AHL_FMP_PVT.GET_APPLICABLE_MRS to validate MR-Instance applicability' );
345 END IF;
346
347 l_mr_applicable_flag := false;
348 IF (l_applicable_mr_tbl.COUNT > 0) THEN
349 FOR j IN l_applicable_mr_tbl.FIRST .. l_applicable_mr_tbl.LAST LOOP
350 IF (l_applicable_mr_tbl(j).MR_HEADER_ID = p_mr_header_id AND
351 l_applicable_mr_tbl(j).ITEM_INSTANCE_ID = p_instance_id) THEN
352 l_mr_applicable_flag := true;
353 k := j;
354 EXIT;
355 END IF; -- Applicable
356 END LOOP; -- All Applicable MRs
357 END IF; -- Table Count > 0
358
359 IF (l_mr_applicable_flag = false) THEN
360 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UE_NOT_FOUND');
361 FND_MSG_PUB.ADD;
362 RAISE FND_API.G_EXC_ERROR;
366 l_all_appl_mrs.mr_header_id := p_mr_header_id;
363 END IF; -- MR applicable
364
365 l_all_appl_mrs.csi_item_instance_id := p_instance_id;
367 l_all_appl_mrs.descendent_count := l_applicable_mr_tbl(k).descendent_count;
368
369 END IF; -- is_grp_mr_check%FOUND
370
371 -- BACHANDR - NR project start
372
373 l_fleet_header_id := AHL_UMP_UTIL_PKG.Get_Associated_Fleet(AHL_UTIL_UC_PKG.get_uc_header_id(p_instance_id => p_instance_id),sysdate,null);
374
375 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
376 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , 'Fleet header id derived -> '||l_fleet_header_id);
377 END IF;
378
379 -- BACHANDR - NR project end
380
381 SELECT AHL_UNIT_EFFECTIVITIES_B_S.NEXTVAL INTO l_unit_effectivity_id FROM DUAL;
382
383 AHL_UNIT_EFFECTIVITIES_PKG.INSERT_ROW (
384 X_ROWID => l_rowid,
385 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
386 X_EARLIEST_DUE_DATE => NULL ,
387 X_LATEST_DUE_DATE => NULL ,
388 X_ACCOMPLISHED_DATE => NULL ,
389 X_SERVICE_LINE_ID => NULL ,
390 X_PROGRAM_MR_HEADER_ID => NULL,
391 X_CANCEL_REASON_CODE => NULL ,
392 X_ATTRIBUTE_CATEGORY => NULL ,
393 X_ATTRIBUTE1 => NULL ,
394 X_ATTRIBUTE2 => NULL ,
395 X_ATTRIBUTE3 => NULL ,
396 X_ATTRIBUTE4 => NULL ,
397 X_ATTRIBUTE5 => NULL ,
398 X_ATTRIBUTE6 => NULL ,
399 X_ATTRIBUTE7 => NULL ,
400 X_ATTRIBUTE8 => NULL ,
401 X_ATTRIBUTE9 => NULL ,
402 X_ATTRIBUTE10 => NULL ,
403 X_ATTRIBUTE11 => NULL ,
404 X_ATTRIBUTE12 => NULL ,
405 X_ATTRIBUTE13 => NULL ,
406 X_ATTRIBUTE14 => NULL ,
407 X_ATTRIBUTE15 => NULL ,
408 X_OBJECT_VERSION_NUMBER => 1 ,
409 --X_CSI_ITEM_INSTANCE_ID => l_all_appl_mrs.CSI_ITEM_INSTANCE_ID,
410 --X_MR_HEADER_ID => l_all_appl_mrs.MR_HEADER_ID,
411 X_CSI_ITEM_INSTANCE_ID => p_instance_id,
412 X_MR_HEADER_ID => p_mr_header_id,
413 X_MR_EFFECTIVITY_ID => NULL ,
414 X_MR_INTERVAL_ID => NULL ,
415 X_STATUS_CODE => NULL ,
416 X_DUE_DATE => NULL ,
417 X_DUE_COUNTER_VALUE => NULL ,
418 X_FORECAST_SEQUENCE => NULL ,
419 X_REPETITIVE_MR_FLAG => NULL ,
420 X_TOLERANCE_FLAG => NULL ,
421 X_MESSAGE_CODE => NULL ,
422 X_DATE_RUN => NULL ,
423 X_PRECEDING_UE_ID => NULL ,
424 X_SET_DUE_DATE => NULL ,
425 X_REMARKS => NULL ,
426 X_DEFER_FROM_UE_ID => NULL ,
427 X_CS_INCIDENT_ID => NULL ,
428 X_QA_COLLECTION_ID => NULL ,
429 X_ORIG_DEFERRAL_UE_ID => NULL ,
430 X_APPLICATION_USG_CODE => l_appln_code,
431 X_OBJECT_TYPE => 'MR',
432 X_COUNTER_ID => NULL ,
433 X_MANUALLY_PLANNED_FLAG => 'Y',
434 X_LOG_SERIES_CODE => NULL,
435 X_LOG_SERIES_NUMBER => NULL,
436 X_FLIGHT_NUMBER => NULL,
437 X_MEL_CDL_TYPE_CODE => NULL,
438 X_POSITION_PATH_ID => NULL,
439 X_ATA_CODE => NULL,
440 X_UNIT_CONFIG_HEADER_ID => NULL,
441 X_CREATION_DATE => sysdate,
442 X_CREATED_BY => G_USER_ID,
443 X_LAST_UPDATE_DATE => sysdate,
444 X_LAST_UPDATED_BY => G_USER_ID,
445 X_LAST_UPDATE_LOGIN => G_LOGIN_ID,
446 -- BACHANDR - NR project start
447 X_FLEET_HEADER_ID => l_fleet_header_id
448 -- BACHANDR - NR project end
449 );
450
451 x_orig_ue_id := l_unit_effectivity_id;
452
453 -- Initialize temporary table.
454
455 -- Process_Group_MRs() is not used as this takes all Group MRs in AHL_APPLICABLE_MRS and calls
456 -- process_Group_MR_Instance() on all of them.
457
458 -- for the particular record of interest in AHL_APPLICABLE_MRS, process_Group_MR_Instance() should
459 -- get the whole tree
460 IF( l_all_appl_mrs.DESCENDENT_COUNT > 0 )
461 THEN
462
463 -- call the API AHL_UMP_UTIL_PKG.process_Group_MR_Instance for the relevant MR in AHL_APPLICABLE_MRS
464 AHL_UMP_UTIL_PKG.process_Group_MR_Instance(
465 p_top_mr_id => l_all_appl_mrs.MR_HEADER_ID ,
466 p_top_item_instance_id => l_all_appl_mrs.CSI_ITEM_INSTANCE_ID ,
467 p_init_temp_table => 'Y'); -- To clean up temp table first
468
469 x_msg_count := FND_MSG_PUB.count_msg;
470 IF x_msg_count > 0
471 THEN
472 RAISE FND_API.G_EXC_ERROR;
473 END IF;
474
475 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
476 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' AFTER Calling AHL_UMP_UTIL_PKG.process_Group_MR_Instance to populate AHL_APPLICABLE_MR_RELNS' );
477 END IF;
478
479
480 -- create Unit Effectivities from Related MRs also
481
482 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
483 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' BEFORE Calling AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row for Related MRs from AHL_APPLICABLE_MR_RELNS' );
484 END IF;
485
486 FOR l_appl_mrs_relns IN Get_Appl_Mr_Relns
487 LOOP
488 --pdoki added for ER 9583373
489 OPEN Chk_UE_Init_Due (l_appl_mrs_relns.RELATED_MR_HEADER_ID, l_appl_mrs_relns.RELATED_CSI_ITEM_INSTANCE_ID);
490 FETCH Chk_UE_Init_Due INTO l_dummy_varchar;
491 IF (Chk_UE_Init_Due%FOUND) THEN
492 --pick the MR Title
493 SELECT title
494 INTO l_mr_title
495 FROM AHL_MR_HEADERS_B
496 WHERE mr_header_id = l_appl_mrs_relns.RELATED_MR_HEADER_ID;
497 FND_MESSAGE.SET_NAME('AHL', 'AHL_UMP_CANNOT_ADD_MRS');
498 FND_MESSAGE.SET_TOKEN('MR_TITLE',l_mr_title);
499 FND_MSG_PUB.ADD;
500 CLOSE Chk_UE_Init_Due;
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503 CLOSE Chk_UE_Init_Due;
504
505 /*
506 -- BACHANDR - NR project start
507 l_uc_header_id := AHL_UTIL_UC_PKG.get_uc_header_id(p_instance_id => l_appl_mrs_relns.RELATED_CSI_ITEM_INSTANCE_ID);
508 -- BACHANDR - NR project end
509 */
510
511 SELECT AHL_UNIT_EFFECTIVITIES_B_S.NEXTVAL INTO l_unit_effectivity_id FROM DUAL;
512
513 AHL_UNIT_EFFECTIVITIES_PKG.INSERT_ROW (
514 X_ROWID => l_rowid,
515 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
516 X_EARLIEST_DUE_DATE => NULL ,
517 X_LATEST_DUE_DATE => NULL ,
518 X_ACCOMPLISHED_DATE => NULL ,
519 X_SERVICE_LINE_ID => NULL ,
520 X_PROGRAM_MR_HEADER_ID => NULL,
521 X_CANCEL_REASON_CODE => NULL ,
522 X_ATTRIBUTE_CATEGORY => NULL ,
523 X_ATTRIBUTE1 => NULL ,
524 X_ATTRIBUTE2 => NULL ,
525 X_ATTRIBUTE3 => NULL ,
526 X_ATTRIBUTE4 => NULL ,
527 X_ATTRIBUTE5 => NULL ,
528 X_ATTRIBUTE6 => NULL ,
529 X_ATTRIBUTE7 => NULL ,
530 X_ATTRIBUTE8 => NULL ,
531 X_ATTRIBUTE9 => NULL ,
532 X_ATTRIBUTE10 => NULL ,
533 X_ATTRIBUTE11 => NULL ,
534 X_ATTRIBUTE12 => NULL ,
535 X_ATTRIBUTE13 => NULL ,
536 X_ATTRIBUTE14 => NULL ,
537 X_ATTRIBUTE15 => NULL ,
538 X_OBJECT_VERSION_NUMBER => 1 ,
539 --X_CSI_ITEM_INSTANCE_ID => l_appl_mrs_relns.CSI_ITEM_INSTANCE_ID,
540 X_CSI_ITEM_INSTANCE_ID => l_appl_mrs_relns.RELATED_CSI_ITEM_INSTANCE_ID,
541 X_MR_HEADER_ID => l_appl_mrs_relns.RELATED_MR_HEADER_ID,
542 X_MR_EFFECTIVITY_ID => NULL ,
543 X_MR_INTERVAL_ID => NULL ,
544 X_STATUS_CODE => NULL ,
545 X_DUE_DATE => NULL ,
546 X_DUE_COUNTER_VALUE => NULL ,
547 X_FORECAST_SEQUENCE => NULL ,
548 X_REPETITIVE_MR_FLAG => NULL ,
549 X_TOLERANCE_FLAG => NULL ,
550 X_MESSAGE_CODE => NULL ,
551 X_DATE_RUN => NULL ,
552 X_PRECEDING_UE_ID => NULL ,
553 X_SET_DUE_DATE => NULL ,
554 X_REMARKS => NULL ,
555 X_DEFER_FROM_UE_ID => NULL ,
556 X_CS_INCIDENT_ID => NULL ,
557 X_QA_COLLECTION_ID => NULL ,
558 X_ORIG_DEFERRAL_UE_ID => NULL ,
559 X_APPLICATION_USG_CODE => l_appln_code,
560 X_OBJECT_TYPE => 'MR',
561 X_COUNTER_ID => NULL ,
562 X_MANUALLY_PLANNED_FLAG => 'Y',
563 X_LOG_SERIES_CODE => NULL,
564 X_LOG_SERIES_NUMBER => NULL,
565 X_FLIGHT_NUMBER => NULL,
566 X_MEL_CDL_TYPE_CODE => NULL,
567 X_POSITION_PATH_ID => NULL,
568 X_ATA_CODE => NULL,
569 X_UNIT_CONFIG_HEADER_ID => NULL,
570 X_CREATION_DATE => sysdate,
571 X_CREATED_BY => G_USER_ID,
572 X_LAST_UPDATE_DATE => sysdate,
573 X_LAST_UPDATED_BY => G_USER_ID,
574 X_LAST_UPDATE_LOGIN => G_LOGIN_ID,
575 -- BACHANDR - NR project start
576 X_FLEET_HEADER_ID => l_fleet_header_id
577 -- BACHANDR - NR project end
578 );
579 -- additional column added to the table AHL_UNIT_EFFECTIVITIES
580
581 -- update the table AHL_APPLICABLE_MR_RELNS with the new UE_IDs generated in AHL_UNIT_EFFECTIVITIES
582
583 UPDATE AHL_APPLICABLE_MR_RELNS
584 SET
585 UE_ID = l_unit_effectivity_id --MANUALLY_PLANNED = 'Y'
586 where CSI_ITEM_INSTANCE_ID = l_appl_mrs_relns.CSI_ITEM_INSTANCE_ID
587 and MR_HEADER_ID = l_appl_mrs_relns.MR_HEADER_ID --amsriniv
588 and RELATED_CSI_ITEM_INSTANCE_ID = l_appl_mrs_relns.RELATED_CSI_ITEM_INSTANCE_ID
589 and RELATED_MR_HEADER_ID = l_appl_mrs_relns.RELATED_MR_HEADER_ID ;
590
591 END LOOP;
592
593 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
594 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' AFTER Calling AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row for Related MRs from AHL_APPLICABLE_MR_RELNS' );
595 END IF;
596
597 -- to update the AHL_UE_RELATIONSHIPS table with the Unit Effectivity Relationships
598
599 FOR l_appl_mrs_relns IN Get_Appl_Mr_Relns
600 LOOP
601
602 IF ( l_appl_mrs_relns.MR_HEADER_ID = p_mr_header_id)
603 THEN
604
605 l_unit_effectivity_id := x_orig_ue_id;
606
607 ELSE
608
609 OPEN get_rel_mr_ue_id(l_appl_mrs_relns.MR_HEADER_ID,
610 l_appl_mrs_relns.CSI_ITEM_INSTANCE_ID); --amsriniv;
611 FETCH get_rel_mr_ue_id into l_unit_effectivity_id;
612 CLOSE get_rel_mr_ue_id;
613 END IF;
614
615 SELECT AHL_UE_RELATIONSHIPS_S.NEXTVAL INTO l_ue_relationship_id FROM DUAL;
616
617 AHL_UE_RELATIONSHIPS_PKG.INSERT_ROW
618 (
619 X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
620 X_UE_ID => l_unit_effectivity_id,
621 X_RELATED_UE_ID => l_appl_mrs_relns.ue_id,
622 X_RELATIONSHIP_CODE => 'PARENT',
623 X_ORIGINATOR_UE_ID => x_orig_ue_id,
624 X_ATTRIBUTE_CATEGORY => NULL,
625 X_ATTRIBUTE1 => NULL,
626 X_ATTRIBUTE2 => NULL,
627 X_ATTRIBUTE3 => NULL,
628 X_ATTRIBUTE4 => NULL,
629 X_ATTRIBUTE5 => NULL,
630 X_ATTRIBUTE6 => NULL,
631 X_ATTRIBUTE7 => NULL,
632 X_ATTRIBUTE8 => NULL,
633 X_ATTRIBUTE9 => NULL,
634 X_ATTRIBUTE10 => NULL,
635 X_ATTRIBUTE11 => NULL,
636 X_ATTRIBUTE12 => NULL,
637 X_ATTRIBUTE13 => NULL,
638 X_ATTRIBUTE14 => NULL,
639 X_ATTRIBUTE15 => NULL,
640 X_OBJECT_VERSION_NUMBER => 1,
641 X_LAST_UPDATE_DATE => sysdate,
642 X_LAST_UPDATED_BY => G_USER_ID,
643 X_CREATION_DATE => sysdate,
644 X_CREATED_BY => G_USER_ID,
645 X_LAST_UPDATE_LOGIN => G_LOGIN_ID
646 );
647 END LOOP;
648
649 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
650 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' AFTER Calling AHL_UE_RELATIONSHIPS_PKG.INSERT_ROW for Related MRs from AHL_APPLICABLE_MR_RELNS' );
651 END IF;
652 END IF;
653
654
655
656 -- API body ends here
657
658 -- Log API exit point
659 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
660 THEN
661 fnd_log.string
662 (
663 G_DEBUG_PROC,
664 L_DEBUG_MODULE||'.end',
665 'At the end of PLSQL procedure'
666 );
667 END IF;
668
669 -- Check Error Message stack.
670 x_msg_count := FND_MSG_PUB.count_msg;
671 IF x_msg_count > 0
672 THEN
673 RAISE FND_API.G_EXC_ERROR;
674 END IF;
675
676
677 EXCEPTION
678 WHEN FND_API.G_EXC_ERROR THEN
679 Rollback to sp_create_unit_effectivity;
680 x_return_status := FND_API.G_RET_STS_ERROR;
681 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL)THEN
682 fnd_log.string
683 (
684 G_DEBUG_ERROR,
685 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
686 'Expected error'
687 );
688 END IF;
689 FND_MSG_PUB.count_and_get
690 (
691 p_count => x_msg_count,
692 p_data => x_msg_data,
693 p_encoded => FND_API.G_FALSE
694 );
695
696 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
697 Rollback to sp_create_unit_effectivity;
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
699 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL)THEN
700 fnd_log.string
701 (
702 G_DEBUG_ERROR,
703 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
704 'Unexpected error'
705 );
706 END IF;
707 FND_MSG_PUB.count_and_get
708 (
709 p_count => x_msg_count,
710 p_data => x_msg_data,
711 p_encoded => FND_API.G_FALSE
712 );
713
714 WHEN OTHERS THEN
715 Rollback to sp_create_unit_effectivity;
716 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
717 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
718 THEN
719 FND_MSG_PUB.add_exc_msg
720 (
721 p_pkg_name => G_PKG_NAME,
722 p_procedure_name => 'Create_Unit_Effectivity',
723 p_error_text => SUBSTR(SQLERRM,1,240)
724 );
725 END IF;
726 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL)THEN
727 fnd_log.string
728 (
729 G_DEBUG_ERROR,
730 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
731 'Other errors'
732 );
733 END IF;
734 FND_MSG_PUB.count_and_get
735 (
736 p_count => x_msg_count,
737 p_data => x_msg_data,
738 p_encoded => FND_API.G_FALSE
739 );
740 END Create_Unit_Effectivity;
741
742
743 PROCEDURE Delete_Unit_Effectivity
744 (
745 p_api_version IN NUMBER,
746 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
747 p_commit IN VARCHAR2 := FND_API.G_FALSE,
748 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
749 x_return_status OUT NOCOPY VARCHAR2,
750 x_msg_count OUT NOCOPY NUMBER,
751 x_msg_data OUT NOCOPY VARCHAR2,
752 p_unit_effectivity_id IN NUMBER
753 )
754 IS
755
756 -- Cursor definitions
757
758 -- given the ue id get the originator ue id.
759 Cursor GetOrigUeId ( c_ue_id NUMBER)
760 IS
761 SELECT ORIGINATOR_UE_ID
762 FROM
763 AHL_UE_RELATIONSHIPS
764 WHERE
765 RELATED_UE_ID = c_ue_id ;
766
767 --given the Top Ue Id, get the whole tree
768
769 Cursor GetAllUeIds ( c_ue_id NUMBER)
770 IS
771 SELECT UE_RELATIONSHIP_ID , UE_ID , RELATED_UE_ID , ORIGINATOR_UE_ID
772 FROM
773 AHL_UE_RELATIONSHIPS
774 START WITH UE_ID = c_ue_id
775 CONNECT BY UE_ID = PRIOR RELATED_UE_ID order by RELATED_UE_ID;
776
777 -- get the status code of the UE before deleting it.
778 Cursor GetStatusCode(c_ue_id NUMBER)
779 IS
780 SELECT STATUS_CODE,OBJECT_TYPE FROM AHL_UNIT_EFFECTIVITIES_APP_V
781 WHERE UNIT_EFFECTIVITY_ID = c_ue_id
782 and MANUALLY_PLANNED_FLAG = 'Y';
783
784 -- Declare local variables
785 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Unit_Effectivity';
786 l_api_version CONSTANT NUMBER := 1.0;
787
788 L_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
789
790 l_originator_ue_id NUMBER;
791 l_appln_code VARCHAR2(30) ;
792 l_msg_count NUMBER;
793 l_get_orig_ue NUMBER;
794 l_all_ue_ids GetAllUeIds%rowtype;
795 l_get_status_code VARCHAR2(30);
796
797 l_return_status VARCHAR2(1);
798 l_object_type VARCHAR2(30);
799
800 BEGIN
801
802 SAVEPOINT sp_delete_unit_effectivity;
803 -- Standard call to check for call compatibility
804 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
805 THEN
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 END IF;
808
809 -- Log API entry point
810 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
811 THEN
812 fnd_log.string
813 (
814 G_DEBUG_PROC,
815 L_DEBUG_MODULE||'.begin',
816 'At the start of PLSQL procedure'
817 );
818 END IF;
819
820 -- API body starts here
821 -- Initialize API return status to success
822 x_return_status := FND_API.G_RET_STS_SUCCESS;
823
824 --Call the procedure AHL_UTIL_PKG.Get_Appln_Usage
825 AHL_UTIL_PKG.Get_Appln_Usage
826 (
827 x_appln_code => l_appln_code,
828 x_return_status => l_return_status
829 );
830
831 l_msg_count := FND_MSG_PUB.COUNT_MSG;
832 IF (l_msg_count > 0)
833 THEN
834 RAISE FND_API.G_EXC_ERROR;
835 END IF;
836
837 -- if the UE Id passed is a middle node then get the Top Node UE Id.
838 OPEN GetOrigUeId ( p_unit_effectivity_id );
839 FETCH GetOrigUeId INTO l_get_orig_ue;
840 IF (GetOrigUeId%NOTFOUND)
841 THEN
842 l_originator_ue_id := p_unit_effectivity_id;
843 -- if not found then must be the top node itself or doesnt have any children.
844 ELSE
845 l_originator_ue_id := l_get_orig_ue;
846
847 END IF;
848 CLOSE GetOrigUeId;
849
850 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
851 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' AFTER finding the originator UE id '|| l_originator_ue_id );
852 END IF;
853
854
855 -- if the ue id to be deleted is not found
856 IF (l_originator_ue_id IS NULL)
857 THEN
858 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UE_NOT_FOUND');
859 FND_MSG_PUB.ADD;
860 RAISE FND_API.G_EXC_ERROR;
861 ELSE
862 -- validate the existance of the Unit Effectivity Id
863 OPEN GetStatusCode (p_unit_effectivity_id);
864 FETCH GetStatusCode INTO l_get_status_code,l_object_type;
865 IF (GetStatusCode%NOTFOUND) THEN
866 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UE_NOT_FOUND');
867 FND_MSG_PUB.ADD;
868 CLOSE GetStatusCode;
869 RAISE FND_API.G_EXC_ERROR;
870 ELSIF ( l_get_status_code IS NOT NULL AND l_get_status_code NOT IN ('EXCEPTION')
871 AND l_object_type = 'SR')THEN
872 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UE_CANNOT_DELETE');
873 FND_MSG_PUB.ADD;
874 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL)THEN
875 fnd_log.string
876 (
877 G_DEBUG_ERROR,
878 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
879 'Cannot delete UE'
880 );
881 END IF;
882 END IF;
883 CLOSE GetStatusCode;
884 END IF;
885
886 FOR l_all_ue_ids IN GetAllUeIds(l_originator_ue_id)
887 LOOP
888
889 OPEN GetStatusCode( l_all_ue_ids.RELATED_UE_ID);
890 FETCH GetStatusCode into l_get_status_code,l_object_type ;
891 IF (GetStatusCode%NOTFOUND)
892 THEN
893 FND_MESSAGE.SET_NAME('AHL', 'AHL_UMP_STATUS_NULL'); -- mesg reused
894 FND_MSG_PUB.ADD;
895 CLOSE GetStatusCode;
896 RAISE FND_API.G_EXC_ERROR;
897 END IF;
898 CLOSE GetStatusCode;
899 IF ( l_get_status_code IS NULL OR l_get_status_code IN ('EXCEPTION') )
900 THEN
901 -- delete the corresponding relationships records too.
902 AHL_UE_RELATIONSHIPS_PKG.DELETE_ROW( l_all_ue_ids.UE_RELATIONSHIP_ID);
903 -- deletes all related ue ids of the originator ue id of the ue id passed for deletion.
904 AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW( l_all_ue_ids.RELATED_UE_ID);
905
906 ELSE
907 FND_MESSAGE.SET_NAME('AHL', 'AHL_UA_UE_CANNOT_DELETE');
908 FND_MSG_PUB.ADD;
909 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL)THEN
910 fnd_log.string
911 (
912 G_DEBUG_ERROR,
913 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
914 'Cannot delete UE'
915 );
916 END IF;
917 RAISE FND_API.G_EXC_ERROR;
918
919 END IF;
920
921 END LOOP;
922
923 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
924 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' AFTER deleting all related UE s and their relationships');
925 END IF;
926
927 -- added for SB Enh for 'Chain' case
928 BEGIN
929 delete from ahl_unit_effectivities_tl
930 where unit_effectivity_id in (select unit_effectivity_id from ahl_unit_effectivities_b where start_lc_ue_id = l_originator_ue_id);
931
932 delete from ahl_unit_effectivities_b
933 where start_lc_ue_id = l_originator_ue_id
934 and unit_effectivity_id <> start_lc_ue_id;
935 EXCEPTION
936 WHEN OTHERS THEN
937 null;
938 END;
939 -- deletes the top node UE id
940
941 AHL_UNIT_EFFECTIVITIES_PKG.DELETE_ROW(l_originator_ue_id);
942
943 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL) THEN
944 FND_LOG.STRING(G_DEBUG_STMT, L_DEBUG_MODULE , ' AFTER deleting the originator UE ');
945 END IF;
946 -- API body ends here
947
948 -- Log API exit point
949 IF (G_DEBUG_PROC >= G_DEBUG_LEVEL)
950 THEN
951 fnd_log.string
952 (
953 G_DEBUG_PROC,
954 L_DEBUG_MODULE||'.end',
955 'At the end of PLSQL procedure'
956 );
957 END IF;
958
959 -- Check Error Message stack.
960 x_msg_count := FND_MSG_PUB.count_msg;
961 IF x_msg_count > 0
962 THEN
963 RAISE FND_API.G_EXC_ERROR;
964 END IF;
965
966 -- Standard call to get message count and if count is 1, get message info
967 FND_MSG_PUB.count_and_get
968 (
969 p_count => x_msg_count,
970 p_data => x_msg_data,
971 p_encoded => FND_API.G_FALSE
972 );
973
974 EXCEPTION
975 WHEN FND_API.G_EXC_ERROR THEN
976 Rollback to sp_delete_unit_effectivity;
977 x_return_status := FND_API.G_RET_STS_ERROR;
978 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL)THEN
979 fnd_log.string
980 (
981 G_DEBUG_ERROR,
982 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
983 'Expected error'
984 );
985 END IF;
986 FND_MSG_PUB.count_and_get
987 (
988 p_count => x_msg_count,
989 p_data => x_msg_data,
990 p_encoded => FND_API.G_FALSE
991 );
992
993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
994 Rollback to sp_delete_unit_effectivity;
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL)THEN
997 fnd_log.string
998 (
999 G_DEBUG_ERROR,
1000 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1001 'Unexpected error'
1002 );
1003 END IF;
1004 FND_MSG_PUB.count_and_get
1005 (
1006 p_count => x_msg_count,
1007 p_data => x_msg_data,
1008 p_encoded => FND_API.G_FALSE
1009 );
1010
1011 WHEN OTHERS THEN
1012 Rollback to sp_delete_unit_effectivity;
1013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1014 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1015 THEN
1016 FND_MSG_PUB.add_exc_msg
1017 (
1018 p_pkg_name => G_PKG_NAME,
1019 p_procedure_name => 'Delete_Unit_Effectivity',
1020 p_error_text => SUBSTR(SQLERRM,1,240)
1021 );
1022 END IF;
1023 IF (G_DEBUG_ERROR >= G_DEBUG_LEVEL)THEN
1024 fnd_log.string
1025 (
1026 G_DEBUG_ERROR,
1027 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||'.begin',
1028 'Other errors'
1029 );
1030 END IF;
1031 FND_MSG_PUB.count_and_get
1032 (
1033 p_count => x_msg_count,
1034 p_data => x_msg_data,
1035 p_encoded => FND_API.G_FALSE
1036 );
1037 End Delete_Unit_Effectivity;
1038
1039 End AHL_UMP_UNPLANNED_PVT;