1 PACKAGE AHL_COMPLETIONS_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVPRCS.pls 120.13.12020000.2 2012/12/07 13:14:54 sareepar ship $ */
3
4 -- Common :-
5
6 TYPE workorder_rec_type IS RECORD
7 (
8 workorder_id NUMBER,
9 object_version_number NUMBER,
10 workorder_name VARCHAR2(80),
11 master_workorder_flag VARCHAR2(1),
12 wip_entity_id NUMBER,
13 organization_id NUMBER,
14 plan_id NUMBER,
15 collection_id NUMBER,
16 scheduled_start_date DATE,
17 scheduled_end_date DATE,
18 actual_start_date DATE,
19 actual_end_date DATE,
20 status_code VARCHAR2(30),
21 status VARCHAR2(80),
22 route_id NUMBER,
23 unit_effectivity_id NUMBER,
24 ue_object_version_number NUMBER,
25 automatic_signoff_flag VARCHAR2(1),
26 item_instance_id NUMBER,
27 completion_subinventory VARCHAR2(30),
28 completion_locator_id VARCHAR2(30),
29 lot_number mtl_lot_numbers.lot_number%TYPE,
30 serial_number VARCHAR2(30),
31 txn_quantity NUMBER
32 );
33
34 TYPE workorder_tbl_type IS TABLE OF workorder_rec_type INDEX BY BINARY_INTEGER;
35
36 TYPE operation_rec_type IS RECORD
37 (
38 workorder_operation_id NUMBER,
39 object_version_number NUMBER,
40 workorder_id NUMBER,
41 workorder_name VARCHAR2(80),
42 wip_entity_id NUMBER,
43 operation_sequence_num NUMBER,
44 organization_id NUMBER,
45 description VARCHAR2(2000),
46 plan_id NUMBER,
47 collection_id NUMBER,
48 scheduled_start_date DATE,
49 scheduled_end_date DATE,
50 actual_start_date DATE,
51 actual_end_date DATE,
52 status_code VARCHAR2(30),
53 status VARCHAR2(80)
54 );
55
56 TYPE operation_tbl_type IS TABLE OF operation_rec_type INDEX BY BINARY_INTEGER;
57
58 -- Complete Operation :-
59
60 PROCEDURE complete_operation
61 (
62 p_api_version IN NUMBER := 1.0,
63 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
64 p_commit IN VARCHAR2 := FND_API.G_FALSE,
65 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
66 p_default IN VARCHAR2 := FND_API.G_FALSE,
67 p_module_type IN VARCHAR2 := NULL,
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER,
70 x_msg_data OUT NOCOPY VARCHAR2,
71 p_workorder_operation_id IN NUMBER,
72 p_object_version_no IN NUMBER := NULL
73 );
74
75 -- Complete Workorder :-
76
77 PROCEDURE complete_workorder
78 (
79 p_api_version IN NUMBER := 1.0,
80 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
81 p_commit IN VARCHAR2 := FND_API.G_FALSE,
82 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
83 p_default IN VARCHAR2 := FND_API.G_FALSE,
84 p_module_type IN VARCHAR2 := NULL,
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_msg_data OUT NOCOPY VARCHAR2,
88 p_workorder_id IN NUMBER,
89 p_object_version_no IN NUMBER := NULL
90 );
91
92 -- Complete MR Instance :-
93
94 TYPE mr_rec_type IS RECORD
95 (
96 unit_effectivity_id NUMBER,
97 ue_object_version_no NUMBER,
98 ue_status VARCHAR2(80),
99 ue_status_code VARCHAR2(30),
100 mr_header_id NUMBER,
101 incident_id NUMBER,
102 mr_title VARCHAR2(80),
103 qa_inspection_type VARCHAR2(150),
104 qa_plan_id NUMBER,
105 qa_collection_id NUMBER,
106 item_instance_id NUMBER,
107 actual_end_date DATE
108 );
109
110 TYPE mr_tbl_type IS TABLE OF mr_rec_type INDEX BY BINARY_INTEGER;
111
112 TYPE counter_rec_type IS RECORD
113 (
114 item_instance_id NUMBER,
115 counter_id NUMBER,
116 counter_group_id NUMBER,
117 counter_value_id NUMBER,
118 counter_reading NUMBER,
119 prev_net_curr_diff NUMBER,
120 counter_type VARCHAR2(30),
121 reset_value NUMBER
122 );
123
124 TYPE counter_tbl_type IS TABLE OF counter_rec_type INDEX BY BINARY_INTEGER;
125
126 TYPE route_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
127
128 -- Start of Comments
129 -- Procedure name : complete_mr_instance
130 -- Type : Private
131 -- Pre-reqs :
132 -- Function :
133 -- Parameters :
134 --
135 -- Standard IN Parameters :
136 -- p_api_version NUMBER Required
137 -- p_init_msg_list VARCHAR2 Default FND_API.G_FALSE
138 -- p_commit VARCHAR2 Default FND_API.G_FALSE
139 -- p_validation_level NUMBER Default FND_API.G_VALID_LEVEL_FULL
140 -- p_default VARCHAR2 Default FND_API.G_TRUE
141 -- p_module_type VARCHAR2 Default NULL
142 --
143 -- Standard OUT Parameters :
144 -- x_return_status VARCHAR2 Required
145 -- x_msg_count NUMBER Required
146 -- x_msg_data VARCHAR2 Required
147 --
148 -- complete_mr_instance IN parameters:
149 -- None.
150 --
151 -- complete_mr_instance IN OUT parameters:
152 -- p_x_mr_rec mr_rec_type Required For Recursive call
153 --
154 -- complete_mr_instance OUT parameters:
155 -- None.
156 --
157 -- Version :
158 -- Current version 1.0
159 --
160 -- End of Comments
161
162
163 PROCEDURE complete_mr_instance
164 (
165 p_api_version IN NUMBER := 1.0,
166 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
167 p_commit IN VARCHAR2 := FND_API.G_FALSE,
168 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
169 p_default IN VARCHAR2 := FND_API.G_FALSE,
170 p_module_type IN VARCHAR2 := NULL,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 p_x_mr_rec IN OUT NOCOPY mr_rec_type
175 );
176
177 -- Defer Workorder :-
178
179 PROCEDURE defer_workorder
180 (
181 p_api_version IN NUMBER := 1.0,
182 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
183 p_commit IN VARCHAR2 := FND_API.G_FALSE,
184 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
185 p_default IN VARCHAR2 := FND_API.G_FALSE,
186 p_module_type IN VARCHAR2 := NULL,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2,
190 p_workorder_id IN NUMBER,
191 p_object_version_no IN NUMBER := NULL
192 );
193
194 -- Function to get the user-enter value 'Operation Status' or 'Workorder Status' QA Plan Element and to check if this value is 'Complete'.
195 FUNCTION validate_qa_status
196 (
197 p_plan_id IN NUMBER,
198 p_char_id IN NUMBER,
199 p_collection_id IN NUMBER
200 ) RETURN VARCHAR2;
201
202 -- Function to Get the status of a MR instance
203 FUNCTION get_mr_status
204 (
205 p_unit_effectivity_id IN NUMBER
206 ) RETURN VARCHAR2;
207
208 TYPE signoff_mr_rec_type IS RECORD
209 (
210 unit_effectivity_id NUMBER,
211 object_version_number NUMBER,
212 signoff_child_mrs_flag VARCHAR2(1),
213 complete_job_ops_flag VARCHAR2(1),
214 default_actual_dates_flag VARCHAR2(1),
215 actual_start_date DATE,
216 actual_end_date DATE,
217 --pekambar added for ER # 9504544 and 9274897
218 wo_comp_dates_flag VARCHAR2(1),
219 wo_childmr_dates_flag VARCHAR2(1),
220 transact_resource_flag VARCHAR2(1),
221 employee_number VARCHAR2(30),
222 serial_number VARCHAR2(30)
223 );
224
225 TYPE close_visit_rec_type IS RECORD
226 (
227 visit_id NUMBER,
228 object_version_number NUMBER,
229 signoff_mrs_flag VARCHAR2(1),
230 complete_job_ops_flag VARCHAR2(1),
231 default_actual_dates_flag VARCHAR2(1),
232 actual_start_date DATE,
233 actual_end_date DATE,
234 --pekambar added for ER # 9504544 and 9274897
235 wo_comp_dates_flag VARCHAR2(1),
236 wo_childmr_dates_flag VARCHAR2(1),
237 transact_resource_flag VARCHAR2(1),
238 employee_number VARCHAR2(30),
239 serial_number VARCHAR2(30),
240 --sukhwsin: SB Effectivity - Added check_unit_complete_flag
241 check_unit_complete_flag VARCHAR2(1)
242 );
243
244 TYPE resource_req_rec_type IS RECORD
245 (
246 wip_entity_id NUMBER,
247 workorder_name VARCHAR2(80),
248 workorder_id NUMBER,
249 workorder_operation_id NUMBER,
250 operation_seq_num NUMBER,
251 resource_seq_num NUMBER,
252 resource_name bom_resources.resource_code%TYPE,
253 organization_id NUMBER,
254 department_id NUMBER,
255 resource_id NUMBER,
256 resource_type NUMBER,
257 uom_code VARCHAR2(3),
258 usage_rate_or_amount NUMBER,
259 transaction_quantity NUMBER := 0
260 );
261
262 TYPE resource_req_tbl_type IS TABLE OF resource_req_rec_type INDEX BY BINARY_INTEGER;
263
264 PROCEDURE signoff_mr_instance
265 (
266 p_api_version IN NUMBER := 1.0,
267 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
268 p_commit IN VARCHAR2 := FND_API.G_FALSE,
269 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
270 p_default IN VARCHAR2 := FND_API.G_FALSE,
271 p_module_type IN VARCHAR2 := NULL,
272 x_return_status OUT NOCOPY VARCHAR2,
273 x_msg_count OUT NOCOPY NUMBER,
274 x_msg_data OUT NOCOPY VARCHAR2,
275 p_signoff_mr_rec IN signoff_mr_rec_type
276 );
277
278 PROCEDURE close_visit
279 (
280 p_api_version IN NUMBER := 1.0,
281 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
282 p_commit IN VARCHAR2 := FND_API.G_FALSE,
283 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
284 p_default IN VARCHAR2 := FND_API.G_FALSE,
285 p_module_type IN VARCHAR2 := NULL,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2,
289 p_close_visit_rec IN close_visit_rec_type
290 );
291
292 /*
293 -- NAME
294 -- PROCEDURE: Get_Default_Op_Actual_Dates
295 -- PARAMETERS
296 -- Standard IN Parameters
297 -- None
298 --
299 -- Standard OUT Parameters
300 -- x_return_status OUT NOCOPY VARCHAR2
301 -- x_msg_count OUT NOCOPY NUMBER
302 -- x_msg_data OUT NOCOPY VARCHAR2
303 --
304 -- Get_Default_Op_Actual_Dates Parameters
305 -- P_x_operation_tbl IN AHL_COMPLETIONS_PVT.operation_tbl_type - Table holding the operation records
306 --
307 -- DESCRIPTION
308 -- This function will be used to default the actual dates before completing operations using the
309 -- My Workorders or Update Workorders Uis. Calling APIs need to populate only the workorder_id and
310 -- operation_sequence_num fields of the operations records.
311 --
312 -- HISTORY
313 -- 16-Jun-2005 rroy Created
314 --*/
315
316 PROCEDURE Get_Default_Op_Actual_Dates
317 (
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_msg_count OUT NOCOPY NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2,
321 P_x_operation_tbl IN OUT NOCOPY AHL_COMPLETIONS_PVT.operation_tbl_type
322 );
323
324 /*
325 -- NAME
326 -- PROCEDURE: Get_Op_Actual_Dates
327 -- PARAMETERS
328 -- Standard IN Parameters
329 -- None
330 --
331 -- Standard OUT Parameters
332 -- x_return_status OUT NOCOPY VARCHAR2
333 --
334 -- Get_Op_Actual_Dates Parameters
335 -- P_x_operation_tbl IN AHL_COMPLETIONS_PVT.operation_tbl_type - Table holding the operation records
336 --
337 -- DESCRIPTION
338 -- This function will be used to retrieve the current actual dates of operations. This is API
339 -- is needed for the defaulting logic of actual dates on the Operations subtab of the
340 -- Update Workorders page. Calling APIs need to populate only the workorder_id and
341 -- operation_sequence_num fields of the operations records.
342 --
343 -- HISTORY
344 -- 16-Jun-2005 rroy Created
345 --*/
346
347 PROCEDURE Get_Op_Actual_Dates
348 (
349 x_return_status OUT NOCOPY VARCHAR2,
350 P_x_operation_tbl IN OUT NOCOPY AHL_COMPLETIONS_PVT.operation_tbl_type
351 );
352
353 /*
354 -- NAME
355 -- PROCEDURE: Get_Default_Wo_Actual_Dates
356 -- PARAMETERS
357 -- Standard IN Parameters
358 -- None
359 --
360 -- Standard OUT Parameters
361 -- x_return_status OUT NOCOPY VARCHAR2
362 --
363 -- Get_Op_Actual_Dates Parameters
364 -- p_workorder_id IN NUMBER - The workorder id for which the actual dates are retrieved
365 -- x_actual_start_date OUT NOCOPY DATE - Actual workorder start date
366 -- x_actual_end_date OUT NOCOPY DATE - Actual workorder end date
367 --
368 -- DESCRIPTION
369 -- This function will be used to default the actual dates before completing workorders using
370 -- the My Workorders or Update Workorders UIs. Calling APIs need to ensure that they call
371 -- this API after updating the operation actual dates.
372 --
373 -- HISTORY
377 PROCEDURE Get_Default_Wo_Actual_Dates
374 -- 16-Jun-2005 rroy Created
375 --*/
376
378 (
379 x_return_status OUT NOCOPY VARCHAR2,
380 p_workorder_id IN NUMBER,
381 x_actual_start_date OUT NOCOPY DATE,
382 x_actual_end_date OUT NOCOPY DATE
383 );
384
385 ------------------------------------------------------------------------------------------------
386 -- Function to check if the workorder completion operation can be carried out. Following factors
387 -- determine the same...
388 -- 1. Unit is quarantined.
389 -- 2. Workorder is in a status where it can be completed.
390 -- 3. Status of child workorders.
391 -- 4. Status of containing operations.
392 -- 5. Quality collection has been done for the workorder or not.
393 ------------------------------------------------------------------------------------------------
394 -- Start of Comments
395 -- Function name : Is_Complete_Enabled
396 -- Type : Private
397 -- Pre-reqs :
398 -- Parameters :
399 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
400 --
401 -- Standard IN Parameters :
402 -- None
403 --
404 -- Standard OUT Parameters :
405 -- None
406 --
407 -- Is_Complete_Enabled IN parameters:
408 -- P_operation_seq_num IN NUMBER
409 -- P_workorder_id IN NUMBER
410 -- p_ue_id IN NUMBER
411 --
412 -- Is_Complete_Enabled IN OUT parameters:
413 -- None
414 --
415 -- Is_Complete_Enabled OUT parameters:
416 -- None.
417 --
418 -- Version :
419 -- Current version 1.0
420 --
421 -- End of Comments
422
423 FUNCTION Is_Complete_Enabled(
424 p_workorder_id IN NUMBER,
425 P_operation_seq_num IN NUMBER,
426 p_ue_id IN NUMBER,
427 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
428 )
429 RETURN VARCHAR2;
430
431
432 -- Wrapper function to complete the visit master workorder
433 -- If the visit id is passed, then the visit master workorder id queried and completed
434 -- If the UE Id is passed, then the UE Master workorder is queried and completed
435 -- If the workorder id is passed, then the workorder is completed.
436 -- Bug 4626717 - Issue 6
437 FUNCTION complete_master_wo
438 (
439 p_visit_id IN NUMBER,
440 p_workorder_id IN NUMBER,
441 p_ue_id IN NUMBER
442 ) RETURN VARCHAR2;
443
444 -- function to get UE status.
445 FUNCTION get_ue_mr_status_code(p_unit_effectivity_id IN NUMBER) RETURN VARCHAR2;
446 ------------------------------------------------------------------------------------------------
447 -- API added for the concurrent program "Close Work Orders".
448 -- This API is to be used with Concurrent program.
449 -- Bug # 6991393 (FP for bug # 6500568)
450 ------------------------------------------------------------------------------------------------
451 PROCEDURE Close_WorkOrders (
452 errbuf OUT NOCOPY VARCHAR2,
453 retcode OUT NOCOPY NUMBER,
454 p_api_version IN NUMBER
455 );
456
457 -- Added function for FP ER# 6435803
458 -- Function to test whether all operations for a WO are complete
459 FUNCTION are_all_operations_complete
460 (
461 p_workorder_id IN NUMBER
462 ) RETURN VARCHAR2;
463
464
465
466 ---------------------------------------------------------------------------------------------------------
467 -- This method is to be used to validate the overlapping of sign off dates and the flight schedule dates.
468 -- Added for ER # 9274897 and 9504544
469 ---------------------------------------------------------------------------------------------------------
470 -- Start of Comments
471 -- Function name : Is_Signoff_Date_Overlapping
472 -- Type : Private
473 -- Pre-reqs :
474 -- Function :
475 -- Return Value : VARCHAR2
476 -- Parameters :
477 --
478 -- Standard IN Parameters :
479 -- None
480 --
481 -- Standard OUT Parameters :
482 -- None
483 --
484 -- is_super_user IN parameters:
485 -- None
486 --
487 -- is_super_user IN OUT parameters:
488 -- None
489 --
490 -- is_super_user OUT parameters:
491 -- None.
492 --
493 -- Version :
494 -- Current version 1.0
495 --
496 -- End of Comments
497 FUNCTION Is_Signoff_Date_Overlapping
498 (
499 p_actual_start_date IN DATE,
500 p_actual_end_date IN DATE,
501 p_unit_effectivity_id IN NUMBER,
502 p_visit_id IN NUMBER
503 ) RETURN VARCHAR2;
504
505
506
507 ------------------------------------------------------------------------------------------------
508 -- Function to check if the current user has authority to signoff MRs.
509 -- Added for ER # 9274897 and 9504544
510 ------------------------------------------------------------------------------------------------
511 -- Start of Comments
512 -- Function name : is_signoff_update_user
513 -- Type : Private
514 -- Pre-reqs :
515 -- Function :
516 -- Return Value : VARCHAR2
517 -- Parameters :
518 --
519 -- Standard IN Parameters :
520 -- None
521 --
522 -- Standard OUT Parameters :
523 -- None
524 --
525 -- is_super_user IN parameters:
526 -- None
527 --
528 -- is_super_user IN OUT parameters:
529 -- None
530 --
531 -- is_super_user OUT parameters:
535 -- Current version 1.0
532 -- None.
533 --
534 -- Version :
536 --
537 -- End of Comments
538 FUNCTION Is_Signoff_Update_User
539 RETURN VARCHAR2;
540
541 -- Changes made by jaramana on 14-DEC-2011
542 -- The following 4 functions were exposed in the package spec so that they can be
543 -- called from AHL_OSP_ACCOMP_PVT for performing OSP Accomplishments
544 FUNCTION get_reset_counters
545 (
546 p_mr_header_id IN NUMBER,
547 p_item_instance_id IN NUMBER,
548 p_actual_date IN DATE,
549 x_counter_tbl OUT NOCOPY counter_tbl_type
550 ) RETURN VARCHAR2;
551
552 FUNCTION reset_counters
553 (
554 p_mr_rec IN mr_rec_type,
555 p_x_counter_tbl IN OUT NOCOPY counter_tbl_type,
556 p_actual_end_date IN DATE,
557 x_msg_count OUT NOCOPY VARCHAR2,
558 x_msg_data OUT NOCOPY VARCHAR2
559 ) RETURN VARCHAR2;
560
561 FUNCTION get_cp_counters
562 (
563 p_item_instance_id IN NUMBER,
564 p_wip_entity_id IN NUMBER,
565 p_actual_date IN DATE,
566 x_counter_tbl OUT NOCOPY counter_tbl_type
567 ) RETURN VARCHAR2;
568
569 FUNCTION update_ump
570 (
571 p_unit_effectivity_id IN NUMBER,
572 p_ue_object_version IN NUMBER,
573 p_actual_end_date IN DATE,
574 p_counter_tbl IN counter_tbl_type,
575 p_dml_flag IN VARCHAR2,
576 x_msg_count OUT NOCOPY VARCHAR2,
577 x_msg_data OUT NOCOPY VARCHAR2
578 ) RETURN VARCHAR2;
579
580 END AHL_COMPLETIONS_PVT;
[Home] [Help]