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