[Home] [Help]
PACKAGE BODY: APPS.EAM_CREATEUPDATE_WO_PVT
Source
1 PACKAGE BODY EAM_CREATEUPDATE_WO_PVT AS
2 /* $Header: EAMVCUWB.pls 120.25.12020000.3 2013/02/12 13:12:00 rsandepo 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_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
129 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
130 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
131 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
132 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
133 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
134 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
135 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
136 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
137 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
138 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
139 l_eam_direct_items_rec EAM_PROCESS_WO_PUB.eam_direct_items_rec_type;
140
141 l_eam_wo_relations_tbl_out EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
142 l_eam_wo_rec_out EAM_PROCESS_WO_PUB.eam_wo_rec_type;
143 l_eam_wo_tbl_out EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
144 l_eam_op_tbl_out EAM_PROCESS_WO_PUB.eam_op_tbl_type;
145 l_eam_op_network_tbl_out EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
146 l_eam_res_tbl_out EAM_PROCESS_WO_PUB.eam_res_tbl_type;
147 l_eam_res_usage_tbl_out EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
148 l_eam_res_inst_tbl_out EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
149 l_eam_sub_res_tbl_out EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
150 l_eam_mat_req_tbl_out EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
151 l_eam_direct_items_tbl_out EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
152 l_eam_wo_comp_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
153 l_eam_wo_quality_tbl_out EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
154 l_eam_meter_reading_tbl_out EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
155 l_eam_counter_prop_tbl_out EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
156 l_eam_wo_comp_rebuild_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
157 l_eam_wo_comp_mr_read_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
158 l_eam_op_comp_tbl_out EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
159 l_eam_request_tbl_out EAM_PROCESS_WO_PUB.eam_request_tbl_type;
160 l_eam_wo_comp_rec_out EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
161
162
163 l_output_dir VARCHAR2(512);
164
165 CURSOR resources
166 (l_resource_id NUMBER,l_organization_id NUMBER)
167 IS
168 SELECT autocharge_type
169 FROM BOM_RESOURCES
170 WHERE resource_id = l_resource_id
171 AND organization_id = l_organization_id;
172
173 l_resources resources%ROWTYPE;
174 l_wip_entity_id NUMBER;
175 l_asset_group_id NUMBER;
176 l_asset_number VARCHAR2(30);
177 l_rebuild_item_id NUMBER;
178 l_rebuild_serial_number VARCHAR2(30);
179
180 /* Added for bug#4555609 */
181 l_prev_activity_id NUMBER;
182 l_prev_description VARCHAR2(240);
183 l_prev_priority NUMBER;
184 l_prev_work_order_type VARCHAR2(30);
185 l_prev_shutdown_type VARCHAR2(30);
186 l_prev_activity_type VARCHAR2(30);
187 l_prev_activity_cause VARCHAR2(30);
188 l_prev_activity_source VARCHAR2(30);
189 l_prev_attribute_category VARCHAR2(30);
190 l_prev_attribute1 VARCHAR2(150);
191 l_prev_attribute2 VARCHAR2(150);
192 l_prev_attribute3 VARCHAR2(150);
193 l_prev_attribute4 VARCHAR2(150);
194 l_prev_attribute5 VARCHAR2(150);
195 l_prev_attribute6 VARCHAR2(150);
196 l_prev_attribute7 VARCHAR2(150);
197 l_prev_attribute8 VARCHAR2(150);
198 l_prev_attribute9 VARCHAR2(150);
199 l_prev_attribute10 VARCHAR2(150);
200 l_prev_attribute11 VARCHAR2(150);
201 l_prev_attribute12 VARCHAR2(150);
202 l_prev_attribute13 VARCHAR2(150);
203 l_prev_attribute14 VARCHAR2(150);
204 l_prev_attribute15 VARCHAR2(150);
205 l_maintenance_object_type NUMBER;
206 l_maintenance_object_id NUMBER;
207 /* Added for bug#6053425 Start */
208 l_prev_project_id NUMBER;
209 l_prev_task_id NUMBER;
210 /* Added for bug#6053425 End */
211
212 l_eam_failure_entry_record EAM_Process_Failure_Entry_PUB.eam_failure_entry_record_typ;
213 l_eam_failure_codes_tbl EAM_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ;
214 l_fail_dept_id NUMBER;
215 l_eam_location_id NUMBER;
216 l_eam_failure_code_required varchar2(30);
217 l_wo_exists BOOLEAN;
218 l_org_id NUMBER;
219 l_validate BOOLEAN;
220 l_error_segments number;
221 l_error_message varchar2(2000);
222
223 -- Added for 8969942
224
225 l_organization_id NUMBER;
226 l_Operation_Seq_Num NUMBER;
227 l_inventory_item_id NUMBER;
228 l_dir_item_seq_id NUMBER;
229
230 l_prev_op_description VARCHAR2(240);
231 l_prev_op_long_description VARCHAR2(4000);
232 l_prev_op_shutdown_type VARCHAR2(30);
233 l_prev_op_attribute_category VARCHAR2(30);
234 l_prev_op_attribute1 VARCHAR2(150);
235 l_prev_op_attribute2 VARCHAR2(150);
236 l_prev_op_attribute3 VARCHAR2(150);
237 l_prev_op_attribute4 VARCHAR2(150);
238 l_prev_op_attribute5 VARCHAR2(150);
239 l_prev_op_attribute6 VARCHAR2(150);
240 l_prev_op_attribute7 VARCHAR2(150);
241 l_prev_op_attribute8 VARCHAR2(150);
242 l_prev_op_attribute9 VARCHAR2(150);
243 l_prev_op_attribute10 VARCHAR2(150);
244 l_prev_op_attribute11 VARCHAR2(150);
245 l_prev_op_attribute12 VARCHAR2(150);
246 l_prev_op_attribute13 VARCHAR2(150);
247 l_prev_op_attribute14 VARCHAR2(150);
248 l_prev_op_attribute15 VARCHAR2(150);
249
250 l_prev_mat_attribute_category VARCHAR2(30);
251 l_prev_mat_attribute1 VARCHAR2(150);
252 l_prev_mat_attribute2 VARCHAR2(150);
253 l_prev_mat_attribute3 VARCHAR2(150);
254 l_prev_mat_attribute4 VARCHAR2(150);
255 l_prev_mat_attribute5 VARCHAR2(150);
256 l_prev_mat_attribute6 VARCHAR2(150);
257 l_prev_mat_attribute7 VARCHAR2(150);
258 l_prev_mat_attribute8 VARCHAR2(150);
259 l_prev_mat_attribute9 VARCHAR2(150);
260 l_prev_mat_attribute10 VARCHAR2(150);
261 l_prev_mat_attribute11 VARCHAR2(150);
262 l_prev_mat_attribute12 VARCHAR2(150);
263 l_prev_mat_attribute13 VARCHAR2(150);
264 l_prev_mat_attribute14 VARCHAR2(150);
265 l_prev_mat_attribute15 VARCHAR2(150);
266
267 l_prev_dir_attribute_category VARCHAR2(30);
268 l_prev_dir_attribute1 VARCHAR2(150);
269 l_prev_dir_attribute2 VARCHAR2(150);
270 l_prev_dir_attribute3 VARCHAR2(150);
271 l_prev_dir_attribute4 VARCHAR2(150);
272 l_prev_dir_attribute5 VARCHAR2(150);
273 l_prev_dir_attribute6 VARCHAR2(150);
274 l_prev_dir_attribute7 VARCHAR2(150);
275 l_prev_dir_attribute8 VARCHAR2(150);
276 l_prev_dir_attribute9 VARCHAR2(150);
277 l_prev_dir_attribute10 VARCHAR2(150);
278 l_prev_dir_attribute11 VARCHAR2(150);
279 l_prev_dir_attribute12 VARCHAR2(150);
280 l_prev_dir_attribute13 VARCHAR2(150);
281 l_prev_dir_attribute14 VARCHAR2(150);
282 l_prev_dir_attribute15 VARCHAR2(150);
283
284 BEGIN
285
286 /* get output directory path from database */
287 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
288
289 SAVEPOINT create_update_wo;
290 /*MSP Project addition*/
291 IF(p_eam_wo_tbl IS NOT NULL) THEN
292 IF(p_eam_wo_tbl.first is not NULL) THEN
293 l_import_eam_wo_rec := p_eam_wo_tbl(p_eam_wo_tbl.first);
294 END IF;
295 END IF;
296
297 IF(l_import_eam_wo_rec.ATTRIBUTE15='import') THEN
298 eam_import_workorders.import_workorders(p_commit,
299 p_eam_wo_tbl,
300 p_eam_wo_relations_tbl,
301 p_eam_op_tbl,
302 p_eam_res_tbl,
303 p_eam_res_inst_tbl,
304 p_eam_res_usage_tbl,
305 p_eam_mat_req_tbl,
306 p_eam_direct_items_tbl,
307 p_eam_request_tbl,
308 p_eam_wo_comp_tbl,
309 p_eam_meter_reading_tbl,
310 p_eam_counter_prop_tbl,
311 p_eam_wo_comp_rebuild_tbl,
312 p_eam_wo_comp_mr_read_tbl,
313 x_wip_entity_id,
314 x_return_status,
315 x_msg_count);
316 ELSE /*MSP code end*/
317
318 l_eam_wo_comp_tbl := p_eam_wo_comp_tbl;
319
320 IF(l_eam_wo_comp_tbl IS NOT NULL AND l_eam_wo_comp_tbl.COUNT>0) THEN
321
322 l_eam_wo_comp_rec := l_eam_wo_comp_tbl(l_eam_wo_comp_tbl.FIRST);
323
324 /* Failure Analysis Project Start */
325
326 l_eam_failure_entry_record.failure_id := p_failure_id;
327 l_eam_failure_entry_record.failure_date := p_failure_date;
328
329 l_eam_failure_codes_tbl(1).failure_id := p_failure_id;
330 l_eam_failure_codes_tbl(1).failure_entry_id := p_failure_entry_id;
331 l_eam_failure_codes_tbl(1).failure_code := p_failure_code;
332 l_eam_failure_codes_tbl(1).cause_code := p_cause_code;
333 l_eam_failure_codes_tbl(1).resolution_code := p_resolution_code;
334 l_eam_failure_codes_tbl(1).comments := p_failure_comments;
335
336
337 SELECT
338 maintenance_object_type
339 ,maintenance_object_id
340 INTO
341 l_maintenance_object_type
342 ,l_maintenance_object_id
343 FROM WIP_DISCRETE_JOBS
344 WHERE wip_entity_id = l_eam_wo_comp_rec.wip_entity_id;
345
346 --only if asset number/rebuild serial number exists for work order
347 IF(l_maintenance_object_type =3) THEN
348 BEGIN
349
350 --if workorder dept. is null,de fault it from asset's owning dept
351
352 SELECT OWNING_DEPARTMENT_ID
353 INTO l_fail_dept_id
354 FROM eam_org_maint_defaults
355 WHERE object_id =l_maintenance_object_id
356 AND object_type = 50
357 AND organization_id =l_eam_wo_comp_rec.organization_id;
358
359 SELECT area_id
360 INTO l_eam_location_id
361 FROM eam_org_maint_defaults
362 WHERE object_id = l_maintenance_object_id
363 AND object_type = 50
364 AND organization_id = l_eam_wo_comp_rec.organization_id;
365
366 EXCEPTION
367 WHEN NO_DATA_FOUND THEN
368 NULL;
369 END;
370 END IF; --end of check for mainteannce_object_type =3
371
372
373 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
374 l_eam_failure_entry_record.source_type := 1;
375 l_eam_failure_entry_record.source_id := l_eam_wo_comp_rec.wip_entity_id;
376 l_eam_failure_entry_record.object_type := l_maintenance_object_type;
377 l_eam_failure_entry_record.object_id := l_maintenance_object_id;
378 l_eam_failure_entry_record.maint_organization_id := l_eam_wo_comp_rec.organization_id;
379 l_eam_failure_entry_record.current_organization_id := l_eam_wo_comp_rec.organization_id;
380 l_eam_failure_entry_record.department_id := l_fail_dept_id;
381 l_eam_failure_entry_record.area_id := l_eam_location_id;
382
383 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
384
385 IF (l_eam_failure_entry_record.failure_date IS NULL) THEN
386 l_eam_failure_entry_record.failure_date := FND_API.G_MISS_DATE;
387 END IF;
388
389 IF (l_eam_failure_codes_tbl(1).failure_code IS NULL) THEN
390 l_eam_failure_codes_tbl(1).failure_code := FND_API.G_MISS_CHAR;
391 END IF;
392
393 IF (l_eam_failure_codes_tbl(1).cause_code IS NULL) THEN
394 l_eam_failure_codes_tbl(1).cause_code := FND_API.G_MISS_CHAR;
395 END IF;
396
397 IF (l_eam_failure_codes_tbl(1).resolution_code IS NULL) THEN
398 l_eam_failure_codes_tbl(1).resolution_code := FND_API.G_MISS_CHAR;
399 END IF;
400
401 IF (l_eam_failure_codes_tbl(1).comments IS NULL) THEN
402 l_eam_failure_codes_tbl(1).comments := FND_API.G_MISS_CHAR;
403 END IF;
404
405 IF(l_eam_failure_entry_record.failure_id IS NOT NULL ) THEN
406 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
407 ELSE
408 IF(l_eam_failure_entry_record.failure_date = FND_API.G_MISS_DATE) THEN
409 l_eam_failure_entry_record.transaction_type :=null;
410 l_eam_failure_entry_record.failure_date :=null;
411 ELSE
412 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE ;
413 END IF;
414 END IF;
415
416 IF(l_eam_failure_codes_tbl(1).failure_entry_id IS NOT NULL) THEN
417 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
418 l_eam_wo_comp_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
419
420 ELSE
421 IF( NOT( (l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR)
422 AND (l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR)
423 AND (l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR)
424 AND (l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR)
425 )
426 ) THEN
427 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
428
429 IF(l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR) THEN
430 l_eam_failure_codes_tbl(1).failure_code := null;
431 END IF;
432
433 IF(l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR) THEN
434 l_eam_failure_codes_tbl(1).cause_code := NULL;
435 END IF;
436
437 IF(l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR) then
438 l_eam_failure_codes_tbl(1).resolution_code := null;
439 END IF;
440
441 IF(l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR) then
442 l_eam_failure_codes_tbl(1).comments := null;
443 END IF;
444 l_eam_wo_comp_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
445
446 ELSE
447 l_eam_failure_codes_tbl.delete;
448 l_eam_wo_comp_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
449
450 END IF;
451 END IF;
452
453 l_eam_wo_comp_rec.eam_failure_entry_record := l_eam_failure_entry_record;
454 l_eam_wo_comp_tbl(l_eam_wo_comp_tbl.FIRST) := l_eam_wo_comp_rec;
455
456 END IF;
457
458 /* Failure Analysis Project End */
459
460 --Set the activity_id to Fnd_Api.G_Miss_Num if we want to delete the activity
461 l_eam_wo_tbl := p_eam_wo_tbl;
462 IF(l_eam_wo_tbl IS NOT NULL AND l_eam_wo_tbl.COUNT>0) THEN
463
464 l_eam_wo_rec := l_eam_wo_tbl(l_eam_wo_tbl.FIRST);
465
466 --set FND_API.G_MISS_xx if any columns are nulled out
467 l_wo_exists := TRUE; --work order already exists...if no data found exception is thrown then l_wo_exists will be set to FALSE
468
469 BEGIN
470 l_wip_entity_id :=l_eam_wo_rec.wip_entity_id;
471
472 SELECT asset_number
473 ,asset_group_id
474 ,rebuild_serial_number
475 ,rebuild_item_id
476 ,primary_item_id
477 ,description
478 ,priority
479 ,work_order_type
480 ,shutdown_type
481 ,activity_type
482 ,activity_cause
483 ,activity_source
484 ,attribute_category
485 ,attribute1
486 ,attribute2
487 ,attribute3
488 ,attribute4
489 ,attribute5
490 ,attribute6
491 ,attribute7
492 ,attribute8
493 ,attribute9
494 ,attribute10
495 ,attribute11
496 ,attribute12
497 ,attribute13
498 ,attribute14
499 ,attribute15
500 /* Added for bug#6053425 Start */
501 ,project_id
502 ,task_id
503 /* Added for bug#6053425 End */
504 INTO l_asset_number
505 ,l_asset_group_id
506 ,l_rebuild_serial_number
507 ,l_rebuild_item_id
508 ,l_prev_activity_id
509 ,l_prev_description
510 ,l_prev_priority
511 ,l_prev_work_order_type
512 ,l_prev_shutdown_type
513 ,l_prev_activity_type
514 ,l_prev_activity_cause
515 ,l_prev_activity_source
516 ,l_prev_attribute_category
517 ,l_prev_attribute1
518 ,l_prev_attribute2
519 ,l_prev_attribute3
520 ,l_prev_attribute4
521 ,l_prev_attribute5
522 ,l_prev_attribute6
523 ,l_prev_attribute7
524 ,l_prev_attribute8
525 ,l_prev_attribute9
526 ,l_prev_attribute10
527 ,l_prev_attribute11
528 ,l_prev_attribute12
529 ,l_prev_attribute13
530 ,l_prev_attribute14
531 ,l_prev_attribute15
532 /* Code Added for bug#6053425 Start */
533 ,l_prev_project_id
534 ,l_prev_task_id
535 /* Code Added for bug#6053425 End */
536 FROM WIP_DISCRETE_JOBS
537 where wip_entity_id = l_wip_entity_id;
538
539 IF(l_asset_number is NOT NULL and l_eam_wo_rec.asset_number IS NULL) THEN
540 l_eam_wo_rec.asset_number := FND_API.G_MISS_CHAR;
541 END IF;
542
543 IF(l_rebuild_serial_number is NOT NULL and l_eam_wo_rec.rebuild_serial_number IS NULL) THEN
544 l_eam_wo_rec.rebuild_serial_number := FND_API.G_MISS_CHAR;
545 END IF;
546
547 IF(l_asset_group_id is NOT NULL and l_eam_wo_rec.asset_group_id IS NULL) THEN
548 l_eam_wo_rec.asset_group_id := FND_API.G_MISS_NUM;
549 END IF;
550
551 IF(l_rebuild_item_id is NOT NULL and l_eam_wo_rec.rebuild_item_id IS NULL) THEN
552 l_eam_wo_rec.rebuild_item_id:= FND_API.G_MISS_NUM;
553 END IF;
554
555 IF l_prev_activity_id is not null and l_eam_wo_rec.asset_activity_id is null THEN
556 l_eam_wo_rec.asset_activity_id := FND_API.G_MISS_NUM;
557 END IF;
558
559 IF l_prev_description is not null and l_eam_wo_rec.description is null THEN
560 l_eam_wo_rec.description := FND_API.G_MISS_CHAR;
561 END IF;
562
563 IF l_prev_activity_type is not null and l_eam_wo_rec.activity_type is null THEN
564 l_eam_wo_rec.activity_type := FND_API.G_MISS_CHAR;
565 END IF;
566
567 IF l_prev_activity_cause is not null and l_eam_wo_rec.activity_cause is null THEN
568 l_eam_wo_rec.activity_cause := FND_API.G_MISS_CHAR;
569 END IF;
570
571 IF l_prev_activity_source is not null and l_eam_wo_rec.activity_source is null THEN
572 l_eam_wo_rec.activity_source := FND_API.G_MISS_CHAR;
573 END IF;
574
575 IF l_prev_shutdown_type is not null and l_eam_wo_rec.shutdown_type is null THEN
576 l_eam_wo_rec.shutdown_type := FND_API.G_MISS_CHAR;
577 END IF;
578
579 IF l_prev_priority is not null and l_eam_wo_rec.priority is null THEN
580 l_eam_wo_rec.priority := FND_API.G_MISS_NUM;
581 END IF;
582
583 IF l_prev_work_order_type is not null and l_eam_wo_rec.work_order_type is null THEN
584 l_eam_wo_rec.work_order_type := FND_API.G_MISS_CHAR;
585 END IF;
586
587 IF l_prev_attribute_category is not null and l_eam_wo_rec.attribute_category is null THEN
588 l_eam_wo_rec.attribute_category := FND_API.G_MISS_CHAR;
589 END IF;
590
591 IF l_prev_attribute1 is not null and l_eam_wo_rec.attribute1 is null THEN
592 l_eam_wo_rec.attribute1 := FND_API.G_MISS_CHAR;
593 END IF;
594
595 IF l_prev_attribute2 is not null and l_eam_wo_rec.attribute2 is null THEN
596 l_eam_wo_rec.attribute2 := FND_API.G_MISS_CHAR;
597 END IF;
598
599 IF l_prev_attribute3 is not null and l_eam_wo_rec.attribute3 is null THEN
600 l_eam_wo_rec.attribute3 := FND_API.G_MISS_CHAR;
601 END IF;
602
603 IF l_prev_attribute4 is not null and l_eam_wo_rec.attribute4 is null THEN
604 l_eam_wo_rec.attribute4 := FND_API.G_MISS_CHAR;
605 END IF;
606
607 IF l_prev_attribute5 is not null and l_eam_wo_rec.attribute5 is null THEN
608 l_eam_wo_rec.attribute5 := FND_API.G_MISS_CHAR;
609 END IF;
610
611 IF l_prev_attribute6 is not null and l_eam_wo_rec.attribute6 is null THEN
612 l_eam_wo_rec.attribute6 := FND_API.G_MISS_CHAR;
613 END IF;
614
615 IF l_prev_attribute7 is not null and l_eam_wo_rec.attribute7 is null THEN
616 l_eam_wo_rec.attribute7 := FND_API.G_MISS_CHAR;
617 END IF;
618
619 IF l_prev_attribute8 is not null and l_eam_wo_rec.attribute8 is null THEN
620 l_eam_wo_rec.attribute8 := FND_API.G_MISS_CHAR;
621 END IF;
622
623 IF l_prev_attribute9 is not null and l_eam_wo_rec.attribute9 is null THEN
624 l_eam_wo_rec.attribute9 := FND_API.G_MISS_CHAR;
625 END IF;
626
627 IF l_prev_attribute10 is not null and l_eam_wo_rec.attribute10 is null THEN
628 l_eam_wo_rec.attribute10 := FND_API.G_MISS_CHAR;
629 END IF;
630
631 IF l_prev_attribute11 is not null and l_eam_wo_rec.attribute11 is null THEN
632 l_eam_wo_rec.attribute11 := FND_API.G_MISS_CHAR;
633 END IF;
634
635 IF l_prev_attribute12 is not null and l_eam_wo_rec.attribute12 is null THEN
636 l_eam_wo_rec.attribute12 := FND_API.G_MISS_CHAR;
637 END IF;
638
639 IF l_prev_attribute13 is not null and l_eam_wo_rec.attribute13 is null THEN
640 l_eam_wo_rec.attribute13 := FND_API.G_MISS_CHAR;
641 END IF;
642
643 IF l_prev_attribute14 is not null and l_eam_wo_rec.attribute14 is null THEN
644 l_eam_wo_rec.attribute14 := FND_API.G_MISS_CHAR;
645 END IF;
646
647 IF l_prev_attribute15 is not null and l_eam_wo_rec.attribute15 is null THEN
648 l_eam_wo_rec.attribute15 := FND_API.G_MISS_CHAR;
649 END IF;
650
651 /* Added for bug#6053425 Start */
652
653 IF l_prev_project_id is not null AND l_eam_wo_rec.project_id is null THEN
654 l_eam_wo_rec.project_id := FND_API.G_MISS_NUM;
655 END IF;
656
657 IF l_prev_task_id is not null AND l_eam_wo_rec.task_id is null THEN
658 l_eam_wo_rec.task_id := FND_API.G_MISS_NUM;
659 END IF;
660
661 /* Added for bug#6053425 End */
662
663 EXCEPTION
664 WHEN NO_DATA_FOUND THEN
665 NULL;
666 END;
667
668
669 --Validate descriptive flexfield for workorder
670 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
671 p_app_short_name => 'WIP',
672 p_desc_flex_name => 'WIP_DISCRETE_JOBS',
673 p_attribute_category => l_eam_wo_rec.attribute_category,
674 p_attribute1 => l_eam_wo_rec.attribute1,
675 p_attribute2 => l_eam_wo_rec.attribute2,
676 p_attribute3 => l_eam_wo_rec.attribute3,
677 p_attribute4 => l_eam_wo_rec.attribute4,
678 p_attribute5 => l_eam_wo_rec.attribute5,
679 p_attribute6 => l_eam_wo_rec.attribute6,
680 p_attribute7 => l_eam_wo_rec.attribute7,
681 p_attribute8 => l_eam_wo_rec.attribute8,
682 p_attribute9 => l_eam_wo_rec.attribute9,
683 p_attribute10 => l_eam_wo_rec.attribute10,
684 p_attribute11 => l_eam_wo_rec.attribute11,
685 p_attribute12 => l_eam_wo_rec.attribute12,
686 p_attribute13 => l_eam_wo_rec.attribute13,
687 p_attribute14 => l_eam_wo_rec.attribute14,
688 p_attribute15 => l_eam_wo_rec.attribute15,
689 x_error_segments => l_error_segments,
690 x_error_message => l_error_message
691 );
692
693 /* Commented for bug 8567361
694 IF l_validate <> TRUE THEN
695 fnd_message.set_name
696 ( application => 'EAM'
697 , name => 'EAM_WO_FLEX_ERROR'
698 );
699
700 fnd_message.set_token(token => 'MESG',
701 value => l_error_message,
702 translate => FALSE);
703
704
705 fnd_msg_pub.add;
706
707 x_wip_entity_id := l_eam_wo_rec.wip_entity_id;
708 x_return_status := 'E';
709 x_msg_count := 1;
710 RETURN;
711 END IF; */
712
713
714 l_eam_wo_tbl(l_eam_wo_tbl.FIRST) := l_eam_wo_rec;
715 END IF;
716
717 l_eam_op_tbl := p_eam_op_tbl;
718 IF(l_eam_op_tbl IS NOT NULL AND l_eam_op_tbl.COUNT>0) THEN
719
720 FOR i in 1..l_eam_op_tbl.COUNT
721 LOOP
722 l_eam_op_rec := l_eam_op_tbl(i);
723
724
725 BEGIN
726 l_wip_entity_id :=l_eam_op_rec.wip_entity_id;
727
728 SELECT
729 description
730 , shutdown_type
731 , long_description
732 , attribute_category
733 , attribute1
734 , attribute2
735 , attribute3
736 , attribute4
737 , attribute5
738 , attribute6
739 , attribute7
740 , attribute8
741 , attribute9
742 , attribute10
743 , attribute11
744 , attribute12
745 , attribute13
746 , attribute14
747 , attribute15
748 INTO
749 l_prev_op_description
750 ,l_prev_op_shutdown_type
751 ,l_prev_op_long_description
752 ,l_prev_op_attribute_category
753 ,l_prev_op_attribute1
754 ,l_prev_op_attribute2
755 ,l_prev_op_attribute3
756 ,l_prev_op_attribute4
757 ,l_prev_op_attribute5
758 ,l_prev_op_attribute6
759 ,l_prev_op_attribute7
760 ,l_prev_op_attribute8
761 ,l_prev_op_attribute9
762 ,l_prev_op_attribute10
763 ,l_prev_op_attribute11
764 ,l_prev_op_attribute12
765 ,l_prev_op_attribute13
766 ,l_prev_op_attribute14
767 ,l_prev_op_attribute15
768 FROM wip_operations wo
769 WHERE wo.wip_entity_id = l_eam_op_rec.wip_entity_id
770 AND wo.organization_id = l_eam_op_rec.organization_id
771 AND wo.operation_seq_num = l_eam_op_rec.operation_seq_num;
772
773
774 IF l_prev_op_description is not null and l_eam_op_rec.description is null THEN
775 l_eam_op_rec.description := FND_API.G_MISS_CHAR;
776 END IF;
777
778 IF l_prev_op_shutdown_type is not null and l_eam_op_rec.shutdown_type is null THEN
779 l_eam_op_rec.shutdown_type := FND_API.G_MISS_CHAR;
780 END IF;
781
782 IF l_prev_op_long_description is not null and l_eam_op_rec.long_description is null THEN
783 l_eam_op_rec.long_description := FND_API.G_MISS_CHAR;
784 END IF;
785
786 IF l_prev_op_attribute_category is not null and l_eam_op_rec.attribute_category is null THEN
787 l_eam_op_rec.attribute_category := FND_API.G_MISS_CHAR;
788 END IF;
789
790 IF l_prev_op_attribute1 is not null and l_eam_op_rec.attribute1 is null THEN
791 l_eam_op_rec.attribute1 := FND_API.G_MISS_CHAR;
792 END IF;
793
794 IF l_prev_op_attribute2 is not null and l_eam_op_rec.attribute2 is null THEN
795 l_eam_op_rec.attribute2 := FND_API.G_MISS_CHAR;
796 END IF;
797
798 IF l_prev_op_attribute3 is not null and l_eam_op_rec.attribute3 is null THEN
799 l_eam_op_rec.attribute3 := FND_API.G_MISS_CHAR;
800 END IF;
801
802 IF l_prev_op_attribute4 is not null and l_eam_op_rec.attribute4 is null THEN
803 l_eam_op_rec.attribute4 := FND_API.G_MISS_CHAR;
804 END IF;
805
806 IF l_prev_op_attribute5 is not null and l_eam_op_rec.attribute5 is null THEN
807 l_eam_op_rec.attribute5 := FND_API.G_MISS_CHAR;
808 END IF;
809
810 IF l_prev_op_attribute6 is not null and l_eam_op_rec.attribute6 is null THEN
811 l_eam_op_rec.attribute6 := FND_API.G_MISS_CHAR;
812 END IF;
813
814 IF l_prev_op_attribute7 is not null and l_eam_op_rec.attribute7 is null THEN
815 l_eam_op_rec.attribute7 := FND_API.G_MISS_CHAR;
816 END IF;
817
818 IF l_prev_op_attribute8 is not null and l_eam_op_rec.attribute8 is null THEN
819 l_eam_op_rec.attribute8 := FND_API.G_MISS_CHAR;
820 END IF;
821
822 IF l_prev_op_attribute9 is not null and l_eam_op_rec.attribute9 is null THEN
823 l_eam_op_rec.attribute9 := FND_API.G_MISS_CHAR;
824 END IF;
825
826 IF l_prev_op_attribute10 is not null and l_eam_op_rec.attribute10 is null THEN
827 l_eam_op_rec.attribute10 := FND_API.G_MISS_CHAR;
828 END IF;
829
830 IF l_prev_op_attribute11 is not null and l_eam_op_rec.attribute11 is null THEN
831 l_eam_op_rec.attribute11 := FND_API.G_MISS_CHAR;
832 END IF;
833
834 IF l_prev_op_attribute12 is not null and l_eam_op_rec.attribute12 is null THEN
835 l_eam_op_rec.attribute12 := FND_API.G_MISS_CHAR;
836 END IF;
837
838 IF l_prev_op_attribute13 is not null and l_eam_op_rec.attribute13 is null THEN
839 l_eam_op_rec.attribute13 := FND_API.G_MISS_CHAR;
840 END IF;
841
842 IF l_prev_op_attribute14 is not null and l_eam_op_rec.attribute14 is null THEN
843 l_eam_op_rec.attribute14 := FND_API.G_MISS_CHAR;
844 END IF;
845
846 IF l_prev_op_attribute15 is not null and l_eam_op_rec.attribute15 is null THEN
847 l_eam_op_rec.attribute15 := FND_API.G_MISS_CHAR;
848 END IF;
849
850
851 EXCEPTION
852 WHEN NO_DATA_FOUND THEN
853 NULL;
854 END;
855
856
857 --Validate descriptive flexfield for Operation
858 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
859 p_app_short_name => 'BOM',
860 p_desc_flex_name => 'OPERATIONS',
861 p_attribute_category => l_eam_op_rec.attribute_category,
862 p_attribute1 => l_eam_op_rec.attribute1,
863 p_attribute2 => l_eam_op_rec.attribute2,
864 p_attribute3 => l_eam_op_rec.attribute3,
865 p_attribute4 => l_eam_op_rec.attribute4,
866 p_attribute5 => l_eam_op_rec.attribute5,
867 p_attribute6 => l_eam_op_rec.attribute6,
868 p_attribute7 => l_eam_op_rec.attribute7,
869 p_attribute8 => l_eam_op_rec.attribute8,
870 p_attribute9 => l_eam_op_rec.attribute9,
871 p_attribute10 => l_eam_op_rec.attribute10,
872 p_attribute11 => l_eam_op_rec.attribute11,
873 p_attribute12 => l_eam_op_rec.attribute12,
874 p_attribute13 => l_eam_op_rec.attribute13,
875 p_attribute14 => l_eam_op_rec.attribute14,
876 p_attribute15 => l_eam_op_rec.attribute15,
877 x_error_segments => l_error_segments,
878 x_error_message => l_error_message
879 );
880
881 /* Commented for bug 8567361
882 IF l_validate <> TRUE THEN
883 fnd_message.set_name
884 ( application => 'EAM'
885 , name => 'EAM_OP_FLEX_ERROR'
886 );
887
888 fnd_message.set_token(token => 'MESG',
889 value => l_error_message,
890 translate => FALSE);
891
892
893 fnd_msg_pub.add;
894
895 x_wip_entity_id := l_eam_op_rec.wip_entity_id;
896 x_return_status := 'E';
897 x_msg_count := 1;
898 RETURN;
899 END IF; */
900
901
902 l_eam_op_tbl(i) := l_eam_op_rec;
903 END LOOP;
904 END IF;
905
906 --Changes for Bug 8969942
907
908 l_eam_mat_req_tbl := p_eam_mat_req_tbl;
909 IF(l_eam_mat_req_tbl IS NOT NULL AND l_eam_mat_req_tbl.COUNT>0) THEN
910 l_eam_mat_req_rec := l_eam_mat_req_tbl(l_eam_mat_req_tbl.FIRST);
911
912 BEGIN
913 l_wip_entity_id := l_eam_mat_req_rec.wip_entity_id;
914 l_organization_id := l_eam_mat_req_rec.ORGANIZATION_ID;
915 l_Operation_Seq_Num := l_eam_mat_req_rec.OPERATION_SEQ_NUM;
916 l_inventory_item_id := l_eam_mat_req_rec.INVENTORY_ITEM_ID;
917
918 SELECT attribute_category
919 ,attribute1
920 ,attribute2
921 ,attribute3
922 ,attribute4
923 ,attribute5
924 ,attribute6
925 ,attribute7
926 ,attribute8
927 ,attribute9
928 ,attribute10
929 ,attribute11
930 ,attribute12
931 ,attribute13
932 ,attribute14
933 ,attribute15
934 INTO l_prev_mat_attribute_category
935 ,l_prev_mat_attribute1
936 ,l_prev_mat_attribute2
937 ,l_prev_mat_attribute3
938 ,l_prev_mat_attribute4
939 ,l_prev_mat_attribute5
940 ,l_prev_mat_attribute6
941 ,l_prev_mat_attribute7
942 ,l_prev_mat_attribute8
943 ,l_prev_mat_attribute9
944 ,l_prev_mat_attribute10
945 ,l_prev_mat_attribute11
946 ,l_prev_mat_attribute12
947 ,l_prev_mat_attribute13
948 ,l_prev_mat_attribute14
949 ,l_prev_mat_attribute15
950 from WIP_REQUIREMENT_OPERATIONS
951 where wip_entity_id =l_wip_Entity_Id
952 and organization_id = l_organization_id
953 and operation_seq_num= l_Operation_Seq_Num
954 and INVENTORY_ITEM_ID = l_inventory_item_id;
955 EXCEPTION
956 WHEN NO_DATA_FOUND THEN
957 null;
958 When Others then
959 null;
960 END;
961
962 IF l_prev_mat_attribute_category is not null and l_eam_mat_req_rec.attribute_category is null THEN
963 l_eam_mat_req_rec.attribute_category := FND_API.G_MISS_CHAR;
964 END IF;
965
966 IF l_prev_mat_attribute1 is not null and l_eam_mat_req_rec.attribute1 is null THEN
967 l_eam_mat_req_rec.attribute1 := FND_API.G_MISS_CHAR;
968 END IF;
969
970 IF l_prev_mat_attribute2 is not null and l_eam_mat_req_rec.attribute2 is null THEN
971 l_eam_mat_req_rec.attribute2 := FND_API.G_MISS_CHAR;
972 END IF;
973
974 IF l_prev_mat_attribute3 is not null and l_eam_mat_req_rec.attribute3 is null THEN
975 l_eam_mat_req_rec.attribute3 := FND_API.G_MISS_CHAR;
976 END IF;
977
978 IF l_prev_mat_attribute4 is not null and l_eam_mat_req_rec.attribute4 is null THEN
979 l_eam_mat_req_rec.attribute4 := FND_API.G_MISS_CHAR;
980 END IF;
981
982 IF l_prev_mat_attribute5 is not null and l_eam_mat_req_rec.attribute5 is null THEN
983 l_eam_mat_req_rec.attribute5 := FND_API.G_MISS_CHAR;
984 END IF;
985
986 IF l_prev_mat_attribute6 is not null and l_eam_mat_req_rec.attribute6 is null THEN
987 l_eam_mat_req_rec.attribute6 := FND_API.G_MISS_CHAR;
988 END IF;
989
990 IF l_prev_mat_attribute7 is not null and l_eam_mat_req_rec.attribute7 is null THEN
991 l_eam_mat_req_rec.attribute7 := FND_API.G_MISS_CHAR;
992 END IF;
993
994 IF l_prev_mat_attribute8 is not null and l_eam_mat_req_rec.attribute8 is null THEN
995 l_eam_mat_req_rec.attribute8 := FND_API.G_MISS_CHAR;
996 END IF;
997
998 IF l_prev_mat_attribute9 is not null and l_eam_mat_req_rec.attribute9 is null THEN
999 l_eam_mat_req_rec.attribute9 := FND_API.G_MISS_CHAR;
1000 END IF;
1001
1002 IF l_prev_mat_attribute10 is not null and l_eam_mat_req_rec.attribute10 is null THEN
1003 l_eam_mat_req_rec.attribute10 := FND_API.G_MISS_CHAR;
1004 END IF;
1005
1006 IF l_prev_mat_attribute11 is not null and l_eam_mat_req_rec.attribute11 is null THEN
1007 l_eam_mat_req_rec.attribute11 := FND_API.G_MISS_CHAR;
1008 END IF;
1009
1010 IF l_prev_mat_attribute12 is not null and l_eam_mat_req_rec.attribute12 is null THEN
1011 l_eam_mat_req_rec.attribute12 := FND_API.G_MISS_CHAR;
1012 END IF;
1013
1014 IF l_prev_mat_attribute13 is not null and l_eam_mat_req_rec.attribute13 is null THEN
1015 l_eam_mat_req_rec.attribute13 := FND_API.G_MISS_CHAR;
1016 END IF;
1017
1018 IF l_prev_mat_attribute14 is not null and l_eam_mat_req_rec.attribute14 is null THEN
1019 l_eam_mat_req_rec.attribute14 := FND_API.G_MISS_CHAR;
1020 END IF;
1021
1022 IF l_prev_mat_attribute15 is not null and l_eam_mat_req_rec.attribute15 is null THEN
1023 l_eam_mat_req_rec.attribute15 := FND_API.G_MISS_CHAR;
1024 END IF;
1025
1026 --Validate descriptive flexfield for materials - "Component Information"
1027 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
1028 p_app_short_name => 'BOM',
1029 p_desc_flex_name => 'BOM_INVENTORY_COMPONENTS',
1030 p_attribute_category => l_eam_mat_req_rec.attribute_category,
1031 p_attribute1 => l_eam_mat_req_rec.attribute1,
1032 p_attribute2 => l_eam_mat_req_rec.attribute2,
1033 p_attribute3 => l_eam_mat_req_rec.attribute3,
1034 p_attribute4 => l_eam_mat_req_rec.attribute4,
1035 p_attribute5 => l_eam_mat_req_rec.attribute5,
1036 p_attribute6 => l_eam_mat_req_rec.attribute6,
1037 p_attribute7 => l_eam_mat_req_rec.attribute7,
1038 p_attribute8 => l_eam_mat_req_rec.attribute8,
1039 p_attribute9 => l_eam_mat_req_rec.attribute9,
1040 p_attribute10 => l_eam_mat_req_rec.attribute10,
1041 p_attribute11 => l_eam_mat_req_rec.attribute11,
1042 p_attribute12 => l_eam_mat_req_rec.attribute12,
1043 p_attribute13 => l_eam_mat_req_rec.attribute13,
1044 p_attribute14 => l_eam_mat_req_rec.attribute14,
1045 p_attribute15 => l_eam_mat_req_rec.attribute15,
1046 x_error_segments => l_error_segments,
1047 x_error_message => l_error_message
1048 );
1049
1050
1051
1052 l_eam_mat_req_tbl(l_eam_mat_req_tbl.FIRST) := l_eam_mat_req_rec;
1053 END IF;
1054
1055 l_eam_direct_items_tbl := p_eam_direct_items_tbl;
1056 IF(l_eam_direct_items_tbl IS NOT NULL AND l_eam_direct_items_tbl.COUNT>0) THEN
1057 l_eam_direct_items_rec := l_eam_direct_items_tbl(l_eam_direct_items_tbl.FIRST);
1058
1059 BEGIN
1060 l_wip_entity_id := l_eam_direct_items_rec.wip_entity_id;
1061 l_organization_id := l_eam_direct_items_rec.ORGANIZATION_ID;
1062 l_Operation_Seq_Num := l_eam_direct_items_rec.OPERATION_SEQ_NUM;
1063 l_dir_item_seq_id := l_eam_direct_items_rec.Direct_Item_Sequence_Id;
1064
1065 SELECT attribute_category
1066 ,attribute1
1067 ,attribute2
1068 ,attribute3
1069 ,attribute4
1070 ,attribute5
1071 ,attribute6
1072 ,attribute7
1073 ,attribute8
1074 ,attribute9
1075 ,attribute10
1076 ,attribute11
1077 ,attribute12
1078 ,attribute13
1079 ,attribute14
1080 ,attribute15
1081 INTO l_prev_dir_attribute_category
1082 ,l_prev_dir_attribute1
1083 ,l_prev_dir_attribute2
1084 ,l_prev_dir_attribute3
1085 ,l_prev_dir_attribute4
1086 ,l_prev_dir_attribute5
1087 ,l_prev_dir_attribute6
1088 ,l_prev_dir_attribute7
1089 ,l_prev_dir_attribute8
1090 ,l_prev_dir_attribute9
1091 ,l_prev_dir_attribute10
1092 ,l_prev_dir_attribute11
1093 ,l_prev_dir_attribute12
1094 ,l_prev_dir_attribute13
1095 ,l_prev_dir_attribute14
1096 ,l_prev_dir_attribute15
1097 from wip_eam_direct_items
1098 where wip_entity_id =l_wip_Entity_Id
1099 and organization_id = l_organization_id
1100 and operation_seq_num= l_Operation_Seq_Num ;
1101
1102 EXCEPTION
1103 WHEN NO_DATA_FOUND THEN
1104 null;
1105 When Others then
1106 null;
1107 END;
1108
1109 IF l_prev_dir_attribute_category is not null and l_eam_direct_items_rec.attribute_category is null THEN
1110 l_eam_direct_items_rec.attribute_category := FND_API.G_MISS_CHAR;
1111 END IF;
1112
1113 IF l_prev_dir_attribute1 is not null and l_eam_direct_items_rec.attribute1 is null THEN
1114 l_eam_direct_items_rec.attribute1 := FND_API.G_MISS_CHAR;
1115 END IF;
1116
1117 IF l_prev_dir_attribute2 is not null and l_eam_direct_items_rec.attribute2 is null THEN
1118 l_eam_direct_items_rec.attribute2 := FND_API.G_MISS_CHAR;
1119 END IF;
1120
1121 IF l_prev_dir_attribute3 is not null and l_eam_direct_items_rec.attribute3 is null THEN
1122 l_eam_direct_items_rec.attribute3 := FND_API.G_MISS_CHAR;
1123 END IF;
1124
1125 IF l_prev_dir_attribute4 is not null and l_eam_direct_items_rec.attribute4 is null THEN
1126 l_eam_direct_items_rec.attribute4 := FND_API.G_MISS_CHAR;
1127 END IF;
1128
1129 IF l_prev_dir_attribute5 is not null and l_eam_direct_items_rec.attribute5 is null THEN
1130 l_eam_direct_items_rec.attribute5 := FND_API.G_MISS_CHAR;
1131 END IF;
1132
1133 IF l_prev_dir_attribute6 is not null and l_eam_direct_items_rec.attribute6 is null THEN
1134 l_eam_direct_items_rec.attribute6 := FND_API.G_MISS_CHAR;
1135 END IF;
1136
1137 IF l_prev_dir_attribute7 is not null and l_eam_direct_items_rec.attribute7 is null THEN
1138 l_eam_direct_items_rec.attribute7 := FND_API.G_MISS_CHAR;
1139 END IF;
1140
1141 IF l_prev_dir_attribute8 is not null and l_eam_direct_items_rec.attribute8 is null THEN
1142 l_eam_direct_items_rec.attribute8 := FND_API.G_MISS_CHAR;
1143 END IF;
1144
1145 IF l_prev_dir_attribute9 is not null and l_eam_direct_items_rec.attribute9 is null THEN
1146 l_eam_direct_items_rec.attribute9 := FND_API.G_MISS_CHAR;
1147 END IF;
1148
1149 IF l_prev_dir_attribute10 is not null and l_eam_direct_items_rec.attribute10 is null THEN
1150 l_eam_direct_items_rec.attribute10 := FND_API.G_MISS_CHAR;
1151 END IF;
1152
1153 IF l_prev_dir_attribute11 is not null and l_eam_direct_items_rec.attribute11 is null THEN
1154 l_eam_direct_items_rec.attribute11 := FND_API.G_MISS_CHAR;
1155 END IF;
1156
1157 IF l_prev_dir_attribute12 is not null and l_eam_direct_items_rec.attribute12 is null THEN
1158 l_eam_direct_items_rec.attribute12 := FND_API.G_MISS_CHAR;
1159 END IF;
1160
1161 IF l_prev_dir_attribute13 is not null and l_eam_direct_items_rec.attribute13 is null THEN
1162 l_eam_direct_items_rec.attribute13 := FND_API.G_MISS_CHAR;
1163 END IF;
1164
1165 IF l_prev_dir_attribute14 is not null and l_eam_direct_items_rec.attribute14 is null THEN
1166 l_eam_direct_items_rec.attribute14 := FND_API.G_MISS_CHAR;
1167 END IF;
1168
1169 IF l_prev_dir_attribute15 is not null and l_eam_direct_items_rec.attribute15 is null THEN
1170 l_eam_direct_items_rec.attribute15 := FND_API.G_MISS_CHAR;
1171 END IF;
1172
1173 --Validate descriptive flexfield for workorder
1174 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
1175 p_app_short_name => 'EAM',
1176 p_desc_flex_name => 'EAM_DESC_DIR_ITEM',
1177 p_attribute_category => l_eam_direct_items_rec.attribute_category,
1178 p_attribute1 => l_eam_direct_items_rec.attribute1,
1179 p_attribute2 => l_eam_direct_items_rec.attribute2,
1180 p_attribute3 => l_eam_direct_items_rec.attribute3,
1181 p_attribute4 => l_eam_direct_items_rec.attribute4,
1182 p_attribute5 => l_eam_direct_items_rec.attribute5,
1183 p_attribute6 => l_eam_direct_items_rec.attribute6,
1184 p_attribute7 => l_eam_direct_items_rec.attribute7,
1185 p_attribute8 => l_eam_direct_items_rec.attribute8,
1186 p_attribute9 => l_eam_direct_items_rec.attribute9,
1187 p_attribute10 => l_eam_direct_items_rec.attribute10,
1188 p_attribute11 => l_eam_direct_items_rec.attribute11,
1189 p_attribute12 => l_eam_direct_items_rec.attribute12,
1190 p_attribute13 => l_eam_direct_items_rec.attribute13,
1191 p_attribute14 => l_eam_direct_items_rec.attribute14,
1192 p_attribute15 => l_eam_direct_items_rec.attribute15,
1193 x_error_segments => l_error_segments,
1194 x_error_message => l_error_message
1195 );
1196
1197 l_eam_direct_items_tbl(l_eam_direct_items_tbl.FIRST) := l_eam_direct_items_rec;
1198 END IF;
1199
1200 ----end of changes for 8969942
1201
1202 --Set the auto_charge type to Manual/POReceipt for newly created workorders
1203 l_eam_res_tbl := p_eam_res_tbl;
1204 IF(l_eam_res_tbl IS NOT NULL AND l_eam_res_tbl.COUNT>0) THEN
1205 FOR i IN l_eam_res_tbl.FIRST .. l_eam_res_tbl.LAST LOOP
1206 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
1207 OPEN resources(l_eam_res_tbl(i).resource_id,l_eam_res_tbl(i).organization_id);
1208 FETCH resources INTO l_resources;
1209 IF(resources%FOUND) THEN
1210 IF(l_resources.autocharge_type=1 OR l_resources.autocharge_type=2) THEN
1211 l_eam_res_tbl(i).autocharge_type := 2;
1212 ELSE
1213 l_eam_res_tbl(i).autocharge_type := 3;
1214 END IF;
1215 END IF;
1216 CLOSE resources;
1217 END IF;
1218 END LOOP;
1219 END IF;
1220
1221 --p_failure_code_required will be passed only when failure code information is entered or modified.
1222 --Make sure that work order record is passed whenever failure entry is passed, as we have some logic on work order related info
1223 IF(p_failure_code_required IS NOT NULL) THEN
1224
1225 /* Failure Analysis Project Start */
1226 l_eam_failure_entry_record.failure_id := p_failure_id;
1227 l_eam_failure_entry_record.failure_date := p_failure_date;
1228
1229 l_eam_failure_codes_tbl(1).failure_id := p_failure_id;
1230 l_eam_failure_codes_tbl(1).failure_entry_id := p_failure_entry_id;
1231 l_eam_failure_codes_tbl(1).failure_code := p_failure_code;
1232 l_eam_failure_codes_tbl(1).cause_code := p_cause_code;
1233 l_eam_failure_codes_tbl(1).resolution_code := p_resolution_code;
1234 l_eam_failure_codes_tbl(1).comments := p_failure_comments;
1235 /* Failure Analysis Project End */
1236
1237 l_fail_dept_id := l_eam_wo_rec.owning_department;
1238
1239 --ideally work order info should be passed when failure info is passed. But check that workorder is passed
1240 IF(l_eam_wo_tbl IS NOT NULL AND l_eam_wo_tbl.COUNT>0) THEN
1241
1242 --only if asset number/rebuild serial number exists for work order
1243 IF(l_maintenance_object_type =3) THEN
1244 BEGIN
1245
1246 --if workorder dept. is null,de fault it from asset's owning dept
1247 IF(l_fail_dept_id IS NULL) THEN
1248 SELECT OWNING_DEPARTMENT_ID
1249 INTO l_fail_dept_id
1250 FROM eam_org_maint_defaults
1251 WHERE object_id =l_maintenance_object_id
1252 AND object_type = 50
1253 AND organization_id =l_eam_wo_comp_rec.organization_id;
1254 END IF;
1255
1256 SELECT area_id
1257 INTO l_eam_location_id
1258 FROM eam_org_maint_defaults
1259 WHERE object_id = l_maintenance_object_id
1260 AND object_type = 50
1261 AND organization_id = l_eam_wo_comp_rec.organization_id;
1262
1263 EXCEPTION
1264 WHEN NO_DATA_FOUND THEN
1265 NULL;
1266 END;
1267 END IF; --end of check for mainteannce_object_type =3
1268
1269 l_eam_wo_rec.failure_code_required := p_failure_code_required;
1270
1271 IF(l_wo_exists = TRUE) THEN
1272
1273 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
1274 l_eam_failure_entry_record.source_type := 1;
1275 l_eam_failure_entry_record.source_id := l_eam_wo_rec.wip_entity_id;
1276 l_eam_failure_entry_record.object_type := l_eam_wo_rec.maintenance_object_type;
1277 l_eam_failure_entry_record.object_id := l_eam_wo_rec.maintenance_object_id;
1278 l_eam_failure_entry_record.maint_organization_id := l_eam_wo_rec.organization_id;
1279 l_eam_failure_entry_record.current_organization_id := l_eam_wo_rec.organization_id;
1280 l_eam_failure_entry_record.department_id := l_fail_dept_id;
1281 l_eam_failure_entry_record.area_id := l_eam_location_id;
1282
1283
1284 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
1285 if (l_eam_failure_entry_record.failure_date is null) then
1286 l_eam_failure_entry_record.failure_date := FND_API.G_MISS_DATE;
1287 end if;
1288 if (l_eam_failure_codes_tbl(1).failure_code is null) then
1289 l_eam_failure_codes_tbl(1).failure_code := FND_API.G_MISS_CHAR;
1290 end if;
1291 if (l_eam_failure_codes_tbl(1).cause_code is null) then
1292 l_eam_failure_codes_tbl(1).cause_code := FND_API.G_MISS_CHAR;
1293 end if;
1294 if (l_eam_failure_codes_tbl(1).resolution_code is null) then
1295 l_eam_failure_codes_tbl(1).resolution_code := FND_API.G_MISS_CHAR;
1296 end if;
1297 if (l_eam_failure_codes_tbl(1).comments is null) then
1298 l_eam_failure_codes_tbl(1).comments := FND_API.G_MISS_CHAR;
1299 end if;
1300 if(l_eam_failure_entry_record.failure_id is not null ) then
1301 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
1302 else
1303 if(l_eam_failure_entry_record.failure_date = FND_API.G_MISS_DATE) then
1304 l_eam_failure_entry_record.transaction_type :=null;
1305 l_eam_failure_entry_record.failure_date :=null;
1306 else
1307 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE ;
1308 end if;
1309 end if;
1310
1311 if(l_eam_failure_codes_tbl(1).failure_entry_id is not null) then
1312 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
1313 l_eam_wo_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
1314
1315 else
1316 if( not( (l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR)
1317 and (l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR)
1318 and (l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR)
1319 and (l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR)
1320 )
1321 ) then
1322 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
1323 if(l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR) then
1324 l_eam_failure_codes_tbl(1).failure_code := null;
1325 end if;
1326 if(l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR) then
1327 l_eam_failure_codes_tbl(1).cause_code := null;
1328 end if;
1329 if(l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR) then
1330 l_eam_failure_codes_tbl(1).resolution_code := null;
1331 end if;
1332 if(l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR) then
1333 l_eam_failure_codes_tbl(1).comments := null;
1334 end if;
1335 l_eam_wo_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
1336
1337 else
1338 l_eam_failure_codes_tbl.delete;
1339 l_eam_wo_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
1340
1341 end if;
1342 end if;
1343 l_eam_wo_rec.eam_failure_entry_record := l_eam_failure_entry_record;
1344
1345 ELSE -- work order is getting created
1346
1347 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
1348 l_eam_failure_entry_record.source_type := 1;
1349 l_eam_failure_entry_record.source_id := l_eam_wo_rec.wip_entity_id;
1350 l_eam_failure_entry_record.object_type := l_eam_wo_rec.maintenance_object_type;
1351 l_eam_failure_entry_record.object_id := l_eam_wo_rec.maintenance_object_id;
1352 l_eam_failure_entry_record.maint_organization_id := l_eam_wo_rec.organization_id;
1353 l_eam_failure_entry_record.current_organization_id := l_eam_wo_rec.organization_id;
1354 l_eam_failure_entry_record.department_id := l_fail_dept_id;
1355 l_eam_failure_entry_record.area_id := l_eam_location_id;
1356
1357 if(l_eam_failure_entry_record.failure_date is null) then
1358 l_eam_failure_entry_record.transaction_type :=null;
1359 end if;
1360 l_eam_wo_rec.eam_failure_entry_record := l_eam_failure_entry_record;
1361
1362 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
1363 if( not( l_eam_failure_codes_tbl(1).failure_code is null
1364 and l_eam_failure_codes_tbl(1).cause_code is null
1365 and l_eam_failure_codes_tbl(1).resolution_code is null
1366 and l_eam_failure_codes_tbl(1).comments is null
1367 )
1368 ) then
1369 l_eam_wo_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
1370 else
1371 l_eam_failure_codes_tbl.delete;
1372 l_eam_wo_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
1373 end if;
1374
1375 END IF;
1376
1377
1378 l_eam_wo_tbl(l_eam_wo_tbl.FIRST) := l_eam_wo_rec;
1379
1380 END IF; --end of check for work order record passed
1381
1382 END IF; --end of check for failure data passed
1383
1384
1385 x_wip_entity_id := NULL;
1386
1387 IF(p_eam_wo_relations_tbl IS NOT NULL AND p_eam_wo_relations_tbl.COUNT > 0) THEN
1388
1389 EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO(
1390 p_bo_identifier =>'EAM'
1391 , p_api_version_number => 1.0
1392 , p_init_msg_list => TRUE
1393 , p_eam_wo_relations_tbl => p_eam_wo_relations_tbl
1394 , p_eam_wo_tbl => l_eam_wo_tbl
1395 , p_eam_op_tbl => l_eam_op_tbl
1396 , p_eam_op_network_tbl => l_eam_op_network_tbl
1397 , p_eam_res_tbl => l_eam_res_tbl
1398 , p_eam_res_inst_tbl => p_eam_res_inst_tbl
1399 ,p_eam_res_usage_tbl => p_eam_res_usage_tbl
1400 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1401 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1402 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
1403 , p_eam_wo_comp_tbl => p_eam_wo_comp_tbl
1404 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1405 , p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
1406 , p_eam_counter_prop_tbl => p_eam_counter_prop_tbl
1407 , p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl
1408 , p_eam_wo_comp_mr_read_tbl => p_eam_wo_comp_mr_read_tbl
1409 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1410 , p_eam_request_tbl => p_eam_request_tbl
1411 , x_eam_wo_tbl => l_eam_wo_tbl_out
1412 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl_out
1413 , x_eam_op_tbl => l_eam_op_tbl_out
1414 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
1415 , x_eam_res_tbl => l_eam_res_tbl_out
1416 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
1417 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
1418 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
1419 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
1420 , x_eam_direct_items_tbl =>l_eam_direct_items_tbl_out
1421 , x_eam_wo_comp_tbl => l_eam_wo_comp_tbl_out
1422 , x_eam_wo_quality_tbl => l_eam_wo_quality_tbl_out
1423 , x_eam_meter_reading_tbl => l_eam_meter_reading_tbl_out
1424 , x_eam_counter_prop_tbl => l_eam_counter_prop_tbl_out
1425 , x_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl_out
1426 , x_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl_out
1427 , x_eam_op_comp_tbl => l_eam_op_comp_tbl_out
1428 , x_eam_request_tbl => l_eam_request_tbl_out
1429 , x_return_status => x_return_status
1430 , x_msg_count => x_msg_count
1431 , p_commit => 'N'
1432 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1433 , p_output_dir => l_output_dir
1434 , p_debug_filename => 'createupdatewo.log'
1435 , p_debug_file_mode => 'W'
1436 );
1437
1438 ELSE
1439 IF ( l_eam_wo_tbl.COUNT > 0 ) THEN
1440 l_eam_wo_rec := l_eam_wo_tbl(l_eam_wo_tbl.FIRST);
1441 END IF;
1442
1443 IF ( l_eam_wo_comp_tbl.COUNT > 0 ) THEN
1444 l_eam_wo_comp_rec := l_eam_wo_comp_tbl(l_eam_wo_comp_tbl.FIRST);
1445 END IF;
1446
1447 EAM_PROCESS_WO_PUB.PROCESS_WO(
1448 p_bo_identifier =>'EAM'
1449 , p_api_version_number => 1.0
1450 , p_init_msg_list => TRUE
1451 , p_eam_wo_rec => l_eam_wo_rec
1452 , p_eam_op_tbl => l_eam_op_tbl
1453 , p_eam_op_network_tbl => l_eam_op_network_tbl
1454 , p_eam_res_tbl => l_eam_res_tbl
1455 , p_eam_res_inst_tbl => p_eam_res_inst_tbl
1456 , p_eam_res_usage_tbl => p_eam_res_usage_tbl
1457 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1458 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1459 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
1460 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
1461 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1462 , p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
1463 , p_eam_counter_prop_tbl => p_eam_counter_prop_tbl
1464 , p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl
1465 , p_eam_wo_comp_mr_read_tbl => p_eam_wo_comp_mr_read_tbl
1466 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1467 , p_eam_request_tbl => p_eam_request_tbl
1468 , x_eam_wo_rec => l_eam_wo_rec_out
1469 , x_eam_op_tbl => l_eam_op_tbl_out
1470 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
1471 , x_eam_res_tbl => l_eam_res_tbl_out
1472 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
1473 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
1474 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
1475 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
1476 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_out
1477 , x_eam_wo_comp_rec => l_eam_wo_comp_rec_out
1478 , x_eam_wo_quality_tbl => l_eam_wo_quality_tbl_out
1479 , x_eam_meter_reading_tbl => l_eam_meter_reading_tbl_out
1480 , x_eam_counter_prop_tbl => l_eam_counter_prop_tbl_out
1481 , x_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl_out
1482 , x_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl_out
1483 , x_eam_op_comp_tbl => l_eam_op_comp_tbl_out
1484 , x_eam_request_tbl => l_eam_request_tbl_out
1485 , x_return_status => x_return_status
1486 , x_msg_count => x_msg_count
1487 , p_commit => 'N'
1488 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1489 , p_output_dir => l_output_dir
1490 , p_debug_filename => 'createupdatewo.log'
1491 , p_debug_file_mode => 'W'
1492 );
1493
1494 END IF;
1495
1496 END IF; /*MSP IF END*/
1497
1498 IF(x_return_status='S') THEN
1499 IF p_commit = FND_API.G_TRUE THEN
1500 COMMIT WORK;
1501 end if;
1502 IF(l_eam_wo_tbl_out IS NOT NULL AND l_eam_wo_tbl_out.COUNT>0) THEN
1503 x_wip_entity_id := l_eam_wo_tbl_out(l_eam_wo_tbl_out.FIRST).wip_entity_id;
1504 ELSIF(l_eam_wo_rec_out.wip_entity_id IS NOT NULL) THEN
1505 x_wip_entity_id := l_eam_wo_rec_out.wip_entity_id;
1506 END IF;
1507 END IF;
1508
1509 IF(x_return_status <> 'S') THEN
1510 ROLLBACK TO create_update_wo;
1511 END IF;
1512
1513 END CREATE_UPDATE_WO;
1514
1515
1516 /*********************************************************
1517 Wrapper procedure on top of WO API.This is used to create/update workorder with permits(old 12.1.3 code to be compatible with 12.2)
1518 ************************************************/
1519 PROCEDURE CREATE_UPDATE_WO
1520 (
1521 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1522 p_eam_wo_tbl IN EAM_PROCESS_WO_PUB.eam_wo_tbl_type,
1523 p_eam_wo_relations_tbl IN EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type,
1524 p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type,
1525 p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type,
1526 p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type,
1527 p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type,
1528 p_eam_mat_req_tbl IN EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type,
1529 p_eam_direct_items_tbl IN EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type,
1530 p_eam_request_tbl IN EAM_PROCESS_WO_PUB.eam_request_tbl_type,
1531 p_eam_wo_comp_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type,
1532 p_eam_meter_reading_tbl IN EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type,
1533 p_eam_counter_prop_tbl IN EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type,
1534 p_eam_wo_comp_rebuild_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type,
1535 p_eam_wo_comp_mr_read_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type,
1536 p_eam_permit_tbl IN EAM_PROCESS_PERMIT_PUB.eam_wp_tbl_type, -- new param for safety permit
1537 p_eam_permit_wo_assoc_tbl IN EAM_PROCESS_PERMIT_PUB.eam_wp_association_tbl_type, -- new param for safety permit
1538 p_prev_activity_id IN NUMBER,
1539 p_failure_id IN NUMBER := null,
1540 p_failure_date IN DATE := null,
1541 p_failure_entry_id IN NUMBER := null,
1542 p_failure_code IN VARCHAR2 := null,
1543 p_cause_code IN VARCHAR2 := null,
1544 p_resolution_code IN VARCHAR2 := null,
1545 p_failure_comments IN VARCHAR2 := null,
1546 p_failure_code_required IN VARCHAR2 DEFAULT NULL,
1547 x_wip_entity_id OUT NOCOPY NUMBER,
1548 x_return_status OUT NOCOPY VARCHAR2,
1549 x_msg_count OUT NOCOPY NUMBER
1550 )
1551 IS
1552 l_eam_permit_tbl EAM_PROCESS_SAFETY_PUB.eam_wp_tbl_type;
1553 l_eam_permit_wo_assoc_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
1554 l_eam_work_clearance_tbl EAM_PROCESS_SAFETY_PUB.eam_clearance_header_tbl_type;-- new parameter for safety clearance
1555 l_eam_wc_wo_assoc_tbl EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type;
1556
1557 BEGIN
1558
1559
1560 -- copy old table in 12.1.3 code to 12.2 code.
1561
1562 IF(p_eam_permit_tbl IS NOT NULL AND p_eam_permit_tbl.COUNT>0) THEN
1563
1564 FOR i IN p_eam_permit_tbl.FIRST .. p_eam_permit_tbl.LAST LOOP
1565 l_eam_permit_tbl(i).HEADER_ID:= p_eam_permit_tbl(i).HEADER_ID;
1566 l_eam_permit_tbl(i).BATCH_ID:= p_eam_permit_tbl(i).BATCH_ID;
1567 l_eam_permit_tbl(i).ROW_ID:= p_eam_permit_tbl(i).ROW_ID;
1568 l_eam_permit_tbl(i).TRANSACTION_TYPE:= p_eam_permit_tbl(i).TRANSACTION_TYPE;
1569 l_eam_permit_tbl(i).PERMIT_ID:= p_eam_permit_tbl(i).PERMIT_ID;
1570 l_eam_permit_tbl(i).PERMIT_NAME:= p_eam_permit_tbl(i).PERMIT_NAME;
1571 l_eam_permit_tbl(i).PERMIT_TYPE:= p_eam_permit_tbl(i).PERMIT_TYPE;
1572 l_eam_permit_tbl(i).DESCRIPTION:= p_eam_permit_tbl(i).DESCRIPTION;
1573 l_eam_permit_tbl(i).ORGANIZATION_ID:= p_eam_permit_tbl(i).ORGANIZATION_ID;
1574 l_eam_permit_tbl(i).STATUS_TYPE:= p_eam_permit_tbl(i).STATUS_TYPE;
1575 l_eam_permit_tbl(i).VALID_FROM:= p_eam_permit_tbl(i).VALID_FROM;
1576 l_eam_permit_tbl(i).VALID_TO:= p_eam_permit_tbl(i).VALID_TO;
1577 l_eam_permit_tbl(i).PENDING_FLAG:= p_eam_permit_tbl(i).PENDING_FLAG;
1578 l_eam_permit_tbl(i).COMPLETION_DATE:= p_eam_permit_tbl(i).COMPLETION_DATE;
1579 l_eam_permit_tbl(i).USER_DEFINED_STATUS_ID:= p_eam_permit_tbl(i).USER_DEFINED_STATUS_ID;
1580 l_eam_permit_tbl(i).ATTRIBUTE_CATEGORY:= p_eam_permit_tbl(i).ATTRIBUTE_CATEGORY;
1581 l_eam_permit_tbl(i).ATTRIBUTE1:= p_eam_permit_tbl(i).ATTRIBUTE1;
1582 l_eam_permit_tbl(i).ATTRIBUTE2:= p_eam_permit_tbl(i).ATTRIBUTE2;
1583 l_eam_permit_tbl(i).ATTRIBUTE3:= p_eam_permit_tbl(i).ATTRIBUTE3;
1584 l_eam_permit_tbl(i).ATTRIBUTE4:= p_eam_permit_tbl(i).ATTRIBUTE4;
1585 l_eam_permit_tbl(i).ATTRIBUTE5:= p_eam_permit_tbl(i).ATTRIBUTE5;
1586 l_eam_permit_tbl(i).ATTRIBUTE6:= p_eam_permit_tbl(i).ATTRIBUTE6;
1587 l_eam_permit_tbl(i).ATTRIBUTE7:= p_eam_permit_tbl(i).ATTRIBUTE7;
1588 l_eam_permit_tbl(i).ATTRIBUTE8:= p_eam_permit_tbl(i).ATTRIBUTE8;
1589 l_eam_permit_tbl(i).ATTRIBUTE9:= p_eam_permit_tbl(i).ATTRIBUTE9;
1590 l_eam_permit_tbl(i).ATTRIBUTE10:= p_eam_permit_tbl(i).ATTRIBUTE10;
1591 l_eam_permit_tbl(i).ATTRIBUTE11:= p_eam_permit_tbl(i).ATTRIBUTE11;
1592 l_eam_permit_tbl(i).ATTRIBUTE12:= p_eam_permit_tbl(i).ATTRIBUTE12;
1593 l_eam_permit_tbl(i).ATTRIBUTE13:= p_eam_permit_tbl(i).ATTRIBUTE13;
1594 l_eam_permit_tbl(i).ATTRIBUTE14:= p_eam_permit_tbl(i).ATTRIBUTE14;
1595 l_eam_permit_tbl(i).ATTRIBUTE15:= p_eam_permit_tbl(i).ATTRIBUTE15;
1596 l_eam_permit_tbl(i).ATTRIBUTE16:= p_eam_permit_tbl(i).ATTRIBUTE16;
1597 l_eam_permit_tbl(i).ATTRIBUTE17:= p_eam_permit_tbl(i).ATTRIBUTE17;
1598 l_eam_permit_tbl(i).ATTRIBUTE18:= p_eam_permit_tbl(i).ATTRIBUTE18;
1599 l_eam_permit_tbl(i).ATTRIBUTE19:= p_eam_permit_tbl(i).ATTRIBUTE19;
1600 l_eam_permit_tbl(i).ATTRIBUTE20:= p_eam_permit_tbl(i).ATTRIBUTE20;
1601 l_eam_permit_tbl(i).ATTRIBUTE21:= p_eam_permit_tbl(i).ATTRIBUTE21;
1602 l_eam_permit_tbl(i).ATTRIBUTE22:= p_eam_permit_tbl(i).ATTRIBUTE22;
1603 l_eam_permit_tbl(i).ATTRIBUTE23:= p_eam_permit_tbl(i).ATTRIBUTE23;
1604 l_eam_permit_tbl(i).ATTRIBUTE24:= p_eam_permit_tbl(i).ATTRIBUTE24;
1605 l_eam_permit_tbl(i).ATTRIBUTE25:= p_eam_permit_tbl(i).ATTRIBUTE25;
1606 l_eam_permit_tbl(i).ATTRIBUTE26:= p_eam_permit_tbl(i).ATTRIBUTE26;
1607 l_eam_permit_tbl(i).ATTRIBUTE27:= p_eam_permit_tbl(i).ATTRIBUTE27;
1608 l_eam_permit_tbl(i).ATTRIBUTE28:= p_eam_permit_tbl(i).ATTRIBUTE28;
1609 l_eam_permit_tbl(i).ATTRIBUTE29:= p_eam_permit_tbl(i).ATTRIBUTE29;
1610 l_eam_permit_tbl(i).ATTRIBUTE30:= p_eam_permit_tbl(i).ATTRIBUTE30;
1611 l_eam_permit_tbl(i).APPROVED_BY:= p_eam_permit_tbl(i).APPROVED_BY;
1612 l_eam_permit_tbl(i).CREATED_BY:= p_eam_permit_tbl(i).CREATED_BY;
1613 l_eam_permit_tbl(i).CREATION_DATE:= p_eam_permit_tbl(i).CREATION_DATE;
1614 l_eam_permit_tbl(i).USER_ID:= p_eam_permit_tbl(i).USER_ID;
1615 l_eam_permit_tbl(i).RESPONSIBILITY_ID:= p_eam_permit_tbl(i).RESPONSIBILITY_ID;
1616 END LOOP;
1617
1618 END IF;
1619
1620 IF(p_eam_permit_wo_assoc_tbl IS NOT NULL AND p_eam_permit_wo_assoc_tbl.COUNT>0) THEN
1621
1622 FOR i IN p_eam_permit_wo_assoc_tbl.FIRST .. p_eam_permit_wo_assoc_tbl.LAST LOOP
1623 l_eam_permit_wo_assoc_tbl(i).HEADER_ID:= p_eam_permit_wo_assoc_tbl(i).HEADER_ID;
1624 l_eam_permit_wo_assoc_tbl(i).BATCH_ID:= p_eam_permit_wo_assoc_tbl(i).BATCH_ID;
1625 l_eam_permit_wo_assoc_tbl(i).ROW_ID:= p_eam_permit_wo_assoc_tbl(i).ROW_ID;
1626 l_eam_permit_wo_assoc_tbl(i).TRANSACTION_TYPE:= p_eam_permit_wo_assoc_tbl(i).TRANSACTION_TYPE;
1627 l_eam_permit_wo_assoc_tbl(i).SAFETY_ASSOCIATION_ID:=p_eam_permit_wo_assoc_tbl(i).SAFETY_ASSOCIATION_ID;
1628 l_eam_permit_wo_assoc_tbl(i).SOURCE_ID:= p_eam_permit_wo_assoc_tbl(i).SOURCE_ID;
1629 l_eam_permit_wo_assoc_tbl(i).TARGET_REF_ID:= p_eam_permit_wo_assoc_tbl(i).TARGET_REF_ID;
1630 l_eam_permit_wo_assoc_tbl(i).ASSOCIATION_TYPE:= p_eam_permit_wo_assoc_tbl(i).ASSOCIATION_TYPE;
1631 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE_CATEGORY:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE_CATEGORY;
1632 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE1:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE1;
1633 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE2:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE2;
1634 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE3:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE3;
1635 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE4:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE4;
1636 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE5:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE5;
1637 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE6:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE6;
1638 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE7:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE7;
1639 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE8:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE8;
1640 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE9:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE9;
1641 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE10:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE10;
1642 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE11:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE11;
1643 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE12:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE12;
1644 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE13:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE13;
1645 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE14:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE14;
1646 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE15:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE15;
1647 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE16:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE16;
1648 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE17:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE17;
1649 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE18:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE18;
1650 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE19:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE19;
1651 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE20:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE20;
1652 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE21:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE21;
1653 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE22:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE22;
1654 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE23:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE23;
1655 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE24:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE24;
1656 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE25:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE25;
1657 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE26:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE26;
1658 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE27:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE27;
1659 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE28:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE28;
1660 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE29:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE29;
1661 l_eam_permit_wo_assoc_tbl(i).ATTRIBUTE30:= p_eam_permit_wo_assoc_tbl(i).ATTRIBUTE30;
1662 l_eam_permit_wo_assoc_tbl(i).CREATED_BY:= p_eam_permit_wo_assoc_tbl(i).CREATED_BY;
1663 l_eam_permit_wo_assoc_tbl(i).CREATION_DATE:= p_eam_permit_wo_assoc_tbl(i).CREATION_DATE;
1664 END LOOP;
1665
1666 END IF;
1667
1668 -- Delegate call to new signature
1669
1670 BEGIN
1671 CREATE_UPDATE_WO
1672 (
1673 p_commit => p_commit,
1674 p_eam_wo_tbl =>p_eam_wo_tbl,
1675 p_eam_wo_relations_tbl => p_eam_wo_relations_tbl,
1676 p_eam_op_tbl => p_eam_op_tbl,
1677 p_eam_res_tbl => p_eam_res_tbl ,
1678 p_eam_res_inst_tbl => p_eam_res_inst_tbl,
1679 p_eam_res_usage_tbl => p_eam_res_usage_tbl ,
1680 p_eam_mat_req_tbl => p_eam_mat_req_tbl ,
1681 p_eam_direct_items_tbl => p_eam_direct_items_tbl,
1682 p_eam_request_tbl => p_eam_request_tbl ,
1683 p_eam_wo_comp_tbl => p_eam_wo_comp_tbl,
1684 p_eam_meter_reading_tbl => p_eam_meter_reading_tbl ,
1685 p_eam_counter_prop_tbl => p_eam_counter_prop_tbl ,
1686 p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl,
1687 p_eam_wo_comp_mr_read_tbl => p_eam_wo_comp_mr_read_tbl,
1688 p_eam_permit_tbl => l_eam_permit_tbl, -- new param for safety permit
1689 p_eam_permit_wo_assoc_tbl => l_eam_permit_wo_assoc_tbl, -- new param for safety permit
1690 p_eam_work_clearance_tbl => l_eam_work_clearance_tbl ,-- new parameter for safety clearance
1691 p_eam_wc_wo_assoc_tbl => l_eam_wc_wo_assoc_tbl , --new parameter for safety clearance
1692 p_prev_activity_id => p_prev_activity_id,
1693 p_failure_id => p_failure_id,
1694 p_failure_date => p_failure_date ,
1695 p_failure_entry_id => p_failure_entry_id,
1696 p_failure_code => p_failure_code,
1697 p_cause_code => p_cause_code,
1698 p_resolution_code => p_resolution_code,
1699 p_failure_comments => p_failure_comments,
1700 p_failure_code_required =>p_failure_code_required ,
1701 x_wip_entity_id =>x_wip_entity_id ,
1702 x_return_status =>x_return_status ,
1703 x_msg_count =>x_msg_count
1704 );
1705 END;
1706
1707
1708
1709 END CREATE_UPDATE_WO;
1710
1711
1712 /*********************************************************
1713 Wrapper procedure on top of WO API.Overloaded procedure of CREATE_UPDATE_WO for safety
1714 This is used to create/update workorder and its related entities.Added for new signatures
1715 and for clearance.This currently can not be invoked from UI as the generated Rosetta gives
1716 method too large error for this particular procedure.
1717 ************************************************/
1718 PROCEDURE CREATE_UPDATE_WO
1719 (
1720 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1721 p_eam_wo_tbl IN EAM_PROCESS_WO_PUB.eam_wo_tbl_type,
1722 p_eam_wo_relations_tbl IN EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type,
1723 p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type,
1724 p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type,
1725 p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type,
1726 p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type,
1727 p_eam_mat_req_tbl IN EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type,
1728 p_eam_direct_items_tbl IN EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type,
1729 p_eam_request_tbl IN EAM_PROCESS_WO_PUB.eam_request_tbl_type,
1730 p_eam_wo_comp_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type,
1731 p_eam_meter_reading_tbl IN EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type,
1732 p_eam_counter_prop_tbl IN EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type,
1733 p_eam_wo_comp_rebuild_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type,
1734 p_eam_wo_comp_mr_read_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type,
1735 p_eam_permit_tbl IN EAM_PROCESS_SAFETY_PUB.eam_wp_tbl_type, -- new param for safety permit
1736 p_eam_permit_wo_assoc_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type, -- new param for safety permit
1737 p_eam_work_clearance_tbl IN EAM_PROCESS_SAFETY_PUB.eam_clearance_header_tbl_type ,-- new parameter for safety clearance
1738 p_eam_wc_wo_assoc_tbl IN EAM_PROCESS_SAFETY_PUB.eam_safety_assoc_tbl_type , --new parameter for safety clearance
1739 p_prev_activity_id IN NUMBER,
1740 p_failure_id IN NUMBER := null,
1741 p_failure_date IN DATE := null,
1742 p_failure_entry_id IN NUMBER := null,
1743 p_failure_code IN VARCHAR2 := null,
1744 p_cause_code IN VARCHAR2 := null,
1745 p_resolution_code IN VARCHAR2 := null,
1746 p_failure_comments IN VARCHAR2 := null,
1747 p_failure_code_required IN VARCHAR2 DEFAULT NULL,
1748 x_wip_entity_id OUT NOCOPY NUMBER,
1749 x_return_status OUT NOCOPY VARCHAR2,
1750 x_msg_count OUT NOCOPY NUMBER
1751 ) IS
1752
1753
1754 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
1755 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1756 l_import_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type; --MSP Project
1757 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1758 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1759 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
1760 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1761 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1762 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1763 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1764 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
1765 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1766 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1767 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
1768 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1769 l_eam_direct_items_rec EAM_PROCESS_WO_PUB.eam_direct_items_rec_type;
1770
1771 l_eam_wo_relations_tbl_out EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
1772 l_eam_wo_rec_out EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1773 l_eam_wo_tbl_out EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
1774 l_eam_op_tbl_out EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1775 l_eam_op_network_tbl_out EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1776 l_eam_res_tbl_out EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1777 l_eam_res_usage_tbl_out EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1778 l_eam_res_inst_tbl_out EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1779 l_eam_sub_res_tbl_out EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1780 l_eam_mat_req_tbl_out EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1781 l_eam_direct_items_tbl_out EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1782 l_eam_wo_comp_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
1783 l_eam_wo_quality_tbl_out EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1784 l_eam_meter_reading_tbl_out EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1785 l_eam_counter_prop_tbl_out EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1786 l_eam_wo_comp_rebuild_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1787 l_eam_wo_comp_mr_read_tbl_out EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1788 l_eam_op_comp_tbl_out EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1789 l_eam_request_tbl_out EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1790 l_eam_wo_comp_rec_out EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
1791
1792
1793 l_output_dir VARCHAR2(512);
1794
1795 CURSOR resources
1796 (l_resource_id NUMBER,l_organization_id NUMBER)
1797 IS
1798 SELECT autocharge_type
1799 FROM BOM_RESOURCES
1800 WHERE resource_id = l_resource_id
1801 AND organization_id = l_organization_id;
1802
1803 l_resources resources%ROWTYPE;
1804 l_wip_entity_id NUMBER;
1805 l_asset_group_id NUMBER;
1806 l_asset_number VARCHAR2(30);
1807 l_rebuild_item_id NUMBER;
1808 l_rebuild_serial_number VARCHAR2(30);
1809
1810 /* Added for bug#4555609 */
1811 l_prev_activity_id NUMBER;
1812 l_prev_description VARCHAR2(240);
1813 l_prev_priority NUMBER;
1814 l_prev_work_order_type VARCHAR2(30);
1815 l_prev_shutdown_type VARCHAR2(30);
1816 l_prev_activity_type VARCHAR2(30);
1817 l_prev_activity_cause VARCHAR2(30);
1818 l_prev_activity_source VARCHAR2(30);
1819 l_prev_attribute_category VARCHAR2(30);
1820 l_prev_attribute1 VARCHAR2(150);
1821 l_prev_attribute2 VARCHAR2(150);
1822 l_prev_attribute3 VARCHAR2(150);
1823 l_prev_attribute4 VARCHAR2(150);
1824 l_prev_attribute5 VARCHAR2(150);
1825 l_prev_attribute6 VARCHAR2(150);
1826 l_prev_attribute7 VARCHAR2(150);
1827 l_prev_attribute8 VARCHAR2(150);
1828 l_prev_attribute9 VARCHAR2(150);
1829 l_prev_attribute10 VARCHAR2(150);
1830 l_prev_attribute11 VARCHAR2(150);
1831 l_prev_attribute12 VARCHAR2(150);
1832 l_prev_attribute13 VARCHAR2(150);
1833 l_prev_attribute14 VARCHAR2(150);
1834 l_prev_attribute15 VARCHAR2(150);
1835 l_maintenance_object_type NUMBER;
1836 l_maintenance_object_id NUMBER;
1837 /* Added for bug#6053425 Start */
1838 l_prev_project_id NUMBER;
1839 l_prev_task_id NUMBER;
1840 /* Added for bug#6053425 End */
1841
1842 l_eam_failure_entry_record EAM_Process_Failure_Entry_PUB.eam_failure_entry_record_typ;
1843 l_eam_failure_codes_tbl EAM_Process_Failure_Entry_PUB.eam_failure_codes_tbl_typ;
1844 l_fail_dept_id NUMBER;
1845 l_eam_location_id NUMBER;
1846 l_eam_failure_code_required varchar2(30);
1847 l_wo_exists BOOLEAN;
1848 l_org_id NUMBER;
1849 l_validate BOOLEAN;
1850 l_error_segments number;
1851 l_error_message varchar2(2000);
1852
1853 -- Added for 8969942
1854
1855 l_organization_id NUMBER;
1856 l_Operation_Seq_Num NUMBER;
1857 l_inventory_item_id NUMBER;
1858 l_dir_item_seq_id NUMBER;
1859
1860 l_prev_op_description VARCHAR2(240);
1861 l_prev_op_long_description VARCHAR2(4000);
1862 l_prev_op_shutdown_type VARCHAR2(30);
1863 l_prev_op_attribute_category VARCHAR2(30);
1864 l_prev_op_attribute1 VARCHAR2(150);
1865 l_prev_op_attribute2 VARCHAR2(150);
1866 l_prev_op_attribute3 VARCHAR2(150);
1867 l_prev_op_attribute4 VARCHAR2(150);
1868 l_prev_op_attribute5 VARCHAR2(150);
1869 l_prev_op_attribute6 VARCHAR2(150);
1870 l_prev_op_attribute7 VARCHAR2(150);
1871 l_prev_op_attribute8 VARCHAR2(150);
1872 l_prev_op_attribute9 VARCHAR2(150);
1873 l_prev_op_attribute10 VARCHAR2(150);
1874 l_prev_op_attribute11 VARCHAR2(150);
1875 l_prev_op_attribute12 VARCHAR2(150);
1876 l_prev_op_attribute13 VARCHAR2(150);
1877 l_prev_op_attribute14 VARCHAR2(150);
1878 l_prev_op_attribute15 VARCHAR2(150);
1879
1880 l_prev_mat_attribute_category VARCHAR2(30);
1881 l_prev_mat_attribute1 VARCHAR2(150);
1882 l_prev_mat_attribute2 VARCHAR2(150);
1883 l_prev_mat_attribute3 VARCHAR2(150);
1884 l_prev_mat_attribute4 VARCHAR2(150);
1885 l_prev_mat_attribute5 VARCHAR2(150);
1886 l_prev_mat_attribute6 VARCHAR2(150);
1887 l_prev_mat_attribute7 VARCHAR2(150);
1888 l_prev_mat_attribute8 VARCHAR2(150);
1889 l_prev_mat_attribute9 VARCHAR2(150);
1890 l_prev_mat_attribute10 VARCHAR2(150);
1891 l_prev_mat_attribute11 VARCHAR2(150);
1892 l_prev_mat_attribute12 VARCHAR2(150);
1893 l_prev_mat_attribute13 VARCHAR2(150);
1894 l_prev_mat_attribute14 VARCHAR2(150);
1895 l_prev_mat_attribute15 VARCHAR2(150);
1896
1897 l_prev_dir_attribute_category VARCHAR2(30);
1898 l_prev_dir_attribute1 VARCHAR2(150);
1899 l_prev_dir_attribute2 VARCHAR2(150);
1900 l_prev_dir_attribute3 VARCHAR2(150);
1901 l_prev_dir_attribute4 VARCHAR2(150);
1902 l_prev_dir_attribute5 VARCHAR2(150);
1903 l_prev_dir_attribute6 VARCHAR2(150);
1904 l_prev_dir_attribute7 VARCHAR2(150);
1905 l_prev_dir_attribute8 VARCHAR2(150);
1906 l_prev_dir_attribute9 VARCHAR2(150);
1907 l_prev_dir_attribute10 VARCHAR2(150);
1908 l_prev_dir_attribute11 VARCHAR2(150);
1909 l_prev_dir_attribute12 VARCHAR2(150);
1910 l_prev_dir_attribute13 VARCHAR2(150);
1911 l_prev_dir_attribute14 VARCHAR2(150);
1912 l_prev_dir_attribute15 VARCHAR2(150);
1913
1914 BEGIN
1915
1916 /* get output directory path from database */
1917 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
1918
1919 SAVEPOINT create_update_wo;
1920 /*MSP Project addition*/
1921 IF(p_eam_wo_tbl IS NOT NULL) THEN
1922 IF(p_eam_wo_tbl.first is not NULL) THEN
1923 l_import_eam_wo_rec := p_eam_wo_tbl(p_eam_wo_tbl.first);
1924 END IF;
1925 END IF;
1926
1927 IF(l_import_eam_wo_rec.ATTRIBUTE15='import') THEN
1928 eam_import_workorders.import_workorders(p_commit,
1929 p_eam_wo_tbl,
1930 p_eam_wo_relations_tbl,
1931 p_eam_op_tbl,
1932 p_eam_res_tbl,
1933 p_eam_res_inst_tbl,
1934 p_eam_res_usage_tbl,
1935 p_eam_mat_req_tbl,
1936 p_eam_direct_items_tbl,
1937 p_eam_request_tbl,
1938 p_eam_wo_comp_tbl,
1939 p_eam_meter_reading_tbl,
1940 p_eam_counter_prop_tbl,
1941 p_eam_wo_comp_rebuild_tbl,
1942 p_eam_wo_comp_mr_read_tbl,
1943 x_wip_entity_id,
1944 x_return_status,
1945 x_msg_count);
1946 ELSE /*MSP code end*/
1947
1948 l_eam_wo_comp_tbl := p_eam_wo_comp_tbl;
1949
1950 IF(l_eam_wo_comp_tbl IS NOT NULL AND l_eam_wo_comp_tbl.COUNT>0) THEN
1951
1952 l_eam_wo_comp_rec := l_eam_wo_comp_tbl(l_eam_wo_comp_tbl.FIRST);
1953
1954 /* Failure Analysis Project Start */
1955
1956 l_eam_failure_entry_record.failure_id := p_failure_id;
1957 l_eam_failure_entry_record.failure_date := p_failure_date;
1958
1959 l_eam_failure_codes_tbl(1).failure_id := p_failure_id;
1960 l_eam_failure_codes_tbl(1).failure_entry_id := p_failure_entry_id;
1961 l_eam_failure_codes_tbl(1).failure_code := p_failure_code;
1962 l_eam_failure_codes_tbl(1).cause_code := p_cause_code;
1963 l_eam_failure_codes_tbl(1).resolution_code := p_resolution_code;
1964 l_eam_failure_codes_tbl(1).comments := p_failure_comments;
1965
1966
1967 SELECT
1968 maintenance_object_type
1969 ,maintenance_object_id
1970 INTO
1971 l_maintenance_object_type
1972 ,l_maintenance_object_id
1973 FROM WIP_DISCRETE_JOBS
1974 WHERE wip_entity_id = l_eam_wo_comp_rec.wip_entity_id;
1975
1976 --only if asset number/rebuild serial number exists for work order
1977 IF(l_maintenance_object_type =3) THEN
1978 BEGIN
1979
1980 --if workorder dept. is null,de fault it from asset's owning dept
1981
1982 SELECT OWNING_DEPARTMENT_ID
1983 INTO l_fail_dept_id
1984 FROM eam_org_maint_defaults
1985 WHERE object_id =l_maintenance_object_id
1986 AND object_type = 50
1987 AND organization_id =l_eam_wo_comp_rec.organization_id;
1988
1989 SELECT area_id
1990 INTO l_eam_location_id
1991 FROM eam_org_maint_defaults
1992 WHERE object_id = l_maintenance_object_id
1993 AND object_type = 50
1994 AND organization_id = l_eam_wo_comp_rec.organization_id;
1995
1996 EXCEPTION
1997 WHEN NO_DATA_FOUND THEN
1998 NULL;
1999 END;
2000 END IF; --end of check for mainteannce_object_type =3
2001
2002
2003 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
2004 l_eam_failure_entry_record.source_type := 1;
2005 l_eam_failure_entry_record.source_id := l_eam_wo_comp_rec.wip_entity_id;
2006 l_eam_failure_entry_record.object_type := l_maintenance_object_type;
2007 l_eam_failure_entry_record.object_id := l_maintenance_object_id;
2008 l_eam_failure_entry_record.maint_organization_id := l_eam_wo_comp_rec.organization_id;
2009 l_eam_failure_entry_record.current_organization_id := l_eam_wo_comp_rec.organization_id;
2010 l_eam_failure_entry_record.department_id := l_fail_dept_id;
2011 l_eam_failure_entry_record.area_id := l_eam_location_id;
2012
2013 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
2014
2015 IF (l_eam_failure_entry_record.failure_date IS NULL) THEN
2016 l_eam_failure_entry_record.failure_date := FND_API.G_MISS_DATE;
2017 END IF;
2018
2019 IF (l_eam_failure_codes_tbl(1).failure_code IS NULL) THEN
2020 l_eam_failure_codes_tbl(1).failure_code := FND_API.G_MISS_CHAR;
2021 END IF;
2022
2023 IF (l_eam_failure_codes_tbl(1).cause_code IS NULL) THEN
2024 l_eam_failure_codes_tbl(1).cause_code := FND_API.G_MISS_CHAR;
2025 END IF;
2026
2027 IF (l_eam_failure_codes_tbl(1).resolution_code IS NULL) THEN
2028 l_eam_failure_codes_tbl(1).resolution_code := FND_API.G_MISS_CHAR;
2029 END IF;
2030
2031 IF (l_eam_failure_codes_tbl(1).comments IS NULL) THEN
2032 l_eam_failure_codes_tbl(1).comments := FND_API.G_MISS_CHAR;
2033 END IF;
2034
2035 IF(l_eam_failure_entry_record.failure_id IS NOT NULL ) THEN
2036 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
2037 ELSE
2038 IF(l_eam_failure_entry_record.failure_date = FND_API.G_MISS_DATE) THEN
2039 l_eam_failure_entry_record.transaction_type :=null;
2040 l_eam_failure_entry_record.failure_date :=null;
2041 ELSE
2042 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE ;
2043 END IF;
2044 END IF;
2045
2046 IF(l_eam_failure_codes_tbl(1).failure_entry_id IS NOT NULL) THEN
2047 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
2048 l_eam_wo_comp_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
2049
2050 ELSE
2051 IF( NOT( (l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR)
2052 AND (l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR)
2053 AND (l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR)
2054 AND (l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR)
2055 )
2056 ) THEN
2057 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
2058
2059 IF(l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR) THEN
2060 l_eam_failure_codes_tbl(1).failure_code := null;
2061 END IF;
2062
2063 IF(l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR) THEN
2064 l_eam_failure_codes_tbl(1).cause_code := NULL;
2065 END IF;
2066
2067 IF(l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR) then
2068 l_eam_failure_codes_tbl(1).resolution_code := null;
2069 END IF;
2070
2071 IF(l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR) then
2072 l_eam_failure_codes_tbl(1).comments := null;
2073 END IF;
2074 l_eam_wo_comp_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
2075
2076 ELSE
2077 l_eam_failure_codes_tbl.delete;
2078 l_eam_wo_comp_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
2079
2080 END IF;
2081 END IF;
2082
2083 l_eam_wo_comp_rec.eam_failure_entry_record := l_eam_failure_entry_record;
2084 l_eam_wo_comp_tbl(l_eam_wo_comp_tbl.FIRST) := l_eam_wo_comp_rec;
2085
2086 END IF;
2087
2088 /* Failure Analysis Project End */
2089
2090 --Set the activity_id to Fnd_Api.G_Miss_Num if we want to delete the activity
2091 l_eam_wo_tbl := p_eam_wo_tbl;
2092 IF(l_eam_wo_tbl IS NOT NULL AND l_eam_wo_tbl.COUNT>0) THEN
2093
2094 l_eam_wo_rec := l_eam_wo_tbl(l_eam_wo_tbl.FIRST);
2095
2096 --set FND_API.G_MISS_xx if any columns are nulled out
2097 l_wo_exists := TRUE; --work order already exists...if no data found exception is thrown then l_wo_exists will be set to FALSE
2098
2099 BEGIN
2100 l_wip_entity_id :=l_eam_wo_rec.wip_entity_id;
2101
2102 SELECT asset_number
2103 ,asset_group_id
2104 ,rebuild_serial_number
2105 ,rebuild_item_id
2106 ,primary_item_id
2107 ,description
2108 ,priority
2109 ,work_order_type
2110 ,shutdown_type
2111 ,activity_type
2112 ,activity_cause
2113 ,activity_source
2114 ,attribute_category
2115 ,attribute1
2116 ,attribute2
2117 ,attribute3
2118 ,attribute4
2119 ,attribute5
2120 ,attribute6
2121 ,attribute7
2122 ,attribute8
2123 ,attribute9
2124 ,attribute10
2125 ,attribute11
2126 ,attribute12
2127 ,attribute13
2128 ,attribute14
2129 ,attribute15
2130 /* Added for bug#6053425 Start */
2131 ,project_id
2132 ,task_id
2133 /* Added for bug#6053425 End */
2134 INTO l_asset_number
2135 ,l_asset_group_id
2136 ,l_rebuild_serial_number
2137 ,l_rebuild_item_id
2138 ,l_prev_activity_id
2139 ,l_prev_description
2140 ,l_prev_priority
2141 ,l_prev_work_order_type
2142 ,l_prev_shutdown_type
2143 ,l_prev_activity_type
2144 ,l_prev_activity_cause
2145 ,l_prev_activity_source
2146 ,l_prev_attribute_category
2147 ,l_prev_attribute1
2148 ,l_prev_attribute2
2149 ,l_prev_attribute3
2150 ,l_prev_attribute4
2151 ,l_prev_attribute5
2152 ,l_prev_attribute6
2153 ,l_prev_attribute7
2154 ,l_prev_attribute8
2155 ,l_prev_attribute9
2156 ,l_prev_attribute10
2157 ,l_prev_attribute11
2158 ,l_prev_attribute12
2159 ,l_prev_attribute13
2160 ,l_prev_attribute14
2161 ,l_prev_attribute15
2162 /* Code Added for bug#6053425 Start */
2163 ,l_prev_project_id
2164 ,l_prev_task_id
2165 /* Code Added for bug#6053425 End */
2166 FROM WIP_DISCRETE_JOBS
2167 where wip_entity_id = l_wip_entity_id;
2168
2169 IF(l_asset_number is NOT NULL and l_eam_wo_rec.asset_number IS NULL) THEN
2170 l_eam_wo_rec.asset_number := FND_API.G_MISS_CHAR;
2171 END IF;
2172
2173 IF(l_rebuild_serial_number is NOT NULL and l_eam_wo_rec.rebuild_serial_number IS NULL) THEN
2174 l_eam_wo_rec.rebuild_serial_number := FND_API.G_MISS_CHAR;
2175 END IF;
2176
2177 IF(l_asset_group_id is NOT NULL and l_eam_wo_rec.asset_group_id IS NULL) THEN
2178 l_eam_wo_rec.asset_group_id := FND_API.G_MISS_NUM;
2179 END IF;
2180
2181 IF(l_rebuild_item_id is NOT NULL and l_eam_wo_rec.rebuild_item_id IS NULL) THEN
2182 l_eam_wo_rec.rebuild_item_id:= FND_API.G_MISS_NUM;
2183 END IF;
2184
2185 IF l_prev_activity_id is not null and l_eam_wo_rec.asset_activity_id is null THEN
2186 l_eam_wo_rec.asset_activity_id := FND_API.G_MISS_NUM;
2187 END IF;
2188
2189 IF l_prev_description is not null and l_eam_wo_rec.description is null THEN
2190 l_eam_wo_rec.description := FND_API.G_MISS_CHAR;
2191 END IF;
2192
2193 IF l_prev_activity_type is not null and l_eam_wo_rec.activity_type is null THEN
2194 l_eam_wo_rec.activity_type := FND_API.G_MISS_CHAR;
2195 END IF;
2196
2197 IF l_prev_activity_cause is not null and l_eam_wo_rec.activity_cause is null THEN
2198 l_eam_wo_rec.activity_cause := FND_API.G_MISS_CHAR;
2199 END IF;
2200
2201 IF l_prev_activity_source is not null and l_eam_wo_rec.activity_source is null THEN
2202 l_eam_wo_rec.activity_source := FND_API.G_MISS_CHAR;
2203 END IF;
2204
2205 IF l_prev_shutdown_type is not null and l_eam_wo_rec.shutdown_type is null THEN
2206 l_eam_wo_rec.shutdown_type := FND_API.G_MISS_CHAR;
2207 END IF;
2208
2209 IF l_prev_priority is not null and l_eam_wo_rec.priority is null THEN
2210 l_eam_wo_rec.priority := FND_API.G_MISS_NUM;
2211 END IF;
2212
2213 IF l_prev_work_order_type is not null and l_eam_wo_rec.work_order_type is null THEN
2214 l_eam_wo_rec.work_order_type := FND_API.G_MISS_CHAR;
2215 END IF;
2216
2217 IF l_prev_attribute_category is not null and l_eam_wo_rec.attribute_category is null THEN
2218 l_eam_wo_rec.attribute_category := FND_API.G_MISS_CHAR;
2219 END IF;
2220
2221 IF l_prev_attribute1 is not null and l_eam_wo_rec.attribute1 is null THEN
2222 l_eam_wo_rec.attribute1 := FND_API.G_MISS_CHAR;
2223 END IF;
2224
2225 IF l_prev_attribute2 is not null and l_eam_wo_rec.attribute2 is null THEN
2226 l_eam_wo_rec.attribute2 := FND_API.G_MISS_CHAR;
2227 END IF;
2228
2229 IF l_prev_attribute3 is not null and l_eam_wo_rec.attribute3 is null THEN
2230 l_eam_wo_rec.attribute3 := FND_API.G_MISS_CHAR;
2231 END IF;
2232
2233 IF l_prev_attribute4 is not null and l_eam_wo_rec.attribute4 is null THEN
2234 l_eam_wo_rec.attribute4 := FND_API.G_MISS_CHAR;
2235 END IF;
2236
2237 IF l_prev_attribute5 is not null and l_eam_wo_rec.attribute5 is null THEN
2238 l_eam_wo_rec.attribute5 := FND_API.G_MISS_CHAR;
2239 END IF;
2240
2241 IF l_prev_attribute6 is not null and l_eam_wo_rec.attribute6 is null THEN
2242 l_eam_wo_rec.attribute6 := FND_API.G_MISS_CHAR;
2243 END IF;
2244
2245 IF l_prev_attribute7 is not null and l_eam_wo_rec.attribute7 is null THEN
2246 l_eam_wo_rec.attribute7 := FND_API.G_MISS_CHAR;
2247 END IF;
2248
2249 IF l_prev_attribute8 is not null and l_eam_wo_rec.attribute8 is null THEN
2250 l_eam_wo_rec.attribute8 := FND_API.G_MISS_CHAR;
2251 END IF;
2252
2253 IF l_prev_attribute9 is not null and l_eam_wo_rec.attribute9 is null THEN
2254 l_eam_wo_rec.attribute9 := FND_API.G_MISS_CHAR;
2255 END IF;
2256
2257 IF l_prev_attribute10 is not null and l_eam_wo_rec.attribute10 is null THEN
2258 l_eam_wo_rec.attribute10 := FND_API.G_MISS_CHAR;
2259 END IF;
2260
2261 IF l_prev_attribute11 is not null and l_eam_wo_rec.attribute11 is null THEN
2262 l_eam_wo_rec.attribute11 := FND_API.G_MISS_CHAR;
2263 END IF;
2264
2265 IF l_prev_attribute12 is not null and l_eam_wo_rec.attribute12 is null THEN
2266 l_eam_wo_rec.attribute12 := FND_API.G_MISS_CHAR;
2267 END IF;
2268
2269 IF l_prev_attribute13 is not null and l_eam_wo_rec.attribute13 is null THEN
2270 l_eam_wo_rec.attribute13 := FND_API.G_MISS_CHAR;
2271 END IF;
2272
2273 IF l_prev_attribute14 is not null and l_eam_wo_rec.attribute14 is null THEN
2274 l_eam_wo_rec.attribute14 := FND_API.G_MISS_CHAR;
2275 END IF;
2276
2277 IF l_prev_attribute15 is not null and l_eam_wo_rec.attribute15 is null THEN
2278 l_eam_wo_rec.attribute15 := FND_API.G_MISS_CHAR;
2279 END IF;
2280
2281 /* Added for bug#6053425 Start */
2282
2283 IF l_prev_project_id is not null AND l_eam_wo_rec.project_id is null THEN
2284 l_eam_wo_rec.project_id := FND_API.G_MISS_NUM;
2285 END IF;
2286
2287 IF l_prev_task_id is not null AND l_eam_wo_rec.task_id is null THEN
2288 l_eam_wo_rec.task_id := FND_API.G_MISS_NUM;
2289 END IF;
2290
2291 /* Added for bug#6053425 End */
2292
2293 EXCEPTION
2294 WHEN NO_DATA_FOUND THEN
2295 NULL;
2296 END;
2297
2298
2299 --Validate descriptive flexfield for workorder
2300 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
2301 p_app_short_name => 'WIP',
2302 p_desc_flex_name => 'WIP_DISCRETE_JOBS',
2303 p_attribute_category => l_eam_wo_rec.attribute_category,
2304 p_attribute1 => l_eam_wo_rec.attribute1,
2305 p_attribute2 => l_eam_wo_rec.attribute2,
2306 p_attribute3 => l_eam_wo_rec.attribute3,
2307 p_attribute4 => l_eam_wo_rec.attribute4,
2308 p_attribute5 => l_eam_wo_rec.attribute5,
2309 p_attribute6 => l_eam_wo_rec.attribute6,
2310 p_attribute7 => l_eam_wo_rec.attribute7,
2311 p_attribute8 => l_eam_wo_rec.attribute8,
2312 p_attribute9 => l_eam_wo_rec.attribute9,
2313 p_attribute10 => l_eam_wo_rec.attribute10,
2314 p_attribute11 => l_eam_wo_rec.attribute11,
2315 p_attribute12 => l_eam_wo_rec.attribute12,
2316 p_attribute13 => l_eam_wo_rec.attribute13,
2317 p_attribute14 => l_eam_wo_rec.attribute14,
2318 p_attribute15 => l_eam_wo_rec.attribute15,
2319 x_error_segments => l_error_segments,
2320 x_error_message => l_error_message
2321 );
2322
2323 /* Commented for bug 8567361
2324 IF l_validate <> TRUE THEN
2325 fnd_message.set_name
2326 ( application => 'EAM'
2327 , name => 'EAM_WO_FLEX_ERROR'
2328 );
2329
2330 fnd_message.set_token(token => 'MESG',
2331 value => l_error_message,
2332 translate => FALSE);
2333
2334
2335 fnd_msg_pub.add;
2336
2337 x_wip_entity_id := l_eam_wo_rec.wip_entity_id;
2338 x_return_status := 'E';
2339 x_msg_count := 1;
2340 RETURN;
2341 END IF; */
2342
2343
2344 l_eam_wo_tbl(l_eam_wo_tbl.FIRST) := l_eam_wo_rec;
2345 END IF;
2346
2347 -- Operations
2348 l_eam_op_tbl := p_eam_op_tbl;
2349 IF(l_eam_op_tbl IS NOT NULL AND l_eam_op_tbl.COUNT>0) THEN
2350
2351 FOR i in 1..l_eam_op_tbl.COUNT
2352 LOOP
2353 l_eam_op_rec := l_eam_op_tbl(i);
2354
2355
2356 BEGIN
2357 l_wip_entity_id :=l_eam_op_rec.wip_entity_id;
2358
2359 SELECT
2360 description
2361 , shutdown_type
2362 , long_description
2363 , attribute_category
2364 , attribute1
2365 , attribute2
2366 , attribute3
2367 , attribute4
2368 , attribute5
2369 , attribute6
2370 , attribute7
2371 , attribute8
2372 , attribute9
2373 , attribute10
2374 , attribute11
2375 , attribute12
2376 , attribute13
2377 , attribute14
2378 , attribute15
2379 INTO
2380 l_prev_op_description
2381 ,l_prev_op_shutdown_type
2382 ,l_prev_op_long_description
2383 ,l_prev_op_attribute_category
2384 ,l_prev_op_attribute1
2385 ,l_prev_op_attribute2
2386 ,l_prev_op_attribute3
2387 ,l_prev_op_attribute4
2388 ,l_prev_op_attribute5
2389 ,l_prev_op_attribute6
2390 ,l_prev_op_attribute7
2391 ,l_prev_op_attribute8
2392 ,l_prev_op_attribute9
2393 ,l_prev_op_attribute10
2394 ,l_prev_op_attribute11
2395 ,l_prev_op_attribute12
2396 ,l_prev_op_attribute13
2397 ,l_prev_op_attribute14
2398 ,l_prev_op_attribute15
2399 FROM wip_operations wo
2400 WHERE wo.wip_entity_id = l_eam_op_rec.wip_entity_id
2401 AND wo.organization_id = l_eam_op_rec.organization_id
2402 AND wo.operation_seq_num = l_eam_op_rec.operation_seq_num;
2403
2404
2405 IF l_prev_op_description is not null and l_eam_op_rec.description is null THEN
2406 l_eam_op_rec.description := FND_API.G_MISS_CHAR;
2407 END IF;
2408
2409 IF l_prev_op_shutdown_type is not null and l_eam_op_rec.shutdown_type is null THEN
2410 l_eam_op_rec.shutdown_type := FND_API.G_MISS_CHAR;
2411 END IF;
2412
2413 IF l_prev_op_long_description is not null and l_eam_op_rec.long_description is null THEN
2414 l_eam_op_rec.long_description := FND_API.G_MISS_CHAR;
2415 END IF;
2416
2417 IF l_prev_op_attribute_category is not null and l_eam_op_rec.attribute_category is null THEN
2418 l_eam_op_rec.attribute_category := FND_API.G_MISS_CHAR;
2419 END IF;
2420
2421 IF l_prev_op_attribute1 is not null and l_eam_op_rec.attribute1 is null THEN
2422 l_eam_op_rec.attribute1 := FND_API.G_MISS_CHAR;
2423 END IF;
2424
2425 IF l_prev_op_attribute2 is not null and l_eam_op_rec.attribute2 is null THEN
2426 l_eam_op_rec.attribute2 := FND_API.G_MISS_CHAR;
2427 END IF;
2428
2429 IF l_prev_op_attribute3 is not null and l_eam_op_rec.attribute3 is null THEN
2430 l_eam_op_rec.attribute3 := FND_API.G_MISS_CHAR;
2431 END IF;
2432
2433 IF l_prev_op_attribute4 is not null and l_eam_op_rec.attribute4 is null THEN
2434 l_eam_op_rec.attribute4 := FND_API.G_MISS_CHAR;
2435 END IF;
2436
2437 IF l_prev_op_attribute5 is not null and l_eam_op_rec.attribute5 is null THEN
2438 l_eam_op_rec.attribute5 := FND_API.G_MISS_CHAR;
2439 END IF;
2440
2441 IF l_prev_op_attribute6 is not null and l_eam_op_rec.attribute6 is null THEN
2442 l_eam_op_rec.attribute6 := FND_API.G_MISS_CHAR;
2443 END IF;
2444
2445 IF l_prev_op_attribute7 is not null and l_eam_op_rec.attribute7 is null THEN
2446 l_eam_op_rec.attribute7 := FND_API.G_MISS_CHAR;
2447 END IF;
2448
2449 IF l_prev_op_attribute8 is not null and l_eam_op_rec.attribute8 is null THEN
2450 l_eam_op_rec.attribute8 := FND_API.G_MISS_CHAR;
2451 END IF;
2452
2453 IF l_prev_op_attribute9 is not null and l_eam_op_rec.attribute9 is null THEN
2454 l_eam_op_rec.attribute9 := FND_API.G_MISS_CHAR;
2455 END IF;
2456
2457 IF l_prev_op_attribute10 is not null and l_eam_op_rec.attribute10 is null THEN
2458 l_eam_op_rec.attribute10 := FND_API.G_MISS_CHAR;
2459 END IF;
2460
2461 IF l_prev_op_attribute11 is not null and l_eam_op_rec.attribute11 is null THEN
2462 l_eam_op_rec.attribute11 := FND_API.G_MISS_CHAR;
2463 END IF;
2464
2465 IF l_prev_op_attribute12 is not null and l_eam_op_rec.attribute12 is null THEN
2466 l_eam_op_rec.attribute12 := FND_API.G_MISS_CHAR;
2467 END IF;
2468
2469 IF l_prev_op_attribute13 is not null and l_eam_op_rec.attribute13 is null THEN
2470 l_eam_op_rec.attribute13 := FND_API.G_MISS_CHAR;
2471 END IF;
2472
2473 IF l_prev_op_attribute14 is not null and l_eam_op_rec.attribute14 is null THEN
2474 l_eam_op_rec.attribute14 := FND_API.G_MISS_CHAR;
2475 END IF;
2476
2477 IF l_prev_op_attribute15 is not null and l_eam_op_rec.attribute15 is null THEN
2478 l_eam_op_rec.attribute15 := FND_API.G_MISS_CHAR;
2479 END IF;
2480
2481
2482 EXCEPTION
2483 WHEN NO_DATA_FOUND THEN
2484 NULL;
2485 END;
2486
2487
2488 --Validate descriptive flexfield for Operation
2489 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
2490 p_app_short_name => 'BOM',
2491 p_desc_flex_name => 'OPERATIONS',
2492 p_attribute_category => l_eam_op_rec.attribute_category,
2493 p_attribute1 => l_eam_op_rec.attribute1,
2494 p_attribute2 => l_eam_op_rec.attribute2,
2495 p_attribute3 => l_eam_op_rec.attribute3,
2496 p_attribute4 => l_eam_op_rec.attribute4,
2497 p_attribute5 => l_eam_op_rec.attribute5,
2498 p_attribute6 => l_eam_op_rec.attribute6,
2499 p_attribute7 => l_eam_op_rec.attribute7,
2500 p_attribute8 => l_eam_op_rec.attribute8,
2501 p_attribute9 => l_eam_op_rec.attribute9,
2502 p_attribute10 => l_eam_op_rec.attribute10,
2503 p_attribute11 => l_eam_op_rec.attribute11,
2504 p_attribute12 => l_eam_op_rec.attribute12,
2505 p_attribute13 => l_eam_op_rec.attribute13,
2506 p_attribute14 => l_eam_op_rec.attribute14,
2507 p_attribute15 => l_eam_op_rec.attribute15,
2508 x_error_segments => l_error_segments,
2509 x_error_message => l_error_message
2510 );
2511
2512 /* Commented for bug 8567361
2513 IF l_validate <> TRUE THEN
2514 fnd_message.set_name
2515 ( application => 'EAM'
2516 , name => 'EAM_OP_FLEX_ERROR'
2517 );
2518
2519 fnd_message.set_token(token => 'MESG',
2520 value => l_error_message,
2521 translate => FALSE);
2522
2523
2524 fnd_msg_pub.add;
2525
2526 x_wip_entity_id := l_eam_op_rec.wip_entity_id;
2527 x_return_status := 'E';
2528 x_msg_count := 1;
2529 RETURN;
2530 END IF; */
2531
2532
2533 l_eam_op_tbl(i) := l_eam_op_rec;
2534 END LOOP;
2535 END IF;
2536
2537
2538 --Changes for Bug 8969942
2539
2540 l_eam_mat_req_tbl := p_eam_mat_req_tbl;
2541 IF(l_eam_mat_req_tbl IS NOT NULL AND l_eam_mat_req_tbl.COUNT>0) THEN
2542 l_eam_mat_req_rec := l_eam_mat_req_tbl(l_eam_mat_req_tbl.FIRST);
2543
2544 BEGIN
2545 l_wip_entity_id := l_eam_mat_req_rec.wip_entity_id;
2546 l_organization_id := l_eam_mat_req_rec.ORGANIZATION_ID;
2547 l_Operation_Seq_Num := l_eam_mat_req_rec.OPERATION_SEQ_NUM;
2548 l_inventory_item_id := l_eam_mat_req_rec.INVENTORY_ITEM_ID;
2549
2550 SELECT attribute_category
2551 ,attribute1
2552 ,attribute2
2553 ,attribute3
2554 ,attribute4
2555 ,attribute5
2556 ,attribute6
2557 ,attribute7
2558 ,attribute8
2559 ,attribute9
2560 ,attribute10
2561 ,attribute11
2562 ,attribute12
2563 ,attribute13
2564 ,attribute14
2565 ,attribute15
2566 INTO l_prev_mat_attribute_category
2567 ,l_prev_mat_attribute1
2568 ,l_prev_mat_attribute2
2569 ,l_prev_mat_attribute3
2570 ,l_prev_mat_attribute4
2571 ,l_prev_mat_attribute5
2572 ,l_prev_mat_attribute6
2573 ,l_prev_mat_attribute7
2574 ,l_prev_mat_attribute8
2575 ,l_prev_mat_attribute9
2576 ,l_prev_mat_attribute10
2577 ,l_prev_mat_attribute11
2578 ,l_prev_mat_attribute12
2579 ,l_prev_mat_attribute13
2580 ,l_prev_mat_attribute14
2581 ,l_prev_mat_attribute15
2582 from WIP_REQUIREMENT_OPERATIONS
2583 where wip_entity_id =l_wip_Entity_Id
2584 and organization_id = l_organization_id
2585 and operation_seq_num= l_Operation_Seq_Num
2586 and INVENTORY_ITEM_ID = l_inventory_item_id;
2587 EXCEPTION
2588 WHEN NO_DATA_FOUND THEN
2589 null;
2590 When Others then
2591 null;
2592 END;
2593
2594 IF l_prev_mat_attribute_category is not null and l_eam_mat_req_rec.attribute_category is null THEN
2595 l_eam_mat_req_rec.attribute_category := FND_API.G_MISS_CHAR;
2596 END IF;
2597
2598 IF l_prev_mat_attribute1 is not null and l_eam_mat_req_rec.attribute1 is null THEN
2599 l_eam_mat_req_rec.attribute1 := FND_API.G_MISS_CHAR;
2600 END IF;
2601
2602 IF l_prev_mat_attribute2 is not null and l_eam_mat_req_rec.attribute2 is null THEN
2603 l_eam_mat_req_rec.attribute2 := FND_API.G_MISS_CHAR;
2604 END IF;
2605
2606 IF l_prev_mat_attribute3 is not null and l_eam_mat_req_rec.attribute3 is null THEN
2607 l_eam_mat_req_rec.attribute3 := FND_API.G_MISS_CHAR;
2608 END IF;
2609
2610 IF l_prev_mat_attribute4 is not null and l_eam_mat_req_rec.attribute4 is null THEN
2611 l_eam_mat_req_rec.attribute4 := FND_API.G_MISS_CHAR;
2612 END IF;
2613
2614 IF l_prev_mat_attribute5 is not null and l_eam_mat_req_rec.attribute5 is null THEN
2615 l_eam_mat_req_rec.attribute5 := FND_API.G_MISS_CHAR;
2616 END IF;
2617
2618 IF l_prev_mat_attribute6 is not null and l_eam_mat_req_rec.attribute6 is null THEN
2619 l_eam_mat_req_rec.attribute6 := FND_API.G_MISS_CHAR;
2620 END IF;
2621
2622 IF l_prev_mat_attribute7 is not null and l_eam_mat_req_rec.attribute7 is null THEN
2623 l_eam_mat_req_rec.attribute7 := FND_API.G_MISS_CHAR;
2624 END IF;
2625
2626 IF l_prev_mat_attribute8 is not null and l_eam_mat_req_rec.attribute8 is null THEN
2627 l_eam_mat_req_rec.attribute8 := FND_API.G_MISS_CHAR;
2628 END IF;
2629
2630 IF l_prev_mat_attribute9 is not null and l_eam_mat_req_rec.attribute9 is null THEN
2631 l_eam_mat_req_rec.attribute9 := FND_API.G_MISS_CHAR;
2632 END IF;
2633
2634 IF l_prev_mat_attribute10 is not null and l_eam_mat_req_rec.attribute10 is null THEN
2635 l_eam_mat_req_rec.attribute10 := FND_API.G_MISS_CHAR;
2636 END IF;
2637
2638 IF l_prev_mat_attribute11 is not null and l_eam_mat_req_rec.attribute11 is null THEN
2639 l_eam_mat_req_rec.attribute11 := FND_API.G_MISS_CHAR;
2640 END IF;
2641
2642 IF l_prev_mat_attribute12 is not null and l_eam_mat_req_rec.attribute12 is null THEN
2643 l_eam_mat_req_rec.attribute12 := FND_API.G_MISS_CHAR;
2644 END IF;
2645
2646 IF l_prev_mat_attribute13 is not null and l_eam_mat_req_rec.attribute13 is null THEN
2647 l_eam_mat_req_rec.attribute13 := FND_API.G_MISS_CHAR;
2648 END IF;
2649
2650 IF l_prev_mat_attribute14 is not null and l_eam_mat_req_rec.attribute14 is null THEN
2651 l_eam_mat_req_rec.attribute14 := FND_API.G_MISS_CHAR;
2652 END IF;
2653
2654 IF l_prev_mat_attribute15 is not null and l_eam_mat_req_rec.attribute15 is null THEN
2655 l_eam_mat_req_rec.attribute15 := FND_API.G_MISS_CHAR;
2656 END IF;
2657
2658 --Validate descriptive flexfield for materials - "Component Information"
2659 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
2660 p_app_short_name => 'BOM',
2661 p_desc_flex_name => 'BOM_INVENTORY_COMPONENTS',
2662 p_attribute_category => l_eam_mat_req_rec.attribute_category,
2663 p_attribute1 => l_eam_mat_req_rec.attribute1,
2664 p_attribute2 => l_eam_mat_req_rec.attribute2,
2665 p_attribute3 => l_eam_mat_req_rec.attribute3,
2666 p_attribute4 => l_eam_mat_req_rec.attribute4,
2667 p_attribute5 => l_eam_mat_req_rec.attribute5,
2668 p_attribute6 => l_eam_mat_req_rec.attribute6,
2669 p_attribute7 => l_eam_mat_req_rec.attribute7,
2670 p_attribute8 => l_eam_mat_req_rec.attribute8,
2671 p_attribute9 => l_eam_mat_req_rec.attribute9,
2672 p_attribute10 => l_eam_mat_req_rec.attribute10,
2673 p_attribute11 => l_eam_mat_req_rec.attribute11,
2674 p_attribute12 => l_eam_mat_req_rec.attribute12,
2675 p_attribute13 => l_eam_mat_req_rec.attribute13,
2676 p_attribute14 => l_eam_mat_req_rec.attribute14,
2677 p_attribute15 => l_eam_mat_req_rec.attribute15,
2678 x_error_segments => l_error_segments,
2679 x_error_message => l_error_message
2680 );
2681
2682
2683
2684 l_eam_mat_req_tbl(l_eam_mat_req_tbl.FIRST) := l_eam_mat_req_rec;
2685 END IF;
2686
2687 l_eam_direct_items_tbl := p_eam_direct_items_tbl;
2688 IF(l_eam_direct_items_tbl IS NOT NULL AND l_eam_direct_items_tbl.COUNT>0) THEN
2689 l_eam_direct_items_rec := l_eam_direct_items_tbl(l_eam_direct_items_tbl.FIRST);
2690
2691 BEGIN
2692 l_wip_entity_id := l_eam_direct_items_rec.wip_entity_id;
2693 l_organization_id := l_eam_direct_items_rec.ORGANIZATION_ID;
2694 l_Operation_Seq_Num := l_eam_direct_items_rec.OPERATION_SEQ_NUM;
2695 l_dir_item_seq_id := l_eam_direct_items_rec.Direct_Item_Sequence_Id;
2696
2697 SELECT attribute_category
2698 ,attribute1
2699 ,attribute2
2700 ,attribute3
2701 ,attribute4
2702 ,attribute5
2703 ,attribute6
2704 ,attribute7
2705 ,attribute8
2706 ,attribute9
2707 ,attribute10
2708 ,attribute11
2709 ,attribute12
2710 ,attribute13
2711 ,attribute14
2712 ,attribute15
2713 INTO l_prev_dir_attribute_category
2714 ,l_prev_dir_attribute1
2715 ,l_prev_dir_attribute2
2716 ,l_prev_dir_attribute3
2717 ,l_prev_dir_attribute4
2718 ,l_prev_dir_attribute5
2719 ,l_prev_dir_attribute6
2720 ,l_prev_dir_attribute7
2721 ,l_prev_dir_attribute8
2722 ,l_prev_dir_attribute9
2723 ,l_prev_dir_attribute10
2724 ,l_prev_dir_attribute11
2725 ,l_prev_dir_attribute12
2726 ,l_prev_dir_attribute13
2727 ,l_prev_dir_attribute14
2728 ,l_prev_dir_attribute15
2729 from wip_eam_direct_items
2730 where wip_entity_id =l_wip_Entity_Id
2731 and organization_id = l_organization_id
2732 and operation_seq_num= l_Operation_Seq_Num ;
2733
2734 EXCEPTION
2735 WHEN NO_DATA_FOUND THEN
2736 null;
2737 When Others then
2738 null;
2739 END;
2740
2741 IF l_prev_dir_attribute_category is not null and l_eam_direct_items_rec.attribute_category is null THEN
2742 l_eam_direct_items_rec.attribute_category := FND_API.G_MISS_CHAR;
2743 END IF;
2744
2745 IF l_prev_dir_attribute1 is not null and l_eam_direct_items_rec.attribute1 is null THEN
2746 l_eam_direct_items_rec.attribute1 := FND_API.G_MISS_CHAR;
2747 END IF;
2748
2749 IF l_prev_dir_attribute2 is not null and l_eam_direct_items_rec.attribute2 is null THEN
2750 l_eam_direct_items_rec.attribute2 := FND_API.G_MISS_CHAR;
2751 END IF;
2752
2753 IF l_prev_dir_attribute3 is not null and l_eam_direct_items_rec.attribute3 is null THEN
2754 l_eam_direct_items_rec.attribute3 := FND_API.G_MISS_CHAR;
2755 END IF;
2756
2757 IF l_prev_dir_attribute4 is not null and l_eam_direct_items_rec.attribute4 is null THEN
2758 l_eam_direct_items_rec.attribute4 := FND_API.G_MISS_CHAR;
2759 END IF;
2760
2761 IF l_prev_dir_attribute5 is not null and l_eam_direct_items_rec.attribute5 is null THEN
2762 l_eam_direct_items_rec.attribute5 := FND_API.G_MISS_CHAR;
2763 END IF;
2764
2765 IF l_prev_dir_attribute6 is not null and l_eam_direct_items_rec.attribute6 is null THEN
2766 l_eam_direct_items_rec.attribute6 := FND_API.G_MISS_CHAR;
2767 END IF;
2768
2769 IF l_prev_dir_attribute7 is not null and l_eam_direct_items_rec.attribute7 is null THEN
2770 l_eam_direct_items_rec.attribute7 := FND_API.G_MISS_CHAR;
2771 END IF;
2772
2773 IF l_prev_dir_attribute8 is not null and l_eam_direct_items_rec.attribute8 is null THEN
2774 l_eam_direct_items_rec.attribute8 := FND_API.G_MISS_CHAR;
2775 END IF;
2776
2777 IF l_prev_dir_attribute9 is not null and l_eam_direct_items_rec.attribute9 is null THEN
2778 l_eam_direct_items_rec.attribute9 := FND_API.G_MISS_CHAR;
2779 END IF;
2780
2781 IF l_prev_dir_attribute10 is not null and l_eam_direct_items_rec.attribute10 is null THEN
2782 l_eam_direct_items_rec.attribute10 := FND_API.G_MISS_CHAR;
2783 END IF;
2784
2785 IF l_prev_dir_attribute11 is not null and l_eam_direct_items_rec.attribute11 is null THEN
2786 l_eam_direct_items_rec.attribute11 := FND_API.G_MISS_CHAR;
2787 END IF;
2788
2789 IF l_prev_dir_attribute12 is not null and l_eam_direct_items_rec.attribute12 is null THEN
2790 l_eam_direct_items_rec.attribute12 := FND_API.G_MISS_CHAR;
2791 END IF;
2792
2793 IF l_prev_dir_attribute13 is not null and l_eam_direct_items_rec.attribute13 is null THEN
2794 l_eam_direct_items_rec.attribute13 := FND_API.G_MISS_CHAR;
2795 END IF;
2796
2797 IF l_prev_dir_attribute14 is not null and l_eam_direct_items_rec.attribute14 is null THEN
2798 l_eam_direct_items_rec.attribute14 := FND_API.G_MISS_CHAR;
2799 END IF;
2800
2801 IF l_prev_dir_attribute15 is not null and l_eam_direct_items_rec.attribute15 is null THEN
2802 l_eam_direct_items_rec.attribute15 := FND_API.G_MISS_CHAR;
2803 END IF;
2804
2805 --Validate descriptive flexfield for workorder
2806 l_validate := EAM_COMMON_UTILITIES_PVT.validate_desc_flex_field(
2807 p_app_short_name => 'EAM',
2808 p_desc_flex_name => 'EAM_DESC_DIR_ITEM',
2809 p_attribute_category => l_eam_direct_items_rec.attribute_category,
2810 p_attribute1 => l_eam_direct_items_rec.attribute1,
2811 p_attribute2 => l_eam_direct_items_rec.attribute2,
2812 p_attribute3 => l_eam_direct_items_rec.attribute3,
2813 p_attribute4 => l_eam_direct_items_rec.attribute4,
2814 p_attribute5 => l_eam_direct_items_rec.attribute5,
2815 p_attribute6 => l_eam_direct_items_rec.attribute6,
2816 p_attribute7 => l_eam_direct_items_rec.attribute7,
2817 p_attribute8 => l_eam_direct_items_rec.attribute8,
2818 p_attribute9 => l_eam_direct_items_rec.attribute9,
2819 p_attribute10 => l_eam_direct_items_rec.attribute10,
2820 p_attribute11 => l_eam_direct_items_rec.attribute11,
2821 p_attribute12 => l_eam_direct_items_rec.attribute12,
2822 p_attribute13 => l_eam_direct_items_rec.attribute13,
2823 p_attribute14 => l_eam_direct_items_rec.attribute14,
2824 p_attribute15 => l_eam_direct_items_rec.attribute15,
2825 x_error_segments => l_error_segments,
2826 x_error_message => l_error_message
2827 );
2828
2829 l_eam_direct_items_tbl(l_eam_direct_items_tbl.FIRST) := l_eam_direct_items_rec;
2830 END IF;
2831
2832 ----end of changes for 8969942
2833
2834 --Set the auto_charge type to Manual/POReceipt for newly created workorders
2835 l_eam_res_tbl := p_eam_res_tbl;
2836 IF(l_eam_res_tbl IS NOT NULL AND l_eam_res_tbl.COUNT>0) THEN
2837 FOR i IN l_eam_res_tbl.FIRST .. l_eam_res_tbl.LAST LOOP
2838 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
2839 OPEN resources(l_eam_res_tbl(i).resource_id,l_eam_res_tbl(i).organization_id);
2840 FETCH resources INTO l_resources;
2841 IF(resources%FOUND) THEN
2842 IF(l_resources.autocharge_type=1 OR l_resources.autocharge_type=2) THEN
2843 l_eam_res_tbl(i).autocharge_type := 2;
2844 ELSE
2845 l_eam_res_tbl(i).autocharge_type := 3;
2846 END IF;
2847 END IF;
2848 CLOSE resources;
2849 END IF;
2850 END LOOP;
2851 END IF;
2852
2853 --p_failure_code_required will be passed only when failure code information is entered or modified.
2854 --Make sure that work order record is passed whenever failure entry is passed, as we have some logic on work order related info
2855 IF(p_failure_code_required IS NOT NULL) THEN
2856
2857 /* Failure Analysis Project Start */
2858 l_eam_failure_entry_record.failure_id := p_failure_id;
2859 l_eam_failure_entry_record.failure_date := p_failure_date;
2860
2861 l_eam_failure_codes_tbl(1).failure_id := p_failure_id;
2862 l_eam_failure_codes_tbl(1).failure_entry_id := p_failure_entry_id;
2863 l_eam_failure_codes_tbl(1).failure_code := p_failure_code;
2864 l_eam_failure_codes_tbl(1).cause_code := p_cause_code;
2865 l_eam_failure_codes_tbl(1).resolution_code := p_resolution_code;
2866 l_eam_failure_codes_tbl(1).comments := p_failure_comments;
2867 /* Failure Analysis Project End */
2868
2869 l_fail_dept_id := l_eam_wo_rec.owning_department;
2870
2871 --ideally work order info should be passed when failure info is passed. But check that workorder is passed
2872 IF(l_eam_wo_tbl IS NOT NULL AND l_eam_wo_tbl.COUNT>0) THEN
2873
2874 --only if asset number/rebuild serial number exists for work order
2875 IF(l_maintenance_object_type =3) THEN
2876 BEGIN
2877
2878 --if workorder dept. is null,de fault it from asset's owning dept
2879 IF(l_fail_dept_id IS NULL) THEN
2880 SELECT OWNING_DEPARTMENT_ID
2881 INTO l_fail_dept_id
2882 FROM eam_org_maint_defaults
2883 WHERE object_id =l_maintenance_object_id
2884 AND object_type = 50
2885 AND organization_id =l_eam_wo_comp_rec.organization_id;
2886 END IF;
2887
2888 SELECT area_id
2889 INTO l_eam_location_id
2890 FROM eam_org_maint_defaults
2891 WHERE object_id = l_maintenance_object_id
2892 AND object_type = 50
2893 AND organization_id = l_eam_wo_comp_rec.organization_id;
2894
2895 EXCEPTION
2896 WHEN NO_DATA_FOUND THEN
2897 NULL;
2898 END;
2899 END IF; --end of check for mainteannce_object_type =3
2900
2901 l_eam_wo_rec.failure_code_required := p_failure_code_required;
2902
2903 IF(l_wo_exists = TRUE) THEN
2904
2905 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
2906 l_eam_failure_entry_record.source_type := 1;
2907 l_eam_failure_entry_record.source_id := l_eam_wo_rec.wip_entity_id;
2908 l_eam_failure_entry_record.object_type := l_eam_wo_rec.maintenance_object_type;
2909 l_eam_failure_entry_record.object_id := l_eam_wo_rec.maintenance_object_id;
2910 l_eam_failure_entry_record.maint_organization_id := l_eam_wo_rec.organization_id;
2911 l_eam_failure_entry_record.current_organization_id := l_eam_wo_rec.organization_id;
2912 l_eam_failure_entry_record.department_id := l_fail_dept_id;
2913 l_eam_failure_entry_record.area_id := l_eam_location_id;
2914
2915
2916 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
2917 if (l_eam_failure_entry_record.failure_date is null) then
2918 l_eam_failure_entry_record.failure_date := FND_API.G_MISS_DATE;
2919 end if;
2920 if (l_eam_failure_codes_tbl(1).failure_code is null) then
2921 l_eam_failure_codes_tbl(1).failure_code := FND_API.G_MISS_CHAR;
2922 end if;
2923 if (l_eam_failure_codes_tbl(1).cause_code is null) then
2924 l_eam_failure_codes_tbl(1).cause_code := FND_API.G_MISS_CHAR;
2925 end if;
2926 if (l_eam_failure_codes_tbl(1).resolution_code is null) then
2927 l_eam_failure_codes_tbl(1).resolution_code := FND_API.G_MISS_CHAR;
2928 end if;
2929 if (l_eam_failure_codes_tbl(1).comments is null) then
2930 l_eam_failure_codes_tbl(1).comments := FND_API.G_MISS_CHAR;
2931 end if;
2932 if(l_eam_failure_entry_record.failure_id is not null ) then
2933 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
2934 else
2935 if(l_eam_failure_entry_record.failure_date = FND_API.G_MISS_DATE) then
2936 l_eam_failure_entry_record.transaction_type :=null;
2937 l_eam_failure_entry_record.failure_date :=null;
2938 else
2939 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE ;
2940 end if;
2941 end if;
2942
2943 if(l_eam_failure_codes_tbl(1).failure_entry_id is not null) then
2944 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
2945 l_eam_wo_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
2946
2947 else
2948 if( not( (l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR)
2949 and (l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR)
2950 and (l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR)
2951 and (l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR)
2952 )
2953 ) then
2954 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
2955 if(l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR) then
2956 l_eam_failure_codes_tbl(1).failure_code := null;
2957 end if;
2958 if(l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR) then
2959 l_eam_failure_codes_tbl(1).cause_code := null;
2960 end if;
2961 if(l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR) then
2962 l_eam_failure_codes_tbl(1).resolution_code := null;
2963 end if;
2964 if(l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR) then
2965 l_eam_failure_codes_tbl(1).comments := null;
2966 end if;
2967 l_eam_wo_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
2968
2969 else
2970 l_eam_failure_codes_tbl.delete;
2971 l_eam_wo_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
2972
2973 end if;
2974 end if;
2975 l_eam_wo_rec.eam_failure_entry_record := l_eam_failure_entry_record;
2976
2977 ELSE -- work order is getting created
2978
2979 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
2980 l_eam_failure_entry_record.source_type := 1;
2981 l_eam_failure_entry_record.source_id := l_eam_wo_rec.wip_entity_id;
2982 l_eam_failure_entry_record.object_type := l_eam_wo_rec.maintenance_object_type;
2983 l_eam_failure_entry_record.object_id := l_eam_wo_rec.maintenance_object_id;
2984 l_eam_failure_entry_record.maint_organization_id := l_eam_wo_rec.organization_id;
2985 l_eam_failure_entry_record.current_organization_id := l_eam_wo_rec.organization_id;
2986 l_eam_failure_entry_record.department_id := l_fail_dept_id;
2987 l_eam_failure_entry_record.area_id := l_eam_location_id;
2988
2989 if(l_eam_failure_entry_record.failure_date is null) then
2990 l_eam_failure_entry_record.transaction_type :=null;
2991 end if;
2992 l_eam_wo_rec.eam_failure_entry_record := l_eam_failure_entry_record;
2993
2994 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
2995 if( not( l_eam_failure_codes_tbl(1).failure_code is null
2996 and l_eam_failure_codes_tbl(1).cause_code is null
2997 and l_eam_failure_codes_tbl(1).resolution_code is null
2998 and l_eam_failure_codes_tbl(1).comments is null
2999 )
3000 ) then
3001 l_eam_wo_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
3002 else
3003 l_eam_failure_codes_tbl.delete;
3004 l_eam_wo_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
3005 end if;
3006
3007 END IF;
3008
3009
3010 l_eam_wo_tbl(l_eam_wo_tbl.FIRST) := l_eam_wo_rec;
3011
3012 END IF; --end of check for work order record passed
3013
3014 END IF; --end of check for failure data passed
3015
3016
3017 x_wip_entity_id := NULL;
3018
3019 IF(p_eam_wo_relations_tbl IS NOT NULL AND p_eam_wo_relations_tbl.COUNT > 0) THEN
3020
3021 EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO(
3022 p_bo_identifier =>'EAM'
3023 , p_api_version_number => 1.0
3024 , p_init_msg_list => TRUE
3025 , p_eam_wo_relations_tbl => p_eam_wo_relations_tbl
3026 , p_eam_wo_tbl => l_eam_wo_tbl
3027 , p_eam_op_tbl => l_eam_op_tbl
3028 , p_eam_op_network_tbl => l_eam_op_network_tbl
3029 , p_eam_res_tbl => l_eam_res_tbl
3030 , p_eam_res_inst_tbl => p_eam_res_inst_tbl
3031 ,p_eam_res_usage_tbl => p_eam_res_usage_tbl
3032 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3033 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3034 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
3035 , p_eam_wo_comp_tbl => p_eam_wo_comp_tbl
3036 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3037 , p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
3038 , p_eam_counter_prop_tbl => p_eam_counter_prop_tbl
3039 , p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl
3040 , p_eam_wo_comp_mr_read_tbl => p_eam_wo_comp_mr_read_tbl
3041 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3042 , p_eam_request_tbl => p_eam_request_tbl
3043 , x_eam_wo_tbl => l_eam_wo_tbl_out
3044 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl_out
3045 , x_eam_op_tbl => l_eam_op_tbl_out
3046 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
3047 , x_eam_res_tbl => l_eam_res_tbl_out
3048 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
3049 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
3050 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
3051 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
3052 , x_eam_direct_items_tbl =>l_eam_direct_items_tbl_out
3053 , x_eam_wo_comp_tbl => l_eam_wo_comp_tbl_out
3054 , x_eam_wo_quality_tbl => l_eam_wo_quality_tbl_out
3055 , x_eam_meter_reading_tbl => l_eam_meter_reading_tbl_out
3056 , x_eam_counter_prop_tbl => l_eam_counter_prop_tbl_out
3057 , x_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl_out
3058 , x_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl_out
3059 , x_eam_op_comp_tbl => l_eam_op_comp_tbl_out
3060 , x_eam_request_tbl => l_eam_request_tbl_out
3061 , x_return_status => x_return_status
3062 , x_msg_count => x_msg_count
3063 , p_commit => 'N'
3064 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3065 , p_output_dir => l_output_dir
3066 , p_debug_filename => 'createupdatewo.log'
3067 , p_debug_file_mode => 'W'
3068 );
3069
3070 ELSE
3071 IF ( l_eam_wo_tbl.COUNT > 0 ) THEN
3072 l_eam_wo_rec := l_eam_wo_tbl(l_eam_wo_tbl.FIRST);
3073 END IF;
3074
3075 IF ( l_eam_wo_comp_tbl.COUNT > 0 ) THEN
3076 l_eam_wo_comp_rec := l_eam_wo_comp_tbl(l_eam_wo_comp_tbl.FIRST);
3077 END IF;
3078
3079 EAM_PROCESS_WO_PUB.PROCESS_WO(
3080 p_bo_identifier =>'EAM'
3081 , p_api_version_number => 1.0
3082 , p_init_msg_list => TRUE
3083 , p_eam_wo_rec => l_eam_wo_rec
3084 , p_eam_op_tbl => l_eam_op_tbl
3085 , p_eam_op_network_tbl => l_eam_op_network_tbl
3086 , p_eam_res_tbl => l_eam_res_tbl
3087 , p_eam_res_inst_tbl => p_eam_res_inst_tbl
3088 , p_eam_res_usage_tbl => p_eam_res_usage_tbl
3089 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3090 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3091 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
3092 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
3093 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3094 , p_eam_meter_reading_tbl => p_eam_meter_reading_tbl
3095 , p_eam_counter_prop_tbl => p_eam_counter_prop_tbl
3096 , p_eam_wo_comp_rebuild_tbl => p_eam_wo_comp_rebuild_tbl
3097 , p_eam_wo_comp_mr_read_tbl => p_eam_wo_comp_mr_read_tbl
3098 , p_eam_permit_tbl => p_eam_permit_tbl
3099 , p_eam_permit_wo_assoc_tbl => p_eam_permit_wo_assoc_tbl
3100 , p_eam_work_clearance_tbl => p_eam_work_clearance_tbl-- new parameter for safety clearance
3101 , p_eam_wc_wo_assoc_tbl => p_eam_wc_wo_assoc_tbl
3102 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3103 , p_eam_request_tbl => p_eam_request_tbl
3104 , x_eam_wo_rec => l_eam_wo_rec_out
3105 , x_eam_op_tbl => l_eam_op_tbl_out
3106 , x_eam_op_network_tbl => l_eam_op_network_tbl_out
3107 , x_eam_res_tbl => l_eam_res_tbl_out
3108 , x_eam_res_usage_tbl => l_eam_res_usage_tbl_out
3109 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_out
3110 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_out
3111 , x_eam_mat_req_tbl => l_eam_mat_req_tbl_out
3112 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_out
3113 , x_eam_wo_comp_rec => l_eam_wo_comp_rec_out
3114 , x_eam_wo_quality_tbl => l_eam_wo_quality_tbl_out
3115 , x_eam_meter_reading_tbl => l_eam_meter_reading_tbl_out
3116 , x_eam_counter_prop_tbl => l_eam_counter_prop_tbl_out
3117 , x_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl_out
3118 , x_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl_out
3119 , x_eam_op_comp_tbl => l_eam_op_comp_tbl_out
3120 , x_eam_request_tbl => l_eam_request_tbl_out
3121 , x_return_status => x_return_status
3122 , x_msg_count => x_msg_count
3123 , p_commit => 'N'
3124 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3125 , p_output_dir => l_output_dir
3126 , p_debug_filename => 'createupdatewo.log'
3127 , p_debug_file_mode => 'W'
3128 );
3129
3130 END IF;
3131
3132 END IF; /*MSP IF END*/
3133
3134 IF(x_return_status='S') THEN
3135 IF p_commit = FND_API.G_TRUE THEN
3136 COMMIT WORK;
3137 end if;
3138 IF(l_eam_wo_tbl_out IS NOT NULL AND l_eam_wo_tbl_out.COUNT>0) THEN
3139 x_wip_entity_id := l_eam_wo_tbl_out(l_eam_wo_tbl_out.FIRST).wip_entity_id;
3140 ELSIF(l_eam_wo_rec_out.wip_entity_id IS NOT NULL) THEN
3141 x_wip_entity_id := l_eam_wo_rec_out.wip_entity_id;
3142 END IF;
3143 END IF;
3144
3145 IF(x_return_status <> 'S') THEN
3146 ROLLBACK TO create_update_wo;
3147 END IF;
3148
3149 END CREATE_UPDATE_WO;
3150
3151
3152
3153
3154 /********************************************************
3155 Procedure to find the required,assigned and unassigned hours at workorder level
3156 *********************************************************/
3157 PROCEDURE ASSIGNED_HOURS
3158 (
3159 p_wip_entity_id IN NUMBER,
3160 x_required_hours OUT NOCOPY NUMBER,
3161 x_assigned_hours OUT NOCOPY NUMBER,
3162 x_unassigned_hours OUT NOCOPY NUMBER
3163 )
3164 IS
3165
3166 l_uom_conv NUMBER;
3167 l_hour_uom VARCHAR2(10);
3168 l_sysdate DATE;
3169 l_next_date DATE;
3170 l_inv_item_id NUMBER;
3171
3172 CURSOR get_hours_details_csr IS
3173 SELECT ROUND(NVL(wor.usage_rate_or_amount * (1/l_uom_conv) *
3174 (DECODE (con.conversion_rate,'', 0, '0', 0, con.conversion_rate)),0),2) required_hours,
3175 (SELECT ROUND(NVL(SUM((woru.completion_date-woru.start_date)*24) ,0),2)
3176 FROM WIP_OPERATION_RESOURCE_USAGE woru
3177 WHERE woru.wip_entity_id = wor.wip_entity_id
3178 AND woru.organization_id = wor.organization_id
3179 AND woru.operation_seq_num = wor.operation_seq_num
3180 AND woru.resource_seq_num = wor.resource_seq_num
3181 AND woru.instance_id IS NOT NULL
3182 AND woru.serial_number IS NULL) assigned_hours
3183 FROM WIP_OPERATION_RESOURCES wor,MTL_UOM_CONVERSIONS con,BOM_RESOURCES br
3184 WHERE wor.wip_entity_id = p_wip_entity_id
3185 AND wor.resource_id = br.resource_id
3186 AND br.resource_type = 2 --for person type resources only
3187 AND con.uom_code = wor. uom_code
3188 AND NVL(con.disable_date, l_next_date) > l_sysdate
3189 AND con.inventory_item_id = l_inv_item_id;
3190
3191
3192 BEGIN
3193
3194 l_hour_uom := fnd_profile.value('BOM:HOUR_UOM_CODE');
3195
3196 l_sysdate := SYSDATE;
3197 l_next_date := SYSDATE+1;
3198 l_inv_item_id := 0;
3199
3200
3201
3202 x_required_hours := 0;
3203 x_assigned_hours := 0;
3204 x_unassigned_hours := 0;
3205
3206
3207 SELECT CON.CONVERSION_RATE
3208 INTO l_uom_conv
3209 FROM MTL_UOM_CONVERSIONS CON
3210 WHERE CON.UOM_CODE = l_hour_uom
3211 AND NVL(DISABLE_DATE, l_next_date) > l_sysdate
3212 AND CON.INVENTORY_ITEM_ID = l_inv_item_id;
3213
3214 --Required Hours will be 'Usage-Rate-Or-Amount' and assigned hrs will be fetched from WORU
3215 --Unassigned hours will be added only if Assigned < Required, else it will be treated as 0
3216
3217 FOR p_hours_row IN get_hours_details_csr
3218 LOOP
3219 x_required_hours := x_required_hours + p_hours_row.required_hours;
3220 x_assigned_hours := x_assigned_hours + p_hours_row.assigned_hours;
3221
3222 IF(p_hours_row.required_hours - p_hours_row.assigned_hours > 0) THEN
3223 x_unassigned_hours := x_unassigned_hours + (p_hours_row.required_hours - p_hours_row.assigned_hours);
3224 END IF;
3225
3226 END LOOP;
3227
3228 EXCEPTION
3229 WHEN NO_DATA_FOUND THEN
3230 NULL;
3231 END ASSIGNED_HOURS;
3232
3233 END EAM_CREATEUPDATE_WO_PVT;
3234