[Home] [Help]
PACKAGE BODY: APPS.EAM_WORKORDERS_JSP
Source
1 package body EAM_WORKORDERS_JSP AS
2 /* $Header: EAMJOBJB.pls 120.15 2006/09/18 08:58:24 cboppana noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_WORKORDERS_JSP';
4 g_debug_sqlerrm VARCHAR2(250);
5
6 ---------------------------------------------------------------------
7 --procedure to add existing work orders
8 ------------------------------------------------------
9
10 procedure add_exist_work_order(
11 p_api_version IN NUMBER := 1.0
12 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
13 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
14 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
15 ,p_record_version_number IN NUMBER := NULL
16 ,x_return_status OUT NOCOPY VARCHAR2
17 ,x_msg_count OUT NOCOPY NUMBER
18 ,x_msg_data OUT NOCOPY VARCHAR2
19 ,p_organization_id IN NUMBER
20 ,p_wip_entity_id IN NUMBER
21 ,p_firm_flag IN NUMBER
22 ,p_parent_wip_id IN NUMBER
23 , p_relation_type IN NUMBER
24
25 )
26 IS
27 l_api_name CONSTANT VARCHAR(30) := 'add_exist_work_order';
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_return_status VARCHAR2(250);
30 l_error_msg_code VARCHAR2(250);
31 l_msg_count NUMBER;
32 l_msg_data VARCHAR2(250);
33 l_err_code VARCHAR2(250);
34 l_err_stage VARCHAR2(250);
35 l_err_stack VARCHAR2(250);
36 l_data VARCHAR2(250);
37 l_msg_index_out NUMBER;
38
39 l_workorder_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
40 l_workorder_rec1 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
41 l_workorder_rec2 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
42 l_workorder_rec3 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
43 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
44 l_eam_op_tbl1 EAM_PROCESS_WO_PUB.eam_op_tbl_type;
45 l_eam_op_tbl2 EAM_PROCESS_WO_PUB.eam_op_tbl_type;
46 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
47 l_eam_op_network_tbl1 EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
48 l_eam_op_network_tbl2 EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
49 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
50 l_eam_res_tbl1 EAM_PROCESS_WO_PUB.eam_res_tbl_type;
51 l_eam_res_tbl2 EAM_PROCESS_WO_PUB.eam_res_tbl_type;
52 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
53 l_eam_res_inst_tbl1 EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
54 l_eam_res_inst_tbl2 EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
55 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
56 l_eam_sub_res_tbl1 EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
57 l_eam_sub_res_tbl2 EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
58 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
59 l_eam_res_usage_tbl1 EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
60 l_eam_res_usage_tbl2 EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
61 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
62 l_eam_mat_req_tbl1 EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
63 l_eam_mat_req_tbl2 EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
64 l_wip_entity_id NUMBER;
65 --Bug3592712: Max length of workorder name is 240 char.
66 l_wip_entity_name VARCHAR2(240);
67
68 l_eam_item_type NUMBER;
69 l_status_type NUMBER;
70
71 l_wip_entity_updt NUMBER;
72
73 l_mode NUMBER; -- 0 for Create and 1 for Update
74 l_date_released DATE;
75 l_user_id NUMBER;
76 l_responsibility_id NUMBER;
77 l_firm NUMBER;
78 l_serial_number_control NUMBER := 0;
79 l_work_name VARCHAR2(240);
80 l_parent_work_order_count number:=0;
81 l_adjust_parent varchar2(10);
82 l_row_count number :=0;
83
84 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
85 l_eam_wo_relations_tbl1 EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
86 l_eam_wo_relations_rec EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
87 l_eam_wo_relations_rec1 EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
88
89 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
90 l_eam_wo_tbl1 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
91 l_eam_wo_tbl2 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
92 l_eam_wo_tbl3 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
93
94 l_eam_msg_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
95 l_old_rebuild_source NUMBER;
96 l_message_text varchar2(20);
97
98 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
99 l_eam_direct_items_tbl_1 EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
100 l_output_dir VARCHAR2(512);
101
102 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
103 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
104 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
105 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
106 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
107 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
108 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
109 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
110 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
111 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
112 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
113 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
114 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
115 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
116 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
117 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
118
119 BEGIN
120
121 SAVEPOINT add_exist_work_order;
122
123 eam_debug.init_err_stack('eam_workorders_jsp.add_exist_work_order');
124
125 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
126 p_api_version,
127 l_api_name,
128 g_pkg_name)
129 THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131 END IF;
132
133 IF FND_API.TO_BOOLEAN(p_init_msg_list)
134 THEN
135 FND_MSG_PUB.initialize;
136 END IF;
137
138 /* get output directory path from database */
139 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
140
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142
143
144 l_workorder_rec.header_id := p_wip_entity_id;
145 l_workorder_rec.batch_id := 1;
146 l_workorder_rec.return_status := null;
147
148 l_workorder_rec.wip_entity_id := p_wip_entity_id;
149 l_workorder_rec.organization_id := p_organization_id;
150
151
152
153 l_workorder_rec.firm_planned_flag := p_firm_flag;
154 l_workorder_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
155
156 -- Set user id and responsibility id so that we can set apps context
157 -- before calling any concurrent program
158 l_workorder_rec.user_id := l_user_id;
159 l_workorder_rec.responsibility_id := l_responsibility_id;
160
161
162 if(p_relation_type=1) then
163 l_eam_wo_tbl(1) := l_workorder_rec;
164 end if;
165
166 l_eam_wo_relations_rec.batch_id := 1;
167 l_eam_wo_relations_rec.parent_object_id := p_parent_wip_id;
168 l_eam_wo_relations_rec.parent_object_type_id := 1;
169 l_eam_wo_relations_rec.parent_header_id := p_parent_wip_id;
170 l_eam_wo_relations_rec.child_object_type_id := 1;
171 l_eam_wo_relations_rec.child_header_id :=p_wip_entity_id;
172 l_eam_wo_relations_rec.child_object_id :=p_wip_entity_id;
173 l_eam_wo_relations_rec.parent_relationship_type := p_relation_type ;
174 l_eam_wo_relations_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
175
176 l_eam_wo_relations_tbl(1) := l_eam_wo_relations_rec;
177
178 EAM_PROCESS_WO_PUB.l_eam_wo_list.delete; --Added for bug#4563210
179
180 EAM_PROCESS_WO_PUB.Process_Master_Child_WO
181 ( p_bo_identifier => 'EAM'
182 , p_init_msg_list => TRUE
183 , p_api_version_number => 1.0
184 , p_eam_wo_tbl => l_eam_wo_tbl
185 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
186 , p_eam_op_tbl => l_eam_op_tbl
187 , p_eam_op_network_tbl => l_eam_op_network_tbl
188 , p_eam_res_tbl => l_eam_res_tbl
189 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
190 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
191 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
192 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
193 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
194 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
195 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
196 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
197 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
198 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
199 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
200 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
201 , p_eam_request_tbl => l_eam_request_tbl
202 , x_eam_wo_tbl => l_eam_wo_tbl1
203 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
204 , x_eam_op_tbl => l_eam_op_tbl1
205 , x_eam_op_network_tbl => l_eam_op_network_tbl1
206 , x_eam_res_tbl => l_eam_res_tbl1
207 , x_eam_res_inst_tbl => l_eam_res_inst_tbl1
208 , x_eam_sub_res_tbl => l_eam_sub_res_tbl1
209 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
210 , x_eam_mat_req_tbl => l_eam_mat_req_tbl1
211 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
212 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
213 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
214 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
215 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
216 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
217 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
218 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
219 , x_eam_request_tbl => l_out_eam_request_tbl
220 , x_return_status => x_return_status
221 , x_msg_count => x_msg_count
222 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
223 , p_debug_filename => 'addexistwo.log'
224 , p_output_dir => l_output_dir
225 , p_commit => 'N'
226 , p_debug_file_mode => 'w'
227 );
228
229
230 /*End of update wo ***********/
231
232 if(x_return_status<>'S') then
233 ROLLBACK TO add_exist_work_order;
234 RAISE FND_API.G_EXC_ERROR;
235 end if;
236
237
238 EXCEPTION
239 WHEN
240 FND_API.G_EXC_UNEXPECTED_ERROR THEN
241 IF p_commit = FND_API.G_TRUE THEN
242 ROLLBACK TO add_exist_work_order;
243 END IF;
244 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_workorders_jsp.ADD_EXIST_WORK_ORDER',
245 p_procedure_name => EAM_DEBUG.G_err_stack);
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 WHEN FND_API.G_EXC_ERROR THEN
248 IF p_commit = FND_API.G_TRUE THEN
249 ROLLBACK TO add_exist_work_order;
250 END IF;
251
252 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_workorders_jsp.ADD_EXIST_WORK_ORDER',
253 p_procedure_name => EAM_DEBUG.G_err_stack);
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255
256 WHEN OTHERS THEN -- all dml excpetion
257 IF p_commit = FND_API.G_TRUE THEN
258 ROLLBACK TO add_exist_work_order;
259 END IF;
260
261 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_workorders_jsp.ADD_EXIST_WORK_ORDER',
262 p_procedure_name => EAM_DEBUG.G_err_stack);
263
264
265 END add_exist_work_order;
266
267
268 procedure validate_cancel(p_wip_entity_id NUMBER)
269 IS
270
271 network_child_job_var varchar2(2):='0';
272 dependent_rel varchar2(2):='0';
273 wo_released NUMBER;
274 BEGIN
275
276 begin
277 begin
278 SELECT '1'
279 INTO network_child_job_var
280 FROM dual
281 WHERE EXISTS (SELECT '1'
282 FROM wip_discrete_jobs
283 WHERE wip_entity_id IN
284 (
285 SELECT DISTINCT child_object_id
286 FROM eam_wo_relationships
287 WHERE parent_relationship_type =1
288 START WITH parent_object_id = p_wip_entity_id AND parent_relationship_type = 1
289 CONNECT BY parent_object_id = prior child_object_id AND parent_relationship_type = 1
290 )
291 AND status_type NOT IN (7)
292 );
293 exception
294 when NO_DATA_FOUND then
295 null;
296 end;
297
298
299 if (network_child_job_var = '1') then --In the network Work Order is there are child work orders not in cancelled state
300
301 fnd_message.set_name('EAM','EAM_WO_CANCEL_ERR');
302
303 APP_EXCEPTION.RAISE_EXCEPTION;
304 else
305 begin
306 SELECT decode(wdj.status_type,3,1,0)
307 INTO wo_released
308 FROM WIP_DISCRETE_JOBS wdj
309 WHERE wdj.wip_entity_id=p_wip_entity_id;
310
311 SELECT '1'
312 INTO dependent_rel
313 FROM DUAL
314 WHERE EXISTS (SELECT ewr.child_object_id
315 FROM EAM_WO_RELATIONSHIPS ewr,WIP_DISCRETE_JOBS wdj
316 WHERE ewr.parent_object_id=p_wip_entity_id AND ewr.parent_relationship_type = 2
317 AND wdj.wip_entity_id=ewr.child_object_id AND (wo_released=1 OR wdj.status_type=3)
318 UNION
319 SELECT ewr.parent_object_id
320 FROM EAM_WO_RELATIONSHIPS ewr,WIP_DISCRETE_JOBS wdj
321 WHERE ewr.child_object_id=p_wip_entity_id AND ewr.parent_relationship_type = 2
322 AND wdj.wip_entity_id=ewr.parent_object_id AND (wo_released=1 OR wdj.status_type=3)
323 );
324 exception
325 when NO_DATA_FOUND then
326 null;
327 end;
328
329 if(dependent_rel='1') then
330 fnd_message.set_name('EAM','EAM_WO_CANCEL_DEPENDENCY_ERR');
331
332 APP_EXCEPTION.RAISE_EXCEPTION;
333 end if;
334
335 end if;
336 exception
337 WHEN OTHERS THEN
338 APP_EXCEPTION.RAISE_EXCEPTION;
339 end;
340 END validate_cancel;
341
342
343 -------------------------------------------------------------------------
344 -- a wrapper procedure to the eam_completion.complete_work_order,
345 -- also check the return status add message to the message list
346 -- so jsp pages can get them.
347 -------------------------------------------------------------------------
348 procedure Complete_Workorder
349 ( p_api_version IN NUMBER := 1.0
350 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
351 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
352 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
353 ,p_record_version_number IN NUMBER := NULL
354 ,x_return_status OUT NOCOPY VARCHAR2
355 ,x_msg_count OUT NOCOPY NUMBER
356 ,x_msg_data OUT NOCOPY VARCHAR2
357 ,p_wip_entity_id IN NUMBER -- data
358 ,p_actual_start_date IN DATE
359 ,p_actual_end_date IN DATE
360 ,p_actual_duration IN NUMBER
361 ,p_transaction_date IN DATE
362 ,p_transaction_type IN NUMBER
363 ,p_shutdown_start_date IN DATE
364 ,p_shutdown_end_date IN DATE
365 ,p_reconciliation_code IN VARCHAR2
366 ,p_stored_last_update_date IN DATE -- old update date, for locking only
367 ,p_rebuild_jobs IN VARCHAR2 := NULL -- holds 'Y' or 'N'
368 ,p_subinventory IN VARCHAR2 := NULL
369 ,p_subinv_ctrl IN NUMBER := NULL
370 ,p_org_id IN NUMBER := NULL
371 ,p_item_id IN NUMBER := NULL
372 ,p_locator_id IN NUMBER := NULL
373 ,p_locator_ctrl IN NUMBER := NULL
374 ,p_locator IN VARCHAR2 := NULL
375 ,p_lot IN VARCHAR2 := NULL
376 ,p_serial IN VARCHAR2 := NULL
377 ,p_manual_flag IN VARCHAR2 := NULL
378 ,p_serial_status IN VARCHAR2 := NULL
379 ,p_qa_collection_id IN NUMBER
380 ,p_attribute_category IN VARCHAR2 := null
381 ,p_attribute1 IN VARCHAR2 := null
382 ,p_attribute2 IN VARCHAR2 := null
383 ,p_attribute3 IN VARCHAR2 := null
384 ,p_attribute4 IN VARCHAR2 := null
385 ,p_attribute5 IN VARCHAR2 := null
386 ,p_attribute6 IN VARCHAR2 := null
387 ,p_attribute7 IN VARCHAR2 := null
388 ,p_attribute8 IN VARCHAR2 := null
389 ,p_attribute9 IN VARCHAR2 := null
390 ,p_attribute10 IN VARCHAR2 := null
391 ,p_attribute11 IN VARCHAR2 := null
392 ,p_attribute12 IN VARCHAR2 := null
393 ,p_attribute13 IN VARCHAR2 := null
394 ,p_attribute14 IN VARCHAR2 := null
395 ,p_attribute15 IN VARCHAR2 := null
396 ) IS
397
398 l_api_name CONSTANT VARCHAR(30) := 'Complete_Workorder';
399 l_api_version CONSTANT NUMBER := 1.0;
400 l_return_status VARCHAR2(250);
401 l_error_msg_code VARCHAR2(250);
402 l_msg_count NUMBER;
403 l_msg_data VARCHAR2(250);
404 l_err_code VARCHAR2(250);
405 l_err_stage VARCHAR2(250);
406 l_err_stack VARCHAR2(250);
407 l_data VARCHAR2(250);
408 l_msg_index_out NUMBER;
409 l_err_number NUMBER;
410
411 l_new_status NUMBER;
412 l_db_status NUMBER;
413 l_db_last_update_date DATE;
414 l_org_id NUMBER;
415 l_tran_type NUMBER;
416 l_reconciliation_code VARCHAR2(30);
417 l_shutdown_type VARCHAR2(30);
418 l_max_compl_op_date DATE;
419 l_min_open_period DATE;
420 l_min_compl_op_date DATE;
421 l_max_tran_date DATE;
422 l_actual_start_date DATE := p_actual_start_date;
423 l_actual_end_date DATE := p_actual_end_date;
424 l_actual_duration NUMBER := p_actual_duration;
425 l_dummy NUMBER;
426
427
428 l_subinv VARCHAR2(80);
429 l_locator_ctrl NUMBER ; -- Holds the Locator Control information
430 l_error_flag NUMBER;
431 l_error_mssg VARCHAR2(250);
432 l_item_id NUMBER;
433 l_locator_id NUMBER;
434 l_completion_info NUMBER;
435 l_lot_ctrl_code NUMBER;
436 l_lot_number VARCHAR2(80);
437
438
439 BEGIN
440
441 IF p_commit = FND_API.G_TRUE THEN
442 SAVEPOINT complete_workorder;
443 END IF;
444
445 eam_debug.init_err_stack('eam_workorders_jsp.complete_workorder');
446
447 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
448 p_api_version,
449 l_api_name,
450 g_pkg_name)
451 THEN
452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
453 END IF;
454
455 IF FND_API.TO_BOOLEAN(p_init_msg_list)
456 THEN
457 FND_MSG_PUB.initialize;
458 END IF;
459
460 x_return_status := FND_API.G_RET_STS_SUCCESS;
461
462 -- check if data is stale or not
463 -- using last_update_date as indicator
464 BEGIN
465 SELECT last_update_date, status_type, shutdown_type
466 INTO l_db_last_update_date, l_db_status , l_shutdown_type
467 FROM wip_discrete_jobs
468 WHERE wip_entity_id = p_wip_entity_id
469 FOR UPDATE;
470
471 IF l_db_last_update_date <> p_stored_last_update_date THEN
472 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_STALED_DATA');
473 x_return_status := FND_API.G_RET_STS_ERROR;
474 END IF;
475
476 IF ( (p_transaction_type = 1 AND l_db_status <> 3) or
477 (p_transaction_type = 2 and (l_db_status <> 4 and l_db_status <> 5)) ) THEN -- 5??
478 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_COMP_WRONG_STATUS');
479 x_return_status := FND_API.G_RET_STS_ERROR;
480 END IF;
481
482 EXCEPTION WHEN OTHERS THEN
483 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_NOT_FOUND');
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
485 END;
486
487 --dgupta: default actual start and end dates from the last completion txn.
488 --Note: This is redundant as we are not using the actual start and end for
489 --uncompletion anywhere, but keeping for sake of consistency with forms
490 if (p_transaction_type = 2) then
491 select max(transaction_date) into l_max_tran_date
492 from eam_job_completion_txns where transaction_type = 1
493 and wip_entity_id = p_wip_entity_id;
494 select actual_start_date, actual_end_date into
495 l_actual_start_date, l_actual_end_date
496 from eam_job_completion_txns where transaction_date = l_max_tran_date
497 and wip_entity_id = p_wip_entity_id;
498 l_actual_duration := (l_actual_end_date - l_actual_start_date)* 24;
499 end if;
500
501 select nvl(min(period_start_date), sysdate+2)
502 into l_min_open_period
503 from org_acct_periods
504 where organization_id = p_org_id
505 and open_flag = 'Y';
506 /* Fix for bug no: 2695696 */
507 /*Fix for bug 3235163*/
508 --Previously the check was for actual_end date.It has been changed to p_transaction_date
509 if (p_transaction_date < l_min_open_period) then
510 eam_execution_jsp.add_message(p_app_short_name => 'EAM',
511 p_msg_name => 'EAM_TRANSACTION_DATE_INVALID');
512 end if;
513 /*End of fix for bug 3235163*/
514 /* end of fix for bug o:2695696 */
515
516 if (p_transaction_type = 1) then -- added by dgupta
517 /* Fix for Bug 2100416 */
518 select nvl(max(actual_end_date), sysdate - 200000)
519 into l_max_compl_op_date
520 from eam_op_completion_txns eoct
521 where wip_entity_id = p_wip_entity_id
522 --fix for 3543834.added where clause so that the last completion date will be fetched if the operation is complete
523 and transaction_type=1
524 and transaction_id = (select max(transaction_id)
525 from eam_op_completion_txns
526 where wip_entity_id = p_wip_entity_id
527 and operation_seq_num = eoct.operation_seq_num
528 );
529 /* Fix for bug no:2730242 */
530 select nvl(min(actual_start_date), sysdate + 200000)
531 into l_min_compl_op_date
532 from eam_op_completion_txns eoct
533 where wip_entity_id = p_wip_entity_id
534 --fix for 3543834.added where clause so that the last completion date will be fetched if the operation is complete
535 and transaction_type=1
536 and transaction_id = (select max(transaction_id)
537 from eam_op_completion_txns
538 where wip_entity_id = p_wip_entity_id
539 and operation_seq_num = eoct.operation_seq_num
540 );
541
542 if ((p_actual_start_date is not null) and (p_actual_duration is not null)) then
543 -- Start Fix for Bug 2165293
544 if (p_actual_duration < 0) then
545 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_NEGATIVE_DURATION');
546 x_return_status := FND_API.G_RET_STS_ERROR;
547 end if;
548 -- End Fix for Bug 2165293
549
550 if (p_actual_end_date > sysdate) then
551 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_END_LATER_THAN_TODAY');
552 x_return_status := FND_API.G_RET_STS_ERROR;
553 end if;
554
555
556
557 -- mmaduska added for bug 3273898
558 -- mmaduska added and condition to solve the date time truncation problem
559
560 -- changed conditions for 3543834 so that actual_start_date and actual_end_date will be validated
561 if (
562 ((p_actual_end_date < l_max_compl_op_date) AND (l_max_compl_op_date - p_actual_end_date > (0.000011575 * 60 ))) OR
563 ((p_actual_start_date > l_min_compl_op_date) AND (p_actual_start_date - l_min_compl_op_date > (0.000011575 * 60 )))
564 )then
565 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_COMPL_DATES_INVALID',
566 p_token1=>'MIN_OP_DATE',p_value1=>TO_CHAR(l_min_compl_op_date,'dd-MON-yyyy HH24:MI:SS'),p_token2=>'MAX_OP_DATE'
567 ,p_value2=>TO_CHAR(l_max_compl_op_date,'dd-MON-yyyy HH24:MI:SS'));
568 x_return_status := FND_API.G_RET_STS_ERROR;
569 end if;
570
571 -- if p_actual_start_date is close to l_min_compl_op_date by a min or p_actual_end_date is close to l_max_compl_op_date
572 if (p_actual_end_date < l_max_compl_op_date) then
573 l_actual_end_date := l_max_compl_op_date;
574 else
575 l_actual_end_date := p_actual_end_date;
576 end if;
577
578 if(p_actual_start_date > l_min_compl_op_date) then
579 l_actual_start_date := l_min_compl_op_date;
580 else
581 l_actual_start_date := p_actual_start_date;
582 end if;
583
584 end if; -- end of if p_actual_start_date is not null ...
585 /* End of Fix 2100416*/
586 end if; -- end of if (p_transaction_type = 1 ...
587 BEGIN
588 l_reconciliation_code := null;
589 if( p_reconciliation_code is not null) then
590 select mlu.lookup_code
591 into l_reconciliation_code
592 from mfg_lookups mlu
593 where mlu.lookup_type = 'WIP_EAM_RECONCILIATION_CODE'
594 and mlu.meaning = p_reconciliation_code;
595 end if;
596 EXCEPTION WHEN OTHERS THEN
597 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_RECONCILIATION_CODE_INV');
598 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
599 END;
600
601 if( p_shutdown_start_date > p_shutdown_end_date) then
602 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_SHUTDOWN_DATE_BAD');
603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604 end if;
605
606 /* I. As part of IB changes, Completing to a subinventory is always optional
607 II. Even Asset WOs can be completed to subinventory
608 III. As part of Transactability changes, an asset may be completed to
609 subinventory under the following cases only:
610 1) Item is assigned to the current work order org and
611 2) If Item is serialized the current status of the item is 4 */
612
613
614 l_completion_info := 0;
615
616
617 /* Finding out SubInventory is Correct or Not */
618 if(p_subinventory IS NOT NULL) then
619
620 if (p_serial is null) then
621 begin
622 select 1 into l_Dummy from mtl_system_items_b msi
623 where msi.inventory_item_id = p_item_id
624 and msi.organization_id = p_org_id;
625
626 exception
627 when no_data_found then
628 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_ITEM_NOT_ASSIGNED');
629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
630 end;
631
632 else
633
634 begin
635 select 1 into l_Dummy from mtl_serial_numbers msn
636 where msn.inventory_item_id = p_item_id
637 and msn.serial_number = p_serial
638 and msn.current_status = 4;
639 exception
640 when no_data_found then
641 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_NOT_OUT_OF_STORES');
642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
643 end;
644
645 end if;
646
647
648 select
649 lot_control_code into l_lot_ctrl_code
650 from
651 mtl_system_items_b
652 where
653 inventory_item_id = p_item_id
654 and organization_id = p_org_id ;
655
656
657 Begin
658 if(p_subinv_ctrl is null or p_subinv_ctrl <> 1) then
659 select secondary_inventory_name into l_subinv
660 from mtl_secondary_inventories
661 where
662 secondary_inventory_name = p_subinventory
663 and organization_id = p_org_id
664 and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
665 and Asset_inventory = 2;
666 elsif(p_subinv_ctrl = 1) then
667 select secondary_inventory_name into l_subinv
668 from mtl_secondary_inventories
669 where
670 secondary_inventory_name = p_subinventory
671 and organization_id = p_org_id
672 and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
673 and Asset_inventory = 2
674 and EXISTS (select secondary_inventory from mtl_item_sub_inventories
675 where secondary_inventory = secondary_inventory_name
676 and inventory_item_id = p_item_id
677 and organization_id = p_org_id);
678 end if;
679 exception
680 when no_data_found then
681 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_RET_MAT_INVALID_SUBINV');
682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
683 return;
684 end;
685 end if;
686
687 l_locator_id := p_locator_id;
688
689 /* Finding out Locator ID from Locator */
690 if(p_locator_id IS NULL and p_locator IS NOT NULL) then
691 Begin
692
693 if(p_locator_ctrl is null or p_locator_ctrl <> 1) then
694 select Inventory_Location_ID into l_locator_id
695 from mtl_item_locations_kfv where
696 concatenated_segments = p_locator
697 and subinventory_code = p_subinventory
698 and organization_id = p_org_id;
699 elsif(p_locator_ctrl = 1) then
700 select Inventory_Location_ID into l_locator_id
701 from mtl_item_locations_kfv where
702 concatenated_segments = p_locator
703 and subinventory_code = p_subinventory
704 and organization_id = p_org_id
705 and EXISTS (select '1' from mtl_secondary_locators
706 where inventory_item_id = p_item_id
707 and organization_id = p_org_id
708 and secondary_locator = inventory_location_id) ;
709 end if; -- end of inner if
710 exception
711 when no_data_found then
712 -- x_error_flag := 1;
713 -- x_error_mssg := 'EAM_RET_MAT_INVALID_LOCATOR';
714 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_RET_MAT_INVALID_LOCATOR');
715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716 return;
717 end;
718 end if;
719
720 /* Check for Locator Control which could be defined
721 at 3 level Organization,Subinventory,Item .
722 */
723 EAM_MTL_TXN_PROCESS.Get_LocatorControl_Code(
724 p_org_id,
725 p_subinventory,
726 p_item_id,
727 27,
728 l_locator_ctrl,
729 l_error_flag,
730 l_error_mssg);
731
732 if(l_error_flag <> 0) then
733 return;
734 end if;
735
736 -- if the locator control is Predefined or Dynamic Entry
737 if(l_locator_ctrl = 2 or l_locator_ctrl = 3) then
738 if(l_locator_id IS NULL) then
739 /* l_error_flag := 1;
740 l_error_mssg := 'EAM_RET_MAT_LOCATOR_NEEDED'; */
741 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_RET_MAT_LOCATOR_NEEDED');
742 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
743 return;
744 end if;
745 elsif(l_locator_ctrl = 1) then -- If the locator control is NOControl
746 if(l_locator_id IS NOT NULL) then
747 /* l_error_flag := 1;
748 l_error_mssg := 'EAM_RET_MAT_LOCATOR_RESTRICTED'; */
749 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_RET_MAT_LOCATOR_RESTRICTED');
750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
751 return;
752 end if;
753 end if; -- end of locator_control checkif
754
755 /* CHECK for lot entry */
756 if(l_lot_ctrl_code = 2) then
757 if(p_lot is not null)then
758 begin
759 select
760 lot_number into l_lot_number
761 from
762 mtl_lot_numbers
763 where
764 inventory_item_id = p_item_id
765 and organization_id = p_org_id;
766 exception
767 when NO_DATA_FOUND then
768 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_NO_LOT_NUMBER');
769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
770 end;
771 else
772 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_LOT_NEEDED');
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
774 end if;
775 else
776 if(p_lot is not null) then
777 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_LOT_NOT_NEEDED');
778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
779 end if;
780 end if; -- end of lot entry check
781
782 -- end if; -- end of completion_info check
783
784 -- end if; -- end of rebuild flag check
785
786 -- ----------------------------------
787
788 -- if validate not passed then raise error
789 l_msg_count := FND_MSG_PUB.count_msg;
790 IF l_msg_count = 1 THEN
791 eam_execution_jsp.Get_Messages
792 (p_encoded => FND_API.G_FALSE,
793 p_msg_index => 1,
794 p_msg_count => l_msg_count,
795 p_msg_data => nvl(l_msg_data,FND_API.g_MISS_CHAR),
796 p_data => l_data,
797 p_msg_index_out => l_msg_index_out);
798 x_msg_count := l_msg_count;
799 x_msg_data := l_msg_data;
800 ELSE
801 x_msg_count := l_msg_count;
802 END IF;
803
804 IF l_msg_count > 0 THEN
805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
806 RAISE FND_API.G_EXC_ERROR;
807 END IF;
808
809 BEGIN
810 /* call processing logic */
811 eam_completion.complete_work_order(
812 x_wip_entity_id => p_wip_entity_id
813 ,x_rebuild_jobs => p_rebuild_jobs
814 ,x_transaction_type => p_transaction_type
815 ,x_transaction_date => sysdate
816 ,x_user_id => g_last_updated_by
817 ,x_actual_start_date => l_actual_start_date
818 ,x_actual_end_date => l_actual_end_date
819 ,x_actual_duration => l_actual_duration
820 ,x_reconcil_code => l_reconciliation_code
821 ,x_shutdown_start_date => p_shutdown_start_date
822 ,x_shutdown_end_date => p_shutdown_end_date
823 ,x_subinventory => p_subinventory
824 ,x_locator_id => p_locator_id
825 ,x_lot_number => p_lot
826 ,x_serial_number => p_serial
827 ,errcode => l_err_number
828 ,errmsg => l_err_code
829 ,x_qa_collection_id =>p_qa_collection_id
830 ,x_attribute_category => p_attribute_category
831 ,x_attribute1 => p_attribute1
832 ,x_attribute2 => p_attribute2
833 ,x_attribute3 => p_attribute3
834 ,x_attribute4 => p_attribute4
835 ,x_attribute5 => p_attribute5
836 ,x_attribute6 => p_attribute6
837 ,x_attribute7 => p_attribute7
838 ,x_attribute8 => p_attribute8
839 ,x_attribute9 => p_attribute9
840 ,x_attribute10 => p_attribute10
841 ,x_attribute11 => p_attribute11
842 ,x_attribute12 => p_attribute12
843 ,x_attribute13 => p_attribute13
844 ,x_attribute14 => p_attribute14
845 ,x_attribute15 => p_attribute15
846 );
847
848 IF (l_err_number = 2) THEN
849 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name =>'EAM_WO_NO_UNCOMPLETE' );
850 x_return_status := FND_API.G_RET_STS_ERROR;
851 ELSIF (l_err_number > 0 ) THEN
852 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name =>l_err_code);
853 x_return_status := FND_API.G_RET_STS_ERROR;
854 END IF;
855
856 EXCEPTION WHEN OTHERS THEN
857 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_EXCEPTION',
858 p_token1 => 'TEXT', p_value1 => sqlerrm);
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 END;
861
862
863 -- if DML not passed then raise error
864 l_msg_count := FND_MSG_PUB.count_msg;
865 IF l_msg_count = 1 THEN
866 eam_execution_jsp.Get_Messages
867 (p_encoded => FND_API.G_FALSE,
868 p_msg_index => 1,
869 p_msg_count => l_msg_count,
870 p_msg_data => nvl(l_msg_data,FND_API.g_MISS_CHAR) ,
871 p_data => l_data,
872 p_msg_index_out => l_msg_index_out);
873 x_msg_count := l_msg_count;
874 x_msg_data := l_msg_data;
875 ELSE
876 x_msg_count := l_msg_count;
877 END IF;
878
879 IF l_msg_count > 0 THEN
880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
881 RAISE FND_API.G_EXC_ERROR;
882 END IF;
883
884
885 IF FND_API.TO_BOOLEAN(P_COMMIT)
886 THEN
887 COMMIT WORK;
888 END IF;
889
890 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
891 IF p_commit = FND_API.G_TRUE THEN
892 ROLLBACK TO complete_workorder;
893 END IF;
894
895 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'EAM_WORKORDERS_JSP.COMPLETE_WORKORDER',
896 p_procedure_name => EAM_DEBUG.G_err_stack);
897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
898 WHEN FND_API.G_EXC_ERROR THEN
899 IF p_commit = FND_API.G_TRUE THEN
900 ROLLBACK TO complete_workorder;
901 END IF;
902
903 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'EAM_WORKORDERS_JSP.COMPLETE_WORKORDER',
904 p_procedure_name => EAM_DEBUG.G_err_stack);
905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
906 WHEN OTHERS THEN
907 IF p_commit = FND_API.G_TRUE THEN
908 ROLLBACK TO complete_workorder;
909 END IF;
910
911 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'EAM_WORKORDERS_JSP.COMPLETE_WORKORDER',
912 p_procedure_name => EAM_DEBUG.G_err_stack);
913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
914 END Complete_Workorder;
915
916
917
918 -------------------------------------------------------------------------------
919 -- Creating easy work order
920 -- insert row into wip_discrete_jobs, wip_entities
921 -- create a default operation 10 for the new work order
922 -- release the work order and call wip_change_status.release
923
924 --anjgupta Changes for IB and Transactable Assets Project in R12
925 -------------------------------------------------------------------------------
926 procedure create_ez_work_order
927 ( p_api_version IN NUMBER := 1.0
928 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
929 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
930 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
931 ,p_record_version_number IN NUMBER := NULL
932 ,x_return_status OUT NOCOPY VARCHAR2
933 ,x_msg_count OUT NOCOPY NUMBER
934 ,x_msg_data OUT NOCOPY VARCHAR2
935 ,p_organization_id IN NUMBER
936 ,p_asset_number IN VARCHAR2
937 ,p_asset_group IN VARCHAR2
938 ,p_work_order_type IN NUMBER -- data
939 ,p_description IN VARCHAR2
940 ,p_activity_type IN NUMBER
941 ,p_activity_cause IN NUMBER
942 ,p_scheduled_start_date IN DATE
943 ,p_scheduled_completion_date IN DATE
944 ,p_owning_department IN VARCHAR2
945 ,p_priority IN NUMBER
946 ,p_request_type IN NUMBER := 1
947 ,p_work_request_number IN VARCHAR2
948 ,p_work_request_id IN NUMBER
949 ,x_new_work_order_name OUT NOCOPY VARCHAR2
950 ,x_new_work_order_id OUT NOCOPY NUMBER
951 ,p_asset_activity IN VARCHAR2
952 ,p_project_number IN VARCHAR2
953 ,p_task_number IN VARCHAR2
954 ,p_service_request_number IN VARCHAR2
955 ,p_service_request_id IN NUMBER
956 ,p_material_issue_by_mo IN VARCHAR2
957 ,p_status_type IN NUMBER
958 ,p_mode IN NUMBER
959 ,p_wip_entity_name IN VARCHAR2
960 ,p_user_id IN NUMBER
961 ,p_responsibility_id IN NUMBER
962 ,p_firm IN VARCHAR2
963 ,p_activity_source IN NUMBER
964 ,p_shutdown_type IN NUMBER
965 ,p_parent_work_order IN VARCHAR2 DEFAULT NULL
966 ,p_sched_parent_wip_entity_id IN VARCHAR2 DEFAULT NULL
967 ,p_relationship_type IN VARCHAR2 DEFAULT NULL
968 , p_attribute_category IN VARCHAR2 DEFAULT NULL
969 , p_attribute1 IN VARCHAR2 DEFAULT NULL
970 , p_attribute2 IN VARCHAR2 DEFAULT NULL
971 , p_attribute3 IN VARCHAR2 DEFAULT NULL
972 , p_attribute4 IN VARCHAR2 DEFAULT NULL
973 , p_attribute5 IN VARCHAR2 DEFAULT NULL
974 , p_attribute6 IN VARCHAR2 DEFAULT NULL
975 , p_attribute7 IN VARCHAR2 DEFAULT NULL
976 , p_attribute8 IN VARCHAR2 DEFAULT NULL
977 , p_attribute9 IN VARCHAR2 DEFAULT NULL
978 , p_attribute10 IN VARCHAR2 DEFAULT NULL
979 , p_attribute11 IN VARCHAR2 DEFAULT NULL
980 , p_attribute12 IN VARCHAR2 DEFAULT NULL
981 , p_attribute13 IN VARCHAR2 DEFAULT NULL
982 , p_attribute14 IN VARCHAR2 DEFAULT NULL
983 , p_attribute15 IN VARCHAR2 DEFAULT NULL
984 , p_failure_id IN NUMBER DEFAULT NULL
985 , p_failure_date IN DATE DEFAULT NULL
986 , p_failure_entry_id IN NUMBER DEFAULT NULL
987 , p_failure_code IN VARCHAR2 DEFAULT NULL
988 , p_cause_code IN VARCHAR2 DEFAULT NULL
989 , p_resolution_code IN VARCHAR2 DEFAULT NULL
990 , p_failure_comments IN VARCHAR2 DEFAULT NULL
991 , p_failure_code_required IN VARCHAR2 DEFAULT NULL
992 ) IS
993
994 l_api_name CONSTANT VARCHAR(30) := 'create_easy_work_order';
995 l_api_version CONSTANT NUMBER := 1.0;
996 l_return_status VARCHAR2(250);
997 l_error_msg_code VARCHAR2(250);
998 l_msg_count NUMBER;
999 l_msg_data VARCHAR2(250);
1000 l_err_code VARCHAR2(250);
1001 l_err_stage VARCHAR2(250);
1002 l_err_stack VARCHAR2(250);
1003 l_data VARCHAR2(250);
1004 l_msg_index_out NUMBER;
1005
1006 l_dept_id NUMBER;
1007 l_asset_group_id NUMBER;
1008 l_parent_wip_entity_id NUMBER;
1009 l_prefix VARCHAR2(30);
1010 l_eam_class VARCHAR2(10);
1011 l_asset_class VARCHAR2(10);
1012 l_auto_firm VARCHAR2(10);
1013
1014 l_row wip_discrete_jobs%ROWTYPE;
1015 l_entity wip_entities%ROWTYPE;
1016 l_op wip_operations%ROWTYPE;
1017
1018 l_rowid VARCHAR2(250);
1019 l_we_rowid VARCHAR2(250);
1020 l_routing_exists VARCHAR2(250);
1021
1022 l_work_request_id NUMBER;
1023 l_work_request_number VARCHAR2(250);
1024 l_work_request_wip_entity_id NUMBER;
1025 l_work_request_status NUMBER;
1026 l_count NUMBER;
1027 l_min_acct_period_date DATE;
1028 l_max_acct_period_date DATE;
1029
1030 -- baroy -- variables added for the 'Create EZ WO from asset activity project'
1031 -- Project Bug#2523149
1032 l_asset_activity_id NUMBER;
1033 l_explode_msg_count NUMBER;
1034 l_explode_msg_data VARCHAR2(250);
1035 l_explode_ret_stat VARCHAR2(250);
1036 l_project_id NUMBER := null;
1037 l_task_id NUMBER := null;
1038 l_class_code VARCHAR2(30);
1039 l_wdi_default_class VARCHAR2(10) := '';
1040 l_wdi_lot_number_def NUMBER;
1041 l_wdi_wip_param_ct NUMBER;
1042 l_wdi_acct_class_flag NUMBER := 0;
1043 l_wdi_disable_date DATE;
1044 l_wdi_default_ma NUMBER;
1045 l_wdi_default_mva NUMBER;
1046 l_wdi_default_moa NUMBER;
1047 l_wdi_default_ra NUMBER;
1048 l_wdi_default_rva NUMBER;
1049 l_wdi_default_opa NUMBER;
1050 l_wdi_default_opva NUMBER;
1051 l_wdi_default_oa NUMBER;
1052 l_wdi_default_ova NUMBER;
1053 l_wdi_default_scaa NUMBER;
1054 l_wdi_org_locator_control NUMBER;
1055 l_wdi_demand_class_mp VARCHAR2(30);
1056 l_wdi_mp_calendar_code VARCHAR2(10);
1057 l_wdi_mp_exception_set_id NUMBER;
1058 l_wdi_project_ref NUMBER;
1059 l_wdi_project_control NUMBER;
1060 l_wdi_pm_cost_collection NUMBER;
1061 l_wdi_primary_cost_method NUMBER;
1062 l_wdi_po_creation_time NUMBER;
1063 -- baroy
1064
1065
1066 -- Fields added for accounts (Bug 2217939)
1067 l_material_account NUMBER ;
1068 l_material_overhead_account NUMBER ;
1069 l_resource_account NUMBER ;
1070 l_outside_processing_account NUMBER ;
1071 l_material_variance_account NUMBER ;
1072 l_resource_variance_account NUMBER ;
1073 l_out_proc_var_account NUMBER ;
1074 l_std_cost_adjustment_account NUMBER ;
1075 l_overhead_account NUMBER ;
1076 l_overhead_variance_account NUMBER ;
1077
1078 -- sraval: local service request variables
1079 l_service_request_id NUMBER;
1080 l_service_request_number VARCHAR2(64);
1081 l_service_association_id NUMBER;
1082
1083 -- lllin: fields added for maintenance object id, type, and source
1084 l_maintenance_object_id number;
1085 l_maintenance_object_type number;
1086 l_maintenance_object_source number;
1087
1088 -- Fields added for new Work Order API
1089 l_workorder_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1090 l_workorder_rec1 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1091 l_workorder_rec2 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1092 l_workorder_rec3 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
1093 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1094 l_eam_op_tbl1 EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1095 l_eam_op_tbl2 EAM_PROCESS_WO_PUB.eam_op_tbl_type;
1096 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1097 l_eam_op_network_tbl1 EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1098 l_eam_op_network_tbl2 EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
1099 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1100 l_eam_res_tbl1 EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1101 l_eam_res_tbl2 EAM_PROCESS_WO_PUB.eam_res_tbl_type;
1102 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1103 l_eam_res_inst_tbl1 EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1104 l_eam_res_inst_tbl2 EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
1105 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1106 l_eam_sub_res_tbl1 EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1107 l_eam_sub_res_tbl2 EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
1108 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1109 l_eam_res_usage_tbl1 EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1110 l_eam_res_usage_tbl2 EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
1111 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1112 l_eam_mat_req_tbl1 EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1113 l_eam_mat_req_tbl2 EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
1114 l_wip_entity_id NUMBER;
1115 --Bug3592712: Max length of workorder name is 240 char.
1116 l_wip_entity_name VARCHAR2(240);
1117
1118
1119 l_eam_item_type NUMBER;
1120 l_user_defined_status_type NUMBER;
1121 l_status_type NUMBER;
1122
1123 l_wip_entity_updt NUMBER;
1124
1125 l_mode NUMBER; -- 0 for Create and 1 for Update
1126 l_date_released DATE;
1127 l_user_id NUMBER;
1128 l_responsibility_id NUMBER;
1129 l_firm NUMBER;
1130 l_serial_number_control NUMBER := 0;
1131 l_work_name VARCHAR2(240);
1132 l_parent_work_order_count number:=0;
1133 l_adjust_parent varchar2(10);
1134 l_row_count number :=0;
1135 l_start_date Date;
1136 l_end_date Date;
1137 /* FA project */
1138 l_fail_dept_id NUMBER;
1139 l_eam_location_id NUMBER;
1140
1141
1142 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
1143 l_eam_wo_relations_tbl1 EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
1144 l_eam_wo_relations_tbl2 EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
1145 l_eam_wo_relations_rec1 EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
1146 l_eam_wo_relations_rec2 EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
1147 l_eam_wo_relations_rec3 EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
1148 l_eam_wo_relations_rec4 EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
1149
1150 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
1151 l_eam_wo_tbl1 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
1152 l_eam_wo_tbl2 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
1153 l_eam_wo_tbl3 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
1154
1155 l_eam_msg_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
1156 l_old_rebuild_source NUMBER;
1157 l_message_text varchar2(20);
1158
1159 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1160 l_eam_direct_items_tbl_1 EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
1161 wip_id NUMBER;
1162 manual_rebuild_flag varchar2(1);
1163 constraining_rel number;
1164 followup_rel number;
1165 record_count number :=1;
1166 l_orig_service_request_id number;
1167 l_orig_wo_status number;
1168 asset_status number;
1169 l_output_dir VARCHAR2(512);
1170 l_prev_activity_id NUMBER := null;
1171
1172 /* Added for bug#5284499 Start */
1173 l_prev_project_id NUMBER;
1174 l_prev_task_id NUMBER;
1175 /* Added for bug#5284499 End */
1176
1177 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
1178 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1179 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1180 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1181 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1182 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1183 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1184 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
1185 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
1186 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
1187 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
1188 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
1189 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
1190 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
1191 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1192 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
1193
1194 /* Added for FA */
1195 l_eam_failure_entry_record eam_process_failure_entry_pub.eam_failure_entry_record_typ ;
1196 l_eam_failure_codes_tbl eam_process_failure_entry_pub.eam_failure_codes_tbl_typ ;
1197 /* End of FA */
1198
1199
1200 -- Cursor to fetch system status for corresponding user defined status passed to API.
1201 CURSOR get_system_status IS
1202 SELECT system_status
1203 FROM EAM_WO_STATUSES_V
1204 WHERE status_id = p_status_type;
1205
1206 BEGIN
1207
1208 --Initialize the message count.This will make check_errors to look in the message stack and display all messages.
1209 x_msg_count := 0;
1210
1211 SAVEPOINT create_easy_work_order;
1212
1213 eam_debug.init_err_stack('eam_workorders_jsp.create_easy_work_order');
1214
1215 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1216 p_api_version,
1217 l_api_name,
1218 g_pkg_name)
1219 THEN
1220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1221 END IF;
1222
1223 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1224 THEN
1225 FND_MSG_PUB.initialize;
1226 END IF;
1227
1228 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
1229
1230
1231 x_return_status := FND_API.G_RET_STS_SUCCESS;
1232
1233 -- Assign user defined status passed to API to a local variable
1234 l_user_defined_status_type := p_status_type;
1235
1236 -- Fetch corresponding system status for user defined status passed to the API.
1237 OPEN get_system_status;
1238 FETCH get_system_status INTO l_status_type;
1239 CLOSE get_system_status;
1240
1241 l_mode := p_mode;
1242
1243
1244 /*cboppana-3245839..Aded this code to validate if an asset is deactivated
1245 we should not allow change of status of the workorder from cancelled
1246 to any other status other than closed*/
1247 if(l_mode=1) then
1248 begin
1249
1250 select wdj.status_type
1251 into l_orig_wo_status
1252 from wip_discrete_jobs wdj,wip_entities we
1253 where we.wip_entity_id=wdj.wip_entity_id
1254 and we.organization_id=p_organization_id
1255 and wdj.organization_id=p_organization_id
1256 and we.wip_entity_name=p_wip_entity_name;
1257
1258 if(p_asset_number is not null) then
1259 select nvl(cii.active_start_date, sysdate-1),
1260 nvl(cii.active_start_date, sysdate-1),msikfv.eam_item_type
1261 into l_start_date, l_end_date, l_eam_item_type
1262 from csi_item_instances cii, mtl_system_items_b_kfv msikfv, mtl_parameters mp
1263 where cii.inventory_item_id =msikfv.inventory_item_id
1264 and cii.last_vld_organization_id =msikfv.organization_id --Bug 2157979
1265 and cii.last_vld_organization_id = mp.organization_id
1266 and mp.maint_organization_id = p_organization_id
1267 and cii.serial_number = p_asset_number
1268 and msikfv.CONCATENATED_SEGMENTS = nvl(p_asset_group, msikfv.CONCATENATED_SEGMENTS)
1269 and rownum = 1;
1270
1271 if (l_start_date <= sysdate and l_end_date >= sysdate) then
1272 asset_status := 3; --active
1273 else
1274 asset_status := 4; --inactive
1275 end if;
1276
1277 if((l_eam_item_type=1) and (l_orig_wo_status=7) and (l_status_type in (1,3,6,17)) and (asset_status<>3)) then
1278 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_DEACTIVATE_CANNOT_UNCANCEL',
1279 p_token1 => 'NAME', p_value1 => p_wip_entity_name);
1280 x_return_status := FND_API.G_RET_STS_ERROR;
1281 return;
1282 end if;
1283 end if;
1284 exception
1285 when others then
1286 null;
1287 end;
1288 end if;
1289
1290
1291 -- get asset group id and other info needed
1292 BEGIN
1293
1294 if (p_asset_number is not null) then
1295 select cii.inventory_item_id , eomd.ACCOUNTING_CLASS_CODE,
1296 cii.instance_id, msikfv.eam_item_type , eomd.area_id
1297 into l_asset_group_id, l_asset_class, l_maintenance_object_id, l_eam_item_type , l_eam_location_id
1298 from csi_item_instances cii, mtl_system_items_b_kfv msikfv,
1299 mtl_parameters mp, eam_org_maint_defaults eomd
1300 where cii.inventory_item_id =msikfv.inventory_item_id
1301 and cii.last_vld_organization_id =msikfv.organization_id --Bug 2157979
1302 and cii.last_vld_organization_id = mp.organization_id
1303 and mp.maint_organization_id = p_organization_id
1304 and cii.serial_number = p_asset_number
1305 and msikfv.CONCATENATED_SEGMENTS = nvl(p_asset_group, msikfv.CONCATENATED_SEGMENTS)
1306 and eomd.object_type (+) = 50
1307 and eomd.object_id (+) = cii.instance_id
1308 and eomd.organization_id (+) = p_organization_id
1309 and rownum = 1;
1310
1311 l_maintenance_object_type:=3;
1312 else
1313
1314 begin
1315 -- Changes by amondal for New Work Order API
1316 select msikfv.inventory_item_id, msikfv.eam_item_type, msikfv.serial_number_control_code
1317 into l_asset_group_id, l_eam_item_type, l_serial_number_control
1318 from mtl_system_items_b_kfv msikfv, mtl_parameters mp
1319 where msikfv.organization_id=mp.organization_id
1320 and mp.maint_organization_id = p_organization_id
1321 and msikfv.CONCATENATED_SEGMENTS = p_asset_group
1322 and rownum = 1;
1323
1324 l_maintenance_object_id:=l_asset_group_id;
1325 l_maintenance_object_type:=2;
1326 exception
1327 when others then
1328 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_ASSET_BAD');
1329 x_return_status := FND_API.G_RET_STS_ERROR;
1330 end;
1331 end if;
1332
1333 l_maintenance_object_source:=1;
1334
1335 EXCEPTION WHEN OTHERS THEN
1336 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_ASSET_BAD');
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 END;
1339
1340 -- get owning department id
1341 --fix for 3396024.
1342
1343 BEGIN
1344 IF(p_owning_department IS NOT NULL) THEN
1345 select department_id
1346 into l_dept_id
1347 from bom_departments
1348 where organization_id = p_organization_id
1349 and department_code = p_owning_department;
1350 END IF;
1351 EXCEPTION WHEN OTHERS THEN
1352 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_DEPT_BAD');
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354 END;
1355
1356 --if it is a work request
1357 if(p_request_type=1) then
1358 -- validate work request
1359 BEGIN
1360 l_work_request_id := null;
1361 l_work_request_number := null;
1362 -- if only number is provided
1363 if( p_work_request_id is not null) then
1364 l_work_request_id := p_work_request_id;
1365 elsif (p_work_request_number is not null) then
1366 l_work_request_number := p_work_request_number;
1367 select count(*), avg(r.work_request_id)
1368 into l_count, l_work_request_id
1369 from wip_eam_work_requests r
1370 where r.work_request_number = p_work_request_number;
1371
1372 -- unique
1373 if(l_count > 1) then
1374 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_WQ_NOTUNIQUE');
1375 x_return_status := FND_API.G_RET_STS_ERROR;
1376 elsif (l_count <1) then
1377 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_WQ_NOTFOUND');
1378 x_return_status := FND_API.G_RET_STS_ERROR;
1379 end if;
1380 end if;
1381
1382 -- check request status
1383 if( x_return_status = FND_API.G_RET_STS_SUCCESS and l_work_request_id is not null) then
1384 select r.work_request_number, r.wip_entity_id, r.work_request_status_id
1385 into l_work_request_number, l_work_request_wip_entity_id, l_work_request_status
1386 from wip_eam_work_requests r
1387 where r.work_request_id = l_work_request_id for update;
1388
1389 if(l_work_request_number <> p_work_request_number) then
1390 -- should not happen
1391 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_WQ_CONFLICT');
1392 x_return_status := FND_API.G_RET_STS_ERROR;
1393 end if;
1394 if(l_work_request_wip_entity_id is not null) then
1395 -- has work order on work request
1396 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_WQ_WO_EXIST');
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 end if;
1399 if(l_work_request_status <> 3) then
1400 -- has work order on work request
1401 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_WQ_STATUS_WRONG');
1402 x_return_status := FND_API.G_RET_STS_ERROR;
1403 end if;
1404 end if;
1405 EXCEPTION WHEN OTHERS THEN
1406 -- only occur if work request not exist, should not happen
1407 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_WQID_NOTFOUND');
1408 x_return_status := FND_API.G_RET_STS_ERROR;
1409 END;
1410 end if;
1411
1412 --start of fix for 3396024. removed the validations and added code to fetch only the parent_wip_entity_id
1413 if(p_parent_work_order is not null) then
1414 begin
1415 SELECT wip_entity_id
1416 INTO l_parent_wip_entity_id
1417 FROM WIP_ENTITIES
1418 WHERE wip_entity_name=p_parent_work_order
1419 AND organization_id=p_organization_id;
1420 exception
1421 when others then
1422 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_PARENT_WO_BAD');
1423 x_return_status := FND_API.G_RET_STS_ERROR;
1424 end;
1425 end if;
1426 --end of fix for 3396024
1427
1428 -- Validate Asset Activity
1429 BEGIN
1430 if p_asset_activity is not null then
1431 -- See whether it is a valid asset activity
1432
1433 select inventory_item_id into l_asset_activity_id
1434 from mtl_system_items_b_kfv
1435 where concatenated_segments = p_asset_activity
1436 and eam_item_type = 2
1437 and organization_id = p_organization_id;
1438 end if;
1439
1440 EXCEPTION WHEN OTHERS THEN
1441 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_GENERIC_ERROR',
1442 p_token1 => 'EAM_ERROR', p_value1 => sqlerrm);
1443 x_return_status := FND_API.G_RET_STS_ERROR;
1444 END;
1445
1446
1447
1448 if(p_request_type = 2) then
1449 -- sraval: Validate Service Request
1450 BEGIN
1451 l_service_request_number := null;
1452 l_service_request_id := null;
1453
1454 -- if user enters service request direcly without selecting from LOV
1455 if (p_service_request_id is null and p_service_request_number is not null) then
1456 select incident_id
1457 into l_service_request_id
1458 from cs_incidents_all_b
1459 where incident_number = p_service_request_number;
1460
1461 elsif (p_service_request_id is not null) then
1462 l_service_request_id := p_service_request_id;
1463
1464 end if;
1465 EXCEPTION WHEN OTHERS THEN
1466 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_BAD_SERVICE_REQUEST');
1467 x_return_status := FND_API.G_RET_STS_ERROR;
1468 END;
1469 end if;
1470
1471 -- Project and task validation
1472 BEGIN
1473
1474 if p_project_number is null and p_task_number is not null then
1475 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_PROJECT_REQUIRED');
1476 x_return_status := FND_API.G_RET_STS_ERROR;
1477 end if;
1478
1479 -- set profile values to be used by PJM Views. Bug#4384541
1480 fnd_profile.put('MFG_ORGANIZATION_ID',p_organization_id );
1481
1482 if p_project_number is not null and p_task_number is null then
1483 select count(*) into l_count
1484 from pjm_projects_v ppv,
1485 pjm_project_parameters ppp
1486 where ppv.project_id = ppp.project_id
1487 and ppp.organization_id = p_organization_id
1488 and ppv.project_number = p_project_number
1489 and rownum <= 1;
1490 if l_count <> 1 then
1491 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_BAD_PROJECT');
1492 x_return_status := FND_API.G_RET_STS_ERROR;
1493 else
1494 select ppv.project_id into l_project_id
1495 from pjm_projects_v ppv,
1496 pjm_project_parameters ppp
1497 where ppv.project_id = ppp.project_id
1498 and ppp.organization_id = p_organization_id
1499 and ppv.project_number = p_project_number;
1500 end if;
1501 end if;
1502
1503 /* Bug # 4862404 : Removed the validation for project_id and task_id as we
1504 calling WO API */
1505
1506 EXCEPTION WHEN OTHERS THEN
1507 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_GENERIC_ERROR',
1508 p_token1 => 'EAM_ERROR', p_value1 => SQLERRM);
1509 x_return_status := FND_API.G_RET_STS_ERROR;
1510 END;
1511 -- Check Validation Errors
1512
1513 -- if validate not passed then raise error
1514 l_msg_count := FND_MSG_PUB.count_msg;
1515 IF l_msg_count = 1 THEN
1516 eam_execution_jsp.Get_Messages
1517 (p_encoded => FND_API.G_FALSE,
1518 p_msg_index => 1,
1519 p_msg_count => l_msg_count,
1520 p_msg_data => nvl(l_msg_data,FND_API.g_MISS_CHAR) ,
1521 p_data => l_data,
1522 p_msg_index_out => l_msg_index_out);
1523 x_msg_count := l_msg_count;
1524 x_msg_data := l_msg_data;
1525 ELSE
1526 x_msg_count := l_msg_count;
1527 END IF;
1528
1529 IF l_msg_count > 0 THEN
1530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1531 RAISE FND_API.G_EXC_ERROR;
1532 END IF;
1533
1534 -- End of check for Validation Errors
1535
1536 -- Code Added for Status Type
1537 BEGIN
1538
1539 l_mode := p_mode;
1540 l_wip_entity_name := p_wip_entity_name;
1541 -- l_user_id := p_user_id;
1542 -- l_responsibility_id := p_responsibility_id;
1543 l_user_id := fnd_global.user_id;
1544 l_responsibility_id := fnd_global.resp_id;
1545
1546 if p_firm = '1' then
1547 l_firm := to_number(p_firm);
1548 else
1549 l_firm := 2;
1550 end if;
1551
1552 /* Start of FA project code */
1553 /* Commented this code as this is not the right place to capture Failure Information.
1554 Added the code accordingtly while creating easy work orders
1555
1556 l_fail_dept_id := l_dept_id ;
1557
1558 IF(p_failure_code_required IS NOT NULL) THEN
1559 l_eam_failure_entry_record.failure_id := p_failure_id;
1560 l_eam_failure_entry_record.failure_date := p_failure_date;
1561 l_eam_failure_codes_tbl(1).failure_id := p_failure_id;
1562 l_eam_failure_codes_tbl(1).failure_entry_id := p_failure_entry_id;
1563 l_eam_failure_codes_tbl(1).failure_code := p_failure_code;
1564 l_eam_failure_codes_tbl(1).cause_code := p_cause_code;
1565 l_eam_failure_codes_tbl(1).resolution_code := p_resolution_code;
1566 l_eam_failure_codes_tbl(1).comments := p_failure_comments;
1567
1568 IF(l_mode = 1) THEN -- if updating work order
1569 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
1570 l_eam_failure_entry_record.source_type := 1;
1571 l_eam_failure_entry_record.source_id := l_workorder_rec.wip_entity_id;
1572 l_eam_failure_entry_record.object_type := l_workorder_rec.maintenance_object_type;
1573 l_eam_failure_entry_record.object_id := l_workorder_rec.maintenance_object_id;
1574 l_eam_failure_entry_record.maint_organization_id := l_workorder_rec.organization_id;
1575 l_eam_failure_entry_record.current_organization_id := l_workorder_rec.organization_id;
1576 l_eam_failure_entry_record.department_id := l_fail_dept_id;
1577 l_eam_failure_entry_record.area_id := l_eam_location_id;
1578
1579 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
1580
1581 IF (l_eam_failure_entry_record.failure_date IS NULL) THEN
1582 l_eam_failure_entry_record.failure_date := FND_API.G_MISS_DATE;
1583 END IF;
1584
1585 IF (l_eam_failure_codes_tbl(1).failure_code IS NULL) THEN
1586 l_eam_failure_codes_tbl(1).failure_code := FND_API.G_MISS_CHAR;
1587 END IF;
1588
1589 IF (l_eam_failure_codes_tbl(1).cause_code IS NULL) THEN
1590 l_eam_failure_codes_tbl(1).cause_code := FND_API.G_MISS_CHAR;
1591 END IF;
1592
1593 IF (l_eam_failure_codes_tbl(1).resolution_code IS NULL) THEN
1594 l_eam_failure_codes_tbl(1).resolution_code := FND_API.G_MISS_CHAR;
1595 END IF;
1596
1597 IF (l_eam_failure_codes_tbl(1).comments IS NULL) THEN
1598 l_eam_failure_codes_tbl(1).comments := FND_API.G_MISS_CHAR;
1599 END IF;
1600
1601 IF (l_eam_failure_entry_record.failure_id IS NOT NULL ) THEN
1602 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
1603 ELSE
1604 IF (l_eam_failure_entry_record.failure_date = FND_API.G_MISS_DATE) THEN
1605 l_eam_failure_entry_record.transaction_type := NULL;
1606 ELSE
1607 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE ;
1608 END IF;
1609 END IF;
1610
1611 IF (l_eam_failure_codes_tbl(1).failure_entry_id IS NOT NULL) THEN
1612 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
1613 l_workorder_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
1614 ELSE
1615 IF ( NOT ( (l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR)
1616 AND (l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR)
1617 AND (l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR)
1618 AND (l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR)
1619 ) ) THEN
1620 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
1621 IF (l_eam_failure_codes_tbl(1).failure_code = FND_API.G_MISS_CHAR) THEN
1622 l_eam_failure_codes_tbl(1).failure_code := NULL;
1623 END IF;
1624 IF (l_eam_failure_codes_tbl(1).cause_code = FND_API.G_MISS_CHAR) THEN
1625 l_eam_failure_codes_tbl(1).cause_code := NULL;
1626 END IF;
1627 IF (l_eam_failure_codes_tbl(1).resolution_code = FND_API.G_MISS_CHAR) THEN
1628 l_eam_failure_codes_tbl(1).resolution_code := NULL;
1629 END IF;
1630 IF (l_eam_failure_codes_tbl(1).comments = FND_API.G_MISS_CHAR) THEN
1631 l_eam_failure_codes_tbl(1).comments := NULL;
1632 END IF;
1633
1634 l_workorder_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
1635 ELSE
1636 l_eam_failure_codes_tbl.delete;
1637 l_workorder_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
1638 END IF;
1639 END IF;
1640 l_workorder_rec.eam_failure_entry_record := l_eam_failure_entry_record;
1641 ELSE -- work order is getting created
1642
1643 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
1644 l_eam_failure_entry_record.source_type := 1;
1645 l_eam_failure_entry_record.source_id := l_workorder_rec.wip_entity_id;
1646 l_eam_failure_entry_record.object_type := l_workorder_rec.maintenance_object_type;
1647 l_eam_failure_entry_record.object_id := l_workorder_rec.maintenance_object_id;
1648 l_eam_failure_entry_record.maint_organization_id := l_workorder_rec.organization_id;
1649 l_eam_failure_entry_record.current_organization_id := l_workorder_rec.organization_id;
1650 l_eam_failure_entry_record.department_id := l_fail_dept_id;
1651 l_eam_failure_entry_record.area_id := l_eam_location_id;
1652
1653 IF (l_eam_failure_entry_record.failure_date IS NULL) THEN
1654 l_eam_failure_entry_record.transaction_type := NULL;
1655 END IF;
1656 l_workorder_rec.eam_failure_entry_record := l_eam_failure_entry_record;
1657
1658 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
1659 IF ( NOT ( l_eam_failure_codes_tbl(1).failure_code IS NULL
1660 AND l_eam_failure_codes_tbl(1).cause_code IS NULL
1661 AND l_eam_failure_codes_tbl(1).resolution_code IS NULL
1662 AND l_eam_failure_codes_tbl(1).comments IS NULL)
1663 ) THEN
1664 l_workorder_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
1665 ELSE
1666 l_eam_failure_codes_tbl.delete;
1667 l_workorder_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
1668 END IF;
1669 END IF; -- end of work order mode check
1670
1671 END IF; --end of check for failure data passed
1672 */
1673 /* End of FA code */
1674
1675
1676 if (l_mode = 1) then -- Update of Work Order API
1677
1678 begin
1679 /*Bug#4425025 - have date_released as null if its null to enable defaulting in EAM_WO_DEFAULT_PVT*/
1680
1681 /* select we.wip_entity_id, nvl(wdj.date_released,sysdate),wdj.parent_wip_entity_id,wdj.manual_rebuild_flag */
1682 select we.wip_entity_id,wdj.date_released,wdj.parent_wip_entity_id,wdj.manual_rebuild_flag
1683 into l_wip_entity_updt, l_date_released,l_old_rebuild_source,manual_rebuild_flag
1684 from wip_entities we, wip_discrete_jobs wdj
1685 where we.wip_entity_name = l_wip_entity_name
1686 and we.organization_id = p_organization_id
1687 and we.organization_id = wdj.organization_id
1688 and we.wip_entity_id = wdj.wip_entity_id;
1689
1690 EXCEPTION WHEN OTHERS THEN
1691 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_GENERIC_ERROR',
1692 p_token1 => 'EAM_ERROR', p_value1 => SQLERRM);
1693 x_return_status := FND_API.G_RET_STS_ERROR;
1694 end;
1695
1696
1697 BEGIN
1698 -- Start of Call to Work Order PL/SQL API
1699
1700 l_workorder_rec.header_id := 1;
1701 l_workorder_rec.batch_id := 1;
1702 l_workorder_rec.return_status := null;
1703 l_workorder_rec.wip_entity_name := l_wip_entity_name;
1704 l_workorder_rec.wip_entity_id := l_wip_entity_updt;
1705 l_workorder_rec.organization_id := p_organization_id;
1706 l_workorder_rec.description := p_description;
1707
1708 if (l_eam_item_type <>3) then
1709 l_workorder_rec.asset_number := p_asset_number;
1710 l_workorder_rec.asset_group_id := l_asset_group_id;
1711 l_workorder_rec.rebuild_serial_number := null;
1712 l_workorder_rec.rebuild_item_id := null;
1713 else
1714 l_workorder_rec.rebuild_serial_number := p_asset_number;
1715 l_workorder_rec.rebuild_item_id := l_asset_group_id;
1716 if(l_parent_wip_entity_id is not null) then
1717 l_workorder_rec.parent_wip_entity_id := l_parent_wip_entity_id;
1718 end if;
1719 l_workorder_rec.asset_number := null;
1720 l_workorder_rec.asset_group_id := null;
1721
1722 end if;
1723
1724
1725 l_workorder_rec.firm_planned_flag := l_firm;
1726 -- Code change for Bug 3454269
1727 l_workorder_rec.requested_start_date := p_scheduled_start_date;
1728 -- Code change for Bug 3454269
1729 -- l_workorder_rec.due_date := p_scheduled_start_date;
1730 l_workorder_rec.owning_department := l_dept_id;
1731 l_workorder_rec.scheduled_start_date := p_scheduled_start_date;
1732 l_workorder_rec.scheduled_completion_date := p_scheduled_completion_date;
1733 l_workorder_rec.status_type := l_status_type;
1734 l_workorder_rec.user_defined_status_id := l_user_defined_status_type;
1735 l_workorder_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
1736 l_workorder_rec.maintenance_object_id := l_maintenance_object_id;
1737 l_workorder_rec.maintenance_object_type := l_maintenance_object_type;
1738 l_workorder_rec.maintenance_object_source := l_maintenance_object_source;
1739 l_workorder_rec.asset_activity_id := l_asset_activity_id;
1740 l_workorder_rec.attribute_category := p_attribute_category; --Flex field columns
1741 l_workorder_rec.attribute1 := p_attribute1;
1742 l_workorder_rec.attribute2 := p_attribute2;
1743 l_workorder_rec.attribute3 := p_attribute3;
1744 l_workorder_rec.attribute4 := p_attribute4;
1745 l_workorder_rec.attribute5 := p_attribute5;
1746 l_workorder_rec.attribute6 := p_attribute6;
1747 l_workorder_rec.attribute7 := p_attribute7;
1748 l_workorder_rec.attribute8 := p_attribute8;
1749 l_workorder_rec.attribute9 := p_attribute9;
1750 l_workorder_rec.attribute10 := p_attribute10;
1751 l_workorder_rec.attribute11 := p_attribute11;
1752 l_workorder_rec.attribute12 := p_attribute12;
1753 l_workorder_rec.attribute13 := p_attribute13;
1754 l_workorder_rec.attribute14 := p_attribute14;
1755 l_workorder_rec.attribute15 := p_attribute15;
1756
1757
1758 -- # 3436679 code added to prevent the defaulting of the asset activity if user removes it while updating work order
1759
1760 BEGIN
1761 select primary_item_id,project_id,task_id into l_prev_activity_id,l_prev_project_id,l_prev_task_id
1762 from wip_discrete_jobs
1763 where wip_entity_id = l_workorder_rec.wip_entity_id
1764 and organization_id = l_workorder_rec.organization_id;
1765
1766 EXCEPTION
1767 WHEN NO_DATA_FOUND THEN
1768 null;
1769 end;
1770
1771 IF l_prev_activity_id is not null and l_asset_activity_id is null THEN
1772 l_workorder_rec.asset_activity_id := FND_API.G_MISS_NUM;
1773 ELSE
1774 l_workorder_rec.asset_activity_id := l_asset_activity_id;
1775 END IF;
1776
1777 /* Added for bug#5346446 Start */
1778 IF l_prev_project_id is not null AND l_project_id is null THEN
1779 l_workorder_rec.project_id := FND_API.G_MISS_NUM;
1780 ELSE
1781 l_workorder_rec.project_id := l_project_id;
1782 END IF;
1783
1784 IF l_prev_task_id is not null AND l_task_id is null THEN
1785 l_workorder_rec.task_id := FND_API.G_MISS_NUM;
1786 ELSE
1787 l_workorder_rec.task_id := l_task_id;
1788 END IF;
1789 /* Added for bug#5346446 End */
1790
1791
1792 l_workorder_rec.activity_type := to_char(p_activity_type);
1793 l_workorder_rec.activity_cause := to_char(p_activity_cause);
1794 l_workorder_rec.activity_source := to_char(p_activity_source);
1795 l_workorder_rec.shutdown_type:=to_char(p_shutdown_type);
1796 l_workorder_rec.work_order_type := to_char(p_work_order_type);
1797 l_workorder_rec.priority := p_priority;
1798 l_workorder_rec.project_id := l_project_id;
1799 l_workorder_rec.task_id := l_task_id;
1800 l_workorder_rec.material_issue_by_mo := p_material_issue_by_mo;
1801
1802 -- Set user id and responsibility id so that we can set apps context
1803 -- before calling any concurrent program
1804 l_workorder_rec.user_id := l_user_id;
1805 l_workorder_rec.responsibility_id := l_responsibility_id;
1806
1807 if (l_status_type = 3) then -- Set Date Released to be sysdate, if you want to
1808 -- Release the work order now
1809 l_workorder_rec.date_released := l_date_released;
1810 end if;
1811
1812
1813 l_eam_wo_tbl(1) := l_workorder_rec;
1814
1815
1816 if((l_eam_item_type =3) and (manual_rebuild_flag='Y'))then
1817
1818 /*Delink child from the old rebuild source and attach to the new rebuild source */
1819
1820
1821 if(((l_old_rebuild_source is not null) and (l_parent_wip_entity_id is null))
1822 or ((l_old_rebuild_source is null ) and (l_parent_wip_entity_id is not null))
1823 or (l_old_rebuild_source <> l_parent_wip_entity_id)
1824 )then
1825
1826
1827 if(l_old_rebuild_source is not null) then
1828
1829 ----If constraining relationship exists with rebuild source delete it
1830 select count(*)
1831 into constraining_rel
1832 from eam_wo_relationships
1833 where parent_object_id=l_old_rebuild_source
1834 and child_object_id=l_wip_entity_updt
1835 and parent_relationship_type=1;
1836
1837 if(constraining_rel=1) then
1838 l_eam_wo_relations_rec1.batch_id := 1;
1839 l_eam_wo_relations_rec1.parent_object_id := l_old_rebuild_source;
1840 l_eam_wo_relations_rec1.parent_object_type_id := 1;
1841 l_eam_wo_relations_rec1.parent_header_id := l_old_rebuild_source;
1842 l_eam_wo_relations_rec1.child_object_type_id := 1;
1843 l_eam_wo_relations_rec1.child_header_id :=l_wip_entity_updt;
1844 l_eam_wo_relations_rec1.child_object_id :=l_wip_entity_updt;
1845 l_eam_wo_relations_rec1.parent_relationship_type := 1;
1846 l_eam_wo_relations_rec1.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
1847
1848 l_eam_wo_relations_tbl(record_count) := l_eam_wo_relations_rec1;
1849 record_count := record_count+1;
1850
1851 end if;
1852
1853
1854 ----If followup relationship exists with rebuild source delete it
1855
1856
1857 select count(*)
1858 into followup_rel
1859 from eam_wo_relationships
1860 where parent_object_id=l_old_rebuild_source
1861 and child_object_id=l_wip_entity_updt
1862 and parent_relationship_type=4;
1863
1864 if(followup_rel=1) then
1865
1866 l_eam_wo_relations_rec2.batch_id := 1;
1867 l_eam_wo_relations_rec2.parent_object_id := l_old_rebuild_source;
1868 l_eam_wo_relations_rec2.parent_object_type_id := 1;
1869 l_eam_wo_relations_rec2.parent_header_id := l_old_rebuild_source;
1870 l_eam_wo_relations_rec2.child_object_type_id := 1;
1871 l_eam_wo_relations_rec2.child_header_id :=l_wip_entity_updt;
1872 l_eam_wo_relations_rec2.child_object_id :=l_wip_entity_updt;
1873 l_eam_wo_relations_rec2.parent_relationship_type := 4;
1874 l_eam_wo_relations_rec2.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
1875
1876 l_eam_wo_relations_tbl(record_count) := l_eam_wo_relations_rec2;
1877 record_count := record_count+1;
1878
1879 end if;
1880
1881 end if;
1882
1883
1884 if(l_parent_wip_entity_id is not null) then
1885
1886 --create a constraining relationship with the new rebuild source
1887 l_eam_wo_relations_rec3.batch_id := 1;
1888 l_eam_wo_relations_rec3.parent_object_id := l_parent_wip_entity_id;
1889 l_eam_wo_relations_rec3.parent_object_type_id := 1;
1890 l_eam_wo_relations_rec3.parent_header_id := l_parent_wip_entity_id;
1891 l_eam_wo_relations_rec3.child_object_type_id := 1;
1892 l_eam_wo_relations_rec3.child_header_id :=l_wip_entity_updt;
1893 l_eam_wo_relations_rec3.child_object_id :=l_wip_entity_updt;
1894 l_eam_wo_relations_rec3.parent_relationship_type := 1;
1895 l_eam_wo_relations_rec3.adjust_parent := FND_API.G_FALSE;
1896 l_eam_wo_relations_rec3.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
1897
1898 l_eam_wo_relations_tbl(record_count) := l_eam_wo_relations_rec3;
1899 record_count := record_count+1;
1900
1901
1902 --create a followup relationship with the new rebuild source
1903
1904 l_eam_wo_relations_rec4.batch_id := 1;
1905 l_eam_wo_relations_rec4.parent_object_id := l_parent_wip_entity_id;
1906 l_eam_wo_relations_rec4.parent_object_type_id := 1;
1907 l_eam_wo_relations_rec4.parent_header_id := l_parent_wip_entity_id;
1908 l_eam_wo_relations_rec4.child_object_type_id := 1;
1909 l_eam_wo_relations_rec4.child_header_id :=l_wip_entity_updt;
1910 l_eam_wo_relations_rec4.child_object_id :=l_wip_entity_updt;
1911 l_eam_wo_relations_rec4.parent_relationship_type := 4;
1912 l_eam_wo_relations_rec4.adjust_parent := FND_API.G_FALSE;
1913 l_eam_wo_relations_rec4.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
1914
1915 l_eam_wo_relations_tbl(record_count) := l_eam_wo_relations_rec4;
1916 record_count := record_count+1;
1917
1918 end if; ---End of adding rel to new rebuild source
1919 end if;--End of delinking and attaching
1920 end if;
1921 EAM_PROCESS_WO_PUB.Process_Master_Child_WO
1922 ( p_bo_identifier => 'EAM'
1923 , p_init_msg_list => TRUE
1924 , p_api_version_number => 1.0
1925 , p_eam_wo_tbl => l_eam_wo_tbl
1926 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
1927 , p_eam_op_tbl => l_eam_op_tbl
1928 , p_eam_op_network_tbl => l_eam_op_network_tbl
1929 , p_eam_res_tbl => l_eam_res_tbl
1930 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
1931 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
1932 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
1933 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
1934 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
1935 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
1936 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
1937 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
1938 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
1939 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
1940 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
1941 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
1942 , p_eam_request_tbl => l_eam_request_tbl
1943 , x_eam_wo_tbl => l_eam_wo_tbl1
1944 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
1945 , x_eam_op_tbl => l_eam_op_tbl1
1946 , x_eam_op_network_tbl => l_eam_op_network_tbl1
1947 , x_eam_res_tbl => l_eam_res_tbl1
1948 , x_eam_res_inst_tbl => l_eam_res_inst_tbl1
1949 , x_eam_sub_res_tbl => l_eam_sub_res_tbl1
1950 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
1951 , x_eam_mat_req_tbl => l_eam_mat_req_tbl1
1952 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
1953 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
1954 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
1955 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
1956 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
1957 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
1958 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
1959 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
1960 , x_eam_request_tbl => l_out_eam_request_tbl
1961 , x_return_status => l_return_status
1962 , x_msg_count => l_msg_count
1963 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
1964 , p_debug_filename => 'updatewoss.log'
1965 , p_output_dir =>l_output_dir
1966 , p_commit => 'N'
1967 , p_debug_file_mode => 'w'
1968 );
1969
1970 l_workorder_rec1 := l_eam_wo_tbl1(1);
1971
1972 x_return_status := l_return_status;
1973 x_msg_count := l_msg_count;
1974
1975 /*End of update wo ***********/
1976 if(x_return_status<>'S') then
1977 ROLLBACK TO create_easy_work_order;
1978 RAISE FND_API.G_EXC_ERROR;
1979 end if;
1980
1981 EXCEPTION WHEN OTHERS THEN
1982 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_GENERIC_ERROR',
1983 p_token1 => 'EAM_ERROR', p_value1 => SQLERRM);
1984 x_return_status := FND_API.G_RET_STS_ERROR;
1985 END;
1986
1987
1988 BEGIN
1989 --if service request
1990 if(p_request_type=2) then
1991
1992 -- if service request is specified then insert into eam_wo_service_association
1993 if (l_service_request_id is not null) then
1994 select eam_wo_service_association_s.nextval
1995 into l_service_association_id
1996 from dual;
1997
1998 --Assign only one service request to a work order
1999
2000 select count(*)
2001 into l_row_count
2002 from eam_wo_service_association
2003 where wip_entity_id = l_wip_entity_updt
2004 and (enable_flag IS NULL OR enable_flag = 'Y'); -- Fix for 3773450
2005
2006 if(l_row_count=0) then
2007
2008 insert into eam_wo_service_association
2009 (
2010 wo_service_entity_assoc_id
2011 ,maintenance_organization_id
2012 ,wip_entity_id
2013 ,service_request_id
2014 ,last_update_date
2015 ,last_updated_by
2016 ,creation_date
2017 ,created_by
2018 ,last_update_login
2019 ,enable_flag -- Fix for Bug 3773450
2020 )
2021 values
2022 (
2023 l_service_association_id
2024 ,p_organization_id
2025 ,l_wip_entity_updt
2026 ,l_service_request_id
2027 ,sysdate
2028 ,FND_GLOBAL.user_id
2029 ,sysdate
2030 ,FND_GLOBAL.user_id
2031 ,FND_GLOBAL.LOGIN_ID
2032 ,'Y'
2033
2034 );
2035
2036 else
2037 if(l_row_count=1) then
2038 select service_request_id
2039 into l_orig_service_request_id
2040 from eam_wo_service_association
2041 where maintenance_organization_id=p_organization_id
2042 and wip_entity_id=l_wip_entity_updt
2043 and (enable_flag IS NULL OR enable_flag='Y'); -- Fix for Bug 3773450
2044
2045 if(l_orig_service_request_id<>l_service_request_id) then
2046 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_SERVICE_REQUEST_EXISTS');
2047 x_return_status := FND_API.G_RET_STS_ERROR;
2048 end if;
2049 end if;
2050
2051 end if;
2052 end if;
2053
2054 else
2055 if(p_request_type=1) then
2056 -- update work request if exist
2057 if( l_work_request_id is not null) then
2058 update wip_eam_work_requests r
2059 set r.work_request_status_id = 4
2060 , r.wip_entity_id = l_workorder_rec1.wip_entity_id
2061 , r.last_update_date = sysdate
2062 , r.last_updated_by = FND_GLOBAL.user_id
2063 where r.work_request_id = l_work_request_id;
2064 end if;
2065 end if;
2066 end if;
2067 EXCEPTION
2068 WHEN OTHERS THEN
2069 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_BAD_SERVICE_REQUEST');
2070 x_return_status := FND_API.G_RET_STS_ERROR;
2071
2072 END;
2073
2074 IF FND_API.TO_BOOLEAN(P_COMMIT)
2075 THEN
2076 COMMIT WORK;
2077 END IF;
2078
2079
2080 else -- End of Update Work Order, Start of Create Work Order
2081
2082
2083 -------------------------------------------------------------
2084 -- DML here
2085 --create new work order
2086
2087 BEGIN
2088 -- Start of Call to Work Order PL/SQL API
2089 /*cboppana-Changed for Work Order Linking project */
2090 l_workorder_rec.header_id := 1;
2091 l_workorder_rec.batch_id := 1;
2092 l_workorder_rec.return_status := null;
2093 l_workorder_rec.wip_entity_name := p_wip_entity_name;
2094 l_workorder_rec.wip_entity_id := null;
2095 l_workorder_rec.organization_id := p_organization_id;
2096 l_workorder_rec.description := p_description;
2097 l_workorder_rec.attribute_category := p_attribute_category; --Flex field columns
2098 l_workorder_rec.attribute1 := p_attribute1;
2099 l_workorder_rec.attribute2 := p_attribute2;
2100 l_workorder_rec.attribute3 := p_attribute3;
2101 l_workorder_rec.attribute4 := p_attribute4;
2102 l_workorder_rec.attribute5 := p_attribute5;
2103 l_workorder_rec.attribute6 := p_attribute6;
2104 l_workorder_rec.attribute7 := p_attribute7;
2105 l_workorder_rec.attribute8 := p_attribute8;
2106 l_workorder_rec.attribute9 := p_attribute9;
2107 l_workorder_rec.attribute10 := p_attribute10;
2108 l_workorder_rec.attribute11 := p_attribute11;
2109 l_workorder_rec.attribute12 := p_attribute12;
2110 l_workorder_rec.attribute13 := p_attribute13;
2111 l_workorder_rec.attribute14 := p_attribute14;
2112 l_workorder_rec.attribute15 := p_attribute15;
2113
2114
2115 if (l_eam_item_type <>3) then
2116 l_workorder_rec.asset_number := p_asset_number;
2117 l_workorder_rec.asset_group_id := l_asset_group_id;
2118 l_workorder_rec.rebuild_serial_number := null;
2119 l_workorder_rec.rebuild_item_id := null;
2120 else
2121 l_workorder_rec.rebuild_serial_number := p_asset_number;
2122 l_workorder_rec.rebuild_item_id := l_asset_group_id;
2123 if(l_parent_wip_entity_id is not null) then
2124 l_workorder_rec.parent_wip_entity_id := l_parent_wip_entity_id;
2125
2126 /*cboppana --Add this work order as a constraining child of the rebuild source */
2127 l_eam_wo_relations_rec1.batch_id := 1;
2128 l_eam_wo_relations_rec1.parent_object_id := l_parent_wip_entity_id;
2129 l_eam_wo_relations_rec1.parent_object_type_id := 1;
2130 l_eam_wo_relations_rec1.parent_header_id := l_parent_wip_entity_id;
2131 l_eam_wo_relations_rec1.child_object_type_id := 1;
2132 l_eam_wo_relations_rec1.child_header_id :=1;
2133 l_eam_wo_relations_rec1.child_object_id :=1;
2134 l_eam_wo_relations_rec1.parent_relationship_type := 1;
2135 l_eam_wo_relations_rec1.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
2136
2137 l_eam_wo_relations_rec2.batch_id := 1;
2138 l_eam_wo_relations_rec2.parent_object_id := l_parent_wip_entity_id;
2139 l_eam_wo_relations_rec2.parent_object_type_id := 1;
2140 l_eam_wo_relations_rec2.parent_header_id := l_parent_wip_entity_id;
2141 l_eam_wo_relations_rec2.child_object_type_id := 1;
2142 l_eam_wo_relations_rec2.child_header_id :=1;
2143 l_eam_wo_relations_rec2.child_object_id :=1;
2144 l_eam_wo_relations_rec2.parent_relationship_type := 4;
2145 l_eam_wo_relations_rec2.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
2146
2147 l_eam_wo_relations_tbl(1) := l_eam_wo_relations_rec1;
2148 l_eam_wo_relations_tbl(2) := l_eam_wo_relations_rec2;
2149 end if;
2150 --End of rebuild source
2151 l_workorder_rec.manual_rebuild_flag := 'Y';
2152 l_workorder_rec.asset_number := null;
2153 l_workorder_rec.asset_group_id := null;
2154 end if;
2155
2156 l_workorder_rec.job_quantity := 1;
2157 l_workorder_rec.requested_start_date := p_scheduled_start_date;
2158 l_workorder_rec.owning_department := l_dept_id;
2159 l_workorder_rec.firm_planned_flag := l_firm;
2160 l_workorder_rec.scheduled_start_date := p_scheduled_start_date;
2161 l_workorder_rec.scheduled_completion_date := p_scheduled_completion_date;
2162 l_workorder_rec.status_type := l_status_type;
2163 l_workorder_rec.user_defined_status_id := l_user_defined_status_type;
2164 l_workorder_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
2165 l_workorder_rec.wip_supply_type := wip_constants.based_on_bom;
2166 l_workorder_rec.maintenance_object_id := l_maintenance_object_id;
2167 l_workorder_rec.maintenance_object_type := l_maintenance_object_type;
2168 l_workorder_rec.maintenance_object_source := l_maintenance_object_source;
2169 l_workorder_rec.asset_activity_id := l_asset_activity_id;
2170 l_workorder_rec.activity_type := to_char(p_activity_type);
2171 l_workorder_rec.activity_cause := to_char(p_activity_cause);
2172 l_workorder_rec.activity_source := to_char(p_activity_source);
2173 l_workorder_rec.shutdown_type := to_char(p_shutdown_type);
2174 l_workorder_rec.work_order_type := to_char(p_work_order_type);
2175 l_workorder_rec.priority := p_priority;
2176 l_workorder_rec.project_id := l_project_id;
2177 l_workorder_rec.task_id := l_task_id;
2178 l_workorder_rec.material_issue_by_mo := p_material_issue_by_mo;
2179
2180 /*Bug#4425025 - have date_released as null to enable defaulting in EAM_WO_DEFAULT_PVT*/
2181 /* if (l_status_type = 3) then -- Set Date Released to be sysdate, if you want to
2182 -- Release the work order now
2183 l_workorder_rec.date_released := sysdate;
2184 end if;
2185 */
2186
2187 -- Set user id and responsibility id so that we can set apps context
2188 -- before calling any concurrent program
2189 l_workorder_rec.user_id := l_user_id;
2190 l_workorder_rec.responsibility_id := fnd_global.resp_id;
2191
2192 /* Added for bug #5453280 */
2193 IF (p_failure_code_required IS NOT NULL) THEN
2194
2195 l_eam_failure_entry_record.failure_id := p_failure_id;
2196 l_eam_failure_entry_record.failure_date := p_failure_date;
2197
2198 l_eam_failure_codes_tbl(1).failure_id := p_failure_id;
2199 l_eam_failure_codes_tbl(1).failure_entry_id := p_failure_entry_id;
2200 l_eam_failure_codes_tbl(1).failure_code := p_failure_code;
2201 l_eam_failure_codes_tbl(1).cause_code := p_cause_code;
2202 l_eam_failure_codes_tbl(1).resolution_code := p_resolution_code;
2203 l_eam_failure_codes_tbl(1).comments := p_failure_comments;
2204
2205 l_fail_dept_id := l_workorder_rec.owning_department;
2206
2207 l_workorder_rec.failure_code_required := p_failure_code_required;
2208
2209 l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
2210 l_eam_failure_entry_record.source_type := 1;
2211 l_eam_failure_entry_record.source_id := l_workorder_rec.wip_entity_id;
2212 l_eam_failure_entry_record.object_type := l_workorder_rec.maintenance_object_type;
2213 l_eam_failure_entry_record.object_id := l_workorder_rec.maintenance_object_id;
2214 l_eam_failure_entry_record.maint_organization_id := l_workorder_rec.organization_id;
2215 l_eam_failure_entry_record.current_organization_id := l_workorder_rec.organization_id;
2216 l_eam_failure_entry_record.department_id := l_fail_dept_id;
2217 l_eam_failure_entry_record.area_id := l_eam_location_id;
2218
2219 if(l_eam_failure_entry_record.failure_date is null) then
2220 l_eam_failure_entry_record.transaction_type :=null;
2221 end if;
2222 l_workorder_rec.eam_failure_entry_record := l_eam_failure_entry_record;
2223
2224 l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_CREATE;
2225 if( not( l_eam_failure_codes_tbl(1).failure_code is null
2226 and l_eam_failure_codes_tbl(1).cause_code is null
2227 and l_eam_failure_codes_tbl(1).resolution_code is null
2228 and l_eam_failure_codes_tbl(1).comments is null
2229 )
2230 ) then
2231 l_workorder_rec.eam_failure_codes_tbl(1) := l_eam_failure_codes_tbl(1);
2232 else
2233 l_eam_failure_codes_tbl.delete;
2234 l_workorder_rec.eam_failure_codes_tbl := l_eam_failure_codes_tbl;
2235 end if;
2236
2237 End if; -- end of check for failure code required
2238 /* End of change for bug #5453280 */
2239
2240 l_eam_wo_tbl(1) := l_workorder_rec;
2241
2242 EAM_PROCESS_WO_PUB.l_eam_wo_list.delete; --Added for bug#4563210
2243
2244 EAM_PROCESS_WO_PUB.Process_Master_Child_WO
2245 ( p_bo_identifier => 'EAM'
2246 , p_init_msg_list => TRUE
2247 , p_api_version_number => 1.0
2248 , p_eam_wo_tbl => l_eam_wo_tbl
2249 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
2250 , p_eam_op_tbl => l_eam_op_tbl
2251 , p_eam_op_network_tbl => l_eam_op_network_tbl
2252 , p_eam_res_tbl => l_eam_res_tbl
2253 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2254 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2255 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2256 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2257 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
2258 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
2259 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2260 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2261 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
2262 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
2263 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
2264 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
2265 , p_eam_request_tbl => l_eam_request_tbl
2266 , x_eam_wo_tbl => l_eam_wo_tbl1
2267 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
2268 , x_eam_op_tbl => l_eam_op_tbl1
2269 , x_eam_op_network_tbl => l_eam_op_network_tbl1
2270 , x_eam_res_tbl => l_eam_res_tbl1
2271 , x_eam_res_inst_tbl => l_eam_res_inst_tbl1
2272 , x_eam_sub_res_tbl => l_eam_sub_res_tbl1
2273 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
2274 , x_eam_mat_req_tbl => l_eam_mat_req_tbl1
2275 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
2276 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
2277 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
2278 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
2279 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
2280 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
2281 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
2282 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
2283 , x_eam_request_tbl => l_out_eam_request_tbl
2284 , x_return_status => l_return_status
2285 , x_msg_count => l_msg_count
2286 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
2287 , p_debug_filename => 'createwoss.log'
2288 , p_output_dir => l_output_dir
2289 , p_commit => 'N'
2290 , p_debug_file_mode => 'w'
2291 );
2292
2293 l_workorder_rec1 := l_eam_wo_tbl1(1);
2294
2295 x_return_status := l_return_status;
2296 -- End of Call to Work Order PL/SQL API
2297
2298 EXCEPTION WHEN OTHERS THEN
2299 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_GENERIC_ERROR',
2300 p_token1 => 'EAM_ERROR', p_value1 => SQLERRM);
2301 x_return_status := FND_API.G_RET_STS_ERROR;
2302 END;
2303
2304 x_return_status := l_return_status;
2305 x_msg_count := FND_MSG_PUB.count_msg;
2306
2307 if(x_return_status<>'S') then
2308 ROLLBACK TO create_easy_work_order;
2309 RAISE FND_API.G_EXC_ERROR;
2310 end if;
2311
2312 -- assign out parameters
2313 x_new_work_order_name := l_workorder_rec1.wip_entity_name;
2314 x_new_work_order_id := l_workorder_rec1.wip_entity_id;
2315
2316
2317
2318 BEGIN
2319 if(p_request_type=2) then
2320
2321 -- if service request is specified then insert into eam_wo_service_association
2322 if (l_service_request_id is not null) then
2323 select eam_wo_service_association_s.nextval
2324 into l_service_association_id
2325 from dual;
2326
2327 insert into eam_wo_service_association
2328 (
2329 wo_service_entity_assoc_id
2330 ,maintenance_organization_id
2331 ,wip_entity_id
2332 ,service_request_id
2333 ,last_update_date
2334 ,last_updated_by
2335 ,creation_date
2336 ,created_by
2337 ,last_update_login
2338 ,enable_flag -- Fix for Bug 3773450
2339 )
2340 values
2341 (
2342 l_service_association_id
2343 ,p_organization_id
2344 ,x_new_work_order_id
2345 ,l_service_request_id
2346 ,sysdate
2347 ,FND_GLOBAL.user_id
2348 ,sysdate
2349 ,FND_GLOBAL.user_id
2350 ,FND_GLOBAL.LOGIN_ID
2351 ,'Y' -- Fix for Bug 3773450
2352
2353 );
2354 end if;
2355 else
2356 if(p_request_type=1) then
2357 -- update work request if exist
2358 if( l_work_request_id is not null) then
2359 update wip_eam_work_requests r
2360 set r.work_request_status_id = 4
2361 , r.wip_entity_id = l_workorder_rec1.wip_entity_id
2362 , r.last_update_date = sysdate
2363 , r.last_updated_by = FND_GLOBAL.user_id
2364 where r.work_request_id = l_work_request_id;
2365 end if;
2366 end if;
2367 end if;
2368 EXCEPTION
2369 WHEN OTHERS THEN
2370 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_EZWO_BAD_SERVICE_REQUEST');
2371 x_return_status := FND_API.G_RET_STS_ERROR;
2372
2373 END;
2374
2375 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
2376 COMMIT WORK;
2377 END IF;
2378
2379
2380
2381 if(p_sched_parent_wip_entity_id is not null) then
2382
2383
2384 SAVEPOINT create_relationship;
2385
2386
2387 l_eam_wo_relations_rec3.batch_id := 1;
2388 l_eam_wo_relations_rec3.parent_object_id := p_sched_parent_wip_entity_id;
2389 l_eam_wo_relations_rec3.parent_object_type_id := 1;
2390 l_eam_wo_relations_rec3.parent_header_id := p_sched_parent_wip_entity_id;
2391 l_eam_wo_relations_rec3.child_object_type_id := 1;
2392 l_eam_wo_relations_rec3.child_header_id :=x_new_work_order_id;
2393 l_eam_wo_relations_rec3.child_object_id :=x_new_work_order_id;
2394 l_eam_wo_relations_rec3.parent_relationship_type := to_number(p_relationship_type);
2395 l_eam_wo_relations_rec3.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
2396
2397 l_eam_wo_relations_tbl2(1) := l_eam_wo_relations_rec3;
2398
2399
2400
2401 EAM_PROCESS_WO_PUB.Process_Master_Child_WO
2402 ( p_bo_identifier => 'EAM'
2403 , p_init_msg_list => TRUE
2404 , p_api_version_number => 1.0
2405 , p_eam_wo_tbl => l_eam_wo_tbl2
2406 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl2
2407 , p_eam_op_tbl => l_eam_op_tbl
2408 , p_eam_op_network_tbl => l_eam_op_network_tbl
2409 , p_eam_res_tbl => l_eam_res_tbl
2410 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2411 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2412 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2413 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2414 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
2415 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
2416 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2417 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2418 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
2419 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
2420 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
2421 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
2422 , p_eam_request_tbl => l_eam_request_tbl
2423 , x_eam_wo_tbl => l_eam_wo_tbl1
2424 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
2425 , x_eam_op_tbl => l_eam_op_tbl1
2426 , x_eam_op_network_tbl => l_eam_op_network_tbl1
2427 , x_eam_res_tbl => l_eam_res_tbl1
2428 , x_eam_res_inst_tbl => l_eam_res_inst_tbl1
2429 , x_eam_sub_res_tbl => l_eam_sub_res_tbl1
2430 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
2431 , x_eam_mat_req_tbl => l_eam_mat_req_tbl1
2432 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
2433 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
2434 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
2435 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
2436 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
2437 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
2438 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
2439 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
2440 , x_eam_request_tbl => l_out_eam_request_tbl
2441 , x_return_status => l_return_status
2442 , x_msg_count => l_msg_count
2443 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
2444 , p_debug_filename => 'createrelss.log'
2445 , p_output_dir => l_output_dir
2446 , p_commit => 'N'
2447 , p_debug_file_mode => 'w'
2448 );
2449
2450 x_return_status := l_return_status;
2451 x_msg_count := FND_MSG_PUB.count_msg;
2452
2453 if(x_return_status<>'S') then
2454 ROLLBACK TO create_relationship;
2455 RAISE FND_API.G_EXC_ERROR;
2456 end if;
2457
2458 end if;--End of creating relationship
2459
2460 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
2461 COMMIT WORK;
2462 END IF;
2463
2464
2465 end if; -- End of Create Work Order
2466
2467 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
2468 COMMIT WORK;
2469 END IF;
2470
2471 EXCEPTION WHEN OTHERS THEN -- all dml excpetion
2472 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_EXCEPTION'
2473 , p_token1 => 'TEXT', p_value1 => SQLERRM);
2474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2475
2476 l_msg_count := FND_MSG_PUB.count_msg;
2477 IF l_msg_count = 1 THEN
2478 eam_execution_jsp.Get_Messages
2479 (p_encoded => FND_API.G_FALSE,
2480 p_msg_index => 1,
2481 p_msg_count => l_msg_count,
2482 p_msg_data => nvl(l_msg_data,FND_API.g_MISS_CHAR) ,
2483 p_data => l_data,
2484 p_msg_index_out => l_msg_index_out);
2485 x_msg_count := l_msg_count;
2486 x_msg_data := l_msg_data;
2487 ELSE
2488 x_msg_count := l_msg_count;
2489 END IF;
2490 END; -- dml
2491
2492
2493 EXCEPTION
2494 WHEN
2495 FND_API.G_EXC_UNEXPECTED_ERROR THEN
2496 IF p_commit = FND_API.G_TRUE THEN
2497 ROLLBACK TO create_easy_work_order;
2498 END IF;
2499 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_workorders_jsp.CREATE_EASY_WORK_ORDER',
2500 p_procedure_name => EAM_DEBUG.G_err_stack);
2501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2502 WHEN FND_API.G_EXC_ERROR THEN
2503 IF p_commit = FND_API.G_TRUE THEN
2504 ROLLBACK TO create_easy_work_order;
2505 END IF;
2506
2507 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_workorders_jsp.CREATE_EASY_WORK_ORDER',
2508 p_procedure_name => EAM_DEBUG.G_err_stack);
2509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2510
2511 WHEN OTHERS THEN -- all dml excpetion
2512 IF p_commit = FND_API.G_TRUE THEN
2513 ROLLBACK TO create_easy_work_order;
2514 END IF;
2515
2516 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'eam_workorders_jsp.CREATE_EASY_WORK_ORDER',
2517 p_procedure_name => EAM_DEBUG.G_err_stack);
2518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2519
2520 end create_ez_work_order;
2521
2522
2523
2524
2525
2526 -----------------------------------------------------------------------------------
2527 -- update work order, not involved in changes that could invoke transaction
2528 -----------------------------------------------------------------------------------
2529 procedure update_work_order
2530 ( p_api_version IN NUMBER := 1.0
2531 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2532 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2533 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2534 ,p_record_version_number IN NUMBER := NULL
2535 ,x_return_status OUT NOCOPY VARCHAR2
2536 ,x_msg_count OUT NOCOPY NUMBER
2537 ,x_msg_data OUT NOCOPY VARCHAR2
2538 ,p_wip_entity_id IN NUMBER
2539 ,p_description IN VARCHAR2
2540 ,p_owning_department IN VARCHAR2
2541 ,p_priority IN NUMBER
2542 ,p_shutdown_type IN VARCHAR2
2543 ,p_activity_type IN VARCHAR2
2544 ,p_activity_cause IN VARCHAR2
2545 ,p_firm_planned_flag IN NUMBER
2546 ,p_notification_required IN VARCHAR2
2547 ,p_tagout_required IN VARCHAR2
2548 ,p_scheduled_start_date IN DATE
2549 ,p_stored_last_update_date IN DATE
2550 ) IS
2551 l_api_name CONSTANT VARCHAR(30) := 'update_work_order';
2552 l_api_version CONSTANT NUMBER := 1.0;
2553 l_return_status VARCHAR2(250);
2554 l_error_msg_code VARCHAR2(250);
2555 l_msg_count NUMBER;
2556 l_msg_data VARCHAR2(250);
2557 l_err_code VARCHAR2(250);
2558 l_err_stage VARCHAR2(250);
2559 l_err_stack VARCHAR2(250);
2560 l_data VARCHAR2(250);
2561 l_msg_index_out NUMBER;
2562 l_err_number NUMBER;
2563
2564 l_new_status NUMBER;
2565 l_db_status NUMBER;
2566 l_db_last_update_date DATE;
2567
2568 l_org_id NUMBER;
2569 l_dept_id NUMBER;
2570 l_shift NUMBER;
2571 l_duration NUMBER;
2572
2573 BEGIN
2574
2575 IF p_commit = FND_API.G_TRUE THEN
2576 SAVEPOINT complete_workorder;
2577 END IF;
2578
2579 eam_debug.init_err_stack('eam_workorders_jsp.update_work_order');
2580
2581 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2582 p_api_version,
2583 l_api_name,
2584 g_pkg_name)
2585 THEN
2586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2587 END IF;
2588
2589 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2590 THEN
2591 FND_MSG_PUB.initialize;
2592 END IF;
2593
2594 x_return_status := FND_API.G_RET_STS_SUCCESS;
2595
2596 -- check if data is stale or not
2597 -- using last_update_date as indicator
2598 BEGIN
2599 SELECT last_update_date, status_type, organization_id
2600 , scheduled_completion_date - scheduled_start_date
2601 , p_scheduled_start_date - scheduled_start_date
2602 INTO l_db_last_update_date, l_db_status , l_org_id , l_duration , l_shift
2603 FROM wip_discrete_jobs
2604 WHERE wip_entity_id = p_wip_entity_id
2605 FOR UPDATE;
2606
2607 IF l_db_last_update_date <> p_stored_last_update_date THEN
2608 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_STALED_DATA');
2609 x_return_status := FND_API.G_RET_STS_ERROR;
2610 END IF;
2611 EXCEPTION WHEN OTHERS THEN
2612 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_NOT_FOUND');
2613 x_return_status := FND_API.G_RET_STS_ERROR;
2614 END;
2615
2616 BEGIN
2617 select department_id
2618 into l_dept_id
2619 from bom_departments
2620 where organization_id = l_org_id
2621 and department_code = p_owning_department;
2622
2623 EXCEPTION WHEN OTHERS THEN
2624 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_DEPT_NOT_FOUND');
2625 x_return_status := FND_API.G_RET_STS_ERROR;
2626 END;
2627
2628 if( p_firm_planned_flag = 1) then
2629 if(p_scheduled_start_date is null) then
2630 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_UPDATE_DATE_MISS');
2631 x_return_status := FND_API.G_RET_STS_ERROR;
2632 end if;
2633 end if;
2634
2635 -- if validate not passed then raise error
2636 l_msg_count := FND_MSG_PUB.count_msg;
2637 IF l_msg_count = 1 THEN
2638 eam_execution_jsp.Get_Messages
2639 (p_encoded => FND_API.G_FALSE,
2640 p_msg_index => 1,
2641 p_msg_count => l_msg_count,
2642 p_msg_data => nvl(l_msg_data,FND_API.g_MISS_CHAR) ,
2643 p_data => l_data,
2644 p_msg_index_out => l_msg_index_out);
2645 x_msg_count := l_msg_count;
2646 x_msg_data := l_msg_data;
2647 ELSE
2648 x_msg_count := l_msg_count;
2649 END IF;
2650
2651 IF l_msg_count > 0 THEN
2652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2653 RAISE FND_API.G_EXC_ERROR;
2654 END IF;
2655
2656 -- call processing logic
2657 BEGIN
2658 update wip_discrete_jobs j
2659 set j.description = p_description
2660 , j.activity_type = p_activity_type
2661 , j.activity_cause = p_activity_cause
2662 , j.owning_department = l_dept_id
2663 , j.priority = p_priority
2664 , j.shutdown_type = p_shutdown_type
2665 , j.firm_planned_flag = p_firm_planned_flag
2666 , j.notification_required = p_notification_required
2667 , j.tagout_required = p_tagout_required
2668 , j.last_update_date = sysdate
2669 , j.last_updated_by = g_last_updated_by
2670 , j.last_update_login = g_last_update_login
2671 where j.wip_entity_id = p_wip_entity_id;
2672
2673 if( p_firm_planned_flag = 1 and l_shift <> 0) then -- firm
2674 -- update work order start and completion date
2675 update wip_discrete_jobs j
2676 set j.scheduled_start_date = p_scheduled_start_date
2677 , j.scheduled_completion_date = j.scheduled_completion_date + l_shift
2678 where j.wip_entity_id = p_wip_entity_id;
2679
2680 -- shift operation dates
2681 update wip_operations op
2682 set op.first_unit_start_date = op.first_unit_start_date + l_shift
2683 , op.last_unit_start_date = op.last_unit_start_date + l_shift
2684 , op.first_unit_completion_date = op.first_unit_completion_date + l_shift
2685 , op.last_unit_completion_date = op.last_unit_completion_date + l_shift
2686 , op.last_update_date = sysdate
2687 , op.last_updated_by = g_last_updated_by
2688 , op.last_update_login = g_last_update_login
2689 where op.wip_entity_id = p_wip_entity_id;
2690
2691 -- shift resources dates
2692 update wip_operation_resources wor
2693 set wor.start_date = wor.start_date + l_shift
2694 , wor.completion_date = wor.completion_date + l_shift
2695 , wor.last_update_date = sysdate
2696 , wor.last_updated_by = g_last_updated_by
2697 , wor.last_update_login = g_last_update_login
2698 where wor.wip_entity_id = p_wip_entity_id;
2699 end if;
2700 EXCEPTION WHEN OTHERS THEN
2701 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_EXCEPTION'
2702 ,p_token1 => 'TEXT', p_value1 => SQLERRM);
2703 x_return_status := FND_API.G_RET_STS_ERROR;
2704 END;
2705
2706 -- if DML not passed then raise error
2707 l_msg_count := FND_MSG_PUB.count_msg;
2708 IF l_msg_count = 1 THEN
2709 eam_execution_jsp.Get_Messages
2710 (p_encoded => FND_API.G_FALSE,
2711 p_msg_index => 1,
2712 p_msg_count => l_msg_count,
2713 p_msg_data => nvl(l_msg_data,FND_API.g_MISS_CHAR) ,
2714 p_data => l_data,
2715 p_msg_index_out => l_msg_index_out);
2716 x_msg_count := l_msg_count;
2717 x_msg_data := l_msg_data;
2718 ELSE
2719 x_msg_count := l_msg_count;
2720 END IF;
2721
2722 IF l_msg_count > 0 THEN
2723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2724 RAISE FND_API.G_EXC_ERROR;
2725 END IF;
2726
2727
2728 IF FND_API.TO_BOOLEAN(P_COMMIT)
2729 THEN
2730 COMMIT WORK;
2731 END IF;
2732
2733 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2734 IF p_commit = FND_API.G_TRUE THEN
2735 ROLLBACK TO complete_workorder;
2736 END IF;
2737
2738 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'EAM_WORKORDERS_JSP.update_work_order',
2739 p_procedure_name => EAM_DEBUG.G_err_stack);
2740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2741 WHEN FND_API.G_EXC_ERROR THEN
2742 IF p_commit = FND_API.G_TRUE THEN
2743 ROLLBACK TO complete_workorder;
2744 END IF;
2745
2746 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'EAM_WORKORDERS_JSP.update_work_order',
2747 p_procedure_name => EAM_DEBUG.G_err_stack);
2748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2749 WHEN OTHERS THEN
2750 IF p_commit = FND_API.G_TRUE THEN
2751 ROLLBACK TO complete_workorder;
2752 END IF;
2753
2754 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'EAM_WORKORDERS_JSP.update_work_order',
2755 p_procedure_name => EAM_DEBUG.G_err_stack);
2756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2757 END update_work_order;
2758
2759 procedure get_completion_defaults (
2760 p_wip_entity_id in number
2761 ,p_tx_type in number
2762 ,p_sched_start_date in date
2763 ,p_sched_end_date in date
2764 ,x_start_date out NOCOPY date
2765 ,x_end_date out NOCOPY date
2766 ,x_return_status out NOCOPY varchar2
2767 ,x_msg_count out NOCOPY number
2768 ,x_msg_data out NOCOPY varchar2
2769 ) is
2770 l_api_name constant varchar2(30) := 'get_completion_defaults';
2771 l_max_op_end_date date := null;
2772 l_min_op_start_date date := null;
2773 l_sched_start_date date := null;
2774 l_sched_end_date date := null;
2775
2776 begin
2777 eam_debug.init_err_stack('eam_workorders_jsp.' || l_api_name);
2778
2779 --initialize so at sysdate is returned when 1) no completed operations exist
2780 -- 2) some other error takes place
2781 x_start_date := sysdate;
2782 x_end_date := sysdate;
2783 if (p_tx_type = 1) then --completion
2784 x_msg_data := 'Completion: ';
2785 if (p_sched_start_date is not null AND p_sched_end_date is not null) then
2786 l_sched_start_date := p_sched_start_date;
2787 l_sched_end_date := p_sched_end_date;
2788 else
2789 select scheduled_start_date, scheduled_completion_date
2790 into l_sched_start_date, l_sched_end_date
2791 from wip_discrete_jobs
2792 where wip_entity_id = p_wip_entity_id;
2793 end if;
2794 x_start_date := l_sched_start_date;
2795 x_end_date := l_sched_end_date; --fixed for #2429880.
2796 begin
2797 --fix for 3543834.changed queries to fetch correct data
2798 select max(actual_end_date)
2799 into l_max_op_end_date
2800 from eam_op_completion_txns eoct
2801 where wip_entity_id = p_wip_entity_id
2802 and transaction_type=1
2803 and transaction_id = (select max(transaction_id)
2804 from eam_op_completion_txns
2805 where wip_entity_id = p_wip_entity_id
2806 and operation_seq_num = eoct.operation_seq_num
2807 );
2808
2809 select min(actual_start_date)
2810 into l_min_op_start_date
2811 from eam_op_completion_txns eoct
2812 where wip_entity_id = p_wip_entity_id
2813 and transaction_type=1
2814 and transaction_id = (select max(transaction_id)
2815 from eam_op_completion_txns
2816 where wip_entity_id = p_wip_entity_id
2817 and operation_seq_num = eoct.operation_seq_num
2818 );
2819
2820 if (l_max_op_end_date is not null and l_min_op_start_date is not null) then
2821 x_start_date := l_min_op_start_date;
2822 x_end_date := l_max_op_end_date;
2823 end if;
2824
2825 exception
2826 when others then
2827 x_msg_data := x_msg_data || 'No completed operations exist: ';
2828 end;
2829 end if; -- of p_tx_type = 1
2830 if (p_tx_type = 2) then --uncompletion
2831 x_msg_data := x_msg_data || 'Uncompletion: ';
2832 select actual_start_date, actual_end_date into
2833 x_start_date, x_end_date
2834 from eam_job_completion_txns
2835 where wip_entity_id = p_wip_entity_id
2836 and transaction_date = (
2837 select max(transaction_date)
2838 from eam_job_completion_txns where transaction_type = 1
2839 and wip_entity_id = p_wip_entity_id);
2840 end if;
2841
2842 IF(x_start_date > SYSDATE) THEN
2843 x_start_date := SYSDATE;
2844 x_end_date := SYSDATE;
2845 ELSIF (x_end_date > SYSDATE) THEN
2846 x_end_date := SYSDATE;
2847 END IF;
2848
2849
2850 x_return_status := FND_API.G_RET_STS_SUCCESS;
2851 x_msg_data := x_msg_data ||
2852 'x_start_date = ' || to_char(x_start_date, 'MMM-DD-YYYY HH24:MI:SS')
2853 || ', x_end_date = ' || to_char(x_end_date, 'MMM-DD-YYYY HH24:MI:SS')
2854 || ', l_max_op_end_date=' || to_char(l_max_op_end_date, 'MMM-DD-YYYY HH24:MI:SS')
2855 || ', l_sched_start_date=' || to_char(l_sched_start_date, 'MMM-DD-YYYY HH24:MI:SS')
2856 || ', l_sched_end_date=' || to_char(l_sched_end_date, 'MMM-DD-YYYY HH24:MI:SS');
2857 exception
2858 when others then
2859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2860 x_msg_data := x_msg_data || ' UNEXPECTED ERROR: ' || SQLERRM;
2861 eam_debug.init_err_stack('Exception has occured in ' || l_api_name);
2862 end get_completion_defaults;
2863
2864
2865 procedure Add_WorkOrder_Dependency (
2866 p_api_version IN NUMBER := 1.0
2867 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2868 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2869 ,p_organization_id IN NUMBER
2870 ,p_prior_object_id IN NUMBER
2871 ,p_prior_object_type_id IN NUMBER
2872 ,p_next_object_id IN NUMBER
2873 ,p_next_object_type_id IN NUMBER
2874 ,x_return_status OUT NOCOPY VARCHAR2
2875 ,x_msg_count OUT NOCOPY NUMBER
2876 ,x_msg_data OUT NOCOPY VARCHAR2
2877 ) is
2878
2879 l_api_name constant varchar2(30) := 'Add_WorkOrder_Dependency';
2880 l_api_version CONSTANT NUMBER := 1.0;
2881 l_msg_data VARCHAR2(10000) ;
2882 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2883 l_msg_count NUMBER;
2884 l_message_text VARCHAR2(1000);
2885
2886 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
2887 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
2888 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
2889 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
2890 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
2891 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
2892 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
2893 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
2894 l_eam_wo_tbl_1 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
2895 l_eam_wo_relations_tbl_1 EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
2896 l_eam_op_tbl_1 EAM_PROCESS_WO_PUB.eam_op_tbl_type;
2897 l_eam_op_network_tbl_1 EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
2898 l_eam_res_tbl_1 EAM_PROCESS_WO_PUB.eam_res_tbl_type;
2899 l_eam_res_inst_tbl_1 EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
2900 l_eam_sub_res_tbl_1 EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
2901 l_eam_mat_req_tbl_1 EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
2902 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2903 l_eam_direct_items_tbl_1 EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
2904 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
2905
2906 l_eam_wo_relations_rec EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
2907 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
2908 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2909 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2910 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2911 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2912 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2913 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2914 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
2915 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
2916 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
2917 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
2918 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
2919 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
2920 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
2921 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2922 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
2923
2924 l_output_dir VARCHAR2(512);
2925
2926
2927 begin
2928
2929 IF p_commit = FND_API.G_TRUE THEN
2930 SAVEPOINT CREATE_DEPENDENT_WORK_ORDER;
2931 END IF;
2932
2933 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2934 p_api_version,
2935 l_api_name,
2936 g_pkg_name)
2937 THEN
2938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2939 END IF;
2940
2941 IF FND_API.TO_BOOLEAN(p_init_msg_list)
2942 THEN
2943 FND_MSG_PUB.initialize;
2944 END IF;
2945
2946 IF p_prior_object_id = 0000 OR p_next_object_id = 0000 THEN
2947 x_return_status := FND_API.G_RET_STS_ERROR;
2948 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_NOT_ENOUGH_VALUES');
2949 return;
2950 END IF;
2951
2952 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
2953
2954 l_eam_wo_relations_rec.BATCH_ID :=1;
2955 l_eam_wo_relations_rec.WO_RELATIONSHIP_ID :=null;
2956 l_eam_wo_relations_rec.PARENT_OBJECT_ID :=p_prior_object_id;
2957 l_eam_wo_relations_rec.PARENT_OBJECT_TYPE_ID :=1;
2958 l_eam_wo_relations_rec.PARENT_HEADER_ID :=1;
2959 l_eam_wo_relations_rec.CHILD_OBJECT_ID :=p_next_object_id;
2960 l_eam_wo_relations_rec.CHILD_OBJECT_TYPE_ID :=1;
2961 l_eam_wo_relations_rec.CHILD_HEADER_ID :=2;
2962 l_eam_wo_relations_rec.PARENT_RELATIONSHIP_TYPE :=2;
2963 l_eam_wo_relations_rec.RELATIONSHIP_STATUS :=null;
2964 l_eam_wo_relations_rec.TOP_LEVEL_OBJECT_ID :=null;
2965 l_eam_wo_relations_rec.TOP_LEVEL_OBJECT_TYPE_ID :=1;
2966 l_eam_wo_relations_rec.TOP_LEVEL_HEADER_ID :=1;
2967 l_eam_wo_relations_rec.RETURN_STATUS :=null;
2968 l_eam_wo_relations_rec.TRANSACTION_TYPE :=EAM_PROCESS_WO_PUB.G_OPR_CREATE;
2969
2970 l_eam_wo_relations_tbl(1) := l_eam_wo_relations_rec;
2971
2972
2973
2974
2975 EAM_PROCESS_WO_PUB.l_eam_wo_list.delete; --Added for bug#4563210
2976
2977
2978 EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO
2979 ( p_bo_identifier => 'EAM'
2980 , p_api_version_number=> 1.0
2981 , p_init_msg_list => TRUE
2982 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
2983 , p_eam_wo_tbl => l_eam_wo_tbl
2984 , p_eam_op_tbl => l_eam_op_tbl
2985 , p_eam_op_network_tbl => l_eam_op_network_tbl
2986 , p_eam_res_tbl => l_eam_res_tbl
2987 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
2988 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
2989 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
2990 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
2991 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
2992 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
2993 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
2994 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
2995 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
2996 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
2997 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
2998 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
2999 , p_eam_request_tbl => l_eam_request_tbl
3000 , x_eam_wo_tbl => l_eam_wo_tbl_1
3001 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl_1
3002 , x_eam_op_tbl => l_eam_op_tbl_1
3003 , x_eam_op_network_tbl => l_eam_op_network_tbl_1
3004 , x_eam_res_tbl => l_eam_res_tbl_1
3005 , x_eam_res_inst_tbl => l_eam_res_inst_tbl_1
3006 , x_eam_sub_res_tbl => l_eam_sub_res_tbl_1
3007 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
3008 , x_eam_mat_req_tbl => l_eam_mat_req_tbl
3009 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
3010 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
3011 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3012 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3013 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3014 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3015 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
3016 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
3017 , x_eam_request_tbl => l_out_eam_request_tbl
3018 , x_return_status => l_return_status
3019 , x_msg_count => l_msg_count
3020 -- , x_error_msg_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.error_tbl_type
3021 , p_commit => FND_API.G_TRUE
3022 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3023 , p_debug_filename => 'adddepen.log'
3024 , p_output_dir => l_output_dir
3025 , p_debug_file_mode => 'W'
3026 );
3027
3028
3029 l_msg_count := FND_MSG_PUB.count_msg;
3030 x_return_status := l_return_status;
3031 x_msg_count := l_msg_count;
3032
3033
3034 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3035 IF p_commit = FND_API.G_TRUE THEN
3036 ROLLBACK TO CREATE_DEPENDENT_WORK_ORDER;
3037 END IF;
3038 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_NEXT,
3039 p_encoded => 'F',
3040 p_data => l_message_text,
3041 p_msg_index_out => l_msg_count);
3042 fnd_message.set_name('EAM','EAM_ERROR_UPDATE_WO');
3043
3044 fnd_message.set_token(token => 'MESG',
3045 value => l_message_text,
3046 translate => FALSE);
3047 APP_EXCEPTION.RAISE_EXCEPTION;
3048
3049 END IF;
3050
3051 IF p_commit = FND_API.G_TRUE THEN
3052 COMMIT WORK;
3053 end if;
3054
3055 EXCEPTION
3056
3057 when others then
3058 IF p_commit = FND_API.G_TRUE THEN
3059 ROLLBACK TO CREATE_DEPENDENT_WORK_ORDER;
3060 END IF;
3061
3062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3063 return;
3064
3065
3066 end Add_WorkOrder_Dependency;
3067
3068
3069 procedure Delete_WorkOrder_Dependency (
3070 p_api_version IN NUMBER := 1.0
3071 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3072 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3073 ,p_organization_id IN NUMBER
3074 ,p_prior_object_id IN NUMBER
3075 ,p_prior_object_type_id IN NUMBER
3076 ,p_next_object_id IN NUMBER
3077 ,p_next_object_type_id IN NUMBER
3078 ,p_relationship_type IN NUMBER := 2
3079 ,x_return_status OUT NOCOPY VARCHAR2
3080 ,x_msg_count OUT NOCOPY NUMBER
3081 ,x_msg_data OUT NOCOPY VARCHAR2
3082 ) is
3083
3084 l_api_name constant varchar2(30) := 'Delete_WorkOrder_Dependency';
3085 l_api_version CONSTANT NUMBER := 1.0;
3086
3087 -- All in parameters
3088 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
3089 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
3090 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3091 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3092 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3093 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3094 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3095 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3096 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3097
3098 -- All Out parateres
3099 l_out_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
3100 l_out_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
3101 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3102 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3103 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3104 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3105 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3106 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3107 l_eam_direct_items_tbl_1 EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3108
3109 l_eam_wo_relations_rec EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
3110
3111 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
3112 l_msg_count NUMBER;
3113 l_message_text VARCHAR2(1000);
3114 l_output_dir VARCHAR2(512);
3115 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
3116 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3117 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3118 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3119 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3120 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3121 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3122 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
3123 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3124 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3125 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3126 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3127 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3128 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3129 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3130 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3131 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3132
3133 begin
3134
3135 IF p_commit = FND_API.G_TRUE THEN
3136 SAVEPOINT DELETE_DEPENDEND_WORK_ORDER;
3137 END IF;
3138
3139 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3140 p_api_version,
3141 l_api_name,
3142 g_pkg_name)
3143 THEN
3144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3145 END IF;
3146
3147 IF FND_API.TO_BOOLEAN(p_init_msg_list)
3148 THEN
3149 FND_MSG_PUB.initialize;
3150 END IF;
3151
3152 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
3153
3154 l_eam_wo_relations_rec.BATCH_ID :=1;
3155 l_eam_wo_relations_rec.WO_RELATIONSHIP_ID :=null;
3156 l_eam_wo_relations_rec.PARENT_OBJECT_ID :=p_prior_object_id;
3157 l_eam_wo_relations_rec.PARENT_OBJECT_TYPE_ID :=p_prior_object_type_id;
3158 l_eam_wo_relations_rec.PARENT_HEADER_ID :=1;
3159 l_eam_wo_relations_rec.CHILD_OBJECT_ID :=p_next_object_id;
3160 l_eam_wo_relations_rec.CHILD_OBJECT_TYPE_ID :=p_next_object_type_id;
3161 l_eam_wo_relations_rec.CHILD_HEADER_ID :=2;
3162 l_eam_wo_relations_rec.PARENT_RELATIONSHIP_TYPE :=p_relationship_type;
3163 l_eam_wo_relations_rec.RELATIONSHIP_STATUS :=null;
3164 l_eam_wo_relations_rec.TOP_LEVEL_OBJECT_ID :=null;
3165 l_eam_wo_relations_rec.TOP_LEVEL_OBJECT_TYPE_ID :=1;
3166 l_eam_wo_relations_rec.TOP_LEVEL_HEADER_ID :=1;
3167 l_eam_wo_relations_rec.RETURN_STATUS :=null;
3168 l_eam_wo_relations_rec.TRANSACTION_TYPE :=EAM_PROCESS_WO_PUB.G_OPR_DELETE;
3169
3170
3171 l_eam_wo_relations_tbl(1) := l_eam_wo_relations_rec;
3172
3173 eam_process_wo_pub.PROCESS_MASTER_CHILD_WO
3174 ( p_bo_identifier => 'EAM'
3175 , p_init_msg_list => TRUE
3176 , p_api_version_number => 1.0
3177 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
3178 , p_eam_wo_tbl => l_eam_wo_tbl
3179 , p_eam_op_tbl => l_eam_op_tbl
3180 , p_eam_op_network_tbl => l_eam_op_network_tbl
3181 , p_eam_res_tbl => l_eam_res_tbl
3182 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
3183 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3184 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
3185 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3186 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
3187 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
3188 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3189 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
3190 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
3191 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
3192 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
3193 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3194 , p_eam_request_tbl => l_eam_request_tbl
3195 , x_eam_wo_tbl => l_out_eam_wo_tbl
3196 , x_eam_wo_relations_tbl => l_out_eam_wo_relations_tbl
3197 , x_eam_op_tbl => l_out_eam_op_tbl
3198 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
3199 , x_eam_res_tbl => l_out_eam_res_tbl
3200 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
3201 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
3202 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
3203 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
3204 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
3205 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
3206 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3207 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3208 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3209 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3210 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
3211 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
3212 , x_eam_request_tbl => l_out_eam_request_tbl
3213 , x_return_status => l_return_status
3214 , x_msg_count => l_msg_count
3215 -- , x_error_msg_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.error_tbl_type
3216 , p_commit => p_commit
3217 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3218 , p_debug_filename => 'deldepen.log'
3219 , p_output_dir => l_output_dir
3220 , p_debug_file_mode => 'W'
3221 );
3222
3223
3224 l_msg_count := FND_MSG_PUB.count_msg;
3225 x_return_status := l_return_status;
3226 x_msg_count := l_msg_count;
3227
3228 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3229 IF p_commit = FND_API.G_TRUE THEN
3230 ROLLBACK TO DELETE_DEPENDEND_WORK_ORDER;
3231 END IF;
3232
3233 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_NEXT,
3234 p_encoded => 'F',
3235 p_data => l_message_text,
3236 p_msg_index_out => l_msg_count);
3237 fnd_message.set_name('EAM','EAM_ERROR_UPDATE_WO');
3238
3239 fnd_message.set_token(token => 'MESG',
3240 value => l_message_text,
3241 translate => FALSE);
3242
3243 APP_EXCEPTION.RAISE_EXCEPTION;
3244 ELSE
3245 IF FND_API.TO_BOOLEAN(p_commit)THEN
3246 COMMIT WORK;
3247 END IF;
3248 END IF;
3249
3250
3251 EXCEPTION
3252
3253 when others then
3254 IF p_commit = FND_API.G_TRUE THEN
3255 ROLLBACK TO DELETE_DEPENDEND_WORK_ORDER;
3256 END IF;
3257
3258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3259 return;
3260 end Delete_WorkOrder_Dependency;
3261
3262 -- Start of comments
3263 -- API name : create_cost_hierarchy_pvt
3264 -- Type : Private.
3265 -- Function : Creates the costing hierarchy from the scheduling hierarchy.
3266 -- Pre-reqs : None.
3267 -- Parameters :
3268 -- IN p_api_version IN NUMBER
3269 -- p_init_msg_list IN VARCHAR2 Default = FND_API.G_TRUE
3270 -- p_commit IN VARCHAR2 Default = FND_API.G_FALSE
3271 -- p_validation_level IN NUMBER Default = FND_API.G_VALID_LEVEL_FULL
3272 -- p_top_level_object_id IN VARCHAR2
3273 -- OUT x_return_status OUT NOCOPY NUMBER
3274 -- x_msg_count OUT NOCOPY NUMBER
3275 -- x_msg_data OUT NOCOPY VARCHAR2
3276 -- Notes : The procedure gets the entire work hierarchy for the required top_level_object_id.
3277 -- It then passes the child workorder and the parent Work order to the Process_Master_Child_WO
3278 -- in the EAM_PROCESS_WO_PUB, to generate the costing relationship between the 2 workorders
3279 --
3280 -- End of comments
3281 procedure create_cost_hierarchy_pvt(
3282 p_api_version IN NUMBER :=1.0 ,
3283 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
3284 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
3285 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
3286 p_wip_entity_id IN VARCHAR2,
3287 p_org_id IN VARCHAR2,
3288 x_return_status OUT NOCOPY VARCHAR2 ,
3289 x_msg_count OUT NOCOPY NUMBER ,
3290 x_msg_data OUT NOCOPY VARCHAR2
3291 )
3292 IS
3293 --Bug3545056: Import hierarchy only under the workorder.
3294 CURSOR c_work_hierarchy IS
3295 SELECT child_object_id,
3296 parent_object_id,
3297 PARENT_RELATIONSHIP_TYPE
3298 FROM EAM_WO_RELATIONSHIPS
3299 WHERE parent_relationship_type = 1
3300 START WITH parent_object_id = p_wip_entity_id
3301 AND parent_relationship_type = 1
3302 CONNECT BY parent_object_id = PRIOR child_object_id
3303 AND parent_relationship_type = 1;
3304
3305 l_workorder_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3306 l_workorder_rec1 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3307 l_workorder_rec2 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3308 l_workorder_rec3 EAM_PROCESS_WO_PUB.eam_wo_rec_type;
3309 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3310 l_eam_op_tbl1 EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3311 l_eam_op_tbl2 EAM_PROCESS_WO_PUB.eam_op_tbl_type;
3312 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3313 l_eam_op_network_tbl1 EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3314 l_eam_op_network_tbl2 EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
3315 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3316 l_eam_res_tbl1 EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3317 l_eam_res_tbl2 EAM_PROCESS_WO_PUB.eam_res_tbl_type;
3318 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3319 l_eam_res_inst_tbl1 EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3320 l_eam_res_inst_tbl2 EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
3321 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3322 l_eam_sub_res_tbl1 EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3323 l_eam_sub_res_tbl2 EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
3324 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3325 l_eam_res_usage_tbl1 EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3326 l_eam_res_usage_tbl2 EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
3327 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3328 l_eam_mat_req_tbl1 EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3329 l_eam_mat_req_tbl2 EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
3330 l_wip_entity_id NUMBER;
3331 --Bug3592712: Max length of workorder name is 240 char.
3332 l_wip_entity_name VARCHAR2(240);
3333
3334 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
3335 l_eam_wo_relations_tbl1 EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
3336 l_eam_wo_relations_rec EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
3337 l_eam_wo_relations_rec1 EAM_PROCESS_WO_PUB.eam_wo_relations_rec_type;
3338
3339 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
3340 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3341 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3342 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3343 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3344 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3345 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3346
3347 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
3348 l_eam_wo_tbl1 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
3349 l_eam_wo_tbl2 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
3350 l_eam_wo_tbl3 EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
3351
3352 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
3353 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
3354 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
3355 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
3356 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
3357 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
3358 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
3359 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3360 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
3361
3362 l_eam_msg_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
3363 l_old_rebuild_source NUMBER;
3364
3365 l_eam_direct_items_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3366 l_eam_direct_items_tbl_1 EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
3367
3368 l_parent_id NUMBER;
3369 l_child_wip_id NUMBER;
3370
3371 l_api_name CONSTANT VARCHAR2(30) := 'create_cost_hierarchy_pvt';
3372 l_api_version CONSTANT NUMBER := 1.0;
3373 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3374 l_parent_object_id NUMBER := null;
3375 l_output_dir VARCHAR2(512);
3376
3377
3378 BEGIN
3379 SAVEPOINT create_cost_hierarchy;
3380
3381 IF NOT FND_API.compatible_api_call(
3382 l_api_version
3383 ,p_api_version
3384 ,l_api_name
3385 ,g_pkg_name) THEN
3386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3387 END IF;
3388
3389 -- Initialize message list if p_init_msg_list is set to TRUE.
3390 IF FND_API.to_boolean(p_init_msg_list) THEN
3391 FND_MSG_PUB.initialize;
3392 END IF;
3393
3394 -- Initialize API return status to success
3395 x_return_status := FND_API.g_ret_sts_success;
3396
3397 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
3398
3399 FOR c_hierarchy_row IN c_work_hierarchy
3400 LOOP
3401 BEGIN
3402 -- Delete any parent for the current work order having type 3 relationship, to create the new one.
3403
3404
3405 SELECT parent_object_id INTO l_parent_object_id
3406 FROM EAM_WO_RELATIONSHIPS
3407 WHERE parent_relationship_type = 3
3408 AND child_object_id = c_hierarchy_row.child_object_id;
3409
3410
3411
3412 -- Delete the record if exists. The child WO cannot have more than 1 parent.
3413 EAM_WORKORDERS_JSP.Delete_WorkOrder_Dependency (
3414 p_commit => FND_API.G_TRUE
3415 ,p_prior_object_type_id => 1
3416 ,p_next_object_type_id => 1
3417 ,p_organization_id => p_org_id
3418 ,p_prior_object_id => l_parent_object_id
3419 ,p_next_object_id => c_hierarchy_row.child_object_id
3420 ,p_relationship_type => 3
3421 ,x_return_status => x_return_status
3422 ,x_msg_count => x_msg_count
3423 ,x_msg_data => x_msg_data
3424 );
3425
3426
3427 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3428 /* Create a new relationship between the parent and the child workorder taken from the scheduling hierarchy. */
3429 l_parent_id := c_hierarchy_row.parent_object_id ;
3430 l_child_wip_id := c_hierarchy_row.child_object_id ;
3431 l_eam_wo_relations_rec.batch_id := 1;
3432 l_eam_wo_relations_rec.parent_object_id := l_parent_id;
3433 l_eam_wo_relations_rec.parent_object_type_id := 1;
3434 l_eam_wo_relations_rec.parent_header_id := l_parent_id;
3435 l_eam_wo_relations_rec.child_object_type_id := 1;
3436 l_eam_wo_relations_rec.child_header_id :=l_child_wip_id;
3437 l_eam_wo_relations_rec.child_object_id :=l_child_wip_id;
3438 l_eam_wo_relations_rec.parent_relationship_type := 3;
3439 l_eam_wo_relations_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
3440
3441 l_eam_wo_relations_tbl(1) := l_eam_wo_relations_rec;
3442
3443 EAM_PROCESS_WO_PUB.Process_Master_Child_WO(
3444 p_bo_identifier => 'EAM'
3445 , p_init_msg_list => TRUE
3446 , p_api_version_number => 1.0
3447 , p_eam_wo_tbl => l_eam_wo_tbl2
3448 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
3449 , p_eam_op_tbl => l_eam_op_tbl
3450 , p_eam_op_network_tbl => l_eam_op_network_tbl
3451 , p_eam_res_tbl => l_eam_res_tbl
3452 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
3453 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3454 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
3455 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3456 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
3457 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
3458 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3459 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
3460 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
3461 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
3462 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
3463 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3464 , p_eam_request_tbl => l_eam_request_tbl
3465 , x_eam_wo_tbl => l_eam_wo_tbl1
3466 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
3467 , x_eam_op_tbl => l_eam_op_tbl1
3468 , x_eam_op_network_tbl => l_eam_op_network_tbl1
3469 , x_eam_res_tbl => l_eam_res_tbl1
3470 , x_eam_res_inst_tbl => l_eam_res_inst_tbl1
3471 , x_eam_sub_res_tbl => l_eam_sub_res_tbl1
3472 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
3473 , x_eam_mat_req_tbl => l_eam_mat_req_tbl1
3474 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
3475 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
3476 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3477 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3478 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3479 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3480 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
3481 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
3482 , x_eam_request_tbl => l_out_eam_request_tbl
3483 , x_return_status => x_return_status
3484 , x_msg_count => x_msg_count
3485 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3486 , p_debug_filename => 'deletecosthier.log'
3487 , p_output_dir => l_output_dir
3488 , p_commit => p_commit
3489 , p_debug_file_mode => 'A'
3490 );
3491
3492 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3495 END IF;
3496 ELSE
3497 -- if there is any exception then rollback and come out of the procedure.
3498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3500 END IF;
3501
3502 EXCEPTION
3503 -- If there is no parent with type '3' for the workorder, then simply create a new relationship.
3504 WHEN NO_DATA_FOUND THEN
3505
3506
3507 -- create the new parent child costing relationship if it does not exist.
3508 l_parent_id := c_hierarchy_row.parent_object_id ;
3509 l_child_wip_id := c_hierarchy_row.child_object_id ;
3510 l_eam_wo_relations_rec.batch_id := 1;
3511 l_eam_wo_relations_rec.parent_object_id := l_parent_id;
3512 l_eam_wo_relations_rec.parent_object_type_id := 1;
3513 l_eam_wo_relations_rec.parent_header_id := l_parent_id;
3514 l_eam_wo_relations_rec.child_object_type_id := 1;
3515 l_eam_wo_relations_rec.child_header_id :=l_child_wip_id;
3516 l_eam_wo_relations_rec.child_object_id :=l_child_wip_id;
3517 l_eam_wo_relations_rec.parent_relationship_type := 3;
3518 l_eam_wo_relations_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
3519
3520 l_eam_wo_relations_tbl(1) := l_eam_wo_relations_rec;
3521 EAM_PROCESS_WO_PUB.Process_Master_Child_WO(
3522 p_bo_identifier => 'EAM'
3523 , p_init_msg_list => TRUE
3524 , p_api_version_number => 1.0
3525 , p_eam_wo_tbl => l_eam_wo_tbl2
3526 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
3527 , p_eam_op_tbl => l_eam_op_tbl
3528 , p_eam_op_network_tbl => l_eam_op_network_tbl
3529 , p_eam_res_tbl => l_eam_res_tbl
3530 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
3531 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
3532 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
3533 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
3534 , p_eam_direct_items_tbl => l_eam_direct_items_tbl
3535 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
3536 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
3537 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
3538 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
3539 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
3540 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
3541 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
3542 , p_eam_request_tbl => l_eam_request_tbl
3543 , x_eam_wo_tbl => l_eam_wo_tbl1
3544 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl1
3545 , x_eam_op_tbl => l_eam_op_tbl1
3546 , x_eam_op_network_tbl => l_eam_op_network_tbl1
3547 , x_eam_res_tbl => l_eam_res_tbl1
3548 , x_eam_res_inst_tbl => l_eam_res_inst_tbl1
3549 , x_eam_sub_res_tbl => l_eam_sub_res_tbl1
3550 , x_eam_res_usage_tbl => l_eam_res_usage_tbl
3551 , x_eam_mat_req_tbl => l_eam_mat_req_tbl1
3552 , x_eam_direct_items_tbl => l_eam_direct_items_tbl_1
3553 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
3554 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
3555 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
3556 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
3557 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
3558 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
3559 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
3560 , x_eam_request_tbl => l_out_eam_request_tbl
3561 , x_return_status => x_return_status
3562 , x_msg_count => x_msg_count
3563 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
3564 , p_debug_filename =>'createcosthier.log'
3565 , p_output_dir => l_output_dir
3566 , p_commit => p_commit
3567 , p_debug_file_mode => 'A'
3568 );
3569
3570 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3572 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3573 END IF;
3574 WHEN OTHERS THEN
3575
3576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3578 END;
3579 END LOOP;
3580
3581
3582
3583 IF ((FND_API.TO_BOOLEAN(p_commit)) AND (x_return_status = FND_API.G_RET_STS_SUCCESS)) THEN
3584 COMMIT WORK;
3585 END IF;
3586
3587 EXCEPTION
3588 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3589 ROLLBACK TO create_cost_hierarchy;
3590 FND_MSG_PUB.add_exc_msg( p_pkg_name => l_full_name,
3591 p_procedure_name => l_api_name);
3592 WHEN OTHERS THEN
3593 ROLLBACK TO create_cost_hierarchy;
3594 FND_MSG_PUB.add_exc_msg( p_pkg_name => l_full_name,
3595 p_procedure_name => l_api_name);
3596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3597
3598 END create_cost_hierarchy_pvt;
3599
3600 -- Start of comments
3601 -- API name : resize_wo_edit_hierarchy_pvt
3602 -- Type : Private.
3603 -- Function : Insert the hierarchy into the CST_EAM_HIERARCHY_SNAPSHOT table.
3604 -- Pre-reqs : None.
3605 -- Parameters :
3606 -- IN p_api_version IN NUMBER
3607 -- p_init_msg_list IN VARCHAR2 Default = FND_API.G_FALSE
3608 -- p_commit IN VARCHAR2 Default = FND_API.G_FALSE
3609 -- p_validation_level IN NUMBER Default = FND_API.G_VALID_LEVEL_FULL
3610 -- p_object_id IN NUMBER
3611 -- p_object_type_id IN NUMBER
3612 -- p_schedule_start_date IN DATE
3613 -- p_schedule_end_date IN DATE
3614 -- p_requested_start_date IN DATE := NULL
3615 -- p_requested_due_date IN DATE := NULL
3616 -- p_duration_for_shifting IN NUMBER
3617 -- p_firm IN NUMBER
3618 -- OUT x_return_status OUT NOCOPY NUMBER
3619 -- x_msg_count OUT NOCOPY NUMBER
3620 -- x_msg_data OUT NOCOPY VARCHAR2
3621 -- Notes : The procedure sees if the dates being passed are >= current date.
3622 -- Consider only schedule start and end date if schedule start date,end date and duration
3623 -- is entered.If any 2 is given calculate the other and pass the Start Date and End Date
3624 -- to the API to resize the workorder.
3625 --
3626 -- End of comments
3627
3628 /*Bug3521886: Pass requested start date and due date*/
3629 PROCEDURE resize_wo_hierarchy_pvt(
3630 p_api_version IN NUMBER ,
3631 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
3632 p_commit IN VARCHAR2:= FND_API.G_FALSE ,
3633 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
3634 p_object_id IN NUMBER,
3635 p_object_type_id IN NUMBER,
3636 p_schedule_start_date IN DATE,
3637 p_schedule_end_date IN DATE,
3638 p_duration_for_shifting IN NUMBER,
3639 p_requested_start_date IN DATE := NULL ,
3640 p_requested_due_date IN DATE := NULL,
3641 p_firm IN NUMBER,
3642 p_org_id IN VARCHAR2,
3643 x_return_status OUT NOCOPY VARCHAR2 ,
3644 x_msg_count OUT NOCOPY NUMBER ,
3645 x_msg_data OUT NOCOPY VARCHAR2
3646 ) IS
3647
3648 l_schedule_start_date DATE := p_schedule_start_date;
3649 l_schedule_end_date DATE := p_schedule_end_date;
3650 l_api_name VARCHAR2(100) := 'resize_wo_hierarchy_pvt';
3651 l_msg_data VARCHAR2(10000) ;
3652 l_api_version NUMBER := 1.0;
3653 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3654 BEGIN
3655
3656 -- Standard Start of API savepoint
3657 SAVEPOINT resize_wo_hierarchy_pvt;
3658
3659 -- Standard call to check for call compatibility.
3660 IF NOT FND_API.compatible_api_call(
3661 l_api_version
3662 ,p_api_version
3663 ,l_api_name
3664 ,g_pkg_name) THEN
3665 RAISE fnd_api.g_exc_unexpected_error;
3666 END IF;
3667
3668 -- Initialize message list if p_init_msg_list is set to TRUE.
3669 IF FND_API.to_boolean(p_init_msg_list) THEN
3670 FND_MSG_PUB.initialize;
3671 END IF;
3672
3673 -- Initialize API return status to success
3674 x_return_status := FND_API.g_ret_sts_success;
3675
3676 -- API body
3677 IF (p_schedule_end_date IS NULL) THEN
3678 l_schedule_end_date := p_schedule_start_date + (p_duration_for_shifting / 24);
3679 ELSIF (l_schedule_start_date IS NULL) THEN
3680 l_schedule_start_date := p_schedule_end_date - (p_duration_for_shifting / 24);
3681 END IF;
3682
3683 -- Call the API to
3684 EAM_WO_NETWORK_DEFAULT_PVT.Resize_WO(
3685 p_api_version => 1.0 ,
3686 p_object_id => p_object_id,
3687 p_object_type_id => 1,
3688 p_start_date => l_schedule_start_date,
3689 p_completion_date => l_schedule_end_date,
3690 /*Bug3521886: Pass requested start date and due date*/
3691 p_required_start_date => p_requested_start_date,
3692 p_required_due_date => p_requested_due_date,
3693 p_org_id => p_org_id,
3694 p_firm => p_firm ,
3695 x_return_status => x_return_status,
3696 x_msg_count => x_msg_count,
3697 x_msg_data => x_msg_data);
3698 -- End of API body
3699 IF (x_return_status = FND_API.g_ret_sts_success) THEN
3700 IF FND_API.TO_BOOLEAN(p_commit)THEN
3701 COMMIT WORK;
3702 END IF;
3703 END IF;
3704
3705
3706 FND_MSG_PUB.Count_And_Get
3707 ( p_count => x_msg_count ,
3708 p_data => x_msg_data
3709 );
3710 IF x_msg_count > 0 THEN
3711 FOR indexCount IN 1 ..x_msg_count
3712 LOOP
3713 l_msg_data := FND_MSG_PUB.get(indexCount, FND_API.G_FALSE);
3714 -- DBMS_OUTPUT.PUT_LINE(indexCount ||'-'||l_msg_data);
3715 END LOOP;
3716 END IF;
3717
3718 EXCEPTION
3719 WHEN OTHERS THEN
3720 ROLLBACK TO resize_wo_hierarchy_pvt;
3721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3722
3723
3724
3725 END resize_wo_hierarchy_pvt;
3726 end EAM_WORKORDERS_JSP;