[Home] [Help]
PACKAGE BODY: APPS.EAM_CREATEUPDATE_WO_PVT
Source
1 PACKAGE BODY EAM_CREATEUPDATE_WO_PVT AS
2 /* $Header: EAMVCUWB.pls 120.20 2008/06/06 21:58:58 jvittes ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVCUWB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_CREATEUPDATE_WO_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 ***************************************************************************/
20
21
22 /*******************************
23 Procedure to create a workorder from
24 another workorder.This procedure calls workorder API to copy the workorder
25 *******************************/
26 PROCEDURE COPY_WORKORDER
27 (
28 p_init_msg_list IN VARCHAR2
29 , p_commit IN VARCHAR2
30 , p_wip_entity_id IN NUMBER
31 , p_organization_id IN NUMBER
32 , x_return_status OUT NOCOPY VARCHAR2
33 , x_wip_entity_name OUT NOCOPY VARCHAR2
34 ,x_wip_entity_id OUT NOCOPY NUMBER
35 )
36 IS
37 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
38 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
39 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
40 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
41 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
42 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
43 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
44 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
45 l_msg_count NUMBER;
46
47 BEGIN
48
49 SAVEPOINT COPY_WO;
50
51 EAM_PROCESS_WO_PUB.COPY_WORKORDER
52 ( p_bo_identifier =>'EAM'
53 , p_api_version_number =>1.0
54 , p_init_msg_list => p_init_msg_list
55 , p_commit => FND_API.G_FALSE
56 , p_wip_entity_id =>p_wip_entity_id
57 , p_organization_id => p_organization_id
58 , x_eam_wo_rec => l_eam_wo_rec
59 , x_eam_op_tbl => l_eam_op_tbl
60 , x_eam_op_network_tbl => l_eam_op_network_tbl
61 , x_eam_res_tbl => l_eam_res_tbl
62 , x_eam_res_inst_tbl => l_eam_res_inst_tbl
63 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
64 , x_eam_mat_req_tbl => l_eam_mat_req_tbl
65 , x_eam_direct_items_tbl => l_eam_direct_items_tbl
66 , x_return_status => x_return_status
67 , x_msg_count => l_msg_count
68 );
69
70 IF(NVL(x_return_status,'U') <> 'S') THEN
71 ROLLBACK TO COPY_WO;
72 RETURN;
73 END IF;
74
75 IF(x_return_status = 'S' ) THEN
76 x_wip_entity_name := l_eam_wo_rec.wip_entity_name;
77 x_wip_entity_id := l_eam_wo_rec.wip_entity_id;
78 IF(p_commit = FND_API.G_TRUE) THEN
79 COMMIT;
80 END IF;
81 END IF;
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 ROLLBACK TO COPY_WO;
86 x_return_status := 'U';
87 END COPY_WORKORDER;
88
89
90 /*********************************************************
91 Wrapper procedure on top of WO API.This is used to create/update workorder and its related entities
92 ************************************************/
93 PROCEDURE CREATE_UPDATE_WO
94 (
95 p_commit IN VARCHAR2 := FND_API.G_FALSE,
96 p_eam_wo_tbl IN EAM_PROCESS_WO_PUB.eam_wo_tbl_type,
97 p_eam_wo_relations_tbl IN EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type,
98 p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type,
99 p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type,
100 p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type,
101 p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type,
102 p_eam_mat_req_tbl IN EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type,
103 p_eam_direct_items_tbl IN EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type,
104 p_eam_request_tbl IN EAM_PROCESS_WO_PUB.eam_request_tbl_type,
105 p_eam_wo_comp_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type,
106 p_eam_meter_reading_tbl IN EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type,
107 p_eam_counter_prop_tbl IN EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type,
108 p_eam_wo_comp_rebuild_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type,
109 p_eam_wo_comp_mr_read_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type,
110 p_prev_activity_id IN NUMBER,
111 p_failure_id IN NUMBER := null,
112 p_failure_date IN DATE := null,
113 p_failure_entry_id IN NUMBER := null,
114 p_failure_code IN VARCHAR2 := null,
115 p_cause_code IN VARCHAR2 := null,
116 p_resolution_code IN VARCHAR2 := null,
117 p_failure_comments IN VARCHAR2 := null,
118 p_failure_code_required IN VARCHAR2 DEFAULT NULL,
119 x_wip_entity_id OUT NOCOPY NUMBER,
120 x_return_status OUT NOCOPY VARCHAR2,
121 x_msg_count OUT NOCOPY NUMBER
122 )
123 IS
124 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
125 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
126 l_import_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type; --MSP Project
127 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
128 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
129 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
130 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
131 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
132 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
133 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
134
135 l_eam_wo_relations_tbl_out EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
136 l_eam_wo_rec_out EAM_PROCESS_WO_PUB.eam_wo_rec_type;
137 l_eam_wo_tbl_out EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
138 l_eam_op_tbl_out EAM_PROCESS_WO_PUB.eam_op_tbl_type;
139 l_eam_op_network_tbl_out EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
140 l_eam_res_tbl_out EAM_PROCESS_WO_PUB.eam_res_tbl_type;
141 l_eam_res_usage_tbl_out EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
142 l_eam_res_inst_tbl_out EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
143 l_eam_sub_res_tbl_out EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
144 l_eam_mat_req_tbl_out EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
145 l_eam_direct_items_tbl_out EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
146 l_eam_wo_comp_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
147 l_eam_wo_quality_tbl_out EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
148 l_eam_meter_reading_tbl_out EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
149 l_eam_counter_prop_tbl_out EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
150 l_eam_wo_comp_rebuild_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
151 l_eam_wo_comp_mr_read_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
152 l_eam_op_comp_tbl_out EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
153 l_eam_request_tbl_out EAM_PROCESS_WO_PUB.eam_request_tbl_type;
154 l_eam_wo_comp_rec_out EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
155
156
157 l_output_dir VARCHAR2(512);
158
159 CURSOR resources
160 (l_resource_id NUMBER,l_organization_id NUMBER)
161 IS
162 SELECT autocharge_type
163 FROM BOM_RESOURCES
164 WHERE resource_id = l_resource_id
165 AND organization_id = l_organization_id;
166
167 l_resources resources%ROWTYPE;
168 l_wip_entity_id NUMBER;
169 l_asset_group_id NUMBER;
170 l_asset_number VARCHAR2(30);
171 l_rebuild_item_id NUMBER;
172 l_rebuild_serial_number VARCHAR2(30);
173
174 /* Added for bug#4555609 */
175 l_prev_activity_id NUMBER;
176 l_prev_description VARCHAR2(240);
177 l_prev_priority NUMBER;
178 l_prev_work_order_type VARCHAR2(30);
179 l_prev_shutdown_type VARCHAR2(30);
180 l_prev_activity_type VARCHAR2(30);
181 l_prev_activity_cause VARCHAR2(30);
182 l_prev_activity_source VARCHAR2(30);
183 l_prev_attribute_category VARCHAR2(30);
184 l_prev_attribute1 VARCHAR2(150);
185 l_prev_attribute2 VARCHAR2(150);
186 l_prev_attribute3 VARCHAR2(150);
187 l_prev_attribute4 VARCHAR2(150);
188 l_prev_attribute5 VARCHAR2(150);
189 l_prev_attribute6 VARCHAR2(150);
190 l_prev_attribute7 VARCHAR2(150);
191 l_prev_attribute8 VARCHAR2(150);
192 l_prev_attribute9 VARCHAR2(150);
193 l_prev_attribute10 VARCHAR2(150);
194 l_prev_attribute11 VARCHAR2(150);
195 l_prev_attribute12 VARCHAR2(150);
196 l_prev_attribute13 VARCHAR2(150);
197 l_prev_attribute14 VARCHAR2(150);
198 l_prev_attribute15 VARCHAR2(150);
199 l_maintenance_object_type NUMBER;
200 l_maintenance_object_id NUMBER;
201 /* Added for bug#6053425 Start */
202 l_prev_project_id NUMBER;
203 l_prev_task_id NUMBER;
204 /* Added for bug#6053425 End */
205
206 l_eam_failure_entry_record EAM_Process_Failure_Entry_PUB.eam_failure_entry_record_typ;
207 l_eam_failure_codes_tbl EAM_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ;
208 l_fail_dept_id NUMBER;
209 l_eam_location_id NUMBER;
210 l_eam_failure_code_required varchar2(30);
211 l_wo_exists BOOLEAN;
212 l_org_id NUMBER;
213 l_validate BOOLEAN;
214 l_error_segments number;
215 l_error_message varchar2(2000);
216
217 BEGIN
218
219 /* get output directory path from database */
220 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
221
222 SAVEPOINT create_update_wo;
223 /*MSP Project addition*/
224 IF(p_eam_wo_tbl IS NOT NULL) THEN
225 IF(p_eam_wo_tbl.first is not NULL) THEN
226 l_import_eam_wo_rec := p_eam_wo_tbl(p_eam_wo_tbl.first);
227 END IF;
228 END IF;
229
230 IF(l_import_eam_wo_rec.ATTRIBUTE15='import') THEN
231 eam_import_workorders.import_workorders(p_commit,
232 p_eam_wo_tbl,
233 p_eam_wo_relations_tbl,
234 p_eam_op_tbl,
235 p_eam_res_tbl,
236 p_eam_res_inst_tbl,
237 p_eam_res_usage_tbl,
238 p_eam_mat_req_tbl,
239 p_eam_direct_items_tbl,
240 p_eam_request_tbl,
241 p_eam_wo_comp_tbl,
242 p_eam_meter_reading_tbl,
243 p_eam_counter_prop_tbl,
244 p_eam_wo_comp_rebuild_tbl,
245 p_eam_wo_comp_mr_read_tbl,
246 x_wip_entity_id,
247 x_return_status,
248 x_msg_count);
249 ELSE /*MSP code end*/
250
251 l_eam_wo_comp_tbl := p_eam_wo_comp_tbl;
252
253 IF(l_eam_wo_comp_tbl IS NOT NULL AND l_eam_wo_comp_tbl.COUNT>0) THEN
254
255 l_eam_wo_comp_rec := l_eam_wo_comp_tbl(l_eam_wo_comp_tbl.FIRST);
256
257 /* Failure Analysis Project Start */
258
259 l_eam_failure_entry_record.failure_id := p_failure_id;
260 l_eam_failure_entry_record.failure_date := p_failure_date;
261
262 l_eam_failure_codes_tbl(1).failure_id := p_failure_id;
263 l_eam_failure_codes_tbl(1).failure_entry_id := p_failure_entry_id;
264 l_eam_failure_codes_tbl(1).failure_code := p_failure_code;
265 l_eam_failure_codes_tbl(1).cause_code := p_cause_code;
266 l_eam_failure_codes_tbl(1).resolution_code := p_resolution_code;
267 l_eam_failure_codes_tbl(1).comments := p_failure_comments;
268
269
270 SELECT
271 maintenance_object_type
272 ,maintenance_object_id
273 INTO
274 l_maintenance_object_type
275 ,l_maintenance_object_id
276 FROM WIP_DISCRETE_JOBS
277 WHERE wip_entity_id = l_eam_wo_comp_rec.wip_entity_id;
278
279 --only if asset number/rebuild serial number exists for work order
280 IF(l_maintenance_object_type =3) THEN
281 BEGIN
282
283 --if workorder dept. is null,de fault it from asset's owning dept
284
285 SELECT OWNING_DEPARTMENT_ID
286 INTO l_fail_dept_id
287 FROM eam_org_maint_defaults
288 WHERE object_id =l_maintenance_object_id
289 AND object_type = 50
290 AND organization_id =l_eam_wo_comp_rec.organization_id;
291
292 SELECT area_id
293 INTO l_eam_location_id
294 FROM eam_org_maint_defaults
295 WHERE object_id = l_maintenance_object_id
296 AND object_type = 50
297 AND organization_id = l_eam_wo_comp_rec.organization_id;
298
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 NULL;
302 END;
303 END IF; --end of check for mainteannce_object_type =3
304
305
306 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
307 l_eam_failure_entry_record.source_type := 1;
308 l_eam_failure_entry_record.source_id := l_eam_wo_comp_rec.wip_entity_id;
309 l_eam_failure_entry_record.object_type := l_maintenance_object_type;
310 l_eam_failure_entry_record.object_id := l_maintenance_object_id;
311 l_eam_failure_entry_record.maint_organization_id := l_eam_wo_comp_rec.organization_id;
312 l_eam_failure_entry_record.current_organization_id := l_eam_wo_comp_rec.organization_id;
313 l_eam_failure_entry_record.department_id := l_fail_dept_id;
314 l_eam_failure_entry_record.area_id := l_eam_location_id;
315
316 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
317
318 IF (l_eam_failure_entry_record.failure_date IS NULL) THEN
319 l_eam_failure_entry_record.failure_date := FND_API.G_MISS_DATE;
320 END IF;
321
322 IF (l_eam_failure_codes_tbl(1).failure_code IS NULL) THEN
323 l_eam_failure_codes_tbl(1).failure_code := FND_API.G_MISS_CHAR;
324 END IF;
325
326 IF (l_eam_failure_codes_tbl(1).cause_code IS NULL) THEN
327 l_eam_failure_codes_tbl(1).cause_code := FND_API.G_MISS_CHAR;
328 END IF;
329
330 IF (l_eam_failure_codes_tbl(1).resolution_code IS NULL) THEN
331 l_eam_failure_codes_tbl(1).resolution_code := FND_API.G_MISS_CHAR;
332 END IF;
333
334 IF (l_eam_failure_codes_tbl(1).comments IS NULL) THEN
335 l_eam_failure_codes_tbl(1).comments := FND_API.G_MISS_CHAR;
336 END IF;
337
338 IF(l_eam_failure_entry_record.failure_id IS NOT NULL ) THEN
339 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
340 ELSE
341 IF(l_eam_failure_entry_record.failure_date = FND_API.G_MISS_DATE) THEN
342 l_eam_failure_entry_record.transaction_type :=null;
343 l_eam_failure_entry_record.failure_date :=null;
344 ELSE
345 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE ;
346 END IF;
347 END IF;
348
349 IF(l_eam_failure_codes_tbl(1).failure_entry_id IS NOT NULL) THEN
350 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
351 l_eam_wo_comp_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
352
353 ELSE
354 IF( NOT( (l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR)
355 AND (l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR)
356 AND (l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR)
357 AND (l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR)
358 )
359 ) THEN
360 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
361
362 IF(l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR) THEN
363 l_eam_failure_codes_tbl(1).failure_code := null;
364 END IF;
365
366 IF(l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR) THEN
367 l_eam_failure_codes_tbl(1).cause_code := NULL;
368 END IF;
369
370 IF(l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR) then
371 l_eam_failure_codes_tbl(1).resolution_code := null;
372 END IF;
373
374 IF(l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR) then
375 l_eam_failure_codes_tbl(1).comments := null;
376 END IF;
377 l_eam_wo_comp_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
378
379 ELSE
380 l_eam_failure_codes_tbl.delete;
381 l_eam_wo_comp_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
382
383 END IF;
384 END IF;
385
386 l_eam_wo_comp_rec.eam_failure_entry_record := l_eam_failure_entry_record;
387 l_eam_wo_comp_tbl(l_eam_wo_comp_tbl.FIRST) := l_eam_wo_comp_rec;
388
389 END IF;
390
391 /* Failure Analysis Project End */
392
393 --Set the activity_id to Fnd_Api.G_Miss_Num if we want to delete the activity
394 l_eam_wo_tbl := p_eam_wo_tbl;
395 IF(l_eam_wo_tbl IS NOT NULL AND l_eam_wo_tbl.COUNT>0) THEN
396
397 l_eam_wo_rec := l_eam_wo_tbl(l_eam_wo_tbl.FIRST);
398
399 --set FND_API.G_MISS_xx if any columns are nulled out
400 l_wo_exists := TRUE; --work order already exists...if no data found exception is thrown then l_wo_exists will be set to FALSE
401
402 BEGIN
403 l_wip_entity_id :=l_eam_wo_rec.wip_entity_id;
404
405 SELECT asset_number
406 ,asset_group_id
407 ,rebuild_serial_number
408 ,rebuild_item_id
409 ,primary_item_id
410 ,description
411 ,priority
412 ,work_order_type
413 ,shutdown_type
414 ,activity_type
415 ,activity_cause
416 ,activity_source
417 ,attribute_category
418 ,attribute1
419 ,attribute2
420 ,attribute3
421 ,attribute4
422 ,attribute5
423 ,attribute6
424 ,attribute7
425 ,attribute8
426 ,attribute9
427 ,attribute10
428 ,attribute11
429 ,attribute12
430 ,attribute13
431 ,attribute14
432 ,attribute15
433 /* Added for bug#6053425 Start */
434 ,project_id
435 ,task_id
436 /* Added for bug#6053425 End */
437 INTO l_asset_number
438 ,l_asset_group_id
439 ,l_rebuild_serial_number
440 ,l_rebuild_item_id
441 ,l_prev_activity_id
442 ,l_prev_description
443 ,l_prev_priority
444 ,l_prev_work_order_type
445 ,l_prev_shutdown_type
446 ,l_prev_activity_type
447 ,l_prev_activity_cause
448 ,l_prev_activity_source
449 ,l_prev_attribute_category
450 ,l_prev_attribute1
451 ,l_prev_attribute2
452 ,l_prev_attribute3
453 ,l_prev_attribute4
454 ,l_prev_attribute5
455 ,l_prev_attribute6
456 ,l_prev_attribute7
457 ,l_prev_attribute8
458 ,l_prev_attribute9
459 ,l_prev_attribute10
460 ,l_prev_attribute11
461 ,l_prev_attribute12
462 ,l_prev_attribute13
463 ,l_prev_attribute14
464 ,l_prev_attribute15
465 /* Code Added for bug#6053425 Start */
466 ,l_prev_project_id
467 ,l_prev_task_id
468 /* Code Added for bug#6053425 End */
469 FROM WIP_DISCRETE_JOBS
470 where wip_entity_id = l_wip_entity_id;
471
472 IF(l_asset_number is NOT NULL and l_eam_wo_rec.asset_number IS NULL) THEN
473 l_eam_wo_rec.asset_number := FND_API.G_MISS_CHAR;
474 END IF;
475
476 IF(l_rebuild_serial_number is NOT NULL and l_eam_wo_rec.rebuild_serial_number IS NULL) THEN
477 l_eam_wo_rec.rebuild_serial_number := FND_API.G_MISS_CHAR;
478 END IF;
479
480 IF(l_asset_group_id is NOT NULL and l_eam_wo_rec.asset_group_id IS NULL) THEN
481 l_eam_wo_rec.asset_group_id := FND_API.G_MISS_NUM;
482 END IF;
483
484 IF(l_rebuild_item_id is NOT NULL and l_eam_wo_rec.rebuild_item_id IS NULL) THEN
485 l_eam_wo_rec.rebuild_item_id:= FND_API.G_MISS_NUM;
486 END IF;
487
488 IF l_prev_activity_id is not null and l_eam_wo_rec.asset_activity_id is null THEN
489 l_eam_wo_rec.asset_activity_id := FND_API.G_MISS_NUM;
490 END IF;
491
492 IF l_prev_description is not null and l_eam_wo_rec.description is null THEN
493 l_eam_wo_rec.description := FND_API.G_MISS_CHAR;
494 END IF;
495
496 IF l_prev_activity_type is not null and l_eam_wo_rec.activity_type is null THEN
497 l_eam_wo_rec.activity_type := FND_API.G_MISS_CHAR;
498 END IF;
499
500 IF l_prev_activity_cause is not null and l_eam_wo_rec.activity_cause is null THEN
501 l_eam_wo_rec.activity_cause := FND_API.G_MISS_CHAR;
502 END IF;
503
504 IF l_prev_activity_source is not null and l_eam_wo_rec.activity_source is null THEN
505 l_eam_wo_rec.activity_source := FND_API.G_MISS_CHAR;
506 END IF;
507
508 IF l_prev_shutdown_type is not null and l_eam_wo_rec.shutdown_type is null THEN
509 l_eam_wo_rec.shutdown_type := FND_API.G_MISS_CHAR;
510 END IF;
511
512 IF l_prev_priority is not null and l_eam_wo_rec.priority is null THEN
513 l_eam_wo_rec.priority := FND_API.G_MISS_NUM;
514 END IF;
515
516 IF l_prev_work_order_type is not null and l_eam_wo_rec.work_order_type is null THEN
517 l_eam_wo_rec.work_order_type := FND_API.G_MISS_CHAR;
518 END IF;
519
520 IF l_prev_attribute_category is not null and l_eam_wo_rec.attribute_category is null THEN
521 l_eam_wo_rec.attribute_category := FND_API.G_MISS_CHAR;
522 END IF;
523
524 IF l_prev_attribute1 is not null and l_eam_wo_rec.attribute1 is null THEN
525 l_eam_wo_rec.attribute1 := FND_API.G_MISS_CHAR;
526 END IF;
527
528 IF l_prev_attribute2 is not null and l_eam_wo_rec.attribute2 is null THEN
529 l_eam_wo_rec.attribute2 := FND_API.G_MISS_CHAR;
530 END IF;
531
532 IF l_prev_attribute3 is not null and l_eam_wo_rec.attribute3 is null THEN
533 l_eam_wo_rec.attribute3 := FND_API.G_MISS_CHAR;
534 END IF;
535
536 IF l_prev_attribute4 is not null and l_eam_wo_rec.attribute4 is null THEN
537 l_eam_wo_rec.attribute4 := FND_API.G_MISS_CHAR;
538 END IF;
539
540 IF l_prev_attribute5 is not null and l_eam_wo_rec.attribute5 is null THEN
541 l_eam_wo_rec.attribute5 := FND_API.G_MISS_CHAR;
542 END IF;
543
544 IF l_prev_attribute6 is not null and l_eam_wo_rec.attribute6 is null THEN
545 l_eam_wo_rec.attribute6 := FND_API.G_MISS_CHAR;
546 END IF;
547
548 IF l_prev_attribute7 is not null and l_eam_wo_rec.attribute7 is null THEN
549 l_eam_wo_rec.attribute7 := FND_API.G_MISS_CHAR;
550 END IF;
551
552 IF l_prev_attribute8 is not null and l_eam_wo_rec.attribute8 is null THEN
553 l_eam_wo_rec.attribute8 := FND_API.G_MISS_CHAR;
554 END IF;
555
556 IF l_prev_attribute9 is not null and l_eam_wo_rec.attribute9 is null THEN
557 l_eam_wo_rec.attribute9 := FND_API.G_MISS_CHAR;
558 END IF;
559
560 IF l_prev_attribute10 is not null and l_eam_wo_rec.attribute10 is null THEN
561 l_eam_wo_rec.attribute10 := FND_API.G_MISS_CHAR;
562 END IF;
563
564 IF l_prev_attribute11 is not null and l_eam_wo_rec.attribute11 is null THEN
565 l_eam_wo_rec.attribute11 := FND_API.G_MISS_CHAR;
566 END IF;
567
568 IF l_prev_attribute12 is not null and l_eam_wo_rec.attribute12 is null THEN
569 l_eam_wo_rec.attribute12 := FND_API.G_MISS_CHAR;
570 END IF;
571
572 IF l_prev_attribute13 is not null and l_eam_wo_rec.attribute13 is null THEN
573 l_eam_wo_rec.attribute13 := FND_API.G_MISS_CHAR;
574 END IF;
575
576 IF l_prev_attribute14 is not null and l_eam_wo_rec.attribute14 is null THEN
577 l_eam_wo_rec.attribute14 := FND_API.G_MISS_CHAR;
578 END IF;
579
580 IF l_prev_attribute15 is not null and l_eam_wo_rec.attribute15 is null THEN
581 l_eam_wo_rec.attribute15 := FND_API.G_MISS_CHAR;
582 END IF;
583
584 /* Added for bug#6053425 Start */
585
586 IF l_prev_project_id is not null AND l_eam_wo_rec.project_id is null THEN
587 l_eam_wo_rec.project_id := FND_API.G_MISS_NUM;
588 END IF;
589
590 IF l_prev_task_id is not null AND l_eam_wo_rec.task_id is null THEN
591 l_eam_wo_rec.task_id := FND_API.G_MISS_NUM;
592 END IF;
593
594 /* Added for bug#6053425 End */
595
596 EXCEPTION
597 WHEN NO_DATA_FOUND THEN
598 NULL;
599 END;
600
601
602 --Validate descriptive flexfield for workorder
603 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
604 p_app_short_name => 'WIP',
605 p_desc_flex_name => 'WIP_DISCRETE_JOBS',
606 p_attribute_category => l_eam_wo_rec.attribute_category,
607 p_attribute1 => l_eam_wo_rec.attribute1,
608 p_attribute2 => l_eam_wo_rec.attribute2,
609 p_attribute3 => l_eam_wo_rec.attribute3,
610 p_attribute4 => l_eam_wo_rec.attribute4,
611 p_attribute5 => l_eam_wo_rec.attribute5,
612 p_attribute6 => l_eam_wo_rec.attribute6,
613 p_attribute7 => l_eam_wo_rec.attribute7,
614 p_attribute8 => l_eam_wo_rec.attribute8,
615 p_attribute9 => l_eam_wo_rec.attribute9,
616 p_attribute10 => l_eam_wo_rec.attribute10,
617 p_attribute11 => l_eam_wo_rec.attribute11,
618 p_attribute12 => l_eam_wo_rec.attribute12,
619 p_attribute13 => l_eam_wo_rec.attribute13,
620 p_attribute14 => l_eam_wo_rec.attribute14,
621 p_attribute15 => l_eam_wo_rec.attribute15,
622 x_error_segments => l_error_segments,
623 x_error_message => l_error_message
624 );
625
626 IF l_validate <> TRUE THEN
627 fnd_message.set_name
628 ( application => 'EAM'
629 , name => 'EAM_WO_FLEX_ERROR'
630 );
631
632 fnd_message.set_token(token => 'MESG',
633 value => l_error_message,
634 translate => FALSE);
635
636
637 fnd_msg_pub.add;
638
639 x_wip_entity_id := l_eam_wo_rec.wip_entity_id;
640 x_return_status := 'E';
641 x_msg_count := 1;
642 RETURN;
643 END IF;
644
645
646 l_eam_wo_tbl(l_eam_wo_tbl.FIRST) := l_eam_wo_rec;
647 END IF;
648
649 --Set the auto_charge type to Manual/POReceipt for newly created workorders
650 l_eam_res_tbl := p_eam_res_tbl;
651 IF(l_eam_res_tbl IS NOT NULL AND l_eam_res_tbl.COUNT>0) THEN
652 FOR i IN l_eam_res_tbl.FIRST .. l_eam_res_tbl.LAST LOOP
653 IF(l_eam_res_tbl(i).transaction_type=EAM_PROCESS_WO_PUB.G_OPR_CREATE AND (l_eam_res_tbl(i).autocharge_type IS NULL)) THEN
654 OPEN resources(l_eam_res_tbl(i).resource_id,l_eam_res_tbl(i).organization_id);
655 FETCH resources INTO l_resources;
656 IF(resources%FOUND) THEN
657 IF(l_resources.autocharge_type=1 OR l_resources.autocharge_type=2) THEN
658 l_eam_res_tbl(i).autocharge_type := 2;
659 ELSE
660 l_eam_res_tbl(i).autocharge_type := 3;
661 END IF;
662 END IF;
663 CLOSE resources;
664 END IF;
665 END LOOP;
666 END IF;
667
668 --p_failure_code_required will be passed only when failure code information is entered or modified.
669 --Make sure that work order record is passed whenever failure entry is passed, as we have some logic on work order related info
670 IF(p_failure_code_required IS NOT NULL) THEN
671
672 /* Failure Analysis Project Start */
673 l_eam_failure_entry_record.failure_id := p_failure_id;
674 l_eam_failure_entry_record.failure_date := p_failure_date;
675
676 l_eam_failure_codes_tbl(1).failure_id := p_failure_id;
677 l_eam_failure_codes_tbl(1).failure_entry_id := p_failure_entry_id;
678 l_eam_failure_codes_tbl(1).failure_code := p_failure_code;
679 l_eam_failure_codes_tbl(1).cause_code := p_cause_code;
680 l_eam_failure_codes_tbl(1).resolution_code := p_resolution_code;
681 l_eam_failure_codes_tbl(1).comments := p_failure_comments;
682 /* Failure Analysis Project End */
683
684 l_fail_dept_id := l_eam_wo_rec.owning_department;
685
686 --ideally work order info should be passed when failure info is passed. But check that workorder is passed
687 IF(l_eam_wo_tbl IS NOT NULL AND l_eam_wo_tbl.COUNT>0) THEN
688
689 --only if asset number/rebuild serial number exists for work order
690 IF(l_maintenance_object_type =3) THEN
691 BEGIN
692
693 --if workorder dept. is null,de fault it from asset's owning dept
694 IF(l_fail_dept_id IS NULL) THEN
695 SELECT OWNING_DEPARTMENT_ID
696 INTO l_fail_dept_id
697 FROM eam_org_maint_defaults
698 WHERE object_id =l_maintenance_object_id
699 AND object_type = 50
700 AND organization_id =l_eam_wo_comp_rec.organization_id;
701 END IF;
702
703 SELECT area_id
704 INTO l_eam_location_id
705 FROM eam_org_maint_defaults
706 WHERE object_id = l_maintenance_object_id
707 AND object_type = 50
708 AND organization_id = l_eam_wo_comp_rec.organization_id;
709
710 EXCEPTION
711 WHEN NO_DATA_FOUND THEN
712 NULL;
713 END;
714 END IF; --end of check for mainteannce_object_type =3
715
716 l_eam_wo_rec.failure_code_required := p_failure_code_required;
717
718 IF(l_wo_exists = TRUE) THEN
719
720 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
721 l_eam_failure_entry_record.source_type := 1;
722 l_eam_failure_entry_record.source_id := l_eam_wo_rec.wip_entity_id;
723 l_eam_failure_entry_record.object_type := l_eam_wo_rec.maintenance_object_type;
724 l_eam_failure_entry_record.object_id := l_eam_wo_rec.maintenance_object_id;
725 l_eam_failure_entry_record.maint_organization_id := l_eam_wo_rec.organization_id;
726 l_eam_failure_entry_record.current_organization_id := l_eam_wo_rec.organization_id;
727 l_eam_failure_entry_record.department_id := l_fail_dept_id;
728 l_eam_failure_entry_record.area_id := l_eam_location_id;
729
730
731 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
732 if (l_eam_failure_entry_record.failure_date is null) then
733 l_eam_failure_entry_record.failure_date := FND_API.G_MISS_DATE;
734 end if;
735 if (l_eam_failure_codes_tbl(1).failure_code is null) then
736 l_eam_failure_codes_tbl(1).failure_code := FND_API.G_MISS_CHAR;
737 end if;
738 if (l_eam_failure_codes_tbl(1).cause_code is null) then
739 l_eam_failure_codes_tbl(1).cause_code := FND_API.G_MISS_CHAR;
740 end if;
741 if (l_eam_failure_codes_tbl(1).resolution_code is null) then
742 l_eam_failure_codes_tbl(1).resolution_code := FND_API.G_MISS_CHAR;
743 end if;
744 if (l_eam_failure_codes_tbl(1).comments is null) then
745 l_eam_failure_codes_tbl(1).comments := FND_API.G_MISS_CHAR;
746 end if;
747 if(l_eam_failure_entry_record.failure_id is not null ) then
748 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
749 else
750 if(l_eam_failure_entry_record.failure_date = FND_API.G_MISS_DATE) then
751 l_eam_failure_entry_record.transaction_type :=null;
752 l_eam_failure_entry_record.failure_date :=null;
753 else
754 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE ;
755 end if;
756 end if;
757
758 if(l_eam_failure_codes_tbl(1).failure_entry_id is not null) then
759 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
760 l_eam_wo_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
761
762 else
763 if( not( (l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR)
764 and (l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR)
765 and (l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR)
766 and (l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR)
767 )
768 ) then
769 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
770 if(l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR) then
771 l_eam_failure_codes_tbl(1).failure_code := null;
772 end if;
773 if(l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR) then
774 l_eam_failure_codes_tbl(1).cause_code := null;
775 end if;
776 if(l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR) then
777 l_eam_failure_codes_tbl(1).resolution_code := null;
778 end if;
779 if(l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR) then
780 l_eam_failure_codes_tbl(1).comments := null;
781 end if;
782 l_eam_wo_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
783
784 else
785 l_eam_failure_codes_tbl.delete;
786 l_eam_wo_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
787
788 end if;
789 end if;
790 l_eam_wo_rec.eam_failure_entry_record := l_eam_failure_entry_record;
791
792 ELSE -- work order is getting created
793
794 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
795 l_eam_failure_entry_record.source_type := 1;
796 l_eam_failure_entry_record.source_id := l_eam_wo_rec.wip_entity_id;
797 l_eam_failure_entry_record.object_type := l_eam_wo_rec.maintenance_object_type;
798 l_eam_failure_entry_record.object_id := l_eam_wo_rec.maintenance_object_id;
799 l_eam_failure_entry_record.maint_organization_id := l_eam_wo_rec.organization_id;
800 l_eam_failure_entry_record.current_organization_id := l_eam_wo_rec.organization_id;
801 l_eam_failure_entry_record.department_id := l_fail_dept_id;
802 l_eam_failure_entry_record.area_id := l_eam_location_id;
803
804 if(l_eam_failure_entry_record.failure_date is null) then
805 l_eam_failure_entry_record.transaction_type :=null;
806 end if;
807 l_eam_wo_rec.eam_failure_entry_record := l_eam_failure_entry_record;
808
809 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
810 if( not( l_eam_failure_codes_tbl(1).failure_code is null
811 and l_eam_failure_codes_tbl(1).cause_code is null
812 and l_eam_failure_codes_tbl(1).resolution_code is null
813 and l_eam_failure_codes_tbl(1).comments is null
814 )
815 ) then
816 l_eam_wo_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
817 else
818 l_eam_failure_codes_tbl.delete;
819 l_eam_wo_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
820 end if;
821
822 END IF;
823
824
825 l_eam_wo_tbl(l_eam_wo_tbl.FIRST) := l_eam_wo_rec;
826
827 END IF; --end of check for work order record passed
828
829 END IF; --end of check for failure data passed
830
831
832 x_wip_entity_id := NULL;
833
834 IF(p_eam_wo_relations_tbl IS NOT NULL AND p_eam_wo_relations_tbl.COUNT > 0) THEN
835
836 EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO(
837 p_bo_identifier =>'EAM'
838 , p_api_version_number => 1.0
839 , p_init_msg_list => TRUE
840 , p_eam_wo_relations_tbl => p_eam_wo_relations_tbl
841 , p_eam_wo_tbl => l_eam_wo_tbl
842 , p_eam_op_tbl => p_eam_op_tbl
843 , p_eam_op_network_tbl => l_eam_op_network_tbl
844 , p_eam_res_tbl => l_eam_res_tbl
845 , p_eam_res_inst_tbl => p_eam_res_inst_tbl
846 ,p_eam_res_usage_tbl => p_eam_res_usage_tbl
847 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
848 , p_eam_mat_req_tbl => p_eam_mat_req_tbl
849 , p_eam_direct_items_tbl => p_eam_direct_items_tbl
850 , p_eam_wo_comp_tbl => p_eam_wo_comp_tbl
851 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
852 , p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
853 , p_eam_counter_prop_tbl => p_eam_counter_prop_tbl
854 , p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl
855 , p_eam_wo_comp_mr_read_tbl => p_eam_wo_comp_mr_read_tbl
856 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
857 , p_eam_request_tbl => p_eam_request_tbl
858 , x_eam_wo_tbl => l_eam_wo_tbl_out
859 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl_out
860 , x_eam_op_tbl => l_eam_op_tbl_out
861 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
862 , x_eam_res_tbl => l_eam_res_tbl_out
863 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
864 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
865 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
866 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
867 , x_eam_direct_items_tbl =>l_eam_direct_items_tbl_out
868 , x_eam_wo_comp_tbl => l_eam_wo_comp_tbl_out
869 , x_eam_wo_quality_tbl => l_eam_wo_quality_tbl_out
870 , x_eam_meter_reading_tbl => l_eam_meter_reading_tbl_out
871 , x_eam_counter_prop_tbl => l_eam_counter_prop_tbl_out
872 , x_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl_out
873 , x_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl_out
874 , x_eam_op_comp_tbl => l_eam_op_comp_tbl_out
875 , x_eam_request_tbl => l_eam_request_tbl_out
876 , x_return_status => x_return_status
877 , x_msg_count => x_msg_count
878 , p_commit => 'N'
879 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
880 , p_output_dir => l_output_dir
881 , p_debug_filename => 'createupdatewo.log'
882 , p_debug_file_mode => 'W'
883 );
884
885 ELSE
886 IF ( l_eam_wo_tbl.COUNT > 0 ) THEN
887 l_eam_wo_rec := l_eam_wo_tbl(l_eam_wo_tbl.FIRST);
888 END IF;
889
890 IF ( l_eam_wo_comp_tbl.COUNT > 0 ) THEN
891 l_eam_wo_comp_rec := l_eam_wo_comp_tbl(l_eam_wo_comp_tbl.FIRST);
892 END IF;
893
894 EAM_PROCESS_WO_PUB.PROCESS_WO(
895 p_bo_identifier =>'EAM'
896 , p_api_version_number => 1.0
897 , p_init_msg_list => TRUE
898 , p_eam_wo_rec => l_eam_wo_rec
899 , p_eam_op_tbl => p_eam_op_tbl
900 , p_eam_op_network_tbl => l_eam_op_network_tbl
901 , p_eam_res_tbl => l_eam_res_tbl
902 , p_eam_res_inst_tbl => p_eam_res_inst_tbl
903 , p_eam_res_usage_tbl => p_eam_res_usage_tbl
904 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
905 , p_eam_mat_req_tbl => p_eam_mat_req_tbl
906 , p_eam_direct_items_tbl => p_eam_direct_items_tbl
907 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
908 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
909 , p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
910 , p_eam_counter_prop_tbl => p_eam_counter_prop_tbl
911 , p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl
912 , p_eam_wo_comp_mr_read_tbl => p_eam_wo_comp_mr_read_tbl
913 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
914 , p_eam_request_tbl => p_eam_request_tbl
915 , x_eam_wo_rec => l_eam_wo_rec_out
916 , x_eam_op_tbl => l_eam_op_tbl_out
917 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
918 , x_eam_res_tbl => l_eam_res_tbl_out
919 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
920 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
921 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
922 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
923 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_out
924 , x_eam_wo_comp_rec => l_eam_wo_comp_rec_out
925 , x_eam_wo_quality_tbl => l_eam_wo_quality_tbl_out
926 , x_eam_meter_reading_tbl => l_eam_meter_reading_tbl_out
927 , x_eam_counter_prop_tbl => l_eam_counter_prop_tbl_out
928 , x_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl_out
929 , x_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl_out
930 , x_eam_op_comp_tbl => l_eam_op_comp_tbl_out
931 , x_eam_request_tbl => l_eam_request_tbl_out
932 , x_return_status => x_return_status
933 , x_msg_count => x_msg_count
934 , p_commit => 'N'
935 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
936 , p_output_dir => l_output_dir
937 , p_debug_filename => 'createupdatewo.log'
938 , p_debug_file_mode => 'W'
939 );
940
941 END IF;
942
943 END IF; /*MSP IF END*/
944
945 IF(x_return_status='S') THEN
946 IF p_commit = FND_API.G_TRUE THEN
947 COMMIT WORK;
948 end if;
949 IF(l_eam_wo_tbl_out IS NOT NULL AND l_eam_wo_tbl_out.COUNT>0) THEN
950 x_wip_entity_id := l_eam_wo_tbl_out(l_eam_wo_tbl_out.FIRST).wip_entity_id;
951 ELSIF(l_eam_wo_rec_out.wip_entity_id IS NOT NULL) THEN
952 x_wip_entity_id := l_eam_wo_rec_out.wip_entity_id;
953 END IF;
954 END IF;
955
956 IF(x_return_status <> 'S') THEN
957 ROLLBACK TO create_update_wo;
958 END IF;
959
960 END CREATE_UPDATE_WO;
961
962
963 /********************************************************
964 Procedure to find the required,assigned and unassigned hours at workorder level
965 *********************************************************/
966 PROCEDURE ASSIGNED_HOURS
967 (
968 p_wip_entity_id IN NUMBER,
969 x_required_hours OUT NOCOPY NUMBER,
970 x_assigned_hours OUT NOCOPY NUMBER,
971 x_unassigned_hours OUT NOCOPY NUMBER
972 )
973 IS
974
975 l_uom_conv NUMBER;
976 l_hour_uom VARCHAR2(10);
977 l_sysdate DATE;
978 l_next_date DATE;
979 l_inv_item_id NUMBER;
980
981 CURSOR get_hours_details_csr IS
982 SELECT ROUND(NVL(wor.usage_rate_or_amount * (1/l_uom_conv) *
983 (DECODE (con.conversion_rate,'', 0, '0', 0, con.conversion_rate)),0),2) required_hours,
984 (SELECT ROUND(NVL(SUM((woru.completion_date-woru.start_date)*24) ,0),2)
985 FROM WIP_OPERATION_RESOURCE_USAGE woru
986 WHERE woru.wip_entity_id = wor.wip_entity_id
987 AND woru.organization_id = wor.organization_id
988 AND woru.operation_seq_num = wor.operation_seq_num
989 AND woru.resource_seq_num = wor.resource_seq_num
990 AND woru.instance_id IS NOT NULL
991 AND woru.serial_number IS NULL) assigned_hours
992 FROM WIP_OPERATION_RESOURCES wor,MTL_UOM_CONVERSIONS con,BOM_RESOURCES br
993 WHERE wor.wip_entity_id = p_wip_entity_id
994 AND wor.resource_id = br.resource_id
995 AND br.resource_type = 2 --for person type resources only
996 AND con.uom_code = wor. uom_code
997 AND NVL(con.disable_date, l_next_date) > l_sysdate
998 AND con.inventory_item_id = l_inv_item_id;
999
1000
1001 BEGIN
1002
1003 l_hour_uom := fnd_profile.value('BOM:HOUR_UOM_CODE');
1004
1005 l_sysdate := SYSDATE;
1006 l_next_date := SYSDATE+1;
1007 l_inv_item_id := 0;
1008
1009
1010
1011 x_required_hours := 0;
1012 x_assigned_hours := 0;
1013 x_unassigned_hours := 0;
1014
1015
1016 SELECT CON.CONVERSION_RATE
1017 INTO l_uom_conv
1018 FROM MTL_UOM_CONVERSIONS CON
1019 WHERE CON.UOM_CODE = l_hour_uom
1020 AND NVL(DISABLE_DATE, l_next_date) > l_sysdate
1021 AND CON.INVENTORY_ITEM_ID = l_inv_item_id;
1022
1023 --Required Hours will be 'Usage-Rate-Or-Amount' and assigned hrs will be fetched from WORU
1024 --Unassigned hours will be added only if Assigned < Required, else it will be treated as 0
1025
1026 FOR p_hours_row IN get_hours_details_csr
1027 LOOP
1028 x_required_hours := x_required_hours + p_hours_row.required_hours;
1029 x_assigned_hours := x_assigned_hours + p_hours_row.assigned_hours;
1030
1031 IF(p_hours_row.required_hours - p_hours_row.assigned_hours > 0) THEN
1032 x_unassigned_hours := x_unassigned_hours + (p_hours_row.required_hours - p_hours_row.assigned_hours);
1033 END IF;
1034
1035 END LOOP;
1036
1037 EXCEPTION
1038 WHEN NO_DATA_FOUND THEN
1039 NULL;
1040 END ASSIGNED_HOURS;
1041
1042 END EAM_CREATEUPDATE_WO_PVT;