[Home] [Help]
PACKAGE BODY: APPS.AHL_AMP_WORKBENCH_PVT
Source
1 PACKAGE BODY AHL_AMP_WORKBENCH_PVT AS
2 /* $Header: AHLVAMPB.pls 120.0.12020000.2 2012/12/10 16:59:08 prakkum noship $ */
3
4 -----------------------------------------------------------------
5 -- Define Global CONSTANTS --
6 -----------------------------------------------------------------
7 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_AMP_WORKBENCH_PVT';
9 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
10 ------------------------------------
11 -- Common constants and variables --
12 ------------------------------------
13 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
14 l_log_statement NUMBER := fnd_log.level_statement;
15 l_log_procedure NUMBER := fnd_log.level_procedure;
16 l_log_error NUMBER := fnd_log.level_error;
17 l_log_unexpected NUMBER := fnd_log.level_unexpected;
18
19 -- constants for WHO Columns
20 -- Added by Prithwi as a part of Public API cleanup
21
22 G_LAST_UPDATE_DATE DATE := SYSDATE;
23 G_LAST_UPDATED_BY NUMBER(15) := FND_GLOBAL.user_id;
24 G_LAST_UPDATE_LOGIN NUMBER(15) := FND_GLOBAL.login_id;
25 G_CREATION_DATE DATE := SYSDATE;
26 G_CREATED_BY NUMBER(15) := FND_GLOBAL.user_id;
27
28 G_SPACE_TYPE VARCHAR2(5) := 'SPACE';
29 G_DEPT_TYPE VARCHAR2(4) := 'DEPT';
30 G_FILTER_ALL CONSTANT VARCHAR2(3) := 'ALL';
31
32
33 PROCEDURE GET_GRAPH_REC_FOR_DATE(
34 p_graph_rec_date IN DATE,
35 p_graph_rec_num IN NUMBER,
36 p_visit_count IN NUMBER,
37 p_not_for_sch IN VARCHAR2 := Fnd_Api.g_false,
38 p_filter_criteria IN VARCHAR2 := 'ALL',
39 p_rec_type IN VARCHAR2 := G_SPACE_TYPE,
40 p_sch_visits_tbl IN SCH_VISITS_TBL,
41 p_x_sch_graph_rec IN OUT NOCOPY SCH_GRAPH_RESULTS_REC
42 );
43
44 /*------------- STHILAK ER # 13799535 STARTS----------------------- */
45
46 -- Function name : GET_NUMBER_OF_WORKING_DAYS
47 -- TO calculate the number of working days in given interval
48 -- Sthilak bug #13958744 department_id is added in claculating working days
49 FUNCTION GET_NUMBER_OF_WORKING_DAYS(p_org_id NUMBER ,p_dept_id NUMBER,p_start_dt DATE , p_end_dt DATE)
50 RETURN NUMBER
51 IS
52 l_number_of_days NUMBER;
53 -- cursor to get the working days in the interval
54 CURSOR c_number_of_working_days (c_org_id NUMBER,c_dept_id NUMBER,c_start_date DATE , c_end_date DATE)
55 IS
56 -- STHILAK CHANGES THE CURSOR DEFNITION
57 select count(distinct drc.AVAILABLE_DATE) no_of_working_date from AHL_DEPT_RESOURCE_CAPACITY drc where drc.available_date between TRUNC(c_start_date) and TRUNC(c_end_date) AND drc.organization_id = c_org_id AND drc.department_id = c_dept_id AND
58 drc.ASCP_PLAN_DATE = (SELECT MAX(ASCP_PLAN_DATE) FROM AHL_DEPT_RESOURCE_CAPACITY ) order by drc.available_date;
59 BEGIN
60
61 OPEN c_number_of_working_days(p_org_id,p_dept_id,p_start_dt,p_end_dt);
62
63 FETCH c_number_of_working_days into l_number_of_days;
64 IF c_number_of_working_days%NOTFOUND THEN
65 l_number_of_days :=0;
66 END IF;
67
68 CLOSE c_number_of_working_days;
69
70 RETURN l_number_of_days;
71 END ;
72 /*---------------------------------------------------------*/
73
74 -- NOT USED Function name : GET_NEXT_WORKING_DATE
75 -- TO calculate the nth working day from the given date
76 -- Sthilak bug #13958744 department_id is added in claculating working days
77 FUNCTION GET_NEXT_WORKING_DATE(p_org_id NUMBER ,p_dept_id NUMBER,p_start_dt DATE , p_no_of_days NUMBER)
78 RETURN DATE
79 IS
80 l_date DATE;
81 l_index NUMBER;
82
83 CURSOR c_next_working_day(c_org_id NUMBER,c_start_date DATE )
84 IS
85 SELECT DISTINCT cal.calendar_date working_date
86 FROM mtl_parameters param,
87 bom_calendar_dates cal,
88 bom_shift_times shifts
89 WHERE param.organization_id = c_org_id
90 AND TRUNC(cal.calendar_date) >= TRUNC(c_start_date)
91 AND cal.calendar_code = param.calendar_code
92 AND cal.exception_set_id = param.calendar_exception_set_id
93 AND param.calendar_code = shifts.calendar_code
94 --AND TRUNC(cal.calendar_date) >= TRUNC(sysdate)
95 AND ((cal.seq_num IS NOT NULL
96 AND NOT EXISTS
97 (SELECT 1
98 FROM CRP_CAL_SHIFT_DELTA delta1
99 WHERE delta1.calendar_code = shifts.calendar_code
100 AND delta1.exception_set_id = param.calendar_exception_set_id
101 AND delta1.delta_code = 1
102 AND delta1.calendar_date = cal.calendar_date
103 AND delta1.shift_num = shifts.shift_num
104 ))
105 OR (cal.seq_num IS NULL
106 AND EXISTS
107 (SELECT 1
108 FROM CRP_CAL_SHIFT_DELTA delta1
109 WHERE delta1.calendar_code = shifts.calendar_code
110 AND delta1.exception_set_id = param.calendar_exception_set_id
111 AND delta1.delta_code = 2
112 AND delta1.calendar_date = cal.calendar_date
113 AND delta1.shift_num = shifts.shift_num
114 ))) order by cal.calendar_date;
115 BEGIN
116
117 IF p_no_of_days < 0 THEN
118 RETURN p_start_dt;
119 END IF;
120
121 l_index := p_no_of_days;
122
123 FOR loop_date IN c_next_working_day(p_org_id,p_start_dt)
124 LOOP
125 l_date := loop_date.working_date;
126 l_index := l_index -1;
127
128 EXIT WHEN l_index = 0;
129 END LOOP;
130
131 IF l_index > 0 THEN /* exit by finishing the loop bu without finding the needed date */
132 l_date := NULL;
133 END IF;
134
135 RETURN l_date;
136 END;
137 /*---------------------------------------------------------*/
138
139 -- NOT USED Function name : GET_NEW_REQ_END_DATE
140 -- TO calculate the new requirement end date based on params
141 -- p_org_id NUMBER ,
142 -- p_start_dt DATE,
143 -- p_end_date DATE,
144 -- p_usage_units NUMBER,
145 -- p_applied_units NUMBER
146 -- Sthilak bug #13958744 department_id is added in claculating working days
147 FUNCTION GET_NEW_REQ_END_DATE(p_org_id NUMBER ,p_dept_id NUMBER,p_start_dt DATE,p_end_date DATE,p_usage_units NUMBER,p_applied_units NUMBER)
148 RETURN DATE
149 IS
150 l_remaining_units NUMBER ;
151 l_per_day_average NUMBER;
152 l_more_days_needed NUMBER;
153 l_left_over_units NUMBER;
154 l_number_of_days NUMBER;
155 l_new_req_end_date DATE;
156
157 BEGIN
158 l_remaining_units := GREATEST(p_usage_units,p_applied_units) - p_applied_units;
159
160 l_number_of_days := AHL_AMP_WORKBENCH_PVT.GET_NUMBER_OF_WORKING_DAYS(p_org_id,p_dept_id,p_start_dt,p_end_date);
161
162 IF l_number_of_days =0 THEN
163 RETURN NULL;
164 END IF;
165
166 l_per_day_average := ROUND(p_usage_units /l_number_of_days,1);
167
168 IF l_per_day_average = 0 THEN
169 l_more_days_needed :=0;
170 l_left_over_units :=0;
171 ELSE
172 l_more_days_needed := TRUNC( l_remaining_units / l_per_day_average);
173
174 l_left_over_units := MOD(l_remaining_units,l_per_day_average);
175 END IF;
176
177 IF (l_left_over_units > 0) THEN
178 l_more_days_needed := l_more_days_needed +1;
179 END IF;
180
181 IF p_start_dt <= sysdate THEN
182 l_new_req_end_date := AHL_AMP_WORKBENCH_PVT.GET_NEXT_WORKING_DATE(p_org_id,p_dept_id,sysdate,l_more_days_needed);
183 ELSE
184 l_new_req_end_date := AHL_AMP_WORKBENCH_PVT.GET_NEXT_WORKING_DATE(p_org_id,p_dept_id,p_start_dt,l_more_days_needed);
185 END IF;
186
187 RETURN l_new_req_end_date;
188 END;
189
190 /*---------------------------------------------------------*/
191
192
193 -- Function name : GET_DAY_REQUIREMENT
194 -- TO calculate the requirement units on a date based on params
195 -- p_usage_units NUMBER,
196 -- p_applied_units NUMBER,
197 -- p_org_id NUMBER,
198 -- p_start_date DATE ,
199 -- p_end_date DATE,
200 -- p_cal_date DATE
201
202 -- STHILAK UPDATED THE FUNCTION
203 -- Sthilak bug #13958744 department_id is added in claculating working days
204 FUNCTION GET_DAY_REQUIREMENT(p_usage_units NUMBER,p_applied_units NUMBER,p_org_id NUMBER,p_dept_id NUMBER,p_start_date DATE , p_end_date DATE,p_cal_date DATE)
205 RETURN NUMBER
206 IS
207 l_req_on_cal_date NUMBER;
208 l_rem_number_of_days NUMBER;
209 l_per_day_req NUMBER;
210 l_remaining_units NUMBER;
211 l_left_over_units NUMBER;
212 l_consumed_units NUMBER;
213 l_start_dt DATE;
214
215 BEGIN
216 -- calcualte the remaining units
217 l_remaining_units := GREATEST(p_usage_units,p_applied_units) - p_applied_units;
218
219 -- calcualte the remainig working days
220 IF p_start_date <= sysdate THEN
221 l_start_dt := sysdate;
222 ELSE
223 l_start_dt := p_start_date;
224 END IF ;
225 l_rem_number_of_days := AHL_AMP_WORKBENCH_PVT.GET_NUMBER_OF_WORKING_DAYS(p_org_id,p_dept_id,l_start_dt,p_end_date);
226
227 -- Calcualte he capacity
228 IF l_rem_number_of_days = 0 THEN /* if no working day found i.e during holiday then the resource capacity is 0*/
229 l_req_on_cal_date:= 0;
230 ELSE
231 l_req_on_cal_date := ROUND((l_remaining_units/ l_rem_number_of_days),2);
232 END IF;
233
234 RETURN l_req_on_cal_date;
235 END;
236
237 /*---------------------------------------------------------*/
238 -- Procedure name : GET_MC_GRAPH_DATA
239 -- Type : Public
240 -- Pre-reqs :
241 -- Function :
242 -- Parameters :
243 --
244 -- Standard IN Parameters :
245 -- p_api_version NUMBER Required
246 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
247 -- p_commit VARCHAR2 Default FND_API.G_FALSE
248 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
249 -- p_default VARCHAR2 Default FND_API.G_TRUE
250 -- p_module_type VARCHAR2 Default NULL
251 --
252 -- Standard OUT Parameters :
253 -- x_return_status VARCHAR2 Required
254 -- x_msg_count NUMBER Required
255 -- x_msg_data VARCHAR2 Required
256
257 -- GET_MC_GRAPH_DATA params
258 -- p_organization_id - organization id
259 -- p_department_id - department id
260 -- p_start_date - start date from which the data for graph has to be calculated
261 -- p_no_of_days - for how many number of days - the data has to be calculated
262 -- p_x_resource_input - list of resource ids for which - the data has to be calculated
263 -- p_x_resource_output - calculated data for drawing graph
264 -- x_plan_date - the date when ASCP program ran last
265
266 -- Sthilak CHANGED THE PROCEDURE LOGIC ER1#3799535
267 -- Sthilak bug #13958744 department_id is added in claculating working days
268 procedure GET_MC_GRAPH_DATA(
269 p_api_version IN NUMBER := 1.0,
270 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
271 p_commit IN VARCHAR2 := FND_API.G_FALSE,
272 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
273 p_default IN VARCHAR2 := FND_API.G_FALSE,
274 p_module_type IN VARCHAR2 := NULL,
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_msg_count OUT NOCOPY NUMBER,
277 x_msg_data OUT NOCOPY VARCHAR2,
278 p_organization_id IN NUMBER,
279 p_department_id IN NUMBER,
280 p_start_date IN DATE ,
281 p_no_of_days IN NUMBER,
282 p_max_range IN NUMBER, --Sthilak added to make the max range as parameterized
283 p_x_resource_input IN OUT NOCOPY resource_input_tbl_type,
284 p_x_resource_output IN OUT NOCOPY resource_output_tbl_type,
285 x_plan_date OUT NOCOPY DATE)
286
287 IS
288
289 -- local variables
290 l_output_record_counter NUMBER;
291 l_requirement NUMBER;
292 l_transacted NUMBER;
293 l_capacity NUMBER;
294 l_availability NUMBER;
295 l_req_uom VARCHAR2(3);
296 l_avail_uom VARCHAR2(3);
297 l_curr_date DATE;
298 l_start_date Date;
299
300 -- cursor to get the working days in the interval
301 CURSOR c_working_dates(c_org_id NUMBER,c_dept_id NUMBER,c_start_date DATE , c_no_of_days NUMBER)
302 IS
303 select cal_date from
304 (select distinct drc.AVAILABLE_DATE cal_date from AHL_DEPT_RESOURCE_CAPACITY drc where drc.available_date >=TRUNC(c_start_date)
305 AND drc.organization_id = c_org_id AND drc.department_id = c_dept_id AND drc.ASCP_PLAN_DATE = (SELECT MAX(ASCP_PLAN_DATE) FROM AHL_DEPT_RESOURCE_CAPACITY ) order by drc.available_date)
306 where ROWNUM < (c_no_of_days+1) ; /* Rule is :Rownum is executed first then follwed by order by , thats why made a sub query and put row num on top of that*/
307
308
309
310 --cursor 1 Resource_requirement
311 CURSOR c_resource_req(c_oranization_id NUMBER,c_department_id NUMBER, c_on_date DATE, c_resource_id NUMBER)
312 IS
313 SELECT SUM (AHL_AMP_WORKBENCH_PVT.GET_DAY_REQUIREMENT(NVL(wor.usage_rate_or_amount,0),
314 NVL(wor.APPLIED_RESOURCE_UNITS,0),wor.organization_id,c_department_id,wor.start_date,wor.completion_date,c_on_date)) req_amt, wor.uom_code
315 FROM wip_operation_resources wor,
316 wip_discrete_jobs wdj,
317 wip_entities wip
318 WHERE wor.organization_id = c_oranization_id
319 AND wor.department_id = c_department_id
320 AND wor.resource_id = c_resource_id
321 AND c_on_date BETWEEN TRUNC(wor.start_date) AND TRUNC(wor.completion_date)
322 AND wor.wip_entity_id = wdj.wip_entity_id
323 AND wdj.status_type IN (17,3,1,6) /* included are Draft(17), Released (3), Unreleased(1) and On Hold (6).*/
324 AND wor.wip_entity_id =wip.wip_entity_id
325 AND wip.entity_type IN (1,2,4,5,6)
326 GROUP BY wor.uom_code;
327
328 -- cursor 2 resource availability - in hours
329 CURSOR c_resource_avail(c_oranization_id NUMBER,c_department_id NUMBER,c_resource_id NUMBER,c_avail_dt DATE)
330 IS
331 SELECT DECODE(UOM_CODE,'DAY',CAPACITY_UNITS * 24,CAPACITY_UNITS) CAPACITY_UNITS ,
332 UOM_CODE
333 FROM AHL_DEPT_RESOURCE_CAPACITY drc
334 WHERE drc.ORGANIZATION_ID = c_oranization_id
335 AND drc.DEPARTMENT_ID =c_department_id
336 AND drc.RESOURCE_ID =c_resource_id
337 AND drc.AVAILABLE_DATE =TRUNC(c_avail_dt)
338 AND drc.ASCP_PLAN_DATE =
339 (SELECT MAX(ASCP_PLAN_DATE) FROM AHL_DEPT_RESOURCE_CAPACITY
340 );
341
342 BEGIN
343 -- Enable Debug (optional)
344 IF ( G_DEBUG = 'Y' ) THEN
345 AHL_DEBUG_PUB.enable_debug;
346 END IF;
347
348 -- ASCP last ran date
349 -- STHILAK ER #13799535 x_plan_date := TRUNC(sysdate);
350 SELECT MAX(ASCP_PLAN_DATE)
351 INTO x_plan_date
352 FROM AHL_DEPT_RESOURCE_CAPACITY;
353 IF G_DEBUG = 'Y' THEN
354 AHL_DEBUG_PUB.debug( 'AHL_AMP_WORKBENCH_PVT' || '.' || 'GET_MC_GRAPH_DATA' || ' : ASCP plan date = '|| x_plan_date );
355 END IF;
356
357 -- set the start date
358 l_start_date := TRUNC(p_start_date);
359 IF (l_start_date IS NULL OR l_start_date < sysdate) THEN
360 l_start_date := sysdate;
361 END IF;
362
363 l_output_record_counter :=-1;
364 FOR cur_date IN c_working_dates(p_organization_id,p_department_id,l_start_date,p_no_of_days)
365 LOOP
366 IF l_output_record_counter >= ( p_no_of_days -1 ) THEN
367 IF G_DEBUG = 'Y' THEN
368 AHL_DEBUG_PUB.debug( 'AHL_AMP_WORKBENCH_PVT' || '.' || 'GET_MC_GRAPH_DATA' || ' : EXITING after reached NO. of records = '|| l_output_record_counter );
369 END IF;
370 EXIT; -- number fo records reached . SO exit from main loop. actually this condition is not needed. but added to handle cornercases
371 END IF;
372
373 l_output_record_counter := l_output_record_counter+1;
374 l_curr_date := cur_date.cal_date;
375 IF G_DEBUG = 'Y' THEN
376 AHL_DEBUG_PUB.debug( 'AHL_AMP_WORKBENCH_PVT' || '.' || 'GET_MC_GRAPH_DATA' || ' : reached NO. of records = '|| l_output_record_counter );
377 AHL_DEBUG_PUB.debug( 'AHL_AMP_WORKBENCH_PVT' || '.' || 'GET_MC_GRAPH_DATA' || ' : DATE = '|| l_curr_date );
378 END IF;
379
380 -- set the date
381 p_x_resource_output(l_output_record_counter).on_date := l_curr_date;
382 -- set the 100 % bar line capacity
383 p_x_resource_output(l_output_record_counter).cent_percent_capacity := 100;
384
385 FOR l_index IN p_x_resource_input.FIRST .. p_x_resource_input.LAST
386 LOOP
387
388
389 -- get the resource requirement
390 OPEN c_resource_req(p_organization_id,p_department_id,l_curr_date,p_x_resource_input(l_index).resource_id);
391 FETCH c_resource_req INTO l_requirement,l_req_uom;
392 IF c_resource_req%NOTFOUND THEN
393 l_requirement:=0;
394 END IF;
395
396 IF G_DEBUG = 'Y' THEN
397 AHL_DEBUG_PUB.debug( 'AHL_AMP_WORKBENCH_PVT' || '.' || 'GET_MC_GRAPH_DATA' || ' : REq = '|| ' res id = ' || p_x_resource_input(l_index).resource_id || ' Date= ' || l_curr_date || ' req units = ' ||l_requirement);
398 END IF;
399
400 -- get the resource avail
401 OPEN c_resource_avail(p_organization_id,p_department_id,p_x_resource_input(l_index).resource_id,l_curr_date);
402 FETCH c_resource_avail INTO l_availability,l_avail_uom;
403 IF c_resource_avail%NOTFOUND THEN
404 l_availability :=0;
405 END IF;
406
407 IF G_DEBUG = 'Y' THEN
408 AHL_DEBUG_PUB.debug( 'AHL_AMP_WORKBENCH_PVT' || '.' || 'GET_MC_GRAPH_DATA' || ' : AVAIL= '|| ' res id = ' || p_x_resource_input(l_index).resource_id || ' Date= ' || l_curr_date || ' avail units = ' ||l_availability);
409 END IF;
410
411 -- clacualte capacity
412 IF l_availability = 0 AND l_requirement = 0 THEN
413 l_capacity := 0;
414 ELSIF l_availability = 0 AND l_requirement <> 0 THEN
415 l_capacity := p_max_range;
416 ELSE
417 l_capacity := ROUND((l_requirement/l_availability) * 100,2); /* l_requirement should not come as zero */
418 END IF;
419
420 -- Restricting to MAX LIMIT
421 IF l_capacity >p_max_range THEN
422 l_capacity:= p_max_range;
423 END IF;
424
425 IF G_DEBUG = 'Y' THEN
426 AHL_DEBUG_PUB.debug( 'AHL_AMP_WORKBENCH_PVT' || '.' || 'GET_MC_GRAPH_DATA' || ' : BEfore case index = '|| l_index );
427 AHL_DEBUG_PUB.debug( 'AHL_AMP_WORKBENCH_PVT' || '.' || 'GET_MC_GRAPH_DATA' || ' : BEfore case resource Id = '|| p_x_resource_input(l_index).resource_id );
428 END IF;
429 CASE (l_index )
430 WHEN 1 THEN
431 p_x_resource_output(l_output_record_counter).r1_capacity := l_capacity;
432 WHEN 2 THEN
433 p_x_resource_output(l_output_record_counter).r2_capacity := l_capacity;
434 WHEN 3 THEN
435 p_x_resource_output(l_output_record_counter).r3_capacity := l_capacity;
436 WHEN 4 THEN
437 p_x_resource_output(l_output_record_counter).r4_capacity := l_capacity;
438 WHEN 5 THEN
439 p_x_resource_output(l_output_record_counter).r5_capacity := l_capacity;
440 END CASE;
441 CLOSE c_resource_req;
442 CLOSE c_resource_avail;
443 END LOOP;
444 END LOOP;
445
446 IF (l_output_record_counter = -1 ) THEN -- STHILAK bug #13889247 handled NO GRPAH DATA i.e NO WORKING DAYS FOUND
447 x_plan_date := null;
448 END IF;
449 END GET_MC_GRAPH_DATA;
450
451
452
453
454 -- Procedure name : GET_ORG_SCH_GRAPH
455 -- Type : Public
456 -- Parameters :
457 --
458 -- Standard IN Parameters :
459 -- p_api_version NUMBER Required
460 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
461 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
462 --
463 -- Standard OUT Parameters :
464 -- x_return_status VARCHAR2 Required
465 -- x_msg_count NUMBER Required
466 -- x_msg_data VARCHAR2 Required
467 -- GET_ORG_SCH_GRAPH params
468 -- p_org_sch_search_rec ORG_SCH_SEARCH_REC Required
469 -- x_sch_graph_results_tbl SCH_GRAPH_RESULTS_TBL
470
471 PROCEDURE GET_ORG_SCH_GRAPH(
472 p_api_version IN NUMBER := 1.0,
473 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
474 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
475 x_return_status OUT NOCOPY VARCHAR2,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2,
478 p_org_sch_search_rec IN org_sch_search_rec,
479 x_sch_graph_results_tbl OUT NOCOPY sch_graph_results_tbl
480 ) IS
481
482 -- LOCAL VARIABLE
483 l_api_name CONSTANT VARCHAR2(30) := 'get_org_sch_graph';
484 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
485 l_api_version CONSTANT NUMBER := 1.0;
486 l_msg_count NUMBER;
487 l_return_status VARCHAR2(1);
488 l_msg_data VARCHAR2(2000);
489 l_loop_window NUMBER;
490 l_loop_start_date DATE;
491 l_row_inc NUMBER := 0;
492 l_org_sch_search_rec org_sch_search_rec;
493 l_sch_graph_rec SCH_GRAPH_RESULTS_REC;
494 l_sch_graph_tbl sch_graph_results_tbl;
495 l_sch_visits_tbl sch_visits_tbl;
496 l_filter_criteria VARCHAR2(15) := G_FILTER_ALL;
497 l_filter_scheduled CONSTANT VARCHAR2(15) := 'SCHEDULED_DAYS';
498
499 -- Cursor
500 CURSOR c_space_dept_for_org (c_org_id IN NUMBER, c_dept_name IN VARCHAR2, c_space_name IN VARCHAR2)
501 IS
502 SELECT SPACE_ID,
503 SPACE_NAME,
504 DEPARTMENT_ID,
505 DEPT_DESCRIPTION,
506 ORGANIZATION_ID
507 FROM
508 (SELECT ASPL.SPACE_ID,
509 ASPL.SPACE_NAME,
510 ASPL.BOM_DEPARTMENT_ID DEPARTMENT_ID,
511 ASPL.ORGANIZATION_ID,
512 BDPT.DESCRIPTION DEPT_DESCRIPTION
513 FROM AHL_SPACES_VL ASPL,
514 BOM_DEPARTMENTS BDPT
515 WHERE ASPL.BOM_DEPARTMENT_ID = BDPT.DEPARTMENT_ID
516 AND ASPL.ORGANIZATION_ID = c_org_id
517 AND ASPL.INACTIVE_FLAG LIKE 'Y'
518
519 UNION
520
521 SELECT NULL SPACE_ID,
522 NULL SPACE_NAME,
523 BOM.DEPARTMENT_ID,
524 ORG.ORGANIZATION_ID,
525 BOM.DESCRIPTION DEPT_DESCRIPTION
526 FROM BOM_DEPARTMENTS BOM,
527 INV_ORGANIZATION_INFO_V ORG,
528 MTL_PARAMETERS MP
529 WHERE BOM.ORGANIZATION_ID = ORG.ORGANIZATION_ID
530 AND MP.ORGANIZATION_ID = BOM.ORGANIZATION_ID
531 AND MP.EAM_ENABLED_FLAG = 'Y'
532 AND ORG.ORGANIZATION_ID = c_org_id
533 )QRSLT
534 WHERE UPPER(DEPT_DESCRIPTION) LIKE UPPER(NVL(c_dept_name, DEPT_DESCRIPTION))
535 AND UPPER(NVL(SPACE_NAME,'X')) LIKE UPPER(NVL(c_space_name, NVL(SPACE_NAME,'X')))
536 ORDER BY DEPT_DESCRIPTION,
537 SPACE_NAME;
538
539 space_dept_for_org_rec c_space_dept_for_org%ROWTYPE;
540
541
542
543
544 BEGIN
545 --------------------Initialize ----------------------------------
546 -- Standard Start of API savepoint
547 SAVEPOINT GET_ORG_SCH_GRAPH;
548
549 IF (l_log_procedure >= l_log_current_level) THEN
550 fnd_log.string(l_log_procedure,
551 l_debug_key ||'.begin',
552 ' At the start of PL SQL function. '||
553 ' Org Id = ' || p_org_sch_search_rec.ORG_ID ||
554 ' Dept Name = ' || p_org_sch_search_rec.DEPARTMENT_NAME||
555 ' Space Name = ' || p_org_sch_search_rec.SPACE_NAME||
556 ' Start from Date = ' || p_org_sch_search_rec.START_FROM_DATE||
557 ' Start before Date = '|| p_org_sch_search_rec.START_BEFORE_DATE||
558 ' Display Window = ' || p_org_sch_search_rec.DISPLAY_WINDOW||
559 ' Filter Criteria = ' || p_org_sch_search_rec.RESULT_FILTER);
560 END IF;
561
562 -- Standard call to check for call compatibility.
563 IF FND_API.to_boolean(p_init_msg_list)
564 THEN
565 FND_MSG_PUB.initialize;
566 END IF;
567 -- Initialize API return status to success
568 x_return_status := FND_API.G_RET_STS_SUCCESS;
569
570 -- Initialize message list if p_init_msg_list is set to TRUE.
571 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
572 p_api_version,
573 l_api_name,G_PKG_NAME)
574 THEN
575 Fnd_Msg_Pub.ADD;
576 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
577 END IF;
578 --
579
580 IF(p_org_sch_search_rec.ORG_ID IS NULL) OR
581 (p_org_sch_search_rec.DISPLAY_WINDOW IS NULL) THEN
582 IF (l_log_statement >= l_log_current_level) THEN
583 fnd_log.string(l_log_statement,
584 L_DEBUG_KEY,
585 'Passed Mandatory fields Org id or Display window is null');
586 END IF;
587 Fnd_Message.set_name(G_APP_NAME, 'AHL_AMP_ORG_MANDATORY');
588 FND_MSG_PUB.ADD;
589 RAISE Fnd_Api.G_EXC_ERROR;
590
591 ELSIF(p_org_sch_search_rec.START_FROM_DATE IS NULL) AND
592 (p_org_sch_search_rec.START_BEFORE_DATE IS NULL) THEN
593
594 Fnd_Message.set_name(G_APP_NAME, 'AHL_AMP_DATE_MANDATORY');
595 Fnd_Msg_Pub.ADD;
596 RAISE Fnd_Api.G_EXC_ERROR;
597 END IF;
598
599 IF (p_org_sch_search_rec.RESULT_FILTER IS NOT NULL) THEN
600 l_filter_criteria := p_org_sch_search_rec.RESULT_FILTER;
601 END IF;
602
603 l_loop_window := p_org_sch_search_rec.DISPLAY_WINDOW;
604
605 IF((p_org_sch_search_rec.START_FROM_DATE IS NOT NULL) AND
606 (p_org_sch_search_rec.START_BEFORE_DATE IS NOT NULL)) OR
607 (p_org_sch_search_rec.START_BEFORE_DATE IS NULL)
608 THEN
609 l_loop_start_date := p_org_sch_search_rec.START_FROM_DATE;
610 ELSIF(p_org_sch_search_rec.START_FROM_DATE IS NULL) THEN
611 l_loop_start_date := p_org_sch_search_rec.START_BEFORE_DATE - l_loop_window + 1;
612 END IF;
613
614 IF (l_log_procedure >= l_log_current_level) THEN
615 fnd_log.string(l_log_procedure,
616 l_debug_key ||'.middle',
617 ' Before looping through rows. '||
618 ' Loop Start date = ' || l_loop_start_date||
619 ' Filter Criteria' ||l_filter_criteria);
620 END IF;
621
622 OPEN c_space_dept_for_org(p_org_sch_search_rec.ORG_ID
623 , p_org_sch_search_rec.DEPARTMENT_NAME
624 , p_org_sch_search_rec.space_name);
625
626 LOOP
627 FETCH c_space_dept_for_org INTO space_dept_for_org_rec;
628 EXIT WHEN c_space_dept_for_org%NOTFOUND;
629 -- call GET_VISITS_FOR_DATE_ORG
630 -- prepare in params...
631 l_org_sch_search_rec := NULL;
632 l_org_sch_search_rec.ORG_ID := p_org_sch_search_rec.ORG_ID;
633 l_org_sch_search_rec.DEPARTMENT_ID := space_dept_for_org_rec.DEPARTMENT_ID;
634 l_org_sch_search_rec.SPACE_ID := space_dept_for_org_rec.SPACE_ID;
635 l_org_sch_search_rec.START_FROM_DATE := l_loop_start_date;
636 l_org_sch_search_rec.DISPLAY_WINDOW := l_loop_window;
637 l_org_sch_search_rec.RESULT_FILTER := l_filter_criteria;
638
639 GET_VISITS_FOR_DATE_ORG(
640 p_api_version => p_api_version,
641 p_init_msg_list => FND_API.G_FALSE,
642 p_validation_level => p_validation_level,
643 x_return_status => l_return_status,
644 x_msg_count => l_msg_count,
645 x_msg_data => l_msg_data,
646 p_org_sch_search_rec => l_org_sch_search_rec,
647 x_sch_graph_rec => l_sch_graph_rec,
648 x_sch_visits_tbl => l_sch_visits_tbl);
649 -- end of call GET_VISITS_FOR_DATE_ORG
650
651 l_sch_graph_rec.ORG_ID := p_org_sch_search_rec.ORG_ID;
652 l_sch_graph_rec.DEPARTMENT_ID := space_dept_for_org_rec.DEPARTMENT_ID;
653 l_sch_graph_rec.DEPARTMENT_DESC := space_dept_for_org_rec.DEPT_DESCRIPTION;
654 l_sch_graph_rec.SPACE_ID := space_dept_for_org_rec.SPACE_ID;
655 L_SCH_GRAPH_REC.SPACE_NAME := SPACE_DEPT_FOR_ORG_REC.SPACE_NAME;
656
657 --add return rec type to the table
658 IF (l_filter_criteria = l_filter_scheduled) AND
659 NOT (l_sch_graph_rec.FILTER_REC) THEN
660 x_sch_graph_results_tbl(l_row_inc) := l_sch_graph_rec;
661 l_row_inc := l_row_inc + 1;
662 ELSIF (l_filter_criteria <> l_filter_scheduled) AND
663 (l_sch_graph_rec.FILTER_REC) THEN
664 x_sch_graph_results_tbl(l_row_inc) := l_sch_graph_rec;
665 l_row_inc := l_row_inc + 1;
666 END IF;
667 END LOOP;
668 CLOSE c_space_dept_for_org;
669
670 l_msg_count := Fnd_Msg_Pub.count_msg;
671 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
672 IF (l_log_statement >= l_log_current_level) THEN
673 fnd_log.string(l_log_statement,
674 L_DEBUG_KEY,
675 'Errors from GET_VISITS_FOR_DATE_ORG. Message count: ' ||
676 l_msg_count || ', Message data: ' || l_msg_data);
677 END IF;
678 x_msg_count := l_msg_count;
679 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
680 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
681 END IF;
682
683 x_return_status := Fnd_Api.g_ret_sts_success;
684 IF (l_log_procedure >= l_log_current_level) THEN
685 fnd_log.string(l_log_procedure,
686 L_DEBUG_KEY || '.end',
687 'Return Status = ' || x_return_status);
688 END IF;
689
690 EXCEPTION
691 WHEN Fnd_Api.G_EXC_ERROR THEN
692 x_return_status := Fnd_Api.G_RET_STS_ERROR;
693 ROLLBACK TO GET_ORG_SCH_GRAPH;
694 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
695 p_data => x_msg_data,
696 p_encoded => Fnd_Api.g_false);
697 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
698 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
699 ROLLBACK TO GET_ORG_SCH_GRAPH;
700 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
701 p_data => x_msg_data,
702 p_encoded => Fnd_Api.g_false);
703 WHEN OTHERS THEN
704 ROLLBACK TO GET_ORG_SCH_GRAPH;
705 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
706 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
707 THEN
708 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
709 END IF;
710 Fnd_Msg_Pub.count_and_get (
711 p_encoded => Fnd_Api.g_false,
712 p_count => x_msg_count,
713 p_data => x_msg_data);
714
715 END GET_ORG_SCH_GRAPH;
716
717 -- Procedure name : GET_VISITS_FOR_DATE_ORG
718 -- Type : Public
719 -- Parameters :
720 --
721 -- Standard IN Parameters :
722 -- p_api_version NUMBER Required
723 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
724 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
725 --
726 -- Standard OUT Parameters :
727 -- x_return_status VARCHAR2 Required
728 -- x_msg_count NUMBER Required
729 -- x_msg_data VARCHAR2 Required
730 -- GET_VISITS_FOR_DATE_ORG params
731 -- p_org_sch_search_rec ORG_SCH_SEARCH_REC Required
732 -- x_sch_graph_rec SCH_GRAPH_RESULTS_REC
733 -- x_sch_visits_tbl SCH_VISITS_TBL
734
735 PROCEDURE GET_VISITS_FOR_DATE_ORG(
736 p_api_version IN NUMBER := 1.0,
737 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
738 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
739 x_return_status OUT NOCOPY VARCHAR2,
740 x_msg_count OUT NOCOPY NUMBER,
741 x_msg_data OUT NOCOPY VARCHAR2,
742 p_org_sch_search_rec IN org_sch_search_rec,
743 x_sch_graph_rec OUT NOCOPY SCH_GRAPH_RESULTS_REC,
744 x_sch_visits_tbl OUT NOCOPY sch_visits_tbl
745 ) IS
746
747 -- LOCAL VARIABLE
748 l_api_name CONSTANT VARCHAR2(30) := 'GET_VISITS_FOR_DATE_ORG';
749 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
750 l_api_version CONSTANT NUMBER := 1.0;
751 l_msg_count NUMBER;
752 l_return_status VARCHAR2(1);
753 l_msg_data VARCHAR2(2000);
754 l_loop_window NUMBER;
755 l_loop_start_date DATE;
756 l_column_inc NUMBER := 0;
757 l_day_inc NUMBER := 1;
758 l_visit_rec SCH_VISITS_REC;
759 l_visit_count NUMBER := 0;
760 l_org_sch_search_rec org_sch_search_rec;
761 l_sch_graph_rec SCH_GRAPH_RESULTS_REC;
762 l_sch_visits_tbl sch_visits_tbl;
763
764 -- Cursor
765 -- inner cursor for ROW wise calculation for space
766 CURSOR c_visit_for_space_day (c_org_id IN NUMBER, c_dept_id IN NUMBER, c_space_id IN NUMBER, c_loop_date IN DATE)
767 IS
768 SELECT ASA.SPACE_ID,
769 AVL.visit_id ,
770 NVL2(ASA.START_FROM , ASA.START_FROM , AVL.START_DATE_TIME) START_DATE_TIME ,
771 NVL2(ASA.END_TO , ASA.END_TO , AVL.CLOSE_DATE_TIME) CLOSE_DATE_TIME
772 FROM AHL_SPACE_ASSIGNMENTS ASA,
773 AHL_VISITS_VL AVL
774 WHERE ASA.visit_id = AVL.visit_id
775 AND ASA.SPACE_ID = c_space_id
776 AND AVL.department_id = c_dept_id
777 AND AVL.organization_id = c_org_id
778 AND AVL.status_code IN ('PLANNING','RELEASED','PARTIALLY RELEASED')
779 AND AVL.template_flag = 'N'
780 AND TRUNC(c_loop_date) BETWEEN TRUNC(NVL2(ASA.START_FROM, ASA.START_FROM
781 , AVL.START_DATE_TIME))
782 AND TRUNC(NVL2(ASA.END_TO, ASA.END_TO
783 , NVL(AVL.CLOSE_DATE_TIME, c_loop_date + 1)))
784 ORDER BY AVL.START_DATE_TIME;
785
786 c_visit_for_space_day_rec c_visit_for_space_day%ROWTYPE;
787
788 -- inner cursor for ROW wise calculation for dept only
789 CURSOR c_visit_for_dept_day (c_org_id IN NUMBER, c_dept_id IN NUMBER, c_loop_date IN DATE)
790 IS
791 SELECT AVL.visit_id,
792 AVL.START_DATE_TIME,
793 AVL.CLOSE_DATE_TIME
794 FROM AHL_VISITS_VL AVL
795 WHERE AVL.visit_id NOT IN
796 (SELECT DISTINCT ASA.VISIT_ID
797 FROM AHL_SPACE_ASSIGNMENTS ASA,
798 AHL_VISITS_VL AVL
799 WHERE TRUNC(c_loop_date) BETWEEN TRUNC(NVL2(ASA.START_FROM, ASA.START_FROM , AVL.START_DATE_TIME))
800 AND TRUNC(NVL2(ASA.END_TO, ASA.END_TO , NVL(AVL.CLOSE_DATE_TIME, c_loop_date)))
801 )
802 AND AVL.department_id = c_dept_id
803 AND AVL.organization_id = c_org_id
804 AND AVL.status_code IN ('PLANNING','RELEASED','PARTIALLY RELEASED')
805 AND AVL.TEMPLATE_FLAG = 'N'
806 AND TRUNC(c_loop_date) BETWEEN TRUNC(AVL.START_DATE_TIME)
807 AND TRUNC(NVL(AVL.CLOSE_DATE_TIME, c_loop_date + 1))
808 ORDER BY AVL.START_DATE_TIME;
809
810 c_visit_for_dept_day_rec c_visit_for_dept_day%ROWTYPE;
811
812 BEGIN
813 --------------------Initialize ----------------------------------
814 -- Standard Start of API savepoint
815 SAVEPOINT GET_VISITS_FOR_DATE_ORG;
816
817 IF (l_log_procedure >= l_log_current_level) THEN
818 fnd_log.string(l_log_procedure,
819 l_debug_key ||'.begin',
820 ' At the start of PL SQL function. '||
821 ' Org Id = ' || p_org_sch_search_rec.ORG_ID ||
822 ' Dept Id = ' || p_org_sch_search_rec.DEPARTMENT_ID||
823 ' Space Id = ' || p_org_sch_search_rec.SPACE_ID||
824 ' Loop Start Date = ' || p_org_sch_search_rec.START_FROM_DATE||
825 ' Loop Window = ' || p_org_sch_search_rec.DISPLAY_WINDOW||
826 ' Filter Criteria = ' || p_org_sch_search_rec.RESULT_FILTER);
827 END IF;
828
829 -- Standard call to check for call compatibility.
830 IF FND_API.to_boolean(p_init_msg_list)
831 THEN
832 FND_MSG_PUB.initialize;
833 END IF;
834 -- Initialize API return status to success
835 x_return_status := FND_API.G_RET_STS_SUCCESS;
836
837 -- Initialize message list if p_init_msg_list is set to TRUE.
838 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
839 p_api_version,
840 l_api_name,G_PKG_NAME)
841 THEN
842 Fnd_Msg_Pub.ADD;
843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
844 END IF;
845 --
846
847 IF(p_org_sch_search_rec.ORG_ID IS NULL) OR
848 (p_org_sch_search_rec.DEPARTMENT_ID IS NULL) OR
849 (p_org_sch_search_rec.START_FROM_DATE IS NULL) OR
850 (p_org_sch_search_rec.DISPLAY_WINDOW IS NULL) THEN
851 IF (l_log_statement >= l_log_current_level) THEN
852 fnd_log.string(l_log_statement,
853 L_DEBUG_KEY,
854 'One of the Passed Mandatory fields is null');
855 END IF;
856 Fnd_Message.set_name(G_APP_NAME, 'AHL_AMP_ORG_MANDATORY');
857 FND_MSG_PUB.ADD;
858 RAISE Fnd_Api.G_EXC_ERROR;
859 END IF;
860
861 l_loop_window := p_org_sch_search_rec.DISPLAY_WINDOW;
862 l_loop_start_date := p_org_sch_search_rec.START_FROM_DATE;
863
864 LOOP
865 EXIT WHEN l_day_inc > l_loop_window;
866 l_visit_count := 0;
867 l_sch_visits_tbl.DELETE;
868 IF (p_org_sch_search_rec.SPACE_ID IS NOT NULL) THEN
869 --Space Cursor
870 OPEN c_visit_for_space_day(p_org_sch_search_rec.ORG_ID
871 ,p_org_sch_search_rec.DEPARTMENT_ID
872 ,p_org_sch_search_rec.SPACE_ID
873 ,l_loop_start_date);
874 LOOP
875 FETCH c_visit_for_space_day INTO c_visit_for_space_day_rec;
876 EXIT WHEN c_visit_for_space_day%NOTFOUND;
877 l_visit_rec.VISIT_ID := c_visit_for_space_day_rec.visit_id;
878 l_visit_rec.START_DATE := c_visit_for_space_day_rec.START_DATE_TIME;
879 l_visit_rec.END_DATE := c_visit_for_space_day_rec.CLOSE_DATE_TIME;
880 l_sch_visits_tbl(l_visit_count) := l_visit_rec;
881 l_visit_count := l_visit_count + 1;
882 END LOOP;
883 CLOSE c_visit_for_space_day;
884
885 -- call GET_GRAPH_REC_FOR_DATE
886 GET_GRAPH_REC_FOR_DATE(
887 p_graph_rec_date => l_loop_start_date,
888 p_graph_rec_num => l_day_inc,
889 p_visit_count => l_visit_count,
890 p_filter_criteria => p_org_sch_search_rec.RESULT_FILTER,
891 p_rec_type => G_SPACE_TYPE,
892 p_sch_visits_tbl => l_sch_visits_tbl,
893 p_x_sch_graph_rec => l_sch_graph_rec);
894
895 ELSIF (p_org_sch_search_rec.SPACE_ID IS NULL) THEN
896 -- Dept cursor
897 OPEN c_visit_for_dept_day(p_org_sch_search_rec.ORG_ID
898 ,p_org_sch_search_rec.DEPARTMENT_ID
899 ,l_loop_start_date);
900 LOOP
901 FETCH c_visit_for_dept_day INTO c_visit_for_dept_day_rec;
902 EXIT WHEN c_visit_for_dept_day%NOTFOUND;
903 l_visit_rec.VISIT_ID := c_visit_for_dept_day_rec.visit_id;
904 l_visit_rec.START_DATE := c_visit_for_dept_day_rec.START_DATE_TIME;
905 l_visit_rec.END_DATE := c_visit_for_dept_day_rec.CLOSE_DATE_TIME;
906 l_sch_visits_tbl(l_visit_count) := l_visit_rec;
907 l_visit_count := l_visit_count + 1;
908 END LOOP;
909 CLOSE c_visit_for_dept_day;
910
911 -- call GET_GRAPH_REC_FOR_DATE
912 GET_GRAPH_REC_FOR_DATE(
913 p_graph_rec_date => l_loop_start_date,
914 p_graph_rec_num => l_day_inc,
915 p_visit_count => l_visit_count,
916 p_filter_criteria => p_org_sch_search_rec.RESULT_FILTER,
917 p_rec_type => G_DEPT_TYPE,
918 p_sch_visits_tbl => l_sch_visits_tbl,
919 p_x_sch_graph_rec => l_sch_graph_rec);
920 END IF;
921
922 l_loop_start_date := l_loop_start_date+1;
923 l_day_inc := l_day_inc+1;
924 END LOOP;
925 x_sch_visits_tbl := l_sch_visits_tbl;
926 x_sch_graph_rec := l_sch_graph_rec;
927
928
929 l_msg_count := Fnd_Msg_Pub.count_msg;
930 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
931 IF (l_log_statement >= l_log_current_level) THEN
932 fnd_log.string(l_log_statement,
933 L_DEBUG_KEY,
934 'Errors from GET_VISITS_FOR_DATE_ORG. Message count: ' ||
935 l_msg_count || ', Message data: ' || l_msg_data);
936 END IF;
937 x_msg_count := l_msg_count;
938 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
939 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
940 END IF;
941
942 x_return_status := Fnd_Api.g_ret_sts_success;
943 IF (l_log_procedure >= l_log_current_level) THEN
944 fnd_log.string(l_log_procedure,
945 L_DEBUG_KEY || '.end',
946 'Return Status = ' || x_return_status);
947 END IF;
948
949 EXCEPTION
950 WHEN Fnd_Api.G_EXC_ERROR THEN
951 x_return_status := Fnd_Api.G_RET_STS_ERROR;
952 ROLLBACK TO GET_VISITS_FOR_DATE_ORG;
953 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
954 p_data => x_msg_data,
955 p_encoded => Fnd_Api.g_false);
956 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
957 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
958 ROLLBACK TO GET_VISITS_FOR_DATE_ORG;
959 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
960 p_data => x_msg_data,
961 p_encoded => Fnd_Api.g_false);
962 WHEN OTHERS THEN
963 ROLLBACK TO GET_VISITS_FOR_DATE_ORG;
964 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
965 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
966 THEN
967 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
968 END IF;
969 Fnd_Msg_Pub.count_and_get (
970 p_encoded => Fnd_Api.g_false,
971 p_count => x_msg_count,
972 p_data => x_msg_data);
973
974 END GET_VISITS_FOR_DATE_ORG;
975
976 -- Procedure name : GET_FLT_SCH_GRAPH
977 -- Type : Public
978 -- Parameters :
979 --
980 -- Standard IN Parameters :
981 -- p_api_version NUMBER Required
982 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
983 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
984 --
985 -- Standard OUT Parameters :
986 -- x_return_status VARCHAR2 Required
987 -- x_msg_count NUMBER Required
988 -- x_msg_data VARCHAR2 Required
989 -- GET_ORG_SCH_GRAPH params
990 -- p_flt_sch_search_rec FLEET_SCH_SEARCH_REC Required
991 -- x_sch_graph_results_tbl SCH_GRAPH_RESULTS_TBL
992
993 PROCEDURE GET_FLT_SCH_GRAPH(
994 p_api_version IN NUMBER := 1.0,
995 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
996 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
997 x_return_status OUT NOCOPY VARCHAR2,
998 x_msg_count OUT NOCOPY NUMBER,
999 x_msg_data OUT NOCOPY VARCHAR2,
1000 p_flt_sch_search_rec IN FLEET_SCH_SEARCH_REC,
1001 x_sch_graph_results_tbl OUT NOCOPY sch_graph_results_tbl
1002 ) IS
1003
1004 -- LOCAL VARIABLE
1005 l_api_name CONSTANT VARCHAR2(30) := 'GET_FLT_SCH_GRAPH';
1006 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
1007 l_api_version CONSTANT NUMBER := 1.0;
1008 l_msg_count NUMBER;
1009 l_return_status VARCHAR2(1);
1010 l_msg_data VARCHAR2(2000);
1011 l_loop_window NUMBER;
1012 l_loop_start_date DATE;
1013 l_end_date DATE;
1014 l_visit_duration NUMBER := 0;
1015 l_row_inc NUMBER := 0;
1016 l_flt_sch_search_rec FLEET_SCH_SEARCH_REC;
1017 l_sch_graph_rec SCH_GRAPH_RESULTS_REC;
1018 l_sch_graph_tbl sch_graph_results_tbl;
1019 l_sch_visits_tbl sch_visits_tbl;
1020
1021 -- Cursor
1022 CURSOR c_unit_for_flt (c_fleet_id IN NUMBER, c_unit_name IN VARCHAR2, c_mc_name IN VARCHAR2, c_start_date IN DATE, c_end_date IN DATE)
1023 IS
1024 SELECT DISTINCT UCH.NAME UNIT_NAME,
1025 UCH.UNIT_CONFIG_HEADER_ID UNIT_ID
1026 FROM AHL_UNIT_CONFIG_HEADERS UCH,
1027 AHL_FLEET_HEADERS_B FHB,
1028 AHL_FLEET_UNIT_ASSOCS FUA,
1029 AHL_MC_HEADERS_B MC
1030 WHERE FUA.UNIT_CONFIG_HEADER_ID = UCH.UNIT_CONFIG_HEADER_ID
1031 AND FUA.FLEET_HEADER_ID = FHB.FLEET_HEADER_ID
1032 AND UCH.MASTER_CONFIG_ID = MC.MC_HEADER_ID
1033 AND FUA.SIMULATION_PLAN_ID =
1034 (SELECT ASP.SIMULATION_PLAN_ID
1035 FROM AHL_SIMULATION_PLANS_B ASP
1036 WHERE ASP.PRIMARY_PLAN_FLAG = 'Y'
1037 AND ASP.status_code = 'ACTIVE'
1038 AND ASP.simulation_type = 'UMP' -- Sthilak added this extra filter condition AMP-AutoVisit Changes
1039 )
1040 AND UCH.UNIT_CONFIG_STATUS_CODE <> 'DRAFT'
1041 AND TRUNC(NVL(UCH.ACTIVE_END_DATE,c_end_date+1)) > TRUNC(c_end_date)
1042 AND FHB.STATUS_CODE = 'COMPLETE'
1043 AND FHB.FLEET_HEADER_ID = c_fleet_id
1044 AND UPPER(UCH.NAME) LIKE UPPER(NVL(c_unit_name,UCH.NAME))
1045 AND UPPER(MC.NAME) LIKE UPPER(NVL(c_mc_name,MC.NAME))
1046 AND (TRUNC(c_start_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
1047 OR TRUNC(c_end_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
1048 OR TRUNC(FUA.association_start) BETWEEN TRUNC(c_start_date) AND TRUNC(c_end_date))
1049 ORDER BY UCH.NAME;
1050
1051 unit_for_flt_rec c_unit_for_flt%ROWTYPE;
1052
1053
1054
1055 BEGIN
1056 --------------------Initialize ----------------------------------
1057 -- Standard Start of API savepoint
1058 SAVEPOINT GET_FLT_SCH_GRAPH;
1059
1060 IF (l_log_procedure >= l_log_current_level) THEN
1061 fnd_log.string(l_log_procedure,
1062 l_debug_key ||'.begin',
1063 ' At the start of PL SQL function. '||
1064 ' Fleet Id = ' || p_flt_sch_search_rec.FLEET_ID ||
1065 ' Unit Name = ' || p_flt_sch_search_rec.UNIT_NAME||
1066 ' MC Name = ' || p_flt_sch_search_rec.MASTER_CONFIG||
1067 ' Minimum Duration = ' || p_flt_sch_search_rec.MINIMUM_DURATION||
1068 ' Duration UOM = ' || p_flt_sch_search_rec.UOM||
1069 ' Start from Date = ' || p_flt_sch_search_rec.START_FROM_DATE||
1070 ' Start before Date = '|| p_flt_sch_search_rec.START_BEFORE_DATE||
1071 ' Display Window = ' || p_flt_sch_search_rec.DISPLAY_WINDOW);
1072 END IF;
1073
1074 -- Standard call to check for call compatibility.
1075 IF FND_API.to_boolean(p_init_msg_list)
1076 THEN
1077 FND_MSG_PUB.initialize;
1078 END IF;
1079 -- Initialize API return status to success
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081
1082 -- Initialize message list if p_init_msg_list is set to TRUE.
1083 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1084 p_api_version,
1085 l_api_name,G_PKG_NAME)
1086 THEN
1087 Fnd_Msg_Pub.ADD;
1088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1089 END IF;
1090 --
1091
1092 IF(p_flt_sch_search_rec.FLEET_ID IS NULL) OR
1093 (p_flt_sch_search_rec.DISPLAY_WINDOW IS NULL) THEN
1094 IF (l_log_statement >= l_log_current_level) THEN
1095 fnd_log.string(l_log_statement,
1096 L_DEBUG_KEY,
1097 'Passed Mandatory fields Org id or Display window is null');
1098 END IF;
1099 Fnd_Message.set_name(G_APP_NAME, 'AHL_AMP_FLEET_MANDATORY');
1100 FND_MSG_PUB.ADD;
1101 RAISE Fnd_Api.G_EXC_ERROR;
1102
1103 ELSIF(p_flt_sch_search_rec.START_FROM_DATE IS NULL) AND
1104 (p_flt_sch_search_rec.START_BEFORE_DATE IS NULL) THEN
1105
1106 Fnd_Message.set_name(G_APP_NAME, 'AHL_AMP_DATE_MANDATORY');
1107 Fnd_Msg_Pub.ADD;
1108 RAISE Fnd_Api.G_EXC_ERROR;
1109 END IF;
1110
1111 l_loop_window := p_flt_sch_search_rec.DISPLAY_WINDOW;
1112
1113 IF((p_flt_sch_search_rec.START_FROM_DATE IS NOT NULL) AND
1114 (p_flt_sch_search_rec.START_BEFORE_DATE IS NOT NULL)) OR
1115 (p_flt_sch_search_rec.START_BEFORE_DATE IS NULL)
1116 THEN
1117 l_loop_start_date := p_flt_sch_search_rec.START_FROM_DATE;
1118 l_end_date := p_flt_sch_search_rec.START_FROM_DATE + l_loop_window - 1;
1119 ELSIF(p_flt_sch_search_rec.START_FROM_DATE IS NULL) THEN
1120 l_loop_start_date := p_flt_sch_search_rec.START_BEFORE_DATE - l_loop_window + 1;
1121 l_end_date := p_flt_sch_search_rec.START_BEFORE_DATE;
1122 END IF;
1123
1124 IF(p_flt_sch_search_rec.MINIMUM_DURATION IS NOT NULL) THEN
1125 IF(p_flt_sch_search_rec.UOM = 'HOUR') THEN
1126 l_visit_duration := p_flt_sch_search_rec.MINIMUM_DURATION;
1127 ELSE
1128 l_visit_duration := p_flt_sch_search_rec.MINIMUM_DURATION * 24;
1129 END IF;
1130 END IF;
1131
1132 IF (l_log_procedure >= l_log_current_level) THEN
1133 fnd_log.string(l_log_procedure,
1134 l_debug_key ||'.middle',
1135 ' Before looping through rows. '||
1136 ' Loop Start date = ' || l_loop_start_date ||
1137 ' End date = ' || l_end_date ||
1138 ' Duration in Hours = ' || l_visit_duration);
1139 END IF;
1140
1141 OPEN c_unit_for_flt(p_flt_sch_search_rec.FLEET_ID
1142 , p_flt_sch_search_rec.UNIT_NAME
1143 , p_flt_sch_search_rec.MASTER_CONFIG
1144 , l_loop_start_date
1145 , l_end_date);
1146
1147 LOOP
1148 FETCH c_unit_for_flt INTO unit_for_flt_rec;
1149 EXIT WHEN c_unit_for_flt%NOTFOUND;
1150 -- call GET_VISITS_FOR_DATE_FLT
1151 -- prepare in params...
1152 l_flt_sch_search_rec := NULL;
1153 l_flt_sch_search_rec.FLEET_ID := p_flt_sch_search_rec.FLEET_ID;
1154 l_flt_sch_search_rec.UNIT_ID := unit_for_flt_rec.UNIT_ID;
1155 l_flt_sch_search_rec.MINIMUM_DURATION := l_visit_duration;
1156 l_flt_sch_search_rec.START_FROM_DATE := l_loop_start_date;
1157 l_flt_sch_search_rec.DISPLAY_WINDOW := l_loop_window;
1158
1159 GET_VISITS_FOR_DATE_FLT(
1160 p_api_version => p_api_version,
1161 p_init_msg_list => FND_API.G_FALSE,
1162 p_validation_level => p_validation_level,
1163 x_return_status => l_return_status,
1164 x_msg_count => l_msg_count,
1165 x_msg_data => l_msg_data,
1166 p_flt_sch_search_rec => l_flt_sch_search_rec,
1167 x_sch_graph_rec => l_sch_graph_rec,
1168 x_sch_visits_tbl => l_sch_visits_tbl);
1169 -- end of call GET_VISITS_FOR_DATE_FLT
1170
1171 l_sch_graph_rec.UNIT_ID := unit_for_flt_rec.UNIT_ID;
1172 l_sch_graph_rec.UNIT_NAME := unit_for_flt_rec.UNIT_NAME;
1173
1174 --add return rec type to the table
1175 x_sch_graph_results_tbl(l_row_inc) := l_sch_graph_rec;
1176 l_row_inc := l_row_inc + 1;
1177
1178 END LOOP;
1179 CLOSE c_unit_for_flt;
1180
1181 l_msg_count := Fnd_Msg_Pub.count_msg;
1182 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
1183 IF (l_log_statement >= l_log_current_level) THEN
1184 fnd_log.string(l_log_statement,
1185 L_DEBUG_KEY,
1186 'Errors from GET_VISITS_FOR_DATE_FLT. Message count: ' ||
1187 l_msg_count || ', Message data: ' || l_msg_data);
1188 END IF;
1189 x_msg_count := l_msg_count;
1190 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1191 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1192 END IF;
1193
1194 x_return_status := Fnd_Api.g_ret_sts_success;
1195 IF (l_log_procedure >= l_log_current_level) THEN
1196 fnd_log.string(l_log_procedure,
1197 L_DEBUG_KEY || '.end',
1198 'Return Status = ' || x_return_status);
1199 END IF;
1200
1201 EXCEPTION
1202 WHEN Fnd_Api.G_EXC_ERROR THEN
1203 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1204 ROLLBACK TO GET_FLT_SCH_GRAPH;
1205 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1206 p_data => x_msg_data,
1207 p_encoded => Fnd_Api.g_false);
1208 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1209 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1210 ROLLBACK TO GET_FLT_SCH_GRAPH;
1211 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1212 p_data => x_msg_data,
1213 p_encoded => Fnd_Api.g_false);
1214 WHEN OTHERS THEN
1215 ROLLBACK TO GET_FLT_SCH_GRAPH;
1216 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1217 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1218 THEN
1219 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1220 END IF;
1221 Fnd_Msg_Pub.count_and_get (
1222 p_encoded => Fnd_Api.g_false,
1223 p_count => x_msg_count,
1224 p_data => x_msg_data);
1225
1226 END GET_FLT_SCH_GRAPH;
1227
1228 -- Procedure name : GET_VISITS_FOR_DATE_FLT
1229 -- Type : Public
1230 -- Parameters :
1231 --
1232 -- Standard IN Parameters :
1233 -- p_api_version NUMBER Required
1234 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
1235 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
1236 --
1237 -- Standard OUT Parameters :
1238 -- x_return_status VARCHAR2 Required
1239 -- x_msg_count NUMBER Required
1240 -- x_msg_data VARCHAR2 Required
1241 -- GET_VISITS_FOR_DATE_FLT params
1242 -- p_flt_sch_search_rec FLEET_SCH_SEARCH_REC Required
1243 -- x_sch_graph_rec SCH_GRAPH_RESULTS_REC
1244 -- x_sch_visits_tbl SCH_VISITS_TBL
1245
1246 PROCEDURE GET_VISITS_FOR_DATE_FLT(
1247 p_api_version IN NUMBER := 1.0,
1248 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1249 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1250 x_return_status OUT NOCOPY VARCHAR2,
1251 x_msg_count OUT NOCOPY NUMBER,
1252 x_msg_data OUT NOCOPY VARCHAR2,
1253 p_flt_sch_search_rec IN FLEET_SCH_SEARCH_REC,
1254 x_sch_graph_rec OUT NOCOPY SCH_GRAPH_RESULTS_REC,
1255 x_sch_visits_tbl OUT NOCOPY sch_visits_tbl
1256 ) IS
1257
1258 -- LOCAL VARIABLE
1259 l_api_name CONSTANT VARCHAR2(30) := 'GET_VISITS_FOR_DATE_FLT';
1260 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
1261 l_api_version CONSTANT NUMBER := 1.0;
1262 l_msg_count NUMBER;
1263 l_return_status VARCHAR2(1);
1264 l_msg_data VARCHAR2(2000);
1265 l_loop_window NUMBER;
1266 l_loop_start_date DATE;
1267 l_column_inc NUMBER := 0;
1268 l_day_inc NUMBER := 1;
1269 l_visit_rec SCH_VISITS_REC;
1270 l_visit_count NUMBER := 0;
1271 l_flt_sch_search_rec fleet_sch_search_rec;
1272 l_sch_graph_rec SCH_GRAPH_RESULTS_REC;
1273 l_sch_visits_tbl sch_visits_tbl;
1274 l_dummy VARCHAR2(1);
1275 l_not_for_sch VARCHAR2(1) := FND_API.G_FALSE;
1276
1277 -- Cursor
1278 CURSOR c_visit_for_unit_day (c_unit_id IN NUMBER, c_duration IN NUMBER, c_loop_date IN DATE)
1279 IS
1280 SELECT AVT.visit_id,
1281 AVT.START_DATE_TIME,
1282 AVT.CLOSE_DATE_TIME
1283 FROM
1284 (SELECT AVL.visit_id,
1285 AVL.START_DATE_TIME,
1286 AVL.CLOSE_DATE_TIME,
1287 UCH.UNIT_CONFIG_HEADER_ID,
1288 TRUNC((AVL.CLOSE_DATE_TIME - AVL.START_DATE_TIME)*24) visit_duration
1289 FROM ahl_visits_vl AVL,
1290 AHL_UNIT_CONFIG_HEADERS UCH
1291 WHERE AVL.status_code IN ('PLANNING','RELEASED','PARTIALLY RELEASED')
1292 AND AVL.template_flag = 'N'
1293 AND UCH.csi_item_instance_id = AVL.Item_Instance_id
1294 ) AVT
1295 WHERE NVL(AVT.visit_duration,10000) > NVL(c_duration,0)
1296 AND AVT.UNIT_CONFIG_HEADER_ID = c_unit_id
1297 AND TRUNC(c_loop_date) BETWEEN TRUNC(AVT.START_DATE_TIME)
1298 AND TRUNC(NVL(AVT.CLOSE_DATE_TIME, c_loop_date + 1))
1299 ORDER BY AVT.START_DATE_TIME;
1300
1301 c_visit_for_unit_day_rec c_visit_for_unit_day%ROWTYPE;
1302
1303 CURSOR c_flt_unit_ass_day ( c_fleet_id IN NUMBER, c_unit_id IN NUMBER, c_loop_date IN DATE)
1304 IS
1305 SELECT 'X'
1306 FROM AHL_UNIT_CONFIG_HEADERS UCH,
1307 AHL_FLEET_HEADERS_B FHB,
1308 AHL_FLEET_UNIT_ASSOCS FUA
1309 WHERE fua.unit_config_header_id = uch.unit_config_header_id
1310 AND FUA.FLEET_HEADER_ID = FHB.FLEET_HEADER_ID
1311 AND FUA.SIMULATION_PLAN_ID =
1312 (SELECT ASP.SIMULATION_PLAN_ID
1313 FROM AHL_SIMULATION_PLANS_B ASP
1314 WHERE ASP.PRIMARY_PLAN_FLAG = 'Y'
1315 AND ASP.status_code = 'ACTIVE'
1316 AND ASP.simulation_type = 'UMP' -- Sthilak added this extra filter condition AMP-AutoVisit Changes
1317 )
1318 AND UCH.UNIT_CONFIG_STATUS_CODE <> 'DRAFT'
1319 AND TRUNC(NVL(uch.active_end_date,c_loop_date+1)) > TRUNC(c_loop_date)
1320 AND fhb.status_code = 'COMPLETE'
1321 AND fua.unit_config_header_id = c_unit_id
1322 AND fua.fleet_header_id = c_fleet_id
1323 AND c_loop_date BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_loop_date));
1324
1325 BEGIN
1326 --------------------Initialize ----------------------------------
1327 -- Standard Start of API savepoint
1328 SAVEPOINT GET_VISITS_FOR_DATE_FLT;
1329
1330 IF (l_log_procedure >= l_log_current_level) THEN
1331 fnd_log.string(l_log_procedure,
1332 l_debug_key ||'.begin',
1333 ' At the start of PL SQL function. '||
1334 ' Unit Id = ' || p_flt_sch_search_rec.UNIT_ID ||
1335 ' Minimum Duration in hours = ' || p_flt_sch_search_rec.MINIMUM_DURATION||
1336 ' Loop Start Date = ' || p_flt_sch_search_rec.START_FROM_DATE||
1337 ' Loop Window = ' || p_flt_sch_search_rec.DISPLAY_WINDOW);
1338 END IF;
1339
1340 -- Standard call to check for call compatibility.
1341 IF FND_API.to_boolean(p_init_msg_list)
1342 THEN
1343 FND_MSG_PUB.initialize;
1344 END IF;
1345 -- Initialize API return status to success
1346 x_return_status := FND_API.G_RET_STS_SUCCESS;
1347
1348 -- Initialize message list if p_init_msg_list is set to TRUE.
1349 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1350 p_api_version,
1351 l_api_name,G_PKG_NAME)
1352 THEN
1353 Fnd_Msg_Pub.ADD;
1354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1355 END IF;
1356 --
1357
1358 IF(p_flt_sch_search_rec.UNIT_ID IS NULL) OR
1359 (p_flt_sch_search_rec.MINIMUM_DURATION IS NULL) OR
1360 (p_flt_sch_search_rec.START_FROM_DATE IS NULL) OR
1361 (p_flt_sch_search_rec.DISPLAY_WINDOW IS NULL) THEN
1362 IF (l_log_statement >= l_log_current_level) THEN
1363 fnd_log.string(l_log_statement,
1364 L_DEBUG_KEY,
1365 'One of the Passed Mandatory fields is null');
1366 END IF;
1367 Fnd_Message.set_name(G_APP_NAME, 'AHL_AMP_FLEET_MANDATORY');
1368 FND_MSG_PUB.ADD;
1369 RAISE Fnd_Api.G_EXC_ERROR;
1370 END IF;
1371
1372 l_loop_window := p_flt_sch_search_rec.DISPLAY_WINDOW;
1373 l_loop_start_date := p_flt_sch_search_rec.START_FROM_DATE;
1374
1375 LOOP
1376 EXIT WHEN l_day_inc > l_loop_window;
1377 l_visit_count := 0;
1378 l_sch_visits_tbl.DELETE;
1379
1380 OPEN c_visit_for_unit_day(p_flt_sch_search_rec.UNIT_ID
1381 ,p_flt_sch_search_rec.MINIMUM_DURATION
1382 ,l_loop_start_date);
1383 LOOP
1384 FETCH c_visit_for_unit_day INTO c_visit_for_unit_day_rec;
1385 EXIT WHEN c_visit_for_unit_day%NOTFOUND;
1386 l_visit_rec.VISIT_ID := c_visit_for_unit_day_rec.visit_id;
1387 l_visit_rec.START_DATE := c_visit_for_unit_day_rec.START_DATE_TIME;
1388 l_visit_rec.END_DATE := c_visit_for_unit_day_rec.CLOSE_DATE_TIME;
1389 l_sch_visits_tbl(l_visit_count) := l_visit_rec;
1390 l_visit_count := l_visit_count + 1;
1391 END LOOP;
1392 CLOSE c_visit_for_unit_day;
1393
1394 OPEN c_flt_unit_ass_day(p_flt_sch_search_rec.FLEET_ID
1395 ,p_flt_sch_search_rec.UNIT_ID
1396 ,l_loop_start_date);
1397
1398 FETCH c_flt_unit_ass_day INTO l_dummy;
1399 IF c_flt_unit_ass_day%NOTFOUND THEN
1400 l_not_for_sch := FND_API.G_TRUE;
1401 ELSE
1402 l_not_for_sch := FND_API.G_FALSE;
1403 END IF;
1404 CLOSE c_flt_unit_ass_day;
1405
1406 -- call GET_GRAPH_REC_FOR_DATE
1407 GET_GRAPH_REC_FOR_DATE(
1408 p_graph_rec_date => l_loop_start_date,
1409 p_graph_rec_num => l_day_inc,
1410 p_visit_count => l_visit_count,
1411 p_not_for_sch => l_not_for_sch,
1412 p_sch_visits_tbl => l_sch_visits_tbl,
1413 p_x_sch_graph_rec => l_sch_graph_rec);
1414
1415 l_loop_start_date := l_loop_start_date+1;
1416 l_day_inc := l_day_inc+1;
1417 END LOOP;
1418 x_sch_visits_tbl := l_sch_visits_tbl;
1419 x_sch_graph_rec := l_sch_graph_rec;
1420
1421 l_msg_count := Fnd_Msg_Pub.count_msg;
1422 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
1423 IF (l_log_statement >= l_log_current_level) THEN
1424 fnd_log.string(l_log_statement,
1425 L_DEBUG_KEY,
1426 'Errors from GET_VISITS_FOR_DATE_FLT. Message count: ' ||
1427 l_msg_count || ', Message data: ' || l_msg_data);
1428 END IF;
1429 x_msg_count := l_msg_count;
1430 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1431 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1432 END IF;
1433
1434 x_return_status := Fnd_Api.g_ret_sts_success;
1435 IF (l_log_procedure >= l_log_current_level) THEN
1436 fnd_log.string(l_log_procedure,
1437 L_DEBUG_KEY || '.end',
1438 'Return Status = ' || x_return_status);
1439 END IF;
1440
1441 EXCEPTION
1442 WHEN Fnd_Api.G_EXC_ERROR THEN
1443 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1444 ROLLBACK TO GET_VISITS_FOR_DATE_FLT;
1445 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1446 p_data => x_msg_data,
1447 p_encoded => Fnd_Api.g_false);
1448 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1449 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1450 ROLLBACK TO GET_VISITS_FOR_DATE_FLT;
1451 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1452 p_data => x_msg_data,
1453 p_encoded => Fnd_Api.g_false);
1454 WHEN OTHERS THEN
1455 ROLLBACK TO GET_VISITS_FOR_DATE_FLT;
1456 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1457 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1458 THEN
1459 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1460 END IF;
1461 Fnd_Msg_Pub.count_and_get (
1462 p_encoded => Fnd_Api.g_false,
1463 p_count => x_msg_count,
1464 p_data => x_msg_data);
1465
1466 END GET_VISITS_FOR_DATE_FLT;
1467
1468 -- Procedure name : GET_GRAPH_REC_FOR_DATE
1469 -- Type : Private
1470 -- Parameters :
1471 -- GET_GRAPH_REC_FOR_DATE params
1472 -- p_graph_rec_date DATE
1473 -- p_graph_rec_num NUMBER
1474 -- p_visit_count NUMBER
1475 -- p_not_for_sch VARCHAR2
1476 -- p_filter_criteria VARCHAR2
1477 -- p_rec_type VARCHAR2
1478 -- p_sch_visits_tbl SCH_VISITS_TBL
1479 -- p_x_sch_graph_rec SCH_GRAPH_RESULTS_REC
1480
1481 PROCEDURE GET_GRAPH_REC_FOR_DATE(
1482 p_graph_rec_date IN DATE,
1483 p_graph_rec_num IN NUMBER,
1484 p_visit_count IN NUMBER,
1485 p_not_for_sch IN VARCHAR2 := Fnd_Api.g_false,
1486 p_filter_criteria IN VARCHAR2 := 'ALL',
1487 p_rec_type IN VARCHAR2 := G_SPACE_TYPE,
1488 p_sch_visits_tbl IN sch_visits_tbl,
1489 p_x_sch_graph_rec IN OUT NOCOPY SCH_GRAPH_RESULTS_REC
1490 ) IS
1491
1492 --local variable
1493 l_api_name CONSTANT VARCHAR2(30) := 'GET_GRAPH_REC_FOR_DATE';
1494 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
1495
1496 l_schedule_type VARCHAR2(30) := NULL;
1497 l_visit_id NUMBER := NULL;
1498 l_visit_start VARCHAR2(10);
1499 l_visit_end VARCHAR2(10);
1500 l_graph_rec_date_char VARCHAR2(10);
1501 l_tbl_inc NUMBER := 1;
1502 l_end_counter NUMBER;
1503
1504 l_S_V_start CONSTANT VARCHAR2(30) := 'SingleVisitStart';
1505 l_S_V_day CONSTANT VARCHAR2(30) := 'SingleVisitDay';
1506 l_S_V_end CONSTANT VARCHAR2(30) := 'SingleVisitEnd';
1507 l_M_V_no_overlap CONSTANT VARCHAR2(30) := 'MultiVisitNoOverlap';
1508 l_M_V_no_conflict CONSTANT VARCHAR2(30) := 'MultiVisitNoConflict';
1509 l_M_V_conflict CONSTANT VARCHAR2(30) := 'MultiVisitConflict';
1510 l_N_no_visit CONSTANT VARCHAR2(30) := 'NoVisit';
1511 l_N_not_available CONSTANT VARCHAR2(30) := 'NoSchedule';
1512
1513 l_filter_conflict CONSTANT VARCHAR2(15) := 'CONFLICT_DAYS';
1514 l_filter_open CONSTANT VARCHAR2(15) := 'OPEN_DAYS';
1515 l_filter_scheduled CONSTANT VARCHAR2(15) := 'SCHEDULED_DAYS';
1516 l_loop_inc NUMBER := 1;
1517
1518 l_conflict VARCHAR2(30) := l_M_V_conflict||p_graph_rec_num;
1519 l_no_visit VARCHAR2(30) := l_N_no_visit||p_graph_rec_num;
1520
1521 BEGIN
1522 IF(p_not_for_sch = Fnd_Api.g_true) THEN
1523 l_schedule_type := l_N_not_available||p_graph_rec_num;
1524
1525 ELSIF(p_visit_count < 1) THEN
1526 l_schedule_type := l_N_no_visit || p_graph_rec_num;
1527
1528 ELSIF (p_visit_count = 1) THEN
1529 l_visit_start := to_char(p_sch_visits_tbl(0).START_DATE, 'DD-MM-YYYY');
1530 l_visit_end := to_char(p_sch_visits_tbl(0).END_DATE, 'DD-MM-YYYY');
1531 l_graph_rec_date_char := to_char(p_graph_rec_date, 'DD-MM-YYYY');
1532
1533 IF(l_visit_start = l_graph_rec_date_char) THEN
1534 l_schedule_type := l_S_V_start || p_graph_rec_num;
1535
1536 ELSIF(l_visit_end = l_graph_rec_date_char) THEN
1537 l_schedule_type := l_S_V_end || p_graph_rec_num;
1538
1539 ELSE
1540 l_schedule_type := l_S_V_day || p_graph_rec_num;
1541 END IF;
1542
1543 l_visit_id := p_sch_visits_tbl(0).VISIT_ID;
1544
1545 ELSIF (p_visit_count > 1) THEN
1546
1547 l_end_counter := p_visit_count-1;
1548
1549 FOR l_tbl_inc IN 1..l_end_counter LOOP
1550 IF(p_sch_visits_tbl(l_tbl_inc-1).END_DATE
1551 >=
1552 p_sch_visits_tbl(l_tbl_inc).START_DATE) THEN
1553
1554 IF(p_rec_type = G_DEPT_TYPE) THEN
1555 l_schedule_type := l_M_V_no_conflict || p_graph_rec_num;
1556 ELSE
1557 l_schedule_type := l_M_V_conflict || p_graph_rec_num;
1558 END IF;
1559 EXIT;
1560 END IF;
1561
1562 END LOOP;
1563
1564 IF (l_schedule_type IS NULL) THEN
1565 l_schedule_type := l_M_V_no_overlap || p_graph_rec_num;
1566 END IF;
1567
1568 END IF;
1569
1570 CASE p_graph_rec_num
1571 WHEN 1 THEN
1572 p_x_sch_graph_rec.SCHEDULE_TYPE_1 := l_schedule_type;
1573 p_x_sch_graph_rec.VISIT_DATE_1 := p_graph_rec_date;
1574 p_x_sch_graph_rec.VISIT_ID_1 := l_visit_id;
1575 WHEN 2 THEN
1576 p_x_sch_graph_rec.SCHEDULE_TYPE_2 := l_schedule_type;
1577 p_x_sch_graph_rec.VISIT_DATE_2 := p_graph_rec_date;
1578 p_x_sch_graph_rec.VISIT_ID_2 := l_visit_id;
1579 WHEN 3 THEN
1580 p_x_sch_graph_rec.SCHEDULE_TYPE_3 := l_schedule_type;
1581 p_x_sch_graph_rec.VISIT_DATE_3 := p_graph_rec_date;
1582 p_x_sch_graph_rec.VISIT_ID_3 := l_visit_id;
1583 WHEN 4 THEN
1584 p_x_sch_graph_rec.SCHEDULE_TYPE_4 := l_schedule_type;
1585 p_x_sch_graph_rec.VISIT_DATE_4 := p_graph_rec_date;
1586 p_x_sch_graph_rec.VISIT_ID_4 := l_visit_id;
1587 WHEN 5 THEN
1588 p_x_sch_graph_rec.SCHEDULE_TYPE_5 := l_schedule_type;
1589 p_x_sch_graph_rec.VISIT_DATE_5 := p_graph_rec_date;
1590 p_x_sch_graph_rec.VISIT_ID_5 := l_visit_id;
1591 WHEN 6 THEN
1592 p_x_sch_graph_rec.SCHEDULE_TYPE_6 := l_schedule_type;
1593 p_x_sch_graph_rec.VISIT_DATE_6 := p_graph_rec_date;
1594 p_x_sch_graph_rec.VISIT_ID_6 := l_visit_id;
1595 WHEN 7 THEN
1596 p_x_sch_graph_rec.SCHEDULE_TYPE_7 := l_schedule_type;
1597 p_x_sch_graph_rec.VISIT_DATE_7 := p_graph_rec_date;
1598 p_x_sch_graph_rec.VISIT_ID_7 := l_visit_id;
1599 WHEN 8 THEN
1600 p_x_sch_graph_rec.SCHEDULE_TYPE_8 := l_schedule_type;
1601 p_x_sch_graph_rec.VISIT_DATE_8 := p_graph_rec_date;
1602 p_x_sch_graph_rec.VISIT_ID_8 := l_visit_id;
1603 WHEN 9 THEN
1604 p_x_sch_graph_rec.SCHEDULE_TYPE_9 := l_schedule_type;
1605 p_x_sch_graph_rec.VISIT_DATE_9 := p_graph_rec_date;
1606 p_x_sch_graph_rec.VISIT_ID_9 := l_visit_id;
1607 WHEN 10 THEN
1608 p_x_sch_graph_rec.SCHEDULE_TYPE_10 := l_schedule_type;
1609 p_x_sch_graph_rec.VISIT_DATE_10 := p_graph_rec_date;
1610 p_x_sch_graph_rec.VISIT_ID_10 := l_visit_id;
1611 WHEN 11 THEN
1612 p_x_sch_graph_rec.SCHEDULE_TYPE_11 := l_schedule_type;
1613 p_x_sch_graph_rec.VISIT_DATE_11 := p_graph_rec_date;
1614 p_x_sch_graph_rec.VISIT_ID_11 := l_visit_id;
1615 WHEN 12 THEN
1616 p_x_sch_graph_rec.SCHEDULE_TYPE_12 := l_schedule_type;
1617 p_x_sch_graph_rec.VISIT_DATE_12 := p_graph_rec_date;
1618 p_x_sch_graph_rec.VISIT_ID_12 := l_visit_id;
1619 WHEN 13 THEN
1620 p_x_sch_graph_rec.SCHEDULE_TYPE_13 := l_schedule_type;
1621 p_x_sch_graph_rec.VISIT_DATE_13 := p_graph_rec_date;
1622 p_x_sch_graph_rec.VISIT_ID_13 := l_visit_id;
1623 WHEN 14 THEN
1624 p_x_sch_graph_rec.SCHEDULE_TYPE_14 := l_schedule_type;
1625 p_x_sch_graph_rec.VISIT_DATE_14 := p_graph_rec_date;
1626 p_x_sch_graph_rec.VISIT_ID_14 := l_visit_id;
1627 WHEN 15 THEN
1628 p_x_sch_graph_rec.SCHEDULE_TYPE_15 := l_schedule_type;
1629 p_x_sch_graph_rec.VISIT_DATE_15 := p_graph_rec_date;
1630 p_x_sch_graph_rec.VISIT_ID_15 := l_visit_id;
1631 WHEN 16 THEN
1632 p_x_sch_graph_rec.SCHEDULE_TYPE_16 := l_schedule_type;
1633 p_x_sch_graph_rec.VISIT_DATE_16 := p_graph_rec_date;
1634 p_x_sch_graph_rec.VISIT_ID_16 := l_visit_id;
1635 WHEN 17 THEN
1636 p_x_sch_graph_rec.SCHEDULE_TYPE_17 := l_schedule_type;
1637 p_x_sch_graph_rec.VISIT_DATE_17 := p_graph_rec_date;
1638 p_x_sch_graph_rec.VISIT_ID_17 := l_visit_id;
1639 WHEN 18 THEN
1640 p_x_sch_graph_rec.SCHEDULE_TYPE_18 := l_schedule_type;
1641 p_x_sch_graph_rec.VISIT_DATE_18 := p_graph_rec_date;
1642 p_x_sch_graph_rec.VISIT_ID_18 := l_visit_id;
1643 WHEN 19 THEN
1644 p_x_sch_graph_rec.SCHEDULE_TYPE_19 := l_schedule_type;
1645 p_x_sch_graph_rec.VISIT_DATE_19 := p_graph_rec_date;
1646 p_x_sch_graph_rec.VISIT_ID_19 := l_visit_id;
1647 WHEN 20 THEN
1648 p_x_sch_graph_rec.SCHEDULE_TYPE_20 := l_schedule_type;
1649 p_x_sch_graph_rec.VISIT_DATE_20 := p_graph_rec_date;
1650 p_x_sch_graph_rec.VISIT_ID_20 := l_visit_id;
1651 WHEN 21 THEN
1652 p_x_sch_graph_rec.SCHEDULE_TYPE_21 := l_schedule_type;
1653 p_x_sch_graph_rec.VISIT_DATE_21 := p_graph_rec_date;
1654 p_x_sch_graph_rec.VISIT_ID_21 := l_visit_id;
1655 ELSE
1656 FND_MSG_PUB.ADD;
1657 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1658 END CASE;
1659
1660 IF(p_filter_criteria IN (l_filter_open, l_filter_conflict)) THEN
1661 IF NOT p_x_sch_graph_rec.FILTER_REC THEN
1662 IF(p_filter_criteria = l_filter_open) AND
1663 (l_schedule_type = l_no_visit)THEN
1664 p_x_sch_graph_rec.FILTER_REC := TRUE;
1665
1666 ELSIF((p_filter_criteria = l_filter_conflict) AND
1667 (l_schedule_type = l_conflict))THEN
1668 p_x_sch_graph_rec.FILTER_REC := TRUE;
1669 END IF;
1670 END IF;
1671 ELSIF(p_filter_criteria = l_filter_scheduled) THEN
1672 IF (p_graph_rec_num <> 1) AND
1673 (p_x_sch_graph_rec.FILTER_REC) THEN
1674 IF l_schedule_type NOT IN (l_conflict,l_no_visit)THEN
1675 p_x_sch_graph_rec.FILTER_REC := FALSE;
1676 END IF;
1677 ELSIF(p_graph_rec_num = 1) THEN
1678 IF l_schedule_type NOT IN (l_conflict,l_no_visit)THEN
1679 p_x_sch_graph_rec.FILTER_REC := FALSE;
1680 ELSE
1681 p_x_sch_graph_rec.FILTER_REC := TRUE;
1682 END IF;
1683 END IF;
1684 ELSE
1685 p_x_sch_graph_rec.FILTER_REC := TRUE;
1686 END IF;
1687 END GET_GRAPH_REC_FOR_DATE;
1688
1689 -- Function name : GET_FLEET_NAME
1690 -- Type : Public
1691 -- Parameters :
1692 -- GET_FLEET_NAME params
1693 -- p_item_instance_id NUMBER Required
1694 -- p_visit_start_date DATE Required
1695 -- p_visit_end_date DATE Required
1696
1697 FUNCTION GET_FLEET_NAME(p_item_instance_id IN NUMBER,
1698 p_visit_start_date IN DATE,
1699 p_visit_end_date IN DATE)
1700 RETURN VARCHAR2
1701 IS
1702
1703 CURSOR c_fleet_name_for_unit (c_instance_id IN NUMBER, c_start_date IN DATE, c_end_date IN DATE)
1704 IS
1705 SELECT DISTINCT FHB.NAME FLEET_NAME,
1706 FUA.association_start
1707 FROM AHL_UNIT_CONFIG_HEADERS UCH,
1708 AHL_FLEET_HEADERS_B FHB,
1709 AHL_FLEET_UNIT_ASSOCS FUA,
1710 AHL_MC_HEADERS_B MC
1711 WHERE FUA.UNIT_CONFIG_HEADER_ID = UCH.UNIT_CONFIG_HEADER_ID
1712 AND FUA.FLEET_HEADER_ID = FHB.FLEET_HEADER_ID
1713 AND UCH.MASTER_CONFIG_ID = MC.MC_HEADER_ID
1714 AND FUA.SIMULATION_PLAN_ID =
1715 (SELECT ASP.SIMULATION_PLAN_ID
1716 FROM AHL_SIMULATION_PLANS_B ASP
1717 WHERE ASP.PRIMARY_PLAN_FLAG = 'Y'
1718 AND ASP.status_code = 'ACTIVE'
1719 AND ASP.simulation_type = 'UMP' -- Sthilak added this extra filter condition AMP-AutoVisit Changes
1720 )
1721 AND UCH.UNIT_CONFIG_STATUS_CODE <> 'DRAFT'
1722 AND FHB.STATUS_CODE = 'COMPLETE'
1723 AND TRUNC(NVL(UCH.ACTIVE_END_DATE,c_end_date+1)) > TRUNC(c_end_date)
1724 AND UCH.csi_item_instance_id = c_instance_id
1725 AND (TRUNC(c_start_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
1726 OR TRUNC(c_end_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
1727 OR TRUNC(FUA.association_start) BETWEEN TRUNC(c_start_date) AND TRUNC(c_end_date))
1728 ORDER BY association_start;
1729
1730 c_fleet_name_for_unit_rec c_fleet_name_for_unit%ROWTYPE;
1731
1732 BEGIN
1733
1734 OPEN c_fleet_name_for_unit(p_item_instance_id,p_visit_start_date,p_visit_end_date);
1735 FETCH c_fleet_name_for_unit INTO c_fleet_name_for_unit_rec;
1736 IF(c_fleet_name_for_unit%NOTFOUND)THEN
1737 RETURN NULL;
1738 ELSE
1739 RETURN c_fleet_name_for_unit_rec.FLEET_NAME;
1740 END IF;
1741 CLOSE c_fleet_name_for_unit;
1742
1743 RETURN NULL;
1744 END GET_FLEET_NAME;
1745
1746 -- Function name : GET_FLEET_HEADER_ID
1747 -- Type : Public
1748 -- Parameters :
1749 -- GET_FLEET_NAME params
1750 -- p_item_instance_id NUMBER Required
1751 -- p_visit_start_date DATE Required
1752 -- p_visit_end_date DATE Required
1753
1754 FUNCTION GET_FLEET_HEADER_ID(p_item_instance_id IN NUMBER,
1755 p_visit_start_date IN DATE,
1756 p_visit_end_date IN DATE)
1757 RETURN NUMBER
1758 IS
1759
1760 CURSOR c_fleet_id_for_unit (c_instance_id IN NUMBER, c_start_date IN DATE, c_end_date IN DATE)
1761 IS
1762 SELECT DISTINCT FHB.NAME FLEET_NAME,
1763 FUA.association_start,
1764 FHB.FLEET_HEADER_ID
1765 FROM AHL_UNIT_CONFIG_HEADERS UCH,
1766 AHL_FLEET_HEADERS_B FHB,
1767 AHL_FLEET_UNIT_ASSOCS FUA,
1768 AHL_MC_HEADERS_B MC
1769 WHERE FUA.UNIT_CONFIG_HEADER_ID = UCH.UNIT_CONFIG_HEADER_ID
1770 AND FUA.FLEET_HEADER_ID = FHB.FLEET_HEADER_ID
1771 AND UCH.MASTER_CONFIG_ID = MC.MC_HEADER_ID
1772 AND FUA.SIMULATION_PLAN_ID =
1773 (SELECT ASP.SIMULATION_PLAN_ID
1774 FROM AHL_SIMULATION_PLANS_B ASP
1775 WHERE ASP.PRIMARY_PLAN_FLAG = 'Y'
1776 AND ASP.status_code = 'ACTIVE'
1777 AND ASP.simulation_type = 'UMP' -- Sthilak added this extra filter condition AMP-AutoVisit Changes
1778 )
1779 AND UCH.UNIT_CONFIG_STATUS_CODE <> 'DRAFT'
1780 AND FHB.STATUS_CODE = 'COMPLETE'
1781 AND TRUNC(NVL(UCH.ACTIVE_END_DATE,c_end_date+1)) > TRUNC(c_end_date)
1782 AND UCH.csi_item_instance_id = c_instance_id
1783 AND (TRUNC(c_start_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
1784 OR TRUNC(c_end_date) BETWEEN TRUNC(FUA.association_start) AND TRUNC(NVL(FUA.association_end, c_end_date))
1785 OR TRUNC(FUA.association_start) BETWEEN TRUNC(c_start_date) AND TRUNC(c_end_date))
1786 ORDER BY association_start;
1787
1788 c_fleet_id_for_unit_rec c_fleet_id_for_unit%ROWTYPE;
1789
1790 BEGIN
1791
1792 OPEN c_fleet_id_for_unit(p_item_instance_id,p_visit_start_date,p_visit_end_date);
1793 FETCH c_fleet_id_for_unit INTO c_fleet_id_for_unit_rec;
1794 IF(c_fleet_id_for_unit%NOTFOUND)THEN
1795 RETURN NULL;
1796 ELSE
1797 RETURN c_fleet_id_for_unit_rec.FLEET_HEADER_ID;
1798 END IF;
1799 CLOSE c_fleet_id_for_unit;
1800 RETURN NULL;
1801 END GET_FLEET_HEADER_ID;
1802
1803 END AHL_AMP_WORKBENCH_PVT;