1 PACKAGE body AHL_COMPLEX_MX_PVT AS
2 /* $Header: AHLVCAMB.pls 120.0.12020000.3 2013/02/11 22:47:00 sikumar noship $ */
3
4 -----------------------------------------------------------------
5 -- Define Global CONSTANTS --
6 -----------------------------------------------------------------
7 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_COMPLEX_MX_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 -- Function name : CALC_BUILD_GOAL
20 -- Type : Public
21 -- Parameters :
22 -- CALC_BUILD_GOAL params
23 -- p_instance_id NUMBER Required
24 -- p_visit_id DATE Required
25 -- p_ue_id DATE Required
26
27 FUNCTION CALC_BUILD_GOAL(
28 p_instance_id IN NUMBER,
29 p_visit_id IN NUMBER,
30 p_ue_id IN NUMBER := NULL
31 ) RETURN NUMBER
32 IS
33 -- LOCAL VARIABLE
34 -- Standard
35 l_api_name CONSTANT VARCHAR2(30) := 'calc_build_goal';
36 l_debug_key CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
37 l_msg_count NUMBER := 0;
38 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
39 l_msg_data VARCHAR2(2000);
40
41 -- Local
42 l_due_date DATE;
43 l_counter_uom VARCHAR2(10);
44 l_ue_id NUMBER;
45 is_uom_matching BOOLEAN := FALSE;
46 l_uom_matching_set_up NUMBER;
47 l_visit_start_date DATE;
48 l_inst_not_in_inv_wip BOOLEAN := FALSE;
49 --l_net_reading NUMBER;
50 l_counter_reading NUMBER := 0;
51 l_uom_remain NUMBER;
52 l_due_counter_value NUMBER;
53 l_dummy VARCHAR2(10);
54 --l_counter_name CS_COUNTERS_V.NAME%TYPE;
55 l_counter_id NUMBER;
56 l_nc_counter_tbl AHL_UMP_ProcessUnit_PVT.counter_values_tbl_type;
57 l_fc_counter_tbl AHL_UMP_ProcessUnit_PVT.counter_values_tbl_type;
58 l_temp_bg_percent NUMBER := 0;
59 x_bg_percentage INTEGER := 0;
60 --l_setup_itr NUMBER := 0;
61
62 -- CURSOR
63
64 -- Cursor to get the BG set up data
65 CURSOR c_get_BG_set_up(c_visit_id IN NUMBER)
66 IS
67 SELECT uom_code,
68 counter_value
69 FROM AHL_BUILD_GOALS
70 WHERE visit_id = c_visit_id;
71
72 -- Cursor to get the visit start date
73 CURSOR c_get_visit_start_date(c_visit_id IN NUMBER)
74 IS
75 SELECT start_date_time FROM AHL_VISITS_B WHERE visit_id = c_visit_id;
76
77 -- Cursor to check whether the item in INV or WIP
78 CURSOR c_inst_not_in_inv_wip(c_instance_id IN NUMBER)
79 IS
80 SELECT 'Y'
81 FROM csi_item_instances
82 WHERE instance_id = c_instance_id
83 AND INV_LOCATOR_ID IS NULL
84 AND WIP_JOB_ID IS NULL;
85
86 -- Cursor to get the BG MR UE line for the given instance, which is due immediately.
87 CURSOR c_get_mindue_BG_UE (c_instance_id IN NUMBER)
88 IS
89 SELECT unit_effectivity_id,
90 due_date,
91 NVL(AMI_II_COUNTER_ID, UMPCounter_ID) counter_id,
92 (SELECT uom_code
93 FROM csi_counters_b
94 WHERE counter_id = NVL(AMI_II_COUNTER_ID, UMPCounter_ID)
95 ) uom_code,
96 NVL(due_counter_value,0) due_counter_value
97 FROM
98 (SELECT UMP.unit_effectivity_id,
99 UMP.due_date,
100 (SELECT ii.counter_id
101 FROM CSI_COUNTER_ASSOCIATIONS CCA,
102 CSI_COUNTERS_VL ii,
103 CSI_COUNTER_TEMPLATE_VL tmp
104 WHERE CCA.SOURCE_OBJECT_CODE = 'CP'
105 AND CCA.SOURCE_OBJECT_ID = UMP.CSI_ITEM_INSTANCE_ID
106 AND ii.counter_id = cca.counter_id
107 AND tmp.counter_id = ami.counter_id
108 AND tmp.name = ii.counter_template_name
109 ) AMI_II_COUNTER_ID,
110 UMP.counter_id UMPCounter_ID,
111 UMP.due_counter_value
112 FROM AHL_UNIT_EFFECTIVITIES_B UMP,
113 AHL_MR_HEADERS_B MRH,
114 AHL_MR_INTERVALS AMI
115 WHERE
116 --Table joins
117 UMP.MR_HEADER_ID = MRH.MR_HEADER_ID
118 AND ami.mr_interval_id(+) = ump.mr_interval_id
119 -- Bindings
120 AND UMP.CSI_ITEM_INSTANCE_ID = c_instance_id
121 --UMP Related conditions
122 AND UMP.APPLICATION_USG_CODE= 'AHL'
123 AND (UMP.STATUS_CODE IS NULL
124 OR UMP.STATUS_CODE ='INIT-DUE')
125 --MR Related conditions
126 AND MRH.SERVICE_TYPE_CODE = 'ASR'
127 AND MRH.IMPLEMENT_STATUS_CODE IN ('MANDATORY','SOFT_LIMIT','OPTIONAL_IMPLEMENT')
128 AND UMP.due_date IS NOT NULL
129 ORDER BY due_date
130 )
131 WHERE rownum <2;
132
133 -- Cursor to check whether the given UE di is from BG MR and to get
134 -- the UE line details for the .
135 CURSOR c_get_BG_for_UE (c_ue_id IN NUMBER)
136 IS
137 SELECT unit_effectivity_id,
138 due_date,
139 NVL(AMI_II_COUNTER_ID, UMPCounter_ID) counter_id,
140 (SELECT uom_code
141 FROM csi_counters_b
142 WHERE counter_id = NVL(AMI_II_COUNTER_ID, UMPCounter_ID)
143 ) uom_code,
144 NVL(due_counter_value,0) due_counter_value
145 FROM
146 (SELECT UMP.unit_effectivity_id,
147 UMP.due_date,
148 (SELECT ii.counter_id
149 FROM CSI_COUNTER_ASSOCIATIONS CCA,
150 CSI_COUNTERS_VL ii,
151 CSI_COUNTER_TEMPLATE_VL tmp
152 WHERE CCA.SOURCE_OBJECT_CODE = 'CP'
153 AND CCA.SOURCE_OBJECT_ID = UMP.CSI_ITEM_INSTANCE_ID
154 AND ii.counter_id = cca.counter_id
155 AND tmp.counter_id = ami.counter_id
156 AND tmp.name = ii.counter_template_name
157 ) AMI_II_COUNTER_ID,
158 UMP.counter_id UMPCounter_ID,
159 UMP.due_counter_value
160 FROM AHL_UNIT_EFFECTIVITIES_B UMP,
161 AHL_MR_HEADERS_B MRH,
162 AHL_MR_INTERVALS AMI
163 WHERE
164 --Table joins
165 UMP.MR_HEADER_ID = MRH.MR_HEADER_ID
166 AND ami.mr_interval_id(+) = ump.mr_interval_id
167 -- Bindings
168 AND UMP.unit_effectivity_id = c_ue_id
169 --UMP Related conditions
170 AND UMP.APPLICATION_USG_CODE= 'AHL'
171 AND (UMP.STATUS_CODE IS NULL
172 OR UMP.STATUS_CODE ='INIT-DUE')
173 --MR Related conditions
174 AND MRH.SERVICE_TYPE_CODE = 'ASR'
175 AND MRH.IMPLEMENT_STATUS_CODE IN ('MANDATORY','SOFT_LIMIT','OPTIONAL_IMPLEMENT')
176 AND UMP.due_date IS NOT NULL
177 ORDER BY due_date
178 );
179
180 -- Cursor to get the current counter reading for the given counter of an instance.
181 CURSOR c_get_net_counter_reading (c_counter_id IN NUMBER)
182 IS
183 SELECT
184 nvl((SELECT ccr.net_reading
185 FROM csi_counter_readings ccr
186 WHERE ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
187 AND NVL(ccr.disabled_flag,'N') = 'N'
188 ),0) net_reading
189 FROM CSI_COUNTERS_B CC
190 WHERE CC.COUNTER_ID = c_counter_id;
191
192 --Record types
193 c_bg_ue_rec c_get_mindue_BG_UE%ROWTYPE;
194 c_bg_for_ue_rec c_get_BG_for_UE%ROWTYPE;
195
196 --Table types
197 TYPE c_bg_set_up_tbl
198 IS
199 TABLE OF c_get_BG_set_up%ROWTYPE INDEX BY BINARY_INTEGER;
200
201 l_set_up_tbl c_bg_set_up_tbl;
202
203 BEGIN
204
205 IF (l_log_procedure >= l_log_current_level) THEN
206 fnd_log.string(l_log_procedure, l_debug_key ||'.begin',
207 ' At the start of PL SQL function. '||
208 ' Item Instance Id = ' || p_instance_id || ' Visit Id = ' || p_visit_id||
209 ' Unit Effectivity Id = ' || p_ue_id);
210 END IF;
211
212 -- Get the BG Set up data
213 OPEN c_get_BG_set_up(p_visit_id);
214 FETCH c_get_BG_set_up BULK COLLECT INTO l_set_up_tbl;
215 CLOSE c_get_BG_set_up;
216
217 IF (l_set_up_tbl.COUNT < 1) THEN -- BG Set up not available
218 RETURN NULL;
219 END IF;
220
221 -- Get the UE line data with the least due date for the given instance
222 IF (p_ue_id IS NULL)THEN -- When the Function is called without an UE in context
223 -- Open the cursor and get the least due date for the given instance
224 OPEN c_get_mindue_BG_UE(p_instance_id);
225 FETCH c_get_mindue_BG_UE INTO c_bg_ue_rec;
226 IF c_get_mindue_BG_UE%NOTFOUND THEN -- There is no BG MR for this instance
227 CLOSE c_get_mindue_BG_UE;
228 RETURN NULL;
229 -- Earliest BG UE line found
230 ELSE
231 l_due_date := c_bg_ue_rec.due_date;
232 l_counter_id := c_bg_ue_rec.counter_id;
233 --l_counter_name := c_bg_ue_rec.COUNTER_NAME;
234 l_counter_uom := c_bg_ue_rec.UOM_CODE;
235 l_ue_id := c_bg_ue_rec.unit_effectivity_id;
236 l_due_counter_value := c_bg_ue_rec.due_counter_value;
237 END IF;
238 CLOSE c_get_mindue_BG_UE;
239 -- When the Function is called with an UE in context
240 ELSE
241 -- Open the cursor and get the UE line
242 OPEN c_get_BG_for_UE(p_ue_id);
243 FETCH c_get_BG_for_UE INTO c_bg_for_ue_rec;
244 IF c_get_BG_for_UE%NOTFOUND THEN -- There UE line is not from BG MR or its invalid
245 CLOSE c_get_BG_for_UE;
246 RETURN NULL;
247 -- The input UE line is a BG UE line
248 ELSE
249 l_due_date := c_bg_for_ue_rec.due_date;
250 l_counter_id := c_bg_for_ue_rec.counter_id;
251 --l_counter_name := c_bg_for_ue_rec.COUNTER_NAME;
252 l_counter_uom := c_bg_for_ue_rec.UOM_CODE;
253 l_ue_id := p_ue_id;
254 l_due_counter_value := c_bg_for_ue_rec.due_counter_value;
255 END IF;
256 CLOSE c_get_BG_for_UE;
257 END IF;
258
259 -- Loop through the set up data to find whether the UOM Matches
260 FOR i IN 1..l_set_up_tbl.COUNT
261 LOOP
262 IF (l_set_up_tbl(i).uom_code = l_counter_uom) THEN
263 is_uom_matching := TRUE;
264 l_uom_matching_set_up := l_set_up_tbl(i).counter_value;
265 EXIT;
266 END IF;
267 END LOOP;
268
269 IF (l_log_statement >= l_log_current_level) THEN
270 fnd_log.string(l_log_statement, l_debug_key || '.middle',
271 ' The selected UE values are > ' || ' Due Date = ' || TO_CHAR(l_due_date) ||
272 ' Counter UOM = ' || l_counter_uom || ' UE Line id = ' || l_ue_id||
273 ' Counter Id'||l_counter_id );
274 END IF;
275
276 -- Get the Visit start date
277 OPEN c_get_visit_start_date(p_visit_id);
278 FETCH c_get_visit_start_date INTO l_visit_start_date;
279 CLOSE c_get_visit_start_date;
280
281 --Check whether the instance is in INV/WIP
282 OPEN c_inst_not_in_inv_wip(p_instance_id);
283 FETCH c_inst_not_in_inv_wip INTO l_dummy;
284 IF (c_inst_not_in_inv_wip%FOUND) THEN
285 l_inst_not_in_inv_wip := TRUE;
286 END IF;
287 CLOSE c_inst_not_in_inv_wip;
288
289 -- Any instance of an unit cant be in INV/WIP when Visit in future, means the Unit is functional
290 IF (l_inst_not_in_inv_wip AND l_visit_start_date > SYSDATE) THEN -- Future Visit and instance not in INV/WIP
291 --UOM Matching for future visit
292 IF (is_uom_matching) THEN
293 -- Matching future date due counter value reading
294 AHL_UMP_ProcessUnit_PVT.Get_Forecasted_Counter_Values(
295 x_return_status => l_return_status,
296 x_msg_data => l_msg_data,
297 x_msg_count => l_msg_count,
298 p_csi_item_instance_id => p_instance_id,
299 p_forecasted_date => l_visit_start_date,
300 x_counter_values_tbl => l_fc_counter_tbl);
301
302 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
303 IF (l_log_statement >= l_log_current_level) THEN
304 fnd_log.string(l_log_statement,
305 L_DEBUG_KEY || '.Middle',
306 'Errors from Get_Forecasted_Counter_Values. Message count: ' ||
307 l_msg_count || ', Message data: ' || l_msg_data);
308 END IF;
309 RETURN NULL;
310 END IF;
311
312 FOR i IN 1..l_fc_counter_tbl.COUNT
313 LOOP
314 IF (l_fc_counter_tbl(i).COUNTER_ID = l_counter_id) THEN -- Matching the counter id here as the UOM is already matching
315 l_counter_reading := l_fc_counter_tbl(i).COUNTER_VALUE;
316 EXIT;
317 END IF;
318 END LOOP;
319
320 -- Calc UOM Remain
321 l_uom_remain := l_due_counter_value - l_counter_reading;
322 -- Calc BG Percentage
323 x_bg_percentage := (l_uom_remain/l_uom_matching_set_up)*100;
324 -- Return BG Percentage once all calculations done
325
326 -- Returning 0 when BG% is less than 1
327 -- And retruning 100 when BG% is greater than 100
328 IF(x_bg_percentage < 1) THEN
329 x_bg_percentage := 0;
330 ELSIF(x_bg_percentage > 99) THEN
331 x_bg_percentage := 100;
332 END IF;
333
334 IF (l_log_statement >= l_log_current_level) THEN
335 fnd_log.string(l_log_statement, l_debug_key || '.End',
336 'x_bg_percentage' ||x_bg_percentage);
337 END IF;
338 RETURN x_bg_percentage;
339 --UOM not Matching for future visit
340 ELSE
341 -- l_net_reading_date := l_visit_start_date;
342 -- l_counter_reading_date := l_due_date;
343
344 -- Get table of applicable counter records using forecast API for net reading
345 AHL_UMP_ProcessUnit_PVT.Get_Forecasted_Counter_Values(
346 x_return_status => l_return_status,
347 x_msg_data => l_msg_data,
348 x_msg_count => l_msg_count,
349 p_csi_item_instance_id => p_instance_id,
350 p_forecasted_date => l_visit_start_date,
351 x_counter_values_tbl => l_nc_counter_tbl);
352
353 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
354 IF (l_log_statement >= l_log_current_level) THEN
355 fnd_log.string(l_log_statement,
356 L_DEBUG_KEY || '.Middle',
357 'Errors from Get_Forecasted_Counter_Values. Message count: ' ||
358 l_msg_count || ', Message data: ' || l_msg_data);
359 END IF;
360 RETURN NULL;
361 END IF;
362
363 -- Get table of applicable counter records using forecast API for due counter value
364 AHL_UMP_ProcessUnit_PVT.Get_Forecasted_Counter_Values(
365 x_return_status => l_return_status,
366 x_msg_data => l_msg_data,
367 x_msg_count => l_msg_count,
368 p_csi_item_instance_id => p_instance_id,
369 p_forecasted_date => l_due_date,
370 x_counter_values_tbl => l_fc_counter_tbl);
371
372 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
373 IF (l_log_statement >= l_log_current_level) THEN
374 fnd_log.string(l_log_statement,
375 L_DEBUG_KEY || '.Middle',
376 'Errors from Get_Forecasted_Counter_Values. Message count: ' ||
377 l_msg_count || ', Message data: ' || l_msg_data);
378 END IF;
379 RETURN NULL;
380 END IF;
381
382 -- Looping through both the result table to match the counter id and get the corresponding counter value
383 -- and calculate the least BG percentage, if the corresponding set up UOM present
384 FOR i IN 1..l_fc_counter_tbl.COUNT
385 LOOP
386 <<inner_loop>> FOR j IN 1..l_nc_counter_tbl.COUNT
387 LOOP
388 -- Find same counters and get the counter values
389 IF (l_fc_counter_tbl(i).COUNTER_ID = l_nc_counter_tbl(j).COUNTER_ID) THEN
390 l_uom_remain := l_fc_counter_tbl(i).COUNTER_VALUE - l_nc_counter_tbl(j).COUNTER_VALUE;
391 l_counter_uom := l_fc_counter_tbl(i).UOM_CODE;
392
393 -- Check whether a BG set up is found for the selected counter
394 FOR k IN 1..l_set_up_tbl.COUNT
395 LOOP
396 IF (l_set_up_tbl(k).uom_code = l_counter_uom) THEN
397 l_uom_matching_set_up := l_set_up_tbl(k).counter_value;
398 is_uom_matching := TRUE;
399 EXIT;
400 END IF;
401 END LOOP;
402 -- IF BG Set up not present exit inner loop
403 IF NOT is_uom_matching THEN
404 EXIT inner_loop;
405 END IF;
406
407 -- Calc BG Percentage for the urrent UOM
408 l_temp_bg_percent := (l_uom_remain/l_uom_matching_set_up)*100;
409
410 -- Store the minimum of BG percentage
411 IF (i > 1 AND l_temp_bg_percent < x_bg_percentage) THEN
412 x_bg_percentage := l_temp_bg_percent;
413 ELSE
414 x_bg_percentage := l_temp_bg_percent;
415 END IF;
416
417 -- Once matching counters are found and BG calculated exit inner loop
418 EXIT inner_loop;
419 END IF;
420 END LOOP;
421 END LOOP;
422
423 -- Return BG Percentage once all calculations done
424 -- Returning 0 when BG% is less than 1
425 -- And retruning 100 when BG% is greater than 100
426 IF(x_bg_percentage < 1) THEN
427 x_bg_percentage := 0;
428 ELSIF(x_bg_percentage > 99) THEN
429 x_bg_percentage := 100;
430 END IF;
431
432 IF (l_log_statement >= l_log_current_level) THEN
433 fnd_log.string(l_log_statement, l_debug_key || '.End',
434 'x_bg_percentage' ||x_bg_percentage);
435 END IF;
436 RETURN x_bg_percentage;
437 END IF;
438 -- ongoing visit
439 ELSE
440 --UOM Matching for ongoing visit
441 IF (is_uom_matching) THEN
442 --l_net_reading_date := SYSDATE;
443 --l_counter_reading_date := l_due_date;
444 -- The following API returns the UOM remain on sysdate
445 l_uom_remain := AHL_UMP_SMRINSTANCE_PVT.GET_UOM_REMAIN(l_ue_id);
446 x_bg_percentage := (l_uom_remain/l_uom_matching_set_up)*100;
447
448 -- Returning 0 when BG% is less than 1
449 -- And retruning 100 when BG% is greater than 100
450 IF(x_bg_percentage < 1) THEN
451 x_bg_percentage := 0;
452 ELSIF(x_bg_percentage > 99) THEN
453 x_bg_percentage := 100;
454 END IF;
455
456 IF (l_log_statement >= l_log_current_level) THEN
457 fnd_log.string(l_log_statement, l_debug_key || '.End',
458 'x_bg_percentage : ' ||x_bg_percentage);
459 END IF;
460 RETURN x_bg_percentage;
461 --UOM not Matching for ongoing visit
462 ELSE
463 -- Get table of applicable counter records using forecast API for net reading
464 AHL_UMP_ProcessUnit_PVT.Get_Forecasted_Counter_Values(
465 x_return_status => l_return_status,
466 x_msg_data => l_msg_data,
467 x_msg_count => l_msg_count,
468 p_csi_item_instance_id => p_instance_id,
469 p_forecasted_date => SYSDATE,
470 x_counter_values_tbl => l_nc_counter_tbl);
471
472 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
473 IF (l_log_statement >= l_log_current_level) THEN
474 fnd_log.string(l_log_statement,
475 L_DEBUG_KEY || '.Middle',
476 'Errors from Get_Forecasted_Counter_Values. Message count: ' ||
477 l_msg_count || ', Message data: ' || l_msg_data);
478 END IF;
479 RETURN NULL;
480 END IF;
481
482 -- Get table of applicable counter records using forecast API for due counter value
483 AHL_UMP_ProcessUnit_PVT.Get_Forecasted_Counter_Values(
484 x_return_status => l_return_status,
485 x_msg_data => l_msg_data,
486 x_msg_count => l_msg_count,
487 p_csi_item_instance_id => p_instance_id,
488 p_forecasted_date => l_due_date,
489 x_counter_values_tbl => l_fc_counter_tbl);
490
491 IF l_msg_count > 0 OR l_return_status <> Fnd_Api.g_ret_sts_success THEN
492 IF (l_log_statement >= l_log_current_level) THEN
493 fnd_log.string(l_log_statement,
494 L_DEBUG_KEY || '.Middle',
495 'Errors from Get_Forecasted_Counter_Values. Message count: ' ||
496 l_msg_count || ', Message data: ' || l_msg_data);
497 END IF;
498 RETURN NULL;
499 END IF;
500
501 -- Looping through both the result table to match the counter id and get the corresponding counter value
502 -- and calculate the least BG percentage, if the corresponding set up UOM present
503 FOR i IN 1..l_fc_counter_tbl.COUNT
504 LOOP
505 <<inner_loop>> FOR j IN 1..l_nc_counter_tbl.COUNT
506 LOOP
507 -- Find same counters and get the counter values
508 IF (l_fc_counter_tbl(i).COUNTER_ID = l_nc_counter_tbl(j).COUNTER_ID) THEN
509 l_uom_remain := l_fc_counter_tbl(i).COUNTER_VALUE - l_nc_counter_tbl(j).COUNTER_VALUE;
510 l_counter_uom := l_fc_counter_tbl(i).UOM_CODE;
511
512 -- Check whether a BG set up is found for the selected counter
513 FOR k IN 1..l_set_up_tbl.COUNT
514 LOOP
515 IF (l_set_up_tbl(k).uom_code = l_counter_uom) THEN
516 l_uom_matching_set_up := l_set_up_tbl(k).counter_value;
517 is_uom_matching := TRUE;
518 EXIT;
519 END IF;
520 END LOOP;
521 -- IF BG Set up not present exit inner loop
522 IF NOT is_uom_matching THEN
523 EXIT inner_loop;
524 END IF;
525
526 -- Calc BG Percentage for the urrent UOM
527 l_temp_bg_percent := (l_uom_remain/l_uom_matching_set_up)*100;
528
529 -- Store the minimum of BG percentage
530 IF (i > 1 AND l_temp_bg_percent < x_bg_percentage) THEN
531 x_bg_percentage := l_temp_bg_percent;
532 ELSE
533 x_bg_percentage := l_temp_bg_percent;
534 END IF;
535
536 -- Once matching counters are found and BG calculated exit inner loop
537 EXIT inner_loop;
538 END IF;
539 END LOOP;
540 END LOOP;
541
542 -- Return BG Percentage once all calculations done
543 -- Returning 0 when BG% is less than 1
544 -- And retruning 100 when BG% is greater than 100
545 IF(x_bg_percentage < 1) THEN
546 x_bg_percentage := 0;
547 ELSIF(x_bg_percentage > 99) THEN
548 x_bg_percentage := 100;
549 END IF;
550
551 IF (l_log_statement >= l_log_current_level) THEN
552 fnd_log.string(l_log_statement, l_debug_key || '.End',
553 'x_bg_percentage' ||x_bg_percentage);
554 END IF;
555 RETURN x_bg_percentage;
556 END IF;
557 END IF;
558 RETURN NULL;
559 END CALC_BUILD_GOAL;
560
561 -- Changes by STHILAK starts
562 /*
563 Function Name : get_removal_nr_due
564 Type : public
565 Parameters :
566 p_instance_id IN NUMBER Required
567 */
568 FUNCTION get_removal_nr_due
569 (
570 p_instance_id IN NUMBER)
571 RETURN DATE
572 IS
573 l_removal_nr_due DATE;
574
575 CURSOR get_removal_nr_due_date(c_instance_id NUMBER) IS
576 SELECT NVL(ue.earliest_due_date,ue.due_date)
577 FROM ahl_unit_effectivities_b ue,
578 CS_INCIDENTS_ALL_B nr
579 WHERE ue.csi_item_instance_id =c_instance_id
580 AND ue.cs_incident_id =nr.incident_id
581 AND nr.incident_type_id =fnd_profile.value('AHL_REMOVAL_NON_ROUTINE') -- Removal NR type Id
582 AND ue.object_type ='SR'
583 AND ue.application_usg_code = fnd_profile.value('AHL_APPLN_USAGE')
584 AND NVL(ue.status_code,'INIT-DUE') = 'INIT-DUE';
585
586 BEGIN
587 l_removal_nr_due:=NULL;
588 OPEN get_removal_nr_due_date(p_instance_id);
589 FETCH get_removal_nr_due_date into l_removal_nr_due;
590 /* sthilak back port #13488050 */
591 IF get_removal_nr_due_date%NOTFOUND THEN
592 l_removal_nr_due:= null;
593 END IF;
594 CLOSE get_removal_nr_due_date;
595
596 RETURN l_removal_nr_due;
597
598 END;
599 /*
600 Function Name : get_assm_due
601 Type : public
602 Parameters :
603 p_instance_id IN NUMBER Required
604 */
605 FUNCTION get_assm_due
606 (
607 p_instance_id IN NUMBER)
608 RETURN DATE
609 IS
610 /* sthilak back port #13488050 */
611 l_assm_due DATE;
612
613 CURSOR get_assm_due_date(c_instance_id NUMBER) IS
614 SELECT MIN(NVL(earliest_due_date,due_date))
615 FROM ahl_unit_effectivities_b B,
616 ahl_mr_headers_b mr
617 WHERE B.mr_header_id =mr.mr_header_id
618 AND NVL(mr.service_type_code,'ASR') = 'ASR'
619 AND B.object_type ='MR'
620 AND B.application_usg_code = fnd_profile.value('AHL_APPLN_USAGE')
621 AND NVL(B.status_code,'INIT-DUE') = 'INIT-DUE'
622 --AND B.preceding_ue_id IS NULL
623 AND B.csi_item_instance_id IN
624 (SELECT subject_id
625 FROM csi_ii_relationships
626 WHERE 1 =1
627 START WITH object_id =c_instance_id
628 AND relationship_type_code = 'COMPONENT-OF'
629 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
630 CONNECT BY prior subject_id=object_id
631 AND relationship_type_code = 'COMPONENT-OF'
632 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
633
634 UNION
635
636 SELECT c_instance_id AS subject_id
637 FROM dual -- incliude the root instance id also
638 );
639
640 BEGIN
641 /* sthilak back port #13488050 */
642 l_assm_due:= NULL;
643 OPEN get_assm_due_date(p_instance_id);
644 FETCH get_assm_due_date INTO l_assm_due;
645 IF get_assm_due_date%NOTFOUND THEN
646
647
648 l_assm_due:= NULL;
649 END IF;
650 CLOSE get_assm_due_date;
651 RETURN l_assm_due;
652 END;
653 /*
654 Function Name : get_earliest_assm_due
655 Type : public
656 Parameters :
657 p_instance_id IN NUMBER Required
658 */
659 FUNCTION get_earliest_assm_due
660 (
661 p_instance_id IN NUMBER)
662 RETURN DATE
663 IS
664 l_assm_due DATE;
665 l_nr_due DATE;
666 BEGIN
667 l_assm_due :=NULL;
668 l_nr_due :=NULL;
669 l_assm_due :=get_assm_due(p_instance_id);
670 l_nr_due :=get_removal_nr_due(p_instance_id);
671 IF l_assm_due IS NULL THEN
672 RETURN l_nr_due;
673 ELSE
674 IF l_nr_due IS NULL THEN
675 RETURN l_assm_due;
676 ELSE
677 RETURN least(l_assm_due,l_nr_due);
678 END IF;
679 END IF;
680 END;
681 /*
682 Function Name : is_unit_have_RTS_visit
683 Type : public
684 Parameters :
685 p_instance_id IN NUMBER Required
686 p_from_dt IN DATE Required
687 p_to_dt IN DATE Required
688 */
689 FUNCTION is_unit_have_RTS_visit
690 (
691 p_instance_id IN NUMBER,
692 p_from_dt IN DATE ,
693 p_to_dt IN DATE)
694 RETURN NUMBER
695 IS
696 l_have_visit NUMBER;
697 BEGIN
698 l_have_visit := 0;
699 SELECT COUNT(visit_id)
700 INTO l_have_visit
701 FROM ahl_visits_b visit
702 WHERE visit.status_code <> 'CLOSED'
703 AND TRUNC(Get_Visit_End_Date(visit.visit_id)) BETWEEN p_from_dt AND p_to_dt
704 AND visit.item_instance_id IN
705 (SELECT object_id
706 FROM csi_ii_relationships
707 WHERE 1 =1
708 START WITH subject_id = p_instance_id
709 AND AHL_COMPLEX_MX_PVT.Is_Instance_On_Visit(visit.visit_id,subject_id)='Y'
710 CONNECT BY PRIOR object_id = subject_id
711 AND AHL_COMPLEX_MX_PVT.Is_Instance_On_Visit(visit.visit_id,subject_id)='Y'
712 UNION
713
714 SELECT p_instance_id object_id FROM dual
715 )
716 AND IS_RTS_VISIT(p_instance_id,visit.visit_id) = 1;
717 RETURN l_have_visit;
718 END;
719
720 /*
721 Function Name : IS_RTS_VISIT
722 Type : public
723 Parameters :
724 p_instance_id IN NUMBER Required
725 p_visit_id IN NUMBER Required
726
727 */
728 FUNCTION IS_RTS_VISIT
729 (
730 p_instance_id IN NUMBER,
731 p_visit_id IN NUMBER)
732 RETURN NUMBER
733 IS
734 l_RTS_visit NUMBER;
735 BEGIN
736 l_RTS_visit := NULL;
737
738 SELECT 1
739 INTO l_RTS_visit
740 FROM ahl_visits_b vst,
741 ahl_visit_tasks_b task
742 WHERE vst.visit_id = task.visit_id
743 and vst.visit_id=p_visit_id
744 AND (vst.ITEM_INSTANCE_ID = p_instance_id
745 OR task.instance_id =p_instance_id)
746 AND task.return_to_supply_flag = 'Y'
747 AND task.status_code <> 'DELETED'
748 ANd rownum <2;
749
750 IF l_RTS_visit IS NULL then
751 RETURN 0;
752 ELSE
753 RETURN 1;
754 END IF;
755 END;
756
757 /*
758 Function Name : get_net_counter_reading
759 Type : public
760 Parameters :
761 p_instance_id IN NUMBER Required
762 p_ctr_template_name IN DATE Required
763 p_ctr_uom IN DATE Required
764 */
765 FUNCTION get_net_counter_reading
766 (
767 p_instance_id IN NUMBER,
768 p_ctr_template_name IN VARCHAR2,
769 p_ctr_uom IN VARCHAR2)
770 RETURN NUMBER
771 IS
772 l_ctr_reading NUMBER;
773 l_ctr_uom VARCHAR2(10);
774 BEGIN
775 l_ctr_reading:=0;
776 SELECT cc.UOM_CODE,
777 (SELECT ccr.net_reading
778 FROM csi_counter_readings ccr
779 WHERE ccr.counter_value_id = cc.CTR_VAL_MAX_SEQ_NO
780 AND NVL(ccr.disabled_flag,'N') = 'N'
781 )
782 INTO l_ctr_uom,
783 l_ctr_reading
784 FROM CSI_COUNTER_ASSOCIATIONS CCA,
785 CSI_COUNTERS_VL CC
786 WHERE CCA.COUNTER_ID = CC.COUNTER_ID
787 AND CCA.SOURCE_OBJECT_ID = p_instance_id
788 AND CCA.SOURCE_OBJECT_CODE = 'CP'
789 AND CC.COUNTER_TEMPLATE_NAME = p_ctr_template_name;
790 RETURN l_ctr_reading;
791 END;
792
793 --sukhwsin::Complex Assembly Maintenance - changes starts
794 /*
795 Function Name : Get_Visit_End_Date
796 Type : public
797 Parameters :
798 p_visit_id IN NUMBER Required
799 */
800 FUNCTION Get_Visit_End_Date
801 (p_visit_id IN NUMBER)
802 RETURN DATE
803 IS
804
805 CURSOR C_Visit_End_Date(p_visit_id NUMBER)
806 IS
807 select max(nvl(wdj.scheduled_completion_date,vts.end_date_time))
808 from ahl_visit_tasks_b vts, ahl_workorders wo, wip_discrete_jobs wdj
809 where
810 vts.visit_id = p_visit_id
811 and vts.return_to_supply_flag = 'Y'
812 and vts.status_code <> 'DELETED'
813 and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7))
814 /*If workorder exists then its status should not be Closed, Deleted, Complete, Complete No-charge, Cancelled */
815 and vts.visit_task_id = wo.visit_task_id (+)
816 and wo.wip_entity_id = wdj.wip_entity_id (+);
817
818 CURSOR c_visit_close_date(p_visit_id NUMBER) IS
819 SELECT CLOSE_DATE_TIME
820 FROM AHL_VISITS_B
821 WHERE VISIT_ID = p_visit_id;
822
823 l_end_date DATE := NULL;
824 BEGIN
825
826 OPEN C_Visit_End_Date(p_visit_id);
827 FETCH C_Visit_End_Date INTO l_end_date;
828 CLOSE C_Visit_End_Date;
829
830 IF (l_end_date IS NULL) THEN
831 OPEN c_visit_close_date(p_visit_id);
832 FETCH c_visit_close_date INTO l_end_date;
833 CLOSE c_visit_close_date;
834 END IF;
835 RETURN l_end_date;
836 END Get_Visit_End_Date;
837
838 /*
839 FUNCTION Get_Visit_Count : This function return visit count between two dates for particular item and organization.
840 This function is used in Repair Forecast Search UI to reterive Planned and Scheduled Quantity.
841 Type Public
842 Parameters:
843 Parameter Name Type Mandatory
844 p_item_id NUMBER Y
845 p_organization_id NUMBER Y
846 p_start_date DATE Y
847 p_end_date DATE Y
848 p_visit_state VARCHAR2 Y
849
850 Return Type: NUMBER
851
852 Created by sukhwsin
853 */
854 FUNCTION Get_Visit_Count
855 (p_item_id IN NUMBER,
856 p_organization_id IN NUMBER,
857 p_start_date IN DATE,
858 p_end_date IN DATE,
859 p_visit_state IN VARCHAR2)
860 RETURN NUMBER
861 IS
862
863 CURSOR GET_VISIT_COUNT_CSR (ITEM_ID IN NUMBER, ORG_ID IN NUMBER, START_DATE IN DATE, END_DATE IN DATE, c_visit_state IN VARCHAR2) IS
864 SELECT COUNT(*) FROM (
865 SELECT distinct vst.visit_id, vts.instance_id
866 FROM
867 AHL_VISITS_B VST, AHL_VISIT_TASKS_B VTS
868 WHERE
869 vst.visit_id = vts.visit_id
870 AND ( (c_visit_state = 'PLANNED' and VST.STATUS_CODE IN ('DRAFT','PLANNING')) OR
871 (c_visit_state = 'SCHEDULED' and VST.STATUS_CODE IN ('RELEASED','PARTIALLY RELEASED')))
872 AND VTS.INVENTORY_ITEM_ID = ITEM_ID
873 AND VST.ORGANIZATION_ID = ORG_ID
874 and vts.return_to_supply_flag = 'Y'
875 and vts.status_code <> 'DELETED'
876 and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7))
877 AND TRUNC(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)
878 union
879 SELECT distinct vst.visit_id, vst.item_instance_id
880 FROM
881 AHL_VISITS_B VST
882 WHERE
883 ( (c_visit_state = 'PLANNED' and VST.STATUS_CODE IN ('DRAFT','PLANNING')) OR
884 (c_visit_state = 'SCHEDULED' and VST.STATUS_CODE IN ('RELEASED','PARTIALLY RELEASED')))
885 AND VST.INVENTORY_ITEM_ID = ITEM_ID
886 AND VST.ORGANIZATION_ID = ORG_ID
887 and exists(select 1 from ahl_visit_tasks_b vts where vts.visit_id = vst.visit_id and vts.return_to_supply_flag = 'Y' and vts.inventory_item_id <> vst.inventory_item_id and
888 vts.status_code <> 'DELETED' and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7)) and rownum < 2)
889 AND TRUNC(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) BETWEEN TRUNC(START_DATE) AND TRUNC(END_DATE)
890 );
891
892 l_visit_cnt NUMBER := 0;
893 BEGIN
894 OPEN GET_VISIT_COUNT_CSR(P_ITEM_ID, P_ORGANIZATION_ID, P_START_DATE, P_END_DATE, P_VISIT_STATE);
895 FETCH GET_VISIT_COUNT_CSR INTO l_visit_cnt;
896 CLOSE GET_VISIT_COUNT_CSR;
897
898 RETURN l_visit_cnt;
899
900 END Get_Visit_Count;
901
902 /*
903 FUNCTION Get_Monthly_Visit_Count : This function return monthly visit count for given date, item and organization.
904 This function is used in Repair Forecast Search UI - in inner table to reterive Monthly Visit Quantity.
905 Type Public
906 Parameters:
907 Parameter Name Type Mandatory
908 p_item_id NUMBER Y
909 p_organization_id NUMBER Y
910 p_req_date DATE Y
911
912 Return Type: NUMBER
913
914 Created by sukhwsin
915 */
916 FUNCTION Get_Monthly_Visit_Count
917 (p_item_id IN NUMBER,
918 p_organization_id IN NUMBER,
919 p_req_date IN DATE)
920 RETURN NUMBER
921 IS
922
923 CURSOR GET_VISIT_COUNT_CSR (ITEM_ID IN NUMBER, ORG_ID IN NUMBER, REQ_DATE IN DATE) IS
924 SELECT COUNT(*) FROM (
925 SELECT distinct vst.visit_id, vts.instance_id
926 FROM
927 AHL_VISITS_B VST, AHL_VISIT_TASKS_B VTS
928 WHERE
929 vst.visit_id = vts.visit_id
930 AND vst.status_code IN ('DRAFT' , 'PLANNING', 'PARTIALLY RELEASED', 'RELEASED')
931 AND VTS.INVENTORY_ITEM_ID = ITEM_ID
932 AND VST.ORGANIZATION_ID = ORG_ID
933 and vts.return_to_supply_flag = 'Y'
934 and vts.status_code <> 'DELETED'
935 and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7))
936 and LAST_DAY(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) = REQ_DATE
937 union
938 SELECT distinct vst.visit_id, vst.item_instance_id
939 FROM
940 AHL_VISITS_B VST
941 WHERE
942 vst.status_code IN ('DRAFT' , 'PLANNING', 'PARTIALLY RELEASED', 'RELEASED')
943 AND VST.INVENTORY_ITEM_ID = ITEM_ID
944 AND VST.ORGANIZATION_ID = ORG_ID
945 and exists(select 1 from ahl_visit_tasks_b vts where vts.visit_id = vst.visit_id and vts.return_to_supply_flag = 'Y' and vts.inventory_item_id <> vst.inventory_item_id and
946 vts.status_code <> 'DELETED' and not exists (select '1' from ahl_workorders where visit_task_id = vts.visit_task_id and status_code in (12,22,4,5,7)) and rownum < 2)
947 and LAST_DAY(AHL_COMPLEX_MX_PVT.Get_Visit_End_Date(VST.VISIT_ID)) = REQ_DATE
948 );
949
950 l_visit_cnt NUMBER := 0;
951 BEGIN
952 OPEN GET_VISIT_COUNT_CSR(P_ITEM_ID, P_ORGANIZATION_ID, P_REQ_DATE);
953 FETCH GET_VISIT_COUNT_CSR INTO l_visit_cnt;
954 CLOSE GET_VISIT_COUNT_CSR;
955
956 RETURN l_visit_cnt;
957
958 END Get_Monthly_Visit_Count;
959
960 /*
961 FUNCTION Is_Instance_On_Visit : This function returns flag (Y/N) based upon whether instance is on Visit Context or not.
962 Instance can be on Visit Context if it is attached to Visit Unit OR Visit Locator OR Visit job.
963 This function is used in Execution - MR Search Page
964 Type Public
965 Parameters:
966 Parameter Name Type Mandatory
967 p_visit_id NUMBER Y
968 p_instance_id NUMBER Y
969
970 Return Type: VARCHAR2 (Y or N )
971
972 Created by sukhwsin
973 */
974
975 FUNCTION Is_Instance_On_Visit
976 (p_visit_id NUMBER,
977 p_instance_id NUMBER)
978 RETURN VARCHAR2
979 IS
980
981 CURSOR Get_Instance_Attributes(c_instance_id IN NUMBER) IS
982 SELECT cii.instance_id,cii.location_type_code, cii.instance_usage_code,cii.wip_job_id,cii.inv_locator_id,
983 mil.segment19,mil.segment20
984 -- fix for bug 16304850
985 --FROM csi_item_instances cii ,mtl_item_locations_kfv mil
989 and cii.inv_locator_id = mil.inventory_location_id (+);
986 FROM csi_item_instances cii ,mtl_item_locations mil
987 -- end fix for bug 16304850
988 WHERE cii.instance_id = c_instance_id
990
991 CURSOR Get_Visit_Unit_Instance(c_visit_id NUMBER) IS
992 SELECT item_instance_id
993 FROM AHL_VISITS_B
994 WHERE visit_id = c_visit_id;
995
996 CURSOR Get_Child_Instances(c_instance_id NUMBER) IS
997 SELECT object_id, subject_id, position_reference, level, relationship_id csi_ii_relationship_id,
998 object_version_number csi_ii_object_version_number
999 FROM csi_ii_relationships
1000 START WITH object_id = c_instance_id
1001 AND relationship_type_code = 'COMPONENT-OF'
1002 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1003 CONNECT BY PRIOR subject_id = object_id
1004 AND relationship_type_code = 'COMPONENT-OF'
1005 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1))
1006 ORDER BY level;
1007
1008 --Cursor to get Visit Id from Locator attributes
1009 CURSOR Get_Visit_From_Prj_Task(c_project_id IN NUMBER, c_project_task_id IN NUMBER) IS
1010 SELECT vst.visit_Id
1011 FROM ahl_visits_b vst, ahl_visit_tasks_b vts
1012 WHERE
1013 vst.project_id = c_project_id
1014 and vst.visit_id = vts.visit_id
1015 and vts.project_task_id = c_project_task_id
1016 and rownum < 2;
1017
1018 --Cursor to get Visit Id from Job
1019 CURSOR Get_Visit_From_Job(c_wip_entity_id NUMBER) IS
1020 SELECT VST.Visit_Id
1021 FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B VTS, AHL_VISITS_B VST
1022 WHERE
1023 WO.wip_entity_id = c_wip_entity_id
1024 AND VTS.visit_task_id = WO.visit_task_id
1025 AND VST.visit_id = VTS.visit_id;
1026
1027 --local variable declaration
1028 l_visit_id NUMBER;
1029 l_inst_attributes Get_Instance_Attributes%ROWTYPE;
1030 l_inst_on_visit VARCHAR2(1);
1031 l_visit_unit_inst NUMBER;
1032 BEGIN
1033 l_inst_on_visit := 'N';
1034 IF (p_visit_id IS NULL OR p_instance_id IS NULL) THEN
1035 RETURN l_inst_on_visit;
1036 END IF;
1037 OPEN Get_Instance_Attributes(p_instance_id);
1038 FETCH Get_Instance_Attributes INTO l_inst_attributes;
1039 IF (Get_Instance_Attributes%NOTFOUND) THEN
1040 CLOSE Get_Instance_Attributes;
1041 RETURN l_inst_on_visit;
1042 END IF;
1043 CLOSE Get_Instance_Attributes;
1044 -----Following Logic Based upon instance location to check whether instance is on Visit Unit or not.
1045 IF (l_inst_attributes.location_type_code = 'HZ_PARTY_SITES') THEN
1046 --This is the case when instance is on Configuration. Now Check whether it is on Visit Unit or not.
1047 OPEN Get_Visit_Unit_Instance(p_visit_id);
1048 FETCH Get_Visit_Unit_Instance INTO l_visit_unit_inst;
1049 CLOSE Get_Visit_Unit_Instance;
1050 IF (l_visit_unit_inst IS NULL) THEN
1051 RETURN l_inst_on_visit;
1052 ELSE
1053 --First check whether the instance on Visit is same as instance sent as parameter. If this is the case then return Y
1054 IF (l_visit_unit_inst = p_instance_id) THEN
1055 RETURN 'Y';
1056 END IF;
1057 --If Visit Unit instance is not same as param instance. Then check out whether param instance is under Visit instance or not.
1058 FOR i IN Get_Child_Instances(l_visit_unit_inst)
1059 LOOP
1060 IF(i.subject_id = p_instance_id) THEN
1061 l_inst_on_visit := 'Y';
1062 EXIT;
1063 END IF;
1064 END LOOP;
1065 END IF;
1066
1067 ELSIF(l_inst_attributes.location_type_code = 'INVENTORY') THEN
1068 IF (l_inst_attributes.segment19 IS NOT NULL AND l_inst_attributes.segment20 IS NOT NULL) THEN
1069 OPEN Get_Visit_From_Prj_Task(l_inst_attributes.segment19, l_inst_attributes.segment20);
1070 FETCH Get_Visit_From_Prj_Task INTO l_visit_id;
1071 CLOSE Get_Visit_From_Prj_Task;
1072 IF (NVL(l_visit_id,-1) = p_visit_id) THEN
1073 l_inst_on_visit := 'Y';
1074 END IF;
1075 END IF;
1076
1077 ELSIF(l_inst_attributes.location_type_code = 'WIP') THEN
1078 IF (l_inst_attributes.wip_job_id IS NOT NULL) THEN
1079 OPEN Get_Visit_From_Job(l_inst_attributes.wip_job_id);
1080 FETCH Get_Visit_From_Job INTO l_visit_id;
1081 CLOSE Get_Visit_From_Job;
1082 IF (NVL(l_visit_id,-1) = p_visit_id) THEN
1083 l_inst_on_visit := 'Y';
1084 END IF;
1085 END IF;
1086
1087 END IF;
1088 RETURN l_inst_on_visit;
1089 END Is_Instance_On_Visit;
1090
1091 --sukhwsin::Complex Assembly Maintenance - changes ends
1092
1093 END AHL_COMPLEX_MX_PVT;
[Home] [Help]