1 PACKAGE BODY AHL_CMP_UTIL_PKG AS
2 /* $Header: AHLUCMPB.pls 120.0.12020000.4 2013/02/19 20:55:18 sikumar noship $ */
3
4 -- Package constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_CMP_UTIL_PKG';
6 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
7
8 ------------------------------------------------------------------------------------
9 -- Common variables
10 ------------------------------------------------------------------------------------
11 l_log_current_level NUMBER := FND_LOG.g_current_runtime_level;
12 l_log_statement NUMBER := FND_LOG.level_statement;
13 l_log_procedure NUMBER := FND_LOG.level_procedure;
14 l_log_error NUMBER := FND_LOG.level_error;
15 l_log_exception NUMBER := FND_LOG.level_exception;
16 l_log_unexpected NUMBER := FND_LOG.level_unexpected;
17 ------------------------------------------------------------------------------------
18
19 ------------------------------------------------------------------------------------
20 -- Start of Comments
21 -- Function name : Get_Rpr_Batch_For_Inst
22 -- Type : Public
23 -- Function : Function to get the repair batch name where a given instance resides.
24 -- Returns NULL if no open repair batch can be found.
25 --
26 -- Pre-reqs :
27 -- Parameters :
28 --
29 -- Get_Rpr_Batch_For_Inst Parameters:
30 -- p_instance_id IN Given instance id. Required
31 -- p_org_id IN Given org id. Required
32 --
33 -- End of Comments
34
35 FUNCTION Get_Rpr_Batch_For_Inst (
36 p_instance_id IN NUMBER,
37 p_org_id IN NUMBER
38 ) RETURN VARCHAR2 IS
39
40 -- cursor to get the component visit id for a given org
41 CURSOR get_comp_visit_csr (c_org_id NUMBER) IS
42 SELECT VST.visit_id
43 FROM AHL_VISITS_B VST, AHL_VISIT_TYPES_B VTB
44 WHERE VST.organization_id = c_org_id
45 AND VST.visit_type_code = VTB.visit_type_code
46 AND NVL(VTB.component_visit_flag, 'N') = 'Y'
47 AND VTB.status_code = 'COMPLETE';
48
49 -- pdoki modified for Bug 14068468
50 -- cursor to get the repair batch name in the given visit, whose locator holds the given instance
51 CURSOR get_rpr_batch_csr (c_visit_id NUMBER,
52 c_instance_id NUMBER) IS
53 SELECT TSK.repair_batch_name
54 FROM AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
55 CSI_ITEM_INSTANCES CSI, MTL_ITEM_LOCATIONS MTL
56 WHERE TSK.visit_id = c_visit_id
57 AND VST.visit_id = TSK.visit_id
58 AND (
59 MTL.physical_location_id = VST.comp_planning_loc_id
60 OR MTL.physical_location_id = VST.comp_inrepair_loc_id
61 )
62 AND MTL.project_id = VST.project_id
63 AND MTL.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)
64 AND CSI.inv_locator_id = MTL.inventory_location_id
65 AND CSI.instance_id = c_instance_id
66 AND TSK.task_type_code = 'SUMMARY'
67 AND TSK.status_code NOT IN ('CANCELLED', 'CLOSED', 'DELETED')
68 AND TSK.repair_batch_name IS NOT NULL;
69
70 --
71 l_api_name CONSTANT VARCHAR2(30) := 'Get_Rpr_Batch_For_Inst';
72 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
73
74 TYPE t_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
75 l_visit_id_tbl t_id_tbl;
76
77 l_ret_val VARCHAR2(240);
78 --
79
80 BEGIN
81 IF (l_log_procedure >= l_log_current_level) THEN
82 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
83 END IF;
84
85 IF (l_log_statement >= l_log_current_level) THEN
86 FND_LOG.string(l_log_statement, l_full_name,' p_instance_id = '||p_instance_id||
87 ', p_org_id = '||p_org_id);
88 END IF;
89
90 -- Get the component visit ids for the given org
91 OPEN get_comp_visit_csr (p_org_id);
92 FETCH get_comp_visit_csr BULK COLLECT INTO l_visit_id_tbl;
93 CLOSE get_comp_visit_csr;
94 IF (l_log_statement >= l_log_current_level) THEN
95 FND_LOG.string(l_log_statement, l_full_name,' l_visit_id_tbl.COUNT = '||l_visit_id_tbl.COUNT);
96 END IF;
97
98 IF (l_visit_id_tbl.COUNT > 0) THEN
99 FOR i IN l_visit_id_tbl.FIRST..l_visit_id_tbl.LAST LOOP
100 OPEN get_rpr_batch_csr (l_visit_id_tbl(i), p_instance_id);
101 FETCH get_rpr_batch_csr INTO l_ret_val;
102 CLOSE get_rpr_batch_csr;
103
104 IF (l_ret_val IS NOT NULL) THEN
105 EXIT; -- exit loop
106 END IF;
107 END LOOP;
108 END IF;
109
110 IF (l_log_statement >= l_log_current_level) THEN
111 FND_LOG.string(l_log_statement, l_full_name,' l_ret_val = '||l_ret_val);
112 END IF;
113
114 IF (l_log_procedure >= l_log_current_level) THEN
115 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
116 END IF;
117
118 RETURN l_ret_val;
119 END Get_Rpr_Batch_For_Inst;
120 ------------------------------------------------------------------------------------
121
122 ------------------------------------------------------------------------------------
123 -- Start of Comments
124 -- Function name : Get_Rpr_Status_For_Inst
125 -- Type : Public
126 -- Function : Function to get the repair status for a given instance and its inventory org id.
127 -- It returns:
128 -- - UNPLANNED : If instance is in sub-inventory, not in any repair batch.
129 -- - PLANNING : If instance is in a repair batch and does not have any RTS workorder
130 -- - PRODUCTION: If instance is in a repair batch and has an RTS workorder
131 -- - NULL : If instance is not in INVENTORY
132 --
133 -- Status lookup_type is AHL_CMP_REPAIR_STATUS.
134 --
135 -- Pre-reqs :
136 -- Parameters :
137 --
138 -- Get_Rpr_Status_For_Inst Parameters:
139 -- p_instance_id IN Given instance id. Required
140 -- p_org_id IN Given org id. Required
141 --
142 -- End of Comments
143
144 FUNCTION Get_Rpr_Status_For_Inst (
145 p_instance_id IN NUMBER,
146 p_org_id IN NUMBER
147 ) RETURN VARCHAR2 IS
148
149 -- cursor to validate that the instance location is INVENTORY
150 CURSOR validate_inst_loc_csr (c_instance_id NUMBER) IS
151 SELECT 'X'
152 FROM CSI_ITEM_INSTANCES
153 WHERE instance_id = c_instance_id
154 AND location_type_code = 'INVENTORY'
155 AND TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
156 AND TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
157
158 --
159 l_api_name CONSTANT VARCHAR2(30) := 'Get_Rpr_Status_For_Inst';
160 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
161
162 l_dummy VARCHAR2(1);
163 l_rts_flag VARCHAR2(1);
164 l_rpr_batch VARCHAR(240);
165 l_ret_val VARCHAR2(30);
166 --
167
168 BEGIN
169 IF (l_log_procedure >= l_log_current_level) THEN
170 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
171 END IF;
172
173 IF (l_log_statement >= l_log_current_level) THEN
174 FND_LOG.string(l_log_statement, l_full_name,' p_instance_id = '||p_instance_id||
175 ', p_org_id = '||p_org_id);
176 END IF;
177
178 -- Validate instance location
179 OPEN validate_inst_loc_csr (p_instance_id);
180 FETCH validate_inst_loc_csr INTO l_dummy;
181 IF (validate_inst_loc_csr%FOUND) THEN
182 -- For instances in INVENTORY, get the repair batch for the instance
183 l_rpr_batch := AHL_CMP_UTIL_PKG.Get_Rpr_Batch_For_Inst(p_instance_id, p_org_id);
184
185 IF (l_rpr_batch IS NOT NULL) THEN
186 -- if the repair batch has any RTS workorder for this instance, then the status is PRODUCTION
187 -- else, the status is PLANNING
188 l_rts_flag := AHL_CMP_UTIL_PKG.Check_Rts_Workorder_Exists(l_rpr_batch, p_instance_id);
189 IF (l_rts_flag = 'Y') THEN
190 l_ret_val := 'PRODUCTION';
191 ELSE
192 l_ret_val := 'PLANNING';
193 END IF;
194 ELSE
195 -- instance is not in any repair batch, so status is UNPLANNED
196 l_ret_val := 'UNPLANNED';
197 END IF;
198 END IF; -- validate_inst_loc_csr
199 CLOSE validate_inst_loc_csr;
200
201 IF (l_log_statement >= l_log_current_level) THEN
202 FND_LOG.string(l_log_statement, l_full_name,' l_ret_val = '||l_ret_val);
203 END IF;
204
205 IF (l_log_procedure >= l_log_current_level) THEN
206 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
207 END IF;
208
209 RETURN l_ret_val;
210 END Get_Rpr_Status_For_Inst;
211 ------------------------------------------------------------------------------------
212
213 ------------------------------------------------------------------------------------
214 -- Start of Comments
215 -- Function name : Get_Comp_Visit_For_Org
216 -- Type : Public
217 -- Function : Function to get the valid Component Visit id for the given Org id.
218 -- Returns NULL if no valid Component Visit exists.
219 --
220 -- A valid Component Visit is one that satisfies the following criteria:
221 -- 1) Visit Type attribute has an associated Visit Type where the
222 -- Component Visit indicator = Yes
223 -- 2) Visit Start date <= SYSDATE and Planned End date > SYSDATE
224 -- 3) Visit Locked indicator = No
225 -- 4) Mandatory attributes for Component Visit populated
226 --
227 -- Pre-reqs :
228 -- Parameters :
229 --
230 -- Get_Comp_Visit_For_Org Parameters:
231 -- p_org_id IN Given org id. Required
232 --
233 -- End of Comments
234
235 FUNCTION Get_Comp_Visit_For_Org (
236 p_org_id IN NUMBER
237 ) RETURN NUMBER IS
238
239 -- cursor to get the open component visit id for the given organization
240 CURSOR get_comp_visit_csr (c_org_id NUMBER) IS
241 SELECT AVB.visit_id,
242 AVB.status_code
243
244 FROM AHL_VISITS_B AVB,
245 AHL_VISIT_TYPES_B AVTB
246
247 WHERE AVB.organization_id = c_org_id
248 AND AVB.visit_type_code = AVTB.visit_type_code
249 AND AVTB.status_code = 'COMPLETE'
250 AND AVTB.component_visit_flag = 'Y' -- criterion 1
251 AND AVB.start_date_time <= SYSDATE
252 AND AVB.close_date_time > SYSDATE -- criterion 2
253 AND NVL(AVB.locked_flag, 'N') = 'N' -- criterion 3
254 AND AVB.status_code NOT IN ('CANCELLED','CLOSED','DELETED');-- PRAKKUM :: BUG 14542057 :: 31/08/2012
255
256 -- cursor for getting visit details given visit id
257 CURSOR get_visit_details_csr (c_visit_id NUMBER) IS
258 SELECT visit_id,
259 organization_id,
260 department_id,
261 start_date_time,
262 close_date_time,
263 comp_planning_loc_id,
264 comp_inrepair_loc_id,
265 firmed_flag
266 FROM AHL_VISITS_B
267 WHERE visit_id = c_visit_id;
268
269 --
270 l_api_name CONSTANT VARCHAR2(30) := 'Get_Comp_Visit_For_Org';
271 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
272
273 l_return_status VARCHAR2(1);
274 l_msg_count NUMBER;
275 l_msg_data VARCHAR2(2000);
276
277 l_ret_val NUMBER;
278 l_status_code VARCHAR2(30);
279 l_visit_rec AHL_VWP_VISITS_PVT.visit_rec_type;
280 --
281
282 BEGIN
283 IF (l_log_procedure >= l_log_current_level) THEN
284 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
285 END IF;
286
287 IF (l_log_statement >= l_log_current_level) THEN
288 FND_LOG.string(l_log_statement, l_full_name,' p_org_id = '||p_org_id);
289 END IF;
290
291 -- Get the Open Component Visit for the given org
292 OPEN get_comp_visit_csr (p_org_id);
293 FETCH get_comp_visit_csr INTO l_ret_val, l_status_code;
294 CLOSE get_comp_visit_csr;
295
296 /* An old VWP visit with Component Visit Type can also be obtained from the above cursor query,
297 * so a check must be made to avoid identifying an older visit as Component Visit.
298 */
299 IF (l_ret_val IS NOT NULL AND
300 AHL_VWP_VISITS_PVT.Is_Old_Visit (l_ret_val) = 'Y') THEN
301 l_ret_val := null;
302 END IF;
303
304 -- Validate the Component Visit obtained from the above cursor if it's in Draft or Planning status
305 IF (l_ret_val IS NOT NULL AND l_status_code IN ('DRAFT', 'PLANNING')) THEN
306
307 -- Populate visit record with the details required for validating a Component Visit
308 OPEN get_visit_details_csr (l_ret_val);
309 FETCH get_visit_details_csr INTO l_visit_rec.visit_id,
310 l_visit_rec.organization_id,
311 l_visit_rec.department_id,
312 l_visit_rec.start_date,
313 l_visit_rec.plan_end_date,
314 l_visit_rec.comp_planning_loc_id,
315 l_visit_rec.comp_inrepair_loc_id,
316 l_visit_rec.firmed_flag;
317 CLOSE get_visit_details_csr;
318
319 IF (l_log_statement >= l_log_current_level) THEN
320 FND_LOG.string(l_log_statement, l_full_name,' Invoking AHL_VWP_VISITS_PVT.Validate_Component_Visit API for visit_id = ' || l_ret_val);
321 END IF;
322
323 AHL_VWP_VISITS_PVT.Validate_Component_Visit
324 (
325 p_api_version => 1.0,
326 p_init_msg_list => FND_API.G_FALSE,
327 p_commit => FND_API.G_FALSE,
328 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
329 p_module_type => null,
330 p_visit_rec => l_visit_rec,
331 x_return_status => l_return_status,
332 x_msg_count => l_msg_count,
333 x_msg_data => l_msg_data
334 );
335
336 IF (l_log_statement >= l_log_current_level) THEN
337 FND_LOG.string(l_log_statement, l_full_name,' After call to AHL_VWP_VISITS_PVT.Validate_Component_Visit API, Return Status = ' || l_return_status);
338 END IF;
339
340 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
341 IF (l_log_statement >= l_log_current_level) THEN
342 FND_LOG.string(l_log_statement, l_full_name,' Check for mandatory attributes for Open Component Visit ' || l_ret_val || ' failed.');
343 END IF;
344
345 l_ret_val := null;
346 END IF;
347
348 END IF; -- if l_ret_val is not null and l_status_code IN ('DRAFT', 'PLANNING')
349
350 IF (l_log_statement >= l_log_current_level) THEN
351 FND_LOG.string(l_log_statement, l_full_name,' l_ret_val = '||l_ret_val);
352 END IF;
353
354 IF (l_log_procedure >= l_log_current_level) THEN
355 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
356 END IF;
357
358 RETURN l_ret_val;
359 END Get_Comp_Visit_For_Org;
360 ------------------------------------------------------------------------------------
361
362 ------------------------------------------------------------------------------------
363 -- Start of Comments
364 -- Function name : Is_Comp_Visit
365 -- Type : Public
366 -- Function : Function to check whether the given Visit is a Component Visit.
367 -- Returns 'Y' if it's a Component Visit, 'N' otherwise.
368 --
369 -- Pre-reqs :
370 -- Parameters :
371 --
372 -- Is_Comp_Visit Parameters:
373 -- p_visit_id IN Given visit id. Required
374 --
375 -- End of Comments
376
377 FUNCTION Is_Comp_Visit (
378 p_visit_id IN NUMBER
379 ) RETURN VARCHAR2 IS
380
381 -- cursor to check whether the given Visit is a Component Visit
382 CURSOR chk_comp_visit_csr (c_visit_id NUMBER) IS
383 SELECT 'Y'
384 FROM AHL_VISITS_B AVB,
385 AHL_VISIT_TYPES_B AVTB
386 WHERE AVB.visit_id = c_visit_id
387 AND AVB.visit_type_code = AVTB.visit_type_code
388 AND AVTB.status_code = 'COMPLETE'
389 AND AVTB.component_visit_flag = 'Y';
390
391 --
392 l_api_name CONSTANT VARCHAR2(30) := 'Is_Comp_Visit';
393 l_full_name CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
394
395 l_ret_val VARCHAR2(1) := 'N';
396 --
397
398 BEGIN
399 IF (l_log_procedure >= l_log_current_level) THEN
400 FND_LOG.string(l_log_procedure, l_full_name || '.begin', 'At the start of the API');
401 END IF;
402
403 IF (l_log_statement >= l_log_current_level) THEN
404 FND_LOG.string(l_log_statement, l_full_name, ' p_visit_id = ' || p_visit_id);
405 END IF;
406
407 -- Check whether the given visit is a Component Visit
408 OPEN chk_comp_visit_csr (p_visit_id);
409 FETCH chk_comp_visit_csr INTO l_ret_val;
410 CLOSE chk_comp_visit_csr;
411
412 /* An old VWP visit with Component Visit Type can also be obtained from the above cursor query,
413 * so a check must be made to avoid identifying an older visit as Component Visit.
414 */
415 IF (l_ret_val = 'Y' AND
416 AHL_VWP_VISITS_PVT.Is_Old_Visit (p_visit_id) = 'Y') THEN
417 l_ret_val := 'N';
418 END IF;
419
420 IF (l_log_statement >= l_log_current_level) THEN
421 FND_LOG.string(l_log_statement, l_full_name, ' l_ret_val = ' || l_ret_val);
422 END IF;
423
424 IF (l_log_procedure >= l_log_current_level) THEN
425 FND_LOG.string(l_log_procedure, l_full_name || '.end', 'At the end of the API');
426 END IF;
427
428 RETURN l_ret_val;
429 END Is_Comp_Visit;
430 ------------------------------------------------------------------------------------
431
432 ------------------------------------------------------------------------------------
433 -- Start of Comments
434 -- Function name : Check_Rts_Workorder_Exists
435 -- Type : Public
436 -- Function : Function to check for a given Repair Batch and for a given instance if there exists atleast one Open Workorder with RTS flag as Yes.
437 -- Returns 'Y' if it has atleast one Open RTS Workorder, Oherwise 'N'.
438 -- If no value is passed for instance, then we look at all the instances of that Repair Batch.
439 --
440 -- Pre-reqs :
441 -- Parameters :
442 --
443 -- Check_Rts_Workorder_Exists Parameters:
444 -- p_repair_batch IN Given Repair Batch. Required
445 -- p_instance_id IN Given Instance Id Optional
446 --
447 -- End of Comments
448 -------------------------------------------------------------------------------------
449 FUNCTION Check_Rts_Workorder_Exists (
450 p_repair_batch IN VARCHAR2,
451 p_instance_id IN NUMBER
452 ) RETURN VARCHAR2 IS
453
454 l_result VARCHAR2(1) := 'N';
455 l_task_id NUMBER;
456 l_dummy VARCHAR2(1);
457
458 -- Cursor to check if the given repair batch has atleast one open RTS workorder
459 CURSOR Chk_Rts_Workorder(c_repair_batch VARCHAR2, c_ins_id NUMBER)
460 IS
461 SELECT 'x'
462 FROM AHL_WORKORDERS
463 WHERE status_code NOT IN ('7','12','17','22' ) -- Cancelled,Closed,Draft and Deleted
464 AND visit_task_id IN
465 (SELECT vt.visit_task_id
466 FROM AHL_VISIT_TASKS_B vt
467 WHERE vt.cost_parent_id IS NOT NULL
468 AND nvl(vt.return_to_supply_flag,'N') = 'Y'
469 AND vt.instance_id = nvl(c_ins_id,vt.instance_id)
470 START WITH vt.visit_task_id = (SELECT visit_task_id
471 FROM AHL_VISIT_TASKS_B
472 WHERE repair_batch_name = c_repair_batch)
473 CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id );
474
475
476 BEGIN
477
478 OPEN Chk_Rts_Workorder(p_repair_batch, p_instance_id);
479 FETCH Chk_Rts_Workorder INTO l_dummy;
480 IF(Chk_Rts_Workorder%FOUND) THEN
481 l_result := 'Y';
482 END IF;
483 CLOSE Chk_Rts_Workorder;
484
485 RETURN l_result;
486 END Check_Rts_Workorder_Exists;
487 -------------------------------------------------------------------------------------
488
489 ------------------------------------------------------------------------------------
490 -- Start of Comments
491 -- Function name : Get_Batch_Qty
492 -- Type : Public
493 -- Function : Function to retrieve the instance quantity located at repair batch Planning and In-Repair locators of a given Repair Batch
494 -- Pre-reqs :
495 -- Parameters :
496 --
497 -- Get_Batch_Qty Parameters:
498 -- p_repair_batch IN Given Repair Batch. Required
499 --
500 -- End of Comments
501 -------------------------------------------------------------------------------------
502 FUNCTION Get_Batch_Qty (
503 p_repair_batch IN VARCHAR2
504 ) RETURN NUMBER
505 IS
506 l_planning_qty NUMBER := 0;
507 l_inrepair_qty NUMBER := 0;
508
509 l_total_qty NUMBER := 0;
510
511 BEGIN
512
513 -- pdoki modified for Bug 14068468
514 -- Retrieves Planning Locator Quantity
515 SELECT NVL(SUM(CII.quantity),0)
516 INTO l_planning_qty
517 FROM CSI_ITEM_INSTANCES CII,
518 AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
519 MTL_ITEM_LOCATIONS MTL
520 WHERE TSK.repair_batch_name = p_repair_batch
521 AND VST.visit_id = TSK.visit_id
522 AND MTL.physical_location_id = VST.comp_planning_loc_id
523 AND MTL.project_id = VST.project_id
524 AND MTL.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)
525 AND CII.LOCATION_TYPE_CODE = 'INVENTORY'
526 AND CII.inv_locator_id = MTL.inventory_location_id
527 AND CII.inv_organization_id = VST.organization_id
528 AND CII.inventory_item_id = TSK.inventory_item_id
529 AND TRUNC(NVL(CII.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
530 AND TRUNC(NVL(CII.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
531
532
533 -- pdoki modified for Bug 14068468
534 -- Retrieves In-Repair Locator Quantity
535 SELECT NVL(SUM(CII.quantity),0)
536 INTO l_inrepair_qty
537 FROM CSI_ITEM_INSTANCES CII,
538 AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
539 MTL_ITEM_LOCATIONS MTL
540 WHERE TSK.repair_batch_name = p_repair_batch
541 AND VST.visit_id = TSK.visit_id
542 AND MTL.physical_location_id = VST.comp_inrepair_loc_id
543 AND MTL.project_id = VST.project_id
544 AND MTL.task_id = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)
545 AND CII.LOCATION_TYPE_CODE = 'INVENTORY'
546 AND CII.inv_locator_id = MTL.inventory_location_id
547 AND CII.inv_organization_id = VST.organization_id
548 AND CII.inventory_item_id = TSK.inventory_item_id
549 AND TRUNC(NVL(CII.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
550 AND TRUNC(NVL(CII.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
551
552 -- Summation of Planning and In-Repair Locator quantities results in Batch Qty.
553 l_total_qty := l_planning_qty + l_inrepair_qty ;
554
555 RETURN l_total_qty;
556
557 END;
558 -------------------------------------------------------------------------------------
559
560
561 ------------------------------------------------------------------------------------
562 -- Start of Comments
563 -- Function name : Get_Workorder_Qty
564 -- Type : Public
565 -- Function : Function to retrieve the instance quantity that has been issued to a workorder that is associated with this Batch.
566 --
567 -- Pre-reqs :
568 -- Parameters :
569 --
570 -- Get_Workorder_Qty Parameters:
571 -- p_repair_batch IN Given Repair Batch. Required
572 --
573 -- End of Comments
574 -------------------------------------------------------------------------------------
575 FUNCTION Get_Workorder_Qty (
576 p_repair_batch IN VARCHAR2
577 ) RETURN NUMBER
578 IS
579 l_qty NUMBER;
580 l_visit_task_id NUMBER;
581 l_total_qty NUMBER;
582
583 CURSOR Get_Task_Details(c_repair_batch varchar2)
584 IS
585 SELECT vt.visit_task_id
586 FROM AHL_VISIT_TASKS_B vt
587 WHERE VT.COST_PARENT_ID IS NOT NULL
588 START WITH vt.visit_task_id = ( SELECT visit_task_id
589 FROM AHL_VISIT_TASKS_B
590 WHERE REPAIR_BATCH_NAME = C_REPAIR_BATCH )
591 CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id;
592
593 CURSOR Get_Ins_Qty(C_VISIT_TASK_ID NUMBER)
594 IS
595 SELECT CII.QUANTITY
596 FROM CSI_ITEM_INSTANCES CII,
597 AHL_WORKORDERS WO
598 where WO.visit_task_id = c_visit_task_id
599 AND CII.location_type_code = 'WIP'
600 and CII.wip_job_id = WO.wip_entity_id
601 AND TRUNC(NVL(CII.ACTIVE_START_DATE, SYSDATE)) <= TRUNC(SYSDATE)
602 AND TRUNC(NVL(CII.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);
603
604
605 BEGIN
606
607 l_qty := 0;
608 l_total_qty := 0;
609
610 OPEN Get_Task_Details(p_repair_batch);
611 LOOP
612 FETCH Get_Task_Details into l_visit_task_id;
613 Exit when Get_Task_Details%NOTFOUND;
614
615 OPEN GET_INS_QTY(L_VISIT_TASK_ID);
616 LOOP
617 FETCH GET_INS_QTY INTO L_QTY;
618 EXIT WHEN GET_INS_QTY%NOTFOUND;
619 l_total_qty := l_total_qty + nvl(l_qty,0);
620 END LOOP;
621 Close GET_INS_QTY;
622 END LOOP;
623 CLOSE Get_Task_Details;
624
625 RETURN l_total_qty;
626
627 END;
628 ------------------------------------------------------------------------------------
629
630 ------------------------------------------------------------------------------------
631 -- Start of Comments
632 -- Function name : Get_Locator_Segments
633 -- Type : Public
634 -- Function : Function to retrieve the Concatenated Locator Segments for a given Locator.
635 --
636 -- Pre-reqs :
637 -- Parameters :
638 --
639 -- Get_Locator_Segments Parameters:
640 -- p_locator_id IN Given Locator. Required
641 --
642 -- End of Comments
643 -------------------------------------------------------------------------------------
644 FUNCTION Get_Locator_Segments (
645 p_locator_id IN NUMBER
646 ) RETURN VARCHAR2
647 IS
648 -- fix for bug 16304850
649 --l_concat_segs VARCHAR2(100);
650 l_concat_segs VARCHAR2(300);
651
652 CURSOR get_locator_segments(p_locator_id IN NUMBER)IS
653 SELECT DECODE(MIL.segment19, NULL, MIL_kfv.concatenated_segments, INV_PROJECT.GET_LOCSEGS(MIL_kfv.concatenated_segments)
654 || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
655 || INV_ProjectLocator_PUB.get_project_number(MIL.segment19)
656 || FND_FLEX_EXT.GET_DELIMITER('INV', 'MTLL', 101)
657 || INV_ProjectLocator_PUB.get_task_number(MIL.segment20)) locator_segments
658 FROM MTL_ITEM_LOCATIONS MIL,MTL_ITEM_LOCATIONS_KFV MIL_kfv
659 WHERE MIL_kfv.INVENTORY_LOCATION_ID = MIL.INVENTORY_LOCATION_ID AND
660 MIL_kfv.ORGANIZATION_ID = MIL.ORGANIZATION_ID AND
661 MIL.INVENTORY_LOCATION_ID = p_locator_id;
662
663 BEGIN
664
665 /*SELECT DECODE(MIL.segment19, NULL, MIL.concatenated_segments,INV_PROJECT.GET_LOCSEGS(MIL.concatenated_segments)
666 || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
667 || INV_ProjectLocator_PUB.get_project_number(MIL.segment19)
668 || FND_FLEX_EXT.GET_DELIMITER('INV', 'MTLL', 101)
669 || INV_PROJECTLOCATOR_PUB.GET_TASK_NUMBER(MIL.SEGMENT20))
670 INTO l_concat_segs
671 FROM MTL_ITEM_LOCATIONS_KFV MIL
672 WHERE MIL.INVENTORY_LOCATION_ID = p_locator_id;*/
673
674 OPEN get_locator_segments(p_locator_id);
675 FETCH get_locator_segments INTO l_concat_segs;
676 CLOSE get_locator_segments;
677
678 RETURN l_concat_segs;
679
680 END;
681 -------------------------------------------------------------------------------------
682
683 ------------------------------------------------------------------------------------
684 -- Start of Comments
685 -- Function name : Get_Repair_Batch
686 -- Type : Public
687 -- Function : Function to retrieve the Repair Batch for a given child task.
688 --
689 -- Pre-reqs :
690 -- Parameters :
691 --
692 -- Get_Repair_Batch Parameters:
693 -- p_child_task_id IN Child Task ID Required
694 --
695 -- End of Comments
696 -------------------------------------------------------------------------------------
697 FUNCTION Get_Repair_Batch (
698 p_child_task_id IN NUMBER
699 ) RETURN VARCHAR2
700 IS
701 l_rpr_batch AHL_VISIT_TASKS_B.REPAIR_BATCH_NAME%TYPE;
702
703 BEGIN
704
705 SELECT REPAIR_BATCH_NAME
706 INTO l_rpr_batch
707 FROM AHL_VISIT_TASKS_B
708 WHERE cost_parent_id IS NULL
709 AND repair_batch_name IS NOT NULL
710 START WITH visit_task_id = p_child_task_id
711 CONNECT BY PRIOR cost_parent_id = visit_task_id ;
712
713 RETURN l_rpr_batch;
714
715 END;
716 -------------------------------------------------------------------------------------
717
718 ------------------------------------------------------------------------------------
719 -- Start of Comments
720 -- Function name : Get_Planned_Quantity
721 -- Type : Public
722 -- Function : Function to retrieve the planned quantity for a given item,
723 -- organization and Workorder criteria
724 -- Planned qty : the # of unique workorder qty of item instances in an Org and Visit(s)
725 -- that does not have a Return to supply flag = YES
726 -- Pre-reqs :
727 -- Parameters :
728 --
729 -- Get_Planned_Quantity Parameters:
730 -- p_item_id IN NUMBER,
731 -- p_org_id IN NUMBER,
732 -- p_start_date IN DATE,
733 -- p_end_date IN DATE,
734 -- p_comp_visit_flag VARCHAR2
735 --
736 -- End of Comments
737 -------------------------------------------------------------------------------------
738 FUNCTION Get_Planned_Quantity (
739 p_item_id IN NUMBER,
740 p_org_id IN NUMBER,
741 p_start_date IN DATE,
742 p_end_date IN DATE,
743 p_comp_visit_flag VARCHAR2
744 ) RETURN NUMBER
745 IS
746 l_plan_qty NUMBER := 0;
747 l_instance_qty NUMBER := 0;
748 l_instance_id NUMBER;
749 l_rts_exist CHAR(1) := 'N';
750
751 /* Cursor to get the total workorder quantity for each instance, given the item ID
752 * , organization ID, scheduled start date and end date
753 */
754 CURSOR c_get_task_instance_qty(p_item_id NUMBER,
755 p_org_id NUMBER,
756 p_start_date DATE,
757 p_end_date DATE,
758 p_comp_visit_flag VARCHAR2) IS
759 SELECT SUM(task.quantity) plan_qty, task.instance_id FROM ahl_visit_tasks_b task,
760 ahl_workorders wo,ahl_visits_b visit,WIP_DISCRETE_JOBS WDJ
761 WHERE
762 wo.VISIT_TASK_ID = task.VISIT_TASK_ID
763 AND WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
764 AND WO.STATUS_CODE IN (1,3,6,17,19,20)
765 AND task.VISIT_ID = visit.visit_id
766 AND task.inventory_item_id = p_item_id
767 AND visit.organization_id = p_org_id
768 AND (p_comp_visit_flag IS NULL OR
769 AHL_CMP_UTIL_PKG.Is_Comp_Visit(task.visit_id) = p_comp_visit_flag)
770 AND WDJ.scheduled_completion_date >=
771 NVL(p_start_date,WDJ.scheduled_completion_date)
772 AND WDJ.scheduled_completion_date <=
773 NVL(p_end_date,WDJ.scheduled_completion_date)
774 AND task_type_code NOT IN ('SUMMARY')
775 GROUP BY
776 task.instance_id;
777
778 /* Cursor to identify whether a record with rts flag = Y exist for a
779 * particular instance
780 */
781 CURSOR c_is_rts_exist(p_instance_id NUMBER,
782 p_start_date DATE,
783 p_end_date DATE,
784 p_comp_visit_flag VARCHAR2) IS
785 SELECT 'Y' FROM ahl_visit_tasks_b task,
786 ahl_workorders wo,WIP_DISCRETE_JOBS WDJ
787 WHERE
788 wo.VISIT_TASK_ID = task.VISIT_TASK_ID
789 AND WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
790 AND WO.STATUS_CODE IN (1,3,6,17,19,20)
791 AND task.instance_id = p_instance_id
792 AND (p_comp_visit_flag IS NULL OR
793 AHL_CMP_UTIL_PKG.Is_Comp_Visit(task.visit_id) = p_comp_visit_flag)
794 AND WDJ.scheduled_completion_date >=
795 NVL(p_start_date,WDJ.scheduled_completion_date)
796 AND WDJ.scheduled_completion_date <=
797 NVL(p_end_date,WDJ.scheduled_completion_date)
798 AND return_to_supply_flag = 'Y'
799 AND task_type_code NOT IN ('SUMMARY')
800 AND rownum < 2;
801
802 l_task_instance_qty_rec c_get_task_instance_qty%ROWTYPE;
803
804 BEGIN
805 FOR l_task_instance_qty_rec IN c_get_task_instance_qty(p_item_id,
806 p_org_id, p_start_date, p_end_date, p_comp_visit_flag)
807 LOOP
808 l_instance_id := l_task_instance_qty_rec.instance_id;
809 l_instance_qty := l_task_instance_qty_rec.plan_qty;
810 /* For an instance, if there exist a record with rts flag ='Y' then
811 * the planned quantity for that instance(l_instance_qty) is 0
812 */
813 OPEN c_is_rts_exist(l_instance_id, p_start_date, p_end_date, p_comp_visit_flag);
814 FETCH c_is_rts_exist INTO l_rts_exist;
815 CLOSE c_is_rts_exist;
816 IF(l_rts_exist = 'Y') THEN
817 l_instance_qty := 0;
818 l_rts_exist := 'N';
819 END IF;
820
821 l_plan_qty := l_plan_qty + l_instance_qty;
822
823 END LOOP;
824
825
826 RETURN l_plan_qty;
827
828 END Get_Planned_Quantity;
829 -------------------------------------------------------------------------------------
830
831 ------------------------------------------------------------------------------------
832 -- Start of Comments
833 -- Function name : Get_Scheduled_Quantity
834 -- Type : Public
835 -- Function : Function to retrieve the planned quantity for a given item, organization and Workorder criteria
836 -- Scheduled qty : the # of unique workorder qty of item instances in an Org and Visit(s)
837 -- that have a Return to supply flag = YES
838 -- Pre-reqs :
839 -- Parameters :
840 --
841 -- Get_Scheduled_Quantity Parameters:
842 -- p_item_id IN NUMBER,
843 -- p_org_id IN NUMBER,
844 -- p_start_date IN DATE,
845 -- p_end_date IN DATE,
846 -- p_comp_visit_flag VARCHAR2
847 --
848 -- End of Comments
849 -------------------------------------------------------------------------------------
850 FUNCTION Get_Scheduled_Quantity (
851 p_item_id IN NUMBER,
852 p_org_id IN NUMBER,
853 p_start_date IN DATE,
854 p_end_date IN DATE,
855 p_comp_visit_flag VARCHAR2
856 ) RETURN NUMBER
857 IS
858 l_sched_qty NUMBER := 0;
859 l_instance_qty NUMBER := 0;
860
861 /* Cursor to get the total workorder quantity for each instance
862 * RTS flag = 'Y', given the item ID
863 * , organization ID, scheduled start date and end date
864 */
865 CURSOR c_get_task_instance_qty(p_item_id NUMBER,
866 p_org_id NUMBER,
867 p_start_date DATE,
868 p_end_date DATE,
869 p_comp_visit_flag VARCHAR2) IS
870 SELECT SUM(task.quantity) sched_qty FROM ahl_visit_tasks_b task,
871 ahl_workorders wo,ahl_visits_b visit,WIP_DISCRETE_JOBS WDJ
872 WHERE
873 wo.VISIT_TASK_ID = task.VISIT_TASK_ID
874 AND WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
875 AND WO.STATUS_CODE IN (1,3,6,17,19,20)
876 AND task.VISIT_ID = visit.visit_id
877 AND task.inventory_item_id = p_item_id
878 AND visit.organization_id = p_org_id
879 AND return_to_supply_flag = 'Y'
880 AND (p_comp_visit_flag IS NULL OR
881 AHL_CMP_UTIL_PKG.Is_Comp_Visit(task.visit_id) = p_comp_visit_flag)
882 AND WDJ.scheduled_completion_date >=
883 NVL(p_start_date,WDJ.scheduled_completion_date)
884 AND WDJ.scheduled_completion_date <=
885 NVL(p_end_date,WDJ.scheduled_completion_date)
886 AND task_type_code NOT IN ('SUMMARY')
887 GROUP BY
888 task.instance_id;
889 l_task_instance_qty_rec c_get_task_instance_qty%ROWTYPE;
890
891 BEGIN
892 FOR l_task_instance_qty_rec IN c_get_task_instance_qty(p_item_id,
893 p_org_id, p_start_date, p_end_date, p_comp_visit_flag)
894 LOOP
895 l_instance_qty := l_task_instance_qty_rec.sched_qty;
896
897 l_sched_qty := l_sched_qty + l_instance_qty;
898
899 END LOOP;
900
901
902 RETURN l_sched_qty;
903
904 END Get_Scheduled_Quantity;
905
906 ------------------------------------------------------------------------------------------------
907 -- Start of Comments
908 -- Function name : Are_All_Workorders_Complete
909 -- Type : Public
910 -- Function : Function to check whether all the Workorders associated to a given Repair Batch and for a given instance are Completed.
911 -- If no value is passed for instance, then we look at all the instances of that Repair Batch.
912 -- If no value is passed for repair batch, then we retrieve the repair batch from the instance given.
913 -- Pre-reqs :
914 -- Parameters :
915 --
916 -- Are_All_Workorders_Complete Parameters:
917 -- p_repair_batch IN Given Repair Batch. Optional.
918 -- p_instance_id IN Given Instance Optional.
919 -- p_org_id IN Given Org Required.
920 --
921 -- End of Comments
922 -----------------------------------------------------------------------------------------------
923 FUNCTION Are_All_Workorders_Complete (
924 p_repair_batch IN VARCHAR2,
925 p_instance_id IN NUMBER,
926 p_org_id IN NUMBER
927 ) RETURN VARCHAR2
928 IS
929
930 l_result VARCHAR2(1) := 'Y';
931 l_dummy VARCHAR2(1);
932
933 l_repair_batch VARCHAR2(240);
934
935 -- Cursor to check if all the workorders associated to a given repair batch are Complete.
936 CURSOR Chk_Complete_Workorders(c_repair_batch VARCHAR2, c_ins_id NUMBER)
937 IS
938 SELECT 'x'
939 FROM AHL_WORKORDERS
940 WHERE status_code NOT IN ('4','12','7','5','17' ) -- Complete,Closed,Cancelled,Complete-No-Charge and Draft
941 AND visit_task_id IN
942 (SELECT visit_task_id
943 FROM AHL_VISIT_TASKS_B vt
944 WHERE vt.cost_parent_id IS NOT NULL
945 AND vt.instance_id = nvl(c_ins_id,vt.instance_id)
946 START WITH vt.visit_task_id = (SELECT visit_task_id
947 FROM AHL_VISIT_TASKS_B
948 WHERE repair_batch_name = c_repair_batch)
949 CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id );
950
951
952 BEGIN
953
954 IF p_repair_batch IS NULL THEN
955 l_repair_batch := Get_Rpr_Batch_For_Inst(p_instance_id, p_org_id);
956 OPEN Chk_Complete_Workorders(l_repair_batch, p_instance_id );
957 ELSE
958 OPEN Chk_Complete_Workorders(p_repair_batch, p_instance_id );
959 END IF;
960
961 FETCH Chk_Complete_Workorders INTO l_dummy;
962 IF(Chk_Complete_Workorders%FOUND) THEN
963 l_result := 'N';
964 END IF;
965 CLOSE Chk_Complete_Workorders;
966
967 RETURN l_result;
968 END Are_All_Workorders_Complete;
969 ---------------------------------------------------------------------------------------
970
971 -----------------------------------------------------------------------------------------------------
972 -- Start of Comments
973 -- Function name : Get_Rpr_Qty
974 -- Type : Public
975 -- Function : Function to retrieve the repair quantity for a given Repair Batch
976 --
977 -- Pre-reqs :
978 -- Parameters :
979 --
980 -- Get_Rpr_Qty Parameters:
981 -- p_repair_batch IN Given Repair Batch. Required
982 --
983 -- End of Comments
984 ------------------------------------------------------------------------------------------------------
985 FUNCTION Get_Rpr_Qty (
986 p_repair_batch IN VARCHAR2
987 ) RETURN NUMBER
988 IS
989
990 l_repair_qty NUMBER := 0;
991 l_serviceable_qty NUMBER := 0;
992 l_unserviceable_qty NUMBER := 0;
993
994
995 --Cursor to fetch the serviceable and unserviceable quantities for a given repair batch
996 CURSOR rpr_batch_quantities_csr(p_repair_batch_csr VARCHAR2) IS
997 SELECT nvl(ret_serviceable_qty, 0) serv_qty, nvl(ret_unserviceable_qty,0) unserv_qty FROM
998 ahl_visit_tasks_b task WHERE repair_batch_name = p_repair_batch_csr;
999
1000 BEGIN
1001
1002
1003 OPEN rpr_batch_quantities_csr(p_repair_batch);
1004 FETCH rpr_batch_quantities_csr INTO l_serviceable_qty, l_unserviceable_qty;
1005 CLOSE rpr_batch_quantities_csr;
1006
1007 l_repair_qty := AHL_CMP_UTIL_PKG.Get_Batch_Qty(p_repair_batch) + l_serviceable_qty + l_unserviceable_qty;
1008
1009 RETURN l_repair_qty;
1010
1011 END Get_Rpr_Qty;
1012 -----------------------------------------------------------------------------------------------------------
1013
1014 END AHL_CMP_UTIL_PKG;
[Home] [Help]