1 PACKAGE BODY AHL_PRD_UTIL_PKG AS
2 /* $Header: AHLUPRDB.pls 120.16.12000000.2 2007/10/06 00:22:14 sikumar ship $ */
3
4 -- Purpose: Briefly explain the functionality of the package body
5 -- Contains common utility procedures to be used by parts change and material transactions.
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 --Validates locator for a given organization. Also if subinventory is provided then chekcs if the loctaor belongs to the
10 -- subinventory.
11 PROCEDURE validate_locators
12 ( p_locator_id IN number,
13 p_org_id IN number,
14 p_subinventory_code in varchar2,
15 X_Return_Status Out NOCOPY Varchar2,
16 X_Msg_Data Out NOCOPY Varchar2
17 )
18 AS
19 l_subinv_code varchar2(20):= null;
20 CURSOR ahl_locator_csr(p_org_id number, p_locator_id number) is
21 select subinventory_code
22 from mtl_item_locations_kfv
23 where organization_id = p_org_id
24 and inventory_location_id = p_locator_id
25 and nvl(disable_Date, sysdate) >= sysdate;
26
27 BEGIN
28 -- Initialize API return status to success
29 x_return_status := FND_API.G_RET_STS_SUCCESS;
30 -- test if subinventory si null;
31 if (p_subinventory_code is null ) then
32
33 FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_PC_SUBINV_MANDATORY');
34
35 FND_MSG_PUB.ADD;
36 RAISE FND_API.G_EXC_ERROR;
37 end if;
38
39 -- Commented the following validation as locator id need not exist in
40 -- mtl_item_locations if it is setup as dynamic entry.
41
42 -- OPEN ahl_locator_csr(p_Org_Id,p_locator_id);
43 -- FETCH ahl_locator_csr INTO l_subinv_code;
44 -- CLOSE ahl_locator_csr;
45
46
47 /* if (l_subinv_code is null) then
48 x_return_status := FND_API.G_RET_STS_ERROR;
49 FND_MESSAGE.Set_Name('AHL','AHL_PRD_LOCATOR_INVALID');
50 FND_MSG_PUB.ADD;
51
52 END if;*/
53
54 --Check if the locator is attached to the subinventory if subinventory is provided
55 -- if (l_subinv_code is not null
56 -- and l_subinv_code <> p_subinventory_code ) then
57 -- x_return_status := FND_API.G_RET_STS_ERROR;
58 -- FND_MESSAGE.Set_Name('AHL','AHL_PRD_LOCATOR_SUBINV_INVALID');
59 -- FND_MSG_PUB.ADD;
60
61 -- END if;
62 EXCEPTION
63 -- WHEN NO_DATA_FOUND THEN
64 -- x_return_status := FND_API.G_RET_STS_ERROR;
65 -- FND_MESSAGE.Set_Name('AHL','AHL_PRD_LOCATOR_INVALID');
66 -- FND_MSG_PUB.ADD;
67
68
69 WHEN FND_API.G_EXC_ERROR THEN
70 x_return_status := FND_API.G_RET_STS_ERROR;
71 WHEN OTHERS THEN
72 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
73 END;
74 --Validates a removal condition
75 procedure validate_condition
76 (
77 p_condition_id In number,
78 x_return_status out NOCOPY varchar2,
79 x_msg_data out NOCOPY varchar2
80
81 )
82 AS
83
84 l_junk varchar2(1):= null;
85
86 CURSOR ahl_condition_csr (p_condn_id number) IS
87 select 'x'
88 from mtl_material_statuses
89 where status_id=p_condn_id
90 and enabled_flag =1;
91 BEGIN
92
93 -- Initialize API return status to success
94 x_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 OPEN ahl_condition_csr(p_condition_id);
97 FETCH ahl_condition_csr INTO l_junk;
98 CLOSE ahl_condition_csr;
99
100 if (l_junk is null) then
101 x_return_status := FND_API.G_RET_STS_ERROR;
102 FND_MESSAGE.Set_Name('AHL','AHL_PRD_PC_CONDN_MISSING');
103 FND_MSG_PUB.ADD;
104 END if;
105 END;
106
107 --Validates a removal reason.
108 procedure validate_reason
109 (
110 p_reason_id In number,
111 x_return_status out NOCOPY varchar2,
112 x_msg_data out NOCOPY varchar2
113
114 )
115
116 AS
117 CURSOR ahl_reason_csr (p_reason_id number) IS
118 select 'x'
119 from MTL_TRANSACTION_REASONS
120 where reason_id=p_reason_id
121 and nvl(disable_date, sysdate) >= sysdate;
122
123 l_junk varchar2(1) := null;
124 begin
125 -- Initialize API return status to success
126 x_return_status := FND_API.G_RET_STS_SUCCESS;
127
128 OPEN ahl_reason_csr(p_reason_id);
129 FETCH ahl_reason_csr INTO l_junk;
130 CLOSE ahl_reason_csr;
131
132
133 if (l_junk is null) then
134 x_return_status := FND_API.G_RET_STS_ERROR;
135 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_REASON');
136 FND_MESSAGE.Set_Token('REASON',p_reason_id);
137
138 FND_MSG_PUB.ADD;
139 END if;
140 end;
141
142
143 /********************************************************
144 This procedure checks the condition of the item and validates
145 if the sub inventory is valid for this codnition.
146 *********************************************************/
147
148
149 PROCEDURE VALIDATE_MATERIAL_STATUS(p_Organization_Id IN NUMBER,
150 p_Subinventory_Code IN VARCHAR2,-- not null
151 p_Condition_id IN NUMBER,-- null/not null
152 x_return_status OUT NOCOPY VARCHAR2
153 )
154 IS
155 l_status_id NUMBER;
156 --for inventory sttaus
157 CURSOR ahl_inv_status_csr(p_org_id number, p_subinv_code varchar2) is
158 SELECT status_id
159 FROM MTL_SECONDARY_INVENTORIES
160 WHERE ORGANIZATION_ID = p_org_Id
161 AND SECONDARY_INVENTORY_NAME = p_Subinv_Code;
162
163 BEGIN
164
165
166
167 -- Initialize API return status to success
168 x_return_status := FND_API.G_RET_STS_SUCCESS;
169
170 OPEN ahl_inv_status_csr(p_Organization_Id,p_Subinventory_Code);
171 FETCH ahl_inv_status_csr INTO l_status_id;
172 CLOSE ahl_inv_status_csr;
173
174 if l_status_id is null THEN
175 FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_SUBINVENTORY');
176 FND_MESSAGE.set_token('INV', p_Subinventory_Code);
177 FND_MSG_PUB.ADD;
178 x_return_status := FND_API.G_RET_STS_ERROR;
179
180 End if;
181 --dbms_output.put_line('ahl_prd_util_pkg - Condition id'|| p_condition_id);
182 -- dbms_output.put_line('ahl_prd_util_pkg - status id'|| l_status_id);
183 --dbms_output.put_line('ahl_prd_util_pkg - org id'|| p_organization_id);
184 if (p_condition_id is not null
185 and
186 ( (FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_SERVICABLE')is not null and
187 p_condition_id = FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_SERVICABLE'))
188 OR
189 ( FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_UNSERVICABLE') is not null and
190 p_condition_id=FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_UNSERVICABLE'))
191 OR
192 (FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_MRB')is not null and
193 p_condition_id = FND_PROFILE.VALUE('AHL_MTL_MAT_STATUS_MRB'))
194 )
195 AND
196 p_condition_id <> l_status_id ) then
197
198
199 FND_MESSAGE.Set_Name('AHL','AHL_PRD_CONDN_SUBINV_MISMATCH');
200 FND_MSG_PUB.ADD;
201 x_return_status := FND_API.G_RET_STS_ERROR;
202
203 END IF;
204
205 END ;--VALIDATE_MATERIAL_STATUS;
206
207 ------------------------------------------------------------------------------------------------
208 -- Function to test if the Unit in context is locked or not. The input to the API can be one of
209 -- workorder_id, mr_id, visit_id or item_instance_id.
210 ------------------------------------------------------------------------------------------------
211 FUNCTION Is_Unit_Locked(
212 P_workorder_id IN NUMBER,
213 P_ue_id IN NUMBER,
214 P_visit_id IN NUMBER,
215 P_item_instance_id IN NUMBER
216 )
217 RETURN VARCHAR2
218 IS
219 /*
220 * Cursor to get workorder_ids of a given visit.
221 */
222 CURSOR c_get_visit_workorders(p_visit_id IN NUMBER)
223 IS
224 SELECT
225 workorder_id
226 FROM
227 ahl_workorders
228 WHERE
229 visit_id = p_visit_id AND
230 status_code not in (22, 7, 17) AND
231 master_workorder_flag <> 'Y';
232 /*
233 * Cursor for getting item details from a workorder.
234 * Detailed are retrieved by joining workorder and visit tables.
235 * If the visit header has item instance info then use that else
236 * get the info from visit task tables.
237 */
238 CURSOR c_get_wo_item_details(p_workorder_id IN NUMBER)
239 IS
240 SELECT
241 vst.item_instance_id,
242 vtk.instance_id
243 FROM
244 ahl_visit_tasks_b vtk,
245 ahl_visits_b vst,
246 ahl_workorders wo
247 WHERE
248 wo.workorder_id = p_workorder_id AND
249 vtk.visit_task_id = wo.visit_task_id AND
250 vtk.visit_id = vst.visit_id;
251
252 /*
253 * Cursor for getting csi_item_instance_id given mr_id.
254 */
255 CURSOR c_get_ue_instance_id(p_ue_id IN NUMBER)
256 IS
257 SELECT
258 csi_item_instance_id
259 FROM
260 AHL_UNIT_EFFECTIVITIES_B
261 WHERE
262 unit_effectivity_id = p_ue_id;
263
264 -- declare all local variables here
265 l_item_instance_id NUMBER;
266 l_instance_id NUMBER;
267
268 BEGIN
269 -- Check if item_instance_id is input to the API.
270 IF p_item_instance_id IS NOT NULL
271 THEN
272 RETURN AHL_UTIL_UC_PKG.Is_Unit_Quarantined(
273 p_unit_header_id => null,
274 p_instance_id => p_item_instance_id
275 );
276 -- If visit_id is input
277 ELSIF p_visit_id IS NOT NULL
278 THEN
279 -- get all visit workorders
280 FOR vst_wos IN c_get_visit_workorders(p_visit_id)
281 LOOP
282 -- for each workorder get item instance info.
283 OPEN c_get_wo_item_details(vst_wos.workorder_id);
284 FETCH c_get_wo_item_details INTO l_item_instance_id, l_instance_id;
285 CLOSE c_get_wo_item_details;
286
287 -- If visit header has item instance info.
288 IF l_instance_id IS NOT NULL
289 THEN
290 IF AHL_UTIL_UC_PKG.Is_Unit_Quarantined(
291 p_unit_header_id => null,
292 p_instance_id => l_instance_id
293 ) = FND_API.G_TRUE
294 THEN
295 RETURN FND_API.G_TRUE;
296 END IF;
297
298 ELSE
299 -- If visit task has item instance info.
300 RETURN AHL_UTIL_UC_PKG.Is_Unit_Quarantined(
301 p_unit_header_id => null,
302 p_instance_id => l_item_instance_id
303 );
304 END IF;
305 END LOOP;
306
307 RETURN FND_API.G_FALSE;
308 -- if mr header id is input to the API.
309 ELSIF p_ue_id IS NOT NULL
310 THEN
311 OPEN c_get_ue_instance_id(p_ue_id);
312 FETCH c_get_ue_instance_id INTO l_instance_id;
313 CLOSE c_get_ue_instance_id;
314 RETURN AHL_UTIL_UC_PKG.Is_Unit_Quarantined(
315 p_unit_header_id => null,
316 p_instance_id => l_instance_id
317 );
318 -- If workorder id is input to the API.
319 ELSIF p_workorder_id IS NOT NULL
320 THEN
321 OPEN c_get_wo_item_details(p_workorder_id);
322 FETCH c_get_wo_item_details INTO l_item_instance_id, l_instance_id;
323 CLOSE c_get_wo_item_details;
324 RETURN AHL_UTIL_UC_PKG.Is_Unit_Quarantined(
325 p_unit_header_id => null,
326 p_instance_id => nvl(l_instance_id, l_item_instance_id) );
327 END IF;
328
329 -- Control will reach here if all inputs to the API are null. return false then.
330 RETURN FND_API.G_FALSE;
331
332 END Is_Unit_Locked;
333
334 ------------------------------------------------------------------------------------------------
335 -- Function to test if the workorder can be updated.
336 -- Determined based on following factors
337 -- 1. If the unit is quarantined then it cannot be updated.
338 -- 2. If the workorder status is any of 22, 12 and 7 then it cannot be updated.
339 ------------------------------------------------------------------------------------------------
340 FUNCTION Is_Wo_Updatable(
341 P_workorder_id IN NUMBER,
342 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
343 )
344 RETURN VARCHAR2
345 IS
346 /*
347 * Cursor for selecting workorder status validity.
348 */
349 CURSOR c_validate_wo_status(p_workorder_id IN NUMBER) IS
350 SELECT
351 'X'
352 FROM
353 ahl_workorders
354 WHERE
355 workorder_id = p_workorder_id AND
356 status_code in (7, 12, 22);
357
358 -- declare local variables here
359 l_exists VARCHAR2(1);
360
361 BEGIN
362 -- If workorder_id is null then immediately return false.
363 IF p_workorder_id IS NULL
364 THEN
365 RETURN FND_API.G_FALSE;
366 END IF;
367
368 -- Check if the unit is locked... If so then return false to indicate
369 -- the workorder can't be updated.
370 IF p_check_unit = FND_API.G_TRUE THEN
371 IF Is_Unit_Locked(
372 p_workorder_id => p_workorder_id,
373 P_ue_id => null,
374 P_visit_id => null,
375 P_item_instance_id => null
376 ) = FND_API.G_TRUE
377 THEN
378 RETURN FND_API.G_FALSE;
379 END IF;
380 END IF;
381
382 -- If the unit is not locked and the workorder is in any of
383 -- 7, 12 or 22 then return false.
384 OPEN c_validate_wo_status(p_workorder_id);
385 FETCH c_validate_wo_status INTO l_exists;
386 CLOSE c_validate_wo_status;
387
388 IF l_exists IS NOT NULL
389 THEN
390 RETURN FND_API.G_FALSE;
391 END IF;
392
393 -- If this point is reached then neither the unit is locked nor the workorder is
394 -- in invalid status. Return true here.
395 RETURN FND_API.G_TRUE;
396
397 END Is_Wo_Updatable;
398
399 ------------------------------------------------------------------------------------------------
400 -- Function to test if the workorder operation can be updated.
401 -- Determined based on following factors
402 -- 1. If the unit associated with the workorder to which the operation belongs is quarantined
403 -- then it cannot be updated.
404 -- 2. If the workorder status is any of 22, 12 and 7 then it cannot be updated.
405 -- 3. If the operation status is 'COMPLETE' then it cannot be updated
406 ------------------------------------------------------------------------------------------------
407 FUNCTION Is_Op_Updatable(
408 p_workorder_id IN NUMBER,
409 p_operation_seq_num IN NUMBER,
410 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
411 )
412 RETURN VARCHAR2
413 IS
414
415 /*
416 * Cursor for selecting workorder status validity.
417 */
418 CURSOR c_validate_wo_status(p_workorder_id IN NUMBER)
419 IS
420 SELECT
421 'X'
422 FROM
423 ahl_workorders
424 WHERE
425 workorder_id = p_workorder_id AND
426 status_code in (4, 5, 7, 12, 22);
427 /*
428 * Cursor for checking workorder operation status validity
429 */
430 CURSOR c_validate_op_status(p_workorder_id IN NUMBER, p_op_seq_no IN NUMBER)
431 IS
432 SELECT
433 'X'
434 FROM
435 ahl_workorder_operations
436 WHERE
437 workorder_id = p_workorder_id AND
438 operation_sequence_num = p_op_seq_no AND
439 status_code = 1;
440
441 -- declare local variables here
442 l_exists VARCHAR2(1);
443
444 BEGIN
445 -- If either of workorder_id or operation sequence number is null then
446 -- return false as it is incorrect input to this API.
447 IF p_workorder_id IS NULL OR p_operation_seq_num IS NULL
448 THEN
449 RETURN FND_API.G_FALSE;
450 END IF;
451
452 -- If the unit associated with this workorder is locked then
453 -- operation should not be updatable.
454 IF p_check_unit = FND_API.G_TRUE THEN
455 IF Is_Unit_Locked(
456 p_workorder_id => p_workorder_id,
457 P_ue_id => null,
458 P_visit_id => null,
459 P_item_instance_id => null
460 ) = FND_API.G_TRUE
461 THEN
462 RETURN FND_API.G_FALSE;
463 END IF;
464 END IF;
465
466 -- If the workorder status is any of 7, 12 or 22 then return false.
467 OPEN c_validate_wo_status(p_workorder_id);
468 FETCH c_validate_wo_status INTO l_exists;
469 CLOSE c_validate_wo_status;
470 IF l_exists IS NOT NULL
471 THEN
472 RETURN FND_API.G_FALSE;
473 END IF;
474
475 -- If the operation status is 'Complete' then return false
476 -- to indicate the operation is not updatable.
477 OPEN c_validate_op_status(p_workorder_id, p_operation_seq_num);
478 FETCH c_validate_op_status INTO l_exists;
479 IF l_exists IS NOT NULL
480 THEN
481 RETURN FND_API.G_FALSE;
482 END IF;
483
484 -- Control reaching here indicates above three checks are not valid for
485 -- the input hence return true to indicate the operation is updatable.
486 RETURN FND_API.G_TRUE;
487
488 END Is_Op_Updatable;
489
490 ------------------------------------------------------------------------------------------------
491 -- Function to determine if a MR requires Quality collection to be done.
492 -- (Whether QA Collection is required)
493 -- The function returns QA Plan id or null if one is not associated with the MR.
494 ------------------------------------------------------------------------------------------------
495 FUNCTION Is_Mr_Qa_Enabled(
496 p_ue_id IN NUMBER,
497 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
498 )
499 RETURN NUMBER
500 IS
501
502 /*
503 * Cursor to retrieve mr_header_id and qa_collection_id from ue_effectivities table.
504 */
505 CURSOR c_get_mr_details(p_ue_id IN NUMBER)
506 IS
507 SELECT
508 mr_header_id,
509 qa_collection_id
510 FROM
511 AHL_UNIT_EFFECTIVITIES_B
512 WHERE
513 unit_effectivity_id = p_ue_id;
514
515 /*
516 * Cursor to retrieve plan id given qa collection id.
517 */
518 CURSOR c_get_plan_from_col(p_qa_collection_id IN NUMBER)
519 IS
520 SELECT
521 DISTINCT plan_id
522 FROM
523 QA_RESULTS
524 WHERE
525 collection_id = p_qa_collection_id;
526
527 /*
528 * Cursor to retrieve mr_qa_inspection_type given mr_header_id
529 */
530 CURSOR c_get_mr_insp_type(p_mr_header_id IN NUMBER)
531 IS
532 SELECT
533 qa_inspection_type
534 FROM
535 AHL_MR_HEADERS_B
536 WHERE
537 mr_header_id = p_mr_header_id;
538
539 /*
540 * Cursor for getting qa plan id from MR qa_inspection_type and ue_id.
541 */
542 CURSOR c_get_plan_from_insp_type(p_qa_insp_type IN VARCHAR2, p_ue_id IN NUMBER)
543 IS
544 SELECT
545 QP.PLAN_ID
546 FROM
547 QA_PLANS_VAL_V QP,
548 QA_PLAN_TRANSACTIONS QPT,
549 QA_PLAN_COLLECTION_TRIGGERS QPCT
550 WHERE
551 QP.PLAN_ID = QPT.PLAN_ID AND
552 QPT.PLAN_TRANSACTION_ID = QPCT.PLAN_TRANSACTION_ID AND
553 QP.ORGANIZATION_ID = (SELECT
554 ORGANIZATION_ID
555 FROM
556 AHL_VISITS_B VST,
557 AHL_VISIT_TASKS_B TSK
558 WHERE VST.VISIT_ID = TSK.VISIT_ID AND
559 TSK.UNIT_EFFECTIVITY_ID = p_ue_id AND
560 ROWNUM < 2) AND
561 QPT.TRANSACTION_NUMBER IN (9999,2001) AND
562 QPCT.COLLECTION_TRIGGER_ID = 87 AND
563 QPCT.LOW_VALUE = p_qa_insp_type
564 GROUP BY
565 QP.PLAN_ID,
566 QPT.TRANSACTION_NUMBER
567 HAVING
568 TRANSACTION_NUMBER = MAX(TRANSACTION_NUMBER);
569
570 --declare all local variables here
571 l_mr_id NUMBER;
572 l_qa_collection_id NUMBER;
573 l_plan_id NUMBER;
574 l_qa_insp_type VARCHAR2(150);
575
576 BEGIN
577 -- If ue id which is only input to this API is null then return null.
578 IF p_ue_id IS NULL
579 THEN
580 RETURN NULL;
581 END IF;
582
583 -- retrieve mr_header_id and qa_collection_id for the given ue.
584 OPEN c_get_mr_details(p_ue_id);
585 FETCH c_get_mr_details INTO l_mr_id, l_qa_collection_id;
586 CLOSE c_get_mr_details;
587
588 -- If mr_header_id is null dont proceed further and return null.
589 IF l_mr_id IS NULL
590 THEN
591 RETURN NULL;
592 END IF;
593
594 -- If the unit associated with this workorder is locked then
595 -- operation should not be updatable.
596 IF p_check_unit = FND_API.G_TRUE THEN
597 IF Is_Unit_Locked(
598 p_workorder_id => null,
599 P_ue_id => l_mr_id,
600 P_visit_id => null,
601 P_item_instance_id => null
602 ) = FND_API.G_TRUE
603 THEN
604 RETURN NULL;
605 END IF;
606 END IF;
607
608 -- retrieve plan_id for information gathered above.
609 IF l_qa_collection_id IS NOT NULL
610 THEN
611 -- Get qa_plan_id from qa_collection_id
612 OPEN c_get_plan_from_col(l_qa_collection_id);
613 FETCH c_get_plan_from_col INTO l_plan_id;
614 CLOSE c_get_plan_from_col;
615 ELSE
616 -- Get qa_inspection_type from mr header.
617 OPEN c_get_mr_insp_type(l_mr_id);
618 FETCH c_get_mr_insp_type INTO l_qa_insp_type;
619 CLOSE c_get_mr_insp_type;
620
621 -- retrieve qa_plan_id from qa_inspection_type and ue_id.
622 IF l_qa_insp_type IS NOT NULL
623 THEN
624 OPEN c_get_plan_from_insp_type(l_qa_insp_type, p_ue_id);
625 FETCH c_get_plan_from_insp_type INTO l_plan_id;
626 CLOSE c_get_plan_from_insp_type;
627 END IF;
628 END IF;
629
630 -- return plan_id collected. could be null also if no plan is associated.
631 RETURN l_plan_id;
632
633 END Is_Mr_Qa_Enabled;
634
635
636 ------------------------------------------------------------------------------------------------
637 -- Function to determine if parts changes are allowed for a workorder
638 -- 1. If the unit is quarantined then part changes are not allowed.
639 -- 2. If the workorder status is anything other than Released (3) or On Parts Hold (19) then part changes
640 -- are not allowed.
641 ------------------------------------------------------------------------------------------------
642 FUNCTION Is_PartChange_Enabled(
643 P_workorder_id IN NUMBER,
644 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
645 )
646 RETURN VARCHAR2
647 IS
648 /*
649 * Cursor for getting the status of the workorder id passed as input to this API.
650 * workorder status mapping
651 * ------------------------
652 * 3 Released
653 * 19 on parts hold
654 */
655 CURSOR c_get_workorder_details(p_workorder_id IN NUMBER)
656 IS
657 SELECT AWOS.STATUS_CODE,
658 NVL(VTS.INSTANCE_ID, VST.ITEM_INSTANCE_ID)
659 FROM
660 AHL_WORKORDERS AWOS,
661 AHL_VISITS_B VST,
662 AHL_VISIT_TASKS_B VTS
663 WHERE
664 AWOS.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
665 VST.VISIT_ID = VTS.VISIT_ID AND
666 WORKORDER_ID = p_workorder_id;
667
668 -- declare local variables here
669 l_status_code VARCHAR2(30);
670 l_item_instance_id NUMBER;
671
672 l_unit_config_name ahl_unit_config_headers.name%TYPE;
673 l_unit_config_id NUMBER;
674 l_return_status VARCHAR2(1);
675 BEGIN
676 -- If sole input to this API p_workorder_id is null then return false
677 IF p_workorder_id IS NULL
678 THEN
679 RETURN FND_API.G_FALSE;
680 END IF;
681
682 -- If the Unit is locked then part changes cannot be done hence return false
683 IF p_check_unit = FND_API.G_TRUE THEN
684 IF Is_Unit_Locked(
685 p_workorder_id => p_workorder_id,
686 P_ue_id => null,
687 P_visit_id => null,
688 P_item_instance_id => null
689 ) = FND_API.G_TRUE THEN
690 RETURN FND_API.G_FALSE;
691 END IF;
692 END IF;
693
694 -- If workorder is in an invalid status where part changes cannot be done then
695 -- return false;
696 OPEN c_get_workorder_details(p_workorder_id);
697 FETCH c_get_workorder_details INTO l_status_code, l_item_instance_id;
698 CLOSE c_get_workorder_details;
699
700 IF l_status_code NOT IN ('3', '19') THEN
701 RETURN FND_API.G_FALSE;
702 END IF;
703
704 AHL_PRD_PARTS_CHANGE_PVT.get_unit_config_information(
705 p_item_instance_id => l_item_instance_id,
706 p_workorder_id => null,
707 x_unit_config_id => l_unit_config_id,
708 x_unit_config_name => l_unit_config_name,
709 x_return_status => l_return_status);
710 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
711 RETURN FND_API.G_FALSE;
712 END IF;
713
714 -- All above conditions are false hence part changes can be done. return true.
715 RETURN FND_API.G_TRUE;
716
717 END Is_PartChange_Enabled;
718
719
720 ------------------------------------------------------------------------------------------------
721 -- Function to check if resource assignment should be allowed. The logic is based on following
722 -- factors :
723 -- 1. The unit is quarantined.
724 -- 2. A user is currently logged into the resource assignment.
725 -- 3. Resource transactions have been posted corresponding to this resource assignment.
726 ------------------------------------------------------------------------------------------------
727 FUNCTION IsDelAsg_Enabled(
728 P_assignment_id IN NUMBER,
729 P_workorder_id IN NUMBER,
730 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
731 )
732 RETURN VARCHAR2
733 IS
734
735 /*
736 * Cursor for getting resource assignment details given assingment id.
737 */
738 CURSOR c_get_ass_details(p_assignment_id IN NUMBER)
739 IS
740 SELECT
741 WREQ.operation_sequence,
742 WREQ.resource_sequence
743 FROM
744 AHL_WORK_ASSIGNMENTS WASS,
745 AHL_PP_REQUIREMENT_V WREQ
746 WHERE
747 WASS.assignment_id = p_assignment_id AND
748 WREQ.resource_id = WASS.operation_resource_id AND
749 login_date IS NOT NULL;
750
751 -- declare local variables here
752 --l_login_date DATE;
753 l_op_seq_num NUMBER;
754 l_res_seq_num NUMBER;
755
756 BEGIN
757
758 -- If sole input to this API p_workorder_id is null then return false
759 IF P_assignment_id IS NULL
760 THEN
761 RETURN FND_API.G_FALSE;
762 END IF;
763 -- If the Unit is locked then Assignment cant be deleted return false.
764 IF p_check_unit = FND_API.G_TRUE THEN
765 IF Is_Unit_Locked(
766 p_workorder_id => p_workorder_id,
767 P_ue_id => null,
768 P_visit_id => null,
769 P_item_instance_id => null
770 ) = FND_API.G_TRUE
771 THEN
772 Fnd_Message.Set_Name('AHL', 'AHL_PP_DEL_RESASG_UNTLCKD');
773 Fnd_Msg_Pub.ADD;
774 RETURN FND_API.G_FALSE;
775 END IF;
776 END IF;
777
778 OPEN c_get_ass_details(p_assignment_id);
779 FETCH c_get_ass_details INTO l_op_seq_num, l_res_seq_num;
780
781 IF c_get_ass_details%FOUND
782 THEN
783 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_DELASG_LOGDIN');
784 FND_MESSAGE.set_token( 'OP_SEQ' , l_op_seq_num);
785 FND_MESSAGE.set_token( 'RES_SEQ' , l_res_seq_num);
786 Fnd_Msg_Pub.ADD;
787
788 CLOSE c_get_ass_details;
789 RETURN FND_API.G_FALSE;
790 END IF;
791 CLOSE c_get_ass_details;
792
793
794 /*IF l_login_date IS NOT NULL
795 THEN
796 -- the user is currently logged in corresponding to this resource assignment
797 -- the resource assignment cannot be deleted
798 RETURN FND_API.G_FALSE;
799 END IF;
800 */
801
802 RETURN FND_API.G_TRUE;
803
804 END IsDelAsg_Enabled;
805
806 FUNCTION Is_Wo_Completable(
807 P_workorder_id IN NUMBER
808 )
809 RETURN VARCHAR2
810 IS
811 /*
812 * Cursor for selecting workorder status validity.
813 */
814 CURSOR c_validate_wo_status(p_workorder_id IN NUMBER) IS
815 SELECT
816 'X'
817 FROM
818 ahl_workorders
819 WHERE
820 workorder_id = p_workorder_id AND
821 status_code in (1,4,5,7, 12,21, 22);
822
823 /*
824 * Cursor for selecting operation status validity.
825 */
826 CURSOR c_validate_op_status(p_workorder_id IN NUMBER) IS
827 SELECT
828 'X'
829 FROM
830 ahl_workorder_operations
831 WHERE
832 workorder_id = p_workorder_id AND
833 status_code in (0,2);
834
835 -- declare local variables here
836 l_exists VARCHAR2(1);
837
838 BEGIN
839 -- If workorder_id is null then immediately return false.
840 IF p_workorder_id IS NULL
841 THEN
842 RETURN FND_API.G_FALSE;
843 END IF;
844
845 OPEN c_validate_wo_status(p_workorder_id);
846 FETCH c_validate_wo_status INTO l_exists;
847 CLOSE c_validate_wo_status;
848
849 IF l_exists IS NOT NULL
850 THEN
851 RETURN FND_API.G_FALSE;
852 END IF;
853
854 OPEN c_validate_op_status(p_workorder_id);
855 FETCH c_validate_op_status INTO l_exists;
856 CLOSE c_validate_op_status;
857
858 IF l_exists IS NOT NULL
859 THEN
860 RETURN FND_API.G_FALSE;
861 END IF;
862
863 -- If this point is reached then neither the unit is locked nor the workorder is
864 -- in invalid status. Return true here.
865 RETURN FND_API.G_TRUE;
866
867 END Is_Wo_Completable;
868
869 ------------------------------------------------------------------------------------------------
870 -- Function to test if resource transactions are allowed for a workorder
871 ------------------------------------------------------------------------------------------------
872 -- Start of Comments
873 -- Function name : Is_ResTxn_Allowed
874 -- Type : Private
875 -- Pre-reqs :
876 -- Parameters :
877 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
878 --
879 -- Standard IN Parameters :
880 -- None
881 --
882 -- Standard OUT Parameters :
883 -- None
884 --
885 -- Is_Unit_Locked IN parameters:
886 -- P_workorder_id NUMBER Required
887 --
888 -- Is_Unit_Locked IN OUT parameters:
889 -- None
890 --
891 -- Is_Unit_Locked OUT parameters:
892 -- None.
893 --
894 -- Version :
895 -- Current version 1.0
896 --
897 -- End of Comments
898
899 FUNCTION Is_ResTxn_Allowed(
900 P_workorder_id IN NUMBER,
901 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
902 )
903 RETURN VARCHAR2
904 IS
905 -- cursor to retrieve the workorder status
906 CURSOR get_wo_status (c_wo_id NUMBER)
907 IS
908 SELECT STATUS_CODE
909 FROM AHL_WORKORDERS
910 WHERE WORKORDER_ID = c_wo_id;
911
912 l_return_value VARCHAR2(1);
913 l_wo_status VARCHAR2(30);
914 BEGIN
915
916 l_return_value := FND_API.G_FALSE;
917 IF p_check_unit = FND_API.G_TRUE THEN
918 l_return_value := is_unit_locked(p_workorder_id => p_workorder_id,
919 p_ue_id => NULL,
920 p_item_instance_id => NULL,
921 p_visit_id => NULL
922 );
923 -- If the unit is locked, then resource transactions are not allowed
924 IF l_return_value = FND_API.G_TRUE THEN
925 RETURN FND_API.G_FALSE;
926 END IF;
927 END IF;
928
929 OPEN get_wo_status(p_workorder_id);
930 FETCH get_wo_status INTO l_wo_status;
931 CLOSE get_wo_status;
932
933 -- If the workorder is in status On Hold, Closed, Complete No Charge, Unreleased, Cancelled
934 -- then resource transactions are not allowed
935 IF l_wo_status IS NULL OR l_wo_status IN ('6','12','5','1', '7', '13') THEN
936 RETURN FND_API.G_FALSE;
937 END IF;
938
939 RETURN FND_API.G_TRUE;
940
941 END Is_ResTxn_Allowed;
942
943 ------------------------------------------------------------------------------------------------
944 -- Function to test if user has privileges to cancel a workorder that is not un-released
945 -- or if user has privileges to cancel any work order.
946 ------------------------------------------------------------------------------------------------
947 -- Start of Comments
948 -- Function name : Is_Wo_Cancel_Allowed
949 -- Type : Private
950 -- Pre-reqs :
951 -- Parameters :
952 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
953 --
954 -- Standard IN Parameters :
955 -- None
956 --
957 -- Standard OUT Parameters :
958 -- None
959 --
960 --
961 --
962 -- Version :
963 -- Current version 1.0
964 --
965 -- End of Comments
966
967 FUNCTION Is_Wo_Cancel_Allowed(
968 P_workorder_id IN NUMBER := NULL
969 ) RETURN VARCHAR2
970 IS
971 -- cursor to retrieve whether workorder is released
972 CURSOR is_workorders_released(c_wo_id NUMBER)
973 IS
974 SELECT 'x'
975 FROM AHL_WORKORDERS WO,wip_discrete_jobs WIP
976 WHERE WO.wip_entity_id = WIP.wip_entity_id
977 AND WIP.date_released IS NOT NULL AND
978 WO.WORKORDER_ID = c_wo_id;
979
980 l_junk VARCHAR2(1);
981
982 BEGIN
983
984 IF NOT(FND_FUNCTION.TEST('AHL_PRD_DISALLOW_CANCEL_JOBS',null)) THEN
985
986 -- if no workorder ID is passed, then check for only AHL_PRD_DISALLOW_CANCEL_JOBS
987 -- function access.
988 IF (p_workorder_id IS NULL) THEN
989 return FND_API.G_TRUE;
990 ELSE
991 OPEN is_workorders_released(p_workorder_id);
992 FETCH is_workorders_released INTO l_junk;
993 IF(is_workorders_released%NOTFOUND)THEN
994 CLOSE is_workorders_released;
995 return FND_API.G_TRUE;
996 END IF;
997 CLOSE is_workorders_released;
998
999 IF(FND_FUNCTION.TEST('AHL_PRD_CANCEL_REL_JOBS',null))THEN
1000 return FND_API.G_TRUE;
1001 END IF;
1002 END IF;
1003
1004 END IF;
1005
1006 RETURN FND_API.G_FALSE;
1007
1008 END Is_Wo_Cancel_Allowed;
1009
1010 -- Start of Comments --
1011 -- Function name : Get_Op_TotalHours_Assigned
1012 --
1013 -- Parameters :
1014 --
1015 -- p_employee_id -- Optional Input Employee Id.
1016 -- p_workorder_id -- Mandatory Workorder ID.
1017 -- p_operation_seq_num -- Mandatory Operation Seq Number
1018 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
1019 --
1020 --
1021 -- Description : This function returns the total hours assigned to an operation.
1022 -- If the user role is technician or line maintenance technician, then the
1023 -- total hours are calculated for that particular employee resource,
1024 -- otherwise the total hours are calculated for all the person type
1025 -- resources in the operation. If the employee id is not passed to the
1026 -- function then the calculations are done for the user who is currently
1027 -- logged into the application.
1028 --
1029
1030 FUNCTION Get_Op_TotalHours_Assigned (p_employee_id IN NUMBER := NULL,
1031 p_workorder_id IN NUMBER,
1032 p_operation_seq_num IN NUMBER,
1033 p_fnd_function_name IN VARCHAR2)
1034 RETURN NUMBER
1035
1036 IS
1037
1038 -- query to retrieve total hrs assigned to an employee for an operation.
1039 CURSOR c_get_total_hours_emp(p_employee_id IN NUMBER,
1040 p_workorder_id IN NUMBER,
1041 p_operation_seq_num IN NUMBER) IS
1042 SELECT SUM((AWAS.assign_end_date - AWAS.assign_start_date) * 24)
1043 FROM AHL_WORKORDER_OPERATIONS WO, AHL_OPERATION_RESOURCES AOR,
1044 AHL_WORK_ASSIGNMENTS AWAS, BOM_RESOURCES BOM
1045 WHERE WO.workorder_operation_id = AOR.workorder_operation_id
1046 AND AOR.operation_resource_id = AWAS.operation_resource_id
1047 AND BOM.resource_id = AOR.resource_id
1048 AND BOM.resource_type = 2 -- Person
1049 AND WO.workorder_id = p_workorder_id
1050 AND WO.operation_sequence_num = p_operation_seq_num
1051 AND AWAS.employee_id = p_employee_id;
1052
1053 -- query to retrieve total hrs required for all employees for an operation.
1054 CURSOR c_get_total_hours_op(p_workorder_id IN NUMBER,
1055 p_operation_seq_num IN NUMBER) IS
1056 SELECT SUM(NVL(AOR.duration, 0))
1057 FROM AHL_OPERATION_RESOURCES AOR,
1058 BOM_RESOURCES BOMR,
1059 AHL_WORKORDER_OPERATIONS AWOP
1060 WHERE AOR.RESOURCE_ID = BOMR.RESOURCE_ID
1061 AND BOMR.resource_type = 2 -- Person
1062 AND AOR.WORKORDER_OPERATION_ID = AWOP.WORKORDER_OPERATION_ID
1063 AND AWOP.workorder_id = p_workorder_id
1064 AND AWOP.operation_sequence_num = p_operation_seq_num;
1065
1066 l_employee_id NUMBER;
1067 l_total_hours NUMBER;
1068
1069 BEGIN
1070
1071 l_total_hours := 0;
1072 IF (p_fnd_function_name = G_TECH_MYWO) THEN -- Technician.
1073 -- Get logged in user's emp ID if input employee ID is null.
1074 IF (p_employee_id IS NULL) THEN
1075 l_employee_id := AHL_PRD_WO_LOGIN_PVT.Get_Employee_ID;
1076 ELSE
1077 l_employee_id := p_employee_id;
1078 END IF;
1079
1080 -- get total hrs.
1081 OPEN c_get_total_hours_emp(l_employee_id, p_workorder_id, p_operation_seq_num);
1082 FETCH c_get_total_hours_emp INTO l_total_hours;
1083 CLOSE c_get_total_hours_emp;
1084 ELSE -- Data Clerk, Transit role.
1085 OPEN c_get_total_hours_op(p_workorder_id, p_operation_seq_num);
1086 FETCH c_get_total_hours_op INTO l_total_hours;
1087 CLOSE c_get_total_hours_op;
1088 END IF;
1089
1090 RETURN round(l_total_hours,3);
1091
1092 END Get_Op_TotalHours_Assigned;
1093 -----------------------------------------------------------------------------------------
1094
1095 -- Start of Comments --
1096 -- Function name : Get_Res_TotalHours_Assigned
1097 --
1098 -- Parameters :
1099 --
1100 -- p_employee_id -- Optional Input Employee Id.
1101 -- p_workorder_id -- Mandatory Workorder ID.
1102 -- p_operation_seq_num -- Mandatory Operation Seq Number
1103 -- p_resource_id -- Mandatory resource ID.
1104 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
1105 --
1106 --
1107 -- Description : This function returns the total hours assigned for a specific resource
1108 -- within an operation. If the employee id passed to the function is null,
1109 -- then the calculations are done for the user who is currently logged
1110 -- into the application.
1111
1112 FUNCTION Get_Res_TotalHours_Assigned (p_employee_id IN NUMBER := NULL,
1113 p_workorder_id IN NUMBER,
1114 p_operation_seq_num IN NUMBER,
1115 p_resource_id IN NUMBER,
1116 p_fnd_function_name IN VARCHAR2)
1117 RETURN NUMBER
1118 IS
1119 -- query to retrieve total hrs assigned to an employee for an operation-resource.
1120 CURSOR c_get_total_hours_emp(p_employee_id IN NUMBER,
1121 p_workorder_id IN NUMBER,
1122 p_operation_seq_num IN NUMBER,
1123 p_resource_id IN NUMBER) IS
1124 SELECT SUM((AWAS.assign_end_date - AWAS.assign_start_date) * 24)
1125 FROM AHL_WORKORDER_OPERATIONS WO, AHL_OPERATION_RESOURCES AOR,
1126 AHL_WORK_ASSIGNMENTS AWAS, BOM_RESOURCES BOM
1127 WHERE WO.workorder_operation_id = AOR.workorder_operation_id
1128 AND AOR.operation_resource_id = AWAS.operation_resource_id
1129 AND BOM.resource_id = AOR.resource_id
1130 AND BOM.resource_type = 2 -- Person
1131 AND WO.workorder_id = p_workorder_id
1132 AND WO.operation_sequence_num = p_operation_seq_num
1133 AND AOR.resource_id = p_resource_id
1134 AND AWAS.employee_id = p_employee_id;
1135
1136 -- query to retrieve total hrs assigned to all employees for an operation-resource.
1137 CURSOR c_get_total_hours_op(p_workorder_id IN NUMBER,
1138 p_operation_seq_num IN NUMBER,
1139 p_resource_id IN NUMBER) IS
1140 SELECT SUM((AWAS.assign_end_date - AWAS.assign_start_date) * 24)
1141 FROM AHL_WORKORDER_OPERATIONS WO, AHL_OPERATION_RESOURCES AOR,
1142 AHL_WORK_ASSIGNMENTS AWAS, BOM_RESOURCES BOM
1143 WHERE WO.workorder_operation_id = AOR.workorder_operation_id
1144 AND AOR.operation_resource_id = AWAS.operation_resource_id
1145 AND BOM.resource_id = AOR.resource_id
1146 AND BOM.resource_type = 2 -- Person
1147 AND WO.workorder_id = p_workorder_id
1148 AND WO.operation_sequence_num = p_operation_seq_num
1149 AND AOR.resource_id = p_resource_id;
1150
1151 l_total_hours NUMBER;
1152 l_employee_id NUMBER;
1153
1154 BEGIN
1155
1156 l_total_hours := 0;
1157 IF (p_fnd_function_name = G_TECH_MYWO) THEN -- Technician.
1158 -- Get logged in user's emp ID if input employee ID is null.
1159 IF (p_employee_id IS NULL) THEN
1160 l_employee_id := AHL_PRD_WO_LOGIN_PVT.Get_Employee_ID;
1161 ELSE
1162 l_employee_id := p_employee_id;
1163 END IF;
1164
1165 -- get total hrs.
1166 OPEN c_get_total_hours_emp(l_employee_id, p_workorder_id, p_operation_seq_num,
1167 p_resource_id);
1168 FETCH c_get_total_hours_emp INTO l_total_hours;
1169 CLOSE c_get_total_hours_emp;
1170 ELSE
1171 -- Data Clerk, Transit.
1172 OPEN c_get_total_hours_op(p_workorder_id, p_operation_seq_num, p_resource_id);
1173 FETCH c_get_total_hours_op INTO l_total_hours;
1174 CLOSE c_get_total_hours_op;
1175 END IF;
1176
1177 RETURN round(l_total_hours,3);
1178
1179 END Get_Res_TotalHours_Assigned;
1180 -----------------------------------------------------------------------------------------
1181
1182 -- Start of Comments --
1183 -- Function name : Get_Op_Transacted_Hours
1184 --
1185 -- Parameters :
1186 -- p_employee_id -- Optional Input Employee Id.
1187 -- p_wip_entity_id -- Mandatory Workorder ID.
1188 -- p_operation_seq_num -- Mandatory Operation Seq Number
1189 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
1190 --
1191 -- Description : This function returns the number of hours transacted by an employee
1192 -- accross all resources within an operation. If the employee id passed to the
1193 -- function is null then the calculations are based on the user currently logged
1194 -- into the application.
1195
1196 FUNCTION Get_Op_Transacted_Hours (p_employee_id IN NUMBER := NULL,
1197 p_wip_entity_id IN NUMBER,
1198 p_operation_seq_num IN NUMBER,
1199 p_fnd_function_name IN VARCHAR2)
1200 RETURN NUMBER
1201 IS
1202 -- query to retrieve total hrs transacted by an employee.
1203 CURSOR c_get_res_txns_emp(p_wip_entity_id IN NUMBER,
1204 p_operation_seq_num IN NUMBER,
1205 p_employee_id IN NUMBER ) IS
1206 SELECT NVL( SUM( transaction_quantity ), 0 )
1207 FROM WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
1208 WHERE WT.resource_id = BRS.resource_id
1209 AND BRS.resource_type = 2 -- person.
1210 AND wt.wip_entity_id = p_wip_entity_id
1211 AND wt.operation_seq_num = p_operation_seq_num
1212 AND wt.employee_id = p_employee_id;
1213
1214 -- query to get pending resource txns for an employee.
1215 CURSOR c_get_pend_res_txns_emp(p_wip_entity_id NUMBER,
1216 p_operation_seq_num NUMBER,
1217 p_employee_id NUMBER ) IS
1218 SELECT NVL( SUM( transaction_quantity ), 0 )
1219 FROM WIP_COST_TXN_INTERFACE wcti, bom_resources br, wip_operation_resources wor
1220 WHERE wcti.wip_entity_id = wor.wip_entity_id
1221 AND wcti.operation_seq_num = wor.operation_seq_num
1222 AND wcti.resource_seq_num = wor.resource_seq_num
1223 AND wcti.organization_id = wor.organization_id
1224 AND wor.resource_id = br.resource_id
1225 AND br.resource_type = 2
1226 AND wcti.wip_entity_id = p_wip_entity_id
1227 AND wcti.operation_seq_num = p_operation_seq_num
1228 AND wcti.employee_id = p_employee_id
1229 AND wcti.process_status <> 3; -- skip errored txns.
1230
1231 -- query to retrieve total hrs transacted by all employees.
1232 CURSOR c_get_res_txns_op(p_wip_entity_id IN NUMBER,
1233 p_operation_seq_num IN NUMBER) IS
1234 SELECT NVL( SUM( transaction_quantity ), 0 )
1235 FROM WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
1236 WHERE WT.resource_id = BRS.resource_id
1237 AND BRS.resource_type = 2 -- person.
1238 AND WT.wip_entity_id = p_wip_entity_id
1239 AND WT.operation_seq_num = p_operation_seq_num;
1240
1241 -- query to get pending resource txns for all employees.
1242 CURSOR c_get_pend_res_txns_op(p_wip_entity_id NUMBER,
1243 p_operation_seq_num NUMBER) IS
1244 -- Here we cannot join with wcti.resource_id column
1245 -- since this col can be null.
1246 SELECT NVL( SUM( transaction_quantity ), 0 )
1247 FROM WIP_COST_TXN_INTERFACE wcti, bom_resources br, wip_operation_resources wor
1248 WHERE wcti.wip_entity_id = wor.wip_entity_id
1249 AND wcti.operation_seq_num = wor.operation_seq_num
1250 AND wcti.resource_seq_num = wor.resource_seq_num
1251 AND wcti.organization_id = wor.organization_id
1252 AND wor.resource_id = br.resource_id
1253 AND br.resource_type = 2
1254 AND wcti.wip_entity_id = p_wip_entity_id
1255 AND wcti.operation_seq_num = p_operation_seq_num
1256 AND wcti.process_status <> 3; -- skip errored txns.
1257
1258 l_total_hrs NUMBER;
1259 l_pending_hrs NUMBER;
1260 l_employee_id NUMBER;
1261
1262 BEGIN
1263
1264 l_total_hrs := 0;
1265 l_pending_hrs := 0;
1266 IF (p_fnd_function_name = G_TECH_MYWO OR p_fnd_function_name = G_LINE_TECH) THEN
1267
1268 -- Get logged in user's emp ID if input employee ID is null.
1269 IF (p_employee_id IS NULL) THEN
1270 l_employee_id := AHL_PRD_WO_LOGIN_PVT.Get_Employee_ID;
1271 ELSE
1272 l_employee_id := p_employee_id;
1273 END IF;
1274 -- get emp txns for an wo-oper.
1275 OPEN c_get_res_txns_emp(p_wip_entity_id, p_operation_seq_num, l_employee_id);
1276 FETCH c_get_res_txns_emp INTO l_total_hrs;
1277 CLOSE c_get_res_txns_emp;
1278 -- get pending txns.
1279 OPEN c_get_pend_res_txns_emp(p_wip_entity_id, p_operation_seq_num, l_employee_id);
1280 FETCH c_get_pend_res_txns_emp INTO l_pending_hrs;
1281 CLOSE c_get_pend_res_txns_emp;
1282 l_total_hrs := l_total_hrs + l_pending_hrs;
1283
1284 ELSE -- Data Clerk
1285
1286 -- get operation txns.
1287 OPEN c_get_res_txns_op(p_wip_entity_id, p_operation_seq_num);
1288 FETCH c_get_res_txns_op INTO l_total_hrs;
1289 CLOSE c_get_res_txns_op;
1290
1291 -- get pending txns.
1292 OPEN c_get_pend_res_txns_op(p_wip_entity_id, p_operation_seq_num);
1293 FETCH c_get_pend_res_txns_op INTO l_pending_hrs;
1294 CLOSE c_get_pend_res_txns_op;
1295
1296 l_total_hrs := l_total_hrs + l_pending_hrs;
1297 END IF; -- p_fnd_function_name.
1298
1299 RETURN round(l_total_hrs,3);
1300
1301 END Get_Op_Transacted_Hours;
1302 -----------------------------------------------------------------------------------------
1303
1304 -- Start of Comments --
1305 -- Function name : Get_Res_Transacted_Hours
1306 --
1307 -- Parameters :
1308 -- p_employee_id -- Optional Input Employee Id.
1309 -- p_wip_entity_id -- Mandatory Workorder ID.
1310 -- p_operation_seq_num -- Mandatory Operation Seq Number
1311 -- p_resource_seq_num -- Mandatory Resource ID.
1312 -- p_fnd_function_name -- Mandatory fnd_function to identify user role.
1313 --
1314 -- Description : This function returns the number of hours transacted by an employee
1315 -- for a particular resource requirement within an operation if the user is
1316 -- has a role of a technician or line maintenance technician. It returns the
1317 -- number of hours transacted by all employees for a resource requirement
1318 -- within an operation if the user is a data clerk.
1319 --
1320
1321 FUNCTION Get_Res_Transacted_Hours (p_employee_id IN NUMBER := NULL,
1322 p_wip_entity_id IN NUMBER,
1323 p_operation_seq_num IN NUMBER,
1324 p_resource_seq_num IN NUMBER,
1325 p_fnd_function_name IN VARCHAR2)
1326 RETURN NUMBER
1327 IS
1328 -- query to retrieve total hrs transacted by an employee.
1329 CURSOR c_get_res_txns_emp(p_wip_entity_id IN NUMBER,
1330 p_operation_seq_num IN NUMBER,
1331 p_resource_seq_num IN NUMBER,
1332 p_employee_id IN NUMBER ) IS
1333 SELECT NVL( SUM( transaction_quantity ), 0 )
1334 FROM WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
1335 WHERE WT.resource_id = BRS.resource_id
1336 AND BRS.resource_type = 2 -- person.
1337 AND WT.transaction_type = 1 -- resource txn.
1338 AND wt.wip_entity_id = p_wip_entity_id
1339 AND wt.operation_seq_num = p_operation_seq_num
1340 AND wt.resource_seq_num = p_resource_seq_num
1341 AND wt.employee_id = p_employee_id;
1342
1343 -- query to get pending resource txns for an employee.
1344 CURSOR c_get_pend_res_txns_emp(p_wip_entity_id NUMBER,
1345 p_operation_seq_num NUMBER,
1346 p_resource_seq_num IN NUMBER,
1347 p_employee_id NUMBER ) IS
1348 SELECT NVL( SUM( transaction_quantity ), 0 )
1349 FROM WIP_COST_TXN_INTERFACE wcti, bom_resources br,
1350 wip_operation_resources wor
1351 WHERE wcti.wip_entity_id = wor.wip_entity_id
1352 AND wcti.operation_seq_num = wor.operation_seq_num
1353 AND wcti.resource_seq_num = wor.resource_seq_num
1354 AND wcti.organization_id = wor.organization_id
1355 AND wor.resource_id = br.resource_id
1356 AND br.resource_type = 2 -- person
1357 AND wcti.transaction_type = 1 -- resource txn.
1358 AND wcti.wip_entity_id = p_wip_entity_id
1359 AND wcti.operation_seq_num = p_operation_seq_num
1360 AND wcti.resource_seq_num = p_resource_seq_num
1361 AND wcti.employee_id = p_employee_id
1362 AND wcti.process_status <> 3; -- skip errored txns.
1363
1364 -- query to retrieve total hrs transacted by all employees.
1365 CURSOR c_get_res_txns_res(p_wip_entity_id IN NUMBER,
1366 p_operation_seq_num IN NUMBER,
1367 p_resource_seq_num IN NUMBER) IS
1368 SELECT NVL( SUM( transaction_quantity ), 0 )
1369 FROM WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
1370 WHERE WT.resource_id = BRS.resource_id
1371 AND BRS.resource_type = 2 -- person.
1372 AND WT.transaction_type = 1 -- resource txn.
1373 AND wt.wip_entity_id = p_wip_entity_id
1374 AND wt.operation_seq_num = p_operation_seq_num
1375 AND wt.resource_seq_num = p_resource_seq_num;
1376
1377 -- query to get pending resource txns for all employees.
1378 -- Here we cannot join with wcti.resource_id column
1379 -- since this col maybe null
1380 CURSOR c_get_pend_res_txns_res(p_wip_entity_id IN NUMBER,
1381 p_operation_seq_num IN NUMBER,
1382 p_resource_seq_num IN NUMBER) IS
1383 SELECT NVL( SUM( transaction_quantity ), 0 )
1384 FROM WIP_COST_TXN_INTERFACE wcti, bom_resources br,
1385 wip_operation_resources wor
1386 WHERE wcti.wip_entity_id = wor.wip_entity_id
1387 AND wcti.operation_seq_num = wor.operation_seq_num
1388 AND wcti.resource_seq_num = wor.resource_seq_num
1389 AND wcti.organization_id = wor.organization_id
1390 AND wor.resource_id = br.resource_id
1391 AND br.resource_type = 2 -- person
1392 AND wcti.transaction_type = 1 -- resource txn.
1393 AND wcti.wip_entity_id = p_wip_entity_id
1394 AND wcti.operation_seq_num = p_operation_seq_num
1395 AND wcti.resource_seq_num = p_resource_seq_num
1396 AND wcti.process_status <> 3; -- skip errored txns.
1397
1398 l_total_hrs NUMBER;
1399 l_pending_hrs NUMBER;
1400 l_employee_id NUMBER;
1401 l_fnd_function_name VARCHAR2(80);
1402
1403 BEGIN
1404
1405 l_total_hrs := 0;
1406 l_pending_hrs := 0;
1407
1408 -- Though p_fnd_function_name is mandatory it cannot be passed from the OA Tech Search UI
1409 -- due to the limitation with OA inner table. Hence putting the code here.
1410 l_fnd_function_name := p_fnd_function_name;
1411 IF (l_fnd_function_name IS NULL) THEN
1412 l_fnd_function_name := AHL_PRD_WO_LOGIN_PVT.Get_User_Role;
1413 END IF;
1414
1415 -- Technician or Transit Technician
1416 IF (l_fnd_function_name = G_TECH_MYWO OR l_fnd_function_name = G_LINE_TECH) THEN
1417
1418 -- Get logged in user's emp ID if input employee ID is null.
1419 IF (p_employee_id IS NULL) THEN
1420 l_employee_id := AHL_PRD_WO_LOGIN_PVT.Get_Employee_ID;
1421 ELSE
1422 l_employee_id := p_employee_id;
1423 END IF;
1424
1425 -- get emp txns for an wo-oper.
1426 OPEN c_get_res_txns_emp(p_wip_entity_id, p_operation_seq_num, p_resource_seq_num,l_employee_id);
1427 FETCH c_get_res_txns_emp INTO l_total_hrs;
1428 CLOSE c_get_res_txns_emp;
1429
1430 -- get pending txns.
1431 OPEN c_get_pend_res_txns_emp(p_wip_entity_id, p_operation_seq_num, p_resource_seq_num,l_employee_id);
1432 FETCH c_get_pend_res_txns_emp INTO l_pending_hrs;
1433 CLOSE c_get_pend_res_txns_emp;
1434
1435 l_total_hrs := l_total_hrs + l_pending_hrs;
1436
1437 ELSE -- Data Clerk
1438 -- get operation txns.
1439 OPEN c_get_res_txns_res(p_wip_entity_id, p_operation_seq_num, p_resource_seq_num);
1440 FETCH c_get_res_txns_res INTO l_total_hrs;
1441 CLOSE c_get_res_txns_res;
1442
1443 -- get pending txns.
1444 OPEN c_get_pend_res_txns_res(p_wip_entity_id, p_operation_seq_num, p_resource_seq_num);
1445 FETCH c_get_pend_res_txns_res INTO l_pending_hrs;
1446 CLOSE c_get_pend_res_txns_res;
1447
1448 l_total_hrs := l_total_hrs + l_pending_hrs;
1449 END IF; -- p_fnd_function_name.
1450
1451 RETURN round(l_total_hrs,3);
1452
1453 END Get_Res_Transacted_Hours;
1454 -----------------------------------------------------------------------------------------
1455
1456 -- Start of Comments --
1457 -- Function name : Get_Op_Assigned_Start_Date
1458 --
1459 -- Parameters :
1460 --
1461 -- p_employee_id -- Optional Input Employee Id.
1462 -- p_workorder_id -- Mandatory Workorder ID.
1463 -- p_operation_seq_num -- Mandatory Operation Seq Number
1464 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
1465 --
1466 --
1467 -- Description : This function will be used to retrieve the Assigned Start Date for an
1468 -- operation as displayed on the Operations subtab of the Update Workorders
1469 -- page. The logic for retrieving the correct date is as follows:
1470 -- 1. If the user is a technician, then assigned start time is the
1471 -- assign_start_date for the employee if he is assigned to only
1472 -- one resource within the operation.
1473 -- If the employee is assigned to more than one resource within
1474 -- the operation, then the assigned start date is the earliest
1475 -- of all the assignment dates for the employee.
1476 ---2. If the user is a data clerk or a line maintenance technician, then the assigned
1477 --- start date is the scheduled start date for the operation.
1478
1479 --
1480
1481 FUNCTION Get_Op_Assigned_Start_Date(p_employee_id IN NUMBER := NULL,
1482 p_workorder_id IN NUMBER,
1483 p_operation_seq_num IN NUMBER,
1484 p_fnd_function_name IN VARCHAR2)
1485 RETURN DATE
1486
1487 IS
1488
1489 -- query to retrieve min of assigned to an employee for an operation.
1490 CURSOR c_get_assigned_start_emp(p_employee_id IN NUMBER,
1491 p_workorder_id IN NUMBER,
1492 p_operation_seq_num IN NUMBER) IS
1493 SELECT MIN(AWAS.ASSIGN_START_DATE)
1494 FROM WIP_Operation_Resources WOR, AHL_OPERATION_RESOURCES AOR,
1495 AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDERS AW
1496 WHERE WOR.resource_id = AOR.resource_id
1497 AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
1498 AND AOR.operation_resource_id = AWAS.operation_resource_id
1499 AND WOR.wip_entity_id = AW.wip_entity_id
1500 AND AWAS.employee_id = p_employee_id
1501 AND AW.workorder_id = p_workorder_id
1502 AND WOR.operation_seq_num = p_operation_seq_num;
1503
1504 -- query to retrieve min of assigned to all employees for an operation.
1505 CURSOR c_get_op_sched_start(p_workorder_id IN NUMBER,
1506 p_operation_seq_num IN NUMBER) IS
1507 SELECT SCHEDULED_START_DATE
1508 FROM AHL_WORKORDER_OPERATIONS_V AO
1509 WHERE AO.workorder_id = p_workorder_id
1510 AND AO.operation_sequence_num = p_operation_seq_num;
1511
1512 l_employee_id NUMBER;
1513 l_assigned_start DATE;
1514
1515 BEGIN
1516
1517 IF (p_fnd_function_name = G_TECH_MYWO) THEN -- Technician.
1518 -- Get logged in user's emp ID if input employee ID is null.
1519 IF (p_employee_id IS NULL) THEN
1520 l_employee_id := AHL_PRD_WO_LOGIN_PVT.Get_Employee_ID;
1521 ELSE
1522 l_employee_id := p_employee_id;
1523 END IF;
1524
1525 -- get assigned start.
1526 OPEN c_get_assigned_start_emp(l_employee_id, p_workorder_id, p_operation_seq_num);
1527 FETCH c_get_assigned_start_emp INTO l_assigned_start;
1528 CLOSE c_get_assigned_start_emp;
1529 ELSE -- Data Clerk, Transit role.
1530 OPEN c_get_op_sched_start(p_workorder_id, p_operation_seq_num);
1531 FETCH c_get_op_sched_start INTO l_assigned_start;
1532 CLOSE c_get_op_sched_start;
1533 END IF;
1534
1535 RETURN l_assigned_start;
1536
1537 END Get_Op_Assigned_Start_Date;
1538 -----------------------------------------------------------------------------------------
1539
1540 -- Start of Comments --
1541 -- Function name : Get_Op_Assigned_End_Date
1542 --
1543 -- Parameters :
1544 --
1545 -- p_employee_id -- Optional Input Employee Id.
1546 -- p_workorder_id -- Mandatory Workorder ID.
1547 -- p_operation_seq_num -- Mandatory Operation Seq Number
1548 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
1549 --
1550 --
1551 -- Description : This function will be used to retrieve the Assigned End Date for an
1552 -- operation as displayed on the Operations subtab of the Update Workorders
1553 -- page. The logic for retrieving the correct date is as follows:
1554 -- 1. If the user is a technician, then assigned end time is the
1555 -- assign_end_date for the employee if he is assigned to only
1556 -- one resource within the operation.
1557 -- If the employee is assigned to more than one resource within
1558 -- the operation, then the assigned end date is the latest
1559 -- of all the assignment dates for the employee.
1560 --- 2. If the user is a data clerk or a line maintenance technician, then
1561 -- the assigned end date is the scheduled start date for the operation.
1562 --
1563
1564 FUNCTION Get_Op_Assigned_End_Date(p_employee_id IN NUMBER := NULL,
1565 p_workorder_id IN NUMBER,
1566 p_operation_seq_num IN NUMBER,
1567 p_fnd_function_name IN VARCHAR2)
1568 RETURN DATE
1569
1570 IS
1571
1572 -- query to retrieve min of assigned to an employee for an operation.
1573 CURSOR c_get_assigned_end_emp(p_employee_id IN NUMBER,
1574 p_workorder_id IN NUMBER,
1575 p_operation_seq_num IN NUMBER) IS
1576 SELECT MAX(AWAS.ASSIGN_END_DATE)
1577 FROM WIP_Operation_Resources WOR, AHL_OPERATION_RESOURCES AOR,
1578 AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDERS AW
1579 WHERE WOR.resource_id = AOR.resource_id
1580 AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
1581 AND AOR.operation_resource_id = AWAS.operation_resource_id
1582 AND WOR.wip_entity_id = AW.wip_entity_id
1583 AND AWAS.employee_id = p_employee_id
1584 AND AW.workorder_id = p_workorder_id
1585 AND WOR.operation_seq_num = p_operation_seq_num;
1586
1587 -- query to retrieve min of assigned to all employees for an operation.
1588 CURSOR c_get_op_sched_end(p_workorder_id IN NUMBER,
1589 p_operation_seq_num IN NUMBER) IS
1590 SELECT SCHEDULED_END_DATE
1591 FROM AHL_WORKORDER_OPERATIONS_V AO
1592 WHERE AO.workorder_id = p_workorder_id
1593 AND AO.operation_sequence_num = p_operation_seq_num;
1594
1595 l_employee_id NUMBER;
1596 l_assigned_end DATE;
1597
1598 BEGIN
1599
1600 IF (p_fnd_function_name = G_TECH_MYWO) THEN -- Technician.
1601 -- Get logged in user's emp ID if input employee ID is null.
1602 IF (p_employee_id IS NULL) THEN
1603 l_employee_id := AHL_PRD_WO_LOGIN_PVT.Get_Employee_ID;
1604 ELSE
1605 l_employee_id := p_employee_id;
1606 END IF;
1607
1608 -- get assigned start.
1609 OPEN c_get_assigned_end_emp(l_employee_id, p_workorder_id, p_operation_seq_num);
1610 FETCH c_get_assigned_end_emp INTO l_assigned_end;
1611 CLOSE c_get_assigned_end_emp;
1612 ELSE -- Data Clerk, Transit role.
1613 OPEN c_get_op_sched_end(p_workorder_id, p_operation_seq_num);
1614 FETCH c_get_op_sched_end INTO l_assigned_end;
1615 CLOSE c_get_op_sched_end;
1616 END IF;
1617
1618 RETURN l_assigned_end;
1619
1620 END Get_Op_Assigned_End_Date;
1621 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1622
1623 END AHL_PRD_UTIL_PKG; -- Package Body AHL_PRD_UTIL_PKG
[Home] [Help]