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