1 PACKAGE AHL_COMPLETIONS_PVT AS
2 /* $Header: AHLVPRCS.pls 120.7.12010000.3 2009/01/09 22:31:12 sikumar 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 transact_resource_flag VARCHAR2(1),
218 employee_number VARCHAR2(30),
219 serial_number VARCHAR2(30)
220 );
221
222 TYPE close_visit_rec_type IS RECORD
223 (
224 visit_id NUMBER,
225 object_version_number NUMBER,
226 signoff_mrs_flag VARCHAR2(1),
227 complete_job_ops_flag VARCHAR2(1),
228 default_actual_dates_flag VARCHAR2(1),
229 actual_start_date DATE,
230 actual_end_date DATE,
231 transact_resource_flag VARCHAR2(1),
232 employee_number VARCHAR2(30),
233 serial_number VARCHAR2(30)
234 );
235
236 TYPE resource_req_rec_type IS RECORD
237 (
238 wip_entity_id NUMBER,
239 workorder_name VARCHAR2(80),
240 workorder_id NUMBER,
241 workorder_operation_id NUMBER,
242 operation_seq_num NUMBER,
243 resource_seq_num NUMBER,
244 resource_name bom_resources.resource_code%TYPE,
245 organization_id NUMBER,
246 department_id NUMBER,
247 resource_id NUMBER,
248 resource_type NUMBER,
249 uom_code VARCHAR2(3),
250 usage_rate_or_amount NUMBER,
251 transaction_quantity NUMBER := 0
252 );
253
254 TYPE resource_req_tbl_type IS TABLE OF resource_req_rec_type INDEX BY BINARY_INTEGER;
255
256 PROCEDURE signoff_mr_instance
257 (
258 p_api_version IN NUMBER := 1.0,
259 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
260 p_commit IN VARCHAR2 := FND_API.G_FALSE,
261 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
262 p_default IN VARCHAR2 := FND_API.G_FALSE,
263 p_module_type IN VARCHAR2 := NULL,
264 x_return_status OUT NOCOPY VARCHAR2,
265 x_msg_count OUT NOCOPY NUMBER,
266 x_msg_data OUT NOCOPY VARCHAR2,
267 p_signoff_mr_rec IN signoff_mr_rec_type
268 );
269
270 PROCEDURE close_visit
271 (
272 p_api_version IN NUMBER := 1.0,
273 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
274 p_commit IN VARCHAR2 := FND_API.G_FALSE,
275 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
276 p_default IN VARCHAR2 := FND_API.G_FALSE,
277 p_module_type IN VARCHAR2 := NULL,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 x_msg_data OUT NOCOPY VARCHAR2,
281 p_close_visit_rec IN close_visit_rec_type
282 );
283
284 /*
285 -- NAME
286 -- PROCEDURE: Get_Default_Op_Actual_Dates
287 -- PARAMETERS
288 -- Standard IN Parameters
289 -- None
290 --
291 -- Standard OUT Parameters
292 -- x_return_status OUT NOCOPY VARCHAR2
293 -- x_msg_count OUT NOCOPY NUMBER
294 -- x_msg_data OUT NOCOPY VARCHAR2
295 --
296 -- Get_Default_Op_Actual_Dates Parameters
297 -- P_x_operation_tbl IN AHL_COMPLETIONS_PVT.operation_tbl_type - Table holding the operation records
298 --
299 -- DESCRIPTION
300 -- This function will be used to default the actual dates before completing operations using the
301 -- My Workorders or Update Workorders Uis. Calling APIs need to populate only the workorder_id and
302 -- operation_sequence_num fields of the operations records.
303 --
304 -- HISTORY
305 -- 16-Jun-2005 rroy Created
306 --*/
307
308 PROCEDURE Get_Default_Op_Actual_Dates
309 (
310 x_return_status OUT NOCOPY VARCHAR2,
311 x_msg_count OUT NOCOPY NUMBER,
312 x_msg_data OUT NOCOPY VARCHAR2,
313 P_x_operation_tbl IN OUT NOCOPY AHL_COMPLETIONS_PVT.operation_tbl_type
314 );
315
316 /*
317 -- NAME
318 -- PROCEDURE: Get_Op_Actual_Dates
319 -- PARAMETERS
320 -- Standard IN Parameters
321 -- None
322 --
323 -- Standard OUT Parameters
324 -- x_return_status OUT NOCOPY VARCHAR2
325 --
326 -- Get_Op_Actual_Dates Parameters
327 -- P_x_operation_tbl IN AHL_COMPLETIONS_PVT.operation_tbl_type - Table holding the operation records
328 --
329 -- DESCRIPTION
330 -- This function will be used to retrieve the current actual dates of operations. This is API
331 -- is needed for the defaulting logic of actual dates on the Operations subtab of the
332 -- Update Workorders page. Calling APIs need to populate only the workorder_id and
333 -- operation_sequence_num fields of the operations records.
334 --
335 -- HISTORY
336 -- 16-Jun-2005 rroy Created
337 --*/
338
339 PROCEDURE Get_Op_Actual_Dates
340 (
341 x_return_status OUT NOCOPY VARCHAR2,
342 P_x_operation_tbl IN OUT NOCOPY AHL_COMPLETIONS_PVT.operation_tbl_type
343 );
344
345 /*
346 -- NAME
347 -- PROCEDURE: Get_Default_Wo_Actual_Dates
348 -- PARAMETERS
349 -- Standard IN Parameters
350 -- None
351 --
352 -- Standard OUT Parameters
353 -- x_return_status OUT NOCOPY VARCHAR2
354 --
355 -- Get_Op_Actual_Dates Parameters
356 -- p_workorder_id IN NUMBER - The workorder id for which the actual dates are retrieved
357 -- x_actual_start_date OUT NOCOPY DATE - Actual workorder start date
358 -- x_actual_end_date OUT NOCOPY DATE - Actual workorder end date
359 --
360 -- DESCRIPTION
361 -- This function will be used to default the actual dates before completing workorders using
362 -- the My Workorders or Update Workorders UIs. Calling APIs need to ensure that they call
363 -- this API after updating the operation actual dates.
364 --
365 -- HISTORY
366 -- 16-Jun-2005 rroy Created
367 --*/
368
369 PROCEDURE Get_Default_Wo_Actual_Dates
370 (
371 x_return_status OUT NOCOPY VARCHAR2,
372 p_workorder_id IN NUMBER,
373 x_actual_start_date OUT NOCOPY DATE,
374 x_actual_end_date OUT NOCOPY DATE
375 );
376
377 ------------------------------------------------------------------------------------------------
378 -- Function to check if the workorder completion operation can be carried out. Following factors
379 -- determine the same...
380 -- 1. Unit is quarantined.
381 -- 2. Workorder is in a status where it can be completed.
382 -- 3. Status of child workorders.
383 -- 4. Status of containing operations.
384 -- 5. Quality collection has been done for the workorder or not.
385 ------------------------------------------------------------------------------------------------
386 -- Start of Comments
387 -- Function name : Is_Complete_Enabled
388 -- Type : Private
389 -- Pre-reqs :
390 -- Parameters :
391 -- Return : FND_API.G_TRUE or FND_API.G_FALSE.
392 --
393 -- Standard IN Parameters :
394 -- None
395 --
396 -- Standard OUT Parameters :
397 -- None
398 --
399 -- Is_Complete_Enabled IN parameters:
400 -- P_operation_seq_num IN NUMBER
401 -- P_workorder_id IN NUMBER
402 -- p_ue_id IN NUMBER
403 --
404 -- Is_Complete_Enabled IN OUT parameters:
405 -- None
406 --
407 -- Is_Complete_Enabled OUT parameters:
408 -- None.
409 --
410 -- Version :
411 -- Current version 1.0
412 --
413 -- End of Comments
417 P_operation_seq_num IN NUMBER,
414
415 FUNCTION Is_Complete_Enabled(
416 p_workorder_id IN NUMBER,
418 p_ue_id IN NUMBER,
419 p_check_unit IN VARCHAR2 DEFAULT FND_API.G_TRUE
420 )
421 RETURN VARCHAR2;
422
423
424 -- Wrapper function to complete the visit master workorder
425 -- If the visit id is passed, then the visit master workorder id queried and completed
426 -- If the UE Id is passed, then the UE Master workorder is queried and completed
427 -- If the workorder id is passed, then the workorder is completed.
428 -- Bug 4626717 - Issue 6
429 FUNCTION complete_master_wo
430 (
431 p_visit_id IN NUMBER,
432 p_workorder_id IN NUMBER,
433 p_ue_id IN NUMBER
434 ) RETURN VARCHAR2;
435
436 -- function to get UE status.
437 FUNCTION get_ue_mr_status_code(p_unit_effectivity_id IN NUMBER) RETURN VARCHAR2;
438 ------------------------------------------------------------------------------------------------
439 -- API added for the concurrent program "Close Work Orders".
440 -- This API is to be used with Concurrent program.
441 -- Bug # 6991393 (FP for bug # 6500568)
442 ------------------------------------------------------------------------------------------------
443 PROCEDURE Close_WorkOrders (
444 errbuf OUT NOCOPY VARCHAR2,
445 retcode OUT NOCOPY NUMBER,
446 p_api_version IN NUMBER
447 );
448
449 -- Added function for FP ER# 6435803
450 -- Function to test whether all operations for a WO are complete
451 FUNCTION are_all_operations_complete
452 (
453 p_workorder_id IN NUMBER
454 ) RETURN VARCHAR2;
455
456
457 END AHL_COMPLETIONS_PVT;
[Home] [Help]