[Home] [Help]
PACKAGE BODY: APPS.AHL_UMP_UTIL_PKG
Source
1 PACKAGE BODY AHL_UMP_UTIL_PKG AS
2 /* $Header: AHLUUMPB.pls 120.7.12010000.2 2008/12/26 23:03:59 sracha ship $ */
3
4
5 -----------------------------------------------------------
6 -- Function to get unit configuration name for a given --
7 -- item instance. --
8 -----------------------------------------------------------
9 FUNCTION get_unitName (p_csi_item_instance_id IN NUMBER)
10 RETURN VARCHAR2
11 IS
12
13 -- Get unit name for component.
14 CURSOR get_unit_name_csr (p_csi_item_instance_id IN NUMBER) IS
15 SELECT name
16 FROM ahl_unit_config_headers uc
17 WHERE csi_item_instance_id in ( SELECT object_id
18 FROM csi_ii_relationships
19 START WITH subject_id = p_csi_item_instance_id
20 AND relationship_type_code = 'COMPONENT-OF'
21 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
22 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
23 CONNECT BY PRIOR object_id = subject_id
24 AND relationship_type_code = 'COMPONENT-OF'
25 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
26 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
27 )
28 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
29 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
30 AND parent_uc_header_id IS NULL;
31
32 -- For top node.
33 CURSOR get_unit_name_csr1 (p_csi_item_instance_id IN NUMBER) IS
34 SELECT name
35 FROM ahl_unit_config_headers uc
36 WHERE csi_item_instance_id = p_csi_item_instance_id
37 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
38 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
39 AND parent_uc_header_id IS NULL;
40
41 l_name ahl_unit_config_headers.name%TYPE;
42
43 begin
44
45 --Check for top node.
46 OPEN get_unit_name_csr1(p_csi_item_instance_id);
47 FETCH get_unit_name_csr1 INTO l_name;
48 IF (get_unit_name_csr1%NOTFOUND) THEN
49 -- Check for component.
50 OPEN get_unit_name_csr(p_csi_item_instance_id);
51 FETCH get_unit_name_csr INTO l_name;
52 IF (get_unit_name_csr%NOTFOUND) THEN
53 l_name := null;
54 END IF;
55 CLOSE get_unit_name_csr;
56 END IF;
57 CLOSE get_unit_name_csr1;
58
59 RETURN l_name;
60
61 end get_unitName;
62
63 -------------------------------------------------------
64 -- Function to get the children count for a group MR --
65 -------------------------------------------------------
66 FUNCTION GetCount_childUE(p_ue_id IN NUMBER)
67 RETURN NUMBER
68 IS
69 --
70 CURSOR get_count_child_csr(p_id IN NUMBER) IS
71 SELECT count(related_ue_id)
72 FROM ahl_ue_relationships
73 WHERE relationship_code = 'PARENT'
74 AND ue_id = p_id;
75 --
76 l_count NUMBER;
77 --
78 BEGIN
79 OPEN get_count_child_csr(p_ue_id);
80 FETCH get_count_child_csr INTO l_count;
81 CLOSE get_count_child_csr;
82
83 return l_count;
84 END GetCount_childUE;
85
86
87 -----------------------------------------------------------
88 -- Procedure to get Visit details for a unit effectivity --
89 -----------------------------------------------------------
90 PROCEDURE get_Visit_Details ( p_unit_effectivity_id IN NUMBER,
91 x_visit_Start_date OUT NOCOPY DATE,
92 x_visit_End_date OUT NOCOPY DATE,
93 x_visit_Assign_code OUT NOCOPY VARCHAR2)
94
95 IS
96
97 --l_ump_visit_rec AHL_VWP_VISITS_PVT.Srch_UMP_rec_type;
98 l_return_status VARCHAR2(1);
99 l_msg_count NUMBER;
100 l_msg_data VARCHAR2(2000);
101 l_visit_id NUMBER;
102
103 -- 11.5.10CU2: Ignore simulation visits.
104 CURSOR ahl_visit_csr(p_ue_id IN NUMBER) IS
105 SELECT vst.start_date_time, vst.visit_id
106 FROM ahl_visit_tasks_b tsk, (select vst1.*
107 from ahl_visits_b vst1, ahl_simulation_plans_b sim
108 where vst1.simulation_plan_id = sim.simulation_plan_id
109 and sim.primary_plan_flag = 'Y'
110 UNION ALL
111 select vst1.*
112 from ahl_visits_b vst1
113 where vst1.simulation_plan_id IS NULL) vst
114 WHERE vst.visit_id = tsk.visit_id
115 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
116 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
117 AND tsk.unit_effectivity_id = p_ue_id;
118 begin
119
120 x_visit_End_date := null;
121 x_visit_start_date := null;
122 x_visit_assign_code := null;
123
124 -- Call VWP API.
125 /* AHL_VWP_VISITS_PVT.UMP_Visit_Info( p_api_version => 1.0,
126 p_init_msg_list => FND_API.G_FALSE,
127 p_commit => FND_API.G_FALSE,
128 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
129 p_unit_effectivity_id => p_unit_effectivity_id,
130 x_return_status => l_return_status,
131 x_msg_count => l_msg_count,
132 x_msg_data => l_msg_data,
133 x_ump_visit_rec => l_ump_visit_rec);
134
135
136 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
139 RAISE FND_API.G_EXC_ERROR;
140 END IF; */
141
142 open ahl_visit_csr (p_unit_effectivity_id);
143 FETCH ahl_visit_csr INTO x_visit_start_date, l_visit_id;
144
145 /* Call vwp function to get visit end date */
146
147 IF (ahl_visit_csr%FOUND) THEN
148 x_visit_End_date := AHL_VWP_TIMES_PVT.get_visit_end_time(p_visit_id => l_visit_id,
149 p_use_actuals => FND_API.G_FALSE);
150 END IF;
151
152 close ahl_visit_csr;
153
154 --x_visit_start_date := l_ump_visit_rec.Visit_start_Date;
155 --x_visit_End_date := l_ump_visit_rec.Visit_End_Date;
156 --x_visit_assign_code := l_ump_visit_rec.Assign_Status_Code;
157
158 end get_Visit_Details;
159
160 -------------------------------------------------------------------------------
161 -- Function to get the visit status - planning/released/closed/ --
162 -- This procedure will be called by Process_Unit and Terminate_MR_Instances. --
163 -------------------------------------------------------------------------------
164 FUNCTION get_Visit_Status ( p_unit_effectivity_id IN NUMBER)
165
166 RETURN VARCHAR2
167
168
169 IS
170
171 --l_ump_visit_rec AHL_VWP_VISITS_PVT.Srch_UMP_rec_type;
172 l_visit_status_code AHL_VISITS_B.STATUS_CODE%TYPE;
173 l_return_status VARCHAR2(1);
174 l_msg_count NUMBER;
175 l_msg_data VARCHAR2(2000);
176
177 -- 11.5.10CU2: Ignore simulation visits.
178 CURSOR ahl_visit_csr(p_ue_id IN NUMBER) IS
179
180 SELECT decode(vst.status_code,'CLOSED', vst.status_code, tsk.status_code)
181 FROM ahl_visit_tasks_b tsk, (select vst1.*
182 from ahl_visits_b vst1, ahl_simulation_plans_b sim
183 where vst1.simulation_plan_id = sim.simulation_plan_id
184 and sim.primary_plan_flag = 'Y'
185 UNION ALL
186 select vst1.*
187 from ahl_visits_b vst1
188 where vst1.simulation_plan_id IS NULL) vst
189 WHERE vst.visit_id = tsk.visit_id
190 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
191 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
192 AND tsk.unit_effectivity_id = p_ue_id;
193 /*
194 FROM ahl_visit_tasks_b tsk, ahl_visits_b vst
195 WHERE vst.visit_id = tsk.visit_id
196 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
197 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
198 AND tsk.unit_effectivity_id = p_ue_id;
199 */
200
201 begin
202 /*
203 -- Call VWP API.
204 AHL_VWP_VISITS_PVT.UMP_Visit_Info( p_api_version => 1.0,
205 p_init_msg_list => FND_API.G_FALSE,
206 p_commit => FND_API.G_FALSE,
207 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
208 p_unit_effectivity_id => p_unit_effectivity_id,
209 x_return_status => l_return_status,
210 x_msg_count => l_msg_count,
211 x_msg_data => l_msg_data,
212 x_ump_visit_rec => l_ump_visit_rec);
213
214 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
217 RAISE FND_API.G_EXC_ERROR;
218 END IF;
219
220 */
221
222 open ahl_visit_csr (p_unit_effectivity_id);
223 FETCH ahl_visit_csr INTO l_visit_status_code;
224 close ahl_visit_csr;
225
226 -- return visit status.
227 --RETURN l_ump_visit_rec.Visit_Status_Code;
228 RETURN l_visit_status_code;
229
230
231 end get_Visit_Status;
232
233 -------------------------------------------------------------------------
234 -- Procedure to get the last accomplishment of an MR for any given item
235 -- instance. --
236 -------------------------------------------------------------------------
237 PROCEDURE get_last_accomplishment (p_csi_item_instance_id IN NUMBER,
238 p_mr_header_id IN NUMBER,
239 x_accomplishment_date OUT NOCOPY DATE,
240 x_unit_effectivity_id OUT NOCOPY NUMBER,
241 x_deferral_flag OUT NOCOPY BOOLEAN,
242 x_status_code OUT NOCOPY VARCHAR2,
243 x_return_val OUT NOCOPY BOOLEAN)
244 IS
245
246 -- cursor to get mr title, version and copy accomplishment.
247 CURSOR ahl_mr_headers_csr (p_mr_header_id IN NUMBER) IS
248 SELECT title mr_title, version_number, copy_accomplishment_flag
249 FROM ahl_mr_headers_b
250 WHERE mr_header_id = p_mr_header_id;
251
252 -- cursor to get mr title, version and copy accomplishment.
253 CURSOR ahl_mr_title_csr (p_mr_title IN VARCHAR2,
254 p_version_number IN NUMBER) IS
255 SELECT version_number, copy_accomplishment_flag, mr_header_id
256 FROM ahl_mr_headers_b
257 WHERE title = p_mr_title AND
258 version_number = p_version_number;
259
260 -- cursor to get accomplishments for current version.
261 CURSOR ahl_unit_effectivities_csr (p_csi_item_instance_id IN NUMBER,
262 p_mr_header_id IN NUMBER) IS
263 SELECT ue.accomplished_date, ue.unit_effectivity_id, ue.status_code,
264 decode(ue.status_code, 'TERMINATED', ter.affect_due_calc_flag, def.affect_due_calc_flag),
265 decode(ue.status_code, 'TERMINATED', ter.deferral_effective_on, def.deferral_effective_on)
266 FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b def, ahl_unit_deferrals_b ter
267 WHERE ue.defer_from_ue_id = def.unit_effectivity_id (+)
268 AND ue.unit_effectivity_id = ter.unit_effectivity_id(+)
269 AND ue.status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED','TERMINATED')
270 AND ue.csi_item_instance_id = p_csi_item_instance_id
271 AND ue.mr_header_id = p_mr_header_id
272 --ORDER BY accomplished_date DESC;
273 ORDER BY decode (ue.status_code, 'TERMINATED', ter.deferral_effective_on, ue.accomplished_date) DESC;
274
275 l_accomplish_found BOOLEAN := FALSE;
276 l_mr_header_id NUMBER := p_mr_header_id;
277 l_unit_effectivity_id NUMBER;
278 l_accomplishment_date DATE;
279 l_copy_accomplishment_flag ahl_mr_headers_v.copy_accomplishment_flag%TYPE;
280 l_mr_title ahl_mr_headers_v.title%TYPE;
281 l_version_number NUMBER;
282 l_status_code ahl_unit_effectivities_vl.status_code%TYPE;
283
284 -- Added for deferral functionality.
285 l_affect_due_calc_flag VARCHAr2(1);
286 l_deferral_effective_on DATE;
287
288 BEGIN
289 -- Set return status.
290 x_return_val := TRUE;
291
292 -- Set deferral flag.
293 x_deferral_flag := FALSE;
294
295 -- GET MR details.
296 OPEN ahl_mr_headers_csr (p_mr_header_id);
297 FETCH ahl_mr_headers_csr INTO l_mr_title,
298 l_version_number,
299 l_copy_accomplishment_flag;
300 IF (ahl_mr_headers_csr%NOTFOUND) THEN
301 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
302 FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
303 FND_MSG_PUB.ADD;
304 x_return_val := FALSE;
305 x_accomplishment_date := null;
306 x_unit_effectivity_id := null;
307 x_status_code := null;
308 CLOSE ahl_mr_headers_csr;
309 RETURN;
310 END IF;
311 CLOSE ahl_mr_headers_csr;
312
313 -- pick the most recent accomplishment from previous version.
314 l_accomplish_found := FALSE;
315 WHILE NOT(l_accomplish_found) LOOP
316 -- Get last accomplishment.
317 OPEN ahl_unit_effectivities_csr(p_csi_item_instance_id,
318 l_mr_header_id);
319 FETCH ahl_unit_effectivities_csr INTO l_accomplishment_date,
320 l_unit_effectivity_id,
321 l_status_code,
322 l_affect_due_calc_flag,
323 l_deferral_effective_on;
324
325 IF (ahl_unit_effectivities_csr%FOUND) THEN
326 --dbms_output.put_line ('ue id' || l_unit_effectivity_id);
327 -- Added for deferral enhancements.
328 -- Use deferral_effective_on date instead of accomplishment date.
329 IF (l_affect_due_calc_flag = 'N') THEN
330 l_accomplishment_date := l_deferral_effective_on;
331 x_deferral_flag := TRUE;
332 END IF;
333 x_accomplishment_date := l_accomplishment_date;
334 x_unit_effectivity_id := l_unit_effectivity_id;
335 x_status_code := l_status_code;
336 l_accomplish_found := TRUE;
337 CLOSE ahl_unit_effectivities_csr;
338 ELSE
339 -- find accomplishments from last mr revision based on copy accomplishment flag.
340 IF (l_copy_accomplishment_flag = 'N') THEN
341 --dbms_output.put_line ('copy_accomplishment_flag' || l_copy_accomplishment_flag );
342 x_accomplishment_date := null;
343 x_unit_effectivity_id := null;
344 x_status_code := null;
345 l_accomplish_found := TRUE;
346 ELSE
347 -- check if any more versions available.
348 IF (l_version_number = 1) THEN
349 --dbms_output.put_line ('version_number = 1' );
350 x_accomplishment_date := null;
351 x_unit_effectivity_id := null;
352 x_status_code := null;
353 l_accomplish_found := TRUE;
354 ELSE
355 -- check if the earlier version exists.
356 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
357 CLOSE ahl_unit_effectivities_csr;
358 END IF;
359 --dbms_output.put_line ('next version');
360 OPEN ahl_mr_title_csr(l_mr_title,
361 l_version_number-1);
362 FETCH ahl_mr_title_csr INTO l_version_number,
363 l_copy_accomplishment_flag,
364 l_mr_header_id;
365 IF (ahl_mr_title_csr%NOTFOUND) THEN
366 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_TITLE_INVALID');
367 FND_MESSAGE.Set_Token('TITLE',l_mr_title);
368 FND_MESSAGE.Set_Token('VERSION',l_version_number);
369 FND_MSG_PUB.ADD;
370 x_return_val := FALSE;
371 x_accomplishment_date := null;
372 x_unit_effectivity_id := null;
373 x_status_code := null;
374 CLOSE ahl_mr_title_csr;
375 RETURN;
376 END IF;
377 CLOSE ahl_mr_title_csr;
378 END IF; /* version number */
379 END IF; /* l_copy accomplishment flag */
380 END IF; /* unit effectivities not found */
381 --dbms_output.put_line ('loop again');
382 END LOOP; /* while */
383
384 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
385 CLOSE ahl_unit_effectivities_csr;
386 END IF;
387
388 END get_last_accomplishment;
389
390 -----------------------------------------------------------------------------
391 --
392 -- Is_UE_In_Execution
393 -- Checks if the unit effectivity (item instance and MR) is currently
394 -- in execution by calling the get_Visit_Status procedure given above.
395 -- Returns TRUE even if any of the descendents (group MR) is in execution.
396 -- Used by Capture_MR_Updates before terminating an UE
397 --
398 -- Input (Mandatory)
399 -- p_ue_id: NUMBER Unit Effectivity Id
400 --
401 -----------------------------------------------------------------------------
402 FUNCTION Is_UE_In_Execution
403 (
404 p_ue_id NUMBER) return boolean IS
405
406 BEGIN
407
408 IF ( nvl(get_visit_status(p_ue_id),'X') NOT IN ('RELEASED', 'CLOSED') ) THEN
409 RETURN FALSE;
410 ELSE
411 RETURN TRUE;
412 END IF;
413
414
415 END Is_UE_In_Execution;
416
417
418 ---------------------------------------------------------------------
419 -- Procedure to get Service Request details for a unit effectivity --
420 -- Used in Preventive Maintenance mode only --
421 ---------------------------------------------------------------------
422 PROCEDURE get_ServiceRequest_Details (p_unit_effectivity_id IN NUMBER,
423 x_incident_id OUT NOCOPY NUMBER,
424 x_incident_number OUT NOCOPY VARCHAR2,
425 x_scheduled_date OUT NOCOPY DATE)
426 IS
427
428 CURSOR get_SR_details_csr (p_unit_effectivity_id IN NUMBER,
429 p_cs_link_id IN NUMBER) IS
430 SELECT inc.incident_number, inc.incident_id
431 FROM cs_incident_links link, cs_incidents_all_vl inc
432 WHERE link.subject_id = inc.incident_id
433 AND subject_type = 'SR'
434 AND link_type_id = p_cs_link_id
435 AND object_type = 'AHL_UMP_EFF'
436 AND object_id = p_unit_effectivity_id;
437
438 c_cs_link_id CONSTANT NUMBER := 6;
439 -- This link-id is seeded in cs_link_types_b and
440 -- points to link type code = 'Reference'.
441
442 BEGIN
443
444 -- Initialize.
445 x_scheduled_date := null;
446
447 -- Get Service request details.
448 OPEN get_SR_details_csr(p_unit_effectivity_id, c_cs_link_id);
449 FETCH get_SR_details_csr INTO x_incident_number, x_incident_id;
450 CLOSE get_SR_details_csr;
451
452 END get_ServiceRequest_Details;
453
454 -----------------------------------------------------------------------
455 -- Start of Comments --
456 -- Procedure name : Populate_Appl_MRs
457 -- Type : Private
458 -- Function : Calls FMP and populates the AHL_APPLICABLE_MRS table.
459 -- Pre-reqs :
460 -- Parameters :
461 --
462 -- Populate_Appl_MRs Parameters:
463 -- p_csi_ii_id IN csi item instance id Required
464 --
465 -- Version :
466 -- Initial Version 1.0
467 --
468 -- End of Comments.
469
470
471 PROCEDURE Populate_Appl_MRs (
472 p_csi_ii_id IN NUMBER,
473 p_include_doNotImplmt IN VARCHAR2 := 'Y',
474 x_return_status OUT NOCOPY VARCHAR2,
475 x_msg_count OUT NOCOPY NUMBER,
476 x_msg_data OUT NOCOPY VARCHAR2)
477 IS
478 l_api_version CONSTANT NUMBER := 1.0;
479 l_appl_mrs_tbl AHL_FMP_PVT.applicable_mr_tbl_type;
480
481 BEGIN
482
483 -- Initialize temporary table.
484 DELETE FROM AHL_APPLICABLE_MRS;
485
486 -- call api to fetch all applicable mrs for ASO installation.
487 AHL_FMP_PVT.get_applicable_mrs(
488 p_api_version => l_api_version,
489 p_init_msg_list => FND_API.G_FALSE,
490 p_commit => FND_API.G_FALSE,
491 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
492 x_return_status => x_return_status,
493 x_msg_count => x_msg_count,
494 x_msg_data => x_msg_data,
495 p_item_instance_id => p_csi_ii_id,
496 p_components_flag => 'Y',
497 p_include_doNotImplmt => p_include_doNotImplmt,
498 x_applicable_mr_tbl => l_appl_mrs_tbl);
499
500
501 -- Raise errors if exceptions occur
502 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
503 RAISE FND_API.G_EXC_ERROR;
504 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
505 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
506 END IF;
507
508 -- Populate temporary table ahl_applicable_mrs.
509 IF (l_appl_mrs_tbl.COUNT > 0) THEN
510 FOR i IN l_appl_mrs_tbl.FIRST..l_appl_mrs_tbl.LAST LOOP
511 -- dbms_output.put_line( l_appl_mrs_tbl(i).item_instance_id||' '||
512 -- l_appl_mrs_tbl(i).mr_header_id);
513 INSERT INTO AHL_APPLICABLE_MRS (
514 CSI_ITEM_INSTANCE_ID,
515 MR_HEADER_ID,
516 MR_EFFECTIVITY_ID,
517 REPETITIVE_FLAG ,
518 SHOW_REPETITIVE_CODE,
519 COPY_ACCOMPLISHMENT_CODE,
520 PRECEDING_MR_HEADER_ID,
521 IMPLEMENT_STATUS_CODE,
522 DESCENDENT_COUNT
523 ) values
524 ( l_appl_mrs_tbl(i).item_instance_id,
525 l_appl_mrs_tbl(i).mr_header_id,
526 l_appl_mrs_tbl(i).mr_effectivity_id,
527 l_appl_mrs_tbl(i).repetitive_flag,
528 l_appl_mrs_tbl(i).show_repetitive_code,
529 l_appl_mrs_tbl(i).copy_accomplishment_flag,
530 l_appl_mrs_tbl(i).preceding_mr_header_id,
531 l_appl_mrs_tbl(i).implement_status_code,
532 l_appl_mrs_tbl(i).descendent_count
533 );
534 END LOOP;
535 END IF;
536
537 END Populate_Appl_MRs;
538
539 --------------------------------------------------------------------
540 PROCEDURE Process_Group_MRs
541 IS
542 --
543 CURSOR ahl_applicable_mrs_csr IS
544 SELECT distinct mr_header_id, csi_item_instance_id, descendent_count
545 FROM ahl_applicable_mrs
546 WHERE descendent_count > 0;
547
548 --
549 l_mr_header_id NUMBER;
550 l_csi_ii_id NUMBER;
551 l_desc_count NUMBER;
552 --
553 BEGIN
554
555 -- Initialize temporary table.
556 DELETE FROM AHL_APPLICABLE_MR_RELNS;
557
558 OPEN ahl_applicable_mrs_csr;
559 LOOP
560 FETCH ahl_applicable_mrs_csr INTO l_mr_header_id,
561 l_csi_ii_id, l_desc_count;
562 EXIT WHEN ahl_applicable_mrs_csr%NOTFOUND;
563 IF (l_desc_count > 0) THEN
564 process_group_mr_instance(
565 p_top_mr_id => l_mr_header_id,
566 p_top_item_instance_id => l_csi_ii_id);
567 END IF;
568 END LOOP;
569 CLOSE ahl_applicable_mrs_csr;
570
571 END Process_Group_MRs;
572
573 -----------------------------------------------------------------------
574 -- Start of Comments --
575 -- Procedure name : Process_group_mr_instance
576 -- Type : Private
577 -- Function : Generate relationships for one mr+item instance combination.
578 -- Pre-reqs :
579 -- Parameters :
580 --
581 -- Populate_Appl_MRs Parameters:
582 -- p_top_item_instance_id IN csi item instance id Required
583 -- p_top_mr_id IN top mr id Required
584 --
585 -- Version :
586 -- Initial Version 1.0
587 --
588 -- End of Comments.
589
590
591 PROCEDURE Process_Group_MR_Instance (
592 p_top_mr_id IN NUMBER,
593 p_top_item_instance_id IN NUMBER,
594 p_init_temp_table IN VARCHAR2 DEFAULT 'N')
595 IS
596 --
597 -- cursor that selects all distinct, valid mr relationships
598 CURSOR ahl_fmp_relationships_csr(p_mr_id IN NUMBER) IS
599
600 /*
601 SELECT distinct r.mr_header_id, r.related_mr_header_id,
602 r.relationship_code
603 FROM ahl_mr_relationships r
604 WHERE EXISTS (SELECT 'x'
605 FROM AHL_MR_HEADERS_B b1, AHL_MR_HEADERS_B b2
606 WHERE b1.mr_header_id = r.mr_header_id
607 AND b2.mr_header_id = r.related_mr_header_id
608 AND b1.mr_status_code = 'COMPLETE'
609 AND b2.mr_status_code = 'COMPLETE'
610 AND NVL(b1.effective_from, SYSDATE) <= SYSDATE
611 AND NVL(b2.effective_from, SYSDATE) <= SYSDATE
612 AND NVL(b1.effective_to, SYSDATE+1) >= SYSDATE
613 AND NVL(b2.effective_to, SYSDATE+1) >= SYSDATE)
614 START WITH r.mr_header_id = p_mr_id
615 CONNECT BY r.mr_header_id = PRIOR r.related_mr_header_id
616 AND r.relationship_code = 'PARENT';
617 */
618
619 SELECT distinct r.mr_header_id, r.related_mr_header_id,
620 r.relationship_code
621 FROM ahl_mr_relationships r
622 START WITH r.mr_header_id = p_mr_id
623 AND r.relationship_code = 'PARENT'
624 AND exists (select 'x' from ahl_mr_headers_b mr1
625 where mr1.mr_header_id = r.related_mr_header_id
626 and mr1.version_number = (select max(mr2.version_number)
627 from ahl_mr_headers_b mr2
628 where mr2.title = mr1.title
629 and mr2.mr_status_code = 'COMPLETE'
630 and SYSDATE between trunc(mr2.effective_from)
631 and trunc(nvl(mr2.effective_to,SYSDATE+1))
632 )
633 )
634 CONNECT BY r.mr_header_id = PRIOR r.related_mr_header_id
635 AND r.relationship_code = 'PARENT'
636 AND exists (select 'x' from ahl_mr_headers_b mr1
637 where mr1.mr_header_id = r.related_mr_header_id
638 and mr1.version_number = (select max(mr2.version_number)
639 from ahl_mr_headers_b mr2
640 where mr2.title = mr1.title
641 and mr2.mr_status_code = 'COMPLETE'
642 and SYSDATE between trunc(mr2.effective_from)
643 and trunc(nvl(mr2.effective_to,SYSDATE+1))
644 )
645 );
646
647 --
648 CURSOR ahl_appl_parent_mr_csr(p_mr_id IN NUMBER) IS
649
650 SELECT distinct csi_item_instance_id
651 FROM ahl_applicable_mrs
652 WHERE mr_header_id = p_mr_id;
653 --
654 CURSOR ahl_appl_child_mrs_csr(p_mr_id IN NUMBER,
655 p_item_instance_id IN NUMBER) IS
656
657 --Priyan Changed the SQl Query for performance tuning reasons
658 --Refer to Bug # 4918807
659
660 /*SELECT distinct csi_item_instance_id
661 FROM ahl_applicable_mrs
662 WHERE mr_header_id = p_mr_id
663 AND (csi_item_instance_id = p_item_instance_id
664 OR csi_item_instance_id IN (SELECT subject_id
665 FROM csi_ii_relationships
666 START WITH object_id = p_item_instance_id
667 AND relationship_type_code = 'COMPONENT-OF'
668 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
669 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
670 CONNECT BY PRIOR subject_id = object_id
671 AND relationship_type_code = 'COMPONENT-OF'
672 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
673 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
674 ); */
675
676 /* Modified for performance in R12.0. replaced with WITH clause
677 SELECT distinct csi_item_instance_id
678 FROM ahl_applicable_mrs amr,
679 (SELECT subject_id
680 FROM csi_ii_relationships
681 START WITH object_id = p_item_instance_id
682 AND relationship_type_code = 'COMPONENT-OF'
683 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
684 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
685 CONNECT BY PRIOR subject_id = object_id
686 AND relationship_type_code = 'COMPONENT-OF'
687 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
688 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
689 UNION ALL
690 SELECT p_item_instance_id
691 FROM DUAL) cs
692 WHERE amr.mr_header_id = p_mr_id
693 AND amr.csi_item_instance_id = cs.subject_id;
694 */
695 /* performance fix.
696 WITH INST AS (SELECT subject_id csi_item_instance_id
697 FROM csi_ii_relationships
698 START WITH object_id = p_item_instance_id
699 AND relationship_type_code = 'COMPONENT-OF'
700 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
701 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
702 CONNECT BY PRIOR subject_id = object_id
703 AND relationship_type_code = 'COMPONENT-OF'
704 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
705 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
706 UNION ALL
707 SELECT p_item_instance_id csi_item_instance_id
708 FROM DUAL)
709 SELECT csi_item_instance_id
710 FROM INST
711 WHERE EXISTS (SELECT 'x'
712 FROM ahl_applicable_mrs AMR
713 WHERE amr.mr_header_id = p_mr_id
714 AND amr.csi_item_instance_id = inst.csi_item_instance_id);
715 */
716
717 SELECT subject_id csi_item_instance_id
718 FROM csi_ii_relationships
719 WHERE EXISTS (SELECT 'x'
720 FROM ahl_applicable_mrs AMR
721 WHERE amr.mr_header_id = p_mr_id
722 AND amr.csi_item_instance_id = subject_id)
723 START WITH object_id = p_item_instance_id
724 AND relationship_type_code = 'COMPONENT-OF'
725 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
726 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
727 CONNECT BY PRIOR subject_id = object_id
728 AND relationship_type_code = 'COMPONENT-OF'
729 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
730 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
731 UNION ALL
732 SELECT p_item_instance_id csi_item_instance_id
733 FROM DUAL
734 WHERE EXISTS (SELECT 'x'
735 FROM ahl_applicable_mrs AMR
736 WHERE amr.mr_header_id = p_mr_id
737 AND amr.csi_item_instance_id = p_item_instance_id);
738 --
739 CURSOR ahl_appl_mrs_csr(p_mr_id IN NUMBER,
740 p_item_instance_id IN NUMBER) IS
741 SELECT level depth_level, mr_header_id, csi_item_instance_id,
742 related_mr_header_id, related_csi_item_instance_id
743 FROM ahl_applicable_mr_relns
744 --WHERE orig_mr_header_id = p_mr_id
745 --AND orig_csi_item_instance_id = p_item_instance_id
746 START WITH mr_header_id = p_mr_id
747 AND csi_item_instance_id = p_item_instance_id
748 AND orig_mr_header_id = p_mr_id
749 AND orig_csi_item_instance_id = p_item_instance_id
750 CONNECT BY mr_header_id = PRIOR related_mr_header_id
751 AND csi_item_instance_id = PRIOR related_csi_item_instance_id
752 AND orig_mr_header_id = p_mr_id
753 AND orig_csi_item_instance_id = p_item_instance_id;
754
755 --
756 CURSOR ahl_get_depth_level_csr(p_mr_id IN NUMBER,
757 p_item_instance_id IN NUMBER,
758 p_related_mr_id IN NUMBER,
759 p_related_item_instance_id IN NUMBER,
760 p_orig_mr_id IN NUMBER,
761 p_orig_item_instance_id IN NUMBER) IS
762 SELECT NVL(tree_depth_level, 0)
763 FROM ahl_applicable_mr_relns
764 WHERE orig_mr_header_id = p_orig_mr_id
765 AND orig_csi_item_instance_id = p_orig_item_instance_id
766 AND mr_header_id = p_mr_id
767 AND csi_item_instance_id = p_item_instance_id
768 AND related_mr_header_id = p_related_mr_id
769 AND related_csi_item_instance_id = p_related_item_instance_id;
770 --
771 CURSOR ahl_duplicate_relns_csr(p_mr_id IN NUMBER,
772 p_item_instance_id IN NUMBER) IS
773 SELECT related_mr_header_id, related_csi_item_instance_id
774 FROM ahl_applicable_mr_relns
775 WHERE orig_mr_header_id = p_mr_id
776 AND orig_csi_item_instance_id = p_item_instance_id
777 GROUP BY related_mr_header_id, related_csi_item_instance_id
778 HAVING COUNT(*)>1;
779 --
780 CURSOR ahl_max_depth_reln_csr(p_mr_id IN NUMBER,
781 p_item_instance_id IN NUMBER,
782 p_orig_mr_id IN NUMBER,
783 p_orig_item_instance_id IN NUMBER) IS
784 SELECT mr_header_id, csi_item_instance_id
785 FROM ahl_applicable_mr_relns
786 WHERE tree_depth_level = (SELECT max(tree_depth_level)
787 from ahl_applicable_mr_relns
788 where orig_mr_header_id = p_orig_mr_id
789 AND orig_csi_item_instance_id = p_orig_item_instance_id
790 AND related_mr_header_id = p_mr_id
791 AND related_csi_item_instance_id=p_item_instance_id)
792 AND orig_mr_header_id = p_orig_mr_id
793 AND orig_csi_item_instance_id = p_orig_item_instance_id
794 AND related_mr_header_id = p_mr_id
795 AND related_csi_item_instance_id=p_item_instance_id;
796 --
797 l_orig_ii_id NUMBER;
798 l_orig_mr_id NUMBER;
799 l_num_of_desc NUMBER;
800 l_mr_header_id NUMBER;
801 l_related_mr_header_id NUMBER;
802 l_relationship_code VARCHAR2(30);
803 l_csi_ii_id NUMBER;
804 l_related_csi_ii_id NUMBER;
805 l_depth_level NUMBER;
806 l_appl_mr_relns_rec ahl_appl_mrs_csr%ROWTYPE;
807
808 --
809 BEGIN
810
811 -- Initialize temporary table.
812 IF (p_init_temp_table = 'Y') THEN
813 DELETE FROM AHL_APPLICABLE_MR_RELNS;
814 END IF;
815
816 l_orig_ii_id := p_top_item_instance_id;
817 l_orig_mr_id := p_top_mr_id;
818
819
820 --dbms_output.put_line(l_orig_mr_id||'::'||l_orig_ii_id);
821 --Now fetch all relations into l_mr_relns_tbl
822 --And populate the ahl_applicable_mr_relns table
823 OPEN ahl_fmp_relationships_csr(l_orig_mr_id);
824 LOOP
825 FETCH ahl_fmp_relationships_csr INTO l_mr_header_id,
826 l_related_mr_header_id,
827 l_relationship_code;
828 EXIT WHEN ahl_fmp_relationships_csr%NOTFOUND;
829
830
831
832 --For each edge of mr_relationships graph
833 --Loop through all the mr + ii combinations
834 OPEN ahl_appl_parent_mr_csr(l_mr_header_id);
835 LOOP
836 FETCH ahl_appl_parent_mr_csr INTO l_csi_ii_id;
837 EXIT WHEN ahl_appl_parent_mr_csr%NOTFOUND;
838
839 --For each mr+ii combination
840 OPEN ahl_appl_child_mrs_csr(l_related_mr_header_id, l_csi_ii_id);
841 LOOP
842 FETCH ahl_appl_child_mrs_csr INTO l_related_csi_ii_id;
843 EXIT WHEN ahl_appl_child_mrs_csr%NOTFOUND;
844
845
846 INSERT INTO AHL_APPLICABLE_MR_RELNS (
847 MR_HEADER_ID,
848 CSI_ITEM_INSTANCE_ID,
849 RELATED_MR_HEADER_ID,
850 RELATED_CSI_ITEM_INSTANCE_ID,
851 ORIG_MR_HEADER_ID,
852 ORIG_CSI_ITEM_INSTANCE_ID,
853 RELATIONSHIP_CODE
854 ) values
855 ( l_mr_header_id,
856 l_csi_ii_id,
857 l_related_mr_header_id,
858 l_related_csi_ii_id,
859 l_orig_mr_id,
860 l_orig_ii_id,
861 l_relationship_code
862 );
863 END LOOP;
864 Close ahl_appl_child_mrs_csr;
865
866 END LOOP;
867 Close ahl_appl_parent_mr_csr;
868
869 END LOOP;
870 CLOSE ahl_fmp_relationships_csr;
871
872 --Done with creating all possible applicable edges.
873 --Now fetch all relations reachable from the top node
874 OPEN ahl_appl_mrs_csr(l_orig_mr_id, l_orig_ii_id);
875 LOOP
876 FETCH ahl_appl_mrs_csr INTO l_appl_mr_relns_rec;
877 EXIT WHEN ahl_appl_mrs_csr%NOTFOUND;
878
879 OPEN ahl_get_depth_level_csr(l_appl_mr_relns_rec.mr_header_id,
880 l_appl_mr_relns_rec.csi_item_instance_id,
881 l_appl_mr_relns_rec.related_mr_header_id,
882 l_appl_mr_relns_rec.related_csi_item_instance_id,
883 l_orig_mr_id,
884 l_orig_ii_id);
885 FETCH ahl_get_depth_level_csr INTO l_depth_level;
886
887 IF (ahl_get_depth_level_csr%FOUND) THEN
888 --If depth is greater in rec, update to new depth
889 IF (l_depth_level < l_appl_mr_relns_rec.depth_level) THEN
890 UPDATE ahl_applicable_mr_relns
891 SET tree_depth_level = l_appl_mr_relns_rec.depth_level
892 WHERE orig_mr_header_id = l_orig_mr_id
893 AND orig_csi_item_instance_id = l_orig_ii_id
894 AND mr_header_id = l_appl_mr_relns_rec.mr_header_id
895 AND csi_item_instance_id = l_appl_mr_relns_rec.csi_item_instance_id
896 AND related_mr_header_id = l_appl_mr_relns_rec.related_mr_header_id
897 AND related_csi_item_instance_id = l_appl_mr_relns_rec.related_csi_item_instance_id;
898 END IF;
899 END IF; -- ahl_get_depth_level_csr%FOUND
900 CLOSE ahl_get_depth_level_csr;
901 END LOOP;
902 CLOSE ahl_appl_mrs_csr;
903
904 --Now delete all rows with null depth (unreachable)
905 DELETE FROM ahl_applicable_mr_relns
906 WHERE tree_depth_level IS NULL
907 AND orig_mr_header_id = l_orig_mr_id
908 AND orig_csi_item_instance_id = l_orig_ii_id;
909
910 --Remove all duplicates and keep deepest paths
911 OPEN ahl_duplicate_relns_csr(l_orig_mr_id, l_orig_ii_id);
912 LOOP
913 FETCH ahl_duplicate_relns_csr INTO l_related_mr_header_id, l_related_csi_ii_id;
914 EXIT WHEN ahl_duplicate_relns_csr%NOTFOUND;
915
916 OPEN ahl_max_depth_reln_csr(l_related_mr_header_id,
917 l_related_csi_ii_id,
918 l_orig_mr_id,
919 l_orig_ii_id);
920 FETCH ahl_max_depth_reln_csr INTO l_mr_header_id, l_csi_ii_id;
921 IF (ahl_max_depth_reln_csr%FOUND) THEN
922 --Delete all rows != edge with maximum depth
923 DELETE FROM ahl_applicable_mr_relns
924 WHERE (mr_header_id <> l_mr_header_id
925 OR csi_item_instance_id <> l_csi_ii_id)
926 AND orig_mr_header_id = l_orig_mr_id
927 AND orig_csi_item_instance_id = l_orig_ii_id
928 AND related_mr_header_id = l_related_mr_header_id
929 AND related_csi_item_instance_id = l_related_csi_ii_id;
930 END IF; -- ahl_max_depth_reln_csr%FOUND
931 CLOSE ahl_max_depth_reln_csr;
932 END LOOP;
933 CLOSE ahl_duplicate_relns_csr;
934
935 END Process_Group_MR_Instance;
936
937 -------------------------------------------------------------------------
938 -- Procedure to get the first accomplishment of an MR for any given item
939 -- instance. --
940 -------------------------------------------------------------------------
941 PROCEDURE get_first_accomplishment (p_csi_item_instance_id IN NUMBER,
942 p_mr_header_id IN NUMBER,
943 x_accomplishment_date OUT NOCOPY DATE,
944 x_unit_effectivity_id OUT NOCOPY NUMBER,
945 x_deferral_flag OUT NOCOPY BOOLEAN,
946 x_status_code OUT NOCOPY VARCHAR2,
947 x_return_val OUT NOCOPY BOOLEAN)
948 IS
949
950 -- cursor to get mr title, version and copy accomplishment.
951 CURSOR ahl_mr_headers_csr (p_mr_header_id IN NUMBER) IS
952 SELECT title mr_title, version_number, copy_accomplishment_flag
953 FROM ahl_mr_headers_b
954 WHERE mr_header_id = p_mr_header_id;
955
956 -- cursor to get mr title, version and copy accomplishment.
957 CURSOR ahl_mr_title_csr (p_mr_title IN VARCHAR2,
958 p_version_number IN NUMBER) IS
959 SELECT version_number, copy_accomplishment_flag, mr_header_id
960 FROM ahl_mr_headers_b
961 WHERE title = p_mr_title AND
962 version_number = p_version_number;
963
964 -- cursor to get accomplishments for current version.
965 CURSOR ahl_unit_effectivities_csr (p_csi_item_instance_id IN NUMBER,
966 p_mr_header_id IN NUMBER) IS
967 SELECT ue.accomplished_date, ue.unit_effectivity_id, ue.status_code,
968 affect_due_calc_flag, deferral_effective_on
969 FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b def
970 WHERE ue.defer_from_ue_id = def.unit_effectivity_id (+)
971 AND ue.status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED')
972 AND ue.csi_item_instance_id = p_csi_item_instance_id
973 AND ue.mr_header_id = p_mr_header_id
974 ORDER BY accomplished_date ASC;
975
976 l_accomplish_found BOOLEAN := FALSE;
977 l_mr_header_id NUMBER := p_mr_header_id;
978 l_unit_effectivity_id NUMBER;
979 l_accomplishment_date DATE;
980 l_copy_accomplishment_flag ahl_mr_headers_v.copy_accomplishment_flag%TYPE;
981 l_mr_title ahl_mr_headers_v.title%TYPE;
982 l_version_number NUMBER;
983 l_status_code ahl_unit_effectivities_vl.status_code%TYPE;
984
985 -- Added for deferral functionality.
986 l_affect_due_calc_flag VARCHAr2(1);
987 l_deferral_effective_on DATE;
988
989 BEGIN
990
991 -- Set return status.
992 x_return_val := TRUE;
993
994 -- Set deferral flag.
995 x_deferral_flag := FALSE;
996
997 l_accomplish_found := FALSE;
998
999 -- GET MR details.
1000 OPEN ahl_mr_headers_csr (p_mr_header_id);
1001 FETCH ahl_mr_headers_csr INTO l_mr_title,
1002 l_version_number,
1003 l_copy_accomplishment_flag;
1004 IF (ahl_mr_headers_csr%NOTFOUND) THEN
1005 --dbms_output.put_line ('mr_heeader_id not found');
1006 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
1007 FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
1008 FND_MSG_PUB.ADD;
1009 x_return_val := FALSE;
1010 x_accomplishment_date := null;
1011 x_unit_effectivity_id := null;
1012 x_status_code := null;
1013 CLOSE ahl_mr_headers_csr;
1014 RETURN;
1015 END IF;
1016
1017 CLOSE ahl_mr_headers_csr;
1018
1019 -- default l_copy_accomplishment_flag to Y if NULL.
1020 IF (l_copy_accomplishment_flag IS NULL) THEN
1021 l_copy_accomplishment_flag := 'Y';
1022 END IF;
1023
1024 -- Get first accomplishment for current mr revsision.
1025 OPEN ahl_unit_effectivities_csr(p_csi_item_instance_id,
1026 l_mr_header_id);
1027 FETCH ahl_unit_effectivities_csr INTO l_accomplishment_date,
1028 l_unit_effectivity_id,
1029 l_status_code,
1030 l_affect_due_calc_flag,
1031 l_deferral_effective_on;
1032
1033 IF (ahl_unit_effectivities_csr%FOUND) THEN
1034 -- dbms_output.put_line ('ue id' || l_unit_effectivity_id);
1035 -- Added for deferral enhancements.
1036 -- Use deferral_effective_on date instead of accomplishment date.
1037 IF (l_affect_due_calc_flag = 'N') THEN
1038 x_accomplishment_date := l_deferral_effective_on;
1039 x_deferral_flag := TRUE;
1040 ELSE
1041 x_accomplishment_date := l_accomplishment_date;
1042 END IF;
1043 x_unit_effectivity_id := l_unit_effectivity_id;
1044 x_status_code := l_status_code;
1045 l_accomplish_found := TRUE;
1046 ELSE
1047 x_accomplishment_date := null;
1048 x_unit_effectivity_id := null;
1049 x_status_code := null;
1050 END IF; -- unit effectivities not found
1051 CLOSE ahl_unit_effectivities_csr;
1052
1053 WHILE ((l_copy_accomplishment_flag = 'Y') AND (l_version_number > 1)) LOOP
1054
1055 -- check if the earlier version exists.
1056 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
1057 CLOSE ahl_unit_effectivities_csr;
1058 END IF;
1059 --dbms_output.put_line ('next version');
1060
1061 OPEN ahl_mr_title_csr(l_mr_title,
1062 l_version_number-1);
1063 FETCH ahl_mr_title_csr INTO l_version_number,
1064 l_copy_accomplishment_flag,
1065 l_mr_header_id;
1066 IF (ahl_mr_title_csr%NOTFOUND) THEN
1067 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_TITLE_INVALID');
1068 FND_MESSAGE.Set_Token('TITLE',l_mr_title);
1069 FND_MESSAGE.Set_Token('VERSION',l_version_number);
1070 FND_MSG_PUB.ADD;
1071 x_return_val := FALSE;
1072 x_accomplishment_date := null;
1073 x_unit_effectivity_id := null;
1074 x_status_code := null;
1075 CLOSE ahl_mr_title_csr;
1076 RETURN;
1077 END IF;
1078 CLOSE ahl_mr_title_csr;
1079
1080 -- Get first accomplishment for mr version.
1081 OPEN ahl_unit_effectivities_csr(p_csi_item_instance_id,
1082 l_mr_header_id);
1083 FETCH ahl_unit_effectivities_csr INTO l_accomplishment_date,
1084 l_unit_effectivity_id,
1085 l_status_code,
1086 l_affect_due_calc_flag,
1087 l_deferral_effective_on;
1088
1089 IF (ahl_unit_effectivities_csr%FOUND) THEN
1090 -- dbms_output.put_line ('ue id' || l_unit_effectivity_id);
1091 -- Added for deferral enhancements.
1092 -- Use deferral_effective_on date instead of accomplishment date.
1093 IF (l_affect_due_calc_flag = 'N') THEN
1094 x_accomplishment_date := l_deferral_effective_on;
1095 x_deferral_flag := TRUE;
1096 ELSE
1097 x_deferral_flag := FALSE;
1098 x_accomplishment_date := l_accomplishment_date;
1099 END IF;
1100 x_unit_effectivity_id := l_unit_effectivity_id;
1101 x_status_code := l_status_code;
1102 l_accomplish_found := TRUE;
1103
1104 END IF; -- unit effectivities not found
1105 CLOSE ahl_unit_effectivities_csr;
1106
1107 -- dbms_output.put_line ('loop again');
1108
1109 END LOOP; /* while */
1110
1111 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
1112 CLOSE ahl_unit_effectivities_csr;
1113 END IF;
1114
1115 -- dbms_output.put_line ('x_ue_id:' || x_unit_effectivity_id);
1116 -- dbms_output.put_line ('x_acc_dt:' || x_accomplishment_date);
1117
1118 END get_first_accomplishment;
1119
1120 -------------------------------------------------------------------------
1121 END AHL_UMP_UTIL_PKG;