1 PACKAGE AHL_PRD_UTIL_PKG AUTHID CURRENT_USER AS
2 /* $Header: AHLUPRDS.pls 120.11.12020000.2 2012/12/10 16:52:31 prakkum ship $ */
3 --
4 -- To modify this template, edit file PKGSPEC.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Contains utility and helper functions used by the CMRO Execution module.
8 --
9 -- Added global constants to be used to identity techinican, Data Clerk and Line Maintenance
10 -- Technician roles.
11
12 G_TECH_MYWO CONSTANT VARCHAR2(30) := 'AHL_PRD_TECH_MYWO';
13 G_DATA_CLERK CONSTANT VARCHAR2(30) := 'AHL_PRD_DATA_CLERK';
14 G_LINE_TECH CONSTANT VARCHAR2(30) := 'AHL_PRD_TRANSIT_TECH';
15
16 PROCEDURE validate_locators
17 ( p_locator_id IN number,
18 p_org_id IN number,
19 p_subinventory_code IN Varchar2,
20 X_Return_Status Out NOCOPY Varchar2,
21 X_Msg_Data Out NOCOPY Varchar2
22 );
23
24 procedure validate_condition
25 (
26 p_condition_id In number,
27 x_return_status out NOCOPY varchar2,
28 x_msg_data out NOCOPY varchar2
29
30 );
31
32 procedure validate_reason
33 (
34 p_reason_id In number,
35 x_return_status out NOCOPY varchar2,
36 x_msg_data out NOCOPY varchar2
37
38 );
39
40 PROCEDURE VALIDATE_MATERIAL_STATUS(p_Organization_Id IN NUMBER,
41 p_Subinventory_Code IN VARCHAR2,-- not null
42 p_Condition_id IN NUMBER,-- null/not null
43 x_return_status OUT NOCOPY VARCHAR2
44 );
45
46 ------------------------------------------------------------------------------------------------
47 -- Function to test if the Unit in context is locked or not. The input to the API can be one of
48 -- workorder_id, mr_id, visit_id or item_instance_id.
49 ------------------------------------------------------------------------------------------------
50 -- Start of Comments
51 -- Function name : Is_Unit_Locked
52 -- Type : Private
53 -- Pre-reqs :
54 -- Parameters :
55 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
56 --
57 -- Standard IN Parameters :
58 -- None
59 --
60 -- Standard OUT Parameters :
61 -- None
62 --
63 -- Is_Unit_Locked IN parameters:
64 -- P_workorder_id NUMBER Required
65 -- P_ue_id NUMBER Required
66 -- P_visit_id NUMBER Required
67 -- P_item_instance_id NUMBER Required
68 --
69 -- Is_Unit_Locked IN OUT parameters:
70 -- None
71 --
72 -- Is_Unit_Locked OUT parameters:
73 -- None.
74 --
75 -- Version :
76 -- Current version 1.0
77 --
78 -- End of Comments
79
80 FUNCTION Is_Unit_Locked(
81 P_workorder_id IN NUMBER,
82 P_ue_id IN NUMBER,
83 P_visit_id IN NUMBER,
84 P_item_instance_id IN NUMBER
85 )
86 RETURN VARCHAR2;
87
88 ------------------------------------------------------------------------------------------------
89 -- Function to test if the workorder can be updated.
90 -- Determined based on following factors
91 -- 1. If the unit is quarantined then it cannot be updated.
92 -- 2. If the workorder status is any of 22, 12 and 7 then it cannot be updated.
93 ------------------------------------------------------------------------------------------------
94 -- Start of Comments
95 -- Function name : Is_Wo_Updatable
96 -- Type : Private
97 -- Pre-reqs :
98 -- Parameters :
99 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
100 --
101 -- Standard IN Parameters :
102 -- None
103 --
104 -- Standard OUT Parameters :
105 -- None
106 --
107 -- Is_Wo_Updatable IN parameters:
108 -- P_workorder_id NUMBER Required
109 --
110 -- Is_Wo_Updatable IN OUT parameters:
111 -- None
112 --
113 -- Is_Wo_Updatable OUT parameters:
114 -- None.
115 --
116 -- Version :
117 -- Current version 1.0
118 --
119 -- End of Comments
120
121 FUNCTION Is_Wo_Updatable(
122 P_workorder_id IN NUMBER,
123 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
124 )
125 RETURN VARCHAR2;
126
127 ------------------------------------------------------------------------------------------------
128 -- Function to test if the workorder operation can be updated.
129 -- Determined based on following factors
130 -- 1. If the unit associated with the workorder to which the operation belongs is quarantined
131 -- then it cannot be updated.
132 -- 2. If the workorder status is any of 22, 12 and 7 then it cannot be updated.
133 -- 3. If the operation status is 'COMPLETE' then it cannot be updated
134 ------------------------------------------------------------------------------------------------
135 -- Start of Comments
136 -- Function name : Is_Op_Updatable
137 -- Type : Private
138 -- Pre-reqs :
139 -- Parameters :
140 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
141 --
142 -- Standard IN Parameters :
143 -- None
144 --
145 -- Standard OUT Parameters :
146 -- None
147 --
148 -- Is_Op_Updatable IN parameters:
149 -- P_workorder_id NUMBER Required
150 -- p_operation_seq_num NUMBER Required
151 --
152 -- Is_Op_Updatable IN OUT parameters:
153 -- None
154 --
155 -- Is_Op_Updatable OUT parameters:
156 -- None.
157 --
158 -- Version :
159 -- Current version 1.0
160 --
161 -- End of Comments
162 FUNCTION Is_Op_Updatable(
163 p_workorder_id IN NUMBER,
164 p_operation_seq_num IN NUMBER,
165 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
166 )
167 RETURN VARCHAR2;
168
169 ------------------------------------------------------------------------------------------------
170 -- Function to determine if a MR requires Quality collection to be done.
171 -- (Whether QA Collection is required)
172 -- The function returns QA Plan id or null if one is not associated with the MR.
173 ------------------------------------------------------------------------------------------------
174 -- Start of Comments
175 -- Function name : Is_Mr_Qa_Enabled
176 -- Type : Private
177 -- Pre-reqs :
178 -- Parameters :
179 -- Return : VARCHAR2.
180 --
181 -- Standard IN Parameters :
182 -- None
183 --
184 -- Standard OUT Parameters :
185 -- None
186 --
187 -- Is_Mr_Qa_Enabled IN parameters:
188 -- P_workorder_id NUMBER Required
189 -- p_ue_id NUMBER Required
190 --
191 -- Is_Mr_Qa_Enabled IN OUT parameters:
192 -- None
193 --
194 -- Is_Mr_Qa_Enabled OUT parameters:
195 -- None.
196 --
197 -- Version :
198 -- Current version 1.0
199 --
200 -- End of Comments
201 FUNCTION Is_Mr_Qa_Enabled(
202 p_ue_id IN NUMBER,
203 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
204 )
205 RETURN NUMBER;
206
207 ------------------------------------------------------------------------------------------------
208 -- Function to determine if parts changes are allowed for a workorder
209 -- 1. If the unit is quarantined then part changes are not allowed.
210 -- 2. If the workorder status is any of 22, 12 and 7 then part changes cannot be done.
211 ------------------------------------------------------------------------------------------------
212 -- Start of Comments
213 -- Function name : Is_PartChange_Enabled
214 -- Type : Private
215 -- Pre-reqs :
216 -- Parameters :
217 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
218 --
219 -- Standard IN Parameters :
220 -- None
221 --
222 -- Standard OUT Parameters :
223 -- None
224 --
225 -- Is_PartChange_Enabled IN parameters:
226 -- P_workorder_id NUMBER Required
227 --
228 -- Is_PartChange_Enabled IN OUT parameters:
229 -- None
230 --
231 -- Is_PartChange_Enabled OUT parameters:
232 -- None.
233 --
234 -- Version :
235 -- Current version 1.0
236 --
237 -- End of Comments
238 FUNCTION Is_PartChange_Enabled(
239 P_workorder_id IN NUMBER,
240 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
241 )
242 RETURN VARCHAR2;
243
244 ------------------------------------------------------------------------------------------------
245 -- Function to check if resource assignment should be allowed. The logic is based on following
246 -- factors :
247 -- 1. The unit is quarantined.
248 -- 2. A user is currently logged into the resource assignment.
249 -- 3. Resource transactions have been posted corresponding to this resource assignment.
250 ------------------------------------------------------------------------------------------------
251 -- Start of Comments
252 -- Function name : IsDelAsg_Enabled
253 -- Type : Private
254 -- Pre-reqs :
255 -- Parameters :
256 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
257 --
258 -- Standard IN Parameters :
259 -- None
260 --
261 -- Standard OUT Parameters :
262 -- None
263 --
264 -- IsDelAsg_Enabled IN parameters:
265 -- P_assignment_id IN REQUIRED
266 -- P_workorder_id IN REQUIRED
267 --
268 -- IsDelAsg_Enabled IN OUT parameters:
269 -- None
270 --
271 -- IsDelAsg_Enabled OUT parameters:
272 -- None.
273 --
274 -- Version :
275 -- Current version 1.0
276 --
277 -- End of Comments
278 FUNCTION IsDelAsg_Enabled(
279 P_assignment_id IN NUMBER,
280 P_workorder_id IN NUMBER,
281 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
282 )
283 RETURN VARCHAR2;
284
285 ------------------------------------------------------------------------------------------------
286 -- Function to test if the workorder can be completed.
287 ------------------------------------------------------------------------------------------------
288 -- Start of Comments
289 -- Function name : Is_Wo_Completable
290 -- Type : Private
291 -- Pre-reqs :
292 -- Parameters :
293 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
294 --
295 -- Standard IN Parameters :
296 -- None
297 --
298 -- Standard OUT Parameters :
299 -- None
300 --
301 -- Is_Wo_Updatable IN parameters:
302 -- P_workorder_id NUMBER Required
303 --
304 -- Is_Wo_Updatable IN OUT parameters:
305 -- None
306 --
307 -- Is_Wo_Updatable OUT parameters:
308 -- None.
309 --
310 -- Version :
311 -- Current version 1.0
312 --
313 -- End of Comments
314
315 FUNCTION Is_Wo_Completable(
316 P_workorder_id IN NUMBER
317 )
318 RETURN VARCHAR2;
319
320
321 ------------------------------------------------------------------------------------------------
322 -- Function to test if resource transactions are allowed for a workorder
323 ------------------------------------------------------------------------------------------------
324 -- Start of Comments
325 -- Function name : Is_ResTxn_Allowed
326 -- Type : Private
327 -- Pre-reqs :
328 -- Parameters :
329 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
330 --
331 -- Standard IN Parameters :
332 -- None
333 --
334 -- Standard OUT Parameters :
335 -- None
336 --
337 -- Is_Unit_Locked IN parameters:
338 -- P_workorder_id NUMBER Required
339 --
340 -- Is_Unit_Locked IN OUT parameters:
341 -- None
342 --
343 -- Is_Unit_Locked OUT parameters:
344 -- None.
345 --
346 -- Version :
347 -- Current version 1.0
348 --
349 -- End of Comments
350
351 FUNCTION Is_ResTxn_Allowed(
352 P_workorder_id IN NUMBER,
353 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
354 )
355 RETURN VARCHAR2;
356
357 ------------------------------------------------------------------------------------------------
358 -- Function to test if user has preivilages to cancel a workorder that is not un-released
362 -- Type : Private
359 ------------------------------------------------------------------------------------------------
360 -- Start of Comments
361 -- Function name : Is_Wo_Cancel_Allowed
363 -- Pre-reqs :
364 -- Parameters :
365 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
366 --
367 -- Standard IN Parameters :
368 -- None
369 --
370 -- Standard OUT Parameters :
371 -- None
372 --
373 --
374 -- Version :
375 -- Current version 1.0
376 --
377 -- End of Comments
378
379 FUNCTION Is_Wo_Cancel_Allowed(
380 P_workorder_id IN NUMBER := NULL
381 )
382 RETURN VARCHAR2;
383
384 --------------------------------------------------------------------------------------------
385 -- Start of Comments --
386 -- Function name : Get_Op_TotalHours_Assigned
387 --
388 -- Parameters :
389 --
390 -- p_employee_id -- Optional Input Employee Id.
391 -- p_workorder_id -- Mandatory Workorder ID.
392 -- p_operation_seq_num -- Mandatory Operation Seq Number
393 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
394 --
395 --
396 -- Description : This function returns the total hours assigned to an operation.
397 -- If the user role is technician or line maintenance technician, then the
398 -- total hours are calculated for that particular employee resource,
399 -- otherwise the total hours are calculated for all the person type
400 -- resources in the operation. If the employee id is not passed to the
401 -- function then the calculations are done for the user who is currently
402 -- logged into the application.
403 --
404
405 FUNCTION Get_Op_TotalHours_Assigned (p_employee_id IN NUMBER := NULL,
406 p_workorder_id IN NUMBER,
407 p_operation_seq_num IN NUMBER,
408 p_fnd_function_name IN VARCHAR2)
409 RETURN NUMBER;
410 --------------------------------------------------------------------------------------------
411
412
413 -- Start of Comments --
414 -- Function name : Get_Res_TotalHours_Assigned
415 --
416 -- Parameters :
417 --
418 -- p_employee_id -- Optional Input Employee Id.
419 -- p_workorder_id -- Mandatory Workorder ID.
420 -- p_operation_seq_num -- Mandatory Operation Seq Number
421 -- p_resource_id -- Mandatory resource ID.
422 -- p_resource_seq_num -- Mandatory resource ID.
423 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
424 --
425 --
426 -- Description : This function returns the total hours assigned for a specific resource
427 -- within an operation. If the employee id passed to the function is null,
428 -- then the calculations are done for the user who is currently logged
429 -- into the application.
430
431 FUNCTION Get_Res_TotalHours_Assigned (p_employee_id IN NUMBER := NULL,
432 p_workorder_id IN NUMBER,
433 p_operation_seq_num IN NUMBER,
434 p_resource_id IN NUMBER,
435 p_resource_seq_num IN NUMBER,
436 p_fnd_function_name IN VARCHAR2)
437 RETURN NUMBER;
438
439
440 --------------------------------------------------------------------------------------------
441 -- Start of Comments --
442 -- Function name : Get_Op_Transacted_Hours
443 --
444 -- Parameters :
445 -- p_employee_id -- Optional Input Employee Id.
446 -- p_wip_entity_id -- Mandatory Workorder ID.
447 -- p_operation_seq_num -- Mandatory Operation Seq Number
448 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
449 --
450 -- Description : This function returns the number of hours transacted by an employee
451 -- accross all resources within an operation. If the employee id passed to the
452 -- function is null then the calculations are based on the user currently logged
453 -- into the application.
454
455 FUNCTION Get_Op_Transacted_Hours (p_employee_id IN NUMBER := NULL,
456 p_wip_entity_id IN NUMBER,
457 p_operation_seq_num IN NUMBER,
458 p_fnd_function_name IN VARCHAR2)
459 RETURN NUMBER;
460
461
462 --------------------------------------------------------------------------------------------
463 -- Start of Comments --
464 -- Function name : Get_Res_Transacted_Hours
465 --
466 -- Parameters :
467 -- p_employee_id -- Optional Input Employee Id.
468 -- p_wip_entity_id -- Mandatory Workorder ID.
469 -- p_operation_seq_num -- Mandatory Operation Seq Number
470 -- p_resource_seq_num -- Mandatory Resource ID.
471 -- p_fnd_function_name -- Mandatory fnd_function to identify user role.
472 --
473 -- Description : This function returns the number of hours transacted by an employee
474 -- for a particular resource requirement within an operation if the user is
475 -- has a role of a technician or line maintenance technician. It returns the
476 -- number of hours transacted by all employees for a resource requirement
477 -- within an operation if the user is a data clerk.
478 --
479
483 p_resource_seq_num IN NUMBER,
480 FUNCTION Get_Res_Transacted_Hours (p_employee_id IN NUMBER := NULL,
481 p_wip_entity_id IN NUMBER,
482 p_operation_seq_num IN NUMBER,
484 p_fnd_function_name IN VARCHAR2)
485 RETURN NUMBER;
486
487 --------------------------------------------------------------------------------------------
488
489 -- Start of Comments --
490 -- Function name : Get_Op_Assigned_Start_Date
491 --
492 -- Parameters :
493 --
494 -- p_employee_id -- Optional Input Employee Id.
495 -- p_workorder_id -- Mandatory Workorder ID.
496 -- p_operation_seq_num -- Mandatory Operation Seq Number
497 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
498 --
499 --
500 -- Description : This function will be used to retrieve the Assigned Start Date for an
501 -- operation as displayed on the Operations subtab of the Update Workorders
502 -- page. The logic for retrieving the correct date is as follows:
503 -- 1. If the user is a technician, then assigned start time is the
504 -- assign_start_date for the employee if he is assigned to only
505 -- one resource within the operation.
506 -- If the employee is assigned to more than one resource within
507 -- the operation, then the assigned start date is the earliest
508 -- of all the assignment dates for the employee.
509 -- 2. If the user is a data clerk or a line maintenance technician,
510 -- then the assigned start date is the scheduled start date for the operation.
511 --
512 FUNCTION Get_Op_Assigned_Start_Date(p_employee_id IN NUMBER := NULL,
513 p_workorder_id IN NUMBER,
514 p_operation_seq_num IN NUMBER,
515 p_fnd_function_name IN VARCHAR2)
516 RETURN DATE;
517
518
519 -- Start of Comments --
520 -- Function name : Get_Op_Assigned_End_Date
521 --
522 -- Parameters :
523 --
524 -- p_employee_id -- Optional Input Employee Id.
525 -- p_workorder_id -- Mandatory Workorder ID.
526 -- p_operation_seq_num -- Mandatory Operation Seq Number
527 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
528 --
529 --
530 -- Description : This function will be used to retrieve the Assigned End Date for an
531 -- operation as displayed on the Operations subtab of the Update Workorders
532 -- page. The logic for retrieving the correct date is as follows:
533 -- 1. If the user is a technician, then assigned end time is the
534 -- assign_end_date for the employee if he is assigned to only
535 -- one resource within the operation.
536 -- If the employee is assigned to more than one resource within
537 -- the operation, then the assigned end date is the latest
538 -- of all the assignment dates for the employee.
539 --- 2. If the user is a data clerk or a line maintenance technician, then
540 -- the assigned end date is the scheduled start date for the operation.
541 --
542
543 FUNCTION Get_Op_Assigned_End_Date(p_employee_id IN NUMBER := NULL,
544 p_workorder_id IN NUMBER,
545 p_operation_seq_num IN NUMBER,
546 p_fnd_function_name IN VARCHAR2)
547 RETURN DATE;
548
549 -- Start of Comments --
550 -- Function name : Hr_To_Duration
551 -- Created by JKJ on 9th Jan 2009 for Bug No. 7658562. Fp Bug 8241923
552 -- Parameters :
553 -- p_hr -- Mandatory Input : Total Hours in Decimal Format.
554 -- Description :
555 -- This function returns a String in Hours:Minutes:Seconds format when given hours as input in decimal format.
556 --
557 FUNCTION Hr_To_Duration(
558 p_hr IN NUMBER
559 )
560 RETURN VARCHAR2 ;
561
562 --MANESING::NR Analysis, 02-Mar-2011, added following function
563 ----------------------------------------------------------------------------------------------
564 -- Start of Comments
565 -- Function name : Get_CMRO_Resource
566 -- Type : Public
567 -- Description : This function finds and returns the CMRO resource name corresponding to the
568 -- BOM resource that's associated with a work order operation.
569 -- Additionally, it takes into consideration the current state of CMRO resource i.e.
570 -- whether the BOM resource that was associated with CMRO resource when work order operation
571 -- was created, is still associated to it or not.
572 -- Pre-reqs :
573 -- Parameters : p_wip_entity_id IN NUMBER Required,
574 -- p_operation_seq_num IN NUMBER Required,
575 -- p_resource_seq_num IN NUMBER Required
576 -- p_bom_resource_id IN NUMBER Required
577 --
578 -- End of Comments
579 FUNCTION Get_CMRO_Resource
580 (
581 p_wip_entity_id IN NUMBER,
582 p_operation_seq_num IN NUMBER,
583 p_resource_seq_num IN NUMBER,
584 p_bom_resource_id IN NUMBER
585 ) RETURN VARCHAR2;
586 ----------------------------------------------------------------------------------------------
587 --sareepar added below function for Quality Integration
588 --Start
589 -- Checks whether a quality plan associated to the WO
590 -- If no plan is associated return "BLANK"
591 -- else if plan is associated
592 ---- and if results are collected return "QA_ENTERED"
593 ---- and if results are not collected return "QA_NOTENTERED"
594 --End
595 Function get_WO_QA_Status
596 (
597 P_workorder_id In Number
598 )Return VARCHAR2;
599
600 Function get_MR_QA_Status
601 (
602 P_ue_id In Number
603 )Return VARCHAR2;
604
605 Function get_OPR_QA_Status
606 (
607 P_workorder_operation_id In Number
608 )Return VARCHAR2;
609
610 Function get_Orgname_from_Orgid
611 (
612 P_org_id In Number
613 )Return VARCHAR2;
614
615 END AHL_PRD_UTIL_PKG; -- Package Specification AHL_PRD_UTIL_PKG
[Home] [Help]