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