1 PACKAGE AHL_PRD_UTIL_PKG AS
2 /* $Header: AHLUPRDS.pls 120.7.12000000.2 2007/10/06 00:21:19 sikumar 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
359 ------------------------------------------------------------------------------------------------
360 -- Start of Comments
361 -- Function name : Is_Wo_Cancel_Allowed
362 -- Type : Private
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.
423 --
420 -- p_operation_seq_num -- Mandatory Operation Seq Number
421 -- p_resource_id -- Mandatory resource ID.
422 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
424 --
425 -- Description : This function returns the total hours assigned for a specific resource
426 -- within an operation. If the employee id passed to the function is null,
427 -- then the calculations are done for the user who is currently logged
428 -- into the application.
429
430 FUNCTION Get_Res_TotalHours_Assigned (p_employee_id IN NUMBER := NULL,
431 p_workorder_id IN NUMBER,
432 p_operation_seq_num IN NUMBER,
433 p_resource_id IN NUMBER,
434 p_fnd_function_name IN VARCHAR2)
435 RETURN NUMBER;
436
437
438 --------------------------------------------------------------------------------------------
439 -- Start of Comments --
440 -- Function name : Get_Op_Transacted_Hours
441 --
442 -- Parameters :
443 -- p_employee_id -- Optional Input Employee Id.
444 -- p_wip_entity_id -- Mandatory Workorder ID.
445 -- p_operation_seq_num -- Mandatory Operation Seq Number
446 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
447 --
448 -- Description : This function returns the number of hours transacted by an employee
449 -- accross all resources within an operation. If the employee id passed to the
450 -- function is null then the calculations are based on the user currently logged
451 -- into the application.
452
453 FUNCTION Get_Op_Transacted_Hours (p_employee_id IN NUMBER := NULL,
454 p_wip_entity_id IN NUMBER,
455 p_operation_seq_num IN NUMBER,
456 p_fnd_function_name IN VARCHAR2)
457 RETURN NUMBER;
458
459
460 --------------------------------------------------------------------------------------------
461 -- Start of Comments --
462 -- Function name : Get_Res_Transacted_Hours
463 --
464 -- Parameters :
465 -- p_employee_id -- Optional Input Employee Id.
466 -- p_wip_entity_id -- Mandatory Workorder ID.
467 -- p_operation_seq_num -- Mandatory Operation Seq Number
468 -- p_resource_seq_num -- Mandatory Resource ID.
469 -- p_fnd_function_name -- Mandatory fnd_function to identify user role.
470 --
471 -- Description : This function returns the number of hours transacted by an employee
472 -- for a particular resource requirement within an operation if the user is
473 -- has a role of a technician or line maintenance technician. It returns the
474 -- number of hours transacted by all employees for a resource requirement
475 -- within an operation if the user is a data clerk.
476 --
477
478 FUNCTION Get_Res_Transacted_Hours (p_employee_id IN NUMBER := NULL,
479 p_wip_entity_id IN NUMBER,
480 p_operation_seq_num IN NUMBER,
481 p_resource_seq_num IN NUMBER,
482 p_fnd_function_name IN VARCHAR2)
483 RETURN NUMBER;
484
485 --------------------------------------------------------------------------------------------
486
487 -- Start of Comments --
488 -- Function name : Get_Op_Assigned_Start_Date
489 --
490 -- Parameters :
491 --
492 -- p_employee_id -- Optional Input Employee Id.
493 -- p_workorder_id -- Mandatory Workorder ID.
494 -- p_operation_seq_num -- Mandatory Operation Seq Number
495 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
496 --
497 --
498 -- Description : This function will be used to retrieve the Assigned Start Date for an
499 -- operation as displayed on the Operations subtab of the Update Workorders
500 -- page. The logic for retrieving the correct date is as follows:
501 -- 1. If the user is a technician, then assigned start time is the
502 -- assign_start_date for the employee if he is assigned to only
503 -- one resource within the operation.
504 -- If the employee is assigned to more than one resource within
505 -- the operation, then the assigned start date is the earliest
506 -- of all the assignment dates for the employee.
507 -- 2. If the user is a data clerk or a line maintenance technician,
508 -- then the assigned start date is the scheduled start date for the operation.
509 --
510 FUNCTION Get_Op_Assigned_Start_Date(p_employee_id IN NUMBER := NULL,
511 p_workorder_id IN NUMBER,
512 p_operation_seq_num IN NUMBER,
513 p_fnd_function_name IN VARCHAR2)
517 -- Start of Comments --
514 RETURN DATE;
515
516
518 -- Function name : Get_Op_Assigned_End_Date
519 --
520 -- Parameters :
521 --
522 -- p_employee_id -- Optional Input Employee Id.
523 -- p_workorder_id -- Mandatory Workorder ID.
524 -- p_operation_seq_num -- Mandatory Operation Seq Number
525 -- p_fnd_function_name -- Mandatory fnd_function to identify User role.
526 --
527 --
528 -- Description : This function will be used to retrieve the Assigned End Date for an
529 -- operation as displayed on the Operations subtab of the Update Workorders
530 -- page. The logic for retrieving the correct date is as follows:
531 -- 1. If the user is a technician, then assigned end time is the
532 -- assign_end_date for the employee if he is assigned to only
533 -- one resource within the operation.
534 -- If the employee is assigned to more than one resource within
535 -- the operation, then the assigned end date is the latest
536 -- of all the assignment dates for the employee.
537 --- 2. If the user is a data clerk or a line maintenance technician, then
538 -- the assigned end date is the scheduled start date for the operation.
539 --
540
541 FUNCTION Get_Op_Assigned_End_Date(p_employee_id IN NUMBER := NULL,
542 p_workorder_id IN NUMBER,
543 p_operation_seq_num IN NUMBER,
544 p_fnd_function_name IN VARCHAR2)
545 RETURN DATE;
546
547
548 END AHL_PRD_UTIL_PKG; -- Package Specification AHL_PRD_UTIL_PKG
[Home] [Help]