1 PACKAGE BODY AHL_UMP_UTIL_PKG AS
2 /* $Header: AHLUUMPB.pls 120.12.12020000.3 2013/04/01 02:46:50 sracha ship $ */
3
4
5 -- Define local functions/procedures
6 PROCEDURE Populate_Config_Components (p_root_instance_id IN NUMBER);
7
8 -- Added for performance fix bug# 9434441.
9 -- number table.
10 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
11 TYPE vchar_tbl_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
12
13
14 -----------------------------------------------------------
15 -- Function to get unit configuration name for a given --
16 -- item instance. --
17 -----------------------------------------------------------
18 FUNCTION get_unitName (p_csi_item_instance_id IN NUMBER)
19 RETURN VARCHAR2
20 IS
21
22 -- Get unit name for component.
23 CURSOR get_unit_name_csr (p_csi_item_instance_id IN NUMBER) IS
24 SELECT name
25 FROM ahl_unit_config_headers uc
26 WHERE csi_item_instance_id in ( SELECT object_id
27 FROM csi_ii_relationships
28 START WITH subject_id = p_csi_item_instance_id
29 AND relationship_type_code = 'COMPONENT-OF'
30 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
31 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
32 CONNECT BY PRIOR object_id = subject_id
33 AND relationship_type_code = 'COMPONENT-OF'
34 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
35 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
36 )
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 -- For top node.
42 CURSOR get_unit_name_csr1 (p_csi_item_instance_id IN NUMBER) IS
43 SELECT name
44 FROM ahl_unit_config_headers uc
45 WHERE csi_item_instance_id = p_csi_item_instance_id
46 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
47 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
48 AND parent_uc_header_id IS NULL;
49
50 l_name ahl_unit_config_headers.name%TYPE;
51
52 begin
53
54 --Check for top node.
55 OPEN get_unit_name_csr1(p_csi_item_instance_id);
56 FETCH get_unit_name_csr1 INTO l_name;
57 IF (get_unit_name_csr1%NOTFOUND) THEN
58 -- Check for component.
59 OPEN get_unit_name_csr(p_csi_item_instance_id);
60 FETCH get_unit_name_csr INTO l_name;
61 IF (get_unit_name_csr%NOTFOUND) THEN
62 l_name := null;
63 END IF;
64 CLOSE get_unit_name_csr;
65 END IF;
66 CLOSE get_unit_name_csr1;
67
68 RETURN l_name;
69
70 end get_unitName;
71
72 -------------------------------------------------------
73 -- Function to get the children count for a group MR --
74 -------------------------------------------------------
75 FUNCTION GetCount_childUE(p_ue_id IN NUMBER)
76 RETURN NUMBER
77 IS
78 --
79 CURSOR get_count_child_csr(p_id IN NUMBER) IS
80 SELECT count(related_ue_id)
81 FROM ahl_ue_relationships
82 WHERE relationship_code = 'PARENT'
83 AND ue_id = p_id;
84 --
85 l_count NUMBER;
86 --
87 BEGIN
88 OPEN get_count_child_csr(p_ue_id);
89 FETCH get_count_child_csr INTO l_count;
90 CLOSE get_count_child_csr;
91
92 return l_count;
93 END GetCount_childUE;
94
95
96 -----------------------------------------------------------
97 -- Procedure to get Visit details for a unit effectivity --
98 -----------------------------------------------------------
99 PROCEDURE get_Visit_Details ( p_unit_effectivity_id IN NUMBER,
100 x_visit_Start_date OUT NOCOPY DATE,
101 x_visit_End_date OUT NOCOPY DATE,
102 x_visit_Assign_code OUT NOCOPY VARCHAR2)
103
104 IS
105
106 --l_ump_visit_rec AHL_VWP_VISITS_PVT.Srch_UMP_rec_type;
107 l_return_status VARCHAR2(1);
108 l_msg_count NUMBER;
109 l_msg_data VARCHAR2(2000);
110 l_visit_id NUMBER;
111
112 -- 11.5.10CU2: Ignore simulation visits.
113 CURSOR ahl_visit_csr(p_ue_id IN NUMBER) IS
114 SELECT vst.start_date_time, vst.visit_id, tsk.status_code
115 FROM ahl_visit_tasks_b tsk, (select vst1.*
116 from ahl_visits_b vst1, ahl_simulation_plans_b sim
117 where vst1.simulation_plan_id = sim.simulation_plan_id
118 and sim.primary_plan_flag = 'Y'
119 UNION ALL
120 select vst1.*
121 from ahl_visits_b vst1
122 where vst1.simulation_plan_id IS NULL) vst
123 WHERE vst.visit_id = tsk.visit_id
124 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
125 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
126 AND tsk.task_type_code = 'SUMMARY'
127 AND tsk.unit_effectivity_id = p_ue_id;
128
129 -- added to get MWO dates when UE is on the shop floor.
130 CURSOR get_wo_dt (p_unit_effectivity_id IN NUMBER) IS
131 SELECT max(scheduled_completion_date)
132 FROM wip_discrete_jobs
133 WHERE wip_entity_id IN (select wip_entity_id
134 from ahl_workorders awo, ahl_visit_tasks_b tsk
135 where awo.visit_task_id = tsk.visit_task_id
136 and tsk.task_type_code <> 'SUMMARY'
137 and awo.status_code NOT IN ('17','22','7')
138 and tsk.unit_effectivity_id = p_unit_effectivity_id);
139
140 l_mwo_end_date DATE;
141 l_tsk_status_code ahl_visit_tasks_b.status_code%TYPE;
142
143
144 begin
145
146 x_visit_End_date := null;
147 x_visit_start_date := null;
148 x_visit_assign_code := null;
149
150
151 -- Call VWP API.
152 /* AHL_VWP_VISITS_PVT.UMP_Visit_Info( p_api_version => 1.0,
153 p_init_msg_list => FND_API.G_FALSE,
154 p_commit => FND_API.G_FALSE,
155 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
156 p_unit_effectivity_id => p_unit_effectivity_id,
157 x_return_status => l_return_status,
158 x_msg_count => l_msg_count,
159 x_msg_data => l_msg_data,
160 x_ump_visit_rec => l_ump_visit_rec);
161
162
163 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
165 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
166 RAISE FND_API.G_EXC_ERROR;
167 END IF; */
168
169 open ahl_visit_csr (p_unit_effectivity_id);
170 FETCH ahl_visit_csr INTO x_visit_start_date, l_visit_id, l_tsk_status_code;
171
172
173 /* Call vwp function to get visit end date */
174
175 IF (ahl_visit_csr%FOUND) THEN
176
177 -- added check for VWP Enh (support partial p2p of MR)
178 IF (l_tsk_status_code = 'PARTIALLY RELEASED') THEN
179 l_tsk_status_code := 'RELEASED';
180 END IF;
181
182 IF (l_tsk_status_code IN ('RELEASED' ,'CLOSED')) THEN
183 -- Get workorder dates if UE has workorder.
184 OPEN get_wo_dt(p_unit_effectivity_id);
185 FETCH get_wo_dt INTO l_mwo_end_date;
186 IF (get_wo_dt%FOUND AND l_mwo_end_date IS NOT NULL) THEN
187 x_visit_End_date := l_mwo_end_date;
188 CLOSE get_wo_dt;
189 ELSE
190 CLOSE get_wo_dt;
191 x_visit_End_date := AHL_VWP_TIMES_PVT.get_visit_end_time(p_visit_id => l_visit_id,
192 p_use_actuals => FND_API.G_FALSE);
193 END IF; -- get_wo_dt
194 END IF; -- l_tsk_status_code
195 END IF;
196
197 close ahl_visit_csr;
198
199 --x_visit_start_date := l_ump_visit_rec.Visit_start_Date;
200 --x_visit_End_date := l_ump_visit_rec.Visit_End_Date;
201 --x_visit_assign_code := l_ump_visit_rec.Assign_Status_Code;
202
203 end get_Visit_Details;
204
205 -------------------------------------------------------------------------------
206 -- Function to get the visit status - planning/released/closed/ --
207 -- This procedure will be called by Process_Unit and Terminate_MR_Instances. --
208 -------------------------------------------------------------------------------
209 FUNCTION get_Visit_Status ( p_unit_effectivity_id IN NUMBER)
210
211 RETURN VARCHAR2
212
213
214 IS
215
216 --l_ump_visit_rec AHL_VWP_VISITS_PVT.Srch_UMP_rec_type;
217 l_visit_status_code AHL_VISITS_B.STATUS_CODE%TYPE;
218 l_return_status VARCHAR2(1);
219 l_msg_count NUMBER;
220 l_msg_data VARCHAR2(2000);
221
222 -- 11.5.10CU2: Ignore simulation visits.
223 CURSOR ahl_visit_csr(p_ue_id IN NUMBER) IS
224
225 SELECT decode(vst.status_code,'CLOSED', vst.status_code, tsk.status_code)
226 FROM ahl_visit_tasks_b tsk, (select vst1.*
227 from ahl_visits_b vst1, ahl_simulation_plans_b sim
228 where vst1.simulation_plan_id = sim.simulation_plan_id
229 and sim.primary_plan_flag = 'Y'
230 UNION ALL
231 select vst1.*
232 from ahl_visits_b vst1
233 where vst1.simulation_plan_id IS NULL) vst
234 WHERE vst.visit_id = tsk.visit_id
235 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
236 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
237 AND tsk.task_type_code = 'SUMMARY'
238 AND tsk.unit_effectivity_id = p_ue_id;
239 /*
240 FROM ahl_visit_tasks_b tsk, ahl_visits_b vst
241 WHERE vst.visit_id = tsk.visit_id
242 AND NVL(vst.status_code,'x') NOT IN ('DELETED','CANCELLED')
243 AND NVL(tsk.status_code,'x') NOT IN ('DELETED','CANCELLED')
244 AND tsk.unit_effectivity_id = p_ue_id;
245 */
246
247 begin
248 /*
249 -- Call VWP API.
250 AHL_VWP_VISITS_PVT.UMP_Visit_Info( p_api_version => 1.0,
251 p_init_msg_list => FND_API.G_FALSE,
252 p_commit => FND_API.G_FALSE,
253 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
254 p_unit_effectivity_id => p_unit_effectivity_id,
255 x_return_status => l_return_status,
256 x_msg_count => l_msg_count,
257 x_msg_data => l_msg_data,
258 x_ump_visit_rec => l_ump_visit_rec);
259
260 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265
266 */
267
268 open ahl_visit_csr (p_unit_effectivity_id);
269 FETCH ahl_visit_csr INTO l_visit_status_code;
270 IF (ahl_visit_csr%FOUND) THEN
271 -- added check for VWP Enh (support partial p2p of MR)
272 IF (l_visit_status_code = 'PARTIALLY RELEASED') THEN
273 l_visit_status_code := 'RELEASED';
274 END IF;
275 END IF;
276 close ahl_visit_csr;
277
278 -- return visit status.
279 --RETURN l_ump_visit_rec.Visit_Status_Code;
280 RETURN l_visit_status_code;
281
282
283 end get_Visit_Status;
284
285 -------------------------------------------------------------------------
286 -- Procedure to get the last accomplishment of an MR for any given item
287 -- instance. --
288 -------------------------------------------------------------------------
289 PROCEDURE get_last_accomplishment (p_csi_item_instance_id IN NUMBER,
290 p_mr_header_id IN NUMBER,
291 x_accomplishment_date OUT NOCOPY DATE,
292 x_unit_effectivity_id OUT NOCOPY NUMBER,
293 x_deferral_flag OUT NOCOPY BOOLEAN,
294 x_status_code OUT NOCOPY VARCHAR2,
295 x_return_val OUT NOCOPY BOOLEAN)
296 IS
297
298 -- cursor to get mr title, version and copy accomplishment.
299 CURSOR ahl_mr_headers_csr (p_mr_header_id IN NUMBER) IS
300 SELECT title mr_title, version_number, copy_accomplishment_flag
301 FROM ahl_mr_headers_b
302 WHERE mr_header_id = p_mr_header_id;
303
304 -- cursor to get mr title, version and copy accomplishment.
305 CURSOR ahl_mr_title_csr (p_mr_title IN VARCHAR2,
306 p_version_number IN NUMBER) IS
307 SELECT version_number, copy_accomplishment_flag, mr_header_id
308 FROM ahl_mr_headers_b
309 WHERE title = p_mr_title AND
310 version_number = p_version_number;
311
312 -- cursor to get accomplishments for current version.
313 CURSOR ahl_unit_effectivities_csr (p_csi_item_instance_id IN NUMBER,
314 p_mr_header_id IN NUMBER) IS
315 SELECT * FROM (
316 SELECT ue.accomplished_date, ue.unit_effectivity_id, ue.status_code,
317 decode(ue.status_code, 'TERMINATED', ter.affect_due_calc_flag, def.affect_due_calc_flag),
318 decode(ue.status_code, 'TERMINATED', ter.deferral_effective_on, def.deferral_effective_on)
319 FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b def, ahl_unit_deferrals_b ter
320 WHERE ue.defer_from_ue_id = def.unit_effectivity_id (+)
321 AND ue.unit_effectivity_id = ter.unit_effectivity_id(+)
322 AND ue.status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED','TERMINATED')
323 AND def.unit_deferral_type(+) = 'DEFERRAL'
324 AND ter.unit_deferral_type(+) = 'DEFERRAL'
325 AND ue.csi_item_instance_id = p_csi_item_instance_id
326 AND ue.mr_header_id = p_mr_header_id
327 --ORDER BY accomplished_date DESC;
328 ORDER BY decode (ue.status_code, 'TERMINATED', ter.deferral_effective_on, ue.accomplished_date) DESC
329 )
330 WHERE ROWNUM < 2;
331
332 l_accomplish_found BOOLEAN := FALSE;
333 l_mr_header_id NUMBER := p_mr_header_id;
334 l_unit_effectivity_id NUMBER;
335 l_accomplishment_date DATE;
336 l_copy_accomplishment_flag ahl_mr_headers_b.copy_accomplishment_flag%TYPE;
337 l_mr_title ahl_mr_headers_b.title%TYPE;
338 l_version_number NUMBER;
339 l_status_code ahl_unit_effectivities_b.status_code%TYPE;
340
341 -- Added for deferral functionality.
342 l_affect_due_calc_flag VARCHAr2(1);
343 l_deferral_effective_on DATE;
344
345 BEGIN
346 -- Set return status.
347 x_return_val := TRUE;
348
349 -- Set deferral flag.
350 x_deferral_flag := FALSE;
351
352 -- GET MR details.
353 OPEN ahl_mr_headers_csr (p_mr_header_id);
354 FETCH ahl_mr_headers_csr INTO l_mr_title,
355 l_version_number,
356 l_copy_accomplishment_flag;
357 IF (ahl_mr_headers_csr%NOTFOUND) THEN
358 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
359 FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
360 FND_MSG_PUB.ADD;
361 x_return_val := FALSE;
362 x_accomplishment_date := null;
363 x_unit_effectivity_id := null;
364 x_status_code := null;
365 CLOSE ahl_mr_headers_csr;
366 RETURN;
367 END IF;
368 CLOSE ahl_mr_headers_csr;
369
370 -- pick the most recent accomplishment from previous version.
371 l_accomplish_found := FALSE;
372 WHILE NOT(l_accomplish_found) LOOP
373 -- Get last accomplishment.
374 OPEN ahl_unit_effectivities_csr(p_csi_item_instance_id,
375 l_mr_header_id);
376 FETCH ahl_unit_effectivities_csr INTO l_accomplishment_date,
377 l_unit_effectivity_id,
378 l_status_code,
379 l_affect_due_calc_flag,
380 l_deferral_effective_on;
381
382 IF (ahl_unit_effectivities_csr%FOUND) THEN
383 --dbms_output.put_line ('ue id' || l_unit_effectivity_id);
384 -- Added for deferral enhancements.
385 -- Use deferral_effective_on date instead of accomplishment date.
386 IF (l_affect_due_calc_flag = 'N') THEN
387 l_accomplishment_date := l_deferral_effective_on;
388 x_deferral_flag := TRUE;
389 END IF;
390 x_accomplishment_date := l_accomplishment_date;
391 x_unit_effectivity_id := l_unit_effectivity_id;
392 x_status_code := l_status_code;
393 l_accomplish_found := TRUE;
394 CLOSE ahl_unit_effectivities_csr;
395 ELSE
396 -- find accomplishments from last mr revision based on copy accomplishment flag.
397 IF (l_copy_accomplishment_flag = 'N') THEN
398 --dbms_output.put_line ('copy_accomplishment_flag' || l_copy_accomplishment_flag );
399 x_accomplishment_date := null;
400 x_unit_effectivity_id := null;
401 x_status_code := null;
402 l_accomplish_found := TRUE;
403 ELSE
404 -- check if any more versions available.
405 IF (l_version_number = 1) THEN
406 --dbms_output.put_line ('version_number = 1' );
407 x_accomplishment_date := null;
408 x_unit_effectivity_id := null;
409 x_status_code := null;
410 l_accomplish_found := TRUE;
411 ELSE
412 -- check if the earlier version exists.
413 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
414 CLOSE ahl_unit_effectivities_csr;
415 END IF;
416 --dbms_output.put_line ('next version');
417 OPEN ahl_mr_title_csr(l_mr_title,
418 l_version_number-1);
419 FETCH ahl_mr_title_csr INTO l_version_number,
420 l_copy_accomplishment_flag,
421 l_mr_header_id;
422 IF (ahl_mr_title_csr%NOTFOUND) THEN
423 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_TITLE_INVALID');
424 FND_MESSAGE.Set_Token('TITLE',l_mr_title);
425 FND_MESSAGE.Set_Token('VERSION',l_version_number);
426 FND_MSG_PUB.ADD;
427 x_return_val := FALSE;
428 x_accomplishment_date := null;
429 x_unit_effectivity_id := null;
430 x_status_code := null;
431 CLOSE ahl_mr_title_csr;
432 RETURN;
433 END IF;
434 CLOSE ahl_mr_title_csr;
435 END IF; /* version number */
436 END IF; /* l_copy accomplishment flag */
437 END IF; /* unit effectivities not found */
438 --dbms_output.put_line ('loop again');
439 END LOOP; /* while */
440
441 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
442 CLOSE ahl_unit_effectivities_csr;
443 END IF;
444
445 END get_last_accomplishment;
446
447 -----------------------------------------------------------------------------
448 --
449 -- Is_UE_In_Execution
450 -- Checks if the unit effectivity (item instance and MR) is currently
451 -- in execution by calling the get_Visit_Status procedure given above.
452 -- Returns TRUE even if any of the descendents (group MR) is in execution.
453 -- Used by Capture_MR_Updates before terminating an UE
454 --
455 -- Input (Mandatory)
456 -- p_ue_id: NUMBER Unit Effectivity Id
457 --
458 -----------------------------------------------------------------------------
459 FUNCTION Is_UE_In_Execution
460 (
461 p_ue_id NUMBER) return boolean IS
462
463 BEGIN
464
465 IF ( nvl(get_visit_status(p_ue_id),'X') NOT IN ('RELEASED', 'CLOSED') ) THEN
466 RETURN FALSE;
467 ELSE
468 RETURN TRUE;
469 END IF;
470
471
472 END Is_UE_In_Execution;
473
474
475 ---------------------------------------------------------------------
476 -- Procedure to get Service Request details for a unit effectivity --
477 -- Used in Preventive Maintenance mode only --
478 ---------------------------------------------------------------------
479 PROCEDURE get_ServiceRequest_Details (p_unit_effectivity_id IN NUMBER,
480 x_incident_id OUT NOCOPY NUMBER,
481 x_incident_number OUT NOCOPY VARCHAR2,
482 x_scheduled_date OUT NOCOPY DATE)
483 IS
484
485 CURSOR get_SR_details_csr (p_unit_effectivity_id IN NUMBER,
486 p_cs_link_id IN NUMBER) IS
487 SELECT inc.incident_number, inc.incident_id
488 FROM cs_incident_links link, cs_incidents_all_vl inc
489 WHERE link.subject_id = inc.incident_id
490 AND subject_type = 'SR'
491 AND link_type_id = p_cs_link_id
492 AND object_type = 'AHL_UMP_EFF'
493 AND object_id = p_unit_effectivity_id;
494
495 c_cs_link_id CONSTANT NUMBER := 6;
496 -- This link-id is seeded in cs_link_types_b and
497 -- points to link type code = 'Reference'.
498
499 BEGIN
500
501 -- Initialize.
502 x_scheduled_date := null;
503
504 -- Get Service request details.
505 OPEN get_SR_details_csr(p_unit_effectivity_id, c_cs_link_id);
506 FETCH get_SR_details_csr INTO x_incident_number, x_incident_id;
507 CLOSE get_SR_details_csr;
508
509 END get_ServiceRequest_Details;
510
511 -----------------------------------------------------------------------
512 -- Start of Comments --
513 -- Procedure name : Populate_Appl_MRs
514 -- Type : Private
515 -- Function : Calls FMP and populates the AHL_APPLICABLE_MRS table.
516 -- Pre-reqs :
517 -- Parameters :
518 --
519 -- Populate_Appl_MRs Parameters:
520 -- p_csi_ii_id IN csi item instance id Required
521 --
522 -- Version :
523 -- Initial Version 1.0
524 --
525 -- End of Comments.
526
527
528 PROCEDURE Populate_Appl_MRs (
529 p_csi_ii_id IN NUMBER,
530 p_include_doNotImplmt IN VARCHAR2 := 'Y',
531 x_return_status OUT NOCOPY VARCHAR2,
532 x_msg_count OUT NOCOPY NUMBER,
533 x_msg_data OUT NOCOPY VARCHAR2)
534 IS
535 l_api_version CONSTANT NUMBER := 1.0;
536 l_appl_mrs_tbl AHL_FMP_PVT.applicable_mr_tbl_type;
537
538 --apattark start for SB effectivity changes
539 l_mr_header_count NUMBER;
540 l_process_flag VARCHAR2(1) := 'N';
541 l_item_instance_tbl nbr_tbl_type;
542 l_item_instance_count_tbl nbr_tbl_type;
543 l_start_mr_relns AHL_MR_LOOP_CHAIN_RELNS_PVT.MR_CHAIN_LOOP_TBL_TYPE;
544 l_return_status VARCHAR2(50);
545 l_msg_count NUMBER;
546 l_msg_data VARCHAR2(50);
547 l_mr_header_id NUMBER;
548 l_rel_code VARCHAR2(30) := null;
549 l_mr_item_inst_tbl AHL_FMP_PVT.MR_ITEM_INSTANCE_TBL_TYPE;
550 l_applicable_mr_tbl nbr_tbl_type;
551 l_valid_mr_id NUMBER;
552 l_mr_valid_flag VARCHAR2(1);
553
554 CURSOR get_start_mr_header_id IS
555 SELECT DISTINCT MR_HEADER_ID
556 FROM AHL_APPLICABLE_MRS
557 WHERE START_MR_HEADER_ID = MR_HEADER_ID
558 AND ACCOMPLISH_TRIGGER_TYPE IN ('LOOP', 'CHAIN');
559
560 CURSOR get_mr_header_id(c_item_instance_id IN NUMBER) IS
561 SELECT MR_HEADER_ID
562 FROM AHL_APPLICABLE_MRS
563 WHERE CSI_ITEM_INSTANCE_ID = c_item_instance_id
564 AND ACCOMPLISH_TRIGGER_TYPE IN ('LOOP', 'CHAIN');
565 --apattark end for SB effectivity changes
566
567 BEGIN
568
569 -- Initialize temporary table.
570 DELETE FROM AHL_APPLICABLE_MRS;
571
572 -- call api to fetch all applicable mrs for ASO installation.
573 AHL_FMP_PVT.get_applicable_mrs(
574 p_api_version => l_api_version,
575 p_init_msg_list => FND_API.G_FALSE,
576 p_commit => FND_API.G_FALSE,
577 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
578 x_return_status => x_return_status,
579 x_msg_count => x_msg_count,
580 x_msg_data => x_msg_data,
581 p_item_instance_id => p_csi_ii_id,
582 p_components_flag => 'Y',
583 p_include_doNotImplmt => p_include_doNotImplmt,
584 -- fix perf bug# 13629335
585 p_insert_into_tmp_tbl => 'Y',
586 x_applicable_mr_tbl => l_appl_mrs_tbl);
587
588
589 -- Raise errors if exceptions occur
590 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
591 RAISE FND_API.G_EXC_ERROR;
592 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 END IF;
595
596 -- fix perf bug# 13629335
597 -- commented following code as this is done by ahl_fmp_pvt.get_applicable_mrs API.
598 /*
599 -- Populate temporary table ahl_applicable_mrs.
600 IF (l_appl_mrs_tbl.COUNT > 0) THEN
601 FOR i IN l_appl_mrs_tbl.FIRST..l_appl_mrs_tbl.LAST LOOP
602 -- dbms_output.put_line( l_appl_mrs_tbl(i).item_instance_id||' '||
603 -- l_appl_mrs_tbl(i).mr_header_id);
604 INSERT INTO AHL_APPLICABLE_MRS (
605 CSI_ITEM_INSTANCE_ID,
606 MR_HEADER_ID,
607 MR_EFFECTIVITY_ID,
608 REPETITIVE_FLAG ,
609 SHOW_REPETITIVE_CODE,
610 COPY_ACCOMPLISHMENT_CODE,
611 PRECEDING_MR_HEADER_ID,
612 IMPLEMENT_STATUS_CODE,
613 DESCENDENT_COUNT,
614 ACCOMPLISH_TRIGGER_TYPE,
615 START_MR_HEADER_ID,
616 LOOP_CHAIN_SEQ_NUM,
617 PROCESS_STATUS_FLAG,
618 PROCESSING_ORDER
619 ) values
620 (l_appl_mrs_tbl(i).item_instance_id,
621 l_appl_mrs_tbl(i).mr_header_id,
622 l_appl_mrs_tbl(i).mr_effectivity_id,
623 l_appl_mrs_tbl(i).repetitive_flag,
624 l_appl_mrs_tbl(i).show_repetitive_code,
625 l_appl_mrs_tbl(i).copy_accomplishment_flag,
626 l_appl_mrs_tbl(i).preceding_mr_header_id,
627 l_appl_mrs_tbl(i).implement_status_code,
628 l_appl_mrs_tbl(i).descendent_count,
629 l_appl_mrs_tbl(i).relationship_code,
630 l_appl_mrs_tbl(i).start_mr_header_id,
631 l_appl_mrs_tbl(i).sequence_number,
632 'N', -- default
633 1 -- default
634 );
635 END LOOP;
636 END IF;
637 */
638
639 -- delete loop/chain data that do not have loop / chain seq #1
640 UPDATE AHL_APPLICABLE_MRS apmr
641 SET accomplish_trigger_type = null,
642 start_mr_header_id = null
643 WHERE start_mr_header_id is not null
644 AND not exists (select 'x' from AHL_APPLICABLE_MRS apmr1
645 where apmr1.csi_item_instance_id = apmr.csi_item_instance_id
646 and apmr1.mr_header_id = apmr.start_mr_header_id
647 and apmr1.accomplish_trigger_type = apmr.accomplish_trigger_type
648 and apmr1.loop_chain_seq_num = 1);
649
650 -- apattark changes for SB Effectivity.
651 FOR l_index IN get_start_mr_header_id LOOP
652
653 l_mr_header_id := l_index.mr_header_id;
654 AHL_MR_LOOP_CHAIN_RELNS_PVT.get_valid_loop_chain_relns(l_mr_header_id, SYSDATE,SYSDATE, l_rel_code,
655 l_return_status,
656 l_msg_count,
657 l_msg_data,
658 l_start_mr_relns);
659
660 IF (l_start_mr_relns.count > 0) THEN
661 SELECT DISTINCT CSI_ITEM_INSTANCE_ID BULK COLLECT INTO l_item_instance_tbl
662 FROM AHL_APPLICABLE_MRS
663 WHERE MR_HEADER_ID = l_start_mr_relns(0).mr_header_id;
664
665 IF (l_item_instance_tbl.count > 0) THEN
666 FOR m IN l_item_instance_tbl.FIRST..l_item_instance_tbl.LAST
667 LOOP
668 OPEN get_mr_header_id(l_item_instance_tbl(m));
669 FETCH get_mr_header_id BULK COLLECT INTO l_applicable_mr_tbl;
670 CLOSE get_mr_header_id;
671
672 l_process_flag := 'Y';
673
674 FOR i in l_start_mr_relns.FIRST..l_start_mr_relns.LAST LOOP
675 l_valid_mr_id := l_start_mr_relns(i).mr_header_id;
676 l_mr_valid_flag := 'N';
677 FOR j in l_applicable_mr_tbl.FIRST..l_applicable_mr_tbl.LAST loop
678 IF l_valid_mr_id = l_applicable_mr_tbl(j) THEN
679 l_mr_valid_flag := 'Y';
680 EXIT;
681 END IF;
682 END LOOP;
683 IF l_mr_valid_flag = 'N' THEN
684 l_process_flag := 'N';
685 EXIT;
686 END IF;
687 END LOOP;
688 IF l_process_flag = 'Y' THEN
689 EXIT;
690 END IF;
691 END LOOP;
692 END IF; -- l_item_instance_tbl.count
693 FOR k IN l_start_mr_relns.FIRST..l_start_mr_relns.LAST LOOP
694 IF (l_process_flag = 'N') THEN
695 UPDATE AHL_APPLICABLE_MRS
696 SET ACCOMPLISH_TRIGGER_TYPE = null
697 WHERE MR_HEADER_ID = l_start_mr_relns(k).MR_HEADER_ID
698 AND ACCOMPLISH_TRIGGER_TYPE IN ('LOOP','CHAIN') ;
699 END IF;
700 END LOOP;
701 l_process_flag := 'N';
702 END IF; -- l_start_mr_relns.count
703 END LOOP;
704 -- apattark changes for SB Effectivity end.
705
706
707 END Populate_Appl_MRs;
708
709 --------------------------------------------------------------------
710 PROCEDURE Process_Group_MRs
711 IS
712 --
713 CURSOR ahl_applicable_mrs_csr IS
714 SELECT distinct mr_header_id, csi_item_instance_id
715 FROM ahl_applicable_mrs
716 WHERE descendent_count > 0;
717
718 --
719 l_mr_header_id NUMBER;
720 l_csi_ii_id NUMBER;
721 --
722 BEGIN
723
724 -- Initialize temporary table.
725 DELETE FROM AHL_APPLICABLE_MR_RELNS;
726
727 OPEN ahl_applicable_mrs_csr;
728 LOOP
729 FETCH ahl_applicable_mrs_csr INTO l_mr_header_id,
730 l_csi_ii_id;
731 EXIT WHEN ahl_applicable_mrs_csr%NOTFOUND;
732 process_group_mr_instance(
733 p_top_mr_id => l_mr_header_id,
734 p_top_item_instance_id => l_csi_ii_id);
735 END LOOP;
736 CLOSE ahl_applicable_mrs_csr;
737
738 END Process_Group_MRs;
739
740 -----------------------------------------------------------------------
741 -- Start of Comments --
742 -- Procedure name : Process_group_mr_instance
743 -- Type : Private
744 -- Function : Generate relationships for one mr+item instance combination.
745 -- Pre-reqs :
746 -- Parameters :
747 --
748 -- Populate_Appl_MRs Parameters:
749 -- p_top_item_instance_id IN csi item instance id Required
750 -- p_top_mr_id IN top mr id Required
751 --
752 -- Version :
753 -- Initial Version 1.0
754 --
755 -- End of Comments.
756
757
758 PROCEDURE Process_Group_MR_Instance (
759 p_top_mr_id IN NUMBER,
760 p_top_item_instance_id IN NUMBER,
761 p_init_temp_table IN VARCHAR2 DEFAULT 'N')
762 IS
763 --
764 -- cursor that selects all distinct, valid mr relationships
765 CURSOR ahl_fmp_relationships_csr(p_mr_id IN NUMBER) IS
766
767 /*
768 SELECT distinct r.mr_header_id, r.related_mr_header_id,
769 r.relationship_code
770 FROM ahl_mr_relationships r
771 WHERE EXISTS (SELECT 'x'
772 FROM AHL_MR_HEADERS_B b1, AHL_MR_HEADERS_B b2
773 WHERE b1.mr_header_id = r.mr_header_id
774 AND b2.mr_header_id = r.related_mr_header_id
775 AND b1.mr_status_code = 'COMPLETE'
776 AND b2.mr_status_code = 'COMPLETE'
777 AND NVL(b1.effective_from, SYSDATE) <= SYSDATE
778 AND NVL(b2.effective_from, SYSDATE) <= SYSDATE
779 AND NVL(b1.effective_to, SYSDATE+1) >= SYSDATE
780 AND NVL(b2.effective_to, SYSDATE+1) >= SYSDATE)
781 START WITH r.mr_header_id = p_mr_id
782 CONNECT BY r.mr_header_id = PRIOR r.related_mr_header_id
783 AND r.relationship_code = 'PARENT';
784 */
785
786 -- added hint as per Apps Perf team feedback to fix perf bug# 13629335
787 SELECT distinct r.mr_header_id, r.related_mr_header_id,
788 r.relationship_code
789 FROM ahl_mr_relationships r
790 START WITH r.mr_header_id = p_mr_id
791 AND r.relationship_code = 'PARENT'
792 AND exists (select /*+ push_subq no_unnest */ 'x' from ahl_mr_headers_b mr1
793 where mr1.mr_header_id = r.related_mr_header_id
794 and mr1.version_number = (select max(mr2.version_number)
795 from ahl_mr_headers_b mr2
796 where mr2.title = mr1.title
797 and mr2.mr_status_code = 'COMPLETE'
798 and SYSDATE between trunc(mr2.effective_from)
799 and trunc(nvl(mr2.effective_to,SYSDATE+1))
800 )
801 )
802 CONNECT BY r.mr_header_id = PRIOR r.related_mr_header_id
803 AND r.relationship_code = 'PARENT'
804 AND exists (select /*+ push_subq no_unnest */ 'x' from ahl_mr_headers_b mr1
805 where mr1.mr_header_id = r.related_mr_header_id
806 and mr1.version_number = (select max(mr2.version_number)
807 from ahl_mr_headers_b mr2
808 where mr2.title = mr1.title
809 and mr2.mr_status_code = 'COMPLETE'
810 and SYSDATE between trunc(mr2.effective_from)
811 and trunc(nvl(mr2.effective_to,SYSDATE+1))
812 )
813 );
814
815 --
816 -- added query for subj_child_exists to fix perf bug# 13629335
817 CURSOR ahl_appl_parent_mr_csr(p_mr_id IN NUMBER) IS
818
819 SELECT csi_item_instance_id,
820 (select subj_child_exists from ahl_config_components
821 where subject_id = csi_item_instance_id) child_exists
822 FROM (SELECT distinct csi_item_instance_id
823 FROM ahl_applicable_mrs
824 WHERE mr_header_id = p_mr_id);
825 --
826 CURSOR ahl_appl_child_mrs_csr(p_mr_id IN NUMBER,
827 p_item_instance_id IN NUMBER) IS
828
829 --Priyan Changed the SQl Query for performance tuning reasons
830 --Refer to Bug # 4918807
831
832 /*SELECT distinct csi_item_instance_id
833 FROM ahl_applicable_mrs
834 WHERE mr_header_id = p_mr_id
835 AND (csi_item_instance_id = p_item_instance_id
836 OR csi_item_instance_id IN (SELECT subject_id
837 FROM csi_ii_relationships
838 START WITH object_id = p_item_instance_id
839 AND relationship_type_code = 'COMPONENT-OF'
840 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
841 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
842 CONNECT BY PRIOR subject_id = object_id
843 AND relationship_type_code = 'COMPONENT-OF'
844 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
845 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))
846 ); */
847
848 /* Modified for performance in R12.0. replaced with WITH clause
849 SELECT distinct csi_item_instance_id
850 FROM ahl_applicable_mrs amr,
851 (SELECT subject_id
852 FROM csi_ii_relationships
853 START WITH object_id = p_item_instance_id
854 AND relationship_type_code = 'COMPONENT-OF'
855 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
856 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
857 CONNECT BY PRIOR subject_id = object_id
858 AND relationship_type_code = 'COMPONENT-OF'
859 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
860 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
861 UNION ALL
862 SELECT p_item_instance_id
863 FROM DUAL) cs
864 WHERE amr.mr_header_id = p_mr_id
865 AND amr.csi_item_instance_id = cs.subject_id;
866 */
867 /* performance fix.
868 WITH INST AS (SELECT subject_id csi_item_instance_id
869 FROM csi_ii_relationships
870 START WITH object_id = p_item_instance_id
871 AND relationship_type_code = 'COMPONENT-OF'
872 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
873 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
874 CONNECT BY PRIOR subject_id = object_id
875 AND relationship_type_code = 'COMPONENT-OF'
876 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
877 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
878 UNION ALL
879 SELECT p_item_instance_id csi_item_instance_id
880 FROM DUAL)
881 SELECT csi_item_instance_id
882 FROM INST
883 WHERE EXISTS (SELECT 'x'
884 FROM ahl_applicable_mrs AMR
885 WHERE amr.mr_header_id = p_mr_id
886 AND amr.csi_item_instance_id = inst.csi_item_instance_id);
887 */
888
889 /* perf tuning: bug# 9434441
890 SELECT subject_id csi_item_instance_id
891 FROM csi_ii_relationships
892 WHERE EXISTS (SELECT 'x'
893 FROM ahl_applicable_mrs AMR
894 WHERE amr.mr_header_id = p_mr_id
895 AND amr.csi_item_instance_id = subject_id)
896 START WITH object_id = p_item_instance_id
897 AND relationship_type_code = 'COMPONENT-OF'
898 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
899 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
900 CONNECT BY PRIOR subject_id = object_id
901 AND relationship_type_code = 'COMPONENT-OF'
902 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
903 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
904 UNION ALL
905 SELECT p_item_instance_id csi_item_instance_id
906 FROM DUAL
907 WHERE EXISTS (SELECT 'x'
908 FROM ahl_applicable_mrs AMR
909 WHERE amr.mr_header_id = p_mr_id
910 AND amr.csi_item_instance_id = p_item_instance_id);
911 */
912
913 -- added hint as per Apps Perf team feedback to fix perf bug# 13629335
914 -- this SQL will be executed only if subj_child_exists = Y
915 SELECT /*+ push_subq */ subject_id csi_item_instance_id
916 FROM ahl_config_components
917 WHERE EXISTS (SELECT /*+ push_subq */ 'x'
918 FROM ahl_applicable_mrs AMR
919 WHERE amr.mr_header_id = p_mr_id
920 AND amr.csi_item_instance_id = subject_id)
921 START WITH object_id = p_item_instance_id
922 CONNECT BY PRIOR subject_id = object_id
923 UNION ALL
924 SELECT p_item_instance_id csi_item_instance_id
925 FROM DUAL
926 WHERE EXISTS (SELECT /*+ push_subq */ 'x'
927 FROM ahl_applicable_mrs AMR
928 WHERE amr.mr_header_id = p_mr_id
929 AND amr.csi_item_instance_id = p_item_instance_id);
930
931 --
932 -- added to fix perf bug# 13629335
933 -- get applicability for a given mr_id and ii_id.
934 CURSOR ahl_appl_ii_csr(p_mr_id IN NUMBER,
935 p_item_instance_id IN NUMBER) IS
936
937 SELECT p_item_instance_id csi_item_instance_id
938 FROM DUAL
939 WHERE EXISTS (SELECT /*+ push_subq */ 'x'
940 FROM ahl_applicable_mrs AMR
941 WHERE amr.mr_header_id = p_mr_id
942 AND amr.csi_item_instance_id = p_item_instance_id);
943 --
944 -- added to fix perf bug# 13629335
945 -- ahl_config_components contains all components for root object id only.
946 -- For root object Id case, get all child instances with applicability for a given mr_id
947 CURSOR ahl_appl_all_csr(p_mr_id IN NUMBER,
948 p_item_instance_id IN NUMBER) IS
949 SELECT /*+ push_subq */ cmp.subject_id csi_item_instance_id
950 FROM ahl_config_components cmp
951 WHERE EXISTS (SELECT /*+ push_subq */ 'x'
952 FROM ahl_applicable_mrs AMR
953 WHERE amr.mr_header_id = p_mr_id
954 AND amr.csi_item_instance_id = cmp.subject_id)
955 UNION ALL
956 SELECT p_item_instance_id subject_id
957 FROM DUAL
958 WHERE EXISTS (SELECT /*+ push_subq */ 'x'
959 FROM ahl_applicable_mrs AMR
960 WHERE amr.mr_header_id = p_mr_id
961 AND amr.csi_item_instance_id = p_item_instance_id);
962
963 --
964
965 CURSOR ahl_appl_mrs_csr(p_mr_id IN NUMBER,
966 p_item_instance_id IN NUMBER) IS
967 SELECT level depth_level, mr_header_id, csi_item_instance_id,
968 related_mr_header_id, related_csi_item_instance_id
969 FROM ahl_applicable_mr_relns
970 --WHERE orig_mr_header_id = p_mr_id
971 --AND orig_csi_item_instance_id = p_item_instance_id
972 START WITH mr_header_id = p_mr_id
973 AND csi_item_instance_id = p_item_instance_id
974 AND orig_mr_header_id = p_mr_id
975 AND orig_csi_item_instance_id = p_item_instance_id
976 CONNECT BY mr_header_id = PRIOR related_mr_header_id
977 AND csi_item_instance_id = PRIOR related_csi_item_instance_id
978 AND orig_mr_header_id = p_mr_id
979 AND orig_csi_item_instance_id = p_item_instance_id;
980
981 --
982 CURSOR ahl_get_depth_level_csr(p_mr_id IN NUMBER,
983 p_item_instance_id IN NUMBER,
984 p_related_mr_id IN NUMBER,
985 p_related_item_instance_id IN NUMBER,
986 p_orig_mr_id IN NUMBER,
987 p_orig_item_instance_id IN NUMBER) IS
988 SELECT NVL(tree_depth_level, 0)
989 FROM ahl_applicable_mr_relns
990 WHERE orig_mr_header_id = p_orig_mr_id
991 AND orig_csi_item_instance_id = p_orig_item_instance_id
992 AND mr_header_id = p_mr_id
993 AND csi_item_instance_id = p_item_instance_id
994 AND related_mr_header_id = p_related_mr_id
995 AND related_csi_item_instance_id = p_related_item_instance_id;
996 --
997 CURSOR ahl_duplicate_relns_csr(p_mr_id IN NUMBER,
998 p_item_instance_id IN NUMBER) IS
999 SELECT related_mr_header_id, related_csi_item_instance_id
1000 FROM ahl_applicable_mr_relns
1001 WHERE orig_mr_header_id = p_mr_id
1002 AND orig_csi_item_instance_id = p_item_instance_id
1003 GROUP BY related_mr_header_id, related_csi_item_instance_id
1004 HAVING COUNT(*)>1;
1005 --
1006 CURSOR ahl_max_depth_reln_csr(p_mr_id IN NUMBER,
1007 p_item_instance_id IN NUMBER,
1008 p_orig_mr_id IN NUMBER,
1009 p_orig_item_instance_id IN NUMBER) IS
1010 SELECT mr_header_id, csi_item_instance_id
1011 FROM ahl_applicable_mr_relns
1012 WHERE tree_depth_level = (SELECT max(tree_depth_level)
1013 from ahl_applicable_mr_relns
1014 where orig_mr_header_id = p_orig_mr_id
1015 AND orig_csi_item_instance_id = p_orig_item_instance_id
1016 AND related_mr_header_id = p_mr_id
1017 AND related_csi_item_instance_id=p_item_instance_id)
1018 AND orig_mr_header_id = p_orig_mr_id
1019 AND orig_csi_item_instance_id = p_orig_item_instance_id
1020 AND related_mr_header_id = p_mr_id
1021 AND related_csi_item_instance_id=p_item_instance_id;
1022 --
1023 l_orig_ii_id NUMBER;
1024 l_orig_mr_id NUMBER;
1025 l_num_of_desc NUMBER;
1026 l_mr_header_id NUMBER;
1027 l_related_mr_header_id NUMBER;
1028 l_relationship_code VARCHAR2(30);
1029 l_csi_ii_id NUMBER;
1030 l_related_csi_ii_id NUMBER;
1031 l_depth_level NUMBER;
1032 l_appl_mr_relns_rec ahl_appl_mrs_csr%ROWTYPE;
1033
1034 -- added for perf fix 9434441
1035 l_related_csi_ii_tbl nbr_tbl_type;
1036 l_mr_header_tbl nbr_tbl_type;
1037 l_related_mr_header_tbl nbr_tbl_type;
1038 l_relationship_code_tbl vchar_tbl_type;
1039
1040 l_buffer_limit number := 1000;
1041
1042 -- added to fix perf bug# 13629335
1043 l_subj_child_exists VARCHAR2(3);
1044
1045 --
1046 BEGIN
1047
1048 -- Initialize temporary table.
1049 IF (p_init_temp_table = 'Y') THEN
1050 DELETE FROM AHL_APPLICABLE_MR_RELNS;
1051
1052 -- Populate config temp table.
1053 -- fix for perf bug# 9434441
1054 Populate_Config_Components (p_top_item_instance_id);
1055
1056 END IF;
1057
1058 l_orig_ii_id := p_top_item_instance_id;
1059 l_orig_mr_id := p_top_mr_id;
1060
1061
1062 --dbms_output.put_line(l_orig_mr_id||'::'||l_orig_ii_id);
1063 --Now fetch all relations into l_mr_relns_tbl
1064 --And populate the ahl_applicable_mr_relns table
1065 OPEN ahl_fmp_relationships_csr(l_orig_mr_id);
1066 LOOP
1067 --FETCH ahl_fmp_relationships_csr INTO l_mr_header_id,
1068 -- l_related_mr_header_id,
1069 -- l_relationship_code;
1070
1071 FETCH ahl_fmp_relationships_csr BULK COLLECT INTO l_mr_header_tbl,
1072 l_related_mr_header_tbl,
1073 l_relationship_code_tbl LIMIT l_buffer_limit;
1074 --EXIT WHEN ahl_fmp_relationships_csr%NOTFOUND;
1075 EXIT WHEN (l_mr_header_tbl.count = 0);
1076
1077 FOR i IN l_mr_header_tbl.FIRST..l_mr_header_tbl.LAST LOOP
1078 l_mr_header_id := l_mr_header_tbl(i);
1079 l_related_mr_header_id := l_related_mr_header_tbl(i);
1080 l_relationship_code := l_relationship_code_tbl(i);
1081
1082 --For each edge of mr_relationships graph
1083 --Loop through all the mr + ii combinations
1084 OPEN ahl_appl_parent_mr_csr(l_mr_header_id);
1085 LOOP
1086 -- modified to fix perf bug# 13629335
1087 FETCH ahl_appl_parent_mr_csr INTO l_csi_ii_id, l_subj_child_exists;
1088 EXIT WHEN ahl_appl_parent_mr_csr%NOTFOUND;
1089
1090 -- added IF conditions to fix bug# 13629335
1091 IF (l_subj_child_exists IS NULL) THEN
1092 -- root object ID case
1093 OPEN ahl_appl_all_csr(l_related_mr_header_id, l_orig_ii_id);
1094 FETCH ahl_appl_all_csr BULK COLLECT INTO l_related_csi_ii_tbl;
1095 CLOSE ahl_appl_all_csr;
1096
1097 ELSIF (l_subj_child_exists = 'Y') THEN
1098 --For each mr+ii combination
1099 OPEN ahl_appl_child_mrs_csr(l_related_mr_header_id, l_csi_ii_id);
1100 FETCH ahl_appl_child_mrs_csr BULK COLLECT INTO l_related_csi_ii_tbl;
1101 CLOSE ahl_appl_child_mrs_csr;
1102 ELSE
1103 -- leaf node case (l_subj_child_exists = N)
1104 OPEN ahl_appl_ii_csr(l_related_mr_header_id, l_csi_ii_id);
1105 FETCH ahl_appl_ii_csr BULK COLLECT INTO l_related_csi_ii_tbl;
1106 CLOSE ahl_appl_ii_csr;
1107 END IF;
1108
1109 IF (l_related_csi_ii_tbl.count > 0) THEN
1110
1111 FORALL k IN l_related_csi_ii_tbl.FIRST..l_related_csi_ii_tbl.LAST
1112 INSERT INTO AHL_APPLICABLE_MR_RELNS (
1113 MR_HEADER_ID,
1114 CSI_ITEM_INSTANCE_ID,
1115 RELATED_MR_HEADER_ID,
1116 RELATED_CSI_ITEM_INSTANCE_ID,
1117 ORIG_MR_HEADER_ID,
1118 ORIG_CSI_ITEM_INSTANCE_ID,
1119 RELATIONSHIP_CODE
1120 ) values
1121 ( l_mr_header_id,
1122 l_csi_ii_id,
1123 l_related_mr_header_id,
1124 --l_related_csi_ii_id,
1125 l_related_csi_ii_tbl(k),
1126 l_orig_mr_id,
1127 l_orig_ii_id,
1128 l_relationship_code
1129 );
1130
1131 END IF;
1132
1133 l_related_csi_ii_tbl.delete;
1134
1135 END LOOP;
1136 Close ahl_appl_parent_mr_csr;
1137 END LOOP; -- l_mr_header_tbl.FIRST
1138
1139 l_mr_header_tbl.delete;
1140 l_related_mr_header_tbl.delete;
1141 l_relationship_code_tbl.delete;
1142 l_related_csi_ii_tbl.delete;
1143
1144 END LOOP;
1145 CLOSE ahl_fmp_relationships_csr;
1146
1147 --Done with creating all possible applicable edges.
1148 --Now fetch all relations reachable from the top node
1149 OPEN ahl_appl_mrs_csr(l_orig_mr_id, l_orig_ii_id);
1150 LOOP
1151 FETCH ahl_appl_mrs_csr INTO l_appl_mr_relns_rec;
1152 EXIT WHEN ahl_appl_mrs_csr%NOTFOUND;
1153
1154 OPEN ahl_get_depth_level_csr(l_appl_mr_relns_rec.mr_header_id,
1155 l_appl_mr_relns_rec.csi_item_instance_id,
1156 l_appl_mr_relns_rec.related_mr_header_id,
1157 l_appl_mr_relns_rec.related_csi_item_instance_id,
1158 l_orig_mr_id,
1159 l_orig_ii_id);
1160 FETCH ahl_get_depth_level_csr INTO l_depth_level;
1161
1162 IF (ahl_get_depth_level_csr%FOUND) THEN
1163 --If depth is greater in rec, update to new depth
1164 IF (l_depth_level < l_appl_mr_relns_rec.depth_level) THEN
1165 UPDATE ahl_applicable_mr_relns
1166 SET tree_depth_level = l_appl_mr_relns_rec.depth_level
1167 WHERE orig_mr_header_id = l_orig_mr_id
1168 AND orig_csi_item_instance_id = l_orig_ii_id
1169 AND mr_header_id = l_appl_mr_relns_rec.mr_header_id
1170 AND csi_item_instance_id = l_appl_mr_relns_rec.csi_item_instance_id
1171 AND related_mr_header_id = l_appl_mr_relns_rec.related_mr_header_id
1172 AND related_csi_item_instance_id = l_appl_mr_relns_rec.related_csi_item_instance_id;
1173 END IF;
1174 END IF; -- ahl_get_depth_level_csr%FOUND
1175 CLOSE ahl_get_depth_level_csr;
1176 END LOOP;
1177 CLOSE ahl_appl_mrs_csr;
1178
1179 --Now delete all rows with null depth (unreachable)
1180 DELETE FROM ahl_applicable_mr_relns
1181 WHERE tree_depth_level IS NULL
1182 AND orig_mr_header_id = l_orig_mr_id
1183 AND orig_csi_item_instance_id = l_orig_ii_id;
1184
1185 --Remove all duplicates and keep deepest paths
1186 OPEN ahl_duplicate_relns_csr(l_orig_mr_id, l_orig_ii_id);
1187 LOOP
1188 FETCH ahl_duplicate_relns_csr INTO l_related_mr_header_id, l_related_csi_ii_id;
1189 EXIT WHEN ahl_duplicate_relns_csr%NOTFOUND;
1190
1191 OPEN ahl_max_depth_reln_csr(l_related_mr_header_id,
1192 l_related_csi_ii_id,
1193 l_orig_mr_id,
1194 l_orig_ii_id);
1195 FETCH ahl_max_depth_reln_csr INTO l_mr_header_id, l_csi_ii_id;
1196 IF (ahl_max_depth_reln_csr%FOUND) THEN
1197 --Delete all rows != edge with maximum depth
1198 DELETE FROM ahl_applicable_mr_relns
1199 WHERE (mr_header_id <> l_mr_header_id
1200 OR csi_item_instance_id <> l_csi_ii_id)
1201 AND orig_mr_header_id = l_orig_mr_id
1202 AND orig_csi_item_instance_id = l_orig_ii_id
1203 AND related_mr_header_id = l_related_mr_header_id
1204 AND related_csi_item_instance_id = l_related_csi_ii_id;
1205 END IF; -- ahl_max_depth_reln_csr%FOUND
1206 CLOSE ahl_max_depth_reln_csr;
1207 END LOOP;
1208 CLOSE ahl_duplicate_relns_csr;
1209
1210 END Process_Group_MR_Instance;
1211
1212 -------------------------------------------------------------------------
1213 -- Procedure to get the first accomplishment of an MR for any given item
1214 -- instance. --
1215 -------------------------------------------------------------------------
1216 PROCEDURE get_first_accomplishment (p_csi_item_instance_id IN NUMBER,
1217 p_mr_header_id IN NUMBER,
1218 x_accomplishment_date OUT NOCOPY DATE,
1219 x_unit_effectivity_id OUT NOCOPY NUMBER,
1220 x_deferral_flag OUT NOCOPY BOOLEAN,
1221 x_status_code OUT NOCOPY VARCHAR2,
1222 x_return_val OUT NOCOPY BOOLEAN)
1223 IS
1224
1225 -- cursor to get mr title, version and copy accomplishment.
1226 CURSOR ahl_mr_headers_csr (p_mr_header_id IN NUMBER) IS
1227 SELECT title mr_title, version_number, copy_accomplishment_flag
1228 FROM ahl_mr_headers_b
1229 WHERE mr_header_id = p_mr_header_id;
1230
1231 -- cursor to get mr title, version and copy accomplishment.
1232 CURSOR ahl_mr_title_csr (p_mr_title IN VARCHAR2,
1233 p_version_number IN NUMBER) IS
1234 SELECT version_number, copy_accomplishment_flag, mr_header_id
1235 FROM ahl_mr_headers_b
1236 WHERE title = p_mr_title AND
1237 version_number = p_version_number;
1238
1239 -- cursor to get accomplishments for current version.
1240 CURSOR ahl_unit_effectivities_csr (p_csi_item_instance_id IN NUMBER,
1241 p_mr_header_id IN NUMBER) IS
1242 SELECT * FROM (
1243 SELECT ue.accomplished_date, ue.unit_effectivity_id, ue.status_code,
1244 affect_due_calc_flag, deferral_effective_on
1245 FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b def
1246 WHERE ue.defer_from_ue_id = def.unit_effectivity_id (+)
1247 AND ue.status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED')
1248 AND ue.csi_item_instance_id = p_csi_item_instance_id
1249 AND ue.mr_header_id = p_mr_header_id
1250 ORDER BY accomplished_date ASC
1251 )
1252 WHERE ROWNUM < 2;
1253
1254 l_accomplish_found BOOLEAN := FALSE;
1255 l_mr_header_id NUMBER := p_mr_header_id;
1256 l_unit_effectivity_id NUMBER;
1257 l_accomplishment_date DATE;
1258 l_copy_accomplishment_flag ahl_mr_headers_b.copy_accomplishment_flag%TYPE;
1259 l_mr_title ahl_mr_headers_b.title%TYPE;
1260 l_version_number NUMBER;
1261 l_status_code ahl_unit_effectivities_b.status_code%TYPE;
1262
1263 -- Added for deferral functionality.
1264 l_affect_due_calc_flag VARCHAr2(1);
1265 l_deferral_effective_on DATE;
1266
1267 BEGIN
1268
1269 -- Set return status.
1270 x_return_val := TRUE;
1271
1272 -- Set deferral flag.
1273 x_deferral_flag := FALSE;
1274
1275 l_accomplish_found := FALSE;
1276
1277 -- GET MR details.
1278 OPEN ahl_mr_headers_csr (p_mr_header_id);
1279 FETCH ahl_mr_headers_csr INTO l_mr_title,
1280 l_version_number,
1281 l_copy_accomplishment_flag;
1282 IF (ahl_mr_headers_csr%NOTFOUND) THEN
1283 --dbms_output.put_line ('mr_heeader_id not found');
1284 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_MR_NOTFOUND');
1285 FND_MESSAGE.Set_Token('MR_ID',p_mr_header_id);
1286 FND_MSG_PUB.ADD;
1287 x_return_val := FALSE;
1288 x_accomplishment_date := null;
1289 x_unit_effectivity_id := null;
1290 x_status_code := null;
1291 CLOSE ahl_mr_headers_csr;
1292 RETURN;
1293 END IF;
1294
1295 CLOSE ahl_mr_headers_csr;
1296
1297 -- default l_copy_accomplishment_flag to Y if NULL.
1298 IF (l_copy_accomplishment_flag IS NULL) THEN
1299 l_copy_accomplishment_flag := 'Y';
1300 END IF;
1301
1302 -- Get first accomplishment for current mr revsision.
1303 OPEN ahl_unit_effectivities_csr(p_csi_item_instance_id,
1304 l_mr_header_id);
1305 FETCH ahl_unit_effectivities_csr INTO l_accomplishment_date,
1306 l_unit_effectivity_id,
1307 l_status_code,
1308 l_affect_due_calc_flag,
1309 l_deferral_effective_on;
1310
1311 IF (ahl_unit_effectivities_csr%FOUND) THEN
1312 -- dbms_output.put_line ('ue id' || l_unit_effectivity_id);
1313 -- Added for deferral enhancements.
1314 -- Use deferral_effective_on date instead of accomplishment date.
1315 IF (l_affect_due_calc_flag = 'N') THEN
1316 x_accomplishment_date := l_deferral_effective_on;
1317 x_deferral_flag := TRUE;
1318 ELSE
1319 x_accomplishment_date := l_accomplishment_date;
1320 END IF;
1321 x_unit_effectivity_id := l_unit_effectivity_id;
1322 x_status_code := l_status_code;
1323 l_accomplish_found := TRUE;
1324 ELSE
1325 x_accomplishment_date := null;
1326 x_unit_effectivity_id := null;
1327 x_status_code := null;
1328 END IF; -- unit effectivities not found
1329 CLOSE ahl_unit_effectivities_csr;
1330
1331 WHILE ((l_copy_accomplishment_flag = 'Y') AND (l_version_number > 1)) LOOP
1332
1333 -- check if the earlier version exists.
1334 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
1335 CLOSE ahl_unit_effectivities_csr;
1336 END IF;
1337 --dbms_output.put_line ('next version');
1338
1339 OPEN ahl_mr_title_csr(l_mr_title,
1340 l_version_number-1);
1341 FETCH ahl_mr_title_csr INTO l_version_number,
1342 l_copy_accomplishment_flag,
1343 l_mr_header_id;
1344 IF (ahl_mr_title_csr%NOTFOUND) THEN
1345 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_TITLE_INVALID');
1346 FND_MESSAGE.Set_Token('TITLE',l_mr_title);
1347 FND_MESSAGE.Set_Token('VERSION',l_version_number);
1348 FND_MSG_PUB.ADD;
1349 x_return_val := FALSE;
1350 x_accomplishment_date := null;
1351 x_unit_effectivity_id := null;
1352 x_status_code := null;
1353 CLOSE ahl_mr_title_csr;
1354 RETURN;
1355 END IF;
1356 CLOSE ahl_mr_title_csr;
1357
1358 -- Get first accomplishment for mr version.
1359 OPEN ahl_unit_effectivities_csr(p_csi_item_instance_id,
1360 l_mr_header_id);
1361 FETCH ahl_unit_effectivities_csr INTO l_accomplishment_date,
1362 l_unit_effectivity_id,
1363 l_status_code,
1364 l_affect_due_calc_flag,
1365 l_deferral_effective_on;
1366
1367 IF (ahl_unit_effectivities_csr%FOUND) THEN
1368 -- dbms_output.put_line ('ue id' || l_unit_effectivity_id);
1369 -- Added for deferral enhancements.
1370 -- Use deferral_effective_on date instead of accomplishment date.
1371 IF (l_affect_due_calc_flag = 'N') THEN
1372 x_accomplishment_date := l_deferral_effective_on;
1373 x_deferral_flag := TRUE;
1374 ELSE
1375 x_deferral_flag := FALSE;
1376 x_accomplishment_date := l_accomplishment_date;
1377 END IF;
1378 x_unit_effectivity_id := l_unit_effectivity_id;
1379 x_status_code := l_status_code;
1380 l_accomplish_found := TRUE;
1381
1382 END IF; -- unit effectivities not found
1383 CLOSE ahl_unit_effectivities_csr;
1384
1385 -- dbms_output.put_line ('loop again');
1386
1387 END LOOP; /* while */
1388
1389 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
1390 CLOSE ahl_unit_effectivities_csr;
1391 END IF;
1392
1393 -- dbms_output.put_line ('x_ue_id:' || x_unit_effectivity_id);
1394 -- dbms_output.put_line ('x_acc_dt:' || x_accomplishment_date);
1395
1396 END get_first_accomplishment;
1397
1398 -------------------------------------------------------------------------
1399 PROCEDURE Populate_Config_Components (p_root_instance_id IN NUMBER) IS
1400
1401 -- get config elements.
1402 -- added query to get indicator child_exists to fix bug# 13629335
1403 CURSOR get_config_components(p_root_instance_id IN NUMBER) IS
1404 SELECT ii.subject_id, ii.object_id,
1405 (select 'Y' from csi_ii_relationships where object_id = ii.subject_id
1406 and relationship_type_code = 'COMPONENT-OF'
1407 and trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1408 and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1409 and rownum = 1) child_exists
1410 FROM csi_ii_relationships ii
1411 START WITH ii.object_id = p_root_instance_id
1412 AND ii.relationship_type_code = 'COMPONENT-OF'
1413 AND trunc(nvl(ii.active_start_date,sysdate)) <= trunc(sysdate)
1414 AND trunc(sysdate) < trunc(nvl(ii.active_end_date, sysdate+1))
1415 CONNECT BY PRIOR ii.subject_id = ii.object_id
1416 AND ii.relationship_type_code = 'COMPONENT-OF'
1417 AND trunc(nvl(ii.active_start_date,sysdate)) <= trunc(sysdate)
1418 AND trunc(sysdate) < trunc(nvl(ii.active_end_date, sysdate+1));
1419
1420 l_buffer_limit NUMBER := 1000;
1421
1422 l_subj_id_tbl nbr_tbl_type;
1423 l_obj_id_tbl nbr_tbl_type;
1424 -- added for perf fix bug# 13629335
1425 l_child_exists_tbl vchar_tbl_type;
1426
1427 BEGIN
1428 DELETE FROM AHL_CONFIG_COMPONENTS;
1429
1430 OPEN get_config_components(p_root_instance_id);
1431 LOOP
1432 FETCH get_config_components BULK COLLECT INTO l_subj_id_tbl, l_obj_id_tbl, l_child_exists_tbl LIMIT l_buffer_limit;
1433 EXIT WHEN (l_subj_id_tbl.count = 0);
1434
1435 -- insert into temp table.
1436 FORALL i IN 1..l_obj_id_tbl.count
1437 INSERT INTO AHL_CONFIG_COMPONENTS (subject_id, object_id, root_object_id, subj_child_exists)
1438 values (l_subj_id_tbl(i), l_obj_id_tbl(i), p_root_instance_id, nvl(l_child_exists_tbl(i), 'N'));
1439
1440 l_subj_id_tbl.delete;
1441 l_obj_id_tbl.delete;
1442 l_child_exists_tbl.delete;
1443
1444 END LOOP;
1445 CLOSE get_config_components;
1446
1447 END Populate_Config_Components;
1448 -------------------------------------------------------------------------
1449
1450 ------------------------------------------------------------------------
1451 -- Function to return last accomplishment of an MR for any given item
1452 -- instance. --
1453 -------------------------------------------------------------------------
1454 FUNCTION Get_Last_Accomplishment (p_csi_item_instance_id IN NUMBER,
1455 p_mr_header_id IN NUMBER )
1456 RETURN DATE IS
1457
1458 -- cursor to get mr title, version and copy accomplishment.
1459 CURSOR ahl_mr_headers_csr (p_mr_header_id IN NUMBER) IS
1460 SELECT title mr_title, version_number, copy_accomplishment_flag
1461 FROM ahl_mr_headers_b
1462 WHERE mr_header_id = p_mr_header_id;
1463
1464 -- cursor to get mr title, version and copy accomplishment.
1465 CURSOR ahl_mr_title_csr (p_mr_title IN VARCHAR2,
1466 p_version_number IN NUMBER) IS
1467 SELECT version_number, copy_accomplishment_flag, mr_header_id
1468 FROM ahl_mr_headers_b
1469 WHERE title = p_mr_title AND
1470 version_number = p_version_number;
1471
1472 -- cursor to get accomplishments for current version.
1473 CURSOR ahl_unit_effectivities_csr (p_csi_item_instance_id IN NUMBER,
1474 p_mr_header_id IN NUMBER) IS
1475 SELECT * FROM (
1476 SELECT ue.accomplished_date, ue.unit_effectivity_id, ue.status_code,
1477 decode(ue.status_code, 'TERMINATED', ter.affect_due_calc_flag, def.affect_due_calc_flag),
1478 decode(ue.status_code, 'TERMINATED', ter.deferral_effective_on, def.deferral_effective_on)
1479 FROM ahl_unit_effectivities_b ue, ahl_unit_deferrals_b def, ahl_unit_deferrals_b ter
1480 WHERE ue.defer_from_ue_id = def.unit_effectivity_id (+)
1481 AND ue.unit_effectivity_id = ter.unit_effectivity_id(+)
1482 AND ue.status_code IN ('ACCOMPLISHED','INIT-ACCOMPLISHED','TERMINATED')
1483 AND def.unit_deferral_type(+) = 'DEFERRAL'
1484 AND ter.unit_deferral_type(+) = 'DEFERRAL'
1485 AND ue.csi_item_instance_id = p_csi_item_instance_id
1486 AND ue.mr_header_id = p_mr_header_id
1487 --ORDER BY accomplished_date DESC;
1488 ORDER BY decode (ue.status_code, 'TERMINATED', ter.deferral_effective_on, ue.accomplished_date) DESC
1489 )
1490 WHERE ROWNUM < 2;
1491
1492 l_accomplish_found BOOLEAN := FALSE;
1493 l_mr_header_id NUMBER := p_mr_header_id;
1494 l_unit_effectivity_id NUMBER;
1495 l_accomplishment_date DATE;
1496 l_copy_accomplishment_flag ahl_mr_headers_b.copy_accomplishment_flag%TYPE;
1497 l_mr_title ahl_mr_headers_b.title%TYPE;
1498 l_version_number NUMBER;
1499 l_status_code ahl_unit_effectivities_b.status_code%TYPE;
1500
1501 -- Added for deferral functionality.
1502 l_affect_due_calc_flag VARCHAr2(1);
1503 l_deferral_effective_on DATE;
1504
1505 BEGIN
1506
1507 -- GET MR details.
1508 OPEN ahl_mr_headers_csr (p_mr_header_id);
1509 FETCH ahl_mr_headers_csr INTO l_mr_title,
1510 l_version_number,
1511 l_copy_accomplishment_flag;
1512 IF (ahl_mr_headers_csr%NOTFOUND) THEN
1513 CLOSE ahl_mr_headers_csr;
1514 RETURN NULL;
1515 END IF;
1516 CLOSE ahl_mr_headers_csr;
1517
1518 -- pick the most recent accomplishment from previous version.
1519 l_accomplish_found := FALSE;
1520 WHILE NOT(l_accomplish_found) LOOP
1521 -- Get last accomplishment.
1522 OPEN ahl_unit_effectivities_csr(p_csi_item_instance_id,
1523 l_mr_header_id);
1524 FETCH ahl_unit_effectivities_csr INTO l_accomplishment_date,
1525 l_unit_effectivity_id,
1526 l_status_code,
1527 l_affect_due_calc_flag,
1528 l_deferral_effective_on;
1529
1530 IF (ahl_unit_effectivities_csr%FOUND) THEN
1531 --dbms_output.put_line ('ue id' || l_unit_effectivity_id);
1532 -- Added for deferral enhancements.
1533 -- Use deferral_effective_on date instead of accomplishment date.
1534 IF (l_affect_due_calc_flag = 'N') THEN
1535 l_accomplishment_date := l_deferral_effective_on;
1536 END IF;
1537 l_accomplish_found := TRUE;
1538 CLOSE ahl_unit_effectivities_csr;
1539 ELSE
1540 -- find accomplishments from last mr revision based on copy accomplishment flag.
1541 IF (l_copy_accomplishment_flag = 'N') THEN
1542 --dbms_output.put_line ('copy_accomplishment_flag' || l_copy_accomplishment_flag );
1543 l_accomplishment_date := NULL;
1544 l_accomplish_found := TRUE;
1545 ELSE
1546 -- check if any more versions available.
1547 IF (l_version_number = 1) THEN
1548 --dbms_output.put_line ('version_number = 1' );
1549 l_accomplishment_date := NULL;
1550 l_accomplish_found := TRUE;
1551 ELSE
1552 -- check if the earlier version exists.
1553 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
1554 CLOSE ahl_unit_effectivities_csr;
1555 END IF;
1556 --dbms_output.put_line ('next version');
1557 OPEN ahl_mr_title_csr(l_mr_title,
1558 l_version_number-1);
1559 FETCH ahl_mr_title_csr INTO l_version_number,
1560 l_copy_accomplishment_flag,
1561 l_mr_header_id;
1562 IF (ahl_mr_title_csr%NOTFOUND) THEN
1563 CLOSE ahl_mr_title_csr;
1564 RETURN NULL;
1565 END IF;
1566 CLOSE ahl_mr_title_csr;
1567 END IF; /* version number */
1568 END IF; /* l_copy accomplishment flag */
1569 END IF; /* unit effectivities not found */
1570 --dbms_output.put_line ('loop again');
1571 END LOOP; /* while */
1572
1573 IF (ahl_unit_effectivities_csr%ISOPEN) THEN
1574 CLOSE ahl_unit_effectivities_csr;
1575 END IF;
1576 RETURN l_accomplishment_date;
1577
1578 End Get_Last_Accomplishment ;
1579
1580 -- BACHANDR - NR project start
1581 ------------------------------------------------------------------------
1582 -- Function to return fleet id to which the input unit is associated at
1583 -- the input date in the context of input simulation plan.
1584 -- Added as part of NR Analysis and Forecasting project.
1585 -------------------------------------------------------------------------
1586 FUNCTION Get_Associated_Fleet(p_uc_header_id IN NUMBER,
1587 p_assoc_date IN DATE,
1588 p_plan_id IN NUMBER)
1589 RETURN NUMBER
1590 IS
1591 -- get fleet for the unit on the date given.
1592 CURSOR c_get_assoc_fleet(c_unit_config_header_id NUMBER,
1593 c_due_date DATE,
1594 c_plan_id NUMBER)
1595 IS
1596 SELECT FUA.fleet_header_id
1597 FROM ahl_fleet_unit_assocs FUA, ahl_fleet_headers_b FLT
1598 WHERE unit_config_header_id = c_unit_config_header_id
1599 AND c_due_date between ASSOCIATION_START and nvl(ASSOCIATION_END,c_due_date)
1600 AND FLT.fleet_header_id = FUA.fleet_header_id
1601 AND FLT.status_code = 'COMPLETE'
1602 AND FUA.simulation_plan_id = c_plan_id;
1603
1604
1605 CURSOR get_primary_plan IS
1606 SELECT simulation_plan_id
1607 FROM AHL_SIMULATION_PLANS_B
1608 WHERE primary_plan_flag = 'Y'
1609 AND status_code = 'ACTIVE'
1610 AND nvl(simulation_type,'UMP') = 'UMP';
1611
1612 l_fleet_header_id NUMBER;
1613 l_plan_id NUMBER;
1614 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||'AHL_UMP_UTIL_PKG'||'.'||'Get_Associated_Fleet';
1615
1616 BEGIN
1617
1618 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1619 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1620 'API Input Dump');
1621 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1622 '****************');
1623 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1624 'p_uc_header_id -> '||p_uc_header_id);
1625 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1626 'p_assoc_date -> '||p_assoc_date);
1627 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1628 'p_plan_id -> '||p_plan_id);
1629 END IF;
1630
1631 IF p_uc_header_id IS NULL OR p_assoc_date IS NULL
1632 THEN
1633
1634 RETURN l_fleet_header_id;
1635
1636 END IF;
1637
1638 l_plan_id := p_plan_id;
1639
1640 IF p_plan_id IS NULL
1641 THEN
1642
1643 OPEN get_primary_plan;
1644 FETCH get_primary_plan INTO l_plan_id;
1645 CLOSE get_primary_plan;
1646
1647 END IF;
1648
1649 OPEN c_get_assoc_fleet(p_uc_header_id,p_assoc_date,l_plan_id);
1650 FETCH c_get_assoc_fleet INTO l_fleet_header_id;
1651 CLOSE c_get_assoc_fleet;
1652
1653 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1654 FND_LOG.string(FND_LOG.level_statement,l_full_name,
1655 'l_fleet_header_id -> '||l_fleet_header_id);
1656 END IF;
1657
1658 RETURN l_fleet_header_id;
1659
1660 END Get_Associated_Fleet;
1661 -- BACHANDR - NR project end
1662
1663 END AHL_UMP_UTIL_PKG;
[Home] [Help]